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

/*******************************************************************************
date : 2007-05-08
处理者: yunfang.shang
*******************************************************************************/

select * from rcv_transactions_interface
–查接收接口中来自销售订单退货的记录(error_message是错误信息提示)
SELECT rti.processing_request_id,
       rti.creation_date,
       rti.transaction_date,
       rti.group_id,
       oeh.order_number,
       rti.item_id,
       item_description,
       rti.unit_of_measure,
       rti.quantity,
       rti.source_document_code,
       rti.subinventory,
       oel.flow_status_code,
       pie.error_message
  FROM rcv_transactions_interface rti,
       oe_order_headers_all       oeh,
       oe_order_lines_all         oel,
       PO_INTERFACE_ERRORS        pie
 WHERE rti.oe_order_header_id = oeh.header_id
   AND rti.oe_order_line_id = oel.line_id
   AND rti.oe_order_header_id = oel.header_id
   AND rti.interface_transaction_id = pie.interface_line_id(+)
 ORDER BY oeh.order_number
—       
/*
364244 2007-5-7 16:57:22 2007-4-12 16:57:14 1310 1000000933 1898 振华P999手机（银） 部 1 RMA 111216601 CLOSED “RVTII-&ERR_NUMBER：子例行程序 rvtiicreate() 返回的错误原因：子例行程序 rvtiicreate() 返回时出现内部错误。措施：请记录此错误编号以及您尝试进行的活动。 请与系统管理员联系以寻”
364881 2007-5-7 20:54:16 2007-4-12 20:54:01 1312 1000000933 1898 振华P999手机（银） 部 1 RMA 111216601 CLOSED “RVTII-&ERR_NUMBER：子例行程序 rvtiicreate() 返回的错误原因：子例行程序 rvtiicreate() 返回时出现内部错误。措施：请记录此错误编号以及您尝试进行的活动。 请与系统管理员联系以寻”
366128 2007-5-8 9:13:45 2007-5-8 9:13:45 1521 1000000998 3489 天时达TS933手机（黑) 部 1 RMA 111217801 CLOSED RCV_TP_ACCT_PERIOD_CLOSED
366133 2007-5-8 9:14:13 2007-5-8 9:14:13 1524 1000000998 111 诺基亚N2610手机(黑) 部 1 RMA 111217801 CLOSED RCV_TP_ACCT_PERIOD_CLOSED
366131 2007-5-8 9:14:06 2007-5-8 9:14:06 1523 1000000998 111 诺基亚N2610手机(黑) 部 1 RMA 111217801 CLOSED RCV_TP_ACCT_PERIOD_CLOSED
366129 2007-5-8 9:13:53 2007-5-8 9:13:53 1522 1000000998 3489 天时达TS933手机（黑) 部 1 RMA 111217801 CLOSED RCV_TP_ACCT_PERIOD_CLOSED
366613 2007-5-8 4:37:32 2007-5-8 4:37:32 1519 1000001000 101 诺基亚N1112 手机(浅蓝) 部 1 RMA 111217801 AWAITING_RETURN RCV_TP_ACCT_PERIOD_CLOSED
366613 2007-5-8 4:37:32 2007-5-8 4:37:32 1519 1000001000 101 诺基亚N1112 手机(浅蓝) 部 1 RMA 111217801 AWAITING_RETURN RCV_TP_ACCT_PERIOD_CLOSED
366613 2007-5-8 4:37:32 2007-5-8 4:37:32 1519 1000001000 101 诺基亚N1112 手机(浅蓝) 部 1 RMA 111217801 AWAITING_RETURN RCV_TP_ACCT_PERIOD_CLOSED
366613 2007-5-8 4:37:32 2007-5-8 4:37:32 1519 1000001000 101 诺基亚N1112 手机(浅蓝) 部 1 RMA 111217801 AWAITING_RETURN RCV_TP_ACCT_PERIOD_CLOSED
366613 2007-5-8 4:37:32 2007-5-8 4:37:32 1519 1000001000 101 诺基亚N1112 手机(浅蓝) 部 1 RMA 111217801 AWAITING_RETURN RCV_TP_ACCT_PERIOD_CLOSED
*/

–错误信息的具体内容可以查：
select * from fnd_new_messages where message_name = ‘RCV_TP_ACCT_PERIOD_CLOSED’

/*您的事务处理日期必须在打开的会计期内

原因：您提交的接收事务处理的日期不在打开的会计期内。

措施：重新输入事务处理，使事务处理日期在打开的会计期内，或者打开事务处理日期所在的会计期。*/
—
–上述接口数据  有5条在 Awaiting_return,其他已经Close
–已经Close 的数据是因为，李进做了手工接收。
–前两条自动处理失败是因为 当时1000000933 订单的销售员没有定义收入帐户导致Cogs出错。进而导致接收失败。
–李进手工接收时，把订单对应的销售员的收入帐户添加好才处理过去。
–后几条自动处理失败是因为 当时1000000998 订单的事务处理日期在5月份，而5月份的库存会计期还没开。
–李进手工接收时，把事务日期改成4月份才处理过去。

–RMA订单行已经关闭说明接收已经被手工处理。这种接口数据需要删除
–create table rcv_trx_interface_bak as select * from rcv_transactions_interface where 1=2;
–create unique index rcv_trx_interface_u1 on rcv_trx_interface_bak(INTERFACE_TRANSACTION_ID);
–备份
INSERT INTO rcv_trx_interface_bak
  (SELECT *
     FROM rcv_transactions_interface
    WHERE INTERFACE_TRANSACTION_ID IN
          (SELECT INTERFACE_TRANSACTION_ID
             FROM rcv_transactions_interface rti, oe_order_lines_all oel
            WHERE rti.oe_order_line_id = oel.line_id
              AND rti.oe_order_header_id = oel.header_id
              AND oel.flow_status_code = ‘CLOSED’
              AND rti.transaction_status_code = ‘ERROR’
              AND rti.source_document_code = ‘RMA’));
–6行被插入
–删除
DELETE FROM rcv_transactions_interface
 WHERE INTERFACE_TRANSACTION_ID IN
       (SELECT INTERFACE_TRANSACTION_ID
          FROM rcv_transactions_interface rti, oe_order_lines_all oel       
         WHERE rti.oe_order_line_id = oel.line_id
           AND rti.oe_order_header_id = oel.header_id
           AND oel.flow_status_code = ‘CLOSED’
           AND rti.transaction_status_code = ‘ERROR’
           and rti.source_document_code = ‘RMA’);
–6行被删除

/*
在用户层打开如下系统配置文件：
a. RCV: Debug Mode=Yes
b. PO:Enable Sql Trace for Receiving Processor=Yes
c. FND: Debug Log Enabled=Yes
d. FND: Debug Log Level=Statement
e. FND: Debug Log Module=%
*/

 — 查看PO接收接口处理器的错误方法 ：参见Metalink Note : Note:299497.1   [ (老版本 Note:50903.1)]
 — 更改因为事务处理日期不在打开的会计期内的错误，可以更改事务处理日期
 — 然后提交接口处理程序。如下面的Sql
 declare
        l_request_id number ;
        X_Group_Id  number := 182;         — 输入接口表中出错记录的 group_id
        X_Transaction_date date :=  to_date(‘2007-4-29′,’YYYY-MM-DD’)   ;– 输入一个打开会计期内的日期：比如 to_date(‘2007-4-29′,’YYYY-MM-DD’);
 begin
 
 –注意，这里的user_id,resp_id,resp_appl_id 在不同的环境可能不同。
 fnd_global.APPS_INITIALIZE(user_id => 1111,
                            resp_id => 50260,
                            resp_appl_id => 660);
                           
 UPDATE rcv_transactions_interface
   SET processing_status_code  = ‘PENDING’,
       transaction_status_code = ‘PENDING’,
       processing_request_id = NULL,
       transaction_date = X_Transaction_date,
       expected_receipt_date = X_Transaction_date      
 WHERE group_id = X_Group_Id;                          
                           
                           
  l_request_id := fnd_request.submit_request(‘PO’,
                                               ‘RVCTP’,
                                               ”,
                                               to_char(sysdate+0.0003,
                                                       ‘YYYY/MM/DD HH24:MI:SS’),
                                               false,
                                               ‘IMMEDIATE’,
                                               X_Group_Id,
                                               chr(0)); 
  dbms_output.put_line(to_char(l_request_id));
 end ;
 
 –得到的request_id
 –查request的运行结果
 SELECT request_id,
        last_update_date,
        requested_start_date,
        phase_code,
        status_code,
        argument_text
   FROM fnd_concurrent_requests
  WHERE request_id = &p_request_id
 
 –如果有错误，查错误日志
SELECT to_char(TIMESTAMP, ‘DD-MON-YY HH24:MI:SS’),
       SUBSTR(message_text, 1, 300),
       SUBSTR(module, 1, 30),
       log_sequence
  FROM fnd_log_messages
 WHERE trunc(TIMESTAMP) = trunc(SYSDATE)
   AND process_id = (SELECT os_process_id
                       FROM fnd_concurrent_requests
                      WHERE request_id = &p_request_id)
   AND module NOT LIKE ‘fnd%’
 ORDER BY log_sequence;
 
 —
/* 146 08-5月 -07 21:40:24 ”          ORA-01405: 读取的列值为 NULL
” po.src.rvtp.rvtii.RVTIICREATE. 94948
147 08-5月 -07 21:40:24 ”          RVTII:076 OE COGS API returned Error 销货成本帐户生成失败，并出现以下错误：ORA-20002: 3135: ‘在同步进程中，不允许出现 ‘Wf_Item_Activity_Status.Set_Error(error_process)’。 ‘

” po.src.rvtp.rvtii.RVTIICREATE. 94950
148 08-5月 -07 21:40:24 ”          ORA-01405: 读取的列值为 NULL
” po.src.rvtp.rvtii.RVTIICREATE. 94952*/

–可见是COGS 帐户生成失败
–可能是某个COGS要取的某个帐户没有设置
— 20070508 21:54 接口中一些错误的接收，被李进手工接收了，订单行已经关闭
— 备份
INSERT INTO rcv_trx_interface_bak
  (SELECT *
     FROM rcv_transactions_interface
    WHERE INTERFACE_TRANSACTION_ID IN
          (SELECT INTERFACE_TRANSACTION_ID
             FROM rcv_transactions_interface rti, oe_order_lines_all oel
            WHERE rti.oe_order_line_id = oel.line_id
              AND rti.oe_order_header_id = oel.header_id
              AND oel.flow_status_code = ‘CLOSED’
              AND rti.transaction_status_code = ‘ERROR’
              AND rti.source_document_code = ‘RMA’));
–7行被插入
–删除
DELETE FROM rcv_transactions_interface
 WHERE INTERFACE_TRANSACTION_ID IN
       (SELECT INTERFACE_TRANSACTION_ID
          FROM rcv_transactions_interface rti, oe_order_lines_all oel       
         WHERE rti.oe_order_line_id = oel.line_id
           AND rti.oe_order_header_id = oel.header_id
           AND oel.flow_status_code = ‘CLOSED’
           AND rti.transaction_status_code = ‘ERROR’
           and rti.source_document_code = ‘RMA’);
–7行被删除