[SELECT
'SeLECT Upgrade' AS source_system,
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 str_ro_rtp_netapp.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.unit_length_uom || ' '
|| item.unit_width_uom || ' '
|| item.unit_height_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,
CAST(NULL AS VARCHAR2(4000)) AS delivery_record_number,
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,
CAST(NULL AS DATE) AS delivery_record_creat_date_loc,
CAST(NULL AS DATE) AS delivery_record_creat_date_gmt,
CAST(NULL AS DATE) AS label_printtimestamp_loc,
CAST(NULL AS DATE) AS label_printtimestamp_gmt,
DECODE ( ( select sum(ol.ordered_qty) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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,
timeconvertor_oracle(rh.extn_truck_arrival_time, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS truck_arrival_time_loc,
rh.extn_truck_arrival_time AS truck_arrival_time_gmt,
timeconvertor_oracle(CASE WHEN TRIM(rh.status) = '1500' THEN rh.status_date ELSE NULL END, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS system_part_receipt_date_loc,
CASE WHEN TRIM(rh.status) = '1500' THEN rh.status_date ELSE NULL END AS system_part_receipt_date_gmt,
/* CR108-d */
timeconvertor_oracle(rcpt_status.new_status_date, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS source_part_putaway_date_loc,
rcpt_status.new_status_date AS source_part_putaway_date_gmt,
timeconvertor_oracle(rh.extn_truck_arrival_time, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS dts_start_time_loc,
rh.extn_truck_arrival_time AS dts_start_time_gmt,
/* CR108-d */
timeconvertor_oracle(rcpt_status.new_status_date, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS dts_completion_date_loc,
rcpt_status.new_status_date AS dts_completion_date_gmt,
timeconvertor_oracle(NULLIF(rl.ship_by_date, TO_DATE('2500-01-01', 'YYYY-MM-DD')), 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)) AS expiry_date_loc,
NULLIF(rl.ship_by_date, TO_DATE('2500-01-01', 'YYYY-MM-DD')) AS expiry_date_gmt,
timeconvertor_oracle(CASE
WHEN DECODE ( ( select sum(ol.ordered_qty) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 rcpt_status.new_status_date-- rcpt_status.new_status_date
END, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)
) AS reporting_date_loc,
CASE
WHEN DECODE ( ( select sum(ol.ordered_qty) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 rcpt_status.new_status_date-- rcpt_status.new_status_date
END AS reporting_date_gmt,
timeconvertor_oracle(CASE
WHEN DECODE ( ( select sum(ol.ordered_qty) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 rcpt_status.new_status_date --rcpt_status.new_status_date
END, 'UTC', (SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.yfs_organization og ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND og.organization_key = ol.receiving_node)
) AS billing_date_loc,
CASE
WHEN DECODE ( ( select sum(ol.ordered_qty) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select min(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_status st, str_ro_rtp_netapp.yfs_pipeline p
WHERE st.status = (select max(status) from str_ro_rtp_netapp.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 str_ro_rtp_netapp.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 rcpt_status.new_status_date --rcpt_status.new_status_date
END AS billing_date_gmt,
CAST(NULL AS VARCHAR2(20)) AS product_class_2,
CAST(NULL AS VARCHAR2(40)) AS truck_license_plate,
CAST(NULL AS VARCHAR2(20)) AS source_dts_reason_code,
CAST(NULL AS VARCHAR2(1)) AS dock_to_stock_rc_comments,
-- QC2443: For cancelled order, map cancelled date
--lo.timezone,
--(SELECT lo.timezone FROM str_ro_rtp_netapp.yfs_locale lo INNER JOIN str_ro_rtp_netapp.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
FROM
str_ro_rtp_netapp.yfs_order_header oh
INNER JOIN str_ro_rtp_netapp.yfs_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 str_ro_rtp_netapp.yfs_item 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 str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_receipt_line rl ON (ol.order_line_key = rl.order_line_key)
LEFT JOIN (
str_ro_rtp_netapp.yfs_receipt_header rh INNER JOIN str_ro_rtp_netapp.yfs_status rhst ON (rhst.status = rh.status)
INNER JOIN str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_shipment sh ON (sh.shipment_key = rh.shipment_key)
LEFT JOIN str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_task_status_audit tsa ON (tsa.task_key = ts.task_key AND tsa.new_status = '2000')
/* CR108-d: Received timestamps */
LEFT JOIN str_ro_rtp_netapp.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 str_ro_rtp_netapp.yfs_shipment_line sl ON sl.shipment_key = sh.shipment_key AND ol.order_line_key = sl.order_line_key
WHERE 1=1
AND oh.document_type IN ('0005', '0006')
AND oh.order_header_key >= '20130901'
--and oh.order_no in ('ORDCANCEL01')
AND oh.order_header_key = ord;
rec_ord c1%rowtype;]