# View: dt_product_exceptions

**View Name:** dt_product_exceptions
**Table Source:** `Derived from: Prod.Dim_Country, Prod.Vw_Dim_Abacus_Account, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Account_Payment_Term, Prod_Royalty_Accounting_Royalty_Accounting.Contract + 3 more`
**File Path:** `views/dt_product_exceptions.view.lkml`

## Overview

- **File Size:** 10103 bytes
- **Lines of Code:** 218
- **Dimensions:** 20
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- hidden: yes

## Dimensions

| Name | Type |
|------|------|
| `account_id` | number |
| `account_name` | string |
| `contract_id` | number |
| `contract_name` | string |
| `contract_term_id` | number |
| `term_type` | string |
| `term_rate` | string |
| `list_of_country_name` | string |
| `list_of_description` | string |
| `manual_vs_sax_migration` | string |
| `term_last_modified_by_name` | string |
| `term_last_modified_by_email` | string |
| `term_created_by_name` | string |
| `term_created_by_email` | string |
| `abacus_account_url` | string |
| `abacus_contract_url` | string |
| `last_modified` | string |
| `last_modified_by` | string |
| `run_controller_id` | number |
| `product_rank` | number |

## Derived Table

```sql
sql:
      WITH royalty_accounting_prod_vw_dim_abacus_account AS (select aa.*, dc.countryname from ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_ACCOUNT aa
          left join facts.prod.dim_country dc on dc.ISO3166A3
       = aa.country_of_tax_residence
          ),
      VW_CONTRACT_DENORMALIZED AS (
      select
          ac.account_id,
          apt.currency_code,
          c.contract_id,
          ct.contract_term_id,
          ctc.contract_term_condition_id,
          iff(c.term_start is null,'1970-02-01',c.term_start) contract_term_start,
          iff(c.term_end is null,'2100-01-01',c.term_end)     contract_term_end,
          ct.term_type,
          ctc.term_rate,
          pd.label_id as term_label_id,
          pd.upc as term_upc,
          pd.isrc as term_isrc,
          cn.country_id,
          st.store_id,
          tt.transaction_type_id,
          ctc.priority,
          rcc.run_controller_id,
          sum(iff(ct.term_type='product',1,null)) over (
              partition by ac.account_id,pd.label_id,pd.upc,pd.isrc,cn.country_id,tt.transaction_type_id,st.store_id order by priority,term_rate desc
              rows between unbounded preceding and current row
          ) product_rank
      from orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.account_contract ac
      join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.contract c on c.contract_id=ac.contract_id
      join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.run_controller_contract rcc
          on rcc.contract_id=c.contract_id
      join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.contract_term ct on ct.contract_id=c.contract_id and ct.term_type='product'
      join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.contract_term_condition ctc on ctc.contract_term_id=ct.contract_term_id
      join orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.account_payment_term apt on apt.account_id=ac.account_id
      join ROYALTY_ACCOUNTING.PROD.vw_denorm_contract_term_condition_products pd on pd.contract_term_condition_id=ctc.contract_term_condition_id and pd.term_type=ct.term_type and pd.term_type= 'product'
      join ROYALTY_ACCOUNTING.PROD.vw_denorm_contract_term_condition_countries cn on cn.contract_term_condition_id=ctc.contract_term_condition_id
      join ROYALTY_ACCOUNTING.PROD.vw_denorm_contract_term_condition_transaction_types tt on tt.contract_term_condition_id=ctc.contract_term_condition_id
      join ROYALTY_ACCOUNTING.PROD.vw_denorm_contract_term_condition_stores st on st.contract_term_condition_id=ctc.contract_term_condition_id
      qualify product_rank=1 )

      SELECT
          royalty_accounting_prod_vw_contract_denormalized.term_type,
          orch_app_ar_royalty_accounting_contract_term.last_modified_by,
          facts_prod_identity.name as term_last_modified_by_name,
          facts_prod_identity.email as term_last_modified_by_email,
          royalty_accounting_prod_vw_dim_abacus_contract.contract_id,
          royalty_accounting_prod_vw_dim_abacus_contract.contract_name,
          royalty_accounting_prod_vw_dim_abacus_account.account_name ,
          royalty_accounting_prod_vw_contract_denormalized.contract_term_id,
              (TO_CHAR(DATE_TRUNC('second', orch_app_ar_royalty_accounting_contract_term.last_modified ), 'YYYY-MM-DD HH24:MI:SS')) AS last_modified,
          royalty_accounting_prod_vw_dim_abacus_account.account_id,
          CONCAT('https://abacus.theorchard.com/account/', royalty_accounting_prod_vw_dim_abacus_account.account_id) AS abacus_account_url,
          CONCAT('https://abacus.theorchard.com/contract/', royalty_accounting_prod_vw_dim_abacus_contract.contract_id) AS abacus_contract_url,
          facts_prod_identity_term_created.name as  term_created_by_name,
          facts_prod_identity_term_created.email as term_created_by_email,
          CASE
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.last_modified_by)) = UPPER('sax_ingestion') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.last_modified_by)) = UPPER('simplification-label-base-term') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.last_modified_by)) = UPPER('sax_ingestion-batch-ACC-5004') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.last_modified_by)) = UPPER('sax_ingestion-batch-ACC-4921') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.last_modified_by)) = UPPER('ACC-5022-demote-term-from-base') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.created_by)) = UPPER('sax_ingestion-batch-3') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.created_by)) = UPPER('sax_ingestion-batch-6') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.created_by)) = UPPER('sax_ingestion-terminated_contracts') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.created_by)) = UPPER('sax_ingestion-batch-ACC-4921') THEN 'sax_migration'
        WHEN UPPER(UPPER(orch_app_ar_royalty_accounting_contract_term.created_by)) = UPPER('sax_ingestion-batch-ACC-5004') THEN 'sax_migration'
        ELSE 'manual'
      END
       AS manual_vs_sax_migration,
      term_rate,
          LISTAGG(DISTINCT facts_prod_dim_transactiontype.transactiontypedesc, ' | ') WITHIN GROUP (ORDER BY facts_prod_dim_transactiontype.transactiontypedesc) AS list_of_description,
          LISTAGG(DISTINCT facts_prod_dim_country.countryname, ' | ') WITHIN GROUP (ORDER BY facts_prod_dim_country.countryname) AS list_of_country_name
      FROM VW_CONTRACT_DENORMALIZED  AS royalty_accounting_prod_vw_contract_denormalized
      LEFT JOIN royalty_accounting_prod_vw_dim_abacus_account ON royalty_accounting_prod_vw_contract_denormalized.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 royalty_accounting_prod_vw_contract_denormalized.contract_id= royalty_accounting_prod_vw_dim_abacus_contract.contract_id
      LEFT JOIN facts.prod.dim_country  AS facts_prod_dim_country ON royalty_accounting_prod_vw_contract_denormalized.country_id = facts_prod_dim_country.countryid
      LEFT JOIN facts.prod.dim_transactiontype  AS facts_prod_dim_transactiontype ON royalty_accounting_prod_vw_contract_denormalized.transaction_type_id = facts_prod_dim_transactiontype.transactiontypeid
      LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM  AS orch_app_ar_royalty_accounting_contract_term ON royalty_accounting_prod_vw_contract_denormalized.contract_term_id= orch_app_ar_royalty_accounting_contract_term.contract_term_id
      LEFT JOIN facts.prod.identity  AS facts_prod_identity ON orch_app_ar_royalty_accounting_contract_term.last_modified_by= facts_prod_identity.id
      LEFT JOIN facts.prod.identity  AS facts_prod_identity_term_created ON orch_app_ar_royalty_accounting_contract_term.created_by= facts_prod_identity_term_created.id
      WHERE ((UPPER(( royalty_accounting_prod_vw_contract_denormalized.term_type  )) = UPPER('product'))) AND (orch_app_ar_royalty_accounting_contract_term.deleted_by ) IS NULL
      GROUP BY
          (DATE_TRUNC('second', orch_app_ar_royalty_accounting_contract_term.last_modified )),
          1,
          2,
          3,
          4,
          5,
          6,
          7,
          8,
          9,
          10,
          11,
          12,
          13,
          14,
          15,
          16;;
```