Performance issue for PSA_ACCTGGL at step PSA_ACCTGGL.SEL_PC.SHARINGA (Doc ID 1364494.1)

Last updated on FEBRUARY 25, 2016

Applies to:

PeopleSoft Enterprise FIN Contracts - Version 9 to 9 [Release 9]
PeopleSoft Enterprise FIN Project Costing - Version 9 to 9 [Release 9]
Information in this document applies to any platform.

Symptoms

On :  9 version, Performance, DB2, 9.0 ESA bundle #32

When attempting to run Process Accounting Process (PSA_ACCTGGL) the following error occurs due to performance.

ERROR
-----------------------
The process abended after a long time of execution


-- 01.12.24 ...(PSA_ACCTGGL.Sel_PC.SharingA) (SQL)
INSERT INTO PS_PSA_ACCTG_TA15 (PROCESS_INSTANCE, BUSINESS_UNIT_PC,
PROJECT_ID, ACTIVITY_ID, RESOURCE_ID, ACCTG_FLG, CUR_EFFDT_TYPE,
CONTRACT_NUM, CONTRACT_LINE_NUM, CONTRACT_PPD_SEQ, ACCT_PLAN_ID, EVENT_NUM,
ACCOUNT, ALTACCT, ACCOUNTING_DT, DELETE_ME, BUSINESS_UNIT, BUSINESS_UNIT_BI,
BUSINESS_UNIT_GL, BUSINESS_UNIT_TO, CA_RQST_SRC, GL_DISTRIB_STATUS,
CUR_EFFDT, CURRENCY_CD, BASE_CURRENCY, FOREIGN_CURRENCY, FOREIGN_AMOUNT,
RESOURCE_AMOUNT, PROJECT_TYPE, AMOUNT_BSE, AMOUNT, DST_ID, ANALYSIS_TYPE,
RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, RESOURCE_ID_FROM, LEDGER,
LEDGER_GROUP, APPL_JRNL_ID, STATISTICS_CODE, STATISTICS_ACCOUNT,
PROJ_TRANS_TYPE, PROJ_TRANS_CODE, RT_TYPE, MOVEMENT_FLAG, RATE_MULT,
RATE_DIV, BUSINESS_UNIT_GL2, RESOURCE_QUANTITY, DEPTID, DST_ACCT_TYPE,
DST_SEQ_NUM, SEQ_NBR, POST_SEQ_NUM, SYSTEM_SOURCE, DESCR, LINE_DESCR,
UNIT_OF_MEASURE, TRANS_DT, ACTIVE_STATUS, OPERATING_UNIT, PRODUCT, FUND_CODE,
CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1,
AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, IU_SYS_TRAN_CD,
IU_TRAN_CD, CRIT_SEQ, INVOICE, BUSINESS_UNIT_WO, WO_ID, WO_TASK_ID,
RSRC_TYPE, RES_LN_NBR, BUSINESS_UNIT_AR, CUST_ID, ITEM, ITEM_LINE,
ITEM_SEQ_NUM, OPRID, ORDER_NO, RESOURCE_STATUS, EMPLID, RES_USER1, RES_USER2,
RES_USER3, RES_USER4, RES_USER5, LEVEL1_CF, LEVEL2_CF, LEVEL3_CF, LEVEL4_CF,
LEVEL5_CF) SELECT TA0.PROCESS_INSTANCE, TA0.BUSINESS_UNIT_PC, TA0.PROJECT_ID,
TA0.ACTIVITY_ID, TA0.RESOURCE_ID, 'S', 'A', TA0.CONTRACT_NUM,
TA0.CONTRACT_LINE_NUM, TA0.CONTRACT_PPD_SEQ, PSA.ACCT_PLAN_ID, PSA.EVENT_NUM,
TA0.ACCOUNT, TA0.ALTACCT, TA0.ACCOUNTING_DT, TA0.DELETE_ME,
TA0.BUSINESS_UNIT_PC, TA0.BUSINESS_UNIT_BI, TA0.BUSINESS_UNIT_GL,
TA0.BUSINESS_UNIT_TO, TA0.CA_RQST_SRC, TA0.GL_DISTRIB_STATUS, TA0.CUR_EFFDT,
TA0.CURRENCY_CD, TA0.BASE_CURRENCY, TA0.FOREIGN_CURRENCY, TA0.FOREIGN_AMOUNT,
TA0.RESOURCE_AMOUNT, TA0.PROJECT_TYPE, TA0.AMOUNT_BSE, TA0.AMOUNT,
PSA.DST_ID, TA0.ANALYSIS_TYPE, TA0.RESOURCE_TYPE, TA0.RESOURCE_CATEGORY,
TA0.RESOURCE_SUB_CAT, TA0.RESOURCE_ID_FROM, TA0.LEDGER, TA0.LEDGER_GROUP,
TA0.APPL_JRNL_ID, TA0.STATISTICS_CODE, TA0.STATISTICS_ACCOUNT,
TA0.PROJ_TRANS_TYPE, TA0.PROJ_TRANS_CODE, TA0.RT_TYPE, TA0.MOVEMENT_FLAG,
TA0.RATE_MULT, TA0.RATE_DIV, TA0.BUSINESS_UNIT_GL2, TA0.RESOURCE_QUANTITY,
TA0.DEPTID, TA0.DST_ACCT_TYPE, TA0.DST_SEQ_NUM, TA0.SEQ_NBR,
TA0.POST_SEQ_NUM, TA0.SYSTEM_SOURCE, TA0.DESCR, TA0.LINE_DESCR,
TA0.UNIT_OF_MEASURE, TA0.TRANS_DT, TA0.ACTIVE_STATUS, TA0.OPERATING_UNIT,
TA0.PRODUCT, TA0.FUND_CODE, TA0.CLASS_FLD, TA0.PROGRAM_CODE, TA0.BUDGET_REF,
TA0.AFFILIATE, TA0.AFFILIATE_INTRA1, TA0.AFFILIATE_INTRA2, TA0.CHARTFIELD1,
TA0.CHARTFIELD2, TA0.CHARTFIELD3, TA0.IU_SYS_TRAN_CD, TA0.IU_TRAN_CD,
TA0.CRIT_SEQ, TA0.INVOICE, TA0.BUSINESS_UNIT_WO, TA0.WO_ID, TA0.WO_TASK_ID,
TA0.RSRC_TYPE, TA0.RES_LN_NBR, TA0.BUSINESS_UNIT_AR, TA0.CUST_ID, TA0.ITEM,
TA0.ITEM_LINE, TA0.ITEM_SEQ_NUM, TA0.OPRID, TA0.ORDER_NO,
TA0.RESOURCE_STATUS, TA0.EMPLID, TA0.RES_USER1, TA0.RES_USER2, TA0.RES_USER3,
TA0.RES_USER4, TA0.RES_USER5, ' ', ' ', ' ', ' ', ' ' FROM PS_PSA_ACCTG_TA05
TA0 , PS_PSA_PLANS_TA05 PSA , PS_CA_DTL_PROJ_VW DETPV, PS_PROJ_AN_GRP_MAP
PROJ_AN , PS_SET_CNTRL_REC REC WHERE TA0.PROCESS_INSTANCE = 265517 AND
PSA.PROCESS_INSTANCE = 265517 AND PROJ_AN.ANALYSIS_GROUP = 'PSTDR' AND
PSA.CONTRACT_NUM = DETPV.CONTRACT_NUM AND PSA.CONTRACT_LINE_NUM =
DETPV.CONTRACT_LINE_NUM AND TA0.BUSINESS_UNIT_PC = DETPV.BUSINESS_UNIT_PC AND
TA0.PROJECT_ID = DETPV.PROJECT_ID AND TA0.ACTIVITY_ID = DETPV.ACTIVITY_ID AND
TA0.CONTRACT_NUM = PSA.CONTRACT_NUM AND TA0.CONTRACT_LINE_NUM =
PSA.CONTRACT_LINE_NUM AND TA0.ANALYSIS_TYPE = PROJ_AN.ANALYSIS_TYPE AND
TA0.ANALYSIS_TYPE NOT IN ('UTL', 'UAJ') AND TA0.BUSINESS_UNIT =
REC.SETCNTRLVALUE AND REC.RECNAME = 'PROJ_AN_GRP_MAP' AND PROJ_AN.SETID =
REC.SETID AND TA0.ANALYSIS_TYPE <> 'OLT' AND TA0.RESOURCE_ID NOT IN ( SELECT
RESOURCE_ID FROM PS_PSA_ACCTG_TA15 WHERE PROCESS_INSTANCE = 265517)
/
-- Row(s) affected: 0
-- 09.00.57 Process 265517 ABENDED at Step PSA_ACCTGGL.Sel_PC.SharingA (Action SQL) -- RC = 8200
ROLLBACK
/
-- 09.00.57 SQL Error: [IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003 (SQLSTATE 08003) -99999


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Project Costing > Accounting > Process Accounting

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