VIEW: APPS.IGS_PR_ACAD_PROGRAM_V

Source

View Text - Preformatted

SELECT pe.person_number, spa.person_id, pe.full_name, spa.course_cd program_cd, spa.version_number program_version_number, pv.title program_title, pv.course_type program_type, pt.description program_type_description, pv.responsible_org_unit_cd, ou.description responsible_org_unit_desc, spa.primary_program_type, spa.commencement_dt start_dt, spa.course_attempt_status program_attempt_status, spa.discontinued_dt, spa.discontinuation_reason_cd, dr.description discontinuation_description, spa.course_rqrmnts_complete_dt, DECODE( NVL(spa.primary_program_type, 'PRIMARY'), 'PRIMARY', igs_pr_acad_details.get_cum_attempted_cp ( spa.person_id, spa.course_cd, term.load_cal_type, term.load_ci_sequence_number), 0) cumulative_attempted_credit, DECODE( NVL(spa.primary_program_type, 'PRIMARY'), 'PRIMARY', igs_pr_acad_details.get_cum_earned_cp ( spa.person_id, spa.course_cd, term.load_cal_type, term.load_ci_sequence_number), 0) cumulative_earned_credit, DECODE( NVL(spa.primary_program_type,'PRIMARY'), 'PRIMARY', igs_pr_acad_details.get_cum_gpa_cp ( spa.person_id, spa.course_cd, term.load_cal_type, term.load_ci_sequence_number), 0) cumulative_grade_points, DECODE( NVL(spa.primary_program_type,'PRIMARY'), 'PRIMARY', igs_pr_acad_details.get_cum_gpa ( spa.person_id, spa.course_cd, term.load_cal_type, term.load_ci_sequence_number), 0) cumulative_GPA, gr.award_cd, pa.award_title, gr.conferral_dt, gr.honours_level, hl.description honours_level_description FROM igs_en_stdnt_ps_att spa, igs_pe_person pe, igs_ps_ver_all pv, igs_ps_type pt, igs_gr_graduand gr, igs_gr_honours_level_all hl, igs_ps_awd pa, igs_en_dcnt_reasoncd_all dr, igs_or_unit ou, ( SELECT DISTINCT sua.person_id, sua.course_cd, ttl.load_cal_type, ttl.load_ci_sequence_number FROM igs_en_su_attempt sua, igs_ca_teach_to_load_v ttl WHERE sua.cal_type = ttl.teach_cal_type AND sua.ci_sequence_number = ttl.teach_ci_sequence_number AND sua.unit_attempt_status IN ('COMPLETED','DUPLICATE','ENROLLED','DISCONTIN') AND sua.ci_end_dt = (SELECT MAX(sua.ci_end_dt) FROM igs_en_su_attempt sua1 WHERE sua.person_id = sua1.person_id AND sua.course_cd = sua1.course_cd AND sua1.unit_attempt_status IN ('COMPLETED','DUPLICATE','ENROLLED','DISCONTIN') )) term WHERE pv.course_cd = spa.course_cd AND pv.version_number = spa.version_number AND pt.course_type = pv.course_type AND pe.person_id = spa.person_id AND gr.person_id (+)= spa.person_id AND gr.course_cd (+)= spa.course_cd AND hl.honours_level (+)= gr.honours_level AND pa.award_cd (+)= gr.award_cd AND term.person_id = spa.person_id AND term.course_cd = spa.course_cd AND dr.discontinuation_reason_cd (+)= spa.discontinuation_reason_cd AND ou.org_unit_cd = pv.responsible_org_unit_cd

View Text - HTML Formatted

SELECT PE.PERSON_NUMBER
, SPA.PERSON_ID
, PE.FULL_NAME
, SPA.COURSE_CD PROGRAM_CD
, SPA.VERSION_NUMBER PROGRAM_VERSION_NUMBER
, PV.TITLE PROGRAM_TITLE
, PV.COURSE_TYPE PROGRAM_TYPE
, PT.DESCRIPTION PROGRAM_TYPE_DESCRIPTION
, PV.RESPONSIBLE_ORG_UNIT_CD
, OU.DESCRIPTION RESPONSIBLE_ORG_UNIT_DESC
, SPA.PRIMARY_PROGRAM_TYPE
, SPA.COMMENCEMENT_DT START_DT
, SPA.COURSE_ATTEMPT_STATUS PROGRAM_ATTEMPT_STATUS
, SPA.DISCONTINUED_DT
, SPA.DISCONTINUATION_REASON_CD
, DR.DESCRIPTION DISCONTINUATION_DESCRIPTION
, SPA.COURSE_RQRMNTS_COMPLETE_DT
, DECODE( NVL(SPA.PRIMARY_PROGRAM_TYPE
, 'PRIMARY')
, 'PRIMARY'
, IGS_PR_ACAD_DETAILS.GET_CUM_ATTEMPTED_CP ( SPA.PERSON_ID
, SPA.COURSE_CD
, TERM.LOAD_CAL_TYPE
, TERM.LOAD_CI_SEQUENCE_NUMBER)
, 0) CUMULATIVE_ATTEMPTED_CREDIT
, DECODE( NVL(SPA.PRIMARY_PROGRAM_TYPE
, 'PRIMARY')
, 'PRIMARY'
, IGS_PR_ACAD_DETAILS.GET_CUM_EARNED_CP ( SPA.PERSON_ID
, SPA.COURSE_CD
, TERM.LOAD_CAL_TYPE
, TERM.LOAD_CI_SEQUENCE_NUMBER)
, 0) CUMULATIVE_EARNED_CREDIT
, DECODE( NVL(SPA.PRIMARY_PROGRAM_TYPE
, 'PRIMARY')
, 'PRIMARY'
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA_CP ( SPA.PERSON_ID
, SPA.COURSE_CD
, TERM.LOAD_CAL_TYPE
, TERM.LOAD_CI_SEQUENCE_NUMBER)
, 0) CUMULATIVE_GRADE_POINTS
, DECODE( NVL(SPA.PRIMARY_PROGRAM_TYPE
, 'PRIMARY')
, 'PRIMARY'
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA ( SPA.PERSON_ID
, SPA.COURSE_CD
, TERM.LOAD_CAL_TYPE
, TERM.LOAD_CI_SEQUENCE_NUMBER)
, 0) CUMULATIVE_GPA
, GR.AWARD_CD
, PA.AWARD_TITLE
, GR.CONFERRAL_DT
, GR.HONOURS_LEVEL
, HL.DESCRIPTION HONOURS_LEVEL_DESCRIPTION
FROM IGS_EN_STDNT_PS_ATT SPA
, IGS_PE_PERSON PE
, IGS_PS_VER_ALL PV
, IGS_PS_TYPE PT
, IGS_GR_GRADUAND GR
, IGS_GR_HONOURS_LEVEL_ALL HL
, IGS_PS_AWD PA
, IGS_EN_DCNT_REASONCD_ALL DR
, IGS_OR_UNIT OU
, ( SELECT DISTINCT SUA.PERSON_ID
, SUA.COURSE_CD
, TTL.LOAD_CAL_TYPE
, TTL.LOAD_CI_SEQUENCE_NUMBER
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_CA_TEACH_TO_LOAD_V TTL
WHERE SUA.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND SUA.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DUPLICATE'
, 'ENROLLED'
, 'DISCONTIN')
AND SUA.CI_END_DT = (SELECT MAX(SUA.CI_END_DT)
FROM IGS_EN_SU_ATTEMPT SUA1
WHERE SUA.PERSON_ID = SUA1.PERSON_ID
AND SUA.COURSE_CD = SUA1.COURSE_CD
AND SUA1.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DUPLICATE'
, 'ENROLLED'
, 'DISCONTIN') )) TERM
WHERE PV.COURSE_CD = SPA.COURSE_CD
AND PV.VERSION_NUMBER = SPA.VERSION_NUMBER
AND PT.COURSE_TYPE = PV.COURSE_TYPE
AND PE.PERSON_ID = SPA.PERSON_ID
AND GR.PERSON_ID (+)= SPA.PERSON_ID
AND GR.COURSE_CD (+)= SPA.COURSE_CD
AND HL.HONOURS_LEVEL (+)= GR.HONOURS_LEVEL
AND PA.AWARD_CD (+)= GR.AWARD_CD
AND TERM.PERSON_ID = SPA.PERSON_ID
AND TERM.COURSE_CD = SPA.COURSE_CD
AND DR.DISCONTINUATION_REASON_CD (+)= SPA.DISCONTINUATION_REASON_CD
AND OU.ORG_UNIT_CD = PV.RESPONSIBLE_ORG_UNIT_CD