EPY: Budget Retro Distribution (HP_BGTRETDST ) performance issue
(Doc ID 1560132.1)
Last updated on JULY 21, 2023
Applies to:
PeopleSoft Enterprise HCM Payroll for North America - Version 9.1 and laterInformation in this document applies to any platform.
Goal
Issue #1:
Performance Issue when running the Budget Retro job (HP_BGTRETDST) for 200-400 positions over multiple fiscal years. The process ran for 12 days.
Issue #2:
There is no where clause in the Insert statement -
INSERT INTO PS_HP_BGTACTUAL_WK (HP_KK_DOC_ID ,HP_KK_DOC_LINE_NBR ,FISCAL_YEAR ,HP_BUDGET_YEAR ,EMPLID ,EMPL_RCD ,POSITION_NBR ,HRS_JOB_OPENING_ID ,POSITION_POOL_ID ,SETID_JOBCODE ,JOBCODE ,SETID ,DEPTID ,ENCUMB_PROC_TYPE ,COMPANY ,PAYGROUP ,BUSINESS_UNIT_GL ,ACCT_CD ,ACCOUNT ,POSTED_ACTUAL_AMT ,POSTED_NE_ACT_AMT ,POSTED_ENCUMB_AMT ,ANNUAL_ENCUMB_AMT ,WORK_ACTUAL_AMT ,WORK_ENCUMB_AMT ,HP_ENC_DATE) SELECT HP_KK_DOC_ID ,HP_KK_DOC_LINE_NBR ,FISCAL_YEAR ,HP_BUDGET_YEAR ,EMPLID ,EMPL_RCD ,POSITION_NBR ,HRS_JOB_OPENING_ID ,POSITION_POOL_ID ,SETID_JOBCODE ,JOBCODE ,SETID ,DEPTID ,ENCUMB_PROC_TYPE ,COMPANY ,PAYGROUP ,BUSINESS_UNIT_GL ,ACCT_CD ,ACCOUNT ,POSTED_ACTUAL_AMT ,POSTED_NE_ACT_AMT ,POSTED_ENCUMB_AMT ,ANNUAL_ENCUMB_AMT ,WORK_ACTUAL_AMT ,WORK_ENCUMB_AMT ,HP_ENC_DATE FROM PS_BUDGET_ACTUALS
The initial insert is all rows from PS_BUDGET_ACTUALS, 3.6 million rows.
Why doesn't this insert statement use a WHERE clause for filtering based on Run Control parameters?
The UPDATE statement does use the Run Control parameters such as Dept ID and Fiscal Year.
The Insert and Delete statements take over 100 sec.
Solution
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
Goal |
Solution |
References |