Oracle EBS 销售时物料保留出错相关症状和处理方法
来源：互联网 发布：中国二手车交易数据 编辑：程序博客网 时间：2024/04/28 16:42
之前,我在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发运事务处理订单行要保留的数量,找出哪一些保留是要修改或新增.
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_code2.对于一些发运事务处理订单行没有产生保留,要用保留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
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环境上使用.
- Oracle EBS 销售时物料保留出错相关症状和处理方法
- Oracle EBS “处理物料搬运单”出错解决方法
- Oracle EBS OM处理销售挑库卡在待定事务处理和保留中
- Oracle EBS OM处理销售挑库卡在待定事务处理和保留中
- Oracle EBS OM销售订单行状态“施实”处理方法
- Oracle EBS 物料与客户物料查询
- Oracle EBS导入物料接口
- Oracle EBS OM RMA销售退货异常处理(Datafix)
- Oracle EBS发放销售订单
- Oracle EBS: 物料的属性设定
- Oracle EBS: 物料的属性设定
- INV:物料现有量，可保留量，可处理提取方法
- Oracle EBS Inventory Item 导入（库存物料导入）
- Oracle EBS Inventory Item 导入（库存物料导入）
- Oracle EBS 物料事务处理未产生会计分录
- Oracle EBS相关的站点
- (Oracle EBS)和标准用户有关的处理的API
- 递归方法计划销售订单的计划物料成本
- joj1018
- android/ophone中本机号码的获取
- Win7 系统安装VS2008没反应 点击安装一闪就没有反应
- test_jiathis
- oracle9.2数据库大表优化
- Oracle EBS 销售时物料保留出错相关症状和处理方法
- WINDOWS 逻辑坐标 设备坐标 屏幕坐标 客户区坐标 .
- VC中判断目录,文件是否存在,创建目录，求目录或文件大小的方法
- 一个操作系统的实现---内核雏形
- 字符串的左旋转
- 随手记
- xml
- Linux学习笔记
- 2011NBA季后赛playbyplay数据库
