# View: dms_preferred_deliverable_upcs

**View Name:** dms_preferred_deliverable_upcs
**Table Source:** `my_schema_name.tester`
**File Path:** `dms_preferred_deliverable_upcs.view.lkml`

## Overview

- **File Size:** 11717 bytes
- **Lines of Code:** 272
- **Dimensions:** 2
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 2

## Comments & Notes

- # You can specify the table name if it's different from the view name:
- sql_table_name: my_schema_name.tester ;;
- # # Define your dimensions and measures here, like this:

## Dimensions

| Name | Type |
|------|------|
| `UPC` | string |
| `status` | string |

## Filters

- `filter_upc`
- `customer_master_master_id`

## SQL Comments

- artist country

## Derived Table

```sql
sql:  select agg.upc
    , CASE WHEN  agg.artist_country_preferred_dms_id is null
      AND agg.genre_preferred_dms_id is null
      AND agg.label_preferred_dms_id is null
      AND agg.label_country_preferred_dms_id is null
      AND agg.label_priority_preferred_dms_id is null
      AND agg.marketing_priority_preferred_dms_id is null
      AND agg.sales_start_date_preferred_dms_id is null
      AND agg.subaccount_preferred_dms_id is null
      AND agg.subgenre_preferred_dms_id is null
      THEN 'Not Deliverable'
      ELSE 'Deliverable'
      END AS status
from (
select r.upc
  , ac.artist_country_id
  , ac.artist_country_preferred_dms_id
  , g.genre_id
  , g.genre_preferred_dms_id
  , l.label_id
  , l.label_preferred_dms_id
  , lc.label_country_id
  , lc.label_country_preferred_dms_id
  , lp.label_priority_id
  , lp.label_priority_preferred_dms_id
  , mp.marketing_priority_id
  , mp.marketing_priority_preferred_dms_id
  , ssd.start_date
  , ssd.sales_start_date_preferred_dms_id
  , sa.subaccount_id
  , sa.subaccount_preferred_dms_id
  , sg.subgenre_id
  , sg.subgenre_preferred_dms_id
from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r

--artist country
left join

    (select r.upc
            , dpac.artist_country_id
            , dpac.customer_master_master_id as artist_country_preferred_dms_id
    from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
    left join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on ai.artist_id=r.artist_id
    left 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.dms_preferred_artist_country dpac on dpac.artist_country_id=ai.orchard_country
    WHERE    {% condition filter_upc %
```