# View: dt_periscope_report

**View Name:** dt_periscope_report
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Project, Art_Relations_Prod_Art_Relations.Releases, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Contract + 3 more`
**File Path:** `dt_periscope_report.view.lkml`

## Overview

- **File Size:** 6482 bytes
- **Lines of Code:** 165
- **Dimensions:** 12
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `UPC` | string |
| `release_name` | string |
| `ARTIST_NAME` | string |
| `release_status` | 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 |

## Dimension Groups

| Name | Type |
|------|------|
| `release_date` | time |

## 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
      )
      select DISPLAY_UPC as UPC, r.release_name, ai.name as ARTIST_NAME, r.release_date, r.release_status, 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,
      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
      left join attached_labels al on al.label_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 al.label_id is null
      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;;
```