--Non ARMA , OTIF at line level
SELECT
ship_from,
site,
order_number,
line_number line_number,
item_code,
item_desc,
uin,
business_unit,
site_type,
demand_type,
order_month,
category,
MIN(ordered_quantity) ordered_quantity,
SUM(shipped_quantity) shipped_quantity,
SUM(quantity_received) quantity_received,
MAX(requisition_creation_date) requisition_creation_date,
MAX(so_creation_date) so_creation_date,
MAX(promise_date) promise_date,
MAX(need_by_date) need_by_date,
MAX(inv_allocation_date) inv_allocation_date,
MAX(pick_date) pick_date,
MAX(ship_date) ship_date,
MAX(delivered_date) delivered_date,
MAX(receive_date) receive_date,
LISTAGG(DISTINCT tracking_number, ',') WITHIN GROUP(
ORDER BY
order_number, item_code
) tracking_number,
LISTAGG(DISTINCT carrier_name, ',') WITHIN GROUP(
ORDER BY
order_number, item_code
) carrier_name
FROM
(
SELECT
decode(mp.attribute5, 'RAD-1', 'Janus-RAD1', mp.attribute5) ship_from,
shipto.attribute5 site,
to_char(ooha.order_number) order_number,
rtrim(oola.line_number
|| '.'
|| oola.shipment_number
|| '.'
|| oola.option_number
|| '.'
|| oola.component_number, '.') line_number,
msi.segment1 item_code,
shipto.attribute7 business_unit,
ooha.attribute5 site_type,
ooha.attribute3 demand_type,
to_char(ooha.ordered_date, 'YYYY-MM') order_month,
(
SELECT
concatenated_segments
FROM
apps.mtl_categories_kfv mck,
apps.mtl_item_categories mic
WHERE
mic.category_id = mck.category_id
AND mic.inventory_item_id = msi.inventory_item_id
AND category_set_id = 1100000041
AND organization_id = 101
AND ROWNUM = 1
) category,
msi.description item_desc,
msi.attribute9 uin,
wdd.src_requested_quantity ordered_quantity,
wdd.shipped_quantity shipped_quantity,
-- wdd.requested_quantity requested_quantity,
receipt.quantity_received quantity_received,
prh.creation_date requisition_creation_date,
oola.creation_date so_creation_date,
oola.promise_date promise_date,
oola.request_date need_by_date,
(
SELECT
trunc(wpb.last_update_date)
FROM
apps.wsh_picking_batches wpb
WHERE
wpb.batch_id = wdd.batch_id
) inv_allocation_date,
(
SELECT
MAX(trunc(trl.status_date))
FROM
apps.mtl_txn_request_lines_v trl,
apps.mtl_txn_request_headers trh
WHERE
trl.line_id = wdd.move_order_line_id
AND trl.header_id = trh.header_id
AND trl.line_status = 5
) pick_date,
--For orders shipped outside of DSCS below code is added to consider oola.last_update_date
CASE
WHEN oola.flow_status_code = 'CLOSED'
AND wnd.confirm_date IS NULL THEN
nvl(oola.actual_shipment_date, oola.last_update_date)
ELSE
wnd.confirm_date
END ship_date,
TO_DATE(wdd.attribute7, 'YYYY-MM-DD HH24:MI:SS') delivered_date,
receipt.creation_date receive_date,
wdd.tracking_number tracking_number,
(
SELECT
carrier_name
FROM
apps.wsh_carriers_v wcv
WHERE
wcv.carrier_id = wnd.carrier_id
AND ROWNUM = 1
) carrier_name,
wdd.attribute6 expected_delivery_date,
oola.attribute6 first_attempted_delivery_date
FROM
apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.mtl_parameters mp,
apps.ar_customers ac,
apps.mtl_system_items_kfv msi,
apps.hr_all_organization_units hou,
apps.hz_cust_site_uses_all hcsua,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_parties hp,
apps.hz_cust_site_uses_all hcsuab,
apps.hz_cust_acct_sites_all hcasab,
apps.hz_party_sites hpsb,
apps.hz_locations hlb,
apps.hz_parties hpb,
apps.po_requisition_lines_all prl,
apps.po_requisition_headers_all prh,
apps.per_all_people_f ppf,
apps.mtl_parameters mpw,
apps.po_location_associations_all pla,
apps.mtl_parameters shipto,
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_new_deliveries wnd,
(
SELECT DISTINCT
MAX(rcv.creation_date)
OVER(PARTITION BY oola1.line_id) creation_date,
SUM(rsl.quantity_received)
OVER(PARTITION BY oola1.line_id) quantity_received,
oola1.line_id
FROM
apps.oe_order_lines_all oola1,
apps.rcv_shipment_lines rsl,
apps.po_requisition_lines_all prla,
apps.rcv_shipment_headers rsh,
apps.rcv_transactions rcv
WHERE
oola1.source_document_line_id = prla.requisition_line_id (+)
AND prla.requisition_line_id = rsl.requisition_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rcv.shipment_line_id = rsl.shipment_line_id
AND transaction_type = 'RECEIVE'
AND rsh.receipt_num IS NOT NULL
AND rsl.quantity_received > 0
) receipt
WHERE
ooha.header_id = oola.header_id
AND oola.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = oola.ship_from_org_id
AND mp.organization_id = msi.organization_id
AND ac.customer_id = ooha.sold_to_org_id
AND hou.organization_id = ooha.org_id
AND ooha.booked_flag = 'Y'
AND ooha.cancelled_flag = 'N'
AND ooha.ship_to_org_id = hcsua.site_use_id (+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
AND hcasa.party_site_id = hps.party_site_id (+)
AND hps.location_id = hl.location_id (+)
AND hps.party_id = hp.party_id (+)
AND ooha.invoice_to_org_id = hcsuab.site_use_id
AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hpsb.location_id = hlb.location_id
AND hpsb.party_id = hpb.party_id
AND oola.source_document_line_id = prl.requisition_line_id (+)
AND prh.requisition_header_id (+) = prl.requisition_header_id
AND prh.preparer_id = ppf.person_id (+)
AND trunc(sysdate) BETWEEN trunc(ppf.effective_start_date(+)) AND trunc(ppf.effective_end_date(+))
AND mpw.organization_id = oola.ship_from_org_id
AND hcsua.site_use_id = pla.site_use_id (+)
AND pla.organization_id = shipto.organization_id (+)
AND oola.line_id = receipt.line_id (+)
AND oola.line_id = wdd.source_line_id (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wnd.delivery_id (+) = wda.delivery_id
AND mp.attribute3 ='RAD'
AND mp.attribute5 != 'RAD-1'
AND oola.flow_status_code != 'CANCELLED'
AND wdd.attribute7 IS NOT NULL
AND wdd.released_status = 'C'
AND wdd.source_code = 'OE'
AND ooha.attribute3 NOT IN ( 'ARMA','RMA')
-- AND ooha.order_number ='126559'
AND EXISTS (
SELECT
1
FROM
apps.wsh_delivery_details wdd1
WHERE
1 = 1
AND source_code = 'OE'
AND oola.line_id = wdd1.source_line_id
)
) inner_sql
GROUP BY
ship_from,
site,
line_number,
order_number,
item_code,
item_desc,
uin,
business_unit,
site_type,
demand_type,
order_month,
category
HAVING
MIN(ordered_quantity) = SUM(shipped_quantity)
-- ARMA , OTIF at Item level
UNION
SELECT
ship_from,
site,
order_number,
NULL line_number,
item_code,
item_desc,
uin,
business_unit,
site_type,
demand_type,
order_month,
category,
MIN(ordered_quantity),
SUM(shipped_quantity),
--sum(requested_quantity),
SUM(quantity_received),
MAX(requisition_creation_date),
MAX(so_creation_date),
MAX(promise_date),
MAX(need_by_date),
MAX(inv_allocation_date),
MAX(pick_date),
MAX(ship_date),
MAX(delivered_date),
MAX(receive_date),
LISTAGG(DISTINCT tracking_number, ',') WITHIN GROUP(
ORDER BY
order_number, item_code
),
LISTAGG(DISTINCT carrier_name, ',') WITHIN GROUP(
ORDER BY
order_number, item_code
) carrier_name
FROM
(
SELECT
decode(mp.attribute5, 'RAD-1', 'Janus-RAD1', mp.attribute5) ship_from,
shipto.attribute5 site,
to_char(ooha.order_number) order_number,
rtrim(oola.line_number
|| '.'
|| oola.shipment_number
|| '.'
|| oola.option_number
|| '.'
|| oola.component_number, '.') line_number,
msi.segment1 item_code,
shipto.attribute7 business_unit,
ooha.attribute5 site_type,
ooha.attribute3 demand_type,
to_char(ooha.ordered_date, 'YYYY-MM') order_month,
(
SELECT
concatenated_segments
FROM
apps.mtl_categories_kfv mck,
apps.mtl_item_categories mic
WHERE
mic.category_id = mck.category_id
AND mic.inventory_item_id = msi.inventory_item_id
AND category_set_id = 1100000041
AND organization_id = 101
AND ROWNUM = 1
) category,
msi.description item_desc,
msi.attribute9 uin,
SUM(wdd.src_requested_quantity)
OVER(PARTITION BY order_number, msi.segment1) ordered_quantity,
wdd.shipped_quantity shipped_quantity,
wdd.requested_quantity requested_quantity,
receipt.quantity_received quantity_received,
prh.creation_date requisition_creation_date,
oola.creation_date so_creation_date,
oola.promise_date promise_date,
oola.request_date need_by_date,
(
SELECT
trunc(wpb.last_update_date)
FROM
apps.wsh_picking_batches wpb
WHERE
wpb.batch_id = wdd.batch_id
) inv_allocation_date,
(
SELECT
MAX(trunc(trl.status_date))
FROM
apps.mtl_txn_request_lines_v trl,
apps.mtl_txn_request_headers trh
WHERE
trl.line_id = wdd.move_order_line_id
AND trl.header_id = trh.header_id
AND trl.line_status = 5
) pick_date,
--For orders shipped outside of DSCS below code is added to consider oola.last_update_date
CASE
WHEN oola.flow_status_code = 'CLOSED'
AND wnd.confirm_date IS NULL THEN
nvl(oola.actual_shipment_date, oola.last_update_date)
ELSE
wnd.confirm_date
END ship_date,
TO_DATE(wdd.attribute7, 'YYYY-MM-DD HH24:MI:SS') delivered_date,
receipt.creation_date receive_date,
wdd.tracking_number tracking_number,
(
SELECT
carrier_name
FROM
apps.wsh_carriers_v wcv
WHERE
wcv.carrier_id = wnd.carrier_id
AND ROWNUM = 1
) carrier_name,
wdd.attribute6 expected_delivery_date,
oola.attribute6 first_attempted_delivery_date
FROM
apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.mtl_parameters mp,
apps.ar_customers ac,
apps.mtl_system_items_kfv msi,
apps.hr_all_organization_units hou,
apps.hz_cust_site_uses_all hcsua,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_parties hp,
apps.hz_cust_site_uses_all hcsuab,
apps.hz_cust_acct_sites_all hcasab,
apps.hz_party_sites hpsb,
apps.hz_locations hlb,
apps.hz_parties hpb,
apps.po_requisition_lines_all prl,
apps.po_requisition_headers_all prh,
apps.per_all_people_f ppf,
apps.mtl_parameters mpw,
apps.po_location_associations_all pla,
apps.mtl_parameters shipto,
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_new_deliveries wnd,
(
SELECT DISTINCT
MAX(rcv.creation_date)
OVER(PARTITION BY oola1.line_id) creation_date,
SUM(rsl.quantity_received)
OVER(PARTITION BY oola1.line_id) quantity_received,
oola1.line_id
FROM
apps.oe_order_lines_all oola1,
apps.rcv_shipment_lines rsl,
apps.po_requisition_lines_all prla,
apps.rcv_shipment_headers rsh,
apps.rcv_transactions rcv
WHERE
oola1.source_document_line_id = prla.requisition_line_id (+)
AND prla.requisition_line_id = rsl.requisition_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rcv.shipment_line_id = rsl.shipment_line_id
AND transaction_type = 'RECEIVE'
AND rsh.receipt_num IS NOT NULL
AND rsl.quantity_received > 0
) receipt
WHERE
ooha.header_id = oola.header_id
AND oola.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = oola.ship_from_org_id
AND mp.organization_id = msi.organization_id
AND ac.customer_id = ooha.sold_to_org_id
AND hou.organization_id = ooha.org_id
AND ooha.booked_flag = 'Y'
AND ooha.cancelled_flag = 'N'
AND ooha.ship_to_org_id = hcsua.site_use_id (+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
AND hcasa.party_site_id = hps.party_site_id (+)
AND hps.location_id = hl.location_id (+)
AND hps.party_id = hp.party_id (+)
AND ooha.invoice_to_org_id = hcsuab.site_use_id
AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hpsb.location_id = hlb.location_id
AND hpsb.party_id = hpb.party_id
AND oola.source_document_line_id = prl.requisition_line_id (+)
AND prh.requisition_header_id (+) = prl.requisition_header_id
AND prh.preparer_id = ppf.person_id (+)
AND trunc(sysdate) BETWEEN trunc(ppf.effective_start_date(+)) AND trunc(ppf.effective_end_date(+))
AND mpw.organization_id = oola.ship_from_org_id
AND hcsua.site_use_id = pla.site_use_id (+)
AND pla.organization_id = shipto.organization_id (+)
AND oola.line_id = receipt.line_id (+)
AND oola.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wnd.delivery_id (+) = wda.delivery_id
AND mp.attribute3 ='RAD'
AND mp.attribute5 != 'RAD-1'
AND oola.flow_status_code != 'CANCELLED'
AND wdd.attribute7 IS NOT NULL
AND wdd.released_status = 'C'
AND wdd.source_code = 'OE'
AND ooha.attribute3 in ( 'ARMA','RMA')
-- and ooha.order_number ='126559'
AND EXISTS (
SELECT
1
FROM
apps.wsh_delivery_details wdd1
WHERE
1 = 1
AND source_code = 'OE'
AND oola.line_id = wdd1.source_line_id
)
) inner_sql
GROUP BY
ship_from,
site,
order_number,
item_code,
item_desc,
uin,
business_unit,
site_type,
demand_type,
order_month,
category
HAVING
MIN(ordered_quantity) = SUM(shipped_quantity) |
