# View: dt_abacus_batch_adjustments_expenses_qa

**View Name:** dt_abacus_batch_adjustments_expenses_qa
**Table Source:** `Derived from: Qa.Dim_Label, Qa_Royalty_Accounting_Royalty_Accounting.Abacus_Event, Qa_Royalty_Accounting_Royalty_Accounting.Abacus_State, Qa_Royalty_Accounting_Royalty_Accounting.Ledger_Adjustment_Applied, Qa_Royalty_Accounting_Royalty_Accounting.Reference_Adjustment_Type + 3 more`
**File Path:** `views/dt_abacus_batch_adjustments_expenses_qa.view.lkml`

## Overview

- **File Size:** 14061 bytes
- **Lines of Code:** 351
- **Dimensions:** 30
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- primary_key: yes

## Dimensions

| Name | Type |
|------|------|
| `worksheet_adjustment_detail_id` | string |
| `worksheet_adjustment_id` | string |
| `batch_id` | number |
| `adjustment_item_id` | number |
| `ledger_status` | string |
| `account_id` | number |
| `account_name` | string |
| `contract_id` | number |
| `contract_name` | 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 |
| `internal_note` | string |
| `file_name` | string |
| `adjustment_payee_currency_code` | string |
| `adjustment_amount_payee_currency` | number |
| `adjustment_created_at` | date_time |
| `date_approved` | date_time |
| `date_applied` | date_time |
| `approved_by` | string |
| `applied_by` | string |
| `expense_amount` | string |
| `run_controller_id` | number |
| `adjustment_or_expense` | string |
| `UPC` | string |

## Derived Table

```sql
sql:
      WITH pending_adj_details as (
          SELECT
              CASE
                  WHEN apply_file_sub_query.action_name = 'apply_file'
                  AND apply_file_sub_query.action_status = 'complete'
                  AND approve_file_sub_query.action_name = 'approve_file'
                  AND approve_file_sub_query.action_status = 'complete'
                  AND upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'complete' THEN 'applied'
                  WHEN approve_file_sub_query.action_name = 'approve_file'
                  AND approve_file_sub_query.action_status = 'complete'
                  AND upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'complete' THEN 'approved'
                  WHEN upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'complete' THEN 'not_approved'
                  ELSE NULL
              END AS STATUS,
              spaf.valid_row_count AS valid_row_count,
              spaf.statement_period_adjustment_file_id AS statement_period_adjustment_file_id,
              spaf.created_at AS created_at,
              spaf.created_by AS created_by,
              spaf.file_name AS file_name,
              spaf.total_rounded_amount_multicurrency AS total_rounded_amount_multicurrency,
              spaf.statement_period_id AS statement_period_id,
              CASE
                  WHEN approve_file_sub_query.action_name = 'approve_file'
                  AND approve_file_sub_query.action_status = 'complete' THEN approve_file_sub_query.last_modified
                  ELSE NULL
              END AS date_approved,
              CASE
                  WHEN approve_file_sub_query.action_name = 'approve_file'
                  AND approve_file_sub_query.action_status = 'complete' THEN approve_file_sub_query.last_modified_by
                  ELSE NULL
              END AS approved_by,
              CASE
                  WHEN apply_file_sub_query.action_name = 'apply_file'
                  AND apply_file_sub_query.action_status = 'complete' THEN apply_file_sub_query.last_modified
                  ELSE NULL
              END AS date_applied,
              CASE
                  WHEN apply_file_sub_query.action_name = 'apply_file'
                  AND apply_file_sub_query.action_status = 'complete' THEN apply_file_sub_query.event_created_by
                  ELSE NULL
              END AS applied_by
          FROM
              orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
              JOIN (
                  SELECT
                      spaf.statement_period_adjustment_file_id AS statement_period_adjustment_file_id,
                      astate.*
                  FROM
                      orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
                      JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.abacus_state AS astate ON spaf.statement_period_adjustment_file_id = astate.parent_table_id
                  WHERE
                      astate.parent_table_name = 'statement_period_adjustment_file'
                      AND astate.action_name = 'upload_file'
                      AND astate.action_status = 'complete'
              ) AS upload_file_sub_query ON spaf.statement_period_adjustment_file_id = upload_file_sub_query.parent_table_id
              LEFT OUTER JOIN (
                  SELECT
                      spaf.statement_period_adjustment_file_id AS statement_period_adjustment_file_id,
                      astate.*
                  FROM
                      orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
                      JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.abacus_state AS astate ON spaf.statement_period_adjustment_file_id = astate.parent_table_id
                  WHERE
                      astate.parent_table_name = 'statement_period_adjustment_file'
                      AND astate.action_name = 'approve_file'
                      AND astate.action_status = 'complete'
              ) AS approve_file_sub_query ON spaf.statement_period_adjustment_file_id = approve_file_sub_query.parent_table_id
              LEFT OUTER JOIN (
                  SELECT
                      spaf.statement_period_adjustment_file_id AS statement_period_adjustment_file_id,
                      astate.*,
                      aevent.created_by AS event_created_by
                  FROM
                      orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
                      JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.abacus_state AS astate ON spaf.statement_period_adjustment_file_id = astate.parent_table_id
                      JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.abacus_event AS aevent ON spaf.statement_period_id = aevent.statement_period_id
                      AND spaf.statement_period_adjustment_file_id = aevent.target_id
                      AND aevent.target_type = 'statement_period_adjustment_file'
                      AND aevent.event_name = 'apply_pending_adjustments'
                      LEFT OUTER JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.abacus_event AS aevent2 ON aevent.statement_period_id = aevent2.statement_period_id
                      AND aevent.target_id = aevent2.target_id
                      AND aevent.abacus_event_id < aevent2.abacus_event_id
                      AND aevent2.target_type = 'statement_period_adjustment_file'
                      AND aevent2.event_name = 'apply_pending_adjustments'
                  WHERE
                      astate.parent_table_name = 'statement_period_adjustment_file'
                      AND astate.action_name = 'apply_file'
                      AND astate.action_status = 'complete'
                      AND aevent2.abacus_event_id IS NULL
              ) AS apply_file_sub_query ON spaf.statement_period_adjustment_file_id = apply_file_sub_query.parent_table_id
          WHERE
              spaf.deleted_at IS NULL
              AND spaf.deleted_by IS NULL
              AND spaf.statement_period_adjustment_file_id IN (
                  SELECT
                      DISTINCT statement_period_adjustment_file_id AS statement_period_adjustment_file_id
                  FROM
                      orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.worksheet_adjustment
              )
      )
      select
          spaf.statement_period_adjustment_file_id as batch_id,
          spaf.file_name,
          la.worksheet_adjustment_id as adjustment_item_id,
          case
              when contains(
                  laa.worksheet_adjustment_id,
                  la.worksheet_adjustment_id
              ) then 'Applied'
              else pad.status
          end as ledger_status,
          la.account_id,
          l.labelname as account_name,
          la.contract_id,
          c.contract_name,
          la.adjustment_amount,
          la.adjustment_currency_code,
          la.reference_adjustment_type_id as adjustment_type_id,
          rat.oa_category_name,
          rat.type_name,
          la.activity_statement_period_id as activity_period,
          la.apply_to_statement_period_id as statement_period,
          listagg(distinct coalesce(la.note, lad.note), ', ') as adjustment_comment,
          listagg(distinct coalesce(la.internal_note, lad.internal_note), ', ') as internal_note,
          round(coalesce(laa.adjustment_amount_payee_currency, lad.amount * IFNULL(er.rate, 1), la.adjustment_amount * IFNULL(er.rate, 1)), 4) as adjustment_amount_payee_currency,
          coalesce(laa.adjustment_payee_currency_code, a.currency_code) as adjustment_payee_currency_code,
          la.created_at as adjustment_created_at,
          c.run_controller_id,
          pad.approved_by,
          pad.date_approved,
          pad.date_applied,
          pad.applied_by,
          lad.upc,
          lad.worksheet_adjustment_detail_id,
          la.worksheet_adjustment_id,
          CASE
            WHEN (
                lad.worksheet_adjustment_detail_id IS NOT NULL
            ) THEN 'Expense'
            ELSE 'Adjustment'
          END AS adjustment_or_expense,
          coalesce(lad.amount, 0) as expense_amount
      from
          (
              select
                  *
              from
                  orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.worksheet_adjustment la
              where
                  la._fivetran_deleted = False
          ) la
          join pending_adj_details pad on la.statement_period_adjustment_file_id = pad.statement_period_adjustment_file_id
          left join orchard_app_reporting_v2.QA_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.QA_royalty_accounting_royalty_accounting.ledger_adjustment_applied laa on laa.worksheet_adjustment_id = la.worksheet_adjustment_id
          and laa._fivetran_deleted = False
          left join orchard_app_reporting_v2.QA_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.QA_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.QA_royalty_accounting_royalty_accounting.statement_period_adjustment_file spaf on spaf.statement_period_adjustment_file_id = ae.target_id
          and spaf._fivetran_deleted = False
          left join facts.QA.dim_label l on l.labelid = la.account_id
          left join royalty_accounting.QA.vw_dim_abacus_contract c on c.contract_id = la.contract_id
          left join royalty_accounting.QA.vw_dim_abacus_account a on a.account_id=c.account_id
          left join orchard_app_reporting_v2.QA_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
      GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,18,19,20,21,22,23,24,25,26,27,28,29,30;;
```