R12: Performance Issue: Uninvoiced Receipt Report Is Running For A Long Time (Doc ID 736109.1)

Last updated on MAY 18, 2017

Applies to:

Oracle Cost Management - Version 12.0.0 to 12.0.6 [Release 12]
Oracle Cost Management - Version 12.2 to 12.2.4 [Release 12.2]
Information in this document applies to any platform.
CSTACREP


Symptoms

After upgrade to Release 12, The report Uninvoiced Receipts Report is running for very long time.
In Previous releases, It takes maximum 1 minute whereas now it takes hours to complete.

Slow performing SQL:

SELECT POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, POLL.LINE_LOCATION_ID
LINE_LOCATION_ID, DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, 0) SERVICE_FLAG,
DECODE (POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED -
NVL(POD.AMOUNT_CANCELLED, 0), POD.QUANTITY_ORDERED -
.....
FROM
PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL,
PO_HEADERS_ALL POH, PO_VENDORS POV, MTL_CATEGORIES_KFV MCA,
MTL_DEFAULT_SETS_VIEW MDS
WHERE POL.PO_HEADER_ID = POH.PO_HEADER_ID AND
....
AND EXISTS (SELECT 'Get a receipt/match
for this shipment' FROM RCV_TRANSACTIONS RVT WHERE RVT.PO_LINE_LOCATION_ID
= POLL.LINE_LOCATION_ID AND RVT.TRANSACTION_TYPE IN ('RECEIVE','MATCH') AND
RVT.TRANSACTION_DATE <= :B3 )

 

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