SSR_CLS_LOC_VW(SQL View) |
Index Back |
|---|---|
Class Location and Room |
| SELECT A.EMPLID , A.INSTITUTION , A.ACAD_CAREER , A.STRM , H.DESCR , I.DESCR , J.DESCR , B.CRSE_ID , B.SUBJECT , B.CATALOG_NBR , B.DESCR , C.XLATLONGNAME , A.CLASS_NBR , D.CLASS_MTG_NBR , D.START_DT , D.END_DT , D.MON , D.TUES , D.WED , D.THURS , D.FRI , D.SAT , D.SUN , D.MEETING_TIME_START , D.MEETING_TIME_END , E.DESCR , F.DESCR , E.SCC_LATITUDE , E.SCC_LONGITUDE , G.SETID , G.LOCATION , G.EFFDT FROM PS_STDNT_ENRL A , PS_CLASS_TBL B , PSXLATITEM C , PS_CLASS_MTG_PAT D , PS_BLDG_TBL E , PS_FACILITY_TBL F , PS_LOCATION_TBL G , PS_INSTITUTION_TBL H , PS_ACAD_CAR_TBL I , PS_TERM_TBL J WHERE A.INSTITUTION = B.INSTITUTION AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STRM = B.STRM AND A.CLASS_NBR = B.CLASS_NBR AND A.ENRL_STATUS_REASON IN ('ENRL','EWAT') AND C.FIELDNAME = 'SSR_COMPONENT' AND B.SSR_COMPONENT = C.FIELDVALUE AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PSXLATITEM C1 WHERE C.FIELDNAME = C1.FIELDNAME AND C.FIELDVALUE = C1.FIELDVALUE AND C1.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND B.CRSE_ID = D.CRSE_ID AND B.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND B.STRM = D.STRM AND B.SESSION_CODE = D.SESSION_CODE AND B.CLASS_SECTION = D.CLASS_SECTION AND B.INSTITUTION = F.SETID AND D.FACILITY_ID = F.FACILITY_ID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_FACILITY_TBL F1 WHERE F.SETID = F1.SETID AND F.FACILITY_ID = F1.FACILITY_ID AND F1.EFFDT <= %CurrentDateIn) AND F.EFF_STATUS = 'A' AND F.BLDG_CD = E.BLDG_CD AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_BLDG_TBL E1 WHERE E.BLDG_CD = E1.BLDG_CD AND E1.EFFDT <= %CurrentDateIn) AND E.EFF_STATUS = 'A' AND F.SETID = G.SETID AND F.LOCATION = G.LOCATION AND G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_LOCATION_TBL G1 WHERE G.SETID = G1.SETID AND G.LOCATION = G1.LOCATION AND G1.EFFDT <= %CurrentDateIn) AND G.EFF_STATUS = 'A' AND A.INSTITUTION = J.INSTITUTION AND A.ACAD_CAREER = J.ACAD_CAREER AND A.STRM = J.STRM AND A.INSTITUTION = I.INSTITUTION AND A.ACAD_CAREER = I.ACAD_CAREER AND I.EFFDT = ( SELECT MAX(I1.EFFDT) FROM PS_ACAD_CAR_TBL I1 WHERE I.INSTITUTION = I1.INSTITUTION AND I.ACAD_CAREER = I1.ACAD_CAREER AND I1.EFFDT <= J.TERM_BEGIN_DT) AND A.INSTITUTION = H.INSTITUTION AND H.EFFDT = ( SELECT MAX(H1.EFFDT) FROM PS_INSTITUTION_TBL H1 WHERE H.INSTITUTION = H1.INSTITUTION AND H1.EFFDT <= J.TERM_BEGIN_DT) |
| # | 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 | 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 |
| 4 | STRM | Character(4) | VARCHAR2(4) NOT NULL | Term |
| 5 | INSTITUTION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Institution |
| 6 | ACAD_CAREER_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Academic Career |
| 7 | TERM_DESCR30 | Character(30) | VARCHAR2(30) NOT NULL | Term |
| 8 | CRSE_ID | Character(6) | VARCHAR2(6) NOT NULL | Course ID |
| 9 | SUBJECT | Character(8) | VARCHAR2(8) NOT NULL | Subject Area |
| 10 | CATALOG_NBR | Character(10) | VARCHAR2(10) NOT NULL | Catalog Nbr |
| 11 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 12 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 13 | CLASS_NBR | Number(5,0) | INTEGER NOT NULL | Class Nbr |
| 14 | CLASS_MTG_NBR | Number(2,0) | SMALLINT NOT NULL | Class Meeting Pattern Nbr |
| 15 | START_DT | Date(10) | DATE | Start Date |
| 16 | END_DT | Date(10) | DATE | end date |
| 17 | MON | Character(1) | VARCHAR2(1) NOT NULL | Monday |
| 18 | TUES | Character(1) | VARCHAR2(1) NOT NULL | Tuesday |
| 19 | WED | Character(1) | VARCHAR2(1) NOT NULL | Wednesday |
| 20 | THURS | Character(1) | VARCHAR2(1) NOT NULL | Thursday |
| 21 | FRI | Character(1) | VARCHAR2(1) NOT NULL | Friday |
| 22 | SAT | Character(1) | VARCHAR2(1) NOT NULL | Saturday |
| 23 | SUN | Character(1) | VARCHAR2(1) NOT NULL | Sunday |
| 24 | MEETING_TIME_START | Time(15) | TIMESTAMP | Meeting Start Time |
| 25 | MEETING_TIME_END | Time(15) | TIMESTAMP | Meeting End Time |
| 26 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 27 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
| 28 | SCC_LATITUDE | Signed Number(12,7) | DECIMAL(10,7) NOT NULL | Latitude |
| 29 | SCC_LONGITUDE | Signed Number(12,7) | DECIMAL(10,7) NOT NULL | Longitude |
| 30 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 31 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 32 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |