View: IGS_AS_SLIST_ADI_V

Product:	IGS - Student System
Description:	Assessment Student List WebADI View
Implementation/DBA Data:	APPS.IGS_AS_SLIST_ADI_V

View Text

SELECT UOO.UNIT_CD UNIT_CD
, UOO.VERSION_NUMBER VERSION_NUMBER
, UOO.CALL_NUMBER CALL_NUMBER
, UV.TITLE UNIT_TITLE
, TTL.TEACH_CAL_TYPE CAL_TYPE
, TTL.TEACH_CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, ( SELECT INSTRUCTOR_ID
FROM IGS_PS_USEC_TCH_RESP INST
WHERE UOO.UOO_ID = INST.UOO_ID
AND INST.LEAD_INSTRUCTOR_FLAG = 'Y') INSTRUCTOR_ID
, UOO.UOO_ID UOO_ID
, PE.PARTY_ID PERSON_ID
, PE.PARTY_NUMBER PERSON_NUMBER
, PE.PERSON_LAST_NAME || '
, ' || PE.PERSON_FIRST_NAME || ' ' || PE.PERSON_MIDDLE_NAME PERSON_NAME
, SUA.COURSE_CD COURSE_CD
, LKUPV.MEANING || DECODE(SUA.NO_ASSESSMENT_IND
, 'Y'
, ' ('||FND_MESSAGE.GET_STRING('IGS'
, 'IGS_AS_AUDIT')||') '
, '') CLASS_ATTEMPT_STATUS
, PSVER.TITLE PROGRAM_TITLE
, DECODE ( SUA.UNIT_ATTEMPT_STATUS
, 'ENROLLED'
, ENROLLED_DT
, 'DISCONTIN'
, SUA.DISCONTINUED_DT
, 'WAITLISTED'
, WAITLIST_DT
, 'DROPPED'
, SUA.LAST_UPDATE_DATE
, 'COMPLETED'
, SUA.LAST_UPDATE_DATE
, NULL ) DATE_OF_LAST_ACTIVITY
, TTL.TEACH_ALTERNATE_CODE ALTERNATE_CODE
, PE.PERSON_LAST_NAME SURNAME
, SUA.ADMINISTRATIVE_PRIORITY WAITLISTED_POSITION
, SCA.NOMINATED_COMPLETION_PERD || DECODE (SCA.NOMINATED_COMPLETION_YR
, NULL
, NULL
, DECODE (SCA.NOMINATED_COMPLETION_PERD
, NULL
, NULL
, '
, ')) || SCA.NOMINATED_COMPLETION_YR NOM_COMPL_YR
, IGS_AS_ADI_UPLD_AIO_PKG.GET_SUA_YOP( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER) YR_PROG
, SUA.UNIT_ATTEMPT_STATUS UNIT_ATTEMPT_STATUS
, SUA.NO_ASSESSMENT_IND NO_ASSESSMENT_IND
, TTL.LOAD_CAL_TYPE
, TTL.LOAD_CI_SEQUENCE_NUMBER
FROM IGS_PS_UNIT_OFR_OPT_ALL UOO
, IGS_PS_UNIT_VER_ALL UV
, HZ_PARTIES PE
, IGS_EN_SU_ATTEMPT_SV SUA
, IGS_EN_STDNT_PS_ATT_ALL SCA
, IGS_PS_VER_ALL PSVER
, IGS_LOOKUP_VALUES LKUPV
, IGS_CA_LOAD_TO_TEACH_V TTL
WHERE UV.UNIT_CD = UOO.UNIT_CD
AND UV.VERSION_NUMBER = UOO.VERSION_NUMBER
AND UOO.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND UOO.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND SUA.PERSON_ID = PE.PARTY_ID
AND SUA.PERSON_ID = SCA.PERSON_ID
AND SUA.COURSE_CD = SCA.COURSE_CD
AND PSVER.COURSE_CD = SCA.COURSE_CD
AND PSVER.VERSION_NUMBER = SCA.VERSION_NUMBER
AND LKUPV.LOOKUP_TYPE = 'UNIT_ATTEMPT_STATUS'
AND LKUPV.LOOKUP_CODE = SUA.UNIT_ATTEMPT_STATUS
AND SUA.UOO_ID = UOO.UOO_ID
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'DISCONTIN'
, 'WAITLISTED'
, 'DROPPED'
, 'COMPLETED')

Columns