VIEW: APPS.IGS_AS_PROGUGAI_ADI_V

Source

View Text - Preformatted

SELECT hp.party_number person_number, hp.person_last_name || ', ' || hp.person_first_name || ' ' || hp.person_middle_name person_name, aip.anonymous_id anonymous_id, pv.course_cd course_cd, pv.course_type course_type, pv.responsible_org_unit_cd responsible_org_unit_cd, susa.unit_set_cd unit_set_cd, ci.alternate_code prg_alternate_cd, ci.cal_type prg_cal_type, ci.sequence_number prg_ci_sequence_number, NULL progression_outcome_type, hes.grad_sch_grade yop_grade, hes.mark yop_mark, '-' unit_cd, TO_NUMBER (NULL) sua_version_numbe, NULL teach_cal_type, TO_NUMBER (NULL) teach_ci_sequence_number, TO_NUMBER (NULL) uoo_id, NULL location_cd, '-' grading_period_cd, NULL grading_schema_cd, TO_NUMBER (NULL) gs_version_number, NULL unit_grade, TO_NUMBER (NULL) unit_mark, NULL finalised_outcome_indicator, TO_NUMBER (NULL) assessment_id, NULL assessment_type, NULL ai_grading_schema_cd, TO_NUMBER (NULL) ai_gs_version_number, TO_NUMBER (NULL) ai_mark, NULL ai_grade, 'PRG' record_type, NULL unit_class, NULL REFERENCE FROM igs_en_stdnt_ps_att_sv spa, igs_ps_ver_all pv, hz_parties hp, igs_as_su_setatmpt susa, igs_ca_inst_all ci, igs_ca_type ct, igs_he_en_susa hes, igs_as_anon_id_ps aip WHERE hp.party_id = spa.person_id AND spa.course_cd = pv.course_cd AND spa.version_number = pv.version_number AND spa.course_attempt_status IN ('ENROLLED', 'INACTIVE') AND susa.person_id = spa.person_id AND susa.course_cd = spa.course_cd AND susa.end_dt IS NULL AND susa.rqrmnts_complete_dt IS NULL AND susa.selection_dt IS NOT NULL AND susa.person_id = hes.person_id(+) AND susa.course_cd = hes.course_cd(+) AND susa.unit_set_cd = hes.unit_set_cd(+) AND susa.sequence_number = hes.sequence_number(+) AND aip.person_id(+) = spa.person_id AND aip.course_cd(+) = spa.course_cd AND ci.cal_type = ct.cal_type AND ct.s_cal_cat = 'PROGRESS' AND EXISTS ( SELECT 'X' FROM igs_en_su_attempt_all sua, igs_ca_inst_rel cir WHERE sua.person_id = spa.person_id AND sua.course_cd = spa.course_cd AND sua.cal_type = cir.sub_cal_type AND sua.ci_sequence_number = cir.sub_ci_sequence_number AND ci.cal_type = cir.sup_cal_type AND ci.sequence_number = cir.sup_ci_sequence_number AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED')) AND NOT EXISTS ( SELECT 'X' FROM igs_pr_stdnt_pr_ou_all spo, igs_pr_ou_type_all pot WHERE spo.person_id = spa.person_id AND spo.course_cd = spa.course_cd AND spo.prg_cal_type = ci.cal_type AND spo.prg_ci_sequence_number = ci.sequence_number AND spo.progression_outcome_type = pot.progression_outcome_type AND (pot.s_progression_outcome_type IN ('NOPENALTY', 'ADVANCE', 'REPEATYR', 'MANUAL', 'EXCLUSION', 'EXPULSION' ) )) UNION ALL SELECT hp.party_number person_number, hp.person_last_name || ', ' || hp.person_first_name || ' ' || hp.person_middle_name person_name, aip.anonymous_id anonymous_id, pv.course_cd course_cd, pv.course_type course_type, pv.responsible_org_unit_cd responsible_org_unit_cd, susa.unit_set_cd unit_set_cd, ci.alternate_code prg_alternate_cd, ci.cal_type prg_cal_type, ci.sequence_number prg_ci_sequence_number, spo.progression_outcome_type progression_outcome_type, hes.grad_sch_grade yop_grade, hes.mark yop_mark, '-' unit_cd, TO_NUMBER (NULL) sua_version_numbe, NULL teach_cal_type, TO_NUMBER (NULL) teach_ci_sequence_number, TO_NUMBER (NULL) uoo_id, NULL location_cd, '-' grading_period_cd, NULL grading_schema_cd, TO_NUMBER (NULL) gs_version_number, NULL unit_grade, TO_NUMBER (NULL) unit_mark, NULL finalised_outcome_indicator, TO_NUMBER (NULL) assessment_id, NULL assessment_type, NULL ai_grading_schema_cd, TO_NUMBER (NULL) ai_gs_version_number, TO_NUMBER (NULL) ai_mark, NULL ai_grade, 'PRG' record_type, NULL unit_class, NULL REFERENCE FROM igs_en_stdnt_ps_att_sv spa, igs_ps_ver_all pv, hz_parties hp, igs_as_su_setatmpt susa, igs_ca_inst_all ci, igs_pr_stdnt_pr_ou_all spo, igs_pr_ou_type_all pot, igs_he_en_susa hes, igs_as_anon_id_ps aip WHERE hp.party_id = spa.person_id AND spa.course_cd = pv.course_cd AND spa.version_number = pv.version_number AND spa.course_attempt_status IN ('ENROLLED', 'INACTIVE') AND susa.person_id = spa.person_id AND susa.course_cd = spa.course_cd AND susa.end_dt IS NULL AND susa.rqrmnts_complete_dt IS NULL AND susa.selection_dt IS NOT NULL AND aip.person_id(+) = spa.person_id AND aip.course_cd(+) = spa.course_cd AND susa.person_id = hes.person_id(+) AND susa.course_cd = hes.course_cd(+) AND susa.unit_set_cd = hes.unit_set_cd(+) AND susa.sequence_number = hes.sequence_number(+) AND spo.person_id = spa.person_id AND spo.course_cd = spa.course_cd AND spo.prg_cal_type = ci.cal_type AND spo.prg_ci_sequence_number = ci.sequence_number AND spo.decision_status <> 'REMOVED' AND spo.progression_outcome_type = pot.progression_outcome_type AND pot.s_progression_outcome_type IN ('NOPENALTY', 'ADVANCE', 'REPEATYR', 'MANUAL', 'EXCLUSION', 'EXPULSION') UNION ALL SELECT hp.party_number person_number, hp.person_last_name || ', ' || hp.person_first_name || ' ' || hp.person_middle_name person_name, aip.anonymous_id anonymous_id, pv.course_cd course_cd, pv.course_type course_type, pv.responsible_org_unit_cd responsible_org_unit_cd, susa.unit_set_cd unit_set_cd, NULL prg_alternate_cd, NULL prg_cal_type, TO_NUMBER (NULL) prg_ci_sequence_number, '-' progression_outcome_type, NULL yop_grade, TO_NUMBER (NULL) yop_mark, sua.unit_cd unit_cd, sua.version_number sua_version_numbe, sua.cal_type teach_cal_type, sua.ci_sequence_number teach_ci_sequence_number, sua.uoo_id uoo_id, sua.location_cd location_cd, suao.grading_period_cd grading_period_cd, suao.grading_schema_cd unit_grading_schema_cd, suao.version_number unit_gs_version_number, suao.grade unit_grade, suao.mark unit_mark, suao.finalised_outcome_ind finalised_outcome_indicator, TO_NUMBER (NULL) assessment_id, NULL assessment_type, NULL ai_grading_schema_cd, TO_NUMBER (NULL) ai_gs_version_number, TO_NUMBER (NULL) ai_mark, NULL ai_grade, 'UNIT' record_type, sua.unit_class unit_class, NULL REFERENCE FROM igs_en_su_attempt_sv sua, igs_as_su_stmptout_all suao, igs_en_stdnt_ps_att_all spa, igs_ps_ver_all pv, hz_parties hp, igs_as_su_setatmpt susa, igs_he_en_susa hes, igs_as_anon_id_ps aip, igs_en_unit_set us, igs_en_unit_set_cat usc WHERE hp.party_id = spa.person_id AND spa.course_cd = pv.course_cd AND spa.version_number = pv.version_number AND spa.course_attempt_status IN ('ENROLLED', 'INACTIVE') AND aip.person_id(+) = spa.person_id AND aip.course_cd(+) = spa.course_cd AND sua.person_id = spa.person_id AND sua.course_cd = spa.course_cd AND spa.person_id = susa.person_id AND spa.course_cd = susa.course_cd AND (igs_en_gen_015.get_effective_census_date ( NULL, NULL, sua.cal_type, sua.ci_sequence_number ) BETWEEN susa.selection_dt AND NVL ( susa.rqrmnts_complete_dt, NVL ( susa.end_dt, fnd_date.canonical_to_date ('4000/12/31') ) ) ) AND susa.unit_set_cd = us.unit_set_cd AND susa.us_version_number = us.version_number AND us.unit_set_cat = usc.unit_set_cat AND usc.s_unit_set_cat = 'PRENRL_YR' AND sua.person_id = suao.person_id(+) AND sua.course_cd = suao.course_cd(+) AND sua.uoo_id = suao.uoo_id(+) AND suao.grading_period_cd(+) <> 'MIDTERM' AND ( suao.outcome_dt IS NULL OR suao.outcome_dt IN (SELECT MAX (suao.outcome_dt) FROM igs_as_su_stmptout_all suao WHERE suao.person_id = sua.person_id AND suao.course_cd = sua.course_cd AND suao.uoo_id = sua.uoo_id AND suao.grading_period_cd <> 'MIDTERM') ) AND susa.person_id = hes.person_id(+) AND susa.course_cd = hes.course_cd(+) AND susa.unit_set_cd = hes.unit_set_cd(+) AND susa.sequence_number = hes.sequence_number(+) 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 susa.end_dt IS NULL AND susa.rqrmnts_complete_dt IS NULL AND susa.selection_dt IS NOT NULL UNION ALL SELECT hp.party_number person_number, hp.person_last_name || ', ' || hp.person_first_name || ' ' || hp.person_middle_name person_name, aip.anonymous_id anonymous_id, pv.course_cd course_cd, pv.course_type course_type, pv.responsible_org_unit_cd responsible_org_unit_cd, susa.unit_set_cd unit_set_cd, NULL prg_alternate_cd, NULL prg_cal_type, TO_NUMBER (NULL) prg_ci_sequence_number, '-' progression_outcome_type, NULL yop_grade, TO_NUMBER (NULL) yop_mark, sua.unit_cd unit_cd, sua.version_number sua_version_numbe, sua.cal_type teach_cal_type, sua.ci_sequence_number teach_ci_sequence_number, sua.uoo_id uoo_id, sua.location_cd location_cd, '-' grading_period_cd, NULL unit_grading_schema_cd, TO_NUMBER (NULL) unit_gs_version_number, NULL unit_grade, TO_NUMBER (NULL) unit_mark, NULL finalised_outcome_indicator, sai.ass_id assessment_id, aia.assessment_type assessment_type, sai.grading_schema_cd ai_grading_schema_cd, sai.gs_version_number ai_gs_version_number, sai.mark ai_mark, sai.grade ai_grade, 'AI' record_type, sua.unit_class unit_class, igs_as_gen_003.assp_get_ai_ref ( sai.unit_section_ass_item_id, sai.unit_ass_item_id ) REFERENCE FROM igs_en_su_attempt_sv sua, igs_as_su_setatmpt susa, igs_en_unit_set us, igs_en_unit_set_cat usc, igs_en_stdnt_ps_att_all spa, igs_ps_ver_all pv, hz_parties hp, igs_he_en_susa hes, igs_as_anon_id_ps aip, igs_as_su_atmpt_itm sai, igs_as_assessmnt_itm_all aia WHERE hp.party_id = spa.person_id AND spa.course_cd = pv.course_cd AND spa.version_number = pv.version_number AND spa.course_attempt_status IN ('ENROLLED', 'INACTIVE') AND aip.person_id(+) = spa.person_id AND aip.course_cd(+) = spa.course_cd AND sua.person_id = spa.person_id AND sua.course_cd = spa.course_cd AND spa.person_id = susa.person_id AND spa.course_cd = susa.course_cd AND (igs_en_gen_015.get_effective_census_date ( NULL, NULL, sua.cal_type, sua.ci_sequence_number ) BETWEEN susa.selection_dt AND NVL ( susa.rqrmnts_complete_dt, NVL ( susa.end_dt, fnd_date.canonical_to_date ('4000/12/31') ) ) ) AND susa.unit_set_cd = us.unit_set_cd AND susa.us_version_number = us.version_number AND us.unit_set_cat = usc.unit_set_cat AND usc.s_unit_set_cat = 'PRENRL_YR' AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED') AND sai.person_id = sua.person_id AND sai.uoo_id = sua.uoo_id AND sai.course_cd = sua.course_cd AND sai.ass_id = aia.ass_id AND susa.person_id = hes.person_id(+) AND susa.course_cd = hes.course_cd(+) AND susa.unit_set_cd = hes.unit_set_cd(+) AND susa.sequence_number = hes.sequence_number(+) AND sai.logical_delete_dt IS NULL AND susa.end_dt IS NULL AND susa.rqrmnts_complete_dt IS NULL AND susa.selection_dt IS NOT NULL

View Text - HTML Formatted

SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, CI.ALTERNATE_CODE PRG_ALTERNATE_CD
, CI.CAL_TYPE PRG_CAL_TYPE
, CI.SEQUENCE_NUMBER PRG_CI_SEQUENCE_NUMBER
, NULL PROGRESSION_OUTCOME_TYPE
, HES.GRAD_SCH_GRADE YOP_GRADE
, HES.MARK YOP_MARK
, '-' UNIT_CD
, TO_NUMBER (NULL) SUA_VERSION_NUMBE
, NULL TEACH_CAL_TYPE
, TO_NUMBER (NULL) TEACH_CI_SEQUENCE_NUMBER
, TO_NUMBER (NULL) UOO_ID
, NULL LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL GRADING_SCHEMA_CD
, TO_NUMBER (NULL) GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'PRG' RECORD_TYPE
, NULL UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_STDNT_PS_ATT_SV SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_CA_INST_ALL CI
, IGS_CA_TYPE CT
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND SUSA.PERSON_ID = SPA.PERSON_ID
AND SUSA.COURSE_CD = SPA.COURSE_CD
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND CI.CAL_TYPE = CT.CAL_TYPE
AND CT.S_CAL_CAT = 'PROGRESS'
AND EXISTS ( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT_ALL SUA
, IGS_CA_INST_REL CIR
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SUA.CAL_TYPE = CIR.SUB_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = CIR.SUB_CI_SEQUENCE_NUMBER
AND CI.CAL_TYPE = CIR.SUP_CAL_TYPE
AND CI.SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED'))
AND NOT EXISTS ( SELECT 'X'
FROM IGS_PR_STDNT_PR_OU_ALL SPO
, IGS_PR_OU_TYPE_ALL POT
WHERE SPO.PERSON_ID = SPA.PERSON_ID
AND SPO.COURSE_CD = SPA.COURSE_CD
AND SPO.PRG_CAL_TYPE = CI.CAL_TYPE
AND SPO.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND SPO.PROGRESSION_OUTCOME_TYPE = POT.PROGRESSION_OUTCOME_TYPE
AND (POT.S_PROGRESSION_OUTCOME_TYPE IN ('NOPENALTY'
, 'ADVANCE'
, 'REPEATYR'
, 'MANUAL'
, 'EXCLUSION'
, 'EXPULSION' ) )) UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, CI.ALTERNATE_CODE PRG_ALTERNATE_CD
, CI.CAL_TYPE PRG_CAL_TYPE
, CI.SEQUENCE_NUMBER PRG_CI_SEQUENCE_NUMBER
, SPO.PROGRESSION_OUTCOME_TYPE PROGRESSION_OUTCOME_TYPE
, HES.GRAD_SCH_GRADE YOP_GRADE
, HES.MARK YOP_MARK
, '-' UNIT_CD
, TO_NUMBER (NULL) SUA_VERSION_NUMBE
, NULL TEACH_CAL_TYPE
, TO_NUMBER (NULL) TEACH_CI_SEQUENCE_NUMBER
, TO_NUMBER (NULL) UOO_ID
, NULL LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL GRADING_SCHEMA_CD
, TO_NUMBER (NULL) GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'PRG' RECORD_TYPE
, NULL UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_STDNT_PS_ATT_SV SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_CA_INST_ALL CI
, IGS_PR_STDNT_PR_OU_ALL SPO
, IGS_PR_OU_TYPE_ALL POT
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND SUSA.PERSON_ID = SPA.PERSON_ID
AND SUSA.COURSE_CD = SPA.COURSE_CD
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND SPO.PERSON_ID = SPA.PERSON_ID
AND SPO.COURSE_CD = SPA.COURSE_CD
AND SPO.PRG_CAL_TYPE = CI.CAL_TYPE
AND SPO.PRG_CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND SPO.DECISION_STATUS <> 'REMOVED'
AND SPO.PROGRESSION_OUTCOME_TYPE = POT.PROGRESSION_OUTCOME_TYPE
AND POT.S_PROGRESSION_OUTCOME_TYPE IN ('NOPENALTY'
, 'ADVANCE'
, 'REPEATYR'
, 'MANUAL'
, 'EXCLUSION'
, 'EXPULSION') UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, NULL PRG_ALTERNATE_CD
, NULL PRG_CAL_TYPE
, TO_NUMBER (NULL) PRG_CI_SEQUENCE_NUMBER
, '-' PROGRESSION_OUTCOME_TYPE
, NULL YOP_GRADE
, TO_NUMBER (NULL) YOP_MARK
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBE
, SUA.CAL_TYPE TEACH_CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER TEACH_CI_SEQUENCE_NUMBER
, SUA.UOO_ID UOO_ID
, SUA.LOCATION_CD LOCATION_CD
, SUAO.GRADING_PERIOD_CD GRADING_PERIOD_CD
, SUAO.GRADING_SCHEMA_CD UNIT_GRADING_SCHEMA_CD
, SUAO.VERSION_NUMBER UNIT_GS_VERSION_NUMBER
, SUAO.GRADE UNIT_GRADE
, SUAO.MARK UNIT_MARK
, SUAO.FINALISED_OUTCOME_IND FINALISED_OUTCOME_INDICATOR
, TO_NUMBER (NULL) ASSESSMENT_ID
, NULL ASSESSMENT_TYPE
, NULL AI_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) AI_GS_VERSION_NUMBER
, TO_NUMBER (NULL) AI_MARK
, NULL AI_GRADE
, 'UNIT' RECORD_TYPE
, SUA.UNIT_CLASS UNIT_CLASS
, NULL REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_STMPTOUT_ALL SUAO
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_AS_SU_SETATMPT SUSA
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE ( NULL
, NULL
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER ) BETWEEN SUSA.SELECTION_DT
AND NVL ( SUSA.RQRMNTS_COMPLETE_DT
, NVL ( SUSA.END_DT
, FND_DATE.CANONICAL_TO_DATE ('4000/12/31') ) ) )
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND SUA.PERSON_ID = SUAO.PERSON_ID(+)
AND SUA.COURSE_CD = SUAO.COURSE_CD(+)
AND SUA.UOO_ID = SUAO.UOO_ID(+)
AND SUAO.GRADING_PERIOD_CD(+) <> 'MIDTERM'
AND ( SUAO.OUTCOME_DT IS NULL OR SUAO.OUTCOME_DT IN (SELECT MAX (SUAO.OUTCOME_DT)
FROM IGS_AS_SU_STMPTOUT_ALL SUAO
WHERE SUAO.PERSON_ID = SUA.PERSON_ID
AND SUAO.COURSE_CD = SUA.COURSE_CD
AND SUAO.UOO_ID = SUA.UOO_ID
AND SUAO.GRADING_PERIOD_CD <> 'MIDTERM') )
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
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 SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL UNION ALL SELECT HP.PARTY_NUMBER PERSON_NUMBER
, HP.PERSON_LAST_NAME || '
, ' || HP.PERSON_FIRST_NAME || ' ' || HP.PERSON_MIDDLE_NAME PERSON_NAME
, AIP.ANONYMOUS_ID ANONYMOUS_ID
, PV.COURSE_CD COURSE_CD
, PV.COURSE_TYPE COURSE_TYPE
, PV.RESPONSIBLE_ORG_UNIT_CD RESPONSIBLE_ORG_UNIT_CD
, SUSA.UNIT_SET_CD UNIT_SET_CD
, NULL PRG_ALTERNATE_CD
, NULL PRG_CAL_TYPE
, TO_NUMBER (NULL) PRG_CI_SEQUENCE_NUMBER
, '-' PROGRESSION_OUTCOME_TYPE
, NULL YOP_GRADE
, TO_NUMBER (NULL) YOP_MARK
, SUA.UNIT_CD UNIT_CD
, SUA.VERSION_NUMBER SUA_VERSION_NUMBE
, SUA.CAL_TYPE TEACH_CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER TEACH_CI_SEQUENCE_NUMBER
, SUA.UOO_ID UOO_ID
, SUA.LOCATION_CD LOCATION_CD
, '-' GRADING_PERIOD_CD
, NULL UNIT_GRADING_SCHEMA_CD
, TO_NUMBER (NULL) UNIT_GS_VERSION_NUMBER
, NULL UNIT_GRADE
, TO_NUMBER (NULL) UNIT_MARK
, NULL FINALISED_OUTCOME_INDICATOR
, SAI.ASS_ID ASSESSMENT_ID
, AIA.ASSESSMENT_TYPE ASSESSMENT_TYPE
, SAI.GRADING_SCHEMA_CD AI_GRADING_SCHEMA_CD
, SAI.GS_VERSION_NUMBER AI_GS_VERSION_NUMBER
, SAI.MARK AI_MARK
, SAI.GRADE AI_GRADE
, 'AI' RECORD_TYPE
, SUA.UNIT_CLASS UNIT_CLASS
, IGS_AS_GEN_003.ASSP_GET_AI_REF ( SAI.UNIT_SECTION_ASS_ITEM_ID
, SAI.UNIT_ASS_ITEM_ID ) REFERENCE
FROM IGS_EN_SU_ATTEMPT_SV SUA
, IGS_AS_SU_SETATMPT SUSA
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL PV
, HZ_PARTIES HP
, IGS_HE_EN_SUSA HES
, IGS_AS_ANON_ID_PS AIP
, IGS_AS_SU_ATMPT_ITM SAI
, IGS_AS_ASSESSMNT_ITM_ALL AIA
WHERE HP.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = PV.COURSE_CD
AND SPA.VERSION_NUMBER = PV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'INACTIVE')
AND AIP.PERSON_ID(+) = SPA.PERSON_ID
AND AIP.COURSE_CD(+) = SPA.COURSE_CD
AND SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SPA.PERSON_ID = SUSA.PERSON_ID
AND SPA.COURSE_CD = SUSA.COURSE_CD
AND (IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE ( NULL
, NULL
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER ) BETWEEN SUSA.SELECTION_DT
AND NVL ( SUSA.RQRMNTS_COMPLETE_DT
, NVL ( SUSA.END_DT
, FND_DATE.CANONICAL_TO_DATE ('4000/12/31') ) ) )
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND SUSA.US_VERSION_NUMBER = US.VERSION_NUMBER
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
AND SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED')
AND SAI.PERSON_ID = SUA.PERSON_ID
AND SAI.UOO_ID = SUA.UOO_ID
AND SAI.COURSE_CD = SUA.COURSE_CD
AND SAI.ASS_ID = AIA.ASS_ID
AND SUSA.PERSON_ID = HES.PERSON_ID(+)
AND SUSA.COURSE_CD = HES.COURSE_CD(+)
AND SUSA.UNIT_SET_CD = HES.UNIT_SET_CD(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+)
AND SAI.LOGICAL_DELETE_DT IS NULL
AND SUSA.END_DT IS NULL
AND SUSA.RQRMNTS_COMPLETE_DT IS NULL
AND SUSA.SELECTION_DT IS NOT NULL