View: IGS_DA_XML_COURSE_V

Product:	IGS - Student System
Description:	XML element CourseType (child of CourseInstitution)1)(SUA) Student Unit Attempt"s at local Institution. Includes Units local institution Units and currently enrolled units.Enrolled units can be excluded EIP Control feature code.2) (AV) Adv
Implementation/DBA Data:	APPS.IGS_DA_XML_COURSE_V

View Text

SELECT DISTINCT IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (SUA.UNIT_CD
, 'A')
, IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (SUA.UNIT_CD
, 'N')
, IGS_SS_ENR_DETAILS.GET_TITLE (SUA.PERSON_ID
, SUA.UOO_ID
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.COURSE_CD)
, TTL.LOAD_ALTERNATE_CODE
, IGS_DA_XML_PKG.GET_SUA_ATTEMPTED_CP (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_SUA_EARNED_CP (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_SUA_GPA_CP (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_SUA_GPA_QP (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_SUA_GRADE (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_SUA_RESULT_TYPE (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UOO_ID
, SUA.UNIT_ATTEMPT_STATUS)
, IGS_DA_XML_PKG.GET_UNIT_REPEATABLE (SUA.PERSON_ID
, SUA.COURSE_CD
, SUA.UNIT_CD
, SUA.VERSION_NUMBER
, SUA.CAL_TYPE
, SUA.CI_SEQUENCE_NUMBER
, SUA.UNIT_ATTEMPT_STATUS )
, ''
, ''
, ''
, DR.BATCH_ID
, SUA.PERSON_ID
, HP.PARTY_NUMBER
, HP.PARTY_NUMBER
, TO_NUMBER ('')
, SUA.UNIT_CD
, 'SUA'
FROM IGS_EN_STDNT_PS_ATT SPA
, IGS_PS_VER_ALL CRV
, IGS_CA_TEACH_TO_LOAD_V TTL
, IGS_EN_SU_ATTEMPT_ALL SUA
, IGS_PS_UNIT_VER UV
, IGS_PE_HZ_PARTIES IHP
, HZ_PARTIES HP
, IGS_DA_RQST DR
WHERE SUA.PERSON_ID = SPA.PERSON_ID
AND SUA.COURSE_CD = SPA.COURSE_CD
AND ( EXISTS ( SELECT 'X'
FROM IGS_DA_REQ_FTRS DRF1
WHERE DRF1.BATCH_ID = DR.BATCH_ID
AND DRF1.FEATURE_CODE = 'EIP'
AND SUA.UNIT_ATTEMPT_STATUS IN (DECODE ( NVL (DRF1.FEATURE_VALUE
, 'N')
, 'Y'
, ''
, 'N'
, 'ENROLLED' )
, 'COMPLETED'
, 'DISCONTIN'
, 'DUPLICATE' )
AND DRF1.FEATURE_VALUE IS NOT NULL) OR ( SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED'
, 'DISCONTIN'
, 'DUPLICATE')
AND NOT EXISTS ( SELECT 'X'
FROM IGS_DA_REQ_FTRS DRF1
WHERE DRF1.BATCH_ID = DR.BATCH_ID
AND DRF1.FEATURE_CODE = 'EIP') ) )
AND UV.UNIT_CD = SUA.UNIT_CD
AND UV.VERSION_NUMBER = SUA.VERSION_NUMBER
AND SPA.COURSE_CD = CRV.COURSE_CD
AND SPA.VERSION_NUMBER = CRV.VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED'
, 'INACTIVE'
, 'INTERMIT')
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' ) )
AND IHP.OI_LOCAL_INSTITUTION_IND = 'Y'
AND HP.PARTY_ID = IHP.PARTY_ID
AND IHP.INST_ORG_IND = 'I' UNION SELECT DISTINCT IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (ASU.UNIT_CD
, 'A')
, IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (ASU.UNIT_CD
, 'N')
, URV.TITLE
, CI.ALTERNATE_CODE
, ASU.ACHIEVABLE_CREDIT_POINTS
, ASU.ACHIEVABLE_CREDIT_POINTS
, DECODE (GSG.GPA_VAL
, TO_NUMBER ('')
, TO_NUMBER ('')
, ASU.ACHIEVABLE_CREDIT_POINTS)
, (NVL (ASU.ACHIEVABLE_CREDIT_POINTS
, 0) * NVL (GSG.GPA_VAL
, 0) )
, ASU.GRADE
, GSG.S_RESULT_TYPE
, 'FALSE'
, ''
, ''
, ''
, DR.BATCH_ID
, ASU.PERSON_ID
, OI.INSTITUTION_CD
, OI.INSTITUTION_CD
, ASU.AV_STND_UNIT_ID
, ASU.UNIT_CD
, 'AV'
FROM IGS_AV_STND_UNIT_ALL ASU
, IGS_EN_STDNT_PS_ATT SPA
, IGS_PS_VER_ALL CRV
, IGS_PS_UNIT_VER_ALL URV
, IGS_DA_RQST DR
, IGS_OR_INSTITUTION_V OI
, IGS_CA_INST_ALL CI
, IGS_AS_GRD_SCH_GRADE GSG
WHERE ASU.CAL_TYPE = CI.CAL_TYPE
AND ASU.CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
AND ASU.S_ADV_STND_GRANTING_STATUS = 'GRANTED'
AND SPA.PERSON_ID = ASU.PERSON_ID
AND SPA.COURSE_CD = ASU.AS_COURSE_CD
AND SPA.VERSION_NUMBER = ASU.AS_VERSION_NUMBER
AND SPA.COURSE_ATTEMPT_STATUS IN ('ENROLLED'
, 'COMPLETED'
, 'INACTIVE'
, 'INTERMIT')
AND ASU.GRADING_SCHEMA_CD = GSG.GRADING_SCHEMA_CD(+)
AND ASU.GRD_SCH_VERSION_NUMBER = GSG.VERSION_NUMBER(+)
AND ASU.GRADE = GSG.GRADE(+)
AND ASU.AS_COURSE_CD = CRV.COURSE_CD
AND ASU.AS_VERSION_NUMBER = CRV.VERSION_NUMBER
AND ASU.UNIT_CD = URV.UNIT_CD
AND ASU.VERSION_NUMBER = URV.VERSION_NUMBER
AND NVL(FND_PROFILE.VALUE('IGS_AV_STND_SOURCE')
, 'X') <> 'EXTERNAL' UNION SELECT DISTINCT IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (ADTU.UNIT
, 'A')
, IGS_DA_XML_PKG.GET_COURSE_ABBR_NUM (ADTU.UNIT
, 'N')
, ADTU.UNIT_NAME
, ADTD.TERM
, ADTU.CP_ATTEMPTED
, ADTU.CP_EARNED
, DECODE (ADTU.CP_EARNED
, TO_NUMBER ('')
, TO_NUMBER ('')
, ADTU.CP_EARNED )
, ADTU.UNIT_GRADE_POINTS
, ADTU.GRADE
, ''
, 'FALSE'
, ''
, ''
, ''
, DR.BATCH_ID
, HE.PARTY_ID
, P.PARTY_NUMBER
, OI.INSTITUTION_CD
, TO_NUMBER ('')
, ADTU.UNIT
, 'ED'
FROM HZ_EDUCATION HE
, IGS_AD_TRANSCRIPT ADT
, IGS_AD_TERM_DETAILS ADTD
, IGS_AD_TERM_UNITDTLS ADTU
, IGS_DA_RQST DR
, IGS_OR_INSTITUTION_V OI
, IGS_DA_CNFG_REQ_TYP CFG
, HZ_PARTIES P
WHERE HE.EDUCATION_ID = ADT.EDUCATION_ID
AND ADT.TRANSCRIPT_ID = ADTD.TRANSCRIPT_ID
AND ADTD.TERM_DETAILS_ID = ADTU.TERM_DETAILS_ID
AND DR.REQUEST_TYPE_ID = CFG.REQUEST_TYPE_ID
AND CFG.REQUEST_TYPE = 'TE'
AND NVL (FND_PROFILE.VALUE ('IGS_AV_STND_SOURCE')
, 'X') <> 'EXTERNAL'
AND P.PARTY_ID = HE.SCHOOL_PARTY_ID

Columns