[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_EMP_ELIG
Source
1 PACKAGE BODY ben_cwb_emp_elig as
2 /* $Header: bencwbee.pkb 120.6 2008/05/26 12:50:01 sgnanama noship $ */
3 g_package varchar2(60) := 'ben_cwb_emp_elig' ;
4 g_post_process_exception exception;
5
6 FUNCTION get_person_name(p_group_per_in_ler_id IN NUMBER)
7 RETURN VARCHAR2
8 IS
9 CURSOR wsm(v_group_per_in_ler_id in number) IS
10 SELECT info.full_name
11 FROM ben_cwb_person_info info
12 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
13 l_person_name varchar2(2000);
14 BEGIN
15 OPEN wsm(p_group_per_in_ler_id);
16 FETCH wsm INTO l_person_name;
17 CLOSE wsm;
18
19 RETURN l_person_name;
20 END;
21
22 /*******************************************************************************************/
23
24 FUNCTION get_for_period(p_group_per_in_ler_id IN NUMBER)
25 RETURN VARCHAR2
26 IS
27 CURSOR c11
28 IS
29 SELECT nvl(dsgn.wthn_yr_start_dt,dsgn.yr_perd_start_dt)||' - '||
30 nvl(dsgn.wthn_yr_end_dt,dsgn.yr_perd_end_dt) forPeriod
31 FROM ben_per_in_ler pil,
32 ben_cwb_pl_dsgn dsgn
33 WHERE pil.per_in_ler_id = p_group_per_in_ler_id
34 AND pil.group_pl_id = dsgn.group_pl_id
35 AND pil.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
36 AND dsgn.oipl_id = -1
37 AND dsgn.group_pl_id = dsgn.pl_id
38 AND dsgn.group_oipl_id = dsgn.oipl_id;
39 l_info c11%ROWTYPE;
40 BEGIN
41 OPEN c11;
42 FETCH c11 INTO l_info;
43 CLOSE c11;
44
45 RETURN l_info.forPeriod;
46 END;
47
48 /*******************************************************************************************/
49 PROCEDURE isCompManagerRole
50 (
51 p_person_id IN NUMBER
52 ,retValue OUT NOCOPY VARCHAR2
53 )
54 IS
55 cursor c1(c_role_id in number) is
56 SELECT pei.person_id person_id, ppf.full_name person_name ,
57 usr.user_name user_name, usr.user_id user_id
58 FROM per_people_extra_info pei , per_all_people_f ppf ,
59 fnd_user usr , pqh_roles rls
60 WHERE information_type = 'PQH_ROLE_USERS' and
61 pei.person_id = ppf.person_id
62 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date and ppf.effective_end_date
63 and usr.employee_id = ppf.person_id
64 and rls.role_id = to_number(pei.pei_information3)
65 and nvl(pei.pei_information5,'Y')='Y'
66 and rls.role_id = c_role_id;
67 cursor c2 is
68 select role_id
69 from pqh_roles
70 where role_type_cd ='CWB';
71 l_proc varchar2(80) := g_package||'.isCompManagerRole';
72 BEGIN
73 hr_utility.set_location ('Entering '||l_proc,15);
74 retValue := 'N';
75 FOR rl in c2
76 LOOP
77 FOR person_in_role IN c1(rl.role_id)
78 LOOP
79 IF (person_in_role.person_id = p_person_id ) THEN
80 retValue := 'Y';
81 END IF;
82 END LOOP;
83 END LOOP;
84 hr_utility.set_location ('Leaving '||l_proc,20);
85 exception
86 when others then
87 retValue := null;
88 raise;
89 END;
90 /*******************************************************************************************/
91
92 /*******************************************************************************************/
93 PROCEDURE updateEligibility
94 (
95 p_group_per_in_ler_id NUMBER
96 ,p_pl_pl_id NUMBER
97 ,p_pl_oipl_id NUMBER
98 ,p_pl_elig_flag VARCHAR2
99 ,p_pl_elig_change_status VARCHAR2
100 ,p_pl_person_rate_id NUMBER
101 ,p_o1_pl_id NUMBER
102 ,p_o1_oipl_id NUMBER
103 ,p_o1_elig_flag VARCHAR2
104 ,p_o1_elig_change_status VARCHAR2
105 ,p_o1_person_rate_id NUMBER default null
106 ,p_o2_pl_id NUMBER
107 ,p_o2_oipl_id NUMBER
108 ,p_o2_elig_flag VARCHAR2
109 ,p_o2_elig_change_status VARCHAR2
110 ,p_o2_person_rate_id NUMBER
111 ,p_o3_pl_id NUMBER
112 ,p_o3_oipl_id NUMBER
113 ,p_o3_elig_flag VARCHAR2
114 ,p_o3_elig_change_status VARCHAR2
115 ,p_o3_person_rate_id NUMBER
116 ,p_o4_pl_id NUMBER
117 ,p_o4_oipl_id NUMBER
118 ,p_o4_elig_flag VARCHAR2
119 ,p_o4_elig_change_status VARCHAR2
120 ,p_o4_person_rate_id NUMBER
121 ,p_elig_ovrid_person_id NUMBER
122 ,p_elig_ovrid_dt DATE
123 )IS
124 l_inelig_rsn_cd VARCHAR2(4) := NULL;
125 l_object_version_number NUMBER;
126 l_ws_val NUMBER;
127 l_elig_flag VARCHAR2(1);
128 l_ws_start_date DATE := hr_api.g_date;
129 l_proc VARCHAR2(80) := g_package||'.updateEligibility';
130
131 CURSOR c_person_rate(v_person_rate_id IN VARCHAR2) IS
132 SELECT object_version_number
133 ,ws_val
134 ,elig_flag
135 ,ws_rt_start_date
136 FROM ben_cwb_person_rates
137 WHERE person_rate_id = v_person_rate_id;
138
139 BEGIN
140 hr_utility.set_location ('Entering '||l_proc,40);
141
142 IF p_o1_elig_change_status = 'O1Y' THEN
143
144 IF p_o1_elig_flag = 'N' THEN
145 l_ws_val := NULL;
146 l_inelig_rsn_cd := 'MGR';
147 l_ws_start_date := NULL;
148 ELSE
149 l_inelig_rsn_cd := NULL;
150 l_ws_start_date := hr_api.g_date;
151 END IF;
152
153 IF p_pl_elig_change_status = 'PY' THEN
154 IF p_pl_elig_flag = 'Y' THEN
155 OPEN c_person_rate(p_o1_person_rate_id);
156 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
157 CLOSE c_person_rate;
158 ben_cwb_person_rates_api.update_person_rate
159 (
160 p_group_per_in_ler_id => p_group_per_in_ler_id
161 ,p_pl_id => p_o1_pl_id
162 ,p_oipl_id => p_o1_oipl_id
163 ,p_elig_flag => p_o1_elig_flag
164 ,p_ws_val => l_ws_val
165 ,p_inelig_rsn_cd => l_inelig_rsn_cd
166 ,p_elig_ovrid_dt => p_elig_ovrid_dt
167 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
168 ,p_object_version_number => l_object_version_number
169 ,p_ws_rt_start_date => l_ws_start_date
170 );
171 END IF;
172 ELSE
173 OPEN c_person_rate(p_o1_person_rate_id);
174 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
175 CLOSE c_person_rate;
176 IF p_o1_elig_flag = 'N' THEN
177 l_ws_val := NULL;
178 l_inelig_rsn_cd := 'MGR';
179 l_ws_start_date := NULL;
180 ELSE
181 l_inelig_rsn_cd := NULL;
182 l_ws_start_date := hr_api.g_date;
183 END IF;
184 ben_cwb_person_rates_api.update_person_rate
185 (
186 p_group_per_in_ler_id => p_group_per_in_ler_id
187 ,p_pl_id => p_o1_pl_id
188 ,p_oipl_id => p_o1_oipl_id
189 ,p_elig_flag => p_o1_elig_flag
190 ,p_ws_val => l_ws_val
191 ,p_inelig_rsn_cd => l_inelig_rsn_cd
192 ,p_elig_ovrid_dt => p_elig_ovrid_dt
193 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
194 ,p_object_version_number => l_object_version_number
195 ,p_ws_rt_start_date => l_ws_start_date
196 );
197
198 OPEN c_person_rate(p_pl_person_rate_id);
199 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
200 CLOSE c_person_rate;
201 IF l_elig_flag = 'N' AND p_o1_elig_flag = 'Y' THEN
202 ben_cwb_person_rates_api.update_person_rate
203 (
204 p_group_per_in_ler_id => p_group_per_in_ler_id
205 ,p_pl_id => p_pl_pl_id
206 ,p_oipl_id => p_pl_oipl_id
207 ,p_elig_flag => 'Y'
208 ,p_elig_ovrid_dt => p_elig_ovrid_dt
209 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
210 ,p_object_version_number => l_object_version_number
211 );
212 END IF;
213 END IF;
214 END IF;
215
216 IF p_o2_elig_change_status = 'O2Y' THEN
217
218 IF p_o2_elig_flag = 'N' THEN
219 l_ws_val := NULL;
220 l_inelig_rsn_cd := 'MGR';
221 l_ws_start_date := NULL;
222 ELSE
223 l_inelig_rsn_cd := NULL;
224 l_ws_start_date := hr_api.g_date;
225 END IF;
226
227 IF p_pl_elig_change_status = 'PY' THEN
228 IF p_pl_elig_flag = 'Y' THEN
229 OPEN c_person_rate(p_o2_person_rate_id);
230 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
231 CLOSE c_person_rate;
232 ben_cwb_person_rates_api.update_person_rate
233 (
234 p_group_per_in_ler_id => p_group_per_in_ler_id
235 ,p_pl_id => p_o2_pl_id
236 ,p_oipl_id => p_o2_oipl_id
237 ,p_elig_flag => p_o2_elig_flag
238 ,p_ws_val => l_ws_val
239 ,p_inelig_rsn_cd => l_inelig_rsn_cd
240 ,p_elig_ovrid_dt => p_elig_ovrid_dt
241 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
242 ,p_object_version_number => l_object_version_number
243 ,p_ws_rt_start_date => l_ws_start_date
244 );
245 END IF;
246 ELSE
247 OPEN c_person_rate(p_o2_person_rate_id);
248 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
249 CLOSE c_person_rate;
250 IF p_o2_elig_flag = 'N' THEN
251 l_ws_val := NULL;
252 l_inelig_rsn_cd := 'MGR';
253 l_ws_start_date := NULL;
254 ELSE
255 l_inelig_rsn_cd := NULL;
256 l_ws_start_date := hr_api.g_date;
257 END IF;
258 ben_cwb_person_rates_api.update_person_rate
259 (
260 p_group_per_in_ler_id => p_group_per_in_ler_id
261 ,p_pl_id => p_o2_pl_id
262 ,p_oipl_id => p_o2_oipl_id
263 ,p_elig_flag => p_o2_elig_flag
264 ,p_ws_val => l_ws_val
265 ,p_inelig_rsn_cd => l_inelig_rsn_cd
266 ,p_elig_ovrid_dt => p_elig_ovrid_dt
267 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
268 ,p_object_version_number => l_object_version_number
269 ,p_ws_rt_start_date => l_ws_start_date
270 );
271
272 OPEN c_person_rate(p_pl_person_rate_id);
273 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
274 CLOSE c_person_rate;
275 IF l_elig_flag = 'N' AND p_o2_elig_flag = 'Y' THEN
276 ben_cwb_person_rates_api.update_person_rate
277 (
278 p_group_per_in_ler_id => p_group_per_in_ler_id
279 ,p_pl_id => p_pl_pl_id
280 ,p_oipl_id => p_pl_oipl_id
281 ,p_elig_flag => 'Y'
282 ,p_elig_ovrid_dt => p_elig_ovrid_dt
283 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
284 ,p_object_version_number => l_object_version_number
285 );
286 END IF;
287 END IF;
288 END IF;
289
290 IF p_o3_elig_change_status = 'O3Y' THEN
291
292 IF p_o3_elig_flag = 'N' THEN
293 l_ws_val := NULL;
294 l_inelig_rsn_cd := 'MGR';
295 l_ws_start_date := NULL;
296 ELSE
297 l_inelig_rsn_cd := NULL;
298 l_ws_start_date := hr_api.g_date;
299 END IF;
300
301 IF p_pl_elig_change_status = 'PY' THEN
302 IF p_pl_elig_flag = 'Y' THEN
303 OPEN c_person_rate(p_o3_person_rate_id);
304 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
305 CLOSE c_person_rate;
306 ben_cwb_person_rates_api.update_person_rate
307 (
308 p_group_per_in_ler_id => p_group_per_in_ler_id
309 ,p_pl_id => p_o3_pl_id
310 ,p_oipl_id => p_o3_oipl_id
311 ,p_elig_flag => p_o3_elig_flag
312 ,p_ws_val => l_ws_val
313 ,p_inelig_rsn_cd => l_inelig_rsn_cd
314 ,p_elig_ovrid_dt => p_elig_ovrid_dt
315 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
316 ,p_object_version_number => l_object_version_number
317 ,p_ws_rt_start_date => l_ws_start_date
318 );
319 END IF;
320 ELSE
321 OPEN c_person_rate(p_o3_person_rate_id);
322 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
323 CLOSE c_person_rate;
324 IF p_o3_elig_flag = 'N' THEN
325 l_ws_val := NULL;
326 l_inelig_rsn_cd := 'MGR';
327 l_ws_start_date := NULL;
328 ELSE
329 l_inelig_rsn_cd := NULL;
330 l_ws_start_date := hr_api.g_date;
331 END IF;
332 ben_cwb_person_rates_api.update_person_rate
333 (
334 p_group_per_in_ler_id => p_group_per_in_ler_id
335 ,p_pl_id => p_o3_pl_id
336 ,p_oipl_id => p_o3_oipl_id
337 ,p_elig_flag => p_o3_elig_flag
338 ,p_ws_val => l_ws_val
339 ,p_inelig_rsn_cd => l_inelig_rsn_cd
340 ,p_elig_ovrid_dt => p_elig_ovrid_dt
341 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
342 ,p_object_version_number => l_object_version_number
343 ,p_ws_rt_start_date => l_ws_start_date
344 );
345
346 OPEN c_person_rate(p_pl_person_rate_id);
347 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
348 CLOSE c_person_rate;
349 IF l_elig_flag = 'N' AND p_o3_elig_flag = 'Y' THEN
350 ben_cwb_person_rates_api.update_person_rate
351 (
352 p_group_per_in_ler_id => p_group_per_in_ler_id
353 ,p_pl_id => p_pl_pl_id
354 ,p_oipl_id => p_pl_oipl_id
355 ,p_elig_flag => 'Y'
356 ,p_elig_ovrid_dt => p_elig_ovrid_dt
357 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
358 ,p_object_version_number => l_object_version_number
359 );
360 END IF;
361 END IF;
362 END IF;
363
364 IF p_o4_elig_change_status = 'O4Y' THEN
365
366 IF p_o4_elig_flag = 'N' THEN
367 l_ws_val := NULL;
368 l_inelig_rsn_cd := 'MGR';
369 l_ws_start_date := NULL;
370 ELSE
371 l_inelig_rsn_cd := NULL;
372 l_ws_start_date := hr_api.g_date;
373 END IF;
374
375 IF p_pl_elig_change_status = 'PY' THEN
376 IF p_pl_elig_flag = 'Y' THEN
377 OPEN c_person_rate(p_o4_person_rate_id);
378 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
379 CLOSE c_person_rate;
380 ben_cwb_person_rates_api.update_person_rate
381 (
382 p_group_per_in_ler_id => p_group_per_in_ler_id
383 ,p_pl_id => p_o4_pl_id
384 ,p_oipl_id => p_o4_oipl_id
385 ,p_elig_flag => p_o4_elig_flag
386 ,p_ws_val => l_ws_val
387 ,p_inelig_rsn_cd => l_inelig_rsn_cd
388 ,p_elig_ovrid_dt => p_elig_ovrid_dt
389 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
390 ,p_object_version_number => l_object_version_number
391 ,p_ws_rt_start_date => l_ws_start_date
392 );
393 END IF;
394 ELSE
395 OPEN c_person_rate(p_o4_person_rate_id);
396 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
397 CLOSE c_person_rate;
398 IF p_o4_elig_flag = 'N' THEN
399 l_ws_val := NULL;
400 l_inelig_rsn_cd := 'MGR';
401 l_ws_start_date := NULL;
402 ELSE
403 l_inelig_rsn_cd := NULL;
404 l_ws_start_date := hr_api.g_date;
405 END IF;
406 ben_cwb_person_rates_api.update_person_rate
407 (
408 p_group_per_in_ler_id => p_group_per_in_ler_id
409 ,p_pl_id => p_o4_pl_id
410 ,p_oipl_id => p_o4_oipl_id
411 ,p_elig_flag => p_o4_elig_flag
412 ,p_ws_val => l_ws_val
413 ,p_inelig_rsn_cd => l_inelig_rsn_cd
414 ,p_elig_ovrid_dt => p_elig_ovrid_dt
415 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
416 ,p_object_version_number => l_object_version_number
417 ,p_ws_rt_start_date => l_ws_start_date
418 );
419
420 OPEN c_person_rate(p_pl_person_rate_id);
421 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
422 CLOSE c_person_rate;
423 IF l_elig_flag = 'N' AND p_o4_elig_flag = 'Y' THEN
424 ben_cwb_person_rates_api.update_person_rate
425 (
426 p_group_per_in_ler_id => p_group_per_in_ler_id
427 ,p_pl_id => p_pl_pl_id
428 ,p_oipl_id => p_pl_oipl_id
429 ,p_elig_flag => 'Y'
430 ,p_elig_ovrid_dt => p_elig_ovrid_dt
431 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
432 ,p_object_version_number => l_object_version_number
433 );
434 END IF;
435 END IF;
436 END IF;
437
438
439 IF p_pl_elig_change_status = 'PY' THEN
440
441 OPEN c_person_rate(p_pl_person_rate_id);
442 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
443 CLOSE c_person_rate;
444
445 IF p_pl_elig_flag = 'N' THEN
446 l_ws_val := NULL;
447 l_inelig_rsn_cd := 'MGR';
448 l_ws_start_date := NULL;
449 ELSE
450 l_inelig_rsn_cd := NULL;
451 l_ws_start_date := hr_api.g_date;
452 -- Need to call the routine which deletes all assignment changes
453 -- as the person becomes ineligible.
454 END IF;
455
456 ben_cwb_person_rates_api.update_person_rate
457 (
458 p_group_per_in_ler_id => p_group_per_in_ler_id
459 ,p_pl_id => p_pl_pl_id
460 ,p_oipl_id => p_pl_oipl_id
461 ,p_elig_flag => p_pl_elig_flag
462 ,p_ws_val => l_ws_val
463 ,p_inelig_rsn_cd => l_inelig_rsn_cd
464 ,p_elig_ovrid_dt => p_elig_ovrid_dt
465 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
466 ,p_object_version_number => l_object_version_number
467 ,p_ws_rt_start_date => l_ws_start_date
468 );
469
470
471 l_object_version_number := NULL;
472 l_ws_val := NULL;
473 l_inelig_rsn_cd := NULL;
474
475 IF p_pl_elig_flag = 'N' THEN
476
477 IF p_o1_oipl_id IS NOT NULL THEN
478 OPEN c_person_rate(p_o1_person_rate_id);
479 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
480 CLOSE c_person_rate;
481 ben_cwb_person_rates_api.update_person_rate
482 (
483 p_group_per_in_ler_id => p_group_per_in_ler_id
484 ,p_pl_id => p_o1_pl_id
485 ,p_oipl_id => p_o1_oipl_id
486 ,p_elig_flag => 'N'
487 ,p_ws_val => null
488 ,p_inelig_rsn_cd => 'MGR'
489 ,p_elig_ovrid_dt => p_elig_ovrid_dt
490 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
491 ,p_object_version_number => l_object_version_number
492 ,p_ws_rt_start_date => null
493 );
494 END IF;
495
496 l_object_version_number := NULL;
497 l_ws_val := NULL;
498 l_inelig_rsn_cd := NULL;
499
500 IF p_o2_oipl_id IS NOT NULL THEN
501 OPEN c_person_rate(p_o2_person_rate_id);
502 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
503 CLOSE c_person_rate;
504 ben_cwb_person_rates_api.update_person_rate
505 (
506 p_group_per_in_ler_id => p_group_per_in_ler_id
507 ,p_pl_id => p_o2_pl_id
508 ,p_oipl_id => p_o2_oipl_id
509 ,p_elig_flag => 'N'
510 ,p_ws_val => null
511 ,p_inelig_rsn_cd => 'MGR'
512 ,p_elig_ovrid_dt => p_elig_ovrid_dt
513 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
514 ,p_object_version_number => l_object_version_number
515 ,p_ws_rt_start_date => null
516 );
517 END IF;
518
519 l_object_version_number := NULL;
520 l_ws_val := NULL;
521 l_inelig_rsn_cd := NULL;
522
523 IF p_o3_oipl_id IS NOT NULL THEN
524 OPEN c_person_rate(p_o3_person_rate_id);
525 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
526 CLOSE c_person_rate;
527 ben_cwb_person_rates_api.update_person_rate
528 (
529 p_group_per_in_ler_id => p_group_per_in_ler_id
530 ,p_pl_id => p_o3_pl_id
531 ,p_oipl_id => p_o3_oipl_id
532 ,p_elig_flag => 'N'
533 ,p_ws_val => null
534 ,p_inelig_rsn_cd => 'MGR'
535 ,p_elig_ovrid_dt => p_elig_ovrid_dt
536 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
537 ,p_object_version_number => l_object_version_number
538 ,p_ws_rt_start_date => null
539 );
540 END IF;
541
542 l_object_version_number := NULL;
543 l_ws_val := NULL;
544 l_inelig_rsn_cd := NULL;
545
546 IF p_o4_oipl_id IS NOT NULL THEN
547 OPEN c_person_rate(p_o4_person_rate_id);
548 FETCH c_person_rate INTO l_object_version_number, l_ws_val,l_elig_flag,l_ws_start_date;
549 CLOSE c_person_rate;
550 ben_cwb_person_rates_api.update_person_rate
551 (
552 p_group_per_in_ler_id => p_group_per_in_ler_id
553 ,p_pl_id => p_o4_pl_id
554 ,p_oipl_id => p_o4_oipl_id
555 ,p_elig_flag => 'N'
556 ,p_ws_val => null
557 ,p_inelig_rsn_cd => 'MGR'
558 ,p_elig_ovrid_dt => p_elig_ovrid_dt
559 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
560 ,p_object_version_number => l_object_version_number
561 ,p_ws_rt_start_date => null
562 );
563 END IF;
564
565 END IF;
566
567 END IF;
568
569 hr_utility.set_location ('Leaving '||l_proc,45);
570 END;
571 /*******************************************************************************************/
572
573 /*******************************************************************************************/
574 PROCEDURE cwb_emp_elig_start_process(
575 p_plan_name in varchar2 default null
576 , p_requestor_name in varchar2 default null
577 , p_worksheet_manger in varchar2 default null
578 , p_relationship_id in number
579 , p_group_per_in_ler_id in number
580 )
581 is
582 l_proc varchar2(61) := g_package||':'||'cwb_emp_elig_start_process';
583 l_itemkey number := p_relationship_id;
584 l_itemtype varchar2(60) := 'BENCWBFY';
585 l_process_name varchar2(60) := 'CWB_EMP_ELIG';
586 l_process_name_c varchar2(60);
587 Begin
588
589 -- hr_utility.trace_on (null, 'ORACLE');
590
591 hr_utility.set_location ('Entering '||l_proc,50);
592
593 hr_utility.set_location ('Seeded Elig Process Name'||l_process_name ,55);
594
595 hr_utility.set_location ('Profile :: '|| fnd_profile.value('BEN_CWB_EMP_ELIG_W_PROCESS') ,55);
596
597 l_process_name := nvl(fnd_profile.value('BEN_CWB_EMP_ELIG_W_PROCESS'),'CWB_EMP_ELIG');
598
599 hr_utility.set_location ('Elig Process Name After reading profile'||l_process_name ,55);
600
601 wf_engine.createProcess(ItemType => l_itemtype,
602 ItemKey => l_itemkey,
603 process => l_process_name );
604
605 wf_engine.SetItemAttrText(itemtype => l_itemtype
606 , itemkey => l_itemkey
607 , aname => 'PLAN_NAME'
608 , avalue => p_plan_name);
609 wf_engine.SetItemAttrText(itemtype => l_itemtype
610 , itemkey => l_itemkey
611 , aname => 'REQUESTOR_NAME'
612 , avalue => p_requestor_name);
613 wf_engine.SetItemAttrText(itemtype => l_itemtype
614 , itemkey => l_itemkey
615 , aname => 'WORKSHEET_MANAGER'
616 , avalue => p_worksheet_manger);
617 wf_engine.SetItemAttrText(itemtype => l_itemtype
618 , itemkey => l_itemkey
619 , aname => 'MANAGER_NAME'
620 , avalue => get_person_name(p_group_per_in_ler_id));
621 wf_engine.SetItemAttrText(itemtype => l_itemtype
622 , itemkey => l_itemkey
623 , aname => 'FROM_ROLE'
624 , avalue => p_requestor_name);
625 wf_engine.SetItemAttrText(itemtype => l_itemtype
626 , itemkey => l_itemkey
627 , aname => 'TRANSACTION_ID'
628 , avalue => p_relationship_id);
629 wf_engine.SetItemAttrText(itemtype => l_itemtype
630 , itemkey => l_itemkey
631 , aname => 'FOR_PERIOD'
632 , avalue => get_for_period (p_group_per_in_ler_id));
633 wf_engine.SetItemOwner(itemtype => l_itemtype
634 , itemkey => l_itemkey
635 , owner => p_requestor_name);
636 wf_engine.StartProcess ( ItemType => l_itemtype,
637 ItemKey => l_ItemKey );
638 hr_utility.set_location ('Leaving '||l_proc,55);
639 exception
640 when others then
641 hr_utility.set_location ('Error occured cwb_emp_elig_start_process',60);
642 END;
643 /*******************************************************************************************/
644
645 /*******************************************************************************************/
646 PROCEDURE cwb_emp_elig_appr_api
647 ( p_ws_person_id in number default null
648 , p_rcvr_person_id in number default null
649 , p_plan_name in varchar2
650 , p_relationship_id in number
651 , p_group_per_in_ler_id in number
652 )
653 IS
654 l_proc varchar2(61) := g_package||':'||'cwb_emp_elig_appr_api';
655 l_itemkey number;
656 l_itemtype varchar2(60) := 'BENCWBFY';
657 l_process_name varchar2(240) := 'CWB_EMP_ELIG';
658 l_rcvr_user_name varchar2(240);
659 l_ws_user_name varchar2(240);
660 l_start_process varchar2(10) := 'N';
661 cursor c1 is select user_name from fnd_user
662 where employee_id = p_rcvr_person_id;
663 cursor c2 is select user_name from fnd_user
664 where employee_id = p_ws_person_id;
665 BEGIN
666 hr_utility.set_location ('Entering '||l_proc,85);
667 if p_rcvr_person_id is null then
668 hr_utility.set_location ('receiver person id to be passed ',90);
669 else
670 open c1;
671 fetch c1 into l_rcvr_user_name;
672 if c1%notfound then
673 hr_utility.set_location ('fnd user does not exist'||p_rcvr_person_id,95);
674 else
675 l_start_process := 'Y' ;
676 end if;
677 close c1;
678 end if;
679 if p_ws_person_id is null then
680 hr_utility.set_location ('wroksheet manager person id to be passed ',95);
681 else
682 open c2;
683 fetch c2 into l_ws_user_name;
684 if c2%notfound then
685 l_start_process := 'N';
686 else
687 l_start_process := 'Y' ;
688 end if;
689 close c2;
690 end if;
691 if l_start_process = 'Y' then
692 cwb_emp_elig_start_process
693 ( p_plan_name => p_plan_name
694 , p_requestor_name => l_rcvr_user_name
695 , p_worksheet_manger => l_ws_user_name
696 , p_relationship_id => p_relationship_id
697 , p_group_per_in_ler_id => p_group_per_in_ler_id
698 );
699 end if;
700 hr_utility.set_location ('Leaving '||l_proc,100);
701 exception
702 when others then
703 hr_utility.set_location ('Error occured cwb_emp_elig_appr_api',105);
704 END;
705 /*******************************************************************************************/
706
707 /*******************************************************************************************/
708 PROCEDURE select_next_approver
709 ( itemtype IN VARCHAR2
710 , itemkey IN VARCHAR2
711 , actid IN NUMBER
712 , funcmode IN VARCHAR2
713 , result OUT NOCOPY VARCHAR2
714 )
715 IS
716 cursor approver_name(c_approver_id IN NUMBER) is select user_name from fnd_user
717 where employee_id = c_approver_id;
718 l_proc varchar2(61) := g_package||':'||'select_next_approver';
719 l_is_ame_used VARCHAR2(240) := 'Y';
720 c_next_approver_out ame_util.approverRecord;
721 l_approver_name VARCHAR2(240);
722 BEGIN
723 hr_utility.set_location ('Entering '||l_proc,110);
724 ame_api.getnextapprover
725 (
726 applicationIdIn =>805,
727 transactionIdIn =>itemkey,
728 transactionTypeIn =>'EMPELIG',
729 nextApproverOut =>c_next_approver_out);
730 IF(c_next_approver_out.person_id is not null) THEN
731 OPEN approver_name(c_next_approver_out.person_id);
732 FETCH approver_name INTO l_approver_name;
733 IF approver_name%NOTFOUND then
734 result := 'COMPLETE:' ||'APPROVER_NOT_FOUND';
735 ELSE
736 wf_engine.SetItemAttrText(itemtype => itemtype
737 , itemkey => itemkey
738 , aname => 'APPROVER_NAME'
739 , avalue => l_approver_name );
740 update ben_transaction
741 set attribute36 = l_approver_name,
742 attribute38 = to_char(sysdate,'yyyy/mm/dd'),
743 attribute35 = c_next_approver_out.person_id
744 where attribute1 = itemkey
745 and transaction_type = 'EMPELIGEMP';
746
747 wf_engine.SetItemAttrNumber(itemtype => itemtype
748 , itemkey => itemkey
749 , aname => 'APPROVER_ID'
750 , avalue => c_next_approver_out.person_id );
751 result := 'COMPLETE:' ||'CWB_APPROVER_FOUND';
752 END IF;
753 close approver_name;
754 ELSE
755 result := 'COMPLETE:' ||'APPROVER_NOT_FOUND';
756 END IF;
757 hr_utility.set_location ('Leaving '||l_proc,115);
758 exception
759 when others then
760 wf_engine.SetItemAttrText(itemtype => itemtype
761 , itemkey => itemkey
762 , aname => 'ERROR_OCCURED_AT'
763 , avalue => 'Select Next Approver Node');
764 wf_engine.SetItemAttrText(itemtype => itemtype
765 , itemkey => itemkey
766 , aname => 'ERROR_MESSAGE'
767 , avalue => 'Unable to get the next approver or failed to update the transaction table');
768 wf_engine.SetItemAttrText(itemtype => itemtype
769 , itemkey => itemkey
770 , aname => 'ERROR_SQLERRM'
771 , avalue => SQLERRM);
772 wf_engine.SetItemAttrText(itemtype => itemtype
773 , itemkey => itemkey
774 , aname => 'ERROR_SQLCODE'
775 , avalue => SQLCODE);
776 result := 'COMPLETE:'||'EMP_ELIG_ERROR';
777 END;
778 /******************************************************************************************/
779
780 /*******************************************************************************************/
781 PROCEDURE store_transaction
782 ( itemtype IN VARCHAR2
783 , itemkey IN VARCHAR2
784 , actid IN NUMBER
785 , funcmode IN VARCHAR2
786 , result OUT NOCOPY VARCHAR2
787 )
788 IS
789 l_proc varchar2(61) := g_package||':'||'store_transaction';
790 l_approver_name VARCHAR2(240);
791 l_approver_id NUMBER;
792 l_comments VARCHAR(2000);
793 l_notification_id NUMBER;
794 l_itemkey VARCHAR(240) := itemkey;
795 l_itemtype VARCHAR(240) := itemtype;
796 cursor employeeId (c_approver_name IN VARCHAR2) is select employee_id from fnd_user
797 where user_name = l_approver_name;
798 BEGIN
799 hr_utility.set_location ('Entering '||l_proc,135);
800 l_approver_name :=wf_engine.getitemattrtext(itemtype => itemtype ,
801 itemkey => itemkey,
802 aname => 'APPROVER_NAME');
803 l_approver_id :=wf_engine.getitemattrNumber(itemtype => itemtype ,
804 itemkey => itemkey,
805 aname => 'APPROVER_ID');
806 IF(l_approver_name is not null) THEN
807 OPEN employeeId(l_approver_name);
808 FETCH employeeId INTO l_approver_id;
809 IF employeeId%NOTFOUND then
810 hr_utility.set_location ('Was not able to get the approver name in store transaction',145);
811 ELSE
812 ame_api.updateApprovalStatus2(applicationIdIn => 805,
813 transactionIdIn => itemkey,
814 approvalStatusIn => ame_util.approvedStatus,
815 approverPersonIdIn => l_approver_id,
816 transactionTypeIn => 'EMPELIG');
817 END IF;
818 close employeeId;
819 wf_engine.SetItemAttrText(itemtype => l_itemtype
820 , itemkey => l_itemkey
821 , aname => 'FROM_ROLE'
822 , avalue => l_approver_name);
823 END IF;
824 result := 'COMPLETE:'||'EMP_ELIG_SUCCESS' ;
825 hr_utility.set_location ('Leaving '||l_proc,10);
826 exception
827 when others then
828 wf_engine.SetItemAttrText(itemtype => itemtype
829 , itemkey => itemkey
830 , aname => 'ERROR_OCCURED_AT'
831 , avalue => 'Store transaction Node');
832 wf_engine.SetItemAttrText(itemtype => itemtype
833 , itemkey => itemkey
834 , aname => 'ERROR_MESSAGE'
835 , avalue => 'Unable to update ame approver status');
836 wf_engine.SetItemAttrText(itemtype => itemtype
837 , itemkey => itemkey
838 , aname => 'ERROR_SQLERRM'
839 , avalue => SQLERRM);
840 wf_engine.SetItemAttrText(itemtype => itemtype
841 , itemkey => itemkey
842 , aname => 'ERROR_SQLCODE'
843 , avalue => SQLCODE);
844 result := 'COMPLETE:'||'EMP_ELIG_ERROR';
845 END;
846 /*******************************************************************************************/
847
848 /*******************************************************************************************/
849 PROCEDURE store_rejection
850 ( itemtype IN VARCHAR2
851 , itemkey IN VARCHAR2
852 , actid IN NUMBER
853 , funcmode IN VARCHAR2
854 , result OUT NOCOPY VARCHAR2
855 )
856 IS
857 l_proc varchar2(61) := g_package||':'||'store_rejection';
858 l_approver_id NUMBER;
859 l_approver_name VARCHAR2(1000);
860 l_itemkey VARCHAR(240) := itemkey;
861 l_itemtype VARCHAR(240) := itemtype;
862 BEGIN
863 hr_utility.set_location ('Entering '||l_proc,150);
864 l_approver_id :=wf_engine.getitemattrNumber(itemtype => itemtype ,
865 itemkey => itemkey,
866 aname => 'APPROVER_ID');
867 l_approver_name :=wf_engine.getitemattrtext(itemtype => itemtype ,
868 itemkey => itemkey,
869 aname => 'APPROVER_NAME');
870 wf_engine.SetItemAttrText(itemtype => l_itemtype
871 , itemkey => l_itemkey
872 , aname => 'FROM_ROLE'
873 , avalue => l_approver_name);
874 result := 'COMPLETE:';
875 hr_utility.set_location ('Leaving '||l_proc,155);
876 exception
877 when others then
878 result := null;
879 END;
880 /*******************************************************************************************/
881
882 /*******************************************************************************************/
883 PROCEDURE store_approval
884 ( itemtype IN VARCHAR2
885 , itemkey IN VARCHAR2
886 , actid IN NUMBER
887 , funcmode IN VARCHAR2
888 , result OUT NOCOPY VARCHAR2
889 )
890 IS
891 l_proc varchar2(61) := g_package||':'||'store_approval';
892 l_approver_id NUMBER;
893 l_comments VARCHAR2(500);
894 l_errmsg VARCHAR2(500) := 'Unable to update the choice records after approval';
895 cursor upd_emp_elig is
896 select tran_tbl.attribute2 group_per_in_ler_id
897 -- Plan Level transaction values
898 ,tran_tbl.attribute18 pl_pl_id
899 ,tran_tbl.attribute19 pl_oipl_id
900 ,tran_tbl.attribute7 pl_status
901 ,tran_tbl.attribute12 pl_change_status
902 ,tran_tbl.attribute17 pl_person_rate_id
903 -- Option1 Level transaction values
904 ,tran_tbl.attribute21 o1_pl_id
905 ,tran_tbl.attribute22 o1_oipl_id
906 ,tran_tbl.attribute8 o1_status
907 ,tran_tbl.attribute13 o1_change_status
908 ,tran_tbl.attribute20 o1_person_rate_id
909 -- Option2 Level transaction values
910 ,tran_tbl.attribute24 o2_pl_id
911 ,tran_tbl.attribute25 o2_oipl_id
912 ,tran_tbl.attribute9 o2_status
913 ,tran_tbl.attribute14 o2_change_status
914 ,tran_tbl.attribute23 o2_person_rate_id
915 -- Option3 Level transaction values
916 ,tran_tbl.attribute27 o3_pl_id
917 ,tran_tbl.attribute28 o3_oipl_id
918 ,tran_tbl.attribute10 o3_status
919 ,tran_tbl.attribute15 o3_change_status
920 ,tran_tbl.attribute26 o3_person_rate_id
921 -- Option4 Level transaction values
922 ,tran_tbl.attribute30 o4_pl_id
923 ,tran_tbl.attribute31 o4_oipl_id
924 ,tran_tbl.attribute11 o4_status
925 ,tran_tbl.attribute16 o4_change_status
926 ,tran_tbl.attribute29 o4_person_rate_id
927 ,tran_tbl.attribute37 ovrd_person_id
928 ,pil.per_in_ler_stat_cd ler_stat
929 from ben_transaction tran_tbl,
930 ben_per_in_ler pil
931 where tran_tbl.attribute1 = itemkey
932 and tran_tbl.transaction_type = 'EMPELIGEMP'
933 and to_number(tran_tbl.attribute2) = pil.per_in_ler_id;
934 BEGIN
935 hr_utility.set_location ('Entering '||l_proc,160);
936
937 ben_cwb_summary_pkg.delete_pl_sql_tab;
938
939 for emp_elig in upd_emp_elig
940 loop
941 If emp_elig.ler_stat = 'STRTD' THEN
942 updateEligibility
943 (
944 p_group_per_in_ler_id => to_number(emp_elig.group_per_in_ler_id)
945 -- Plan Level Parameters
946 ,p_pl_pl_id => to_number(emp_elig.pl_pl_id)
947 ,p_pl_oipl_id => to_number(emp_elig.pl_oipl_id)
948 ,p_pl_elig_flag => emp_elig.pl_status
949 ,p_pl_elig_change_status => emp_elig.pl_change_status
950 ,p_pl_person_rate_id => to_number(emp_elig.pl_person_rate_id)
951 -- Option1 Level Parameters
952 ,p_o1_pl_id => to_number(emp_elig.o1_pl_id)
953 ,p_o1_oipl_id => to_number(emp_elig.o1_oipl_id)
954 ,p_o1_elig_flag => emp_elig.o1_status
955 ,p_o1_elig_change_status => emp_elig.o1_change_status
956 ,p_o1_person_rate_id => to_number(emp_elig.o1_person_rate_id)
957 -- Option2 Level Parameters
958 ,p_o2_pl_id => to_number(emp_elig.o2_pl_id)
959 ,p_o2_oipl_id => to_number(emp_elig.o2_oipl_id)
960 ,p_o2_elig_flag => emp_elig.o2_status
961 ,p_o2_elig_change_status => emp_elig.o2_change_status
962 ,p_o2_person_rate_id => to_number(emp_elig.o2_person_rate_id)
963 -- Option3 Level Parameters
964 ,p_o3_pl_id => to_number(emp_elig.o3_pl_id)
965 ,p_o3_oipl_id => to_number(emp_elig.o3_oipl_id)
966 ,p_o3_elig_flag => emp_elig.o3_status
967 ,p_o3_elig_change_status => emp_elig.o3_change_status
968 ,p_o3_person_rate_id => to_number(emp_elig.o3_person_rate_id)
969 -- Option4 Level Parameters
970 ,p_o4_pl_id => to_number(emp_elig.o4_pl_id)
971 ,p_o4_oipl_id => to_number(emp_elig.o4_oipl_id)
972 ,p_o4_elig_flag => emp_elig.o4_status
973 ,p_o4_elig_change_status => emp_elig.o4_change_status
974 ,p_o4_person_rate_id => to_number(emp_elig.o4_person_rate_id)
975
976 ,p_elig_ovrid_person_id => to_number(emp_elig.ovrd_person_id)
977 ,p_elig_ovrid_dt => sysdate
978 );
979 ELSE
980 l_errmsg := 'Unable to update the choice record as it does not have a started life event';
981 raise g_post_process_exception;
982 END IF;
983 end loop;
984
985 ben_cwb_summary_pkg.save_pl_sql_tab;
986
987 result := 'COMPLETE:'||'EMP_ELIG_SUCCESS' ;
988 hr_utility.set_location ('Leaving '||l_proc,165);
989 exception
990 when others then
991 wf_engine.SetItemAttrText( itemtype => itemtype
992 ,itemkey => itemkey
993 ,aname => 'ERROR_OCCURED_AT'
994 ,avalue => 'Store approval Node');
995 wf_engine.SetItemAttrText(itemtype => itemtype
996 ,itemkey => itemkey
997 ,aname => 'ERROR_MESSAGE'
998 ,avalue => l_errmsg);
999 wf_engine.SetItemAttrText(itemtype => itemtype
1000 ,itemkey => itemkey
1001 ,aname => 'ERROR_SQLERRM'
1002 ,avalue => SQLERRM);
1003 wf_engine.SetItemAttrText(itemtype => itemtype
1004 ,itemkey => itemkey
1005 ,aname => 'ERROR_SQLCODE'
1006 ,avalue => SQLCODE);
1007 result := 'COMPLETE:'||'EMP_ELIG_ERROR';
1008 END;
1009 /*******************************************************************************************/
1010
1011 /*******************************************************************************************/
1012 PROCEDURE is_req_wsmgr_same
1013 ( itemtype IN VARCHAR2
1014 , itemkey IN VARCHAR2
1015 , actid IN NUMBER
1016 , funcmode IN VARCHAR2
1017 , result OUT NOCOPY VARCHAR2
1018 )
1019 IS
1020 l_proc varchar2(61) := g_package||':'||'is_req_wsmgr_same';
1021 l_worksheet_manager VARCHAR2(240);
1022 l_requestor VARCHAR2(240);
1023 BEGIN
1024 hr_utility.set_location ('Entering '||l_proc,170);
1025 l_worksheet_manager :=wf_engine.getitemattrText(itemtype => itemtype ,
1026 itemkey => itemkey,
1027 aname => 'WORKSHEET_MANAGER');
1028 l_requestor :=wf_engine.getitemattrText(itemtype => itemtype ,
1029 itemkey => itemkey,
1030 aname => 'REQUESTOR_NAME');
1031 IF(l_worksheet_manager = l_requestor) THEN
1032 result := 'COMPLETE:' ||'Y';
1033 ELSE
1034 result := 'COMPLETE:' ||'N';
1035 END IF;
1036 hr_utility.set_location ('Leaving '||l_proc,175);
1037 exception
1038 when others then
1039 result := null;
1040 END;
1041 /*******************************************************************************************/
1042
1043 /*******************************************************************************************/
1044 PROCEDURE remove_transaction
1045 ( itemtype IN VARCHAR2
1046 , itemkey IN VARCHAR2
1047 , actid IN NUMBER
1048 , funcmode IN VARCHAR2
1049 , result OUT NOCOPY VARCHAR2
1050 )
1051 IS
1052 l_proc varchar2(61) := g_package||':'||'remove_transaction';
1053 BEGIN
1054 hr_utility.set_location ('Entering '||l_proc,180);
1055 update ben_transaction
1056 set STATUS='PROCESSED'
1057 where attribute1 = itemkey
1058 and transaction_type='EMPELIGHDR';
1059 update ben_transaction
1060 set STATUS='PROCESSED'
1061 where transaction_type = 'EMPELIGEMP'
1062 and attribute1 = itemkey;
1063 result := 'COMPLETE:';
1064 hr_utility.set_location ('Leaving '||l_proc,185);
1065 exception
1066 when others then
1067 result := null;
1068 END;
1069 /*******************************************************************************************/
1070 END;