# View: historic_gis_test

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

## Overview

- **File Size:** 11053 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
    ,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYY-MM-DD') Payment_Date
    ,           TO_CHAR(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
    ,           TO_CHAR(ROY_PAY_SUMS_EXT.PAYMENT_DATE,'YYYY-MM-DD')
    ,           TO_CHAR(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
    ,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYY-MM-DD') Payment_Date
    ,           TO_CHAR(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
      ,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYY-MM-DD')
      ,           TO_CHAR(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
      ;;
```