# View: dt_terms_of_product_exceptions

**View Name:** dt_terms_of_product_exceptions
**Table Source:** `Derived from: Prod.Dim_Country, Prod.Dim_Transactiontype, Prod.Vw_Denorm_Contract_Term_Condition_Countries, Prod.Vw_Denorm_Contract_Term_Condition_Transaction_Types, Prod.Vw_Dim_Abacus_Account + 3 more`
**File Path:** `views/dt_terms_of_product_exceptions.view.lkml`

## Overview

- **File Size:** 6390 bytes
- **Lines of Code:** 167
- **Dimensions:** 18
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## 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 |

## Derived Table

```sql
sql:
SELECT distinct
            ct.term_type,
          ct.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 ,
          ct.contract_term_id,
          (TO_CHAR(DATE_TRUNC('second', ct.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(ct.last_modified_by)) = UPPER('sax_ingestion') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.last_modified_by)) = UPPER('simplification-label-base-term') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.last_modified_by)) = UPPER('sax_ingestion-batch-ACC-5004') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.last_modified_by)) = UPPER('sax_ingestion-batch-ACC-4921') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.last_modified_by)) = UPPER('ACC-5022-demote-term-from-base') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.created_by)) = UPPER('sax_ingestion-batch-3') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.created_by)) = UPPER('sax_ingestion-batch-6') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.created_by)) = UPPER('sax_ingestion-terminated_contracts') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.created_by)) = UPPER('sax_ingestion-batch-ACC-4921') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.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 ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM ct
JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM_CONDITION ctc on ct.contract_term_id = ctc.contract_term_id
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
LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_CONTRACT  AS royalty_accounting_prod_vw_dim_abacus_contract ON royalty_accounting_prod_vw_dim_abacus_contract.contract_id= ct.contract_id
LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_ACCOUNT as royalty_accounting_prod_vw_dim_abacus_account ON royalty_accounting_prod_vw_dim_abacus_contract.account_id= royalty_accounting_prod_vw_dim_abacus_account.account_id
LEFT JOIN facts.prod.dim_country  AS facts_prod_dim_country ON cn.country_id = facts_prod_dim_country.countryid
LEFT JOIN facts.prod.dim_transactiontype  AS facts_prod_dim_transactiontype ON tt.transaction_type_id = facts_prod_dim_transactiontype.transactiontypeid
LEFT JOIN facts.prod.identity  AS facts_prod_identity ON ct.last_modified_by= facts_prod_identity.id
LEFT JOIN facts.prod.identity  AS facts_prod_identity_term_created ON ct.created_by= facts_prod_identity_term_created.id
WHERE ct.deleted_by IS NULL
AND ct.contract_id IN (select distinct contract_id from ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM ct where term_type = 'product' and deleted_by is NULL)
GROUP BY
    (DATE_TRUNC('second', ct.last_modified )),
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16;;
```