[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_USEC_SCHEDULE
Source
1 PACKAGE BODY igs_ps_usec_schedule AS
2 /* $Header: IGSPS77B.pls 120.18 2006/05/22 10:06:18 sommukhe ship $ */
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.
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 */
20
21
22 -- Forward declaration of local procedures
23
24 PROCEDURE purge_schd_record( p_c_cal_type IN VARCHAR2,
25 p_n_seq_num IN NUMBER);
26
27 PROCEDURE log_teach_cal (p_c_cal_type IN VARCHAR2,
28 p_n_ci_sequence_number IN NUMBER);
29
30 PROCEDURE log_usec_details (p_c_unit_cd IN VARCHAR2,
31 p_n_version_number IN NUMBER,
32 p_c_location_description IN VARCHAR2,
33 p_c_unit_class IN VARCHAR2,
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,
43 p_val IN NUMBER
44 ) ;
45 FUNCTION get_alternate_code (p_c_cal_type IN VARCHAR2,
46 p_n_seq_num IN NUMBER)RETURN VARCHAR2;
47 g_n_user_id igs_ps_unit_ver_all.created_by%TYPE := NVL(fnd_global.user_id,-1); -- Stores the User Id
48 g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
49
50
51 PROCEDURE prgp_init_prs_sched(
52 errbuf OUT NOCOPY VARCHAR2,
53 retcode OUT NOCOPY NUMBER,
54 p_teach_prd IN VARCHAR2,
55 p_uoo_id IN NUMBER,
56 p_usec_id IN NUMBER,
57 p_sch_type IN VARCHAR2 ,
58 p_org_id IN NUMBER)
59 AS
60 /**********************************************************
61 Created By : kmunuswa
62
63 Date Created By : 29-AUG-2000
64
65 Purpose : For scheduling the Unit Section occurrences
66
67 Know limitations, enhancements or remarks
68
69 Change History
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 ***************************************************************/
79
80 -- All active and inactive unit sections
81 -- Added location_description in the following cursor as part of enh bug#2833850
82 CURSOR uoo_cur( l_cal_type igs_ca_inst.cal_type%TYPE,
83 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
84 cp_unit_cd VARCHAR2,
85 cp_version_number IN NUMBER,
86 cp_location_cd IN VARCHAR2,
87 cp_unit_class IN VARCHAR2) IS
88 SELECT uoo.uoo_id,
89 uoo.unit_cd,
90 uoo.version_number,
91 uv.title title,
92 uoo.location_cd,
93 loc.description location_description,
94 uoo.unit_class,
95 uoo.unit_section_start_date,
96 uoo.unit_section_end_date,
97 uoo.enrollment_actual,
98 uoo.unit_section_status,
99 ci.start_dt cal_start_dt,
100 ci.end_dt cal_end_dt,
101 uoo.call_number,
102 ci.alternate_code teaching_cal_alternate_code,
103 NULL subtitle,
104 uv.subtitle_id,
105 NVL(uoo.owner_org_unit_cd, uv.owner_org_unit_cd) owner_org_unit_cd
106 FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv,igs_ca_inst_all ci, igs_ad_location_all loc
107 WHERE uoo.cal_type = l_cal_type
108 AND uoo.ci_sequence_number = l_ci_sequence_number
109 AND uoo.unit_cd=cp_unit_cd
110 AND uoo.version_number=cp_version_number
111 AND (uoo.location_cd=cp_location_cd OR cp_location_cd IS NULL)
112 AND (uoo.unit_class=cp_unit_class OR cp_unit_class IS NULL)
113 AND uoo.unit_cd = uv.unit_cd
114 AND uoo.version_number = uv.version_number
115 AND uoo.unit_section_status <> 'NOT_OFFERED'
116 AND uoo.cal_type = ci.cal_type
117 AND uoo.ci_sequence_number = ci.sequence_number
118 AND uoo.location_cd = loc.location_cd;
119
120
121
122 -- Get the Usec Occurrence data for a given uoo_id
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'
132 AND (unit_section_occurrence_id = cp_unit_section_occurrence_id OR cp_unit_section_occurrence_id IS NULL)
133 AND (
134 schedule_status IS NULL
135 OR
136 (schedule_status NOT IN('SCHEDULED'))
137 );
138
139
140 -- Get the calendar end date for the particular unit section for Bug # 2383553
141 CURSOR c_end_dt (cp_uoo_id IGS_PS_UNIT_OFR_OPT_V.UOO_ID%TYPE) IS
142 SELECT ci.end_dt cal_end_dt
143 FROM igs_ps_unit_ofr_opt_all uoo, igs_ca_inst_all ci
144 WHERE UOO_ID =cp_uoo_id
145 AND uoo.cal_type = ci.cal_type
146 AND uoo.ci_sequence_number = ci.sequence_number;
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'))
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)
159 AND ROWNUM <2 ;
160
161 ---
162 CURSOR c_usec_param(cp_uoo_id IN NUMBER) IS
163 SELECT unit_cd,version_number,cal_type,ci_sequence_number,unit_class,location_cd
164 FROM igs_ps_unit_ofr_opt_all
165 WHERE uoo_id=cp_uoo_id;
166 l_usec_param c_usec_param%ROWTYPE;
167
168 CURSOR pat_cur(cp_cal_type IN VARCHAR2, cp_ci_sequence_number IN NUMBER,
169 cp_unit_cd IN VARCHAR2, cp_version_number IN NUMBER) IS
170 SELECT us.*,b.alternate_code ,b.start_dt,b.end_dt,c.unit_status,c.enrollment_expected,c.enrollment_maximum,c.override_enrollment_max
171 FROM igs_ps_unit_ofr_pat us, igs_ca_inst_all b,igs_ps_unit_ver c, igs_ps_unit_stat d
172 WHERE us.cal_type=cp_cal_type
173 AND us.ci_sequence_number=cp_ci_sequence_number
174 AND (us.unit_cd=cp_unit_cd OR cp_unit_cd IS NULL)
175 AND (us.version_number=cp_version_number OR cp_version_number IS NULL)
176 AND us.cal_type=b.cal_type
177 AND us.ci_sequence_number=b.sequence_number
178 AND us.unit_cd=c.unit_cd
179 AND us.version_number=c.version_number
180 AND c.unit_status=d.unit_status
181 AND d.s_unit_status <> 'INACTIVE';
182 l_uoo_cur uoo_cur%ROWTYPE;
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
192 SELECT 'X'
193 FROM igs_ps_sch_usec_int_all
194 WHERE int_pat_id = cp_int_pat_id;
195
196 CURSOR c_pattern_int_exists(cp_transaction_id IN NUMBER) IS
197 SELECT 'X'
198 FROM igs_ps_sch_pat_int
199 WHERE transaction_id = cp_transaction_id;
200
201 CURSOR c_pattern_prod(cp_transaction_id IN NUMBER) IS
202 SELECT *
203 FROM igs_ps_sch_pat_int
204 WHERE transaction_id=cp_transaction_id;
205
206 CURSOR c_section(cp_int_pat_id IN NUMBER) IS
207 SELECT *
208 FROM igs_ps_sch_usec_int_all
209 WHERE int_pat_id=cp_int_pat_id;
210
211 CURSOR c_occurrence(cp_int_usec_id IN NUMBER) IS
212 SELECT *
213 FROM igs_ps_sch_int_all
214 WHERE int_usec_id=cp_int_usec_id;
215
216 l_int_pat_id NUMBER;
217 l_valid_occur BOOLEAN;
218 l_unit_status igs_ps_unit_ver_all.unit_status%TYPE;
219 ---
220
221 l_trans_id igs_ps_sch_hdr_int.transaction_id%TYPE;
222 l_request_id igs_ps_sch_hdr_int.request_id%TYPE;
223 l_program_id igs_ps_sch_hdr_int.program_id%TYPE;
224 l_program_application_id igs_ps_sch_hdr_int.program_application_id%TYPE;
225 l_derd_sch_type VARCHAR2(13); -- Changed as varchar2(13) from igs_ps_sch_int_all.transaction_type%TYPE w.r.t. Bug # 2383553
226 l_int_sch_status igs_ps_sch_int_all.transaction_type%TYPE;
227 l_cal_type igs_ca_inst.cal_type%TYPE;
228 l_enrollment_maximum igs_ps_usec_lim_wlst.enrollment_maximum%TYPE;
229 l_enrollment_expected igs_ps_usec_lim_wlst.enrollment_expected%TYPE;
230 l_override_enrollment_max igs_ps_usec_lim_wlst.override_enrollment_max%TYPE;
231 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
232 l_start_date igs_ca_inst.start_dt%TYPE;
233 l_end_date igs_ca_inst.end_dt%TYPE;
234 l_trans_type igs_ps_sch_int_all.transaction_type%TYPE;
235 l_int_usec_id igs_ps_sch_usec_int_all.int_usec_id%TYPE;
236 l_max_enr_meet_grp igs_ps_uso_clas_meet_v.enrollment_maximum%TYPE;
237 l_lead_instructor_id igs_ps_usec_tch_resp.instructor_id%TYPE;
238 l_surname igs_pe_person.surname%TYPE ;
239 l_given_names igs_pe_person.given_names%TYPE ;
240 l_middle_name igs_pe_person.middle_name%TYPE;
241 l_data_found BOOLEAN := FALSE; -- Added as a part of Bug # 2383553
242 usec_occur_rec usec_occur%ROWTYPE;
243 rec_end_dt c_end_dt%ROWTYPE;
244 l_cal BOOLEAN := TRUE; -- Added as a part of Bug # 2833850
245 l_usec BOOLEAN := TRUE; -- Added as a part of Bug # 2833850
246 rec_is_req c_is_req%ROWTYPE;
247
248
249 PROCEDURE create_header IS
250 -- create a header record for the process.
251 BEGIN
252 INSERT INTO igs_ps_sch_hdr_int_all (
253 transaction_id ,
254 originator ,
255 request_date ,
256 org_id ,
257 created_by ,
258 creation_date ,
259 last_updated_by ,
260 last_update_date ,
261 last_update_login)
262 VALUES (
263 IGS_PS_SCH_HDR_INT_S.NEXTVAL,
264 'INTERNAL',
265 SYSDATE,
266 p_org_id,
267 g_n_user_id,
268 SYSDATE,
269 g_n_user_id,
270 SYSDATE,
271 g_n_login_id ) RETURNING transaction_id INTO l_trans_id;
272
273 END create_header;
274
275 -- Procedure to get the enrollment limits (Enrollment Expected, Enrollment Maximum and Override Maximum) for the unit section.
276 -- Uses inheritance logic to get enrollment limits.
277 -- (i.e gets enrollment limits from unit section[IGS_PS_USEC_LIM_WLST] level if defined (overriden) otherwise from unit level [IGS_PS_UNIT_VER_ALL].)
278 PROCEDURE get_enrollment_lmts(p_n_uoo_id IN NUMBER) AS
279
280 -- gets the enrollment limits defined at unit section level.
281 CURSOR usec_lmts(cp_n_uoo_id IN NUMBER) IS
282 SELECT usec.enrollment_expected,
283 NVL(usec.enrollment_maximum,999999),
284 NVL(usec.override_enrollment_max,999999)
285 FROM igs_ps_usec_lim_wlst usec
286 WHERE usec.uoo_id = cp_n_uoo_id;
287
288 -- gets the enrollment limits defined at unit level.
289 CURSOR unit_lmts(cp_n_uoo_id IN NUMBER) IS
290 SELECT unit.enrollment_expected,
291 NVL(unit.enrollment_maximum, 999999),
292 NVL(unit.override_enrollment_max,999999)
293 FROM IGS_PS_UNIT_VER_ALL unit,
294 IGS_PS_UNIT_OFR_OPT_ALL usec
295 WHERE unit.unit_cd = usec.unit_cd
296 AND unit.version_number = usec.version_number
297 AND usec.uoo_id = cp_n_uoo_id;
298
299 BEGIN
300 l_enrollment_expected := NULL;
301 l_enrollment_maximum := NULL;
302 l_override_enrollment_max := NULL;
303 OPEN usec_lmts(p_n_uoo_id);
304 FETCH usec_lmts INTO l_enrollment_expected, l_enrollment_maximum, l_override_enrollment_max;
305 IF usec_lmts%NOTFOUND THEN
306 -- if limits are not overriden at unit section level then get it from unit level.
307 OPEN unit_lmts (p_n_uoo_id);
308 FETCH unit_lmts INTO l_enrollment_expected, l_enrollment_maximum, l_override_enrollment_max;
309 CLOSE unit_lmts;
310 END IF;
311 CLOSE usec_lmts;
312
313 END get_enrollment_lmts;
314
315 -- function to get lead instructor information such as instructor identifier
316 -- given name (first name), middle name and last name.
317 -- returns false if it could not find the lead instructor information for the
318 -- identifier existing in the unit section teaching responsibility.
319 -- returns true, if the unit section does not have lead instructor or
320 -- if it finds the lead instructor information successfully.
321
322 FUNCTION get_instructor_info (p_n_uoo_id IN NUMBER) RETURN BOOLEAN AS
323 -- Get the lead instructor identifier
324 CURSOR c_lead_instructor (cp_n_uoo_id IN NUMBER) IS
325 SELECT instructor_id
326 FROM igs_ps_usec_tch_resp
327 WHERE uoo_id = cp_n_uoo_id
328 AND lead_instructor_flag = 'Y' ;
329
330 -- Get the given name (first name), middle name and last name for the given Instructor Identifier
331 CURSOR c_get_names(cp_n_ins_id IN NUMBER) IS
332 SELECT first_name,
333 middle_name,
334 last_name
335 FROM igs_pe_person_base_v
336 WHERE person_id = cp_n_ins_id;
337 l_ret_type BOOLEAN;
338 BEGIN
339 l_lead_instructor_id := NULL;
340 l_given_names := NULL;
341 l_middle_name := NULL;
342 l_surname := NULL;
343 l_ret_type := TRUE;
344 OPEN c_lead_instructor(p_n_uoo_id);
345 FETCH c_lead_instructor INTO l_lead_instructor_id;
346 IF c_lead_instructor%FOUND THEN
347 OPEN c_get_names( l_lead_instructor_id );
348 FETCH c_get_names INTO l_given_names,l_middle_name,l_surname;
349 IF c_get_names%NOTFOUND THEN
350 l_ret_type := FALSE;
351 END IF;
352 CLOSE c_get_names;
353 END IF;
354 CLOSE c_lead_instructor;
355 return l_ret_type;
356 END get_instructor_info;
357
358 ---
359 PROCEDURE transfer_patterns(p_pat_cur_rec IN pat_cur%ROWTYPE,
360 p_unit_status OUT NOCOPY VARCHAR2,
361 p_int_pat_id OUT NOCOPY NUMBER) AS
362 /**********************************************************
363 Created By : sarakshi
364
365 Date Created By : 21-May-2005
366
367 Purpose : Transfer Data to pattern interface table and its childs location and facilities
368
369 Know limitations, enhancements or remarks
370
371 Change History
372
373 Who When What
374 ***************************************************************/
375 l_u_enrollment_expected igs_ps_unit_ver_all.enrollment_expected%TYPE ;
376 l_u_enrollment_maximum igs_ps_unit_ver_all.enrollment_maximum%TYPE ;
377 l_u_override_enrollment_max igs_ps_unit_ver_all.override_enrollment_max%TYPE ;
378
379
380 CURSOR c_section_exists(cp_cal_type IN igs_ps_unit_ofr_opt_all.cal_type%TYPE,
381 cp_ci_sequence_number IN igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE,
382 cp_unit_cd IN igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
383 cp_version_number IN igs_ps_unit_ofr_opt_all.version_number%TYPE) IS
384 SELECT 'X'
385 FROM igs_ps_unit_ofr_opt_all us
386 WHERE us.cal_type=cp_cal_type
387 AND us.ci_sequence_number=cp_ci_sequence_number
388 AND us.unit_cd=cp_unit_cd
389 AND us.version_number=cp_version_number;
390
391 CURSOR c_loc(cp_unit_cd IN VARCHAR2,
392 cp_version_number IN NUMBER) IS
393 SELECT *
394 FROM igs_ps_unit_location_v
395 WHERE unit_code=cp_unit_cd
396 AND unit_version_number=cp_version_number;
397
398 CURSOR c_fac(cp_unit_cd IN VARCHAR2,
399 cp_version_number IN NUMBER) IS
400 SELECT *
401 FROM igs_ps_unit_facility_v
402 WHERE unit_code=cp_unit_cd
403 AND unit_version_number=cp_version_number;
404
405 BEGIN
406 -- Check if section record exist in production for the pattern
407 OPEN c_section_exists(l_cal_type,l_ci_sequence_number,p_pat_cur_rec.unit_cd,p_pat_cur_rec.version_number);
408 FETCH c_section_exists INTO l_c_var;
409 IF c_section_exists%FOUND THEN
410 l_u_enrollment_expected:= NULL;
411 l_u_enrollment_maximum:= NULL;
412 l_u_override_enrollment_max:= NULL;
413 p_unit_status:= NULL;
414 ELSE
415 l_u_enrollment_expected:= p_pat_cur_rec.enrollment_expected;
416 l_u_enrollment_maximum:= p_pat_cur_rec.enrollment_maximum;
417 l_u_override_enrollment_max:= p_pat_cur_rec.override_enrollment_max;
418 p_unit_status:= p_pat_cur_rec.unit_status;
419 END IF;
420 CLOSE c_section_exists;
421
422 --Insert into pattern interface table
423 INSERT INTO IGS_PS_SCH_PAT_INT
424 (int_pat_id ,
425 transaction_id ,
426 calendar_type ,
427 sequence_number ,
428 teaching_cal_alternate_code ,
429 start_date ,
430 end_date ,
431 unit_cd ,
432 version_number ,
433 enrollment_expected ,
434 enrollment_maximum ,
435 override_enrollment_maximum ,
436 unit_status ,
437 abort_flag ,
438 import_done_flag ,
439 created_by ,
440 creation_date ,
441 last_updated_by ,
442 last_update_date ,
443 last_update_login)
444 VALUES(
445 IGS_PS_SCH_PAT_INT_S.NEXTVAL,
446 l_trans_id,
447 l_cal_type,
448 l_ci_sequence_number,
449 p_pat_cur_rec.alternate_code,
450 p_pat_cur_rec.start_dt,
451 p_pat_cur_rec.end_dt,
452 p_pat_cur_rec.unit_cd,
453 p_pat_cur_rec.version_number,
454 l_u_enrollment_expected,
455 l_u_enrollment_maximum,
456 l_u_override_enrollment_max,
457 p_unit_status,
458 'N',
459 'N',
460 g_n_user_id,
461 SYSDATE,
462 g_n_user_id,
463 SYSDATE,
464 g_n_login_id
465 ) RETURNING int_pat_id INTO p_int_pat_id;
466
467
468 -- If sections does not exists for the pattern then insert the location and facilities
469 IF p_unit_status IS NOT NULL THEN
470
471 FOR c_loc_rec IN c_loc(p_pat_cur_rec.unit_cd,p_pat_cur_rec.version_number) LOOP
472 INSERT INTO IGS_PS_SCH_LOC_INT(
473 int_loc_id ,
474 int_pat_id ,
475 location_code ,
476 location_description,
477 building_code ,
478 building_description,
479 room_code ,
480 room_description ,
481 created_by ,
482 creation_date ,
483 last_updated_by ,
484 last_update_date ,
485 last_update_login )
486 VALUES
487 (IGS_PS_SCH_LOC_INT_S.NEXTVAL,
488 p_int_pat_id,
489 c_loc_rec.location_cd,
490 c_loc_rec.location_description,
491 c_loc_rec.building_cd,
492 c_loc_rec.building_description,
493 c_loc_rec.room_cd,
494 c_loc_rec.room_description,
495 g_n_user_id,
496 SYSDATE,
497 g_n_user_id,
498 SYSDATE,
499 g_n_login_id
500 );
501
502 END LOOP;
503
504 FOR c_fac_rec IN c_fac(p_pat_cur_rec.unit_cd,p_pat_cur_rec.version_number) LOOP
505 INSERT INTO IGS_PS_SCH_FAC_INT (
506 int_fac_id ,
507 int_pat_id ,
508 media_code ,
509 media_description ,
510 created_by ,
511 creation_date ,
512 last_updated_by ,
513 last_update_date ,
514 last_update_login
515 )
516 VALUES
517 (IGS_PS_SCH_FAC_INT_S.NEXTVAL,
518 p_int_pat_id,
519 c_fac_rec.media_code,
520 c_fac_rec.media_description,
521 g_n_user_id,
522 SYSDATE,
523 g_n_user_id,
524 SYSDATE,
525 g_n_login_id
526 );
527 END LOOP;
528 END IF;
529
530 END transfer_patterns;
531
532 PROCEDURE transfer_sections(p_uoo_cur_rec IN uoo_cur%ROWTYPE,
533 p_int_pat_id IN NUMBER,
534 p_int_usec_id OUT NOCOPY NUMBER) AS
535 /**********************************************************
536 Created By : sarakshi
537
538 Date Created By : 21-May-2005
539
540 Purpose : Transfer Data to section interface table and its childs crosslisted and meetwith groups
541
542 Know limitations, enhancements or remarks
543
544 Change History
545
546 Who When What
547 sommukhe 24-Jan-2006 Bug #4926548,modified the cursor org_unit to fetch org_unit_description
548 ***************************************************************/
549 l_ou_description igs_or_inst_org_base_v.party_name%TYPE;
550 l_usec_x_grp_name igs_ps_usec_x_grp.usec_x_listed_group_name%TYPE;
551 l_max_enr_x_grp igs_ps_usec_x_grpmem_v.enrollment_maximum%TYPE;
552 l_meet_with_grp_name igs_ps_uso_cm_grp.class_meet_group_name%TYPE;
553
554 -- Get the Org Unit description for the unit
555 CURSOR org_unit( cp_owner_org_unit_cd igs_ps_unit_ofr_opt_all.owner_org_unit_cd%TYPE) IS
556 SELECT party_name org_unit_description
557 FROM igs_or_inst_org_base_v
558 WHERE party_number = cp_owner_org_unit_cd
559 AND inst_org_ind = 'O';
560
561
562 -- Get unit Section Cross Section
563 CURSOR usec_x_list ( l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) IS
564 SELECT unit_sec_cross_unit_sec_id,
565 parent_uoo_id,child_uoo_id,
566 child_unit_cd,child_version_number,
567 child_title,child_cal_type,
568 child_alternate_code,start_dt,
569 end_dt,child_ci_sequence_number,child_unit_class,
570 child_unit_mode,child_location_cd,child_location_description
571 FROM igs_ps_usec_x_usec_v
572 WHERE parent_uoo_id = l_uoo_id;
573
574 -- Get the Cross Listed Group Name of the uoo_id
575 CURSOR usec_x_grp_name ( l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) IS
576 SELECT A.usec_x_listed_group_name
577 FROM igs_ps_usec_x_grp_v A,
578 igs_ps_usec_x_grpmem_v B
579 WHERE A.usec_x_listed_group_id = B.usec_x_listed_group_id
580 AND B.uoo_id = l_uoo_id
581 AND B.parent = 'Y';
582
583 -- Get the Maximum Enrollment Number for the Cross Listed Group of the uoo_id
584 -- Modified the cursor to select Enrollment Maximum when it is defined at group level(Cross Listed Unit Section)
585 -- otherwise from the unit section level as a part of Enh Bug # 2613933
586
587 CURSOR max_enr_x_grp ( l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) IS
588 SELECT NVL(A.max_ovr_group, A.max_enr_group) enroll_max
589 FROM igs_ps_usec_x_grp A,
590 igs_ps_usec_x_grpmem B
591 WHERE A.max_enr_group IS NOT NULL AND
592 A.usec_x_listed_group_id = B.usec_x_listed_group_id AND
593 B.uoo_id = l_uoo_id and B.parent = 'Y'
594 UNION ALL
595 SELECT SUM(nvl(A.override_maximum, A.enrollment_maximum)) enroll_max
596 FROM igs_ps_usec_x_grpmem_v A,
597 igs_ps_usec_x_grpmem_v B
598 WHERE A.enrollment_maximum is NOT NULL
599 AND A.usec_x_listed_group_id = B.usec_x_listed_group_id
600 AND B.uoo_id = l_uoo_id
601 AND B.parent = 'Y';
602
603 -- Get the data of 'Meet_with' group related to the usec_id
604 -- Modified the cursor name from meet_with uso_id to meet_with_uoo_id and also modified
605 -- to select uoo_id instead of uso_id. As Meet with class functionality
606 -- is moved to Unit Section level as part of Enh Bug # 2613933
607 CURSOR meet_with_uoo_ids( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
608 SELECT A.uoo_id host_uoo_id,
609 B.uoo_id guest_uoo_id
610 FROM igs_ps_uso_clas_meet A,
611 igs_ps_uso_clas_meet B
612 WHERE A.class_meet_group_id = B.class_meet_group_id AND
613 A.uoo_id = l_uoo_id AND
614 A.host = 'Y' AND
615 B.host = 'N' ;
616
617 -- Get the Class Meet Group Name of the usec_id
618 -- Modified the cursor to select group name from tables rather than view as a part of Enh Bug # 2613933
619 CURSOR meet_with_grp_name ( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
620 SELECT A.class_meet_group_name
621 FROM igs_ps_uso_cm_grp A,
622 igs_ps_uso_clas_meet B
623 WHERE A.class_meet_group_id = B.class_meet_group_id AND
624 B.uoo_id = l_uoo_id AND
625 B.host = 'Y';
626
627 -- Get the Maximum enrollment for meet with group of the usec_id
628 -- Modified the cursor to select Enrollment Maximum when it is defined at group level(Meet With Class)
629 -- otherwise from the unit section level as a part of Enh Bug # 2613933
630 CURSOR max_enr_meet_grp ( l_uoo_id igs_ps_uso_clas_meet.uoo_id%TYPE) IS
631 SELECT NVL(A.max_ovr_group, A.max_enr_group) enroll_max
632 FROM igs_ps_uso_cm_grp A,
633 igs_ps_uso_clas_meet B
634 WHERE A.max_enr_group IS NOT NULL AND
635 A.class_meet_group_id = B.class_meet_group_id AND
636 B.uoo_id = l_uoo_id and B.host = 'Y'
637 UNION ALL
638 SELECT SUM(NVL(A.override_maximum, A.enrollment_maximum)) enroll_max
639 FROM igs_ps_uso_clas_meet_v A,
640 igs_ps_uso_clas_meet_v B
641 WHERE A.enrollment_maximum IS NOT NULL AND
642 A.class_meet_group_id = B.class_meet_group_id AND
643 B.uoo_id = l_uoo_id AND
644 B.host = 'Y';
645
646 BEGIN
647 -- Get Org Unit
648 OPEN org_unit(p_uoo_cur_rec.owner_org_unit_cd);
649 FETCH org_unit INTO l_ou_description;
650 CLOSE org_unit;
651
652 -- Procedure get_enrollment_lmts populates enrollment limts enrollment expected, enrollment maximum and
653 -- override maximum for this unit section in local variables l_enrollment_expected, l_enrollment_maximum
654 -- and l_override_enrollment_max respectively.
655
656 get_enrollment_lmts(p_uoo_cur_rec.uoo_id);
657
658 -- Insert Unit Section Occurs Interface Records (IGS_PS_SCH_USEC_INT_ALL)
659 BEGIN
660 INSERT INTO igs_ps_sch_usec_int_all (
661 int_usec_id ,
662 calendar_type ,
663 sequence_number ,
664 unit_cd ,
665 version_number ,
666 unit_title ,
667 owner_org_unit_cd ,
668 unit_class ,
669 unit_section_start_date ,
670 unit_section_end_date ,
671 unit_section_status ,
672 enrollment_maximum ,
673 enrollment_actual ,
674 enrollment_expected ,
675 override_enrollment_max ,
676 location_cd ,
677 cal_start_dt ,
678 cal_end_dt ,
679 uoo_id ,
680 transaction_id ,
681 org_id ,
682 IMPORT_DONE_FLAG ,
683 INT_PAT_ID ,
684 ABORT_FLAG ,
685 CALL_NUMBER ,
686 SUBTITLE ,
687 ORG_UNIT_DESCRIPTION ,
688 TEACHING_CAL_ALTERNATE_CODE ,
689 created_by ,
690 creation_date ,
691 last_updated_by ,
692 last_update_date ,
693 last_update_login
694 )
695 VALUES (
696 IGS_PS_SCH_USEC_INT_S.NEXTVAL,
697 l_cal_type,
698 l_ci_sequence_number,
699 p_uoo_cur_rec.unit_cd,
700 p_uoo_cur_rec.version_number,
701 p_uoo_cur_rec.title,
702 p_uoo_cur_rec.owner_org_unit_cd,
703 p_uoo_cur_rec.unit_class,
704 p_uoo_cur_rec.unit_section_start_date,
705 p_uoo_cur_rec.unit_section_end_date,
706 p_uoo_cur_rec.unit_section_status,
707 l_enrollment_maximum,
708 p_uoo_cur_rec.enrollment_actual,
709 l_enrollment_expected,
710 l_override_enrollment_max,
711 p_uoo_cur_rec.location_cd,
712 p_uoo_cur_rec.cal_start_dt,
713 p_uoo_cur_rec.cal_end_dt,
714 p_uoo_cur_rec.uoo_id,
715 l_trans_id,
716 p_org_id,
717 'N',
718 p_int_pat_id,
719 'N',
720 p_uoo_cur_rec.call_number,
721 p_uoo_cur_rec.subtitle,
722 l_ou_description,
723 p_uoo_cur_rec.teaching_cal_alternate_code,
724 g_n_user_id,
725 SYSDATE,
726 g_n_user_id,
727 SYSDATE,
728 g_n_login_id
729 ) RETURNING int_usec_id INTO p_int_usec_id;
730 END;
731
732 -- Insert Cross Listings Information
733 FOR usec_x_list_rec IN usec_x_list(p_uoo_cur_rec.uoo_id) LOOP
734
735 OPEN usec_x_grp_name(p_uoo_cur_rec.uoo_id);
736 FETCH usec_x_grp_name INTO l_usec_x_grp_name;
737 IF usec_x_grp_name%NOTFOUND THEN
738 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_GRP_EXISTS');
739 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
740 l_usec_x_grp_name := NULL;
741 ROLLBACK;
742 retcode := 2;
743 RETURN;
744 END IF;
745 CLOSE usec_x_grp_name;
746
747 OPEN max_enr_x_grp(p_uoo_cur_rec.uoo_id);
748 FETCH max_enr_x_grp INTO l_max_enr_x_grp;
749 IF max_enr_x_grp%NOTFOUND THEN
750 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_GRP_MAX_EXISTS');
751 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
752 l_max_enr_x_grp := NULL;
753 ROLLBACK;
754 retcode := 2;
755 RETURN;
756 END IF;
757 CLOSE max_enr_x_grp;
758
759 BEGIN
760 INSERT INTO igs_ps_sch_x_usec_int_all (
761 int_usec_x_usec_id ,
762 int_usec_id ,
763 unit_sec_cross_unit_sec_id ,
764 parent_uoo_id ,
765 child_uoo_id ,
766 child_unit_Cd ,
767 child_version_number ,
768 child_title ,
769 child_cal_type ,
770 child_alternate_code ,
771 start_dt ,
772 end_dt ,
773 child_ci_sequence_number ,
774 child_unit_class ,
775 child_unit_mode ,
776 child_location_Cd ,
777 child_location_description ,
778 org_id ,
779 cross_list_group_name ,
780 class_max_enrollment_number ,
781 created_by ,
782 creation_date ,
783 last_updated_by ,
784 last_update_date ,
785 last_update_login
786 )
787 VALUES (
788 IGS_PS_SCH_X_USEC_INT_S.NEXTVAL ,
789 p_int_usec_id,
790 usec_x_list_rec.unit_sec_cross_unit_sec_id,
791 usec_x_list_rec.parent_uoo_id,
792 usec_x_list_rec.child_uoo_id,
793 usec_x_list_rec.child_unit_cd,
794 usec_x_list_rec.child_version_number,
795 usec_x_list_rec.child_title,
796 usec_x_list_rec.child_cal_type,
797 usec_x_list_rec.child_alternate_code,
798 usec_x_list_rec.start_dt,
799 usec_x_list_rec.end_dt,
800 usec_x_list_rec.child_ci_sequence_number,
801 usec_x_list_rec.child_unit_class,
802 usec_x_list_rec.child_unit_mode,
803 usec_x_list_rec.child_location_cd,
804 usec_x_list_rec.child_location_description,
805 p_org_id,
806 l_usec_x_grp_name,
807 l_max_enr_x_grp ,
808 g_n_user_id,
809 SYSDATE,
810 g_n_user_id,
811 SYSDATE,
812 g_n_login_id
813 ) ;
814 END;
815
816 END LOOP; -- End of FOR usec_x_list_rec IN usec_x_list(p_uoo_cur_rec.uoo_id) LOOP
817
818 -- MWC functionality has been moved from Unit Section Occurrence level to
819 -- Unit Section level. As per Enh Bug # 2613933
820 -- Insert Meet with group related Information
821 -- As the Meet with class functionality is moved to Unit Section level
822 -- Modified the cursors meet_with_uso_ids as meet_with_uoo_id. Also modified to cursor
823 -- to get meet_with_grp_name,max_enr_meet_grp with UOO_ID insted of USO_ID
824
825 FOR meet_with_grp_data_rec IN meet_with_uoo_ids ( p_uoo_cur_rec.uoo_id) LOOP
826
827 BEGIN
828
829 OPEN meet_with_grp_name(p_uoo_cur_rec.uoo_id);
830 FETCH meet_with_grp_name INTO l_meet_with_grp_name;
831 IF meet_with_grp_name%NOTFOUND THEN
832 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_MEET_GRP_EXISTS');
833 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
834 ROLLBACK;
835 RETCODE := 2;
836 RETURN;
837 END IF;
838 CLOSE meet_with_grp_name;
839
840 OPEN max_enr_meet_grp(p_uoo_cur_rec.uoo_id);
841 FETCH max_enr_meet_grp INTO l_max_enr_meet_grp;
842 IF max_enr_meet_grp%NOTFOUND THEN
843 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_MEET_GRP_MAX_EXISTS');
844 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
845 ROLLBACK;
846 RETCODE := 2;
847 RETURN;
848 END IF;
849 CLOSE max_enr_meet_grp;
850
851 INSERT INTO igs_ps_sch_mwc_all (
852 mwc_group_id ,
853 meet_with_class_group_name ,
854 host_uoo_id ,
855 guest_uoo_id ,
856 mwc_max_enrollment_number ,
857 org_id ,
858 int_usec_id ,
859 created_by ,
860 creation_date ,
861 last_updated_by ,
862 last_update_date ,
863 last_update_login
864 )
865 VALUES (
866 IGS_PS_SCH_MWC_S.NEXTVAL,
867 l_meet_with_grp_name,
868 meet_with_grp_data_rec.host_uoo_id,
869 meet_with_grp_data_rec.guest_uoo_id,
870 l_max_enr_meet_grp,
871 p_org_id,
872 p_int_usec_id,
873 g_n_user_id,
874 SYSDATE,
875 g_n_user_id,
876 SYSDATE,
877 g_n_login_id
878 );
879 END;
880
881 END LOOP; -- End of FOR meet_with_grp_data_rec IN meet_with_uoo_ids ( p_uoo_cur_rec.uoo_id) LOOP
882
883 END transfer_sections;
884
885
886 PROCEDURE transfer_occurrences(p_usec_occur_rec usec_occur%ROWTYPE,
887 p_uoo_cur_rec uoo_cur%ROWTYPE,
888 p_int_usec_id IN NUMBER,
889 P_trans_type IN VARCHAR2) AS
890 /**********************************************************
891 Created By : sarakshi
892
893 Date Created By : 21-May-2005
894
895 Purpose : Transfer Data to occurrence interface table and its childs instructors, facilities and reference codes.
896
897 Know limitations, enhancements or remarks
898
899 Change History
900
901 Who When What
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
911 FROM igs_ps_usec_ocur_ref_v
912 WHERE unit_section_occurrence_id = l_usec_id;
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,
922 B.party_name person_name
923 FROM igs_ps_uso_instrctrs_v A,
924 HZ_PARTIES B
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;
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,
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
944 AND b.unit_cd=c.unit_code
945 AND b.version_number=c.unit_version_number;
946
947 l_section_facility_exist BOOLEAN := FALSE;
948
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;
958 l_end_date := p_uoo_cur_rec.cal_end_dt;
959 ELSE
960 l_start_date := p_usec_occur_rec.start_date;
961 l_end_date := p_usec_occur_rec.end_date;
962 END IF;
963
964 -- The fields which are passed as NULL are OBSOLETE columns as per the Modified Scheduling Interface DLD 1.0
965 -- Enh bug #2833850.
966 -- Added the column preferred_region_code to the call igs_ps_sch_int_pkg.insert_row
967 INSERT INTO igs_ps_sch_int_all (
968 int_occurs_id ,
969 int_usec_id ,
970 calendar_type ,
971 sequence_number ,
972 transaction_type ,
973 unit_section_occurrence_id ,
974 unit_cd ,
975 version_number ,
976 unit_title ,
977 owner_org_unit_cd ,
978 unit_class ,
979 monday ,
980 tuesday ,
981 wednesday ,
982 thursday ,
983 friday ,
984 saturday ,
985 sunday ,
986 unit_section_start_date ,
987 unit_section_end_date ,
988 start_time ,
989 end_time ,
990 enrollment_maximum ,
991 enrollment_actual ,
992 instructor_id ,
993 building_id ,
994 room_id ,
995 dedicated_building_id ,
996 dedicated_room_id ,
997 preferred_building_id ,
998 preferred_room_id ,
999 tba_status ,
1000 uso_start_date ,
1001 uso_end_date ,
1002 location_cd ,
1003 unit_sec_cross_unit_sec_id ,
1004 uoo_id ,
1005 schedule_status ,
1006 error_text ,
1007 transaction_id ,
1008 surname ,
1009 given_names ,
1010 middle_name ,
1011 preferred_region_code ,
1012 org_id ,
1013 occurrence_identifier ,
1014 import_done_flag ,
1015 abort_flag ,
1016 created_by ,
1017 creation_date ,
1018 last_updated_by ,
1019 last_update_date ,
1020 last_update_login
1021 )
1022 VALUES (
1023 IGS_PS_SCH_INT_S.NEXTVAL,
1024 p_int_usec_id,
1025 NULL,
1026 NULL,
1027 P_trans_type,
1028 p_usec_occur_rec.unit_section_occurrence_id,
1029 NULL,
1030 NULL,
1031 NULL,
1032 NULL,
1033 NULL,
1034 p_usec_occur_rec.monday,
1035 p_usec_occur_rec.tuesday,
1036 p_usec_occur_rec.wednesday,
1037 p_usec_occur_rec.thursday,
1038 p_usec_occur_rec.friday,
1039 p_usec_occur_rec.saturday,
1040 p_usec_occur_rec.sunday,
1041 l_start_date,
1042 l_end_date,
1043 p_usec_occur_rec.start_time,
1044 p_usec_occur_rec.end_time,
1045 NULL,
1046 NULL,
1047 l_lead_instructor_id,
1048 p_usec_occur_rec.building_code,
1049 p_usec_occur_rec.room_code,
1050 p_usec_occur_rec.dedicated_building_code,
1051 p_usec_occur_rec.dedicated_room_code,
1052 p_usec_occur_rec.preferred_building_code,
1053 p_usec_occur_rec.preferred_room_code,
1054 p_usec_occur_rec.to_be_announced,
1055 p_usec_occur_rec.start_date,
1056 p_usec_occur_rec.end_date,
1057 NULL,
1058 NULL,
1059 NULL,
1060 NULL,
1061 NULL,
1062 NULL,
1063 l_surname ,
1064 l_given_names,
1065 l_middle_name,
1066 p_usec_occur_rec.preferred_region_code,
1067 p_org_id,
1068 p_usec_occur_rec.occurrence_identifier,
1069 'N',
1070 'N',
1071 g_n_user_id,
1072 SYSDATE,
1073 g_n_user_id,
1074 SYSDATE,
1075 g_n_login_id
1076 ) RETURNING int_occurs_id INTO l_int_occurs_id;
1077 END;
1078
1079 -- Insert Ref Cd Information
1080 FOR ref_cd_rec IN ref_cd( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
1081 BEGIN
1082 -- The fields which are passed as NULL are OBSOLETE columns as per the Modified Scheduling Interface DLD 1.0
1083 INSERT INTO igs_ps_prefs_sch_int_all (
1084 int_prefs_id ,
1085 int_occurs_id ,
1086 reference_cd ,
1087 reference_code_description ,
1088 reference_cd_type ,
1089 reference_type_description ,
1090 transaction_id ,
1091 unit_section_occurrence_id ,
1092 org_id ,
1093 created_by ,
1094 creation_date ,
1095 last_updated_by ,
1096 last_update_date ,
1097 last_update_login
1098 )
1099 VALUES (
1100 IGS_PS_PREFS_SCH_INT_S.NEXTVAL,
1101 l_int_occurs_id,
1102 ref_cd_rec.reference_code,
1103 ref_cd_rec.reference_code_description,
1104 ref_cd_rec.reference_code_type,
1105 ref_cd_rec.reference_type_description,
1106 NULL,
1107 NULL,
1108 p_org_id,
1109 g_n_user_id,
1110 SYSDATE,
1111 g_n_user_id,
1112 SYSDATE,
1113 g_n_login_id
1114 );
1115 END;
1116 END LOOP;
1117
1118 -- Insert Instructor related Information
1119 FOR instrs_rec IN instrs( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
1120 BEGIN
1121 INSERT INTO igs_ps_sch_instr_all (
1122 int_instruc_id ,
1123 instructor_id ,
1124 surname ,
1125 given_names ,
1126 middle_name ,
1127 int_occurs_id ,
1128 person_name ,
1129 org_id ,
1130 created_by ,
1131 creation_date ,
1132 last_updated_by ,
1133 last_update_date ,
1134 last_update_login
1135 )
1136 VALUES (
1137 IGS_PS_SCH_INSTR_S.NEXTVAL,
1138 instrs_rec.instructor_id,
1139 instrs_rec.surname,
1140 instrs_rec.given_names,
1141 instrs_rec.middle_name,
1142 l_int_occurs_id,
1143 instrs_rec.person_name,
1144 p_org_id,
1145 g_n_user_id,
1146 SYSDATE,
1147 g_n_user_id,
1148 SYSDATE,
1149 g_n_login_id
1150 );
1151 END;
1152 END LOOP;
1153
1154 -- Insert Facility related Information
1155 FOR facilities_rec IN facilities ( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
1156 BEGIN
1157 l_section_facility_exist := TRUE;
1158 INSERT INTO igs_ps_sch_faclt_all (
1159 facility_id ,
1160 facility_code ,
1161 facility_description ,
1162 org_id ,
1163 int_occurs_id ,
1164 created_by ,
1165 creation_date ,
1166 last_updated_by ,
1167 last_update_date ,
1168 last_update_login
1169 )
1170 VALUES (
1171 IGS_PS_SCH_FACLT_S.NEXTVAL,
1172 facilities_rec.facility_code,
1173 facilities_rec.facility_description,
1174 p_org_id,
1175 l_int_occurs_id,
1176 g_n_user_id,
1177 SYSDATE,
1178 g_n_user_id,
1179 SYSDATE,
1180 g_n_login_id
1181 );
1182 END;
1183 END LOOP;
1184
1185 --If facilities does not exist at section level then pass the unit level value
1186 IF l_section_facility_exist = FALSE THEN
1187 FOR unit_facilities_rec IN unit_facilities ( p_usec_occur_rec.unit_section_occurrence_id ) LOOP
1188 BEGIN
1189 INSERT INTO igs_ps_sch_faclt_all (
1190 facility_id ,
1191 facility_code ,
1192 facility_description ,
1193 org_id ,
1194 int_occurs_id ,
1195 created_by ,
1196 creation_date ,
1197 last_updated_by ,
1198 last_update_date ,
1199 last_update_login
1200 )
1201 VALUES (
1202 IGS_PS_SCH_FACLT_S.NEXTVAL,
1203 unit_facilities_rec.media_code,
1204 unit_facilities_rec.media_description,
1205 p_org_id,
1206 l_int_occurs_id,
1207 g_n_user_id,
1208 SYSDATE,
1209 g_n_user_id,
1210 SYSDATE,
1211 g_n_login_id
1212 );
1213 END;
1214 END LOOP;
1215 END IF;
1216
1217 END transfer_occurrences;
1218
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
1228
1229 Purpose : Transfer Data to scheduling interface table
1230
1231 Know limitations, enhancements or remarks
1232
1233 Change History
1234
1235 Who When What
1236 sommukhe 22-MAY-2006 Bug#5239345,Added one extra parameter to Cursor c_is_req and hence conditional
1237 transfer of sections depending on the schedule type.
1238 sarakshi 12-Jan-2005 Bug#4926548, created cursors usec_ref and unit_sub
1239 ***************************************************************/
1240
1241 CURSOR cur_prod_pattern(cp_unit_cd IN VARCHAR2,
1242 cp_version_number IN NUMBER,
1243 cp_cal_type IN VARCHAR2,
1244 cp_ci_sequence_number IN NUMBER) IS
1245 SELECT pt.*,pt.rowid
1246 FROM igs_ps_unit_ofr_pat pt
1247 WHERE unit_cd=cp_unit_cd
1248 AND version_number=cp_version_number
1249 AND cal_type=cp_cal_type
1250 AND ci_sequence_number=cp_ci_sequence_number;
1251 l_prod_pattern cur_prod_pattern%ROWTYPE;
1252
1253 CURSOR cur_prod_uoo(cp_uoo_id IN NUMBER) IS
1254 SELECT us.*,us.rowid
1255 FROM igs_ps_unit_ofr_opt_all us
1256 WHERE us.uoo_id = cp_uoo_id;
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;
1266 l_trans_type VARCHAR2(30);
1267
1268 CURSOR usec_ref(cp_uoo_id igs_ps_usec_ref_v.uoo_id%TYPE) IS
1269 SELECT ur.title, ur.subtitle
1270 FROM igs_ps_usec_ref_v ur
1271 WHERE ur.uoo_id = cp_uoo_id;
1272
1273 CURSOR unit_sub(cp_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE) IS
1274 SELECT usub.subtitle
1275 FROM igs_ps_unit_subtitle usub
1276 WHERE usub.subtitle_id = cp_subtitle_id ;
1277 l_usec_title igs_ps_usec_ref_v.title%TYPE;
1278 l_usec_subtitle igs_ps_usec_ref_v.subtitle%TYPE;
1279 l_unit_subtitle igs_ps_unit_subtitle.subtitle%TYPE;
1280
1281
1282 BEGIN
1283 -- Set the Derived scheduler Type
1284 IF p_sch_type = 'REQUEST' OR p_sch_type IS NULL THEN
1285 l_derd_sch_type := 'NULL';
1286 ELSIF p_sch_type = 'UPDATE' THEN
1287 l_derd_sch_type := 'USER_UPDATE';
1288 ELSIF p_sch_type = 'CANCEL' THEN
1289 l_derd_sch_type := 'USER_CANCEL';
1290 END IF;
1291
1292
1293 --Get the pattern details and insert in the pattern interface table
1294 FOR pat_cur_rec IN pat_cur(l_cal_type,l_ci_sequence_number,p_c_unit_cd,p_n_version_number) LOOP
1295
1296 IF l_trans_id IS NULL THEN
1297 create_header;
1298 END IF;
1299
1300 l_unit_status:= NULL;
1301 l_int_pat_id:= NULL;
1302 transfer_patterns(pat_cur_rec,l_unit_status,l_int_pat_id);
1303
1304 --If section does exists in the production
1305 IF l_unit_status IS NULL THEN
1306 -- Get the Unit Section Details
1307 FOR uoo_cur_rec IN uoo_cur(l_cal_type, l_ci_sequence_number,pat_cur_rec.unit_cd,pat_cur_rec.version_number,p_c_location_cd,p_n_unit_class) LOOP
1308
1309 --For performance reason share memory high , creating independent sql for getting certain values for the
1310 --above cursor
1311 l_usec_title :=NULL;
1312 l_usec_subtitle := NULL;
1313 l_unit_subtitle := NULL;
1314
1315 OPEN usec_ref(uoo_cur_rec.uoo_id);
1316 FETCH usec_ref INTO l_usec_title,l_usec_subtitle;
1317 CLOSE usec_ref;
1318
1319 IF l_usec_subtitle IS NULL AND uoo_cur_rec.subtitle_id IS NOT NULL THEN
1320 OPEN unit_sub(uoo_cur_rec.subtitle_id);
1321 FETCH unit_sub INTO l_unit_subtitle;
1322 CLOSE unit_sub;
1323 END IF;
1324
1325 uoo_cur_rec.title := NVL(l_usec_title,uoo_cur_rec.title);
1326 uoo_cur_rec.subtitle := NVL(l_usec_subtitle,l_unit_subtitle);
1327
1328 l_valid_occur:= FALSE; --added by sarakshi
1329 OPEN c_is_req (uoo_cur_rec.uoo_id,p_n_usec_id,l_derd_sch_type) ;
1330 FETCH c_is_req INTO rec_is_req;
1331 -- if there exist atleast one unit section which needs to be transferred to interface table then
1332 IF c_is_req%FOUND THEN
1333
1334 transfer_sections(uoo_cur_rec,l_int_pat_id,l_int_usec_id);
1335
1336 END IF; -- End of
1337 CLOSE c_is_req;
1338
1339 --function get_instructor_info populates lead instructor identifier, given name(first name), middle name
1340 --and last name in the local variables l_lead_instructor_id, l_given_names ,l_middle_name and l_surname respectively.
1341 -- returns false only when lead instructor is set at unit section and could not fetch instructor details.
1342 IF NOT get_instructor_info(uoo_cur_rec.uoo_id) THEN
1343 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_NAMES_EXISTS');
1344 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1345 ROLLBACK;
1346 RETCODE := 2;
1347 RETURN;
1348 END IF;
1349
1350
1351 -- Get unit Section occurrence details
1352 FOR usec_occur_rec IN usec_occur( uoo_cur_rec.uoo_id,p_n_usec_id) LOOP
1353
1354 -- Get the schedule_status
1355 IF p_sch_type IS NOT NULL THEN --Called from SRS
1356 IF NVL(usec_occur_rec.schedule_status,'NULL') = l_derd_sch_type THEN
1357 l_valid_occur:= TRUE;
1358 transfer_occurrences(usec_occur_rec,uoo_cur_rec,l_int_usec_id,p_sch_type);
1359 END IF;
1360 ELSE --Called from buttons (schedule current/set)
1361 IF NVL(usec_occur_rec.schedule_status,'NULL') IN ('NULL','CANCELLED') THEN
1362 l_trans_type := 'REQUEST';
1363 l_valid_occur:= TRUE;
1364 transfer_occurrences(usec_occur_rec,uoo_cur_rec,l_int_usec_id,l_trans_type);
1365 ELSIF usec_occur_rec.schedule_status = 'USER_UPDATE' THEN
1366 l_trans_type := 'UPDATE';
1367 l_valid_occur:= TRUE;
1368 transfer_occurrences(usec_occur_rec,uoo_cur_rec,l_int_usec_id,l_trans_type);
1369 ELSIF usec_occur_rec.schedule_status = 'USER_CANCEL' THEN
1370 l_trans_type := 'CANCEL';
1371 l_valid_occur:= TRUE;
1372 transfer_occurrences(usec_occur_rec,uoo_cur_rec,l_int_usec_id,l_trans_type);
1373 END IF;
1374
1375 END IF;
1376
1377 END LOOP; -- End of unit Section occurrence details loop
1378
1379 l_usec := TRUE;
1380
1381 --If valid occurrence is not there, and the section is not having any occurrences in production table then
1382 --insert the section record
1383 IF l_valid_occur = FALSE THEN
1384 OPEN c_occur_exists(uoo_cur_rec.uoo_id);
1385 FETCH c_occur_exists INTO l_c_var;
1386 IF c_occur_exists%NOTFOUND THEN
1387 IF p_sch_type = 'REQUEST' THEN
1388 transfer_sections(uoo_cur_rec,l_int_pat_id,l_int_usec_id);
1389 END IF;
1390 END IF;
1391 CLOSE c_occur_exists;
1392 END IF;
1393
1394 END LOOP; -- End of FOR uoo_cur_rec IN uoo_cur
1395 END IF;
1396
1397 --IF there is no section for the pattern record in the interface
1398 --If no section are there in production also then do nothing else delete the pattern record.
1399
1400 OPEN c_section_int_exists(l_int_pat_id);
1401 FETCH c_section_int_exists INTO l_c_var;
1402 IF c_section_int_exists%NOTFOUND THEN
1403 IF (p_sch_type = 'REQUEST' AND l_unit_status IS NULL) OR (p_sch_type IN ('UPDATE','CANCEL') ) THEN
1404 DELETE IGS_PS_SCH_PAT_INT WHERE int_pat_id=l_int_pat_id;
1405 END IF;
1406 END IF;
1407 CLOSE c_section_int_exists;
1408
1409 END LOOP; --Pattern loop
1410
1411 --If there are no pattern records in the interface table against the header record then delete the header record.
1412 IF l_trans_id IS NOT NULL THEN
1413 OPEN c_pattern_int_exists(l_trans_id);
1414 FETCH c_pattern_int_exists INTO l_c_var;
1415 IF c_pattern_int_exists%NOTFOUND THEN
1416 DELETE IGS_PS_SCH_HDR_INT_ALL WHERE transaction_id = l_trans_id;
1417 l_data_found := FALSE;
1418 ELSE
1419 l_data_found := TRUE;
1420 --Update all the production records for the abort_flag='N'
1421 FOR l_pattern_prod_rec IN c_pattern_prod(l_trans_id) LOOP
1422
1423 --Log the pattern in the log file
1424 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1425 fnd_file.put_line(fnd_file.log, igs_ps_validate_lgcy_pkg.get_lkup_meaning('PATTERNS','IGS_PS_TABLE_NAME')||':');
1426 fnd_file.put_line(fnd_file.log, '**********');
1427 log_teach_cal(l_pattern_prod_rec.calendar_type,l_pattern_prod_rec.sequence_number);
1428 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),l_pattern_prod_rec.unit_cd,10);
1429 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('VERSION_NUMBER','IGS_PS_LOG_PARAMETERS'),l_pattern_prod_rec.version_number,10);
1430
1431
1432 OPEN cur_prod_pattern(l_pattern_prod_rec.unit_cd,l_pattern_prod_rec.version_number,l_pattern_prod_rec.calendar_type,l_pattern_prod_rec.sequence_number);
1433 FETCH cur_prod_pattern INTO l_prod_pattern;
1434 CLOSE cur_prod_pattern;
1435
1436 igs_ps_unit_ofr_pat_pkg.update_row (
1437 X_Mode => 'R',
1438 X_RowId => l_prod_pattern.rowid,
1439 X_Unit_Cd => l_prod_pattern.Unit_Cd,
1440 X_Version_Number => l_prod_pattern.Version_Number,
1441 X_Cal_Type => l_prod_pattern.Cal_Type,
1442 X_Ci_Sequence_Number => l_prod_pattern.Ci_Sequence_Number,
1443 X_Ci_Start_Dt => l_prod_pattern.Ci_Start_Dt,
1444 X_Ci_End_Dt => l_prod_pattern.Ci_End_Dt,
1445 x_waitlist_allowed => l_prod_pattern.waitlist_allowed,
1446 x_max_students_per_waitlist => l_prod_pattern.max_students_per_waitlist,
1447 x_delete_flag => l_prod_pattern.delete_flag,
1448 x_abort_flag => 'N'
1449 );
1450
1451
1452 l_o_count:=1;
1453 FOR l_section_prod_rec IN c_section(l_pattern_prod_rec.int_pat_id) LOOP
1454
1455 --Log the section in the log file
1456 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1457 IF l_o_count=1 THEN
1458 fnd_file.put_line(fnd_file.log, igs_ps_validate_lgcy_pkg.get_lkup_meaning('SECTIONS','IGS_PS_TABLE_NAME')||':');
1459 fnd_file.put_line(fnd_file.log, '----------');
1460 END IF;
1461 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),l_section_prod_rec.unit_cd,10);
1462 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('VERSION_NUMBER','IGS_PS_LOG_PARAMETERS'),l_section_prod_rec.version_number,10);
1463 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('LOC','IGS_FI_ACCT_ENTITIES'),l_section_prod_rec.location_cd,10);
1464 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CLASS','LEGACY_TOKENS'),l_section_prod_rec.unit_class,10);
1465
1466
1467
1468
1469 OPEN cur_prod_uoo(l_section_prod_rec.uoo_id);
1470 FETCH cur_prod_uoo INTO l_cur_uoo_rec;
1471 CLOSE cur_prod_uoo;
1472
1473 igs_ps_unit_ofr_opt_pkg.update_row (
1474 x_mode => 'R',
1475 x_rowid => l_cur_uoo_rec.rowid,
1476 x_unit_cd => l_cur_uoo_rec.unit_cd,
1477 x_version_number => l_cur_uoo_rec.version_number,
1478 x_cal_type => l_cur_uoo_rec.cal_type,
1479 x_ci_sequence_number => l_cur_uoo_rec.ci_sequence_number,
1480 x_location_cd => l_cur_uoo_rec.location_cd,
1481 x_unit_class => l_cur_uoo_rec.unit_class,
1482 x_uoo_id => l_cur_uoo_rec.uoo_id,
1483 x_ivrs_available_ind => l_cur_uoo_rec.ivrs_available_ind,
1484 x_call_number => l_cur_uoo_rec.call_number,
1485 x_unit_section_status => l_cur_uoo_rec.unit_section_status,
1486 x_unit_section_start_date => l_cur_uoo_rec.unit_section_start_date,
1487 x_unit_section_end_date => l_cur_uoo_rec.unit_section_end_date,
1488 x_enrollment_actual => l_cur_uoo_rec.enrollment_actual,
1489 x_waitlist_actual => l_cur_uoo_rec.waitlist_actual,
1490 x_offered_ind => l_cur_uoo_rec.offered_ind,
1491 x_state_financial_aid => l_cur_uoo_rec.state_financial_aid,
1492 x_grading_schema_prcdnce_ind => l_cur_uoo_rec.grading_schema_prcdnce_ind,
1493 x_federal_financial_aid => l_cur_uoo_rec.federal_financial_aid,
1494 x_unit_quota => l_cur_uoo_rec.unit_quota,
1495 x_unit_quota_reserved_places => l_cur_uoo_rec.unit_quota_reserved_places,
1496 x_institutional_financial_aid => l_cur_uoo_rec.institutional_financial_aid,
1497 x_unit_contact => l_cur_uoo_rec.unit_contact,
1498 x_grading_schema_cd => l_cur_uoo_rec.grading_schema_cd,
1499 x_gs_version_number => l_cur_uoo_rec.gs_version_number,
1500 x_owner_org_unit_cd => l_cur_uoo_rec.owner_org_unit_cd,
1501 x_attendance_required_ind => l_cur_uoo_rec.attendance_required_ind,
1502 x_reserved_seating_allowed => l_cur_uoo_rec.reserved_seating_allowed,
1503 x_special_permission_ind => l_cur_uoo_rec.special_permission_ind,
1504 x_ss_enrol_ind => l_cur_uoo_rec.ss_enrol_ind,
1505 x_ss_display_ind => l_cur_uoo_rec.ss_display_ind,
1506 x_dir_enrollment => l_cur_uoo_rec.dir_enrollment,
1507 x_enr_from_wlst => l_cur_uoo_rec.enr_from_wlst,
1508 x_inq_not_wlst => l_cur_uoo_rec.inq_not_wlst,
1509 x_rev_account_cd => l_cur_uoo_rec.rev_account_cd,
1510 x_anon_unit_grading_ind => l_cur_uoo_rec.anon_unit_grading_ind,
1511 x_anon_assess_grading_ind => l_cur_uoo_rec.anon_assess_grading_ind,
1512 x_non_std_usec_ind => l_cur_uoo_rec.non_std_usec_ind,
1513 x_auditable_ind => l_cur_uoo_rec.auditable_ind,
1514 x_audit_permission_ind => l_cur_uoo_rec.audit_permission_ind,
1515 x_not_multiple_section_flag => l_cur_uoo_rec.not_multiple_section_flag,
1516 x_sup_uoo_id => l_cur_uoo_rec.sup_uoo_id,
1517 x_relation_type => l_cur_uoo_rec.relation_type,
1518 x_default_enroll_flag => l_cur_uoo_rec.default_enroll_flag,
1519 x_abort_flag => 'N'
1520 );
1521
1522
1523 --UPDATE igs_ps_unit_ofr_opt_all set abort_flag='N' where uoo_id=l_section_prod_rec.uoo_id;
1524
1525 l_o_count := 0;
1526 FOR l_occurrence_prod_rec IN c_occurrence(l_section_prod_rec.int_usec_id) LOOP
1527
1528 --Log the occurrences in the log file
1529 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1530 IF l_o_count = 0 THEN
1531 fnd_file.put_line(fnd_file.log, igs_ps_validate_lgcy_pkg.get_lkup_meaning('OCCURRENCES','IGS_PS_TABLE_NAME')||':');
1532 fnd_file.put_line(fnd_file.log, '-------------');
1533 l_o_count:=1;
1534 END IF;
1535 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('USEC_OCCRS_ID','IGS_PS_LOG_PARAMETERS'),l_occurrence_prod_rec.occurrence_identifier,10);
1536
1537
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,
1547 x_tuesday => l_usec_occurs_rec.tuesday,
1548 x_wednesday => l_usec_occurs_rec.wednesday,
1549 x_thursday => l_usec_occurs_rec.thursday,
1550 x_friday => l_usec_occurs_rec.friday,
1551 x_saturday => l_usec_occurs_rec.saturday,
1552 x_sunday => l_usec_occurs_rec.sunday,
1553 x_start_time => l_usec_occurs_rec.start_time,
1554 x_end_time => l_usec_occurs_rec.end_time,
1555 x_building_code => l_usec_occurs_rec.building_code,
1556 x_room_code => l_usec_occurs_rec.room_code,
1557 x_schedule_status => 'PROCESSING',
1558 x_status_last_updated => SYSDATE,
1559 x_instructor_id => l_usec_occurs_rec.instructor_id,
1560 X_attribute_category => l_usec_occurs_rec.attribute_category,
1561 X_attribute1 => l_usec_occurs_rec.attribute1,
1562 X_attribute2 => l_usec_occurs_rec.attribute2,
1563 X_attribute3 => l_usec_occurs_rec.attribute3,
1564 X_attribute4 => l_usec_occurs_rec.attribute4,
1565 X_attribute5 => l_usec_occurs_rec.attribute5,
1566 X_attribute6 => l_usec_occurs_rec.attribute6,
1567 X_attribute7 => l_usec_occurs_rec.attribute7,
1568 X_attribute8 => l_usec_occurs_rec.attribute8,
1569 X_attribute9 => l_usec_occurs_rec.attribute9,
1570 X_attribute10 => l_usec_occurs_rec.attribute10,
1571 X_attribute11 => l_usec_occurs_rec.attribute11,
1572 X_attribute12 => l_usec_occurs_rec.attribute12,
1573 X_attribute13 => l_usec_occurs_rec.attribute13,
1574 X_attribute14 => l_usec_occurs_rec.attribute14,
1575 X_attribute15 => l_usec_occurs_rec.attribute15,
1576 X_attribute16 => l_usec_occurs_rec.attribute16,
1577 X_attribute17 => l_usec_occurs_rec.attribute17,
1578 X_attribute18 => l_usec_occurs_rec.attribute18,
1579 X_attribute19 => l_usec_occurs_rec.attribute19,
1580 X_attribute20 => l_usec_occurs_rec.attribute20,
1581 x_error_text => l_usec_occurs_rec.error_text,
1582 x_mode => 'R',
1583 X_start_date => l_usec_occurs_rec.start_date,
1584 X_end_date => l_usec_occurs_rec.end_date,
1585 X_to_be_announced => l_usec_occurs_rec.to_be_announced,
1586 x_dedicated_building_code => l_usec_occurs_rec.dedicated_building_code,
1587 x_dedicated_room_code => l_usec_occurs_rec.dedicated_room_code,
1588 x_preferred_building_code => l_usec_occurs_rec.preferred_building_code,
1589 x_preferred_room_code => l_usec_occurs_rec.preferred_room_code,
1590 x_inst_notify_ind => l_usec_occurs_rec.inst_notify_ind,
1591 x_notify_status => l_usec_occurs_rec.notify_status,
1592 x_preferred_region_code => l_usec_occurs_rec.preferred_region_code,
1593 x_no_set_day_ind => l_usec_occurs_rec.no_set_day_ind,
1594 x_cancel_flag => 'N',
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;
1604
1605 END LOOP;
1606
1607
1608 END IF;
1609 CLOSE c_pattern_int_exists;
1610 END IF;
1611
1612
1613
1614
1615 END call_generic_transfer;
1616 ---
1617
1618
1619 BEGIN
1620
1621 l_data_found := FALSE;
1622
1623 -- Set the default status as success
1624 retcode := 0;
1625
1626 -- set the multi org id
1627 igs_ge_gen_003.set_org_id (p_org_id);
1628
1629 -- Check for the scheduling software is installed or not
1630 -- Profile name (IGS: Indicates whether Scheduling Software is installed.)
1631 -- Give a message to user if the software is not installed and
1632 -- stop the further processing
1633 IF (NVL(FND_PROFILE.VALUE('IGS_PS_SCH_SOFT_NOT_INSTLD'),'N')) = 'N' THEN
1634 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SCH_SOFT_NOT_INSTLD');
1635 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1636 retcode := 2;
1637 RETURN;
1638 END IF;
1639
1640
1641 -- Not enough parameters condition
1642 IF ( (p_teach_prd IS NULL) AND (p_uoo_id IS NULL) AND (p_usec_id IS NULL ) AND (p_sch_type IS NULL) ) THEN
1643 FND_MESSAGE.SET_NAME('IGS','IGS_GE_NOT_ENGH_PARAM');
1644 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1645 retcode := 2;
1646 RETURN;
1647 END IF;
1648
1649 -- Scheduling Can only be done for Current and Future teaching period Unit Sections and
1650 -- not for past teaching period unit sections for the Bug # 2383610 as a part of Bug # 2383553
1651
1652 IF p_teach_prd IS NULL AND p_uoo_id IS NOT NULL THEN
1653 OPEN c_end_dt(p_uoo_id);
1654 FETCH c_end_dt INTO rec_end_dt;
1655 CLOSE c_end_dt;
1656 IF TRUNC(rec_end_dt.cal_end_dt) < TRUNC(sysdate) THEN
1657 FND_MESSAGE.SET_NAME('IGS','IGS_PS_CANT_SCH_PAST_TP');
1658 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1659 ROLLBACK;
1660 retcode := 2;
1661 RETURN;
1662 END IF;
1663 END IF;
1664
1665
1666 -- check the valid values for the sch_type
1667 IF p_sch_type IS NOT NULL AND p_sch_type NOT IN ('REQUEST', 'UPDATE', 'CANCEL' ) THEN
1668
1669 FND_MESSAGE.SET_NAME('IGS','IGS_PS_INVALID_SCHTYPE');
1670 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1671 retcode := 2;
1672 RETURN;
1673 END IF;
1674
1675 -- Batch Program case
1676 -- 1.Teach Period is supplied - Batch Process
1677 --
1678 IF p_teach_prd IS NOT NULL THEN
1679
1680 -- Get the cal_tpe,sequence_number and start date and End date
1681 l_cal_type := RTRIM(SUBSTR(p_teach_prd,101,10));
1682 l_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_teach_prd,112,6)));
1683 l_start_date := fnd_date.string_to_date(RTRIM(SUBSTR(p_teach_prd,11,11)), 'DD-MON-YYYY');
1684 l_end_date := fnd_date.string_to_date(RTRIM(SUBSTR(p_teach_prd,24,11)), 'DD-MON-YYYY');
1685
1686 -- Check for End date is <= sysdate
1687 -- Thus avoiding scheduling of past teaching periods.
1688 IF TRUNC(l_end_date) < TRUNC(sysdate) THEN
1689 FND_MESSAGE.SET_NAME('IGS','IGS_PS_CANT_SCH_PAST_TP');
1690 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1691 ROLLBACK;
1692 retcode := 2;
1693 RETURN;
1694 END IF;
1695
1696 call_generic_transfer (
1697 p_c_unit_cd =>NULL,
1698 p_n_version_number =>NULL,
1699 p_c_location_cd =>NULL,
1700 p_n_unit_class =>NULL,
1701 p_n_usec_id =>NULL);
1702
1703 ELSIF p_uoo_id IS NOT NULL AND p_usec_ID IS NULL THEN
1704 OPEN c_usec_param(p_uoo_id);
1705 FETCH c_usec_param INTO l_usec_param;
1706 CLOSE c_usec_param;
1707
1708 l_cal_type := l_usec_param.cal_type;
1709 l_ci_sequence_number := l_usec_param.ci_sequence_number;
1710
1711 call_generic_transfer (
1712 p_c_unit_cd =>l_usec_param.unit_cd,
1713 p_n_version_number =>l_usec_param.version_number,
1714 p_c_location_cd =>l_usec_param.location_cd,
1715 p_n_unit_class =>l_usec_param.unit_class,
1716 p_n_usec_id =>NULL);
1717
1718
1719 ELSIF p_uoo_id IS NOT NULL AND p_usec_ID IS NOT NULL THEN
1720 OPEN c_usec_param(p_uoo_id);
1721 FETCH c_usec_param INTO l_usec_param;
1722 CLOSE c_usec_param;
1723
1724 l_cal_type := l_usec_param.cal_type;
1725 l_ci_sequence_number := l_usec_param.ci_sequence_number;
1726
1727 call_generic_transfer (
1728 p_c_unit_cd =>l_usec_param.unit_cd,
1729 p_n_version_number =>l_usec_param.version_number,
1730 p_c_location_cd =>l_usec_param.location_cd,
1731 p_n_unit_class =>l_usec_param.unit_class,
1732 p_n_usec_id =>p_usec_id);
1733
1734 END IF;
1735
1736 -- if there exist no valid USO then transfer of data should not take place and rollback should take place for the earlier inserted records
1737 IF l_data_found = FALSE THEN
1738 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_DATA_TRANSFER');
1739 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1740 ROLLBACK;
1741 RETURN;
1742 END IF;
1743
1744 -- Call the Hooker Procedure
1745 prgp_init_scheduling;
1746
1747
1748 -- End of Procedure
1749 retcode := 0;
1750 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SCH_PRS_INIT_SUCCESS');
1751 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1752 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 ROLLBACK;
1757 retcode:=2;
1758 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
1759 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') ;
1760 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
1761
1762 END prgp_init_prs_sched;
1763
1764
1765 PROCEDURE prgp_init_scheduling AS
1766 /**********************************************************
1767 Created By : kmunuswa
1768
1769 Date Created By : 29-AUG-2000
1770
1771 Purpose : To Initiate the scheduling.
1772
1773 Know limitations, enhancements or remarks
1774
1775 Change History
1776
1777 Who When What
1778
1779 (reverse chronological order - newest change first)
1780 ***************************************************************/
1781 BEGIN
1782
1783 NULL;
1784
1785 END prgp_init_scheduling;
1786
1787 FUNCTION prgp_get_schd_status (
1788 p_uoo_id IN NUMBER,
1789 p_usec_id IN NUMBER,
1790 p_message_name OUT NOCOPY VARCHAR2 )
1791 RETURN BOOLEAN AS
1792 /**********************************************************
1793 Created By : Saperuma
1794
1795 Date Created By : 29-AUG-2000
1796
1797 Purpose : For scheduling the Unit Section occurrences
1798
1799 Know limitations, enhancements or remarks
1800
1801 Change History
1802
1803 Who When What
1804
1805 (reverse chronological order - newest change first)
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
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);
1821 BEGIN
1822 -- check the uoo id
1823 IF p_uoo_id IS NULL THEN
1824 -- return the error message parameter cannot be null
1825 p_message_name := 'IGS_GE_PARAMETER_NOT_NULL';
1826 RETURN FALSE;
1827 ELSIF p_usec_id IS NULL THEN
1828 -- passing only the uoo id to check the scheduling status
1829 OPEN c_get_schd_status_uoo;
1830 FETCH c_get_schd_status_uoo INTO l_dummy;
1831 IF (c_get_schd_status_uoo%NOTFOUND) THEN
1832 CLOSE c_get_schd_status_uoo;
1833 p_message_name := NULL;
1834 RETURN FALSE;
1835 ELSE
1836 CLOSE c_get_schd_status_uoo;
1837 p_message_name := NULL;
1838 RETURN TRUE;
1839 END IF;
1840 ELSE
1841 -- passing uoo id,unit_section_occurence_id to check the scheduling status
1842 OPEN c_get_schd_status_usec;
1843 FETCH c_get_schd_status_usec INTO l_dummy;
1844 IF (c_get_schd_status_usec%NOTFOUND) THEN
1845 CLOSE c_get_schd_status_usec;
1846 p_message_name := NULL;
1847 RETURN FALSE;
1848 ELSE
1849 CLOSE c_get_schd_status_usec;
1850 p_message_name := NULL;
1851 RETURN TRUE;
1852 END IF;
1853 END IF;
1854
1855 EXCEPTION
1856 WHEN OTHERS THEN
1857 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1858 IGS_GE_MSG_STACK.ADD;
1859 App_Exception.Raise_Exception;
1860 END prgp_get_schd_status;
1861
1862 PROCEDURE prgp_schd_purge_data(
1863 errbuf OUT NOCOPY VARCHAR2,
1864 retcode OUT NOCOPY NUMBER,
1865 p_teach_prd IN VARCHAR2,
1866 p_org_id IN NUMBER)
1867 AS
1868 /**********************************************************
1869 Created By : Saperuma
1870
1871 Date Created By : 29-AUG-2000
1872
1873 Purpose : For scheduling the Unit Section occurrences
1874
1875 Know limitations, enhancements or remarks
1876
1877 Change History
1878
1879 Who When What
1880 jbegum 07-Apr-2003 Bug #2833850
1881 Made changes to this procedure as part of PSP Scheduling Interface Enhancements TD.
1882 schodava 6-Jun-2001 SI DLD
1883 schodava 30-Jan-2001 Modified Scheduling DLD Changes
1884
1885 (reverse chronological order - newest change first)
1886 ***************************************************************/
1887
1888 CURSOR cur_pat IS
1889 SELECT DISTINCT calendar_type,sequence_number
1890 FROM igs_ps_sch_pat_int;
1891
1892 BEGIN
1893
1894 -- set the multi org id
1895 igs_ge_gen_003.set_org_id (p_org_id);
1896
1897 IF p_teach_prd IS NOT NULL THEN
1898 purge_schd_record(RTRIM(SUBSTR(p_teach_prd, 101, 10)),TO_NUMBER(RTRIM(SUBSTR(p_teach_prd,112,6))));
1899 ELSE
1900 FOR cur_pat_rec IN cur_pat LOOP
1901 purge_schd_record(cur_pat_rec.calendar_type,cur_pat_rec.sequence_number);
1902 END LOOP;
1903 END IF;
1904
1905 retcode :=0;
1906
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 retcode :=2;
1910 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
1911 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1912 ROLLBACK;
1913 END prgp_schd_purge_data;
1914
1915 FUNCTION prgp_upd_usec_dtls (
1916 p_uoo_id IN NUMBER,
1917 p_location_cd IN VARCHAR2 ,
1918 p_usec_status IN VARCHAR2 ,
1919 p_max_enrollments IN NUMBER ,
1920 p_override_enrollment_max IN NUMBER ,
1921 p_enrollment_expected IN NUMBER ,
1922 p_request_id OUT NOCOPY NUMBER,
1923 p_message_name OUT NOCOPY VARCHAR2
1924 ) RETURN BOOLEAN AS
1925 /*************************************************************
1926 Created By : Sreenivas.Bonam
1927 Date Created : 2000/08/28
1928 Purpose : To update the Scheduling Status of all unit section occurences
1929 belonging to the unit section passed whenever there is
1930 a change in a Unit Section's location/maximum enrollments/Unit Section Status
1931 If a Unit Section is closed then also submit a request to the scheduler to cancel
1932 the scheduled information for that Unit Section.
1933 Know limitations, enhancements or remarks
1934 Change History
1935 Who When What
1936 smvk 11-Mar-2003 Bug # 2831065. Modified the cursor c_usec_occurs to update schedule status of appropriate USO.
1937 (i.e) Based on earlier and latest schedule status of USO.
1938 ssawhney 13-Nov-2000 chr(0)as the terminator in fnd_request.submit_request
1939 (reverse chronological order - newest change first)
1940 ***************************************************************/
1941
1942 CURSOR c_loc_modified_chk ( p_uoo_id IN NUMBER ) IS -- To check if location code has undergone modification
1943 SELECT location_cd
1944 FROM igs_ps_unit_ofr_opt
1945 WHERE uoo_id = p_uoo_id;
1946
1947 CURSOR c_maxenrl_modified_chk ( p_uoo_id IN NUMBER ) IS -- To check if max. enrollments has undergone modification
1948 SELECT enrollment_maximum ,
1949 override_enrollment_max,
1950 enrollment_expected
1951 FROM igs_ps_usec_lim_wlst
1952 WHERE uoo_id = p_uoo_id;
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
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;
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
1978
1979 IF schd_stat ='USER_CANCEL' THEN
1980 IF c_usec_occurs_rec.schedule_status = 'PROCESSING' THEN
1981 l_c_schedule_status := 'PROCESSING';
1982 ELSE
1983 l_c_schedule_status := schd_stat;
1984 END IF;
1985 l_c_cancel := 'Y';
1986 ELSE
1987 l_c_schedule_status := schd_stat;
1988 l_c_cancel := 'N';
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,
1998 x_tuesday => c_usec_occurs_rec.tuesday,
1999 x_wednesday => c_usec_occurs_rec.wednesday,
2000 x_thursday => c_usec_occurs_rec.thursday,
2001 x_friday => c_usec_occurs_rec.friday,
2002 x_saturday => c_usec_occurs_rec.saturday,
2003 x_sunday => c_usec_occurs_rec.sunday,
2004 x_start_time => c_usec_occurs_rec.start_time,
2005 x_end_time => c_usec_occurs_rec.end_time,
2006 x_building_code => c_usec_occurs_rec.building_code,
2007 x_room_code => c_usec_occurs_rec.room_code,
2008 x_schedule_status => l_c_schedule_status,
2009 x_status_last_updated => c_usec_occurs_rec.status_last_updated,
2010 x_instructor_id => c_usec_occurs_rec.instructor_id,
2011 X_attribute_category => c_usec_occurs_rec.attribute_category,
2012 X_attribute1 => c_usec_occurs_rec.attribute1,
2013 X_attribute2 => c_usec_occurs_rec.attribute2,
2014 X_attribute3 => c_usec_occurs_rec.attribute3,
2015 X_attribute4 => c_usec_occurs_rec.attribute4,
2016 X_attribute5 => c_usec_occurs_rec.attribute5,
2017 X_attribute6 => c_usec_occurs_rec.attribute6,
2018 X_attribute7 => c_usec_occurs_rec.attribute7,
2019 X_attribute8 => c_usec_occurs_rec.attribute8,
2020 X_attribute9 => c_usec_occurs_rec.attribute9,
2021 X_attribute10 => c_usec_occurs_rec.attribute10,
2022 X_attribute11 => c_usec_occurs_rec.attribute11,
2023 X_attribute12 => c_usec_occurs_rec.attribute12,
2024 X_attribute13 => c_usec_occurs_rec.attribute13,
2025 X_attribute14 => c_usec_occurs_rec.attribute14,
2026 X_attribute15 => c_usec_occurs_rec.attribute15,
2027 X_attribute16 => c_usec_occurs_rec.attribute16,
2028 X_attribute17 => c_usec_occurs_rec.attribute17,
2029 X_attribute18 => c_usec_occurs_rec.attribute18,
2030 X_attribute19 => c_usec_occurs_rec.attribute19,
2031 X_attribute20 => c_usec_occurs_rec.attribute20,
2032 x_error_text => c_usec_occurs_rec.error_text,
2033 x_mode => 'R',
2034 X_start_date => c_usec_occurs_rec.start_date,
2035 X_end_date => c_usec_occurs_rec.end_date,
2036 X_to_be_announced => c_usec_occurs_rec.to_be_announced,
2037 x_dedicated_building_code => c_usec_occurs_rec.dedicated_building_code,
2038 x_dedicated_room_code => c_usec_occurs_rec.dedicated_room_code,
2039 x_preferred_building_code => c_usec_occurs_rec.preferred_building_code,
2040 x_preferred_room_code => c_usec_occurs_rec.preferred_room_code,
2041 x_inst_notify_ind => c_usec_occurs_rec.inst_notify_ind,
2042 x_notify_status => c_usec_occurs_rec.notify_status,
2043 x_preferred_region_code => c_usec_occurs_rec.preferred_region_code,
2044 x_no_set_day_ind => c_usec_occurs_rec.no_set_day_ind,
2045 x_cancel_flag => l_c_cancel,
2046 x_occurrence_identifier => c_usec_occurs_rec.occurrence_identifier,
2047 x_abort_flag => c_usec_occurs_rec.abort_flag
2048 );
2049 END IF;
2050 END LOOP;
2051
2052 EXCEPTION
2053 WHEN OTHERS THEN
2054 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2055 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls:upd_usec_occurs_schd_status');
2056 IGS_GE_MSG_STACK.ADD;
2057 APP_EXCEPTION.RAISE_EXCEPTION;
2058
2059 END upd_usec_occurs_schd_status;
2060
2061 BEGIN
2062
2063 IF p_uoo_id IS NULL THEN
2064 p_message_name := 'IGS_GE_PARAMETER_NOT_NULL';
2065 RETURN FALSE;
2066 ELSIF (p_location_cd IS NOT NULL) AND
2067 (
2068 (p_usec_status IS NOT NULL) OR
2069 (
2070 p_max_enrollments IS NOT NULL OR
2071 p_override_enrollment_max IS NOT NULL OR
2072 p_enrollment_expected IS NOT NULL
2073 )
2074 ) THEN
2075 p_message_name := 'IGS_GE_NOT_ENGH_PARAM';
2076 RETURN FALSE;
2077 ELSIF (p_max_enrollments IS NOT NULL OR
2078 p_override_enrollment_max IS NOT NULL OR
2079 p_enrollment_expected IS NOT NULL ) AND (p_location_cd IS NOT NULL OR
2080 p_usec_status IS NOT NULL
2081 ) THEN
2082 p_message_name := 'IGS_PS_INVALID_PARAM';
2083 RETURN FALSE;
2084 ELSIF (p_usec_status IS NOT NULL) AND
2085 (
2086 (p_location_cd IS NOT NULL) OR
2087 (
2088 p_max_enrollments IS NOT NULL OR
2089 p_override_enrollment_max IS NOT NULL OR
2090 p_enrollment_expected IS NOT NULL
2091 )
2092 )THEN
2093 p_message_name := 'IGS_GE_NOT_ENGH_PARAM';
2094 RETURN FALSE;
2095
2096 END IF;
2097
2098 -- Populate org id.
2099 l_org_id := igs_ge_gen_003.get_org_id;
2100
2101 IF p_usec_status ='CANCELLED' THEN
2102
2103 upd_usec_occurs_schd_status(p_uoo_id,'USER_CANCEL');
2104
2105 BEGIN
2106
2107 -- Enh Bug#2833850
2108 -- Passing 'N' to argument6.This argument maps to the delete transaction completed record field of concurrent manager.
2109
2110 p_request_id := FND_REQUEST.SUBMIT_REQUEST (
2111 application => 'IGS', program => 'IGSPSJ05', description => 'Initiate Scheduling of Units Section Occurrences', start_time => NULL,
2112 sub_request => FALSE, argument1 => NULL, argument2 => p_uoo_id, argument3 => NULL, argument4 => 'CANCEL', argument5 => l_org_id,
2113 argument6 => chr(0), argument7 => '', argument8 => '', argument9 => '', argument10 => '', argument11 => '', argument12 => '',
2114 argument13 => '', argument14 => '', argument15 => '', argument16 => '', argument17 => '', argument18 => '', argument19 => '',
2115 argument20 => '', argument21 => '', argument22 => '', argument23 => '', argument24 => '', argument25 => '', argument26 => '',
2116 argument27 => '', argument28 => '', argument29 => '', argument30 => '', argument31 => '', argument32 => '', argument33 => '',
2117 argument34 => '', argument35 => '', argument36 => '', argument37 => '', argument38 => '', argument39 => '', argument40 => '',
2118 argument41 => '', argument42 => '', argument43 => '', argument44 => '', argument45 => '', argument46 => '', argument47 => '',
2119 argument48 => '', argument49 => '', argument50 => '', argument51 => '', argument52 => '', argument53 => '', argument54 => '',
2120 argument55 => '', argument56 => '', argument57 => '', argument58 => '', argument59 => '', argument60 => '', argument61 => '',
2121 argument62 => '', argument63 => '', argument64 => '', argument65 => '', argument66 => '', argument67 => '', argument68 => '',
2122 argument69 => '', argument70 => '', argument71 => '', argument72 => '', argument73 => '', argument74 => '', argument75 => '',
2123 argument76 => '', argument77 => '', argument78 => '', argument79 => '', argument80 => '', argument81 => '', argument82 => '',
2124 argument83 => '', argument84 => '', argument85 => '', argument86 => '', argument87 => '', argument88 => '', argument89 => '',
2125 argument90 => '', argument91 => '', argument92 => '', argument93 => '', argument94 => '', argument95 => '', argument96 => '',
2126 argument97 => '', argument98 => '', argument99 => '', argument100 => '');
2127 IF p_request_id = 0 THEN
2128 FND_MESSAGE.RETRIEVE(p_message_name);
2129 RETURN FALSE;
2130 END IF;
2131 p_message_name := 'IGS_PS_SCST_CAN';
2132 RETURN TRUE;
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2136 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls');
2137 igs_ge_msg_stack.add;
2138 APP_EXCEPTION.RAISE_EXCEPTION;
2139 END;
2140 --
2141 -- the following code is added as part of Unit Section Enrollment Information
2142 -- build.
2143 -- the code triggers the workflow to raise business events
2144 --
2145 DECLARE
2146 l_message VARCHAR2(30);
2147 BEGIN
2148 IF FND_PROFILE.VALUE('IGS_WF_ENABLE') = 'Y' THEN
2149 --
2150 -- raise business event only when workflow is installed
2151 --
2152 igs_ps_wf_event_pkg.wf_create_event(p_uoo_id => p_uoo_id,
2153 p_usec_occur_id => NULL,
2154 p_event_type => 'CNCL',
2155 p_message => l_message);
2156 IF l_message IS NOT NULL THEN
2157 FND_MESSAGE.SET_NAME('IGS',l_message);
2158 IGS_GE_MSG_STACK.ADD;
2159 APP_EXCEPTION.RAISE_EXCEPTION;
2160 END IF;
2161 END IF;
2162 END;
2163 --
2164 -- end of code as per theUnit Section Enrollment build
2165 --
2166 ELSIF p_location_cd IS NOT NULL THEN
2167 DECLARE
2168 lv_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE;
2169 BEGIN
2170 OPEN c_loc_modified_chk ( p_uoo_id );
2171 FETCH c_loc_modified_chk INTO lv_location_cd;
2172 IF nvl(p_location_cd,-999) <> nvl(lv_location_cd,-999) THEN -- Location has undergone modification
2173 upd_usec_occurs_schd_status(p_uoo_id,'USER_UPDATE');
2174 END IF;
2175 p_message_name := 'IGS_PS_SCST_UPD';
2176 RETURN TRUE;
2177 EXCEPTION
2178 WHEN OTHERS THEN
2179 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2180 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls');
2181 igs_ge_msg_stack.add;
2182 APP_EXCEPTION.RAISE_EXCEPTION;
2183 END;
2184 ELSIF ( p_max_enrollments IS NOT NULL or p_override_enrollment_max IS NOT NULL or p_enrollment_expected IS NOT NULL) THEN -- Maximum Enrollment case
2185 BEGIN
2186 OPEN c_maxenrl_modified_chk( p_uoo_id );
2187 FETCH c_maxenrl_modified_chk INTO lv_enrol_max;
2188 IF( ( nvl(lv_enrol_max.enrollment_maximum,-999) <> nvl(p_max_enrollments,-999) )
2189 OR
2190 ( nvl(lv_enrol_max.override_enrollment_max,-999) <> nvl(p_override_enrollment_max,-999) )
2191 OR
2192 ( nvl(lv_enrol_max.enrollment_expected,-999) <> nvl(p_enrollment_expected,-999) ) ) THEN -- Max. Enrollments has undergone modification
2193 upd_usec_occurs_schd_status(p_uoo_id,'USER_UPDATE');
2194 END IF;
2195 p_message_name := 'IGS_PS_SCST_UPD';
2196 RETURN TRUE;
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2200 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls');
2201 igs_ge_msg_stack.add;
2202 APP_EXCEPTION.RAISE_EXCEPTION;
2203 END;
2204 END IF;
2205 EXCEPTION
2206 WHEN OTHERS THEN
2207 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2208 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls');
2209 igs_ge_msg_stack.add;
2210 APP_EXCEPTION.RAISE_EXCEPTION;
2211
2212
2213 END prgp_upd_usec_dtls;
2214
2215 PROCEDURE prgp_write_ref_file (
2216 errbuf OUT NOCOPY VARCHAR2,
2217 retcode OUT NOCOPY NUMBER,
2218 p_column_sep IN VARCHAR2,
2219 p_org_id IN NUMBER ) AS
2220 /**********************************************************
2221 Created By : rareddy
2222
2223 Date Created By : 29-AUG-2000
2224
2225 Purpose : EXporting Table Data in to Flat Files
2226
2227 Know limitations, enhancements or remarks
2228
2229 Change History
2230
2231 Who When What
2232 sarakshi 02-Feb-2006 Bug#4960517, replaced the profile IGS_PS_EXP_DIR_PATH to UTL_FILE_OUT
2233 jbegum 04-Apr-2003 As per bug#2833850 added code to export preferred region code information
2234 to flat file.
2235 smvk 07-May-2002 Removed Hardcoded filename and filename are choosed from lookup_values to
2236 overcome translation issues as per the Bug # 2401826
2237 smvk 09-May-2002 Created a private procedure prof_value_pres_pvt to check the value of
2238 profile 'IGS_PS_EXP_DIR_PATH' is matches with the value of v$paramtere(table)
2239 whose name is 'utl_file_dir' and Output file names are modified to have
2240 request id with them as per Bug # 2343189
2241 (reverse chronological order - newest change first)
2242 ***************************************************************/
2243
2244 l_message VARCHAR2(30);
2245 l_output_message VARCHAR2(80);
2246 l_column_sep VARCHAR2(30);
2247 invalid EXCEPTION;
2248 valid EXCEPTION;
2249 dirnotfound EXCEPTION;
2250 l_check CHAR;
2251 l_handler_room UTL_FILE.FILE_TYPE ;
2252 l_handler_building UTL_FILE.FILE_TYPE ;
2253 l_handler_location UTL_FILE.FILE_TYPE ;
2254 l_handler_region UTL_FILE.FILE_TYPE ; -- Added local variable as part of bug#2833850
2255 l_handler_check BOOLEAN;
2256 l_path_var VARCHAR2(80);
2257 l_req_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID(); -- returns the concurrent request id;
2258 l_prof_val_pres VARCHAR2(1); -- to check profile value is present in utl_file_dir of v$parameter
2259
2260 -- Cursor to get room information to be exported to flat file
2261 CURSOR c_room IS
2262 SELECT room_id,building_id,room_cd,description,primary_use_cd,capacity,closed_ind
2263 FROM igs_ad_room
2264 ORDER BY 1;
2265
2266 -- Cursor to get building information to be exported to flat file
2267 CURSOR c_building IS
2268 SELECT building_id,location_cd,building_cd,description,closed_ind
2269 FROM igs_ad_building
2270 ORDER BY 1;
2271
2272 -- Cursor to get location information to be exported to flat file
2273 CURSOR c_location IS
2274 SELECT location_cd,description,location_type,mail_dlvry_wrk_days,coord_person_id,closed_ind
2275 FROM igs_ad_location
2276 ORDER BY 1;
2277
2278 -- Cursor added as part of bug#2833850
2279 -- Cursor to get region code information to be exported to flat file
2280 CURSOR c_region IS
2281 SELECT lookup_code,meaning,description,tag,start_date_active,end_date_active,enabled_flag,closed_ind
2282 FROM igs_lookup_values
2283 WHERE lookup_type = 'IGS_OR_LOC_REGION'
2284 AND enabled_flag = 'Y'
2285 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,TRUNC(SYSDATE))) AND TRUNC(NVL(end_date_active,TRUNC(SYSDATE)))
2286 ORDER BY lookup_code;
2287
2288 -- cursors for getting the filenames to export data, Added as a part of Bug # 2401826
2289
2290 -- Cursor to select the filename to export room information
2291 CURSOR c_room_fname IS
2292 SELECT meaning
2293 FROM IGS_LOOKUP_VALUES
2294 WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
2295 LOOKUP_CODE='IGS_PS_SCH_ROOM' ;
2296
2297 -- Cursor to select the filename to export building information
2298 CURSOR c_building_fname IS
2299 SELECT meaning
2300 FROM IGS_LOOKUP_VALUES
2301 WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
2302 LOOKUP_CODE='IGS_PS_SCH_BLDG';
2303
2304 -- Cursor to select the filename to export location information
2305 CURSOR c_location_fname IS
2306 SELECT meaning
2307 FROM IGS_LOOKUP_VALUES
2308 WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
2309 LOOKUP_CODE='IGS_PS_SCH_LOC';
2310
2311 -- Cursor added as part of bug#2833850
2312 -- Cursor to select the filename to export preferred region information
2313 CURSOR c_reg_fname IS
2314 SELECT meaning
2315 FROM IGS_LOOKUP_VALUES
2316 WHERE LOOKUP_TYPE='SCHED_EXP_FILES' AND
2317 LOOKUP_CODE='IGS_PS_SCH_PRF_REG';
2318
2319 -- local variables to hold the export flat file name, Added as a part of Bug # 2401826
2320
2321 rec_room_fname c_room_fname%ROWTYPE;
2322 rec_building_fname c_building_fname%ROWTYPE;
2323 rec_location_fname c_location_fname%ROWTYPE;
2324
2325 -- local variable added as part of bug#2833850 to hold the export flat file name for preferred region
2326 rec_reg_fname c_reg_fname%ROWTYPE;
2327
2328 -- Cursor added as part of bug#2833850
2329 CURSOR c_loc_reg_map(cp_c_loc_cd IN igs_or_loc_region.location_cd%TYPE) IS
2330 SELECT region_cd
2331 FROM igs_or_loc_region
2332 WHERE location_cd = cp_c_loc_cd;
2333
2334 procedure prof_value_pres_pvt(p_isthere OUT NOCOPY varchar2) AS
2335 /**********************************************************
2336 Created By : smvk
2337
2338 Date Created By : 08-MAY-2002
2339
2340 Purpose : Private procedure to check the value of profile 'IGS_PS_EXP_DIR_PATH'
2341 (output dir for export data file) matches with the value in v$parameter
2342 for the name utl_file_dir. Called within prgp_write_ref_file procedure only.
2343
2344 Know limitations, enhancements or remarks
2345
2346 Change History
2347
2348 Who When What
2349
2350 (reverse chronological order - newest change first)
2351 ***************************************************************/
2352
2353 l_start_str_index NUMBER := 1;
2354 l_end_comma_index NUMBER := 1;
2355 l_start_comma_index NUMBER := 1;
2356 l_dbvalue V$PARAMETER.VALUE%TYPE;
2357 l_temp V$PARAMETER.VALUE%TYPE;
2358 l_fndvalue VARCHAR2(80);
2359
2360 CURSOR c_value IS
2361 SELECT VALUE
2362 FROM V$PARAMETER
2363 WHERE NAME='utl_file_dir';
2364
2365 BEGIN
2366 p_isthere := 'N';
2367 l_fndvalue := LTRIM(RTRIM(FND_PROFILE.VALUE('UTL_FILE_OUT')));
2368 OPEN c_value ;
2369 FETCH c_value INTO l_dbvalue ;
2370 IF c_value%FOUND AND l_dbvalue IS NOT NULL THEN
2371 l_dbvalue:= LTRIM(RTRIM(l_dbvalue));
2372 LOOP
2373 SELECT INSTR(l_dbvalue,l_fndvalue,l_end_comma_index) INTO l_start_str_index FROM DUAL;
2374 IF l_start_str_index = 0 THEN
2375 p_isthere := 'N';
2376 return;
2377 END IF;
2378 SELECT INSTR(SUBSTR(l_dbvalue,1,l_start_str_index),',',-1)+1
2379 INTO l_start_comma_index
2380 FROM DUAL;
2381 SELECT DECODE(
2382 INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1,
2383 INSTR(l_dbvalue,',',l_start_str_index))
2384 INTO l_end_comma_index
2385 FROM DUAL;
2386 SELECT LTRIM(RTRIM(SUBSTR(l_dbvalue,l_start_comma_index, l_end_comma_index-l_start_comma_index)))
2387 INTO l_temp
2388 FROM DUAL;
2389 IF l_temp = l_fndvalue THEN
2390 p_isthere := 'Y';
2391 return;
2392 END IF;
2393 END LOOP;
2394 ELSE
2395 p_isthere :='N';
2396 END IF;
2397 CLOSE c_value;
2398 END prof_value_pres_pvt;
2399
2400 BEGIN
2401 --adding the following code to overcome file.sql.35 warning.
2402 l_path_var := FND_PROFILE.VALUE('UTL_FILE_OUT');
2403 l_prof_val_pres := 'N'; -- to check profile value is present in utl_file_dir of v$parameter
2404
2405 -- set the multi org id
2406 igs_ge_gen_003.set_org_id (p_org_id);
2407
2408
2409 l_message := NULL;
2410 retcode := 0;
2411 l_column_sep := NVL(p_column_sep,'###');
2412
2413 IF (l_path_var IS NULL) THEN
2414 l_message :='IGS_GE_DIR_PRF_NOT_SET';
2415 RAISE invalid;
2416 END IF;
2417
2418 -- calling private procedure
2419 -- to check the value present in profile matches with that value of utl_file_dir in v$parameter
2420 prof_value_pres_pvt(l_prof_val_pres);
2421 IF l_prof_val_pres = 'N' THEN
2422 l_message :='IGS_PS_OUT_DIR_NOT_FOUND';
2423 RAISE dirnotfound;
2424 END IF;
2425
2426 -- code added to get the filenames to export data, Added as a part of Bug # 2401826
2427
2428 OPEN c_room_fname;
2429 FETCH c_room_fname INTO rec_room_fname;
2430
2431 OPEN c_building_fname ;
2432 FETCH c_building_fname INTO rec_building_fname;
2433
2434 OPEN c_location_fname;
2435 FETCH c_location_fname INTO rec_location_fname;
2436
2437 CLOSE c_room_fname;
2438 CLOSE c_building_fname;
2439 CLOSE c_location_fname;
2440
2441 -- Added code as part of bug#2833850 to get flat filename to export preferred region information
2442 OPEN c_reg_fname;
2443 FETCH c_reg_fname INTO rec_reg_fname;
2444 CLOSE c_reg_fname;
2445
2446
2447 -- WRITING from TABLES
2448
2449 BEGIN
2450 l_handler_room := UTL_FILE.FOPEN(l_path_var, LTRIM(RTRIM(rec_room_fname.meaning)) || l_req_id, 'w');
2451 l_handler_check := UTL_FILE.IS_OPEN(l_handler_room);
2452 IF (l_handler_check = FALSE ) THEN
2453 RAISE invalid;
2454 END IF;
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 l_message :='IGS_GE_DIR_FOPEN_ERR';
2458 RAISE invalid;
2459 END;
2460
2461 FOR cur_temp IN c_room LOOP
2462 UTL_FILE.PUT_LINE(l_handler_room, cur_temp.room_id|| l_column_sep ||
2463 cur_temp.building_id|| l_column_sep ||cur_temp.room_cd|| l_column_sep ||
2464 cur_temp.description|| l_column_sep ||cur_temp.primary_use_cd||
2465 l_column_sep ||cur_temp.capacity|| l_column_sep ||cur_temp.closed_ind);
2466 END LOOP;
2467 UTL_FILE.FCLOSE(l_handler_room);
2468
2469 BEGIN
2470 l_handler_building := UTL_FILE.FOPEN(l_path_var, LTRIM(RTRIM(rec_building_fname.meaning)) || l_req_id, 'w');
2471 l_handler_check := UTL_FILE.IS_OPEN(l_handler_building);
2472 IF (l_handler_check = FALSE ) THEN
2473 RAISE invalid;
2474 END IF;
2475 EXCEPTION
2476 WHEN OTHERS THEN
2477 l_message :='IGS_GE_DIR_FOPEN_ERR';
2478 RAISE invalid;
2479 END;
2480
2481 FOR cur_temp IN c_building LOOP
2482 UTL_FILE.PUT_LINE(l_handler_building, cur_temp.building_id|| l_column_sep ||
2483 cur_temp.location_cd|| l_column_sep ||cur_temp.building_cd|| l_column_sep ||
2484 cur_temp.description|| l_column_sep ||cur_temp.closed_ind);
2485 END LOOP;
2486 UTL_FILE.FCLOSE(l_handler_building);
2487
2488 BEGIN
2489 l_handler_location := UTL_FILE.FOPEN(l_path_var, LTRIM(RTRIM(rec_location_fname.meaning))|| l_req_id, 'w');
2490 l_handler_check := UTL_FILE.IS_OPEN(l_handler_location);
2491 IF (l_handler_check = FALSE ) THEN
2492 RAISE invalid;
2493 END IF;
2494 EXCEPTION
2495 WHEN OTHERS THEN
2496 l_message :='IGS_GE_DIR_FOPEN_ERR';
2497 RAISE invalid;
2498 END;
2499
2500 -- As part of bug#2833850 added the cursor FOR loop cur_map
2501 -- This was done to modify the location flat file structure , so that it would show the region codes
2502 -- attached to a location code
2503
2504 FOR cur_temp IN c_location LOOP
2505 UTL_FILE.PUT(l_handler_location,cur_temp.location_cd|| l_column_sep ||
2506 cur_temp.description|| l_column_sep ||cur_temp.location_type || l_column_sep ||
2507 cur_temp.mail_dlvry_wrk_days|| l_column_sep ||cur_temp.coord_person_id
2508 || l_column_sep ||cur_temp.closed_ind);
2509 FOR cur_map IN c_loc_reg_map(cur_temp.location_cd) LOOP
2510 UTL_FILE.PUT(l_handler_location,l_column_sep ||cur_map.region_cd);
2511 END LOOP;
2512 UTL_FILE.NEW_LINE(l_handler_location,1);
2513 END LOOP;
2514
2515 UTL_FILE.FCLOSE(l_handler_location);
2516
2517
2518 -- Added following code as part of bug#2833850 to write preferred region information to the flat file
2519
2520 BEGIN
2521 l_handler_region := UTL_FILE.FOPEN(l_path_var, LTRIM(RTRIM(rec_reg_fname.meaning))|| l_req_id, 'w');
2522 l_handler_check := UTL_FILE.IS_OPEN(l_handler_region);
2523 IF (l_handler_check = FALSE ) THEN
2524 RAISE invalid;
2525 END IF;
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 l_message :='IGS_GE_DIR_FOPEN_ERR';
2529 RAISE invalid;
2530 END;
2531
2532 FOR cur_temp IN c_region LOOP
2533 UTL_FILE.PUT_LINE(l_handler_region, cur_temp.lookup_code|| l_column_sep ||
2534 cur_temp.meaning|| l_column_sep ||cur_temp.description|| l_column_sep ||
2535 cur_temp.tag|| l_column_sep ||cur_temp.start_date_active|| l_column_sep ||
2536 cur_temp.end_date_active|| l_column_sep ||cur_temp.enabled_flag|| l_column_sep ||cur_temp.closed_ind);
2537 END LOOP;
2538
2539 UTL_FILE.FCLOSE(l_handler_region);
2540
2541 l_message :='IGS_PS_SCH_EXP_SUCCESS';
2542 RAISE valid;
2543
2544 EXCEPTION
2545
2546 WHEN VALID THEN
2547 retcode:=0;
2548 errbuf:=FND_MESSAGE.GET_STRING('IGS',l_message);
2549 WHEN INVALID THEN
2550 retcode:=2;
2551 errbuf :=FND_MESSAGE.GET_STRING('IGS',l_message);
2552 WHEN dirnotfound THEN
2553 retcode:=2;
2554 errbuf :=FND_MESSAGE.GET_STRING('IGS',l_message);
2555 WHEN OTHERS THEN
2556 retcode:=2;
2557 errbuf:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2558 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2559
2560 END prgp_write_ref_file;
2561
2562 PROCEDURE prgp_get_schd_records(
2563 errbuf OUT NOCOPY varchar2,
2564 retcode OUT NOCOPY number ,
2565 p_org_id IN NUMBER
2566 ) AS
2567 /**********************************************************
2568 Created By : John Victor Deekollu
2569
2570 Date Created By : 29-AUG-2000
2571
2572 Purpose : Gets the Scheduled Records from Interface Table
2573
2574 Know limitations, enhancements or remarks
2575
2576 Change History
2577
2578 Who When What
2579 sommukhe 27-APR-2006 Bug#5122473,Modified the cursor check_ovrd and the code respectively to include Date override check so that the Get Scheduled interface job considers Date Occurrence Override during import.
2580 sarakshi 12-Jan-2006 bug#4926548, replaced the cursor c_table with the pl-sql table . Also Modified cursor c_ipsuo and c_print_uso such that it uses its base table.
2581 sarakshi 16-Feb-2004 Bug#3431844, added owner filter in the cursor c_table and modified its usage accordingly
2582 smvk 29-Jun-2003 Bug # 3060089. Modified the procedure update_info to display the message 'IGS_PS_SCH_TBA_USO_NSD_USEC'.
2583 smvk 13-May-2003 Created a local procedures update_info and local function get_location_description for code optimization
2584 and coded the validation mentioned PSP Scheduling inteface enhancements TD.Enh Bug #2833850.
2585 smvk 26-Jun-2002 In interface table, when there is no unit section occurrence exist in valid state
2586 to populate in production table, proper message should log in the log file.
2587 as per the Bug # 2427725
2588 schodava 30-Jan-2001 Modified Scheduling DLD Changes
2589 bayadav 28-May-2001 SCheduling Interface DLD Changes
2590 smvk 31-Dec-2002 Bug # 2710978. Collecting the statistics of the interface table as per standards.
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;
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;
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
2618 l_d_uso_end_date igs_ps_sch_int_all.unit_section_end_date%TYPE; -- Added as a part of Enh Bug #2833850
2619 l_c_t_cal_type igs_ca_inst_all.cal_type%TYPE;
2620 l_n_t_seq_num igs_ca_inst_all.sequence_number%TYPE;
2621 l_n_t_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2622 l_b_print_cal BOOLEAN;
2623 l_b_print_uoo BOOLEAN;
2624
2625 -- is there any schduled records exists needs to be transfer to production table as per the Bug # 2427725
2626 l_valid_rec_for_prod BOOLEAN := FALSE;
2627
2628 CURSOR c_get_usec_inter IS
2629 SELECT sui.*
2630 FROM igs_ps_sch_usec_int_all sui
2631 WHERE sui.import_done_flag = 'N'
2632 FOR UPDATE NOWAIT
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'
2642 AND ipsi.import_done_flag = 'N'
2643 AND ipsi.int_usec_id = cp_int_usec_id
2644 FOR UPDATE NOWAIT;
2645
2646 CURSOR c_get_originator(p_transaction_id NUMBER) IS
2647 SELECT originator
2648 FROM igs_ps_sch_hdr_int_ALL
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
2658 TYPE tabnames IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2659 tablenames_tbl tabnames;
2660
2661 l_ipsuo c_ipsuo%ROWTYPE;
2662 l_c_status VARCHAR2(1);
2663 l_c_industry VARCHAR2(1);
2664 l_c_schema VARCHAR2(30);
2665 l_b_return BOOLEAN;
2666
2667 CURSOR c_date (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
2668 SELECT a.unit_section_start_date us_start_date,
2669 a.unit_section_end_date us_end_date,
2670 b.start_dt tp_start_date,
2671 b.end_dt tp_end_date
2672 FROM igs_ps_unit_ofr_opt_all a,
2673 igs_ca_inst_all b
2674 WHERE a.uoo_id = cp_n_uoo_id
2675 AND a.cal_type = b.cal_type
2676 AND a.ci_sequence_number = b.sequence_number;
2677
2678 CURSOR c_bldg_exists (cp_n_building_id igs_ad_building_all.building_id%TYPE) IS
2679 SELECT 'x'
2680 FROM igs_ad_building_all
2681 WHERE building_id = cp_n_building_id
2682 AND ROWNUM < 2 ;
2683
2684 CURSOR c_room_exists (cp_n_room_id igs_ad_room_all.room_id%TYPE) IS
2685 SELECT 'x'
2686 FROM igs_ad_room_all
2687 WHERE room_id = cp_n_room_id
2688 AND ROWNUM < 2 ;
2689
2690 CURSOR c_bldg_room_exists (cp_n_building_id igs_ad_building_all.building_id%TYPE,
2691 cp_n_room_id igs_ad_room_all.room_id%TYPE) IS
2692 SELECT 'x'
2693 FROM igs_ad_room_all
2694 WHERE building_id = cp_n_building_id
2695 AND room_id = cp_n_room_id
2696 AND ROWNUM < 2 ;
2697
2698 l_c_exists VARCHAR2(1);
2699 rec_date c_date%ROWTYPE;
2700
2701
2702 CURSOR check_ovrd IS
2703 SELECT day_ovrd_flag, time_ovrd_flag, scheduled_bld_ovrd_flag, scheduled_room_ovrd_flag,date_ovrd_flag
2704 FROM igs_ps_sch_ocr_cfig;
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,
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,
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
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
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;
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
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;
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'
2787 FROM igs_ps_sch_int_all
2788 WHERE int_usec_id=cp_int_usec_id
2789 AND import_done_flag='N';
2790
2791 CURSOR cur_pattern_import(cp_int_pat_id IN NUMBER) IS
2792 SELECT 'X'
2793 FROM igs_ps_sch_usec_int_all
2794 WHERE int_pat_id=cp_int_pat_id
2795 AND import_done_flag='N';
2796 l_c_var VARCHAR2(1);
2797
2798 BEGIN
2799
2800 OPEN c_prd_uso (p_n_prd_uso_id);
2801 FETCH c_prd_uso INTO rec_prd_uso;
2802 CLOSE c_prd_uso;
2803
2804 OPEN c_int_uso (p_n_int_uso_id);
2805 FETCH c_int_uso INTO rec_int_uso;
2806 CLOSE c_int_uso;
2807
2808 -- if the unit section occurrece is succesfully scheduled (i.e schedule_status = 'SCHEDULED')
2809 -- then clear the scheduling preferences such as preferred region, preferred / dedicated building/room.
2810 IF p_c_sch_status <> 'SCHEDULED' THEN
2811 l_c_prf_reg := rec_prd_uso.preferred_region_code;
2812 l_n_prf_bld := rec_prd_uso.preferred_building_code;
2813 l_n_prf_rom := rec_prd_uso.preferred_room_code;
2814 l_n_ded_bld := rec_prd_uso.dedicated_building_code;
2815 l_n_ded_rom := rec_prd_uso.dedicated_room_code;
2816 END IF;
2817
2818 BEGIN
2819
2820 /*OPEN check_ovrd;
2821 FETCH check_ovrd INTO l_check_ovrd;
2822 IF check_ovrd%FOUND THEN
2823 --Days override
2824 IF l_check_ovrd.day_ovrd_flag = 'N' AND (rec_prd_uso.monday='Y' OR
2825 rec_prd_uso.tuesday='Y' OR
2826 rec_prd_uso.wednesday='Y' OR
2827 rec_prd_uso.thursday='Y' OR
2828 rec_prd_uso.friday='Y' OR
2829 rec_prd_uso.saturday='Y' OR
2830 rec_prd_uso.sunday='Y' ) THEN
2831 l_c_monday := rec_prd_uso.monday;
2832 l_c_tuesday := rec_prd_uso.tuesday;
2833 l_c_wednesday := rec_prd_uso.wednesday;
2834 l_c_thursday := rec_prd_uso.thursday;
2835 l_c_friday := rec_prd_uso.friday;
2836 l_c_saturday := rec_prd_uso.saturday;
2837 l_c_sunday := rec_prd_uso.sunday;
2838 ELSE
2839 l_c_monday := p_c_monday;
2840 l_c_tuesday := p_c_tuesday;
2841 l_c_wednesday := p_c_wednesday;
2842 l_c_thursday := p_c_thursday;
2843 l_c_friday := p_c_friday;
2844 l_c_saturday := p_c_saturday;
2845 l_c_sunday := p_c_sunday;
2846 END IF;
2847 --Time override
2848 IF l_check_ovrd.time_ovrd_flag = 'N' AND (rec_prd_uso.start_time IS NOT NULL OR
2849 rec_prd_uso.end_time IS NOT NULL ) THEN
2850 l_start_time := rec_prd_uso.start_time;
2851 l_end_time := rec_prd_uso.end_time;
2852 ELSE
2853 l_start_time := p_d_uso_start_tm;
2854 l_end_time := p_d_uso_end_tm;
2855 END IF;
2856 --Schedule Building override
2857 IF l_check_ovrd.scheduled_bld_ovrd_flag = 'N' AND (rec_prd_uso.building_code IS NOT NULL) THEN
2858 l_sch_bld := rec_prd_uso.building_code;
2859 ELSE
2860 l_sch_bld := l_building_code;
2861 END IF;
2862 --Schedule Room override
2863 IF l_check_ovrd.scheduled_room_ovrd_flag = 'N' AND (rec_prd_uso.room_code IS NOT NULL ) THEN
2864 l_sch_room := rec_prd_uso.room_code;
2865 ELSE
2866 l_sch_room := l_room_code;
2867 END IF;
2868
2869 ELSE
2870 l_c_monday := p_c_monday;
2871 l_c_tuesday := p_c_tuesday;
2872 l_c_wednesday := p_c_wednesday;
2873 l_c_thursday := p_c_thursday;
2874 l_c_friday := p_c_friday;
2875 l_c_saturday := p_c_saturday;
2876 l_c_sunday := p_c_sunday;
2877 l_start_time := p_d_uso_start_tm;
2878 l_end_time := p_d_uso_end_tm;
2879 l_sch_bld := l_building_code;
2880 l_sch_room := l_room_code;
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,
2890 X_uoo_id => rec_prd_uso.uoo_id,
2891 X_monday => p_c_monday,
2892 X_tuesday => p_c_tuesday,
2893 X_wednesday => p_c_wednesday,
2894 X_thursday => p_c_thursday,
2895 X_friday => p_c_friday,
2896 X_saturday => p_c_saturday,
2897 X_sunday => p_c_sunday,
2898 X_start_time => p_d_uso_start_tm,
2899 X_end_time => p_d_uso_end_tm,
2900 X_building_code => l_building_code,
2901 X_room_code => l_room_code,
2902 X_schedule_status => p_c_sch_status,
2903 X_status_last_updated => sysdate,
2904 X_instructor_id => rec_prd_uso.instructor_id,
2905 X_attribute_category => rec_prd_uso.attribute_category,
2906 X_attribute1 => rec_prd_uso.attribute1,
2907 X_attribute2 => rec_prd_uso.attribute2,
2908 X_attribute3 => rec_prd_uso.attribute3,
2909 X_attribute4 => rec_prd_uso.attribute4,
2910 X_attribute5 => rec_prd_uso.attribute5,
2911 X_attribute6 => rec_prd_uso.attribute6,
2912 X_attribute7 => rec_prd_uso.attribute7,
2913 X_attribute8 => rec_prd_uso.attribute8,
2914 X_attribute9 => rec_prd_uso.attribute9,
2915 X_attribute10 => rec_prd_uso.attribute10,
2916 X_attribute11 => rec_prd_uso.attribute11,
2917 X_attribute12 => rec_prd_uso.attribute12,
2918 X_attribute13 => rec_prd_uso.attribute13,
2919 X_attribute14 => rec_prd_uso.attribute14,
2920 X_attribute15 => rec_prd_uso.attribute15,
2921 X_attribute16 => rec_prd_uso.attribute16,
2922 X_attribute17 => rec_prd_uso.attribute17,
2923 X_attribute18 => rec_prd_uso.attribute18,
2924 X_attribute19 => rec_prd_uso.attribute19,
2925 X_attribute20 => rec_prd_uso.attribute20,
2926 X_error_text => p_c_err_text,
2927 X_start_date => p_d_uso_start_dt,
2928 X_end_date => p_d_uso_end_dt,
2929 X_to_be_Announced => p_c_tba_uso,
2930 X_dedicated_building_code => l_n_ded_bld,
2931 X_dedicated_room_code => l_n_ded_rom,
2932 X_preferred_building_code => l_n_prf_bld,
2933 X_preferred_room_code => l_n_prf_rom,
2934 X_inst_notify_ind => rec_prd_uso.inst_notify_ind,
2935 X_notify_status => rec_prd_uso.notify_status,
2936 x_preferred_region_code => l_c_prf_reg,
2937 x_no_set_day_ind => rec_prd_uso.no_set_day_ind,
2938 x_cancel_flag => rec_prd_uso.cancel_flag,
2939 x_occurrence_identifier => rec_prd_uso.occurrence_identifier,
2940 x_abort_flag => rec_prd_uso.abort_flag
2941 );
2942
2943 IF p_c_trans_type ='COMPLETE' THEN
2944 l_import_done:= 'Y';
2945 ELSE
2946 l_import_done:= 'N';
2947 END IF;
2948 -- Update interface Occurrence table
2949 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,
2950 import_done_flag=l_import_done WHERE int_occurs_id = p_n_int_uso_id;
2951
2952
2953 IF l_import_done = 'Y' THEN
2954 --Update the interface section import_done_flag
2955 OPEN cur_section_import(rec_int_uso.int_usec_id);
2956 FETCH cur_section_import INTO l_c_var;
2957 IF cur_section_import%NOTFOUND THEN
2958 UPDATE igs_ps_sch_usec_int_all set import_done_flag='Y' WHERE int_usec_id = rec_int_uso.int_usec_id;
2959 END IF;
2960 CLOSE cur_section_import;
2961
2962 --Update the interface pattern import_done_flag
2963 OPEN cur_pattern_import(rec_int_uso.int_pat_id);
2964 FETCH cur_pattern_import INTO l_c_var;
2965 IF cur_pattern_import%NOTFOUND THEN
2966 UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = rec_int_uso.int_pat_id;
2967 END IF;
2968 CLOSE cur_pattern_import;
2969 END IF;
2970
2971
2972 OPEN c_print_uso (p_n_prd_uso_id);
2973 FETCH c_print_uso INTO rec_print_uso;
2974 CLOSE c_print_uso;
2975 log_usec_occurs (p_c_trans_type, rec_int_uso.instructor_id, rec_print_uso,'G');
2976 IF rec_prd_uso.to_be_announced = 'Y' AND p_c_sch_status = 'SCHEDULED' THEN
2977 OPEN c_nstd_us(p_n_prd_uso_id);
2978 FETCH c_nstd_us INTO l_c_exists;
2979 IF c_nstd_us%FOUND THEN
2980 fnd_message.set_name('IGS','IGS_PS_SCH_TBA_USO_NSD_USEC');
2981 fnd_file.put_line(fnd_file.LOG,' ' ||fnd_message.get);
2982 END IF;
2983 CLOSE c_nstd_us;
2984 END IF;
2985
2986 EXCEPTION
2987 WHEN OTHERS THEN
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,
2997 X_uoo_id => rec_prd_uso.uoo_id,
2998 X_monday => rec_prd_uso.monday,
2999 X_tuesday => rec_prd_uso.tuesday,
3000 X_wednesday => rec_prd_uso.wednesday,
3001 X_thursday => rec_prd_uso.thursday,
3002 X_friday => rec_prd_uso.friday,
3003 X_saturday => rec_prd_uso.saturday,
3004 X_sunday => rec_prd_uso.sunday,
3005 X_start_time => rec_prd_uso.start_time,
3006 X_end_time => rec_prd_uso.end_time,
3007 X_building_code => rec_prd_uso.building_code,
3008 X_room_code => rec_prd_uso.room_code,
3009 X_schedule_status => l_c_sch_status,
3010 X_status_last_updated => sysdate,
3011 X_instructor_id => rec_prd_uso.instructor_id,
3012 X_attribute_category => rec_prd_uso.attribute_category,
3013 X_attribute1 => rec_prd_uso.attribute1,
3014 X_attribute2 => rec_prd_uso.attribute2,
3015 X_attribute3 => rec_prd_uso.attribute3,
3016 X_attribute4 => rec_prd_uso.attribute4,
3017 X_attribute5 => rec_prd_uso.attribute5,
3018 X_attribute6 => rec_prd_uso.attribute6,
3019 X_attribute7 => rec_prd_uso.attribute7,
3020 X_attribute8 => rec_prd_uso.attribute8,
3021 X_attribute9 => rec_prd_uso.attribute9,
3022 X_attribute10 => rec_prd_uso.attribute10,
3023 X_attribute11 => rec_prd_uso.attribute11,
3024 X_attribute12 => rec_prd_uso.attribute12,
3025 X_attribute13 => rec_prd_uso.attribute13,
3026 X_attribute14 => rec_prd_uso.attribute14,
3027 X_attribute15 => rec_prd_uso.attribute15,
3028 X_attribute16 => rec_prd_uso.attribute16,
3029 X_attribute17 => rec_prd_uso.attribute17,
3030 X_attribute18 => rec_prd_uso.attribute18,
3031 X_attribute19 => rec_prd_uso.attribute19,
3032 X_attribute20 => rec_prd_uso.attribute20,
3033 X_error_text => l_c_err_text,
3034 X_start_date => rec_prd_uso.start_date,
3035 X_end_date => rec_prd_uso.end_date,
3036 X_to_be_Announced => rec_prd_uso.to_be_announced,
3037 X_dedicated_building_code => rec_prd_uso.dedicated_building_code,
3038 X_dedicated_room_code => rec_prd_uso.dedicated_room_code,
3039 X_preferred_building_code => rec_prd_uso.preferred_building_code,
3040 X_preferred_room_code => rec_prd_uso.preferred_room_code,
3041 X_inst_notify_ind => rec_prd_uso.inst_notify_ind,
3042 X_notify_status => rec_prd_uso.notify_status,
3043 x_preferred_region_code => rec_prd_uso.preferred_region_code,
3044 x_no_set_day_ind => rec_prd_uso.no_set_day_ind,
3045 x_cancel_flag => rec_prd_uso.cancel_flag,
3046 x_occurrence_identifier => rec_prd_uso.occurrence_identifier,
3047 x_abort_flag => rec_prd_uso.abort_flag
3048 );
3049
3050 -- Update interface Occurrence table
3051 -- Update interface table (i.e. Transaction Type) by transaction status as 'INCOMPLETE' and Schedule status as 'ERROR'
3052 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
3053 WHERE int_occurs_id = p_n_int_uso_id;
3054
3055 OPEN c_print_uso (p_n_prd_uso_id);
3056 FETCH c_print_uso INTO rec_print_uso;
3057 CLOSE c_print_uso;
3058 log_usec_occurs (l_c_trans_type, rec_int_uso.instructor_id, rec_print_uso,'G');
3059 END;
3060
3061 END update_info;
3062 FUNCTION get_location_description(p_location_cd igs_ad_location_all.location_cd%TYPE) RETURN VARCHAR2 AS
3063 CURSOR c_desc(cp_c_location_cd IN igs_ad_location_all.location_cd%TYPE) IS
3064 SELECT description
3065 FROM igs_ad_location_all
3066 WHERE location_cd = cp_c_location_cd;
3067 l_c_description igs_ad_location_all.description%TYPE;
3068 BEGIN
3069 OPEN c_desc(p_location_cd);
3070 FETCH c_desc INTO l_c_description;
3071 CLOSE c_desc;
3072 RETURN l_c_description;
3073 END get_location_description;
3074
3075 BEGIN
3076
3077 -- To fetch table schema name for gather statistics
3078 l_b_return := fnd_installation.get_app_info('IGS', l_c_status, l_c_industry, l_c_schema);
3079
3080 -- Collect statistics of the interface table as per standards. Bug # 2710978
3081 tablenames_tbl(1) := 'IGS_PS_SCH_INSTR_ALL';
3082 tablenames_tbl(2) := 'IGS_PS_SCH_MWC_ALL';
3083 tablenames_tbl(3) := 'IGS_PS_SCH_FACLT_ALL';
3084 tablenames_tbl(4) := 'IGS_PS_SCH_X_USEC_INT_ALL';
3085 tablenames_tbl(5) := 'IGS_PS_SCH_USEC_INT_ALL';
3086 tablenames_tbl(6) := 'IGS_PS_SCH_INT_ALL';
3087 tablenames_tbl(7) := 'IGS_PS_PREFS_SCH_INT_ALL';
3088 tablenames_tbl(8) := 'IGS_PS_SCH_HDR_INT_ALL';
3089
3090 FOR i IN 1.. tablenames_tbl.LAST
3091 LOOP
3092 fnd_stats.gather_table_stats(ownname => l_c_schema,
3093 tabname => tablenames_tbl(i),
3094 cascade => TRUE
3095 );
3096 END LOOP;
3097
3098 -- set the multi org id
3099 igs_ge_gen_003.set_org_id (p_org_id);
3100
3101 -- set the flag to success
3102 retcode:=0;
3103
3104 -- open the Scheduler Unit Section Details Data cursor - This is the Main LOOP
3105 FOR l_fetch_usec_inter IN c_get_usec_inter
3106 LOOP
3107 IF l_c_t_cal_type IS NULL AND l_n_t_seq_num IS NULL AND l_n_t_uoo_id IS NULL THEN
3108 l_c_t_cal_type := l_fetch_usec_inter.calendar_type;
3109 l_n_t_seq_num := l_fetch_usec_inter.sequence_number;
3110 l_n_t_uoo_id := l_fetch_usec_inter.uoo_id;
3111 l_b_print_cal := TRUE;
3112 l_b_print_uoo := TRUE;
3113 ELSE
3114 IF (l_c_t_cal_type <> l_fetch_usec_inter.calendar_type) AND
3115 (l_n_t_seq_num <> l_fetch_usec_inter.sequence_number) THEN
3116 l_b_print_cal :=TRUE;
3117 l_b_print_uoo := TRUE;
3118 l_c_t_cal_type := l_fetch_usec_inter.calendar_type;
3119 l_n_t_seq_num := l_fetch_usec_inter.sequence_number;
3120 ELSE
3121 IF l_n_t_uoo_id <> l_fetch_usec_inter.uoo_id THEN
3122 l_b_print_uoo := TRUE;
3123 l_n_t_uoo_id := l_fetch_usec_inter.uoo_id;
3124 END IF;
3125 END IF;
3126 END IF;
3127
3128 -- cursor for fetching Originator id
3129 OPEN c_get_originator(l_fetch_usec_inter.transaction_id);
3130 FETCH c_get_originator INTO l_originator;
3131 IF (c_get_originator%NOTFOUND) THEN
3132 CLOSE c_get_originator;
3133 retcode:=2;
3134 fnd_message.set_name('IGS','IGS_GE_VAL_DOES_NOT_XS');
3135 fnd_file.put_line(fnd_file.log,fnd_message.get);
3136 app_exception.raise_exception;
3137 END IF;
3138 CLOSE c_get_originator; -- closing cursor for originator id
3139
3140 -- Cursor for fetching records from Interface Table(igs_ps_sch_int)
3141 -- For each record fetched by the main Unit Section Details Cursor Loop,
3142 -- We Loop across the Scheduler Interface Details table.
3143
3144 FOR fetch_records_inter IN c_get_records_inter(l_fetch_usec_inter.int_usec_id)
3145 Loop
3146 l_error_text := NULL;
3147 l_valid_rec_for_prod := TRUE;
3148 l_unit_section_occurrence_id := fetch_records_inter.unit_section_occurrence_id;
3149 l_schedule_status := fetch_records_inter.schedule_status;
3150 l_inter_error_text := fetch_records_inter.error_text;
3151 l_transaction_type := fetch_records_inter.transaction_type;
3152 l_building_code := fetch_records_inter.building_id;
3153 l_room_code := fetch_records_inter.room_id;
3154 l_start_time := fetch_records_inter.start_time;
3155 l_end_time := fetch_records_inter.end_time;
3156 l_monday := fetch_records_inter.monday;
3157 l_tuesday := fetch_records_inter.tuesday;
3158 l_wednesday := fetch_records_inter.wednesday;
3159 l_thursday := fetch_records_inter.thursday;
3160 l_friday := fetch_records_inter.friday;
3161 l_saturday := fetch_records_inter.saturday;
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
3171 CLOSE c_ipsuo;
3172 retcode:=2;
3173 fnd_message.set_name('IGS','IGS_GE_VAL_DOES_NOT_XS');
3174 fnd_file.put_line(fnd_file.log,fnd_message.get);
3175 app_exception.raise_exception;
3176 END IF;
3177 -- This is added as part of bug#4287940
3178 l_tba_status := l_ipsuo.to_be_announced;
3179
3180 --Check if the production record is already schdeuled to avoid confliction in scheduling
3181 --added by Babita
3182 IF l_originator = 'INTERNAL' OR l_originator = 'EXTERNAL' THEN
3183 -- Conflicting scheduling: 1
3184 -- if the schedule status of the USO is scheduled in production table, then it is already scheduled
3185 -- no need to import the record, log the message IGS_PS_USO_SCHED
3186 -- Conflicting scheduling: 2, if the origination of transaction is external then the schedule status of the corresponding
3187 -- unit section occurrence should not be in 'Scheduling in Progress', 'Cancellation Requested' and 'Rescheduling Requested'
3188 -- Set the error message to indicate to 3rd party s/w about conflicting schedule
3189 -- Also set the values of variables l_set_scheduled_status and l_transaction_type to be used to update interface table
3190 l_status:=l_ipsuo.schedule_status;
3191 IF l_status = 'SCHEDULED' OR (l_originator = 'EXTERNAL' AND l_status IN ('PROCESSING','USER_UPDATE','USER_CANCEL')) THEN
3192 IF l_status = 'SCHEDULED' THEN
3193 fnd_message.set_name('IGS','IGS_PS_USO_SCHED');
3194 l_error_text:= fnd_message.get;
3195 l_set_scheduled_status := 'SCHEDULED';
3196 l_set_transaction_type := 'COMPLETE';
3197 ELSE
3198 fnd_message.set_name('IGS','IGS_PS_CONFLICT_SCHD');
3199 l_error_text:= fnd_message.get;
3200 l_set_scheduled_status := 'ERROR';
3201 l_set_transaction_type := 'INCOMPLETE';
3202 END IF;
3203
3204 -- if the scheduling software could not schedules a unit section occurrence (schedule_status ='ERROR') then scheduling software
3205 -- should provide the error message. if error message is not provided then set the default error message 'IGS_PS_REF_3RD_PRTY_SW_ERR'
3206 ELSIF l_schedule_status = 'ERROR' THEN
3207 IF l_inter_error_text IS NULL THEN
3208 fnd_message.set_name('IGS','IGS_PS_REF_3RD_PRTY_SW_ERR');
3209 l_error_text:= fnd_message.get;
3210 ELSE
3211 l_error_text:=l_inter_error_text;
3212 END IF; -- end of l_inter_error_text
3213 --Added to get the values of variables to be set in interface table
3214 l_set_transaction_type := 'INCOMPLETE';
3215 l_set_scheduled_status := 'ERROR';
3216
3217 -- if the scheduling software successfully scheduled a unit section occurrence it sets the schedule status = 'SCHEDULED'
3218 ELSIF l_schedule_status = 'OK' THEN
3219 l_error_text :=NULL;
3220 -- if the unit section occurrence is request for schedule / request for rescheduled then scheduling software should provide
3221 -- scheduled building identifier and room identifier for normal unit section occurrence. for to be announced unit section
3222 -- occurrence scheduling software should provide unit section occcurrence effective dates, start/end time along wich scheduled
3223 -- building/room identifier.
3224 IF l_transaction_type IN ('REQUEST','UPDATE') THEN
3225
3226 ----
3227 OPEN check_ovrd;
3228 FETCH check_ovrd INTO l_check_ovrd;
3229 IF check_ovrd%FOUND THEN
3230 --Days override
3231 IF l_check_ovrd.day_ovrd_flag = 'N' AND (l_ipsuo.monday='Y' OR
3232 l_ipsuo.tuesday='Y' OR
3233 l_ipsuo.wednesday='Y' OR
3234 l_ipsuo.thursday='Y' OR
3235 l_ipsuo.friday='Y' OR
3236 l_ipsuo.saturday='Y' OR
3237 l_ipsuo.sunday='Y' ) THEN
3238 l_monday := l_ipsuo.monday;
3239 l_tuesday := l_ipsuo.tuesday;
3240 l_wednesday := l_ipsuo.wednesday;
3241 l_thursday := l_ipsuo.thursday;
3242 l_friday := l_ipsuo.friday;
3243 l_saturday := l_ipsuo.saturday;
3244 l_sunday := l_ipsuo.sunday;
3245 END IF;
3246 --Date override
3247 IF l_check_ovrd.date_ovrd_flag = 'N' AND (l_ipsuo.start_date IS NOT NULL OR
3248 l_ipsuo.end_date IS NOT NULL ) THEN
3249 l_d_uso_start_date := l_ipsuo.start_date;
3250 l_d_uso_end_date := l_ipsuo.end_date;
3251 END IF;
3252 --Time override
3253 IF l_check_ovrd.time_ovrd_flag = 'N' AND (l_ipsuo.start_time IS NOT NULL OR
3254 l_ipsuo.end_time IS NOT NULL ) THEN
3255 l_start_time := l_ipsuo.start_time;
3256 l_end_time := l_ipsuo.end_time;
3257 END IF;
3258 --Schedule Building override
3259 IF l_check_ovrd.scheduled_bld_ovrd_flag = 'N' AND (l_ipsuo.building_code IS NOT NULL) THEN
3260 l_building_code := l_ipsuo.building_code;
3261 END IF;
3262 --Schedule Room override
3263 IF l_check_ovrd.scheduled_room_ovrd_flag = 'N' AND (l_ipsuo.room_code IS NOT NULL ) THEN
3264 l_room_code := l_ipsuo.room_code;
3265 END IF;
3266 END IF;
3267 CLOSE check_ovrd;
3268 ----
3269
3270 -- check whehter the scheduling software provided building identifier / room identifier
3271 IF l_building_code is NULL OR
3272 l_d_uso_start_date IS NULL OR l_d_uso_end_date IS NULL OR
3273 ( (NVL(l_sunday,'N') = 'N') AND (NVL(l_monday,'N') = 'N') AND (NVL(l_tuesday,'N') = 'N') AND (NVL(l_wednesday,'N') = 'N')
3274 AND (NVL(l_thursday,'N') = 'N') AND (NVL(l_friday,'N') = 'N') AND (NVL(l_saturday,'N') = 'N') ) THEN
3275 --check for the value of tba_status,If 'Y' then set the corresponding error textwith scheduled status = 'TBA'
3276 IF fetch_records_inter.tba_status = 'Y' THEN
3277 fnd_message.set_name('IGS','IGS_PS_USO_TBA_STATUS');
3278 l_error_text:= fnd_message.get;
3279 l_set_scheduled_status := 'TBA' ;
3280 ELSE
3281 IF l_inter_error_text IS NULL THEN
3282 fnd_message.set_name('IGS','IGS_PS_VALUES_NULL');
3283 l_error_text:= fnd_message.get;
3284 ELSE
3285 l_error_text:=l_inter_error_text;
3286 END IF;
3287 l_set_scheduled_status := 'ERROR';
3288 END IF;
3289 l_set_transaction_type := 'INCOMPLETE';
3290 ELSE
3291 l_set_scheduled_status := 'SCHEDULED' ;
3292 l_set_transaction_type := 'COMPLETE';
3293 l_error_text := NULL;
3294
3295 --Check the value of TBA_STATUS.
3296 /*IF fetch_records_inter.tba_status = 'Y' THEN
3297 -- for to be announced unit section occurrence. Apart from building /room identifier. Scheduling software
3298 -- provides the meeting days (Sunday - Monday) , start / end time. Also unit section occurrence effective dates
3299 -- if not provided by the user in the system.*/
3300
3301 IF l_start_time IS NOT NULL AND l_end_time IS NOT NULL THEN
3302 IF TO_CHAR(l_start_time,'HH24:MI:SS') > TO_CHAR(l_end_time,'HH24:MI:SS') THEN
3303 -- start time should be less than end time.
3304 fnd_message.set_name('IGS', 'IGS_GE_ST_TIME_LT_END_TIME');
3305 l_error_text := fnd_message.get;
3306 END IF;
3307 END IF;
3308
3309 -- if the unit section occurrence start / end date is not provided by user in the system then it would be
3310 -- provided by scheduling software, need to validate the user provided date is valid date.
3311 OPEN c_date(l_ipsuo.uoo_id);
3312 FETCH c_date INTO rec_date;
3313 CLOSE c_date;
3314
3315 -- l_d_uso_start_date and l_d_uso_end_date should be not null for non tba occurrence
3316 IF l_d_uso_start_date IS NOT NULL AND l_d_uso_end_date IS NOT NULL THEN
3317
3318 -- Unit section occurrence start date should be less than end date
3319 IF l_d_uso_end_date < l_d_uso_start_date THEN
3320 fnd_message.set_name('IGS','IGS_PE_EDT_LT_SDT');
3321 l_error_text := fnd_message.get;
3322 END IF;
3323
3324 IF l_d_uso_start_date IS NOT NULL THEN
3325 IF rec_date.us_start_date IS NOT NULL THEN
3326 IF l_d_uso_start_date < rec_date.us_start_date THEN
3327 fnd_message.set_name ('IGS','IGS_PS_USO_STDT_GE_US_STDT');
3328 l_error_text := fnd_message.get;
3329 END IF;
3330 ELSE
3331 IF l_d_uso_start_date < rec_date.tp_start_date THEN
3332 fnd_message.set_name ('IGS','IGS_PS_USO_STDT_GE_TP_STDT');
3333 l_error_text := fnd_message.get;
3334 END IF;
3335 END IF;
3336
3337 IF rec_date.us_end_date IS NOT NULL THEN
3338 IF l_d_uso_start_date > rec_date.us_end_date THEN
3339 fnd_message.set_name ('IGS','IGS_PS_USO_ST_DT_UOO_END_DT');
3340 l_error_text := fnd_message.get;
3341 END IF;
3342 ELSE
3343 IF l_d_uso_start_date > rec_date.tp_end_date THEN
3344 fnd_message.set_name ('IGS','IGS_PS_USO_ST_DT_TP_END_DT');
3345 l_error_text := fnd_message.get;
3346 END IF;
3347 END IF;
3348 END IF;
3349
3350 IF l_d_uso_end_date IS NOT NULL THEN
3351 IF rec_date.us_start_date IS NOT NULL THEN
3352 IF l_d_uso_end_date < rec_date.us_start_date THEN
3353 fnd_message.set_name ('IGS','IGS_PS_USO_END_DT_UOO_ST_DT');
3354 l_error_text := fnd_message.get;
3355 END IF;
3356 ELSE
3357 IF l_d_uso_end_date < rec_date.tp_start_date THEN
3358 fnd_message.set_name ('IGS','IGS_PS_USO_END_DT_TP_ST_DT');
3359 l_error_text := fnd_message.get;
3360 END IF;
3361 END IF;
3362
3363 IF rec_date.us_end_date IS NOT NULL THEN
3364 IF l_d_uso_end_date > rec_date.us_end_date THEN
3365 fnd_message.set_name ('IGS','IGS_PS_USO_ENDT_LE_US_ENDT');
3366 l_error_text := fnd_message.get;
3367 END IF;
3368 ELSE
3369 IF l_d_uso_end_date > rec_date.tp_end_date THEN
3370 fnd_message.set_name ('IGS','IGS_PS_USO_ENDT_LE_TP_ENDT');
3371 l_error_text := fnd_message.get;
3372
3373 END IF;
3374 END IF;
3375
3376 END IF;
3377 END IF;
3378
3379
3380 -- if the unit section occurrence is success in all the above validation then set the tba_status as 'N'
3381 IF l_error_text IS NULL THEN
3382 l_tba_status := 'N' ;
3383 END IF;
3384 /*ELSE -- for normal unit section occurrences
3385 l_monday := l_ipsuo.monday ;
3386 l_tuesday := l_ipsuo.tuesday;
3387 l_wednesday := l_ipsuo.wednesday;
3388 l_thursday := l_ipsuo.thursday;
3389 l_friday := l_ipsuo.friday;
3390 l_saturday := l_ipsuo.saturday;
3391 l_sunday := l_ipsuo.sunday;
3392 l_d_uso_start_date := l_ipsuo.start_date;
3393 l_d_uso_end_date := l_ipsuo.end_date;
3394 l_start_time := l_ipsuo.start_time;
3395 l_end_time := l_ipsuo.end_time;
3396 END IF;*/
3397
3398 -- validate the building identifier
3399 OPEN c_bldg_exists(l_building_code);
3400 FETCH c_bldg_exists INTO l_c_exists;
3401 IF c_bldg_exists%NOTFOUND THEN
3402 fnd_message.set_name ('IGS','IGS_PS_BUILDING_ID_INVALID');
3403 l_error_text := fnd_message.get;
3404 END IF;
3405 CLOSE c_bldg_exists;
3406
3407 IF l_room_code IS NOT NULL THEN
3408 -- validate the room identifier
3409 OPEN c_room_exists(l_room_code);
3410 FETCH c_room_exists INTO l_c_exists;
3411 IF c_room_exists%NOTFOUND THEN
3412 fnd_message.set_name ('IGS','IGS_PS_ROOM_ID_INVALID');
3413 l_error_text := fnd_message.get;
3414 END IF;
3415 CLOSE c_room_exists;
3416
3417 -- validate the building / room identifier
3418 OPEN c_bldg_room_exists(l_building_code,l_room_code);
3419 FETCH c_bldg_room_exists INTO l_c_exists;
3420 IF c_bldg_room_exists%NOTFOUND THEN
3421 fnd_message.set_name ('IGS','IGS_PS_ROOM_INV_FOR_BLD');
3422 l_error_text := fnd_message.get;
3423 END IF;
3424 CLOSE c_bldg_room_exists;
3425 END IF;
3426
3427 IF l_error_text IS NOT NULL THEN
3428 l_set_scheduled_status := 'ERROR' ;
3429 END IF;
3430 END IF; -- if start time , end time , building identifier and room identifier are null.
3431 ELSIF l_transaction_type = 'CANCEL' THEN
3432 IF l_building_code IS NULL AND l_room_code IS NULL THEN
3433 l_set_scheduled_status := 'CANCELLED';
3434 l_set_transaction_type := 'COMPLETE';
3435 l_error_text := NULL;
3436 ELSE
3437 IF l_inter_error_text IS NULL THEN
3438 fnd_message.set_name('IGS','IGS_PS_VALUES_NOT_NULL');
3439 l_error_text:= fnd_message.get;
3440 ELSE
3441 l_error_text:=l_inter_error_text;
3442 END IF;
3443 l_set_scheduled_status := 'ERROR';
3444 l_set_transaction_type := 'INCOMPLETE';
3445 END IF; -- End of checking for NOT NULL values
3446 END IF; -- End Transcation Type(REQUEST,UPDATE,CANCEL)
3447 END IF; -- End of Schedule Status(ERROR,OK)
3448
3449
3450 IF l_error_text IS NOT NULL THEN
3451 l_set_transaction_type := 'INCOMPLETE';
3452 l_building_code := l_ipsuo.building_code;
3453 l_room_code := l_ipsuo.room_code;
3454 l_d_uso_start_date := l_ipsuo.start_date;
3455 l_d_uso_end_date := l_ipsuo.end_date;
3456 l_start_time := l_ipsuo.start_time;
3457 l_end_time := l_ipsuo.end_time;
3458 l_sunday := l_ipsuo.sunday;
3459 l_monday := l_ipsuo.monday;
3460 l_tuesday := l_ipsuo.tuesday;
3461 l_wednesday := l_ipsuo.wednesday;
3462 l_thursday := l_ipsuo.thursday;
3463 l_friday := l_ipsuo.friday;
3464 l_saturday := l_ipsuo.saturday;
3465 l_tba_status := l_ipsuo.to_be_announced;
3466 END IF;
3467
3468 -- Printing calendar details
3469 IF l_b_print_cal THEN
3470 log_teach_cal (l_fetch_usec_inter.calendar_type,
3471 l_fetch_usec_inter.sequence_number);
3472 l_b_print_cal := FALSE;
3473 END IF;
3474 -- Printing unit section details
3475 IF l_b_print_uoo THEN
3476 log_usec_details (l_fetch_usec_inter.unit_cd,
3477 l_fetch_usec_inter.version_number,
3478 get_location_description(l_fetch_usec_inter.location_cd),
3479 l_fetch_usec_inter.unit_class,
3480 l_fetch_usec_inter.enrollment_maximum);
3481 l_b_print_uoo :=FALSE;
3482 END IF;
3483
3484 update_info( l_ipsuo.unit_section_occurrence_id,
3485 fetch_records_inter.int_occurs_id,
3486 l_set_scheduled_status,
3487 l_set_transaction_type,
3488 l_building_code,
3489 l_room_code,
3490 l_d_uso_start_date,
3491 l_d_uso_end_date,
3492 l_start_time,
3493 l_end_time,
3494 l_sunday,
3495 l_monday,
3496 l_tuesday,
3497 l_wednesday,
3498 l_thursday,
3499 l_friday,
3500 l_saturday,
3501 l_tba_status,
3502 l_error_text
3503 );
3504 END IF; -- End of Originator Id(INTERNAL,EXTERNAL)
3505 CLOSE c_ipsuo; -- closing of production table cursor
3506 END LOOP; -- for c_fetch_records_inter loop
3507 END LOOP; -- for c_fetch_usec_inter loop
3508
3509 -- Modified to show the success messsage only if some valid records are transfered from
3510 -- interface table to production table, otherwise log the message ' NO valid record exists
3511 -- to import into the production table', added as a part of Bug # 2427725
3512 IF l_valid_rec_for_prod THEN
3513 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SCH_GET_SCHD_SUCCESS');
3514 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3515 ELSE
3516 -- Message is logged when there exists no scheduled records in interface table that needs to be
3517 -- transfer from Interface tables
3518 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_SHD_REC_FRM_INT');
3519 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3520 END IF;
3521
3522 EXCEPTION
3523 WHEN OTHERS THEN
3524 RETCODE:=2;
3525 fnd_file.put_line(fnd_file.log, SQLERRM);
3526 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
3527 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3528 ROLLBACK;
3529 END prgp_get_schd_records ;
3530
3531
3532 PROCEDURE abort_sched(
3533 errbuf OUT NOCOPY VARCHAR2,
3534 retcode OUT NOCOPY NUMBER,
3535 p_teach_calendar IN VARCHAR2 ,
3536 p_unit_cd IN VARCHAR2 ,
3537 p_version_number IN NUMBER,
3538 p_location IN VARCHAR2,
3539 p_unit_class IN VARCHAR2,
3540 p_cancel_only IN VARCHAR2) AS
3541 /**********************************************************************
3542 Created By : sommukhe
3543
3544 Date Created On : 12-May-2005
3545
3546 Purpose : To abort records from scheduling interface tables.
3547
3548 Know limitations, enhancements or remarks
3549
3550 Change History
3551 Who When What
3552 (reverse chronological order - newest change first)
3553 sommukhe 9-JAN-2006 Bug# 4869737,included call to igs_ge_gen_003.set_org_id
3554 ************************************************************************/
3555
3556 --This cursor picks those occurrences from the interface table whose schedule status is null and filtered by the input parameters
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
3566 AND c.unit_cd= NVL (p_unit_cd, c.unit_cd)
3567 AND c.version_number= NVL (p_version_number, c.version_number)
3568 AND c.location_cd=NVL (p_location, c.location_cd)
3569 AND c.unit_class= NVL (p_unit_class, c.unit_class)
3570 AND (p_cancel_only ='N' OR b.cancel_flag = 'Y')
3571 AND a.schedule_status IS NULL;
3572
3573
3574 --all the interface section records
3575 CURSOR c_int_usec_ss (cp_sequence_number igs_ps_sch_int_all.sequence_number%TYPE,
3576 cp_cal_type igs_ps_sch_int_all.calendar_type%TYPE) IS
3577 SELECT us.ROWID intrid,us.*
3578 FROM igs_ps_sch_usec_int_all us
3579 WHERE calendar_type=cp_cal_type
3580 AND sequence_number = cp_sequence_number
3581 AND unit_cd= NVL (p_unit_cd, us.unit_cd)
3582 AND version_number=NVL (p_version_number, us.version_number)
3583 AND location_cd=NVL (p_location, us.location_cd)
3584 AND unit_class= NVL (p_unit_class, us.unit_class);
3585
3586 --all the interface pattern records
3587 CURSOR c_int_pat_ss (cp_sequence_number igs_ps_sch_int_all.sequence_number%TYPE,
3588 cp_cal_type igs_ps_sch_int_all.calendar_type%TYPE) IS
3589 SELECT pt.ROWID intrid,pt.*
3590 FROM igs_ps_sch_pat_int pt
3591 WHERE pt.calendar_type=cp_cal_type
3592 AND pt.sequence_number =cp_sequence_number
3593 AND pt.unit_cd= NVL (p_unit_cd,pt.unit_cd)
3594 AND pt.version_number=NVL (p_version_number, pt.version_number);
3595
3596
3597 CURSOR c_int_uso (cp_n_int_usec_id igs_ps_sch_int_all.int_usec_id%TYPE) IS
3598 SELECT count(*)
3599 FROM igs_ps_sch_int_all a
3600 WHERE a.int_usec_id = cp_n_int_usec_id
3601 AND a.abort_flag = 'N';
3602
3603 c_int_uso_rec c_int_uso%ROWTYPE;
3604
3605 CURSOR c_int_usec (cp_n_int_pat_id igs_ps_sch_usec_int_all.int_pat_id%TYPE) IS
3606 SELECT count(*)
3607 FROM igs_ps_sch_usec_int_all a
3608 WHERE a.int_pat_id = cp_n_int_pat_id
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
3618 CURSOR c_prod_usec (cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
3619 SELECT a.rowid prodrid,a.*
3620 FROM igs_ps_unit_ofr_opt_all a
3621 WHERE a.uoo_id=cp_uoo_id;
3622
3623 c_prod_usec_rec c_prod_usec%ROWTYPE;
3624
3625 CURSOR c_prod_pat (cp_sequence_number igs_ps_sch_int_all.sequence_number%TYPE,
3626 cp_cal_type igs_ps_sch_int_all.calendar_type%TYPE,
3627 cp_unit_cd igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
3628 cp_version_number igs_ps_unit_ofr_pat_all.version_number%TYPE) IS
3629 SELECT pt.ROWID prodrid,pt.*
3630 FROM igs_ps_unit_ofr_pat_all pt
3631 WHERE cal_type=cp_cal_type
3632 AND ci_sequence_number =cp_sequence_number
3633 AND unit_cd= cp_unit_cd
3634 AND version_number=cp_version_number;
3635
3636 c_prod_pat_rec c_prod_pat%ROWTYPE;
3637
3638
3639 l_cal_type igs_ca_inst.cal_type%TYPE;
3640 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
3641 l_start_date igs_ca_inst.start_dt%TYPE;
3642 l_end_date igs_ca_inst.end_dt%TYPE;
3643 l_uso_count NUMBER(10);
3644 l_abort_flag VARCHAR2(2);
3645 l_abort_count NUMBER(10);
3646 l_proc_count NUMBER(10);
3647
3648 BEGIN
3649
3650 igs_ge_gen_003.set_org_id (NULL);
3651 retcode:=0;
3652
3653 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
3654 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('TEACHING_PERIOD','IGS_PS_LOG_PARAMETERS'));
3655 fnd_message.set_token('PARAMETER_VAL' ,p_teach_calendar);
3656 fnd_file.put_line(fnd_file.log,fnd_message.get);
3657
3658 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
3659 || ' : ' || p_unit_cd );
3660 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
3661 || ' : ' || TO_CHAR (p_version_number) );
3662 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
3663 || ' : ' || p_location );
3664 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CLASS' )
3665 || ' : ' || p_unit_class );
3666
3667 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
3668 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('CANCEL_OCCUR_ONLY','IGS_PS_LOG_PARAMETERS'));
3669 fnd_message.set_token('PARAMETER_VAL' ,p_cancel_only);
3670 fnd_file.put_line(fnd_file.log,fnd_message.get);
3671 fnd_file.put_line(fnd_file.log,' ');
3672 fnd_file.put_line(fnd_file.log,' ');
3673
3674
3675 -- Get the cal_tpe,sequence_number and start date and End date
3676 l_cal_type := RTRIM(SUBSTR(p_teach_calendar,101,10));
3677 l_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_teach_calendar,112,6)));
3678 l_start_date := fnd_date.string_to_date(RTRIM(SUBSTR(p_teach_calendar,11,11)), 'DD-MON-YYYY');
3679 l_end_date := fnd_date.string_to_date(RTRIM(SUBSTR(p_teach_calendar,24,11)), 'DD-MON-YYYY');
3680
3681 l_abort_count := 0;
3682 l_proc_count := 0;
3683
3684 fnd_message.set_name('IGS','IGS_PS_ABORT_OCCURS_SEC_PAT');
3685 fnd_message.set_token('TABLENAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('OCCURRENCES','IGS_PS_TABLE_NAME'));
3686 fnd_file.put_line ( fnd_file.LOG,fnd_message.get );
3687 fnd_file.put_line(fnd_file.log,' ');
3688
3689 FOR c_int_uso_ss_rec IN c_int_uso_ss(l_ci_sequence_number,l_cal_type) LOOP
3690
3691 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
3692 || ' : ' || c_int_uso_ss_rec.unit_code );
3693 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
3694 || ' : ' || TO_CHAR (c_int_uso_ss_rec.unit_version_number) );
3695 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
3696 || ' : ' || c_int_uso_ss_rec.location_code );
3697 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CLASS' )
3698 || ' : ' || c_int_uso_ss_rec.unit_cls );
3699 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
3700 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('USEC_OCCRS_ID','IGS_PS_LOG_PARAMETERS'));
3701 fnd_message.set_token('PARAMETER_VAL' ,c_int_uso_ss_rec.occurrence_identifier);
3702 fnd_file.put_line(fnd_file.log,fnd_message.get);
3703 fnd_file.put_line ( fnd_file.LOG, 'Int Occurs Id'|| ' : ' || c_int_uso_ss_rec.int_occurs_id );
3704 fnd_file.put_line(fnd_file.log,' ');
3705
3706 UPDATE igs_ps_sch_int_all SET abort_flag = 'Y'
3707 WHERE ROWID = c_int_uso_ss_rec.intrid;
3708
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,
3718 X_tuesday => c_int_uso_prod_rec.tuesday,
3719 X_wednesday => c_int_uso_prod_rec.wednesday,
3720 X_thursday => c_int_uso_prod_rec.thursday,
3721 X_friday => c_int_uso_prod_rec.friday,
3722 X_saturday => c_int_uso_prod_rec.saturday,
3723 X_sunday => c_int_uso_prod_rec.sunday,
3724 X_start_time => c_int_uso_prod_rec.start_time,
3725 X_end_time => c_int_uso_prod_rec.end_time,
3726 X_building_code => c_int_uso_prod_rec.building_code,
3727 X_room_code => c_int_uso_prod_rec.room_code,
3728 X_schedule_status => NULL,
3729 X_status_last_updated => SYSDATE,
3730 X_instructor_id => c_int_uso_prod_rec.instructor_id,
3731 X_attribute_category => c_int_uso_prod_rec.attribute_category,
3732 X_attribute1 => c_int_uso_prod_rec.attribute1,
3733 X_attribute2 => c_int_uso_prod_rec.attribute2,
3734 X_attribute3 => c_int_uso_prod_rec.attribute3,
3735 X_attribute4 => c_int_uso_prod_rec.attribute4,
3736 X_attribute5 => c_int_uso_prod_rec.attribute5,
3737 X_attribute6 => c_int_uso_prod_rec.attribute6,
3738 X_attribute7 => c_int_uso_prod_rec.attribute7,
3739 X_attribute8 => c_int_uso_prod_rec.attribute8,
3740 X_attribute9 => c_int_uso_prod_rec.attribute9,
3741 X_attribute10 => c_int_uso_prod_rec.attribute10,
3742 X_attribute11 => c_int_uso_prod_rec.attribute11,
3743 X_attribute12 => c_int_uso_prod_rec.attribute12,
3744 X_attribute13 => c_int_uso_prod_rec.attribute13,
3745 X_attribute14 => c_int_uso_prod_rec.attribute14,
3746 X_attribute15 => c_int_uso_prod_rec.attribute15,
3747 X_attribute16 => c_int_uso_prod_rec.attribute16,
3748 X_attribute17 => c_int_uso_prod_rec.attribute17,
3749 X_attribute18 => c_int_uso_prod_rec.attribute18,
3750 X_attribute19 => c_int_uso_prod_rec.attribute19,
3751 X_attribute20 => c_int_uso_prod_rec.attribute20,
3752 X_error_text => c_int_uso_prod_rec.error_text ,
3753 x_mode => 'R',
3754 X_start_date => c_int_uso_prod_rec.start_date,
3755 X_end_date => c_int_uso_prod_rec.end_date,
3756 X_to_be_announced => c_int_uso_prod_rec.to_be_announced,
3757 X_inst_notify_ind => c_int_uso_prod_rec.inst_notify_ind,
3758 X_notify_status => c_int_uso_prod_rec.notify_status,
3759 X_dedicated_building_code => c_int_uso_prod_rec.dedicated_building_code,
3760 X_dedicated_room_code => c_int_uso_prod_rec.dedicated_room_code,
3761 X_preferred_building_code => c_int_uso_prod_rec.preferred_building_code,
3762 X_preferred_room_code => c_int_uso_prod_rec.preferred_room_code,
3763 X_preferred_region_code => c_int_uso_prod_rec.preferred_region_code,
3764 X_no_set_day_ind => c_int_uso_prod_rec.no_set_day_ind,
3765 x_cancel_flag => 'N',
3766 x_occurrence_identifier => c_int_uso_prod_rec.occurrence_identifier,
3767 x_abort_flag => 'Y'
3768 );
3769 CLOSE c_int_uso_prod;
3770 l_abort_count := l_abort_count +1;
3771 END LOOP;
3772
3773 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_PROCESS');
3774 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('OCCURRENCES','IGS_PS_TABLE_NAME'));
3775 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_abort_count );
3776 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_ABORTED');
3777 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('OCCURRENCES','IGS_PS_TABLE_NAME'));
3778 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_abort_count );
3779
3780 l_abort_count:= 0;
3781 l_proc_count := 0;
3782
3783 fnd_file.put_line ( fnd_file.LOG, ' ' );
3784 fnd_message.set_name('IGS','IGS_PS_ABORT_OCCURS_SEC_PAT');
3785 fnd_message.set_token('TABLENAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('SECTIONS','IGS_PS_TABLE_NAME'));
3786 fnd_file.put_line ( fnd_file.LOG,fnd_message.get );
3787 fnd_file.put_line ( fnd_file.LOG, ' ' );
3788
3789 FOR c_int_usec_ss_rec IN c_int_usec_ss(l_ci_sequence_number,l_cal_type) LOOP
3790 l_uso_count := NULL;
3791 OPEN c_int_uso(c_int_usec_ss_rec.int_usec_id);
3792 FETCH c_int_uso INTO l_uso_count;
3793 CLOSE c_int_uso;
3794
3795 IF NVL(l_uso_count,0) = 0 THEN
3796 l_abort_flag := 'Y';
3797 l_abort_count := l_abort_count +1;
3798
3799 --aborting the following section
3800 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
3801 || ' : ' || c_int_usec_ss_rec.unit_cd );
3802 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
3803 || ' : ' || TO_CHAR (c_int_usec_ss_rec.version_number) );
3804 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
3805 || ' : ' || c_int_usec_ss_rec.location_cd );
3806 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CLASS' )
3807 || ' : ' || c_int_usec_ss_rec.unit_class );
3808 fnd_file.put_line ( fnd_file.LOG, 'Int Usec Id'|| ' : ' || c_int_usec_ss_rec.int_usec_id );
3809 fnd_file.put_line ( fnd_file.LOG,' ' );
3810
3811
3812 ELSE
3813 l_abort_flag := 'N';
3814 END IF;
3815
3816
3817 --update the interface table
3818 UPDATE igs_ps_sch_usec_int_all SET abort_flag = l_abort_flag
3819 WHERE ROWID =c_int_usec_ss_rec.intrid ;
3820
3821 --update the production table
3822 OPEN c_prod_usec(c_int_usec_ss_rec.uoo_id);
3823 FETCH c_prod_usec INTO c_prod_usec_rec;
3824
3825 igs_ps_unit_ofr_opt_pkg.update_row(
3826 x_rowid => c_prod_usec_rec.prodrid,
3827 x_unit_cd => c_prod_usec_rec.unit_cd,
3828 x_version_number => c_prod_usec_rec.version_number,
3829 x_cal_type => c_prod_usec_rec.cal_type,
3830 x_ci_sequence_number => c_prod_usec_rec.ci_sequence_number,
3831 x_location_cd => c_prod_usec_rec.location_cd,
3832 x_unit_class => c_prod_usec_rec.unit_class,
3833 x_uoo_id => c_prod_usec_rec.uoo_id,
3834 x_ivrs_available_ind => c_prod_usec_rec.ivrs_available_ind,
3835 x_call_number => c_prod_usec_rec.call_number,
3836 x_unit_section_status => c_prod_usec_rec.unit_section_status,
3837 x_unit_section_start_date => c_prod_usec_rec.unit_section_start_date,
3838 x_unit_section_end_date => c_prod_usec_rec.unit_section_end_date,
3839 x_enrollment_actual => c_prod_usec_rec.enrollment_actual,
3840 x_waitlist_actual => c_prod_usec_rec.waitlist_actual,
3841 x_offered_ind => c_prod_usec_rec.offered_ind,
3842 x_state_financial_aid => c_prod_usec_rec.state_financial_aid,
3843 x_grading_schema_prcdnce_ind => c_prod_usec_rec.grading_schema_prcdnce_ind,
3844 x_federal_financial_aid => c_prod_usec_rec.federal_financial_aid,
3845 x_unit_quota => c_prod_usec_rec.unit_quota,
3846 x_unit_quota_reserved_places => c_prod_usec_rec.unit_quota_reserved_places,
3847 x_institutional_financial_aid => c_prod_usec_rec.institutional_financial_aid,
3848 x_grading_schema_cd => c_prod_usec_rec.grading_schema_cd,
3849 x_gs_version_number => c_prod_usec_rec.gs_version_number,
3850 x_unit_contact => c_prod_usec_rec.unit_contact,
3851 x_mode => 'R',
3852 x_ss_enrol_ind => c_prod_usec_rec.ss_enrol_ind,
3853 x_owner_org_unit_cd => c_prod_usec_rec.owner_org_unit_cd,
3854 x_attendance_required_ind => c_prod_usec_rec.attendance_required_ind,
3855 x_reserved_seating_allowed => c_prod_usec_rec.reserved_seating_allowed,
3856 x_ss_display_ind => c_prod_usec_rec.ss_display_ind,
3857 x_special_permission_ind => c_prod_usec_rec.special_permission_ind,
3858 x_dir_enrollment => c_prod_usec_rec.dir_enrollment,
3859 x_enr_from_wlst => c_prod_usec_rec.enr_from_wlst,
3860 x_inq_not_wlst => c_prod_usec_rec.inq_not_wlst,
3861 x_rev_account_cd => c_prod_usec_rec.rev_account_cd,
3862 x_anon_unit_grading_ind => c_prod_usec_rec.anon_unit_grading_ind,
3863 x_anon_assess_grading_ind => c_prod_usec_rec.anon_assess_grading_ind,
3864 x_non_std_usec_ind => c_prod_usec_rec.non_std_usec_ind,
3865 x_auditable_ind => c_prod_usec_rec.auditable_ind,
3866 x_audit_permission_ind => c_prod_usec_rec.audit_permission_ind,
3867 x_not_multiple_section_flag => c_prod_usec_rec.not_multiple_section_flag,
3868 x_sup_uoo_id => c_prod_usec_rec.sup_uoo_id,
3869 x_relation_type => c_prod_usec_rec.relation_type,
3870 x_default_enroll_flag => c_prod_usec_rec.default_enroll_flag,
3871 x_abort_flag => l_abort_flag
3872 );
3873
3874 CLOSE c_prod_usec;
3875 l_proc_count := l_proc_count +1;
3876 END LOOP;
3877
3878 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_PROCESS');
3879 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('SECTIONS','IGS_PS_TABLE_NAME'));
3880 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_proc_count );
3881 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_ABORTED');
3882 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('SECTIONS','IGS_PS_TABLE_NAME'));
3883 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_abort_count );
3884
3885 l_abort_count := 0;
3886 l_proc_count := 0;
3887
3888 fnd_file.put_line ( fnd_file.LOG, ' ' );
3889 fnd_message.set_name('IGS','IGS_PS_ABORT_OCCURS_SEC_PAT');
3890 fnd_message.set_token('TABLENAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('PATTERNS','IGS_PS_TABLE_NAME'));
3891 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
3892 fnd_file.put_line ( fnd_file.LOG, ' ' );
3893
3894 FOR c_int_pat_ss_rec IN c_int_pat_ss(l_ci_sequence_number,l_cal_type) LOOP
3895 l_uso_count:=null;
3896 OPEN c_int_usec(c_int_pat_ss_rec.int_pat_id);
3897 FETCH c_int_usec INTO l_uso_count;
3898 CLOSE c_int_usec;
3899
3900 IF NVL(l_uso_count,0) = 0 THEN
3901 l_abort_flag := 'Y';
3902 l_abort_count := l_abort_count +1;
3903
3904 --aborting the following patterns
3905 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
3906 || ' : ' || c_int_pat_ss_rec.unit_cd );
3907 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
3908 || ' : ' || TO_CHAR (c_int_pat_ss_rec.version_number) );
3909 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
3910 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('TEACHING_PERIOD','IGS_PS_LOG_PARAMETERS'));
3911 fnd_message.set_token('PARAMETER_VAL' ,p_teach_calendar);
3912 fnd_file.put_line(fnd_file.log, fnd_message.get);
3913 fnd_file.put_line ( fnd_file.LOG, 'Int Pat Id'|| ' : ' || c_int_pat_ss_rec.int_pat_id );
3914 fnd_file.put_line ( fnd_file.LOG, ' ' );
3915
3916 ELSE
3917 l_abort_flag := 'N';
3918 END IF;
3919
3920
3921 --update the interface table
3922 UPDATE igs_ps_sch_pat_int SET abort_flag = l_abort_flag
3923 WHERE ROWID =c_int_pat_ss_rec.intrid ;
3924
3925 --update the production table
3926 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 );
3927 FETCH c_prod_pat INTO c_prod_pat_rec;
3928 igs_ps_unit_ofr_pat_pkg.update_row (
3929 x_rowid => c_prod_pat_rec.prodrid ,
3930 x_unit_cd => c_prod_pat_rec.unit_cd,
3931 x_version_number => c_prod_pat_rec.version_number,
3932 x_ci_sequence_number => c_prod_pat_rec.ci_sequence_number,
3933 x_cal_type => c_prod_pat_rec.cal_type,
3934 x_ci_start_dt => c_prod_pat_rec.ci_start_dt,
3935 x_ci_end_dt => c_prod_pat_rec.ci_end_dt,
3936 x_waitlist_allowed => c_prod_pat_rec.waitlist_allowed,
3937 x_max_students_per_waitlist => c_prod_pat_rec.max_students_per_waitlist,
3938 x_mode => 'R' ,
3939 x_delete_flag => c_prod_pat_rec.delete_flag,
3940 x_abort_flag => l_abort_flag
3941 );
3942 CLOSE c_prod_pat;
3943 l_proc_count := l_proc_count +1;
3944 END LOOP;
3945
3946 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_PROCESS');
3947 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('PATTERNS','IGS_PS_TABLE_NAME'));
3948 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_proc_count );
3949 fnd_message.set_name('IGS','IGS_PS_TOT_RECORDS_ABORTED');
3950 fnd_message.set_token('TABLE',igs_ps_validate_lgcy_pkg.get_lkup_meaning('PATTERNS','IGS_PS_TABLE_NAME'));
3951 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get||l_abort_count );
3952
3953 EXCEPTION
3954 WHEN OTHERS THEN
3955 RETCODE:=2;
3956 fnd_file.put_line ( fnd_file.LOG, ' ');
3957 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
3958 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3959 ROLLBACK;
3960 END abort_sched;
3961
3962
3963
3964 PROCEDURE purge_schd_record(
3965 p_c_cal_type IN VARCHAR2,
3966 p_n_seq_num IN NUMBER
3967 ) AS
3968 /**********************************************************************
3969 Created By : jbegum
3970
3971 Date Created On : 07-Apr-2003
3972
3973 Purpose : To purge records from scheduling interface tables.
3974
3975 Know limitations, enhancements or remarks
3976
3977 Change History
3978 Who When What
3979 (reverse chronological order - newest change first)
3980 ************************************************************************/
3981
3982 CURSOR cur_config (cp_teaching_calendar_type IN igs_ps_sch_prg_cfig.teaching_calendar_type%TYPE) IS
3983 SELECT purge_type
3984 FROM igs_ps_sch_prg_cfig
3985 WHERE teaching_calendar_type=cp_teaching_calendar_type;
3986 l_teaching_calendar_type igs_ps_sch_prg_cfig.teaching_calendar_type%TYPE;
3987
3988 CURSOR cur_dates (cp_cal_type IN igs_ca_inst_all.cal_type%TYPE,
3989 cp_sequence_number IN igs_ca_inst_all.sequence_number%TYPE) IS
3990 SELECT start_dt,end_dt
3991 FROM igs_ca_inst_all
3992 WHERE cal_type=cp_cal_type
3993 AND sequence_number=cp_sequence_number;
3994
3995 -- cursor to select occurrence interface records for purging
3996 CURSOR c_uso( cp_c_cal_type igs_ps_sch_usec_int_all.calendar_type%TYPE,
3997 cp_n_seq_num igs_ps_sch_usec_int_all.sequence_number%TYPE,
3998 cp_completed VARCHAR2,
3999 cp_cancelled VARCHAR2,
4000 cp_aborted VARCHAR2) IS
4001
4002 SELECT uso.int_occurs_id int_occurs_id,uso.occurrence_identifier, us.unit_cd, us.version_number, us.unit_class, us.location_cd
4003 FROM igs_ps_sch_int_all uso, igs_ps_sch_usec_int_all us
4004 WHERE us.calendar_type= cp_c_cal_type
4005 AND us.sequence_number=cp_n_seq_num
4006 AND us.int_usec_id = uso.int_usec_id
4007 AND ((uso.transaction_type = 'COMPLETE' AND cp_completed ='Y')
4008 OR (uso.schedule_status = 'CANCELLED' AND cp_cancelled ='Y')
4009 OR (uso.abort_flag = 'Y' AND cp_aborted ='Y'))
4010 AND (uso.import_done_flag = 'Y' OR (uso.import_done_flag='N' AND (uso.abort_flag = 'Y' AND cp_aborted ='Y')));
4011
4012 -- cursor to select section interface records for purging
4013 CURSOR c_usec( cp_c_cal_type igs_ps_sch_usec_int_all.calendar_type%TYPE,
4014 cp_n_seq_num igs_ps_sch_usec_int_all.sequence_number%TYPE,
4015 cp_aborted VARCHAR2) IS
4016 SELECT us.int_usec_id, us.unit_cd, us.version_number, us.unit_class, us.location_cd
4017 FROM igs_ps_sch_usec_int_all us
4018 WHERE us.calendar_type= cp_c_cal_type
4019 AND us.sequence_number=cp_n_seq_num
4020 AND (import_done_flag = 'Y' OR (import_done_flag='N' AND (us.abort_flag = 'Y' AND cp_aborted ='Y')))
4021 AND NOT EXISTS (SELECT 'X' FROM igs_ps_sch_int_all uso WHERE uso.int_usec_id=us.int_usec_id) ;
4022
4023
4024 -- cursor to select section interface records for purging
4025 CURSOR c_pat( cp_c_cal_type igs_ps_sch_usec_int_all.calendar_type%TYPE,
4026 cp_n_seq_num igs_ps_sch_usec_int_all.sequence_number%TYPE,
4027 cp_aborted VARCHAR2) IS
4028 SELECT pat.int_pat_id, pat.unit_cd,pat.version_number
4029 FROM igs_ps_sch_pat_int pat
4030 WHERE pat.calendar_type= cp_c_cal_type
4031 AND pat.sequence_number=cp_n_seq_num
4032 AND (import_done_flag = 'Y' OR (import_done_flag='N' AND (pat.abort_flag = 'Y' AND cp_aborted ='Y')))
4033 AND NOT EXISTS (SELECT 'X' FROM igs_ps_sch_usec_int_all us WHERE us.int_pat_id=pat.int_pat_id) ;
4034
4035 CURSOR c_header IS
4036 SELECT hdr.transaction_id
4037 FROM igs_ps_sch_hdr_int hdr
4038 WHERE NOT EXISTS ( SELECT 'X' FROM igs_ps_sch_pat_int pat WHERE pat.transaction_id = hdr.transaction_id);
4039
4040
4041 l_start_date DATE;
4042 l_end_date DATE;
4043 l_completed VARCHAR2(1);
4044 l_cancelled VARCHAR2(1);
4045 l_aborted VARCHAR2(1);
4046
4047 l_c_occurrence_exists BOOLEAN :=FALSE;
4048 l_c_section_exists BOOLEAN :=FALSE;
4049 l_c_pat_exists BOOLEAN :=FALSE;
4050
4051 BEGIN
4052 l_completed:='N';
4053 l_cancelled :='N';
4054 l_aborted:= 'N';
4055
4056 OPEN cur_dates( p_c_cal_type, p_n_seq_num);
4057 FETCH cur_dates INTO l_start_date,l_end_date;
4058 CLOSE cur_dates;
4059
4060 IF TRUNC(l_end_date) < TRUNC(SYSDATE) THEN
4061 l_teaching_calendar_type := 'PAST';
4062 ELSIF TRUNC(l_start_date) > TRUNC(SYSDATE) THEN
4063 l_teaching_calendar_type := 'FUTURE';
4064 ELSE
4065 l_teaching_calendar_type := 'PRESENT';
4066 END IF;
4067
4068 FOR cur_config_rec IN cur_config(l_teaching_calendar_type) LOOP
4069 IF cur_config_rec.purge_type = 'COMPLETED' THEN
4070 l_completed := 'Y';
4071 ELSIF cur_config_rec.purge_type = 'CANCELLED' THEN
4072 l_cancelled :='Y';
4073 ELSIF cur_config_rec.purge_type = 'ABORTED' THEN
4074 l_aborted := 'Y';
4075 END IF;
4076 END LOOP;
4077
4078 /*Log the Teaching Calendar information */
4079 fnd_file.put_line(fnd_file.log,' ');
4080 fnd_file.put_line(fnd_file.log,' ');
4081 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
4082 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('CAL_TYPE','LEGACY_TOKENS'));
4083 fnd_message.set_token('PARAMETER_VAL' ,p_c_cal_type);
4084 fnd_file.put_line(fnd_file.log,fnd_message.get);
4085 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
4086 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('START_DT','IGS_FI_LOCKBOX'));
4087 fnd_message.set_token('PARAMETER_VAL' ,TO_CHAR(TRUNC(l_start_date)));
4088 fnd_file.put_line(fnd_file.log,fnd_message.get);
4089 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
4090 fnd_message.set_token('PARAMETER_NAME',igs_ps_validate_lgcy_pkg.get_lkup_meaning('END_DT','IGS_FI_LOCKBOX'));
4091 fnd_message.set_token('PARAMETER_VAL' ,TO_CHAR(TRUNC(l_end_date)));
4092 fnd_file.put_line(fnd_file.log,fnd_message.get);
4093 fnd_file.put_line(fnd_file.log,'--------------------------------------------------------');
4094 fnd_file.put_line(fnd_file.log,' ');
4095 fnd_file.put_line(fnd_file.log,' ');
4096
4097
4098 --If purge type setup is there then only process else return
4099 IF l_completed = 'Y' OR l_cancelled = 'Y' OR l_aborted = 'Y' THEN
4100
4101 /********************** Purging the Occurrence Records***************************/
4102 l_c_occurrence_exists:=FALSE;
4103 FOR rec_uso IN c_uso(p_c_cal_type,p_n_seq_num,l_completed,l_cancelled,l_aborted) LOOP
4104
4105 IF NOT l_c_occurrence_exists THEN
4106 fnd_file.put_line ( fnd_file.LOG,'Purging The Following Occurrences:' );
4107 fnd_file.put_line(fnd_file.log,' ');
4108 l_c_occurrence_exists:=TRUE;
4109 END IF;
4110
4111 -- Deleting the child of the occurrence
4112 DELETE FROM igs_ps_prefs_sch_int_all WHERE int_occurs_id=rec_uso.int_occurs_id;
4113 DELETE FROM igs_ps_sch_faclt_all WHERE int_occurs_id=rec_uso.int_occurs_id;
4114 DELETE FROM igs_ps_sch_instr_all WHERE int_occurs_id=rec_uso.int_occurs_id;
4115
4116 --Deleting the occurrence record
4117 DELETE FROM igs_ps_sch_int_all WHERE int_occurs_id=rec_uso.int_occurs_id;
4118
4119 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
4120 || ' : ' || rec_uso.unit_cd );
4121 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
4122 || ' : ' || TO_CHAR (rec_uso.version_number) );
4123 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
4124 || ' : ' || rec_uso.location_cd );
4125 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CLASS' )
4126 || ' : ' || rec_uso.unit_class );
4127
4128 fnd_file.put_line ( fnd_file.LOG,igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'USEC_OCCRS_ID' )
4129 || ' : ' || rec_uso.occurrence_identifier );
4130 fnd_file.put_line(fnd_file.log,' ');
4131
4132
4133 END LOOP;
4134
4135 IF NOT l_c_occurrence_exists THEN
4136 fnd_file.put_line ( fnd_file.LOG,'No Occurrence record to be purged' );
4137 fnd_file.put_line(fnd_file.log,' ');
4138 END IF;
4139
4140 /********************** Purging the Section Records***************************/
4141 l_c_section_exists:=FALSE;
4142 FOR rec_usec IN c_usec(p_c_cal_type,p_n_seq_num,l_aborted) LOOP
4143
4144 IF NOT l_c_section_exists THEN
4145 fnd_file.put_line ( fnd_file.LOG,'Purging The Following Sections:' );
4146 fnd_file.put_line(fnd_file.log,' ');
4147 l_c_section_exists:=TRUE;
4148 END IF;
4149
4150
4151 -- Deleting the child of unit section
4152 DELETE FROM igs_ps_sch_x_usec_int_all WHERE int_usec_id=rec_usec.int_usec_id;
4153 DELETE FROM igs_ps_sch_mwc_all WHERE int_usec_id=rec_usec.int_usec_id;
4154
4155 -- Deleting unit section record
4156 DELETE FROM igs_ps_sch_usec_int_all WHERE int_usec_id=rec_usec.int_usec_id;
4157
4158
4159 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
4160 || ' : ' || rec_usec.unit_cd );
4161 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
4162 || ' : ' || TO_CHAR (rec_usec.version_number) );
4163 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
4164 || ' : ' || rec_usec.location_cd );
4165 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CLASS' )
4166 || ' : ' || rec_usec.unit_class );
4167 fnd_file.put_line ( fnd_file.LOG,' ' );
4168
4169
4170 END LOOP;
4171
4172 IF NOT l_c_section_exists THEN
4173 fnd_file.put_line ( fnd_file.LOG,'No Section record to be purged' );
4174 fnd_file.put_line(fnd_file.log,' ');
4175 END IF;
4176
4177
4178 /********************** Purging the pattern Records***************************/
4179 l_c_pat_exists:=FALSE;
4180 FOR rec_pat IN c_pat(p_c_cal_type,p_n_seq_num,l_aborted) LOOP
4181
4182 IF NOT l_c_pat_exists THEN
4183 fnd_file.put_line ( fnd_file.LOG,'Purging The Following Patterns:' );
4184 fnd_file.put_line(fnd_file.log,' ');
4185 l_c_pat_exists:=TRUE;
4186 END IF;
4187
4188
4189 -- Deleting the child of patterns
4190 DELETE FROM igs_ps_sch_loc_int WHERE int_pat_id=rec_pat.int_pat_id;
4191 DELETE FROM igs_ps_sch_fac_int WHERE int_pat_id=rec_pat.int_pat_id;
4192
4193 -- Deleting pattern record
4194 DELETE FROM igs_ps_sch_pat_int WHERE int_pat_id=rec_pat.int_pat_id;
4195
4196 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
4197 || ' : ' || rec_pat.unit_cd );
4198 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
4199 || ' : ' || TO_CHAR (rec_pat.version_number) );
4200 fnd_file.put_line ( fnd_file.LOG, ' ' );
4201
4202 END LOOP;
4203
4204 IF NOT l_c_pat_exists THEN
4205 fnd_file.put_line ( fnd_file.LOG,'No pattern record to be purged' );
4206 fnd_file.put_line(fnd_file.log,' ');
4207 END IF;
4208
4209
4210 /********************** Purging the header Records***************************/
4211 FOR rec_header IN c_header LOOP
4212
4213 -- Deleting transaction header record
4214 DELETE FROM igs_ps_sch_hdr_int_all WHERE transaction_id=rec_header.transaction_id;
4215
4216 END LOOP;
4217
4218
4219 END IF;
4220
4221 IF l_c_occurrence_exists=FALSE AND l_c_section_exists=FALSE AND l_c_pat_exists=FALSE THEN
4222 fnd_file.put_line ( fnd_file.LOG,'No records to be purged for this teaching calendar' );
4223 fnd_file.put_line(fnd_file.log,' ');
4224 END IF;
4225
4226
4227 END purge_schd_record;
4228
4229
4230 FUNCTION get_enrollment_max(
4231 p_n_uoo_id IN NUMBER
4232 ) RETURN NUMBER AS
4233
4234 /**********************************************************************
4235 Created By : jbegum
4236
4237 Date Created On : 24-Apr-2003
4238
4239 Purpose : Function to return maximum enrollment for a unit section
4240
4241 Know limitations, enhancements or remarks
4242
4243 Change History
4244 Who When What
4245 (reverse chronological order - newest change first)
4246 ************************************************************************/
4247
4248 CURSOR c_enr_max( cp_n_uoo_id igs_ps_usec_lim_wlst.uoo_id%TYPE ) IS
4249 SELECT enrollment_maximum
4250 FROM igs_ps_usec_lim_wlst
4251 WHERE uoo_id = cp_n_uoo_id;
4252
4253 l_n_enr_max igs_ps_usec_lim_wlst.enrollment_maximum%TYPE;
4254
4255 BEGIN
4256
4257 OPEN c_enr_max(p_n_uoo_id);
4258 FETCH c_enr_max INTO l_n_enr_max;
4259 CLOSE c_enr_max;
4260
4261 l_n_enr_max := NVL(l_n_enr_max,999999);
4262
4263 RETURN l_n_enr_max;
4264
4265 END get_enrollment_max;
4266
4267 PROCEDURE log_messages ( p_msg_name IN VARCHAR2,
4268 p_msg_val IN VARCHAR2,
4269 p_val IN NUMBER
4270 ) AS
4271 /**********************************************************************
4272 Created By : jbegum
4273
4274 Date Created On : 15-Apr-2003
4275
4276 Purpose : This procedure is private to this package body .
4277 The procedure logs transferred information to the log file.
4278
4279 Know limitations, enhancements or remarks
4280
4281 Change History
4282 Who When What
4283 (reverse chronological order - newest change first)
4284 ************************************************************************/
4285
4286 l_c_str VARCHAR2(50);
4287
4288 BEGIN
4289
4290 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
4291 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
4292 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val);
4293 FOR i IN 1..p_val LOOP
4294 l_c_str := l_c_str || ' ';
4295 END LOOP;
4296 FND_FILE.PUT_LINE(FND_FILE.LOG,l_c_str||FND_MESSAGE.GET);
4297
4298 END log_messages;
4299
4300 PROCEDURE log_teach_cal (p_c_cal_type IN VARCHAR2,
4301 p_n_ci_sequence_number IN NUMBER) AS
4302 /**********************************************************************
4303 Created By : jbegum
4304
4305 Date Created On : 15-Apr-2003
4306
4307 Purpose : To log alternate code of teaching calendar.
4308
4309 Know limitations, enhancements or remarks
4310
4311 Change History
4312 Who When What
4313 (reverse chronological order - newest change first)
4314 ************************************************************************/
4315
4316 CURSOR c_alt_cd(cp_c_cal_type igs_ca_inst.cal_type%TYPE,cp_n_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
4317 SELECT alternate_code
4318 FROM igs_ca_inst
4319 WHERE cal_type = cp_c_cal_type
4320 AND sequence_number = cp_n_ci_sequence_number;
4321
4322 l_alt_code igs_ca_inst.alternate_code%TYPE;
4323
4324 BEGIN
4325
4326 OPEN c_alt_cd(p_c_cal_type,p_n_ci_sequence_number);
4327 FETCH c_alt_cd INTO l_alt_code;
4328 CLOSE c_alt_cd;
4329
4330 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4331 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('TEACHING','CAL_CAT'),l_alt_code,3);
4332
4333 END log_teach_cal;
4334
4335 PROCEDURE log_usec_details (p_c_unit_cd IN VARCHAR2,
4336 p_n_version_number IN NUMBER,
4337 p_c_location_description IN VARCHAR2,
4338 p_c_unit_class IN VARCHAR2,
4339 p_n_enrollment_maximum IN NUMBER) AS
4340 /**********************************************************************
4341 Created By : jbegum
4342
4343 Date Created On : 15-Apr-2003
4344
4345 Purpose : To log transferred unit section details
4346
4347 Know limitations, enhancements or remarks
4348
4349 Change History
4350 Who When What
4351 (reverse chronological order - newest change first)
4352 ************************************************************************/
4353
4354 BEGIN
4355
4356 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4357 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),p_c_unit_cd,10);
4358 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('VERSION_NUMBER','IGS_PS_LOG_PARAMETERS'),p_n_version_number,10);
4359 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('LOC','IGS_FI_ACCT_ENTITIES'),p_c_location_description,10);
4360 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CLASS','LEGACY_TOKENS'),p_c_unit_class,10);
4361 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('ENROLLMENT_MAXIMUM','LEGACY_TOKENS'),p_n_enrollment_maximum,10);
4362
4363
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
4373 Date Created On : 15-Apr-2003
4374
4375 Purpose : To log transferred unit section occurrences details
4376
4377 Know limitations, enhancements or remarks
4378
4379 Change History
4380 Who When What
4381 (reverse chronological order - newest change first)
4382 sarakshi 12-Jan-2006 Bug#4926548, created cursors bld_desc,rom_desc and cur_lookup_meaning and used them appropriately
4383 ************************************************************************/
4384
4385 CURSOR c_ins_name(cp_n_lead_instructor_id igs_pe_person_base_v.person_id%TYPE) IS
4386 SELECT first_name , last_name
4387 FROM igs_pe_person_base_v
4388 WHERE person_id = cp_n_lead_instructor_id;
4389
4390 rec_ins_name c_ins_name%ROWTYPE;
4391 l_str VARCHAR2(1000);
4392
4393 CURSOR bld_desc(cp_bld_code igs_ad_building_all.building_id%TYPE) IS
4394 SELECT description
4395 FROM igs_ad_building_all
4396 WHERE building_id = cp_bld_code;
4397
4398 CURSOR rom_desc(cp_rom_code igs_ad_room_all.room_id%TYPE) IS
4399 SELECT description
4400 FROM igs_ad_room_all
4401 WHERE room_id = cp_rom_code;
4402 l_sch_bld_desc igs_ad_building_all.description%TYPE;
4403 l_sch_rom_desc igs_ad_room_all.description%TYPE;
4404 l_ded_bld_desc igs_ad_building_all.description%TYPE;
4405 l_ded_rom_desc igs_ad_room_all.description%TYPE;
4406 l_prf_bld_desc igs_ad_building_all.description%TYPE;
4407 l_prf_rom_desc igs_ad_room_all.description%TYPE;
4408
4409 CURSOR cur_lookup_meaning (cp_lookup_type igs_lookup_values.lookup_type%TYPE,
4410 cp_lookup_code igs_lookup_values.lookup_code%TYPE) IS
4411 SELECT meaning
4412 FROM igs_lookup_values
4413 WHERE lookup_type=cp_lookup_type
4414 AND lookup_code=cp_lookup_code;
4415 l_prf_reg_desc igs_lookup_values.meaning%TYPE;
4416 l_sch_status_desc igs_lookup_values.meaning%TYPE;
4417
4418 BEGIN
4419
4420 OPEN c_ins_name(p_n_lead_instructor_id);
4421 FETCH c_ins_name INTO rec_ins_name;
4422 CLOSE c_ins_name;
4423
4424 IF p_usec_occur_rec.monday = 'Y' THEN
4425 l_str := igs_ps_validate_lgcy_pkg.get_lkup_meaning('MONDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4426 END IF;
4427
4428 IF p_usec_occur_rec.tuesday = 'Y' THEN
4429 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('TUESDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4430 END IF;
4431
4432 IF p_usec_occur_rec.wednesday = 'Y' THEN
4433 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('WEDNESDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4434 END IF;
4435
4436 IF p_usec_occur_rec.thursday = 'Y' THEN
4437 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('THURSDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4438 END IF;
4439
4440 IF p_usec_occur_rec.friday = 'Y' THEN
4441 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('FRIDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4442 END IF;
4443
4444 IF p_usec_occur_rec.saturday = 'Y' THEN
4445 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('SATURDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4446 END IF;
4447
4448 IF p_usec_occur_rec.sunday = 'Y' THEN
4449 l_str := l_str || igs_ps_validate_lgcy_pkg.get_lkup_meaning('SUNDAY','DT_OFFSET_CONSTRAINT_TYPE')||' ';
4450 END IF;
4451
4452 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4453 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('USEC_OCCUR_ID','IGS_PS_LOG_PARAMETERS'),p_usec_occur_rec.unit_section_occurrence_id,20);
4454 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('TRANSACTION_TYPE','IGS_PS_LOG_PARAMETERS'),p_c_trans_type,20);
4455
4456 OPEN cur_lookup_meaning('SCHEDULE_TYPE',p_usec_occur_rec.schedule_status);
4457 FETCH cur_lookup_meaning INTO l_sch_status_desc;
4458 CLOSE cur_lookup_meaning;
4459 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('SCHEDULE_STATUS','IGS_PS_LOG_PARAMETERS'),l_sch_status_desc,20);
4460
4461 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('USEC_OCCUR_DATES','IGS_PS_LOG_PARAMETERS'),
4462 TO_CHAR(p_usec_occur_rec.start_date,'DD MON YYYY')||' - '||TO_CHAR(p_usec_occur_rec.end_date,'DD MON YYYY'),20);
4463 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('TBA_IND','IGS_PS_LOG_PARAMETERS'),NVL(p_usec_occur_rec.to_be_announced,'N'),20);
4464 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('DAYS','IGS_PS_PROGRAM_LENGTH_MESR'),l_str,20);
4465 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('TIME','IGS_PS_LOG_PARAMETERS'),
4466 TO_CHAR(p_usec_occur_rec.start_time,'HH24:MIam')||' - '||TO_CHAR(p_usec_occur_rec.end_time,'HH24:MIam'),20);
4467 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('INSTRUCTOR_NAME','IGS_AS_ADV_SEARCH'),rec_ins_name.first_name||' '||rec_ins_name.last_name,20);
4468
4469 -- Following transferred information is printed in log file if procedure log_usec_occurs is called from
4470 -- Initiate Scheduling Unit Section Occurrence process
4471 IF p_c_call = 'I' THEN
4472 OPEN cur_lookup_meaning('IGS_OR_LOC_REGION',p_usec_occur_rec.preferred_region_code);
4473 FETCH cur_lookup_meaning INTO l_prf_reg_desc;
4474 CLOSE cur_lookup_meaning;
4475 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('IGSPS130-CG$TABPAGE_2','GE_CFG_TAB'),l_prf_reg_desc,20);
4476 END IF;
4477
4478 OPEN bld_desc(p_usec_occur_rec.building_code);
4479 FETCH bld_desc INTO l_sch_bld_desc;
4480 CLOSE bld_desc;
4481
4482 OPEN rom_desc(p_usec_occur_rec.room_code);
4483 FETCH rom_desc INTO l_sch_rom_desc;
4484 CLOSE rom_desc;
4485
4486
4487 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('SCHEDULED_BLD','IGS_PS_LOG_PARAMETERS'),l_sch_bld_desc,20);
4488 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('SCHEDULED_ROOM','IGS_PS_LOG_PARAMETERS'),l_sch_rom_desc,20);
4489
4490 -- Following transferred information is printed in log file if procedure log_usec_occurs is called from
4491 -- Initiate Scheduling Unit Section Occurrence process
4492 IF p_c_call = 'I' THEN
4493 OPEN bld_desc(p_usec_occur_rec.dedicated_building_code);
4494 FETCH bld_desc INTO l_ded_bld_desc;
4495 CLOSE bld_desc;
4496
4497 OPEN rom_desc(p_usec_occur_rec.dedicated_room_code);
4498 FETCH rom_desc INTO l_ded_rom_desc;
4499 CLOSE rom_desc;
4500 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('DEDICATED_BLD','IGS_PS_LOG_PARAMETERS'),l_ded_bld_desc,20);
4501 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('DEDICATED_ROOM','IGS_PS_LOG_PARAMETERS'),l_ded_rom_desc,20);
4502
4503 OPEN bld_desc(p_usec_occur_rec.preferred_building_code);
4504 FETCH bld_desc INTO l_prf_bld_desc;
4505 CLOSE bld_desc;
4506
4507 OPEN rom_desc(p_usec_occur_rec.preferred_room_code);
4508 FETCH rom_desc INTO l_prf_rom_desc;
4509 CLOSE rom_desc;
4510 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('PREFERRED_BLD','IGS_PS_LOG_PARAMETERS'),l_prf_bld_desc,20);
4511 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('PREFERRED_ROOM','IGS_PS_LOG_PARAMETERS'),l_prf_rom_desc,20);
4512 END IF;
4513
4514 -- Following transferred information is printed in log file if procedure log_usec_occurs is called from
4515 -- Get Interface Scheduled data process
4516 IF p_c_call = 'G' THEN
4517 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('ERROR_TEXT','IGS_PS_LOG_PARAMETERS'),p_usec_occur_rec.error_text,20);
4518 END IF;
4519
4520 END log_usec_occurs;
4521
4522 FUNCTION get_alternate_code (p_c_cal_type IN VARCHAR2,
4523 p_n_seq_num IN NUMBER)RETURN VARCHAR2 AS
4524
4525 CURSOR c_alt_code (cp_c_cal_type igs_ca_inst_all.cal_type%TYPE, cp_n_seq_num igs_ca_inst_all.sequence_number%TYPE) IS
4526 SELECT alternate_code
4527 FROM IGS_CA_INST_ALL
4528 WHERE cal_type = cp_c_cal_type
4529 AND sequence_number = cp_n_seq_num;
4530
4531 l_c_alt_code IGS_CA_INST_ALL.alternate_code%TYPE;
4532
4533 BEGIN
4534 OPEN c_alt_code(p_c_cal_type,p_n_seq_num) ;
4535 FETCH c_alt_code INTO l_c_alt_code;
4536 CLOSE c_alt_code;
4537 RETURN l_c_alt_code;
4538 END get_alternate_code;
4539
4540 PROCEDURE update_occurrence_status(
4541 p_unit_section_occurrence_id IN NUMBER,
4542 p_scheduled_status IN VARCHAR2,
4543 p_cancel_flag IN VARCHAR2
4544 ) IS
4545 /**********************************************************************
4546 Created By : sarakshi
4547
4548 Date Created On : 12-May-2005
4549
4550 Purpose : To update the schedule status to 'USER_UPDATE'
4551
4552 Know limitations, enhancements or remarks
4553
4554 Change History
4555 Who When What
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,
4574 x_tuesday => l_usec_occurs_rec.tuesday,
4575 x_wednesday => l_usec_occurs_rec.wednesday,
4576 x_thursday => l_usec_occurs_rec.thursday,
4577 x_friday => l_usec_occurs_rec.friday,
4578 x_saturday => l_usec_occurs_rec.saturday,
4579 x_sunday => l_usec_occurs_rec.sunday,
4580 x_start_time => l_usec_occurs_rec.start_time,
4581 x_end_time => l_usec_occurs_rec.end_time,
4582 x_building_code => l_usec_occurs_rec.building_code,
4583 x_room_code => l_usec_occurs_rec.room_code,
4584 x_schedule_status => p_scheduled_status,
4585 x_status_last_updated => l_usec_occurs_rec.status_last_updated,
4586 x_instructor_id => l_usec_occurs_rec.instructor_id,
4587 X_attribute_category => l_usec_occurs_rec.attribute_category,
4588 X_attribute1 => l_usec_occurs_rec.attribute1,
4589 X_attribute2 => l_usec_occurs_rec.attribute2,
4590 X_attribute3 => l_usec_occurs_rec.attribute3,
4591 X_attribute4 => l_usec_occurs_rec.attribute4,
4592 X_attribute5 => l_usec_occurs_rec.attribute5,
4593 X_attribute6 => l_usec_occurs_rec.attribute6,
4594 X_attribute7 => l_usec_occurs_rec.attribute7,
4595 X_attribute8 => l_usec_occurs_rec.attribute8,
4596 X_attribute9 => l_usec_occurs_rec.attribute9,
4597 X_attribute10 => l_usec_occurs_rec.attribute10,
4598 X_attribute11 => l_usec_occurs_rec.attribute11,
4599 X_attribute12 => l_usec_occurs_rec.attribute12,
4600 X_attribute13 => l_usec_occurs_rec.attribute13,
4601 X_attribute14 => l_usec_occurs_rec.attribute14,
4602 X_attribute15 => l_usec_occurs_rec.attribute15,
4603 X_attribute16 => l_usec_occurs_rec.attribute16,
4604 X_attribute17 => l_usec_occurs_rec.attribute17,
4605 X_attribute18 => l_usec_occurs_rec.attribute18,
4606 X_attribute19 => l_usec_occurs_rec.attribute19,
4607 X_attribute20 => l_usec_occurs_rec.attribute20,
4608 x_error_text => l_usec_occurs_rec.error_text,
4609 x_mode => 'R',
4610 X_start_date => l_usec_occurs_rec.start_date,
4611 X_end_date => l_usec_occurs_rec.end_date,
4612 X_to_be_announced => l_usec_occurs_rec.to_be_announced,
4613 x_dedicated_building_code => l_usec_occurs_rec.dedicated_building_code,
4614 x_dedicated_room_code => l_usec_occurs_rec.dedicated_room_code,
4615 x_preferred_building_code => l_usec_occurs_rec.preferred_building_code,
4616 x_preferred_room_code => l_usec_occurs_rec.preferred_room_code,
4617 x_inst_notify_ind => l_usec_occurs_rec.inst_notify_ind,
4618 x_notify_status => l_usec_occurs_rec.notify_status,
4619 x_preferred_region_code => l_usec_occurs_rec.preferred_region_code,
4620 x_no_set_day_ind => l_usec_occurs_rec.no_set_day_ind,
4621 x_cancel_flag => p_cancel_flag,
4622 x_occurrence_identifier => l_usec_occurs_rec.occurrence_identifier,
4623 x_abort_flag => l_usec_occurs_rec.abort_flag
4624 );
4625
4626 EXCEPTION
4627 WHEN OTHERS THEN
4628 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
4629 FND_MESSAGE.SET_TOKEN('NAME','prgp_upd_usec_dtls:igs_ps_usec_schedule');
4630 IGS_GE_MSG_STACK.ADD;
4631 APP_EXCEPTION.RAISE_EXCEPTION;
4632 END;
4633
4634 END igs_ps_usec_schedule;