1.Base Views and Permissions

COURSE_SECTIONS (Select permission)

 ID

 SEC.CAPACITY

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

 SEC.COURSE.NO

 SEC.LOCATION

 SEC.NO

 SEC.SHORT.TITLE

 SEC.SUBJECT

 SEC.TERM

 COURSE.SECTIONS.CHGDATE

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

 SEC.XLIST

SEC.STATUS

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

SEC.ACTIVE.STUDENTS

 

COURSE_SEC_MEETING (Astra user Select and Update (for building and room))

 ID

 COURSE.SEC.MEETING.CHGDATE

 COURSE.SEC.MEETING.ID

 CSM.BLDG

 CSM.COURSE.SECTION

 CSM.END.DATE

 CSM.ROOM

 CSM.START.TIME

 CSM.START.DATE

 CSM.END.TIME

 CSM.MONDAY

 CSM.TUESDAY

 CSM.WEDNESDAY

 CSM.THURSDAY

 CSM.FRIDAY

 CSM.SATURDAY

 CSM.SUNDAY

 CSM.INSTR.METHOD

 SEC.LOCATION

 

COURSE_SEC_FACULTY (Select permission)

 ID

 CSF.FACULTY

 CSF.COURSE.SECTION

 

PERSON (Select permission)

 ID

 LAST.NAME

 MIDDLE.NAME

 FIRST.NAME

2.Additional Views

LOOKUPS IMPORT - NOTE:  Only required for Sectioning

 

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;

 

COURSE IMPORT - NOTE:  Only required for Sectioning

 

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;

 

SECTION IMPORT

 

COURSE_SECTIONS_ASTRA

CREATE VIEW COURSE_SECTIONS_ASTRA AS SELECT

ID,

SEC_CAPACITY,

SEC_COURSE_NO,

SEC_LOCATION,

SEC_NO,

SEC_SHORT_TITLE,

SEC_SUBJECT,

SEC_TERM,

COURSE_SECTIONS_CHGDATE,

COURSE_SECTIONS_ID,

ASTRA_SEC_ACTUAL_ENROLLMENT,

SEC_ACTIVE_STUDENTS,

SEC_XLIST,

SEC_CURRENT_STATUS

FROM COURSE_SECTIONS;

 

COURSE_SEC_MEETING_ASTRA

CREATE VIEW COURSE_SEC_MEETING_ASTRA AS SELECT CSM.ID,

CSM.COURSE_SEC_MEETING_CHGDATE,

CSM.COURSE_SEC_MEETING_ID,

CSM.CSM_BLDG,

CSM.CSM_COURSE_SECTION,

CSM.CSM_END_DATE,

CSM.CSM_ROOM,

CSM.CSM_START_TIME,

CSM.CSM_START_DATE,

CSM.CSM_END_TIME,

CSM.CSM_INSTR_METHOD,

CSM.CSM_MONDAY,

CSM.CSM_TUESDAY,

CSM.CSM_WEDNESDAY,

CSM.CSM_THURSDAY,

CSM.CSM_FRIDAY,

CSM.CSM_SATURDAY,

CSM.CSM_SUNDAY,

SEC.SEC_LOCATION,

SEC.SEC_TERM

FROM COURSE_SEC_MEETING CSM JOIN COURSE_SECTIONS SEC ON CSM.CSM_COURSE_SECTION = SEC.ID;

 

CROSSLISTKEYINFO_ASTRA

CREATE VIEW CROSSLISTKEYINFO_ASTRA AS SELECT

CS.COURSE_SECTIONS_ID COURSE_SECTIONS_ID,

CS.SEC_TERM SEC_TERM,

CS.SEC_XLIST SEC_XLIST

FROM COURSE_SECTIONS_ASTRA CS

WHERE CS.SEC_XLIST IS NOT NULL;

 

CROSSLIST_ASTRA

CREATE VIEW CROSSLIST_ASTRA AS SELECT

CLK.SEC_XLIST,

CLK.SEC_TERM,

CLK.COURSE_SECTIONS_ID,

CLK2.COURSE_SECTIONS_ID CXNUM

FROM CROSSLISTKEYINFO_ASTRA CLK,

CROSSLISTKEYINFO_ASTRA CLK2

WHERE CLK.SEC_XLIST = CLK2.SEC_XLIST AND CLK.SEC_TERM = CLK2.SEC_TERM AND  CLK.COURSE_SECTIONS_ID <> CLK2.COURSE_SECTIONS_ID;

 

PERSON_ASTRA

CREATE VIEW PERSON_ASTRA AS SELECT

LAST_NAME,

FIRST_NAME,

MIDDLE_NAME,

ID

FROM PERSON_1;

 

COURSE_SEC_FACULTY_ASTRA

CREATE VIEW COURSE_SEC_FACULTY_ASTRA AS SELECT

ID,

CSF_FACULTY,

CSF_COURSE_SECTION

FROM COURSE_SEC_FACULTY;

 

COURSE_SEC_INSTRUCTOR_ASTRA

CREATE VIEW COURSE_SEC_INSTRUCTOR_ASTRA AS SELECT

P.ID,

CSF.CSF_COURSE_SECTION COURSE_SECTIONS_ID,

P.LAST_NAME LAST_NAME,

P.FIRST_NAME FIRST_NAME,

P.MIDDLE_NAME MIDDLE_NAME FROM

PERSON_ASTRA P JOIN COURSE_SEC_FACULTY_ASTRA CSF ON

(P.ID = CSF.CSF_FACULTY) WHERE CSF_FACULTY is NOT NULL;

 

COURSE_MEETINGTYPES_ASTRA

CREATE VIEW COURSE_MEETING_TYPES_ASTRA AS SELECT

SEC.SEC_TERM,

SEC.SEC_LOCATION,

SEC.COURSE_SECTIONS_ID,

CSM.COURSE_SEC_MEETING_ID,

SEC.SEC_SUBJECT,

SEC.SEC_COURSE_NO,

CSM.CSM_INSTR_METHOD,

CSM.CSM_COURSE_SECTION

FROM COURSE_SECTIONS SEC

JOIN COURSE_SEC_MEETING CSM  on SEC.ID = CSM.CSM_COURSE_SECTION;

 

STUDENT IMPORT - NOTE:  Only required for Sectioning

 

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

 

PROGRAM RULE IMPORT - NOTE:  Only required for Sectioning

 

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;

 

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