The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
FROM igs_ps_ver_ru
WHERE course_cd = v_course_cd
AND version_number = v_version_number
AND s_rule_call_cd = p_s_rule_call_cd;
SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
FROM igs_ps_stage_ru
WHERE course_cd = v_course_cd
AND version_number = v_version_number
AND cst_sequence_number = p_cst_sequence_number
AND s_rule_call_cd = p_s_rule_call_cd;
SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
FROM igs_en_unit_set_rule
WHERE unit_set_cd = p_unit_set_cd
AND version_number = p_us_version_number
AND s_rule_call_cd = p_s_rule_call_cd;
SELECT pro.override_appeal_ind
FROM igs_pr_rule_out_v pro
WHERE pro.progression_rule_cat = p_progression_rule_cat
AND pro.pra_sequence_number = p_pra_sequence_number
AND pro.sequence_number = p_sequence_number;
SELECT pro.override_show_cause_ind
FROM igs_pr_rule_out_v pro
WHERE pro.progression_rule_cat = p_progression_rule_cat
AND pro.pra_sequence_number = p_pra_sequence_number
AND pro.sequence_number = p_sequence_number;
SELECT DISTINCT spc.prg_cal_type,
spc.prg_ci_sequence_number,
ci1.start_dt
FROM igs_pr_stdnt_pr_ck spc,
igs_ca_inst ci1
WHERE spc.person_id = p_person_id
AND spc.course_cd = p_course_cd
AND igs_pr_gen_001.prgp_get_cal_stream (
p_course_cd,
p_version_number,
p_prg_cal_type,
spc.prg_cal_type
) = 'Y'
AND ci1.cal_type = spc.prg_cal_type
AND ci1.sequence_number = spc.prg_ci_sequence_number
AND ci1.start_dt <= (SELECT ci2.start_dt
FROM igs_ca_inst ci2
WHERE ci2.cal_type = p_prg_cal_type
AND ci2.sequence_number = p_prg_sequence_number)
ORDER BY ci1.start_dt DESC;
SELECT sprc.passed_ind
FROM igs_pr_sdt_pr_ru_ck sprc
WHERE sprc.person_id = p_person_id
AND sprc.course_cd = p_course_cd
AND sprc.prg_cal_type = cp_prg_cal_type
AND sprc.prg_ci_sequence_number = cp_prg_ci_sequence_number
AND sprc.progression_rule_cat = p_progression_rule_cat
AND sprc.pra_sequence_number = p_pra_sequence_number
ORDER BY sprc.rule_check_dt DESC;
SELECT igs_ca_gen_001.calp_get_alias_val (
dai.dt_alias,
dai.sequence_number,
dai.cal_type,
dai.ci_sequence_number
)
FROM igs_ca_da_inst dai
WHERE dai.cal_type = p_prg_cal_type
AND dai.ci_sequence_number = p_prg_sequence_number
AND dai.dt_alias = cp_dt_alias
ORDER BY 1;
SELECT NVL (
dai.absolute_val,
igs_ca_gen_001.calp_get_alias_val (
dai.dt_alias,
dai.sequence_number,
dai.cal_type,
dai.ci_sequence_number
)
)
FROM igs_ca_da_inst dai,
igs_pr_s_prg_conf spc
WHERE dai.cal_type = p_prg_cal_type
AND dai.ci_sequence_number = p_prg_sequence_number
AND dai.dt_alias = spc.encumb_end_dt_alias
ORDER BY 1 DESC;
SELECT ci.end_dt
FROM igs_ca_inst ci
WHERE ci.cal_type = p_prg_cal_type
AND ci.sequence_number = p_prg_sequence_number;
SELECT spo.prg_cal_type,
spo.prg_ci_sequence_number
FROM igs_pr_stdnt_pr_ou spo,
igs_ca_inst ci,
igs_pr_ou_type pot
WHERE spo.person_id = p_person_id
AND spo.course_cd = p_course_cd
AND spo.decision_status IN (cst_approved, cst_pending)
AND ci.cal_type = spo.prg_cal_type
AND ci.sequence_number = spo.prg_ci_sequence_number
AND ((p_prg_cal_type IS NOT NULL
AND p_prg_ci_sequence_number IS NOT NULL
AND p_prg_cal_type = spo.prg_cal_type
AND p_prg_ci_sequence_number = spo.prg_ci_sequence_number
)
OR (p_prg_cal_type IS NULL
OR p_prg_ci_sequence_number IS NULL
)
)
AND spo.progression_outcome_type = pot.progression_outcome_type
AND pot.positive_outcome_ind = 'N'
ORDER BY ci.start_dt DESC;
SELECT spo.course_cd,
spo.sequence_number,
spo.progression_outcome_type,
spo.decision_status,
spo.show_cause_expiry_dt,
spo.show_cause_dt,
spo.show_cause_outcome_dt,
spo.encmb_course_group_cd
FROM igs_pr_stdnt_pr_ou spo
WHERE spo.person_id = p_person_id
AND spo.course_cd = p_course_cd
AND spo.decision_status IN (cst_approved, cst_pending)
AND spo.prg_cal_type = cp_latest_cal_type
AND spo.prg_ci_sequence_number = cp_latest_sequence_number
AND igs_pr_gen_006.igs_pr_get_spo_expiry (
spo.person_id,
spo.course_cd,
spo.sequence_number,
spo.expiry_dt) <> 'EXPIRED';
SELECT 'X'
FROM igs_pr_stdnt_pr_ps spc
WHERE spc.person_id = p_person_id
AND spc.spo_course_cd = pl_spo_course_cd
AND spc.spo_sequence_number = pl_spo_sequence_number
AND spc.course_cd = p_course_cd;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_party_id;
SELECT 'X'
FROM igs_ps_grp_mbr cgm
WHERE cgm.course_cd = p_course_cd
AND cgm.version_number = p_version_number
AND course_group_cd = pl_encmb_course_group_cd;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_party_id;
SELECT pot.s_progression_outcome_type,
pot.encumbrance_type
FROM igs_pr_ou_type pot
WHERE pot.progression_outcome_type = pl_progression_outcome_type;
SELECT 'X'
FROM igs_fi_enc_dflt_eft etde
WHERE encumbrance_type = cp_encumbrance_type
AND s_encmb_effect_type IN (cst_exc_course, cst_exc_crs_gp);
SELECT 'X'
FROM igs_pr_stdnt_pr_ou spo
WHERE spo.person_id = p_person_id
AND spo.course_cd = p_course_cd
AND spo.decision_status = cst_approved
AND spo.appeal_dt IS NOT NULL
AND spo.appeal_outcome_dt IS NULL;
SELECT DISTINCT sprc.prg_cal_type,
sprc.prg_ci_sequence_number
FROM igs_pr_sdt_pr_ru_ck sprc,
igs_ca_inst ci1
WHERE sprc.person_id = p_person_id
AND sprc.course_cd = p_course_cd
AND sprc.progression_rule_cat = p_progression_rule_cat
AND sprc.pra_sequence_number = p_pra_sequence_number
AND sprc.prg_cal_type = ci1.cal_type
AND sprc.prg_ci_sequence_number = ci1.sequence_number
AND ci1.start_dt <= --gjha Changed to <= from <
(SELECT ci2.start_dt
FROM igs_ca_inst ci2
WHERE ci2.cal_type = p_prg_cal_type
AND ci2.sequence_number = p_prg_ci_sequence_number);
SELECT ci1.cal_type,
ci1.sequence_number
FROM igs_ca_inst ci1,
igs_ca_type ct,
igs_ca_stat cs
WHERE ct.cal_type = ci1.cal_type
AND ct.s_cal_cat = cst_progress
AND cs.cal_status = ci1.cal_status
AND cs.s_cal_status = cst_active
AND ci1.start_dt <= (SELECT ci2.start_dt
FROM igs_ca_inst ci2
WHERE ci2.cal_type = p_prg_cal_type
AND ci2.sequence_number = p_prg_ci_sequence_number)
AND (-- Logic from CALP_GET_CAL_STREAM.
EXISTS ( SELECT 'x'
FROM igs_pr_s_prg_cal spc1,
igs_pr_s_prg_cal spc2
WHERE spc1.s_control_num = 1
AND spc2.s_control_num = 1
AND spc1.prg_cal_type = p_prg_cal_type
AND spc2.prg_cal_type = ci1.cal_type
AND spc1.stream_num = spc2.stream_num)
OR EXISTS ( SELECT 'x'
FROM igs_pr_s_ou_prg_cal sopc1,
igs_pr_s_ou_prg_cal sopc2
WHERE igs_pr_gen_001.prgp_get_crv_cmt (
p_course_cd,
p_course_version_number,
sopc1.org_unit_cd,
sopc1.ou_start_dt
) = 'Y'
AND sopc1.prg_cal_type = p_prg_cal_type
AND sopc2.org_unit_cd = sopc1.org_unit_cd
AND sopc2.ou_start_dt = sopc1.ou_start_dt
AND sopc2.prg_cal_type = ci1.cal_type
AND sopc1.stream_num = sopc2.stream_num)
OR EXISTS ( SELECT 'x'
FROM igs_pr_s_crv_prg_cal scpc1,
igs_pr_s_crv_prg_cal scpc2
WHERE scpc1.course_cd = p_course_cd
AND scpc1.version_number = p_course_version_number
AND scpc1.prg_cal_type = p_prg_cal_type
AND scpc2.course_cd = scpc1.course_cd
AND scpc2.version_number = scpc1.version_number
AND scpc2.prg_cal_type = ci1.cal_type
AND scpc1.stream_num = scpc2.stream_num)
)
AND EXISTS ( -- Units must exist within the progression calendar.
SELECT 'X'
FROM igs_en_su_attempt sua,
igs_ca_inst_rel cir
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND cir.sup_cal_type = ci1.cal_type
AND cir.sup_ci_sequence_number = ci1.sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND sua.unit_attempt_status IN (cst_enrolled, cst_discontin, cst_completed));
SELECT crv.course_type,
cow.org_unit_cd,
cow.ou_start_dt
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv,
igs_ps_own cow
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd
AND (sca.version_number = p_version_number
OR p_version_number IS NULL
)
AND crv.course_cd = sca.course_cd
AND crv.version_number = sca.version_number
AND crv.course_cd = cow.course_cd
AND crv.version_number = cow.version_number;
SELECT 'X'
FROM igs_or_unit_rel our
WHERE our.parent_org_unit_cd = p_org_unit_cd
AND our.parent_start_dt = p_ou_start_dt
AND our.child_org_unit_cd = cp_cow_org_unit_cd
AND our.child_start_dt = cp_cow_ou_start_dt
AND our.logical_delete_dt IS NULL
AND EXISTS ( SELECT 'X'
FROM igs_or_rel_ps_type ourct
WHERE our.parent_org_unit_cd = ourct.parent_org_unit_cd
AND our.parent_start_dt = ourct.parent_start_dt
AND our.child_org_unit_cd = ourct.child_org_unit_cd
AND our.child_start_dt = ourct.child_start_dt
AND our.create_dt = ourct.our_create_dt
AND ourct.course_type = cp_course_type);
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.discontinued_dt,
sua.uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst_rel cir
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status IN (cst_enrolled, cst_discontin, cst_completed)
AND cir.sup_cal_type = p_prg_cal_type
AND cir.sup_ci_sequence_number = p_prg_sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND (sua.administrative_unit_status IS NULL
OR sua.administrative_unit_status IN (SELECT aus.administrative_unit_status
FROM igs_ad_adm_unit_stat aus
WHERE aus.effective_progression_ind = 'Y')
)
ORDER BY DECODE (sua.unit_attempt_status, cst_enrolled, 1, cst_discontin, 2, cst_completed, 3);
SELECT scpm.VALUE
FROM igs_pr_sdt_ps_pr_msr scpm
WHERE scpm.person_id = p_person_id
AND scpm.course_cd = p_course_cd
AND scpm.prg_cal_type = p_prg_cal_type
AND scpm.prg_ci_sequence_number = p_prg_ci_sequence_number
AND scpm.s_prg_measure_type = p_s_prg_measure_type
AND scpm.calculation_dt = (SELECT MAX (scpm2.calculation_dt)
FROM igs_pr_sdt_ps_pr_msr scpm2
WHERE scpm2.person_id = scpm.person_id
AND scpm2.course_cd = scpm.course_cd
AND scpm2.prg_cal_type = scpm.prg_cal_type
AND scpm2.prg_ci_sequence_number = scpm.prg_ci_sequence_number
AND scpm2.s_prg_measure_type = scpm.s_prg_measure_type);