[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- This modules validates the insert/update/delete of
-- IGS_AD_PS_APLINSTUNT.
DECLARE
v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
SELECT aas.s_adm_appl_status
FROM IGS_AD_APPL aa,
IGS_AD_APPL_STAT aas
WHERE
aa.person_id = p_person_id AND
aa.admission_appl_number= p_admission_appl_number AND
aa.adm_appl_status = aas.adm_appl_status;
SELECT aos.s_adm_outcome_status
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.sequence_number = p_acai_sequence_number AND
acai.adm_outcome_status = aos.adm_outcome_status;
-- Admission course application units should not be inserted/updated/deleted
p_message_name := 'IGS_AD_NO_IUD_ADMPRC_CAT';
SELECT DISTINCT uoo.cal_type,
uoo.ci_sequence_number
FROM IGS_PS_UNIT_OFR_OPT uoo,
IGS_CA_INST_REL cir
WHERE uoo.unit_cd = cp_unit_cd AND
uoo.version_number = cp_uv_version_number AND
uoo.offered_ind = cst_yes AND
cir.sub_cal_type = uoo.cal_type AND
cir.sub_ci_sequence_number = uoo.ci_sequence_number AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_ci_sequence_number;
SELECT 'x'
FROM IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = cp_adm_cal_type AND
cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
cir.sup_cal_type = cp_sup_cal_type AND
cir.sup_ci_sequence_number = cp_sup_ci_sequence_number;
-- Select valid teaching periods for the admission course application
CURSOR c_cir IS
SELECT cir.sub_cal_type,
cir.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat1,
IGS_CA_TYPE cat2
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
cat1.s_cal_cat = 'TEACHING' AND
cat2.s_cal_cat = 'ACADEMIC' AND
cir.sub_cal_type = cat1.cal_type AND
cir.sup_cal_type = cat2.cal_type;
SELECT uoo.offered_ind,
uoo.unit_class
FROM IGS_PS_UNIT_OFR_OPT uoo
WHERE uoo.unit_cd = p_unit_cd AND
uoo.version_number = p_uv_version_number AND
uoo.cal_type = cp_cal_type AND
uoo.ci_sequence_number = cp_ci_sequence_number AND
(p_location_cd IS NULL OR
uoo.location_cd = p_location_cd) AND
(p_unit_class IS NULL OR
uoo.unit_class = p_unit_class);
SELECT aos.s_adm_outcome_status
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.adm_outcome_status = aos.adm_outcome_status AND
acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
acai.nominated_course_cd = p_nominated_course_cd AND
acai.sequence_number = p_acai_sequence_number;
SELECT cs.s_cal_status,
cat.s_cal_cat
FROM IGS_CA_INST ci,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE ci.cal_status = cs.cal_status AND
ci.cal_type = cat.cal_type AND
ci.cal_type = p_teach_cal_type AND
ci.sequence_number = p_teach_ci_sequence_number;
SELECT 'x'
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sup_cal_type = cat.cal_type AND
cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
cir.sub_cal_type = p_teach_cal_type AND
cir.sub_ci_sequence_number = p_teach_ci_sequence_number AND
cat.s_cal_cat = cst_academic;
SELECT 'x'
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cat.cal_type AND
cir.sup_cal_type = p_teach_cal_type AND
cir.sup_ci_sequence_number = p_teach_ci_sequence_number AND
cir.sub_cal_type = p_adm_cal_type AND
cir.sub_ci_sequence_number = p_adm_ci_sequence_number AND
cat.s_cal_cat = cst_admission;
SELECT 'x'
FROM IGS_AS_UNIT_CLASS uc
WHERE uc.unit_mode = p_unit_mode AND
uc.unit_class = p_unit_class;
SELECT count(*)
FROM IGS_AD_PS_APLINSTUNT acaiu
WHERE acaiu.person_id = p_person_id AND
acaiu.admission_appl_number = p_admission_appl_number AND
acaiu.nominated_course_cd = p_nominated_course_cd AND
acaiu.acai_sequence_number = p_acai_sequence_number AND
acaiu.unit_cd <> p_unit_cd AND
acaiu.uv_version_number <> p_uv_version_number AND
NVL(acaiu.cal_type,'*-1') <> NVL(p_cal_type,'*-2') AND
NVL(acaiu.ci_sequence_number,-1) <> NVL(p_ci_sequence_number,-2) AND
NVL(acaiu.location_cd,'*-1') <> NVL(p_location_cd,'*-2') AND
NVL(acaiu.unit_class,'*-1') <> NVL(p_unit_class,'*-2');
SELECT 'x'
FROM IGS_AD_UNIT_OU_STAT auos
WHERE adm_unit_outcome_status = p_adm_unit_outcome_status AND
closed_ind = 'Y';
SELECT cat.s_cal_cat
FROM IGS_CA_INST ci,
IGS_CA_TYPE cat
WHERE ci.alternate_code = p_alternate_code AND
ci.cal_type = cat.cal_type;
SELECT ci.cal_type,
ci.sequence_number,
cs.s_cal_status
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.alternate_code = p_alternate_code AND
cat.cal_type = ci.cal_type AND
cs.cal_status = ci.cal_status AND
cat.s_cal_cat = cst_teaching AND
ci.cal_status = cs.cal_status;
SELECT 'x'
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = p_adm_cal_type AND
cir.sub_ci_sequence_number = p_adm_ci_sequence_number AND
cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_sequence_number;
SELECT 'x'
FROM IGS_PS_UNIT_OFR_OPT uoo
WHERE uoo.unit_cd = p_unit_cd AND
uoo.version_number = p_uv_version_number AND
uoo.cal_type = cp_cal_type AND
uoo.ci_sequence_number = cp_sequence_number AND
uoo.offered_ind = 'Y';
SELECT uv.award_course_only_ind,
us.s_unit_status
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_uv_version_number AND
us.unit_status = uv.unit_status;
SELECT 'X'
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_uv_version_number AND
uv.research_unit_ind = 'Y';
SELECT 'X'
FROM IGS_RE_CANDIDATURE can
WHERE can.person_id = p_person_id AND
((v_admission_appl_number IS NOT NULL AND
can.acai_admission_appl_number = v_admission_appl_number AND
v_nominated_course_cd IS NOT NULL AND
can.acai_nominated_course_cd = v_nominated_course_cd AND
v_acai_sequence_number IS NOT NULL AND
can.acai_sequence_number = v_acai_sequence_number) OR
(p_course_cd IS NOT NULL AND
can.sca_course_cd = p_course_cd));
SELECT uv.expiry_dt
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_uv_version_number AND
uv.expiry_dt IS NOT NULL;
SELECT ci.start_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_teach_cal_type AND
ci.sequence_number = p_teach_ci_sequence_number;