GVT_PH_RET_STS

(SQL View)
 Index Back

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , B.NAME , B.FIRST_NAME , B.LAST_NAME , A.company ,A.JOBCODE ,A.POSITION_NBR ,A.ACTION ,A.ACTION_REASON ,A.GVT_WIP_STATUS ,A.GVT_STATUS_TYPE ,A.GVT_NOA_CODE ,A.GVT_LEG_AUTH_1 ,A.GVT_LEG_AUTH_2 ,A.GVT_WORK_SCHED ,A.STD_HOURS ,CASE WHEN (A.GVT_WORK_SCHED=( SELECT C.GVT_WORK_SCHED FROM PS_POSITION_DATA C WHERE A.POSITION_NBR=C.POSITION_NBR AND C.EFFDT=( SELECT MAX(E.EFFDT) FROM PS_POSITION_DATA E WHERE E.POSITION_NBR=C.POSITION_NBR AND E.EFFDT<=A.EFFDT) ) ) THEN 'N0' ELSE 'YES' END ,A.GVT_ANN_IND ,A.GVT_RETIRE_PLAN , CASE WHEN EXISTS( SELECT 1 FROM PS_GVT_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD AND a1.action='RET' AND A1.GVT_NOA_CODE IN ('307','308') AND A1.EFFDT<=%CurrentDateIn AND (A1.GVT_WIP_STATUS='PRO' OR A1.GVT_WIP_STATUS='COR') ) THEN 'RETIRED PHASED' ELSE CASE WHEN EXISTS( SELECT 1 FROM PS_GVT_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD AND A1.ACTION='PRT' AND A1.ACTION_REASON='RFE' AND A1.EFFDT<=%CurrentDateIn AND (A1.GVT_WIP_STATUS='PRO' OR A1.GVT_WIP_STATUS='COR') ) THEN 'RETURNED FULL' ELSE CASE WHEN EXISTS( SELECT 1 FROM PS_GVT_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD AND A1.ACTION='PRT' AND A1.ACTION_REASON='PRT' AND A1.EFFDT<=%CurrentDateIn AND (A1.GVT_WIP_STATUS='PRO' OR A1.GVT_WIP_STATUS='COR' )) THEN 'PHASED RETIREE' ELSE 'NA'END END END FROM PS_GVT_JOB A , PS_NAMES B WHERE A.GVT_NOA_CODE IN('615','616','307','308') AND (A.GVT_WIP_STATUS='PRO' OR A.GVT_WIP_STATUS='COR') AND A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_NAMES D WHERE D.EMPLID = B.EMPLID AND D.EFFDT <= %CurrentDateIn ) AND A.GVT_TRANS_NBR_SEQ=( SELECT MAX(E.GVT_TRANS_NBR_SEQ) FROM PS_GVT_JOB E WHERE E.EMPLID=A.EMPLID AND E.EFFDT=A.EFFDT AND E.GVT_TRANS_NBR=A.GVT_TRANS_NBR AND E.ACTION=A.ACTION AND E.ACTION_REASON=A.ACTION_REASON AND E.GVT_NOA_CODE=A.GVT_NOA_CODE)

# 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 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

10 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Prompt Table: POSITION_DATA

11 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

12 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

Prompt Table: ACTN_REASON_TBL

13 GVT_WIP_STATUS Character(3) VARCHAR2(3) NOT NULL Work-in-Progress Status

Prompt Table: GVT_WIP_STS_VW

14 GVT_STATUS_TYPE Character(3) VARCHAR2(3) NOT NULL Status Type
CAN=Cancelled
COM=Completed
COR=Corrected
IRR=IRR Reported
WIP=Work-in-progress
15 GVT_NOA_CODE Character(3) VARCHAR2(3) NOT NULL Nature of Action Code

Prompt Table: GVT_NOAC_TBL

16 GVT_LEG_AUTH_1 Character(3) VARCHAR2(3) NOT NULL Legal Authority (1)

Prompt Table: GVT_AUTH_VAL_VW

17 GVT_LEG_AUTH_2 Character(3) VARCHAR2(3) NOT NULL Legal Authority (2)

Prompt Table: GVT_AUTH_VAL_VW

18 GVT_WORK_SCHED Character(1) VARCHAR2(1) NOT NULL Work Schedule
B=Baylor Plan
F=Full Time
G=Full Time Seasonal
I=Intermittent
J=Intermittent-Seasonal
P=Part Time
Q=Part-Time Seasonal
R=Part-Time Phased Retiree
S=Part-Time Job Sharer
T=Part-Time Seasonal Job Sharer

Default Value: F

19 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
20 UPDATE_POSITION Character(1) VARCHAR2(1) NOT NULL Update forsitionn for Work Sch
21 GVT_ANN_IND Character(1) VARCHAR2(1) NOT NULL Annuitant Indicator
1=Reempl Ann-CS
2=Ret Officer
3=Ret Enlisted
4=Ret Off/Reempl Ann-CS
5=Ret Enl/Reempl Ann-CS
6=CS-No Reduction
7=Ret Off/CS-No Reduc
8=Ret Enl/CS-No Reduc
9=Not Applicable
A=Reempl Ann-FE
B=Former Ann-FE
C=Ret Off/Reempl Ann-FE
D=Ret Off/Former Ann-FE
E=Ret Enl/Reempl Ann-FE
F=Ret Enl/Former Ann-FE
G=FE - No Reduction
H=Ret Off/FE-No Reduc
J=Ret Enl/FE-No Reduc
M=Former FERS Phased Retiree
N=FERS Phased Retiree Officer
P=Phased Ret/Former Enlisted FE
W=Phased Retiree/Former CS
X=CSRS Phased Retiree Officer
Y=Phased Ret/Former Enlisted CS

Default Value: 9

Prompt Table: GVT_ANNUITY_VW

22 GVT_RETIRE_PLAN Character(2) VARCHAR2(2) NOT NULL Retirement Plan

Default Value: K

Prompt Table: GVT_RETIRE_VW

23 GVT_PH_RET_STATUS Character(20) VARCHAR2(20) NOT NULL PHASED RETIRE STATUTS