# View: dt_manual_adjustment_flatten

**View Name:** dt_manual_adjustment_flatten
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Currency_Exchange_Rates, Art_Relations_Prod_Art_Relations.Manual_Adjustment_Category, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations, Prod.Vw_Abacus_Manual_Adjustment + 3 more`
**File Path:** `dt_manual_adjustment_flatten.view.lkml`

## Overview

- **File Size:** 6919 bytes
- **Lines of Code:** 182
- **Dimensions:** 11
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `accounting_period_id` | number |
| `label_id` | number |
| `fx_currency_id` | number |
| `fx_reserves_withheld` | number |
| `usd_reserves_withheld` | number |
| `fx_reserves_released` | number |
| `usd_reserves_released` | number |
| `fx_recharges` | number |
| `usd_recharges` | number |
| `fx_other` | number |
| `usd_other` | number |

## SQL Comments

- Reserves Withheld
- Reserves Released <= 235
- Reserves Released > 235

## Derived Table

```sql
sql:
WITH currency_exchange_rates AS (
    SELECT
        period_id,
        currency_from_id,
        currency_to_id,
        exchange_rate,
        IFF(period_id <= 235, currency_to_id, currency_from_id) AS r_map
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.currency_exchange_rates
),
mac_mappings AS (
    SELECT
        category_id,
        CASE
            WHEN category_id = 68 THEN 'withheld'
            WHEN category_id = 69 THEN 'released'
            WHEN category_id IN (1,22,30,31,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,62,63,65,71,74,75,76,77,78,79,80,81,82,83,84)
              THEN 'recharges'
            WHEN category_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24,25,26,27,28,29,32,33,49,50,51,52,53,54,55,56,57,58,59,60,61,64,66,67,70,72,73)
              THEN 'other'
            END AS mac_mapping
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.manual_adjustment_category
),
manual_adjustments AS (
    SELECT
        apply_to_period_id,
        label_id,
        fx_currency,
        ROUND(MAX(fx_withheld), 4) AS fx_withheld,
        ROUND(MAX(fx_released), 4) AS fx_released,
        ROUND(MAX(fx_recharges), 4) AS fx_recharges,
        ROUND(MAX(fx_other), 4) AS fx_other,
        ROUND(MAX(usd_withheld), 4) AS usd_withheld,
        ROUND(MAX(usd_released), 4) AS usd_released,
        ROUND(MAX(usd_recharges), 4) AS usd_recharges,
        ROUND(MAX(usd_other), 4) AS usd_other
    FROM (
        SELECT
            apply_to_period_id,
            label_id,
            fx_currency,
            IFF(mac_mapping = 'withheld', fx_adjustment, 0) AS fx_withheld,
            IFF(mac_mapping= 'released', fx_adjustment, 0) AS fx_released,
            IFF(mac_mapping = 'recharges', fx_adjustment, 0) AS fx_recharges,
            IFF(mac_mapping = 'other', fx_adjustment, 0) AS fx_other,
            IFF(mac_mapping = 'withheld', usd_adjustment, 0) AS usd_withheld,
            IFF(mac_mapping= 'released', usd_adjustment, 0) AS usd_released,
            IFF(mac_mapping = 'recharges', usd_adjustment, 0) AS usd_recharges,
            IFF(mac_mapping = 'other', usd_adjustment, 0) AS usd_other
      FROM (
            SELECT
                mm.mac_mapping,
                ma.apply_to_period_id,
                ma.parent_id AS label_id,
                CASE
                    WHEN mm.mac_mapping = 'withheld' THEN cer1.currency_from_id
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id <= 235 THEN cer2.currency_to_id
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id > 235 THEN cer3.currency_from_id
                    WHEN mm.mac_mapping = 'recharges' THEN cer2.currency_to_id
                    WHEN mm.mac_mapping = 'other' THEN cer2.currency_to_id
                END AS fx_currency,
                CASE
                    WHEN mm.mac_mapping = 'withheld' THEN SUM(ma.amount / cer1.exchange_rate)
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id <= 235 THEN SUM(ma.amount * cer2.exchange_rate)
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id > 235 THEN SUM(ma.amount / cer3.exchange_rate)
                    WHEN mm.mac_mapping = 'recharges' THEN SUM(ma.amount * cer2.exchange_rate)
                    WHEN mm.mac_mapping = 'other' THEN SUM(ma.amount * cer2.exchange_rate)
                END AS fx_adjustment,
                SUM(ma.amount) AS usd_adjustment
            FROM ROYALTY_ACCOUNTING.PROD.VW_ABACUS_MANUAL_ADJUSTMENT ma
                INNER JOIN mac_mappings mm ON ma.category_id = mm.category_id
                INNER JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_BOOKED_VENDOR_CONTRACT_SNAPSHOT bvcs
                    ON ma.apply_to_period_id = bvcs.period_id
                    AND ma.parent_id = bvcs.vendor_id
                LEFT JOIN currency_exchange_rates cer1 -- Reserves Withheld
                    ON cer1.period_id = bvcs.period_id
                    AND cer1.currency_to_id = 1
                    AND cer1.currency_from_id = bvcs.currency_id
                LEFT JOIN currency_exchange_rates cer2 -- Reserves Released <= 235
                    ON cer2.period_id = ma.apply_to_period_id
                    AND cer2.currency_to_id = bvcs.currency_id
                    AND cer2.currency_from_id = 1
                LEFT JOIN currency_exchange_rates cer3 -- Reserves Released > 235
                    ON cer3.period_id = ma.adjust_for_period_id
                    AND cer3.currency_to_id = 1
                    AND cer3.currency_from_id = bvcs.currency_id
            WHERE ma.parent_type = 'vendor'
            GROUP BY 1,2,3,4
        )
    ) GROUP BY 1,2,3
)
SELECT
    apply_to_period_id AS accounting_period_id,
    label_id,
    IFNULL(fx_currency, 1) AS fx_currency_id,
    COALESCE(fx_withheld, 0) AS fx_reserves_withheld,
    COALESCE(usd_withheld, 0) AS usd_reserves_withheld,
    COALESCE(fx_released, 0) AS fx_reserves_released,
    COALESCE(usd_released, 0) AS usd_reserves_released,
    COALESCE(fx_recharges, usd_recharges) AS fx_recharges,
    COALESCE(usd_recharges, 0) AS usd_recharges,
    COALESCE(fx_other, 0) AS fx_other,
    COALESCE(usd_other, 0) AS usd_other
FROM manual_adjustments
WHERE {% condition accounting_period_id %
```