商云方的技术博客 (沪ICP备14046774号-1)

/*******************************************************************************
date : 2007-07-06
处理者: yunfang.shang
*******************************************************************************/

/*
现象： 现在有几张订单传到库存了之后传不到应收接口了
订单号：1000002895
*/

select * from oe_order_headers_all  where order_number = ‘1000002895’

— 查订单的 POS小票号
select oel.attribute2
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oeh. order_number = ‘1000002895’

–查发票接口
select  * from  RA_INTERFACE_LINES_ALL  where attribute2 in (select oel.attribute2
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oeh. order_number = ‘1000002895’)
–返回 0 条记录，说明已经全部被处理
–查发票行
select  * from  RA_CUSTOMER_TRX_LINES_ALL  where attribute2 in  (select oel.attribute2
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oeh. order_number = ‘1000002895’)
–返回 0条记录
–说明根本就没有生成到AR接口表中

–查订单行的开票状态字段
select oel.INVOICE_INTERFACE_STATUS_CODE,oel.*,oel.lock_control
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oeh. order_number = ‘1000002895’
— INVOICE_INTERFACE_STATUS_CODE是空的 NULL, 正常情况下开票完成的应该是 YES

–从界面查该订单，订单头 已登记 状态；订单行 实施 状态。 从菜单 工具/工作流状态 看到
— 开票接口出现例外。 看错误信息是表空间扩展问题。

–查数据库日志，表空间不足。 DBA扩展表空间后。
— 客户方 IT 人员 在出错的订单行界面按右键 选择 重试出错的活动。就 retry 过去了。

–但是这种记录很多，有700多条，如果手工retry的话，工作量很大。用户希望能用程序retry.

–首先找出这些记录
select count(1) from
(
select oeh.order_number,oel.INVOICE_INTERFACE_STATUS_CODE,oel.*,oel.lock_control
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oel.INVOICE_INTERFACE_STATUS_CODE is null and oel.attribute2 is not null and line_category_code =’ORDER’
and oel.flow_status_code =’FULFILLED’
)
–707 条

— 获取其中一条的 activity , item_key 就是line_id

SELECT was.item_type,
       was.item_key,
       wpa.process_name || ‘:’ || wpa.instance_label activity,
       was.activity_status,
       was.activity_result_code,
       was.assigned_user,
       was.notification_id,
       was.begin_date,
       pro.display_name process_name,
       act.display_name activity_name
  FROM wf_item_activity_statuses was,
       wf_process_activities     wpa,
       wf_activities_vl          pro,
       wf_activities_vl          act,
       wf_items                  wi
 WHERE was.process_activity = wpa.instance_id
   AND wpa.process_item_type = pro.item_type
   AND wpa.process_name = pro.NAME
   AND wpa.process_version = pro.version
   AND wpa.activity_item_type = act.item_type
   AND wpa.activity_name = act.NAME
   AND was.item_type = wi.item_type
   AND was.item_key = wi.item_key
   AND wi.begin_date >= act.begin_date
   AND wi.begin_date < nvl(act.end_date, wi.begin_date + 1) 
   AND was.item_type = ‘OEOL’
   AND was.item_key = ‘106458’
   AND was.end_date IS NULL
   AND was.activity_status =’ERROR’ ;

— 执行retry  比如单个记录的retry :

BEGIN

  — 初始化，根据实际环境修改，这样可以获得环境变量，主要是Org_id，一般不需要
  fnd_global.apps_initialize(user_id      => 1111,
                             resp_id      => 50260,
                             resp_appl_id => 660);
  — Call the procedure
  wf_engine.handleerror(itemtype => ‘OEOL’,
                        itemkey  => ‘106457’,
                        activity => ‘LINE_DEF_INVOICE_INTERFACE_SUB:INVOICE_INTERFACE’,
                        command  => wf_engine.eng_retry);
END;

–成功执行，再去界面看，行已经Close了。
/********************************************************************************
— 批量Retry 执行 这些出错的节点：
********************************************************************************/

begin
    — 注意把当前Sql窗口中的 “输出” tab页中的缓冲区大小设置大一点，至少 100000
    — 初始化
    fnd_global.apps_initialize(user_id => 1111, resp_id => 50260,
                               resp_appl_id => 660);

    for c1 in (select oel.line_id
               from oe_order_lines_all oel
               where oel.INVOICE_INTERFACE_STATUS_CODE is null
               and oel.attribute2 is not null
               and line_category_code = ‘ORDER’
               and oel.flow_status_code = ‘FULFILLED’) loop
   
        for c2 in (SELECT was.item_type, was.item_key,
                          wpa.process_name || ‘:’ || wpa.instance_label activity,
                          was.activity_status, was.activity_result_code,
                          was.assigned_user, was.notification_id,
                          was.begin_date, pro.display_name process_name,
                          act.display_name activity_name
                   FROM wf_item_activity_statuses was,
                        wf_process_activities wpa, wf_activities_vl pro,
                        wf_activities_vl act, wf_items wi
                   WHERE was.process_activity = wpa.instance_id
                   AND wpa.process_item_type = pro.item_type
                   AND wpa.process_name = pro.NAME
                   AND wpa.process_version = pro.version
                   AND wpa.activity_item_type = act.item_type
                   AND wpa.activity_name = act.NAME
                   AND was.item_type = wi.item_type
                   AND was.item_key = wi.item_key
                   AND wi.begin_date >= act.begin_date
                   AND wi.begin_date < nvl(act.end_date, wi.begin_date + 1)
                   AND was.item_type = ‘OEOL’
                   AND was.item_key = c1.line_id
                   AND was.end_date IS NULL
                   AND was.activity_status = ‘ERROR’) loop
       
            — Call the procedure
                   
             wf_engine.handleerror(itemtype => ‘OEOL’, itemkey => c1.line_id,
                                  activity => c2.activity,
                                  command => wf_engine.eng_retry);
                                 
            dbms_output.put_line(‘c.line_id=’ || to_char(c1.line_id) || ‘     c2.activity=’ || c2.activity || ‘   be   processed’ );
            exit ;
        end loop;   
    end loop;
    –commit;
end;

/********************************************************************************
— 成功执行完毕后检查
********************************************************************************/

–再次检查
select oeh.order_number,oel.INVOICE_INTERFACE_STATUS_CODE,oel.*,oel.lock_control
from oe_order_lines_all oel, oe_order_headers_all oeh
where oeh.header_id = oel.header_id
and oel.INVOICE_INTERFACE_STATUS_CODE is null and oel.attribute2 is not null and line_category_code =’ORDER’
and oel.flow_status_code =’FULFILLED’

— 返回0条记录。 说明全部被处理了。

— 自动开票程序运行后 再运行
— 下面这句话可以证明 程序处理过的订单行均已开票成功
select b.order_number, b.line_id, a.*
       from RA_CUSTOMER_TRX_LINES_ALL a,
            (select oeh.order_number, oel.INVOICE_INTERFACE_STATUS_CODE,
                     oel.line_id, oel.lock_control
              from oe_order_lines_all oel, oe_order_headers_all oeh
              where oeh.header_id = oel.header_id
              and oel.last_update_date between
                    to_date(‘2007-7-6 23:24:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
                    to_date(‘2007-7-6 23:27:00’, ‘YYYY-MM-DD HH24:MI:SS’)) b
       where a.interface_line_attribute6 = to_char(b.line_id)
      
— 被处理的订单行是706条。对应的发票行也是706条。全部开票成功。