HR- PeopleSoft- SQL Query for Max Effective MAX (EFFDT) dated row from JOB table
Obtaining the Latest Effective Dated Row in the JOB Table (MAX(EFFDT) FROM JOB)
The effective date serves as a pivotal field across numerous tables within PeopleSoft, including control tables and transactional tables. It functions as a mechanism for tracking historical data, allowing users to revisit past records and examine how information appeared at specific points in time. Among these tables, PS_JOB stands out prominently. It serves as a cornerstone across a spectrum of PeopleSoft applications, spanning Core HR, Benefits, Payroll, Performance Management, and beyond.
For developers navigating their daily tasks, the PS_JOB table holds significant importance, often serving as a primary resource in various contexts. Chief among its utilities is the retrieval of the most recent effective dated row. Below is a commonly utilized query for achieving this purpose:
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)
No comments:
Post a Comment