VIEW: APPS.IGS_DA_XML_COURSEINSTITUTION_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'