R12: AP: Create Accounting Performance - Table Access Full on AP_CHECKS_ALL table (Doc ID 2446780.1)

Last updated on JULY 01, 2022

Applies to:

Symptoms

On : 12.1.3 version, Performance

ACTUAL BEHAVIOR
---------------

Create Accounting Running long for more than 2 days

See that the below SQL ID 750bp5tdpyx07 is doing a TABLE ACCESS FULL AP_CHECKS_ALL:

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 , L2.LINE_NUMBER , L2.AID_DESCRIPTION SOURCE_1 , L2.RECP_INV_DIST_DESC SOURCE_14 ,
L2.AID_DIST_CCID SOURCE_35 , L2.CGAC_GAIN_CCID SOURCE_42 , L2.CGAC_LOSS_CCID SOURCE_44 , L2.AWT_RELATED_DIST_ACCOUNT SOURCE_55 , L2.APAD_DIST_LOOKUP_CODE SOURCE_56 ,
L6.DEF_REC_SETTLEMENT_OPTION_CODE SOURCE_62 , FVL62.MEANING SOURCE_62_MEANING , L6.TAX_ACCOUNT_CCID SOURCE_63 , L2.PREPAY_INV_DIST_CCID SOURCE_65 , L2.DIST_ACCOUNT_REVERSAL_OPTION SOURCE_67 ,
L2.DISTRIBUTION_LINK_TYPE SOURCE_69 , L2.UPG_ENC_CR_CCID SOURCE_78 , L2.UPG_ENC_CR_AMT SOURCE_79 , L2.UPG_ENC_CR_BASE_AMT SOURCE_81 , L2.UPG_ENC_DR_CCID SOURCE_82 , L2.UPG_ENC_DR_AMT SOURCE_83 ,
L2.UPG_ENC_DR_BASE_AMT SOURCE_84 , L2.UPG_AP_ENCUM_OPTION SOURCE_85 , L2.DEFERRED_END_DATE SOURCE_90 , L2.DEFERRED_OPTION SOURCE_91 , L2.DEFERRED_START_DATE SOURCE_92 ,
L2.OVERRIDE_ACCTD_AMT_FLAG SOURCE_93 , FVL93.MEANING SOURCE_93_MEANING , L2.UPG_CR_ENC_TYPE_ID SOURCE_102 , L2.UPG_DR_ENC_TYPE_ID SOURCE_103 , L2.BUS_FLOW_AP_APP_ID SOURCE_107 ,
L2.BUS_FLOW_INV_DIST_TYPE SOURCE_108 , L2.BUS_FLOW_INV_ENTITY_CODE SOURCE_109 , L2.AID_ENCUMBERED_FLAG SOURCE_114 , FVL114.MEANING SOURCE_114_MEANING , L3.DEF_REC_PP_SET_OPTION_CODE SOURCE_147 ,
FVL147.MEANING SOURCE_147_MEANING , L2.BF_CASHALWAYS_PP_MAT_DATE SOURCE_148 , L2.APAD_DISTRIBUTION_IDENTIFIER SOURCE_150 , L2.BF_CASH_ALWAYS_PP_DIST_TYPE SOURCE_151 ,
L2.BF_CASH_ALWAYS_PREPAY_ENTITY SOURCE_152 , L2.BF_CASHCLEAR_PP_CLEAR_DIST_ID SOURCE_153 , L2.BF_CASHALWAYS_PP_PMT_ID SOURCE_154 , L2.UPG_ENC_CR_ACCT_CLASS SOURCE_155 ,
L2.UPG_ENC_DR_ACCT_CLASS SOURCE_156 , L2.APAD_AMOUNT SOURCE_157 , L2.APAD_BASE_AMOUNT SOURCE_158 , L2.REVERSED_PREPAY_APP_DIST_ID SOURCE_159 , L5.AID_TAX_LINE_ID SOURCE_160 ,
L5.AID_REC_NREC_TAX_DIST_ID SOURCE_161 , L5.AID_SUMMARY_TAX_LINE_ID SOURCE_162 , L2.BF_CASHALWAYS_PP_MAT_DIST_ID SOURCE_163 , L2.BF_CASHALWAYS_PP_PMT_DIST_ID SOURCE_164 ,
L2.BASE_AMT_AT_PP_XRATE SOURCE_165 , L2.BF_PREPAY_REC_DIST_ID SOURCE_167 , L2.BF_PREPAY_REC_INV SOURCE_168 , L2.BF_ACCRUAL_PREPAY_DIST_TYPE SOURCE_170 , L2.BF_ACCRUAL_PREPAY_ENTITY_CODE SOURCE_171 ,
L2.BF_ACCRUAL_PP_DIST_ID SOURCE_172 , L2.BF_ACCRUAL_PP_INV SOURCE_173 , L2.APAD_BASE_AMOUNT_DIFF SOURCE_182 , L2.INV_PREPMT_GAIN_LOSS_INDICATOR SOURCE_188 FROM XLA_EVENTS_GT XET ,
AP_PREPAYAPP_EXTRACT_DETAILS_V L2 , AP_PREPAY_DEF_TAX_EXTRACT_V L3 , AP_ZX_DEF_TAX_EXTRACT_V L5 , ZX_AP_DEF_TAX_EXTRACT_V L6 , FND_LOOKUP_VALUES FVL62 , FND_LOOKUP_VALUES FVL93 ,
FND_LOOKUP_VALUES FVL114 ,
FND_LOOKUP_VALUES FVL147 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 L2.EVENT_ID = XET.EVENT_ID
AND L2.PP_DETAIL_TAX_DIST_ID = L3.REC_NREC_PP_TAX_DIST_ID (+) AND L2.AID_DETAIL_TAX_DIST_ID = L5.AID_REC_NREC_TAX_DIST_ID (+) AND L2.RECP_DETAIL_TAX_DIST_ID = L6.REC_NREC_TAX_DIST_ID (+)
AND FVL62.LOOKUP_TYPE(+) = 'ZX_REC_SETTLEMENT_OPTION' AND FVL62.LOOKUP_CODE(+) = L6.DEF_REC_SETTLEMENT_OPTION_CODE AND FVL62.VIEW_APPLICATION_ID(+) = 0 AND FVL62.LANGUAGE(+) = USERENV('LANG')
AND FVL93.LOOKUP_TYPE(+) = 'YES_NO' AND FVL93.LOOKUP_CODE(+) = L2.OVERRIDE_ACCTD_AMT_FLAG AND FVL93.VIEW_APPLICATION_ID(+) = 0 AND FVL93.LANGUAGE(+) = USERENV('LANG') AND FVL114.LOOKUP_TYPE(+)
= 'YES_NO' AND FVL114.LOOKUP_CODE(+) = L2.AID_ENCUMBERED_FLAG AND FVL114.VIEW_APPLICATION_ID(+) = 0 AND FVL114.LANGUAGE(+) = USERENV('LANG') AND FVL147.LOOKUP_TYPE(+)
= 'ZX_REC_SETTLEMENT_OPTION' AND FVL147.LOOKUP_CODE(+) = L3.DEF_REC_PP_SET_OPTION_CODE AND FVL147.VIEW_APPLICATION_ID(+) = 0 AND FVL147.LANGUAGE(+) = USERENV('LANG')

EXPECTED BEHAVIOR
-----------------------
Create Accounting should finished faster

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Submit the Create Accounting program


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot close the period

Cause

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