VIEW: APPS.IGS_DA_EXPL_PRG_CAT_V

Source

View Text - Preformatted

SELECT rqst.batch_id, NVL(ci.description, lk.meaning) description, ci.cal_type, ci.sequence_number, ci.start_dt, ci.end_dt FROM igs_ca_inst_all ci, igs_ca_type ct, igs_ca_stat cs, igs_pe_stat_details psd, igs_da_req_ftrs rf, igs_da_rqst rqst, igs_da_req_stdnts stdnt, fnd_lookup_values lk WHERE ci.cal_type = ct.cal_type AND ct.s_cal_cat IN ('ACADEMIC','LOAD') AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND lk.lookup_type = 'UNDEFINED' AND lk.lookup_code = 'UNDEFINED' AND rqst.request_mode = 'S' AND rf.batch_id = rqst.batch_id AND rf.feature_name = 'WCC' AND rf.feature_value IN ('PCDR','PCPY','MDDR','MDPY') AND stdnt.batch_id = rqst.batch_id AND psd.person_id(+) = stdnt.person_id AND ((DECODE(rf.feature_value, 'MDDR', psd.matr_cal_type, 'MDPY', psd.matr_cal_type, psd.catalog_cal_type) IS NULL AND ci.start_dt >= trunc(sysdate)) OR ci.start_dt >= (SELECT ci1.start_dt FROM igs_ca_inst_all pci, igs_ca_type pct, igs_ca_inst_all ci1, igs_ca_type cat1, igs_ca_stat cs1 WHERE pci.cal_type = DECODE(rf.feature_value, 'MDDR', psd.matr_cal_type, 'MDPY', psd.matr_cal_type, psd.catalog_cal_type) AND pci.sequence_number = DECODE(rf.feature_value, 'MDDR', psd.matr_sequence_number, 'MDPY', psd.matr_sequence_number, psd.catalog_sequence_number) AND pct.cal_type = pci.cal_type AND cat1.cal_type = ci1.cal_type AND cat1.s_cal_cat IN ('ACADEMIC','LOAD') AND cs1.cal_status = ci1.cal_status AND cs1.s_cal_status = 'ACTIVE' AND ((ci1.cal_type = pci.cal_type AND ci1.sequence_number = pci.sequence_number ) OR (pct.cal_type = 'LOAD' AND (ci1.cal_type, ci1.sequence_number) IN (SELECT cir.sup_cal_type, cir.sup_ci_sequence_number FROM igs_ca_inst_rel cir WHERE cir.sub_cal_type = pci.cal_type AND cir.sub_ci_sequence_number = pci.sequence_number ) ) ) ) AND ci.end_dt <= DECODE(rf.feature_value, 'MDDR', SYSDATE, 'PCDR', SYSDATE, SYSDATE + 365) ) UNION SELECT DISTINCT rqst.batch_id, NVL(ci.description, lk.meaning) description, ci.cal_type, ci.sequence_number, ci.start_dt, ci.end_dt FROM igs_ca_inst_all ci, igs_ca_type cat, igs_ca_stat cs, igs_pe_stat_details psd, igs_da_req_ftrs rf, igs_da_rqst rqst, igs_da_req_stdnts stdnt, fnd_lookup_values lk, igs_lookups_view lkv WHERE lk.lookup_type = 'UNDEFINED' AND lk.lookup_code = 'UNDEFINED' AND rqst.request_mode = 'S' AND rf.batch_id = rqst.batch_id AND rf.feature_name = 'WCC' AND rf.feature_value = 'INDV' AND lkv.lookup_type = 'IGS_DA_WIF_INDV_CAT' AND NVL(lkv.end_date_active,SYSDATE) <= SYSDATE AND stdnt.batch_id = rqst.batch_id AND psd.person_id(+) = stdnt.person_id AND ci.cal_type = cat.cal_type AND cat.s_cal_cat IN ('ACADEMIC','LOAD') AND cs.cal_status = ci.cal_status AND cs.s_cal_status = 'ACTIVE' AND ((lkv.lookup_code = 'DCAT' AND ci.start_dt <= SYSDATE AND nvl ( ci.end_dt, sysdate) >= sysdate ) OR (lkv.lookup_code = 'OCAT' AND ci.start_dt <= SYSDATE + 365) OR (lkv.lookup_code = 'PCAT' AND psd.catalog_cal_type = ci.cal_type AND psd.catalog_sequence_number = ci.sequence_number AND nvl ( ci.end_dt, sysdate) >= sysdate ) OR (lkv.lookup_code = 'MCAT' AND psd.matr_cal_type = ci.cal_type AND psd.matr_sequence_number = ci.sequence_number AND nvl ( ci.end_dt, sysdate) >= sysdate ) OR (lkv.lookup_code = 'ACAT' AND nvl ( ci.end_dt, sysdate) >= sysdate AND (ci.cal_type, ci.sequence_number) IN (SELECT NVL(spa.catalog_cal_type, ci.cal_type), NVL(spa.catalog_seq_num, ci.sequence_number) FROM igs_en_stdnt_ps_att_all spa WHERE spa.person_id = stdnt.person_id AND spa.course_attempt_status IN ('ENROLLED','INACTIVE') ) ) OR (lkv.lookup_code = 'UCAT' AND nvl ( ci.end_dt, sysdate) >= sysdate AND (ci.cal_type, ci.sequence_number) IN (SELECT NVL(susa.catalog_cal_type, ci.cal_type), NVL(susa.catalog_seq_num, ci.sequence_number) FROM igs_as_su_setatmpt susa, igs_en_unit_set_all us, igs_en_unit_set_cat usc WHERE susa.person_id = stdnt.person_id AND susa.student_confirmed_ind = 'Y' AND susa.end_dt IS NOT NULL AND us.unit_set_cd = susa.unit_set_cd AND us.version_number = susa.us_version_number AND usc.unit_set_cat = us.unit_set_cat AND usc.s_unit_set_cat = 'MAJOR' ) ) )

View Text - HTML Formatted

SELECT RQST.BATCH_ID
, NVL(CI.DESCRIPTION
, LK.MEANING) DESCRIPTION
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, CI.START_DT
, CI.END_DT
FROM IGS_CA_INST_ALL CI
, IGS_CA_TYPE CT
, IGS_CA_STAT CS
, IGS_PE_STAT_DETAILS PSD
, IGS_DA_REQ_FTRS RF
, IGS_DA_RQST RQST
, IGS_DA_REQ_STDNTS STDNT
, FND_LOOKUP_VALUES LK
WHERE CI.CAL_TYPE = CT.CAL_TYPE
AND CT.S_CAL_CAT IN ('ACADEMIC'
, 'LOAD')
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND LK.LOOKUP_TYPE = 'UNDEFINED'
AND LK.LOOKUP_CODE = 'UNDEFINED'
AND RQST.REQUEST_MODE = 'S'
AND RF.BATCH_ID = RQST.BATCH_ID
AND RF.FEATURE_NAME = 'WCC'
AND RF.FEATURE_VALUE IN ('PCDR'
, 'PCPY'
, 'MDDR'
, 'MDPY')
AND STDNT.BATCH_ID = RQST.BATCH_ID
AND PSD.PERSON_ID(+) = STDNT.PERSON_ID
AND ((DECODE(RF.FEATURE_VALUE
, 'MDDR'
, PSD.MATR_CAL_TYPE
, 'MDPY'
, PSD.MATR_CAL_TYPE
, PSD.CATALOG_CAL_TYPE) IS NULL
AND CI.START_DT >= TRUNC(SYSDATE)) OR CI.START_DT >= (SELECT CI1.START_DT
FROM IGS_CA_INST_ALL PCI
, IGS_CA_TYPE PCT
, IGS_CA_INST_ALL CI1
, IGS_CA_TYPE CAT1
, IGS_CA_STAT CS1
WHERE PCI.CAL_TYPE = DECODE(RF.FEATURE_VALUE
, 'MDDR'
, PSD.MATR_CAL_TYPE
, 'MDPY'
, PSD.MATR_CAL_TYPE
, PSD.CATALOG_CAL_TYPE)
AND PCI.SEQUENCE_NUMBER = DECODE(RF.FEATURE_VALUE
, 'MDDR'
, PSD.MATR_SEQUENCE_NUMBER
, 'MDPY'
, PSD.MATR_SEQUENCE_NUMBER
, PSD.CATALOG_SEQUENCE_NUMBER)
AND PCT.CAL_TYPE = PCI.CAL_TYPE
AND CAT1.CAL_TYPE = CI1.CAL_TYPE
AND CAT1.S_CAL_CAT IN ('ACADEMIC'
, 'LOAD')
AND CS1.CAL_STATUS = CI1.CAL_STATUS
AND CS1.S_CAL_STATUS = 'ACTIVE'
AND ((CI1.CAL_TYPE = PCI.CAL_TYPE
AND CI1.SEQUENCE_NUMBER = PCI.SEQUENCE_NUMBER ) OR (PCT.CAL_TYPE = 'LOAD'
AND (CI1.CAL_TYPE
, CI1.SEQUENCE_NUMBER) IN (SELECT CIR.SUP_CAL_TYPE
, CIR.SUP_CI_SEQUENCE_NUMBER
FROM IGS_CA_INST_REL CIR
WHERE CIR.SUB_CAL_TYPE = PCI.CAL_TYPE
AND CIR.SUB_CI_SEQUENCE_NUMBER = PCI.SEQUENCE_NUMBER ) ) ) )
AND CI.END_DT <= DECODE(RF.FEATURE_VALUE
, 'MDDR'
, SYSDATE
, 'PCDR'
, SYSDATE
, SYSDATE + 365) ) UNION SELECT DISTINCT RQST.BATCH_ID
, NVL(CI.DESCRIPTION
, LK.MEANING) DESCRIPTION
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, CI.START_DT
, CI.END_DT
FROM IGS_CA_INST_ALL CI
, IGS_CA_TYPE CAT
, IGS_CA_STAT CS
, IGS_PE_STAT_DETAILS PSD
, IGS_DA_REQ_FTRS RF
, IGS_DA_RQST RQST
, IGS_DA_REQ_STDNTS STDNT
, FND_LOOKUP_VALUES LK
, IGS_LOOKUPS_VIEW LKV
WHERE LK.LOOKUP_TYPE = 'UNDEFINED'
AND LK.LOOKUP_CODE = 'UNDEFINED'
AND RQST.REQUEST_MODE = 'S'
AND RF.BATCH_ID = RQST.BATCH_ID
AND RF.FEATURE_NAME = 'WCC'
AND RF.FEATURE_VALUE = 'INDV'
AND LKV.LOOKUP_TYPE = 'IGS_DA_WIF_INDV_CAT'
AND NVL(LKV.END_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND STDNT.BATCH_ID = RQST.BATCH_ID
AND PSD.PERSON_ID(+) = STDNT.PERSON_ID
AND CI.CAL_TYPE = CAT.CAL_TYPE
AND CAT.S_CAL_CAT IN ('ACADEMIC'
, 'LOAD')
AND CS.CAL_STATUS = CI.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND ((LKV.LOOKUP_CODE = 'DCAT'
AND CI.START_DT <= SYSDATE
AND NVL ( CI.END_DT
, SYSDATE) >= SYSDATE ) OR (LKV.LOOKUP_CODE = 'OCAT'
AND CI.START_DT <= SYSDATE + 365) OR (LKV.LOOKUP_CODE = 'PCAT'
AND PSD.CATALOG_CAL_TYPE = CI.CAL_TYPE
AND PSD.CATALOG_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND NVL ( CI.END_DT
, SYSDATE) >= SYSDATE ) OR (LKV.LOOKUP_CODE = 'MCAT'
AND PSD.MATR_CAL_TYPE = CI.CAL_TYPE
AND PSD.MATR_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND NVL ( CI.END_DT
, SYSDATE) >= SYSDATE ) OR (LKV.LOOKUP_CODE = 'ACAT'
AND NVL ( CI.END_DT
, SYSDATE) >= SYSDATE
AND (CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) IN (SELECT NVL(SPA.CATALOG_CAL_TYPE
, CI.CAL_TYPE)
, NVL(SPA.CATALOG_SEQ_NUM
, CI.SEQUENCE_NUMBER)
FROM IGS_EN_STDNT_PS_ATT_ALL SPA
WHERE SPA.PERSON_ID = STDNT.PERSON_ID
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE') ) ) OR (LKV.LOOKUP_CODE = 'UCAT'
AND NVL ( CI.END_DT
, SYSDATE) >= SYSDATE
AND (CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) IN (SELECT NVL(SUSA.CATALOG_CAL_TYPE
, CI.CAL_TYPE)
, NVL(SUSA.CATALOG_SEQ_NUM
, CI.SEQUENCE_NUMBER)
FROM IGS_AS_SU_SETATMPT SUSA
, IGS_EN_UNIT_SET_ALL US
, IGS_EN_UNIT_SET_CAT USC
WHERE SUSA.PERSON_ID = STDNT.PERSON_ID
AND SUSA.STUDENT_CONFIRMED_IND = 'Y'
AND SUSA.END_DT IS NOT NULL
AND US.UNIT_SET_CD = SUSA.UNIT_SET_CD
AND US.VERSION_NUMBER = SUSA.US_VERSION_NUMBER
AND USC.UNIT_SET_CAT = US.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'MAJOR' ) ) )