POSN_HISTORY3

(SQL View)
 Index Back

Position Hist-EE Exit Salaries

POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history. It selects Job records with effective dates before position exits to obtain exit salaries.

SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFFSEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency_Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.EmplID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))

  • Parent record: POSN_HISTORY2

    • # 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 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    7 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    8 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
    9 STEP Number(2,0) SMALLINT NOT NULL Step
    10 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
    11 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
    A=Annual
    B=Biweekly
    C=Contract
    D=Daily
    H=Hourly
    M=Monthly
    S=Semimonthly
    W=Weekly
    12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    13 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan

    Prompt Table: GVT_PAY_PLAN

    14 GVT_PAY_BASIS Character(2) VARCHAR2(2) NOT NULL Pay Basis
    BW=Bi-weekly
    FB=Fee Basis
    PA=Per Annum
    PD=Per Diem
    PH=Per Hour
    PM=Per Month
    PW=Piece Work
    SM=Semi-monthly
    SY=School Year
    WC=Without Compensation
    15 GVT_OCC_SERIES Character(4) VARCHAR2(4) NOT NULL Occupational Series
    16 MANAGER_LEVEL Character(2) VARCHAR2(2) NOT NULL Manager Level
    0=Chief Operating Officer
    1=Director
    2=Senior Officer
    3=Vice President
    4=Senior Manager
    5=Mid-Level Manager
    6=First-Line Manager
    7=Supervisor
    8=All Other Positions
    9=Non-Manager