SS_ADVR_SRECOV2

(SQL View)
 Index Back

Enrollment Request Search

This view is used to search for existing an Enrollment Request for display/update.

SELECT X.EMPLID ,A.EMPLID ,A.ACAD_CAREER ,A.INSTITUTION ,A.STRM ,Y.OPRID FROM PS_STDNT_CAR_TERM A , PS_ACAD_CAR_TBL B , PS_INSTITUTION_TBL C , PS_TERM_TBL D , PS_STDNT_ADVR_HIST E , PS_PERS_NID X , PSOPRDEFN Y WHERE A.INSTITUTION = B.INSTITUTION AND A.ACAD_CAREER = B.ACAD_CAREER AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_ACAD_CAR_TBL B1 WHERE B1.INSTITUTION = B.INSTITUTION AND B1.ACAD_CAREER = B.ACAD_CAREER AND B1.EFFDT <= D.TERM_BEGIN_DT ) AND B.EFF_STATUS <> 'I' AND A.INSTITUTION = C.INSTITUTION AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_INSTITUTION_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.EFFDT <= D.TERM_BEGIN_DT) AND C.EFF_STATUS <> 'I' AND A.INSTITUTION = D.INSTITUTION AND A.ACAD_CAREER = D.ACAD_CAREER AND A.STRM = D.STRM AND A.EMPLID = E.EMPLID AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_STDNT_ADVR_HIST E1 WHERE E1.EMPLID=E.EMPLID AND E1.INSTITUTION=E.INSTITUTION AND E1.EFFDT <= %CurrentDateIn) AND (E.ADVISOR_ID = X.EMPLID OR E.COMM_PERS_CD = 'Y' AND E.COMMITTEE_ID = ( SELECT Y.COMMITTEE_ID FROM PS_COMMITTEE_MEMBR Y WHERE Y.INSTITUTION = E.INSTITUTION AND Y.COMMITTEE_ID = E.COMMITTEE_ID AND Y.EFFDT <= E.EFFDT AND Y.EMPLID = X.EMPLID AND Y.START_DT <= %CurrentDateIn AND Y.END_DT >= %CurrentDateIn )) AND X.EMPLID = Y.EMPLID

# 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

Prompt Table: PEOPLE_SRCH

3 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

Prompt Table: SS_STDNT_CAR_VW

4 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

5 STRM Character(4) VARCHAR2(4) NOT NULL Term

Prompt Table: SS_STDNT_TRM_VW

6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).