[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM user_objects
WHERE object_name = 'IGS_HE_PS_PKG';
SELECT *
FROM IGS_PS_VER
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_VER
WHERE course_cd = p_new_course_cd AND
version_number = p_new_version_number;
SELECT *
FROM IGS_PS_AWARD
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number AND
CLOSED_IND = 'N';
SELECT *
FROM IGS_PS_AWD_OWN
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number AND
award_cd = gv_ca_rec.award_cd;
SELECT *
FROM IGS_PS_OWN
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_VER_NOTE cvn
WHERE cvn.course_cd = p_old_course_cd AND
cvn.version_number = p_old_version_number AND
EXISTS (SELECT 1
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = cvn.reference_number AND
nte.note_text IS NOT NULL);
SELECT *
FROM IGS_PE_ALTERNATV_EXT
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_GRP_MBR
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_FI_FND_SRC_RSTN
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_FIELD_STUDY
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_CATEGORISE
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_REF_CD
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_ANL_LOAD
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_ANL_LOAD_U_LN
WHERE course_cd = p_old_course_cd AND
crv_version_number = p_old_version_number AND
yr_num = gv_cal_rec.yr_num AND
effective_start_dt = gv_cal_rec.effective_start_dt;
SELECT *
FROM IGS_PS_OFR
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number;
SELECT *
FROM IGS_PS_OFR_OPT
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number AND
cal_type = gv_co_rec.cal_type
AND delete_flag = 'N';
SELECT *
FROM IGS_PS_ENT_PT_REF_CD
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number AND
cal_type = gv_coo_rec.cal_type AND
location_cd = gv_coo_rec.location_cd AND
attendance_mode = gv_coo_rec.attendance_mode AND
attendance_type = gv_coo_rec.attendance_type;
SELECT *
FROM IGS_PS_OFR_NOTE con
WHERE con.course_cd = p_old_course_cd AND
con.version_number = p_old_version_number AND
cal_type = gv_co_rec.cal_type AND
EXISTS (SELECT 1
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = con.reference_number AND
nte.note_text IS NOT NULL);
SELECT *
FROM IGS_PS_OFR_OPT_NOTE coon
WHERE coon.course_cd = p_old_course_cd AND
coon.version_number = p_old_version_number AND
coon.cal_type = gv_coo_rec.cal_type AND
coon.location_cd = gv_coo_rec.location_cd AND
coon.attendance_mode = gv_coo_rec.attendance_mode AND
coon.attendance_type = gv_coo_rec.attendance_type AND
EXISTS (SELECT 1
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = coon.reference_number AND
nte.note_text IS NOT NULL);
SELECT *
FROM IGS_PS_OFR_INST coi
WHERE coi.course_cd = p_old_course_cd AND
coi.version_number = p_old_version_number AND
coi.cal_type = gv_co_rec.cal_type
ORDER BY coi.ci_end_dt DESC, coi.ci_start_dt DESC;
SELECT *
FROM IGS_PS_OFR_PAT
WHERE course_cd = p_old_course_cd AND
version_number = p_old_version_number AND
cal_type = gv_coi_rec.cal_type AND
ci_sequence_number = gv_coi_rec.ci_sequence_number;
SELECT *
FROM IGS_PS_OFR_PAT_NOTE cop
WHERE cop.cop_id = gv_cop_rec.cop_id AND
EXISTS (SELECT 1
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = cop.reference_number AND
nte.note_text IS NOT NULL);
SELECT IGS_GE_NOTE_RF_NUM_S.NEXTVAL
FROM DUAL;
SELECT IGS_PS_OFR_OPT_COO_ID_S.NEXTVAL
FROM DUAL;
SELECT rowid,IGS_GE_NOTE.*
FROM IGS_GE_NOTE
WHERE reference_number = gv_coon_rec.reference_number;
IGS_GE_NOTE_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_REFERENCE_NUMBER =>gv_ref_num,
X_S_NOTE_FORMAT_TYPE =>SGN_Rec.s_note_format_type,
X_NOTE_TEXT =>SGN_Rec.note_text,
X_MODE =>'R');
IGS_PS_OFR_OPT_NOTE_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => gv_coon_rec.cal_type,
X_ATTENDANCE_MODE => gv_coon_rec.attendance_mode,
X_REFERENCE_NUMBER => gv_ref_num,
X_ATTENDANCE_TYPE => gv_coon_rec.attendance_type,
X_LOCATION_CD => gv_coon_rec.location_cd,
X_COO_ID => gv_coo_seq_num,
X_CRS_NOTE_TYPE => gv_coon_rec.crs_note_type,
X_MODE => 'R'
);
SELECT
rowid , IGS_GE_NOTE.*
FROM
IGS_GE_NOTE
WHERE
reference_number = gv_copn_rec.reference_number;
IGS_GE_NOTE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_REFERENCE_NUMBER => gv_ref_num,
X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
X_NOTE_TEXT => Rec_SGN.note_text,
X_MODE => 'R'
);
IGS_PS_OFR_PAT_NOTE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CI_SEQUENCE_NUMBER => gv_copn_rec.ci_sequence_number,
X_CAL_TYPE => gv_copn_rec.cal_type,
X_VERSION_NUMBER => p_new_version_number,
X_LOCATION_CD => gv_copn_rec.location_cd,
X_ATTENDANCE_TYPE => gv_copn_rec.attendance_type,
X_REFERENCE_NUMBER => gv_ref_num,
X_ATTENDANCE_MODE => gv_copn_rec.attendance_mode,
X_COP_ID => gv_cop_seq_num,
X_CRS_NOTE_TYPE => gv_copn_rec.crs_note_type,
X_MODE => 'R'
);
SELECT MAX(gs.version_number)
FROM IGS_AS_GRD_SCHEMA gs
WHERE gs.grading_schema_cd = cp_gs_cd;
SELECT IGS_PS_OFR_PAT_COP_ID_S.NEXTVAL
INTO gv_cop_seq_num
FROM DUAL;
IGS_PS_OFR_PAT_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_COURSE_CD => p_new_course_cd,
X_CI_SEQUENCE_NUMBER => gv_cop_rec.ci_sequence_number,
X_CAL_TYPE => gv_cop_rec.cal_type,
X_VERSION_NUMBER => p_new_version_number,
X_LOCATION_CD => gv_cop_rec.location_cd,
X_ATTENDANCE_TYPE => gv_cop_rec.attendance_type,
X_ATTENDANCE_MODE => gv_cop_rec.attendance_mode,
X_COP_ID => gv_cop_seq_num,
X_COO_ID => gv_coo_seq_num,
X_OFFERED_IND => gv_cop_rec.offered_ind,
X_CONFIRMED_OFFERING_IND => gv_cop_rec.confirmed_offering_ind,
X_ENTRY_POINT_IND => gv_cop_rec.entry_point_ind,
X_PRE_ENROL_UNITS_IND => gv_cop_rec.pre_enrol_units_ind,
X_ENROLLABLE_IND => gv_cop_rec.enrollable_ind,
X_IVRS_AVAILABLE_IND => gv_cop_rec.ivrs_available_ind,
X_MIN_ENTRY_ASS_SCORE => NULL,
X_GUARANTEED_ENTRY_ASS_SCR => NULL,
X_MAX_CROSS_FACULTY_CP => NULL,
X_MAX_CROSS_LOCATION_CP => NULL,
X_MAX_CROSS_MODE_CP => NULL,
X_MAX_HIST_CROSS_FACULTY_CP => NULL,
X_ADM_ASS_OFFICER_PERSON_ID => gv_cop_rec.adm_ass_officer_person_id,
X_ADM_CONTACT_PERSON_ID => gv_cop_rec.adm_contact_person_id,
X_GRADING_SCHEMA_CD => gv_cop_rec.grading_schema_cd,
X_GS_VERSION_NUMBER => gv_cop_rec.gs_version_number,
X_MODE => 'R'
);
SELECT
rowid,IGS_GE_NOTE.*
FROM
IGS_GE_NOTE
WHERE
reference_number = gv_con_rec.reference_number;
IGS_GE_NOTE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_REFERENCE_NUMBER => gv_ref_num,
X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
X_NOTE_TEXT => Rec_SGN.note_text,
X_MODE => 'R'
);
IGS_PS_OFR_NOTE_PKG.INSERT_ROW (
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_REFERENCE_NUMBER => gv_ref_num,
X_CAL_TYPE => gv_con_rec.cal_type,
X_VERSION_NUMBER => p_new_version_number,
X_CRS_NOTE_TYPE => gv_con_rec.crs_note_type,
X_MODE => 'R'
);
IGS_PS_ENT_PT_REF_CD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_SEQUENCE_NUMBER => gv_ceprcd_rec.sequence_number,
X_REFERENCE_CD_TYPE => gv_ceprcd_rec.reference_cd_type,
X_ATTENDANCE_TYPE => gv_ceprcd_rec.attendance_type,
X_CAL_TYPE => gv_ceprcd_rec.cal_type,
X_LOCATION_CD => gv_ceprcd_rec.location_cd,
X_VERSION_NUMBER => p_new_version_number,
X_ATTENDANCE_MODE => gv_ceprcd_rec.attendance_mode,
X_COO_ID => gv_coo_seq_num,
X_UNIT_SET_CD => gv_ceprcd_rec.unit_set_cd,
X_US_VERSION_NUMBER => gv_ceprcd_rec.us_version_number,
X_REFERENCE_CD => gv_ceprcd_rec.reference_cd,
X_DESCRIPTION => gv_ceprcd_rec.description,
X_MODE => 'R'
);
SELECT cooac.cal_type,
cooac.location_cd,
cooac.attendance_mode,
cooac.attendance_type,
cooac.admission_cat,
cooac.system_default_ind
FROM IGS_PS_OF_OPT_AD_CAT cooac
WHERE cooac.course_cd = p_old_course_cd AND
cooac.version_number = p_old_version_number AND
cooac.cal_type = gv_coo_rec.cal_type AND
cooac.location_cd = gv_coo_rec.location_cd AND
cooac.attendance_mode = gv_coo_rec.attendance_mode AND
cooac.attendance_type = gv_coo_rec.attendance_type;
SELECT cacus.cal_type,
cacus.location_cd,
cacus.attendance_mode,
cacus.attendance_type,
cacus.admission_cat,
cacus.unit_set_cd,
cacus.us_version_number
FROM IGS_PS_COO_AD_UNIT_S cacus
WHERE cacus.course_cd = p_old_course_cd AND
cacus.crv_version_number = p_old_version_number AND
cacus.cal_type = cp_cooac_cal_type AND
cacus.location_cd = cp_cooac_location_cd AND
cacus.attendance_mode = cp_cooac_attendance_mode AND
cacus.attendance_type = cp_cooac_attendance_type AND
cacus.admission_cat = cp_cooac_admission_cat;
IGS_PS_OF_OPT_AD_CAT_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_cooac_rec.cal_type,
X_LOCATION_CD => v_cooac_rec.location_cd,
X_ATTENDANCE_TYPE => v_cooac_rec.attendance_type,
X_ATTENDANCE_MODE => v_cooac_rec.attendance_mode,
X_ADMISSION_CAT => v_cooac_rec.admission_cat,
X_COO_ID => gv_coo_seq_num,
X_SYSTEM_DEFAULT_IND => v_cooac_rec.system_default_ind,
X_MODE => 'R'
);
IGS_PS_COO_AD_UNIT_S_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_cacus_rec.cal_type,
X_LOCATION_CD => v_cacus_rec.location_cd,
X_ATTENDANCE_MODE => v_cacus_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_cacus_rec.attendance_type,
X_ADMISSION_CAT => v_cacus_rec.admission_cat,
X_UNIT_SET_CD => v_cacus_rec.unit_set_cd,
X_US_VERSION_NUMBER => v_cacus_rec.us_version_number,
X_MODE => 'R'
);
SELECT coous.cal_type,
coous.location_cd,
coous.attendance_mode,
coous.attendance_type,
coous.unit_set_cd,
coous.us_version_number
FROM IGS_PS_OF_OPT_UNT_ST coous
WHERE coous.course_cd = p_old_course_cd AND
coous.crv_version_number = p_old_version_number AND
coous.cal_type = gv_coo_rec.cal_type AND
coous.location_cd = gv_coo_rec.location_cd AND
coous.attendance_mode = gv_coo_rec.attendance_mode AND
coous.attendance_type = gv_coo_rec.attendance_type AND
EXISTS (SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_new_course_cd AND
cous.crv_version_number = p_new_version_number AND
cous.cal_type = coous.cal_type AND
cous.unit_set_cd = coous.unit_set_cd AND
cous.us_version_number = coous.us_version_number);
IGS_PS_OFR_OPT_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => gv_coo_rec.cal_type,
X_ATTENDANCE_MODE => gv_coo_rec.attendance_mode,
X_ATTENDANCE_TYPE => gv_coo_rec.attendance_type,
X_LOCATION_CD => gv_coo_rec.location_cd,
X_COO_ID => gv_coo_seq_num,
X_FORCED_LOCATION_IND => gv_coo_rec.forced_location_ind,
X_FORCED_ATT_MODE_IND => gv_coo_rec.forced_att_mode_ind,
X_FORCED_ATT_TYPE_IND => gv_coo_rec.forced_att_type_ind,
X_TIME_LIMITATION => gv_coo_rec.time_limitation,
X_ENR_OFFICER_PERSON_ID => gv_coo_rec.enr_officer_person_id,
X_ATTRIBUTE_CATEGORY => gv_coo_rec.attribute_category,
X_ATTRIBUTE1 => gv_coo_rec.attribute1,
X_ATTRIBUTE2 => gv_coo_rec.attribute2,
X_ATTRIBUTE3 => gv_coo_rec.attribute3,
X_ATTRIBUTE4 => gv_coo_rec.attribute4,
X_ATTRIBUTE5 => gv_coo_rec.attribute5,
X_ATTRIBUTE6 => gv_coo_rec.attribute6,
X_ATTRIBUTE7 => gv_coo_rec.attribute7,
X_ATTRIBUTE8 => gv_coo_rec.attribute8,
X_ATTRIBUTE9 => gv_coo_rec.attribute9,
X_ATTRIBUTE10 => gv_coo_rec.attribute10,
X_ATTRIBUTE11 => gv_coo_rec.attribute11,
X_ATTRIBUTE12 => gv_coo_rec.attribute12,
X_ATTRIBUTE13 => gv_coo_rec.attribute13,
X_ATTRIBUTE14 => gv_coo_rec.attribute14,
X_ATTRIBUTE15 => gv_coo_rec.attribute15,
X_ATTRIBUTE16 => gv_coo_rec.attribute16,
X_ATTRIBUTE17 => gv_coo_rec.attribute17,
X_ATTRIBUTE18 => gv_coo_rec.attribute18,
X_ATTRIBUTE19 => gv_coo_rec.attribute19,
X_ATTRIBUTE20 => gv_coo_rec.attribute20,
X_MODE => 'R',
X_ORG_ID => l_org_id ,
x_program_length => gv_coo_rec.program_length, --added as per bug# 2608227
x_program_length_measurement => gv_coo_rec.program_length_measurement --added as per bug# 2608227
);
IGS_PS_OF_OPT_UNT_ST_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_LOCATION_CD => v_coous_rec.location_cd,
X_ATTENDANCE_MODE => v_coous_rec.attendance_mode,
X_CAL_TYPE => v_coous_rec.cal_type,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_ATTENDANCE_TYPE => v_coous_rec.attendance_type,
X_US_VERSION_NUMBER => v_coous_rec.us_version_number,
X_UNIT_SET_CD => v_coous_rec.unit_set_cd,
X_COO_ID => gv_coo_seq_num,
X_MODE => 'R'
);
SELECT 'x'
FROM IGS_PS_OFR_OPT coo
WHERE coo.course_cd = p_new_course_cd AND
coo.version_number = p_new_version_number AND
coo.cal_type = cp_cal_type AND
coo.location_cd = cp_location_cd AND
coo.attendance_mode = cp_attendance_mode AND
coo.attendance_type = cp_attendance_type AND
coo.delete_flag = 'N';
IGS_PS_OFR_INST_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => gv_coi_rec.cal_type,
X_CI_SEQUENCE_NUMBER => gv_coi_rec.ci_sequence_number,
X_CI_START_DT => gv_coi_rec.ci_start_dt,
X_CI_END_DT => gv_coi_rec.ci_end_dt,
X_MIN_ENTRY_ASS_SCORE => gv_coi_rec.min_entry_ass_score,
X_GUARANTEED_ENTRY_ASS_SCR => gv_coi_rec.guaranteed_entry_ass_scr,
X_MODE => 'R' );
--sarakshi 15-May-2006 Bug#3460640,modified the call to IGS_PS_PAT_STUDY_PRD_PKG.INSERT_ROW with correct values
BEGIN
DECLARE
v_pos_seq_num IGS_PS_PAT_OF_STUDY.sequence_number%TYPE;
SELECT IGS_PS_PAT_OF_STUDY_POS_NUM_S.NEXTVAL
FROM DUAL;
SELECT IGS_PS_PAT_STUDY_UNT_POSPSEQ_S.NEXTVAL
FROM DUAL;
SELECT IGS_PS_PAT_STUDY_UNT_SEQ_NUM_S.NEXTVAL
FROM DUAL;
SELECT pos.cal_type,
pos.sequence_number,
pos.location_cd,
pos.attendance_mode,
pos.attendance_type,
pos.unit_set_cd,
pos.admission_cal_type,
pos.admission_cat,
pos.aprvd_ci_sequence_number,
pos.number_of_periods,
pos.always_pre_enrol_ind,
pos.acad_perd_unit_set
FROM IGS_PS_PAT_OF_STUDY pos
WHERE pos.course_cd = p_old_course_cd AND
pos.version_number = p_old_version_number AND
pos.cal_type = gv_co_rec.cal_type;
SELECT posp.cal_type,
posp.pos_sequence_number,
posp.sequence_number,
posp.acad_period_num,
posp.teach_cal_type,
posp.description
FROM IGS_PS_PAT_STUDY_PRD posp
WHERE posp.course_cd = p_old_course_cd AND
posp.version_number = p_old_version_number AND
posp.cal_type = gv_co_rec.cal_type AND
posp.pos_sequence_number = cp_pos_seq_num;
SELECT posu.cal_type,
posu.pos_sequence_number,
posu.posp_sequence_number,
posu.sequence_number,
posu.unit_cd,
posu.unit_location_cd,
posu.unit_class,
posu.description,
posu.core_ind
FROM IGS_PS_PAT_STUDY_UNT posu
WHERE posu.course_cd = p_old_course_cd AND
posu.version_number = p_old_version_number AND
posu.cal_type = gv_co_rec.cal_type AND
posu.pos_sequence_number = cp_pos_seq_num AND
posu.posp_sequence_number = cp_posp_seq_num;
IGS_PS_PAT_OF_STUDY_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CAL_TYPE => v_pos_rec.cal_type,
X_VERSION_NUMBER => p_new_version_number,
X_SEQUENCE_NUMBER => v_pos_seq_num,
X_LOCATION_CD => v_pos_rec.location_cd,
X_ATTENDANCE_MODE => v_pos_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_pos_rec.attendance_type,
X_UNIT_SET_CD => v_pos_rec.unit_set_cd,
X_ADMISSION_CAL_TYPE => v_pos_rec.admission_cal_type,
X_ADMISSION_CAT => v_pos_rec.admission_cat,
X_APRVD_CI_SEQUENCE_NUMBER => v_pos_rec.aprvd_ci_sequence_number,
X_NUMBER_OF_PERIODS => v_pos_rec.number_of_periods,
X_ALWAYS_PRE_ENROL_IND => v_pos_rec.always_pre_enrol_ind,
X_acad_perd_unit_set => v_pos_rec.acad_perd_unit_set,
X_MODE => 'R'
);
IGS_PS_PAT_STUDY_PRD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_POS_SEQUENCE_NUMBER => v_pos_seq_num,--this is the FK
X_SEQUENCE_NUMBER => v_posp_seq_num,--this is the PK
X_CAL_TYPE => v_posp_rec.cal_type,
X_ACAD_PERIOD_NUM => v_posp_rec.acad_period_num,
X_TEACH_CAL_TYPE => v_posp_rec.teach_cal_type,
X_DESCRIPTION => v_posp_rec.description,
X_MODE => 'R'
);
IGS_PS_PAT_STUDY_UNT_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_POS_SEQUENCE_NUMBER => v_pos_seq_num,
X_SEQUENCE_NUMBER => v_posu_seq_num,
X_POSP_SEQUENCE_NUMBER => v_posp_seq_num,
X_CAL_TYPE => v_posu_rec.cal_type,
X_UNIT_CD => v_posu_rec.unit_cd,
X_UNIT_LOCATION_CD => v_posu_rec.unit_location_cd,
X_UNIT_CLASS => v_posu_rec.unit_class,
X_DESCRIPTION => v_posu_rec.description,
X_MODE => 'R',
X_CORE_IND => v_posu_rec.core_ind
);
SELECT cous.cal_type,
cous.unit_set_cd,
cous.us_version_number,
cous.override_title,
cous.only_as_sub_ind,
cous.show_on_official_ntfctn_ind
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_old_course_cd AND
cous.crv_version_number = p_old_version_number AND
cous.cal_type = gv_co_rec.cal_type;
SELECT cousr.cal_type,
cousr.sup_unit_set_cd,
cousr.sup_us_version_number,
cousr.sub_unit_set_cd,
cousr.sub_us_version_number
FROM IGS_PS_OF_UNT_SET_RL cousr,
IGS_PS_OFR_UNIT_SET cous_sup,
IGS_PS_OFR_UNIT_SET cous_sub
WHERE cousr.course_cd = p_old_course_cd AND
cousr.crv_version_number = p_old_version_number AND
cousr.cal_type = gv_co_rec.cal_type AND
cousr.cal_type = cous_sub.cal_type AND
cousr.sub_unit_set_cd = cous_sub.unit_set_cd AND
cousr.sub_us_version_number = cous_sub.us_version_number AND
cousr.course_cd = cous_sub.course_cd AND
cousr.crv_version_number = cous_sub.crv_version_number AND
cousr.sup_unit_set_cd = cous_sup.unit_set_cd AND
cousr.sup_us_version_number = cous_sup.us_version_number AND
cousr.course_cd = cous_sup.course_cd AND
cousr.crv_version_number = cous_sup.crv_version_number AND
cousr.cal_type = cous_sup.cal_type AND
EXISTS (SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_new_course_cd AND
cous.crv_version_number = p_new_version_number AND
cous.cal_type = cous_sup.cal_type AND
cous.unit_set_cd = cous_sup.unit_set_cd AND
cous.us_version_number = cous_sup.us_version_number) AND
EXISTS (SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.course_cd = p_new_course_cd AND
cous.crv_version_number = p_new_version_number AND
cous.cal_type = cous_sub.cal_type AND
cous.unit_set_cd = cous_sub.unit_set_cd AND
cous.us_version_number = cous_sub.us_version_number);
IGS_PS_OFR_UNIT_SET_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_cous_rec.cal_type,
X_UNIT_SET_CD => v_cous_rec.unit_set_cd,
X_US_VERSION_NUMBER => v_cous_rec.us_version_number,
X_OVERRIDE_TITLE => v_cous_rec.override_title,
X_ONLY_AS_SUB_IND => v_cous_rec.only_as_sub_ind,
X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_cous_rec.show_on_official_ntfctn_ind,
X_MODE => 'R'
);
IGS_PS_OF_UNT_SET_RL_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_SUP_US_VERSION_NUMBER => v_cousr_rec.sup_us_version_number,
X_SUB_UNIT_SET_CD => v_cousr_rec.sub_unit_set_cd,
X_SUP_UNIT_SET_CD => v_cousr_rec.sup_unit_set_cd,
X_CAL_TYPE => v_cousr_rec.cal_type,
X_SUB_US_VERSION_NUMBER => v_cousr_rec.sub_us_version_number,
X_MODE => 'R'
);
IGS_PS_OFR_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => gv_co_rec.cal_type,
X_ATTRIBUTE_CATEGORY => gv_co_rec.attribute_category,
X_ATTRIBUTE1 => gv_co_rec.attribute1,
X_ATTRIBUTE2 => gv_co_rec.attribute2,
X_ATTRIBUTE3 => gv_co_rec.attribute3,
X_ATTRIBUTE4 => gv_co_rec.attribute4,
X_ATTRIBUTE5 => gv_co_rec.attribute5,
X_ATTRIBUTE6 => gv_co_rec.attribute6,
X_ATTRIBUTE7 => gv_co_rec.attribute7,
X_ATTRIBUTE8 => gv_co_rec.attribute8,
X_ATTRIBUTE9 => gv_co_rec.attribute9,
X_ATTRIBUTE10 => gv_co_rec.attribute10,
X_ATTRIBUTE11 => gv_co_rec.attribute11,
X_ATTRIBUTE12 => gv_co_rec.attribute12,
X_ATTRIBUTE13 => gv_co_rec.attribute13,
X_ATTRIBUTE14 => gv_co_rec.attribute14,
X_ATTRIBUTE15 => gv_co_rec.attribute15,
X_ATTRIBUTE16 => gv_co_rec.attribute16,
X_ATTRIBUTE17 => gv_co_rec.attribute17,
X_ATTRIBUTE18 => gv_co_rec.attribute18,
X_ATTRIBUTE19 => gv_co_rec.attribute19,
X_ATTRIBUTE20 => gv_co_rec.attribute20,
X_MODE => 'R' ,
X_ORG_ID => l_org_id
);
IGS_PS_AWD_OWN_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_ORG_UNIT_CD => gv_cao_rec.org_unit_cd,
X_OU_START_DT => gv_cao_rec.ou_start_dt,
X_AWARD_CD => gv_cao_rec.award_cd,
X_VERSION_NUMBER => p_new_version_number,
X_PERCENTAGE => gv_cao_rec.percentage,
X_MODE => 'R'
);
IGS_PS_ANL_LOAD_U_LN_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_EFFECTIVE_START_DT => gv_calulink_rec.effective_start_dt,
X_YR_NUM => gv_calulink_rec.yr_num,
X_UV_VERSION_NUMBER => gv_calulink_rec.uv_version_number,
X_UNIT_CD => gv_calulink_rec.unit_cd,
X_MODE => 'R'
);
IGS_PS_AWARD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_AWARD_CD => gv_ca_rec.award_cd,
X_VERSION_NUMBER => p_new_version_number,
X_MODE => 'R',
X_DEFAULT_IND => gv_ca_rec.default_ind,
X_CLOSED_IND => gv_ca_rec.closed_ind
);
IGS_PS_OWN_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_OU_START_DT => gv_cow_rec.ou_start_dt,
X_ORG_UNIT_CD => gv_cow_rec.org_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_PERCENTAGE => gv_cow_rec.percentage,
X_MODE => 'R'
);
IGS_PE_ALTERNATV_EXT_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_EXIT_COURSE_CD => gv_ae_rec.exit_course_cd,
X_EXIT_VERSION_SET => gv_ae_rec.exit_version_set,
X_MODE => 'R'
);
IGS_PS_GRP_MBR_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_COURSE_GROUP_CD => gv_cgm_rec.course_group_cd,
X_VERSION_NUMBER => p_new_version_number,
X_MODE => 'R'
);
IGS_FI_FND_SRC_RSTN_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_FUNDING_SOURCE => gv_fsr_rec.funding_source,
X_VERSION_NUMBER => p_new_version_number,
X_DFLT_IND => gv_fsr_rec.dflt_ind,
X_RESTRICTED_IND => gv_fsr_rec.restricted_ind,
X_MODE => 'R'
);
IGS_PS_FIELD_STUDY_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_FIELD_OF_STUDY => gv_cfos_rec.field_of_study,
X_VERSION_NUMBER => p_new_version_number,
X_MAJOR_FIELD_IND => gv_cfos_rec.major_field_ind,
X_PERCENTAGE => gv_cfos_rec.percentage,
X_MODE => 'R'
);
IGS_PS_CATEGORISE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_COURSE_CAT => gv_ccat_rec.course_cat,
X_MODE => 'R',
X_ORG_ID => l_org_id
);
IGS_PS_REF_CD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_REFERENCE_CD => gv_crcd_rec.reference_cd,
X_REFERENCE_CD_TYPE => gv_crcd_rec.reference_cd_type,
X_DESCRIPTION => gv_crcd_rec.description,
X_MODE => 'R'
);
IGS_PS_ANL_LOAD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_VERSION_NUMBER => p_new_version_number,
X_COURSE_CD => p_new_course_cd,
X_YR_NUM => gv_cal_rec.yr_num,
X_EFFECTIVE_START_DT => gv_cal_rec.effective_start_dt,
X_EFFECTIVE_END_DT => gv_cal_rec.effective_end_dt,
X_ANNUAL_LOAD_VAL => gv_cal_rec.annual_load_val,
X_MODE => 'R'
);
SELECT rowid,IGS_GE_NOTE.*
FROM IGS_GE_NOTE
WHERE reference_number = gv_cvn_rec.reference_number;
IGS_GE_NOTE_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_REFERENCE_NUMBER =>gv_ref_num,
X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
X_NOTE_TEXT =>Rec_SGN.note_text,
X_MODE =>'R');
IGS_PS_VER_NOTE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_REFERENCE_NUMBER => gv_ref_num,
X_CRS_NOTE_TYPE => gv_cvn_rec.crs_note_type,
X_MODE => 'R'
);
SELECT IGS_PS_STAGE_SEQ_NUM_S.NEXTVAL
FROM DUAL;
SELECT cst.sequence_number,
cst.course_stage_type,
cst.description,
cst.comments
FROM IGS_PS_STAGE cst
WHERE cst.course_cd = p_old_course_cd AND
cst.version_number = p_old_version_number;
SELECT csr.cst_sequence_number,
csr.s_rule_call_cd,
csr.rul_sequence_number
FROM IGS_PS_STAGE_RU csr
WHERE csr.course_cd = p_old_course_cd AND
csr.version_number = p_old_version_number AND
csr.cst_sequence_number = cp_cst_seq_num;
IGS_PS_STAGE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_SEQUENCE_NUMBER => v_cst_seq_num,
X_COURSE_STAGE_TYPE => v_cst_rec.course_stage_type,
X_DESCRIPTION => v_cst_rec.description,
X_COMMENTS => v_cst_rec.comments,
X_MODE => 'R'
);
IGS_PS_STAGE_RU_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_S_RULE_CALL_CD => v_csr_rec.s_rule_call_cd,
X_CST_SEQUENCE_NUMBER => v_cst_seq_num,
X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_num,
X_MODE => 'R'
);
SELECT cvr.s_rule_call_cd,
cvr.rul_sequence_number
FROM IGS_PS_VER_RU cvr
WHERE cvr.course_cd = p_old_course_cd AND
cvr.version_number = p_old_version_number;
IGS_PS_VER_RU_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_S_RULE_CALL_CD => v_cvr_rec.s_rule_call_cd,
X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_number,
X_MODE => 'R'
);
SELECT dms.milestone_type,
dms.attendance_type,
dms.attendance_mode,
dms.offset_days,
dms.comments
FROM IGS_RE_DFLT_MS_SET dms
WHERE dms.course_cd = p_old_course_cd AND
dms.version_number = p_old_version_number;
SELECT IGS_RE_DFLT_MS_SET_SEQ_NUM_S.NEXTVAL
FROM DUAL;
IGS_RE_DFLT_MS_SET_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_MILESTONE_TYPE => v_dms_rec.milestone_type,
X_ATTENDANCE_TYPE => v_dms_rec.attendance_type,
X_ATTENDANCE_MODE => v_dms_rec.attendance_mode,
X_SEQUENCE_NUMBER => v_seq_num,
X_OFFSET_DAYS => v_dms_rec.offset_days,
X_COMMENTS => v_dms_rec.comments,
X_MODE => 'R' ,
X_ORG_ID => l_org_id
);
SELECT *
FROM igs_en_psv_term_it
WHERE course_cd = p_old_course_cd
AND version_number = p_old_version_number;
igs_en_psv_term_it_pkg.insert_row(x_rowid => l_rowid,
x_cal_type => l_psv_term_rec.cal_type,
x_sequence_number => l_psv_term_rec.sequence_number,
x_course_cd => p_new_course_cd,
x_version_number => p_new_version_number,
x_term_instruction_time => l_psv_term_rec.term_instruction_time);
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_old_uoo_id ;
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_new_uoo_id ;
SELECT *
FROM igs_ps_usec_occurs
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_ocur_ref
WHERE unit_section_occurrence_id = p_usec_id;
SELECT *
FROM igs_ps_uso_instrctrs
WHERE unit_section_occurrence_id = cp_Unit_Section_occurrence_id;
SELECT *
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_wlst_pri
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_wlst_prf
WHERE unit_sec_waitlist_priority_id = p_usec_pri_id;
SELECT *
FROM igs_ps_usec_cps
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name = cp_grp_name
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_no ;
SELECT grpmem.*, grp.usec_x_listed_group_name , grp.location_inheritance,
grp.max_enr_group,max_ovr_group
FROM igs_ps_usec_x_grpmem grpmem, igs_ps_usec_x_grp grp
WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
AND grpmem.uoo_id = cp_uoo_id;
SELECT *
FROM igs_ps_usec_spnsrshp
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_tch_resp
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_as
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_as_us_ai_group
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_unitass_item
WHERE uoo_id = cp_uoo_id
AND us_ass_item_group_id=cp_us_ass_item_group_id;
SELECT *
FROM igs_ps_usec_ref
WHERE uoo_id = p_uoo_id;
SELECT *
FROM igs_ps_usec_ref_cd
WHERE unit_section_reference_id = p_usec_ref_id;
SELECT *
FROM igs_ps_us_req_ref_cd
WHERE unit_section_reference_id = cp_usec_ref_id;
SELECT *
FROM igs_ps_usec_grd_schm
WHERE uoo_id = p_uoo_id;
SELECT snote.*, genote.s_note_format_type, genote.note_text
FROM igs_ps_unt_ofr_opt_n snote, igs_ge_note genote
WHERE uoo_id = cp_uoo_id
AND snote.reference_number = geNote.reference_number;
SELECT *
FROM igs_ps_usec_ru_v
WHERE uoo_id = cp_uoo_id;
SELECT *
FROM igs_ps_usec_category
WHERE uoo_id = cp_uoo_id;
SELECT *
FROM igs_ps_us_unsched_cl
WHERE uoo_id = cp_uoo_id;
SELECT *
FROM igs_ps_tch_resp_ovrd
WHERE uoo_id = cp_uoo_id;
SELECT *
FROM igs_ps_usec_sp_fees
WHERE uoo_id = cp_uoo_id;
SELECT a.*
FROM igs_ps_nsus_rtn a,
igs_ps_unit_ofr_opt_all b
WHERE a.uoo_id = p_old_uoo_id
AND a.uoo_id = b.uoo_id
AND b.non_std_usec_ind = 'Y'
AND a.definition_code IN ('UNIT_SECTION','UNIT_SECTION_FEE_TYPE');
SELECT *
FROM igs_ps_nsus_rtn_dtl
WHERE non_std_usec_rtn_id = cp_non_std_usec_rtn_id;
SELECT *
FROM igs_ps_uso_facility
WHERE unit_section_occurrence_id = cp_unit_section_occurrence_id;
SELECT ci.cal_type,ci.sequence_number
FROM igs_fi_fee_type ft,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE ft.s_fee_type IN ('TUTNFEE', 'OTHER', 'SPECIAL', 'AUDIT')
AND ft.closed_ind = 'N'
AND ft.fee_type = ftci.fee_type
AND ft.fee_type = cp_c_source_fee_type
AND ftci.fee_cal_type = ci.cal_type
AND ftci.fee_ci_sequence_number = ci.sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'FEE'
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE';
SELECT start_dt,end_dt
FROM igs_ca_inst_all
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_num;
SELECT *
FROM igs_ps_sch_ocr_cfig;
SELECT 'X'
FROM igs_ps_unitass_item
WHERE uoo_id = cp_uoo_id
AND us_ass_item_group_id = cp_us_ass_item_group_id;
SELECT instructor_id
FROM igs_ps_usec_tch_resp
WHERE uoo_id = cp_uoo_id
AND lead_instructor_flag = 'Y';
SELECT instructor_id
FROM igs_ps_usec_tch_resp
WHERE uoo_id = cp_uoo_id
AND lead_instructor_flag = 'Y'
AND instructor_id = cp_ins;
SELECT sup_cal_type cal_type, sup_ci_sequence_number sequence_number
FROM igs_ca_inst_rel
WHERE (sub_cal_type,sub_ci_sequence_number) IN (SELECT load_cal_type, load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = cp_c_cal_type
AND teach_ci_sequence_number = cp_n_sequence_number);
SELECT 'X'
FROM igs_ps_usec_occurs_all
WHERE uoo_id = cp_uoo_id
AND occurrence_identifier=cp_occurrence_identifier
AND ROWNUM = 1 ;
igs_ps_usec_occurs_pkg.insert_row (
x_rowid => lv_rowid,
x_unit_section_occurrence_id=> l_usec_id,
x_uoo_id => p_new_uoo_id ,
x_monday => l_c_monday,
x_tuesday => l_c_tuesday,
x_wednesday => l_c_wednesday,
x_thursday => l_c_thursday,
x_friday => l_c_friday,
x_saturday => l_c_saturday,
x_sunday => l_c_sunday,
x_start_time => l_d_start_time,
x_end_time => l_d_end_time,
x_building_code => l_c_building_code,
x_room_code => l_c_room_code,
x_schedule_status => NULL,
x_status_last_updated => NULL,
x_instructor_id => usec_occurs_rec.instructor_id ,
x_error_text => NULL,
x_mode => 'R' ,
x_org_id => l_org_id,
x_start_date => l_d_uso_dest_start_dt,
x_end_date => l_d_uso_dest_end_dt,
x_to_be_announced => usec_occurs_rec.to_be_announced,
x_attribute_category => l_c_attribute_category,
x_attribute1 => l_c_attribute1,
x_attribute2 => l_c_attribute2,
x_attribute3 => l_c_attribute3,
x_attribute4 => l_c_attribute4,
x_attribute5 => l_c_attribute5,
x_attribute6 => l_c_attribute6,
x_attribute7 => l_c_attribute7,
x_attribute8 => l_c_attribute8,
x_attribute9 => l_c_attribute9,
x_attribute10 => l_c_attribute10,
x_attribute11 => l_c_attribute11,
x_attribute12 => l_c_attribute12,
x_attribute13 => l_c_attribute13,
x_attribute14 => l_c_attribute14,
x_attribute15 => l_c_attribute15,
x_attribute16 => l_c_attribute16,
x_attribute17 => l_c_attribute17,
x_attribute18 => l_c_attribute18,
x_attribute19 => l_c_attribute19,
x_attribute20 => l_c_attribute20,
x_inst_notify_ind => l_c_inst_change_notify,
x_notify_status => 'NEW',
x_dedicated_building_code => l_c_dedicated_building_code,
x_dedicated_room_code => l_c_dedicated_room_code,
x_preferred_building_code => l_c_preferred_building_code,
x_preferred_room_code => l_c_preferred_room_code,
x_preferred_region_code => l_c_preferred_region_code,
x_no_set_day_ind => usec_occurs_rec.no_set_day_ind,
x_cancel_flag => 'N',
x_occurrence_identifier => usec_occurs_rec.occurrence_identifier,
x_abort_flag => 'N');
igs_ps_usec_ocur_ref_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_sec_occur_reference_id => l_usec_ref_id,
x_unit_section_occurrence_id => l_usec_id,
x_reference_code_type => usec_occurs_refcd_rec.reference_code_type,
x_reference_code => usec_occurs_refcd_rec.reference_code,
x_mode => 'R',
x_reference_code_desc => usec_occurs_refcd_rec.reference_code_desc);
igs_ps_uso_instrctrs_pkg.insert_row (
x_rowid => lv_rowid,
x_uso_instructor_id => l_uso_instructor_id,
x_unit_section_occurrence_id => l_usec_id,
x_instructor_id => usec_instr_rec.instructor_id,
x_mode => 'R' );
igs_ps_uso_facility_pkg.insert_row (
x_rowid => lv_rowid,
x_uso_facility_id => l_uso_facility_id,
x_unit_section_occurrence_id => l_usec_id,
x_facility_code => usec_fac_rec.facility_code,
x_mode => 'R' );
SELECT unit_cd,version_number,cal_type,ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=p_new_uoo_id;
SELECT waitlist_allowed
FROM igs_ps_unit_ofr_pat
WHERE unit_cd=cp_unit_cd
AND version_number=cp_version_number
AND cal_type=cp_cal_type
AND ci_sequence_number=cp_ci_sequence_number
AND delete_flag = 'N';
SELECT unit_section_limit_waitlist_id ,waitlist_allowed
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_uoo_id
AND ROWNUM = 1 ;
igs_ps_usec_lim_wlst_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_section_limit_wlst_id => l_usec_lim_id,
x_uoo_id => p_new_uoo_id,
x_enrollment_expected => wlst_limit_rec.enrollment_expected ,
x_enrollment_minimum => wlst_limit_rec.enrollment_minimum ,
x_enrollment_maximum => wlst_limit_rec.enrollment_maximum ,
x_advance_maximum => wlst_limit_rec.advance_maximum,
x_override_enrollment_max => wlst_limit_rec.override_enrollment_max,
x_waitlist_allowed => wlst_limit_rec.waitlist_allowed ,
x_max_students_per_waitlist => wlst_limit_rec.max_students_per_waitlist,
x_max_auditors_allowed => wlst_limit_rec.max_auditors_allowed,
x_mode => 'R'
);
SELECT unit_sec_waitlist_priority_id
FROM igs_ps_usec_wlst_pri
WHERE uoo_id = cp_uoo_id
AND priority_number = cp_priority_number
AND priority_value = cp_priority_value
AND ROWNUM = 1 ;
igs_ps_usec_wlst_pri_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_sec_wlst_priority_id => l_usec_pri_id,
x_priority_number => wlst_pri_rec.priority_number ,
x_priority_value => wlst_pri_rec.priority_value ,
x_uoo_id => p_new_uoo_Id,
x_mode => 'R'
);
SELECT 'X'
FROM igs_ps_usec_wlst_prf
WHERE unit_sec_waitlist_priority_id = cp_unit_sec_wlst_priority_id
AND preference_code = cp_preference_code
AND preference_version = cp_preference_version
AND ROWNUM = 1 ;
igs_ps_usec_wlst_prf_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_sec_waitlist_pref_id => l_usec_prf_id,
x_unit_sec_wlst_priority_id => NVL(l_usec_pri_id,l_cur_wlst_pri_new.unit_sec_waitlist_priority_id),
x_preference_order => wlst_prf_rec.preference_order ,
x_preference_code => wlst_prf_rec.preference_code ,
x_preference_version => wlst_prf_rec.preference_version ,
x_mode => 'R');
SELECT 'X'
FROM igs_ps_usec_cps
WHERE uoo_id = cp_uoo_id
AND ROWNUM = 1;
igs_ps_usec_cps_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_sec_credit_points_id => l_usec_cps_id,
x_uoo_id => p_new_uoo_id,
x_minimum_credit_points => usec_cps_rec.minimum_credit_points ,
x_maximum_credit_points => usec_cps_rec.maximum_credit_points ,
x_variable_increment => usec_cps_rec.variable_increment ,
x_lecture_credit_points => usec_cps_rec.lecture_credit_points ,
x_lab_credit_points => usec_cps_rec.lab_credit_points ,
x_other_credit_points => usec_cps_rec.other_credit_points ,
x_clock_hours => usec_cps_rec.clock_hours ,
x_work_load_cp_lecture => usec_cps_rec.work_load_cp_lecture ,
x_work_load_cp_lab => usec_cps_rec.work_load_cp_lab ,
x_continuing_education_units => usec_cps_rec.continuing_education_units ,
x_work_load_other => usec_cps_rec.work_load_other,
x_contact_hrs_lecture => usec_cps_rec.contact_hrs_lecture ,
x_contact_hrs_lab => usec_cps_rec.contact_hrs_lab,
x_contact_hrs_other => usec_cps_rec.contact_hrs_other,
x_non_schd_required_hrs => usec_cps_rec.non_schd_required_hrs,
x_exclude_from_max_cp_limit => usec_cps_rec.exclude_from_max_cp_limit,
x_mode => 'R',
x_claimable_hours => usec_cps_rec.claimable_hours,
x_achievable_credit_points => usec_cps_rec.achievable_credit_points,
x_enrolled_credit_points => usec_cps_rec.enrolled_credit_points,
x_billing_credit_points => usec_cps_rec.billing_credit_points,
x_billing_hrs => usec_cps_rec.billing_hrs
);
SELECT 'X'
FROM igs_ps_usec_x_grpmem
WHERE uoo_id = cp_uoo_id
AND ROWNUM = 1;
igs_ps_usec_x_grp_pkg.insert_row (
x_rowid => lvGRp_Row_ID,
x_usec_x_listed_group_id => lnUsec_X_Grp_Id,
x_usec_x_listed_group_name => usec_x_grpmem_rec.usec_x_listed_group_name,
x_location_inheritance => usec_x_grpmem_rec.location_inheritance,
x_cal_type => gv_new_usec_rec.cal_type,
x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
x_max_enr_group => usec_x_grpmem_rec.max_enr_group,
x_max_ovr_group => usec_x_grpmem_rec.max_ovr_group,
x_mode => 'R' );
igs_ps_usec_x_grpmem_pkg.insert_row (
x_rowid => lv_GrpMemrowid,
x_usec_x_listed_group_mem_id => lnusec_x_listed_group_mem_id,
x_usec_x_listed_group_id => lnUsec_X_Grp_Id,
x_uoo_id => p_new_uoo_id,
x_parent => l_parent,
x_mode => 'R' );
SELECT 'X'
FROM igs_ps_usec_spnsrshp
WHERE uoo_id = cp_uoo_id
AND organization_code = cp_organization_code
AND ROWNUM = 1;
igs_ps_usec_spnsrshp_pkg.insert_row (
x_rowid => lv_rowid,
x_unit_section_sponsorship_id => l_usec_spn_id,
x_uoo_id => p_new_uoo_id,
x_organization_code => usec_spn_rec.organization_code ,
x_sponsorship_percentage => usec_spn_rec.sponsorship_percentage ,
x_mode => 'R'
);
SELECT 'X'
FROM igs_ps_usec_tch_resp
WHERE uoo_id = cp_uoo_id
AND instructor_id = cp_instructor_id
AND ROWNUM = 1;
SELECT 'X'
FROM igs_ps_usec_occurs_all a,igs_ps_uso_instrctrs b
WHERE a.UNIT_SECTION_OCCURRENCE_ID = b.UNIT_SECTION_OCCURRENCE_ID
AND a.uoo_id = cp_uoo_id
AND b.instructor_id= cp_ins_id;
igs_ps_usec_tch_resp_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_section_teach_resp_id => l_usec_tchr_id,
x_instructor_id => usec_tchr_rec.instructor_id ,
x_confirmed_flag => usec_tchr_rec.confirmed_flag ,
x_percentage_allocation => usec_tchr_rec.percentage_allocation ,
x_instructional_load => usec_tchr_rec.instructional_load ,
x_lead_instructor_flag => usec_tchr_rec.lead_instructor_flag ,
x_uoo_id => p_new_uoo_id,
x_instructional_load_lab => usec_tchr_rec.instructional_load_lab,
x_instructional_load_lecture => usec_tchr_rec.instructional_load_lecture,
x_mode => 'R'
);
SELECT 'X'
FROM igs_ps_usec_as
WHERE uoo_id = cp_uoo_id
AND ROWNUM = 1;
igs_ps_usec_as_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_section_assessment_id => l_usec_ass_id,
x_uoo_id => p_new_uoo_id,
x_final_exam_date => usec_as_rec.final_exam_date ,
x_exam_start_time => usec_as_rec.exam_start_time ,
x_exam_end_time => usec_as_rec.exam_end_time ,
x_location_cd => usec_as_rec.location_cd ,
x_building_code => usec_as_rec.building_code ,
x_room_code => usec_as_rec.room_code ,
x_mode => 'R'
);
SELECT us_ass_item_group_id
FROM igs_as_us_ai_group
WHERE uoo_id = cp_uoo_id
AND group_name = cp_group_name;
igs_as_us_ai_group_pkg.insert_row (
x_rowid => l_rowid,
x_us_ass_item_group_id => l_us_ass_item_group_id,
x_uoo_id => p_new_uoo_id,
x_group_name => usec_unitassgrp_rec.group_name,
x_midterm_formula_code => usec_unitassgrp_rec.midterm_formula_code,
x_midterm_formula_qty => usec_unitassgrp_rec.midterm_formula_qty,
x_midterm_weight_qty => usec_unitassgrp_rec.midterm_weight_qty,
x_final_formula_code => usec_unitassgrp_rec.final_formula_code,
x_final_formula_qty => usec_unitassgrp_rec.final_formula_qty,
x_final_weight_qty => usec_unitassgrp_rec.final_weight_qty,
x_mode => 'R'
);
igs_ps_unitass_item_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_section_ass_item_id => l_usec_assitem_id,
x_uoo_id => p_new_uoo_id,
x_ass_id => usec_unitass_rec.ass_id ,
x_sequence_number => l_sequence_number,
x_ci_start_dt => l_ci_start_dt,
x_ci_end_dt => l_ci_end_dt,
x_due_dt => usec_unitass_rec.due_dt ,
x_reference => usec_unitass_rec.reference ,
x_dflt_item_ind => usec_unitass_rec.dflt_item_ind ,
x_logical_delete_dt => usec_unitass_rec.logical_delete_dt ,
x_action_dt => usec_unitass_rec.action_dt ,
x_exam_cal_type => usec_unitass_rec.exam_cal_type ,
x_exam_ci_sequence_number => usec_unitass_rec.exam_ci_sequence_number ,
x_mode => 'R' ,
x_grading_schema_cd => usec_unitass_rec.grading_schema_cd,
x_gs_version_number => usec_unitass_rec.gs_version_number,
x_release_date => usec_unitass_rec.release_date,
x_description => usec_unitass_rec.description,
x_us_ass_item_group_id => l_us_ass_item_group_id,
x_midterm_mandatory_type_code => usec_unitass_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => usec_unitass_rec.midterm_weight_qty,
x_final_mandatory_type_code => usec_unitass_rec.final_mandatory_type_code,
x_final_weight_qty => usec_unitass_rec.final_weight_qty
);
SELECT *
FROM igs_ps_usec_ref
WHERE uoo_id = p_uoo_id;
SELECT 'X'
FROM igs_ps_usec_ref_cd
WHERE unit_section_reference_id = cp_unit_section_reference_id
AND reference_code_type = cp_reference_code_type
AND reference_code = cp_reference_code
AND ROWNUM = 1;
igs_ps_usec_ref_cd_pkg.insert_row (
x_rowid => lv_rowid,
x_unit_section_reference_cd_id => l_unit_section_reference_cd_id,
x_unit_section_reference_id => l_usec_ref.unit_section_reference_id,
x_mode => 'R',
x_reference_code_type => usec_refcd_rec.reference_code_type,
x_reference_code => usec_refcd_rec.reference_code,
x_reference_code_desc => usec_refcd_rec.reference_code_desc
);
SELECT 'X'
FROM igs_ps_us_req_ref_cd
WHERE unit_section_reference_id = cp_unit_section_reference_id
AND reference_cd_type = cp_reference_cd_type
AND reference_code = cp_reference_code
AND ROWNUM = 1;
igs_ps_us_req_ref_cd_pkg.insert_row (
x_rowid => lv_rowid,
x_unit_section_req_ref_cd_id => l_unit_section_req_ref_cd_id,
x_unit_section_reference_id => l_usec_ref.unit_section_reference_id,
x_reference_cd_type => us_req_refcd_rec.reference_cd_type,
x_mode => 'R',
x_reference_code => us_req_refcd_rec.reference_code,
x_reference_code_desc => us_req_refcd_rec.reference_code_desc
);
SELECT 'X'
FROM igs_ps_usec_grd_schm
WHERE uoo_id = cp_uoo_id
AND grading_schema_code = cp_grading_schema_code
AND grd_schm_version_number = cp_grd_schm_version_number
AND ROWNUM = 1;
SELECT grading_schema_code,grd_schm_version_number
FROM igs_ps_usec_grd_schm
WHERE uoo_id = cp_uoo_id
AND default_flag = 'Y';
igs_ps_usec_grd_schm_pkg.insert_row(
x_rowid => lv_rowid,
x_unit_section_grad_schema_id => l_usec_grdsch_id,
x_uoo_id => p_new_uoo_id,
x_grading_schema_code => usec_grdsch_rec.grading_schema_code ,
x_grd_schm_version_number => usec_grdsch_rec.grd_schm_version_number ,
x_default_flag => usec_grdsch_rec.default_flag ,
x_mode => 'R'
);
SELECT 'X'
FROM igs_ps_unt_ofr_opt_n
WHERE uoo_id = cp_uoo_id
AND crs_note_type = cp_crs_note_type
AND ROWNUM = 1;
SELECT IGS_GE_NOTE_RF_NUM_S.NEXTVAL INTO lnnote_ref_number FROM dual;
igs_ge_note_pkg.insert_row(
x_rowid => lv_gerowid,
x_reference_number => lnnote_ref_number ,
x_s_note_format_type => c_unt_ofr_opt_n_rec.s_note_format_type,
x_note_text => c_unt_ofr_opt_n_rec.Note_text,
x_mode => 'R' );
igs_ps_unt_ofr_opt_n_pkg.insert_row(
x_rowid => lv_usrowid,
x_unit_cd => gv_new_usec_rec.unit_cd,
x_version_number => gv_new_usec_rec.version_number,
x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
x_unit_class => gv_new_usec_rec.unit_class,
x_reference_number => lnnote_ref_number,
x_location_cd => gv_new_usec_rec.location_cd,
x_cal_type => gv_new_usec_rec.cal_type,
x_uoo_id => gv_new_usec_rec.uoo_id,
x_crs_note_type => c_unt_ofr_opt_n_rec.crs_note_type,
x_mode =>'R' );
SELECT 'X'
FROM igs_ps_usec_ru
WHERE uoo_id = cp_uoo_id
AND s_rule_call_cd = cp_s_rule_call_cd
AND ROWNUM = 1;
p_group => usec_pre_co_req_rule_rec.select_group,
p_return_type => usec_pre_co_req_rule_rec.s_return_type,
p_rule_description => usec_pre_co_req_rule_rec.rule_description,
p_rule_processed => usec_pre_co_req_rule_rec.rule_text,
p_rule_unprocessed => lv_rule_unprocessed,
p_generate_rule => TRUE,
p_rule_number => ln_rule_number,
p_lov_number => ln_lov_number );
igs_ps_usec_ru_pkg.insert_row (
x_rowid => lv_rowid,
x_usecru_id => l_usecru_id,
x_uoo_id => p_new_uoo_id,
x_s_rule_call_cd => usec_pre_co_req_rule_rec.s_rule_call_cd,
x_rul_sequence_number => ln_rule_number,
x_mode => 'R' );
SELECT 'X'
FROM igs_ps_usec_category
WHERE uoo_id = cp_uoo_id
AND unit_cat = cp_unit_cat
AND ROWNUM = 1;
igs_ps_usec_category_pkg.insert_row (
x_rowid => lv_rowid,
x_usec_cat_id => l_usec_cat_id,
x_uoo_id => p_new_uoo_id,
x_unit_cat => usec_cat_rec.unit_cat,
x_mode => 'R' );
SELECT 'X'
FROM igs_ps_us_unsched_cl
WHERE uoo_id = cp_uoo_id
AND activity_type_id = cp_activity_type_id
AND location_cd = cp_location_cd
AND building_id = cp_building_id
AND room_id = cp_room_id
AND ROWNUM = 1;
igs_ps_us_unsched_cl_pkg.insert_row (
x_rowid => lv_rowid,
x_us_unscheduled_cl_id => l_us_unscheduled_cl_id,
x_uoo_id => p_new_uoo_id,
x_activity_type_id => usec_plushr_rec.activity_type_id,
x_location_cd => usec_plushr_rec.location_cd,
x_building_id => usec_plushr_rec.building_id,
x_room_id => usec_plushr_rec.room_id,
x_number_of_students => usec_plushr_rec.number_of_students,
x_hours_per_student => usec_plushr_rec.hours_per_student,
x_hours_per_faculty => usec_plushr_rec.hours_per_faculty,
x_instructor_id => usec_plushr_rec.instructor_id,
x_mode => 'R' );
SELECT 'X'
FROM igs_ps_tch_resp_ovrd
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND location_cd = cp_location_cd
AND unit_class = cp_unit_class
AND org_unit_cd = cp_org_unit_cd
AND ou_start_dt = cp_ou_start_dt
AND ROWNUM = 1;
SELECT 'x'
FROM igs_or_inst_org_base_v ou,
igs_or_status os
WHERE ou.party_number = cp_org_unit_cd
AND ou.start_dt = cp_start_dt
AND os.org_status = ou.org_status
AND os.s_org_status = cst_active;
igs_ps_tch_resp_ovrd_pkg.insert_row(
x_rowid => l_rowid,
x_unit_cd => gv_new_usec_rec.unit_cd,
x_version_number => gv_new_usec_rec.version_number,
x_cal_type => gv_new_usec_rec.cal_type,
x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
x_location_cd => gv_new_usec_rec.location_cd,
x_unit_class => gv_new_usec_rec.unit_class,
x_ou_start_dt => usec_tro_rec.ou_start_dt,
x_org_unit_cd => usec_tro_rec.org_unit_cd,
x_uoo_id => p_new_uoo_id,
x_percentage => usec_tro_rec.percentage,
x_mode => 'R',
x_org_id => l_org_id);
SELECT 'x'
FROM igs_ps_usec_sp_fees
WHERE uoo_id = cp_n_uoo_id
AND fee_type = cp_c_fee_type
AND ROWNUM = 1;
SELECT ci.cal_type,ci.sequence_number
FROM igs_fi_fee_type ft,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE ft.s_fee_type = 'SPECIAL'
AND ft.closed_ind = 'N'
AND ft.fee_type = ftci.fee_type
AND ft.fee_type = cp_source_fee_type
AND ftci.fee_cal_type = ci.cal_type
AND ftci.fee_ci_sequence_number = ci.sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'FEE'
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE' ;
igs_ps_usec_sp_fees_pkg.insert_row (
x_rowid => lv_rowid,
x_usec_sp_fees_id => l_usec_sp_fees_id,
x_uoo_id => p_new_uoo_id,
x_fee_type => usec_spl_fees_rec.fee_type,
x_sp_fee_amt => usec_spl_fees_rec.sp_fee_amt,
x_closed_flag => usec_spl_fees_rec.closed_flag,
x_mode => 'R' );
SELECT non_std_usec_rtn_id
FROM igs_ps_nsus_rtn
WHERE uoo_id = cp_uoo_id
AND fee_type = cp_fee_type;
SELECT non_std_usec_rtn_id
FROM igs_ps_nsus_rtn
WHERE uoo_id = cp_uoo_id
AND definition_code ='UNIT_SECTION';
igs_ps_nsus_rtn_pkg.insert_row(
x_rowid => lv_rowid,
x_non_std_usec_rtn_id => l_non_std_usec_rtn_id,
x_uoo_id => p_new_uoo_id,
x_fee_type => c_rtn_us_rec.fee_type,
x_definition_code => c_rtn_us_rec.definition_code,
x_formula_method => c_rtn_us_rec.formula_method,
x_round_method => c_rtn_us_rec.round_method,
x_incl_wkend_duration_flag => c_rtn_us_rec.incl_wkend_duration_flag,
x_mode => 'R'
);
SELECT 'X'
FROM igs_ps_nsus_rtn_dtl
WHERE non_std_usec_rtn_id = cp_non_std_usec_rtn_id
AND offset_value = cp_offset_value;
igs_ps_nsus_rtn_dtl_pkg.insert_row(
x_rowid => lv_rowid,
x_non_std_usec_rtn_dtl_id => l_non_std_usec_rtn_dtl_id,
x_non_std_usec_rtn_id => l_non_std_usec_rtn_id,
x_offset_value => c_rtn_us_dtl_rec.offset_value,
x_retention_percent => c_rtn_us_dtl_rec.retention_percent,
x_retention_amount => c_rtn_us_dtl_rec.retention_amount,
x_offset_date => l_offset_date,
x_override_date_flag => 'N', -- calculating the dates while rolling over. So if in the source if it is overriden here it is calculated, to keep the data in the correct calendar.
x_mode => 'R'
);
teachCalendar_tbl.DELETE;
|| Purpose:Updates unit section status to 'Not Offered' if the calendar status is changes to INACTIVE
|| for those unit section of the calendar instance which are not having any enrollment
|| activity,also changes the status to OPEN when calendar status is updated from INACTIVE
|| to ACTIVE.This procedure returns status(p_b_ret_status), if this is false then p_c_message
|| _name will contain the error message name which needs to be shown to the calling env and
|| process needs to be stopped.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| sarakshi 21-oct-2003 Enh#3052452,used function igs_ps_gen_003.enrollment_for_uoo_check for checking the
|| existance of record in igs_en_su_attempt rather than explicitly coding a cursor locally.
|| sarakshi 18-sep-2003 Enh#3052452, added new parameters relation_type,sup_uoo_id,default_enroll_flag to the
|| call of igs_ps_unit_ofr_opt_pkg.update_row .
|| vvutukur 04-Aug-2003 Enh#3045069.PSP Enh Build.Modified the calls to igs_ps_unit_ofr_opt_pkg.update_row to add
|| new parameter not_multiple_section_flag.
*/
CURSOR c_uoo_id IS
SELECT usec.* ,usec.rowid
FROM igs_ps_unit_ofr_opt_all usec
WHERE cal_type=p_c_cal_type
AND ci_sequence_number=p_n_ci_sequence_number;
igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_uoo_id.rowid,
x_unit_cd =>l_uoo_id.unit_cd,
x_version_number =>l_uoo_id.version_number,
x_cal_type =>l_uoo_id.cal_type,
x_ci_sequence_number =>l_uoo_id.ci_sequence_number,
x_location_cd =>l_uoo_id.location_cd,
x_unit_class =>l_uoo_id.unit_class,
x_uoo_id =>l_uoo_id.uoo_id,
x_ivrs_available_ind =>l_uoo_id.ivrs_available_ind,
x_call_number =>l_uoo_id.call_number,
x_unit_section_status => 'NOT_OFFERED',
x_unit_section_start_date =>l_uoo_id.unit_section_start_date,
x_unit_section_end_date =>l_uoo_id.unit_section_end_date,
x_enrollment_actual =>l_uoo_id.enrollment_actual,
x_waitlist_actual =>l_uoo_id.waitlist_actual,
x_offered_ind =>l_uoo_id.offered_ind,
x_state_financial_aid =>l_uoo_id.state_financial_aid,
x_grading_schema_prcdnce_ind =>l_uoo_id.grading_schema_prcdnce_ind,
x_federal_financial_aid =>l_uoo_id.federal_financial_aid,
x_unit_quota =>l_uoo_id.unit_quota,
x_unit_quota_reserved_places =>l_uoo_id.unit_quota_reserved_places,
x_institutional_financial_aid =>l_uoo_id.institutional_financial_aid,
x_unit_contact =>l_uoo_id.unit_contact,
x_grading_schema_cd =>l_uoo_id.grading_schema_cd,
x_gs_version_number =>l_uoo_id.gs_version_number,
x_owner_org_unit_cd =>l_uoo_id.owner_org_unit_cd,
x_attendance_required_ind =>l_uoo_id.attendance_required_ind,
x_reserved_seating_allowed =>l_uoo_id.reserved_seating_allowed,
x_special_permission_ind =>l_uoo_id.special_permission_ind,
x_ss_display_ind =>l_uoo_id.ss_display_ind,
x_mode =>'R',
x_ss_enrol_ind =>l_uoo_id.ss_enrol_ind,
x_dir_enrollment =>l_uoo_id.dir_enrollment,
x_enr_from_wlst =>l_uoo_id.enr_from_wlst,
x_inq_not_wlst =>l_uoo_id.inq_not_wlst,
x_rev_account_cd =>l_uoo_id.rev_account_cd,
x_anon_unit_grading_ind =>l_uoo_id.anon_unit_grading_ind,
x_anon_assess_grading_ind =>l_uoo_id.anon_assess_grading_ind,
x_non_std_usec_ind =>l_uoo_id.non_std_usec_ind,
x_auditable_ind =>l_uoo_id.auditable_ind,
x_audit_permission_ind =>l_uoo_id.audit_permission_ind,
x_not_multiple_section_flag =>l_uoo_id.not_multiple_section_flag,
x_sup_uoo_id =>l_uoo_id.sup_uoo_id,
x_relation_type =>l_uoo_id.relation_type,
x_default_enroll_flag =>l_uoo_id.default_enroll_flag ,
x_abort_flag =>l_uoo_id.abort_flag
);
igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_uoo_id.rowid,
x_unit_cd =>l_uoo_id.unit_cd,
x_version_number =>l_uoo_id.version_number,
x_cal_type =>l_uoo_id.cal_type,
x_ci_sequence_number =>l_uoo_id.ci_sequence_number,
x_location_cd =>l_uoo_id.location_cd,
x_unit_class =>l_uoo_id.unit_class,
x_uoo_id =>l_uoo_id.uoo_id,
x_ivrs_available_ind =>l_uoo_id.ivrs_available_ind,
x_call_number =>l_uoo_id.call_number,
x_unit_section_status => 'OPEN',
x_unit_section_start_date =>l_uoo_id.unit_section_start_date,
x_unit_section_end_date =>l_uoo_id.unit_section_end_date,
x_enrollment_actual =>l_uoo_id.enrollment_actual,
x_waitlist_actual =>l_uoo_id.waitlist_actual,
x_offered_ind =>l_uoo_id.offered_ind,
x_state_financial_aid =>l_uoo_id.state_financial_aid,
x_grading_schema_prcdnce_ind =>l_uoo_id.grading_schema_prcdnce_ind,
x_federal_financial_aid =>l_uoo_id.federal_financial_aid,
x_unit_quota =>l_uoo_id.unit_quota,
x_unit_quota_reserved_places =>l_uoo_id.unit_quota_reserved_places,
x_institutional_financial_aid =>l_uoo_id.institutional_financial_aid,
x_unit_contact =>l_uoo_id.unit_contact,
x_grading_schema_cd =>l_uoo_id.grading_schema_cd,
x_gs_version_number =>l_uoo_id.gs_version_number,
x_owner_org_unit_cd =>l_uoo_id.owner_org_unit_cd,
x_attendance_required_ind =>l_uoo_id.attendance_required_ind,
x_reserved_seating_allowed =>l_uoo_id.reserved_seating_allowed,
x_special_permission_ind =>l_uoo_id.special_permission_ind,
x_ss_display_ind =>l_uoo_id.ss_display_ind,
x_mode =>'R',
x_ss_enrol_ind =>l_uoo_id.ss_enrol_ind,
x_dir_enrollment =>l_uoo_id.dir_enrollment,
x_enr_from_wlst =>l_uoo_id.enr_from_wlst,
x_inq_not_wlst =>l_uoo_id.inq_not_wlst,
x_rev_account_cd =>l_uoo_id.rev_account_cd,
x_anon_unit_grading_ind =>l_uoo_id.anon_unit_grading_ind,
x_anon_assess_grading_ind =>l_uoo_id.anon_assess_grading_ind,
x_non_std_usec_ind =>l_uoo_id.non_std_usec_ind,
x_auditable_ind =>l_uoo_id.auditable_ind,
x_audit_permission_ind =>l_uoo_id.audit_permission_ind,
x_not_multiple_section_flag =>l_uoo_id.not_multiple_section_flag,
x_sup_uoo_id =>l_uoo_id.sup_uoo_id,
x_relation_type =>l_uoo_id.relation_type,
x_default_enroll_flag =>l_uoo_id.default_enroll_flag,
x_abort_flag =>l_uoo_id.abort_flag
);
SELECT calendar_cat
FROM igs_ps_exp_wl;
SELECT ecw.expected_wl_num
FROM igs_ps_emp_cats_wl ecw
WHERE ecw.emp_cat_code = cp_c_emp_cat_code AND
cal_cat_code = cp_n_cal_cat_code ORDER BY ecw.last_update_date desc;
SELECT NVL(fow.new_exp_wl,0)
FROM igs_ps_fac_wl fw,igs_ps_fac_ovr_wl fow
WHERE fw.person_id = cp_n_person_id AND
fow.fac_wl_id = fw.fac_wl_id AND
fw.cal_type = cp_c_cal_type AND
fw.ci_sequence_number=cp_n_cal_seq_num AND
SYSDATE BETWEEN fow.start_date AND NVL(fow.end_date,SYSDATE);
SELECT NVL(SUM(NVL(fat.default_wl,0)),0)
FROM igs_ps_fac_wl fw,igs_ps_fac_asg_task fat
WHERE fw.person_id = cp_n_person_id AND
fat.fac_wl_id = fw.fac_wl_id AND
fw.cal_type = cp_c_cal_type AND
fw.ci_sequence_number=cp_n_cal_seq_num AND
fat.confirmed_ind = 'Y';
SELECT NVL(utr.INSTRUCTIONAL_LOAD_LAB,0) +
NVL(utr.INSTRUCTIONAL_LOAD_LECTURE,0) +
NVL(utr.INSTRUCTIONAL_LOAD,0) teach_wl
FROM igs_ps_usec_tch_resp utr,igs_ps_unit_ofr_opt_all opt
WHERE utr.instructor_id = cp_n_person_id AND
utr.uoo_id = opt.uoo_id AND
opt.cal_type = cp_c_cal_type AND
utr.confirmed_flag = 'Y' AND
opt.ci_sequence_number = cp_n_cal_seq_num AND
opt.unit_section_status NOT IN ('CANCELLED','NOT_OFFERED');
OPEN c_get_tch_cal FOR 'SELECT teach_cal_type,teach_ci_sequence_number'||
' FROM igs_ca_load_to_teach_v WHERE load_cal_type = :1 AND'||
' load_ci_sequence_number = :2 ' USING p_c_cal_type,p_n_cal_seq_num;
OPEN c_get_tch_cal FOR 'SELECT cir.sub_cal_type,cir.sub_ci_sequence_number'||
' FROM igs_ca_inst_rel cir, igs_ca_type ct'||
' WHERE cir.sub_cal_type = ct.cal_type AND'||
' ct.s_cal_cat = :1 AND'||
' sup_cal_type = :2 AND'||
' sup_ci_sequence_number = :3 ' USING l_c_s_cal_cat,p_c_cal_type,p_n_cal_seq_num;
SELECT calendar_cat
FROM igs_ps_exp_wl;
OPEN c_get_cal FOR 'SELECT load_cal_type,load_ci_sequence_number'||
' FROM igs_ca_teach_to_load_v WHERE teach_cal_type = :1 AND'||
' teach_ci_sequence_number = :2 ' USING p_c_cal_type,p_n_cal_seq_num;
OPEN c_get_cal FOR 'SELECT cir.sup_cal_type,cir.sup_ci_sequence_number'||
' FROM igs_ca_inst_rel cir, igs_ca_type ct'||
' WHERE cir.sup_cal_type = ct.cal_type AND'||
' ct.s_cal_cat = :1 AND'||
' sub_cal_type = :2 AND'||
' sub_ci_sequence_number = :3 ' USING l_c_s_cal_cat,p_c_cal_type,p_n_cal_seq_num;