Oracle EBS: AR關帳問題 (Credit Memo Incomplete)

狀況: user關帳執行Subledger Period Close Exception Report, 在Receivable有幾筆Credit Memo的Status是Incomplete.

嘗試單純的方式, 以Examine在Form上直接把complete_flag改為N, 存檔時出現APP_AR_11017 error , 失敗.

搜尋一些文章, 有個del_orphans_xla_120.sql可用, 但Oracle support上可能已把這檔案移掉, 找不到了.

最後處理方式:
1.以SQL找出orphan record, 比對與出問題的那幾筆相符
2.針對其中一筆以diagnostics功能查看資訊, XLA_EVENTS出現兩列資料, 其中一列的event_status_code和process_status_code都是P (10/4建立), 另一列則分別是I和U (10/15建立)
3.為避免刪資料會有問題, 直接把event_status_code和process_status_code都改為P
4.重跑Subledger Period Close Exception Report, 已無異常資料

不知是否會有其他影響, 後續再觀察.


SQL: Orphan records

select xte.*, --xte.source_id_int_1 source_id,

       users.user_name user_name
  from xla_events xe,
       xla.xla_transaction_entities xte,
       fnd_user users
 where xe.created_by = users.user_id
   and xte.entity_code = 'TRANSACTIONS'
   AND xte.upg_batch_id is NULL
   and xte.entity_id = xe.entity_id
   and xte.application_id = 222
   and xe.application_id = 222
   and xe.event_status_code not in ('P','Z')
   and not exists
     (select 'x'
        from ra_cust_trx_line_gl_dist_all dist
       where dist.customer_trx_id = xte.source_id_int_1
         and dist.posting_control_id = -3
         and ((dist.event_id is null
               and not exists (select 'x'
                                 from ra_cust_trx_line_gl_dist_all dist2
                                where dist2.customer_trx_id = dist.customer_trx_id
                                  and dist2.account_set_flag = 'N'
                                  and dist2.latest_rec_flag = 'Y'
                                  and dist2.account_class = 'REC'))
             OR
             dist.event_id = xe.event_id)
      union
      select 'x'
        from ar_receivable_applications_all
       where customer_trx_id = xte.source_id_int_1
         and posting_control_id = -3
         and event_id = xe.event_id
      union
      select 'x'
        from ar_receivable_applications_all
       where applied_customer_trx_id = xte.source_id_int_1
         and posting_control_id = -3
         and event_id = xe.event_id
     )
 

Ref:
1.Orphan Records In XLA_TRANSACTION_ENTITIES table (Doc ID 1066982.1)
2.How to Find Orphan Records in Subledger Accounting Related to Oracle Receivables? (Doc ID 549020.1)