[Home] [Help]
PACKAGE BODY: APPS.MSC_RESOURCE_AVAILABILITY
Source
1 PACKAGE BODY MSC_RESOURCE_AVAILABILITY AS
2 /* $Header: MSCRAVLB.pls 120.6 2007/08/27 12:32:05 tramamoo ship $ */
3
4 DELETE_WORKDAY CONSTANT number := 1;
5 CHANGE_WORKDAY CONSTANT number := 2;
6 ADD_WORKDAY CONSTANT number := 3;
7 HOLD_TIME CONSTANT number := 9999999;
8 v_stmt NUMBER;
9
10
11 --v_current_date DATE; -- added for testing the performance
12 --v_current_user NUMBER;
13 v_current_request NUMBER;
14 v_current_login NUMBER;
15 v_current_application NUMBER;
16 v_current_conc_program NUMBER;
17 v_calendar_code VARCHAR2(14);
18 v_calendar_exception_set_id NUMBER;
19
20 /* the following parameters are used for better performance in
21 data collection program */
22 v_old_calendar_code VARCHAR2(14):='*';
23 v_old_calendar_ex_set_id NUMBER:= 0;
24 v_old_start_date DATE:= SYSDATE;
25 v_old_cutoff_date DATE:= SYSDATE;
26 v_old_disable_date DATE:= SYSDATE;
27 v_old_sr_instance_id NUMBER:= 0;
28
29 /* added this variable to show the warning message -bug 3022523*/
30 v_show_warning number;
31
32 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
33 v_workdate DateTab;
34 v_workdate_count NUMBER;
35
36 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
37 IS
38 BEGIN
39
40 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
41 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
42 ELSE
43 --DBMS_OUTPUT.PUT_LINE( pBUFF);
44 NULL;
45 END IF;
46 END LOG_MESSAGE;
47
48 FUNCTION check_24(var_time in number) return number IS
49 BEGIN
50
51 IF var_time > 24*3600 then
52 return var_time - 24*3600;
53 ELSE
54 return var_time;
55 END if;
56
57 END check_24;
58
59 FUNCTION check_start_date(
60 arg_organization_id in number,
61 arg_sr_instance_id in number) return DATE IS
62 v_start_date date;
63
64 BEGIN
65
66 v_stmt := 10;
67 select TRUNC(MIN(calendar_date))
68 into v_start_date
69 from msc_calendar_dates cal
70 ,msc_trading_partners tp
71 where tp.sr_tp_id = arg_organization_id
72 and tp.sr_instance_id = arg_sr_instance_id
73 and tp.partner_type = 3
74 and cal.calendar_code = tp.calendar_code
75 and cal.sr_instance_id = tp.sr_instance_id
76 and cal.exception_set_id = tp.calendar_exception_set_id
77 and cal.seq_num is not null;
78
79 return v_start_date;
80
81 EXCEPTION
82 WHEN no_data_found THEN
83 FND_MESSAGE.SET_NAME('MSC', 'MSC_NRA_NO_CALENDAR');
84 FND_MESSAGE.SET_TOKEN('ORG_ID', arg_organization_id);
85 LOG_MESSAGE(FND_MESSAGE.Get);
86
87 END check_start_date;
88
89 FUNCTION check_cutoff_date(arg_organization_id in number,
90 arg_sr_instance_id in number) return DATE IS
91 v_cutoff_date date;
92
93 BEGIN
94
95 v_stmt := 20;
96 select TRUNC(MAX(calendar_date))
97 into v_cutoff_date
98 from msc_calendar_dates cal
99 ,msc_trading_partners tp
100 where tp.sr_tp_id = arg_organization_id
101 and tp.sr_instance_id = arg_sr_instance_id
102 and tp.partner_type = 3
103 and cal.calendar_code = tp.calendar_code
104 and cal.sr_instance_id = tp.sr_instance_id
105 and cal.exception_set_id = tp.calendar_exception_set_id
106 and cal.seq_num is not null;
107
108 return v_cutoff_date;
109
110 EXCEPTION
111 WHEN no_data_found THEN
112 FND_MESSAGE.SET_NAME('MSC', 'MSC_NRA_NO_CALENDAR');
113 FND_MESSAGE.SET_TOKEN('ORG_ID', arg_organization_id);
114 LOG_MESSAGE(FND_MESSAGE.Get);
115
116 END check_cutoff_date;
117
118 PROCEDURE update_avail(
119 var_rowid in ROWID,
120 var_from_time in number,
121 var_to_time in number) IS
122 var_time1 number;
123 var_time2 number;
124
125 BEGIN
126
127 if var_from_time >= 86400 AND var_to_time >= 86400 then
128 var_time1 := check_24(var_to_time);
129 var_time2 := check_24(var_from_time);
130 else
131 var_time1 := var_to_time;
132 var_time2 := var_from_time;
133 end if;
134
135 v_stmt := 30;
136
137 if var_from_time >= 86400 AND var_to_time >= 86400 then
138
139 UPDATE MSC_net_resource_avail
140 SET to_time = var_time1,
141 from_time =var_time2,
142 shift_date = shift_date + 1
143 WHERE rowid = var_rowid;
144
145 else
146
147 UPDATE MSC_net_resource_avail
148 SET to_time = var_time1,
149 from_time = var_time2
150 WHERE rowid = var_rowid;
151
152 end if;
153
154 if( v_show_warning is null) then
155 v_show_warning := 0;
156 end if;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
161 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
162
163 v_show_warning :=SQLCODE;
164
165
166
167 END update_avail;
168
169 PROCEDURE delete_avail(var_rowid in ROWID) IS
170 BEGIN
171
172 v_stmt := 40;
173 DELETE from MSC_net_resource_avail
174 WHERE rowid = var_rowid;
175
176 if( v_show_warning is null) then
177 v_show_warning := 0;
178 end if;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
183 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
184 v_show_warning :=SQLCODE;
185
186 END delete_avail;
187
188 PROCEDURE insert_avail( var_date in DATE,
189 var_department_id in number,
190 var_resource_id in number,
191 var_organization_id in number,
192 var_sr_instance_id in number,
193 var_shift_num in number,
194 var_simulation_set in varchar2,
195 var_from_time in number,
196 var_to_time in number,
197 var_cap_units in number,
198 var_aggregate_resource_id in number,
199 var_refresh_number in number) IS
200 var_time1 number;
201 var_time2 number;
202 var_transaction_id number;
203
204 BEGIN
205
206 MSC_UTIL.MSC_DEBUG('Org: ' || to_char(var_organization_id));
207 MSC_UTIL.MSC_DEBUG('Instance: ' || to_char(var_sr_instance_id));
208 MSC_UTIL.MSC_DEBUG('Sim Set: ' || var_simulation_set);
209 MSC_UTIL.MSC_DEBUG('Dept: ' || to_char(var_department_id));
210 MSC_UTIL.MSC_DEBUG('Resource: ' || to_char(var_resource_id));
211
212 var_time1 := check_24(var_from_time);
213 var_time2 := check_24(var_to_time);
214
215 v_stmt := 50;
216
217 v_stmt := 60;
218 INSERT into MSC_net_resource_avail(
219 transaction_id,
220 plan_id,
221 department_id,
222 resource_id,
223 organization_id,
224 sr_instance_id,
225 shift_num,
226 shift_date,
227 from_time,
228 to_time,
229 capacity_units,
230 simulation_set,
231 aggregate_resource_id,
232 status,
233 applied,
234 updated,
235 last_update_date,
236 last_updated_by,
237 creation_date,
238 created_by,
239 last_update_login,
240 request_id,
241 program_application_id,
242 program_id,
243 program_update_date,
244 refresh_number)
245 VALUES(
246 msc_net_resource_avail_s.NEXTVAL
247 , -1
248 ,var_department_id
249 ,var_resource_id
250 ,var_organization_id
251 ,var_sr_instance_id
252 ,var_shift_num
253 ,var_date
254 ,var_from_time
255 ,var_to_time
256 ,var_cap_units
257 ,var_simulation_set
258 ,var_aggregate_resource_id
259 ,NULL /* STATUS */
260 ,NULL /* APPLIED */
261 ,2 /* UPDATED */
262 ,MSC_CL_COLLECTION.v_current_date
263 ,MSC_CL_COLLECTION.v_current_user
264 ,MSC_CL_COLLECTION.v_current_date
265 ,MSC_CL_COLLECTION.v_current_user
266 ,v_current_login
267 ,v_current_request
268 ,v_current_application
269 ,v_current_conc_program
270 ,MSC_CL_COLLECTION.v_current_date
271 ,var_refresh_number);
272 if( v_show_warning is null) then
273 v_show_warning := 0;
274 end if;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
279 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
280 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
281 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
282
283 v_show_warning :=SQLCODE;
284
285 END insert_avail;
286
287 PROCEDURE calc_res_avail( arg_organization_id IN number,
288 arg_sr_instance_id IN number,
289 arg_department_id IN number,
290 arg_resource_id IN number,
291 arg_simulation_set IN varchar2,
292 arg_24hr_flag IN number,
293 arg_start_date IN date ,
294 arg_cutoff_date IN date,
295 arg_aggregate_resource_id IN NUMBER,
296 arg_refresh_number IN number,
297 arg_capacity_units IN number,
298 arg_disable_date IN DATE) IS
299
300 CURSOR changes IS
301 SELECT changes.action_type,
302 changes.from_time,
303 DECODE(LEAST(changes.to_time, changes.from_time),
304 changes.to_time, changes.to_time + 24*3600,
305 changes.to_time),
306 dates.shift_date,
307 changes.shift_num,
308 changes.capacity_change
309 from msc_shift_dates dates,
310 msc_resource_changes changes
311 WHERE dates.calendar_code = v_calendar_code
312 AND dates.sr_instance_id = arg_sr_instance_id
313 AND dates.exception_set_id = v_calendar_exception_set_id
314 AND dates.seq_num is not null
315 AND dates.shift_date between changes.from_date AND
316 NVL(changes.to_date, changes.from_date)
317 AND dates.shift_num = changes.shift_num
318 AND changes.to_date >= trunc(arg_start_date)
319 AND changes.from_date <= arg_cutoff_date
320 AND changes.simulation_set = arg_simulation_set
321 AND changes.action_type = CHANGE_WORKDAY
322 AND changes.resource_id = arg_resource_id
323 AND changes.department_id = arg_department_id
324 AND changes.sr_instance_id = arg_sr_instance_id
325 ORDER BY dates.shift_date, changes.from_time;
326
327 var_action_type number;
328 var_from_time number;
329 var_to_time number;
330 var_shift_date date;
331 var_from_shift_time number;
332 var_to_shift_time number;
333 var_orig_cap number;
334 var_shift_num number;
335 var_cap_change number;
336 var_orig_from_time number;
337 var_orig_to_time number;
338 var_next_from_time number;
339 var_rowid rowid;
340 var_rowcount number;
341 var_transaction_id number;
342 var_aggregate_resource_id number;
343 var_calendar_date date;
344 var_capacity number;
345
346 /* add index hint for better performance */
347 CURSOR avail IS
348 SELECT /*+ index (nra MSC_NET_RESOURCE_AVAIL_U2 )*/
349 capacity_units capacity_units,
350 from_time from_time,
351 to_time to_time,
352 rowid
353 FROM MSC_net_resource_avail nra
354 WHERE plan_id = -1
355 AND sr_instance_id = arg_sr_instance_id
356 AND organization_id = arg_organization_id
357 AND department_id = arg_department_id
358 AND resource_id = arg_resource_id
359 AND simulation_set = arg_simulation_set
360 AND shift_num = var_shift_num
361 AND shift_date = var_shift_date
362 UNION ALL
363 SELECT 0 capacity_units,
364 HOLD_TIME from_time,
365 HOLD_TIME to_time,
366 rowid
367 from dual
368 ORDER BY 2, 3;
369
370 BEGIN
371
372 if arg_24hr_flag = 2 THEN
373
374 v_stmt := 70;
375
376 if (arg_disable_date IS NOT NULL) then
377
378 INSERT into MSC_net_resource_avail(
379 transaction_id,
380 plan_id,
381 organization_id,
382 sr_instance_id,
383 department_id,
384 resource_id,
385 shift_num,
386 shift_date,
387 from_time,
388 to_time,
389 capacity_units,
390 simulation_set,
391 aggregate_resource_id,
392 status,
393 applied,
394 updated,
395 last_update_date,
396 last_updated_by,
397 creation_date,
398 created_by,
399 last_update_login,
400 request_id,
401 program_application_id,
402 program_id,
403 program_update_date)
404 SELECT msc_net_resource_avail_s.NEXTVAL
405 ,-1
406 ,arg_organization_id
407 ,arg_sr_instance_id
408 ,arg_department_id
409 ,arg_resource_id
410 ,res_shifts.shift_num
411 ,arg_disable_date
412 ,0
413 ,0
414 ,0
415 ,arg_simulation_set
416 ,arg_aggregate_resource_id
417 ,NULL /* STATUS */
418 ,NULL /* APPLIED */
419 ,2 /* UPDATED */
420 ,MSC_CL_COLLECTION.v_current_date
421 ,MSC_CL_COLLECTION.v_current_user
422 ,MSC_CL_COLLECTION.v_current_date
423 ,MSC_CL_COLLECTION.v_current_user
424 ,v_current_login
425 ,v_current_request
426 ,v_current_application
427 ,v_current_conc_program
428 ,MSC_CL_COLLECTION.v_current_date
429 FROM
430 msc_resource_shifts res_shifts
431 WHERE
432 res_shifts.department_id = arg_department_id
433 AND res_shifts.resource_id = arg_resource_id
434 AND res_shifts.sr_instance_id = arg_sr_instance_id;
435
436 if (arg_disable_date < sysdate) then
437 -- If the disable date is in the past, just return!
438 return;
439 end if;
440
441 end if;
442
443 INSERT into MSC_net_resource_avail(
444 transaction_id,
445 plan_id,
446 organization_id,
447 sr_instance_id,
448 department_id,
449 resource_id,
450 shift_num,
451 shift_date,
452 from_time,
453 to_time,
454 capacity_units,
455 simulation_set,
456 aggregate_resource_id,
457 status,
458 applied,
459 updated,
460 last_update_date,
461 last_updated_by,
462 creation_date,
463 created_by,
464 last_update_login,
465 request_id,
466 program_application_id,
467 program_id,
468 program_update_date)
469 SELECT msc_net_resource_avail_s.NEXTVAL
470 ,-1
471 ,arg_organization_id
472 ,arg_sr_instance_id
473 ,arg_department_id
474 ,arg_resource_id
475 ,res_shifts.shift_num
476 ,dates.shift_date
477 ,shifts.from_time
478 ,decode(least(shifts.from_time,shifts.to_time),shifts.to_time,shifts.to_time+86400,shifts.to_time)
479 ,nvl(res_shifts.capacity_units,arg_capacity_units)
480 ,arg_simulation_set
481 ,arg_aggregate_resource_id
482 ,NULL /* STATUS */
483 ,NULL /* APPLIED */
484 ,2 /* UPDATED */
485 ,MSC_CL_COLLECTION.v_current_date
486 ,MSC_CL_COLLECTION.v_current_user
487 ,MSC_CL_COLLECTION.v_current_date
488 ,MSC_CL_COLLECTION.v_current_user
489 ,v_current_login
490 ,v_current_request
491 ,v_current_application
492 ,v_current_conc_program
493 ,MSC_CL_COLLECTION.v_current_date
494 FROM msc_shift_dates dates,
495 msc_shift_times shifts,
496 msc_resource_shifts res_shifts
497 WHERE dates.calendar_code = v_calendar_code
498 AND dates.sr_instance_id = arg_sr_instance_id
499 AND dates.exception_set_id = v_calendar_exception_set_id
500 AND dates.shift_num = shifts.shift_num
501 AND dates.seq_num is not null
502 AND dates.shift_date >= trunc(arg_start_date)
503 AND dates.shift_date <= least(trunc(arg_cutoff_date),
504 trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
505 AND shifts.shift_num = res_shifts.shift_num
506 AND shifts.calendar_code = v_calendar_code
507 AND shifts.sr_instance_id= arg_sr_instance_id
508 AND res_shifts.department_id = arg_department_id
509 AND res_shifts.resource_id = arg_resource_id
510 AND res_shifts.sr_instance_id = arg_sr_instance_id;
511
512
513 /* Due to the performace issues, the delete_workday is handled
514 by the next UPDATE SQL statements.
515
516 AND ( arg_simulation_set is null
517 OR NOT EXISTS
518 (SELECT NULL
519 FROM msc_resource_changes changes
520 WHERE changes.department_id = arg_department_id
521 AND changes.resource_id = arg_resource_id
522 AND changes.sr_instance_id = arg_sr_instance_id
523 AND changes.shift_num = dates.shift_num
524 AND changes.from_date = dates.shift_date
525 AND changes.simulation_set= arg_simulation_set
526 AND changes.action_type = DELETE_WORKDAY) );
527 */
528
529 IF arg_simulation_set IS NOT NULL THEN
530
531 UPDATE MSC_NET_RESOURCE_AVAIL
532 SET capacity_units= 0
533 WHERE ROWID IN
534 ( select /*+ ordered index (nra MSC_NET_RESOURCE_AVAIL_U2 )*/
535 nra.ROWID
536 from MSC_RESOURCE_CHANGES changes,
537 MSC_NET_RESOURCE_AVAIL nra
538 WHERE changes.department_id = arg_department_id
539 AND changes.resource_id = arg_resource_id
540 AND changes.sr_instance_id = arg_sr_instance_id
541 AND changes.simulation_set= arg_simulation_set
542 AND changes.action_type = DELETE_WORKDAY
543 AND changes.from_date >= trunc(arg_start_date)
544 AND changes.from_date <= arg_cutoff_date
545 AND nra.plan_id= -1
546 AND nra.sr_instance_id= changes.sr_instance_id
547 AND nra.organization_id= arg_organization_id
548 AND nra.simulation_set= changes.simulation_set
549 AND nra.department_id= changes.department_id
550 AND nra.resource_id= changes.resource_id
551 AND nra.shift_num= changes.shift_num
552 AND nra.shift_date= changes.from_date );
553
554 END IF;
555
556 ELSE
557
558 if (arg_disable_date IS NOT NULL) then
559
560 INSERT into MSC_net_resource_avail(
561 transaction_id,
562 plan_id,
563 organization_id,
564 sr_instance_id,
565 department_id,
566 resource_id,
567 shift_num,
568 shift_date,
569 from_time,
570 to_time,
571 capacity_units,
572 simulation_set,
573 aggregate_resource_id,
574 status,
575 applied,
576 updated,
577 last_update_date,
578 last_updated_by,
579 creation_date,
580 created_by,
581 last_update_login,
582 request_id,
583 program_application_id,
584 program_id,
585 program_update_date)
586 VALUES( msc_net_resource_avail_s.NEXTVAL
587 ,-1
588 ,arg_organization_id
589 ,arg_sr_instance_id
590 ,arg_department_id
591 ,arg_resource_id
592 ,0
593 ,arg_disable_date
594 ,0
595 ,0
596 ,0
597 ,arg_simulation_set
598 ,arg_aggregate_resource_id
599 ,NULL /* STATUS */
600 ,NULL /* APPLIED */
601 ,2 /* UPDATED */
602 ,MSC_CL_COLLECTION.v_current_date
603 ,MSC_CL_COLLECTION.v_current_user
604 ,MSC_CL_COLLECTION.v_current_date
605 ,MSC_CL_COLLECTION.v_current_user
606 ,v_current_login
607 ,v_current_request
608 ,v_current_application
609 ,v_current_conc_program
610 ,MSC_CL_COLLECTION.v_current_date);
611
612 if (arg_disable_date < sysdate) then
613 -- If the disable date is in the past, just return!
614 return;
615 end if;
616
617 end if;
618
619 v_stmt := 80;
620
621 IF v_old_calendar_code <> v_calendar_code OR
622 v_old_calendar_ex_set_id <> v_calendar_exception_set_id OR
623 v_old_start_date <> arg_start_date OR
624 v_old_cutoff_date <> arg_cutoff_date OR
625 nvl(v_old_disable_date,trunc(sysdate - 1000)) <>
626 nvl(arg_disable_date, trunc(sysdate - 1000)) OR
627 v_old_sr_instance_id <> arg_sr_instance_id THEN
628
629 BEGIN
630 SELECT dates.calendar_date
631 BULK COLLECT
632 INTO v_workdate
633 FROM msc_calendar_dates dates
634 WHERE dates.calendar_code = v_calendar_code
635 AND dates.exception_set_id = v_calendar_exception_set_id
636 AND dates.sr_instance_id = arg_sr_instance_id
637 AND dates.calendar_date >= trunc(arg_start_date)
638 AND dates.calendar_date <= least(trunc(arg_cutoff_date),
639 trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
640 AND dates.seq_num is not null;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN NULL;
643 WHEN OTHERS THEN RAISE;
644 END;
645 v_workdate_count:= SQL%ROWCOUNT;
646
647 v_old_calendar_code := v_calendar_code;
648 v_old_calendar_ex_set_id := v_calendar_exception_set_id;
649 v_old_start_date := arg_start_date;
650 v_old_cutoff_date := arg_cutoff_date;
651 v_old_disable_date := arg_disable_date;
652 v_old_sr_instance_id := arg_sr_instance_id;
653
654 END IF; -- calendar_code, calendar_exception_set_id
655
656 FORALL j IN 1..v_workdate_count
657 INSERT into MSC_net_resource_avail(
658 transaction_id,
659 plan_id,
660 organization_id,
661 sr_instance_id,
662 department_id,
663 resource_id,
664 shift_num,
665 shift_date,
666 from_time,
667 to_time,
668 capacity_units,
669 simulation_set,
670 aggregate_resource_id,
671 status,
672 applied,
673 updated,
674 last_update_date,
675 last_updated_by,
676 creation_date,
677 created_by,
678 last_update_login,
679 request_id,
680 program_application_id,
681 program_id,
682 program_update_date)
683 VALUES( msc_net_resource_avail_s.NEXTVAL
684 ,-1
685 ,arg_organization_id
686 ,arg_sr_instance_id
687 ,arg_department_id
688 ,arg_resource_id
689 ,0
690 ,v_workdate(j)
691 ,0
692 ,24*60*60
693 ,arg_capacity_units
694 ,arg_simulation_set
695 ,arg_aggregate_resource_id
696 ,NULL /* STATUS */
697 ,NULL /* APPLIED */
698 ,2 /* UPDATED */
699 ,MSC_CL_COLLECTION.v_current_date
700 ,MSC_CL_COLLECTION.v_current_user
701 ,MSC_CL_COLLECTION.v_current_date
702 ,MSC_CL_COLLECTION.v_current_user
703 ,v_current_login
704 ,v_current_request
705 ,v_current_application
706 ,v_current_conc_program
707 ,MSC_CL_COLLECTION.v_current_date);
708
709 END if; -- arg_24hr_flag
710
711
712 IF arg_simulation_set IS NOT NULL THEN
713
714 if arg_24hr_flag = 2 then
715
716 OPEN changes;
717 LOOP
718 FETCH changes INTO
719 var_action_type,
720 var_orig_from_time,
721 var_orig_to_time,
722 var_shift_date,
723 var_shift_num,
724 var_cap_change;
725
726 EXIT WHEN changes%NOTFOUND;
727
728 /*----------------------------------------------------------+
729 | For each modification we get the current resource |
730 | calendar and process sections of the modification that |
731 | overlaps with the shift segment |
732 +----------------------------------------------------------*/
733 -- Initialize the variables
734 var_from_time := var_orig_from_time;
735 var_next_from_time := var_orig_from_time;
736 var_to_time := var_orig_to_time;
737 var_rowcount := 0;
738
739 OPEN avail;
740 LOOP
741 FETCH avail INTO
742 var_orig_cap,
743 var_from_shift_time,
744 var_to_shift_time,
745 var_rowid;
746 EXIT WHEN avail%NOTFOUND;
747
748 -- Set the from time for the modification to the start of
749 -- the unprocessed section
750
751 var_from_time := var_next_from_time;
752
753 -- Set the to time to the original to time of the modification
754 var_to_time := var_orig_to_time;
755
756 -- If you have completely processed the modification you are
757 -- done so exit
758 if (var_from_time > var_to_time) then
759 EXIT;
760 END if;
761
762 var_rowcount := var_rowcount + 1;
763
764 -- If only row is the extra dummy row, you are processing a
765 -- modification for a deleted workday... skip the row
766
767 if var_from_shift_time = HOLD_TIME AND var_rowcount = 1 THEN
768 EXIT;
769
770 -- If this is the dummy extra row and you have not completely
771 -- processed the modification...that is probably because the
772 -- modification does not overlap with the shift..go ahead and
773 -- process it that way
774 elsif var_from_shift_time = HOLD_TIME
775 AND var_from_time <= var_to_time
776 THEN
777 var_from_shift_time := var_from_time - 2;
778 var_to_shift_time := var_from_time - 1;
779 else
780 -- If the modification overlaps a shift segment then set
781 -- the END time of the modification to the least of the
782 -- modification END time or shift end time
783 if (var_from_time < var_to_shift_time) then
784 var_to_time := LEAST(var_to_shift_time, var_to_time);
785 var_next_from_time := var_to_time + 1;
786 else
787 -- Otherwise the modification does not overlap with the
788 -- shift. In that case do not process and leave it for the
789 -- next shift segment
790 goto skip;
791 END if;
792 END if;
793
794 /*
795 If the modification starts before the shift starts and
796 ENDs after the shift starts but on or before the shift ends */
797 if var_from_shift_time > var_from_time AND
798 var_from_shift_time <= var_to_time AND
799 var_to_time <= var_to_shift_time THEN
800
801 if var_to_time < var_to_shift_time then
802 insert_avail(
803 var_shift_date,
804 arg_department_id,
805 arg_resource_id,
806 arg_organization_id,
807 arg_sr_instance_id,
808 var_shift_num,
809 arg_simulation_set,
810 var_from_time,
811 var_from_shift_time - 1,
812 var_cap_change,
813 arg_aggregate_resource_id,
814 arg_refresh_number);
815
816 /* Bug 2727286 */
817 if var_to_time = var_from_shift_time then
818 /* Nothing to do */
819 null;
820
821 else
822 insert_avail(
823 var_shift_date,
824 arg_department_id,
825 arg_resource_id,
826 arg_organization_id,
827 arg_sr_instance_id,
828 var_shift_num,
829 arg_simulation_set,
830 var_from_shift_time,
831 var_to_time,
832 var_orig_cap + var_cap_change,
833 arg_aggregate_resource_id,
834 arg_refresh_number);
835
836 update_avail(
837 var_rowid,
838 var_to_time + 1,
839 var_to_shift_time);
840
841 end if; /* Bug 2727286 */
842
843 /* Otherwise the to time and the shift END time are
844 the same */
845 else
846 delete_avail(
847 var_rowid);
848 insert_avail(
849 var_shift_date,
850 arg_department_id,
851 arg_resource_id,
852 arg_organization_id,
853 arg_sr_instance_id,
854 var_shift_num,
855 arg_simulation_set,
856 var_from_time,
857 var_from_shift_time - 1,
858 var_cap_change,
859 arg_aggregate_resource_id,
860 arg_refresh_number);
861
862 insert_avail(
863 var_shift_date,
864 arg_department_id,
865 arg_resource_id,
866 arg_organization_id,
867 arg_sr_instance_id,
868 var_shift_num,
869 arg_simulation_set,
870 var_from_shift_time,
871 var_to_shift_time,
872 var_orig_cap + var_cap_change,
873 arg_aggregate_resource_id,
874 arg_refresh_number);
875 END if;
876 END if;
877 /* If the modification starts before the shift starts and
878 ENDs before the shift starts */
879 if var_from_shift_time > var_from_time and
880 var_from_shift_time > var_to_time THEN
881 insert_avail(
882 var_shift_date,
883 arg_department_id,
884 arg_resource_id,
885 arg_organization_id,
886 arg_sr_instance_id,
887 var_shift_num,
888 arg_simulation_set,
889 var_from_time,
890 var_to_time,
891 var_cap_change,
892 arg_aggregate_resource_id,
893 arg_refresh_number);
894 END if;
895 /* If the modification starts after the shift starts but ENDs
896 before the shift ENDs */
897 if var_from_time >= var_from_shift_time AND
898 var_to_shift_time >= var_to_time THEN
899 /* If the modification times match the shift time
900 exactly */
901 if var_from_time = var_from_shift_time AND
902 var_to_shift_time = var_to_time THEN
903
904 delete_avail(
905 var_rowid);
906
907 insert_avail(
908 var_shift_date,
909 arg_department_id,
910 arg_resource_id,
911 arg_organization_id,
912 arg_sr_instance_id,
913 var_shift_num,
914 arg_simulation_set,
915 var_from_time,
916 var_to_time,
917 var_orig_cap + var_cap_change,
918 arg_aggregate_resource_id,
919 arg_refresh_number);
920 elsif var_from_time = var_from_shift_time THEN
921 insert_avail(
922 var_shift_date,
923 arg_department_id,
924 arg_resource_id,
925 arg_organization_id,
926 arg_sr_instance_id,
927 var_shift_num,
928 arg_simulation_set,
929 var_from_time,
930 var_to_time,
931 var_orig_cap + var_cap_change,
932 arg_aggregate_resource_id,
933 arg_refresh_number);
934
935 update_avail(
936 var_rowid,
937 var_to_time + 1,
938 var_to_shift_time);
939
940 elsif var_to_shift_time = var_to_time THEN
941 insert_avail(
942 var_shift_date,
943 arg_department_id,
944 arg_resource_id,
945 arg_organization_id,
946 arg_sr_instance_id,
947 var_shift_num,
948 arg_simulation_set,
949 var_from_time,
950 var_to_time,
951 var_orig_cap + var_cap_change,
952 arg_aggregate_resource_id,
953 arg_refresh_number);
954 /*6319294 start */
955 if (var_from_shift_time = var_from_time - 1) then
956 delete_avail(var_rowid);
957 else
958 update_avail(var_rowid,
959 var_from_shift_time,
960 var_from_time - 1);
961 end if;
962 /*6319294 end */
963
964 else
965 insert_avail(
966 var_shift_date,
967 arg_department_id,
968 arg_resource_id,
969 arg_organization_id,
970 arg_sr_instance_id,
971 var_shift_num,
972 arg_simulation_set,
973 var_from_time,
974 var_to_time,
975 var_orig_cap + var_cap_change,
976 arg_aggregate_resource_id,
977 arg_refresh_number);
978
979 update_avail(
980 var_rowid,
981 var_from_shift_time,
982 var_from_time - 1);
983
984 insert_avail(
985 var_shift_date,
986 arg_department_id,
987 arg_resource_id,
988 arg_organization_id,
989 arg_sr_instance_id,
990 var_shift_num,
991 arg_simulation_set,
992 var_to_time + 1,
993 var_to_shift_time,
994 var_orig_cap,
995 arg_aggregate_resource_id,
996 arg_refresh_number);
997 END if;
998 END if;
999
1000 /* If the modification starts after the shift starts and ENDs
1001 after the shift ENDs */
1002 if var_from_shift_time <= var_from_time AND
1003 var_to_shift_time >= var_from_time AND
1004 var_to_time > var_to_shift_time THEN
1005
1006 /* If the shift start and the change start match */
1007 if var_from_shift_time = var_from_time then
1008 delete_avail(
1009 var_rowid);
1010 insert_avail(
1011 var_shift_date,
1012 arg_department_id,
1013 arg_resource_id,
1014 arg_organization_id,
1015 arg_sr_instance_id,
1016 var_shift_num,
1017 arg_simulation_set,
1018 var_from_time,
1019 var_to_shift_time,
1020 var_orig_cap + var_cap_change,
1021 arg_aggregate_resource_id,
1022 arg_refresh_number);
1023
1024 insert_avail(
1025 var_shift_date,
1026 arg_department_id,
1027 arg_resource_id,
1028 arg_organization_id,
1029 arg_sr_instance_id,
1030 var_shift_num,
1031 arg_simulation_set,
1032 var_to_shift_time + 1,
1033 var_to_time,
1034 var_cap_change,
1035 arg_aggregate_resource_id,
1036 arg_refresh_number);
1037 else
1038 update_avail(
1039 var_rowid,
1040 var_from_shift_time,
1041 var_from_time - 1);
1042
1043 insert_avail(
1044 var_shift_date,
1045 arg_department_id,
1046 arg_resource_id,
1047 arg_organization_id,
1048 arg_sr_instance_id,
1049 var_shift_num,
1050 arg_simulation_set,
1051 var_from_time,
1052 var_to_shift_time,
1053 var_orig_cap + var_cap_change,
1054 arg_aggregate_resource_id,
1055 arg_refresh_number);
1056
1057 insert_avail(
1058 var_shift_date,
1059 arg_department_id,
1060 arg_resource_id,
1061 arg_organization_id,
1062 arg_sr_instance_id,
1063 var_shift_num,
1064 arg_simulation_set,
1065 var_to_shift_time + 1,
1066 var_to_time,
1067 var_cap_change,
1068 arg_aggregate_resource_id,
1069 arg_refresh_number);
1070 END if;
1071 END if;
1072 /* If the modification starts after the shift ENDs */
1073 if var_from_time > var_to_shift_time THEN
1074 insert_avail(
1075 var_shift_date,
1076 arg_department_id,
1077 arg_resource_id,
1078 arg_organization_id,
1079 arg_sr_instance_id,
1080 var_shift_num,
1081 arg_simulation_set,
1082 var_from_time,
1083 var_to_time,
1084 var_cap_change,
1085 arg_aggregate_resource_id,
1086 arg_refresh_number);
1087 END if;
1088 <<skip>>
1089 NULL;
1090 END loop;
1091 close avail;
1092 END loop;
1093
1094 close changes;
1095
1096 -- Finally add the availability from the add workday type modifications
1097
1098 v_stmt := 90;
1099 INSERT into MSC_net_resource_avail(
1100 transaction_id,
1101 plan_id,
1102 organization_id,
1103 sr_instance_id,
1104 department_id,
1105 resource_id,
1106 shift_num,
1107 shift_date,
1108 from_time,
1109 to_time,
1110 capacity_units,
1111 simulation_set,
1112 aggregate_resource_id,
1113 status,
1114 applied,
1115 updated,
1116 last_update_date,
1117 last_updated_by,
1118 creation_date,
1119 created_by,
1120 last_update_login,
1121 request_id,
1122 program_application_id,
1123 program_id,
1124 program_update_date,
1125 refresh_number)
1126 SELECT msc_net_resource_avail_s.NEXTVAL
1127 ,-1
1128 ,arg_organization_id
1129 ,arg_sr_instance_id
1130 ,arg_department_id
1131 ,arg_resource_id
1132 ,changes.shift_num
1133 ,changes.from_date
1134 ,changes.from_time
1135 ,changes.to_time
1136 ,changes.capacity_change
1137 ,arg_simulation_set
1138 ,arg_aggregate_resource_id
1139 ,NULL /* STATUS */
1140 ,NULL /* APPLIED */
1141 ,2 /* UPDATED */
1142 ,MSC_CL_COLLECTION.v_current_date
1143 ,MSC_CL_COLLECTION.v_current_user
1144 ,MSC_CL_COLLECTION.v_current_date
1145 ,MSC_CL_COLLECTION.v_current_user
1146 ,v_current_login
1147 ,v_current_request
1148 ,v_current_application
1149 ,v_current_conc_program
1150 ,MSC_CL_COLLECTION.v_current_date
1151 ,arg_refresh_number
1152 FROM msc_resource_changes changes
1153 WHERE changes.department_id = arg_department_id
1154 AND changes.resource_id = arg_resource_id
1155 AND changes.action_type = ADD_WORKDAY
1156 AND changes.simulation_set= arg_simulation_set
1157 AND changes.sr_instance_id = arg_sr_instance_id;
1158
1159 END IF; -- arg_24hr_flag
1160
1161 END IF; -- arg_simulation_set
1162
1163 if( v_show_warning is null) then
1164 v_show_warning := 0;
1165 end if;
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 IF changes%isopen THEN CLOSE changes; END IF;
1170 IF avail%isopen THEN CLOSE avail; END IF;
1171
1172 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1173 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1174 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1175 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1176
1177 v_show_warning := SQLCODE;
1178
1179 END calc_res_avail;
1180
1181 --
1182 -- This procedule is used to populate resource for each simulation set
1183 -- within an organization instance
1184 --
1185
1186 PROCEDURE populate_avail_resources(
1187 arg_refresh_number IN number,
1188 arg_refresh_flag IN number,
1189 arg_simulation_set IN varchar2,
1190 arg_organization_id IN number,
1191 arg_sr_instance_id IN number,
1192 arg_start_date IN date,
1193 arg_cutoff_date IN date) IS
1194
1195 CURSOR dept_res is
1196 SELECT dept_res.department_id,
1197 dept_res.resource_id,
1198 NVL(dept_res.available_24_hours_flag, 2),
1199 dept_res.aggregate_resource_id,
1200 NVL(dept_res.capacity_units,1), --**
1201 dept_res.disable_date, --**
1202 org.calendar_code, --**
1203 org.calendar_exception_set_id --**
1204 FROM msc_trading_partners org,
1205 msc_department_resources dept_res
1206 WHERE dept_res.owning_department_id = dept_res.department_id
1207 AND dept_res.plan_id = -1
1208 AND dept_res.resource_id <> -1
1209 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
1210 -- AND NVL(dept_res.disable_date,sysdate+1) > sysdate
1211 AND dept_res.organization_id = org.sr_tp_id
1212 AND dept_res.sr_instance_id = org.sr_instance_id
1213 AND org.sr_tp_id= arg_organization_id
1214 AND org.sr_instance_id= arg_sr_instance_id
1215 AND org.partner_type=3
1216 ORDER BY
1217 org.calendar_code,
1218 org.calendar_exception_set_id;
1219
1220
1221 CURSOR dept_res_change is
1222 SELECT distinct dept_res.department_id,
1223 dept_res.resource_id,
1224 NVL(dept_res.available_24_hours_flag, 2),
1225 dept_res.aggregate_resource_id,
1226 NVL(dept_res.capacity_units,1), --**
1227 dept_res.disable_date, --**
1228 org.calendar_code, --**
1229 org.calendar_exception_set_id --**
1230 FROM msc_trading_partners org,
1231 msc_resource_changes chg,
1232 msc_department_resources dept_res
1233 WHERE chg.department_id = dept_res.department_id
1234 AND chg.resource_id = dept_res.resource_id
1235 AND chg.sr_instance_id = dept_res.sr_instance_id
1236 AND chg.refresh_number = arg_refresh_number
1237 AND dept_res.owning_department_id = dept_res.department_id
1238 AND dept_res.plan_id = -1
1239 AND dept_res.resource_id <> -1
1240 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
1241 -- AND NVL(dept_res.disable_date,sysdate+1) > sysdate
1242 AND dept_res.organization_id = org.sr_tp_id
1243 AND dept_res.sr_instance_id = org.sr_instance_id
1244 AND org.sr_tp_id= arg_organization_id
1245 AND org.sr_instance_id= arg_sr_instance_id
1246 AND org.partner_type=3
1247 ORDER BY
1248 org.calendar_code,
1249 org.calendar_exception_set_id;
1250
1251 var_department_id NUMBER;
1252 var_resource_id NUMBER;
1253 var_24hr_flag NUMBER;
1254 v_cutoff_date DATE;
1255 v_start_date DATE;
1256 var_aggregate_resource_id NUMBER;
1257
1258 var_capacity_units NUMBER; -- new variables for calling calc_res_avail
1259 var_disable_date DATE;
1260
1261 BEGIN
1262
1263 MSC_CL_COLLECTION.v_current_date:= SYSDATE;
1264 MSC_CL_COLLECTION.v_current_user:= FND_GLOBAL.USER_ID;
1265 v_current_login:= FND_GLOBAL.LOGIN_ID;
1266 v_current_request:= FND_GLOBAL.CONC_REQUEST_ID;
1267 v_current_application:= FND_GLOBAL.PROG_APPL_ID;
1268 v_current_conc_program:= FND_GLOBAL.CONC_PROGRAM_ID;
1269
1270 LOG_MESSAGE('--------------------------------------------------------');
1271 LOG_MESSAGE(' Populating Available Resources.........................');
1272 LOG_MESSAGE('--------------------------------------------------------');
1273 if arg_start_date is null then
1274 v_start_date := check_start_date(arg_organization_id, arg_sr_instance_id);
1275 else
1276 v_start_date := arg_start_date;
1277 end if;
1278
1279 if arg_cutoff_date is null then
1280 v_cutoff_date := check_cutoff_date(arg_organization_id, arg_sr_instance_id);
1281 else
1282 v_cutoff_date := arg_cutoff_date;
1283 end if;
1284
1285 if arg_refresh_flag = 1 then
1286 -- process complete refresh
1287
1288 OPEN dept_res;
1289 LOOP
1290 Fetch dept_res into var_department_id,
1291 var_resource_id,
1292 var_24hr_flag,
1293 var_aggregate_resource_id,
1294 var_capacity_units,
1295 var_disable_date,
1296 v_calendar_code,
1297 v_calendar_exception_set_id;
1298
1299 EXIT WHEN dept_res%NOTFOUND;
1300
1301 calc_res_avail(
1302 arg_organization_id,
1303 arg_sr_instance_id,
1304 var_department_id,
1305 var_resource_id,
1306 arg_simulation_set,
1307 var_24hr_flag,
1308 v_start_date,
1309 v_cutoff_date,
1310 var_aggregate_resource_id,
1311 arg_refresh_number,
1312 var_capacity_units,
1313 var_disable_date);
1314 commit;
1315 SAVEPOINT SP1;
1316 END LOOP;
1317
1318 CLOSE dept_res;
1319
1320 else
1321 -- process all changed department resources
1322
1323 OPEN dept_res_change;
1324 LOOP
1325 Fetch dept_res_change into var_department_id,
1326 var_resource_id,
1327 var_24hr_flag,
1328 var_aggregate_resource_id,
1329 var_capacity_units,
1330 var_disable_date,
1331 v_calendar_code,
1332 v_calendar_exception_set_id;
1333
1334 EXIT WHEN dept_res_change%NOTFOUND;
1335
1336 calc_res_avail(
1337 arg_organization_id,
1338 arg_sr_instance_id,
1339 var_department_id,
1340 var_resource_id,
1341 arg_simulation_set,
1342 var_24hr_flag,
1343 v_start_date,
1344 v_cutoff_date,
1345 var_aggregate_resource_id,
1346 arg_refresh_number,
1347 var_capacity_units,
1348 var_disable_date);
1349 commit;
1350 SAVEPOINT SP1;
1351 END LOOP;
1352
1353 CLOSE dept_res_change;
1354
1355 end if;
1356
1357 -- retcode := 0;
1358 -- return;
1359
1360 if( v_show_warning is null) then
1361 v_show_warning := 0;
1362 end if;
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1367 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1368
1369 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1370 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1371
1372 IF dept_res%isopen THEN CLOSE dept_res; END IF;
1373 IF dept_res_change%isopen THEN CLOSE dept_res_change; END IF;
1374 -- retcode := 1;
1375 -- return;
1376 v_show_warning :=SQLCODE;
1377
1378 END populate_avail_resources;
1379
1380 --
1381 -- This procedulre populate all resources for an organization.
1382 --
1383
1384 PROCEDURE populate_org_resources( RETCODE OUT NOCOPY number,
1385 arg_refresh_flag IN number,
1386 arg_refresh_number IN number,
1387 arg_organization_id IN number,
1388 arg_sr_instance_id IN number,
1389 arg_start_date IN date,
1390 arg_cutoff_date IN date ) IS
1391
1392 CURSOR c_simulation_set IS
1393 SELECT simulation_set
1394 FROM msc_simulation_sets
1395 WHERE organization_id = arg_organization_id
1396 AND sr_instance_id = arg_sr_instance_id;
1397
1398 var_simulation_set VARCHAR2(10);
1399 var_return_status NUMBER;
1400
1401 BEGIN
1402
1403 LOG_MESSAGE('========================================================');
1404 LOG_MESSAGE('Populating Org Resources for the Org: '|| arg_organization_id);
1405 LOG_MESSAGE('========================================================');
1406
1407 MSC_UTIL.MSC_DEBUG('Creating resource for all simulation set ....');
1408 MSC_UTIL.MSC_DEBUG('Org Id:' || to_char(arg_organization_id));
1409 MSC_UTIL.MSC_DEBUG('Instance:' || to_char(arg_sr_instance_id));
1410
1411 -- For complete refresh, the collection program will handle deleting all
1412 -- resource avail.
1413 -- For net change, refresh_flag = 2, delete resourse availability of
1414 -- all department resources with the new refresh number.
1415
1416 if arg_refresh_flag = 2 then
1417 v_stmt := 100;
1418 delete from msc_net_resource_avail
1419 where rowid in (select res.rowid
1420 from msc_net_resource_avail res,
1421 msc_resource_changes chg,
1422 msc_department_resources dept
1423 where res.organization_id = arg_organization_id
1424 and res.sr_instance_id = arg_sr_instance_id
1425 and res.plan_id = -1
1426 and res.department_id = chg.department_id
1427 and res.resource_id = chg.resource_id
1428 and chg.sr_instance_id = arg_sr_instance_id
1429 and chg.refresh_number = arg_refresh_number
1430 and dept.department_id = chg.department_id
1431 and dept.resource_id = chg.resource_id
1432 and dept.line_flag <> 1
1433 and dept.plan_id = -1
1434 and dept.organization_id = arg_organization_id
1435 and dept.sr_instance_id = arg_sr_instance_id );
1436 end if;
1437
1438
1439 -- Populate resource without simulation set
1440
1441 var_simulation_set := NULL;
1442
1443 LOG_MESSAGE(' Populating Org Resources for Null Simulation Set ......');
1444
1445 populate_avail_resources (
1446 arg_refresh_number,
1447 arg_refresh_flag,
1448 var_simulation_set,
1449 arg_organization_id,
1450 arg_sr_instance_id,
1451 arg_start_date,
1452 arg_cutoff_date);
1453
1454
1455 -- Populate resource for each simulation set belong to the organization
1456
1457 OPEN c_simulation_set;
1458 LOOP
1459 Fetch c_simulation_set into var_simulation_set;
1460
1461 EXIT WHEN c_simulation_set%NOTFOUND;
1462
1463 LOG_MESSAGE(' Populating Org Resources for the Simulation Set :'||var_simulation_set);
1464
1465 populate_avail_resources (
1466 arg_refresh_number,
1467 arg_refresh_flag,
1468 var_simulation_set,
1469 arg_organization_id,
1470 arg_sr_instance_id,
1471 arg_start_date,
1472 arg_cutoff_date );
1473
1474 END LOOP;
1475
1476 CLOSE c_simulation_set;
1477 -- COMMIT;
1478
1479
1480
1481 if( v_show_warning is null or v_show_warning =0) then
1482 retcode := 0;
1483 else
1484 retcode:= v_show_warning;
1485 end if;
1486 return;
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1491 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1492
1493 IF c_simulation_set%isopen THEN
1494 CLOSE c_simulation_set;
1495 END IF;
1496
1497 -- fix for 2393358 --
1498 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1499
1500 LOG_MESSAGE('========================================');
1501 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1502 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_ORG_RESOURCES');
1503 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
1504 LOG_MESSAGE(FND_MESSAGE.GET);
1505
1506 LOG_MESSAGE(SQLERRM);
1507
1508
1509 END IF;
1510 --retcode := 1;
1511 retcode :=SQLCODE;
1512 return;
1513
1514 END populate_org_resources;
1515
1516 --
1517 -- This procedure populate all resource information for
1518 -- all lines of an organization
1519 --
1520 PROCEDURE populate_all_lines (
1521 RETCODE OUT NOCOPY number,
1522 arg_refresh_flag IN number,
1523 arg_refresh_number IN number,
1524 arg_organization_id IN number,
1525 arg_sr_instance_id IN number,
1526 arg_start_date IN date,
1527 arg_cutoff_date IN date ) IS
1528
1529 var_line_id NUMBER;
1530 var_calendar_date DATE;
1531 var_start_time NUMBER;
1532 var_stop_time NUMBER;
1533 var_max_rate NUMBER;
1534 v_start_date DATE;
1535 v_cutoff_date DATE;
1536 var_transaction_id NUMBER;
1537
1538 BEGIN
1539
1540 LOG_MESSAGE('======================================================================');
1541 LOG_MESSAGE(' Populating Resources of all lines for the Org: '||arg_organization_id);
1542 LOG_MESSAGE('======================================================================');
1543
1544 -- Determine the start date and cutoff date
1545 if arg_start_date is null then
1546 v_start_date := check_start_date(arg_organization_id, arg_sr_instance_id);
1547 else
1548 v_start_date := arg_start_date;
1549 end if;
1550
1551 if arg_cutoff_date is null then
1552 v_cutoff_date := check_cutoff_date(arg_organization_id, arg_sr_instance_id);
1553 else
1554 v_cutoff_date := arg_cutoff_date;
1555 end if;
1556
1557 MSC_UTIL.MSC_DEBUG('Creating resource for all lines....');
1558 MSC_UTIL.MSC_DEBUG('Org Id:' || to_char(arg_organization_id));
1559 MSC_UTIL.MSC_DEBUG('Instance:' || to_char(arg_sr_instance_id));
1560 MSC_UTIL.MSC_DEBUG('Start Date:' || to_char(v_start_date,'YYYY/MM/DD HH24:MI:SS'));
1561 MSC_UTIL.MSC_DEBUG('Cutoff Date:' || to_char(v_cutoff_date,'YYYY/MM/DD HH24:MI:SS'));
1562
1563 -- For complete refresh, the collection program will handle deleting all
1564 -- resource avail.
1565 -- For net change, refresh_flag = 2, delete resourse availability of
1566 -- lines with the new refresh number.
1567 if arg_refresh_flag = 2 then
1568 v_stmt := 110;
1569 delete from msc_net_resource_avail
1570 where rowid in (select res.rowid
1571 from msc_net_resource_avail res, msc_department_resources line
1572 where res.organization_id = line.organization_id
1573 and res.sr_instance_id = line.sr_instance_id
1574 and res.department_id = line.department_id
1575 and res.resource_id = -1
1576 and line.line_flag = 1
1577 and line.plan_id = -1
1578 and line.refresh_number = arg_refresh_number
1579 and line.organization_id = arg_organization_id
1580 and line.sr_instance_id = arg_sr_instance_id ) ;
1581 end if;
1582
1583 /* 2201418 - Added hints to improve performance. Also defined a new index
1584 on msc_department_resources (line_flag, plan_id, sr_instance_id,
1585 organization_id) */
1586
1587 INSERT into MSC_net_resource_avail(
1588 transaction_id,
1589 plan_id,
1590 organization_id,
1591 sr_instance_id,
1592 department_id,
1593 resource_id,
1594 shift_date,
1595 from_time,
1596 to_time,
1597 capacity_units,
1598 status,
1599 applied,
1600 updated,
1601 last_update_date,
1602 last_updated_by,
1603 creation_date,
1604 created_by,
1605 last_update_login,
1606 request_id,
1607 program_application_id,
1608 program_id,
1609 program_update_date,
1610 refresh_number)
1611 SELECT /*+ leading(line) INDEX(LINE) use_nl(dates) */
1612 msc_net_resource_avail_s.NEXTVAL
1613 ,-1
1614 ,arg_organization_id
1615 ,arg_sr_instance_id
1616 ,line.department_id
1617 ,-1
1618 ,dates.calendar_date
1619 ,line.start_time
1620 ,line.stop_time
1621 ,line.max_rate
1622 ,NULL /*STATUS*/
1623 ,NULL /*APPLIED*/
1624 ,2 /*UPDATED*/
1625 ,SYSDATE
1626 ,FND_GLOBAL.USER_ID
1627 ,SYSDATE
1628 ,FND_GLOBAL.USER_ID
1629 ,FND_GLOBAL.LOGIN_ID
1630 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
1631 ,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
1632 ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1633 ,SYSDATE /* PROGRAM_UPDATE_DATE */
1634 ,arg_refresh_number
1635 FROM msc_calendar_dates dates,
1636 msc_department_resources line,
1637 msc_trading_partners org
1638 WHERE line.organization_id = arg_organization_id
1639 AND line.sr_instance_id = arg_sr_instance_id
1640 AND line.line_flag = 1
1641 AND line.plan_id = -1
1642 AND line.refresh_number = arg_refresh_number
1643 AND NVL(line.disable_date, sysdate+1) > sysdate
1644 AND org.sr_tp_id = line.organization_id
1645 AND org.sr_instance_id = line.sr_instance_id
1646 AND org.partner_type = 3
1647 AND dates.calendar_code = org.calendar_code
1648 AND dates.sr_instance_id = arg_sr_instance_id
1649 AND dates.exception_set_id = org.calendar_exception_set_id
1650 AND dates.calendar_date >= trunc(v_start_date)
1651 AND dates.calendar_date <= least(trunc(v_cutoff_date),
1652 trunc(nvl(line.disable_date-1, v_cutoff_date)) )
1653 AND dates.seq_num is not null;
1654
1655 INSERT into MSC_net_resource_avail(
1656 transaction_id,
1657 plan_id,
1658 organization_id,
1659 sr_instance_id,
1660 department_id,
1661 resource_id,
1662 shift_date,
1663 from_time,
1664 to_time,
1665 capacity_units,
1666 status,
1667 applied,
1668 updated,
1669 last_update_date,
1670 last_updated_by,
1671 creation_date,
1672 created_by,
1673 last_update_login,
1674 request_id,
1675 program_application_id,
1676 program_id,
1677 program_update_date,
1678 refresh_number)
1679 SELECT /*+ leading(line) INDEX(LINE) use_nl(dates) */
1680 msc_net_resource_avail_s.NEXTVAL
1681 ,-1
1682 ,arg_organization_id
1683 ,arg_sr_instance_id
1684 ,line.department_id
1685 ,-1
1686 ,line.disable_date
1687 ,0
1688 ,0
1689 ,0
1690 ,NULL /*STATUS*/
1691 ,NULL /*APPLIED*/
1692 ,2 /*UPDATED*/
1693 ,SYSDATE
1694 ,FND_GLOBAL.USER_ID
1695 ,SYSDATE
1696 ,FND_GLOBAL.USER_ID
1697 ,FND_GLOBAL.LOGIN_ID
1698 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
1699 ,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
1700 ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1701 ,SYSDATE /* PROGRAM_UPDATE_DATE */
1702 ,arg_refresh_number
1703 FROM
1704 msc_department_resources line,
1705 msc_trading_partners org
1706 WHERE line.organization_id = arg_organization_id
1707 AND line.sr_instance_id = arg_sr_instance_id
1708 AND line.line_flag = 1
1709 AND line.plan_id = -1
1710 AND line.refresh_number = arg_refresh_number
1711 AND line.disable_date IS NOT NULL
1712 AND org.sr_tp_id = line.organization_id
1713 AND org.sr_instance_id = line.sr_instance_id
1714 AND org.partner_type = 3;
1715
1716 --COMMIT;
1717 retcode := 0;
1718 return;
1719
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1723 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1724 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1725 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1726
1727 -- fix for 2393358 --
1728 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1729
1730 LOG_MESSAGE('========================================');
1731 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1732 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_ALL_LINES');
1733 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
1734 LOG_MESSAGE(FND_MESSAGE.GET);
1735
1736 LOG_MESSAGE(SQLERRM);
1737
1738
1739 END IF;
1740
1741 --retcode := 1;
1742 retcode :=SQLCODE;
1743 return;
1744
1745 END populate_all_lines;
1746 PROCEDURE COMPUTE_RES_AVAIL (ERRBUF OUT NOCOPY VARCHAR2,
1747 RETCODE OUT NOCOPY NUMBER,
1748 pINSTANCE_ID IN NUMBER,
1749 pSTART_DATE IN VARCHAR2)
1750 IS
1751 lv_start_date DATE := TO_DATE(pSTART_DATE, 'YYYY/MM/DD HH24:MI:SS');
1752 lv_retval BOOLEAN;
1753 lv_dummy1 VARCHAR2(32);
1754 lv_dummy2 VARCHAR2(32);
1755 lv_ret_res_ava number;
1756 lv_where_clause varchar2(500) := NULL;
1757
1758 BEGIN
1759
1760 lv_retval := FND_INSTALLATION.GET_APP_INFO(
1761 'FND', lv_dummy1,lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
1762 /* initialize the variables */
1763 SELECT
1764 APPS_VER,
1765 SYSDATE,
1766 SYSDATE,
1767 FND_GLOBAL.USER_ID,
1768 SYSDATE,
1769 FND_GLOBAL.USER_ID,
1770 UPPER(INSTANCE_CODE), /* Bug 2129155 */
1771 INSTANCE_TYPE, -- OPM
1772 nvl(LCID,0)
1773 INTO
1774 MSC_CL_COLLECTION.v_apps_ver,
1775 MSC_CL_COLLECTION.START_TIME,
1776 MSC_CL_COLLECTION.v_current_date,
1777 MSC_CL_COLLECTION.v_current_user,
1778 MSC_CL_COLLECTION.v_current_date,
1779 MSC_CL_COLLECTION.v_current_user,
1780 MSC_CL_COLLECTION.v_instance_code,
1781 MSC_CL_COLLECTION.v_instance_type, -- OPM
1782 MSC_CL_COLLECTION.v_last_collection_id
1783 FROM MSC_APPS_INSTANCES
1784 WHERE INSTANCE_ID= pINSTANCE_ID;
1785
1786 MSC_CL_COLLECTION.v_is_complete_refresh := TRUE;
1787 MSC_CL_COLLECTION.v_is_incremental_refresh := FALSE;
1788 MSC_CL_COLLECTION.v_is_partial_refresh := FALSE;
1789
1790 MSC_CL_COLLECTION.v_instance_id := pINSTANCE_ID;
1791
1792 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Start date : '|| lv_start_date);
1793
1794 lv_where_clause := ' AND ORGANIZATION_ID IN ( SELECT SR_TP_ID FROM MSC_TRADING_PARTNERS WHERE '||
1795 ' SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id ||
1796 ' AND ORGANIZATION_TYPE =1 ) ';
1797
1798 -- log_debug('before delete of MSC_NET_RESOURCE_AVAIL debug0 ');
1799 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_DISCRETE OR MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1800 -- log_debug('before delete of MSC_NET_RESOURCE_AVAIL ');
1801 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
1802 COMMIT;
1803 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
1804 COMMIT;
1805
1806 /* call the function to calc. resource avail */
1807 lv_ret_res_ava:=CALC_RESOURCE_AVAILABILITY(lv_start_date-1,MSC_UTIL.G_ALL_ORGANIZATIONS,TRUE);
1808
1809
1810 IF lv_ret_res_ava = 2 THEN
1811 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1812 ERRBUF:= FND_MESSAGE.GET;
1813 RETCODE:= MSC_UTIL.G_WARNING;
1814 ELSIF lv_ret_res_ava <> 0 THEN
1815 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1816 ERRBUF:= FND_MESSAGE.GET;
1817 RETCODE:= MSC_UTIL.G_ERROR;
1818
1819 END IF;
1820
1821 ELSIF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_MIXED THEN
1822 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-07');
1823 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
1824 COMMIT;
1825 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
1826 COMMIT;
1827
1828
1829 lv_ret_res_ava:=CALC_RESOURCE_AVAILABILITY(lv_start_date-1,MSC_UTIL.G_ALL_ORGANIZATIONS,TRUE);
1830
1831
1832 IF lv_ret_res_ava = 2 THEN
1833 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1834 ERRBUF:= FND_MESSAGE.GET;
1835 RETCODE:= MSC_UTIL.G_WARNING;
1836 ELSIF lv_ret_res_ava <> 0 THEN
1837 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1838
1839 ERRBUF:= FND_MESSAGE.GET;
1840 RETCODE:= MSC_UTIL.G_ERROR;
1841 END IF;
1842 ELSE
1843 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This program can be run only for Instance Type: Discrete.');
1844 ERRBUF:= FND_MESSAGE.GET;
1845 RETCODE:= MSC_UTIL.G_ERROR;
1846
1847 END IF;
1848
1849 EXCEPTION
1850 WHEN OTHERS THEN
1851 ROLLBACK;
1852 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1853 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1854 RETCODE := MSC_UTIL.G_ERROR;
1855
1856 END COMPUTE_RES_AVAIL;
1857
1858 --==============================================================
1859
1860 /*Resource start time changes*/
1861 FUNCTION CALC_RESOURCE_AVAILABILITY (pSTART_TIME IN DATE,
1862 pORG_GROUP IN VARCHAR2,
1863 pSTANDALONE BOOLEAN)
1864 RETURN NUMBER IS
1865
1866 lv_ret_code NUMBER;
1867 lv_refresh_flag NUMBER;
1868 lv_temp_ret_flag NUMBER;
1869 /*Resource Start TIme*/
1870 CURR_DATE DATE;
1871
1872 lv_task_start_time DATE;
1873 lv_task_end_time DATE;
1874
1875 lv_mrp_cutoff_date_offset NUMBER; -- Months
1876 ex_calc_res_avail EXCEPTION; -- fix for 2393358
1877 lv_res_avail_before_sysdate NUMBER; -- Days
1878
1879 CURSOR c1 IS
1880 SELECT tp.Organization_ID
1881 FROM MSC_PARAMETERS tp,
1882 MSC_INSTANCE_ORGS ins_org,
1883 MSC_TRADING_PARTNERS mtp
1884 WHERE tp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1885 AND ins_org.SR_INSTANCE_ID=tp.SR_INSTANCE_ID
1886 AND ins_org.Organization_ID=tp.ORGANIZATION_ID
1887 AND ins_org.ENABLED_FLAG= MSC_UTIL.SYS_YES
1888 AND ((pORG_GROUP = MSC_UTIL.G_ALL_ORGANIZATIONS ) OR (ins_org.ORG_GROUP=pORG_GROUP))
1889 AND mtp.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1890 AND mtp.sr_tp_id = tp.organization_id
1891 AND mtp.partner_type = 3
1892 AND mtp.organization_type = 1; -- Discrete Mfg.
1893
1894
1895 /************** LEGACY_CHANGE_START*************************/
1896
1897 CURSOR c2 IS
1898 SELECT 1
1899 FROM MSC_ST_RESOURCE_CHANGES
1900 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1901 AND process_flag = 5;
1902
1903 lv_changes_exists NUMBER := 0;
1904 /*****************LEGACY_CHANGE_ENDS************************/
1905
1906 BEGIN
1907 /* Resource Start Time changes*/
1908 lv_task_start_time:= pSTART_TIME;
1909 CURR_DATE:= SYSDATE;
1910
1911 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1912 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CALC_RESOURCE_AVAILABILITY');
1913 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1914
1915 lv_mrp_cutoff_date_offset:= TO_NUMBER(FND_PROFILE.VAlUE('MRP_CUTOFF_DATE_OFFSET'));
1916 lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
1917
1918 IF pSTANDALONE THEN
1919 lv_task_end_time := ADD_MONTHS(lv_task_start_time,lv_mrp_cutoff_date_offset);
1920 ELSE
1921 lv_task_end_time := ADD_MONTHS(lv_task_start_time,lv_mrp_cutoff_date_offset) + lv_res_avail_before_sysdate;
1922 END IF;
1923
1924 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_mrp_cutoff_date_offset:'||lv_mrp_cutoff_date_offset);
1925 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_res_avail_before_sysdate:'||lv_res_avail_before_sysdate);
1926 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_task_end_time:'||lv_task_end_time);
1927 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_task_start_time:' ||lv_task_start_time);
1928 --- PREPLACE CHANGE START ---
1929 /*
1930 IF v_is_complete_refresh THEN
1931 lv_refresh_flag:= 1;
1932 ELSE
1933 lv_refresh_flag:= 2;
1934 END IF;
1935 */
1936
1937 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1938 lv_refresh_flag := 1;
1939 ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1940 lv_refresh_flag := 2;
1941 ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1942 lv_refresh_flag := 1; -- Functionality is same as complete_refresh
1943 END IF;
1944
1945 /************** LEGACY_CHANGE_START*************************/
1946 -- Calling the program as complete refresh for legacy so that new
1947 -- records coming in are considered
1948
1949 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1950 lv_refresh_flag := 1;
1951 END IF;
1952 /*****************LEGACY_CHANGE_ENDS************************/
1953
1954 --- PREPLACE CHANGE END ---
1955
1956 -- USING DEFALUT VALUE FOR START DATE AND CUTOFF DATE
1957
1958 SAVEPOINT SP1;
1959
1960 FOR c_rec IN c1 LOOP
1961
1962 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RESOURCES');
1963
1964 POPULATE_ORG_RESOURCES
1965 ( lv_ret_code,
1966 lv_refresh_flag,
1967 MSC_CL_COLLECTION.v_last_collection_id,
1968 c_rec.organization_id,
1969 MSC_CL_COLLECTION.v_instance_id,
1970 lv_task_start_time,
1971 lv_task_end_time);
1972
1973 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
1974
1975 IF lv_ret_code <> 0 THEN
1976 ROLLBACK WORK TO SAVEPOINT SP1;
1977 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
1978 lv_temp_ret_flag:=1;
1979 RAISE ex_calc_res_avail;
1980 else
1981 lv_temp_ret_flag:=2;
1982 END IF;
1983 ELSE
1984 COMMIT;
1985 SAVEPOINT SP1;
1986 END IF;
1987
1988 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ALL_LINES');
1989
1990 POPULATE_ALL_LINES
1991 ( lv_ret_code,
1992 lv_refresh_flag,
1993 MSC_CL_COLLECTION.v_last_collection_id,
1994 c_rec.organization_id,
1995 MSC_CL_COLLECTION.v_instance_id,
1996 lv_task_start_time,
1997 lv_task_end_time);
1998
1999 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2000
2001 IF lv_ret_code <> 0 THEN
2002 ROLLBACK WORK TO SAVEPOINT SP1;
2003 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2004 lv_temp_ret_flag:=1;
2005 RAISE ex_calc_res_avail;
2006 else
2007 lv_temp_ret_flag:=2;
2008 END IF;
2009 ELSE
2010 COMMIT;
2011 SAVEPOINT SP1;
2012 END IF;
2013
2014 /* yvon: resource instanc eavail changes start */
2015 -----------------------------------------------------
2016 -- populate resource instance availability
2017 -----------------------------------------------------
2018 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RES_INSTANCES');
2019
2020 MSC_NET_RES_INST_AVAILABILITY.POPULATE_ORG_RES_INSTANCES
2021 ( lv_ret_code,
2022 lv_refresh_flag,
2023 MSC_CL_COLLECTION.v_last_collection_id,
2024 c_rec.organization_id,
2025 MSC_CL_COLLECTION.v_instance_id,
2026 lv_task_start_time,
2027 lv_task_end_time);
2028
2029 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2030
2031 IF lv_ret_code <> 0 THEN
2032 ROLLBACK WORK TO SAVEPOINT SP1;
2033 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2034 lv_temp_ret_flag:=1;
2035 RAISE ex_calc_res_avail;
2036 else
2037 lv_temp_ret_flag:=2;
2038 END IF;
2039 ELSE
2040 COMMIT;
2041 SAVEPOINT SP1;
2042 END IF;
2043 /* yvon: resource instanc eavail changes end */
2044
2045 END LOOP;
2046
2047 /************** LEGACY_CHANGE_START*************************/
2048
2049 -- This is to enable resource changes to be
2050 -- considered for legacy. Both, the resource information and
2051 -- resource changes may come in at the same time for legacy.
2052
2053 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2054
2055 OPEN C2;
2056 FETCH C2 INTO lv_changes_exists;
2057 CLOSE C2;
2058
2059 IF lv_changes_exists = 1 THEN
2060
2061 lv_refresh_flag := 2;
2062
2063 -- USING DEFALUT VALUE FOR START DATE AND CUTOFF DATE
2064
2065 SAVEPOINT SP1;
2066
2067 FOR c_rec IN c1 LOOP
2068
2069 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RESOURCES');
2070
2071 POPULATE_ORG_RESOURCES
2072 ( lv_ret_code,
2073 lv_refresh_flag,
2074 MSC_CL_COLLECTION.v_last_collection_id,
2075 c_rec.organization_id,
2076 MSC_CL_COLLECTION.v_instance_id,
2077 lv_task_start_time,
2078 lv_task_end_time);
2079
2080 IF lv_ret_code <> 0 THEN
2081 ROLLBACK WORK TO SAVEPOINT SP1;
2082 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2083 lv_temp_ret_flag:=1;
2084 RAISE ex_calc_res_avail;
2085 else
2086 lv_temp_ret_flag:=2;
2087 END IF;
2088 ELSE
2089 COMMIT;
2090 SAVEPOINT SP1;
2091 END IF;
2092
2093 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ALL_LINES');
2094
2095 POPULATE_ALL_LINES
2096 ( lv_ret_code,
2097 lv_refresh_flag,
2098 MSC_CL_COLLECTION.v_last_collection_id,
2099 c_rec.organization_id,
2100 MSC_CL_COLLECTION.v_instance_id,
2101 lv_task_start_time,
2102 lv_task_end_time);
2103
2104 IF lv_ret_code <> 0 THEN
2105 ROLLBACK WORK TO SAVEPOINT SP1;
2106 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2107 lv_temp_ret_flag:=1;
2108 RAISE ex_calc_res_avail;
2109 else
2110 lv_temp_ret_flag:=2;
2111 END IF;
2112 ELSE
2113 COMMIT;
2114 SAVEPOINT SP1;
2115 END IF;
2116
2117 /* yvon: resource instanc eavail changes start */
2118 -----------------------------------------------------
2119 -- populate resource instance availability
2120 -----------------------------------------------------
2121 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RES_INSTANCES');
2122
2123 MSC_NET_RES_INST_AVAILABILITY.POPULATE_ORG_RES_INSTANCES
2124 ( lv_ret_code,
2125 lv_refresh_flag,
2126 MSC_CL_COLLECTION.v_last_collection_id,
2127 c_rec.organization_id,
2128 MSC_CL_COLLECTION.v_instance_id,
2129 lv_task_start_time,
2130 lv_task_end_time);
2131
2132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2133
2134 IF lv_ret_code <> 0 THEN
2135 ROLLBACK WORK TO SAVEPOINT SP1;
2136 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2137 lv_temp_ret_flag:=1;
2138 RAISE ex_calc_res_avail;
2139 else
2140 lv_temp_ret_flag:=2;
2141 END IF;
2142 ELSE
2143 COMMIT;
2144 SAVEPOINT SP1;
2145 END IF;
2146 /* yvon: resource instanc eavail changes end */
2147
2148 END LOOP;
2149 END IF; -- lv_changes_exists
2150 END IF; -- MSC_UTIL.G_INS_OTHER
2151
2152 /************** LEGACY_CHANGE_ENDS*************************/
2153
2154 /* Bug 3295824 - We need to set the capacity units to 0 of any records
2155 having -ve capacity units */
2156
2157 update MSC_net_resource_avail
2158 set capacity_units = 0
2159 where capacity_units < 0
2160 and plan_id = -1
2161 AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2162 AND simulation_set is not null
2163 and shift_date between trunc(lv_task_start_time) and
2164 lv_task_end_time;
2165
2166 COMMIT;
2167
2168 /* End Bug 3295824 */
2169
2170 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
2171 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
2172 TO_CHAR(CEIL((SYSDATE- CURR_DATE)*14400.0)/10));
2173 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2174
2175
2176
2177
2178 IF (lv_temp_ret_flag=2 ) THEN
2179 return lv_temp_ret_flag;
2180 else
2181 RETURN 0;
2182 END IF;
2183
2184 EXCEPTION
2185
2186 WHEN OTHERS THEN
2187
2188 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2189
2190 IF (lv_temp_ret_flag=1 ) THEN
2191 return lv_temp_ret_flag;
2192 else
2193 RETURN SQLCODE;
2194 END IF;
2195
2196 END CALC_RESOURCE_AVAILABILITY;
2197
2198
2199 --==================================================================
2200
2201 --=======================================================================
2202 --The following procedure also include the DS change for the
2203 --MSC_NET_RES_INST_AVAIL
2204 --=======================================================================
2205 PROCEDURE LOAD_NET_RESOURCE_AVAIL IS
2206
2207 -- OPM
2208 -- cursor to select the rows from net_resource_avail. process mfg only
2209 CURSOR c11 (org_id NUMBER) IS
2210 SELECT
2211 msnra.organization_id,
2212 msnra.sr_instance_id,
2213 msnra.resource_id,
2214 msnra.department_id,
2215 msnra.simulation_set,
2216 msnra.shift_num,
2217 msnra.shift_date,
2218 msnra.from_time,
2219 msnra.to_time,
2220 msnra.capacity_units
2221 FROM msc_st_net_resource_avail msnra
2222 WHERE msnra.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
2223 msnra.organization_id=org_id;
2224
2225
2226 ---------------------------------------------------------------------
2227 -- adding the change for the msc_st_net_res_avail for DS
2228 ---------------------------------------------------------------------
2229
2230 -- OPM
2231 -- cursor to select the rows from net_res_inst_avail. process mfg only
2232 CURSOR c_res_inst (org_id NUMBER) IS
2233 SELECT
2234 msnria.sr_instance_id,
2235 msnria.res_instance_id,
2236 msnria.resource_id,
2237 msnria.department_id,
2238 msnria.organization_id,
2239 msnria.serial_number,
2240 t1.inventory_item_id equipment_item_id,
2241 msnria.simulation_set,
2242 msnria.shift_num,
2243 msnria.shift_date,
2244 msnria.from_time,
2245 msnria.to_time
2246 FROM msc_st_net_res_inst_avail msnria,
2247 MSC_ITEM_ID_LID t1
2248 WHERE msnria.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2249 and t1.sr_instance_id (+) = msnria.sr_instance_id
2250 and t1.sr_inventory_item_id (+) = msnria.equipment_item_id
2251 and msnria.organization_id=org_id;
2252
2253 CURSOR c_org_list IS
2254 select organization_id
2255 from msc_instance_orgs mio,
2256 msc_trading_partners mtp
2257 where mio.sr_instance_id= MSC_CL_COLLECTION.v_instance_id and
2258 mio.enabled_flag= 1 and
2259 ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (mio.org_group = MSC_CL_COLLECTION.v_coll_prec.org_group_flag)) and
2260 mio.sr_instance_id=mtp.sr_instance_id and
2261 mio.organization_id=mtp.sr_tp_id and
2262 mtp.partner_type=3 and
2263 mtp.organization_type=2;
2264
2265 c_count NUMBER:= 0;
2266 lv_res_avail NUMBER := MSC_UTIL.SYS_NO;
2267 lv_res_inst_avail NUMBER := MSC_UTIL.SYS_NO;
2268 lv_sql_stmt VARCHAR2(2048);
2269
2270 BEGIN
2271
2272 IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
2273
2274 /*IF (v_is_complete_refresh OR (v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag = MSC_CL_COLLECTION.SYS_YES)) THEN
2275 -- We want to delete all NRA related data and get new stuff.
2276
2277 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
2278 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
2279
2280 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS THEN
2281 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-00');
2282 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
2283 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
2284 ELSE
2285 v_sub_str :=' AND ORGANIZATION_ID '||v_in_org_str;
2286 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-01');
2287 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,v_sub_str);
2288 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,v_sub_str);
2289 END IF;
2290
2291 END IF;*/
2292
2293 -- process mfg only. move the rows for the st table to the msc table
2294 -- for net resource avail
2295 IF MSC_CL_COLLECTION.v_process_flag = MSC_UTIL.SYS_YES THEN
2296
2297 /*
2298 We will do a bulk insert of res avail for OPM orgs. If this fails,
2299 then we will switch to old, row by row processing.
2300
2301 The same applies to collection of net res instance avail data as well.
2302 */
2303
2304 FOR c_rec1 IN c_org_list LOOP
2305 BEGIN
2306
2307 SAVEPOINT LOAD_RES_AVAIL_SP;
2308
2309 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading res avail for OPM orgs : ' || c_rec1.organization_id);
2310
2311 lv_sql_stmt:=
2312 ' INSERT into MSC_net_resource_avail '
2313 ||' ( transaction_id,'
2314 ||' plan_id,'
2315 ||' department_id,'
2316 ||' resource_id,'
2317 ||' organization_id,'
2318 ||' sr_instance_id,'
2319 ||' shift_num,'
2320 ||' shift_date,'
2321 ||' from_time,'
2322 ||' to_time,'
2323 ||' capacity_units,'
2324 ||' simulation_set,'
2325 ||' status,'
2326 ||' applied,'
2327 ||' updated,'
2328 ||' last_update_date,'
2329 ||' last_updated_by,'
2330 ||' creation_date,'
2331 ||' created_by,'
2332 ||' refresh_number)'
2333 ||' SELECT'
2334 ||' msc_net_resource_avail_s.NEXTVAL,'
2335 ||' -1,'
2336 ||' msnra.department_id,'
2337 ||' msnra.resource_id,'
2338 ||' msnra.organization_id,'
2339 ||' msnra.sr_instance_id,'
2340 ||' msnra.shift_num,'
2341 ||' msnra.shift_date,'
2342 ||' msnra.from_time,'
2343 ||' msnra.to_time,'
2344 ||' msnra.capacity_units,'
2345 ||' msnra.simulation_set,'
2346 ||' NULL,' /* STATUS */
2347 ||' NULL,' /* APPLIED */
2348 ||' 2,' /* UPDATED */
2349 ||' :v_current_date,'
2350 ||' :v_current_user,'
2351 ||' :v_current_date,'
2352 ||' :v_current_user,'
2353 ||' :v_last_collection_id'
2354 ||' FROM msc_st_net_resource_avail msnra'
2355 ||' WHERE msnra.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2356 ||' AND msnra.organization_id = ' ||c_rec1.organization_id;
2357
2358 EXECUTE IMMEDIATE lv_sql_stmt
2359 USING
2360 MSC_CL_COLLECTION.v_current_date,
2361 MSC_CL_COLLECTION.v_current_user,
2362 MSC_CL_COLLECTION.v_current_date,
2363 MSC_CL_COLLECTION.v_current_user,
2364 MSC_CL_COLLECTION.v_last_collection_id;
2365
2366 COMMIT;
2367
2368 lv_res_avail:=MSC_UTIL.SYS_YES;
2369
2370 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loaded res avail for OPM orgs : ' || c_rec1.organization_id);
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374
2375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_NET_RESOURCE_AVAIL>>');
2376 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2377 ROLLBACK WORK TO SAVEPOINT LOAD_RES_AVAIL_SP;
2378 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing for org : ' || c_rec1.organization_id);
2379 END;
2380
2381 IF lv_res_avail = MSC_UTIL.SYS_NO THEN
2382 c_count:= 0;
2383
2384 FOR c_rec IN c11(c_rec1.organization_id) LOOP
2385
2386 BEGIN
2387 INSERT into MSC_net_resource_avail(
2388 transaction_id,
2389 plan_id,
2390 department_id,
2391 resource_id,
2392 organization_id,
2393 sr_instance_id,
2394 shift_num,
2395 shift_date,
2396 from_time,
2397 to_time,
2398 capacity_units,
2399 simulation_set,
2400 status,
2401 applied,
2402 updated,
2403 last_update_date,
2404 last_updated_by,
2405 creation_date,
2406 created_by,
2407 refresh_number)
2408 VALUES(
2409 msc_net_resource_avail_s.NEXTVAL,
2410 -1,
2411 c_rec.department_id,
2412 c_rec.resource_id,
2413 c_rec.organization_id,
2414 c_rec.sr_instance_id,
2415 c_rec.shift_num,
2416 c_rec.shift_date,
2417 c_rec.from_time,
2418 c_rec.to_time,
2419 c_rec.capacity_units,
2420 c_rec.simulation_set,
2421 NULL, /* STATUS */
2422 NULL, /* APPLIED */
2423 2, /* UPDATED */
2424 MSC_CL_COLLECTION.v_current_date,
2425 MSC_CL_COLLECTION.v_current_user,
2426 MSC_CL_COLLECTION.v_current_date,
2427 MSC_CL_COLLECTION.v_current_user,
2428 MSC_CL_COLLECTION.v_last_collection_id);
2429
2430 c_count:= c_count+1;
2431
2432 IF c_count> MSC_CL_COLLECTION.PBS THEN
2433 COMMIT;
2434 c_count:= 0;
2435 END IF;
2436
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439
2440 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2441
2442 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2443 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2444 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2445 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
2446 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2447
2448 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2449 RAISE;
2450
2451 ELSE
2452
2453 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2454
2455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2456 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2457 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2458 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
2459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2460
2461 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2462 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2463 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
2464 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2465
2466 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2467 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2468 FND_MESSAGE.SET_TOKEN('VALUE',
2469 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2470 MSC_CL_COLLECTION.v_instance_id));
2471 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2472
2473 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2474 END IF;
2475
2476 END;
2477
2478 END LOOP;
2479
2480 END IF; /* If lv_res_avail:=MSC_CL_COLLECTION.SYS_NO */
2481 END LOOP;
2482
2483 ------------------------------------------------------------------------
2484 -- here is the change for msc_net_res_inst_avail (DS change)
2485 ------------------------------------------------------------------------
2486 FOR c_rec1 IN c_org_list LOOP
2487 BEGIN
2488
2489 SAVEPOINT LOAD_RES_INST_AVAIL_SP;
2490
2491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading res instance avail for OPM orgs : ' || c_rec1.organization_id);
2492
2493 lv_sql_stmt:=
2494 ' INSERT into MSC_net_res_inst_avail '
2495 ||' ( inst_transaction_id,'
2496 ||' plan_id,'
2497 ||' sr_instance_id,'
2498 ||' organization_id,'
2499 ||' department_id,'
2500 ||' resource_id,'
2501 ||' res_instance_id,'
2502 ||' equipment_item_id,'
2503 ||' parent_id,'
2504 ||' serial_number,'
2505 ||' simulation_set,'
2506 ||' shift_num,'
2507 ||' shift_date,'
2508 ||' from_time,'
2509 ||' to_time,'
2510 ||' status,'
2511 ||' applied,'
2512 ||' updated,'
2513 ||' last_update_date,'
2514 ||' last_updated_by,'
2515 ||' creation_date,'
2516 ||' created_by,'
2517 ||' refresh_number)'
2518 ||' SELECT'
2519 ||' msc_net_res_inst_avail_s.NEXTVAL,'
2520 ||' -1,'
2521 ||' msnria.sr_instance_id,'
2522 ||' msnria.organization_id,'
2523 ||' msnria.department_id,'
2524 ||' msnria.resource_id,'
2525 ||' msnria.res_instance_id,'
2526 ||' t1.inventory_item_id,'
2527 ||' NULL,'
2528 ||' msnria.serial_number,'
2529 ||' msnria.simulation_set,'
2530 ||' msnria.shift_num,'
2531 ||' msnria.shift_date,'
2532 ||' msnria.from_time,'
2533 ||' msnria.to_time,'
2534 ||' NULL,' /* STATUS */
2535 ||' NULL,' /* APPLIED */
2536 ||' 2,' /* UPDATED */
2537 ||' :v_current_date,'
2538 ||' :v_current_user,'
2539 ||' :v_current_date,'
2540 ||' :v_current_user,'
2541 ||' :v_last_collection_id'
2542 ||' FROM msc_st_net_res_inst_avail msnria,'
2543 ||' MSC_ITEM_ID_LID t1'
2544 ||' WHERE msnria.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2545 ||' and t1.sr_instance_id (+) = msnria.sr_instance_id'
2546 ||' and t1.sr_inventory_item_id (+) = msnria.equipment_item_id'
2547 ||' and msnria.organization_id = ' ||c_rec1.organization_id;
2548
2549 EXECUTE IMMEDIATE lv_sql_stmt
2550 USING
2551 MSC_CL_COLLECTION.v_current_date,
2552 MSC_CL_COLLECTION.v_current_user,
2553 MSC_CL_COLLECTION.v_current_date,
2554 MSC_CL_COLLECTION.v_current_user,
2555 MSC_CL_COLLECTION.v_last_collection_id;
2556
2557 COMMIT;
2558
2559 lv_res_inst_avail:=MSC_UTIL.SYS_YES;
2560
2561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loaded res instance avail for OPM orgs : ' || c_rec1.organization_id);
2562
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565
2566 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_NET_RESOURCE_INSTANCE_AVAIL>>');
2567 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2568 ROLLBACK WORK TO SAVEPOINT LOAD_RES_INST_AVAIL_SP;
2569 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing for org ' || c_rec1.organization_id);
2570 END;
2571
2572 IF lv_res_inst_avail = MSC_UTIL.SYS_NO THEN
2573
2574 c_count:= 0;
2575
2576 FOR c_rec_resinst IN c_res_inst(c_rec1.organization_id) LOOP
2577
2578 BEGIN
2579
2580 INSERT into MSC_net_res_inst_avail(
2581 inst_transaction_id,
2582 plan_id,
2583 sr_instance_id,
2584 organization_id,
2585 department_id,
2586 resource_id,
2587 res_instance_id,
2588 equipment_item_id,
2589 parent_id,
2590 serial_number,
2591 simulation_set,
2592 shift_num,
2593 shift_date,
2594 from_time,
2595 to_time,
2596 status,
2597 applied,
2598 updated,
2599 last_update_date,
2600 last_updated_by,
2601 creation_date,
2602 created_by,
2603 refresh_number)
2604 VALUES(
2605 msc_net_res_inst_avail_s.NEXTVAL,
2606 -1,
2607 c_rec_resinst.sr_instance_id,
2608 c_rec_resinst.organization_id,
2609 c_rec_resinst.department_id,
2610 c_rec_resinst.resource_id,
2611 c_rec_resinst.res_instance_id,
2612 c_rec_resinst.equipment_item_id,
2613 NULL,
2614 c_rec_resinst.serial_number,
2615 c_rec_resinst.simulation_set,
2616 c_rec_resinst.shift_num,
2617 c_rec_resinst.shift_date,
2618 c_rec_resinst.from_time,
2619 c_rec_resinst.to_time,
2620 NULL, /* STATUS */
2621 NULL, /* APPLIED */
2622 2, /* UPDATED */
2623 MSC_CL_COLLECTION.v_current_date,
2624 MSC_CL_COLLECTION.v_current_user,
2625 MSC_CL_COLLECTION.v_current_date,
2626 MSC_CL_COLLECTION.v_current_user,
2627 MSC_CL_COLLECTION.v_last_collection_id);
2628
2629 c_count:= c_count+1;
2630
2631 IF c_count> MSC_CL_COLLECTION.PBS THEN
2632 COMMIT;
2633 c_count:= 0;
2634 END IF;
2635
2636 EXCEPTION
2637 WHEN OTHERS THEN
2638
2639 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2640
2641 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2642 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2643 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2644 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RES_INST_AVAIL');
2645 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2646
2647 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2648 RAISE;
2649
2650 ELSE
2651
2652 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2653
2654 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2655 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2656 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2657 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RES_INST_AVAIL');
2658 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2659
2660 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2661 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2662 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec_resinst.RESOURCE_ID));
2663 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2664
2665 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2666 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2667 FND_MESSAGE.SET_TOKEN('VALUE',
2668 MSC_GET_NAME.ORG_CODE( c_rec_resinst.ORGANIZATION_ID,
2669 MSC_CL_COLLECTION.v_instance_id));
2670 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2671
2672 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2673 END IF;
2674
2675 END;
2676
2677 END LOOP;
2678 END IF; /* IF lv_res_inst_avail = MSC_CL_COLLECTION.SYS_NO THEN */
2679 END LOOP;
2680
2681 COMMIT;
2682
2683 END IF;
2684
2685 END IF; -- recalc_nra
2686
2687 END LOAD_NET_RESOURCE_AVAIL;
2688
2689 --===================================================================
2690
2691 END MSC_resource_availability;