SQL Query for Max Effective MAX (EFFDT) dated row from JOB table

Maximum Effective dated row in JOB table (MAX (EFFDT) FROM JOB)

Effective date is a key field in most of the tables (Control Tables, Transaction Tables etc…) and it’s used to track the history data in PeopleSoft. In other words, tables which have effective dated transactions provide us the opportunity to go back to history row and check how the data looked like as of a particular past effective date.

However, today the table that we are talking about is PS_JOB and it's apparently one of them. Remember, this is the key table used across almost all the PeopleSoft applications (Core HR, Benefits, Payroll, Performance Management etc…)

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft
Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
FTE For Multiple Jobs in PoeopleSoft
PeopleSoft Set Control Field Concept and Tableset Sharing 
Understanding Future dated security in PeopleSoft 
PeopleSoft 9.1 Person Model
Creating Query Report with PS Query in PeopleSoft
PeopleSoft HRMS Online Training

SQL Query for Max Effective dated row from JOB table

A developer in their day to day work uses this table a lot in various ways. But the most frequent use is getting the Maximum effective dated row and in fact below is the query I have seen being used mostly:

   SELECT * FROM PS_JOB J
  WHERE J.EMPLID = 'NZ11SN28'
    AND J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT <= SYSDATE)
    AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                    FROM PS_JOB J2
                   WHERE J2.EMPLID = J.EMPLID
                     AND J2.EMPL_RCD = J.EMPL_RCD
                     AND J2.EFFDT = J.EFFDT)

  

   Well, this SQL will work in many cases but not in few. Let’s understand how.

   An employee NZ11SN28 has following rows in PS_JOB table: 

You can see the max effective dated row is with EFFDT - 06-FEB-14 so, if we just run the SQL we saw earlier, then will see what we get:

 

So far so good..! The result is correct however, you should also note that in this case employee has only one job record (EMPL_RCD = 0) and that is substantive job record as we know.

But, what if employee has more than one job records, will the same SQL still work?  Let’s check

Before we get into that you must understand the Person Model in PeopleSoft and that has been explained here PeopleSoft 9.1 Person Model

Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
How the Various Date Fields on Employment Data Page are Updated
PeopleSoft 9.1 Person Model
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
Understanding Component Interface in PeopleSoft 
How the FTE is Calculated in PeopleSoft
Business Unit, Company and Regulatory Region in PeopleSoft

Joining PS_PER_ORG_INST with PS_JOB in PeopleSoft

Suppose the employee NZ11SN28 gets an additional assignment under the same Employment Instance (PER_ORG = ‘EMP’ and ORG_INSTANCE_ERN = 0) and in the same Company/Regulatory Region hence, HR user creates an additional assignment (For more information on how to create Additional Assignment please visit PeopleSoft 9.1 Person Model).

   Now the employee will have following records in PS_JOB table  

The requirement is still same, get the Max effective dated row for the substantive job (EMPL_RCD = 0) so let’s again run the same SQL and see what we get:

  

Oops..!! you certainly expected the row with EFFDT = 6-Feb-14 but not the other one hence I would just imply that this SQL will not work in the cases where an employee has more than one Job record.

However, if the additional assignment is in different company or region then we can still add this criteria in the WHERE clause though still not a good idea but if it’s in the same company or region then it won’t work.

Although we certainly have a solution but I have seen many developers add Max Effective dated criteria on the field EMPL_RCD too hoping to get correct result which is absolutely something you shouldn’t do.

So what should we do now?   

Not to worry, we can bring in the table PS_PER_ORG_INST in the scene to get the correct result i.e. we can join PS_JOB with PS_PER_ORG_INST. The reason we are joining PS_PER_ORG_INST is this table tracks the organization instances and hence we can easily identify which job record (EMPL_RCD) in PS_JOB table created for the Organizational Instance (ORG_INSTANCE_ERN) in PS_PER_ORG_INST table. This job record is also called “Substantive Job” record.

Join both the tables and run it as shown below:

  

As you can see, once we join PS_PER_ORG_INST table with PS_JOB then the SQL looks for the Max effective dated row among those under substantive job record.

So the point is, whether or not we have multiple job records for employees we should always join PS_PER_ORG_INST with PS_JOB to get correct result.

SQL Query to get second highest Max Effective dated (MAX (EFFDT)) row from JOB table

An employee NZ11SN28 has following rows in PS_JOB table:

The second highest max effective dated row is with EFFDT 31-JAN-14 so here is the SQL which will get us this row:

SELECT * FROM PS_JOB J
 WHERE J.EMPLID = 'NZ11SN28'
   AND J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT < (SELECT MAX(J3.EFFDT)
                                       FROM PS_JOB J3
                                      WHERE J3.EMPLID = J.EMPLID
                                        AND J3.EMPL_RCD =    J.EMPL_RCD
                                        AND J3.EFFDT <= SYSDATE)
                  )
  
   AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                     FROM PS_JOB J2
                    WHERE J2.EMPLID = J.EMPLID
                      AND J2.EMPL_RCD = J.EMPL_RCD
                      AND J2.EFFDT = SYSDATE)

 Lets test this query:

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field
Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft 
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft 
'Override Position Data' and 'Use Position Data' Option in Job Data Page 
How the Various Date Fields on Employment Data Page are Updated 
Process Security in PeopleSoft 

Want to learn PeopleSoft technical module thoroughly? I have several videos with total duration of over 50 hours.

Following is the link to the YouTube videos Technical
Click here to see course contents

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training