R12: AP: Prepayment Balance Report Performance Issue (Doc ID 1359340.1)

Last updated on MARCH 20, 2017

Applies to:

Oracle Payables - Version 12.0.0 to 12.1.3 [Release 12.0 to 12.1]
Information in this document applies to any platform.

Symptoms

The AP Prepayment Balance Report  is taking too long to complete.

The below query causing the performance issue:


SELECT NVL(SUM(NVL(XDL.UNROUNDED_ENTERED_DR,0)-NVL(XDL.UNROUNDED_ENTERED_CR,0) ),0)
FROM
AP_INVOICES AI ,AP_INVOICE_DISTRIBUTIONS AID
,AP_INVOICE_DISTRIBUTIONS
AIDINV ,AP_INVOICES AIINV ,XLA_AE_HEADERS XAH
,XLA_AE_LINES XAL ,
XLA_DISTRIBUTION_LINKS XDL WHERE AI.INVOICE_ID=:B2 AND
AI.INVOICE_DATE <=
:B1 AND AID.INVOICE_ID=AI.INVOICE_ID AND
AID.LINE_TYPE_LOOKUP_CODE='ITEM'
AND
AIDINV.PREPAY_DISTRIBUTION_ID=AID.INVOICE_DISTRIBUTION_ID
AND
AIINV.INVOICE_ID=AIDINV.INVOICE_ID AND
XAH.EVENT_TYPE_CODE IN ('PREPAYMENT
APPLIED','PREPAYMENT UNAPPLIED') AND XAH.EVENT_ID=
AIDINV.ACCOUNTING_EVENT_ID AND XDL.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XDL.AE_HEADER_ID AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
XAL.AE_LINE_NUM=XDL.AE_LINE_NUM AND
XAL.ACCOUNTING_CLASS_CODE='LIABILITY'
AND XAH.APPLICATION_ID=200 AND XDL.APPLICATION_ID=200 AND
XAL.APPLICATION_ID=200

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