# View: potential_audio_infringement_audit_history

**View Name:** potential_audio_infringement_audit_history
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Cr_Completed_Ignore_Unsubmitted, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations, Prod.Company_Brand + 2 more`
**File Path:** `views/periscope_migration/potential_audio_infringement_audit_history.view.lkml`

## Overview

- **File Size:** 8221 bytes
- **Lines of Code:** 251
- **Dimensions:** 23
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `product_id` | string |
| `display_upc` | string |
| `release_name` | string |
| `potentially_infringed_artists` | string |
| `product_primary_artists` | string |
| `track_id` | string |
| `isrc` | string |
| `track_name` | string |
| `potentially_infringed_track_name` | string |
| `relationship_manager` | string |
| `secondary_relationship_manager` | string |
| `assigned_reviewer` | string |
| `account_id` | string |
| `account_name` | string |
| `potentially_infringed_label` | string |
| `owner` | string |
| `brand` | string |
| `account_service_tier` | string |
| `review_queue_id` | string |
| `actual_reviewer` | string |
| `completion_type` | string |
| `approval_note` | string |
| `rejection_note` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `review_completed_date` | time |

## Derived Table

```sql
sql:
    WITH cr_completed_ignore_unsubmitted AS (
      SELECT
        rq.ID,
        rq.PRODUCT_ID,
        rq.CREATED_DATETIME AS REVIEW_CREATED_DATETIME,
        rq.STATUS,
        rq.QUEUE_NAME,
        cr.COMPLETION_TYPE,
        cr.REVIEW_COMPLETED_DATETIME,
        cr.USER_ID,
        v.VENDOR_ID,
        s.SUBACCOUNT_ID,
        v.OWNER,
        st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER,
        rq.locked_at_datetime,
        rq.locked_until_datetime,
        rq.locked_by_user_id,
        r.sale_start_date,
        rq.submitted_by_user_id
      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REVIEW_QUEUE as rq
      INNER JOIN (
        SELECT USER_ID, REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE, CREATED_DATETIME AS REVIEW_COMPLETED_DATETIME
        FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".APPROVAL
        WHERE _fivetran_deleted=false
        UNION ALL
        SELECT USER_ID, REVIEW_QUEUE_ID, 'rejection' AS COMPLETION_TYPE, CREATED_DATETIME
        FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
        WHERE _fivetran_deleted=false
      ) as cr ON rq.ID=cr.REVIEW_QUEUE_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".RELEASES r ON rq.PRODUCT_ID=r.RELEASE_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".PROJECT p ON p.PROJECT_ID = r.PROJECT_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v ON v.VENDOR_ID=p.VENDOR_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".subaccount s ON s.SUBACCOUNT_ID=r.SUBACCOUNT_ID
      LEFT JOIN "FACTS"."PROD".VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst
      ON v.vendor_id = vst.vendor_id
      LEFT JOIN "FACTS"."PROD".SERVICE_TIER st
      ON st.uuid = vst.service_tier_uuid
      WHERE rq._fivetran_deleted=false
      AND v.label_identifier != 'Test' OR v.label_identifier IS NULL
      HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL
)


SELECT
  CR.PRODUCT_ID as product_id,
  r.display_upc as display_upc,
  r.release_name as product_name,
  lp.name as product_primary_artists,
  ARRAY_TO_STRING(f.value:artists, ', ') as potentially_infringed_artists,
  rvh.Track_id,
  t.isrc as track_isrc,
  t.track_name as track_name,
  CAST(f.value:title AS STRING) as potentially_infringed_track_name,
  DATE(cr.REVIEW_COMPLETED_DATETIME) as review_completed_date,
  concat(o.f_name, ' ', o.l_name) as relationship_manager,
  concat(o2.f_name, ' ', o2.l_name) as secondary_relationship_manager,
  concat(o3.f_name, ' ', o3.l_name) as assigned_reviewer,
  cr.vendor_ID as account_id,
  IFF(v.company is NULL OR v.company = '', v.name, v.company) as account_name,
  CAST(f.value:label AS STRING) as potentially_infringed_label,
  cr.Owner as owner,
  cb.display_name as brand,
  st.display_name as account_service_tier,
  cr.id as review_queue_id,
  INITCAP(i.NAME) as actual_reviewer,
  cr.COMPLETION_TYPE as completion_type,
  app.note as approval_note,
  rej.note as rejection_note
FROM cr_completed_ignore_unsubmitted as cr
JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history rvh on cr.id = rvh.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".rejection rej on cr.id = rej.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".approval app on cr.id = app.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".RELEASES r ON CR.PRODUCT_ID=r.RELEASE_ID
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".PROJECT p ON p.PROJECT_ID = r.PROJECT_ID
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v ON v.VENDOR_ID=p.VENDOR_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users o ON v.assigned_to = o.id
LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users o2 ON v.quarterback_label_manager = o2.id
LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users o3 ON v.assigned_reviewer = o3.id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".subaccount s ON s.SUBACCOUNT_ID=r.SUBACCOUNT_ID
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".track t ON t.id=rvh.track_id
left outer join FACTS.PROD.COMPANY_BRAND_HAS_LABEL_VENDOR cbv on cbv.vendor_id = v.vendor_id
left outer join FACTS.PROD.COMPANY_BRAND cb on cbv.company_brand_uuid = cb.uuid
left outer join FACTS.PROD.VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst on vst.vendor_id = v.vendor_id
LEFT JOIN "FACTS"."PROD".SERVICE_TIER st ON st.uuid = vst.service_tier_uuid
LEFT JOIN "FACTS"."PROD".IDENTITY as i ON i.ID=cr.USER_ID
LEFT JOIN (
    SELECT lpi.product_id, listagg(lp1.name, ', ') as name
    FROM "FACTS"."PROD".LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_PRODUCT lpi
    LEFT JOIN "FACTS"."PROD".LABEL_PARTICIPANT lp1 ON lp1.id = lpi.label_participant_id
    WHERE lpi.participated_as = 'performer'
    GROUP BY lpi.product_id
) as lp ON cr.product_id = lp.product_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history rvh2 on rvh.id = rvh2.id,
lateral flatten(input => rvh2.details, path => 'matches', outer => TRUE) f
WHERE lower(rvh.validation_code) = 'potential_audio_infringement';;
```