View: IGS_DA_EXPL_PRG_CAT_V

Product:	IGS - Student System
Description:	View is used to determine the program catalogs allowed to be displayed for the Explore Program Catalog LOV based on configuration of profiles setup and the user allowed to choose Explore Program options.
Implementation/DBA Data:	APPS.IGS_DA_EXPL_PRG_CAT_V

View Text

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

Columns