SSR_ADVISOR_VW2

(SQL View)
 Index Back

Advisor View

Lists a student's advisors + committe members

SELECT DISTINCT a.EMPLID , a.INSTITUTION , a.EFFDT , a.ADVISOR_ID , a.ADVISOR_ROLE , a.APPROVE_ENRLMT , a.ACAD_CAREER , a.ACAD_PROG , a.ACAD_PLAN , ' ' FROM PS_STDNT_ADVR_HIST a , PS_ACAD_PROG d WHERE a.COMM_PERS_CD='N' AND a.EMPLID = d.EMPLID AND a.ACAD_CAREER = d.ACAD_CAREER AND a.ACAD_PROG = d.ACAD_PROG AND a.INSTITUTION = d.INSTITUTION AND d.EFFDT = ( SELECT MAX(d1.EFFDT) FROM PS_ACAD_PROG d1 WHERE d1.EMPLID = d.EMPLID AND d1.ACAD_CAREER = d.ACAD_CAREER AND d1.STDNT_CAR_NBR = d.STDNT_CAR_NBR AND d1.EFFDT <= a.EFFDT) AND d.EFFSEQ = ( SELECT MAX(d2.EFFSEQ) FROM PS_ACAD_PROG d2 WHERE d2.EMPLID = d.EMPLID AND d2.ACAD_CAREER = d.ACAD_CAREER AND d2.STDNT_CAR_NBR = d.STDNT_CAR_NBR AND d2.EFFDT = d.EFFDT) AND d.PROG_STATUS = 'AC' UNION SELECT DISTINCT a.EMPLID , a.INSTITUTION , a.EFFDT , c.EMPLID , a.ADVISOR_ROLE , a.APPROVE_ENRLMT , a.ACAD_CAREER , a.ACAD_PROG , a.ACAD_PLAN , a.COMMITTEE_ID FROM PS_STDNT_ADVR_HIST a , PS_COMMITTEE b , PS_COMMITTEE_MEMBR c , PS_ACAD_PROG d WHERE a.COMM_PERS_CD='Y' AND a.COMMITTEE_ID=b.COMMITTEE_ID AND b.EFFDT = ( SELECT MAX(b1.EFFDT) FROM PS_COMMITTEE b1 WHERE b1.INSTITUTION=b.INSTITUTION AND b1.COMMITTEE_ID=b.COMMITTEE_ID AND b1.EFFDT<=a.EFFDT) AND b.EFF_STATUS='A' AND b.INSTITUTION=c.INSTITUTION AND b.COMMITTEE_ID=c.COMMITTEE_ID AND b.EFFDT=c.EFFDT AND c.START_DT<=a.EFFDT AND c.END_DT>=a.EFFDT AND a.EMPLID = d.EMPLID AND a.ACAD_CAREER = d.ACAD_CAREER AND a.ACAD_PROG = d.ACAD_PROG AND a.INSTITUTION = d.INSTITUTION AND d.EFFDT = ( SELECT MAX(d1.EFFDT) FROM PS_ACAD_PROG d1 WHERE d1.EMPLID = d.EMPLID AND d1.ACAD_CAREER = d.ACAD_CAREER AND d1.STDNT_CAR_NBR = d.STDNT_CAR_NBR AND d1.EFFDT <= a.EFFDT) AND d.EFFSEQ = ( SELECT MAX(d2.EFFSEQ) FROM PS_ACAD_PROG d2 WHERE d2.EMPLID = d.EMPLID AND d2.ACAD_CAREER = d.ACAD_CAREER AND d2.STDNT_CAR_NBR = d.STDNT_CAR_NBR AND d2.EFFDT = d.EFFDT) AND d.PROG_STATUS = 'AC'

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

Default Value: %date

4 ADVISOR_ID Character(11) VARCHAR2(11) NOT NULL This field is used to identify advisors assigned to a student.
5 ADVISOR_ROLE Character(4) VARCHAR2(4) NOT NULL Advisor Role
ADVR=Advisor
QEC=Qualifying Exam Committee
THES=Thesis Committee
6 APPROVE_ENRLMT Character(1) VARCHAR2(1) NOT NULL Must Approve Enrollment
7 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
8 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
9 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan
10 COMMITTEE_ID Character(6) VARCHAR2(6) NOT NULL Committee