# View: dt_abacus_remaps

**View Name:** dt_abacus_remaps
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Releases, Prod.Dim_Artist, Prod.Remaps_Physical, Prod.Vw_Abacus_Fact_Sales_Distro_V2, Prod.Vw_Dim_Abacus_Account + 3 more`
**File Path:** `views/dt_abacus_remaps.view.lkml`

## Overview

- **File Size:** 7607 bytes
- **Lines of Code:** 212
- **Dimensions:** 24
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- dimension_group: statement_month {
- label: "statement_month"
- sql: ${TABLE}.statement_month ;;

## Dimensions

| Name | Type |
|------|------|
| `account_id` | string |
| `contract_id` | string |
| `artist_name` | string |
| `orchard_upc` | string |
| `display_upc` | string |
| `manufacturer_upc` | string |
| `release_name` | string |
| `contract_name` | string |
| `accounting_period_name` | string |
| `accounting_period_status` | string |
| `account_name` | string |
| `owner` | string |
| `contract_type` | string |
| `statement_period_name` | string |
| `statement_month` | string |
| `physical_new_upc` | string |
| `physical_old_upc` | string |
| `digital_new_upc` | string |
| `digital_old_upc` | string |
| `is_physical` | string |
| `upc_lookup_digital` | number |
| `upc_lookup_physical` | number |
| `vendor_catalog_number` | string |
| `product_code` | string |

## Derived Table

```sql
sql:
    WITH stmt_db_prod_remaps AS (select *
  from STMT_DB.PROD.REMAPS
  where territory is null
  or territory = '')
SELECT
    royalty_accounting_prod_vw_dim_abacus_contract.account_id  AS account_id,
    royalty_accounting_prod_vw_dim_abacus_contract.contract_id  AS contract_id,
    facts_prod_dim_artist.artistname  AS artist_name,
    vw_abacus_fact_sales_distro.upc  AS orchard_upc,
    case when orch_app_ar_releases.display_upc = '' then null else orch_app_ar_releases.display_upc end AS display_upc,
    orch_app_ar_releases.manufacturer_upc  AS manufacturer_upc,
    orch_app_ar_releases.release_name  AS release_name,
    orch_app_ar_releases.vendor_catalog_number as vendor_catalog_number,
    orch_app_ar_releases.product_code as product_code,
    royalty_accounting_prod_vw_dim_abacus_contract.contract_name  AS contract_name,
    orch_app_ar_royalty_accounting_period.accounting_period_name  AS accounting_period_name,
    orch_app_ar_royalty_accounting_period.accounting_period_status  AS accounting_period_status,
    royalty_accounting_prod_vw_dim_abacus_account.account_name  AS account_name,
    royalty_accounting_prod_vw_dim_abacus_account.owner AS owner,
    royalty_accounting_prod_vw_dim_abacus_contract.contract_type  AS contract_type,
    orch_app_ar_royalty_statement_period.statement_period_name  AS statement_period_name,
        (TO_CHAR(DATE_TRUNC('month', DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')  ), 'YYYY-MM')) AS statement_month,
    stmt_db_prod_remaps_physical.new_upc  AS physical_new_upc,
    stmt_db_prod_remaps_physical.old_upc  AS physical_old_upc,
    stmt_db_prod_remaps.new_upc  AS digital_new_upc,
    stmt_db_prod_remaps.old_upc  AS digital_old_upc,
    IFF(pp.release_id is NULL,'N','Y') as is_physical
FROM ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_DISTRO_V2  AS vw_abacus_fact_sales_distro
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases ON vw_abacus_fact_sales_distro.upc = orch_app_ar_releases.upc
LEFT JOIN facts.prod.dim_artist  AS facts_prod_dim_artist ON vw_abacus_fact_sales_distro.artist_id = facts_prod_dim_artist.artistid
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD  AS orch_app_ar_royalty_statement_period ON vw_abacus_fact_sales_distro.statement_period_id = orch_app_ar_royalty_statement_period.statement_period_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_PERIOD  AS orch_app_ar_royalty_accounting_period ON vw_abacus_fact_sales_distro.accounting_period_id = orch_app_ar_royalty_accounting_period.accounting_period_id
LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_ACCOUNT  AS royalty_accounting_prod_vw_dim_abacus_account ON vw_abacus_fact_sales_distro.account_id= royalty_accounting_prod_vw_dim_abacus_account.account_id
LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_CONTRACT  AS royalty_accounting_prod_vw_dim_abacus_contract ON vw_abacus_fact_sales_distro.contract_id= royalty_accounting_prod_vw_dim_abacus_contract.contract_id
LEFT JOIN stmt_db_prod_remaps ON vw_abacus_fact_sales_distro.upc = stmt_db_prod_remaps.new_upc
LEFT JOIN STMT_DB.PROD.REMAPS_PHYSICAL  AS stmt_db_prod_remaps_physical ON vw_abacus_fact_sales_distro.upc = stmt_db_prod_remaps_physical.new_upc
left join orchard_app_reporting_v2.art_relations_prod_art_relations.product_physical  pp on orch_app_ar_releases.release_id = pp.release_id

WHERE ((( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) >= ((DATEADD('month', -2, DATE_TRUNC('month', CURRENT_DATE())))) AND ( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) < ((DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', CURRENT_DATE())))))))

GROUP BY
    (DATE_TRUNC('month', DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')  )),
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,

    18,
    19,
    20,
    21,
    22
ORDER BY
    11 DESC
    ;;
```