Using Julian Date in PeopleSoft

Julian Date at any given point in time is defined as a Time-Interval ( Day Count + Fraction of Day) between the given time and 1st Jan, 4713 BC Greenwich noon, BC, Julian proleptic calendar -4712. 1st Jan, 4713 BC Greenwich noon is considered to be day 0 and the count starts from there.

How to get Julian Day (Day Count)?
If I run queries like :
a) SELECT TO_CHAR(SYSDATE,'J') FROM DUAL;
The return value is: 2454886
b) SELECT TO_DATE(2454886,'J') FROM DUAL;
The return value is: 02/23/2009 ( Today's Date)
b) SELECT TO_CHAR(SYSDATE,'J')-TO_CHAR(SYSDATE-1,'J') FROM DUAL;
Return Value: 1

Now, since we know how to use Julian Date concept, let’s proceed with an example.

We need to get all the Employees who have been transferred today. (Normally we see such business requirement to produce similar reports by nightly batch process).

We'll make some assumptions here, which may vary as per business need:
a) Transfer is considered when there is a change in DEPTID or SETID_DEPT (in Job record)
b) JOB.ACTION='XFR'
c) JOB.EFFDT=SYSDATE or JOB.ACTION_DT=SYSDATE AND JOB.EFFDT < SYSDATE

One of the simplest solution for above requirement is :

SELECT A.EMPLID

, A.EMPL_RCD

, A.EFFDT

, A.EFFSEQ

FROM PS_JOB A

, PS_JOB J

WHERE A.EFFDT= (

      SELECT MAX(A_ED.EFFDT)

      FROM PS_JOB A_ED

      WHERE A_ED.EMPLID=A.EMPLID

      AND A_ED.EMPL_RCD=A.EMPL_RCD

      AND A_ED.EFFDT<=%CurrentDateIn)

AND A.EFFSEQ=(

      SELECT MAX(A_EF.EFFSEQ)

      FROM PS_JOB A_EF

      WHERE A_EF.EMPLID=A.EMPLID

      AND A_EF.EMPL_RCD=A.EMPL_RCD

      AND A_EF.EFFDT=A.EFFDT)

AND A.ACTION='XFR'

AND (A.EFFDT=%CurrentDateIn

OR (A.ACTION_DT=%CurrentDateIn

AND A.EFFDT<%CurrentDateIn)) AND A.EMPLID=J.EMPLID AND A.EMPL_RCD=J.EMPL_RCD AND (TO_CHAR(J.EFFDT,'J')||J.EFFSEQ) = 

          ( SELECT MAX(TO_CHAR(J_ED.EFFDT,'J')||J_ED.EFFSEQ) 

            FROM PS_JOB J_ED 

            WHERE J.EMPLID = J_ED.EMPLID 

            AND J.EMPL_RCD = J_ED.EMPL_RCD  

            AND (TO_CHAR(J_ED.EFFDT,'J')||J_ED.EFFSEQ) < ( TO_CHAR(A.EFFDT,'J')||A.EFFSEQ))

AND (A.DEPTID<>J.DEPTID OR A.SETID_DEPT<>J.SETID_DEPT)

Here as you can see we first converted Effdt to Julian Date and then concatenated it with EffSeq. The resulting value was used as a boundry condition to get the most effective dated second last row.
Julian Date concept can be widely used in Effdt'd record to put a limiting criterion.