VIEW: APPS.IGS_AS_UG_ADI_V

Source

View Text - Preformatted

SELECT sua.person_id, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', NULL, p.party_number) person_number, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', 'Anonymous', p.person_last_name || ', ' || p.person_first_name || ' ' || p.person_middle_name) person_name, sua.course_cd, sua.unit_cd, sua.version_number sua_version_number, sua.cal_type, sua.ci_sequence_number, sua.ci_start_dt, sua.ci_end_dt, sua.uoo_id, outcome_dt, grading_schema_cd, suao.version_number suao_version_number, DECODE (ec.key_derive_unit_grade_flag, 'N', suao.grade, igs_as_derive_grade.derive_suao_grade_from_suaio ( sua.person_id, sua.course_cd, sua.uoo_id, suao.grading_period_cd, suao.mark, suao.grade, 'Y' )) grade, s_grade_creation_method_type, finalised_outcome_ind finalised_outcome_indicator, TO_NUMBER (DECODE (ec.key_derive_unit_grade_flag, 'N', suao.mark, igs_as_derive_grade.derive_suao_mark_from_suaio ( sua.person_id, sua.course_cd, sua.uoo_id, suao.grading_period_cd, suao.mark, suao.grade, 'N' ))) mark, number_times_keyed, translated_grading_schema_cd, translated_version_number, translated_grade, translated_dt, sua.org_id, incomp_deadline_date, incomp_grading_schema_cd, incomp_version_number, incomp_default_mark, incomp_default_grade, suao.comments, suao.grading_period_cd grading_period_cd, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', igs_as_anon_grd_pkg.get_anon_id( sua.person_id, sua.course_cd, sua.unit_cd, sua.cal_type, sua.ci_sequence_number, sua.uoo_id, NULL, 'Y'), NULL) anonymous_id, suao.manual_override_flag, suao.mark_capped_flag, suao.release_date, (SELECT sub.submitted_date FROM igs_as_gaa_sub_hist sub WHERE sub.uoo_id = suao.uoo_id AND sub.person_id = suao.person_id AND sub.grading_period_cd = suao.grading_period_cd AND sub.submission_type = 'GRADE' AND sub.submission_status = 'COMPLETE') submitted_date, sua.location_cd, sua.unit_class FROM igs_en_su_attempt_SV sua, igs_as_su_stmptout_all suao, hz_parties p, igs_as_entry_conf ec WHERE sua.person_id = suao.person_id AND sua.course_cd = suao.course_cd AND sua.uoo_id = suao.uoo_id AND sua.person_id = p.party_id AND ec.s_control_num = 1 AND (sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED') OR (sua.unit_attempt_status = 'DISCONTIN' AND EXISTS( SELECT gsg.grading_schema_cd, gsg.version_number, gsg.grade FROM igs_as_grd_sch_grade gsg WHERE suao.grading_schema_cd = gsg.grading_schema_cd AND suao.version_number = gsg.version_number AND suao.grade = gsg.grade AND gsg.s_result_type = 'FAIL'))) AND outcome_dt = (SELECT MAX(outcome_dt) FROM igs_as_su_stmptout_all WHERE person_id = suao.person_id AND course_cd = suao.course_cd AND uoo_id = suao.uoo_id AND grading_period_cd = suao.grading_period_cd) UNION ALL SELECT sua.person_id, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', NULL, p.party_number) person_number, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', 'Anonymous', p.person_last_name || ', ' || p.person_first_name || ' ' || p.person_middle_name) person_name, sua.course_cd, sua.unit_cd, sua.version_number sua_version_number, sua.cal_type, sua.ci_sequence_number, sua.ci_start_dt, sua.ci_end_dt, sua.uoo_id, TO_DATE(NULL, 'YYYY-MM-DD') outcome_dt, NULL grading_schema_cd, TO_NUMBER(NULL) suao_version_number, DECODE (ec.key_derive_unit_grade_flag, 'N', NULL, igs_as_derive_grade.derive_suao_grade_from_suaio ( sua.person_id, sua.course_cd, sua.uoo_id, 'FINAL', NULL, NULL, 'Y' )) grade, NULL s_grade_creation_method_type, NULL finalised_outcome_indicator, TO_NUMBER (DECODE (ec.key_derive_unit_grade_flag, 'N', NULL, igs_as_derive_grade.derive_suao_mark_from_suaio ( sua.person_id, sua.course_cd, sua.uoo_id, 'FINAL', NULL, NULL, 'N' ))) mark, TO_NUMBER(NULL) number_times_keyed, NULL translated_grading_schema_cd, TO_NUMBER(NULL) translated_version_number, NULL translated_grade, TO_DATE(NULL, 'YYYY-MM-DD') translated_dt, sua.org_id, TO_DATE(NULL, 'YYYY-MM-DD') incomp_deadline_date, NULL incomp_grading_schema_cd, TO_NUMBER(NULL) incomp_version_number, TO_NUMBER(NULL) incomp_default_mark, NULL incomp_default_grade, NULL comments, NULL grading_period_cd, DECODE( igs_as_anon_grd_pkg.chk_anon_graded(sua.uoo_id, NULL), 'Y', igs_as_anon_grd_pkg.get_anon_id( sua.person_id, sua.course_cd, sua.unit_cd, sua.cal_type, sua.ci_sequence_number, sua.uoo_id, NULL, 'Y'), NULL) anonymous_id, NULL, NULL, TO_DATE (NULL), TO_DATE (NULL), sua.location_cd, sua.unit_class FROM igs_en_su_attempt_SV sua, hz_parties p, igs_as_entry_conf ec WHERE sua.person_id = p.party_id AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED') AND ec.s_control_num = 1 AND NOT EXISTS( SELECT 1 FROM igs_as_su_stmptout_all suao WHERE sua.person_id = suao.person_id AND sua.course_cd = suao.course_cd AND sua.uoo_id = suao.uoo_id)

View Text - HTML Formatted

SELECT SUA.PERSON_ID
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, NULL
, P.PARTY_NUMBER) PERSON_NUMBER
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, 'ANONYMOUS'
, P.PERSON_LAST_NAME || '
, ' || P.PERSON_FIRST_NAME || ' ' || P.PERSON_MIDDLE_NAME) PERSON_NAME
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.CI_START_DT
, SUA.CI_END_DT
, SUA.UOO_ID
, OUTCOME_DT
, GRADING_SCHEMA_CD
, SUAO.VERSION_NUMBER SUAO_VERSION_NUMBER
, DECODE (EC.KEY_DERIVE_UNIT_GRADE_FLAG
, 'N'
, SUAO.GRADE
, IGS_AS_DERIVE_GRADE.DERIVE_SUAO_GRADE_FROM_SUAIO ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUAO.GRADING_PERIOD_CD
, SUAO.MARK
, SUAO.GRADE
, 'Y' )) GRADE
, S_GRADE_CREATION_METHOD_TYPE
, FINALISED_OUTCOME_IND FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (DECODE (EC.KEY_DERIVE_UNIT_GRADE_FLAG
, 'N'
, SUAO.MARK
, IGS_AS_DERIVE_GRADE.DERIVE_SUAO_MARK_FROM_SUAIO ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUAO.GRADING_PERIOD_CD
, SUAO.MARK
, SUAO.GRADE
, 'N' ))) MARK
, NUMBER_TIMES_KEYED
, TRANSLATED_GRADING_SCHEMA_CD
, TRANSLATED_VERSION_NUMBER
, TRANSLATED_GRADE
, TRANSLATED_DT
, SUA.ORG_ID
, INCOMP_DEADLINE_DATE
, INCOMP_GRADING_SCHEMA_CD
, INCOMP_VERSION_NUMBER
, INCOMP_DEFAULT_MARK
, INCOMP_DEFAULT_GRADE
, SUAO.COMMENTS
, SUAO.GRADING_PERIOD_CD GRADING_PERIOD_CD
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, IGS_AS_ANON_GRD_PKG.GET_ANON_ID( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, NULL
, 'Y')
, NULL) ANONYMOUS_ID
, SUAO.MANUAL_OVERRIDE_FLAG
, SUAO.MARK_CAPPED_FLAG
, SUAO.RELEASE_DATE
, (SELECT SUB.SUBMITTED_DATE
FROM IGS_AS_GAA_SUB_HIST SUB
WHERE SUB.UOO_ID = SUAO.UOO_ID
AND SUB.PERSON_ID = SUAO.PERSON_ID
AND SUB.GRADING_PERIOD_CD = SUAO.GRADING_PERIOD_CD
AND SUB.SUBMISSION_TYPE = 'GRADE'
AND SUB.SUBMISSION_STATUS = 'COMPLETE') SUBMITTED_DATE
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_STMPTOUT_ALL SUAO
, HZ_PARTIES P
, IGS_AS_ENTRY_CONF EC
WHERE SUA.PERSON_ID = SUAO.PERSON_ID
AND SUA.COURSE_CD = SUAO.COURSE_CD
AND SUA.UOO_ID = SUAO.UOO_ID
AND SUA.PERSON_ID = P.PARTY_ID
AND EC.S_CONTROL_NUM = 1
AND (SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR (SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS( SELECT GSG.GRADING_SCHEMA_CD
, GSG.VERSION_NUMBER
, GSG.GRADE
FROM IGS_AS_GRD_SCH_GRADE GSG
WHERE SUAO.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD
AND SUAO.VERSION_NUMBER = GSG.VERSION_NUMBER
AND SUAO.GRADE = GSG.GRADE
AND GSG.S_RESULT_TYPE = 'FAIL')))
AND OUTCOME_DT = (SELECT MAX(OUTCOME_DT)
FROM IGS_AS_SU_STMPTOUT_ALL
WHERE PERSON_ID = SUAO.PERSON_ID
AND COURSE_CD = SUAO.COURSE_CD
AND UOO_ID = SUAO.UOO_ID
AND GRADING_PERIOD_CD = SUAO.GRADING_PERIOD_CD) UNION ALL SELECT SUA.PERSON_ID
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, NULL
, P.PARTY_NUMBER) PERSON_NUMBER
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, 'ANONYMOUS'
, P.PERSON_LAST_NAME || '
, ' || P.PERSON_FIRST_NAME || ' ' || P.PERSON_MIDDLE_NAME) PERSON_NAME
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.CI_START_DT
, SUA.CI_END_DT
, SUA.UOO_ID
, TO_DATE(NULL
, 'YYYY-MM-DD') OUTCOME_DT
, NULL GRADING_SCHEMA_CD
, TO_NUMBER(NULL) SUAO_VERSION_NUMBER
, DECODE (EC.KEY_DERIVE_UNIT_GRADE_FLAG
, 'N'
, NULL
, IGS_AS_DERIVE_GRADE.DERIVE_SUAO_GRADE_FROM_SUAIO ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, 'FINAL'
, NULL
, NULL
, 'Y' )) GRADE
, NULL S_GRADE_CREATION_METHOD_TYPE
, NULL FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (DECODE (EC.KEY_DERIVE_UNIT_GRADE_FLAG
, 'N'
, NULL
, IGS_AS_DERIVE_GRADE.DERIVE_SUAO_MARK_FROM_SUAIO ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, 'FINAL'
, NULL
, NULL
, 'N' ))) MARK
, TO_NUMBER(NULL) NUMBER_TIMES_KEYED
, NULL TRANSLATED_GRADING_SCHEMA_CD
, TO_NUMBER(NULL) TRANSLATED_VERSION_NUMBER
, NULL TRANSLATED_GRADE
, TO_DATE(NULL
, 'YYYY-MM-DD') TRANSLATED_DT
, SUA.ORG_ID
, TO_DATE(NULL
, 'YYYY-MM-DD') INCOMP_DEADLINE_DATE
, NULL INCOMP_GRADING_SCHEMA_CD
, TO_NUMBER(NULL) INCOMP_VERSION_NUMBER
, TO_NUMBER(NULL) INCOMP_DEFAULT_MARK
, NULL INCOMP_DEFAULT_GRADE
, NULL COMMENTS
, NULL GRADING_PERIOD_CD
, DECODE( IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(SUA.UOO_ID
, NULL)
, 'Y'
, IGS_AS_ANON_GRD_PKG.GET_ANON_ID( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UOO_ID
, NULL
, 'Y')
, NULL) ANONYMOUS_ID
, NULL
, NULL
, TO_DATE (NULL)
, TO_DATE (NULL)
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
FROM IGS_EN_SU_ATTEMPT_SV SUA
, HZ_PARTIES P
, IGS_AS_ENTRY_CONF EC
WHERE SUA.PERSON_ID = P.PARTY_ID
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED')
AND EC.S_CONTROL_NUM = 1
AND NOT EXISTS( SELECT 1
FROM IGS_AS_SU_STMPTOUT_ALL SUAO
WHERE SUA.PERSON_ID = SUAO.PERSON_ID
AND SUA.COURSE_CD = SUAO.COURSE_CD
AND SUA.UOO_ID = SUAO.UOO_ID)