Max Effective Date and %EffdtCheck

Below is an example to get the max effective dated row.

   SELECT * FROM PS_TEST T
  WHERE T.EMPLID = '9999999'
    AND T.EFFDT = (SELECT MAX(T1.EFFDT)
                    FROM PS_TEST T1
                   WHERE T1.EMPLID = T.EMPLID
                     AND T1.EFFDT <= SYSDATE)
    AND T.EFFSEQ = (SELECT MAX(T2.EFFSEQ)
                    FROM PS_TEST T2
                   WHERE T2.EMPLID = T.EMPLID
                     AND T2.EMPL_RCD = T.EMPL_RCD
                     AND T2.EFFDT = T.EFFDT)

====================================================================
Also the metasql for this functionality is %EffdtCheck

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND %EffdtCheck(JOB, JOB_ES, %CurrentDateIn)

expands into this:

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND JOB_ES.EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB JOB
WHERE JOB.EMPLID=JOB_ES.EMPLID
AND JOB.EMPL_RCD=JOB_ES.EMPL_RCD
AND JOB.EFFSEQ=JOB_ES.EFFSEQ /* this shouldn't be used in EFFDT sub-query */
AND JOB.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

This information was taken directly from the 2 urls below.  They describe it in 

greater detail.

http://peoplesoft.wikidot.com/effdtcheck
http://peoplesoftconcept.blogspot.com/2014/03/sql-query-for-max-effective-max-effdt.html