View: IGS_PR_STU_ACAD_V

View Text

SELECT SPA.PERSON_ID PERSON_ID
, P.PARTY_NUMBER PERSON_NUMBER
, SPA.COURSE_CD COURSE_CD
, CRV.COURSE_TYPE COURSE_TYPE
, CI2.CAL_TYPE ACAD_CAL_TYPE
, CI2.SEQUENCE_NUMBER ACAD_SEQUENCE_NUMBER
, CI2.ALTERNATE_CODE ACAD_ALTERNATE_CODE
, CI2.DESCRIPTION ACAD_DESCRIPTION
, CI.CAL_TYPE LOAD_CAL_TYPE
, CI.SEQUENCE_NUMBER LOAD_SEQUENCE_NUMBER
, CI.ALTERNATE_CODE LOAD_ALTERNATE_CODE
, CI.DESCRIPTION LOAD_DESCRIPTION
, CI.START_DT LOAD_START_DT
, CI.END_DT LOAD_END_DT
, ST.STAT_TYPE STAT_TYPE
, ST.DESCRIPTION STAT_TYPE_DESC
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) TERM_GPA
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) TERM_GPA_CP
, IGS_PR_ACAD_DETAILS.GET_LOAD_GPA_QP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) TERM_GPA_QP
, IGS_PR_ACAD_DETAILS.GET_LOAD_EARNED_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) TERM_EARNED_CP
, IGS_PR_ACAD_DETAILS.GET_LOAD_ATTEMPTED_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) TERM_ATTEMPTED_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) CUM_GPA
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) CUM_GPA_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_GPA_QP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) CUM_GPA_QP
, IGS_PR_ACAD_DETAILS.GET_CUM_EARNED_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) CUM_EARNED_CP
, IGS_PR_ACAD_DETAILS.GET_CUM_ATTEMPTED_CP(SPA.PERSON_ID
, SPA.COURSE_CD
, CI.CAL_TYPE
, CI.SEQUENCE_NUMBER
, ST.STAT_TYPE) CUM_ATTEMPTED_CP
, IGS_PR_GET_CLASS_STD.GET_CLASS_STANDING(SPA.PERSON_ID
, SPA.COURSE_CD
, 'N'
, NULL
, NULL
, NULL) CLASS_STANDING
, IGS_PR_GET_CLASS_STD.GET_CLASS_STANDING(SPA.PERSON_ID
, SPA.COURSE_CD
, 'Y'
, NULL
, NULL
, NULL) PREDICTIVE_CLASS_STANDING
, IGS_EN_PRC_LOAD.ENRP_GET_PRG_ATT_TYPE(SPA.PERSON_ID
, SPA.COURSE_CD
, CI2.CAL_TYPE
, CI2.SEQUENCE_NUMBER) ATTENDANCE_TYPE
, IGS_PR_GEN_006.GET_ANTCP_COMPL_DT(SPA.PERSON_ID
, SPA.COURSE_CD) ENRL_ANTCP_COMPL_DT
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
, IGS_PR_STAT_TYPE ST
, IGS_CA_TYPE CT
, IGS_CA_STAT CS
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 CI.CAL_TYPE = CT.CAL_TYPE
AND CT.S_CAL_CAT = 'LOAD'
AND CI.CAL_STATUS = CS.CAL_STATUS
AND CS.S_CAL_STATUS = 'ACTIVE'
AND EXISTS ( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT SUA
, IGS_CA_TEACH_TO_LOAD_V TTL
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 SUA.UNIT_ATTEMPT_STATUS IN ('COMPLETED'
, 'DUPLICATE'
, 'ENROLLED'
, 'DISCONTIN') )
AND ST.CLOSED_IND = 'N'
AND (EXISTS (SELECT 'X'
FROM IGS_PR_ORG_STAT OS
WHERE ST.STAT_TYPE = OS.STAT_TYPE
AND CRV.RESPONSIBLE_ORG_UNIT_CD = OS.ORG_UNIT_CD) OR EXISTS (SELECT 'X'
FROM IGS_PR_INST_STAT INS
WHERE ST.STAT_TYPE = INS.STAT_TYPE ) )

Columns