# View: historic_gis_sum

**View Name:** historic_gis_sum
**Table Source:** `Derived from: Awal_Sax.Sax, Sax.Exchange_Rates, Sax.Interested_Parties, Sax.Recording_Artists, Sax.Right_Types + 3 more`
**File Path:** `views/historic_gis_sum.view.lkml`

## Overview

- **File Size:** 9046 bytes
- **Lines of Code:** 245
- **Dimensions:** 26
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `AGR_ID` | string |
| `AGR_Assignor_ID` | string |
| `AGR_Assignor` | string |
| `Rights_Description` | string |
| `AGR_Outgoing_Type` | string |
| `NR_Agreement_Type` | string |
| `Contributor_ID` | string |
| `Contributor_Name` | string |
| `Source_CMO` | string |
| `WRC_ID` | string |
| `WRC_Title` | string |
| `WRC_Version` | string |
| `WRC_ISRC` | string |
| `WRC_Artist` | string |
| `Right_Type` | string |
| `Territory_Name` | string |
| `Payment_Date` | date |
| `Reconciliation_Date` | date |
| `Usage_Start_Date` | date |
| `Usage_End_Date` | date |
| `Acc_Currency` | string |
| `Commission_rate` | string |
| `WHT_Amount_USD` | number |
| `WHT_Adjusted_Received_Amount_USD` | number |
| `Commission_USD` | number |
| `Distributed_Amount_USD` | number |

## Measures

| Name | Type |
|------|------|
| `Source_Amount_USD` | sum |

## SQL Comments

- usd exchange rate
- usd exchange rate

## Derived Table

```sql
sql:
    select
    rps.account_id AGR_ID,
    assignor.id AGR_Assignor_ID,
    assignor.name AGR_Assignor,
    a.DESCRIPTION_OF_RIGHTS Rights_Description,
    a.outgoing_agreement_type AGR_Outgoing_Type,
    a.nr_agreement_type NR_Agreement_Type,
    rps.royalty_chain_contributor_id Contributor_ID,
    contributor.name Contributor_Name,
    case when source.name is null then 'VPL' else source.name end Source_CMO,
    r.recording_id WRC_ID,
    case when wrc.title is null then ro.RECORDING_TITLE else wrc.title end WRC_Title,
    case when wrc.sub_title is null then ro.RECORDING_SUB_TITLE else wrc.sub_title end WRC_Version,
    case when wrc.isrc is null then ro.recording_isrc else wrc.isrc end WRC_ISRC,
    case when ra.name is null then ro.RECORDING_ARTIST_NAME else ra.name end WRC_Artist,
    rt.description Right_Type,
    t.name Territory_Name,
    TO_CHAR(rps.payment_date,'YYYY-MM-DD') Payment_Date,
    TO_CHAR(rec.fixed_date, 'YYYY-MM-DD') Reconciliation_Date,
    TO_CHAR(r.royalty_period_start_date, 'YYYY-MM-DD') Usage_Start_Date,
    TO_CHAR(r.royalty_period_end_date, 'YYYY-MM-DD') Usage_End_Date,
    c.abbreviation Acc_Currency,
    rps.roy_gen_step_perc_comm_taken Commission_rate,
    -- USD
    sum(r.received_amount * rps.reconciliation_exchange_rate * er.exchange_rate) Source_Amount_USD,
    sum(r.wht_amount * rps.reconciliation_exchange_rate * er.exchange_rate) WHT_Amount_USD,
    sum(rps.roy_gen_total_received_amount * rps.reconciliation_exchange_rate * er.exchange_rate) WHT_Adjusted_Received_Amount_USD,
    sum(rps.roy_gen_chain_source_amount * rps.reconciliation_exchange_rate * er.exchange_rate)- sum(rps.wht_adjusted_amount * rps.reconciliation_exchange_rate * er.exchange_rate) Commission_USD,
    sum(rps.wht_adjusted_amount * rps.reconciliation_exchange_rate * er.exchange_rate) Distributed_Amount_USD
    from  AWAL_SAX.SAX.royalty_payments rps
    inner join AWAL_SAX.SAX.royalties r on r.id = rps.royalty_id
    inner join AWAL_SAX.SAX.royalties_original_data ro on r.id = ro.id
    inner join AWAL_SAX.SAX.batches bch on bch.id = r.batch_id
    left join AWAL_SAX.SAX.recordings wrc on r.recording_id = wrc.id
    left join AWAL_SAX.SAX.recording_artists ra on ra.id = wrc.recording_artist_id
    left join AWAL_SAX.SAX.right_types rt on rt.id = r.right_type_id
    inner join AWAL_SAX.SAX.territories t on t.id = r.territory_id
    left join AWAL_SAX.SAX.interested_parties source on source.id = bch.source_ip_id
    inner join AWAL_SAX.SAX.agreements a on rps.account_id = a.id
    inner join AWAL_SAX.SAX.interested_parties assignor on a.assignor_id = assignor.id
    left join AWAL_SAX.SAX.interested_parties contributor on contributor.id = rps.royalty_chain_contributor_id
    inner join AWAL_SAX.SAX.reconciliations rec on bch.reconciliation_id = rec.id
    left join awal_sax.sax.currencies c on a.payment_currency_id = c.id
    --usd exchange rate
    inner join AWAL_SAX.SAX.EXCHANGE_RATES er on er.FROM_CURRENCY_ID = rec.PAYEE_CURRENCY_ID
    AND er.TO_CURRENCY_ID = 'CUR79'
    AND er.START_DATE <= rps.payment_date
    AND er.END_DATE >= rps.payment_date
    where r.generation_status_id is not null
    group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22

    union

    select
    rap.account_id AGR_ID,
    assignor.id AGR_Assignor_ID,
    assignor.name AGR_Assignor,
    a.DESCRIPTION_OF_RIGHTS Rights_Description,
    a.outgoing_agreement_type AGR_Outgoing_Type,
    a.nr_agreement_type NR_Agreement_Type,
    case when a.nr_agreement_type like ('%Performer%') then r.PUBL_SUB_ACCOUNT_IP_ID else '' end Contributor_ID,
    case when a.nr_agreement_type like ('%Performer%') then contributor.name else '' end Contributor_Name,
    case when source.name is null then 'VPL' else source.name end Source_CMO,
    r.recording_id as WRC_ID,
    case when wrc.title is null then ro.RECORDING_TITLE else wrc.title end WRC_Title,
    case when wrc.sub_title is null then ro.RECORDING_SUB_TITLE else wrc.sub_title end WRC_Version,
    case when wrc.isrc is null then ro.recording_isrc else wrc.isrc end WRC_ISRC,
    case when ra.name is null then ro.RECORDING_ARTIST_NAME else ra.name end WRC_Artist,
    rt.description Right_Type,
    t.name Territory_Name,
    TO_CHAR(rap.payment_date, 'YYYY-MM-DD') Payment_Date,
    TO_CHAR(rec.fixed_date, 'YYYY-MM-DD') Reconciliation_Date,
    TO_CHAR(r.royalty_period_start_date, 'YYYY-MM-DD') Usage_Start_Date,
    TO_CHAR(r.royalty_period_end_date, 'YYYY-MM-DD') Usage_End_Date,
    c.abbreviation Acc_Currency,
    rap.roy_gen_commission_rate_taken Commission_rate,
    -- USD
    sum(r.received_amount * rap.reconciliation_exchange_rate * er.exchange_rate) Source_Amount_USD,
    sum(r.wht_amount * rap.reconciliation_exchange_rate * er.exchange_rate) WHT_Amount_USD,
    sum(rap.roy_gen_received_amount * rap.reconciliation_exchange_rate * er.exchange_rate) WHT_Adjusted_Received_Amount_USD,
    sum(rap.roy_gen_source_amount * rap.reconciliation_exchange_rate * er.exchange_rate)- sum(rap.wht_adjusted_amount * rap.reconciliation_exchange_rate * er.exchange_rate) Commission_USD,
    sum(rap.wht_adjusted_amount * rap.reconciliation_exchange_rate * er.exchange_rate) Distributed_Amount_USD

    from  AWAL_SAX.SAX.royalty_adj_payments rap
    inner join AWAL_SAX.SAX.royalties r on r.id = rap.royalty_id
    inner join AWAL_SAX.SAX.royalties_original_data ro on r.id = ro.id
    inner join AWAL_SAX.SAX.batches bch on bch.id = r.batch_id
    left join AWAL_SAX.SAX.recordings wrc on wrc.id = r.recording_id
    left join AWAL_SAX.SAX.recording_artists ra on ra.id = wrc.recording_artist_id
    left join AWAL_SAX.SAX.right_types rt on rt.id = r.right_type_id
    inner join AWAL_SAX.SAX.territories t on t.id = r.territory_id
    left join AWAL_SAX.SAX.interested_parties source on source.id = bch.source_ip_id
    inner join AWAL_SAX.SAX.agreements a on rap.account_id = a.id
    inner join AWAL_SAX.SAX.interested_parties assignor on a.assignor_id = assignor.id
    left join AWAL_SAX.SAX.interested_parties contributor on contributor.id = r.PUBL_SUB_ACCOUNT_IP_ID
    inner join AWAL_SAX.SAX.reconciliations rec on bch.reconciliation_id = rec.id
    left join awal_sax.sax.currencies c on a.payment_currency_id = c.id
    --usd exchange rate
    inner join AWAL_SAX.SAX.EXCHANGE_RATES er on er.FROM_CURRENCY_ID = rec.PAYEE_CURRENCY_ID
    AND er.TO_CURRENCY_ID = 'CUR79'
    AND er.START_DATE <= rap.payment_date
    AND er.END_DATE >= rap.payment_date
    where  r.generation_status_id is not null
    group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22;;
```