- view: release_deliverable_to_proper
  derived_table: 
    sql: |
          SELECT r.upc
          FROM art_relations.releases r
          INNER JOIN art_relations.artist_info ai ON r.artist_id = ai.artist_id
          INNER JOIN art_relations.vendor v ON v.vendor_id=ai.vendor_id
          LEFT JOIN art_relations.release_dms_master_restriction rdmr ON rdmr.upc = r.upc AND rdmr.distribution_type_id = 1 AND rdmr.customer_master_master_id = 696
          LEFT JOIN art_relations.subaccount_dms_master_restriction sdmr ON sdmr.`subaccount_id`=r.`subaccount_id` AND sdmr.`customer_master_master_id` = 696
          WHERE r.`not_for_distribution`<>'y'
              AND r.`deletions`<>'y'
              AND rdmr.upc IS NULL
              AND sdmr.`subaccount_id` IS NULL
              AND r.release_status = "in_content"
              AND ai.vendor_id IN 
          
                  (SELECT v.vendor_id
                  FROM art_relations.vendor v
                  INNER JOIN art_relations.vendor_contract vcc ON v.vendor_id=vcc.vendor_id
                  LEFT JOIN art_relations.vendor_contract vcc2 ON vcc.vendor_id = vcc2.vendor_id AND vcc2.cont_start <= NOW()
                                                                                  AND ( vcc2.contract_type = 'per_release_term' AND
                                                                                                  ( vcc2.cont_start = '0000-00-00' OR vcc2.release_term <=0 OR ( vcc2.cont_start + INTERVAL vcc2.release_term MONTH) > NOW() )
                                                                                  OR ( (vcc2.contract_type <> 'per_release_term' OR vcc2.contract_type IS NULL) AND (vcc2.cont_end >= NOW() OR vcc2.cont_end IS NULL OR vcc2.cont_end = '0000-00-00') )
                                                                                  )
                                                                   AND vcc.cont_start < vcc2.cont_start
                  WHERE vcc2.id IS NULL  AND vcc.cont_start <= NOW()
                  AND ( vcc.contract_type = 'per_release_term' AND
                                  ( vcc.cont_start = '0000-00-00' OR vcc.release_term <=0 OR ( vcc.cont_start + INTERVAL vcc.release_term MONTH) > NOW() )
                  OR ( (vcc.contract_type <> 'per_release_term' OR vcc.contract_type IS NULL) AND (vcc.cont_end >= NOW() OR vcc.cont_end IS NULL OR vcc.cont_end = '0000-00-00') ))
                  AND v.vendor_id NOT IN 
          
                              (SELECT v.vendor_id
                              FROM art_relations.vendor v
                              INNER JOIN art_relations.vendor_contract vcc ON v.vendor_id=vcc.vendor_id
                              LEFT JOIN art_relations.vendor_contract vcc2 ON vcc.vendor_id = vcc2.vendor_id AND vcc2.cont_start <= NOW()
                                                                                              AND ( vcc2.contract_type = 'per_release_term' AND
                                                                                                              ( vcc2.cont_start = '0000-00-00' OR vcc2.release_term <=0 OR ( vcc2.cont_start + INTERVAL vcc2.release_term MONTH) > NOW())
                                                                                              OR ( (vcc2.contract_type <> 'per_release_term' OR vcc2.contract_type IS NULL) AND (vcc2.cont_end >= NOW() OR vcc2.cont_end IS NULL OR vcc2.cont_end = '0000-00-00') )
                                                                                              )
                                                                               AND vcc.cont_start < vcc2.cont_start
                              LEFT JOIN art_relations.vendor_dms_master_restriction vdmr ON vdmr.vendor_contract_id = vcc.id
                              LEFT JOIN art_relations.customer_master_master cmm ON cmm.customer_master_master_id =vdmr.customer_master_master_id
                              WHERE vcc2.id IS NULL  AND vcc.cont_start <= NOW()
                              AND ( vcc.contract_type = 'per_release_term' AND
                                              ( vcc.cont_start = '0000-00-00' OR vcc.release_term <=0 OR ( vcc.cont_start + INTERVAL vcc.release_term MONTH) > NOW())
                              OR ( (vcc.contract_type <> 'per_release_term' OR vcc.contract_type IS NULL) AND (vcc.cont_end >= NOW() OR vcc.cont_end IS NULL OR vcc.cont_end = '0000-00-00') ))
                              AND vdmr.customer_master_master_id = 696
                              AND vdmr.distribution_type_id = 1
                              GROUP BY v.vendor_id)
          
          AND v.status = 'signed'
          GROUP BY v.vendor_id);;


      

  fields:

#   - filter: filter_customer_master_master_id
#     type: number

  - dimension: upc
    label: Deliverable to Proper? (Yes = Not Null)
    type: string
    sql: ${TABLE}.upc