The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT IGS_CA_STAT.s_cal_status
FROM IGS_CA_INST, IGS_CA_STAT
WHERE IGS_CA_INST.cal_type = cp_cal_type AND
IGS_CA_INST.sequence_number = cp_ci_sequence_number AND
IGS_CA_INST.cal_status = IGS_CA_STAT.cal_status;
-- pattern for non-examinable items which have not been deleted.
-- This is similar to ASSP_VAL_UAI_UNIQREF except that:
-- * The routine validates non-examinable items as opposed
-- to examinable items
-- * Reference is optional
-- * Reference when set is unique within an assessment type and
-- only for items that have not been deleted
DECLARE
CURSOR c_uai IS
SELECT 'x'
FROM igs_ps_unitass_item uai,
IGS_AS_ASSESSMNT_ITM ai,
IGS_AS_ASSESSMNT_TYP atyp,
igs_ps_unit_ofr_opt uoo
WHERE atyp.examinable_ind = 'N' AND
atyp.ASSESSMENT_TYPE = p_assessment_type AND
atyp.ASSESSMENT_TYPE = ai.ASSESSMENT_TYPE AND
uai.ass_id = ai.ass_id AND
uoo.unit_cd = p_unit_cd AND
uoo.version_number = p_version_number AND
uoo.cal_type = p_cal_type AND
uoo.ci_sequence_number = p_ci_sequence_number AND
uai.ass_id <> p_ass_id AND
uai.sequence_number <> p_sequence_number AND
uai.reference = p_reference AND
uai.logical_delete_dt IS NULL and uai.uoo_id=uoo.uoo_id;
-- Select from the table taking care not to select
-- record passed in.
OPEN c_uai;
SELECT 'x'
FROM IGS_AS_ASSESSMNT_TYP atyp,
IGS_AS_ASSESSMNT_ITM ai,
igs_ps_unitass_item uai,
igs_ps_unit_ofr_opt uoo
WHERE atyp.examinable_ind = 'Y' AND
ai.assessment_type = atyp.assessment_type AND
uai.ass_id = ai.ass_id AND
uoo.unit_cd = p_unit_cd AND
uoo.version_number = p_version_number AND
uoo.cal_type = p_cal_type AND
uoo.ci_sequence_number = p_ci_sequence_number AND
uai.ass_id = p_ass_id AND
uai.sequence_number <> p_sequence_number AND
NVL(uai.reference, 'NULL') <> NVL(p_reference, 'NULL') and uai.uoo_id=uoo.uoo_id;