Oracle EBS 销售时物料保留出错相关症状和处理方法

之前,我在Oracle EBS R12版本中遇到过销售订单发运时"物料保留出错",伴随着物料事处处理接口错误和发运事务处事处理不了
背景:
销售订单发运事务处理或销售订发放,子库存的保留和物料事务处理出错.
主要症状:
1.INV:物料保留窗体中的题头的值为空
2.INV:物料事务处理接口错误解释"在释放保留时出现错误."
3.OM:发运事务处理下一步为"运行接口"
4.AR:应收事务处理已经正常产生
若以上没有第1个症状的情况,还是比较简单方法去"系统管理员"提交"工作流后台流程"(Workflow Background Process)就可以处理;当第1个症状时,若保留中的数量与发运事务处理发运数一致比较好办,也可以提交一下"工作流后台流程"(Workflow Background Process)试一试.但是,若保留中的数量与发运事务处理发运数不一致,则麻烦太了.这个错误是Oracle EBS的BUG,公司提交SR,半个月Oracle的也没有回复这事处理.做事效率也太低了.真是"店大欺负客,客大欺店"呀!
原因:.
我们公司用户太NB了,一个订单行拆分成70多行致导保留出错.
解决方法:只能通过保留API来改修或新增销售订单的保留.
1.找到SO发运事务处理订单行要保留的数量,找出哪一些保留是要修改或新增.

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

2.对于一些发运事务处理订单行没有产生保留,要用保留API添加销售订单的保留量,处理SQL

DECLARE   p_rsv_rec           inv_reservation_global.mtl_reservation_rec_type;   p_serial_number     inv_reservation_global.serial_number_tbl_type;   x_return_status     VARCHAR2(1);   x_msg_count         NUMBER;   x_msg_data          VARCHAR2(2000);   x_serial_number     inv_reservation_global.serial_number_tbl_type;   x_quantity_reserved NUMBER;   x_reservation_id    NUMBER;   Cursor Cr is     Select MMT.Creation_Date - + (-15 / 24 / 60) REQUIREMENT_DATE,            MMT.ORGANIZATION_ID,            MMT.INVENTORY_ITEM_ID,            2 DEMAND_SOURCE_TYPE_ID,            MMT.SOURCE_LINE_ID,            MMT.TRANSACTION_UOM,            -1 * Sum(MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,            13 SUPPLY_SOURCE_TYPE_ID,            MMT.SUBINVENTORY_CODE,            MMT.TRANSACTION_SOURCE_ID       From MTL_TRANSACTIONS_INTERFACE MMT, MTL_RESERVATIONS_ALL_V MRA      Where MMT.ORGANIZATION_ID = 429        And MMT.SOURCE_CODE = 'ORDER ENTRY'        And MMT.ORGANIZATION_ID = MRA.ORGANIZATION_ID(+)        And MMT.SOURCE_LINE_ID = MRA.DEMAND_SOURCE_LINE_ID(+)        And MRA.RESERVATION_ID Is Null        --And MMT.SOURCE_LINE_ID = 664042      Group By MMT.Creation_Date - + (-15 / 24 / 60),               MMT.ORGANIZATION_ID,               MMT.INVENTORY_ITEM_ID,               MMT.SOURCE_LINE_ID,               MMT.TRANSACTION_UOM,               MMT.SUBINVENTORY_CODE,               MMT.TRANSACTION_SOURCE_ID; ---221111040022 BEGIN   For Rs In Cr Loop     p_rsv_rec.reservation_id               := NULL;     p_rsv_rec.requirement_date             := Rs.REQUIREMENT_DATE;     p_rsv_rec.organization_id              := Rs.ORGANIZATION_ID;     p_rsv_rec.inventory_item_id            := Rs.INVENTORY_ITEM_ID;     p_rsv_rec.demand_source_type_id        := Rs.DEMAND_SOURCE_TYPE_ID;     p_rsv_rec.demand_source_name           := NULL;     p_rsv_rec.demand_source_header_id      := Rs.TRANSACTION_SOURCE_ID;     p_rsv_rec.demand_source_line_id        := Rs.SOURCE_LINE_ID;     p_rsv_rec.demand_source_delivery       := NULL;     p_rsv_rec.primary_uom_code             := NULL;     p_rsv_rec.primary_uom_id               := NULL;     p_rsv_rec.reservation_uom_code         := Rs.TRANSACTION_UOM;     p_rsv_rec.reservation_uom_id           := NULL;     p_rsv_rec.reservation_quantity         := Rs.TRANSACTION_QUANTITY;     p_rsv_rec.primary_reservation_quantity := NULL;     p_rsv_rec.detailed_quantity            := NULL;     p_rsv_rec.autodetail_group_id          := NULL;     p_rsv_rec.external_source_code         := NULL;     p_rsv_rec.external_source_line_id      := NULL;     p_rsv_rec.supply_source_type_id        := Rs.SUPPLY_SOURCE_TYPE_ID;     p_rsv_rec.supply_source_header_id      := NULL;     p_rsv_rec.supply_source_line_id        := NULL;     p_rsv_rec.supply_source_name           := NULL;     p_rsv_rec.supply_source_line_detail    := NULL;     p_rsv_rec.revision                     := NULL;     p_rsv_rec.subinventory_code            := Rs.SUBINVENTORY_CODE;     p_rsv_rec.subinventory_id              := NULL;     p_rsv_rec.locator_id                   := NULL;     p_rsv_rec.lot_number                   := NULL;     p_rsv_rec.lot_number_id                := NULL;     p_rsv_rec.pick_slip_number             := NULL;     p_rsv_rec.lpn_id                       := NULL;     p_rsv_rec.ship_ready_flag              := NULL;     p_rsv_rec.attribute_category           := NULL;     p_rsv_rec.attribute1                   := NULL;     p_rsv_rec.attribute2                   := NULL;     p_rsv_rec.attribute3                   := NULL;     p_rsv_rec.attribute4                   := NULL;     p_rsv_rec.attribute5                   := NULL;     p_rsv_rec.attribute6                   := NULL;     p_rsv_rec.attribute7                   := NULL;     p_rsv_rec.attribute8                   := NULL;     p_rsv_rec.attribute9                   := NULL;     p_rsv_rec.attribute10                  := NULL;     p_rsv_rec.attribute11                  := NULL;     p_rsv_rec.attribute12                  := NULL;     p_rsv_rec.attribute13                  := NULL;     p_rsv_rec.attribute14                  := NULL;     p_rsv_rec.attribute15                  := NULL;         Update OE_ORDER_LINES_ALL        Set open_flag = 'Y'      Where LINE_ID = Rs.SOURCE_LINE_ID;     inv_reservation_pub.create_reservation(p_api_version_number       => '1.0',                                            p_init_msg_lst             => fnd_api.g_true,                                            x_return_status            => x_return_status,                                            x_msg_count                => x_msg_count,                                            x_msg_data                 => x_msg_data,                                            p_rsv_rec                  => p_rsv_rec,                                            p_serial_number            => p_serial_number,                                            x_serial_number            => x_serial_number,                                            p_partial_reservation_flag => fnd_api.g_true,                                            p_force_reservation_flag   => fnd_api.g_false,                                            p_validation_flag          => fnd_api.g_true,                                            x_quantity_reserved        => x_quantity_reserved,                                            x_reservation_id           => x_reservation_id);     Update OE_ORDER_LINES_ALL        Set open_flag = 'N'      Where LINE_ID = Rs.SOURCE_LINE_ID;          IF x_return_status = fnd_api.g_ret_sts_success THEN       dbms_output.put_line('Reservation Created Successfully! Reservation ID: ' ||                            x_reservation_id);     ELSE       ROLLBACK;       dbms_output.put_line('Return Status: ' || x_return_status);       fnd_msg_pub.count_and_get(p_count => x_msg_count,                                 p_data  => x_msg_data);       dbms_output.put_line('Error count  : ' || to_char(x_msg_count));       dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));       FOR i IN 2 .. x_msg_count LOOP         x_msg_data := fnd_msg_pub.get;         dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));       END LOOP;         END IF;   End Loop; END;

注意:以上红色SQL是将订单关闭状态改为登记.记得在物料事处理接口运行完以后要修改成关闭,其OPEN_FLAG为Y.
3.对一些发运事务处理的订单行保留数量与发运数量不一致的,要用保留的API修改订单的保留量,处理的SQL

DECLARE   p_original_rsv_rec       inv_reservation_global.mtl_reservation_rec_type;   p_to_rsv_rec             inv_reservation_global.mtl_reservation_rec_type;   p_original_serial_number inv_reservation_global.serial_number_tbl_type;   p_to_serial_number       inv_reservation_global.serial_number_tbl_type;   x_return_status          VARCHAR2(1);   x_msg_count              NUMBER;   x_msg_data               VARCHAR2(2000);   x_quantity_reserved      NUMBER;   x_secondary_quantity_reserved Number;   Cursor Cr is     Select MRA.RESERVATION_ID,            MMT.TRANSACTION_UOM,            -1 * Sum(MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,            MMT.TRANSACTION_SOURCE_ID       From MTL_TRANSACTIONS_INTERFACE MMT, MTL_RESERVATIONS_ALL_V MRA      Where MMT.ORGANIZATION_ID = 429        And MMT.SOURCE_CODE = 'ORDER ENTRY'        And MMT.ORGANIZATION_ID = MRA.ORGANIZATION_ID(+)        And MMT.SOURCE_LINE_ID = MRA.DEMAND_SOURCE_LINE_ID(+)        And MRA.RESERVATION_ID Is Not Null              ---And MMT.SOURCE_LINE_ID = 662459      Group By MRA.RESERVATION_ID, MMT.TRANSACTION_UOM,MMT.TRANSACTION_SOURCE_ID; BEGIN   For Rs In Cr Loop     p_original_rsv_rec.reservation_id := Rs.RESERVATION_ID;     p_to_rsv_rec.reservation_uom_code := Rs.TRANSACTION_UOM;     p_to_rsv_rec.reservation_quantity := Rs.TRANSACTION_QUANTITY;     p_to_rsv_rec.demand_source_header_id :=Rs.TRANSACTION_SOURCE_ID;         inv_reservation_pub.update_reservation(p_api_version_number       => '1.0',                                            p_init_msg_lst             => fnd_api.g_true,                                            x_return_status            => x_return_status,                                            x_msg_count                => x_msg_count,                                            x_msg_data                 => x_msg_data,                                            x_quantity_reserved        => x_quantity_reserved,                                            x_secondary_quantity_reserved=>x_secondary_quantity_reserved,                                             p_original_rsv_rec         => p_original_rsv_rec,                                            p_to_rsv_rec               => p_to_rsv_rec,                                            p_original_serial_number   => p_original_serial_number,                                            p_to_serial_number         => p_to_serial_number,                                            p_validation_flag          => fnd_api.g_true,                                            p_partial_reservation_flag => fnd_api.g_false,                                            p_check_availability       => fnd_api.g_false);       IF x_return_status = fnd_api.g_ret_sts_success THEN       dbms_output.put_line('Reservation Updated Successfully! New Quantity: ' ||                            x_quantity_reserved);     ELSE       ROLLBACK;       dbms_output.put_line('Return Status: ' || x_return_status);       fnd_msg_pub.count_and_get(p_count => x_msg_count,                                 p_data  => x_msg_data);       dbms_output.put_line('Error count  : ' || to_char(x_msg_count));       dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));       FOR i IN 2 .. x_msg_count LOOP         x_msg_data := fnd_msg_pub.get;         dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));       END LOOP;         END IF;   end loop; END;

目前,我们公司没有出现过有保留量是要删除的,也可以通过保留API删除.
注意:以上方法仅供参考,Oracle EBS有一个错误,可能有好多原因导致.请大家慎重使用,一定要先在UAT环境上测试OK,才能在PROD环境上使用.