[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);
SELECT ROWID, puo.*
FROM igs_ps_usec_occurs puo
WHERE uoo_id = p_uoo_id
AND (schedule_status IS NULL OR schedule_status <> cp_c_schd_stat)
AND NO_SET_DAY_IND ='N'
FOR UPDATE NOWAIT;
IF schd_stat ='USER_CANCEL' OR (schd_stat ='USER_UPDATE' AND (c_usec_occurs_rec.schedule_status IS NOT NULL AND c_usec_occurs_rec.schedule_status <> 'PROCESSING')) THEN
igs_ps_usec_occurs_pkg.update_row (
x_rowid => c_usec_occurs_rec.ROWID,
x_unit_section_occurrence_id => c_usec_occurs_rec.unit_section_occurrence_id,
x_uoo_id => c_usec_occurs_rec.uoo_id,
x_monday => c_usec_occurs_rec.monday,
x_tuesday => c_usec_occurs_rec.tuesday,
x_wednesday => c_usec_occurs_rec.wednesday,
x_thursday => c_usec_occurs_rec.thursday,
x_friday => c_usec_occurs_rec.friday,
x_saturday => c_usec_occurs_rec.saturday,
x_sunday => c_usec_occurs_rec.sunday,
x_start_time => c_usec_occurs_rec.start_time,
x_end_time => c_usec_occurs_rec.end_time,
x_building_code => c_usec_occurs_rec.building_code,
x_room_code => c_usec_occurs_rec.room_code,
x_schedule_status => l_c_schedule_status,
x_status_last_updated => c_usec_occurs_rec.status_last_updated,
x_instructor_id => c_usec_occurs_rec.instructor_id,
X_attribute_category => c_usec_occurs_rec.attribute_category,
X_attribute1 => c_usec_occurs_rec.attribute1,
X_attribute2 => c_usec_occurs_rec.attribute2,
X_attribute3 => c_usec_occurs_rec.attribute3,
X_attribute4 => c_usec_occurs_rec.attribute4,
X_attribute5 => c_usec_occurs_rec.attribute5,
X_attribute6 => c_usec_occurs_rec.attribute6,
X_attribute7 => c_usec_occurs_rec.attribute7,
X_attribute8 => c_usec_occurs_rec.attribute8,
X_attribute9 => c_usec_occurs_rec.attribute9,
X_attribute10 => c_usec_occurs_rec.attribute10,
X_attribute11 => c_usec_occurs_rec.attribute11,
X_attribute12 => c_usec_occurs_rec.attribute12,
X_attribute13 => c_usec_occurs_rec.attribute13,
X_attribute14 => c_usec_occurs_rec.attribute14,
X_attribute15 => c_usec_occurs_rec.attribute15,
X_attribute16 => c_usec_occurs_rec.attribute16,
X_attribute17 => c_usec_occurs_rec.attribute17,
X_attribute18 => c_usec_occurs_rec.attribute18,
X_attribute19 => c_usec_occurs_rec.attribute19,
X_attribute20 => c_usec_occurs_rec.attribute20,
x_error_text => c_usec_occurs_rec.error_text,
x_mode => 'R',
X_start_date => c_usec_occurs_rec.start_date,
X_end_date => c_usec_occurs_rec.end_date,
X_to_be_announced => c_usec_occurs_rec.to_be_announced,
x_dedicated_building_code => c_usec_occurs_rec.dedicated_building_code,
x_dedicated_room_code => c_usec_occurs_rec.dedicated_room_code,
x_preferred_building_code => c_usec_occurs_rec.preferred_building_code,
x_preferred_room_code => c_usec_occurs_rec.preferred_room_code,
x_inst_notify_ind => c_usec_occurs_rec.inst_notify_ind,
x_notify_status => c_usec_occurs_rec.notify_status,
x_preferred_region_code => c_usec_occurs_rec.preferred_region_code,
x_no_set_day_ind => c_usec_occurs_rec.no_set_day_ind,
x_cancel_flag => l_c_cancel,
x_occurrence_identifier => c_usec_occurs_rec.occurrence_identifier,
x_abort_flag => c_usec_occurs_rec.abort_flag
);
SELECT 'X'
FROM igs_ps_unit_subtitle
WHERE closed_ind='N'
AND approved_ind='Y'
AND unit_cd=cp_unit_cd
AND version_number=cp_version_number;
l_c_cur_stat :='SELECT '||''''||'X'||''''||' FROM IGS_OR_INST_ORG_BASE_V WHERE party_number = :p_c_org_unit_cd AND '|| l_c_where_clause;
l_c_cur_stat :='SELECT '||''''||'X'||''''||' FROM IGS_OR_INST_ORG_BASE_V WHERE party_number= :p_c_org_unit_cd ';
Purpose : This procedure will do validations after inserting records of Teaching
Responsibility.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
smvk 12-Dec-2002 Added a boolean parameter to the function call igs_ps_val_tr.crsp_val_tr_perc.
As a part of the Bug # 2696207
********************************************************************************************** */
l_c_message VARCHAR2(30);
Purpose : This procedure will do validations after inserting records of Unit Discipline.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
smvk 12-Dec-2002 Added a boolean parameter to the function call igs_ps_val_ud.crsp_val_ud_perc.
As a part of the Bug # 2696207
********************************************************************************************** */
l_c_message VARCHAR2(30);
Purpose : This procedure will do validations before inserting records of Unit Grading Schema.
This is called from sub process of legacy import data, which inserts Unit GS records.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
BEGIN
-- Check if grading schema type is 'UNIT' for a given grading schema.
IF NOT validate_gs_type ( p_unit_gs_rec.grading_schema_code, p_unit_gs_rec.grd_schm_version_number ) THEN
fnd_message.set_name ( 'IGS', 'IGS_PS_LGCY_INCORRECT_GS_TYPE' );
Purpose : This function will do validations after inserting records of Unit Grading Schema.
This will returns TRUE if all the validations pass and returns FALSE, if fails.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_unit_gs_count (
cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
cp_ver_num igs_ps_unit_ver_all.version_number%TYPE
) IS
SELECT COUNT(*) cnt
FROM igs_ps_unit_grd_schm
WHERE
unit_code = cp_unit_cd AND
unit_version_number = cp_ver_num AND
default_flag = 'Y';
p_insert_update VARCHAR2,
p_conc_flag OUT NOCOPY BOOLEAN)
AS
/***********************************************************************************************
Created By : shtatiko
Date Created By: 22-NOV-2002
Purpose : This does legacy validations before inserting Unit Offering Option Records.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 12-JAN-2006 Bug#4926548, in the cursor user_check changed the table fnd_user_resp_groups to fnd_user_resp_groups_direct
modified the cursors c_anon_grd_method and c_cal_rel also introduced new cursors c_min_load_start_dt and cur_teach_load.
sarakshi 13-Apr-2004 Bug#3555871,added validation of teach calender association with load calender.Also removed the call of get_call_number .
sarakshi 21-oct-2003 Bug#3052452,used igs_ps_gen_003.enrollment_for_uoo_check in place of using a local cursor
smvk 24-Sep-2003 Bug # 3121311. Removed the validation to check unit contact person is of person type staff member.
Removed the variable l_n_unit_contact_id and calls to get_party_id and validate_staff_person.
sarakshi 11-Sep-2003 Enh#3052452,Added validation related to passed sup_uoo_id
sarakshi 22-Aug-2003 Bug#304509, added validation, Not Multiple Unit Section Flag should not be N if it N at Unit level
sarakshi 04-Mar-2003 Bug#2768783, addded call number validation for profile option NONE also
smvk 26-Dec-2002 Bug # 2721495. Using the newly created function and procedure get_party_id, validate_staff_person.
********************************************************************************************** */
l_n_call_number igs_ps_unit_ofr_opt_all.call_number%TYPE;
SELECT 'x'
FROM igs_ad_location_all a,
igs_ad_location_type_all b
WHERE a.location_type = b.location_type
AND b.s_location_type = 'CAMPUS'
AND a.location_cd = cp_location_cd;
SELECT 1
FROM igs_ps_unit_ver
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND anon_unit_grading_ind = 'Y' ;
SELECT 1
FROM igs_ca_teach_to_load_v a,
igs_as_anon_method b
WHERE a.teach_cal_type = cp_cal_type
AND a.teach_ci_sequence_number = cp_ci_seq_num
AND a.load_start_dt = cp_load_start_dt
AND a.load_cal_type = b.load_cal_type;
SELECT MIN(c.load_start_dt)
FROM igs_ca_teach_to_load_v c
WHERE c.teach_cal_type = cp_cal_type
AND c.teach_ci_sequence_number = cp_ci_seq_num ;
SELECT 1
FROM igs_ps_unit_ver
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND anon_assess_grading_ind = 'Y';
SELECT B.s_cal_status
FROM igs_ca_inst_all A,igs_ca_stat B
WHERE A.cal_type = cp_c_cal_type
AND A.sequence_number = cp_n_ci_seq_num
AND A.cal_status = B.cal_status;
SELECT same_teaching_period
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT 'X'
FROM igs_ps_usec_x_grpmem
WHERE uoo_id = cp_uoo_id;
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_uoo_id
AND unit_section_status = cp_usec_status;
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_uoo_id
AND relation_type = cp_relation_type;
SELECT uoo.cal_type,uoo.ci_sequence_number
FROM igs_ps_unit_ofr_opt_all uoo
WHERE uoo.uoo_id = cp_uoo_id;
SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type=p_c_cal_type
AND teach_ci_sequence_number=p_n_seq_num;
SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE teach_cal_type = cp_cal_teach_type
AND teach_ci_sequence_number = cp_teach_ci_sequence_number;
SELECT load_cal_type lcal_type, load_ci_sequence_number lseq_num
FROM igs_ca_teach_to_load_v
WHERE
teach_cal_type = cp_cal_type AND
teach_ci_sequence_number = cp_seq_num;
SELECT a.*, a.rowid
FROM igs_ps_unit_ofr_opt_all a
WHERE unit_cd = p_usec_rec.unit_cd
AND version_number = p_usec_rec.version_number
AND ci_sequence_number =p_n_seq_num
AND unit_class = p_usec_rec.unit_class
AND location_cd = p_usec_rec.location_cd
AND cal_type = p_c_cal_type ;
SELECT uso.rowid, uso.*
FROM igs_ps_usec_occurs_all uso
WHERE uso.uoo_id = cp_n_uoo_id
AND building_code IS NOT NULL;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT
WHERE uoo_id = cp_uoo_id
AND no_assessment_ind='Y';
SELECT 'X'
FROM fnd_user_resp_groups_direct a,fnd_responsibility_vl b, fnd_user c
WHERE a.user_id= cp_user_id
AND a.user_id = c.user_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(c.start_date) AND TRUNC(NVL(c.end_date,SYSDATE))
AND a.responsibility_id= b.responsibility_id
AND b.responsibility_key ='IGS_SUPER_USER'
AND TRUNC(SYSDATE) BETWEEN TRUNC(a.start_date) AND TRUNC(NVL(a.end_date,SYSDATE));
IF p_insert_update = 'I' THEN---this validation needs to be done only while insert operation
-- Validate Call Number
IF (fnd_profile.value('IGS_PS_CALL_NUMBER') = 'AUTO' AND p_usec_rec.call_number IS NOT NULL) THEN
-- Profile is AUTO and values is passed to call_number so raise error
igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'CALL_NUMBER', 'LEGACY_TOKENS', FALSE);
teachCalendar_tbl.DELETE;
IF p_insert_update = 'U' THEN
OPEN c_usec;
--update of the field NOT_MULTIPLE_SECTION_FLAG is not allowed
IF p_usec_rec.not_multiple_section_flag <> c_usec_rec.not_multiple_section_flag THEN
fnd_message.set_name('IGS','IGS_PS_LGCY_US_MULTIPLE_FLAG');
--update of the field AUDIT_PERMISSION_IND is not allowed
IF p_usec_rec.audit_permission_ind <>c_usec_rec.audit_permission_ind THEN
fnd_message.set_name('IGS', 'IGS_PS_AUDIT_PERMISSION_EXIST');
igs_ps_usec_occurs_pkg.update_row (
X_Mode => 'R',
X_RowId => rec_uso.rowid ,
X_unit_section_occurrence_id => rec_uso.unit_section_occurrence_id,
X_uoo_id => rec_uso.uoo_id,
X_monday => rec_uso.monday,
X_tuesday => rec_uso.tuesday,
X_wednesday => rec_uso.wednesday,
X_thursday => rec_uso.thursday,
X_friday => rec_uso.friday,
X_saturday => rec_uso.saturday,
X_sunday => rec_uso.sunday,
X_start_time => rec_uso.start_time,
X_end_time => rec_uso.end_time,
X_building_code => NULL, -- Clearing the building code
X_room_code => NULL, -- Clearing the room code
X_schedule_status => rec_uso.schedule_status,
X_status_last_updated => SYSDATE,
X_instructor_id => rec_uso.instructor_id,
X_attribute_category => rec_uso.attribute_category,
X_attribute1 => rec_uso.attribute1,
X_attribute2 => rec_uso.attribute2,
X_attribute3 => rec_uso.attribute3,
X_attribute4 => rec_uso.attribute4,
X_attribute5 => rec_uso.attribute5,
X_attribute6 => rec_uso.attribute6,
X_attribute7 => rec_uso.attribute7,
X_attribute8 => rec_uso.attribute8,
X_attribute9 => rec_uso.attribute9,
X_attribute10 => rec_uso.attribute10,
X_attribute11 => rec_uso.attribute11,
X_attribute12 => rec_uso.attribute12,
X_attribute13 => rec_uso.attribute13,
X_attribute14 => rec_uso.attribute14,
X_attribute15 => rec_uso.attribute15,
X_attribute16 => rec_uso.attribute16,
X_attribute17 => rec_uso.attribute17,
X_attribute18 => rec_uso.attribute18,
X_attribute19 => rec_uso.attribute19,
X_attribute20 => rec_uso.attribute20,
X_error_text => rec_uso.error_text ,
X_start_date => rec_uso.start_date,
X_end_date => rec_uso.end_date,
X_to_be_announced => rec_uso.to_be_announced,
X_inst_notify_ind => rec_uso.inst_notify_ind,
X_notify_status => rec_uso.notify_status,
X_preferred_region_code => rec_uso.preferred_region_code,
X_no_set_day_ind => rec_uso.no_set_day_ind,
X_preferred_building_code => rec_uso.preferred_building_code,
X_preferred_room_code => rec_uso.preferred_room_code,
X_dedicated_building_code => rec_uso.dedicated_building_code,
X_dedicated_room_code => rec_uso.dedicated_room_code,
x_cancel_flag => rec_uso.cancel_flag,
x_occurrence_identifier => rec_uso.occurrence_identifier,
x_abort_flag => rec_uso.abort_flag
);
--Update the occurrence status
upd_usec_occurs_schd_status(c_usec_rec.uoo_id,'USER_CANCEL');
END IF;--End of validations for update
p_insert_update VARCHAR2) AS
/***********************************************************************************************
Created By : shtatiko
Date Created By: 23-NOV-2002
Purpose : This procedure will validate records before inserting Unit Section Credit Points
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 29-AUG-2005 Bug # 4089179.Included the check for insert condition while recalculating Re-calculating the values in Worload lecture,
Laboratory and Other in Teaching Responsibilities
smvk 17-Jun-2004 Bug # 3697443.Added variable increment into the validation for displaying the message IGS_PS_LGCY_CPS_NULL.
sarakshi 10-Nov-2003 Enh#3116171, added business logic related to the newly introduced field BILLING_CREDIT_POINTS
sarakshi 28-Jun-2003 Enh#2930935,modified cursor c_credits such that it no longer selects
enrolled and achievable credit points
********************************************************************************************** */
CURSOR c_credit (cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
cp_ver_num igs_ps_unit_ver_all.version_number%TYPE) IS
SELECT points_override_ind
FROM igs_ps_unit_ver_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_ver_num;
SELECT rowid,iputr.*
FROM igs_ps_usec_tch_resp iputr
WHERE iputr.uoo_id = p_uoo_id
AND iputr.percentage_allocation IS NOT NULL
AND iputr.instructional_load_lab IS NULL
AND iputr.instructional_load_lecture IS NULL
AND iputr.instructional_load IS NULL;
SELECT *
FROM igs_ps_usec_cps
WHERE uoo_id = cp_n_uoo_id;
IF (p_insert_update = 'U' AND
( NVL(p_usec_rec.work_load_other,-1) <> NVL(c_usec_cp_rec.work_load_other,-1) OR
NVL(p_usec_rec.work_load_cp_lecture ,-1) <> NVL(c_usec_cp_rec.work_load_cp_lecture ,-1) OR
NVL(p_usec_rec.work_load_cp_lab,-1) <> NVL(c_usec_cp_rec.work_load_cp_lab,-1))) OR p_insert_update = 'I' THEN
-- Re-calculating the values in Worload lecture,Laboratory and Other in Teaching Responsibilities as these points are modified at Unit Section level
FOR c_teach_resp_rec in c_teach_resp(p_n_uoo_id)
LOOP
--igs_ps_fac_credt_wrkload.calculate_teach_work_load(c_teach_resp_rec.uoo_id,c_teach_resp_rec.percentage_allocation,l_new_lab,l_new_lecture,l_new_other);
igs_ps_usec_tch_resp_pkg.update_row (
x_mode => 'R',
x_rowid => c_teach_resp_rec.rowid,
x_unit_section_teach_resp_id => c_teach_resp_rec.unit_section_teach_resp_id,
x_instructor_id => c_teach_resp_rec.instructor_id,
x_confirmed_flag => c_teach_resp_rec.confirmed_flag ,
x_percentage_allocation => c_teach_resp_rec.percentage_allocation,
x_instructional_load => l_new_other ,
x_lead_instructor_flag => c_teach_resp_rec.lead_instructor_flag,
x_uoo_id => c_teach_resp_rec.uoo_id,
x_instructional_load_lab => l_new_lab,
x_instructional_load_lecture => l_new_lecture
);
p_insert_update VARCHAR2)
AS
/***********************************************************************************************
Created By : shtatiko
Date Created By: 23-NOV-2002
Purpose : This does legacy validations before inserting Unit Section Reference Records.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_subtitle ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
cp_ver_num igs_ps_unit_ver_all.version_number%TYPE) IS
SELECT 1
FROM igs_ps_unit_subtitle
WHERE closed_ind = 'N'
AND approved_ind = 'Y'
AND unit_cd = cp_unit_cd
AND version_number = cp_ver_num;
SELECT subtitle_id
FROM igs_ps_unit_subtitle
WHERE closed_ind = 'N'
AND approved_ind = cp_approved_ind
AND unit_cd = cp_unit_cd
AND version_number = cp_ver_num
AND subtitle = cp_subtitle ;
SELECT uv.title_override_ind,
uv.subtitle_modifiable_flag
FROM igs_ps_unit_ver uv
WHERE uv.unit_cd = cp_unit_cd
AND uv.version_number = cp_ver_num;
SELECT *
FROM igs_ps_usec_ref
WHERE uoo_id = p_n_uoo_id;
SELECT 'X'
FROM igs_ps_unit_subtitle
WHERE closed_ind = 'Y'
AND unit_cd = cp_unit_cd
AND version_number = cp_ver_num
AND subtitle = cp_subtitle ;
--If the passed subtitle is a closed one then it is an error condition , if it does not exists then insert it
OPEN c_subtitle_closed(p_usec_rec.unit_cd, p_usec_rec.version_number, p_usec_rec.reference_subtitle);
-- then insert the passed subtitle into table
INSERT INTO igs_ps_unit_subtitle
(subtitle_id,
unit_cd,
version_number,
subtitle,
approved_ind,
closed_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(igs_ps_unit_subtitle_s.NEXTVAL,
p_usec_rec.unit_cd,
p_usec_rec.version_number,
p_usec_rec.reference_subtitle,
'N',
'N',
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
)RETURNING subtitle_id INTO p_n_subtitle_id;
IF p_insert_update ='U' THEN
OPEN c_unit_ver(p_usec_rec.unit_cd,p_usec_rec.version_number);
--cannot update TITLE log error message
IF p_usec_rec.reference_title <> c_usec_ref_rec.title THEN
fnd_message.set_name( 'IGS', 'IGS_PS_CNT_UPD_TITLE');
--cannot update SUBTITLE log error message
IF p_usec_rec.reference_subtitle <> c_usec_ref_rec.subtitle THEN
fnd_message.set_name( 'IGS', 'IGS_PS_CNT_UPD_SUBTITLE');
Purpose : This procedure will do validations before inserting records of Unit Section Grading Schema.
This is called from sub process of legacy import data, which inserts Unit Section GS records.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_c_message VARCHAR2(30);
Purpose : This function will do validations after inserting records of Unit Section Grading Schema.
This will returns TRUE if all the validations pass and returns FALSE, if fails.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_usec_gs_count ( cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE ) IS
SELECT COUNT(*) cnt
FROM igs_ps_usec_grd_schm
WHERE uoo_id = cp_uoo_id
AND default_flag = 'Y';
SELECT uoo_id
FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
WHERE a.unit_cd = cp_usec_gs_rec.unit_cd
AND a.version_number = cp_usec_gs_rec.version_number
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.sequence_number
AND b.alternate_code=cp_usec_gs_rec.teach_cal_alternate_code
AND a.location_cd =cp_usec_gs_rec.location_cd
AND a.unit_class = cp_usec_gs_rec.unit_class;
p_insert IN VARCHAR2,
p_calling_context IN VARCHAR2,
p_notify_status OUT NOCOPY VARCHAR2,
p_schedule_status IN OUT NOCOPY VARCHAR2
) AS
/***********************************************************************************************
Created By : shtatiko
Date Created By: 20-NOV-2002
Purpose : This procedure will do validations before inserting record of Unit Section Occurrence.
This is called from sub process of legacy import data, which inserts USO records.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
jbegum 3-June-2003 Enh Bug#2972950
For the PSP Scheduling Enhancements TD:
Added validations given in TD.
********************************************************************************************** */
CURSOR c_usec_dates ( cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE ) IS
SELECT unit_section_start_date start_date,
unit_section_end_date end_date
FROM igs_ps_unit_ofr_opt_all
WHERE
uoo_id = cp_uoo_id ;
SELECT 'X'
FROM igs_ad_room
WHERE building_id=cp_bld_id
AND room_id=cp_rom_id;
SELECT instructor_id FROM igs_ps_uso_instrctrs
WHERE unit_section_occurrence_id = cp_uso_id;
SELECT *
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_uso_id;
SELECT count(*)
FROM IGS_PS_SH_USEC_OCCURS
WHERE unit_section_occurrence_id= l_unit_sec_occurrence_id;
SELECT monday,tuesday, wednesday, thursday, friday, saturday,
sunday, start_time, end_time, building_code, room_code, instructor_id
FROM IGS_PS_SH_USEC_OCCURS
WHERE unit_section_occurrence_id= l_unit_sec_occurrence_id;
SELECT instructor_id from IGS_PS_SH_USEC_OCCURS
WHERE unit_section_occurrence_id =l_unit_occur_id;
SELECT uso.transaction_type,uso.schedule_status,uso.int_occurs_id,uso.int_usec_id,uso.tba_status, usec.int_pat_id
FROM igs_ps_sch_int_all uso, igs_ps_sch_usec_int_all usec
WHERE uso.unit_section_occurrence_id=cp_uso_id
AND uso.int_usec_id = usec.int_usec_id
AND uso.transaction_type IN ('REQUEST','UPDATE' ,'CANCEL')
AND uso.abort_flag='N';
SELECT *
FROM igs_ps_sch_usec_int_all
WHERE uoo_Id=cp_uoo_id
AND abort_flag='N';
IF p_insert = 'U' THEN
OPEN cur_occur(p_n_uso_id);
ELSIF l_cur_sch_int.transaction_type IN ('REQUEST','UPDATE') THEN
--For normal occurrences shoud have
fnd_message.set_name ( 'IGS', 'IGS_PS_VALUES_NULL' );
IF l_cur_sch_int.transaction_type IN ('REQUEST','UPDATE') AND p_n_building_code IS NULL THEN
fnd_message.set_name ( 'IGS', 'IGS_PS_SCH_BLDIS_MUST' );
UPDATE igs_ps_sch_int_all set transaction_type='COMPLETE',schedule_status=NVL(p_schedule_status,schedule_status),import_done_flag='Y' WHERE int_occurs_id = l_cur_sch_int.int_occurs_id;
UPDATE igs_ps_sch_usec_int_all set import_done_flag='Y' WHERE int_usec_id = l_cur_sch_int.int_usec_id;
UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = l_cur_sch_int.int_pat_id;
p_schedule_status := 'USER_UPDATE';
SELECT IGS_PS_SH_USEC_OCCURS_S.NEXTVAL INTO l_new_usecsh_id FROM dual;
INSERT INTO IGS_PS_SH_USEC_OCCURS(USECSH_ID,
UNIT_SECTION_OCCURRENCE_ID,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
SUNDAY,
ROOM_CODE,
BUILDING_CODE,
START_TIME,
END_TIME,
INSTRUCTOR_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
VALUES (
l_new_USECSH_ID,
l_new_unit_section_occur_id,
l_new_MONDAY,
l_new_TUESDAY,
l_new_WEDNESDAY,
l_new_THURSDAY,
l_new_FRIDAY,
l_new_SATURDAY,
l_new_SUNDAY,
to_number(l_new_ROOM_CODE),
to_number(l_new_BUILDING_CODE),
fnd_date.canonical_to_date(fnd_date.string_to_canonical(l_new_START_TIME,'DD-MON-YYYY HH24:MI:SS')),
fnd_date.canonical_to_date(fnd_date.string_to_canonical(l_new_END_TIME,'DD-MON-YYYY HH24:MI:SS')),
l_new_INSTRUCTOR_ID,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id);
UPDATE IGS_PS_SH_USEC_OCCURS SET
monday = l_new_monday,
tuesday = l_new_tuesday,
wednesday = l_new_wednesday,
thursday = l_new_thursday,
friday = l_new_friday,
saturday = l_new_saturday,
sunday = l_new_sunday,
start_time = fnd_date.canonical_to_date(fnd_date.string_to_canonical(l_new_START_TIME,'DD-MON-YYYY HH24:MI:SS')),
end_time = fnd_date.canonical_to_date(fnd_date.string_to_canonical(l_new_END_TIME,'DD-MON-YYYY HH24:MI:SS')),
room_code = to_number(l_new_room_code),
building_code = to_number(l_new_building_code),
instructor_id = l_new_instructor_id,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE
WHERE unit_section_occurrence_id = p_n_uso_id;
ELSE --insert
IF p_calling_context = 'S' THEN
OPEN cur_int_usec(p_n_uoo_id);
UPDATE igs_ps_sch_usec_int_all set import_done_flag='Y' WHERE int_usec_id = l_cur_int_usec.int_usec_id;
UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = l_cur_int_usec.int_pat_id;
END IF; --insert/update
p_b_delete_flag IN BOOLEAN
)AS
/***********************************************************************************************
Created By : smvk
Date Created By: 18-NOV-2002
Purpose : This procedure sets the particular message in the message stack.
Based upon the input arguments this procedure does the following functions
-- if the p_c_msg_name is null then returns immediately
-- if p_c_token and p_c_lkup_type
-- if the p_b_delete_flag is true then it deletes last message in the message stack.
-- if the
-- if p_c_token and p_c_lkup_type are not null then
it returns null for invalid lookup_code or/and lookup_type.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
l_n_count NUMBER;
IF p_b_delete_flag THEN
l_n_count:= FND_MSG_PUB.COUNT_MSG;
FND_MSG_PUB.DELETE_MSG(l_n_count);
SELECT A.meaning
FROM igs_lookups_view A
WHERE A.lookup_code = cp_c_lkup_cd
AND A.lookup_type = cp_c_lkup_type
AND A.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(A.start_date_active,TRUNC(SYSDATE))
AND NVL(A.end_date_active,TRUNC(SYSDATE)) ;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt_all
WHERE UNIT_CD = cp_unit_cd
AND version_number = cp_ver_num
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_num
AND location_cd = cp_loc_cd
AND unit_class = cp_unit_class;
SELECT inst.waitlist_alwd
FROM igs_en_inst_wlst_opt inst,
igs_ca_load_to_teach_v lot
WHERE
inst.cal_type = lot.load_cal_type AND
lot.teach_cal_type = cp_c_cal_type AND
lot.teach_ci_sequence_number = cp_n_seq_num;
SELECT 1
FROM igs_as_grd_schema
WHERE
grading_schema_cd = cp_gs_cd AND
version_number = cp_gs_ver AND
grading_schema_type = cp_gs_type ;
SELECT 1
FROM igs_ca_type
WHERE
s_cal_cat = cp_cal_cat AND
cal_type = cp_cal_type;
l_c_cur_stat := 'SELECT 1 FROM igs_or_inst_org_base_v WHERE party_number = :p_c_org_unit_cd ';
l_c_cur_stat := 'SELECT 1 FROM igs_or_inst_org_base_v WHERE party_number = :p_c_org_unit_cd AND ' || l_c_where_clause;
SELECT hz.party_id
FROM hz_parties hz
WHERE hz.party_number = cp_c_party_number
AND hz.status in ('A','I');
p_insert_update VARCHAR2) AS
/***********************************************************************************************
Created By : jbegum
Date Created By: 02-June-2003
Purpose : Bug # 2972950.
For the Legacy Enhancements TD:
This procedure does the business validation related to enrollment limits of unit section
As mentioned in TD.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 23-NOV-2005 BUG#4675113,include cursor c_usec_st, so that unit section is not updated to open on updating Enrollment Maximum
when unit is planned.
sarakshi 12-Jul-2004 Bug#3729462, Added the predicate DELETE_FLAG in the cursor c_waitlist_allowed.
smvk 25-Nov-2003 Bug # 2833971. Removed the validation associated with displaying error
messages IGS_PS_WLST_MAX_LESS_THAN_ACT and IGS_PS_ENR_MAX_LESS_THAN_ACT.
********************************************************************************************** */
CURSOR c_act (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
SELECT NVL(enrollment_actual, 0) enrollment_actual,
NVL(auditable_ind,'N') auditable_ind,
waitlist_actual
FROM igs_ps_unit_ofr_opt_all a
WHERE uoo_id = cp_n_uoo_id;
SELECT waitlist_allowed
FROM igs_ps_unit_ofr_pat_all a,
igs_ps_unit_ofr_opt_all b
WHERE b.uoo_id = cp_n_uoo_id
AND a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.ci_sequence_number
AND a.delete_flag='N';
SELECT *
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_n_uoo_id;
SELECT count(*)
FROM igs_en_su_attempt
WHERE uoo_id = cp_uoo_id
AND no_assessment_ind = 'Y';
SELECT max_auditors_allowed
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_uoo_id;
SELECT 'X' FROM igs_ps_usec_wlst_pri
WHERE uoo_id = cp_n_uoo_id;
SELECT b.s_unit_status unit_status
FROM igs_ps_unit_ver_all a,igs_ps_unit_stat b
WHERE a.UNIT_STATUS=b.UNIT_STATUS
AND a.unit_cd=cp_unit_cd
AND a.version_number=cp_version_number;
IF p_insert_update = 'U' THEN
OPEN c_usec_lim(p_n_uoo_id);
UPDATE igs_ps_unit_ofr_opt_all
SET unit_section_status = l_c_usec_status
WHERE uoo_id = p_n_uoo_id;
p_insert_update VARCHAR2) AS
CURSOR cur_unit_limit(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
SELECT enrollment_maximum,enrollment_expected,override_enrollment_max
FROM igs_ps_unit_ver_all uv,
igs_ps_unit_ofr_opt_all uoo
WHERE uv.unit_cd=uoo.unit_cd
AND uv.version_number=uoo.version_number
AND uoo.uoo_id=cp_uoo_id;
IF p_insert_update = 'I' THEN
OPEN cur_unit_limit(p_n_uoo_id);
SELECT COUNT(*)
FROM igs_ps_usec_tch_resp
WHERE instructor_id = cp_n_instructor_id
AND uoo_id = cp_n_uoo_id
AND ROWNUM = 1;
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;
INSERT INTO IGS_PS_USEC_TCH_RESP(
UNIT_SECTION_TEACH_RESP_ID,
UOO_ID,
INSTRUCTOR_ID,
CONFIRMED_FLAG,
PERCENTAGE_ALLOCATION,
INSTRUCTIONAL_LOAD_LECTURE,
INSTRUCTIONAL_LOAD_LAB,
INSTRUCTIONAL_LOAD,
LEAD_INSTRUCTOR_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
IGS_PS_USEC_TCH_RESP_S.nextval,
p_n_uoo_id,
p_n_ins_id,
p_uso_ins_rec.confirmed_flag,
p_uso_ins_rec.wl_percentage_allocation,
p_uso_ins_rec.instructional_load_lecture,
p_uso_ins_rec.instructional_load_laboratory,
p_uso_ins_rec.instructional_load_other,
p_uso_ins_rec.lead_instructor_flag,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
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_tr.delete;
SELECT unit_flag,
unit_section_flag,
unit_section_occurrence_flag
FROM igs_ge_ref_cd_type
WHERE reference_cd_type = cp_c_ref_cd_type;
SELECT uso.unit_section_occurrence_id
FROM igs_ps_usec_occurs_all uso
WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
AND uso.no_set_day_ind ='N'
AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
SELECT 'X'
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_uso_id
AND schedule_status = 'PROCESSING';
--Update the schedule status of the occurrence to USER_UPDATE if inserting/updating a record
FOR l_occurs_rec IN c_occurs(p_n_uso_id) LOOP
igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
SELECT waitlist_allowed_flag
FROM igs_en_inst_wl_stps;
SELECT unit_section_occurrence_id
FROM igs_ps_usec_occurs_all
WHERE uoo_id= p_uoo_id
AND occurrence_identifier=p_occurrence_identifier;
SELECT 'X'
FROM IGS_PE_PERSON_TYPES PT,IGS_PE_TYP_INSTANCES_ALL PTI,HZ_PARTIES HZ
WHERE HZ.PARTY_ID = PTI.PERSON_ID
AND HZ.PARTY_ID = p_person_id
AND PTI.PERSON_TYPE_CODE = PT.PERSON_TYPE_CODE
AND PT.SYSTEM_TYPE IN ('STAFF','FACULTY')
AND TRUNC(SYSDATE) BETWEEN TRUNC(PTI.START_DATE) AND TRUNC(NVL(PTI.END_DATE,SYSDATE))
AND HZ.STATUS = 'A'
UNION
SELECT 'X'
FROM PER_PERSON_TYPE_USAGES_F USG,PER_PEOPLE_F PEO,IGS_PE_PER_TYPE_MAP MAP,HZ_PARTIES HZ
WHERE HZ.PARTY_ID = peo.party_id
AND USG.PERSON_ID = PEO.PERSON_ID
AND HZ.PARTY_ID = p_person_id
AND USG.PERSON_TYPE_ID = MAP.PER_PERSON_TYPE_ID AND TRUNC(SYSDATE) BETWEEN
TRUNC(PEO.EFFECTIVE_START_DATE) AND TRUNC(PEO.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(USG.EFFECTIVE_START_DATE) AND TRUNC(USG.EFFECTIVE_END_DATE)
AND HZ.STATUS = 'A' ;
SELECT schedule_status,abort_flag
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = p_n_uso_id;
SELECT abort_flag,unit_section_status
FROM igs_ps_unit_ofr_opt_all
WHERE unit_cd = p_unit_cd
AND version_number = p_version_number
AND cal_type = cp_cal_type
AND ci_sequence_number =cp_seq_num
AND location_cd = p_location_cd
AND unit_class = p_unit_class;
SELECT 'X'
FROM igs_ps_unit_ofr_pat_all
WHERE cal_type= cp_cal_type
AND ci_sequence_number=cp_seq_num
AND unit_cd = cp_unit_cd
AND version_number=cp_version_number
AND abort_flag = 'Y';
SELECT schedule_status,abort_flag
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_n_uso_id;
SELECT abort_flag,unit_section_status
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
SELECT 'X'
FROM igs_ps_unit_ofr_pat_all pt, igs_ps_unit_ofr_opt_all uoo
WHERE uoo.uoo_id= cp_n_uoo_id
AND uoo.unit_cd=pt.unit_cd
AND uoo.version_number=pt.version_number
AND uoo.cal_type= pt.cal_type
AND uoo.ci_sequence_number=pt.ci_sequence_number
AND pt.abort_flag = 'Y';
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id
AND unit_section_status='NOT_OFFERED';