Performance Issue while Querying Accounting Transactions in OLFM
(Doc ID 2315063.1)
Last updated on MARCH 01, 2022
Applies to:
Oracle Lease and Finance Management - Version 12.2.4 and laterInformation in this document applies to any platform.
Symptoms
On : 12.2.4 version, Accounting Transactions
ACTUAL BEHAVIOR
---------------
Find that there is a 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
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 |
Changes |
Cause |
Solution |
References |