SSR_ADV_PREF_VW (SQL View)	Index Back
Advisee Search View Lists students assigned to an advisor. This view is a clone of view SSR_ADVISEE_VW where HCR_PERSON_NM_I is replaced by SCC_PRFPRIMNMVW to fetch Preferred Name if present else Primary name.

SSR_ADV_PREF_VW

(SQL View)

Advisee Search View

Lists students assigned to an advisor. This view is a clone of view SSR_ADVISEE_VW where HCR_PERSON_NM_I is replaced by SCC_PRFPRIMNMVW to fetch Preferred Name if present else Primary name.

SELECT DISTINCT b.ADVISOR_ID , a.EMPLID , a.NAME , a.LAST_NAME_SRCH , a.FIRST_NAME_SRCH , a.NAME_DISPLAY , b.ADVISOR_ROLE FROM PS_SCC_PRFPRIMNMVW a , PS_STDNT_ADVR_HIST b , PS_ACAD_PROG c WHERE a.EMPLID=b.EMPLID AND b.COMM_PERS_CD='N' 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 d.EMPLID , a.EMPLID , a.NAME , a.LAST_NAME_SRCH , a.FIRST_NAME_SRCH , a.NAME_DISPLAY , b.ADVISOR_ROLE FROM PS_SCC_PRFPRIMNMVW 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.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 ADVISOR_ID Character(11) VARCHAR2(11) NOT NULL This field is used to identify advisors assigned to a student.

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 NAME Character(50) VARCHAR2(50) NOT NULL Name

4 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

5 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name

6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

7 ADVISOR_ROLE Character(4) VARCHAR2(4) NOT NULL Advisor Role
ADVR=Advisor
QEC=Qualifying Exam Committee
THES=Thesis Committee

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	ADVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	This field is used to identify advisors assigned to a student.
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
4	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
5	FIRST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	First Name
6	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
7	ADVISOR_ROLE	Character(4)	VARCHAR2(4) NOT NULL	Advisor Role ADVR=Advisor QEC=Qualifying Exam Committee THES=Thesis Committee