Subledger Accounting: Performance Issue with the List of Values (LOV) of the Create Accounting for Third Party Merge (XLAMERGEACCT) (Doc ID 1670073.1)

Last updated on AUGUST 24, 2016

Applies to:

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

Symptoms

Attempting to run the Create Accounting for Third Party Merge (XLAMERGEACCT) and find the List of Values (LOV) for the Event parameter is taking a long time to return results.

It takes about about 5 minutes to get the values and after the selecting a value it takes about 5 minutes again for the next parameter.

The following statement is the one that is slow:

SELECT XE.EVENT_ID,XE.EVENT_ID VALUE, TO_CHAR(XE.EVENT_ID) DESCRIPTION,
NVL('N', 'N'), NVL(TO_NUMBER(NULL), -1), NULL, NVL('Y', 'Y'),
NVL(TO_CHAR(TO_DATE(NULL), 'J'), 0), NVL(TO_CHAR(TO_DATE(NULL), 'J'), 0),
GL.NAME "Ledger Name", XE.EVENT_DATE "Merge Date", XTPV1.THIRD_PARTY_NUMBER

"Original Party Number", XTPSV1.THIRD_PARTY_SITE_CODE "Original Site Code",

XTPV2.THIRD_PARTY_NUMBER "New Party Number", XTPSV2.THIRD_PARTY_SITE_CODE "New Site Code"
FROM
XLA_EVENTS XE, XLA_TRANSACTION_ENTITIES XTE, GL_LEDGERS GL,
XLA_THIRD_PARTIES_V XTPV1, XLA_THIRD_PARTIES_V XTPV2,
XLA_THIRD_PARTY_SITES_V XTPSV1, XLA_THIRD_PARTY_SITES_V XTPSV2 WHERE (
XE.ENTITY_ID = XTE.ENTITY_ID AND XE.APPLICATION_ID = :b1 AND
XE.APPLICATION_ID = XTE.APPLICATION_ID AND XE.EVENT_TYPE_CODE IN
('FULL_MERGE', 'PARTIAL_MERGE') AND XTE.LEDGER_ID = GL.LEDGER_ID AND
XE.PROCESS_STATUS_CODE <> 'F' AND XE.REFERENCE_NUM_1 =
XTPV1.THIRD_PARTY_ID AND XE.REFERENCE_CHAR_1 = XTPV1.THIRD_PARTY_TYPE
AND XE.REFERENCE_NUM_3 = XTPV2.THIRD_PARTY_ID AND XE.REFERENCE_CHAR_1 =
XTPV2.THIRD_PARTY_TYPE AND XE.REFERENCE_NUM_1 = XTPSV1.THIRD_PARTY_ID(+)

AND XE.REFERENCE_NUM_2 = XTPSV1.THIRD_PARTY_SITE_ID(+) AND
XE.REFERENCE_CHAR_1 = XTPSV1.THIRD_PARTY_TYPE(+) AND XE.REFERENCE_NUM_3 =

XTPSV2.THIRD_PARTY_ID (+) AND XE.REFERENCE_NUM_4 =
XTPSV2.THIRD_PARTY_SITE_ID(+) AND XE.REFERENCE_CHAR_1 =
XTPSV2.THIRD_PARTY_TYPE(+)) AND XE.EVENT_ID = :X

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.05 0.05 0 0 0 0
Fetch 5 144.33 601.50 2995067 2972475 0

 

 

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