1 PACKAGE BODY MRP_WB_BUCKET_DATES_SC AS
2 /* $Header: MRPPWBBB.pls 115.10 2004/04/05 21:52:03 skanta ship $ */
3
4 DAILY_BUCKET CONSTANT INTEGER := 1;
5 WEEKLY_BUCKET CONSTANT INTEGER := 2;
6 PERIODIC_BUCKET CONSTANT INTEGER := 3;
7 --
8 HP_DAILY_BUCKET CONSTANT INTEGER := 4;
9 HP_WEEKLY_BUCKET CONSTANT INTEGER := 5;
10 HP_PERIODIC_BUCKET CONSTANT INTEGER := 6;
11
12 MAX_BUCKETS CONSTANT INTEGER := 36;
13 var_plan_start_date DATE;
14
15 -- ==============================================================
16 -- This procedure populates one row in MRP_WORKBENCH_BUCKET_DATES
17 -- ==============================================================
18 PROCEDURE populate_row(arg_organization_id IN NUMBER,
19 arg_planned_organization IN NUMBER,
20 arg_compile_designator IN VARCHAR2,
21 arg_bucket_type IN NUMBER,
22 arg_bucket_desc IN VARCHAR2 DEFAULT NULL,
23 arg_num_days IN NUMBER DEFAULT NULL,
24 arg_num_weeks IN NUMBER DEFAULT NULL) IS
25
26 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
27
28 var_calendar_code VARCHAR2(10); -- Calendar Code
29 var_exception_set_id NUMBER; -- Exception set id
30 var_num_days NUMBER; -- Number of days
31 var_num_weeks NUMBER; -- Number of weeks
32 var_num_periods NUMBER; -- Number of periods
33 var_user_id NUMBER; -- User id
34 var_the_date DATE; -- Last fetched date
35 counter BINARY_INTEGER := 0; -- Array counter
36
37 -- -------------------------------------------------------------------
38 -- Date array to store days/weeks/periods this array will be used to
39 -- insert into MRP_WORKBENCH_BUCKET_DATES. It will have the following
40 -- data in it
41 -- 1 ... arg_num_days Daily
42 -- arg_num_days + 1 ... arg_num_days + arg_num_weeks Weekly
43 -- arg_num_daye + arg_num_weeks + 1 ... MAX_BUCKETS + 1 Periodic
44 -- -------------------------------------------------------------------
45 var_dates CALENDAR_DATE;
46 var_dates1 CALENDAR_DATE;
47
48
49 BEGIN
50 /* $Header: MRPPWBBB.pls 115.10 2004/04/05 21:52:03 skanta ship $ */
51
52 -- -------------------------
53 -- Get the calendat defaults
54 -- -------------------------
55 -- dbms_output.put_line('Getting defaults');
56
57 mrp_calendar.select_calendar_defaults(arg_planned_organization,
58 var_calendar_code,
59 var_exception_set_id);
60 --dbms_output.put_line('Got defaults');
61
62 -- ----------------------------------------------
63 -- Figure out each number of buckets of each type
64 -- ----------------------------------------------
65 IF (arg_bucket_type = DAILY_BUCKET OR arg_bucket_type = HP_DAILY_BUCKET )
66 OR (arg_bucket_type = -1 OR arg_bucket_type = -4) THEN
67
68 --dbms_output.put_line('Creating daily buckets');
69 var_num_days := MAX_BUCKETS;
70 var_num_weeks := 0;
71 var_num_periods := 0;
72 ELSIF (arg_bucket_type = WEEKLY_BUCKET OR arg_bucket_type = HP_WEEKLY_BUCKET)
73 OR (arg_bucket_type = -2 OR arg_bucket_type = -5) THEN
74
75 --dbms_output.put_line('Creating weekly buckets');
76 var_num_days := 0;
77 var_num_weeks := MAX_BUCKETS;
78 var_num_periods := 0;
79 ELSIF (arg_bucket_type = PERIODIC_BUCKET OR arg_bucket_type = HP_PERIODIC_BUCKET)
80 OR (arg_bucket_type = -3 OR arg_bucket_type = -6 ) THEN
81
82 --dbms_output.put_line('Creating periodic buckets');
83 var_num_days := 0;
84 var_num_weeks := 0;
85 var_num_periods := MAX_BUCKETS;
86 ELSE
87
88 --dbms_output.put_line('Creating customized buckets');
89 DECLARE dummy NUMBER;
90 BEGIN
91 SELECT count(*)
92 INTO dummy
93 FROM mfg_lookups
94 WHERE lookup_type = 'MRP_WORKBENCH_BUCKET_TYPE'
95 AND lookup_code = arg_bucket_type;
96
97 IF dummy = 0 THEN
98 -- -------------------------------------
99 -- The bucket type is not in MFG_LOOKUPS
100 -- -------------------------------------
101 --dbms_output.put_line('Creating new lookup');
102 INSERT INTO mfg_lookups
103 (lookup_type,
104 lookup_code,
105 last_update_date,
106 last_updated_by,
107 creation_date,
108 created_by,
109 meaning,
110 enabled_flag)
111 VALUES
112 ('MRP_WORKBENCH_BUCKET_TYPE',
113 arg_bucket_type,
114 SYSDATE,
115 -1,
116 SYSDATE,
117 -1,
118 NVL(arg_bucket_desc , '???'),
119 'Y');
120 END IF;
121 END;
122
123 -- ----------------------------------------------------
124 -- If num days is negative then make it zero.
125 -- If num days is greater than MAX_BUCKETS then make it
126 -- MAX_BUCKETS.
127 -- ----------------------------------------------------
128 var_num_days := LEAST(GREATEST(arg_num_days,0), MAX_BUCKETS);
129
130 -- ------------------------------------------------------
131 -- If num weeks is negative then make it zero.
132 -- If num weeks is greater that MAX_BUCKETS minus num days
133 -- make it MAX_BUCKETS minus num days
134 -- ------------------------------------------------------
135 var_num_weeks := LEAST(GREATEST(arg_num_weeks,0),MAX_BUCKETS-var_num_days);
136
137 -- ------------------------------
138 -- Make num periods the left over
139 -- ------------------------------
140 var_num_periods := MAX_BUCKETS - var_num_days - var_num_weeks;
141
142 END IF;
143
144 --dbms_output.put_line('Number of days :'||var_num_days);
145 --dbms_output.put_line('Number of weeks :'||var_num_weeks);
146 --dbms_output.put_line('Number of periods:'||var_num_periods);
147
148 var_the_date := var_plan_start_date;
149 --dbms_output.put_line('var_the_date: '||var_the_date);
150
151
152 IF var_num_days > 0 THEN
153
154 -- --------------------------------------------------------------------
155 -- The DAYS cursor gets at most num_days + 1 workdays from the calendar
156 -- beginning at the plan start_date
157 -- --------------------------------------------------------------------
158 DECLARE CURSOR DAYS IS
159 SELECT calendar_date
160 FROM bom_calendar_dates
161 WHERE calendar_code = var_calendar_code
162 AND exception_set_id = var_exception_set_id
163 AND seq_num IS NOT NULL
164 AND calendar_date >= var_the_date
165 -- AND rownum <= var_num_days + 1
166 ORDER BY calendar_date;
167 --
168 -- bug 3468984
169 CURSOR HP_DAYS IS
170 SELECT calendar_date
171 FROM bom_calendar_dates
172 WHERE calendar_code = var_calendar_code
173 AND exception_set_id = var_exception_set_id
174 --AND seq_num IS NOT NULL
175 AND calendar_date >= var_the_date
176 -- AND rownum <= var_num_days + 1
177 ORDER BY calendar_date;
178
179 BEGIN
180 OPEN DAYS;
181 -- -------------------------------------------------------------
182 -- Get the days portion and place it in the front section of the
183 -- array var_the_dates
184 -- -------------------------------------------------------------
185 LOOP
186 counter := counter + 1;
187 FETCH DAYS INTO var_dates(counter);
188 EXIT WHEN DAYS%NOTFOUND;
189 IF counter > var_num_days +1 THEN
190 EXIT;
191 END IF;
192 var_the_date := var_dates(counter);
193 END LOOP;
194 counter := counter - 1;
195 CLOSE DAYS;
196 --
197 counter := 0;
198 var_the_date := var_plan_start_date;
199 OPEN HP_DAYS;
200 LOOP
201 counter := counter + 1;
202 FETCH HP_DAYS INTO var_dates1(counter);
203 EXIT WHEN HP_DAYS%NOTFOUND;
204 IF counter > var_num_days +1 THEN
205 EXIT;
206 END IF;
207 var_the_date := var_dates1(counter);
208 END LOOP;
209 counter := counter - 1;
210 CLOSE HP_DAYS;
211 END;
212 END IF; -- var_num_days
213
214 IF var_num_weeks > 0 THEN
215
216 -- -----------------------------------------------------------------
217 -- The WEEKS cursor gets at most num_weeks workdays from the calendar
218 -- beginning at var_the_date.
219 --
220 -- NOTE
221 -- If we are doing only weeks then the first week should be the
222 -- beginning of the week that include var_the_date
223 -- -----------------------------------------------------------------
224 DECLARE CURSOR WEEKS IS
225 SELECT week_start_date
226 FROM bom_cal_week_start_dates
227 WHERE calendar_code = var_calendar_code
228 AND exception_set_id = var_exception_set_id
229 AND seq_num IS NOT NULL
230 AND week_start_date >= var_the_date + DECODE(counter, 0, 0, 1)
231 -- AND rownum <= var_num_weeks + DECODE(counter, 0, 1, 0)
232 ORDER BY week_start_date;
233 --
234 CURSOR HP_WEEKS IS
235 SELECT week_start_date
236 FROM bom_cal_week_start_dates
237 WHERE calendar_code = var_calendar_code
238 AND exception_set_id = var_exception_set_id
239 --AND seq_num IS NOT NULL
240 AND week_start_date >= var_the_date + DECODE(counter, 0, 0, 1)
241 -- AND rownum <= var_num_weeks + DECODE(counter, 0, 1, 0)
242 ORDER BY week_start_date;
243
244 BEGIN
245
246 IF counter = 0 THEN
247
248 var_the_date := mrp_calendar.prev_work_day(arg_planned_organization,
249 WEEKLY_BUCKET,
250 var_the_date);
251 END IF;
252 OPEN WEEKS;
253 -- --------------------------------------------------------------
254 -- Get the weeks portion and place it in the front section of the
255 -- array var_the_dates
256 -- --------------------------------------------------------------
257 LOOP
258 counter := counter + 1;
259 FETCH WEEKS INTO var_dates(counter);
260 EXIT WHEN WEEKS%NOTFOUND;
261 IF counter > var_num_weeks +1 THEN
262 EXIT;
263 END IF;
264 var_the_date := var_dates(counter);
265 END LOOP;
266 CLOSE WEEKS;
267 counter := counter - 1;
268 --
269 counter := 0;
270 var_the_date := var_plan_start_date;
271 var_the_date := mrp_calendar.prev_work_day(arg_planned_organization,
272 WEEKLY_BUCKET,
273 var_the_date);
274 OPEN HP_WEEKS;
275 LOOP
276 counter := counter + 1;
277 FETCH HP_WEEKS INTO var_dates1(counter);
278 EXIT WHEN HP_WEEKS%NOTFOUND;
279 IF counter > var_num_weeks +1 THEN
280 EXIT;
281 END IF;
282 var_the_date := var_dates1(counter);
283 END LOOP;
284 CLOSE HP_WEEKS;
285 counter := counter - 1;
286 END;
287 END IF; -- var_num_weeks
288
289 IF var_num_periods > 0 THEN
290
291 -- -----------------------------------------------------------------
292 -- The WEEKS cursor gets at most num_days workdays from the calendar
293 -- beginning at the plan start_date
294 -- -----------------------------------------------------------------
295 DECLARE CURSOR PERIODS IS
296 SELECT period_start_date
297 FROM bom_period_start_dates
298 WHERE calendar_code = var_calendar_code
299 AND exception_set_id = var_exception_set_id
300 AND period_start_date >= var_the_date + DECODE(counter, 0, 0, 1)
301 -- AND rownum <= var_num_periods + DECODE(counter, 0, 1, 0)
302 ORDER BY period_start_date;
303
304 BEGIN
305
306 IF counter = 0 THEN
307 var_the_date := mrp_calendar.prev_work_day(arg_planned_organization,
308 PERIODIC_BUCKET,
309 var_the_date);
310 END IF;
311
312 OPEN PERIODS;
313 -- --------------------------------------------------------------
314 -- Get the weeks portion and place it in the front section of the
315 -- array var_the_dates
316 -- --------------------------------------------------------------
317 LOOP
318 counter := counter + 1;
319 FETCH PERIODS INTO var_dates(counter);
320 EXIT WHEN PERIODS%NOTFOUND;
321 IF counter > var_num_periods +1 THEN
322 EXIT;
323 END IF;
324 var_the_date := var_dates(counter);
325 END LOOP;
326 counter := counter - 1;
327 CLOSE PERIODS;
328 --
329 counter := 0;
330 var_the_date := var_plan_start_date;
331 var_the_date := mrp_calendar.prev_work_day(arg_planned_organization,
332 PERIODIC_BUCKET,
333 var_the_date);
334
335 OPEN PERIODS;
336 LOOP
337 counter := counter + 1;
338 FETCH PERIODS INTO var_dates1(counter);
339 EXIT WHEN PERIODS%NOTFOUND;
340 IF counter > var_num_periods +1 THEN
341 EXIT;
342 END IF;
343 var_the_date := var_dates1(counter);
344 END LOOP;
345 counter := counter - 1;
346 CLOSE PERIODS;
347
348 END;
349 END IF; -- var_num_periods
350
351 IF counter < MAX_BUCKETS + 1 THEN
352
353 -- -----------------------------------------------------------
354 -- This means that there weren't enough days ,weeks and months
355 -- to fill out all the columns in the table. We will set the
356 -- remaining columns with one day increment from the last date
357 -- -----------------------------------------------------------
358 --dbms_output.put_line('There were not enough dates');
359 --dbms_output.put_line('The date counter is: '||counter);
360 IF counter <= 1 THEN
361
362 -- ----------------------------------------------------
363 -- We found no dates at all. Set the first entry in the
364 -- var_dates array to avoid an out of bounds exception
365 -- in the loop bellow.
366 -- ----------------------------------------------------
367 --dbms_output.put_line('There were no dates');
368 counter := 1;
369 var_dates(counter) := var_the_date;
370 var_dates1(counter) := var_the_date;
371 --dbms_output.put_line('Set first date to '||var_dates(counter));
372 END IF;
373
374 FOR j IN (counter + 1) .. MAX_BUCKETS + 1 LOOP
375
376 -- ----------------------------------------------------------
377 -- Set the current element of the var_dates array to one plus
378 -- the previous elemnt
379 -- ----------------------------------------------------------
380 var_dates(j) := var_dates(j - 1) + 1;
381 var_dates1(j) := var_dates(j - 1) + 1;
382 --dbms_output.put_line('Set date '||j||' to:'||var_dates(j));
383 END LOOP;
384 END IF; -- < MAX_BUCKETS
385
386 -- ----------------------------------------------------------
387 -- Insert the var_dates array into MRP_WORKBENCH_BUCKET_DATES
388 -- ----------------------------------------------------------
389 var_user_id := FND_PROFILE.VALUE('USER_ID');
390 IF arg_bucket_type in (1,2,3,-1,-2,-3) THEN
391 INSERT INTO mrp_workbench_bucket_dates
392 (organization_id,
393 planned_organization,
394 compile_designator,
395 bucket_type,
396 last_update_date,
397 last_updated_by,
398 creation_date,
399 created_by,
400 date1, date2, date3, date4, date5, date6,
401 date7, date8, date9, date10, date11, date12,
402 date13, date14, date15, date16, date17, date18,
403 date19, date20, date21, date22, date23, date24,
404 date25, date26, date27, date28, date29, date30,
405 date31, date32, date33, date34, date35, date36,
406 date37)
407 VALUES
408 (arg_organization_id,
409 arg_planned_organization,
410 arg_compile_designator,
411 arg_bucket_type,
412 SYSDATE,
413 -1, -- var_user_id,
414 SYSDATE,
415 -1, -- var_user_id,
416 var_dates(1), var_dates(2), var_dates(3), var_dates(4),
417 var_dates(5), var_dates(6), var_dates(7), var_dates(8),
418 var_dates(9), var_dates(10), var_dates(11), var_dates(12),
419 var_dates(13), var_dates(14), var_dates(15), var_dates(16),
420 var_dates(17), var_dates(18), var_dates(19), var_dates(20),
421 var_dates(21), var_dates(22), var_dates(23), var_dates(24),
422 var_dates(25), var_dates(26), var_dates(27), var_dates(28),
423 var_dates(29), var_dates(30), var_dates(31), var_dates(32),
424 var_dates(33), var_dates(34), var_dates(35), var_dates(36),
425 var_dates(37));
426 --
427 ELSE
428 INSERT INTO mrp_workbench_bucket_dates
429 (organization_id,
430 planned_organization,
431 compile_designator,
432 bucket_type,
433 last_update_date,
434 last_updated_by,
435 creation_date,
436 created_by,
437 date1, date2, date3, date4, date5, date6,
438 date7, date8, date9, date10, date11, date12,
439 date13, date14, date15, date16, date17, date18,
440 date19, date20, date21, date22, date23, date24,
441 date25, date26, date27, date28, date29, date30,
442 date31, date32, date33, date34, date35, date36,
443 date37)
444 VALUES
445 (arg_organization_id,
446 arg_planned_organization,
447 arg_compile_designator,
448 arg_bucket_type,
449 SYSDATE,
450 -1, -- var_user_id,
451 SYSDATE,
452 -1, -- var_user_id,
453 var_dates1(1), var_dates1(2), var_dates1(3), var_dates1(4),
454 var_dates1(5), var_dates1(6), var_dates1(7), var_dates1(8),
455 var_dates1(9), var_dates1(10), var_dates1(11), var_dates1(12),
456 var_dates1(13), var_dates1(14), var_dates1(15), var_dates1(16),
457 var_dates1(17), var_dates1(18), var_dates1(19), var_dates1(20),
458 var_dates1(21), var_dates1(22), var_dates1(23), var_dates1(24),
459 var_dates1(25), var_dates1(26), var_dates1(27), var_dates1(28),
460 var_dates1(29), var_dates1(30), var_dates1(31), var_dates1(32),
461 var_dates1(33), var_dates1(34), var_dates1(35), var_dates1(36),
462 var_dates1(37));
463 END IF;
464 END populate_row;
465
466
467 -- ====================================================================
468 -- Create all rows in MRP_WORKBENCH_BUCKET_DATES
469 -- ====================================================================
470 PROCEDURE populate_bucket_dates ( arg_organization_id IN NUMBER,
471 arg_compile_designator IN VARCHAR2,
472 arg_planned_organization IN NUMBER DEFAULT NULL) IS
473
474 var_current_start_date DATE; -- Current first date in
475 -- MRP_WORKBENCH_BUCKET_DATES
476 recreate BOOLEAN := FALSE;
477 var_curr_start_date1 DATE; -- Current first date in MRP_WORKBENCH_BUCKET_DATES
478 -- for current data.
479 recreate1 BOOLEAN := FALSE;
480
481 BEGIN
482
483 -- --------------------------------------------------
484 -- First figure out if we need to do recreate rows in
485 -- MRP_WORKBENCH_BUCKET_DATES
486 -- apatanka -- Fix for Bug No. 353458
487 -- MRP workbench duplicate rows.
488 -- --------------------------------------------------
489
490 BEGIN
491
492 SELECT date1
493 INTO var_current_start_date
494 FROM mrp_workbench_bucket_dates
495 WHERE compile_designator = arg_compile_designator
496 AND organization_id = arg_organization_id
497 -- AND planned_organization = arg_planned_organization
498 AND NVL(planned_organization,organization_id) = arg_planned_organization
499 AND bucket_type = HP_DAILY_BUCKET;
500
501
502 EXCEPTION WHEN NO_DATA_FOUND THEN
503
504 -- -------------------------------------------
505 -- There are no rows so we need to create them
506 -- -------------------------------------------
507 recreate := TRUE;
508 -- dbms_output.put_line('No rows exist. Will recreate them');
509
510 END;
511
512 -- ------------------------------------------------------
513 -- We will select the first date for current data buckets
514 -- -------------------------------------------------------
515 BEGIN
516
517 SELECT date1
518 INTO var_curr_start_date1
519 FROM mrp_workbench_bucket_dates
520 WHERE compile_designator = arg_compile_designator
521 AND NVL(planned_organization, organization_id) =
522 arg_planned_organization
523 AND bucket_type = -4;
524
525 -- dbms_output.put_line('Current Start Date for Current Data is '||
526 -- var_curr_start_date1);
527 EXCEPTION WHEN NO_DATA_FOUND THEN
528 recreate1 := TRUE;
529 --dbms_output.put_line('No rows exist for Current Data Buckets.
530 -- Will recreate them');
531 END;
532
533
534 SELECT TRUNC(plan_start_date)
535 INTO var_plan_start_date
536 FROM mrp_plans
537 WHERE compile_designator = arg_compile_designator
538 AND organization_id = arg_organization_id;
539
540 var_plan_start_date := mrp_calendar.next_work_day(arg_planned_organization,
541 DAILY_BUCKET,
542 var_plan_start_date);
543
544
545 IF (var_plan_start_date <> var_current_start_date) THEN
546
547 -- ------------------------------------------------------------------
548 -- The plan start date has changed since the last time the rows where
549 -- created so we need to recreate them
550 -- ------------------------------------------------------------------
551 recreate := TRUE;
552
553 END IF;
554
555 IF (var_curr_start_date1 <> TRUNC(sysdate)) THEN
556 -- -------------------------------------------------------------------
557 -- The start date for current data buckets is changed so we need to
558 -- recreate them
559 -- -------------------------------------------------------------------
560 recreate1 := TRUE;
561 END IF;
562
563
564 IF recreate = TRUE THEN
565
566 DELETE mrp_workbench_bucket_dates
567 WHERE compile_designator = arg_compile_designator
568 AND organization_id = arg_organization_id
569 AND NVL(planned_organization,organization_id) = arg_planned_organization
570 AND bucket_type IN (DAILY_BUCKET, WEEKLY_BUCKET, PERIODIC_BUCKET,HP_DAILY_BUCKET,HP_WEEKLY_BUCKET,HP_PERIODIC_BUCKET);
571
572 -- apatanka
573 -- AND planned_organization = arg_planned_organization;
574 --dbms_output.put_line('Blah');
575
576 DELETE mrp_material_plans
577 WHERE compile_designator = arg_compile_designator
578 AND organization_id = arg_organization_id;
579 --dbms_output.put_line('Blah');
580
581
582 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
583 arg_planned_organization,
584 arg_compile_designator,
585 DAILY_BUCKET);
586 --dbms_output.put_line('Blah');
587
588 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
589 arg_planned_organization,
590 arg_compile_designator,
591 WEEKLY_BUCKET);
592
593 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
594 arg_planned_organization,
595 arg_compile_designator,
596 PERIODIC_BUCKET);
597 --
598 -- Populate data for HP
599 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
600 arg_planned_organization,
601 arg_compile_designator,
602 HP_DAILY_BUCKET);
603
604 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
605 arg_planned_organization,
606 arg_compile_designator,
607 HP_WEEKLY_BUCKET);
608
609 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
610 arg_planned_organization,
611 arg_compile_designator,
612 HP_PERIODIC_BUCKET);
613
614 mrp_custom_wb.mrp_custom_wb_bucket_dates(arg_organization_id,
615 arg_compile_designator);
616
617 --ELSE
618 -- ----------------------------------
619 -- Return because nothing has changed
620 -- ----------------------------------
621 --dbms_output.put_line('Nothing has changed');
622 END IF;
623
624 -- ------------------------------------------
625 -- We will recreate buckets for current data
626 -- ------------------------------------------
627 IF recreate1 = TRUE THEN
628
629 -- ----------------------------------------------
630 -- Reset var_plan_start_date to sysdate
631 -- ----------------------------------------------
632 var_plan_start_date := TRUNC(sysdate);
633 BEGIN
634 DELETE mrp_workbench_bucket_dates
635 WHERE compile_designator = arg_compile_designator
636 AND organization_id = arg_organization_id
637 AND NVL(planned_organization,organization_id) =
638 arg_planned_organization
639 AND bucket_type IN (-1, -2, -3,-4,-5,-6);
640 EXCEPTION WHEN NO_DATA_FOUND THEN
641 NULL;
642 END;
643
644 BEGIN
645 DELETE mrp_material_plans
646 WHERE compile_designator = arg_compile_designator
647 AND organization_id = arg_organization_id;
648 EXCEPTION WHEN NO_DATA_FOUND THEN
649 NULL;
650 END;
651
652 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
653 arg_planned_organization,
654 arg_compile_designator,
655 -1);
656
657 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
658 arg_planned_organization,
659 arg_compile_designator,
660 -2);
661
662 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
663 arg_planned_organization,
664 arg_compile_designator,
665 -3);
666
667 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
668 arg_planned_organization,
669 arg_compile_designator,
670 -4);
671
672 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
673 arg_planned_organization,
674 arg_compile_designator,
675 -5);
676
677 mrp_wb_bucket_dates_sc.populate_row(arg_organization_id,
678 arg_planned_organization,
679 arg_compile_designator,
680 -6);
681
682 mrp_custom_wb.mrp_custom_wb_bucket_dates(arg_organization_id,
683 arg_compile_designator);
684 --ELSE
685
686 --dbms_output.put_line('Nothing has changed for Current data');
687
688 END IF;
689
690 COMMIT WORK;
691 END populate_bucket_dates;
692
693 END MRP_WB_BUCKET_DATES_sc;
[Home] [Help]