SSR_CRSOFR_ESVW(SQL View) |
Index Back |
---|---|
Course Offer ES Record ViewCourse Offer Elastic Search Record View |
SELECT DISTINCT O.CRSE_ID , O.CRSE_OFFER_NBR , O.EFFDT , O.SUBJECT , O.SSR_CRSE_TYPOFF_CD , GBX.XLATLONGNAME , C.RQMNT_DESIGNTN , CTX.XLATLONGNAME , AC.DESCR , COX.XLATLONGNAME , O.CAMPUS , ORG.DESCR , ATT.CRSE_ATTR , ATT.CRSE_ATTR_VALUE , U.DESCR , C.UNITS_MINIMUM , C.UNITS_MAXIMUM , CASE WHEN ATT.SSR_CLSCFG_ATTRVAL = 'Y' THEN ATT.DESCR1 %Concat '/' %Concat ATT.DESCR2 ELSE ATT.DESCR1 END , O.INSTITUTION , O.ACAD_GROUP , O.SUBJECT %Concat '/' %Concat S.DESCR , O.CATALOG_NBR , C.DESCR , O.SUBJECT %Concat ' ' %Concat O.CATALOG_NBR , C.COURSE_TITLE_LONG , O.COURSE_APPROVED , O.EFFDT , T.DESCR , T.DESCR , CASE WHEN O.SSR_CRSE_TYPOFF_CD <> ' ' THEN 'Y' ELSE 'N' END , C.GRADING_BASIS , O.OEE_IND , O.ACAD_CAREER , CO.SSR_COMPONENT , O.ACAD_ORG , TPC.DESCR , TPC.DESCR , CASE WHEN C.UNITS_MINIMUM = C.UNITS_MAXIMUM THEN CASE WHEN C.UNITS_MINIMUM = 1 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END ELSE CASE WHEN C.UNITS_MAXIMUM = 1 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END END /* , CASE WHEN C.UNITS_MINIMUM = C.UNITS_MAXIMUM THEN CASE WHEN C.UNITS_MINIMUM > 1 OR C.UNITS_MINIMUM = 0 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT END ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END AS DESCR4 */ , U.DESCR FROM PS_CRSE_OFFER O LEFT OUTER JOIN PS_CRSE_TOPICS TPC ON O.CRSE_ID = TPC.CRSE_ID AND TPC.EFFDT = O.EFFDT LEFT OUTER JOIN PS_SSR_BC_UNITS_VW U ON O.CRSE_ID = U.CRSE_ID AND O.EFFDT = U.EFFDT LEFT OUTER JOIN PS_SSR_CRS_ATTR_VW ATT ON O.CRSE_ID = ATT.CRSE_ID AND O.CRSE_OFFER_NBR = ATT.CRSE_OFFER_NBR AND O.EFFDT = ATT.EFFDT , PS_CRSE_CATALOG C , PSXLATITEM GBX , PS_CRSE_COMPONENT CO , PS_ACAD_CAR_TBL AC , PSXLATITEM COX , PSXLATITEM CTX , PS_ACAD_ORG_TBL ORG , PS_SUBJECT_TBL S , PS_SSR_CRS_TYPF_VW T , PSMSGCATDEFN UNS , PSMSGCATDEFN UNT WHERE O.CRSE_ID = C.CRSE_ID AND O.EFFDT = C.EFFDT AND O.COURSE_APPROVED = 'A' AND O.CATALOG_PRINT = 'Y' AND C.EFF_STATUS = 'A' AND GBX.FIELDNAME = 'GRADING_BASIS' AND GBX.FIELDVALUE = C.GRADING_BASIS AND GBX.EFFDT = ( SELECT MAX(GBX2.EFFDT) FROM PSXLATITEM GBX2 WHERE GBX.FIELDNAME = GBX2.FIELDNAME AND GBX.FIELDVALUE = GBX2.FIELDVALUE AND GBX2.EFFDT <= %CurrentDateIn) AND C.CRSE_ID = O.CRSE_ID AND CO.CRSE_ID = O.CRSE_ID AND AC.INSTITUTION = O.INSTITUTION AND AC.ACAD_CAREER = O.ACAD_CAREER AND AC.EFFDT = ( SELECT MAX(AC2.EFFDT) FROM PS_ACAD_CAR_TBL AC2 WHERE AC.INSTITUTION = AC2.INSTITUTION AND AC.ACAD_CAREER = AC2.ACAD_CAREER AND AC2.EFFDT <= O.EFFDT) AND COX.FIELDNAME = 'SSR_COMPONENT' AND COX.FIELDVALUE = CO.SSR_COMPONENT AND COX.EFFDT = ( SELECT MAX(COX2.EFFDT) FROM PSXLATITEM COX2 WHERE COX.FIELDNAME = COX2.FIELDNAME AND COX.FIELDVALUE = COX2.FIELDVALUE AND COX2.EFFDT <= %CurrentDateIn) AND CTX.FIELDNAME = 'ALLOWABLE_IND' AND CTX.FIELDVALUE = O.OEE_IND AND CTX.EFFDT = ( SELECT MAX(CTX2.EFFDT) FROM PSXLATITEM CTX2 WHERE CTX.FIELDNAME = CTX2.FIELDNAME AND CTX.FIELDVALUE = CTX2.FIELDVALUE AND CTX2.EFFDT <= %CurrentDateIn) AND ORG.ACAD_ORG = O.ACAD_ORG AND ORG.EFFDT = ( SELECT MAX(ORG2.EFFDT) FROM PS_ACAD_ORG_TBL ORG2 WHERE ORG.ACAD_ORG = ORG2.ACAD_ORG AND ORG2.EFFDT <= O.EFFDT) AND S.INSTITUTION = O.INSTITUTION AND S.SUBJECT = O.SUBJECT AND S.EFFDT = ( SELECT MAX(S2.EFFDT) FROM PS_SUBJECT_TBL S2 WHERE S.INSTITUTION = S2.INSTITUTION AND S.SUBJECT = S2.SUBJECT AND S2.EFFDT <= O.EFFDT) AND T.CRSE_ID = O.CRSE_ID AND T.CRSE_OFFER_NBR = O.CRSE_OFFER_NBR AND T.EFFDT = O.EFFDT AND (UNS.MESSAGE_SET_NBR = 14770 AND UNS.MESSAGE_NBR = 9025) AND (UNT.MESSAGE_SET_NBR = 14770 AND UNT.MESSAGE_NBR = 9024) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | Character(6) | VARCHAR2(6) NOT NULL | Course ID | |
2 | Number(2,0) | SMALLINT NOT NULL | Course Offering Nbr | |
3 | Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
|
4 | Character(8) | VARCHAR2(8) NOT NULL |
Subject Area
Prompt Table: SUBJECT_TBL |
|
5 | Character(10) | VARCHAR2(10) NOT NULL | Course Typically Offered | |
6 | Character(30) | VARCHAR2(30) NOT NULL | Grading Basis | |
7 | Character(4) | VARCHAR2(4) NOT NULL | This field is used to track the Requirement Designation attached to a course in Student Records. This field is also used for tracking information through Academic Advisement. | |
8 | Character(3) | VARCHAR2(3) NOT NULL | Open Entry/Exit Indicator Description | |
9 | Character(30) | VARCHAR2(30) NOT NULL | Career Description | |
10 | Character(30) | VARCHAR2(30) NOT NULL | Component Description | |
11 | Character(5) | VARCHAR2(5) NOT NULL |
Campus
Prompt Table: CAMPUS_TBL |
|
12 | Character(30) | VARCHAR2(30) NOT NULL | Academic Organization Description | |
13 | Character(4) | VARCHAR2(4) NOT NULL | Course Attribute | |
14 | Character(10) | VARCHAR2(10) NOT NULL | Course Attribute Value | |
15 | Character(30) | VARCHAR2(30) NOT NULL | Number of Units | |
16 | UNITS_MINIMUM | Number(6,2) | DECIMAL(5,2) NOT NULL | Minimum Units |
17 | UNITS_MAXIMUM | Number(6,2) | DECIMAL(5,2) NOT NULL | Maximum Units |
18 | SSR_CRS_ATTR_FL | Character(70) | VARCHAR2(70) NOT NULL | Course Attribute |
19 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL |
Academic Institution
Default Value: OPR_DEF_TBL_CS.INSTITUTION Prompt Table: INSTITUTION_TBL |
20 | ACAD_GROUP | Character(5) | VARCHAR2(5) NOT NULL |
Academic Group
Default Value: OPR_DEF_TBL_CS.ACAD_GROUP Prompt Table: ACAD_GROUP_TBL |
21 | SSR_SUBJ_DESCR_ES | Character(30) | VARCHAR2(30) NOT NULL | Subject |
22 | CATALOG_NBR | Character(10) | VARCHAR2(10) NOT NULL | Catalog Nbr |
23 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
24 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
25 | COURSE_TITLE_LONG | Character(100) | VARCHAR2(100) NOT NULL | Long Course Title |
26 | COURSE_APPROVED | Character(1) | VARCHAR2(1) NOT NULL |
Course Approved
A=Approved D=Denied P=Pending Default Value: P |
27 | SSR_CRSE_AS_OF_DT | Character(35) | VARCHAR2(35) NOT NULL | Course As Of Date |
28 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
29 | SSR_CRSE_OFF_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Course Typically Offered Description |
30 | SSR_CRSE_TYPOFF_YN | Character(1) | VARCHAR2(1) NOT NULL |
Student Records Course Typically Offered Yes or No
N=No Y=Yes |
31 | GRADING_BASIS | Character(3) | VARCHAR2(3) NOT NULL |
Grading Basis
ANC=ABC/NC Grading AUD=Audit BMT=Multi-Term Course: Not Graded CNC=Credit / No Credit EQV=Equivalent GRD=Graded HSC=Hundred Point Scale (NLD) LAW=Law MED=Medical School Grades NOG=No Grade Associated NON=Non-Graded Component NQF=NQF Scale OPT=Student Option PNP=Pass/Not Pass SUS=Satisfactory/Unsatisfactory TRN=Transfer Grading Basis TSC=Ten Point Scale (NLD) |
32 | OEE_IND | Character(1) | VARCHAR2(1) NOT NULL | Allow OEE Enrollment |
33 | 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 |
34 | SSR_COMPONENT | Character(3) | VARCHAR2(3) NOT NULL |
Course Component.
CLN=Clinical CON=Continuance DIS=Discussion FLD=Field Studies IND=Independent Study LAB=Laboratory LEC=Lecture PRA=Practicum RSC=Research SEM=Seminar SUP=Supervision THE=Thesis Research TUT=Tutorial |
35 | ACAD_ORG | Character(10) | VARCHAR2(10) NOT NULL |
Academic Organization
Prompt Table: ACAD_ORG_SRCH |
36 | SSR_CRSE_TOPIC_ES | Character(30) | VARCHAR2(30) NOT NULL | Course Topic |
37 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
38 | DESCR4 | Character(30) | VARCHAR2(30) NOT NULL | Descr 4 |
39 | DESCR5 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |