GVT_IRR_SH_VW

(SQL View)
 Index Back

IRR Service History

selects only IRR reportable PAR's 7/23/03 Lee - ICE 608075000 - Replace seq_no with seq_no3. The new field has a numeric 3 attribute, versus a numeric 2 attribute.

SELECT Z.EMPLID , Z.EMPL_RCD , Z.GVT_IRR_TYPE , Z.EFFDT , Z.EFFSEQ , A.GVT_EFFDT , A.EFFSEQ , C.GVT_IRR_RET_FORM , B.DESCR25 , A.GVT_COMPRATE , A.GVT_LOCALITY_ADJ , A.GVT_STATUS_TYPE , A.GVT_PAY_PLAN , JOB.GRADE , A.GVT_STEP FROM PS_GVT_IRR_CONTROL Z , PS_JOB JOB , PS_JOB_USF A , PS_GVT_NOAC_TBL B , PS_RTRMNT_PLAN_TBL C WHERE Z.EMPLID=A.EMPLID AND Z.EMPL_RCD = A.EMPL_RCD AND A.EFFDT <= Z.EFFDT AND A.GVT_NOA_CODE=B.GVT_NOA_CODE AND JOB.ACTION=B.ACTION AND B.EFFDT= ( SELECT MAX(B1.EFFDT) FROM PS_GVT_NOAC_TBL B1 WHERE B1.ACTION=JOB.ACTION AND B1.GVT_NOA_CODE=A.GVT_NOA_CODE AND B1.EFFDT <= A.EFFDT) AND B.GVT_IRR_REPORTABLE='Y' AND C.PLAN_TYPE='70' AND C.BENEFIT_PLAN=A.GVT_RETIRE_PLAN AND C.EFFDT=( SELECT MAX(C1.EFFDT) FROM PS_RTRMNT_PLAN_TBL C1 WHERE C1.BENEFIT_PLAN = A.GVT_RETIRE_PLAN AND C1.PLAN_TYPE = '70' AND C1.EFFDT <= A.EFFDT) AND JOB.EMPLID = A.EMPLID AND JOB.EMPL_RCD = A.EMPL_RCD AND JOB.EFFDT = A.EFFDT AND JOB.EFFSEQ = A.EFFSEQ

  • Related Language Record: GVT_IRRH_LNG_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON_NAME

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    3 GVT_IRR_TYPE Character(1) VARCHAR2(1) NOT NULL IRR Type based on above NOAC
    A=Final Disability
    C=Correction
    D=Death
    M=Military Deposit
    N=Not Applicable
    P=Preliminary Disability
    R=Retirement
    S=Supplemental
    T=Transfer/Resignation
    4 EFFDT Date(10) DATE NOT NULL Effective Date
    5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    6 EFFDT_L1 Date(10) DATE Effective Date L1
    7 SEQ_NO3 Number(3,0) SMALLINT NOT NULL 7/23/03 Lee - ICE 608075000 - This is a generic sequence number field, with three integer positions.
    8 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
    9 DESCR25 Character(25) VARCHAR2(25) NOT NULL Description - Part 1
    10 GVT_COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Base Pay
    11 GVT_LOCALITY_ADJ Number(8,2) DECIMAL(7,2) NOT NULL Locality Adjustment
    12 GVT_STATUS_TYPE Character(3) VARCHAR2(3) NOT NULL Status Type
    CAN=Cancelled
    COM=Completed
    COR=Corrected
    IRR=IRR Reported
    WIP=Work-in-progress
    13 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan
    14 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
    15 GVT_STEP Character(2) VARCHAR2(2) NOT NULL US Federal Step