AP encountered Error while creating accounting for AP Invoice
Symptom
AP PQ encountered error while creating accounting for AP Invoice.
Error message:app_sqlap-10000:ora-00001:unique constraint (AP. AP_AE_HEADERS_U2)
Ag0516_01†"attached error message
cause
1. Due to Bug 3144056-apacceng fails with Ora-00001:unique Constraint (AP). AP_AE_HEADERS_U2) violated
The system created accounting for a event but left the event in created status. Therefore the next time the accounting process tries to account the event it again tries to create a new header and Receiv Es the unique key violation error.
2. Bug 4361701 Prepayment Application EVENT ENDS in FATAL ERROR and rounding line:
Bug 4657723 (Notoginseng) Appaceng fails with FATAL ERROR DUE to ROUND 0.01 for prepay
Bug 4361701 has been fixed in APPPYCRB.PLS version 115.64
Bug 4657723 has been fixed in APPPYCRB.PLS version 115.66
Customer has file Apppycrb.pls and version 115.62 which is lower than this fixed versions.
Solutions
1. [25-jul-07] AP PQ reported that they encountered the error while doing invoice validation.
2. [25-jul-07] Vilma of AP PQ do invoice validation into test server and the result is successful
3. [25-jul-07] in Erpapp, Vilma tried different PO as reference and the same error occurred
4. [25-jul-07] in Erpapp, Vilma tried Invoice to Validation for CK and the
5. [25-jul-07] Metalink suggest the solution. Ag0516_02†"attached Oracle metalink Document ID note:309045.1
1. [27-jul-07] We have already encountered this problem previously (refer to Ag0168-ck encountered problem in P Ayment and invoice encoding); We submitted an SR and got feedback from Oracle. (Refer to ag0516_03)
2. [27-jul-07] In test server there is no error, so need to clone production server to test server
3. [28-jul-07] Ren do weekly cloning to have a latest data on the TEST server.
4. [30-jul-07] According to AG0516_03, Lucy needs to apply the solution on TEST server I, before applying it to PROD server.
· 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 #: 107671
§undo the Events now:y
§enter N to Sweep to currently Open Period
§enter N to Don't delete events IDs (you'll only get this prompt on 11i. Ap. K and above)
§commit; <<< script does not self-commit
· Run the request Payables Accounting Process
5. [31-jul-07] Lucy used the following SQL statement and found out there were two 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 '
6. [31-jul-07] After successfully running the solution from TEST Server, Lucy applied the solution on production Server.
· 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 #: 107671 & 108410
§undo the Events now:y
§enter N to Sweep to currently Open Period
§enter N to Don't delete events IDs (you'll only get this prompt on 11i. Ap. K and above)
§commit; <<< script does not self-commit
· Run the request Payables Accounting Process
AG0516_05---Output of payables Accounting Process
7. [31-jul-07] Divine said they can process other AP invoice.
8. [31-JUL-07] The error message has been gone but the invoice PO7840 the still is cannot. There is a error message on Payables Accountin G process“ Fatal Error in Payables Accounting
9. [31-jul-07] Lucy checked Metalink and found out a document for this problem (Oracle document note 422734.1). This is Oracle bug, we needed to apply Patch 5930565 (refer to ag0516_06)
[01-aug-07] Ren applied Patch 5930565 on TEST server.
[01-aug-07] Lucy try to run payable Accounting Process and is successful
[05-aug-07] Ren applied Patch 5930565 on PROD server.