SELECT osto.name oe_customer_name,
osto.customer_number oe_customer_number,
oeh.header_id oe_header_id,
oeh.order_number oe_number,
OTYPE.name oe_type,
oeh.ordered_date oe_order_date,
oel.line_id oe_line_id,
oel.line_number
|| DECODE (oel.shipment_number,
NULL, NULL,
'.' || oel.shipment_number)
|| DECODE (oel.option_number, NULL, NULL, '.' || oel.option_number)
line_shipment_option_number,
oel.item_identifier_type,
OEL.INVENTORY_ITEM_ID,
OEL.ORDERED_ITEM_ID,
OEL.ORDERED_ITEM,
poh.segment1 po_number,
pol.line_num po_line_number,
porel.release_num po_release_number,
poll.shipment_num po_shipment_number,
porh.segment1 requisition_number,
porl.line_num requisition_line_number,
msi1.description po_item_description,
msi2.description rq_item_description,
DECODE (
oel.line_set_id,
NULL, (SELECT ordered_quantity
FROM oe_order_lines_all
WHERE line_id = oel.line_id
AND line_set_id IS NULL
AND NVL (org_id, 0) = NVL (oel.org_id, 0)),
(SELECT ordered_quantity
FROM oe_order_lines_all
WHERE line_set_id = oel.line_set_id
AND line_id = oel.line_id
AND line_set_id IS NOT NULL
AND NVL (org_id, 0) = NVL (oel.org_id, 0)))
oe_quantity,
poll.quantity - NVL (poll.quantity_cancelled, 0) po_shipment_quantity,
porl.quantity - NVL (porl.quantity_cancelled, 0) rq_quantity,
oel.order_quantity_uom oe_unit,
muom1.uom_code po_unit,
muom2.uom_code rq_unit,
oel.schedule_ship_date oe_schedule_date,
poll.need_by_date po_needby_date,
porl.need_by_date rq_needby_date,
zloc_oe.address1 oe_ship_to_location,
NVL (rloc_po.location_CODE, zloc_po.address1) po_ship_to_location,
NVL (rloc_rq.location_code, zloc_rq.address1) rq_ship_to_location,
DECODE (poh.user_hold_flag, 'Y', 'Y', pol.user_hold_flag) po_hold
FROM mtl_system_items_vl msi,
mtl_system_items_vl msi1,
mtl_system_items_vl msi2,
mtl_units_of_measure muom1,
mtl_units_of_measure muom2,
po_line_locations_all poll,
po_releases porel,
po_lines_all pol,
po_headers_all poh,
po_requisition_lines_all porl,
po_requisition_headers_all porh,
oe_order_lines_all oel,
OE_TRANSACTION_TYPES_TL OTYPE,
oe_order_headers oeh,
oe_drop_ship_sources oes,
oe_sold_to_orgs_v osto,
HR_LOCATIONS rloc_po,
HR_LOCATIONS rloc_rq,
HZ_LOCATIONS zloc_oe,
HZ_LOCATIONS zloc_po,
HZ_LOCATIONS zloc_rq,
HZ_PARTY_SITES party,
HZ_CUST_ACCT_SITES_ALL acct,
HZ_CUST_SITE_USES_ALL cust
WHERE OEH.HEADER_ID = OES.HEADER_ID
AND NVL (oeh.org_id, 0) = NVL (:p_organization_id, 0)
AND NVL (oel.org_id, NVL (:p_organization_id, 0)) =
NVL (:p_organization_id, 0)
AND oeh.sold_to_org_id = osto.customer_id(+)
AND OTYPE.TRANSACTION_TYPE_ID = OEH.ORDER_TYPE_ID
AND OTYPE.language = USERENV ('lang')
AND oes.line_id = oel.line_id(+)
AND MSI.INVENTORY_ITEM_ID = PORL.ITEM_ID
AND NVL (msi.organization_id, 0) = :c_master_org
AND poh.po_header_id(+) = oes.po_header_id
AND pol.po_line_id(+) = oes.po_line_id
AND msi1.inventory_item_id(+) = pol.item_id
AND NVL (msi1.organization_id, NVL (:c_master_org, 0)) = :c_master_org
AND msi2.inventory_item_id(+) = porl.item_id
AND NVL (msi2.organization_id, 0) = :c_master_org
AND poll.line_location_id(+) = oes.line_location_id
AND porh.requisition_header_id(+) = oes.requisition_header_id
AND porl.requisition_line_id(+) = oes.requisition_line_id
AND porel.po_release_id(+) = poll.po_release_id
AND muom1.unit_of_measure(+) = pol.unit_meas_lookup_code
AND muom2.unit_of_measure(+) = porl.unit_meas_lookup_code
AND rloc_po.location_id(+) = poll.ship_to_location_id
AND rloc_rq.location_id(+) = porl.deliver_to_location_id
AND zloc_po.location_id(+) = poll.ship_to_location_id
AND zloc_rq.location_id(+) = porl.deliver_to_location_
AND CUST.SITE_USE_ID(+) = oel.ship_to_org_id
AND NVL (CUST.STATUS, 'A') = 'A'
AND NVL (CUST.SITE_USE_CODE, 'SHIP_TO') = 'SHIP_TO'
AND ACCT.CUST_ACCT_SITE_ID(+) = CUST.CUST_ACCT_SITE_ID
AND PARTY.PARTY_SITE_ID(+) = ACCT.PARTY_SITE_ID
AND zloc_oe.location_id(+) = PARTY.location_id
AND (oes.po_header_id IS NOT NULL
AND (pol.item_id != oel.inventory_item_id
OR TRUNC (poll.need_by_date) !=
TRUNC (oel.schedule_ship_date)
OR poll.ship_to_location_id != PARTY.LOCATION_ID
OR muom1.uom_code != oel.order_quantity_uom
OR DECODE (
0,
0, (SELECT SUM (ordered_quantity)
FROM oe_order_lines_all
WHERE line_set_id = oel.line_set_id
AND line_set_id IS NOT NULL
AND NVL (org_id, 0) = NVL (oel.org_id, 0))) NOT IN
(SELECT SUM (quantity)
FROM po_line_locations_all poll2,
oe_drop_ship_sources oes2
WHERE poll2.line_location_id = oes2.line_location_id
AND oes2.line_id = oel.line_id)
OR DECODE (
0,
0, (SELECT ordered_quantity
FROM oe_order_lines_all
WHERE line_id = oel.line_id
AND line_set_id IS NULL
AND NVL (org_id, 0) = NVL (oel.org_id, 0))) NOT IN
(SELECT SUM (quantity)
FROM po_line_locations_all poll2,
oe_drop_ship_sources oes2
WHERE poll2.line_location_id = oes2.line_location_id
AND oes2.line_id = oel.line_id)
OR DECODE (poh.user_hold_flag,
'Y', 1,
DECODE (pol.user_hold_flag, 'Y', 1, 0)) NOT IN
(SELECT DECODE (COUNT (order_hold_id), 0, 0, 1)
FROM oe_order_holds_all orh,
oe_hold_sources_all ohs,
oe_hold_definitions ohd
WHERE orh.header_id = oel.header_id
AND orh.hold_release_id IS NULL
AND orh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND ohd.item_type IS NULL
AND ohd.activity_name IS NULL
AND (orh.line_id = oel.line_id
OR orh.line_id IS NULL)))
OR (oes.po_header_id IS NULL
AND oes.requisition_header_id IS NOT NULL
AND (DECODE (oel.inventory_item_id, NULL, -98, porl.item_id) !=
NVL (oel.inventory_item_id, -99)
OR TRUNC (
DECODE (oel.schedule_ship_date,
NULL, SYSDATE,
porl.need_by_date)) !=
TRUNC (NVL (oel.schedule_ship_date, SYSDATE - 1))
OR NVL (oel.ship_to_org_id, -98) !=
NVL (oel.ship_to_org_id, -99)
OR porl.deliver_to_location_id != PARTY.LOCATION_ID
OR muom2.uom_code != oel.order_quantity_uom
OR porl.quantity !=
DECODE (
0,
0, (SELECT SUM (ordered_quantity)
FROM oe_order_lines_all
WHERE line_set_id = oel.line_set_id
AND line_set_id IS NOT NULL
AND NVL (org_id, 0) =
NVL (oel.org_id, 0)))
OR porl.quantity !=
DECODE (
0,
0, (SELECT ordered_quantity
FROM oe_order_lines_all
WHERE line_id = oel.line_id
AND line_set_id IS NULL
AND NVL (org_id, 0) =
NVL (oel.org_id, 0)))
OR 0 NOT IN
(SELECT DECODE (COUNT (order_hold_id), 0, 0, 1)
FROM oe_order_holds_all orh,
oe_hold_sources_all ohs,
oe_hold_definitions ohd
WHERE orh.header_id = oel.header_id
AND orh.hold_source_id =
ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND ohd.item_type IS NULL
AND ohd.activity_name IS NULL
AND orh.hold_release_id IS NULL
AND (orh.line_id = oel.line_id
OR orh.line_id IS NULL))))
OR oel.line_id IS NULL)
ORDER BY oeh.order_number
