Last active
May 12, 2022 21:20
-
-
Save devanmoylan/de8b8c4731ac8bcdf13c4839a7d8fbcb to your computer and use it in GitHub Desktop.
[WIP] Transmetro and fashionthing blanket purchase order header query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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%' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Sample 500 results (here)