VIEW: APPS.IGS_AS_ASSITEM_ADI_V

Source

View Text - Preformatted

SELECT hz.person_last_name || ', ' || hz.person_first_name || ' ' || hz.person_middle_name PARTY_NAME, hz.party_number PARTY_NUMBER, null ANONYMOUS_ID, aia.assessment_type ASSESSMENT_TYPE, sai.grading_schema_cd GRADING_SCHEMA_CD, sai.gs_version_number GS_VERSION_NUMBER, sai.outcome_dt OUTCOME_DT, sai.mark MARK, sai.grade GRADE, DECODE(sai.OUTCOME_COMMENT_CODE,NULL,'',IGS_GE_GEN_004.genp_get_lookup('MARKS_GRADE_CHANGE_COMMENT',sai.OUTCOME_COMMENT_CODE )) COMMENTS, sua.exam_location_cd EXAM_LOCATION_CD, sai.person_id PERSON_ID, sai.course_cd COURSE_CD, sai.unit_cd UNIT_CD, uv.title TITLE, sai.cal_type CAL_TYPE, sai.ci_sequence_number CI_SEQUENCE_NUMBER, sai.ass_id ASSESSMENT_ID, uoo.location_cd LOCATION_CD, uoo.uoo_id UOO_ID, igs_as_anon_grd_pkg.chk_anon_graded(uoo.uoo_id, sai.ass_id) CHK_ANON_GRADED, DECODE (sai.unit_section_ass_item_id, NULL, (SELECT uai.reference FROM igs_as_unitass_item_all uai WHERE uai.unit_ass_item_id = sai.unit_ass_item_id), (SELECT usai.reference FROM igs_ps_unitass_item usai WHERE usai.unit_section_ass_item_id = sai.unit_section_ass_item_id)) reference, DECODE (sai.unit_section_ass_item_id, NULL, (SELECT uai.due_dt FROM igs_as_unitass_item_all uai WHERE uai.unit_ass_item_id = sai.unit_ass_item_id), (SELECT usai.due_dt FROM igs_ps_unitass_item usai WHERE usai.unit_section_ass_item_id = sai.unit_section_ass_item_id)) due_dt, sai.waived_flag waived_flag, sai.override_due_dt override_due_dt, sai.submitted_date submitted_date, sai.penalty_applied_flag penalty_applied_flag, uoo.unit_class unit_class 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, igs_ps_unit_ver_all uv WHERE hz.party_id = sai.person_id AND sai.person_id = sua.person_id AND sai.unit_cd = sua.unit_cd AND sua.unit_cd = uv.unit_cd AND sua.version_number = uv.version_number 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 igs_as_anon_grd_pkg.chk_anon_graded(uoo.uoo_id, sai.ass_id) = 'N' 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') ) ) UNION SELECT null PARTY_NAME , null PARTY_NUMBER, nvl(igs_as_anon_grd_pkg.get_anon_id(sai.person_id, sai.course_cd, sai.unit_cd, sai.cal_type, sai.ci_sequence_number, uoo.uoo_id, sai.ass_id, 'N'), 'Error No Anonymous Id') ANONYMOUS_ID, aia.assessment_type ASSESSMENT_TYPE, sai.grading_schema_cd GRADING_SCHEMA_CD, sai.gs_version_number GS_VERSION_NUMBER, sai.outcome_dt OUTCOME_DT, sai.mark MARK, sai.grade GRADE, DECODE(sai.outcome_comment_code,NULL,'',IGS_GE_GEN_004.genp_get_lookup('MARKS_GRADE_CHANGE_COMMENT',sai.OUTCOME_COMMENT_CODE )) COMMENTS, sua.exam_location_cd EXAM_LOCATION_CD, sai.person_id PERSON_ID, sai.course_cd COURSE_CD, sua.unit_cd UNIT_CD, uv.title TITLE, sua.cal_type CAL_TYPE, sua.ci_sequence_number CI_SEQUENCE_NUMBER, sai.ass_id ASSESSMENT_ID, uoo.location_cd LOCATION_CD, uoo.uoo_id UOO_ID, igs_as_anon_grd_pkg.chk_anon_graded(uoo.uoo_id, sai.ass_id) CHK_ANON_GRADED, DECODE (sai.unit_section_ass_item_id, NULL, (SELECT uai.reference FROM igs_as_unitass_item_all uai WHERE uai.unit_ass_item_id = sai.unit_ass_item_id), (SELECT usai.reference FROM igs_ps_unitass_item usai WHERE usai.unit_section_ass_item_id = sai.unit_section_ass_item_id)) reference, DECODE (sai.unit_section_ass_item_id, NULL, (SELECT uai.due_dt FROM igs_as_unitass_item_all uai WHERE uai.unit_ass_item_id = sai.unit_ass_item_id), (SELECT usai.due_dt FROM igs_ps_unitass_item usai WHERE usai.unit_section_ass_item_id = sai.unit_section_ass_item_id)) due_dt, sai.waived_flag waived_flag, sai.override_due_dt override_due_dt, sai.submitted_date submitted_date, sai.penalty_applied_flag penalty_applied_flag, uoo.unit_class unit_class 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, igs_ps_unit_ver_all uv WHERE hz.party_id = sai.person_id AND sai.person_id = sua.person_id AND sai.unit_cd = sua.unit_cd AND sua.unit_cd = uv.unit_cd AND sua.version_number = uv.version_number 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 (igs_as_anon_grd_pkg.chk_anon_graded(uoo.uoo_id, sai.ass_id) = 'Y') 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 HZ.PERSON_LAST_NAME || '
, ' || HZ.PERSON_FIRST_NAME || ' ' || HZ.PERSON_MIDDLE_NAME PARTY_NAME
, HZ.PARTY_NUMBER PARTY_NUMBER
, NULL ANONYMOUS_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER GS_VERSION_NUMBER
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.MARK MARK
, SAI.GRADE GRADE
, DECODE(SAI.OUTCOME_COMMENT_CODE
, NULL
, ''
, IGS_GE_GEN_004.GENP_GET_LOOKUP('MARKS_GRADE_CHANGE_COMMENT'
, SAI.OUTCOME_COMMENT_CODE )) COMMENTS
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.PERSON_ID PERSON_ID
, SAI.COURSE_CD COURSE_CD
, SAI.UNIT_CD UNIT_CD
, UV.TITLE TITLE
, SAI.CAL_TYPE CAL_TYPE
, SAI.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SAI.ASS_ID ASSESSMENT_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UOO_ID UOO_ID
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.REFERENCE
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.REFERENCE
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) REFERENCE
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.DUE_DT
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.DUE_DT
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) DUE_DT
, SAI.WAIVED_FLAG WAIVED_FLAG
, SAI.OVERRIDE_DUE_DT OVERRIDE_DUE_DT
, SAI.SUBMITTED_DATE SUBMITTED_DATE
, SAI.PENALTY_APPLIED_FLAG PENALTY_APPLIED_FLAG
, UOO.UNIT_CLASS UNIT_CLASS
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
, IGS_PS_UNIT_VER_ALL UV
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SUA.UNIT_CD = UV.UNIT_CD
AND SUA.VERSION_NUMBER = UV.VERSION_NUMBER
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 IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) = 'N'
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') ) ) UNION SELECT NULL PARTY_NAME
, NULL PARTY_NUMBER
, NVL(IGS_AS_ANON_GRD_PKG.GET_ANON_ID(SAI.PERSON_ID
, SAI.COURSE_CD
, SAI.UNIT_CD
, SAI.CAL_TYPE
, SAI.CI_SEQUENCE_NUMBER
, UOO.UOO_ID
, SAI.ASS_ID
, 'N')
, 'ERROR NO ANONYMOUS ID') ANONYMOUS_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER GS_VERSION_NUMBER
, SAI.OUTCOME_DT OUTCOME_DT
, SAI.MARK MARK
, SAI.GRADE GRADE
, DECODE(SAI.OUTCOME_COMMENT_CODE
, NULL
, ''
, IGS_GE_GEN_004.GENP_GET_LOOKUP('MARKS_GRADE_CHANGE_COMMENT'
, SAI.OUTCOME_COMMENT_CODE )) COMMENTS
, SUA.EXAM_LOCATION_CD EXAM_LOCATION_CD
, SAI.PERSON_ID PERSON_ID
, SAI.COURSE_CD COURSE_CD
, SUA.UNIT_CD UNIT_CD
, UV.TITLE TITLE
, SUA.CAL_TYPE CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER CI_SEQUENCE_NUMBER
, SAI.ASS_ID ASSESSMENT_ID
, UOO.LOCATION_CD LOCATION_CD
, UOO.UOO_ID UOO_ID
, IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) CHK_ANON_GRADED
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.REFERENCE
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.REFERENCE
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) REFERENCE
, DECODE (SAI.UNIT_SECTION_ASS_ITEM_ID
, NULL
, (SELECT UAI.DUE_DT
FROM IGS_AS_UNITASS_ITEM_ALL UAI
WHERE UAI.UNIT_ASS_ITEM_ID = SAI.UNIT_ASS_ITEM_ID)
, (SELECT USAI.DUE_DT
FROM IGS_PS_UNITASS_ITEM USAI
WHERE USAI.UNIT_SECTION_ASS_ITEM_ID = SAI.UNIT_SECTION_ASS_ITEM_ID)) DUE_DT
, SAI.WAIVED_FLAG WAIVED_FLAG
, SAI.OVERRIDE_DUE_DT OVERRIDE_DUE_DT
, SAI.SUBMITTED_DATE SUBMITTED_DATE
, SAI.PENALTY_APPLIED_FLAG PENALTY_APPLIED_FLAG
, UOO.UNIT_CLASS UNIT_CLASS
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
, IGS_PS_UNIT_VER_ALL UV
WHERE HZ.PARTY_ID = SAI.PERSON_ID
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UNIT_CD = SUA.UNIT_CD
AND SUA.UNIT_CD = UV.UNIT_CD
AND SUA.VERSION_NUMBER = UV.VERSION_NUMBER
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 (IGS_AS_ANON_GRD_PKG.CHK_ANON_GRADED(UOO.UOO_ID
, SAI.ASS_ID) = 'Y')
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') ) )