[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT IGS_GE_NOTE_RF_NUM_S.nextval
FROM dual;
SELECT IGS_GE_NOTE.s_note_format_type,
IGS_GE_NOTE.note_text
FROM IGS_GE_NOTE IGS_GE_NOTE
WHERE IGS_GE_NOTE.reference_number = p_existing_ref_number;
IGS_GE_NOTE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_REFERENCE_NUMBER => v_note_seq,
X_S_NOTE_FORMAT_TYPE => v_note_rec.s_note_format_type,
X_NOTE_TEXT => v_note_rec.note_text,
X_MODE => 'R');
SELECT usn.reference_number,
usn.crs_note_type
FROM IGS_EN_UNIT_SET_NOTE usn
WHERE usn.unit_set_cd = p_old_unit_set_cd AND
usn.version_number = p_old_version_number AND
EXISTS (
SELECT 'x'
FROM IGS_GE_NOTE nte
WHERE nte.reference_number =
usn.reference_number AND
nte.note_text IS NOT NULL);
IGS_EN_UNIT_SET_NOTE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_SET_CD => p_new_unit_set_cd,
X_VERSION_NUMBER => p_new_version_number,
X_REFERENCE_NUMBER => v_new_ref_no,
X_CRS_NOTE_TYPE => v_usn_rec.crs_note_type,
X_MODE => 'R');
SELECT usct.course_type
FROM IGS_EN_UNITSETPSTYPE usct
WHERE usct.unit_set_cd = p_old_unit_set_cd AND
usct.version_number = p_old_version_number;
IGS_EN_UNITSETPSTYPE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_SET_CD => p_new_unit_set_cd,
X_VERSION_NUMBER => p_new_version_number,
X_COURSE_TYPE => v_usct_rec.course_type,
X_MODE => 'R');
SELECT cous.course_cd,
cous.crv_version_number,
cous.cal_type,
cous.override_title,
cous.only_as_sub_ind,
cous.show_on_official_ntfctn_ind
FROM IGS_PS_OFR_UNIT_SET cous
WHERE cous.unit_set_cd = p_old_unit_set_cd AND
cous.us_version_number = p_old_version_number;
SELECT cousr.course_cd,
cousr.crv_version_number,
cousr.cal_type,
cousr.sub_unit_set_cd,
cousr.sub_us_version_number
FROM IGS_PS_OF_UNT_SET_RL cousr
WHERE cousr.sup_unit_set_cd = p_old_unit_set_cd AND
cousr.sup_us_version_number = p_old_version_number AND
cousr.course_cd = cp_cous_course_cd AND
cousr.cal_type = cp_cous_cal_type AND
cousr.crv_version_number = cp_cous_crv_ver_num;
SELECT cousr.course_cd,
cousr.crv_version_number,
cousr.cal_type,
cousr.sup_unit_set_cd,
cousr.sup_us_version_number
FROM IGS_PS_OF_UNT_SET_RL cousr
WHERE cousr.sub_unit_set_cd = p_old_unit_set_cd AND
cousr.sub_us_version_number = p_old_version_number AND
cousr.course_cd = cp_cous_course_cd AND
cousr.cal_type = cp_cous_cal_type AND
cousr.crv_version_number = cp_cous_crv_ver_num;
SELECT coous.course_cd,
coous.crv_version_number,
coous.cal_type,
coous.location_cd,
coous.attendance_mode,
coous.attendance_type,
coous.coo_id
FROM IGS_PS_OF_OPT_UNT_ST coous
WHERE coous.unit_set_cd = p_old_unit_set_cd AND
coous.us_version_number = p_old_version_number AND
coous.course_cd = cp_cous_course_cd AND
coous.crv_version_number = cp_cous_version_number AND
coous.cal_type = cp_cous_cal_type;
IGS_PS_OFR_UNIT_SET_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => v_cous_rec.course_cd,
X_CRV_VERSION_NUMBER => v_cous_rec.crv_version_number,
X_CAL_TYPE => v_cous_rec.cal_type,
X_UNIT_SET_CD => p_new_unit_set_cd,
X_US_VERSION_NUMBER => p_new_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 => v_cousr_sub_rec.course_cd,
X_CRV_VERSION_NUMBER => v_cousr_sub_rec.crv_version_number,
X_SUP_US_VERSION_NUMBER => v_cousr_sub_rec.sup_us_version_number,
X_SUB_UNIT_SET_CD => p_new_unit_set_cd,
X_SUP_UNIT_SET_CD => v_cousr_sub_rec.sup_unit_set_cd,
X_CAL_TYPE => v_cousr_sub_rec.cal_type,
X_SUB_US_VERSION_NUMBER => p_new_version_number,
X_MODE => 'R');
IGS_PS_OF_UNT_SET_RL_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => v_cousr_sup_rec.course_cd,
X_CRV_VERSION_NUMBER => v_cousr_sup_rec.crv_version_number,
X_SUP_US_VERSION_NUMBER => p_new_version_number,
X_SUB_UNIT_SET_CD => v_cousr_sup_rec.sub_unit_set_cd,
X_SUP_UNIT_SET_CD => p_new_unit_set_cd,
X_CAL_TYPE => v_cousr_sup_rec.cal_type,
X_SUB_US_VERSION_NUMBER => v_cousr_sup_rec.sub_us_version_number,
X_MODE => 'R');
IGS_PS_OF_OPT_UNT_ST_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => v_coous_rec.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 => v_coous_rec.crv_version_number,
X_ATTENDANCE_TYPE => v_coous_rec.attendance_type,
X_US_VERSION_NUMBER => p_new_version_number,
X_UNIT_SET_CD => p_new_unit_set_cd,
X_COO_ID => v_coous_rec.coo_id,
X_MODE => 'R');
SELECT usr.s_rule_call_cd,
usr.rul_sequence_number
FROM IGS_EN_UNIT_SET_RULE usr
WHERE usr.unit_set_cd = p_old_unit_set_cd AND
usr.version_number = p_old_version_number;
IGS_EN_UNIT_SET_RULE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_SET_CD => p_new_unit_set_cd,
X_VERSION_NUMBER => p_new_version_number,
X_S_RULE_CALL_CD => v_usr_rec.s_rule_call_cd,
X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_number,
X_MODE => 'R');
SELECT cacus.course_cd,
cacus.crv_version_number,
cacus.cal_type,
cacus.location_cd,
cacus.attendance_mode,
cacus.attendance_type,
cacus.admission_cat
FROM IGS_PS_COO_AD_UNIT_S cacus
WHERE cacus.unit_set_cd = p_old_unit_set_cd AND
cacus.us_version_number = p_old_version_number;
IGS_PS_COO_AD_UNIT_S_PKG.Insert_Row(
X_ROWID => x_rowid,
X_COURSE_CD => v_cacus_rec.course_cd,
X_CRV_VERSION_NUMBER => v_cacus_rec.crv_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 => p_new_unit_set_cd,
X_US_VERSION_NUMBER => p_new_version_number,
X_MODE => 'R');
PROCEDURE update_usec_record (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
p_relation_type igs_ps_unit_ofr_opt.relation_type%TYPE,
p_sup_uoo_id igs_ps_unit_ofr_opt.sup_uoo_id%TYPE,
p_default_enroll_flag igs_ps_unit_ofr_opt.default_enroll_flag%TYPE) IS
/*----------------------------------------------------------------------------
|| Created By :sarakshi
|| Created On :17-oct-2003
|| Purpose :For updating the sup_uo_id and relation_type value of IGS_PS_UNIT_OFR_OPT_ALL from multiple places in this package
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| sarakshi 17-Nov-2005 Bug#4726940,changed the signature by adding p_default_enroll_flag
----------------------------------------------------------------------------*/
CURSOR cur_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;
igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_cur_usec.row_id,
x_unit_cd =>l_cur_usec.unit_cd,
x_version_number =>l_cur_usec.version_number,
x_cal_type =>l_cur_usec.cal_type,
x_ci_sequence_number =>l_cur_usec.ci_sequence_number,
x_location_cd =>l_cur_usec.location_cd,
x_unit_class =>l_cur_usec.unit_class,
x_uoo_id =>l_cur_usec.uoo_id,
x_ivrs_available_ind =>l_cur_usec.ivrs_available_ind,
x_call_number =>l_cur_usec.call_number,
x_unit_section_status =>l_cur_usec.unit_section_status,
x_unit_section_start_date =>l_cur_usec.unit_section_start_date,
x_unit_section_end_date =>l_cur_usec.unit_section_end_date,
x_enrollment_actual =>l_cur_usec.enrollment_actual,
x_waitlist_actual =>l_cur_usec.waitlist_actual,
x_offered_ind =>l_cur_usec.offered_ind,
x_state_financial_aid =>l_cur_usec.state_financial_aid,
x_grading_schema_prcdnce_ind =>l_cur_usec.grading_schema_prcdnce_ind,
x_federal_financial_aid =>l_cur_usec.federal_financial_aid,
x_unit_quota =>l_cur_usec.unit_quota,
x_unit_quota_reserved_places =>l_cur_usec.unit_quota_reserved_places,
x_institutional_financial_aid =>l_cur_usec.institutional_financial_aid,
x_grading_schema_cd =>l_cur_usec.grading_schema_cd,
x_gs_version_number =>l_cur_usec.gs_version_number,
x_unit_contact =>l_cur_usec.unit_contact,
x_mode =>'R',
x_ss_enrol_ind => l_cur_usec.ss_enrol_ind,
x_owner_org_unit_cd => l_cur_usec.owner_org_unit_cd,
x_attendance_required_ind => l_cur_usec.attendance_required_ind,
x_reserved_seating_allowed => l_cur_usec.reserved_seating_allowed,
x_ss_display_ind => l_cur_usec.ss_display_ind,
x_special_permission_ind => l_cur_usec.special_permission_ind,
x_rev_account_cd => l_cur_usec.rev_account_cd ,
x_anon_unit_grading_ind => l_cur_usec.anon_unit_grading_ind,
x_anon_assess_grading_ind => l_cur_usec.anon_assess_grading_ind ,
x_non_std_usec_ind => l_cur_usec.non_std_usec_ind,
x_auditable_ind => l_cur_usec.auditable_ind,
x_audit_permission_ind => l_cur_usec.audit_permission_ind,
x_not_multiple_section_flag => l_cur_usec.not_multiple_section_flag,
x_sup_uoo_id => p_sup_uoo_id,
x_relation_type => p_relation_type,
x_default_enroll_flag => NVL(p_default_enroll_flag,l_cur_usec.default_enroll_flag),
x_abort_flag => l_cur_usec.abort_flag
);
END update_usec_record;
SELECT *
FROM IGS_GE_NOTE
WHERE reference_number = p_existing_ref_number;
SELECT IGS_GE_NOTE_RF_NUM_S.nextval
INTO p_new_ref_number
FROM dual;
IGS_GE_NOTE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_REFERENCE_NUMBER => p_new_ref_number,
X_S_NOTE_FORMAT_TYPE => SGN_Rec.s_note_format_type,
X_NOTE_TEXT => SGN_Rec.Note_Text,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNIT_VER_NOTE uvn
WHERE uvn.unit_cd = p_unit_cd AND
uvn.version_number = p_version_number AND
EXISTS (
SELECT 'x'
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = uvn.reference_number AND
nte.note_text IS NOT NULL );
IGS_PS_UNIT_VER_NOTE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_REFERENCE_NUMBER => v_new_ref_number,
X_VERSION_NUMBER => p_new_version_number,
X_CRS_NOTE_TYPE => v_unit_ver_note_rec.crs_note_type,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNIT_OFR_NOTE uon
WHERE uon.unit_cd = p_unit_cd AND
uon.version_number = p_version_number AND
EXISTS (
SELECT 'x'
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = uon.reference_number AND
nte.note_text IS NOT NULL );
IGS_PS_UNIT_OFR_NOTE_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_unit_offer_note_rec.cal_type,
X_REFERENCE_NUMBER => v_new_ref_number,
X_CRS_NOTE_TYPE => v_unit_offer_note_rec.crs_note_type,
X_MODE => 'R');
SELECT *
FROM IGS_PS_TCH_RESP
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_TCH_RESP_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_OU_START_DT => v_teach_resp_rec.ou_start_dt,
X_ORG_UNIT_CD => v_teach_resp_rec.org_unit_cd,
X_PERCENTAGE => v_teach_resp_rec.percentage,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNIT_DSCP
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_UNIT_DSCP_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_DISCIPLINE_GROUP_CD => v_unit_discipline_rec.discipline_group_cd,
X_PERCENTAGE => v_unit_discipline_rec.percentage,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNIT_CATEGORY
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_UNIT_CATEGORY_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_UNIT_CAT => v_unit_cat_rec.unit_cat,
X_MODE => 'R',
X_ORG_ID => l_org_id);
SELECT *
FROM IGS_PS_UNIT_LVL
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_UNIT_LVL_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_UNIT_LEVEL => v_crs_unit_lvl_rec.unit_level,
X_WAM_WEIGHTING => v_crs_unit_lvl_rec.wam_weighting,
X_MODE => 'R',
X_ORG_ID => l_org_id,
X_COURSE_CD => v_crs_unit_lvl_rec.course_cd,
X_COURSE_VERSION_NUMBER => v_crs_unit_lvl_rec.course_version_number
);
SELECT *
FROM IGS_PS_UNIT_REF_CD
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_UNIT_REF_CD_PKG.Insert_Row(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_REFERENCE_CD_TYPE => v_unit_ref_cd_rec.reference_cd_type,
X_REFERENCE_CD => v_unit_ref_cd_rec.reference_cd,
X_DESCRIPTION => v_unit_ref_cd_rec.description,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNT_OFR_OPT_N uoon
WHERE uoon.uoo_id = p_exist_uoo_id AND
EXISTS (
SELECT 'x'
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = uoon.reference_number AND
nte.note_text IS NOT NULL );
IGS_PS_UNT_OFR_OPT_N_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CI_SEQUENCE_NUMBER => v_unit_offer_opt_note_rec.ci_sequence_number,
X_UNIT_CLASS => v_unit_offer_opt_note_rec.unit_class,
X_REFERENCE_NUMBER => v_new_ref_number,
X_LOCATION_CD => v_unit_offer_opt_note_rec.location_cd,
X_CAL_TYPE => v_unit_offer_opt_note_rec.cal_type,
X_UOO_ID => v_uoo_id,
X_CRS_NOTE_TYPE => v_unit_offer_opt_note_rec.crs_note_type,
X_MODE => 'R'
);
SELECT tro.location_cd,
tro.unit_class,
tro.org_unit_cd,
tro.ou_start_dt,
tro.percentage
FROM IGS_PS_TCH_RESP_OVRD tro
WHERE tro.unit_cd = p_unit_cd AND
tro.version_number = p_version_number AND
tro.cal_type = p_cal_type AND
tro.ci_sequence_number = p_ci_sequence_number AND
tro.location_cd = p_location_cd AND
tro.unit_class = p_unit_class;
IGS_PS_TCH_RESP_OVRD_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_LOCATION_CD => c_tro_rec.location_cd,
X_CI_SEQUENCE_NUMBER => p_ci_sequence_number,
X_CAL_TYPE => p_cal_type,
X_UNIT_CLASS => c_tro_rec.unit_class,
X_OU_START_DT => c_tro_rec.ou_start_dt,
X_ORG_UNIT_CD => c_tro_rec.org_unit_cd,
X_UOO_ID => p_new_uoo_id,
X_PERCENTAGE => c_tro_rec.percentage,
X_MODE => 'R',
X_ORG_ID => l_org_id);
|| sarakshi 17-Nov-2005 Bug#4726940, impact of change of signature of the update_usec_record
----------------------------------------------------------------------------*/
l_c_none VARCHAR2(10);
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE relation_type = l_c_subordinate
AND sup_uoo_id = p_old_uoo_id;
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE relation_type = l_c_superior
AND uoo_id = (SELECT sup_uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_old_uoo_id
);
SELECT uoo.*
FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv, igs_ps_unit_stat us
WHERE uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = cp_ci_sequence_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class
AND uoo.unit_cd = cp_unit_cd
AND uoo.version_number > cp_version_number
AND uoo.unit_cd=uv.unit_cd
AND uoo.version_number=uv.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status IN (l_c_active,l_c_planned)
AND uoo.relation_type = l_c_none
AND uoo_id NOT IN (SELECT uoo_id FROM igs_en_su_attempt)
ORDER BY uoo.unit_cd,uoo.version_number ASC;
SELECT uoo.*
FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv, igs_ps_unit_stat us
WHERE uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = cp_ci_sequence_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class
AND uoo.unit_cd = cp_unit_cd
AND uoo.version_number > cp_version_number
AND uoo.unit_cd=uv.unit_cd
AND uoo.version_number=uv.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status IN (l_c_active,l_c_planned)
AND uoo_id NOT IN (SELECT uoo_id FROM igs_en_su_attempt)
AND uoo.relation_type IN (l_c_superior,l_c_none)
ORDER BY uoo.unit_cd,uoo.version_number ASC;
update_usec_record (p_uoo_id => l_c_new_sub.uoo_id,
p_relation_type => l_c_subordinate,
p_sup_uoo_id => p_new_uoo_id,
p_default_enroll_flag => l_old_sub_rec.default_enroll_flag);
update_usec_record (p_uoo_id => p_new_uoo_id,
p_relation_type => l_c_superior,
p_sup_uoo_id => NULL,
p_default_enroll_flag => NULL);
update_usec_record (p_uoo_id => l_c_new_sup.uoo_id,
p_relation_type => l_c_superior,
p_sup_uoo_id => NULL ,
p_default_enroll_flag => NULL);
update_usec_record (p_uoo_id => p_new_uoo_id,
p_relation_type => l_c_subordinate,
p_sup_uoo_id => l_c_new_sup.uoo_id,
p_default_enroll_flag => NULL );
sarakshi 31-AUG-2004 Bug#3864738,passed unit_section_status as OPEN in the insert row call of IGS_PS_UNIT_OFR_OPT
sarakshi 13-Apr-2004 Bug#3555871, removed the logic of getting the call number for AUTO profile option.
sarakshi 17-oct-2003 Enh#3168650,Added call to the procedure crspl_upd_usec_relation
sarakshi 23-sep-2003 Enh#3052452,Added column sup_uoo_id,relation_type,default_enroll_flag to the call of igs_ps_unit_ofr_opt_pkg.insert_row
vvutukur 05-aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.insert_row to added new column not_multiple_section_flag.
sarakshi 18-Apr-2003 Bug#2910695,passed actual_enrollment and actual_waitlist null in the table IGS_PS_OFR_OPT_ALL
sarakshi 05-Mar-2003 bug#2768783,added logic for checking/generating the call number
*/
v_unit_offer_opt_rec IGS_PS_UNIT_OFR_OPT%ROWTYPE;
SELECT *
FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number AND
cal_type = p_cal_type AND
ci_sequence_number = p_ci_sequence_number;
SELECT MAX(gs.version_number)
FROM IGS_AS_GRD_SCHEMA gs
WHERE gs.grading_schema_cd = cp_gs_cd;
SELECT IGS_PS_UNIT_OFR_OPT_UOO_ID_S.nextval
INTO v_new_uoo_id
FROM dual;
IGS_PS_UNIT_OFR_OPT_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => P_new_version_number,
X_CAL_TYPE => v_unit_offer_opt_rec.cal_type,
X_CI_SEQUENCE_NUMBER => v_unit_offer_opt_rec.ci_sequence_number,
X_LOCATION_CD => v_unit_offer_opt_rec.location_cd,
X_UNIT_CLASS => v_unit_offer_opt_rec.unit_class,
X_UOO_ID => v_new_uoo_id,
X_IVRS_AVAILABLE_IND => v_unit_offer_opt_rec.ivrs_available_ind,
X_CALL_NUMBER => v_unit_offer_opt_rec.call_number,
X_UNIT_SECTION_STATUS => l_c_usec_status,
X_UNIT_SECTION_START_DATE => v_unit_offer_opt_rec.unit_section_start_date,
X_UNIT_SECTION_END_DATE => v_unit_offer_opt_rec.unit_section_end_date,
X_ENROLLMENT_ACTUAL => NULL,
X_WAITLIST_ACTUAL => NULL,
X_OFFERED_IND => v_unit_offer_opt_rec.offered_ind,
X_STATE_FINANCIAL_AID => v_unit_offer_opt_rec.state_financial_aid,
X_GRADING_SCHEMA_PRCDNCE_IND => v_unit_offer_opt_rec.grading_schema_prcdnce_ind,
X_FEDERAL_FINANCIAL_AID => v_unit_offer_opt_rec.federal_financial_aid,
X_UNIT_QUOTA => v_unit_offer_opt_rec.unit_quota,
X_UNIT_QUOTA_RESERVED_PLACES => v_unit_offer_opt_rec.unit_quota_reserved_places,
X_INSTITUTIONAL_FINANCIAL_AID => v_unit_offer_opt_rec.institutional_financial_aid,
X_GRADING_SCHEMA_CD => v_unit_offer_opt_rec.grading_schema_cd,
X_GS_VERSION_NUMBER => v_latest_gs_version,
X_UNIT_CONTACT => v_unit_offer_opt_rec.unit_contact,
X_MODE => 'R',
X_ORG_ID => l_org_id,
x_ss_enrol_ind => v_unit_offer_opt_rec.ss_enrol_ind,
x_ss_display_ind => v_unit_offer_opt_rec.ss_display_ind, --Added by apelleti as per the DLD PSP001-US
X_OWNER_ORG_UNIT_CD => v_unit_offer_opt_rec.owner_org_unit_cd, -- Added By Pradhakr as per DLD PSP001-US
X_ATTENDANCE_REQUIRED_IND => v_unit_offer_opt_rec.attendance_required_ind,
X_RESERVED_SEATING_ALLOWED => v_unit_offer_opt_rec.reserved_seating_allowed,
X_SPECIAL_PERMISSION_IND => v_unit_offer_opt_rec.special_permission_ind,
X_DIR_ENROLLMENT => v_unit_offer_opt_rec.dir_enrollment, -- The following three fields were added by Pradhakr
X_ENR_FROM_WLST => v_unit_offer_opt_rec.enr_from_wlst, -- as part of Enrollment Build process (Enh.Bug# 1832130)
X_INQ_NOT_WLST => v_unit_offer_opt_rec.inq_not_wlst,
-- msrinivi 16 Aug,2001 : Added the following col according to bug 1882122
x_rev_account_cd => v_unit_offer_opt_rec.rev_account_cd ,
x_anon_unit_grading_ind => v_unit_offer_opt_rec.anon_unit_grading_ind ,
x_anon_assess_grading_ind => v_unit_offer_opt_rec.anon_assess_grading_ind ,
x_non_std_usec_ind => v_unit_offer_opt_rec.non_std_usec_ind,
x_auditable_ind => v_unit_offer_opt_rec.auditable_ind,
x_audit_permission_ind => v_unit_offer_opt_rec.audit_permission_ind,
x_not_multiple_section_flag => v_unit_offer_opt_rec.not_multiple_section_flag,
x_sup_uoo_id => NULL,
x_relation_type => 'NONE',
x_default_enroll_flag => v_unit_offer_opt_rec.default_enroll_flag,
x_abort_flag => 'N'
);
SELECT *
FROM igs_as_unit_ai_grp
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;
SELECT *
FROM IGS_AS_UNITASS_ITEM uai
WHERE uai.unit_cd = p_unit_cd AND
uai.version_number = p_version_number AND
uai.cal_type = p_cal_type AND
uai.ci_sequence_number = p_ci_sequence_number AND
uai.unit_ass_item_group_id = cp_unit_ass_item_group_id AND
uai.logical_delete_dt IS NULL;
SELECT max(gs.version_number) maxm
FROM igs_as_grd_schema gs
WHERE gs.grading_schema_cd = cp_grad_schema_cd;
igs_as_unit_ai_grp_pkg.insert_row(
x_rowid => l_rowid,
x_unit_ass_item_group_id => l_unit_ass_item_group_id,
x_unit_cd => p_new_unit_cd,
x_version_number => p_new_version_number,
x_cal_type => cur_unit_ass_group_rec.cal_type,
x_ci_sequence_number => cur_unit_ass_group_rec.ci_sequence_number,
x_group_name => cur_unit_ass_group_rec.group_name,
x_midterm_formula_code => cur_unit_ass_group_rec.midterm_formula_code,
x_midterm_formula_qty => cur_unit_ass_group_rec.midterm_formula_qty,
x_midterm_weight_qty => cur_unit_ass_group_rec.midterm_weight_qty,
x_final_formula_code => cur_unit_ass_group_rec.final_formula_code,
x_final_formula_qty => cur_unit_ass_group_rec.final_formula_qty,
x_final_weight_qty => cur_unit_ass_group_rec.final_weight_qty
);
SELECT IGS_AS_UNITASS_ITEM_SEQ_NUM_S.nextval
INTO v_new_sequence_number
FROM dual;
IGS_AS_UNITASS_ITEM_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_unit_assessment_item_rec.cal_type,
X_CI_SEQUENCE_NUMBER => v_unit_assessment_item_rec.ci_sequence_number,
X_ASS_ID => v_unit_assessment_item_rec.ass_id,
X_SEQUENCE_NUMBER => v_new_sequence_number,
X_CI_START_DT => v_unit_assessment_item_rec.ci_start_dt,
X_CI_END_DT => v_unit_assessment_item_rec.ci_end_dt,
X_UNIT_CLASS => v_unit_assessment_item_rec.unit_class,
X_UNIT_MODE => v_unit_assessment_item_rec.unit_mode,
X_LOCATION_CD => v_unit_assessment_item_rec.location_cd,
X_DUE_DT => v_unit_assessment_item_rec.due_dt,
X_REFERENCE => v_unit_assessment_item_rec.reference,
X_DFLT_ITEM_IND => v_unit_assessment_item_rec.dflt_item_ind,
X_LOGICAL_DELETE_DT => v_unit_assessment_item_rec.logical_delete_dt,
X_ACTION_DT => v_unit_assessment_item_rec.action_dt,
X_EXAM_CAL_TYPE => v_unit_assessment_item_rec.exam_cal_type,
X_EXAM_CI_SEQUENCE_NUMBER => v_unit_assessment_item_rec.exam_ci_sequence_number,
X_MODE => 'R',
X_ORG_ID => igs_ge_gen_003.get_org_id,
X_GRADING_SCHEMA_CD => v_unit_assessment_item_rec.grading_schema_cd,
X_GS_VERSION_NUMBER => v_unit_assessment_item_rec.gs_version_number,
X_RELEASE_DATE => v_unit_assessment_item_rec.release_date,
X_UNIT_ASS_ITEM_ID => l_unit_ass_item_id, --out parameter
X_DESCRIPTION => v_unit_assessment_item_rec.description,
x_unit_ass_item_group_id => l_unit_ass_item_group_id,
x_midterm_mandatory_type_code => v_unit_assessment_item_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => v_unit_assessment_item_rec.midterm_weight_qty,
x_final_mandatory_type_code => v_unit_assessment_item_rec.final_mandatory_type_code,
x_final_weight_qty => v_unit_assessment_item_rec.final_weight_qty
);
SELECT *
FROM IGS_PS_UNT_OFR_PAT_N uopn
WHERE uopn.unit_cd = p_unit_cd AND
uopn.version_number = p_version_number AND
uopn.cal_type = p_cal_type AND
uopn.ci_sequence_number = p_ci_sequence_number AND
EXISTS (
SELECT 'x'
FROM IGS_GE_NOTE nte
WHERE nte.reference_number = uopn.reference_number AND
nte.note_text IS NOT NULL );
IGS_PS_UNT_OFR_PAT_N_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_REFERENCE_NUMBER => v_new_ref_number,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_unit_offer_pat_note_rec.cal_type,
X_CI_SEQUENCE_NUMBER => v_unit_offer_pat_note_rec.ci_sequence_number,
X_CRS_NOTE_TYPE => v_unit_offer_pat_note_rec.crs_note_type,
X_MODE => 'R'
);
SELECT *
FROM igs_ps_uofr_wlst_pri
WHERE unit_cd = p_unit_cd
AND version_number = p_version_number
AND calender_type = p_cal_type
AND ci_sequence_number= p_ci_sequence_number;
SELECT *
FROM igs_ps_uofr_wlst_prf
WHERE unit_ofr_waitlist_priority_id = cp_unit_ofr_wlst_priority_id;
igs_ps_uofr_wlst_pri_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_ofr_wl_priority_id => v_unit_ofr_wlist_pri_id,
x_unit_cd => p_new_unit_cd,
x_version_number => p_new_version_number,
x_calender_type => c_uofr_wlst_pri_rec.calender_type,
x_ci_sequence_number => c_uofr_wlst_pri_rec.ci_sequence_number,
x_priority_number => c_uofr_wlst_pri_rec.priority_number,
x_priority_value => c_uofr_wlst_pri_rec.priority_value,
X_MODE => 'R');
igs_ps_uofr_wlst_prf_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_ofr_wl_pref_id => v_unit_ofr_wlist_prf_id,
x_unit_ofr_wl_priority_id => v_unit_ofr_wlist_pri_id,
x_preference_order => c_uofr_wlst_prf_rec.preference_order,
x_preference_code => c_uofr_wlst_prf_rec.preference_code,
x_preference_version => c_uofr_wlst_prf_rec.preference_version,
X_MODE => 'R');
SELECT *
FROM IGS_PS_UNIT_OFR_PAT UOP
WHERE UOP.unit_cd = p_unit_cd AND
UOP.version_number = p_version_number AND
UOP.cal_type = p_cal_type AND
UOP.delete_flag = 'N' AND
UOP.ci_end_dt = (
SELECT MAX(ci_end_dt)
FROM IGS_PS_UNIT_OFR_PAT UOP2
WHERE UOP2.unit_cd = UOP.unit_cd AND
UOP2.version_number = UOP.version_number AND
UOP2.delete_flag = 'N' AND
UOP2.cal_type = UOP.cal_type)
ORDER BY UOP.ci_end_dt DESC, UOP.ci_start_dt DESC;
IGS_PS_UNIT_OFR_PAT_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CI_SEQUENCE_NUMBER => v_unit_offer_pat_rec.ci_sequence_number,
X_CAL_TYPE => v_unit_offer_pat_rec.cal_type,
X_CI_START_DT => v_unit_offer_pat_rec.ci_start_dt,
X_CI_END_DT => v_unit_offer_pat_rec.ci_end_dt,
X_WAITLIST_ALLOWED => v_unit_offer_pat_rec.waitlist_allowed,
X_MAX_STUDENTS_PER_WAITLIST => v_unit_offer_pat_rec.max_students_per_waitlist,
X_MODE => 'R',
X_ORG_ID => l_org_id,
X_DELETE_FLAG => v_unit_offer_pat_rec.delete_flag ,
x_abort_flag => 'N'
);
SELECT *
FROM IGS_PS_UNIT_OFR
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
IGS_PS_UNIT_OFR_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_UNIT_CD => p_new_unit_cd,
X_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => v_unit_offer_rec.cal_type,
X_MODE => 'R'
);
SELECT *
FROM igs_ps_unit_location
WHERE unit_code = p_unit_cd
AND unit_version_number = p_version_number;
igs_ps_unit_location_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_location_id => v_unit_location_id,
x_unit_code => p_new_unit_cd,
x_unit_version_number => p_new_version_number,
x_location_code => c_unit_loc_rec.location_code,
x_building_id => c_unit_loc_rec.building_id,
x_room_id => c_unit_loc_rec.room_id,
X_MODE => 'R');
SELECT *
FROM igs_ps_unit_facility
WHERE unit_code = p_unit_cd
AND unit_version_number = p_version_number;
igs_ps_unit_facility_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_media_id => v_unit_media_id,
x_unit_code => p_new_unit_cd,
x_unit_version_number => p_new_version_number,
x_media_code => c_unit_fac_rec.media_code,
X_MODE => 'R');
SELECT *
FROM igs_ps_unit_cros_ref
WHERE parent_unit_code = p_unit_cd
AND parent_unit_version_number = p_version_number;
igs_ps_unit_cros_ref_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_cross_reference_id => v_unit_cross_reference_id,
x_parent_unit_code => p_new_unit_cd,
x_parent_unit_version_number => p_new_version_number,
x_child_unit_code => c_unit_cros_ref_rec.child_unit_code,
x_child_unit_version_number => c_unit_cros_ref_rec.child_unit_version_number,
X_MODE => 'R');
SELECT *
FROM igs_ps_unit_grd_schm
WHERE unit_code = p_unit_cd
AND unit_version_number = p_version_number;
igs_ps_unit_grd_schm_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_grading_schema_id => v_unit_grading_schema_id,
x_unit_code => p_new_unit_cd,
x_unit_version_number => p_new_version_number,
x_grading_schema_code => c_unit_grd_schm_rec.grading_schema_code,
x_grd_schm_version_number => c_unit_grd_schm_rec.grd_schm_version_number,
x_default_flag => c_unit_grd_schm_rec.default_flag,
X_MODE => 'R');
SELECT *
FROM igs_ps_unit_fld_stdy
WHERE unit_code = p_unit_cd
AND version_number = p_version_number;
igs_ps_unit_fld_stdy_pkg.Insert_Row(
x_rowid => x_rowid,
x_unit_field_of_study_id => v_unit_field_of_study_id,
x_unit_code => p_new_unit_cd,
x_version_number => p_new_version_number,
x_field_of_study => c_unit_fld_stdy_rec.field_of_study,
X_MODE => 'R');
SELECT *
FROM igs_as_appr_grd_sch
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number AND
closed_ind = 'N';
SELECT MAX(version_number)
FROM igs_as_grd_schema
WHERE grading_schema_cd = p_grading_schema_cd;
igs_as_appr_grd_sch_pkg.insert_row(
x_rowid => l_rowid,
x_unit_cd => p_new_unit_cd,
x_version_number => p_new_version_number,
x_assessment_type => l_appr_grd_sch.assessment_type,
x_grading_schema_cd => l_appr_grd_sch.grading_schema_cd,
x_gs_version_number => l_max_ver_grd_sch,
x_default_ind => l_appr_grd_sch.default_ind,
x_closed_ind => 'N',
x_mode => 'R' );
SELECT 'x'
FROM IGS_PS_UNIT_VER
WHERE unit_cd = cp_new_unit_cd AND
version_number = cp_new_version_number;
SELECT 'x'
FROM IGS_PS_UNIT_VER
WHERE unit_cd = cp_old_unit_cd AND
version_number = cp_old_version_number;
SELECT 'X'
FROM igs_ge_s_log_entry
WHERE s_log_type='USEC-ROLL'
AND creation_dt=cp_creation_dt;
SELECT rowid
FROM igs_ge_s_log
WHERE s_log_type='USEC-ROLL'
AND creation_dt=cp_creation_dt;
v_uoo_inserted_cnt NUMBER(4);
v_uai_inserted_cnt NUMBER(4);
SELECT 'x'
FROM igs_ps_unit_ofr_pat uop
WHERE uop.unit_cd = p_unit_cd
AND uop.version_number = p_version_number
AND uop.cal_type = p_cal_type
AND uop.ci_sequence_number = p_dest_ci_sequence_number
AND uop.delete_flag = 'N';
SELECT *
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo.unit_cd = p_unit_cd
AND uoo.version_number = p_version_number
AND uoo.cal_type = p_source_cal_type
AND uoo.ci_sequence_number = p_source_ci_sequence_number;
SELECT MAX(gs.version_number)
FROM igs_as_grd_schema gs
WHERE gs.grading_schema_cd = cp_grad_schema_cd;
SELECT *
FROM igs_as_unit_ai_grp
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;
SELECT *
FROM igs_as_unitass_item uai
WHERE uai.logical_delete_dt IS NULL
AND uai.unit_cd = cp_unit_cd
AND uai.version_number = cp_version_number
AND uai.cal_type = cp_source_cal_type
AND uai.ci_sequence_number = cp_source_ci_sequence_number
AND uai.unit_ass_item_group_id = cp_unit_ass_item_group_id
ORDER BY uai.exam_cal_type, uai.exam_ci_sequence_number;
SELECT *
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.version_number = cp_version_number
AND uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = cp_dest_ci_sequence_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class;
SELECT IGS_PS_UNIT_OFR_OPT_UOO_ID_S.NEXTVAL
FROM DUAL;
SELECT IGS_AS_UNITASS_ITEM_SEQ_NUM_S.NEXTVAL
FROM DUAL;
SELECT start_dt,end_dt
FROM igs_ca_inst_all
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_num;
SELECT owner_org_unit_cd
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
sarakshi 17-Nov-2005 Bug#4726940, impact of change of signature of the update_usec_record, passing the default enroll flag
value from the source to the destination for the subordinate section. Also removed variable l_usec_roll
as relationship logic needs to be called irrespective of whether a single unit ssection has been rolled or not
(reverse chronological order - newest change first)
***************************************************************/
l_c_none VARCHAR2(10) := 'NONE';
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE sup_uoo_id IN ( SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE cal_type = p_src_cal_type
AND ci_sequence_number = p_src_sequence_num
AND unit_cd = p_unit_cd
AND version_number = p_version_number
);
SELECT unit_cd,version_number,location_cd,unit_class
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND cal_type = p_dst_cal_type
AND ci_sequence_number = p_dst_sequence_num
AND location_cd = cp_location_cd
AND unit_class = cp_unit_class;
SELECT uoo.*
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_unit_ver_all uv,
igs_ps_unit_stat us
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.version_number = cp_version_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class
AND uoo.relation_type = l_c_none
AND uoo.unit_section_status <> l_c_notoffered
AND uoo.unit_cd = uv.unit_cd
AND uoo.version_number = uv.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status <> l_c_inactive
AND uoo.uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem);
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE cal_type = p_src_cal_type
AND ci_sequence_number = p_src_sequence_num
AND unit_cd = p_unit_cd
AND version_number = p_version_number
AND relation_type = l_c_subordinate;
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND location_cd = cp_location_cd
AND unit_class = cp_unit_class
AND cal_type = p_dst_cal_type
AND ci_sequence_number = p_dst_sequence_num;
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT uoo.uoo_id,uoo.sup_uoo_id
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_unit_ver_all uv,
igs_ps_unit_stat us
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.version_number = cp_version_number
AND uoo.location_cd = cp_location_cd
AND uoo.unit_class = cp_unit_class
AND uoo.relation_type IN (l_c_superior,l_c_none)
AND uoo.cal_type = p_dst_cal_type
AND uoo.ci_sequence_number = p_dst_sequence_num
AND uoo.unit_cd = uv.unit_cd
AND uoo.version_number = uv.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status <> l_c_inactive
AND uoo.uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem)
AND uoo.unit_section_status <> l_c_notoffered;
SELECT uoo_id,sup_uoo_id,cal_type,ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND location_cd = cp_location_cd
AND unit_class = cp_unit_class
AND relation_type IN (l_c_superior,l_c_none)
AND uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem)
AND unit_section_Status <> l_c_notoffered;
SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE load_cal_type = cp_load_cal
AND load_ci_sequence_number = cp_load_seq;
SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE teach_cal_type=p_dst_cal_type
AND teach_ci_sequence_number=p_dst_sequence_num;
--update the new unit sections record, relation_type and sup_uoo_id column value
update_usec_record(rec_new_sub_us.uoo_id,l_c_subordinate,l_c_new_sup_uoo_id.uoo_id,rec_get_sub_us_list.default_enroll_flag);
update_usec_record(l_c_new_sup_uoo_id.uoo_id,l_c_superior,NULL,NULL);
update_usec_record(l_c_get_new_sub.uoo_id,l_c_subordinate,l_c_new_sup_us.uoo_id,rec_get_sub_us_list1.default_enroll_flag);
update_usec_record(l_c_new_sup_us.uoo_id,l_c_superior,NULL,NULL);
--update the new unit sections relation_type and sup_uoo_id column value
update_usec_record(l_c_get_new_sub.uoo_id,l_c_subordinate,l_c_new_sup_us1.uoo_id,rec_get_sub_us_list1.default_enroll_flag);
--update the new unit sections relation_type
update_usec_record(l_c_new_sup_us1.uoo_id,l_c_superior,NULL,NULL);
teachCalendar_tbl.DELETE;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_type ct,
igs_ca_inst ci,
igs_ca_inst_rel cir
WHERE cir.sup_ci_sequence_number = ci.sequence_number
AND cir.sup_cal_type = ci.cal_type
AND ct.cal_type = ci.cal_type
AND ct.s_cal_cat = cst_exam
AND cir.sub_cal_type = cp_teach_cal_type
AND cir.sub_ci_sequence_number = cp_teach_seq_num
AND ci.cal_type = cp_exam_cal_type
ORDER BY ci.start_dt;
SELECT COUNT(ci.cal_type)
FROM IGS_CA_TYPE ct,
IGS_CA_INST ci,
IGS_CA_INST_REL cir
WHERE cir.sup_ci_sequence_number = ci.sequence_number
AND cir.sup_cal_type = ci.cal_type
AND ct.cal_type = ci.cal_type
AND ct.s_cal_cat = cst_exam
AND cir.sub_cal_type = cp_teach_cal_type
AND cir.sub_ci_sequence_number = cp_teach_seq_num
AND ci.cal_type = cp_exam_cal_type;
v_uoo_inserted_cnt := 0;
v_uai_inserted_cnt := 0;
igs_ge_s_log_pkg.insert_row( x_rowid => l_rowid ,
x_s_log_type => 'USEC-ROLL',
x_creation_dt =>p_log_creation_date,
x_key =>NULL,
x_mode => 'R' );
igs_ps_unit_ofr_opt_pkg.insert_row(
x_rowid => x_rowid,
x_unit_cd => v_uoo_rec.unit_cd,
x_version_number => v_uoo_rec.version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_dest_ci_sequence_number,
x_location_cd => v_uoo_rec.location_cd,
x_unit_class => v_uoo_rec.unit_class,
x_uoo_id => v_new_uoo_id,
x_ivrs_available_ind => v_uoo_rec.ivrs_available_ind,
x_call_number => v_uoo_rec.call_number,
x_unit_section_status => l_c_usec_status,
x_unit_section_start_date => l_d_us_dest_start_dt,
x_unit_section_end_date => l_d_us_dest_end_dt,
x_enrollment_actual => NULL,
x_waitlist_actual => NULL,
x_offered_ind => v_uoo_rec.offered_ind,
x_state_financial_aid => v_uoo_rec.state_financial_aid,
x_grading_schema_prcdnce_ind => v_uoo_rec.grading_schema_prcdnce_ind,
x_federal_financial_aid => v_uoo_rec.federal_financial_aid,
x_unit_quota => v_uoo_rec.unit_quota,
x_unit_quota_reserved_places => v_uoo_rec.unit_quota_reserved_places,
x_institutional_financial_aid => v_uoo_rec.institutional_financial_aid,
x_grading_schema_cd => v_uoo_rec.grading_schema_cd,
x_gs_version_number => v_latest_gs_version,
x_unit_contact => v_unit_contact,
x_mode => 'R',
x_org_id => l_org_id,
x_ss_enrol_ind => v_uoo_rec.SS_ENROL_ind,
x_ss_display_ind => v_uoo_rec.ss_display_ind, --Added by apelleti as per DLD PSP001-US on 14-JUN-01
x_owner_org_unit_cd => v_uoo_rec.owner_org_unit_cd,
x_attendance_required_ind => v_uoo_rec.attendance_required_ind,
x_reserved_seating_allowed => v_uoo_rec.reserved_seating_allowed,
x_special_permission_ind => v_uoo_rec.special_permission_ind,
x_dir_enrollment => v_uoo_rec.dir_enrollment, --The following three fields were added by Pradhakr
x_enr_from_wlst => v_uoo_rec.enr_from_wlst, -- as part of Enrollment Build process (Enh.Bug# 1832130)
x_inq_not_wlst => v_uoo_rec.inq_not_wlst,
--Added the following col according to bug 1882122
x_rev_account_cd => v_uoo_rec.rev_account_cd , -- lpriyadh for enhacement bug # 1516959
x_anon_unit_grading_ind => v_uoo_rec.anon_unit_grading_ind ,
x_anon_assess_grading_ind => v_uoo_rec.anon_assess_grading_ind,
x_non_std_usec_ind => v_uoo_rec.non_std_usec_ind,
x_auditable_ind => v_uoo_rec.auditable_ind,
x_audit_permission_ind => v_uoo_rec.audit_permission_ind,
x_not_multiple_section_flag => v_uoo_rec.not_multiple_section_flag,
x_sup_uoo_id => NULL,
x_relation_type => 'NONE',
x_default_enroll_flag => v_uoo_rec.default_enroll_flag,
x_abort_flag => 'N'
);
v_uoo_inserted_cnt := v_uoo_inserted_cnt + 1;
SELECT unit_ass_item_group_id
FROM igs_as_unit_ai_grp
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 group_name = cp_group_name;
SELECT 'X'
FROM igs_as_unitass_item_all
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 unit_ass_item_group_id = cp_unit_ass_item_group_id;
igs_as_unit_ai_grp_pkg.insert_row(
x_rowid => l_rowid1,
x_unit_ass_item_group_id => l_unit_ass_item_group_id,
x_unit_cd => p_unit_cd,
x_version_number => p_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_dest_ci_sequence_number,
x_group_name => cur_unit_ass_group_rec.group_name,
x_midterm_formula_code => cur_unit_ass_group_rec.midterm_formula_code,
x_midterm_formula_qty => cur_unit_ass_group_rec.midterm_formula_qty,
x_midterm_weight_qty => cur_unit_ass_group_rec.midterm_weight_qty,
x_final_formula_code => cur_unit_ass_group_rec.final_formula_code,
x_final_formula_qty => cur_unit_ass_group_rec.final_formula_qty,
x_final_weight_qty => cur_unit_ass_group_rec.final_weight_qty
);
-- insert to proceed, else don't insert this particular
-- IGS_AS_UNITASS_ITEM record
-- Validate reference is valid in destination pattern's UAI records
v_reference := v_uai_rec.reference;
-- Perform insert uai record
x_rowid := NULL;
igs_as_unitass_item_pkg.insert_row(
x_rowid => x_rowid,
x_unit_cd => p_unit_cd,
x_version_number => p_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_dest_ci_sequence_number,
x_ass_id => v_uai_rec.ass_id,
x_sequence_number => v_uai_seq_num,
x_ci_start_dt => v_uai_rec.ci_start_dt,
x_ci_end_dt => v_uai_rec.ci_end_dt,
x_unit_class => v_uai_rec.unit_class,
x_unit_mode => v_uai_rec.unit_mode,
x_location_cd => v_uai_rec.location_cd,
x_due_dt => NULL,
x_reference => v_reference,
x_dflt_item_ind => v_uai_rec.dflt_item_ind,
x_logical_delete_dt => NULL,
x_action_dt => NULL,
x_exam_cal_type => v_exam_cal_type,
x_exam_ci_sequence_number => v_exam_ci_sequence_number,
x_mode => 'R',
x_org_id => igs_ge_gen_003.get_org_id,
x_grading_schema_cd => v_uai_rec.grading_schema_cd,
x_gs_version_number => v_uai_rec.gs_version_number,
x_release_date => v_uai_rec.release_date,
x_unit_ass_item_id => l_unit_ass_item_id, --out parameter
x_description => v_uai_rec.description,
x_unit_ass_item_group_id => l_unit_ass_item_group_id,
x_midterm_mandatory_type_code => v_uai_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => v_uai_rec.midterm_weight_qty,
x_final_mandatory_type_code => v_uai_rec.final_mandatory_type_code,
x_final_weight_qty => v_uai_rec.final_weight_qty
);
v_uai_inserted_cnt := v_uai_inserted_cnt + 1;
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_UOO_NO_UOO_TOBE_ROLLED';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_INV_NO_UOO_ROLLED';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTILROLL_USI';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_SUCCESS_ROLL_UOO_UAI';
ELSIF ( v_uoo_inserted_cnt = 0 AND v_uoo_error = FALSE) THEN
-- no uai records were selected
IF (v_uai_fetched_cnt = 0) THEN
p_message_name := 'IGS_PS_UOO_NO_UAI_TOBE_ROLLED';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_NO_UOO_UAI_ROLLED';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_INV_UOO_ROLLED';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARROLL_UAI_INVLD_DATA';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF ( v_uoo_inserted_cnt = 0 AND v_uoo_error = TRUE) THEN
-- no uai records were selected
IF (v_uai_fetched_cnt = 0) THEN
p_message_name := 'IGS_PS_INV_NO_UAI_TOBE_ROLLED';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_INV_NO_UAI_OBS_DATA';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_INV_NO_UAI_OBS_DATA';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_INVALID_DATA';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARROLL_USI_INVALID';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_NOTROLLED_INVALID_DATA';
ELSIF ((v_uoo_inserted_cnt < c_uoo_source_rec%ROWCOUNT OR v_tro_recs_skipped = TRUE) AND v_uoo_error = FALSE) THEN
-- no uai records were selected
IF (v_uai_fetched_cnt = 0) THEN
p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_INV_PARROLL_UOO_OBSDAT';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UAI';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF ((v_uoo_inserted_cnt < c_uoo_source_rec%ROWCOUNT OR v_tro_recs_skipped = TRUE) AND v_uoo_error = TRUE) THEN
-- no uai records were selected
IF (v_uai_fetched_cnt = 0) THEN
p_message_name := 'IGS_PS_PRINV_PARROL_UOO_OBS';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_PRINV_NO_UAI_ROL_OBS';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_PRINV_NO_UAI_INVALID';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_INVALI';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARROLL_UOO_AND_UAI';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_INVDAT';
ELSIF (v_uoo_inserted_cnt = c_uoo_source_rec%ROWCOUNT) THEN
-- no uai records were selected
IF (v_uai_fetched_cnt = 0) THEN
p_message_name := 'IGS_PS_SUCCESSROLL_UOO_UAI';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
p_message_name := 'IGS_PS_INV_ALL_UOO_ROLLED';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_PARTIALROLL_UAI_UAIINV';
ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
IF v_successful_pattern_mbr = FALSE THEN
p_message_name := 'IGS_PS_SUCCESS_ROLL_UOO_UAI';
igs_ge_s_log_pkg.delete_row(x_rowid=>l_rowid);
SELECT owner_org_unit_cd
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;