# View: ownership_OA_full_delivery_history

**View Name:** ownership_OA_full_delivery_history
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Full_Deletions, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Subaccount_Royalty_Collection, Art_Relations_Prod_Art_Relations.Track + 3 more`
**File Path:** `views/ownership_OA_full_delivery_history.view.lkml`

## Overview

- **File Size:** 8421 bytes
- **Lines of Code:** 168
- **Dimensions:** 1
- **Measures:** 1
- **Dimension Groups:** 3
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `vendor_id` | string |

## Measures

| Name | Type |
|------|------|
| `count` | count |

## Dimension Groups

| Name | Type |
|------|------|
| `ingestion_date` | time |
| `date_deleted` | time |
| `delivered_date` | time |

## Derived Table

```sql
sql:
      WITH track_artist_agg AS (
          SELECT
              track_id,
              LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS track_artist
          FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist
          WHERE type = 'performer'
          GROUP BY track_id
      ),

        delivery_info AS (
        SELECT
        tuid,
        customer_name AS store_name,
        territory,
        iso_2,
        date_delivered,
        deliver_history_table,
                FROM intelligence.knr.ownership_delivery_history_aggregated
        )

        SELECT
        v.vendor_id,
        COALESCE(NULLIF(v.company, ''), v.name) AS label_name,
        v.owner as vendor_owner,
        r.label AS imprint,
        c2.name AS label_country,
        r.upc,
        r.release_name,
        ai.name AS artist_name,
        g.genre,
        r.release_date,
        tr.isrc,
        tr.id AS tuid,
        tr.track_name,
        tr.version,
        ta.track_artist,
                LEFT(tr.p_line, 4) AS pline_year,
        tr.length_minute,
        tr.length_seconds,

        /* Product flags */
        IFF(v.label_identifier IS NULL OR v.label_identifier != 'Test', 'Valid_Label', 'Invalid_Label') AS label_identifier,
        IFF(r.product_type_id = 1, 'Music', 'Not_Music') AS product_type,
        IFF(r.not_for_distribution = 'N', 'Yes', 'No') AS product_distributable,
        IFF(r.release_status = 'in_content', 'Yes', 'No') AS product_incontent,
        IFF(r.distribution_format_id IN (1, 57), 'Digital', 'Physical') AS distribution_format,
        IFF(tr.track_type = 'music', 'Yes', 'No') AS tracktype_music,

        /* Rights & metadata */
        mr.is_owner AS flag_masterrights,
        c3.name AS country_recording,
        c4.name AS nationality_originalrightsowner,
        TO_CHAR(r.ingestion_completed, 'YYYY-MM-DD') AS ingestion_date,
        IFF(r.deletions = 'Y', 'Deleted', 'Not_Deleted') AS deletion_status,
        TO_CHAR(fd.date_deleted, 'YYYY-MM-DD') AS date_deleted,

        /* Delivery status */
        IFF(di.tuid IS NOT NULL, 'Delivered', 'Not Delivered') AS delivery_status,

        /* Delivery details */
        di.store_name,
        di.territory AS delivered_territory,
        di.iso_2 AS delivered_iso_2,
        di.date_delivered AS delivered_date,
        di.deliver_history_table AS delivered_history_table

        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track tr
        LEFT JOIN track_artist_agg ta ON ta.track_id = tr.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_master_rights mr
        ON tr.id = mr.track_id
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
        ON tr.release_id = r.release_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.full_deletions fd
        ON fd.upc = r.upc
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_royalty_collection src
        ON r.subaccount_id = src.subaccount_id AND src.active = 'Y'
        left JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai
        ON r.artist_id = ai.artist_id
        INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor v
        ON ai.vendor_id = v.vendor_id
        left JOIN intelligence.dbt_prod.active_vendor_contracts vw
        ON v.vendor_id = vw.vendor_id
        LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract vc
        ON vw.vendor_contract_id = vc.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_producer_nationality pn
        ON tr.id = pn.track_id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c2
        ON v.country = c2.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c3
        ON tr.recording_country = c3.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c4
        ON pn.nationality_country_id = c4.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre g
        ON r.genre_id = g.genre_id
        LEFT JOIN delivery_info di ON di.tuid = tr.id

        WHERE
        (v.label_identifier IS NULL OR v.label_identifier != 'Test')
        AND tr.id IS NOT NULL
        AND tr.isrc IS NOT NULL AND tr.isrc != ''
        AND TRY_TO_NUMBER(LEFT(tr.p_line, 4)) BETWEEN 1900 AND 2100
        AND COALESCE(tr.length_minute, 0) + COALESCE(tr.length_seconds, 0) > 0
        ;;
```