R12 PAXACCPB PRC: Create Accounting Has Slow Performance With Two Sql Queries (Doc ID 1476682.1)

Last updated on AUGUST 25, 2017

Applies to:

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

Symptoms

After upgrading from 11.5.10.2 with 10G database to 12.1.3 with an 11.2.0.3.0 database the PAXACCPB: PRC: Create Accounting process for Process Category: Labor Cost experiences severe performance problems taking up to 10 hours to complete. The issue is sporadic but does not occur if they enable trace before the process is run.

The problematic queries are:

1. INSERT INTO XLA_AE_LINES ( AE_HEADER_ID , AE_LINE_NUM , DISPLAYED_LINE_NUMBER
  , CODE_COMBINATION_ID , GL_TRANSFER_MODE_CODE , CREATION_DATE ,
  ACCOUNTED_CR , ACCOUNTED_DR , UNROUNDED_ACCOUNTED_CR ,
  UNROUNDED_ACCOUNTED_DR , GAIN_OR_LOSS_FLAG , ACCOUNTING_CLASS_CODE ,
  CURRENCY_CODE , CURRENCY_CONVERSION_DATE , CURRENCY_CONVERSION_RATE ,
  CURRENCY_CONVERSION_TYPE , DESCRIPTION , ENTERED_CR , ENTERED_DR ,
  UNROUNDED_ENTERED_CR , UNROUNDED_ENTERED_DR , LAST_UPDATE_DATE ,
  LAST_UPDATE_LOGIN , PARTY_ID , PARTY_SITE_ID , PARTY_TYPE_CODE ,
  STATISTICAL_AMOUNT , USSGL_TRANSACTION_CODE , CREATED_BY , LAST_UPDATED_BY ,
  JGZZ_RECON_REF , PROGRAM_UPDATE_DATE , PROGRAM_APPLICATION_ID , PROGRAM_ID
  , ANALYTICAL_BALANCE_FLAG , APPLICATION_ID , REQUEST_ID , GL_SL_LINK_TABLE ,
  BUSINESS_CLASS_CODE , MPA_ACCRUAL_ENTRY_FLAG , ENCUMBRANCE_TYPE_ID ,
  LEDGER_ID , ACCOUNTING_DATE , GL_SL_LINK_ID , CONTROL_BALANCE_FLAG )
  (SELECT AE_HEADER_ID ,AE_LINE_NUM ,DECODE(NVL(ACCOUNTED_CR, 0) +
  NVL(ACCOUNTED_DR, 0), 0, -1, 1) * (ROW_NUMBER() OVER (PARTITION BY
  AE_HEADER_ID ORDER BY DECODE(SIGN(ABS(NVL(ACCOUNTED_DR, 0)) -
  ABS(NVL(ACCOUNTED_CR, 0))), 1, 3, -1, 2, 0) DESC, ABS(NVL(ACCOUNTED_DR, 0)
  + NVL(ACCOUNTED_CR, 0)) DESC, SIGN(NVL(ACCOUNTED_DR, 0) + NVL(ACCOUNTED_CR,
  0)) DESC)) ,CODE_COMBINATION_ID ,GL_TRANSFER_MODE_CODE ,CREATION_DATE ,
  ACCOUNTED_CR ,ACCOUNTED_DR ,UNROUNDED_ACCOUNTED_CR ,UNROUNDED_ACCOUNTED_DR ,
  GAIN_OR_LOSS_FLAG ,DECODE(ACCOUNTING_CLASS_CODE,
  'DUMMY_EXCHANGE_GAIN_LOSS_DUMMY', 'EXCHANGE_GAIN_LOSS',
  ACCOUNTING_CLASS_CODE) ,CURRENCY_CODE ,CURRENCY_CONVERSION_DATE ,
  CURRENCY_CONVERSION_RATE ,CURRENCY_CONVERSION_TYPE ,DESCRIPTION ,ENTERED_CR
  ,ENTERED_DR ,UNROUNDED_ENTERED_CR ,UNROUNDED_ENTERED_DR ,LAST_UPDATE_DATE ,
  LAST_UPDATE_LOGIN ,PARTY_ID ,PARTY_SITE_ID ,PARTY_TYPE_CODE ,
  STATISTICAL_AMOUNT ,USSGL_TRANSACTION_CODE ,CREATED_BY ,LAST_UPDATED_BY ,
  JGZZ_RECON_REF ,PROGRAM_UPDATE_DATE ,PROGRAM_APPLICATION_ID ,PROGRAM_ID ,
  ANALYTICAL_BALANCE_FLAG ,APPLICATION_ID ,REQUEST_ID ,GL_SL_LINK_TABLE ,
  BUSINESS_CLASS_CODE ,MPA_ACCRUAL_ENTRY_FLAG ,ENCUMBRANCE_TYPE_ID ,LEDGER_ID
  ,ACCOUNTING_DATE ,DECODE(:B35 ,'F',XLA_GL_SL_LINK_ID_S.NEXTVAL,NULL) ,
  DECODE(CONTROL_BALANCE_FLAG,'Y','P' ,'CUSTOMER','P' ,'SUPPLIER','P' ,NULL)
  FROM (SELECT /*+ Leading (LIN) Cardinality(LIN 1) use_nl(GCC) */
  LIN.AE_HEADER_ID AE_HEADER_ID , AE_LINE_NUM , CASE GAIN_OR_LOSS_FLAG WHEN
  'Y' THEN CASE CALCULATE_G_L_AMTS_FLAG WHEN 'Y' THEN
  DECODE(SIGN(SUM(NVL(UNROUNDED_ACCOUNTED_CR, 0)- NVL(UNROUNDED_ACCOUNTED_DR,
  0))), 1, DECODE(CODE_COMBINATION_STATUS_CODE ,:B24 ,LIN.CODE_COMBINATION_ID
  , -1 ), DECODE(ALT_CCID_STATUS_CODE ,:B24 ,ALT_CODE_COMBINATION_ID , -1 ))
  ELSE DECODE(CODE_COMBINATION_STATUS_CODE ,:B24 ,LIN.CODE_COMBINATION_ID ,
  -1 ) END ELSE DECODE(CODE_COMBINATION_STATUS_CODE ,:B24 ,
  LIN.CODE_COMBINATION_ID , -1 ) END CODE_COMBINATION_ID ,
  GL_TRANSFER_MODE_CODE , :B34 CREATION_DATE ,
  DECODE(NVL(SUM(UNROUNDED_ACCOUNTED_CR), SUM(UNROUNDED_ACCOUNTED_DR)), NULL,
  NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN CASE SIGN(
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)+
  NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0) ) WHEN -1 THEN NULL WHEN 1 THEN ROUND(
  (NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)+
  NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0)) /:B17 +:B18 ) *:B19 ELSE CASE
  SIGN(NVL(SUM(UNROUNDED_ENTERED_CR),0) - NVL(SUM(UNROUNDED_ENTERED_DR),0)+
  NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0)) WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE
  DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0, 0, NULL) END END ELSE
  DECODE(SUM(UNROUNDED_ACCOUNTED_CR), NULL, TO_NUMBER(NULL) , ROUND(
  (SUM(UNROUNDED_ACCOUNTED_CR) + NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0)) /:B20
  +:B21 ) *:B22 ) END) ACCOUNTED_CR , DECODE(NVL(SUM(UNROUNDED_ACCOUNTED_CR),
  SUM(UNROUNDED_ACCOUNTED_DR)), NULL, NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y'
  THEN CASE SIGN( NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0)- NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0) )
  WHEN -1 THEN NULL WHEN 1 THEN ROUND( (NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0)- NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0))
  /:B8 +:B9 ) *:B10 ELSE CASE SIGN(NVL(SUM(UNROUNDED_ENTERED_DR),0) -
  NVL(SUM(UNROUNDED_ENTERED_CR),0)- NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0))
  WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0,
  TO_NUMBER(NULL), 0) END END ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR), NULL,
  ROUND( (SUM(UNROUNDED_ACCOUNTED_DR)-NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0))
  /:B11 +:B12 ) *:B13 ,ROUND( SUM(UNROUNDED_ACCOUNTED_DR) /:B14 +:B15 ) *:B16
  ) END) ACCOUNTED_DR , DECODE(NVL(SUM(UNROUNDED_ACCOUNTED_CR),
  SUM(UNROUNDED_ACCOUNTED_DR)), NULL, NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y'
  THEN CASE SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)) WHEN -1 THEN NULL WHEN 1 THEN
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)
  ELSE CASE SIGN(NVL(SUM(UNROUNDED_ENTERED_CR),0) -
  NVL(SUM(UNROUNDED_ENTERED_DR),0)) WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE
  DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0, 0, NULL) END END ELSE
  SUM(UNROUNDED_ACCOUNTED_CR) END) UNROUNDED_ACCOUNTED_CR ,
  DECODE(NVL(SUM(UNROUNDED_ACCOUNTED_CR), SUM(UNROUNDED_ACCOUNTED_DR)), NULL,
  NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN CASE
  SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_CR),0)
  ) WHEN 1 THEN NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) WHEN -1 THEN NULL ELSE CASE
  SIGN(NVL(SUM(UNROUNDED_ENTERED_DR),0) - NVL(SUM(UNROUNDED_ENTERED_CR),0))
  WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0,
  TO_NUMBER(NULL), 0) END END ELSE SUM(UNROUNDED_ACCOUNTED_DR) END)
  UNROUNDED_ACCOUNTED_DR , GAIN_OR_LOSS_FLAG , ACCOUNTING_CLASS_CODE ,
  CURRENCY_CODE , DECODE(:B6 , 'PRIMARY', (DECODE(CURRENCY_CODE, :B7 , NULL,
  CURRENCY_CONVERSION_DATE)), CURRENCY_CONVERSION_DATE)
  CURRENCY_CONVERSION_DATE , CURRENCY_CONVERSION_RATE ,
  CURRENCY_CONVERSION_TYPE , LIN.DESCRIPTION DESCRIPTION ,
  DECODE(NVL(SUM(UNROUNDED_ENTERED_CR), SUM(UNROUNDED_ENTERED_DR)), NULL,
  NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN CASE SIGN(
  NVL(SUM(UNROUNDED_ENTERED_CR),0) - NVL(SUM(UNROUNDED_ENTERED_DR),0)+
  NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0) ) WHEN -1 THEN NULL WHEN 1 THEN
  ROUND( (NVL(SUM(UNROUNDED_ENTERED_CR),0) - NVL(SUM(UNROUNDED_ENTERED_DR),0)
  + NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0)) /ENTERED_CURRENCY_MAU +:B4 )
  *ENTERED_CURRENCY_MAU ELSE CASE SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) +NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0))
  WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0,
  0, NULL) END END ELSE DECODE(SUM(UNROUNDED_ENTERED_CR), NULL,
  TO_NUMBER(NULL) , ROUND( (SUM(UNROUNDED_ENTERED_CR) +
  NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0)) /ENTERED_CURRENCY_MAU +:B5 )
  *ENTERED_CURRENCY_MAU) END) ENTERED_CR ,
  DECODE(NVL(SUM(UNROUNDED_ENTERED_CR), SUM(UNROUNDED_ENTERED_DR)), NULL,
  NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN CASE SIGN(
  NVL(SUM(UNROUNDED_ENTERED_DR),0) - NVL(SUM(UNROUNDED_ENTERED_CR),0)-
  NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0) ) WHEN -1 THEN NULL WHEN 1 THEN
  ROUND( (NVL(SUM(UNROUNDED_ENTERED_DR),0) - NVL(SUM(UNROUNDED_ENTERED_CR),0)
  - NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0)) /ENTERED_CURRENCY_MAU +:B1 )
  *ENTERED_CURRENCY_MAU ELSE CASE SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_DR),0) -
  NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) -NVL(SUM(DOC_ROUNDING_ACCTD_AMT), 0))
  WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR), 0,
  TO_NUMBER(NULL), 0) END END ELSE DECODE(SUM(UNROUNDED_ENTERED_CR), NULL,
  ROUND( (SUM(UNROUNDED_ENTERED_DR)-NVL(SUM(DOC_ROUNDING_ENTERED_AMT), 0))
  /ENTERED_CURRENCY_MAU +:B2 ) *ENTERED_CURRENCY_MAU ,ROUND(
  SUM(UNROUNDED_ENTERED_DR) /ENTERED_CURRENCY_MAU +:B3 )
  *ENTERED_CURRENCY_MAU ) END) ENTERED_DR ,
  DECODE(NVL(SUM(UNROUNDED_ENTERED_CR), SUM(UNROUNDED_ENTERED_DR)), NULL,
  NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN CASE
  SIGN(NVL(SUM(UNROUNDED_ENTERED_CR),0) - NVL(SUM(UNROUNDED_ENTERED_DR),0))
  WHEN -1 THEN NULL WHEN 1 THEN NVL(SUM(UNROUNDED_ENTERED_CR),0) -
  NVL(SUM(UNROUNDED_ENTERED_DR),0) ELSE CASE
  SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)
  ) WHEN -1 THEN NULL WHEN 1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR),
  0, 0, NULL) END END ELSE SUM(UNROUNDED_ENTERED_CR) END)
  UNROUNDED_ENTERED_CR , DECODE(NVL(SUM(UNROUNDED_ENTERED_CR),
  SUM(UNROUNDED_ENTERED_DR)), NULL, NULL, CASE SWITCH_SIDE_FLAG WHEN 'Y' THEN
  CASE SIGN(NVL(SUM(UNROUNDED_ENTERED_CR),0) - NVL(SUM(UNROUNDED_ENTERED_DR),
  0)) WHEN 1 THEN NULL WHEN -1 THEN NVL(SUM(UNROUNDED_ENTERED_DR),0) -
  NVL(SUM(UNROUNDED_ENTERED_CR),0) ELSE CASE
  SIGN(NVL(SUM(UNROUNDED_ACCOUNTED_CR),0) - NVL(SUM(UNROUNDED_ACCOUNTED_DR),0)
  ) WHEN 1 THEN NULL WHEN -1 THEN 0 ELSE DECODE(SUM(UNROUNDED_ACCOUNTED_CR),
  0, TO_NUMBER(NULL), 0) END END ELSE SUM(UNROUNDED_ENTERED_DR) END)
  UNROUNDED_ENTERED_DR , :B33 LAST_UPDATE_DATE , :B32 LAST_UPDATE_LOGIN ,
  PARTY_ID , PARTY_SITE_ID , PARTY_TYPE_CODE , SUM(STATISTICAL_AMOUNT)
  STATISTICAL_AMOUNT , USSGL_TRANSACTION_CODE , :B31 CREATED_BY , :B30
  LAST_UPDATED_BY , JGZZ_RECON_REF , :B29 PROGRAM_UPDATE_DATE , :B28
  PROGRAM_APPLICATION_ID , :B27 PROGRAM_ID , ANALYTICAL_BALANCE_FLAG
  ANALYTICAL_BALANCE_FLAG , :B25 APPLICATION_ID , :B26 REQUEST_ID , 'XLAJEL'
  GL_SL_LINK_TABLE , BUSINESS_CLASS_CODE , MPA_ACCRUAL_ENTRY_FLAG ,
  ENCUMBRANCE_TYPE_ID , LEDGER_ID , ACCOUNTING_DATE , GCC.REFERENCE3
  CONTROL_BALANCE_FLAG FROM XLA_AE_LINES_GT LIN ,GL_CODE_COMBINATIONS GCC
  WHERE LEDGER_ID = :B23 AND AE_LINE_NUM IS NOT NULL AND
  LIN.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+) GROUP BY
  LIN.AE_HEADER_ID , AE_LINE_NUM , HEADER_NUM , GL_TRANSFER_MODE_CODE , :B34 ,
  :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , 'XLAJEL' , :B25 ,
  ACCOUNTING_CLASS_CODE , EVENT_CLASS_CODE , EVENT_TYPE_CODE ,
  LINE_DEFINITION_OWNER_CODE , LINE_DEFINITION_CODE , ENTERED_CURRENCY_MAU ,
  CURRENCY_CODE , CURRENCY_CONVERSION_TYPE , CURRENCY_CONVERSION_DATE ,
  CURRENCY_CONVERSION_RATE , PARTY_ID , PARTY_SITE_ID , PARTY_TYPE_CODE ,
  LIN.CODE_COMBINATION_ID , :B24 , CODE_COMBINATION_STATUS_CODE ,
  LIN.DESCRIPTION , JGZZ_RECON_REF , USSGL_TRANSACTION_CODE ,
  MERGE_DUPLICATE_CODE , ANALYTICAL_BALANCE_FLAG , SWITCH_SIDE_FLAG ,
  GAIN_OR_LOSS_FLAG , CALCULATE_G_L_AMTS_FLAG , ALT_CCID_STATUS_CODE ,
  ALT_CODE_COMBINATION_ID , LIN.BUSINESS_CLASS_CODE ,
  LIN.MPA_ACCRUAL_ENTRY_FLAG , ENCUMBRANCE_TYPE_ID , MERGE_INDEX , LEDGER_ID ,
  ACCOUNTING_DATE , GCC.REFERENCE3 ) WHERE ACCOUNTING_CLASS_CODE
  <>'DUMMY_EXCHANGE_GAIN_LOSS_DUMMY' OR NVL(ACCOUNTED_CR, 0) <>
  NVL(ACCOUNTED_DR, 0) )

and

2. SELECT /*+ Leading(LGT,XDL) use_nl(lgt xdl aeh ael)*/ AEL.AE_HEADER_ID ,AEL.AE_LINE_NUM ,AEH.PARENT_AE_HEADER_ID FROM XLA_AE_LINES_GT LGT ,XLA_AE_LINES AEL ,XLA_AE_HEADERS AEH ,XLA_DISTRIBUTION_LINKS XDL WHERE XDL.APPLICATION_ID = :B1 AND XDL.SOURCE_DISTRIBUTION_TYPE = LGT.REVERSE_DISTRIBUTION_TYPE AND LGT.REVERSAL_CODE = 'DUMMY_LR' AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = LGT.REVERSE_DIST_ID_NUM_1 AND NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_2,-99) = NVL(LGT.REVERSE_DIST_ID_NUM_2,-99) AND NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_3,-99) = NVL(LGT.REVERSE_DIST_ID_NUM_3,-99) AND NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_4,-99) = NVL(LGT.REVERSE_DIST_ID_NUM_4,-99) AND NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_5,-99) = NVL(LGT.REVERSE_DIST_ID_NUM_5,-99) AND NVL(XDL.SOURCE_DISTRIBUTION_ID_CHAR_1,' ') = NVL(LGT.REVERSE_DIST_ID_CHAR_1,' ') AND NVL(XDL.SOURCE_DISTRIBUTION_ID_CHAR_2,' ') = NVL(LGT.REVERSE_DIST_ID_CHAR_2,' ') AND NVL(XDL.SOURCE_DISTRIBUTION_ID_CHAR_3,' ') = NVL(LGT.REVERSE_DIST_ID_CHAR_3,' ') AND NVL(XDL.SOURCE_DISTRIBUTION_ID_CHAR_4,' ') = NVL(LGT.REVERSE_DIST_ID_CHAR_4,' ') AND NVL(XDL.SOURCE_DISTRIBUTION_ID_CHAR_5,' ') = NVL(LGT.REVERSE_DIST_ID_CHAR_5,' ') AND AEH.APPLICATION_ID = XDL.APPLICATION_ID AND AEH.AE_HEADER_ID = XDL.AE_HEADER_ID AND AEH.LEDGER_ID = LGT.LEDGER_ID AND AEH.ENTITY_ID = LGT.ENTITY_ID AND AEL.APPLICATION_ID = AEH.APPLICATION_ID AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID AND AEL.AE_LINE_NUM = XDL.AE_LINE_NUM AND AEH.PARENT_AE_HEADER_ID IS NOT NULL AND AEH.ACCOUNTING_ENTRY_STATUS_CODE IN ('D','N','I','R','RELATED_EVENT_ERROR')

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