PAXACCPB PRC: Create Accounting Performance Issue (Doc ID 1616284.1)

Last updated on FEBRUARY 13, 2024

Applies to:

Symptoms

PAXACCPB module: PRC: Create Accounting

Performance issues.


4 hrs or more during regular workdays. During closing it took 7 hrs. This
impacts monthend/quarter closing not to complete on time.


Problem SQL:

SELECT /*+ leading(xet) cardinality(xet,1) */ XET.ENTITY_ID ,
 XET.LEGAL_ENTITY_ID ,XET.ENTITY_CODE ,XET.TRANSACTION_NUMBER ,XET.EVENT_ID ,
 XET.EVENT_CLASS_CODE ,XET.EVENT_TYPE_CODE ,XET.EVENT_NUMBER ,XET.EVENT_DATE
 ,XET.TRANSACTION_DATE ,XET.REFERENCE_NUM_1 ,XET.REFERENCE_NUM_2 ,
 XET.REFERENCE_NUM_3 ,XET.REFERENCE_NUM_4 ,XET.REFERENCE_CHAR_1 ,
 XET.REFERENCE_CHAR_2 ,XET.REFERENCE_CHAR_3 ,XET.REFERENCE_CHAR_4 ,
 XET.REFERENCE_DATE_1 ,XET.REFERENCE_DATE_2 ,XET.REFERENCE_DATE_3 ,
 XET.REFERENCE_DATE_4 ,XET.EVENT_CREATED_BY ,XET.BUDGETARY_CONTROL_FLAG ,
 L1.LINE_NUMBER , L1.COST_CCID SOURCE_3 , L1.ALLOW_OVERRIDE_CCID_FLAG
 SOURCE_4 , L1.ADJ_COST_CCID SOURCE_5 , L1.COST_CLEARING_CCID SOURCE_6 ,
 L1.ADJ_COST_CLEARING_CCID SOURCE_7 , L1.REVERSING_LINE_FLAG SOURCE_22 ,
 L1.ACTUAL_UPG_CR_ACCT_CLASS SOURCE_23 , L1.ENTERED_RAW_COST SOURCE_24 ,
 L1.ENTERED_CURRENCY_CODE SOURCE_25 , L1.ACCT_RAW_COST SOURCE_26 ,
 L1.EXCHANGE_RATE_DATE SOURCE_27 , L1.EXCHANGE_RATE SOURCE_28 ,
 L1.EXCHANGE_RATE_TYPE SOURCE_29 , L1.ACTUAL_UPG_DR_ACCT_CLASS SOURCE_30 ,
 L1.USE_ACT_UPG_ATTRIB_FLAG SOURCE_31 , L1.LINE_NUMBER SOURCE_33 ,
 L1.LINE_TYPE SOURCE_34 , FVL34.MEANING SOURCE_34_MEANING ,
 L1.LINE_NUM_REVERSED SOURCE_35
FROM
XLA_EVENTS_GT XET , PA_XLA_CDL_LINES_V L1 , FND_LOOKUP_VALUES FVL34 WHERE
 XET.EVENT_ID BETWEEN :B5 AND :B4 AND XET.EVENT_DATE BETWEEN :B3 AND :B2 AND
 XET.EVENT_CLASS_CODE = :B1 AND XET.EVENT_STATUS_CODE <> 'N' AND L1.EVENT_ID
 = XET.EVENT_ID AND FVL34.LOOKUP_TYPE(+) = 'COST DISTRIBUTION LINE TYPE' AND
 FVL34.LOOKUP_CODE(+) = L1.LINE_TYPE AND FVL34.VIEW_APPLICATION_ID(+) = 275
 AND FVL34.LANGUAGE(+) = USERENV('LANG')

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.