eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CALENDAR dependencies on MSC_CALENDAR

Line 1: PACKAGE BODY MSC_CALENDAR AS

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:

Line 7: MSC_CALENDAR_RET_DATES NUMBER := 0;

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;

Line 8: msc_calendar_cal_code VARCHAR2(14) := '17438gdjh';

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;

Line 9: msc_calendar_excep_set NUMBER := -23453;

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;

Line 39: IF arg_calendar_code <> msc_calendar_cal_code OR

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)

Line 40: arg_exception_set_id <> msc_calendar_excep_set THEN

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

Line 45: FROM msc_calendar_dates

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;

Line 67: msc_calendar_cal_code := arg_calendar_code;

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

Line 68: msc_calendar_excep_set := arg_exception_set_id;

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:

Line 71: IF MSC_CALENDAR_RET_DATES = 0 THEN

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;

Line 75: MSC_CALENDAR_RET_DATES := 1;

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;

Line 77: MSC_CALENDAR_RET_DATES := 2;

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

Line 165: FROM msc_calendar_dates cal

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

Line 174: IF MSC_CALENDAR_RET_DATES = 1

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

Line 214: IF MSC_CALENDAR_RET_DATES = 1

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

Line 241: msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,

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);

Line 248: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN

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

Line 250: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN

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

Line 255: FROM msc_calendar_dates cal

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;

Line 263: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN

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

Line 265: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN

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

Line 278: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN

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;

Line 281: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN

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)

Line 311: msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,

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

Line 316: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN

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

Line 318: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN

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

Line 323: FROM msc_calendar_dates cal

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;

Line 330: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN

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

Line 332: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN

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

Line 344: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN

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

Line 346: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN

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

Line 371: msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,

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

Line 375: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN

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

Line 377: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN

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

Line 382: FROM msc_calendar_dates cal

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 ;

Line 389: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN

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

Line 391: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN

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

Line 417: msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,

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

Line 421: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN

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

Line 423: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN

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

Line 428: FROM msc_calendar_dates cal

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 ;

Line 435: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN

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

Line 437: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN

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

Line 475: msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,

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:

Line 529: msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,

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

Line 620: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN

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

Line 622: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN

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

Line 627: FROM msc_calendar_dates cal

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;

Line 654: IF arg_calendar_code <> msc_calendar_cal_code OR

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)

Line 655: arg_exception_set_id <> msc_calendar_excep_set THEN

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

Line 660: FROM msc_calendar_dates

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:

Line 679: msc_calendar_cal_code := arg_calendar_code;

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

Line 680: msc_calendar_excep_set := arg_exception_set_id;

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:

Line 683: IF MSC_CALENDAR_RET_DATES = 0 THEN

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;

Line 687: MSC_CALENDAR_RET_DATES := 1;

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;

Line 689: MSC_CALENDAR_RET_DATES := 2;

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

Line 722: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN

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

Line 724: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN

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

Line 729: FROM msc_calendar_dates cal

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;

Line 736: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN

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

Line 738: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN

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

Line 750: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN

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

Line 752: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN

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

Line 790: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN

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

Line 792: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN

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

Line 797: FROM msc_calendar_dates cal

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;

Line 803: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN

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

Line 805: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN

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

Line 817: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN

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

Line 819: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN

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

Line 853: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN

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

Line 855: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN

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

Line 860: FROM msc_calendar_dates cal

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;

Line 867: IF MSC_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN

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

Line 869: ELSIF MSC_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN

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

Line 907: FROM msc_calendar_dates

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

Line 977: l_calendar_code VARCHAR2(14) := MSC_CALENDAR.FOC;

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;

Line 1002: IF (p_calendar_type = MSC_CALENDAR.SSC OR p_calendar_type = MSC_CALENDAR.CRC) THEN

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

Line 1009: IF p_calendar_type = MSC_CALENDAR.CRC THEN

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;

Line 1018: FROM MSC_CALENDAR_ASSIGNMENTS

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

Line 1031: ELSIF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC)

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

Line 1038: IF p_calendar_type = MSC_CALENDAR.ORC THEN

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;

Line 1047: FROM MSC_CALENDAR_ASSIGNMENTS

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

Line 1067: ELSIF (p_calendar_type = MSC_CALENDAR.VIC) THEN

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

Line 1076: FROM MSC_CALENDAR_ASSIGNMENTS

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:

Line 1085: ELSIF (p_calendar_type = MSC_CALENDAR.SMC) THEN

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

Line 1091: SELECT NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type

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

Line 1107: ELSIF (p_calendar_type = MSC_CALENDAR.OMC) THEN

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

Line 1129: IF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC) THEN

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

Line 1167: l_calendar_code VARCHAR2(14) := MSC_CALENDAR.FOC;

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;

Line 1192: IF (p_calendar_type = MSC_CALENDAR.SSC OR p_calendar_type = MSC_CALENDAR.CRC) THEN

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

Line 1199: IF p_calendar_type = MSC_CALENDAR.CRC THEN

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;

Line 1208: FROM MSC_CALENDAR_ASSIGNMENTS

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

Line 1219: ELSIF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC)

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

Line 1225: IF p_calendar_type = MSC_CALENDAR.ORC THEN

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;

Line 1233: FROM MSC_CALENDAR_ASSIGNMENTS

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

Line 1249: ELSIF (p_calendar_type = MSC_CALENDAR.VIC) THEN

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

Line 1256: FROM MSC_CALENDAR_ASSIGNMENTS

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;

Line 1262: ELSIF (p_calendar_type = MSC_CALENDAR.SMC) THEN

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.

Line 1267: SELECT NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type

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

Line 1282: ELSIF (p_calendar_type = MSC_CALENDAR.OMC) THEN

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

Line 1300: IF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC) THEN

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

Line 1340: IF (p_calendar_code = MSC_CALENDAR.FOC) 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

Line 1347: FROM MSC_CALENDAR_DATES

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);

Line 1359: FROM MSC_CALENDARS

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

Line 1398: IF p_calendar_code = MSC_CALENDAR.FOC 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

Line 1405: FROM MSC_CALENDAR_DATES

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);

Line 1417: FROM MSC_CALENDARS

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

Line 1460: IF (p_days_offset = 0) and (p_calendar_code = MSC_CALENDAR.FOC) 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,

Line 1463: l_offsetted_day := MSC_CALENDAR.PREV_WORK_DAY(

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

Line 1468: l_offsetted_day := MSC_CALENDAR.NEXT_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

Line 1475: l_input_date := MSC_CALENDAR.NEXT_WORK_DAY( --6625744 START

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

Line 1481: l_input_date := MSC_CALENDAR.PREV_WORK_DAY( --6625744 START

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;

Line 1487: IF p_calendar_code = MSC_CALENDAR.FOC THEN

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

Line 1495: FROM MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2

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

Line 1513: FROM MSC_CALENDARS

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

Line 1547: FROM MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2

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

Line 1565: FROM MSC_CALENDARS

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

Line 1629: IF p_first_cal_code = MSC_CALENDAR.FOC THEN

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,

Line 1632: l_first_date := MSC_CALENDAR.PREV_WORK_DAY(

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

Line 1637: l_first_date := MSC_CALENDAR.NEXT_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

Line 1650: IF (p_offset_days = 0) and (p_second_cal_code = MSC_CALENDAR.FOC) 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,

Line 1653: l_second_date := MSC_CALENDAR.PREV_WORK_DAY(

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

Line 1658: l_second_date := MSC_CALENDAR.NEXT_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

Line 1662: ELSIF p_second_cal_code = MSC_CALENDAR.FOC 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,

Line 1665: l_second_date := MSC_CALENDAR.DATE_OFFSET(

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,

Line 1672: l_second_date := MSC_CALENDAR.DATE_OFFSET(

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,

Line 1687: IF p_third_cal_code = MSC_CALENDAR.FOC 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,

Line 1690: l_output_date := MSC_CALENDAR.PREV_WORK_DAY(

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

Line 1695: l_output_date := MSC_CALENDAR.NEXT_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

Line 1715: END MSC_CALENDAR;

1711: /*--------------------------------------------------------------------------
1712: | End Functions added for ship_rec_cal project
1713: +-------------------------------------------------------------------------*/
1714:
1715: END MSC_CALENDAR;
1716: