View: IGS_AS_XSLISTSEP_ADI_V

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

View Text

SELECT TTL.LOAD_DESCRIPTION LOAD_DESCRIPTION
, TTL.LOAD_CAL_TYPE LOAD_CAL_TYPE
, TTL.LOAD_CI_SEQUENCE_NUMBER LOAD_CI_SEQUENCE_NUMBER
, TTL.TEACH_DESCRIPTION TEACH_DESCRIPTION
, UV.TITLE TITLE
, SUA.UNIT_CLASS UNIT_CLASS
, LOC.DESCRIPTION LOCATION
, HZINST.PERSON_LAST_NAME || '
, ' || HZINST.PERSON_FIRST_NAME || ' ' || HZINST.PERSON_MIDDLE_NAME INSTRUCTOR_NAME
, UOO.UNIT_CD UNIT_CD
, 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
, INITCAP (PE.PERSON_LAST_NAME) SURNAME
, INITCAP (PE.KNOWN_AS) PREFERRED_GIVEN_NAME
, 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'
, SUA.ENROLLED_DT
, 'DISCONTIN'
, SUA.DISCONTINUED_DT
, 'WAITLISTED'
, SUA.WAITLIST_DT
, 'DROPPED'
, SUA.LAST_UPDATE_DATE
, 'COMPLETED'
, SUA.LAST_UPDATE_DATE
, NULL) DATE_OF_LAST_ACTIVITY
, 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
, USXGM.USEC_X_LISTED_GROUP_ID USEC_X_LISTED_GROUP_ID
, SUA.UNIT_ATTEMPT_STATUS UNIT_ATTEMPT_STATUS
, SUA.NO_ASSESSMENT_IND NO_ASSESSMENT_IND
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_PS_USEC_X_GRPMEM USXGM
, IGS_PS_USEC_TCH_RESP INSTR
, IGS_CA_TEACH_TO_LOAD_V TTL
, IGS_AD_LOCATION LOC
, HZ_PARTIES HZINST
WHERE UV.UNIT_CD = UOO.UNIT_CD
AND UV.VERSION_NUMBER = UOO.VERSION_NUMBER
AND SUA.UOO_ID = UOO.UOO_ID
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 USXGM.UOO_ID = UOO.UOO_ID
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'DISCONTIN'
, 'WAITLISTED'
, 'DROPPED'
, 'COMPLETED')
AND UOO.UOO_ID = INSTR.UOO_ID(+)
AND INSTR.LEAD_INSTRUCTOR_FLAG(+) = 'Y'
AND UOO.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND UOO.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND UOO.LOCATION_CD = LOC.LOCATION_CD
AND INSTR.INSTRUCTOR_ID = HZINST.PARTY_ID

Columns