SQLize | PHPize | SQLtest

-- STEP 0 fx table with fx_rates AS( SELECT from_cc, to_cc, round(avg(exchange_rate),4) as fx FROM O_EXCHANGE_RATES WHERE from_cc IN ('EUR','GBP','USD') AND to_cc IN ('EUR','GBP','USD') AND exchange_start_date BETWEEN TO_TIMESTAMP(DATE_SUB('${date}',40), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_SUB('${date}',10), 'YYYY-MM-DD') -- same month and year than creation date GROUP BY 1,2 ) , -- STEP 1. Actual linehaul cpu for each reactive vrid_info AS (SELECT tdi.trailer_id AS vrid ,MAX(pte.cpt) AS cpt ,MAX(CASE WHEN manifest_currency='GBP' then (ROUND(NVL(dat.reporting_base_amount,0), 4) + ROUND(NVL(dat.reporting_cancellation_amount, 0) + NVL(dat.reporting_fsc_amount, 0) + NVL(dat.reporting_detention_amount, 0) + NVL(dat.reporting_other_amount, 0), 4))*(SELECT fx FROM fx_rates WHERE from_cc='GBP' AND to_cc='USD') else (ROUND(NVL(dat.reporting_base_amount,0), 4) + ROUND(NVL(dat.reporting_cancellation_amount, 0) + NVL(dat.reporting_fsc_amount, 0) + NVL(dat.reporting_detention_amount, 0) + NVL(dat.reporting_other_amount , 0), 4))*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') end) AS truck_cost ,SUM(tdi.quantity_received) as transfer_units ,SUM(CASE WHEN tdi.TRANSFER_REQUEST_ID LIKE '%:%' THEN tdi.quantity_received ELSE 0 END) AS reactive_units ,MAX(CASE WHEN manifest_currency='GBP' then (ROUND(NVL(dat.reporting_base_amount,0), 4) + ROUND(NVL(dat.reporting_cancellation_amount, 0) + NVL(dat.reporting_fsc_amount, 0) + NVL(dat.reporting_detention_amount, 0) + NVL(dat.reporting_other_amount, 0), 4))*(SELECT fx FROM fx_rates WHERE from_cc='GBP' AND to_cc='USD') else (ROUND(NVL(dat.reporting_base_amount,0), 4) + ROUND(NVL(dat.reporting_cancellation_amount, 0) + NVL(dat.reporting_fsc_amount, 0) + NVL(dat.reporting_detention_amount, 0) + NVL(dat.reporting_other_amount , 0), 4))*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') end)/SUM(tdi.quantity_received) AS linehaul_cpu FROM D_TRANSFER_DELIVERY_ITEMS tdi LEFT JOIN elettra_finance_lh_manifest dat ON dat.vr_id=tdi.trailer_id -- make sure vrid is PK in ats table LEFT JOIN PerfectMile.D_PERFECTMILE_TRUCK_LANES_EU_HOURLY pte ON tdi.trailer_id = pte.vrid AND pte.departure_time_local BETWEEN TO_TIMESTAMP(DATE_SUB('${date}',55), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_ADD('${date}',20), 'YYYY-MM-DD') AND pte.execution_status <> 'CANCELLED' -- AND date_filter WHERE tdi.region_id = region_id AND tdi.departure_datetime BETWEEN TO_TIMESTAMP(DATE_SUB('${date}',40), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_ADD('${date}',10), 'YYYY-MM-DD') AND NOT ( (tdi.source_warehouse_id = 'XBH1' AND tdi.destination_warehouse_id = 'XUKD') OR (tdi.source_warehouse_id = 'XMP2' AND tdi.destination_warehouse_id = 'XITC') OR (tdi.source_warehouse_id = 'XMP1' AND tdi.destination_warehouse_id = 'XITD') OR (tdi.source_warehouse_id = 'XDU1' AND tdi.destination_warehouse_id = 'XDET') ) -- exclude PrimeNow FCs AND NOT (tdi.source_warehouse_id IN ('UUK1', 'UUK2', 'UUK3', 'HUK3', 'UIT1', 'UIT4', 'UNW1', 'UMC2', 'ULI1', 'ULO1', 'ULS1', 'UPO1', 'UDE6', 'USH1', 'UFR1', 'UES1', 'UES4', 'UDE3', 'UGL1', 'ULO5', 'ULO6') OR tdi.destination_warehouse_id IN ('UUK1', 'UUK2', 'UUK3', 'HUK3', 'UIT1', 'UIT4', 'UNW1', 'UMC2', 'ULI1', 'ULO1', 'ULS1', 'UPO1', 'UDE6', 'USH1', 'UFR1', 'UES1', 'UES4', 'UDE3', 'UGL1', 'ULO5', 'ULO6')) -- exclude PhotoStudio AND NOT (tdi.source_warehouse_id IN ( 'PILE', 'PILH', 'PIBC') OR tdi.destination_warehouse_id IN ( 'PILE', 'PILH', 'PIBC')) -- removed below condition as per Nitish's request. -- AND NOT (tdi.source_warehouse_id IN ('EUKA', 'EUKB', 'EUKD', 'EDEA') OR tdi.destination_warehouse_id IN ('EUKA', 'EUKB', 'EUKD', 'EDEA')) -- exclude Back to IXD AND tdi.destination_warehouse_id NOT IN ('DTM2', 'BCN2', 'BHX4') -- exclude Fresh AND NOT(tdi.source_warehouse_id IN ('LCY1', 'BER6', 'MUC6') OR tdi.destination_warehouse_id IN ('LCY1', 'BER6', 'MUC6')) -- exclude Dubai AND NOT (tdi.source_warehouse_id IN ('DXB3', 'PIAE') OR tdi.destination_warehouse_id IN ('DXB3', 'PIAE')) -- exclude Saudi AND NOT (tdi.source_warehouse_id IN ('JED6', 'PIRU', 'RUH3', 'RUH4') OR tdi.destination_warehouse_id IN ('JED6', 'PIRU', 'RUH3', 'RUH4')) -- exclude virtual transfers AND NOT((tdi.source_warehouse_id = 'ORY1' and tdi.destination_warehouse_id = 'XFRH') OR (tdi.source_warehouse_id = 'ORY1' and tdi.destination_warehouse_id = 'XFRJ') OR (tdi.source_warehouse_id = 'XRE1' and tdi.destination_warehouse_id = 'XESC') OR (tdi.source_warehouse_id = 'XDU2' and tdi.destination_warehouse_id = 'XDEZ') OR (tdi.source_warehouse_id = 'XFR2' and tdi.destination_warehouse_id = 'XDEH') OR (tdi.source_warehouse_id = 'XMA2' and tdi.destination_warehouse_id = 'XESE') OR (tdi.source_warehouse_id = 'XMA3' and tdi.destination_warehouse_id = 'XESF') OR (tdi.source_warehouse_id = 'XVA1' and tdi.destination_warehouse_id = 'XFRZ') OR (tdi.source_warehouse_id = 'XOR2' and tdi.destination_warehouse_id = 'XFRE') OR (tdi.source_warehouse_id = 'XOR6' and tdi.destination_warehouse_id = 'XFRK') OR (tdi.source_warehouse_id = 'XOR3' and tdi.destination_warehouse_id = 'XFRF') OR (tdi.source_warehouse_id = 'XLI1' and tdi.destination_warehouse_id = 'XITG') OR (tdi.source_warehouse_id = 'XLI3' and tdi.destination_warehouse_id = 'XITI') OR (tdi.source_warehouse_id = 'XAR1' and tdi.destination_warehouse_id = 'XSE1') OR (tdi.source_warehouse_id = 'XSC1' and tdi.destination_warehouse_id = 'XDEQ') OR (tdi.source_warehouse_id = 'XWR1' and tdi.destination_warehouse_id = 'XPLA') OR (tdi.source_warehouse_id = 'XDU1' and tdi.destination_warehouse_id = 'XDET') OR (tdi.source_warehouse_id = 'XFR1' and tdi.destination_warehouse_id = 'XDEY') OR (tdi.source_warehouse_id = 'XFR3' and tdi.destination_warehouse_id = 'XDEW') OR (tdi.source_warehouse_id = 'XMA1' and tdi.destination_warehouse_id = 'XESA') OR (tdi.source_warehouse_id = 'XBH2' and tdi.destination_warehouse_id = 'XUKN') OR (tdi.source_warehouse_id = 'XMP1' and tdi.destination_warehouse_id = 'XITD') OR (tdi.source_warehouse_id = 'XMP2' and tdi.destination_warehouse_id = 'XITC') OR (tdi.source_warehouse_id = 'XBH1' and tdi.destination_warehouse_id = 'XUKD') OR (tdi.source_warehouse_id = 'PNQ1' and tdi.destination_warehouse_id = 'SDEF') OR (tdi.source_warehouse_id = 'XLT2' and tdi.destination_warehouse_id = 'EUKB') OR (tdi.source_warehouse_id = 'XLT1' and tdi.destination_warehouse_id = 'EUKA') OR (tdi.source_warehouse_id = 'EUKB' and tdi.destination_warehouse_id = 'XLT2') OR (tdi.source_warehouse_id = 'EUKA' and tdi.destination_warehouse_id = 'XLT1') OR (tdi.source_warehouse_id = 'EUKD' and tdi.destination_warehouse_id = 'XPL1') OR (tdi.source_warehouse_id = 'XPL1' and tdi.destination_warehouse_id = 'EUKD') OR (tdi.source_warehouse_id = 'PNQ2' and tdi.destination_warehouse_id = 'SDEF')) GROUP BY 1 HAVING reactive_units >0)-- only trucks with reactive transfers , transships_executed AS ( SELECT SUBSTRING(tdi.transfer_request_id, 1, 19) as order_id -- ,tdi.fnsku ,tdi.source_warehouse_id AS source_fc ,tdi.destination_warehouse_id AS destination_fc ,SUM(vr.linehaul_cpu*tdi.quantity_received)/SUM(tdi.quantity_received) AS linehaul_cpu -- weighted average ,MAX(vr.vrid) AS vrid FROM D_TRANSFER_DELIVERY_ITEMS tdi LEFT JOIN vrid_info vr ON vr.vrid=tdi.trailer_id -- make sure vrid is PK in ats table WHERE tdi.region_id = region_id AND tdi.departure_datetime BETWEEN TO_TIMESTAMP(DATE_SUB('${date}',40), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_ADD('${date}',10), 'YYYY-MM-DD') AND tdi.TRANSFER_REQUEST_ID LIKE '%:%' -- REACTIVE units -- AND date_filter GROUP BY 1,2,3 ) , -- STEP 2. Full route for prod and sim full_route AS ( -- PK is shipment_archive_id and fulfillment_request_id with shipment_archive AS ( SELECT DISTINCT shipment_archive_id,fulfillment_request_id FROM denormalized_archive da WHERE 1 = 1 AND context_name in ( 'f2p_production', 'f2p_no_transships' ) ) SELECT shipment_archive_id,fulfillment_request_id ,CASE n_nodes -- no aggregation because it is at the shipment archive fulfilment request granularity WHEN 1 THEN node_1 || '>' || 'Customer' WHEN 2 THEN node_1 || '>' || node_2 || '>' || 'Customer' WHEN 3 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || 'Customer' WHEN 4 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| 'Customer' WHEN 5 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || 'Customer' WHEN 6 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || node_6 || '>' || 'Customer' WHEN 7 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || node_6 || '>' ||node_7 || '>' || 'Customer' WHEN 8 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || node_6 || '>' ||node_7 || '>' || node_8 || '>' || 'Customer' WHEN 9 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || node_6 || '>' ||node_7 || '>' || node_8 || '>' || node_9 || '>' || 'Customer' WHEN 10 THEN node_1 || '>' || node_2 || '>' || node_3 || '>' || node_4 ||'>'|| node_5 || '>' || node_6 || '>' ||node_7 || '>' || node_8 || '>' || node_9 || '>' || node_10 || '>' || 'Customer' ELSE '10+nodes' END AS full_route FROM ( SELECT shipment_archive_id ,fulfillment_request_id ,MAX(CASE WHEN true_sequence=1 THEN origin_id END) AS node_1 ,MAX(CASE WHEN true_sequence=2 THEN origin_id END) AS node_2 ,MAX(CASE WHEN true_sequence=3 THEN origin_id END) AS node_3 ,MAX(CASE WHEN true_sequence=4 THEN origin_id END) AS node_4 ,MAX(CASE WHEN true_sequence=5 THEN origin_id END) AS node_5 ,MAX(CASE WHEN true_sequence=6 THEN origin_id END) AS node_6 ,MAX(CASE WHEN true_sequence=7 THEN origin_id END) AS node_7 ,MAX(CASE WHEN true_sequence=8 THEN origin_id END) AS node_8 ,MAX(CASE WHEN true_sequence=9 THEN origin_id END) AS node_9 ,MAX(CASE WHEN true_sequence=10 THEN origin_id END) AS node_10 ,MAX(true_sequence) AS n_nodes FROM ( -- 2nd step SELECT shipment_archive_id ,fulfillment_request_id ,origin_id ,leg_sequence_number ,DENSE_RANK() OVER(PARTITION BY fulfillment_request_id,shipment_archive_id ORDER BY leg_sequence_number ASC) AS true_sequence FROM ( -- 1st step SELECT DISTINCT shipment_archive_id ,fulfillment_request_id ,leg_sequence_number ,origin_id ,row_number() over(PARTITION BY fulfillment_request_id,shipment_archive_id,origin_id ORDER BY leg_sequence_number DESC) AS row_number FROM atrops_archive a JOIN shipment_archive USING(fulfillment_request_id,shipment_archive_id) ) WHERE row_number=1 ) GROUP BY 1,2 ) group by 1,2,3) , -- STEP 3. Ship off-manifest cost by full route and ship method off_manifest AS ( -- PK is full_route and ship_method SELECT nodes AS full_route ,LEFT(route_ship_method,20) AS ship_method -- ,ship_cost_uom ,SUM(ship_cost) AS ship_cost -- manifested cost ,SUM(total_cost) AS total_cost -- total actual cost (look at query sample #6 https://w.amazon.com/bin/units_per_shipmentview/GTSFinTech/Products/Hydra/DataSources/Queries) ,SUM(total_cost)/SUM(ship_cost) AS off_manifest_ratio FROM PACKAGE_COST_EU WHERE first_insert_month BETWEEN TO_TIMESTAMP(ADD_MONTHS('${date}',-3), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_SUB('${date}',11), 'YYYY-MM-DD') -- same month and year than creation date GROUP BY 1,2) , -- STEP 4. Order bind order_binded_orders AS ( WITH outbounded AS ( SELECT DISTINCT ordering_order_id AS order_id, asin, warehouse_id FROM PerfectMile.D_PERFECTMILE_PACKAGE_ITEMS_V2_EU pki where ducsi_ship_date BETWEEN TO_TIMESTAMP(ADD_MONTHS('${date}',-2), 'YYYY-MM-DD') AND TO_TIMESTAMP(ADD_MONTHS('${date}',1), 'YYYY-MM-DD') AND NVL(is_mfn, 'Y') = 'N' AND NVL(quantity, 0) > 0 AND planned_shipment_id IS NOT NULL ) , transfers AS ( SELECT DISTINCT SUBSTRING(tdi.transfer_request_id, 1, 19) as order_id ,tdi.asin -- ,tdi.fnsku -- ,tdi.source_warehouse_id AS source_fc ,tdi.destination_warehouse_id AS warehouse_id FROM D_TRANSFER_DELIVERY_ITEMS tdi WHERE tdi.region_id = region_id AND tdi.departure_datetime BETWEEN TO_TIMESTAMP(DATE_SUB('${date}',40), 'YYYY-MM-DD') AND TO_TIMESTAMP(DATE_ADD('${date}',10), 'YYYY-MM-DD') AND tdi.TRANSFER_REQUEST_ID LIKE '%:%' -- REACTIVE units ) SELECT DISTINCT t.order_id FROM transfers t INNER JOIN outbounded o USING (order_id,asin,warehouse_id)) , -- STEP 5. Reactive profitability reactive_profitability AS ( WITH transfer_orders AS ( SELECT DISTINCT da.fulfillment_request_id, planning_date FROM denormalized_archive da INNER JOIN transships_executed te ON te.order_id=da.order_id AND te.source_fc=da.transship_from AND te.destination_fc=da.fc_name-- taking only transfer requests that got executed WHERE context_name = 'f2p_production' AND transship_from IS NOT NULL ) , reactive_details AS ( SELECT da.order_id ,planning_date -- order-specific attributes ,MAX(CASE WHEN context_name='f2p_production' THEN shipments_per_order END) AS shipments_per_order_prod ,MAX(CASE WHEN context_name='f2p_no_transships' THEN shipments_per_order END) AS shipments_per_order_sim ,MAX(CASE WHEN context_name='f2p_production' THEN shipments_per_order_real END) AS shipments_per_order_real_prod ,MAX(CASE WHEN context_name='f2p_no_transships' THEN shipments_per_order_real END) AS shipments_per_order_real_sim ,MAX(CASE WHEN context_name='f2p_production' THEN units_per_shipment END) AS units_per_shipment_prod ,MAX(CASE WHEN context_name='f2p_production' THEN units_per_shipment END) AS units_per_shipment_sim ,CASE WHEN MAX(CASE WHEN context_name='f2p_production' THEN total_units_order_real END)>1 THEN 'Multi' ELSE 'Single' END AS is_single_multi ,MAX(CASE WHEN context_name='f2p_production' AND transship_from IS NOT NULL THEN transship_from || "->" || fc_name END) AS lane ,MAX(CASE WHEN context_name='f2p_production' THEN DATEDIFF(date_trunc('DD',internal_pdd), date_trunc('DD',order_date)) END) AS c2pdd -- prod costs system, coverted to USD ,MAX(CASE WHEN context_name='f2p_production' THEN ship_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS ship_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN ship_cost_order+atrops_penalty_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS ship_cost_var_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN pick_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS pick_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN pack_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS pack_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN transship_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS transship_cost_order -- prod costs actual, already in USD ,MAX(CASE WHEN context_name='f2p_production' THEN actual_ship_cost_order END) AS actual_ship_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN actual_pick_cost_order END) AS actual_pick_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN actual_pack_cost_order END) AS actual_pack_cost_order_prod ,MAX(CASE WHEN context_name='f2p_production' THEN actual_transship_cost_order END) AS actual_transship_cost_order ,MAX(CASE WHEN context_name='f2p_production' THEN actual_transship_lh_cost_order END) AS actual_transship_lh_cost_order ,MAX(CASE WHEN context_name='f2p_production' THEN actual_transship_labor_cost_order END) AS actual_transship_labor_cost_order -- sim costs system ,MAX(CASE WHEN context_name='f2p_no_transships' THEN ship_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS ship_cost_order_sim ,MAX(CASE WHEN context_name='f2p_no_transships' THEN ship_cost_order+atrops_penalty_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS ship_cost_var_order_sim ,MAX(CASE WHEN context_name='f2p_no_transships' THEN pick_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS pick_cost_order_sim ,MAX(CASE WHEN context_name='f2p_no_transships' THEN pack_cost_order END)*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') AS pack_cost_order_sim -- sim costs system ,MAX(CASE WHEN context_name='f2p_no_transships' THEN actual_ship_cost_order END) AS actual_ship_cost_order_sim ,MAX(CASE WHEN context_name='f2p_no_transships' THEN actual_pick_cost_order END) AS actual_pick_cost_order_sim ,MAX(CASE WHEN context_name='f2p_no_transships' THEN actual_pack_cost_order END) AS actual_pack_cost_order_sim ,MAX(CASE WHEN context_name='f2p_production' THEN transship_units_order END) AS transship_units_order ,MAX(CASE WHEN context_name='f2p_production' THEN transship_units_order_real END) AS transship_units_order_real -- ,MAX(CASE WHEN context_name='f2p_production' THEN is_order_bind END) AS is_order_bind FROM ( SELECT DISTINCT da.fulfillment_request_id ,planning_date ,da.order_id ,context_name -- ,fnsku ,iaid AS asin ,transship_from ,da.fc_name -- ,te.vrid AS vrid -- attributes ,units_per_shipment ,order_date ,internal_pdd ,dense_rank() over (PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name ORDER BY da.shipment_archive_id ASC) + dense_rank() over (PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name ORDER BY da.shipment_archive_id DESC) - 1 AS shipments_per_order ,dense_rank() over (PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name,da.order_id ORDER BY da.shipment_archive_id ASC) + dense_rank() over (PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id ORDER BY da.shipment_archive_id DESC) - 1 AS shipments_per_order_real -- configured costs ,SUM(ship_cpu) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS ship_cost_order ,SUM(picking_cpu) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS pick_cost_order ,SUM(packing_cpu) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS pack_cost_order ,SUM(transship_cost) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS transship_cost_order ,SUM(atrops_penalty_cpu) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS atrops_penalty_order -- actualized costs ,SUM(ship_cpu*GREATEST(LEAST(COALESCE(off_manifest_ratio,(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD')),MaxOffManifestRatio),MinOffManifestRatio)) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_ship_cost_order ,SUM(LEAST(COALESCE(ob_vcpu*quantity,picking_cpu),MaxOBVCPU)) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_pick_cost_order ,SUM(BoxCost/units_per_shipment*quantity) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_pack_cost_order ,SUM(LEAST(COALESCE(CASE WHEN transship_from IS NOT NULL THEN (te.linehaul_cpu+coalesce(tsi.vcpu,TSIvcpuDef)+coalesce(tso.vcpu,TSOvcpuDef))*quantity ELSE 0 END,transship_cost*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') ),MaxTSCPU*quantity)) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_transship_cost_order ,SUM(CASE WHEN transship_from IS NULL THEN 0 ELSE CASE WHEN (te.linehaul_cpu+coalesce(tsi.vcpu,TSIvcpuDef)+coalesce(tso.vcpu,TSOvcpuDef))*quantity IS NULL THEN transship_cost*0.75*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') WHEN (te.linehaul_cpu+coalesce(tsi.vcpu,TSIvcpuDef)+coalesce(tso.vcpu,TSOvcpuDef))*quantity>MaxTSCPU*quantity THEN MaxTSCPU*quantity*0.75 ELSE (te.linehaul_cpu)*quantity END END) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_transship_lh_cost_order ,SUM(CASE WHEN transship_from IS NULL THEN 0 ELSE CASE WHEN (te.linehaul_cpu+coalesce(tsi.vcpu,TSIvcpuDef)+coalesce(tso.vcpu,TSOvcpuDef))*quantity IS NULL THEN transship_cost*0.25*(SELECT fx FROM fx_rates WHERE from_cc='EUR' AND to_cc='USD') WHEN (te.linehaul_cpu+coalesce(tsi.vcpu,TSIvcpuDef)+coalesce(tso.vcpu,TSOvcpuDef))*quantity>MaxTSCPU*quantity THEN MaxTSCPU*quantity*0.25 ELSE (tsi.vcpu+tso.vcpu)*quantity END END) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS actual_transship_labor_cost_order -- units ,SUM(quantity) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS total_units_order ,SUM(quantity) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.order_id,da.context_name) AS total_units_order_real ,SUM(CASE WHEN transship_from IS NOT NULL and context_name= 'f2p_production' THEN quantity ELSE 0 END) OVER(PARTITION BY da.fulfillment_request_id,da.planning_date,da.context_name, da.order_id) AS transship_units_order ,SUM(CASE WHEN transship_from IS NOT NULL and context_name= 'f2p_production' THEN quantity ELSE 0 END) OVER(PARTITION BY da.fulfillment_request_id,da.order_id,da.planning_date,da.context_name) AS transship_units_order_real -- F2P plan ,DENSE_RANK() OVER(PARTITION BY da.fulfillment_request_id,da.order_id,da.context_name ORDER BY da.planning_date DESC) AS f2p_plan FROM denormalized_archive da INNER JOIN transfer_orders USING (fulfillment_request_id, planning_date) -- only taking orders which contain a transship request in prod and that got executed -- FULL ROUTE LEFT JOIN full_route fr ON da.fulfillment_request_id=fr.fulfillment_request_id AND da.shipment_archive_id=fr.shipment_archive_id -- SHIP OFF MANIFEST LEFT JOIN off_manifest om ON LEFT(da.ship_method,20)=om.ship_method AND fr.full_route=om.full_route -- ship_method in hydra table is varchar(20), hence ship methods with more lenght get truncated which would result in ship method change 1 -- OB VCPU LEFT JOIN ob ON ob.fc_name=da.fc_name -- TS labor LEFT JOIN tso ON tso.fc=da.transship_from LEFT JOIN tsi ON tsi.fc=da.fc_name -- TS Linehaul LEFT JOIN transships_executed te ON te.order_id=da.order_id AND te.source_fc=da.transship_from AND te.destination_fc=da.fc_name WHERE context_name IN ('f2p_production', 'f2p_no_transships') ) AS da WHERE f2p_plan=1 AND atrops_penalty_order/total_units_order BETWEEN -8 AND 8 GROUP BY 1,2 HAVING lane IS NOT NULL -- filtering only order_ids which have a transship unit (we were looking at Fset data with >=1 transship, which could have order IDs without transship) ) SELECT order_id -- order attributes -- ,MAX(is_order_bind) AS is_order_bind ,MAX(is_single_multi) AS is_single_multi ,MAX(CASE WHEN shipments_per_order_prod<shipments_per_order_sim /* OR -- this is to account for splits saved for single-unit orders, since a count of boxes per order would not capture the splits saved units_per_shipment_prod>units_per_shipment_sim */ THEN 1 ELSE 0 END) AS is_splits_saved ,MAX(c2pdd) AS c2pdd -- gross benefits system ,(MAX(ship_cost_var_order_sim) - MAX(ship_cost_var_order_prod))/MAX(transship_units_order) AS ship_bpu_var_system ,(MAX(ship_cost_order_sim) - MAX(ship_cost_order_prod))/MAX(transship_units_order) AS ship_bpu_system ,(MAX(pick_cost_order_sim) - MAX(pick_cost_order_prod))/MAX(transship_units_order) AS pick_bpu_system ,(MAX(pack_cost_order_sim) - MAX(pack_cost_order_prod))/MAX(transship_units_order) AS pack_bpu_system -- gross benefits actualized ,(MAX(actual_ship_cost_order_sim) - MAX(actual_ship_cost_order_prod))/MAX(transship_units_order) AS ship_bpu_actual ,(MAX(actual_pick_cost_order_sim) - MAX(actual_pick_cost_order_prod))/MAX(transship_units_order) AS pick_bpu_actual ,(MAX(actual_pack_cost_order_sim) - MAX(actual_pack_cost_order_prod))/MAX(transship_units_order) AS pack_bpu_actual -- TS cost ,MAX(transship_cost_order)/MAX(transship_units_order) AS transship_cpu_system -- TS cost actualized ,MAX(actual_transship_cost_order)/MAX(transship_units_order) AS transship_cpu_actual ,MAX(actual_transship_lh_cost_order)/MAX(transship_units_order) AS transship_lh_cpu_actual ,MAX(actual_transship_labor_cost_order)/MAX(transship_units_order) AS transship_labor_cpu_actual -- units ,MAX(transship_units_order) AS transship_units_order ,MAX(transship_units_order_real) AS transship_units_order_real -- order bind ,0.7 AS order_bind ,MAX(order_id || " - " || planning_date) AS sample FROM reactive_details rd --LEFT JOIN order_bind_Table GROUP BY 1 HAVING pack_bpu_system IS NOT NULL -- filtering out orders with no_transship simulation not running AND ship_bpu_system BETWEEN -12 AND 12 -- filtering out potential outliers ) -- STEP 6 final select SELECT -- ,'${YYYY-MM}' as month wa.default_marketplace ,te.source_fc ,te.destination_fc /*, CASE WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<=0 THEN '0' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.1 THEN '0.1' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.2 THEN '0.2' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.3 THEN '0.3' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.4 THEN '0.4' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.5 THEN '0.5' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.6 THEN '0.6' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.7 THEN '0.7' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.8 THEN '0.8' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<0.9 THEN '0.9' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual<=1 THEN '1.0' WHEN ship_bpu_var_system+pick_bpu_actual+pack_bpu_actual-transship_cpu_actual>1 THEN '1' END AS profit_bucket */ -- ,is_single_multi ,is_splits_saved ,date_format(vr.cpt,'E') AS cpt_dow ,te.source_fc || "->" || te.destination_fc || "-" || date_format(vr.cpt,'E') || "-" || date_format(vr.cpt,'HH:mm') AS cpt_code -- ,CASE WHEN obo.order_id IS NOT NULL THEN 1 ELSE 0 END AS is_order_bind -- ,CASE WHEN c2pdd<=4 THEN CAST(c2pdd AS varchar(2)) ELSE '4+' END AS c2pdd ,MAX(vr.transfer_units) AS transfer_units ,MAX(vr.reactive_units) AS reactive_units ,MAX(vr.truck_cost) AS CPL -- benefits ,SUM(ship_bpu_system*transship_units_order_real)/SUM(transship_units_order_real) AS ship_bpu_system ,SUM(ship_bpu_var_system*transship_units_order_real)/SUM(transship_units_order_real) AS ship_bpu_var_system ,SUM(pick_bpu_system*transship_units_order_real)/SUM(transship_units_order_real) AS pick_bpu_system ,SUM(pack_bpu_system*transship_units_order_real)/SUM(transship_units_order_real) AS pack_bpu_system ,SUM(ship_bpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS ship_bpu_actual ,SUM(pick_bpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS pick_bpu_actual ,SUM(pack_bpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS pack_bpu_actual -- -- costs ,SUM(transship_cpu_system*transship_units_order_real)/SUM(transship_units_order_real) AS transship_cpu_system ,SUM(transship_cpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS transship_cpu_actual ,SUM(transship_lh_cpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS transship_lh_cpu_actual ,SUM(transship_labor_cpu_actual*transship_units_order_real)/SUM(transship_units_order_real) AS transship_labor_cpu_actual -- sample order ID ,MAX(sample) AS sample_1 ,MIN(sample) AS sample_2 ,SUM(transship_units_order_real) AS rtd_units ,date_part('week',TO_TIMESTAMP(DATE_ADD(vr.cpt,1), 'YYYY-MM-DD')) AS week -- n boxes -- n distance per box -- order bind FROM transships_executed te INNER JOIN reactive_profitability rp -- INNER to leave out 1) reactive units which did not have a no_transships sim running and 2) reactive units out of the month/time frame being considered in denorm_archive partition ON te.order_id=rp.order_id LEFT JOIN vrid_info vr ON vr.vrid=te.vrid LEFT JOIN order_binded_orders obo ON obo.order_id=te.order_id LEFT JOIN WAREHOUSE_ATTRIBUTES wa ON te.destination_fc=wa.warehouse_id AND wa.is_current=1 WHERE c2pdd>=0 AND date_format(vr.cpt,'E') is not NULL GROUP BY 1,2,3,4,5,6,24