My Oracle Support Banner

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

Last updated on FEBRUARY 13, 2020

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

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

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
Cause
Solution
 Standard Patching Process
 Patch Wizard
 Have more questions?
References


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