[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This view would be used for logical grouping of student unit set attmpts in year of program mode for unit set inquiry form |
| Implementation/DBA Data: | Not implemented in this database |
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')