Friday, September 26, 2014

Oracle Payables Accounting Process(APACCENG) program does not account cancelled Invoice

Payables Accounting Process(APACCENG) and Unaccounted Transactions Report (APXTRSWP)


This is bug found in Oracle Applications 11i version.

More details of this bug mentioned on document (BUG 7629187 - 11I APACCENG DOES NOT ACCOUNT FOR INVOICE CANCELLATION ) and they have provided patch also to fix this.

More Technical Details:

Generally accounting_date is truncated while populating line into ap_invoice_distributions_all table.But for invoice cancellation, the accounting_date is not truncating. The same accounting_date will get populated to ap_accounting_events_all as well.


For Example:
Suppose you are cancelling one invoice on 31-Mar-2013 at 11:22:28 AM, then accouting_date of cancelled invoice distribution line will populate as 31-May-2013 11:22:28 not like truncated date i.e. 31-May-2013 00:00:00 happening in other cases.

Because of this if you are running Payables Accounting Process program or Unaccounted Transactions Report with date from/to paramters like 1-MAY-2013 as from date and 31-May-2013 as end date , this cancelled invoice will not get selected for accounting or reporting.


Workaround Solution:

Payables Accounting Process program can submit with next day of cancelled invoice to get selected for accounting. In this case 1-Jun-2013.

Unaccounted Transactions Report, you can use same above solution or instead of from/to date , you can pass period name to get selected for reprting. In this case, MAY-13.


Permanant Fix:
You can check with oracle for appropriate patch to fix this issue.


We have face this problem in our Oracle apps 11i env, not sure this issue is there or not in R12 env.


More Technical Analysis:

Opened code of program 'Payables Accounting Process’. Through this report at one place they are calling package AP_Accounting_Engine_Pkg.Do_Accounting. In this package, below sql is failing for cancelled invoice accounting-

UPDATE AP_ACCOUNTING_EVENTS AAE
SET request_id = P_request_id
WHERE AAE.source_table = 'AP_INVOICES'
AND AAE.event_status_code = 'CREATED'
AND (AAE.accounting_date >= G_start_date OR G_start_date IS NULL)
AND (AAE.accounting_date <= G_end_date OR G_end_date IS NULL);

For example- for given invoice# 34202382 , accounting date in unaccounted distribution line is '31-may-2013 11:22:28' and end date passed by Payables Accounting Process was'31-may-2013 00:00:00'' . As this condition got failed, invoice did not pick for accounting.

It means if we created invoice 30-MAY-2013 and then cancelled it on 31-MAY-2013. Then ran Payables Accounting Process to create accounting for cancelled invoice with end date parameter 31-MAY-2013. Program will not pick this invoice for accounting. But if run with end date next day of day when cancelled that is 1-JUN-2013, it will get picked.  




1 comment:

  1. Hi Sagar, Good Day. I've tried to set the day to the next day but it won't account.
    the GL_Date and the Accounting date is the same (03-MAY-2015) so i've set the date to 04-MAY-2015 when i run Payables Accounting Process. I've also checked the data backend and its truncated.

    any advise besides a direct data fix?

    Thank you!

    ReplyDelete