转自:https://blog.csdn.net/chenxianping/article/details/40917577
Oracle EBS OM RMA销售退货异常处理(Datafix)
2014年11月08日 08:09:37 AlanChen 阅读数:7174
系统版本:
RDBMS : 9.2.0.6.0
Oracle 应用产品 : 11.5.10.2
问题症状:
1.通过物料事务处理查看销售订单行退货记录显示已经入库为40;
2.销售退货订单行已经接收入库。但是,订单行状态仍然是“等待退货”,发运数量为空;
3.销售退货仍然能做RMA接收40数量。但是,查看接收事务处理记录显示已经接收入库了;
4.AR接口行和AR事务处理没有找到对应销售退货订单行的记录;
5.销售退货订单行工作流报错;
EBS群下的朋友说要Datafix。但是,遗憾的是没有提供具体的解决方案和PLSQL代码。也朋友建议尝试如下操作
1.选中对应订单行,“右击”分别选择“订单进程”和“重试出错的活动”,查看对应订单行的工作流的活动“等待接收”状态仍然是“错误”。
2.“状态监控程序”查找到对应的销售退货订单行的工作流并且做“重试”操作。但是,该方法还是不行。然后,我就开始自作聪明做工作流程“倒退”也是不行,接着又“更新属性”,折腾好久,问题仍然没有解决。
在度娘上好搜索好久,也试用了好多关键没有找到想要的结果。
没有办法,借用朋友的账号上Oracle EBS官方上查找处理方法,最后查找到有一个SR与我们问题症状相似,RMA LINE STUCK IN "AWAITING_RETURN" OR "AWAITING_RETURN_DISPOSITION" (文档 ID 378221.1)网址:https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=514804030234119&id=378221.1&displayIndex=4&_afrWindowMode=0&_adf.ctrl-state=2xpv9x45m_378
由于对自己的英文没有自信,不敢在生产环境贸然行事。待历史备份数据在测试环境上Clone以后操作试一下。由于备份数据是历史数量,没有操作过订单行工作流程“重试”、“倒退”和“更新属性”等等。按照官方提供的PLSQL操作以后,销售退货行状态“已关闭”,没有需要退货行接收记录,AR接口也产生对应记录,自动开票“自动开票主程序”,通过对各项数据检查没有发现什么问题。
当在生产环境上操作时,发现没有Clone环境上那么顺利了。由于之前在生产环境对订单行工作流操作过“重试”、“倒退”和“更新属生”等等,提示错误如下:
ORA-20001: OE_RMA_WF.Wait_For_Receiving(OEOL, 263115, 161649, RUN) Wf_Engine_Util.Function_Call(OE_RMA_WF.WAIT_FOR_RECEIVING, OEOL, 263115, 161649, RUN) |
并且订单行的子流程也提示错误:
OE_STANDARD_WF.STANDARD_BLOCK(OMERROR, WF11118,162379, RUN) Wf_Engine_Util.Function_Call(OE_ERROR_WF.Set_entity_Descriptor, OMERROR, WF11118,162379, RUN) |
最后,没有办法只能重新Clone生产环境历史备份数据,把生产环境上关于该订单行流程相关记录删除,删除之前注意备份数据,再把Clone环境上关于该订单行流程数据导入到生产环境。执行官方提供的PLSQL才能顺利完成。
解决方法:
由于对生产环上订单行流程操作过“重试”、“倒退”和“更新属性”,导致执行官方提供的ontd0018.sql不执行,需求修订单行流程的数据。操作方法:
备份数据:
-
--备份数据
-
create table cux.WF_ITEMS as
-
select * from WF_ITEMS where ITEM_KEY='263115' for update;
-
delete from WF_ITEMS where ITEM_KEY='263115';
-
create table cux.wf_item_activity_statuses as
-
Select * From wf_item_activity_statuses wf where wf.item_key='263115';
-
delete from wf_item_activity_statuses wf where wf.item_key='263115';
-
create table cux.WF_ITEM_ACTIVITY_STATUSES_H as
-
SELECT * FROM WF_ITEM_ACTIVITY_STATUSES_H WHERE ITEM_KEY='263115';
-
Delete FROM WF_ITEM_ACTIVITY_STATUSES_H WHERE ITEM_KEY='263115'
-
--子流程提示错误记录
-
Insert Into cux.wf_item_activity_statuses
-
Select * From cux.wf_item_activity_statuses wf where wf.item_key In ('WF11148','WF11146');
-
Insert Into cux.WF_ITEM_ACTIVITY_STATUSES_H
-
SELECT * FROM WF_ITEM_ACTIVITY_STATUSES_H WHERE ITEM_KEY In ('WF11148','WF11146');
-
Insert Into cux.WF_ITEMS
-
select * from WF_ITEMS where ITEM_KEY In ('WF11148','WF11146');
-
--从Clone环境导入数据
-
select * from WF_ITEMS where ITEM_KEY='263115' for update;
-
Select * From wf_item_activity_statuses wf where wf.item_key='263115' for update;
-
SELECT * FROM WF_ITEM_ACTIVITY_STATUSES_H WHERE ITEM_KEY='263115' for update;
-
<span style="color:black;"><span style="font-size:14px;">注意:</span></span>
ITEM_KEY ='263115'为销售订单行的LINE_ID;ITEM_KEYIn ('WF11148','WF11146')为之流程项目关键字的值。
然后执行ontd0018.sql,如下PLSQL
1. 检查要DataFix的记录
-
--&report_yn默认值为Y
-
select oh.order_number,
-
ol.line_number || '.' || ol.shipment_number line_number,
-
ol.line_id,
-
ol.flow_status_code,
-
ol.ordered_quantity,
-
mmt.transaction_date received_date,
-
rcv.quantity received_quantity
-
from OE_ORDER_HEADERS_ALL OH,
-
OE_ORDER_LINES_ALL OL,
-
RCV_TRANSACTIONS RCV,
-
MTL_MATERIAL_TRANSACTIONS MMT
-
where oh.header_id = ol.header_id
-
and ol.line_id = rcv.oe_order_line_id
-
and ol.line_id = mmt.trx_source_line_id
-
and mmt.transaction_type_id = 15
-
and mmt.rcv_transaction_id = rcv.transaction_id
-
and ol.flow_status_code = 'AWAITING_RETURN'
-
and nvl(upper('&report_yn'), 'N') = 'Y';
2. 然后,根据检查结果的订单行LINE_ID批量或逐行执行
-
declare
-
l_line_id number := 263115;
-
l_ordered_qty number;
-
cursor line_info is
-
select line_id, ordered_quantity
-
from oe_order_lines_all ool
-
where ool.line_id = l_line_id
-
and ool.flow_status_code = 'AWAITING_RETURN'
-
and exists
-
(select 'x'
-
from mtl_material_transactions mmt, rcv_transactions rcv
-
where mmt.trx_source_line_id = ool.line_id
-
and mmt.transaction_type_id = 15
-
and rcv.oe_order_line_id = ool.line_id
-
and mmt.rcv_transaction_id = rcv.transaction_id)
-
for update nowait;
-
l_user_id number;
-
l_resp_id number;
-
l_appl_id number;
-
x_return_status varchar2(10);
-
x_msg_count number;
-
x_msg_data varchar2(2000);
-
begin
-
if nvl(l_line_id, 0) > 0 then
-
open line_info;
-
fetch line_info
-
into l_line_id, l_ordered_qty;
-
if line_info%notfound then
-
close line_info;
-
dbms_output.put_line('Error: Invalid Line Id, Re-enter.');
-
return;
-
end if;
-
close line_info;
-
else
-
return;
-
end if;
-
Begin
-
select number_value
-
into l_user_id
-
from wf_item_attribute_values
-
where item_type = 'OEOL'
-
and item_key = l_line_id
-
and name = 'USER_ID';
-
select number_value
-
into l_resp_id
-
from wf_item_attribute_values
-
where item_type = 'OEOL'
-
and item_key = l_line_id
-
and name = 'RESPONSIBILITY_ID';
-
select number_value
-
into l_appl_id
-
from wf_item_attribute_values
-
where item_type = 'OEOL'
-
and item_key = l_line_id
-
and name = 'APPLICATION_ID';
-
Exception
-
When No_Data_Found Then
-
dbms_output.put_line('Error: Line flow does not exist.');
-
return;
-
End;
-
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
-
update oe_order_lines
-
set fulfilled_quantity = null,
-
shipped_quantity = null,
-
last_updated_by = -99999999,
-
last_update_date = sysdate
-
where line_id = l_line_id;
-
begin
-
oe_rma_receiving.push_receiving_info(l_line_id,
-
l_ordered_qty,
-
'NO PARENT',
-
'RECEIVE',
-
'N',
-
x_return_status,
-
x_msg_count,
-
x_msg_data);
-
if x_return_status = 'S' then
-
oe_rma_receiving.push_receiving_info(l_line_id,
-
l_ordered_qty,
-
'RECEIVE',
-
'DELIVER',
-
'N',
-
x_return_status,
-
x_msg_count,
-
x_msg_data);
-
end if;
-
oe_debug_pub.add('no. of OE messages :' || x_msg_count, 1);
-
dbms_output.put_line('no. of OE messages :' || x_msg_count);
-
for k in 1 .. x_msg_count loop
-
x_msg_data := oe_msg_pub.get(p_msg_index => k, p_encoded => 'F');
-
oe_debug_pub.add(substr(x_msg_data, 1, 255));
-
oe_debug_pub.add(substr(x_msg_data, 255, length(x_msg_data)));
-
dbms_output.put_line('Error msg: ' || substr(x_msg_data, 1, 200));
-
end loop;
-
fnd_msg_pub.count_and_get(p_encoded => 'F',
-
p_count => x_msg_count,
-
p_data => x_msg_data);
-
oe_debug_pub.add('no. of FND messages :' || x_msg_count, 1);
-
dbms_output.put_line('no. of FND messages :' || x_msg_count);
-
for k in 1 .. x_msg_count loop
-
x_msg_data := fnd_msg_pub.get(p_msg_index => k, p_encoded => 'F');
-
dbms_output.put_line('Error msg: ' || substr(x_msg_data, 1, 200));
-
oe_debug_pub.add(substr(x_msg_data, 1, 255));
-
end loop;
-
if x_return_status <> 'S' then
-
oe_debug_pub.add('Error occurred, rolling back changes.', 1);
-
dbms_output.put_line('Error occurred, please fix the errors and retry.');
-
rollback;
-
else
-
commit;
-
end if;
-
end;
-
dbms_output.put_line('For details, see OM Debug File: ' ||
-
OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);
-
end;