[Home] [Help]
PACKAGE BODY: APPS.MSC_RESOURCE_AVAILABILITY
Source
1 PACKAGE BODY MSC_RESOURCE_AVAILABILITY AS
2 /* $Header: MSCRAVLB.pls 120.10 2012/03/15 06:54:34 ramepai 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 ,decode(changes.action_type,DELETE_WORKDAY,0,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 (select distinct department_id, resource_id,
498 sr_instance_id, simulation_set,
499 from_date, shift_num,action_type
500 From MSC_RESOURCE_CHANGES
501 where action_type = DELETE_WORKDAY ) changes --7705958
502 WHERE dates.calendar_code = v_calendar_code
503 AND dates.sr_instance_id = arg_sr_instance_id
504 AND dates.exception_set_id = v_calendar_exception_set_id
505 AND dates.shift_num = shifts.shift_num
506 AND dates.seq_num is not null
507 AND dates.shift_date >= trunc(arg_start_date)
508 AND dates.shift_date <= least(trunc(arg_cutoff_date),
509 trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
510 AND shifts.shift_num = res_shifts.shift_num
511 AND shifts.calendar_code = v_calendar_code
512 AND shifts.sr_instance_id= arg_sr_instance_id
513 AND res_shifts.department_id = arg_department_id
514 AND res_shifts.resource_id = arg_resource_id
515 AND res_shifts.sr_instance_id = arg_sr_instance_id
516 /* Bug 6648494 incorporated here */
517 AND changes.department_id (+) = arg_department_id
518 AND changes.resource_id (+) = arg_resource_id
519 AND changes.sr_instance_id (+) = arg_sr_instance_id
520 AND changes.simulation_set (+) = arg_simulation_set
521 AND changes.from_date (+) = dates.shift_date
522 AND changes.shift_num (+) = dates.shift_num;
523
524
525 /* Due to the performace issues, the delete_workday is handled
526 by the next UPDATE SQL statements.
527
528 AND ( arg_simulation_set is null
529 OR NOT EXISTS
530 (SELECT NULL
531 FROM msc_resource_changes changes
532 WHERE changes.department_id = arg_department_id
533 AND changes.resource_id = arg_resource_id
534 AND changes.sr_instance_id = arg_sr_instance_id
535 AND changes.shift_num = dates.shift_num
536 AND changes.from_date = dates.shift_date
537 AND changes.simulation_set= arg_simulation_set
538 AND changes.action_type = DELETE_WORKDAY) );
539
540
541 IF arg_simulation_set IS NOT NULL THEN
542
543 UPDATE MSC_NET_RESOURCE_AVAIL
544 SET capacity_units= 0
545 WHERE ROWID IN
546 ( select /*+ ordered index (nra MSC_NET_RESOURCE_AVAIL_U2 )*/
547 /* nra.ROWID
548 from MSC_RESOURCE_CHANGES changes,
549 MSC_NET_RESOURCE_AVAIL nra
550 WHERE changes.department_id = arg_department_id
551 AND changes.resource_id = arg_resource_id
552 AND changes.sr_instance_id = arg_sr_instance_id
553 AND changes.simulation_set= arg_simulation_set
554 AND changes.action_type = DELETE_WORKDAY
555 AND changes.from_date >= trunc(arg_start_date)
556 AND changes.from_date <= arg_cutoff_date
557 AND nra.plan_id= -1
558 AND nra.sr_instance_id= changes.sr_instance_id
559 AND nra.organization_id= arg_organization_id
560 AND nra.simulation_set= changes.simulation_set
561 AND nra.department_id= changes.department_id
562 AND nra.resource_id= changes.resource_id
563 AND nra.shift_num= changes.shift_num
564 AND nra.shift_date= changes.from_date );
565
566 END IF; /*6648494 */
567
568 ELSE
569
570 if (arg_disable_date IS NOT NULL) then
571
572 INSERT into MSC_net_resource_avail(
573 transaction_id,
574 plan_id,
575 organization_id,
576 sr_instance_id,
577 department_id,
578 resource_id,
579 shift_num,
580 shift_date,
581 from_time,
582 to_time,
583 capacity_units,
584 simulation_set,
585 aggregate_resource_id,
586 status,
587 applied,
588 updated,
589 last_update_date,
590 last_updated_by,
591 creation_date,
592 created_by,
593 last_update_login,
594 request_id,
595 program_application_id,
596 program_id,
597 program_update_date)
598 VALUES( msc_net_resource_avail_s.NEXTVAL
599 ,-1
600 ,arg_organization_id
601 ,arg_sr_instance_id
602 ,arg_department_id
603 ,arg_resource_id
604 ,0
605 ,arg_disable_date
606 ,0
607 ,0
608 ,0
609 ,arg_simulation_set
610 ,arg_aggregate_resource_id
611 ,NULL /* STATUS */
612 ,NULL /* APPLIED */
613 ,2 /* UPDATED */
614 ,MSC_CL_COLLECTION.v_current_date
615 ,MSC_CL_COLLECTION.v_current_user
616 ,MSC_CL_COLLECTION.v_current_date
617 ,MSC_CL_COLLECTION.v_current_user
618 ,v_current_login
619 ,v_current_request
620 ,v_current_application
621 ,v_current_conc_program
622 ,MSC_CL_COLLECTION.v_current_date);
623
624 if (arg_disable_date < sysdate) then
625 -- If the disable date is in the past, just return!
626 return;
627 end if;
628
629 end if;
630
631 v_stmt := 80;
632
633 IF v_old_calendar_code <> v_calendar_code OR
634 v_old_calendar_ex_set_id <> v_calendar_exception_set_id OR
635 v_old_start_date <> arg_start_date OR
636 v_old_cutoff_date <> arg_cutoff_date OR
637 nvl(v_old_disable_date,trunc(sysdate - 1000)) <>
638 nvl(arg_disable_date, trunc(sysdate - 1000)) OR
639 v_old_sr_instance_id <> arg_sr_instance_id THEN
640
641 BEGIN
642 SELECT dates.calendar_date
643 BULK COLLECT
644 INTO v_workdate
645 FROM msc_calendar_dates dates
646 WHERE dates.calendar_code = v_calendar_code
647 AND dates.exception_set_id = v_calendar_exception_set_id
648 AND dates.sr_instance_id = arg_sr_instance_id
649 AND dates.calendar_date >= trunc(arg_start_date)
650 AND dates.calendar_date <= least(trunc(arg_cutoff_date),
651 trunc(NVL(arg_disable_date-1,arg_cutoff_date)))
652 AND dates.seq_num is not null;
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN NULL;
655 WHEN OTHERS THEN RAISE;
656 END;
657 v_workdate_count:= SQL%ROWCOUNT;
658
659 v_old_calendar_code := v_calendar_code;
660 v_old_calendar_ex_set_id := v_calendar_exception_set_id;
661 v_old_start_date := arg_start_date;
662 v_old_cutoff_date := arg_cutoff_date;
663 v_old_disable_date := arg_disable_date;
664 v_old_sr_instance_id := arg_sr_instance_id;
665
666 END IF; -- calendar_code, calendar_exception_set_id
667
668 FORALL j IN 1..v_workdate_count
669 INSERT into MSC_net_resource_avail(
670 transaction_id,
671 plan_id,
672 organization_id,
673 sr_instance_id,
674 department_id,
675 resource_id,
676 shift_num,
677 shift_date,
678 from_time,
679 to_time,
680 capacity_units,
681 simulation_set,
682 aggregate_resource_id,
683 status,
684 applied,
685 updated,
686 last_update_date,
687 last_updated_by,
688 creation_date,
689 created_by,
690 last_update_login,
691 request_id,
692 program_application_id,
693 program_id,
694 program_update_date)
695 VALUES( msc_net_resource_avail_s.NEXTVAL
696 ,-1
697 ,arg_organization_id
698 ,arg_sr_instance_id
699 ,arg_department_id
700 ,arg_resource_id
701 ,0
702 ,v_workdate(j)
703 ,0
704 ,24*60*60
705 ,arg_capacity_units
706 ,arg_simulation_set
707 ,arg_aggregate_resource_id
708 ,NULL /* STATUS */
709 ,NULL /* APPLIED */
710 ,2 /* UPDATED */
711 ,MSC_CL_COLLECTION.v_current_date
712 ,MSC_CL_COLLECTION.v_current_user
713 ,MSC_CL_COLLECTION.v_current_date
714 ,MSC_CL_COLLECTION.v_current_user
715 ,v_current_login
716 ,v_current_request
717 ,v_current_application
718 ,v_current_conc_program
719 ,MSC_CL_COLLECTION.v_current_date);
720
721 END if; -- arg_24hr_flag
722
723
724 IF arg_simulation_set IS NOT NULL THEN
725
726 if arg_24hr_flag = 2 then
727
728 OPEN changes;
729 LOOP
730 FETCH changes INTO
731 var_action_type,
732 var_orig_from_time,
733 var_orig_to_time,
734 var_shift_date,
735 var_shift_num,
736 var_cap_change;
737
738 EXIT WHEN changes%NOTFOUND;
739
740 /*----------------------------------------------------------+
741 | For each modification we get the current resource |
742 | calendar and process sections of the modification that |
743 | overlaps with the shift segment |
744 +----------------------------------------------------------*/
745 -- Initialize the variables
746 var_from_time := var_orig_from_time;
747 var_next_from_time := var_orig_from_time;
748 var_to_time := var_orig_to_time;
749 var_rowcount := 0;
750
751 OPEN avail;
752 LOOP
753 FETCH avail INTO
754 var_orig_cap,
755 var_from_shift_time,
756 var_to_shift_time,
757 var_rowid;
758 EXIT WHEN avail%NOTFOUND;
759
760 -- Set the from time for the modification to the start of
761 -- the unprocessed section
762
763 var_from_time := var_next_from_time;
764
765 -- Set the to time to the original to time of the modification
766 var_to_time := var_orig_to_time;
767
768 -- If you have completely processed the modification you are
769 -- done so exit
770 if (var_from_time > var_to_time) then
771 EXIT;
772 END if;
773
774 var_rowcount := var_rowcount + 1;
775
776 -- If only row is the extra dummy row, you are processing a
777 -- modification for a deleted workday... skip the row
778
779 if var_from_shift_time = HOLD_TIME AND var_rowcount = 1 THEN
780 EXIT;
781
782 -- If this is the dummy extra row and you have not completely
783 -- processed the modification...that is probably because the
784 -- modification does not overlap with the shift..go ahead and
785 -- process it that way
786 elsif var_from_shift_time = HOLD_TIME
787 AND var_from_time <= var_to_time
788 THEN
789 var_from_shift_time := var_from_time - 2;
790 var_to_shift_time := var_from_time - 1;
791 else
792 -- If the modification overlaps a shift segment then set
793 -- the END time of the modification to the least of the
794 -- modification END time or shift end time
795 if (var_from_time < var_to_shift_time) then
796 var_to_time := LEAST(var_to_shift_time, var_to_time);
797 var_next_from_time := var_to_time + 1;
798 else
799 -- Otherwise the modification does not overlap with the
800 -- shift. In that case do not process and leave it for the
801 -- next shift segment
802 goto skip;
803 END if;
804 END if;
805
806 /*
807 If the modification starts before the shift starts and
808 ENDs after the shift starts but on or before the shift ends */
809 if var_from_shift_time > var_from_time AND
810 var_from_shift_time <= var_to_time AND
811 var_to_time <= var_to_shift_time THEN
812
813 if var_to_time < var_to_shift_time then
814 insert_avail(
815 var_shift_date,
816 arg_department_id,
817 arg_resource_id,
818 arg_organization_id,
819 arg_sr_instance_id,
820 var_shift_num,
821 arg_simulation_set,
822 var_from_time,
823 var_from_shift_time - 1,
824 var_cap_change,
825 arg_aggregate_resource_id,
826 arg_refresh_number);
827
828 /* Bug 2727286 */
829 if var_to_time = var_from_shift_time then
830 /* Nothing to do */
831 null;
832
833 else
834 insert_avail(
835 var_shift_date,
836 arg_department_id,
837 arg_resource_id,
838 arg_organization_id,
839 arg_sr_instance_id,
840 var_shift_num,
841 arg_simulation_set,
842 var_from_shift_time,
843 var_to_time,
844 var_orig_cap + var_cap_change,
845 arg_aggregate_resource_id,
846 arg_refresh_number);
847
848 update_avail(
849 var_rowid,
850 var_to_time + 1,
851 var_to_shift_time);
852
853 end if; /* Bug 2727286 */
854
855 /* Otherwise the to time and the shift END time are
856 the same */
857 else
858 delete_avail(
859 var_rowid);
860 insert_avail(
861 var_shift_date,
862 arg_department_id,
863 arg_resource_id,
864 arg_organization_id,
865 arg_sr_instance_id,
866 var_shift_num,
867 arg_simulation_set,
868 var_from_time,
869 var_from_shift_time - 1,
870 var_cap_change,
871 arg_aggregate_resource_id,
872 arg_refresh_number);
873
874 insert_avail(
875 var_shift_date,
876 arg_department_id,
877 arg_resource_id,
878 arg_organization_id,
879 arg_sr_instance_id,
880 var_shift_num,
881 arg_simulation_set,
882 var_from_shift_time,
883 var_to_shift_time,
884 var_orig_cap + var_cap_change,
885 arg_aggregate_resource_id,
886 arg_refresh_number);
887 END if;
888 END if;
889 /* If the modification starts before the shift starts and
890 ENDs before the shift starts */
891 if var_from_shift_time > var_from_time and
892 var_from_shift_time > var_to_time THEN
893 insert_avail(
894 var_shift_date,
895 arg_department_id,
896 arg_resource_id,
897 arg_organization_id,
898 arg_sr_instance_id,
899 var_shift_num,
900 arg_simulation_set,
901 var_from_time,
902 var_to_time,
903 var_cap_change,
904 arg_aggregate_resource_id,
905 arg_refresh_number);
906 END if;
907 /* If the modification starts after the shift starts but ENDs
908 before the shift ENDs */
909 if var_from_time >= var_from_shift_time AND
910 var_to_shift_time >= var_to_time THEN
911 /* If the modification times match the shift time
912 exactly */
913 if var_from_time = var_from_shift_time AND
914 var_to_shift_time = var_to_time THEN
915
916 delete_avail(
917 var_rowid);
918
919 insert_avail(
920 var_shift_date,
921 arg_department_id,
922 arg_resource_id,
923 arg_organization_id,
924 arg_sr_instance_id,
925 var_shift_num,
926 arg_simulation_set,
927 var_from_time,
928 var_to_time,
929 var_orig_cap + var_cap_change,
930 arg_aggregate_resource_id,
931 arg_refresh_number);
932 elsif var_from_time = var_from_shift_time THEN
933 insert_avail(
934 var_shift_date,
935 arg_department_id,
936 arg_resource_id,
937 arg_organization_id,
938 arg_sr_instance_id,
939 var_shift_num,
940 arg_simulation_set,
941 var_from_time,
942 var_to_time,
943 var_orig_cap + var_cap_change,
944 arg_aggregate_resource_id,
945 arg_refresh_number);
946
947 update_avail(
948 var_rowid,
949 var_to_time + 1,
950 var_to_shift_time);
951
952 elsif var_to_shift_time = var_to_time THEN
953 insert_avail(
954 var_shift_date,
955 arg_department_id,
956 arg_resource_id,
957 arg_organization_id,
958 arg_sr_instance_id,
959 var_shift_num,
960 arg_simulation_set,
961 var_from_time,
962 var_to_time,
963 var_orig_cap + var_cap_change,
964 arg_aggregate_resource_id,
965 arg_refresh_number);
966 /*6319294 start */
967 if (var_from_shift_time = var_from_time - 1) then
968 delete_avail(var_rowid);
969 else
970 update_avail(var_rowid,
971 var_from_shift_time,
972 var_from_time - 1);
973 end if;
974 /*6319294 end */
975
976 else
977 insert_avail(
978 var_shift_date,
979 arg_department_id,
980 arg_resource_id,
981 arg_organization_id,
982 arg_sr_instance_id,
983 var_shift_num,
984 arg_simulation_set,
985 var_from_time,
986 var_to_time,
987 var_orig_cap + var_cap_change,
988 arg_aggregate_resource_id,
989 arg_refresh_number);
990
991 update_avail(
992 var_rowid,
993 var_from_shift_time,
994 var_from_time - 1);
995
996 insert_avail(
997 var_shift_date,
998 arg_department_id,
999 arg_resource_id,
1000 arg_organization_id,
1001 arg_sr_instance_id,
1002 var_shift_num,
1003 arg_simulation_set,
1004 var_to_time + 1,
1005 var_to_shift_time,
1006 var_orig_cap,
1007 arg_aggregate_resource_id,
1008 arg_refresh_number);
1009 END if;
1010 END if;
1011
1012 /* If the modification starts after the shift starts and ENDs
1013 after the shift ENDs */
1014 if var_from_shift_time <= var_from_time AND
1015 var_to_shift_time >= var_from_time AND
1016 var_to_time > var_to_shift_time THEN
1017
1018 /* If the shift start and the change start match */
1019 if var_from_shift_time = var_from_time then
1020 delete_avail(
1021 var_rowid);
1022 insert_avail(
1023 var_shift_date,
1024 arg_department_id,
1025 arg_resource_id,
1026 arg_organization_id,
1027 arg_sr_instance_id,
1028 var_shift_num,
1029 arg_simulation_set,
1030 var_from_time,
1031 var_to_shift_time,
1032 var_orig_cap + var_cap_change,
1033 arg_aggregate_resource_id,
1034 arg_refresh_number);
1035
1036 insert_avail(
1037 var_shift_date,
1038 arg_department_id,
1039 arg_resource_id,
1040 arg_organization_id,
1041 arg_sr_instance_id,
1042 var_shift_num,
1043 arg_simulation_set,
1044 var_to_shift_time + 1,
1045 var_to_time,
1046 var_cap_change,
1047 arg_aggregate_resource_id,
1048 arg_refresh_number);
1049 else
1050 update_avail(
1051 var_rowid,
1052 var_from_shift_time,
1053 var_from_time - 1);
1054
1055 insert_avail(
1056 var_shift_date,
1057 arg_department_id,
1058 arg_resource_id,
1059 arg_organization_id,
1060 arg_sr_instance_id,
1061 var_shift_num,
1062 arg_simulation_set,
1063 var_from_time,
1064 var_to_shift_time,
1065 var_orig_cap + var_cap_change,
1066 arg_aggregate_resource_id,
1067 arg_refresh_number);
1068
1069 insert_avail(
1070 var_shift_date,
1071 arg_department_id,
1072 arg_resource_id,
1073 arg_organization_id,
1074 arg_sr_instance_id,
1075 var_shift_num,
1076 arg_simulation_set,
1077 var_to_shift_time + 1,
1078 var_to_time,
1079 var_cap_change,
1080 arg_aggregate_resource_id,
1081 arg_refresh_number);
1082 END if;
1083 END if;
1084 /* If the modification starts after the shift ENDs */
1085 if var_from_time > var_to_shift_time THEN
1086 insert_avail(
1087 var_shift_date,
1088 arg_department_id,
1089 arg_resource_id,
1090 arg_organization_id,
1091 arg_sr_instance_id,
1092 var_shift_num,
1093 arg_simulation_set,
1094 var_from_time,
1095 var_to_time,
1096 var_cap_change,
1097 arg_aggregate_resource_id,
1098 arg_refresh_number);
1099 END if;
1100 <<skip>>
1101 NULL;
1102 END loop;
1103 close avail;
1104 END loop;
1105
1106 close changes;
1107
1108 -- Finally add the availability from the add workday type modifications
1109
1110 v_stmt := 90;
1111 INSERT into MSC_net_resource_avail(
1112 transaction_id,
1113 plan_id,
1114 organization_id,
1115 sr_instance_id,
1116 department_id,
1117 resource_id,
1118 shift_num,
1119 shift_date,
1120 from_time,
1121 to_time,
1122 capacity_units,
1123 simulation_set,
1124 aggregate_resource_id,
1125 status,
1126 applied,
1127 updated,
1128 last_update_date,
1129 last_updated_by,
1130 creation_date,
1131 created_by,
1132 last_update_login,
1133 request_id,
1134 program_application_id,
1135 program_id,
1136 program_update_date,
1137 refresh_number)
1138 SELECT msc_net_resource_avail_s.NEXTVAL
1139 ,-1
1140 ,arg_organization_id
1141 ,arg_sr_instance_id
1142 ,arg_department_id
1143 ,arg_resource_id
1144 ,changes.shift_num
1145 ,changes.from_date
1146 ,changes.from_time
1147 ,changes.to_time
1148 ,changes.capacity_change
1149 ,arg_simulation_set
1150 ,arg_aggregate_resource_id
1151 ,NULL /* STATUS */
1152 ,NULL /* APPLIED */
1153 ,2 /* UPDATED */
1154 ,MSC_CL_COLLECTION.v_current_date
1155 ,MSC_CL_COLLECTION.v_current_user
1156 ,MSC_CL_COLLECTION.v_current_date
1157 ,MSC_CL_COLLECTION.v_current_user
1158 ,v_current_login
1159 ,v_current_request
1160 ,v_current_application
1161 ,v_current_conc_program
1162 ,MSC_CL_COLLECTION.v_current_date
1163 ,arg_refresh_number
1164 FROM msc_resource_changes changes
1165 WHERE changes.department_id = arg_department_id
1166 AND changes.resource_id = arg_resource_id
1167 AND changes.action_type = ADD_WORKDAY
1168 AND changes.simulation_set= arg_simulation_set
1169 AND changes.sr_instance_id = arg_sr_instance_id;
1170
1171 END IF; -- arg_24hr_flag
1172
1173 END IF; -- arg_simulation_set
1174
1175 if( v_show_warning is null) then
1176 v_show_warning := 0;
1177 end if;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 IF changes%isopen THEN CLOSE changes; END IF;
1182 IF avail%isopen THEN CLOSE avail; END IF;
1183
1184 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1185 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1186 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1187 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1188
1189 v_show_warning := SQLCODE;
1190
1191 END calc_res_avail;
1192
1193 --
1194 -- This procedule is used to populate resource for each simulation set
1195 -- within an organization instance
1196 --
1197
1198 PROCEDURE populate_avail_resources(
1199 arg_refresh_number IN number,
1200 arg_refresh_flag IN number,
1201 arg_simulation_set IN varchar2,
1202 arg_organization_id IN number,
1203 arg_sr_instance_id IN number,
1204 arg_start_date IN date,
1205 arg_cutoff_date IN date) IS
1206
1207 CURSOR dept_res is
1208 SELECT dept_res.department_id,
1209 dept_res.resource_id,
1210 NVL(dept_res.available_24_hours_flag, 2),
1211 dept_res.aggregate_resource_id,
1212 NVL(dept_res.capacity_units,1), --**
1213 dept_res.disable_date, --**
1214 org.calendar_code, --**
1215 org.calendar_exception_set_id --**
1216 FROM msc_trading_partners org,
1217 msc_department_resources dept_res
1218 WHERE dept_res.owning_department_id = dept_res.department_id
1219 AND dept_res.plan_id = -1
1220 AND dept_res.resource_id <> -1
1221 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
1222 -- AND NVL(dept_res.disable_date,sysdate+1) > sysdate
1223 AND dept_res.organization_id = org.sr_tp_id
1224 AND dept_res.sr_instance_id = org.sr_instance_id
1225 AND org.sr_tp_id= arg_organization_id
1226 AND org.sr_instance_id= arg_sr_instance_id
1227 AND org.partner_type=3
1228 ORDER BY
1229 org.calendar_code,
1230 org.calendar_exception_set_id;
1231
1232
1233 CURSOR dept_res_change is
1234 SELECT distinct dept_res.department_id,
1235 dept_res.resource_id,
1236 NVL(dept_res.available_24_hours_flag, 2),
1237 dept_res.aggregate_resource_id,
1238 NVL(dept_res.capacity_units,1), --**
1239 dept_res.disable_date, --**
1240 org.calendar_code, --**
1241 org.calendar_exception_set_id --**
1242 FROM msc_trading_partners org,
1243 msc_resource_changes chg,
1244 msc_department_resources dept_res
1245 WHERE chg.department_id = dept_res.department_id
1246 AND chg.resource_id = dept_res.resource_id
1247 AND chg.sr_instance_id = dept_res.sr_instance_id
1248 AND chg.refresh_number = arg_refresh_number
1249 AND dept_res.owning_department_id = dept_res.department_id
1250 AND dept_res.plan_id = -1
1251 AND dept_res.resource_id <> -1
1252 AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
1253 -- AND NVL(dept_res.disable_date,sysdate+1) > sysdate
1254 AND dept_res.organization_id = org.sr_tp_id
1255 AND dept_res.sr_instance_id = org.sr_instance_id
1256 AND org.sr_tp_id= arg_organization_id
1257 AND org.sr_instance_id= arg_sr_instance_id
1258 AND org.partner_type=3
1259 ORDER BY
1260 org.calendar_code,
1261 org.calendar_exception_set_id;
1262
1263 var_department_id NUMBER;
1264 var_resource_id NUMBER;
1265 var_24hr_flag NUMBER;
1266 v_cutoff_date DATE;
1267 v_start_date DATE;
1268 var_aggregate_resource_id NUMBER;
1269
1270 var_capacity_units NUMBER; -- new variables for calling calc_res_avail
1271 var_disable_date DATE;
1272
1273 BEGIN
1274
1275 MSC_CL_COLLECTION.v_current_date:= SYSDATE;
1276 MSC_CL_COLLECTION.v_current_user:= FND_GLOBAL.USER_ID;
1277 v_current_login:= FND_GLOBAL.LOGIN_ID;
1278 v_current_request:= FND_GLOBAL.CONC_REQUEST_ID;
1279 v_current_application:= FND_GLOBAL.PROG_APPL_ID;
1280 v_current_conc_program:= FND_GLOBAL.CONC_PROGRAM_ID;
1281
1282 LOG_MESSAGE('--------------------------------------------------------');
1283 LOG_MESSAGE(' Populating Available Resources.........................');
1284 LOG_MESSAGE('--------------------------------------------------------');
1285 if arg_start_date is null then
1286 v_start_date := check_start_date(arg_organization_id, arg_sr_instance_id);
1287 else
1288 v_start_date := arg_start_date;
1289 end if;
1290
1291 if arg_cutoff_date is null then
1292 v_cutoff_date := check_cutoff_date(arg_organization_id, arg_sr_instance_id);
1293 else
1294 v_cutoff_date := arg_cutoff_date;
1295 end if;
1296
1297 if arg_refresh_flag = 1 then
1298 -- process complete refresh
1299
1300 OPEN dept_res;
1301 LOOP
1302 Fetch dept_res into var_department_id,
1303 var_resource_id,
1304 var_24hr_flag,
1305 var_aggregate_resource_id,
1306 var_capacity_units,
1307 var_disable_date,
1308 v_calendar_code,
1309 v_calendar_exception_set_id;
1310
1311 EXIT WHEN dept_res%NOTFOUND;
1312
1313 calc_res_avail(
1314 arg_organization_id,
1315 arg_sr_instance_id,
1316 var_department_id,
1317 var_resource_id,
1318 arg_simulation_set,
1319 var_24hr_flag,
1320 v_start_date,
1321 v_cutoff_date,
1322 var_aggregate_resource_id,
1323 arg_refresh_number,
1324 var_capacity_units,
1325 var_disable_date);
1326 commit;
1327 SAVEPOINT SP1;
1328 END LOOP;
1329
1330 CLOSE dept_res;
1331
1332 else
1333 -- process all changed department resources
1334
1335 OPEN dept_res_change;
1336 LOOP
1337 Fetch dept_res_change into var_department_id,
1338 var_resource_id,
1339 var_24hr_flag,
1340 var_aggregate_resource_id,
1341 var_capacity_units,
1342 var_disable_date,
1343 v_calendar_code,
1344 v_calendar_exception_set_id;
1345
1346 EXIT WHEN dept_res_change%NOTFOUND;
1347
1348 calc_res_avail(
1349 arg_organization_id,
1350 arg_sr_instance_id,
1351 var_department_id,
1352 var_resource_id,
1353 arg_simulation_set,
1354 var_24hr_flag,
1355 v_start_date,
1356 v_cutoff_date,
1357 var_aggregate_resource_id,
1358 arg_refresh_number,
1359 var_capacity_units,
1360 var_disable_date);
1361 commit;
1362 SAVEPOINT SP1;
1363 END LOOP;
1364
1365 CLOSE dept_res_change;
1366
1367 end if;
1368
1369 -- retcode := 0;
1370 -- return;
1371
1372 if( v_show_warning is null) then
1373 v_show_warning := 0;
1374 end if;
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1379 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1380
1381 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1382 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1383
1384 IF dept_res%isopen THEN CLOSE dept_res; END IF;
1385 IF dept_res_change%isopen THEN CLOSE dept_res_change; END IF;
1386 -- retcode := 1;
1387 -- return;
1388 v_show_warning :=SQLCODE;
1389
1390 END populate_avail_resources;
1391
1392 --
1393 -- This procedulre populate all resources for an organization.
1394 --
1395
1396 PROCEDURE populate_org_resources( RETCODE OUT NOCOPY number,
1397 arg_refresh_flag IN number,
1398 arg_refresh_number IN number,
1399 arg_organization_id IN number,
1400 arg_sr_instance_id IN number,
1401 arg_start_date IN date,
1402 arg_cutoff_date IN date ) IS
1403
1404 CURSOR c_simulation_set IS
1405 SELECT simulation_set
1406 FROM msc_simulation_sets
1407 WHERE organization_id = arg_organization_id
1408 AND sr_instance_id = arg_sr_instance_id;
1409
1410 var_simulation_set VARCHAR2(10);
1411 var_return_status NUMBER;
1412
1413 BEGIN
1414
1415 LOG_MESSAGE('========================================================');
1416 LOG_MESSAGE('Populating Org Resources for the Org: '|| arg_organization_id);
1417 LOG_MESSAGE('========================================================');
1418
1419 MSC_UTIL.MSC_DEBUG('Creating resource for all simulation set ....');
1420 MSC_UTIL.MSC_DEBUG('Org Id:' || to_char(arg_organization_id));
1421 MSC_UTIL.MSC_DEBUG('Instance:' || to_char(arg_sr_instance_id));
1422
1423 -- For complete refresh, the collection program will handle deleting all
1424 -- resource avail.
1425 -- For net change, refresh_flag = 2, delete resourse availability of
1426 -- all department resources with the new refresh number.
1427
1428 if arg_refresh_flag = 2 then
1429 v_stmt := 100;
1430 delete from msc_net_resource_avail
1431 where rowid in (select res.rowid
1432 from msc_net_resource_avail res,
1433 msc_resource_changes chg,
1434 msc_department_resources dept
1435 where res.organization_id = arg_organization_id
1436 and res.sr_instance_id = arg_sr_instance_id
1437 and res.plan_id = -1
1438 and res.department_id = chg.department_id
1439 and res.resource_id = chg.resource_id
1440 and chg.sr_instance_id = arg_sr_instance_id
1441 and chg.refresh_number = arg_refresh_number
1442 and dept.department_id = chg.department_id
1443 and dept.resource_id = chg.resource_id
1444 and dept.line_flag <> 1
1445 and dept.plan_id = -1
1446 and dept.organization_id = arg_organization_id
1447 and dept.sr_instance_id = arg_sr_instance_id );
1448 end if;
1449
1450
1451 -- Populate resource without simulation set
1452
1453 var_simulation_set := NULL;
1454
1455 LOG_MESSAGE(' Populating Org Resources for Null Simulation Set ......');
1456
1457 populate_avail_resources (
1458 arg_refresh_number,
1459 arg_refresh_flag,
1460 var_simulation_set,
1461 arg_organization_id,
1462 arg_sr_instance_id,
1463 arg_start_date,
1464 arg_cutoff_date);
1465
1466
1467 -- Populate resource for each simulation set belong to the organization
1468
1469 OPEN c_simulation_set;
1470 LOOP
1471 Fetch c_simulation_set into var_simulation_set;
1472
1473 EXIT WHEN c_simulation_set%NOTFOUND;
1474
1475 LOG_MESSAGE(' Populating Org Resources for the Simulation Set :'||var_simulation_set);
1476
1477 populate_avail_resources (
1478 arg_refresh_number,
1479 arg_refresh_flag,
1480 var_simulation_set,
1481 arg_organization_id,
1482 arg_sr_instance_id,
1483 arg_start_date,
1484 arg_cutoff_date );
1485
1486 END LOOP;
1487
1488 CLOSE c_simulation_set;
1489 -- COMMIT;
1490
1491
1492
1493 if( v_show_warning is null or v_show_warning =0) then
1494 retcode := 0;
1495 else
1496 retcode:= v_show_warning;
1497 end if;
1498 return;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1503 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1504
1505 IF c_simulation_set%isopen THEN
1506 CLOSE c_simulation_set;
1507 END IF;
1508
1509 -- fix for 2393358 --
1510 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1511
1512 LOG_MESSAGE('========================================');
1513 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1514 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_ORG_RESOURCES');
1515 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
1516 LOG_MESSAGE(FND_MESSAGE.GET);
1517
1518 LOG_MESSAGE(SQLERRM);
1519
1520
1521 END IF;
1522 --retcode := 1;
1523 retcode :=SQLCODE;
1524 return;
1525
1526 END populate_org_resources;
1527
1528 --
1529 -- This procedure populate all resource information for
1530 -- all lines of an organization
1531 --
1532 PROCEDURE populate_all_lines (
1533 RETCODE OUT NOCOPY number,
1534 arg_refresh_flag IN number,
1535 arg_refresh_number IN number,
1536 arg_organization_id IN number,
1537 arg_sr_instance_id IN number,
1538 arg_start_date IN date,
1539 arg_cutoff_date IN date ) IS
1540
1541 var_line_id NUMBER;
1542 var_calendar_date DATE;
1543 var_start_time NUMBER;
1544 var_stop_time NUMBER;
1545 var_max_rate NUMBER;
1546 v_start_date DATE;
1547 v_cutoff_date DATE;
1548 var_transaction_id NUMBER;
1549
1550 BEGIN
1551
1552 LOG_MESSAGE('======================================================================');
1553 LOG_MESSAGE(' Populating Resources of all lines for the Org: '||arg_organization_id);
1554 LOG_MESSAGE('======================================================================');
1555
1556 -- Determine the start date and cutoff date
1557 if arg_start_date is null then
1558 v_start_date := check_start_date(arg_organization_id, arg_sr_instance_id);
1559 else
1560 v_start_date := arg_start_date;
1561 end if;
1562
1563 if arg_cutoff_date is null then
1564 v_cutoff_date := check_cutoff_date(arg_organization_id, arg_sr_instance_id);
1565 else
1566 v_cutoff_date := arg_cutoff_date;
1567 end if;
1568
1569 MSC_UTIL.MSC_DEBUG('Creating resource for all lines....');
1570 MSC_UTIL.MSC_DEBUG('Org Id:' || to_char(arg_organization_id));
1571 MSC_UTIL.MSC_DEBUG('Instance:' || to_char(arg_sr_instance_id));
1572 MSC_UTIL.MSC_DEBUG('Start Date:' || to_char(v_start_date,'YYYY/MM/DD HH24:MI:SS'));
1573 MSC_UTIL.MSC_DEBUG('Cutoff Date:' || to_char(v_cutoff_date,'YYYY/MM/DD HH24:MI:SS'));
1574
1575 -- For complete refresh, the collection program will handle deleting all
1576 -- resource avail.
1577 -- For net change, refresh_flag = 2, delete resourse availability of
1578 -- lines with the new refresh number.
1579 if arg_refresh_flag = 2 then
1580 v_stmt := 110;
1581 delete from msc_net_resource_avail
1582 where rowid in (select res.rowid
1583 from msc_net_resource_avail res, msc_department_resources line
1584 where res.organization_id = line.organization_id
1585 and res.sr_instance_id = line.sr_instance_id
1586 and res.department_id = line.department_id
1587 and res.resource_id = -1
1588 and line.line_flag = 1
1589 and line.plan_id = -1
1590 and line.refresh_number = arg_refresh_number
1591 and line.organization_id = arg_organization_id
1592 and line.sr_instance_id = arg_sr_instance_id ) ;
1593 end if;
1594
1595 /* 2201418 - Added hints to improve performance. Also defined a new index
1596 on msc_department_resources (line_flag, plan_id, sr_instance_id,
1597 organization_id) */
1598
1599 INSERT into MSC_net_resource_avail(
1600 transaction_id,
1601 plan_id,
1602 organization_id,
1603 sr_instance_id,
1604 department_id,
1605 resource_id,
1606 shift_date,
1607 from_time,
1608 to_time,
1609 capacity_units,
1610 status,
1611 applied,
1612 updated,
1613 last_update_date,
1614 last_updated_by,
1615 creation_date,
1616 created_by,
1617 last_update_login,
1618 request_id,
1619 program_application_id,
1620 program_id,
1621 program_update_date,
1622 refresh_number)
1623 SELECT /*+ leading(line) INDEX(LINE) use_nl(dates) */
1624 msc_net_resource_avail_s.NEXTVAL
1625 ,-1
1626 ,arg_organization_id
1627 ,arg_sr_instance_id
1628 ,line.department_id
1629 ,-1
1630 ,dates.calendar_date
1631 ,line.start_time
1632 ,line.stop_time
1633 ,line.max_rate
1634 ,NULL /*STATUS*/
1635 ,NULL /*APPLIED*/
1636 ,2 /*UPDATED*/
1637 ,SYSDATE
1638 ,FND_GLOBAL.USER_ID
1639 ,SYSDATE
1640 ,FND_GLOBAL.USER_ID
1641 ,FND_GLOBAL.LOGIN_ID
1642 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
1643 ,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
1644 ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1645 ,SYSDATE /* PROGRAM_UPDATE_DATE */
1646 ,arg_refresh_number
1647 FROM msc_calendar_dates dates,
1648 msc_department_resources line,
1649 msc_trading_partners org
1650 WHERE line.organization_id = arg_organization_id
1651 AND line.sr_instance_id = arg_sr_instance_id
1652 AND line.line_flag = 1
1653 AND line.plan_id = -1
1654 AND line.refresh_number = arg_refresh_number
1655 AND NVL(line.disable_date, sysdate+1) > sysdate
1656 AND org.sr_tp_id = line.organization_id
1657 AND org.sr_instance_id = line.sr_instance_id
1658 AND org.partner_type = 3
1659 AND dates.calendar_code = org.calendar_code
1660 AND dates.sr_instance_id = arg_sr_instance_id
1661 AND dates.exception_set_id = org.calendar_exception_set_id
1662 AND dates.calendar_date >= trunc(v_start_date)
1663 AND dates.calendar_date <= least(trunc(v_cutoff_date),
1664 trunc(nvl(line.disable_date-1, v_cutoff_date)) )
1665 AND dates.seq_num is not null;
1666
1667 INSERT into MSC_net_resource_avail(
1668 transaction_id,
1669 plan_id,
1670 organization_id,
1671 sr_instance_id,
1672 department_id,
1673 resource_id,
1674 shift_date,
1675 from_time,
1676 to_time,
1677 capacity_units,
1678 status,
1679 applied,
1680 updated,
1681 last_update_date,
1682 last_updated_by,
1683 creation_date,
1684 created_by,
1685 last_update_login,
1686 request_id,
1687 program_application_id,
1688 program_id,
1689 program_update_date,
1690 refresh_number)
1691 SELECT /*+ leading(line) INDEX(LINE) use_nl(dates) */
1692 msc_net_resource_avail_s.NEXTVAL
1693 ,-1
1694 ,arg_organization_id
1695 ,arg_sr_instance_id
1696 ,line.department_id
1697 ,-1
1698 ,line.disable_date
1699 ,0
1700 ,0
1701 ,0
1702 ,NULL /*STATUS*/
1703 ,NULL /*APPLIED*/
1704 ,2 /*UPDATED*/
1705 ,SYSDATE
1706 ,FND_GLOBAL.USER_ID
1707 ,SYSDATE
1708 ,FND_GLOBAL.USER_ID
1709 ,FND_GLOBAL.LOGIN_ID
1710 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
1711 ,FND_GLOBAL.PROG_APPL_ID /*PROGRAM_APPLICATION_ID */
1712 ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1713 ,SYSDATE /* PROGRAM_UPDATE_DATE */
1714 ,arg_refresh_number
1715 FROM
1716 msc_department_resources line,
1717 msc_trading_partners org
1718 WHERE line.organization_id = arg_organization_id
1719 AND line.sr_instance_id = arg_sr_instance_id
1720 AND line.line_flag = 1
1721 AND line.plan_id = -1
1722 AND line.refresh_number = arg_refresh_number
1723 AND line.disable_date IS NOT NULL
1724 AND org.sr_tp_id = line.organization_id
1725 AND org.sr_instance_id = line.sr_instance_id
1726 AND org.partner_type = 3;
1727
1728 --COMMIT;
1729 retcode := 0;
1730 return;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 -- dbms_output.put_line('exception: ' || to_char(v_stmt) || ' - ' ||
1735 -- to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1736 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
1737 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
1738
1739 -- fix for 2393358 --
1740 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1741
1742 LOG_MESSAGE('========================================');
1743 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1744 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_ALL_LINES');
1745 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
1746 LOG_MESSAGE(FND_MESSAGE.GET);
1747
1748 LOG_MESSAGE(SQLERRM);
1749
1750
1751 END IF;
1752
1753 --retcode := 1;
1754 retcode :=SQLCODE;
1755 return;
1756
1757 END populate_all_lines;
1758 PROCEDURE COMPUTE_RES_AVAIL (ERRBUF OUT NOCOPY VARCHAR2,
1759 RETCODE OUT NOCOPY NUMBER,
1760 pINSTANCE_ID IN NUMBER,
1761 pSTART_DATE IN VARCHAR2)
1762 IS
1763 lv_start_date DATE := TO_DATE(pSTART_DATE, 'YYYY/MM/DD HH24:MI:SS');
1764 lv_retval BOOLEAN;
1765 lv_dummy1 VARCHAR2(32);
1766 lv_dummy2 VARCHAR2(32);
1767 lv_ret_res_ava number;
1768 lv_where_clause varchar2(500) := NULL;
1769
1770 BEGIN
1771
1772 lv_retval := FND_INSTALLATION.GET_APP_INFO(
1773 'FND', lv_dummy1,lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
1774 /* initialize the variables */
1775 SELECT
1776 APPS_VER,
1777 SYSDATE,
1778 SYSDATE,
1779 FND_GLOBAL.USER_ID,
1780 SYSDATE,
1781 FND_GLOBAL.USER_ID,
1782 UPPER(INSTANCE_CODE), /* Bug 2129155 */
1783 INSTANCE_TYPE, -- OPM
1784 nvl(LCID,0)
1785 INTO
1786 MSC_CL_COLLECTION.v_apps_ver,
1787 MSC_CL_COLLECTION.START_TIME,
1788 MSC_CL_COLLECTION.v_current_date,
1789 MSC_CL_COLLECTION.v_current_user,
1790 MSC_CL_COLLECTION.v_current_date,
1791 MSC_CL_COLLECTION.v_current_user,
1792 MSC_CL_COLLECTION.v_instance_code,
1793 MSC_CL_COLLECTION.v_instance_type, -- OPM
1794 MSC_CL_COLLECTION.v_last_collection_id
1795 FROM MSC_APPS_INSTANCES
1796 WHERE INSTANCE_ID= pINSTANCE_ID;
1797
1798 MSC_CL_COLLECTION.v_is_complete_refresh := TRUE;
1799 MSC_CL_COLLECTION.v_is_incremental_refresh := FALSE;
1800 MSC_CL_COLLECTION.v_is_partial_refresh := FALSE;
1801
1802 MSC_CL_COLLECTION.v_instance_id := pINSTANCE_ID;
1803
1804 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Start date : '|| lv_start_date);
1805
1806 lv_where_clause := ' AND ORGANIZATION_ID IN ( SELECT SR_TP_ID FROM MSC_TRADING_PARTNERS WHERE '||
1807 ' SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id ||
1808 ' AND ORGANIZATION_TYPE =1 ) ';
1809
1810 -- log_debug('before delete of MSC_NET_RESOURCE_AVAIL debug0 ');
1811 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
1812 -- log_debug('before delete of MSC_NET_RESOURCE_AVAIL ');
1813 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
1814 COMMIT;
1815 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1);
1816 COMMIT;
1817
1818 /* call the function to calc. resource avail */
1819 lv_ret_res_ava:=CALC_RESOURCE_AVAILABILITY(lv_start_date-1,MSC_UTIL.G_ALL_ORGANIZATIONS,TRUE);
1820
1821
1822 IF lv_ret_res_ava = 2 THEN
1823 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1824 ERRBUF:= FND_MESSAGE.GET;
1825 RETCODE:= MSC_UTIL.G_WARNING;
1826 ELSIF lv_ret_res_ava <> 0 THEN
1827 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1828 ERRBUF:= FND_MESSAGE.GET;
1829 RETCODE:= MSC_UTIL.G_ERROR;
1830
1831 END IF;
1832
1833 ELSIF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_MIXED THEN
1834 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-07');
1835 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
1836 COMMIT;
1837 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', MSC_CL_COLLECTION.v_instance_id, -1,lv_where_clause);
1838 COMMIT;
1839
1840
1841 lv_ret_res_ava:=CALC_RESOURCE_AVAILABILITY(lv_start_date-1,MSC_UTIL.G_ALL_ORGANIZATIONS,TRUE);
1842
1843
1844 IF lv_ret_res_ava = 2 THEN
1845 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1846 ERRBUF:= FND_MESSAGE.GET;
1847 RETCODE:= MSC_UTIL.G_WARNING;
1848 ELSIF lv_ret_res_ava <> 0 THEN
1849 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1850
1851 ERRBUF:= FND_MESSAGE.GET;
1852 RETCODE:= MSC_UTIL.G_ERROR;
1853 END IF;
1854 ELSE
1855 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This program can be run only for Instance Type: Discrete.');
1856 ERRBUF:= FND_MESSAGE.GET;
1857 RETCODE:= MSC_UTIL.G_ERROR;
1858
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 ROLLBACK;
1864 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
1865 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1866 RETCODE := MSC_UTIL.G_ERROR;
1867
1868 END COMPUTE_RES_AVAIL;
1869
1870 --==============================================================
1871
1872 /*Resource start time changes*/
1873 FUNCTION CALC_RESOURCE_AVAILABILITY (pSTART_TIME IN DATE,
1874 pORG_GROUP IN VARCHAR2,
1875 pSTANDALONE BOOLEAN)
1876 RETURN NUMBER IS
1877
1878 lv_ret_code NUMBER;
1879 lv_refresh_flag NUMBER;
1880 lv_temp_ret_flag NUMBER;
1881 /*Resource Start TIme*/
1882 CURR_DATE DATE;
1883
1884 lv_task_start_time DATE;
1885 lv_task_end_time DATE;
1886
1887 lv_mrp_cutoff_date_offset NUMBER; -- Months
1888 ex_calc_res_avail EXCEPTION; -- fix for 2393358
1889 lv_res_avail_before_sysdate NUMBER; -- Days
1890
1891 CURSOR c1 IS
1892 SELECT tp.Organization_ID
1893 FROM MSC_PARAMETERS tp,
1894 MSC_INSTANCE_ORGS ins_org,
1895 MSC_TRADING_PARTNERS mtp
1896 WHERE tp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1897 AND ins_org.SR_INSTANCE_ID=tp.SR_INSTANCE_ID
1898 AND ins_org.Organization_ID=tp.ORGANIZATION_ID
1899 AND ins_org.ENABLED_FLAG= MSC_UTIL.SYS_YES
1900 AND ((pORG_GROUP = MSC_UTIL.G_ALL_ORGANIZATIONS ) OR (ins_org.ORG_GROUP=pORG_GROUP))
1901 AND mtp.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1902 AND mtp.sr_tp_id = tp.organization_id
1903 AND mtp.partner_type = 3
1904 AND mtp.organization_type = 1; -- Discrete Mfg.
1905
1906
1907 /************** LEGACY_CHANGE_START*************************/
1908
1909 CURSOR c2 IS
1910 SELECT 1
1911 FROM MSC_ST_RESOURCE_CHANGES
1912 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1913 AND process_flag = 5;
1914
1915 lv_changes_exists NUMBER := 0;
1916 /*****************LEGACY_CHANGE_ENDS************************/
1917
1918 BEGIN
1919 /* Resource Start Time changes*/
1920 lv_task_start_time:= pSTART_TIME;
1921 CURR_DATE:= SYSDATE;
1922
1923 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1924 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CALC_RESOURCE_AVAILABILITY');
1925 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1926
1927 lv_mrp_cutoff_date_offset:= TO_NUMBER(FND_PROFILE.VAlUE('MRP_CUTOFF_DATE_OFFSET'));
1928 lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
1929
1930 IF pSTANDALONE THEN
1931 lv_task_end_time := ADD_MONTHS(lv_task_start_time,lv_mrp_cutoff_date_offset);
1932 ELSE
1933 lv_task_end_time := ADD_MONTHS(lv_task_start_time,lv_mrp_cutoff_date_offset) + lv_res_avail_before_sysdate;
1934 END IF;
1935
1936 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_mrp_cutoff_date_offset:'||lv_mrp_cutoff_date_offset);
1937 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_res_avail_before_sysdate:'||lv_res_avail_before_sysdate);
1938 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_task_end_time:'||lv_task_end_time);
1939 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_task_start_time:' ||lv_task_start_time);
1940 --- PREPLACE CHANGE START ---
1941 /*
1942 IF v_is_complete_refresh THEN
1943 lv_refresh_flag:= 1;
1944 ELSE
1945 lv_refresh_flag:= 2;
1946 END IF;
1947 */
1948
1949 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1950 lv_refresh_flag := 1;
1951 ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1952 lv_refresh_flag := 2;
1953 ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1954 lv_refresh_flag := 1; -- Functionality is same as complete_refresh
1955 END IF;
1956
1957 /************** LEGACY_CHANGE_START*************************/
1958 -- Calling the program as complete refresh for legacy so that new
1959 -- records coming in are considered
1960
1961 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1962 lv_refresh_flag := 1;
1963 END IF;
1964 /*****************LEGACY_CHANGE_ENDS************************/
1965
1966 --- PREPLACE CHANGE END ---
1967
1968 -- USING DEFALUT VALUE FOR START DATE AND CUTOFF DATE
1969
1970 SAVEPOINT SP1;
1971
1972 FOR c_rec IN c1 LOOP
1973
1974 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RESOURCES');
1975
1976 POPULATE_ORG_RESOURCES
1977 ( lv_ret_code,
1978 lv_refresh_flag,
1979 MSC_CL_COLLECTION.v_last_collection_id,
1980 c_rec.organization_id,
1981 MSC_CL_COLLECTION.v_instance_id,
1982 lv_task_start_time,
1983 lv_task_end_time);
1984
1985 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
1986
1987 IF lv_ret_code <> 0 THEN
1988 ROLLBACK WORK TO SAVEPOINT SP1;
1989 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
1990 lv_temp_ret_flag:=1;
1991 RAISE ex_calc_res_avail;
1992 else
1993 lv_temp_ret_flag:=2;
1994 END IF;
1995 ELSE
1996 COMMIT;
1997 SAVEPOINT SP1;
1998 END IF;
1999
2000 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ALL_LINES');
2001
2002 POPULATE_ALL_LINES
2003 ( lv_ret_code,
2004 lv_refresh_flag,
2005 MSC_CL_COLLECTION.v_last_collection_id,
2006 c_rec.organization_id,
2007 MSC_CL_COLLECTION.v_instance_id,
2008 lv_task_start_time,
2009 lv_task_end_time);
2010
2011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2012
2013 IF lv_ret_code <> 0 THEN
2014 ROLLBACK WORK TO SAVEPOINT SP1;
2015 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2016 lv_temp_ret_flag:=1;
2017 RAISE ex_calc_res_avail;
2018 else
2019 lv_temp_ret_flag:=2;
2020 END IF;
2021 ELSE
2022 COMMIT;
2023 SAVEPOINT SP1;
2024 END IF;
2025
2026 /* yvon: resource instanc eavail changes start */
2027 -----------------------------------------------------
2028 -- populate resource instance availability
2029 -----------------------------------------------------
2030 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RES_INSTANCES');
2031
2032 MSC_NET_RES_INST_AVAILABILITY.POPULATE_ORG_RES_INSTANCES
2033 ( lv_ret_code,
2034 lv_refresh_flag,
2035 MSC_CL_COLLECTION.v_last_collection_id,
2036 c_rec.organization_id,
2037 MSC_CL_COLLECTION.v_instance_id,
2038 lv_task_start_time,
2039 lv_task_end_time);
2040
2041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2042
2043 IF lv_ret_code <> 0 THEN
2044 ROLLBACK WORK TO SAVEPOINT SP1;
2045 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2046 lv_temp_ret_flag:=1;
2047 RAISE ex_calc_res_avail;
2048 else
2049 lv_temp_ret_flag:=2;
2050 END IF;
2051 ELSE
2052 COMMIT;
2053 SAVEPOINT SP1;
2054 END IF;
2055 /* yvon: resource instanc eavail changes end */
2056
2057 END LOOP;
2058
2059 /************** LEGACY_CHANGE_START*************************/
2060
2061 -- This is to enable resource changes to be
2062 -- considered for legacy. Both, the resource information and
2063 -- resource changes may come in at the same time for legacy.
2064
2065 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2066
2067 OPEN C2;
2068 FETCH C2 INTO lv_changes_exists;
2069 CLOSE C2;
2070
2071 IF lv_changes_exists = 1 THEN
2072
2073 lv_refresh_flag := 2;
2074
2075 -- USING DEFALUT VALUE FOR START DATE AND CUTOFF DATE
2076
2077 SAVEPOINT SP1;
2078
2079 FOR c_rec IN c1 LOOP
2080
2081 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RESOURCES');
2082
2083 POPULATE_ORG_RESOURCES
2084 ( lv_ret_code,
2085 lv_refresh_flag,
2086 MSC_CL_COLLECTION.v_last_collection_id,
2087 c_rec.organization_id,
2088 MSC_CL_COLLECTION.v_instance_id,
2089 lv_task_start_time,
2090 lv_task_end_time);
2091
2092 IF lv_ret_code <> 0 THEN
2093 ROLLBACK WORK TO SAVEPOINT SP1;
2094 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2095 lv_temp_ret_flag:=1;
2096 RAISE ex_calc_res_avail;
2097 else
2098 lv_temp_ret_flag:=2;
2099 END IF;
2100 ELSE
2101 COMMIT;
2102 SAVEPOINT SP1;
2103 END IF;
2104
2105 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ALL_LINES');
2106
2107 POPULATE_ALL_LINES
2108 ( lv_ret_code,
2109 lv_refresh_flag,
2110 MSC_CL_COLLECTION.v_last_collection_id,
2111 c_rec.organization_id,
2112 MSC_CL_COLLECTION.v_instance_id,
2113 lv_task_start_time,
2114 lv_task_end_time);
2115
2116 IF lv_ret_code <> 0 THEN
2117 ROLLBACK WORK TO SAVEPOINT SP1;
2118 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2119 lv_temp_ret_flag:=1;
2120 RAISE ex_calc_res_avail;
2121 else
2122 lv_temp_ret_flag:=2;
2123 END IF;
2124 ELSE
2125 COMMIT;
2126 SAVEPOINT SP1;
2127 END IF;
2128
2129 /* yvon: resource instanc eavail changes start */
2130 -----------------------------------------------------
2131 -- populate resource instance availability
2132 -----------------------------------------------------
2133 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CALLING POPULATE_ORG_RES_INSTANCES');
2134
2135 MSC_NET_RES_INST_AVAILABILITY.POPULATE_ORG_RES_INSTANCES
2136 ( lv_ret_code,
2137 lv_refresh_flag,
2138 MSC_CL_COLLECTION.v_last_collection_id,
2139 c_rec.organization_id,
2140 MSC_CL_COLLECTION.v_instance_id,
2141 lv_task_start_time,
2142 lv_task_end_time);
2143
2144 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'RET_CODE: ' ||lv_ret_code);
2145
2146 IF lv_ret_code <> 0 THEN
2147 ROLLBACK WORK TO SAVEPOINT SP1;
2148 IF lv_ret_code IN (-01653,-01650,-01562,-01683) THEN
2149 lv_temp_ret_flag:=1;
2150 RAISE ex_calc_res_avail;
2151 else
2152 lv_temp_ret_flag:=2;
2153 END IF;
2154 ELSE
2155 COMMIT;
2156 SAVEPOINT SP1;
2157 END IF;
2158 /* yvon: resource instanc eavail changes end */
2159
2160 END LOOP;
2161 END IF; -- lv_changes_exists
2162 END IF; -- MSC_UTIL.G_INS_OTHER
2163
2164 /************** LEGACY_CHANGE_ENDS*************************/
2165
2166 /* Bug 3295824 - We need to set the capacity units to 0 of any records
2167 having -ve capacity units */
2168
2169 update MSC_net_resource_avail
2170 set capacity_units = 0
2171 where capacity_units < 0
2172 and plan_id = -1
2173 AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2174 AND simulation_set is not null
2175 and shift_date between trunc(lv_task_start_time) and
2176 lv_task_end_time;
2177
2178 COMMIT;
2179
2180 /* End Bug 3295824 */
2181
2182 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
2183 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
2184 TO_CHAR(CEIL((SYSDATE- CURR_DATE)*14400.0)/10));
2185 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2186
2187
2188
2189
2190 IF (lv_temp_ret_flag=2 ) THEN
2191 return lv_temp_ret_flag;
2192 else
2193 RETURN 0;
2194 END IF;
2195
2196 EXCEPTION
2197
2198 WHEN OTHERS THEN
2199
2200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2201
2202 IF (lv_temp_ret_flag=1 ) THEN
2203 return lv_temp_ret_flag;
2204 else
2205 RETURN SQLCODE;
2206 END IF;
2207
2208 END CALC_RESOURCE_AVAILABILITY;
2209
2210
2211 --==================================================================
2212
2213 --=======================================================================
2214 --The following procedure also include the DS change for the
2215 --MSC_NET_RES_INST_AVAIL
2216 --=======================================================================
2217 PROCEDURE LOAD_NET_RESOURCE_AVAIL IS
2218
2219 -- OPM
2220 -- cursor to select the rows from net_resource_avail. process mfg only
2221 CURSOR c11 (org_id NUMBER) IS
2222 SELECT
2223 msnra.organization_id,
2224 msnra.sr_instance_id,
2225 msnra.resource_id,
2226 msnra.department_id,
2227 msnra.simulation_set,
2228 msnra.shift_num,
2229 msnra.shift_date,
2230 msnra.from_time,
2231 msnra.to_time,
2232 msnra.capacity_units
2233 FROM msc_st_net_resource_avail msnra
2234 WHERE msnra.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
2235 msnra.organization_id=org_id;
2236
2237
2238 ---------------------------------------------------------------------
2239 -- adding the change for the msc_st_net_res_avail for DS
2240 ---------------------------------------------------------------------
2241
2242 -- OPM
2243 -- cursor to select the rows from net_res_inst_avail. process mfg only
2244 CURSOR c_res_inst (org_id NUMBER) IS
2245 SELECT
2246 msnria.sr_instance_id,
2247 msnria.res_instance_id,
2248 msnria.resource_id,
2249 msnria.department_id,
2250 msnria.organization_id,
2251 msnria.serial_number,
2252 t1.inventory_item_id equipment_item_id,
2253 msnria.simulation_set,
2254 msnria.shift_num,
2255 msnria.shift_date,
2256 msnria.from_time,
2257 msnria.to_time
2258 FROM msc_st_net_res_inst_avail msnria,
2259 MSC_ITEM_ID_LID t1
2260 WHERE msnria.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2261 and t1.sr_instance_id (+) = msnria.sr_instance_id
2262 and t1.sr_inventory_item_id (+) = msnria.equipment_item_id
2263 and msnria.organization_id=org_id;
2264
2265 CURSOR c_org_list IS
2266 select organization_id
2267 from msc_instance_orgs mio,
2268 msc_trading_partners mtp
2269 where mio.sr_instance_id= MSC_CL_COLLECTION.v_instance_id and
2270 mio.enabled_flag= 1 and
2271 ((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
2272 mio.sr_instance_id=mtp.sr_instance_id and
2273 mio.organization_id=mtp.sr_tp_id and
2274 mtp.partner_type=3 and
2275 mtp.organization_type=2;
2276
2277 c_count NUMBER:= 0;
2278 lv_res_avail NUMBER := MSC_UTIL.SYS_NO;
2279 lv_res_inst_avail NUMBER := MSC_UTIL.SYS_NO;
2280 lv_sql_stmt VARCHAR2(2048);
2281
2282 BEGIN
2283
2284 IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
2285
2286 /*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
2287 -- We want to delete all NRA related data and get new stuff.
2288
2289 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
2290 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
2291
2292 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS THEN
2293 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-00');
2294 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
2295 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
2296 ELSE
2297 v_sub_str :=' AND ORGANIZATION_ID '||v_in_org_str;
2298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-01');
2299 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,v_sub_str);
2300 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,v_sub_str);
2301 END IF;
2302
2303 END IF;*/
2304
2305 -- process mfg only. move the rows for the st table to the msc table
2306 -- for net resource avail
2307 IF MSC_CL_COLLECTION.v_process_flag = MSC_UTIL.SYS_YES THEN
2308
2309 /*
2310 We will do a bulk insert of res avail for OPM orgs. If this fails,
2311 then we will switch to old, row by row processing.
2312
2313 The same applies to collection of net res instance avail data as well.
2314 */
2315
2316 FOR c_rec1 IN c_org_list LOOP
2317 BEGIN
2318
2319 SAVEPOINT LOAD_RES_AVAIL_SP;
2320
2321 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading res avail for OPM orgs : ' || c_rec1.organization_id);
2322
2323 lv_sql_stmt:=
2324 ' INSERT into MSC_net_resource_avail '
2325 ||' ( transaction_id,'
2326 ||' plan_id,'
2327 ||' department_id,'
2328 ||' resource_id,'
2329 ||' organization_id,'
2330 ||' sr_instance_id,'
2331 ||' shift_num,'
2332 ||' shift_date,'
2333 ||' from_time,'
2334 ||' to_time,'
2335 ||' capacity_units,'
2336 ||' simulation_set,'
2337 ||' status,'
2338 ||' applied,'
2339 ||' updated,'
2340 ||' last_update_date,'
2341 ||' last_updated_by,'
2342 ||' creation_date,'
2343 ||' created_by,'
2344 ||' refresh_number)'
2345 ||' SELECT'
2346 ||' msc_net_resource_avail_s.NEXTVAL,'
2347 ||' -1,'
2348 ||' msnra.department_id,'
2349 ||' msnra.resource_id,'
2350 ||' msnra.organization_id,'
2351 ||' msnra.sr_instance_id,'
2352 ||' msnra.shift_num,'
2353 ||' msnra.shift_date,'
2354 ||' msnra.from_time,'
2355 ||' msnra.to_time,'
2356 ||' msnra.capacity_units,'
2357 ||' msnra.simulation_set,'
2358 ||' NULL,' /* STATUS */
2359 ||' NULL,' /* APPLIED */
2360 ||' 2,' /* UPDATED */
2361 ||' :v_current_date,'
2362 ||' :v_current_user,'
2363 ||' :v_current_date,'
2364 ||' :v_current_user,'
2365 ||' :v_last_collection_id'
2366 ||' FROM msc_st_net_resource_avail msnra'
2367 ||' WHERE msnra.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2368 ||' AND msnra.organization_id = ' ||c_rec1.organization_id;
2369
2370 EXECUTE IMMEDIATE lv_sql_stmt
2371 USING
2372 MSC_CL_COLLECTION.v_current_date,
2373 MSC_CL_COLLECTION.v_current_user,
2374 MSC_CL_COLLECTION.v_current_date,
2375 MSC_CL_COLLECTION.v_current_user,
2376 MSC_CL_COLLECTION.v_last_collection_id;
2377
2378 COMMIT;
2379
2380 lv_res_avail:=MSC_UTIL.SYS_YES;
2381
2382 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loaded res avail for OPM orgs : ' || c_rec1.organization_id);
2383
2384 EXCEPTION
2385 WHEN OTHERS THEN
2386
2387 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_NET_RESOURCE_AVAIL>>');
2388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2389 ROLLBACK WORK TO SAVEPOINT LOAD_RES_AVAIL_SP;
2390 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing for org : ' || c_rec1.organization_id);
2391 END;
2392
2393 IF lv_res_avail = MSC_UTIL.SYS_NO THEN
2394 c_count:= 0;
2395
2396 FOR c_rec IN c11(c_rec1.organization_id) LOOP
2397
2398 BEGIN
2399 INSERT into MSC_net_resource_avail(
2400 transaction_id,
2401 plan_id,
2402 department_id,
2403 resource_id,
2404 organization_id,
2405 sr_instance_id,
2406 shift_num,
2407 shift_date,
2408 from_time,
2409 to_time,
2410 capacity_units,
2411 simulation_set,
2412 status,
2413 applied,
2414 updated,
2415 last_update_date,
2416 last_updated_by,
2417 creation_date,
2418 created_by,
2419 refresh_number)
2420 VALUES(
2421 msc_net_resource_avail_s.NEXTVAL,
2422 -1,
2423 c_rec.department_id,
2424 c_rec.resource_id,
2425 c_rec.organization_id,
2426 c_rec.sr_instance_id,
2427 c_rec.shift_num,
2428 c_rec.shift_date,
2429 c_rec.from_time,
2430 c_rec.to_time,
2431 c_rec.capacity_units,
2432 c_rec.simulation_set,
2433 NULL, /* STATUS */
2434 NULL, /* APPLIED */
2435 2, /* UPDATED */
2436 MSC_CL_COLLECTION.v_current_date,
2437 MSC_CL_COLLECTION.v_current_user,
2438 MSC_CL_COLLECTION.v_current_date,
2439 MSC_CL_COLLECTION.v_current_user,
2440 MSC_CL_COLLECTION.v_last_collection_id);
2441
2442 c_count:= c_count+1;
2443
2444 IF c_count> MSC_CL_COLLECTION.PBS THEN
2445 COMMIT;
2446 c_count:= 0;
2447 END IF;
2448
2449 EXCEPTION
2450 WHEN OTHERS THEN
2451
2452 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2453
2454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2455 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2456 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2457 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
2458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2459
2460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2461 RAISE;
2462
2463 ELSE
2464
2465 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2466
2467 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2468 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2469 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2470 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RESOURCE_AVAIL');
2471 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2472
2473 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2474 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2475 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
2476 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2477
2478 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2479 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2480 FND_MESSAGE.SET_TOKEN('VALUE',
2481 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2482 MSC_CL_COLLECTION.v_instance_id));
2483 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2484
2485 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2486 END IF;
2487
2488 END;
2489
2490 END LOOP;
2491
2492 END IF; /* If lv_res_avail:=MSC_CL_COLLECTION.SYS_NO */
2493 END LOOP;
2494
2495 ------------------------------------------------------------------------
2496 -- here is the change for msc_net_res_inst_avail (DS change)
2497 ------------------------------------------------------------------------
2498 FOR c_rec1 IN c_org_list LOOP
2499 BEGIN
2500
2501 SAVEPOINT LOAD_RES_INST_AVAIL_SP;
2502
2503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading res instance avail for OPM orgs : ' || c_rec1.organization_id);
2504
2505 lv_sql_stmt:=
2506 ' INSERT into MSC_net_res_inst_avail '
2507 ||' ( inst_transaction_id,'
2508 ||' plan_id,'
2509 ||' sr_instance_id,'
2510 ||' organization_id,'
2511 ||' department_id,'
2512 ||' resource_id,'
2513 ||' res_instance_id,'
2514 ||' equipment_item_id,'
2515 ||' parent_id,'
2516 ||' serial_number,'
2517 ||' simulation_set,'
2518 ||' shift_num,'
2519 ||' shift_date,'
2520 ||' from_time,'
2521 ||' to_time,'
2522 ||' status,'
2523 ||' applied,'
2524 ||' updated,'
2525 ||' last_update_date,'
2526 ||' last_updated_by,'
2527 ||' creation_date,'
2528 ||' created_by,'
2529 ||' refresh_number)'
2530 ||' SELECT'
2531 ||' msc_net_res_inst_avail_s.NEXTVAL,'
2532 ||' -1,'
2533 ||' msnria.sr_instance_id,'
2534 ||' msnria.organization_id,'
2535 ||' msnria.department_id,'
2536 ||' msnria.resource_id,'
2537 ||' msnria.res_instance_id,'
2538 ||' t1.inventory_item_id,'
2539 ||' NULL,'
2540 ||' msnria.serial_number,'
2541 ||' msnria.simulation_set,'
2542 ||' msnria.shift_num,'
2543 ||' msnria.shift_date,'
2544 ||' msnria.from_time,'
2545 ||' msnria.to_time,'
2546 ||' NULL,' /* STATUS */
2547 ||' NULL,' /* APPLIED */
2548 ||' 2,' /* UPDATED */
2549 ||' :v_current_date,'
2550 ||' :v_current_user,'
2551 ||' :v_current_date,'
2552 ||' :v_current_user,'
2553 ||' :v_last_collection_id'
2554 ||' FROM msc_st_net_res_inst_avail msnria,'
2555 ||' MSC_ITEM_ID_LID t1'
2556 ||' WHERE msnria.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2557 ||' and t1.sr_instance_id (+) = msnria.sr_instance_id'
2558 ||' and t1.sr_inventory_item_id (+) = msnria.equipment_item_id'
2559 ||' and msnria.organization_id = ' ||c_rec1.organization_id;
2560
2561 EXECUTE IMMEDIATE lv_sql_stmt
2562 USING
2563 MSC_CL_COLLECTION.v_current_date,
2564 MSC_CL_COLLECTION.v_current_user,
2565 MSC_CL_COLLECTION.v_current_date,
2566 MSC_CL_COLLECTION.v_current_user,
2567 MSC_CL_COLLECTION.v_last_collection_id;
2568
2569 COMMIT;
2570
2571 lv_res_inst_avail:=MSC_UTIL.SYS_YES;
2572
2573 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loaded res instance avail for OPM orgs : ' || c_rec1.organization_id);
2574
2575 EXCEPTION
2576 WHEN OTHERS THEN
2577
2578 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_NET_RESOURCE_INSTANCE_AVAIL>>');
2579 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2580 ROLLBACK WORK TO SAVEPOINT LOAD_RES_INST_AVAIL_SP;
2581 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing for org ' || c_rec1.organization_id);
2582 END;
2583
2584 IF lv_res_inst_avail = MSC_UTIL.SYS_NO THEN
2585
2586 c_count:= 0;
2587
2588 FOR c_rec_resinst IN c_res_inst(c_rec1.organization_id) LOOP
2589
2590 BEGIN
2591
2592 INSERT into MSC_net_res_inst_avail(
2593 inst_transaction_id,
2594 plan_id,
2595 sr_instance_id,
2596 organization_id,
2597 department_id,
2598 resource_id,
2599 res_instance_id,
2600 equipment_item_id,
2601 parent_id,
2602 serial_number,
2603 simulation_set,
2604 shift_num,
2605 shift_date,
2606 from_time,
2607 to_time,
2608 status,
2609 applied,
2610 updated,
2611 last_update_date,
2612 last_updated_by,
2613 creation_date,
2614 created_by,
2615 refresh_number)
2616 VALUES(
2617 msc_net_res_inst_avail_s.NEXTVAL,
2618 -1,
2619 c_rec_resinst.sr_instance_id,
2620 c_rec_resinst.organization_id,
2621 c_rec_resinst.department_id,
2622 c_rec_resinst.resource_id,
2623 c_rec_resinst.res_instance_id,
2624 c_rec_resinst.equipment_item_id,
2625 NULL,
2626 c_rec_resinst.serial_number,
2627 c_rec_resinst.simulation_set,
2628 c_rec_resinst.shift_num,
2629 c_rec_resinst.shift_date,
2630 c_rec_resinst.from_time,
2631 c_rec_resinst.to_time,
2632 NULL, /* STATUS */
2633 NULL, /* APPLIED */
2634 2, /* UPDATED */
2635 MSC_CL_COLLECTION.v_current_date,
2636 MSC_CL_COLLECTION.v_current_user,
2637 MSC_CL_COLLECTION.v_current_date,
2638 MSC_CL_COLLECTION.v_current_user,
2639 MSC_CL_COLLECTION.v_last_collection_id);
2640
2641 c_count:= c_count+1;
2642
2643 IF c_count> MSC_CL_COLLECTION.PBS THEN
2644 COMMIT;
2645 c_count:= 0;
2646 END IF;
2647
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650
2651 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2652
2653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2654 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2655 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2656 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RES_INST_AVAIL');
2657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2658
2659 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2660 RAISE;
2661
2662 ELSE
2663
2664 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2665
2666 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2667 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2668 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
2669 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_NET_RES_INST_AVAIL');
2670 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2671
2672 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2673 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2674 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec_resinst.RESOURCE_ID));
2675 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2676
2677 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2678 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2679 FND_MESSAGE.SET_TOKEN('VALUE',
2680 MSC_GET_NAME.ORG_CODE( c_rec_resinst.ORGANIZATION_ID,
2681 MSC_CL_COLLECTION.v_instance_id));
2682 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2683
2684 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2685 END IF;
2686
2687 END;
2688
2689 END LOOP;
2690 END IF; /* IF lv_res_inst_avail = MSC_CL_COLLECTION.SYS_NO THEN */
2691 END LOOP;
2692
2693 COMMIT;
2694
2695 END IF;
2696
2697 END IF; -- recalc_nra
2698
2699 END LOAD_NET_RESOURCE_AVAIL;
2700
2701
2702
2703 PROCEDURE POPULATE_AVAIL_RES_AHL (
2704 ERRBUF OUT NOCOPY VARCHAR2,
2705 RETCODE OUT NOCOPY NUMBER,
2706 pINSTANCE_ID IN NUMBER
2707 )
2708 IS
2709 v_stmt VARCHAR2 (10);
2710 v_current_login NUMBER;
2711 V_count NUMBER;
2712 l_sql_stmt VARCHAR2 (4000);
2713 l_sql_stmt_del VARCHAR2 (4000);
2714 l_sql_stmt_tab VARCHAR2 (4000);
2715 lv_plan_name msc_plans.COMPILE_DESIGNATOR%TYPE;
2716 lv_plan_id msc_plans.plan_id%TYPE;
2717 lv_INSTANCE_ID MSC_APPS_INSTANCES.instance_id%TYPE;
2718 LV_DATA_START_DATE MSC_PLANS.DATA_START_DATE%TYPE;
2719 lv_PLAN_COMPLETION_DATE MSC_PLANS.PLAN_COMPLETION_DATE%TYPE;
2720 lv_UOM MSC_DEPARTMENT_RESOURCES.UNIT_OF_MEASURE%TYPE;
2721 v_dblink MSC_APPS_INSTANCES.M2A_DBLINK%TYPE;
2722 LV_SOURCE_APPLICATION VARCHAR2 (3);
2723 v_current_date MSC_NET_RESOURCE_AVAIL.LAST_UPDATE_DATE%TYPE;
2724 v_current_user MSC_NET_RESOURCE_AVAIL.LAST_UPDATED_BY%TYPE;
2725 lv_TABLE_NAME all_tables.TABLE_NAME%TYPE;
2726 NULL_DBLINK CONSTANT VARCHAR2 (1) := ' ';
2727 lv_ahl_schema VARCHAR2(32);
2728
2729 BEGIN
2730
2731 v_current_date := SYSDATE;
2732 v_current_user := FND_GLOBAL.USER_ID;
2733 v_current_login := FND_GLOBAL.LOGIN_ID;
2734 lv_plan_name := fnd_profile.VALUE ('AHL_MSC_RES_CPCTY_PLAN');
2735 LV_SOURCE_APPLICATION := 'MSC';
2736 lv_ahl_schema := MSC_UTIL.GET_SCHEMA_NAME(867);
2737
2738 SAVEPOINT AHL;
2739
2740 BEGIN
2741 SELECT DECODE (M2A_DBLINK, NULL, NULL_DBLINK, '@' || M2A_DBLINK)
2742 INTO v_dblink
2743 FROM MSC_APPS_INSTANCES
2744 WHERE INSTANCE_ID = pINSTANCE_ID;
2745 EXCEPTION
2746 WHEN NO_DATA_FOUND
2747 THEN
2748 RETCODE := MSC_UTIL.G_ERROR;
2749 FND_MESSAGE.SET_NAME ('MSC', 'MSC_DP_INVALID_INSTANCE_ID');
2750 FND_MESSAGE.SET_TOKEN ('INSTANCE_ID', pINSTANCE_ID);
2751 ERRBUF := FND_MESSAGE.GET;
2752 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, ERRBUF);
2753 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
2754 RETURN;
2755 WHEN OTHERS
2756 THEN
2757 RAISE;
2758 END;
2759
2760
2761
2762 BEGIN
2763
2764 l_sql_stmt_tab := 'SELECT TABLE_NAME from all_tables'|| v_dblink
2765 || ' where TABLE_NAME =''AHL_DEPT_RESOURCE_CAPACITY'''
2766 || ' AND owner= ''AHL''';
2767
2768 EXECUTE IMMEDIATE l_sql_stmt_tab INTO lv_TABLE_NAME;
2769
2770 EXCEPTION
2771 WHEN NO_DATA_FOUND
2772 THEN
2773 RETCODE := MSC_UTIL.G_ERROR;
2774 LOG_MESSAGE('Table name AHL_DEPT_RESOURCE_CAPACITY is not available on source');
2775 ERRBUF := FND_MESSAGE.GET;
2776 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, ERRBUF);
2777 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
2778 RETURN;
2779 WHEN OTHERS
2780 THEN
2781 RAISE;
2782 END;
2783
2784
2785 BEGIN
2786 SELECT PLAN_ID,DATA_START_DATE,PLAN_COMPLETION_DATE
2787 INTO lv_plan_id,lv_DATA_START_DATE,lv_PLAN_COMPLETION_DATE
2788 FROM msc_plans
2789 WHERE COMPILE_DESIGNATOR = lv_plan_name
2790 AND SR_INSTANCE_ID = pINSTANCE_ID;
2791 EXCEPTION
2792 WHEN NO_DATA_FOUND
2793 THEN
2794 RETCODE := MSC_UTIL.G_WARNING;
2795 LOG_MESSAGE('Error::NO RECORDS for PLAN '|| lv_plan_name || '::IN TABLE MSC_PLANS');
2796 ERRBUF := FND_MESSAGE.GET;
2797 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, ERRBUF);
2798 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
2799 RETURN;
2800 WHEN OTHERS
2801 THEN
2802 RAISE;
2803 END;
2804
2805
2806
2807
2808 SELECT COUNT ( * )
2809 INTO V_count
2810 FROM MSC_NET_RESOURCE_AVAIL
2811 WHERE PLAN_ID = lv_plan_id AND SR_INSTANCE_ID = pINSTANCE_ID and rownum <2;
2812
2813 IF V_count = 0
2814 THEN
2815 RETCODE := MSC_UTIL.G_WARNING;
2816
2817 LOG_MESSAGE('Error::NO RECORDS for PLAN '|| lv_plan_name || '::IN TABLE MSC_NET_RESOURCE_AVAIL');
2818
2819 END IF;
2820
2821 IF lv_PLAN_COMPLETION_DATE is null
2822 THEN
2823 RETCODE := MSC_UTIL.G_WARNING;
2824
2825 LOG_MESSAGE('Error::For The Plan PLAN '|| lv_plan_name || '::PLAN_COMPLETION_DATE is Null In TABLE MSC_PLANS');
2826
2827 END IF;
2828
2829 IF V_count > 0 and lv_PLAN_COMPLETION_DATE is not null
2830 THEN
2831
2832
2833
2834
2835
2836
2837 BEGIN
2838
2839
2840 l_sql_stmt_del := 'DELETE from AHL_DEPT_RESOURCE_CAPACITY'|| v_dblink
2841 ||'where SOURCE_APPLICATION = ''MSC'''; -- 13820344
2842
2843
2844
2845
2846 EXECUTE IMMEDIATE l_sql_stmt_del;
2847
2848 EXCEPTION
2849 WHEN OTHERS
2850 THEN
2851 RAISE;
2852 END;
2853
2854
2855
2856 LOG_MESSAGE('--------------------------------------------------------');
2857 LOG_MESSAGE(' Populating Available ResourcesINTO AHL_DEPT_RESOURCE_CAPACITY.......................');
2858 LOG_MESSAGE('--------------------------------------------------------');
2859
2860
2861 v_stmt := 'AHL100';
2862
2863
2864 l_sql_stmt :=
2865 'INSERT INTO AHL_DEPT_RESOURCE_CAPACITY'
2866 || v_dblink
2867 || ' (Organization_id, '
2868 || ' Department_id, '
2869 || ' Resource_id, '
2870 || ' available_date, '
2871 || ' LAST_UPDATE_DATE, '
2872 || ' LAST_UPDATED_BY, '
2873 || ' CREATION_DATE, '
2874 || ' LAST_UPDATE_LOGIN, '
2875 || ' CREATED_BY, '
2876 || ' capacity_units, '
2877 || ' UOM_CODE, '
2878 || ' SOURCE_APPLICATION, '
2879 || ' ASCP_PLAN_ID, '
2880 || ' ASCP_PLAN_DATE, '
2881 || ' ASCP_PLAN_NAME, '
2882 || ' BOM_SIMULATION_SET_ID) '
2883 || ' SELECT '
2884 || ' MNRA.ORGANIZATION_ID, '
2885 || ' MNRA.DEPARTMENT_ID/2, '
2886 || ' MNRA.RESOURCE_ID/2, '
2887 || ' trunc(MNRA.SHIFT_DATE), '
2888 ||' SYSDATE, '
2889 || v_current_user
2890 || ','
2891 ||' SYSDATE, '
2892 || V_CURRENT_LOGIN
2893 || ','
2894 || v_current_user
2895 || ','
2896 || ' SUM( nvl((DECODE(LEAST(mnra.to_time, mnra.from_time),mnra.to_time, mnra.to_time + 24*3600,mnra.to_time)- mnra.from_time)/3600,0.0) *mnra.capacity_units), '
2897 || ' MDR.UNIT_OF_MEASURE, '
2898 || '''' || LV_SOURCE_APPLICATION || ''','
2899 || ' MNRA.plan_id, '
2900 || '''' || lv_DATA_START_DATE || ''','
2901 || '''' || lv_plan_name || ''','
2902 || ' MNRA.SIMULATION_SET_ID '
2903 || ' From MSC_NET_RESOURCE_AVAIL MNRA,MSC_DEPARTMENT_RESOURCES MDR '
2904 || ' WHERE MNRA.PLAN_ID = MDR.PLAN_ID '
2905 || ' AND MNRA.ORGANIZATION_ID = MDR.ORGANIZATION_ID '
2906 || ' AND MNRA.SR_INSTANCE_ID = MDR.SR_INSTANCE_ID '
2907 || ' AND MNRA.RESOURCE_ID = MDR.RESOURCE_ID '
2908 || ' AND MNRA.DEPARTMENT_ID = MDR.DEPARTMENT_ID '
2909 || ' AND MNRA.PLAN_ID ='
2910 || lv_plan_id
2911 || ' AND MNRA.SR_INSTANCE_ID = :pINSTANCE_ID '
2912 || ' GROUP BY MNRA.ORGANIZATION_ID,MNRA.DEPARTMENT_ID,MNRA.RESOURCE_ID,trunc( MNRA.SHIFT_DATE ),UNIT_OF_MEASURE,MNRA.plan_id,MNRA.SIMULATION_SET_ID';
2913
2914
2915 EXECUTE IMMEDIATE l_sql_stmt USING pINSTANCE_ID;
2916
2917
2918
2919
2920 END IF;
2921
2922 COMMIT;
2923 EXCEPTION
2924 WHEN OTHERS
2925 THEN
2926 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Stack...');
2927 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR,
2928 DBMS_UTILITY.FORMAT_ERROR_STACK);
2929 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR, 'Error_Backtrace...');
2930 MSC_UTIL.LOG_MSG (MSC_UTIL.G_LVL_FATAL_ERR,
2931 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2932 ROLLBACK TO SAVEPOINT AHL;
2933 RETCODE := MSC_UTIL.G_ERROR;
2934
2935 LOG_MESSAGE('Error::('|| to_char(v_stmt) || ')::' ||
2936 to_char(sqlcode) ||':'|| substr(sqlerrm,1,60));
2937
2938 END POPULATE_AVAIL_RES_AHL;
2939
2940 --===================================================================
2941
2942 END MSC_resource_availability;