1 PACKAGE BODY Msc_UPDATE_RESOURCE AS
2 /* $Header: MSCFNAVB.pls 120.7 2009/10/12 22:21:39 pabram ship $ */
3
4 TYPE ResRecTyp IS RECORD (
5 TRANSACTION_ID NUMBER,
6 PARENT_ID NUMBER,
7 AGGREGATE_RESOURCE_ID NUMBER,
8 SIMULATION_SET VARCHAR2(10),
9 FROM_TIME NUMBER,
10 TO_TIME NUMBER,
11 CAPACITY_UNITS NUMBER,
12 STATUS NUMBER,
13 APPLIED NUMBER,
14 UPDATED NUMBER,
15 LAST_UPDATE_DATE DATE,
16 LAST_UPDATED_BY NUMBER,
17 CREATION_DATE DATE,
18 CREATED_BY NUMBER,
19 LAST_UPDATE_LOGIN NUMBER,
20 shift_date DATE,
21 shift_number NUMBER);
22
23 TYPE ResTabTyp IS TABLE OF ResRecTyp
24 INDEX by binary_integer;
25
26 TYPE ChangeRecTyp IS RECORD (
27 OPERATION NUMBER,
28 ORGANIZATION_ID NUMBER,
29 SR_INSTANCE_ID NUMBER,
30 DEPARTMENT_ID NUMBER,
31 RESOURCE_ID NUMBER,
32 SHIFT_DATE DATE,
33 SHIFT_NUMBER NUMBER,
34 FROM_TIME NUMBER,
35 TO_TIME NUMBER,
36 CAPACITY_UNITS NUMBER,
37 LAST_UPDATED_BY NUMBER,
38 RES_INST_ID NUMBER,
39 SERIAL_NUMBER VARCHAR2(2000));
40
41 g_resource_exist boolean;
42 g_res_inst_set_data boolean;
43 g_error_stat VARCHAR2(300);
44 g_plan_id NUMBER;
45 g_simulation_set VARCHAR2(10);
46 g_res_group VARCHAR2(30);
47 g_cutoff_date DATE;
48 g_query_id NUMBER;
49 g_org_id NUMBER;
50 g_instance_id NUMBER;
51 g_department_id NUMBER;
52 g_resource_id NUMBER;
53 g_res_inst_id NUMBER;
54 g_serial_number VARCHAR2(2000);
55 g_shift_date DATE;
56 g_shift_number NUMBER;
57 g_from_time NUMBER;
58 g_to_time NUMBER;
59 g_units NUMBER;
60 g_change_rec ChangeRecTyp;
61 g_res_tab ResTabTyp;
62 g_res_inst_tab ResTabTyp;
63 g_tmp_tab ResTabTyp;
64 i binary_integer;
65 j binary_integer;
66 k binary_integer;
67 OP_ADD_DAY CONSTANT INTEGER :=0;
68 OP_ADD CONSTANT INTEGER :=1;
69 OP_DEL CONSTANT INTEGER :=2;
70 OP_SET CONSTANT INTEGER :=3;
71 OP_DEL_DAY CONSTANT INTEGER :=4;
72
73 CURSOR C_MFQ IS
74 SELECT
75 NUMBER1,
76 NUMBER2,
77 NUMBER3,
78 NUMBER4,
79 NUMBER5,
80 DATE1,
81 NUMBER6,
82 NUMBER7,
83 NUMBER8,
84 NUMBER9,
85 LAST_UPDATED_BY,
86 NUMBER10, CHAR1
87 FROM Msc_FORM_QUERY
88 WHERE query_id = g_query_id
89 ORDER BY number2, number3, number4, number5, date1,
90 number6, number7,number1;
91
92 CURSOR C_CAR IS
93 SELECT
94 transaction_id,
95 parent_id,
96 aggregate_resource_id,
97 simulation_set,
98 from_time,
99 to_time,
100 capacity_units,
101 status,
102 applied,
103 updated,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 CREATION_DATE,
107 CREATED_BY,
108 LAST_UPDATE_LOGIN,
109 shift_date,
110 shift_num
111 FROM MSC_NET_RESOURCE_AVAIL
112 WHERE plan_id=g_plan_id
113 AND organization_id = g_org_id
114 AND sr_instance_id =g_instance_id
115 AND department_id = g_department_id
116 AND resource_id = g_resource_id
117 and shift_date = g_shift_date
118 and decode(resource_id,-1,-1,shift_num) =
119 decode(resource_id,-1,-1,g_shift_number)
120 and capacity_units >=0
121 and nvl(parent_id,0) <> -1
122 order by from_time;
123
124 CURSOR c_car_inst IS
125 SELECT
126 inst_transaction_id transaction_id,
127 parent_id,
128 to_number(null) aggregate_resource_id,
129 simulation_set,
130 from_time,
131 to_time,
132 nvl(capacity_units,1) capacity_units,
133 status,
134 applied,
135 updated,
136 LAST_UPDATE_DATE,
137 LAST_UPDATED_BY,
138 CREATION_DATE,
139 CREATED_BY,
140 LAST_UPDATE_LOGIN,
141 shift_date,
142 shift_num
143 FROM msc_net_res_inst_avail
144 WHERE plan_id=g_plan_id
145 AND organization_id = g_org_id
146 AND sr_instance_id =g_instance_id
147 AND department_id = g_department_id
148 AND resource_id = g_resource_id
149 and res_instance_id = g_res_inst_id
150 and serial_number = g_serial_number
151 and shift_date = g_shift_date
152 and decode(resource_id,-1,-1,shift_num) = decode(resource_id,-1,-1,g_shift_number)
153 and nvl(capacity_units,1) >=0
154 and nvl(parent_id,0) <> -1
155 order by from_time;
156
157 CURSOR date_range IS
158 SELECT distinct mra.shift_date
159 FROM msc_net_resource_avail mra,
160 msc_form_query mfq
161 WHERE mra.plan_id = g_plan_id
162 and mra.organization_id = g_org_id
163 and mra.sr_instance_id = g_instance_id
164 and mra.department_id = g_department_id
165 and mra.resource_id = g_resource_id
166 and nvl(mra.parent_id,0) <> -1
167 and mra.capacity_units >=0
168 and mfq.query_id = g_query_id
169 and trunc(mra.shift_date) between
170 trunc(mfq.date1) and trunc(mfq.date2)
171 ORDER BY mra.shift_date;
172
173 -- global variable for move_resource
174 Type NumTab IS TABLE of number INDEX BY BINARY_INTEGER;
175 Type DateTab IS TABLE of DATE INDEX BY BINARY_INTEGER;
176 p_start_time dateTab;
177 p_end_time dateTab;
178 p_resource_units numTab;
179 p_trans_id numTab;
180
181 TYPE simu_res_type IS RECORD (
182 org_id numTab,
183 inst_id numTab,
184 dept_id numTab,
185 res_id numTab,
186 assign_units numTab,
187 res_hours numTab,
188 op_seq_id numTab,
189 rt_seq_id numTab
190 );
191
192 sim_res simu_res_type;
193
194
195 ---------------------------------------------------------------
196 -- apply change
197 ---------------------------------------------------------------
198 PROCEDURE apply_change( p_query_id IN NUMBER,
199 p_plan_id IN NUMBER ) IS
200
201 l_work_day number;
202
203 CURSOR NWD is
204 SELECT nvl(dates.seq_num, -1)
205 FROM msc_trading_partners mtp,
206 msc_calendar_dates dates
207 WHERE dates.calendar_date = trunc(g_shift_date)
208 AND dates.calendar_code = mtp.calendar_code
209 AND dates.exception_set_id = mtp.calendar_exception_set_id
210 AND dates.sr_instance_id = mtp.sr_instance_id
211 AND mtp.partner_type = 3
212 AND mtp.sr_tp_id = g_org_id
213 AND mtp.sr_instance_id = g_instance_id;
214
215 BEGIN
216 g_plan_id :=p_plan_id;
217 g_query_id := p_query_id;
218 g_org_id :=0;
219 g_instance_id :=0;
220 g_department_id :=0;
221 g_resource_id :=0;
222 g_shift_date :=to_date(null);
223 g_shift_number :=null;
224
225 -- load from mrp_form_query for the changes, if the resource changes
226 -- re-query data from msc_net_resource_avail
227
228 OPEN C_MFQ;
229 LOOP
230 FETCH C_MFQ INTO g_change_rec;
231 EXIT WHEN C_MFQ%NOTFOUND;
232 g_org_id :=g_change_rec.organization_id;
233 g_instance_id := g_change_rec.sr_instance_id;
234 g_department_id :=g_change_rec.department_id;
235 g_resource_id :=g_change_rec.resource_id;
236 g_res_inst_id := g_change_rec.res_inst_id;
237 g_serial_number := g_change_rec.serial_number;
238 g_shift_number :=g_change_rec.shift_number;
239 g_from_time := g_change_rec.from_time;
240 g_to_time := g_change_rec.to_time;
241 g_units := g_change_rec.capacity_units;
242
243 if (g_change_rec.res_inst_id is not null and g_change_rec.operation = OP_SET) then
244 g_res_inst_set_data := true;
245 else
246 g_res_inst_set_data := false;
247 end if;
248
249 OPEN date_range;
250 LOOP
251 FETCH date_range into g_shift_date;
252 EXIT WHEN date_range%NOTFOUND;
253
254 OPEN NWD;
255 FETCH NWD into l_work_day;
256 CLOSE NWD;
257 if ( l_work_day <> -1 ) then
258 initialize_table;
259 calculate_change;
260 update_table;
261 end if;
262 END LOOP;
263 CLOSE date_range;
264 END LOOP;
265 CLOSE C_MFQ;
266 commit;
267 EXCEPTION when others THEN
268
269 IF (C_MFQ%ISOPEN) THEN
270 close C_MFQ;
271 END IF;
272 IF date_range%ISOPEN THEN
273 close date_range;
274 END IF;
275 raise_application_error(-20000, sqlerrm);
276 END apply_change;
277
278 ---------------------------------------------------------------------
279 -- to get the values into PL/SQL tables
280 ---------------------------------------------------------------------
281 PROCEDURE initialize_table IS
282 l_ctr number :=0;
283 BEGIN
284
285 -- load from crp_available_resources for all the records
286 -- related to the same resource
287 j :=0;
288 g_res_tab.delete;
289
290 OPEN C_CAR;
291 LOOP
292 j := j+1;
293 FETCH C_CAR INTO g_res_tab(j);
294 if C_CAR%NOTFOUND then
295 if C_CAR%ROWCOUNT=0 then
296 g_resource_exist :=false;
297 end if;
298 exit;
299 end if;
300
301 END LOOP;
302 IF C_CAR%ROWCOUNT >0 THEN
303 g_resource_exist :=true;
304 END IF;
305 CLOSE C_CAR;
306
307 if (g_res_inst_set_data) then
308 g_res_inst_tab.delete;
309 open c_car_inst;
310 loop
311 l_ctr := l_ctr +1;
312 fetch c_car_inst into g_res_inst_tab(l_ctr);
313 if c_car_inst%notfound then
314 exit;
315 end if;
316 end loop;
317 close c_car_inst;
318 end if;
319
320 EXCEPTION WHEN others THEN
321 IF (C_CAR%ISOPEN) THEN
322 close C_CAR;
323 END IF;
324
325 END initialize_table;
326
327 Function insert_undo_data(undo_type number,
328 j number default null,
329 v_undo_parent_id number default null) return number is
330 v_undo_id number;
331 net_res_columns msc_undo.changeRGType;
332 x_return_sts varchar2(100);
333 x_msg_count number;
334 x_msg_data varchar2(200);
335 i number;
336 begin
337
338 select msc_undo_summary_s.nextval
339 into v_undo_id
340 from dual;
341
342 if undo_type = 2 then -- update
343
344 i := 1;
345 if g_res_tab(j).capacity_units <>
346 g_tmp_tab(k).capacity_units then
347 net_res_columns(i).column_changed := 'CAPACITY_UNITS';
348 net_res_columns(i).column_changed_text := 'Capacity Units';
349 net_res_columns(i).old_value := to_char(g_res_tab(j).capacity_units);
350 net_res_columns(i).column_type := 'NUMBER';
351 net_res_columns(i).new_value := to_char(g_tmp_tab(k).capacity_units);
352 i := i+1;
353 if (g_res_inst_set_data) then
354 g_tmp_tab(k).capacity_units := g_res_tab(j).capacity_units;
355 end if;
356 end if;
357
358 if g_res_tab(j).from_time <>
359 g_tmp_tab(k).from_time then
360 net_res_columns(i).column_changed := 'FROM_TIME';
361 net_res_columns(i).column_changed_text := 'From Time';
362 net_res_columns(i).old_value := to_char(g_res_tab(j).from_time);
363 net_res_columns(i).column_type := 'NUMBER';
364 net_res_columns(i).new_value := to_char(g_tmp_tab(k).from_time);
365 i := i+1;
366 end if;
367
368 if g_res_tab(j).to_time <>
369 g_tmp_tab(k).to_time then
370 net_res_columns(i).column_changed := 'TO_TIME';
371 net_res_columns(i).column_changed_text := 'To Time';
372 net_res_columns(i).old_value := to_char(g_res_tab(j).to_time);
373 net_res_columns(i).column_type := 'NUMBER';
374 net_res_columns(i).new_value := to_char(g_tmp_tab(k).to_time);
375 end if;
376
377 end if;
378
379 msc_undo.store_undo(4, --means msc_net_resource_avail
380 undo_type, --2 is update , 1 is insert a record
381 g_tmp_tab(k).transaction_id,
382 g_plan_id,
383 g_instance_id,
384 v_undo_parent_id,
385 net_res_Columns,
386 x_return_sts,
387 x_msg_count,
388 x_msg_data,
389 v_undo_id);
390
391 return v_undo_id;
392
393 end insert_undo_data;
394
395 Function insert_res_inst_undo_data(undo_type number,
396 v_trx_id number,
397 v_old_shift_date varchar2, v_new_shift_date varchar2,
398 v_old_shift_number varchar2, v_new_shift_number varchar2,
399 v_old_from_time varchar2, v_new_from_time varchar2,
400 v_old_to_time varchar2, v_new_to_time varchar2,
401 v_old_units varchar2, v_new_units varchar2,
402 v_undo_parent_id number default null) return number is
403 v_undo_id number;
404 net_res_columns msc_undo.changeRGType;
405 x_return_sts varchar2(100);
406 x_msg_count number;
407 x_msg_data varchar2(200);
408 begin
409 select msc_undo_summary_s.nextval
410 into v_undo_id
411 from dual;
412
413 if undo_type = 2 then -- update
414
415 i := 1;
416 net_res_columns(i).column_changed := 'SHIFT_DATE';
417 net_res_columns(i).column_changed_text := 'Shift Date';
418 net_res_columns(i).old_value := v_old_shift_date;
419 net_res_columns(i).column_type := 'DATE';
420 net_res_columns(i).new_value := v_new_shift_date;
421
422 i := i+1;
423 net_res_columns(i).column_changed := 'SHIFT_NUM';
424 net_res_columns(i).column_changed_text := 'Shift Number';
425 net_res_columns(i).old_value := to_char(v_old_shift_number);
426 net_res_columns(i).column_type := 'NUMBER';
427 net_res_columns(i).new_value := to_char(v_new_shift_number);
428
429 i := i+1;
430 net_res_columns(i).column_changed := 'FROM_TIME';
431 net_res_columns(i).column_changed_text := 'From Time';
432 net_res_columns(i).old_value := to_char(v_old_from_time);
433 net_res_columns(i).column_type := 'NUMBER';
434 net_res_columns(i).new_value := to_char(v_new_to_time);
435
436 i := i+1;
437 net_res_columns(i).column_changed := 'TO_TIME';
438 net_res_columns(i).column_changed_text := 'To Time';
439 net_res_columns(i).old_value := to_char(v_old_to_time);
440 net_res_columns(i).column_type := 'NUMBER';
441 net_res_columns(i).new_value := to_char(v_new_to_time);
442
443 i := i+1;
444 net_res_columns(i).column_changed := 'CAPACITY_UNITS';
445 net_res_columns(i).column_changed_text := 'Capacity Units';
446 net_res_columns(i).old_value := to_char(v_old_units);
447 net_res_columns(i).column_type := 'NUMBER';
448 net_res_columns(i).new_value := to_char(v_new_units);
449 end if;
450
451 msc_undo.store_undo(8, --means msc_net_resource_avail
452 undo_type, --2 is update , 1 is insert a record
453 v_trx_id,
454 g_plan_id,
455 g_instance_id,
456 v_undo_parent_id,
457 net_res_Columns,
458 x_return_sts,
459 x_msg_count,
463
460 x_msg_data,
461 v_undo_id);
462 return nvl(v_undo_parent_id,v_undo_id);
464 end insert_res_inst_undo_data;
465
466 ---------------------------------------------------------------------
467 -- to
468 ---------------------------------------------------------------------
469 PROCEDURE calculate_change IS
470 v_start_record number;
471 v_end_record number;
472 v_undo_id number;
473 v_undo_parent_id number;
474 BEGIN
475
476 IF g_resource_exist THEN
477 -- try to find which records in res_tab are affected by the change
478
479 -- try to find which record the change start date falls
480
481 j:=g_res_tab.FIRST;
482 IF (g_change_rec.from_time <
483 g_res_tab(j).from_time ) THEN
484 -- the change record starts before the range
485 v_start_record :=0;
486 ELSE
487 --find the first record whose start date is greater than change's start date
488 --then the previous record will be where the change starts
489 While (j is not null) and
490 ( g_change_rec.from_time >=
491 g_res_tab(j).from_time )
492 LOOP
493 j:=g_res_tab.next(j);
494 END LOOP;
495 IF j is null THEN
496 -- if j is null, then the change is on or outside the last record
497 i :=g_res_tab.LAST;
498 IF ( g_res_tab(i).to_time is null ) THEN
499 v_start_record :=g_res_tab.LAST;
500 v_end_record :=g_res_tab.LAST;
501 ELSE
502 IF (g_change_rec.from_time <=
503 g_res_tab(i).to_time ) THEN
504 v_start_record :=g_res_tab.LAST;
505 v_end_record :=g_res_tab.LAST;
506 ELSE
507 v_start_record :=g_res_tab.LAST+1;
508 v_end_record :=g_res_tab.LAST+1;
509
510 END IF;
511 END IF;
512 ELSE
513 -- otherwise, the change is inside the range
514 -- but it could be on a record or in a gap between two records
515 IF (g_change_rec.from_time <=
516 g_res_tab(j-1).to_time ) THEN
517 --change falls on the previos record
518 v_start_record := j-1;
519 ELSE
520 --change falls on the gap between record j-1 and record j
521 v_start_record := j-0.5;
522 END IF;
523 --go to the previous record to find where change ends
524 j:=j-1;
525 END IF;
526 END IF;
527
528 -- try to find which record the change end date fall
529
530 -- if the change does not have end date, the change extends till the end
531 -- but it could be on the last record, or outside the range
532 IF ( g_change_rec.to_time is null ) THEN
533 i :=g_res_tab.LAST;
534 IF ( g_res_tab(i).to_time is null ) THEN
535 -- falls on the last record
536 v_end_record :=g_res_tab.LAST;
537 ELSE
538 --falls outside the last record
539 v_end_record :=g_res_tab.LAST+1;
540 END IF;
541
542 ELSE
543 IF ( g_change_rec.to_time <=
544 g_res_tab(1).from_time ) THEN
545 -- the change ends before the first record
546 v_end_record :=0;
547 ELSE
548
549 While (j is not null) and
550 ( g_change_rec.to_time >
551 g_res_tab(j).from_time )
552 LOOP
553 j:=g_res_tab.next(j);
554 END LOOP;
555
556 IF j is null THEN
557 -- if j is null, then the change ends on or outside the last record
558 i :=g_res_tab.LAST;
559 IF ( g_res_tab(i).to_time is null ) THEN
560 v_end_record :=g_res_tab.LAST;
561 ELSE
562 IF (g_change_rec.to_time <=
563 g_res_tab(i).to_time ) THEN
564 v_end_record :=g_res_tab.LAST;
565 ELSE
566 v_end_record :=g_res_tab.LAST+1;
567 END IF;
568 END IF;
569
570 ELSE
571 IF (g_change_rec.to_time <=
572 g_res_tab(j-1).to_time ) THEN
573 -- change ends on the previous record
574 v_end_record := j-1;
575 ELSE
576 -- change ends in the gap between record j-1 and record j
577 v_end_record := j-0.5;
578 END IF;
579 END IF;
580 END IF;
581 END IF;
582
583 -- flush the records to tmp_tab
584 k:=0;
585 g_tmp_tab.delete;
586
587 IF g_change_rec.operation <> OP_SET THEN
588
589 IF ( v_start_record =0 ) THEN
590 IF g_change_rec.operation not in (OP_DEL, OP_DEL_DAY) THEN
591 add_new_record(1,false,false);
592 v_undo_id :=insert_undo_data(1); -- insert
593 IF (v_end_record <> 0) THEN
594 g_tmp_tab(k).to_time :=
595 g_res_tab(1).from_time;
596 END IF;
597
598 -- if add non working day, set the updated field as 1
599 -- so that when re-plan, it will be treated as work day
600
601 IF g_change_rec.operation = OP_ADD_DAY THEN
602 g_tmp_tab(k).updated :=1;
603 END IF;
604 END IF;
605 END IF;
606
607 j:=g_res_tab.FIRST;
608 While (j is not null)
609 LOOP
610
611 v_undo_parent_id := null;
612
613 IF (j < v_start_record) or (j > v_end_record) THEN
614 -- no change, just copy the old record
615 add_new_record(j, true,true);
616
617 ELSIF (j > v_start_record) and (j<v_end_record) THEN
618 -- the whole record is affected, change the qty
619 add_new_record(j, true,true);
620 IF g_change_rec.operation = OP_ADD THEN
621 g_tmp_tab(k).capacity_units :=
622 g_res_tab(j).capacity_units +
623 g_change_rec.capacity_units ;
627 g_change_rec.capacity_units ;
624 ELSIF g_change_rec.operation = OP_DEL THEN
625 g_tmp_tab(k).capacity_units :=
626 g_res_tab(j).capacity_units -
628
629 END IF;
630 v_undo_id :=insert_undo_data(2,j); -- update
631 ELSIF (j=v_start_record) and (j = v_end_record) THEN
632 -- need to cut the record into three records
633 IF (g_change_rec.from_time <>
634 g_res_tab(j).from_time ) THEN
635 -- need to change the date for the first record
636 add_new_record(j, true,true); -- retain old tran_id
637 g_tmp_tab(k).to_time:=
638 g_change_rec.from_time;
639 v_undo_parent_id :=insert_undo_data(2,j); --update
640
641 END IF;
642
643 -- add a new record
644 -- delete work day and add non working day would be caught
645 -- here only if it falls inside the range and not in a gap,
646 -- because v_start_record will always = v_end_record in these cases
647
648 IF g_change_rec.operation <> OP_DEL_DAY THEN
649 if v_undo_parent_id is not null then
650 add_new_record(j,false,false);
651 else -- retain old transaction_id
652 add_new_record(j,false,true);
653 end if;
654 IF g_change_rec.operation = OP_ADD THEN
655 g_tmp_tab(k).capacity_units :=
656 g_res_tab(j).capacity_units +
657 g_change_rec.capacity_units ;
658 ELSIF g_change_rec.operation = OP_DEL THEN
659 g_tmp_tab(k).capacity_units :=
660 g_res_tab(j).capacity_units -
661 g_change_rec.capacity_units ;
662
663 -- don't add onto the quantity of the original record
664 ELSIF g_change_rec.operation = OP_ADD_DAY THEN
665 g_tmp_tab(k).updated :=1;
666
667 END IF;
668 if v_undo_parent_id is not null then
669 v_undo_id :=
670 insert_undo_data(1,j,v_undo_parent_id); -- insert
671 else
672 v_undo_parent_id:=insert_undo_data(2,j); -- update
673 end if;
674 END IF;
675
676 IF (g_change_rec.to_time <>
677 g_res_tab(j).to_time ) or
678 ( g_res_tab(j).to_time is null and
679 g_change_rec.to_time is not null) THEN
680 -- need to change the date for the third record
681 if v_undo_parent_id is not null then
682 add_new_record(j,true,false);
683 else -- retain old transaction_id
684 add_new_record(j,true,true);
685 end if;
686 g_tmp_tab(k).from_time:=
687 g_change_rec.to_time;
688 if v_undo_parent_id is not null then
689 v_undo_id :=
690 insert_undo_data(1,j,v_undo_parent_id); -- insert
691 else
692 v_undo_parent_id:=insert_undo_data(2,j); -- update
693 end if;
694 END IF;
695
696 ELSIF (j=v_start_record) and (j <> v_end_record) THEN
697 -- need to cut the record
698 IF (g_change_rec.from_time <>
699 g_res_tab(j).from_time ) THEN
700 -- need to change the date
701 add_new_record(j,true,true);
702 g_tmp_tab(k).to_time:=
703 g_change_rec.from_time;
704 v_undo_parent_id :=insert_undo_data(2,j); --update
705 END IF;
706
707 -- and add a new record
708 if v_undo_parent_id is not null then
709 add_new_record(j,false,false);
710 else -- retain old transaction_id
711 add_new_record(j,false,true);
712 end if;
713 g_tmp_tab(k).to_time:=
714 g_res_tab(j).to_time;
715 IF g_change_rec.operation = OP_ADD THEN
716 g_tmp_tab(k).capacity_units :=
717 g_res_tab(j).capacity_units +
718 g_change_rec.capacity_units ;
719 ELSIF g_change_rec.operation = OP_DEL THEN
720 g_tmp_tab(k).capacity_units :=
721 g_res_tab(j).capacity_units -
722 g_change_rec.capacity_units ;
723 END IF;
724 if v_undo_parent_id is not null then
725 v_undo_id :=
726 insert_undo_data(1,j,v_undo_parent_id); -- insert
727 else
728 v_undo_parent_id:=insert_undo_data(2,j); -- update
729 end if;
730
731 ELSIF (j=v_end_record) and (j <> v_start_record) THEN
732 -- need to cut the record
733 -- add a new record
734 add_new_record(j,false,true);
735 g_tmp_tab(k).from_time:=
736 g_res_tab(j).from_time;
737 IF g_change_rec.operation = OP_ADD THEN
738 g_tmp_tab(k).capacity_units :=
739 g_res_tab(j).capacity_units +
743 g_res_tab(j).capacity_units -
740 g_change_rec.capacity_units ;
741 ELSIF g_change_rec.operation = OP_DEL THEN
742 g_tmp_tab(k).capacity_units :=
744 g_change_rec.capacity_units ;
745 END IF;
746 v_undo_parent_id :=insert_undo_data(2,j);
747 IF (g_change_rec.to_time <>
748 g_res_tab(j).to_time ) or
749 (g_change_rec.to_time is not null and
750 g_res_tab(j).to_time is null )THEN
751 -- need to change the date
752 add_new_record(j,true,false);
753 g_tmp_tab(k).from_time:=
754 g_change_rec.to_time;
755 v_undo_id :=insert_undo_data(1,j,v_undo_parent_id);
756 END IF;
757 END IF;
758
759 -- if change starts or ends in the gap, need to insert new row
760 IF g_change_rec.operation not in (OP_DEL_DAY, OP_DEL) THEN
761
762 IF (v_start_record >j ) and (v_start_record <j+1 ) THEN
763 add_new_record(j,false,false);
764 v_undo_id :=insert_undo_data(1);
765 IF (g_change_rec.to_time >=
766 g_res_tab(j+1).from_time or
767 g_change_rec.to_time is null) THEN
768 --the change extends over the gap, need to change the end date
769 g_tmp_tab(k).to_time:=
770 g_res_tab(j+1).from_time;
771 END IF;
772
773 ELSIF (v_end_record >j ) and (v_end_record <j+1 ) THEN
774 add_new_record(j,false,false);
775 v_undo_id :=insert_undo_data(1);
776 IF (g_change_rec.from_time <=
777 g_res_tab(j).to_time ) THEN
778 --the change extends over the gap, need to change start date
779 g_tmp_tab(k).from_time:=
780 g_res_tab(j).to_time;
781 END IF;
782 END IF;
783 END IF;
784
785 j:=g_res_tab.next(j);
786 END LOOP;
787
788 -- if the record falls outside the original range, add a new row
789 i := g_res_tab.LAST;
790 IF (v_end_record = i+1) THEN
791 IF g_change_rec.operation not in (OP_DEL_DAY, OP_DEL) THEN
792 add_new_record(i,false,false);
793 v_undo_id :=insert_undo_data(1,j,v_undo_parent_id);
794 IF (v_start_record <> i +1 ) THEN
795 g_tmp_tab(k).from_time:=
796 g_res_tab(i).to_time;
797 END IF;
798 IF g_change_rec.operation = OP_ADD_DAY THEN
799 g_tmp_tab(k).updated :=1;
800 END IF;
801 END IF;
802 END IF;
803
804 ELSIF g_change_rec.operation= OP_SET THEN
805
806 i := g_res_tab.LAST;
807
808 IF (v_start_record = 0) THEN
809
810 --if change falls before the range, add a row, go to the end record
811 --cut record if needed, then go to the next record
812 add_new_record(1,false,false);
813 v_undo_id :=insert_undo_data(1);
814 -- if the set record ends outside the range, don't have to loop
815 IF (v_end_record = i+1) THEN
816 j:='';
817 ELSIF (v_end_record=0) THEN
818 -- the change ends before the range, loop from record1
819 j:=g_res_tab.FIRST;
820 ELSIF (v_end_record > trunc(v_end_record)) THEN
821 -- change falls on a gap, go to the record after the gap
822 j:=trunc(v_end_record)+1;
823 -- if the set record ends outside the range, don't have to loop
824 ELSIF (v_end_record < i+1) THEN
825 -- go to where the set record ends and add row if needed
826 j:=v_end_record;
827
828 IF (g_change_rec.to_time <
829 g_res_tab(j).to_time ) or
830 (g_res_tab(j).to_time is null and
831 g_change_rec.to_time is not null) THEN
832
833 -- need to add row for the date change
834 add_new_record(j,true,false);
835 g_tmp_tab(k).from_time :=
836 g_change_rec.to_time;
837 v_undo_id :=insert_undo_data(1);
838 END IF;
839 --go to the next record, and ready for loop
840 j:=j+1;
841 END IF;
842 ELSE
843 j:=g_res_tab.FIRST;
844 END IF;
845
846 IF j < i+1 THEN
847 While ( j is not null ) LOOP
848 IF (j < v_start_record) or (j > v_end_record) THEN
849 -- no change
850 add_new_record(j,true,true);
851 ELSIF (j=v_start_record) THEN
852 v_undo_parent_id := null;
853 IF (g_change_rec.from_time >
854 g_res_tab(j).from_time ) THEN
855 -- need to insert row with date change only first
856 add_new_record(j,true,true);
857 g_tmp_tab(k).to_time :=
858 g_change_rec.from_time;
859 v_undo_parent_id :=insert_undo_data(2,j);
860 END IF;
861
862 -- add new row
863 if v_undo_parent_id is not null then
864 add_new_record(j,false,false);
865 v_undo_id :=
866 insert_undo_data(1,j,v_undo_parent_id); -- insert
867 else -- retain old transaction_id
868 add_new_record(j,false,true);
869 v_undo_parent_id := insert_undo_data(2,j);
870 end if;
871
872 IF (v_end_record > trunc(v_end_record)) THEN
873 -- change ends on a gap,
874 j:=trunc(v_end_record);
875 ELSIF (v_end_record < i+1) THEN
876 -- go the where the set record ends, and add row if needed
877 j:=v_end_record;
878 IF (g_change_rec.to_time <
879 g_res_tab(j).to_time ) or
883 -- need to add row for the date change
880 (g_change_rec.to_time is not null and
881 g_res_tab(j).to_time is null) THEN
882
884 add_new_record(j,true,false);
885 g_tmp_tab(k).from_time :=
886 g_change_rec.to_time;
887 v_undo_id :=
888 insert_undo_data(1,j,v_undo_parent_id); -- insert
889 END IF;
890 END IF;
891
892 END IF;
893
894 -- if change starts on a gap
895 IF (v_start_record > j) and (v_start_record < j+1) THEN
896 -- add new row
897 add_new_record(j,false,true);
898 v_undo_parent_id :=insert_undo_data(2,j);
899 IF (v_end_record > trunc(v_end_record)) THEN
900 -- change ends on a gap,
901 j:=trunc(v_end_record);
902 ELSIF (v_end_record < i+1) THEN
903 -- go to where the set record ends, and add row if needed
904 j:=v_end_record;
905 IF (g_change_rec.to_time <
906 g_res_tab(j).to_time ) or
907 (g_change_rec.to_time is not null and
908 g_res_tab(j).to_time is null) THEN
909
910 -- need to add row for the date change
911 add_new_record(j,true,false);
912 g_tmp_tab(k).from_time :=
913 g_change_rec.to_time;
914 v_undo_id :=
915 insert_undo_data(1,j,v_undo_parent_id); -- insert
916 END IF;
917 END IF;
918 END IF;
919
920 j:=g_res_tab.next(j);
921 END LOOP;
922 END IF;
923
924 -- if the set record starts outside the range
925 i:=g_res_tab.LAST;
926 IF (v_start_record = i+1 ) THEN
927 -- need to add row for the date change
928 add_new_record(i,false,false);
929 v_undo_id :=insert_undo_data(1);
930 END IF;
931 END IF;
932
933 ELSE --user enters a resource which is not in msc_net_resource_avail table
934 g_tmp_tab.delete;
935 add_new_record(0,false,false);
936 v_undo_id :=insert_undo_data(1);
937 END IF;
938
939 g_res_tab :=g_tmp_tab;
940
941 END calculate_change;
942
943 FUNCTION get_inst_trx_id RETURN NUMBER IS
944 v_trx_id NUMBER;
945 BEGIN
946 select msc_net_res_inst_avail_s.nextval
947 into v_trx_id
948 from dual;
949 return v_trx_id;
950 END;
951 ------------------------------------------------------------------------
952 --to update msc_net_resource_avail table
953 -----------------------------------------------------------------------------
954 PROCEDURE update_table IS
955 CURSOR bucket IS
956 SELECT mpb.bkt_start_date, mpb.bkt_end_date
957 FROM msc_plan_buckets mpb,
958 msc_plans mp
959 where mp.plan_id = g_plan_id
960 and mp.plan_id = mpb.plan_id
961 and mp.organization_id = mpb.organization_id
962 and mp.sr_instance_id = mpb.sr_instance_id
963 and mpb.curr_flag =1
964 and g_shift_date between mpb.bkt_start_date and mpb.bkt_end_date;
965
966 m INTEGER;
967 v_start_date DATE;
968 v_end_date DATE;
969 v_capacity_units NUMBER;
970
971 l_units number;
972 l_res_inst_trx_id number;
973 l_res_inst_units number;
974 l_res_inst_undo_id number;
975 BEGIN
976 if g_resource_exist then
977
978 for m in 1..g_res_tab.LAST loop
979 /*
980 dbms_output.put_line('del for tran='||to_char(g_res_tab(m).transaction_id));
981 dbms_output.put_line('capacity_units='||to_char(g_res_tab(m).capacity_units));
982 */
983 delete from msc_net_resource_avail
984 where plan_id = g_plan_id
985 and transaction_id = g_res_tab(m).transaction_id
986 and g_res_tab(m).from_time <> g_res_tab(m).to_time;
987 end loop;
988
989 update msc_net_resource_avail
990 set capacity_units = -1,
991 status =0,
992 applied =2,
993 from_time = from_time+1,
994 to_time = to_time +1
995 where plan_id = g_plan_id
996 and organization_id = g_org_id
997 and sr_instance_id = g_instance_id
998 AND department_id = g_department_id
999 AND resource_id = g_resource_id
1000 AND nvl(parent_id, 0) <> -1
1001 AND shift_date = g_shift_date
1002 AND decode(resource_id, -1,-1,shift_num) =
1003 decode(resource_id,-1,-1,g_shift_number) ;
1004 end if;
1005
1006 if (g_res_inst_set_data) then
1007 For m in 1 .. g_res_inst_tab.LAST LOOP
1008 update msc_net_res_inst_avail
1009 set capacity_units = 0,
1010 status =0,
1011 applied =2
1012 where plan_id = g_plan_id
1013 and inst_transaction_id = g_res_inst_tab(m).transaction_id
1014 and g_res_inst_tab(m).from_time <> g_res_inst_tab(m).to_time;
1015 end loop;
1016 end if;
1017
1018
1019 For m in 1 .. g_res_tab.LAST LOOP
1020
1021 IF g_res_tab(m).from_time <> g_res_tab(m).to_time THEN
1022 /*
1023 dbms_output.put_line('insert for tran='||to_char(g_res_tab(m).transaction_id));
1024 dbms_output.put_line('date='||g_shift_date);
1025 dbms_output.put_line('from='||to_char(g_res_tab(m).from_time));
1026 dbms_output.put_line('to='||to_char(g_res_tab(m).to_time));
1027 dbms_output.put_line('simulation='||to_char(g_res_tab(m).simulation_set));
1028 dbms_output.put_line('capacity_units='||to_char(g_res_tab(m).capacity_units));
1029 */
1030 l_units := greatest(g_res_tab(m).capacity_units,0);
1034 if (g_units = 0) then
1031 --dbms_output.put_line('capacity_units 1='||l_units);
1032 if (g_res_inst_set_data) then
1033 if (g_from_time = g_res_tab(m).from_time and g_to_time = g_res_tab(m).to_time) then
1035 l_units := greatest(g_res_tab(m).capacity_units,0);
1036 l_res_inst_units := 0;
1037 else -- g_units = 1
1038 l_units := greatest(g_res_tab(m).capacity_units+1,0);
1039 l_res_inst_units := 1;
1040 end if;
1041 else
1042 begin
1043 l_res_inst_units := g_res_inst_tab(m).capacity_units;
1044 exception
1045 when others then
1046 if (l_units = 0) then
1047 l_res_inst_units := 0;
1048 else
1049 l_res_inst_units := 1;
1050 end if;
1051 end;
1052 end if;
1053 end if;
1054 --dbms_output.put_line('capacity_units 2 ='||l_units);
1055
1056 INSERT INTO msc_net_resource_avail
1057 (plan_id,
1058 parent_id,
1059 transaction_id,
1060 organization_id,
1061 sr_instance_id,
1062 department_id,
1063 resource_id,
1064 shift_date,
1065 shift_num,
1066 from_time,
1067 to_time,
1068 capacity_units,
1069 simulation_set,
1070 status,
1071 applied,
1072 updated,
1073 last_update_date,
1074 last_updated_by,
1075 creation_date,
1076 created_by,
1077 last_update_login)
1078 VALUES
1079 (g_plan_id,
1080 -2,
1081 g_res_tab(m).transaction_id,
1082 g_org_id,
1083 g_instance_id,
1084 g_department_id,
1085 g_resource_id,
1086 g_shift_date,
1087 decode(g_resource_id, -1, null,g_shift_number),
1088 g_res_tab(m).from_time,
1089 g_res_tab(m).to_time,
1090 l_units,
1091 g_res_tab(m).simulation_set,
1092 g_res_tab(m).status,
1093 g_res_tab(m).applied,
1094 g_res_tab(m).updated,
1095 g_res_tab(m).last_update_date,
1096 g_res_tab(m).last_updated_by,
1097 g_res_tab(m).creation_date,
1098 g_res_tab(m).created_by,
1099 g_res_tab(m).last_update_login);
1100
1101 /*
1102 dbms_output.put_line('RESOURCE -inst trx/shift/from/to/units '|| g_res_tab(m).transaction_id
1103 ||' - '||g_shift_number||' - '||g_res_tab(m).from_time||' - '||g_res_tab(m).to_time||' - '|| l_units);
1104 */
1105
1106 if (g_res_inst_set_data) then
1107 if (m <= g_res_inst_tab.last) then
1108 /*
1109 dbms_output.put_line('INSTANCE res-inst trx/shift/from/to/units '||g_res_inst_tab(m).transaction_id
1110 ||' - '||g_shift_number||' - '||g_res_tab(m).from_time||' - '||g_res_tab(m).to_time||' - '|| l_res_inst_units);
1111 */
1112
1113 update msc_net_res_inst_avail
1114 set capacity_units = l_res_inst_units,
1115 shift_date = g_shift_date,
1116 shift_num = g_shift_number,
1117 from_time = g_res_tab(m).from_time,
1118 to_time = g_res_tab(m).to_time,
1119 status =0,
1120 applied =2
1121 where plan_id = g_plan_id
1122 and inst_transaction_id = g_res_inst_tab(m).transaction_id;
1123
1124 if (sql%rowcount <> 0) then
1125 l_res_inst_undo_id := insert_res_inst_undo_data(2,
1126 g_res_inst_tab(m).transaction_id,
1127 fnd_date.date_to_canonical(g_res_inst_tab(m).shift_date),
1128 fnd_date.date_to_canonical(g_shift_date),
1129 g_res_inst_tab(m).shift_number, g_shift_number,
1130 g_res_inst_tab(m).from_time, g_res_tab(m).from_time,
1131 g_res_inst_tab(m).to_time, g_res_tab(m).to_time,
1132 g_res_inst_tab(m).capacity_units, l_res_inst_units,
1133 null);
1134 end if;
1135 end if;
1136
1137 if (sql%rowcount = 0 or m > g_res_inst_tab.last) then
1138 l_res_inst_trx_id := get_inst_trx_id;
1139 insert into msc_net_res_inst_avail (
1140 inst_transaction_id,
1141 last_update_date, last_updated_by, creation_date, created_by, last_update_login,
1142 plan_id, sr_instance_id, organization_id, department_id, resource_id,
1143 res_instance_id, serial_number, shift_num, shift_date,
1144 from_time, to_time, simulation_set, capacity_units)
1145 values (
1146 l_res_inst_trx_id,
1147 sysdate, fnd_global.user_id, sysdate,fnd_global.user_id, fnd_global.user_id,
1148 g_plan_id, g_instance_id, g_org_id, g_department_id, g_resource_id, g_res_inst_id,
1149 g_serial_number, g_shift_number, g_shift_date,
1150 g_res_tab(m).from_time, g_res_tab(m).to_time, g_res_tab(m).simulation_set, l_res_inst_units);
1151
1152 --dbms_output.put_line('res-inst insert '||l_res_inst_trx_id||' - '||g_shift_number
1153 --||' - '||g_res_tab(m).from_time||' - '||g_res_tab(m).to_time||' - '|| l_res_inst_units);
1154 l_res_inst_undo_id := insert_res_inst_undo_data(1,
1155 l_res_inst_trx_id,
1156 null, null,
1157 null, null,
1158 null, null,
1159 null, null,
1160 null, null,
1161 null);
1162
1163 end if;
1164
1165 end if;
1166 END IF;
1167 END LOOP;
1168
1169 -- update the parent record
1170
1174
1171 OPEN bucket;
1172 FETCH bucket into v_start_date, v_end_date;
1173 CLOSE bucket;
1175 v_capacity_units :=0;
1176 begin
1177 select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
1178 (to_time+86400-from_time)
1179 )/3600*capacity_units),6)
1180 into v_capacity_units
1181 from msc_net_resource_avail
1182 where plan_id = g_plan_id
1183 and organization_id = g_org_id
1184 and sr_instance_id = g_instance_id
1185 AND department_id = g_department_id
1186 AND resource_id = g_resource_id
1187 and nvl(parent_id, 0) <> -1
1188 and capacity_units >0
1189 and shift_date between v_start_date and v_end_date;
1190 exception when no_data_found then
1191 v_capacity_units :=0;
1192 end;
1193
1194 v_capacity_units := nvl(v_capacity_units,0);
1195
1196 -- update the resource units for parent record
1197 update msc_net_resource_avail
1198 set capacity_units = v_capacity_units,
1199 status =0,
1200 applied =2,
1201 updated =2
1202 where plan_id = g_plan_id
1203 and organization_id = g_org_id
1204 and sr_instance_id = g_instance_id
1205 AND department_id = g_department_id
1206 AND resource_id = g_resource_id
1207 and shift_date = v_start_date
1208 and parent_id =-1;
1209
1210 -- update the parent_id for the added record
1211 update msc_net_resource_avail
1212 set parent_id = g_res_tab(1).parent_id
1213 where plan_id = g_plan_id
1214 and organization_id = g_org_id
1215 and sr_instance_id = g_instance_id
1216 AND department_id = g_department_id
1217 AND resource_id = g_resource_id
1218 and parent_id = -2 ;
1219
1220 END update_table;
1221
1222 ------------------------------------------------------------------------
1223 --to get transaction_id from msc_net_resource_avail table
1224 -----------------------------------------------------------------------------
1225 FUNCTION get_transaction_id RETURN NUMBER IS
1226 v_transaction_id NUMBER;
1227 BEGIN
1228 select msc_net_resource_avail_s.nextval
1229 into v_transaction_id
1230 from dual;
1231
1232 return v_transaction_id;
1233 END;
1234
1235 ------------------------------------------------------------------------
1236 --to get transaction_id from msc_net_resource_avail table
1237 -----------------------------------------------------------------------------
1238 PROCEDURE add_new_record(m NUMBER, retain_old boolean default false,
1239 retain_id boolean default false) IS
1240
1241 l_units number;
1242 BEGIN
1243 k :=k +1;
1244 if retain_id then
1245 g_tmp_tab(k).transaction_id := g_res_tab(m).transaction_id;
1246 else
1247 g_tmp_tab(k).transaction_id := get_transaction_id;
1248 end if;
1249 g_tmp_tab(k).parent_id := g_res_tab(m).parent_id;
1250 g_tmp_tab(k).aggregate_resource_id :=
1251 g_res_tab(m).aggregate_resource_id;
1252 g_tmp_tab(k).simulation_set := g_res_tab(m).simulation_set;
1253 if retain_old then
1254 g_tmp_tab(k).from_time := g_res_tab(m).from_time;
1255 g_tmp_tab(k).to_time := g_res_tab(m).to_time;
1256 g_tmp_tab(k).capacity_units := g_res_tab(m).capacity_units;
1257 else
1258 g_tmp_tab(k).from_time := g_change_rec.from_time;
1259 g_tmp_tab(k).to_time := g_change_rec.to_time;
1260 if (g_res_inst_set_data) then
1261 if (g_change_rec.capacity_units = 0 ) then
1262 l_units := -1;
1263 else
1264 l_units := 1;
1265 end if;
1266 g_tmp_tab(k).capacity_units := greatest(g_res_tab(m).capacity_units + l_units,0);
1267 else
1268 g_tmp_tab(k).capacity_units := g_change_rec.capacity_units;
1269 end if;
1270 end if;
1271 g_tmp_tab(k).status := 0;
1272 g_tmp_tab(k).applied := 2;
1273 -- g_tmp_tab(k).updated := 2;
1274 g_tmp_tab(k).last_update_date := sysdate;
1275 g_tmp_tab(k).last_updated_by := g_change_rec.last_updated_by;
1276 g_tmp_tab(k).creation_date := sysdate;
1277 g_tmp_tab(k).created_by := g_change_rec.last_updated_by;
1278
1279 /*
1280 dbms_output.put_line('add_new_record tmp-trx-from-to-units '||g_tmp_tab(k).transaction_id||' - '||
1281 g_tmp_tab(k).from_time||' - '||g_tmp_tab(k).to_time||' - '||g_tmp_tab(k).capacity_units);
1282
1283 dbms_output.put_line('add_new_record res-trx-from-to-units '||g_res_tab(m).transaction_id||' - '||
1284 g_res_tab(m).from_time||' - '||g_res_tab(m).to_time||' - '||g_res_tab(m).capacity_units);
1285 */
1286 END;
1287
1288
1289 -------------------------------------------------------------------
1290 -- to group the child record to parent record
1291 -------------------------------------------------------------------
1292 PROCEDURE aggregate_child_records(v_plan_id NUMBER) IS
1293
1294 CURSOR net_resource IS
1295 SELECT res.department_id, res.resource_id,
1296 res.organization_id, res.sr_instance_id
1297 FROM msc_department_resources res
1298 WHERE plan_id = v_plan_id;
1299
1300 TYPE resource_table IS RECORD
1301 ( dept_id NUMBER,
1302 res_id NUMBER,
1303 org_id NUMBER,
1304 instance_id NUMBER);
1305
1306 v_res_table resource_table;
1307
1308 v_dept_id number:=0;
1309 v_res_id number:=0;
1310 v_org_id number:=0;
1311 v_instance_id number:=0;
1312
1313 BEGIN
1314
1315 -- loop thru each dept/resource
1316
1317 open net_resource;
1318 LOOP
1319 FETCH net_resource into v_res_table;
1320 EXIT WHEN net_resource%NOTFOUND;
1321
1325 v_res_id := v_res_table.res_id;
1322 -- for each new dept/resource
1323
1324 v_dept_id := v_res_table.dept_id;
1326 v_org_id := v_res_table.org_id;
1327 v_instance_id := v_res_table.instance_id;
1328
1329 -- delete old parent record first
1330
1331 delete from msc_net_resource_avail
1332 where plan_id = v_plan_id
1333 AND organization_id = v_org_id
1334 AND sr_instance_id =v_instance_id
1335 AND department_id = v_dept_id
1336 AND resource_id = v_res_id
1337 and parent_id =-1;
1338
1339 aggregate_one_resource(v_plan_id, v_org_id, v_instance_id,
1340 v_dept_id, v_res_id);
1341 END LOOP;
1342 close net_resource;
1343 END;
1344
1345 PROCEDURE aggregate_some_resources(v_plan_id NUMBER,
1346 p_org_instance_list varchar2,
1347 p_dept_class_list VARCHAR2,
1348 p_res_group_list VARCHAR2,
1349 p_dept_list varchar2,
1350 p_res_list varchar2,
1351 p_line_list VARCHAR2) IS
1352 where_statement varchar2(1000);
1353 TYPE res_cursor_type IS REF CURSOR;
1354 res_cursor res_cursor_type;
1355 sql_statement varchar2(1500);
1356
1357 TYPE resource_table IS RECORD
1358 ( dept_id NUMBER,
1359 res_id NUMBER,
1360 org_id NUMBER,
1361 instance_id NUMBER);
1362
1363 v_res_table resource_table;
1364
1365 BEGIN
1366
1367
1368
1369 IF p_dept_list IS NOT NULL THEN
1370 where_statement := where_statement ||
1371 ' and department_id in (' || p_dept_list || ')';
1372 ELSIF p_dept_class_list IS NOT NULL THEN
1373 where_statement := where_statement ||
1374 ' and department_id in (select distinct department_id ' ||
1375 ' from msc_department_resources where NVL(department_class,''@@@'') '||
1376 ' in (' || p_dept_class_list || ') and plan_id = '
1377 ||to_char(v_plan_id)||
1378 ' and (sr_instance_id, organization_id) in ('||p_org_instance_list ||'))';
1379 ELSIF p_res_group_list IS NOT NULL THEN
1380 where_statement := where_statement ||
1381 ' and (department_id, resource_id) in (select '||
1382 ' department_id, resource_id from msc_department_resources where ' ||
1383 ' NVL(resource_group_name,''@@@'') in ('
1384 || p_res_group_list || ') and '||
1385 ' plan_id = '||to_char(v_plan_id)||
1386 ' and (sr_instance_id, organization_id) in ('||
1387 p_org_instance_list ||'))';
1388 END IF;
1389 IF p_line_list IS NOT NULL THEN
1390 where_statement := where_statement ||
1391 ' and department_id IN (' || p_line_list || ')';
1392 END IF;
1393 IF p_res_list IS NOT NULL THEN
1394 where_statement := where_statement ||
1395 ' and resource_id IN (' || p_res_list || ')';
1396 END IF;
1397
1398 where_statement := where_statement ||
1399 ' ORDER BY organization_id, sr_instance_id, department_id, resource_id';
1400 if p_org_instance_list is not null then
1401 sql_statement :=
1402 'SELECT distinct department_id, resource_id, '||
1403 'organization_id, sr_instance_id '||
1404 'FROM msc_net_resource_avail '||
1405 'WHERE plan_id = '||to_char(v_plan_id) ||
1406 ' AND nvl(parent_id, 0) <> -1 ' ||
1407 ' AND (sr_instance_id, organization_id) in ('||
1408 p_org_instance_list ||')' || where_statement;
1409 else
1410 sql_statement :=
1411 'SELECT distinct department_id, resource_id, '||
1412 'organization_id, sr_instance_id '||
1413 'FROM msc_net_resource_avail '||
1414 'WHERE plan_id = '||to_char(v_plan_id) ||
1415 ' AND nvl(parent_id, 0) <> -1 ' || where_statement;
1416 end if;
1417
1418
1419 OPEN res_cursor FOR sql_statement;
1420 LOOP
1421 FETCH res_cursor INTO v_res_table;
1422 EXIT WHEN res_cursor%NOTFOUND;
1423 aggregate_one_resource(v_plan_id, v_res_table.org_id,
1424 v_res_table.instance_id,v_res_table.dept_id,
1425 v_res_table.res_id);
1426 END LOOP;
1427 CLOSE res_cursor;
1428
1429 END;
1430
1431 PROCEDURE aggregate_one_resource(v_plan_id NUMBER,
1432 p_org_id NUMBER,
1433 p_instance_id NUMBER,
1434 p_dept_id NUMBER,
1435 p_res_id NUMBER) IS
1436 CURSOR bucket IS
1437 SELECT mpb.bkt_start_date, mpb.bkt_end_date
1438 FROM msc_plan_buckets mpb,
1439 msc_plans mp
1440 WHERE mp.plan_id = v_plan_id
1441 and mp.plan_id = mpb.plan_id
1442 and mp.sr_instance_id = mpb.sr_instance_id
1443 and mp.organization_id = mpb.organization_id
1444 and mpb.curr_flag =1
1445 order by mpb.bucket_index;
1446
1447 v_new_capacity_units number;
1448 i number;
1449 v_transaction_id number;
1450
1451 TYPE BucketRecTyp IS RECORD (
1452 start_date DATE,
1453 end_date DATE);
1454
1455 TYPE BucketTabTyp IS TABLE OF BucketRecTyp INDEX BY BINARY_INTEGER;
1456 v_bucket BucketTabTyp;
1457
1458 dummy number;
1459
1460 CURSOR parent_record IS
1461 select 1
1462 from msc_net_resource_avail
1463 where plan_id = v_plan_id
1464 and sr_instance_id = p_instance_id
1465 and organization_id = p_org_id
1466 and department_id = p_dept_id
1467 and resource_id = p_res_id
1468 and parent_id =-1
1469 and rownum <2;
1470
1474 )/3600*capacity_units)
1471 CURSOR time_record(v_start_date DATE, v_end_date DATE) IS
1472 select sum(decode(sign(to_time-from_time),-1,(to_time+86400 - from_time),
1473 (to_time-from_time)
1475 from msc_net_resource_avail
1476 where plan_id = v_plan_id
1477 and sr_instance_id = p_instance_id
1478 and organization_id = p_org_id
1479 AND department_id = p_dept_id
1480 AND resource_id = p_res_id
1481 and capacity_units >0
1482 and nvl(parent_id,0) <> -1
1483 and trunc(shift_date) between trunc(v_start_date)
1484 and trunc(v_end_date);
1485
1486 v_agg_resource number;
1487 CURSOR agg_resource IS
1488 SELECT aggregate_resource_flag
1489 from msc_department_resources
1490 where plan_id = v_plan_id
1491 and sr_instance_id = p_instance_id
1492 and organization_id = p_org_id
1493 AND department_id = p_dept_id
1494 AND resource_id = p_res_id;
1495 /*
1496 CURSOR agg_record(v_start_date DATE, v_end_date DATE) IS
1497 select sum(capacity_units)
1498 from msc_net_resource_avail
1499 where plan_id = v_plan_id
1500 and sr_instance_id = p_instance_id
1501 and organization_id = p_org_id
1502 AND department_id = p_dept_id
1503 AND resource_id = p_res_id
1504 and capacity_units >0
1505 and trunc(shift_date) between trunc(v_start_date)
1506 and trunc(v_end_date);
1507 */
1508
1509 BEGIN
1510
1511 -- if it is an aggregate resource, don't create parent record
1512 OPEN agg_resource;
1513 FETCH agg_resource INTO v_agg_resource;
1514 CLOSE agg_resource;
1515
1516 IF nvl(v_agg_resource,2) =2 THEN
1517 -- check if the parent record is created already
1518
1519 OPEN parent_record;
1520 FETCH parent_record into dummy;
1521 CLOSE parent_record;
1522 IF dummy is null THEN
1523
1524 -- populate the bucket dates
1525 i :=1;
1526 open bucket;
1527 LOOP
1528 FETCH bucket into v_bucket(i);
1529 EXIT WHEN bucket%NOTFOUND;
1530 i := i+1;
1531 END LOOP;
1532 close bucket;
1533
1534 For i in 1 .. v_bucket.COUNT LOOP
1535 -- calculate the new capacity units for each bucket
1536 /*
1537 if v_agg_resource = 1 then
1538 OPEN agg_record(v_bucket(i).start_date, v_bucket(i).end_date);
1539 FETCH agg_record into v_new_capacity_units;
1540 CLOSE agg_record;
1541
1542 else
1543 */
1544 OPEN time_record(v_bucket(i).start_date, v_bucket(i).end_date);
1545 FETCH time_record into v_new_capacity_units;
1546 CLOSE time_record;
1547
1548 v_new_capacity_units:=nvl(v_new_capacity_units,0);
1549 -- end if;
1550
1551 -- we will insert one for each bucket, even the resource_units is 0
1552
1553 -- if nvl(v_new_capacity_units,0) <>0 then
1554
1555 select msc_net_resource_avail_s.nextval
1556 into v_transaction_id
1557 from dual;
1558
1559 -- insert parent record
1560
1561 insert into msc_net_resource_avail
1562 ( TRANSACTION_ID,
1563 parent_id,
1564 PLAN_ID ,
1565 ORGANIZATION_ID,
1566 SR_INSTANCE_ID ,
1567 DEPARTMENT_ID ,
1568 RESOURCE_ID ,
1569 SHIFT_DATE ,
1570 CAPACITY_UNITS ,
1571 LAST_UPDATE_DATE ,
1572 LAST_UPDATED_BY ,
1573 CREATION_DATE ,
1574 CREATED_BY
1575 )
1576 values (
1577 v_transaction_id,
1578 -1,
1579 v_plan_id,
1580 p_org_id,
1581 p_instance_id,
1582 p_dept_id,
1583 p_res_id,
1584 v_bucket(i).start_date,
1585 v_new_capacity_units,
1586 sysdate,
1587 1,
1588 sysdate,
1589 1);
1590
1591 -- now update the parent_id for the child records
1592 update msc_net_resource_avail
1593 set parent_id = v_transaction_id
1594 where plan_id = v_plan_id
1595 and sr_instance_id = p_instance_id
1596 and organization_id = p_org_id
1597 AND department_id = p_dept_id
1598 AND resource_id = p_res_id
1599 and capacity_units >=0
1600 AND nvl(parent_id,0) <> -1
1601 and trunc(shift_date) between trunc(v_bucket(i).start_date)
1602 and trunc(v_bucket(i).end_date);
1603 -- end if;
1604 END LOOP;
1605 commit;
1606 END IF;
1607 END IF;
1608
1609 END;
1610
1611 PROCEDURE refresh_parent_record(p_plan_id number,
1612 p_instance_id number,
1613 p_transaction_id number) IS
1614 cursor c_net_res_avail is
1615 select organization_id,
1616 department_id,
1617 resource_id,
1618 shift_date
1619 from msc_net_resource_avail
1620 where plan_id = p_plan_id
1621 and transaction_id = p_transaction_id
1622 and sr_instance_id = p_instance_id;
1623
1624 v_start_date DATE;
1625 v_end_date DATE;
1626 v_capacity_units NUMBER;
1627 v_org_id number;
1628 v_dept_id number;
1629 v_res_id number;
1630 v_shift_date date;
1631
1632 CURSOR bucket IS
1633 SELECT mpb.bkt_start_date, mpb.bkt_end_date
1637 and mp.plan_id = mpb.plan_id
1634 FROM msc_plan_buckets mpb,
1635 msc_plans mp
1636 where mp.plan_id = p_plan_id
1638 and mp.organization_id = mpb.organization_id
1639 and mp.sr_instance_id = mpb.sr_instance_id
1640 and mpb.curr_flag =1
1641 and v_shift_date between mpb.bkt_start_date and mpb.bkt_end_date;
1642 BEGIN
1643
1644 OPEN c_net_res_avail;
1645 FETCH c_net_res_avail into v_org_id, v_dept_id, v_res_id,v_shift_date;
1646 CLOSE c_net_res_avail;
1647
1648 OPEN bucket;
1649 FETCH bucket into v_start_date, v_end_date;
1650 CLOSE bucket;
1651
1652 v_capacity_units :=0;
1653 begin
1654 select round(sum(decode(sign(to_time-from_time),1,(to_time - from_time),
1655 (to_time+86400-from_time)
1656 )/3600*capacity_units),6)
1657 into v_capacity_units
1658 from msc_net_resource_avail
1659 where plan_id = p_plan_id
1660 and organization_id = v_org_id
1661 and sr_instance_id = p_instance_id
1662 AND department_id = v_dept_id
1663 AND resource_id = v_res_id
1664 and nvl(parent_id, 0) <> -1
1665 and capacity_units >0
1666 and shift_date between v_start_date and v_end_date;
1667 exception when no_data_found then
1668 v_capacity_units :=0;
1669 end;
1670
1671 v_capacity_units := nvl(v_capacity_units,0);
1672
1673 -- update the resource units for parent record
1674 update msc_net_resource_avail
1675 set capacity_units = v_capacity_units,
1676 status =0,
1677 applied =2,
1678 updated =2
1679 where plan_id = p_plan_id
1680 and organization_id = v_org_id
1681 and sr_instance_id = p_instance_id
1682 AND department_id = v_dept_id
1683 AND resource_id = v_res_id
1684 and shift_date = v_start_date
1685 and parent_id =-1;
1686
1687 END refresh_parent_record;
1688
1689 FUNCTION isFirstOP(p_plan_id number,
1690 p_supply_id number,
1691 p_changed_op number,
1692 p_changed_res number) RETURN boolean IS
1693 cursor op_c is
1694 select operation_seq_num,resource_seq_num
1695 from msc_resource_requirements
1696 where plan_id = p_plan_id
1697 and supply_id = p_supply_id
1698 and parent_id = 2
1699 order by operation_seq_num,resource_seq_num;
1700 v_op number;
1701 v_res number;
1702 BEGIN
1703 OPEN op_c;
1704 FETCH op_c INTO v_op, v_res;
1705 CLOSE op_c;
1706
1707 if p_changed_op = v_op and p_changed_res = v_res then
1708 return true;
1709 else
1710 return false;
1711 end if;
1712 END isFirstOP;
1713
1714 PROCEDURE reset_changes IS
1715 BEGIN
1716 if p_trans_id is not null then
1717 p_trans_id.delete;
1718 p_start_time.delete;
1719 p_end_time.delete;
1720 p_resource_units.delete;
1721 end if;
1722 END reset_changes;
1723
1724 PROCEDURE set_sim_res_times(p_plan_id NUMBER,
1725 p_avail_checked number,
1726 p_sim_start date,
1727 p_sim_end date,
1728 p_new_start out nocopy date,
1729 p_new_end out nocopy date,
1730 p_error_status out nocopy varchar2) IS
1731 p_sim_new_start date;
1732 p_sim_new_end date;
1733
1734 BEGIN
1735
1736 FOR a in 1..nvl(sim_res.org_id.last,0) LOOP
1737 if a <> p_avail_checked then
1738 -- bug5969889, don't use res_units from routing for simultaneous res
1739 get_new_time(p_plan_id, sim_res.org_id(a), sim_res.inst_id(a),
1740 sim_res.dept_id(a), sim_res.res_id(a),
1741 p_sim_start,
1742 sim_res.res_hours(a), sim_res.assign_units(a),
1743 false,
1744 p_new_start, p_new_end, p_error_status);
1745 if p_error_status = 'NO_RES_AVAIL' then
1746 exit;
1747 end if;
1748 end if;
1749
1750 if p_new_start <> p_sim_start or
1751 p_new_end <> p_sim_end then
1752 -- res(a) can not start/end at the same time as other res
1753 p_sim_new_start := p_new_start;
1754 p_sim_new_end := p_new_end;
1755 set_sim_res_times(p_plan_id, a, p_sim_new_start,p_sim_new_end,
1756 p_new_start,p_new_end,p_error_status);
1757 exit;
1758 end if;
1759
1760 END LOOP;
1761
1762 END set_sim_res_times;
1763
1764 PROCEDURE reset_sim_res IS
1765 BEGIN
1766 sim_res.org_id.delete;
1767 sim_res.inst_id.delete;
1768 sim_res.dept_id.delete;
1769 sim_res.res_id.delete;
1770 sim_res.res_hours.delete;
1771 sim_res.assign_units.delete;
1772 sim_res.op_seq_id.delete;
1773 sim_res.rt_seq_id.delete;
1774
1775 END reset_sim_res;
1776
1777
1778 PROCEDURE calculate_ops(p_plan_id NUMBER,
1779 p_supply_id number,
1780 p_changed_op number,
1781 p_changed_res number,
1782 p_changed_date date,
1783 p_new_end_date date,
1784 p_status out nocopy varchar2 ) IS
1785
1786 cursor time_c is
1787 select transaction_id, operation_seq_num,resource_seq_num,
1788 organization_id,sr_instance_id,department_id,resource_id,
1792 operation_sequence_id,
1789 resource_hours,assigned_units,
1790 nvl(firm_start_date,start_date),
1791 nvl(firm_end_date,end_date),
1793 routing_sequence_id
1794 from msc_resource_requirements
1795 where plan_id = p_plan_id
1796 and supply_id = p_supply_id
1797 and parent_id = 2
1798 order by operation_seq_num,resource_seq_num;
1799
1800
1801 p_op numTab;
1802 p_res numTab;
1803 p_org_id numTab;
1804 p_inst_id numTab;
1805 p_dept_id numTab;
1806 p_res_id numTab;
1807 p_res_hours numTab;
1808 p_assign_units numTab;
1809 p_op_seq_id numTab;
1810 p_rt_seq_id numTab;
1811
1812 p_new_start date;
1813 p_new_end date;
1814 p_current_op number;
1815 p_current_res number;
1816
1817 p_sim_start date;
1818 p_sim_end date;
1819
1820 k number := 0;
1821 p_effective_date date;
1822 p_disable_date date;
1823 BEGIN
1824
1825 reset_changes;
1826
1827 OPEN time_c;
1828 FETCH time_c BULK COLLECT INTO p_trans_id, p_op, p_res,
1829 p_org_id, p_inst_id, p_dept_id, p_res_id,
1830 p_res_hours, p_assign_units,p_start_time, p_end_time,
1831 p_op_seq_id, p_rt_seq_id;
1832 CLOSE time_c;
1833
1834 FOR a in 1..nvl(p_op.last,0) LOOP
1835
1836 p_resource_units(a) := routing_res_unit(p_plan_id, p_op_seq_id(a),
1837 p_rt_seq_id(a), p_res_id(a),p_assign_units(a));
1838
1839 if a = 1 then
1840 p_current_op := p_op(a);
1841 p_current_res := p_res(a);
1842 p_new_start := p_changed_date;
1843 p_new_end := p_new_end_date;
1844
1845 end if;-- if a = 1 then
1846
1847 if p_op(a) <> p_current_op or
1848 p_res(a) <> p_current_res then -- new op/res
1849
1850 p_current_op := p_op(a);
1851 p_current_res := p_res(a);
1852
1853 if k > 0 then -- calculate simultaneous resources times
1854 --dbms_output.put_line('k='||k);
1855 set_sim_res_times(p_plan_id,1,
1856 p_sim_start, p_sim_end,
1857 p_new_start, p_new_end, p_status);
1858 for i in 1..nvl(sim_res.org_id.last,0) loop
1859 p_start_time(a-i) := p_new_start;
1860 p_end_time(a-i) := p_new_end;
1861 p_resource_units(a-i) := p_assign_units(a-i);
1862 end loop;
1863 k :=0;
1864 reset_sim_res;
1865 end if;
1866 -- use end time of prev op as start time
1867
1868 get_new_time(p_plan_id, p_org_id(a), p_inst_id(a),
1869 p_dept_id(a), p_res_id(a),
1870 p_end_time(a-1), p_res_hours(a), p_resource_units(a),
1871 false,
1872 p_new_start, p_new_end, p_status);
1873 elsif a > 1 then -- simultaneous resources
1874 k := k+1;
1875 --dbms_output.put_line('k='||k||', a='||a);
1876 if k = 1 then -- get the first sim res
1877 sim_res.org_id(k) := p_org_id(a-1);
1878 sim_res.inst_id(k) := p_inst_id(a-1);
1879 sim_res.dept_id(k) := p_dept_id(a-1);
1880 sim_res.res_id(k) := p_res_id(a-1);
1881 sim_res.res_hours(k) := p_res_hours(a-1);
1882 sim_res.assign_units(k) := p_assign_units(a-1);
1883 sim_res.op_seq_id(k) := p_op_seq_id(a-1);
1884 sim_res.rt_seq_id(k) := p_rt_seq_id(a-1);
1885 p_sim_start := p_start_time(a-1);
1886 p_sim_end := p_end_time(a-1);
1887 k := k+1;
1888 end if;
1889 sim_res.org_id(k) := p_org_id(a);
1890 sim_res.inst_id(k) := p_inst_id(a);
1891 sim_res.dept_id(k) := p_dept_id(a);
1892 sim_res.res_id(k) := p_res_id(a);
1893 sim_res.res_hours(k) := p_res_hours(a);
1894 sim_res.assign_units(k) := p_assign_units(a);
1895 sim_res.op_seq_id(k) := p_op_seq_id(a);
1896 sim_res.rt_seq_id(k) := p_rt_seq_id(a);
1897
1898 end if;
1899
1900 p_start_time(a) := p_new_start;
1901 p_end_time(a) := p_new_end;
1902
1903 -- dbms_output.put_line(a||','||to_char(p_start_time(a),'MM/DD/RRRR HH24:MI')||','||to_char(p_end_time(a),'MM/DD/RRRR HH24:MI')||','||p_res_hours(a));
1904
1905 END LOOP;
1906
1907 --5578138,
1908 ProcessDates(p_plan_id, p_supply_id, p_effective_date, p_disable_date );
1909 if p_new_end > p_disable_date or
1910 p_new_end < p_effective_date then
1911 p_status := 'SUPPLY_OUTSIDE_PROCESS_DATE';
1912 end if;
1913
1914 END calculate_ops;
1915
1916 PROCEDURE move_res_req(p_plan_id number,
1917 p_supply_id number) IS
1918 a number;
1919 BEGIN
1920
1921 forall a in 1..p_trans_id.count
1922 update msc_resource_requirements
1923 set firm_start_date = p_start_time(a),
1924 firm_end_date = p_end_time(a),
1925 assigned_units = p_resource_units(a), --bug 5973698
1926 status = 0,
1927 applied =2,
1928 firm_flag = 7
1929 where plan_id = p_plan_id
1930 and transaction_id = p_trans_id(a);
1931 --dbms_output.put_line('new due date='||to_char(p_new_end,'MM/DD/RRRR HH24:MI'));
1932
1933 a := nvl(p_trans_id.last,0);
1934 if a > 0 then
1935 update msc_supplies
1936 set status = 0,
1937 applied =2,
1938 firm_planned_type = 1,
1939 firm_date = p_end_time(a),
1940 firm_quantity = new_order_quantity
1941 where plan_id = p_plan_id
1945 END move_res_req;
1942 and transaction_id = p_supply_id;
1943 end if;
1944
1946
1947 PROCEDURE get_new_time(p_plan_id NUMBER,
1948 p_org_id NUMBER,
1949 p_inst_id NUMBER,
1950 p_dept_id NUMBER,
1951 p_res_id NUMBER,
1952 p_changed_date date,
1953 p_res_hours number,
1954 p_assign_units number,
1955 p_first_activity boolean,
1956 p_new_start out nocopy date,
1957 p_new_end out nocopy date,
1958 p_error_status out nocopy varchar2) IS
1959 p_valid_start boolean := false;
1960
1961 p_cum number := 0;
1962 p_start date;
1963 p_end date;
1964
1965 cursor avail_c is
1966 select shift_date, from_time, to_time, capacity_units
1967 from msc_net_resource_avail
1968 where plan_id = p_plan_id
1969 and organization_id = p_org_id
1970 and sr_instance_id = p_inst_id
1971 and department_id = p_dept_id
1972 and resource_id = p_res_id
1973 and capacity_units > 0
1974 and nvl(parent_id, 0) <> -1
1975 and shift_date >= trunc(p_changed_date)
1976 order by shift_date, from_time, to_time;
1977
1978 cursor infinite_c is
1979 select 1
1980 from msc_net_resource_avail
1981 where plan_id = p_plan_id
1982 and organization_id = p_org_id
1983 and sr_instance_id = p_inst_id
1984 and department_id = p_dept_id
1985 and resource_id = p_res_id
1986 and nvl(parent_id, 0) <> -1;
1987
1988 avail_rec avail_c%ROWTYPE;
1989 v_infinite number;
1990 v_res_minutes_per_unit number;
1991 v_res_minutes number;
1992
1993 BEGIN
1994 --dbms_output.put_line(p_org_id||','||p_inst_id||','||p_dept_id||','||p_res_id);
1995 --dbms_output.put_line('get new time: '||to_char(p_changed_date,'MM/DD/RRRR HH24:MI')||','||p_res_hours||','||p_assign_units);
1996 OPEN infinite_c;
1997 FETCH infinite_c INTO v_infinite;
1998 CLOSE infinite_c;
1999
2000 --bug5973236, need to run up to minute level
2001 v_res_minutes := round(p_res_hours*60,0);
2002
2003 if nvl(p_assign_units,0) <> 0 then
2004 --bug5973236, need to ceil to minute level for per unit time
2005 v_res_minutes_per_unit := ceil(v_res_minutes/p_assign_units);
2006 else
2007 v_res_minutes_per_unit := v_res_minutes;
2008 end if;
2009
2010 if v_infinite is null then
2011 -- dbms_output.put_line('infinite resource');
2012 p_new_start := p_changed_date;
2013 p_new_end := p_new_start + (v_res_minutes_per_unit/(24*60));
2014 return;
2015 end if;
2016
2017 OPEN avail_c;
2018 LOOP
2019 FETCH avail_c INTO avail_rec;
2020 EXIT WHEN avail_c%NOTFOUND;
2021
2022 p_start := avail_rec.shift_date + avail_rec.from_time/86400 ;
2023 if avail_rec.to_time > avail_rec.from_time then
2024 p_end := avail_rec.shift_date + avail_rec.to_time/86400 ;
2025 else
2026 p_end := avail_rec.shift_date + avail_rec.to_time/86400 + 1;
2027 end if;
2028 --dbms_output.put_line('avail dates '||to_char(p_start,'MM/DD/RRRR HH24:MI')||','||to_char(p_end,'MM/DD/RRRR HH24:MI'));
2029
2030 if p_start <= p_changed_date and -- p_changed_date is not in break
2031 p_changed_date <= p_end and
2032 p_assign_units <= avail_rec.capacity_units then
2033 p_valid_start := true;
2034 p_start := p_changed_date;
2035 p_new_start := p_changed_date;
2036 --dbms_output.put_line('p_changed_start='||to_char(p_new_start,'MM/DD/RRRR HH24:MI'));
2037 end if;
2038
2039 if not(p_valid_start) and
2040 p_changed_date < p_start and -- p_new_date is in a break
2041 p_assign_units <= avail_rec.capacity_units then
2042 if p_first_activity then -- can start in a break
2043 p_error_status := 'START_IN_BREAK';
2044 end if; -- if p_first_activity then
2045 p_new_start := p_start; -- need to move the date
2046 p_valid_start := true;
2047 --dbms_output.put_line('p_new_start='||to_char(p_new_start,'MM/DD/RRRR HH24:MI'));
2048 end if; -- if not(p_valid_start) and
2049
2050 if p_valid_start then -- find the end time
2051 --dbms_output.put_line(round(p_cum,2)||','||to_char(p_start,'MM/DD/RRRR HH24:MI')||','||to_char(p_end,'MM/DD/RRRR HH24:MI'));
2052 if v_res_minutes_per_unit <= p_cum + (p_end-p_start)*24*60 then
2053 p_new_end := p_start +
2054 (v_res_minutes_per_unit - p_cum)/(24*60);
2055 --dbms_output.put_line('p_new_end='||to_char(p_new_end,'MM/DD/RRRR HH24:MI'));
2056 exit;
2057 else
2058 p_cum := p_cum + (p_end - p_start)*24*60;
2059 end if;
2060 end if; -- if p_valid_start then
2061 END LOOP;
2062
2063 CLOSE avail_c;
2064 --dbms_output.put_line(to_char(p_start,'MM/DD/RRRR HH24:MI')||','||to_char(p_end,'MM/DD/RRRR HH24:MI')||','||to_char(p_new_start,'MM/DD/RRRR HH24:MI')||','||to_char(p_new_end,'MM/DD/RRRR HH24:MI'));
2065 if p_new_end is null or p_new_start is null then
2066 -- no avail resource found, use the last avail res end time
2067 p_new_start := nvl(p_new_start, nvl(p_start,p_changed_date));
2068 p_new_end := nvl(p_new_end, nvl(p_end, p_new_start+1/60));
2069 p_error_status := 'NO_RES_AVAIL';
2070 end if;
2071
2072 END get_new_time;
2073
2074 Procedure ProcessDates(p_plan_id in number,
2075 p_supply_id in number,
2076 p_effective_date out nocopy date,
2080 from msc_process_effectivity mpe,
2077 p_disable_date out nocopy date) IS
2078 CURSOR eff_c IS
2079 select mpe.effectivity_date, mpe.disable_date
2081 msc_supplies ms
2082 where ms.plan_id = p_plan_id
2083 and ms.transaction_id = p_supply_id
2084 and mpe.plan_id = ms.plan_id
2085 and mpe.process_sequence_id = ms.process_seq_id;
2086 BEGIN
2087 OPEN eff_c;
2088 FETCH eff_c INTO p_effective_date, p_disable_date;
2089 CLOSE eff_c;
2090 END ProcessDates;
2091
2092 FUNCTION routing_res_unit(p_plan_id number, p_op_seq_id number,
2093 p_rt_seq_id number, p_res_id number,
2094 p_assign_units number) RETURN number IS
2095 --bug 5846499, get assign_units from routing
2096 CURSOR unit_c IS
2097 select nvl(resource_units, max_resource_units)
2098 from msc_operation_resources
2099 where plan_id = p_plan_id
2100 and operation_sequence_id = p_op_seq_id
2101 and routing_sequence_id = p_rt_seq_id
2102 and resource_id = p_res_id;
2103 v_assign_units number;
2104 BEGIN
2105
2106 OPEN unit_c;
2107 FETCH unit_c INTO v_assign_units;
2108 CLOSE unit_c;
2109
2110 /* bug8835167, if units from routing is greater than res_req table, use
2111 the one from res_req */
2112 if v_assign_units > p_assign_units then
2113 return p_assign_units;
2114 end if;
2115
2116 return nvl(v_assign_units,p_assign_units);
2117 END routing_res_unit;
2118
2119 PROCEDURE verify_data(p_plan_id number, p_supply_id number) IS
2120 p_org_id NUMBER;
2121 p_inst_id NUMBER;
2122 p_dept_id NUMBER;
2123 p_res_id NUMBER;
2124 p_start_date date;
2125 p_end_date date;
2126 p_start_time date;
2127 p_end_time date;
2128
2129 cursor mrr_c is
2130 select transaction_id, operation_seq_num,resource_seq_num, assigned_units,
2131 organization_id,sr_instance_id,department_id,resource_id,
2132 to_char(firm_start_date,'MM/DD/RRRR HH24:MI') firm_start_time,
2133 to_char(firm_end_date,'MM/DD/RRRR HH24:MI') firm_end_time,
2134 to_char(start_date,'MM/DD/RRRR HH24:MI') start_time,
2135 to_char(end_date,'MM/DD/RRRR HH24:MI') end_time,
2136 resource_hours, overloaded_capacity
2137 from msc_resource_requirements
2138 where plan_id = p_plan_id
2139 and supply_id = p_supply_id
2140 and parent_id = 2
2141 order by operation_seq_num,resource_seq_num;
2142
2143 cursor avail_c is
2144 select shift_date, from_time, to_time, capacity_units
2145 from msc_net_resource_avail
2146 where plan_id = p_plan_id
2147 and organization_id = p_org_id
2148 and sr_instance_id = p_inst_id
2149 and department_id = p_dept_id
2150 and resource_id = p_res_id
2151 and capacity_units > 0
2152 and nvl(parent_id, 0) <> -1
2153 and shift_date >= trunc(p_start_time)
2154 and shift_date <= trunc(p_end_time)
2155 order by 1,2,3;
2156
2157 avail_rec avail_c%ROWTYPE;
2158 mrr_rec mrr_c%ROWTYPE;
2159
2160 BEGIN
2161 -- dbms_output.put_line(p_plan_id||','||p_supply_id);
2162 OPEN mrr_c;
2163 LOOP
2164 FETCH mrr_c INTO mrr_rec;
2165 EXIT WHEN mrr_c%NOTFOUND;
2166 --dbms_output.put_line('req');
2167 if to_date(mrr_rec.firm_start_time,'MM/DD/RRRR HH24:MI') <
2168 to_date(mrr_rec.start_time,'MM/DD/RRRR HH24:MI') then
2169 p_start_time := to_date(mrr_rec.firm_start_time,'MM/DD/RRRR HH24:MI');
2170 else
2171 p_start_time := to_date(mrr_rec.start_time,'MM/DD/RRRR HH24:MI');
2172 end if;
2173 if to_date(mrr_rec.firm_end_time,'MM/DD/RRRR HH24:MI') >
2174 to_date(mrr_rec.end_time,'MM/DD/RRRR HH24:MI') then
2175 p_end_time := to_date(mrr_rec.firm_end_time,'MM/DD/RRRR HH24:MI');
2176 else
2177 p_end_time := to_date(mrr_rec.end_time,'MM/DD/RRRR HH24:MI');
2178 end if;
2179 /*
2180 dbms_output.put_line(mrr_rec.operation_seq_num||','||
2181 mrr_rec.resource_seq_num||',au:'||
2182 mrr_rec.assigned_units||',rh:'||
2183 mrr_rec.resource_hours||','||
2184 mrr_rec.firm_start_time||','||
2185 mrr_rec.firm_end_time||','||
2186 mrr_rec.start_time||','||
2187 mrr_rec.end_time);
2188 */
2189 p_org_id := mrr_rec.organization_id;
2190 p_inst_id := mrr_rec.sr_instance_id;
2191 p_dept_id := mrr_rec.department_id;
2192 p_res_id := mrr_rec.resource_id;
2193
2194 -- dbms_output.put_line('avail ');
2195 OPEN avail_c;
2196 LOOP
2197 FETCH avail_c INTO avail_rec;
2198 EXIT WHEN avail_c%NOTFOUND;
2199
2200 p_start_date := avail_rec.shift_date + avail_rec.from_time/86400 ;
2201 if avail_rec.to_time > avail_rec.from_time then
2202 p_end_date := avail_rec.shift_date + avail_rec.to_time/86400 ;
2203 else
2204 p_end_date := avail_rec.shift_date + avail_rec.to_time/86400 + 1;
2205 end if;
2206 /*
2207 dbms_output.put_line(avail_rec.shift_date||','||
2208 avail_rec.from_time||','||
2209 avail_rec.to_time||','||
2210 avail_rec.capacity_units||','||
2211 to_char(p_start_date,'MM/DD/RRRR HH24:MI')||','||
2212 to_char(p_end_date,'MM/DD/RRRR HH24:MI'));
2213 */
2214 END LOOP;
2215 CLOSE avail_c;
2216 END LOOP;
2217 CLOSE mrr_c;
2218 END verify_data;
2219
2220 END;
[Home] [Help]