# View: unified_income_summary_performer

**View Name:** unified_income_summary_performer
**Table Source:** `Derived from: Dbt_Prod_Knr.Historic_Generated_Income_Summary_Account_Currency, Dbt_Prod_Knr.Historic_Generated_Income_Summary_Usd, Prod.Dim_Transactiontype, Prod.Performance_Nr_Contributor, Prod.Stmt_Db_Sales_Nr + 3 more`
**File Path:** `views/unified_income_summary_performer.view.lkml`

## Overview

- **File Size:** 12087 bytes
- **Lines of Code:** 349
- **Dimensions:** 2
- **Measures:** 12
- **Dimension Groups:** 2
- **Filters:** 0

## Comments & Notes

- --- PAYEE CURRENCY ---
- --- USD ---

## Dimensions

| Name | Type |
|------|------|
| `row_id` | string |
| `data_source` | number |

## Measures

| Name | Type |
|------|------|
| `min_statement_year` | min |
| `max_statement_year` | max |
| `total_gross_revenue_payee_currency` | sum |
| `total_withholding_tax_payee_currency` | sum |
| `total_gross_revenue_post_wht_payee_currency` | sum |
| `total_net_share_payee_currency` | sum |
| `total_commission_value_payee_currency` | number |
| `total_gross_revenue_usd` | sum |
| `total_withholding_tax_usd` | sum |
| `total_gross_revenue_post_wht_usd` | sum |
| `total_net_share_usd` | sum |
| `total_commission_value_usd` | number |

## Dimension Groups

| Name | Type |
|------|------|
| `usage_start` | time |
| `usage_end` | time |

## SQL Comments

- NORMALISED SAX BASE
- USD AGGREGATED TO SAFE GRAIN
- FINAL JOIN WITH PRO-RATA ALLOCATION
- =========================
- ABACUS

## Derived Table

```sql
sql:

      WITH contributor_crosswalk AS (
        SELECT
          TO_VARCHAR(EXTERNAL_ID) AS sax_contributor_id,
          TO_VARCHAR(ID) AS abacus_contributor_id
        FROM FACTS.PROD.PERFORMANCE_NR_CONTRIBUTOR
        WHERE EXTERNAL_ID IS NOT NULL
      ),

      -- NORMALISED SAX BASE
      base_sax AS (
      SELECT
      sax.*,
      COALESCE(TO_DATE(sax.USAGE_START_DATE), DATE '1900-01-01') AS norm_usage_start_date,
      COALESCE(TO_DATE(sax.USAGE_END_DATE), DATE '1900-01-01') AS norm_usage_end_date,
      UPPER(TRIM(sax.RIGHT_TYPE)) AS norm_right_type
      FROM INTELLIGENCE.DBT_PROD_KNR.HISTORIC_GENERATED_INCOME_SUMMARY_ACCOUNT_CURRENCY sax
      ),

      -- USD AGGREGATED TO SAFE GRAIN
      base_usd AS (
      SELECT
      AGR_ID,
      AGR_ASSIGNOR_ID,
      CONTRIBUTOR_ID,
      WRC_ID,
      PAYMENT_DATE,

      COALESCE(TO_DATE(USAGE_START_DATE), DATE '1900-01-01') AS norm_usage_start_date,
      COALESCE(TO_DATE(USAGE_END_DATE), DATE '1900-01-01') AS norm_usage_end_date,
      UPPER(TRIM(RIGHT_TYPE)) AS norm_right_type,

      SUM(SOURCE_AMOUNT_USD) AS total_usd_gross,
      SUM(WHT_AMOUNT_USD) AS total_usd_wht,
      SUM(WHT_ADJUSTED_RECEIVED_AMOUNT_USD) AS total_usd_post_wht,
      SUM(DISTRIBUTED_AMOUNT_USD) AS total_usd_net

      FROM INTELLIGENCE.DBT_PROD_KNR.HISTORIC_GENERATED_INCOME_SUMMARY_USD
      GROUP BY
      AGR_ID,
      AGR_ASSIGNOR_ID,
      CONTRIBUTOR_ID,
      WRC_ID,
      PAYMENT_DATE,
      norm_usage_start_date,
      norm_usage_end_date,
      norm_right_type
      ),

      -- FINAL JOIN WITH PRO-RATA ALLOCATION
      joined_sax_history AS (
      SELECT
      s.*,

      (s.SOURCE_AMOUNT_ACC_CCY / NULLIF(SUM(s.SOURCE_AMOUNT_ACC_CCY) OVER (
      PARTITION BY s.AGR_ID, s.AGR_ASSIGNOR_ID, s.CONTRIBUTOR_ID, s.PAYMENT_DATE, COALESCE(s.WRC_ID, 'NULL'), s.norm_usage_start_date, s.norm_usage_end_date, s.norm_right_type
      ), 0)) * u.total_usd_gross AS gross_revenue_usd_allocated,

      (s.WHT_AMOUNT_ACC_CCY / NULLIF(SUM(s.WHT_AMOUNT_ACC_CCY) OVER (
      PARTITION BY s.AGR_ID, s.AGR_ASSIGNOR_ID, s.CONTRIBUTOR_ID, s.PAYMENT_DATE, COALESCE(s.WRC_ID, 'NULL'), s.norm_usage_start_date, s.norm_usage_end_date, s.norm_right_type
      ), 0)) * u.total_usd_wht AS wht_usd_allocated,

      (s.WHT_ADJUSTED_RECEIVED_AMOUNT_ACC_CCY / NULLIF(SUM(s.WHT_ADJUSTED_RECEIVED_AMOUNT_ACC_CCY) OVER (
      PARTITION BY s.AGR_ID, s.AGR_ASSIGNOR_ID, s.CONTRIBUTOR_ID, s.PAYMENT_DATE, COALESCE(s.WRC_ID, 'NULL'), s.norm_usage_start_date, s.norm_usage_end_date, s.norm_right_type
      ), 0)) * u.total_usd_post_wht AS gross_post_wht_usd_allocated,

      (s.DISTRIBUTED_AMOUNT_ACC_CCY / NULLIF(SUM(s.DISTRIBUTED_AMOUNT_ACC_CCY) OVER (
      PARTITION BY s.AGR_ID, s.AGR_ASSIGNOR_ID, s.CONTRIBUTOR_ID, s.PAYMENT_DATE, COALESCE(s.WRC_ID, 'NULL'), s.norm_usage_start_date, s.norm_usage_end_date, s.norm_right_type
      ), 0)) * u.total_usd_net AS net_share_usd_allocated

      FROM base_sax s
      LEFT JOIN base_usd u
      ON s.AGR_ID = u.AGR_ID
      AND s.AGR_ASSIGNOR_ID = u.AGR_ASSIGNOR_ID
      AND s.CONTRIBUTOR_ID = u.CONTRIBUTOR_ID
      AND s.PAYMENT_DATE = u.PAYMENT_DATE
      AND COALESCE(s.WRC_ID, 'NULL') = COALESCE(u.WRC_ID, 'NULL')
      AND s.norm_usage_start_date = u.norm_usage_start_date
      AND s.norm_usage_end_date = u.norm_usage_end_date
      AND COALESCE(s.norm_right_type, 'NULL') = COALESCE(u.norm_right_type, 'NULL')
      )

      -- =========================
      -- ABACUS
      -- =========================
      SELECT
      'ABACUS' AS DATA_SOURCE,
      asp.statement_period_name,
      aspo.statement_year,
      aspo.statement_month,
      corx.start_date AS Usage_Start_Date,
      corx.end_date AS Usage_End_Date,
      TO_VARCHAR(corx.account_id) AS account_id,
      a.account_name,
      TO_VARCHAR(corx.contract_id) AS contract_id,
      c.contract_name,
      TO_VARCHAR(corx.contributor_id) AS contributor_id,
      corx.contributor_name,
      corx.sound_recording_id,
      sr.main_artist,
      sr.recording_title,
      sr.version AS recording_version,
      corx.isrc,
      cmm.customer_name AS society_name,
      cn.name AS country_name,
      t.transactiontypedesc AS trans_type,
      corx.transaction_subtype_description AS trans_sub_type,
      CAST(corx.royalty_rate AS FLOAT) AS royalty_rate,
      corx.account_payee_currency AS payee_currency,
      corx.gross_revenue_payee_currency AS GROSS_REVENUE_PAYEE_CURRENCY,
      corx.withholding_tax_payee_currency AS WITHHOLDING_TAX_PAYEE_CURRENCY,
      corx.gross_revenue_after_withholding_tax_payee_currency AS GROSS_REVENUE_POST_WHT_PAYEE_CURRENCY,
      corx.net_share_payee_currency AS NET_SHARE_PAYEE_CURRENCY,
      (corx.gross_revenue_sale_currency * txn.activity_rate) AS GROSS_REVENUE_USD,
      (corx.withholding_tax_sale_currency * txn.activity_rate) AS WITHHOLDING_TAX_USD,
      (corx.gross_revenue_after_withholding_tax_sale_currency * txn.activity_rate) AS GROSS_REVENUE_POST_WHT_USD,
      (corx.net_share_sale_currency * txn.activity_rate) AS NET_SHARE_USD,
      CONCAT('ABACUS|', corx.account_id, '|', corx.contract_id, '|', corx.contributor_id, '|', corx.sound_recording_id, '|', asp.statement_period_name) AS row_id

      FROM royalty_accounting.prod.vw_abacus_fact_sales_nr_v3 corx
      JOIN royalty_accounting.prod.stmt_db_sales_nr txn
      ON corx.txn_id = txn.stmt_db_sales_nr_txn_id
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract c
      ON corx.contract_id = c.contract_id
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account a
      ON corx.account_id = a.account_id
      JOIN facts.prod.dim_transactiontype 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
      LEFT JOIN facts.prod.performance_nr_sound_recording sr
      ON sr.id = txn.sound_recording_id
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period asp
      ON corx.statement_period_id = asp.statement_period_id
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period aspo
      ON corx.original_statement_period_id = aspo.statement_period_id

      UNION ALL

      -- =========================
      -- SAX
      -- =========================
      SELECT
      'SAX' AS DATA_SOURCE,

      INITCAP(TO_VARCHAR(sax.payment_date, 'MMMM YYYY')),
      EXTRACT(YEAR FROM sax.payment_date),
      EXTRACT(MONTH FROM sax.payment_date),

      TRY_TO_DATE(sax.usage_start_date),
      TRY_TO_DATE(sax.usage_end_date),

      TO_VARCHAR(sax.agr_assignor_id),
      sax.agr_assignor,

      TO_VARCHAR(sax.agr_id),
      sax.rights_description,

      TO_VARCHAR(COALESCE(xwalk.abacus_contributor_id, sax.contributor_id)),
      sax.contributor_name,

      sax.wrc_id,
      sax.wrc_artist,
      sax.wrc_title,
      sax.wrc_version,
      sax.wrc_isrc,

      sax.source_cmo,
      sax.territory_name,

      sax.right_type,
      NULL,

      CAST((1.00 - (ABS(sax.commission_rate) / 100.0)) AS FLOAT),

      sax.acc_currency AS payee_currency,
      sax.source_amount_acc_ccy AS GROSS_REVENUE_PAYEE_CURRENCY,
      sax.wht_amount_acc_ccy AS WITHHOLDING_TAX_PAYEE_CURRENCY,
      sax.wht_adjusted_received_amount_acc_ccy AS GROSS_REVENUE_POST_WHT_PAYEE_CURRENCY,
      sax.distributed_amount_acc_ccy AS NET_SHARE_PAYEE_CURRENCY,

      sax.gross_revenue_usd_allocated AS GROSS_REVENUE_USD,
      sax.wht_usd_allocated AS WITHHOLDING_TAX_USD,
      sax.gross_post_wht_usd_allocated AS GROSS_REVENUE_POST_WHT_USD,
      sax.net_share_usd_allocated AS NET_SHARE_USD,

      CONCAT(
      'SAX|',
      sax.agr_assignor_id, '|',
      sax.agr_id, '|',
      sax.contributor_id, '|',
      COALESCE(sax.wrc_id, 'NULL'), '|',
      sax.payment_date, '|',
      UUID_STRING()
      ) AS row_id

      FROM joined_sax_history sax
      LEFT JOIN contributor_crosswalk xwalk
      ON TO_VARCHAR(sax.contributor_id) = xwalk.sax_contributor_id
      ;;
```