# View: dt_abacus_expenses_prod

**View Name:** dt_abacus_expenses_prod
**Table Source:** `Derived from: Prod_Royalty_Accounting_Royalty_Accounting.Abacus_State, Prod_Royalty_Accounting_Royalty_Accounting.Account_Payment_Term, Prod_Royalty_Accounting_Royalty_Accounting.Exchange_Rate, Prod_Royalty_Accounting_Royalty_Accounting.Ledger_Adjustment_Applied, Prod_Royalty_Accounting_Royalty_Accounting.Ledger_Adjustment_Detail_Applied + 3 more`
**File Path:** `views/dt_abacus_expenses_prod.view.lkml`

## Overview

- **File Size:** 8885 bytes
- **Lines of Code:** 218
- **Dimensions:** 22
- **Measures:** 2
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `batch_id` | number |
| `adjustment_item_id` | number |
| `expense_item_id` | number |
| `ledger_status` | string |
| `account_id` | number |
| `account_name` | string |
| `contract_id` | number |
| `contract_name` | string |
| `upc` | number |
| `distribution_type` | string |
| `adjustment_amount` | number |
| `adjustment_currency_code` | string |
| `adjustment_type_id` | number |
| `oa_category_name` | string |
| `type_name` | string |
| `activity_period` | number |
| `statement_period` | number |
| `note` | string |
| `file_name` | string |
| `payee_currency_code` | string |
| `adjustment_created_at` | date_time |
| `expense_created_at` | date_time |

## Measures

| Name | Type |
|------|------|
| `expense_amount` | sum |
| `expense_amount_payee_currency` | sum |

## SQL Comments

- left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.abacus_event ae on ae.abacus_event_id = la.abacus_event_id and ae._fivetran_deleted = False

## Derived Table

```sql
sql:
    with  not_applied_adjustments as (
              select
                  wa.account_id,
                  contract_id,
                  apply_to_statement_period_id as statement_period_id,
                  wa.statement_period_adjustment_file_id,
                  wa.adjustment_amount,
                  wa.adjustment_currency_code,
                  wa.worksheet_adjustment_id,
                  MAX(
                      CASE
                          WHEN action_name = 'apply_file'
                          and action_status = 'complete' THEN 3
                          WHEN (
                              action_name = 'approve_file'
                              and action_status = 'complete'
                          ) THEN 2
                          WHEN (
                              action_name = 'upload_file'
                              and action_status = 'complete'
                          ) THEN 1
                      END
                  ) as adjustment_status_rank,
                  MAX(CASE
                    WHEN apt.currency_code = wa.adjustment_currency_code
                        THEN adjustment_amount
                    WHEN rate is not null THEN round(rate * wa.adjustment_amount, 2)
                    ELSE NULL
                END)
                    as adjustment_amount_payee_currency
              from
                  orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.worksheet_adjustment wa
                  inner join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period_adjustment_file spaf on spaf.statement_period_adjustment_file_id = wa.statement_period_adjustment_file_id
                  left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_state ast on ast.parent_table_id = wa.statement_period_adjustment_file_id and ast.parent_table_name = 'statement_period_adjustment_file'
                  left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_payment_term apt on wa.account_id= apt.account_id
                  left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.exchange_rate er on wa.apply_to_statement_period_id = er.statement_period_id and apt.currency_code = er.to_currency_code and wa.adjustment_currency_code = er.from_currency_code
                  where spaf.deleted_at is null and wa.deleted_at is null
                  group by 1,2,3,4,5,6,7
      )
      select
          lad.worksheet_adjustment_detail_id,
          spaf.statement_period_adjustment_file_id as batch_id,
          spaf.file_name,
          la.worksheet_adjustment_id as adjustment_item_id,
          lad.worksheet_adjustment_detail_id as expense_item_id,
          case
              when contains(IFNULL(laa.ledger_adjustment_id, laa.worksheet_adjustment_id) ,la.worksheet_adjustment_id) then 'Applied'
              WHEN adjustment_status_rank = 2 THEN 'approved'
              WHEN adjustment_status_rank = 1 THEN 'not_approved'
          end as ledger_status,
          la.account_id,
          l.labelname as account_name,
          la.contract_id,
          c.contract_name,
          lad.upc,
          lad.distribution_type,
          la.adjustment_amount,
          la.adjustment_currency_code,
          lad.reference_adjustment_type_id as adjustment_type_id,
          rat2.oa_category_name,
          rat2.type_name,
          la.activity_statement_period_id as activity_period,
          la.apply_to_statement_period_id as statement_period,
          lad.note,
          lad.amount as expense_amount,
          coalesce(laad.adjustment_payee_currency_code, a.currency_code) as payee_currency_code,
          coalesce(laad.adjustment_amount_payee_currency, lad.amount * IFNULL(er.rate, 1)) as expense_amount_payee_currency,
          la.created_at as adjustment_created_at,
          lad.created_at as expense_created_at
      from (select * from orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.worksheet_adjustment la where la._fivetran_deleted = False and la.deleted_at is null) la
            left join not_applied_adjustments naa on naa.worksheet_adjustment_id = la.worksheet_adjustment_id
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.worksheet_adjustment_detail lad on lad.worksheet_adjustment_id = la.worksheet_adjustment_id and lad._fivetran_deleted = False
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.ledger_adjustment_applied laa on IFNULL(laa.ledger_adjustment_id, laa.worksheet_adjustment_id) = la.worksheet_adjustment_id and laa._fivetran_deleted = False
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.ledger_adjustment_detail_applied laad on IFNULL(laad.ledger_adjustment_detail_id, laad.worksheet_adjustment_detail_id) = lad.worksheet_adjustment_detail_id and laad._fivetran_deleted = False
          left 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 and rat._fivetran_deleted = False
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.reference_adjustment_type rat2 on rat2.reference_adjustment_type_id = lad.reference_adjustment_type_id and rat._fivetran_deleted = False
          -- left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.abacus_event ae on ae.abacus_event_id = la.abacus_event_id and ae._fivetran_deleted = False
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.statement_period_adjustment_file spaf on
          spaf.statement_period_adjustment_file_id = la.statement_period_adjustment_file_id and spaf._fivetran_deleted = False
          left join facts.PROD.dim_label l on l.labelid = la.account_id
          left join royalty_accounting.PROD.vw_dim_abacus_contract c on c.contract_id = la.contract_id
          left join royalty_accounting.PROD.vw_dim_abacus_account a on a.account_id=c.account_id
          left join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.exchange_rate er on er.statement_period_id = la.apply_to_statement_period_id and er.from_currency_code = la.adjustment_currency_code and er.to_currency_code = a.currency_code
          where rat.type_name = 'Account Expense' and spaf.deleted_at is null and lad.deleted_at is null;;
```