How to retrive the effective dated empid from PS_JOB table

Question

I am new to peoplesoft. I can understand the effective date at a conceptual level, but I am still struggling with it in SQR. How do I retrieve the effective dated empid from PS_JOB table? How do I retrieve the valid empname from ps_names for the retrieved empid? Thanks in advance!

Are you asking about the syntax of a SQL query in SQR? – Bob Apr 03 '15 at 16:47 — Bob, Apr 03 '15 at 16:47

score 0 · Answer 1 · answered Apr 03 '15 at 13:09

the current name - from the ps_names_vw view you can use a similar where clause for the ps_job table.

select a.emplid,
       a.name_type,
       a.effdt,
       a.last_name,
       a.first_name,
       a.middle_name,
       a.name_prefix,
       a.name_suffix,
       a.last_name_srch,
       a.first_name_srch,
       a.name_display
  from ps_names a
 where a.effdt = (select max (b.effdt)
                    from ps_names b
                   where b.emplid     = a.emplid and
                         b.name_type  = a.name_type and
                         b.effdt     <= to_date (to_char (sysdate, 'YYYY-MM-DD'), 'YYYY-MM-DD'));

score 0 · Answer 2 · answered Apr 21 '15 at 14:22

Join names and job table with their respective effective date. here i have taken A, L, P, S employees assuming you need active employees' names only. if not , you can remove empl_status criterion. Also you need to specify the name_type in the main criterion , otherwise multiple rows may be returned for one emplid.

SELECT * FROM PS_NAMES N, PS_JOB J
WHERE N.EMPLID = J.EMPLID
AND N.EFFDT = (SELECT MAX(N1.EFFDT) FROM PS_NAMES N1
                WHERE N1.EMPLID= N.EMPLID
                AND N1.NAME_TYPE = N.NAME_TYPE
                AND N1.EFFDT <= SYSDATE)
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)
AND J.EMPL_STATUS IN ('A','L','P','S')

score 0 · Answer 3 · answered Jul 26 '15 at 15:20

The below query will fetch you the most recent data of an employee with Emplid 'XXX' from PS_JOB

SELECT * FROM PS_JOB J WHERE J.EMPLID = 'XXX' 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 < TRUNC(SYSDATE)) AND J.EMPL_RCD = ( SELECT MAX(J2.EMPL_RCD) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EFFDT = J.EFFDT ) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = J.EFFDT)
ORDER BY J.EMPLID , J.EMPL_RCD, J.EFFDT, J.EFFSEQ

How to retrive the effective dated empid from PS_JOB table

3 Answers3