# View: dt_terms_of_track_exceptions

**View Name:** dt_terms_of_track_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_track_exceptions.view.lkml`

## Overview

- **File Size:** 6451 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-ACC-4996') 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-promote-track-term-to-base') 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-terminated_contracts') THEN 'sax_migration'
          WHEN UPPER(UPPER(ct.created_by)) = UPPER('sax_ingestion-batch-ACC-4921') 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 = 'track' 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;;
```