# View: pre_label_prod_aa

**View Name:** pre_label_prod_aa
**Table Source:** `Derived from: Prod_Royalty_Accounting_Royalty_Accounting.Account, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Account_Payment_Term, Prod_Royalty_Accounting_Royalty_Accounting.Contract, Prod_Royalty_Accounting_Royalty_Accounting.Ledger_Adjustment_Adjustment_Detail + 3 more`
**File Path:** `views/pre_label_prod_aa.view.lkml`

## Overview

- **File Size:** 6391 bytes
- **Lines of Code:** 169
- **Dimensions:** 19
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Run_Controller_Name` | string |
| `APPLY_TO_STATEMENT_PERIOD_ID` | string |
| `Statement_Period_Name` | string |
| `Account_ID` | string |
| `Account_Name` | string |
| `Agreement_ID` | string |
| `Contract_ID` | string |
| `Contract_Name` | string |
| `Contract_Type` | string |
| `Adjustment_Type` | string |
| `Adjustment_Expenses_Count` | number |
| `Adjustment_Expenses` | number |
| `Adjustment_Comment` | string |
| `Ledger_Adjustment_ID` | string |
| `Adjustment_Currency_Code` | string |
| `Adjustment_Amount` | number |
| `Account_Currency` | string |
| `Adjustment_Amount_Payee_Currency` | number |
| `Created_At` | date |

## SQL Comments

- and to_date(ae.event_date) > '2022-12-01'
- and a.account_id in ('68935','69167','68005','68188','67154'))
- add predicates like account_id, agreement_id etc..
- and account_id in ('67933')
- and contract_id in ('67933')

## Derived Table

```sql
sql:
    with details_by_adj as (
    select
        lad.ledger_adjustment_id,
        count(*) detail_count,
        parse_json('['||listagg(nvl(ld.amount,0),',')||']') expenses,
        parse_json('['||listagg(distinct concat('"',replace(ld.note,'\"','\''),'"'),',')||']') details
    from orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_adjustment_adjustment_detail lad
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_adjustment_detail ld
        on ld.ledger_adjustment_detail_id=lad.ledger_adjustment_detail_id
    group by 1
    order by 1
),
adjustments as (
    select
        la.APPLY_TO_STATEMENT_PERIOD_ID,
        asp.statement_period_name Statement_Period_Name,
        rc.run_controller_name Run_Controller_Name,
        a.account_id Account_ID,
        a.account_name Account_Name,
        ex.external_source_id Agreement_ID,
        la.contract_id Contract_ID,
        c.contract_name Contract_Name,
        c.contract_type,
        rat.type_name Adjustment_Type,
        nvl(deets.detail_count,0) Adjustment_Expenses_Count,
        nvl(deets.expenses,0) Adjustment_Expenses,
        IFF(la.note is not null,la.note,deets.details) Adjustment_Comment,
        la.ledger_adjustment_id Ledger_Adjustment_ID,
        la.adjustment_currency_code Adjustment_Currency_Code,
        la.adjustment_amount Adjustment_Amount,
        apt.currency_code Account_Currency,
        la.adjustment_amount*NVL(xr.rate,1) Adjustment_Amount_Payee_Currency,
        to_char(la.created_at,'YYYY-MM-DD HH24:MI:SS') Created_At
    from orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account a
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_payment_term apt on apt.account_id=a.account_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac on ac.account_id=a.account_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract c on c.contract_id=ac.contract_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.run_controller_contract rcc on rcc.contract_id=c.contract_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.run_controller rc on rc.run_controller_id=rcc.run_controller_id
    left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_external_source ex
        on ex.parent_table_id=c.contract_id
            and ex.parent_table_name='contract'
            and ex.external_source_id<>'sax_ingestion'
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_adjustment la
        on la.account_id=a.account_id
            and la.contract_id=c.contract_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_adjustment_type rat on rat.reference_adjustment_type_id=la.reference_adjustment_type_id
    left join details_by_adj deets on deets.ledger_adjustment_id=la.ledger_adjustment_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_adjustment_applied lap on lap.ledger_adjustment_id=la.ledger_adjustment_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae
        on ae.abacus_event_id=lap.abacus_event_id
          --  and to_date(ae.event_date) > '2022-12-01'
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period per on per.statement_period_id=lap.statement_period_id
    left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.exchange_rate xr
        on xr.statement_period_id=per.statement_period_id
            and xr.from_currency_code=la.adjustment_currency_code
            and xr.to_currency_code=apt.currency_code
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period as asp on la.APPLY_TO_STATEMENT_PERIOD_ID = asp.statement_period_id
where rc.run_controller_id in ('6', '11'))
--and a.account_id in ('68935','69167','68005','68188','67154'))
select a.* exclude (created_at),
    sum(adjustment_amount_payee_currency) over (partition by account_id,contract_id order by account_id,contract_id,created_at rows between unbounded preceding and current row) adjustment_amount_total,
    a.created_at
from adjustments a
where true
-- add predicates like account_id, agreement_id etc..
--and account_id in ('67933')
--and contract_id in ('67933')
order by account_id,contract_id,created_at;;
```