Friday, March 28, 2014

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

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

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

11 comments:

  1. HI, I need to return max eff date row satisfying a criteria from Job table.
    SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.CHANGE_AMT
    FROM PS_JOB A
    WHERE A.EMPLID = '00001'
    AND A.CHANGE_AMT <> 0.00

    Above will give all row for eff date that meets this criteria. How do I return only the latest/max row? I have access only to Query Manager (P/S 9.0)

    fah_begum@hotmail.com

    ReplyDelete
    Replies
    1. Keep the two criteria and just add subqueries on EFFDT and EFFSEQ to get the max row. to know how to do it in PS Query please visit How to Create a PS Query

      Delete
    2. Hi. Thank You. I tried below. This is not giving me any row. Most probably it is looking at the latest row of empl job tablw which does not satisfy the criteria.

      SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.CHANGE_AMT
      FROM PS_JOB A
      WHERE A.EFFDT = (SELECT B.EFFDT
      FROM PS_JOB B
      WHERE A.EMPLID = B.EMPLID
      AND B.EFFDT =
      (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
      WHERE B.EMPLID = B_ED.EMPLID
      AND B.EMPL_RCD = B_ED.EMPL_RCD)
      AND B.EFFSEQ =
      (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
      WHERE B.EMPLID = B_ES.EMPLID
      AND B.EMPL_RCD = B_ES.EMPL_RCD
      AND B.EFFDT = B_ES.EFFDT))
      AND A.EMPLID = '00001'
      AND A.CHANGE_AMT <> 0.00

      Delete
    3. Try This:

      SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.CHANGE_AMT
      FROM PS_JOB A
      WHERE A.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
      WHERE A.EMPLID = B_ED.EMPLID
      AND A.EMPL_RCD = B_ED.EMPL_RCD
      AND B_ED.EFFDT <= SYSDATE)

      AND A.EFFSEQ =
      (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
      WHERE A.EMPLID = B_ES.EMPLID
      AND A.EMPL_RCD = B_ES.EMPL_RCD
      AND A.EFFDT = B_ES.EFFDT)

      AND A.EMPLID = '00001'
      AND A.CHANGE_AMT <> 0.00

      Delete
    4. Thank you again. I could not mimic your code. I use query manager, so I need to select the criteria. I used subquery for EffDt of Job table A. Then in subquery, I used Expression to make the EffDt an aggregate one so that I can select Max on Field Edit menu. Then I added the criteria to join A with B, leaving the B.EffDt default criteria for subquery.

      SELECT A.EMPLID, A.CHANGE_AMT, MAX( TO_CHAR(A.EFFDT,'YYYY-MM-DD'))
      FROM PS_JOB A
      WHERE A.EMPLID = '00008'
      AND A.CHANGE_AMT <> 0.00
      AND A.EFFDT = (SELECT MAX( B.EFFDT)
      FROM PS_JOB B
      WHERE B.EFFDT =
      (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
      WHERE B.EMPLID = B_ED.EMPLID
      AND B.EMPL_RCD = B_ED.EMPL_RCD
      AND B_ED.EFFDT <= SYSDATE)
      AND B.EFFSEQ =
      (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
      WHERE B.EMPLID = B_ES.EMPLID
      AND B.EMPL_RCD = B_ES.EMPL_RCD
      AND B.EFFDT = B_ES.EFFDT)
      AND A.EMPLID = B.EMPLID
      AND A.EMPL_RCD = B.EMPL_RCD)
      GROUP BY A.EMPLID, A.CHANGE_AMT

      Delete
  2. if there are 2 entries in PS_PER_ORG_INST then we will get 2 rows right

    ReplyDelete
  3. Hi,

    obj : I need to get the max effective date row <= sysdate.

    what I have : if there is a future date for one particular employee I need to get the max effdt row which is <= sys date. how do I do that?

    ReplyDelete
    Replies
    1. I have three employees as follows:

      todays date : 3/9/2015

      1 2/9/2015
      1 4/9/2015
      2 1/9/2015
      3 3/9/2015

      desired output :
      1 2/9/2015
      2 1/9/2015
      3 3/9/2015

      some one please help me out.

      thanks in adv!

      Delete
  4. hi, I need sql to get maximum Annual Rt above their salary grade

    ReplyDelete
  5. Thank you for the detailed note. Furthermore, we can do this as shown below which increases the efficiency.
    select j.* from
    (select rank() over ( partition by emplid, empl_rcd order by effdt, effseq desc) as rank, j1.* from ps_job j1) j
    where j.rank =1

    ReplyDelete
  6. Hi,
    I am having similar issue when 2 rows are coming when searching for max effdt and max eff seq as there are 2 empl_rcd 0 and 1.
    There is no PS_PER_ORG_INST table in our database.
    Please suggest how to resolve this issue
    Thanks in advance

    ReplyDelete