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 to AP Invoice [Intangible assets-1], the error is App-sqlap-10710:onli NE accounting could not is created. Please submit the Payables Accounting Process instead to create Accounting entries for this transaction. (Refer to AG0516 A G0526)

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 is 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

· 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 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

6. [08-dec-08] End of the request Payables Accounting Process, Kevin encountered other error:ora-01403:no data found OCC Urred 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 and our symptom. (refer to ag1043_05)

8. [08-dec-08] Kevin asked Ren to the 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-dec-08] PQ accounting confirmed that problem is OK in erpdb. (Refer to Ag1043_06)

[11-dec-08] Jack applied Patch 5253656 in Erpapp successfully. (Refer to ag1043_07)

[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)

[12-dec-08] for invoice [po#14421] which can ' t being accounted, because it ' held and the cause is ' insufficient ex Ist for this invoice. Kevin asked them to add budget and then try to create accounting again.

[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 are a tax line without a associated event ID. This indicates a data corruption it most likely caused by following bug::
Bug 5330439-unaccounted Tax distribution after prepayment unapplication

[16-dec-08] Patch 5474676-1OFF:5330439:FP. g:11.5.10.2:unaccounted Tax Distribution after prepayment

This is patch includes the fix for Bug 5330439

[19-dec-08] After applied Patch 5474676 in erpdb, the problem still.

[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
 and  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  and  is NULL

[22-dec-08] Kevin applied the solution in erpdb and asked user to check them. (Refer to Ag1043_11)

[06-jan-09] Kevin applied the solution in erpdb and asked user to check them again. (Refer to Ag1043_12)

[06-jan-09] Kevin applied the solution in Erpapp and PQ accounting confirmed, that's problem was normal. (Refer to Ag1043_13)