# View: Income_by_client_Roys_and_Roy_Adj

**View Name:** income_by_client_Roys_and_Roy_Adj
**Table Source:** `Derived Table (Custom SQL)`
**File Path:** `views/Income_by_client_Roys_and_Roy_Adj.view.lkml`

## Overview

- **File Size:** 7015 bytes
- **Lines of Code:** 160
- **Dimensions:** 11
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `CLIENT_ID` | string |
| `CLIENT` | string |
| `AGR_ID` | string |
| `PERIOD` | string |
| `STATEMENT_DESC` | string |
| `TERRITORY` | string |
| `SOURCE` | string |
| `SOURCE_ACC_CCY` | string |
| `DISTRIBUTED_ACC_CCY` | string |
| `SOURCE_GBP` | string |
| `DISTRIBUTED_GBP` | string |

## SQL Comments

- -------
- --------------------------------------------------
- --------------------------------------------------
- --------------------------------------------------
- --------------------------------------------------

## Derived Table

```sql
sql:
    SELECT  CLIENTS.ID AS CLIENT_ID
,           CLIENTS.NAME AS CLIENT
,           AGREEMENTS.ID AS AGR_ID
,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYYMM') AS PERIOD
,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'MON YY') AS STATEMENT_DESC
,           TERRITORIES.NAME AS TERRITORY
,           SOURCES.NAME AS SOURCE
,           SUM(ROY_PAY_SUMS_EXT.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*ROY_PAY_SUMS_EXT.ACCOUNT_EXCHANGE_RATE) AS 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) AS DISTRIBUTED_ACC_CCY
,           SUM(ROY_PAY_SUMS_EXT.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS SOURCE_GBP
,           SUM(ROY_PAY_SUMS_EXT.WHT_ADJUSTED_AMOUNT*ROY_PAY_SUMS_EXT.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS DISTRIBUTED_GBP
FROM        "AWAL_SAX"."SAX"."INTERESTED_PARTIES" CLIENTS
,           "AWAL_SAX"."SAX"."INTERESTED_PARTIES" SOURCES
,           "AWAL_SAX"."SAX"."AGREEMENTS" AGREEMENTS
,           "AWAL_SAX"."SAX"."RECORDINGS" RECORDINGS
,           "AWAL_SAX"."SAX"."RECORDING_ARTISTS" RECORDING_ARTISTS
,           "AWAL_SAX"."DEV"."ROY_PAY_SUMS_EXT" ROY_PAY_SUMS_EXT
,           "AWAL_SAX"."SAX"."TERRITORIES" TERRITORIES
,           "AWAL_SAX"."SAX"."RECONCILIATIONS" RECONCILIATIONS
,           "AWAL_SAX"."SAX"."EXCHANGE_RATES" EXCHANGE_RATES
,           "AWAL_SAX"."SAX"."BATCHES" BATCHES
WHERE       1=1
AND         ROY_PAY_SUMS_EXT.ACCOUNT_ID    = AGREEMENTS.ID
AND         AGREEMENTS.ASSIGNOR_ID = CLIENTS.ID
AND         ROY_PAY_SUMS_EXT.RECORDING_ID = RECORDINGS.ID (+)
AND         ROY_PAY_SUMS_EXT.TERRITORY_ID = TERRITORIES.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.OUTGOING_AGREEMENT_TYPE = 'Client'
AND         AGREEMENTS.CLIENT_RIGHTS    = 'Neighbouring Rights'
AND         ROY_PAY_SUMS_EXT.PAYMENT_DATE    >=  TO_DATE('01/12/2017','DD/MM/YYYY')
AND         ROY_PAY_SUMS_EXT.PAYMENT_DATE    <   TO_DATE('01/01/2018','DD/MM/YYYY')
---------
GROUP BY CLIENTS.ID
,           CLIENTS.NAME
,           AGREEMENTS.ID
,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYYMM')
,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'MON YY')
,           TERRITORIES.NAME
,           SOURCES.NAME
----------------------------------------------------
----------------------------------------------------
union
----------------------------------------------------
----------------------------------------------------
SELECT      CLIENTS.ID AS CLIENT_ID
,           CLIENTS.NAME AS CLIENT
,           AGREEMENTS.ID AS AGR_ID
,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYYMM') AS PERIOD
,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'MON YY') AS STATEMENT_DESC
,           TERRITORIES.NAME AS TERRITORY
,           SOURCES.NAME AS SOURCE
,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS SOURCE_ACC_CCY
,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS DISTRIBUTED_ACC_CCY
,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS SOURCE_GBP
,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS DISTRIBUTED_GBP
FROM        "AWAL_SAX"."SAX"."INTERESTED_PARTIES" CLIENTS
,           "AWAL_SAX"."SAX"."INTERESTED_PARTIES" SOURCES
,           "AWAL_SAX"."SAX"."AGREEMENTS" AGREEMENTS
,           "AWAL_SAX"."SAX"."RECORDINGS" RECORDINGS
,           "AWAL_SAX"."SAX"."RECORDING_ARTISTS" RECORDING_ARTISTS
,           "AWAL_SAX"."SAX"."ROYALTY_ADJ_PAYMENTS" ROYALTY_ADJ_PAYMENTS
,           "AWAL_SAX"."SAX"."ROYALTIES" ROYALTIES
,           "AWAL_SAX"."SAX"."TERRITORIES" TERRITORIES
,           "AWAL_SAX"."SAX"."RECONCILIATIONS" RECONCILIATIONS
,           "AWAL_SAX"."SAX"."EXCHANGE_RATES" EXCHANGE_RATES
,           "AWAL_SAX"."SAX"."BATCHES" BATCHES
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         ROYALTIES.RECORDING_ID = RECORDINGS.ID (+)
AND         ROYALTIES.TERRITORY_ID = TERRITORIES.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.OUTGOING_AGREEMENT_TYPE = 'Client'
AND         AGREEMENTS.CLIENT_RIGHTS    = 'Neighbouring Rights'
AND         ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE    >=  TO_DATE('01/12/2017','DD/MM/YYYY')
AND         ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE    <   TO_DATE('01/01/2018','DD/MM/YYYY')
---------
GROUP BY CLIENTS.ID
,           CLIENTS.NAME
,           AGREEMENTS.ID
,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYYMM')
,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'MON YY')
,           TERRITORIES.NAME
,           SOURCES.NAME
;;
```