# View: mxm_query

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

## Overview

- **File Size:** 10227 bytes
- **Lines of Code:** 253
- **Dimensions:** 16
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `CLIENT_ID` | string |
| `CLIENT` | string |
| `AGR_ID` | string |
| `PERIOD` | date |
| `STATEMENT_DESC` | string |
| `TERRITORY` | string |
| `SOURCE` | string |
| `ARTIST_NAME` | string |
| `RECORDING_TITLE` | string |
| `VERSION` | string |
| `SOURCE_USD` | number |
| `EXCHANGE_RATE` | number |
| `OUTGOING_X_RATE` | number |
| `SOURCE_ACC_CURRENCY` | string |
| `COMMISSION` | string |
| `DISTRIBUTED_ACC_CURRENCY` | string |

## SQL Comments

- ,           AGREEMENTS.DESCRIPTION_OF_RIGHTS
- ,           ROYALTY_STATUSES.DESCRIPTION AS ROY_TYPE
- ,           CONTRIBUTORS.ID AS CONTRIB_ID
- ,           CONTRIBUTORS.NAME AS CONTRIBUTOR
- ,           RECORDINGS.ID

## Derived Table

```sql
sql:
    SELECT
            CLIENTS.ID AS CLIENT_ID
,           CLIENTS.NAME AS CLIENT
,           AGREEMENTS.ID AS AGR_ID
--,           AGREEMENTS.DESCRIPTION_OF_RIGHTS
--,           ROYALTY_STATUSES.DESCRIPTION AS ROY_TYPE
--,           CONTRIBUTORS.ID AS CONTRIB_ID
--,           CONTRIBUTORS.NAME AS CONTRIBUTOR
,           TO_CHAR(ROYALTY_PAYMENTS.PAYMENT_DATE,'YYYYMM') AS PERIOD
,           TO_CHAR(ROYALTY_PAYMENTS.PAYMENT_DATE,'MON YY') AS STATEMENT_DESC
,           TERRITORIES.NAME AS TERRITORY
,           SOURCES.NAME AS SOURCE
--,           RECORDINGS.ID
,           RECORDING_ARTISTS.NAME AS ARTIST_NAME
,           RECORDINGS.TITLE AS RECORDING_TITLE
,           RECORDINGS.SUB_TITLE AS VERSION

      --
      ,           SUM(ROYALTY_PAYMENTS.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS SOURCE_USD
      ,           ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE AS INCOMING_X_RATE
      ,           ROYALTY_PAYMENTS.ACCOUNT_EXCHANGE_RATE AS OUTGOING_X_RATE

      ,           SUM(ROYALTY_PAYMENTS.ROY_GEN_CHAIN_SOURCE_AMOUNT*ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS SOURCE_ACC_CURRENCY
      --,           SUM(ROYALTY_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS DISTRIBUTED_USD
      --
      ,           ROYALTY_PAYMENTS.ROY_GEN_STEP_PERC_COMM_TAKEN AS COMM_TAKEN
      ,           SUM(ROYALTY_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS DISTRIBUTED_ACC_CURRENCY
      ----------
      ----------
      FROM        "AWAL_SAX"."SAX"."INTERESTED_PARTIES" CLIENTS
      ,           "AWAL_SAX"."SAX"."INTERESTED_PARTIES" SOURCES
      --,           INTERESTED_PARTIES CONTRIBUTORS
      ,           "AWAL_SAX"."SAX"."AGREEMENTS"
      --,           ROYALTY_STATUSES
      ,           "AWAL_SAX"."SAX"."RECORDINGS"
      ,           "AWAL_SAX"."SAX"."RECORDING_ARTISTS"
      ,           "AWAL_SAX"."SAX"."ROYALTY_PAYMENTS"
      ,           "AWAL_SAX"."SAX"."ROYALTIES"
      ,           "AWAL_SAX"."SAX"."TERRITORIES"
      ----
      ,           "AWAL_SAX"."SAX"."RECONCILIATIONS"
      ,           "AWAL_SAX"."SAX"."EXCHANGE_RATES"
      ,           "AWAL_SAX"."SAX"."BATCHES"
      ----
      WHERE       1=1
      AND         ROYALTY_PAYMENTS.ACCOUNT_ID    = AGREEMENTS.ID
      AND         ROYALTY_PAYMENTS.ROYALTY_ID    = ROYALTIES.ID
      AND         AGREEMENTS.ASSIGNOR_ID = CLIENTS.ID
      --AND         ROYALTY_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 = 'CUR79'
      AND         EXCHANGE_RATES.START_DATE <= ROYALTY_PAYMENTS.PAYMENT_DATE
      AND         EXCHANGE_RATES.END_DATE >= ROYALTY_PAYMENTS.PAYMENT_DATE
      ---------
      GROUP BY CLIENTS.ID
      ,           CLIENTS.NAME
      ,           AGREEMENTS.ID
      --,           AGREEMENTS.DESCRIPTION_OF_RIGHTS
      --,           ROYALTY_STATUSES.DESCRIPTION
      --,           CONTRIBUTORS.ID
      --,           CONTRIBUTORS.NAME
      ,           TO_CHAR(ROYALTY_PAYMENTS.PAYMENT_DATE,'YYYYMM')
      ,           TO_CHAR(ROYALTY_PAYMENTS.PAYMENT_DATE,'MON YY')
      ,           TERRITORIES.NAME
      ,           SOURCES.NAME
      --,           RECORDINGS.ID
      ,           RECORDING_ARTISTS.NAME
      ,           RECORDINGS.TITLE
      ,           RECORDINGS.SUB_TITLE
      ,           ROYALTY_PAYMENTS.RECONCILIATION_EXCHANGE_RATE
      ,           ROYALTY_PAYMENTS.ACCOUNT_EXCHANGE_RATE
      ,           ROYALTY_PAYMENTS.ROY_GEN_STEP_PERC_COMM_TAKEN
      ----------------------------------------------------
      ----------------------------------------------------
      union
      ----------------------------------------------------
      ----------------------------------------------------
      SELECT      CLIENTS.ID AS CLIENT_ID
      ,           CLIENTS.NAME AS CLIENT
      ,           AGREEMENTS.ID AS AGR_ID
      --,           AGREEMENTS.DESCRIPTION_OF_RIGHTS
      --,           ROYALTY_STATUSES.DESCRIPTION AS ROY_TYPE
      --,           ''  AS CONTRIB_ID
      --,           ''  AS CONTRIBUTOR
      ,           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
      --,           RECORDINGS.ID
      ,           RECORDING_ARTISTS.NAME AS ARTIST_NAME
      ,           RECORDINGS.TITLE AS RECORDING_TITLE
      ,           RECORDINGS.SUB_TITLE AS VERSION
      --
      ,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS SOURCE_USD
      ,           ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE AS INCOMING_X_RATE
      ,           ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE AS OUTGOING_X_RATE

      ,           SUM(ROYALTY_ADJ_PAYMENTS.ROY_GEN_SOURCE_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS SOURCE_ACC_CURRENCY
      ,           ROYALTY_ADJ_PAYMENTS.ROY_GEN_COMMISSION_RATE_TAKEN AS COMM_TAKEN
      ,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE) AS DISTRIBUTED_ACC_CURRENCY
      --
      --
      --,           SUM(ROYALTY_ADJ_PAYMENTS.WHT_ADJUSTED_AMOUNT*ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE*EXCHANGE_RATES.EXCHANGE_RATE) AS DISTRIBUTED_USD
      --

      ----------
      ----------
      FROM        "AWAL_SAX"."SAX"."INTERESTED_PARTIES" CLIENTS
      ,           "AWAL_SAX"."SAX"."INTERESTED_PARTIES" SOURCES
      --,           INTERESTED_PARTIES CONTRIBUTORS
      ,           "AWAL_SAX"."SAX"."AGREEMENTS"
      --,           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"
      ----
      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 = 'CUR79'
      AND         EXCHANGE_RATES.START_DATE <= ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE
      AND         EXCHANGE_RATES.END_DATE >= ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE
      --
      ---------
      GROUP BY CLIENTS.ID
      ,           CLIENTS.NAME
      ,           AGREEMENTS.ID
      --,           AGREEMENTS.DESCRIPTION_OF_RIGHTS
      --,           ROYALTY_STATUSES.DESCRIPTION
      --,           CONTRIBUTORS.ID
      --,           CONTRIBUTORS.NAME
      ,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'YYYYMM')
      ,           TO_CHAR(ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE,'MON YY')
      ,           TERRITORIES.NAME
      ,           SOURCES.NAME
      --,           RECORDINGS.ID
      ,           RECORDING_ARTISTS.NAME
      ,           RECORDINGS.TITLE
      ,           RECORDINGS.SUB_TITLE
      ,           ROYALTY_ADJ_PAYMENTS.RECONCILIATION_EXCHANGE_RATE
      ,           ROYALTY_ADJ_PAYMENTS.ACCOUNT_EXCHANGE_RATE
      ,           ROYALTY_ADJ_PAYMENTS.ROY_GEN_COMMISSION_RATE_TAKEN;;
```