VIEW: APPS.IGS_DA_XML_COURSE_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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