# View: New_Income_Summary_Label_Test

**View Name:** New_Income_Summary_Labels_Test
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Track_Artist, Prod.Accounting_Run_Results_Distro, Prod.Contract_Transaction_Staging, Prod.Stmt_Db_Sales_Distro, Prod_Royalty_Accounting_Royalty_Accounting.Account + 3 more`
**File Path:** `views/New_Income_Summary_Label_Test.view.lkml`

## Overview

- **File Size:** 8737 bytes
- **Lines of Code:** 301
- **Dimensions:** 40
- **Measures:** 3
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Accounting_Run_ID` | string |
| `Accounting_Run_Name` | string |
| `Account_ID` | string |
| `Account_Name` | string |
| `Contract_ID` | string |
| `Contract_Name` | string |
| `Batch_ID` | string |
| `SH_BATCH_ID` | string |
| `Statement_Period_ID` | string |
| `Statement_Period` | string |
| `ISRC` | string |
| `UPC` | string |
| `Country_ID` | string |
| `Country_Name` | string |
| `Society_ID` | string |
| `Source_CMO` | string |
| `txn_type_abbrev` | string |
| `txn_type` | string |
| `Recording_Artist` | string |
| `Recording_Title` | string |
| `Recording_Version` | string |
| `Start_Date` | date |
| `End_Date` | date |
| `Rate` | string |
| `Quantity` | number |
| `TXN_ID` | string |
| `Unit_price_USD` | number |
| `WHT_USD` | number |
| `Activity_Exchange_Rate` | number |
| `Sale_Currency` | string |
| `Source_Unite_Price` | number |
| `Source_Gross_Revenue` | number |
| `Source_WHT` | number |
| `Payee_Currency` | string |
| `Payee_Gross_Revenue` | number |
| `Withholding_Tax_Payee_Currency` | number |
| `Net_Revenue_Payee_Currency` | number |
| `Payee_Distribution_Fee` | number |
| `Statement_Year` | string |
| `Statement_Month` | string |

## Measures

| Name | Type |
|------|------|
| `Total_USD` | sum |
| `Source_Gross_Revenue_After_WHT` | sum |
| `Gross_Revenue_After_Withholding_Tax_Payee_Currency` | sum |

## SQL Comments

- Slaughter House Data
- Sale Currency
- Payee Currency
- contract
- filter out any contracts not KNR label

## Derived Table

```sql
sql:
    select
        txn.batch_id SH_BATCH_ID,
        asp.statement_period_id Statement_Period_ID,
        asp.statement_period_name Statement_Period,
        asp.statement_year,
        asp.statement_month,
        arr.accounting_run_id Accounting_Run_ID,
        arr.accounting_run_name Accounting_Run_Name,
        cts.account_id Account_ID,
        a.account_name Account_Name,
        cts.contract_id Contract_ID,
        c.contract_name Contract_Name,
        txn.isrc ISRC,
        txn.upc UPC,
        txn.country_id Country_ID,
        cn.name Country_Name,
        txn.store_id Society_ID,
        cmm.customer_name Source_CMO,
        txn.transaction_type txn_type_abbrev,
        t.transactiontypedesc txn_type,
           case when (select listagg (ta.name,', ') from orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta where tr.id = ta.track_id and ta.type = 'performer') is null then '' else (select listagg (ta.name,', ') from orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta where tr.id = ta.track_id and ta.type = 'performer') end as Recording_Artist,
        tr.track_name Recording_Title,
        case when tr.version is null then '' else tr.version end Recording_Version,
        txn.start_date Start_Date,
        txn.transaction_date End_Date,
        arr.term_rate Rate,
    -- Slaughter House Data
        txn.quantity Quantity,
        arr.txn_id TXN_ID,
        txn.UNIT_PRICE_USD Unit_price_USD,
        txn.total_usd Total_USD,
        txn.withholding_tax_usd WHT_USD,
        txn.activity_rate Activity_Exchange_Rate,
    -- Sale Currency
        txn.sale_currency_code Sale_Currency,
        arr.unit_price_sale_currency Source_Unite_Price,
        arr.gross_revenue_sale_currency Source_Gross_Revenue,
        arr.withholding_tax_sale_currency Source_WHT,
        arr.gross_revenue_after_withholding_tax_sale_currency Source_Gross_Revenue_After_WHT,
    -- Payee Currency
        apt.currency_code Payee_Currency,
        arr.gross_revenue_payee_currency Payee_Gross_Revenue,
        arr.withholding_tax_payee_currency Withholding_Tax_Payee_Currency,
        arr.gross_revenue_after_withholding_tax_payee_currency Gross_Revenue_After_Withholding_Tax_Payee_Currency,
        arr.net_revenue_payee_currency Net_Revenue_Payee_Currency,
        arr.distribution_fee_payee_currency Payee_Distribution_Fee
    -- sales
 from royalty_accounting.prod.accounting_run_results_distro as arr
join royalty_accounting.prod.stmt_db_sales_distro as txn on arr.txn_id = txn.stmt_db_sales_distro_txn_id
-- contract
join royalty_accounting.prod.contract_transaction_staging as cts on arr.txn_id = cts.txn_id and cts.accounting_run_id=arr.accounting_run_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract as c on cts.contract_id = c.contract_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.run_controller_contract rcc
    on rcc.contract_id=c.contract_id
and rcc.run_controller_id=11 -- filter out any contracts not KNR label
-- account
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account as a on cts.account_id = a.account_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_payment_term as apt on a.account_id = apt.account_id
-- contract conditions
join facts.prod.dim_transactiontype as t on txn.transaction_type = t.transactiontypeabbr
left join orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm on cmm.customer_master_master_id=txn.store_id
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cn on cn.id=txn.country_id
--track
left join orchard_app_reporting_v2.art_relations_prod_art_relations.track tr on txn.upc = tr.upc and txn.isrc = tr.isrc

      join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.accounting_run as ari on arr.accounting_run_id = ari.accounting_run_id
      join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.accounting_period as api on ari.accounting_period_id = api.accounting_period_id
      join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period as asp on api.statement_period_id = asp.statement_period_id
      -- predicates
      where true
      --and arr.accounting_run_id=$accounting_run_id
      --and arr.mechanical_fee_sale_currency != 0
      --and arr.ringtone_fee_sale_currency != 0
      --and txn.transaction_type = 'as'
      --and txn.quantity < 0
      --and ctry.countryname = 'usa'
      --and txn.isrc in ('USC4R0920571','USC4R0817634','USFI87000060','USFI88000235')
      --and cts.contract_id = 535946
      --and arr.accounting_run_id=157
      order by account_id,contract_id;;
```