My Oracle Support Banner

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

Last updated on JANUARY 16, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
 Standard Patching Process
 Patch Wizard
 Have more questions?
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.