The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ostr1.include_or_exclude
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd;
SELECT 'X'
FROM igs_av_unt_ref_cds urc,
igs_ge_ref_cd refcd,
igs_ge_ref_cd_type rct
WHERE urc.av_stnd_unit_id = p_av_stnd_unit_id
AND urc.reference_code_id = refcd.reference_code_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND urc.deleted_date IS NULL
AND EXISTS ( SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = refcd.reference_cd
AND ostr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_av_unt_ref_cds urc,
igs_ge_ref_cd refcd,
igs_ge_ref_cd_type rct
WHERE urc.av_stnd_unit_id = p_av_stnd_unit_id
AND urc.reference_code_id = refcd.reference_code_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND urc.deleted_date IS NULL
AND EXISTS ( SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = refcd.reference_cd
AND ostr1.include_or_exclude = 'EXCLUDE');
SELECT INSTR.include_or_exclude
FROM igs_pr_inst_sta_ref INSTR
WHERE INSTR.stat_type = p_stat_type;
SELECT 'X'
FROM igs_av_unt_ref_cds urc,
igs_ge_ref_cd refcd,
igs_ge_ref_cd_type rct
WHERE urc.av_stnd_unit_id = p_av_stnd_unit_id
AND urc.reference_code_id = refcd.reference_code_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND urc.deleted_date IS NULL
AND EXISTS ( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = refcd.reference_cd
AND instr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_av_unt_ref_cds urc,
igs_ge_ref_cd refcd,
igs_ge_ref_cd_type rct
WHERE urc.av_stnd_unit_id = p_av_stnd_unit_id
AND urc.reference_code_id = refcd.reference_code_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND urc.deleted_date IS NULL
AND EXISTS ( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = refcd.reference_cd
AND instr1.include_or_exclude = 'EXCLUDE');
SELECT orst.org_unit_cd,
st.stat_type,
st.derivation,
orst.include_standard_ind,
orst.include_local_ind,
orst.include_other_ind
FROM igs_en_stdnt_ps_att spa,
igs_ps_ver crv,
igs_pr_stat_type st,
igs_pr_org_stat orst
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND spa.course_cd = crv.course_cd
AND spa.version_number = crv.version_number
AND st.stat_type = orst.stat_type
AND orst.org_unit_cd = crv.responsible_org_unit_cd
AND (orst.stat_type = p_stat_type
OR p_stat_type IS NULL
AND ((p_system_stat IS NULL AND orst.standard_ind = 'Y')
OR (p_system_stat = 'STANDARD' AND orst.standard_ind = 'Y'))
AND ((p_cumulative_ind = 'Y'
AND (orst.timeframe = 'CUMULATIVE'
OR orst.timeframe = 'BOTH'))
OR (p_cumulative_ind = 'N' AND orst.timeframe = 'PERIOD'
OR orst.timeframe = 'BOTH')));
SELECT st.stat_type,
st.derivation,
inst.include_standard_ind,
inst.include_local_ind,
inst.include_other_ind
FROM igs_pr_stat_type st, igs_pr_inst_stat inst
WHERE st.stat_type = inst.stat_type
AND (inst.stat_type = p_stat_type
OR p_stat_type IS NULL
AND ((p_system_stat IS NULL AND inst.standard_ind = 'Y')
OR (p_system_stat = 'STANDARD' AND inst.standard_ind =
'Y'))
AND ((p_cumulative_ind = 'Y'
AND (inst.timeframe = 'CUMULATIVE'
OR inst.timeframe = 'BOTH'))
OR (p_cumulative_ind = 'N' AND inst.timeframe = 'PERIOD'
OR inst.timeframe = 'BOTH'))); -- Bug Fix 2707516
SELECT ostr1.include_or_exclude
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type AND ostr1.org_unit_cd =
p_org_unit_cd;
SELECT 'X'
FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = p_unit_cd
AND urc.version_number = p_unit_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = urc.reference_cd
AND ostr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = p_unit_cd
AND urc.version_number = p_unit_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = urc.reference_cd
AND ostr1.include_or_exclude = 'EXCLUDE');
SELECT INSTR.include_or_exclude
FROM igs_pr_inst_sta_ref INSTR
WHERE INSTR.stat_type = p_stat_type;
SELECT 'X'
FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = p_unit_cd
AND urc.version_number = p_unit_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = urc.reference_cd
AND instr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = p_unit_cd
AND urc.version_number = p_unit_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = urc.reference_cd
AND instr1.include_or_exclude = 'EXCLUDE');
SELECT asu.unit_cd,
asu.version_number,
asu.achievable_credit_points,
asu.grading_schema_cd,
asu.grd_sch_version_number,
asu.grade,
asu.av_stnd_unit_id
FROM igs_av_stnd_unit asu, igs_or_inst_org_base_v inst, igs_ca_inst ci
WHERE asu.person_id = p_person_id
AND asu.as_course_cd = p_course_cd
AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
AND asu.s_adv_stnd_recognition_type = 'CREDIT'
AND NVL(asu.expiry_dt, SYSDATE + 1) > SYSDATE
AND asu.exemption_institution_cd = inst.party_number(+) --swaghmar change
AND ((p_include_local_ind = 'Y' AND inst.oi_local_institution_ind = 'Y')
OR (p_include_other_ind = 'Y' AND inst.oi_local_institution_ind =
'N'))
AND asu.cal_type = ci.cal_type
AND asu.ci_sequence_number = ci.sequence_number
AND inst.inst_org_ind = 'I'
AND ((p_cumulative_ind = 'N'
AND p_load_cal_type = asu.cal_type
AND p_load_ci_sequence_number = asu.ci_sequence_number)
OR (p_cumulative_ind = 'Y'
AND 0 < (SELECT COUNT(*)
FROM igs_ca_inst ci2
WHERE p_load_cal_type = ci2.cal_type
AND p_load_ci_sequence_number =
ci2.sequence_number
AND ci.start_dt <= ci2.start_dt)));
SELECT s_result_type,
gpa_val
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND gsg.grade = cp_grade;
SELECT SUM(NVL(asul.credit_points, 0)) sumvalue
FROM igs_av_stnd_unit_lvl asul, igs_or_inst_org_base_v inst, igs_ca_inst ci
WHERE asul.person_id = p_person_id
AND asul.as_course_cd = p_course_cd
AND asul.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
AND NVL(asul.expiry_dt, SYSDATE + 1) > SYSDATE
AND asul.exemption_institution_cd = inst.ou_institution_cd(+)
AND ((p_include_local_ind = 'Y' AND inst.oi_local_institution_ind = 'Y')
OR (p_include_other_ind = 'Y' AND inst.oi_local_institution_ind =
'N'))
AND asul.cal_type = ci.cal_type
AND asul.ci_sequence_number = ci.sequence_number
AND inst.inst_org_ind = 'I'
AND ((p_cumulative_ind = 'N'
AND p_load_cal_type = asul.cal_type
AND p_load_ci_sequence_number = asul.ci_sequence_number)
OR (p_cumulative_ind = 'Y'
AND 0 < (SELECT COUNT(*)
FROM igs_ca_inst ci2
WHERE p_load_cal_type = ci2.cal_type
AND p_load_ci_sequence_number =
ci2.sequence_number
AND ci.start_dt <= ci2.start_dt)));
SELECT sua.unit_attempt_status,
NVL(sua.override_achievable_cp , sua.override_enrolled_cp ) sua_override_cp,
NVL(uc.achievable_credit_points, uc.enrolled_credit_points) uc_credit_points,
NVL(uv.achievable_credit_points, uv.enrolled_credit_points) uv_credit_points
FROM igs_en_stdnt_ps_att spa,
igs_ps_ver pv,
igs_en_su_attempt_ALL sua,
igs_ps_unit_ver uv,
igs_ps_usec_cps uc
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND sua.person_id = spa.person_id
AND sua.course_cd = spa.course_cd
AND sua.uoo_id = p_uoo_id
AND sua.unit_cd = p_unit_cd
AND sua.version_number = p_unit_version_number
AND sua.cal_type = p_teach_cal_type
AND sua.ci_sequence_number = p_teach_ci_sequence_number
AND sua.unit_attempt_status IN
('COMPLETED', 'DUPLICATE', 'ENROLLED', 'DISCONTIN')
AND uv.unit_cd = sua.unit_cd
AND uv.version_number = sua.version_number
AND sua.uoo_id = uc.uoo_id(+)
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 = pv.course_type
AND pur.student_career_transcript = 'N')
AND NVL (sua.student_career_transcript, 'X') <> 'N'
)
)
ORDER BY sua.unit_cd ASC, sua.ci_end_dt ASC;
SELECT gsg.gpa_val,
NVL (gsg.show_in_earned_crdt_ind, 'N') show_in_earned_crdt_ind
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND gsg.grade = cp_grade;
SELECT sua.person_id,sua.course_cd, sua.unit_cd, sua.version_number, sua.cal_type, sua.ci_sequence_number,
sua.uoo_id ,sua.unit_attempt_status
FROM igs_en_su_attempt_ALL sua, igs_ca_inst ci1
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status IN
('COMPLETED', 'DUPLICATE', 'ENROLLED', 'DISCONTIN')
AND ci1.cal_type = sua.cal_type
AND ci1.sequence_number = sua.ci_sequence_number
AND ( ( p_cumulative_ind = 'N'
AND EXISTS ( SELECT 'X'
FROM igs_ca_load_to_teach_v ltt1
WHERE p_load_cal_type = ltt1.load_cal_type
AND p_load_ci_sequence_number =
ltt1.load_ci_sequence_number
AND sua.cal_type = ltt1.teach_cal_type
AND sua.ci_sequence_number =
ltt1.teach_ci_sequence_number)
)
OR ( p_cumulative_ind = 'Y'
AND EXISTS ( SELECT 'X'
FROM igs_ca_inst ci2, igs_ca_load_to_teach_v ltt2
WHERE ci2.cal_type = p_load_cal_type
AND ci2.sequence_number =
p_load_ci_sequence_number
AND sua.cal_type = ltt2.teach_cal_type
AND sua.ci_sequence_number =
ltt2.teach_ci_sequence_number
AND ltt2.load_end_dt <= ci2.end_dt)
)
)
ORDER BY sua.unit_cd ASC, sua.ci_end_dt ASC;
SELECT sas.earned_credit_points,
sas.attempted_credit_points,
sas.gpa,
sas.gpa_credit_points,
sas.gpa_quality_points
FROM igs_pr_stu_acad_stat sas
WHERE sas.person_id = p_person_id
AND sas.course_cd = p_course_cd
AND sas.cal_type = p_load_cal_type
AND sas.ci_sequence_number = p_load_ci_sequence_number
AND sas.stat_type = cp_stat_type
AND ((sas.timeframe IN ('CUMULATIVE','BOTH') AND p_cumulative_ind = 'Y')
OR (sas.timeframe IN ('PERIOD','BOTH') AND p_cumulative_ind = 'N'));
SELECT ostr1.include_or_exclude
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type AND ostr1.org_unit_cd = p_org_unit_cd;
SELECT 'X'
FROM igs_as_sua_ref_cds urc,
igs_ge_ref_cd_type rct
WHERE urc.person_id = p_person_id
AND urc.uoo_id = p_uoo_id
AND urc.course_cd = p_course_cd
AND urc.reference_cd_type = rct.reference_cd_type
AND urc.deleted_date IS NULL
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS (
SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = urc.reference_cd
AND ostr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_as_sua_ref_cds urc, igs_ge_ref_cd_type rct
WHERE urc.person_id = p_person_id
AND urc.uoo_id = p_uoo_id
AND urc.course_cd = p_course_cd
AND urc.reference_cd_type = rct.reference_cd_type
AND urc.deleted_date IS NULL
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_org_stat_ref ostr1
WHERE ostr1.stat_type = p_stat_type
AND ostr1.org_unit_cd = p_org_unit_cd
AND ostr1.unit_ref_cd = urc.reference_cd
AND ostr1.include_or_exclude = 'EXCLUDE');
SELECT INSTR.include_or_exclude
FROM igs_pr_inst_sta_ref INSTR
WHERE INSTR.stat_type = p_stat_type;
SELECT 'X'
FROM igs_as_sua_ref_cds urc, igs_ge_ref_cd_type rct
WHERE urc.person_id = p_person_id
AND urc.uoo_id = p_uoo_id
AND urc.course_cd = p_course_cd
AND urc.reference_cd_type = rct.reference_cd_type
AND urc.deleted_date IS NULL
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = urc.reference_cd
AND instr1.include_or_exclude = 'INCLUDE');
SELECT 'X'
FROM igs_as_sua_ref_cds src, igs_ge_ref_cd_type rct
WHERE src.person_id = p_person_id
AND src.uoo_id = p_uoo_id
AND src.course_cd = p_course_cd
AND src.reference_cd_type = rct.reference_cd_type
AND src.deleted_date IS NULL
AND rct.s_reference_cd_type = 'STATS'
AND EXISTS( SELECT 'X'
FROM igs_pr_inst_sta_ref instr1
WHERE instr1.stat_type = p_stat_type
AND instr1.unit_ref_cd = src.reference_cd
AND instr1.include_or_exclude = 'EXCLUDE');