[Home] [Help]
PACKAGE BODY: APPS.GMP_CALENDAR_API
Source
1 PACKAGE BODY gmp_calendar_api AS
2 /* $Header: GMPCAPIB.pls 120.5.12010000.1 2008/07/30 06:15:08 appldev ship $ */
3
4 PROCEDURE check_cal_data(
5 p_calendar_code IN VARCHAR2,
6 p_date IN DATE,
7 x_return_status OUT NOCOPY VARCHAR2);
8
9 PROCEDURE check_contig_periods(
10 p_calendar_code IN VARCHAR2,
11 p_start_date IN DATE,
12 p_end_date IN DATE,
13 p_duration IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2);
15
16 PROCEDURE check_all_dates(
17 p_calendar_code IN VARCHAR2,
18 p_start_date IN DATE,
19 p_end_date IN DATE,
20 x_return_status OUT NOCOPY VARCHAR2);
21
22 /* B3194180 Rajesh D. Patangya Dynamic statement for LEAD function */
23 TYPE interval_typ is RECORD
24 (
25 calendar_date date,
26 next_date date,
27 day_diff number,
28 l_working number
29 );
30
31 TYPE interval_tab is table of interval_typ index by BINARY_INTEGER;
32 interval_record interval_typ;
33
34 /* Declare Cursor Types */
35 TYPE ref_cursor_typ is REF CURSOR;
36 /* Declare global variables */
37 zero_date DATE := sysdate - 3650 ; /* B3278900 */
38 max_date DATE := sysdate + 3650 ; /* B3278900 */
39
40 /*
41 |==========================================================================
42 | Procedure: |
43 | is_working_day |
44 | |
45 | DESCRIPTION: |
46 | |
47 | API returns if the date passed for a calendar is a work day or a |
48 | Non Work day |
49 | |
50 | History : |
51 | Sridhar 21-AUG-2003 Initial implementation |
52 ==========================================================================
53 */
54
55 FUNCTION is_working_day(
56 p_api_version IN NUMBER,
57 p_init_msg_list IN BOOLEAN := TRUE,
58 p_calendar_code IN VARCHAR2,
59 p_date IN DATE,
60 x_return_status IN OUT NOCOPY VARCHAR2
61 ) RETURN BOOLEAN
62 IS
63
64 /* Local variable section */
65 l_api_name CONSTANT VARCHAR2(30) := 'IS_WORKING_DAY';
66 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
67
68 CURSOR get_day_cur (c_calendar_id VARCHAR2 , c_cal_date DATE) IS
69 SELECT 1 FROM dual
70 WHERE EXISTS
71 (SELECT 1
72 FROM BOM_shift_dates sd, bom_shift_times sht
73 WHERE sd.calendar_code = sht.calendar_code
74 AND sd.shift_date = trunc(c_cal_date)
75 AND sd.shift_num = sht.shift_num
76 AND sd.calendar_code = c_calendar_id
77 AND sd.SEQ_NUM is NOT NULL
78 AND sht.to_time > sht.from_time);
79 --
80 CURSOR Cur_cal_check IS
81 SELECT COUNT(1)
82 FROM bom_Calendars
83 WHERE calendar_code = p_calendar_code;
84
85 CURSOR Cur_cal_date IS
86 SELECT calendar_start_date,
87 calendar_end_date
88 FROM bom_calendars
89 WHERE calendar_code = p_calendar_code;
90
91 l_duration NUMBER := 0 ;
92 v_min_date date ;
93 v_max_date date;
94 l_count NUMBER := 0;
95
96 /* Define Exceptions */
97 CALENDAR_REQUIRED EXCEPTION;
98 INVALID_DATA_PASSED EXCEPTION;
99 INVALID_VERSION EXCEPTION;
100 DATE_OUT_OF_CAL_RANGE EXCEPTION;
101 PS_INVALID_CALENDAR EXCEPTION;
102 X_msg varchar2(2000) := '';
103
104 BEGIN
105
106 /* Set the return status to success initially */
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108
109 /* Initialize message list and count if needed */
110 IF p_init_msg_list THEN
111 fnd_msg_pub.initialize;
112 END IF;
113
114 /* Make sure we are call compatible */
115 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
116 ,p_api_version
117 ,'IS_WORKING_DAY'
118 ,GMP_CALENDAR_API.m_pkg_name) THEN
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 RAISE INVALID_VERSION;
121 END IF;
122
123 IF ((p_calendar_code is NOT NULL) AND (p_date is NOT NULL ))
124 THEN
125
126 /* Check if the Calendar Id passed is Valid or Nor */
127 OPEN Cur_cal_check;
128 FETCH Cur_cal_check into l_count;
129 CLOSE Cur_cal_check;
130
131 IF l_count = 0
132 THEN
133 RAISE PS_INVALID_CALENDAR;
134 END IF;
135 /* Check If Date passed is Out of Calendar Range first */
136 OPEN Cur_cal_date;
137 FETCH Cur_cal_date INTO v_min_date,v_max_date;
138 CLOSE Cur_cal_date;
139
140 IF ((p_date < v_min_date) OR (p_date > v_max_date ))
141 THEN
142 RAISE DATE_OUT_OF_CAL_RANGE;
143 END IF;
144
145 --
146 OPEN get_day_cur(p_calendar_code , p_date) ;
147 FETCH get_day_cur INTO l_duration ;
148 CLOSE get_day_cur ;
149
150 IF l_duration > 0 THEN
151 RETURN TRUE ;
152 ELSE
153 RETURN FALSE ;
154 END IF ;
155 ELSE
156 x_return_status := 'E';
157 X_msg := 'Calendar/Date ';
158 RAISE CALENDAR_REQUIRED;
159 END IF;
160
161 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
162
163 EXCEPTION
164 WHEN INVALID_DATA_PASSED OR invalid_version THEN
165
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
168 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
169 FND_MSG_PUB.ADD;
170 RETURN FALSE ;
171
172 WHEN CALENDAR_REQUIRED THEN
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
175 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
176 FND_MSG_PUB.ADD;
177 RETURN FALSE ;
178
179 WHEN DATE_OUT_OF_CAL_RANGE THEN
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181 FND_MESSAGE.SET_NAME('GMP','GMP_DATE_OUT_OF_CAL_RANGE');
182 FND_MSG_PUB.ADD;
183 RETURN TRUE;
184
185 WHEN PS_INVALID_CALENDAR THEN
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
188 FND_MSG_PUB.ADD;
189 RETURN FALSE ;
190
191 WHEN OTHERS THEN
192 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
193 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
194 FND_MSG_PUB.ADD;
195 x_return_status := FND_API.g_ret_sts_unexp_error;
196 RETURN FALSE ;
197 END is_working_day;
198
199 /*
200 |==========================================================================
201 | Procedure: |
202 | get_contiguous_periods |
203 | |
204 | DESCRIPTION: |
205 | |
206 | The API calculates contiguous periods for a given Calendar Id, Start or
207 | End Date and a duration. If Start Date is given the duration is calculated
208 | from the Start date and the calendar dates and durations are returned, If
209 | end date is given, the duration is calculated from the end date backwards
210 | and the calendar dates and the durations are returned in a Output PL/sql
211 | table
212 |
213 | History :
214 | Abhay 08/21/2003 Initial implementation
215 | Sridhar 10/08/2003 Added CEIL function for Date Differences |
216 | B3167015 |
217 | Sridhar 03/24/2004 CEIL Function is used wherever remaining |
218 | duration assignment is used |
219 +========================================================================== +
220 */
221
222 PROCEDURE get_contiguous_periods(
223 p_api_version IN NUMBER,
224 p_init_msg_list IN BOOLEAN := TRUE,
225 p_start_date IN DATE,
226 p_end_date IN DATE,
227 p_calendar_code IN VARCHAR2,
228 p_duration IN NUMBER,
229 p_output_tbl OUT NOCOPY contig_period_tbl,
230 x_return_status IN OUT NOCOPY VARCHAR2
231 )IS
232
233 CURSOR start_date_cur (c_calendar_code VARCHAR2,
234 c_start_date DATE) IS
235 SELECT sd.shift_date calendar_date,
236 sd.shift_num shift_num,
237 st.from_time from_time,
238 decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time)) duration,
239 st.to_time to_time
240 FROM bom_calendars cal,
241 bom_shift_dates sd,
242 bom_shift_times st
243 WHERE cal.calendar_code = c_calendar_code
244 AND sd.calendar_code = cal.calendar_code
245 AND st.calendar_code = sd.calendar_code
246 AND sd.shift_num = st.shift_num
247 AND sd.seq_num IS NOT NULL
248 AND (sd.shift_date + (st.from_time + decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time)))/86400) > c_start_date
249 ORDER BY sd.shift_date ,
250 st.from_time ,
251 st.to_time ;
252
253 CURSOR end_date_cur (c_calendar_code VARCHAR2,
254 c_end_date DATE) IS
255 SELECT sd.shift_date calendar_date,
256 sd.shift_num shift_num,
257 st.from_time from_time,
258 decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time)) duration,
259 st.to_time to_time
260 FROM bom_calendars cal,
261 bom_shift_dates sd,
262 bom_shift_times st
263 WHERE cal.calendar_code = c_calendar_code
264 AND sd.calendar_code = cal.calendar_code
265 AND st.calendar_code = sd.calendar_code
266 AND sd.shift_num = st.shift_num
267 AND sd.seq_num IS NOT NULL
268 AND (sd.shift_date + st.from_time/86400) < c_end_date
269 ORDER BY sd.shift_date DESC,
270 (st.from_time + decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time))) DESC,
271 st.from_time DESC ;
272
273 o_cnt INTEGER := 0 ;
274 i INTEGER := 1 ;
275 remaining_duration NUMBER := 0;
276 previous_start_date DATE ;
277 current_start_date DATE ;
278 current_end_date DATE ;
279 previous_end_date DATE ;
280 contig_start_date DATE ;
281 contig_end_date DATE ;
282 contig_duration NUMBER := 0 ;
283
284 /* Local variable section */
285
286 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTIGUOUS_PERIODS';
287 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
288
289 /* Define Exceptions */
290 CALENDAR_REQUIRED EXCEPTION;
291 CONTIG_PERIODS_FAILURE EXCEPTION;
292 INVALID_VERSION EXCEPTION;
293 ZERO_DURATION EXCEPTION;
294 X_msg varchar2(2000) := '';
295 l_date DATE;
296
297 BEGIN
298
299 /* Set the return status to success initially */
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 /* Initialize message list and count if needed */
303 IF p_init_msg_list THEN
304 fnd_msg_pub.initialize;
305 END IF;
306
307 /* Make sure we are call compatible */
308 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
309 ,p_api_version
310 ,'GET_CONTIGUOUS_PERIODS'
311 ,GMP_CALENDAR_API.m_pkg_name) THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 RAISE INVALID_VERSION;
314 END IF;
315
316 /* { */
317 IF (p_calendar_code is NOT NULL) AND ((p_start_date is NOT NULL) OR
318 (p_end_date is NOT NULL)) AND
319 (p_duration is NOT NULL )
320 THEN
321 check_contig_periods(
322 p_calendar_code,
323 p_start_date,
324 p_end_date,
325 p_duration,
326 l_return_status
327 );
328 /* { */
329 IF l_return_status = 'E'
330 THEN
331 RAISE CONTIG_PERIODS_FAILURE;
332 ELSE
333 /* Handling the 0 duration case right before the Looping starts */
334 IF (p_duration = 0)
335 THEN
336 IF (p_start_date is NOT NULL)
337 THEN
338 l_date := p_start_date;
339 ELSIF(p_end_date is NOT NULL)
340 THEN
341 l_date := p_end_date;
342 END IF;
343
344 p_output_tbl(1).start_date := l_date ;
345 p_output_tbl(1).duration := 0;
346 p_output_tbl(1).end_date := l_date;
347 RAISE ZERO_DURATION;
348 END IF;
349 --
350 -- remaining_duration := p_duration * 3600 ;
351 remaining_duration := CEIL(p_duration * 3600) ; /* B3361082 */
352 /* B3361082 - CEIL is used, where remaining_duration value is assigned */
353
354 IF p_start_date is NOT NULL THEN
355 /* { */
356 current_start_date := zero_date ;
357 current_end_date := zero_date ;
358 contig_start_date := zero_date ;
359 contig_end_date := zero_date ;
360 previous_end_date := zero_date ;
361 previous_start_date := zero_date ;
362
363 x_return_status := 'S';
364 --
365 FOR cur_rec in start_date_cur (p_calendar_code, p_start_date)
366 LOOP
367 IF cur_rec.calendar_date + (cur_rec.from_time /86400) >
368 previous_end_date THEN
369 current_start_date := cur_rec.calendar_date +
370 cur_rec.from_time /86400 ;
371 END IF ;
372 IF (current_start_date <> previous_start_date AND
373 previous_start_date <> zero_date ) OR
374 remaining_duration <= 0 THEN
375 o_cnt := o_cnt + 1 ;
376 p_output_tbl(o_cnt).start_date := contig_start_date ;
377 p_output_tbl(o_cnt).duration := (contig_duration/3600) ;
378 p_output_tbl(o_cnt).end_date := p_output_tbl(o_cnt).start_date + (p_output_tbl(o_cnt).duration)/24;
379 contig_start_date := zero_date ;
380 END IF ;
381 IF remaining_duration <= 0 THEN
382 EXIT ;
383 END IF ;
384
385 IF cur_rec.calendar_date +
386 ((cur_rec.from_time+cur_rec.duration) /86400) > current_end_date
387 THEN
388 current_end_date := cur_rec.calendar_date + ((cur_rec.from_time + cur_rec.duration)/86400) ;
389 END IF ;
390 --
394 ELSE
391 IF p_start_date > current_start_date AND
392 p_start_date < current_end_date THEN
393 contig_start_date := p_start_date ;
395 contig_start_date := current_start_date ;
396 END IF ;
397 --
398 IF current_start_date = previous_start_date THEN
399 IF current_end_date > previous_end_date THEN
400 IF remaining_duration >
401 CEIL((current_end_date - previous_end_date ) * 86400) THEN
402 contig_duration := contig_duration + CEIL((current_end_date - previous_end_date ) * 86400) ;
403 remaining_duration := CEIL(remaining_duration - CEIL((current_end_date - previous_end_date ) * 86400)) ;
404 ELSE
405 contig_duration := contig_duration + remaining_duration ;
406 remaining_duration := CEIL(remaining_duration - remaining_duration) ;
407 END IF ;
408 END IF ;
409 ELSE
410 IF remaining_duration <
411 CEIL((current_end_date - contig_start_date ) * 86400) THEN
412 contig_duration := remaining_duration ;
413 remaining_duration := CEIL(remaining_duration - contig_duration) ;
414 ELSE
415 IF remaining_duration >
416 CEIL((current_end_date - contig_start_date ) * 86400) THEN
417 contig_duration :=
418 (current_end_date - contig_start_date ) * 86400 ;
419 remaining_duration := CEIL(remaining_duration - contig_duration) ;
420 ELSE
421 contig_duration := remaining_duration ;
422 remaining_duration := CEIL(remaining_duration - remaining_duration) ;
423 END IF;
424 END IF ;
425 END IF ;
426 IF previous_start_date = zero_date AND
427 remaining_duration <= 0 THEN
428 o_cnt := o_cnt + 1 ;
429 p_output_tbl(o_cnt).start_date := contig_start_date ;
430 p_output_tbl(o_cnt).duration := (contig_duration/3600) ;
431 p_output_tbl(o_cnt).end_date := p_output_tbl(o_cnt).start_date + (p_output_tbl(o_cnt).duration)/24;
432 EXIT ;
433 END IF ;
434
435 previous_start_date := current_start_date ;
436 previous_end_date := current_end_date ;
437 END LOOP ;
438
439 IF remaining_duration > 0 THEN
440 p_output_tbl.DELETE ;
441 END IF ;
442
443 /* } */
444 -- ====***===***===***===***END DATE***===***===***===***===***===
445 /* { */
446 ELSIF p_end_date is NOT NULL THEN
447 current_start_date := max_date ;
448 current_end_date := max_date ;
449 contig_start_date := max_date ;
450 contig_end_date := max_date ;
451 previous_end_date := max_date ;
452 previous_start_date := max_date ;
453 x_return_status := 'S';
454 --
455
456 FOR cur_rec in end_date_cur (p_calendar_code, p_end_date)
457 LOOP
458
459 IF cur_rec.calendar_date + ((cur_rec.from_time + cur_rec.duration) /86400) <
460 previous_start_date THEN
461 current_end_date := cur_rec.calendar_date + ((cur_rec.from_time+cur_rec.duration) /86400) ;
462 END IF ;
463
464 IF (current_end_date <> previous_end_date AND
465 previous_end_date <> max_date ) OR
466 remaining_duration <= 0 THEN
467 o_cnt := o_cnt + 1 ;
468 p_output_tbl(o_cnt).start_date :=
469 contig_end_date - contig_duration/86400 ;
470 p_output_tbl(o_cnt).duration := (contig_duration/3600) ;
471 p_output_tbl(o_cnt).end_date := p_output_tbl(o_cnt).start_date + (p_output_tbl(o_cnt).duration)/24;
472 END IF ;
473
474 IF remaining_duration <= 0 THEN
475 EXIT ;
476 END IF ;
477
478 IF cur_rec.calendar_date + (cur_rec.from_time /86400) <
479 current_start_date THEN
480 current_start_date := cur_rec.calendar_date + (cur_rec.from_time /86400) ;
481 END IF ;
482
483 IF p_end_date > current_start_date AND p_end_date <
484 current_end_date THEN
485 contig_end_date := p_end_date ;
486 ELSE
487 contig_end_date := current_end_date ;
488 END IF ;
489 /* ----------NEW------------- */
490 IF current_end_date = previous_end_date THEN
491 IF current_start_date < previous_start_date THEN
492 IF remaining_duration >
493 CEIL((previous_start_date - current_start_date ) * 86400) THEN
494 contig_duration := contig_duration + CEIL((previous_start_date - current_start_date ) * 86400) ;
495 remaining_duration := CEIL(remaining_duration - CEIL((previous_start_date - current_start_date ) * 86400)) ;
496 ELSE
497 contig_duration := contig_duration + remaining_duration ;
498 remaining_duration := CEIL(remaining_duration - remaining_duration);
499 END IF ;
503 CEIL((current_start_date - contig_end_date ) * 86400) THEN
500 END IF ;
501 ELSE
502 IF p_duration * 3600 <
504 contig_duration := p_duration * 3600 ;
505 remaining_duration := CEIL(remaining_duration - contig_duration) ;
506 ELSE
507 IF remaining_duration >
508 CEIL((contig_end_date - current_start_date ) * 86400) THEN
509 contig_duration :=
510 (contig_end_date - current_start_date) * 86400 ;
511 remaining_duration := CEIL(remaining_duration - contig_duration);
512 ELSE
513 contig_duration := remaining_duration ;
514 remaining_duration := CEIL(remaining_duration - remaining_duration);
515 END IF;
516 END IF ;
517 END IF ;
518 IF previous_end_date = max_date AND
519 remaining_duration <= 0 THEN
520 o_cnt := o_cnt + 1 ;
521 p_output_tbl(o_cnt).start_date := contig_end_date - (p_duration/24) ;
522 p_output_tbl(o_cnt).duration := (contig_duration/3600) ;
523 p_output_tbl(o_cnt).end_date := p_output_tbl(o_cnt).start_date + (p_output_tbl(o_cnt).duration)/24;
524 EXIT ;
525 END IF ;
526
527 previous_start_date := current_start_date ;
528 previous_end_date := current_end_date ;
529
530 END LOOP ;
531
532 IF remaining_duration > 0 THEN
533 p_output_tbl.DELETE ;
534 END IF ;
535
536 ELSE
537 FND_MESSAGE.SET_NAME('GMP','GMP_ENTER_START_OR_END_DATE');
538 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
539 FND_MSG_PUB.ADD;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541
542 /* } */
543 END IF ;
544 /* } */
545 END IF;
546 ELSE
547 x_return_status := 'E';
548 X_msg := 'Calendar/Start or End Date ';
549 RAISE CALENDAR_REQUIRED;
550 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
551 FND_MSG_PUB.ADD;
552 END IF;
553 /* } */
554
555 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
556
557
558 EXCEPTION
559 WHEN CONTIG_PERIODS_FAILURE OR INVALID_VERSION THEN
560 x_return_status := FND_API.G_RET_STS_ERROR;
561
562 WHEN CALENDAR_REQUIRED THEN
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
565 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
566 FND_MSG_PUB.ADD;
567
568 WHEN ZERO_DURATION THEN
569 NULL;
570
571 WHEN OTHERS THEN
572 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
573 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
574 FND_MSG_PUB.ADD;
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 END get_contiguous_periods;
577 /*
578 ==========================================================================
579 Procedure:
580 check_contig_periods
581
582 DESCRIPTION:
583
584 The following Procedure checks the data passed and Returns S code If
585 Successful
586
587 History :
588 Sgidugu 08/21/2003 Initial implementation
589 ==========================================================================
590 */
591 PROCEDURE check_contig_periods(
592 p_calendar_code IN VARCHAR2,
593 p_start_date IN DATE,
594 p_end_date IN DATE,
595 p_duration IN NUMBER,
596 x_return_status OUT NOCOPY VARCHAR2) IS
597
598 CURSOR Cur_cal_check ( c_calendar_code VARCHAR2 ) IS
599 SELECT COUNT(1)
600 FROM bom_Calendars
601 WHERE calendar_code = c_calendar_code;
602
603 CURSOR Cur_cal_date ( c_calendar_code VARCHAR2 ) IS
604 SELECT calendar_start_date ,
605 calendar_end_date
606 FROM bom_Calendars
607 WHERE calendar_code = c_calendar_code;
608
609 v_min_date date;
610 v_max_date date;
611
612 INVALID_DATE_RANGE EXCEPTION;
613 CALENDAR_NULL EXCEPTION;
614 INVALID_VALUE EXCEPTION;
615 GMP_DATE_NOT_IN_CAL_RANGE EXCEPTION;
616 ENTER_START_OR_END_DATE EXCEPTION;
617 PS_INVALID_CALENDAR EXCEPTION;
618
619 X_field varchar2(2000) := '';
620 X_value varchar2(2000) := '';
621 X_msg varchar2(2000) := '';
622 l_count number := 0;
623
624 begin
628 FETCH Cur_cal_date into v_min_date,v_max_date;
625 x_return_status := 'S';
626
627 OPEN Cur_cal_date (p_calendar_code);
629 CLOSE Cur_cal_date;
630
631 if p_duration < 0
632 then
633 x_return_status := 'E';
634 X_field := 'Duration';
635 X_value := p_duration;
636 RAISE INVALID_VALUE;
637 end if;
638
639 /* We could write an ELSE condition to make the logic complete, but is
640 not needed as calling proc makes sure one and only one date is NOT NULL
641 */
642
643 IF p_start_date IS NOT NULL THEN
644 IF (p_start_date < v_min_date) OR (p_start_date > v_max_date)
645 THEN
646 x_return_status := 'E';
647 RAISE GMP_DATE_NOT_IN_CAL_RANGE;
648 END IF;
649 ELSIF p_end_date IS NOT NULL THEN
650 IF (p_end_date < v_min_date) OR (p_end_date > v_max_date)
651 THEN
652 x_return_status := 'E';
653 RAISE GMP_DATE_NOT_IN_CAL_RANGE;
654 END IF;
655 END IF ;
656
657 OPEN Cur_cal_check (p_calendar_code);
658 FETCH Cur_cal_check INTO l_count;
659 CLOSE Cur_cal_check;
660 --
661 IF l_count = 0
662 THEN
663 RAISE PS_INVALID_CALENDAR;
664 END IF;
665 --
666 /* Erroring Out when Both Start Date and End Date is Passed at the same time
667 */
668 if ((p_start_date is NOT NULL) AND
669 (p_end_date is NOT NULL ))
670 then
671 x_return_status := 'E';
672 RAISE ENTER_START_OR_END_DATE;
673 end If;
674
675
676 EXCEPTION
677 WHEN GMP_DATE_NOT_IN_CAL_RANGE THEN
678 x_return_status := FND_API.G_RET_STS_ERROR;
679 FND_FILE.PUT_LINE(FND_FILE.LOG,'Date Passed is Out of Calendar Range '||X_msg);
680 FND_MESSAGE.SET_NAME('GMP','GMP_DATE_NOT_IN_CAL_RANGE');
681 FND_MSG_PUB.ADD;
682
683 WHEN ENTER_START_OR_END_DATE THEN
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enter Start Or End Date '||X_msg);
686 FND_MESSAGE.SET_NAME('GMP','GMP_ENTER_START_OR_END_DATE');
687 FND_MSG_PUB.ADD;
688
689 WHEN PS_INVALID_CALENDAR THEN
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
692 FND_MSG_PUB.ADD;
693
694 WHEN INVALID_VALUE THEN
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
697 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
698 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
699 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
700 FND_MSG_PUB.ADD;
701 END check_contig_periods;
702
703 /* *****************************************************************
704 Gantt Chart APIs
705 *****************************************************************
706 ==========================================================================
707 Procedure:
708 get_all_dates
709
710 DESCRIPTION:
711
712 The following Procedure gets the Working and Non-Working dates between two
713 specified Start and End Dates in a Calendar
714
715 History :
716 Sgidugu 08/21/2003 Initial implementation
717 ==========================================================================
718 */
719
720 PROCEDURE get_all_dates(
721 p_api_version IN NUMBER,
722 p_init_msg_list IN BOOLEAN := TRUE,
723 p_calendar_code IN VARCHAR2,
724 p_start_date IN DATE,
725 p_end_date IN DATE,
726 p_output_tbl OUT NOCOPY date_tbl,
727 x_return_status IN OUT NOCOPY VARCHAR2
728 ) IS
729
730 CURSOR get_all_dates (c_calendar_code VARCHAR2,
731 c_start_date DATE,
732 c_end_date DATE) IS
733 SELECT sd.shift_date calendar_date,
734 decode(SUM(decode(sd.seq_num,NULL,0,1)),0,0,1) l_work_day
735 FROM bom_calendars cal,
736 bom_shift_dates sd,
737 bom_shift_times st
738 WHERE cal.calendar_code = c_calendar_code
739 AND sd.calendar_code = cal.calendar_code
740 AND st.calendar_code = sd.calendar_code
741 AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
742 AND sd.shift_num = st.shift_num
743 GROUP BY sd.shift_date
744 ORDER BY sd.shift_date; /*B5182025 - sowsubra - added order by clause*/
745
746 i INTEGER := 0 ;
747 add_day INTEGER := 1 ;
748
749 /* Local variable section */
750
751 l_api_name CONSTANT VARCHAR2(30) := 'GET_ALL_DATES';
752 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
753
754 /* Define Exceptions */
755 CALENDAR_REQUIRED EXCEPTION;
756 CHECK_ALL_DATES_FAILURE EXCEPTION;
757 INVALID_VERSION EXCEPTION;
758 VALUE_REQUIRED EXCEPTION;
759 INVALID_CAL_RANGE EXCEPTION;
760
761 X_field varchar2(2000) := '';
765 BEGIN
762 X_value varchar2(2000) := '';
763 X_msg varchar2(2000) := '';
764
766
767 /* Set the return status to success initially */
768 x_return_status := FND_API.G_RET_STS_SUCCESS;
769
770 /* Initialize message list and count if needed */
771 IF p_init_msg_list THEN
772 fnd_msg_pub.initialize;
773 END IF;
774
775 /* Make sure we are call compatible */
776 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
777 ,p_api_version
778 ,'GET_ALL_DATES'
779 ,GMP_CALENDAR_API.m_pkg_name) THEN
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 RAISE INVALID_VERSION;
782 END IF;
783 --
784 IF p_calendar_code is NOT NULL
785 THEN
786 IF ((p_start_date IS NULL) OR (p_end_date IS NULL))
787 THEN
788 x_return_status := 'E';
789 X_field := 'Start/End Date';
790 X_value := p_start_date||'-'||p_end_date ;
791 RAISE VALUE_REQUIRED;
792 END IF;
793 --
794 IF p_end_date < p_start_date
795 THEN
796 x_return_status := 'E';
797 RAISE INVALID_CAL_RANGE;
798 END IF;
799 --
800 IF l_return_status = 'E'
801 THEN
802 RAISE check_all_dates_failure;
803 ELSE
804 FOR c_rec in get_all_dates (p_calendar_code, p_start_date, p_end_date)
805 LOOP
806 i := i + 1;
807 p_output_tbl(i). cal_date := c_rec.calendar_date ;
808 p_output_tbl(i). is_workday:= c_rec.l_work_day;
809 END LOOP;
810 END IF;
811 ELSE
812 x_return_status := 'E';
813 X_msg := 'Calendar';
814 RAISE CALENDAR_REQUIRED;
815 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
816 FND_MSG_PUB.ADD;
817 END IF;
818
819 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
820
821 EXCEPTION
822 WHEN check_all_dates_failure OR invalid_version THEN
823 x_return_status := FND_API.G_RET_STS_ERROR;
824
825 WHEN CALENDAR_REQUIRED OR VALUE_REQUIRED THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
828 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
829 FND_MSG_PUB.ADD;
830
831 WHEN INVALID_CAL_RANGE THEN
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 FND_MESSAGE.SET_NAME('GMP','MR_INV_CALENDAR_RANGE');
834 FND_MSG_PUB.ADD;
835
836 WHEN OTHERS THEN
837 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
838 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
839 FND_MSG_PUB.ADD;
840 x_return_status := FND_API.g_ret_sts_unexp_error;
841
842 END get_all_dates ;
843
844 /*
845 ==========================================================================
846 Procedure:
847 get_work_days
848
849 DESCRIPTION:
850
851 The following Procedure gets the workdays between a specified Start and
852 End dates in a Calendar
853
854 History :
855 Sgidugu 08/21/2003 Initial implementation
856 ==========================================================================
857 */
858
859 PROCEDURE get_work_days(
860 p_api_version IN NUMBER,
861 p_init_msg_list IN BOOLEAN := TRUE,
862 p_calendar_code IN VARCHAR2,
863 p_start_date IN DATE,
864 p_end_date IN DATE,
865 p_output_tbl OUT NOCOPY workdays_tbl,
866 x_return_status IN OUT NOCOPY VARCHAR2
867 ) IS
868 CURSOR get_cal_dates (c_calendar_code VARCHAR2,
869 c_start_date DATE ,
870 c_end_date DATE ) IS
871 SELECT sd.shift_date calendar_date,
872 SUM((st.to_time - st.from_time)/3600) duration
873 FROM bom_calendars cal,
874 bom_shift_dates sd,
875 bom_shift_times st
876 WHERE cal.calendar_code = c_calendar_code
877 AND sd.calendar_code = cal.calendar_code
878 AND st.calendar_code = sd.calendar_code
879 AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
880 AND sd.shift_num = st.shift_num
881 AND sd.seq_num IS NOT NULL
882 GROUP BY sd.shift_date
883 HAVING SUM((st.to_time - st.from_time)/3600) > 0 ;
884
885 i INTEGER := 0 ;
886 /* Local variable section */
887
888 l_api_name CONSTANT VARCHAR2(30) := 'GET_WORK_DAYS';
889 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
890
891 /* Define Exceptions */
892 CALENDAR_REQUIRED EXCEPTION;
893 work_days_failure EXCEPTION;
894 INVALID_VERSION EXCEPTION;
895 X_msg varchar2(2000) := '';
896
897 BEGIN
898
899 /* Set the return status to success initially */
900 x_return_status := FND_API.G_RET_STS_SUCCESS;
904 fnd_msg_pub.initialize;
901
902 /* Initialize message list and count if needed */
903 IF p_init_msg_list THEN
905 END IF;
906
907 /* Make sure we are call compatible */
908 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
909 ,p_api_version
910 ,'GET_WORK_DAYS'
911 ,GMP_CALENDAR_API.m_pkg_name) THEN
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 RAISE INVALID_VERSION;
914 END IF;
915
916 IF p_calendar_code is NOT NULL
917 THEN
918 check_all_dates (
919 p_calendar_code,
920 p_start_date,
921 p_end_date,
922 l_return_status
923 );
924 --
925 IF l_return_status = 'E'
926 THEN
927 RAISE work_days_failure;
928 ELSE
929 FOR c_rec in get_cal_dates (p_calendar_code, p_start_date, p_end_date)
930 LOOP
931 i := i + 1;
932 p_output_tbl(i).workday := c_rec.calendar_date ;
933 END LOOP ;
934 END IF;
935 ELSE
936 x_return_status := 'E';
937 X_msg := 'Calendar';
938 RAISE CALENDAR_REQUIRED;
939 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
940 FND_MSG_PUB.ADD;
941 END IF;
942
943 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
944
945 EXCEPTION
946 WHEN work_days_failure OR invalid_version THEN
947 x_return_status := FND_API.G_RET_STS_ERROR;
948
949 WHEN CALENDAR_REQUIRED THEN
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
952 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
953 FND_MSG_PUB.ADD;
954
955 WHEN OTHERS THEN
956 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
957 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
958 FND_MSG_PUB.ADD;
959 x_return_status := FND_API.g_ret_sts_unexp_error;
960
961 END get_work_days ;
962
963 /* ==========================================================================
964 Procedure:
965 get_workday_details
966
967 DESCRIPTION:
968
969 The following Procedure gets the Workday Details for a given Shopday
970
971 History :
972 Sgidugu 08/21/2003 Initial implementation
973 ========================================================================== */
974
975 PROCEDURE get_workday_details(
976 p_api_version IN NUMBER,
977 p_init_msg_list IN BOOLEAN := TRUE,
978 p_calendar_code IN VARCHAR2,
979 p_shopday_no IN NUMBER,
980 p_output_tbl OUT NOCOPY shopday_dtl_tbl,
981 x_return_status IN OUT NOCOPY VARCHAR2
982 ) IS
983
984 CURSOR shopday_dtls_cur (c_calendar_code VARCHAR2,
985 c_shopday_no NUMBER) IS
986 SELECT shift_num,from_time,to_time
987 FROM bom_shift_times
988 WHERE calendar_code = c_calendar_code
989 AND shift_num = c_shopday_no
990 ORDER BY from_time ;
991
992 CURSOR Cur_shop_day (c_calendar_code VARCHAR2,
993 c_shopday_no NUMBER) IS
994 SELECT COUNT(*)
995 FROM bom_shift_times
996 WHERE calendar_code = c_calendar_code
997 AND shift_num = c_shopday_no;
998
999 i INTEGER := 0 ;
1000
1001 /* Local variable section */
1002
1003 l_api_name CONSTANT VARCHAR2(30) := 'GET_WORKDAY_DETAILS';
1004 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1005
1006 /* Define Exceptions */
1010 INVALID_SHOPDAY EXCEPTION;
1007 SHOPDAY_NUMBER_REQUIRED EXCEPTION;
1008 WORKDAY_DTLS_FAILURE EXCEPTION;
1009 INVALID_VERSION EXCEPTION;
1011 X_msg varchar2(2000) := '';
1012 l_count number := 0 ;
1013
1014 BEGIN
1015
1016 /* Set the return status to success initially */
1017 x_return_status := FND_API.G_RET_STS_SUCCESS;
1018
1019 /* Initialize message list and count if needed */
1020 IF p_init_msg_list THEN
1021 fnd_msg_pub.initialize;
1022 END IF;
1023
1024 /* Make sure we are call compatible */
1025 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1026 ,p_api_version
1027 ,'GET_WORKDAY_DETAILS'
1028 ,GMP_CALENDAR_API.m_pkg_name) THEN
1029 x_return_status := FND_API.G_RET_STS_ERROR;
1030 RAISE INVALID_VERSION;
1031 END IF;
1032 --
1033 OPEN Cur_shop_day(p_calendar_code , p_shopday_no);
1034 FETCH Cur_shop_day INTO l_count;
1035 CLOSE Cur_shop_day;
1036
1037 IF l_count = 0
1038 THEN
1039 x_return_status := 'E';
1040 RAISE INVALID_SHOPDAY;
1041 END IF;
1042 --
1043 IF p_shopday_no is NOT NULL
1044 THEN
1045 FOR c_rec in shopday_dtls_cur (p_calendar_code , p_shopday_no) /*Parameter added - calendar id*/
1046 LOOP
1047 i := i + 1;
1048 p_output_tbl(i).shift_no := c_rec.shift_num ;
1049 p_output_tbl(i).shift_start := c_rec.from_time ;
1050 p_output_tbl(i).shift_duration := c_rec.to_time ;
1051 END LOOP ;
1052 ELSE
1053 x_return_status := 'E';
1054 X_msg := 'Shopday Number';
1055 RAISE SHOPDAY_NUMBER_REQUIRED;
1056 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1057 FND_MSG_PUB.ADD;
1058 END IF;
1059
1060 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1061
1062 EXCEPTION
1063 WHEN INVALID_VERSION THEN
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065
1066 WHEN SHOPDAY_NUMBER_REQUIRED THEN
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1069 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1070 FND_MSG_PUB.ADD;
1071
1072 WHEN INVALID_SHOPDAY THEN
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_SHOPDAY');
1075 FND_MSG_PUB.ADD;
1076
1077 WHEN OTHERS THEN
1078 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1079 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1080 FND_MSG_PUB.ADD;
1081 x_return_status := FND_API.g_ret_sts_unexp_error;
1082
1083 END get_workday_details ;
1084
1085 /* ==========================================================================
1086 Procedure:
1087 check_cal_data
1088
1089 DESCRIPTION:
1090
1091 The following Procedure checks the data passed and Returns S code If
1092 Successful
1093
1094 History :
1095 Sgidugu 08/21/2003 Initial implementation
1096 ========================================================================== */
1097 PROCEDURE check_cal_data(
1098 p_calendar_code IN VARCHAR2,
1099 p_date IN DATE,
1100 x_return_status OUT NOCOPY VARCHAR2) IS
1101
1102 CURSOR Cur_cal_check IS
1103 SELECT COUNT(1)
1104 FROM bom_Calendars
1105 WHERE calendar_code = p_calendar_code;
1106
1107 CURSOR Cur_cal_date IS
1108 SELECT calendar_start_date ,
1109 calendar_end_date
1110 FROM bom_Calendars
1111 WHERE calendar_code = p_calendar_code;
1112
1113 GMP_SDATE_BEFORE_CAL_SDATE EXCEPTION;
1114 GMP_EDATE_AFTER_CAL_EDATE EXCEPTION;
1118 v_max_date date;
1115 PS_INVALID_CALENDAR EXCEPTION;
1116
1117 v_min_date date;
1119 X_field varchar2(2000) := '';
1120 X_value varchar2(2000) := '';
1121 X_msg varchar2(2000) := '';
1122 l_count number := 0;
1123
1124
1125 begin
1126 x_return_status := 'S';
1127
1128 --
1129 OPEN Cur_cal_date;
1130 FETCH Cur_cal_date into v_min_date,v_max_date;
1131 CLOSE Cur_cal_date;
1132 --
1133 if nvl(p_date,sysdate) < v_min_date
1134 then
1135 x_return_status := 'E';
1136 RAISE GMP_SDATE_BEFORE_CAL_SDATE;
1137 end if;
1138 --
1139 if nvl(p_date,sysdate) > v_max_date
1140 then
1141 x_return_status := 'E';
1142 RAISE GMP_EDATE_AFTER_CAL_EDATE;
1143 end if;
1144 --
1145 OPEN Cur_cal_check;
1146 FETCH Cur_cal_check into l_count;
1147 CLOSE Cur_cal_check;
1148 --
1149 IF l_count = 0
1150 THEN
1151 RAISE PS_INVALID_CALENDAR;
1152 END IF;
1153
1154
1155 EXCEPTION
1156 WHEN GMP_SDATE_BEFORE_CAL_SDATE THEN
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 FND_MESSAGE.SET_NAME('GMP','GMP_SDATE_BEFORE_CAL_SDATE');
1159 FND_MSG_PUB.ADD;
1160
1161 WHEN PS_INVALID_CALENDAR THEN
1162 x_return_status := FND_API.G_RET_STS_ERROR;
1163 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
1164 FND_MSG_PUB.ADD;
1165
1166 WHEN GMP_EDATE_AFTER_CAL_EDATE THEN
1167 x_return_status := FND_API.G_RET_STS_ERROR;
1168 FND_MESSAGE.SET_NAME('GMP','GMP_EDATE_AFTER_CAL_EDATE');
1169 FND_MSG_PUB.ADD;
1170
1171 WHEN OTHERS THEN
1172 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1173 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1174 FND_MSG_PUB.ADD;
1175 x_return_status := FND_API.g_ret_sts_unexp_error;
1176 end check_cal_data;
1177 /* ==========================================================================
1178 Procedure:
1179 check_all_dates
1180
1181 DESCRIPTION:
1182
1183 The following Procedure checks the data passed and Returns S code If
1184 Successful
1185
1186 History :
1187 Sgidugu 08/21/2003 Initial implementation
1188 ========================================================================== */
1189
1190 PROCEDURE check_all_dates(
1191 p_calendar_code IN VARCHAR2,
1192 p_start_date IN DATE,
1193 p_end_date IN DATE,
1194 x_return_status OUT NOCOPY VARCHAR2) IS
1195
1196 CURSOR Cur_cal_check (c_calendar_code VARCHAR2 ) IS
1197 SELECT COUNT(1)
1198 FROM bom_Calendars
1199 WHERE calendar_code = c_calendar_code;
1200
1201 INVALID_DATE_RANGE EXCEPTION;
1202 CALENDAR_NULL EXCEPTION;
1203 INVALID_VALUE EXCEPTION;
1204 VALUE_REQUIRED EXCEPTION;
1205 INVALID_CAL_RANGE EXCEPTION;
1206 PS_INVALID_CALENDAR EXCEPTION;
1207
1208 X_field varchar2(2000) := '';
1209 X_value varchar2(2000) := '';
1210 X_msg varchar2(2000) := '';
1211 l_count number := 0;
1212
1213
1214 begin
1215 x_return_status := 'S';
1216 --
1217 if ((p_start_date IS NULL) OR (p_end_date IS NULL))
1218 then
1219 x_return_status := 'E';
1220 X_field := 'Start/End Date';
1221 X_value := p_start_date||'-'||p_end_date ;
1222 RAISE VALUE_REQUIRED;
1223 end if;
1224 --
1225 OPEN Cur_cal_check ( p_calendar_code );
1226 FETCH Cur_cal_check into l_count;
1227 CLOSE Cur_cal_check;
1228 --
1229 IF l_count = 0
1230 THEN
1231 RAISE PS_INVALID_CALENDAR;
1232 END IF;
1233
1234 --
1235 /* The following lines were commented as per Eddie's recommendation
1236
1237 if ((nvl(p_start_date,sysdate) < v_min_date) OR
1238 (nvl(p_start_date,sysdate) > v_max_date))
1239 then
1240 x_return_status := 'E';
1241 RAISE INVALID_DATE_RANGE;
1242 end If;
1243
1244 if ((nvl(p_end_date,sysdate) < v_min_date) OR
1245 (nvl(p_end_date,sysdate) > v_max_date))
1246 then
1247 x_return_status := 'E';
1248 RAISE INVALID_DATE_RANGE;
1249 end If;
1250 */
1251 --
1252 if p_end_date < p_start_date
1253 then
1254 x_return_status := 'E';
1258 EXCEPTION
1255 RAISE INVALID_CAL_RANGE;
1256 end If;
1257
1259 WHEN VALUE_REQUIRED THEN
1260 x_return_status := FND_API.G_RET_STS_ERROR;
1261 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1262 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1263 FND_MSG_PUB.ADD;
1264 --
1265 WHEN INVALID_CAL_RANGE THEN
1266 x_return_status := FND_API.G_RET_STS_ERROR;
1267 FND_MESSAGE.SET_NAME('GMP','MR_INV_CALENDAR_RANGE');
1268 FND_MSG_PUB.ADD;
1269 --
1270 WHEN PS_INVALID_CALENDAR THEN
1271 x_return_status := FND_API.G_RET_STS_ERROR;
1272 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
1273 FND_MSG_PUB.ADD;
1274 --
1275 WHEN OTHERS THEN
1276 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1277 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1278 FND_MSG_PUB.ADD;
1279 x_return_status := FND_API.g_ret_sts_unexp_error;
1280
1281 end check_all_dates;
1282
1283 /*
1284 |==========================================================================
1285 | Procedure: |
1286 | is_working_daytime |
1287 | |
1288 | DESCRIPTION: |
1289 | |
1290 | API returns if the date time passed for a calendar is a work day or a |
1291 | Non Work day |
1292 | The API takes Calendar_id, Date and Time and Indicator as Inputs |
1293 | and returns if the day is a work day or a Non-work day, The |
1294 | Indicator takes values 0 or 1 0 means Start and 1 means End |
1295 | |
1296 | History : |
1297 | Sridhar 19-SEP-2003 Initial implementation |
1298 | B4610901, Rajesh Patangya 15-Sep-2005 |
1299 ==========================================================================
1300 */
1301
1302 FUNCTION IS_WORKING_DAYTIME(
1303 p_api_version IN NUMBER,
1304 p_init_msg_list IN BOOLEAN := TRUE,
1305 p_calendar_code IN VARCHAR2,
1306 p_date IN DATE,
1307 p_ind IN NUMBER,
1308 x_return_status IN OUT NOCOPY VARCHAR2
1309 ) RETURN BOOLEAN
1310 IS
1311 /* p_ind 0 means start and 1 means end */
1312
1313 /* Local variable section */
1314 l_api_name CONSTANT VARCHAR2(30) := 'IS_WORKING_DAYTIME';
1315 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1316
1317 x_date date;
1318
1319 CURSOR get_datetime_cur (c_calendar_code VARCHAR2, c_cal_date DATE) IS
1320 SELECT 1
1321 FROM bom_calendars cal,
1322 bom_shift_dates sd,
1323 bom_shift_times st
1324 WHERE cal.calendar_code = c_calendar_code
1325 AND sd.calendar_code = cal.calendar_code
1326 AND st.calendar_code = sd.calendar_code
1327 AND sd.shift_num = st.shift_num
1328 -- B4610901, Rajesh Patangya 15-Sep-2005
1329 AND (sd.shift_date + (st.from_time/86400)) <= c_cal_date
1330 AND DECODE(
1331 SIGN(st.from_time - st.to_time),
1332 1,(sd.shift_date+1), sd.shift_date
1333 ) + (st.to_time/86400) >= c_cal_date
1334 AND sd.seq_num IS NOT NULL;
1335
1336 /* Define Exceptions */
1337 CALENDAR_REQUIRED EXCEPTION;
1338 INVALID_DATA_PASSED EXCEPTION;
1339 INVALID_VALUE EXCEPTION;
1340 INVALID_VERSION EXCEPTION;
1341
1342 l_count NUMBER := 0 ;
1343 X_msg varchar2(2000) := '';
1344 X_field varchar2(2000) := '';
1345 X_value varchar2(2000) := '';
1346
1347 BEGIN
1348
1349 /* Set the return status to success initially */
1350 x_return_status := FND_API.G_RET_STS_SUCCESS;
1351
1352 /* Initialize message list and count if needed */
1353 IF p_init_msg_list THEN
1354 fnd_msg_pub.initialize;
1355 END IF;
1356
1357 /* Make sure we are call compatible */
1358 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1359 ,p_api_version
1360 ,'IS_WORKING_DAYTIME'
1361 ,GMP_CALENDAR_API.m_pkg_name) THEN
1362 x_return_status := FND_API.G_RET_STS_ERROR;
1363 RAISE INVALID_VERSION;
1364 END IF;
1365
1366 /* Error Out if the Indicator passed not 0 or 1 */
1367 IF (p_ind not in (0,1))
1368 THEN
1369 X_field := 'Indicator ';
1370 X_value := p_ind;
1371 RAISE INVALID_VALUE;
1372
1373 END IF;
1374 IF ((p_calendar_code is NOT NULL) AND (p_date is NOT NULL ))
1375 THEN
1376 check_cal_data(
1377 p_calendar_code,
1378 p_date,
1379 l_return_status
1380 );
1381
1382 IF l_return_status = 'E'
1383 THEN
1384 RAISE INVALID_DATA_PASSED;
1385 ELSE
1386 IF p_ind = 0 THEN
1390 END IF ;
1387 x_date := p_date + 1/86400 ;
1388 ELSIF p_ind = 1 THEN
1389 x_date := p_date - 1/86400 ;
1391
1392 OPEN get_datetime_cur (p_calendar_code , x_date) ;
1393 FETCH get_datetime_cur INTO l_count ;
1394 CLOSE get_datetime_cur ;
1395
1396 IF l_count = 1 THEN
1397 RETURN TRUE ;
1398 ELSE
1399 RETURN FALSE ;
1400 END IF ;
1401 END IF;
1402 ELSE
1403 x_return_status := 'E';
1404 X_msg := 'Calendar/Date ';
1405 RAISE CALENDAR_REQUIRED;
1406 END IF;
1407
1408 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1409
1410 EXCEPTION
1411 WHEN INVALID_DATA_PASSED OR invalid_version THEN
1412
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1415 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1416 FND_MSG_PUB.ADD;
1417 RETURN FALSE ;
1418
1419 WHEN CALENDAR_REQUIRED THEN
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1422 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1423 FND_MSG_PUB.ADD;
1424 RETURN FALSE ;
1425
1426 WHEN INVALID_VALUE THEN
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
1429 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
1430 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
1431 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
1432 FND_MSG_PUB.ADD;
1433 RETURN FALSE ;
1434
1435 WHEN OTHERS THEN
1436 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1437 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1438 FND_MSG_PUB.ADD;
1439 x_return_status := FND_API.g_ret_sts_unexp_error;
1440 RETURN FALSE ;
1441 END IS_WORKING_DAYTIME ;
1442
1443 -- Bug: 6265867 Kbanddyo added this procedure
1444 /*
1445 |==========================================================================
1446 | Procedure: |
1447 | get_nearest_workdaytime |
1448 | |
1449 | DESCRIPTION: |
1450 | |
1451 | The purpose of the API is to return the working date-time that is |
1452 | closest to the date-time passed in as parameter |
1453 | When the date-time passed in is NOT work time the API either searches |
1454 | backwards to locate the end of previous workday OR searches forward |
1455 | to locate the start of next workday - this direction of search is |
1456 | controlled by parameter pDirection |
1457 | |
1458 | PARAMETERS |
1459 | p_direction - 0 means backwards and 1 means forward |
1460 | History : |
1461 | Abhay 24-Jul-2006 Initial implementation |
1462 | B5378109 Teva |
1463 ==========================================================================
1464 */
1465
1466 PROCEDURE get_nearest_workdaytime(
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN BOOLEAN := TRUE,
1469 p_calendar_id IN VARCHAR2,
1470 p_date IN DATE,
1471 p_direction IN NUMBER,
1472 x_date IN OUT NOCOPY DATE ,
1473 x_return_status IN OUT NOCOPY VARCHAR2
1474 )
1475 IS
1476
1477 CALENDAR_REQUIRED EXCEPTION;
1478 INVALID_DATA_PASSED EXCEPTION;
1479 INVALID_VALUE EXCEPTION;
1480 INVALID_VERSION EXCEPTION;
1481 l_count NUMBER ;
1482 X_msg VARCHAR2(2000) ;
1483 X_field VARCHAR2(2000) ;
1484 X_value VARCHAR2(2000) ;
1485 l_api_name CONSTANT VARCHAR2(30) := 'GET_NEAREST_WORKDAYTIME';
1486 l_return_status VARCHAR2(1) ;
1487 l_date DATE;
1488
1489 CURSOR Is_WorkDayTime (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1490 SELECT 1
1491 FROM sys.dual
1492 WHERE EXISTS (
1493 select 'x'
1494 FROM bom_calendars bd,
1495 bom_shift_dates sd,
1496 bom_shift_times st
1497 WHERE bd.calendar_code = p_calendar_id
1498 AND sd.calendar_code = bd.calendar_code
1499 AND sd.calendar_code= st.calendar_code
1500 AND sd.shift_num = st.shift_num
1501 AND (sd.shift_date + (st.from_time/86400)) <= p_date
1502 AND sd.seq_num IS NOT NULL
1503 AND ((decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,
1504 (sd.shift_date+1),sd.shift_date) ) +
1505 (decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,
1506 (st.from_time + (st.to_time- st.from_time) - 86400),
1507 (st.from_time + (st.to_time- st.from_time)))/86400 ) ) >= p_date);
1508
1512 FROM bom_calendars bd,
1509
1510 CURSOR get_NextDatetime_cur (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1511 SELECT min (sd.shift_date + (st.from_time/86400))
1513 bom_shift_dates sd,
1514 bom_shift_times st
1515 WHERE bd.calendar_code = p_calendar_id
1516 AND sd.calendar_code = bd.calendar_code
1517 AND sd.calendar_code= st.calendar_code
1518 AND sd.shift_num = st.shift_num
1519 AND sd.seq_num IS NOT NULL
1520 AND (st.to_time- st.from_time) > 0
1521 AND (sd.shift_date + (st.from_time/86400)) > p_cal_date ;
1522
1523
1524 CURSOR get_PrevDatetime_cur (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1525 SELECT max (
1526 ((decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,
1527 (sd.shift_date+1),sd.shift_date) ) +
1528 (decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,(st.from_time
1529 + (st.to_time- st.from_time) - 86400),(st.from_time + (st.to_time- st.from_time)))/86400 ) )
1530 )
1531 FROM bom_calendars bd,
1532 bom_shift_dates sd,
1533 bom_shift_times st
1534 WHERE bd.calendar_code = p_calendar_id
1535 AND sd.calendar_code = bd.calendar_code
1536 AND sd.calendar_code= st.calendar_code
1537 AND sd.shift_num = st.shift_num
1538 AND sd.seq_num IS NOT NULL
1539 AND (st.to_time- st.from_time) > 0
1540 AND ((decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,
1541 (sd.shift_date+1),sd.shift_date) ) +
1542 (decode(sign(st.from_time + (st.to_time- st.from_time) - 86400),1,(st.from_time
1543 + (st.to_time- st.from_time) - 86400),(st.from_time + (st.to_time- st.from_time)))/86400 ))
1544 < p_date;
1545
1546 BEGIN
1547
1548 l_count := 0 ;
1549 X_msg := '';
1550 X_field := '';
1551 X_value := '';
1552 l_date := NULL;
1553 l_return_status := FND_API.G_RET_STS_SUCCESS;
1554 x_return_status := FND_API.G_RET_STS_SUCCESS;
1555
1556 /* Initialize message list and count if needed */
1557 IF p_init_msg_list THEN
1558 fnd_msg_pub.initialize;
1559 END IF;
1560
1561 /* Make sure we are call compatible */
1562 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1563 ,p_api_version
1564 ,'GET_NEAREST_WORKDAYTIME'
1565 ,GMP_CALENDAR_API.m_pkg_name) THEN
1566 FND_FILE.PUT_LINE ( FND_FILE.LOG,'if not FND_API.compatible_api_call');
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 x_date := (sysdate - 9999 ) ;
1569 RAISE INVALID_VERSION;
1570 END IF;
1571
1572 /* Error Out if the Indicator passed not 0 or 1 */
1573
1574
1575 IF (p_direction not in (0,1))
1576 THEN
1577 X_field := 'Direction ';
1578 X_value := p_direction;
1579
1580 x_return_status := FND_API.G_RET_STS_ERROR;
1581 x_date := (sysdate - 9999 ) ;
1582 RAISE INVALID_VALUE;
1583
1584 END IF;
1585
1586 IF ((p_calendar_id is NOT NULL) AND (p_date is NOT NULL )) THEN
1587 check_cal_data(
1588 p_calendar_id,
1589 p_date,
1590 l_return_status
1591 );
1592
1593
1594
1595
1596 FND_FILE.PUT_LINE ( FND_FILE.LOG,p_calendar_id);
1597 FND_FILE.PUT_LINE ( FND_FILE.LOG,to_char(p_date,'dd/mm/yyyy hh24:mi:ss'));
1598
1599 IF l_return_status = 'E' THEN
1600
1601 FND_FILE.PUT_LINE ( FND_FILE.LOG,'IF l_return_status = E');
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 x_date := (sysdate - 9999 ) ;
1604 RAISE INVALID_DATA_PASSED;
1605 ELSE
1606 OPEN Is_WorkDayTime(p_calendar_id , p_date) ;
1607 FETCH Is_WorkdayTime INTO l_count ;
1608 CLOSE Is_WorkdayTime ;
1609
1610 IF l_count = 1 THEN
1611 x_return_status := FND_API.G_RET_STS_SUCCESS;
1612 x_date := p_date ;
1613 ELSE
1614 IF p_direction = 1 THEN
1615 OPEN get_NextDatetime_cur(p_calendar_id , p_date) ;
1616 FETCH get_NextDatetime_cur INTO l_date ;
1617 CLOSE get_NextDatetime_cur ;
1618
1619 ELSE
1620 OPEN get_PrevDatetime_cur(p_calendar_id , p_date) ;
1621 FETCH get_PrevDatetime_cur INTO l_date ;
1622 CLOSE get_PrevDatetime_cur ;
1623 END IF ;
1624 l_return_status := FND_API.G_RET_STS_SUCCESS;
1625 x_return_status := FND_API.G_RET_STS_SUCCESS;
1626 X_date := l_date ;
1627
1628 END IF ; /* IF l_count = 1 */
1629 END IF; /* IF l_return_status = 'E' */
1630 ELSE
1631
1632
1633 FND_FILE.PUT_LINE ( FND_FILE.LOG,'last else');
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635 x_date := (sysdate - 9999 ) ;
1636 X_msg := 'Calendar/Date ';
1637 RAISE CALENDAR_REQUIRED;
1638 END IF;
1639 EXCEPTION
1640 WHEN INVALID_DATA_PASSED OR invalid_version THEN
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 x_date := (sysdate - 9999 ) ;
1643 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1644 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1645 FND_MSG_PUB.ADD;
1646
1647 WHEN CALENDAR_REQUIRED THEN
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1650 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1651 FND_MSG_PUB.ADD;
1652 x_date := (sysdate - 9999 ) ;
1653
1654 WHEN INVALID_VALUE THEN
1655 x_return_status := FND_API.G_RET_STS_ERROR;
1656 x_date := (sysdate - 9999 ) ;
1657 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
1658 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
1659 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
1660 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
1661 FND_MSG_PUB.ADD;
1662
1663 WHEN OTHERS THEN
1664 x_return_status := FND_API.g_ret_sts_unexp_error;
1665 x_date := (sysdate - 9999 ) ;
1666 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1667 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1668 FND_MSG_PUB.ADD;
1669
1670 END get_nearest_workdaytime ;
1671
1672 END gmp_calendar_api ;