Wednesday, March 27, 2019

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);

No comments:

Post a Comment