[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This view contains the details Student Program Attempts, containing statistics information including GPA and Credit Point figures. |
| Implementation/DBA Data: | Not implemented in this database |
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