VIEW: APPS.IGS_PS_OFR_PAT_UNITSET_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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