Monitoring the Progress of the Global Payroll Process

31 January 2012

The Global Payroll process is a Cobol program. It is not unusual for the payroll process to take a number of hours to complete, depending on factors such as:

  • The number of payees
  • The complexity of the payroll element configuration
  • Infrastructure capability
  • The type of database used
  • The amount of performance tuning employed

It is difficult to determine the progress of the payroll process. However, there are a few methods by which we can monitor the status of the payroll calculation.

PS_GP_RUNCTL

The “PS_GP_RUNCTL” table is periodically updated by Global Payroll Process (GPPDPRUN) and can be used to get an indication of the process progress.

The following SQL script shows the key fields related to the process progress:


SELECT CAL_RUN_ID,

RUN_CNTL_ID,

C.PHASE_OPTN AS RUN_PHASE_OPTN,

B.PHASE_STEP AS RUN_PHASE_STEP,

NEXT_PGM,

NEXT_EMPLID,

TO_CHAR(PRC_SAVE_TS, ‘hh:mm:ss am’)

FROM PS_GP_RUNCTL A,

(SELECT FIELDVALUE, XLATLONGNAME AS PHASE_STEP FROM PSXLATITEM B WHERE FIELDNAME = ‘RUN_PHASE_STEP’) B,

(SELECT FIELDVALUE, XLATSHORTNAME AS PHASE_OPTN FROM PSXLATITEM B WHERE FIELDNAME = ‘RUN_PHASE_OPTN’) C

WHERE A.RUN_PHASE_STEP = B.FIELDVALUEAND A.RUN_PHASE_OPTN = C.FIELDVALUE

AND OPRID = ‘ENTER YOUR OPRID HERE’

The query returns the Phase, Step and Employee ID last saved.

The RUN_PHASE_STEP field shows the last completed step in the process. Steps include:

STEP DESCRIPTION
1 Run Phase Step Initial
2 Run Phase Step After Initial
3 Run Phase Step Initial End
4 Run Phase Before Iterative
5 Run Phase Step Iterative
6 Run Phase Step After Iter
7 Run Phase Step Iter End
8 Run Phase Step Before Canel
9 Run Phase Step Cancel
10 Run Phase Step After Cancel
11 Run Phase Step Cancel End
12 Run Phase Step Before Identif
13 Run Phase Step Identification
14 Run Phase Step After Identif
15 Run Phase Step Identifictn End
16 Run Phase Step TL Interface
17 Run Phase Step Before Calc
18 Run Phase Step Calculate
19 Run Phase Step After Calc
20 Run Phase Step Calc End
21 Run Phase Step Before Final
22 Run Phase Step Final
23 Run Phase Step Final End
30 Run Phase Step Before Ident 2

Monitor SQL

Another way to view the inner workings of the Payroll process is to use the “Monitor SQL…” function in “Oracle SQL Developer” application (assuming you are using an Oracle DB).  This function shows the details of SQL statements being called/executed by the Payroll process.

Details of each SQL Statement including Start and End time are shown.

Restart Information

Finally, a slightly simpler approach. The “Restart Information” link on the “Calculate Absence and Payroll” component is useful for identifying the Phase and Step of the process.

2 Comments | global payroll, peoplesoft, Uncategorized | Permalink
Posted by Paul Clarke

How to Uniquely Identify Payroll Elements

19 January 2012

One of the fundamentals of relational database design is the use of unique identifiers. So it’s a reasonable assumption that Payroll Elements can be easily identified by a unique ID of some sort. However nothing is that simple in PeopleSoft Global Payroll.

Each Element is assigned a numerical value called a Pin Number (PIN_NUM). Pin Numbers can be used to identify Elements within the scope of a single instance of PeopleSoft. However, when Elements are migrated to another instance they may be assigned a new Pin Number. Accordingly, Pin Numbers are not a reliable method of identifying Elements.

Each Element has a Pin Name (PIN_NM). The Pin Name field is the primary method by which users lookup/identify Elements using the PeopleSoft GUI. Whilst at first glance Pin Names seem to be unique, it is possible to have duplicate Pin Names in different countries. As a result it is not good practice to identify Elements using Pin Name.

Using the combination of the PIN_NM and COUNTRY fields to uniquely identify Elements is awkward at best. Fortunately PeopleSoft provide a solution. The Pin Code (PIN_CODE) field is a system maintained field that combines the Pin Name and Country Code.

Your choice of unique identifier may change depending on what you’re doing, however in general it is best to use the PIN_CODE. This is especially true when it comes to hard coding Elements references.

Leave a Comment » | configuration, global payroll, peoplesoft, Uncategorized | Permalink
Posted by Paul Clarke

Outsourcing & Off-shoring: The Cost of Regulatory Compliance and Risk Mitigation

22 December 2011

As any auditor will tell you: controls, processes and procedures are the secret to mitigating risk and ensuring regulatory compliance.

Organisations are like a garden shed, they start off clean and tidy, but have a tendency to accumulate clutter as successive projects, reorganisation and management changes add layer upon layer of controls and processes.

Controls, processes and procedures are not free. There are material costs such as documentation, division of duties, developing “idiot-proof” processes, auditing, checks and controls.

In addition to the direct cost, there is an indirect cost, as process-heavy organisations labour under the weight of procedural overhead. Loss in agility can have major implications on an organisations ability to compete in a rapidly changing environment. I have seen scenarios where a 5 minute fix that would provide major cost savings had to run the gauntlet of a 3 month SDLC.

It is no wonder why the business case for off-shoring and/or outsourcing is attractive to so many organisations. Outsourcing/Off-shoring solutions offer a fresh, clean and tidy garden shed.

However, would organisations be better served by cleaning out their own garden shed?

Leave a Comment » | Uncategorized | Tagged: , , , | Permalink
Posted by Paul Clarke

How to Un-Finalize a Global Payroll Calendar Group

21 September 2011

It is sometimes very useful to be able to un-finalize a pay period/Calendar Group while testing and/or debugging. However, there is no online functionality to enable this.

The following SQL Script can be used to reset a Calendar Group. After running the script it is important that you run the “Cancel” process using the online “Calculate Absence and Payroll” component.

Whilst I have never had an issue with this process, I would not recommend applying it to a production environment. And as usual: I take no responsibility for any issues resulting from the use of this process ;-)

–Reset Seg Stat table
UPDATE PS_GP_PYE_SEG_STAT
SET PYE_CALC_STAT = ’50′
WHERE CAL_RUN_ID = :1;
/
–Reset the 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 = ‘N’
WHERE CAL_RUN_ID = :1;
/
UPDATE PS_GP_CAL_RUN_DTL
SET CAL_FINAL_TS = NULL
WHERE CAL_RUN_ID = :1;
/
–Reset T&L
DELETE FROM PS_TL_RUNCTL_ACDT
WHERE CAL_RUN_ID = :1;
/
–Reverse GL
DELETE FROM PS_GP_GL_DATA
WHERE CAL_RUN_ID = :1;
/
–Reverse Payment Data
DELETE FROM PS_GP_PAYMENT
WHERE CAL_RUN_ID = :1;
/

Leave a Comment » | global payroll, payroll process, peoplesoft, unfinalize | Permalink
Posted by Paul Clarke

Calendar View: PS_SCH_CLND_VW

1 September 2011

The “PS_SCH_CLND_VW” view shows Schedule Details by calendar day, including Shift ID, Workday day and Scheduled Hours.

This is very useful for Arrays that require schedule details.  It is equally useful for analysis of payroll/absence results (e.g. Forecasting validation)

The following SQL shows an example of its use:

SELECT DUR,

SCHED_HRS

FROM PS_SCH_CLND_VW

WHERE SCHEDULE_ID = ‘KA8HRDAY’

AND DUR >= ‘01-SEP-2011’

AND DUR <= ’30-SEP-2011’

Leave a Comment » | Uncategorized | Tagged: , , , | Permalink
Posted by Paul Clarke

Running an Element Resolution Chain on the Forecasting Process

2 December 2010

Warning: This is an advanced trick. It is not for the faint hearted and I would definitely not do this in a production environment (i.e. “don’t try this at home…”).

The Element Resolution Chain is an invaluable tool for debugging Global Payroll rules. However, it is not possible to generate an Element Resolution Chain when forecasting… or is it?

Here’s a trick learned from the Godfather of PeopleSoft Global Payroll, Hubert Naim. You will need access to App Designer and a stiff drink.

1. Goto the Field Formula of the “DERIVED_GP > FCST_PB” field.

2. Change the “RUN_TRACE_OPTN” from “N” to “A”.

3. Go and run the forecast process online and “hey presto” you have an Element Resolution Chain.

4. Don’t forget to undo the PeopleCode change when you’re done.

5. Use the following SQL to view the results:

SELECT A.EMPLID,
A.EMPL_RCD,
A.GP_PAYGROUP,
A.CAL_RUN_ID,
C.RUN_TYPE,
A.ORIG_CAL_RUN_ID,
A.CAL_ID,
A.RSLT_SEG_NUM,
B.PIN_NM,
B.PIN_TYPE,
A.AUDIT_SORT_KEY,
A.PIN_CHAIN_SEQ_NUM,
A.INSTANCE_NUM,
TO_CHAR(A.SLICE_BGN_DT,’YYYY-MM-DD’),
TO_CHAR(A.SLICE_END_DT,’YYYY-MM-DD’),
A.CALC_RSLT_VAL,
A.CALC_ADJ_VAL,
A.CALC_RAW_VAL,
TO_CHAR(A.DATE_PIN_VAL,’YYYY-MM-DD’),
A.CHR_PIN_VAL
FROM PS_GP_AUDIT_TBL A,
PS_GP_PIN B,
PS_GP_CALENDAR C
WHERE A.PIN_NUM = B.PIN_NUM
AND A.GP_PAYGROUP = C.GP_PAYGROUP
AND A.CAL_ID = C.CAL_ID

4 Comments | global payroll, peoplesoft | Permalink
Posted by Paul Clarke

Documentation Tool

25 February 2010

One of the best ways to document GP Configuration is to put an explanation into the “Comments” of the Elements.  This makes it easier for those who follow in your foot sets to understand the configuration without looking in archives and searching through documents formatted by Project Managers who think paper grows on trees :-P .

However, I have never met a Project Manager that doesn’t want a hard copy Design Document for their customer to sign-off.

Element Comments are stored in the Database as a “CLOB”, making them difficult to extract.  The following script extracts Comments from the CLOB using the “TO_CHAR” function.

SELECT PIN_NM, DESCR, TO_CHAR(COMMENTS)
FROM PS_GP_PIN
WHERE LENGTH(TO_CHAR(COMMENTS)) > 0

Run this script from your SQL Client…  you are then just a cut and paste away from finishing your Design Document.

Leave a Comment » | Uncategorized | Permalink
Posted by Paul Clarke

“Ctrl J” in Google Chrome

17 February 2010

I haven’t posted for ages.  But I found a workaround that is worth sharing.

For those of you that like to use Google Chrome (I do, it’s fast). You may have found that the “Ctrl J” sequence doesn’t work, as this is the shortcut key for the Chrome downloads page.

I have found a simple workaround:

  1. Press & hold the “J” key
  2. Press & hold the “Ctrl” key
  3. Release the “J” key
  4. Release the “Ctrl” key

2 Comments | Uncategorized | Permalink
Posted by Paul Clarke

Preventing a formula from being processed in retro calculations

20 April 2009

It is often necessary to change the behaviour of a formula depending on wether it is being called from the current calendar or a retro calendar. This tip shows you a simple method for determining if a formula is being processed in the current calendar.

The “CURR PRD CAL ID” System Element returns the Calendar ID of the current period to be processed.

The “CAL ID” System Element returns the calendar ID from the calendar currently being processed

By comparing the “CURR PRD CAL ID” and “CAL ID” we can check if a formula is being processed in the current Calendar/Period, as apposed to a retro Calendar/Period. If the “CURR PRD CAL ID” and “CAL ID” are equal, the formula is being processed in the current Calendar.

The following formula excerpt shows this logic in an If construct:

IF SY[CURR PRD CAL ID] = SY[CAL ID] THEN
Process in current period only
ELSE
Process in retro periods only
ENDIF

1 Comment | Uncategorized | Tagged: , , , | Permalink
Posted by Paul Clarke

Setting up Mass Triggers

27 October 2008

Mass Triggers are new to version 8.9 and are useful in triggering retrospective calculation when setup tables are changed. For example, if an allowance rate is changed retrospectively Mass Triggers can be used create Iterative and Retro triggers for Employees who have the Earning assigned via EDAP.

Unfortunately, the process of creating Mass Triggers is poorly documented in PeopleBooks. This example should help fill the gaps.

To setup Mass Triggers:

Create SQL Object

The SQL object must return Employee ID, Employee Record Number and the Effective Date of the Trigger. In the following example the SQL Object uses parameters of the Earning Pin Number and Effective Date of the changes to the Earning to select Employees who have EDAP entries impacted by the changes. The SQL then returns the Employee ID, Employee Record and the greater of Effective Date of the Changes or EDAP Begin Date.

Create Trigger Event Definition

A Retro Event Definition is required for the configuration of Mass Triggers. An existing Event Definition can be used for this purpose, however in this scenario a new Definition has been configured.

Product Related > Global Payroll & Absence Mgmt
> Triggers > Retro Event Definitions

Create Mass Trigger Definition

The Component, Record Name, Retro Event Definition and selected Fields are used to define the Mass Trigger Definition.

Product Related > Global Payroll & Absence Mgmt
> Triggers > Mass Trigger Definitions

The SQL Object is bound to the Mass Trigger Tables using the parameters defined in the SQL Object. In this case the Pin Number and Effective Date of the config changes is passed.

Product Related > Global Payroll & Absence Mgmt
> Triggers > Mass Trigger Definitions

Create Mass Trigger Generation Logic

Finally, PeopleCode is required on the Mass Trigger record (in this scenario the “GP_ERN_DED” record). This step is not mentioned in PeopleBooks, but is nicely documented in the code comments of the “GP_MT_TRG_DFN” record (where else would you expect it ;-) .

The follow screen shows the PeopleCode used to generate Mass Triggers. The GenerateMassTriggers function takes Component and Record names as parameters.

6 Comments | configuration, global payroll, hrms, peoplesoft | Permalink
Posted by Paul Clarke

« Previous Entries
Follow

Get every new post delivered to your Inbox.