View: IGS_PR_ACAD_UNIT_V

View Text

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'))

Columns