1 PACKAGE BODY gmp_rsrc_avl_pkg as
2 /* $Header: GMPAVLB.pls 120.2 2006/10/03 16:20:41 asatpute noship $ */
3
4 G_PKG_NAME varchar2(32);
5
6 TYPE cal_shift_typ is RECORD
7 ( cal_date DATE,
8 shift_num NUMBER,
9 cal_from_date DATE,
10 cal_to_date DATE
11 );
12 calendar_record cal_shift_typ;
13 TYPE cal_tab is table of cal_shift_typ index by BINARY_INTEGER;
14
15 cal_rec cal_tab;
16
17 /* New changes for Unavailable Resources - 12/14/00 */
18 TYPE unavail_rsrc_typ is RECORD
19 (
20 resource_count NUMBER,
21 u_from_date DATE,
22 u_to_date DATE
23 );
24 unavail_resource_record unavail_rsrc_typ;
25 TYPE unavail_rsrc_tab is table of unavail_rsrc_typ index by BINARY_INTEGER;
26
27 unavail_rec unavail_rsrc_tab;
28 new_unavail_rec unavail_rsrc_tab;
29
30 v_resource_id NUMBER;
31 v_shift_num NUMBER;
32 v_unavail_qty NUMBER;
33 v_assigned_qty NUMBER := 0;
34 v_calendar_date DATE;
35 v_from_date DATE;
36 v_to_date DATE;
37 qty_null EXCEPTION;
38 date_null EXCEPTION;
39 v_from_time DATE;
40 v_to_time DATE;
41 l_organization_id NUMBER;
42 l_calendar_code VARCHAR2(10) ;
43 c INTEGER := 0;
44 u INTEGER ;
45 u1 INTEGER := 1;
46 x INTEGER := 1;
47 i INTEGER := 1;
48 j INTEGER := 1;
49 tur NUMBER := 0;
50 stmt_no NUMBER := 0;
51 update_flag VARCHAR2(1) := 'N';
52 NO_EXCP VARCHAR2(1) := '';
53 NO_NO_EXCP VARCHAR2(1) := '';
54 unavail_from_date DATE;
55 unavail_to_date DATE;
56 temp_from_time DATE;
57 temp_date VARCHAR2(26) ;
58 v_cp_enabled BOOLEAN := TRUE;
59
60 PROCEDURE log_message ( string IN VARCHAR2) IS
61 loop_var INTEGER;
62 BEGIN
63 IF v_cp_enabled THEN
64 FND_FILE.PUT_LINE (FND_FILE.LOG, string);
65 ELSE
66 NULL;
67 END IF;
68 END log_message;
69
70 /*
71 REM+=========================================================================+
72 REM| PROCEDURE NAME |
73 REM| rsrc_avl |
74 REM| |
75 REM| TYPE |
76 REM| Private |
77 REM| |
78 REM| DESCRIPTION |
79 REM| This Procedure will find out the Available Time per Resource and |
80 REM| Calendar code assicatied resource or organization level |
81 REM| HISTROY |
82 REM| Rajesh Patangya created |
83 REM| B4999940 Use of BOM Calendar,Inventory Convergence |
84 REM+=========================================================================+
85 */
86 PROCEDURE rsrc_avl(
87 p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
89 p_cal_code IN VARCHAR2, -- B4999940
90 p_resource_id IN NUMBER,
91 p_from_date IN DATE,
92 p_to_date IN DATE,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2,
96 x_return_code OUT NOCOPY VARCHAR2,
97 p_rec IN OUT NOCOPY cal_tab2,
98 p_flag IN OUT NOCOPY VARCHAR2
99 ) is
100 /* Local Variables for API */
101 gmp_api_name varchar2(30) := 'rsrc_avl';
102 gmp_api_version number := 1.0;
103
104
105 CURSOR cal_c1 IS
106 SELECT cdate,shift_no,
107 decode(sign(ftime - p_from_date),-1,p_from_date,ftime) from_time,
108 decode(sign(ttime - v_to_time),-1,ttime,v_to_time) to_time
109 FROM
110 (
111 SELECT bsd.shift_date cdate,
112 bsd.shift_num shift_no,
113 (bsd.shift_date + (bst.from_time/86400)) ftime,
114 (bsd.shift_date + (bst.to_time/86400)) ttime
115 FROM bom_calendars bc,
116 bom_shift_dates bsd,
117 bom_shift_times bst
118 WHERE bsd.calendar_code = bc.calendar_code
119 AND bst.calendar_code = bsd.calendar_code
120 AND bsd.shift_num = bst.shift_num
121 AND bsd.seq_num is not null
122 AND bc.calendar_code = p_cal_code
123 )
124 WHERE 1= 1
125 AND (
126 (ftime between p_from_date and v_to_time)
127 OR
128 (ttime between p_from_date and v_to_time)
129 )
130 ORDER BY cdate,from_time ;
131
132 CURSOR unavail_c2 IS
133 SELECT resource_units,
134 decode(sign(from_date - p_from_date),-1,p_from_date,from_date) from_time,
135 decode(sign(to_date - v_to_time),-1,to_date,v_to_time) to_time
136 FROM gmp_rsrc_unavail_dtl_v
137 WHERE resource_id = p_resource_id
138 AND
139 (
140 (from_date between p_from_date and v_to_time)
141 OR
142 (to_date between p_from_date and v_to_time)
143 )
144 ORDER BY from_time;
145
146 CURSOR qty_c3 IS
147 SELECT assigned_qty FROM cr_rsrc_dtl
148 WHERE resource_id = p_resource_id;
149
150 c INTEGER := 0;
151 u INTEGER ;
152 u1 INTEGER := 1;
153 x INTEGER := 1;
154 i INTEGER := 1;
155 j INTEGER := 1;
156 tur NUMBER := 0;
157 stmt_no NUMBER := 0;
158
159 BEGIN
160
161 /* New Lines Added for API Standards */
162 IF NOT FND_API.compatible_api_call(gmp_api_version,
163 p_api_version,
164 gmp_api_name,
165 G_PKG_NAME)
166 THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169 --
170 IF FND_API.to_boolean(p_init_msg_list)
171 THEN
172 FND_MSG_PUB.initialize;
173 END IF;
174 --
175 p_flag := 'Y' ;
176
177 IF ((p_from_date is NULL) OR (p_to_date is NULL))
178 THEN
179 p_flag := 'N';
180 raise date_null;
181 END IF;
182 --
183 SELECT to_char(p_to_date,'HH24:MI:SS')
184 INTO temp_date
185 FROM DUAL ;
186 --
187 /* B2992029 - Fix for Gantt Chart query for Resource Availability */
188 IF temp_date = '00:00:00' THEN
189 /* 21-NOV-2003 B3267633 - RESOURCE AVAILABILITY SHOWING AS ZERO IN OPM
190 SCHEDULER WORKBENCH */
191 v_to_time :=
192 to_date((substrb(to_char(p_to_date,'DD/MM/YYYY'),1,11)||' 23:59:59'),
193 'DD/MM/YYYY HH24:MI:SS');
194 ELSE
195 v_to_time := p_to_date ;
196 END IF ;
197 --
198 OPEN qty_c3;
199 FETCH qty_c3 INTO v_assigned_qty;
200 if v_assigned_qty = 0
201 then
202 p_flag := 'N' ;
203 raise qty_null;
204 end if;
205 --
206 CLOSE qty_c3;
207
208 stmt_no := 10;
209 /* If Pl/SQL Tbl cal_rec has any residual rows,
210 we Need to clean before populating the
211 New Table */
212
213 if cal_rec.COUNT > 0
214 then
215 cal_rec.delete;
216 end if;
217 --
218 /* Delete the Unavailable PL/SQL table before start */
219 if unavail_rec.COUNT > 0
220 then
221 unavail_rec.delete;
222 end if;
223 --
224 /* Delete the Out Cal Rec PL/SQL table before start */
225 if p_rec.COUNT > 0
226 then
227 p_rec.delete;
228 end if;
229
230 /* Open the Calendar Cursor */
231 stmt_no := 15;
232 OPEN cal_c1;
233
234 IF cal_c1%NOTFOUND THEN
235 RAISE fnd_api.g_exc_error;
236 END IF;
237
238 loop
239 FETCH cal_c1 INTO calendar_record;
240 EXIT WHEN cal_c1%NOTFOUND;
241
242 cal_rec(i).cal_date := calendar_record.cal_date ;
243 cal_rec(i).shift_num := calendar_record.shift_num ;
244 cal_rec(i).cal_from_date := calendar_record.cal_from_date;
245 cal_rec(i).cal_to_date := calendar_record.cal_to_date;
246 i := i + 1;
247 end loop;
248 CLOSE cal_c1;
249
250 /* OPEN Unavailable Cursor */
251 OPEN unavail_c2;
252
253 IF unavail_c2%NOTFOUND THEN
254 RAISE fnd_api.g_exc_error;
255 END IF;
256
257 loop
258 FETCH unavail_c2 INTO unavail_resource_record;
259 EXIT WHEN unavail_c2%NOTFOUND;
260
261 unavail_rec(x).resource_count :=
262 unavail_resource_record.resource_count;
263 unavail_rec(x).u_from_date :=
264 unavail_resource_record.u_from_date;
265 unavail_rec(x).u_to_date :=
266 unavail_resource_record.u_to_date;
267 x := x + 1;
268 end loop;
269 tur := unavail_rec.COUNT;
270 CLOSE unavail_c2;
271 --
272 if (tur = 0)
273 then
274 /* No exceptions , thus raise the flag so and code at the
275 end of this package will do the necessary inserts */
276 NO_NO_EXCP := 'Y' ;
277 end if;
278 --
279 /* ===================================
280 Brief Logic is as follows
281 Loop through Calendars (cal_rec)
282 For each calendar record
283 Loop through unavailable_time_tbl
284 Insert the resultant into Out Tbl Which is a PL/SQL table
285 end loop;
286 End Loop;
287
288 ================================= */
289 stmt_no := 20;
290
291 FOR c in 1..cal_rec.COUNT
292 LOOP
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294 IF NO_NO_EXCP = 'Y' THEN
295 EXIT ;
296 END IF ;
297 NO_EXCP := 'N';
298
299 FOR u in u1..unavail_rec.COUNT
300 /* { Loop for unavailable */
301 LOOP
302 /* A flag is set if it find that the unavailable resource id
303 is greater than the resource id coming from the resource
304 rec that means this resource has no exception and it can
305 skip all the calculations and gets directly inserted into
306 the ST table - The assumption is that the resource ids
307 will be coming in the same order in the Unvailable Cursor
308 and the resources cursor - This is taken care in both the
309 Cursors */
310
311 /* { ==A==
312 Now check if the Cal date from time is less than Unavailable
313 from time - Here the comparisons are made with both date
314 and Time
315 */
316 IF (cal_rec(c).cal_from_date <= unavail_rec(u).u_from_date)
317 THEN
318
319 /* If the calendar from time is Yes, then check if the
320 Calendar end time is greater than unavailable from time
321 */
322 IF /* { Special 1 */
323 (cal_rec(c).cal_to_date > unavail_rec(u).u_from_date)
324 THEN
325 /* {
326 Check if the Calendar to time is Less than Unavailable
327 to date
328 */
329 IF /* == A 2 and A3 */
330 (cal_rec(c).cal_to_date <= unavail_rec(u).u_to_date)
331 THEN
332 /* shorten the shift (remaining shift is consumed)
333 and insert the record */
334
335 stmt_no := 30;
336 p_rec(j).out_resource_count := v_assigned_qty;
337 p_rec(j).out_shift_num := cal_rec(c).shift_num;
338 p_rec(j).out_cal_date := cal_rec(c).cal_date;
339 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
340 p_rec(j).out_cal_to_date := unavail_rec(u).u_from_date;
341 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
342 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
343 END IF ;
344 j := j + 1;
345 --
346 stmt_no := 31;
347 IF (v_assigned_qty - unavail_rec(u).resource_count > 0 ) THEN
348 p_rec(j).out_resource_count :=
349 v_assigned_qty - unavail_rec(u).resource_count;
350 p_rec(j).out_shift_num := cal_rec(c).shift_num;
351 p_rec(j).out_cal_date := cal_rec(c).cal_date;
352 p_rec(j).out_cal_from_date := unavail_rec(u).u_from_date;
353 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
354 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
355 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
356 END IF ;
357 j := j + 1;
358 END IF ;
359 --
360 /* Store the existing position of unavailable counter
361 This is helpful in looping from the same place where
362 we left from in the unavailable rec
363 */
364 u1 := u ;
365
366 EXIT; /* Exit the Unavailable rec loop and come with
367 the next cal date */
368
369 ELSIF /* == A 1 , if the Cal date to time is greater
370 than Unavailable to time */
371 (cal_rec(c).cal_to_date > unavail_rec(u).u_to_date)
372 THEN
373 /* Break the shift and insert firt record */
374 /* Assign new values to start and end times of
375 the cal_rec shift */
376 /* preserve the counter u into u1 */
377 /* As you continue to loop check resource_id */
378
379 /* Break the shift and insert first record */
380
381 stmt_no := 40;
382 p_rec(j).out_resource_count := v_assigned_qty;
383 p_rec(j).out_shift_num := cal_rec(c).shift_num;
384 p_rec(j).out_cal_date := cal_rec(c).cal_date;
385 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
386 p_rec(j).out_cal_to_date := unavail_rec(u).u_from_date;
390 j := j + 1;
387 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
388 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
389 END IF ;
391 --
392 stmt_no := 41;
393 IF (v_assigned_qty - unavail_rec(u).resource_count > 0 ) THEN
394 p_rec(j).out_resource_count :=
395 v_assigned_qty - unavail_rec(u).resource_count;
396 p_rec(j).out_shift_num := cal_rec(c).shift_num;
397 p_rec(j).out_cal_date := cal_rec(c).cal_date;
398 p_rec(j).out_cal_from_date := unavail_rec(u).u_from_date;
399 p_rec(j).out_cal_to_date := unavail_rec(u).u_to_date;
400 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
401 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
402 END IF ;
403 j := j + 1;
404 END IF ;
405 --
406 /* Assign New Values to the start time of cal_rec */
407 /* !!!!!!!! WATCH THIS !!!!!!! */
408 /* Since we are updating one of the fields in
409 the calendar table, that is the calendar from
410 time, this piece of code is written to help
411 avoid writing the changed value of cal_rec.from_time
412 at all other places for different resource ids
413 */
414 IF update_flag = 'N' THEN
415 update_flag := 'Y' ;
416 temp_from_time := cal_rec(c).cal_from_date;
417 END IF ;
418 cal_rec(c).cal_from_date := unavail_rec(u).u_to_date;
419
420 /* preserve the counter u into u1 */
421 /* u1 := u + 1; */
422 u1 := u ;
423 END IF ; /* } A2 and A3 and A1 also */
424 ELSE /* Else for Special 1 */
425
426 stmt_no := 50;
427 /* Calendar time finishes before the Unavailable Period */
428 p_rec(j).out_resource_count := v_assigned_qty;
429 p_rec(j).out_shift_num := cal_rec(c).shift_num;
430 p_rec(j).out_cal_date := cal_rec(c).cal_date;
431 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
432 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
433 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
434 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
435 END IF ;
436 j := j + 1;
437 --
438 --
439 /* Call the Insert Procedure */
440 u1 := u ;
441 EXIT ;
442 END IF ; /* } For special 1, that is cal from time
443 is less than unavailable from time */
444
445 /* ===== B ===== Special 2 , Cal from time
446 is greater than Unavailable from time */
447
448 ELSIF (unavail_rec(u).u_to_date > cal_rec(c).cal_from_date )
449 THEN
450 /* ===== B1 =====
451 { Calendar End time is greater than Unavailable
452 End time */
453
454 IF (cal_rec(c).cal_to_date > unavail_rec(u).u_to_date)
455 THEN
456 /* Shorten the shift and loop through unavailable
457 records Do NOT write the record yet as there may be
458 another unavaialable record consuming into this
459 shift */
460 /* !!!!!!!! WATCH THIS !!!!!!! */
461 /* Since we are updating one of the fields in
462 the calendar table, that is the calendar from
463 time, this piece of code is written to help
464 avoid writing the changed value of cal_rec.from_time
465 at all other places for different resource ids
466 */
467 IF (v_assigned_qty - unavail_rec(u).resource_count ) > 0
468 THEN
469 p_rec(j).out_resource_count :=
470 v_assigned_qty - unavail_rec(u).resource_count;
471 p_rec(j).out_shift_num := cal_rec(c).shift_num;
472 p_rec(j).out_cal_date := cal_rec(c).cal_date;
473 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
474 p_rec(j).out_cal_to_date := unavail_rec(u).u_to_date;
475 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
476 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
477 END IF ;
478 j := j + 1;
479 END IF ;
480 --
481 IF update_flag = 'N' THEN
482 update_flag := 'Y' ;
483 temp_from_time := cal_rec(c).cal_from_date ;
484 END IF ;
485 cal_rec(c).cal_from_date := unavail_rec(u).u_to_date;
486 --
487 /* continue looping in unavailble loop */
488 ELSIF
489 /* ===== B 2 and 3 ===== */
490 (cal_rec(c).cal_to_date <= unavail_rec(u).u_to_date)
491 THEN
495 -- log_message('Shift is Completely Consumed ');
492 /* The shift is consumed , increase the counters for both
493 the loops */
494 -- p_flag := 'Y';
496 --
497 IF (v_assigned_qty - unavail_rec(u).resource_count ) > 0
498 THEN
499 p_rec(j).out_resource_count :=
500 v_assigned_qty - unavail_rec(u).resource_count;
501 p_rec(j).out_shift_num := cal_rec(c).shift_num;
502 p_rec(j).out_cal_date := cal_rec(c).cal_date;
503 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
504 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
505 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
506 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
507 END IF ;
508 j := j + 1;
509 END IF ;
510 --
511 IF (cal_rec(c).cal_to_date = unavail_rec(u).u_to_date)
512 THEN
513
514 u1 := u + 1;
515 ELSE
516 u1 := u ;
517 END IF ;
518 EXIT ;
519 /* Exits out of the Unavailable loop and increases the
520 Calendar loop count */
521 END IF ; /* } */
522 END IF ; /* } End If for Cal from time , Unavailable from date */
523
524 -- END IF ; /* } resource id matching if */
525
526 /* This is to Set the flag when the counter for Unavailable exceeds
527 the Unavaible rec count
528 */
529 u1 := u ;
530 IF u1 >= unavail_rec.COUNT THEN
531 NO_EXCP := 'Y' ;
532 END IF ;
533
534 END LOOP ; /* } End loop for unavail_rec */
535
536 IF NO_EXCP = 'Y'
537 THEN
538 /* Insert into PL/SQL TABLE while looping through the cal_rec
539 from current position onwards */
540 /* c := c + 1 ; */
541 For i in c..cal_rec.COUNT
542 LOOP
543 stmt_no := 60;
544 p_rec(j).out_resource_count := v_assigned_qty ;
545 p_rec(j).out_shift_num := cal_rec(i).shift_num;
546 p_rec(j).out_cal_date := cal_rec(i).cal_date;
547 p_rec(j).out_cal_from_date := cal_rec(i).cal_from_date;
548 p_rec(j).out_cal_to_date := cal_rec(i).cal_to_date;
549 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
550 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
551 END IF ;
552 -- p_flag := 'N';
553 j := j + 1;
554 --
555 END LOOP ;
556
557 IF update_flag = 'Y'
558 THEN
559 cal_rec(c).cal_from_date := temp_from_time ;
560 update_flag := 'N';
561 END IF ;
562 EXIT ; /* Exit calendar loop so as to go to next rsrc */
563 END IF ; /* End if for EXCP Flag */
564
565 /* Original value of cal_rec.from time is written back here
566 for other resources
567 */
568 IF update_flag = 'Y'
569 THEN
570 cal_rec(c).cal_from_date := temp_from_time ;
571 update_flag := 'N';
572 END IF ;
573 END LOOP; /* End loop for cal_rec i.e. calendar records */
574
575 IF NO_NO_EXCP = 'Y'
576 THEN
577 For i in 1..cal_rec.COUNT
578 LOOP
579 stmt_no := 70;
580 p_rec(j).out_resource_count := v_assigned_qty ;
581 p_rec(j).out_shift_num := cal_rec(i).shift_num;
582 p_rec(j).out_cal_date := cal_rec(i).cal_date;
583 p_rec(j).out_cal_from_date := cal_rec(i).cal_from_date;
584 p_rec(j).out_cal_to_date := cal_rec(i).cal_to_date;
585 IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
586 p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
587 END IF ;
588 j := j + 1;
589 --
590 END LOOP ;
591 NO_NO_EXCP := 'N' ;
592 END IF ;
593
594 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
595 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
596
597
598 EXCEPTION
599 WHEN date_null
600 THEN
601 log_message('!!! Please Enter From and To Date :' );
602
603 WHEN qty_null
604 THEN
605 log_message('Qty is NULL :' );
606
607 WHEN FND_API.G_EXC_ERROR THEN
608 X_return_code := SQLCODE;
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
611
612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
613 X_return_code := SQLCODE;
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
616
617 WHEN OTHERS THEN
618 X_return_code := SQLCODE;
622 /*
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
621
623 WHEN OTHERS
624 THEN
625 log_message('Error in Test rsrc Insert: '||stmt_no);
626 log_message(sqlerrm);
627 */
628
629 END rsrc_avl ; /* End of Procedure rsrc_avl */
630
631 /*
632 REM+=========================================================================+
633 REM| PROCEDURE NAME |
634 REM| rsrc_avl |
635 REM| |
636 REM| TYPE |
637 REM| Public |
638 REM| |
639 REM| DESCRIPTION |
640 REM| This Procedure will find out the Available Time per Resource |
641 REM| HISTROY |
642 REM| Rajesh Patangya created |
643 REM| B4999940 Use of BOM Calendar,Inventory Convergence |
644 REM+=========================================================================+
645 */
646 PROCEDURE rsrc_avl(
647 p_api_version IN NUMBER,
648 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649 p_resource_id IN NUMBER,
650 p_from_date IN DATE,
651 p_to_date IN DATE,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_msg_count OUT NOCOPY NUMBER,
654 x_msg_data OUT NOCOPY VARCHAR2,
655 x_return_code OUT NOCOPY VARCHAR2,
656 p_rec IN OUT NOCOPY cal_tab2,
657 p_flag IN OUT NOCOPY VARCHAR2
658 ) IS
659
660 gmp_api_name VARCHAR2(30) := 'rsrc_avl';
661 gmp_api_version NUMBER := 1.0;
662 invalid_resource_id EXCEPTION ;
663 undetermined_calendar EXCEPTION ;
664
665 CURSOR plant_cur is
666 SELECT organization_id, calendar_code
667 FROM cr_rsrc_dtl
668 WHERE resource_id = p_resource_id
669 AND delete_mark = 0
670 AND inactive_ind = 0 ;
671
672 CURSOR org_calendar_cur is
673 SELECT calendar_code
674 FROM mtl_parameters
675 WHERE organization_id = l_organization_id ;
676
677 BEGIN
678
679 IF NOT FND_API.compatible_api_call(gmp_api_version,
680 p_api_version,
681 gmp_api_name,
682 G_PKG_NAME) THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF;
685
686 IF FND_API.to_Boolean(p_init_msg_list) THEN
687 FND_MSG_PUB.initialize;
688 END IF;
689
690 OPEN plant_cur;
691 FETCH plant_cur INTO l_organization_id, l_calendar_code;
692
693 IF plant_cur%NOTFOUND THEN
694 RAISE fnd_api.g_exc_error;
695 END IF;
696
697 IF plant_cur%ROWCOUNT <>1 THEN
698 p_flag := 'N' ;
699 raise invalid_resource_id ;
700 END IF;
701
702 CLOSE plant_cur;
703
704 IF l_calendar_code IS NULL THEN
705 OPEN org_calendar_cur ;
706 FETCH org_calendar_cur INTO l_calendar_code ;
707
708 IF org_calendar_cur%NOTFOUND THEN
709 RAISE fnd_api.g_exc_error;
710 END IF;
711
712 IF org_calendar_cur%NOTFOUND THEN
713 p_flag := 'N' ;
714 raise undetermined_calendar ;
715 END IF;
716
717 CLOSE org_calendar_cur ;
718 END IF ;
719
720 gmp_rsrc_avl_pkg.rsrc_avl( p_api_version,
721 p_init_msg_list,
722 l_calendar_code,
723 p_resource_id,
724 p_from_date,
725 p_to_date,
726 x_return_status,
727 x_msg_count,
728 x_msg_data,
729 x_return_code,
730 p_rec,p_flag);
731
732 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
733 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
734
735
736 EXCEPTION
737 WHEN undetermined_calendar THEN
738 X_return_code := -100;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
741 log_message('The Calendar is not assigned to resource and organizations ');
742
743 WHEN invalid_resource_id THEN
744 X_return_code := -101;
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
747 log_message('Invalid Resouce Id ');
748
749 WHEN FND_API.G_EXC_ERROR THEN
750 X_return_code := SQLCODE;
751 x_return_status := FND_API.G_RET_STS_ERROR;
752 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
753
754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
755 X_return_code := SQLCODE;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
758
759 WHEN OTHERS THEN
760 X_return_code := SQLCODE;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
763
764 END rsrc_avl ; /* the proc without cal_id */
765
766 END gmp_rsrc_avl_pkg; /* End of package rsrc_avl_pkg */
[Home] [Help]