1.Base Views and Permissions

COURSE_SECTIONS (Select permission)

 ID

 SEC.CAPACITY

 ASTRA.SEC.ACTUAL.ENROLLMENT **Custom virtual field

 SEC.COURSE.NO

 SEC.LOCATION

 SEC.NO

 SEC.SHORT.TITLE

 SEC.SUBJECT

 SEC.TERM

 COURSE.SECTIONS.CHGDATE

 COURSE.SECTIONS.ID

 SEC.XLIST

 SEC.CURRENT.STATUS

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

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

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

ID1,

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;

 

PROGRAM RULE IMPORT - NOTE:  Only required for Sectioning

 

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: ?astra_schedule_views_for_datat.htm