Payee %1 is being processed in Group List %2 run by User %3. There could be other overlaps.

The below error usually occurs when we run the payroll for a payee using group list who is already being processed by another user through another Group List.

Payee %1 is being processed in Group List %2 run by User %3. There could be other overlaps.

To resolve it, the user needs to be deleted from the other group list used by the other user. Just run below SQL and re-run the payroll for payee in error.

DELETE FROM PS_GP_GRP_LIST_RUN WHERE oprid = '';

ORA-00001: unique constraint (SYSADM.PS_GP_PI_GEN_SOVR) violated in Global Payroll

This error usually occurs when the COBOL process GPPDPRUN errors out and we run it again on top of that. It basically tries to insert the same set of rows on the payroll processing tables with the earlier set of rows already present. And when it does that it throws the UNIQUE CONSTRAINT error.

The first thing that we need to do is check the process log where we will get the clue as to where exactly process failed. Important thing to notice here is, if you process the payroll through streams then it's better to check which particular stream caused the COBOL to fail. Once the stream is located then just cancel that particular stream and re-run it, the error will be gone.

However, if the payroll is processed without streams then the whole payroll needs to be cancelled and re-run which will resolve the issue.

PeopleSoft Installation: Change from host-only to bridged to enable internet in VM

When you deploy Linux based PeopleSoft VM on Virtual Box, system enables Host-Only Adapter so that host and guest can communicate with each other. Most likely, guest machine is assigned an IP 192.168.56.101 though it might differ also.
But the issue with this type of setup is that, guest machine can't communicate with external network which means Web Services to communicate with third part systems can't be created and to deal with this we need to change the adapter setting in Virtual Machine so that it can communicate both with host and external network. The best contender for the same is Bridged Adapter but that's not just enough as we have to also do couple of other things for this to work so lets get started.

Change network setting

Change the network type form host only to bridged adapter under network setting in Virtual Box as shown below:

Obtain new IP address of VM

Restart the VM and check the new IP address by issuing ifconfig command and note down new IP address

The new IP address of VM is 192.168.100.60

Changes in host system

Do following changes in the host machine

tnsnames.ora file

File Location - C:\oracle\product\12.1.0\client_1\network\admin

change the IP address to new IP address i.e. from 192.168.56.101 to 192.168.100.60

Hosts file

File Location - C:\Windows\System32\drivers\etc
change the IP address to new IP address i.e. from 192.168.56.101 to 192.168.100.60 for host hcm92.ps.com

Change in guest machine

Login into VM and change the IP address to new IP address i.e. from 192.168.56.101 to 192.168.100.60 for hcm92.ps.com
Hosts file location - /etc/hosts

Segmentation not created for a payee in PeopleSoft Global Payroll

Recently we had an issue when payroll wasn't creating segments for a new country for which we were implementing GP. We spend nearly two days trying to figure out what exactly was causing this.

These are possible reasons:

Unprocessed Iterative Triggers

If one or more iterative triggers of previous period are in unprocessed state then segmentation for those employees will not be processed. If so then delete those triggers using below SQL
DELETE FROM PS_GP_RTO_TRGR WHERE COUNTRY = <payee's country> AND EMPLID = <emplid> AND TRGR_STATUS = 'U' AND CAL_RUN_ID <> <Current Calendar Group>;

There might also be unprocessed iterative triggers created for the country other than payee's country which will cause this hence delete those triggers too.

Unprocessed Retro Triggers

If one or more retro triggers for other countries are in unprocessed state then segmentation for those employees will not be processed. If so then delete those triggers using below SQL
DELETE FROM PS_GP_ITER_TRGR WHERE COUNTRY <> <payee's country> AND EMPLID = <emplid> AND ITER_TRGR_STATUS = 'U';

Segmentation Triggers

If one or more segmentation triggers for other countries exist and are active for those employees then segmentation will not be processed.
DELETE FROM PS_GP_SEG_TRGR WHERE COUNTRY <> <payee's country> AND EMPLID = <emplid> AND SEG_TRGR_STATUS = 'A';

In our case, all above where the reasons so we basically deleted all those unwanted triggers (Iterative, Retro and Segment) using SQL script and it resolved the issue. The segmentation was processed.

Retro trigger not picked up when created through INSERT SQL in PeopleSoft Global Payroll

Global Payroll system is a den of mysteries and one of them is the retro trigger created using INSERT SQL. The issue with this is it doesn't get picked up by the payroll process unless we also create an iterative trigger for the current open period.

In case you haven't noticed, whenever you create a retro trigger manually through PIA, it also creates an iterative trigger as it's shown below:

Retro trigger created through PIA

Iterative trigger is automatically created with source record as GP_RTO_TRGR_VW

So, it means whenever you create retro triggers using INSERT SQL then create an iterative trigger also. Only if you do this the payroll will pick up the retro trigger and process it.
You can use these SQLs to create retro trigger and iterative trigger respectively.

SQL to create retro trigger

Insert into PS_GP_RTO_TRGR (EMPLID,COUNTRY,TRGR_EVENT_ID,TRGR_EFFDT,TRGR_CREATE_TS,RTO_TRGR_SRC,TRGR_STATUS,TRGR_DESCR,RECNAME,FIELDNAME,TRGR_FLD_VAL_CHAR,TRGR_FLD_VAL_DT,TRGR_FLD_VAL_NUM,CAL_RUN_ID) values (,'GBR','JOB',to_date(,'DD-MM-RR'),SYSDATE,'M','U',' ',' ',' ',' ',null,0,' ');

SQL to create iterative Trigger

Insert into PS_GP_ITER_TRGR (EMPLID,CAL_RUN_ID,ITER_TRGR_STATUS,TRGR_CREATE_TS,ITER_TRGR_SRC,COUNTRY,RECNAME,FIELDNAME,TRGR_FLD_VAL_CHAR,TRGR_FLD_VAL_DT,TRGR_FLD_VAL_NUM) values (,,'U',SYSDATE,'O','GBR','GP_RTO_TRGR_VW',' ',' ',null,0);

Object not copied, parent definition does not exist in target database (62,33)

We once were doing migration for a project but got stuck when app designer wouldn't copy one of the objects in the project to target instance and kept throwing below error:

Object not copied, parent definition does not exist in target database (62,33)

The error was associated with one AE SQL step and we spend quite some time scratching our head over this. Finally we discovered that we forgot to include the parent section of the SQL Step of the App Engine in the project. In other words, we were trying to migrate a child object without it's parent object.  

Well, it doesn't sound convincing right ? because we very often migrate child objects without their parent objects. But in our case it didn't work because the child object (SQL or PeopleCode) was added newly.

Let's try to understand it in a different way. Consider we have an app engine which contains Sections and Steps and this is how they are related.

App Engine - Section(s) - Step(s)

Consider that we have added a new step under a section so if we have to migrate one of the Steps (PeopleCode or SQL) then we must also take along the corresponding parent Section. If you have included the child object but not it's parent then app designer will throw an error (Shown in the first para).  To resolve this, include the respective parent section.

Prompt isn't fetching the elements in the Rule Package Definition in PeopleSoft Global Payroll

Have you ever encountered this issue where in the rule package definition, when you try to add desired elements you don't see them in the prompt as shown below:

as you can see the element that you have defined isn't displayed in the prompt and this is related to your Global Payroll access. For instance, in this case I defined a country specific element for GBR but in my Global Payroll profile I have access to All Countries as shown below:

To resolve the issue, just change the flag to Specific Country and also select that particular country you defined the element for i.e. GBR in this case:

Now go back to the Rule Package definition, open the page again and try adding the element. If it doesn't work then log-out/log-in and then try again.

Unable to stop retro processing for a payee in PeopleSoft Global Payroll

Recently we had an issue where for an employee a retro trigger got created accidentally and caused some of the finalized calendars to re-open. It completely screwed up the payroll results for the current period because we had many Positive Inputs for adjustment and the retro which caused the issue was completely undesired.

We desperately wanted to get rid of the retro pressing so we first tried with cancelling the retro trigger and ran the payroll for that employee but it didn't help as the retro was still opening. We then set the retro limit through the  page hoping that the Assign Retro Limit page hoping that it won't go back beyond current calendar but that didn't help either. The retro was still processing.

We then did following to get the results we wanted to get.

1. Open the Payee Status page and set the process indicator flag to Cancel
2. Process the payroll for the payee
3. Open the Review Triggers page and cancel all the retro triggers for the payee
4. Open the Payee Status page and set the process indicator flag to Uncancel
5. Process the payroll for the payee again

Application Engine with Disable Restart "off" is going "No Success" in PeopleSoft

Have you every encountered this issue when an App Engine process keeps failing with No Success message over and again. Lets discuss what might cause this and how we can resolve.
The Application Engine program which is restart-able always commits the DB transactions if it fails. We can make an app engine re-startable by clearing the checkbox "Disable Restart" in the properties dialog box as shown below:

The next course of action is to re-start the process again hoping that it would resume the processing from where it got failed. If the program have been setup that way then it would work as expected but if not then it will fail again.

Even if you delete the failed process instance and kick of the process again, it would still fail because during the first run the program would have inserted processing data into temp tables and when you run the program again, it tries to insert the same set of processing data into the same temp tables causing the process to fail.

Now the question is, how to resolve this ?

The simple answer is - first clear all the temporary processing data from the tables and then run the process again. But this is easier said than done. So lets break it down into the steps needed to resolve this issue.

Gather all the temporary table instances and standard SQL tables

In this step we need to collect the names of all the tables into which the program tries to insert the data. The easiest way to do this is by first enabling the Application Engine trace and then checking the SQL trace file. Just search all the INSERT INTO text in the SQL trace file which will give you all the tables where the temporary or the transnational data is inserted.

Deleting the data from tables

In this step we will delete the data form all the tables gathered in previous step but perform this task very carefully so that we don't end up deleting any data which which isn't supposed to. First of all, all the data from temp tables can be deleted without any worry. However, before deleting the data from standard SQL tables, we first need to analyse and determine what exactly need to be deleted or updated and only after that data should be deleted.

Run the process again

After all the temporary or transnational data is deleted, we are ready to run the process again. So once the process is run again it should go to success.

How to unfinalize a calendar in PeopleSoft Global Payroll

One thing you all must agree that everyone of us who work in PeopleSoft Global Payroll, at some point of time have to undo the finalize of a calendar just to process it again. But the issue is, there is no delivered way of doing this so we have to use our own way which is through SQL script.

A calendar group can be un-finalized by executing following three steps:

1. Reset the calculation status to Successful in the table GP_PYE_SEG_STAT
2. Reset all the flags and the finalized time stamp in the table GP_CAL_RUN
3. Reset the finalized time stamp in the table GP_CAL_RUN_DTL

All the above three steps can be executed through SQL given below:

UPDATE PS_GP_PYE_SEG_STAT
SET PYE_CALC_STAT = '50'
WHERE CAL_RUN_ID = 'Calendar Group';

UPDATE PS_GP_CAL_RUN
SET RUN_FINALIZED_IND = 'N',
RUN_FINALIZED_TS = NULL,
RUN_OPEN_IND = 'Y',
PMT_SENT_IND = 'N',
GL_SENT_IND = 'Y'
WHERE CAL_RUN_ID = 'Calendar Group';

UPDATE PS_GP_CAL_RUN_DTL
SET CAL_FINAL_TS = NULL
WHERE CAL_RUN_ID = 'Calendar Group';

Why the dynamic role isn't getting assigned to intended users in PeopleSoft

I was once working on a project where I had to create Query for dynamic role assignment. Everything was going well as I was able to create it without any challenge. The query was fetching the desired set of users to whom the role was supposed to be assigned.

However, when I ran the Dynamic Role Assignment app engine (DYNROLE_PUBL), the role wasn't getting assigned to the intended users. I did the RCA and turned out that the PS Query which I created, wasn't returning the distinct OPRIDs hence the app engine process wasn't assigning the role to users.

To resolve the issue, I added a DISTINCT keyword next to SELECT using expression in the PS Query which though started returning distinct rows yet the main issue remained unresolved. I ended up spending several hours to get around this but no luck.

Then I read somewhere that instead of using DISTINCT keyword, I should rather enable distinct option in the query properties to get the distinct rows. The option is shown below:

Then I ran the process again and the issue got resolved.

Most frequently queried tables in PeopleSoft Global Payroll

A PeopleSoft Global Payroll specialist who maintains and support the system have to frequently query a set of tables very frequently. Lets see what are those.

The below tables contain the calculation results and are source of data that is displayed in the page Result by Calendar Group
PS_GP_PYE_PRC_STAT  (Contains all the resolved calendars)
PS_GP_PYE_SEG_STAT (Contains all the resolved segments calendar-wise)
PS_GP_RSLT_ERN_DED (Contains payroll earnings and deduction results under each segments calendar-wise)
PS_GP_RSLT_DELTA (Contains retro delta results calendar-wise)
PS_GP_RSLT_ACUM (Contains accumulator results calendar-wise)
PS_GP_RSLT_PIN (Contains supporting element results (bracket, formula, variables etc..) calendar-wise)
PS_GP_RSLT_PI_DATA (Contains positive input results calendar-wise)

The below tables contain triggers created from various sources and can be seen in the page Review Triggers
PS_GP_RTO_TRGR (Contains retro triggers)
PS_GP_SEG_TRGR (Contains segmentation triggers)
PS_GP_ITER_TRGR (Contains iterative triggers)

This one contains the error/warning messages associated with payees calendar-wise after the pay calculation process is run and can be seen through the page Payee Messages
PS_GP_MESSAGES

When we run the payroll with debug and trace option enabled, the audit trail is stored in the below table and can be seen through the page Element Resolution Chain
PS_GP_AUDIT_TBL

The payee level data is stored and maintained in below tables
PS_GP_PI_MNL_DATA (The earnings and deductions assigned to a payee, Page - One Time (Positive Input))
PS_GP_PAYEE_DATA(The retro limit at payee level, page Assign Retro Limit)
PS_GP_PYE_OVRD (Elements assigned to a payee for a specific period, page -Element Assignment by Payee)

Calendar Group Definitions are stored in this table:
PS_GP_CAL_RUN

Payments processing details and results are stored in below tables:
PS_GP_PMT_PREPARE (Payment processing details for a calendar group, page -Run Payment Prep Process)
PS_GP_PAYMENT (Payment processing results for a calendar group)

GP-GL processing detail and result are stored in below tables:
PS_GP_GL_PREPARE (GP-GL processing details for a calendar group)
PS_GP_GL_DATA (GP-GL processing results)

PeopleSoft Time Admin Process Error : Dropped 1 Time Reporters found in other concurrent runs

Recently we got this error while running the time admin process TL_TIMEADMIN. The error text read as:

Dropped 1 Time Reporters found in other concurrent runs. (13507,9)

We did the RCA and discovered that the table TL_TR_STATUS contains the data that suggests the employee in question is being processed through another run control id. Following fields in the table contained values:

1. PROCESS_INSTANCE
2. RUN_CNTL_ID
3. OPRID

We had to clear these three fields and also update the Earliest Change Date (EARLIEST_CHDT) back to the last reporting date. But instead of doing it through UPDATE SQL, we first deleted the row and then did an INSERT. 
Example:

DELETE  FROM PS_TL_TR_STATUS WHERE EMPLID = 'KU0045';

INSERT
INTO PS_TL_TR_STATUS
  (
    emplid,
    empl_rcd,
    ta_status,
    earliest_chgdt,
    prcs_thru_dt,
    lastupddttm,
    last_prcs_dttm,
    process_instance,
    oprid,
    run_cntl_id
  )
  VALUES
  (
    'KU0045',
    0,
    'Y',
    TO_DATE('2017-06-24','YYYY-MM-DD'),
    TO_DATE('2017-06-24','YYYY-MM-DD'),
    TO_TIMESTAMP('2017-06-24-12.50.57.000000','YYYY-MM-DD-HH24.MI.SS.FF'),
    TO_TIMESTAMP('2017-06-24-12.50.57.000000','YYYY-MM-DD-HH24.MI.SS.FF'),
    '0',
    ' ',
    ' '
  ) ;

Additionally, we also deleted run control data from below three tables:

DELETE FROM PS_TL_TA_RUNCTL  WHERE OPRID = 'PS';
DELETE FROM PS_TL_RUN_CTRL_GRP  WHERE OPRID = 'PS' AND EMPLID = 'KU0045';

DELETE FROM PS_PRCSRUNCNTL WHERE OPRID = 'PS';

After doing the above three steps we ran the time admin again and it went to success and the error was also gone.