--An error message pops up while closing AP period through "Accounting>Control Payables periods"
----Following error showing up in the log file of the Payables Accounting process,
Starting to account all the events created ...ORA-01403: no data found occurred inAP_ACCOUNTING_MAIN_PKG.Create_Accounting_Entry<-AP_ACCOUNTING_ENGINE_PKG.do_accounting<-APXAEREP with parameters (&PARAMETERS) while performing the following operation:&DEBUG_INFO
You get error in the log file similar to the one attached here.
Reason:
There were some Orphan records in AP_ACCOUNTING_EVENTS_ALL table for which there is no corresponding invoice/payment records in AP_INVOICES_ALL / AP_INVOICE_PAYMENTS_ALL table.
Fix:
Delete the orphan records from the AP_ACCOUNTING_EVENTS_ALL table.
Suggested SQL:
Take the back of these records and then use the below delete statement.
DELETE FROM ap_accounting_events_all e
WHERE source_table = 'AP_INVOICES'
AND NOT EXISTS (SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = e.source_id);
For finding the Orphan records following query can be used,
SELECT b.NAME ou_name
, c.NAME set_of_book_name
, a.source_table
, a.source_id
, a.accounting_date
, a.creation_date
, a.accounting_event_id
FROM ap_accounting_events_all a,
hr_operating_units b,
gl_sets_of_books c
WHERE source_table = 'AP_CHECKS'
AND a.org_id = b.organization_id
AND b.set_of_books_id = c.set_of_books_id
AND NOT EXISTS (SELECT d.check_id
FROM ap_checks_all d
WHERE d.check_id = a.source_id)
UNION all
SELECT b.NAME ou_name
, c.NAME set_of_book_name
, a.source_table
, a.source_id
, a.accounting_date
, a.creation_date
, a.accounting_event_id
FROM ap_accounting_events_all a,
hr_operating_units b,
gl_sets_of_books c
WHERE source_table = 'AP_INVOICES'
AND a.org_id = b.organization_id
AND b.set_of_books_id = c.set_of_books_id
AND NOT EXISTS (SELECT d.invoice_id
FROM ap_invoices_all d
WHERE d.invoice_id = a.source_id);
