# View: KNR_Performer_Run_Prod_Unallocated

**View Name:** KNR_Performer_Run_Prod_Unallocated
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Customer_Master_Master, Prod.Accounting_Run_Results_Nr_Staging, Prod.Dim_Country, Prod.Dim_Transactiontype, Prod.Performance_Nr_Contribution + 3 more`
**File Path:** `views/KNR_Performer_Run_Prod_Unallocated.view.lkml`

## Overview

- **File Size:** 5424 bytes
- **Lines of Code:** 197
- **Dimensions:** 22
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Contributor_Only` | string |
| `Contributor_ID` | string |
| `Contributor_Name` | string |
| `Contributor_Stage_Name` | string |
| `Contribution_ID` | string |
| `Contribution_Created_Date` | string |
| `Contribution_Type` | string |
| `ISRC` | string |
| `Recording_ID` | string |
| `Main_Artist` | string |
| `Recording_Title` | string |
| `Recording_Version` | string |
| `Album_Name` | string |
| `Recording_Year` | string |
| `Release_Year` | number |
| `Society` | string |
| `Country` | string |
| `Transaction_Type` | string |
| `Total_Rows` | number |
| `Revenue_USD` | number |
| `WHT_USD` | number |
| `Accounting_Period_ID` | string |

## SQL Comments

- Get the distinct transaction ids that are not in the accounting run

## Derived Table

```sql
sql:
    WITH GROUPED_UNMATCHED_SALES AS (
WITH UNMATCHED_SALES AS (
    SELECT
        nrst.contributor_only
        ,nrst.contributor_id
        ,nrst.contribution_id
        ,nrst.isrc
        ,nrst.sound_recording_id
        ,nrst.store_id
        ,nrst.country_id
        ,nrst.transaction_type
        ,nrst.quantity
        ,nrst.unit_price_usd
        ,nrst.withholding_tax_usd
        ,sff.ACCOUNTING_PERIOD_ID
    FROM ROYALTY_ACCOUNTING.PROD.STMT_DB_SALES_NR nrst
    left join ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.SALES_FILE sff ON nrst.SALES_FILE_ID=sff.SALES_FILE_ID
    WHERE STMT_DB_SALES_NR_TXN_ID IN (
    -- Get the distinct transaction ids that are not in the accounting run
        SELECT DISTINCT(s.STMT_DB_SALES_NR_TXN_ID)
        FROM ROYALTY_ACCOUNTING.PROD.STMT_DB_SALES_NR s
        INNER JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.SALES_FILE sf ON s.SALES_FILE_ID=sf.SALES_FILE_ID
        LEFT JOIN (
            SELECT arr.TXN_ID, ar.ACCOUNTING_PERIOD_ID, ar.ACCOUNTING_RUN_ID
            FROM ROYALTY_ACCOUNTING.PROD.ACCOUNTING_RUN_RESULTS_NR_STAGING arr
            INNER JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar ON AR.ACCOUNTING_RUN_ID=arr.ACCOUNTING_RUN_ID
            WHERE
            ar.RUN_STATUS IN ('Complete', 'Committed')
            AND ar._FIVETRAN_DELETED=FALSE
        ) arr ON arr.TXN_ID=s.STMT_DB_SALES_NR_TXN_ID
        WHERE sf._FIVETRAN_DELETED=FALSE
        AND arr.TXN_ID IS NULL
    )
)
SELECT
    US.CONTRIBUTOR_ONLY
    ,US.CONTRIBUTOR_ID
    ,US.CONTRIBUTION_ID
    ,US.ISRC
    ,US.SOUND_RECORDING_ID
    ,US.STORE_ID
    ,US.COUNTRY_ID
    ,US.TRANSACTION_TYPE
    ,COUNT(*) AS TOTAL_ROWS
    ,SUM(US.QUANTITY * us.UNIT_PRICE_USD) AS REVENUE_USD
    ,SUM(US.WITHHOLDING_TAX_USD) AS TOTAL_WHT_USD
    ,US.ACCOUNTING_PERIOD_ID
FROM UNMATCHED_SALES us
GROUP BY 1,2,3,4,5,6,7,8,12
)
SELECT
     GUS.CONTRIBUTOR_ONLY
    ,GUS.CONTRIBUTOR_ID
    ,NR_CONTRIBUTOR.NAME
    ,NR_CONTRIBUTOR.STAGE_NAME
    ,GUS.CONTRIBUTION_ID
   ,NR_CONT_TYPE.CONTRIBUTION_TYPE
    ,GUS.ISRC
    ,GUS.SOUND_RECORDING_ID
    ,NR_SOUND_RECORDING.MAIN_ARTIST
    ,NR_SOUND_RECORDING.RECORDING_TITLE
    ,NR_SOUND_RECORDING.VERSION
    ,NR_SOUND_RECORDING.ALBUM_NAME
    ,NR_SOUND_RECORDING.RECORDING_YEAR
    ,NR_SOUND_RECORDING.FIRST_RELEASE_YEAR
    ,CMM.CUSTOMER_NAME
    ,DC.COUNTRYNAME
    ,DTT.TRANSACTIONTYPEDESC
        ,GUS.TOTAL_ROWS
    ,GUS.REVENUE_USD
    ,GUS.TOTAL_WHT_USD
    ,NR_CONTRIBUTION.created_at
,gus.ACCOUNTING_PERIOD_ID
    FROM GROUPED_UNMATCHED_SALES gus
  LEFT JOIN FACTS.PROD.DIM_COUNTRY AS DC ON DC.COUNTRYID = gus.COUNTRY_ID
  LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER AS cmm ON cmm.CUSTOMER_MASTER_MASTER_ID=gus.store_id
  LEFT JOIN FACTS.PROD.DIM_TRANSACTIONTYPE AS DTT ON dtt.transactiontypeabbr=gus.transaction_type
  LEFT JOIN FACTS.PROD.PERFORMANCE_NR_CONTRIBUTION AS NR_CONTRIBUTION ON NR_CONTRIBUTION.ID=gus.contribution_id
  LEFT JOIN FACTS.PROD.performance_nr_contributor AS NR_CONTRIBUTOR ON NR_CONTRIBUTOR.ID=gus.contributor_id
  LEFT JOIN FACTS.PROD.PERFORMANCE_NR_SOUND_RECORDING AS NR_SOUND_RECORDING ON NR_SOUND_RECORDING.ID=gus.SOUND_RECORDING_ID
  LEFT JOIN FACTS.PROD.NR_CONTRIBUTION_TYPE AS NR_CONT_TYPE ON NR_CONT_TYPE.NR_CONTRIBUTION_ID = NR_CONTRIBUTION.ID and nr_contribution.is_deleted is null
  ;;
```