[WIP] Transmetro and fashionthing allocated purchase order header query

tm_ft_allocated_po_header_query

-- TODOS: 
  -- oracle po number substring approach sufficient? improve by dropping on and after the first dash
  -- return null on Supplier Brand Name if multiple brands exist
  -- filter out non published po's
  -- talk with IT about how they plan us timestamps -- use that to drive our inclusion strat for timestamps

-- NOTES: 
  -- filters: inner and out filter exists for the header
  -- update dates: are commented out right now
  -- 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
  allocated_po_header.*
FROM (
    SELECT
        
    -- allocated po header attributes

        -- convenience
        tm_po.id AS transmetropolitan_po_id,
        
        -- location
        tm_h.code AS hizzy_name,
        ft_r.name AS procurement_bu,
        
        -- purchase order 
        CONCAT(SUBSTR(ft_po.oracle_purchase_order_number, 1, 8), '-', tm_h.code) AS po_number,
        'A' AS po_type,
        ft_po.oracle_purchase_order_number AS bpa_number,
        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,
        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_as.units_allocated * ft_mb.unit_cost_value) AS allocated_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,
        (
         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
            ) 
        ) 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,
        -- ft_po.created_at as ft_purchase_order_create_date,
        -- ft_po.updated_at as ft_purchase_order_update_date,
        
        -- -- hizzy
        -- tm_h.created_at as tm_hizzy_create_date,
        -- tm_h.updated_at as tm_hizzy_update_date,
        
        -- -- buy hizzy allocation
        -- tm_bha.created_at as tm_buy_hizzy_allocation_create_date,
        -- tm_bha.updated_at as tm_buy_hizzy_allocation_update_date,
        
        -- -- ordered and allocated skus
        -- tm_as.created_at as tm_allocated_sku_create_date,
        -- tm_as.updated_at as tm_allocated_sku_update_date,
        -- 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 

    FROM hive.transmetropolitan_production.hizzies tm_h 
    
    INNER JOIN hive.transmetropolitan_production.buy_hizzy_allocations tm_bha ON tm_bha.hizzy_id = tm_h.id
    INNER JOIN hive.transmetropolitan_production.allocated_skus tm_as ON tm_as.buy_hizzy_allocation_id = tm_bha.id
    INNER JOIN hive.transmetropolitan_production.ordered_skus tm_os ON tm_os.id = tm_as.ordered_sku_id 
    INNER JOIN hive.transmetropolitan_production.merch_buys tm_mb ON tm_mb.id = tm_os.merch_buy_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.core_brand tm_cb ON tm_cb.id = tm_mb.brand_id
    INNER JOIN hive.transmetropolitan_production.purchase_orders tm_po ON tm_po.id = tm_mb.purchase_order_id
    INNER JOIN hive.fashionthing_production.ft_purchase_orders ft_po ON ft_po.purchase_order_id = tm_po.id
    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
    INNER JOIN hive.transmetropolitan_production.admin_users tm_au ON tm_au.id = tm_po.buyer_admin_user_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,
      tm_h.code,
      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,
    --   ft_po.created_at,
    --   ft_po.updated_at,
    --   tm_h.created_at,
    --   tm_h.updated_at,
    --   tm_bha.created_at,
    --   tm_bha.updated_at,
    --   tm_as.created_at,
    --   tm_as.updated_at,
    --   tm_os.created_at,
    --   tm_os.updated_at,
    --   tm_mb.created_at,
    --   tm_mb.updated_at,
    --   ft_mb.created_at,
    --   ft_mb.updated_at,
    --   ft_pmb.created_at,
    --   ft_pmb.updated_at
    
) as allocated_po_header

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