# View: Finance_Team_Performer_Royalties

**View Name:** Finance_Team_Performer_Royalties
**Table Source:** `Derived from: Prod.Stmt_Db_Sales_Nr, Prod.Vw_Abacus_Fact_Sales_Nr_V3, Prod.Vw_Dim_Abacus_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Account, Prod_Royalty_Accounting_Royalty_Accounting.Contract + 3 more`
**File Path:** `views/Finance_Team_Performer_Royalties.view.lkml`

## Overview

- **File Size:** 6586 bytes
- **Lines of Code:** 210
- **Dimensions:** 17
- **Measures:** 10
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Statement_Period_ID` | string |
| `Statement_Period_Name` | string |
| `Original_Statement_Period_ID` | string |
| `Original_Statement_Period_Name` | string |
| `ABACUS_Sale_Type` | string |
| `Accounting_Run_ID` | string |
| `Account_ID` | string |
| `Account_Name` | string |
| `Contract_ID` | string |
| `Contract_Name` | string |
| `Store_ID` | string |
| `Store` | string |
| `Country_ID` | string |
| `Country_Name` | string |
| `Royalty_Rate` | number |
| `Payee_Currency` | string |
| `Sale_Currency` | string |

## Measures

| Name | Type |
|------|------|
| `Total_Gross_Payee_Currency` | sum |
| `Total_WHT_Payee_Currency` | sum |
| `Total_Gross_After_WHT_Payee_Currency` | sum |
| `Net_Revenue_Payee_Currency` | sum |
| `Total_Distribution_Fee_Payee_Currency` | sum |
| `Total_Gross_Sale_Currency` | sum |
| `Total_WHT_Sale_Currency` | sum |
| `Total_Gross_After_WHT_Sale_Currency` | sum |
| `Net_Revenue_Sale_Currency` | sum |
| `Total_Distribution_Fee_Sale_Currency` | sum |

## SQL Comments

- statement details--
- account, contract, contributor details --
- royalty details --
- payee currency
- filter out any contracts not KNR label

## Derived Table

```sql
sql:
    select
--statement details--
    corx.STATEMENT_PERIOD_ID,
    asp.statement_period_name,
    corx.ORIGINAL_STATEMENT_PERIOD_ID,
    aspo.statement_period_name  Original_Statement_Period_Name,
    corx.ABACUS_SALE_TYPE,
    corx.accounting_run_id,
--account, contract, contributor details --
    corx.account_id,
    a.account_name,
    corx.contract_id,
    c.contract_name,
-- royalty details --
    corx.STORE_ID,
    cmm.customer_name store,
    corx.country_id,
    cn.name country_name,
    ac.company_code,
    corx.royalty_rate,
    corx.sale_currency_code as sales_currency,
        corx.ACCOUNT_PAYEE_CURRENCY as payee_currency,
    round(sum(corx.GROSS_REVENUE_sale_CURRENCY),5) Total_Gross_sale_Currency,
    round(sum(corx.withholding_tax_sale_currency),5) total_WHT_sale_currency,
    round(sum(corx.gross_revenue_after_withholding_tax_sale_currency),5) total_gross_after_WHT_sale_currency,
    round(sum(corx.NET_SHARE_sale_CURRENCY),5) net_revenue_sale_currency,
    round(-(sum(corx.gross_revenue_after_withholding_tax_sale_currency-corx.net_share_sale_currency)),5) total_distribution_fee_sale_currency,
--payee currency
    round(sum(corx.GROSS_REVENUE_PAYEE_CURRENCY),5) Total_Gross_Payee_Currency,
    round(sum(corx.withholding_tax_payee_currency),5) total_WHT_payee_currency,
    round(sum(corx.gross_revenue_after_withholding_tax_payee_currency),5) total_gross_after_WHT_payee_currency,
    round(sum(corx.NET_SHARE_PAYEE_CURRENCY),5) net_revenue_payee_currency,
    round(-(sum(corx.gross_revenue_after_withholding_tax_payee_currency-corx.net_share_payee_currency)),5) total_distribution_fee_payee_currency
-- sales
from royalty_accounting.prod.vw_abacus_fact_sales_nr_v3 as corx
join royalty_accounting.prod.stmt_db_sales_nr as txn on corx.txn_id = txn.stmt_db_sales_nr_txn_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract as c on corx.contract_id = c.contract_id
join ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_CONTRACT ac on c.contract_id = ac.contract_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term_condition as ctc on corx.contract_term_condition_id = ctc.contract_term_condition_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term as ct on ctc.contract_term_id = ct.contract_term_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=6 -- filter out any contracts not KNR label
-- account
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account as a on corx.account_id = a.account_id
-- contract conditions
join facts.prod.dim_transactiontype as t on corx.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=corx.store_id
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cn on cn.id=corx.country_id
--- sound recordings
left join facts.prod.performance_nr_sound_recording sr on sr.id=txn.sound_recording_id
--statement period
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period as asp on corx.statement_period_id = asp.statement_period_id
join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period as aspo on corx.ORIGINAL_STATEMENT_PERIOD_ID = aspo.statement_period_id
-- predicates

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18
    ;;
```