AP Encountered Error while creating accounting for AP Invoice [intangible assets-1]

Doc ID: AG1043

Subject: PQ AP Encountered Error while creating accounting for AP Invoice [intangible assets-1]

Type: Problem

Creation Date: 08-DEC-08

Last Revision Date: 12-JAN-09

Status: Closed

Owner: Kevin

 

Symptom

PQ AP Encountered Error while creating accounting for AP Invoice [intangible assets-1], the error is APP-SQLAP-10710: Online accounting could not be created. Please submit the Payables Accounting Process instead to create accounting entries for this transaction.（Refer to AG0516 AG0526）

 

 

Cause

 

 

Solutions

1. [04-DEC-08] PQ AP Encountered Error while creating accounting for AP Invoice [intangible assets-1]. (Refer to AG1043_01 AG1043_02)

2. [08-DEC-08] According to the cue, Kevin submitted the request “Payables Accounting Process”, but he encountered a error. (Log file refers to AG1043_03)

3. [08-DEC-08] The error: ORA-00001: unique constraint (AP.AP_AE_HEADERS_U2) violated occurred in

AP_ACCOUNTING_MAIN_PKG.Insert_AE_HeaderAP_PREPAY_UN_EVENT_CORE_PKG.Derive_Accounting_Ent

ry<-AP_PREPAY_UN_EVENT_CORE_PKG.Execute<-AP_ACCOUNTING_MAIN_PKG.Create_Accountin

g_Entry<-AP_ACCOUNTING_ENGINE_PKG.do_accounting<-APXAEREPwith parameters (&PARAMETERS)

4. [08-DEC-08] Refer to AG0516, Kevin used the following sql statement and found out there was one record error.

·         select distinct aea.accounting_event_id
, aea.source_id
, aea.source_table
, aea.event_status_code
, aea.event_type_code
from ap_accounting_events_all aea
, ap_ae_headers_all aeh
where aeh.accounting_event_id = aea.accounting_event_id
and aea.event_status_code = 'CREATED'

5. [08-DEC-08] Kevin applied the solution on TEST server first,

·         Use tool SQL Plus to run undoatgh.sql (Refer to AG0516_04)

·         Run UndoAtgh.sql

§         Enter Org ID: 109

§         Enter option 1 for Display Selected Event Details

§         Enter option 1 for Display AP Accounting Data

§         Enter option 1 for a Single Event

§         Enter Event #: 196983

§         Undo the Events Now: Y

§         Enter N to Sweep to Currently Open Period

§         Enter N to not delete events IDs (You will only get this prompt on 11i.AP.K and above)

§         COMMIT; <<< the script does not self-commit

·         Run the request Payables Accounting Process

 

6. [08-DEC-08] End of the request Payables Accounting Process, Kevin encountered other error: ORA-01403: no data found occurred in P_PREPAY_UN_EVENT_CORE_PKG.Populate_AE_Line_Elements<-AP_PREPAY_UN_EVENT_CORE_PKG.Derive_Accounting_Entry<-AP_PREPAY_UN_EVENT_CORE_PKG.Execute<-AP_ACCOUNTING_MAIN_PKG.Create_Accounting_Entry<-AP_ACCOUNTING_ENGINE_PKG.do_accounting<-APXAEREP With parameters (&PARAMETERS) (Refer to AG1043_04)

7. [08-DEC-08] From Metalink, Note:564973.1 mentions the same symptom with our problem. (Refer to AG1043_05)

8. [08-DEC-08] Kevin asked Ren to help to apply Patch 5253656 in ERPDB (TEST Server).

9. [09-DEC-08] After applied Patch 5253656 in ERPDB, Kevin ran undoatgh.sql and the problem solved.

10. [10-DEC-08] PQ accounting confirmed that the problem was ok in ERPDB. (Refer to AG1043_06)

11. [11-DEC-08] Jack applied Patch 5253656 in ERPAPP successfully. (Refer to AG1043_07)

12. [11-DEC-08] PQ accounting found new problems those invoices [PO#14421] [0498] [532258-1] can’t be accounted. (Refer to AG1043_08 AG1043_09)

13. [12-DEC-08] For invoice [PO#14421] which can’t be accounted, because it’s held and the cause is “Insufficient funds exist for this invoice”. Kevin asked them to add budget and then try to create accounting again.

14. [16-DEC-08] Kevin found a sql script from Metalink to determine the exact cause. (Refer to AG1043_10)         Kevin ran the sql script as follow and found four records:

select invoice_id 
from ap_invoice_distributions_all aid
where aid.line_type_lookup_code = 'TAX'
and aid.accounting_event_id is null
and aid. match_status_flag = 'A'
and aid.posted_flag = 'N' 
and exists (
select 1
from ap_invoice_distributions_all d
where d.line_type_lookup_Code = 'PREPAY'
and d.invoice_id = aid.invoice_id);

Note: If this returns rows then there is a tax line without an associated event id. This indicates a data corruption that is most likely caused by following bug::
Bug 5330439 - Unaccounted Tax Distribution After Prepayment Unapplication

15. [16-DEC-08] Patch 5474676 - 1Off:5330439:FP.G:11.5.10.2: Unaccounted Tax Distribution After Prepayment

This patch includes the fix for Bug 5330439

16. [19-DEC-08] After applied Patch 5474676 in ERPDB, the problem still existed.

17. [22-DEC-08] Kevin found another solution, the sql script as follows:

update ap_invoice_distributions_all aid

set aid.accrual_posted_flag='Y',

   aid.posted_flag='Y'

where aid.invoice_id=110855 

and aid.org_id=109 and aid.last_update_login=6059048

update ap_invoice_distributions_all aid

set aid.accrual_posted_flag='Y',

    aid.posted_flag='Y'

where aid.invoice_id=118685

and aid.last_update_login=6057973 and request_id is null

18. [22-DEC-08] Kevin applied the solution in ERPDB and asked user to check them. (Refer to AG1043_11)

19. [06-JAN-09] Kevin applied the solution in ERPDB and asked user to check them again. (Refer to AG1043_12)

20. [06-JAN-09] PQ accounting confirmed that the problem was normal. 

21. [06-JAN-09] Kevin applied the solution in ERPAPP and PQ accounting confirmed that the problem was normal. (Refer to AG1043_13)