SSR_SSADVEE_VW

(SQL View)
 Index Back

Advisee Search View

Lists students assigned to an advisor.

SELECT DISTINCT a.OPRCLASS , b.ADVISOR_ID , a.EMPLID , a.NAME , a.SEX , a.BIRTHDATE , a.BIRTHDATE_MSK , a.CAMPUS_ID , a.NATIONAL_ID , a.NATIONAL_ID_MSK , a.NID_COUNTRY , a.NATIONAL_ID_TYPE , a.NID_DESCRSHORT , a.LAST_NAME_SRCH , a.FIRST_NAME_SRCH , a.NAME_DISPLAY FROM PS_STDNT_SRCH a , PS_STDNT_ADVR_HIST b , PS_ACAD_PROG c WHERE a.EMPLID=b.EMPLID AND b.COMM_PERS_CD='N' AND b.ADVISOR_ROLE='ADVR' AND b.EFFDT = ( SELECT MAX(EFFDT) FROM PS_STDNT_ADVISOR b1 WHERE b1.EMPLID = b.EMPLID AND b1.INSTITUTION = b.INSTITUTION AND b1.EFFDT <= %CurrentDateIn) AND b.EMPLID = c.EMPLID AND b.ACAD_CAREER = c.ACAD_CAREER AND b.ACAD_PROG = c.ACAD_PROG AND c.EFFDT = ( SELECT MAX(c1.EFFDT) FROM PS_ACAD_PROG c1 WHERE c1.EMPLID = c.EMPLID AND c1.ACAD_CAREER = c.ACAD_CAREER AND c1.STDNT_CAR_NBR = c.STDNT_CAR_NBR AND c1.EFFDT <= %CurrentDateIn) AND c.EFFSEQ = ( SELECT MAX(c2.EFFSEQ) FROM PS_ACAD_PROG c2 WHERE c2.EMPLID = c.EMPLID AND c2.ACAD_CAREER = c.ACAD_CAREER AND c2.STDNT_CAR_NBR = c.STDNT_CAR_NBR AND c2.EFFDT = c.EFFDT) AND c.PROG_STATUS = 'AC' UNION SELECT DISTINCT a.OPRCLASS , d.EMPLID , a.EMPLID , a.NAME , a.SEX , a.BIRTHDATE , a.BIRTHDATE_MSK , a.CAMPUS_ID , a.NATIONAL_ID , a.NATIONAL_ID_MSK , a.NID_COUNTRY , a.NATIONAL_ID_TYPE , a.NID_DESCRSHORT , a.LAST_NAME_SRCH , a.FIRST_NAME_SRCH , a.NAME_DISPLAY FROM PS_STDNT_SRCH a , PS_STDNT_ADVR_HIST b , PS_COMMITTEE c , PS_COMMITTEE_MEMBR d , PS_ACAD_PROG e WHERE a.EMPLID=b.EMPLID AND b.COMM_PERS_CD='Y' AND b.ADVISOR_ROLE='ADVR' AND b.EFFDT = ( SELECT MAX(EFFDT) FROM PS_STDNT_ADVISOR b1 WHERE b1.EMPLID = b.EMPLID AND b1.INSTITUTION = b.INSTITUTION AND b1.EFFDT <= %CurrentDateIn) AND b.COMMITTEE_ID=c.COMMITTEE_ID AND c.EFFDT = ( SELECT MAX(c1.EFFDT) FROM PS_COMMITTEE c1 WHERE c1.INSTITUTION=c.INSTITUTION AND c1.COMMITTEE_ID=c.COMMITTEE_ID AND c1.EFFDT<=%CurrentDateIn) AND c.EFF_STATUS='A' AND c.INSTITUTION=d.INSTITUTION AND c.COMMITTEE_ID=d.COMMITTEE_ID AND c.EFFDT=d.EFFDT AND d.START_DT<=%CurrentDateIn AND d.END_DT>=%CurrentDateIn AND b.EMPLID = e.EMPLID AND b.ACAD_CAREER = e.ACAD_CAREER AND b.ACAD_PROG = e.ACAD_PROG AND e.EFFDT = ( SELECT MAX(e1.EFFDT) FROM PS_ACAD_PROG e1 WHERE e1.EMPLID = e.EMPLID AND e1.ACAD_CAREER = e.ACAD_CAREER AND e1.STDNT_CAR_NBR = e.STDNT_CAR_NBR AND e1.EFFDT <= %CurrentDateIn) AND e.EFFSEQ = ( SELECT MAX(e2.EFFSEQ) FROM PS_ACAD_PROG e2 WHERE e2.EMPLID = e.EMPLID AND e2.ACAD_CAREER = e.ACAD_CAREER AND e2.STDNT_CAR_NBR = e.STDNT_CAR_NBR AND e2.EFFDT = e.EFFDT) AND e.PROG_STATUS = 'AC'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 ADVISOR_ID Character(11) VARCHAR2(11) NOT NULL This field is used to identify advisors assigned to a student.
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
6 BIRTHDATE Date(10) DATE Date of Birth
7 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
8 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
9 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
10 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
11 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
12 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type

Prompt Table: NID_TYPE_TBL
Set Control Field: NID_COUNTRY

13 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
14 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
15 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country