# View: generated_income_summary_3

**View Name:** generated_income_summary_3
**Table Source:** `Derived from: Awal_Sax, Awal_Sax.Sax, Sax.Interested_Parties`
**File Path:** `views/generated_income_summary_3.view.lkml`

## Overview

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

## Dimensions

| Name | Type |
|------|------|
| `AGR_ID` | string |
| `AGR_Assignor` | string |
| `Contributor_ID` | string |
| `Contributor_Name` | string |
| `Payment_Date` | date |
| `Reconciliation_Date` | date |
| `WRC_ID` | string |
| `WRC_ISRC` | string |
| `WRC_Title` | string |
| `WRC_Version` | string |
| `WRC_Artist` | string |
| `Source_CMO` | string |
| `Territory` | string |
| `Rights_Type` | string |
| `SOURCE_ACC_CCY` | string |
| `DISTRIBUTED_ACC_CCY` | string |
| `SOURCE_GBP` | number |
| `DISTRIBUTED_GBP` | number |

## SQL Comments

- ,           ROY_PAY_SUMS_EXT.ROY_GEN_STEP_PERC_COMM_USED Client_Share
- AS "Commission ACC CCY"
- AS "WHT ACC CCY"
- '' Payment_Type
- AS "Usage Start Date"

## Derived Table

```sql
sql:
SELECT      AGREEMENTS.ID AGR_ID
,           CLIENTS.NAME AGR_Assignor
,           CONTRIBUTORS.ID Contributor_ID
,           CONTRIBUTORS.NAME Contributor_Name
,           ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYY-MM-DD' Payment_Date
,           RECONCILIATIONS.FIXED_DATE,'YYYY-MM-DD' Reconciliation_Date
,           RECORDINGS.ID WRC_ID
,           RECORDINGS.ISRC WRC_ISRC
,           RECORDINGS.TITLE WRC_Title
,           RECORDINGS.SUB_TITLE WRC_Version
,           RECORDING_ARTISTS.NAME WRC_Artist
,           SOURCES.NAME Source_CMO
,           TERRITORIES.NAME Territory
,           RIGHT_TYPES.DESCRIPTION Rights_Type
,           SUM(ROY_PAY_SUMS_EXT.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*ROY_PAY_SUMS_EXT.ACCOUNT_EXCHANGE_RATE) SOURCE_ACC_CCY
,           SUM(ROY_PAY_SUMS_EXT.WHT_ADJUSTED_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*ROY_PAY_SUMS_EXT.ACCOUNT_EXCHANGE_RATE) DISTRIBUTED_ACC_CCY
,           SUM(ROY_PAY_SUMS_EXT.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) SOURCE_GBP
,           SUM(ROY_PAY_SUMS_EXT.WHT_ADJUSTED_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) DISTRIBUTED_GBP
--,           ROY_PAY_SUMS_EXT.ROY_GEN_STEP_PERC_COMM_USED Client_Share
--          AS "Commission ACC CCY"
--          AS "WHT ACC CCY"
--           '' Payment_Type
--          AS "Usage Start Date"
--          AS "Usage End Date"
----------
----------
FROM        AWAL_SAX.SAX.INTERESTED_PARTIES CLIENTS
,           AWAL_SAX.SAX.INTERESTED_PARTIES SOURCES
,           AWAL_SAX.SAX.INTERESTED_PARTIES CONTRIBUTORS
,           AWAL_SAX.SAX.AGREEMENTS
--,           AWAL_SAX.SAX.ROYALTY_STATUSES
,           AWAL_SAX.SAX.RECORDINGS
,           AWAL_SAX.SAX.RECORDING_ARTISTS
,           AWAL_SAX.SAX.ROY_PAY_SUMS_EXT
-- ,           AWAL_SAX.SAX.ROYALTIES
,           AWAL_SAX.SAX.TERRITORIES
----
,           AWAL_SAX.SAX.RECONCILIATIONS
,           AWAL_SAX.SAX.EXCHANGE_RATES
,           AWAL_SAX.SAX.BATCHES
,           AWAL_SAX.SAX.RIGHT_TYPES
----
WHERE       1=1
AND         ROY_PAY_SUMS_EXT.ACCOUNT_ID    = AGREEMENTS.ID
-- AND         ROYALTY_PAYMENTS.ROYALTY_ID    = ROYALTIES.ID
AND         AGREEMENTS.ASSIGNOR_ID = CLIENTS.ID
AND         ROY_PAY_SUMS_EXT.ROYALTY_CHAIN_CONTRIBUTOR_ID = CONTRIBUTORS.ID
AND         ROY_PAY_SUMS_EXT.RIGHT_TYPE_ID = RIGHT_TYPES.ID
--
AND         ROY_PAY_SUMS_EXT.RECORDING_ID = RECORDINGS.ID
AND         ROY_PAY_SUMS_EXT.TERRITORY_ID = TERRITORIES.ID
--AND         ROYALTIES.IDENTIFICATION_STATUS_ID = ROYALTY_STATUSES.ID
AND         RECORDINGS.RECORDING_ARTIST_ID = RECORDING_ARTISTS.ID (+)
--
AND         ROY_PAY_SUMS_EXT.BATCH_ID      = BATCHES.ID
AND         BATCHES.RECONCILIATION_ID = RECONCILIATIONS.ID
AND         BATCHES.SOURCE_IP_ID = SOURCES.ID
--
AND         EXCHANGE_RATES.FROM_CURRENCY_ID = RECONCILIATIONS.PAYEE_CURRENCY_ID
AND         EXCHANGE_RATES.TO_CURRENCY_ID = 'CUR31'
AND         EXCHANGE_RATES.START_DATE <= ROY_PAY_SUMS_EXT.PAYMENT_DATE
AND         EXCHANGE_RATES.END_DATE >= ROY_PAY_SUMS_EXT.PAYMENT_DATE
--
--AND         AGREEMENTS.ID   = 'AGR453508'
AND         AGREEMENTS.OUTGOING_AGREEMENT_TYPE = 'Client'
AND         AGREEMENTS.CLIENT_RIGHTS    = 'Neighbouring Rights'
--AND         SOURCES.ID = 'IP60806'
--
AND         ROY_PAY_SUMS_EXT.PAYMENT_DATE    >=  TO_DATE('2020-09-01','YYYY-MM-DD')
AND         ROY_PAY_SUMS_EXT.PAYMENT_DATE    <   TO_DATE('2022-10-01','YYYY-MM-DD')
---------
GROUP BY AGREEMENTS.ID
,           CLIENTS.NAME
,           CONTRIBUTORS.ID
,           CONTRIBUTORS.NAME
,           ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYY-MM-DD'
,           RECONCILIATIONS.FIXED_DATE,'YYYY-MM-DD'
,           RECORDINGS.ID
,           RECORDINGS.ISRC
,           RECORDINGS.TITLE
,           RECORDINGS.SUB_TITLE
,           RECORDING_ARTISTS.NAME
,           SOURCES.NAME
,           TERRITORIES.NAME
,           RIGHT_TYPES.DESCRIPTION
--          AS 'Usage Start Date'
--          AS 'Usage End Date'
--,           ROY_PAY_SUMS_EXT.ROY_GEN_STEP_PERC_COMM_USED
--          AS "Commission ACC CCY"
--          AS "WHT ACC CCY"
--,           Payment_Type
----------------------------------------------------
----------------------------------------------------
union
----------------------------------------------------
----------------------------------------------------
SELECT      AGREEMENTS.ID AGR_ID
,           CLIENTS.NAME AGR_Assignor
,           CONTRIBUTORS.ID Contributor_ID -- added by B
,           CONTRIBUTORS.NAME Contributor_Name -- added by B
,           ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYY-MM-DD' Payment_Date
,           RECONCILIATIONS.FIXED_DATE,'YYYY-MM-DD' Reconciliation_Date -- added by B
,           RECORDINGS.ID WRC_ID
,           RECORDINGS.ISRC WRC_ISRC
,           RECORDINGS.TITLE WRC_Title
,           RECORDINGS.SUB_TITLE WRC_Version
,           RECORDING_ARTISTS.NAME WRC_Artist
,           SOURCES.NAME Source_CMO
,           TERRITORIES.NAME Territory
,           RIGHT_TYPES.DESCRIPTION Rights_Type
,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) SOURCE_ACC_CCY
,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) DISTRIBUTED_ACC_CCY
,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) SOURCE_GBP
,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) DISTRIBUTED_GBP
--           '' Client Share
--,           ROYALTY_ADJ_PAYMENTS.PAYMENT_TYPE Payment_Type
--          AS "Commission ACC CCY"
--          AS "WHT ACC CCY"
--          AS "Usage Start Date"
--          AS "Usage End Date"

----------
----------
FROM        AWAL_SAX.SAX.INTERESTED_PARTIES CLIENTS
,           AWAL_SAX.SAX.INTERESTED_PARTIES SOURCES
,           AWAL_SAX.SAX.INTERESTED_PARTIES CONTRIBUTORS
,           AWAL_SAX.SAX.AGREEMENTS
,           AWAL_SAX.SAX.ROYALTY_STATUSES
,           AWAL_SAX.SAX.RECORDINGS
,           AWAL_SAX.SAX.RECORDING_ARTISTS
,           AWAL_SAX.SAX.ROYALTY_ADJ_PAYMENTS
,           AWAL_SAX.SAX.ROYALTIES
,           AWAL_SAX.SAX.TERRITORIES
----
,           AWAL_SAX.SAX.RECONCILIATIONS
,           AWAL_SAX.SAX.EXCHANGE_RATES
,           AWAL_SAX.SAX.BATCHES
,           AWAL_SAX.SAX.RIGHT_TYPES
----
WHERE       1=1
AND         ROYALTY_ADJ_PAYMENTS.ACCOUNT_ID    = AGREEMENTS.ID
AND         ROYALTY_ADJ_PAYMENTS.ROYALTY_ID    = ROYALTIES.ID
AND         AGREEMENTS.ASSIGNOR_ID = CLIENTS.ID
--AND         ROYALTY_ADJ_PAYMENTS.ROYALTY_CHAIN_CONTRIBUTOR_ID = CONTRIBUTORS.ID
--
AND         ROYALTIES.RECORDING_ID = RECORDINGS.ID (+)
AND         ROYALTIES.TERRITORY_ID = TERRITORIES.ID
--AND         ROYALTIES.IDENTIFICATION_STATUS_ID = ROYALTY_STATUSES.ID
AND         RECORDINGS.RECORDING_ARTIST_ID = RECORDING_ARTISTS.ID (+)
--
AND         ROYALTIES.BATCH_ID      = BATCHES.ID
AND         BATCHES.RECONCILIATION_ID = RECONCILIATIONS.ID
AND         BATCHES.SOURCE_IP_ID = SOURCES.ID
--
AND         EXCHANGE_RATES.FROM_CURRENCY_ID = RECONCILIATIONS.PAYEE_CURRENCY_ID
AND         EXCHANGE_RATES.TO_CURRENCY_ID = 'CUR31'
AND         EXCHANGE_RATES.START_DATE <= ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE
AND         EXCHANGE_RATES.END_DATE >= ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE
--
--AND         AGREEMENTS.ID   = 'AGR453508'
AND         AGREEMENTS.OUTGOING_AGREEMENT_TYPE = 'Client'
AND         AGREEMENTS.CLIENT_RIGHTS    = 'Neighbouring Rights'
--AND         SOURCES.ID = 'IP60806'
--
AND         ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE    >=  TO_DATE('2020-09-01','YYYY-MM-DD')
AND         ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE    <   TO_DATE('2022-10-01','YYYY-MM-DD')
---------
GROUP BY    AGREEMENTS.ID
,           CLIENTS.NAME
,           CONTRIBUTORS.ID
,           CONTRIBUTORS.NAME
,           ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYY-MM-DD'
,           RECONCILIATIONS.FIXED_DATE,'YYYY-MM-DD'
,           RECORDINGS.ID
,           RECORDINGS.ISRC
,           RECORDINGS.TITLE
,           RECORDINGS.SUB_TITLE
,           RECORDING_ARTISTS.NAME
,           SOURCES.NAME
,           TERRITORIES.NAME
,           RIGHT_TYPES.DESCRIPTION
--          AS "Usage Start Date"
--          AS "Usage End Date"
--          AS "Commission ACC CCY"
--          AS "WHT ACC CCY"
--,           ROYALTY_ADJ_PAYMENTS.PAYMENT_TYPE
;;
```