之前,我在Oracle EBS R12版本中遇到过销售订单发运时物料保留出错,伴随着物料事处处理接口错误和发运事务处事处理不了背景:销

SELECT oeh.org_id ou_id,
hou.NAME ou_name,
mso.concatenated_segments header_number,
oel.line_number || '.' || oel.shipment_number ||
decode(oel.option_number, NULL, NULL, '.' || oel.option_number) ||
decode(oel.component_number, NULL, NULL, '.' || oel.component_number) ||
decode(oel.service_number, NULL, NULL, '.' || oel.service_number) line,
mst.segment1 item_code,
oel.flow_status_code status_code,
lov.meaning status_name,
rsv.organization_id,
ood.organization_code,
ood.organization_name,
rsv.subinventory_code,
rsv.requirement_date,
rsv.reservation_quantity,
rsv.reservation_uom_code,
rsv.staged_flag,
oel.ordered_quantity,
oel.shipped_quantity,
oel.shipping_quantity
FROM inv.mtl_reservations rsv,
inv.mtl_system_items_b mst,
apps.mtl_sales_orders_kfv mso,
ont.oe_order_lines_all oel,
ont.oe_order_headers_all oeh,
apps.fnd_lookup_values_vl lov,
apps.org_organization_definitions ood,
apps.hr_operating_units hou
WHERE rsv.organization_id = mst.organization_id
AND rsv.inventory_item_id = mst.inventory_item_id
AND rsv.demand_source_header_id = mso.sales_order_id
AND rsv.demand_source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND lov.lookup_type = 'LINE_FLOW_STATUS'
AND oel.flow_status_code = lov.lookup_code
AND rsv.organization_id = ood.organization_id
AND oeh.org_id = hou.organization_id
AND rsv.demand_source_type_id = 2 --需求2是销售定单，具体看inv.mtl_txn_source_types
AND rsv.supply_source_type_id = 13 --供应13是库存，，具体看inv.mtl_txn_source_types
AND rsv.reservation_quantity = 0 --数量为0
AND nvl(rsv.staged_flag, 'N') <> 'Y' --未到待发库
AND oel.flow_status_code = 'AWAITING_SHIPPING' --状态是等待发运
AND oeh.org_id = 88
ORDER BY hou.NAME, ood.organization_code