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