AR关闭期间遇到问题的解决办法

AR关闭期间遇到问题的解决办法

 

仅针对11.5.10

 

1.       使用SQL检查是否有记录;

 SELECT gl_date, gl_posted_date, posting_control_id, 'TRADE' CATEGORY
  FROM ar_cash_receipt_history_all
 WHERE org_id = ‘&org_id’
   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT gl_date, gl_posted_date, posting_control_id, 'MISC' CATEGORY
  FROM ar_misc_cash_distributions_all
 WHERE set_of_books_id =‘&sob’
   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT gl_date, gl_posted_date, posting_control_id, 'CUSTOMER' CATEGORY
  FROM ra_cust_trx_line_gl_dist_all
 WHERE set_of_books_id =‘&sob’
   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT gl_date, gl_posted_date, posting_control_id, 'CMAPP' CATEGORY
  FROM ar_receivable_applications_all
 WHERE set_of_books_id =‘&sob’
   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT gl_date, gl_posted_date, posting_control_id, 'ADJ' CATEGORY
  FROM ar_adjustments_all
 WHERE set_of_books_id =‘&sob’
   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT gl_date, gl_posted_date, posting_control_id, 'BILL' CATEGORY
  FROM ar_transaction_history_all
 WHERE org_id = ‘&org_id’

   AND TO_CHAR (gl_date, 'YYYY-mm') = '&gl_date'
   AND posting_control_id < 0
UNION ALL
SELECT NULL gl_date, trx_date, posting_control_id, complete_flag CATEGORY
  FROM ra_customer_trx_all
 WHERE set_of_books_id =‘&sob’
   AND complete_flag <> 'Y'
   AND TO_CHAR (trx_date, 'YYYY-mm') = '&gl_date'

2.       如果上步有返回值,则执行Transfer AR to GL;

2.1首先在ar_posting_control中生成posting_control_id,

2.2另把事务添加到gl_interface中,如果成功,则更新上面表中的gl_posted_date, posting_control_id,并同时更时gl_interface中的reference_21及group_id的值对应到上面的posting_control_id

3.       导入日记帐;

 如果指定specific gourd_id时,此时的值就是reference_21及group_id的值;

 

分析问题

=================================

如果数据已成功导入到GL_INTERFACE后,且并未导入日记帐,此时万一数据被删除了,此时如何处理?

 

本案仅以RA_CUST_TRX_LINE_GL_DIST_ALL为例,如果重新把数据导入到GL_INTERFACE

 

第一步: 模拟接口表数据被误删除;

   delete from gl.gl_interface where group_id=235081 

 

第二步: 更新相关表中的标记位;

   update ra_cust_trx_line_gl_dist_all

set gl_posted_date=null,posting_control_id = -3

where posting_control_id=235081

注:我们是11.5.10,在更新时会触发一个trriger: ra_cust_trx_line_gl_dist_bri,把下面经红色部分注释掉即可, 待update完成后再把注释去掉;

 

CREATE OR REPLACE TRIGGER ra_cust_trx_line_gl_dist_bri
BEFORE INSERT OR DELETE OR UPDATE ON ra_cust_trx_line_gl_dist_all
referencing new as new
            old as old
FOR EACH ROW

….

….

  IF NVL(l_conc_name,'NONE') not in ( 'ARPURGE' , 'ARARCPUR', 'ARGLTP' )
   THEN
     IF (DELETING) OR
        ((UPDATING) AND
         ((nvl(:new.amount,0.000000000001)
                                <> nvl(:old.amount,0.000000000001)) OR
         (nvl(:new.acctd_amount,0.000000000001)
                          <> nvl(:old.acctd_amount,0.000000000001)) OR
         (nvl(:new.gl_date,to_date('31-12-4172','DD-MM-RRRR'))
                    <>  nvl(:old.gl_date,to_date('31-12-4172','DD-MM-RRRR'))) OR
         (nvl(:new.code_combination_id,0.000000000001)
                          <>  nvl(:old.code_combination_id,0.000000000001))
--        OR (nvl(:new.posting_control_id,0.000000000001) <> nvl(:old.posting_control_id,0.000000000001))
        )
        )
      THEN

…

…

第三步: 重新提交AR transfer to GL;

  successful

第四步: 检查接口表数据是否符合;

 successful

第五步: 导入日记帐;

 successful