# View: historic_gis_correct

**View Name:** historic_gis_correct
**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_correct.view.lkml`

## Overview

- **File Size:** 11214 bytes
- **Lines of Code:** 296
- **Dimensions:** 34
- **Measures:** 0
- **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_ID` | string |
| `Source_CMO` | string |
| `WRC_ID` | string |
| `WRC_Title` | string |
| `WRC_Version` | string |
| `WRC_ISRC` | string |
| `WRC_Artist` | string |
| `Right_Type` | string |
| `Territory_ID` | string |
| `Territory_Name` | string |
| `Payment_Date` | date |
| `Reconciliation_Date` | date |
| `Usage_Start_Date` | date |
| `Usage_End_Date` | date |
| `Acc_Currency` | string |
| `Commission_rate` | string |
| `Source_Amount_ACC_CCY` | number |
| `WHT_Amount_ACC_CCY` | number |
| `WHT_Adjusted_Received_Amount_ACC_CCY` | number |
| `Commission_ACC_CCY` | number |
| `Distributed_Amount_ACC_CCY` | number |
| `Source_Amount_USD` | number |
| `WHT_Amount_USD` | number |
| `WHT_Adjusted_Received_Amount_USD` | number |
| `Commission_USD` | number |
| `Distributed_Amount_USD` | number |

## SQL Comments

- Account Currency
- usd exchange rate
- Account Currency
- 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,
     bch.source_ip_id Source_ID,
     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.id Territory_ID,
     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,
     -- Account Currency
     sum(r.received_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate) Source_Amount_ACC_CCY,
     sum(r.wht_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate) WHT_Amount_ACC_CCY,
     sum(rps.roy_gen_total_received_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate) WHT_Adjusted_Received_Amount_ACC_CCY,
     sum(rps.roy_gen_chain_source_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate)- sum(rps.wht_adjusted_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate) Commission_ACC_CCY,
     sum(rps.wht_adjusted_amount * rps.reconciliation_exchange_rate * rps.account_exchange_rate) Distributed_Amount_ACC_CCY,
     -- 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,23,24

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,
     bch.source_ip_id Source_ID,
     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.id Territory_ID,
     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,
     -- Account Currency
     sum(r.received_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate) Source_Amount_ACC_CCY,
     sum(r.wht_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate) WHT_Amount_ACC_CCY,
     sum(rap.roy_gen_received_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate) WHT_Adjusted_Received_Amount_ACC_CCY,
     sum(rap.roy_gen_source_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate)- sum(rap.wht_adjusted_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate) Commission_ACC_CCY,
     sum(rap.wht_adjusted_amount * rap.reconciliation_exchange_rate * rap.account_exchange_rate) Distributed_Amount_ACC_CCY,
     -- 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,23,24;;
```