GPGB_PN_PYE_VW

(SQL View)
 Index Back

Employee Pension Details

View for payee details for qualifying pension scheme.

SELECT A.EMPLID ,A.EMPL_RCD ,A.PAY_ENTITY ,A.EFFDT ,A.GPGB_OPTIN_FLG ,A.GPGB_OPTIN_RE ,A.GPGB_OPTOUT_FLG ,A.GPGB_POSTPONE_DT ,A.GPGB_POSTPONE_RSN ,A.GPGB_AGGER_FLG ,A.GPGB_NOTPRC_FLG ,A.GPGB_OVRPEN_SCHEME ,A.GPGB_PEN_FACTOR FROM PS_GPGB_PEN_PAYEE A WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GPGB_PEN_PAYEE A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT > %Coalesce(( SELECT max(E.START_DATE) FROM PS_GPGB_EE_PENSION E,PS_GPGB_PN_EVNT_VW E1 WHERE E.EMPLID=A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E1.EMPLID=A.EMPLID AND E1.EMPL_rCD=A.EMPL_rCD AND E1.GPGB_PEN_EVENT IN ('ENROLLMENT','RE-ENROLMENT')),%datein('1990-01-01')) AND A1.EFFDT > %Coalesce(( SELECT max(PE.END_DATE) FROM PS_GPGB_EE_PENSION PE,PS_GPGB_PN_EVNT_VW PE1 WHERE PE.EMPLID=A.EMPLID AND PE.EMPL_RCD = A.EMPL_RCD AND PE1.EMPLID=A.EMPLID AND PE1.EMPL_rCD=A.EMPL_rCD AND PE1.GPGB_PEN_EVENT NOT IN ('ENROLLMENT','RE-ENROLMENT')),%datein('1990-01-01')))

  • Parent record: JOB

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

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 GPGB_OPTIN_FLG Character(1) VARCHAR2(1) NOT NULL Pension Opt In Flag for GBR
    0=NO
    1=YES
    6 GPGB_OPTIN_RE Character(1) VARCHAR2(1) NOT NULL Pension Opt In Flag for Re-Enrolment GBR
    0=NO
    1=YES
    7 GPGB_OPTOUT_FLG Character(1) VARCHAR2(1) NOT NULL Pension Opt Out Flag for GBR
    0=None
    1=Opt-Out
    2=Cease
    8 GPGB_POSTPONE_DT Date(10) DATE GBR postpone till date field
    9 GPGB_POSTPONE_RSN Character(4) VARCHAR2(4) NOT NULL Pension Postponement reason for GBR
    JDDF=Job Holder Deferment
    SDDF=Staging Date Deferment
    WRDF=Worker Deferement
    10 GPGB_AGGER_FLG Character(1) VARCHAR2(1) NOT NULL Aggregate Earning Flag for GBR
    0=NO
    1=YES
    11 GPGB_NOTPRC_FLG Character(1) VARCHAR2(1) NOT NULL Do not Process Flag for GBR
    0=NO
    1=YES
    12 GPGB_OVRPEN_SCHEME Character(10) VARCHAR2(10) NOT NULL Pension Scheme for GBR
    13 GPGB_PEN_FACTOR Number(2,0) SMALLINT NOT NULL Multiplication Factor GBR