1 PACKAGE BODY igs_ps_rlovr_fac_tsk AS
2 /* $Header: IGSPS83B.pls 120.5 2006/05/01 07:33:22 sommukhe noship $ */
3
4 --who when what
5 --
6 --============================================================================
7
8
9 FUNCTION crsp_chk_inst_time_conft(
10 p_start_dt_1 IN DATE ,
11 p_end_dt_1 IN DATE,
12 p_monday_1 IN VARCHAR2 ,
13 p_tuesday_1 IN VARCHAR2 ,
14 p_wednesday_1 IN VARCHAR2 ,
15 p_thursday_1 IN VARCHAR2 ,
16 p_friday_1 IN VARCHAR2 ,
17 p_saturday_1 IN VARCHAR2 ,
18 p_sunday_1 IN VARCHAR2 ,
19 p_start_dt_2 IN DATE ,
20 p_end_dt_2 IN DATE,
21 p_monday_2 IN VARCHAR2 ,
22 p_tuesday_2 IN VARCHAR2 ,
23 p_wednesday_2 IN VARCHAR2 ,
24 p_thursday_2 IN VARCHAR2 ,
25 p_friday_2 IN VARCHAR2 ,
26 p_saturday_2 IN VARCHAR2 ,
27 p_sunday_2 IN VARCHAR2
28 ) RETURN BOOLEAN AS
29 --------------------------------------------------------------------------------
30 --Created by : smaddali ( Oracle IDC)
31 --Date created: 21-JAN-2002
32 --
33 --Purpose: This function will check wether the two unit section ocurences passed are
34 -- overlapping at instance level ( ie actual calendar day is overlapping)
35 --if yes return true else false
36 --
37 --Known limitations/enhancements and/or remarks:
38 --
39 --Change History:
40 --Who When What
41 --
42 ------------------------------------------------------------------------------
43 l_overlap_mon VARCHAR2(3) DEFAULT NULL;
44 l_overlap_tue VARCHAR2(3) DEFAULT NULL;
45 l_overlap_wed VARCHAR2(3) DEFAULT NULL;
46 l_overlap_thu VARCHAR2(3) DEFAULT NULL;
47 l_overlap_fri VARCHAR2(3) DEFAULT NULL;
48 l_overlap_sat VARCHAR2(3) DEFAULT NULL;
49 l_overlap_sun VARCHAR2(3) DEFAULT NULL;
50 l_overlap_start_date DATE ;
51 l_overlap_end_date DATE ;
52 l_loop_date_cntr DATE ;
53
54 BEGIN
55 -- Capture all the days where both unit section occurences are meeting
56 IF p_monday_1 ='Y' AND p_monday_2 ='Y' THEN
57 l_overlap_mon := 'MON' ;
58 END IF;
59 IF p_tuesday_1 ='Y' AND p_tuesday_2 ='Y' THEN
60 l_overlap_tue := 'TUE' ;
61 END IF;
62 IF p_wednesday_1 ='Y' AND p_wednesday_2 ='Y' THEN
63 l_overlap_wed := 'WED' ;
64 END IF;
65 IF p_thursday_1 ='Y' AND p_thursday_2 ='Y' THEN
66 l_overlap_thu := 'THU' ;
67 END IF;
68 IF p_friday_1 ='Y' AND p_friday_2 ='Y' THEN
69 l_overlap_fri := 'FRI' ;
70 END IF;
71 IF p_saturday_1 ='Y' AND p_saturday_2 ='Y' THEN
72 l_overlap_sat := 'SAT' ;
73 END IF;
74 IF p_sunday_1 ='Y' AND p_sunday_2 ='Y' THEN
75 l_overlap_sun := 'SUN' ;
76 END IF;
77
78 --Determine the start date and end date of the overlap period
79 --following are the possible scenarios of overlap
80 IF (p_start_dt_1 <= p_start_dt_2 AND p_end_dt_1 <= p_end_dt_2) THEN
81 -- S1--------------E1
82 -- S2-------------------E2
83 l_overlap_start_date := p_start_dt_2 ;
84 l_overlap_end_date := p_end_dt_1 ;
85 ELSIF (p_start_dt_1 <= p_start_dt_2 AND p_end_dt_1 >= p_end_dt_2 ) THEN
86 -- S1----------------------E1
87 -- S2--------------E2
88 l_overlap_start_date := p_start_dt_2 ;
89 l_overlap_end_date := p_end_dt_2 ;
90 ELSIF (p_start_dt_1 >= p_start_dt_2 AND p_end_dt_1 >= p_end_dt_2 ) THEN
91 -- S1 --------------- E1
92 -- S2 -------------E2
93 l_overlap_start_date := p_start_dt_1 ;
94 l_overlap_end_date := p_end_dt_2;
95 ELSIF (p_start_dt_1 >= p_start_dt_2 AND p_end_dt_1 <= p_end_dt_2 ) THEN
96 -- S1 -----------------E1
97 --S2--------------------------E2
98 l_overlap_start_date := p_start_dt_1 ;
99 l_overlap_end_date := p_end_dt_1 ;
100 END IF;
101
102 --loop thru the overlap dates and check if the unit sections are meeting on
103 -- that day , if yes return true as conflict exists else false
104 l_loop_date_cntr := l_overlap_start_date ;
105 WHILE l_loop_date_cntr <= l_overlap_end_date LOOP
106 IF TO_CHAR(l_loop_date_cntr,'DY') IN (l_overlap_mon,l_overlap_tue,
107 l_overlap_wed,l_overlap_thu,l_overlap_fri,l_overlap_sat,
108 l_overlap_sun) THEN
109 RETURN TRUE ;
110 END IF;
111 l_loop_date_cntr := l_loop_date_cntr + 1;
112 END LOOP ;
113 RETURN FALSE ;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
118 FND_MESSAGE.SET_TOKEN('NAME','Igs_ps_rlovr_fac_tsk.crsp_chk_inst_time_conft');
119 IGS_GE_MSG_STACK.ADD;
120 App_exception.raise_exception ;
121
122 END crsp_chk_inst_time_conft;
123
124 --
125 FUNCTION crsp_instrct_time_conflct(
126 p_person_id IN NUMBER ,
127 p_unit_section_occurrence_id IN NUMBER ,
128 p_monday IN VARCHAR2 ,
129 p_tuesday IN VARCHAR2 ,
130 p_wednesday IN VARCHAR2 ,
131 p_thursday IN VARCHAR2 ,
132 p_friday IN VARCHAR2 ,
133 p_saturday IN VARCHAR2 ,
134 p_sunday IN VARCHAR2 ,
135 p_start_time IN DATE ,
136 p_end_time IN DATE ,
137 p_start_date IN DATE ,
138 p_end_date IN DATE ,
139 p_calling_module IN VARCHAR2 ,
140 p_message_name OUT NOCOPY VARCHAR2
141 ) RETURN BOOLEAN AS
142 --------------------------------------------------------------------------------
143 --Created by : smaddali ( Oracle IDC)
144 --Date created: 22-JAN-2002
145 --
146 --Purpose: This function will check wether time conflicting unit section ocurences exists
147 -- for the passed unit section occurrence
148 --if yes return false else true
149 --this function is called from the form IGSPS084 and the below procedure within this package
150 --
151 --Known limitations/enhancements and/or remarks:
152 --
153 --Change History:
154 --Who When What
155 --smvk 21-Apr-2003 Bug # 2902710. Modified the cursor cur_time_conflct to check for
156 -- unit section in meet with class instead unit section occurrence.
157 ------------------------------------------------------------------------------
158
159 l_rowid VARCHAR2(25) ;
160 l_start_time DATE ;
161 l_end_time DATE ;
162 l_time_conflct_exists BOOLEAN DEFAULT FALSE ;
163
164 -- select all the other unit section occurrences assigned to the same instructor as the passed uso
165 -- which conflict in date/day/time with the passed unit section occurrence
166 -- excluding cross listed and meeting with class group usos , which are not to be announced
167 CURSOR cur_time_conflct(cp_start_time DATE , cp_end_time DATE) IS
168 SELECT uso.row_id , uso.unit_section_occurrence_id , uso.monday,uso.tuesday,uso.wednesday,
169 uso.thursday,uso.friday,uso.saturday , uso.sunday,uso.start_date,uso.end_date
170 FROM igs_ps_usec_occurs uso ,
171 igs_ps_uso_instrctrs usoi ,
172 igs_ps_unit_ofr_opt uoo ,
173 igs_ca_inst ci
174 WHERE usoi.instructor_id = p_person_id AND
175 usoi.unit_section_occurrence_id <> p_unit_section_occurrence_id AND
176 usoi.unit_section_occurrence_id = uso.unit_section_occurrence_id AND
177 uoo.uoo_id = uso.uoo_id AND
178 uoo.cal_type = ci.cal_type AND
179 uoo.ci_sequence_number = ci.sequence_number AND
180 ( (uso.monday = p_monday AND p_monday = 'Y' ) OR
181 (uso.tuesday = p_tuesday AND p_tuesday='Y') OR
182 (uso.wednesday = p_wednesday AND p_wednesday='Y') OR
183 (uso.thursday = p_thursday AND p_thursday='Y') OR
184 (uso.friday = p_friday AND p_friday='Y') OR
185 (uso.saturday = p_saturday AND p_saturday='Y') OR
186 (uso.sunday = p_sunday AND p_sunday='Y') ) AND
187 ( (NVL(uso.start_date,NVL(uoo.unit_section_start_date,ci.start_dt)) BETWEEN p_start_date AND p_end_date) OR
188 (NVL(uso.end_date,NVL(uoo.unit_section_end_date,ci.end_dt)) BETWEEN p_start_date AND p_end_date ) OR
189 (p_start_date BETWEEN NVL(uso.start_date,NVL(uoo.unit_section_start_date,ci.start_dt)) AND
190 NVL(uso.end_date,NVL(uoo.unit_section_end_date,ci.end_dt)))
191 ) AND
192 ( (TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') BETWEEN cp_start_time AND cp_end_time) OR
193 (TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') BETWEEN cp_start_time AND cp_end_time) OR
194 (cp_start_time BETWEEN TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') AND
195 TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') )
196 ) AND
197 -- considering boundary conditions as no conflict
198 ( (TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') <> cp_end_time) AND
199 (TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') <> cp_start_time )
200 ) AND
201 NVL(uso.to_be_announced,'N') = 'N' AND
202 NOT EXISTS (SELECT 'x' FROM igs_ps_uso_clas_meet ucm
203 WHERE ucm.uoo_id = uso.uoo_id ) AND
204 NOT EXISTS (SELECT 'x' FROM igs_ps_usec_x_grpmem uxg
205 WHERE uxg.uoo_id = uso.uoo_id)
206 ORDER BY uso.row_id asc;
207
208 --check if the conflicting record already exists in the temp table before inserting it.
209 -- here if we r trying to insert usec_occur_id1 and 2 then we check to see if
210 -- usec_occur2 , 1 already exists ,because order doesn't matter
211 CURSOR cur_tmp_exists(cp_usec_occur_id2 igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
212 SELECT 'x' FROM igs_ps_fac_tcft_tmp
213 WHERE person_id = p_person_id AND
214 usec_occur_id1 = cp_usec_occur_id2 AND
215 usec_occur_id2 = p_unit_section_occurrence_id ;
216 cur_tmp_exists_rec cur_tmp_exists%ROWTYPE ;
217
218 BEGIN
219 -- format the dates
220 --
221 l_start_time := TO_DATE(TO_CHAR(p_start_time,'HH24:MI'),'HH24:MI') ;
222 l_end_time := TO_DATE(TO_CHAR(p_end_time,'HH24:MI'),'HH24:MI') ;
223
224 -- for each of the conflicting unit section occurrences loop
225 --
226 FOR cur_time_conflct_rec IN cur_time_conflct(l_start_time,l_end_time) LOOP
227 -- check if time conflict exists at calendar date instance level
228 --
229 IF NOT crsp_chk_inst_time_conft( p_start_dt_1 => p_start_date,
230 p_end_dt_1 => p_end_date,
231 p_monday_1 => p_monday,
232 p_tuesday_1 => p_tuesday,
233 p_wednesday_1 => p_wednesday,
234 p_thursday_1 => p_thursday ,
235 p_friday_1 => p_friday,
236 p_saturday_1 => p_saturday,
237 p_sunday_1 => p_sunday,
238 p_start_dt_2 => cur_time_conflct_rec.start_date ,
239 p_end_dt_2 => cur_time_conflct_rec.end_date,
240 p_monday_2 => cur_time_conflct_rec.monday ,
241 p_tuesday_2 => cur_time_conflct_rec.tuesday,
242 p_wednesday_2 => cur_time_conflct_rec.wednesday,
243 p_thursday_2 => cur_time_conflct_rec.thursday,
244 p_friday_2 => cur_time_conflct_rec.friday,
245 p_saturday_2 => cur_time_conflct_rec.saturday,
246 p_sunday_2 => cur_time_conflct_rec.sunday ) THEN
247 NULL ;
248 ELSE
249 -- if conflict exists then
250 --
251 l_time_conflct_exists := TRUE ;
252 --if form is calling this function then return false
253 --
254 IF p_calling_module = 'FORM' THEN
255 p_message_name := 'IGS_PS_TIME_CONFLCT_EXIST' ;
256 RETURN FALSE ;
257 -- if report is calling this function then insert the conflicting record
258 --in the temporary table, from where the report displays it
259 --
260 ELSIF p_calling_module = 'REPORT' THEN
261 -- check if the record doesn't already exist then insert
262 --here order does not matter so u1 , u2 and u2 , u1 both should not exist
263 OPEN cur_tmp_exists(cur_time_conflct_rec.unit_section_occurrence_id ) ;
264 FETCH cur_tmp_exists INTO cur_tmp_exists_rec ;
265 IF cur_tmp_exists%NOTFOUND THEN
266
267 igs_ps_fac_tcft_tmp_pkg.insert_row (
268 x_rowid => l_rowid ,
269 x_person_id => p_person_id ,
270 x_usec_occur_id1 => p_unit_section_occurrence_id ,
271 x_usec_occur_id2 => cur_time_conflct_rec.unit_section_occurrence_id ,
272 x_mode => 'R'
273 );
274 END IF;
275 CLOSE cur_tmp_exists ;
276 ELSE
277 RETURN FALSE ;
278 END IF ;
279 END IF; -- end function call
280 END LOOP ;
281 --if time conflict exists then return false else return true
282 IF l_time_conflct_exists THEN
283 RETURN FALSE ;
284 END IF;
285 RETURN TRUE ;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 IF cur_tmp_exists%ISOPEN THEN
290 CLOSE cur_tmp_exists ;
291 END IF;
292 IF cur_time_conflct%ISOPEN THEN
293 CLOSE cur_time_conflct ;
294 END IF;
295 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
296 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_RLOVR_FAC_TSK.crsp_instrct_time_conflct');
297 IGS_GE_MSG_STACK.ADD;
298 App_exception.raise_exception ;
299
300 END crsp_instrct_time_conflct;
301
302 --
303 PROCEDURE crsp_prc_inst_time_cft(
304 p_person_id IN NUMBER ,
305 p_cal_type IN VARCHAR2 ,
306 p_sequence_number IN NUMBER
307 ) AS
308 --------------------------------------------------------------------------------
309 --Created by : smaddali ( Oracle IDC)
310 --Date created: 21-JAN-2002
311 --
312 --Purpose: This procedure will report all the time conflicting unit section ocurences for the
313 -- passed instructor(or all instrcutors) . the report IGSPSS12 will use the temp table
314 -- where these recs are inserted
315 --this procedure is called from report IGSPSS12
316 --
317 --Known limitations/enhancements and/or remarks:
318 --
319 --Change History:
320 --Who When What
321 --sarakshi 12-Jan-2006 Bug#4926548, modified cursor cur_inst_usec_occur to address the performance issue.
322 ------------------------------------------------------------------------------
323 -- p_person_id instructor id for which the rollover should be performed ,
324 -- run the rollover for all the instructors if null
325 -- p_cal_type and p_sequence_number is the load calendar which is mandatory
326
327 --select all the records in the temp table for deletion
328 CURSOR cur_tmp IS
329 SELECT rowid
330 FROM igs_ps_fac_tcft_tmp ;
331
332
333 TYPE teach_cal_rec IS RECORD(
334 cal_type igs_ca_inst_all.cal_type%TYPE,
335 sequence_number igs_ca_inst_all.sequence_number%TYPE
336 );
337 TYPE teachCalendar IS TABLE OF teach_cal_rec INDEX BY BINARY_INTEGER;
338 teachCalendar_tbl teachCalendar;
339 l_n_counter NUMBER(10);
340 l_c_proceed BOOLEAN ;
341
342 CURSOR cur_load_teach IS
343 SELECT teach_cal_type,teach_ci_sequence_number
344 FROM igs_ca_load_to_teach_v
345 WHERE load_cal_type = p_cal_type
346 AND load_ci_sequence_number = p_sequence_number;
347
348 --select all the unit section occurrences for the passed instructor or all instructors
349 -- whose teach cals lie within load calendar passed and to be announced = 'N'
350 CURSOR cur_inst_usec_occur IS
351 SELECT usoi.instructor_id ,
352 uso.unit_section_occurrence_id ,
353 uso.monday,
354 uso.tuesday,
355 uso.wednesday ,
356 uso.thursday ,
357 uso.friday,
358 uso.saturday ,
359 uso.sunday ,
360 NVL(uso.start_date,NVL(uoo.unit_section_start_date,ci.start_dt)) start_date ,
361 NVL(uso.end_date ,NVL(uoo.unit_section_end_date,ci.end_dt)) end_date ,
362 uso.start_time ,
363 uso.end_time,
364 uoo.cal_type,
365 uoo.ci_sequence_number
366 FROM igs_ps_usec_occurs uso,
367 igs_ps_uso_instrctrs usoi ,
368 igs_ps_unit_ofr_opt uoo ,
369 igs_ca_inst ci
370 WHERE usoi.instructor_id = p_person_id AND
371 usoi.unit_section_occurrence_id = uso.unit_section_occurrence_id AND
372 uso.uoo_id = uoo.uoo_id AND
373 uoo.cal_type = ci.cal_type AND
374 uoo.ci_sequence_number = ci.sequence_number AND
375 NVL(uso.to_be_announced,'N') = 'N' ;
376
377 -- Cursor to use when the person_id is null
378 CURSOR cur_inst_usec_occur1 IS
379 SELECT usoi.instructor_id ,
380 uso.unit_section_occurrence_id ,
381 uso.monday,
382 uso.tuesday,
383 uso.wednesday ,
384 uso.thursday ,
385 uso.friday,
386 uso.saturday ,
387 uso.sunday ,
388 NVL(uso.start_date,NVL(uoo.unit_section_start_date,ci.start_dt)) start_date ,
389 NVL(uso.end_date ,NVL(uoo.unit_section_end_date,ci.end_dt)) end_date ,
390 uso.start_time ,
391 uso.end_time,
392 uoo.cal_type,
393 uoo.ci_sequence_number
394 FROM igs_ps_usec_occurs uso,
395 igs_ps_uso_instrctrs usoi ,
396 igs_ps_unit_ofr_opt uoo ,
397 igs_ca_inst ci
398 WHERE usoi.instructor_id > -1 AND
399 usoi.unit_section_occurrence_id = uso.unit_section_occurrence_id AND
400 uso.uoo_id = uoo.uoo_id AND
401 uoo.cal_type = ci.cal_type AND
402 uoo.ci_sequence_number = ci.sequence_number AND
403 NVL(uso.to_be_announced,'N') = 'N'
404 ORDER BY usoi.instructor_id , uso.row_id asc ;
405
406 l_conflct_exists BOOLEAN DEFAULT FALSE;
407 l_message_name VARCHAR2(300);
408 BEGIN
409
410 -- delete all the records from the temp table before inserting new records
411 --
412 FOR cur_tmp_rec IN cur_tmp LOOP
413 IGS_PS_FAC_TCFT_TMP_PKG.DELETE_ROW (X_ROWID => cur_tmp_rec.rowid) ;
414 END LOOP ;
415
416 --for all the unit section occurrences for the passed instructor or all instructors check
417 --if any of them conflict with each other and insert those records
418 --
419 l_n_counter :=1;
420 FOR cur_load_teach_rec IN cur_load_teach LOOP
421 teachCalendar_tbl(l_n_counter).cal_type :=cur_load_teach_rec.teach_cal_type;
422 teachCalendar_tbl(l_n_counter).sequence_number :=cur_load_teach_rec.teach_ci_sequence_number;
423 l_n_counter:=l_n_counter+1;
424 END LOOP;
425
426 IF teachCalendar_tbl.EXISTS(1) THEN
427 IF p_person_id is NOT NULL THEN
428 FOR cur_inst_usec_occur_rec IN cur_inst_usec_occur LOOP
429 l_c_proceed:= FALSE;
430
431 FOR i IN 1..teachCalendar_tbl.last LOOP
432 IF cur_inst_usec_occur_rec.cal_type=teachCalendar_tbl(i).cal_type AND
433 cur_inst_usec_occur_rec.ci_sequence_number=teachCalendar_tbl(i).sequence_number THEN
434 l_c_proceed:= TRUE;
435 EXIT;
436 END IF;
437 END LOOP;
438
439
440 IF l_c_proceed THEN
441 l_conflct_exists := crsp_instrct_time_conflct(
442 p_person_id => cur_inst_usec_occur_rec.instructor_id ,
443 p_unit_section_occurrence_id => cur_inst_usec_occur_rec.unit_section_occurrence_id ,
444 p_monday => cur_inst_usec_occur_rec.monday ,
445 p_tuesday => cur_inst_usec_occur_rec.tuesday ,
446 p_wednesday => cur_inst_usec_occur_rec.wednesday ,
447 p_thursday => cur_inst_usec_occur_rec.thursday ,
448 p_friday => cur_inst_usec_occur_rec.friday ,
449 p_saturday => cur_inst_usec_occur_rec.saturday ,
450 p_sunday => cur_inst_usec_occur_rec.sunday ,
451 p_start_time => cur_inst_usec_occur_rec.start_time ,
452 p_end_time => cur_inst_usec_occur_rec.end_time ,
453 p_start_date => cur_inst_usec_occur_rec.start_date ,
454 p_end_date => cur_inst_usec_occur_rec.end_date ,
455 p_calling_module => 'REPORT' ,
456 p_message_name => l_message_name) ;
457 END IF;
458
459 END LOOP;
460 ELSE
461 FOR cur_inst_usec_occur_rec IN cur_inst_usec_occur1 LOOP
462 l_c_proceed:= FALSE;
463
464 FOR i IN 1..teachCalendar_tbl.last LOOP
465 IF cur_inst_usec_occur_rec.cal_type=teachCalendar_tbl(i).cal_type AND
466 cur_inst_usec_occur_rec.ci_sequence_number=teachCalendar_tbl(i).sequence_number THEN
467 l_c_proceed:= TRUE;
468 EXIT;
469 END IF;
470 END LOOP;
471
472
473 IF l_c_proceed THEN
474 l_conflct_exists := crsp_instrct_time_conflct(
475 p_person_id => cur_inst_usec_occur_rec.instructor_id ,
476 p_unit_section_occurrence_id => cur_inst_usec_occur_rec.unit_section_occurrence_id ,
477 p_monday => cur_inst_usec_occur_rec.monday ,
478 p_tuesday => cur_inst_usec_occur_rec.tuesday ,
479 p_wednesday => cur_inst_usec_occur_rec.wednesday ,
480 p_thursday => cur_inst_usec_occur_rec.thursday ,
481 p_friday => cur_inst_usec_occur_rec.friday ,
482 p_saturday => cur_inst_usec_occur_rec.saturday ,
483 p_sunday => cur_inst_usec_occur_rec.sunday ,
484 p_start_time => cur_inst_usec_occur_rec.start_time ,
485 p_end_time => cur_inst_usec_occur_rec.end_time ,
486 p_start_date => cur_inst_usec_occur_rec.start_date ,
487 p_end_date => cur_inst_usec_occur_rec.end_date ,
488 p_calling_module => 'REPORT' ,
489 p_message_name => l_message_name) ;
490 END IF;
491
492 END LOOP;
493
494 END IF;
495 teachCalendar_tbl.DELETE;
496 END IF;
497
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 IF cur_inst_usec_occur%ISOPEN THEN
502 CLOSE cur_inst_usec_occur ;
503 END IF;
504 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
505 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_RLOVR_FAC_TSK.crsp_prc_inst_time_cft');
506 IGS_GE_MSG_STACK.ADD;
507 App_exception.raise_exception ;
508 END crsp_prc_inst_time_cft;
509
510 PROCEDURE log_messages ( p_msg_name IN VARCHAR2 ,
511 p_msg_val IN VARCHAR2
512 ) IS
513 ------------------------------------------------------------------
514 --Created by : smaddali, Oracle IDC
515 --Date created:23/01/2002
516 --
517 --Purpose: This procedure is private to this package body .
518 -- The procedure logs all the parameter values ,
519 -- in the log file
520 -- called from job procedure rollover_fac_task
521 --Known limitations/enhancements and/or remarks:
522 --
523 --Change History:
524 --Who When What
525 -------------------------------------------------------------------
526 BEGIN
527
528 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
529 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
530 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
531 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
532
533 END log_messages ;
534
535
536 --
537 PROCEDURE rollover_fac_task(
538 errbuf OUT NOCOPY VARCHAR2 ,
539 retcode OUT NOCOPY NUMBER ,
540 p_person_id IN NUMBER ,
541 p_source_cal_type IN VARCHAR2 , --mandatory
542 p_dest_cal_type IN VARCHAR2 , -- mandatory
543 p_org_id IN NUMBER --mandatory
544 ) AS
545 --------------------------------------------------------------------------------
546 --Created by : smaddali ( Oracle IDC)
547 --Date created: 21-JAN-2002
548 --
549 --Purpose: This procedure is called from the job IGSPSJ11
550 --this procedure will rollover faculty workload and tasks from one calendar to the next.
551 --
552 --Known limitations/enhancements and/or remarks:
553 --
554 --Change History:
555 --Who When What
556 --sommukhe 1-May-2006 Bug #5099457, Changes included as incorporated for 4111806
557 --sommukhe 10-FEB-2006 Bug #3712546,modified the cursor cur_fac_wl also added cursors cur_fac_wl_null and cur_fac_asg_task
558 --sommukhe 24-Jan-2006 Bug #4926548,replaced igs_pe_person_v with hz_parties for cursor c_per_id
559 -- sarakshi 14-Feb-2005 Bug#4099575, obsoleted the column std_exp_wl, removed the code asssociated with it
560 ------------------------------------------------------------------------------
561 -- p_person_id instructor id for which the rollover should be performed ,
562 -- run the rollover for all the instructors if null
563 -- the source and dest calendars passed must be of the same calendar category
564 --and source is earlier than the dest
565 --
566 l_source_cal_type igs_ca_inst.cal_type%TYPE ;
567 l_dest_cal_type igs_ca_inst.cal_type%TYPE;
568 l_source_sequence_number igs_ca_inst.sequence_number%TYPE;
569 l_dest_sequence_number igs_ca_inst.sequence_number%TYPE;
570 l_source_start_date igs_ca_inst.start_dt%TYPE ;
571 l_dest_start_date igs_ca_inst.start_dt%TYPE ;
572 l_source_cal_cat igs_ca_type.s_cal_cat%TYPE ;
573 l_dest_cal_cat igs_ca_type.s_cal_cat%TYPE ;
574 l_fac_wl_id igs_ps_fac_wl.fac_wl_id%TYPE DEFAULT NULL;
575
576
577 --get the calendar category for the passed calendar
578 --
579 CURSOR cur_cal_cat(cp_cal_type igs_ca_type.cal_type%TYPE ) IS
580 SELECT s_cal_cat
581 FROM igs_ca_type
582 WHERE cal_type = cp_cal_type ;
583
584 --get all the faculty records
585 --
586 CURSOR cur_fac_wl IS
587 SELECT person_id , fac_wl_id
588 FROM igs_ps_fac_wl fw
589 WHERE fw.cal_type = l_source_cal_type AND
590 fw.ci_sequence_number = l_source_sequence_number AND
591 fw.person_id = p_person_id;
592
593
594 CURSOR cur_fac_wl_null IS
595 SELECT person_id , fac_wl_id
596 FROM igs_ps_fac_wl fw
597 WHERE fw.cal_type = l_source_cal_type
598 AND fw.ci_sequence_number = l_source_sequence_number
599 AND fw.person_id > -1
600 ORDER BY person_id ;
601
602 CURSOR cur_fac_asg_task(cp_fac_wl_id igs_ps_fac_asg_task.fac_wl_id%TYPE) IS
603 SELECT 'x' FROM igs_ps_fac_asg_task
604 WHERE fac_wl_id = cp_fac_wl_id
605 AND NVL(num_rollover_period,99) >= 1
606 AND NVL(rollover_flag,' ') <> 'S';
607 cur_fac_asg_task_rec cur_fac_asg_task%ROWTYPE;
608 -- check if the faculty record already exists for the dest calendar
609 --
610 CURSOR cur_fac_wl_exists(cp_person_id igs_ps_fac_wl.person_id%TYPE) IS
611 SELECT fac_wl_id
612 FROM igs_ps_fac_wl
613 WHERE person_id = cp_person_id AND
614 cal_type = l_dest_cal_type AND
615 ci_sequence_number = l_dest_sequence_number ;
616
617 -- check if faculty task record already exists in the destination calendar
618 --
619 CURSOR cur_fat_exists(cp_fac_wl_id igs_ps_fac_asg_task.fac_wl_id%TYPE ,
620 cp_faculty_task_type igs_ps_fac_asg_task.faculty_task_type%TYPE) IS
621 SELECT 'x'
622 FROM igs_ps_fac_asg_task
623 WHERE fac_wl_id = cp_fac_wl_id AND
624 faculty_task_type = cp_faculty_task_type ;
625 cur_fat_exists_rec cur_fat_exists%ROWTYPE ;
626
627 -- get all the assigned tasks for the faculty
628 --
629 CURSOR cur_source_tasks(cp_fac_wl_id igs_ps_fac_asg_task.fac_wl_id%TYPE) IS
630 SELECT *
631 FROM igs_ps_fac_Asg_task_v
632 WHERE fac_wl_id = cp_fac_wl_id AND
633 NVL(num_rollover_period,99) >= 1 ;
634
635 --update the source task type to set rollover_flag='S'
636 --
637 CURSOR cur_upd_src_task(cp_rowid VARCHAR2 ) IS
638 SELECT rowid,igs_ps_fac_asg_task.*
639 FROM igs_ps_fac_asg_task
640 WHERE rowid = cp_rowid
641 FOR UPDATE OF rollover_flag NOWAIT;
642
643 cur_upd_src_task_rec cur_upd_src_task%ROWTYPE ;
644
645 --get the faculty name and person number to be logged
646 --
647 CURSOR c_per_id (cp_person_id igs_pe_person_v.person_id%TYPE) IS
648 SELECT party_number person_number, party_name person_name
649 FROM hz_parties
650 WHERE party_id =cp_person_id;
651 l_per_id c_per_id%ROWTYPE;
652
653 TYPE plsql_rec IS RECORD (
654 person_id NUMBER ,
655 fac_wl_id NUMBER ) ;
656 TYPE plsql_tab IS TABLE OF plsql_rec INDEX BY BINARY_INTEGER ;
657 l_tab_person_id plsql_tab ;
658 cntr NUMBER DEFAULT 0 ;
659
660 BEGIN
661 retcode := 0;
662 savepoint a;
663 -- set org_id as in request of job
664 IGS_GE_GEN_003.set_org_id(p_org_id);
665
666 -- extract the cal type and sequence_numbers,start date from the passed parameters
667 --
668 l_source_cal_type := RTRIM(SUBSTR (p_source_cal_type, 1, 10));
669 l_source_sequence_number := TO_NUMBER(RTRIM(SUBSTR (p_source_cal_type,75,7)));
670 l_source_start_date := TRUNC(TO_DATE(SUBSTR(p_source_cal_type,12,10),'DD/MM/YY')) ;
671 l_dest_cal_type := RTRIM(SUBSTR (p_dest_cal_type, 1, 10));
672 l_dest_sequence_number := TO_NUMBER(RTRIM(SUBSTR (p_dest_cal_type,75,7))) ;
673 l_dest_start_date := TRUNC(TO_DATE(SUBSTR(p_dest_cal_type,12,10),'DD/MM/YY')) ;
674
675 /** logs all the parameters in the LOG **/
676 --
677 Fnd_Message.Set_Name('IGS','IGS_FI_ANC_LOG_PARM');
678 Fnd_File.Put_Line(Fnd_File.LOG,FND_MESSAGE.GET);
679 IF p_person_id IS NOT NULL THEN
680 OPEN c_per_id(p_person_id);
681 FETCH c_per_id INTO l_per_id;
682 CLOSE c_per_id;
683 END IF;
684 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('PERSON_NUMBER','LEGACY_TOKENS'),l_per_id.person_number);
685 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('SOURCE_CAL','IGS_PS_LOG_PARAMETERS'),p_source_cal_type);
686 log_messages(igs_ps_validate_lgcy_pkg.get_lkup_meaning('DEST_CAL','IGS_PS_LOG_PARAMETERS'),p_dest_cal_type);
687 FND_FILE.PUT_LINE(Fnd_File.LOG,' ') ;
688
689 -- get the source and dest calendar categories
690 --
691 OPEN cur_cal_cat(l_source_cal_type);
692 FETCH cur_cal_cat INTO l_source_cal_cat ;
693 CLOSE cur_cal_cat ;
694 OPEN cur_cal_cat(l_dest_cal_type) ;
695 FETCH cur_cal_cat INTO l_dest_cal_cat ;
696 CLOSE cur_cal_cat ;
697
698 -- if the source and dest calendars are not of the same category then log message to file and return
699 --
700 IF l_source_cal_cat <> l_dest_cal_cat THEN
701 retcode := 2 ;
702 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SAME_CAL_CAT');
703 FND_FILE.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET) ;
704 RETURN ;
705 END IF;
706
707 --if source calendar starts later than the dest calendar then log message in file and return
708 --
709 IF l_source_start_date >= l_dest_start_date THEN
710 retcode := 2 ;
711 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SOURCE_MORE_DEST');
712 FND_FILE.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET) ;
713 RETURN ;
714 END IF;
715
716 -- copy all the eligible faculty workload records to be rolled over ,into the plsql table
717 --
718 cntr := 0 ;
719 IF p_person_id IS NOT NULL THEN
720 FOR cur_fac_wl_rec IN cur_fac_wl LOOP
721 OPEN cur_fac_asg_task(cur_fac_wl_rec.fac_wl_id);
722 FETCH cur_fac_asg_task INTO cur_fac_asg_task_rec;
723 IF cur_fac_asg_task%FOUND THEN
724 cntr := cntr + 1 ;
725 l_tab_person_id(cntr) := cur_fac_wl_rec ;
726 END IF;
727 CLOSE cur_fac_asg_task;
728 END LOOP ;
729 ELSE
730 FOR cur_fac_wl_null_rec IN cur_fac_wl_null LOOP
731 OPEN cur_fac_asg_task(cur_fac_wl_null_rec.fac_wl_id);
732 FETCH cur_fac_asg_task INTO cur_fac_asg_task_rec;
733 IF cur_fac_asg_task%FOUND THEN
734 cntr := cntr + 1 ;
735 l_tab_person_id(cntr) := cur_fac_wl_null_rec ;
736 END IF;
737 CLOSE cur_fac_asg_task;
738 END LOOP ;
739 END IF;
740
741 -- if no records found then return after loging message
742 --
743 IF l_tab_person_id.COUNT = 0 THEN
744 retcode := 2 ;
745 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_ROLLOVER');
746 FND_FILE.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET) ;
747 RETURN ;
748 END IF;
749
750 -- log the heading
751 --
752 FND_MESSAGE.SET_NAME('IGS','IGS_PS_ROLLOVER_FAC_PROC');
753 FND_FILE.PUT_LINE(Fnd_File.LOG, Fnd_Message.GET ) ;
754
755 -- for each of the eligible faculty records rollover into dest calendar
756 -- and copy the tasks assigned to that faculty to the dest calendar also
757 --
758 FOR i IN 1 .. l_tab_person_id.COUNT LOOP
759 --
760 --log the faculty name and number being processed
761 --
762 l_fac_wl_id := NULL ;
763 OPEN c_per_id(l_tab_person_id(i).person_id);
764 FETCH c_per_id INTO l_per_id;
765 FND_MESSAGE.SET_NAME('IGS','IGS_PS_RLOVR_FACULTY');
766 FND_MESSAGE.SET_TOKEN('NUMBER',RPAD(l_per_id.person_number,30));
767 FND_MESSAGE.SET_TOKEN('NAME',l_per_id.person_name);
768 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
769 CLOSE c_per_id;
770
771 OPEN cur_fac_wl_exists(l_tab_person_id(i).person_id) ;
772 FETCH cur_fac_wl_exists INTO l_fac_wl_id ;
773 -- if the faculty record for the dest calendar doesn't exist already
774 --then insert it for the dest calendar
775 --
776 IF cur_fac_wl_exists%NOTFOUND THEN
777 DECLARE
778 l_rowid VARCHAR2(40) ;
779 BEGIN
780 IGS_PS_FAC_WL_PKG.INSERT_ROW (
781 X_ROWID => l_rowid ,
782 X_FAC_WL_ID => l_fac_wl_id ,
783 X_PERSON_ID => l_tab_person_id(i).person_id ,
784 X_CALENDAR_CAT => l_dest_cal_cat ,
785 X_CAL_TYPE => l_dest_cal_type ,
786 X_CI_SEQUENCE_NUMBER => l_dest_sequence_number ,
787 X_ATTRIBUTE_CATEGORY => NULL ,
788 X_ATTRIBUTE1 => NULL ,
789 X_ATTRIBUTE2 => NULL ,
790 X_ATTRIBUTE3 => NULL ,
791 X_ATTRIBUTE4 => NULL ,
792 X_ATTRIBUTE5 => NULL ,
793 X_ATTRIBUTE6 => NULL ,
794 X_ATTRIBUTE7 => NULL ,
795 X_ATTRIBUTE8 => NULL ,
796 X_ATTRIBUTE9 => NULL ,
797 X_ATTRIBUTE10 => NULL ,
798 X_ATTRIBUTE11 => NULL ,
799 X_ATTRIBUTE12 => NULL ,
800 X_ATTRIBUTE13 => NULL ,
801 X_ATTRIBUTE14 => NULL ,
802 X_ATTRIBUTE15 => NULL ,
803 X_ATTRIBUTE16 => NULL ,
804 X_ATTRIBUTE17 => NULL ,
805 X_ATTRIBUTE18 => NULL ,
806 X_ATTRIBUTE19 => NULL ,
807 X_ATTRIBUTE20 => NULL ,
808 X_MODE => 'R' ) ;
809 END ;
810 END IF ; -- if faculty record in dest cal already exists
811 CLOSE cur_fac_wl_exists ;
812
813 -- loop through all the tasks assigned to that faculty in source calendar: fac_wl_id
814 --
815 FOR cur_source_tasks_rec IN cur_source_tasks(l_tab_person_id(i).fac_wl_id) LOOP
816 OPEN cur_fat_exists(l_fac_wl_id,cur_source_tasks_rec.faculty_task_type) ;
817 FETCH cur_fat_exists INTO cur_fat_exists_rec ;
818 --if the task is not already assigned to the faculty in the dest calendar
819 -- then insert it now
820 --
821 IF cur_fat_exists%NOTFOUND THEN
822 DECLARE
823 l_rowid1 VARCHAR2(40);
824 BEGIN
825 IGS_PS_FAC_ASG_TASK_PKG.INSERT_ROW(
826 X_ROWID => l_rowid1 ,
827 X_FAC_WL_ID => l_fac_wl_id ,
828 X_FACULTY_TASK_TYPE => cur_source_tasks_rec.faculty_task_type ,
829 X_CONFIRMED_IND => cur_source_tasks_rec.confirmed_ind,
830 X_NUM_ROLLOVER_PERIOD => (NVL(cur_source_tasks_rec.num_rollover_period,99) - 1) ,
831 X_ROLLOVER_FLAG => 'D' ,
832 X_DEPT_BUDGET_CD => cur_source_tasks_rec.dept_budget_cd ,
833 X_DEFAULT_WL => cur_source_tasks_rec.default_wl ,
834 X_MODE => 'R' );
835 -- log the task rolled over
836 --
837 FND_MESSAGE.SET_NAME('IGS','IGS_PS_FAC_RLOVR_TASK');
838 FND_MESSAGE.SET_TOKEN('TASK',cur_source_tasks_rec.faculty_task_type );
839 FND_FILE.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET ) ;
840 END ;
841 END IF; -- task does not exists already
842 CLOSE cur_fat_exists ;
843
844 -- update the source task type ,set rollover flag to 'S'
845 --
846 OPEN cur_upd_src_task( cur_source_tasks_rec.row_id);
847 FETCH cur_upd_src_task INTO cur_upd_src_task_rec ;
848 IF cur_upd_src_task%FOUND THEN
849 IGS_PS_FAC_ASG_TASK_PKG.UPDATE_ROW(
850 X_ROWID => cur_upd_src_task_rec.rowid,
851 X_FAC_WL_ID => cur_upd_src_task_rec.fac_wl_id ,
852 X_FACULTY_TASK_TYPE => cur_upd_src_task_rec.faculty_task_type ,
853 X_CONFIRMED_IND => cur_upd_src_task_rec.confirmed_ind,
854 X_NUM_ROLLOVER_PERIOD => cur_upd_src_task_rec.num_rollover_period,
855 X_ROLLOVER_FLAG => 'S' ,
856 X_DEPT_BUDGET_CD => cur_upd_src_task_rec.dept_budget_cd ,
857 X_DEFAULT_WL => cur_upd_src_task_rec.default_wl ,
858 X_MODE => 'R' );
859
860 END IF; -- source task type is updated
861 CLOSE cur_upd_src_task ;
862 END LOOP ; -- copying the source task types to dest
863
864 END LOOP ; -- loop all the faculty workloads to be rolled over
865
866 -- log the successful completion of the job and return
867 --
868 FND_MESSAGE.SET_NAME('IGS','IGS_PS_FAC_RLOVR_SUC');
869 FND_FILE.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET) ;
870 RETURN ;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 ROLLBACK TO a;
875 retcode:= 2;
876 ERRBUF := Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
877 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
878 END rollover_fac_task;
879
880
881 END igs_ps_rlovr_fac_tsk;
[Home] [Help]