GVT_IRR_CUR_VW

(SQL View)
 Index Back

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , B.NAME , C.NATIONAL_ID , R.GVT_IRR_RET_FORM FROM PS_GVT_JOB A , PS_GVT_NM_DT_VW B , PS_GVT_PERS_NID C , PS_RTRMNT_PLAN_TBL R WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GVT_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_GVT_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT) AND A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD AND B.EFFDT=( SELECT MAX(B1.EFFDT) FROM PS_GVT_NM_DT_VW B1 WHERE B1.EMPLID=A.EMPLID AND B1.EMPL_RCD=A.EMPL_RCD AND B1.EFFDT <= A.EFFDT) AND B.EFFSEQ=( SELECT MAX(B2.EFFSEQ) FROM PS_GVT_NM_DT_VW B2 WHERE B2.EMPLID=A.EMPLID AND B2.EMPL_RCD=A.EMPL_RCD AND B2.EFFDT = B.EFFDT) AND B.EMPLID=C.EMPLID AND B.EMPL_RCD=C.EMPL_RCD AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_GVT_PERS_NID C1 WHERE C1.EMPLID=A.EMPLID AND C1.EMPL_RCD=A.EMPL_RCD AND C1.EFFDT <= A.EFFDT) AND C.EFFSEQ=( SELECT MAX(C2.EFFSEQ) FROM PS_GVT_PERS_NID C2 WHERE C2.EMPLID=A.EMPLID AND C2.EMPL_RCD=A.EMPL_RCD AND C2.EFFDT = B.EFFDT) AND R.PLAN_TYPE='70' AND R.BENEFIT_PLAN=A.GVT_RETIRE_PLAN AND R.EFFDT=( SELECT MAX(R1.EFFDT) FROM PS_RTRMNT_PLAN_TBL R1 WHERE R1.BENEFIT_PLAN = A.GVT_RETIRE_PLAN AND R1.PLAN_TYPE = '70' AND R1.EFFDT <= A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
7 GVT_IRR_RET_FORM Character(1) VARCHAR2(1) NOT NULL IRR Retirement Form Number
C=CSRS (SF2806/SF2807)
F=FERS (SF3100/SF3103)
N=Not Applicable