XML element CourseInstitutionTypeProvides the Unit"s Institution code based on the batch_id, person_id and local_institution_cd.
Implementation/DBA Data:
Not implemented in this database
View Text
SELECT DISTINCT DR.BATCH_ID , SUA.PERSON_ID , OI.PARTY_NUMBER , OI.PARTY_NUMBER FROM IGS_EN_STDNT_PS_ATT SPA , IGS_PS_VER_ALL CRV , IGS_EN_SU_ATTEMPT_ALL SUA , IGS_PS_UNIT_VER UV , IGS_OR_INST_ORG_BASE_V OI , IGS_DA_RQST DR WHERE OI.INST_ORG_IND = 'I' AND 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' ) AND DRF1.FEATURE_VALUE IS NOT NULL) OR (SUA.UNIT_ATTEMPT_STATUS IN ('ENROLLED' , 'COMPLETED' , 'DISCONTIN') 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 ((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' ) ) UNION SELECT DISTINCT DR.BATCH_ID , ASU.PERSON_ID , OI.PARTY_NUMBER , OI.PARTY_NUMBER LOCAL_INSTITUTION_CD FROM IGS_AV_STND_UNIT_ALL ASU , IGS_DA_RQST DR , IGS_OR_INST_ORG_BASE_V OI WHERE OI.INST_ORG_IND = 'I' UNION SELECT DISTINCT DR.BATCH_ID , HE.PARTY_ID , P.PARTY_NUMBER , OI.PARTY_NUMBER LOCAL_INSTITUTION_CD FROM HZ_EDUCATION HE , HZ_PARTIES P , IGS_AD_TRANSCRIPT ADT , IGS_AD_TERM_DETAILS ADTD , IGS_AD_TERM_UNITDTLS ADTU , IGS_AV_STND_UNIT_ALL ASU , IGS_DA_RQST DR , IGS_OR_INST_ORG_BASE_V OI , IGS_DA_CNFG_REQ_TYP CFG WHERE HE.EDUCATION_ID = ADT.EDUCATION_ID AND ADT.TRANSCRIPT_ID = ADTD.TRANSCRIPT_ID AND ADTD.TERM_DETAILS_ID = ADTU.TERM_DETAILS_ID AND ADTU.UNIT_DETAILS_ID = ASU.UNIT_DETAILS_ID AND HE.PARTY_ID = ASU.PERSON_ID AND P.PARTY_ID = HE.SCHOOL_PARTY_ID AND OI.INST_ORG_IND = 'I' 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'
[Home] [Help]