[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
Purpose : This procedure is a sub process to insert records of Unit Section Reserve Seating.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
--sommukhe 12-AUG-2005 Bug#4377818,changed the cursor cur_hzp, included table igs_pe_hz_parties in
-- FROM clause and modified the WHERE clause by joining HZ_PARTIES and IGS_PE_HZ_PARTIES
-- using party_id and org unit being compared with oss_org_unit_cd of IGS_PE_HZ_PARTIES.
********************************************************************************************** */
/* Private Procedures for create_usec_res_seat */
l_insert_update VARCHAR2(1);
PROCEDURE validate_derivations_pri ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_insert_update VARCHAR2 ) AS
l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
FUNCTION check_insert_update ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_n_rsv_usec_pri_id NUMBER) RETURN VARCHAR2 IS
CURSOR c_usec_rsv_pri(cp_n_uoo_id NUMBER,cp_priority_value VARCHAR2) IS
SELECT 'X'
FROM igs_ps_rsv_usec_pri
WHERE uoo_id = cp_n_uoo_id
AND priority_value = cp_priority_value;
END check_insert_update;
PROCEDURE Assign_default(p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_usprv(cp_n_uoo_id NUMBER,cp_priority_value VARCHAR2) IS
SELECT priority_order
FROM igs_ps_rsv_usec_pri
WHERE uoo_id = cp_n_uoo_id
AND priority_value = cp_priority_value;
IF p_insert_update = 'U' THEN
OPEN c_usprv( l_n_uoo_id,p_usec_rsv_rec.priority_value);
PROCEDURE validate_db_cons_rsvpri ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF(p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_rsv_usec_pri_pkg.get_uk_for_validation (x_uoo_id => l_n_uoo_id,
x_priority_value =>p_usec_rsv_rec.priority_value ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_RSV_PRI', 'LEGACY_TOKENS', FALSE);
validate_derivations_pri(p_usec_rsv_rec,l_insert_update);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rsv_rec,l_n_rsv_usec_pri_id);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_res_seat.create_rsvpri.status_after_check_insert_update',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value||' '||'Status:'
||p_usec_rsv_rec.status);
Assign_default(p_usec_rsv_rec,l_insert_update);
validate_db_cons_rsvpri(p_usec_rsv_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_usec_rsvpri (p_usec_rsv_rec,l_n_uoo_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_rsv_usec_pri
(
rsv_usec_pri_id,
uoo_id,
priority_order,
priority_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
igs_ps_rsv_usec_pri_s.nextval,
l_n_uoo_id,
p_usec_rsv_rec.priority_order,
p_usec_rsv_rec.priority_value,
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_create_generic_pkg.create_usec_res_seat.create_rsvpri.Record_Inserted',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value);
ELSE --update
UPDATE igs_ps_rsv_usec_pri SET
priority_order= p_usec_rsv_rec.priority_order,
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 AND priority_value = p_usec_rsv_rec.priority_value;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_res_seat.create_rsvpri.Record_Updated',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value);
PROCEDURE validate_derivations_prf ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_insert_update VARCHAR2 ) AS
l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
SELECT rsv_usec_pri_id
FROM igs_ps_rsv_usec_pri
WHERE uoo_id = cp_uoo_id
AND priority_value = cp_priority_value;
FUNCTION check_insert_update ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_n_rsv_usec_pri_id NUMBER) RETURN VARCHAR2 IS
CURSOR c_usec_rsv_prf(p_rsv_usec_pri_id NUMBER,p_preference_code VARCHAR2) IS
SELECT 'X'
FROM igs_ps_rsv_usec_prf
WHERE rsv_usec_pri_id = p_rsv_usec_pri_id
AND preference_code = p_preference_code;
SELECT 'X'
FROM igs_ps_rsv_usec_prf
WHERE rsv_usec_pri_id = cp_rsv_usec_pri_id
AND preference_code = cp_preference_code
AND preference_version = cp_preference_version;
END check_insert_update;
PROCEDURE validate_db_cons_rsvprf ( p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR cur_hzp(cp_preference_code VARCHAR2) IS
SELECT 'x'
FROM hz_parties hp, igs_pe_hz_parties pe
WHERE hp.party_id = pe.party_id
AND pe.oss_org_unit_cd =cp_preference_code;
SELECT group_id
FROM igs_pe_persid_group_all
WHERE group_cd = cp_preference_code;
IF(p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_rsv_usec_prf_pkg.get_uk_for_validation(x_rsv_usec_pri_id => l_n_rsv_usec_pri_id ,
x_preference_code =>p_usec_rsv_rec.preference_code ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_RSV_PRF', 'LEGACY_TOKENS', FALSE);
validate_derivations_prf(p_usec_rsv_rec,l_insert_update);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rsv_rec,l_n_rsv_usec_pri_id);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_res_seat.create_rsvprf.status_after_check_insert_update',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value||' '||
'Preference Code:'||p_usec_rsv_rec.preference_code||' '||'Status:'||p_usec_rsv_rec.status);
validate_db_cons_rsvprf(p_usec_rsv_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_usec_rsvprf (p_usec_rsv_rec,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_rsv_usec_prf (
rsv_usec_prf_id,
rsv_usec_pri_id,
preference_order,
preference_code,
preference_version,
percentage_reserved,
group_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_ps_rsv_usec_prf_s.NEXTVAL,
l_n_rsv_usec_pri_id,
p_usec_rsv_rec.preference_order,
p_usec_rsv_rec.preference_code,
p_usec_rsv_rec.preference_version,
p_usec_rsv_rec.percentage_reserved,
l_n_group_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_create_generic_pkg.create_usec_res_seat.create_rsvprf.status_after_Record_Inserted',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value||' '||
'Preference Code:'||p_usec_rsv_rec.preference_code);
ELSE --update
UPDATE igs_ps_rsv_usec_prf SET
preference_order= p_usec_rsv_rec.preference_order,
percentage_reserved=p_usec_rsv_rec.percentage_reserved,
preference_version=p_usec_rsv_rec.preference_version,
last_updated_by = g_n_user_id,
last_update_date= SYSDATE ,
last_update_login= g_n_login_id
WHERE rsv_usec_pri_id =l_n_rsv_usec_pri_id AND preference_code = p_usec_rsv_rec.preference_code;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_res_seat.create_rsvprf.status_after_Record_Updated',
'Unit code:'||p_usec_rsv_rec.unit_cd||' '||'Version number:'||p_usec_rsv_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_rsv_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rsv_rec.location_cd||' '||'Unit Class:'||
p_usec_rsv_rec.unit_class||' '||'Priority Value:'||p_usec_rsv_rec.priority_value||' '||
'Preference Code:'||p_usec_rsv_rec.preference_code);
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_usec_rsv(p_usec_res_seat_tbl,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 Facility.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
SELECT unit_section_occurrence_id
FROM igs_ps_usec_occurs_all
WHERE uoo_id = cp_n_uoo_id
AND occurrence_identifier = cp_occurrence_identifier;
/* Insert record */
INSERT INTO igs_ps_uso_facility
(uso_facility_id,
unit_section_occurrence_id,
facility_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(IGS_PS_USO_FACILITY_S.nextval,
l_n_uso_id,
p_usec_occurs_facility_tbl(I).facility_code,
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_create_generic_pkg.create_uso_facility.Record_Inserted',
'Unit code:'||p_usec_occurs_facility_tbl(I).unit_cd||' '||'Version number:'||p_usec_occurs_facility_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_occurs_facility_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_occurs_facility_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_occurs_facility_tbl(I).unit_class||' '||'Facility Code:'||p_usec_occurs_facility_tbl(I).facility_code||'Unit Section Occurrence id:'
||l_n_uso_id);
END IF;--insert
Purpose : This procedure is a sub process to insert records of Unit Section Catogories.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
/* Insert record */
INSERT INTO igs_ps_usec_category
(usec_cat_id,
uoo_id,
unit_cat,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_ps_usec_category_s.nextval,
l_n_uoo_id,
p_usec_cat_tbl(I).unit_cat,
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_create_generic_pkg.create_usec_cat.Record_Inserted',
'Unit code:'||p_usec_cat_tbl(I).unit_cd||' '||'Version number:'||p_usec_cat_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_cat_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_cat_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_cat_tbl(I).unit_class||' '||'Unit_Cat:'||p_usec_cat_tbl(I).unit_cat);
END IF; --insert
l_insert_update VARCHAR2(1);
SELECT *
FROM igs_ps_tch_resp_ovrd_all
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
AND location_cd=p_location_cd
AND unit_class=p_unit_class
AND org_unit_cd=p_org_unit_cd
AND ou_start_dt =p_ou_start_dt;
FUNCTION check_insert_update ( p_tch_rsp_ovrd_rec IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_rec_type ) RETURN VARCHAR2 IS
CURSOR c_tch_resp_ovrd(p_unit_cd IN VARCHAR2,
p_version_number NUMBER,
p_cal_type VARCHAR2,
p_ci_sequence_number NUMBER,
p_location_cd VARCHAR2,
p_unit_class VARCHAR2,
p_org_unit_cd VARCHAR2,
p_ou_start_dt DATE
) IS
SELECT 'X'
FROM igs_ps_tch_resp_ovrd_all
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
AND location_cd=p_location_cd
AND unit_class=p_unit_class
AND org_unit_cd=p_org_unit_cd
AND ou_start_dt =p_ou_start_dt;
END check_insert_update;
PROCEDURE validate_db_cons ( p_tch_rsp_ovrd_rec IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_tch_resp_ovrd_pkg.get_pk_for_validation ( x_unit_cd => p_tch_rsp_ovrd_rec.unit_cd,
x_version_number => p_tch_rsp_ovrd_rec.version_number,
x_cal_type=>l_c_cal_type,
x_ci_sequence_number=>l_n_seq_num,
x_location_cd=>p_tch_rsp_ovrd_rec.location_cd,
x_unit_class=>p_tch_rsp_ovrd_rec.unit_class,
x_org_unit_cd=>p_tch_rsp_ovrd_rec.org_unit_cd,
x_ou_start_dt =>p_tch_rsp_ovrd_rec.ou_start_dt ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
---INSERT /UPDATE
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_teach_resp_ovrd_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_teach_resp_ovrd.status_after_check_insert_update',
'Unit code:'||p_usec_teach_resp_ovrd_tbl(I).unit_cd||' '||'Version number:'||p_usec_teach_resp_ovrd_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_teach_resp_ovrd_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_teach_resp_ovrd_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_teach_resp_ovrd_tbl(I).unit_class||' '||'org_unit_cd:'||p_usec_teach_resp_ovrd_tbl(I).org_unit_cd
||' '||'ou_start_dt'||p_usec_teach_resp_ovrd_tbl(I).ou_start_dt||' '||'Status:'||p_usec_teach_resp_ovrd_tbl(I).status);
validate_db_cons ( p_usec_teach_resp_ovrd_tbl(I),l_insert_update );
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_tch_resp_ovrd_all
(unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
org_unit_cd,
ou_start_dt,
uoo_id,
percentage,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_usec_teach_resp_ovrd_tbl(I).unit_cd,
p_usec_teach_resp_ovrd_tbl(I).version_number,
l_c_cal_type,
l_n_seq_num,
p_usec_teach_resp_ovrd_tbl(I).location_cd,
p_usec_teach_resp_ovrd_tbl(I).unit_class,
p_usec_teach_resp_ovrd_tbl(I).org_unit_cd,
p_usec_teach_resp_ovrd_tbl(I).ou_start_dt,
l_n_uoo_id,
p_usec_teach_resp_ovrd_tbl(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_create_generic_pkg.create_usec_teach_resp_ovrd.Record_Inserted',
'Unit code:'||p_usec_teach_resp_ovrd_tbl(I).unit_cd||' '||'Version number:'||p_usec_teach_resp_ovrd_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_teach_resp_ovrd_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_teach_resp_ovrd_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_teach_resp_ovrd_tbl(I).unit_class||' '||'org_unit_cd:'||p_usec_teach_resp_ovrd_tbl(I).org_unit_cd
||' '||'ou_start_dt'||p_usec_teach_resp_ovrd_tbl(I).ou_start_dt);
/*Update record*/
--Opening the cursor to fetch the existing data wich will be used in the history table insert
--Note this fetch needs to be done prior to the update statement
OPEN c_tch_rsp_ovrd(p_usec_teach_resp_ovrd_tbl(I).unit_cd,
p_usec_teach_resp_ovrd_tbl(I).version_number,
l_c_cal_type,
l_n_seq_num,
p_usec_teach_resp_ovrd_tbl(I).location_cd,
p_usec_teach_resp_ovrd_tbl(I).unit_class,
p_usec_teach_resp_ovrd_tbl(I).org_unit_cd,
p_usec_teach_resp_ovrd_tbl(I).ou_start_dt);
UPDATE igs_ps_tch_resp_ovrd_all
SET percentage = p_usec_teach_resp_ovrd_tbl(I).percentage,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE unit_cd = p_usec_teach_resp_ovrd_tbl(I).unit_cd
AND version_number= p_usec_teach_resp_ovrd_tbl(I).version_number
AND cal_type=l_c_cal_type
AND ci_sequence_number=l_n_seq_num
AND location_cd=p_usec_teach_resp_ovrd_tbl(I).location_cd
AND unit_class=p_usec_teach_resp_ovrd_tbl(I).unit_class
AND org_unit_cd=p_usec_teach_resp_ovrd_tbl(I).org_unit_cd
AND ou_start_dt =p_usec_teach_resp_ovrd_tbl(I).ou_start_dt;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_teach_resp_ovrd.Record_Updated',
'Unit code:'||p_usec_teach_resp_ovrd_tbl(I).unit_cd||' '||'Version number:'||p_usec_teach_resp_ovrd_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_teach_resp_ovrd_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_teach_resp_ovrd_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_teach_resp_ovrd_tbl(I).unit_class||' '||'org_unit_cd:'||p_usec_teach_resp_ovrd_tbl(I).org_unit_cd
||' '||'ou_start_dt'||p_usec_teach_resp_ovrd_tbl(I).ou_start_dt);
c_tch_rsp_ovrd_rec.last_updated_by,
SYSDATE,
c_tch_rsp_ovrd_rec.last_update_date);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_teach_resp_ovrd.Record_Inserted_into_history_table',
'Unit code:'||p_usec_teach_resp_ovrd_tbl(I).unit_cd||' '||'Version number:'||p_usec_teach_resp_ovrd_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_teach_resp_ovrd_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_teach_resp_ovrd_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_teach_resp_ovrd_tbl(I).unit_class||' '||'org_unit_cd:'||p_usec_teach_resp_ovrd_tbl(I).org_unit_cd
||' '||'ou_start_dt'||p_usec_teach_resp_ovrd_tbl(I).ou_start_dt);
END IF;--insert/update
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_tch_rsp_ovrd (p_usec_teach_resp_ovrd_tbl,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 assessment item groups.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_as_us_ai_group_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type ) RETURN VARCHAR2 IS
CURSOR c_us_ai_group(cp_group_name VARCHAR2 ,cp_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_as_us_ai_group
WHERE group_name = cp_group_name
AND uoo_id = cp_n_uoo_id;
END check_insert_update;
PROCEDURE Assign_default( p_as_us_ai_group_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_us_ai_group(cp_group_name VARCHAR2 ,cp_n_uoo_id NUMBER) IS
SELECT *
FROM igs_as_us_ai_group
WHERE group_name = cp_group_name
AND uoo_id = cp_n_uoo_id;
IF p_insert_update = 'U' THEN
OPEN c_us_ai_group(p_as_us_ai_group_rec.group_name, l_n_uoo_id);
PROCEDURE validate_db_cons ( p_as_us_ai_group_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_as_us_ai_group_pkg.get_uk_for_validation (x_uoo_id => l_n_uoo_id,
x_group_name => p_as_us_ai_group_rec.group_name) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_ASSMNT', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_as_us_ai_group_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ass_item_grp.create_group.status_after_check_insert_update',
'Unit code:'||p_as_us_ai_group_rec.unit_cd||' '||'Version number:'||p_as_us_ai_group_rec.version_number||' '||'teach_cal_alternate_code:'
||p_as_us_ai_group_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_as_us_ai_group_rec.location_cd||' '||'Unit Class:'||
p_as_us_ai_group_rec.unit_class||'Group name:'||p_as_us_ai_group_rec.group_name||' '||'Status:'||p_as_us_ai_group_rec.status);
Assign_default(p_as_us_ai_group_rec,l_insert_update);
validate_db_cons(p_as_us_ai_group_rec,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_as_us_ai_group
(us_ass_item_group_id,
uoo_id,
group_name,
midterm_formula_code,
midterm_formula_qty,
midterm_weight_qty,
final_formula_code,
final_formula_qty,
final_weight_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_as_us_ai_group_s.NEXTVAL,
l_n_uoo_id,
p_as_us_ai_group_rec.group_name,
p_as_us_ai_group_rec.midterm_formula_code,
p_as_us_ai_group_rec.midterm_formula_qty,
p_as_us_ai_group_rec.midterm_weight_qty,
p_as_us_ai_group_rec.final_formula_code,
p_as_us_ai_group_rec.final_formula_qty,
p_as_us_ai_group_rec.final_weight_qty,
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_create_generic_pkg.create_usec_ass_item_grp.create_group.record_Inserted',
'Unit code:'||p_as_us_ai_group_rec.unit_cd||' '||'Version number:'||p_as_us_ai_group_rec.version_number||' '||'teach_cal_alternate_code:'
||p_as_us_ai_group_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_as_us_ai_group_rec.location_cd||' '||'Unit Class:'||
p_as_us_ai_group_rec.unit_class||'Group name:'||p_as_us_ai_group_rec.group_name);
ELSE --update
UPDATE igs_as_us_ai_group
SET midterm_formula_code=p_as_us_ai_group_rec.midterm_formula_code,
midterm_formula_qty=p_as_us_ai_group_rec.midterm_formula_qty,
midterm_weight_qty=p_as_us_ai_group_rec.midterm_weight_qty,
final_formula_code=p_as_us_ai_group_rec.final_formula_code,
final_formula_qty=p_as_us_ai_group_rec.final_formula_qty,
final_weight_qty=p_as_us_ai_group_rec.final_weight_qty,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE group_name =p_as_us_ai_group_rec.group_name
AND uoo_id = l_n_uoo_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ass_item_grp.create_group.record_updated',
'Unit code:'||p_as_us_ai_group_rec.unit_cd||' '||'Version number:'||p_as_us_ai_group_rec.version_number||' '||'teach_cal_alternate_code:'
||p_as_us_ai_group_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_as_us_ai_group_rec.location_cd||' '||'Unit Class:'||
p_as_us_ai_group_rec.unit_class||'Group name:'||p_as_us_ai_group_rec.group_name);
SELECT *
FROM igs_ps_unitass_item
WHERE uoo_id = cp_n_uoo_id
AND ass_id = cp_assessment_id
AND sequence_number=cp_sequence_number;
FUNCTION check_insert_update_item ( p_unitass_item_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type ) RETURN VARCHAR2 IS
CURSOR c_unitass_item(cp_n_uoo_id NUMBER ,cp_assessment_id NUMBER,cp_sequence_number NUMBER) IS
SELECT 'X'
FROM igs_ps_unitass_item
WHERE uoo_id = cp_n_uoo_id
AND ass_id = cp_assessment_id
AND sequence_number=cp_sequence_number;
END check_insert_update_item;
SELECT us_ass_item_group_id
FROM igs_as_us_ai_group
WHERE uoo_id = cp_uoo_id
AND group_name = cp_group_name;
PROCEDURE Assign_default( p_unitass_item_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_unitass_item(cp_n_uoo_id NUMBER ,cp_assessment_id NUMBER,cp_sequence_number NUMBER) IS
SELECT *
FROM igs_ps_unitass_item
WHERE uoo_id = cp_n_uoo_id
AND ass_id = cp_assessment_id
AND sequence_number=cp_sequence_number;
SELECT cal_type,sequence_number
FROM igs_ca_inst_all
WHERE alternate_code = cp_alternate_code;
SELECT 'X'
FROM igs_ca_inst ci,
igs_ca_type cat
WHERE cat.s_cal_cat = 'EXAM'
AND ci.cal_type = cat.cal_type
AND ci.cal_type = cp_cal_type
AND ci.sequence_number = cp_sequence_number
AND ci.sequence_number IN (SELECT ci2.sequence_number
FROM igs_ca_inst ci2,
igs_ca_inst_rel cir
WHERE ci2.cal_type = cir.sup_cal_type
AND ci2.sequence_number = cir.sup_ci_sequence_number
AND cir.sub_cal_type = l_c_cal_type
AND cir.sub_ci_sequence_number = l_n_seq_num);
SELECT description
FROM igs_as_assessmnt_itm
WHERE ass_id = cp_assessment_id;
IF p_insert_update = 'I' THEN
IF p_unitass_item_rec.dflt_item_ind IS NULL THEN
p_unitass_item_rec.dflt_item_ind :='Y';
IF p_unitass_item_rec.logical_delete_dt IS NOT NULL THEN
p_unitass_item_rec.logical_delete_dt :=NULL;
IF p_insert_update = 'U' THEN
OPEN c_unitass_item(l_n_uoo_id,p_unitass_item_rec.assessment_id,p_unitass_item_rec.sequence_number);
IF p_unitass_item_rec.logical_delete_dt IS NULL THEN
p_unitass_item_rec.logical_delete_dt := c_unitass_item_rec.logical_delete_dt;
ELSIF p_unitass_item_rec.logical_delete_dt = FND_API.G_MISS_DATE THEN
p_unitass_item_rec.logical_delete_dt :=NULL;
PROCEDURE validate_db_cons_item ( p_unitass_item_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_assessment_id(cp_assessment_id igs_as_assessmnt_itm.ass_id%TYPE)IS
SELECT 'X'
FROM igs_as_assessmnt_itm
WHERE ass_id =cp_assessment_id;
IF (p_insert_update = 'I') THEN
-- Unique Key Validation
IF igs_ps_unitass_item_pkg.get_uk_for_validation (x_ass_id =>p_unitass_item_rec.assessment_id,
x_sequence_number=>p_unitass_item_rec.sequence_number,
x_uoo_id =>l_n_uoo_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_ASSMNT', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update_item(p_unitass_item_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ass_item_grp.create_item.status_after_check_insert_update_item',
'Unit code:'||p_unitass_item_rec.unit_cd||' '||'Version number:'||p_unitass_item_rec.version_number||' '||'teach_cal_alternate_code:'
||p_unitass_item_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_unitass_item_rec.location_cd||' '||'Unit Class:'||
p_unitass_item_rec.unit_class||' '||'Assesment_id:'||p_unitass_item_rec.assessment_id||' '||'Status:'||p_unitass_item_rec.status);
Assign_default(p_unitass_item_rec,l_insert_update);
validate_db_cons_item(p_unitass_item_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_unitass_item (p_unitass_item_rec, l_c_cal_type ,l_n_seq_num ,l_n_uoo_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_unitass_item
(unit_section_ass_item_id,
uoo_id,
ass_id,
sequence_number,
ci_start_dt,
ci_end_dt,
due_dt,
reference,
dflt_item_ind,
logical_delete_dt,
action_dt,
exam_cal_type,
exam_ci_sequence_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
grading_schema_cd,
gs_version_number,
release_date,
description,
us_ass_item_group_id,
midterm_mandatory_type_code,
midterm_weight_qty,
final_mandatory_type_code,
final_weight_qty
)
VALUES
(igs_ps_unitass_item_s.NEXTVAL,
l_n_uoo_id,
p_unitass_item_rec.assessment_id,
igs_ps_unitass_item_seq_num_s.NEXTVAL,
l_d_start_dt,
l_d_end_dt,
p_unitass_item_rec.due_dt,
p_unitass_item_rec.reference,
p_unitass_item_rec.dflt_item_ind,
p_unitass_item_rec.logical_delete_dt,
SYSDATE,
l_c_exam_cal_type,
l_n_exam_seq_num,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_unitass_item_rec.grading_schema_cd,
p_unitass_item_rec.gs_version_number,
p_unitass_item_rec.release_date,
p_unitass_item_rec.description,
l_n_us_ass_item_group_id,
p_unitass_item_rec.midterm_mandatory_type_code,
p_unitass_item_rec.midterm_weight_qty_item,
p_unitass_item_rec.final_mandatory_type_code,
p_unitass_item_rec.final_weight_qty_item
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ass_item_grp.create_item.Status_record_inserted',
'Unit code:'||p_unitass_item_rec.unit_cd||' '||'Version number:'||p_unitass_item_rec.version_number||' '||'teach_cal_alternate_code:'
||p_unitass_item_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_unitass_item_rec.location_cd||' '||'Unit Class:'||
p_unitass_item_rec.unit_class||' '||'Assesment_id:'||p_unitass_item_rec.assessment_id);
ELSE --update
UPDATE igs_ps_unitass_item
SET due_dt = p_unitass_item_rec.due_dt,
reference = p_unitass_item_rec.reference,
dflt_item_ind = p_unitass_item_rec.dflt_item_ind,
logical_delete_dt = p_unitass_item_rec.logical_delete_dt,
action_dt = SYSDATE,
exam_cal_type = l_c_exam_cal_type,
exam_ci_sequence_number = l_n_exam_seq_num,
grading_schema_cd = p_unitass_item_rec.grading_schema_cd,
gs_version_number = p_unitass_item_rec.gs_version_number,
release_date = p_unitass_item_rec.release_date,
description = p_unitass_item_rec.description,
midterm_mandatory_type_code = p_unitass_item_rec.midterm_mandatory_type_code,
midterm_weight_qty = p_unitass_item_rec.midterm_weight_qty_item,
final_mandatory_type_code = p_unitass_item_rec.final_mandatory_type_code,
final_weight_qty = p_unitass_item_rec.final_weight_qty_item,
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
AND ass_id = p_unitass_item_rec.assessment_id
AND sequence_number=p_unitass_item_rec.sequence_number;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ass_item_grp.create_item.Status_record_updated',
'Unit code:'||p_unitass_item_rec.unit_cd||' '||'Version number:'||p_unitass_item_rec.version_number||' '||'teach_cal_alternate_code:'
||p_unitass_item_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_unitass_item_rec.location_cd||' '||'Unit Class:'||
p_unitass_item_rec.unit_class||' '||'Assesment_id:'||p_unitass_item_rec.assessment_id||' '||'sequence_number:'||p_unitass_item_rec.sequence_number);
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_as_us_ai(p_usec_ass_item_grp_tbl,l_tbl_uoo) THEN
p_c_rec_status := 'E';
l_tbl_uoo.DELETE;
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_uso_cm_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type ) RETURN VARCHAR2 IS
CURSOR c_uso_cm_grp(cp_cm_grp_name VARCHAR2) IS
SELECT 'X'
FROM igs_ps_uso_cm_grp
WHERE class_meet_group_name =cp_cm_grp_name
AND Cal_type=l_c_cal_type
AND ci_sequence_number=l_n_seq_num;
END check_insert_update;
PROCEDURE Assign_default( p_uso_cm_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_uso_cm_grp(cp_cm_grp_name VARCHAR2) IS
SELECT *
FROM igs_ps_uso_cm_grp
WHERE class_meet_group_name =cp_cm_grp_name
AND cal_type=l_c_cal_type
AND ci_sequence_number=l_n_seq_num;
IF p_insert_update = 'U' THEN
OPEN c_uso_cm_grp(p_uso_cm_grp_rec.class_meet_group_name);
PROCEDURE validate_db_cons ( p_uso_cm_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_uso_cm_grp_pkg.get_uk_for_validation (x_class_meet_group_name=>p_uso_cm_grp_rec.class_meet_group_name,
x_cal_type=>l_c_cal_type,
x_ci_sequence_number=>l_n_seq_num) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_MEET_WITH_CLASS_GROUP', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_uso_cm_grp_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_meet_with.create_uso_cm_grp.status_after_check_insert_update',
'Unit code:'||p_uso_cm_grp_rec.unit_cd||' '||'Version number:'||p_uso_cm_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_cm_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_cm_grp_rec.location_cd||' '||'Unit Class:'||
p_uso_cm_grp_rec.unit_class||' '||'Class meet group name:'||p_uso_cm_grp_rec.class_meet_group_name
||' '||'Status:'|| p_uso_cm_grp_rec.status);
Assign_default(p_uso_cm_grp_rec,l_insert_update);
validate_db_cons(p_uso_cm_grp_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_uso_cm_grp(p_uso_cm_grp_rec, l_c_cal_type,l_n_seq_num,l_insert_update,class_meet_rec);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_uso_cm_grp
(class_meet_group_id,
class_meet_group_name,
cal_type,
ci_sequence_number ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
max_enr_group,
max_ovr_group
)
VALUES
(igs_ps_uso_cm_grp_s.NEXTVAL,
p_uso_cm_grp_rec.class_meet_group_name,
l_c_cal_type,
l_n_seq_num,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_uso_cm_grp_rec.max_enr_group,
p_uso_cm_grp_rec.max_ovr_group
) RETURNING class_meet_group_id INTO l_n_class_meet_group_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_meet_with.create_uso_cm_grp.Record_Inserted',
'Unit code:'||p_uso_cm_grp_rec.unit_cd||' '||'Version number:'||p_uso_cm_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_cm_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_cm_grp_rec.location_cd||' '||'Unit Class:'||
p_uso_cm_grp_rec.unit_class||' '||'Class meet group name:'||p_uso_cm_grp_rec.class_meet_group_name);
ELSE --update
UPDATE igs_ps_uso_cm_grp
SET max_enr_group=p_uso_cm_grp_rec.max_enr_group,
max_ovr_group=p_uso_cm_grp_rec.max_ovr_group,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE class_meet_group_name =p_uso_cm_grp_rec.class_meet_group_name
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_create_generic_pkg.create_usec_meet_with.create_uso_cm_grp.Record_Updated',
'Unit code:'||p_uso_cm_grp_rec.unit_cd||' '||'Version number:'||p_uso_cm_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_cm_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_cm_grp_rec.location_cd||' '||'Unit Class:'||
p_uso_cm_grp_rec.unit_class||' '||'Class meet group name:'||p_uso_cm_grp_rec.class_meet_group_name);
FUNCTION check_insert_update_item ( p_uso_clas_meet_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type ) RETURN VARCHAR2 IS
CURSOR c_uso_clas_meet(cp_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_uso_clas_meet
WHERE uoo_id = cp_n_uoo_id;
END check_insert_update_item;
PROCEDURE validate_db_cons_cm ( p_uso_clas_meet_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_uso_clas_meet_pkg.get_uk_for_validation (x_uoo_id =>l_n_uoo_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update_item(p_uso_clas_meet_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_meet_with.create_uso_clas_meet.status_after_check_insert_update_item',
'Unit code:'||p_uso_clas_meet_rec.unit_cd||' '||'Version number:'||p_uso_clas_meet_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_clas_meet_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_clas_meet_rec.location_cd||' '||'Unit Class:'||
p_uso_clas_meet_rec.unit_class||' '||'Class meet group name:'||p_uso_clas_meet_rec.class_meet_group_name||' '||'Status:'|| p_uso_clas_meet_rec.status);
validate_db_cons_cm(p_uso_clas_meet_rec,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_uso_clas_meet
( class_meet_id,
class_meet_group_id,
host,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
uoo_id
)
VALUES
(igs_ps_uso_clas_meet_s.NEXTVAL,
l_n_class_meet_group_id,
p_uso_clas_meet_rec.host,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
l_n_uoo_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_meet_with.create_uso_clas_meet.Record_Inserted',
'Unit code:'||p_uso_clas_meet_rec.unit_cd||' '||'Version number:'||p_uso_clas_meet_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_clas_meet_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_clas_meet_rec.location_cd||' '||'Unit Class:'||
p_uso_clas_meet_rec.unit_class||' '||'Class meet group name:'||p_uso_clas_meet_rec.class_meet_group_name);
ELSE --update
UPDATE igs_ps_uso_clas_meet
SET host = p_uso_clas_meet_rec.host,
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_create_generic_pkg.create_usec_meet_with.create_uso_clas_meet.Record_Updated',
'Unit code:'||p_uso_clas_meet_rec.unit_cd||' '||'Version number:'||p_uso_clas_meet_rec.version_number||' '||'teach_cal_alternate_code:'
||p_uso_clas_meet_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_uso_clas_meet_rec.location_cd||' '||'Unit Class:'||
p_uso_clas_meet_rec.unit_class||' '||'Class meet group name:'||p_uso_clas_meet_rec.class_meet_group_name);
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_usec_meet_with(p_usec_meet_with_tbl,class_meet_tab) THEN
p_c_rec_status := 'E';
class_meet_tab.DELETE;
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_usec_x_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_x_grp(cp_x_grp_name VARCHAR2) IS
SELECT 'X'
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name = cp_x_grp_name
AND cal_type = l_c_cal_type
AND ci_sequence_number = l_n_seq_num;
END check_insert_update;
PROCEDURE Assign_default( p_usec_x_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_usec_x_grp(cp_x_grp_name VARCHAR2) IS
SELECT *
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name =cp_x_grp_name
AND cal_type=l_c_cal_type
AND ci_sequence_number=l_n_seq_num;
IF p_insert_update = 'U' THEN
OPEN c_usec_x_grp(p_usec_x_grp_rec.usec_x_listed_group_name);
PROCEDURE validate_db_cons ( p_usec_x_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_x_grp_pkg.get_uk_for_validation (x_usec_x_listed_group_name=>p_usec_x_grp_rec.usec_x_listed_group_name,
x_cal_type=>l_c_cal_type,
x_ci_sequence_number=>l_n_seq_num) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_CROSS_LIST_GROUP', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_x_grp_rec);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_cross_group.create_usec_x_grp.status_after_check_insert_update',
'Unit code:'||p_usec_x_grp_rec.unit_cd||' '||'Version number:'||p_usec_x_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grp_rec.location_cd||' '||'Unit Class:'||
p_usec_x_grp_rec.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grp_rec.usec_x_listed_group_name
||' '||'Status:'|| p_usec_x_grp_rec.status);
Assign_default(p_usec_x_grp_rec,l_insert_update);
validate_db_cons(p_usec_x_grp_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_usec_x_grp(p_usec_x_grp_rec, l_c_cal_type,l_n_seq_num,l_insert_update,cross_group_rec);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_x_grp
(usec_x_listed_group_id,
usec_x_listed_group_name,
location_inheritance,
cal_type,
ci_sequence_number ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
max_enr_group,
max_ovr_group
)
VALUES
(igs_ps_usec_x_grp_s.NEXTVAL,
p_usec_x_grp_rec.usec_x_listed_group_name,
NVL(p_usec_x_grp_rec.location_inheritance,'Y'),
l_c_cal_type,
l_n_seq_num,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
p_usec_x_grp_rec.max_enr_group,
p_usec_x_grp_rec.max_ovr_group
) RETURNING usec_x_listed_group_id INTO l_n_usec_x_listed_group_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_cross_group.create_usec_x_grp.Record_Inserted',
'Unit code:'||p_usec_x_grp_rec.unit_cd||' '||'Version number:'||p_usec_x_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grp_rec.location_cd||' '||'Unit Class:'||
p_usec_x_grp_rec.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grp_rec.usec_x_listed_group_name);
ELSE --update
UPDATE igs_ps_usec_x_grp
SET max_enr_group=p_usec_x_grp_rec.max_enr_group,
max_ovr_group=p_usec_x_grp_rec.max_ovr_group,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE usec_x_listed_group_name =p_usec_x_grp_rec.usec_x_listed_group_name
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_create_generic_pkg.create_usec_cross_group.create_usec_x_grp.Record_Updated',
'Unit code:'||p_usec_x_grp_rec.unit_cd||' '||'Version number:'||p_usec_x_grp_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grp_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grp_rec.location_cd||' '||'Unit Class:'||
p_usec_x_grp_rec.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grp_rec.usec_x_listed_group_name);
FUNCTION check_insert_update_item ( p_usec_x_grpmem IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_x_grpmem(cp_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_usec_x_grpmem
WHERE uoo_id = cp_n_uoo_id;
END check_insert_update_item;
PROCEDURE validate_db_cons_cm ( p_usec_x_grpmem IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_x_grpmem_pkg.get_uk_for_validation (x_uoo_id =>l_n_uoo_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
l_insert_update:='I';
l_insert_update:= check_insert_update_item(p_usec_x_grpmem);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_cross_group.create_usec_x_grpmem.status_after_check_insert_update_item',
'Unit code:'||p_usec_x_grpmem.unit_cd||' '||'Version number:'||p_usec_x_grpmem.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grpmem.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grpmem.location_cd||' '||'Unit Class:'||
p_usec_x_grpmem.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grpmem.usec_x_listed_group_name
||' '||'Status:'|| p_usec_x_grpmem.status);
validate_db_cons_cm(p_usec_x_grpmem,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_x_grpmem
( usec_x_listed_group_mem_id,
usec_x_listed_group_id,
parent,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
uoo_id
)
VALUES
(igs_ps_usec_x_grpmem_s.NEXTVAL,
l_n_usec_x_listed_group_id,
p_usec_x_grpmem.parent,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id,
l_n_uoo_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_cross_group.create_usec_x_grpmem.Record_Inserted',
'Unit code:'||p_usec_x_grpmem.unit_cd||' '||'Version number:'||p_usec_x_grpmem.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grpmem.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grpmem.location_cd||' '||'Unit Class:'||
p_usec_x_grpmem.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grpmem.usec_x_listed_group_name);
ELSE --update
UPDATE igs_ps_usec_x_grpmem
SET parent = p_usec_x_grpmem.parent,
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_create_generic_pkg.create_usec_cross_group.create_usec_x_grpmem.Record_updated',
'Unit code:'||p_usec_x_grpmem.unit_cd||' '||'Version number:'||p_usec_x_grpmem.version_number||' '||'teach_cal_alternate_code:'
||p_usec_x_grpmem.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_x_grpmem.location_cd||' '||'Unit Class:'||
p_usec_x_grpmem.unit_class||' '||'usec_x_listed_group_name:'||p_usec_x_grpmem.usec_x_listed_group_name);
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_usec_cross_group(p_usec_cross_group_tbl,cross_group_tab) THEN
p_c_rec_status := 'E';
cross_group_tab.DELETE;
Purpose : This procedure is a sub process to insert records of Unit Section Waitlist priority and preference.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
--sommukhe 12-AUG-2005 Bug#4377818,changed the cursor cur_hzp, included table igs_pe_hz_parties in
-- FROM clause and modified the WHERE clause by joining HZ_PARTIES and IGS_PE_HZ_PARTIES
-- using party_id and org unit being compared with oss_org_unit_cd of IGS_PE_HZ_PARTIES.
********************************************************************************************** */
l_insert_update VARCHAR2(1);
PROCEDURE validate_derivations_pri ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_insert_update VARCHAR2 ) AS
l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
FUNCTION check_insert_update ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_n_wlst_usec_pri_id NUMBER) RETURN VARCHAR2 IS
CURSOR c_usec_wlst_pri(cp_n_uoo_id NUMBER,cp_priority_value VARCHAR2) IS
SELECT 'X'
FROM igs_ps_usec_wlst_pri
WHERE uoo_id = cp_n_uoo_id
AND priority_value = cp_priority_value;
END check_insert_update;
PROCEDURE Assign_default(p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_usprv(cp_n_uoo_id NUMBER,cp_priority_value VARCHAR2) IS
SELECT priority_number
FROM igs_ps_usec_wlst_pri
WHERE uoo_id = cp_n_uoo_id
AND priority_value = cp_priority_value;
IF p_insert_update = 'U' THEN
OPEN c_usprv( l_n_uoo_id,p_usec_wlst_rec.priority_value);
PROCEDURE validate_db_cons_wlstpri ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_insert_update VARCHAR2 ) AS
BEGIN
IF(p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_wlst_pri_pkg.get_uk_for_validation ( x_priority_value =>p_usec_wlst_rec.priority_value,
x_uoo_id => l_n_uoo_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_WLST_PRI', 'LEGACY_TOKENS', FALSE);
validate_derivations_pri(p_usec_wlst_rec,l_insert_update);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_wlst_rec,l_n_wlst_usec_pri_id);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_waitlist.create_wlstpri.status_after_check_insert_update',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value||' '||'Status:'
||p_usec_wlst_rec.status);
Assign_default(p_usec_wlst_rec,l_insert_update);
validate_db_cons_wlstpri(p_usec_wlst_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_usec_wlstpri (p_usec_wlst_rec,l_n_uoo_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_wlst_pri
( unit_sec_waitlist_priority_id,
priority_number,
priority_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
uoo_id
)
VALUES
(
igs_ps_usec_wlst_pri_s.nextval,
p_usec_wlst_rec.priority_number,
p_usec_wlst_rec.priority_value,
g_n_user_id,
sysdate,
g_n_user_id,
sysdate,
g_n_login_id,
l_n_uoo_id
);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_waitlist.create_wlstpri.Record_Inserted',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value);
ELSE --update
UPDATE igs_ps_usec_wlst_pri SET
priority_number= p_usec_wlst_rec.priority_number,
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 AND priority_value = p_usec_wlst_rec.priority_value;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_waitlist.create_wlstpri.Record_updated',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value);
PROCEDURE validate_derivations_prf ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_pri_id(cp_uoo_id NUMBER, cp_priority_value igs_ps_usec_wlst_pri.priority_value%type) IS
SELECT unit_sec_waitlist_priority_id
FROM igs_ps_usec_wlst_pri
WHERE uoo_id = cp_uoo_id
AND priority_value = cp_priority_value;
FUNCTION check_insert_update ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_n_wlst_usec_pri_id NUMBER) RETURN VARCHAR2 IS
CURSOR c_usec_wlst_prf(cp_n_wlst_usec_pri_id NUMBER,cp_preference_code VARCHAR2) IS
SELECT 'X'
FROM igs_ps_usec_wlst_prf
WHERE unit_sec_waitlist_priority_id = cp_n_wlst_usec_pri_id
AND preference_code = cp_preference_code;
SELECT 'X'
FROM igs_ps_usec_wlst_prf
WHERE unit_sec_waitlist_priority_id = cp_n_wlst_usec_pri_id
AND preference_code = cp_preference_code
AND preference_version = cp_preference_version;
END check_insert_update;
PROCEDURE validate_db_cons_wlstprf ( p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR cur_hzp(cp_preference_code VARCHAR2) IS
SELECT 'x'
FROM hz_parties hp, igs_pe_hz_parties pe
WHERE hp.party_id = pe.party_id
AND pe.oss_org_unit_cd =cp_preference_code;
IF(p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_wlst_prf_pkg.get_uk_for_validation(x_preference_code =>p_usec_wlst_rec.preference_code,
x_preference_version=>p_usec_wlst_rec.preference_version,
x_unit_sec_wlst_priority_id =>l_n_wlst_usec_pri_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_WLST_PRF', 'LEGACY_TOKENS', FALSE);
validate_derivations_prf(p_usec_wlst_rec,l_insert_update);
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_wlst_rec,l_n_wlst_usec_pri_id);
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_waitlist.create_wlstprf.status_after_check_insert_update',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value||' '||
'Preference Code:'||p_usec_wlst_rec.preference_code||' '||'Status:'||p_usec_wlst_rec.status);
validate_db_cons_wlstprf(p_usec_wlst_rec,l_insert_update);
igs_ps_validate_generic_pkg.validate_usec_wlstprf(p_usec_wlst_rec,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_wlst_prf
( unit_sec_waitlist_pref_id,
unit_sec_waitlist_priority_id,
preference_order,
preference_code,
preference_version,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
igs_ps_usec_wlst_prf_s.nextval,
l_n_wlst_usec_pri_id,
p_usec_wlst_rec.preference_order,
p_usec_wlst_rec.preference_code,
p_usec_wlst_rec.preference_version,
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_create_generic_pkg.create_usec_waitlist.create_wlstprf.Record_Inserted',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value||' '||
'Preference Code:'||p_usec_wlst_rec.preference_code);
ELSE ---update
UPDATE igs_ps_usec_wlst_prf SET
preference_order= p_usec_wlst_rec.preference_order,
preference_version=p_usec_wlst_rec.preference_version,
last_updated_by = g_n_user_id,
last_update_date= SYSDATE ,
last_update_login= g_n_login_id
WHERE unit_sec_waitlist_priority_id =l_n_wlst_usec_pri_id AND preference_code = p_usec_wlst_rec.preference_code;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_waitlist.create_wlstprf.Record_Updated',
'Unit code:'||p_usec_wlst_rec.unit_cd||' '||'Version number:'||p_usec_wlst_rec.version_number||' '||'teach_cal_alternate_code:'
||p_usec_wlst_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_wlst_rec.location_cd||' '||'Unit Class:'||
p_usec_wlst_rec.unit_class||' '||'Priority Value:'||p_usec_wlst_rec.priority_value||' '||
'Preference Code:'||p_usec_wlst_rec.preference_code);
/* Post Insert/Update Checks */
IF NOT igs_ps_validate_generic_pkg.post_usec_wlst(p_usec_waitlist_tbl,l_tbl_uoo) THEN
p_c_rec_status := 'E';
l_tbl_uoo.DELETE;
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_usec_notes_rec IN OUT NOCOPY igs_ps_generic_pub.usec_notes_rec_type ) RETURN VARCHAR2 IS
BEGIN
IF p_usec_notes_rec.reference_number IS NULL THEN
RETURN 'I';
END check_insert_update;
PROCEDURE validate_db_cons ( p_usec_notes_rec IN OUT NOCOPY igs_ps_generic_pub.usec_notes_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR cur_ref_num(cp_uoo_id igs_ps_unt_ofr_opt_n.uoo_id%TYPE,
cp_reference_number igs_ps_unt_ofr_opt_n.reference_number%TYPE) IS
SELECT b.note_text
FROM igs_ps_unt_ofr_opt_n a,igs_ge_note b
WHERE a.uoo_id=cp_uoo_id
AND a.reference_number=cp_reference_number
AND a.reference_number=b.reference_number;
IF (p_insert_update = 'U') THEN
/* While update check if the reference number belong to the passed unit section */
OPEN cur_ref_num(l_n_uoo_id, p_usec_notes_rec.reference_number);
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_notes_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_notes.status_check_insert_update',
'Unit code:'||p_usec_notes_tbl(I).unit_cd||' '||'Version number:'||p_usec_notes_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_notes_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_notes_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_notes_tbl(I).unit_class||' '||'Crs Note Type:'||p_usec_notes_tbl(I).crs_note_type||' '||
'Status:'||p_usec_notes_tbl(I).status);
validate_db_cons ( p_usec_notes_tbl(I),l_insert_update );
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO IGS_GE_NOTE
(reference_number,
s_note_format_type,
note_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(IGS_GE_NOTE_RF_NUM_S.nextval,
'TEXT',
p_usec_notes_tbl(I).note_text,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
)RETURNING reference_number INTO l_n_reference_number;
INSERT INTO igs_ps_unt_ofr_opt_n
(unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
reference_number,
uoo_id,
crs_note_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(p_usec_notes_tbl(I).unit_cd,
p_usec_notes_tbl(I).version_number,
l_c_cal_type,
l_n_seq_num,
p_usec_notes_tbl(I).location_cd,
p_usec_notes_tbl(I).unit_class,
l_n_reference_number,
l_n_uoo_id,
p_usec_notes_tbl(I).crs_note_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_create_generic_pkg.create_usec_notes.Record_Inserted',
'Unit code:'||p_usec_notes_tbl(I).unit_cd||' '||'Version number:'||p_usec_notes_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_notes_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_notes_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_notes_tbl(I).unit_class||' '||'Crs Note Type:'||p_usec_notes_tbl(I).crs_note_type);
/*Update record*/
UPDATE igs_ps_unt_ofr_opt_n
SET crs_note_type = p_usec_notes_tbl(I).crs_note_type,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE reference_number = p_usec_notes_tbl(I).reference_number;
UPDATE igs_ge_note
SET note_text = p_usec_notes_tbl(I).note_text,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE reference_number = p_usec_notes_tbl(I).reference_number;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_notes.Record_updated',
'Unit code:'||p_usec_notes_tbl(I).unit_cd||' '||'Version number:'||p_usec_notes_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_notes_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_notes_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_notes_tbl(I).unit_class||' '||'Crs Note Type:'||p_usec_notes_tbl(I).crs_note_type);
END IF;--insert/update
l_insert_update VARCHAR2(1);
FUNCTION check_insert_update ( p_usec_assmnt_rec IN OUT NOCOPY igs_ps_generic_pub.usec_assmnt_rec_type ) RETURN VARCHAR2 IS
CURSOR c_usec_as(cp_n_uoo_id NUMBER) IS
SELECT 'X'
FROM igs_ps_usec_as
WHERE uoo_id = cp_n_uoo_id;
END check_insert_update;
SELECT building_id
FROM igs_ad_building_all
WHERE building_cd = cp_building_cd
AND location_cd = cp_location_cd;
SELECT room_id
FROM igs_ad_room_all
WHERE room_cd = cp_room_cd
AND building_id = cp_building_id;
PROCEDURE assign_defaults ( p_usec_assmnt_rec IN OUT NOCOPY igs_ps_generic_pub.usec_assmnt_rec_type, p_insert IN VARCHAR2) IS
CURSOR cur_usec_exam ( cp_uoo_id IN NUMBER) IS
SELECT *
FROM igs_ps_usec_as
WHERE uoo_id = cp_uoo_id;
SELECT room_id
FROM igs_ad_room
WHERE room_cd=cp_room_cd
AND building_id=cp_building_id;
IF p_insert = 'U' THEN
OPEN cur_usec_exam(l_n_uoo_id);
PROCEDURE validate_db_cons ( p_usec_assmnt_rec IN OUT NOCOPY igs_ps_generic_pub.usec_assmnt_rec_type,p_insert_update VARCHAR2 ) AS
CURSOR c_loc_cd(cp_location_cd igs_ad_location_all.location_cd%TYPE) IS
SELECT 'X'
FROM igs_ad_location_all
WHERE location_cd = cp_location_cd
AND closed_ind = 'N';
IF (p_insert_update = 'I') THEN
/* Unique Key Validation */
IF igs_ps_usec_as_pkg.get_uk_for_validation (x_building_code =>l_n_building_id,
x_final_exam_date =>p_usec_assmnt_rec.final_exam_date,
x_location_cd =>p_usec_assmnt_rec.exam_location_cd,
x_room_code =>l_n_room_id,
x_uoo_id =>l_n_uoo_id) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'USEC_ASSMNT', 'LEGACY_TOKENS', FALSE);
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_assmnt_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_assmnt.status_after_check_insert_update',
'Unit code:'||p_usec_assmnt_tbl(I).unit_cd||' '||'Version number:'||p_usec_assmnt_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_assmnt_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_assmnt_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_assmnt_tbl(I).unit_class||' '||'Status:'||p_usec_assmnt_tbl(I).status);
assign_defaults(p_usec_assmnt_tbl(I),l_insert_update);
validate_db_cons ( p_usec_assmnt_tbl(I),l_insert_update );
igs_ps_validate_generic_pkg.validate_usec_assmnt ( p_usec_assmnt_tbl(I),l_n_uoo_id,l_d_exam_start_time,l_d_exam_end_time,l_n_building_id,l_n_room_id,l_insert_update);
IF l_insert_update = 'I' THEN
/* Insert Record */
INSERT INTO igs_ps_usec_as
(unit_section_assessment_id,
uoo_id,
final_exam_date,
exam_start_time,
exam_end_time,
location_cd,
building_code,
room_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_ps_usec_as_s.nextval,
l_n_uoo_id,
p_usec_assmnt_tbl(I).final_exam_date,
l_d_exam_start_time,--p_usec_assmnt_tbl(I).final_exam_date,
l_d_exam_end_time,--p_usec_assmnt_tbl(I).exam_start_time,
p_usec_assmnt_tbl(I).exam_location_cd,
l_n_building_id,
l_n_room_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_create_generic_pkg.create_usec_assmnt.Record_inserted',
'Unit code:'||p_usec_assmnt_tbl(I).unit_cd||' '||'Version number:'||p_usec_assmnt_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_assmnt_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_assmnt_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_assmnt_tbl(I).unit_class);
/*Update record*/
UPDATE igs_ps_usec_as
SET final_exam_date = p_usec_assmnt_tbl(I).final_exam_date,
exam_start_time=l_d_exam_start_time,--p_usec_assmnt_tbl(I).exam_start_time,
exam_end_time=l_d_exam_end_time,--p_usec_assmnt_tbl(I).exam_end_time,
location_cd=p_usec_assmnt_tbl(I).exam_location_cd,
building_code=l_n_building_id,
room_code=l_n_room_id,
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_create_generic_pkg.create_usec_assmnt.Record_updated',
'Unit code:'||p_usec_assmnt_tbl(I).unit_cd||' '||'Version number:'||p_usec_assmnt_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_assmnt_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_assmnt_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_assmnt_tbl(I).unit_class);
END IF;--insert/update
l_insert_status BOOLEAN;
l_delete_status BOOLEAN;
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;
PROCEDURE delete_records(p_delete_status OUT NOCOPY BOOLEAN) IS
CURSOR cur_ins_exists(cp_uso_id IN NUMBER) IS
SELECT 'X'
FROM igs_ps_uso_instrctrs
WHERE unit_section_occurrence_id =cp_uso_id;
SELECT 'X'
FROM igs_ps_usec_tch_resp
WHERE uoo_id =cp_uoo_id
AND instructor_id=cp_ins_id
AND NOT EXISTS (SELECT 'X' FROM igs_ps_uso_instrctrs a,igs_ps_usec_occurs_all b
WHERE a.unit_section_occurrence_id=b.unit_section_occurrence_id
AND b.uoo_id=cp_uoo_id
AND a.instructor_id=cp_ins_id);
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id= cp_uoo_id
AND ENROLLMENT_ACTUAL > 0;
p_delete_status:= TRUE;
DELETE igs_ps_uso_instrctrs WHERE unit_section_occurrence_id=p_tab_uso_ins(I).system_uso_id ;
p_delete_status:= FALSE;
DELETE igs_ps_usec_tch_resp WHERE instructor_id=p_tab_uso_ins(I).system_instructor_id AND
uoo_id = p_tab_uso_ins(I).system_uoo_id ;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_uso_ins_ovrd.Delete_for_IGS_PS_USO_INSTRCTRS',
'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);
END delete_records;
PROCEDURE insert_instructors(p_insert_status OUT NOCOPY BOOLEAN) IS
BEGIN
p_insert_status:= TRUE;
p_insert_status:= FALSE;
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,
p_tab_uso_ins(I).system_uso_id,
p_tab_uso_ins(I).system_instructor_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_create_generic_pkg.create_uso_ins_ovrd.Insert_for_IGS_PS_USO_INSTRCTRS',
'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);
p_insert_status:= FALSE;
END insert_instructors;
--Insert is done in insert_instructors
END IF;
delete_records(l_delete_status);
IF l_delete_status = FALSE THEN
RETURN;
insert_instructors(l_insert_status);
IF l_insert_status = FALSE THEN
RETURN;
Purpose: This procedure imports(Updates) unit section Teaching Responsibilities.
Known limitations,enhancements,remarks:
Change History
Who When What
***********************************************************************************************/
l_n_ins_id igs_ps_uso_instrctrs.instructor_id%TYPE;
SELECT *
FROM igs_ps_usec_tch_resp
WHERE instructor_id = cp_n_ins_id
AND uoo_id = cp_n_uoo_id;
SELECT COUNT(*)
FROM IGS_PS_USEC_TCH_RESP
WHERE lead_instructor_flag='Y'
AND uoo_id = cp_n_uoo_id
AND ROWNUM = 1;
SELECT A.cal_type,
A.ci_sequence_number,
A.unit_section_status
FROM IGS_PS_UNIT_OFR_OPT_ALL A
WHERE A.uoo_id = cp_n_uoo_id;
SELECT 'x'
FROM IGS_PS_EXP_WL
WHERE ROWNUM=1;
SELECT count(*)
FROM IGS_PS_USEC_TCH_RESP
WHERE uoo_id = cp_n_uoo_id
AND lead_instructor_flag = 'Y';
SELECT SUM(PERCENTAGE_ALLOCATION)
FROM IGS_PS_USEC_TCH_RESP
WHERE confirmed_flag = 'Y'
AND uoo_id = cp_n_uoo_id;
SELECT unit_cd,
version_number
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND ROWNUM < 2;
SELECT message_text
FROM fnd_new_messages
WHERE message_name = 'IGS_PS_NULL'
AND application_id = 8405
AND LANGUAGE_CODE = USERENV('LANG');
v_tab_usec_sr.delete;
UPDATE IGS_PS_USEC_TCH_RESP SET
confirmed_flag = p_usec_teach_resp_tbl(I).confirmed_flag ,
lead_instructor_flag = p_usec_teach_resp_tbl(I).lead_instructor_flag,
instructional_load = p_usec_teach_resp_tbl(I).instructional_load_other,
instructional_load_lab = p_usec_teach_resp_tbl(I).instructional_load_laboratory,
instructional_load_lecture = p_usec_teach_resp_tbl(I).instructional_load_lecture,
percentage_allocation = p_usec_teach_resp_tbl(I).wl_percentage_allocation,
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 AND instructor_id=l_n_ins_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_teach_resp.Record_updated',
'Unit code:'||p_usec_teach_resp_tbl(I).unit_cd||' '||'Version number:'||p_usec_teach_resp_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_teach_resp_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_teach_resp_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_teach_resp_tbl(I).unit_class||' '||'instructor_person_number:'||p_usec_teach_resp_tbl(I).instructor_person_number);
on insert and update, removed the hard coded format checks.
sommukhe 18-Jan-2006 Bug#4926548, modified cursorc_fee_type_exists to address the performance issue.
Created local procedures and functions.
***********************************************************************************************/
l_c_cal_type igs_ca_inst_all.cal_type%TYPE;
l_insert_update VARCHAR2(1);
PROCEDURE validate_db_cons(p_usec_sp_fee_rec IN OUT NOCOPY igs_ps_generic_pub.usec_sp_fee_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' THEN
/* Unique Key Validation */
IF igs_ps_usec_sp_fees_pkg.get_uk_for_validation ( x_fee_type => p_usec_sp_fee_rec.fee_type,
x_uoo_id => l_n_uoo_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'FEE_TYPE', 'IGS_FI_LOCKBOX', FALSE);
FUNCTION check_insert_update ( p_usec_sp_fee_rec IN OUT NOCOPY igs_ps_generic_pub.usec_sp_fee_rec_type) RETURN VARCHAR2 IS
CURSOR c_sp_fee IS
SELECT 'X'
FROM igs_ps_usec_sp_fees
WHERE uoo_id = l_n_uoo_id
AND fee_type =p_usec_sp_fee_rec.fee_type;
END check_insert_update;
PROCEDURE Assign_default(p_usec_sp_fee_rec IN OUT NOCOPY igs_ps_generic_pub.usec_sp_fee_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_sp_fees(cp_n_uoo_id IN NUMBER,cp_c_fee_type IN VARCHAR2) IS
SELECT *
FROM igs_ps_usec_sp_fees
WHERE uoo_id = cp_n_uoo_id
AND fee_type = cp_c_fee_type;
IF p_insert_update = 'I' THEN
IF p_usec_sp_fee_rec.closed_flag IS NULL THEN
p_usec_sp_fee_rec.closed_flag := 'N';
PROCEDURE Business_validation(p_usec_sp_fee_rec IN OUT NOCOPY igs_ps_generic_pub.usec_sp_fee_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR c_fee_type_exists(cp_source_fee_type igs_fi_fee_type.fee_type%TYPE) IS
SELECT ci.cal_type cal_type,ci.sequence_number sequence_number
FROM igs_fi_fee_type ft,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE ft.s_fee_type = 'SPECIAL'
AND ft.closed_ind = 'N'
AND ft.fee_type = ftci.fee_type
AND ft.fee_type = cp_source_fee_type
AND ftci.fee_cal_type = ci.cal_type
AND ftci.fee_ci_sequence_number = ci.sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'FEE'
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE' ;
SELECT sup_cal_type,sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE sub_cal_type = cp_load_cal
AND sub_ci_sequence_number = cp_load_seq;
SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type=l_c_cal_type
AND teach_ci_sequence_number=l_n_seq_num;
IF p_insert_update = 'I' THEN
l_c_proceed:= FALSE;
teachCalendar_tbl.DELETE;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_sp_fee_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_sp_fee.status_after_check_insert_update',
'Unit code:'||p_usec_sp_fee_tbl(I).unit_cd||' '||'Version number:'||p_usec_sp_fee_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_sp_fee_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_sp_fee_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_sp_fee_tbl(I).unit_class||' '||'Fee type:'||p_usec_sp_fee_tbl(I).fee_type
||' '||'Status:'||p_usec_sp_fee_tbl(I).status);
Assign_default(p_usec_sp_fee_tbl(I),l_insert_update);
validate_db_cons ( p_usec_sp_fee_tbl(I),l_insert_update);
Business_validation(p_usec_sp_fee_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_PS_USEC_SP_FEES(
USEC_SP_FEES_ID,
UOO_ID,
FEE_TYPE,
SP_FEE_AMT,
CLOSED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
VALUES (
igs_ps_usec_sp_fees_s.NEXTVAL,
l_n_uoo_id,
p_usec_sp_fee_tbl(I).fee_type,
p_usec_sp_fee_tbl(I).sp_fee_amt,
p_usec_sp_fee_tbl(I).closed_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_create_generic_pkg.create_usec_sp_fee.Record_Inserted',
'Unit code:'||p_usec_sp_fee_tbl(I).unit_cd||' '||'Version number:'||p_usec_sp_fee_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_sp_fee_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_sp_fee_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_sp_fee_tbl(I).unit_class||' '||'Fee type:'||p_usec_sp_fee_tbl(I).fee_type);
UPDATE IGS_PS_USEC_SP_FEES SET
SP_FEE_AMT = p_usec_sp_fee_tbl(I).sp_fee_amt,
CLOSED_FLAG = p_usec_sp_fee_tbl(I).closed_flag,
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 AND fee_type=p_usec_sp_fee_tbl(I).fee_type;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_sp_fee.Record_Updated',
'Unit code:'||p_usec_sp_fee_tbl(I).unit_cd||' '||'Version number:'||p_usec_sp_fee_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_sp_fee_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_sp_fee_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_sp_fee_tbl(I).unit_class||' '||'Fee type:'||p_usec_sp_fee_tbl(I).fee_type);
l_insert_update VARCHAR2(1);
SELECT activity_type_id
FROM igs_ps_usec_act_type
WHERE activity_type_code=cp_activity_type_code;
SELECT building_id
FROM igs_ad_building
WHERE location_cd = cp_location_cd
AND building_cd=cp_building_code;
SELECT room_id
FROM igs_ad_room
WHERE building_id=cp_building_id
AND room_cd= cp_room_cd;
PROCEDURE validate_db_cons(p_usec_plus_hr_rec IN OUT NOCOPY igs_ps_generic_pub.usec_plus_hr_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' THEN
-- Unique Key Validation
IF igs_ps_us_unsched_cl_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id,
x_activity_type_id => l_n_activity_id,
x_location_cd => p_usec_plus_hr_rec.activity_location_cd,
x_building_id => l_n_building_id,
x_room_id => l_n_room_id ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'PLUS_HOUR', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_plus_hr_rec IN OUT NOCOPY igs_ps_generic_pub.usec_plus_hr_rec_type) RETURN VARCHAR2 IS
CURSOR c_plus_hr IS
SELECT 'X'
FROM igs_ps_us_unsched_cl
WHERE uoo_id = l_n_uoo_id
AND activity_type_id = l_n_activity_id
AND location_cd = p_usec_plus_hr_rec.activity_location_cd
AND building_id = l_n_building_id
AND room_id = l_n_room_id;
END check_insert_update;
PROCEDURE Assign_default(p_usec_plus_hr_rec IN OUT NOCOPY igs_ps_generic_pub.usec_plus_hr_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_plus_hr IS
SELECT *
FROM IGS_PS_US_UNSCHED_CL
WHERE uoo_id = l_n_uoo_id
AND activity_type_id = l_n_activity_id
AND location_cd = p_usec_plus_hr_rec.activity_location_cd
AND building_id = l_n_building_id
AND room_id = l_n_room_id;
IF p_insert_update = 'I' THEN
IF p_usec_plus_hr_rec.instructor_number IS NULL THEN
l_n_ins_id := NULL;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_plus_hr_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_plus_hr.status_after_check_insert_update',
'Unit code:'||p_usec_plus_hr_tbl(I).unit_cd||' '||'Version number:'||p_usec_plus_hr_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_plus_hr_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_plus_hr_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_plus_hr_tbl(I).unit_class||' '||'Activity_id:'||l_n_activity_id
||' '||'activity_location_cd:'||p_usec_plus_hr_tbl(I).activity_location_cd||' '||'building_id:'||l_n_building_id
||' '||'room_id:'||l_n_room_id||' '||'Status:'|| p_usec_plus_hr_tbl(I).status);
Assign_default(p_usec_plus_hr_tbl(I),l_insert_update);
validate_db_cons ( p_usec_plus_hr_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_PS_US_UNSCHED_CL(
us_unscheduled_cl_id,
uoo_id,
activity_type_id,
location_cd,
building_id,
room_id,
number_of_students,
hours_per_student,
hours_per_faculty,
instructor_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_ps_us_unsched_cl_s.NEXTVAL,
l_n_uoo_id,
l_n_activity_id,
p_usec_plus_hr_tbl(I).activity_location_cd,
l_n_building_id,
l_n_room_id,
p_usec_plus_hr_tbl(I).number_of_students,
p_usec_plus_hr_tbl(I).hours_per_student,
p_usec_plus_hr_tbl(I).hours_per_faculty,
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_create_generic_pkg.create_usec_plus_hr.Record_Inserted',
'Unit code:'||p_usec_plus_hr_tbl(I).unit_cd||' '||'Version number:'||p_usec_plus_hr_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_plus_hr_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_plus_hr_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_plus_hr_tbl(I).unit_class||' '||'Activity_id:'||l_n_activity_id
||' '||'activity_location_cd:'||p_usec_plus_hr_tbl(I).activity_location_cd||' '||'building_id:'||l_n_building_id
||' '||'room_id:'||l_n_room_id);
UPDATE IGS_PS_US_UNSCHED_CL SET
number_of_students = p_usec_plus_hr_tbl(I).number_of_students,
hours_per_student = p_usec_plus_hr_tbl(I).hours_per_student,
hours_per_faculty = p_usec_plus_hr_tbl(I).hours_per_faculty,
instructor_id = l_n_ins_id,
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 AND activity_type_id=l_n_activity_id AND location_cd= p_usec_plus_hr_tbl(I).activity_location_cd
AND building_id = l_n_building_id AND room_id = l_n_room_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_plus_hr.Record_Updated',
'Unit code:'||p_usec_plus_hr_tbl(I).unit_cd||' '||'Version number:'||p_usec_plus_hr_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_plus_hr_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_plus_hr_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_plus_hr_tbl(I).unit_class||' '||'Activity_id:'||l_n_activity_id
||' '||'activity_location_cd:'||p_usec_plus_hr_tbl(I).activity_location_cd||' '||'building_id:'||l_n_building_id
||' '||'room_id:'||l_n_room_id);
l_n_select_group NUMBER;
l_insert_update VARCHAR2(1);
PROCEDURE validate_db_cons(p_usec_rule_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rule_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' THEN
-- Unique Key Validation
IF igs_ps_usec_ru_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id,
x_s_rule_call_cd => p_usec_rule_rec.s_rule_call_cd ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'RULE', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_rule_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rule_rec_type) RETURN VARCHAR2 IS
CURSOR c_rule IS
SELECT rul_sequence_number
FROM igs_ps_usec_ru
WHERE uoo_id = l_n_uoo_id
AND s_rule_call_cd = p_usec_rule_rec.s_rule_call_cd;
END check_insert_update;
SELECT rc.select_group,rd.rule_description
FROM igs_ru_call rc,igs_ru_description rd
WHERE rc.s_rule_type_cd = 'USEC'
AND rc.s_rule_call_cd = p_usec_rule_rec.s_rule_call_cd
AND rc.rud_sequence_number = rd.sequence_number;
FETCH cur_rule_check INTO l_n_select_group,l_c_rule_desc;
l_n_select_group := NULL;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_rule_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_rule.status_after_check_insert_update',
'Unit code:'||p_usec_rule_tbl(I).unit_cd||' '||'Version number:'||p_usec_rule_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_rule_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rule_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_rule_tbl(I).unit_class||' '||'s_rule_call_cd:'||p_usec_rule_tbl(I).s_rule_call_cd||' '||'Status:'||
p_usec_rule_tbl(I).status);
validate_db_cons ( p_usec_rule_tbl(I),l_insert_update);
p_group => l_n_select_group, -- 8 for Unit Co-requisite,2 for Unit Pre-requisite
p_return_type => 'BOOLEAN', -- pass BOOLEAN
p_rule_description => l_c_rule_desc, --
p_rule_processed => p_usec_rule_tbl(I).rule_text, -- Pass the rule_text
p_rule_unprocessed => l_c_rule_unprocessed, -- id column out parameter
p_generate_rule => TRUE, -- pass TRUE
p_rule_number => l_n_rule_number, -- id column out parameter
p_lov_number => l_n_lov_number ); -- id column out parameter
IF l_insert_update = 'I' THEN
INSERT INTO IGS_PS_USEC_RU(
usecru_id,
uoo_id,
s_rule_call_cd,
rul_sequence_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_ps_usec_ru_s.NEXTVAL,
l_n_uoo_id,
p_usec_rule_tbl(I).s_rule_call_cd,
l_n_rule_number,
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_create_generic_pkg.create_usec_rule.Record_Inserted',
'Unit code:'||p_usec_rule_tbl(I).unit_cd||' '||'Version number:'||p_usec_rule_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_rule_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rule_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_rule_tbl(I).unit_class||' '||'s_rule_call_cd:'||p_usec_rule_tbl(I).s_rule_call_cd);
--Note: Update is not required as it does not update the base table, only rule table,
--which is getting done by igs_ru_gen_002.rulp_ins_parse function
END IF;
l_insert_update VARCHAR2(1);
PROCEDURE validate_db_cons(p_usec_enr_dead_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dead_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' THEN
-- Unique Key Validation
IF igs_en_nstd_usec_dl_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id,
x_function_name => p_usec_enr_dead_rec.function_name ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'ENR_DEADLINE', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_enr_dead_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dead_rec_type) RETURN VARCHAR2 IS
CURSOR c_enr_dead IS
SELECT 'X'
FROM igs_en_nstd_usec_dl
WHERE uoo_id = l_n_uoo_id
AND function_name = p_usec_enr_dead_rec.function_name;
END check_insert_update;
PROCEDURE Assign_defaults (p_usec_enr_dead_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dead_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_usec(cp_uoo_id IN NUMBER) IS
SELECT owner_org_unit_cd
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id=cp_uoo_id;
SELECT formula_method,round_method,offset_duration,non_std_usec_dls_id,offset_dt_code,org_unit_code,definition_code
FROM igs_en_nsu_dlstp_all
WHERE ((org_unit_code = cp_org_unit_cd AND definition_code = 'ORGANIZATIONAL_UNIT') OR definition_code = 'INSTITUTION')
AND function_name = cp_function_name;
SELECT *
FROM igs_en_nstd_usec_dl
WHERE uoo_id = l_n_uoo_id
AND function_name = p_usec_enr_dead_rec.function_name;
IF p_insert_update = 'I' THEN
-- Dervie the attributes from the org/institution level table
OPEN cur_usec(l_n_uoo_id);
ELSE --update
OPEN c_enr_dead;
END IF; --insert/update
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND non_std_usec_ind = 'Y';
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_enr_dead_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_enr_dead.status_after_check_insert_update',
'Unit code:'||p_usec_enr_dead_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dead_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dead_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dead_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dead_tbl(I).unit_class||' '||'function_name:'||p_usec_enr_dead_tbl(I).function_name||' '||'Status:'||
p_usec_enr_dead_tbl(I).status);
assign_defaults(p_usec_enr_dead_tbl(I),l_insert_update);
validate_db_cons ( p_usec_enr_dead_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_EN_NSTD_USEC_DL(
nstd_usec_dl_id ,
non_std_usec_dls_id ,
function_name ,
definition_code ,
org_unit_code ,
formula_method ,
round_method ,
offset_dt_code ,
offset_duration ,
uoo_id ,
enr_dl_date ,
enr_dl_total_days ,
enr_dl_offset_days ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_en_nstd_usec_dl_s.NEXTVAL,
l_n_non_std_usec_dls_id,
p_usec_enr_dead_tbl(I).function_name,
l_c_definition_code,
l_c_org_unit_code,
l_c_formula_method,
l_c_round_method,
l_c_offset_dt_code,
l_n_offset_duration,
l_n_uoo_id,
l_d_enr_dl_date,
l_n_duration_days,
l_n_offset_days,
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_create_generic_pkg.create_usec_enr_dead.Record_Inserted',
'Unit code:'||p_usec_enr_dead_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dead_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dead_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dead_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dead_tbl(I).unit_class||' '||'function_name:'||p_usec_enr_dead_tbl(I).function_name);
UPDATE IGS_EN_NSTD_USEC_DL SET
non_std_usec_dls_id = l_n_non_std_usec_dls_id,
definition_code = l_c_definition_code,
org_unit_code = l_c_org_unit_code,
formula_method = l_c_formula_method,
round_method = l_c_round_method,
offset_dt_code = l_c_offset_dt_code,
offset_duration = l_n_offset_duration,
enr_dl_date = l_d_enr_dl_date,
enr_dl_total_days = l_n_duration_days,
enr_dl_offset_days = l_n_offset_days,
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 AND function_name=p_usec_enr_dead_tbl(I).function_name;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_enr_dead.Record_Updated',
'Unit code:'||p_usec_enr_dead_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dead_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dead_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dead_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dead_tbl(I).unit_class||' '||'function_name:'||p_usec_enr_dead_tbl(I).function_name);
l_insert_update VARCHAR2(1);
PROCEDURE validate_db_cons(p_usec_enr_dis_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dis_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_amd_unit_stat(cp_administrative_unit_status IN igs_ad_adm_unit_stat.administrative_unit_status%TYPE) IS
SELECT 'X'
FROM igs_ad_adm_unit_stat aus
WHERE aus.administrative_unit_status = cp_administrative_unit_status
AND aus.unit_attempt_status='DISCONTIN'
AND aus.closed_ind ='N';
IF p_insert_update = 'I' THEN
-- Unique Key Validation
IF igs_en_usec_disc_dl_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id,
x_administrative_unit_status => p_usec_enr_dis_rec.administrative_unit_status ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'ENR_DISCONTINUATION', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_enr_dis_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dis_rec_type) RETURN VARCHAR2 IS
CURSOR c_enr_disc IS
SELECT 'X'
FROM igs_en_usec_disc_dl
WHERE uoo_id = l_n_uoo_id
AND administrative_unit_status = p_usec_enr_dis_rec.administrative_unit_status;
END check_insert_update;
PROCEDURE Assign_defaults ( p_usec_enr_dis_rec IN OUT NOCOPY igs_ps_generic_pub.usec_enr_dis_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_usec(cp_uoo_id IN NUMBER) IS
SELECT owner_org_unit_cd
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id=cp_uoo_id;
SELECT formula_method,round_method,offset_duration,non_std_disc_dl_stp_id,offset_dt_code,org_unit_code,definition_code
FROM igs_en_nsd_dlstp_all
WHERE ((org_unit_code = cp_org_unit_cd AND definition_code = 'ORGANIZATIONAL_UNIT') OR definition_code = 'INSTITUTION')
AND administrative_unit_status = cp_administrative_unit_status;
SELECT *
FROM igs_en_usec_disc_dl
WHERE uoo_id = l_n_uoo_id
AND administrative_unit_status = p_usec_enr_dis_rec.administrative_unit_status;
IF p_insert_update = 'I' THEN
-- Dervie the attributes from the org/institution level table
OPEN cur_usec(l_n_uoo_id);
ELSE --update
OPEN c_enr_disc;
END IF; --insert/update
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND non_std_usec_ind = 'Y';
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_enr_dis_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_enr_dis.status_after_check_insert_update',
'Unit code:'||p_usec_enr_dis_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dis_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dis_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dis_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dis_tbl(I).unit_class||' '||'Administrative_unit_status:'||p_usec_enr_dis_tbl(I).administrative_unit_status||' '||'Status:'||
p_usec_enr_dis_tbl(I).status);
assign_defaults(p_usec_enr_dis_tbl(I),l_insert_update);
validate_db_cons ( p_usec_enr_dis_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_EN_USEC_DISC_DL(
usec_disc_dl_id ,
non_std_disc_dl_stp_id ,
administrative_unit_status,
definition_code ,
org_unit_code ,
formula_method ,
round_method ,
offset_dt_code ,
offset_duration ,
uoo_id ,
usec_disc_dl_date ,
usec_disc_total_days ,
usec_disc_offset_days ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_en_usec_disc_dl_s.NEXTVAL,
l_n_non_std_disc_dl_stp_id,
p_usec_enr_dis_tbl(I).administrative_unit_status,
l_c_definition_code,
l_c_org_unit_code,
l_c_formula_method,
l_c_round_method,
l_c_offset_dt_code,
l_n_offset_duration,
l_n_uoo_id,
l_d_enr_dl_date,
l_n_duration_days,
l_n_offset_days,
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_create_generic_pkg.create_usec_enr_dis.Record_Inserted',
'Unit code:'||p_usec_enr_dis_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dis_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dis_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dis_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dis_tbl(I).unit_class||' '||'Administrative_unit_status:'||p_usec_enr_dis_tbl(I).administrative_unit_status);
UPDATE IGS_EN_USEC_DISC_DL SET
non_std_disc_dl_stp_id = l_n_non_std_disc_dl_stp_id,
definition_code = l_c_definition_code,
org_unit_code = l_c_org_unit_code,
formula_method = l_c_formula_method,
round_method = l_c_round_method,
offset_dt_code = l_c_offset_dt_code,
offset_duration = l_n_offset_duration,
usec_disc_dl_date = l_d_enr_dl_date,
usec_disc_total_days = l_n_duration_days,
usec_disc_offset_days = l_n_offset_days,
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 AND administrative_unit_status=p_usec_enr_dis_tbl(I).administrative_unit_status;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_enr_dis.Record_Updated',
'Unit code:'||p_usec_enr_dis_tbl(I).unit_cd||' '||'Version number:'||p_usec_enr_dis_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_enr_dis_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_enr_dis_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_enr_dis_tbl(I).unit_class||' '||'Administrative_unit_status:'||p_usec_enr_dis_tbl(I).administrative_unit_status);
l_insert_update VARCHAR2(1);
PROCEDURE validate_db_cons(p_usec_ret_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' AND p_usec_ret_rec.definition_level='UNIT_SECTION_FEE_TYPE' THEN
-- Unique Key Validation
IF igs_ps_nsus_rtn_pkg.get_uk_for_validation ( x_uoo_id => l_n_uoo_id,
x_fee_type => p_usec_ret_rec.fee_type ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'RETENTION', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_ret_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_rec_type) RETURN VARCHAR2 IS
CURSOR c_ret_usec IS
SELECT non_std_usec_rtn_id
FROM igs_ps_nsus_rtn
WHERE uoo_id = l_n_uoo_id
AND definition_code='UNIT_SECTION';
SELECT non_std_usec_rtn_id
FROM igs_ps_nsus_rtn
WHERE uoo_id = l_n_uoo_id
AND fee_type = p_usec_ret_rec.fee_type;
END check_insert_update;
PROCEDURE Business_validation(p_usec_ret_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_check_ns_usec(cp_n_uoo_id IN NUMBER) IS
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND non_std_usec_ind = 'Y';
SELECT ci.cal_type cal_type,ci.sequence_number sequence_number
FROM igs_fi_fee_type ft,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE ft.s_fee_type IN ('TUTNFEE', 'OTHER', 'SPECIAL', 'AUDIT')
AND ft.closed_ind = 'N'
AND ft.fee_type = ftci.fee_type
AND ft.fee_type = cp_fee_type
AND ftci.fee_cal_type = ci.cal_type
AND ftci.fee_ci_sequence_number = ci.sequence_number
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'FEE'
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE';
SELECT 'X'
FROM igs_ps_nsus_rtn nr,
igs_ps_nsus_rtn_dtl nrd
WHERE nr.non_std_usec_rtn_id = nrd.non_std_usec_rtn_id
AND nr.non_std_usec_rtn_id = cp_non_std_usec_rtn_id
AND p_usec_ret_rec.formula_method IN ('P','M')
AND nrd.offset_value > 100;
SELECT *
FROM igs_ps_nsus_rtn_dtl a
WHERE non_std_usec_rtn_id = cp_non_std_usec_rtn_id
AND override_date_flag = 'N';
SELECT sup_cal_type,sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE sub_cal_type = cp_load_cal
AND sub_ci_sequence_number = cp_load_seq;
SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type=l_c_cal_type
AND teach_ci_sequence_number=l_n_seq_num;
IF p_insert_update = 'I' THEN
IF p_usec_ret_rec.fee_type IS NOT NULL THEN
l_c_proceed:= FALSE;
teachCalendar_tbl.DELETE;
IF p_insert_update = 'U' THEN
--If formula method is 'P'/'M' and any details exists such that offset is greater than 100 then it is an error condition
OPEN cur_check_formula(l_n_non_std_usec_rtn_id);
UPDATE igs_ps_nsus_rtn_dtl SET
offset_date = l_offset_date,
last_updated_by = g_n_user_id ,
last_update_date = sysdate ,
last_update_login = g_n_login_id
WHERE non_std_usec_rtn_dtl_id = l_c_rec.non_std_usec_rtn_dtl_id;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_ret_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ret.status_after_check_insert_update',
'Unit code:'||p_usec_ret_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_tbl(I).definition_level||' '||'Status:'||p_usec_ret_tbl(I).status);
validate_db_cons ( p_usec_ret_tbl(I),l_insert_update);
Business_validation(p_usec_ret_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_PS_NSUS_RTN(
non_std_usec_rtn_id,
uoo_id,
fee_type,
definition_code,
formula_method,
round_method,
incl_wkend_duration_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_ps_nsus_rtn_s.NEXTVAL,
l_n_uoo_id,
p_usec_ret_tbl(I).fee_type,
p_usec_ret_tbl(I).definition_level,
p_usec_ret_tbl(I).formula_method,
p_usec_ret_tbl(I).round_method,
p_usec_ret_tbl(I).incl_wkend_duration_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_create_generic_pkg.create_usec_ret.Record_Inserted',
'Unit code:'||p_usec_ret_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_tbl(I).definition_level);
UPDATE IGS_PS_NSUS_RTN SET
formula_method = p_usec_ret_tbl(I).formula_method,
round_method = p_usec_ret_tbl(I).round_method,
incl_wkend_duration_flag = p_usec_ret_tbl(I).incl_wkend_duration_flag,
last_updated_by = g_n_user_id ,
last_update_date = sysdate ,
last_update_login = g_n_login_id
WHERE non_std_usec_rtn_id=l_n_non_std_usec_rtn_id;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ret.Record_Updated',
'Unit code:'||p_usec_ret_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_tbl(I).definition_level);
l_insert_update VARCHAR2(1);
SELECT non_std_usec_rtn_id,formula_method,round_method,incl_wkend_duration_flag
FROM igs_ps_nsus_rtn
WHERE uoo_id = l_n_uoo_id
AND definition_code='UNIT_SECTION';
SELECT non_std_usec_rtn_id,formula_method,round_method,incl_wkend_duration_flag
FROM igs_ps_nsus_rtn
WHERE uoo_id = l_n_uoo_id
AND fee_type = p_usec_ret_dtl_rec.fee_type;
PROCEDURE validate_db_cons(p_usec_ret_dtl_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_dtl_rec_type,p_insert_update IN VARCHAR2) AS
BEGIN
IF p_insert_update = 'I' THEN
-- Unique Key Validation
IF igs_ps_nsus_rtn_dtl_pkg.get_uk_for_validation ( x_non_std_usec_rtn_id => l_n_non_std_usec_rtn_id,
x_offset_value => p_usec_ret_dtl_rec.offset_value ) THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'RETENTION', 'LEGACY_TOKENS', FALSE);
FUNCTION check_insert_update ( p_usec_ret_dtl_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_dtl_rec_type) RETURN VARCHAR2 IS
CURSOR c_ret_det_usec IS
SELECT 'X'
FROM igs_ps_nsus_rtn_dtl
WHERE non_std_usec_rtn_id = l_n_non_std_usec_rtn_id
AND offset_value = p_usec_ret_dtl_rec.offset_value;
END check_insert_update;
PROCEDURE Assign_defaults(p_usec_ret_dtl_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_dtl_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_ret_det IS
SELECT *
FROM igs_ps_nsus_rtn_dtl
WHERE non_std_usec_rtn_id = l_n_non_std_usec_rtn_id
AND offset_value = p_usec_ret_dtl_rec.offset_value;
IF p_insert_update = 'U' THEN
OPEN cur_ret_det;
PROCEDURE Business_validation(p_usec_ret_dtl_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ret_dtl_rec_type,p_insert_update IN VARCHAR2) AS
CURSOR cur_check_ns_usec(cp_n_uoo_id IN NUMBER) IS
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND non_std_usec_ind = 'Y';
SELECT 'X'
FROM igs_ps_nsus_rtn nr
WHERE nr.non_std_usec_rtn_id = cp_non_std_usec_rtn_id
AND nr.formula_method IN ('P','M')
AND p_usec_ret_dtl_rec.offset_value > 100;
--Find out whether it is insert/update of record
l_insert_update:='I';
l_insert_update:= check_insert_update(p_usec_ret_dtl_tbl(I));
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ret_dtl.status_after_check_insert_update',
'Unit code:'||p_usec_ret_dtl_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_dtl_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_dtl_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_dtl_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_dtl_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_dtl_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_dtl_tbl(I).definition_level||' '||'Offset Value:'||p_usec_ret_dtl_tbl(I).offset_value
||' '||'Status:'||p_usec_ret_dtl_tbl(I).status);
--Defaulting depending upon insert or update
IF p_usec_ret_dtl_tbl(I).status = 'S' THEN
assign_defaults(p_usec_ret_dtl_tbl(I),l_insert_update);
validate_db_cons ( p_usec_ret_dtl_tbl(I),l_insert_update);
Business_validation(p_usec_ret_dtl_tbl(I),l_insert_update);
IF l_insert_update = 'I' THEN
INSERT INTO IGS_PS_NSUS_RTN_DTL(
non_std_usec_rtn_dtl_id,
non_std_usec_rtn_id,
offset_value,
retention_percent,
retention_amount,
offset_date,
override_date_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES (
igs_ps_nsus_rtn_dtl_s.NEXTVAL,
l_n_non_std_usec_rtn_id,
p_usec_ret_dtl_tbl(I).offset_value,
p_usec_ret_dtl_tbl(I).retention_percent,
p_usec_ret_dtl_tbl(I).retention_amount,
l_d_offset_date,
p_usec_ret_dtl_tbl(I).override_date_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_create_generic_pkg.create_usec_ret_dtl.Record_Inserted',
'Unit code:'||p_usec_ret_dtl_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_dtl_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_dtl_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_dtl_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_dtl_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_dtl_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_dtl_tbl(I).definition_level||' '||'Offset Value:'||p_usec_ret_dtl_tbl(I).offset_value);
UPDATE IGS_PS_NSUS_RTN_DTL SET
retention_percent = p_usec_ret_dtl_tbl(I).retention_percent,
retention_amount = p_usec_ret_dtl_tbl(I).retention_amount,
offset_date = l_d_offset_date,
override_date_flag = p_usec_ret_dtl_tbl(I).override_date_flag,
last_updated_by = g_n_user_id ,
last_update_date = sysdate ,
last_update_login = g_n_login_id
WHERE non_std_usec_rtn_id = l_n_non_std_usec_rtn_id AND offset_value = p_usec_ret_dtl_tbl(I).offset_value ;
fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_create_generic_pkg.create_usec_ret_dtl.Record_updated',
'Unit code:'||p_usec_ret_dtl_tbl(I).unit_cd||' '||'Version number:'||p_usec_ret_dtl_tbl(I).version_number||' '||'teach_cal_alternate_code:'
||p_usec_ret_dtl_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_ret_dtl_tbl(I).location_cd||' '||'Unit Class:'||
p_usec_ret_dtl_tbl(I).unit_class||' '||'Fee type:'||p_usec_ret_dtl_tbl(I).fee_type||' '||'definition_level:'
||p_usec_ret_dtl_tbl(I).definition_level||' '||'Offset Value:'||p_usec_ret_dtl_tbl(I).offset_value);