之前,我在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