# View: historic_generated_income_summary_working

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

## Overview

- **File Size:** 10058 bytes
- **Lines of Code:** 336
- **Dimensions:** 23
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `AGR_ID` | string |
| `AGR_Assignor` | string |
| `Assignor_ID` | string |
| `Contributor_ID` | string |
| `Contributor_Name` | string |
| `Payment_Date` | date |
| `Reconciliation_Date` | date |
| `Recording_ID` | string |
| `Recording_ISRC` | string |
| `Recording_Title` | string |
| `Recording_Version` | string |
| `Recording_Artist` | string |
| `Source_CMO` | string |
| `Territory` | string |
| `Right_Type` | string |
| `AGR_currency` | string |
| `Source_ACC` | number |
| `Distributed_ACC` | number |
| `Source_USD` | number |
| `Distributed_USD` | number |
| `Incoming_Type` | string |
| `Agreement_Rights` | string |
| `Entity_Name` | string |

## Derived Table

```sql
sql:
    select
    case when bch.incoming_type_id = 'AIT19' then
    'Producer Income'
    else
    rps.royalty_chain_contributor_id end as contributor_id,
    case when bch.incoming_type_id = 'AIT19' then
    assignor.name || ' (Label)'
    else contributor.name end as contributor_name,
    rps.recording_id as recording_id,
    wrc.title as recording_title,
    wrc.sub_title as recording_version,
    wrc.isrc as isrc,
    ra.name as recording_artist,
    rps.account_id as agr_id,
    assignor.name as agr_assignor,
    assignor.id as assignor_id,
    agr.acquirer_id as entity_id,
    acq.name as entity_name,
    case when bch.incoming_type_id = 'AIT19' then
    'Producer'
    else
    'Performer' end as incoming_type,
    source.name as source_cmo,
    acc_ccy.abbreviation as agr_currency,
    rts.description as right_type,

    round(sum(cast(rps.wht_adjusted_amount as double) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double)), 3) as distributed_acc,

    round(sum(cast(rps.roy_gen_chain_receipts_amount as double) * (1 - (cast(coalesce(rps.internal_wht_rate_used, '0') as double) / 100)) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double)), 3) as source_acc,

    round(sum(cast(rps.wht_adjusted_amount as double) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double) * cast(er.exchange_rate as double)), 3) as distributed_usd,

    round(sum(cast(rps.roy_gen_chain_receipts_amount as double) * (1 - (cast(coalesce(rps.internal_wht_rate_used, '0') as double) / 100)) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double) * cast(er.exchange_rate as double)), 3) as source_usd,

    terr.name as territory,
    to_date(rps.payment_date) as payment_date,
    rec.fixed_date as reconciliation_date,
    agr.client_rights as agreement_rights

from
    "AWAL_SAX"."SAX"."ROY_PAY_SUMS_EXT" rps
inner join
    "AWAL_SAX"."SAX"."AGREEMENTS" agr on agr.id = rps.account_id
and agr.outgoing_agreement_type ='Client'
and agr.client_rights ='Neighbouring Rights'
inner join
    "AWAL_SAX"."SAX"."BATCHES" bch on bch.id = rps.batch_id
and bch.incoming_type_id in ('AIT11', 'AIT21', 'AIT19')
inner join
    "AWAL_SAX"."SAX"."RECONCILIATIONS" rec on rec.id = bch.reconciliation_id
inner join
    "AWAL_SAX"."SAX"."RECORDINGS" wrc on
    wrc.id = rps.recording_id
left outer join
    "AWAL_SAX"."SAX"."RECORDING_ARTISTS" ra on
    wrc.recording_artist_id = ra.id
inner join
    "AWAL_SAX"."SAX"."CURRENCIES" acc_ccy on acc_ccy.id = agr.payment_currency_id
inner join
    "AWAL_SAX"."SAX"."TERRITORIES" terr on rps.territory_id = terr.id
inner join
    "AWAL_SAX"."SAX"."EXCHANGE_RATES" er on
    er.start_date <= to_date(rps.payment_date)
    and er.end_date >= to_date(rps.payment_date)
    and er.from_currency_id = acc_ccy.id
    and er.to_currency_id = 'CUR79'
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" source on
    bch.source_ip_id = source.id
left outer join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" contributor on
    contributor.id = rps.royalty_chain_contributor_id
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" assignor on
    assignor.id = agr.assignor_id
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" acq on
    acq.id = agr.acquirer_id
inner join
    "AWAL_SAX"."SAX"."RIGHT_TYPES" rts on rts.id = rps.right_type_id
group by
    case when bch.incoming_type_id = 'AIT19' then
    'Producer Income'
    else
    rps.royalty_chain_contributor_id end,
    case when bch.incoming_type_id = 'AIT19' then
    assignor.name || ' (Label)'
    else contributor.name end,
    rps.recording_id,
    wrc.title,
    wrc.sub_title,
    wrc.isrc,
    ra.name,
    rps.account_id,
    assignor.name,
    assignor.id,
    agr.acquirer_id,
    acq.name,
    case when bch.incoming_type_id = 'AIT19' then
    'Producer'
    else
    'Performer' end,
    source.name,
    acc_ccy.abbreviation,
    rts.description,
    terr.name,
    to_date(rps.payment_date),
    rec.fixed_date,
    agr.client_rights

union all
select
    case when bch.incoming_type_id = 'AIT19' then
    'Producer Income'
    else
    roy.publ_sub_account_ip_id end as contributor_id,
    case when bch.incoming_type_id = 'AIT19' then
    assignor.name || ' (Label)'
    else contributor.name end as contributor_name,
    roy.id as recording_id,
    bch.description as recording_title,
    'Adjustment' as recording_version,
    'Adjustment' as isrc,
    'Adjustment' as recroding_artist,
    rps.account_id as agr_id,
    assignor.name as agr_assignor,
    assignor.id as assignor_id,
    agr.acquirer_id as entity_id,
    acq.name as entity_name,
    case when bch.incoming_type_id = 'AIT19' then
    'Producer'
    else
    'Performer' end as incoming_type,
    source.name as source_cmo,
    acc_ccy.abbreviation as agr_currency,
    rts.description as right_type,

    round(sum(cast(rps.wht_adjusted_amount as double) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double)), 3) as distributed_acc,

    round(sum(cast(rps.roy_gen_source_amount as double) * (1 - (cast(coalesce(rps.internal_wht_rate_used, 0) as double) / 100)) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double)), 3) as source_acc,

    round(sum(cast(rps.wht_adjusted_amount as double) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double) * cast(er.exchange_rate as double)), 3) as distributed_usd,

    round(sum(cast(rps.roy_gen_source_amount as double) * (1 - (cast(coalesce(rps.internal_wht_rate_used, 0) as double) / 100)) * cast(rps.reconciliation_exchange_rate as double) * cast(rps.account_exchange_rate as double) * cast(er.exchange_rate as double)), 3) as source_usd,

    terr.name as territory,
    rps.payment_date as payment_date,
    rec.fixed_date as reconciliation_date,
    agr.client_rights as agreement_rights
from
    "AWAL_SAX"."SAX"."ROYALTIES" roy
inner join
    "AWAL_SAX"."SAX"."ROYALTY_ADJ_PAYMENTS" rps
    on rps.royalty_id = roy.id
inner join
    "AWAL_SAX"."SAX"."AGREEMENTS" agr on (agr.id = rps.account_id
and agr.outgoing_agreement_type ='Client'
and agr.client_rights ='Neighbouring Rights')
inner join
    "AWAL_SAX"."SAX"."BATCHES" bch on bch.id = roy.batch_id
and bch.incoming_type_id in ('AIT11', 'AIT21', 'AIT19')
inner join
    "AWAL_SAX"."SAX"."RECONCILIATIONS" rec on rec.id = bch.reconciliation_id
inner join
    "AWAL_SAX"."SAX"."CURRENCIES" acc_ccy on acc_ccy.id = agr.payment_currency_id
inner join
    "AWAL_SAX"."SAX"."TERRITORIES" terr on roy.territory_id = terr.id
inner join
    "AWAL_SAX"."SAX"."EXCHANGE_RATES" er on
    rps.payment_date between er.start_date and er.end_date
    and er.from_currency_id = acc_ccy.id
    and er.to_currency_id = 'CUR79'
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" source on
    bch.source_ip_id = source.id
left outer join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" contributor on
    contributor.id = roy.publ_sub_account_ip_id
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" assignor on
    assignor.id = agr.assignor_id
inner join
    "AWAL_SAX"."SAX"."INTERESTED_PARTIES" acq on
    acq.id = agr.acquirer_id
inner join
    "AWAL_SAX"."SAX"."RIGHTS" rts on rts.id = rps.ROY_GEN_RIGHT_ID
group by
    case when bch.incoming_type_id = 'AIT19' then
    'Producer Income'
    else
    roy.publ_sub_account_ip_id end,
    case when bch.incoming_type_id = 'AIT19' then
    assignor.name || ' (Label)'
    else contributor.name end,
    roy.id,
    bch.description,
    'Adjustment',
    'Adjustment',
    'Adjustment',
    rps.account_id,
    assignor.name,
    assignor.id,
    agr.acquirer_id,
    acq.name,
    case when bch.incoming_type_id = 'AIT19' then
    'Producer'
    else
    'Performer' end,
    source.name,
    acc_ccy.abbreviation,
    rts.description,
    terr.name,
    rps.payment_date,
    rec.fixed_date,
    agr.client_rights
      ;;
```