POSN_HISTORY2

(SQL View)
 Index Back

Position Hist-EE Exit Dates

POSN_HISTORY2 is the second of three nested views which retrieve position incumbent history. It selects Job records where employees have exited positions.

SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt , B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND B.EffDt<=%CurrentDateIn AND (C.EffDt > A.Position_Entry_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Position_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))

  • Parent record: POSN_HISTORY

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    2 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    5 POSITION_END_DT Date(10) DATE Position End Date
    6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    7 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Prompt Table: ACTION_TBL