SELECT 'SeLECT Upgrade' AS source_system, customer_name, whs_inbound_order_no, source_ib_order_level, request_number, source_order_type, whs_order_line_no, whs_segment, quantity_requested, source_service_type, shipment_no, receipt_number, receipt_line_number, lpn_number, lot_number, batch_number, revision_number, country_of_origin, actual_part_serial_number, disposition_code, quantity_received, quantity_uom, weight, weight_uom, 0 AS volume, volume_uom, source_whs_name, part_number, product_class, source_awb_mcr, cource_carrier_name, NULL AS delivery_record_number, batch_task_number, putaway_task_number, whs_zone, to_bin_location, NULL AS delivery_record_creat_date_loc, NULL AS delivery_record_creat_date_gmt, NULL AS label_printtimestamp_loc, NULL AS label_printtimestamp_gmt, sib_whs_order_status, receipt_status, source_system_part_status, timeconvertor_oracle (truck_arrival_time_gmt, 'UTC', local_time) AS truck_arrival_time_loc, truck_arrival_time_gmt, timeconvertor_oracle (system_part_receipt_date_gmt, 'UTC', local_time) AS system_part_receipt_date_loc, system_part_receipt_date_gmt, timeconvertor_oracle (source_part_putaway_date_gmt, 'UTC', local_time) AS source_part_putaway_date_loc, source_part_putaway_date_gmt, timeconvertor_oracle (dts_start_time_gmt, 'UTC', local_time) AS dts_start_time_loc, dts_start_time_gmt, timeconvertor_oracle (dts_completion_date_gmt, 'UTC', local_time) AS dts_completion_date_loc, dts_completion_date_gmt, timeconvertor_oracle (expiry_date_gmt, 'UTC', local_time) AS expiry_date_loc, expiry_date_gmt, timeconvertor_oracle (reporting_date_gmt, 'UTC', local_time) AS reporting_date_loc, reporting_date_gmt, timeconvertor_oracle (billing_date_gmt, 'UTC', local_time) AS billing_date_loc, billing_date_gmt, NULL AS product_class_2, NULL AS truck_license_plate, NULL AS source_dts_reason_code, NULL AS dock_to_stock_rc_comments, oh_modifyts, ol_modifyts, sh_modifyts, sl_modifyts, oh_createts FROM (WITH order_h AS (select * from yfs_order_header oh where exists(select 1 from ea_mv_refresh.fact05_order_header ea where ea.order_header_key = oh.order_header_key)), order_line AS ( select ol.* from yfs_order_line ol inner join order_h oh on (oh.order_header_key = ol.order_header_key)), itm AS (select item.unit_length_uom || ' ' || item.unit_width_uom || ' ' || item.unit_height_uom AS volume_uom, item.item_id, item.organization_code, item.uom from yfs_item item, order_line ol, order_h oh where item.item_id = ol.item_id AND item.organization_code = oh.enterprise_key AND item.uom = ol.uom and ol.order_header_key = oh.order_header_key) SELECT TRIM (oh.enterprise_key) AS customer_name, TRIM (oh.order_no) AS whs_inbound_order_no, TRIM (oh.extn_dhl_service_type) AS source_ib_order_level, TRIM (oh.extn_reference_1) AS request_number, CASE oh.document_type WHEN '0005' THEN 'Purchase Order' WHEN '0006' THEN 'Transfer Order' ELSE oh.document_type END AS source_order_type, TRIM (ol.prime_line_no) AS whs_order_line_no, TRIM (ol.segment) AS whs_segment, ol.ordered_qty AS quantity_requested, --TRIM(cc.code_long_description) AS source_service_type, (SELECT code_long_description FROM yfs_common_code WHERE TRIM (code_type) = 'DHL_SVC_TYPE' AND organization_code = 'DEFAULT' AND code_value = oh.extn_dhl_service_type) AS source_service_type, TRIM (sh.shipment_no) AS shipment_no, TRIM (rh.receipt_no) AS receipt_number, TRIM (rl.receipt_line_no) AS receipt_line_number, TRIM (rl.lpn_no) AS lpn_number, TRIM (rl.lot_number) AS lot_number, TRIM (rl.batch_no) AS batch_number, TRIM (rl.revision_no) AS revision_number, TRIM (rl.country_of_origin) AS country_of_origin, TRIM (rl.serial_no) AS actual_part_serial_number, TRIM (rl.disposition_code) AS disposition_code, rl.quantity AS quantity_received, rl.uom AS quantity_uom, rl.net_weight AS weight, rl.net_weight_uom AS weight_uom, /*item.unit_length || ' ' || item.unit_width || ' ' || item.unit_height AS volume,*/ --0 AS volume, item.volume_uom AS volume_uom, TRIM ( COALESCE (rh.receivingnode_key, oh.receiving_node, ol.receiving_node)) AS source_whs_name, NVL (TRIM (rl.item_id), TRIM (ol.item_id)) AS part_number, NVL (TRIM (rl.product_class), TRIM (ol.product_class)) AS product_class, NVL (TRIM (rh.extn_airwaybill_no), TRIM (sh.bol_no)) AS source_awb_mcr, NVL (TRIM (rh.extn_carrier_service), TRIM (sh.scac)) AS cource_carrier_name, TRIM (ts.batch_no) AS batch_task_number, TRIM (ts.task_id) AS putaway_task_number, TRIM (ts.target_zone_id) AS whs_zone, TRIM (ts.target_location_id) AS to_bin_location, DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), '0', 'Cancelled', DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT SUM (sts.status_quantity) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000'), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), (SELECT 'Partially ' || st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000') AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)))))) AS sib_whs_order_status, TRIM (rhst.description) AS receipt_status, TRIM (rhst.description) AS source_system_part_status, rh.extn_truck_arrival_time AS truck_arrival_time_gmt, CASE WHEN TRIM (rh.status) = '1500' THEN rh.status_date ELSE NULL END AS system_part_receipt_date_gmt, /* CR108-d */ --rcpt_status.new_status_date AS source_part_putaway_date_gmt, (SELECT MAX (rcpt_status.new_status_date) FROM yfs_receipt_status_audit rcpt_status WHERE rcpt_status.receipt_header_key = rh.receipt_header_key AND rcpt_status.new_status = '1500') AS source_part_putaway_date_gmt, rh.extn_truck_arrival_time AS dts_start_time_gmt, /* CR108-d */ --rcpt_status.new_status_date AS dts_completion_date_gmt, (SELECT MAX (rcpt_status.new_status_date) FROM yfs_receipt_status_audit rcpt_status WHERE rcpt_status.receipt_header_key = rh.receipt_header_key AND rcpt_status.new_status = '1500') AS dts_completion_date_gmt, NULLIF (rl.ship_by_date, TO_DATE ('2500-01-01', 'YYYY-MM-DD')) AS expiry_date_gmt, CASE WHEN DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), '0', 'Cancelled', DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT SUM (sts.status_quantity) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000'), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), (SELECT 'Partially ' || st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000') AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)))))) = 'Cancelled' THEN cancelled_date.status_date --cancelled_date.status_date ELSE (SELECT MAX (rcpt_status.new_status_date) FROM yfs_receipt_status_audit rcpt_status WHERE rcpt_status.receipt_header_key = rh.receipt_header_key AND rcpt_status.new_status = '1500') --rcpt_status.new_status_date-- rcpt_status.new_status_date END AS reporting_date_gmt, CASE WHEN DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), '0', 'Cancelled', DECODE ( (SELECT SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT SUM (sts.status_quantity) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), DECODE ( (SELECT MIN (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0), (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000'), (SELECT st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MIN ( status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0) AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)), (SELECT 'Partially ' || st.description FROM yfs_status st, yfs_pipeline p WHERE st.status = (SELECT MAX (status) FROM yfs_order_release_status sts WHERE sts.order_header_key = oh.order_header_key AND sts.order_line_key = ol.order_line_key AND sts.status_quantity > 0 AND sts.status < '9000') AND p.process_type_key = st.process_type_key AND p.pipeline_key = (SELECT DISTINCT ors.pipeline_key FROM yfs_order_release_status ors WHERE ors.order_header_key = oh.order_header_key AND ors.order_line_key = ol.order_line_key AND ors.status_quantity > 0)))))) = 'Cancelled' THEN cancelled_date.status_date --cancelled_date.status_date ELSE (SELECT MAX (rcpt_status.new_status_date) FROM yfs_receipt_status_audit rcpt_status WHERE rcpt_status.receipt_header_key = rh.receipt_header_key AND rcpt_status.new_status = '1500') --rcpt_status.new_status_date --rcpt_status.new_status_date END AS billing_date_gmt, -- QC2443: For cancelled order, map cancelled date --lo.timezone, --(SELECT lo.timezone FROM yfs_locale lo INNER JOIN yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node) AS timezone, --cancelled_date.status_date rs_status_date, -- technical columns oh.modifyts AS oh_modifyts, ol.modifyts AS ol_modifyts, sh.modifyts AS sh_modifyts, sl.modifyts AS sl_modifyts, oh.order_date AS oh_createts, (SELECT lo.timezone FROM yfs_locale lo INNER JOIN yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node) AS local_time FROM order_h oh INNER JOIN order_line ol ON (oh.order_header_key = ol.order_header_key) --INNER JOIN dhl_service_type cc ON cc.code_value = oh.extn_dhl_service_type INNER JOIN itm item ON ( item.item_id = ol.item_id AND item.organization_code = oh.enterprise_key AND item.uom = ol.uom) --INNER JOIN locale lo ON (lo.organization_key = ol.receiving_node) LEFT JOIN yfs_order_release_status cancelled_date ON (cancelled_date.order_line_key = ol.order_line_key AND cancelled_date.status_quantity > 0 AND cancelled_date.status = '9000') LEFT JOIN yfs_receipt_line rl ON (ol.order_line_key = rl.order_line_key) LEFT JOIN ( yfs_receipt_header rh INNER JOIN yfs_status rhst ON (rhst.status = rh.status) INNER JOIN yfs_pipeline rhp ON (rhp.process_type_key = rhst.process_type_key AND rhp.pipeline_key = rh.pipeline_key)) ON (rh.receipt_header_key = rl.receipt_header_key) LEFT JOIN yfs_shipment sh ON (sh.shipment_key = rh.shipment_key) LEFT JOIN yfs_task ts ON ( ts.receipt_header_key = rh.receipt_header_key AND ts.item_id = rl.item_id AND ts.source_lpn_no = rl.lpn_no AND ts.task_type = 'GEN-PTWY' AND ts.task_status <> '9000') --LEFT JOIN yfs_task_status_audit tsa ON (tsa.task_key = ts.task_key AND tsa.new_status = '2000') /* CR108-d: Received timestamps */ /*LEFT JOIN yfs_receipt_status_audit rcpt_status ON ( rcpt_status.receipt_header_key = rh.receipt_header_key AND rcpt_status.new_status = '1500 ' -- "Received" ) */ LEFT JOIN yfs_shipment_line sl ON sl.shipment_key = sh.shipment_key AND ol.order_line_key = sl.order_line_key) WHERE 1 = 1