[Home] [Help]
PACKAGE BODY: APPS.PER_GET_ELIG
Source
1 PACKAGE BODY per_get_elig AS
2 -- $Header: perellst.pkb 120.0 2005/05/31 17:34:47 appldev noship $
3
4 -----------------------------------------------------------------------------
5 --
6 -- Scope: PRIVATE
7 --
8 -- Live evaluation version of get_elig_obj_for_per_asg()
9 --
10 -----------------------------------------------------------------------------
11 PROCEDURE get_elig_obj_for_per_asg_live
12 ( p_person_id IN NUMBER
13 , p_assignment_id IN NUMBER DEFAULT NULL
14 , p_effective_date IN DATE
15 , p_table_name IN VARCHAR2
16 , x_eligible_object OUT NOCOPY per_elig_obj_varray
17 , x_return_status OUT NOCOPY NUMBER
18 , x_return_message OUT NOCOPY VARCHAR2
19 ) IS
20
21 l_proc VARCHAR2(50);
22 l_per_elig_obj per_elig_obj;
23
24 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
25 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
26 l_elig_obj_id ben_elig_obj_f.elig_obj_id%TYPE;
27 l_elig_obj_eff_st_dt ben_elig_obj_f.effective_start_date%TYPE;
28 l_elig_obj_eff_en_dt ben_elig_obj_f.effective_end_date%TYPE;
29 l_column_name ben_elig_obj_f.column_name%TYPE;
30 l_column_value ben_elig_obj_f.column_value%TYPE;
31
32 TYPE cur_type IS REF CURSOR;
33 c_per_asg cur_type;
34 c_elig_obj cur_type;
35
36 BEGIN
37 l_proc := 'per_get_elig.get_elig_obj_for_per_asg_live';
38 hr_utility.set_location('Entering: '|| l_proc, 10);
39 l_per_elig_obj := per_elig_obj(NULL,NULL,NULL,NULL,NULL,NULL);
40 x_eligible_object := per_elig_obj_varray(); -- initialize empty
41 x_return_status := '0';
42 x_return_message := '';
43
44 -- Cursor to fetch all the effective assignments for the given person
45 OPEN c_per_asg FOR ' SELECT assignment_id,'||
46 ' business_group_id'||
47 ' FROM per_all_assignments_f'||
48 ' WHERE person_id = :1'||
49 ' AND assignment_id = NVL(:2, assignment_id)'||
50 ' AND effective_start_date <= :3'||
51 ' AND effective_end_date >= :4'
52 USING p_person_id
53 , p_assignment_id
54 , p_effective_date
55 , p_effective_date;
56
57 hr_utility.set_location(l_proc, 20);
58
59 LOOP -- Loop for person assignments
60 FETCH c_per_asg INTO l_assignment_id
61 ,l_business_group_id;
62 EXIT WHEN c_per_asg%NOTFOUND;
63 hr_utility.set_location('PerId '||p_person_id||
64 ' AsgId '||l_assignment_id,25);
65
66 -- Cursor to fetch eligibility objects
67 OPEN c_elig_obj FOR ' SELECT elig_obj_id,'||
68 ' effective_start_date,'||
69 ' effective_end_date,'||
70 ' column_name,'||
71 ' column_value'||
72 ' FROM ben_elig_obj_f'||
73 ' WHERE table_name = :1'||
74 ' AND effective_start_date <= :2'||
75 ' AND effective_end_date >= :3'||
76 ' AND business_group_id = :4'
77 USING p_table_name
78 , p_effective_date
79 , p_effective_date
80 , l_business_group_id;
81
82 hr_utility.set_location(l_proc, 30);
83
84 LOOP -- Loop for eligibility objects
85 FETCH c_elig_obj INTO l_elig_obj_id
86 ,l_elig_obj_eff_st_dt
87 ,l_elig_obj_eff_en_dt
88 ,l_column_name
89 ,l_column_value;
90 EXIT WHEN c_elig_obj%NOTFOUND;
91 hr_utility.set_location('EligObjId '||l_elig_obj_id||
92 ' ColNm '||l_column_name||
93 ' ColVal '||l_column_value,33);
94
95 -- Invoke BEN routine to test eligibility
96 IF ben_per_asg_elig.eligible( p_person_id => p_person_id
97 , p_assignment_id => l_assignment_id
98 , p_elig_obj_id => l_elig_obj_id
99 , p_effective_date => p_effective_date
100 , p_business_group_id => l_business_group_id
101 , p_save_results => FALSE
102 ) THEN
103
104 hr_utility.set_location('Eligible', 36);
105
106 -- Capture details of eligibile object
107 l_per_elig_obj.elig_obj_id := l_elig_obj_id;
108 l_per_elig_obj.tab_name := p_table_name;
109 l_per_elig_obj.col_name := l_column_name;
110 l_per_elig_obj.col_value := l_column_value;
111 l_per_elig_obj.eff_start_date := l_elig_obj_eff_st_dt;
112 l_per_elig_obj.eff_end_date := l_elig_obj_eff_en_dt;
113
114 -- Save eligibile object into array
115 x_eligible_object.EXTEND(1);
116 x_eligible_object(x_eligible_object.COUNT) := l_per_elig_obj;
117 END IF;
118
119 END LOOP; -- Loop for eligibility objects
120 CLOSE c_elig_obj;
121
122 END LOOP; -- Loop for person assignments
123 CLOSE c_per_asg;
124
125 hr_utility.set_location('Leaving: '|| l_proc, 40);
126 EXCEPTION
127
128 WHEN OTHERS THEN
129 hr_utility.set_location('Leaving: '|| l_proc, 50);
130 hr_utility.set_location(SQLERRM, 55);
131 x_return_status := '2';
132 x_return_message := SQLERRM;
133
134 END get_elig_obj_for_per_asg_live;
135
136 -----------------------------------------------------------------------------
137 --
138 -- Scope: PRIVATE
139 --
140 -- Cache data version of get_elig_obj_for_per_asg()
141 --
142 -----------------------------------------------------------------------------
143 PROCEDURE get_elig_obj_for_per_asg_cache
144 ( p_person_id IN NUMBER
145 , p_assignment_id IN NUMBER DEFAULT NULL
146 , p_effective_date IN DATE
147 , p_table_name IN VARCHAR2
148 , x_eligible_object OUT NOCOPY per_elig_obj_varray
149 , x_return_status OUT NOCOPY NUMBER
150 , x_return_message OUT NOCOPY VARCHAR2
151 ) IS
152
153 l_proc VARCHAR2(50);
154 l_per_elig_obj per_elig_obj;
155
156 -- Cursor to fetch all the eligibile objects for the given person
157 CURSOR c_elig_obj ( cp_person_id IN NUMBER
158 , cp_assignment_id IN NUMBER
159 , cp_effective_date IN DATE
160 ) IS
161 SELECT OBJ.elig_obj_id
162 ,OBJ.table_name
163 ,OBJ.column_name
164 ,OBJ.column_value
165 ,OBJ.effective_start_date
166 ,OBJ.effective_end_date
167 FROM ben_elig_rslt_f RSLT
168 ,ben_elig_obj_f OBJ
169 WHERE RSLT.person_id = cp_person_id
170 AND RSLT.assignment_id = NVL(cp_assignment_id, RSLT.assignment_id)
171 AND RSLT.effective_start_date <= cp_effective_date
172 AND RSLT.effective_end_date >= cp_effective_date
173 AND RSLT.elig_flag = 'Y'
174 AND RSLT.elig_obj_id = OBJ.elig_obj_id
175 AND OBJ.effective_start_date <= cp_effective_date
176 AND OBJ.effective_end_date >= cp_effective_date;
177
178 BEGIN
179 l_proc := 'per_get_elig.get_elig_obj_for_per_asg_cache';
180 hr_utility.set_location('Entering: '|| l_proc, 10);
181 l_per_elig_obj := per_elig_obj(NULL,NULL,NULL,NULL,NULL,NULL);
182 x_eligible_object := per_elig_obj_varray(); -- initialize empty
183 x_return_status := '0';
184 x_return_message := '';
185
186 -- Cursor to fetch all the eligibile objects for the given person
187 OPEN c_elig_obj ( p_person_id
188 , p_assignment_id
189 , p_effective_date
190 );
191
192 hr_utility.set_location(l_proc, 20);
193
194 LOOP -- for eligibility objects
195 FETCH c_elig_obj INTO l_per_elig_obj.elig_obj_id
196 ,l_per_elig_obj.tab_name
197 ,l_per_elig_obj.col_name
198 ,l_per_elig_obj.col_value
199 ,l_per_elig_obj.eff_start_date
200 ,l_per_elig_obj.eff_end_date;
201 EXIT WHEN c_elig_obj%NOTFOUND;
202 hr_utility.set_location('EligObjId '||l_per_elig_obj.elig_obj_id, 25);
203
204 -- Save eligibile object into array
205 x_eligible_object.EXTEND(1);
206 x_eligible_object(x_eligible_object.COUNT) := l_per_elig_obj;
207
208 END LOOP; -- for eligibility objects
209 CLOSE c_elig_obj;
210
211 hr_utility.set_location('Leaving: '|| l_proc, 30);
212 EXCEPTION
213
214 WHEN OTHERS THEN
215 hr_utility.set_location('Leaving: '|| l_proc, 40);
216 hr_utility.set_location(SQLERRM, 45);
217 x_return_status := '2';
218 x_return_message := SQLERRM;
219
220 END get_elig_obj_for_per_asg_cache;
221
222 -----------------------------------------------------------------------------
223 --
224 -- Scope: PRIVATE
225 --
226 -- Live evaluation version of get_per_asg_for_elig_obj()
227 --
228 -----------------------------------------------------------------------------
229 PROCEDURE get_per_asg_for_elig_obj_live
230 ( p_table_name IN VARCHAR2
231 , p_column_name IN VARCHAR2
232 , p_column_value IN VARCHAR2
233 , p_effective_date IN DATE
234 , p_business_group_id IN NUMBER
235 , x_person_assignment OUT NOCOPY per_asg_varray
236 , x_return_status OUT NOCOPY NUMBER
237 , x_return_message OUT NOCOPY VARCHAR2
238 ) IS
239
240 l_proc VARCHAR2(50);
241 l_per_asg_obj per_asg_obj;
242
243 l_elig_obj_id ben_elig_obj_f.elig_obj_id%TYPE;
244 l_person_id per_all_people_f.person_id%TYPE;
245 l_party_id per_all_people_f.party_id%TYPE;
246 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
247 l_asg_eff_st_dt per_all_assignments_f.effective_start_date%TYPE;
248 l_asg_eff_en_dt per_all_assignments_f.effective_end_date%TYPE;
249
250 -- Cursor to get the ID of the eligibility object
251 CURSOR c_elig_obj ( cp_table_name IN VARCHAR2
252 , cp_column_name IN VARCHAR2
253 , cp_column_value IN VARCHAR2
254 , cp_business_group_id IN NUMBER
255 , cp_effective_date IN DATE
256 ) IS
257 SELECT elig_obj_id
258 FROM ben_elig_obj_f
259 WHERE table_name = cp_table_name
260 AND column_name = cp_column_name
261 AND column_value = cp_column_value
262 AND business_group_id = cp_business_group_id
263 AND effective_start_date <= cp_effective_date
264 AND effective_end_date >= cp_effective_date;
265
266 TYPE cur_type IS REF CURSOR;
267 c_per cur_type;
268 c_asg cur_type;
269
270 BEGIN
271 l_proc := 'per_get_elig.get_per_asg_for_elig_obj_live';
272 hr_utility.set_location('Entering: '|| l_proc, 10);
273 l_per_asg_obj := per_asg_obj(NULL,NULL,NULL,NULL,NULL);
274 x_person_assignment := per_asg_varray(); -- initialize empty
275 x_return_status := '0';
276 x_return_message := '';
277
278 -- Fetch the ID for the eligibility object
279 OPEN c_elig_obj ( p_table_name
280 , p_column_name
281 , p_column_value
282 , p_business_group_id
283 , p_effective_date
284 );
285 FETCH c_elig_obj INTO l_elig_obj_id;
286 CLOSE c_elig_obj;
287
288 hr_utility.set_location('EligObjId: '||l_elig_obj_id, 20);
289
290 -- Cursor to fetch persons in the business group
291 OPEN c_per FOR ' SELECT person_id,'||
292 ' party_id'||
293 ' FROM per_all_people_f'||
294 ' WHERE business_group_id = :1'||
295 ' AND effective_start_date <= :2'||
296 ' AND effective_end_date >= :3'
297 USING p_business_group_id
298 , p_effective_date
299 , p_effective_date;
300
301 hr_utility.set_location(l_proc, 30);
302
303 LOOP -- Loop for persons in business group
304 FETCH c_per INTO l_person_id
305 ,l_party_id;
306 EXIT WHEN c_per%NOTFOUND;
307 hr_utility.set_location('PersonId '||l_person_id||
308 ' PartyId '||l_party_id,33);
309
310 -- Cursor to fetch assignments for a person
311 OPEN c_asg FOR ' SELECT assignment_id,'||
312 ' effective_start_date,'||
313 ' effective_end_date'||
314 ' FROM per_all_assignments_f'||
315 ' WHERE person_id = :1'||
316 ' AND business_group_id = :2'||
317 ' AND effective_start_date <= :3'||
318 ' AND effective_end_date >= :4'
319 USING l_person_id
320 , p_business_group_id
321 , p_effective_date
322 , p_effective_date;
323
324 hr_utility.set_location(l_proc, 40);
325
326 LOOP -- Loop for assignments for the person
327 FETCH c_asg INTO l_assignment_id
328 ,l_asg_eff_st_dt
329 ,l_asg_eff_en_dt;
330 EXIT WHEN c_asg%NOTFOUND;
331 hr_utility.set_location('AsgId '||l_assignment_id,43);
332
333 -- Invoke BEN routine to test eligibility
334 IF ben_per_asg_elig.eligible( p_person_id => l_person_id
335 , p_assignment_id => l_assignment_id
336 , p_elig_obj_id => l_elig_obj_id
337 , p_effective_date => p_effective_date
338 , p_business_group_id => p_business_group_id
339 , p_save_results => FALSE
340 ) THEN
341
342 hr_utility.set_location('Eligible', 46);
343
344 -- Capture details of eligibile object
345 l_per_asg_obj.person_id := l_person_id;
346 l_per_asg_obj.assignment_id := l_assignment_id;
347 l_per_asg_obj.party_id := l_party_id;
348 l_per_asg_obj.eff_start_date := l_asg_eff_st_dt;
349 l_per_asg_obj.eff_end_date := l_asg_eff_en_dt;
350
351 -- Save eligibile object into array
352 x_person_assignment.EXTEND(1);
353 x_person_assignment(x_person_assignment.COUNT) := l_per_asg_obj;
354 END IF;
355
356 END LOOP; -- Loop for assignments for the person
357 CLOSE c_asg;
358
359 END LOOP; -- Loop for persons in business group
360 CLOSE c_per;
361
362 hr_utility.set_location('Leaving: '|| l_proc, 50);
363 EXCEPTION
364
365 WHEN OTHERS THEN
366 hr_utility.set_location('Leaving: '|| l_proc, 60);
367 hr_utility.set_location(SQLERRM, 65);
368 x_return_status := '2';
369 x_return_message := SQLERRM;
370
371 END get_per_asg_for_elig_obj_live;
372
373 -----------------------------------------------------------------------------
374 --
375 -- Scope: PRIVATE
376 --
377 -- Cache data version of get_per_asg_for_elig_obj()
378 --
379 -----------------------------------------------------------------------------
380 PROCEDURE get_per_asg_for_elig_obj_cache
381 ( p_table_name IN VARCHAR2
382 , p_column_name IN VARCHAR2
383 , p_column_value IN VARCHAR2
384 , p_effective_date IN DATE
385 , p_business_group_id IN NUMBER
386 , x_person_assignment OUT NOCOPY per_asg_varray
387 , x_return_status OUT NOCOPY NUMBER
388 , x_return_message OUT NOCOPY VARCHAR2
389 ) IS
390
391 l_proc VARCHAR2(50);
392 l_per_asg_obj per_asg_obj;
393
394 -- Cursor to fetch all the eligibile persons for the given object
395 CURSOR c_elig_per_asg ( cp_table_name IN VARCHAR2
396 , cp_column_name IN VARCHAR2
397 , cp_column_value IN VARCHAR2
398 , cp_effective_date IN DATE
399 , cp_business_group_id IN NUMBER
400 ) IS
401 SELECT RSLT.person_id
402 ,RSLT.assignment_id
403 FROM ben_elig_rslt_f RSLT
404 ,ben_elig_obj_f OBJ
405 WHERE OBJ.table_name = cp_table_name
406 AND OBJ.column_name = cp_column_name
407 AND OBJ.column_value = cp_column_value
408 AND OBJ.effective_start_date <= cp_effective_date
409 AND OBJ.effective_end_date >= cp_effective_date
410 AND OBJ.elig_obj_id = RSLT.elig_obj_id
411 AND RSLT.effective_start_date <= cp_effective_date
412 AND RSLT.effective_end_date >= cp_effective_date
413 AND RSLT.business_group_id = cp_business_group_id
414 AND RSLT.elig_flag = 'Y';
415
416 -- Cursor to fetch party id
417 CURSOR c_party_id ( cp_person_id IN NUMBER
418 , cp_effective_date IN DATE
419 ) IS
420 SELECT party_id
421 FROM per_all_people_f
422 WHERE person_id = cp_person_id
423 AND effective_start_date <= cp_effective_date
424 AND effective_end_date >= cp_effective_date;
425
426 -- Cursor to fetch assignment effective dates
427 CURSOR c_asg_eff_dates ( cp_person_id IN NUMBER
428 , cp_assignment_id IN NUMBER
429 , cp_effective_date IN DATE
430 ) IS
431 SELECT effective_start_date
432 ,effective_end_date
433 FROM per_all_assignments_f
434 WHERE person_id = cp_person_id
435 AND assignment_id = cp_assignment_id
436 AND effective_start_date <= cp_effective_date
437 AND effective_end_date >= cp_effective_date;
438
439 BEGIN
440 l_proc := 'per_get_elig.get_per_asg_for_elig_obj_cache';
441 hr_utility.set_location('Entering: '|| l_proc, 10);
442 l_per_asg_obj := per_asg_obj(NULL,NULL,NULL,NULL,NULL);
443 x_person_assignment := per_asg_varray(); -- initialize empty
444 x_return_status := '0';
445 x_return_message := '';
446
447 -- Cursor to fetch all the eligibile objects for the given person
448 OPEN c_elig_per_asg ( p_table_name
449 , p_column_name
450 , p_column_value
451 , p_effective_date
452 , p_business_group_id
453 );
454
455 hr_utility.set_location(l_proc, 20);
456
457 LOOP -- for result person assignments
458 FETCH c_elig_per_asg INTO l_per_asg_obj.person_id
459 ,l_per_asg_obj.assignment_id;
460 EXIT WHEN c_elig_per_asg%NOTFOUND;
461 hr_utility.set_location('PerId '||l_per_asg_obj.person_id||
462 ' AsgId '||l_per_asg_obj.assignment_id,23);
463
464 hr_utility.set_location(l_proc, 22);
465
466 -- Get party id
467 OPEN c_party_id (l_per_asg_obj.person_id
468 ,p_effective_date
469 );
470 FETCH c_party_id INTO l_per_asg_obj.party_id;
471 CLOSE c_party_id;
472
473 hr_utility.set_location(l_proc, 24);
474
475 -- Get assignment dates
476 OPEN c_asg_eff_dates (l_per_asg_obj.person_id
477 ,l_per_asg_obj.assignment_id
478 ,p_effective_date
479 );
480 FETCH c_asg_eff_dates INTO l_per_asg_obj.eff_start_date
481 ,l_per_asg_obj.eff_end_date;
482 CLOSE c_asg_eff_dates;
483
484 hr_utility.set_location(l_proc, 26);
485
486 -- Save person assignments into array
487 x_person_assignment.EXTEND(1);
488 x_person_assignment(x_person_assignment.COUNT) := l_per_asg_obj;
489
490 END LOOP; -- for result person assignments
491 CLOSE c_elig_per_asg;
492
493 hr_utility.set_location('Leaving: '|| l_proc, 30);
494 EXCEPTION
495
496 WHEN OTHERS THEN
497 hr_utility.set_location('Leaving: '|| l_proc, 40);
498 hr_utility.set_location(SQLERRM, 45);
499 x_return_status := '2';
500 x_return_message := SQLERRM;
501
502 END get_per_asg_for_elig_obj_cache;
503
504 -----------------------------------------------------------------------------
505 --
506 -- Scope: PUBLIC
507 --
508 -- List the Eligibility Objects that the given Person Assignment is eligible
509 -- for. If the assignment id is supplied, the eligible objects will be for
510 -- that assignment. Else if will be for all assignments.
511 --
512 -----------------------------------------------------------------------------
513 PROCEDURE get_elig_obj_for_per_asg( p_person_id IN NUMBER
514 , p_assignment_id IN NUMBER DEFAULT NULL
515 , p_effective_date IN DATE
516 , p_table_name IN VARCHAR2
517 , p_data_mode IN VARCHAR2 DEFAULT NULL
518 , x_eligible_object OUT NOCOPY per_elig_obj_varray
519 , x_return_status OUT NOCOPY NUMBER
520 , x_return_message OUT NOCOPY VARCHAR2
521 ) IS
522 l_proc VARCHAR2(50);
523 l_data_mode VARCHAR2(1);
524 BEGIN
525 l_proc := 'per_get_elig.get_elig_obj_for_per_asg';
526 hr_utility.set_location('Entering: '|| l_proc, 10);
527
528 CASE
529 WHEN p_data_mode IS NULL THEN
530 l_data_mode := 'L';
531 WHEN p_data_mode = 'C' THEN
532 l_data_mode := 'C';
533 ELSE
534 l_data_mode := 'L';
535 END CASE;
536
537 IF l_data_mode = 'L' THEN
538 hr_utility.set_location(l_proc, 20);
539
540 get_elig_obj_for_per_asg_live ( p_person_id => p_person_id
541 , p_assignment_id => p_assignment_id
542 , p_effective_date => p_effective_date
543 , p_table_name => p_table_name
544 , x_eligible_object => x_eligible_object
545 , x_return_status => x_return_status
546 , x_return_message => x_return_message
547 );
548 ELSE -- data mode is 'C'
549 hr_utility.set_location(l_proc, 30);
550
551 get_elig_obj_for_per_asg_cache ( p_person_id => p_person_id
552 , p_assignment_id => p_assignment_id
553 , p_effective_date => p_effective_date
554 , p_table_name => p_table_name
555 , x_eligible_object => x_eligible_object
556 , x_return_status => x_return_status
557 , x_return_message => x_return_message
558 );
559 END IF; -- data mode check
560
561 hr_utility.set_location('Leaving: '|| l_proc, 40);
562 END get_elig_obj_for_per_asg;
563
564 -----------------------------------------------------------------------------
565 --
566 -- Scope: PUBLIC
567 --
568 -- List the Person Assignments that are eligible for the given
569 -- Eligibility Object.
570 --
571 -----------------------------------------------------------------------------
572 PROCEDURE get_per_asg_for_elig_obj( p_table_name IN VARCHAR2
573 , p_column_name IN VARCHAR2
574 , p_column_value IN VARCHAR2
575 , p_effective_date IN DATE
576 , p_business_group_id IN NUMBER
577 , p_data_mode IN VARCHAR2 DEFAULT NULL
578 , x_person_assignment OUT NOCOPY per_asg_varray
579 , x_return_status OUT NOCOPY NUMBER
580 , x_return_message OUT NOCOPY VARCHAR2
581 ) IS
582 l_proc VARCHAR2(50);
583 l_data_mode VARCHAR2(1);
584 BEGIN
585 l_proc := 'per_get_elig.get_per_asg_for_elig_obj';
586 hr_utility.set_location('Entering: '|| l_proc, 10);
587
588 CASE
589 WHEN p_data_mode IS NULL THEN
590 l_data_mode := 'L';
591 WHEN p_data_mode = 'C' THEN
592 l_data_mode := 'C';
593 ELSE
594 l_data_mode := 'L';
595 END CASE;
596
597 IF l_data_mode = 'L' THEN
598 hr_utility.set_location(l_proc, 20);
599
600 get_per_asg_for_elig_obj_live ( p_table_name => p_table_name
601 , p_column_name => p_column_name
602 , p_column_value => p_column_value
603 , p_effective_date => p_effective_date
604 , p_business_group_id => p_business_group_id
605 , x_person_assignment => x_person_assignment
606 , x_return_status => x_return_status
607 , x_return_message => x_return_message
608 );
609 ELSE -- data mode is 'C'
610 hr_utility.set_location(l_proc, 30);
611
612 get_per_asg_for_elig_obj_cache ( p_table_name => p_table_name
613 , p_column_name => p_column_name
614 , p_column_value => p_column_value
615 , p_effective_date => p_effective_date
616 , p_business_group_id => p_business_group_id
617 , x_person_assignment => x_person_assignment
618 , x_return_status => x_return_status
619 , x_return_message => x_return_message
620 );
621 END IF; -- data mode check
622
623 hr_utility.set_location('Leaving: '|| l_proc, 40);
624 END get_per_asg_for_elig_obj;
625
626 -----------------------------------------------------------------------------
627 --
628 -- Scope: PUBLIC
629 --
630 -- List the Work Schedules that the given Person Assignment
631 -- is eligible for.
632 --
633 -----------------------------------------------------------------------------
634 PROCEDURE get_sch_for_per_asg
635 ( p_person_id IN NUMBER
636 , p_assignment_id IN NUMBER DEFAULT NULL
637 , p_effective_date IN DATE
638 , p_data_mode IN VARCHAR2 DEFAULT NULL
639 , x_schedule OUT NOCOPY per_work_sch_varray
640 , x_return_status OUT NOCOPY NUMBER
641 , x_return_message OUT NOCOPY VARCHAR2
642 ) IS
643
644 l_proc VARCHAR2(50);
645 l_elig_obj_varray per_elig_obj_varray;
646 l_elig_obj per_elig_obj;
647 l_work_sch_obj per_work_sch_obj;
648
649 l_schedule_name cac_sr_schedules_vl.schedule_name%TYPE;
650 l_schedule_category cac_sr_schedules_vl.schedule_category%TYPE;
651
652 -- Cursor to fetch schedule details
653 CURSOR c_sch ( cp_schedule_id IN NUMBER
654 , cp_start_date IN DATE
655 , cp_end_date IN DATE
656 ) IS
657 SELECT schedule_category
658 ,schedule_name
659 FROM cac_sr_schedules_vl
660 WHERE schedule_id = cp_schedule_id
661 AND start_date_active = cp_start_date
662 AND end_date_active = cp_end_date;
663
664 BEGIN
665 l_proc := 'per_get_elig.get_sch_for_per_asg';
666 hr_utility.set_location('Entering: '|| l_proc, 10);
667 l_elig_obj_varray := per_elig_obj_varray();
668 l_elig_obj := per_elig_obj(NULL,NULL,NULL,NULL,NULL,NULL);
669 l_work_sch_obj := per_work_sch_obj(NULL,NULL,NULL,NULL,NULL);
670 x_schedule := per_work_sch_varray(); -- initialize empty
671 x_return_status := '0';
672 x_return_message := '';
673
674 -- Get eligible objects for the given person assignments
675 get_elig_obj_for_per_asg( p_person_id => p_person_id
676 , p_assignment_id => p_assignment_id
677 , p_effective_date => p_effective_date
678 , p_table_name => 'CAC_SR_SCHEDULES_VL'
679 , p_data_mode => p_data_mode
680 , x_eligible_object => l_elig_obj_varray
681 , x_return_status => x_return_status
682 , x_return_message => x_return_message
683 );
684
685 hr_utility.set_location(l_proc, 20);
686
687 -- Translate all the eligibility objects to work schedules
688 FOR i IN l_elig_obj_varray.FIRST..l_elig_obj_varray.LAST LOOP
689 l_elig_obj := l_elig_obj_varray(i);
690
691 hr_utility.set_location('EligObjId: '||l_elig_obj.elig_obj_id||' ColVal: '||l_elig_obj.col_value, 23);
692
693 -- Get the schedule details
694 OPEN c_sch ( l_elig_obj.col_value
695 , l_elig_obj.eff_start_date
696 , l_elig_obj.eff_end_date
697 );
698 FETCH c_sch INTO l_work_sch_obj.schedule_category
699 ,l_work_sch_obj.schedule_name;
700 CLOSE c_sch;
701
702 hr_utility.set_location('SchCat: '||l_work_sch_obj.schedule_category||' SchName: '||l_work_sch_obj.schedule_name, 26);
703
704 -- Save schedule details
705 l_work_sch_obj.schedule_id := l_elig_obj.col_value;
706 l_work_sch_obj.start_date_active := l_elig_obj.eff_start_date;
707 l_work_sch_obj.end_date_active := l_elig_obj.eff_end_date;
708 x_schedule.EXTEND(1);
709 x_schedule(x_schedule.COUNT) := l_work_sch_obj;
710
711 hr_utility.set_location(l_proc, 29);
712 END LOOP;
713
714 hr_utility.set_location('Leaving: '|| l_proc, 30);
715 EXCEPTION
716
717 WHEN OTHERS THEN
718 hr_utility.set_location('Leaving: '|| l_proc, 40);
719 hr_utility.set_location(SQLERRM, 45);
720 x_return_status := '2';
721 x_return_message := SQLERRM;
722
723 END get_sch_for_per_asg;
724
725 -----------------------------------------------------------------------------
726 --
727 -- Scope: PUBLIC
728 --
729 -- List the Person Assignment that are eligible for the given
730 -- Schedule.
731 --
732 -----------------------------------------------------------------------------
733 PROCEDURE get_per_asg_for_sch
734 ( p_schedule_category IN VARCHAR2
735 , p_schedule_name IN VARCHAR2
736 , p_effective_date IN DATE
737 , p_business_group_id IN NUMBER
738 , p_data_mode IN VARCHAR2 DEFAULT NULL
739 , x_person_assignment OUT NOCOPY per_asg_varray
740 , x_return_status OUT NOCOPY NUMBER
741 , x_return_message OUT NOCOPY VARCHAR2
742 ) IS
743
744 l_proc VARCHAR2(50);
745 l_schedule_id cac_sr_schedules_vl.schedule_id%TYPE;
746
747 -- Cursor to fetch schedule identifier
748 CURSOR c_sch ( cp_schedule_category IN VARCHAR2
749 , cp_schedule_name IN VARCHAR2
750 , cp_effective_date IN DATE
751 ) IS
752 SELECT schedule_id
753 FROM cac_sr_schedules_vl
754 WHERE schedule_category = cp_schedule_category
755 AND schedule_name = cp_schedule_name
756 AND start_date_active <= cp_effective_date
757 AND end_date_active >= cp_effective_date;
758
759 BEGIN
760 l_proc := 'per_get_elig.get_per_asg_for_sch';
761 hr_utility.set_location('Entering: '|| l_proc, 10);
762 x_person_assignment := per_asg_varray(); -- initialize empty
763 x_return_status := '0';
764 x_return_message := '';
765
766 -- Get the schedule identifier
767 OPEN c_sch ( p_schedule_category
768 , p_schedule_name
769 , p_effective_date
770 );
771 FETCH c_sch INTO l_schedule_id;
772 CLOSE c_sch;
773
774 hr_utility.set_location('SchId: '||l_schedule_id, 20);
775
776 -- Get the person assignments
777 get_per_asg_for_elig_obj( p_table_name => 'CAC_SR_SCHEDULES_VL'
778 , p_column_name => 'SCHEDULE_ID'
779 , p_column_value => l_schedule_id
780 , p_effective_date => p_effective_date
781 , p_business_group_id => p_business_group_id
782 , p_data_mode => p_data_mode
783 , x_person_assignment => x_person_assignment
784 , x_return_status => x_return_status
785 , x_return_message => x_return_message
786 );
787
788 hr_utility.set_location('Leaving: '|| l_proc, 30);
789 EXCEPTION
790
791 WHEN OTHERS THEN
792 hr_utility.set_location('Leaving: '|| l_proc, 40);
793 hr_utility.set_location(SQLERRM, 45);
794 x_return_status := '2';
795 x_return_message := SQLERRM;
796
797 END get_per_asg_for_sch;
798
799 END per_get_elig;