EPY: HP_BGTRETDST Fails At HP_BGTRETDST.UPACTERN.UPDBU (Doc ID 2798861.1)

Last updated on APRIL 15, 2024

Applies to:

Symptoms

HP_BGTRETDST fails at HP_BGTRETDST.UPACTERN.UPDBU on 9.20.37

We see two different business units on PS_PAY_CHECK_EARNS where PAY_END_DT = '05/31/2021' and page_num =357 and line_num = 3. We get this following error when running the Budget Retro on 9.2.37:


ORA-01427: single-row subquery returns more than one row Failed SQL stmt: UPDATE PS_HPRDSTERN_TAO SET BUSINESS_UNIT = ( SELECT DISTINCT(B.BUSINESS_UNIT) FROM PS_PAY_CHECK_EARNS B WHERE B.COMPANY = PS_HPRDSTERN_TAO.COMPANY AND B.PAYGROUP = PS_HPRDSTERN_TAO.P AYGROUP AND B.PAY_END_DT = PS_HPRDSTERN_TAO.PAY_END_DT AND B.OFF_CYCLE = PS_HPRDSTERN_TAO.OFF_CYCLE AND B.PAGE_NUM = PS_HPRDSTERN_TAO.PAGE_NUM AND B.LINE_NUM = PS_HPRDSTERN_TAO.LINE_NUM AND B.SEPCHK = PS_HPRDSTERN_TAO.SEPCHK AND B.DEPTID = PS_HPRDSTERN_TAO.DEPTID AND B.JOBCODE = PS_HPRDSTERN_TAO.JOB CODE AND B.POSITION_NBR = PS_HPRDSTERN_TAO.POSITION_NBR AND B.EMPL_RCD = PS_HPRDSTERN_TAO.EMPL_RCD) WHERE PROCESS_INSTANCE = 2099231



The issue can be reproduced with the following steps:

1. Create a new position effective 01/01/2021 that is tied to Business Unit "GBIBU" - Organizational Development>Maintain Positions/Budgets>Add/Update Positions Info
2. Create a semi-monthly pay group - Set Up HCM>Product Related>Payroll for North America>Payroll Processing Controls>Pay Group
3. Create pay calendars along with pay run ID - Set Up HCM>Product Related>Payroll for North America>Payroll Processing Controls>Pay Run ID
4. Hired a test employee that is tied to that position - Workforce Administration>Personal Information>Add a Person
5. Associated the pay run ID to pay period 05/16/2021 to 05/31/2021 - Set Up HCM>Product Related>Payroll for North America>Payroll Processing Controls>Pay Calendar Table
6. Insert a row on Position Data and changed the Business Unit "GBIBU" to "US001" effective 05/21/2021 - Organizational Development>Maintain Positions/Budgets>Add/Update Positions Info
7. Run paysheet creation for that employee - Payroll for North America>Payroll Processing USA>Create and Load Paysheets>Create Paysheets
8. Insert a row an enter overtime prior to 05/21/2021 and indicate the business unit "GBIBU" that is prior to the change on Position Data - Payroll for North America>Payroll Processing USA>Update Paysheets>By Payline
9. Entered overtime also on the row that paysheet creation created.
10. Run a payroll calculation and confirm - Payroll for North America>Payroll Processing USA>Produce Payroll>Calculate Payroll
11. Run PSPPFUND and PAYGL02 - Payroll for North America>Payroll Distribution>Commitment Accounting USA>Distribute Actuals & Payroll for North America>Payroll Distribution>Provide GL Information>Commitment Accounting Actuals
12. Do a Budget Retro effective the beginning of the pay period that changes the combination code only - Set Up HCM>Product Related>Commitment Accounting>Budget Information>Department Budget Table USA
13. Run the budget retro process which generates the error message.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.