INSTR_NO_ADV_VW

(SQL View)
 Index Back

Instr Advisor View - No Adv

Instructor Edit in the Academic Organization Table indicates if Class Instructor ID should prompt on Personal Data or Instructor Advisor Table. This view is used as the prompt table for Class Instructor ID if Instructor Edit is set to Instructor Advisor Table. Instructors who are defined as Advisors Only are excluded.

SELECT DISTINCT C.OPRCLASS , F.CRSE_ID , F.CRSE_OFFER_NBR , G.CAMPUS , B.INSTR_AVAILABLE , B.EMPLID , B.EFFDT , B.INSTR_TYPE , B.EFF_STATUS , B.ACAD_ORG , C.COUNTRY , C.NATIONAL_ID_TYPE , D.DESCRSHORT , C.NATIONAL_ID , C.NATIONAL_ID_MSK , A.SEX , A.NAME , A.CAMPUS_ID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_PEOPLE_SRCH A , PS_INSTR_ADVISOR B , PS_PERS_NID_MSK_VW C , PS_NID_TYPE_TBL D , PS_INSTR_CRSE E , PS_CRSE_OFFER F , PS_CAMPUS_TBL G WHERE B.INSTR_TYPE <> 'ADV' AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND D.COUNTRY = C.COUNTRY AND D.NATIONAL_ID_TYPE = C.NATIONAL_ID_TYPE AND B.EMPLID = E.EMPLID AND B.INSTITUTION = E.INSTITUTION AND B.EFFDT = E.EFFDT AND (E.SUBJECT = ' ' OR (E.SUBJECT = F.SUBJECT AND E.CRSE_ID = ' ') OR (E.CRSE_ID = F.CRSE_ID AND E.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR)) AND E.INSTITUTION = G.INSTITUTION AND (E.CAMPUS = ' ' OR E.CAMPUS = G.CAMPUS)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
3 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
4 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus
5 INSTR_AVAILABLE Character(2) VARCHAR2(2) NOT NULL Instructor Available
A=Available
S=Sabbatical
U=Unavailable
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
7 EFFDT Date(10) DATE Effective Date
8 INSTR_TYPE Character(4) VARCHAR2(4) NOT NULL Instructor Type
ADJ=Adjunct Professor
ADV=Advisor Only
ASSC=Associate Professor
ASST=Assistant Professor
EMRT=Emeritus
INST=Instructor
LECT=Lecturer
PROF=Professor
TA=Teaching Assistant
UNDF=Undefined
VLEC=Visiting Lecturer
VPRF=Visiting Professor
9 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
10 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

Prompt Table: ACAD_ORG_TBL

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 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
15 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
16 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
17 NAME Character(50) VARCHAR2(50) NOT NULL Name
18 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
19 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
20 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name