[Home] [Help]
PACKAGE BODY: APPS.MSC_NET_RES_INST_AVAILABILITY
Source
1 PACKAGE BODY MSC_NET_RES_INST_AVAILABILITY AS
2 /* $Header: MSCNRIAB.pls 120.2 2006/01/12 03:28:15 abhikuma noship $ */
3
4
5 var_gt_user_id number;
6 var_gt_debug boolean;
7 var_gt_date DATE;
8 var_gt_request NUMBER;
9 var_gt_login NUMBER;
10 var_gt_application NUMBER;
11 var_gt_conc_program NUMBER;
12
13 FUNCTION check_24( var_time in number) return number is
14 BEGIN
15 /*
16 if var_gt_debug then
17 dbms_output.put_line('In check_24 '|| to_char(var_time));
18 end if;
19 */
20
21 --dbms_output.put_line('In check_24 '|| to_char(var_time));
22 log_message('In check_24 '|| to_char(var_time));
23
24 if var_time > 24*3600 then
25 return var_time - 24*3600;
26 else
27 return var_time;
28 end if;
29 END check_24;
30
31
32 FUNCTION check_start_date(
33 arg_organization_id in number,
34 arg_sr_instance_id in number) return DATE IS
35 v_start_date date;
36
37 BEGIN
38
39 --v_stmt := 10;
40 select TRUNC(MIN(calendar_date))
41 into v_start_date
42 from msc_calendar_dates cal
43 ,msc_trading_partners tp
44 where tp.sr_tp_id = arg_organization_id
45 and tp.sr_instance_id = arg_sr_instance_id
46 and tp.partner_type = 3
47 and cal.calendar_code = tp.calendar_code
48 and cal.sr_instance_id = tp.sr_instance_id
49 and cal.exception_set_id = tp.calendar_exception_set_id
50 and cal.seq_num is not null;
51
52 return v_start_date;
53
54 EXCEPTION
55 WHEN no_data_found THEN
56 FND_MESSAGE.SET_NAME('MSC', 'MSC_NRA_NO_CALENDAR');
57 FND_MESSAGE.SET_TOKEN('ORG_ID', arg_organization_id);
58 MSC_UTIL.MSC_DEBUG(FND_MESSAGE.Get);
59
60 END check_start_date;
61
62 FUNCTION check_cutoff_date(arg_organization_id in number,
63 arg_sr_instance_id in number) return DATE IS
64 v_cutoff_date date;
65
66 BEGIN
67
68 --v_stmt := 20;
69 select TRUNC(MAX(calendar_date))
70 into v_cutoff_date
71 from msc_calendar_dates cal
72 ,msc_trading_partners tp
73 where tp.sr_tp_id = arg_organization_id
74 and tp.sr_instance_id = arg_sr_instance_id
75 and tp.partner_type = 3
76 and cal.calendar_code = tp.calendar_code
77 and cal.sr_instance_id = tp.sr_instance_id
78 and cal.exception_set_id = tp.calendar_exception_set_id
79 and cal.seq_num is not null;
80
81 return v_cutoff_date;
82
83 EXCEPTION
84 WHEN no_data_found THEN
85 FND_MESSAGE.SET_NAME('MSC', 'MSC_NRA_NO_CALENDAR');
86 FND_MESSAGE.SET_TOKEN('ORG_ID', arg_organization_id);
87 MSC_UTIL.MSC_DEBUG(FND_MESSAGE.Get);
88
89 END check_cutoff_date;
90
91 PROCEDURE update_avail( var_rowid in ROWID,
92 var_date in DATE,
93 var_from_time in number,
94 var_to_time in number) is
95 var_time1 number;
96 var_time2 number;
97 var_date1 DATE;
98
99 BEGIN
100
101 --dbms_output.put_line(' rowid: '||var_rowid);
102 log_message(' rowid: '||var_rowid);
103 var_time1 := check_24(var_from_time);
104 var_time2 := check_24(var_to_time);
105 /*
106 if the start time is in the next, this avail should be on the
107 following day
108 */
109
110 var_date1 := var_date;
111 if var_time1 < var_from_time then
112 var_date1 := var_date1 + 1;
113 end if;
114
115 UPDATE msc_net_res_inst_avail
116 SET shift_date = var_date1,
117 from_time = var_time1,
118 to_time = var_time2
119 WHERE rowid = var_rowid;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 MSC_UTIL.MSC_DEBUG('Error in update_avail:: ' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60));
124 END update_avail;
125
126 PROCEDURE delete_avail( var_rowid in ROWID) is
127 BEGIN
128 /*
129 if var_gt_debug then
130 dbms_output.put_line('about to delete');
131 end if;
132 */
133 --dbms_output.put_line('about to delete');
134 log_message('about to delete');
135
136 DELETE from msc_net_res_inst_avail
137 WHERE rowid = var_rowid;
138 --dbms_output.put_line('delete row count ' || sql%rowcount);
139 log_message('delete row count ' || sql%rowcount);
140 END delete_avail;
141
142 PROCEDURE insert_avail( var_date in DATE,
143 var_department_id in number,
144 var_resource_id in number,
145 var_instance_id in number,
146 var_serial_num in varchar2,
147 var_equipment_item_id IN Number,
148 var_organization_id in number,
149 var_sr_instance_id in number,
150 var_shift_num in number,
151 var_simulation_set in varchar2,
152 var_from_time in number,
153 var_to_time in number,
154 var_refresh_number in number) is
155 var_time1 number;
156 var_time2 number;
157 var_date1 DATE;
158
159
160 BEGIN
161 var_time1 := check_24(var_from_time);
162 var_time2 := check_24(var_to_time);
163
164 /*
165 if the start time is in the next, this avail should be on the
166 following day
167 */
168
169 var_date1 := var_date;
170 if var_time1 < var_from_time then
171 var_date1 := var_date1 + 1;
172 end if;
173 /*
174 if var_gt_debug then
175 dbms_output.put_line('Ready to insert' ||
176 ' Dept ' || to_char(var_department_id) ||
177 ' Res ' || to_char(var_resource_id) ||
178 ' shift ' || to_char(var_shift_num) ||
179 ' date '|| to_char(var_date) ||
180 ' from time '|| to_char(var_from_time/3600)||
181 ' to time '|| to_char(var_to_time/3600) ||
182 ' units '|| to_char(var_cap_units));
183 end if;
184 */
185
186 INSERT into msc_net_res_inst_avail(
187 inst_transaction_id,
188 plan_id,
189 sr_instance_id,
190 organization_id,
191 department_id,
192 resource_id,
193 res_instance_id,
194 equipment_item_id,
195 parent_id,
196 serial_number,
197 simulation_set,
198 shift_num,
199 shift_date,
200 from_time,
201 to_time,
202 status,
203 applied,
204 updated,
205 last_update_date,
206 last_updated_by,
207 creation_date,
208 created_by,
209 last_update_login,
210 request_id,
211 program_application_id,
212 program_id,
213 program_update_date,
214 refresh_number)
215 VALUES( msc_net_res_inst_avail_s.NEXTVAL,
216 -1,
217 var_sr_instance_id,
218 var_organization_id,
219 var_department_id,
220 var_resource_id,
221 var_instance_id,
222 var_equipment_item_id,
223 null, --PARENT_ID
224 var_serial_num,
225 var_simulation_set,
226 var_shift_num,
227 var_date1,
228 var_time1,
229 var_time2,
230 null, --STATUS
231 null, --APPLIED
232 2, --UPDATED
233 sysdate,
234 var_gt_user_id,
235 sysdate,
236 var_gt_user_id,
237 var_gt_login,
238 var_gt_request,
239 var_gt_application,
240 var_gt_conc_program,
241 sysdate,
242 var_refresh_number
243 );
244 -- dbms_output.put_line('last insert row count ' || sql%rowcount);
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 MSC_UTIL.MSC_DEBUG('Error in insert_avail:: ' || to_char(sqlcode) || ':' || substr(sqlerrm,1,60));
249
250 END insert_avail;
251
252
253 PROCEDURE calc_res_ins_avail( arg_organization_id IN number,
254 arg_sr_instance_id IN number,
255 arg_department_id IN number,
256 arg_resource_id IN number,
257 arg_simulation_set IN varchar2,
258 arg_instance_id IN number,
259 arg_serial_num IN varchar2,
260 arg_equipment_item_id IN Number,
261 arg_24hr_flag IN number,
262 arg_start_date IN date,
263 arg_cutoff_date IN date,
264 arg_refresh_number IN Number) is
265
266 cursor changes is
267 SELECT distinct
268 changes.action_type,
269 changes.from_time,
270 DECODE(LEAST(changes.to_time, changes.from_time),
271 changes.to_time, changes.to_time + 24*3600,
272 changes.to_time),
273 dates.shift_date,
274 changes.shift_num,
275 reschanges.capacity_change
276 from msc_shift_dates dates,
277 msc_res_instance_changes changes,
278 msc_resource_changes reschanges,
279 msc_trading_partners param
280 WHERE dates.calendar_code = param.calendar_code
281 AND dates.exception_set_id = param.calendar_exception_set_id
282 AND dates.sr_instance_id = param.sr_instance_id
283 AND dates.sr_instance_id = arg_sr_instance_id
284 AND dates.seq_num is not null
285 AND dates.shift_date between changes.from_date AND
286 NVL(changes.to_date, changes.from_date)
287 AND dates.shift_num = changes.shift_num
288 AND param.sr_tp_id = arg_organization_id
289 AND changes.to_date >= trunc(arg_start_date)
290 AND changes.from_date <= arg_cutoff_date
291 AND changes.simulation_set = arg_simulation_set
292 AND changes.action_type = CHANGE_WORKDAY
293 AND reschanges.action_type = CHANGE_WORKDAY
294 AND changes.resource_id = arg_resource_id
295 AND changes.res_instance_id = arg_instance_id
296 AND nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
297 AND changes.department_id = arg_department_id
298 AND reschanges.department_id = changes.department_id
299 AND reschanges.resource_id = changes.resource_id
300 AND reschanges.sr_instance_id = changes.sr_instance_id
301 AND reschanges.shift_num = changes.shift_num
302 AND reschanges.from_date = changes.from_date
303 AND reschanges.to_date = changes.to_date
304 AND reschanges.simulation_set = changes.simulation_set
305 AND reschanges.action_type = changes.action_type
306 AND reschanges.from_time = changes.from_time
307 AND reschanges.to_time = changes.to_time
308 -- Removed for bug #2318675 (24hr changes were ignored)
309 --AND NOT (changes.from_time = changes.to_time AND
310 -- changes.from_date = changes.to_date)
311 ORDER BY dates.shift_date, changes.from_time;
312
313
314 var_action_type number;
315 var_from_time number;
316 var_to_time number;
317 var_shift_date date;
318 var_from_shift_time number;
319 var_to_shift_time number;
320 var_orig_cap number;
321 var_shift_num number;
322 var_cap_change number;
323 var_orig_from_time number;
324 var_orig_to_time number;
325 var_next_from_time number;
326 var_rowid rowid;
327 var_rowcount number;
328 var_equipment_item_id number;
329
330 l_count number := 0;
331
332 cursor avail is
333 SELECT equipment_item_id,
334 from_time from_time,
335 DECODE(LEAST(to_time, from_time),
336 to_time, to_time + 24*3600,
337 to_time) to_time,
338 rowid
339 FROM msc_net_res_inst_avail
340 WHERE plan_id = -1
341 AND department_id = arg_department_id
342 AND resource_id = arg_resource_id
343 AND res_instance_id = arg_instance_id
344 AND nvl(serial_number,-1) = nvl(arg_serial_num, -1)
345 AND simulation_set = arg_simulation_set
346 AND sr_instance_id = arg_sr_instance_id
347 AND organization_id = arg_organization_id
348 AND shift_num = var_shift_num
349 AND shift_date = var_shift_date
350 ORDER BY 2, 3;
351
352 BEGIN
353 /**
354 dbms_output.put_line('sr instance ' || arg_sr_instance_id);
355 dbms_output.put_line('instance ' || arg_instance_id);
356 dbms_output.put_line('resource ' || arg_resource_id);
357 dbms_output.put_line('dept' || arg_department_id);
358 dbms_output.put_line('serial ' || arg_serial_num);
359 dbms_output.put_line('org ' || arg_organization_id);
360 ***/
361
362 log_message('first insert for not 24HR');
363 log_message('sr instance ' || arg_sr_instance_id);
364 log_message('instance ' || arg_instance_id);
365 log_message('resource ' || arg_resource_id);
366 log_message('dept' || arg_department_id);
367 log_message('serial ' || arg_serial_num);
368 log_message('org ' || arg_organization_id);
369
370
371 -- IF NOT 24HR
372 if arg_24hr_flag = 2 THEN
373
374 INSERT into msc_net_res_inst_avail(
375 inst_transaction_id,
376 plan_id,
377 sr_instance_id,
378 organization_id,
379 department_id,
380 resource_id,
381 res_instance_id,
382 equipment_item_id,
383 parent_id,
384 serial_number,
385 simulation_set,
386 shift_num,
387 shift_date,
388 from_time,
389 to_time,
390 status,
391 applied,
392 updated,
393 last_update_date,
394 last_updated_by,
395 creation_date,
396 created_by,
397 last_update_login,
398 request_id,
399 program_application_id,
400 program_id,
401 program_update_date,
402 refresh_number)
403 select msc_net_res_inst_avail_s.NEXTVAL,
404 -1,
405 arg_sr_instance_id,
406 arg_organization_id,
407 arg_department_id,
408 arg_resource_id,
409 arg_instance_id,
410 dept_ins.equipment_item_id,
411 null, --PARENT_ID
412 arg_serial_num,
413 arg_simulation_set,
414 res_shifts.shift_num,
415 dates.shift_date,
416 shifts.from_time,
417 shifts.to_time,
418 null, --STATUS
419 null, --APPLIED
420 2, --UPDATED
421 sysdate,
422 var_gt_user_id,
423 sysdate,
424 var_gt_user_id,
425 var_gt_login,
426 var_gt_request,
427 var_gt_application,
428 var_gt_conc_program,
429 sysdate,
430 arg_refresh_number
431 FROM msc_shift_dates dates,
432 msc_shift_times shifts,
433 msc_resource_shifts res_shifts,
434 msc_department_resources dept_res1,
435 msc_dept_res_instances dept_ins,
436 msc_trading_partners param
437 WHERE dates.calendar_code = param.calendar_code
438 AND dates.exception_set_id = param.calendar_exception_set_id
439 AND dates.sr_instance_id = param.sr_instance_id
440 AND dates.sr_instance_id = arg_sr_instance_id
441 AND param.sr_tp_id = arg_organization_id
442 AND dates.shift_num = shifts.shift_num
443 AND dates.seq_num is not null
444 AND dates.shift_date >= trunc(arg_start_date)
445 AND dates.shift_date <= arg_cutoff_date
446 AND shifts.shift_num = res_shifts.shift_num
447 AND shifts.calendar_code = param.calendar_code
448 AND shifts.sr_instance_id = arg_sr_instance_id
449 AND res_shifts.department_id = dept_res1.department_id
450 AND res_shifts.resource_id = dept_res1.resource_id
451 AND res_shifts.sr_instance_id = arg_sr_instance_id
452 AND dept_res1.plan_id = -1
453 AND NVL(dept_res1.available_24_hours_flag, 2) = 2
454 -- AND dept_res1.owning_department_id is null
455 AND dept_res1.resource_id = arg_resource_id
456 AND dept_res1.department_id = arg_department_id
457 AND dept_res1.organization_id = arg_organization_id
458 AND dept_res1.sr_instance_id = arg_sr_instance_id
459 AND dept_ins.department_id = arg_department_id
460 AND dept_ins.organization_id = arg_organization_id
461 AND dept_ins.sr_instance_id = arg_sr_instance_id
462 AND dept_ins.resource_id = arg_resource_id
463 AND dept_ins.res_instance_id = arg_instance_id
464 AND dept_ins.plan_id = -1
465 AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num, -1)
466 AND NOT EXISTS
467 (SELECT NULL
468 FROM msc_resource_changes changes
469 WHERE changes.sr_instance_id = dept_res1.sr_instance_id
470 AND changes.department_id = dept_res1.department_id
471 AND changes.resource_id = dept_res1.resource_id
472 AND changes.simulation_set = arg_simulation_set
473 AND changes.shift_num = dates.shift_num
474 AND changes.from_date = dates.shift_date
475 AND changes.action_type = DELETE_WORKDAY);
476
477
478 -- debug
479 /*
480 select count(*) into var_rowcount
481 FROM msc_net_res_inst_avail
482 where resource_id = arg_resource_id
483 and instance_id = arg_instance_id
484 and department_id = arg_department_id;
485
486 dbms_output.put_line(' Inserted '|| to_char(var_rowcount)||' avails'); */
487
488 else
489
490 --dbms_output.put_line(' Inserted -- it is a 24 hrs');
491 log_message(' Inserted -- it is a 24 hrs');
492 insert into msc_net_res_inst_avail(
493 inst_transaction_id,
494 plan_id,
495 sr_instance_id,
496 organization_id,
497 department_id,
498 resource_id,
499 res_instance_id,
500 equipment_item_id,
501 parent_id,
502 serial_number,
503 simulation_set,
504 shift_num,
505 shift_date,
506 from_time,
507 to_time,
508 status,
509 applied,
510 updated,
511 last_update_date,
512 last_updated_by,
513 creation_date,
514 created_by,
515 last_update_login,
516 request_id,
517 program_application_id,
518 program_id,
519 program_update_date,
520 refresh_number)
521 select msc_net_res_inst_avail_s.NEXTVAL,
522 -1,
523 arg_sr_instance_id,
524 arg_organization_id,
525 arg_department_id,
526 arg_resource_id,
527 arg_instance_id,
528 dept_ins.equipment_item_id,
529 null, --PARENT_ID
530 arg_serial_num,
531 arg_simulation_set,
532 0,
533 dates.calendar_date,
534 1,
535 24*60*60 - 1,
536 null, --STATUS
537 null, --APPLIED
538 2, --UPDATED
539 sysdate,
540 var_gt_user_id,
541 sysdate,
542 var_gt_user_id,
543 var_gt_login,
544 var_gt_request,
545 var_gt_application,
546 var_gt_conc_program,
547 sysdate,
548 arg_refresh_number
549 FROM msc_calendar_dates dates,
550 msc_department_resources dept_res1,
551 msc_dept_res_instances dept_ins,
552 msc_trading_partners param
553 WHERE dates.calendar_code = param.calendar_code
554 AND dates.exception_set_id = param.calendar_exception_set_id
555 AND dates.sr_instance_id = param.sr_instance_id
556 AND dates.sr_instance_id = arg_sr_instance_id
557 AND dates.calendar_date <= arg_cutoff_date
558 AND dates.seq_num is not null
559 AND dates.calendar_date >= trunc(arg_start_date)
560 AND dept_res1.plan_id = -1
561 AND NVL(dept_res1.available_24_hours_flag, 2) = 1
562 -- AND dept_res1.owning_department_id is null
563 AND dept_res1.resource_id = arg_resource_id
564 AND dept_res1.department_id = arg_department_id
565 AND dept_res1.organization_id = arg_organization_id
566 AND dept_res1.sr_instance_id = arg_sr_instance_id
567 AND dept_ins.department_id = arg_department_id
568 AND dept_ins.resource_id = arg_resource_id
569 AND dept_ins.res_instance_id = arg_instance_id
570 AND dept_ins.organization_id = arg_organization_id
571 AND dept_ins.sr_instance_id = arg_sr_instance_id
572 AND dept_ins.plan_id = -1
573 AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num,-1)
574 AND param.sr_tp_id = arg_organization_id;
575
576 --dbms_output.put_line('2nd insert row count ' || sql%rowcount);
577 end if;
578
579 -- return;
580
581
582 if arg_24hr_flag = 2 then
583 OPEN changes;
584 loop
585 FETCH changes INTO
586 var_action_type,
587 var_orig_from_time,
588 var_orig_to_time,
589 var_shift_date,
590 var_shift_num,
591 var_cap_change;
592 EXIT WHEN changes%NOTFOUND;
593 -- since the capacity for instance can be only 1,
594 -- the changes can be only 1 or -1
595 if var_cap_change > 0 then
596 var_cap_change := 1;
597 else
598 var_cap_change := -1;
599 end if;
600
601 /* dbms_output.put_line('cap_change: '||var_orig_from_time||'-'||
602 var_orig_to_time||' '||var_shift_date||' '||
603 var_cap_change);
604 */
605 log_message('cap_change: '||var_orig_from_time||'-'||
606 var_orig_to_time||' '||var_shift_date||' '||
607 var_cap_change);
608
609
610 /*----------------------------------------------------------+
611 | For each modification we get the current resource |
612 | calendar and process sections of the modification that |
613 | overlaps with the shift segment |
614 +----------------------------------------------------------*/
615 -- Initialize the variables
616 var_from_time := var_orig_from_time;
617 -- var_next_from_time := var_orig_from_time;
618 var_to_time := var_orig_to_time;
619 var_rowcount := 0;
620 OPEN avail;
621 LOOP
622
623 FETCH avail INTO
624 var_equipment_item_id,
625 var_from_shift_time,
626 var_to_shift_time,
627 var_rowid;
628 EXIT WHEN avail%NOTFOUND;
629 -- Set the from time for the modification to the start of
630 -- the unprocessed section
631 var_from_time := var_orig_from_time;
632 -- Set the to time to the original to time of the modification
633 var_to_time := var_orig_to_time;
634
635 -- If you have completely processed the modification you are
636 -- done so exit
637 if (var_from_time > var_to_time) then
638 EXIT;
639 end if;
640 -- if the shift spans over midnight
641 -- and the shift exception starts on the next day
642 -- then, the shift exception times need to be moved to the next day
643 if var_to_shift_time > 24*60*60 then
644 if var_from_time < var_to_shift_time - 24*60*60 then
645 var_from_time := var_from_time + 24*60*60;
646 var_to_time := var_to_time + 24*60*60;
647 end if;
648 end if;
649
650 -- If the from time or to time is outside of the shift, then
651 -- we will just add the entire capacity.. skip partial
652 if var_from_time >= var_to_shift_time
653 OR var_to_time <= var_from_shift_time then
654 goto skip;
655 end if;
656
657 -- If the shift starts, before the modification
658 if var_from_shift_time < var_from_time then
659 -- Then, if the end of the shift is before or equal to the end
660 -- of the modification, update avail from the start of
661 -- the shift to the start of the modification.
662 if var_to_shift_time <= var_to_time then
663 --dbms_output.put_line('update only');
664 update_avail(var_rowid,
665 var_shift_date,
666 var_from_shift_time,
667 var_from_time -1);
668 else
669 --dbms_output.put_line('update and insert');
670 update_avail(var_rowid,
671 var_shift_date,
672 var_from_shift_time,
673 var_from_time -1);
674 insert_avail(var_shift_date,
675 arg_department_id,
676 arg_resource_id,
677 arg_instance_id,
678 arg_serial_num,
679 var_equipment_item_id,
680 arg_organization_id,
681 arg_sr_instance_id,
682 var_shift_num,
683 arg_simulation_set,
684 var_to_time+1,
685 var_to_shift_time,
686 arg_refresh_number);
687 end if;
688
689 elsif var_from_shift_time >= var_from_time then
690 if var_to_shift_time > var_to_time then
691 --dbms_output.put_line('update row');
692 update_avail(var_rowid,
693 var_shift_date,
694 var_to_time+1,
695 var_to_shift_time);
696 else
697 --dbms_output.put_line('delete row ' || var_rowid);
698 delete_avail(var_rowid);
699 end if;
700 end if;
701
702 <<skip>>
703 NULL;
704 end loop;
705 close avail;
706
707 -- Insert modification
708
709 if var_cap_change = 1 then
710 --dbms_output.put_line('insert the modification');
711 insert_avail(var_shift_date,
712 arg_department_id,
713 arg_resource_id,
714 arg_instance_id,
715 arg_serial_num,
716 var_equipment_item_id,
717 arg_organization_id,
718 arg_sr_instance_id,
719 var_shift_num,
720 arg_simulation_set,
721 var_from_time,
722 var_to_time,
723 arg_refresh_number);
724 end if;
725
726 end loop;
727 close changes;
728
729 -- Finally add the availability from the add workday type modifications
730 --dbms_output.put_line('going to insert added workdays');
731 log_message('going to insert added workdays');
732 INSERT into msc_net_res_inst_avail(
733 inst_transaction_id,
734 plan_id,
735 sr_instance_id,
736 organization_id,
737 department_id,
738 resource_id,
739 res_instance_id,
740 equipment_item_id,
741 parent_id,
742 serial_number,
743 simulation_set,
744 shift_num,
745 shift_date,
746 from_time,
747 to_time,
748 status,
749 applied,
750 updated,
751 last_update_date,
752 last_updated_by,
753 creation_date,
754 created_by,
755 last_update_login,
756 request_id,
757 program_application_id,
758 program_id,
759 program_update_date,
760 refresh_number)
761 select msc_net_res_inst_avail_s.NEXTVAL,
762 -1,
763 arg_sr_instance_id,
764 arg_organization_id,
765 arg_department_id,
766 arg_resource_id,
767 arg_instance_id,
768 var_equipment_item_id,
769 null, --PARENT_ID
770 arg_serial_num,
771 arg_simulation_set,
772 changes.shift_num,
773 changes.from_date,
774 changes.from_time,
775 changes.to_time,
776 null, --STATUS
777 null, --APPLIED
778 2, --UPDATED
779 sysdate,
780 var_gt_user_id,
781 sysdate,
782 var_gt_user_id,
783 var_gt_login,
784 var_gt_request,
785 var_gt_application,
786 var_gt_conc_program,
787 sysdate,
788 arg_refresh_number
789 FROM msc_res_instance_changes changes
790 WHERE changes.sr_instance_id = arg_sr_instance_id
791 AND changes.department_id = arg_department_id
792 AND changes.resource_id = arg_resource_id
793 and changes.res_instance_id = arg_instance_id
794 and nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
795 AND changes.action_type = ADD_WORKDAY
796 AND changes.simulation_set= arg_simulation_set;
797
798
799 end if;
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 MSC_UTIL.MSC_DEBUG('Error in calc_ins_avail:: ' || to_char(sqlcode) || ':'
804 || substr(sqlerrm,1,60));
805 --dbms_output.put_line('Error in calc_ins_avail ' || sqlerrm);
806
807 END calc_res_ins_avail;
808
809 -- This procedule is used to populate resource for each simulation set
810 -- within an organization instance
811 --
812
813 PROCEDURE populate_avail_res_instances(
814 arg_refresh_number IN number,
815 arg_refresh_flag IN number,
816 arg_simulation_set IN varchar2,
817 arg_organization_id IN number,
818 arg_sr_instance_id IN number,
819 arg_start_date IN date,
820 arg_cutoff_date IN date) IS
821
822 CURSOR dept_res is
823 SELECT dept_res.department_id,
824 dept_res.resource_id,
825 dept_ins.res_instance_id,
826 dept_ins.serial_number,
827 dept_ins.equipment_item_id,
828 NVL(dept_res.available_24_hours_flag, 2),
829 dept_res.aggregate_resource_id,
830 NVL(dept_res.capacity_units,1), --**
831 dept_res.disable_date --, --**
832 --org.calendar_code, --**
833 -- org.calendar_exception_set_id --**
834 FROM msc_trading_partners org,
835 msc_department_resources dept_res,
836 msc_dept_res_instances dept_ins
837 WHERE dept_res.owning_department_id = dept_res.department_id
838 AND dept_res.plan_id = -1
839 AND dept_res.resource_id <> -1
840 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
841 AND NVL(dept_res.disable_date,sysdate+1) > sysdate
842 AND dept_res.organization_id = org.sr_tp_id
843 AND dept_res.sr_instance_id = org.sr_instance_id
844 AND org.sr_tp_id= arg_organization_id
845 AND org.sr_instance_id= arg_sr_instance_id
846 AND org.partner_type=3
847 AND dept_res.plan_id = dept_ins.plan_id
848 AND dept_res.organization_id = dept_ins.organization_id
849 AND dept_res.sr_instance_id = dept_ins.sr_instance_id
850 AND dept_res.department_id = dept_ins.department_id
851 AND dept_res.resource_id = dept_ins.resource_id
852 /*
853 adding the following condition that the collection for the resource
854 instances for only there is any resource instance change */
855 AND exists (select * from msc_res_instance_changes chg
856 where dept_ins.department_id = chg.department_id
857 and dept_ins.resource_id = chg.resource_id
858 and dept_ins.sr_instance_id = chg.sr_instance_id
859 and dept_ins.res_instance_id = chg.res_instance_id
860 and dept_ins.serial_number = chg.serial_number);
861
862
863 CURSOR dept_res_change is
864 SELECT distinct dept_res.department_id,
865 dept_res.resource_id,
866 res_ins.res_instance_id,
867 res_ins.serial_number,
868 NVL(dept_res.available_24_hours_flag, 2),
869 dept_res.aggregate_resource_id,
870 NVL(dept_res.capacity_units,1), --**
871 dept_res.disable_date --, --**
872 --org.calendar_code, --**
873 -- org.calendar_exception_set_id --**
874 FROM msc_trading_partners org,
875 msc_resource_changes chg,
876 msc_department_resources dept_res,
877 msc_res_instance_changes res_ins
878 WHERE chg.department_id = dept_res.department_id
879 AND chg.resource_id = dept_res.resource_id
880 AND chg.sr_instance_id = dept_res.sr_instance_id
881 AND chg.refresh_number = arg_refresh_number
882 AND dept_res.owning_department_id = dept_res.department_id
883 AND dept_res.plan_id = -1
884 AND dept_res.resource_id <> -1
885 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
886 AND NVL(dept_res.disable_date,sysdate+1) > sysdate
887 AND dept_res.organization_id = org.sr_tp_id
888 AND dept_res.sr_instance_id = org.sr_instance_id
889 AND org.sr_tp_id= arg_organization_id
890 AND org.sr_instance_id= arg_sr_instance_id
891 AND org.partner_type=3
892 AND chg.sr_instance_id = res_ins.sr_instance_id
893 AND chg.department_id = res_ins.department_id
894 AND chg.resource_id = res_ins.resource_id;
895
896 var_department_id NUMBER;
897 var_resource_id NUMBER;
898 var_24hr_flag NUMBER;
899 v_cutoff_date DATE;
900 v_start_date DATE;
901 var_aggregate_resource_id NUMBER;
902
903 var_capacity_units NUMBER; -- new variables for calling calc_res_avail
904 var_disable_date DATE;
905
906 var_res_instance_id NUMBER;
907 var_serial_number msc_dept_res_instances.serial_number%type;
908 var_equipment_item_id msc_dept_res_instances.equipment_item_id%type;
909
910 BEGIN
911
912 var_gt_date := SYSDATE;
913 var_gt_user_id := FND_GLOBAL.USER_ID;
914 var_gt_login := FND_GLOBAL.LOGIN_ID;
915 var_gt_request := FND_GLOBAL.CONC_REQUEST_ID;
916 var_gt_application := FND_GLOBAL.PROG_APPL_ID;
917 var_gt_conc_program := FND_GLOBAL.CONC_PROGRAM_ID;
918
919 LOG_MESSAGE('--------------------------------------------------------');
920 LOG_MESSAGE(' Populating Available Resources Instances...............');
921 LOG_MESSAGE('--------------------------------------------------------');
922
923
924
925
926 if arg_start_date is null then
927 v_start_date := check_start_date(arg_organization_id, arg_sr_instance_id);
928 else
929 v_start_date := arg_start_date;
930 end if;
931
932 if arg_cutoff_date is null then
933 v_cutoff_date := check_cutoff_date(arg_organization_id, arg_sr_instance_id);
934 else
935 v_cutoff_date := arg_cutoff_date;
936 end if;
937
938 if arg_refresh_flag = 1 then
939 -- process complete refresh
940
941 OPEN dept_res;
942 LOOP
943 Fetch dept_res into var_department_id,
944 var_resource_id,
945 var_res_instance_id,
946 var_serial_number,
947 var_equipment_item_id,
948 var_24hr_flag,
949 var_aggregate_resource_id,
950 var_capacity_units,
951 var_disable_date;
952 --v_calendar_code,
953 --v_calendar_exception_set_id;
954
955 EXIT WHEN dept_res%NOTFOUND;
956 --dbms_output.put_line('Process all changed dept resource for complete refresh');
957 log_message('Process all changed dept resource for complete refresh');
958 calc_res_ins_avail(
959 arg_organization_id,
960 arg_sr_instance_id,
961 var_department_id,
962 var_resource_id,
963 arg_simulation_set,
964 var_res_instance_id,
965 var_serial_number,
966 var_equipment_item_id,
967 var_24hr_flag,
968 v_start_date,
969 v_cutoff_date,
970 arg_refresh_number);
971 commit;
972 SAVEPOINT SP1;
973 END LOOP;
974
975 CLOSE dept_res;
976
977 else
978 -- process all changed department resources
979
980 OPEN dept_res_change;
981 LOOP
982 Fetch dept_res_change into var_department_id,
983 var_resource_id,
984 var_res_instance_id,
985 var_serial_number,
986 var_24hr_flag,
987 var_aggregate_resource_id,
988 var_capacity_units,
989 var_disable_date;
990 --v_calendar_code,
991 --v_calendar_exception_set_id;
992
993 EXIT WHEN dept_res_change%NOTFOUND;
994 --dbms_output.put_line('Process all changed dept resource for net change');
995 log_message('Process all changed dept resource for net change');
996 calc_res_ins_avail(
997 arg_organization_id,
998 arg_sr_instance_id,
999 var_department_id,
1000 var_resource_id,
1001 arg_simulation_set,
1002 var_res_instance_id,
1003 var_serial_number,
1004 var_equipment_item_id,
1005 var_24hr_flag,
1006 v_start_date,
1007 v_cutoff_date,
1008 arg_refresh_number);
1009 commit;
1010 SAVEPOINT SP1;
1011 END LOOP;
1012
1013 CLOSE dept_res_change;
1014
1015 end if;
1016
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 MSC_UTIL.MSC_DEBUG('Error in populate_avail_res_instances:: ' || to_char(sqlcode) ||
1021 ':' || substr(sqlerrm,1,60));
1022
1023
1024 IF dept_res%isopen THEN CLOSE dept_res; END IF;
1025 IF dept_res_change%isopen THEN CLOSE dept_res_change; END IF;
1026
1027
1028 END populate_avail_res_instances;
1029
1030 --
1031 -- This procedulre populate all resources for an organization.
1032 --
1033
1034 PROCEDURE populate_org_res_instances( RETCODE OUT NOCOPY number,
1035 arg_refresh_flag IN number,
1036 arg_refresh_number IN number,
1037 arg_organization_id IN number,
1038 arg_sr_instance_id IN number,
1039 arg_start_date IN date,
1040 arg_cutoff_date IN date ) IS
1041
1042 CURSOR c_simulation_set IS
1043 SELECT simulation_set
1044 FROM msc_simulation_sets
1045 WHERE organization_id = arg_organization_id
1046 AND sr_instance_id = arg_sr_instance_id;
1047
1048 var_simulation_set VARCHAR2(10);
1049 var_return_status NUMBER;
1050
1051 BEGIN
1052
1053 LOG_MESSAGE('========================================================');
1054 LOG_MESSAGE('Populating Org Resources for the Org: '|| arg_organization_id);
1055 LOG_MESSAGE('========================================================');
1056
1057 MSC_UTIL.MSC_DEBUG('Creating resource for all simulation set ....');
1058 MSC_UTIL.MSC_DEBUG('Org Id:' || to_char(arg_organization_id));
1059 MSC_UTIL.MSC_DEBUG('Instance:' || to_char(arg_sr_instance_id));
1060
1061
1062 -- For complete refresh, the collection program will handle deleting all
1063 -- resource avail.
1064 -- For net change, refresh_flag = 2, delete resourse instance availability of
1065 -- all department resources with the new refresh number.
1066
1067 if arg_refresh_flag = 2 then
1068 -- v_stmt := 100;
1069
1070 --dbms_output.put_line('Delete msc_net_res_inst_avail');
1071 log_message('Delete msc_net_res_inst_avail');
1072
1073 delete from msc_net_res_inst_avail
1074 where rowid in (select res.rowid
1075 from msc_net_res_inst_avail res,
1076 msc_resource_changes chg,
1077 msc_department_resources dept
1078 where res.organization_id = arg_organization_id
1079 and res.sr_instance_id = arg_sr_instance_id
1080 and res.plan_id = -1
1081 and res.department_id = chg.department_id
1082 and res.resource_id = chg.resource_id
1083 and chg.sr_instance_id = arg_sr_instance_id
1084 and chg.refresh_number = arg_refresh_number
1085 and dept.department_id = chg.department_id
1086 and dept.resource_id = chg.resource_id
1087 and dept.line_flag <> 1
1088 and dept.plan_id = -1
1089 and dept.organization_id = arg_organization_id
1090 and dept.sr_instance_id = arg_sr_instance_id );
1091
1092 --dbms_output.put_line('Number of row deleted from net change ' || sql%rowcount);
1093 log_message('Number of row deleted from net change ' || sql%rowcount);
1094 end if;
1095
1096
1097 -- Populate resource without simulation set
1098
1099 var_simulation_set := NULL;
1100
1101 LOG_MESSAGE(' Populating Org Resources Instances for Null Simulation Set ......');
1102 --dbms_output.put_line('Populating org res inst for null simulation set');
1103
1104 populate_avail_res_instances (
1105 arg_refresh_number,
1106 arg_refresh_flag,
1107 var_simulation_set,
1108 arg_organization_id,
1109 arg_sr_instance_id,
1110 arg_start_date,
1111 arg_cutoff_date);
1112
1113
1114 -- Populate resource for each simulation set belong to the organization
1115
1116 OPEN c_simulation_set;
1117 LOOP
1118 Fetch c_simulation_set into var_simulation_set;
1119
1120 EXIT WHEN c_simulation_set%NOTFOUND;
1121
1122 LOG_MESSAGE(' Populating Org Resources Instances for the Simulation Set :'||var_simulation_set);
1123 --dbms_output.put_line('Populating org res inst for the simulation set ' || var_simulation_set);
1124 populate_avail_res_instances (
1125 arg_refresh_number,
1126 arg_refresh_flag,
1127 var_simulation_set,
1128 arg_organization_id,
1129 arg_sr_instance_id,
1130 arg_start_date,
1131 arg_cutoff_date );
1132
1133 END LOOP;
1134
1135 CLOSE c_simulation_set;
1136 -- COMMIT;
1137
1138 retcode := 0 ;
1139 return;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1144 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1145
1146 IF c_simulation_set%isopen THEN
1147 CLOSE c_simulation_set;
1148 END IF;
1149
1150
1151 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1152
1153 LOG_MESSAGE('========================================');
1154 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1155 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_ORG_RES_INSTANCES');
1156 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RES_INST_AVAIL');
1157 LOG_MESSAGE(FND_MESSAGE.GET);
1158
1159 LOG_MESSAGE(SQLERRM);
1160
1161
1162 END IF;
1163
1164 --dbms_output.put_line('Error in populate_org_res_instance ' || sqlerrm);
1165 retcode :=SQLCODE;
1166 return;
1167
1168 END populate_org_res_instances;
1169
1170 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
1171 IS
1172 BEGIN
1173
1174 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
1175 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
1176 ELSE
1177 --DBMS_OUTPUT.PUT_LINE( pBUFF);
1178 NULL;
1179 END IF;
1180 END LOG_MESSAGE;
1181
1182
1183 END MSC_NET_RES_INST_AVAILABILITY;