--Orders having no stock for shipping
SELECT a.flow_status_code,
c.order_number,
c.attribute7 consolidated_order,
a.header_id,
a.ordered_quantity,
a.ordered_item,
a.user_item_description,
b.SUBINVENTORY_CODE,
a.line_number,
SUM(b.primary_transaction_quantity) OnHand,
(SUM(b.primary_transaction_quantity)-a.ordered_quantity) diff
FROM apps.oe_order_lines_all a,
APPS.mtl_onhand_quantities_detail b,
apps.oe_order_headers_all c
WHERE a.inventory_item_id=b.inventory_item_id(+)
AND a.ship_from_org_id =b.organization_id(+)
AND a.header_id =c.header_id
AND a.CANCELLED_FLAG = 'N'
AND a.SHIPPABLE_FLAG = 'Y'
AND a.header_id IN
(SELECT header_id
FROM apps.oe_order_headers_all
WHERE order_number IN (20370480, 20380248, 20380239, 20380239, 20380239, 20380239, 20380239, 20380239, 20370106, 20370106, 20380234, 20380234, 20380234, 20380234, 20379129, )
)
GROUP BY a.ordered_item,
a.header_id,
a.ordered_quantity,
c.order_number,
a.line_number,
c.attribute7,
b.SUBINVENTORY_CODE,
a.flow_status_code,
a.user_item_description
ORDER BY c.order_number
SELECT a.flow_status_code,
c.order_number,
c.attribute7 consolidated_order,
a.header_id,
a.ordered_quantity,
a.ordered_item,
a.user_item_description,
b.SUBINVENTORY_CODE,
a.line_number,
SUM(b.primary_transaction_quantity) OnHand,
(SUM(b.primary_transaction_quantity)-a.ordered_quantity) diff
FROM apps.oe_order_lines_all a,
APPS.mtl_onhand_quantities_detail b,
apps.oe_order_headers_all c
WHERE a.inventory_item_id=b.inventory_item_id(+)
AND a.ship_from_org_id =b.organization_id(+)
AND a.header_id =c.header_id
AND a.CANCELLED_FLAG = 'N'
AND a.SHIPPABLE_FLAG = 'Y'
AND a.header_id IN
(SELECT header_id
FROM apps.oe_order_headers_all
WHERE order_number IN (20370480, 20380248, 20380239, 20380239, 20380239, 20380239, 20380239, 20380239, 20370106, 20370106, 20380234, 20380234, 20380234, 20380234, 20379129, )
)
GROUP BY a.ordered_item,
a.header_id,
a.ordered_quantity,
c.order_number,
a.line_number,
c.attribute7,
b.SUBINVENTORY_CODE,
a.flow_status_code,
a.user_item_description
ORDER BY c.order_number
No comments:
Post a Comment