VIEW: APPS.IGS_PR_ACAD_YEAR_V

Source

View Text - Preformatted

SELECT p.party_number person_number, spa.person_id, spa.course_cd, us.unit_set_cd, us.version_number UNIT_SET_CD_VERSION, hes.grad_sch_grade, hes.mark, SUM(NVL(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), 0)) earned_cp FROM hz_parties p, IGS_EN_STDNT_PS_ATT SPA, IGS_EN_SU_ATTEMPT SUA, IGS_AS_SU_SETATMPT SUSA, IGS_EN_UNIT_SET US, IGS_EN_UNIT_SET_CAT USC, igs_he_en_susa hes WHERE p.party_id = sua.person_id 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('9999/12/31')))) AND susa.unit_set_cd = us.unit_set_cd AND us.unit_set_cat = usc.unit_set_cat AND usc.s_unit_set_cat = 'PRENRL_YR' 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.us_version_number = hes.us_version_number(+) AND susa.sequence_number = hes.sequence_number(+) GROUP BY p.party_number, spa.person_id, spa.course_cd, us.unit_set_cd, us.version_number, hes.grad_sch_grade, hes.mark

View Text - HTML Formatted

SELECT P.PARTY_NUMBER PERSON_NUMBER
, SPA.PERSON_ID
, SPA.COURSE_CD
, US.UNIT_SET_CD
, US.VERSION_NUMBER UNIT_SET_CD_VERSION
, HES.GRAD_SCH_GRADE
, HES.MARK
, SUM(NVL(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)
, 0)) EARNED_CP
FROM HZ_PARTIES P
, IGS_EN_STDNT_PS_ATT SPA
, IGS_EN_SU_ATTEMPT SUA
, IGS_AS_SU_SETATMPT SUSA
, IGS_EN_UNIT_SET US
, IGS_EN_UNIT_SET_CAT USC
, IGS_HE_EN_SUSA HES
WHERE P.PARTY_ID = SUA.PERSON_ID
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('9999/12/31'))))
AND SUSA.UNIT_SET_CD = US.UNIT_SET_CD
AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
AND USC.S_UNIT_SET_CAT = 'PRENRL_YR'
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.US_VERSION_NUMBER = HES.US_VERSION_NUMBER(+)
AND SUSA.SEQUENCE_NUMBER = HES.SEQUENCE_NUMBER(+) GROUP BY P.PARTY_NUMBER
, SPA.PERSON_ID
, SPA.COURSE_CD
, US.UNIT_SET_CD
, US.VERSION_NUMBER
, HES.GRAD_SCH_GRADE
, HES.MARK