# View: dt_chargeback_reconciliation

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

## Overview

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

## Dimensions

| Name | Type |
|------|------|
| `apply_to_period_id` | number |
| `vendor_id` | number |
| `vendor_name` | string |
| `category` | string |
| `comment` | string |
| `iso_4217_code` | string |
| `id` | number |
| `currency_name` | string |
| `currency_code` | string |
| `date_added` | string |
| `created_by` | number |
| `manual_adjustment_amount` | number |
| `manual_adjustment_original_currency` | number |

## Derived Table

```sql
sql: WITH bvcs_vendor_max_period_id AS (SELECT vendor_id, MAX(period_id) AS max_period_id
      FROM ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_BOOKED_VENDOR_CONTRACT_SNAPSHOT 
      GROUP BY 1
      )

SELECT
    manual_adjustment.APPLY_TO_PERIOD_ID,
    vendor_snowflake.vendor_id,
    IFF(vendor_snowflake.company IS NULL OR vendor_snowflake.company  = '',  vendor_snowflake.name, vendor_snowflake.company)
       AS vendor_name,
    manual_adjustment_category.CATEGORY,
    manual_adjustment.COMMENT,
    bvcs_currency.ISO_4217_CODE,
    manual_adjustment.ID,
    manual_adjustment_local_currency.CURRENCY_NAME,
    manual_adjustment_local_currency.ISO_4217_CODE as currency_code,
    manual_adjustment.DATE_ADDED,
    manual_adjustment.CREATED_BY,
    COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( manual_adjustment.AMOUNT  ,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (TO_NUMBER(MD5( manual_adjustment.ID  ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(MD5( manual_adjustment.ID  ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0)) )  AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) AS manual_adjustment_amount,
    COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( IFNULL(manual_adjustment.AMOUNT_IN_ORIGINAL_CURRENCY, manual_adjustment.AMOUNT)  ,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (TO_NUMBER(MD5( manual_adjustment.ID  ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(MD5( manual_adjustment.ID  ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0)) )  AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) AS manual_adjustment_original_currency
FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS vendor_snowflake
LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_ABACUS_VENDOR_ACCOUNTING  AS vendor_accounting_snowflake ON vendor_snowflake.vendor_id = vendor_accounting_snowflake.vendor_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.period  AS vendor_accounting_period_snowflake ON vendor_accounting_period_snowflake.PERIOD_ID = vendor_accounting_snowflake.period_id
INNER JOIN ROYALTY_ACCOUNTING.PROD.VW_ABACUS_MANUAL_ADJUSTMENT  AS manual_adjustment ON vendor_snowflake.vendor_id = manual_adjustment.PARENT_ID
      AND manual_adjustment.PARENT_TYPE = 'vendor'

LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.manual_adjustment_category
       AS manual_adjustment_category ON manual_adjustment_category.CATEGORY_ID = manual_adjustment.CATEGORY_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.currencies  AS manual_adjustment_local_currency ON manual_adjustment_local_currency.ID = (IFNULL(manual_adjustment.CURRENCIES_ID, 1))
LEFT JOIN bvcs_vendor_max_period_id ON manual_adjustment.PARENT_ID = bvcs_vendor_max_period_id.vendor_id
LEFT JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_BOOKED_VENDOR_CONTRACT_SNAPSHOT AS booked_vendor_contract_snapshot ON bvcs_vendor_max_period_id.vendor_id = booked_vendor_contract_snapshot.VENDOR_ID
      AND bvcs_vendor_max_period_id.max_period_id = booked_vendor_contract_snapshot.PERIOD_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.currency_exchange_rates  AS bvcs_exchange_rates ON manual_adjustment.APPLY_TO_PERIOD_ID = bvcs_exchange_rates.PERIOD_ID
        AND (IFNULL(manual_adjustment.CURRENCIES_ID, 1)) = bvcs_exchange_rates.CURRENCY_FROM_ID
        AND booked_vendor_contract_snapshot.CURRENCY_ID = bvcs_exchange_rates.CURRENCY_TO_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.currencies  AS bvcs_currency ON bvcs_exchange_rates.CURRENCY_TO_ID = bvcs_currency.ID
WHERE (manual_adjustment.APPLY_TO_PERIOD_ID ) = {% parameter filter_period_id %
```