1 PACKAGE BODY gmp_rsrc_avl_pkg as
2 /* $Header: GMPAVLB.pls 120.4.12020000.1 2012/06/27 15:05:52 appldev ship $ */
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
247 -- Bug: 7556621 Vpedarla For shifts going over 12 AM , the to date from bom tables
248 -- is showing the same date. So, Have to added a day to the date keeping the timestamp same
249 IF ( cal_rec(i).cal_to_date < cal_rec(i).cal_from_date ) THEN
250 cal_rec(i).cal_to_date := cal_rec(i).cal_to_date +1 ;
251 END IF;
252 i := i + 1;
253 end loop;
254 CLOSE cal_c1;
255
256 /* OPEN Unavailable Cursor */
257 OPEN unavail_c2;
258
259 IF unavail_c2%NOTFOUND THEN
260 RAISE fnd_api.g_exc_error;
261 END IF;
262
263 loop
264 FETCH unavail_c2 INTO unavail_resource_record;
265 EXIT WHEN unavail_c2%NOTFOUND;
266
267 unavail_rec(x).resource_count :=
268 unavail_resource_record.resource_count;
269 unavail_rec(x).u_from_date :=
270 unavail_resource_record.u_from_date;
271 unavail_rec(x).u_to_date :=
272 unavail_resource_record.u_to_date;
273 x := x + 1;
274 end loop;
275 tur := unavail_rec.COUNT;
276 CLOSE unavail_c2;
277 --
278 if (tur = 0)
279 then
280 /* No exceptions , thus raise the flag so and code at the
281 end of this package will do the necessary inserts */
282 NO_NO_EXCP := 'Y' ;
283 end if;
284 --
285 /* ===================================
286 Brief Logic is as follows
287 Loop through Calendars (cal_rec)
288 For each calendar record
289 Loop through unavailable_time_tbl
290 Insert the resultant into Out Tbl Which is a PL/SQL table
291 end loop;
292 End Loop;
293
294 ================================= */
295 stmt_no := 20;
296
297 FOR c in 1..cal_rec.COUNT
298 LOOP
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300 IF NO_NO_EXCP = 'Y' THEN
301 EXIT ;
302 END IF ;
303 NO_EXCP := 'N';
304
305 FOR u in u1..unavail_rec.COUNT
306 /* { Loop for unavailable */
307 LOOP
308 /* A flag is set if it find that the unavailable resource id
309 is greater than the resource id coming from the resource
310 rec that means this resource has no exception and it can
311 skip all the calculations and gets directly inserted into
312 the ST table - The assumption is that the resource ids
313 will be coming in the same order in the Unvailable Cursor
314 and the resources cursor - This is taken care in both the
315 Cursors */
316
317 /* { ==A==
318 Now check if the Cal date from time is less than Unavailable
319 from time - Here the comparisons are made with both date
320 and Time
321 */
322 IF (cal_rec(c).cal_from_date <= unavail_rec(u).u_from_date)
323 THEN
324
325 /* If the calendar from time is Yes, then check if the
326 Calendar end time is greater than unavailable from time
327 */
328 IF /* { Special 1 */
329 (cal_rec(c).cal_to_date > unavail_rec(u).u_from_date)
330 THEN
331 /* {
332 Check if the Calendar to time is Less than Unavailable
333 to date
334 */
335 IF /* == A 2 and A3 */
336 (cal_rec(c).cal_to_date <= unavail_rec(u).u_to_date)
337 THEN
338 /* shorten the shift (remaining shift is consumed)
339 and insert the record */
340
341 stmt_no := 30;
342 p_rec(j).out_resource_count := v_assigned_qty;
343 p_rec(j).out_shift_num := cal_rec(c).shift_num;
344 p_rec(j).out_cal_date := cal_rec(c).cal_date;
345 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
346 p_rec(j).out_cal_to_date := unavail_rec(u).u_from_date;
347
348 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursor
349 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
350 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
351 --END IF ;
352 j := j + 1;
353 --
354 stmt_no := 31;
355 IF (v_assigned_qty - unavail_rec(u).resource_count > 0 ) THEN
356 p_rec(j).out_resource_count :=
357 v_assigned_qty - unavail_rec(u).resource_count;
358 p_rec(j).out_shift_num := cal_rec(c).shift_num;
359 p_rec(j).out_cal_date := cal_rec(c).cal_date;
360 p_rec(j).out_cal_from_date := unavail_rec(u).u_from_date;
361 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
362
363 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursor
364 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
365 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
366 --END IF ;
367 j := j + 1;
368 END IF ;
369 --
370 /* Store the existing position of unavailable counter
371 This is helpful in looping from the same place where
372 we left from in the unavailable rec
373 */
374 u1 := u ;
375
376 EXIT; /* Exit the Unavailable rec loop and come with
377 the next cal date */
378
379 ELSIF /* == A 1 , if the Cal date to time is greater
380 than Unavailable to time */
381 (cal_rec(c).cal_to_date > unavail_rec(u).u_to_date)
382 THEN
383 /* Break the shift and insert firt record */
384 /* Assign new values to start and end times of
385 the cal_rec shift */
386 /* preserve the counter u into u1 */
387 /* As you continue to loop check resource_id */
388
389 /* Break the shift and insert first record */
390
391 stmt_no := 40;
392 p_rec(j).out_resource_count := v_assigned_qty;
393 p_rec(j).out_shift_num := cal_rec(c).shift_num;
394 p_rec(j).out_cal_date := cal_rec(c).cal_date;
395 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
396 p_rec(j).out_cal_to_date := unavail_rec(u).u_from_date;
397
398 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
399 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
400 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
401 --END IF ;
402 j := j + 1;
403 --
404 stmt_no := 41;
405 IF (v_assigned_qty - unavail_rec(u).resource_count > 0 ) THEN
406 p_rec(j).out_resource_count :=
407 v_assigned_qty - unavail_rec(u).resource_count;
408 p_rec(j).out_shift_num := cal_rec(c).shift_num;
409 p_rec(j).out_cal_date := cal_rec(c).cal_date;
410 p_rec(j).out_cal_from_date := unavail_rec(u).u_from_date;
411 p_rec(j).out_cal_to_date := unavail_rec(u).u_to_date;
412
413 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
414 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
415 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
416 --END IF ;
417 j := j + 1;
418 END IF ;
419 --
420 /* Assign New Values to the start time of cal_rec */
421 /* !!!!!!!! WATCH THIS !!!!!!! */
422 /* Since we are updating one of the fields in
423 the calendar table, that is the calendar from
424 time, this piece of code is written to help
425 avoid writing the changed value of cal_rec.from_time
426 at all other places for different resource ids
427 */
428 IF update_flag = 'N' THEN
429 update_flag := 'Y' ;
430 temp_from_time := cal_rec(c).cal_from_date;
431 END IF ;
432 cal_rec(c).cal_from_date := unavail_rec(u).u_to_date;
433
434 /* preserve the counter u into u1 */
435 /* u1 := u + 1; */
436 u1 := u ;
437 END IF ; /* } A2 and A3 and A1 also */
438 ELSE /* Else for Special 1 */
439
440 stmt_no := 50;
441 /* Calendar time finishes before the Unavailable Period */
442 p_rec(j).out_resource_count := v_assigned_qty;
443 p_rec(j).out_shift_num := cal_rec(c).shift_num;
444 p_rec(j).out_cal_date := cal_rec(c).cal_date;
445 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
446 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
447
448 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
449 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
450 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
451 --END IF ;
452 j := j + 1;
453 --
454 --
455 /* Call the Insert Procedure */
456 u1 := u ;
457 EXIT ;
458 END IF ; /* } For special 1, that is cal from time
459 is less than unavailable from time */
460
461 /* ===== B ===== Special 2 , Cal from time
462 is greater than Unavailable from time */
463
464 ELSIF (unavail_rec(u).u_to_date > cal_rec(c).cal_from_date )
465 THEN
466 /* ===== B1 =====
467 { Calendar End time is greater than Unavailable
468 End time */
469
470 IF (cal_rec(c).cal_to_date > unavail_rec(u).u_to_date)
471 THEN
472 /* Shorten the shift and loop through unavailable
473 records Do NOT write the record yet as there may be
474 another unavaialable record consuming into this
475 shift */
476 /* !!!!!!!! WATCH THIS !!!!!!! */
477 /* Since we are updating one of the fields in
478 the calendar table, that is the calendar from
479 time, this piece of code is written to help
480 avoid writing the changed value of cal_rec.from_time
481 at all other places for different resource ids
482 */
483 IF (v_assigned_qty - unavail_rec(u).resource_count ) > 0
484 THEN
485 p_rec(j).out_resource_count :=
486 v_assigned_qty - unavail_rec(u).resource_count;
487 p_rec(j).out_shift_num := cal_rec(c).shift_num;
488 p_rec(j).out_cal_date := cal_rec(c).cal_date;
489 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
490 p_rec(j).out_cal_to_date := unavail_rec(u).u_to_date;
491
492 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
493 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
494 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
495 --END IF ;
496 j := j + 1;
497 END IF ;
498 --
499 IF update_flag = 'N' THEN
500 update_flag := 'Y' ;
501 temp_from_time := cal_rec(c).cal_from_date ;
502 END IF ;
503 cal_rec(c).cal_from_date := unavail_rec(u).u_to_date;
504 --
505 /* continue looping in unavailble loop */
506 ELSIF
507 /* ===== B 2 and 3 ===== */
508 (cal_rec(c).cal_to_date <= unavail_rec(u).u_to_date)
509 THEN
510 /* The shift is consumed , increase the counters for both
511 the loops */
512 -- p_flag := 'Y';
513 -- log_message('Shift is Completely Consumed ');
514 --
515 IF (v_assigned_qty - unavail_rec(u).resource_count ) > 0
516 THEN
517 p_rec(j).out_resource_count :=
518 v_assigned_qty - unavail_rec(u).resource_count;
519 p_rec(j).out_shift_num := cal_rec(c).shift_num;
520 p_rec(j).out_cal_date := cal_rec(c).cal_date;
521 p_rec(j).out_cal_from_date := cal_rec(c).cal_from_date;
522 p_rec(j).out_cal_to_date := cal_rec(c).cal_to_date;
523
524 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
525 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
526 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
527 --END IF ;
528 j := j + 1;
529 END IF ;
530 --
531 IF (cal_rec(c).cal_to_date = unavail_rec(u).u_to_date)
532 THEN
533 --bug6489270 kbanddyo
534 cal_rec(c).cal_from_date := unavail_rec(u).u_to_date;
535 u1 := u + 1;
536 ELSE
537 u1 := u ;
538 END IF ;
539 EXIT ;
540 /* Exits out of the Unavailable loop and increases the
541 Calendar loop count */
542 END IF ; /* } */
543 END IF ; /* } End If for Cal from time , Unavailable from date */
544
545 -- END IF ; /* } resource id matching if */
546
547 /* This is to Set the flag when the counter for Unavailable exceeds
548 the Unavaible rec count
549 */
550 u1 := u ;
551 IF u1 >= unavail_rec.COUNT THEN
552 NO_EXCP := 'Y' ;
553 END IF ;
554
555 END LOOP ; /* } End loop for unavail_rec */
556
557 --bug6489270 kbanddyo added the following if condition
558 IF u1 > unavail_rec.COUNT THEN
559 NO_EXCP := 'Y' ;
560 END IF ;
561
562 IF NO_EXCP = 'Y'
563 THEN
564 /* Insert into PL/SQL TABLE while looping through the cal_rec
565 from current position onwards */
566 /* c := c + 1 ; */
567 For i in c..cal_rec.COUNT
568 LOOP
569 stmt_no := 60;
570 p_rec(j).out_resource_count := v_assigned_qty ;
571 p_rec(j).out_shift_num := cal_rec(i).shift_num;
572 p_rec(j).out_cal_date := cal_rec(i).cal_date;
573 p_rec(j).out_cal_from_date := cal_rec(i).cal_from_date;
574 p_rec(j).out_cal_to_date := cal_rec(i).cal_to_date;
575
576 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
577 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
578 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
579 --END IF ;
580 -- p_flag := 'N';
581 j := j + 1;
582 --
583 END LOOP ;
584
585 IF update_flag = 'Y'
586 THEN
587 cal_rec(c).cal_from_date := temp_from_time ;
588 update_flag := 'N';
589 END IF ;
590 EXIT ; /* Exit calendar loop so as to go to next rsrc */
591 END IF ; /* End if for EXCP Flag */
592
593 /* Original value of cal_rec.from time is written back here
594 for other resources
595 */
596 IF update_flag = 'Y'
597 THEN
598 cal_rec(c).cal_from_date := temp_from_time ;
599 update_flag := 'N';
600 END IF ;
601 END LOOP; /* End loop for cal_rec i.e. calendar records */
602
603 IF NO_NO_EXCP = 'Y'
604 THEN
605 For i in 1..cal_rec.COUNT
606 LOOP
607 stmt_no := 70;
608 p_rec(j).out_resource_count := v_assigned_qty ;
609 p_rec(j).out_shift_num := cal_rec(i).shift_num;
610 p_rec(j).out_cal_date := cal_rec(i).cal_date;
611 p_rec(j).out_cal_from_date := cal_rec(i).cal_from_date;
612 p_rec(j).out_cal_to_date := cal_rec(i).cal_to_date;
613
614 -- Bug: 7556621 Vpedarla made the below change in the cal_c1 cursore
615 --IF to_char(p_rec(j).out_cal_to_date,'HH24:MI:SS') = '00:00:00' THEN
616 -- p_rec(j).out_cal_to_date := p_rec(j).out_cal_to_date + 1;
617 --END IF ;
618 j := j + 1;
619 --
620 END LOOP ;
621 NO_NO_EXCP := 'N' ;
622 END IF ;
623
624 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
625 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
626
627
628 EXCEPTION
629 WHEN date_null
630 THEN
631 log_message('!!! Please Enter From and To Date :' );
632
633 WHEN qty_null
634 THEN
635 log_message('Qty is NULL :' );
636
637 WHEN FND_API.G_EXC_ERROR THEN
638 X_return_code := SQLCODE;
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
641
642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
643 X_return_code := SQLCODE;
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
646
647 WHEN OTHERS THEN
648 X_return_code := SQLCODE;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
651
652 /*
653 WHEN OTHERS
654 THEN
655 log_message('Error in Test rsrc Insert: '||stmt_no);
656 log_message(sqlerrm);
657 */
658
659 END rsrc_avl ; /* End of Procedure rsrc_avl */
660
661 /*
662 REM+=========================================================================+
663 REM| PROCEDURE NAME |
664 REM| rsrc_avl |
665 REM| |
666 REM| TYPE |
667 REM| Public |
668 REM| |
669 REM| DESCRIPTION |
670 REM| This Procedure will find out the Available Time per Resource |
671 REM| HISTROY |
672 REM| Rajesh Patangya created |
673 REM| B4999940 Use of BOM Calendar,Inventory Convergence |
674 REM+=========================================================================+
675 */
676 PROCEDURE rsrc_avl(
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
679 p_resource_id IN NUMBER,
680 p_from_date IN DATE,
681 p_to_date IN DATE,
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685 x_return_code OUT NOCOPY VARCHAR2,
686 p_rec IN OUT NOCOPY cal_tab2,
687 p_flag IN OUT NOCOPY VARCHAR2
688 ) IS
689
690 gmp_api_name VARCHAR2(30) := 'rsrc_avl';
691 gmp_api_version NUMBER := 1.0;
692 invalid_resource_id EXCEPTION ;
693 undetermined_calendar EXCEPTION ;
694
695 CURSOR plant_cur is
696 SELECT organization_id, calendar_code
697 FROM cr_rsrc_dtl
698 WHERE resource_id = p_resource_id
699 AND delete_mark = 0
700 AND inactive_ind = 0 ;
701
702 CURSOR org_calendar_cur is
703 SELECT calendar_code
704 FROM mtl_parameters
705 WHERE organization_id = l_organization_id ;
706
707 BEGIN
708
709 IF NOT FND_API.compatible_api_call(gmp_api_version,
710 p_api_version,
711 gmp_api_name,
712 G_PKG_NAME) THEN
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714 END IF;
715
716 IF FND_API.to_Boolean(p_init_msg_list) THEN
717 FND_MSG_PUB.initialize;
718 END IF;
719
720 OPEN plant_cur;
721 FETCH plant_cur INTO l_organization_id, l_calendar_code;
722
723 IF plant_cur%NOTFOUND THEN
724 RAISE fnd_api.g_exc_error;
725 END IF;
726
727 IF plant_cur%ROWCOUNT <>1 THEN
728 p_flag := 'N' ;
729 raise invalid_resource_id ;
730 END IF;
731
732 CLOSE plant_cur;
733
734 IF l_calendar_code IS NULL THEN
735 OPEN org_calendar_cur ;
736 FETCH org_calendar_cur INTO l_calendar_code ;
737
738 IF org_calendar_cur%NOTFOUND THEN
739 RAISE fnd_api.g_exc_error;
740 END IF;
741
742 IF org_calendar_cur%NOTFOUND THEN
743 p_flag := 'N' ;
744 raise undetermined_calendar ;
745 END IF;
746
747 CLOSE org_calendar_cur ;
748 END IF ;
749
750 gmp_rsrc_avl_pkg.rsrc_avl( p_api_version,
751 p_init_msg_list,
752 l_calendar_code,
753 p_resource_id,
754 p_from_date,
755 p_to_date,
756 x_return_status,
757 x_msg_count,
758 x_msg_data,
759 x_return_code,
760 p_rec,p_flag);
761
762 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
763 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
764
765
766 EXCEPTION
767 WHEN undetermined_calendar THEN
768 X_return_code := -100;
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
771 log_message('The Calendar is not assigned to resource and organizations ');
772
773 WHEN invalid_resource_id THEN
774 X_return_code := -101;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
777 log_message('Invalid Resouce Id ');
778
779 WHEN FND_API.G_EXC_ERROR THEN
780 X_return_code := SQLCODE;
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
783
784 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
785 X_return_code := SQLCODE;
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
788
789 WHEN OTHERS THEN
790 X_return_code := SQLCODE;
791 x_return_status := FND_API.G_RET_STS_ERROR;
792 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
793
794 END rsrc_avl ; /* the proc without cal_id */
795
796 END gmp_rsrc_avl_pkg; /* End of package rsrc_avl_pkg */
[Home] [Help]