1 PACKAGE BODY MSC_CALENDAR AS
2 /* $Header: MSCCALDB.pls 120.4 2008/01/04 11:04:31 sbnaik ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
5
6 -- Global Static variables
7 MSC_CALENDAR_RET_DATES NUMBER := 0;
8 msc_calendar_cal_code VARCHAR2(14) := '17438gdjh';
9 msc_calendar_excep_set NUMBER := -23453;
10 min_date DATE;
11 max_date DATE;
12 min_week_date DATE;
13 max_week_date DATE;
14 min_period_date DATE;
15 max_period_date DATE;
16 min_seq_num NUMBER;
17 max_seq_num NUMBER;
18 min_week_seq_num NUMBER;
19 max_week_seq_num NUMBER;
20
21 var_return_date DATE;
22 var_prev_work_day DATE;
23 var_prev_work_day2 DATE;
24 var_prev_seq_num NUMBER;
25 var_prev_seq_num2 NUMBER;
26 var_return_number NUMBER;
27 var_string_buffer CHAR(1);
28 var_calendar_code VARCHAR2(14);
29 var_exception_set_id NUMBER;
30
31 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
32
33 PROCEDURE MSC_CAL_INIT_GLOBAL( arg_calendar_code VARCHAR,
34 arg_exception_set_id NUMBER,
35 arg_instance_id NUMBER) IS
36 temp_char VARCHAR2(30);
37 BEGIN
38
39 IF arg_calendar_code <> msc_calendar_cal_code OR
40 arg_exception_set_id <> msc_calendar_excep_set THEN
41
42 SELECT min(calendar_date), max(calendar_date), min(seq_num),
43 max(seq_num)
44 INTO min_date, max_date, min_seq_num, max_seq_num
45 FROM msc_calendar_dates
46 WHERE calendar_code = arg_calendar_code
47 AND seq_num is not null
48 AND exception_set_id = arg_exception_set_id
49 AND sr_instance_id = arg_instance_id;
50
51 SELECT min(period_start_date), max(period_start_date)
52 INTO min_period_date, max_period_date
53 FROM msc_period_start_dates
54 WHERE calendar_code = arg_calendar_code
55 AND exception_set_id = arg_exception_set_id
56 AND sr_instance_id = arg_instance_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 msc_cal_week_start_dates
63 WHERE calendar_code = arg_calendar_code
64 AND exception_set_id = arg_exception_set_id
65 AND sr_instance_id = arg_instance_id ;
66
67 msc_calendar_cal_code := arg_calendar_code;
68 msc_calendar_excep_set := arg_exception_set_id;
69 END IF;
70
71 IF MSC_CALENDAR_RET_DATES = 0 THEN
72
73 temp_Char := FND_PROFILE.VALUE('MRP_RETAIN_DATES_WTIN_CAL_BOUNDARY');
74 IF temp_Char = 'Y' THEN
75 MSC_CALENDAR_RET_DATES := 1;
76 ELSE
77 MSC_CALENDAR_RET_DATES := 2;
78 END IF;
79 END IF;
80
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 FND_MESSAGE.SET_NAME('MRP', 'GEN-CALENDAR NOT COMPILED');
84 APP_EXCEPTION.RAISE_EXCEPTION;
85 END MSC_CAL_INIT_GLOBAL;
86
87 FUNCTION MSC_CALC_PERIOD_OFFSET(arg_date IN DATE,
88 arg_offset IN NUMBER,
89 arg_calendar_code IN VARCHAR2,
90 arg_exception_set_id IN NUMBER,
91 arg_instance_id IN NUMBER) RETURN DATE IS
92 var_abs_number NUMBER;
93 BEGIN
94
95 IF arg_offset > 0 THEN
96 DECLARE CURSOR C1 IS
97 SELECT period_start_date
98 FROM msc_period_start_dates cal
99 WHERE cal.exception_set_id = var_exception_set_id
100 AND cal.calendar_code = var_calendar_code
101 AND cal.period_start_date > TRUNC(arg_date)
102 AND cal.sr_instance_id = arg_instance_id
103 ORDER BY period_start_date;
104 BEGIN
105 -- Round up to next integer
106 var_abs_number := CEIL(arg_offset);
107 OPEN C1;
108 LOOP
109 FETCH C1 INTO var_return_date;
110 IF C1%ROWCOUNT = var_abs_number THEN
111 EXIT;
112 END IF;
113 END LOOP;
114 CLOSE C1;
115 END;
116
117 ELSE
118 DECLARE CURSOR C1 IS
119 SELECT period_start_date
120 FROM msc_period_start_dates cal
121 WHERE cal.exception_set_id = var_exception_set_id
122 AND cal.calendar_code = var_calendar_code
123 AND cal.period_start_date < TRUNC(arg_date)
124 AND cal.sr_instance_id = arg_instance_id
125 ORDER BY period_start_date DESC;
126
127 BEGIN
128 -- Round up to next integer
129 var_abs_number := CEIL(ABS(arg_offset));
130 OPEN C1;
131 LOOP
132 FETCH C1 INTO var_return_date;
133 IF C1%ROWCOUNT = var_abs_number THEN
134 EXIT;
135 END IF;
136 END LOOP;
137 CLOSE C1;
138 END;
139 END IF;
140 return var_return_date;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END;
146
147 FUNCTION MSC_CALC_DATE_OFFSET(arg_seq_num IN NUMBER,
148 arg_offset IN NUMBER,
149 arg_calendar_code IN VARCHAR2,
150 arg_exception_set_id IN NUMBER,
151 arg_instance_id IN NUMBER) RETURN DATE IS
152 l_arg_offset number;
153 BEGIN
154
155 -- Round up to next integer
156 IF arg_offset >= 0 THEN
157 l_arg_offset := CEIL(arg_offset);
158 ELSE
159 l_arg_offset := -1 * CEIL(ABS(arg_offset));
160 END IF;
161
162 BEGIN
163 SELECT calendar_date
164 INTO var_return_date
165 FROM msc_calendar_dates cal
166 WHERE cal.exception_set_id = var_exception_set_id
167 AND cal.calendar_code = var_calendar_code
168 AND cal.seq_num = arg_seq_num + l_arg_offset
169 --AND cal.seq_num = arg_seq_num + arg_offset
170 AND cal.sr_instance_id = arg_instance_id;
171
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 IF MSC_CALENDAR_RET_DATES = 1
175 THEN
176 IF arg_offset > 0 THEN
177 var_return_date := max_date;
178 ELSE
179 var_return_date := min_date;
180 END IF;
181 ELSE
182 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
183 APP_EXCEPTION.RAISE_EXCEPTION;
184 END IF;
185 END;
186 return var_return_date;
187 END;
188
189 FUNCTION MSC_CALC_WEEK_OFFSET(arg_seq_num IN NUMBER,
190 arg_offset IN NUMBER,
191 arg_calendar_code IN VARCHAR2,
192 arg_exception_set_id IN NUMBER,
193 arg_instance_id IN NUMBER) RETURN DATE IS
194 l_arg_offset number;
195 BEGIN
196
197 -- Round up to next integer
198 IF arg_offset >= 0 THEN
199 l_arg_offset := CEIL(arg_offset);
200 ELSE
201 l_arg_offset := -1 * CEIL(ABS(arg_offset));
202 END IF;
203
204 BEGIN
205 SELECT week_start_date
206 INTO var_return_date
207 FROM msc_cal_week_start_dates cal
208 WHERE cal.exception_set_id = var_exception_set_id
209 AND cal.calendar_code = var_calendar_code
210 AND cal.seq_num = arg_seq_num + l_arg_offset
211 AND cal.sr_instance_id = arg_instance_id;
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 IF MSC_CALENDAR_RET_DATES = 1
215 THEN
216 IF arg_offset > 0 THEN
217 var_return_date := max_week_date;
218 ELSE
219 var_return_date := min_week_date;
220 END IF;
221 ELSE
222 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
223 APP_EXCEPTION.RAISE_EXCEPTION;
224 END IF;
225 END;
226
227 return var_return_date;
228 END;
229
230 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
231
232 FUNCTION NEXT_WORK_DAY(arg_org_id IN NUMBER,
233 arg_instance_id IN NUMBER,
234 arg_bucket IN NUMBER,
235 arg_date IN DATE) RETURN DATE IS
236 BEGIN
237
238 IF arg_date is NULL or arg_org_id is NULL OR arg_instance_id IS NULL or arg_bucket is NULL THEN
239 RETURN NULL;
240 END IF;
241 msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
242 var_calendar_code, var_exception_set_id);
243
244
245 MSC_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id,arg_instance_id);
246 IF arg_bucket = TYPE_DAILY_BUCKET THEN
247
248 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
249 var_return_date := max_date;
250 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
251 var_return_date := min_date;
252 ELSE
253 SELECT cal.next_date
254 INTO var_return_date
255 FROM msc_calendar_dates cal
256 WHERE cal.exception_set_id = var_exception_set_id
257 AND cal.calendar_code = var_calendar_code
258 AND cal.calendar_date = TRUNC(arg_date)
259 AND cal.sr_instance_id = arg_instance_id;
260 END IF;
261
262 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
263 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
264 var_return_date := max_week_date;
265 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
266 var_return_date := min_week_date;
267 ELSE
268 SELECT MIN(cal.week_start_date)
269 INTO var_return_date
270 FROM msc_cal_week_start_dates cal
271 WHERE cal.exception_set_id = var_exception_set_id
272 AND cal.calendar_code = var_calendar_code
273 AND cal.week_start_date >= TRUNC(arg_date)
274 AND cal.sr_instance_id = arg_instance_id ;
275 END IF;
276
277 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
278 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN
279 var_return_date := max_period_date;
280
281 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN
282 var_return_date := min_period_date;
283
284 ELSE
285 SELECT MIN(cal.period_start_date)
286 INTO var_return_date
287 FROM msc_period_start_dates cal
288 WHERE cal.exception_set_id = var_exception_set_id
289 AND cal.calendar_code = var_calendar_code
290 AND cal.period_start_date >= TRUNC(arg_date)
291 AND cal.sr_instance_id = arg_instance_id;
292
293 END IF;
294 END IF;
295
296 return var_return_date;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
300 APP_EXCEPTION.RAISE_EXCEPTION;
301 END NEXT_WORK_DAY;
302
303 FUNCTION PREV_WORK_DAY(arg_org_id IN NUMBER,
304 arg_instance_id IN NUMBER,
305 arg_bucket IN NUMBER,
306 arg_date IN DATE) RETURN DATE IS
307 BEGIN
308 IF arg_date is NULL or arg_org_id is NULL OR arg_instance_id IS NULL OR arg_bucket is NULL THEN
309 RETURN NULL;
310 END IF;
311 msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
312 var_calendar_code, var_exception_set_id);
313
314 MSC_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id,arg_instance_id);
315 IF arg_bucket = TYPE_DAILY_BUCKET THEN
316 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
317 var_return_date := max_date;
318 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
319 var_return_date := min_date;
320 ELSE
321 SELECT cal.prior_date
322 INTO var_return_date
323 FROM msc_calendar_dates cal
324 WHERE cal.exception_set_id = var_exception_set_id
325 AND cal.calendar_code = var_calendar_code
326 AND cal.calendar_date = TRUNC(arg_date)
327 AND cal.sr_instance_id = arg_instance_id;
328 END IF;
329 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
330 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
331 var_return_date := max_week_date;
332 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
333 var_return_date := min_week_date;
334 ELSE
335 SELECT MAX(cal.week_start_date)
336 INTO var_return_date
337 FROM msc_cal_week_start_dates cal
338 WHERE cal.exception_set_id = var_exception_set_id
339 AND cal.calendar_code = var_calendar_code
340 AND cal.week_start_date <= TRUNC(arg_date)
341 AND cal.sr_instance_id = arg_instance_id;
342 END IF;
343 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
344 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN
345 var_return_date := max_period_date;
346 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN
347 var_return_date := min_period_date;
348 ELSE
349 SELECT MAX(cal.period_start_date)
350 INTO var_return_date
351 FROM msc_period_start_dates cal
352 WHERE cal.exception_set_id = var_exception_set_id
353 AND cal.calendar_code = var_calendar_code
354 AND cal.period_start_date <= TRUNC(arg_date)
355 AND cal.sr_instance_id = arg_instance_id;
356 END IF;
357 END IF;
358
359 return var_return_date;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 END PREV_WORK_DAY;
365
366 FUNCTION NEXT_WORK_DAY_SEQNUM(arg_org_id IN NUMBER,
367 arg_instance_id IN NUMBER,
368 arg_bucket IN NUMBER,
369 arg_date IN DATE) RETURN NUMBER IS
370 BEGIN
371 msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
372 var_calendar_code, var_exception_set_id);
373
374 IF arg_bucket = TYPE_DAILY_BUCKET THEN
375 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
376 var_return_number := max_seq_num;
377 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
378 var_return_number := min_seq_num;
379 ELSE
380 SELECT cal.next_seq_num
381 INTO var_return_number
382 FROM msc_calendar_dates cal
383 WHERE cal.exception_set_id = var_exception_set_id
384 AND cal.calendar_code = var_calendar_code
385 AND cal.calendar_date = TRUNC(arg_date)
386 AND cal.sr_instance_id = arg_instance_id ;
387 END IF;
388 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
389 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
390 var_return_number := max_week_seq_num;
391 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
392 var_return_number := min_seq_num;
393 ELSE
394 SELECT MIN(cal.seq_num)
395 INTO var_return_number
396 FROM msc_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 AND cal.sr_instance_id = arg_instance_id ;
401 END IF;
402 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
403 raise_application_error(-20000, 'Invalid bucket type');
404 END IF;
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 NEXT_WORK_DAY_SEQNUM;
411
412 FUNCTION PREV_WORK_DAY_SEQNUM(arg_org_id IN NUMBER,
413 arg_instance_id IN NUMBER,
414 arg_bucket IN NUMBER,
415 arg_date IN DATE) RETURN NUMBER IS
416 BEGIN
417 msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
418 var_calendar_code, var_exception_set_id);
419
420 IF arg_bucket = TYPE_DAILY_BUCKET THEN
421 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
422 var_return_number := max_seq_num;
423 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
424 var_return_number := min_seq_num;
425 ELSE
426 SELECT cal.prior_seq_num
427 INTO var_return_number
428 FROM msc_calendar_dates cal
429 WHERE cal.exception_set_id = var_exception_set_id
430 AND cal.calendar_code = var_calendar_code
431 AND cal.calendar_date = TRUNC(arg_date)
432 AND cal.sr_instance_id = arg_instance_id ;
433 END IF;
434 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
435 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
436 var_return_number := max_week_seq_num;
437 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
438 var_return_number := min_week_seq_num;
439 ELSE
440 SELECT MAX(cal.seq_num)
441 INTO var_return_number
442 FROM msc_cal_week_start_dates cal
443 WHERE cal.exception_set_id = var_exception_set_id
444 AND cal.calendar_code = var_calendar_code
445 AND cal.week_start_date <= TRUNC(arg_date)
446 AND cal.sr_instance_id = arg_instance_id ;
447 END IF;
448 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
449 raise_application_error(-20000, 'Invalid bucket type');
450 END IF;
451
452 return var_return_number;
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
456 APP_EXCEPTION.RAISE_EXCEPTION;
457 END PREV_WORK_DAY_SEQNUM;
458
459 FUNCTION DATE_OFFSET( arg_org_id IN NUMBER,
460 arg_instance_id IN NUMBER,
461 arg_bucket IN NUMBER,
462 arg_date IN DATE,
463 arg_offset IN NUMBER) RETURN DATE IS
464 l_arg_offset number;
465 BEGIN
466 IF arg_date IS NULL or arg_org_id is NULL OR arg_instance_id IS NULL or arg_bucket is NULL or
467 arg_offset is null THEN
468 RETURN NULL;
469 END IF;
470 IF arg_offset = 0 THEN
471 var_prev_work_day := PREV_WORK_DAY(arg_org_id, arg_instance_id, 1, arg_date);
472 return var_prev_work_day;
473 END IF;
474
475 msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
476 var_calendar_code, var_exception_set_id);
477
478 MSC_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id,arg_instance_id);
479
480 -- Round up to next integer
481 IF arg_offset >= 0 THEN
482 l_arg_offset := CEIL(arg_offset);
483 ELSE
484 l_arg_offset := -1 * CEIL(ABS(arg_offset));
485 END IF;
486
487 IF arg_bucket = TYPE_DAILY_BUCKET OR arg_bucket = TYPE_WEEKLY_BUCKET
488 THEN
489 -- (3335127) New offset logic. Done along with Enforce Pur LT changes
490 IF arg_offset > 0 THEN
491 var_prev_seq_num :=
492 PREV_WORK_DAY_SEQNUM(arg_org_id, arg_instance_id, arg_bucket, arg_date);
493 ELSE
494 var_prev_seq_num :=
495 NEXT_WORK_DAY_SEQNUM(arg_org_id, arg_instance_id, arg_bucket, arg_date);
496 END IF;
497 END IF;
498
499 IF arg_bucket = TYPE_DAILY_BUCKET THEN
500 var_return_date := msc_calc_date_offset(var_prev_seq_num,
501 l_arg_offset, var_calendar_code, var_exception_set_id,
502 arg_instance_id);
503 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
504 var_return_date := msc_calc_week_offset(var_prev_seq_num,
505 l_arg_offset, var_calendar_code, var_exception_set_id,
506 arg_instance_id);
507 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
508 -- (3335127) New offset logic. Done along with Enforce Pur LT changes
509 -- Not sure who calls this API with anything other than daily bucket. Making the change nonetheless.
510 IF arg_offset > 0 THEN
511 var_prev_work_day := PREV_WORK_DAY(arg_org_id, arg_instance_id, arg_bucket, arg_date);
512 ELSE
513 var_prev_work_day := NEXT_WORK_DAY(arg_org_id, arg_instance_id, arg_bucket, arg_date);
514 END IF;
515 var_return_date := msc_calc_period_offset(var_prev_work_day,
516 l_arg_offset, var_calendar_code, var_exception_set_id,
517 arg_instance_id);
518 END IF;
519
520 return var_return_date;
521 END DATE_OFFSET;
522
523 FUNCTION DAYS_BETWEEN( arg_org_id IN NUMBER,
524 arg_instance_id IN NUMBER,
525 arg_bucket IN NUMBER,
526 arg_date1 IN DATE,
527 arg_date2 IN DATE) RETURN NUMBER IS
528 BEGIN
529 msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
530 var_calendar_code, var_exception_set_id);
531
532 IF arg_date1 is NULL or arg_bucket is null or arg_org_id is NULL OR arg_instance_id IS NULL
533 or arg_date2 IS NULL THEN
534 RETURN NULL;
535 END IF;
536
537 MSC_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id,arg_instance_id);
538 IF (arg_bucket <> TYPE_MONTHLY_BUCKET) THEN
539 var_prev_seq_num := PREV_WORK_DAY_SEQNUM(arg_org_id, arg_instance_id, arg_bucket, arg_date1);
540 var_prev_seq_num2 := PREV_WORK_DAY_SEQNUM(arg_org_id, arg_instance_id, arg_bucket, arg_date2);
541 var_return_number := ABS(var_prev_seq_num2 - var_prev_seq_num);
542 ELSE
543 var_prev_work_day := PREV_WORK_DAY(arg_org_id, arg_instance_id, arg_bucket, arg_date1);
544 var_prev_work_day2 := PREV_WORK_DAY(arg_org_id, arg_instance_id, arg_bucket, arg_date2);
545 SELECT count(period_start_date)
546 INTO var_return_number
547 FROM msc_period_start_dates cal
548 WHERE cal.exception_set_id = var_exception_set_id
549 AND cal.calendar_code = var_calendar_code
550 AND cal.period_start_date between var_prev_work_day
551 and var_prev_work_day2
552 AND cal.period_start_date <> var_prev_work_day2
553 AND cal.sr_instance_id = arg_instance_id ;
554
555 END IF;
556
557 return var_return_number;
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
561 APP_EXCEPTION.RAISE_EXCEPTION;
562 END DAYS_BETWEEN;
563
564 PROCEDURE select_calendar_defaults(
565 arg_org_id IN NUMBER,
566 arg_instance_id IN NUMBER,
567 arg_calendar_code OUT NOCOPY VARCHAR2,
568 arg_exception_set_id OUT NOCOPY NUMBER) IS
569 l_org_id number;
570 BEGIN
571 l_org_id := arg_org_id;
572 /* --------------------------------------------------------------+
573 | arg_org_id will be -ve if the call is for Planning Bucketing |
574 | If the profile is defined MSC_BKT_REFERENCE_CALENDAR then use |
575 | it for Bucketing. |
576 +--------------------------------------------------------------- */
577 IF l_org_id < 0 AND G_VAR_BKT_REFERENCE_CALENDAR <> '-23453' THEN
578 arg_calendar_code := G_VAR_BKT_REFERENCE_CALENDAR;
579 arg_exception_set_id := -1;
580 ELSE
581 IF l_org_id < 0 THEN
582 l_org_id := -1 * l_org_id;
583 END IF;
584 SELECT
585 calendar_code,
586 calendar_exception_set_id
587 INTO arg_calendar_code,
588 arg_exception_set_id
589 FROM msc_trading_partners
590 WHERE sr_tp_id = l_org_id
591 AND partner_type = 3
592 AND sr_instance_id = arg_instance_id;
593
594 IF SQL%NOTFOUND THEN
595 raise_application_error(-200000, 'Cannot select calendar defaults');
596 END IF;
597 END IF;
598
599 END select_calendar_defaults;
600
601 -----------------------------------------------------------------------
602 -- FUNCTION PREV_DELIVERY_CALENDAR_DAY
603 -- for publish order process
604 -----------------------------------------------------------------------
605 FUNCTION PREV_DELIVERY_CALENDAR_DAY (arg_calendar_code IN VARCHAR2,
606 arg_instance_id IN NUMBER,
607 arg_exception_set_id IN NUMBER,
608 arg_date IN DATE,
609 arg_bucket IN NUMBER) RETURN DATE IS
610 var_date date;
611 var_return_date date;
612
613 BEGIN
614 IF arg_calendar_code is NULL OR arg_date is NULL OR arg_instance_id IS NULL THEN
615 RETURN NULL;
616 END IF;
617
618 MSC_CAL_INIT_GLOBAL(arg_calendar_code, arg_exception_set_id ,arg_instance_id);
619 IF arg_bucket = TYPE_DAILY_BUCKET THEN
620 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
621 var_return_date := max_date;
622 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
623 var_return_date := min_date;
624 ELSE
625 SELECT cal.prior_date
626 INTO var_return_date
627 FROM msc_calendar_dates cal
628 WHERE cal.exception_set_id = arg_exception_set_id
629 AND cal.calendar_code = arg_calendar_code
630 AND cal.calendar_date = TRUNC(arg_date)
631 AND cal.sr_instance_id = arg_instance_id;
632 END IF;
633 END IF;
634
635 return var_return_date;
636 EXCEPTION
637 WHEN NO_DATA_FOUND THEN
638 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
639 APP_EXCEPTION.RAISE_EXCEPTION;
640
641
642 END prev_delivery_calendar_day;
643
644 ----------------------------------------------------------------------
645 -- CALENDAR_CAL_INIT_GLOBAL (private proc) to fix bug# 2678523
646 -- Calendar code is an argument
647 ----------------------------------------------------------------------
648 PROCEDURE CALENDAR_CAL_INIT_GLOBAL( arg_calendar_code VARCHAR,
649 arg_exception_set_id NUMBER) IS
650 temp_char VARCHAR2(30);
651 BEGIN
652
653
654 IF arg_calendar_code <> msc_calendar_cal_code OR
655 arg_exception_set_id <> msc_calendar_excep_set THEN
656
657 SELECT min(calendar_date), max(calendar_date), min(seq_num),
658 max(seq_num)
659 INTO min_date, max_date, min_seq_num, max_seq_num
660 FROM msc_calendar_dates
661 WHERE calendar_code = arg_calendar_code
662 AND seq_num is not null
663 AND exception_set_id = arg_exception_set_id;
664
665 SELECT min(period_start_date), max(period_start_date)
666 INTO min_period_date, max_period_date
667 FROM msc_period_start_dates
668 WHERE calendar_code = arg_calendar_code
669 AND exception_set_id = arg_exception_set_id;
670
671 SELECT min(week_start_date), max(week_start_date), min(seq_num),
672 max(seq_num)
673 INTO min_week_date, max_week_date, min_week_seq_num,
674 max_week_seq_num
675 FROM msc_cal_week_start_dates
676 WHERE calendar_code = arg_calendar_code
677 AND exception_set_id = arg_exception_set_id ;
678
679 msc_calendar_cal_code := arg_calendar_code;
680 msc_calendar_excep_set := arg_exception_set_id;
681 END IF;
682
683 IF MSC_CALENDAR_RET_DATES = 0 THEN
684
685 temp_Char := FND_PROFILE.VALUE('MRP_RETAIN_DATES_WTIN_CAL_BOUNDARY');
686 IF temp_Char = 'Y' THEN
687 MSC_CALENDAR_RET_DATES := 1;
688 ELSE
689 MSC_CALENDAR_RET_DATES := 2;
690 END IF;
691 END IF;
692
693 EXCEPTION
694 WHEN NO_DATA_FOUND THEN
695 FND_MESSAGE.SET_NAME('MRP', 'GEN-CALENDAR NOT COMPILED');
696 APP_EXCEPTION.RAISE_EXCEPTION;
697 END CALENDAR_CAL_INIT_GLOBAL;
698
699 ----------------------------------------------------------------------
700 -- CALENDAR_NEXT_WORK_DAY to fix bug# 2678523
701 -- Calendar code is an argument
702 ----------------------------------------------------------------------
703 FUNCTION CALENDAR_NEXT_WORK_DAY(arg_instance_id IN NUMBER,
704 arg_calendar_code IN VARCHAR2,
705 arg_bucket IN NUMBER,
706 arg_date IN DATE) RETURN DATE IS
707
708
709 BEGIN
710
711 var_exception_set_id := -1;
712
713 IF arg_date is NULL OR arg_bucket is NULL OR arg_calendar_code IS NULL or arg_instance_id is NULL THEN
714 RETURN NULL;
715 END IF;
716
717 /* For bug# 3532912, changed the calendar initialization call */
718 -- CALENDAR_CAL_INIT_GLOBAL(arg_calendar_code, var_exception_set_id);
719 MSC_CAL_INIT_GLOBAL(arg_calendar_code, var_exception_set_id,arg_instance_id);
720 IF arg_bucket = TYPE_DAILY_BUCKET THEN
721
722 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
723 var_return_date := max_date;
724 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
725 var_return_date := min_date;
726 ELSE
727 SELECT cal.next_date
728 INTO var_return_date
729 FROM msc_calendar_dates cal
730 WHERE cal.exception_set_id = var_exception_set_id
731 AND cal.calendar_code = arg_calendar_code
732 AND cal.calendar_date = TRUNC(arg_date);
733 END IF;
734
735 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
736 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
737 var_return_date := max_week_date;
738 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
739 var_return_date := min_week_date;
740 ELSE
741 SELECT MIN(cal.week_start_date)
742 INTO var_return_date
743 FROM msc_cal_week_start_dates cal
744 WHERE cal.exception_set_id = var_exception_set_id
745 AND cal.calendar_code = arg_calendar_code
746 AND cal.week_start_date >= TRUNC(arg_date) ;
747 END IF;
748
749 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
750 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN
751 var_return_date := max_period_date;
752 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN
753 var_return_date := min_period_date;
754 ELSE
755 SELECT MIN(cal.period_start_date)
756 INTO var_return_date
757 FROM msc_period_start_dates cal
758 WHERE cal.exception_set_id = var_exception_set_id
759 AND cal.calendar_code = arg_calendar_code
760 AND cal.period_start_date >= TRUNC(arg_date);
761
762 END IF;
763 END IF;
764
765 return var_return_date;
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
769 APP_EXCEPTION.RAISE_EXCEPTION;
770 END CALENDAR_NEXT_WORK_DAY;
771
772 --------------------------------------------------------------------------
773 -- CALENDAR_PREV_WORK_DAYto fix bug# 2678523
774 -- Calendar code is an argument
775 --------------------------------------------------------------------------
776 FUNCTION CALENDAR_PREV_WORK_DAY(arg_instance_id IN NUMBER,
777 arg_calendar_code IN VARCHAR2,
778 arg_bucket IN NUMBER,
779 arg_date IN DATE) RETURN DATE IS
780 BEGIN
781 var_exception_set_id := -1;
782
783 IF arg_date is NULL OR arg_instance_id IS NULL OR arg_bucket is NULL THEN
784 RETURN NULL;
785 END IF;
786
787
788 CALENDAR_CAL_INIT_GLOBAL(arg_calendar_code, var_exception_set_id);
789 IF arg_bucket = TYPE_DAILY_BUCKET THEN
790 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
791 var_return_date := max_date;
792 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
793 var_return_date := min_date;
794 ELSE
795 SELECT cal.prior_date
796 INTO var_return_date
797 FROM msc_calendar_dates cal
798 WHERE cal.exception_set_id = var_exception_set_id
799 AND cal.calendar_code = arg_calendar_code
800 AND cal.calendar_date = TRUNC(arg_date);
801 END IF;
802 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
803 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
804 var_return_date := max_week_date;
805 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
806 var_return_date := min_week_date;
807 ELSE
808 SELECT MAX(cal.week_start_date)
809 INTO var_return_date
810 FROM msc_cal_week_start_dates cal
811 WHERE cal.exception_set_id = var_exception_set_id
812 AND cal.calendar_code = arg_calendar_code
813 AND cal.week_start_date <= TRUNC(arg_date)
814 AND cal.sr_instance_id = arg_instance_id;
815 END IF;
816 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
817 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN
818 var_return_date := max_period_date;
819 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN
820 var_return_date := min_period_date;
821 ELSE
822 SELECT MAX(cal.period_start_date)
823 INTO var_return_date
824 FROM msc_period_start_dates cal
825 WHERE cal.exception_set_id = var_exception_set_id
826 AND cal.calendar_code = arg_calendar_code
827 AND cal.period_start_date <= TRUNC(arg_date);
828 END IF;
829 END IF;
830
831 return var_return_date;
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
835 APP_EXCEPTION.RAISE_EXCEPTION;
836 END CALENDAR_PREV_WORK_DAY;
837
838 --------------------------------------------------------------------------
839 -- CALENDAR_PREV_WORK_DAY_SEQNUM to fix bug# 2678523
840 -- Calendar code is an argument
841 --------------------------------------------------------------------------
842 FUNCTION CALENDAR_PREV_WORK_DAY_SEQNUM(arg_instance_id IN NUMBER,
843 arg_calendar_code IN VARCHAR2,
844 arg_bucket IN NUMBER,
845 arg_date IN DATE) RETURN NUMBER IS
846 BEGIN
847
848 var_exception_set_id := -1;
849
850
851 IF arg_bucket = TYPE_DAILY_BUCKET THEN
852 --dbms_output.put_line('Max date: ' || max_date || ' Min date: ' || min_date ||' Arg date: ' || arg_date);
853 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
854 var_return_number := max_seq_num;
855 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
856 var_return_number := min_seq_num;
857 ELSE
858 SELECT cal.prior_seq_num
859 INTO var_return_number
860 FROM msc_calendar_dates cal
861 WHERE cal.exception_set_id = var_exception_set_id
862 AND cal.calendar_code = arg_calendar_code
863 AND cal.calendar_date = TRUNC(arg_date);
864 END IF;
865
866 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
867 IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
868 var_return_number := max_week_seq_num;
869 ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
870 var_return_number := min_week_seq_num;
871 ELSE
872 SELECT MAX(cal.seq_num)
873 INTO var_return_number
874 FROM msc_cal_week_start_dates cal
875 WHERE cal.exception_set_id = var_exception_set_id
876 AND cal.calendar_code = arg_calendar_code
877 AND cal.week_start_date <= TRUNC(arg_date) ;
878 END IF;
879 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
880 raise_application_error(-20000, 'Invalid bucket type');
881 END IF;
882
883 return var_return_number;
884
885
886 EXCEPTION
887 WHEN NO_DATA_FOUND THEN
888 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
889 APP_EXCEPTION.RAISE_EXCEPTION;
890 END CALENDAR_PREV_WORK_DAY_SEQNUM;
891
892 ------------------------------------------------------------------------
893 -- CALENDAR_DAYS_BETWEEN to fix bug# 2678523
894 -- Calendar code is an argument
895 -------------------------------------------------------------------------
896 FUNCTION CALENDAR_DAYS_BETWEEN(arg_instance_id IN NUMBER,
897 arg_calendar_code IN VARCHAR2,
898 arg_bucket IN NUMBER,
899 arg_date1 IN DATE,
900 arg_date2 IN DATE) RETURN NUMBER IS
901
902
903 BEGIN
904
905 SELECT COUNT(*)
906 INTO var_return_number
907 FROM msc_calendar_dates
908 WHERE sr_instance_id = arg_instance_id
909 AND calendar_code = arg_calendar_code
910 AND exception_set_id = -1
911 AND seq_num is not null
912 AND calendar_date between arg_date1 and arg_date2;
913
914 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'SR ' || arg_instance_id || ' Return number days ' || var_return_number);
915 IF var_return_number = 0 THEN
916 var_return_number := null;
917 END IF;
918
919 return var_return_number;
920
921 EXCEPTION
922 WHEN NO_DATA_FOUND THEN
923 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
924 APP_EXCEPTION.RAISE_EXCEPTION;
925 END CALENDAR_DAYS_BETWEEN;
926
927 /*--------------------------------------------------------------------------
928 | Begin Functions added for ship_rec_cal project
929 +-------------------------------------------------------------------------*/
930
931 /* To be used by ATP. Wrapper on Get_Calendar_Code*/
932 FUNCTION Get_Calendar_Code(
933 p_instance_id IN number,
934 p_plan_id IN number,
935 p_inventory_item_id IN number,
936 p_partner_id IN number,
937 p_partner_site_id IN number,
938 p_partner_type IN number,
939 p_organization_id IN number,
940 p_ship_method_code IN varchar2,
941 p_calendar_type IN integer
942 ) RETURN VARCHAR2
943 IS
944 l_association_type NUMBER;
945 l_calendar_code VARCHAR2(14);
946 BEGIN
947 l_calendar_code := Get_Calendar_Code(
948 p_instance_id,
949 p_plan_id,
950 p_inventory_item_id,
951 p_partner_id,
952 p_partner_site_id,
953 p_partner_type,
954 p_organization_id,
955 p_ship_method_code,
956 p_calendar_type,
957 l_association_type);
958
959 RETURN l_calendar_code;
960 END Get_Calendar_Code;
961
962 /* Added this function to be called directly by UI. This has association_type as an additional out parameter*/
963 FUNCTION Get_Calendar_Code(
964 p_instance_id IN number,
965 p_plan_id IN number,
966 p_inventory_item_id IN number,
967 p_partner_id IN number,
968 p_partner_site_id IN number,
969 p_partner_type IN number,
970 p_organization_id IN number,
971 p_ship_method_code IN varchar2,
972 p_calendar_type IN integer,
973 p_association_type OUT NOCOPY NUMBER
974 ) RETURN VARCHAR2
975 IS
976 l_calendar_type VARCHAR2(15);
977 l_calendar_code VARCHAR2(14) := MSC_CALENDAR.FOC;
978 l_ship_method_code VARCHAR2(50) := NVL(p_ship_method_code, '@@@');
979 l_partner_site_id NUMBER := NVL(p_partner_site_id, -1);
980 C_ORGANIZATION CONSTANT NUMBER := 3;
981 C_ITEM_VENDOR CONSTANT NUMBER := 12;
982 C_ITEM_VENDOR_SITE CONSTANT NUMBER := 13;
983
984 BEGIN
985 IF PG_DEBUG in ('Y','C') THEN
986 msc_sch_wb.atp_debug ('***** Begin Function Get_Calendar_Code *****');
987 msc_sch_wb.atp_debug ('________________Input________________');
988 msc_sch_wb.atp_debug (' Instance ID : ' || p_instance_id );
989 msc_sch_wb.atp_debug (' Plan ID : ' || p_plan_id );
990 msc_sch_wb.atp_debug (' Inv Item ID : ' || p_inventory_item_id);
991 msc_sch_wb.atp_debug (' Partner ID : ' || p_partner_id );
992 msc_sch_wb.atp_debug (' Partner Site ID : ' || p_partner_site_id );
993 msc_sch_wb.atp_debug (' Partner Type : ' || p_partner_type);
994 msc_sch_wb.atp_debug (' Organization ID : ' || p_organization_id);
995 msc_sch_wb.atp_debug (' Ship Method Code : ' || p_ship_method_code);
996 msc_sch_wb.atp_debug (' Calendar Type : ' || p_calendar_type);
997 msc_sch_wb.atp_debug ('G_USE_SHIP_REC_CAL: ' || MSC_ATP_PVT.G_USE_SHIP_REC_CAL);
998 msc_sch_wb.atp_debug (' ');
999 END IF;
1000
1001 -- case 1. Searching for a valid suplier's shipping calendar (SSC) or valid customer receiving calendar (CRC)
1002 IF (p_calendar_type = MSC_CALENDAR.SSC OR p_calendar_type = MSC_CALENDAR.CRC) THEN
1003
1004 -- Bug 3593394
1005 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1006 -- l_calendar_code is already initialized to FOC
1007
1008 -- For SSC p_partner_type = 1, for CRC p_partner_type = 2
1009 IF p_calendar_type = MSC_CALENDAR.CRC THEN
1010 l_calendar_type := 'RECEIVING';
1011 ELSE
1012 l_calendar_type := 'SHIPPING';
1013 END IF;
1014
1015 SELECT calendar_code, association_type
1016 INTO l_calendar_code, p_association_type
1017 FROM (SELECT calendar_code, association_type
1018 FROM MSC_CALENDAR_ASSIGNMENTS
1019 WHERE SR_INSTANCE_ID = p_instance_id
1020 AND CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
1021 AND PARTNER_TYPE = p_partner_type
1022 AND PARTNER_ID = p_partner_id
1023 AND NVL(PARTNER_SITE_ID, l_partner_site_id) = l_partner_site_id
1024 AND NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
1025 ORDER BY ASSOCIATION_LEVEL)
1026 WHERE ROWNUM = 1;
1027
1028 END IF;
1029
1030 -- case 2. Searching for valid Org's Receiving Calendar (ORC) or Org's Shipping Calendar (OSC)
1031 ELSIF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC)
1032 AND p_organization_id IS NOT NULL THEN -- Condition added to handle scheduling cases where both
1033 -- org and customer are NULL. Done with Enforce Pur LT changes.
1034
1035 -- Bug 3593394
1036 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1037
1038 IF p_calendar_type = MSC_CALENDAR.ORC THEN
1039 l_calendar_type := 'RECEIVING';
1040 ELSE
1041 l_calendar_type := 'SHIPPING';
1042 END IF;
1043
1044 SELECT calendar_code, association_type
1045 INTO l_calendar_code, p_association_type
1046 FROM (SELECT calendar_code, association_type
1047 FROM MSC_CALENDAR_ASSIGNMENTS
1048 WHERE SR_INSTANCE_ID = p_instance_id
1049 AND CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
1050 AND ORGANIZATION_ID = p_organization_id
1051 AND NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
1052 ORDER BY ASSOCIATION_LEVEL)
1053 WHERE ROWNUM = 1;
1054
1055 ELSE
1056
1057 -- Bug 3647208 - For b/w compatibility use OMC instead of ORC/OSC
1058 -- Raise exception so that the OMC query gets executed.
1059 IF PG_DEBUG in ('Y','C') THEN
1060 msc_sch_wb.atp_debug ('Get_Calendar_Code :' || ' Use OMC instead on ORC/OSC');
1061 END IF;
1062 RAISE NO_DATA_FOUND;
1063
1064 END IF;
1065
1066 -- case 3. Searching for valid Intransit Calendar (VIC)
1067 ELSIF (p_calendar_type = MSC_CALENDAR.VIC) THEN
1068
1069 -- Bug 3593394
1070 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1071 -- l_calendar_code is already initialized to FOC
1072
1073 -- p_partner_type = 4
1074 SELECT calendar_code, association_type
1075 INTO l_calendar_code, p_association_type
1076 FROM MSC_CALENDAR_ASSIGNMENTS
1077 WHERE SR_INSTANCE_ID = p_instance_id
1078 AND PARTNER_TYPE = p_partner_type
1079 AND SHIP_METHOD_CODE = l_ship_method_code;
1080
1081 END IF;
1082
1083
1084 -- case 4. Searching for valid Suppliers Manufacturing Calendar (SMC)
1085 ELSIF (p_calendar_type = MSC_CALENDAR.SMC) THEN
1086
1087 -- No change required for Bug 3593394 here as this procedure will not be called
1088 -- for supplier case if G_USE_SHIP_REC_CAL='Y'
1089 -- SELECT delivery_calendar_code, association_type
1090 -- return FOC if ASL is defined but calendar is not associated.
1091 SELECT NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type
1092 INTO l_calendar_code, p_association_type
1093 FROM (SELECT delivery_calendar_code, decode(supplier_site_id,
1094 null, C_ITEM_VENDOR,
1095 C_ITEM_VENDOR_SITE) association_type
1096 FROM MSC_ITEM_SUPPLIERS
1097 WHERE SR_INSTANCE_ID = p_instance_id
1098 AND PLAN_ID = p_plan_id
1099 AND INVENTORY_ITEM_ID = p_inventory_item_id
1100 AND SUPPLIER_ID = p_partner_id
1101 AND NVL(SUPPLIER_SITE_ID, l_partner_site_id) = l_partner_site_id
1102 ORDER BY decode(supplier_site_id, null, C_ITEM_VENDOR, C_ITEM_VENDOR_SITE) desc
1103 )
1104 WHERE ROWNUM = 1;
1105
1106 -- case 5. Searching for valid Orgs Manufacturing Calendar (OMC)
1107 ELSIF (p_calendar_type = MSC_CALENDAR.OMC) THEN
1108
1109 SELECT calendar_code, C_ORGANIZATION
1110 INTO l_calendar_code, p_association_type
1111 FROM MSC_TRADING_PARTNERS
1112 WHERE SR_INSTANCE_ID = p_instance_id
1113 AND PARTNER_TYPE = 3
1114 AND SR_TP_ID = p_organization_id;
1115
1116 END IF;
1117
1118 IF PG_DEBUG in ('Y','C') THEN
1119 msc_sch_wb.atp_debug ('________________Output________________');
1120 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
1121 msc_sch_wb.atp_debug (' Association Type : ' || p_association_type);
1122 msc_sch_wb.atp_debug (' ');
1123 END IF;
1124
1125 RETURN l_calendar_code;
1126
1127 EXCEPTION
1128 WHEN NO_DATA_FOUND THEN
1129 IF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC) THEN
1130 -- Return OMC.
1131 SELECT calendar_code, C_ORGANIZATION
1132 INTO l_calendar_code, p_association_type
1133 FROM MSC_TRADING_PARTNERS
1134 WHERE SR_INSTANCE_ID = p_instance_id
1135 AND PARTNER_TYPE = 3
1136 AND SR_TP_ID = p_organization_id;
1137 END IF;
1138
1139 IF PG_DEBUG in ('Y','C') THEN
1140 msc_sch_wb.atp_debug ('****** No Data Found Exception *******');
1141 msc_sch_wb.atp_debug ('________________Output________________');
1142 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
1143 msc_sch_wb.atp_debug (' Association Type : ' || p_association_type);
1144 msc_sch_wb.atp_debug (' ');
1145 END IF;
1146
1147 RETURN l_calendar_code;
1148
1149 END Get_Calendar_Code;
1150
1151 /* Added this function to be called directly by UI/calendar window, with p_from_cal_window as an additional parameter*/
1152 FUNCTION Get_Calendar_Code(
1153 p_instance_id IN number,
1154 p_plan_id IN number,
1155 p_inventory_item_id IN number,
1156 p_partner_id IN number,
1157 p_partner_site_id IN number,
1158 p_partner_type IN number,
1159 p_organization_id IN number,
1160 p_ship_method_code IN varchar2,
1161 p_calendar_type IN integer,
1162 p_from_cal_window IN integer,
1163 p_association_type OUT NOCOPY NUMBER
1164 ) RETURN VARCHAR2
1165 IS
1166 l_calendar_type VARCHAR2(15);
1167 l_calendar_code VARCHAR2(14) := MSC_CALENDAR.FOC;
1168 l_ship_method_code VARCHAR2(50) := NVL(p_ship_method_code, '@@@');
1169 l_partner_site_id NUMBER := NVL(p_partner_site_id, -1);
1170 C_ORGANIZATION CONSTANT NUMBER := 3;
1171 C_ITEM_VENDOR CONSTANT NUMBER := 12;
1172 C_ITEM_VENDOR_SITE CONSTANT NUMBER := 13;
1173
1174 BEGIN
1175 IF PG_DEBUG in ('Y','C') THEN
1176 msc_sch_wb.atp_debug ('***** Begin Function Get_Calendar_Code *****');
1177 msc_sch_wb.atp_debug ('________________Input________________');
1178 msc_sch_wb.atp_debug (' Instance ID : ' || p_instance_id );
1179 msc_sch_wb.atp_debug (' Plan ID : ' || p_plan_id );
1180 msc_sch_wb.atp_debug (' Inv Item ID : ' || p_inventory_item_id);
1181 msc_sch_wb.atp_debug (' Partner ID : ' || p_partner_id );
1182 msc_sch_wb.atp_debug (' Partner Site ID : ' || p_partner_site_id );
1183 msc_sch_wb.atp_debug (' Partner Type : ' || p_partner_type);
1184 msc_sch_wb.atp_debug (' Organization ID : ' || p_organization_id);
1185 msc_sch_wb.atp_debug (' Ship Method Code : ' || p_ship_method_code);
1186 msc_sch_wb.atp_debug (' Calendar Type : ' || p_calendar_type);
1187 msc_sch_wb.atp_debug ('G_USE_SHIP_REC_CAL: ' || MSC_ATP_PVT.G_USE_SHIP_REC_CAL);
1188 msc_sch_wb.atp_debug (' ');
1189 END IF;
1190
1191 -- case 1. Searching for a valid suplier's shipping calendar (SSC) or valid customer receiving calendar (CRC)
1192 IF (p_calendar_type = MSC_CALENDAR.SSC OR p_calendar_type = MSC_CALENDAR.CRC) THEN
1193
1194 -- Bug 3593394
1195 --IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1196 -- l_calendar_code is already initialized to FOC
1197
1198 -- For SSC p_partner_type = 1, for CRC p_partner_type = 2
1199 IF p_calendar_type = MSC_CALENDAR.CRC THEN
1200 l_calendar_type := 'RECEIVING';
1201 ELSE
1202 l_calendar_type := 'SHIPPING';
1203 END IF;
1204
1205 SELECT calendar_code, association_type
1206 INTO l_calendar_code, p_association_type
1207 FROM (SELECT calendar_code, association_type
1208 FROM MSC_CALENDAR_ASSIGNMENTS
1209 WHERE SR_INSTANCE_ID = p_instance_id
1210 AND CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
1211 AND PARTNER_TYPE = p_partner_type
1212 AND PARTNER_ID = p_partner_id
1213 AND NVL(PARTNER_SITE_ID, l_partner_site_id) = l_partner_site_id
1214 AND NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
1215 ORDER BY ASSOCIATION_LEVEL)
1216 WHERE ROWNUM = 1;
1217 --END IF;
1218 -- case 2. Searching for valid Org's Receiving Calendar (ORC) or Org's Shipping Calendar (OSC)
1219 ELSIF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC)
1220 AND p_organization_id IS NOT NULL THEN -- Condition added to handle scheduling cases where both
1221 -- org and customer are NULL. Done with Enforce Pur LT changes.
1222
1223 -- Bug 3593394
1224 --IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1225 IF p_calendar_type = MSC_CALENDAR.ORC THEN
1226 l_calendar_type := 'RECEIVING';
1227 ELSE
1228 l_calendar_type := 'SHIPPING';
1229 END IF;
1230 SELECT calendar_code, association_type
1231 INTO l_calendar_code, p_association_type
1232 FROM (SELECT calendar_code, association_type
1233 FROM MSC_CALENDAR_ASSIGNMENTS
1234 WHERE SR_INSTANCE_ID = p_instance_id
1235 AND CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
1236 AND ORGANIZATION_ID = p_organization_id
1237 AND NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
1238 ORDER BY ASSOCIATION_LEVEL)
1239 WHERE ROWNUM = 1;
1240 /*ELSE
1241 -- Bug 3647208 - For b/w compatibility use OMC instead of ORC/OSC
1242 -- Raise exception so that the OMC query gets executed.
1243 IF PG_DEBUG in ('Y','C') THEN
1244 msc_sch_wb.atp_debug ('Get_Calendar_Code :' || ' Use OMC instead on ORC/OSC');
1245 END IF;
1246 RAISE NO_DATA_FOUND;
1247 END IF;*/
1248 -- case 3. Searching for valid Intransit Calendar (VIC)
1249 ELSIF (p_calendar_type = MSC_CALENDAR.VIC) THEN
1250 -- Bug 3593394
1251 --IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1252 -- l_calendar_code is already initialized to FOC
1253 -- p_partner_type = 4
1254 SELECT calendar_code, association_type
1255 INTO l_calendar_code, p_association_type
1256 FROM MSC_CALENDAR_ASSIGNMENTS
1257 WHERE SR_INSTANCE_ID = p_instance_id
1258 AND PARTNER_TYPE = p_partner_type
1259 AND SHIP_METHOD_CODE = l_ship_method_code;
1260 --END IF;
1261 -- case 4. Searching for valid Suppliers Manufacturing Calendar (SMC)
1262 ELSIF (p_calendar_type = MSC_CALENDAR.SMC) THEN
1263 -- No change required for Bug 3593394 here as this procedure will not be called
1264 -- for supplier case if G_USE_SHIP_REC_CAL='Y'
1265 -- SELECT delivery_calendar_code, association_type
1266 -- return FOC if ASL is defined but calendar is not associated.
1267 SELECT NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type
1268 INTO l_calendar_code, p_association_type
1269 FROM (SELECT delivery_calendar_code, decode(supplier_site_id,
1270 null, C_ITEM_VENDOR,
1271 C_ITEM_VENDOR_SITE) association_type
1272 FROM MSC_ITEM_SUPPLIERS
1273 WHERE SR_INSTANCE_ID = p_instance_id
1274 AND PLAN_ID = p_plan_id
1275 AND INVENTORY_ITEM_ID = p_inventory_item_id
1276 AND SUPPLIER_ID = p_partner_id
1277 AND NVL(SUPPLIER_SITE_ID, l_partner_site_id) = l_partner_site_id
1278 ORDER BY decode(supplier_site_id, null, C_ITEM_VENDOR, C_ITEM_VENDOR_SITE) desc
1279 )
1280 WHERE ROWNUM = 1;
1281 -- case 5. Searching for valid Orgs Manufacturing Calendar (OMC)
1282 ELSIF (p_calendar_type = MSC_CALENDAR.OMC) THEN
1283
1284 SELECT calendar_code, C_ORGANIZATION
1285 INTO l_calendar_code, p_association_type
1286 FROM MSC_TRADING_PARTNERS
1287 WHERE SR_INSTANCE_ID = p_instance_id
1288 AND PARTNER_TYPE = 3
1289 AND SR_TP_ID = p_organization_id;
1290 END IF;
1291 IF PG_DEBUG in ('Y','C') THEN
1292 msc_sch_wb.atp_debug ('________________Output________________');
1293 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
1294 msc_sch_wb.atp_debug (' Association Type : ' || p_association_type);
1295 msc_sch_wb.atp_debug (' ');
1296 END IF;
1297 RETURN l_calendar_code;
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 IF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC) THEN
1301 -- Return OMC.
1302 SELECT calendar_code, C_ORGANIZATION
1303 INTO l_calendar_code, p_association_type
1304 FROM MSC_TRADING_PARTNERS
1305 WHERE SR_INSTANCE_ID = p_instance_id
1306 AND PARTNER_TYPE = 3
1307 AND SR_TP_ID = p_organization_id;
1308 END IF;
1309
1310 IF PG_DEBUG in ('Y','C') THEN
1311 msc_sch_wb.atp_debug ('****** No Data Found Exception *******');
1312 msc_sch_wb.atp_debug ('________________Output________________');
1313 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
1314 msc_sch_wb.atp_debug (' Association Type : ' || p_association_type);
1315 msc_sch_wb.atp_debug (' ');
1316 END IF;
1317
1318 RETURN l_calendar_code;
1319
1320 END Get_Calendar_Code;
1321
1322 -- Overloaded Functions driven by calendar_code rather than org_id
1323 FUNCTION NEXT_WORK_DAY(
1324 p_calendar_code IN varchar2,
1325 p_instance_id IN number,
1326 p_calendar_date IN date
1327 ) RETURN DATE
1328 IS
1329 l_next_work_day DATE;
1330 l_first_work_day DATE;
1331 l_last_work_day DATE;
1332 BEGIN
1333 IF (p_calendar_code IS NULL) OR
1334 (p_instance_id IS NULL) OR
1335 (p_calendar_date IS NULL) THEN
1336 --RETURN NULL; bug3583705
1337 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1338 END IF;
1339
1340 IF (p_calendar_code = MSC_CALENDAR.FOC) THEN
1341 RETURN p_calendar_date;
1342 END IF;
1343
1344 BEGIN
1345 SELECT NEXT_DATE
1346 INTO l_next_work_day
1347 FROM MSC_CALENDAR_DATES
1348 WHERE SR_INSTANCE_ID = p_instance_id
1349 AND CALENDAR_CODE = p_calendar_code
1350 AND EXCEPTION_SET_ID = -1
1351 AND CALENDAR_DATE = TRUNC(p_calendar_date);
1352
1353 EXCEPTION
1354 WHEN NO_DATA_FOUND THEN
1355 IF G_RETAIN_DATE = 'Y' THEN
1356 BEGIN
1357 SELECT FIRST_WORKING_DATE, LAST_WORKING_DATE
1358 INTO l_first_work_day, l_last_work_day
1359 FROM MSC_CALENDARS
1360 WHERE SR_INSTANCE_ID = p_instance_id
1361 AND CALENDAR_CODE = p_calendar_code;
1362 EXCEPTION
1363 WHEN NO_DATA_FOUND THEN
1364 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1365 END;
1366
1367 IF p_calendar_date >= l_last_work_day THEN
1368 l_next_work_day := l_last_work_day;
1369 ELSIF p_calendar_date <= l_first_work_day THEN
1370 l_next_work_day := l_first_work_day;
1371 END IF;
1372 ELSE
1373 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
1374 APP_EXCEPTION.RAISE_EXCEPTION;
1375 END IF;
1376 END;
1377 RETURN l_next_work_day;
1378
1379 END NEXT_WORK_DAY;
1380
1381 FUNCTION PREV_WORK_DAY(
1382 p_calendar_code IN varchar2,
1383 p_instance_id IN number,
1384 p_calendar_date IN date
1385 ) RETURN DATE
1386 IS
1387 l_prev_work_day DATE;
1388 l_first_work_day DATE;
1389 l_last_work_day DATE;
1390 BEGIN
1391 IF (p_calendar_code IS NULL) OR
1392 (p_instance_id IS NULL) OR
1393 (p_calendar_date IS NULL) THEN
1394 --RETURN NULL; bug3583705
1395 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1396 END IF;
1397
1398 IF p_calendar_code = MSC_CALENDAR.FOC THEN
1399 RETURN p_calendar_date;
1400 END IF;
1401
1402 BEGIN
1403 SELECT PRIOR_DATE
1404 INTO l_prev_work_day
1405 FROM MSC_CALENDAR_DATES
1406 WHERE SR_INSTANCE_ID = p_instance_id
1407 AND CALENDAR_CODE = p_calendar_code
1408 AND EXCEPTION_SET_ID = -1
1409 AND CALENDAR_DATE = TRUNC(p_calendar_date);
1410
1411 EXCEPTION
1412 WHEN NO_DATA_FOUND THEN
1413 IF G_RETAIN_DATE = 'Y' THEN
1414 BEGIN
1415 SELECT FIRST_WORKING_DATE, LAST_WORKING_DATE
1416 INTO l_first_work_day, l_last_work_day
1417 FROM MSC_CALENDARS
1418 WHERE SR_INSTANCE_ID = p_instance_id
1419 AND CALENDAR_CODE = p_calendar_code;
1420 EXCEPTION
1421 WHEN NO_DATA_FOUND THEN
1422 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1423 END;
1424
1425 IF p_calendar_date >= l_last_work_day THEN
1426 l_prev_work_day := l_last_work_day;
1427 ELSIF p_calendar_date <= l_first_work_day THEN
1428 l_prev_work_day := l_first_work_day;
1429 END IF;
1430 ELSE
1431 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
1432 APP_EXCEPTION.RAISE_EXCEPTION;
1433 END IF;
1434 END;
1435 RETURN l_prev_work_day;
1436 END PREV_WORK_DAY;
1437
1438 FUNCTION DATE_OFFSET(
1439 p_calendar_code IN varchar2,
1440 p_instance_id IN number,
1441 p_calendar_date IN date,
1442 p_days_offset IN number,
1443 p_offset_type IN number
1444 ) RETURN DATE
1445 IS
1446 l_offsetted_day DATE;
1447 l_first_work_day DATE;
1448 l_last_work_day DATE;
1449 l_days_offset NUMBER;
1450 l_input_date DATE := p_calendar_date;--6625744
1451 BEGIN
1452 IF (p_calendar_code IS NULL) OR
1453 (p_instance_id IS NULL) OR
1454 (p_calendar_date IS NULL) OR
1455 (p_days_offset IS NULL) THEN
1456 --RETURN NULL; bug3583705
1457 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1458 END IF;
1459
1460 IF (p_days_offset = 0) and (p_calendar_code = MSC_CALENDAR.FOC) THEN
1461 RETURN p_calendar_date;
1462 ELSIF (p_days_offset = 0) and (p_offset_type = -1) THEN
1463 l_offsetted_day := MSC_CALENDAR.PREV_WORK_DAY(
1464 p_calendar_code,
1465 p_instance_id,
1466 p_calendar_date);
1467 ELSIF (p_days_offset = 0) and (p_offset_type = +1) THEN
1468 l_offsetted_day := MSC_CALENDAR.NEXT_WORK_DAY(
1469 p_calendar_code,
1470 p_instance_id,
1471 p_calendar_date);
1472 ELSE
1473 IF p_days_offset > 0 THEN
1474 l_days_offset := CEIL(p_days_offset);
1475 l_input_date := MSC_CALENDAR.NEXT_WORK_DAY( --6625744 START
1476 p_calendar_code,
1477 p_instance_id,
1478 p_calendar_date); --6625744 END
1479 ELSE
1480 l_days_offset := FLOOR(p_days_offset);
1481 l_input_date := MSC_CALENDAR.PREV_WORK_DAY( --6625744 START
1482 p_calendar_code,
1483 p_instance_id,
1484 p_calendar_date); --6625744 END
1485 END IF;
1486
1487 IF p_calendar_code = MSC_CALENDAR.FOC THEN
1488 RETURN p_calendar_date + l_days_offset;
1489 END IF;
1490
1491 IF p_days_offset > 0 THEN
1492 BEGIN
1493 SELECT cal2.calendar_date
1494 INTO l_offsetted_day
1495 FROM MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2
1496 WHERE cal1.sr_instance_id = p_instance_id
1497 AND cal1.calendar_code = p_calendar_code
1498 AND cal1.exception_set_id = -1
1499 AND cal1.calendar_date = TRUNC(l_input_date) --6625744
1500 AND cal2.sr_instance_id = cal1.sr_instance_id
1501 AND cal2.calendar_code = cal1.calendar_code
1502 AND cal2.exception_set_id = cal1.exception_set_id
1503 AND cal2.seq_num = cal1.prior_seq_num + l_days_offset;
1504 -- AND cal2.seq_num = cal1.next_seq_num + l_days_offset;
1505 -- (3335127) New offset logic. Done along with Enforce Pur LT changes
1506
1507 EXCEPTION
1508 WHEN NO_DATA_FOUND THEN
1509 IF G_RETAIN_DATE = 'Y' THEN
1510 BEGIN
1511 SELECT FIRST_WORKING_DATE, LAST_WORKING_DATE
1512 INTO l_first_work_day, l_last_work_day
1513 FROM MSC_CALENDARS
1514 WHERE SR_INSTANCE_ID = p_instance_id
1515 AND CALENDAR_CODE = p_calendar_code;
1516 EXCEPTION
1517 WHEN NO_DATA_FOUND THEN
1518 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1519 END;
1520
1521 /* Logic to retain dates was wrong. Fixed along with Enforce Pur LT changes
1522 IF p_calendar_date + l_days_offset >= l_last_work_day THEN
1523 l_offsetted_day := l_last_work_day;
1524 ELSIF p_calendar_date + l_days_offset <= l_first_work_day THEN
1525 l_offsetted_day := l_first_work_day;
1526 END IF;
1527 */
1528 IF p_calendar_date < l_first_work_day THEN
1529 l_offsetted_day := DATE_OFFSET(p_calendar_code,
1530 p_instance_id,
1531 l_first_work_day,
1532 p_days_offset,
1533 p_offset_type);
1534 ELSE
1535 l_offsetted_day := l_last_work_day;
1536 END IF;
1537
1538 ELSE
1539 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
1540 APP_EXCEPTION.RAISE_EXCEPTION;
1541 END IF;
1542 END;
1543 ELSE
1544 BEGIN
1545 SELECT cal2.calendar_date
1546 INTO l_offsetted_day
1547 FROM MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2
1548 WHERE cal1.sr_instance_id = p_instance_id
1549 AND cal1.calendar_code = p_calendar_code
1550 AND cal1.exception_set_id = -1
1551 AND cal1.calendar_date = TRUNC(l_input_date) --6625744
1552 AND cal2.sr_instance_id = cal1.sr_instance_id
1553 AND cal2.calendar_code = cal1.calendar_code
1554 AND cal2.exception_set_id = cal1.exception_set_id
1555 AND cal2.seq_num = cal1.next_seq_num + l_days_offset;
1556 -- AND cal2.seq_num = cal1.prior_seq_num + l_days_offset;
1557 -- (3335127) New offset logic. Done along with Enforce Pur LT changes
1558
1559 EXCEPTION
1560 WHEN NO_DATA_FOUND THEN
1561 IF G_RETAIN_DATE = 'Y' THEN
1562 BEGIN
1563 SELECT FIRST_WORKING_DATE, LAST_WORKING_DATE
1564 INTO l_first_work_day, l_last_work_day
1565 FROM MSC_CALENDARS
1566 WHERE SR_INSTANCE_ID = p_instance_id
1567 AND CALENDAR_CODE = p_calendar_code;
1568 EXCEPTION
1569 WHEN NO_DATA_FOUND THEN
1570 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
1571 END;
1572
1573 /* Logic to retain dates was wrong. Fixed along with Enforce Pur LT changes
1574 IF p_calendar_date + l_days_offset >= l_last_work_day THEN
1575 l_offsetted_day := l_last_work_day;
1576 ELSIF p_calendar_date + l_days_offset <= l_first_work_day THEN
1577 l_offsetted_day := l_first_work_day;
1578 END IF;
1579 */
1580 IF p_calendar_date > l_last_work_day THEN
1581 l_offsetted_day := DATE_OFFSET(p_calendar_code,
1582 p_instance_id,
1583 l_last_work_day,
1584 p_days_offset,
1585 p_offset_type);
1586 ELSE
1587 l_offsetted_day := l_first_work_day;
1588 END IF;
1589 ELSE
1590 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
1591 APP_EXCEPTION.RAISE_EXCEPTION;
1592 END IF;
1593 END;
1594 END IF;
1595 END IF;
1596
1597 RETURN l_offsetted_day;
1598
1599 END DATE_OFFSET;
1600
1601 FUNCTION THREE_STEP_CAL_OFFSET_DATE(
1602 p_input_date IN Date,
1603 p_first_cal_code IN VARCHAR2,
1604 p_first_cal_validation_type IN NUMBER,
1605 p_second_cal_code IN VARCHAR2,
1606 p_offset_days IN NUMBER,
1607 p_second_cal_validation_type IN NUMBER,
1608 p_third_cal_code IN VARCHAR2,
1609 p_third_cal_validation_type IN NUMBER,
1610 p_instance_id IN NUMBER
1611 ) RETURN DATE
1612 IS
1613 l_first_date DATE := NULL;
1614 l_second_date DATE := NULL;
1615 l_output_date DATE := NULL;
1616
1617 BEGIN
1618 IF PG_DEBUG in ('Y','C') THEN
1619 msc_sch_wb.atp_debug ('***** Begin Function THREE_STEP_CAL_OFFSET_DATE *****');
1620 msc_sch_wb.atp_debug ('________________Input________________');
1621 msc_sch_wb.atp_debug (' Input Date : ' || p_input_date );
1622 msc_sch_wb.atp_debug (' First Cal Code : ' || p_first_cal_code );
1623 msc_sch_wb.atp_debug (' Second Cal Code : ' || p_second_cal_code );
1624 msc_sch_wb.atp_debug (' Third Cal Code : ' || p_third_cal_code );
1625 msc_sch_wb.atp_debug (' Days Offset : ' || p_offset_days );
1626 msc_sch_wb.atp_debug (' ');
1627 END IF;
1628 -- First date is computed using p_input_date, first calendar and its validation_type
1629 IF p_first_cal_code = MSC_CALENDAR.FOC THEN
1630 l_first_date := p_input_date;
1631 ELSIF p_first_cal_validation_type = -1 THEN
1632 l_first_date := MSC_CALENDAR.PREV_WORK_DAY(
1633 p_first_cal_code,
1634 p_instance_id,
1635 p_input_date);
1636 ELSIF p_first_cal_validation_type = 1 THEN
1637 l_first_date := MSC_CALENDAR.NEXT_WORK_DAY(
1638 p_first_cal_code,
1639 p_instance_id,
1640 p_input_date);
1641 ELSE
1642 l_first_date := p_input_date;
1643 END IF;
1644
1645 IF PG_DEBUG in ('Y','C') THEN
1646 msc_sch_wb.atp_debug (' Date after validation on first cal: ' || l_first_date );
1647 END IF;
1648
1649 -- Second date is computed using first date, 2nd calendar and offset days
1650 IF (p_offset_days = 0) and (p_second_cal_code = MSC_CALENDAR.FOC) THEN
1651 l_second_date := l_first_date;
1652 ELSIF (p_offset_days = 0) and (p_second_cal_validation_type = -1) THEN
1653 l_second_date := MSC_CALENDAR.PREV_WORK_DAY(
1654 p_second_cal_code,
1655 p_instance_id,
1656 l_first_date);
1657 ELSIF (p_offset_days = 0) and (p_second_cal_validation_type = 1) THEN
1658 l_second_date := MSC_CALENDAR.NEXT_WORK_DAY(
1659 p_second_cal_code,
1660 p_instance_id,
1661 l_first_date);
1662 ELSIF p_second_cal_code = MSC_CALENDAR.FOC THEN
1663 l_second_date := l_first_date + p_offset_days;
1664 ELSIF p_offset_days > 0 THEN
1665 l_second_date := MSC_CALENDAR.DATE_OFFSET(
1666 p_second_cal_code,
1667 p_instance_id,
1668 l_first_date,
1669 p_offset_days,
1670 +1);
1671 ELSIF p_offset_days < 0 THEN
1672 l_second_date := MSC_CALENDAR.DATE_OFFSET(
1673 p_second_cal_code,
1674 p_instance_id,
1675 l_first_date,
1676 p_offset_days,
1677 -1);
1678 ELSE
1679 l_second_date := l_first_date;
1680 END IF;
1681
1682 IF PG_DEBUG in ('Y','C') THEN
1683 msc_sch_wb.atp_debug (' Date after offset using second cal: ' || l_second_date );
1684 END IF;
1685
1686 -- Third date = Output Date is computed using 2nd date, 3rd calendar and validation_type
1687 IF p_third_cal_code = MSC_CALENDAR.FOC THEN
1688 l_output_date := l_second_date;
1689 ELSIF p_third_cal_validation_type = -1 THEN
1690 l_output_date := MSC_CALENDAR.PREV_WORK_DAY(
1691 p_third_cal_code,
1692 p_instance_id,
1693 l_second_date);
1694 ELSIF p_third_cal_validation_type = 1 THEN
1695 l_output_date := MSC_CALENDAR.NEXT_WORK_DAY(
1696 p_third_cal_code,
1697 p_instance_id,
1698 l_second_date);
1699 ELSE
1700 l_output_date := l_second_date;
1701 END IF;
1702
1703 IF PG_DEBUG in ('Y','C') THEN
1704 msc_sch_wb.atp_debug (' Date after validation on third cal: ' || l_output_date );
1705 END IF;
1706
1707 RETURN l_output_date;
1708
1709 END THREE_STEP_CAL_OFFSET_DATE;
1710
1711 /*--------------------------------------------------------------------------
1712 | End Functions added for ship_rec_cal project
1713 +-------------------------------------------------------------------------*/
1714
1715 END MSC_CALENDAR;
1716
[Home] [Help]