GM_ELIG_PI3_VW

(SQL View)
 Index Back

List of eligible PIs

GM_ELIG_PI3_VW fetches the Eligible PIs emplid regardless of their status.

SELECT A.EMPLID , B.EFFDT ,%subrec(NAMEGBL_NPC_SBR,A) FROM PS_PERSONAL_DATA A , PS_GM_PERS_DATA B , PS_JOB C WHERE A.EMPLID=B.EMPLID AND A.EMPLID=C.EMPLID AND B.PROF_ELIG_IND='Y' AND B.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_GM_PERS_DATA F WHERE B.EMPLID = F.EMPLID AND F.EFFDT <= %CurrentDateIn ) AND C.EFFDT = ( SELECT MAX(G.EFFDT) FROM PS_JOB G WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.EFFDT <= %CurrentDateIn) AND C.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = C.EMPLID AND D.EMPL_RCD = C.EMPL_RCD AND D.EFFDT = C.EFFDT)

# 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