1 package body ben_cwb_utils as
2 /* $Header: bencwbutils.pkb 120.17.12010000.2 2008/10/20 12:42:23 sgnanama ship $ */
3 FUNCTION get_task_access (
4 p_hidden_cd IN VARCHAR2,
5 p_task_access_cd IN VARCHAR2,
6 p_plan_access_cd IN VARCHAR2,
7 p_wksht_grp_cd IN VARCHAR2,
8 p_population_cd IN VARCHAR2,
9 p_status_cd IN VARCHAR2,
10 p_dist_bdgt_iss_dt IN DATE,
11 p_ss_update_start_dt IN DATE,
12 p_ss_update_end_dt IN DATE,
13 p_effective_dt IN DATE
14 )
15 RETURN VARCHAR2
16 IS
17 l_access_cd VARCHAR2 (2);
18 BEGIN
19 l_access_cd := 'UP';
20
21 IF (p_status_cd = 'NA')
22 THEN
23 --No Access
24 l_access_cd := 'NA';
25 ELSIF (p_wksht_grp_cd = 'BDGT' AND p_dist_bdgt_iss_dt IS NULL)
26 THEN
27 l_access_cd := 'NA';
28 ELSIF ( p_hidden_cd = 'NA'
29 OR p_task_access_cd = 'NA'
30 OR p_plan_access_cd = 'NA'
31 )
32 THEN
33 l_access_cd := 'NA';
34 ELSIF ( p_hidden_cd = 'RO'
35 OR p_task_access_cd = 'RO'
36 OR p_plan_access_cd = 'RO'
37 )
38 THEN
39 IF (p_wksht_grp_cd = 'BDGT' AND p_population_cd IS NULL)
40 THEN
41 l_access_cd := 'NA';
42 ELSE
43 l_access_cd := 'RO';
44 END IF;
45 ELSIF p_effective_dt NOT BETWEEN p_ss_update_start_dt AND p_ss_update_end_dt
46 THEN
47 l_access_cd := 'RO';
48 END IF;
49
50 RETURN l_access_cd;
51 END get_task_access;
52 --
53 FUNCTION is_task_enabled
54 (p_access_cd in varchar2,
55 p_population_cd in varchar2,
56 p_status_cd in varchar2,
57 p_dist_bdgt_iss_dt in date,
58 p_wksht_grp_cd in varchar2)
59 return varchar2
60 is
61 begin
62 if(p_status_cd = 'NA')
63 then
64 return 'D';
65 else
66 if (p_wksht_grp_cd = 'BDGT')
67 then
68 if (p_dist_bdgt_iss_dt is null)
69 then
70 return 'D';
71 elsif (nvl(p_access_cd,'NA') = 'RO' and p_population_cd is null) then
72 return 'D';
73 end if;
74 /*elsif (p_wksht_grp_cd = 'RVW')
75 then
76 if (p_status_cd = 'NS')
77 then
78 return 'D';
79 end if;*/
80 end if;
81 return 'Y';
82 end if;
83 end is_task_enabled;
84 --
85 FUNCTION get_manager_name(p_emp_per_in_ler_id in number,
86 p_level in number)
87 return varchar2
88 is
89
90 Cursor csr_mgr_name
91 is
92 Select bcpi.full_name,
93 bcpi.brief_name,
94 bcpi.custom_name
95 From ben_cwb_person_info bcpi,
96 ben_cwb_group_hrchy bcgh
97 where bcgh.emp_per_in_ler_id = p_emp_per_in_ler_id
98 and bcgh.lvl_num = (select max(lvl_num) - p_level + 1
99 from ben_cwb_group_hrchy
100 where emp_per_in_ler_id = p_emp_per_in_ler_id)
101 and bcgh.lvl_num > 0
102 and bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id;
103
104 name_profile varchar2(2000);
105 manager_names csr_mgr_name%rowtype;
106
107 begin
108
109 name_profile := get_profile ('BEN_DISPLAY_EMPLOYEE_NAME');
110
111 open csr_mgr_name;
112 fetch csr_mgr_name into manager_names;
113 close csr_mgr_name;
114
115 if('FN' = name_profile)
116 then
117 return manager_names.full_name;
118 elsif ('CN' = name_profile)
119 then
120 return manager_names.custom_name;
121 else
122 return manager_names.brief_name;
123 end if;
124
125 end get_manager_name;
126 --
127
128 FUNCTION get_eligibility(p_plan_status in varchar2,
129 p_opt1_status in varchar2,
130 p_opt2_status in varchar2,
131 p_opt3_status in varchar2,
132 p_opt4_status in varchar2
133 )
134 return varchar2
135 is
136 l_elig_count number := 0;
137 l_inelig_count number := 0;
138 begin
139
140 IF p_plan_status is not null THEN
141 IF p_plan_status = 'Y' THEN
142 l_elig_count := l_elig_count +1;
143 ELSE
144 l_inelig_count := l_inelig_count + 1;
145 END IF;
146 END IF;
147
148 IF p_opt1_status is not null THEN
149 IF p_opt1_status = 'Y' THEN
150 l_elig_count := l_elig_count +1;
151 ELSE
152 l_inelig_count := l_inelig_count + 1;
153 END IF;
154 END IF;
155
156 IF p_opt2_status is not null THEN
157 IF p_opt2_status = 'Y' THEN
158 l_elig_count := l_elig_count +1;
159 ELSE
160 l_inelig_count := l_inelig_count + 1;
161 END IF;
162 END IF;
163
164 IF p_opt3_status is not null THEN
165 IF p_opt3_status = 'Y' THEN
166 l_elig_count := l_elig_count +1;
167 ELSE
168 l_inelig_count := l_inelig_count + 1;
169 END IF;
170 END IF;
171
172 IF p_opt4_status is not null THEN
173 IF p_opt4_status = 'Y' THEN
174 l_elig_count := l_elig_count +1;
175 ELSE
176 l_inelig_count := l_inelig_count + 1;
177 END IF;
178 END IF;
179
180 IF l_elig_count > 0 AND l_inelig_count > 0 THEN
181 return 'BOTH';
182 END IF;
183
184 IF l_elig_count = 0 AND l_inelig_count = 0 THEN
185 return 'BOTH';
186 END IF;
187
188 IF l_elig_count > 0 AND l_inelig_count = 0 THEN
189 return 'Y';
190 END IF;
191
192 IF l_elig_count = 0 AND l_inelig_count > 0 THEN
193 return 'N';
194 END IF;
195
196 end;
197
198 FUNCTION get_profile(p_profile_name in varchar2)
199 return varchar2
200 is
201 name_profile varchar2(2000);
202 begin
203 fnd_profile.get (p_profile_name, name_profile);
204 return name_profile;
205 end get_profile;
206 --
207 PROCEDURE get_site_profile (
208 p_profile_1 in varchar2 default null,
209 p_value_1 out nocopy varchar2)
210 IS
211
212 l_defined_z boolean;
213
214 CURSOR value_site_profile(v_name varchar2)
215 IS
216 SELECT valu.profile_option_value
217 FROM fnd_profile_options options
218 ,fnd_profile_option_values valu
219 WHERE options.profile_option_name = upper(v_name)
220 AND options.start_date_active <= sysdate
221 AND nvl(options.end_date_active, sysdate) >= sysdate
222 AND options.profile_option_id = valu.profile_option_id
223 AND valu.level_id = 10001;
224
225 BEGIN
226 if(p_profile_1 is not null) then
227 open value_site_profile(p_profile_1);
228 fetch value_site_profile
229 into p_value_1;
230 close value_site_profile;
231 end if;
232 END get_site_profile;
233 --
234 --
235 PROCEDURE get_resp_profile (
236 p_resp_id in number default null,
237 p_profile_1 in varchar2 default null,
238 p_value_1 out nocopy varchar2)
239 IS
240
241 l_defined_z boolean;
242
243 CURSOR value_resp_profile(v_name varchar2, v_resp_id number)
244 IS
245 SELECT valu.profile_option_value
246 FROM fnd_profile_options options
247 ,fnd_profile_option_values valu
248 WHERE options.profile_option_name = upper(v_name)
249 AND options.start_date_active <= sysdate
250 AND nvl(options.end_date_active, sysdate) >= sysdate
251 AND options.profile_option_id = valu.profile_option_id
252 AND valu.level_value_application_id = 800
253 AND valu.level_id = 10003
254 AND valu.level_value = v_resp_id;
255
256 BEGIN
257 if(p_profile_1 is not null) then
258 open value_resp_profile(p_profile_1,p_resp_id);
259 fetch value_resp_profile
260 into p_value_1;
261 close value_resp_profile;
262 end if;
263 END get_resp_profile;
264 --
265 PROCEDURE get_user_profile (
266 p_user_id in number default null,
267 p_profile_1 in varchar2 default null,
268 p_profile_2 in varchar2 default null,
269 p_profile_3 in varchar2 default null,
270 p_profile_4 in varchar2 default null,
271 p_profile_5 in varchar2 default null,
272 p_profile_6 in varchar2 default null,
273 p_profile_7 in varchar2 default null,
274 p_profile_8 in varchar2 default null,
275 p_profile_9 in varchar2 default null,
276 p_profile_10 in varchar2 default null,
277 p_value_1 out nocopy varchar2,
278 p_value_2 out nocopy varchar2,
279 p_value_3 out nocopy varchar2,
280 p_value_4 out nocopy varchar2,
281 p_value_5 out nocopy varchar2,
282 p_value_6 out nocopy varchar2,
283 p_value_7 out nocopy varchar2,
284 p_value_8 out nocopy varchar2,
285 p_value_9 out nocopy varchar2,
286 p_value_10 out nocopy varchar2)
287 IS
288
289 l_defined_z boolean;
290
291 CURSOR value_user_profile(v_name varchar2, v_user_id number)
292 IS
293 SELECT valu.profile_option_value
294 FROM fnd_profile_options options
295 ,fnd_profile_option_values valu
296 WHERE options.profile_option_name = upper(v_name)
297 AND options.start_date_active <= sysdate
298 AND nvl(options.end_date_active, sysdate) >= sysdate
299 AND options.profile_option_id = valu.profile_option_id
300 AND valu.application_id = 805
301 AND valu.level_id = 10004
302 AND valu.level_value = v_user_id;
303
304 BEGIN
305 if(p_profile_1 is not null) then
306 open value_user_profile(p_profile_1,p_user_id);
307 fetch value_user_profile
308 into p_value_1;
309 close value_user_profile;
310 end if;
311 if(p_profile_2 is not null) then
312 open value_user_profile(p_profile_2,p_user_id);
313 fetch value_user_profile
314 into p_value_2;
315 close value_user_profile;
316 end if;
317 if(p_profile_3 is not null) then
318 open value_user_profile(p_profile_3,p_user_id);
319 fetch value_user_profile
320 into p_value_3;
321 close value_user_profile;
322 end if;
323 if(p_profile_4 is not null) then
324 open value_user_profile(p_profile_4,p_user_id);
325 fetch value_user_profile
326 into p_value_4;
327 close value_user_profile;
328 end if;
329 if(p_profile_5 is not null) then
330 open value_user_profile(p_profile_5,p_user_id);
331 fetch value_user_profile
332 into p_value_5;
333 close value_user_profile;
334 end if;
335 if(p_profile_6 is not null) then
336 open value_user_profile(p_profile_6,p_user_id);
337 fetch value_user_profile
338 into p_value_6;
339 close value_user_profile;
340 end if;
341 if(p_profile_7 is not null) then
342 open value_user_profile(p_profile_7,p_user_id);
343 fetch value_user_profile
344 into p_value_7;
345 close value_user_profile;
346 end if;
347 if(p_profile_8 is not null) then
348 open value_user_profile(p_profile_8,p_user_id);
349 fetch value_user_profile
350 into p_value_8;
351 close value_user_profile;
352 end if;
353 if(p_profile_9 is not null) then
354 open value_user_profile(p_profile_9,p_user_id);
355 fetch value_user_profile
356 into p_value_9;
357 close value_user_profile;
358 end if;
359 if(p_profile_10 is not null) then
360 open value_user_profile(p_profile_10,p_user_id);
361 fetch value_user_profile
362 into p_value_10;
363 close value_user_profile;
364 end if;
365 END get_user_profile;
366 --
367 FUNCTION get_bdgt_pct_of_elig_sal_decs return number is
368 l_return_value number;
369 begin
370 l_return_value := to_number(get_profile('BEN_CWB_BS_PCT_ES_DECS_DISP'));
371 if l_return_value is null then
372 return 2;
373 elsif l_return_value > 10 then
374 return 10;
375 else
376 return l_return_value;
377 end if;
378 exception
379 when others then
380 return 2;
381 end get_bdgt_pct_of_elig_sal_decs;
382 --
383 FUNCTION get_alloc_pct_of_elig_sal_decs return number is
384 l_return_value number;
385 begin
386 l_return_value := to_number(get_profile('BEN_CWB_WS_PCT_ES_DECS_DISP'));
387 if l_return_value is null then
388 return 2;
389 elsif l_return_value > 10 then
390 return 10;
391 else
392 return l_return_value;
393 end if;
394 exception
395 when others then
396 return 2;
397 end get_alloc_pct_of_elig_sal_decs;
398 --
399 FUNCTION is_person_switchable(p_person_id in number,
400 p_effective_date in date)
401 return varchar2
402 is
403 CURSOR is_person_in_secured_view
404 is
405 SELECT 'x'
406 FROM per_people_f ppf,
407 per_person_types ppt
408 WHERE ppf.person_id = p_person_id
409 AND ppt.person_type_id = ppf.person_type_id
410 AND ppt.system_person_type <> 'EX_EMP_APL'
411 AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
412
413 l_switch varchar2(1);
414
415 begin
416 OPEN is_person_in_secured_view;
417 FETCH is_person_in_secured_view INTO l_switch;
418 if is_person_in_secured_view%NOTFOUND then
419 CLOSE is_person_in_secured_view;
420 return 'N';
421 else
422 CLOSE is_person_in_secured_view;
423 return 'Y';
424 end if;
425 END is_person_switchable;
426
427
428 function add_number_with_null_check(p_orig_val in number,
429 p_new_val in number) return number is
430 begin
431 if p_orig_val is null then
432 return p_new_val;
433 else
434 return p_orig_val + nvl(p_new_val,0);
435 end if;
436 end add_number_with_null_check;
437
438
439
440 /* ---------------------------------------------------------------------
441 Procedures/Functions Below are defined for Document Management
442 Enhancements to support Printable Documents (PDF)
443 BEGIN
444 --------------------------------------------------------------------- */
445
446
447 Function get_option1_name(
448 p_group_plan_id in number,
449 p_lf_evnt_ocrd_dt in Date,
450 p_oipl_id in number,
451 p_group_per_in_ler_id in number,
452 p_pl_id in number,
453 p_ws_sub_acty_typ_cd in varchar2) return varchar2
454 is
455 cursor cur_option1_name is
456 Select name
457 From ben_cwb_pl_dsgn
458 Where pl_id = p_group_plan_id
459 and group_pl_id = p_group_plan_id
460 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
461 --And oipl_id <> -1
465 l_option1_name varchar2(240);
462 --And opt_count = 1;
463 and oipl_ordr_num = 1;
464
466 Begin
467
468 open cur_option1_name;
469 fetch cur_option1_name into l_option1_name;
470 close cur_option1_name;
471 return l_option1_name;
472 End;
473
474
475 Procedure populate_person_option1_rec(
476 p_group_plan_id in number,
477 p_lf_evt_ocrd_dt in Date,
478 p_oipl_id in number,
479 p_group_per_in_ler_id in number,
480 p_pl_id in number,
481 p_ws_sub_acty_typ_cd in varchar2)
482 Is
483 cursor cur_option1_rate is
484 Select bcpr.*
485 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
486 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
487 And bcpd.group_pl_id = p_group_plan_id
488 -- AND bcpd.pl_id = p_group_plan_id
489 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
490 --And bcpd.oipl_id <> -1
491 --And bcpd.opt_count = 1
492 and oipl_ordr_num = 1
493 And bcpr.pl_id = bcpd.pl_id
494 And bcpr.group_pl_id = bcpd.group_pl_id
495 And bcpr.oipl_id = bcpd.oipl_id
496 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
497 and bcpr.elig_flag = 'Y';
498 Begin
499
500 g_opt1_person_rates_rec := null;
501 open cur_option1_rate;
502 fetch cur_option1_rate into g_opt1_person_rates_rec;
503 close cur_option1_rate;
504
505 End;
506
507 Function get_option1_rate_ws_amt(
508 p_group_plan_id in number,
509 p_lf_evnt_ocrd_dt in Date,
510 p_oipl_id in number,
511 p_group_per_in_ler_id in number,
512 p_pl_id in number,
513 p_ws_sub_acty_typ_cd in varchar2) return number
514 is
515 Begin
516 populate_person_option1_rec(
517 p_group_plan_id,
518 p_lf_evnt_ocrd_dt,
519 p_oipl_id,
520 p_group_per_in_ler_id,
521 p_pl_id,
522 p_ws_sub_acty_typ_cd);
523 return g_opt1_person_rates_rec.ws_val;
524
525 End get_option1_rate_ws_amt;
526
527 Function get_option1_unit(
528 p_group_plan_id in number,
529 p_lf_evnt_ocrd_dt in Date,
530 p_oipl_id in number,
531 p_group_per_in_ler_id in number,
532 p_pl_id in number,
533 p_ws_sub_acty_typ_cd in varchar2) return varchar2
534 Is
535 --- ws_nmmntry_uom need to be decode from lookup table
536 Cursor cur_option1_units is
537 Select decode(ws_nnmntry_uom,null,currency, hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom) )
538 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
539 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
540 And bcpd.group_pl_id = p_group_plan_id
541 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
542 And bcpr.pl_id = bcpd.pl_id
543 And bcpr.group_pl_id = bcpd.group_pl_id
544 And bcpr.oipl_id = bcpd.oipl_id
545 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
546 and bcpr.elig_flag = 'Y'
547 and oipl_ordr_num = 1;
548
549 l_option1_unit varchar2(30);
550 Begin
551 open cur_option1_units;
552 fetch cur_option1_units into l_option1_unit;
553 close cur_option1_units;
554 return l_option1_unit;
555 End get_option1_unit;
556
557
558 Function get_option1_elg_sal(
559 p_group_plan_id in number,
560 p_lf_evnt_ocrd_dt in Date,
561 p_oipl_id in number,
562 p_group_per_in_ler_id in number,
563 p_pl_id in number,
564 p_ws_sub_acty_typ_cd in varchar2) return number
565 is
566 Begin
567 populate_person_option1_rec(
568 p_group_plan_id,
569 p_lf_evnt_ocrd_dt,
570 p_oipl_id,
571 p_group_per_in_ler_id,
572 p_pl_id,
573 p_ws_sub_acty_typ_cd);
574 return g_opt1_person_rates_rec.ELIG_SAL_VAL;
575
576 End get_option1_elg_sal;
577
578 Function get_option1_elg_per_sal(
579 p_group_plan_id in number,
580 p_lf_evnt_ocrd_dt in Date,
581 p_oipl_id in number,
582 p_group_per_in_ler_id in number,
583 p_pl_id in number,
584 p_ws_sub_acty_typ_cd in varchar2) return number
585 Is
586 Begin
587
588 populate_person_option1_rec(
589 p_group_plan_id,
590 p_lf_evnt_ocrd_dt,
591 p_oipl_id,
592 p_group_per_in_ler_id,
593 p_pl_id,
594 p_ws_sub_acty_typ_cd);
595
596 if (g_opt1_person_rates_rec.elig_sal_val <> 0) then
597 return round( (g_opt1_person_rates_rec.ws_val / g_opt1_person_rates_rec.elig_sal_val) * 100,2);
598 else
599 return to_number(null);
603
600 end if;
601
602 End get_option1_elg_per_sal;
604 Function get_option1_rate_reco_amt(
605 p_group_plan_id in number,
606 p_lf_evnt_ocrd_dt in Date,
607 p_oipl_id in number,
608 p_group_per_in_ler_id in number,
609 p_pl_id in number,
610 p_ws_sub_acty_typ_cd in varchar2) return number
611 is
612 Begin
613 populate_person_option1_rec(
614 p_group_plan_id,
615 p_lf_evnt_ocrd_dt,
616 p_oipl_id,
617 p_group_per_in_ler_id,
618 p_pl_id,
619 p_ws_sub_acty_typ_cd);
620 return g_opt1_person_rates_rec.REC_VAL;
621
622 End get_option1_rate_reco_amt;
623
624 Function get_option1_rate_oth_sal(
625 p_group_plan_id in number,
626 p_lf_evnt_ocrd_dt in Date,
627 p_oipl_id in number,
628 p_group_per_in_ler_id in number,
629 p_pl_id in number,
630 p_ws_sub_acty_typ_cd in varchar2) return number
631 is
632 Begin
633 populate_person_option1_rec(
634 p_group_plan_id,
635 p_lf_evnt_ocrd_dt,
636 p_oipl_id,
637 p_group_per_in_ler_id,
638 p_pl_id,
639 p_ws_sub_acty_typ_cd);
640 return g_opt1_person_rates_rec.OTH_COMP_VAL;
641
642 End get_option1_rate_oth_sal;
643
644 Function get_option1_rate_sta_sal(
645 p_group_plan_id in number,
646 p_lf_evnt_ocrd_dt in Date,
647 p_oipl_id in number,
648 p_group_per_in_ler_id in number,
649 p_pl_id in number,
650 p_ws_sub_acty_typ_cd in varchar2) return number
651 is
652 Begin
653 populate_person_option1_rec(
654 p_group_plan_id,
655 p_lf_evnt_ocrd_dt,
656 p_oipl_id,
657 p_group_per_in_ler_id,
658 p_pl_id,
659 p_ws_sub_acty_typ_cd);
660 return g_opt1_person_rates_rec.STAT_SAL_VAL;
661
662 End get_option1_rate_sta_sal;
663
664 Function get_option1_rate_tot_comp(
665 p_group_plan_id in number,
666 p_lf_evnt_ocrd_dt in Date,
667 p_oipl_id in number,
668 p_group_per_in_ler_id in number,
669 p_pl_id in number,
670 p_ws_sub_acty_typ_cd in varchar2) return number
671 is
672 Begin
673 populate_person_option1_rec(
674 p_group_plan_id,
675 p_lf_evnt_ocrd_dt,
676 p_oipl_id,
677 p_group_per_in_ler_id,
678 p_pl_id,
679 p_ws_sub_acty_typ_cd);
680 return g_opt1_person_rates_rec.TOT_COMP_VAL;
681
682 End get_option1_rate_tot_comp;
683
684
685 Function get_option1_rate_misc1(
686 p_group_plan_id in number,
687 p_lf_evnt_ocrd_dt in Date,
688 p_oipl_id in number,
689 p_group_per_in_ler_id in number,
690 p_pl_id in number,
691 p_ws_sub_acty_typ_cd in varchar2) return number
692 is
693 Begin
694 populate_person_option1_rec(
695 p_group_plan_id,
696 p_lf_evnt_ocrd_dt,
697 p_oipl_id,
698 p_group_per_in_ler_id,
699 p_pl_id,
700 p_ws_sub_acty_typ_cd);
701 return g_opt1_person_rates_rec.MISC1_VAL;
702
703 End get_option1_rate_misc1;
704
705 Function get_option1_rate_misc2(
706 p_group_plan_id in number,
707 p_lf_evnt_ocrd_dt in Date,
708 p_oipl_id in number,
709 p_group_per_in_ler_id in number,
710 p_pl_id in number,
711 p_ws_sub_acty_typ_cd in varchar2) return number
712 is
713 Begin
714 populate_person_option1_rec(
715 p_group_plan_id,
716 p_lf_evnt_ocrd_dt,
717 p_oipl_id,
718 p_group_per_in_ler_id,
719 p_pl_id,
720 p_ws_sub_acty_typ_cd);
721 return g_opt1_person_rates_rec.MISC2_VAL;
722
723 End get_option1_rate_misc2;
724
725 Function get_option1_rate_misc3(
726 p_group_plan_id in number,
727 p_lf_evnt_ocrd_dt in Date,
728 p_oipl_id in number,
729 p_group_per_in_ler_id in number,
730 p_pl_id in number,
731 p_ws_sub_acty_typ_cd in varchar2) return number
732 is
733 Begin
734 populate_person_option1_rec(
735 p_group_plan_id,
736 p_lf_evnt_ocrd_dt,
737 p_oipl_id,
738 p_group_per_in_ler_id,
739 p_pl_id,
740 p_ws_sub_acty_typ_cd);
741 return g_opt1_person_rates_rec.MISC3_VAL;
742
743 End get_option1_rate_misc3;
744
745
746 Function get_option2_name(
747 p_group_plan_id in number,
748 p_lf_evnt_ocrd_dt in Date,
749 p_oipl_id in number,
753 is
750 p_group_per_in_ler_id in number,
751 p_pl_id in number,
752 p_ws_sub_acty_typ_cd in varchar2) return varchar2
754 cursor cur_option2_name is
755 Select name
756 From ben_cwb_pl_dsgn
757 Where pl_id = p_group_plan_id
758 And group_pl_id = p_group_plan_id
759 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
760 -- And oipl_id <> -1
761 -- And opt_count = 2;
762 and oipl_ordr_num = 2;
763
764 l_option2_name varchar2(240);
765 Begin
766
767 open cur_option2_name;
768 fetch cur_option2_name into l_option2_name;
769 close cur_option2_name;
770 return l_option2_name;
771 End;
772
773 Procedure populate_person_option2_rec(
774 p_group_plan_id in number,
775 p_lf_evt_ocrd_dt in Date,
776 p_oipl_id in number,
777 p_group_per_in_ler_id in number,
778 p_pl_id in number,
779 p_ws_sub_acty_typ_cd in varchar2)
780 Is
781 cursor cur_option2_rate is
782 Select bcpr.*
783 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
784 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
785 And bcpd.group_pl_id = p_group_plan_id
786 --AND bcpd.pl_id = p_group_plan_id
787 And bcpd.lf_evt_ocrd_dt= p_lf_evt_ocrd_dt
788 --And bcpd.oipl_id <> -1
789 -- And bcpd.opt_count = 2
790 and oipl_ordr_num = 2
791 And bcpd.pl_id = bcpr.pl_id
792 And bcpd.group_pl_id = bcpr.group_pl_id
793 And bcpd.oipl_id = bcpr.oipl_id
794 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
795 and bcpr.elig_flag = 'Y';
796 Begin
797
798 g_opt2_person_rates_rec := null;
799 open cur_option2_rate;
800 fetch cur_option2_rate into g_opt2_person_rates_rec;
801 close cur_option2_rate;
802
803 End populate_person_option2_rec;
804
805 Function get_option2_rate_ws_amt(
806 p_group_plan_id in number,
807 p_lf_evnt_ocrd_dt in Date,
808 p_oipl_id in number,
809 p_group_per_in_ler_id in number,
810 p_pl_id in number,
811 p_ws_sub_acty_typ_cd in varchar2) return number
812 is
813 Begin
814 populate_person_option2_rec(
815 p_group_plan_id,
816 p_lf_evnt_ocrd_dt,
817 p_oipl_id,
818 p_group_per_in_ler_id,
819 p_pl_id,
820 p_ws_sub_acty_typ_cd);
821 return g_opt2_person_rates_rec.ws_val;
822
823 End get_option2_rate_ws_amt;
824
825 Function get_option2_unit(
826 p_group_plan_id in number,
827 p_lf_evnt_ocrd_dt in Date,
828 p_oipl_id in number,
829 p_group_per_in_ler_id in number,
830 p_pl_id in number,
831 p_ws_sub_acty_typ_cd in varchar2) return varchar2
832 Is
833 ----- ws_nnmntry_uom needs to be decoded from lookup table
834 Cursor cur_option2_units is
835 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
836 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
837 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
838 And bcpd.group_pl_id = p_group_plan_id
839 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
840 And bcpr.pl_id = bcpd.pl_id
841 And bcpr.group_pl_id = bcpd.group_pl_id
842 And bcpr.oipl_id = bcpd.oipl_id
843 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
844 and bcpr.elig_flag = 'Y'
845 and oipl_ordr_num = 2;
846
847 l_option2_unit varchar2(30);
848 Begin
849 open cur_option2_units;
850 fetch cur_option2_units into l_option2_unit;
851 close cur_option2_units;
852 return l_option2_unit;
853 End get_option2_unit;
854
855
856 Function get_option2_elg_sal(
857 p_group_plan_id in number,
858 p_lf_evnt_ocrd_dt in Date,
859 p_oipl_id in number,
860 p_group_per_in_ler_id in number,
861 p_pl_id in number,
862 p_ws_sub_acty_typ_cd in varchar2) return number
863 is
864 Begin
865
866 populate_person_option2_rec(
867 p_group_plan_id,
868 p_lf_evnt_ocrd_dt,
869 p_oipl_id,
870 p_group_per_in_ler_id,
871 p_pl_id,
872 p_ws_sub_acty_typ_cd);
873 return g_opt2_person_rates_rec.ELIG_SAL_VAL;
874
875 End get_option2_elg_sal;
876
877 Function get_option2_elg_per_sal(
878 p_group_plan_id in number,
879 p_lf_evnt_ocrd_dt in Date,
880 p_oipl_id in number,
881 p_group_per_in_ler_id in number,
882 p_pl_id in number,
883 p_ws_sub_acty_typ_cd in varchar2) return number
884 Is
885 Begin
886
887 populate_person_option2_rec(
888 p_group_plan_id,
892 p_pl_id,
889 p_lf_evnt_ocrd_dt,
890 p_oipl_id,
891 p_group_per_in_ler_id,
893 p_ws_sub_acty_typ_cd);
894
895 if (g_opt2_person_rates_rec.elig_sal_val <> 0) then
896 return round( (g_opt2_person_rates_rec.ws_val / g_opt2_person_rates_rec.elig_sal_val) * 100,2);
897 else
898 return to_number(null);
899 end if;
900
901 End get_option2_elg_per_sal;
902
903 Function get_option2_rate_reco_amt(
904 p_group_plan_id in number,
905 p_lf_evnt_ocrd_dt in Date,
906 p_oipl_id in number,
907 p_group_per_in_ler_id in number,
908 p_pl_id in number,
909 p_ws_sub_acty_typ_cd in varchar2) return number
910 is
911 Begin
912 populate_person_option2_rec(
913 p_group_plan_id,
914 p_lf_evnt_ocrd_dt,
915 p_oipl_id,
916 p_group_per_in_ler_id,
917 p_pl_id,
918 p_ws_sub_acty_typ_cd);
919 return g_opt2_person_rates_rec.REC_VAL;
920
921 End get_option2_rate_reco_amt;
922
923 Function get_option2_rate_oth_sal(
924 p_group_plan_id in number,
925 p_lf_evnt_ocrd_dt in Date,
926 p_oipl_id in number,
927 p_group_per_in_ler_id in number,
928 p_pl_id in number,
929 p_ws_sub_acty_typ_cd in varchar2) return number
930 is
931 Begin
932 populate_person_option2_rec(
933 p_group_plan_id,
934 p_lf_evnt_ocrd_dt,
935 p_oipl_id,
936 p_group_per_in_ler_id,
937 p_pl_id,
938 p_ws_sub_acty_typ_cd);
939 return g_opt2_person_rates_rec.OTH_COMP_VAL;
940
941 End get_option2_rate_oth_sal;
942
943 Function get_option2_rate_sta_sal(
944 p_group_plan_id in number,
945 p_lf_evnt_ocrd_dt in Date,
946 p_oipl_id in number,
947 p_group_per_in_ler_id in number,
948 p_pl_id in number,
949 p_ws_sub_acty_typ_cd in varchar2) return number
950 is
951 Begin
952 populate_person_option2_rec(
953 p_group_plan_id,
954 p_lf_evnt_ocrd_dt,
955 p_oipl_id,
956 p_group_per_in_ler_id,
957 p_pl_id,
958 p_ws_sub_acty_typ_cd);
959 return g_opt2_person_rates_rec.STAT_SAL_VAL;
960
961 End get_option2_rate_sta_sal;
962
963 Function get_option2_rate_tot_comp(
964 p_group_plan_id in number,
965 p_lf_evnt_ocrd_dt in Date,
966 p_oipl_id in number,
967 p_group_per_in_ler_id in number,
968 p_pl_id in number,
969 p_ws_sub_acty_typ_cd in varchar2) return number
970 is
971 Begin
972 populate_person_option2_rec(
973 p_group_plan_id,
974 p_lf_evnt_ocrd_dt,
975 p_oipl_id,
976 p_group_per_in_ler_id,
977 p_pl_id,
978 p_ws_sub_acty_typ_cd);
979 return g_opt2_person_rates_rec.TOT_COMP_VAL;
980
981 End get_option2_rate_tot_comp;
982
983
984 Function get_option2_rate_misc1(
985 p_group_plan_id in number,
986 p_lf_evnt_ocrd_dt in Date,
987 p_oipl_id in number,
988 p_group_per_in_ler_id in number,
989 p_pl_id in number,
990 p_ws_sub_acty_typ_cd in varchar2) return number
991 is
992 Begin
993 populate_person_option2_rec(
994 p_group_plan_id,
995 p_lf_evnt_ocrd_dt,
996 p_oipl_id,
997 p_group_per_in_ler_id,
998 p_pl_id,
999 p_ws_sub_acty_typ_cd);
1000 return g_opt2_person_rates_rec.MISC1_VAL;
1001
1002 End get_option2_rate_misc1;
1003
1004 Function get_option2_rate_misc2(
1005 p_group_plan_id in number,
1006 p_lf_evnt_ocrd_dt in Date,
1007 p_oipl_id in number,
1008 p_group_per_in_ler_id in number,
1009 p_pl_id in number,
1010 p_ws_sub_acty_typ_cd in varchar2) return number
1011 is
1012 Begin
1013 populate_person_option2_rec(
1014 p_group_plan_id,
1015 p_lf_evnt_ocrd_dt,
1016 p_oipl_id,
1017 p_group_per_in_ler_id,
1018 p_pl_id,
1019 p_ws_sub_acty_typ_cd);
1020 return g_opt2_person_rates_rec.MISC2_VAL;
1021
1022 End get_option2_rate_misc2;
1023
1024 Function get_option2_rate_misc3(
1025 p_group_plan_id in number,
1026 p_lf_evnt_ocrd_dt in Date,
1027 p_oipl_id in number,
1028 p_group_per_in_ler_id in number,
1029 p_pl_id in number,
1030 p_ws_sub_acty_typ_cd in varchar2) return number
1031 is
1032 Begin
1033 populate_person_option2_rec(
1034 p_group_plan_id,
1035 p_lf_evnt_ocrd_dt,
1036 p_oipl_id,
1040 return g_opt2_person_rates_rec.MISC3_VAL;
1037 p_group_per_in_ler_id,
1038 p_pl_id,
1039 p_ws_sub_acty_typ_cd);
1041
1042 End get_option2_rate_misc3;
1043
1044
1045 Function get_option3_name(
1046 p_group_plan_id in number,
1047 p_lf_evnt_ocrd_dt in Date,
1048 p_oipl_id in number,
1049 p_group_per_in_ler_id in number,
1050 p_pl_id in number,
1051 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1052 is
1053 cursor cur_option3_name is
1054 Select name
1055 From ben_cwb_pl_dsgn
1056 Where pl_id = p_group_plan_id
1057 And group_pl_id = p_group_plan_id
1058 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1059 -- And oipl_id <> -1
1060 -- And opt_count = 3;
1061 and oipl_ordr_num = 3;
1062
1063 l_option3_name varchar2(240);
1064 Begin
1065
1066 open cur_option3_name;
1067 fetch cur_option3_name into l_option3_name;
1068 close cur_option3_name;
1069 return l_option3_name;
1070 End get_option3_name;
1071
1072 Procedure populate_person_option3_rec(
1073 p_group_plan_id in number,
1074 p_lf_evt_ocrd_dt in Date,
1075 p_oipl_id in number,
1076 p_group_per_in_ler_id in number,
1077 p_pl_id in number,
1078 p_ws_sub_acty_typ_cd in varchar2)
1079 Is
1080 cursor cur_option3_rate is
1081 Select bcpr.*
1082 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1083 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1084 And bcpd.group_pl_id = p_group_plan_id
1085 -- AND bcpd.pl_id = p_group_plan_id
1086 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1087 --And bcpd.oipl_id <> -1
1088 --And bcpd.opt_count = 3
1089 and oipl_ordr_num = 3
1090 And bcpd.pl_id = bcpr.pl_id
1091 And bcpd.group_pl_id = bcpr.group_pl_id
1092 And bcpd.oipl_id = bcpr.oipl_id
1093 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
1094 and bcpr.elig_flag = 'Y';
1095 Begin
1096
1097 g_opt3_person_rates_rec := null;
1098 open cur_option3_rate;
1099 fetch cur_option3_rate into g_opt3_person_rates_rec;
1100 close cur_option3_rate;
1101
1102 End populate_person_option3_rec;
1103
1104 Function get_option3_rate_ws_amt(
1105 p_group_plan_id in number,
1106 p_lf_evnt_ocrd_dt in Date,
1107 p_oipl_id in number,
1108 p_group_per_in_ler_id in number,
1109 p_pl_id in number,
1110 p_ws_sub_acty_typ_cd in varchar2) return number
1111 is
1112 Begin
1113 populate_person_option3_rec(
1114 p_group_plan_id,
1115 p_lf_evnt_ocrd_dt,
1116 p_oipl_id,
1117 p_group_per_in_ler_id,
1118 p_pl_id,
1119 p_ws_sub_acty_typ_cd);
1120 return g_opt3_person_rates_rec.ws_val;
1121
1122 End get_option3_rate_ws_amt;
1123
1124 Function get_option3_unit(
1125 p_group_plan_id in number,
1126 p_lf_evnt_ocrd_dt in Date,
1127 p_oipl_id in number,
1128 p_group_per_in_ler_id in number,
1129 p_pl_id in number,
1130 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1131 Is
1132 -- ws_nnmntry_uom needs to be decoded from lookup table
1133 Cursor cur_option3_units is
1134 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
1135 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1136 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1137 And bcpd.group_pl_id = p_group_plan_id
1138 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1139 And bcpr.pl_id = bcpd.pl_id
1140 And bcpr.group_pl_id = bcpd.group_pl_id
1141 And bcpr.oipl_id = bcpd.oipl_id
1142 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1143 and bcpr.elig_flag = 'Y'
1144 and oipl_ordr_num = 3;
1145
1146 l_option3_unit varchar2(30);
1147 Begin
1148 open cur_option3_units;
1149 fetch cur_option3_units into l_option3_unit;
1150 close cur_option3_units;
1151 return l_option3_unit;
1152 End get_option3_unit;
1153
1154
1155 Function get_option3_elg_sal(
1156 p_group_plan_id in number,
1157 p_lf_evnt_ocrd_dt in Date,
1158 p_oipl_id in number,
1159 p_group_per_in_ler_id in number,
1160 p_pl_id in number,
1161 p_ws_sub_acty_typ_cd in varchar2) return number
1162 is
1163 Begin
1164
1165 populate_person_option3_rec(
1166 p_group_plan_id,
1167 p_lf_evnt_ocrd_dt,
1168 p_oipl_id,
1169 p_group_per_in_ler_id,
1170 p_pl_id,
1171 p_ws_sub_acty_typ_cd);
1172 return g_opt3_person_rates_rec.ELIG_SAL_VAL;
1173
1174 End get_option3_elg_sal;
1175
1176 Function get_option3_elg_per_sal(
1180 p_group_per_in_ler_id in number,
1177 p_group_plan_id in number,
1178 p_lf_evnt_ocrd_dt in Date,
1179 p_oipl_id in number,
1181 p_pl_id in number,
1182 p_ws_sub_acty_typ_cd in varchar2) return number
1183 Is
1184 Begin
1185
1186 populate_person_option3_rec(
1187 p_group_plan_id,
1188 p_lf_evnt_ocrd_dt,
1189 p_oipl_id,
1190 p_group_per_in_ler_id,
1191 p_pl_id,
1192 p_ws_sub_acty_typ_cd);
1193 if (g_opt3_person_rates_rec.elig_sal_val <> 0) then
1194 return round( (g_opt3_person_rates_rec.ws_val / g_opt3_person_rates_rec.elig_sal_val) * 100,2);
1195 else
1196 return to_number(null);
1197 end if;
1198
1199
1200 End get_option3_elg_per_sal;
1201
1202 Function get_option3_rate_reco_amt(
1203 p_group_plan_id in number,
1204 p_lf_evnt_ocrd_dt in Date,
1205 p_oipl_id in number,
1206 p_group_per_in_ler_id in number,
1207 p_pl_id in number,
1208 p_ws_sub_acty_typ_cd in varchar2) return number
1209 is
1210 Begin
1211 populate_person_option3_rec(
1212 p_group_plan_id,
1213 p_lf_evnt_ocrd_dt,
1214 p_oipl_id,
1215 p_group_per_in_ler_id,
1216 p_pl_id,
1217 p_ws_sub_acty_typ_cd);
1218 return g_opt3_person_rates_rec.REC_VAL;
1219
1220 End get_option3_rate_reco_amt;
1221
1222 Function get_option3_rate_oth_sal(
1223 p_group_plan_id in number,
1224 p_lf_evnt_ocrd_dt in Date,
1225 p_oipl_id in number,
1226 p_group_per_in_ler_id in number,
1227 p_pl_id in number,
1228 p_ws_sub_acty_typ_cd in varchar2) return number
1229 is
1230 Begin
1231 populate_person_option3_rec(
1232 p_group_plan_id,
1233 p_lf_evnt_ocrd_dt,
1234 p_oipl_id,
1235 p_group_per_in_ler_id,
1236 p_pl_id,
1237 p_ws_sub_acty_typ_cd);
1238 return g_opt3_person_rates_rec.OTH_COMP_VAL;
1239
1240 End get_option3_rate_oth_sal;
1241
1242 Function get_option3_rate_sta_sal(
1243 p_group_plan_id in number,
1244 p_lf_evnt_ocrd_dt in Date,
1245 p_oipl_id in number,
1246 p_group_per_in_ler_id in number,
1247 p_pl_id in number,
1248 p_ws_sub_acty_typ_cd in varchar2) return number
1249 is
1250 Begin
1251 populate_person_option3_rec(
1252 p_group_plan_id,
1253 p_lf_evnt_ocrd_dt,
1254 p_oipl_id,
1255 p_group_per_in_ler_id,
1256 p_pl_id,
1257 p_ws_sub_acty_typ_cd);
1258 return g_opt3_person_rates_rec.STAT_SAL_VAL;
1259
1260 End get_option3_rate_sta_sal;
1261
1262 Function get_option3_rate_tot_comp(
1263 p_group_plan_id in number,
1264 p_lf_evnt_ocrd_dt in Date,
1265 p_oipl_id in number,
1266 p_group_per_in_ler_id in number,
1267 p_pl_id in number,
1268 p_ws_sub_acty_typ_cd in varchar2) return number
1269 is
1270 Begin
1271 populate_person_option3_rec(
1272 p_group_plan_id,
1273 p_lf_evnt_ocrd_dt,
1274 p_oipl_id,
1275 p_group_per_in_ler_id,
1276 p_pl_id,
1277 p_ws_sub_acty_typ_cd);
1278 return g_opt3_person_rates_rec.TOT_COMP_VAL;
1279
1280 End get_option3_rate_tot_comp;
1281
1282
1283 Function get_option3_rate_misc1(
1284 p_group_plan_id in number,
1285 p_lf_evnt_ocrd_dt in Date,
1286 p_oipl_id in number,
1287 p_group_per_in_ler_id in number,
1288 p_pl_id in number,
1289 p_ws_sub_acty_typ_cd in varchar2) return number
1290 is
1291 Begin
1292 populate_person_option3_rec(
1293 p_group_plan_id,
1294 p_lf_evnt_ocrd_dt,
1295 p_oipl_id,
1296 p_group_per_in_ler_id,
1297 p_pl_id,
1298 p_ws_sub_acty_typ_cd);
1299 return g_opt3_person_rates_rec.MISC1_VAL;
1300
1301 End get_option3_rate_misc1;
1302
1303 Function get_option3_rate_misc2(
1304 p_group_plan_id in number,
1305 p_lf_evnt_ocrd_dt in Date,
1306 p_oipl_id in number,
1307 p_group_per_in_ler_id in number,
1308 p_pl_id in number,
1309 p_ws_sub_acty_typ_cd in varchar2) return number
1310 is
1311 Begin
1312 populate_person_option3_rec(
1313 p_group_plan_id,
1314 p_lf_evnt_ocrd_dt,
1315 p_oipl_id,
1316 p_group_per_in_ler_id,
1317 p_pl_id,
1318 p_ws_sub_acty_typ_cd);
1319 return g_opt3_person_rates_rec.MISC2_VAL;
1320
1321 End get_option3_rate_misc2;
1322
1323 Function get_option3_rate_misc3(
1324 p_group_plan_id in number,
1328 p_pl_id in number,
1325 p_lf_evnt_ocrd_dt in Date,
1326 p_oipl_id in number,
1327 p_group_per_in_ler_id in number,
1329 p_ws_sub_acty_typ_cd in varchar2) return number
1330 is
1331 Begin
1332 populate_person_option3_rec(
1333 p_group_plan_id,
1334 p_lf_evnt_ocrd_dt,
1335 p_oipl_id,
1336 p_group_per_in_ler_id,
1337 p_pl_id,
1338 p_ws_sub_acty_typ_cd);
1339 return g_opt3_person_rates_rec.MISC3_VAL;
1340 End get_option3_rate_misc3;
1341
1342 Function get_option4_name(
1343 p_group_plan_id in number,
1344 p_lf_evnt_ocrd_dt in Date,
1345 p_oipl_id in number,
1346 p_group_per_in_ler_id in number,
1347 p_pl_id in number,
1348 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1349 is
1350 cursor cur_option4_name is
1351 Select name
1352 From ben_cwb_pl_dsgn
1353 Where pl_id = p_group_plan_id
1354 And group_pl_id = p_group_plan_id
1355 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1356 -- And oipl_id <> -1
1357 -- And opt_count = 4;
1358 and oipl_ordr_num = 4 ;
1359
1360 l_option4_name varchar2(240);
1361 Begin
1362
1363 open cur_option4_name;
1364 fetch cur_option4_name into l_option4_name;
1365 close cur_option4_name;
1366 return l_option4_name;
1367 End get_option4_name;
1368
1369 Procedure populate_person_option4_rec(
1370 p_group_plan_id in number,
1371 p_lf_evt_ocrd_dt in Date,
1372 p_oipl_id in number,
1373 p_group_per_in_ler_id in number,
1374 p_pl_id in number,
1375 p_ws_sub_acty_typ_cd in varchar2)
1376 Is
1377 cursor cur_option4_rate is
1378 Select bcpr.*
1379 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1380 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1381 And bcpd.group_pl_id = p_group_plan_id
1382 -- AND bcpd.pl_id = p_group_plan_id
1383 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1384 --And bcpd.oipl_id <> -1
1385 -- And bcpd.opt_count = 4
1386 and oipl_ordr_num = 4
1387 And bcpd.pl_id = bcpr.pl_id
1388 And bcpd.group_pl_id = bcpr.group_pl_id
1389 And bcpd.oipl_id = bcpr.oipl_id
1390 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
1391 and bcpr.elig_flag = 'Y';
1392 Begin
1393 g_opt4_person_rates_rec := null;
1394 open cur_option4_rate;
1395 fetch cur_option4_rate into g_opt4_person_rates_rec;
1396 close cur_option4_rate;
1397
1398 End populate_person_option4_rec;
1399
1400 Function get_option4_rate_ws_amt(
1401 p_group_plan_id in number,
1402 p_lf_evnt_ocrd_dt in Date,
1403 p_oipl_id in number,
1404 p_group_per_in_ler_id in number,
1405 p_pl_id in number,
1406 p_ws_sub_acty_typ_cd in varchar2) return number
1407 is
1408 Begin
1409 populate_person_option4_rec(
1410 p_group_plan_id,
1411 p_lf_evnt_ocrd_dt,
1412 p_oipl_id,
1413 p_group_per_in_ler_id,
1414 p_pl_id,
1415 p_ws_sub_acty_typ_cd);
1416 return g_opt4_person_rates_rec.ws_val;
1417
1418 End get_option4_rate_ws_amt;
1419
1420 Function get_option4_unit(
1421 p_group_plan_id in number,
1422 p_lf_evnt_ocrd_dt in Date,
1423 p_oipl_id in number,
1424 p_group_per_in_ler_id in number,
1425 p_pl_id in number,
1426 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1427 Is
1428 -- ws_nnmntry_uom needs to be decoded from lookup table
1429 Cursor cur_option4_units is
1430 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
1431 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1432 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1433 And bcpd.group_pl_id = p_group_plan_id
1434 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1435 And bcpr.pl_id = bcpd.pl_id
1436 And bcpr.group_pl_id = bcpd.group_pl_id
1437 And bcpr.oipl_id = bcpd.oipl_id
1438 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1439 and bcpr.elig_flag = 'Y'
1440 and oipl_ordr_num = 4;
1441
1442 l_option4_unit varchar2(30);
1443 Begin
1444 open cur_option4_units;
1445 fetch cur_option4_units into l_option4_unit;
1446 close cur_option4_units;
1447 return l_option4_unit;
1448 End get_option4_unit;
1449
1450
1451 Function get_option4_elg_sal(
1452 p_group_plan_id in number,
1453 p_lf_evnt_ocrd_dt in Date,
1454 p_oipl_id in number,
1455 p_group_per_in_ler_id in number,
1456 p_pl_id in number,
1457 p_ws_sub_acty_typ_cd in varchar2) return number
1458 is
1459 Begin
1460
1461 populate_person_option4_rec(
1465 p_group_per_in_ler_id,
1462 p_group_plan_id,
1463 p_lf_evnt_ocrd_dt,
1464 p_oipl_id,
1466 p_pl_id,
1467 p_ws_sub_acty_typ_cd);
1468 return g_opt4_person_rates_rec.ELIG_SAL_VAL;
1469
1470 End get_option4_elg_sal;
1471
1472 Function get_option4_elg_per_sal(
1473 p_group_plan_id in number,
1474 p_lf_evnt_ocrd_dt in Date,
1475 p_oipl_id in number,
1476 p_group_per_in_ler_id in number,
1477 p_pl_id in number,
1478 p_ws_sub_acty_typ_cd in varchar2) return number
1479 Is
1480 Begin
1481
1482 populate_person_option4_rec(
1483 p_group_plan_id,
1484 p_lf_evnt_ocrd_dt,
1485 p_oipl_id,
1486 p_group_per_in_ler_id,
1487 p_pl_id,
1488 p_ws_sub_acty_typ_cd);
1489 if (g_opt4_person_rates_rec.elig_sal_val <> 0) then
1490 return round( (g_opt4_person_rates_rec.ws_val / g_opt4_person_rates_rec.elig_sal_val) * 100,2);
1491 else
1492 return to_number(null);
1493 end if;
1494
1495 End get_option4_elg_per_sal;
1496
1497 Function get_option4_rate_reco_amt(
1498 p_group_plan_id in number,
1499 p_lf_evnt_ocrd_dt in Date,
1500 p_oipl_id in number,
1501 p_group_per_in_ler_id in number,
1502 p_pl_id in number,
1503 p_ws_sub_acty_typ_cd in varchar2) return number
1504 is
1505 Begin
1506 populate_person_option4_rec(
1507 p_group_plan_id,
1508 p_lf_evnt_ocrd_dt,
1509 p_oipl_id,
1510 p_group_per_in_ler_id,
1511 p_pl_id,
1512 p_ws_sub_acty_typ_cd);
1513 return g_opt4_person_rates_rec.REC_VAL;
1514
1515 End get_option4_rate_reco_amt;
1516
1517 Function get_option4_rate_oth_sal(
1518 p_group_plan_id in number,
1519 p_lf_evnt_ocrd_dt in Date,
1520 p_oipl_id in number,
1521 p_group_per_in_ler_id in number,
1522 p_pl_id in number,
1523 p_ws_sub_acty_typ_cd in varchar2) return number
1524 is
1525 Begin
1526 populate_person_option4_rec(
1527 p_group_plan_id,
1528 p_lf_evnt_ocrd_dt,
1529 p_oipl_id,
1530 p_group_per_in_ler_id,
1531 p_pl_id,
1532 p_ws_sub_acty_typ_cd);
1533 return g_opt4_person_rates_rec.OTH_COMP_VAL;
1534
1535 End get_option4_rate_oth_sal;
1536
1537 Function get_option4_rate_sta_sal(
1538 p_group_plan_id in number,
1539 p_lf_evnt_ocrd_dt in Date,
1540 p_oipl_id in number,
1541 p_group_per_in_ler_id in number,
1542 p_pl_id in number,
1543 p_ws_sub_acty_typ_cd in varchar2) return number
1544 is
1545 Begin
1546 populate_person_option4_rec(
1547 p_group_plan_id,
1548 p_lf_evnt_ocrd_dt,
1549 p_oipl_id,
1550 p_group_per_in_ler_id,
1551 p_pl_id,
1552 p_ws_sub_acty_typ_cd);
1553 return g_opt4_person_rates_rec.STAT_SAL_VAL;
1554
1555 End get_option4_rate_sta_sal;
1556
1557 Function get_option4_rate_tot_comp(
1558 p_group_plan_id in number,
1559 p_lf_evnt_ocrd_dt in Date,
1560 p_oipl_id in number,
1561 p_group_per_in_ler_id in number,
1562 p_pl_id in number,
1563 p_ws_sub_acty_typ_cd in varchar2) return number
1564 is
1565 Begin
1566 populate_person_option4_rec(
1567 p_group_plan_id,
1568 p_lf_evnt_ocrd_dt,
1569 p_oipl_id,
1570 p_group_per_in_ler_id,
1571 p_pl_id,
1572 p_ws_sub_acty_typ_cd);
1573 return g_opt4_person_rates_rec.TOT_COMP_VAL;
1574
1575 End get_option4_rate_tot_comp;
1576
1577
1578 Function get_option4_rate_misc1(
1579 p_group_plan_id in number,
1580 p_lf_evnt_ocrd_dt in Date,
1581 p_oipl_id in number,
1582 p_group_per_in_ler_id in number,
1583 p_pl_id in number,
1584 p_ws_sub_acty_typ_cd in varchar2) return number
1585 is
1586 Begin
1587 populate_person_option4_rec(
1588 p_group_plan_id,
1589 p_lf_evnt_ocrd_dt,
1590 p_oipl_id,
1591 p_group_per_in_ler_id,
1592 p_pl_id,
1593 p_ws_sub_acty_typ_cd);
1594 return g_opt4_person_rates_rec.MISC1_VAL;
1595
1596 End get_option4_rate_misc1;
1597
1598 Function get_option4_rate_misc2(
1599 p_group_plan_id in number,
1600 p_lf_evnt_ocrd_dt in Date,
1601 p_oipl_id in number,
1602 p_group_per_in_ler_id in number,
1603 p_pl_id in number,
1604 p_ws_sub_acty_typ_cd in varchar2) return number
1605 is
1606 Begin
1607 populate_person_option4_rec(
1608 p_group_plan_id,
1612 p_pl_id,
1609 p_lf_evnt_ocrd_dt,
1610 p_oipl_id,
1611 p_group_per_in_ler_id,
1613 p_ws_sub_acty_typ_cd);
1614 return g_opt4_person_rates_rec.MISC2_VAL;
1615
1616 End get_option4_rate_misc2;
1617
1618 Function get_option4_rate_misc3(
1619 p_group_plan_id in number,
1620 p_lf_evnt_ocrd_dt in Date,
1621 p_oipl_id in number,
1622 p_group_per_in_ler_id in number,
1623 p_pl_id in number,
1624 p_ws_sub_acty_typ_cd in varchar2) return number
1625 is
1626 Begin
1627 populate_person_option4_rec(
1628 p_group_plan_id,
1629 p_lf_evnt_ocrd_dt,
1630 p_oipl_id,
1631 p_group_per_in_ler_id,
1632 p_pl_id,
1633 p_ws_sub_acty_typ_cd);
1634 return g_opt4_person_rates_rec.MISC3_VAL;
1635 End get_option4_rate_misc3;
1636
1637
1638 --
1639 PROCEDURE populate_person_rates_rec(
1640 p_group_plan_id in number,
1641 p_lf_evt_ocrd_dt in Date,
1642 p_oipl_id in number,
1643 p_group_per_in_ler_id in number,
1644 p_pl_id in number,
1645 p_ws_sub_acty_typ_cd in varchar2,
1646 p_new_or_prior in varchar2 ) is
1647
1648 -- *********
1649 -- This next cursor never retreives any record as the person
1650 -- always gets a new group_per_in_ler_id for each cycle. So for
1651 -- one group_per_in_ler_id, we can find only one lf_evt_ocrd_dt
1652 cursor c_prior_person_rate_dt is
1653 select max(lf_evt_ocrd_dt)
1654 from ben_cwb_person_rates
1655 where group_per_in_ler_id = p_group_per_in_ler_id
1656 and group_pl_id = p_group_plan_id
1657 --and pl_id = p_pl_id
1658 and lf_evt_ocrd_dt < p_lf_evt_ocrd_dt
1659 and elig_flag = 'Y';
1660
1661 -- *** change here. commented out group_pl_id and using pl_id
1662 cursor c_person_rates (c_lf_evt_ocrd_dt Date) is
1663 select * from ben_cwb_person_rates
1664 where group_per_in_ler_id = p_group_per_in_ler_id
1665 and group_pl_id = p_group_plan_id
1666 --and pl_id = p_pl_id
1667 and oipl_id = p_oipl_id
1668 and lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
1669 and elig_flag = 'Y';
1670
1671
1672 l_lf_evt_ocrd_dt date;
1673 begin
1674 hr_utility.set_location('Entering populate_person_rates_rec. p_ws_sub_acty_typ_cd='||p_ws_sub_acty_typ_cd,10);
1675 -- if (p_ws_sub_acty_typ_cd ='ICM7') then -- Salary Plan
1676 hr_utility.set_location('p_new_or_prior='||p_ws_sub_acty_typ_cd,10);
1677 if (p_new_or_prior ='NEW') then
1678
1679 g_person_rates_rec := null;
1680 open c_person_rates (p_lf_evt_ocrd_dt);
1681 fetch c_person_rates into g_person_rates_rec;
1682 close c_person_rates;
1683
1684 elsif (p_new_or_prior ='PRIOR') then
1685 open c_prior_person_rate_dt;
1686 fetch c_prior_person_rate_dt into l_lf_evt_ocrd_dt;
1687 close c_prior_person_rate_dt;
1688
1689 if (l_lf_evt_ocrd_dt is not null) then
1690 g_prior_person_rates_rec := null;
1691 open c_person_rates (l_lf_evt_ocrd_dt);
1692 fetch c_person_rates into g_prior_person_rates_rec;
1693 close c_person_rates;
1694 end if;
1695
1696 end if;
1697 -- end if; then -- Salary Plan
1698 end;
1699
1700 FUNCTION get_pay_rate (
1701 p_group_plan_id in number,
1702 p_lf_evnt_ocrd_dt in Date,
1703 p_oipl_id in number,
1704 p_group_per_in_ler_id in number,
1705 p_pl_id in number,
1706 p_ws_sub_acty_typ_cd in varchar2,
1707 p_new_or_prior in varchar2) return number is
1708
1709
1710 l_proposed_salary number := 0;
1711
1712 cursor c_pay_proposal is
1713 select max(nvl(proposed_salary_n,0))
1714 from per_pay_proposals ppp ,ben_cwb_person_rates rts
1715 where rts.pay_proposal_id = ppp.pay_proposal_id
1716 and rts.group_per_in_ler_id = p_group_per_in_ler_id
1717 and rts.group_pl_id = p_group_plan_id
1718 and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1719 and rts.ws_val is not null
1720 and rts.elig_flag = 'Y';
1721 cursor c_prior_pay_proposal is
1722 select nvl(base_salary,0)
1723 from ben_cwb_person_info
1724 where group_per_in_ler_id = p_group_per_in_ler_id;
1725
1726 begin
1727
1728 if (p_new_or_prior = 'NEW') then
1729 open c_pay_proposal;
1730 fetch c_pay_proposal into l_proposed_salary;
1731 close c_pay_proposal;
1732 else
1733 -- we need to get the prior salary
1734 open c_prior_pay_proposal;
1735 fetch c_prior_pay_proposal into l_proposed_salary;
1736 close c_prior_pay_proposal;
1737 end if;
1738 return l_proposed_salary;
1739 end get_pay_rate;
1740
1741
1742
1743 FUNCTION get_pay_rate_change_amount (
1744 p_group_plan_id in number,
1745 p_lf_evt_ocrd_dt in Date,
1746 p_oipl_id in number,
1747 p_group_per_in_ler_id in number,
1748 p_pl_id in number,
1752 l_prior_proposed_salary number;
1749 p_ws_sub_acty_typ_cd in varchar2) return number is
1750
1751 l_new_proposed_salary number;
1753 l_pay_proposal varchar2(30);
1754 begin
1755 l_new_proposed_salary :=
1756 get_pay_rate (
1757 p_group_plan_id,p_lf_evt_ocrd_dt,
1758 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1759 p_ws_sub_acty_typ_cd, 'NEW');
1760
1761 l_prior_proposed_salary :=
1762 get_pay_rate (
1763 p_group_plan_id,p_lf_evt_ocrd_dt,
1764 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1765 p_ws_sub_acty_typ_cd, 'PRIOR');
1766
1767 if (l_new_proposed_salary = l_prior_proposed_salary) then
1768 return 0;
1769 else
1770 return l_new_proposed_salary - l_prior_proposed_salary;
1771 end if;
1772 end get_pay_rate_change_amount;
1773
1774
1775 FUNCTION get_pay_rate_change_percent (
1776 p_group_plan_id in number,
1777 p_lf_evt_ocrd_dt in Date,
1778 p_oipl_id in number,
1779 p_group_per_in_ler_id in number,
1780 p_pl_id in number,
1781 p_ws_sub_acty_typ_cd in varchar2) return number is
1782
1783 l_new_proposed_salary number;
1784 l_prior_proposed_salary number;
1785 l_pay_proposal varchar2(30);
1786 begin
1787 l_new_proposed_salary :=
1788 get_pay_rate (
1789 p_group_plan_id,p_lf_evt_ocrd_dt,
1790 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1791 p_ws_sub_acty_typ_cd, 'NEW');
1792
1793 l_prior_proposed_salary :=
1794 get_pay_rate (
1795 p_group_plan_id,p_lf_evt_ocrd_dt,
1796 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1797 p_ws_sub_acty_typ_cd, 'PRIOR');
1798
1799
1800 if (l_new_proposed_salary = l_prior_proposed_salary) then
1801 return 0;
1802 else
1803 if (l_new_proposed_salary = 0 ) then
1804 return 0;
1805 elsif (l_prior_proposed_salary = 0) then
1806 return 100;
1807 end if;
1808 return round(((l_new_proposed_salary - l_prior_proposed_salary) / l_prior_proposed_salary) * 100,2);
1809 end if;
1810 end get_pay_rate_change_percent;
1811
1812 FUNCTION get_pay_rate_change_date (
1813 p_group_plan_id in number,
1814 p_lf_evnt_ocrd_dt in Date,
1815 p_oipl_id in number,
1816 p_group_per_in_ler_id in number,
1817 p_pl_id in number,
1818 p_ws_sub_acty_typ_cd in varchar2,
1819 p_new_or_prior in varchar2) return varchar2 is
1820
1821
1822 l_change_date varchar2(30);
1823
1824 -- return the comp_posting_date
1825 cursor c_pay_proposal_date is
1826 select max(change_date)
1827 from per_pay_proposals ppp
1828 ,ben_cwb_person_rates rts
1829 where rts.pay_proposal_id = ppp.pay_proposal_id
1830 and rts.group_per_in_ler_id = p_group_per_in_ler_id
1831 and rts.group_pl_id = p_group_plan_id
1832 and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1833 and rts.ws_val is not null
1834 and rts.elig_flag = 'Y';
1835
1836
1837 cursor c_prior_pay_proposal_date is
1838 select BASE_SALARY_CHANGE_DATE
1839 from ben_cwb_person_info
1840 where group_per_in_ler_id = p_group_per_in_ler_id;
1841
1842 begin
1843 -- *********
1844 if (p_new_or_prior = 'NEW') then
1845 open c_pay_proposal_date;
1846 fetch c_pay_proposal_date into l_change_date;
1847 close c_pay_proposal_date;
1848 else
1849 -- we need to get the prior salary
1850 open c_prior_pay_proposal_date;
1851 fetch c_prior_pay_proposal_date into l_change_date;
1852 close c_prior_pay_proposal_date;
1853 end if;
1854
1855 return l_change_date;
1856
1857 end get_pay_rate_change_date;
1858
1859
1860 FUNCTION get_pay_rate_basis (
1861 p_group_plan_id in number,
1862 p_lf_evnt_ocrd_dt in Date,
1863 p_oipl_id in number,
1864 p_group_per_in_ler_id in number,
1865 p_pl_id in number,
1866 p_ws_sub_acty_typ_cd in varchar2) return Varchar2 is
1867
1868 Cursor c_pay_rate_basis is
1869 select initcap(pay_basis)
1870 from per_pay_bases ppb,
1871 per_all_assignments_f paaf
1872 where ppb.pay_basis_id = paaf.pay_basis_id
1873 and sysdate between paaf.effective_start_date and paaf.effective_end_date
1874 and paaf.primary_flag = 'Y'
1875 and paaf.assignment_id = g_person_rates_rec.assignment_id;
1876
1877 l_pay_basis per_pay_bases.pay_basis%Type;
1878 BEGIN
1879
1880 populate_person_rates_rec (
1881 p_group_plan_id,p_lf_evnt_ocrd_dt,
1882 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1883 p_ws_sub_acty_typ_cd, 'NEW');
1884
1885 OPEN c_pay_rate_basis;
1886 fetch c_pay_rate_basis into l_pay_basis;
1887 close c_pay_rate_basis;
1888
1889 return l_pay_basis;
1890 end get_pay_rate_basis;
1891
1892 FUNCTION get_plan_rate_ws_amt (
1893 p_group_plan_id in number,
1894 p_lf_evnt_ocrd_dt in Date,
1895 p_oipl_id in number,
1896 p_group_per_in_ler_id in number,
1897 p_pl_id in number,
1901 p_group_plan_id,p_lf_evnt_ocrd_dt,
1898 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1899 begin
1900 populate_person_rates_rec (
1902 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1903 p_ws_sub_acty_typ_cd, 'NEW');
1904
1905 return g_person_rates_rec.ws_val;
1906 end get_plan_rate_ws_amt;
1907
1908 FUNCTION get_plan_rate_elig_sal (
1909 p_group_plan_id in number,
1910 p_lf_evnt_ocrd_dt in Date,
1911 p_oipl_id in number,
1912 p_group_per_in_ler_id in number,
1913 p_pl_id in number,
1914 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1915 begin
1916 populate_person_rates_rec (
1917 p_group_plan_id,p_lf_evnt_ocrd_dt,
1918 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1919 p_ws_sub_acty_typ_cd, 'NEW');
1920
1921 return g_person_rates_rec.elig_sal_val;
1922 end get_plan_rate_elig_sal;
1923
1924 FUNCTION get_plan_percent_elig_sal (
1925 p_group_plan_id in number,
1926 p_lf_evnt_ocrd_dt in Date,
1927 p_oipl_id in number,
1928 p_group_per_in_ler_id in number,
1929 p_pl_id in number,
1930 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1931 begin
1932 populate_person_rates_rec (
1933 p_group_plan_id,p_lf_evnt_ocrd_dt,
1934 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1935 p_ws_sub_acty_typ_cd, 'NEW');
1936
1937 if (g_person_rates_rec.elig_sal_val <>0 ) then
1938 return round( (g_person_rates_rec.ws_val / g_person_rates_rec.elig_sal_val) * 100,2);
1939 else
1940 return to_number(null);
1941 end if;
1942
1943 end get_plan_percent_elig_sal;
1944
1945 FUNCTION get_plan_rate_rec_amt (
1946 p_group_plan_id in number,
1947 p_lf_evnt_ocrd_dt in Date,
1948 p_oipl_id in number,
1949 p_group_per_in_ler_id in number,
1950 p_pl_id in number,
1951 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1952 begin
1953 populate_person_rates_rec (
1954 p_group_plan_id,p_lf_evnt_ocrd_dt,
1955 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1956 p_ws_sub_acty_typ_cd, 'NEW');
1957
1958 return g_person_rates_rec.rec_val;
1959 end get_plan_rate_rec_amt;
1960
1961 FUNCTION get_plan_rate_other_sal (
1962 p_group_plan_id in number,
1963 p_lf_evnt_ocrd_dt in Date,
1964 p_oipl_id in number,
1965 p_group_per_in_ler_id in number,
1966 p_pl_id in number,
1967 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1968 begin
1969 populate_person_rates_rec (
1970 p_group_plan_id,p_lf_evnt_ocrd_dt,
1971 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1972 p_ws_sub_acty_typ_cd, 'NEW');
1973
1974 return g_person_rates_rec.oth_comp_val;
1975 end get_plan_rate_other_sal;
1976
1977 FUNCTION get_plan_rate_stat_sal (
1978 p_group_plan_id in number,
1979 p_lf_evnt_ocrd_dt in Date,
1980 p_oipl_id in number,
1981 p_group_per_in_ler_id in number,
1982 p_pl_id in number,
1983 p_ws_sub_acty_typ_cd in varchar2 ) return number is
1984 begin
1985 populate_person_rates_rec (
1986 p_group_plan_id,p_lf_evnt_ocrd_dt,
1987 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
1988 p_ws_sub_acty_typ_cd, 'NEW');
1989
1990 return g_person_rates_rec.stat_sal_val;
1991 end get_plan_rate_stat_sal;
1992
1993 FUNCTION get_plan_rate_total_comp (
1994 p_group_plan_id in number,
1995 p_lf_evnt_ocrd_dt in Date,
1996 p_oipl_id in number,
1997 p_group_per_in_ler_id in number,
1998 p_pl_id in number,
1999 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2000 begin
2001 populate_person_rates_rec (
2002 p_group_plan_id,p_lf_evnt_ocrd_dt,
2003 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2004 p_ws_sub_acty_typ_cd, 'NEW');
2005
2006 return g_person_rates_rec.tot_comp_val;
2007 end get_plan_rate_total_comp;
2008
2009 FUNCTION get_plan_rate_misc1 (
2010 p_group_plan_id in number,
2011 p_lf_evnt_ocrd_dt in Date,
2012 p_oipl_id in number,
2013 p_group_per_in_ler_id in number,
2014 p_pl_id in number,
2015 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2016 begin
2017 populate_person_rates_rec (
2018 p_group_plan_id,p_lf_evnt_ocrd_dt,
2019 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2020 p_ws_sub_acty_typ_cd, 'NEW');
2021
2022 return g_person_rates_rec.misc1_val;
2023 end get_plan_rate_misc1;
2024
2025 FUNCTION get_plan_rate_misc2 (
2026 p_group_plan_id in number,
2027 p_lf_evnt_ocrd_dt in Date,
2028 p_oipl_id in number,
2029 p_group_per_in_ler_id in number,
2030 p_pl_id in number,
2031 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2032 begin
2033 populate_person_rates_rec (
2034 p_group_plan_id,p_lf_evnt_ocrd_dt,
2035 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2036 p_ws_sub_acty_typ_cd, 'NEW');
2037
2038 return g_person_rates_rec.misc2_val;
2039 end get_plan_rate_misc2;
2040
2041 FUNCTION get_plan_rate_misc3 (
2042 p_group_plan_id in number,
2046 p_pl_id in number,
2043 p_lf_evnt_ocrd_dt in Date,
2044 p_oipl_id in number,
2045 p_group_per_in_ler_id in number,
2047 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2048 begin
2049 populate_person_rates_rec (
2050 p_group_plan_id,p_lf_evnt_ocrd_dt,
2051 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2052 p_ws_sub_acty_typ_cd, 'NEW');
2053
2054 return g_person_rates_rec.misc3_val;
2055 end get_plan_rate_misc3;
2056
2057 PROCEDURE populate_asgn_txn_rec (
2058 p_assignment_id in number,
2059 p_asg_updt_eff_date in date) IS
2060
2061 BEGIN
2062
2063 --IF (nvl(g_asgn_txn_rec.assignment_id,-1) <> p_assignment_id OR
2064 -- nvl(to_date(g_asgn_txn_rec.asg_updt_eff_date,'RRRR/MM/DD'),hr_general.start_of_time ) <> p_asg_updt_eff_date ) THEN
2065
2066 g_asgn_txn_rec := null;
2067 OPEN g_cursor_asgn_txn (p_assignment_id, to_char(p_asg_updt_eff_date,'RRRR/MM/DD'));
2068 FETCH g_cursor_asgn_txn into g_asgn_txn_rec;
2069 CLOSE g_cursor_asgn_txn;
2070
2071 --END IF;
2072 END populate_asgn_txn_rec;
2073
2074 FUNCTION get_new_job (
2075 p_assignment_id in number,
2076 p_asg_updt_eff_date in date) return varchar2 IS
2077 begin
2078 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2079
2080 if ( g_asgn_txn_rec.job_id is not null) then
2081 return ( hr_general.decode_job(g_asgn_txn_rec.job_id));
2082 end if;
2083
2084 return null;
2085 end get_new_job;
2086
2087 FUNCTION get_new_position (
2088 p_assignment_id in number,
2089 p_asg_updt_eff_date in date) return varchar2 IS
2090 begin
2091 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2092 -- Using DECODE_POSITION_LATEST_NAME instead of DECODE_POSITION : Anadi
2093 if ( g_asgn_txn_rec.position_id is not null) then
2094 return ( hr_general.DECODE_POSITION_LATEST_NAME(g_asgn_txn_rec.position_id));
2095 end if;
2096
2097 return null;
2098 end get_new_position;
2099
2100 FUNCTION get_new_grade (
2101 p_assignment_id in number,
2102 p_asg_updt_eff_date in date) return varchar2 IS
2103 begin
2104 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2105
2106 if ( g_asgn_txn_rec.grade_id is not null) then
2107 return ( hr_general.decode_grade (g_asgn_txn_rec.grade_id));
2108 end if;
2109
2110 return null;
2111 end get_new_grade ;
2112
2113
2114 FUNCTION get_new_people_group(
2115 p_assignment_id in number,
2116 p_asg_updt_eff_date in date) return varchar2 IS
2117 begin
2118 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2119
2120 if ( g_asgn_txn_rec.people_group_id is not null) then
2121 return ( hr_general.decode_people_group (g_asgn_txn_rec.people_group_id));
2122 end if;
2123
2124 return null;
2125 end get_new_people_group ;
2126
2127 FUNCTION get_new_asgn_flex(
2128 p_assignment_id in number,
2129 p_asg_updt_eff_date in date,
2130 p_asg_flex_num in number
2131 ) return varchar2 IS
2132
2133 l_asgn_flex ben_transaction.attribute11%Type;
2134 begin
2135 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2136
2137 if (p_asg_flex_num = 1) then
2138 l_asgn_flex := g_asgn_txn_rec.asgn_flex1;
2139 elsif (p_asg_flex_num = 2) then
2140 l_asgn_flex := g_asgn_txn_rec.asgn_flex2;
2141 elsif (p_asg_flex_num = 3) then
2142 l_asgn_flex := g_asgn_txn_rec.asgn_flex3;
2143 elsif (p_asg_flex_num = 4) then
2144 l_asgn_flex := g_asgn_txn_rec.asgn_flex4;
2145 elsif (p_asg_flex_num = 5) then
2146 l_asgn_flex := g_asgn_txn_rec.asgn_flex5;
2147 elsif (p_asg_flex_num = 6) then
2148 l_asgn_flex := g_asgn_txn_rec.asgn_flex6;
2149 elsif (p_asg_flex_num = 7) then
2150 l_asgn_flex := g_asgn_txn_rec.asgn_flex7;
2151 elsif (p_asg_flex_num = 8) then
2152 l_asgn_flex := g_asgn_txn_rec.asgn_flex8;
2153 elsif (p_asg_flex_num = 9) then
2154 l_asgn_flex := g_asgn_txn_rec.asgn_flex9;
2155 elsif (p_asg_flex_num = 10) then
2156 l_asgn_flex := g_asgn_txn_rec.asgn_flex10;
2157 elsif (p_asg_flex_num = 11) then
2158 l_asgn_flex := g_asgn_txn_rec.asgn_flex11;
2159 elsif (p_asg_flex_num = 12) then
2160 l_asgn_flex := g_asgn_txn_rec.asgn_flex12;
2161 elsif (p_asg_flex_num = 13) then
2162 l_asgn_flex := g_asgn_txn_rec.asgn_flex13;
2163 elsif (p_asg_flex_num = 14) then
2164 l_asgn_flex := g_asgn_txn_rec.asgn_flex14;
2165 elsif (p_asg_flex_num = 15) then
2166 l_asgn_flex := g_asgn_txn_rec.asgn_flex15;
2167 elsif (p_asg_flex_num = 16) then
2168 l_asgn_flex := g_asgn_txn_rec.asgn_flex16;
2169 elsif (p_asg_flex_num = 17) then
2170 l_asgn_flex := g_asgn_txn_rec.asgn_flex17;
2171 elsif (p_asg_flex_num = 18) then
2172 l_asgn_flex := g_asgn_txn_rec.asgn_flex18;
2173 elsif (p_asg_flex_num = 19) then
2174 l_asgn_flex := g_asgn_txn_rec.asgn_flex19;
2175 elsif (p_asg_flex_num = 20) then
2176 l_asgn_flex := g_asgn_txn_rec.asgn_flex20;
2177 elsif (p_asg_flex_num = 21) then
2178 l_asgn_flex := g_asgn_txn_rec.asgn_flex21;
2182 l_asgn_flex := g_asgn_txn_rec.asgn_flex23;
2179 elsif (p_asg_flex_num = 22) then
2180 l_asgn_flex := g_asgn_txn_rec.asgn_flex22;
2181 elsif (p_asg_flex_num = 23) then
2183 elsif (p_asg_flex_num = 24) then
2184 l_asgn_flex := g_asgn_txn_rec.asgn_flex24;
2185 elsif (p_asg_flex_num = 25) then
2186 l_asgn_flex := g_asgn_txn_rec.asgn_flex25;
2187 elsif (p_asg_flex_num = 26) then
2188 l_asgn_flex := g_asgn_txn_rec.asgn_flex26;
2189 elsif (p_asg_flex_num = 27) then
2190 l_asgn_flex := g_asgn_txn_rec.asgn_flex27;
2191 elsif (p_asg_flex_num = 28) then
2192 l_asgn_flex := g_asgn_txn_rec.asgn_flex28;
2193 elsif (p_asg_flex_num = 29) then
2194 l_asgn_flex := g_asgn_txn_rec.asgn_flex29;
2195 elsif (p_asg_flex_num = 30) then
2196 l_asgn_flex := g_asgn_txn_rec.asgn_flex30;
2197 end if;
2198
2199 return l_asgn_flex;
2200
2201 end get_new_asgn_flex;
2202
2203 function get_new_perf_rating (
2204 p_assignment_id in number,
2205 p_perf_revw_strt_dt in date,
2206 p_emp_interview_typ_cd in varchar2 ) return varchar2 IS
2207
2208 CURSOR c_perf_rate is
2209 select hr_general.decode_lookup('PERFORMANCE_RATING',attribute3)
2210 From ben_transaction
2211 where transaction_id = p_assignment_id
2212 and transaction_type = 'CWBPERF'||to_char(p_perf_revw_strt_dt,'rrrr/mm/dd')
2213 ||p_emp_interview_typ_cd;
2214
2215 l_perf_rating ben_transaction.attribute3%Type;
2216 begin
2217 open c_perf_rate;
2218 fetch c_perf_rate into l_perf_rating;
2219 close c_perf_rate;
2220
2221 return l_perf_rating;
2222
2223 end get_new_perf_rating;
2224
2225 --
2226 function get_group_short_name (
2227 p_plan_id in number ,
2228 p_lf_evt_ocrd_dt in date ) return varchar2 is
2229 cursor c_doc_short_name is
2230 select pqh_document_short_name
2231 from ben_cwb_pl_dsgn
2232 where pl_id = p_plan_id
2233 and oipl_id = -1
2234 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
2235
2236 l_short_name pqh_documents_f.short_name%type;
2237 begin
2238
2239 if (p_plan_id is null) then
2240 return null;
2241 end if;
2242
2243 open c_doc_short_name;
2244 fetch c_doc_short_name into l_short_name;
2245 close c_doc_short_name;
2246
2247 return l_short_name;
2248 end get_group_short_name;
2249
2250
2251 procedure populate_ws_mgr(p_group_per_in_ler_id in number) is
2252
2253 cursor c_ws_mgr_name is
2254 select info.full_name, info.brief_name, info.custom_name
2255 from ben_cwb_person_info info,
2256 ben_cwb_group_hrchy hrchy
2257 where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
2258 and hrchy.mgr_per_in_ler_id = info.group_per_in_ler_id
2259 and hrchy.lvl_num = 1;
2260 begin
2261
2262 open c_ws_mgr_name;
2263 g_ws_mgr_full_name := null;
2264 g_ws_mgr_brief_name := null;
2265 g_ws_mgr_custom_name := null;
2266 fetch c_ws_mgr_name into g_ws_mgr_full_name,g_ws_mgr_brief_name,g_ws_mgr_custom_name;
2267 close c_ws_mgr_name;
2268
2269 end populate_ws_mgr;
2270
2271
2272 function get_ws_mgr_full_name(p_group_per_in_ler_id in number) return varchar2 is
2273 begin
2274 --if (g_ws_mgr_full_name is null) then
2275 populate_ws_mgr(p_group_per_in_ler_id);
2276 --end if;
2277 return g_ws_mgr_full_name;
2278 end get_ws_mgr_full_name;
2279
2280
2281 function get_ws_mgr_brief_name(p_group_per_in_ler_id in number) return varchar2 is
2282 begin
2283 --if (g_ws_mgr_brief_name is null) then
2284 populate_ws_mgr(p_group_per_in_ler_id);
2285 --end if;
2286 return g_ws_mgr_brief_name;
2287 end get_ws_mgr_brief_name;
2288
2289
2290 function get_ws_mgr_custom_name(p_group_per_in_ler_id in number) return varchar2 is
2291 begin
2292 --if (g_ws_mgr_custom_name is null) then
2293 populate_ws_mgr(p_group_per_in_ler_id);
2294 --end if;
2295 return g_ws_mgr_custom_name;
2296 end get_ws_mgr_custom_name;
2297
2298
2299 --
2300
2301 Function get_option_currency(
2302 p_group_plan_id in number,
2303 p_lf_evnt_ocrd_dt in Date,
2304 p_oipl_id in number,
2305 p_group_per_in_ler_id in number,
2306 p_pl_id in number,
2307 p_ws_sub_acty_typ_cd in varchar2,
2308 p_oipl_ordr_num in number) return varchar2
2309 Is
2310
2311 Cursor cur_option_currency is
2312 Select currency
2313 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
2314 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
2315 And bcpd.group_pl_id = p_group_plan_id
2316 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
2317 And bcpr.pl_id = bcpd.pl_id
2318 And bcpr.group_pl_id = bcpd.group_pl_id
2319 And bcpr.oipl_id = bcpd.oipl_id
2320 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
2321 And bcpr.elig_flag = 'Y'
2322 And oipl_ordr_num = p_oipl_ordr_num;
2323
2324 l_option_currency varchar2(30);
2325 Begin
2326 open cur_option_currency;
2327 fetch cur_option_currency into l_option_currency;
2328 close cur_option_currency;
2329 return l_option_currency;
2330 End get_option_currency;
2331
2332
2333
2334 Function get_option1_currency(
2335 p_group_plan_id in number,
2336 p_lf_evnt_ocrd_dt in Date,
2337 p_oipl_id in number,
2338 p_group_per_in_ler_id in number,
2339 p_pl_id in number,
2340 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2341 Is
2342 l_option1_currency varchar2(30);
2343 Begin
2344 l_option1_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2345 p_oipl_id,p_group_per_in_ler_id,
2346 p_pl_id,p_ws_sub_acty_typ_cd,1);
2347 return l_option1_currency;
2348 End get_option1_currency;
2349
2350 Function get_option2_currency(
2351 p_group_plan_id in number,
2352 p_lf_evnt_ocrd_dt in Date,
2353 p_oipl_id in number,
2354 p_group_per_in_ler_id in number,
2355 p_pl_id in number,
2356 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2357 Is
2358 l_option2_currency varchar2(30);
2359 Begin
2360 l_option2_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2361 p_oipl_id,p_group_per_in_ler_id,
2362 p_pl_id,p_ws_sub_acty_typ_cd,2);
2363 return l_option2_currency;
2364 End get_option2_currency;
2365
2366 Function get_option3_currency(
2367 p_group_plan_id in number,
2368 p_lf_evnt_ocrd_dt in Date,
2369 p_oipl_id in number,
2370 p_group_per_in_ler_id in number,
2371 p_pl_id in number,
2372 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2373 Is
2374 l_option3_currency varchar2(30);
2375 Begin
2376 l_option3_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2377 p_oipl_id,p_group_per_in_ler_id,
2378 p_pl_id,p_ws_sub_acty_typ_cd,3);
2379 return l_option3_currency;
2380 End get_option3_currency;
2381
2382 Function get_option4_currency(
2383 p_group_plan_id in number,
2384 p_lf_evnt_ocrd_dt in Date,
2385 p_oipl_id in number,
2386 p_group_per_in_ler_id in number,
2387 p_pl_id in number,
2388 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2389 Is
2390 l_option4_currency varchar2(30);
2391 Begin
2392 l_option4_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2393 p_oipl_id,p_group_per_in_ler_id,
2394 p_pl_id,p_ws_sub_acty_typ_cd,4);
2395 return l_option4_currency;
2396 End get_option4_currency;
2397
2398 FUNCTION get_plan_rate_start_dt (
2399 p_group_plan_id in number,
2400 p_lf_evnt_ocrd_dt in Date,
2404 p_ws_sub_acty_typ_cd in varchar2 ) return varchar2 is
2401 p_oipl_id in number,
2402 p_group_per_in_ler_id in number,
2403 p_pl_id in number,
2405 begin
2406 populate_person_rates_rec (
2407 p_group_plan_id,p_lf_evnt_ocrd_dt,
2408 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2409 p_ws_sub_acty_typ_cd, 'NEW');
2410 return g_person_rates_rec.ws_rt_start_date;
2411 end get_plan_rate_start_dt;
2412
2413 Function get_option1_rate_start_dt(
2414 p_group_plan_id in number,
2415 p_lf_evnt_ocrd_dt in Date,
2416 p_oipl_id in number,
2417 p_group_per_in_ler_id in number,
2418 p_pl_id in number,
2419 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2420 Begin
2421 populate_person_option1_rec(
2422 p_group_plan_id,
2423 p_lf_evnt_ocrd_dt,
2424 p_oipl_id,
2425 p_group_per_in_ler_id,
2426 p_pl_id,
2427 p_ws_sub_acty_typ_cd);
2428 return g_opt1_person_rates_rec.ws_rt_start_date;
2429 end get_option1_rate_start_dt;
2430
2431 Function get_option2_rate_start_dt(
2432 p_group_plan_id in number,
2433 p_lf_evnt_ocrd_dt in Date,
2434 p_oipl_id in number,
2435 p_group_per_in_ler_id in number,
2436 p_pl_id in number,
2437 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2438 Begin
2439 populate_person_option2_rec(
2440 p_group_plan_id,
2441 p_lf_evnt_ocrd_dt,
2442 p_oipl_id,
2443 p_group_per_in_ler_id,
2444 p_pl_id,
2445 p_ws_sub_acty_typ_cd);
2446 return g_opt2_person_rates_rec.ws_rt_start_date;
2447 end get_option2_rate_start_dt;
2448
2449 Function get_option3_rate_start_dt(
2450 p_group_plan_id in number,
2451 p_lf_evnt_ocrd_dt in Date,
2452 p_oipl_id in number,
2453 p_group_per_in_ler_id in number,
2454 p_pl_id in number,
2455 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2456 Begin
2457 populate_person_option3_rec(
2458 p_group_plan_id,
2459 p_lf_evnt_ocrd_dt,
2460 p_oipl_id,
2461 p_group_per_in_ler_id,
2462 p_pl_id,
2463 p_ws_sub_acty_typ_cd);
2464 return g_opt3_person_rates_rec.ws_rt_start_date;
2465 end get_option3_rate_start_dt;
2466
2467 Function get_option4_rate_start_dt(
2468 p_group_plan_id in number,
2469 p_lf_evnt_ocrd_dt in Date,
2470 p_oipl_id in number,
2471 p_group_per_in_ler_id in number,
2472 p_pl_id in number,
2473 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2474 Begin
2475 populate_person_option4_rec(
2476 p_group_plan_id,
2477 p_lf_evnt_ocrd_dt,
2478 p_oipl_id,
2479 p_group_per_in_ler_id,
2480 p_pl_id,
2481 p_ws_sub_acty_typ_cd);
2482 return g_opt4_person_rates_rec.ws_rt_start_date;
2483 end get_option4_rate_start_dt;
2484
2485
2486 --
2487
2488 /* ---------------------------------------------------------------------
2489 END -- Changes for Printable document
2490 --------------------------------------------------------------------- */
2491 END;
[Home] [Help]