# View: unified_income_summary

**View Name:** unified_income_summary
**Table Source:** `Derived from: Dbt_Prod_Knr.Historic_Generated_Income_Summary_Account_Currency, Prod.Stmt_Db_Sales_Nr, Prod.Vw_Abacus_Fact_Sales_Nr_V3, Prod_Royalty_Accounting_Royalty_Accounting.Contract, Prod_Royalty_Accounting_Royalty_Accounting.Contract_Term + 3 more`
**File Path:** `views/unified_income_summary.view.lkml`

## Overview

- **File Size:** 7672 bytes
- **Lines of Code:** 214
- **Dimensions:** 14
- **Measures:** 4
- **Dimension Groups:** 2
- **Filters:** 0

## Comments & Notes

- Keeps performance lightning-fast by caching the unified table in Snowflake
- =========================================================================
- DIMENSIONS (What users group/filter by)

## Dimensions

| Name | Type |
|------|------|
| `data_source` | string |
| `account_id` | string |
| `account_name` | string |
| `contract_id` | string |
| `contract_name` | string |
| `main_artist` | string |
| `recording_title` | string |
| `isrc` | string |
| `payee_currency` | string |
| `trans_type` | string |
| `trans_sub_type` | string |
| `statement_period_name` | string |
| `statement_year` | number |
| `statement_month` | number |

## Measures

| Name | Type |
|------|------|
| `total_gross_revenue` | sum |
| `total_withholding_tax` | sum |
| `total_gross_revenue_post_wht` | sum |
| `total_net_share` | sum |

## Dimension Groups

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

## SQL Comments

- SECTION 1: THE ACTIVE ABACUS INCOME LAYER
- SECTION 2: THE HISTORICAL SAX LAYER (WITH MANAGER'S CALCULATIONS)
- Inverting commission rate to client share

## Derived Table

```sql
# Keeps performance lightning-fast by caching the unified table in Snowflake
    datagroup_trigger: daily_datagroup

    sql:
      WITH max_historic_date AS (
          SELECT MAX(PAYMENT_DATE) AS max_date
          FROM INTELLIGENCE.DBT_PROD_KNR.HISTORIC_GENERATED_INCOME_SUMMARY_ACCOUNT_CURRENCY
      )

      -- SECTION 1: THE ACTIVE ABACUS INCOME LAYER
      SELECT
      NULL AS STATEMENT_PERIOD_ID,
      asp.statement_period_name,
      NULL AS ORIGINAL_STATEMENT_PERIOD_ID,
      aspo.statement_period_name AS ORIGINAL_STATEMENT_PERIOD_NAME,
      aspo.statement_year AS YEAR,
      aspo.statement_month AS MONTH,
      corx.start_date AS Usage_Start_Date,
      corx.end_date AS Usage_End_Date,
      'ABACUS' AS DATA_SOURCE,
      corx.account_id,
      a.account_name,
      corx.contract_id,
      c.contract_name,
      corx.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,
      corx.royalty_rate,
      corx.ACCOUNT_PAYEE_CURRENCY AS payee_currency,
      corx.GROSS_REVENUE_PAYEE_CURRENCY,
      corx.withholding_tax_payee_currency,
      corx.gross_revenue_after_withholding_tax_payee_currency,
      corx.NET_SHARE_PAYEE_CURRENCY
      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.contract_term_condition ctc ON corx.contract_term_condition_id = ctc.contract_term_condition_id
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term 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
      LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.schedule sh ON corx.schedule_id = sh.schedule_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

      -- SECTION 2: THE HISTORICAL SAX LAYER (WITH MANAGER'S CALCULATIONS)
      SELECT
      NULL AS STATEMENT_PERIOD_ID,
      TO_VARCHAR((SELECT max_date FROM max_historic_date), 'YYYY-MM') AS statement_period_name,
      NULL AS ORIGINAL_STATEMENT_PERIOD_ID,
      NULL AS ORIGINAL_STATEMENT_PERIOD_NAME,
      EXTRACT(YEAR FROM (SELECT max_date FROM max_historic_date)) AS YEAR,
      EXTRACT(MONTH FROM (SELECT max_date FROM max_historic_date)) AS MONTH,
      TRY_TO_DATE(sax.USAGE_START_DATE) AS Usage_Start_Date,
      TRY_TO_DATE(sax.USAGE_END_DATE) AS Usage_End_Date,
      'SAX' AS DATA_SOURCE,
      sax.AGR_ASSIGNOR_ID AS account_id,
      sax.AGR_ASSIGNOR AS account_name,
      sax.AGR_ID AS contract_id,
      sax.RIGHTS_DESCRIPTION AS contract_name,
      sax.CONTRIBUTOR_ID AS contributor_id,
      sax.CONTRIBUTOR_NAME AS CONTRIBUTOR_NAME,
      sax.WRC_ID AS sound_recording_id,
      sax.WRC_ARTIST AS main_artist,
      sax.WRC_TITLE AS recording_title,
      sax.WRC_VERSION AS recording_version,
      sax.WRC_ISRC AS isrc,
      sax.SOURCE_CMO AS Society_Name,
      sax.TERRITORY_NAME AS country_name,
      sax.RIGHT_TYPE AS trans_type,
      NULL AS trans_sub_type,
      (1.00 - sax.COMMISSION_RATE) AS royalty_rate, -- Inverting commission rate to client share
      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_after_withholding_tax_payee_currency,
      sax.DISTRIBUTED_AMOUNT_ACC_CCY AS NET_SHARE_PAYEE_CURRENCY
      FROM INTELLIGENCE.DBT_PROD_KNR.HISTORIC_GENERATED_INCOME_SUMMARY_ACCOUNT_CURRENCY sax
      ;;
```