GM_REP_VW

(SQL View)
 Index Back

Department Representative

Department Representative view

SELECT P.EMPLID ,D.EFFDT ,%subrec(NAMEGBL_SBR,P) FROM PS_PERS_DATA_EFFDT P , PS_GM_PERS_DATA D ,PS_JOB A WHERE P.EMPLID=D.EMPLID AND A.EMPLID = P.EMPLID AND A.EMPL_STATUS NOT IN ('T','D','I') AND D.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_GM_PERS_DATA F WHERE D.EMPLID = F.EMPLID) AND P.EFFDT = ( SELECT MAX(G.EFFDT) FROM PS_PERS_DATA_EFFDT G WHERE P.EMPLID=G.EMPLID) AND A.EMPL_RCD = ( SELECT MIN(R.EMPL_RCD) FROM PS_JOB R WHERE R.EMPLID= A.EMPLID AND R.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE H.EMPLID = A.EMPLID AND H.EMPL_RCD = R.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND R.EFFSEQ = ( SELECT MAX(Q.EFFSEQ) FROM PS_JOB Q WHERE Q.EMPLID = A.EMPLID AND Q.EMPL_RCD = R.EMPL_RCD AND Q.EFFDT = R.EFFDT) AND R.EMPL_STATUS NOT IN ('T','D','I') ) AND A.EFFDT =( SELECT MAX(H1.EFFDT) FROM PS_JOB H1 WHERE H1.EMPLID = A.EMPLID AND H1.EMPL_RCD = A.EMPL_RCD AND H1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(Q1.EFFSEQ) FROM PS_JOB Q1 WHERE Q1.EMPLID = A.EMPLID AND Q1.EMPL_RCD = A.EMPL_RCD AND Q1.EFFDT = A.EFFDT)

  • Parent record: PERSONAL_DATA

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    3 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Format for Country

    Prompt Table: COUNTRY_TBL

    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
    6 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
    1=Mr.
    2=Mrs.
    3=Ms.

    Prompt Table: NAME_PREFIX_TBL

    7 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix

    Prompt Table: NAME_SUFFIX_TBL

    8 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix
    9 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Suffix
    10 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title
    11 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    12 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    13 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    14 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Middle Name
    16 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    17 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
    18 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    19 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name