1 PACKAGE BODY MRP_CALENDAR AS
2 /* $Header: MRPCALDB.pls 115.7 2004/08/19 20:03:34 jhegde ship $ */
3
4 -- Global Static variables
5 MRP_CALENDAR_RET_DATES NUMBER := 0;
6 mrp_calendar_cal_code VARCHAR2(10) := '17438gdjh';
7 mrp_calendar_excep_set NUMBER := -23453;
8 min_date DATE;
9 max_date DATE;
10 min_week_date DATE;
11 max_week_date DATE;
12 min_period_date DATE;
13 max_period_date DATE;
14 min_seq_num NUMBER;
15 max_seq_num NUMBER;
16 min_week_seq_num NUMBER;
17 max_week_seq_num NUMBER;
18
19
20 TYPE_DAILY_BUCKET CONSTANT NUMBER := 1;
21 TYPE_WEEKLY_BUCKET CONSTANT NUMBER := 2;
22 TYPE_MONTHLY_BUCKET CONSTANT NUMBER := 3;
23
24 var_return_date DATE;
25 var_prev_work_day DATE;
26 var_prev_work_day2 DATE;
27 var_prev_seq_num NUMBER;
28 var_prev_seq_num2 NUMBER;
29 var_return_number NUMBER;
30 var_string_buffer CHAR(1);
31 var_calendar_code VARCHAR2(10);
32 var_exception_set_id NUMBER;
33
34 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
35
36 PROCEDURE MRP_CAL_INIT_GLOBAL( arg_calendar_code VARCHAR,
37 arg_exception_set_id NUMBER) IS
38 temp_char VARCHAR2(30);
39 BEGIN
40 /*Commented bug 1480385 dbms_output.put_line('In MRP_CAL_INIT_GLOBAL');*/
41 IF arg_calendar_code <> mrp_calendar_cal_code OR
42 arg_exception_set_id <> mrp_calendar_excep_set THEN
43
44 SELECT /*+ index_ffs(bom) */ min(calendar_date), max(calendar_date), min(seq_num),
45 max(seq_num)
46 INTO min_date, max_date, min_seq_num, max_seq_num
47 FROM bom_calendar_dates bom
48 WHERE calendar_code = arg_calendar_code
49 AND seq_num is not null
50 AND exception_set_id = arg_exception_set_id;
51
52 SELECT min(period_start_date), max(period_start_date)
53 INTO min_period_date, max_period_date
54 FROM bom_period_start_dates
55 WHERE calendar_code = arg_calendar_code
56 AND exception_set_id = arg_exception_set_id;
57
58 SELECT min(week_start_date), max(week_start_date), min(seq_num),
59 max(seq_num)
60 INTO min_week_date, max_week_date, min_week_seq_num,
61 max_week_seq_num
62 FROM bom_cal_week_start_dates
63 WHERE calendar_code = arg_calendar_code
64 AND exception_set_id = arg_exception_set_id;
65
66 mrp_calendar_cal_code := arg_calendar_code;
67 mrp_calendar_excep_set := arg_exception_set_id;
68 END IF;
69
70 IF MRP_CALENDAR_RET_DATES = 0 THEN
71 /*Commented bug 1480385 dbms_output.put_line('Getting value of profile');*/
72 temp_Char := FND_PROFILE.VALUE('MRP_RETAIN_DATES_WTIN_CAL_BOUNDARY');
73 IF temp_Char = 'Y' THEN
74 MRP_CALENDAR_RET_DATES := 1;
75 ELSE
76 MRP_CALENDAR_RET_DATES := 2;
77 END IF;
78 END IF;
79 /*Commented bug 1480385 dbms_output.put_line(to_char(MRP_CALENDAR_RET_DATES));*/
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 FND_MESSAGE.SET_NAME('MRP', 'GEN-CALENDAR NOT COMPILED');
83 APP_EXCEPTION.RAISE_EXCEPTION;
84 END MRP_CAL_INIT_GLOBAL;
85
86 FUNCTION MRP_CALC_PERIOD_OFFSET(arg_date IN DATE,
87 arg_offset IN NUMBER,
88 arg_calendar_code IN VARCHAR2,
89 arg_exception_set_id IN NUMBER) RETURN DATE IS
90 var_abs_number NUMBER;
91 BEGIN
92
93 IF arg_offset > 0 THEN
94 DECLARE CURSOR C1 IS
95 SELECT period_start_date
96 FROM bom_period_start_dates cal
97 WHERE cal.exception_set_id = var_exception_set_id
98 AND cal.calendar_code = var_calendar_code
99 AND cal.period_start_date > TRUNC(arg_date);
100 BEGIN
101 var_abs_number := arg_offset;
102 OPEN C1;
103 LOOP
104 FETCH C1 INTO var_return_date;
105 IF C1%ROWCOUNT = var_abs_number THEN
106 EXIT;
107 END IF;
108 END LOOP;
109 CLOSE C1;
110 END;
111
112 ELSE
113 DECLARE CURSOR C1 IS
114 SELECT period_start_date
115 FROM bom_period_start_dates cal
116 WHERE cal.exception_set_id = var_exception_set_id
117 AND cal.calendar_code = var_calendar_code
118 AND cal.period_start_date < TRUNC(arg_date)
119 ORDER BY period_start_date DESC;
120
121 BEGIN
122 var_abs_number := ABS(arg_offset);
123 OPEN C1;
124 LOOP
125 FETCH C1 INTO var_return_date;
126 IF C1%ROWCOUNT = var_abs_number THEN
127 EXIT;
128 END IF;
129 END LOOP;
130 CLOSE C1;
131 END;
132 END IF;
133 return var_return_date;
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
137 APP_EXCEPTION.RAISE_EXCEPTION;
138 END;
139
140 FUNCTION MRP_CALC_DATE_OFFSET(arg_seq_num IN NUMBER,
141 arg_offset IN NUMBER,
142 arg_calendar_code IN VARCHAR2,
143 arg_exception_set_id IN NUMBER) RETURN DATE IS
144 BEGIN
145 BEGIN
146 SELECT calendar_date
147 INTO var_return_date
148 FROM bom_calendar_dates cal
149 WHERE cal.exception_set_id = var_exception_set_id
150 AND cal.calendar_code = var_calendar_code
151 AND cal.seq_num = arg_seq_num + arg_offset;
152
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 IF MRP_CALENDAR_RET_DATES = 1
156 THEN
157 IF arg_offset > 0 THEN
158 var_return_date := max_date;
159 ELSE
160 var_return_date := min_date;
161 END IF;
162 ELSE
163 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 END IF;
166 END;
167 return var_return_date;
168 END;
169
170 FUNCTION MRP_CALC_WEEK_OFFSET(arg_seq_num IN NUMBER,
171 arg_offset IN NUMBER,
172 arg_calendar_code IN VARCHAR2,
173 arg_exception_set_id IN NUMBER) RETURN DATE IS
174 BEGIN
175 BEGIN
176 SELECT week_start_date
177 INTO var_return_date
178 FROM bom_cal_week_start_dates cal
179 WHERE cal.exception_set_id = var_exception_set_id
180 AND cal.calendar_code = var_calendar_code
181 AND cal.seq_num = arg_seq_num + arg_offset;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 IF MRP_CALENDAR_RET_DATES = 1
185 THEN
186 IF arg_offset > 0 THEN
187 var_return_date := max_week_date;
188 ELSE
189 var_return_date := min_week_date;
190 END IF;
191 ELSE
192 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 END IF;
195 END;
196
197 return var_return_date;
198 END;
199
200 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
201
202 FUNCTION NEXT_WORK_DAY(arg_org_id IN NUMBER,
203 arg_bucket IN NUMBER,
204 arg_date IN DATE) RETURN DATE IS
205 BEGIN
206
207 IF arg_date is NULL or arg_org_id is NULL or arg_bucket is NULL THEN
208 RETURN NULL;
209 END IF;
210 mrp_calendar.select_calendar_defaults(arg_org_id,
211 var_calendar_code, var_exception_set_id);
212
213
214 MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
215 IF arg_bucket = TYPE_DAILY_BUCKET THEN
216
217 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
218 var_return_date := max_date;
219 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
220 var_return_date := min_date;
221 ELSE
222 SELECT cal.next_date
223 INTO var_return_date
224 FROM bom_calendar_dates cal
225 WHERE cal.exception_set_id = var_exception_set_id
226 AND cal.calendar_code = var_calendar_code
227 AND cal.calendar_date = TRUNC(arg_date);
228 END IF;
229
230 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
231 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
232 var_return_date := max_week_date;
233 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
234 var_return_date := min_week_date;
235 ELSE
236 SELECT MIN(cal.week_start_date)
237 INTO var_return_date
238 FROM bom_cal_week_start_dates cal
239 WHERE cal.exception_set_id = var_exception_set_id
240 AND cal.calendar_code = var_calendar_code
241 AND cal.week_start_date >= TRUNC(arg_date);
242 END IF;
243
244 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
245 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN
246 var_return_date := max_period_date;
247 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN
248 var_return_date := min_period_date;
249 ELSE
250 SELECT MIN(cal.period_start_date)
251 INTO var_return_date
252 FROM bom_period_start_dates cal
253 WHERE cal.exception_set_id = var_exception_set_id
254 AND cal.calendar_code = var_calendar_code
255 AND cal.period_start_date >= TRUNC(arg_date);
256 END IF;
257 END IF;
258
259 return var_return_date;
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 END NEXT_WORK_DAY;
265
266 FUNCTION PREV_WORK_DAY(arg_org_id IN NUMBER,
267 arg_bucket IN NUMBER,
268 arg_date IN DATE) RETURN DATE IS
269 BEGIN
270 IF arg_date is NULL or arg_org_id is NULL or arg_bucket is NULL THEN
271 RETURN NULL;
272 END IF;
273 mrp_calendar.select_calendar_defaults(arg_org_id,
274 var_calendar_code, var_exception_set_id);
275
276 MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
277 IF arg_bucket = TYPE_DAILY_BUCKET THEN
278 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
279 var_return_date := max_date;
280 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
281 var_return_date := min_date;
282 ELSE
283 SELECT cal.prior_date
284 INTO var_return_date
285 FROM bom_calendar_dates cal
286 WHERE cal.exception_set_id = var_exception_set_id
287 AND cal.calendar_code = var_calendar_code
288 AND cal.calendar_date = TRUNC(arg_date);
289 END IF;
290 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
291 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
292 var_return_date := max_week_date;
293 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
294 var_return_date := min_week_date;
295 ELSE
296 SELECT MAX(cal.week_start_date)
297 INTO var_return_date
298 FROM bom_cal_week_start_dates cal
299 WHERE cal.exception_set_id = var_exception_set_id
300 AND cal.calendar_code = var_calendar_code
301 AND cal.week_start_date <= TRUNC(arg_date);
302 END IF;
303 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
304 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN
305 var_return_date := max_period_date;
306 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN
307 var_return_date := min_period_date;
308 ELSE
309 SELECT MAX(cal.period_start_date)
310 INTO var_return_date
311 FROM bom_period_start_dates cal
312 WHERE cal.exception_set_id = var_exception_set_id
313 AND cal.calendar_code = var_calendar_code
314 AND cal.period_start_date <= TRUNC(arg_date);
315 END IF;
316 END IF;
317
318 return var_return_date;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
322 APP_EXCEPTION.RAISE_EXCEPTION;
323 END PREV_WORK_DAY;
324
325 FUNCTION NEXT_WORK_DAY_SEQNUM(arg_org_id IN NUMBER,
326 arg_bucket IN NUMBER,
327 arg_date IN DATE) RETURN NUMBER IS
328 BEGIN
329 mrp_calendar.select_calendar_defaults(arg_org_id,
330 var_calendar_code, var_exception_set_id);
331
332 IF arg_bucket = TYPE_DAILY_BUCKET THEN
333 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
334 var_return_number := max_seq_num;
335 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
336 var_return_number := min_seq_num;
337 ELSE
338 SELECT cal.next_seq_num
339 INTO var_return_number
340 FROM bom_calendar_dates cal
341 WHERE cal.exception_set_id = var_exception_set_id
342 AND cal.calendar_code = var_calendar_code
343 AND cal.calendar_date = TRUNC(arg_date);
344 END IF;
345 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
349 var_return_number := min_seq_num;
346 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
347 var_return_number := max_week_seq_num;
348 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
350 ELSE
351 SELECT MIN(cal.seq_num)
352 INTO var_return_number
353 FROM bom_cal_week_start_dates cal
354 WHERE cal.exception_set_id = var_exception_set_id
355 AND cal.calendar_code = var_calendar_code
356 AND cal.week_start_date >= TRUNC(arg_date);
357 END IF;
358 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
359 raise_application_error(-20000, 'Invalid bucket type');
360 END IF;
361 return var_return_number;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
365 APP_EXCEPTION.RAISE_EXCEPTION;
366 END NEXT_WORK_DAY_SEQNUM;
367
368 FUNCTION PREV_WORK_DAY_SEQNUM(arg_org_id IN NUMBER,
369 arg_bucket IN NUMBER,
370 arg_date IN DATE) RETURN NUMBER IS
371 BEGIN
372 mrp_calendar.select_calendar_defaults(arg_org_id,
373 var_calendar_code, var_exception_set_id);
374
375 IF arg_bucket = TYPE_DAILY_BUCKET THEN
376 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
377 var_return_number := max_seq_num;
378 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
379 var_return_number := min_seq_num;
380 ELSE
381 SELECT cal.prior_seq_num
382 INTO var_return_number
383 FROM bom_calendar_dates cal
384 WHERE cal.exception_set_id = var_exception_set_id
385 AND cal.calendar_code = var_calendar_code
386 AND cal.calendar_date = TRUNC(arg_date);
387 END IF;
388 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
389 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
390 var_return_number := max_week_seq_num;
391 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
392 var_return_number := min_week_seq_num;
393 ELSE
394 SELECT MAX(cal.seq_num)
395 INTO var_return_number
396 FROM bom_cal_week_start_dates cal
397 WHERE cal.exception_set_id = var_exception_set_id
398 AND cal.calendar_code = var_calendar_code
399 AND cal.week_start_date <= TRUNC(arg_date);
400 END IF;
401 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
402 raise_application_error(-20000, 'Invalid bucket type');
403 END IF;
404
405 return var_return_number;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 END PREV_WORK_DAY_SEQNUM;
411
412 FUNCTION DATE_OFFSET( arg_org_id IN NUMBER,
413 arg_bucket IN NUMBER,
414 arg_date IN DATE,
415 arg_offset IN NUMBER) RETURN DATE IS
416 BEGIN
417 IF arg_date IS NULL or arg_org_id is NULL or arg_bucket is NULL or
418 arg_offset is null THEN
419 RETURN NULL;
420 END IF;
421 IF arg_offset = 0 THEN
422 var_prev_work_day := PREV_WORK_DAY(arg_org_id, 1, arg_date);
423 return var_prev_work_day;
424 END IF;
425
426 mrp_calendar.select_calendar_defaults(arg_org_id,
427 var_calendar_code, var_exception_set_id);
428
429 MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
430 IF arg_bucket = TYPE_DAILY_BUCKET OR arg_bucket = TYPE_WEEKLY_BUCKET
431 THEN
432 var_prev_seq_num :=
433 PREV_WORK_DAY_SEQNUM(arg_org_id, arg_bucket, arg_date);
434 END IF;
435
436 IF arg_bucket = TYPE_DAILY_BUCKET THEN
437 var_return_date := mrp_calc_date_offset(var_prev_seq_num,
438 arg_offset, var_calendar_code, var_exception_set_id);
439 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
440 var_return_date := mrp_calc_week_offset(var_prev_seq_num,
441 arg_offset, var_calendar_code, var_exception_set_id);
442 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
443 var_prev_work_day := PREV_WORK_DAY(arg_org_id, arg_bucket, arg_date);
444 var_return_date := mrp_calc_period_offset(var_prev_work_day,
445 arg_offset, var_calendar_code, var_exception_set_id);
446 END IF;
447
448 return var_return_date;
449 END DATE_OFFSET;
450
451 FUNCTION DAYS_BETWEEN( arg_org_id IN NUMBER,
452 arg_bucket IN NUMBER,
453 arg_date1 IN DATE,
454 arg_date2 IN DATE) RETURN NUMBER IS
455 BEGIN
456 mrp_calendar.select_calendar_defaults(arg_org_id,
457 var_calendar_code, var_exception_set_id);
458
459 IF arg_date1 is NULL or arg_bucket is null or arg_org_id is null
460 or arg_date2 IS NULL THEN
461 RETURN NULL;
462 END IF;
463
464 MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
465 IF (arg_bucket <> TYPE_MONTHLY_BUCKET) THEN
466 var_prev_seq_num := PREV_WORK_DAY_SEQNUM(arg_org_id, arg_bucket, arg_date1);
467 var_prev_seq_num2 := PREV_WORK_DAY_SEQNUM(arg_org_id, arg_bucket, arg_date2);
468 var_return_number := ABS(var_prev_seq_num2 - var_prev_seq_num);
469 ELSE
470 var_prev_work_day := PREV_WORK_DAY(arg_org_id, arg_bucket, arg_date1);
471 var_prev_work_day2 := PREV_WORK_DAY(arg_org_id, arg_bucket, arg_date2);
472 SELECT count(period_start_date)
473 INTO var_return_number
474 FROM bom_period_start_dates cal
475 WHERE cal.exception_set_id = var_exception_set_id
476 AND cal.calendar_code = var_calendar_code
477 AND cal.period_start_date between var_prev_work_day
478 and var_prev_work_day2
479 AND cal.period_start_date <> var_prev_work_day2;
480
481 END IF;
482
483 return var_return_number;
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
487 APP_EXCEPTION.RAISE_EXCEPTION;
488 END DAYS_BETWEEN;
489
490 PROCEDURE select_calendar_defaults(
491 arg_org_id IN NUMBER,
492 arg_calendar_code OUT NOCOPY VARCHAR2, --2663505
493 arg_exception_set_id OUT NOCOPY NUMBER) IS --2663505
494
495 BEGIN
496 SELECT calendar_code,
497 calendar_exception_set_id
498 INTO arg_calendar_code,
499 arg_exception_set_id
500 FROM mtl_parameters
501 WHERE organization_id = arg_org_id;
502
503 IF SQL%NOTFOUND THEN
504 raise_application_error(-200000, 'Cannot select calendar defaults');
505 END IF;
506
507 END select_calendar_defaults;
508
509 END MRP_CALENDAR;
[Home] [Help]