· 4 years ago · Feb 23, 2021, 07:46 PM
1set hive.groupby.orderby.position.alias=true;
2
3DROP TABLE IF EXISTS adhoc_analysis.ffoms_current;
4DROP TABLE IF EXISTS adhoc_analysis.ffoms_historical;
5
6CREATE table adhoc_analysis.ffoms_historical AS
7(SELECT channel_order_id,
8MAX(CASE WHEN reason = 'UCODE_MAPPING_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END) AS catalog_issue1,
9MAX(CASE WHEN reason = 'TECH_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END) AS tech_issue1,
10MAX(CASE WHEN reason = 'RETAILER_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END) AS retailer_issue1,
11MAX(CASE WHEN fo.status = 'INVOICED' THEN 1 ELSE 0 END) AS store_invoiced_historical,
12MAX(CASE WHEN fo.status = 'WH_INVOICED' THEN 1 ELSE 0 END) AS customer_invoiced_historical,
13MAX(CASE WHEN fo.status = 'PENDING_ORDER_CREATION' AND reason not in ('TECH_ISSUE','RETAILER_ISSUE','UCODE_MAPPING_ISSUE') THEN 1 ELSE 0 END) AS other_tech_issue1
14FROM (SELECT channel_order_id, reason, status FROM pe_mercury_ffoms.fulfilment_order_journal where to_date(created_on) >= '2021-01-14') fo
15GROUP BY channel_order_id);
16
17CREATE table adhoc_analysis.ffoms_current AS (
18SELECT channel_order_id, to_date(from_utc_timestamp(created_on,'IST')) AS ffoms_create_date, channel, courier_order, retailer_id, warehouse_order_id, tenant, fo.status,
19CASE WHEN reason = 'UCODE_MAPPING_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END AS catalog_issue2,
20CASE WHEN reason = 'TECH_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END AS tech_issue2,
21CASE WHEN reason = 'RETAILER_ISSUE' AND fo.status IN ('PENDING_ORDER_CREATION','DRAFT') THEN 1 ELSE 0 END AS retailer_issue2,
22CASE WHEN fo.status = 'PENDING_ORDER_CREATION' AND reason not in ('TECH_ISSUE','RETAILER_ISSUE','UCODE_MAPPING_ISSUE') THEN 1 ELSE 0 END AS other_tech_issue2,
23CASE WHEN so.external_order_id IS NOT NULL and pt.reference_id IS NULL AND fo.status NOT IN ('CANCELLED','PENDING_FOR_STOCK') THEN 1 ELSE 0 END AS paper_picking_issue,
24CASE WHEN so.status = 'RECEIVED_AT_STORE' THEN 1 ELSE 0 END AS received_at_store,
25CASE WHEN so.status = 'PENDING_FOR_STOCK' THEN 1 ELSE 0 END AS jit_current,
26CASE WHEN upper(so.status) like '%CANCELLED%' THEN 1 ELSE 0 END AS cancelled_mercury_current,
27CASE WHEN upper(so.status) in ('READY_FOR_PICKING','PICKING_IN_PROGRESS','READY_FOR_BILLING','BILLING_IN_PROGRESS') THEN 1 ELSE 0 END AS rfp_to_prestorebill,
28CASE WHEN so.status = 'STORE_INVOICE_GENERATED' THEN 1 ELSE 0 END AS store_invoiced_current,
29CASE WHEN so.status = 'CUSTOMER_INVOICE_GENERATED' THEN 1 ELSE 0 END AS customer_invoiced_current
30FROM (SELECT channel_order_id, reason, status, warehouse_order_id, created_on, channel, courier_order, retailer_id, tenant FROM pe_mercury_ffoms.fulfilment_order_snapshot where to_date(created_on) >= '2021-01-14') fo
31LEFT JOIN (SELECT id, external_order_id, status FROM pe_mercury_thea.sale_order_snapshot where dt >= '2021-01-14') so ON so.external_order_id = fo.warehouse_order_id
32LEFT JOIN (SELECT distinct reference_id FROM pe_mercury_thea.picker_task_snapshot WHERE dt >= '2021-01-14') pt ON pt.reference_id = fo.warehouse_order_id
33);
34--ORDER FUNNEL:
35--Two issues remaining- 1. Mercury_to_flash_sum is > flash_created_ orders (due to incorrect numbers in either rfp_to_billing_in_progress or cancelled_after_rfp_before_cx)
36-- 2. open_status_damaged_lost numbers not coming correctly
37
38
39DROP TABLE IF EXISTS reporting_batch.ffoms_order_funnel_data;
40
41CREATE TABLE reporting_batch.ffoms_order_funnel_data AS SELECT
42CAST('warehouse_city' AS VARCHAR(50)) warehouse_city,
43CAST('ffoms_create_date' AS VARCHAR(50)) ffoms_create_date,
44CAST('courier_order' AS VARCHAR(50)) courier_order,
45CAST('flash_payment_mode' AS VARCHAR(50)) flash_payment_mode,
46CAST('ffoms_orders' AS VARCHAR(50)) ffoms_orders,
47CAST('mercury_orders_created' AS VARCHAR(50)) mercury_orders_created,
48CAST('total_pending_issues' AS VARCHAR(50)) total_pending_issues,
49CAST('cancelled_in_ffoms' AS VARCHAR(50)) cancelled_in_ffoms,
50CAST('jit_before_pickable' AS VARCHAR(50)) jit_before_pickable,
51CAST('jit_after_pickable' AS VARCHAR(50)) jit_after_pickable,
52CAST('cancelled_before_rfp' AS VARCHAR(50)) cancelled_before_rfp,
53CAST('rfp_to_billing_in_progress' AS VARCHAR(50)) rfp_to_billing_in_progress,
54CAST('warehouse_invoiced' AS VARCHAR(50)) warehouse_invoiced,
55CAST('received_at_store' AS VARCHAR(50)) received_at_store,
56CAST('cancelled_after_rfp_before_cx' AS VARCHAR(50)) cancelled_after_rfp_before_cx,
57CAST('retailer_invoiced' AS VARCHAR(50)) retailer_invoiced,
58CAST('mercury_to_flash_sum' AS VARCHAR(50)) mercury_to_flash_sum,
59CAST('flash_orders_created' AS VARCHAR(50)) flash_orders_created,
60CAST('flash_cancelled_before_cx' AS VARCHAR(50)) flash_cancelled_before_cx,
61CAST('flash_cancelled_after_cx' AS VARCHAR(50)) flash_cancelled_after_cx,
62CAST('open_status_pre_ofd' AS VARCHAR(50)) open_status_pre_ofd,
63CAST('open_status_damaged_lost' AS VARCHAR(50)) open_status_damaged_lost,
64CAST('dispatched_ofd' AS VARCHAR(50)) dispatched_ofd,
65CAST('delivered' AS VARCHAR(50)) delivered,
66CAST('open_status_post_ofd' AS VARCHAR(50)) open_status_post_ofd;
67
68INSERT INTO reporting_batch.ffoms_order_funnel_data
69( SELECT warehouse_city, ffoms_create_date, courier_order, flash_payment_mode, ffoms_orders, mercury_orders_created, total_pending_issues, cancelled_in_ffoms,JIT_before_pickable,JIT_after_pickable, cancelled_before_rfp,rfp_to_billing_in_progress, warehouse_invoiced, received_at_store, cancelled_after_rfp_before_cx, retailer_invoiced, jit_after_pickable + rfp_to_billing_in_progress + warehouse_invoiced + cancelled_after_rfp_before_cx + retailer_invoiced + received_at_store AS Mercury_to_flash_sum, flash_orders_created, flash_cancelled_before_cx, flash_cancelled_after_cx, open_status_pre_ofd, open_status_damaged_lost, dispatched_ofd, delivered, open_status_post_ofd
70FROM
71(
72SELECT tsm.city_name as warehouse_city, ci.ffoms_create_date, ci.courier_order,
73CASE WHEN (s.payment_type IS NOT NULL OR s.payment_mode IS NOT NULL) THEN CONCAT(s.payment_type,CONCAT('-', COALESCE(s.payment_mode,''))) ELSE 'Unavailable' END as flash_payment_mode,
74COUNT(*) AS ffoms_orders,
75SUM(CASE WHEN so.external_order_id IS NOT NULL THEN 1 ELSE 0 END) AS mercury_orders_created,
76SUM(ci.catalog_issue2 + ci.tech_issue2 + ci.retailer_issue2 + ci.other_tech_issue2 + ci.paper_picking_issue + ci.received_at_store) AS total_pending_issues,
77SUM(CASE WHEN ci.status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_in_ffoms,
78SUM(CASE WHEN ci.jit_current = 1 and ifo.min_ready_for_picking_time IS NULL THEN 1 ELSE 0 END) AS JIT_before_pickable,
79SUM(CASE WHEN ci.jit_current = 1 and ifo.min_ready_for_picking_time IS NOT NULL THEN 1 ELSE 0 END) AS JIT_after_pickable,
80SUM(CASE WHEN ci.cancelled_mercury_current = 1 AND ifo.min_ready_for_picking_time is null THEN 1 ELSE 0 END) AS cancelled_before_rfp,
81SUM(ci.rfp_to_prestorebill) AS rfp_to_billing_in_progress,
82SUM(ci.store_invoiced_current) AS warehouse_invoiced,
83SUM(ci.received_at_store) AS received_at_store,
84SUM(CASE WHEN ci.cancelled_mercury_current = 1 and ifo.min_ready_for_picking_time is not null and ifo.min_customer_invoice_generated_time is null THEN 1 ELSE 0 END) AS cancelled_after_rfp_before_cx,
85SUM(ci.customer_invoiced_current) AS retailer_invoiced,
86--Mercury metrics done
87SUM(CASE WHEN s.id IS NOT NULL THEN 1 ELSE 0 END) AS flash_orders_created,
88SUM(CASE WHEN upper(fsm.shipment_status) LIKE '%CANCEL%' and ifo.min_customer_invoice_generated_time is null THEN 1 ELSE 0 END) AS flash_cancelled_before_cx,
89SUM(CASE WHEN upper(fsm.shipment_status) LIKE '%CANCEL%' and ifo.min_customer_invoice_generated_time is not null THEN 1
90WHEN tsk.status = 'CANCELLED' THEN 1 ELSE 0 END) AS flash_cancelled_after_cx,
91SUM(CASE WHEN fsm.shipment_id IS NOT NULL and fsm.first_ofd_time is null and upper(fsm.shipment_status) IN ('CREATED','READY_FOR_DELIVERY') THEN 1 ELSE 0 END) AS open_status_pre_ofd,
92SUM(CASE WHEN upper(fsm.shipment_status) NOT LIKE '%CANCEL%' and upper(s.reason) IN ('DAMAGED ITEMS','LOST','DAMAGED') /*and fsm.first_ofd_time IS NULL*/ THEN 1 ELSE 0 END) AS open_status_damaged_lost,
93SUM(CASE WHEN fsm.is_courier = 1 AND fsm.shipment_status = 'OUT_FOR_DELIVERY' AND (s.reason IS NULL or upper(s.reason) NOT IN ('DAMAGED ITEMS','LOST','DAMAGED')) THEN 1
94WHEN fsm.is_courier = 0 AND fsm.shipment_status = 'OUT_FOR_DELIVERY' AND (s.reason IS NULL or upper(s.reason) NOT IN ('DAMAGED ITEMS','LOST','DAMAGED')) AND tsk.status is not null and tsk.status NOT IN ('UNFINISHED','POSTPONED','CANCELLED_BY_CUSTOMER','CANCELLED') THEN 1 ELSE 0 END) AS dispatched_ofd,
95SUM(CASE WHEN fsm.is_courier = 1 AND fsm.shipment_status IN ('DELIVERED','PARTIALLY_DELIVERED') THEN 1
96WHEN fsm.is_courier = 0 AND tsk.status = 'COMPLETED' THEN 1 ELSE 0 END) AS delivered,
97SUM(CASE WHEN upper(fsm.shipment_status) LIKE '%CANCEL%' THEN 1 ELSE 0 END) AS flash_cancelled,
98SUM(CASE WHEN fsm.first_ofd_time is not null and tsk.status IN ('POSTPONED','UNFINISHED','REMOVED','BLOCKED') THEN 1 ELSE 0 END) AS open_status_post_ofd
99FROM adhoc_analysis.ffoms_current ci
100LEFT JOIN (SELECT id, external_order_id, skull_namespace FROM pe_mercury_thea.sale_order_snapshot where dt >= '2021-01-14') so ON so.external_order_id = ci.warehouse_order_id
101LEFT JOIN (SELECT external_order_id, min_ready_for_picking_time, min_customer_invoice_generated_time, order_status FROM data_model.integrated_f_order where dt >= '2021-01-14') ifo on ifo.external_order_id = ci.warehouse_order_id
102LEFT JOIN (SELECT shipment_id, reference_id, is_courier, shipment_status, shipment_cancellation_time, delivered_time, first_ofd_time FROM data_model.flash_shipment_model where dt >= '2021-01-14') fsm ON fsm.reference_id = ci.warehouse_order_id
103LEFT JOIN (SELECT id, payment_type, payment_mode, reason FROM pe_logistics_bolt_production_shipment_service.shipment_snapshot where dt >= '2021-01-14') s on s.id = fsm.shipment_id
104LEFT JOIN metadata.thea_source_mapping tsm on tsm.thea_id = so.skull_namespace
105LEFT JOIN (SELECT shipment_id, task_id, latest_task_flag, status, nah_time as task_nah_time, postponed_time as task_postponed_time, ofd_time as task_ofd_time FROM data_model.flash_task_model where dt >= date_sub(current_date(),30) and latest_task_flag = 1) tsk ON fsm.shipment_id = tsk.shipment_id
106GROUP BY 1,2,3,4) temp_table);
107
108--ORDER Issues:
109DROP table IF EXISTS reporting_batch.ffoms_order_issues;
110
111CREATE table reporting_batch.ffoms_order_issues AS
112SELECT
113CAST('warehouse_city' AS VARCHAR(50)) warehouse_city,
114CAST('ffoms_create_date' AS VARCHAR(50)) ffoms_create_date,
115CAST('courier_order' AS VARCHAR(50)) courier_order,
116CAST('flash_payment_mode' AS VARCHAR(50)) flash_payment_mode,
117CAST('ffoms_orders' AS VARCHAR(50)) ffoms_orders,
118CAST('catalog_issue_historical' AS VARCHAR(50)) catalog_issue_historical,
119CAST('tech_issue_historical' AS VARCHAR(50)) tech_issue_historical,
120CAST('retailer_issue_historical' AS VARCHAR(50)) retailer_issue_historical,
121CAST('other_tech_issue_historical' AS VARCHAR(50)) other_tech_issue_historical,
122CAST('catalog_issue_current' AS VARCHAR(50)) catalog_issue_current,
123CAST('tech_issue_current' AS VARCHAR(50)) tech_issue_current,
124CAST('retailer_issue_current' AS VARCHAR(50)) retailer_issue_current,
125CAST('other_tech_issue_current' AS VARCHAR(50)) other_tech_issue_current,
126CAST('paper_picking_issue' AS VARCHAR(50)) paper_picking_issue,
127CAST('received_at_store' AS VARCHAR(50)) received_at_store;
128
129INSERT INTO reporting_batch.ffoms_order_issues
130(SELECT tsm.city_name as warehouse_city, data1.ffoms_create_date, data1.courier_order, CASE WHEN (s.payment_type IS NOT NULL OR s.payment_mode IS NOT NULL) THEN CONCAT(s.payment_type,CONCAT('-',COALESCE(s.payment_mode,''))) ELSE 'Unavailable' END as flash_payment_mode,
131COUNT(*) AS ffoms_orders,
132SUM(hi.catalog_issue1) as catalog_issue_historical, SUM(hi.tech_issue1) as tech_issue_historical, SUM(hi.retailer_issue1) as retailer_issue_historical, SUM(hi.other_tech_issue1) as other_tech_issue_historical,
133SUM(ci.catalog_issue2) as catalog_issue_current, SUM(ci.tech_issue2) as tech_issue_current, SUM(ci.retailer_issue2) as retailer_issue_current, SUM(ci.other_tech_issue2) as other_tech_issue_current, SUM(ci.paper_picking_issue) as paper_picking_issue, SUM(ci.received_at_store) as received_at_store
134FROM adhoc_analysis.ffoms_historical hi
135LEFT JOIN adhoc_analysis.ffoms_current ci on hi.channel_order_id = ci.channel_order_id
136JOIN (SELECT channel_order_id, to_date(from_utc_timestamp(created_on,'IST')) AS ffoms_create_date, channel, courier_order, retailer_id,warehouse_order_id, tenant FROM pe_mercury_ffoms.fulfilment_order_snapshot) data1 ON data1.channel_order_id = hi.channel_order_id
137LEFT JOIN (SELECT id, external_order_id, skull_namespace FROM pe_mercury_thea.sale_order_snapshot where dt >= '2021-01-14') so ON so.external_order_id = data1.warehouse_order_id
138LEFT JOIN (SELECT id, reference_id, payment_type, payment_mode FROM pe_logistics_bolt_production_shipment_service.shipment_snapshot where dt >= '2021-01-14') s ON s.reference_id = data1.warehouse_order_id
139LEFT JOIN metadata.thea_source_mapping tsm on so.skull_namespace = tsm.thea_id
140--WHERE hi.catalog_issue1=1 OR hi.tech_issue1=1 OR hi.retailer_issue1=1 OR ci.paper_picking_issue=1 OR hi.other_tech_issue1=1
141GROUP BY 1,2,3,4);
142