The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT s_admission_process_type
FROM IGS_AD_PRD_AD_PRC_CA
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat;
SELECT acov.location_cd,
acov.attendance_mode,
acov.attendance_type
FROM IGS_PS_OFR_PAT_APCOOD_V acov
WHERE acov.adm_cal_type = p_adm_cal_type AND
acov.adm_ci_sequence_number = p_adm_ci_sequence_number AND
acov.admission_cat = p_admission_cat AND
acov.s_admission_process_type = cp_s_admission_process_type AND
(p_course_cd IS NULL OR
(acov.course_cd = p_course_cd AND
acov.version_number = p_version_number AND
acov.acad_cal_type = p_acad_cal_type)) AND
(p_location_cd IS NULL OR
acov.location_cd = p_location_cd) AND
(p_attendance_mode IS NULL OR
acov.attendance_mode = p_attendance_mode) AND
(p_attendance_type IS NULL OR
acov.attendance_type = p_attendance_type);
-- Select s_admission_process_type from dbase
FOR v_apapc_rec IN c_apapc LOOP
v_apapc_found := TRUE;
SELECT cop.course_cd,
cop.version_number,
cop.cal_type,
cop.location_cd,
cop.attendance_mode,
cop.attendance_type
FROM IGS_PS_OFR_PAT cop,
IGS_CA_INST_REL cir
WHERE cir.sup_cal_type = cp_acad_cal_type AND
cir.sub_cal_type = cp_adm_cal_type AND
cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
cop.course_cd = cp_course_cd AND
cop.version_number = cp_version_number AND
cop.offered_ind = 'Y' AND
cop.entry_point_ind = 'Y' AND
cop.cal_type = cir.sup_cal_type AND
cop.ci_sequence_number = cir.sup_ci_sequence_number;
SELECT apapc.s_admission_process_type
FROM IGS_AD_PRD_AD_PRC_CA apapc
WHERE apapc.adm_cal_type = cp_adm_cal_type AND
apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
apapc.admission_cat = cp_admission_cat AND
apapc.closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
dt_alias = p_dt_alias AND
(dai_sequence_number <> p_dai_sequence_number OR
sequence_number <> p_apcood_sequence_number);
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
dt_alias = p_dt_alias AND
(sequence_number <> p_apcood_sequence_number) AND
NVL(s_admission_process_type, 'NULL')
= NVL(p_s_admission_process_type, 'NULL') AND
NVL(course_cd, 'NULL') = NVL(p_course_cd, 'NULL') AND
NVL(version_number, 0) = NVL(p_version_number, 0) AND
NVL(acad_cal_type, 'NULL') = NVL(p_acad_cal_type, 'NULL') AND
NVL(location_cd, 'NULL') = NVL(p_location_cd, 'NULL') AND
NVL(attendance_mode, 'NULL') = NVL(p_attendance_mode, 'NULL') AND
NVL(attendance_type, 'NULL') = NVL(p_attendance_type, 'NULL');
SELECT s_admission_process_type,
course_cd,
version_number,
acad_cal_type,
location_cd,
attendance_mode,
attendance_type
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
dt_alias = p_dt_alias AND
(dai_sequence_number <> p_dai_sequence_number OR
sequence_number <> p_apcood_sequence_number) AND
DECODE(cp_field_name,
's_admission_process_type', S_ADMISSION_PROCESS_TYPE,
'course_cd', COURSE_CD,
'location_cd', LOCATION_CD,
'attendance_type', attendance_type,
'IGS_EN_ATD_MODE', attendance_mode, NULL) IS NULL;
-- Record can be inserted, it is the first
CLOSE c_apcood;
-- can be inserted, updated or deleted.
-- Second level conflict
-- A second level conflict is when a first level conflict is encountered
-- AND a record does NOT already exist to compensate for the first level
-- conflict. This is determined by combining the specified components of
-- the record being validated AND the record found with a first level
-- conflict, AND checking for the existence of a record with this combination.
DECLARE
v_s_admission_process_type
IGS_AD_PECRS_OFOP_DT.s_admission_process_type%TYPE;
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
dt_alias = p_dt_alias AND
(dai_sequence_number <> p_dai_sequence_number OR
sequence_number <> p_apcood_sequence_number) AND
acad_cal_type = p_acad_cal_type AND
(v_s_admission_process_type IS NULL OR
s_admission_process_type = v_s_admission_process_type) AND
(v_course_cd IS NULL OR
course_cd = v_course_cd) AND
(v_version_number IS NULL OR
version_number = v_version_number) AND
(v_location_cd IS NULL OR
location_cd = v_location_cd) AND
(v_attendance_mode IS NULL OR
attendance_mode = v_attendance_mode) AND
(v_attendance_type IS NULL OR
attendance_type = v_attendance_type);
--This module validates the insert of admission
--period course offering option date.
DECLARE
v_apapc_exists VARCHAR2(1);
SELECT 'X'
FROM IGS_AD_PRD_AD_PRC_CA
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
SELECT sequence_number
FROM IGS_CA_DA_INST
WHERE cal_type = p_adm_cal_type AND
ci_sequence_number = p_adm_ci_sequence_number AND
dt_alias = p_dt_alias;
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
dt_alias = p_dt_alias AND
dai_sequence_number = cp_sequence_number AND
(sequence_number <> p_apcood_sequence_number);
--cannot be inserted if no IGS_AD_PRD_AD_PRC_CA exists
OPEN c_apapc;
-- be inserted if this will NOT leave a date alias instance for
-- the date alias with no overrides attached.
FOR v_dai_rec IN c_dai LOOP
IF v_dai_rec.sequence_number <> NVL(p_dai_sequence_number, 0) THEN
OPEN c_apcood(
v_dai_rec.sequence_number);
SELECT adm_appl_offer_resp_dt_alias,
adm_appl_due_dt_alias,
adm_appl_final_dt_alias
FROM IGS_AD_CAL_CONF
WHERE s_control_num = 1;
-- The admission period date restrictions table only applies to selected dates
-- as defined in the system admission calendar configuration table.
OPEN c_sacc;