[WIP] Transmetro and fashionthing allocated purchase order line query

tm_ft_allocated_po_line_query

-- TODOS: 
    -- filter out nike
    -- filter out unpublished
    -- improve allocated po number splitting 
    -- update item type data source to be transmetro

-- NOTES: 
    -- money: cost and price values are in lowest unit for the currency (e.g. cents for USD)
    -- update date: a revision to update date does not necessarily mean a relevant attribute changed; but instead something on the data source changed

SELECT
  po_line.*
FROM (
    SELECT
    ---- po line attributes

        -- sku
        tm_as.sku_id AS sku_id,
        tm_as.units_allocated AS quantity,
        tm_as.units_allocated * 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,
        
        -- 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,
        
        -- purchase order
        CONCAT(SUBSTR(ft_po.oracle_purchase_order_number, 1, 8), '-', tm_h.code) AS po_number, 

        -- item type 
        ft_it.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

        -- sku
        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,
        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 sources

    -- subject: allocated po line    
    FROM hive.transmetropolitan_production.allocated_skus tm_as
    
    -- hizzy attributes
    INNER JOIN hive.transmetropolitan_production.buy_hizzy_allocations tm_bha ON tm_bha.id = tm_as.buy_hizzy_allocation_id
    INNER JOIN hive.transmetropolitan_production.hizzies tm_h ON tm_h.id = tm_bha.hizzy_id
    
    -- purchase attributes
    INNER JOIN hive.transmetropolitan_production.ordered_skus tm_os ON tm_os.id = tm_as.ordered_sku_id 
    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
    
    
---- filtering 
    WHERE
        tm_po.start_ship_on >= DATE '2021-08-04' 

) as po_line

Copy and paste into https://yanagishima.daylight.stitchfix.com/#datasource=prod-presto&engine=presto&tab=treeview for testing.