[Home] [Help]
PACKAGE BODY: APPS.GMP_CALENDAR_API
Source
1 PACKAGE BODY gmp_calendar_api AS
2 /* $Header: GMPCAPIB.pls 120.6.12020000.2 2012/07/24 15:08:05 vkinduri 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 DECODE(sht.to_time,0,86400,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 REM+==========================================================================+
201 REM| PROCEDURE NAME |
202 REM| get_contiguous_periods |
203 REM| |
204 REM| Type |
205 REM| public |
206 REM| |
207 REM| Input Parameters |
208 REM| p_start_date - Either Pass Start Date |
209 REM| p_end_date - OR End Date |
210 REM| p_calendar_code - Fopr a calender |
211 REM| return_status - Status return variable |
212 REM| |
213 REM| Output Parameters |
214 REM| p_output_tbl - Output PL/sql Table of Start Date, End Date and Duration |
215 REM| |
216 REM| DESCRIPTION |
217 REM|The API calculates contiguous periods for a given Calendar Code, Start or |
218 REM|End Date and a duration. If Start Date is given the duration is calculated|
219 REM|from the Start date and the calendar dates and durations are returned, If |
220 REM|end date is given, the duration is calculated from the end date backwards |
221 REM|and the calendar dates and the durations are returned in a Output PL/sql |
222 REM|table |
223 REM| |
224 REM|History : |
225 REM|Abhay 08/21/2003 Initial implementation |
226 REM|Sridhar 10/08/2003 Added CEIL function for Date Differences |
227 REM| B3167015 |
228 REM|Sridhar 03/24/2004 CEIL Function is used wherever remaining |
229 REM| duration assignment is used |
230 REM|Rajesh Patangya 26-OCT-2011 B13030900 |
231 REM+==========================================================================+
232 */
233
234 PROCEDURE get_contiguous_periods(
235 p_api_version IN NUMBER,
236 p_init_msg_list IN BOOLEAN := TRUE,
237 p_start_date IN DATE,
238 p_end_date IN DATE,
239 p_calendar_code IN VARCHAR2,
240 p_duration IN NUMBER,
241 p_output_tbl OUT NOCOPY contig_period_tbl,
242 x_return_status IN OUT NOCOPY VARCHAR2
243 )IS
244
245
246 /* -- For Testing Purpose
247 CURSOR start_date_cur (c_calendar_code VARCHAR2,
248 c_start_date DATE) IS
249 SELECT shift_date,shift_num, from_time,
250 (to_time-from_time) diff_shift,to_time, from_date ,
251 TO_DATE end_date
252 FROM apps.temp_cal
253 -- WHERE shift_DATE >= TO_DATE('06-OCT-2011 00:00:00','DD-MON-YYYY HH24:MI:SS')
254 WHERE shift_DATE >= trunc(c_start_date)
255 -- AND TO_DATE >= TO_DATE('06-OCT-2011 23:00:00','DD-MON-YYYY HH24:MI:SS')
256 AND TO_DATE >= c_start_date
257 AND calendar_code = c_calendar_code -- '21HR'
258 ORDER BY shift_date, from_date, TO_DATE ;
259
260 CURSOR end_date_cur (c_calendar_code VARCHAR2,
261 c_end_date DATE) IS
262 SELECT shift_date,shift_num, from_time,
263 (to_time-from_time) diff_shift,to_time, from_date ,
264 TO_DATE end_date
265 FROM apps.temp_cal
266 WHERE shift_DATE <= trunc(c_end_date)
267 -- WHERE shift_DATE <= TO_DATE('06-OCT-2011 00:00:00','DD-MON-YYYY HH24:MI:SS')
268 -- AND FROM_DATE <= TO_DATE('06-OCT-2011 01:59:55','DD-MON-YYYY HH24:MI:SS')
269 AND FROM_DATE <= c_end_date
270 AND calendar_code = '21HR'
271 ORDER BY shift_date desc, TO_DATE desc, from_date ;
272 */
273
274 -- B13030900 Rajesh Patangya
275 CURSOR start_date_cur (c_calendar_code VARCHAR2,
276 c_start_date DATE) IS
277 SELECT shift_date, shift_num, from_time, to_time,
278 DECODE((to_time-from_time),0,86400,(to_time-from_time)) diff_shift, from_date, TO_DATE end_date
279 FROM gmp_calendar_detail_gtmp
280 WHERE shift_DATE >= trunc(c_start_date)
281 AND TO_DATE >= c_start_date
282 AND calendar_code = c_calendar_code
283 ORDER BY shift_date, from_date, TO_DATE ;
284
285 CURSOR end_date_cur (c_calendar_code VARCHAR2,
286 c_end_date DATE) IS
287 SELECT shift_date, shift_num, from_time, to_time,
288 DECODE((to_time-from_time),0,86400,(to_time-from_time)) diff_shift, from_date, TO_DATE end_date
289 FROM gmp_calendar_detail_gtmp
290 WHERE shift_DATE <= trunc(c_end_date)
291 AND FROM_DATE <= c_end_date
292 AND calendar_code = c_calendar_code
293 ORDER BY shift_date desc, TO_DATE desc, from_date ;
294
295 o_cnt INTEGER := 0 ;
296 i INTEGER := 1 ;
297 remaining_duration NUMBER := 0;
298 previous_start_date DATE ;
299 current_start_date DATE ;
300 current_end_date DATE ;
301 previous_end_date DATE ;
302 contig_start_date DATE ;
303 contig_end_date DATE ;
304 contig_duration NUMBER := 0 ;
305
306 /* Local variable section */
307
308 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTIGUOUS_PERIODS';
309 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
310
311 /* Define Exceptions */
312 CALENDAR_REQUIRED EXCEPTION;
313 CONTIG_PERIODS_FAILURE EXCEPTION;
314 INVALID_VERSION EXCEPTION;
315 ZERO_DURATION EXCEPTION;
316 X_msg varchar2(2000) := '';
317 l_date DATE;
318 l_start_time NUMBER ;
319 l_end_time NUMBER ;
320 c_return_status BOOLEAN ;
321
322 BEGIN
323 /*
324 log_message('GET_CONTIGUOUS_PERIODS STARTED ');
325 log_message('p_start_date: '||to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS') );
326 log_message('p_end_date: '||to_char(p_end_date,'DD-MON-YYYY HH24:MI:SS') );
327 log_message('p_calendar_code: '||p_calendar_code);
328 log_message('p_duration: '||p_duration);
329 */
330 /* Set the return status to success initially */
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 /* Initialize message list and count if needed */
334 IF p_init_msg_list THEN
335 fnd_msg_pub.initialize;
336 END IF;
337
338 /* Make sure we are call compatible */
339 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
340 ,p_api_version
341 ,'GET_CONTIGUOUS_PERIODS'
342 ,GMP_CALENDAR_API.m_pkg_name) THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 RAISE INVALID_VERSION;
345 END IF;
346
347 /* Handling the 0 duration case right before the Looping starts */
348 IF (p_duration = 0) THEN
349
350 IF (p_start_date is NOT NULL) THEN
351 p_output_tbl(1).start_date := p_start_date ;
352 p_output_tbl(1).end_date := p_start_date;
353 ELSIF(p_end_date is NOT NULL) THEN
354 p_output_tbl(1).start_date := p_end_date ;
355 p_output_tbl(1).end_date := p_end_date;
356 END IF;
357
358 p_output_tbl(1).duration := 0;
359 RAISE ZERO_DURATION;
360 END IF;
361
362
363 IF (p_calendar_code is NOT NULL) AND ((p_start_date is NOT NULL) OR
364 (p_end_date is NOT NULL)) AND (p_duration is NOT NULL ) THEN
365 check_contig_periods(
366 p_calendar_code,
367 p_start_date,
368 p_end_date,
369 p_duration,
370 l_return_status
371 );
372 IF l_return_status = 'E' THEN
373 RAISE CONTIG_PERIODS_FAILURE;
374 ELSE
375 -- Cleanup the shifts and generate calender
376 retrieve_calendar_detail(p_calendar_code,
377 c_return_status
378 );
379
380 IF c_return_status = FALSE then
381 x_return_status := 'E';
382 X_msg := 'Calendar/Start or End Date ';
383 RAISE CALENDAR_REQUIRED;
384 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
385 FND_MSG_PUB.ADD;
386 END IF ;
387 END IF;
388
389 ELSE
390 x_return_status := 'E';
391 X_msg := 'Calendar/Start or End Date ';
392 RAISE CALENDAR_REQUIRED;
393 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
394 FND_MSG_PUB.ADD;
395 END IF;
396
397 remaining_duration := CEIL(p_duration * 3600) ; /* B3361082 */
398 -- log_message('Passed remaining_duration: '||remaining_duration);
399
400 IF p_start_date is NOT NULL THEN
401 /* { */
402 l_start_time := 0 ;
403 SELECT ( p_start_date - TRUNC(p_start_date) ) * 86400
404 INTO l_start_time FROM DUAL;
405
406 x_return_status := 'S';
407 FOR cur_rec in start_date_cur (p_calendar_code, p_start_date)
408 LOOP
409 /* log_message ('--> MAIN START DATE p_start_date: ' ||
410 to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS') ||
411 ' shift_date: ' ||
412 to_char(cur_rec.shift_date,'DD-MON-YYYY HH24:MI:SS') );
413
414 log_message(
415 ' cur_rec.from_date: ' ||
416 to_char(cur_rec.from_date,'DD-MON-YYYY HH24:MI:SS') ||
417 ' cur_rec.from_time: '|| cur_rec.from_time ||
418 ' cur_rec.end_date: ' ||
419 to_char(cur_rec.end_date,'DD-MON-YYYY HH24:MI:SS') ||
420 ' cur_rec.to_time: '||cur_rec.to_time ||
421 ' cur_rec.diff_shift : '||cur_rec.diff_shift);
422 */
423 IF (cur_rec.from_date < p_start_date)
424 AND (cur_rec.end_date < p_start_date) then
425 NULL;
426 ELSE
427 IF cur_rec.shift_date = trunc(p_start_date) THEN
428
429 IF (p_start_date < cur_rec.from_date) THEN
430 -- outside of the same day row hence keep it as it is
431 NULL;
432 ELSE
433 -- log_message(' SAME DAY ROW TREATMENT ' );
434
435 cur_rec.from_date := p_start_date ;
436 cur_rec.from_time := l_start_time ;
437 cur_rec.diff_shift := cur_rec.to_time - cur_rec.from_time ;
438
439 /* log_message(
440 ' BECOME cur_rec.from_date: ' ||
441 to_char(cur_rec.from_date,'DD-MON-YYYY HH24:MI:SS') ||
442 ' cur_rec.from_time: '|| cur_rec.from_time ||
443 ' cur_rec.end_date: ' ||
444 to_char(cur_rec.end_date,'DD-MON-YYYY HH24:MI:SS') ||
445 ' cur_rec.to_time: '||cur_rec.to_time ||
446 ' cur_rec.diff_shift : '||cur_rec.diff_shift);
447 */
448 END IF;
449
450 END IF;
451
452 IF remaining_duration > cur_rec.diff_shift THEN
453 o_cnt := o_cnt + 1 ;
454 p_output_tbl(o_cnt).start_date := cur_rec.from_date ;
455 p_output_tbl(o_cnt).end_date := cur_rec.end_date ;
456 p_output_tbl(o_cnt).duration := cur_rec.diff_shift ;
457 remaining_duration := remaining_duration - p_output_tbl(o_cnt).duration ;
458 /*
459 log_message(' 1 -> cur_rec.from_time in seconds: '||cur_rec.from_time ||
460 ' cur_rec.to_time in seconds: '||cur_rec.to_time ||
461 ' cur_rec.diff_shift : '||cur_rec.diff_shift ||
462 ' remaining_duration: ' || remaining_duration );
463
464 log_message(' 1 OUT -> p_output_tbl(o_cnt).start_date: ' ||
465 to_char(p_output_tbl(o_cnt).start_date,'DD-MON-YYYY HH24:MI:SS') ||
466 ' p_output_tbl(o_cnt).duration ' ||
467 p_output_tbl(o_cnt).duration ||
468 ' p_output_tbl(o_cnt).end_date ' ||
469 to_char(p_output_tbl(o_cnt).end_date,'DD-MON-YYYY HH24:MI:SS')
470 );
471 */
472 ELSE
473 o_cnt := o_cnt + 1 ;
474 p_output_tbl(o_cnt).start_date := cur_rec.from_date ;
475 p_output_tbl(o_cnt).duration := (remaining_duration - cur_rec.diff_shift) ;
476 p_output_tbl(o_cnt).end_date := cur_rec.end_date ;
477
478 If p_output_tbl(o_cnt).duration <= 0 THEN
479
480 p_output_tbl(o_cnt).end_date :=
481 cur_rec.from_date + (remaining_duration/86400) ;
482 p_output_tbl(o_cnt).duration := remaining_duration ;
483 remaining_duration := 0;
484 /*
485 log_message(' 2 -> cur_rec.from_time in seconds: '||cur_rec.from_time ||
486 ' cur_rec.to_time in seconds: '||cur_rec.to_time ||
487 ' cur_rec.diff_shift : '||cur_rec.diff_shift ||
488 ' remaining_duration: ' || remaining_duration );
489
490 log_message(' 2 OUT -> p_output_tbl(o_cnt).start_date: ' ||
491 to_char(p_output_tbl(o_cnt).start_date,'DD-MON-YYYY HH24:MI:SS') ||
492 ' p_output_tbl(o_cnt).duration ' ||
493 p_output_tbl(o_cnt).duration ||
494 ' p_output_tbl(o_cnt).end_date ' ||
495 to_char(p_output_tbl(o_cnt).end_date,'DD-MON-YYYY HH24:MI:SS')
496 );
497 */
498 EXIT;
499 END IF;
500
501 END IF;
502 END IF;
503 END LOOP ;
504
505 /* } */
506 -- ====***===***===***===***END DATE***===***===***===***===***===
507 /* { */
508 ELSIF p_end_date is NOT NULL THEN
509
510 l_end_time := 0 ;
511 SELECT ( p_end_date - TRUNC(p_end_date) ) * 86400
512 INTO l_end_time FROM DUAL;
513
514 x_return_status := 'S';
515 FOR cur_rec in end_date_cur (p_calendar_code, p_end_date)
516 LOOP
517 /*
518 log_message ('--> MAIN END DATE p_end_date: ' ||
519 to_char(p_end_date,'DD-MON-YYYY HH24:MI:SS') ||
520 ' shift_date: ' ||
521 to_char(cur_rec.shift_date,'DD-MON-YYYY HH24:MI:SS') );
522
523 log_message(
524 ' cur_rec.from_date: ' ||
525 to_char(cur_rec.from_date,'DD-MON-YYYY HH24:MI:SS') ||
526 ' cur_rec.from_time: '|| cur_rec.from_time ||
527 ' cur_rec.end_date: ' ||
528 to_char(cur_rec.end_date,'DD-MON-YYYY HH24:MI:SS') ||
529 ' cur_rec.to_time: '||cur_rec.to_time ||
530 ' cur_rec.diff_shift : '||cur_rec.diff_shift);
531 */
532 IF cur_rec.shift_date = trunc(p_end_date) THEN
533 -- Bug 13582990 vkinduri
534 IF (p_end_date < cur_rec.end_date) AND (p_end_date >= cur_rec.from_date) THEN
535 log_message(' SAME DAY ROW TREATMENT ' );
536
537 -- within the current row of calender
538 cur_rec.end_date := p_end_date ;
539 cur_rec.to_time := l_end_time ;
540 cur_rec.diff_shift := cur_rec.to_time - cur_rec.from_time ;
541 /*
542 log_message(
543 ' BECOME cur_rec.from_date: ' ||
544 to_char(cur_rec.from_date,'DD-MON-YYYY HH24:MI:SS') ||
545 ' cur_rec.from_time: '|| cur_rec.from_time ||
546 ' cur_rec.end_date: ' ||
547 to_char(cur_rec.end_date,'DD-MON-YYYY HH24:MI:SS') ||
548 ' cur_rec.to_time: '||cur_rec.to_time ||
549 ' cur_rec.diff_shift : '||cur_rec.diff_shift);
550 */
551 ELSE
552 -- outside of the same day row hence keep it as it is
553 NULL;
554 END IF;
555
556 END IF;
557
558 IF remaining_duration > cur_rec.diff_shift THEN
559 o_cnt := o_cnt + 1 ;
560 p_output_tbl(o_cnt).start_date := cur_rec.from_date ;
561 p_output_tbl(o_cnt).end_date := cur_rec.end_date ;
562 p_output_tbl(o_cnt).duration := cur_rec.diff_shift ;
563 remaining_duration := remaining_duration - p_output_tbl(o_cnt).duration ;
564 /*
565 log_message(' 1 -> cur_rec.from_time in seconds: '||cur_rec.from_time ||
566 ' cur_rec.to_time in seconds: '||cur_rec.to_time ||
567 ' cur_rec.diff_shift : '||cur_rec.diff_shift ||
568 ' remaining_duration: ' || remaining_duration );
569
570 log_message(' 1 OUT -> p_output_tbl(o_cnt).start_date: ' ||
571 to_char(p_output_tbl(o_cnt).start_date,'DD-MON-YYYY HH24:MI:SS') ||
572 ' p_output_tbl(o_cnt).duration ' ||
573 p_output_tbl(o_cnt).duration ||
574 ' p_output_tbl(o_cnt).end_date ' ||
575 to_char(p_output_tbl(o_cnt).end_date,'DD-MON-YYYY HH24:MI:SS')
576 );
577 */
578 ELSE
579 o_cnt := o_cnt + 1 ;
580 p_output_tbl(o_cnt).start_date := cur_rec.from_date ;
581 p_output_tbl(o_cnt).duration := (remaining_duration - cur_rec.diff_shift) ;
582 p_output_tbl(o_cnt).end_date := cur_rec.end_date ;
583
584 If p_output_tbl(o_cnt).duration <= 0 THEN
585
586 p_output_tbl(o_cnt).start_date :=
587 cur_rec.end_date - (remaining_duration/86400) ;
588 p_output_tbl(o_cnt).duration := remaining_duration ;
589 remaining_duration := 0;
590 /*
591 log_message(' 2 -> cur_rec.from_time in seconds: '||cur_rec.from_time ||
592 ' cur_rec.to_time in seconds: '||cur_rec.to_time ||
593 ' cur_rec.diff_shift : '||cur_rec.diff_shift ||
594 ' remaining_duration: ' || remaining_duration );
595
596 log_message(' 2 OUT -> p_output_tbl(o_cnt).start_date: ' ||
597 to_char(p_output_tbl(o_cnt).start_date,'DD-MON-YYYY HH24:MI:SS') ||
598 ' p_output_tbl(o_cnt).duration ' ||
599 p_output_tbl(o_cnt).duration ||
600 ' p_output_tbl(o_cnt).end_date ' ||
601 to_char(p_output_tbl(o_cnt).end_date,'DD-MON-YYYY HH24:MI:SS')
602 );
603 */
604 EXIT;
605 END IF;
606
607 END IF;
608
609 END LOOP ;
610
611 ELSE
612 FND_MESSAGE.SET_NAME('GMP','GMP_ENTER_START_OR_END_DATE');
613 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
614 FND_MSG_PUB.ADD;
615 x_return_status := FND_API.G_RET_STS_ERROR;
616
617 /* } */
618 END IF ;
619
620 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
621
622 EXCEPTION
623 WHEN CONTIG_PERIODS_FAILURE OR INVALID_VERSION THEN
624 x_return_status := FND_API.G_RET_STS_ERROR;
625
626 WHEN CALENDAR_REQUIRED THEN
627 x_return_status := FND_API.G_RET_STS_ERROR;
628 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
629 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
630 FND_MSG_PUB.ADD;
631
632 WHEN ZERO_DURATION THEN
633 NULL;
634
635 WHEN OTHERS THEN
636 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
637 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
638 FND_MSG_PUB.ADD;
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 END get_contiguous_periods;
641 /*
642 ==========================================================================
643 Procedure:
644 check_contig_periods
645
646 DESCRIPTION:
647
648 The following Procedure checks the data passed and Returns S code If
649 Successful
650
651 History :
652 Sgidugu 08/21/2003 Initial implementation
653 ==========================================================================
654 */
655 PROCEDURE check_contig_periods(
656 p_calendar_code IN VARCHAR2,
657 p_start_date IN DATE,
658 p_end_date IN DATE,
659 p_duration IN NUMBER,
660 x_return_status OUT NOCOPY VARCHAR2) IS
661
662 CURSOR Cur_cal_check ( c_calendar_code VARCHAR2 ) IS
663 SELECT COUNT(1)
664 FROM bom_Calendars
665 WHERE calendar_code = c_calendar_code;
666
667 CURSOR Cur_cal_date ( c_calendar_code VARCHAR2 ) IS
668 SELECT calendar_start_date ,
669 calendar_end_date
670 FROM bom_Calendars
671 WHERE calendar_code = c_calendar_code;
672
673 v_min_date date;
674 v_max_date date;
675
676 INVALID_DATE_RANGE EXCEPTION;
677 CALENDAR_NULL EXCEPTION;
678 INVALID_VALUE EXCEPTION;
679 GMP_DATE_NOT_IN_CAL_RANGE EXCEPTION;
680 ENTER_START_OR_END_DATE EXCEPTION;
681 PS_INVALID_CALENDAR EXCEPTION;
682
683 X_field varchar2(2000) := '';
684 X_value varchar2(2000) := '';
685 X_msg varchar2(2000) := '';
686 l_count number := 0;
687
688 begin
689 x_return_status := 'S';
690
691 OPEN Cur_cal_date (p_calendar_code);
692 FETCH Cur_cal_date into v_min_date,v_max_date;
693 CLOSE Cur_cal_date;
694
695 if p_duration < 0
696 then
697 x_return_status := 'E';
698 X_field := 'Duration';
699 X_value := p_duration;
700 RAISE INVALID_VALUE;
701 end if;
702
703 /* We could write an ELSE condition to make the logic complete, but is
704 not needed as calling proc makes sure one and only one date is NOT NULL
705 */
706
707 IF p_start_date IS NOT NULL THEN
708 IF (p_start_date < v_min_date) OR (p_start_date > v_max_date)
709 THEN
710 x_return_status := 'E';
711 RAISE GMP_DATE_NOT_IN_CAL_RANGE;
712 END IF;
713 ELSIF p_end_date IS NOT NULL THEN
714 IF (p_end_date < v_min_date) OR (p_end_date > v_max_date)
715 THEN
716 x_return_status := 'E';
717 RAISE GMP_DATE_NOT_IN_CAL_RANGE;
718 END IF;
719 END IF ;
720
721 OPEN Cur_cal_check (p_calendar_code);
722 FETCH Cur_cal_check INTO l_count;
723 CLOSE Cur_cal_check;
724 --
725 IF l_count = 0
726 THEN
727 RAISE PS_INVALID_CALENDAR;
728 END IF;
729 --
730 /* Erroring Out when Both Start Date and End Date is Passed at the same time
731 */
732 if ((p_start_date is NOT NULL) AND
733 (p_end_date is NOT NULL ))
734 then
735 x_return_status := 'E';
736 RAISE ENTER_START_OR_END_DATE;
737 end If;
738
739
740 EXCEPTION
741 WHEN GMP_DATE_NOT_IN_CAL_RANGE THEN
742 x_return_status := FND_API.G_RET_STS_ERROR;
743 FND_FILE.PUT_LINE(FND_FILE.LOG,'Date Passed is Out of Calendar Range '||X_msg);
744 FND_MESSAGE.SET_NAME('GMP','GMP_DATE_NOT_IN_CAL_RANGE');
745 FND_MSG_PUB.ADD;
746
747 WHEN ENTER_START_OR_END_DATE THEN
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enter Start Or End Date '||X_msg);
750 FND_MESSAGE.SET_NAME('GMP','GMP_ENTER_START_OR_END_DATE');
751 FND_MSG_PUB.ADD;
752
753 WHEN PS_INVALID_CALENDAR THEN
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
756 FND_MSG_PUB.ADD;
757
758 WHEN INVALID_VALUE THEN
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
761 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
762 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
763 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
764 FND_MSG_PUB.ADD;
765 END check_contig_periods;
766
767 /* *****************************************************************
768 Gantt Chart APIs
769 *****************************************************************
770 ==========================================================================
771 Procedure:
772 get_all_dates
773
774 DESCRIPTION:
775
776 The following Procedure gets the Working and Non-Working dates between two
777 specified Start and End Dates in a Calendar
778
779 History :
780 Sgidugu 08/21/2003 Initial implementation
781 ==========================================================================
782 */
783
784 PROCEDURE get_all_dates(
785 p_api_version IN NUMBER,
786 p_init_msg_list IN BOOLEAN := TRUE,
787 p_calendar_code IN VARCHAR2,
788 p_start_date IN DATE,
789 p_end_date IN DATE,
790 p_output_tbl OUT NOCOPY date_tbl,
791 x_return_status IN OUT NOCOPY VARCHAR2
792 ) IS
793
794 CURSOR get_all_dates (c_calendar_code VARCHAR2,
795 c_start_date DATE,
796 c_end_date DATE) IS
797 SELECT sd.shift_date calendar_date,
798 decode(SUM(decode(sd.seq_num,NULL,0,1)),0,0,1) l_work_day
799 FROM bom_calendars cal,
800 bom_shift_dates sd,
801 bom_shift_times st
802 WHERE cal.calendar_code = c_calendar_code
803 AND sd.calendar_code = cal.calendar_code
804 AND st.calendar_code = sd.calendar_code
805 AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
806 AND sd.shift_num = st.shift_num
807 GROUP BY sd.shift_date
808 ORDER BY sd.shift_date; /*B5182025 - sowsubra - added order by clause*/
809
810 i INTEGER := 0 ;
811 add_day INTEGER := 1 ;
812
813 /* Local variable section */
814
815 l_api_name CONSTANT VARCHAR2(30) := 'GET_ALL_DATES';
816 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
817
818 /* Define Exceptions */
819 CALENDAR_REQUIRED EXCEPTION;
820 CHECK_ALL_DATES_FAILURE EXCEPTION;
821 INVALID_VERSION EXCEPTION;
822 VALUE_REQUIRED EXCEPTION;
823 INVALID_CAL_RANGE EXCEPTION;
824
825 X_field varchar2(2000) := '';
826 X_value varchar2(2000) := '';
827 X_msg varchar2(2000) := '';
828
829 BEGIN
830
831 /* Set the return status to success initially */
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833
834 /* Initialize message list and count if needed */
835 IF p_init_msg_list THEN
836 fnd_msg_pub.initialize;
837 END IF;
838
839 /* Make sure we are call compatible */
840 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
841 ,p_api_version
842 ,'GET_ALL_DATES'
843 ,GMP_CALENDAR_API.m_pkg_name) THEN
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 RAISE INVALID_VERSION;
846 END IF;
847 --
848 IF p_calendar_code is NOT NULL
849 THEN
850 IF ((p_start_date IS NULL) OR (p_end_date IS NULL))
851 THEN
852 x_return_status := 'E';
853 X_field := 'Start/End Date';
854 X_value := p_start_date||'-'||p_end_date ;
855 RAISE VALUE_REQUIRED;
856 END IF;
857 --
858 IF p_end_date < p_start_date
859 THEN
860 x_return_status := 'E';
861 RAISE INVALID_CAL_RANGE;
862 END IF;
863 --
864 IF l_return_status = 'E'
865 THEN
866 RAISE check_all_dates_failure;
867 ELSE
868 FOR c_rec in get_all_dates (p_calendar_code, p_start_date, p_end_date)
869 LOOP
870 i := i + 1;
871 p_output_tbl(i). cal_date := c_rec.calendar_date ;
872 p_output_tbl(i). is_workday:= c_rec.l_work_day;
873 END LOOP;
874 END IF;
875 ELSE
876 x_return_status := 'E';
877 X_msg := 'Calendar';
878 RAISE CALENDAR_REQUIRED;
879 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
880 FND_MSG_PUB.ADD;
881 END IF;
882
883 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
884
885 EXCEPTION
886 WHEN check_all_dates_failure OR invalid_version THEN
887 x_return_status := FND_API.G_RET_STS_ERROR;
888
889 WHEN CALENDAR_REQUIRED OR VALUE_REQUIRED THEN
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
892 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
893 FND_MSG_PUB.ADD;
894
895 WHEN INVALID_CAL_RANGE THEN
896 x_return_status := FND_API.G_RET_STS_ERROR;
897 FND_MESSAGE.SET_NAME('GMP','MR_INV_CALENDAR_RANGE');
898 FND_MSG_PUB.ADD;
899
900 WHEN OTHERS THEN
901 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
902 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
903 FND_MSG_PUB.ADD;
904 x_return_status := FND_API.g_ret_sts_unexp_error;
905
906 END get_all_dates ;
907
908 /*
909 ==========================================================================
910 Procedure:
911 get_work_days
912
913 DESCRIPTION:
914
915 The following Procedure gets the workdays between a specified Start and
916 End dates in a Calendar
917
918 History :
919 Sgidugu 08/21/2003 Initial implementation
920 ==========================================================================
921 */
922
923 PROCEDURE get_work_days(
924 p_api_version IN NUMBER,
925 p_init_msg_list IN BOOLEAN := TRUE,
926 p_calendar_code IN VARCHAR2,
927 p_start_date IN DATE,
928 p_end_date IN DATE,
929 p_output_tbl OUT NOCOPY workdays_tbl,
930 x_return_status IN OUT NOCOPY VARCHAR2
931 ) IS
932 CURSOR get_cal_dates (c_calendar_code VARCHAR2,
933 c_start_date DATE ,
934 c_end_date DATE ) IS
935 SELECT sd.shift_date calendar_date,
936 SUM((DECODE(st.to_time,0,86400,st.to_time) - st.from_time)/3600) duration
937 FROM bom_calendars cal,
938 bom_shift_dates sd,
939 bom_shift_times st
940 WHERE cal.calendar_code = c_calendar_code
941 AND sd.calendar_code = cal.calendar_code
942 AND st.calendar_code = sd.calendar_code
943 AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
944 AND sd.shift_num = st.shift_num
945 AND sd.seq_num IS NOT NULL
946 GROUP BY sd.shift_date
947 HAVING SUM((DECODE(st.to_time,0,86400,st.to_time) - st.from_time)/3600) > 0 ;
948
949 i INTEGER := 0 ;
950 /* Local variable section */
951
952 l_api_name CONSTANT VARCHAR2(30) := 'GET_WORK_DAYS';
953 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
954
955 /* Define Exceptions */
956 CALENDAR_REQUIRED EXCEPTION;
957 work_days_failure EXCEPTION;
958 INVALID_VERSION EXCEPTION;
959 X_msg varchar2(2000) := '';
960
961 BEGIN
962
963 /* Set the return status to success initially */
964 x_return_status := FND_API.G_RET_STS_SUCCESS;
965
966 /* Initialize message list and count if needed */
967 IF p_init_msg_list THEN
968 fnd_msg_pub.initialize;
969 END IF;
970
971 /* Make sure we are call compatible */
972 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
973 ,p_api_version
974 ,'GET_WORK_DAYS'
975 ,GMP_CALENDAR_API.m_pkg_name) THEN
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 RAISE INVALID_VERSION;
978 END IF;
979
980 IF p_calendar_code is NOT NULL
981 THEN
982 check_all_dates (
983 p_calendar_code,
984 p_start_date,
985 p_end_date,
986 l_return_status
987 );
988 --
989 IF l_return_status = 'E'
990 THEN
991 RAISE work_days_failure;
992 ELSE
993 FOR c_rec in get_cal_dates (p_calendar_code, p_start_date, p_end_date)
994 LOOP
995 i := i + 1;
996 p_output_tbl(i).workday := c_rec.calendar_date ;
997 END LOOP ;
998 END IF;
999 ELSE
1000 x_return_status := 'E';
1001 X_msg := 'Calendar';
1002 RAISE CALENDAR_REQUIRED;
1003 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1004 FND_MSG_PUB.ADD;
1005 END IF;
1006
1007 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1008
1009 EXCEPTION
1010 WHEN work_days_failure OR invalid_version THEN
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012
1013 WHEN CALENDAR_REQUIRED THEN
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1016 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1017 FND_MSG_PUB.ADD;
1018
1019 WHEN OTHERS THEN
1020 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1021 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1022 FND_MSG_PUB.ADD;
1023 x_return_status := FND_API.g_ret_sts_unexp_error;
1024
1025 END get_work_days ;
1026
1027 /* ==========================================================================
1028 Procedure:
1029 get_workday_details
1030
1031 DESCRIPTION:
1032
1033 The following Procedure gets the Workday Details for a given Shopday
1034
1035 History :
1036 Sgidugu 08/21/2003 Initial implementation
1037 ========================================================================== */
1038
1039 PROCEDURE get_workday_details(
1040 p_api_version IN NUMBER,
1041 p_init_msg_list IN BOOLEAN := TRUE,
1042 p_calendar_code IN VARCHAR2,
1043 p_shopday_no IN NUMBER,
1044 p_output_tbl OUT NOCOPY shopday_dtl_tbl,
1045 x_return_status IN OUT NOCOPY VARCHAR2
1046 ) IS
1047
1048 CURSOR shopday_dtls_cur (c_calendar_code VARCHAR2,
1049 c_shopday_no NUMBER) IS
1050 SELECT shift_num,from_time,DECODE(to_time,0,86400,to_time) to_time
1051 FROM bom_shift_times
1052 WHERE calendar_code = c_calendar_code
1053 AND shift_num = c_shopday_no
1054 ORDER BY from_time ;
1055
1056 CURSOR Cur_shop_day (c_calendar_code VARCHAR2,
1057 c_shopday_no NUMBER) IS
1058 SELECT COUNT(*)
1059 FROM bom_shift_times
1060 WHERE calendar_code = c_calendar_code
1061 AND shift_num = c_shopday_no;
1062
1063 i INTEGER := 0 ;
1064
1065 /* Local variable section */
1066
1067 l_api_name CONSTANT VARCHAR2(30) := 'GET_WORKDAY_DETAILS';
1068 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1069
1070 /* Define Exceptions */
1071 SHOPDAY_NUMBER_REQUIRED EXCEPTION;
1072 WORKDAY_DTLS_FAILURE EXCEPTION;
1073 INVALID_VERSION EXCEPTION;
1074 INVALID_SHOPDAY EXCEPTION;
1075 X_msg varchar2(2000) := '';
1076 l_count number := 0 ;
1077
1078 BEGIN
1079
1080 /* Set the return status to success initially */
1081 x_return_status := FND_API.G_RET_STS_SUCCESS;
1082
1083 /* Initialize message list and count if needed */
1084 IF p_init_msg_list THEN
1085 fnd_msg_pub.initialize;
1086 END IF;
1087
1088 /* Make sure we are call compatible */
1089 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1090 ,p_api_version
1091 ,'GET_WORKDAY_DETAILS'
1092 ,GMP_CALENDAR_API.m_pkg_name) THEN
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 RAISE INVALID_VERSION;
1095 END IF;
1096 --
1097 OPEN Cur_shop_day(p_calendar_code , p_shopday_no);
1098 FETCH Cur_shop_day INTO l_count;
1099 CLOSE Cur_shop_day;
1100
1101 IF l_count = 0
1102 THEN
1103 x_return_status := 'E';
1104 RAISE INVALID_SHOPDAY;
1105 END IF;
1106 --
1107 IF p_shopday_no is NOT NULL
1108 THEN
1109 FOR c_rec in shopday_dtls_cur (p_calendar_code , p_shopday_no) /*Parameter added - calendar id*/
1110 LOOP
1111 i := i + 1;
1112 p_output_tbl(i).shift_no := c_rec.shift_num ;
1113 p_output_tbl(i).shift_start := c_rec.from_time ;
1114 p_output_tbl(i).shift_duration := c_rec.to_time ;
1115 END LOOP ;
1116 ELSE
1117 x_return_status := 'E';
1118 X_msg := 'Shopday Number';
1119 RAISE SHOPDAY_NUMBER_REQUIRED;
1120 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1121 FND_MSG_PUB.ADD;
1122 END IF;
1123
1124 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1125
1126 EXCEPTION
1127 WHEN INVALID_VERSION THEN
1128 x_return_status := FND_API.G_RET_STS_ERROR;
1129
1130 WHEN SHOPDAY_NUMBER_REQUIRED THEN
1131 x_return_status := FND_API.G_RET_STS_ERROR;
1132 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1133 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1134 FND_MSG_PUB.ADD;
1135
1136 WHEN INVALID_SHOPDAY THEN
1137 x_return_status := FND_API.G_RET_STS_ERROR;
1138 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_SHOPDAY');
1139 FND_MSG_PUB.ADD;
1140
1141 WHEN OTHERS THEN
1142 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1143 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1144 FND_MSG_PUB.ADD;
1145 x_return_status := FND_API.g_ret_sts_unexp_error;
1146
1147 END get_workday_details ;
1148
1149 /* ==========================================================================
1150 Procedure:
1151 check_cal_data
1152
1153 DESCRIPTION:
1154
1155 The following Procedure checks the data passed and Returns S code If
1156 Successful
1157
1158 History :
1159 Sgidugu 08/21/2003 Initial implementation
1160 ========================================================================== */
1161 PROCEDURE check_cal_data(
1162 p_calendar_code IN VARCHAR2,
1163 p_date IN DATE,
1164 x_return_status OUT NOCOPY VARCHAR2) IS
1165
1166 CURSOR Cur_cal_check IS
1167 SELECT COUNT(1)
1168 FROM bom_Calendars
1169 WHERE calendar_code = p_calendar_code;
1170
1171 CURSOR Cur_cal_date IS
1172 SELECT calendar_start_date ,
1173 calendar_end_date
1174 FROM bom_Calendars
1175 WHERE calendar_code = p_calendar_code;
1176
1177 GMP_SDATE_BEFORE_CAL_SDATE EXCEPTION;
1178 GMP_EDATE_AFTER_CAL_EDATE EXCEPTION;
1179 PS_INVALID_CALENDAR EXCEPTION;
1180
1181 v_min_date date;
1182 v_max_date date;
1183 X_field varchar2(2000) := '';
1184 X_value varchar2(2000) := '';
1185 X_msg varchar2(2000) := '';
1186 l_count number := 0;
1187
1188
1189 begin
1190 x_return_status := 'S';
1191
1192 --
1193 OPEN Cur_cal_date;
1194 FETCH Cur_cal_date into v_min_date,v_max_date;
1195 CLOSE Cur_cal_date;
1196 --
1197 if nvl(p_date,sysdate) < v_min_date
1198 then
1199 x_return_status := 'E';
1200 RAISE GMP_SDATE_BEFORE_CAL_SDATE;
1201 end if;
1202 --
1203 if nvl(p_date,sysdate) > v_max_date
1204 then
1205 x_return_status := 'E';
1206 RAISE GMP_EDATE_AFTER_CAL_EDATE;
1207 end if;
1208 --
1209 OPEN Cur_cal_check;
1210 FETCH Cur_cal_check into l_count;
1211 CLOSE Cur_cal_check;
1212 --
1213 IF l_count = 0
1214 THEN
1215 RAISE PS_INVALID_CALENDAR;
1216 END IF;
1217
1218
1219 EXCEPTION
1220 WHEN GMP_SDATE_BEFORE_CAL_SDATE THEN
1221 x_return_status := FND_API.G_RET_STS_ERROR;
1222 FND_MESSAGE.SET_NAME('GMP','GMP_SDATE_BEFORE_CAL_SDATE');
1223 FND_MSG_PUB.ADD;
1224
1225 WHEN PS_INVALID_CALENDAR THEN
1226 x_return_status := FND_API.G_RET_STS_ERROR;
1227 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
1228 FND_MSG_PUB.ADD;
1229
1230 WHEN GMP_EDATE_AFTER_CAL_EDATE THEN
1231 x_return_status := FND_API.G_RET_STS_ERROR;
1232 FND_MESSAGE.SET_NAME('GMP','GMP_EDATE_AFTER_CAL_EDATE');
1233 FND_MSG_PUB.ADD;
1234
1235 WHEN OTHERS THEN
1236 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1237 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1238 FND_MSG_PUB.ADD;
1239 x_return_status := FND_API.g_ret_sts_unexp_error;
1240 end check_cal_data;
1241 /* ==========================================================================
1242 Procedure:
1243 check_all_dates
1244
1245 DESCRIPTION:
1246
1247 The following Procedure checks the data passed and Returns S code If
1248 Successful
1249
1250 History :
1251 Sgidugu 08/21/2003 Initial implementation
1252 ========================================================================== */
1253
1254 PROCEDURE check_all_dates(
1255 p_calendar_code IN VARCHAR2,
1256 p_start_date IN DATE,
1257 p_end_date IN DATE,
1258 x_return_status OUT NOCOPY VARCHAR2) IS
1259
1260 CURSOR Cur_cal_check (c_calendar_code VARCHAR2 ) IS
1261 SELECT COUNT(1)
1262 FROM bom_Calendars
1263 WHERE calendar_code = c_calendar_code;
1264
1265 INVALID_DATE_RANGE EXCEPTION;
1266 CALENDAR_NULL EXCEPTION;
1267 INVALID_VALUE EXCEPTION;
1268 VALUE_REQUIRED EXCEPTION;
1269 INVALID_CAL_RANGE EXCEPTION;
1270 PS_INVALID_CALENDAR EXCEPTION;
1271
1272 X_field varchar2(2000) := '';
1273 X_value varchar2(2000) := '';
1274 X_msg varchar2(2000) := '';
1275 l_count number := 0;
1276
1277
1278 begin
1279 x_return_status := 'S';
1280 --
1281 if ((p_start_date IS NULL) OR (p_end_date IS NULL))
1282 then
1283 x_return_status := 'E';
1284 X_field := 'Start/End Date';
1285 X_value := p_start_date||'-'||p_end_date ;
1286 RAISE VALUE_REQUIRED;
1287 end if;
1288 --
1289 OPEN Cur_cal_check ( p_calendar_code );
1290 FETCH Cur_cal_check into l_count;
1291 CLOSE Cur_cal_check;
1292 --
1293 IF l_count = 0
1294 THEN
1295 RAISE PS_INVALID_CALENDAR;
1296 END IF;
1297
1298 --
1299 /* The following lines were commented as per Eddie's recommendation
1300
1301 if ((nvl(p_start_date,sysdate) < v_min_date) OR
1302 (nvl(p_start_date,sysdate) > v_max_date))
1303 then
1304 x_return_status := 'E';
1305 RAISE INVALID_DATE_RANGE;
1306 end If;
1307
1308 if ((nvl(p_end_date,sysdate) < v_min_date) OR
1309 (nvl(p_end_date,sysdate) > v_max_date))
1310 then
1311 x_return_status := 'E';
1312 RAISE INVALID_DATE_RANGE;
1313 end If;
1314 */
1315 --
1316 if p_end_date < p_start_date
1317 then
1318 x_return_status := 'E';
1319 RAISE INVALID_CAL_RANGE;
1320 end If;
1321
1322 EXCEPTION
1323 WHEN VALUE_REQUIRED THEN
1324 x_return_status := FND_API.G_RET_STS_ERROR;
1325 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1326 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1327 FND_MSG_PUB.ADD;
1328 --
1329 WHEN INVALID_CAL_RANGE THEN
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331 FND_MESSAGE.SET_NAME('GMP','MR_INV_CALENDAR_RANGE');
1332 FND_MSG_PUB.ADD;
1333 --
1334 WHEN PS_INVALID_CALENDAR THEN
1335 x_return_status := FND_API.G_RET_STS_ERROR;
1336 FND_MESSAGE.SET_NAME('GMP','PS_INVALID_CALENDAR');
1337 FND_MSG_PUB.ADD;
1338 --
1339 WHEN OTHERS THEN
1340 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1341 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1342 FND_MSG_PUB.ADD;
1343 x_return_status := FND_API.g_ret_sts_unexp_error;
1344
1345 end check_all_dates;
1346
1347 /*
1348 |==========================================================================
1349 | Procedure: |
1350 | is_working_daytime |
1351 | |
1352 | DESCRIPTION: |
1353 | |
1354 | API returns if the date time passed for a calendar is a work day or a |
1355 | Non Work day |
1356 | The API takes Calendar_id, Date and Time and Indicator as Inputs |
1357 | and returns if the day is a work day or a Non-work day, The |
1358 | Indicator takes values 0 or 1 0 means Start and 1 means End |
1359 | |
1360 | History : |
1361 | Sridhar 19-SEP-2003 Initial implementation |
1362 | B4610901, Rajesh Patangya 15-Sep-2005 |
1363 ==========================================================================
1364 */
1365
1366 FUNCTION IS_WORKING_DAYTIME(
1367 p_api_version IN NUMBER,
1368 p_init_msg_list IN BOOLEAN := TRUE,
1369 p_calendar_code IN VARCHAR2,
1370 p_date IN DATE,
1371 p_ind IN NUMBER,
1372 x_return_status IN OUT NOCOPY VARCHAR2
1373 ) RETURN BOOLEAN
1374 IS
1375 /* p_ind 0 means start and 1 means end */
1376
1377 /* Local variable section */
1378 l_api_name CONSTANT VARCHAR2(30) := 'IS_WORKING_DAYTIME';
1379 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1380
1381 x_date date;
1382
1383 CURSOR get_datetime_cur (c_calendar_code VARCHAR2, c_cal_date DATE) IS
1384 SELECT 1
1385 FROM bom_calendars cal,
1386 bom_shift_dates sd,
1387 bom_shift_times st
1388 WHERE cal.calendar_code = c_calendar_code
1389 AND sd.calendar_code = cal.calendar_code
1390 AND st.calendar_code = sd.calendar_code
1391 AND sd.shift_num = st.shift_num
1392 -- B4610901, Rajesh Patangya 15-Sep-2005
1393 AND (sd.shift_date + (st.from_time/86400)) <= c_cal_date
1394 AND DECODE(SIGN(st.from_time - DECODE(st.to_time,0,86400,st.to_time)),
1395 1,(sd.shift_date+1), sd.shift_date
1396 ) + (decode(st.to_time,0,86400,st.to_time)/86400) >= c_cal_date
1397 /* AND DECODE(
1398 SIGN(st.from_time - st.to_time),
1399 1,(sd.shift_date+1), sd.shift_date
1400 ) + (st.to_time/86400) >= c_cal_date */
1401 AND sd.seq_num IS NOT NULL;
1402
1403 /* Define Exceptions */
1404 CALENDAR_REQUIRED EXCEPTION;
1405 INVALID_DATA_PASSED EXCEPTION;
1406 INVALID_VALUE EXCEPTION;
1407 INVALID_VERSION EXCEPTION;
1408
1409 l_count NUMBER := 0 ;
1410 X_msg varchar2(2000) := '';
1411 X_field varchar2(2000) := '';
1412 X_value varchar2(2000) := '';
1413
1414 BEGIN
1415
1416 /* Set the return status to success initially */
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 /* Initialize message list and count if needed */
1420 IF p_init_msg_list THEN
1421 fnd_msg_pub.initialize;
1422 END IF;
1423
1424 /* Make sure we are call compatible */
1425 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1426 ,p_api_version
1427 ,'IS_WORKING_DAYTIME'
1428 ,GMP_CALENDAR_API.m_pkg_name) THEN
1429 x_return_status := FND_API.G_RET_STS_ERROR;
1430 RAISE INVALID_VERSION;
1431 END IF;
1432
1433 /* Error Out if the Indicator passed not 0 or 1 */
1434 IF (p_ind not in (0,1))
1435 THEN
1436 X_field := 'Indicator ';
1437 X_value := p_ind;
1438 RAISE INVALID_VALUE;
1439
1440 END IF;
1441 IF ((p_calendar_code is NOT NULL) AND (p_date is NOT NULL ))
1442 THEN
1443 check_cal_data(
1444 p_calendar_code,
1445 p_date,
1446 l_return_status
1447 );
1448
1449 IF l_return_status = 'E'
1450 THEN
1451 RAISE INVALID_DATA_PASSED;
1452 ELSE
1453 IF p_ind = 0 THEN
1454 x_date := p_date + 1/86400 ;
1455 ELSIF p_ind = 1 THEN
1456 x_date := p_date - 1/86400 ;
1457 END IF ;
1458
1459 OPEN get_datetime_cur (p_calendar_code , x_date) ;
1460 FETCH get_datetime_cur INTO l_count ;
1461 CLOSE get_datetime_cur ;
1462
1463 IF l_count = 1 THEN
1464 RETURN TRUE ;
1465 ELSE
1466 RETURN FALSE ;
1467 END IF ;
1468 END IF;
1469 ELSE
1470 x_return_status := 'E';
1471 X_msg := 'Calendar/Date ';
1472 RAISE CALENDAR_REQUIRED;
1473 END IF;
1474
1475 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed '||l_api_name ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1476
1477 EXCEPTION
1478 WHEN INVALID_DATA_PASSED OR invalid_version THEN
1479
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1482 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1483 FND_MSG_PUB.ADD;
1484 RETURN FALSE ;
1485
1486 WHEN CALENDAR_REQUIRED THEN
1487 x_return_status := FND_API.G_RET_STS_ERROR;
1488 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1489 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1490 FND_MSG_PUB.ADD;
1491 RETURN FALSE ;
1492
1493 WHEN INVALID_VALUE THEN
1494 x_return_status := FND_API.G_RET_STS_ERROR;
1495 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
1496 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
1497 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
1498 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
1499 FND_MSG_PUB.ADD;
1500 RETURN FALSE ;
1501
1502 WHEN OTHERS THEN
1503 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1504 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1505 FND_MSG_PUB.ADD;
1506 x_return_status := FND_API.g_ret_sts_unexp_error;
1507 RETURN FALSE ;
1508 END IS_WORKING_DAYTIME ;
1509
1510 -- Bug: 6265867 Kbanddyo added this procedure
1511 /*
1512 |==========================================================================
1513 | Procedure: |
1514 | get_nearest_workdaytime |
1515 | |
1516 | DESCRIPTION: |
1517 | |
1518 | The purpose of the API is to return the working date-time that is |
1519 | closest to the date-time passed in as parameter |
1520 | When the date-time passed in is NOT work time the API either searches |
1521 | backwards to locate the end of previous workday OR searches forward |
1522 | to locate the start of next workday - this direction of search is |
1523 | controlled by parameter pDirection |
1524 | |
1525 | PARAMETERS |
1526 | p_direction - 0 means backwards and 1 means forward |
1527 | History : |
1528 | Abhay 24-Jul-2006 Initial implementation |
1529 | B5378109 Teva |
1530 ==========================================================================
1531 */
1532
1533 PROCEDURE get_nearest_workdaytime(
1534 p_api_version IN NUMBER,
1535 p_init_msg_list IN BOOLEAN := TRUE,
1536 p_calendar_id IN VARCHAR2,
1537 p_date IN DATE,
1538 p_direction IN NUMBER,
1539 x_date IN OUT NOCOPY DATE ,
1540 x_return_status IN OUT NOCOPY VARCHAR2
1541 )
1542 IS
1543
1544 CALENDAR_REQUIRED EXCEPTION;
1545 INVALID_DATA_PASSED EXCEPTION;
1546 INVALID_VALUE EXCEPTION;
1547 INVALID_VERSION EXCEPTION;
1548 l_count NUMBER ;
1549 X_msg VARCHAR2(2000) ;
1550 X_field VARCHAR2(2000) ;
1551 X_value VARCHAR2(2000) ;
1552 l_api_name CONSTANT VARCHAR2(30) := 'GET_NEAREST_WORKDAYTIME';
1553 l_return_status VARCHAR2(1) ;
1554 l_date DATE;
1555
1556 CURSOR Is_WorkDayTime (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1557 SELECT 1
1558 FROM sys.dual
1559 WHERE EXISTS (
1560 select 'x'
1561 FROM bom_calendars bd,
1562 bom_shift_dates sd,
1563 bom_shift_times st
1564 WHERE bd.calendar_code = p_calendar_id
1565 AND sd.calendar_code = bd.calendar_code
1566 AND sd.calendar_code= st.calendar_code
1567 AND sd.shift_num = st.shift_num
1568 AND (sd.shift_date + (st.from_time/86400)) <= p_date
1569 AND sd.seq_num IS NOT NULL
1570 AND ((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,
1571 (sd.shift_date+1),sd.shift_date) ) +
1572 (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,
1573 (st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),
1574 (st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 ) ) >= p_date);
1575
1576
1577 CURSOR get_NextDatetime_cur (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1578 SELECT min (sd.shift_date + (st.from_time/86400))
1579 FROM bom_calendars bd,
1580 bom_shift_dates sd,
1581 bom_shift_times st
1582 WHERE bd.calendar_code = p_calendar_id
1583 AND sd.calendar_code = bd.calendar_code
1584 AND sd.calendar_code= st.calendar_code
1585 AND sd.shift_num = st.shift_num
1586 AND sd.seq_num IS NOT NULL
1587 AND (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) > 0
1588 AND (sd.shift_date + (st.from_time/86400)) > p_cal_date ;
1589
1590
1591 CURSOR get_PrevDatetime_cur (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1592 SELECT max (
1593 ((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,
1594 (sd.shift_date+1),sd.shift_date) ) +
1595 (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,(st.from_time
1596 + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 ) )
1597 )
1598 FROM bom_calendars bd,
1599 bom_shift_dates sd,
1600 bom_shift_times st
1601 WHERE bd.calendar_code = p_calendar_id
1602 AND sd.calendar_code = bd.calendar_code
1603 AND sd.calendar_code= st.calendar_code
1604 AND sd.shift_num = st.shift_num
1605 AND sd.seq_num IS NOT NULL
1606
1607 AND (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) > 0
1608 AND ((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,
1609 (sd.shift_date+1),sd.shift_date) ) +
1610 (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,(st.from_time
1611 + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 ))
1612 < p_date;
1613
1614 BEGIN
1615
1616 l_count := 0 ;
1617 X_msg := '';
1618 X_field := '';
1619 X_value := '';
1620 l_date := NULL;
1621 l_return_status := FND_API.G_RET_STS_SUCCESS;
1622 x_return_status := FND_API.G_RET_STS_SUCCESS;
1623
1624 /* Initialize message list and count if needed */
1625 IF p_init_msg_list THEN
1626 fnd_msg_pub.initialize;
1627 END IF;
1628
1629 /* Make sure we are call compatible */
1630 IF NOT FND_API.compatible_api_call ( GMP_CALENDAR_API.m_api_version
1631 ,p_api_version
1632 ,'GET_NEAREST_WORKDAYTIME'
1633 ,GMP_CALENDAR_API.m_pkg_name) THEN
1634 FND_FILE.PUT_LINE ( FND_FILE.LOG,'if not FND_API.compatible_api_call');
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636 x_date := (sysdate - 9999 ) ;
1637 RAISE INVALID_VERSION;
1638 END IF;
1639
1640 /* Error Out if the Indicator passed not 0 or 1 */
1641
1642
1643 IF (p_direction not in (0,1))
1644 THEN
1645 X_field := 'Direction ';
1646 X_value := p_direction;
1647
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649 x_date := (sysdate - 9999 ) ;
1650 RAISE INVALID_VALUE;
1651
1652 END IF;
1653
1654 IF ((p_calendar_id is NOT NULL) AND (p_date is NOT NULL )) THEN
1655 check_cal_data(
1656 p_calendar_id,
1657 p_date,
1658 l_return_status
1659 );
1660
1661
1662
1663
1664 FND_FILE.PUT_LINE ( FND_FILE.LOG,p_calendar_id);
1665 FND_FILE.PUT_LINE ( FND_FILE.LOG,to_char(p_date,'dd/mm/yyyy hh24:mi:ss'));
1666
1667 IF l_return_status = 'E' THEN
1668
1669 FND_FILE.PUT_LINE ( FND_FILE.LOG,'IF l_return_status = E');
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 x_date := (sysdate - 9999 ) ;
1672 RAISE INVALID_DATA_PASSED;
1673 ELSE
1674 OPEN Is_WorkDayTime(p_calendar_id , p_date) ;
1675 FETCH Is_WorkdayTime INTO l_count ;
1676 CLOSE Is_WorkdayTime ;
1677
1678 IF l_count = 1 THEN
1679 x_return_status := FND_API.G_RET_STS_SUCCESS;
1680 x_date := p_date ;
1681 ELSE
1682 IF p_direction = 1 THEN
1683 OPEN get_NextDatetime_cur(p_calendar_id , p_date) ;
1684 FETCH get_NextDatetime_cur INTO l_date ;
1685 CLOSE get_NextDatetime_cur ;
1686
1687 ELSE
1688 OPEN get_PrevDatetime_cur(p_calendar_id , p_date) ;
1689 FETCH get_PrevDatetime_cur INTO l_date ;
1690 CLOSE get_PrevDatetime_cur ;
1691 END IF ;
1692 l_return_status := FND_API.G_RET_STS_SUCCESS;
1693 x_return_status := FND_API.G_RET_STS_SUCCESS;
1694 X_date := l_date ;
1695
1696 END IF ; /* IF l_count = 1 */
1697 END IF; /* IF l_return_status = 'E' */
1698 ELSE
1699
1700
1701 FND_FILE.PUT_LINE ( FND_FILE.LOG,'last else');
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 x_date := (sysdate - 9999 ) ;
1704 X_msg := 'Calendar/Date ';
1705 RAISE CALENDAR_REQUIRED;
1706 END IF;
1707 EXCEPTION
1708 WHEN INVALID_DATA_PASSED OR invalid_version THEN
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 x_date := (sysdate - 9999 ) ;
1711 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1712 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1713 FND_MSG_PUB.ADD;
1714
1715 WHEN CALENDAR_REQUIRED THEN
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1718 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1719 FND_MSG_PUB.ADD;
1720 x_date := (sysdate - 9999 ) ;
1721
1722 WHEN INVALID_VALUE THEN
1723 x_return_status := FND_API.G_RET_STS_ERROR;
1724 x_date := (sysdate - 9999 ) ;
1725 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Value '||X_field||'-'||X_value);
1726 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
1727 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
1728 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
1729 FND_MSG_PUB.ADD;
1730
1731 WHEN OTHERS THEN
1732 x_return_status := FND_API.g_ret_sts_unexp_error;
1733 x_date := (sysdate - 9999 ) ;
1734 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1735 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1736 FND_MSG_PUB.ADD;
1737
1738 END get_nearest_workdaytime ;
1739
1740
1741 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) IS
1742 BEGIN
1743 IF fnd_global.conc_request_id > 0 THEN
1744 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
1745 ELSE
1746 NULL;
1747 END IF;
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 RETURN;
1752 END LOG_MESSAGE;
1753
1754 PROCEDURE retrieve_calendar_detail( p_calendar_code IN VARCHAR2,
1755 return_status OUT NOCOPY BOOLEAN) IS
1756 n_calendar_code varchar2(40);
1757 cal_count number ;
1758 cal_start_date date;
1759 cal_end_date date;
1760 get_shift_time varchar2(15000);
1761 sql_cal varchar2(15000);
1762 cal_cur ref_cursor_typ;
1763 i integer ;
1764 j integer ;
1765 stmt_no integer ;
1766 wps_index integer ;
1767 ins_stmt VARCHAR2(15000) ;
1768 ins_stmt1 VARCHAR2(15000) ;
1769 temp_from_date DATE ;
1770 temp_to_date DATE ;
1771 temp_to_time NUMBER ;
1772 temp_shift_num NUMBER ;
1773 no_of_secs CONSTANT REAL := 86400;
1774
1775 TYPE cal_shift_typ is RECORD
1776 ( cal_date DATE,
1777 shift_num PLS_INTEGER,
1778 from_time PLS_INTEGER,
1779 to_time PLS_INTEGER
1780 );
1781 calendar_record cal_shift_typ;
1782 TYPE cal_tab is table of cal_shift_typ index by BINARY_INTEGER;
1783 new_rec cal_tab;
1784
1785 BEGIN
1786 i := 0;
1787 j := 0;
1788 cal_count := 0;
1789 wps_index := 0;
1790 ins_stmt := null;
1791 get_shift_time := null;
1792 sql_cal := null;
1793 ins_stmt := null;
1794 ins_stmt1 := null;
1795 temp_from_date := null ;
1796 temp_to_date := null ;
1797 temp_to_time := 0 ;
1798 temp_shift_num := 0 ;
1799
1800 /* Insert for Net Resource starts here, The following select statement gets
1801 the period that are availble for a given calendar, From time and To Time
1802 are taken in seconds here. SEELCT ONLY 800 days row for batches
1803 */
1804
1805 sql_cal := ' SELECT sd.shift_date calendar_date, '
1806 || ' sd.shift_num shift_no, '
1807 || ' st.from_time from_time, '
1808 || ' decode(st.to_time,0,86400,st.to_time) to_time '
1809 || ' FROM bom_calendars cal, '
1810 || ' bom_shift_dates sd, '
1811 || ' bom_shift_times st '
1812 || ' WHERE cal.calendar_code = :curr_cal_code '
1813 || ' AND sd.calendar_code = cal.calendar_code '
1814 || ' AND st.calendar_code = sd.calendar_code '
1815 || ' AND sd.shift_num = st.shift_num '
1816 || ' AND sd.seq_num is not null '
1817 || ' AND sd.shift_date >= (sysdate - 70) '
1818 || ' AND sd.shift_date <= (sysdate + 400) '
1819 || ' ORDER BY calendar_date,from_time,to_time ';
1820
1821 IF new_rec.COUNT > 0
1822 THEN
1823 new_rec.delete;
1824 END IF;
1825
1826 stmt_no := 10;
1827 n_calendar_code := p_calendar_code;
1828 OPEN cal_cur FOR sql_cal USING p_calendar_code;
1829 -- log_message(n_calendar_code || '-' || p_calendar_code);
1830 stmt_no := 20;
1831 i := 0;
1832 LOOP
1833 FETCH cal_cur INTO calendar_record;
1834 EXIT WHEN cal_cur%NOTFOUND;
1835
1836 /* Check for the First record */
1837 IF i = 0 THEN
1838
1839 /* Check if the first row to time is spilling over */
1840 IF calendar_record.to_time < calendar_record.from_time THEN
1841 i := i + 1;
1842 new_rec(i).cal_date := calendar_record.cal_date ;
1843 -- log_message(calendar_record.cal_date);
1844 new_rec(i).shift_num := calendar_record.shift_num ;
1845 new_rec(i).from_time := calendar_record.from_time;
1846 new_rec(i).to_time := no_of_secs ;
1847
1848 /* Add more record for the spilled over shift */
1849
1850 i := i +1 ;
1851 new_rec(i).cal_date := calendar_record.cal_date + 1 ;
1852 new_rec(i).shift_num := calendar_record.shift_num ;
1853 new_rec(i).from_time := 0 ;
1854 new_rec(i).to_time := calendar_record.to_time;
1855 ELSE
1856 /* Else Store the values in the PL/sql table */
1857
1858 i := i + 1;
1859 new_rec(i).cal_date := calendar_record.cal_date ;
1860 new_rec(i).shift_num := calendar_record.shift_num ;
1861 new_rec(i).from_time := calendar_record.from_time;
1862 new_rec(i).to_time := calendar_record.to_time;
1863
1864 END IF;
1865
1866 /* If not the first record, then check if the Calendar date
1867 is greater than the Previous cal date in the PL/sql table */
1868 ELSE
1869 IF calendar_record.cal_date > new_rec(i).cal_date THEN
1870
1871 /* Check if the Date, to_time is spilling over */
1872 IF calendar_record.to_time < calendar_record.from_time THEN
1873 i := i + 1;
1874 new_rec(i).cal_date := calendar_record.cal_date;
1875 new_rec(i).from_time := calendar_record.from_time;
1876 new_rec(i).shift_num := calendar_record.shift_num;
1877 new_rec(i).to_time := no_of_secs;
1878
1879 /* Add more record for the spilled over shift */
1880 i := i + 1;
1881 new_rec(i).cal_date := calendar_record.cal_date + 1;
1882 new_rec(i).shift_num := calendar_record.shift_num;
1883 new_rec(i).from_time := 0;
1884 new_rec(i).to_time := calendar_record.to_time ;
1885 ELSE
1886 /* Else Store the values in the PL/sql table */
1887
1888 i := i + 1 ;
1889 new_rec(i).cal_date := calendar_record.cal_date ;
1890 new_rec(i).shift_num := calendar_record.shift_num ;
1891 new_rec(i).from_time := calendar_record.from_time;
1892 new_rec(i).to_time := calendar_record.to_time;
1893
1894 END IF;
1895
1896 /* If not the first record, then check if the Calendar date
1897 is equal to the Previous cal date in the PL/sql table */
1898
1899 ELSIF calendar_record.cal_date = new_rec(i).cal_date THEN
1900
1901 /* Checking if the Cursor from_time is greater than Previous record to_time */
1902
1903 IF calendar_record.from_time > new_rec(i).to_time THEN
1904 /* Check if the Date, to_time is spilling over */
1905 IF calendar_record.to_time < calendar_record.from_time THEN
1906 i := i + 1;
1907 new_rec(i).cal_date := calendar_record.cal_date;
1908 new_rec(i).from_time := calendar_record.from_time;
1909 new_rec(i).shift_num := calendar_record.shift_num;
1910 new_rec(i).to_time := no_of_secs;
1911
1912 /* Add more record for the spilled over shift */
1913 i := i + 1;
1914 new_rec(i).cal_date := calendar_record.cal_date + 1 ;
1915 new_rec(i).from_time := 0 ;
1916 new_rec(i).shift_num := calendar_record.shift_num;
1917 new_rec(i).to_time := calendar_record.to_time ;
1918 ELSE
1919 i := i + 1;
1920 new_rec(i).cal_date := calendar_record.cal_date ;
1921 new_rec(i).shift_num := calendar_record.shift_num ;
1922 new_rec(i).from_time := calendar_record.from_time;
1923 new_rec(i).to_time := calendar_record.to_time;
1924 END IF ;
1925 ELSE /* Merge time !!!
1926 Shifts Merge is the start time of the shift is Less than
1927 the Previous record to_time
1928 Checking if the record that is Merged is spilling Over to next day */
1929 IF calendar_record.to_time < calendar_record.from_time THEN
1930 new_rec(i).to_time := no_of_secs ;
1931 /* Add more record for the spilled over shift */
1932 i := i + 1;
1933 new_rec(i).cal_date := calendar_record.cal_date + 1;
1934 new_rec(i).from_time := 0 ;
1935 new_rec(i).shift_num := calendar_record.shift_num;
1936 new_rec(i).to_time := calendar_record.to_time ;
1937 ELSE
1938 IF calendar_record.to_time > new_rec(i).to_time THEN
1939 new_rec(i).to_time := calendar_record.to_time ;
1940 END IF ;
1941 END IF ;
1942 END IF ; /* End OF Merge time */
1943
1944 /* checking if the Calendar date is less than the Previous cal date
1945 in the PL/sql table This check is useful when two shifts in a day
1946 are crossing Midnight Then in that case we need to compare the start
1947 time with the Previously completed shift end time and the dates too. */
1948
1949 ELSIF calendar_record.cal_date < new_rec(i).cal_date THEN
1950 IF calendar_record.to_time > no_of_secs THEN
1951 IF calendar_record.to_time - no_of_secs > new_rec(i).to_time THEN
1952 new_rec(i).to_time := calendar_record.to_time - no_of_secs ;
1953 END IF;
1954 END IF ;
1955
1956 END IF ; /* End if for date check */
1957 END IF; /* End if for i = 0 */
1958
1959 END LOOP;
1960
1961 /* cal count gives the Number of rows after the Calendar is exploded */
1962 cal_count := new_rec.COUNT ;
1963 /* Calendar Start date and End dates are Calculated here */
1964 cal_start_date := new_rec(1).cal_date;
1965 cal_end_date := new_rec(cal_count).cal_date;
1966
1967 CLOSE cal_cur;
1968
1969 wps_index := 1 ;
1970 ins_stmt := 'INSERT INTO gmp_calendar_detail_gtmp'
1971 ||' ( '
1972 ||' calendar_code, '
1973 ||' shift_num, '
1974 ||' shift_date, '
1975 ||' from_time, '
1976 ||' to_time, '
1977 ||' from_date, '
1978 ||' to_date '
1979 ||' ) '
1980 ||' VALUES '
1981 ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
1982
1983 /* ins_stmt1 := 'INSERT INTO temp_cal'
1984 ||' ( '
1985 ||' calendar_code, '
1986 ||' shift_num, '
1987 ||' shift_date, '
1988 ||' from_time, '
1989 ||' to_time, '
1990 ||' from_date, '
1991 ||' to_date '
1992 ||' ) '
1993 ||' VALUES '
1994 ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
1995 */
1996 -- log_message(n_calendar_code || '-GTMP-' || new_rec.COUNT);
1997 delete from gmp_calendar_detail_gtmp ;
1998
1999 FOR wps_index IN 1..new_rec.COUNT
2000 LOOP
2001
2002 temp_from_date := (new_rec(wps_index).cal_date +
2003 (new_rec(wps_index).from_time/86400)) ;
2004 temp_to_time := new_rec(wps_index).to_time ;
2005 temp_to_date := (new_rec(wps_index).cal_date + (temp_to_time /86400)) ;
2006
2007 IF new_rec(wps_index).to_time = 86400 THEN
2008 temp_shift_num := new_rec(wps_index).shift_num + 99999 ;
2009 ELSE
2010 temp_shift_num := new_rec(wps_index).shift_num;
2011 END IF ;
2012
2013 EXECUTE IMMEDIATE ins_stmt USING
2014 n_calendar_code,
2015 temp_shift_num,
2016 new_rec(wps_index).cal_date,
2017 new_rec(wps_index).from_time,
2018 temp_to_time,
2019 temp_from_date,
2020 temp_to_date ;
2021
2022 /* EXECUTE IMMEDIATE ins_stmt1 USING
2023 n_calendar_code,
2024 temp_shift_num,
2025 new_rec(wps_index).cal_date,
2026 new_rec(wps_index).from_time,
2027 temp_to_time,
2028 temp_from_date,
2029 temp_to_date ;
2030 */
2031 END LOOP;
2032
2033 return_status := TRUE;
2034
2035 EXCEPTION
2036 WHEN NO_DATA_FOUND THEN
2037 return_status := FALSE;
2038 WHEN OTHERS THEN
2039 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2040 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2041 FND_MSG_PUB.ADD;
2042 return_status := FALSE;
2043
2044 END retrieve_calendar_detail;
2045
2046 END gmp_calendar_api ;