1 PACKAGE BODY MRP_UPDATE_RESOURCE AS
2 /* $Header: MRPFCAVB.pls 115.6 99/07/16 12:20:37 porting shi $ */
3
4 TYPE ResRecTyp IS RECORD (
5 OPERATION NUMBER,
6 ORGANIZATION_ID NUMBER,
7 LINE_ID NUMBER,
8 DEPARTMENT_ID NUMBER,
9 RESOURCE_ID NUMBER,
10 RESOURCE_HOURS NUMBER,
11 MAX_RATE NUMBER,
12 RESOURCE_UNITS NUMBER,
13 STATUS NUMBER,
14 APPLIED NUMBER,
15 RESOURCE_START_DATE DATE,
16 RESOURCE_END_DATE DATE,
17 UPDATED NUMBER,
18 LAST_UPDATE_DATE DATE,
19 LAST_UPDATED_BY NUMBER,
20 CREATION_DATE DATE,
21 CREATED_BY NUMBER,
22 LAST_UPDATE_LOGIN NUMBER);
23
24 TYPE ResTabTyp IS TABLE OF ResRecTyp
25 INDEX by binary_integer;
26
27 g_resource_exist boolean;
28 g_error_stat VARCHAR2(300);
29 g_compile_designator VARCHAR2(20);
30 g_simulation_set VARCHAR2(10);
31 g_res_group VARCHAR2(30);
32 g_cutoff_date DATE;
33 g_query_id NUMBER:=0;
34 g_org_id NUMBER:=0;
35 g_department_id NUMBER:=0;
36 g_line_id NUMBER:=0;
37 g_resource_id NUMBER:=0;
38 g_change_rec ResRecTyp;
39 g_res_tab ResTabTyp;
40 g_tmp_tab ResTabTyp;
41 i binary_integer;
42 j binary_integer;
43 k binary_integer;
44 OP_ADD_DAY CONSTANT INTEGER :=0;
45 OP_ADD CONSTANT INTEGER :=1;
46 OP_DEL CONSTANT INTEGER :=2;
47 OP_SET CONSTANT INTEGER :=3;
48 OP_DEL_DAY CONSTANT INTEGER :=4;
49
50 -- Cursor used to apply simulation for Calculate Resource Supply for ATP
51 CURSOR C_BRC IS
52 SELECT
53 DECODE(brc.action_type,1,OP_DEL_DAY,3,OP_ADD_DAY,
54 DECODE(sign(brc.capacity_change),-1,
55 OP_DEL,OP_ADD)),
56 dept.organization_id,
57 NULL,
58 bdr.department_id,
59 bdr.resource_id,
60 decode(brc.action_type,1,
61 (nvl(sum(decode(bdr.available_24_hours_flag,
62 1,24,2,
63 ((decode(least(shifts.to_time,shifts.from_time),
64 shifts.to_time,shifts.to_time + 24*3600,
65 shifts.to_time) - shifts.from_time)/3600))),0)),
66 (decode(least(nvl(brc.from_time,0),
67 nvl(brc.to_time,1)),
68 nvl(brc.to_time,1),
69 24 * 3600 + nvl(brc.to_time,1),
70 nvl(brc.to_time,1)) -
71 nvl(brc.from_time,0))/3600),
72 NULL,
73 decode(brc.action_type,1,bdr.capacity_units,
74 abs(brc.capacity_change)),
75 0,
76 NULL,
77 decode(brc.action_type,3,cal.prior_date,brc.from_date),
78 decode(brc.action_type,2,nvl(brc.to_date,g_cutoff_date),
79 3,cal.prior_date,brc.from_date),
80 2,
81 SYSDATE,
82 FND_GLOBAL.USER_ID,
83 SYSDATE,
84 FND_GLOBAL.USER_ID,
85 FND_GLOBAL.LOGIN_ID
86 FROM bom_resources res,
87 bom_departments dept,
88 bom_shift_times shifts,
89 mtl_parameters mp,
90 bom_calendar_dates cal,
91 bom_department_resources bdr,
92 bom_resource_changes brc
93 WHERE res.organization_id = dept.organization_id
94 AND bdr.resource_id = res.resource_id
95 AND dept.organization_id = g_org_id
96 AND NVL(bdr.share_from_dept_id,bdr.department_id)
97 = dept.department_id
98 AND share_from_dept_id is null
99 AND brc.shift_num = shifts.shift_num(+)
100 AND (mp.calendar_code = shifts.calendar_code
101 OR shifts.calendar_code IS NULL)
102 AND mp.organization_id = dept.organization_id
103 AND cal.calendar_date = brc.from_date
104 AND cal.exception_set_id = mp.calendar_exception_set_id
105 AND cal.calendar_code = mp.calendar_code
106 AND bdr.ctp_flag = 1
107 AND nvl(bdr.resource_group_name,'-1') =
108 nvl(g_res_group,nvl(bdr.resource_group_name,'-1'))
109 AND brc.department_id = bdr.department_id
110 AND brc.resource_id = bdr.resource_id
111 AND brc.simulation_set = g_simulation_set
112 AND (brc.from_date >= trunc(sysdate)
113 OR brc.to_date >= trunc(sysdate))
114 GROUP BY '-1',dept.organization_id, bdr.department_id, bdr.resource_id,
115 brc.action_type, brc.to_time, brc.from_time, bdr.capacity_units,
116 brc.capacity_change, brc.from_date, cal.prior_date, brc.to_date,
117 brc.shift_num;
118
119
120 CURSOR C_MFQ IS
121 SELECT
122 NUMBER1,
123 NUMBER2,
124 NUMBER3,
125 NUMBER4,
126 NUMBER5,
127 nvl(NUMBER6,0),
128 nvl(NUMBER7,0),
129 nvl(NUMBER8,0),
130 0,
131 2,
132 DATE1,
133 DATE2,
134 2,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 CREATION_DATE,
138 CREATED_BY,
139 LAST_UPDATE_LOGIN
140 FROM MRP_FORM_QUERY
141 WHERE query_id = g_query_id
142 ORDER BY number2, number3, number4, number5, number1;
143
144 CURSOR C_CAR IS
145 SELECT
146 0,
147 organization_id,
148 line_id,
149 department_id,
150 resource_id,
151 decode(line_id, null, resource_hours,1),
152 nvl(max_rate,0),
153 nvl(resource_units,0),
154 status,
155 applied,
156 resource_start_date,
157 resource_end_date,
158 updated,
159 LAST_UPDATE_DATE,
160 LAST_UPDATED_BY,
161 CREATION_DATE,
162 CREATED_BY,
163 LAST_UPDATE_LOGIN
164 FROM CRP_AVAILABLE_RESOURCES
165 WHERE (compile_designator=g_compile_designator)
166 AND (organization_id = g_org_id)
167 AND ( (line_id = g_line_id)
168 OR ( (line_id IS NULL) AND (g_line_id IS NULL)))
169 AND ( (department_id = g_department_id)
170 OR ( (department_id IS NULL) AND (g_department_id IS NULL)))
171 AND ( (resource_id = g_resource_id)
172 OR ( (resource_id IS NULL) AND (g_resource_id IS NULL)))
173 order by resource_start_date;
174
175 ---------------------------------------------------------------
176 -- apply simulation
177 -- used by calculate resource supply for ATP
178 ---------------------------------------------------------------
179 PROCEDURE apply_simulation(p_org_id IN NUMBER,
180 p_res_group IN VARCHAR2,
181 p_simulation_set IN VARCHAR2,
182 p_cutoff_date IN VARCHAR2)
183 IS
184 first_record BOOLEAN :=TRUE;
185 changed_resource BOOLEAN :=TRUE;
186 BEGIN
187 g_compile_designator := '-1';
188 g_org_id := p_org_id;
189 g_res_group := p_res_group;
190 g_simulation_set := p_simulation_set;
191 g_cutoff_date := to_date(p_cutoff_date,'YYYY/MM/DD HH24:MI:SS');
192 g_department_id :=0;
193 g_line_id :=0;
194 g_resource_id :=0;
195
196 -- load the simulation changes, if there are changes then
197 -- re-query data from crp_available_resources
198
199 OPEN C_BRC;
200 LOOP
201 FETCH C_BRC INTO g_change_rec;
202 IF C_BRC%NOTFOUND THEN
203 IF not first_record THEN
204 update_table;
205 END IF;
206 CLOSE C_BRC;
207 commit;
208 exit;
209 END IF;
210
211 IF (g_change_rec.organization_id = g_org_id )
212 AND ( (g_change_rec.line_id = g_line_id)
213 OR ( (g_change_rec.line_id IS NULL) AND (g_line_id IS NULL)))
214 AND ( (g_change_rec.department_id = g_department_id)
215 OR ( (g_change_rec.department_id IS NULL)
216 AND (g_department_id IS NULL)))
217 AND ( (g_change_rec.resource_id = g_resource_id)
218 OR ( (g_change_rec.resource_id IS NULL)
219 AND (g_resource_id IS NULL))) THEN
220
221 changed_resource :=FALSE;
222 ELSE
223
224 changed_resource :=TRUE;
225 END IF;
226
227
228 IF changed_resource THEN
229
230 IF not first_record THEN
231 -- update the change for the previous resource
232 update_table;
233 END IF;
234
235 g_org_id :=g_change_rec.organization_id;
236 g_department_id :=g_change_rec.department_id;
237 g_resource_id :=g_change_rec.resource_id;
238 g_line_id :=g_change_rec.line_id;
239
240 -- initialize the table for next resource
241 initialize_table;
242
243 END IF;
244
245 calculate_change;
246 first_record :=FALSE;
247
248 END LOOP;
249 EXCEPTION when others THEN
250 IF (C_BRC%ISOPEN) THEN
251 close C_BRC;
252 END IF;
253 END apply_simulation;
254
255 ---------------------------------------------------------------
256 -- apply change
257 ---------------------------------------------------------------
258 PROCEDURE apply_change( p_query_id IN NUMBER,
259 p_compile_designator IN VARCHAR2 ) IS
260 first_record BOOLEAN :=TRUE;
261 changed_resource BOOLEAN :=TRUE;
262 BEGIN
263 g_compile_designator :=p_compile_designator;
264 g_query_id := p_query_id;
265 g_org_id :=0;
266 g_department_id :=0;
267 g_line_id :=0;
268 g_resource_id :=0;
269
270
271 -- load from mrp_form_query for the changes, if the resource changes
272 -- re-query data from crp_available_resources
273
274 OPEN C_MFQ;
275 LOOP
276 FETCH C_MFQ INTO g_change_rec;
277
278 IF C_MFQ%NOTFOUND THEN
279 IF not first_record THEN
280 update_table;
281 END IF;
282 CLOSE C_MFQ;
283 commit;
284 exit;
285 END IF;
286
287 IF (g_change_rec.organization_id = g_org_id )
288 AND ( (g_change_rec.line_id = g_line_id)
289 OR ( (g_change_rec.line_id IS NULL) AND (g_line_id IS NULL)))
290 AND ( (g_change_rec.department_id = g_department_id)
291 OR ( (g_change_rec.department_id IS NULL)
292 AND (g_department_id IS NULL)))
293 AND ( (g_change_rec.resource_id = g_resource_id)
294 OR ( (g_change_rec.resource_id IS NULL)
295 AND (g_resource_id IS NULL))) THEN
296
297 changed_resource :=FALSE;
298 ELSE
299
300 changed_resource :=TRUE;
301 END IF;
302
303
304 IF changed_resource THEN
305
306 IF not first_record THEN
307 -- update the change for the previous resource
308 update_table;
309 END IF;
310
311 g_org_id :=g_change_rec.organization_id;
312 g_department_id :=g_change_rec.department_id;
313 g_resource_id :=g_change_rec.resource_id;
314 g_line_id :=g_change_rec.line_id;
315
316 -- initialize the table for next resource
317 initialize_table;
318
319 END IF;
320
321 calculate_change;
322
323 first_record :=FALSE;
324
325 END LOOP;
326 EXCEPTION when others THEN
327
328 IF (C_MFQ%ISOPEN) THEN
329 close C_MFQ;
330 END IF;
331 raise_application_error(-20000, sqlerrm);
332 END apply_change;
333
334 ---------------------------------------------------------------------
335 -- to get the values into PL/SQL tables
336 ---------------------------------------------------------------------
337 PROCEDURE initialize_table IS
338 BEGIN
339
340 -- load from crp_available_resources for all the records
341 -- related to the same resource
342 j :=0;
343 g_res_tab.delete;
344 OPEN C_CAR;
345 LOOP
346 j := j+1;
347 FETCH C_CAR INTO g_res_tab(j);
348 if C_CAR%NOTFOUND then
349 if C_CAR%ROWCOUNT=0 then
350 g_resource_exist :=false;
351 end if;
352 exit;
353 end if;
354
355 END LOOP;
356 IF C_CAR%ROWCOUNT >0 THEN
357 g_resource_exist :=true;
358 END IF;
359 CLOSE C_CAR;
360 EXCEPTION WHEN others THEN
361 IF (C_CAR%ISOPEN) THEN
362 close C_CAR;
363 END IF;
364
365 END initialize_table;
366
367 ---------------------------------------------------------------------
368 -- to
369 ---------------------------------------------------------------------
370 PROCEDURE calculate_change IS
371 v_start_record number;
372 v_end_record number;
373
374 BEGIN
375
376 IF g_resource_exist THEN
377 -- try to find which records in res_tab are affected by the change
378
379 -- try to find which record the change start date falls
380
381 j:=g_res_tab.FIRST;
382 IF (g_change_rec.resource_start_date <
383 g_res_tab(j).resource_start_date ) THEN
384 -- the change record starts before the range
385 v_start_record :=0;
386 ELSE
387 --find the first record whose start date is greater than change's start date
388 --then the previous record will be where the change starts
389 While (j is not null) and
390 ( g_change_rec.resource_start_date >=
391 g_res_tab(j).resource_start_date )
392 LOOP
393 j:=g_res_tab.next(j);
394 END LOOP;
395 IF j is null THEN
396 -- if j is null, then the change is on or outside the last record
397 i :=g_res_tab.LAST;
398 IF ( g_res_tab(i).resource_end_date is null ) THEN
399 v_start_record :=g_res_tab.LAST;
400 v_end_record :=g_res_tab.LAST;
401 ELSE
402 IF (g_change_rec.resource_start_date <=
403 g_res_tab(i).resource_end_date ) THEN
404 v_start_record :=g_res_tab.LAST;
405 v_end_record :=g_res_tab.LAST;
406 ELSE
407 v_start_record :=g_res_tab.LAST+1;
408 v_end_record :=g_res_tab.LAST+1;
409
410 END IF;
411 END IF;
412 ELSE
413 -- otherwise, the change is inside the range
414 -- but it could be on a record or in a gap between two records
415 IF (g_change_rec.resource_start_date <=
416 g_res_tab(j-1).resource_end_date ) THEN
417 --change falls on the previos record
418 v_start_record := j-1;
419 ELSE
420 --change falls on the gap between record j-1 and record j
421 v_start_record := j-0.5;
422 END IF;
423 --go to the previous record to find where change ends
424 j:=j-1;
425 END IF;
426 END IF;
427
428 -- try to find which record the change end date fall
429
430 -- if the change does not have end date, the change extends till the end
431 -- but it could be on the last record, or outside the range
432 IF ( g_change_rec.resource_end_date is null ) THEN
433 i :=g_res_tab.LAST;
434 IF ( g_res_tab(i).resource_end_date is null ) THEN
435 -- falls on the last record
436 v_end_record :=g_res_tab.LAST;
437 ELSE
438 --falls outside the last record
439 v_end_record :=g_res_tab.LAST+1;
440 END IF;
441
442 ELSE
443 IF ( g_change_rec.resource_end_date <
444 g_res_tab(1).resource_start_date ) THEN
445 -- the change ends before the first record
446 v_end_record :=0;
447 ELSE
448
449 While (j is not null) and
450 ( g_change_rec.resource_end_date >=
451 g_res_tab(j).resource_start_date )
452 LOOP
453 j:=g_res_tab.next(j);
454 END LOOP;
455
456 IF j is null THEN
457 -- if j is null, then the change ends on or outside the last record
458 i :=g_res_tab.LAST;
459 IF ( g_res_tab(i).resource_end_date is null ) THEN
460 v_end_record :=g_res_tab.LAST;
461 ELSE
462 IF (g_change_rec.resource_end_date <=
463 g_res_tab(i).resource_end_date ) THEN
464 v_end_record :=g_res_tab.LAST;
465 ELSE
466 v_end_record :=g_res_tab.LAST+1;
467 END IF;
468 END IF;
469
470 ELSE
471 IF (g_change_rec.resource_end_date <=
472 g_res_tab(j-1).resource_end_date ) THEN
473 -- change ends on the previous record
474 v_end_record := j-1;
475 ELSE
476 -- change ends in the gap between record j-1 and record j
477 v_end_record := j-0.5;
478 END IF;
479 END IF;
480 END IF;
481 END IF;
482
483 -- flush the records to tmp_tab
484 k:=0;
485 g_tmp_tab.delete;
486
487 IF g_change_rec.operation <> OP_SET THEN
488
489 IF ( v_start_record =0 ) THEN
490 IF g_change_rec.operation not in (OP_DEL, OP_DEL_DAY) THEN
491 k:=k+1;
492 g_tmp_tab(k) := g_change_rec;
493 IF (v_end_record <> 0) THEN
494 g_tmp_tab(k).resource_end_date :=
495 g_res_tab(1).resource_start_date -1;
496 END IF;
497
498 -- if add non working day, set the updated field as 1
499 -- so that when re-plan, it will be treated as work day
500
501 IF g_change_rec.operation = OP_ADD_DAY THEN
502 g_tmp_tab(k).updated :=1;
503 END IF;
504 END IF;
505 END IF;
506
507 j:=g_res_tab.FIRST;
508 While (j is not null)
509 LOOP
510 IF (j < v_start_record) or (j > v_end_record) THEN
511 -- no change, just copy the old record
512 k:=k+1;
513 g_tmp_tab(k):=g_res_tab(j);
514
515 ELSIF (j > v_start_record) and (j<v_end_record) THEN
516 -- the whole record is affected, change the qty
517 k:=k+1;
518 g_tmp_tab(k):=g_res_tab(j);
519 IF g_change_rec.operation = OP_ADD THEN
520 g_tmp_tab(k).resource_units :=
521 g_res_tab(j).resource_units +
522 g_change_rec.resource_units ;
523 g_tmp_tab(k).resource_hours :=
524 g_res_tab(j).resource_hours +
525 g_change_rec.resource_hours ;
526 g_tmp_tab(k).max_rate:=
527 g_res_tab(j).max_rate+
528 g_change_rec.max_rate;
529 ELSIF g_change_rec.operation = OP_DEL THEN
530 g_tmp_tab(k).resource_units :=
531 g_res_tab(j).resource_units -
532 g_change_rec.resource_units ;
533 g_tmp_tab(k).resource_hours :=
534 g_res_tab(j).resource_hours -
535 g_change_rec.resource_hours ;
536 g_tmp_tab(k).max_rate:=
537 g_res_tab(j).max_rate-
538 g_change_rec.max_rate;
539 END IF;
540 g_tmp_tab(k).status :=0;
541 g_tmp_tab(k).applied:=2;
542 g_tmp_tab(k).last_update_date := sysdate;
543 g_tmp_tab(k).last_updated_by :=
544 g_change_rec.last_updated_by;
545
546
547 ELSIF (j=v_start_record) and (j = v_end_record) THEN
548 -- need to cut the record into three records
549
550 IF (g_change_rec.resource_start_date <>
551 g_res_tab(j).resource_start_date ) THEN
552 -- need to change the date for the first record
553 k:=k+1;
554 g_tmp_tab(k):=g_res_tab(j);
555 g_tmp_tab(k).resource_end_date:=
556 g_change_rec.resource_start_date - 1;
557 g_tmp_tab(k).status :=0;
558 g_tmp_tab(k).applied:=2;
559 g_tmp_tab(k).last_update_date := sysdate;
560 g_tmp_tab(k).last_updated_by :=
561 g_change_rec.last_updated_by;
562
563 END IF;
564
565 -- add a new record
566 -- delete work day and add non working day would be caught
567 -- here only if it falls inside the range and not in a gap,
568 -- because v_start_record will always = v_end_record in these cases
569
570 IF g_change_rec.operation <> OP_DEL_DAY THEN
571 k:=k+1;
572 g_tmp_tab(k):=g_change_rec;
573
574 IF g_change_rec.operation = OP_ADD THEN
575 g_tmp_tab(k).resource_units :=
576 g_res_tab(j).resource_units +
577 g_change_rec.resource_units ;
578 g_tmp_tab(k).resource_hours :=
579 g_res_tab(j).resource_hours +
580 g_change_rec.resource_hours ;
581 g_tmp_tab(k).max_rate:=
582 g_res_tab(j).max_rate+
583 g_change_rec.max_rate;
584 ELSIF g_change_rec.operation = OP_DEL THEN
585 g_tmp_tab(k).resource_units :=
586 g_res_tab(j).resource_units -
587 g_change_rec.resource_units ;
588 g_tmp_tab(k).resource_hours :=
589 g_res_tab(j).resource_hours -
590 g_change_rec.resource_hours ;
591 g_tmp_tab(k).max_rate:=
592 g_res_tab(j).max_rate-
593 g_change_rec.max_rate;
594
595 -- don't add onto the quantity of the original record
596 ELSIF g_change_rec.operation = OP_ADD_DAY THEN
597 g_tmp_tab(k).updated :=1;
598
599 END IF;
600 END IF;
601
602 IF (g_change_rec.resource_end_date <>
603 g_res_tab(j).resource_end_date ) or
604 ( g_res_tab(j).resource_end_date is null and
605 g_change_rec.resource_end_date is not null) THEN
606 -- need to change the date for the third record
607 k:=k+1;
608 g_tmp_tab(k):=g_res_tab(j);
609 g_tmp_tab(k).resource_start_date:=
610 g_change_rec.resource_end_date + 1;
611 g_tmp_tab(k).status :=0;
612 g_tmp_tab(k).applied:=2;
613 g_tmp_tab(k).last_update_date := sysdate;
614 g_tmp_tab(k).last_updated_by :=
615 g_change_rec.last_updated_by;
616
617 END IF;
618
619
620 ELSIF (j=v_start_record) and (j <> v_end_record) THEN
621 -- need to cut the record
622
623 IF (g_change_rec.resource_start_date <>
624 g_res_tab(j).resource_start_date ) THEN
625 -- need to change the date
626 k:=k+1;
627 g_tmp_tab(k):=g_res_tab(j);
628 g_tmp_tab(k).resource_end_date:=
629 g_change_rec.resource_start_date - 1;
630 g_tmp_tab(k).status :=0;
631 g_tmp_tab(k).applied:=2;
632 g_tmp_tab(k).last_update_date := sysdate;
633 g_tmp_tab(k).last_updated_by :=
634 g_change_rec.last_updated_by;
635
636 END IF;
637
638 -- and add a new record
639 k:=k+1;
640 g_tmp_tab(k):=g_change_rec;
641 g_tmp_tab(k).resource_end_date:=
642 g_res_tab(j).resource_end_date;
643 IF g_change_rec.operation = OP_ADD THEN
644 g_tmp_tab(k).resource_units :=
645 g_res_tab(j).resource_units +
646 g_change_rec.resource_units ;
647 g_tmp_tab(k).resource_hours :=
648 g_res_tab(j).resource_hours +
649 g_change_rec.resource_hours ;
650 g_tmp_tab(k).max_rate:=
651 g_res_tab(j).max_rate+
652 g_change_rec.max_rate;
653 ELSIF g_change_rec.operation = OP_DEL THEN
654 g_tmp_tab(k).resource_units :=
655 g_res_tab(j).resource_units -
656 g_change_rec.resource_units ;
657 g_tmp_tab(k).resource_hours :=
658 g_res_tab(j).resource_hours -
659 g_change_rec.resource_hours ;
660 g_tmp_tab(k).max_rate:=
661 g_res_tab(j).max_rate-
662 g_change_rec.max_rate;
663 END IF;
664
665 ELSIF (j=v_end_record) and (j <> v_start_record) THEN
666 -- need to cut the record
667
668 -- add a new record
669 k:=k+1;
670 g_tmp_tab(k):=g_change_rec;
671 g_tmp_tab(k).resource_start_date:=
672 g_res_tab(j).resource_start_date;
673 IF g_change_rec.operation = OP_ADD THEN
674 g_tmp_tab(k).resource_units :=
675 g_res_tab(j).resource_units +
676 g_change_rec.resource_units ;
677 g_tmp_tab(k).resource_hours :=
678 g_res_tab(j).resource_hours +
679 g_change_rec.resource_hours ;
680 g_tmp_tab(k).max_rate:=
681 g_res_tab(j).max_rate+
682 g_change_rec.max_rate;
683 ELSIF g_change_rec.operation = OP_DEL THEN
684 g_tmp_tab(k).resource_units :=
685 g_res_tab(j).resource_units -
686 g_change_rec.resource_units ;
687 g_tmp_tab(k).resource_hours :=
688 g_res_tab(j).resource_hours -
689 g_change_rec.resource_hours ;
690 g_tmp_tab(k).max_rate:=
691 g_res_tab(j).max_rate-
692 g_change_rec.max_rate;
693 END IF;
694
695 IF (g_change_rec.resource_end_date <>
696 g_res_tab(j).resource_end_date ) or
697 (g_change_rec.resource_end_date is not null and
698 g_res_tab(j).resource_end_date is null )THEN
699 -- need to change the date
700 k:=k+1;
701 g_tmp_tab(k):=g_res_tab(j);
702 g_tmp_tab(k).resource_start_date:=
703 g_change_rec.resource_end_date + 1;
704 g_tmp_tab(k).status :=0;
705 g_tmp_tab(k).applied:=2;
706 g_tmp_tab(k).last_update_date := sysdate;
707 g_tmp_tab(k).last_updated_by :=
708 g_change_rec.last_updated_by;
709 END IF;
710 END IF;
711
712 -- if change starts or ends in the gap, need to insert new row
713 IF g_change_rec.operation not in (OP_DEL_DAY, OP_DEL) THEN
714
715 IF (v_start_record >j ) and (v_start_record <j+1 ) THEN
716 k:=k+1;
717 g_tmp_tab(k):=g_change_rec;
718 IF (g_change_rec.resource_end_date >=
719 g_res_tab(j+1).resource_start_date or
720 g_change_rec.resource_end_date is null) THEN
721 --the change extends over the gap, need to change the end date
722 g_tmp_tab(k).resource_end_date:=
723 g_res_tab(j+1).resource_start_date-1;
724 END IF;
725 ELSIF (v_end_record >j ) and (v_end_record <j+1 ) THEN
726 k:=k+1;
727 g_tmp_tab(k):=g_change_rec;
728 IF (g_change_rec.resource_start_date <=
729 g_res_tab(j).resource_end_date ) THEN
730 --the change extends over the gap, need to change start date
731 g_tmp_tab(k).resource_start_date:=
732 g_res_tab(j).resource_end_date+1;
733 END IF;
734 END IF;
735 END IF;
736
737 j:=g_res_tab.next(j);
738 END LOOP;
739
740 -- if the record falls outside the original range, add a new row
741 i := g_res_tab.LAST;
742 IF (v_end_record = i+1) THEN
743 IF g_change_rec.operation not in (OP_DEL_DAY, OP_DEL) THEN
744 k:=k+1;
745 g_tmp_tab(k):=g_change_rec;
746 IF (v_start_record <> i +1 ) THEN
747 g_tmp_tab(k).resource_start_date:=
748 g_res_tab(i).resource_end_date +1;
749 END IF;
750 IF g_change_rec.operation = OP_ADD_DAY THEN
751 g_tmp_tab(k).updated :=1;
752 END IF;
753 END IF;
754 END IF;
755
756 ELSIF g_change_rec.operation= OP_SET THEN
757
758 i := g_res_tab.LAST;
759
760 IF (v_start_record = 0) THEN
761
762 --if change falls before the range, add a row, go to the end record
763 --cut record if needed, then go to the next record
764 k:=k+1;
765 g_tmp_tab(k):=g_change_rec;
766 -- if the set record ends outside the range, don't have to loop
767 IF (v_end_record = i+1) THEN
768 j:='';
769 ELSIF (v_end_record=0) THEN
770 -- the change ends before the range, loop from record1
771 j:=g_res_tab.FIRST;
772 ELSIF (v_end_record > trunc(v_end_record)) THEN
773 -- change falls on a gap, go to the record after the gap
774 j:=trunc(v_end_record)+1;
775 -- if the set record ends outside the range, don't have to loop
776 ELSIF (v_end_record < i+1) THEN
777 -- go to where the set record ends and add row if needed
778 j:=v_end_record;
779
780 IF (g_change_rec.resource_end_date <
781 g_res_tab(j).resource_end_date ) or
782 (g_res_tab(j).resource_end_date is null and
783 g_change_rec.resource_end_date is not null) THEN
784
785 -- need to add row for the date change
786 K:=K+1;
787 g_tmp_tab(k):=g_res_tab(j);
788 g_tmp_tab(k).resource_start_date :=
789 g_change_rec.resource_end_date +1;
790 g_tmp_tab(k).status :=0;
791 g_tmp_tab(k).applied:=2;
792 g_tmp_tab(k).last_update_date := sysdate;
793 g_tmp_tab(k).last_updated_by :=
794 g_change_rec.last_updated_by;
795
796 END IF;
797 --go to the next record, and ready for loop
798 j:=j+1;
799 END IF;
800 ELSE
801 j:=g_res_tab.FIRST;
802 END IF;
803
804 IF j < i+1 THEN
805 While ( j is not null ) LOOP
806 IF (j < v_start_record) or (j > v_end_record) THEN
807 -- no change
808 k:=k+1;
809 g_tmp_tab(k):=g_res_tab(j);
810
811 ELSIF (j=v_start_record) THEN
812
813 IF (g_change_rec.resource_start_date >
814 g_res_tab(j).resource_start_date ) THEN
815 -- need to insert row with date change only first
816 K:=K+1;
817 g_tmp_tab(k):=g_res_tab(j);
818 g_tmp_tab(k).resource_end_date :=
819 g_change_rec.resource_start_date -1;
820 g_tmp_tab(k).status :=0;
821 g_tmp_tab(k).applied:=2;
822 g_tmp_tab(k).last_update_date := sysdate;
823 g_tmp_tab(k).last_updated_by :=
824 g_change_rec.last_updated_by;
825
826 END IF;
827
828 -- add new row
829 K:=K+1;
830 g_tmp_tab(k):=g_change_rec;
831
832 IF (v_end_record > trunc(v_end_record)) THEN
833 -- change ends on a gap,
834 j:=trunc(v_end_record);
835 ELSIF (v_end_record < i+1) THEN
836 -- go the where the set record ends, and add row if needed
837 j:=v_end_record;
838 IF (g_change_rec.resource_end_date <
839 g_res_tab(j).resource_end_date ) or
840 (g_change_rec.resource_end_date is not null and
841 g_res_tab(j).resource_end_date is null) THEN
842
843 -- need to add row for the date change
844 K:=K+1;
845 g_tmp_tab(k):=g_res_tab(j);
846 g_tmp_tab(k).resource_start_date :=
847 g_change_rec.resource_end_date +1;
848 g_tmp_tab(k).status :=0;
849 g_tmp_tab(k).applied:=2;
850 g_tmp_tab(k).last_update_date := sysdate;
851 g_tmp_tab(k).last_updated_by :=
852 g_change_rec.last_updated_by;
853 END IF;
854 END IF;
855
856 END IF;
857
858 -- if change starts on a gap
859 IF (v_start_record > j) and (v_start_record < j+1) THEN
860 -- add new row
861 K:=K+1;
862 g_tmp_tab(k):=g_change_rec;
863
864 IF (v_end_record > trunc(v_end_record)) THEN
865 -- change ends on a gap,
866 j:=trunc(v_end_record);
867 ELSIF (v_end_record < i+1) THEN
868 -- go to where the set record ends, and add row if needed
869 j:=v_end_record;
870 IF (g_change_rec.resource_end_date <
871 g_res_tab(j).resource_end_date ) or
872 (g_change_rec.resource_end_date is not null and
873 g_res_tab(j).resource_end_date is null) THEN
874
875 -- need to add row for the date change
876 K:=K+1;
877 g_tmp_tab(k):=g_res_tab(j);
878 g_tmp_tab(k).resource_start_date :=
879 g_change_rec.resource_end_date +1;
880 g_tmp_tab(k).status :=0;
881 g_tmp_tab(k).applied:=2;
882 g_tmp_tab(k).last_update_date := sysdate;
883 g_tmp_tab(k).last_updated_by :=
884 g_change_rec.last_updated_by;
885 END IF;
886 END IF;
887 END IF;
888
889 j:=g_res_tab.next(j);
890
891 END LOOP;
892 END IF;
893
894 -- if the set record starts outside the range
895 i:=g_res_tab.LAST;
896 IF (v_start_record = i+1 ) THEN
897 -- need to add row for the date change
898 K:=K+1;
899 g_tmp_tab(k):=g_change_rec;
900 END IF;
901
902 END IF;
903
904 ELSE --user enters a resource which is not in crp_available_resource table
905
906 g_tmp_tab.delete;
907 g_tmp_tab(1) := g_change_rec;
908 END IF;
909
910 g_res_tab :=g_tmp_tab;
911
912 END calculate_change;
913
914
915 ------------------------------------------------------------------------
916 --to update crp_available_resources table
917 -----------------------------------------------------------------------------
918 PROCEDURE update_table IS
919 m INTEGER;
920 BEGIN
921 if g_resource_exist then
922 delete crp_available_resources
923 where compile_designator = g_compile_designator
924 and organization_id = g_org_id
925 AND ( (line_id = g_line_id)
926 OR ( (line_id IS NULL) AND (g_line_id IS NULL)))
927 AND ( (department_id = g_department_id)
928 OR ( (department_id IS NULL) AND (g_department_id IS NULL)))
929 AND ( (resource_id = g_resource_id)
930 OR ( (resource_id IS NULL) AND (g_resource_id IS NULL)));
931 end if;
932
933 For m in 1 .. g_res_tab.LAST LOOP
934
935 -- only insert the resources with positive quantity
936 IF (g_res_tab(m).resource_hours > 0 AND
937 g_res_tab(m).resource_units > 0) OR
938 g_res_tab(m).max_rate >0 THEN
939
940 INSERT INTO crp_available_resources
941 (compile_designator,
942 organization_id,
943 line_id,
944 department_id,
945 resource_id,
946 resource_hours,
947 max_rate,
948 resource_units,
949 resource_start_date,
950 resource_end_date,
951 status,
952 applied,
953 updated,
954 last_update_date,
955 last_updated_by,
956 creation_date,
957 created_by,
958 last_update_login)
959 VALUES
960 (g_compile_designator,
961 g_res_tab(m).organization_id,
962 g_res_tab(m).line_id,
963 g_res_tab(m).department_id,
964 g_res_tab(m).resource_id,
965 decode( g_res_tab(m).department_id, null, 0,
966 greatest(0,least(24,g_res_tab(m).resource_hours))),
967 greatest(0,g_res_tab(m).max_rate),
968 decode( g_res_tab(m).department_id, null, 1,
969 greatest(0,round(g_res_tab(m).resource_units,6))),
970 g_res_tab(m).resource_start_date,
971 g_res_tab(m).resource_end_date,
972 g_res_tab(m).status,
973 g_res_tab(m).applied,
974 g_res_tab(m).updated,
975 g_res_tab(m).last_update_date,
976 g_res_tab(m).last_updated_by,
977 g_res_tab(m).creation_date,
978 g_res_tab(m).created_by,
979 g_res_tab(m).last_update_login);
980 END IF;
981 END LOOP;
982
983 END update_table;
984
985 END;
[Home] [Help]