Performance Issue while Querying Accounting Transactions in OLFM (Doc ID 2315063.1)

Last updated on OCTOBER 06, 2017

Applies to:

Symptoms

On : 12.2.4 version, Accounting Transactions

ACTUAL BEHAVIOR
---------------
FInd that there is performance issue while querying accounting transactions in OLFM using Contract Number. It takes 2 minutes or more.

Bad performing SQL is the following:

SELECT * FROM (SELECT
   TRX.ID ID,
   TRX.TRANS_ID TRANS_ID,
   TRX.TRANSACTION_NUMBER TRANSACTION_NUMBER,
   TRX.CONTRACT_NUMBER CONTRACT_NUMBER,
   TRX.TRANSACTION_TYPE TRANSACTION_TYPE,
   TRX.TRANSACTION_STATUS TRANSACTION_STATUS,
   TRX.TRANSACTION_DATE TRANSACTION_DATE,
   TRX.AMOUNT AMOUNT,
   TRX.CURRENCY_CODE CURRENCY_CODE,
   TRX.SOURCE_CODE SOURCE_CODE,
   TRX.SOURCE SOURCE,
   TRX.DESCRIPTION DESCRIPTION,
   TRX.KHR_ID CHR_ID,
   TRX.ACTUAL_DRAFT_STATUS ACTUAL_DRAFT_STATUS,
   TRX.ACTUAL_DRAFT_STATUS_CODE,
   TRX.TOTAL_TRANSACTION_AMOUNT TOTAL_TRANSACTION_AMOUNT,
   TRX.CHECK_ID CHECK_ID,
   ORG_ID,
   hr.NAME organization_name,
   'N' SelectedYn,
   TRX.REPRESENTATION_CODE REPRESENTATION_CODE,
   TRX.VENDOR_ID,
   TRX.ACCOUNT_NUMBER,
   TRX.CONLS_INV_ID,
   TRX.BOOK_TYPE_CODE,
   TRX.PERIOD_COUNTER,
   TRX.DEPRN_RUN_ID,
   TRX.TRY_ID,
   TRX.INVOICE_YN
FROM
   OKL_TRX_HEADER_UV TRX,
   HR_OPERATING_UNITS hr
WHERE
   hr.ORGANIZATION_ID = ORG_ID) QRSLT  WHERE (CHR_ID = :1 AND  REPRESENTATION_CODE = :2)

EXPECTED BEHAVIOR
-----------------------
Expect no performance issues.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to Accounting transactions screen
2. Enter a contract number and Go

Changes

Cause