1 PACKAGE BODY MRP_RHX_RESOURCE_AVAILABILITY AS
2 /* $Header: MRPXNRAB.pls 120.1.12010000.2 2008/09/18 16:38:54 mlouie ship $ */
3 DELETE_WORKDAY CONSTANT number := 1;
4 CHANGE_WORKDAY CONSTANT number := 2;
5 ADD_WORKDAY CONSTANT number := 3;
6 HOLD_TIME CONSTANT number := 9999999;
7 var_gt_user_id number;
8 var_gt_debug boolean;
9
10 function check_24( var_time in number) return number is
11 begin
12 /*
13 if var_gt_debug then
14 dbms_output.put_line('In check_24 '|| to_char(var_time));
15 end if;
16 */
17 if var_time > 24*3600 then
18 return var_time - 24*3600;
19 else
20 return var_time;
21 end if;
22 end check_24;
23
24 procedure update_avail( var_rowid in ROWID,
25 var_date in DATE,
26 var_from_time in number,
27 var_to_time in number) is
28 var_time1 number;
29 var_time2 number;
30 var_date1 DATE;
31 begin
32 var_time1 := check_24(var_from_time);
33 var_time2 := check_24(var_to_time);
34 /*
35 if the start time is in the next, this avail should be on the
36 following day
37 */
38
39 var_date1 := var_date;
40 if var_time1 < var_from_time then
41 var_date1 := var_date1 + 1;
42 end if;
43
44 UPDATE mrp_net_resource_avail
45 SET shift_date = var_date1,
46 from_time = var_time1,
47 to_time = var_time2
48 WHERE rowid = var_rowid;
49 end update_avail;
50
51 procedure delete_avail( var_rowid in ROWID) is
52 begin
53 /*
54 if var_gt_debug then
55 dbms_output.put_line('about to delete');
56 end if;
57 */
58 DELETE from mrp_net_resource_avail
59 WHERE rowid = var_rowid;
60 end delete_avail;
61
62 procedure insert_avail( var_date in DATE,
63 var_department_id in number,
64 var_resource_id in number,
65 var_organization_id in number,
66 var_shift_num in number,
67 var_simulation_set in varchar2,
68 var_from_time in number,
69 var_to_time in number,
70 var_cap_units in number) is
71 var_time1 number;
72 var_time2 number;
73 var_date1 DATE;
74
75 begin
76 var_time1 := check_24(var_from_time);
77 var_time2 := check_24(var_to_time);
78
79 /*
80 if the start time is in the next, this avail should be on the
81 following day
82 */
83
84 var_date1 := var_date;
85 if var_time1 < var_from_time then
86 var_date1 := var_date1 + 1;
87 end if;
88 /*
89 if var_gt_debug then
90 dbms_output.put_line('Ready to insert' ||
91 ' Dept ' || to_char(var_department_id) ||
92 ' Res ' || to_char(var_resource_id) ||
93 ' shift ' || to_char(var_shift_num) ||
94 ' date '|| to_char(var_date) ||
95 ' from time '|| to_char(var_from_time/3600)||
96 ' to time '|| to_char(var_to_time/3600) ||
97 ' units '|| to_char(var_cap_units));
98 end if;
99 */
100 INSERT into mrp_net_resource_avail(
101 department_id,
102 resource_id,
103 organization_id,
104 shift_num,
105 shift_date,
106 from_time,
107 to_time,
108 capacity_units,
109 simulation_set,
110 last_update_date,
111 last_updated_by,
112 creation_date,
113 created_by)
114 VALUES(
115 var_department_id,
116 var_resource_id,
117 var_organization_id,
118 var_shift_num,
119 var_date1,
120 var_time1,
121 var_time2,
122 var_cap_units,
123 var_simulation_set,
124 sysdate,
125 var_gt_user_id,
126 sysdate,
127 var_gt_user_id);
128 end insert_avail;
129
130 PROCEDURE calc_res_avail( arg_organization_id IN number,
131 arg_department_id IN number,
132 arg_resource_id IN number,
133 arg_simulation_set IN varchar2,
134 arg_24hr_flag IN number,
135 arg_start_date IN date default SYSDATE,
136 arg_cutoff_date IN date) is
137
138 cursor changes is
139 SELECT changes.action_type,
140 changes.from_time,
141 DECODE(LEAST(changes.to_time, changes.from_time),
142 changes.to_time, changes.to_time + 24*3600,
143 changes.to_time),
144 dates.shift_date,
145 changes.shift_num,
146 changes.capacity_change
147 from bom_shift_dates dates,
148 bom_resource_changes changes,
149 mtl_parameters param
150 WHERE dates.calendar_code = param.calendar_code
151 AND dates.exception_set_id = param.calendar_exception_set_id
152 AND dates.seq_num is not null
153 AND dates.shift_date between changes.from_date AND
154 NVL(changes.to_date, changes.from_date)
155 AND dates.shift_num = changes.shift_num
156 AND param.organization_id = arg_organization_id
157 AND changes.to_date >= trunc(arg_start_date)
158 AND changes.from_date <= arg_cutoff_date
159 AND changes.simulation_set = arg_simulation_set
160 AND changes.action_type = CHANGE_WORKDAY
161 AND changes.resource_id = arg_resource_id
162 AND changes.department_id = arg_department_id
163 ORDER BY dates.shift_date, changes.from_time;
164
165
166 var_action_type number;
167 var_from_time number;
168 var_to_time number;
169 var_shift_date date;
170 var_from_shift_time number;
171 var_to_shift_time number;
172 var_orig_cap number;
173 var_shift_num number;
174 var_cap_change number;
175 var_orig_from_time number;
176 var_orig_to_time number;
177 var_next_from_time number;
178 var_rowid rowid;
179 var_rowcount number;
180 var_new_cap number;
181 cursor avail is
182 SELECT capacity_units capacity_units,
183 from_time from_time,
184 DECODE(LEAST(to_time, from_time),
185 to_time, to_time + 24*3600,
186 to_time) to_time,
187 rowid
188 FROM mrp_net_resource_avail
189 WHERE department_id = arg_department_id
190 AND resource_id = arg_resource_id
191 AND simulation_set = arg_simulation_set
192 AND organization_id = arg_organization_id
193 AND shift_num = var_shift_num
194 AND shift_date = var_shift_date
195 UNION ALL
196 SELECT 0 capacity_units,
197 HOLD_TIME from_time,
198 HOLD_TIME to_time,
199 rowid
200 from dual
201 ORDER BY 2, 3;
202 begin
203 var_gt_user_id := fnd_global.user_id;
204 if arg_24hr_flag = 2 THEN
205 insert into mrp_net_resource_avail(
206 organization_id,
207 department_id,
208 resource_id,
209 shift_num,
210 shift_date,
211 from_time,
212 to_time,
213 capacity_units,
214 simulation_set,
215 last_update_date,
216 last_updated_by,
217 creation_date,
218 created_by)
219 select arg_organization_id,
220 arg_department_id,
221 arg_resource_id,
222 res_shifts.shift_num,
223 dates.shift_date,
224 shifts.from_time,
225 shifts.to_time,
226 nvl(res_shifts.capacity_units,nvl(dept_res1.capacity_units,1)),
227 arg_simulation_set,
228 sysdate,
229 var_gt_user_id,
230 sysdate,
231 var_gt_user_id
232 FROM bom_shift_dates dates,
233 bom_shift_times shifts,
234 bom_resource_shifts res_shifts,
235 bom_department_resources dept_res1,
236 mtl_parameters param
237 WHERE dates.calendar_code = param.calendar_code
238 AND dates.exception_set_id = param.calendar_exception_set_id
239 AND dates.shift_num = shifts.shift_num
240 AND dates.seq_num is not null
241 AND dates.shift_date >= trunc(arg_start_date)
242 AND dates.shift_date <= arg_cutoff_date
243 AND shifts.shift_num = res_shifts.shift_num
244 AND shifts.calendar_code = param.calendar_code
245 AND res_shifts.department_id = dept_res1.department_id
246 AND res_shifts.resource_id = dept_res1.resource_id
247 AND NVL(dept_res1.available_24_hours_flag, 2) = 2
248 AND dept_res1.share_from_dept_id is null
249 AND dept_res1.resource_id = arg_resource_id
250 AND dept_res1.department_id = arg_department_id
251 AND param.organization_id = arg_organization_id
252 AND NOT EXISTS
253 (SELECT NULL
254 FROM bom_resource_changes changes
255 WHERE changes.department_id = dept_res1.department_id
256 AND changes.resource_id = dept_res1.resource_id
257 AND changes.shift_num = dates.shift_num
258 AND changes.from_date = dates.shift_date
259 AND changes.action_type = DELETE_WORKDAY);
260 else
261 insert into mrp_net_resource_avail(
262 organization_id,
263 department_id,
264 resource_id,
265 shift_num,
266 shift_date,
267 from_time,
268 to_time,
269 capacity_units,
270 simulation_set,
271 last_update_date,
272 last_updated_by,
273 creation_date,
274 created_by)
275 select arg_organization_id,
276 arg_department_id,
277 arg_resource_id,
278 0,
279 dates.calendar_date,
280 1,
281 24*60*60 - 1,
282 nvl(dept_res1.capacity_units, 1),
283 arg_simulation_set,
284 sysdate,
285 var_gt_user_id,
286 sysdate,
287 var_gt_user_id
288 FROM bom_calendar_dates dates,
289 bom_department_resources dept_res1,
290 mtl_parameters param
291 WHERE dates.calendar_code = param.calendar_code
292 AND dates.exception_set_id = param.calendar_exception_set_id
293 AND dates.calendar_date <= arg_cutoff_date
294 AND dates.seq_num is not null
295 AND dates.calendar_date >= trunc(arg_start_date)
296 AND NVL(dept_res1.available_24_hours_flag, 2) = 1
297 AND dept_res1.share_from_dept_id is null
298 AND dept_res1.resource_id = arg_resource_id
299 AND dept_res1.department_id = arg_department_id
300 AND param.organization_id = arg_organization_id;
301 end if;
302
303 if arg_24hr_flag = 2 then
304 OPEN changes;
305 loop
306 FETCH changes INTO
307 var_action_type,
308 var_orig_from_time,
309 var_orig_to_time,
310 var_shift_date,
311 var_shift_num,
312 var_cap_change;
313 EXIT WHEN changes%NOTFOUND;
314 /*
315 if var_gt_debug then
316 dbms_output.put_line('From time '||
317 to_char(var_orig_from_time/3600) ||
318 ' To Time '|| to_char(var_orig_to_time/3600) ||
319 ' Shift '|| to_char(var_shift_num) ||
320 ' Shift date '|| to_char(var_shift_date));
321 end if;
322 */
323
324 /*----------------------------------------------------------+
325 | For each modification we get the current resource |
326 | calendar and process sections of the modification that |
327 | overlaps with the shift segment |
328 +----------------------------------------------------------*/
329 -- Initialize the variables
330 var_from_time := var_orig_from_time;
331 var_next_from_time := var_orig_from_time;
332 var_to_time := var_orig_to_time;
333 var_rowcount := 0;
334 OPEN avail;
335 LOOP
336 FETCH avail INTO
337 var_orig_cap,
338 var_from_shift_time,
339 var_to_shift_time,
340 var_rowid;
341 EXIT WHEN avail%NOTFOUND;
342 -- Set the from time for the modification to the start of
343 -- the unprocessed section
344 var_from_time := var_next_from_time;
345 -- Set the to time to the original to time of the modification
346 var_to_time := var_orig_to_time;
347 -- If you have completely processed the modification you are
348 -- done so exit
349 if (var_from_time > var_to_time) then
350 EXIT;
351 end if;
352 var_rowcount := var_rowcount + 1;
353 -- If only row is the extra dummy row, you are processing a
354 -- modification for a deleted workday... skip the row
355 if var_from_shift_time = HOLD_TIME AND var_rowcount = 1 THEN
356 EXIT;
357 -- If this is the dummy extra row and you have not completely
358 -- processed the modification...that is probably because the
359 -- modification does not overlap with the shift..go ahead and
360 -- process it that way
361 elsif var_from_shift_time = HOLD_TIME
362 AND var_from_time <= var_to_time
363 THEN
364 var_from_shift_time := var_from_time - 2;
365 var_to_shift_time := var_from_time - 1;
366 else
367 -- if the shift spans over midnight
368 -- and the shift exception starts on the next day
369 -- then, the shift exception times need to be moved to the next day
370 if var_to_shift_time > 24*60*60 then
371 if var_from_time < var_to_shift_time - 24*60*60 then
372 var_from_time := var_from_time + 24*60*60;
373 var_to_time := var_to_time + 24*60*60;
374 end if;
375 end if;
376
377 -- If the modification overlaps a shift segment then set
378 -- the end time of the modification to the least of the
379 -- modification end time or shift end time
380 -- the second check (var_to_time > var_from_shift_time)
381 -- checks for the possibility that the exception is
382 -- actually for the following day
383 if (var_from_time < var_to_shift_time
384 and var_to_time > var_from_shift_time) then
385 var_to_time := LEAST(var_to_shift_time, var_to_time);
386 var_next_from_time := var_to_time + 1;
387 else
388 -- Otherwise the modification does not overlap with the
389 -- shift. In that case do not process and leave it for the
390 -- next shift segment
391 goto skip;
392 end if;
393 end if;
394
395 /*
396 if var_gt_debug then
397 dbms_output.put_line('From Shift time '||
398 to_char(var_from_shift_time/3600) ||
399 ' To Shift Time '|| to_char(var_to_shift_time/3600) ||
400 ' Next shift time '|| to_char(var_next_from_time)/3600);
401 end if;
402 */
403 /*
404 If the modification starts before the shift starts and ends on or before the shift starts
405 Note that you can only add capacity here since you have none to reduce
406 */
407 if var_from_time < var_from_shift_time AND
408 var_to_time <= var_from_shift_time AND
409 var_cap_change > 0 THEN
410 /* you cannot reduce capacity here because you have none to reduce
411 addresses bugfix 3072102 */
412 if var_to_time < var_from_shift_time then
413 insert_avail(var_shift_date,
414 arg_department_id,
415 arg_resource_id,
416 arg_organization_id,
417 var_shift_num,
418 arg_simulation_set,
419 var_from_time,
420 var_to_time,
421 var_cap_change);
422 else
423 /* if to time = from shift time, it's ambiguous what the capacity is at from shift time
424 so we will only increase to 1 minute before from shift time */
425 insert_avail(var_shift_date,
426 arg_department_id,
427 arg_resource_id,
428 arg_organization_id,
429 var_shift_num,
430 arg_simulation_set,
431 var_from_time,
432 var_from_shift_time - 1,
433 var_cap_change);
434 end if;
435 end if;
436 /*
437 If the modification starts before the shift starts and
438 ends after the shift starts but on or before the shift ends */
439 if var_from_time < var_from_shift_time AND
440 var_from_shift_time < var_to_time AND
441 var_to_time <= var_to_shift_time THEN
442 /* bugfix 3072102 If modification starts before shifts, then reduce
443 capacity change does not make sense */
444 if var_cap_change > 0 then
445 insert_avail(var_shift_date,
446 arg_department_id,
447 arg_resource_id,
448 arg_organization_id,
449 var_shift_num,
450 arg_simulation_set,
451 var_from_time,
452 var_from_shift_time - 1,
453 var_cap_change);
454 end if;
455 if (var_orig_cap + var_cap_change) <= 0 then
456 delete_avail(var_rowid);
457 else
458 if var_to_time < var_to_shift_time then
459 insert_avail(var_shift_date,
460 arg_department_id,
461 arg_resource_id,
462 arg_organization_id,
463 var_shift_num,
464 arg_simulation_set,
465 var_from_shift_time,
466 var_to_time,
467 var_orig_cap + var_cap_change);
468 update_avail(var_rowid,
469 var_shift_date,
470 var_to_time + 1,
471 var_to_shift_time);
472 /* Otherwise the to time and the shift end time are
473 the same */
474 else
475 delete_avail(var_rowid);
476 insert_avail(var_shift_date,
477 arg_department_id,
478 arg_resource_id,
479 arg_organization_id,
480 var_shift_num,
481 arg_simulation_set,
482 var_from_shift_time,
483 var_to_shift_time,
484 var_orig_cap + var_cap_change);
485 end if;
486 end if;
487 end if;
488 /* If the modification starts before the shift starts and
489 ends after the shift ends */
490 if var_from_time < var_from_shift_time AND
491 var_to_shift_time < var_to_time THEN
492 if var_cap_change > 0 then
493 insert_avail(var_shift_date,
494 arg_department_id,
495 arg_resource_id,
496 arg_organization_id,
497 var_shift_num,
498 arg_simulation_set,
499 var_from_time,
500 var_from_shift_time - 1,
501 var_cap_change);
502 end if;
503 delete_avail(var_rowid);
504 if (var_orig_cap + var_cap_change) > 0 then
505 insert_avail(var_shift_date,
506 arg_department_id,
507 arg_resource_id,
508 arg_organization_id,
509 var_shift_num,
510 arg_simulation_set,
511 var_from_shift_time,
512 var_to_shift_time,
513 var_orig_cap + var_cap_change);
514 end if;
515 if var_cap_change > 0 then
516 insert_avail(var_shift_date,
517 arg_department_id,
518 arg_resource_id,
519 arg_organization_id,
520 var_shift_num,
521 arg_simulation_set,
522 var_to_shift_time + 1,
523 var_to_time,
524 var_cap_change);
525 end if;
526 end if;
527
528 /* If the modification starts on or after the shift starts and
529 ends on or before the shift ends */
530 if var_from_shift_time <= var_from_time AND
531 var_to_time <= var_to_shift_time THEN
532 if var_from_shift_time < var_from_time then
533 update_avail(var_rowid,
534 var_shift_date,
535 var_from_shift_time,
536 var_from_time - 1);
537 end if;
538 if (var_orig_cap + var_cap_change) > 0 then
539 insert_avail(var_shift_date,
540 arg_department_id,
541 arg_resource_id,
542 arg_organization_id,
543 var_shift_num,
544 arg_simulation_set,
545 var_from_time,
546 var_to_time,
547 var_orig_cap + var_cap_change);
548 end if;
549 if var_to_time < var_to_shift_time then
550 insert_avail(var_shift_date,
551 arg_department_id,
552 arg_resource_id,
553 arg_organization_id,
554 var_shift_num,
555 arg_simulation_set,
556 var_to_time + 1,
557 var_to_shift_time,
558 var_orig_cap);
559 end if;
560 if var_from_time = var_from_shift_time AND
561 var_to_time = var_to_shift_time THEN
562 delete_avail(var_rowid);
563 end if;
564 end if;
565
566 /* If the modification starts on or after the shift starts and ends
567 after the shift ends */
568 if var_from_shift_time <= var_from_time AND
569 var_to_shift_time >= var_from_time AND
570 var_to_time > var_to_shift_time THEN
571 if var_from_shift_time < var_from_time then
572 update_avail(var_rowid,
573 var_shift_date,
574 var_from_shift_time,
575 var_from_time - 1);
576 else
577 delete_avail(var_rowid);
578 end if;
579 if (var_orig_cap + var_cap_change) > 0 then
580 insert_avail(var_shift_date,
581 arg_department_id,
582 arg_resource_id,
583 arg_organization_id,
584 var_shift_num,
585 arg_simulation_set,
586 var_from_time,
587 var_to_shift_time,
588 var_orig_cap + var_cap_change);
589 end if;
590 if var_cap_change > 0 then
591 insert_avail(var_shift_date,
592 arg_department_id,
593 arg_resource_id,
594 arg_organization_id,
595 var_shift_num,
596 arg_simulation_set,
597 var_to_shift_time + 1,
598 var_to_time,
599 var_cap_change);
600 end if;
601 end if;
602
603 /* If the modification starts on or after the shift ends
604 and you are adding capacity (cannot reduce capacity here)
605 */
606 if var_from_time >= var_to_shift_time AND
607 var_cap_change > 0 THEN
608 if var_from_time > var_to_shift_time then
609 insert_avail(var_shift_date,
610 arg_department_id,
611 arg_resource_id,
612 arg_organization_id,
613 var_shift_num,
614 arg_simulation_set,
615 var_from_time,
616 var_to_time,
617 var_cap_change);
618 else
619 insert_avail(var_shift_date,
620 arg_department_id,
621 arg_resource_id,
622 arg_organization_id,
623 var_shift_num,
624 arg_simulation_set,
625 var_to_shift_time + 1,
626 var_to_time,
627 var_cap_change);
628 end if;
629 end if;
630 <<skip>>
631 NULL;
632 end loop;
633 close avail;
634 end loop;
635 -- Finally add the availability from the add workday type modifications
636
637 INSERT into mrp_net_resource_avail(
638 organization_id,
639 department_id,
640 resource_id,
641 shift_num,
642 shift_date,
643 from_time,
644 to_time,
645 capacity_units,
646 simulation_set,
647 last_update_date,
648 last_updated_by,
649 creation_date,
650 created_by)
651 select arg_organization_id,
652 arg_department_id,
653 arg_resource_id,
654 changes.shift_num,
655 changes.from_date,
656 changes.from_time,
657 changes.to_time,
658 changes.capacity_change,
659 arg_simulation_set,
660 sysdate,
661 var_gt_user_id,
662 sysdate,
663 var_gt_user_id
664 FROM bom_resource_changes changes
665 WHERE changes.department_id = arg_department_id
666 AND changes.resource_id = arg_resource_id
667 AND changes.action_type = ADD_WORKDAY
668 AND changes.simulation_set= arg_simulation_set;
669 end if;
670 end calc_res_avail;
671
672 procedure populate_avail_resources(arg_simulation_set in varchar2,
673 arg_organization_id in number,
674 arg_start_date IN date default SYSDATE,
675 arg_cutoff_date in date default NULL) is
676 cursor dept_res is
677 select dept_res.department_id,
678 dept_res.resource_id,
679 NVL(dept_res.available_24_hours_flag, 2)
680 from bom_department_resources dept_res,
681 bom_departments dept
682 where dept_res.department_id = dept.department_id
683 AND dept_res.share_from_dept_id is null
684 AND dept.organization_id = arg_organization_id;
685
686 var_department_id NUMBER;
687 var_resource_id NUMBER;
688 var_24hr_flag NUMBER;
689 var_cutoff_date DATE;
690 begin
691 var_gt_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
692 --dbms_output.enable(100000);
693 delete from mrp_net_resource_avail
694 where organization_id = arg_organization_id
695 and (arg_simulation_set is null or simulation_set = arg_simulation_set);
696
697 if arg_cutoff_date is null then
698 var_cutoff_date := TRUNC(SYSDATE) + 700;
699 else
700 var_cutoff_date := arg_cutoff_date;
701 end if;
702 open dept_res;
703 LOOP
704 Fetch dept_res into var_department_id,
705 var_resource_id,
706 var_24hr_flag;
707 EXIT WHEN dept_res%NOTFOUND;
708 calc_res_avail(arg_organization_id,
709 var_department_id,
710 var_resource_id,
711 arg_simulation_set,
712 var_24hr_flag,
713 arg_start_date,
714 var_cutoff_date);
715 END LOOP;
716 COMMIT;
717 end populate_avail_resources;
718 end mrp_rhx_resource_availability;
[Home] [Help]