Create Accounting Performance Issue: Create Accounting Process Performs Poorly (Doc ID 1572285.1)

Last updated on JULY 24, 2017

Applies to:

Oracle Receivables - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms

While attempting to run Create Accounting, the program takes a huge amount of time. 

The following SQL was identified to be causing the issue. 

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 , L3.LINE_NUMBER , L10.TRX_TYPE_NAME SOURCE_3 , L10.TRX_NUMBER SOURCE_4 , L10.TRX_DOC_SEQUENCE_VALUE SOURCE_6 , L4.DIST_CODE_COMBINATION_ID SOURCE_24 , L4.DIST_SOURCE_TYPE SOURCE_25 , L4.DIST_GAIN_CCID SOURCE_26 , L4.DIST_MFAR_ADDITIONAL_ENTRY SOURCE_27 , L4.DIST_LOSS_CCID SOURCE_29 , L4.REVERSED_SOURCE_ID SOURCE_39 , L2.TRX_DISTRIBUTION_TYPE SOURCE_45 , L10.TRX_ENTITY_CODE SOURCE_46 , L2.TRX_LINE_DIST_ID SOURCE_47 , L10.TRX_CUSTOMER_TRX_ID SOURCE_48 , L4.DIST_ENT_AMT SOURCE_51 , L7.REC_ACT_TYPE SOURCE_63 , L4.DIST_LINE_ID SOURCE_78 , L4.DISTRIBUTION_TYPE SOURCE_79 , L4.DIST_CURRENCY_CODE SOURCE_80 , L3.DIST_CUR_CONVERSION_DATE SOURCE_81 , L3.DIST_CUR_CONVERSION_RATE SOURCE_82 , L3.DIST_CUR_CONVERSION_TYPE SOURCE_83 , L3.DIST_TO_ACCTD_AMT SOURCE_84 , L3.RECP_OVERRIDE_ACCTD_AMT SOURCE_85 , L3.DIST_ACCTD_AMT SOURCE_86 , L4.DIST_PARTY_ID SOURCE_89 , L4.DIST_PARTY_SITE_ID SOURCE_90 , L3.DIST_TO_CUR_CONVERSION_DATE SOURCE_111 , L4.DIST_ENT_AMT_FROM SOURCE_113 , L4.DIST_CURRENCY_CODE_FROM SOURCE_114 , L4.RCT_GAIN_LOSS_REF SOURCE_115 , L4.DIST_PARTY_TYPE SOURCE_116 , L3.DIST_TO_CUR_CONVERSION_TYPE SOURCE_117 , L3.DIST_TO_CUR_CONVERSION_RATE SOURCE_118 , L4.DIST_SOURCE_TABLE SOURCE_119 FROM XLA_EVENTS_GT XET , AR_CUST_TRX_LINES_L_V L2 , AR_DISTRIBUTIONS_BASE_V L3 , AR_DISTRIBUTIONS_L_V L4 , AR_RECEIVABLES_TRX_ACT_S_V L7 , AR_TRANSACTIONS_S_V L10 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 L3.EVENT_ID = XET.EVENT_ID AND L2.EVENT_ID (+) = L3.EVENT_ID AND L2.LINE_NUMBER (+) = L3.LINE_NUMBER AND L4.EVENT_ID = L3.EVENT_ID AND L4.LINE_NUMBER = L3.LINE_NUMBER AND L7.EVENT_ID (+) = L3.EVENT_ID AND L7.LINE_NUMBER (+) = L3.LINE_NUMBER AND L10.EVENT_ID (+) = L3.EVENT_ID AND L10.LINE_NUMBER (+) = L3.LINE_NUMBER

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms