[WIP] Transmetro and fashionthing blanket purchase order line query

tm_ft_blanket_po_line_query

-- TODOS: 
  -- update item type data source to be transmetro
  -- filter out nike
  -- filter out not published po's

-- NOTES: 
  -- cost and price values are in lowest unit for the currency (e.g. cents for USD)
  -- size spectrum: if an issue of missing value, might have to fall back `style.merch_mode_object.size_spectrums.first`
  -- primary client focus: if an issue of missing value, might have to default to 'general' 

SELECT
  po_line.*
FROM (
    SELECT
    ---- po line attributes
    
        -- purchase order
        tm_po.id AS transmetropolitan_po_id,
        ft_po.internal_purchase_order_number AS po_number, 
        
        -- buy 
        CASE WHEN tm_mb.cancelled_at IS NULL THEN false ELSE true END AS cancel_flag,
        tm_mb.brand_sku AS brand_sku,
        ft_mb.commodity_code AS commodity_code,
        ft_mb.unit_cost_value AS cost,
        ft_mb.initial_retail_price_value AS initial_retail_price_value,
        
        -- sku 
        tm_os.sku_id AS sku_id,
        tm_os.unit_buy AS quantity,
        tm_os.unit_buy * ft_mb.unit_cost_value AS total_cost, 
        ft_os.vendor_part_number AS vendor_part_number, 
        ft_os.universal_product_code AS universal_product_code, 
        ft_os.european_article_number AS european_article_number, 
        'Ea' AS uom,

        -- item type
        tm_mb.item_type_name AS class,
        ft_it_department.name AS department,
        ft_it_division.name AS division,
        ft_it_mi.name AS merch_intent,
        ft_it_bl.name AS gender_intent, 
        
        -- style
        ft_ft_sv.price_override_value AS price_value,
        ft_ft_s.retail_price_value AS retail_price_value, 
        ft_ft_sv.size_spectrum AS size_spectrum,
        ft_ft_sv.primary_client_focus AS primary_client_focus, 
        JSON_EXTRACT_SCALAR(ft_ft_sv.characteristics, '$["seasonal_intent"]') AS seasonal_intent,
        ft_ft_sv.business_group AS business_group,
        
    -- create and update dates
        
        -- ordered sku
        tm_os.created_at as tm_ordered_sku_create_date,
        tm_os.updated_at as tm_ordered_sku_update_date,
        ft_os.created_at as ft_ordered_sku_create_date,
        ft_os.updated_at as ft_ordered_sku_update_date,
        
        -- merch buy
        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,
        
        -- 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,
    
        -- style
        ft_s.created_at as ft_style_create_date,
        ft_s.updated_at as ft_style_update_date,
        ft_ft_s.created_at as ft_ft_style_create_date,
        ft_ft_s.updated_at as ft_ft_style_update_date,
        
        -- style variant
        ft_sv.created_at as ft_style_variant_create_date,
        ft_sv.updated_at as ft_style_variant_update_date,

        -- item type
        ft_it.created_at as ft_item_type_create_date,
        ft_it.updated_at as ft_item_type_update_date
    
---- data source tables
    
    -- subject: blanket po line 
    FROM hive.transmetropolitan_production.ordered_skus tm_os
    
    -- purchase attributes
    INNER JOIN hive.fashionthing_production.ft_ordered_skus ft_os ON ft_os.ordered_sku_id = tm_os.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.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
    
    -- styles attributes
    INNER JOIN hive.fashionthing_production.styles ft_s ON ft_s.id = tm_mb.style_id
    INNER JOIN hive.fashionthing_production.ft_styles ft_ft_s ON ft_ft_s.style_id = ft_s.id
    INNER JOIN hive.fashionthing_production.style_variants ft_sv ON ft_sv.id = tm_mb.style_variant_id
    INNER JOIN hive.fashionthing_production.ft_style_variants ft_ft_sv ON ft_ft_sv.style_variant_id = ft_sv.id
    
    -- item type attributes 
    INNER JOIN hive.fashionthing_production.item_types ft_it ON ft_it.id = ft_s.item_type_id
    INNER JOIN hive.fashionthing_production.ft_item_types ft_ft_it ON ft_ft_it.item_type_id = ft_it.id
    INNER JOIN hive.fashionthing_production.item_types ft_it_department ON ft_it_department.id = ft_it.parent_id
    INNER JOIN hive.fashionthing_production.item_types ft_it_division ON ft_it_division.id = ft_it_department.parent_id
    INNER JOIN hive.fashionthing_production.item_types ft_it_bl ON ft_it_bl.id = ft_it_division.parent_id
    INNER JOIN hive.fashionthing_production.item_types ft_it_mi ON ft_it_mi.id = ft_it_bl.parent_id
    
---- filters 
    WHERE 
        tm_po.start_ship_on >= DATE '2021-08-04'
    
) as po_line

Sample of 500 results (here)