VIEW: APPS.IGS_AS_XSLISTSEP_ADI_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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