[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT coo_id
FROM IGS_PS_OFR_OPT
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
cal_type = p_cal_type AND
location_cd = p_location_cd AND
attendance_mode = p_attendance_mode AND
attendance_type = p_attendance_type AND
delete_flag = 'N';
SELECT *
FROM IGS_PS_OFR_OPT
WHERE coo_id = p_coo_id
AND delete_flag = 'N';
SELECT cop_id
FROM IGS_PS_OFR_PAT
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
cal_type = p_cal_type AND
ci_sequence_number = p_ci_sequence_number AND
location_cd = p_location_cd AND
attendance_mode = p_attendance_mode AND
attendance_type = p_attendance_type;
SELECT *
FROM IGS_PS_OFR_PAT
WHERE cop_id = p_cop_id;
SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_course_cd AND
cous.crv_version_number = p_crv_version_number AND
cous.cal_type = p_cal_type AND
cous.unit_set_cd = p_unit_set_cd;
SELECT cous.only_as_sub_ind
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_course_cd AND
cous.crv_version_number = p_crv_version_number AND
cous.cal_type = p_cal_type AND
cous.unit_set_cd = p_unit_set_cd AND
cous.us_version_number = p_us_version_number;
v_inserted_cnt NUMBER(4);
SELECT location_cd,
attendance_mode,
attendance_type,
cop_id,
coo_id,
offered_ind,
entry_point_ind,
pre_enrol_units_ind,
enrollable_ind,
ivrs_available_ind,
min_entry_ass_score,
guaranteed_entry_ass_scr,
max_cross_faculty_cp,
max_cross_location_cp,
max_cross_mode_cp,
max_hist_cross_faculty_cp,
adm_ass_officer_person_id,
adm_contact_person_id,
grading_schema_cd,
gs_version_number
FROM IGS_PS_OFR_PAT
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
cal_type = p_source_cal_type AND
ci_sequence_number = p_source_sequence_number;
SELECT 'x'
FROM IGS_PS_OFR_PAT
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
cal_type = p_dest_cal_type AND
ci_sequence_number = p_dest_sequence_number AND
location_cd = v_cop_rec.location_cd AND
attendance_type = v_cop_rec.attendance_type AND
attendance_mode = v_cop_rec.attendance_mode;
SELECT IGS_PS_OFR_PAT_COP_ID_S.NEXTVAL
FROM DUAL;
SELECT MAX(gs.version_number)
FROM IGS_AS_GRD_SCHEMA gs
WHERE gs.grading_schema_cd = cp_gs_cd;
v_inserted_cnt := 0; -- number of records inserted
IGS_PS_OFR_PAT_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => p_course_cd,
X_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
X_CAL_TYPE => p_dest_cal_type,
X_VERSION_NUMBER => p_version_number,
X_LOCATION_CD => v_cop_rec.location_cd,
X_ATTENDANCE_TYPE => v_cop_rec.attendance_type,
X_ATTENDANCE_MODE => v_cop_rec.attendance_mode,
X_COP_ID => v_cop_rec.cop_id,
X_COO_ID => v_cop_rec.coo_id,
X_OFFERED_IND => v_cop_rec.offered_ind,
X_CONFIRMED_OFFERING_IND => NULL,
X_ENTRY_POINT_IND => v_cop_rec.entry_point_ind,
X_PRE_ENROL_UNITS_IND => v_cop_rec.pre_enrol_units_ind,
X_ENROLLABLE_IND => v_cop_rec.enrollable_ind,
X_IVRS_AVAILABLE_IND => v_cop_rec.ivrs_available_ind,
X_MIN_ENTRY_ASS_SCORE => v_cop_rec.min_entry_ass_score,
X_GUARANTEED_ENTRY_ASS_SCR => v_cop_rec.guaranteed_entry_ass_scr,
X_MAX_CROSS_FACULTY_CP => v_cop_rec.max_cross_faculty_cp,
X_MAX_CROSS_LOCATION_CP => v_cop_rec.max_cross_location_cp,
X_MAX_CROSS_MODE_CP => v_cop_rec.max_cross_mode_cp,
X_MAX_HIST_CROSS_FACULTY_CP => v_cop_rec.max_hist_cross_faculty_cp,
X_ADM_ASS_OFFICER_PERSON_ID => v_cop_rec.adm_ass_officer_person_id,
X_ADM_CONTACT_PERSON_ID => v_cop_rec.adm_contact_person_id,
X_GRADING_SCHEMA_CD => v_cop_rec.grading_schema_cd,
X_GS_VERSION_NUMBER => v_cop_rec.gs_version_number,
X_MODE => 'R');
v_inserted_cnt := v_inserted_cnt + 1;
ELSIF (v_inserted_cnt = 0) THEN
p_message_name := 'IGS_PS_PRGOFFR_ROLLED_EXIST';
ELSIF (c_course_offering_pattern%ROWCOUNT <> v_inserted_cnt) THEN
p_message_name := 'IGS_PS_PARTIALCREATION_OFFPAT';
ELSIF (c_course_offering_pattern%ROWCOUNT = v_inserted_cnt) THEN
p_message_name := 'IGS_PS_SUCCESS_CREATION_POP';
v_rec_inserted_cnt NUMBER(4) := 0;
cst_none_cop_rec_inserted BOOLEAN := TRUE;
cst_partial_cop_rec_inserted BOOLEAN := TRUE;
cst_all_cop_rec_inserted BOOLEAN := TRUE;
cst_none_coi_rec_inserted BOOLEAN := FALSE;
cst_partial_coi_rec_inserted BOOLEAN := FALSE;
cst_all_coi_rec_inserted BOOLEAN := FALSE;
SELECT 'x'
FROM IGS_CA_TYPE
WHERE cal_type = p_source_cal_type;
SELECT 'x'
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type AND
sequence_number = cp_sequence_number;
SELECT start_dt, end_dt
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type AND
sequence_number = cp_sequence_number;
SELECT coi.course_cd,
coi.version_number,
coi.cal_type,
coi.ci_sequence_number,
coi.ci_start_dt,
coi.ci_end_dt,
coi.min_entry_ass_score,
coi.guaranteed_entry_ass_scr,
coi.created_by,
coi.creation_date,
coi.last_updated_by,
coi.last_update_date,
coi.last_update_login,
coi.request_id,
coi.PROGRAM_APPLICATION_ID,
coi.PROGRAM_ID,
coi.PROGRAM_UPDATE_DATE
FROM IGS_PS_OFR_INST coi,
IGS_PS_VER cv
WHERE coi.cal_type=p_source_cal_type AND
coi.ci_sequence_number= p_source_sequence_number AND
cv.course_cd = coi.course_cd AND
cv.version_number= coi.version_number AND
cv.expiry_dt IS NULL AND
cv.responsible_org_unit_cd LIKE p_org_unit_cd;
SELECT 'x'
FROM IGS_PS_OFR_INST
WHERE course_cd = cp_course_cd AND
version_number = cp_version_number AND
cal_type = p_dest_cal_type AND
ci_sequence_number = p_dest_sequence_number;
-- Check that IGS_PS_COURSE version is still active and can be updated
IF (IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl(
v_coi_rec.course_cd,
v_coi_rec.version_number,
v_message) = TRUE) THEN
OPEN c_check_coi_exist (
v_coi_rec.course_cd,
v_coi_rec.version_number);
IGS_PS_OFR_INST_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => v_coi_rec.course_cd,
X_VERSION_NUMBER => v_coi_rec.version_number,
X_CAL_TYPE => p_dest_cal_type,
X_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
X_CI_START_DT => gv_ci_start_dt,
X_CI_END_DT => gv_ci_end_dt,
X_MIN_ENTRY_ASS_SCORE => v_coi_rec.min_entry_ass_score,
X_GUARANTEED_ENTRY_ASS_SCR => v_coi_rec.guaranteed_entry_ass_scr,
X_MODE => 'R');
v_rec_inserted_cnt := v_rec_inserted_cnt + 1;
cst_all_cop_rec_inserted := FALSE;
cst_all_cop_rec_inserted := FALSE;
cst_none_cop_rec_inserted := FALSE;
cst_none_cop_rec_inserted := FALSE;
IF (cst_none_cop_rec_inserted AND NOT cst_all_cop_rec_inserted) OR
(cst_all_cop_rec_inserted AND NOT cst_none_cop_rec_inserted) THEN
cst_partial_cop_rec_inserted := FALSE;
-- none course_offering_instance is inserted
IF (v_rec_inserted_cnt = 0) THEN
cst_none_coi_rec_inserted := TRUE;
-- all course_offering_instance are inserted
ELSIF (v_rec_inserted_cnt = c_course_offering_instance%ROWCOUNT) THEN
cst_all_coi_rec_inserted := TRUE;
-- partial course_offering_instance are inserted
ELSE
cst_partial_coi_rec_inserted := TRUE;
-- no course_offering_instances AND no course_offering_patterns are inserted
IF (cst_none_coi_rec_inserted AND cst_none_cop_rec_inserted) THEN
v_message := 'IGS_PS_NO_PRGOFFR_INST_FOUND';
-- (no coi AND all cop are inserted) OR (partial coi OR partial cop are
-- inserted)
IF (cst_none_coi_rec_inserted AND cst_all_cop_rec_inserted) OR
(cst_partial_coi_rec_inserted OR cst_partial_cop_rec_inserted) THEN
v_message := 'IGS_PS_PARTIALCREATION_OFFINS';
-- otherwise: (all coi and all cop are inserted) OR
-- (all coi and no cop are inserted)
v_message := 'IGS_PS_SUCCESS_CREAT_POP';
-- This module inserts a record into the IGS_PS_OFR_UNIT_SET table.
-- The routine is used by the defaulting mechanism for a IGS_EN_UNIT_SET. This is
-- invoked from the 'Apply IGS_PS_UNIT Set to IGS_PS_COURSE Offerings' form (ie: CRSF4210)
-- and applies the unit_set_in context to all IGS_PS_COURSE offerings selected via
-- the screen.
-- The routine is not used by the IGS_EN_UNIT_SET rollover process.
DECLARE
v_administrative_ind IGS_EN_UNIT_SET.administrative_ind%TYPE;
SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_course_cd AND
cous.crv_version_number = p_crv_version_number AND
cous.cal_type = p_cal_type AND
cous.unit_set_cd = p_unit_set_cd AND
cous.us_version_number = p_us_version_number;
IGS_PS_OFR_UNIT_SET_Pkg.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => p_course_cd,
X_CRV_VERSION_NUMBER => p_crv_version_number,
X_CAL_TYPE => p_cal_type,
X_UNIT_SET_CD => p_unit_set_cd,
X_US_VERSION_NUMBER => p_us_version_number,
X_OVERRIDE_TITLE => p_override_title,
X_ONLY_AS_SUB_IND => p_only_as_sub_ind,
X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_show_on_official_ntfctn_ind,
X_MODE => 'R');
SELECT 'X'
FROM igs_en_su_attempt
WHERE uoo_id=cp_n_uoo_id
AND ROWNUM=1;
SELECT uso.unit_section_occurrence_id, uso.uoo_id, crs.usec_x_listed_group_id, mwg.class_meet_group_id
FROM igs_ps_usec_occurs uso, igs_ps_usec_x_grpmem crs, igs_ps_uso_clas_meet mwg
WHERE uso.building_code = p_c_building_cd AND
uso.room_code = p_c_room_cd AND
(
TRUNC( uso.start_date ) BETWEEN TRUNC(p_d_start_date) AND TRUNC(p_d_end_date) OR
TRUNC(uso.end_date) BETWEEN TRUNC(p_d_start_date) AND TRUNC(p_d_end_date) OR
TRUNC(p_d_start_date) BETWEEN TRUNC(uso.start_date) AND TRUNC(uso.end_date) OR
TRUNC(p_d_end_date) BETWEEN TRUNC(uso.start_date) AND TRUNC(uso.end_date)
) AND
(
(((TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI')) OR
(TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI'))) AND
(
-- considering boundary conditions as no conflict
(TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI')) AND
(TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI'))))
OR
(((TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI')) OR
(TO_DATE(TO_CHAR(p_d_end_time ,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI'))) AND
(
-- considering boundary conditions as no conflict
(TO_DATE(TO_CHAR(p_d_start_time ,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI')) AND
(TO_DATE(TO_CHAR(p_d_end_time ,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI'))))
) AND
(uso.monday = DECODE (p_c_monday,'Y','Y','-') OR
uso.tuesday = DECODE (p_c_tuesday,'Y','Y','-') OR
uso.wednesday = DECODE (p_c_wednesday,'Y','Y','-') OR
uso.thursday = DECODE (p_c_thrusday,'Y','Y','-') OR
uso.friday = DECODE (p_c_friday,'Y','Y','-') OR
uso.saturday = DECODE (p_c_saturday,'Y','Y','-') OR
uso.sunday = DECODE (p_c_sunday,'Y','Y','-')) AND
uso.uoo_id=crs.uoo_id(+) AND
uso.uoo_id=mwg.uoo_id(+) AND
uso.unit_section_occurrence_id <> p_n_usec_occurs_id;
SELECT 'X'
FROM igs_ps_usec_x_grpmem
WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id
AND uoo_id = p_n_uoo_id;
SELECT 'X'
FROM igs_ps_uso_clas_meet
WHERE class_meet_group_id = cp_class_meet_group_id
AND uoo_id = p_n_uoo_id;
SELECT b.occurrence_identifier,a.unit_cd,a.version_number,a.location_cd,a.unit_class,c.alternate_code
FROM igs_ps_unit_ofr_opt_all a, igs_ps_usec_occurs_all b, igs_ca_inst_all c
WHERE a.uoo_id=b.uoo_id
AND b.unit_section_occurrence_id=cp_unit_section_occurrence_id
AND a.cal_type=c.cal_type
AND a.ci_sequence_number=c.sequence_number;