VIEW: APPS.IGS_PR_ACAD_UNIT_V

Source

View Text - Preformatted

SELECT p.party_number person_number, sua.person_id, sua.course_cd, crv.course_type, sua.unit_cd, sua.version_number, ttl.teach_cal_type, ttl.teach_ci_sequence_number, ttl.teach_description, ttl.teach_alternate_code, sua.unit_attempt_status, igs_ss_enr_details.get_title (sua.person_id, sua.uoo_id, sua.unit_cd, sua.version_number, sua.course_cd) title, sua.location_cd, sua.unit_class, uoo.uoo_id, uoo.call_number, ttl.load_cal_type, ttl.load_ci_sequence_number, ttl.load_description, ttl.load_alternate_code, igs_pr_acad_details.get_sua_yop (sua.person_id, sua.course_cd, sua.cal_type, sua.ci_sequence_number) year_of_program, igs_pr_acad_details.get_sua_mark ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) mark, igs_pr_acad_details.get_sua_grade ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) grade, igs_pr_acad_details.get_sua_gpa ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) gpa, igs_pr_acad_details.get_sua_gpa_cp ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) gpa_cp, igs_pr_acad_details.get_sua_gpa_qp ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) gpa_qp, igs_pr_acad_details.get_sua_earned_cp ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) earned_cp, igs_pr_acad_details.get_sua_attempted_cp ( sua.person_id, sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number, sua.unit_attempt_status, NULL, sua.uoo_id ) attempted_cp, NVL ( sua.student_career_statistics, (SELECT pur1.student_career_statistics FROM igs_ps_prg_unit_rel pur1 WHERE pur1.unit_type_id = uv.unit_type_id AND pur1.student_career_level = crv.course_type) ) statistics_flag, DECODE ( (SELECT 'X' FROM igs_en_stdnt_ps_att spa2, igs_ps_ver crv2, igs_ps_stdnt_unt_trn sut WHERE sua.unit_attempt_status = 'DUPLICATE' AND sua.person_id = sut.person_id AND sua.unit_cd = sut.unit_cd AND sua.cal_type = sut.cal_type AND sua.ci_sequence_number = sut.ci_sequence_number AND sut.person_id = spa2.person_id AND sut.transfer_course_cd = spa2.course_cd AND spa2.course_cd = crv2.course_cd AND spa2.version_number = crv2.version_number AND crv2.course_type != crv.course_type AND ROWNUM < 2), 'X', 'Y', 'N' ) transferred_flag FROM hz_parties p, igs_en_stdnt_ps_att_all spa, igs_ps_ver_all crv, igs_ca_teach_to_load_v ttl, igs_en_su_attempt_all sua, igs_ps_unit_ofr_opt_all uoo, igs_ps_unit_ver_all uv WHERE p.party_id = sua.person_id AND sua.person_id = spa.person_id AND sua.course_cd = spa.course_cd AND sua.unit_attempt_status IN ('COMPLETED', 'DUPLICATE', 'ENROLLED', 'DISCONTIN') AND sua.uoo_id = uoo.uoo_id AND spa.course_cd = crv.course_cd AND spa.version_number = crv.version_number AND uv.unit_cd = sua.unit_cd AND uv.version_number = sua.version_number AND sua.cal_type = ttl.teach_cal_type AND sua.ci_sequence_number = ttl.teach_ci_sequence_number AND ((sua.student_career_transcript = 'Y') OR (NOT EXISTS ( SELECT 'Y' FROM igs_ps_prg_unit_rel pur WHERE pur.unit_type_id = uv.unit_type_id AND pur.student_career_level = crv.course_type AND pur.student_career_transcript = 'N') AND NVL (sua.student_career_transcript, 'X') <> 'N'))

View Text - HTML Formatted

SELECT P.PARTY_NUMBER PERSON_NUMBER
, SUA.PERSON_ID
, SUA.COURSE_CD
, CRV.COURSE_TYPE
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, TTL.TEACH_CAL_TYPE
, TTL.TEACH_CI_SEQUENCE_NUMBER
, TTL.TEACH_DESCRIPTION
, TTL.TEACH_ALTERNATE_CODE
, SUA.UNIT_ATTEMPT_STATUS
, IGS_SS_ENR_DETAILS.GET_TITLE (SUA.PERSON_ID
, SUA.UOO_ID
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.COURSE_CD) TITLE
, SUA.LOCATION_CD
, SUA.UNIT_CLASS
, UOO.UOO_ID
, UOO.CALL_NUMBER
, TTL.LOAD_CAL_TYPE
, TTL.LOAD_CI_SEQUENCE_NUMBER
, TTL.LOAD_DESCRIPTION
, TTL.LOAD_ALTERNATE_CODE
, IGS_PR_ACAD_DETAILS.GET_SUA_YOP (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER) YEAR_OF_PROGRAM
, IGS_PR_ACAD_DETAILS.GET_SUA_MARK ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) MARK
, IGS_PR_ACAD_DETAILS.GET_SUA_GRADE ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) GRADE
, IGS_PR_ACAD_DETAILS.GET_SUA_GPA ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) GPA
, IGS_PR_ACAD_DETAILS.GET_SUA_GPA_CP ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) GPA_CP
, IGS_PR_ACAD_DETAILS.GET_SUA_GPA_QP ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) GPA_QP
, IGS_PR_ACAD_DETAILS.GET_SUA_EARNED_CP ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) EARNED_CP
, IGS_PR_ACAD_DETAILS.GET_SUA_ATTEMPTED_CP ( SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS
, NULL
, SUA.UOO_ID ) ATTEMPTED_CP
, NVL ( SUA.STUDENT_CAREER_STATISTICS
, (SELECT PUR1.STUDENT_CAREER_STATISTICS
FROM IGS_PS_PRG_UNIT_REL PUR1
WHERE PUR1.UNIT_TYPE_ID = UV.UNIT_TYPE_ID
AND PUR1.STUDENT_CAREER_LEVEL = CRV.COURSE_TYPE) ) STATISTICS_FLAG
, DECODE ( (SELECT 'X'
FROM IGS_EN_STDNT_PS_ATT SPA2
, IGS_PS_VER CRV2
, IGS_PS_STDNT_UNT_TRN SUT
WHERE SUA.UNIT_ATTEMPT_STATUS = 'DUPLICATE'
AND SUA.PERSON_ID = SUT.PERSON_ID
AND SUA.UNIT_CD = SUT.UNIT_CD
AND SUA.CAL_TYPE = SUT.CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = SUT.CI_SEQUENCE_NUMBER
AND SUT.PERSON_ID = SPA2.PERSON_ID
AND SUT.TRANSFER_COURSE_CD = SPA2.COURSE_CD
AND SPA2.COURSE_CD = CRV2.COURSE_CD
AND SPA2.VERSION_NUMBER = CRV2.VERSION_NUMBER
AND CRV2.COURSE_TYPE != CRV.COURSE_TYPE
AND ROWNUM < 2)
, 'X'
, 'Y'
, 'N' ) TRANSFERRED_FLAG
FROM HZ_PARTIES P
, IGS_EN_STDNT_PS_ATT_ALL SPA
, IGS_PS_VER_ALL CRV
, IGS_CA_TEACH_TO_LOAD_V TTL
, IGS_EN_SU_ATTEMPT_ALL SUA
, IGS_PS_UNIT_OFR_OPT_ALL UOO
, IGS_PS_UNIT_VER_ALL UV
WHERE P.PARTY_ID = SUA.PERSON_ID
AND SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SUA.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DUPLICATE'
, 'ENROLLED'
, 'DISCONTIN')
AND SUA.UOO_ID = UOO.UOO_ID
AND SPA.COURSE_CD = CRV.COURSE_CD
AND SPA.VERSION_NUMBER = CRV.VERSION_NUMBER
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SUA.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND ((SUA.STUDENT_CAREER_TRANSCRIPT = 'Y') OR (NOT EXISTS ( SELECT 'Y'
FROM IGS_PS_PRG_UNIT_REL PUR
WHERE PUR.UNIT_TYPE_ID = UV.UNIT_TYPE_ID
AND PUR.STUDENT_CAREER_LEVEL = CRV.COURSE_TYPE
AND PUR.STUDENT_CAREER_TRANSCRIPT = 'N')
AND NVL (SUA.STUDENT_CAREER_TRANSCRIPT
, 'X') <> 'N'))