# View: awal_nr_royalties

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

## Overview

- **File Size:** 5480 bytes
- **Lines of Code:** 155
- **Dimensions:** 18
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Statement_Period_ID` | string |
| `Statement_Period_Name` | string |
| `Accounting_Run_ID` | string |
| `Accounting_Run_Name` | string |
| `Account_ID` | string |
| `Account_Name` | string |
| `Contract_ID` | string |
| `Contract_Name` | string |
| `reference_sap_profit_center_id` | number |
| `Country_Name` | string |
| `Store` | string |
| `Term_Rate` | string |
| `Payee_Currency` | string |
| `Total_Gross_Payee_Currency` | number |
| `Total_WHT_Payee_Currency` | number |
| `Total_Gross_After_WHT_Payee_Currency` | number |
| `Net_Revenue_Payee_Currency` | number |
| `Total_Distribution_Fee_Payee_Currency` | number |

## SQL Comments

- Payee Currency
- contract
- filter out any KNR label contracts
- account
- contract conditions

## Derived Table

```sql
sql:
    select
    asp.statement_period_id Statement_Period_ID,
    asp.statement_period_name Statement_Period_Name,
    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,
    rse.reference_sap_profit_center_id,
    cn.iso3166a3 Country_Name,
    cmm.customer_name Store,
    arr.term_rate Term_Rate,
-- Payee Currency
    apt.currency_code as Payee_Currency,
    round(sum(arr.gross_revenue_payee_currency),5) Total_Gross_Payee_Currency,
    round(sum(arr.withholding_tax_payee_currency),5) Total_WHT_Payee_Currency,
    round(sum(arr.gross_revenue_after_withholding_tax_payee_currency),5) Total_Gross_After_WHT_Payee_Currency,
    round(sum(arr.net_revenue_payee_currency),5) Net_Revenue_Payee_Currency,
    round(sum(arr.distribution_fee_payee_currency),5) Total_Distribution_Fee_Payee_Currency
-- 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 KNR label contracts
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_signing_entity as rse
    on rse.reference_signing_entity_id = c.reference_signing_entity_id
-- 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 FACTS.PROD.DIM_RELEASE AS DR ON dr.releaseid=txn.UPC
  LEFT JOIN FACTS.PROD.DIM_ARTIST AS DA ON da.artistid=dr.artistid
  LEFT JOIN FACTS.PROD.DIM_TRACK AS DT ON dt.upc=txn.upc and dt.isrc=txn.isrc
--statement period
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 txn.transaction_type in ('RC','EH','PX','CC','CX','ER','RW','MA','MD','PA','PV','RR','PP','RL','SL','TV','UP','UN','NC')
--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 = 'gbkpl1356311'
--and cts.contract_id = 535896
--and arr.accounting_run_id=178
group by 1,2,3,4,5,6,7,8,9,10,11,12,13
order by account_id,contract_id;;
```