# View: dt_abacus_fact_sales_unified_ledger_comparison

**View Name:** dt_abacus_fact_sales_unified_ledger_comparison
**Table Source:** `Derived from: Abacus_Data, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Prod_Royalty_Accounting_Royalty_Accounting, Prod.Abacus_Fact_Sales_Unified_Dbt, Prod_Royalty_Accounting_Royalty_Accounting.Abacus_Event + 3 more`
**File Path:** `views/dt_abacus_fact_sales_unified_ledger_comparison.view.lkml`

## Overview

- **File Size:** 3413 bytes
- **Lines of Code:** 121
- **Dimensions:** 10
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `currency_code_unified` | string |
| `currency_code_raw` | string |
| `statement_period_id` | number |
| `contract_id` | number |
| `delta_significance` | string |
| `presence_status` | string |
| `currency_match_status` | string |
| `delta` | number |
| `currency_amount_raw` | number |
| `currency_amount_unified` | number |

## Derived Table

```sql
sql:
    WITH abacus_data AS (
      SELECT
          statement_period_id,
          contract_id,
          account_payee_currency AS currency_code,
          'abacus_unified' AS data_type,
          SUM(net_share_payee_currency) AS currency_amount
      FROM royalty_accounting.prod.abacus_fact_sales_unified_dbt
      GROUP BY statement_period_id, contract_id, account_payee_currency

      UNION ALL

      SELECT
          statement_period_id,
          contract_id,
          currency_code,
          'abacus_raw' AS data_type,
          currency_amount
      FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_account_contract lac
      JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae
          ON lac.abacus_event_id = ae.abacus_event_id
      WHERE event_name = 'commit_royalties'
    )

  , pivoted_data AS (
      SELECT
          statement_period_id,
          contract_id,
          MAX(CASE WHEN data_type = 'abacus_unified' THEN currency_code END) AS currency_code_unified,
          MAX(CASE WHEN data_type = 'abacus_raw' THEN currency_code END) AS currency_code_raw,
          MAX(CASE WHEN data_type = 'abacus_unified' THEN currency_amount END) AS currency_amount_unified,
          MAX(CASE WHEN data_type = 'abacus_raw' THEN currency_amount END) AS currency_amount_raw
      FROM abacus_data
      GROUP BY statement_period_id, contract_id
  )

  SELECT
      statement_period_id,
      contract_id,
      currency_code_unified,
      currency_code_raw,
      currency_amount_unified,
      currency_amount_raw,
      ROUND(currency_amount_unified - currency_amount_raw, 10) AS delta,

      CASE
          WHEN currency_amount_unified IS NULL THEN 'MISSING IN UNIFIED'
          WHEN currency_amount_raw IS NULL THEN 'MISSING IN RAW'
          WHEN ABS(currency_amount_unified - currency_amount_raw) < 0.01 THEN 'NO SIGNIFICANT DIFFERENCE'
          ELSE 'SIGNIFICANT DIFFERENCE'
      END AS delta_significance,

      CASE
          WHEN currency_amount_unified IS NULL THEN 'ONLY IN RAW'
          WHEN currency_amount_raw IS NULL THEN 'ONLY IN UNIFIED'
          ELSE 'PRESENT IN BOTH'
      END AS presence_status,

      CASE
          WHEN currency_code_unified = currency_code_raw THEN 'MATCH'
          ELSE 'MISMATCH'
      END AS currency_match_status

  FROM pivoted_data;;
```