VIEW: APPS.IGS_AS_SLIST_ADI_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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