VIEW: APPS.IGS_AS_UGAI_ADI_V

Source

View Text - Preformatted

SELECT sua.person_id 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', NULL,p.person_last_name || ', ' || p.person_first_name || ' ' || p.person_middle_name ) person_name, sua.course_cd COURSE_CD, sua.unit_cd UNIT_CD, sua.version_number SUA_VERSION_NUMBER, sua.cal_type CAL_TYPE, sua.ci_sequence_number CI_SEQUENCE_NUMBER, sua.ci_start_dt CI_START_DT, sua.ci_end_dt CI_END_DT, sua.uoo_id UOO_ID, suao.outcome_dt OUTCOME_DT, suao.grading_schema_cd GRADING_SCHEMA_CD, suao.version_number SUAO_VERSION_NUMBER, suao.grade GRADE, suao.s_grade_creation_method_type S_GRADE_CREATION_METHOD_TYPE, suao.finalised_outcome_ind FINALISED_OUTCOME_INDICATOR, suao.mark MARK, suao.number_times_keyed NUMBER_TIMES_KEYED, suao.translated_grading_schema_cd TRANSLATED_GRADING_SCHEMA_CD, suao.translated_version_number TRANSLATED_VERSION_NUMBER, suao.translated_grade TRANSLATED_GRADE, suao.translated_dt TRANSLATED_DT, sua.org_id ORG_ID, suao.incomp_deadline_date INCOMP_DEADLINE_DATE, suao.incomp_grading_schema_cd INCOMP_GRADING_SCHEMA_CD, suao.incomp_version_number INCOMP_VERSION_NUMBER, suao.incomp_default_mark INCOMP_DEFAULT_MARK, suao.incomp_default_grade INCOMP_DEFAULT_GRADE, suao.comments COMMENTS, suao.grading_period_cd GRADING_PERIOD_CD, Null ASSESSMENT_TYPE, Null EXAM_LOCATION_CD, TO_NUMBER(Null) ASS_ID, sua.location_cd location_cd, sua.unit_class unit_class, Null CHK_ANON_GRADED, suao.mark_capped_flag mark_capped_flag, suao.show_on_academic_histry_flag show_on_academic_histry_flag, suao.release_date release_date, suao.manual_override_flag manual_override_flag, 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' ) anonymous_id, NULL reference FROM igs_en_su_attempt_sv sua, igs_as_su_stmptout_all suao, hz_parties p WHERE sua.unit_cd = suao.unit_cd (+) AND sua.person_id = suao.person_id (+) AND sua.course_cd = suao.course_cd (+) AND sua.cal_type = suao.cal_type (+) AND sua.ci_sequence_number = suao.ci_sequence_number (+) AND sua.person_id = p.party_id AND (sua.unit_attempt_status in ('ENROLLED','COMPLETED') OR (sua.unit_attempt_status = 'DISCONTIN' AND EXISTS (SELECT 1 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 ( suao.person_id IS NULL OR suao.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 SELECT hz.party_Id PERSON_ID, DECODE (igs_as_anon_grd_pkg.chk_anon_graded ( uoo.uoo_id, sai.ass_id), 'Y', NULL, hz.party_number ) PERSON_NUMBER, DECODE (igs_as_anon_grd_pkg.chk_anon_graded (uoo.uoo_id, sai.ass_id), 'Y', NULL, hz.person_last_name || ',' || hz.person_first_name || ' ' || hz.person_middle_name ) PERSON_NAME, sai.course_cd COURSE_CD, sua.unit_cd UNIT_CD, sua.version_number SUA_VERSION_NUMBER, sua.cal_type CAL_TYPE, sua.ci_sequence_number CI_SEQUENCE_NUMBER, TO_DATE(Null, 'YYYY-MM-DD') CI_START_DT, TO_DATE(Null, 'YYYY-MM-DD') CI_END_DT, uoo.uoo_id UOO_ID, sai.outcome_dt OUTCOME_DT, sai.grading_schema_cd GRADING_SCHEMA_CD, TO_NUMBER(Null) SUAO_VERSION_NUMBER, sai.grade GRADE, Null S_GRADE_CREATION_METHOD_TYPE, Null FINALISED_OUTCOME_IND, sai.mark 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, TO_NUMBER(Null) ORG_ID, TO_DATE(Null, 'YYYY-MM-DD') INCOMP_DEADLINE_DT, Null INCOMP_GRADING_SCHEMA_CD, TO_NUMBER(Null) INCOMP_VERSION_NUMBER, TO_NUMBER(Null) INCOMP_DEFAULT_MARK, Null INCOMP_DEFAULT_GRADE, aia.comments COMMENTS, '-' GRADING_PERIOD_CD, aia.assessment_type ASSESSMENT_TYPE, sua.exam_location_cd EXAM_LOCATION_CD, sai.ass_id ASS_ID, uoo.location_cd LOCATION_CD, uoo.unit_class unit_class, igs_as_anon_grd_pkg.chk_anon_graded(uoo.uoo_id, sai.ass_id) CHK_ANON_GRADED, NULL mark_capped_flag, NULL show_on_academic_histry_flag, TO_DATE(NULL, 'DD-MON-RRRR') release_date, NULL manual_override_flag, 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, sai.ass_id, 'N' ) ANONYMOUS_ID, IGS_AS_GEN_003.assp_get_ai_ref(sai.unit_section_ass_item_id,sai.unit_ass_item_id ) reference FROM hz_parties hz, igs_en_su_attempt_sv sua, igs_as_su_atmpt_itm sai, igs_ps_unit_ofr_opt_all uoo, igs_as_assessmnt_itm_all aia WHERE hz.party_id = sai.person_id AND sai.person_id = sua.person_id AND sai.unit_cd = sua.unit_cd AND sai.course_cd = sua.course_cd AND sai.cal_type = sua.cal_type AND sai.ci_sequence_number = sua.ci_sequence_number AND uoo.uoo_id = sua.uoo_id AND sai.ass_id = aia.ass_id AND sai.logical_delete_dt IS NULL AND ( sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED') OR ( sua.unit_attempt_status = 'DISCONTIN' AND EXISTS ( SELECT 1 FROM igs_as_grd_sch_grade gsg WHERE sai.grading_schema_cd = gsg.grading_schema_cd AND sai.gs_version_number = gsg.version_number AND sai.grade = gsg.grade AND gsg.s_result_type = 'FAIL') ) )

View Text - HTML Formatted

SELECT SUA.PERSON_ID 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'
, NULL
, P.PERSON_LAST_NAME || '
, ' || P.PERSON_FIRST_NAME || ' ' || P.PERSON_MIDDLE_NAME ) PERSON_NAME
, SUA.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SUA.CI_START_DT CI_START_DT
, SUA.CI_END_DT CI_END_DT
, SUA.UOO_ID UOO_ID
, SUAO.OUTCOME_DT OUTCOME_DT
, SUAO.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SUAO.VERSION_NUMBER SUAO_VERSION_NUMBER
, SUAO.GRADE GRADE
, SUAO.S_GRADE_CREATION_METHOD_TYPE S_GRADE_CREATION_METHOD_TYPE
, SUAO.FINALISED_OUTCOME_IND FINALISED_OUTCOME_INDICATOR
, SUAO.MARK MARK
, SUAO.NUMBER_TIMES_KEYED NUMBER_TIMES_KEYED
, SUAO.TRANSLATED_GRADING_SCHEMA_CD TRANSLATED_GRADING_SCHEMA_CD
, SUAO.TRANSLATED_VERSION_NUMBER TRANSLATED_VERSION_NUMBER
, SUAO.TRANSLATED_GRADE TRANSLATED_GRADE
, SUAO.TRANSLATED_DT TRANSLATED_DT
, SUA.ORG_ID ORG_ID
, SUAO.INCOMP_DEADLINE_DATE INCOMP_DEADLINE_DATE
, SUAO.INCOMP_GRADING_SCHEMA_CD INCOMP_GRADING_SCHEMA_CD
, SUAO.INCOMP_VERSION_NUMBER INCOMP_VERSION_NUMBER
, SUAO.INCOMP_DEFAULT_MARK INCOMP_DEFAULT_MARK
, SUAO.INCOMP_DEFAULT_GRADE INCOMP_DEFAULT_GRADE
, SUAO.COMMENTS COMMENTS
, SUAO.GRADING_PERIOD_CD GRADING_PERIOD_CD
, NULL ASSESSMENT_TYPE
, NULL EXAM_LOCATION_CD
, TO_NUMBER(NULL) ASS_ID
, SUA.LOCATION_CD LOCATION_CD
, SUA.UNIT_CLASS UNIT_CLASS
, NULL CHK_ANON_GRADED
, SUAO.MARK_CAPPED_FLAG MARK_CAPPED_FLAG
, SUAO.SHOW_ON_ACADEMIC_HISTRY_FLAG SHOW_ON_ACADEMIC_HISTRY_FLAG
, SUAO.RELEASE_DATE RELEASE_DATE
, SUAO.MANUAL_OVERRIDE_FLAG MANUAL_OVERRIDE_FLAG
, 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' ) ANONYMOUS_ID
, NULL REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_STMPTOUT_ALL SUAO
, HZ_PARTIES P
WHERE SUA.UNIT_CD = SUAO.UNIT_CD (+)
AND SUA.PERSON_ID = SUAO.PERSON_ID (+)
AND SUA.COURSE_CD = SUAO.COURSE_CD (+)
AND SUA.CAL_TYPE = SUAO.CAL_TYPE (+)
AND SUA.CI_SEQUENCE_NUMBER = SUAO.CI_SEQUENCE_NUMBER (+)
AND SUA.PERSON_ID = P.PARTY_ID
AND (SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR (SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS (SELECT 1
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 ( SUAO.PERSON_ID IS NULL OR SUAO.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 SELECT HZ.PARTY_ID PERSON_ID
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED ( UOO.UOO_ID
, SAI.ASS_ID)
, 'Y'
, NULL
, HZ.PARTY_NUMBER ) PERSON_NUMBER
, DECODE (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED (UOO.UOO_ID
, SAI.ASS_ID)
, 'Y'
, NULL
, HZ.PERSON_LAST_NAME || '
, ' || HZ.PERSON_FIRST_NAME || ' ' || HZ.PERSON_MIDDLE_NAME ) PERSON_NAME
, SAI.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBER
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, TO_DATE(NULL
, 'YYYY-MM-DD') CI_START_DT
, TO_DATE(NULL
, 'YYYY-MM-DD') CI_END_DT
, UOO.UOO_ID UOO_ID
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, TO_NUMBER(NULL) SUAO_VERSION_NUMBER
, SAI.GRADE GRADE
, NULL S_GRADE_CREATION_METHOD_TYPE
, NULL FINALISED_OUTCOME_IND
, SAI.MARK 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
, TO_NUMBER(NULL) ORG_ID
, TO_DATE(NULL
, 'YYYY-MM-DD') INCOMP_DEADLINE_DT
, NULL INCOMP_GRADING_SCHEMA_CD
, TO_NUMBER(NULL) INCOMP_VERSION_NUMBER
, TO_NUMBER(NULL) INCOMP_DEFAULT_MARK
, NULL INCOMP_DEFAULT_GRADE
, AIA.COMMENTS COMMENTS
, '-' GRADING_PERIOD_CD
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.ASS_ID ASS_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UNIT_CLASS UNIT_CLASS
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, NULL MARK_CAPPED_FLAG
, NULL SHOW_ON_ACADEMIC_HISTRY_FLAG
, TO_DATE(NULL
, 'DD-MON-RRRR') RELEASE_DATE
, NULL MANUAL_OVERRIDE_FLAG
, 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
, SAI.ASS_ID
, 'N' ) ANONYMOUS_ID
, IGS_AS_GEN_003.ASSP_GET_AI_REF(SAI.UNIT_SECTION_ASS_ITEM_ID
, SAI.UNIT_ASS_ITEM_ID ) REFERENCE
FROM HZ_PARTIES HZ
, IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_ATMPT_ITM SAI
, IGS_PS_UNIT_OFR_OPT_ALL UOO
, IGS_AS_ASSESSMNT_ITM_ALL AIA
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SAI.COURSE_CD = SUA.COURSE_CD
AND SAI.CAL_TYPE = SUA.CAL_TYPE
AND SAI.CI_SEQUENCE_NUMBER = SUA.CI_SEQUENCE_NUMBER
AND UOO.UOO_ID = SUA.UOO_ID
AND SAI.ASS_ID = AIA.ASS_ID
AND SAI.LOGICAL_DELETE_DT IS NULL
AND ( SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED') OR ( SUA.UNIT_ATTEMPT_STATUS = 'DISCONTIN'
AND EXISTS ( SELECT 1
FROM IGS_AS_GRD_SCH_GRADE GSG
WHERE SAI.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD
AND SAI.GS_VERSION_NUMBER = GSG.VERSION_NUMBER
AND SAI.GRADE = GSG.GRADE
AND GSG.S_RESULT_TYPE = 'FAIL') ) )