Some organizations manage their workforces with positions.  A position specifies a job and a place on an organization chart, which can exist without a person or with more than one person.  Position management can give organizations more control over their structures, budgeting, and succession planning.

The Table

PS_POSITION_DATA is the table of positions with key fields POSITION_NBR and EFFDT.  In Peoplesoft HR 8.9, it has 107 fields that tells us almost everything about a position except who’s in it.  For that, we use the POSITION_NBR field in PS_JOB and PS_EMPLOYEES.  When we assign an employee to a position in the JOB_DATA component, Peoplesoft can use data from PS_POSITION_DATA to enter default values in PS_JOB.

MAX_HEAD_COUNT is an integer field in which we can specify how many individuals would be allowed to occupy this position at one time.  It suggests that a field called ACTUAL_HEAD_COUNT would be useful but strangely, this is a long character field in DERIVED_HR.

The View

A customer in my organization’s budget office asked for a way to see the actual headcount in queries she designed.  I created a view with all the fields of PS_POSITION_DATA and a three digit integer field called INCUMBENT.  It’s not practical for a position to have over one thousand incumbents, because every change saved in a row of PS_POSITION_DATA involves a Peoplecode search for and check of every incumbent in PS_JOB.

I loaded the POSITION_DATA record in Application Designer, changed its record type to “SQL View,” and saved it as POSITION_VW.  I chose not to save a copy of its Peoplecode.  Next, I inserted the INCUMBENT field at the end – yes, after the long character field DESCRLONG; it’s only a view – and saved it again.

I opened the SQL editor and entered the following code:

SELECT P.*,
(SELECT COUNT(*) FROM PS_EMPLOYEES E WHERE E.POSITION_NBR = P.POSITION_NBR)
FROM PS_POSITION_DATA P
WHERE P.EFFDT = (SELECT MAX(EFFDT)
FROM PS_POSITION_DATA
WHERE POSITION_NBR = P.POSITION_NBR
AND EFFDT <= SYSDATE)

It didn’t work.  Oracle replied, “ORA-01730: invalid number of column names specified.”  Although we can write an insert into command that uses a select * command for its values, we cannot do that with a create view command.  Instead, we have to list each column: P.POSITION_NBR, P.EFFDT, P.EFF_STATUS, and so on.

Historical Data

We can use PS_EMPLOYEES if we are only interested in the number of incumbents as of the current date.  That’s why I select the current PS_POSITION_DATA row as of the SYSDATE as well.  We cannot use PS_EMPLOYEES for historical data; it only includes people who were employees at the time it was generated, and it only includes their positions as of that time.  We would have to use a three-way self-join of PS_JOB where HR_STATUS = ‘A’ to get historical data.

If we wanted headcounts from an arbitrary date like July 1, 2009, we can use that date for our selection of PS_JOB.  Some people might want to use the latest row in PS_POSITION_DATA as of today, others might prefer the row that was current on July 1.  In either case, it may be better to write out the SQL specifically for the task.  If we try to use a view, we can calculate the headcount as of each effective date in PS_POSITION_DATA, but that’s too slow and not even the proper result.