The 'Generate Accruals Master - Streams' Program has a Performance Issue after Applying the SEP 2019 UP
(Doc ID 2699850.1)
Last updated on AUGUST 27, 2021
Applies to:Oracle Lease and Finance Management - Version 12.2 and later
Information in this document applies to any platform.
Find that there is a performance problem when running the "Generate Accruals Master - Streams" program after applying the Sep 2019 Unified Patch. It takes anywhere between 10 to 17 hours.
The worst performing sql is:
SELECT MIN(DUE_DATE) MIN_DUE_DATE, COUNT(CUSTOMER_TRX_ID) TOTAL_OS
( SELECT PS.CUSTOMER_TRX_ID, PS.DUE_DATE DUE_DATE FROM
AR_PAYMENT_SCHEDULES_ALL PS, OKL_CNSLD_AR_STRMS_B ST , OKL_CNSLD_AR_LINES_B
LN , OKL_CNSLD_AR_HDRS_B HD , OKL_STRM_TYPE_B SP , OKC_K_HEADERS_ALL_B CN
WHERE PS.CLASS IN ('INV') AND ST.RECEIVABLES_INVOICE_ID =
PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND SP.ID =
ST.STY_ID AND CN.ID = ST.KHR_ID AND ST.KHR_ID = :B1 AND SP.ACCRUAL_YN NOT
IN('CASH_RECEIPT', 'CASH_WITHOUT_CONTINGENCY') AND STATUS = 'OP' AND
CN.CUST_ACCT_ID = PS.CUSTOMER_ID AND PS.DUE_DATE <= SYSDATE UNION SELECT
RACTRX.CUSTOMER_TRX_ID, APS.DUE_DATE DUE_DATE FROM
RA_CUSTOMER_TRX_LINES_ALL RACTRL, OKL_TXD_AR_LN_DTLS_B TXD ,
RA_CUSTOMER_TRX_ALL RACTRX , OKL_STRM_TYPE_B SM , OKL_TXL_AR_INV_LNS_B TIL ,
AR_PAYMENT_SCHEDULES_ALL APS , OKC_K_HEADERS_ALL_B KHR WHERE
RACTRL.INTERFACE_LINE_ATTRIBUTE14 = (SELECT TO_CHAR(TXD.ID) FROM
OKL_TXD_AR_LN_DTLS_B A WHERE A.ID = TXD.ID) AND RACTRL.CUSTOMER_TRX_ID =
RACTRX.CUSTOMER_TRX_ID AND SM.ID = TXD.STY_ID AND TXD.TIL_ID_DETAILS =
TIL.ID AND RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID AND APS.CLASS IN
('INV') AND TXD.KHR_ID = :B1 AND SM.ACCRUAL_YN NOT IN('CASH_RECEIPT',
'CASH_WITHOUT_CONTINGENCY') AND STATUS = 'OP' AND KHR.ID = TXD.KHR_ID AND
KHR.CUST_ACCT_ID = APS.CUSTOMER_ID AND APS.DUE_DATE <= SYSDATE )
SELECT STY.ID, STYTL.NAME, STM.ID STREAM_ID, STE.ID STREAM_ELEMENT_ID,
OKL_STRM_TYPE_B STY, OKL_STRM_TYPE_TL STYTL, OKL_STREAMS_REP_V STM,
OKL_STRM_ELEMENTS STE, OKL_PROD_STRM_TYPES PSTY, OKL_K_HEADERS KHR WHERE
STM.KHR_ID = :B5 AND KHR.ID = STM.KHR_ID AND STM.ACTIVE_YN = 'Y' AND
STM.SAY_CODE = 'CURR' AND (STM.PURPOSE_CODE IS NULL OR STM.PURPOSE_CODE=
'REPORT') AND STM.STY_ID = STY.ID AND STY.ID = STYTL.ID AND STYTL.LANGUAGE =
USERENV('LANG') AND STY.ACCRUAL_YN = :B4 AND STY.ID = PSTY.STY_ID AND
PSTY.PDT_ID = :B3 AND PSTY.ACCRUAL_YN = 'Y' AND STM.ID = STE.STM_ID AND
TRUNC(STE.STREAM_ELEMENT_DATE) BETWEEN TRUNC(:B2 ) AND TRUNC(:B1 ) AND
STE.AMOUNT <> 0 AND STE.ACCRUED_YN IS NULL
1. Run Generate Accruals Master - Streams wide open.
Applied the Sep 2019 Unified Patch.
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