[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This view contains the details of a Student Program Attempt within a Load Calendar with cumulative and period statistics. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT P.PARTY_NUMBER PERSON_NUMBER
, SPA.PERSON_ID
, SPA.COURSE_CD
, CRV.COURSE_TYPE
, CI2.DESCRIPTION ACAD_DESCRIPTION
, CI2.ALTERNATE_CODE ACAD_ALTERNATE_CODE
, CI.CAL_TYPE LOAD_CAL_TYPE
, CI.SEQUENCE_NUMBER LOAD_CI_SEQUENCE_NUMBER
, CI.DESCRIPTION LOAD_DESCRIPTION
, CI.ALTERNATE_CODE LOAD_ALTERNATE_CODE
, CI.START_DT LOAD_START_DT
, CI.END_DT LOAD_END_DT
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) TERM_GPA
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) TERM_GPA_CP
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA_QP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) TERM_GPA_QP
, IGS_PR_ACAD_DETAILS.GET_LOAD_EARNED_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) TERM_EARNED_CP
, IGS_PR_ACAD_DETAILS.GET_LOAD_ATTEMPTED_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) TERM_ATTEMPTED_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) CUM_GPA
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) CUM_GPA_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA_QP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) CUM_GPA_QP
, IGS_PR_ACAD_DETAILS.GET_CUM_EARNED_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) CUM_EARNED_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_ATTEMPTED_CP( SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER) CUM_ATTEMPTED_CP
FROM HZ_PARTIES P
, IGS_EN_STDNT_PS_ATT SPA
, IGS_PS_VER CRV
, IGS_CA_INST CI
, IGS_CA_INST_REL CIR
, IGS_CA_INST CI2
, IGS_CA_TYPE CT2
, IGS_CA_STAT CS2
WHERE P.PARTY_ID = SPA.PERSON_ID
AND SPA.COURSE_CD = CRV.COURSE_CD
AND SPA.VERSION_NUMBER = CRV.VERSION_NUMBER
AND CI.CAL_TYPE = CIR.SUB_CAL_TYPE
AND CI.SEQUENCE_NUMBER = CIR.SUB_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = CIR.SUP_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = CIR.SUP_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = CT2.CAL_TYPE
AND CT2.S_CAL_CAT = 'ACADEMIC'
AND CI2.CAL_STATUS = CS2.CAL_STATUS
AND CS2.S_CAL_STATUS = 'ACTIVE'
AND EXISTS ( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_PS_UNIT_VER UV
, IGS_CA_TEACH_TO_LOAD_V TTL
, IGS_PS_PRG_UNIT_REL PUR
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND SUA.CAL_TYPE = TTL.TEACH_CAL_TYPE
AND SUA.CI_SEQUENCE_NUMBER = TTL.TEACH_CI_SEQUENCE_NUMBER
AND CI.CAL_TYPE = TTL.LOAD_CAL_TYPE
AND CI.SEQUENCE_NUMBER = TTL.LOAD_CI_SEQUENCE_NUMBER
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SUA.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DUPLICATE'
, 'ENROLLED'
, 'DISCONTIN')
AND (SUA.STUDENT_CAREER_TRANSCRIPT = 'Y' OR (SUA.STUDENT_CAREER_TRANSCRIPT IS NULL
AND PUR.UNIT_TYPE_ID = UV.UNIT_TYPE_ID
AND PUR.STUDENT_CAREER_LEVEL = CRV.COURSE_TYPE
AND PUR.STUDENT_CAREER_TRANSCRIPT = 'Y')))