# View: review_approval_history **View Name:** review_approval_history **Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Project, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Subaccount + 3 more` **File Path:** `views/periscope_migration/review_approval_history.view.lkml` ## Overview - **File Size:** 9385 bytes - **Lines of Code:** 276 - **Dimensions:** 19 - **Measures:** 0 - **Dimension Groups:** 2 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `id` | string | | `product_name` | string | | `product_primary_artist` | string | | `product_id` | string | | `upc` | string | | `relationship_manager` | string | | `secondary_relationship_manager` | string | | `assigned_reviewer` | string | | `actual_reviewer` | string | | `submission_type` | string | | `approval_reason` | string | | `approval_note` | string | | `account_name` | string | | `account_id` | string | | `Account_Service_Tier` | string | | `subaccount_name` | string | | `subaccount_id` | string | | `special_instructions` | string | | `version` | string | ## Dimension Groups | Name | Type | |------|------| | `submitted_time` | time | | `approval_time` | time | ## Derived Table ```sql sql: with cr_completed_reviews_with_vendor_data as ( SELECT * FROM( SELECT r.UPC, 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, cr.approval_note, rq.submission_type, rq.submission_count, 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, NOTE as APPROVAL_NOTE 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, NOTE as REJECTION_NOTE 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 cr.COMPLETION_TYPE IS NOT NULL ) ) select completed_reviews.id as id, iff(releases.release_id is not NULL, releases.release_name, 'DELETED PRODUCT') as Product_Name, artist.name as Product_Primary_Artist, releases.release_id as Product_ID, releases.upc as upc, 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, identity.name as Actual_Reviewer, completed_reviews.REVIEW_CREATED_DATETIME as SUBMITTED_TIME, INITCAP(completed_reviews.submission_type) as Submission_Type, completed_reviews.REVIEW_COMPLETED_DATETIME as Approval_Time, canned_response.Approval_Reason as Approval_Reason, completed_reviews.approval_note as approval_note, v.name as Account_Name, v.vendor_id as Account_ID, st.display_name as Account_Service_Tier, s.subaccount_name as SubAccount_Name, s.subaccount_id as SubAccount_ID, releases.special_instructions as special_instructions, releases.version as version FROM cr_completed_reviews_with_vendor_data as completed_reviews LEFT JOIN facts.PROD.identity as identity ON completed_reviews.user_id = identity.id LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.releases releases ON completed_reviews.product_id = releases.release_id LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.artist_info artist ON releases.artist_id = artist.artist_id LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.project p ON p.project_id = releases.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 = releases.subaccount_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 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 outer join FACTS.PROD.SERVICE_TIER st on st.uuid = vst.service_tier_uuid LEFT OUTER JOIN ( SELECT his.review_queue_id, LISTAGG(res.keyword, ', ') WITHIN GROUP (ORDER BY res.keyword) AS Approval_Reason, FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response res LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_category cat on res.category_ID = cat.ID LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_history his on his.CANNED_RESPONSE_ID = res.id GROUP BY his.review_queue_id ) as canned_response on canned_response.review_queue_id = completed_reviews.id WHERE Account_ID NOT IN (16160,35976, 16162) AND v.Assigned_Reviewer = 2903 AND (releases.special_instructions IS NOT NULL AND releases.special_instructions <> '' OR releases.version IS NOT NULL AND releases.version <> '') AND completed_reviews.Completion_Type = 'approval' AND identity.name IN ( 'Tristan Morris', 'Khannah Bint Saliym', 'Khannah Saliym', 'Miranda Leone', 'Tabitha Barnett', 'Tabitha Losavio', 'Nelson Liu', 'Shouvik Dey', 'Jayasoorya S J', 'Elaina Morgan', 'Joseph Oladimeji', 'Sofiko Kereselidze', 'Valentina Gilly', 'Kevin Fuccillo', 'kevin fuccillo', 'Carter Buchanan', 'Minthang Singson', 'Carter Buchanan', 'Derek Kirch', 'Kadian Wood', 'Jordan Stockdale', 'Brianna Budner-Joyce', 'Edward Edwards', 'Kervin Martinez', 'Nhien Na Pham', 'William Grandin', 'Arancha Bahillo', 'Kervin Martinez' );; ```