R12 AP: Performance Problem: Create Accounting On Invoices With Large Numbers Of Distributions (Doc ID 1361579.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms



Performance problem with Create Accounting on large batches. The last run of Create Accounting on a 3 invoice batch with a total of 110000 lines took just under 24 hours to complete.

One of the SQL statements that is taking long time is the following:

SELECT SUM( APHD.PAID_BASE_AMOUNT ) , SUM( APHD.INVOICE_DIST_BASE_AMOUNT ) , SUM( APHD.CLEARED_BASE_AMOUNT )
FROM AP_PAYMENT_HIST_DISTS APHD , AP_PAYMENT_HISTORY_ALL APH WHERE APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT :B1 FROM DUAL UNION SELECT DISTINCT AID_AWT.INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID_AWT , AP_INVOICE_DISTRIBUTIONS_ALL AID_ITEM WHERE 1 = 1
AND AID_ITEM.INVOICE_DISTRIBUTION_ID = :B1 AND AID_ITEM.LINE_TYPE_LOOKUP_CODE <> 'AWT'
AND AID_AWT.INVOICE_ID = AID_ITEM.INVOICE_ID AND AID_AWT.AWT_RELATED_ID = AID_ITEM.INVOICE_DISTRIBUTION_ID
AND AID_AWT.LINE_TYPE_LOOKUP_CODE = 'AWT' ) AND APHD.PAY_DIST_LOOKUP_CODE IN( 'CASH', 'DISCOUNT', 'AWT' )
AND NVL( APH.POSTED_FLAG, 'N' ) IN( 'Y', 'S' ) AND APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APH.TRANSACTION_TYPE IN( 'PAYMENT CREATED', 'PAYMENT CANCELLED' , 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED', 'REFUND ADJUSTED',
'REFUND CANCELLED', 'MANUAL REFUND ADJUSTED' ) AND NOT EXISTS (SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL WHERE APH_REL.CHECK_ID = APH.CHECK_ID
AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID ) =
NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID ) AND APH_REL.REV_PMT_HIST_ID IS NOT NULL ) ;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms