# View: review_history

**View Name:** review_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_product_history/review_history.view.lkml`

## Overview

- **File Size:** 7501 bytes
- **Lines of Code:** 199
- **Dimensions:** 12
- **Measures:** 0
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |
| `product_id` | string |
| `product_name` | string |
| `event` | string |
| `action_completed_by` | string |
| `approval_or_rejection_reason` | string |
| `approval_or_rejection_note` | string |
| `escalated_to` | string |
| `escalation_type` | string |
| `move_note` | string |
| `label_id` | string |
| `label_Name` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `submitted_date` | time |
| `action_completed_date` | time |

## SQL Comments

- this consolidates all actions into one column (approved, rejected, escalated, or under investigation)
- This consolidates who did the event
- this consolidates the dates of approval ,rejection or moves

## Derived Table

```sql
sql:
    SELECT
      releases.UPC as upc,
      review_queue.product_id as product_id,
      iff(releases.release_id is not NULL, releases.release_name, 'DELETED PRODUCT') as product_name,
      v.vendor_id as label_id,
      v.name as label_name,
      review_queue.created_datetime as submitted_date,
      -- this consolidates all actions into one column (approved, rejected, escalated, or under investigation)
      CASE
        WHEN cr.COMPLETION_TYPE = 'approval' THEN 'Approved'
            WHEN cr.COMPLETION_TYPE = 'rejection' THEN 'Rejected'
            WHEN qmh.DESTINATION_QUEUE_NAME = 'escalation' THEN 'Escalated'
            WHEN qmh.DESTINATION_QUEUE_NAME = 'under_investigation' THEN 'Under Investigation'
            ELSE 'Awaiting Review'
      END AS EVENT,
      -- This consolidates who did the event
      CASE
            WHEN EVENT = 'Approved' THEN identity.name
            WHEN EVENT = 'Rejected' THEN identity.name
            WHEN EVENT = 'Escalated' THEN i2.name
            WHEN EVENT = 'Under Investigation' THEN i2.name
            ELSE NULL
        END AS ACTION_COMPLETED_BY,
      -- this consolidates the dates of approval ,rejection or moves
      CASE
            WHEN EVENT IN ('Approved', 'Rejected') AND cr.REVIEW_COMPLETED IS NOT NULL THEN cr.REVIEW_COMPLETED
            WHEN EVENT IN ('Escalated', 'Under Investigation') AND qmh.MOVED_AT IS NOT NULL THEN qmh.MOVED_AT
            ELSE NULL
        END AS ACTION_COMPLETED_DATE,

      canned_response.Reason AS APPROVAL_OR_REJECTION_REASON,
      cr.note as APPROVAL_OR_REJECTION_NOTE,
      qmh.ESCALATED_TO as escalated_to,
      t.escalation_type as escalation_type,
      qmh.move_note as move_note

    FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_queue
    LEFT JOIN (
        SELECT USER_ID, created_datetime as REVIEW_COMPLETED, review_queue_id, 'approval' as COMPLETION_TYPE, NOTE  from "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".approval
        UNION ALL
        SELECT USER_ID, created_datetime as REVIEW_COMPLETED, review_queue_id, 'rejection' as COMPLETION_TYPE, NOTE from "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".rejection
        ) as cr on review_queue.id = cr.review_queue_id
    LEFT JOIN (
        SELECT queue_move_history.review_queue_id, moved_at_datetime as MOVED_AT, queue_name as DESTINATION_QUEUE_NAME, moved_to_target_id as MOVE_TARGET_ID, moved_to_target_name as ESCALATED_TO, move_note, MOVED_TO_QUEUE_BY_USER_ID FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".queue_move_history
        ) as qmh on qmh.review_queue_id = review_queue.id
    LEFT JOIN (SELECT * FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".move_target) t on t.id = qmh.MOVE_TARGET_ID
    LEFT JOIN facts.PROD.identity as identity ON cr.user_id = identity.id
    LEFT JOIN "FACTS"."PROD".IDENTITY as i2 ON i2.ID = qmh.MOVED_TO_QUEUE_BY_USER_ID
    LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.releases releases ON review_queue.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(DISTINCT res.keyword, ',  ') WITHIN GROUP (ORDER BY res.keyword) AS 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 = review_queue.id;;
```