# View: dt_periscope_report2

**View Name:** dt_periscope_report2
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Release_Status, Art_Relations_Prod_Art_Relations.Releases, Prod_Royalty_Accounting_Royalty_Accounting.Account, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Contract + 3 more`
**File Path:** `dt_periscope_report2.view.lkml`

## Overview

- **File Size:** 7370 bytes
- **Lines of Code:** 183
- **Dimensions:** 14
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `UPC` | string |
| `release_name` | string |
| `ARTIST_NAME` | string |
| `release_status` | string |
| `complete_date` | string |
| `not_for_distribution` | string |
| `deletions` | string |
| `LABEL_ID` | number |
| `LABEL_NAME` | string |
| `LABEL_OWNER` | string |
| `SAX_INTERESTED_PARTY_ID` | string |
| `possible_contracts` | string |
| `possible_contract_terms` | string |
| `default_label_base_terms` | string |

## SQL Comments

- having min(date) > '2022-11-20'

## Derived Table

```sql
sql:

    with attached_products as (
  select to_number(attached_upcs.value) as upc
  from ORCHARD_APP_REPORTING_V2.prod_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term ct,
      Table(flatten(ct.attachments)) as attached_upcs
  where term_type = 'product'
  and ct._fivetran_deleted = false
),
attached_labels as (
  select to_number(attached_label_ids.value) as label_id
  from ORCHARD_APP_REPORTING_V2.prod_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term ct,
      Table(flatten(ct.attachments)) as attached_label_ids
  where term_type = 'label'
  and ct._fivetran_deleted = false
),
possible_contracts as (
    select ac.account_id,
    parse_json( concat('[', listagg( distinct concat(
        '"Contract Name: ', replace(c.contract_name, '"', '\\"'),
        ' | Contract Id: ', c.contract_id,
        ' | SAX terminated date: ', coalesce(to_char(agr.terminated_date), 'NULL'),
        ' | SAX notes: ',  substr(coalesce(can.notes, 'NULL'), 1, 1000),
        ' "'),
        ', '), ']')) as possible_contracts,
    parse_json( concat('[', listagg( concat(
        '"Contract Name: ', replace(c.contract_name, '"', '\\"'), ' | Contract Id: ', c.contract_id, ' | Term Id: ', ct.contract_term_id, ' | Term Type: ', ct.term_type, ' | ', array_size(ct.attachments), ' ', ct.term_type, iff(array_size(ct.attachments) > 1, 's', ''), ' | SAX Id: ' , coalesce(res.external_source_id, ''), '"'),
        ', '), ']')) as possible_contract_terms
    from orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac
    inner join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract c
        on c.contract_id = ac.contract_id and c._fivetran_deleted = false
    left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term ct
        on ct.contract_id=ac.contract_id and ct._fivetran_deleted = false
    left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_external_source res
        on ct.contract_term_id=res.parent_table_id and res._fivetran_deleted = false
        and res.parent_table_name = 'contract_term' and res.external_source != 'sax_ingestion'
    left join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_external_source res_contract
        on c.contract_id=res_contract.parent_table_id and res_contract._fivetran_deleted = false
        and res_contract.parent_table_name = 'contract' and res_contract.external_source = 'sax_agreement_id'
    left join awal_sax.sax.agreements agr on agr.id = res_contract.external_source_id
    left join awal_sax.sax.client_admin_notes can on can.agreement_id = agr.id
    where ac._fivetran_deleted = false
    group by ac.account_id
),
product_last_complete_dates as (
    select release_id, min(date) as complete_date
    from orchard_app_reporting_v2.art_relations_prod_art_relations.release_status rs
    where rs.status = 'in_content'
    group by release_id
    -- having min(date) > '2022-11-20'
),
default_label_base_terms as (
    select account_id, listagg(concat('"Contract Name: ', replace(c.contract_name, '"', '\\"'), ' | Contract Id: ', c.contract_id, ' | Contract Term Id: ', ct.contract_term_id, '"'), ', ') as default_label_base_terms
    from orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_term ct
    inner join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract c
        on c.contract_id=ct.contract_id
    inner join orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account a
        on to_varchar(a.account_id) = get(ct.attachments, 0)
    where ct.term_type = 'label'
    group by account_id
)
select DISPLAY_UPC as UPC, r.release_name, ai.name as ARTIST_NAME, r.release_date, r.release_status, plc.complete_date, r.not_for_distribution, r.deletions,
v.vendor_id as LABEL_ID, v.company as LABEL_NAME, v.owner as LABEL_OWNER, v.contact_email as SAX_INTERESTED_PARTY_ID,
dlbt.default_label_base_terms, pc.possible_contracts, pc.possible_contract_terms
from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
inner join orchard_app_reporting_v2.art_relations_prod_art_relations.project p on p.project_id=r.project_id
inner join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on ai.artist_id=p.artist_id
inner join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on p.vendor_id=v.vendor_id
inner join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor related_v on v.contact_email=related_v.contact_email
inner join possible_contracts pc on pc.account_id = related_v.vendor_id
inner join attached_labels al on al.label_id = v.vendor_id
inner join product_last_complete_dates plc on plc.release_id = r.release_id
inner join default_label_base_terms dlbt on dlbt.account_id = v.vendor_id
left join attached_products ap on ap.upc = r.upc
where related_v.owner in ('awal','AWAL-UK','AWAL-US','HIFI-AWAL')
and ap.upc is null
and plc.complete_date > (CURRENT_DATE - 30)
and array_size(pc.possible_contract_terms) > 1
and v.migrated_to_abacus and related_v.migrated_to_abacus
order by r.upc, related_v.company, related_v.contact_email, related_v.vendor_id ;;
```