VIEW: APPS.IGS_EN_SUSA_YEAR_V

Source

View Text - Preformatted

SELECT SUSA.PERSON_ID, SUSA.COURSE_CD, SUSA.UNIT_SET_CD, SUSA.US_VERSION_NUMBER, SUSA.SEQUENCE_NUMBER, US.TITLE, SUSA.SELECTION_DT, SUSA.RQRMNTS_COMPLETE_DT , SUSA.END_DT, DECODE(SUSA.RQRMNTS_COMPLETE_DT,NULL, DECODE(SUSA.END_DT,NULL,'ENROLLED','ENDED'),'COMPLETED') STATUS_CODE, LKUPV.MEANING STATUS_MEANING, ( SELECT COUNT (DISTINCT CIR.SUP_CI_SEQUENCE_NUMBER || CIR.SUP_CAL_TYPE) FROM IGS_EN_UNIT_SET US,IGS_EN_SU_ATTEMPT SUA,IGS_CA_INST_REL CIR, IGS_CA_INST CI,IGS_CA_TYPE CAT,IGS_EN_UNIT_SET_CAT USC WHERE SUA.PERSON_ID = SUSA.PERSON_ID AND SUA.COURSE_CD = SUSA.COURSE_CD AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE (Null,Null,SUA.CAL_TYPE, SUA.CI_SEQUENCE_NUMBER) BETWEEN SUSA.SELECTION_DT AND NVL(SUSA.RQRMNTS_COMPLETE_DT,NVL(SUSA.END_DT, TO_DATE('31/12/4000','DD/MM/RRRR')))) AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT AND USC.S_UNIT_SET_CAT = 'PRENRL_YR' AND SUA.UNIT_ATTEMPT_STATUS IN ('DUPLICATE','ENROLLED', 'COMPLETED', 'DISCONTIN') AND CIR.SUB_CAL_TYPE = SUA.CAL_TYPE AND CIR.SUB_CI_SEQUENCE_NUMBER = SUA.CI_SEQUENCE_NUMBER AND CIR.SUP_CAL_TYPE=SCA.CAL_TYPE AND CI.CAL_TYPE = CIR.SUP_CAL_TYPE AND CI.SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER AND CAT.CAL_TYPE = CI.CAL_TYPE AND CAT.S_CAL_CAT = 'ACADEMIC' ) ACAD_PERD, SUSA.ATTRIBUTE_CATEGORY, SUSA.ATTRIBUTE1, SUSA.ATTRIBUTE2, SUSA.ATTRIBUTE3, SUSA.ATTRIBUTE4, SUSA.ATTRIBUTE5, SUSA.ATTRIBUTE6, SUSA.ATTRIBUTE7, SUSA.ATTRIBUTE8, SUSA.ATTRIBUTE9, SUSA.ATTRIBUTE10, SUSA.ATTRIBUTE11, SUSA.ATTRIBUTE12, SUSA.ATTRIBUTE13, SUSA.ATTRIBUTE14, SUSA.ATTRIBUTE15, SUSA.ATTRIBUTE16, SUSA.ATTRIBUTE17, SUSA.ATTRIBUTE18, SUSA.ATTRIBUTE19, SUSA.ATTRIBUTE20, SUSA.CREATED_BY, SUSA.CREATION_DATE, SUSA.LAST_UPDATED_BY, SUSA.LAST_UPDATE_DATE, SUSA.LAST_UPDATE_LOGIN FROM IGS_AS_SU_SETATMPT SUSA, IGS_EN_STDNT_PS_ATT SCA, IGS_EN_UNIT_SET US, IGS_EN_UNIT_SET_CAT USC, IGS_LOOKUPS_VIEW LKUPV WHERE SCA.PERSON_ID = SUSA.PERSON_ID AND SCA.COURSE_CD = SUSA.COURSE_CD AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT AND USC.S_UNIT_SET_CAT = 'PRENRL_YR' AND LKUPV.LOOKUP_TYPE = 'US_ATTEMPT_STATUS' AND LKUPV.LOOKUP_CODE = DECODE(SUSA.RQRMNTS_COMPLETE_DT,NULL, DECODE(SUSA.END_DT,NULL,'ENROLLED','ENDED'),'COMPLETED')

View Text - HTML Formatted

SELECT SUSA.PERSON_ID
, SUSA.COURSE_CD
, SUSA.UNIT_SET_CD
, SUSA.US_VERSION_NUMBER
, SUSA.SEQUENCE_NUMBER
, US.TITLE
, SUSA.SELECTION_DT
, SUSA.RQRMNTS_COMPLETE_DT
, SUSA.END_DT
, DECODE(SUSA.RQRMNTS_COMPLETE_DT
, NULL
, DECODE(SUSA.END_DT
, NULL
, 'ENROLLED'
, 'ENDED')
, 'COMPLETED') STATUS_CODE
, LKUPV.MEANING STATUS_MEANING
, ( SELECT COUNT (DISTINCT CIR.SUP_CI_SEQUENCE_NUMBER || CIR.SUP_CAL_TYPE)
FROM IGS_EN_UNIT_SET US
, IGS_EN_SU_ATTEMPT SUA
, IGS_CA_INST_REL CIR
, IGS_CA_INST CI
, IGS_CA_TYPE CAT
, IGS_EN_UNIT_SET_CAT USC
WHERE SUA.PERSON_ID = SUSA.PERSON_ID
AND SUA.COURSE_CD = SUSA.COURSE_CD
AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE (NULL
, NULL
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER) BETWEEN SUSA.SELECTION_DT
AND NVL(SUSA.RQRMNTS_COMPLETE_DT
, NVL(SUSA.END_DT
, TO_DATE('31/12/4000'
, 'DD/MM/RRRR'))))
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND SUA.UNIT_ATTEMPT_STATUS IN ('DUPLICATE'
, 'ENROLLED'
, 'COMPLETED'
, 'DISCONTIN')
AND CIR.SUB_CAL_TYPE = SUA.CAL_TYPE
AND CIR.SUB_CI_SEQUENCE_NUMBER = SUA.CI_SEQUENCE_NUMBER
AND CIR.SUP_CAL_TYPE=SCA.CAL_TYPE
AND CI.CAL_TYPE = CIR.SUP_CAL_TYPE
AND CI.SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER
AND CAT.CAL_TYPE = CI.CAL_TYPE
AND CAT.S_CAL_CAT = 'ACADEMIC' ) ACAD_PERD
, SUSA.ATTRIBUTE_CATEGORY
, SUSA.ATTRIBUTE1
, SUSA.ATTRIBUTE2
, SUSA.ATTRIBUTE3
, SUSA.ATTRIBUTE4
, SUSA.ATTRIBUTE5
, SUSA.ATTRIBUTE6
, SUSA.ATTRIBUTE7
, SUSA.ATTRIBUTE8
, SUSA.ATTRIBUTE9
, SUSA.ATTRIBUTE10
, SUSA.ATTRIBUTE11
, SUSA.ATTRIBUTE12
, SUSA.ATTRIBUTE13
, SUSA.ATTRIBUTE14
, SUSA.ATTRIBUTE15
, SUSA.ATTRIBUTE16
, SUSA.ATTRIBUTE17
, SUSA.ATTRIBUTE18
, SUSA.ATTRIBUTE19
, SUSA.ATTRIBUTE20
, SUSA.CREATED_BY
, SUSA.CREATION_DATE
, SUSA.LAST_UPDATED_BY
, SUSA.LAST_UPDATE_DATE
, SUSA.LAST_UPDATE_LOGIN
FROM IGS_AS_SU_SETATMPT SUSA
, IGS_EN_STDNT_PS_ATT SCA
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
, IGS_LOOKUPS_VIEW LKUPV
WHERE SCA.PERSON_ID = SUSA.PERSON_ID
AND SCA.COURSE_CD = SUSA.COURSE_CD
AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND LKUPV.LOOKUP_TYPE = 'US_ATTEMPT_STATUS'
AND LKUPV.LOOKUP_CODE = DECODE(SUSA.RQRMNTS_COMPLETE_DT
, NULL
, DECODE(SUSA.END_DT
, NULL
, 'ENROLLED'
, 'ENDED')
, 'COMPLETED')