INST_CMP_CRS_VW

(SQL View)
 Index Back

Instr Advisor View - Subjects

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 and the Course and Campus checkboxes are on. Instructors who are defined as Advisors Only are excluded.

SELECT C.ACAD_ORG , C.CAMPUS , C.CRSE_ID , C.CRSE_OFFER_NBR , E.ACAD_CAREER , E.STRM , B.EMPLID , B.EFFDT , C.SUBJECT , B.INSTR_AVAILABLE , B.INSTR_TYPE , B.EFF_STATUS , A.NAME , D.CAMPUS_ID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_SCC_CUR_NM_VW2 A , PS_INSTR_ADVISOR B , PS_INSTR_CRSE C , PS_SCC_PERS_SA_VW D , PS_TERM_TBL E WHERE B.INSTR_TYPE <> 'ADV' AND A.EMPLID = B.EMPLID AND B.EMPLID = C.EMPLID AND C.EMPLID = D.EMPLID AND B.EFFDT=C.EFFDT AND B.INSTITUTION=C.INSTITUTION AND B.INSTR_AVAILABLE = 'A' AND B.INSTITUTION = E.INSTITUTION AND B.EFFDT = ( SELECT MAX (EFFDT) FROM PS_INSTR_CRSE F WHERE F.EFFDT <= E.TERM_BEGIN_DT AND F.EMPLID = C.EMPLID AND F.INSTITUTION = C.INSTITUTION AND F.INSTR_CRSE_NBR = C.INSTR_CRSE_NBR)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

Prompt Table: ACAD_ORG_TBL

2 CAMPUS_INSTR Character(5) VARCHAR2(5) NOT NULL Campus Instructor
3 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
4 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
5 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
6 STRM Character(4) VARCHAR2(4) NOT NULL Term
7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
8 EFFDT Date(10) DATE Effective Date
9 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area
10 INSTR_AVAILABLE Character(2) VARCHAR2(2) NOT NULL Instructor Available
A=Available
S=Sabbatical
U=Unavailable
11 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
12 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
13 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
15 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
16 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name