[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This view describes the offering pattern of a program offering option. It also describes a unit-set, being a logical sub-grouping of program. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT COOUSV.UNIT_SET_CD
, COOUSV.US_VERSION_NUMBER
, COP.COURSE_CD
, COP.VERSION_NUMBER
, COP.CAL_TYPE
, COP.CI_SEQUENCE_NUMBER
, COP.LOCATION_CD
, COP.ATTENDANCE_MODE
, COP.ATTENDANCE_TYPE
, APAPC.ADM_CAL_TYPE
, APAPC.ADM_CI_SEQUENCE_NUMBER
, APAPC.ADMISSION_CAT
, APAPC.S_ADMISSION_PROCESS_TYPE
, COP.CREATED_BY
, COP.CREATION_DATE
, COP.LAST_UPDATED_BY
, COP.LAST_UPDATE_DATE
, COP.LAST_UPDATE_LOGIN
FROM IGS_PS_OFR_PAT COP
, IGS_CA_INST_REL CIR
, IGS_AD_PRD_AD_PRC_CA APAPC
, IGS_PS_OFR_OPT_UNIT_SET_V COOUSV
, IGS_PS_OFR_UNIT_SET COUS
, IGS_EN_UNIT_SET_STAT USS
WHERE COP.CAL_TYPE = CIR.SUP_CAL_TYPE
AND COP.CI_SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER
AND CIR.SUB_CAL_TYPE = APAPC.ADM_CAL_TYPE
AND CIR.SUB_CI_SEQUENCE_NUMBER = APAPC.ADM_CI_SEQUENCE_NUMBER
AND COOUSV.COURSE_CD = COP.COURSE_CD
AND COOUSV.CRV_VERSION_NUMBER = COP.VERSION_NUMBER
AND COOUSV.CAL_TYPE = COP.CAL_TYPE
AND COOUSV.LOCATION_CD = COP.LOCATION_CD
AND COOUSV.ATTENDANCE_MODE = COP.ATTENDANCE_MODE
AND COOUSV.ATTENDANCE_TYPE = COP.ATTENDANCE_TYPE
AND COOUSV.EXPIRY_DT IS NULL
AND USS.UNIT_SET_STATUS = COOUSV.UNIT_SET_STATUS
AND USS.S_UNIT_SET_STATUS = 'ACTIVE'
AND COUS.COURSE_CD = COOUSV.COURSE_CD
AND COUS.CRV_VERSION_NUMBER = COOUSV.CRV_VERSION_NUMBER
AND COUS.CAL_TYPE = COOUSV.CAL_TYPE
AND COUS.UNIT_SET_CD = COOUSV.UNIT_SET_CD
AND COUS.US_VERSION_NUMBER = COOUSV.US_VERSION_NUMBER
AND COUS.ONLY_AS_SUB_IND = 'N'
AND NOT EXISTS ( SELECT 'X' FROM IGS_PS_OF_UNT_SET_RL COUSR WHERE COUSR.COURSE_CD = COOUSV.COURSE_CD
AND COUSR.CRV_VERSION_NUMBER = COOUSV.CRV_VERSION_NUMBER
AND COUSR.CAL_TYPE = COOUSV.CAL_TYPE
AND COUSR.SUB_UNIT_SET_CD = COOUSV.UNIT_SET_CD
AND COUSR.SUB_US_VERSION_NUMBER = COOUSV.US_VERSION_NUMBER)
AND (( 0 < ( SELECT COUNT(*) FROM IGS_PS_COO_AD_UNIT_S CACUS WHERE CACUS.COURSE_CD = COOUSV.COURSE_CD
AND CACUS.CRV_VERSION_NUMBER = COOUSV.CRV_VERSION_NUMBER
AND CACUS.CAL_TYPE = COOUSV.CAL_TYPE
AND CACUS.ADMISSION_CAT = APAPC.ADMISSION_CAT)
AND (COOUSV.UNIT_SET_CD
, COOUSV.US_VERSION_NUMBER
, COOUSV.LOCATION_CD
, COOUSV.ATTENDANCE_MODE
, COOUSV.ATTENDANCE_TYPE) IN ( SELECT CACUS1.UNIT_SET_CD
, CACUS1.US_VERSION_NUMBER
, CACUS1.LOCATION_CD
, CACUS1.ATTENDANCE_MODE
, CACUS1.ATTENDANCE_TYPE FROM IGS_PS_COO_AD_UNIT_S CACUS1 WHERE CACUS1.COURSE_CD = COOUSV.COURSE_CD
AND CACUS1.CRV_VERSION_NUMBER = COOUSV.CRV_VERSION_NUMBER
AND CACUS1.CAL_TYPE = COOUSV.CAL_TYPE
AND CACUS1.ADMISSION_CAT = APAPC.ADMISSION_CAT)) OR 0 = ( SELECT COUNT(*) FROM IGS_PS_COO_AD_UNIT_S CACUS2 WHERE CACUS2.COURSE_CD = COOUSV.COURSE_CD
AND CACUS2.CRV_VERSION_NUMBER = COOUSV.CRV_VERSION_NUMBER
AND CACUS2.CAL_TYPE = COOUSV.CAL_TYPE
AND CACUS2.ADMISSION_CAT = APAPC.ADMISSION_CAT))