[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
to delete the particular unit section occurrence in interface table with is child and parent
information.
jbegum 07-Apr-2003 Enh Bug #2833850.
As part of PSP Scheduling Interface Enhancements TD added a local procedure purge_schd_record.
Added a new public function get_enrollment_max
Added the columns preferred_region_code,no_set_day_ind to the call of igs_ps_usec_occurs_pkg.update_row
Added the columns preferred_region_code to the calls of insert_row and update_row of
igs_ps_sch_int_pkg.
(reverse chronological order - newest change first)
*/
-- Forward declaration of local procedures
PROCEDURE purge_schd_record( p_c_cal_type IN VARCHAR2,
p_n_seq_num IN NUMBER);
g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
SELECT uoo.uoo_id,
uoo.unit_cd,
uoo.version_number,
uv.title title,
uoo.location_cd,
loc.description location_description,
uoo.unit_class,
uoo.unit_section_start_date,
uoo.unit_section_end_date,
uoo.enrollment_actual,
uoo.unit_section_status,
ci.start_dt cal_start_dt,
ci.end_dt cal_end_dt,
uoo.call_number,
ci.alternate_code teaching_cal_alternate_code,
NULL subtitle,
uv.subtitle_id,
NVL(uoo.owner_org_unit_cd, uv.owner_org_unit_cd) owner_org_unit_cd
FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv,igs_ca_inst_all ci, igs_ad_location_all loc
WHERE uoo.cal_type = l_cal_type
AND uoo.ci_sequence_number = l_ci_sequence_number
AND uoo.unit_cd=cp_unit_cd
AND uoo.version_number=cp_version_number
AND (uoo.location_cd=cp_location_cd OR cp_location_cd IS NULL)
AND (uoo.unit_class=cp_unit_class OR cp_unit_class IS NULL)
AND uoo.unit_cd = uv.unit_cd
AND uoo.version_number = uv.version_number
AND uoo.unit_section_status <> 'NOT_OFFERED'
AND uoo.cal_type = ci.cal_type
AND uoo.ci_sequence_number = ci.sequence_number
AND uoo.location_cd = loc.location_cd;
SELECT *
FROM igs_ps_usec_occurs_all
WHERE uoo_id = l_uoo_id
AND no_set_day_ind = 'N'
AND (unit_section_occurrence_id = cp_unit_section_occurrence_id OR cp_unit_section_occurrence_id IS NULL)
AND (
schedule_status IS NULL
OR
(schedule_status NOT IN('SCHEDULED'))
);
SELECT ci.end_dt cal_end_dt
FROM igs_ps_unit_ofr_opt_all uoo, igs_ca_inst_all ci
WHERE UOO_ID =cp_uoo_id
AND uoo.cal_type = ci.cal_type
AND uoo.ci_sequence_number = ci.sequence_number;
SELECT 'x'
FROM igs_ps_usec_occurs_all
WHERE NVL(schedule_status,'NULL') = DECODE(cp_unit_section_occurrence_id,NULL,cp_schedule_status,NVL(schedule_status,'NULL'))
AND uoo_id = cp_n_uoo_id
AND no_set_day_ind = 'N'
AND (unit_section_occurrence_id = cp_unit_section_occurrence_id OR cp_unit_section_occurrence_id IS NULL)
AND ROWNUM <2 ;
SELECT unit_cd,version_number,cal_type,ci_sequence_number,unit_class,location_cd
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id=cp_uoo_id;
SELECT us.*,b.alternate_code ,b.start_dt,b.end_dt,c.unit_status,c.enrollment_expected,c.enrollment_maximum,c.override_enrollment_max
FROM igs_ps_unit_ofr_pat us, igs_ca_inst_all b,igs_ps_unit_ver c, igs_ps_unit_stat d
WHERE us.cal_type=cp_cal_type
AND us.ci_sequence_number=cp_ci_sequence_number
AND (us.unit_cd=cp_unit_cd OR cp_unit_cd IS NULL)
AND (us.version_number=cp_version_number OR cp_version_number IS NULL)
AND us.cal_type=b.cal_type
AND us.ci_sequence_number=b.sequence_number
AND us.unit_cd=c.unit_cd
AND us.version_number=c.version_number
AND c.unit_status=d.unit_status
AND d.s_unit_status <> 'INACTIVE';
SELECT 'X'
FROM igs_ps_usec_occurs_all
WHERE uoo_id=cp_uoo_id;
SELECT 'X'
FROM igs_ps_sch_usec_int_all
WHERE int_pat_id = cp_int_pat_id;
SELECT 'X'
FROM igs_ps_sch_pat_int
WHERE transaction_id = cp_transaction_id;
SELECT *
FROM igs_ps_sch_pat_int
WHERE transaction_id=cp_transaction_id;
SELECT *
FROM igs_ps_sch_usec_int_all
WHERE int_pat_id=cp_int_pat_id;
SELECT *
FROM igs_ps_sch_int_all
WHERE int_usec_id=cp_int_usec_id;
INSERT INTO igs_ps_sch_hdr_int_all (
transaction_id ,
originator ,
request_date ,
org_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login)
VALUES (
IGS_PS_SCH_HDR_INT_S.NEXTVAL,
'INTERNAL',
SYSDATE,
p_org_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id ) RETURNING transaction_id INTO l_trans_id;
SELECT usec.enrollment_expected,
NVL(usec.enrollment_maximum,999999),
NVL(usec.override_enrollment_max,999999)
FROM igs_ps_usec_lim_wlst usec
WHERE usec.uoo_id = cp_n_uoo_id;
SELECT unit.enrollment_expected,
NVL(unit.enrollment_maximum, 999999),
NVL(unit.override_enrollment_max,999999)
FROM IGS_PS_UNIT_VER_ALL unit,
IGS_PS_UNIT_OFR_OPT_ALL usec
WHERE unit.unit_cd = usec.unit_cd
AND unit.version_number = usec.version_number
AND usec.uoo_id = cp_n_uoo_id;
SELECT instructor_id
FROM igs_ps_usec_tch_resp
WHERE uoo_id = cp_n_uoo_id
AND lead_instructor_flag = 'Y' ;
SELECT first_name,
middle_name,
last_name
FROM igs_pe_person_base_v
WHERE person_id = cp_n_ins_id;
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all us
WHERE us.cal_type=cp_cal_type
AND us.ci_sequence_number=cp_ci_sequence_number
AND us.unit_cd=cp_unit_cd
AND us.version_number=cp_version_number;
SELECT *
FROM igs_ps_unit_location_v
WHERE unit_code=cp_unit_cd
AND unit_version_number=cp_version_number;
SELECT *
FROM igs_ps_unit_facility_v
WHERE unit_code=cp_unit_cd
AND unit_version_number=cp_version_number;
--Insert into pattern interface table
INSERT INTO IGS_PS_SCH_PAT_INT
(int_pat_id ,
transaction_id ,
calendar_type ,
sequence_number ,
teaching_cal_alternate_code ,
start_date ,
end_date ,
unit_cd ,
version_number ,
enrollment_expected ,
enrollment_maximum ,
override_enrollment_maximum ,
unit_status ,
abort_flag ,
import_done_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login)
VALUES(
IGS_PS_SCH_PAT_INT_S.NEXTVAL,
l_trans_id,
l_cal_type,
l_ci_sequence_number,
p_pat_cur_rec.alternate_code,
p_pat_cur_rec.start_dt,
p_pat_cur_rec.end_dt,
p_pat_cur_rec.unit_cd,
p_pat_cur_rec.version_number,
l_u_enrollment_expected,
l_u_enrollment_maximum,
l_u_override_enrollment_max,
p_unit_status,
'N',
'N',
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) RETURNING int_pat_id INTO p_int_pat_id;
-- If sections does not exists for the pattern then insert the location and facilities
IF p_unit_status IS NOT NULL THEN
FOR c_loc_rec IN c_loc(p_pat_cur_rec.unit_cd,p_pat_cur_rec.version_number) LOOP
INSERT INTO IGS_PS_SCH_LOC_INT(
int_loc_id ,
int_pat_id ,
location_code ,
location_description,
building_code ,
building_description,
room_code ,
room_description ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login )
VALUES
(IGS_PS_SCH_LOC_INT_S.NEXTVAL,
p_int_pat_id,
c_loc_rec.location_cd,
c_loc_rec.location_description,
c_loc_rec.building_cd,
c_loc_rec.building_description,
c_loc_rec.room_cd,
c_loc_rec.room_description,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
INSERT INTO IGS_PS_SCH_FAC_INT (
int_fac_id ,
int_pat_id ,
media_code ,
media_description ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES
(IGS_PS_SCH_FAC_INT_S.NEXTVAL,
p_int_pat_id,
c_fac_rec.media_code,
c_fac_rec.media_description,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
SELECT party_name org_unit_description
FROM igs_or_inst_org_base_v
WHERE party_number = cp_owner_org_unit_cd
AND inst_org_ind = 'O';
SELECT unit_sec_cross_unit_sec_id,
parent_uoo_id,child_uoo_id,
child_unit_cd,child_version_number,
child_title,child_cal_type,
child_alternate_code,start_dt,
end_dt,child_ci_sequence_number,child_unit_class,
child_unit_mode,child_location_cd,child_location_description
FROM igs_ps_usec_x_usec_v
WHERE parent_uoo_id = l_uoo_id;
SELECT A.usec_x_listed_group_name
FROM igs_ps_usec_x_grp_v A,
igs_ps_usec_x_grpmem_v B
WHERE A.usec_x_listed_group_id = B.usec_x_listed_group_id
AND B.uoo_id = l_uoo_id
AND B.parent = 'Y';
-- Modified the cursor to select Enrollment Maximum when it is defined at group level(Cross Listed Unit Section)
-- otherwise from the unit section level as a part of Enh Bug # 2613933
CURSOR max_enr_x_grp ( l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) IS
SELECT NVL(A.max_ovr_group, A.max_enr_group) enroll_max
FROM igs_ps_usec_x_grp A,
igs_ps_usec_x_grpmem B
WHERE A.max_enr_group IS NOT NULL AND
A.usec_x_listed_group_id = B.usec_x_listed_group_id AND
B.uoo_id = l_uoo_id and B.parent = 'Y'
UNION ALL
SELECT SUM(nvl(A.override_maximum, A.enrollment_maximum)) enroll_max
FROM igs_ps_usec_x_grpmem_v A,
igs_ps_usec_x_grpmem_v B
WHERE A.enrollment_maximum is NOT NULL
AND A.usec_x_listed_group_id = B.usec_x_listed_group_id
AND B.uoo_id = l_uoo_id
AND B.parent = 'Y';
-- to select uoo_id instead of uso_id. As Meet with class functionality
-- is moved to Unit Section level as part of Enh Bug # 2613933
CURSOR meet_with_uoo_ids( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
SELECT A.uoo_id host_uoo_id,
B.uoo_id guest_uoo_id
FROM igs_ps_uso_clas_meet A,
igs_ps_uso_clas_meet B
WHERE A.class_meet_group_id = B.class_meet_group_id AND
A.uoo_id = l_uoo_id AND
A.host = 'Y' AND
B.host = 'N' ;
-- Modified the cursor to select group name from tables rather than view as a part of Enh Bug # 2613933
CURSOR meet_with_grp_name ( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
SELECT A.class_meet_group_name
FROM igs_ps_uso_cm_grp A,
igs_ps_uso_clas_meet B
WHERE A.class_meet_group_id = B.class_meet_group_id AND
B.uoo_id = l_uoo_id AND
B.host = 'Y';
-- Modified the cursor to select Enrollment Maximum when it is defined at group level(Meet With Class)
-- otherwise from the unit section level as a part of Enh Bug # 2613933
CURSOR max_enr_meet_grp ( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
SELECT NVL(A.max_ovr_group, A.max_enr_group) enroll_max
FROM igs_ps_uso_cm_grp A,
igs_ps_uso_clas_meet B
WHERE A.max_enr_group IS NOT NULL AND
A.class_meet_group_id = B.class_meet_group_id AND
B.uoo_id = l_uoo_id and B.host = 'Y'
UNION ALL
SELECT SUM(NVL(A.override_maximum, A.enrollment_maximum)) enroll_max
FROM igs_ps_uso_clas_meet_v A,
igs_ps_uso_clas_meet_v B
WHERE A.enrollment_maximum IS NOT NULL AND
A.class_meet_group_id = B.class_meet_group_id AND
B.uoo_id = l_uoo_id AND
B.host = 'Y';
-- Insert Unit Section Occurs Interface Records (IGS_PS_SCH_USEC_INT_ALL)
BEGIN
INSERT INTO igs_ps_sch_usec_int_all (
int_usec_id ,
calendar_type ,
sequence_number ,
unit_cd ,
version_number ,
unit_title ,
owner_org_unit_cd ,
unit_class ,
unit_section_start_date ,
unit_section_end_date ,
unit_section_status ,
enrollment_maximum ,
enrollment_actual ,
enrollment_expected ,
override_enrollment_max ,
location_cd ,
cal_start_dt ,
cal_end_dt ,
uoo_id ,
transaction_id ,
org_id ,
IMPORT_DONE_FLAG ,
INT_PAT_ID ,
ABORT_FLAG ,
CALL_NUMBER ,
SUBTITLE ,
ORG_UNIT_DESCRIPTION ,
TEACHING_CAL_ALTERNATE_CODE ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_USEC_INT_S.NEXTVAL,
l_cal_type,
l_ci_sequence_number,
p_uoo_cur_rec.unit_cd,
p_uoo_cur_rec.version_number,
p_uoo_cur_rec.title,
p_uoo_cur_rec.owner_org_unit_cd,
p_uoo_cur_rec.unit_class,
p_uoo_cur_rec.unit_section_start_date,
p_uoo_cur_rec.unit_section_end_date,
p_uoo_cur_rec.unit_section_status,
l_enrollment_maximum,
p_uoo_cur_rec.enrollment_actual,
l_enrollment_expected,
l_override_enrollment_max,
p_uoo_cur_rec.location_cd,
p_uoo_cur_rec.cal_start_dt,
p_uoo_cur_rec.cal_end_dt,
p_uoo_cur_rec.uoo_id,
l_trans_id,
p_org_id,
'N',
p_int_pat_id,
'N',
p_uoo_cur_rec.call_number,
p_uoo_cur_rec.subtitle,
l_ou_description,
p_uoo_cur_rec.teaching_cal_alternate_code,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) RETURNING int_usec_id INTO p_int_usec_id;
-- Insert Cross Listings Information
FOR usec_x_list_rec IN usec_x_list(p_uoo_cur_rec.uoo_id) LOOP
OPEN usec_x_grp_name(p_uoo_cur_rec.uoo_id);
INSERT INTO igs_ps_sch_x_usec_int_all (
int_usec_x_usec_id ,
int_usec_id ,
unit_sec_cross_unit_sec_id ,
parent_uoo_id ,
child_uoo_id ,
child_unit_Cd ,
child_version_number ,
child_title ,
child_cal_type ,
child_alternate_code ,
start_dt ,
end_dt ,
child_ci_sequence_number ,
child_unit_class ,
child_unit_mode ,
child_location_Cd ,
child_location_description ,
org_id ,
cross_list_group_name ,
class_max_enrollment_number ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_X_USEC_INT_S.NEXTVAL ,
p_int_usec_id,
usec_x_list_rec.unit_sec_cross_unit_sec_id,
usec_x_list_rec.parent_uoo_id,
usec_x_list_rec.child_uoo_id,
usec_x_list_rec.child_unit_cd,
usec_x_list_rec.child_version_number,
usec_x_list_rec.child_title,
usec_x_list_rec.child_cal_type,
usec_x_list_rec.child_alternate_code,
usec_x_list_rec.start_dt,
usec_x_list_rec.end_dt,
usec_x_list_rec.child_ci_sequence_number,
usec_x_list_rec.child_unit_class,
usec_x_list_rec.child_unit_mode,
usec_x_list_rec.child_location_cd,
usec_x_list_rec.child_location_description,
p_org_id,
l_usec_x_grp_name,
l_max_enr_x_grp ,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) ;
-- Insert Meet with group related Information
-- As the Meet with class functionality is moved to Unit Section level
-- Modified the cursors meet_with_uso_ids as meet_with_uoo_id. Also modified to cursor
-- to get meet_with_grp_name,max_enr_meet_grp with UOO_ID insted of USO_ID
FOR meet_with_grp_data_rec IN meet_with_uoo_ids ( p_uoo_cur_rec.uoo_id) LOOP
BEGIN
OPEN meet_with_grp_name(p_uoo_cur_rec.uoo_id);
INSERT INTO igs_ps_sch_mwc_all (
mwc_group_id ,
meet_with_class_group_name ,
host_uoo_id ,
guest_uoo_id ,
mwc_max_enrollment_number ,
org_id ,
int_usec_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_MWC_S.NEXTVAL,
l_meet_with_grp_name,
meet_with_grp_data_rec.host_uoo_id,
meet_with_grp_data_rec.guest_uoo_id,
l_max_enr_meet_grp,
p_org_id,
p_int_usec_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
SELECT reference_code,
reference_code_description,
reference_code_type,
reference_type_description
FROM igs_ps_usec_ocur_ref_v
WHERE unit_section_occurrence_id = l_usec_id;
SELECT A.instructor_id,
B.PERSON_LAST_NAME surname,
B.PERSON_FIRST_NAME given_names,
B.PERSON_MIDDLE_NAME middle_name,
B.party_name person_name
FROM igs_ps_uso_instrctrs_v A,
HZ_PARTIES B
WHERE A.person_number = B.party_number AND
A.unit_section_occurrence_id = l_usec_id;
SELECT facility_code,
facility_description
FROM igs_ps_uso_facility_v
WHERE unit_section_occurrence_id = l_usec_id;
SELECT media_code,
media_description
FROM igs_ps_usec_occurs_all a,
igs_ps_unit_ofr_opt_all b,
igs_ps_unit_facility_v c
WHERE a.unit_section_occurrence_id = l_usec_id
AND a.uoo_id=b.uoo_id
AND b.unit_cd=c.unit_code
AND b.version_number=c.unit_version_number;
-- Insert Unit Section Occurs Interface Records
DECLARE
l_start_date igs_ps_usec_occurs_all.start_date%TYPE;
-- Added the column preferred_region_code to the call igs_ps_sch_int_pkg.insert_row
INSERT INTO igs_ps_sch_int_all (
int_occurs_id ,
int_usec_id ,
calendar_type ,
sequence_number ,
transaction_type ,
unit_section_occurrence_id ,
unit_cd ,
version_number ,
unit_title ,
owner_org_unit_cd ,
unit_class ,
monday ,
tuesday ,
wednesday ,
thursday ,
friday ,
saturday ,
sunday ,
unit_section_start_date ,
unit_section_end_date ,
start_time ,
end_time ,
enrollment_maximum ,
enrollment_actual ,
instructor_id ,
building_id ,
room_id ,
dedicated_building_id ,
dedicated_room_id ,
preferred_building_id ,
preferred_room_id ,
tba_status ,
uso_start_date ,
uso_end_date ,
location_cd ,
unit_sec_cross_unit_sec_id ,
uoo_id ,
schedule_status ,
error_text ,
transaction_id ,
surname ,
given_names ,
middle_name ,
preferred_region_code ,
org_id ,
occurrence_identifier ,
import_done_flag ,
abort_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_INT_S.NEXTVAL,
p_int_usec_id,
NULL,
NULL,
P_trans_type,
p_usec_occur_rec.unit_section_occurrence_id,
NULL,
NULL,
NULL,
NULL,
NULL,
p_usec_occur_rec.monday,
p_usec_occur_rec.tuesday,
p_usec_occur_rec.wednesday,
p_usec_occur_rec.thursday,
p_usec_occur_rec.friday,
p_usec_occur_rec.saturday,
p_usec_occur_rec.sunday,
l_start_date,
l_end_date,
p_usec_occur_rec.start_time,
p_usec_occur_rec.end_time,
NULL,
NULL,
l_lead_instructor_id,
p_usec_occur_rec.building_code,
p_usec_occur_rec.room_code,
p_usec_occur_rec.dedicated_building_code,
p_usec_occur_rec.dedicated_room_code,
p_usec_occur_rec.preferred_building_code,
p_usec_occur_rec.preferred_room_code,
p_usec_occur_rec.to_be_announced,
p_usec_occur_rec.start_date,
p_usec_occur_rec.end_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_surname ,
l_given_names,
l_middle_name,
p_usec_occur_rec.preferred_region_code,
p_org_id,
p_usec_occur_rec.occurrence_identifier,
'N',
'N',
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
) RETURNING int_occurs_id INTO l_int_occurs_id;
-- Insert Ref Cd Information
FOR ref_cd_rec IN ref_cd( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
BEGIN
-- The fields which are passed as NULL are OBSOLETE columns as per the Modified Scheduling Interface DLD 1.0
INSERT INTO igs_ps_prefs_sch_int_all (
int_prefs_id ,
int_occurs_id ,
reference_cd ,
reference_code_description ,
reference_cd_type ,
reference_type_description ,
transaction_id ,
unit_section_occurrence_id ,
org_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_PREFS_SCH_INT_S.NEXTVAL,
l_int_occurs_id,
ref_cd_rec.reference_code,
ref_cd_rec.reference_code_description,
ref_cd_rec.reference_code_type,
ref_cd_rec.reference_type_description,
NULL,
NULL,
p_org_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
-- Insert Instructor related Information
FOR instrs_rec IN instrs( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
BEGIN
INSERT INTO igs_ps_sch_instr_all (
int_instruc_id ,
instructor_id ,
surname ,
given_names ,
middle_name ,
int_occurs_id ,
person_name ,
org_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_INSTR_S.NEXTVAL,
instrs_rec.instructor_id,
instrs_rec.surname,
instrs_rec.given_names,
instrs_rec.middle_name,
l_int_occurs_id,
instrs_rec.person_name,
p_org_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
-- Insert Facility related Information
FOR facilities_rec IN facilities ( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
BEGIN
l_section_facility_exist := TRUE;
INSERT INTO igs_ps_sch_faclt_all (
facility_id ,
facility_code ,
facility_description ,
org_id ,
int_occurs_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_FACLT_S.NEXTVAL,
facilities_rec.facility_code,
facilities_rec.facility_description,
p_org_id,
l_int_occurs_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
INSERT INTO igs_ps_sch_faclt_all (
facility_id ,
facility_code ,
facility_description ,
org_id ,
int_occurs_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
IGS_PS_SCH_FACLT_S.NEXTVAL,
unit_facilities_rec.media_code,
unit_facilities_rec.media_description,
p_org_id,
l_int_occurs_id,
g_n_user_id,
SYSDATE,
g_n_user_id,
SYSDATE,
g_n_login_id
);
SELECT pt.*,pt.rowid
FROM igs_ps_unit_ofr_pat pt
WHERE unit_cd=cp_unit_cd
AND version_number=cp_version_number
AND cal_type=cp_cal_type
AND ci_sequence_number=cp_ci_sequence_number;
SELECT us.*,us.rowid
FROM igs_ps_unit_ofr_opt_all us
WHERE us.uoo_id = cp_uoo_id;
SELECT us.*,us.rowid
FROM igs_ps_usec_occurs_all us
WHERE us.unit_section_occurrence_id = cp_unit_section_occurrence_id;
SELECT ur.title, ur.subtitle
FROM igs_ps_usec_ref_v ur
WHERE ur.uoo_id = cp_uoo_id;
SELECT usub.subtitle
FROM igs_ps_unit_subtitle usub
WHERE usub.subtitle_id = cp_subtitle_id ;
ELSIF p_sch_type = 'UPDATE' THEN
l_derd_sch_type := 'USER_UPDATE';
--Get the pattern details and insert in the pattern interface table
FOR pat_cur_rec IN pat_cur(l_cal_type,l_ci_sequence_number,p_c_unit_cd,p_n_version_number) LOOP
IF l_trans_id IS NULL THEN
create_header;
ELSIF usec_occur_rec.schedule_status = 'USER_UPDATE' THEN
l_trans_type := 'UPDATE';
--insert the section record
IF l_valid_occur = FALSE THEN
OPEN c_occur_exists(uoo_cur_rec.uoo_id);
--If no section are there in production also then do nothing else delete the pattern record.
OPEN c_section_int_exists(l_int_pat_id);
IF (p_sch_type = 'REQUEST' AND l_unit_status IS NULL) OR (p_sch_type IN ('UPDATE','CANCEL') ) THEN
DELETE IGS_PS_SCH_PAT_INT WHERE int_pat_id=l_int_pat_id;
--If there are no pattern records in the interface table against the header record then delete the header record.
IF l_trans_id IS NOT NULL THEN
OPEN c_pattern_int_exists(l_trans_id);
DELETE IGS_PS_SCH_HDR_INT_ALL WHERE transaction_id = l_trans_id;
--Update all the production records for the abort_flag='N'
FOR l_pattern_prod_rec IN c_pattern_prod(l_trans_id) LOOP
--Log the pattern in the log file
FND_FILE.NEW_LINE(FND_FILE.LOG,1);
igs_ps_unit_ofr_pat_pkg.update_row (
X_Mode => 'R',
X_RowId => l_prod_pattern.rowid,
X_Unit_Cd => l_prod_pattern.Unit_Cd,
X_Version_Number => l_prod_pattern.Version_Number,
X_Cal_Type => l_prod_pattern.Cal_Type,
X_Ci_Sequence_Number => l_prod_pattern.Ci_Sequence_Number,
X_Ci_Start_Dt => l_prod_pattern.Ci_Start_Dt,
X_Ci_End_Dt => l_prod_pattern.Ci_End_Dt,
x_waitlist_allowed => l_prod_pattern.waitlist_allowed,
x_max_students_per_waitlist => l_prod_pattern.max_students_per_waitlist,
x_delete_flag => l_prod_pattern.delete_flag,
x_abort_flag => 'N'
);
igs_ps_unit_ofr_opt_pkg.update_row (
x_mode => 'R',
x_rowid => l_cur_uoo_rec.rowid,
x_unit_cd => l_cur_uoo_rec.unit_cd,
x_version_number => l_cur_uoo_rec.version_number,
x_cal_type => l_cur_uoo_rec.cal_type,
x_ci_sequence_number => l_cur_uoo_rec.ci_sequence_number,
x_location_cd => l_cur_uoo_rec.location_cd,
x_unit_class => l_cur_uoo_rec.unit_class,
x_uoo_id => l_cur_uoo_rec.uoo_id,
x_ivrs_available_ind => l_cur_uoo_rec.ivrs_available_ind,
x_call_number => l_cur_uoo_rec.call_number,
x_unit_section_status => l_cur_uoo_rec.unit_section_status,
x_unit_section_start_date => l_cur_uoo_rec.unit_section_start_date,
x_unit_section_end_date => l_cur_uoo_rec.unit_section_end_date,
x_enrollment_actual => l_cur_uoo_rec.enrollment_actual,
x_waitlist_actual => l_cur_uoo_rec.waitlist_actual,
x_offered_ind => l_cur_uoo_rec.offered_ind,
x_state_financial_aid => l_cur_uoo_rec.state_financial_aid,
x_grading_schema_prcdnce_ind => l_cur_uoo_rec.grading_schema_prcdnce_ind,
x_federal_financial_aid => l_cur_uoo_rec.federal_financial_aid,
x_unit_quota => l_cur_uoo_rec.unit_quota,
x_unit_quota_reserved_places => l_cur_uoo_rec.unit_quota_reserved_places,
x_institutional_financial_aid => l_cur_uoo_rec.institutional_financial_aid,
x_unit_contact => l_cur_uoo_rec.unit_contact,
x_grading_schema_cd => l_cur_uoo_rec.grading_schema_cd,
x_gs_version_number => l_cur_uoo_rec.gs_version_number,
x_owner_org_unit_cd => l_cur_uoo_rec.owner_org_unit_cd,
x_attendance_required_ind => l_cur_uoo_rec.attendance_required_ind,
x_reserved_seating_allowed => l_cur_uoo_rec.reserved_seating_allowed,
x_special_permission_ind => l_cur_uoo_rec.special_permission_ind,
x_ss_enrol_ind => l_cur_uoo_rec.ss_enrol_ind,
x_ss_display_ind => l_cur_uoo_rec.ss_display_ind,
x_dir_enrollment => l_cur_uoo_rec.dir_enrollment,
x_enr_from_wlst => l_cur_uoo_rec.enr_from_wlst,
x_inq_not_wlst => l_cur_uoo_rec.inq_not_wlst,
x_rev_account_cd => l_cur_uoo_rec.rev_account_cd,
x_anon_unit_grading_ind => l_cur_uoo_rec.anon_unit_grading_ind,
x_anon_assess_grading_ind => l_cur_uoo_rec.anon_assess_grading_ind,
x_non_std_usec_ind => l_cur_uoo_rec.non_std_usec_ind,
x_auditable_ind => l_cur_uoo_rec.auditable_ind,
x_audit_permission_ind => l_cur_uoo_rec.audit_permission_ind,
x_not_multiple_section_flag => l_cur_uoo_rec.not_multiple_section_flag,
x_sup_uoo_id => l_cur_uoo_rec.sup_uoo_id,
x_relation_type => l_cur_uoo_rec.relation_type,
x_default_enroll_flag => l_cur_uoo_rec.default_enroll_flag,
x_abort_flag => 'N'
);
--UPDATE igs_ps_unit_ofr_opt_all set abort_flag='N' where uoo_id=l_section_prod_rec.uoo_id;
igs_ps_usec_occurs_pkg.update_row (
x_rowid => l_usec_occurs_rec.ROWID,
x_unit_section_occurrence_id => l_usec_occurs_rec.unit_section_occurrence_id,
x_uoo_id => l_usec_occurs_rec.uoo_id,
x_monday => l_usec_occurs_rec.monday,
x_tuesday => l_usec_occurs_rec.tuesday,
x_wednesday => l_usec_occurs_rec.wednesday,
x_thursday => l_usec_occurs_rec.thursday,
x_friday => l_usec_occurs_rec.friday,
x_saturday => l_usec_occurs_rec.saturday,
x_sunday => l_usec_occurs_rec.sunday,
x_start_time => l_usec_occurs_rec.start_time,
x_end_time => l_usec_occurs_rec.end_time,
x_building_code => l_usec_occurs_rec.building_code,
x_room_code => l_usec_occurs_rec.room_code,
x_schedule_status => 'PROCESSING',
x_status_last_updated => SYSDATE,
x_instructor_id => l_usec_occurs_rec.instructor_id,
X_attribute_category => l_usec_occurs_rec.attribute_category,
X_attribute1 => l_usec_occurs_rec.attribute1,
X_attribute2 => l_usec_occurs_rec.attribute2,
X_attribute3 => l_usec_occurs_rec.attribute3,
X_attribute4 => l_usec_occurs_rec.attribute4,
X_attribute5 => l_usec_occurs_rec.attribute5,
X_attribute6 => l_usec_occurs_rec.attribute6,
X_attribute7 => l_usec_occurs_rec.attribute7,
X_attribute8 => l_usec_occurs_rec.attribute8,
X_attribute9 => l_usec_occurs_rec.attribute9,
X_attribute10 => l_usec_occurs_rec.attribute10,
X_attribute11 => l_usec_occurs_rec.attribute11,
X_attribute12 => l_usec_occurs_rec.attribute12,
X_attribute13 => l_usec_occurs_rec.attribute13,
X_attribute14 => l_usec_occurs_rec.attribute14,
X_attribute15 => l_usec_occurs_rec.attribute15,
X_attribute16 => l_usec_occurs_rec.attribute16,
X_attribute17 => l_usec_occurs_rec.attribute17,
X_attribute18 => l_usec_occurs_rec.attribute18,
X_attribute19 => l_usec_occurs_rec.attribute19,
X_attribute20 => l_usec_occurs_rec.attribute20,
x_error_text => l_usec_occurs_rec.error_text,
x_mode => 'R',
X_start_date => l_usec_occurs_rec.start_date,
X_end_date => l_usec_occurs_rec.end_date,
X_to_be_announced => l_usec_occurs_rec.to_be_announced,
x_dedicated_building_code => l_usec_occurs_rec.dedicated_building_code,
x_dedicated_room_code => l_usec_occurs_rec.dedicated_room_code,
x_preferred_building_code => l_usec_occurs_rec.preferred_building_code,
x_preferred_room_code => l_usec_occurs_rec.preferred_room_code,
x_inst_notify_ind => l_usec_occurs_rec.inst_notify_ind,
x_notify_status => l_usec_occurs_rec.notify_status,
x_preferred_region_code => l_usec_occurs_rec.preferred_region_code,
x_no_set_day_ind => l_usec_occurs_rec.no_set_day_ind,
x_cancel_flag => 'N',
x_occurrence_identifier => l_usec_occurs_rec.occurrence_identifier,
x_abort_flag => 'N'
);
--UPDATE igs_ps_usec_occurs_all set abort_flag='N', schedule_status='PROCESSING',status_last_updated=SYSDATE,cancel_flag='N' where unit_section_occurrence_id=l_occurrence_prod_rec.unit_section_occurrence_id;
IF p_sch_type IS NOT NULL AND p_sch_type NOT IN ('REQUEST', 'UPDATE', 'CANCEL' ) THEN
FND_MESSAGE.SET_NAME('IGS','IGS_PS_INVALID_SCHTYPE');
SELECT 'X'
FROM igs_ps_usec_occurs
WHERE schedule_status='PROCESSING'
AND UOO_ID=p_uoo_id;
SELECT 'X'
FROM igs_ps_usec_occurs
WHERE schedule_status='PROCESSING'
AND UOO_ID=p_uoo_id
AND UNIT_SECTION_OCCURRENCE_ID=p_usec_id;
SELECT DISTINCT calendar_type,sequence_number
FROM igs_ps_sch_pat_int;
Purpose : To update the Scheduling Status of all unit section occurences
belonging to the unit section passed whenever there is
a change in a Unit Section's location/maximum enrollments/Unit Section Status
If a Unit Section is closed then also submit a request to the scheduler to cancel
the scheduled information for that Unit Section.
Know limitations, enhancements or remarks
Change History
Who When What
smvk 11-Mar-2003 Bug # 2831065. Modified the cursor c_usec_occurs to update schedule status of appropriate USO.
(i.e) Based on earlier and latest schedule status of USO.
ssawhney 13-Nov-2000 chr(0)as the terminator in fnd_request.submit_request
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_loc_modified_chk ( p_uoo_id IN NUMBER ) IS -- To check if location code has undergone modification
SELECT location_cd
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT enrollment_maximum ,
override_enrollment_max,
enrollment_expected
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = p_uoo_id;
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
);
upd_usec_occurs_schd_status(p_uoo_id,'USER_UPDATE');
upd_usec_occurs_schd_status(p_uoo_id,'USER_UPDATE');
SELECT room_id,building_id,room_cd,description,primary_use_cd,capacity,closed_ind
FROM igs_ad_room
ORDER BY 1;
SELECT building_id,location_cd,building_cd,description,closed_ind
FROM igs_ad_building
ORDER BY 1;
SELECT location_cd,description,location_type,mail_dlvry_wrk_days,coord_person_id,closed_ind
FROM igs_ad_location
ORDER BY 1;
SELECT lookup_code,meaning,description,tag,start_date_active,end_date_active,enabled_flag,closed_ind
FROM igs_lookup_values
WHERE lookup_type = 'IGS_OR_LOC_REGION'
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,TRUNC(SYSDATE))) AND TRUNC(NVL(end_date_active,TRUNC(SYSDATE)))
ORDER BY lookup_code;
SELECT meaning
FROM IGS_LOOKUP_VALUES
WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
LOOKUP_CODE='IGS_PS_SCH_ROOM' ;
SELECT meaning
FROM IGS_LOOKUP_VALUES
WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
LOOKUP_CODE='IGS_PS_SCH_BLDG';
SELECT meaning
FROM IGS_LOOKUP_VALUES
WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
LOOKUP_CODE='IGS_PS_SCH_LOC';
SELECT meaning
FROM IGS_LOOKUP_VALUES
WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
LOOKUP_CODE='IGS_PS_SCH_PRF_REG';
SELECT region_cd
FROM igs_or_loc_region
WHERE location_cd = cp_c_loc_cd;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME='utl_file_dir';
SELECT INSTR(l_dbvalue,l_fndvalue,l_end_comma_index) INTO l_start_str_index FROM DUAL;
SELECT INSTR(SUBSTR(l_dbvalue,1,l_start_str_index),',',-1)+1
INTO l_start_comma_index
FROM DUAL;
SELECT DECODE(
INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1,
INSTR(l_dbvalue,',',l_start_str_index))
INTO l_end_comma_index
FROM DUAL;
SELECT LTRIM(RTRIM(SUBSTR(l_dbvalue,l_start_comma_index, l_end_comma_index-l_start_comma_index)))
INTO l_temp
FROM DUAL;
smvk 29-Jun-2003 Bug # 3060089. Modified the procedure update_info to display the message 'IGS_PS_SCH_TBA_USO_NSD_USEC'.
smvk 13-May-2003 Created a local procedures update_info and local function get_location_description for code optimization
and coded the validation mentioned PSP Scheduling inteface enhancements TD.Enh Bug #2833850.
smvk 26-Jun-2002 In interface table, when there is no unit section occurrence exist in valid state
to populate in production table, proper message should log in the log file.
as per the Bug # 2427725
schodava 30-Jan-2001 Modified Scheduling DLD Changes
bayadav 28-May-2001 SCheduling Interface DLD Changes
smvk 31-Dec-2002 Bug # 2710978. Collecting the statistics of the interface table as per standards.
(reverse chronological order - newest change first)
***************************************************************/
l_originator igs_ps_sch_hdr_int.originator%TYPE;
SELECT sui.*
FROM igs_ps_sch_usec_int_all sui
WHERE sui.import_done_flag = 'N'
FOR UPDATE NOWAIT
ORDER BY sui.calendar_type, sui.sequence_number, sui.uoo_id;
SELECT ipsi.rowid,ipsi.*
FROM igs_ps_sch_int_all ipsi, igs_ps_usec_occurs_all uso
WHERE ipsi.transaction_type IN ('REQUEST','UPDATE','CANCEL') AND ipsi.schedule_status IN ('OK','ERROR')
AND ipsi.unit_section_occurrence_id=uso.unit_section_occurrence_id
AND uso.schedule_status <> 'CANCELLED'
AND uso.abort_flag = 'N'
AND ipsi.import_done_flag = 'N'
AND ipsi.int_usec_id = cp_int_usec_id
FOR UPDATE NOWAIT;
SELECT originator
FROM igs_ps_sch_hdr_int_ALL
WHERE transaction_id=p_transaction_id;
SELECT *
FROM igs_ps_usec_occurs_all ipsuo
WHERE unit_section_occurrence_id=p_usec_id
FOR UPDATE NOWAIT;
SELECT a.unit_section_start_date us_start_date,
a.unit_section_end_date us_end_date,
b.start_dt tp_start_date,
b.end_dt tp_end_date
FROM igs_ps_unit_ofr_opt_all a,
igs_ca_inst_all b
WHERE a.uoo_id = cp_n_uoo_id
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.sequence_number;
SELECT 'x'
FROM igs_ad_building_all
WHERE building_id = cp_n_building_id
AND ROWNUM < 2 ;
SELECT 'x'
FROM igs_ad_room_all
WHERE room_id = cp_n_room_id
AND ROWNUM < 2 ;
SELECT 'x'
FROM igs_ad_room_all
WHERE building_id = cp_n_building_id
AND room_id = cp_n_room_id
AND ROWNUM < 2 ;
SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag,date_ovrd_flag
FROM igs_ps_sch_ocr_cfig;
PROCEDURE update_info( p_n_prd_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
p_n_int_uso_id igs_ps_sch_int_all.unit_section_occurrence_id%TYPE,
p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
) AS
CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
SELECT rowid,uso.*
FROM igs_ps_usec_occurs_all uso
WHERE uso.unit_section_occurrence_id=cp_n_uso_id;
SELECT uso.rowid, uso.*, usec.int_pat_id
FROM igs_ps_sch_int_all uso, igs_ps_sch_usec_int_all usec
WHERE uso.int_usec_id = usec.int_usec_id
AND uso.int_occurs_id = cp_n_uso_id;
SELECT *
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id=cp_n_uso_id;
SELECT 'x'
FROM igs_ps_usec_occurs_all uso,
igs_ps_unit_ofr_opt_all uoo
WHERE uoo.non_std_usec_ind = 'Y'
AND uoo.uoo_id = uso.uoo_id
AND uso.unit_section_occurrence_id=cp_n_uso_id;
SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag
FROM igs_ps_sch_ocr_cfig;
SELECT 'X'
FROM igs_ps_sch_int_all
WHERE int_usec_id=cp_int_usec_id
AND import_done_flag='N';
SELECT 'X'
FROM igs_ps_sch_usec_int_all
WHERE int_pat_id=cp_int_pat_id
AND import_done_flag='N';
igs_ps_usec_occurs_pkg.update_row
(
X_Mode => 'R',
X_RowId => rec_prd_uso.rowid ,
X_unit_section_occurrence_id => rec_prd_uso.unit_section_occurrence_id,
X_uoo_id => rec_prd_uso.uoo_id,
X_monday => p_c_monday,
X_tuesday => p_c_tuesday,
X_wednesday => p_c_wednesday,
X_thursday => p_c_thursday,
X_friday => p_c_friday,
X_saturday => p_c_saturday,
X_sunday => p_c_sunday,
X_start_time => p_d_uso_start_tm,
X_end_time => p_d_uso_end_tm,
X_building_code => l_building_code,
X_room_code => l_room_code,
X_schedule_status => p_c_sch_status,
X_status_last_updated => sysdate,
X_instructor_id => rec_prd_uso.instructor_id,
X_attribute_category => rec_prd_uso.attribute_category,
X_attribute1 => rec_prd_uso.attribute1,
X_attribute2 => rec_prd_uso.attribute2,
X_attribute3 => rec_prd_uso.attribute3,
X_attribute4 => rec_prd_uso.attribute4,
X_attribute5 => rec_prd_uso.attribute5,
X_attribute6 => rec_prd_uso.attribute6,
X_attribute7 => rec_prd_uso.attribute7,
X_attribute8 => rec_prd_uso.attribute8,
X_attribute9 => rec_prd_uso.attribute9,
X_attribute10 => rec_prd_uso.attribute10,
X_attribute11 => rec_prd_uso.attribute11,
X_attribute12 => rec_prd_uso.attribute12,
X_attribute13 => rec_prd_uso.attribute13,
X_attribute14 => rec_prd_uso.attribute14,
X_attribute15 => rec_prd_uso.attribute15,
X_attribute16 => rec_prd_uso.attribute16,
X_attribute17 => rec_prd_uso.attribute17,
X_attribute18 => rec_prd_uso.attribute18,
X_attribute19 => rec_prd_uso.attribute19,
X_attribute20 => rec_prd_uso.attribute20,
X_error_text => p_c_err_text,
X_start_date => p_d_uso_start_dt,
X_end_date => p_d_uso_end_dt,
X_to_be_Announced => p_c_tba_uso,
X_dedicated_building_code => l_n_ded_bld,
X_dedicated_room_code => l_n_ded_rom,
X_preferred_building_code => l_n_prf_bld,
X_preferred_room_code => l_n_prf_rom,
X_inst_notify_ind => rec_prd_uso.inst_notify_ind,
X_notify_status => rec_prd_uso.notify_status,
x_preferred_region_code => l_c_prf_reg,
x_no_set_day_ind => rec_prd_uso.no_set_day_ind,
x_cancel_flag => rec_prd_uso.cancel_flag,
x_occurrence_identifier => rec_prd_uso.occurrence_identifier,
x_abort_flag => rec_prd_uso.abort_flag
);
UPDATE igs_ps_sch_int_all set transaction_type=p_c_trans_type,schedule_status=p_c_sch_status,error_text=p_c_err_text,
import_done_flag=l_import_done WHERE int_occurs_id = p_n_int_uso_id;
--Update the interface section import_done_flag
OPEN cur_section_import(rec_int_uso.int_usec_id);
UPDATE igs_ps_sch_usec_int_all set import_done_flag='Y' WHERE int_usec_id = rec_int_uso.int_usec_id;
UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = rec_int_uso.int_pat_id;
igs_ps_usec_occurs_pkg.update_row
(
X_Mode => 'R',
X_RowId => rec_prd_uso.rowid ,
X_unit_section_occurrence_id => rec_prd_uso.unit_section_occurrence_id,
X_uoo_id => rec_prd_uso.uoo_id,
X_monday => rec_prd_uso.monday,
X_tuesday => rec_prd_uso.tuesday,
X_wednesday => rec_prd_uso.wednesday,
X_thursday => rec_prd_uso.thursday,
X_friday => rec_prd_uso.friday,
X_saturday => rec_prd_uso.saturday,
X_sunday => rec_prd_uso.sunday,
X_start_time => rec_prd_uso.start_time,
X_end_time => rec_prd_uso.end_time,
X_building_code => rec_prd_uso.building_code,
X_room_code => rec_prd_uso.room_code,
X_schedule_status => l_c_sch_status,
X_status_last_updated => sysdate,
X_instructor_id => rec_prd_uso.instructor_id,
X_attribute_category => rec_prd_uso.attribute_category,
X_attribute1 => rec_prd_uso.attribute1,
X_attribute2 => rec_prd_uso.attribute2,
X_attribute3 => rec_prd_uso.attribute3,
X_attribute4 => rec_prd_uso.attribute4,
X_attribute5 => rec_prd_uso.attribute5,
X_attribute6 => rec_prd_uso.attribute6,
X_attribute7 => rec_prd_uso.attribute7,
X_attribute8 => rec_prd_uso.attribute8,
X_attribute9 => rec_prd_uso.attribute9,
X_attribute10 => rec_prd_uso.attribute10,
X_attribute11 => rec_prd_uso.attribute11,
X_attribute12 => rec_prd_uso.attribute12,
X_attribute13 => rec_prd_uso.attribute13,
X_attribute14 => rec_prd_uso.attribute14,
X_attribute15 => rec_prd_uso.attribute15,
X_attribute16 => rec_prd_uso.attribute16,
X_attribute17 => rec_prd_uso.attribute17,
X_attribute18 => rec_prd_uso.attribute18,
X_attribute19 => rec_prd_uso.attribute19,
X_attribute20 => rec_prd_uso.attribute20,
X_error_text => l_c_err_text,
X_start_date => rec_prd_uso.start_date,
X_end_date => rec_prd_uso.end_date,
X_to_be_Announced => rec_prd_uso.to_be_announced,
X_dedicated_building_code => rec_prd_uso.dedicated_building_code,
X_dedicated_room_code => rec_prd_uso.dedicated_room_code,
X_preferred_building_code => rec_prd_uso.preferred_building_code,
X_preferred_room_code => rec_prd_uso.preferred_room_code,
X_inst_notify_ind => rec_prd_uso.inst_notify_ind,
X_notify_status => rec_prd_uso.notify_status,
x_preferred_region_code => rec_prd_uso.preferred_region_code,
x_no_set_day_ind => rec_prd_uso.no_set_day_ind,
x_cancel_flag => rec_prd_uso.cancel_flag,
x_occurrence_identifier => rec_prd_uso.occurrence_identifier,
x_abort_flag => rec_prd_uso.abort_flag
);
-- Update interface table (i.e. Transaction Type) by transaction status as 'INCOMPLETE' and Schedule status as 'ERROR'
UPDATE igs_ps_sch_int_all set transaction_type=l_c_trans_type,schedule_status=l_c_sch_status,error_text=l_c_err_text
WHERE int_occurs_id = p_n_int_uso_id;
END update_info;
SELECT description
FROM igs_ad_location_all
WHERE location_cd = cp_c_location_cd;
IF l_status = 'SCHEDULED' OR (l_originator = 'EXTERNAL' AND l_status IN ('PROCESSING','USER_UPDATE','USER_CANCEL')) THEN
IF l_status = 'SCHEDULED' THEN
fnd_message.set_name('IGS','IGS_PS_USO_SCHED');
IF l_transaction_type IN ('REQUEST','UPDATE') THEN
----
OPEN check_ovrd;
END IF; -- End Transcation Type(REQUEST,UPDATE,CANCEL)
update_info( l_ipsuo.unit_section_occurrence_id,
fetch_records_inter.int_occurs_id,
l_set_scheduled_status,
l_set_transaction_type,
l_building_code,
l_room_code,
l_d_uso_start_date,
l_d_uso_end_date,
l_start_time,
l_end_time,
l_sunday,
l_monday,
l_tuesday,
l_wednesday,
l_thursday,
l_friday,
l_saturday,
l_tba_status,
l_error_text
);
SELECT a.ROWID intrid,a.*,b.ROWID prodrid,
c.unit_cd UNIT_CODE,c.version_number UNIT_VERSION_NUMBER,c.location_cd LOCATION_CODE,c.unit_class UNIT_CLS
FROM igs_ps_sch_int_all a,Igs_ps_usec_occurs_all b, igs_ps_sch_usec_int_all c
WHERE a.unit_section_occurrence_id=b.unit_section_occurrence_id
AND a.int_usec_id = c.int_usec_id
AND c.calendar_type=cp_cal_type
AND c.sequence_number =cp_sequence_number
AND c.unit_cd= NVL (p_unit_cd, c.unit_cd)
AND c.version_number= NVL (p_version_number, c.version_number)
AND c.location_cd=NVL (p_location, c.location_cd)
AND c.unit_class= NVL (p_unit_class, c.unit_class)
AND (p_cancel_only ='N' OR b.cancel_flag = 'Y')
AND a.schedule_status IS NULL;
SELECT us.ROWID intrid,us.*
FROM igs_ps_sch_usec_int_all us
WHERE calendar_type=cp_cal_type
AND sequence_number = cp_sequence_number
AND unit_cd= NVL (p_unit_cd, us.unit_cd)
AND version_number=NVL (p_version_number, us.version_number)
AND location_cd=NVL (p_location, us.location_cd)
AND unit_class= NVL (p_unit_class, us.unit_class);
SELECT pt.ROWID intrid,pt.*
FROM igs_ps_sch_pat_int pt
WHERE pt.calendar_type=cp_cal_type
AND pt.sequence_number =cp_sequence_number
AND pt.unit_cd= NVL (p_unit_cd,pt.unit_cd)
AND pt.version_number=NVL (p_version_number, pt.version_number);
SELECT count(*)
FROM igs_ps_sch_int_all a
WHERE a.int_usec_id = cp_n_int_usec_id
AND a.abort_flag = 'N';
SELECT count(*)
FROM igs_ps_sch_usec_int_all a
WHERE a.int_pat_id = cp_n_int_pat_id
AND a.abort_flag = 'N';
SELECT b.ROWID prodrid,b.*
FROM igs_ps_usec_occurs_all b
WHERE rowid = cp_rowid;
SELECT a.rowid prodrid,a.*
FROM igs_ps_unit_ofr_opt_all a
WHERE a.uoo_id=cp_uoo_id;
SELECT pt.ROWID prodrid,pt.*
FROM igs_ps_unit_ofr_pat_all pt
WHERE cal_type=cp_cal_type
AND ci_sequence_number =cp_sequence_number
AND unit_cd= cp_unit_cd
AND version_number=cp_version_number;
UPDATE igs_ps_sch_int_all SET abort_flag = 'Y'
WHERE ROWID = c_int_uso_ss_rec.intrid;
igs_ps_usec_occurs_pkg.update_row (
X_RowId => c_int_uso_prod_rec.prodrid ,
X_unit_section_occurrence_id => c_int_uso_prod_rec.unit_section_occurrence_id,
X_uoo_id => c_int_uso_prod_rec.uoo_id,
X_monday => c_int_uso_prod_rec.monday,
X_tuesday => c_int_uso_prod_rec.tuesday,
X_wednesday => c_int_uso_prod_rec.wednesday,
X_thursday => c_int_uso_prod_rec.thursday,
X_friday => c_int_uso_prod_rec.friday,
X_saturday => c_int_uso_prod_rec.saturday,
X_sunday => c_int_uso_prod_rec.sunday,
X_start_time => c_int_uso_prod_rec.start_time,
X_end_time => c_int_uso_prod_rec.end_time,
X_building_code => c_int_uso_prod_rec.building_code,
X_room_code => c_int_uso_prod_rec.room_code,
X_schedule_status => NULL,
X_status_last_updated => SYSDATE,
X_instructor_id => c_int_uso_prod_rec.instructor_id,
X_attribute_category => c_int_uso_prod_rec.attribute_category,
X_attribute1 => c_int_uso_prod_rec.attribute1,
X_attribute2 => c_int_uso_prod_rec.attribute2,
X_attribute3 => c_int_uso_prod_rec.attribute3,
X_attribute4 => c_int_uso_prod_rec.attribute4,
X_attribute5 => c_int_uso_prod_rec.attribute5,
X_attribute6 => c_int_uso_prod_rec.attribute6,
X_attribute7 => c_int_uso_prod_rec.attribute7,
X_attribute8 => c_int_uso_prod_rec.attribute8,
X_attribute9 => c_int_uso_prod_rec.attribute9,
X_attribute10 => c_int_uso_prod_rec.attribute10,
X_attribute11 => c_int_uso_prod_rec.attribute11,
X_attribute12 => c_int_uso_prod_rec.attribute12,
X_attribute13 => c_int_uso_prod_rec.attribute13,
X_attribute14 => c_int_uso_prod_rec.attribute14,
X_attribute15 => c_int_uso_prod_rec.attribute15,
X_attribute16 => c_int_uso_prod_rec.attribute16,
X_attribute17 => c_int_uso_prod_rec.attribute17,
X_attribute18 => c_int_uso_prod_rec.attribute18,
X_attribute19 => c_int_uso_prod_rec.attribute19,
X_attribute20 => c_int_uso_prod_rec.attribute20,
X_error_text => c_int_uso_prod_rec.error_text ,
x_mode => 'R',
X_start_date => c_int_uso_prod_rec.start_date,
X_end_date => c_int_uso_prod_rec.end_date,
X_to_be_announced => c_int_uso_prod_rec.to_be_announced,
X_inst_notify_ind => c_int_uso_prod_rec.inst_notify_ind,
X_notify_status => c_int_uso_prod_rec.notify_status,
X_dedicated_building_code => c_int_uso_prod_rec.dedicated_building_code,
X_dedicated_room_code => c_int_uso_prod_rec.dedicated_room_code,
X_preferred_building_code => c_int_uso_prod_rec.preferred_building_code,
X_preferred_room_code => c_int_uso_prod_rec.preferred_room_code,
X_preferred_region_code => c_int_uso_prod_rec.preferred_region_code,
X_no_set_day_ind => c_int_uso_prod_rec.no_set_day_ind,
x_cancel_flag => 'N',
x_occurrence_identifier => c_int_uso_prod_rec.occurrence_identifier,
x_abort_flag => 'Y'
);
UPDATE igs_ps_sch_usec_int_all SET abort_flag = l_abort_flag
WHERE ROWID =c_int_usec_ss_rec.intrid ;
--update the production table
OPEN c_prod_usec(c_int_usec_ss_rec.uoo_id);
igs_ps_unit_ofr_opt_pkg.update_row(
x_rowid => c_prod_usec_rec.prodrid,
x_unit_cd => c_prod_usec_rec.unit_cd,
x_version_number => c_prod_usec_rec.version_number,
x_cal_type => c_prod_usec_rec.cal_type,
x_ci_sequence_number => c_prod_usec_rec.ci_sequence_number,
x_location_cd => c_prod_usec_rec.location_cd,
x_unit_class => c_prod_usec_rec.unit_class,
x_uoo_id => c_prod_usec_rec.uoo_id,
x_ivrs_available_ind => c_prod_usec_rec.ivrs_available_ind,
x_call_number => c_prod_usec_rec.call_number,
x_unit_section_status => c_prod_usec_rec.unit_section_status,
x_unit_section_start_date => c_prod_usec_rec.unit_section_start_date,
x_unit_section_end_date => c_prod_usec_rec.unit_section_end_date,
x_enrollment_actual => c_prod_usec_rec.enrollment_actual,
x_waitlist_actual => c_prod_usec_rec.waitlist_actual,
x_offered_ind => c_prod_usec_rec.offered_ind,
x_state_financial_aid => c_prod_usec_rec.state_financial_aid,
x_grading_schema_prcdnce_ind => c_prod_usec_rec.grading_schema_prcdnce_ind,
x_federal_financial_aid => c_prod_usec_rec.federal_financial_aid,
x_unit_quota => c_prod_usec_rec.unit_quota,
x_unit_quota_reserved_places => c_prod_usec_rec.unit_quota_reserved_places,
x_institutional_financial_aid => c_prod_usec_rec.institutional_financial_aid,
x_grading_schema_cd => c_prod_usec_rec.grading_schema_cd,
x_gs_version_number => c_prod_usec_rec.gs_version_number,
x_unit_contact => c_prod_usec_rec.unit_contact,
x_mode => 'R',
x_ss_enrol_ind => c_prod_usec_rec.ss_enrol_ind,
x_owner_org_unit_cd => c_prod_usec_rec.owner_org_unit_cd,
x_attendance_required_ind => c_prod_usec_rec.attendance_required_ind,
x_reserved_seating_allowed => c_prod_usec_rec.reserved_seating_allowed,
x_ss_display_ind => c_prod_usec_rec.ss_display_ind,
x_special_permission_ind => c_prod_usec_rec.special_permission_ind,
x_dir_enrollment => c_prod_usec_rec.dir_enrollment,
x_enr_from_wlst => c_prod_usec_rec.enr_from_wlst,
x_inq_not_wlst => c_prod_usec_rec.inq_not_wlst,
x_rev_account_cd => c_prod_usec_rec.rev_account_cd,
x_anon_unit_grading_ind => c_prod_usec_rec.anon_unit_grading_ind,
x_anon_assess_grading_ind => c_prod_usec_rec.anon_assess_grading_ind,
x_non_std_usec_ind => c_prod_usec_rec.non_std_usec_ind,
x_auditable_ind => c_prod_usec_rec.auditable_ind,
x_audit_permission_ind => c_prod_usec_rec.audit_permission_ind,
x_not_multiple_section_flag => c_prod_usec_rec.not_multiple_section_flag,
x_sup_uoo_id => c_prod_usec_rec.sup_uoo_id,
x_relation_type => c_prod_usec_rec.relation_type,
x_default_enroll_flag => c_prod_usec_rec.default_enroll_flag,
x_abort_flag => l_abort_flag
);
UPDATE igs_ps_sch_pat_int SET abort_flag = l_abort_flag
WHERE ROWID =c_int_pat_ss_rec.intrid ;
--update the production table
OPEN c_prod_pat(l_ci_sequence_number,l_cal_type,c_int_pat_ss_rec.unit_cd ,c_int_pat_ss_rec.version_number );
igs_ps_unit_ofr_pat_pkg.update_row (
x_rowid => c_prod_pat_rec.prodrid ,
x_unit_cd => c_prod_pat_rec.unit_cd,
x_version_number => c_prod_pat_rec.version_number,
x_ci_sequence_number => c_prod_pat_rec.ci_sequence_number,
x_cal_type => c_prod_pat_rec.cal_type,
x_ci_start_dt => c_prod_pat_rec.ci_start_dt,
x_ci_end_dt => c_prod_pat_rec.ci_end_dt,
x_waitlist_allowed => c_prod_pat_rec.waitlist_allowed,
x_max_students_per_waitlist => c_prod_pat_rec.max_students_per_waitlist,
x_mode => 'R' ,
x_delete_flag => c_prod_pat_rec.delete_flag,
x_abort_flag => l_abort_flag
);
SELECT purge_type
FROM igs_ps_sch_prg_cfig
WHERE teaching_calendar_type=cp_teaching_calendar_type;
SELECT start_dt,end_dt
FROM igs_ca_inst_all
WHERE cal_type=cp_cal_type
AND sequence_number=cp_sequence_number;
SELECT uso.int_occurs_id int_occurs_id,uso.occurrence_identifier, us.unit_cd, us.version_number, us.unit_class, us.location_cd
FROM igs_ps_sch_int_all uso, igs_ps_sch_usec_int_all us
WHERE us.calendar_type= cp_c_cal_type
AND us.sequence_number=cp_n_seq_num
AND us.int_usec_id = uso.int_usec_id
AND ((uso.transaction_type = 'COMPLETE' AND cp_completed ='Y')
OR (uso.schedule_status = 'CANCELLED' AND cp_cancelled ='Y')
OR (uso.abort_flag = 'Y' AND cp_aborted ='Y'))
AND (uso.import_done_flag = 'Y' OR (uso.import_done_flag='N' AND (uso.abort_flag = 'Y' AND cp_aborted ='Y')));
SELECT us.int_usec_id, us.unit_cd, us.version_number, us.unit_class, us.location_cd
FROM igs_ps_sch_usec_int_all us
WHERE us.calendar_type= cp_c_cal_type
AND us.sequence_number=cp_n_seq_num
AND (import_done_flag = 'Y' OR (import_done_flag='N' AND (us.abort_flag = 'Y' AND cp_aborted ='Y')))
AND NOT EXISTS (SELECT 'X' FROM igs_ps_sch_int_all uso WHERE uso.int_usec_id=us.int_usec_id) ;
SELECT pat.int_pat_id, pat.unit_cd,pat.version_number
FROM igs_ps_sch_pat_int pat
WHERE pat.calendar_type= cp_c_cal_type
AND pat.sequence_number=cp_n_seq_num
AND (import_done_flag = 'Y' OR (import_done_flag='N' AND (pat.abort_flag = 'Y' AND cp_aborted ='Y')))
AND NOT EXISTS (SELECT 'X' FROM igs_ps_sch_usec_int_all us WHERE us.int_pat_id=pat.int_pat_id) ;
SELECT hdr.transaction_id
FROM igs_ps_sch_hdr_int hdr
WHERE NOT EXISTS ( SELECT 'X' FROM igs_ps_sch_pat_int pat WHERE pat.transaction_id = hdr.transaction_id);
DELETE FROM igs_ps_prefs_sch_int_all WHERE int_occurs_id=rec_uso.int_occurs_id;
DELETE FROM igs_ps_sch_faclt_all WHERE int_occurs_id=rec_uso.int_occurs_id;
DELETE FROM igs_ps_sch_instr_all WHERE int_occurs_id=rec_uso.int_occurs_id;
DELETE FROM igs_ps_sch_int_all WHERE int_occurs_id=rec_uso.int_occurs_id;
DELETE FROM igs_ps_sch_x_usec_int_all WHERE int_usec_id=rec_usec.int_usec_id;
DELETE FROM igs_ps_sch_mwc_all WHERE int_usec_id=rec_usec.int_usec_id;
DELETE FROM igs_ps_sch_usec_int_all WHERE int_usec_id=rec_usec.int_usec_id;
DELETE FROM igs_ps_sch_loc_int WHERE int_pat_id=rec_pat.int_pat_id;
DELETE FROM igs_ps_sch_fac_int WHERE int_pat_id=rec_pat.int_pat_id;
DELETE FROM igs_ps_sch_pat_int WHERE int_pat_id=rec_pat.int_pat_id;
DELETE FROM igs_ps_sch_hdr_int_all WHERE transaction_id=rec_header.transaction_id;
SELECT enrollment_maximum
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_n_uoo_id;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_c_cal_type
AND sequence_number = cp_n_ci_sequence_number;
SELECT first_name , last_name
FROM igs_pe_person_base_v
WHERE person_id = cp_n_lead_instructor_id;
SELECT description
FROM igs_ad_building_all
WHERE building_id = cp_bld_code;
SELECT description
FROM igs_ad_room_all
WHERE room_id = cp_rom_code;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type=cp_lookup_type
AND lookup_code=cp_lookup_code;
SELECT alternate_code
FROM IGS_CA_INST_ALL
WHERE cal_type = cp_c_cal_type
AND sequence_number = cp_n_seq_num;
PROCEDURE update_occurrence_status(
p_unit_section_occurrence_id IN NUMBER,
p_scheduled_status IN VARCHAR2,
p_cancel_flag IN VARCHAR2
) IS
/**********************************************************************
Created By : sarakshi
Date Created On : 12-May-2005
Purpose : To update the schedule status to 'USER_UPDATE'
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
************************************************************************/
CURSOR c_occurs IS
SELECT uso.*,uso.ROWID
FROM igs_ps_usec_occurs_all uso
WHERE unit_section_occurrence_id=p_unit_section_occurrence_id;
igs_ps_usec_occurs_pkg.update_row (
x_rowid => l_usec_occurs_rec.rowid,
x_unit_section_occurrence_id => l_usec_occurs_rec.unit_section_occurrence_id,
x_uoo_id => l_usec_occurs_rec.uoo_id,
x_monday => l_usec_occurs_rec.monday,
x_tuesday => l_usec_occurs_rec.tuesday,
x_wednesday => l_usec_occurs_rec.wednesday,
x_thursday => l_usec_occurs_rec.thursday,
x_friday => l_usec_occurs_rec.friday,
x_saturday => l_usec_occurs_rec.saturday,
x_sunday => l_usec_occurs_rec.sunday,
x_start_time => l_usec_occurs_rec.start_time,
x_end_time => l_usec_occurs_rec.end_time,
x_building_code => l_usec_occurs_rec.building_code,
x_room_code => l_usec_occurs_rec.room_code,
x_schedule_status => p_scheduled_status,
x_status_last_updated => l_usec_occurs_rec.status_last_updated,
x_instructor_id => l_usec_occurs_rec.instructor_id,
X_attribute_category => l_usec_occurs_rec.attribute_category,
X_attribute1 => l_usec_occurs_rec.attribute1,
X_attribute2 => l_usec_occurs_rec.attribute2,
X_attribute3 => l_usec_occurs_rec.attribute3,
X_attribute4 => l_usec_occurs_rec.attribute4,
X_attribute5 => l_usec_occurs_rec.attribute5,
X_attribute6 => l_usec_occurs_rec.attribute6,
X_attribute7 => l_usec_occurs_rec.attribute7,
X_attribute8 => l_usec_occurs_rec.attribute8,
X_attribute9 => l_usec_occurs_rec.attribute9,
X_attribute10 => l_usec_occurs_rec.attribute10,
X_attribute11 => l_usec_occurs_rec.attribute11,
X_attribute12 => l_usec_occurs_rec.attribute12,
X_attribute13 => l_usec_occurs_rec.attribute13,
X_attribute14 => l_usec_occurs_rec.attribute14,
X_attribute15 => l_usec_occurs_rec.attribute15,
X_attribute16 => l_usec_occurs_rec.attribute16,
X_attribute17 => l_usec_occurs_rec.attribute17,
X_attribute18 => l_usec_occurs_rec.attribute18,
X_attribute19 => l_usec_occurs_rec.attribute19,
X_attribute20 => l_usec_occurs_rec.attribute20,
x_error_text => l_usec_occurs_rec.error_text,
x_mode => 'R',
X_start_date => l_usec_occurs_rec.start_date,
X_end_date => l_usec_occurs_rec.end_date,
X_to_be_announced => l_usec_occurs_rec.to_be_announced,
x_dedicated_building_code => l_usec_occurs_rec.dedicated_building_code,
x_dedicated_room_code => l_usec_occurs_rec.dedicated_room_code,
x_preferred_building_code => l_usec_occurs_rec.preferred_building_code,
x_preferred_room_code => l_usec_occurs_rec.preferred_room_code,
x_inst_notify_ind => l_usec_occurs_rec.inst_notify_ind,
x_notify_status => l_usec_occurs_rec.notify_status,
x_preferred_region_code => l_usec_occurs_rec.preferred_region_code,
x_no_set_day_ind => l_usec_occurs_rec.no_set_day_ind,
x_cancel_flag => p_cancel_flag,
x_occurrence_identifier => l_usec_occurs_rec.occurrence_identifier,
x_abort_flag => l_usec_occurs_rec.abort_flag
);