GVT_CITIZEN_VW

(SQL View)
 Index Back

Government Citizenship View

This view is used to push data from GVT_CITIZENSHIP to CITIZENSHIP.

SELECT A.EMPLID , A.DEPENDENT_ID , A.COUNTRY , A.CITIZENSHIP_STATUS FROM PS_GVT_CITIZENSHIP A , PS_GVT_JOB B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND B.GVT_STATUS_TYPE IN ('COR', 'COM') AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_GVT_JOB D WHERE D.EMPLID = B.EMPLID AND D.EMPL_RCD = B.EMPL_RCD AND D.EFFDT <= %CurrentDateIn AND D.GVT_STATUS_TYPE IN ('COR', 'COM')) AND B.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_GVT_JOB C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = B.EFFDT AND C.GVT_STATUS_TYPE IN ('COM', 'COR'))

  • Parent record: GVT_CITIZENSHIP

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

    Prompt Table: PERSON

    2 DEPENDENT_ID Character(2) VARCHAR2(2) NOT NULL Dependent ID

    Prompt Table: DEPENDENT_VW

    3 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    4 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

    Prompt Table: CITIZEN_STS_TBL
    Set Control Field: COUNTRY