# View: New_Income_Summary_AWAL_NR

**View Name:** New_Income_Summary_AWAL_NR
**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_AWAL_NR.view.lkml`

## Overview

- **File Size:** 9292 bytes
- **Lines of Code:** 290
- **Dimensions:** 41
- **Measures:** 0
- **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 |
| `Total_USD` | number |
| `WHT_USD` | number |
| `Activity_Exchange_Rate` | string |
| `Sale_Currency` | string |
| `Source_Unite_Price` | number |
| `Source_Gross_Revenue` | number |
| `Source_WHT` | number |
| `Source_Gross_Revenue_After_WHT` | number |
| `Payee_Currency` | string |
| `Payee_Gross_Revenue` | number |
| `Withholding_Tax_Payee_Currency` | number |
| `Gross_Revenue_After_Withholding_Tax_Payee_Currency` | number |
| `Net_Revenue_Payee_Currency` | number |
| `Payee_Distribution_Fee` | number |

## 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,
              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 not in ('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
      and txn.transaction_type in ('RC','EH','PX','CC','CX','ER','RW','MA','MD','PA','PV','RR','PP','RL','SL','TV','UP','UN','NC')
       order by account_id,contract_id;;
```