[Home] [Help]
PACKAGE BODY: APPS.PER_ABS_SHADOW_BOOKING_PKG
Source
1 PACKAGE BODY per_abs_shadow_booking_pkg AS
2 -- $Header: peabsbkg.pkb 120.0 2005/05/31 04:44:57 appldev noship $
3
4 -----------------------------------------------------------------------------
5 --
6 -- Scope: PRIVATE
7 --
8 -- Validate the parameters passed in depending on the mode.
9 --
10 -----------------------------------------------------------------------------
11 FUNCTION validate_params( p_mode IN VARCHAR2
12 , p_person_id IN NUMBER
13 , p_assignment_id IN NUMBER
14 , p_absence_id IN NUMBER
15 , p_start_date IN DATE
16 , p_end_date IN DATE
17 ) RETURN BOOLEAN IS
18
19 l_proc VARCHAR2(50);
20 e_invalid_params EXCEPTION;
21
22 BEGIN
23
24 l_proc := 'per_abs_shadow_booking_pkg.validate_params';
25 hr_utility.set_location('Entering: '|| l_proc, 10);
26
27 IF p_mode IN ('CRE','UPD','DEL','GET') THEN
28 -- Check that either person id or assignment id is supplied
29 IF p_person_id IS NULL AND p_assignment_id IS NULL THEN
30 RAISE e_invalid_params;
31 END IF;
32 END IF;
33
34 hr_utility.set_location(l_proc, 20);
35
36 IF p_mode IN ('CRE','UPD','DEL') THEN
37 -- Check that absence id is supplied
38 IF p_absence_id IS NULL THEN
39 RAISE e_invalid_params;
40 END IF;
41 END IF;
42
43 hr_utility.set_location(l_proc, 30);
44
45 IF p_mode IN ('CRE') THEN
46 -- Check that start date is supplied
47 IF p_start_date IS NULL THEN
48 RAISE e_invalid_params;
49 END IF;
50 END IF;
51
52 hr_utility.set_location('Leaving: '|| l_proc, 40);
53
54 RETURN TRUE;
55
56 EXCEPTION
57
58 WHEN e_invalid_params THEN
59 hr_utility.set_location('Leaving: '|| l_proc, 50);
60 RETURN FALSE;
61
62 WHEN OTHERS THEN
63 hr_utility.set_location('Leaving: '|| l_proc, 60);
64 hr_utility.set_location(SQLERRM, 65);
65 RETURN FALSE;
66
67 END validate_params;
68
69 -----------------------------------------------------------------------------
70 --
71 -- Scope: PRIVATE
72 --
73 -- Get the primary assignment for the given person identifier.
74 --
75 -----------------------------------------------------------------------------
76 FUNCTION get_primary_asg(p_person_id IN NUMBER
77 ,p_start_date IN DATE
78 ,p_end_date IN DATE
79 ) RETURN NUMBER IS
80
81 l_proc VARCHAR2(50);
82 l_assignment_id NUMBER;
83
84 CURSOR c_prim_asg (cp_person_id IN NUMBER
85 ,cp_start_date IN DATE
86 ,cp_end_date IN DATE
87 ) IS
88 SELECT assignment_id
89 FROM per_all_assignments_f
90 WHERE person_id = cp_person_id
91 AND primary_flag = 'Y'
92 AND effective_start_date <= NVL(cp_end_date, SYSDATE)
93 AND effective_end_date >= NVL(cp_start_date, SYSDATE);
94
95 BEGIN
96
97 l_proc := 'per_abs_shadow_booking_pkg.get_primary_asg';
98 hr_utility.set_location('Entering: '|| l_proc, 10);
99
100 OPEN c_prim_asg ( p_person_id
101 , p_start_date
102 , p_end_date
103 );
104 FETCH c_prim_asg INTO l_assignment_id;
105 CLOSE c_prim_asg;
106
107 hr_utility.set_location('PerId:'||p_person_id||
108 ' AsgId:'||l_assignment_id, 20);
109
110 RETURN l_assignment_id;
111
112 EXCEPTION
113
114 WHEN OTHERS THEN
115 hr_utility.set_location('Leaving: '|| l_proc, 30);
116 hr_utility.set_location(SQLERRM, 35);
117 RETURN NULL;
118
119 END get_primary_asg;
120
121 -----------------------------------------------------------------------------
122 --
123 -- Scope: PRIVATE
124 --
125 -- Get the booking type id for the booking type 'General'
126 --
127 -----------------------------------------------------------------------------
128 FUNCTION get_bkg_type_id(p_bkg_type IN VARCHAR2) RETURN NUMBER IS
129
130 l_proc VARCHAR2(50);
131 l_bkg_type_id NUMBER;
132
133 CURSOR c_bkg_type_id (cp_bkg_type IN VARCHAR2) IS
134 SELECT task_type_id
135 FROM jtf_task_types_vl
136 WHERE name = cp_bkg_type;
137
138 BEGIN
139
140 l_proc := 'per_abs_shadow_booking_pkg.get_bkg_type_id';
141 hr_utility.set_location('Entering: '|| l_proc, 10);
142
143 OPEN c_bkg_type_id ( p_bkg_type
144 );
145 FETCH c_bkg_type_id INTO l_bkg_type_id;
146 CLOSE c_bkg_type_id;
147
148 hr_utility.set_location('BkgTypId:'||l_bkg_type_id, 20);
149
150 RETURN l_bkg_type_id;
151
152 EXCEPTION
153
154 WHEN OTHERS THEN
155 hr_utility.set_location('Leaving: '|| l_proc, 30);
156 hr_utility.set_location(SQLERRM, 35);
157 RETURN NULL;
158
159 END get_bkg_type_id;
160
161 -----------------------------------------------------------------------------
162 --
163 -- Scope: PRIVATE
164 --
165 -- Get the booking id for the absence id.
166 --
167 -----------------------------------------------------------------------------
168 PROCEDURE get_bkg_id(p_abs_id IN NUMBER
169 ,p_bkg_id OUT NOCOPY NUMBER
170 ,p_bkg_ovn OUT NOCOPY NUMBER
171 ) IS
172
173 l_proc VARCHAR2(50);
174 l_bkg_id NUMBER;
175 l_bkg_ovn NUMBER;
176
177 CURSOR c_bkg_id (cp_abs_id IN NUMBER) IS
178 SELECT task_type_id
179 ,object_version_number
180 FROM jtf_tasks_vl
181 WHERE source_object_type_code = 'ABSENCE'
182 AND source_object_id = cp_abs_id;
183
184 BEGIN
185
186 l_proc := 'per_abs_shadow_booking_pkg.get_bkg_id';
187 hr_utility.set_location('Entering: '|| l_proc, 10);
188
189 OPEN c_bkg_id ( p_abs_id );
190 FETCH c_bkg_id INTO l_bkg_id
191 ,l_bkg_ovn;
192 CLOSE c_bkg_id;
193
194 hr_utility.set_location('BkgId:'||l_bkg_id||' BkgOVN:'||l_bkg_ovn, 20);
195
196 p_bkg_id := l_bkg_id;
197 p_bkg_ovn := l_bkg_ovn;
198
199 EXCEPTION
200
201 WHEN OTHERS THEN
202 hr_utility.set_location('Leaving: '|| l_proc, 30);
203 hr_utility.set_location(SQLERRM, 35);
204
205 END get_bkg_id;
206
207 -----------------------------------------------------------------------------
208 --
209 -- Scope: PUBLIC
210 --
211 -- Create a shadow booking for the absence record against the primary
212 -- assignment.
213 --
214 -----------------------------------------------------------------------------
215 PROCEDURE create_shadow_booking( p_person_id IN NUMBER DEFAULT NULL
216 , p_assignment_id IN NUMBER DEFAULT NULL
217 , p_absence_id IN NUMBER
218 , p_start_date IN DATE
219 , p_end_date IN DATE DEFAULT NULL
220 , x_booking_id OUT NOCOPY NUMBER
221 , x_return_status OUT NOCOPY NUMBER
222 , x_return_message OUT NOCOPY VARCHAR2
223 ) IS
224
225 l_proc VARCHAR2(50);
226 l_assignment_id NUMBER;
227 lr_new_booking cac_bookings_pub.booking_type;
228 l_return_status VARCHAR2(1);
229 l_msg_count NUMBER;
230 l_msg_data VARCHAR2(2000);
231 e_invalid_params EXCEPTION;
232
233 BEGIN
234
235 l_proc := 'per_abs_shadow_booking_pkg.create_shadow_booking';
236 hr_utility.set_location('Entering: '|| l_proc, 10);
237 x_booking_id := NULL;
238 x_return_status := '0';
239 x_return_message := '';
240
241 IF NOT validate_params('CRE' -- Mode
242 ,p_person_id
243 ,p_assignment_id
244 ,p_absence_id
245 ,p_start_date
246 ,p_end_date
247 ) THEN
248 RAISE e_invalid_params;
249 END IF;
250
251 hr_utility.set_location(l_proc, 20);
252
253 IF p_person_id IS NOT NULL AND p_assignment_id IS NULL THEN
254 l_assignment_id := get_primary_asg( p_person_id
255 , p_start_date
256 , p_end_date
257 );
258 END IF;
259
260 hr_utility.set_location(l_proc, 30);
261
262 -- Setup up the booking record details
263 lr_new_booking.booking_id := NULL;
264 lr_new_booking.resource_type_code := 'PERSON_ASSIGNMENT';
265 IF p_assignment_id IS NULL THEN
266 lr_new_booking.resource_id := l_assignment_id;
267 ELSE
268 lr_new_booking.resource_id := p_assignment_id;
269 END IF;
270 lr_new_booking.start_date := p_start_date;
271 lr_new_booking.end_date := p_end_date;
272 lr_new_booking.booking_type_id := get_bkg_type_id('General');
273 lr_new_booking.booking_status_id := NULL;
274 lr_new_booking.source_object_type_code := 'ABSENCE';
275 lr_new_booking.source_object_id := p_absence_id;
276 lr_new_booking.booking_subject := 'ABSENCE BOOKING';
277 lr_new_booking.freebusytype := 'BUSY';
278
279 hr_utility.set_location(l_proc, 40);
280
281 -- Invoke JTF Bookings API
282 cac_bookings_pub.create_booking
283 ( p_api_version => 1.0
284 , p_init_msg_list => 'T'
285 , p_commit => 'T'
286 , p_booking_rec => lr_new_booking
287 , x_booking_id => x_booking_id
288 , x_return_status => l_return_status
289 , x_msg_count => l_msg_count
290 , x_msg_data => l_msg_data
291 );
292
293 hr_utility.set_location(l_proc, 50);
294
295 CASE l_return_status
296 WHEN 'S' THEN
297 x_return_status := '0';
298 ELSE -- 'E', 'U', unknown
299 x_return_status := '2';
300 x_return_message := l_msg_data;
301 END CASE;
302
303 hr_utility.set_location('Leaving: '|| l_proc, 60);
304
305 EXCEPTION
306
307 WHEN e_invalid_params THEN
308 hr_utility.set_location('Leaving: '|| l_proc, 70);
309 x_return_status := '2';
310
311 WHEN OTHERS THEN
312 hr_utility.set_location('Leaving: '|| l_proc, 80);
313 hr_utility.set_location(SQLERRM, 85);
314 x_return_status := '2';
315 x_return_message := SQLERRM;
316
317 END create_shadow_booking;
318
319 -----------------------------------------------------------------------------
320 --
321 -- Scope: PUBLIC
322 --
323 -- Update an existing shadow booking correcponding to changes to the
324 -- absence record.
325 --
326 -----------------------------------------------------------------------------
327 PROCEDURE update_shadow_booking( p_person_id IN NUMBER DEFAULT NULL
328 , p_assignment_id IN NUMBER DEFAULT NULL
329 , p_absence_id IN NUMBER
330 , p_start_date IN DATE
331 , p_end_date IN DATE
332 , x_return_status OUT NOCOPY NUMBER
333 , x_return_message OUT NOCOPY VARCHAR2
334 ) IS
335
336 l_proc VARCHAR2(50);
337 l_assignment_id NUMBER;
338 l_booking_ovn NUMBER;
339 lr_booking cac_bookings_pub.booking_type;
340 l_return_status VARCHAR2(1);
341 l_msg_count NUMBER;
342 l_msg_data VARCHAR2(2000);
343 e_invalid_params EXCEPTION;
344
345 BEGIN
346
347 l_proc := 'per_abs_shadow_booking_pkg.update_shadow_booking';
348 hr_utility.set_location('Entering: '|| l_proc, 10);
349 x_return_status := '0';
350 x_return_message := '';
351
352 IF NOT validate_params('UPD' -- Mode
353 ,p_person_id
354 ,p_assignment_id
355 ,p_absence_id
356 ,p_start_date
357 ,p_end_date
358 ) THEN
359 RAISE e_invalid_params;
360 END IF;
361
362 hr_utility.set_location(l_proc, 20);
363
364 IF p_person_id IS NOT NULL AND p_assignment_id IS NULL THEN
365 l_assignment_id := get_primary_asg( p_person_id
366 , p_start_date
367 , p_end_date
368 );
369 END IF;
370
371 hr_utility.set_location(l_proc, 30);
372
373 -- Get the booking details
374 get_bkg_id( p_absence_id
375 , lr_booking.booking_id
376 , l_booking_ovn
377 );
378
379 hr_utility.set_location(l_proc, 40);
380
381 -- Setup up the booking record details
382 lr_booking.resource_type_code := 'PERSON_ASSIGNMENT';
383 IF p_assignment_id IS NULL THEN
384 lr_booking.resource_id := l_assignment_id;
385 ELSE
386 lr_booking.resource_id := p_assignment_id;
387 END IF;
388 lr_booking.start_date := p_start_date;
389 lr_booking.end_date := p_end_date;
390 lr_booking.booking_type_id := get_bkg_type_id('General');
391 lr_booking.booking_status_id := NULL;
392 lr_booking.source_object_type_code := 'ABSENCE';
393 lr_booking.source_object_id := p_absence_id;
394 lr_booking.booking_subject := 'ABSENCE BOOKING';
395 lr_booking.freebusytype := 'BUSY';
396
397 hr_utility.set_location(l_proc, 50);
398
399 -- Invoke JTF Bookings API
400 cac_bookings_pub.update_booking
401 ( p_api_version => 1.0
402 , p_init_msg_list => 'T'
403 , p_commit => 'T'
404 , p_booking_rec => lr_booking
405 , p_object_version_number => l_booking_ovn
406 , x_return_status => l_return_status
407 , x_msg_count => l_msg_count
408 , x_msg_data => l_msg_data
409 );
410
411 hr_utility.set_location(l_proc, 60);
412
413 CASE l_return_status
414 WHEN 'S' THEN
415 x_return_status := '0';
416 ELSE -- 'E', 'U', unknown
417 x_return_status := '2';
418 x_return_message := l_msg_data;
419 END CASE;
420
421 hr_utility.set_location('Leaving: '|| l_proc, 70);
422
423 EXCEPTION
424
425 WHEN e_invalid_params THEN
426 hr_utility.set_location('Leaving: '|| l_proc, 80);
427 x_return_status := '2';
428
429 WHEN OTHERS THEN
430 hr_utility.set_location('Leaving: '|| l_proc, 90);
431 hr_utility.set_location(SQLERRM, 95);
432 x_return_status := '2';
433 x_return_message := SQLERRM;
434
435 END update_shadow_booking;
436
437 -----------------------------------------------------------------------------
438 --
439 -- Scope: PUBLIC
440 --
441 -- Delete an existing shadow booking correcponding to delete of the
442 -- absence record.
443 --
444 -----------------------------------------------------------------------------
445 PROCEDURE delete_shadow_booking( p_person_id IN NUMBER DEFAULT NULL
446 , p_assignment_id IN NUMBER DEFAULT NULL
447 , p_absence_id IN NUMBER
448 , x_return_status OUT NOCOPY NUMBER
449 , x_return_message OUT NOCOPY VARCHAR2
450 ) IS
451
452 l_proc VARCHAR2(50);
453 l_assignment_id NUMBER;
454 l_booking_id NUMBER;
455 l_booking_ovn NUMBER;
456 l_return_status VARCHAR2(1);
457 l_msg_count NUMBER;
458 l_msg_data VARCHAR2(2000);
459 e_invalid_params EXCEPTION;
460
461 BEGIN
462
463 l_proc := 'per_abs_shadow_booking_pkg.delete_shadow_booking';
464 hr_utility.set_location('Entering: '|| l_proc, 10);
465 x_return_status := '0';
466 x_return_message := '';
467
468 IF NOT validate_params('DEL' -- Mode
469 ,p_person_id
470 ,p_assignment_id
471 ,p_absence_id
472 ,NULL -- Start Date
473 ,NULL -- End Date
474 ) THEN
475 RAISE e_invalid_params;
476 END IF;
477
478 hr_utility.set_location(l_proc, 20);
479
480 IF p_person_id IS NOT NULL AND p_assignment_id IS NULL THEN
481 l_assignment_id := get_primary_asg( p_person_id
482 , NULL -- start date
483 , NULL -- end date
484 );
485 END IF;
486
487 hr_utility.set_location(l_proc, 30);
488
489 -- Get the booking details
490 get_bkg_id( p_absence_id
491 , l_booking_id
492 , l_booking_ovn
493 );
494
495 hr_utility.set_location(l_proc, 40);
496
497 -- Invoke JTF Bookings API
498 cac_bookings_pub.delete_booking
499 ( p_api_version => 1.0
500 , p_init_msg_list => 'T'
501 , p_commit => 'T'
502 , p_booking_id => l_booking_id
503 , p_object_version_number => l_booking_ovn
504 , x_return_status => l_return_status
505 , x_msg_count => l_msg_count
506 , x_msg_data => l_msg_data
507 );
508
509 hr_utility.set_location(l_proc, 50);
510
511 CASE l_return_status
512 WHEN 'S' THEN
513 x_return_status := '0';
514 ELSE -- 'E', 'U', unknown
515 x_return_status := '2';
516 x_return_message := l_msg_data;
517 END CASE;
518
519 hr_utility.set_location('Leaving: '|| l_proc, 60);
520
521 EXCEPTION
522
523 WHEN e_invalid_params THEN
524 hr_utility.set_location('Leaving: '|| l_proc, 70);
525 x_return_status := '2';
526
527 WHEN OTHERS THEN
528 hr_utility.set_location('Leaving: '|| l_proc, 80);
529 hr_utility.set_location(SQLERRM, 85);
530 x_return_status := '2';
531 x_return_message := SQLERRM;
532
533 END delete_shadow_booking;
534
535 -----------------------------------------------------------------------------
536 --
537 -- Scope: PUBLIC
538 --
539 -- Retrieve absences as shadow bookings for the given person assignment.
540 --
541 -----------------------------------------------------------------------------
542 PROCEDURE get_shadow_booking( p_person_id IN NUMBER DEFAULT NULL
543 , p_assignment_id IN NUMBER DEFAULT NULL
544 , p_start_date IN DATE DEFAULT NULL
545 , p_end_date IN DATE DEFAULT NULL
546 , x_bookings OUT NOCOPY per_abs_booking_varray
547 , x_return_status OUT NOCOPY NUMBER
548 , x_return_message OUT NOCOPY VARCHAR2
549 ) IS
550
551 l_proc VARCHAR2(50);
552 l_booking_obj per_abs_booking_obj;
553 l_return_status VARCHAR2(1);
554 l_msg_count NUMBER;
555 l_msg_data VARCHAR2(2000);
556 e_invalid_params EXCEPTION;
557
558 TYPE cur_type IS REF CURSOR;
559 c_bkgs cur_type;
560
561 BEGIN
562
563 l_proc := 'per_abs_shadow_booking_pkg.get_shadow_booking';
564 hr_utility.set_location('Entering: '|| l_proc, 10);
565 l_booking_obj := per_abs_booking_obj(NULL,NULL,NULL,NULL,NULL,
566 NULL,NULL,NULL,NULL,NULL);
567 x_bookings := per_abs_booking_varray();
568 x_return_status := '0';
569 x_return_message := '';
570
571 IF NOT validate_params('GET' -- Mode
572 ,p_person_id
573 ,p_assignment_id
574 ,NULL -- Absence Id
575 ,p_start_date
576 ,p_end_date
577 ) THEN
578 RAISE e_invalid_params;
579 END IF;
580
581 hr_utility.set_location(l_proc, 20);
582
583 -- Cursor to fetch bookings
584 OPEN c_bkgs FOR ' SELECT T.task_id'||
585 ',T.source_object_id'||
586 ',T.task_name'||
587 ',TA.booking_start_date'||
588 ',TA.booking_end_date'||
589 ',TA.free_busy_type'||
590 ' FROM jtf_tasks_vl T'||
591 ',jtf_task_assignments TA'||
592 ' WHERE T.entity = "BOOKING"'||
593 ' AND T.task_id = TA.task_id'||
594 ' AND T.source_object_type_code = "ABSENCE"'||
595 ' AND TA.resource_type_code = "PERSON_ASSIGNMENT"'||
596 ' AND TA.resource_id = :1'||
597 ' AND TA.booking_start_date <= NVL(:2, TA.booking_start_date)'||
598 ' AND TA.booking_end_date >= NVL(:3, TA.booking_end_date)'
599 USING p_assignment_id
600 ,p_start_date
601 ,p_end_date;
602
603 hr_utility.set_location(l_proc, 30);
604
605 LOOP -- loop for bookings
606 FETCH c_bkgs INTO l_booking_obj.booking_id
607 ,l_booking_obj.absence_id
608 ,l_booking_obj.booking_name
609 ,l_booking_obj.start_date
610 ,l_booking_obj.end_date
611 ,l_booking_obj.free_busy;
612 EXIT WHEN c_bkgs%NOTFOUND;
613
614 hr_utility.set_location('BkgId:'||l_booking_obj.booking_id||
615 ' AbsId:'||l_booking_obj.absence_id, 35);
616
617 -- Save object to array
618 x_bookings.EXTEND(1);
619 x_bookings(x_bookings.COUNT) := l_booking_obj;
620
621 END LOOP; -- loop for bookings
622 CLOSE c_bkgs;
623
624 hr_utility.set_location('Leaving: '|| l_proc, 40);
625
626 EXCEPTION
627
628 WHEN e_invalid_params THEN
629 hr_utility.set_location('Leaving: '|| l_proc, 50);
630 x_return_status := '2';
631
632 WHEN OTHERS THEN
633 hr_utility.set_location('Leaving: '|| l_proc, 60);
634 hr_utility.set_location(SQLERRM, 65);
635 x_return_status := '2';
636 x_return_message := SQLERRM;
637
638 END get_shadow_booking;
639
640 END per_abs_shadow_booking_pkg;