SS_ADVR_SRECOV1

(SQL View)
 Index Back

Enrollment Request Search

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

SELECT X.EMPLID ,E.EMPLID ,Y.OPRID FROM PS_INSTITUTION_TBL C , PS_STDNT_ADVR_HIST E , PS_PERS_NID X , PSOPRDEFN Y WHERE E.INSTITUTION = C.INSTITUTION AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_INSTITUTION_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS <> 'I' 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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).