# View: historic_gis_performers_Blanka

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

## Overview

- **File Size:** 9640 bytes
- **Lines of Code:** 181
- **Dimensions:** 5
- **Measures:** 2
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Source_CMO` | string |
| `WRC_Title` | string |
| `WRC_Artist` | string |
| `Contributor_Name` | string |
| `Contributor_ID` | string |

## Measures

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

## Dimension Groups

| Name | Type |
|------|------|
| `Payment_Date` | time |
| `Usage_End_Date` | time |

## SQL Comments

- Account Currency
- usd exchange rate
- and source.name = 'CPRA'
- 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,
     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,
     DATE(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,
     -- 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
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
and a.Outgoing_Agreement_type = 'Client'
and a.client_rights  = 'Neighbouring Rights'
and a.nr_agreement_type = 'Performer / Contributor'
--and source.name = 'CPRA'
and    rps.payment_date between to_date('01/01/2018', 'DD/MM/YYYY') and to_date('01/01/2023', 'DD/MM/YYYY')
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,
      DATE(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,
      DATE(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,
            -- 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

      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
      and a.Outgoing_Agreement_type = 'Client'
      and a.client_rights  = 'Neighbouring Rights'
      and a.nr_agreement_type = 'Performer / Contributor'
      --and source.name = 'CPRA'
      and rap.payment_date between to_date('01/01/2018', 'DD/MM/YYYY') and to_date('01/01/2023', 'DD/MM/YYYY')
      group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22;;
```