# View: dt_abacus_batch_adjustments

**View Name:** dt_abacus_batch_adjustments
**Table Source:** `Derived from: Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Qa_Royalty_Accounting_Royalty_Accounting, Qa_Royalty_Accounting_Royalty_Accounting.Abacus_State, Qa_Royalty_Accounting_Royalty_Accounting.Statement_Period_Adjustment_File, Qa_Royalty_Accounting_Royalty_Accounting.Worksheet_Adjustment + 1 more`
**File Path:** `views/dt_abacus_batch_adjustments.view.lkml`

## Overview

- **File Size:** 8869 bytes
- **Lines of Code:** 231
- **Dimensions:** 21
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- primary_key: yes

## Dimensions

| Name | Type |
|------|------|
| `worksheet_adjustment_detail_id` | string |
| `worksheet_adjustment_id` | string |
| `worksheet_adjustment_statement_period_adjustment_file_id` | string |
| `worksheet_adjustment_account_id` | string |
| `worksheet_adjustment_contract_id` | string |
| `upc` | string |
| `worksheet_adjustment_adjustment_currency_code` | string |
| `activity_statement_period_id` | string |
| `worksheet_adjustment_apply_to_statement_period_id` | string |
| `amount` | string |
| `note` | string |
| `internal_note` | string |
| `reference_adjustment_type_id` | string |
| `distribution_type` | string |
| `adjustment_or_expense` | string |
| `status` | string |
| `valid_row_count` | string |
| `adj_created_at` | string |
| `adj_created_by` | string |
| `adj_date_applied` | string |
| `adj_approved_by` | string |

## Derived Table

```sql
sql:
      WITH SPAF as (
          SELECT
              CASE
                  WHEN apply_file_sub_query.action_name = 'apply_pending_adjustments'
                  AND apply_file_sub_query.action_status = 'complete' THEN 'Applied'
                  WHEN upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'approved' 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,
              CASE
                  WHEN upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'approved' THEN upload_file_sub_query.last_modified
                  ELSE NULL
              END AS date_approved,
              CASE
                  WHEN upload_file_sub_query.action_name = 'upload_file'
                  AND upload_file_sub_query.action_status = 'approved' THEN upload_file_sub_query.last_modified_by
                  ELSE NULL
              END AS approved_by,
              CASE
                  WHEN apply_file_sub_query.action_name = 'apply_pending_adjustments'
                  AND apply_file_sub_query.action_status = 'complete' THEN apply_file_sub_query.last_modified
                  ELSE NULL
              END AS date_applied
          FROM
              orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
              INNER JOIN (
                  SELECT
                      statement_period_adjustment_file_id,
                      astate.*
                  FROM
                      orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
                      INNER 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.action_name = 'upload_file'
                      AND astate.parent_table_name = 'statement_period_adjustment_file'
                      AND astate.action_status IN ('complete', 'approved')
              ) AS upload_file_sub_query ON upload_file_sub_query.statement_period_adjustment_file_id = spaf.statement_period_adjustment_file_id
              LEFT JOIN (
                  SELECT
                      statement_period_adjustment_file_id,
                      astate.*
                  FROM
                      orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.statement_period_adjustment_file AS spaf
                      INNER 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.action_name = 'apply_pending_adjustments'
                      AND astate.parent_table_name = 'statement_period_adjustment_file'
                      AND astate.action_status IN ('complete')
              ) AS apply_file_sub_query ON apply_file_sub_query.statement_period_adjustment_file_id = spaf.statement_period_adjustment_file_id
          WHERE
              deleted_at is null
      )
      SELECT
          anon_1.worksheet_adjustment_detail_id AS worksheet_adjustment_detail_id,
          worksheet_adjustment.worksheet_adjustment_id AS worksheet_adjustment_id,
          worksheet_adjustment.statement_period_adjustment_file_id AS worksheet_adjustment_statement_period_adjustment_file_id,
          worksheet_adjustment.account_id AS worksheet_adjustment_account_id,
          worksheet_adjustment.contract_id AS worksheet_adjustment_contract_id,
          anon_1.upc AS upc,
          worksheet_adjustment.adjustment_currency_code AS worksheet_adjustment_adjustment_currency_code,
          worksheet_adjustment.apply_to_statement_period_id AS worksheet_adjustment_apply_to_statement_period_id,
          CASE
              WHEN (
                  anon_1.worksheet_adjustment_detail_id IS NOT NULL
              ) THEN 'Expense'
              ELSE 'Adjustment'
          END AS adjustment_or_expense,
          CASE
              WHEN (
                  anon_1.activity_statement_period_id IS NOT NULL
              ) THEN anon_1.activity_statement_period_id
              ELSE worksheet_adjustment.activity_statement_period_id
          END AS activity_statement_period_id,
          CASE
              WHEN (
                  anon_1.worksheet_adjustment_detail_id IS NOT NULL
              ) THEN anon_1.amount
              ELSE worksheet_adjustment.adjustment_amount
          END AS amount,
          CASE
              WHEN (
                  anon_1.worksheet_adjustment_detail_id IS NOT NULL
              ) THEN anon_1.note
              ELSE worksheet_adjustment.note
          END AS note,
          CASE
              WHEN (
                  anon_1.worksheet_adjustment_detail_id IS NOT NULL
              ) THEN anon_1.internal_note
              ELSE worksheet_adjustment.internal_note
          END AS internal_note,
          CASE
              WHEN (
                  anon_1.worksheet_adjustment_detail_id IS NOT NULL
              ) THEN anon_1.reference_adjustment_type_id
              ELSE worksheet_adjustment.reference_adjustment_type_id
          END AS reference_adjustment_type_id,
          anon_1.distribution_type AS distribution_type,
          spaf.status as status,
          spaf.valid_row_count AS valid_row_count,
          spaf.created_at AS adj_created_at,
          spaf.created_by AS adj_created_by,
          spaf.date_applied as adj_date_applied,
          spaf.approved_by as adj_approved_by
      FROM
          orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.worksheet_adjustment
          join spaf on spaf.statement_period_adjustment_file_id = worksheet_adjustment.statement_period_adjustment_file_id
          LEFT OUTER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.worksheet_adjustment_detail anon_1 ON anon_1.worksheet_adjustment_id = worksheet_adjustment.worksheet_adjustment_id;;
```