# View: dt_video_product_builder

**View Name:** dt_video_product_builder
**Table Source:** `INTELLIGENCE.DBT_PROD.VIDEO_PRODUCT_BUILDER`
**File Path:** `dt_video_product_builder.view.lkml`

## Overview

- **File Size:** 9345 bytes
- **Lines of Code:** 307
- **Dimensions:** 36
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- derived_table: {
- WITH user_mapping AS (
- SELECT 'oa:'||id AS user_id, f_name||' '||l_name AS user_name

## Dimensions

| Name | Type |
|------|------|
| `report_type` | string |
| `release_id` | number |
| `upc` | string |
| `isrc` | string |
| `genre_id` | number |
| `Associated_Track_Id` | string |
| `product_code` | string |
| `label_id` | number |
| `label_name` | string |
| `label_manager` | string |
| `imprint` | string |
| `video_title` | string |
| `artist_name` | string |
| `channel_selection` | string |
| `type_of_video` | string |
| `version` | string |
| `new_release` | yesno |
| `submitted_date` | date |
| `release_date` | date |
| `original_release_date` | date |
| `parental_advisory` | string |
| `language_of_video_title` | string |
| `language_of_video_content` | string |
| `c_line` | string |
| `p_line` | string |
| `product_link` | string |
| `special_instructions` | string |
| `release_status` | string |
| `content_approved_by` | string |
| `content_approved_at` | date_time |
| `product_approved_by` | string |
| `product_approved_at` | date_time |
| `final_approval_by` | string |
| `automated_approver` | yesno |
| `final_approval_at` | date_time |
| `is_rights_mgmt` | yesno |

## Dimension Groups

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

## Derived Table

```sql
#    sql:
#WITH user_mapping AS (
#    SELECT 'oa:'||id AS user_id, f_name||' '||l_name AS user_name
#    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users
#),
#AssetsDT AS
#(SELECT
#    upc,
#    LISTAGG(DISTINCT asset_type_id, ',') WITHIN GROUP (ORDER BY asset_type_id DESC) AS ids
#FROM ORCHARD_APP_REPORTING_V2.REPORTSDD_DIRECT_DELIVERY.ASSET
#GROUP BY 1)
#SELECT
#    CASE
#        WHEN r.deletions = 'Y' THEN 'Deleted'
#        WHEN r.release_status = 'label_processing' THEN 'In Progress'
#        WHEN r.not_for_distribution != 'N' THEN 'Completed / Delivered'
#        WHEN dh.upc IS NOT NULL THEN 'Completed / Delivered'
#        WHEN eqd.upc IS NOT NULL THEN 'Delivery In Progress'
#        WHEN adt.ids IN('546,12,9,3','548,12,9,3') AND r.not_for_distribution = 'N' THEN 'Ready for Delivery Encoding Order'
#        WHEN (
#            r.release_status = 'in_content'
#            AND adt.ids NOT IN('546,12,9,3','548,12,9,3')
#            ) THEN 'Final Asset Creation / Ripper Transfer'
#        WHEN (
#            r.release_status = 'transfer_to_content'
#            AND pva.content_approved = TRUE
#            AND pva.final_approved_by IS NULL
#            ) THEN 'Ready for Release Approval'
#        WHEN (
#            r.release_status = 'transfer_to_content'
#            AND (pva.release_approved IS NULL OR pva.release_approved = FALSE)
#            AND (pva.content_approved IS NULL OR pva.content_approved = FALSE)
#            ) THEN 'Ready for Content Approval'
#        ELSE 'Other' END AS report_type,
#    pv.release_id,
#    pv.upc,
#    pv.isrc,
#    pv.product_code,
#    v.vendor_id AS label_id,
#    IFF(v.company IS NULL OR v.company = '', v.name, v.company) AS label_name,
#    oa.f_name||' '||oa.l_name AS label_manager,
#    pv.imprint,
#    pv.video_title,
#    ai.name AS artist_name,
#    pv.channel_selection,
#    pv.type_of_video,
#    pv.version,
#    pv.new_release,
#    pv.submitted_at AS submitted_date,
#    pv.release_date,
#    pv.original_release_date,
#    pv.parental_advisory,
#    pv.language_of_video_title,
#    pv.language_of_video_content,
#    pv.c_line_year ||' '|| pv.c_line_copyright_holder AS c_line,
#    pv.p_line_year ||' '|| pv.p_line_copyright_holder AS p_line,
#    r.not_for_distribution,
#    r.genre_id,
#    IFF(r.release_status = 'in_content',
#      'https://oa.theorchard.com/cont_mgmt/view_release.php?upc='||r.upc,
#      'https://oa.theorchard.com/cont_mgmt/edit_ex_release.php?release_id='||pv.release_id
#    ) AS product_link,
#    pv.special_instructions,
#    CASE r.release_status
#        WHEN 'in_content' THEN 'Completed'
#        WHEN 'transfer_to_content' THEN 'Submitted / In Review'
#        WHEN 'label_processing' THEN 'In Progress'
#        ELSE '' END AS release_status,
#    user1.user_name AS content_approved_by,
#    pva.content_approved_at,
#    user2.user_name AS product_approved_by,
#    pva.release_approved_at AS product_approved_at,
#    user3.user_name AS final_approval_by,
#    pva.final_approved_at
#FROM orchard_app_reporting_v2.art_relations_prod_art_relations.product_video pv
#    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.product_video_approval pva ON pv.release_id = pva.release_id
#    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON pv.release_id = r.release_id
#    left join AssetsDT adt on r.upc = adt.upc
#    LEFT JOIN (
#        SELECT DISTINCT upc FROM orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue_detail
#    ) eqd ON r.upc = eqd.upc
#    LEFT JOIN (
#        SELECT DISTINCT upc FROM orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history WHERE customer_master_master_id != 694
#    ) dh ON r.upc = dh.upc
#    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
#    INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON ai.vendor_id = v.vendor_id
#    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa ON v.assigned_to = oa.id
#    LEFT JOIN user_mapping user1 ON pva.content_approved_by = user1.user_id
#    LEFT JOIN user_mapping user2 ON pva.release_approved_by = user2.user_id
#    LEFT JOIN user_mapping user3 ON pva.final_approved_by = user3.user_id
#WHERE v.vendor_id NOT IN (16157, 25824, 16156, 7123, 16159) ;;
#
```