[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
sarakshi 04-May-2004 Enh#3568858,columns ovrd_wkld_val_flag, workload_val_code has been trimed and also included in the insert statement.
sarakshi 10-Nov-2003 Enh#3116171, added logic related to the newly introduced field BILLING_CREDIT_POINTS, modified trim values procedure and insert_row
***********************************************************************************************/
CURSOR cur_check (cp_unit_cd igs_ps_unit.unit_cd%TYPE) IS
SELECT 'X'
FROM igs_ps_unit
WHERE unit_cd=cp_unit_cd;
SELECT unit_type_id
FROM igs_ps_unit_type_lvl
WHERE level_code=cp_level_code;
SELECT rpt_fmly_id
FROM igs_ps_rpt_fmly
WHERE repeat_code=cp_repeat_code;
SELECT igs_ps_unt_crclm_all_s.NEXTVAL
FROM DUAL;
SELECT 'X'
FROM igs_lookup_values
WHERE lookup_type='CREDIT_POINT_DSCR'
AND lookup_code=cp_credit_point_descriptor;
INSERT INTO igs_ps_unit
(
unit_cd,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
p_unit_ver_rec.unit_cd,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id
);
INSERT INTO igs_ps_unit_subtitle
(
subtitle_id,
unit_cd,
version_number,
subtitle,
approved_ind,
closed_ind,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
igs_ps_unit_subtitle_s.NEXTVAL,
p_unit_ver_rec.unit_cd,
p_unit_ver_rec.version_number,
p_unit_ver_rec.subtitle,
p_unit_ver_rec.subtitle_approved_ind,
p_unit_ver_rec.subtitle_closed_ind,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id
) RETURNING subtitle_id INTO l_n_subtitle_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.rec_inserted_igs_ps_unit_subtitle',
'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
'Subtitle_id:'||l_n_subtitle_id||' '||'Status:'||p_unit_ver_rec.status);
INSERT INTO igs_ps_unt_crclm_all
(
curriculum_id,
description,
closed_ind,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
p_unit_ver_rec.curriculum_id,
p_unit_ver_rec.curriculum_description,
p_unit_ver_rec.curriculum_closed_ind,
igs_ge_gen_003.get_org_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.rec_inserted_igs_ps_unt_crclm_all',
'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
'curriculum_id:'||p_unit_ver_rec.curriculum_id||' '||'Status:'||p_unit_ver_rec.status);
INSERT INTO igs_ps_unit_ver_all
(
unit_cd,
version_number,
start_dt,
review_dt,
expiry_dt,
end_dt,
unit_status,
title,
short_title,
title_override_ind,
abbreviation,
unit_level,
credit_point_descriptor,
enrolled_credit_points,
points_override_ind,
supp_exam_permitted_ind,
coord_person_id,
owner_org_unit_cd,
owner_ou_start_dt,
award_course_only_ind,
research_unit_ind,
industrial_ind,
practical_ind,
repeatable_ind,
assessable_ind,
achievable_credit_points,
points_increment,
points_min,
points_max,
unit_int_course_level_cd,
subtitle_modifiable_flag,
approval_date,
lecture_credit_points,
lab_credit_points,
other_credit_points,
clock_hours,
work_load_cp_lecture,
work_load_cp_lab,
continuing_education_units,
enrollment_expected,
enrollment_minimum,
enrollment_maximum,
advance_maximum,
state_financial_aid,
federal_financial_aid,
institutional_financial_aid,
same_teaching_period,
max_repeats_for_credit,
max_repeats_for_funding,
max_repeat_credit_points,
same_teach_period_repeats,
same_teach_period_repeats_cp,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
subtitle_id,
work_load_other,
contact_hrs_lecture,
contact_hrs_lab,
contact_hrs_other,
non_schd_required_hrs,
exclude_from_max_cp_limit,
record_exclusion_flag,
ss_display_ind,
cal_type_enrol_load_cal,
sequence_num_enrol_load_cal,
cal_type_offer_load_cal,
sequence_num_offer_load_cal,
curriculum_id,
override_enrollment_max,
rpt_fmly_id,
unit_type_id,
special_permission_ind,
org_id,
ss_enrol_ind,
ivr_enrol_ind,
rev_account_cd,
claimable_hours,
anon_unit_grading_ind,
anon_assess_grading_ind,
auditable_ind,
audit_permission_ind,
max_auditors_allowed,
billing_credit_points,
ovrd_wkld_val_flag,
workload_val_code,
billing_hrs,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
p_unit_ver_rec.unit_cd,
p_unit_ver_rec.version_number,
p_unit_ver_rec.start_dt,
p_unit_ver_rec.review_dt,
p_unit_ver_rec.expiry_dt,
p_unit_ver_rec.end_dt,
p_unit_ver_rec.unit_status,
p_unit_ver_rec.title,
p_unit_ver_rec.short_title,
p_unit_ver_rec.title_override_ind,
p_unit_ver_rec.abbreviation,
p_unit_ver_rec.unit_level,
p_unit_ver_rec.credit_point_descriptor,
p_unit_ver_rec.enrolled_credit_points,
p_unit_ver_rec.points_override_ind,
p_unit_ver_rec.supp_exam_permitted_ind,
l_n_coord_person_id,
p_unit_ver_rec.owner_org_unit_cd,
l_d_owner_ou_start_dt,
p_unit_ver_rec.award_course_only_ind,
p_unit_ver_rec.research_unit_ind,
p_unit_ver_rec.industrial_ind,
p_unit_ver_rec.practical_ind,
p_unit_ver_rec.repeatable_ind,
p_unit_ver_rec.assessable_ind,
p_unit_ver_rec.achievable_credit_points,
p_unit_ver_rec.points_increment,
p_unit_ver_rec.points_min,
p_unit_ver_rec.points_max,
p_unit_ver_rec.unit_int_course_level_cd,
p_unit_ver_rec.subtitle_modifiable_flag,
p_unit_ver_rec.approval_date,
p_unit_ver_rec.lecture_credit_points,
p_unit_ver_rec.lab_credit_points,
p_unit_ver_rec.other_credit_points,
p_unit_ver_rec.clock_hours,
p_unit_ver_rec.work_load_cp_lecture,
p_unit_ver_rec.work_load_cp_lab,
p_unit_ver_rec.continuing_education_units,
p_unit_ver_rec.enrollment_expected,
p_unit_ver_rec.enrollment_minimum,
p_unit_ver_rec.enrollment_maximum,
p_unit_ver_rec.advance_maximum,
p_unit_ver_rec.state_financial_aid,
p_unit_ver_rec.federal_financial_aid,
p_unit_ver_rec.institutional_financial_aid,
p_unit_ver_rec.same_teaching_period,
p_unit_ver_rec.max_repeats_for_credit,
p_unit_ver_rec.max_repeats_for_funding,
p_unit_ver_rec.max_repeat_credit_points,
p_unit_ver_rec.same_teach_period_repeats,
p_unit_ver_rec.same_teach_period_repeats_cp,
p_unit_ver_rec.attribute_category,
p_unit_ver_rec.attribute1,
p_unit_ver_rec.attribute2,
p_unit_ver_rec.attribute3,
p_unit_ver_rec.attribute4,
p_unit_ver_rec.attribute5,
p_unit_ver_rec.attribute6,
p_unit_ver_rec.attribute7,
p_unit_ver_rec.attribute8,
p_unit_ver_rec.attribute9,
p_unit_ver_rec.attribute10,
p_unit_ver_rec.attribute11,
p_unit_ver_rec.attribute12,
p_unit_ver_rec.attribute13,
p_unit_ver_rec.attribute14,
p_unit_ver_rec.attribute15,
p_unit_ver_rec.attribute16,
p_unit_ver_rec.attribute17,
p_unit_ver_rec.attribute18,
p_unit_ver_rec.attribute19,
p_unit_ver_rec.attribute20,
l_n_subtitle_id,
p_unit_ver_rec.work_load_other,
p_unit_ver_rec.contact_hrs_lecture,
p_unit_ver_rec.contact_hrs_lab,
p_unit_ver_rec.contact_hrs_other,
p_unit_ver_rec.non_schd_required_hrs,
p_unit_ver_rec.exclude_from_max_cp_limit,
p_unit_ver_rec.record_exclusion_flag,
p_unit_ver_rec.ss_display_ind,
l_c_cal_type_enrol_load_cal,
l_n_seq_num_enrol_load_cal,
l_c_cal_type_offer_load_cal,
l_n_seq_num_offer_load_cal,
p_unit_ver_rec.curriculum_id,
p_unit_ver_rec.override_enrollment_max,
l_n_rpt_fmly_id,
l_n_unit_type_id,
p_unit_ver_rec.special_permission_ind,
igs_ge_gen_003.get_org_id,
p_unit_ver_rec.ss_enrol_ind,
p_unit_ver_rec.ivr_enrol_ind,
p_unit_ver_rec.rev_account_cd,
p_unit_ver_rec.claimable_hours,
p_unit_ver_rec.anon_unit_grading_ind,
p_unit_ver_rec.anon_assess_grading_ind,
p_unit_ver_rec.auditable_ind,
p_unit_ver_rec.audit_permission_ind,
p_unit_ver_rec.max_auditors_allowed,
p_unit_ver_rec.billing_credit_points,
p_unit_ver_rec.ovrd_wkld_val_flag,
p_unit_ver_rec.workload_val_code,
p_unit_ver_rec.billing_hrs,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.Record_Inserted',
'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number);
Purpose : This procedure is a sub process to insert records of Teaching Responsibility.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
smvk 12-Dec-2002 Added a boolean parameter to the function call igs_ps_val_tr.crsp_val_tr_perc.
As a part of the Bug # 2696207
smvk 20-Dec-2002 Removed the IGS_PS_TCH_RESP_PKG.check_constraints for org_unit_cd,
as the org unit code can have value in mixed case. Bug # 2487149.
********************************************************************************************** */
l_d_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
SELECT 'X'
FROM igs_or_inst_org_base_v a, igs_or_status b
WHERE a.party_number = cp_org_unit_cd
AND a.org_status = b.org_status
AND b.s_org_status <> 'INACTIVE';
/* Insert the Record */
INSERT INTO igs_ps_tch_resp
(unit_cd,
version_number,
org_unit_cd,
ou_start_dt,
percentage,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_tab_teach_resp(I).unit_cd,
p_tab_teach_resp(I).version_number,
p_tab_teach_resp(I).org_unit_cd,
l_d_ou_start_dt,
p_tab_teach_resp(I).percentage,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.Record_Inserted',
'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'ou_start_dt:'||l_d_ou_start_dt);
/* Post Insert Validations */
IF NOT igs_ps_validate_lgcy_pkg.post_teach_resp(p_tab_teach_resp) THEN
p_c_rec_status := 'E';
'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_Post_insert_validation','Status:'||p_c_rec_status);
Purpose : This procedure is a sub process to insert records of Unit Disciplines.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
smvk 12-Dec-2002 Added a boolean parameter to the function call igs_ps_val_ud.crsp_val_ud_perc.
As a part of the Bug # 2696207
********************************************************************************************** */
l_c_message_name VARCHAR2(30);
/* Insert record */
INSERT INTO igs_ps_unit_dscp
(unit_cd,
version_number,
discipline_group_cd,
percentage,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_tab_unit_dscp(I).unit_cd,
p_tab_unit_dscp(I).version_number,
p_tab_unit_dscp(I).discipline_group_cd,
p_tab_unit_dscp(I).percentage,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.Record_Inserted',
'Unit code:'||p_tab_unit_dscp(I).unit_cd||' '||'Version number:'||p_tab_unit_dscp(I).version_number||' '||
'discipline_group_cd:'||p_tab_unit_dscp(I).discipline_group_cd);
/* Post Insert Checks */
IF NOT igs_ps_validate_lgcy_pkg.post_unit_discip(p_tab_unit_dscp) THEN
p_c_rec_status := 'E';
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.Status_after_Post_insert_check',
'Status:'||p_c_rec_status);
Purpose : This procedure is a sub process to insert records of Unit Grading Schema.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
/* Private Procedures for create_unit_grd_sch */
PROCEDURE trim_values ( p_grd_sch_rec IN OUT NOCOPY igs_ps_generic_pub.unit_gs_rec_type ) AS
BEGIN
p_grd_sch_rec.unit_cd := trim(p_grd_sch_rec.unit_cd);
/* Insert Record */
INSERT INTO igs_ps_unit_grd_schm
(unit_grading_schema_id,
unit_code,
unit_version_number,
grading_schema_code,
grd_schm_version_number,
default_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_ps_unit_grd_schm_s.NEXTVAL,
p_tab_grd_sch(I).unit_cd,
p_tab_grd_sch(I).version_number,
p_tab_grd_sch(I).grading_schema_code,
p_tab_grd_sch(I).grd_schm_version_number,
p_tab_grd_sch(I).default_flag,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.record_inserted',
'Unit code:'||p_tab_grd_sch(I).unit_cd||' '||'Version number:'||p_tab_grd_sch(I).version_number||' '||
'grading_schema_code:'||p_tab_grd_sch(I).grading_schema_code||' '||'grd_schm_version_number:'||
p_tab_grd_sch(I).grd_schm_version_number);
/* Post Insert Checks */
IF NOT igs_ps_validate_lgcy_pkg.post_unit_grd_sch (p_tab_grd_sch) THEN
p_c_rec_status := 'E';
SELECT s_unit_status
FROM igs_ps_unit_stat
WHERE unit_status = cp_c_unit_status;
l_insert_update VARCHAR2(1);
l_b_uop_deleted BOOLEAN;
sommukhe 14-NOV-2005 Bug # 4730169 addded column abort_flag in the insert to igs_ps_unit_ofr
********************************************************************************************* */
BEGIN
validate_uo_parameters ( p_usec_rec );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.record is already existing update status as Success and return',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
INSERT INTO igs_ps_unit_ofr
(unit_cd,
version_number,
cal_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_usec_rec.unit_cd,
p_usec_rec.version_number,
l_c_cal_type,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.Record_Inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code);
SELECT delete_flag
FROM igs_ps_unit_ofr_pat_all
WHERE unit_cd = cp_c_unit_cd AND
version_number = cp_n_ver_num AND
cal_type = cp_c_cal_type AND
ci_sequence_number = cp_n_ci_seq_num;
/* Pk validation for IGS_PS_UNIT_OFR_PAT , having delete_flag implementation*/
l_b_uop_deleted := FALSE;
IF rec_rowid.delete_flag ='N' THEN
p_usec_rec.status := 'K';
l_b_uop_deleted := TRUE;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.record is already existing update status as Success and return',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
IF l_b_uop_deleted THEN
UPDATE igs_ps_unit_ofr_pat_all
SET
waitlist_allowed = p_usec_rec.waitlist_allowed,
max_students_per_waitlist = p_usec_rec.max_students_per_waitlist,
delete_flag = 'N',
created_by = g_n_user_id,
creation_date = SYSDATE,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE unit_cd = p_usec_rec.unit_cd
AND version_number = p_usec_rec.version_number
AND cal_type = l_c_cal_type
AND ci_sequence_number = l_n_seq_num;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.Record_updated_when_uop_deleted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code);
INSERT INTO igs_ps_unit_ofr_pat_all
(unit_cd,
version_number,
cal_type,
ci_sequence_number,
ci_start_dt,
ci_end_dt,
waitlist_allowed,
max_students_per_waitlist,
delete_flag,
abort_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_usec_rec.unit_cd,
p_usec_rec.version_number,
l_c_cal_type,
l_n_seq_num,
l_d_start_dt,
l_d_end_dt,
p_usec_rec.waitlist_allowed,
p_usec_rec.max_students_per_waitlist,
'N',
'N',
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.Record_Inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code);
PROCEDURE validate_uoo_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
/***********************************************************************************************
Created By :
Date Created By:
Purpose :
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sarakshi 11-Sep-2003 Enh#3052452, Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
for validating default_enroll_flag.
vvutukur 19-Aug-2003 Enh#3045069.PSP Enh Build. Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
for validating not_multiple_section_flag.
********************************************************************************************* */
BEGIN
IF p_insert_update = 'I' THEN
/* Check for Unique Key Validation */
IF igs_ps_unit_ofr_opt_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
x_version_number => p_usec_rec.version_number,
x_cal_type => l_c_cal_type,
x_ci_sequence_number => l_n_seq_num,
x_location_cd => p_usec_rec.location_cd,
x_unit_class => p_usec_rec.unit_class ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
the values are null then do no proceed with the insert/update for these two tables.
sarakshi 12-Apr-2004 bug#3555871, Added code to populate the call_number field if teh profile option is AUTO
sarakshi 11-Sep-2003 Enh#3052452,Added new column sup_uoo_id,relation_type,default_enroll_flag to the insert statement.
Also updated the superior unit section record.
vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Added new column not_multiple_section_flag while insertion.
********************************************************************************************* */
AS
CURSOR c_usec_check(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id=cp_uoo_id
AND relation_type='SUPERIOR';
SELECT *
FROM igs_ps_sch_pat_int
WHERE unit_cd=cp_unit_cd
AND version_number=cp_version_number
AND calendar_type =cp_cal_type
AND sequence_number= cp_seq_num
AND abort_flag='N';
SELECT *
FROM igs_ps_sch_usec_int_all
WHERE unit_cd=cp_unit_cd
AND version_number=cp_version_number
AND calendar_type =cp_cal_type
AND sequence_number= cp_seq_num
AND location_cd=cp_location_cd
AND unit_class=cp_unit_class
AND abort_flag='N';
FUNCTION check_insert_update ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type) RETURN VARCHAR2 IS
CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE unit_cd = p_usec_rec.unit_cd
AND version_number = p_usec_rec.version_number
AND ci_sequence_number =cp_seq_num
AND unit_class = p_usec_rec.unit_class
AND location_cd = p_usec_rec.location_cd
AND cal_type = cp_cal_type ;
END check_insert_update;
PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
SELECT *
FROM igs_ps_unit_ofr_opt_all
WHERE unit_cd = p_usec_rec.unit_cd
AND version_number = p_usec_rec.version_number
AND ci_sequence_number =cp_seq_num
AND unit_class = p_usec_rec.unit_class
AND location_cd = p_usec_rec.location_cd
AND cal_type = cp_cal_type ;
SELECT auditable_ind, audit_permission_ind
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT grading_schema_code, grd_schm_version_number
FROM igs_ps_unit_grd_schm
WHERE unit_code = cp_unit_cd
AND unit_version_number = cp_version_number
AND default_flag = 'Y';
SELECT ivr_enrol_ind
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT ss_enrol_ind
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT same_teaching_period
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT owner_org_unit_cd
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
IF p_insert_update = 'I' THEN
IF p_usec_rec.offered_ind IS NULL THEN
p_usec_rec.offered_ind := 'Y';
IF p_insert_update = 'U' THEN
OPEN c_usec( l_c_cal_type,l_n_seq_num);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_check_insert_update',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
Assign_default(p_usec_rec,l_insert_update);
validate_uoo_db_cons ( p_usec_rec,l_insert_update );
igs_ps_validate_lgcy_pkg.validate_uoo ( p_usec_rec, l_c_cal_type, l_n_seq_num,l_n_sup_uoo_id,l_insert_update,l_conc_flag ) ;
IF l_insert_update = 'I' THEN
IF l_n_sup_uoo_id IS NOT NULL THEN
OPEN c_usec_check(l_n_sup_uoo_id);
UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
WHERE uoo_id = l_n_sup_uoo_id;
SELECT relation_type,sup_uoo_id
FROM igs_ps_unit_ofr_opt_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND ci_sequence_number =cp_seq_num
AND unit_class = cp_unit_class
AND location_cd = cp_location_cd
AND cal_type = cp_cal_type ;
SELECT COUNT(*)
FROM igs_ps_unit_ofr_opt_all
WHERE sup_uoo_id= cp_uoo_id;
UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
WHERE uoo_id = l_n_sup_uoo_id;
UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'NONE'
WHERE uoo_id = l_cur_usec.sup_uoo_id;
--should not perform while update...
--Set the value of the call_number if the profile option is AUTO
IF l_insert_update = 'I' THEN
IF FND_PROFILE.VALUE('IGS_PS_CALL_NUMBER') = 'AUTO' THEN
p_usec_rec.call_number := igs_ps_unit_ofr_opt_pkg.get_call_number( l_c_cal_type, l_n_seq_num);
INSERT INTO igs_ps_unit_ofr_opt_all
(unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
uoo_id,
ivrs_available_ind,
call_number,
unit_section_status,
unit_section_start_date,
unit_section_end_date,
offered_ind,
state_financial_aid,
grading_schema_prcdnce_ind,
federal_financial_aid,
unit_quota,
unit_quota_reserved_places,
institutional_financial_aid,
grading_schema_cd,
gs_version_number,
unit_contact,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
ss_enrol_ind,
owner_org_unit_cd,
attendance_required_ind,
reserved_seating_allowed,
special_permission_ind,
ss_display_ind,
rev_account_cd,
anon_unit_grading_ind,
anon_assess_grading_ind,
non_std_usec_ind,
auditable_ind,
audit_permission_ind,
not_multiple_section_flag,
sup_uoo_id,
relation_type,
default_enroll_flag,
abort_flag
)
VALUES
(p_usec_rec.unit_cd,
p_usec_rec.version_number,
l_c_cal_type,
l_n_seq_num,
p_usec_rec.location_cd,
p_usec_rec.unit_class,
igs_ps_unit_ofr_opt_uoo_id_s.NEXTVAL,
p_usec_rec.ivrs_available_ind,
p_usec_rec.call_number,
p_usec_rec.unit_section_status,
p_usec_rec.unit_section_start_date,
p_usec_rec.unit_section_end_date,
p_usec_rec.offered_ind,
p_usec_rec.state_financial_aid,
p_usec_rec.grading_schema_prcdnce_ind,
p_usec_rec.federal_financial_aid,
p_usec_rec.unit_quota,
p_usec_rec.unit_quota_reserved_places,
p_usec_rec.institutional_financial_aid,
p_usec_rec.grading_schema_cd,
p_usec_rec.gs_version_number,
l_n_unit_contact_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_usec_rec.ss_enrol_ind,
NVL(p_usec_rec.owner_org_unit_cd,l_c_org_unit_cd),
p_usec_rec.attendance_required_ind,
p_usec_rec.reserved_seating_allowed,
p_usec_rec.special_permission_ind,
p_usec_rec.ss_display_ind,
p_usec_rec.rev_account_cd,
p_usec_rec.anon_unit_grading_ind,
p_usec_rec.anon_assess_grading_ind,
p_usec_rec.non_std_usec_ind,
p_usec_rec.auditable_ind,
p_usec_rec.audit_permission_ind,
p_usec_rec.not_multiple_section_flag,
l_n_sup_uoo_id,
l_c_relation_type,
p_usec_rec.default_enroll_flag,
'N'
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.record_inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
--If calling context is scheduling then update the interface table import done flag
IF p_calling_context = 'S' THEN
OPEN cur_int_pat(p_usec_rec.unit_cd,p_usec_rec.version_number,l_c_cal_type,l_n_seq_num);
UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = l_cur_int_pat.int_pat_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.interface_table_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'int_pat_id:'||l_cur_int_pat.int_pat_id);
ELSE --update
UPDATE igs_ps_unit_ofr_opt_all SET
ivrs_available_ind = p_usec_rec.ivrs_available_ind,
call_number=p_usec_rec.call_number,
unit_section_status=p_usec_rec.unit_section_status,
unit_section_start_date=p_usec_rec.unit_section_start_date,
unit_section_end_date=p_usec_rec.unit_section_end_date,
offered_ind=p_usec_rec.offered_ind,
state_financial_aid=p_usec_rec.state_financial_aid,
grading_schema_prcdnce_ind=p_usec_rec.grading_schema_prcdnce_ind,
federal_financial_aid=p_usec_rec.federal_financial_aid,
unit_quota=p_usec_rec.unit_quota,
unit_quota_reserved_places=p_usec_rec.unit_quota_reserved_places,
institutional_financial_aid=p_usec_rec.institutional_financial_aid,
grading_schema_cd=p_usec_rec.grading_schema_cd,
gs_version_number=p_usec_rec.gs_version_number,
unit_contact= l_n_unit_contact_id,
ss_enrol_ind=p_usec_rec.ss_enrol_ind,
owner_org_unit_cd=p_usec_rec.owner_org_unit_cd,
attendance_required_ind=p_usec_rec.attendance_required_ind,
reserved_seating_allowed=p_usec_rec.reserved_seating_allowed,
special_permission_ind=p_usec_rec.special_permission_ind,
ss_display_ind=p_usec_rec.ss_display_ind,
rev_account_cd=p_usec_rec.rev_account_cd,
anon_unit_grading_ind=p_usec_rec.anon_unit_grading_ind,
anon_assess_grading_ind=p_usec_rec.anon_assess_grading_ind,
non_std_usec_ind=p_usec_rec.non_std_usec_ind,
auditable_ind=p_usec_rec.auditable_ind,
audit_permission_ind=p_usec_rec.audit_permission_ind,
not_multiple_section_flag=p_usec_rec.not_multiple_section_flag,
sup_uoo_id = l_n_sup_uoo_id,
relation_type=l_c_relation_type,
default_enroll_flag=p_usec_rec.default_enroll_flag,
last_updated_by = g_n_user_id,
last_update_date= SYSDATE ,
last_update_login= g_n_login_id
WHERE unit_cd = p_usec_rec.unit_cd
AND version_number = p_usec_rec.version_number
AND ci_sequence_number =l_n_seq_num
AND unit_class = p_usec_rec.unit_class
AND location_cd = p_usec_rec.location_cd
AND cal_type = l_c_cal_type ;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.record_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
--If calling context is scheduling then update the interface table import done flag
IF p_calling_context = 'S' THEN
OPEN cur_int_usec(p_usec_rec.unit_cd,p_usec_rec.version_number,l_c_cal_type,l_n_seq_num,p_usec_rec.location_cd, p_usec_rec.unit_class);
UPDATE igs_ps_sch_usec_int_all set import_done_flag='Y' WHERE int_usec_id = l_cur_int_usec.int_usec_id;
UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = l_cur_int_usec.int_pat_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.interface_table_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'int_pat_id:'||l_cur_int_pat.int_pat_id);
END IF; --insert /update
PROCEDURE validate_cp_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
--sarakshi 15-May-2006 Bug#3064563, modified the format mask(clock_hours,continuing_education_units,work_load_cp_lecture,work_load_cp_lab,contact_hrs_lab) as specified in the bug.
BEGIN
IF p_insert_update = 'I' THEN
/* Check for Unique Key Constraints */
IF igs_ps_usec_cps_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_CPS', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update (p_usec_rec IN igs_ps_generic_pub.usec_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_cp(cp_n_uoo_id NUMBER) IS
SELECT *
FROM igs_ps_usec_cps
WHERE uoo_id = cp_n_uoo_id;
END check_insert_update;
p_insert_update VARCHAR2 ) AS
CURSOR c_usec_cp(p_n_uoo_id NUMBER) IS
SELECT *
FROM igs_ps_usec_cps
WHERE uoo_id = p_n_uoo_id;
IF p_insert_update = 'U' THEN
OPEN c_usec_cp(l_n_uoo_id);
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_check_insert_update',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
Assign_default(p_usec_rec,l_insert_update);
validate_cp_db_cons ( p_usec_rec,l_insert_update );
igs_ps_validate_lgcy_pkg.validate_cps ( p_usec_rec,l_n_uoo_id,l_insert_update ) ;
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_cps
(unit_sec_credit_points_id,
uoo_id,
minimum_credit_points,
maximum_credit_points,
variable_increment,
lecture_credit_points,
lab_credit_points,
other_credit_points,
clock_hours,
billing_credit_points,
work_load_cp_lecture,
work_load_cp_lab,
continuing_education_units,
achievable_credit_points,
enrolled_credit_points,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
work_load_other,
contact_hrs_lecture,
contact_hrs_lab,
contact_hrs_other,
billing_hrs,
non_schd_required_hrs,
exclude_from_max_cp_limit,
claimable_hours
)
VALUES
(igs_ps_usec_cps_s.NEXTVAL,
l_n_uoo_id,
p_usec_rec.minimum_credit_points,
p_usec_rec.maximum_credit_points,
p_usec_rec.variable_increment,
p_usec_rec.lecture_credit_points,
p_usec_rec.lab_credit_points,
p_usec_rec.other_credit_points,
p_usec_rec.clock_hours,
p_usec_rec.billing_credit_points,
p_usec_rec.work_load_cp_lecture,
p_usec_rec.work_load_cp_lab,
p_usec_rec.continuing_education_units,
p_usec_rec.achievable_credit_points,
p_usec_rec.enrolled_credit_points,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_usec_rec.work_load_other,
p_usec_rec.contact_hrs_lecture,
p_usec_rec.contact_hrs_lab,
p_usec_rec.contact_hrs_other,
p_usec_rec.billing_hrs,
p_usec_rec.non_schd_required_hrs,
p_usec_rec.exclude_from_max_cp_limit,
p_usec_rec.claimable_hours
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.record_inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
ELSE ---update
UPDATE igs_ps_usec_cps SET
minimum_credit_points =p_usec_rec.minimum_credit_points,
maximum_credit_points =p_usec_rec.maximum_credit_points,
variable_increment =p_usec_rec.variable_increment,
lecture_credit_points =p_usec_rec.lecture_credit_points,
lab_credit_points =p_usec_rec.lab_credit_points,
other_credit_points =p_usec_rec.other_credit_points,
clock_hours =p_usec_rec.clock_hours,
billing_credit_points =p_usec_rec.billing_credit_points,
work_load_cp_lecture =p_usec_rec.work_load_cp_lecture,
work_load_cp_lab =p_usec_rec.work_load_cp_lab,
continuing_education_units =p_usec_rec.continuing_education_units,
achievable_credit_points =p_usec_rec.achievable_credit_points,
enrolled_credit_points =p_usec_rec.enrolled_credit_points,
last_updated_by =g_n_user_id,
last_update_date =SYSDATE,
last_update_login =g_n_login_id,
work_load_other =p_usec_rec.work_load_other,
contact_hrs_lecture = p_usec_rec.contact_hrs_lecture,
contact_hrs_lab =p_usec_rec.contact_hrs_lab,
contact_hrs_other =p_usec_rec.contact_hrs_other,
billing_hrs =p_usec_rec.billing_hrs,
non_schd_required_hrs =p_usec_rec.non_schd_required_hrs,
exclude_from_max_cp_limit = p_usec_rec.exclude_from_max_cp_limit,
claimable_hours =p_usec_rec.claimable_hours
WHERE uoo_id = l_n_uoo_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.record_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
PROCEDURE validate_ref_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF p_insert_update = 'I' THEN
/* Check for Unique Key Constraints */
IF igs_ps_usec_ref_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_SEC_REF', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update (p_usec_rec IN igs_ps_generic_pub.usec_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_ref(p_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_usec_ref
WHERE uoo_id = p_n_uoo_id;
END check_insert_update;
p_insert_update VARCHAR2 ) AS
CURSOR c_usec_ref(p_n_uoo_id NUMBER) IS
SELECT *
FROM igs_ps_usec_ref
WHERE uoo_id = p_n_uoo_id;
IF p_insert_update = 'I' THEN
IF p_usec_rec.reference_subtitle_mod_flag IS NULL THEN
p_usec_rec.reference_subtitle_mod_flag := 'N';
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_check_insert_update',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
Assign_default(p_usec_rec,l_insert_update);
validate_ref_db_cons ( p_usec_rec,l_insert_update );
igs_ps_validate_lgcy_pkg.validate_ref ( p_usec_rec, l_n_subtitle_id ,l_n_uoo_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_ref
(unit_section_reference_id,
uoo_id,
short_title,
subtitle,
subtitle_modifiable_flag,
class_schedule_exclusion_flag,
registration_exclusion_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
record_exclusion_flag,
title,
subtitle_id
)
VALUES
(igs_ps_usec_ref_s.NEXTVAL,
l_n_uoo_id,
p_usec_rec.reference_short_title,
p_usec_rec.reference_subtitle,
p_usec_rec.reference_subtitle_mod_flag,
p_usec_rec.reference_class_sch_excl_flag,
null,
p_usec_rec.reference_attribute_category,
p_usec_rec.reference_attribute1,
p_usec_rec.reference_attribute2,
p_usec_rec.reference_attribute3,
p_usec_rec.reference_attribute4,
p_usec_rec.reference_attribute5,
p_usec_rec.reference_attribute6,
p_usec_rec.reference_attribute7,
p_usec_rec.reference_attribute8,
p_usec_rec.reference_attribute9,
p_usec_rec.reference_attribute10,
p_usec_rec.reference_attribute11,
p_usec_rec.reference_attribute12,
p_usec_rec.reference_attribute13,
p_usec_rec.reference_attribute14,
p_usec_rec.reference_attribute15,
p_usec_rec.reference_attribute16,
p_usec_rec.reference_attribute17,
p_usec_rec.reference_attribute18,
p_usec_rec.reference_attribute19,
p_usec_rec.reference_attribute20,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_usec_rec.reference_rec_exclusion_flag,
p_usec_rec.reference_title,
l_n_subtitle_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.record_inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
ELSE ---update
UPDATE igs_ps_usec_ref SET
short_title =p_usec_rec.reference_short_title,
subtitle =p_usec_rec.reference_subtitle,
subtitle_modifiable_flag =p_usec_rec.reference_subtitle_mod_flag,
class_schedule_exclusion_flag =p_usec_rec.reference_class_sch_excl_flag,
attribute_category =p_usec_rec.reference_attribute_category,
attribute1 =p_usec_rec.reference_attribute1,
attribute2 =p_usec_rec.reference_attribute2,
attribute3 =p_usec_rec.reference_attribute3,
attribute4 =p_usec_rec.reference_attribute4,
attribute5 =p_usec_rec.reference_attribute5,
attribute6 =p_usec_rec.reference_attribute6,
attribute7 =p_usec_rec.reference_attribute7,
attribute8 =p_usec_rec.reference_attribute8,
attribute9 =p_usec_rec.reference_attribute9,
attribute10 =p_usec_rec.reference_attribute10,
attribute11 =p_usec_rec.reference_attribute11,
attribute12 =p_usec_rec.reference_attribute12,
attribute13 =p_usec_rec.reference_attribute13,
attribute14 =p_usec_rec.reference_attribute14,
attribute15 =p_usec_rec.reference_attribute15,
attribute16 =p_usec_rec.reference_attribute16,
attribute17 =p_usec_rec.reference_attribute17,
attribute18 =p_usec_rec.reference_attribute18,
attribute19 =p_usec_rec.reference_attribute19,
attribute20 =p_usec_rec.reference_attribute20,
last_updated_by =g_n_user_id,
last_update_date =SYSDATE,
last_update_login =g_n_login_id,
title=p_usec_rec.reference_title,
record_exclusion_flag =p_usec_rec.reference_rec_exclusion_flag,
subtitle_id=l_n_subtitle_id
WHERE uoo_id = l_n_uoo_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.record_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
FUNCTION check_insert_update (p_usec_rec IN igs_ps_generic_pub.usec_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_lim(p_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = p_n_uoo_id;
END check_insert_update;
p_insert_update VARCHAR2 ) AS
CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
SELECT waitlist_allowed, max_students_per_waitlist
FROM igs_ps_unit_ofr_pat_all
WHERE unit_cd = cp_c_unit_cd
AND version_number = cp_n_ver_num
AND cal_type = cp_c_cal_type
AND ci_sequence_number = cp_n_seq_num ;
SELECT *
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = p_n_uoo_id;
IF p_insert_update = 'I' THEN
OPEN c_uop (p_usec_rec.unit_cd, p_usec_rec.version_number, l_c_cal_type, l_n_seq_num);
PROCEDURE validate_db_cons(p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF p_insert_update = 'I' THEN
IF igs_ps_usec_lim_wlst_pkg.get_uk_for_validation(l_n_uoo_id) THEN
p_usec_rec.status :='W';
--No need to insert/update the empty record
NULL ;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_check_insert_update',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
Assign_default(p_usec_rec,l_insert_update);
validate_db_cons(p_usec_rec,l_insert_update);
igs_ps_validate_lgcy_pkg.validate_usec_el (p_usec_rec,l_n_uoo_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_lim_wlst
(unit_section_limit_waitlist_id,
uoo_id,
enrollment_expected,
enrollment_minimum ,
enrollment_maximum ,
advance_maximum,
waitlist_allowed ,
max_students_per_waitlist,
override_enrollment_max,
max_auditors_allowed,
created_by ,
creation_date,
last_updated_by,
last_update_date ,
last_update_login
)
VALUES
(
igs_ps_usec_lim_wlst_s.nextval,
l_n_uoo_id,
p_usec_rec.enrollment_expected,
p_usec_rec.enrollment_minimum,
p_usec_rec.enrollment_maximum,
p_usec_rec.advance_maximum,
p_usec_rec.usec_waitlist_allowed,
p_usec_rec.usec_max_students_per_waitlist,
p_usec_rec.override_enrollment_maximum,
p_usec_rec.max_auditors_allowed,
g_n_user_id,
sysdate,
g_n_user_id,
sysdate,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.record_inserted',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
ELSE ---update
UPDATE igs_ps_usec_lim_wlst SET
enrollment_expected=p_usec_rec.enrollment_expected,
enrollment_minimum=p_usec_rec.enrollment_minimum ,
enrollment_maximum=p_usec_rec.enrollment_maximum ,
advance_maximum=p_usec_rec.advance_maximum,
waitlist_allowed=p_usec_rec.usec_waitlist_allowed ,
max_students_per_waitlist=p_usec_rec.usec_max_students_per_waitlist,
override_enrollment_max=p_usec_rec.override_enrollment_maximum,
max_auditors_allowed=p_usec_rec.max_auditors_allowed,
last_updated_by = g_n_user_id,
last_update_date= SYSDATE ,
last_update_login= g_n_login_id
WHERE uoo_id =l_n_uoo_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.record_updated',
'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
p_usec_rec.unit_class);
igs_ps_validate_lgcy_pkg.post_usec_limits(p_usec_rec,p_calling_context,l_n_uoo_id,l_insert_update);
Purpose : This procedure is a sub process to insert records of Unit Section Grading Schema.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_gs(p_grading_schema_code IN VARCHAR2,p_grd_schm_version_number NUMBER ,p_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_usec_grd_schm
WHERE grading_schema_code =p_grading_schema_code
AND grd_schm_version_number = p_grd_schm_version_number
AND uoo_id = p_n_uoo_id;
END check_insert_update;
PROCEDURE validate_db_cons ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_grd_schm_pkg.get_uk_for_validation ( x_grading_schema_code => p_usec_gs_rec.grading_schema_code,
x_grd_schm_version_number => p_usec_gs_rec.grd_schm_version_number,
x_uoo_id => l_n_uoo_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_GRADING_SCHM', 'LEGACY_TOKENS', FALSE);
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_tab_usec_gs(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_check_insert_update',
'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
validate_db_cons ( p_tab_usec_gs(I),l_insert_update );
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_grd_schm
(unit_section_grading_schema_id,
uoo_id,
grading_schema_code,
grd_schm_version_number,
default_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_ps_usec_grd_schm_s.NEXTVAL,
l_n_uoo_id,
p_tab_usec_gs(I).grading_schema_code,
p_tab_usec_gs(I).grd_schm_version_number,
p_tab_usec_gs(I).default_flag,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.record_inserted',
'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
||p_tab_usec_gs(I).grd_schm_version_number);
/*Update record*/
UPDATE igs_ps_usec_grd_schm
SET default_flag = p_tab_usec_gs(I).default_flag,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE grading_schema_code = p_tab_usec_gs(I).grading_schema_code
AND grd_schm_version_number = p_tab_usec_gs(I).grd_schm_version_number
AND uoo_id = l_n_uoo_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.record_updated',
'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
||p_tab_usec_gs(I).grd_schm_version_number);
END IF;--insert/update
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_lgcy_pkg.post_usec_grd_sch (p_tab_usec_gs,l_tbl_uoo) THEN
p_c_rec_status := 'E';
l_tbl_uoo.DELETE;
Purpose : This procedure is a sub process to insert records of Unit Section Occurrence.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 02-AUG-2006 Bug#5356402, Using a PL/SQl table l_tbl_uso to store USO that get inserted.
sommukhe 27-APR-2006 Bug#5122473,Modified the cursor check_ovrd to include Date override check
so that the Scheduling API considers Date Occurrence Override during import.
sommukhe 27-SEP-2005 Bug #4632652.Used cursor c_build_id to derive Building id.
smvk 25-jun-2003 Enh bug#2918094. Added column cancel_flag and its value will be 'N'
jbegum 3-June-2003 Enh Bug#2972950
For PSP Scheduling Enhancements TD:
Modified the local procedure's validate_derivation,validate_db_cons.
********************************************************************************************** */
l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
l_insert_update VARCHAR2(1);
SELECT building_id
FROM igs_ad_building_all
WHERE
building_cd = cp_building_cd AND
location_cd = cp_location_cd;
SELECT building_id
FROM igs_ad_building_all
WHERE building_cd = cp_building_cd;
SELECT room_id
FROM igs_ad_room_all
WHERE
room_cd = cp_room_cd AND
building_id = cp_building_id;
PROCEDURE validate_db_cons ( p_uso_rec IN OUT NOCOPY igs_ps_generic_pub.uso_rec_type,p_insert IN VARCHAR2 ) AS
BEGIN
/* Validate Check Constraints */
-- Following validation added as part of bug#2972950 for the PSP Scheduling Enhancements TD
-- If No Set Day Indicator is not NULL then it should have a value of either of 'Y' or 'N'
BEGIN
igs_ps_usec_occurs_pkg.check_constraints ( 'NO_SET_DAY_IND', p_uso_rec.no_set_day_ind );
IF p_insert = 'I' THEN
/* Unique Key Validation */
IF igs_ps_usec_occurs_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id ,
x_occurrence_identifier => p_uso_rec.occurrence_identifier
) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_OCCRS_ID', 'IGS_PS_LOG_PARAMETERS', FALSE);
FUNCTION check_insert_update ( p_uso_rec IN OUT NOCOPY igs_ps_generic_pub.uso_rec_type ) RETURN VARCHAR2 IS
CURSOR c_occr IS
SELECT unit_section_occurrence_id
FROM igs_ps_usec_occurs_all
WHERE uoo_id = l_n_uoo_id
AND occurrence_identifier=p_uso_rec.occurrence_identifier;
END check_insert_update;
PROCEDURE assign_defaults ( p_uso_rec IN OUT NOCOPY igs_ps_generic_pub.uso_rec_type, p_insert IN VARCHAR2) IS
CURSOR cur_usec_ocurs ( cp_uso_id IN NUMBER) IS
SELECT *
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_uso_id;
SELECT room_id
FROM igs_ad_room
WHERE room_cd=cp_room_cd
AND building_id=cp_building_id;
IF p_insert = 'I' THEN
-- If To Be Announced is Null then default it to 'N'
IF ( p_uso_rec.to_be_announced IS NULL ) THEN
p_uso_rec.to_be_announced := 'N';
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_tab_usec_occur(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_check_insert_update',
'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
'Status:'||p_tab_usec_occur(I).status);
assign_defaults(p_tab_usec_occur(I),l_insert_update);
validate_db_cons ( p_tab_usec_occur(I),l_insert_update);
l_insert_update,p_calling_context,l_notify_status,l_c_schedule_status
);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_occurs_all
(unit_section_occurrence_id,
uoo_id,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
sunday,
start_time,
end_time,
building_code,
room_code,
schedule_status,
status_last_updated,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
dedicated_building_code,
dedicated_room_code,
preferred_building_code,
preferred_room_code,
start_date,
end_date,
to_be_announced,
preferred_region_code,
no_set_day_ind,
cancel_flag,
occurrence_identifier,
abort_flag
)
VALUES
(igs_ps_usec_occurs_s.NEXTVAL,
l_n_uoo_id,
p_tab_usec_occur(I).monday,
p_tab_usec_occur(I).tuesday,
p_tab_usec_occur(I).wednesday,
p_tab_usec_occur(I).thursday,
p_tab_usec_occur(I).friday,
p_tab_usec_occur(I).saturday,
p_tab_usec_occur(I).sunday,
p_tab_usec_occur(I).start_time,
p_tab_usec_occur(I).end_time,
l_n_building_code,
l_n_room_code,
l_c_schedule_status,
TRUNC(SYSDATE),
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_tab_usec_occur(I).attribute_category,
p_tab_usec_occur(I).attribute1,
p_tab_usec_occur(I).attribute2,
p_tab_usec_occur(I).attribute3,
p_tab_usec_occur(I).attribute4,
p_tab_usec_occur(I).attribute5,
p_tab_usec_occur(I).attribute6,
p_tab_usec_occur(I).attribute7,
p_tab_usec_occur(I).attribute8,
p_tab_usec_occur(I).attribute9,
p_tab_usec_occur(I).attribute10,
p_tab_usec_occur(I).attribute11,
p_tab_usec_occur(I).attribute12,
p_tab_usec_occur(I).attribute13,
p_tab_usec_occur(I).attribute14,
p_tab_usec_occur(I).attribute15,
p_tab_usec_occur(I).attribute16,
p_tab_usec_occur(I).attribute17,
p_tab_usec_occur(I).attribute18,
p_tab_usec_occur(I).attribute19,
p_tab_usec_occur(I).attribute20,
l_n_dedicated_building_code,
l_n_dedicated_room_code,
l_n_preferred_building_code,
l_n_preferred_room_code,
p_tab_usec_occur(I).start_date,
p_tab_usec_occur(I).end_date,
p_tab_usec_occur(I).to_be_announced,
p_tab_usec_occur(I).preferred_region_code,
p_tab_usec_occur(I).no_set_day_ind,
'N',
p_tab_usec_occur(I).occurrence_identifier,
'N'
)RETURNING unit_section_occurrence_id INTO l_n_usec_occurs_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.record_inserted',
'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier);
SELECT rowid,uso.*
FROM igs_ps_usec_occurs_all uso
WHERE uso.unit_section_occurrence_id=cp_n_uso_id;
SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag,date_ovrd_flag
FROM igs_ps_sch_ocr_cfig;
/*Update record*/
UPDATE IGS_PS_USEC_OCCURS_ALL SET
monday = l_c_monday,
tuesday = l_c_tuesday,
wednesday = l_c_wednesday,
thursday =l_c_thursday,
friday = l_c_friday,
saturday = l_c_saturday,
sunday = l_c_sunday,
start_time = l_start_time,
end_time = l_end_time,
building_code = l_sch_bld,
room_code = l_sch_room,
schedule_status = NVL(l_c_schedule_status,schedule_status),
error_text = DECODE(l_c_schedule_status,'SCHEDULED',NULL,'CANCELLED',NULL,error_text),
notify_status = NVL(l_notify_status,notify_status),
status_last_updated = TRUNC(SYSDATE),
last_updated_by=g_n_user_id,
last_update_date=SYSDATE,
last_update_login=g_n_login_id,
attribute_category=p_tab_usec_occur(I).attribute_category,
attribute1=p_tab_usec_occur(I).attribute1,
attribute2=p_tab_usec_occur(I).attribute2,
attribute3=p_tab_usec_occur(I).attribute3,
attribute4=p_tab_usec_occur(I).attribute4,
attribute5=p_tab_usec_occur(I).attribute5,
attribute6=p_tab_usec_occur(I).attribute6,
attribute7=p_tab_usec_occur(I).attribute7,
attribute8=p_tab_usec_occur(I).attribute8,
attribute9=p_tab_usec_occur(I).attribute9,
attribute10=p_tab_usec_occur(I).attribute10,
attribute11=p_tab_usec_occur(I).attribute11,
attribute12=p_tab_usec_occur(I).attribute12,
attribute13=p_tab_usec_occur(I).attribute13,
attribute14=p_tab_usec_occur(I).attribute14,
attribute15=p_tab_usec_occur(I).attribute15,
attribute16=p_tab_usec_occur(I).attribute16,
attribute17=p_tab_usec_occur(I).attribute17,
attribute18=p_tab_usec_occur(I).attribute18,
attribute19=p_tab_usec_occur(I).attribute19,
attribute20=p_tab_usec_occur(I).attribute20,
dedicated_building_code=l_n_dedicated_building_code,
dedicated_room_code=l_n_dedicated_room_code,
preferred_building_code=l_n_preferred_building_code,
preferred_room_code=l_n_preferred_room_code,
start_date=l_d_uso_start_date,
end_date=l_d_uso_end_date,
to_be_announced=p_tab_usec_occur(I).to_be_announced,
preferred_region_code=p_tab_usec_occur(I).preferred_region_code,
no_set_day_ind=p_tab_usec_occur(I).no_set_day_ind
WHERE unit_section_occurrence_id = l_n_uso_id ;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.record_updated',
'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier);
l_insert_update VARCHAR2(1);
SELECT restricted_flag
FROM igs_ge_ref_cd_type_all
WHERE reference_cd_type = cp_c_ref_type;
SELECT A.unit_section_reference_id
FROM igs_ps_usec_ref A
WHERE A.uoo_id = cp_uoo_id;
SELECT rc.description
FROM igs_ge_ref_cd_type_all rct,
igs_ge_ref_cd rc
WHERE rct.reference_cd_type = cp_c_ref_type
AND rct.reference_cd_type = rc.reference_cd_type
AND rc.reference_cd = cp_c_ref_cd
AND rct.restricted_flag = 'Y';
PROCEDURE validate_usec_db_cons(p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type,p_insert IN VARCHAR2) AS
BEGIN
IF p_insert = 'I' THEN
--Unique Key Validation
--Enh#2972950,impact of change of signature of igs_ps_usec_ref_cd_pkg.get_uk_for_validation
IF p_ref_cd_rec.gen_ref_flag = 'Y' THEN -- if generic reference code
IF igs_ps_usec_ref_cd_pkg.get_uk_for_validation(l_n_usec_ref_id,p_ref_cd_rec.reference_cd_type,p_ref_cd_rec.reference_cd) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS',
igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_SECTION','LEGACY_TOKENS') || ' ' ||
igs_ps_validate_lgcy_pkg.get_lkup_meaning('REFERENCE_CD','LEGACY_TOKENS'),
NULL, FALSE);
PROCEDURE validate_occur_db_cons(p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type,p_insert IN VARCHAR2) AS
BEGIN
IF p_insert = 'I' THEN
-- Unique Key Validation
IF igs_ps_usec_ocur_ref_pkg.get_uk_for_validation( p_ref_cd_rec.reference_cd_type ,
p_ref_cd_rec.reference_cd ,
l_n_uso_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS',
igs_ps_validate_lgcy_pkg.get_lkup_meaning('USEC_OCCUR','LEGACY_TOKENS') || ' ' ||
igs_ps_validate_lgcy_pkg.get_lkup_meaning('REFERENCE_CD','LEGACY_TOKENS'),
NULL, FALSE);
PROCEDURE validate_db_cons( p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type,p_insert IN VARCHAR2) AS
BEGIN
IF p_ref_cd_rec.data_type = 'UNIT' THEN
validate_unit_db_cons(p_ref_cd_rec);
validate_usec_db_cons(p_ref_cd_rec,p_insert);
validate_occur_db_cons(p_ref_cd_rec,p_insert);
FUNCTION check_insert_update ( p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type ) RETURN VARCHAR2 IS
--For IGS_PS_USEC_REF_CD record
CURSOR c_gen IS
SELECT rc.restricted_flag,ur.reference_code_desc
FROM igs_ps_usec_ref_cd ur, igs_ge_ref_cd_type_all rc
WHERE ur.unit_section_reference_id = l_n_usec_ref_id
AND ur.reference_code_type = p_ref_cd_rec.reference_cd_type
AND ur.reference_code = p_ref_cd_rec.reference_cd
AND ur.reference_code_type=rc.reference_cd_type;
SELECT rc.restricted_flag,urr.reference_code_desc
FROM igs_ps_us_req_ref_cd urr,igs_ge_ref_cd_type_all rc
WHERE urr.unit_section_reference_id = l_n_usec_ref_id
AND urr.reference_cd_type = p_ref_cd_rec.reference_cd_type
AND urr.reference_code = p_ref_cd_rec.reference_cd
AND urr.reference_cd_type=rc.reference_cd_type;
SELECT rc.restricted_flag,uo.reference_code_desc
FROM igs_ps_usec_ocur_ref uo,igs_ge_ref_cd_type_all rc
WHERE uo.reference_code_type = p_ref_cd_rec.reference_cd_type
AND uo.reference_code = p_ref_cd_rec.reference_cd
AND uo.unit_section_occurrence_id = l_n_uso_id
AND uo.reference_code_type=rc.reference_cd_type;
END check_insert_update;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_tab_ref_cd(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_check_insert_update',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag||' '||'occurrence_identifier'||p_tab_ref_cd(i).occurrence_identifier
||' '||'production_uso_id:'||p_tab_ref_cd(i).production_uso_id||' '||'Status:'||p_tab_ref_cd(i).status);
validate_db_cons(p_tab_ref_cd(i),l_insert_update);
-- Insert the reference codes in appropriate table based on datatype value.
IF p_tab_ref_cd(i).status = 'S' THEN
IF p_tab_ref_cd(i).data_type = 'UNIT' THEN
IF p_tab_ref_cd(i).gen_ref_flag = 'Y' THEN -- if generic reference code
-- Creating the unit refernce code if the datatype is UNIT.
INSERT INTO igs_ps_unit_ref_cd (
UNIT_CD,
VERSION_NUMBER,
REFERENCE_CD_TYPE,
REFERENCE_CD,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
p_tab_ref_cd(i).unit_cd,
p_tab_ref_cd(i).version_number,
p_tab_ref_cd(i).reference_cd_type,
p_tab_ref_cd(i).reference_cd,
p_tab_ref_cd(i).description,
sysdate,
g_n_user_id,
sysdate,
g_n_user_id,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_unit_ref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||
'reference_cd:'||p_tab_ref_cd(i).reference_cd||' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
INSERT INTO igs_ps_unitreqref_cd (
unit_req_ref_cd_id,
unit_cd,
version_number,
reference_cd_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
reference_code,
reference_code_desc
) VALUES (
igs_ps_unitreqref_cd_s.NEXTVAL,
p_tab_ref_cd(i).unit_cd,
p_tab_ref_cd(i).version_number,
p_tab_ref_cd(i).reference_cd_type,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id,
p_tab_ref_cd(i).reference_cd,
p_tab_ref_cd(i).description
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_unitreqref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||
'reference_cd:'||p_tab_ref_cd(i).reference_cd||' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
--in the INSERT of table igs_ps_usec_ref_cd
--Also deleted column reference_code_id in the INSERT of table igs_ps_usec_ref_cd
-- Creating the unit section refernce code if the datatype is SECTION.
IF l_insert_update = 'I' THEN
INSERT INTO igs_ps_usec_ref_cd (
unit_section_reference_cd_id,
unit_section_reference_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
reference_code_type,
reference_code,
reference_code_desc
) VALUES (
igs_ps_usec_ref_cd_s.nextval,
l_n_usec_ref_id,
sysdate,
g_n_user_id,
sysdate,
g_n_user_id,
g_n_login_id,
p_tab_ref_cd(i).reference_cd_type,
p_tab_ref_cd(i).reference_cd,
p_tab_ref_cd(i).description
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_usec_ref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
UPDATE igs_ps_usec_ref_cd SET reference_code_desc=p_tab_ref_cd(i).description,last_updated_by=g_n_user_id,
last_update_date=SYSDATE WHERE unit_section_reference_id=l_n_usec_ref_id AND
reference_code_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Updated_igs_ps_usec_ref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
IF l_insert_update = 'I' THEN
INSERT INTO igs_ps_us_req_ref_cd (
unit_section_req_ref_cd_id,
unit_section_reference_id,
reference_cd_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
reference_code,
reference_code_desc
) VALUES (
igs_ps_us_req_ref_cd_s.NEXTVAL,
l_n_usec_ref_id,
p_tab_ref_cd(i).reference_cd_type,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_user_id,
g_n_login_id,
p_tab_ref_cd(i).reference_cd,
p_tab_ref_cd(i).description
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_us_req_ref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
UPDATE igs_ps_us_req_ref_cd SET reference_code_desc=p_tab_ref_cd(i).description,last_updated_by=g_n_user_id,
last_update_date=SYSDATE WHERE unit_section_reference_id=l_n_usec_ref_id AND
reference_cd_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_updated_igs_ps_us_req_ref_cd',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
--Added the reference_code_desc column in the INSERT of table igs_ps_usec_ocur_ref
--Creating the unit section occurrence refernce code if the datatype is OCCURRENCE.
IF l_insert_update = 'I' THEN
INSERT INTO igs_ps_usec_ocur_ref (
unit_sec_occur_reference_id,
unit_section_occurrence_id,
reference_code_type,
reference_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
reference_code_desc
) VALUES (
igs_ps_usec_occur_ref_s.nextval,
l_n_uso_id,
p_tab_ref_cd(i).reference_cd_type,
p_tab_ref_cd(i).reference_cd,
sysdate,
g_n_user_id,
sysdate,
g_n_user_id,
g_n_login_id,
p_tab_ref_cd(i).description
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_inserted_igs_ps_usec_ocur_ref',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag||' '||'occurrence_identifier'||p_tab_ref_cd(i).occurrence_identifier
||' '||'production_uso_id:'||p_tab_ref_cd(i).production_uso_id);
UPDATE igs_ps_usec_ocur_ref SET reference_code_desc=p_tab_ref_cd(i).description,last_updated_by=g_n_user_id,
last_update_date=SYSDATE WHERE unit_section_occurrence_id=l_n_uso_id AND
reference_code_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_updated_igs_ps_usec_ocur_ref',
'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag||' '||'occurrence_identifier'||p_tab_ref_cd(i).occurrence_identifier
||' '||'production_uso_id:'||p_tab_ref_cd(i).production_uso_id);
smvk 30-Aug-2004 Bug # 3862086. Modified Return to Exit. Post insert business logic needs to be executed Always.
This internally clears the package level PL/SQL table v_tab_usec_tr.
smvk 23-Sep-2003 Bug # 3121311, Removed the call to procedures uso_effective_dates and validate_instructor.
jbegum 4-June-2003 Enh Bug#2972950
For PSP Scheduling Enhancements TD:
Modified local procedures validate_db_cons and validate_derivation.
***********************************************************************************************/
l_n_ins_id igs_ps_uso_instrctrs.instructor_id%TYPE;
SELECT uso.unit_section_occurrence_id
FROM igs_ps_usec_occurs_all uso
WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
AND uso.no_set_day_ind ='N'
AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
SELECT A.uoo_id
FROM igs_ps_usec_occurs_all A
WHERE A.unit_section_occurrence_id = cp_n_uso_id;
SELECT a.unit_cd, a.version_number
FROM igs_ps_unit_ofr_opt_all a, igs_ps_usec_occurs_all b
WHERE a.uoo_id = b.uoo_id
AND b.unit_section_occurrence_id = cp_n_uso_id;
SELECT 'X'
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_uso_id
AND schedule_status = 'PROCESSING';
INSERT INTO IGS_PS_USO_INSTRCTRS (
USO_INSTRUCTOR_ID,
UNIT_SECTION_OCCURRENCE_ID,
INSTRUCTOR_ID,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
) VALUES (
igs_ps_uso_instrctrs_s.nextval,
l_n_uso_id,
l_n_ins_id,
g_n_user_id,
sysdate,
g_n_user_id,
sysdate,
g_n_login_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.record_inserted',
'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
||' '||'production_uso_id:'||p_tab_uso_ins(I).production_uso_id);
--Update the schedule status of the occurrence to USER_UPDATE if inserting a record
--Note this validation cannot be pushed in the post validation as that is also called from the instructor override
IF NOT usoexists(l_n_uso_id) THEN
FOR l_occurs_rec IN c_occurs(l_n_uso_id) LOOP
igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
l_tbl_uso.DELETE;