Query to finde orders with no stock for shipping in r12

--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

Post a Comment