# View: new_societies_usd_labels

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

## Overview

- **File Size:** 9130 bytes
- **Lines of Code:** 301
- **Dimensions:** 38
- **Measures:** 2
- **Dimension Groups:** 2
- **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 |
| `Rate` | string |
| `Quantity` | number |
| `TXN_ID` | string |
| `Unit_price_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 |

## Measures

| Name | Type |
|------|------|
| `Total_USD` | sum |
| `WHT_USD` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `Statement_Date` | time |
| `Payment_Date` | time |

## SQL Comments

- txn.batch_id SH_BATCH_ID,
- asp.statement_period_id Statement_Period_ID,
- arr.accounting_run_id Accounting_Run_ID,
- arr.accounting_run_name Accounting_Run_Name,
- cts.account_id Account_ID,

## Derived Table

```sql
sql:
     select
        --txn.batch_id SH_BATCH_ID,
        --asp.statement_period_id Statement_Period_ID,
        asp.statement_period_name Statement_Period,
        DATE('01/'|| asp.statement_month || '/'|| asp.statement_year) Statement_Date,
        --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,
        --(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') Recording_Artist,
        --txn.track_name Recording_Title,
        --case when tr.version is null then '' else tr.version end Recording_Version,
        --txn.start_date Start_Date,
        --DATE(txn.transaction_date, 'YYYY-MM-DD') Payment_Date,
        arr.term_rate Rate,
    -- Slaughter House Data
        --txn.quantity Quantity,
        --arr.txn_id TXN_ID,
        --txn.UNIT_PRICE_USD Unit_price_USD,
        sum(txn.total_usd) Total_USD,
        sum(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,
       --sum(arr.gross_revenue_sale_currency) Source_Gross_Revenue,
        --sum(arr.withholding_tax_sale_currency) Source_WHT,
        --sum(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
join orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta on tr.id = ta.track_id
--  JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON ai.artist_id=dr.artist_id
--  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 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
    group by 1,2,3,4,5;;
```