Platinum Analytics Views for Datatel Colleague (UniData)

1.Create Custom SQL Tables

Create 6 custom SQL tables as co-files of Colleague files via the VSG tool, as follows. This step helps make some multi-relational data more readily accessible to the SQL views.

X_CRSASTRA (Co-file of the COURSES file)

X_CSMASTRA (Co-file of the COURSE.SEC.MEETING file)

X_FACASTRA (Co-file of the FACULTY file)

X_SECASTRA (Co-file of the COURSE.SECTIONS file)

X_STPRASTRA (Co-file of the STUDENT.PROGRAMS file)

X_STUASTRA (Co-file of the STUDENTS file)

2.Base Views and Permissions

DEGREES_1 from DEGREES (Select permission)

DEG.DESC

DEGREES.ADDDATE

DEGREES.ID

ACAD_PROGRAMS from ACAD.PROGRAMS (Select permission)

ID0

ACPG.DESC

ACPG.DEGREE

ACPG.ACAD_LEVEL

ACTIVITY.DATE

MAJORS_1 from MAJORS (Select permission)

ID0

MAJ.DESC

MAJORS.ADDDATE

MAJORS_1 from MAJORS (Select permission)

ID0

MAJ.DESC

MAJORS.ADDDATE

ACAD_LEVELS from ACAD.LEVELS (Select permission)

ID0

ACLV.DESC

ACAD.LEVELS.ADDDATE

DIVISIONS_1 from DIVISIONS (Select permission)

ID0

DIV.DESC

DIVISIONS.ADD.DATE

DEPTS_1 from DEPTS (Select permission)

ID0

DEPTS.DESC

DEPTS.DIVISION

DEPTS.ACTIVE.FLAG

DEPTS.ADD.DATE

DEPTS.TYPE

DEPTS.ID

INSTR_METHODS from INSTR.METHODS (Select permission)

ID0

INM.DESC

INSTR.METHODS.ADDDATE

INSTR.METHODS.ID

CLASSES_1 from CLASSES (Select permission)

ID0

CLS.DESC

CLASSES.ADDDATE

STUDENT_TYPES from STUDENT.TYPES (Select permission)

ID0

STT.DESC

STUDENT.TYPES.ADDDATE

SUBJECTS_1 from SUBJECTS (Select permission)

ID0

SUBJ.DESC

SUBJECTS.ADDDATE

LOCATIONS_1 from LOCATIONS (Select permission)

ID0

LOC.DESC

LOCATIONS.ID

BUILDINGS_1 from BUILDINGS (Select permission)

ID0

BLDG.DESC

BLDG.LOCATION

BUILDINGS.ID

ROOMS_1 from ROOMS (Select permission)

ID0

ROOM.NAME

ROOM.CAPACITY

ROOMS.BLDG.ID

ROOMS.ROOM.ID

ROOM.LOCATION TRANS("BUILDINGS",ROOMS.BLDG.ID,"BLDG.LOCATION","X")

X_ROOM_CONFIGURATIONS AS (((ROOMS.BLDG.ID:"*"):ROOMS.ROOM.ID):"_Default")

TERMS_1 from TERMS (Select permission)

ID0

TERM.DESC

TERM.START.DATE

TERM.END.DATE

TERMS.ID

COURSES_1 from COURSES (Select permission)

ID0

CRS.TITLE

CRS.DEPTS

CRS.NAME

CRS.DESC

CRS.SHORT.TITLE

CRS.SUBJECT

CRS.MIN.CRED

CRS.MAX.CRED

CRS.PREREQS

CRS.NO

CRS.STATUS

CRS.COREQ.COURSES

CRS.START.DATE

CRS.END.DATE

CRS.COURSE.TYPES

CRS.ACAD.LEVEL

CRS.LOAD

CRS.CONTACT.HOURS

CRS.LOCATIONS

CRS.INSTR.METHODS

CRS.COREQ.COURSES.REQD.FLAG

CRS.EQUATE.CODES

CRS.ALLOW.PASS.NOPASS.FLAG

X_CRS_ASTRA_NULL AS ""

X_CRS_CAMPUS_RESTRICTIONS AS SUBR('-CATS',REUSE((@ID:"*")),CRS_LOCATIONS)

X_CRS_DESC AS CONVERT(@VM,' ',CRS_DESC)

X_CRS_CRED_HOURS_INDICATOR AS IF CRS.MAX.CRED THEN ("1") ELSE ("0")

X_CRS_DELIVERY_METHOD AS (@ID:"*Standard")

X_CRS_COURSE_HAS_PREREQ AS IF CRS.PREREQS = '' THEN ("FALSE") ELSE ("TRUE")

X_CRS_COURSE_IS_ACTIVE AS IF CRS.CURRENT.STATUS EQ "A" THEN ("TRUE") ELSE ("FALSE")

X_CRS_COURSE_IS_IN_CATALOG AS D.DATE = '' THEN (DATE()) ELSE (CRS.END.DATE); IF DATE() GE CRS.START.DATE AND DATE() LE @1 THEN ("TRUE") ELSE ("FALSE")

X_CRS_MEETING_TYPE AS SUBR('-CATS',REUSE((@ID:"*")),CRS.INSTR.METHODS)

CRS_CURRENT_STATUS AS FIELD(CRS_STATUS,@VM,1);@1

X_CRS_CURRENT_STATUS AS EXTRACT(CRS.STATUS,1,1,0)

CRS_DIVISIONS AS TRANS("DEPTS",CRS.DEPTS,"DEPTS.DIVISION","X")

X_CRS_MEET_TYPE_NAME AS TRANS("INSTR.METHODS",CRS.INSTR.METHODS,"INM.DESC","X")

X_CRS_CURRENT_STATUS_FLAG AS IF (CRS.CURRENT.STATUS EQ "A") THEN "Y" ELSE "N"

X_CRSASTRA from X.CRSASTRA (Select permission)

ID0

X.CRSASTRA.DESC

X.CRSASTRA.CURR.STATUS

COURSE_SECTIONS from COURSE.SECTIONS (Select permission)

ID0

SEC.SHORT.TITLE

SEC.LOCATION

SEC.CAPACITY

SEC.SUBJECT

SEC.MEETING

SEC.TERM

SEC.COURSE.NO

SEC.NO

COURSE.SECTIONS.CHGDATE

SEC.COURSE

SEC.XLIST

SEC.FACULTY

SEC.ACTIVE.STUDENTS

SEC.WAITLIST.MAX

SEC.STATUS

SEC.NAME

ASTRA.SEC.ACTUAL.ENROLLMENT AS DCOUNT(SEC_ACTIVE_STUDENTS,@VM)

SEC.CURRENT.STATUS AS FIELD(SEC_STATUS,@VM,1)

COURSE.SECTIONS.ID AS FIELD(@ID,"*",1)

COURSE_SEC_XLISTS from COURSE.SEC.XLISTS (Select permission)

ID0

CSXL.CAPACITY

CSXL.PRIMARY.SECTION

COURSE_SEC_MEETING from COURSE.SEC.MEETING (Select permission)

ID0

CSM.COURSE.SECTION

CSM.START.DATE

CSM.END.DATE

CSM.BLDG

CSM.ROOM

CSM.INSTR.METHOD

CSM.START.TIME

CSM.END.TIME

COURSE.SEC.MEETING.CHGDATE

CSM.MONDAY

CSM.TUESDAY

CSM.WEDNESDAY

CSM.THURSDAY

CSM.FRIDAY

CSM.SATURDAY

CSM.SUNDAY

CSM.FACULTY

CSM.ROOM.SCHED.TERM

COURSE.SEC.MEETING.ID AS FIELD(@ID,"*",1)

X_CSM_CSF_FACULTY as TRANS("COURSE_SEC_FACULTY",TRANS("COURSE_SECTIONS",CSM_COURSE_SECTION,"SEC_FACULTY","X"),"CSF_FACULTY","X")

SEC.LOCATION AS TRANS(COURSE.SECTIONS,CSM.COURSE.SECTION,'SEC.LOCATION','X')

COURSE_SECTIONS from COURSE.SECTIONS (Select permission)

ID0

SEC.SHORT.TITLE

SEC.LOCATION

SEC.CAPACITY

SEC.SUBJECT
SEC.MEETING

SEC.TERM

SEC.COURSE.NO

SEC.NO

COURSE.SECTIONS.CHGDATE

SEC.COURSE

SEC.XLIST

SEC.FACULTY

SEC.ACTIVE.STUDENTS

SEC.WAITLIST.MAX

SEC.STATUS

SEC.NAME

ASTRA.SEC.ACTUAL.ENROLLMENT AS DCOUNT(SEC_ACTIVE_STUDENTS,@VM)

SEC.CURRENT.STATUS AS FIELD(SEC_STATUS,@VM,1)

COURSE.SECTIONS.ID AS FIELD(@ID,"*",1)

X_CSMASTRA from X.CSMASTRA (Select permission)

ID0

X.CSMASTRA.DAYS

X.CSMASTRA.FACULTY.ID

X.CSMASTRA.MTG.PATTERN

X_CSMASTRA from X.CSMASTRA (Select permission)

ID0

X.CSMASTRA.DAYS

X.CSMASTRA.FACULTY.ID

X.CSMASTRA.MTG.PATTERN

X_SECASTRA from X.SECASTRA (Select permission)

ID0

X.SECASTRA.ACTIVE.ENROLL

X.SECASTRA.WAIT.ENROLL

X.SECASTRA.CURR.STATUS

X.SECASTRA.FIRST.FACULTY

X.SECASTRA.XLIST.ENROLL

PERSON_1 from PERSON (Select permission)

ID0

LAST.NAME

FIRST.NAME

MIDDLE.NAME

PREFIX

PREFERRED.ADDRESS

NICKNAME

SUFFIX

DECEASED.DATE

PREFERRED.RESIDENCE

PERSON.CORP.INDICATOR

PERSON.EMAIL.ADDRESSES

ADDR.TYPE

ADDR.EFFECTIVE.START

ADDR.EFFECTIVE.END

PRIVACY.FLAG

ADDRESS.LINES

COURSE_SEC_FACULTY from COURSE.SEC.FACULTY (Select permission)

ID0

CSF.COURSE.SECTION

CSF.FACULTY

X_FACASTRA from X. FACASTRA (Select permission)

ID0

X.FACASTRA.FULL.NAME

X.FACASTRA.EMAIL

X.FACASTRA.TITLE

X.FACASTRA.PHONE

X.FACASTRA.EXTENSION

X.FACASTRA.MOBILE

X.FACASTRA.FAX

X.FACASTRA.IM.NAME

X.FACASTRA.PRIMARY.DEPT

X.FACASTRA.START.DATE

X.FACASTRA.END.DATE

X.FACASTRA.STATUS

STUDENTS_1 from STUDENTS (Select permission)

ID0

STU.RESIDENCY.STATUS

STU.TYPES (

STU.ACAD.LEVELS

STU.ACAD.PROGRAMS

STU.TERMS

STU.NOTES

STUDENTS.CHGDATE

X.STU.PREF.ADD.LINES AS TRANS("ADDRESS_1",X_STU_PER_ADDRESS_POINTER,"ADDRESS_LINES","X")

X.STU.PER.ADDRESS.POINTER AS TRANS("PERSON_1",@ID,"PREFERRED_ADDRESS","X")

STU.ACTIVE.PROGRAMS

X.STU.ACTIVE.STUDENT AS DATE(); ((SUBR("CC.GET.LATEST.STC.END.DATE",@ID)) - (@1)); IF @2 GT 0 THEN ("ACTIVE") ELSE ("INACTIVE")

X.STU.PREF.ADDRESS.L1 AS FIELD(X_STU_PREF_ADD_LINES,@VM,1)

X.STU.PREF.ADDRESS.L2 AS FIELD(X_STU_PREF_ADD_LINES,@VM,2)

X_STU_STA_ADMIT_STATUS AS TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_ADMIT_STATUS","X")

STU.ANT.CMPL.DATES AS SUBR("CC.GET.STU.ANT.CMPL.DATES",@ID);@1

X_STU_ANT_GRAD_YEAR AS EXTRACT(STU_ANT_CMPL_DATES,1,1,0)

X.STU.CITY AS TRANS("ADDRESS_1",X_STU_PER_ADDRESS_POINTER,"CITY","X")

X.STU.MAXIENT.CLASS AS ""; IF TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_CLASS","X") EQ "FR" THEN ("Freshman") ELSE @1; IF TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_CLASS","X") EQ "SO" THEN ("Sophomore") ELSE @2

X_STU_CURR_RESIDENCY_STATUS AS EXTRACT(STU_RESIDENCY_STATUS,1,1,0)

X_STU_PER_FIRST_EMAIL AS EXTRACT(TRANS("PERSON_1",@ID,"PERSON_EMAIL_ADDRESSES","X"),1,1,0)

X.STU.MAXIENT.FIRST.NAME AS TRANS("PERSON_1",@ID,"FIRST_NAME","X"); IF @1 EQ "" THEN ("unavailable") ELSE @1

X_STU_FIRST_TYPE AS EXTRACT(STU_TYPES,1,1,0)

X_STU_IS_DECEASED AS ""; IF TRANS("PERSON_1",@ID,"DECEASED_DATE","X") THEN ("TRUE") ELSE ("FALSE")

X_STU_ASTRA_IS_GRAD AS IF STU_ACTIVE_PROGRAMS THEN ("FALSE") ELSE ("TRUE")

X_STU_ASTRA_UG_LEVEL AS IF TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_START_DATE","X") THEN ("UG") ELSE ("")

STU.LAST.NAME AS TRANS("PERSON_1",@ID,"LAST_NAME","X"); IF @1 EQ "" THEN ("unavailable") ELSE @1

X_STU_STA_LOAD_INTENT AS IF TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_STUDENT_LOAD_INTENT","X") EQ "FT" THEN ("TRUE") ELSE ("FALSE")

X.STU.MAXIENT.MIDDLE.NAME AS TRANS("PERSON_1",@ID,"MIDDLE_NAME","X")

X_STU_ANT_GRAD_TERM AS ""

X.STU.STATE AS TRANS("ADDRESS_1",X_STU_PER_ADDRESS_POINTER,"STATE","X")

X_STU_LOAD_INTENT AS TRANS("STUDENT_ACAD_LEVELS",(@ID:"*UG"),"STA_STUDENT_LOAD_INTENT","X")

X.STU.ZIP AS TRANS("ADDRESS_1",X_STU_PER_ADDRESS_POINTER,"ZIP","X")

STUDENT_ACAD_CRED from STUDENT.ACAD.CRED (Select permission)

ID0

STC.PERSON.ID

STC.TITLE

STC.ACAD.LEVEL

STC.SUBJECT

STC.COURSE

STC.STATUS

STC.CRED

STC.CMPL.CRED

STC.VERIFIED.GRADE

STC.TERM

STC.ATT.CRED

STC.GRADE.PTS

STC.REPL.CODE

STUDENT.ACAD.CRED.CHGDATE

STUDENT.ACAD.CRED.ID AS FIELD(@ID,"*",1)

X_STC_ADDR_END_DATE AS EXTRACT(TRANS("PERSON_1",STC_PERSON_ID,"ADDR_EFFECTIVE_END","X"),1,1,0)

X_STC_ADDRESS_L2 AS FIELD(TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ADDRESS_LINES","X"),@VM,2)

X_STC_ADDRESS_L3 AS FIELD(TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ADDRESS_LINES","X"),@VM,3)

X_STC_ADDRESS_L4 AS FIELD(TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ADDRESS_LINES","X"),@VM,4)

X_STC_ADDRESS_L5 AS FIELD(TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ADDRESS_LINES","X"),@VM,5)

X_STC_ADDR_START_DATE AS EXTRACT(TRANS("PERSON_1",STC_PERSON_ID,"ADDR_EFFECTIVE_START","X"),1,1,0)

X_STC_PER_ADDR_TYPE1 AS FIELD(TRANS("PERSON_1",STC_PERSON_ID,"ADDR_TYPE","X"),@VM,1); FIELD(CONVERT(@VM,@SM,TRANS("PERSON_1",STC_PERSON_ID,"ADDR_TYPE","X")),@SM,1)

X_STC_ADDRESS_L1 AS AS FIELD(TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ADDRESS_LINES","X"),@VM,1)

X_STC_CRS_ALLOW_PASS_NOPASS AS IF TRANS("COURSES_1",STC_COURSE,"CRS_ALLOW_PASS_NOPASS_FLAG","X") EQ "N" THEN ("1") ELSE ("0")

X_STC_PREF_ADDR_CITY AS TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"CITY","X")

STC_CURRENT_STATUS AS EXTRACT(STC_STATUS,1,1,0)

X_STC_PER_FIRST_EMAIL AS EXTRACT(TRANS("PERSON_1",STC_PERSON_ID,"PERSON_EMAIL_ADDRESSES","X"),1,1,0)

X_STC_ASTRA_ACTIVE_PROGRAMS AS IF EXTRACT(SUBR("S.UDT.TRANS","STUDENTS_1",STC_PERSON_ID,"STU_ACTIVE_PROGRAMS"),1,1,0) NE "" THEN ("N") ELSE ("Y")

X_STC_GRADED AS IF STC_VERIFIED_GRADE THEN ("1") ELSE ("0")

X_STC_PREFERRED_ADDR AS TRANS("PERSON_1",STC_PERSON_ID,"PREFERRED_ADDRESS","X")

X_STC_REPL_FLAG AS IF STC_REPL_CODE EQ "R" THEN ("1") ELSE ("0")

X_STC_PREF_ADDR_STATE AS TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"STATE","X")

X_STC_SUM_ATTEMPT_CREDS AS SUM(SUM(SUBR("S.UDT.TRANS","PERSON_ST",STC_PERSON_ID,"PST_ACAD_CRED")))

X_STC_GRD_GRADE AS TRANS('GRADES_1',STC_VERIFIED_GRADE,'GRD_GRADE','X')

X_STC_PREF_ADDR_ZIP AS TRANS("ADDRESS_1",X_STC_PREFERRED_ADDR,"ZIP","X")

STUDENT_PROGRAMS from STUDENT.PROGRAMS (Select permission)

ID0

STPR.ANT.CMPL.DATE

STPR.CATALOG

STPR.LOCATION

STPR.STATUS

STPR.DEPT

STPR.STUDENT

STPR.ACAD.LEVEL

STPR.ACAD.PROGRAM

X_STPR_COLLEGE AS "0013313"

X_STPR_DCTAL_CONCENTRATION AS IF SUBR("CC.X.S.CONCENTRATOR.CHECK",STPR_STUDENT) EQ "1" THEN ("DCTAL") ELSE ("")

X_STPR_CONCENTRATION_PRI AS IF SUBR("CC.X.S.CONCENTRATOR.CHECK",STPR_STUDENT) EQ "1" THEN ("TRUE") ELSE ("FALSE")

STPR.CURRENT.STATUS AS FIELD(STPR_STATUS,@VM,1)

X_STPR_DEGREE_SIS_KEY AS ((((STPR_STUDENT:"_"):X_STPR_ACPG_DEGREE):"_"):STPR_ACAD_PROGRAM)

X_STPR_ACPG_DEGREE AS TRANS("ACAD_PROGRAMS",STPR_ACAD_PROGRAM,"ACPG_DEGREE","X")

X_STPR_PRIMARY_DEGREE AS IF STPR_CURRENT_STATUS EQ "A" AND STPR_ACAD_LEVEL EQ "UG" THEN ("TRUE") ELSE ("FALSE")

X_STPRASTRA from X.STPRASTRA (Select permission)

ID0

X.STPRASTRA.STUDENT

X.STPRASTRA.ACAD.PROGRAM

X.STPRASTRA.DEGREE

X.STPRASTRA.CURR.STATUS

X.STPRASTRA.CURR.STATUS.DATE

X.STPRASTRA.LAST.TERM

X.STPRASTRA.MAJOR

X.STPRASTRA.MINOR

X.STPRASTRA.ACAD.LEVEL

X.STPRASTRA.MAJOR.IS.PRI AS IF X_STPRASTRA_MAJOR THEN ("TRUE") ELSE ("FALSE")

X.XSTPRASTRA.MAJOR.SIS.KEY AS ((((((X_STPRASTRA_STUDENT:"_"):X_STPRASTRA_DEGREE):"_"):X_STPRASTRA_ACAD_PROGRAM):"_"):X_STPRASTRA_MAJOR)

PERSON_ST from PERSON.ST (Select permission)

ID0

PST.STUDENT.ACAD.CRED

PST.ADMISSIONS.TESTS

PST.ACAD.CRED AS TRANS("STUDENT_ACAD_CRED",PST_STUDENT_ACAD_CRED,"STC_ATT_CRED","X")

STUDENT_NON_COURSES from STUDENT.NON.COURSES (Select permission)

ID0

STNC.SCORE

STNC.NON.COURSE

STUDENT_TERMS from STUDENT.TERMS (Select permission)

ID0

STTR.SCHEDULE

STTR.STUDENT.ACAD.CRED

X_STTR_IN_PROGRESS_CREDITS AS SUBR("CC.X.S.SUM.REGISTERED.CREDS",STTR_SCHEDULE,"","9","UG")

X_STTR_COURSE_CREDITS AS SUBR("CC.X.S.SUM.REGISTERED.CREDS",STTR_SCHEDULE,"I","","UG")

STTR.STUDENT AS FIELD(@ID,"*",1)

STTR.TERM AS FIELD(@ID,"*",2)

X_STTR_TRANSFER_CREDITS AS SUBR("CC.X.S.SUM.REGISTERED.CREDS",STTR_STUDENT_ACAD_CRED,"","14","UG")

ACAD_REQMTS from ACAD.REQMTS (Select permission)

ID0

ACR.DESC

ACR.COMMENTS

ACR.ACAD.PROGRAM.REQMTS

ACR.CATALOGS

ACR.PREREQ.COURSE

ACR.TOP.REQMT.BLOCK

X_ACR_CAT_UNATTACHED AS (LEN(ACR_CATALOGS) GT 0)

X_ACR_IS_PREREQ AS (LEN(ACR_PREREQ_COURSE) GT 0)

ACAD_REQMT_BLOCKS from ACAD.REQMT.BLOCKS (Select permission)

ID0

ACRB.LABEL

ACRB.ACAD.CRED.RULES

ACRB.COURSE.REUSE.FLAG

ACRB.MIN.GRADE

ACRB.TYPE

ACRB.COURSES

ACRB.MIN.CRED

ACRB.SUBBLOCKS

ACRB.MIN.NO.SUBBLOCKS

ACRB.PARENT.BLOCK

ACRB.ACAD.REQMT

ACRB.FROM.COURSES

ACRB.BUT.NOT.COURSES

ACRB.FROM.DEPTS

ACRB.FROM.SUBJECTS

ACRB.FROM.CRS.LEVELS

ACRB.BUT.NOT.SUBJECTS

X_ACRB_ASTRA_9999 AS "9999"

X_ACRB_ASTRA_BLANK AS ""

X_ACRB_ASTRA_EFFECTIVE_DATE AS ICONV("01/01/1901", "D")

X_ACRB_ASTRA_VALID_DATE AS ICONV("12/31/9999", "D")

X_ACRB_INSTITUTION_CRED AS IF ACRB_PARENT_BLOCK THEN (ACRB_INSTITUTION_CRED) ELSE (TRANS("ACAD_PROGRAM_REQMTS",EXTRACT(TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_ACAD_PROGRAM_REQMTS","X"),1,1,0),"ACPR_INSTITUTION_CRED","X"))

X_ACRB_MIN_CRED AS IF ACRB_PARENT_BLOCK THEN (ACRB_MIN_CRED) ELSE (TRANS("ACAD_PROGRAM_REQMTS",EXTRACT(TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_ACAD_PROGRAM_REQMTS","X"),1,1,0),"ACPR_CRED","X"))

ACAD_REQMT_BLOCKS_ID AS FIELD(@ID,"*",1)

X_ACRB_ACR_COMMENTS AS TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_COMMENTS","X")

X_ACRB_ASTRA_COUNT_GPA AS (LEN(TRANS("ACAD_PROGRAM_REQMTS",TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_ACAD_PROGRAM_REQMTS","X"),"ACPR_MIN_GPA","X")) GT 0)

X_ACRB_FIRST_ACPR_POINTER AS EXTRACT(TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_ACAD_PROGRAM_REQMTS","X"),1,1,0)

X_ACRB_LOGIC_OPERATOR AS IF ACRB_TYPE EQ "10" THEN ("0") ELSE ""; IF ACRB_TYPE EQ "11" THEN ("1") ELSE @1; IF ACRB_TYPE EQ "20" THEN ("0") ELSE @2; IF ACRB_TYPE EQ "21" THEN ("1") ELSE @3; IF ACRB_TYPE EQ "30" THEN ("3") ELSE @4; IF ACRB_TYPE EQ "31" THEN ("2") ELSE @5; IF ACRB_TYPE EQ "32" THEN ("2") ELSE @6; IF ACRB_TYPE EQ "33" THEN ("2") ELSE @7; IF ACRB_TYPE EQ "34" THEN ("4") ELSE @8

X_ACRB_MIN_GRADE AS IF ACRB_MIN_GRADE NE "" THEN (ACRB_MIN_GRADE) ELSE (TRANS("ACAD_PROGRAM_REQMTS",EXTRACT(TRANS("ACAD_REQMTS",ACRB_ACAD_REQMT,"ACR_ACAD_PROGRAM_REQMTS","X"),1,1,0),"ACPR_MIN_GRADE","X")); TRANS("GRADES_1",@1,"GRD_GRADE","X")

X_ACRB_MIN_NO_SUBBLOCKS AS (LEN(ACRB_MIN_NO_SUBBLOCKS) GT 0); IF @1 EQ "1" THEN (ACRB_MIN_NO_SUBBLOCKS) ELSE ("0")

X_ACRB_COURSE_REUSE_FLAG AS IF ACRB_COURSE_REUSE_FLAG EQ "N" THEN ("0") ELSE ""; IF ACRB_COURSE_REUSE_FLAG EQ "Y" THEN ("1") ELSE @1

X_ACRB_RULE_NAME AS ((ACRB_LABEL:"Rule "):@ID)

X_ACRB_ASTRA_IS_SUBSET AS (LEN(ACRB_PARENT_BLOCK) GT 0); IF @1 EQ "1" THEN ("1") ELSE ("0")

X_ACRB_TRANSFER_CRED AS ((TRANS("ACAD_PROGRAM_REQMTS",X_ACRB_FIRST_ACPR_POINTER,"ACPR_CRED","X")) - (TRANS("ACAD_PROGRAM_REQMTS",X_ACRB_FIRST_ACPR_POINTER,"ACPR_INSTITUTION_CRED","X")))

X_ACRB_WITHIN_INDICATOR AS "0"

ACAD_PROGRAM_REQMTS from ACAD.PROGRAM.REQMTS (Select permission)

ID0

ACPR.CRED

ACPR.INSTITUTION.CRED

ACPR.MIN.GPA

ACPR.ACAD.REQMTS

ACPR.MIN.GRADE

ACPR.ACAD.CRED.RULES

X_ACPR_ASTRA_FALSE AS "0"

X_ACPR_ASTRA_IS_CAPTIVE AS "1"

X_ACPR_ASTRA_RECORD_TYPE AS "2"

X_ACPR_ASTRA_SISKEY AS ((((ACPR_ACAD_PROGRAM:"*"):ACPR_CATALOG):"*"):X_ACPR_TOP_REQMT_BLOCK)

X_ACPR_COMPARE_FIELD_TYPE AS "CourseLevel"

X_ACPR_COMPARE_VALUE AS TRANS("RULES","DA.NODEV","RL.CHECK.VALUES","X")

X_ACPR_TOP_REQMT_BLOCK AS TRANS("ACAD_REQMTS",EXTRACT(ACPR_ACAD_REQMTS,1,1,0),"ACR_TOP_REQMT_BLOCK","X")

ACPR.ACAD.PROGRAM AS FIELD(@ID,"*",1)

ACPR.CATALOG AS FIELD(@ID,"*",2)

X.ACPR.ACPG.COMMENTS AS TRANS('ACAD.PROGRAMS',ACPR.ACAD.PROGRAM,'ACPG.COMMENTS','X')

X_ACPR_MIN_GRADE AS TRANS("GRADES_1",ACPR_MIN_GRADE,"GRD_GRADE","X")

3.Additional Views

LOOKUPS IMPORT

DEGREES_1_ASTRA

CREATE VIEW DEGREES_1_ASTRA AS

SELECT DEGREES_ID,

DEG_DESC

FROM DEGREES_1;

ACAD_PROGRAMS_ASTRA

CREATE VIEW ACAD_PROGRAMS_ASTRA AS

SELECT ID,

ACPG_DESC,

ACTIVITY_DATE

FROM ACAD_PROGRAMS;

MAJORS_1_ASTRA

CREATE VIEW MAJORS_1_ASTRA AS

SELECT ID,

MAJ_DESC

FROM MAJORS_1;

ACAD_LEVELS_ASTRA

CREATE VIEW ACAD_LEVELS_ASTRA AS

SELECT ID,

ACLV_DESC

FROM ACAD_LEVELS;

DIVISIONS_1_ASTRA

CREATE VIEW DIVISIONS_1_ASTRA AS

SELECT ID,

DIV_DESC FROM DIVISIONS_1;

DEPTS_1_ASTRA

CREATE VIEW DEPTS_1_ASTRA AS

SELECT ID,

DEPTS_DESC

FROM DEPTS_1;

INSTR_METHODS_ASTRA

CREATE VIEW INSTR_METHODS_ASTRA AS

SELECT ID,

INM_DESC

FROM INSTR_METHODS;

CLASSES_1_ASTRA

CREATE VIEW CLASSES_1_ASTRA AS

SELECT ID,

CLS_DESC

FROM CLASSES_1;

STUDENT_TYPES_ASTRA

CREATE VIEW STUDENT_TYPES_ASTRA AS

SELECT ID,

STT_DESC

FROM STUDENT_TYPES;

SUBJECTS_1_ASTRA

CREATE VIEW SUBJECTS_1_ASTRA AS

SELECT ID,

SUBJ_DESC

FROM SUBJECTS_1;

CAMPUSBUILDINGROOM IMPORT

LOCATIONS_1_ASTRA

CREATE VIEW LOCATIONS_1_ASTRA AS

SELECT ID,

LOC_DESC

FROM LOCATIONS_1;

BUILDINGS_1_ASTRA

CREATE VIEW BUILDINGS_1_ASTRA AS

SELECT ID,

BLDG_DESC,

BLDG_LOCATION

FROM BUILDINGS_1;

ROOMS_1_ASTRA

CREATE VIEW ROOMS_1_ASTRA AS

SELECT ROOMS_1.ID,

ROOM_NAME,

ROOMS_ROOM_ID,

ROOMS_BLDG_ID,

BUILDINGS_1.BLDG_LOCATION

FROM ROOMS_1 INNER JOIN BUILDINGS_1

ON ROOMS_1.ROOMS_BLDG_ID = BUILDINGS_1.BUILDINGS_ID;

ACAD_LEVELS_ASTRA

CREATE VIEW ACAD_LEVELS_ASTRA AS

SELECT ID,

ACLV_DESC

FROM ACAD_LEVELS;

ROOMS_1_ASTRA

CREATE VIEW ROOMS_1_ASTRA AS

SELECT ROOMS_1.ID,

ROOM_NAME,

ROOMS_ROOM_ID,

ROOMS_BLDG_ID,

BUILDINGS_1.BLDG_LOCATION

FROM ROOMS_1

INNER JOIN BUILDINGS_1 ON ROOMS_1.ROOMS_BLDG_ID = BUILDINGS_1.BUILDINGS_ID;

TERMS IMPORT

TERMS_1_ASTRA

CREATE VIEW TERMS_1_ASTRA AS

SELECT ID,

TERM_DESC,

TERM_START_DATE,

TERM_END_DATE

FROM TERMS_1;

COURSE IMPORT

COURSES_1_ASTRA

CREATE VIEW COURSES_1_ASTRA AS

SELECT CRS_NAME,

X_CRSASTRA.X_CRSASTRA_DESC,

COURSES_1.ID,

CRS_SUBJECT,

CRS_SHORT_TITLE,

CRS_START_DATE,

CRS_END_DATE,

CRS_MIN_CRED,

CRS_MAX_CRED,

CRS_PREREQS,

X_CRSASTRA.X_CRSASTRA_CURR_STATUS

FROM COURSES_1 INNER JOIN X_CRSASTRA ON COURSES_1.ID = X_CRSASTRA.ID;

COURSES_1_ASTRA_DEPTCOURSES

CREATE VIEW COURSES_1_ASTRA_DEPTCOURSES AS

SELECT ID,

CRS_DEPTS

FROM COURSES_1 UNNEST CRS_DEPTS;

COURSES_1_ASTRA_ORGCOURSES

CREATE VIEW COURSES_1_ASTRA_ORGCOURSES AS

SELECT COURSES_1.ID,

DEPTS_1.DEPTS_DIVISION

FROM COURSES_1 INNER JOIN DEPTS_1

ON COURSES_1.CRS_DEPTS = DEPTS_1.DEPTS_ID;

COURSES_1_ASTRA_CAMPUSRESTR

CREATE VIEW COURSES_1_ASTRA_CAMPUSRESTR AS

SELECT ID,

CRS_LOCATIONS

FROM COURSES_1 UNNEST CRS_LOCATIONS;

COURSES_1_ASTRA_ATTRIBUTES

CREATE VIEW COURSES_1_ASTRA_ATTRIBUTES AS

SELECT ID,

CRS_COURSE_TYPES

FROM COURSES_1 UNNEST CRS_COURSE_TYPES;

COURSES_1_ASTRA_LEVELCOURSES

CREATE VIEW COURSES_1_ASTRA_LEVELCOURSES AS

SELECT ID,

CRS_ACAD_LEVEL

FROM COURSES_1;

COURSES_1_ASTRA_EQUIV

CREATE VIEW COURSES_1_ASTRA_EQUIV AS

SELECT COURSES_1.ID,

CRS_EQUATE_CODES

FROM COURSES_1 UNNEST CRS_EQUATE_CODES

WHERE CRS_EQUATE_CODES IS NOT NULL;

COURSES_1_ASTRA_COURSECOREQS

CREATE VIEW COURSES_1_ASTRA_COURSECOREQS AS

SELECT ID,

CRS_COREQ_COURSES,

CRS_COREQ_COURSES_REQD_FLAG

FROM COURSES_1

UNNEST CRS_COREQ_COURSES, CRS_COREQ_COURSES_REQD_FLAG;

COURSES_1_ASTRA_DELIVERY

CREATE VIEW COURSES_1_ASTRA_DELIVERY AS

SELECT ID

FROM COURSES_1;

COURSES_1_ASTRA_MEETINGTYPES

CREATE VIEW COURSES_1_ASTRA_MEETINGTYPES AS

SELECT COURSES_1.ID,

CRS_INSTR_METHODS,

CRS_LOAD,

CRS_CONTACT_HOURS,

INSTR_METHODS.INM_DESC

FROM COURSES_1

INNER JOIN INSTR_METHODS

ON COURSES_1.CRS_INSTR_METHODS = INSTR_METHODS.INSTR_METHODS_ID

UNNEST CRS_INSTR_METHODS;

SECTION IMPORT

COURSE_SECTIONS_ASTRA

CREATE VIEW COURSE_SECTIONS_ASTRA AS

SELECT COURSE_SECTIONS.ID,

SEC_NAME,

SEC_TERM,

TERMS_1.ID0,

TERMS_1.TERM_DESC,

TERMS_1.TERM_START_DATE,

TERMS_1.TERM_END_DATE,

SEC_SUBJECT, SEC_COURSE,

SEC_COURSE_NO,

COURSES_1.CRS_SHORT_TITLE,

SEC_LOCATION, SEC_SHORT_TITLE,

X_SECASTRA.X_SECASTRA_ACTIVE_ENROLL,

SEC_CAPACITY,

COURSES_1.CRS_PREREQS,

SEC_WAITLIST_MAX,

SEC_XLIST,

COURSE_SEC_XLISTS.CSXL_PRIMARY_SECTION, X_SECASTRA.X_SECASTRA_CURR_STATUS,

SEC_NO,

COURSE_SECTIONS_CHGDATE,

X_SECASTRA.X_SECASTRA_WAIT_ENROLL

FROM COURSE_SECTIONS

INNER JOIN TERMS_1 ON COURSE_SECTIONS.SEC_TERM = TERMS_1.ID

INNER JOIN COURSES_1 ON COURSE_SECTIONS.SEC_COURSE = COURSES_1.ID

INNER JOIN X_SECASTRA ON COURSE_SECTIONS.ID = X_SECASTRA.ID

LEFT OUTER JOIN COURSE_SEC_XLISTS ON COURSE_SECTIONS.SEC_XLIST = COURSE_SEC_XLISTS.ID;

COURSE_SECTIONS_ASTRA_MEETING

CREATE VIEW COURSE_SECTIONS_ASTRA_MEETING AS

SELECT CSM_COURSE_SECTION,

COURSE_SEC_MEETING.ID,

COURSE_SECTIONS.SEC_COURSE_NO,

X_SECASTRA.X_SECASTRA_FIRST_FACULTY,

CSM_INSTR_METHOD,

X_CSMASTRA.X_CSMASTRA_DAYS,

X_CSMASTRA.X_CSMASTRA_MTG_PATTERN,

CSM_START_TIME,

CSM_END_TIME,

CSM_START_DATE,

CSM_END_DATE,

COURSE_SECTIONS.SEC_LOCATION,

CSM_BLDG, CSM_ROOM,

COURSE_SECTIONS.SEC_TERM

FROM COURSE_SEC_MEETING

INNER JOIN COURSE_SECTIONS

ON COURSE_SEC_MEETING.CSM_COURSE_SECTION = COURSE_SECTIONS.ID

INNER JOIN X_CSMASTRA ON COURSE_SEC_MEETING.ID = X_CSMASTRA.ID

LEFT OUTER JOIN X_SECASTRA ON COURSE_SECTIONS.ID = X_SECASTRA.ID;

COURSE_SECTIONS_ASTRA_XLIST

CREATE VIEW COURSE_SECTIONS_ASTRA_XLIST AS

SELECT SEC_XLIST,

COURSE_SECTIONS.ID,

SEC_TERM,

X_SECASTRA.X_SECASTRA_XLIST_ENROLL,

COURSE_SEC_XLISTS.CSXL_CAPACITY,

SEC_MEETING

FROM COURSE_SECTIONS INNER JOIN COURSE_SEC_XLISTS

ON COURSE_SECTIONS.SEC_XLIST = COURSE_SEC_XLISTS.ID

INNER JOIN X_CSMASTRA

ON X_CSMASTRA.ID = SEC_MEETING

LEFT OUTER JOIN X_SECASTRA

ON COURSE_SECTIONS.SEC_XLIST = X_SECASTRA.ID

UNNEST SEC_MEETING;

COURSE_SECTIONS_ASTRA_INSTR

CREATE VIEW COURSE_SECTIONS_ASTRA_INSTR AS

SELECT CSM_COURSE_SECTION,

COURSE_SEC_MEETING.ID,

X_CSM_CSF_FACULTY,

X_CSMASTRA_FACULTY_ID,

X_FACASTRA.X_FACASTRA_EMAIL,

COURSE_SECTIONS.SEC_TERM,

PERSON_1.PREFIX,

PERSON_1.NICKNAME,

PERSON_1.FIRST_NAME,

PERSON_1.MIDDLE_NAME,

PERSON_1.LAST_NAME,

PERSON_1.SUFFIX,

X_FACASTRA.X_FACASTRA_EMPLOYER,

FACULTY_1.FAC_SPECIAL_STATUS,

X_FACASTRA.X_FACASTRA_PRIMARY_DEPT,

X_FACASTRA.X_FACASTRA_START_DATE,

X_FACASTRA.X_FACASTRA_END_DATE

FROM COURSE_SEC_MEETING

INNER JOIN X_CSMASTRA ON COURSE_SEC_MEETING.ID = X_CSMASTRA.ID

INNER JOIN X_FACASTRA ON X_FACASTRA.ID = X_CSM_CSF_FACULTY

INNER JOIN PERSON_1 ON PERSON_1.ID = X_CSM_CSF_FACULTY

INNER JOIN COURSE_SECTIONS

ON COURSE_SECTIONS.ID = COURSE_SEC_MEETING.CSM_COURSE_SECTION

INNER JOIN FACULTY_1 ON FACULTY_1.ID = X_CSM_CSF_FACULTY

UNNEST X_CSM_CSF_FACULTY;

STUDENT IMPORT

STUDENTS_1_ASTRA_ANALYSIS

CREATE VIEW STUDENTS_1_ASTRA_ANALYSIS AS

SELECT DISTINCT STC_PERSON_ID StudentID,

STC_TERM TermCode

FROM STUDENT_ACAD_CRED

WHERE STC_TERM IS NOT NULL;

STUDENTS_1_ASTRA_BASE_DATA

CREATE VIEW STUDENTS_1_ASTRA_BASE_DATA AS

SELECT STUDENTS_1.ID StudentID,

STU_LAST_NAME LastName,

X_STU_MAXIENT_FIRST_NAME FirstName,

X_STU_MAXIENT_MIDDLE_NAME MiddleName,

X_STU_PREF_ADDRESS_L1 Address1,

X_STU_PREF_ADDRESS_L2 Address2,

X_STU_CITY City,

X_STU_STATE State,

X_STU_ZIP ZipCode,

X_STU_PER_FIRST_EMAIL Email,

X_STU_IS_DECEASED IsDead,

X_STU_STA_LOAD_INTENT IsFullTime,

X_STU_ASTRA_IS_GRAD IsGraduated,

X_STU_STA_ADMIT_STATUS AdmissionCode,

X_STU_ANT_GRAD_TERM ExpectedGraduationTerm,

X_STU_ANT_GRAD_YEAR ExpectedGraduationYear,

STUDENTS_CHGDATE ActivityDate,

X_STU_ANT_GRAD_TERM LeaveOfAbsenceStart,

X_STU_ANT_GRAD_TERM LeaveOfAbsenceEnd,

X_STU_MAXIENT_CLASS CurrentEducationLevel,

X_STU_ANT_GRAD_TERM LeaveOfAbsenceCode,

X_STU_LOAD_INTENT Status,

X_STU_FIRST_TYPE StudentType,

X_STU_ASTRA_UG_LEVEL StudentLevel,

X_STU_CURR_RESIDENCY_STATUS ResidentialStatus, X_STUASTRA.X_STU_ACTIVE_STUDENT ActiveCode

FROM STUDENTS_1

INNER JOIN X_STUASTRA ON STUDENTS_1.ID = X_STUASTRA.ID;

STUDENTS_1_ASTRA_DEGREE_DATA

CREATE VIEW STUDENTS_1_ASTRA_DEGREE_DATA AS

SELECT X_STPRASTRA.X_STPRASTRA_STUDENT StudentId, X_STPRASTRA.X_STPRASTRA_DEGREE DegreeName, X_STPRASTRA.X_STPRASTRA_ACAD_PROGRAM ProgramName,

X_STPR_PRIMARY_DEGREE IsDefault,

X_STPRASTRA.X_STPRASTRA_CURR_STATUS CurrentStatus, X_STPRASTRA.X_STPRASTRA_CURR_STATUS_DATE StatusDate,

STPR_CATALOG CatalogVersion,

X_STPRASTRA.X_STPRASTRA_LAST_TERM EffectiveTerm,

STPR_LOCATION Campus,

X_STPR_COLLEGE College,

X_STPRASTRA.X_STPRASTRA_ACAD_LEVEL StudentLevel

FROM STUDENT_PROGRAMS

INNER JOIN X_STPRASTRA ON STUDENT_PROGRAMS.ID = X_STPRASTRA.ID

WHERE X_STPRASTRA.X_STPRASTRA_CURR_STATUS IN ('P','A');

STUDENTS_1_ASTRA_DEGREE_DEPT

CREATE VIEW STUDENTS_1_ASTRA_DEGREE_DEPT AS

SELECT X_STPR_DEGREE_SIS_KEY DegreeSisKey,

STPR_DEPT Department,

STPR_LOCATION Campus

FROM STUDENT_PROGRAMS

INNER JOIN X_STPRASTRA ON STUDENT_PROGRAMS.ID = X_STPRASTRA.ID

WHERE X_STPRASTRA.X_STPRASTRA_CURR_STATUS IN ('P','A');

STUDENTS_1_ASTRA_DEGREE_MAJOR

CREATE VIEW STUDENTS_1_ASTRA_DEGREE_MAJOR AS SELECT X_STPR_DEGREE_SIS_KEY DegreeSisKey,

X_STPRASTRA.X_STPRASTRA_MAJOR Major,

X_STPRASTRA.X_STPRASTRA_MAJOR_IS_PRI IsPrimary

FROM STUDENT_PROGRAMS

INNER JOIN X_STPRASTRA ON STUDENT_PROGRAMS.ID = X_STPRASTRA.ID

WHERE X_STPRASTRA.X_STPRASTRA_CURR_STATUS IN ('P','A');

STUDENTS_1_ASTRA_DEGREE_MINOR

CREATE VIEW STUDENTS_1_ASTRA_DEGREE_MINOR AS

SELECT X_STPR_DEGREE_SIS_KEY DegreeSisKey,

X_STPRASTRA.X_STPRASTRA_MINOR Minor

FROM STUDENT_PROGRAMS

INNER JOIN X_STPRASTRA ON STUDENT_PROGRAMS.ID = X_STPRASTRA.ID

WHERE X_STPRASTRA.X_STPRASTRA_CURR_STATUS IN ('P','A');

STUDENTS_1_ASTRA_DEGREE_CONC

CREATE VIEW STUDENTS_1_ASTRA_DEGREE_CONC AS

SELECT X_STPRASTRA.X_XSTPRASTRA_MAJOR_SIS_KEY MajorSisKey, X_STPR_DCTAL_CONCENTRATION Concentration,

X_STPR_CONCENTRATION_PRI IsPrimary

FROM STUDENT_PROGRAMS INNER JOIN X_STPRASTRA

ON STUDENT_PROGRAMS.ID = X_STPRASTRA.ID

WHERE X_STPRASTRA.X_STPRASTRA_CURR_STATUS IN ('P','A');

STUDENTS_1_ASTRA_TRANSFER

CREATE VIEW STUDENTS_1_ASTRA_TRANSFER AS

SELECT STC_PERSON_ID StudentId,

STC_TERM TermCode,

STC_SUBJECT Subject,

STC_COURSE CourseNumber,

STUDENT_ACAD_CRED_ID SisKey,

STC_CMPL_CRED CreditHours,

X_STC_GRD_GRADE Grade,

X_STC_CRS_ALLOW_PASS_NOPASS IsGradeable,

X_STC_REPL_FLAG RepeatCourseInd,

STC_TITLE Title,

STC_ACAD_LEVEL StudentLevel

FROM STUDENT_ACAD_CRED;

STUDENTS_1_ASTRA_INST

CREATE VIEW STUDENTS_1_ASTRA_INST AS

SELECT STC_PERSON_ID StudentId,

STC_TERM TermCode,

STC_SUBJECT Subject,

STC_COURSE CourseNumber,

STUDENT_ACAD_CRED_ID SisKey,

STC_CMPL_CRED CreditHours,

X_STC_GRD_GRADE Grade,

X_STC_GRADED IsComplete,

STC_GRADE_PTS QualityPoints,

X_STC_CRS_ALLOW_PASS_NOPASS IsGradeable,

X_STC_REPL_FLAG RepeatCourseInd,

STC_TITLE Title,

STC_ACAD_LEVEL StudentLevel

FROM STUDENT_ACAD_CRED;

STUDENTS_1_ASTRA_REG

CREATE VIEW STUDENTS_1_ASTRA_REG AS

SELECT STC_PERSON_ID StudentId,

STC_TERM TermCode,

STC_SUBJECT Subject,

STC_COURSE CourseNumber,

STUDENT_ACAD_CRED_ID SisKey,

STC_CMPL_CRED CreditHours,

X_STC_GRD_GRADE Grade,

STC_GRADE_PTS QualityPoints,

X_STC_CRS_ALLOW_PASS_NOPASS IsGradeable,

X_STC_REPL_FLAG RepeatCourseInd,

STC_TITLE Title,

STC_ACAD_LEVEL StudentLevel

FROM STUDENT_ACAD_CRED;

STUDENTS_1_ASTRA_ATT_TEST

CREATE VIEW STUDENTS_1_ASTRA_ATT_TEST AS

SELECT PST_ADMISSIONS_TESTS SisKey,

STUDENT_NON_COURSES.STNC_NON_COURSE Code, STUDENT_NON_COURSES.STNC_SCORE TestScore,

PERSON_ST.ID StudentId

FROM PERSON_ST

INNER JOIN STUDENT_NON_COURSES

ON PERSON_ST.PST_ADMISSIONS_TESTS = STUDENT_NON_COURSES.STUDENT_NON_COURSES.ID

UNNEST PST_ADMISSIONS_TESTS;

STUDENTS_1_ASTRA_COURSE_ATT

CREATE VIEW STUDENTS_1_ASTRA_COURSE_ATT AS

SELECT COURSES_1.ID CourseSisKey,

CRS_COURSE_TYPES Attribute

FROM COURSES_1 UNNEST CRS_COURSE_TYPES

WHERE CRS_COURSE_TYPES IS NOT NULL;

STUDENTS_1_ASTRA_COURSE_SUM

CREATE VIEW STUDENTS_1_ASTRA_COURSE_SUM AS

SELECT STTR_STUDENT StudentId,

STTR_TERM Term,

X_STTR_COURSE_CREDITS CourseCredits,

X_STTR_TRANSFER_CREDITS TransferCredits,

X_STTR_IN_PROGRESS_CREDITS InProgressCredits

FROM STUDENT_TERMS;

PROGRAM RULE IMPORT

PROGRAM_RULES_ASTRA

CREATE VIEW PROGRAM_RULES_ASTRA AS

SELECT ACAD_REQMT_BLOCKS.ID SisKey,

X_ACRB_ASTRA_9999 Priority,

X_ACRB_MIN_GRADE MinGrade,

X_ACRB_WITHIN_INDICATOR AttributeReuse,

X_ACRB_COURSE_REUSE_FLAG CourseReuse,

X_ACRB_WITHIN_INDICATOR WithinIndicator,

X_ACRB_WITHIN_INDICATOR RequiredLogicIndicator,

X_ACRB_MIN_CRED RequiredCreditHours,

X_ACRB_INSTITUTION_CRED InstitutionCreditHours,

X_ACRB_ASTRA_BLANK InstitutionCourses,

X_ACRB_TRANSFER_CRED MaxTransferCreditHours,

X_ACRB_ASTRA_BLANK MaxTransferCourses,

ACAD_PROGRAM_REQMTS.ACPR_MIN_GPA MinGPA,

X_ACRB_ASTRA_EFFECTIVE_DATE EffDate,

X_ACRB_RULE_NAME RuleName,

ACAD_REQMTS.ACR_DESC Description,

ACAD_REQMTS.X_ACR_CAT_UNATTACHED IsUnattached, ACAD_REQMTS.X_ACR_IS_PREREQ IsPrereq,

ACAD_REQMTS.ACR_CATALOGS CatalogVersion,

X_ACRB_LOGIC_OPERATOR LogicOperator,

X_ACRB_ACR_COMMENTS Comments,

ACRB_PARENT_BLOCK ParentRuleId,

ACAD_REQMTS.ACR_TOP_REQMT_BLOCK AreaName,

X_ACRB_ASTRA_COUNT_GPA CountInGPA,

X_ACRB_ASTRA_EFFECTIVE_DATE ValidToDate,

X_ACRB_ASTRA_VALID_DATE ValidFromDate,

X_ACRB_ASTRA_IS_SUBSET IsSubsetRule,

X_ACRB_MIN_NO_SUBBLOCKS NumberOfConditions,

X_ACRB_WITHIN_INDICATOR MaxLogicIndicator,

X_ACRB_WITHIN_INDICATOR RequiredPerCondLogicIndicator,

X_ACRB_WITHIN_INDICATOR MaxPerCondLogicIndicator

FROM ACAD_REQMT_BLOCKS

LEFT OUTER JOIN ACAD_PROGRAM_REQMTS

ON ACAD_REQMT_BLOCKS.X_ACRB_FIRST_ACPR_POINTER = ACAD_PROGRAM_REQMTS.ID INNER JOIN ACAD_REQMTS

ON ACAD_REQMT_BLOCKS.ACRB_ACAD_REQMT = ACAD_REQMTS.ID

UNNEST ACAD_REQMTS.ACR_CATALOGS;

COMPLEX_PREREQ_ASTRA

CREATE VIEW COMPLEX_PREREQ_ASTRA AS

SELECT CRS_PREREQS RuleId,

COURSES_1.ID SisKey,

CRS_SUBJECT Subject,

COURSES_1.ID Course,

X_CRS_ASTRA_NULL CatalogVersion

FROM COURSES_1

WHERE CRS_PREREQS IS NOT NULL;

PROGRAM_VERSION_ASTRA

CREATE VIEW PROGRAM_VERSION_ASTRA AS

SELECT ACAD_PROGRAM_REQMTS.ID SisKey,

ACPR_ACAD_PROGRAM Program,

ACPR_CATALOG CatalogVersion,

X_ACPR_ASTRA_IS_CAPTIVE IsCaptive,

X_ACPR_MIN_GRADE MinGrade,

X_ACPR_ASTRA_FALSE SingleEntryReuse,

ACPR_CRED RequiredCredHours,

ACPR_INSTITUTION_CRED InstitutionCreditHours,

ACPR_MIN_GPA MinGPA,

X_ACPR_ASTRA_IS_CAPTIVE IsActive

FROM ACAD_PROGRAM_REQMTS;

RULE_TO_PROGRAM_VERSION_ASTRA

CREATE VIEW RULE_TO_PROGRAM_VERSION_ASTRA AS

SELECT ACAD_PROGRAM_REQMTS.ID ProgramVersionId,

X_ACPR_TOP_REQMT_BLOCK RuleId,

X_ACPR_ASTRA_SISKEY SisKey

FROM ACAD_PROGRAM_REQMTS;

RULE_RESTRICTIONS_ASTRA

CREATE VIEW RULE_RESTRICTIONS_ASTRA AS

SELECT ACRB_LABEL RuleName,

ACAD_PROGRAM_REQMTS.ID SisKey,

ACAD_REQMT_BLOCKS.ID RuleId,

ACRB_BUT_NOT_SUBJECTS Subject,

ACRB_BUT_NOT_COURSES Course,

X_ACRB_ASTRA_EFFECTIVE_DATE EffDate

FROM ACAD_REQMT_BLOCKS

LEFT OUTER JOIN ACAD_PROGRAM_REQMTS

ON ACAD_REQMT_BLOCKS.X_ACRB_FIRST_ACPR_POINTER = ACAD_PROGRAM_REQMTS.ID

UNNEST ACRB_BUT_NOT_SUBJECTS, ACRB_BUT_NOT_COURSES;

ASTRA_ACAD_REQMT_BLOCKS_NL0

CREATE VIEW ASTRA_ACAD_REQMT_BLOCKS_NL0 AS

SELECT SUB0.ID ,

CRS.ACRB_COURS_MV_KEY CRSEPOS,

FSUB.ACRB_FSUB_MV_KEY FSUBPOS,

FDEP.ACRB_FDEP_MV_KEY FDEPPOS,

FCRS.ACRB_FCRSVLV_MV_KEY FCRSVLVPOS,

FCOU.ACRB_FCOU_MV_KEY FCOUPOS,

SUB0.ACRB_PARENT_BLOCK RuleId,

AREA.ACR_TOP_REQMT_BLOCK Area,

FSUB.ACRB_FROM_SUBJECTS SubjectRange,

FCOU.ACRB_FROM_COURSES FromCourses,

CRS.ACRB_COURSES CourseRangeLow,

FDEP.ACRB_FROM_DEPTS CourseAttribute,

FCRS.ACRB_FROM_CRS_LEVELS CourseLevel,

SUB0.X_ACRB_ASTRA_COUNT_GPA CountInGPA,

SUB0.X_ACRB_WITHIN_INDICATOR RequiredLogicIndicator,

SUB0.X_ACRB_WITHIN_INDICATOR MaxTransferLogicIndicator,

SUB0.ACRB_MIN_CRED RequiredCreditHours,

SUB0.X_ACRB_MIN_GRADE MinGrade,

SUB0.X_ACRB_TRANSFER_CRED MaxTransferCreditHours

FROM ACAD_REQMT_BLOCKS_NL0 SUB0

INNER JOIN ACAD_REQMTS AREA ON AREA.ID = SUB0.ACRB_ACAD_REQMT

LEFT JOIN ACAD_REQMT_ACRB_COUR_MV_SU_1 CRS ON SUB0.ID=CRS.ID

LEFT JOIN ACAD_REQMT_ACRB_FSUB_MV_SUB FSUB ON SUB0.ID=FSUB.ID

LEFT JOIN ACAD_REQMT_ACRB_FDEP_MV_SUB FDEP ON SUB0.ID=FDEP.ID

LEFT JOIN ACAD_REQMT_ACRB_FCRS_MV_SUB FCRS ON SUB0.ID=FCRS.ID

LEFT JOIN ACAD_REQMT_ACRB_FCOU_MV_SUB FCOU ON SUB0.ID=FCOU.ID;

CREATE SUBTABLE ACAD_REQMT_BLOCKS_NL0 AS

SELECT ID,

X_ACRB_ASTRA_COUNT_GPA,

X_ACRB_WITHIN_INDICATOR,

X_ACRB_MIN_CRED,

X_ACRB_MIN_GRADE,

X_ACRB_TRANSFER_CRED,

ACRB_PARENT_BLOCK,

ACRB_ACAD_REQMT,

ACRB_MIN_CRED

FROM ACAD_REQMT_BLOCKS PRIMARY KEY ID;

CREATE SUBTABLE ACAD_REQMT_ACRB_COUR_MV_SU_1(ID,ACRB_COURS_MV_KEY,ACRB_COURSES) AS

SELECT ID,

NL1_KEY(ACRB_COURSES),

ACRB_COURSES

FROM ACAD_REQMT_BLOCKS

UNNEST NL1 ACRB_COURSES PRIMARY KEY ID,ACRB_COURS_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0;

CREATE SUBTABLE ACAD_REQMT_ACRB_FSUB_MV_SUB(ID,ACRB_FSUB_MV_KEY,ACRB_FROM_SUBJECTS) AS

SELECT ID,

NL1_KEY(ACRB_FROM_SUBJECTS),

ACRB_FROM_SUBJECTS

FROM ACAD_REQMT_BLOCKS

UNNEST NL1 ACRB_FROM_SUBJECTS PRIMARY KEY ID,ACRB_FSUB_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0;

CREATE SUBTABLE ACAD_REQMT_ACRB_FDEP_MV_SUB(ID,ACRB_FDEP_MV_KEY,ACRB_FROM_DEPTS ) AS

SELECT ID,

NL1_KEY(ACRB_FROM_DEPTS),

ACRB_FROM_DEPTS

FROM ACAD_REQMT_BLOCKS

UNNEST NL1 ACRB_FROM_DEPTS PRIMARY KEY ID,ACRB_FDEP_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0;

CREATE SUBTABLE ACAD_REQMT_ACRB_FCRS_MV_SUB(ID,ACRB_FCRSVLV_MV_KEY,ACRB_FROM_CRSE_LEVELS) AS

SELECT ID,

NL1_KEY(ACRB_FROM_CRSE_LEVELS),

ACRB_FROM_CRSE_LEVELS

FROM ACAD_REQMT_BLOCKS

UNNEST NL1 ACRB_FROM_CRSE_LEVELS PRIMARY KEY ID,ACRB_FCRSVLV_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0;

CREATE SUBTABLE ACAD_REQMT_ACRB_FCOU_MV_SUB(ID,ACRB_FCOU_MV_KEY,ACRB_FROM_COURSES) AS

SELECT ID,

NL1_KEY(ACRB_FROM_COURSES),

ACRB_FROM_COURSES

FROM ACAD_REQMT_BLOCKS

UNNEST NL1 ACRB_FROM_COURSES PRIMARY KEY ID,ACRB_FCOU_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0;

ASTRA_CUSTOMRULEBASE

CREATE VIEW ASTRA_CUSTOMRULEBASE AS

SELECT ARB.ID ParentRuleId,

RULES_1_NF_SUB.RULES_ID RuleId,

SUB1.CUSTOM_SUB_KEY POS,

SUB1.RL_CHECK_CONNECTOR Connector,

SUB1.RL_CHECK_DATA_ELEMENTS FieldName,

SUB1.RL_CHECK_OPERATORS Operator,

SUB1.RL_CHECK_VALUES CheckValues

FROM RULES_1_NF_SUB SUB0

INNER JOIN ACAD_REQMT_BLOCKS ARB

ON ARB.ACRB_ACAD_CRED_RULES = SUB0.RULES_ID

LEFT JOIN RULES_1_CUSTOM_RULE_SUB SUB1

ON SUB1.ID=SUB0.ID

WHERE ACRB_ACAD_CRED_RULES IS NOT NULL;

CREATE SUBTABLE RULES_1_CUSTOM_RULE_SUB(ID,CUSTOM_SUB_KEY,RL_CHECK_CONNECTOR,RL_CHECK_OPERATORS,RL_CHECK_VALUES,RL_CHECK_DATA_ELEMENTS) AS

SELECT ID,NL1_KEY

(RL_CHECK_CONNECTOR,RL_CHECK_OPERATORS,RL_CHECK_VALUES,RL_CHECK_DATA_ELEMENTS),

RL_CHECK_CONNECTOR,

RL_CHECK_OPERATORS,

RL_CHECK_VALUES,

RL_CHECK_DATA_ELEMENTS

FROM RULES_1

UNNEST NL1 RL_CHECK_CONNECTOR,

RL_CHECK_OPERATORS,

RL_CHECK_VALUES,

RL_CHECK_DATA_ELEMENTS

PRIMARY KEY ID,CUSTOM_SUB_KEY

FOREIGN KEY ID REFERENCES RULES_1_NF_SUB;

Page url: ?platinum_analytics_views_for_d.htm