ARCHIVE - Platinum Analytics Views for 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;

ES01_LEVELS

CREATE VIEW ES01_LEVELS AS SELECT A.ACAD_LEVELS_ID, A.ACLV_DESC FROM ACAD_LEVELS_SQL A;

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;

MWC_TEST

CREATE VIEW MWC_TEST AS SELECT A.LAST_NAME, A.FIRST_NAME FROM PERSON_SQL A;

TOPVIEW_TEST

CREATE VIEW TOPVIEW_TEST AS SELECT A.LAST_NAME, A.FIRST_NAME FROM PERSON_SQL A;

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;

TERMS IMPORT

TERMS_1_ASTRA (linked to STUDENTS_1_ASTRA_COURSE_SUM)

CREATE VIEW TERMS_1_ASTRA AS SELECT ID,TERM_DESC,TERM_START_DATE,TERM_END_DATE,TERM_REPORTING_TERM FROM TERMS_1;

ES01_TERMS

CREATE VIEW ES01_TERMS AS SELECT A.TERM_START_DATE, A.TERMS_ID FROM TERMS_SQL A;

ES02_DEPTS

CREATE VIEW ES02_DEPTS AS SELECT A.DEPTS_ID, A.DEPTS_DESC FROM DEPTS_SQL A;

ES02_TERMS

CREATE VIEW ES02_TERMS AS SELECT A.TERM_START_DATE, A.TERMS_ID FROM TERMS_SQL A;

COURSE IMPORT

COURSES_1_ASTRA (Linked to STUDENTS_1_ASTRA_ACAHIST)

CREATE VIEW COURSES_1_ASTRA AS SELECT CRS_NAME, X_CRSASTRA.X_CRSASTRA_DESC, COURSES_1.ID, COURSES_1.CRS_NO, 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,CRS_NO,CRS_SUBJECT FROM COURSES_1;

COURSES_1_ASTRA_MEETINGTYPES

CREATE VIEW COURSES_1_ASTRA_MEETINGTYPES AS SELECT COURSES_1.ID, CRS_SUBJECT, 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;

COURSES_1_CRS_DESC_MV_SUB

CREATE SUBTABLE COURSES_1_CRS_DESC_MV_SUB AS SELECT ID0,CRS_DESC FROM COURSES_1 UNNEST NL1 CRS_DESC FOREIGN KEY ID0 REFERENCES COURSES_1_NF_SUB_4;

COURSES_1_CRS_STATU_MV_SUB

CREATE SUBTABLE COURSES_1_CRS_STATU_MV_SUB AS SELECT ID0,CRS_STATUS FROM COURSES_1 UNNEST NL1 CRS_STATUS FOREIGN KEY ID0 REFERENCES COURSES_1_NF_SUB_2;

COURSES_1_CRS_TITLE_MV_SUB

CREATE SUBTABLE COURSES_1_CRS_TITLE_MV_SUB AS SELECT ID0,CRS_TITLE FROM COURSES_1 UNNEST NL1 CRS_TITLE FOREIGN KEY ID0 REFERENCES COURSES_1_NF_SUB_1;

COURSES_1_NF_SUB

CREATE SUBTABLE COURSES_1_NF_SUB AS SELECT ID0 FROM COURSES_1 PRIMARY KEY ID0;

COURSES_1_NF_SUB_1 (Linked to COURSES_1_CRS_TITLE_MV_SUB)

CREATE SUBTABLE COURSES_1_NF_SUB_1 AS SELECT ID0 FROM COURSES_1 PRIMARY KEY ID0;

COURSES_1_NF_SUB_2 (Linked to COURSES_1_CRS_STATU_MV_SUB)

CREATE SUBTABLE COURSES_1_NF_SUB_2 AS SELECT ID0 FROM COURSES_1 PRIMARY KEY ID0;

COURSES_1_NF_SUB_3

CREATE SUBTABLE COURSES_1_NF_SUB_3 AS SELECT ID0 FROM COURSES_1 PRIMARY KEY ID0;

COURSES_1_NF_SUB_4 (Linked to COURSES_1_CRS_DESC_MV_SUB)

CREATE SUBTABLE COURSES_1_NF_SUB_4 AS SELECT ID0 FROM COURSES_1 PRIMARY KEY ID0;

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_BASE

CREATE VIEW COURSE_SECTIONS_ASTRA_BASE 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_CSM_ASTRA_SIS_KEY, X_CSM_ASTRA_ROOM_SIS_KEY, 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;

COURSE_SEC_FACULTY_ASTRA

CREATE VIEW COURSE_SEC_FACULTY_ASTRA AS SELECT ID, CSF_FACULTY, CSF_COURSE_SECTION FROM COURSE_SEC_FACULTY;

SEC_MEP

CREATE VIEW SEC_MEP AS SELECT SEC_NAME, COURSE_SECTIONS.ID, ASTRA_SEC_ACTUAL_ENROLLMENT FROM COURSE_SECTIONS;

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_ACAHIST

CREATE VIEW STUDENTS_1_ASTRA_ACAHIST AS SELECT STC_PERSON_ID StudentId, STC_TERM TermCode, STC_SUBJECT Subject, STC_COURSE CourseSisKey, COURSES_1_ASTRA.CRS_NO 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, STC_CRED_TYPE FROM STUDENT_ACAD_CRED INNER JOIN COURSES_1_ASTRA ON COURSES_1_ASTRA.ID = STUDENT_ACAD_CRED.STC_COURSE;

STUDENTS_1_ASTRA_DEPT_DATA

CREATE VIEW STUDENTS_1_ASTRA_DEPT_DATA 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_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 INNER JOIN TERMS_1_ASTRA ON STUDENT_TERMS.STTR_TERM = TERMS_1_ASTRA.ID WHERE TERMS_1_ASTRA.ID <> TERMS_1_ASTRA.TERM_REPORTING_TERM;

ES01_TEMPLATE

CREATE VIEW ES01_TEMPLATE AS SELECT A.STTR_STUDENT, A.STTR_TERM, A.STTR_ACAD_LEVEL, A.STTR_ACTIVE_CRED, A.STTR_ACTIVE_PROGRAMS, B.LAST_NAME, B.FIRST_NAME, B.ID FROM STUDENT_TERMS_SQL A, PERSON_SQL B UNNEST A.STTR_ACTIVE_PROGRAMS WHERE A.STTR_STUDENT = B.ID AND A.STTR_ACTIVE_CRED >= 0;

PROGRAM RULE IMPORT

PROGRAM_RULES_ASTRA

CREATE VIEW PROGRAM_RULES_ASTRA AS SELECT ACAD_REQMT_BLOCKS.ID SisKey, ACAD_REQMT_BLOCKS.ACRB_LABEL RuleLabel, 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, 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;

PROGRAM_VERSION_REST_ASTRA

CREATE VIEW PROGRAM_VERSION_REST_ASTRA AS SELECT ACAD_PROGRAM_REQMTS.ID ProgramVersion, ACAD_REQMT_BLOCKS.ID SisKey, 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;

RULE_CONTROLS_ASTRA

CREATE VIEW RULE_CONTROLS_ASTRA AS SELECT ACAD_REQMT_BLOCKS.ID RuleId, ACAD_PROGRAM_REQMTS.ID SisKey, X_ACRB_ASTRA_EFFECTIVE_DATE EffDate, ACRB_LABEL Name, X_ACPR_COMPARE_VALUE CompareValue, X_ACPR_COMPARE_FIELD_TYPE CompareFieldType, X_ACPR_ASTRA_RECORD_TYPE RecordType, X_ACPR_ASTRA_IS_CAPTIVE Operand FROM ACAD_REQMT_BLOCKS LEFT OUTER JOIN ACAD_PROGRAM_REQMTS ON ACAD_REQMT_BLOCKS.X_ACRB_FIRST_ACPR_POINTER = ACAD_PROGRAM_REQMTS.ID UNNEST X_ACPR_COMPARE_VALUE WHERE ACPR_ACAD_CRED_RULES = 'DA.NODEV';

RULE_ELEMENTS_ASTRA

CREATE VIEW RULE_ELEMENTS_ASTRA AS SELECT ACAD_REQMT_BLOCKS.ID SisKey, ACAD_REQMT_BLOCKS.ID ElementKey, ACRB_PARENT_BLOCK RuleId, ACAD_REQMTS.ACR_TOP_REQMT_BLOCK Area, ACRB_FROM_SUBJECTS SubjectRange, ACRB_COURSES CourseRangeLow, X_ACRB_ASTRA_COUNT_GPA CountInGPA, X_ACRB_WITHIN_INDICATOR RequiredLogicIndicator, X_ACRB_WITHIN_INDICATOR MaxTransferLogicIndicator, ACRB_MIN_CRED RequiredCreditHours, X_ACRB_MIN_GRADE MinGrade, X_ACRB_TRANSFER_CRED MaxTransferCreditHours FROM ACAD_REQMT_BLOCKS INNER JOIN ACAD_REQMTS ON ACAD_REQMT_BLOCKS.ACRB_ACAD_REQMT = ACAD_REQMTS.ID UNNEST ACRB_COURSES, ACRB_FROM_SUBJECTS;

RULE_ELEMENT_REST_ASTRA

CREATE VIEW RULE_ELEMENT_REST_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 ValidToDate, X_ACRB_ASTRA_VALID_DATE ValidFromDate, 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;

RULE_REUSE_ASTRA

CREATE VIEW RULE_REUSE_ASTRA AS SELECT ACAD_PROGRAM_REQMTS.ID ProgramVersionId, ACAD_REQMT_BLOCKS.ID RuleId, ACAD_REQMT_BLOCKS.ID SisKey, X_ACRB_WITHIN_INDICATOR AttributeReuse, X_ACRB_COURSE_REUSE_FLAG CourseReuse, X_ACRB_WITHIN_INDICATOR WithinIndicator FROM ACAD_REQMT_BLOCKS LEFT OUTER JOIN ACAD_PROGRAM_REQMTS ON ACAD_REQMT_BLOCKS.X_ACRB_FIRST_ACPR_POINTER = ACAD_PROGRAM_REQMTS.ID;

COMPLEX_PREREQ_ASTRA

CREATE VIEW COMPLEX_PREREQ_ASTRA AS SELECT ACAD_REQMTS. ACR_TOP_REQMT_BLOCK, COURSES_1.ID FROM COURSES_1 INNER JOIN ACAD_REQMTS ON ACAD_REQMTS.ID = COURSES_1.CRS_PREREQS;

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_ACRB_RULES

CREATE VIEW ASTRA_ACRB_RULES AS SELECT ACAD_REQMT_BLOCKS.ID, RL_PRIMARY_VIEW, RL_CHECK_DATA_ELEMENTS, RL_CHECK_OPERATORS, RL_CHECK_VALUES FROM ACAD_REQMT_BLOCKS INNER JOIN RULES_1 ON RULES_1.RULES_ID = ACRB_ACAD_CRED_RULES UNNEST RL_CHECK_DATA_ELEMENTS, RL_CHECK_OPERATORS, RL_CHECK_VALUES WHERE ACRB_ACAD_CRED_RULES IS NOT NULL;

ASTRA_ACAD_REQMT_BLOCKS_NL0

create view ASTRA_ACAD_REQMT_BLOCKS_NL0 AS select SUB0.ID , SUB0.ACRB_LABEL, 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;

ACAD_REQMT_BLOCKS_NL0 (Linked to ACAD_REQMT_ACRB_COUR_MV_SU_1; ACAD_REQMT_ACRB_FCOU_MV_SUB; ACAD_REQMT_ACRB_FCRS_MV_SUB; ACAD_REQMT_ACRB_FDEP_MV_SUB; ACAD_REQMT_ACRB_FSUB_MV_SUB)

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,ACRB_LABEL FROM ACAD_REQMT_BLOCKS PRIMARY KEY ID;

ACAD_REQMT_ACRB_COUR_MV_SU_1

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; ;

ACAD_REQMT_ACRB_FSUB_MV_SUB

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; ;

ACAD_REQMT_ACRB_FDEP_MV_SUB

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; ;

ACAD_REQMT_ACRB_FCRS_MV_SUB

CREATE SUBTABLE ACAD_REQMT_ACRB_FCRS_MV_SUB(ID,ACRB_FCRSVLV_MV_KEY,ACRB_FROM_CRS_LEVELS) AS SELECT ID,NL1_KEY(ACRB_FROM_CRS_LEVELS),ACRB_FROM_CRS_LEVELS FROM ACAD_REQMT_BLOCKS UNNEST NL1 ACRB_FROM_CRS_LEVELS PRIMARY KEY ID,ACRB_FCRSVLV_MV_KEY FOREIGN KEY ID REFERENCES ACAD_REQMT_BLOCKS_NL0; ;

ACAD_REQMT_ACRB_FCOU_MV_SUB

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;

RULES_1_CUSTOM_RULE_SUB

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; ;

RULES_1_NF_SUB (Linked to RULES_1_CUSTOM_RULE_SUB)

CREATE SUBTABLE RULES_1_NF_SUB AS SELECT ID,RULES_ID FROM RULES_1 PRIMARY KEY ID; ;

Help URL: http://www.yourdomain.com/help/index.html?archiveplatinum_analytics_views_for_colleague.htm