1 PACKAGE BODY MRP_WB_BUCKET_DATES AS
2 /* $Header: MRPWBBDB.pls 115.1 99/07/16 12:42:15 porting ship $ */
3
4 DAILY_BUCKET CONSTANT INTEGER := 1;
5 WEEKLY_BUCKET CONSTANT INTEGER := 2;
6 PERIODIC_BUCKET CONSTANT INTEGER := 3;
7 MAX_BUCKETS CONSTANT INTEGER := 36;
8 var_plan_start_date DATE;
9
10 -- ==============================================================
11 -- This procedure populates one row in MRP_WORKBENCH_BUCKET_DATES
12 -- ==============================================================
13 PROCEDURE populate_row(arg_organization_id IN NUMBER,
14 arg_compile_designator IN VARCHAR2,
15 arg_bucket_type IN NUMBER,
16 arg_bucket_desc IN VARCHAR2 DEFAULT NULL,
17 arg_num_days IN NUMBER DEFAULT NULL,
18 arg_num_weeks IN NUMBER DEFAULT NULL) IS
19
20 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
21
22 var_calendar_code VARCHAR2(10); -- Calendar Code
23 var_exception_set_id NUMBER; -- Exception set id
24 var_num_days NUMBER; -- Number of days
25 var_num_weeks NUMBER; -- Number of weeks
26 var_num_periods NUMBER; -- Number of periods
27 var_user_id NUMBER; -- User id
28 var_the_date DATE; -- Last fetched date
29 counter BINARY_INTEGER := 0; -- Array counter
30
31 -- -------------------------------------------------------------------
32 -- Date array to store days/weeks/periods this array will be used to
33 -- insert into MRP_WORKBENCH_BUCKET_DATES. It will have the following
34 -- data in it
35 -- 1 ... arg_num_days Daily
36 -- arg_num_days + 1 ... arg_num_days + arg_num_weeks Weekly
37 -- arg_num_daye + arg_num_weeks + 1 ... MAX_BUCKETS + 1 Periodic
38 -- -------------------------------------------------------------------
39 var_dates CALENDAR_DATE;
40
41 BEGIN
42
43 -- -------------------------
44 -- Get the calendat defaults
45 -- -------------------------
46 mrp_calendar.select_calendar_defaults(arg_organization_id,
47 var_calendar_code,
48 var_exception_set_id);
49
50 -- ----------------------------------------------
51 -- Figure out each number of buckets of each type
52 -- ----------------------------------------------
53 IF (arg_bucket_type = DAILY_BUCKET) OR (arg_bucket_type = -1) THEN
54
55 dbms_output.put_line('Creating daily buckets');
56 var_num_days := MAX_BUCKETS;
57 var_num_weeks := 0;
58 var_num_periods := 0;
59 ELSIF (arg_bucket_type = WEEKLY_BUCKET) OR (arg_bucket_type = -2) THEN
60
61 dbms_output.put_line('Creating weekly buckets');
62 var_num_days := 0;
63 var_num_weeks := MAX_BUCKETS;
64 var_num_periods := 0;
65 ELSIF (arg_bucket_type = PERIODIC_BUCKET) OR (arg_bucket_type = -3) THEN
66
67 dbms_output.put_line('Creating periodic buckets');
68 var_num_days := 0;
69 var_num_weeks := 0;
70 var_num_periods := MAX_BUCKETS;
71 ELSE
72
73 dbms_output.put_line('Creating customized buckets');
74 DECLARE dummy NUMBER;
75 BEGIN
76 SELECT count(*)
77 INTO dummy
78 FROM mfg_lookups
79 WHERE lookup_type = 'MRP_WORKBENCH_BUCKET_TYPE'
80 AND lookup_code = arg_bucket_type;
81
82 IF dummy = 0 THEN
83 -- -------------------------------------
84 -- The bucket type is not in MFG_LOOKUPS
85 -- -------------------------------------
86 dbms_output.put_line('Creating new lookup');
87 INSERT INTO mfg_lookups
88 (lookup_type,
89 lookup_code,
90 last_update_date,
91 last_updated_by,
92 creation_date,
93 created_by,
94 meaning,
95 enabled_flag)
96 VALUES
97 ('MRP_WORKBENCH_BUCKET_TYPE',
98 arg_bucket_type,
99 SYSDATE,
100 -1,
101 SYSDATE,
102 -1,
103 NVL(arg_bucket_desc , '???'),
104 'Y');
105 END IF;
106 END;
107
108 -- ----------------------------------------------------
109 -- If num days is negative then make it zero.
110 -- If num days is greater than MAX_BUCKETS then make it
111 -- MAX_BUCKETS.
112 -- ----------------------------------------------------
113 var_num_days := LEAST(GREATEST(arg_num_days,0), MAX_BUCKETS);
114
115 -- ------------------------------------------------------
116 -- If num weeks is negative then make it zero.
117 -- If num weeks is greater that MAX_BUCKETS minus num days
118 -- make it MAX_BUCKETS minus num days
119 -- ------------------------------------------------------
120 var_num_weeks := LEAST(GREATEST(arg_num_weeks,0),MAX_BUCKETS-var_num_days);
121
122 -- ------------------------------
123 -- Make num periods the left over
124 -- ------------------------------
125 var_num_periods := MAX_BUCKETS - var_num_days - var_num_weeks;
126
127 END IF;
128
129 dbms_output.put_line('Number of days :'||var_num_days);
130 dbms_output.put_line('Number of weeks :'||var_num_weeks);
131 dbms_output.put_line('Number of periods:'||var_num_periods);
132
133 var_the_date := var_plan_start_date;
134
135 IF var_num_days > 0 THEN
136
137 -- --------------------------------------------------------------------
138 -- The DAYS cursor gets at most num_days + 1 workdays from the calendar
139 -- beginning at the plan start_date
140 -- --------------------------------------------------------------------
141 DECLARE CURSOR DAYS IS
142 SELECT calendar_date
143 FROM bom_calendar_dates
144 WHERE calendar_code = var_calendar_code
145 AND exception_set_id = var_exception_set_id
146 AND seq_num IS NOT NULL
147 AND calendar_date >= var_the_date
148 AND rownum <= var_num_days + 1
149 ORDER BY calendar_date;
150
151 BEGIN
152
153 OPEN DAYS;
154 dbms_output.put_line('Getting the daily buckets');
155 -- -------------------------------------------------------------
156 -- Get the days portion and place it in the front section of the
157 -- array var_the_dates
158 -- -------------------------------------------------------------
159 LOOP
160 counter := counter + 1;
161 FETCH DAYS INTO var_dates(counter);
162 EXIT WHEN DAYS%NOTFOUND;
163 var_the_date := var_dates(counter);
164 dbms_output.put_line('Got date '||counter||': '||var_dates(counter));
165 END LOOP;
166 counter := counter - 1;
167 CLOSE DAYS;
168 END;
169 END IF; -- var_num_days
170
171 IF var_num_weeks > 0 THEN
172
173 -- -----------------------------------------------------------------
174 -- The WEEKS cursor gets at most num_weeks workdays from the calendar
175 -- beginning at var_the_date.
176 --
177 -- NOTE
178 -- If we are doing only weeks then the first week should be the
179 -- beginning of the week that include var_the_date
180 -- -----------------------------------------------------------------
181 DECLARE CURSOR WEEKS IS
182 SELECT week_start_date
183 FROM bom_cal_week_start_dates
184 WHERE calendar_code = var_calendar_code
185 AND exception_set_id = var_exception_set_id
186 AND seq_num IS NOT NULL
187 AND week_start_date >= var_the_date + DECODE(counter, 0, 0, 1)
188 AND rownum <= var_num_weeks + DECODE(counter, 0, 1, 0)
189 ORDER BY week_start_date;
190
191 BEGIN
192
193 IF counter = 0 THEN
194
195 dbms_output.put_line('Weeks only');
196 var_the_date := mrp_calendar.prev_work_day(arg_organization_id,
197 WEEKLY_BUCKET,
198 var_the_date);
199 dbms_output.put_line('Reset var_the_date to:'||var_the_date);
200 END IF;
201 OPEN WEEKS;
202 dbms_output.put_line('Getting the weekly buckets');
203 -- --------------------------------------------------------------
204 -- Get the weeks portion and place it in the front section of the
205 -- array var_the_dates
206 -- --------------------------------------------------------------
207 LOOP
208 counter := counter + 1;
209 FETCH WEEKS INTO var_dates(counter);
210 EXIT WHEN WEEKS%NOTFOUND;
211 var_the_date := var_dates(counter);
212 dbms_output.put_line('Got date '||counter||': '||var_dates(counter));
213 END LOOP;
214 CLOSE WEEKS;
215 counter := counter - 1;
216 END;
217 END IF; -- var_num_weeks
218
219 IF var_num_periods > 0 THEN
220
221 -- -----------------------------------------------------------------
222 -- The WEEKS cursor gets at most num_days workdays from the calendar
223 -- beginning at the plan start_date
224 -- -----------------------------------------------------------------
225 DECLARE CURSOR PERIODS IS
226 SELECT period_start_date
227 FROM bom_period_start_dates
228 WHERE calendar_code = var_calendar_code
229 AND exception_set_id = var_exception_set_id
230 AND period_start_date >= var_the_date + DECODE(counter, 0, 0, 1)
231 AND rownum <= var_num_periods + DECODE(counter, 0, 1, 0)
232 ORDER BY period_start_date;
233
234 BEGIN
235
236 IF counter = 0 THEN
237 dbms_output.put_line('Periodic only');
238 var_the_date := mrp_calendar.prev_work_day(arg_organization_id,
239 PERIODIC_BUCKET,
240 var_the_date);
241 dbms_output.put_line('Reset var_the_date to:'||var_the_date);
242 END IF;
243
244 OPEN PERIODS;
245 dbms_output.put_line('Getting the periodic buckets');
246 -- --------------------------------------------------------------
247 -- Get the weeks portion and place it in the front section of the
248 -- array var_the_dates
249 -- --------------------------------------------------------------
250 LOOP
251 counter := counter + 1;
252 FETCH PERIODS INTO var_dates(counter);
253 EXIT WHEN PERIODS%NOTFOUND;
254 var_the_date := var_dates(counter);
255 dbms_output.put_line('Got date '||counter||': '||var_dates(counter));
256 END LOOP;
257 counter := counter - 1;
258 CLOSE PERIODS;
259 END;
260 END IF; -- var_num_periods
261
262 IF counter < MAX_BUCKETS + 1 THEN
263
264 -- -----------------------------------------------------------
265 -- This means that there weren't enough days ,weeks and months
266 -- to fill out all the columns in the table. We will set the
267 -- remaining columns with one day increment from the last date
268 -- -----------------------------------------------------------
269 dbms_output.put_line('There were not enough dates');
270 dbms_output.put_line('The date counter is: '||counter);
271 IF counter <= 1 THEN
272
273 -- ----------------------------------------------------
274 -- We found no dates at all. Set the first entry in the
275 -- var_dates array to avoid an out of bounds exception
276 -- in the loop bellow.
277 -- ----------------------------------------------------
278 dbms_output.put_line('There were no dates');
279 counter := 1;
280 var_dates(counter) := var_the_date;
281 dbms_output.put_line('Set first date to '||var_dates(counter));
282 END IF;
283
284 FOR j IN (counter + 1) .. MAX_BUCKETS + 1 LOOP
285
286 -- ----------------------------------------------------------
287 -- Set the current element of the var_dates array to one plus
288 -- the previous elemnt
289 -- ----------------------------------------------------------
290 var_dates(j) := var_dates(j - 1) + 1;
291 dbms_output.put_line('Set date '||j||' to:'||var_dates(j));
292 END LOOP;
293 END IF; -- < MAX_BUCKETS
294
295 -- ----------------------------------------------------------
296 -- Insert the var_dates array into MRP_WORKBENCH_BUCKET_DATES
297 -- ----------------------------------------------------------
298 var_user_id := FND_PROFILE.VALUE('USER_ID');
299 INSERT INTO mrp_workbench_bucket_dates
300 (organization_id,
301 compile_designator,
302 bucket_type,
303 last_update_date,
304 last_updated_by,
305 creation_date,
306 created_by,
307 date1, date2, date3, date4, date5, date6,
308 date7, date8, date9, date10, date11, date12,
309 date13, date14, date15, date16, date17, date18,
310 date19, date20, date21, date22, date23, date24,
311 date25, date26, date27, date28, date29, date30,
312 date31, date32, date33, date34, date35, date36,
313 date37)
314 VALUES
315 (arg_organization_id,
316 arg_compile_designator,
317 arg_bucket_type,
318 SYSDATE,
319 -1, -- var_user_id,
320 SYSDATE,
321 -1, -- var_user_id,
322 var_dates(1), var_dates(2), var_dates(3), var_dates(4),
323 var_dates(5), var_dates(6), var_dates(7), var_dates(8),
324 var_dates(9), var_dates(10), var_dates(11), var_dates(12),
325 var_dates(13), var_dates(14), var_dates(15), var_dates(16),
326 var_dates(17), var_dates(18), var_dates(19), var_dates(20),
327 var_dates(21), var_dates(22), var_dates(23), var_dates(24),
328 var_dates(25), var_dates(26), var_dates(27), var_dates(28),
329 var_dates(29), var_dates(30), var_dates(31), var_dates(32),
330 var_dates(33), var_dates(34), var_dates(35), var_dates(36),
331 var_dates(37));
332 END populate_row;
333
334
335 -- ====================================================================
336 -- Create all rows in MRP_WORKBENCH_BUCKET_DATES
337 -- ====================================================================
338 PROCEDURE populate_bucket_dates ( arg_organization_id IN NUMBER,
339 arg_compile_designator IN VARCHAR2) IS
340
341 var_current_start_date DATE; -- Current first date in
342 -- MRP_WORKBENCH_BUCKET_DATES
343 recreate BOOLEAN := FALSE;
344 var_curr_start_date1 DATE; -- Current first date in MRP_WORKBENCH_BUCKET_DATES
345 -- for current data.
346 recreate1 BOOLEAN := FALSE;
347 BEGIN
348
349 -- --------------------------------------------------
350 -- First figure out if we need to do recreate rows in
351 -- MRP_WORKBENCH_BUCKET_DATES
352 -- --------------------------------------------------
353
354 BEGIN
355
356 SELECT date1
357 INTO var_current_start_date
358 FROM mrp_workbench_bucket_dates
359 WHERE compile_designator = arg_compile_designator
360 AND organization_id = arg_organization_id
361 AND bucket_type = DAILY_BUCKET;
362
363 dbms_output.put_line('Current start date is '||var_current_start_date);
364
365 EXCEPTION WHEN NO_DATA_FOUND THEN
366
367 -- -------------------------------------------
368 -- There are no rows so we need to create them
369 -- -------------------------------------------
370 recreate := TRUE;
371 dbms_output.put_line('No rows exist. Will recreate them');
372
373 END;
374
375 -- ------------------------------------------------------
376 -- We will select the first date for current data buckets
377 -- -------------------------------------------------------
378 BEGIN
379
380 SELECT date1
381 INTO var_curr_start_date1
382 FROM mrp_workbench_bucket_dates
383 WHERE compile_designator = arg_compile_designator
384 AND organization_id = arg_organization_id
385 AND bucket_type = -1;
386
387 dbms_output.put_line('Current Start Date for Current Data is '||
388 var_curr_start_date1);
389 EXCEPTION WHEN NO_DATA_FOUND THEN
390 recreate1 := TRUE;
391 dbms_output.put_line('No rows exist for Current Data Buckets.
392 Will recreate them');
393 END;
394
395
396 SELECT TRUNC(plan_start_date)
397 INTO var_plan_start_date
398 FROM mrp_plans
399 WHERE compile_designator = arg_compile_designator
400 AND organization_id = arg_organization_id;
401
402 var_plan_start_date := mrp_calendar.next_work_day(arg_organization_id,
403 DAILY_BUCKET,
404 var_plan_start_date);
405
406 dbms_output.put_line('Plan start date is: '||var_plan_start_date);
407
408 IF (var_plan_start_date <> var_current_start_date) THEN
409
410 -- ------------------------------------------------------------------
411 -- The plan start date has changed since the last time the rows where
412 -- created so we need to recreate them
413 -- ------------------------------------------------------------------
414 recreate := TRUE;
415 dbms_output.put_line('Plan start date has changed will recreate');
416
417 END IF;
418
419 IF (var_curr_start_date1 <> TRUNC(sysdate)) THEN
420 -- -------------------------------------------------------------------
421 -- The start date for current data buckets is changed so we need to
422 -- recreate them
423 -- -------------------------------------------------------------------
424 recreate1 := TRUE;
425 dbms_output.put_line('Plan start date has changed will
426 recreate for Current Data');
427 END IF;
428
429 IF recreate = TRUE THEN
430
431 DELETE mrp_workbench_bucket_dates
432 WHERE compile_designator = arg_compile_designator
433 AND organization_id = arg_organization_id
434 AND bucket_type IN (DAILY_BUCKET, WEEKLY_BUCKET, PERIODIC_BUCKET);
435
436 DELETE mrp_material_plans
437 WHERE compile_designator = arg_compile_designator
438 AND organization_id = arg_organization_id;
439
440
441 mrp_wb_bucket_dates.populate_row(arg_organization_id,
442 arg_compile_designator,
443 DAILY_BUCKET);
444
445 mrp_wb_bucket_dates.populate_row(arg_organization_id,
446 arg_compile_designator,
447 WEEKLY_BUCKET);
448
449 mrp_wb_bucket_dates.populate_row(arg_organization_id,
450 arg_compile_designator,
451 PERIODIC_BUCKET);
452
453 mrp_custom_wb.mrp_custom_wb_bucket_dates(arg_organization_id,
454 arg_compile_designator);
455
456 ELSE
457 -- ----------------------------------
458 -- Return because nothing has changed
459 -- ----------------------------------
460 dbms_output.put_line('Nothing has changed');
461 END IF;
462
463 -- ------------------------------------------
464 -- We will recreate buckets for current data
465 -- ------------------------------------------
466 IF recreate1 = TRUE THEN
467
468 -- ----------------------------------------------
469 -- Reset var_plan_start_date to sysdate
470 -- ----------------------------------------------
471 var_plan_start_date := TRUNC(sysdate);
472 BEGIN
473 DELETE mrp_workbench_bucket_dates
474 WHERE compile_designator = arg_compile_designator
475 AND organization_id = arg_organization_id
476 AND bucket_type IN (-1, -2, -3);
477 EXCEPTION WHEN NO_DATA_FOUND THEN
478 NULL;
479 END;
480
481 BEGIN
482 DELETE mrp_material_plans
483 WHERE compile_designator = arg_compile_designator
484 AND organization_id = arg_organization_id;
485 EXCEPTION WHEN NO_DATA_FOUND THEN
486 NULL;
487 END;
488 dbms_output.put_line('creating buckets for current data');
489
490 mrp_wb_bucket_dates.populate_row(arg_organization_id,
491 arg_compile_designator,
492 -1);
493
494 mrp_wb_bucket_dates.populate_row(arg_organization_id,
495 arg_compile_designator,
496 -2);
497
498 mrp_wb_bucket_dates.populate_row(arg_organization_id,
499 arg_compile_designator,
500 -3);
501
502 mrp_custom_wb.mrp_custom_wb_bucket_dates(arg_organization_id,
503 arg_compile_designator);
504 ELSE
505
506 dbms_output.put_line('Nothing has changed for Current data');
507
508 END IF;
509 COMMIT WORK;
510 END populate_bucket_dates;
511
512 END MRP_WB_BUCKET_DATES;
[Home] [Help]