[Home] [Help]
3: /* Change History
4: Who When What
5: sarakshi 05-May-2005 Bug#4349740, modified upd_usec_occurs_schd_status, changed the main if condition.
6: smvk 29-Jun-2003 Bug # 3060089. Modified the procedure prgp_get_schd_records.
7: --smvk 25-jun-2003 Enh bug#2918094. Added column cancel_flag in the call to igs_ps_usec_occurs_pkg.
8: Create a procedure abort_scheduling to provide the feature to the user to abort the
9: scheduling in progress unit section occurrence. modified the procedure purge_schd_record
10: to delete the particular unit section occurrence in interface table with is child and parent
11: information.
11: information.
12: jbegum 07-Apr-2003 Enh Bug #2833850.
13: As part of PSP Scheduling Interface Enhancements TD added a local procedure purge_schd_record.
14: Added a new public function get_enrollment_max
15: Added the columns preferred_region_code,no_set_day_ind to the call of igs_ps_usec_occurs_pkg.update_row
16: Added the columns preferred_region_code to the calls of insert_row and update_row of
17: igs_ps_sch_int_pkg.
18: (reverse chronological order - newest change first)
19: */
34: p_n_enrollment_maximum IN NUMBER);
35:
36: PROCEDURE log_usec_occurs (p_c_trans_type IN VARCHAR2,
37: p_n_lead_instructor_id IN NUMBER,
38: p_usec_occur_rec IN igs_ps_usec_occurs_all%ROWTYPE,
39: p_c_call IN VARCHAR2);
40:
41: PROCEDURE log_messages ( p_msg_name IN VARCHAR2,
42: p_msg_val IN VARCHAR2,
70:
71: Who When What
72: sommukhe 24-Jan-2006 Bug #4926548,replaced igs_ps_unit_ofr_opt_v with igs_ps_unit_ofr_opt_all for cursor c_end_dt
73: sarakshi 12-Jan-2005 Bug#4926548, modified cursor uoo_cur to remove the two outer joins with two tables and included them in the place of open cursor .Also
74: modified the cursor usec_occur to use its base table rather than the view IGS_PS_USEC_OCCURS_V
75: sarakshi 06-Dec-2005 Bug#4863051,modified cursor pat_cur such that it excludes the inactive units for processing.
76: sarakshi 19-Sep-2005 Bug#4588504, modified cursor uoo_cur such it picks the subtitle correctly.
77: sarakshi 13-Sep-2005 Bug#4584578, removed the = condition when comparting sysdate with the teaching calendaer end date
78: ***************************************************************/
123: -- Modified the following cursor for Enh bug#2833850
124: -- Added the no_set_day_ind check in the where clause and also replaced the columns in the select statement with *.
125: -- Modified the following cursor for Enh Bug # 2918094. Removed schedule status 'PROCESSING' in the where clause.
126: CURSOR usec_occur(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
127: cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
128: SELECT *
129: FROM igs_ps_usec_occurs_all
130: WHERE uoo_id = l_uoo_id
131: AND no_set_day_ind = 'N'
125: -- Modified the following cursor for Enh Bug # 2918094. Removed schedule status 'PROCESSING' in the where clause.
126: CURSOR usec_occur(l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
127: cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
128: SELECT *
129: FROM igs_ps_usec_occurs_all
130: WHERE uoo_id = l_uoo_id
131: AND no_set_day_ind = 'N'
132: AND (unit_section_occurrence_id = cp_unit_section_occurrence_id OR cp_unit_section_occurrence_id IS NULL)
133: AND (
147:
148: -- Check whether it is required to pass the unit section information to interface table or not.
149: -- for aborting occurrences no need to pass the unit section occurrence process.
150: CURSOR c_is_req (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
151: cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
152: cp_schedule_status igs_ps_usec_occurs_all.schedule_status%TYPE) IS
153: SELECT 'x'
154: FROM igs_ps_usec_occurs_all
155: WHERE NVL(schedule_status,'NULL') = DECODE(cp_unit_section_occurrence_id,NULL,cp_schedule_status,NVL(schedule_status,'NULL'))
148: -- Check whether it is required to pass the unit section information to interface table or not.
149: -- for aborting occurrences no need to pass the unit section occurrence process.
150: CURSOR c_is_req (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
151: cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
152: cp_schedule_status igs_ps_usec_occurs_all.schedule_status%TYPE) IS
153: SELECT 'x'
154: FROM igs_ps_usec_occurs_all
155: WHERE NVL(schedule_status,'NULL') = DECODE(cp_unit_section_occurrence_id,NULL,cp_schedule_status,NVL(schedule_status,'NULL'))
156: AND uoo_id = cp_n_uoo_id
150: CURSOR c_is_req (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
151: cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
152: cp_schedule_status igs_ps_usec_occurs_all.schedule_status%TYPE) IS
153: SELECT 'x'
154: FROM igs_ps_usec_occurs_all
155: WHERE NVL(schedule_status,'NULL') = DECODE(cp_unit_section_occurrence_id,NULL,cp_schedule_status,NVL(schedule_status,'NULL'))
156: AND uoo_id = cp_n_uoo_id
157: AND no_set_day_ind = 'N'
158: AND (unit_section_occurrence_id = cp_unit_section_occurrence_id OR cp_unit_section_occurrence_id IS NULL)
183:
184:
185: CURSOR c_occur_exists (cp_uoo_id IN NUMBER) IS
186: SELECT 'X'
187: FROM igs_ps_usec_occurs_all
188: WHERE uoo_id=cp_uoo_id;
189: l_c_var VARCHAR2(1);
190:
191: CURSOR c_section_int_exists(cp_int_pat_id IN NUMBER) IS
902: sommukhe 24-Jan-2006 Bug #4926548,replaced igs_pe_person_v with hz_parties for cursor instrs
903: ***************************************************************/
904: l_int_occurs_id igs_ps_sch_int_all.int_occurs_id%TYPE;
905: -- Get the reference code and reference code type
906: CURSOR ref_cd( l_usec_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
907: SELECT reference_code,
908: reference_code_description,
909: reference_code_type,
910: reference_type_description
913:
914:
915:
916: -- Get the instructors of the usec_id
917: CURSOR instrs (l_usec_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
918: SELECT A.instructor_id,
919: B.PERSON_LAST_NAME surname,
920: B.PERSON_FIRST_NAME given_names,
921: B.PERSON_MIDDLE_NAME middle_name,
925: WHERE A.person_number = B.party_number AND
926: A.unit_section_occurrence_id = l_usec_id;
927:
928: -- Get the Facilities associated with the usec_id
929: CURSOR facilities (l_usec_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
930: SELECT facility_code,
931: facility_description
932: FROM igs_ps_uso_facility_v
933: WHERE unit_section_occurrence_id = l_usec_id;
932: FROM igs_ps_uso_facility_v
933: WHERE unit_section_occurrence_id = l_usec_id;
934:
935: -- Get the Facilities associated with the usec_id
936: CURSOR unit_facilities (l_usec_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
937: SELECT media_code,
938: media_description
939: FROM igs_ps_usec_occurs_all a,
940: igs_ps_unit_ofr_opt_all b,
935: -- Get the Facilities associated with the usec_id
936: CURSOR unit_facilities (l_usec_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
937: SELECT media_code,
938: media_description
939: FROM igs_ps_usec_occurs_all a,
940: igs_ps_unit_ofr_opt_all b,
941: igs_ps_unit_facility_v c
942: WHERE a.unit_section_occurrence_id = l_usec_id
943: AND a.uoo_id=b.uoo_id
949: BEGIN
950:
951: -- Insert Unit Section Occurs Interface Records
952: DECLARE
953: l_start_date igs_ps_usec_occurs_all.start_date%TYPE;
954: l_end_date igs_ps_usec_occurs_all.end_date%TYPE;
955: BEGIN
956: IF p_usec_occur_rec.start_date IS NULL OR p_usec_occur_rec.end_date IS NULL THEN
957: l_start_date := p_uoo_cur_rec.cal_start_dt;
950:
951: -- Insert Unit Section Occurs Interface Records
952: DECLARE
953: l_start_date igs_ps_usec_occurs_all.start_date%TYPE;
954: l_end_date igs_ps_usec_occurs_all.end_date%TYPE;
955: BEGIN
956: IF p_usec_occur_rec.start_date IS NULL OR p_usec_occur_rec.end_date IS NULL THEN
957: l_start_date := p_uoo_cur_rec.cal_start_dt;
958: l_end_date := p_uoo_cur_rec.cal_end_dt;
1219: PROCEDURE call_generic_transfer ( p_c_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
1220: p_n_version_number IN igs_ps_unit_ver.version_number%TYPE,
1221: p_c_location_cd IN igs_ps_unit_ofr_opt_all.location_cd%TYPE,
1222: p_n_unit_class IN igs_ps_unit_ofr_opt_all.unit_class%TYPE,
1223: p_n_usec_id IN igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE ) AS
1224: /**********************************************************
1225: Created By : sarakshi
1226:
1227: Date Created By : 21-May-2005
1257: l_cur_uoo_rec cur_prod_uoo%ROWTYPE;
1258:
1259: CURSOR cur_prod_uoo_occur(cp_unit_section_occurrence_id IN NUMBER) IS
1260: SELECT us.*,us.rowid
1261: FROM igs_ps_usec_occurs_all us
1262: WHERE us.unit_section_occurrence_id = cp_unit_section_occurrence_id;
1263: l_usec_occurs_rec cur_prod_uoo_occur%ROWTYPE;
1264:
1265: l_o_count NUMBER;
1538: OPEN cur_prod_uoo_occur(l_occurrence_prod_rec.unit_section_occurrence_id);
1539: FETCH cur_prod_uoo_occur INTO l_usec_occurs_rec;
1540: CLOSE cur_prod_uoo_occur;
1541:
1542: igs_ps_usec_occurs_pkg.update_row (
1543: x_rowid => l_usec_occurs_rec.ROWID,
1544: x_unit_section_occurrence_id => l_usec_occurs_rec.unit_section_occurrence_id,
1545: x_uoo_id => l_usec_occurs_rec.uoo_id,
1546: x_monday => l_usec_occurs_rec.monday,
1595: x_occurrence_identifier => l_usec_occurs_rec.occurrence_identifier,
1596: x_abort_flag => 'N'
1597: );
1598:
1599: --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;
1600:
1601: END LOOP;
1602:
1603: END LOOP;
1806: ***************************************************************/
1807: -- select the PROCSSING scheduling records for only the UOO_ID
1808: CURSOR c_get_schd_status_uoo IS
1809: SELECT 'X'
1810: FROM igs_ps_usec_occurs
1811: WHERE schedule_status='PROCESSING'
1812: AND UOO_ID=p_uoo_id;
1813: -- select the PROCSSING scheduling records for UOO_ID with UNIT_SECTION_OCCURRENCE_ID
1814: CURSOR c_get_schd_status_usec IS
1812: AND UOO_ID=p_uoo_id;
1813: -- select the PROCSSING scheduling records for UOO_ID with UNIT_SECTION_OCCURRENCE_ID
1814: CURSOR c_get_schd_status_usec IS
1815: SELECT 'X'
1816: FROM igs_ps_usec_occurs
1817: WHERE schedule_status='PROCESSING'
1818: AND UOO_ID=p_uoo_id
1819: AND UNIT_SECTION_OCCURRENCE_ID=p_usec_id;
1820: l_dummy VARCHAR2(1);
1953:
1954: l_org_id NUMBER(15);
1955: lv_enrol_max c_maxenrl_modified_chk%ROWTYPE;
1956:
1957: -- Local procedure to update igs_ps_usec_occurs table with a given schedule status for a unit section id
1958: -- both of which are passed as parameters
1959:
1960:
1961: PROCEDURE upd_usec_occurs_schd_status ( p_uoo_id IN NUMBER, schd_stat IN VARCHAR2 ) AS
1960:
1961: PROCEDURE upd_usec_occurs_schd_status ( p_uoo_id IN NUMBER, schd_stat IN VARCHAR2 ) AS
1962:
1963: --Bug # 2831065. Update the USO which are not in schedule status processing and input schedule status schd_stat.
1964: CURSOR c_usec_occurs ( p_uoo_id IN NUMBER, cp_c_schd_stat IN igs_ps_usec_occurs.schedule_status%TYPE) IS
1965: SELECT ROWID, puo.*
1966: FROM igs_ps_usec_occurs puo
1967: WHERE uoo_id = p_uoo_id
1968: AND (schedule_status IS NULL OR schedule_status <> cp_c_schd_stat)
1962:
1963: --Bug # 2831065. Update the USO which are not in schedule status processing and input schedule status schd_stat.
1964: CURSOR c_usec_occurs ( p_uoo_id IN NUMBER, cp_c_schd_stat IN igs_ps_usec_occurs.schedule_status%TYPE) IS
1965: SELECT ROWID, puo.*
1966: FROM igs_ps_usec_occurs puo
1967: WHERE uoo_id = p_uoo_id
1968: AND (schedule_status IS NULL OR schedule_status <> cp_c_schd_stat)
1969: AND NO_SET_DAY_IND ='N'
1970: FOR UPDATE NOWAIT;
1968: AND (schedule_status IS NULL OR schedule_status <> cp_c_schd_stat)
1969: AND NO_SET_DAY_IND ='N'
1970: FOR UPDATE NOWAIT;
1971:
1972: l_c_cancel igs_ps_usec_occurs_all.cancel_flag%TYPE;
1973: l_c_schedule_status igs_ps_usec_occurs_all.schedule_status%TYPE;
1974:
1975: BEGIN
1976:
1969: AND NO_SET_DAY_IND ='N'
1970: FOR UPDATE NOWAIT;
1971:
1972: l_c_cancel igs_ps_usec_occurs_all.cancel_flag%TYPE;
1973: l_c_schedule_status igs_ps_usec_occurs_all.schedule_status%TYPE;
1974:
1975: BEGIN
1976:
1977: FOR c_usec_occurs_rec IN c_usec_occurs(p_uoo_id, schd_stat) LOOP
1989: END IF;
1990:
1991: 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
1992:
1993: igs_ps_usec_occurs_pkg.update_row (
1994: x_rowid => c_usec_occurs_rec.ROWID,
1995: x_unit_section_occurrence_id => c_usec_occurs_rec.unit_section_occurrence_id,
1996: x_uoo_id => c_usec_occurs_rec.uoo_id,
1997: x_monday => c_usec_occurs_rec.monday,
2591: (reverse chronological order - newest change first)
2592: ***************************************************************/
2593:
2594: l_originator igs_ps_sch_hdr_int.originator%TYPE;
2595: l_status igs_ps_usec_occurs.schedule_status%TYPE;
2596: l_unit_section_occurrence_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2592: ***************************************************************/
2593:
2594: l_originator igs_ps_sch_hdr_int.originator%TYPE;
2595: l_status igs_ps_usec_occurs.schedule_status%TYPE;
2596: l_unit_section_occurrence_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2593:
2594: l_originator igs_ps_sch_hdr_int.originator%TYPE;
2595: l_status igs_ps_usec_occurs.schedule_status%TYPE;
2596: l_unit_section_occurrence_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2594: l_originator igs_ps_sch_hdr_int.originator%TYPE;
2595: l_status igs_ps_usec_occurs.schedule_status%TYPE;
2596: l_unit_section_occurrence_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2596: l_unit_section_occurrence_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2597: l_schedule_status igs_ps_usec_occurs.schedule_status%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2598: l_inter_error_text igs_ps_usec_occurs.error_text%TYPE;
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2599: l_transaction_type igs_ps_sch_int.transaction_type%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2600: l_building_code igs_ps_usec_occurs.building_code%TYPE;
2601: l_room_code igs_ps_usec_occurs.room_code%TYPE;
2602: l_start_time igs_ps_usec_occurs.start_time%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2603: l_end_time igs_ps_usec_occurs.end_time%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2604: l_error_text igs_ps_usec_occurs.error_text%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2605: l_trans_id igs_ps_sch_usec_int_all.transaction_id%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2613: l_sunday igs_ps_usec_occurs.sunday%TYPE;
2606: --Added By Bayadav as a part of Nov,2001 SI Build
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2613: l_sunday igs_ps_usec_occurs.sunday%TYPE;
2614: l_set_scheduled_status igs_ps_sch_int_all.schedule_status%TYPE;
2607: l_monday igs_ps_usec_occurs.monday%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2613: l_sunday igs_ps_usec_occurs.sunday%TYPE;
2614: l_set_scheduled_status igs_ps_sch_int_all.schedule_status%TYPE;
2615: l_set_transaction_type igs_ps_sch_int_all.transaction_type%TYPE;
2608: l_tuesday igs_ps_usec_occurs.tuesday%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2613: l_sunday igs_ps_usec_occurs.sunday%TYPE;
2614: l_set_scheduled_status igs_ps_sch_int_all.schedule_status%TYPE;
2615: l_set_transaction_type igs_ps_sch_int_all.transaction_type%TYPE;
2616: l_tba_status igs_ps_sch_int_all.tba_status%TYPE;
2609: l_wednesday igs_ps_usec_occurs.wednesday%TYPE;
2610: l_thursday igs_ps_usec_occurs.thursday%TYPE;
2611: l_friday igs_ps_usec_occurs.friday%TYPE;
2612: l_saturday igs_ps_usec_occurs.saturday%TYPE;
2613: l_sunday igs_ps_usec_occurs.sunday%TYPE;
2614: l_set_scheduled_status igs_ps_sch_int_all.schedule_status%TYPE;
2615: l_set_transaction_type igs_ps_sch_int_all.transaction_type%TYPE;
2616: l_tba_status igs_ps_sch_int_all.tba_status%TYPE;
2617: l_d_uso_start_date igs_ps_sch_int_all.unit_section_start_date%TYPE; -- Added as a part of Enh Bug #2833850
2633: ORDER BY sui.calendar_type, sui.sequence_number, sui.uoo_id;
2634:
2635: CURSOR c_get_records_inter(cp_int_usec_id IN NUMBER) IS
2636: SELECT ipsi.rowid,ipsi.*
2637: FROM igs_ps_sch_int_all ipsi, igs_ps_usec_occurs_all uso
2638: WHERE ipsi.transaction_type IN ('REQUEST','UPDATE','CANCEL') AND ipsi.schedule_status IN ('OK','ERROR')
2639: AND ipsi.unit_section_occurrence_id=uso.unit_section_occurrence_id
2640: AND uso.schedule_status <> 'CANCELLED'
2641: AND uso.abort_flag = 'N'
2649: WHERE transaction_id=p_transaction_id;
2650:
2651: CURSOR c_ipsuo(p_usec_id NUMBER) IS
2652: SELECT *
2653: FROM igs_ps_usec_occurs_all ipsuo
2654: WHERE unit_section_occurrence_id=p_usec_id
2655: FOR UPDATE NOWAIT;
2656:
2657:
2705:
2706: l_check_ovrd check_ovrd%ROWTYPE;
2707:
2708:
2709: PROCEDURE update_info( p_n_prd_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
2710: p_n_int_uso_id igs_ps_sch_int_all.unit_section_occurrence_id%TYPE,
2711: p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2707:
2708:
2709: PROCEDURE update_info( p_n_prd_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
2710: p_n_int_uso_id igs_ps_sch_int_all.unit_section_occurrence_id%TYPE,
2711: p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2709: PROCEDURE update_info( p_n_prd_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE,
2710: p_n_int_uso_id igs_ps_sch_int_all.unit_section_occurrence_id%TYPE,
2711: p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2710: p_n_int_uso_id igs_ps_sch_int_all.unit_section_occurrence_id%TYPE,
2711: p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2711: p_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2712: p_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2713: p_n_bldg_cd igs_ps_usec_occurs_all.building_code%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2714: p_n_room_cd igs_ps_usec_occurs_all.room_code%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2715: p_d_uso_start_dt igs_ps_usec_occurs_all.start_date%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2716: p_d_uso_end_dt igs_ps_usec_occurs_all.end_date%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2717: p_d_uso_start_tm igs_ps_usec_occurs_all.start_time%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2718: p_d_uso_end_tm igs_ps_usec_occurs_all.end_time%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2719: p_c_sunday igs_ps_usec_occurs_all.sunday%TYPE,
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2720: p_c_monday igs_ps_usec_occurs_all.monday%TYPE,
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2721: p_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE,
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2729: CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2722: p_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE,
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2729: CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2730: SELECT rowid,uso.*
2723: p_c_thursday igs_ps_usec_occurs_all.thursday%TYPE,
2724: p_c_friday igs_ps_usec_occurs_all.friday%TYPE,
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2729: CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2730: SELECT rowid,uso.*
2731: FROM igs_ps_usec_occurs_all uso
2725: p_c_saturday igs_ps_usec_occurs_all.saturday%TYPE,
2726: p_c_tba_uso igs_ps_usec_occurs_all.to_be_announced%TYPE,
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2729: CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2730: SELECT rowid,uso.*
2731: FROM igs_ps_usec_occurs_all uso
2732: WHERE uso.unit_section_occurrence_id=cp_n_uso_id;
2733:
2727: p_c_err_text igs_ps_usec_occurs_all.error_text%TYPE
2728: ) AS
2729: CURSOR c_prd_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2730: SELECT rowid,uso.*
2731: FROM igs_ps_usec_occurs_all uso
2732: WHERE uso.unit_section_occurrence_id=cp_n_uso_id;
2733:
2734: CURSOR c_int_uso (cp_n_uso_id igs_ps_sch_int_all.int_occurs_id%TYPE) IS
2735: SELECT uso.rowid, uso.*, usec.int_pat_id
2736: FROM igs_ps_sch_int_all uso, igs_ps_sch_usec_int_all usec
2737: WHERE uso.int_usec_id = usec.int_usec_id
2738: AND uso.int_occurs_id = cp_n_uso_id;
2739:
2740: CURSOR c_print_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2741: SELECT *
2742: FROM igs_ps_usec_occurs_all
2743: WHERE unit_section_occurrence_id=cp_n_uso_id;
2744:
2738: AND uso.int_occurs_id = cp_n_uso_id;
2739:
2740: CURSOR c_print_uso(cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2741: SELECT *
2742: FROM igs_ps_usec_occurs_all
2743: WHERE unit_section_occurrence_id=cp_n_uso_id;
2744:
2745: -- Bug #3060089. Cursor to check the unit section is non standard unit section or not.
2746: CURSOR c_nstd_us (cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2742: FROM igs_ps_usec_occurs_all
2743: WHERE unit_section_occurrence_id=cp_n_uso_id;
2744:
2745: -- Bug #3060089. Cursor to check the unit section is non standard unit section or not.
2746: CURSOR c_nstd_us (cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2747: SELECT 'x'
2748: FROM igs_ps_usec_occurs_all uso,
2749: igs_ps_unit_ofr_opt_all uoo
2750: WHERE uoo.non_std_usec_ind = 'Y'
2744:
2745: -- Bug #3060089. Cursor to check the unit section is non standard unit section or not.
2746: CURSOR c_nstd_us (cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2747: SELECT 'x'
2748: FROM igs_ps_usec_occurs_all uso,
2749: igs_ps_unit_ofr_opt_all uoo
2750: WHERE uoo.non_std_usec_ind = 'Y'
2751: AND uoo.uoo_id = uso.uoo_id
2752: AND uso.unit_section_occurrence_id=cp_n_uso_id;
2753:
2754: rec_prd_uso c_prd_uso%ROWTYPE;
2755: rec_int_uso c_int_uso%ROWTYPE;
2756: rec_print_uso c_print_uso%ROWTYPE;
2757: l_c_prf_reg igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2754: rec_prd_uso c_prd_uso%ROWTYPE;
2755: rec_int_uso c_int_uso%ROWTYPE;
2756: rec_print_uso c_print_uso%ROWTYPE;
2757: l_c_prf_reg igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2755: rec_int_uso c_int_uso%ROWTYPE;
2756: rec_print_uso c_print_uso%ROWTYPE;
2757: l_c_prf_reg igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2763: l_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
2756: rec_print_uso c_print_uso%ROWTYPE;
2757: l_c_prf_reg igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2763: l_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
2764: l_c_err_text igs_ps_usec_occurs_all.error_text%TYPE;
2757: l_c_prf_reg igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2763: l_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
2764: l_c_err_text igs_ps_usec_occurs_all.error_text%TYPE;
2765: l_c_exists VARCHAR2(1);
2758: l_n_prf_bld igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2759: l_n_prf_rom igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2763: l_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
2764: l_c_err_text igs_ps_usec_occurs_all.error_text%TYPE;
2765: l_c_exists VARCHAR2(1);
2766:
2760: l_n_ded_bld igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2761: l_n_ded_rom igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2762: l_c_sch_status igs_ps_usec_occurs_all.schedule_status%TYPE;
2763: l_c_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
2764: l_c_err_text igs_ps_usec_occurs_all.error_text%TYPE;
2765: l_c_exists VARCHAR2(1);
2766:
2767: /*CURSOR check_ovrd IS
2768: SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag
2768: SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag
2769: FROM igs_ps_sch_ocr_cfig;
2770:
2771: l_check_ovrd check_ovrd%ROWTYPE;
2772: l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2769: FROM igs_ps_sch_ocr_cfig;
2770:
2771: l_check_ovrd check_ovrd%ROWTYPE;
2772: l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2770:
2771: l_check_ovrd check_ovrd%ROWTYPE;
2772: l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2771: l_check_ovrd check_ovrd%ROWTYPE;
2772: l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2772: l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2773: l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2774: l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2782: l_sch_room igs_ps_usec_occurs_all.room_code%TYPE;*/
2775: l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2782: l_sch_room igs_ps_usec_occurs_all.room_code%TYPE;*/
2783: l_import_done VARCHAR2(1);
2776: l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2782: l_sch_room igs_ps_usec_occurs_all.room_code%TYPE;*/
2783: l_import_done VARCHAR2(1);
2784:
2777: l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2782: l_sch_room igs_ps_usec_occurs_all.room_code%TYPE;*/
2783: l_import_done VARCHAR2(1);
2784:
2785: CURSOR cur_section_import(cp_int_usec_id IN NUMBER) IS
2778: l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2779: l_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2780: l_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2781: l_sch_bld igs_ps_usec_occurs_all.building_code%TYPE;
2782: l_sch_room igs_ps_usec_occurs_all.room_code%TYPE;*/
2783: l_import_done VARCHAR2(1);
2784:
2785: CURSOR cur_section_import(cp_int_usec_id IN NUMBER) IS
2786: SELECT 'X'
2881: END IF;
2882: CLOSE check_ovrd;*/
2883:
2884: -- Update production table with scheduling details(i.e building/room code and schedule status)
2885: igs_ps_usec_occurs_pkg.update_row
2886: (
2887: X_Mode => 'R',
2888: X_RowId => rec_prd_uso.rowid ,
2889: X_unit_section_occurrence_id => rec_prd_uso.unit_section_occurrence_id,
2988: l_c_sch_status := 'ERROR' ;
2989: l_c_trans_type := 'INCOMPLETE';
2990: l_c_err_text:=fnd_message.get || sqlerrm;
2991: -- Update production table with exception error(i.e. error text and schedule status)
2992: igs_ps_usec_occurs_pkg.update_row
2993: (
2994: X_Mode => 'R',
2995: X_RowId => rec_prd_uso.rowid ,
2996: X_unit_section_occurrence_id => rec_prd_uso.unit_section_occurrence_id,
3162: l_sunday := fetch_records_inter.sunday;
3163: l_d_uso_start_date := fetch_records_inter.uso_start_date;
3164: l_d_uso_end_date := fetch_records_inter.uso_end_date;
3165:
3166: -- Fetching records from production table(igs_ps_usec_occurs)
3167:
3168: OPEN c_ipsuo(fetch_records_inter.unit_section_occurrence_id);
3169: FETCH c_ipsuo INTO l_ipsuo;
3170: IF (c_ipsuo%NOTFOUND) THEN
3557: CURSOR c_int_uso_ss (cp_sequence_number igs_ps_sch_int_all.sequence_number%TYPE,
3558: cp_cal_type igs_ps_sch_int_all.calendar_type%type) IS
3559: SELECT a.ROWID intrid,a.*,b.ROWID prodrid,
3560: c.unit_cd UNIT_CODE,c.version_number UNIT_VERSION_NUMBER,c.location_cd LOCATION_CODE,c.unit_class UNIT_CLS
3561: FROM igs_ps_sch_int_all a,Igs_ps_usec_occurs_all b, igs_ps_sch_usec_int_all c
3562: WHERE a.unit_section_occurrence_id=b.unit_section_occurrence_id
3563: AND a.int_usec_id = c.int_usec_id
3564: AND c.calendar_type=cp_cal_type
3565: AND c.sequence_number =cp_sequence_number
3609: AND a.abort_flag = 'N';
3610:
3611: CURSOR c_int_uso_prod (cp_rowid ROWID) IS
3612: SELECT b.ROWID prodrid,b.*
3613: FROM igs_ps_usec_occurs_all b
3614: WHERE rowid = cp_rowid;
3615:
3616: c_int_uso_prod_rec c_int_uso_prod%ROWTYPE;
3617:
3709: OPEN c_int_uso_prod(c_int_uso_ss_rec.prodrid);
3710: FETCH c_int_uso_prod INTO c_int_uso_prod_rec;
3711:
3712:
3713: igs_ps_usec_occurs_pkg.update_row (
3714: X_RowId => c_int_uso_prod_rec.prodrid ,
3715: X_unit_section_occurrence_id => c_int_uso_prod_rec.unit_section_occurrence_id,
3716: X_uoo_id => c_int_uso_prod_rec.uoo_id,
3717: X_monday => c_int_uso_prod_rec.monday,
4364: END log_usec_details;
4365:
4366: PROCEDURE log_usec_occurs (p_c_trans_type IN VARCHAR2,
4367: p_n_lead_instructor_id IN NUMBER,
4368: p_usec_occur_rec IN igs_ps_usec_occurs_all%ROWTYPE,
4369: p_c_call IN VARCHAR2) AS
4370: /**********************************************************************
4371: Created By : jbegum
4372:
4556: (reverse chronological order - newest change first)
4557: ************************************************************************/
4558: CURSOR c_occurs IS
4559: SELECT uso.*,uso.ROWID
4560: FROM igs_ps_usec_occurs_all uso
4561: WHERE unit_section_occurrence_id=p_unit_section_occurrence_id;
4562: l_usec_occurs_rec c_occurs%ROWTYPE;
4563:
4564: BEGIN
4565: OPEN c_occurs;
4566: FETCH c_occurs INTO l_usec_occurs_rec;
4567: CLOSE c_occurs;
4568:
4569: igs_ps_usec_occurs_pkg.update_row (
4570: x_rowid => l_usec_occurs_rec.rowid,
4571: x_unit_section_occurrence_id => l_usec_occurs_rec.unit_section_occurrence_id,
4572: x_uoo_id => l_usec_occurs_rec.uoo_id,
4573: x_monday => l_usec_occurs_rec.monday,