[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ci.alternate_code
FROM IGS_CA_INST ci
WHERE ci.cal_type = cp_acad_cal_type AND
ci.sequence_number = cp_acad_ci_sequence_number;
SELECT ci.cal_type,
ci.sequence_number,
ci.prior_ci_sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat
WHERE cat.cal_type = ci.cal_type AND
cat.s_cal_cat = cst_admission AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_ci_sequence_number AND
cir.sub_cal_type = ci.cal_type AND
cir.sub_ci_sequence_number = ci.sequence_number;
-- Insert log for exception recording
IGS_GE_GEN_003.GENP_INS_LOG(
v_s_log_type,
v_alternate_code,
v_creation_dt);
-- Routine to insert all details from the one Admission Period Admission
-- Category
-- to another. This process will be called by the Admission calendar rollover
-- process, and from the form for duplicating admission period admission
-- categories.
-- NOTE: This function will be called after the admission period admission
-- category
-- has been inserted. The database trigger associated with the admission period
-- admission category also in inserts adm_period_admission process categories.
DECLARE
cst_error CONSTANT VARCHAR2(1) := 'E';
v_apcoo_one_not_insert BOOLEAN DEFAULT FALSE;
v_apcoo_one_insert BOOLEAN DEFAULT FALSE;
SELECT IGS_AD_PRD_PS_OF_OPT_SEQ_NUM_S.NEXTVAL FROM DUAL;
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_new_adm_ci_sequence_number AND
admission_cat = p_new_admission_cat AND
closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
SELECT 'x'
FROM IGS_AD_PRD_AD_PRC_CA
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_old_adm_ci_sequence_number AND
admission_cat = p_old_admission_cat AND
s_admission_process_type = cp_s_adm_process_type AND
closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
SELECT 'x'
FROM IGS_AD_APPL
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
admission_cat = p_new_admission_cat AND
s_admission_process_type = cp_s_adm_process_type;
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
admission_cat = p_new_admission_cat AND
s_admission_process_type = cp_s_adm_process_type;
SELECT adm_cal_type,
adm_ci_sequence_number,
admission_cat,
s_admission_process_type,
course_cd,
version_number,
acad_cal_type,
sequence_number,
location_cd,
attendance_mode,
attendance_type,
rollover_inclusion_ind
FROM IGS_AD_PRD_PS_OF_OPT
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_old_adm_ci_sequence_number AND
admission_cat = p_old_admission_cat AND
s_admission_process_type = cp_s_admission_process_type;
SELECT 'x'
FROM IGS_PS_OFR_PAT cop,
IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = p_adm_cal_type AND
cir.sub_ci_sequence_number = p_new_adm_ci_sequence_number AND
cop.course_cd = cp_course_cd AND
cop.version_number = cp_version_number AND
cop.cal_type = cp_acad_cal_type AND
cop.offered_ind = 'Y' AND
cop.entry_point_ind = 'Y' AND
cir.sup_cal_type = cop.cal_type AND
cir.sup_ci_sequence_number = cop.ci_sequence_number;
SELECT rowid, APAPC.*
FROM IGS_AD_PRD_AD_PRC_CA APAPC
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
admission_cat = p_new_admission_cat AND
s_admission_process_type = p_s_admission_process_type;
PROCEDURE admpl_ins_insert_log(
p_adm_ci_sequence_number
IGS_AD_PRD_PS_OF_OPT.adm_ci_sequence_number%TYPE,
p_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE,
p_s_admission_process_type
IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE,
p_course_cd IGS_AD_PRD_PS_OF_OPT.course_cd%TYPE,
p_version_number IGS_AD_PRD_PS_OF_OPT.version_number%TYPE,
p_acad_cal_type IGS_AD_PRD_PS_OF_OPT.acad_cal_type%TYPE,
p_sequence_number IGS_AD_PRD_PS_OF_OPT.sequence_number%TYPE,
p_message_name VARCHAR2)
IS
BEGIN -- admpl_ins_insert_log
DECLARE
v_key IGS_GE_S_LOG.key%TYPE;
SELECT start_dt,
end_dt
FROM IGS_CA_INST
WHERE cal_type = p_adm_cal_type AND
sequence_number = p_new_adm_ci_sequence_number;
END admpl_ins_insert_log;
-- Select IGS_AD_PRD_AD_PRC_CA to duplicate IGS_PS_COURSE offering
-- option restrictions.
OPEN c_apapc;
-- For each record, remove apapc if no longer required or inserted via
-- the table trigger.
OPEN c_apapc_check(
v_apapc_rec.s_admission_process_type);
-- Delete this record, if no child records exist.
OPEN c_aa_child(
v_apapc_rec.s_admission_process_type);
IGS_AD_PRD_AD_PRC_CA_Pkg.Delete_Row (
Rec_IGS_AD_PRD_AD_PRC_CA.RowId
);
-- For each record, select the Admission Period COURSE Offering Option
-- Restrictions for the process type.
OPEN c_apcoo(
v_apapc_rec.s_admission_process_type);
-- one apcoo could not be inserted
v_apcoo_one_not_insert := TRUE;
-- Insert log **
admpl_ins_insert_log(
v_apcoo_rec.adm_ci_sequence_number,
v_apcoo_rec.admission_cat,
v_apcoo_rec.s_admission_process_type,
v_apcoo_rec.course_cd,
v_apcoo_rec.version_number,
v_apcoo_rec.acad_cal_type,
v_apcoo_rec.sequence_number,
v_message_name);
-- one apcoo could not be inserted.
v_apcoo_one_not_insert := TRUE;
--Insert log **
admpl_ins_insert_log(
v_apcoo_rec.adm_ci_sequence_number,
v_apcoo_rec.admission_cat,
v_apcoo_rec.s_admission_process_type,
v_apcoo_rec.course_cd,
v_apcoo_rec.version_number,
v_apcoo_rec.acad_cal_type,
v_apcoo_rec.sequence_number,
v_message_name);
-- apcoo could not be inserted.
v_apcoo_one_not_insert := TRUE;
-- Insert log **
admpl_ins_insert_log(
v_apcoo_rec.adm_ci_sequence_number,
v_apcoo_rec.admission_cat,
v_apcoo_rec.s_admission_process_type,
v_apcoo_rec.course_cd,
v_apcoo_rec.version_number,
v_apcoo_rec.acad_cal_type,
v_apcoo_rec.sequence_number,
v_message_name);
IGS_AD_PRD_PS_OF_OPT_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Adm_Cal_Type => p_adm_cal_type,
X_Adm_Ci_Sequence_Number => p_new_adm_ci_sequence_number,
X_Admission_Cat => p_new_admission_cat,
X_S_Admission_Process_Type => v_apcoo_rec.s_admission_process_type,
X_Course_Cd => v_apcoo_rec.course_cd,
X_Version_Number => v_apcoo_rec.version_number,
X_Acad_Cal_Type => v_apcoo_rec.acad_cal_type,
X_Sequence_Number => lv_nextval,
X_Location_Cd => v_apcoo_rec.location_cd,
X_Attendance_Mode => v_apcoo_rec.attendance_mode,
X_Attendance_Type => v_apcoo_rec.attendance_type,
X_Rollover_Inclusion_Ind => 'Y'
);
-- apcoo has been inserted
v_apcoo_one_insert := TRUE;
IF (v_apcoo_one_insert = FALSE AND
v_apcoo_one_apcoo = TRUE) THEN
p_message_name := 'IGS_AD_ADMPRD_NOT_DUPLICATED';
IF (v_apcoo_one_not_insert = TRUE AND
v_apcoo_one_apcoo = TRUE) THEN
p_message_name := 'IGS_AD_ONE_ADMPERIOD_PRG_OFOP';
SELECT 'X'
FROM IGS_AD_PRD_PS_OF_OPT apcoo
WHERE apcoo.adm_cal_type = p_adm_cal_type AND
apcoo.adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
(p_admission_cat IS NULL OR
apcoo.admission_cat = p_admission_cat);
SELECT ci.start_dt,
ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_adm_cal_type AND
ci.sequence_number = p_new_adm_ci_sequence_number;
SELECT apac.adm_cal_type,
apac.admission_cat,
apac.adm_ci_sequence_number
FROM IGS_AD_PERD_AD_CAT apac
WHERE apac.adm_cal_type = p_adm_cal_type AND
apac.adm_ci_sequence_number = p_old_adm_ci_sequence_number;
SELECT 'X'
FROM IGS_AD_PERD_AD_CAT apac
WHERE apac.adm_cal_type = p_adm_cal_type AND
apac.adm_ci_sequence_number = p_new_adm_ci_sequence_number AND
apac.admission_cat = cp_apac1_admission_cat;
--Insert log if it doesn't already exist
IF (p_s_log_type IS NULL) THEN
v_s_log_type := 'ADM-ROLL';
--Insert an entry into the system log
IGS_GE_GEN_003.GENP_INS_LOG(
v_s_log_type,
(RPAD(p_acad_alternate_code,10)||' '||RPAD(v_adm_calendar,35)),
v_creation_dt);
--could not be inserted
v_acat_not_ins := TRUE;
--Insert admission period admission category
--IGS_GE_NOTE: this will also insert admission period admission process
--Categories via the database trigger
IGS_AD_PERD_AD_CAT_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Adm_Cal_Type => v_apac1_rec.adm_cal_type,
X_Adm_Ci_Sequence_Number => p_new_adm_ci_sequence_number,
X_Admission_Cat => v_apac1_rec.admission_cat,
X_Ci_Start_Dt => NULL,
X_Ci_End_Dt => NULL
);
--Insert admission period COURSE offering options
IF (IGS_AD_INS_ADMPERD.admp_ins_apapc_apcoo(
p_acad_alternate_code,
p_adm_cal_type,
p_old_adm_ci_sequence_number,
v_apac1_rec.admission_cat,
p_new_adm_ci_sequence_number,
v_apac1_rec.admission_cat,
'Y', --indicates this is called from the rollover process
v_s_log_type,
v_creation_dt,
v_message_name,
p_return_type) = FALSE) THEN
v_acat_det_not_ins := TRUE;