Encountered Error while creating accounting for AP Invoice

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 an 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 receives the unique key violation error.

2.    Bug 4361701  PREPAYMENT APPLICATION EVENT ENDS IN FATAL ERROR WITH .01 ROUNDING LINE:

Bug 4657723  (37) APPACENG FAILS WITH FATAL ERROR DUE TO ROUND 0.01 FOR PREPAY APPLIC

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 error while doing invoice validation.

2.          [25-Jul-07] Vilma of AP PQ do invoice validation in 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 Validation for CK and the result is successful

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 Payment and invoice encoding ); we submitted an SR and got feedback from Oracle. (Refer to AG0516_03 )

2.          [27-Jul-07] In test server there was 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 first, 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 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

5.          [31-Jul-07] Lucy used the following sql statement and found out there were two records 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 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

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 still cannot be accounted. There is an error message on Payables Accountin g Process “ Fatal Error In Payables Accounting Process”

9.          [31-Jul-07] Lucy checked Metalink and found out a document for this problem (Oracle Document Note 422734.1). This was Oracle bug, we needed to apply Patch 5930565 (Refer to AG0516_06 )

10.     [01-Aug-07] Ren applied Patch 5930565 on TEST server.

11.     [01-Aug-07] Lucy try to run Payable Accounting Process and is successful

12.     [05-Aug-07] Ren applied Patch 5930565 on PROD server. AG0516_07 – attached screenshots

13.     [05-Aug-07] Waiting for User to confirm if it successfully solve the problem in PROD server

14.     [22-Oct-07]   The problem has been solved in PROD server successfully.