EEX 9.1: EX_TRAN_PRCS Performance Issue on IU_PROCESSOR Steps U000.U000-010 and B000.B000-01A (Doc ID 1459366.1)

Last updated on MARCH 16, 2016

Applies to:

PeopleSoft Enterprise FIN Expenses - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

 ISSUE:

The Expense Processing Application Engine Program (EX_TRAN_PRCS) is experiencing a performance degradation and takes over 6 hours to complete. Upon analysis performed on the trace files taken, it has been confirmed that 97% of the time was spent on SQL Statement executions. From these, the ones causing the biggest performance degradation were:

  - The SQL Statement within IU_PROCESSOR.B000.B000-01A took 3,387 seconds to complete (57 minutes), being called twice.
  - The SQL Statement within IU_PROCESSOR.U000.U000-010 took 16,937 seconds to complete (4 hours and 42 minutes), being called 12 times.

Here is a sample of the SQL Statement executed at Step IU_PROCESSOR.U000.U000-010:

INSERT INTO PS_EXACCTIU_TAO7
(PROCESS_INSTANCE, EX_DOC_ID, EX_DOC_TYPE, LINE_NBR, DISTRIB_LINE_NUM, DST_ACCT_TYPE, APPL_JRNL_ID, UNPOST_SEQ, ACCOUNTING_DT, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, ACCOUNTING_PERIOD, BUSINESS_UNIT, BUSINESS_UNIT_GL, FISCAL_YEAR, JOURNAL_ID, JOURNAL_DATE, JOURNAL_LINE, GL_DISTRIB_STATUS, LEDGER_CATEGORY, LEDGER_GROUP, LEDGER, MONETARY_AMOUNT, CURRENCY_CD, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV, RT_TYPE, EMPLID, BI_DISTRIB_STATUS, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, PROJECT_ID, BUSINESS_UNIT_PC, ACTIVITY_ID, RESOURCE_CATEGORY, RESOURCE_TYPE, RESOURCE_SUB_CAT, ANALYSIS_TYPE, PC_DISTRIB_STATUS, VAT_DISTRIB_STATUS, MOVEMENT_FLAG, VAT_BASIS_AMT, VAT_BASIS_AMT_BSE, VAT_CALC_AMT, VAT_CALC_AMT_BSE, VAT_RCVRY_AMT, VAT_RCVRY_AMT_BSE, VAT_REBATE_AMT, VAT_REBATE_AMT_BSE, VAT_TRANS_AMT, VAT_TRANS_AMT_BSE, VAT_AMT, VAT_AMT_BSE, VAT_RCLM_AMT, VAT_RCLM_AMT_BSE, TXN_CURRENCY_CD1, CURRENCY_CD1, IU_ANCHOR_FLG, IU_SYS_TRAN_CD, IU_TRAN_CD, IU_LINE_TYPE, IU_LEGAL_ENT_BU_IU, PRIMARY_LEDGER_IU, TRANSL_LEDGER_IU, SETID_IU, LEDGERS_SYNC_IU, SECOND_LEDGERS_IU, MAX_SEQUENCE)

SELECT
2822182, LN_NA.EX_DOC_ID, LN_NA.EX_DOC_TYPE, LN_NA.LINE_NBR, LN_NA.DISTRIB_LINE_NUM, 'IUPY', LN_A.APPL_JRNL_ID, LN_A.UNPOST_SEQ, LN_A.ACCOUNTING_DT, LN_A.DOC_TYPE, LN_A.DOC_SEQ_NBR, LN_A.DOC_SEQ_DATE, LN_A.ACCOUNTING_PERIOD, LN_A.BUSINESS_UNIT, LN_A.BUSINESS_UNIT_GL, LN_A.FISCAL_YEAR, ' ', LN_A.JOURNAL_DATE, LN_A.JOURNAL_LINE, 'N', LN_A.LEDGER_CATEGORY, LN_A.LEDGER_GROUP, LN_A.LEDGER, LN_NA.MONETARY_AMOUNT, LN_A.CURRENCY_CD, LN_NA.FOREIGN_AMOUNT, LN_NA.FOREIGN_CURRENCY, LN_NA.RATE_MULT, LN_NA.RATE_DIV, LN_NA.RT_TYPE, LN_A.EMPLID, 'I', LN_A.ACCOUNT, LN_A.ALTACCT, LN_A.DEPTID, LN_A.OPERATING_UNIT, LN_A.PRODUCT, LN_A.FUND_CODE, LN_A.CLASS_FLD, LN_A.PROGRAM_CODE, LN_A.BUDGET_REF, LN_NA.BUSINESS_UNIT_GL, LN_A.AFFILIATE_INTRA1, LN_A.AFFILIATE_INTRA2, LN_A.CHARTFIELD1, LN_A.CHARTFIELD2, LN_A.CHARTFIELD3, LN_A.PROJECT_ID, ' ', ' ', ' ', ' ', ' ', ' ', 'I', 'I', LN_A.MOVEMENT_FLAG, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, LN_A.TXN_CURRENCY_CD1, LN_A.CURRENCY_CD1, 'N', LN_A.IU_SYS_TRAN_CD, LN_A.IU_TRAN_CD, '8', LN_A.IU_LEGAL_ENT_BU_IU, LN_A.PRIMARY_LEDGER_IU, LN_A.TRANSL_LEDGER_IU, LN_A.SETID_IU, LN_A.LEDGERS_SYNC_IU, LN_A.SECOND_LEDGERS_IU, LN_A.MAX_SEQUENCE

FROM
PS_EXACCTI2_TAO7 LN_A,PS_EXACCTIU_TAO7 LN_NA

WHERE
LN_A.IU_ANCHOR_FLG='Y' AND LN_A.PROCESS_INSTANCE = 2822182 AND LN_A.EX_DOC_TYPE = 'S' AND LN_A.DISTRIB_LINE_NUM=LN_NA.DISTRIB_LINE_NUM AND LN_A.EX_DOC_ID=LN_NA.EX_DOC_ID AND LN_A.EX_DOC_TYPE=LN_NA.EX_DOC_TYPE AND LN_A.LINE_NBR=LN_NA.LINE_NBR  AND LN_NA.BUSINESS_UNIT_GL <> LN_A.BUSINESS_UNIT_GL AND LN_A.IU_LINE_TYPE='1' AND LN_NA.IU_LINE_TYPE IN ('2','U') AND ( LN_NA.FOREIGN_AMOUNT < 0  OR (LN_NA.FOREIGN_AMOUNT= 0 AND LN_NA.MONETARY_AMOUNT < 0)) AND LN_A.SETID_IU='SHARE' AND LN_A.LEDGER_GROUP='RECORDING' AND (((LN_A.SETID_IU <> LN_NA.SETID_IU OR
LN_A.LEDGER_GROUP <> LN_NA.LEDGER_GROUP) AND (LN_NA.LEDGERS_SYNC_IU = 'Y' OR LN_NA.SECOND_LEDGERS_IU = 'N') AND LN_NA.PRIMARY_LEDGER_IU = 'Y') OR (((LN_A.SETID_IU = LN_NA.SETID_IU AND LN_A.LEDGER_GROUP = LN_NA.LEDGER_GROUP) OR (LN_NA.LEDGERS_SYNC_IU = 'N' AND LN_NA.SECOND_LEDGERS_IU = 'Y')) AND LN_A.LEDGER = LN_NA.LEDGER)) AND (LN_A.CURRENCY_CD=LN_NA.CURRENCY_CD) AND LN_A.TRANSL_LEDGER_IU='N' AND LN_A.PROCESS_INSTANCE=2822182 AND LN_NA.PROCESS_INSTANCE=2822182

REPLICATION STEPS:

  - Log into the FSCM Application Online with a User ID for Expenses Administration
  - Navigate to: Travel and Expenses > Process Expenses > Expense Processing
  - Add a new Run Control ID
  - Select the Stage Payments, Post Liabilities, and Post Payments options
  - Run the process
  - The program takes a long time to complete, specially for the several calls into Step IU_PROCESSOR.U000.U000-010

EXPECTED BEHAVIOR:

The Expense Processing program should be improved in its performance, and complete all its transaction handling within a proper period of time given the volume processed.

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