Last active
May 12, 2022 21:31
-
-
Save devanmoylan/c0bd1cef0077975657a675f757eb994c to your computer and use it in GitHub Desktop.
[WIP] Transmetro and fashionthing allocated 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
-- 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' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment