# View: abacus_contract_terms

**View Name:** abacus_contract_terms
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Prod.Dim_Transactiontype, Prod_Royalty_Accounting_Royalty_Accounting.Account, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Contract + 3 more`
**File Path:** `views/abacus_contract_terms.view.lkml`

## Overview

- **File Size:** 5459 bytes
- **Lines of Code:** 194
- **Dimensions:** 28
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `ACCOUNT_ID` | number |
| `ACCOUNT_NAME` | string |
| `CONTRACT_ID` | number |
| `CONTRACT_NAME` | string |
| `CONTRACT_TYPE` | string |
| `TERM_START` | date |
| `TERM_END` | date |
| `SUMMARY_NOTE` | string |
| `GENERAL_NOTE` | string |
| `DELETED_AT` | date |
| `CONTRACT_TERM_ID` | number |
| `CONTRACT_TERM_NAME` | string |
| `TERM_TYPE` | string |
| `TERM_DELETED_AT` | string |
| `CONTRACT_TERM_CONDITION_ID` | string |
| `CONTRACT_TERM_CONDITION_NAME` | string |
| `CONDITION_TERRITORY` | string |
| `transaction_type_id` | string |
| `TERM_RATE` | string |
| `COMMISSION` | string |
| `CONDITION_DELETED_AT` | string |
| `CONTRIBUTOR_ID` | string |
| `CONTRIBUTOR` | string |
| `SCHEDULE_ID` | number |
| `SCHEDULE_NAME` | string |
| `AUTOADD` | string |
| `COMPANY_CODE` | string |
| `CLIENT_OWNER` | string |

## SQL Comments

- rspc.company_code in ('4917','4918','4919','4920')

## Derived Table

```sql
sql:
      select
  a.account_id,
  a.account_name,
  c.contract_type,
  c.contract_id,
  c.contract_name,
  c.term_start,
  c.term_end,
  c.summary_note,
  c.general_note,
  ct.deleted_at,
  ct.contract_term_id,
  ct.contract_term_name,
  ct.term_type,
ct.deleted_at term_deleted_at,
  ctc.contract_term_condition_id,
  ctc.contract_term_condition_name,
  listagg (distinct NVL(tt.value,'all'),', ') within group (order by NVL(tt.value,'all') )transaction_type_id,
LISTAGG(DISTINCT NVL(cn.value,'WW'),', ') WITHIN GROUP (ORDER BY NVL(cn.value,'WW') ) condition_territory,
  ctc.term_rate,
  ctc.commission,
  ctc.deleted_at condition_deleted_at,
nc.name contributor,
s.target_id contributor_id,
s.schedule_id,
s.schedule_name,
auto.value autoadd,
rspc.company_code,
us.f_name ||' '||us.l_name as client_owner
from orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account a
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac on ac.account_id=a.account_id
    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.contract_term ct on ct.contract_id=c.contract_id
    join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term_condition ctc on ctc.contract_term_id=ct.contract_term_id
    join (lateral flatten (input => parse_json(ctc.conditions),path=>'countries', outer=>true)) cn
    left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cy on cy.iso3166a3=cn.value
    join (lateral flatten (input => parse_json(ctc.conditions),path=>'transaction_types', outer=>true)) tt
left join facts.prod.dim_transactiontype td on td.TRANSACTIONTYPEID=tt.value
 join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_signing_entity rse on rse.reference_signing_entity_id=c.reference_signing_entity_id
 join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_sap_profit_center rspc on rse.reference_sap_profit_center_id=rspc.reference_sap_profit_center_id
left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term_schedule cts ON cts.contract_term_id = ct.contract_term_id
left JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.schedule s ON s.schedule_id=cts.schedule_id
left join facts.prod.nr_contributor nc on nc.id=s.target_id
join (lateral flatten (input => parse_json(s.conditions), outer=>true)) auto
left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor as v on a.account_id = v.VENDOR_ID
left join ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHADMIN_USERS as us on v.assigned_to = us.id

 where --rspc.company_code in ('4917','4918','4919','4920')
     term_type not like 'contribution'
    group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,19,20,21,22,23,24,25,26,27,28;;
```