首页 > DataFix > DATAFIX_OM行流批量Retry

DATAFIX_OM行流批量Retry

2008年12月16日 商云方 发表评论 阅读评论

/*******************************************************************************
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条。全部开票成功。

 

 

关于作者:

昵称:商云方
档案信息:顾问, HAND张江技术中心
联系方式:你可以通过yunfang.shang@hand-china.com联系作者
点击查看商云方发表过的所有文章...
本文永久链接: http://blog.retailsolution.cn/archives/2149

 

 

对本文的评价:

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

 

 

分类: DataFix 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.
您必须在 登录 后才能发布评论.