"-- FACT02 - OB Detail -- CRXXX: Map "Reporting Date local/gmt" from yfs_order_release_status.createts where status = '3700' -- Shipped SELECT 'SeLECT Upgrade' AS source_system, enterprise_key AS source_customer_name, shipnode_key AS source_whs_name, dhl_service_type_desc AS source_service_type, document_type AS source_order_type, level_of_service AS source_ob_service_level, extn_reference_1 AS request_number, shipment_no AS shipment_no, order_no AS whs_outbound_order_number, order_line_no AS order_line_number, segment AS whs_segment, source_zone_id AS whs_zone, NULL AS acknowledge_by, timeconvertor_oracle(NULLIF(pap_start_datetime, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS acknowledge_date_time_local, NULLIF(pap_start_datetime, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS acknowledge_date_time_gmt, timeconvertor_oracle(NULLIF(req_delivery_date, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS requested_delivery_date_local, NULLIF(req_delivery_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS requested_delivery_date_gmt, timeconvertor_oracle(NULLIF(start_pick_datetime, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS start_picking_date_local, NULLIF(start_pick_datetime, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS start_picking_date_gmt, timeconvertor_oracle(NULLIF(packed_datetime, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS packed_date_local, NULLIF(packed_datetime, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS packed_date_gmt, timeconvertor_oracle(NULLIF(dispatched_date, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS dispatch_date_local, NULLIF(dispatched_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS dispatch_date_gmt, timeconvertor_oracle(NULLIF(courier_pickup_date, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS pickup_date_local, NULLIF(courier_pickup_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS pickup_date_gmt, timeconvertor_oracle(NULLIF(pap_start_datetime, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS pap_start_date_local, NULLIF(pap_start_datetime, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS pap_start_date_gmt, timeconvertor_oracle(NULLIF(packed_datetime, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS pap_completion_date_local, NULLIF(packed_datetime, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS pap_completion_date_gmt, timeconvertor_oracle(NULLIF(intial_eta, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS first_eta_local, NULLIF(intial_eta, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS first_eta_gmt, timeconvertor_oracle(NULLIF(latest_eta, to_date('01-01-2500 00:00:00', 'dd-mm-yyyy hh24:mi:ss')), 'Etc/GMT', ship_from_whs_timezone) AS final_eta_local, NULLIF(latest_eta, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) AS final_eta_gmt, order_line_status AS source_ob_whs_ol_status, item_id AS part_number, product_class AS source_physical_part_status, inventory_status AS source_system_part_status, lpn_no AS lpn_number, lot_number AS lot_number, batch_no AS batch_number, revision_no AS revision_number, country_of_origin AS country_of_origin, NULL AS requested_part_serial_number, source_location_id AS from_bin_location, serial_no AS actual_part_serial_number, awb AS source_awb_cmr, carrier AS source_carrier_name, rawb AS return_waybill_number, original_ordered_qty AS requested_qty, picked_qty AS picked_quantity, shipped_qty AS shipped_qty, uom AS qty_uom, Ship_to_country AS ship_to_country, NULL AS source_pap_rc, NULL AS rc_comments, REPLACE(REPLACE(REPLACE(TRIM(Pick_Instructions),'"',' '),CHR(10),''),CHR(13),'') AS pick_instructions, REPLACE(REPLACE(REPLACE(TRIM(Pack_Instructions),'"',' '),CHR(10),''),CHR(13),'') AS pack_instructions, CASE WHEN order_line_status_code = '9000' THEN timeconvertor_oracle(order_line_status_date , 'Etc/GMT' ,ship_from_whs_timezone) ELSE timeconvertor_oracle(order_shipped_date , 'Etc/GMT' ,ship_from_whs_timezone) END AS reporting_date_local, order_shipped_date AS reporting_date_local_old, CASE WHEN order_line_status_code = '9000' THEN NULLIF(order_line_status_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) ELSE NULLIF(order_shipped_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) END AS reporting_date_gmt, order_shipped_date AS reporting_date_gmt_old, CASE WHEN order_line_status_code = '9000' THEN timeconvertor_oracle(order_line_status_date , 'Etc/GMT' ,ship_from_whs_timezone) ELSE timeconvertor_oracle(POD_updated , 'Etc/GMT' ,ship_from_whs_timezone) END AS billing_date_local, POD_updated AS billing_date_local_old, CASE WHEN order_line_status_code = '9000' THEN NULLIF(order_line_status_date, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) ELSE NULLIF(POD_updated, to_date('01-01-2500 00:00:00','dd-mm-yyyy hh24:mi:ss')) END AS billing_date_gmt, POD_updated AS billing_date_gmt_old, NULL AS order_line_outbound_complete, oh_createts AS order_header_created_date, oh_modifyts AS order_header_modify_date, ol_modifyts AS order_line_modify_date, sh_modifyts AS shipment_header_modify_date, sl_modifyts AS shipment_line_modify_date FROM ( WITH dhl_service_type AS ( SELECT /*+ DRIVING_SITE(su_yfs_common_code) */ /*+ MATERIALIZE */ code_value, code_long_description FROM su_yfs_common_code WHERE code_type = 'DHL_SVC_TYPE' AND organization_code = 'DEFAULT' ), pucm_shipments AS ( SELECT /*+ MATERIALIZE */ TRIM(pto.org_order_id) AS pucm_order_no, TRIM(psh.orig_shipment_id) AS pucm_shipment_no, psh.latest_eta AS pucm_latest_eta, pde.updated AS pucm_pod_updated, ppickup.picked_up AS pucm_picked_up_date, alt_peta.eta AS pucm_initial_eta FROM pucm_shipment psh LEFT JOIN pucm_v_shipment_order so ON psh.shipment_id = so.shipment_id LEFT JOIN pucm_trans_order pto ON pto.order_id = so.order_id LEFT JOIN pucm_delivery pde ON (psh.shipment_id = pde.shipment_id AND pde.failed = 0) LEFT JOIN pucm_pickup ppickup ON psh.shipment_id = ppickup.shipment_id LEFT JOIN pucm_eta alt_peta ON (psh.shipment_id = alt_peta.shipment_id AND alt_peta.eta_number = '1') WHERE pto.orig_date_created >= '1-SEP-2013' -- BR2.0 Orders ), order_line_status AS ( SELECT ors_1.order_release_key, ors_1.status_date, ors_1.status_quantity, ors_1.order_line_key, ors_1.order_header_key, ors_1.status, st.description FROM su_yfs_order_release_status ors_1 INNER JOIN ( SELECT MAX(order_release_status_key) AS order_release_status_key FROM su_yfs_order_release_status GROUP BY order_line_key ) latest_ors ON latest_ors.order_release_status_key = ors_1.order_release_status_key AND ors_1.status_quantity > 0 LEFT JOIN su_yfs_pipeline pipe ON ors_1.pipeline_key = pipe.pipeline_key LEFT JOIN su_yfs_status st ON ors_1.status = st.status AND pipe.process_type_key = st.process_type_key WHERE ors_1.order_header_key >= '2013090100000000000000 ' ), shipped_status AS ( SELECT MAX(ors_1.status_date) AS shipped_date, SUM(ors_1.status_quantity) AS status_quantity, ors_1.order_line_key, ors_1.order_header_key, ors_1.status, st.description, count(*) FROM su_yfs_order_release_status ors_1 LEFT JOIN su_yfs_pipeline pipe ON ors_1.pipeline_key = pipe.pipeline_key LEFT JOIN su_yfs_status st ON (ors_1.status = st.status AND pipe.process_type_key = st.process_type_key) WHERE ors_1.order_header_key >= '2013090100000000000000 ' AND ors_1.status = '3700' GROUP BY ors_1.order_line_key, ors_1.order_header_key, ors_1.status, st.description ), task AS ( SELECT t1.shipment_line_key, t1.task_key, t1.task_type, TRIM(t1.task_status) AS task_status, t1.source_location_id, t1.inventory_status, t1.source_zone_id FROM su_yfs_task t1 INNER JOIN ( SELECT MAX(t2.task_key) AS TASK_KEY FROM su_yfs_task t2 GROUP BY shipment_line_key ) latest_task ON latest_task.task_key = t1.task_key AND TRIM(t1.inventory_status) = 'ATP' AND TRIM(t1.task_status) <> '9000' where t1.task_key >= '2013090100000000000000 ' ), wms_shipments AS ( SELECT sl.order_header_key, sl.order_line_key, sh.shipment_key, sl.shipment_line_key, sh.shipment_no, sh.shipnode_key, sh.level_of_service, sh.actual_shipment_date, sh.scac, sh.bol_no, sh.modifyts AS sh_modifyts, sl.segment, sl.item_id, sl.product_class, sl.country_of_origin, sl.quantity, sl.uom AS uom, sl.modifyts AS sl_modifyts, sts.lot_number, sts.batch_no, sts.revision_no, sts.serial_no, sc.container_scm, sts.quantity AS serial_shipped_qty, cd.quantity AS container_line_detail_qty FROM su_yfs_shipment_line sl LEFT JOIN su_yfs_shipment sh ON sh.shipment_key = sl.shipment_key LEFT JOIN su_yfs_container_details cd ON sl.shipment_line_key = cd.shipment_line_key LEFT JOIN su_yfs_shipment_container sc ON cd.shipment_container_key = sc.shipment_container_key LEFT JOIN su_yfs_shipment_tag_serial sts ON sl.shipment_line_key = sts.shipment_line_key AND cd.container_details_key = sts.container_detail_key WHERE sh.status <> '9000' AND sl.order_header_key >= '2013090100000000000000 ' ), wms_order_shipments AS ( SELECT NVL(ol_loc.timezone, sh_loc.timezone) AS ship_from_whs_timezone, TRIM(oh.enterprise_key) AS enterprise_key, TRIM(nvl(ol.shipnode_key,wms_shipment.shipnode_key)) AS shipnode_key, cc.code_long_description AS dhl_service_type_desc, CASE oh.document_type WHEN '0001' THEN 'Sales Order' ELSE 'Transfer Order' END AS document_type, TRIM(NVL(oh.level_of_service,wms_shipment.level_of_service)) AS level_of_service, TRIM(oh.extn_reference_1) AS extn_reference_1, TRIM(wms_shipment.shipment_no) AS shipment_no, TRIM(oh.order_no) AS order_no, TRIM(ol.prime_line_no) AS order_line_no, TRIM(wms_shipment.segment) AS segment, TRIM(task.source_zone_id) AS source_zone_id, ol.req_delivery_date AS req_delivery_date, pap_start.createts AS pap_start_datetime, (select max(createts) from su_yfs_shipment_status_audit where shipment_key = wms_shipment.shipment_key AND new_status = '1200') as start_pick_datetime, (select max(createts) from su_yfs_shipment_status_audit where shipment_key = wms_shipment.shipment_key AND new_status = '1300') as packed_datetime, dispatch.createts AS dispatched_date, TRIM(order_line_status.description) AS order_line_status, TRIM(order_line_status.status) AS order_line_status_code, order_line_status.status_date AS order_line_status_date, TRIM(NVL(ol.item_id,wms_shipment.item_id)) AS item_id, TRIM(NVL(ol.product_class,wms_shipment.product_class)) AS product_class, TRIM(NVL(task.inventory_status,'ATP')) AS inventory_status, TRIM(wms_shipment.container_scm) AS lpn_no, TRIM(wms_shipment.lot_number) AS Lot_Number, TRIM(wms_shipment.batch_no) AS batch_no, TRIM(wms_shipment.revision_no) AS revision_no, TRIM(wms_shipment.country_of_origin) AS Country_of_Origin, TRIM(task.source_location_id) AS source_location_id, TRIM(wms_shipment.serial_no) AS serial_no, TRIM(wms_shipment.bol_no) AS awb, TRIM(wms_shipment.scac) AS carrier, TRIM(oh.extn_rawb_no) AS rawb, ol.original_ordered_qty AS original_ordered_qty, ors_pick.total_quantity AS picked_qty, NVL(NVL(wms_shipment.serial_shipped_qty, wms_shipment.container_line_detail_qty), wms_shipment.quantity) AS Shipped_Qty, TRIM(wms_shipment.uom) AS uom, TRIM(to_addr.country) AS Ship_to_country, (SELECT DISTINCT FIRST_VALUE(instruction_text) over (ORDER BY sequence_no) FROM su_yfs_instruction_detail inst WHERE ol.order_line_key = inst.reference_key AND inst.table_name = 'YFS_ORDER_LINE' AND TRIM(instruction_type) = 'PICK') AS Pick_Instructions, (SELECT DISTINCT FIRST_VALUE(instruction_text) over (ORDER BY sequence_no) FROM su_yfs_instruction_detail inst WHERE ol.order_line_key = inst.reference_key AND inst.table_name = 'YFS_ORDER_LINE' AND TRIM(instruction_type) = 'PACK') AS Pack_Instructions, shipped_status.shipped_date AS order_shipped_date, oh.createts AS oh_createts, oh.modifyts AS oh_modifyts, ol.modifyts AS ol_modifyts, wms_shipment.sh_modifyts AS sh_modifyts, wms_shipment.sl_modifyts AS sl_modifyts FROM su_yfs_order_header oh LEFT JOIN su_yfs_order_line ol ON ol.order_header_key = oh.order_header_key LEFT JOIN wms_shipments wms_shipment ON (wms_shipment.order_header_key = ol.order_header_key and wms_shipment.order_line_key = ol.order_line_key) /*********/ /** Order Line Release Status **/ LEFT OUTER JOIN order_line_status order_line_status ON order_line_status.order_line_key = ol.order_line_key /* To obtain source_location_id and souce_zone_id and inventory_status */ LEFT JOIN TASK task ON wms_shipment.shipment_line_key = task.shipment_line_key /*********/ LEFT JOIN shipped_status shipped_status ON shipped_status.order_line_key = ol.order_line_key LEFT OUTER JOIN su_yfs_person_info to_addr ON oh.ship_to_key = to_addr.person_info_key LEFT OUTER JOIN su_yfs_shipment_status_audit dispatch ON ( wms_shipment.shipment_key = dispatch.shipment_key AND TRIM(dispatch.new_status) = '1400' ) LEFT OUTER JOIN su_yfs_shipment_status_audit pap_start ON ( wms_shipment.shipment_key = pap_start.shipment_key AND TRIM(pap_start.new_status) = '1100' ) LEFT OUTER JOIN su_yfs_order_release_status ors_pick ON ( oh.order_header_key = ors_pick.order_header_key AND ol.order_line_key = ors_pick.order_line_key and ors_pick.status = '3350' and ors_pick.order_release_key = order_line_status.order_release_key ) LEFT OUTER JOIN dhl_service_type cc ON (cc.code_value = oh.extn_dhl_service_type) /** order_line.shipnode_key **/ LEFT OUTER JOIN su_yfs_organization ol_org ON ol.shipnode_key = ol_org.organization_code LEFT OUTER JOIN su_yfs_locale ol_loc ON ol_org.locale_code = ol_loc.localecode /** shipment.shipnode_key **/ LEFT OUTER JOIN su_yfs_organization sh_org ON wms_shipment.shipnode_key = sh_org.organization_code LEFT OUTER JOIN su_yfs_locale sh_loc ON sh_org.locale_code = sh_loc.localecode WHERE oh.document_type IN ('0001','0006') AND oh.order_header_key >= '2013090100000000000000 ' ) SELECT wms_orders.ship_from_whs_timezone, wms_orders.enterprise_key, wms_orders.shipnode_key, wms_orders.dhl_service_type_desc, wms_orders.document_type, wms_orders.level_of_service, wms_orders.extn_reference_1, wms_orders.shipment_no, wms_orders.order_no, wms_orders.order_line_no, wms_orders.segment, wms_orders.source_zone_id, wms_orders.req_delivery_date, wms_orders.pap_start_datetime, wms_orders.start_pick_datetime, wms_orders.packed_datetime, wms_orders.dispatched_date, pucm_shipments.pucm_picked_up_date AS courier_pickup_date, NVL(wms_orders.req_delivery_date, pucm_shipments.pucm_initial_eta) AS intial_eta, pucm_shipments.pucm_latest_eta AS latest_eta, wms_orders.order_line_status, wms_orders.order_line_status_code, wms_orders.order_line_status_date, wms_orders.item_id, wms_orders.product_class, wms_orders.inventory_status, wms_orders.lpn_no, wms_orders.Lot_Number, wms_orders.batch_no, wms_orders.revision_no, wms_orders.Country_of_Origin, wms_orders.source_location_id, wms_orders.serial_no, wms_orders.awb, wms_orders.carrier, wms_orders.rawb, wms_orders.original_ordered_qty, wms_orders.picked_qty, wms_orders.Shipped_Qty, wms_orders.uom, wms_orders.Ship_to_country, wms_orders.Pick_Instructions, wms_orders.Pack_Instructions, wms_orders.order_shipped_date, pucm_shipments.pucm_pod_updated AS POD_updated, wms_orders.oh_createts, wms_orders.oh_modifyts, wms_orders.ol_modifyts, wms_orders.sh_modifyts, wms_orders.sl_modifyts FROM wms_order_shipments wms_orders LEFT OUTER JOIN pucm_shipments pucm_shipments ON (TRIM(wms_orders.order_no) = TRIM(pucm_shipments.pucm_order_no) AND TRIM(wms_orders.shipment_no) = TRIM(pucm_shipments.pucm_shipment_no)) ) --WHERE enterprise_key = 'EMC2' --and shipment_no = '100404964' "