# View: rejections

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

## Overview

- **File Size:** 6019 bytes
- **Lines of Code:** 193
- **Dimensions:** 14
- **Measures:** 0
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `review_queue_id` | string |
| `product_name` | string |
| `product_primary_artist` | string |
| `product_id` | string |
| `upc` | string |
| `primary_contact` | string |
| `actual_reviewer` | string |
| `rejection_reason` | string |
| `rejection_note` | string |
| `account_name` | string |
| `account_id` | string |
| `subaccount_name` | string |
| `subaccount_id` | string |
| `special_instructions` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `submitted_time` | time |
| `rejection_time` | time |

## Derived Table

```sql
sql:
    select
        completed_reviews.review_queue_id as review_queue_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 Primary_Contact,
        identity.name as Actual_Reviewer,
        completed_reviews.submitted_time as submitted_time,
        completed_reviews.completion_time  as Rejection_Time,
        canned_response.rejection_reason as Rejection_Reason,
        completed_reviews.rejection_note as rejection_note,
        v.name as Account_Name,
        v.vendor_id as Account_ID,
        s.subaccount_name as SubAccount_Name,
        s.subaccount_id as SubAccount_ID,
        releases.special_instructions as special_instructions
    FROM (
        select
            rq.id as review_queue_id,
            rq.product_id,
            rq.created_datetime as submitted_time,
            re.note as rejection_note,
            'rejected' as resolution,
            re.created_datetime as completion_time,
            re.user_id as user_uuid
        FROM orchard_app_reporting_v2.PROD_content_review_content_review.review_queue as rq
        INNER JOIN orchard_app_reporting_v2.PROD_content_review_content_review.rejection as re ON rq.id = re.review_queue_id AND re._fivetran_deleted = false
        where rq.status = 'complete'
    ) as completed_reviews
    LEFT JOIN facts.PROD.identity as identity ON completed_reviews.user_uuid = 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 OUTER JOIN (
          SELECT
          his.review_queue_id,
          LISTAGG(DISTINCT res.keyword, ',  ') WITHIN GROUP (ORDER BY res.keyword) AS Rejection_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.review_queue_id
    AND Account_ID NOT IN (16160,35976, 16162)
    AND completed_reviews.resolution = 'rejected';;
```