[WIP] Transmetro and fashionthing blanket purchase order header query

tm_ft_blanket_po_header_query

-- TODO: 
    -- return nil for multiple brand names
    -- add filters
        -- only published (look at `fashionthing.purchase_order_events.description`)
        
-- notes:
    -- filters: inner and out filters exist for the header 
    -- money: cost and price values are in lowest unit for the currency (e.g. cents for USD)
    -- exclusive brand: is_exclusive returns true if ANY merch buy is exclusive
    -- update date: an update to update date does not necessarily mean a relevant attribute changed
    
SELECT
  master_po_header.*
FROM (
    SELECT 
    ---- blanket po header attributes 
    
        -- convenience
        tm_po.id AS transmetropolitan_po_id,
        
        -- location
        ft_r.name AS procurement_bu,

        -- purchase order 
        ft_po.internal_purchase_order_number AS po_number, 
        ft_po.oracle_purchase_order_number AS bpa_number,
        CASE ft_po.inventory_model WHEN 'owned' THEN 'M' ELSE 'C' END AS po_type, 
        ft_po.shipping_terms AS freight_terms,
        ft_po.payment_terms AS payment_terms, 
        ft_po.business_line AS lob,
        ft_po.inventory_model AS inventory_type, 
        tm_po.start_ship_on AS start_ship_date, 
        tm_po.cancel_on AS cancel_ship_date,
        ft_po.eta AS eta, 
        ft_po.end_of_life AS end_of_life,
        ft_po.converted AS converted, 
        tm_po.cancelled_at AS cancel_date,
        tm_po.closed_at AS closed_date, 

        -- buy
        SUM(tm_os.unit_buy * ft_mb.unit_cost_value) AS blanket_total_amount,
        ARRAY_JOIN(ARRAY_AGG (DISTINCT(ft_mb.unit_cost_currency)), ',') AS currency,
        ARRAY_JOIN(ARRAY_AGG (DISTINCT(ft_pmb.factory_code)),',') AS factory_id,  
        ARRAY_JOIN(ARRAY_AGG (DISTINCT(ft_pmb.country_of_origin)),',') AS country_of_origin,

        -- brand
        ARRAY_JOIN(ARRAY_AGG (DISTINCT(tm_cb.name)), ',') AS supplier_brand_name, 
        CASE
        (
         SELECT DISTINCT(tm_cb.is_exclusive = ANY (
            SELECT is_exclusive
            FROM hive.transmetropolitan_production.core_brand
            WHERE tm_mb.brand_id = tm_cb.id AND is_exclusive = true  
         ))  
         FROM hive.transmetropolitan_production.core_brand tm_cb
         INNER JOIN hive.transmetropolitan_production.merch_buys tm_mb ON tm_mb.brand_id = tm_cb.id
         WHERE tm_mb.cancelled_at IS NULL AND tm_mb.purchase_order_id = tm_po.id 
            AND is_exclusive = ANY (
                SELECT is_exclusive
                FROM hive.transmetropolitan_production.core_brand
                WHERE tm_mb.brand_id = tm_cb.id AND is_exclusive = true
            ) 
        )
        WHEN true THEN true ELSE false END AS is_exclusive,
        
        -- buyer 
        tm_au.email AS buyer_email, 
    
    ---- create and update dates

        -- purchase order
        tm_po.created_at as tm_purchase_order_create_date,
        tm_po.updated_at as tm_purchase_order_update_date
        
        -- -- ordered skus
        -- tm_os.created_at as tm_ordered_sku_create_date,
        -- tm_os.updated_at as tm_ordered_sku_update_date,
        
        -- -- buy lines
        -- tm_mb.created_at as tm_merch_buy_create_date,
        -- tm_mb.updated_at as tm_merch_buy_update_date,
        -- ft_mb.created_at as ft_merch_buy_create_date,
        -- ft_mb.updated_at as ft_merch_buy_update_date,
        -- ft_pmb.created_at as ft_pre_merch_buy_create_date,
        -- ft_pmb.updated_at as ft_pre_merch_buy_update_date

---- data source tables 

    -- purchase 
    FROM "hive"."transmetropolitan_production"."purchase_orders" tm_po
    
    INNER JOIN hive.fashionthing_production.ft_purchase_orders ft_po ON ft_po.purchase_order_id = tm_po.id
    INNER JOIN hive.transmetropolitan_production.merch_buys tm_mb ON tm_mb.purchase_order_id = tm_po.id
    INNER JOIN hive.fashionthing_production.ft_merch_buys ft_mb ON ft_mb.merch_buy_id = tm_mb.id
    INNER JOIN hive.fashionthing_production.pre_merch_buys ft_pmb ON ft_pmb.merch_buy_id = tm_mb.id
    INNER JOIN hive.transmetropolitan_production.ordered_skus tm_os ON tm_os.merch_buy_id = tm_mb.id

    -- business  
    INNER JOIN hive.fashionthing_production.merch_modes ft_mm ON ft_mm.id = ft_po.merch_mode_id 
    INNER JOIN hive.fashionthing_production.regions ft_r ON ft_r.id = ft_mm.region_id
    
    -- buyer
    INNER JOIN hive.transmetropolitan_production.admin_users tm_au ON tm_au.id = tm_po.buyer_admin_user_id

    -- brand
    INNER JOIN hive.transmetropolitan_production.core_brand tm_cb ON tm_cb.id = tm_mb.brand_id
    
---- inner filters    
    WHERE 
      tm_mb.cancelled_at IS NULL 
      AND tm_po.start_ship_on >= DATE '2021-08-04'

    
    GROUP BY 
        tm_po.id,
        ft_po.oracle_purchase_order_number,
        ft_po.internal_purchase_order_number,
        ft_r.name,
        tm_au.email,
        ft_po.shipping_terms,
        ft_po.payment_terms,
        ft_po.business_line,
        ft_po.inventory_model,
        tm_po.start_ship_on,
        tm_po.cancel_on,
        ft_po.eta,
        ft_po.end_of_life,
        ft_po.converted,
        tm_po.cancelled_at,
        tm_po.closed_at,
        tm_po.created_at,
        tm_po.updated_at
      
) AS master_po_header

---- outer filters
WHERE 
    LOWER(supplier_brand_name) NOT LIKE '%nike%'

Sample 500 results (here)