1 package body ben_cwb_utils as
2 /* $Header: bencwbutils.pkb 120.20.12020000.6 2013/01/29 14:57:24 kmsuresh 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 PROCEDURE create_perf_benefit_actions
86 (
87 p_validate in boolean default false
88 ,p_benefit_action_id out nocopy number
89 ,p_process_date in date default SYSDATE
90 ,p_uneai_effective_date in date default null
91 ,p_mode_cd in varchar2 default 'W'
92 ,p_derivable_factors_flag in varchar2 default 'N'
93 ,p_close_uneai_flag in varchar2 default 'N'
94 ,p_validate_flag in varchar2 default 'N'
95 ,p_person_id in number default null
96 ,p_person_type_id in number default null
97 ,p_pgm_id in number default null
98 ,p_business_group_id in number default null
102 ,p_no_plans_flag in varchar2 default 'N'
99 ,p_pl_id in number default null
100 ,p_popl_enrt_typ_cycl_id in number default null
101 ,p_no_programs_flag in varchar2 default 'N'
103 ,p_comp_selection_rl in number default null
104 ,p_person_selection_rl in number default null
105 ,p_ler_id in number default null
106 ,p_organization_id in number default null
107 ,p_benfts_grp_id in number default null
108 ,p_location_id in number default null
109 ,p_pstl_zip_rng_id in number default null
110 ,p_rptg_grp_id in number default null
111 ,p_pl_typ_id in number default null
112 ,p_opt_id in number default null
113 ,p_eligy_prfl_id in number default null
114 ,p_vrbl_rt_prfl_id in number default null
115 ,p_legal_entity_id in number default null
116 ,p_payroll_id in number default null
117 ,p_debug_messages_flag in varchar2 default 'N'
118 ,p_cm_trgr_typ_cd in varchar2 default null
119 ,p_cm_typ_id in number default null
120 ,p_age_fctr_id in number default null
121 ,p_min_age in number default null
122 ,p_max_age in number default null
123 ,p_los_fctr_id in number default null
124 ,p_min_los in number default null
125 ,p_max_los in number default null
126 ,p_cmbn_age_los_fctr_id in number default null
127 ,p_min_cmbn in number default null
128 ,p_max_cmbn in number default null
129 ,p_date_from in date default null
130 ,p_elig_enrol_cd in varchar2 default null
131 ,p_actn_typ_id in number default null
132 ,p_use_fctr_to_sel_flag in varchar2 default 'N'
133 ,p_los_det_to_use_cd in varchar2 default null
134 ,p_audit_log_flag in varchar2 default 'N'
135 ,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
136 ,p_lf_evt_ocrd_dt in date default null
137 ,p_ptnl_ler_for_per_stat_cd in varchar2 default null
138 ,p_bft_attribute_category in varchar2 default null
139 ,p_bft_attribute1 in varchar2 default null
140 ,p_bft_attribute3 in varchar2 default null
141 ,p_bft_attribute4 in varchar2 default null
142 ,p_bft_attribute5 in varchar2 default null
143 ,p_bft_attribute6 in varchar2 default null
144 ,p_bft_attribute7 in varchar2 default null
145 ,p_bft_attribute8 in varchar2 default null
146 ,p_bft_attribute9 in varchar2 default null
147 ,p_bft_attribute10 in varchar2 default null
148 ,p_bft_attribute11 in varchar2 default null
149 ,p_bft_attribute12 in varchar2 default null
150 ,p_bft_attribute13 in varchar2 default null
151 ,p_bft_attribute14 in varchar2 default null
152 ,p_bft_attribute15 in varchar2 default null
153 ,p_bft_attribute16 in varchar2 default null
154 ,p_bft_attribute17 in varchar2 default null
155 ,p_bft_attribute18 in varchar2 default null
156 ,p_bft_attribute19 in varchar2 default null
157 ,p_bft_attribute20 in varchar2 default null
158 ,p_bft_attribute21 in varchar2 default null
159 ,p_bft_attribute22 in varchar2 default null
160 ,p_bft_attribute23 in varchar2 default null
161 ,p_bft_attribute24 in varchar2 default null
162 ,p_bft_attribute25 in varchar2 default null
163 ,p_bft_attribute26 in varchar2 default null
164 ,p_bft_attribute27 in varchar2 default null
165 ,p_bft_attribute28 in varchar2 default null
166 ,p_bft_attribute29 in varchar2 default null
167 ,p_bft_attribute30 in varchar2 default null
168 ,p_request_id in number default null
169 ,p_program_application_id in number default null
170 ,p_program_id in number default null
171 ,p_program_update_date in date default null
172 ,p_object_version_number out nocopy number
173 ,p_effective_date in date default null
174 ,p_enrt_perd_id in number default null
175 ,p_inelg_action_cd in varchar2 default null
176 ,p_org_hierarchy_id in number default null
177 ,p_org_starting_node_id in number default null
178 ,p_grade_ladder_id in number default null
179 ,p_asg_events_to_all_sel_dt in varchar2 default null
180 ,p_rate_id in number default null
181 ,p_per_sel_dt_cd in varchar2 default null
182 ,p_per_sel_freq_cd in varchar2 default null
183 ,p_per_sel_dt_from in date default null
184 ,p_per_sel_dt_to in date default null
185 ,p_year_from in number default null
186 ,p_year_to in number default null
187 ,p_cagr_id in number default null
188 ,p_qual_type in number default null
189 ,p_qual_status in varchar2 default null
190 ,p_concat_segs in varchar2 default null
191 ,p_grant_price_val in number default null
192 )
193
194
195 is
196 --
197 -- Declare cursors and local variables
198 --
199 l_benefit_action_id ben_benefit_actions.benefit_action_id%TYPE;
200 l_object_version_number ben_benefit_actions.object_version_number%TYPE;
201 --
202 begin
203 --
204 -- Issue a savepoint if operating in validation only mode
205 --
206 savepoint create_perf_benefit_actions;
207
208 --
209 -- Process Logic
210 --
211 l_object_version_number := 1;
212 --
213 select ben_benefit_actions_s.nextval
214 into l_benefit_action_id
215 from sys.dual;
216 --
217 insert into ben_benefit_actions
218 (benefit_action_id
219 ,process_date
220 ,uneai_effective_date
221 ,mode_cd
222 ,derivable_factors_flag
223 ,close_uneai_flag
224 ,validate_flag
225 ,person_id
226 ,person_type_id
227 ,pgm_id
228 ,business_group_id
229 ,pl_id
230 ,popl_enrt_typ_cycl_id
231 ,no_programs_flag
232 ,no_plans_flag
233 ,comp_selection_rl
234 ,person_selection_rl
235 ,ler_id
236 ,organization_id
237 ,benfts_grp_id
238 ,location_id
239 ,pstl_zip_rng_id
240 ,rptg_grp_id
241 ,pl_typ_id
242 ,opt_id
243 ,eligy_prfl_id
244 ,vrbl_rt_prfl_id
245 ,legal_entity_id
246 ,payroll_id
247 ,debug_messages_flag
248 ,cm_trgr_typ_cd
249 ,cm_typ_id
250 ,age_fctr_id
251 ,min_age
252 ,max_age
253 ,los_fctr_id
254 ,min_los
255 ,max_los
256 ,cmbn_age_los_fctr_id
257 ,min_cmbn
258 ,max_cmbn
259 ,date_from
260 ,elig_enrol_cd
261 ,actn_typ_id
262 ,use_fctr_to_sel_flag
263 ,los_det_to_use_cd
264 ,audit_log_flag
265 ,lmt_prpnip_by_org_flag
266 ,lf_evt_ocrd_dt
267 ,ptnl_ler_for_per_stat_cd
268 ,bft_attribute_category
269 ,bft_attribute1
270 ,bft_attribute3
271 ,bft_attribute4
272 ,bft_attribute5
273 ,bft_attribute6
274 ,bft_attribute7
275 ,bft_attribute8
276 ,bft_attribute9
277 ,bft_attribute10
278 ,bft_attribute11
279 ,bft_attribute12
280 ,bft_attribute13
281 ,bft_attribute14
282 ,bft_attribute15
283 ,bft_attribute16
284 ,bft_attribute17
285 ,bft_attribute18
286 ,bft_attribute19
287 ,bft_attribute20
288 ,bft_attribute21
289 ,bft_attribute22
290 ,bft_attribute23
291 ,bft_attribute24
292 ,bft_attribute25
293 ,bft_attribute26
294 ,bft_attribute27
295 ,bft_attribute28
296 ,bft_attribute29
297 ,bft_attribute30
298 ,request_id
299 ,program_application_id
300 ,program_id
301 ,program_update_date
302 ,object_version_number
303 ,enrt_perd_id
304 ,inelg_action_cd
305 ,org_hierarchy_id
306 ,org_starting_node_id
307 ,grade_ladder_id
308 ,asg_events_to_all_sel_dt
309 ,rate_id
310 ,per_sel_dt_cd
311 ,per_sel_freq_cd
312 ,per_sel_dt_from
313 ,per_sel_dt_to
314 ,year_from
315 ,year_to
316 ,cagr_id
317 ,qual_type
318 ,qual_status
319 ,concat_segs
320 ,grant_price_val)
321 values
322 (l_benefit_action_id
323 ,p_process_date
324 ,p_uneai_effective_date
325 ,p_mode_cd
326 ,p_derivable_factors_flag
327 ,p_close_uneai_flag
328 ,p_validate_flag
329 ,p_person_id
330 ,p_person_type_id
331 ,p_pgm_id
332 ,p_business_group_id
333 ,p_pl_id
334 ,p_popl_enrt_typ_cycl_id
335 ,p_no_programs_flag
336 ,p_no_plans_flag
337 ,p_comp_selection_rl
338 ,p_person_selection_rl
339 ,p_ler_id
340 ,p_organization_id
341 ,p_benfts_grp_id
342 ,p_location_id
343 ,p_pstl_zip_rng_id
344 ,p_rptg_grp_id
345 ,p_pl_typ_id
346 ,p_opt_id
347 ,p_eligy_prfl_id
348 ,p_vrbl_rt_prfl_id
349 ,p_legal_entity_id
350 ,p_payroll_id
354 ,p_age_fctr_id
351 ,p_debug_messages_flag
352 ,p_cm_trgr_typ_cd
353 ,p_cm_typ_id
355 ,p_min_age
356 ,p_max_age
357 ,p_los_fctr_id
358 ,p_min_los
359 ,p_max_los
360 ,p_cmbn_age_los_fctr_id
361 ,p_min_cmbn
362 ,p_max_cmbn
363 ,p_date_from
364 ,p_elig_enrol_cd
365 ,p_actn_typ_id
366 ,p_use_fctr_to_sel_flag
367 ,p_los_det_to_use_cd
368 ,p_audit_log_flag
369 ,p_lmt_prpnip_by_org_flag
370 ,to_char(p_lf_evt_ocrd_dt,'DD-MON-YYYY')
371 ,p_ptnl_ler_for_per_stat_cd
372 ,p_bft_attribute_category
373 ,p_bft_attribute1
374 ,p_bft_attribute3
375 ,p_bft_attribute4
376 ,p_bft_attribute5
377 ,p_bft_attribute6
378 ,p_bft_attribute7
379 ,p_bft_attribute8
380 ,p_bft_attribute9
381 ,p_bft_attribute10
382 ,p_bft_attribute11
383 ,p_bft_attribute12
384 ,p_bft_attribute13
385 ,p_bft_attribute14
386 ,p_bft_attribute15
387 ,p_bft_attribute16
388 ,p_bft_attribute17
389 ,p_bft_attribute18
390 ,p_bft_attribute19
391 ,p_bft_attribute20
392 ,p_bft_attribute21
393 ,p_bft_attribute22
394 ,p_bft_attribute23
395 ,p_bft_attribute24
396 ,p_bft_attribute25
397 ,p_bft_attribute26
398 ,p_bft_attribute27
399 ,p_bft_attribute28
400 ,p_bft_attribute29
401 ,p_bft_attribute30
402 ,p_request_id
403 ,p_program_application_id
404 ,p_program_id
405 ,p_program_update_date
406 ,l_object_version_number
407 ,p_enrt_perd_id
408 ,p_inelg_action_cd
409 ,p_org_hierarchy_id
410 ,p_org_starting_node_id
411 ,p_grade_ladder_id
412 ,p_asg_events_to_all_sel_dt
413 ,p_rate_id
414 ,p_per_sel_dt_cd
415 ,p_per_sel_freq_cd
416 ,p_per_sel_dt_from
417 ,p_per_sel_dt_to
418 ,p_year_from
419 ,p_year_to
420 ,p_cagr_id
421 ,p_qual_type
422 ,p_qual_status
423 ,p_concat_segs
424 ,p_grant_price_val);
425
426
427 --
428 -- When in validation only mode raise the Validate_Enabled exception
429 --
430 -- Set all output arguments
431 --
432 p_benefit_action_id := l_benefit_action_id;
433 p_object_version_number := l_object_version_number;
434 --
435 exception
436 --
437 when hr_api.validate_enabled then
438 --
439 -- As the Validate_Enabled exception has been raised
440 -- we must rollback to the savepoint
441 --
442 ROLLBACK TO create_perf_benefit_actions;
443 --
444 -- Only set output warning arguments
445 -- (Any key or derived arguments must be set to null
446 -- when validation only mode is being used.)
447 --
448 p_benefit_action_id := null;
449 p_object_version_number := null;
450 --hr_utility.set_location(' Leaving:'||l_proc, 80);
451 --
452 when others then
453 --
454 -- A validation or unexpected error has occured
455 --
456 ROLLBACK TO create_perf_benefit_actions;
457 -- nocopy, reset
458 p_benefit_action_id := null;
459 p_object_version_number := null;
460 raise;
461 --
462
463 end create_perf_benefit_actions;
464 --
465 PROCEDURE insert_person_actions (
466 p_per_actn_id_array IN HR_MISC_SS_NUMBER_TABLE
467 , p_per_id IN HR_MISC_SS_NUMBER_TABLE
468 , p_group_per_in_ler_id IN HR_MISC_SS_NUMBER_TABLE
469 , p_benefit_action_id IN NUMBER
470 ,p_is_placeholder IN HR_MISC_SS_NUMBER_TABLE
471 )
472 IS
473 l_num_rows NUMBER := p_per_actn_id_array.COUNT;
474 BEGIN
475 --g_proc := 'insert_person_actions';
476 --write('Time before inserting person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
477 FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
478 --
479 INSERT INTO ben_person_actions
480 (person_action_id
481 , person_id
482 , ler_id
483 , benefit_action_id
484 , action_status_cd
485 , object_version_number
486 , NON_PERSON_CD
487 )
488 VALUES (p_per_actn_id_array (l_count)
489 , p_per_id (l_count)
490 , p_group_per_in_ler_id (l_count)
491 , p_benefit_action_id
492 , 'U'
493 , 1
494 , decode(p_is_placeholder (l_count),1,'Y','N')
495 );
496
497
498 END insert_person_actions;
499 --
500 FUNCTION get_manager_name(p_emp_per_in_ler_id in number,
501 p_level in number)
502 return varchar2
503 is
504
505 Cursor csr_mgr_name
506 is
507 Select bcpi.full_name,
508 bcpi.brief_name,
509 bcpi.custom_name
510 From ben_cwb_person_info bcpi,
511 ben_cwb_group_hrchy bcgh
512 where bcgh.emp_per_in_ler_id = p_emp_per_in_ler_id
513 and bcgh.lvl_num = (select max(lvl_num) - p_level + 1
514 from ben_cwb_group_hrchy
515 where emp_per_in_ler_id = p_emp_per_in_ler_id)
516 and bcgh.lvl_num > 0
517 and bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id;
518
519 name_profile varchar2(2000);
520 manager_names csr_mgr_name%rowtype;
521
522 begin
523
524 name_profile := get_profile ('BEN_DISPLAY_EMPLOYEE_NAME');
525
526 open csr_mgr_name;
527 fetch csr_mgr_name into manager_names;
528 close csr_mgr_name;
529
530 if('FN' = name_profile)
531 then
532 return manager_names.full_name;
533 elsif ('CN' = name_profile)
534 then
535 return manager_names.custom_name;
536 else
537 return manager_names.brief_name;
538 end if;
539
540 end get_manager_name;
541 --
542
543 FUNCTION get_eligibility(p_plan_status in varchar2,
544 p_opt1_status in varchar2,
545 p_opt2_status in varchar2,
546 p_opt3_status in varchar2,
547 p_opt4_status in varchar2
548 )
549 return varchar2
550 is
551 l_elig_count number := 0;
552 l_inelig_count number := 0;
553 begin
554
555 IF p_plan_status is not null THEN
556 IF p_plan_status = 'Y' THEN
557 l_elig_count := l_elig_count +1;
558 ELSE
559 l_inelig_count := l_inelig_count + 1;
560 END IF;
561 END IF;
562
563 IF p_opt1_status is not null THEN
564 IF p_opt1_status = 'Y' THEN
565 l_elig_count := l_elig_count +1;
566 ELSE
567 l_inelig_count := l_inelig_count + 1;
568 END IF;
569 END IF;
570
571 IF p_opt2_status is not null THEN
572 IF p_opt2_status = 'Y' THEN
573 l_elig_count := l_elig_count +1;
574 ELSE
575 l_inelig_count := l_inelig_count + 1;
576 END IF;
577 END IF;
578
579 IF p_opt3_status is not null THEN
580 IF p_opt3_status = 'Y' THEN
581 l_elig_count := l_elig_count +1;
582 ELSE
583 l_inelig_count := l_inelig_count + 1;
584 END IF;
585 END IF;
586
587 IF p_opt4_status is not null THEN
588 IF p_opt4_status = 'Y' THEN
589 l_elig_count := l_elig_count +1;
590 ELSE
591 l_inelig_count := l_inelig_count + 1;
592 END IF;
593 END IF;
594
595 IF l_elig_count > 0 AND l_inelig_count > 0 THEN
596 return 'BOTH';
597 END IF;
598
599 IF l_elig_count = 0 AND l_inelig_count = 0 THEN
600 return 'BOTH';
601 END IF;
602
603 IF l_elig_count > 0 AND l_inelig_count = 0 THEN
604 return 'Y';
605 END IF;
606
607 IF l_elig_count = 0 AND l_inelig_count > 0 THEN
608 return 'N';
609 END IF;
610
611 end;
612
613 FUNCTION get_profile(p_profile_name in varchar2)
614 return varchar2
615 is
616 name_profile varchar2(2000);
617 begin
618 fnd_profile.get (p_profile_name, name_profile);
619 return name_profile;
620 end get_profile;
621 --
622 PROCEDURE get_site_profile (
623 p_profile_1 in varchar2 default null,
624 p_value_1 out nocopy varchar2)
625 IS
626
627 l_defined_z boolean;
628 l_profile fnd_profile_options.profile_option_name%TYPE;
629 CURSOR value_site_profile(v_name varchar2)
630 IS
631 SELECT
632 valu.profile_option_value
633 FROM
634 fnd_profile_options options
635 ,fnd_profile_option_values valu
636 WHERE
637 options.profile_option_name = v_name
638 AND options.start_date_active <= SYSDATE
639 AND nvl (options.end_date_active
640 ,SYSDATE) >= SYSDATE
641 AND options.application_id = valu.application_id
642 AND options.profile_option_id = valu.profile_option_id
643 AND valu.level_id = 10001;
644
645 BEGIN
646 if(p_profile_1 is not null) then
647 l_profile := upper(p_profile_1);
648 open value_site_profile(l_profile);
649 fetch value_site_profile
650 into p_value_1;
651 close value_site_profile;
652 end if;
653 END get_site_profile;
654 --
655 --
656 PROCEDURE get_resp_profile (
657 p_resp_id in number default null,
658 p_profile_1 in varchar2 default null,
659 p_value_1 out nocopy varchar2)
660 IS
661
662 l_defined_z boolean;
663
664 CURSOR value_resp_profile(v_name varchar2, v_resp_id number)
665 IS
666 SELECT valu.profile_option_value
667 FROM fnd_profile_options options
668 ,fnd_profile_option_values valu
669 WHERE options.profile_option_name = upper(v_name)
670 AND options.start_date_active <= sysdate
671 AND nvl(options.end_date_active, sysdate) >= sysdate
675 AND valu.level_value = v_resp_id;
672 AND options.profile_option_id = valu.profile_option_id
673 AND valu.level_value_application_id = 800
674 AND valu.level_id = 10003
676
677 BEGIN
678 if(p_profile_1 is not null) then
679 open value_resp_profile(p_profile_1,p_resp_id);
680 fetch value_resp_profile
681 into p_value_1;
682 close value_resp_profile;
683 end if;
684 END get_resp_profile;
685 --
686 PROCEDURE get_user_profile (
687 p_user_id in number default null,
688 p_profile_1 in varchar2 default null,
689 p_profile_2 in varchar2 default null,
690 p_profile_3 in varchar2 default null,
691 p_profile_4 in varchar2 default null,
692 p_profile_5 in varchar2 default null,
693 p_profile_6 in varchar2 default null,
694 p_profile_7 in varchar2 default null,
695 p_profile_8 in varchar2 default null,
696 p_profile_9 in varchar2 default null,
697 p_profile_10 in varchar2 default null,
698 p_profile_11 in varchar2 default null,
699 p_profile_12 in varchar2 default null,
700 p_profile_13 in varchar2 default null,
701 p_profile_14 in varchar2 default null,
702 p_value_1 out nocopy varchar2,
703 p_value_2 out nocopy varchar2,
704 p_value_3 out nocopy varchar2,
705 p_value_4 out nocopy varchar2,
706 p_value_5 out nocopy varchar2,
707 p_value_6 out nocopy varchar2,
708 p_value_7 out nocopy varchar2,
709 p_value_8 out nocopy varchar2,
710 p_value_9 out nocopy varchar2,
711 p_value_10 out nocopy varchar2,
712 p_value_11 out nocopy varchar2,
713 p_value_12 out nocopy varchar2,
714 p_value_13 out nocopy varchar2,
715 p_value_14 out nocopy varchar2)
716 IS
717
718 l_defined_z boolean;
719
720 CURSOR value_user_profile(v_name varchar2, v_user_id number)
721 IS
722 SELECT valu.profile_option_value
723 FROM fnd_profile_options options
724 ,fnd_profile_option_values valu
725 WHERE options.profile_option_name = upper(v_name)
726 AND options.start_date_active <= sysdate
727 AND nvl(options.end_date_active, sysdate) >= sysdate
728 AND options.profile_option_id = valu.profile_option_id
729 AND valu.application_id = 805
730 AND valu.level_id = 10004
731 AND valu.level_value = v_user_id;
732
733 BEGIN
734 if(p_profile_1 is not null) then
735 open value_user_profile(p_profile_1,p_user_id);
736 fetch value_user_profile
737 into p_value_1;
738 close value_user_profile;
739 end if;
740 if(p_profile_2 is not null) then
741 open value_user_profile(p_profile_2,p_user_id);
742 fetch value_user_profile
743 into p_value_2;
744 close value_user_profile;
745 end if;
746 if(p_profile_3 is not null) then
747 open value_user_profile(p_profile_3,p_user_id);
748 fetch value_user_profile
749 into p_value_3;
750 close value_user_profile;
751 end if;
752 if(p_profile_4 is not null) then
753 open value_user_profile(p_profile_4,p_user_id);
754 fetch value_user_profile
755 into p_value_4;
756 close value_user_profile;
757 end if;
758 if(p_profile_5 is not null) then
759 open value_user_profile(p_profile_5,p_user_id);
760 fetch value_user_profile
761 into p_value_5;
762 close value_user_profile;
763 end if;
764 if(p_profile_6 is not null) then
765 open value_user_profile(p_profile_6,p_user_id);
766 fetch value_user_profile
767 into p_value_6;
768 close value_user_profile;
769 end if;
770 if(p_profile_7 is not null) then
771 open value_user_profile(p_profile_7,p_user_id);
772 fetch value_user_profile
773 into p_value_7;
774 close value_user_profile;
775 end if;
776 if(p_profile_8 is not null) then
777 open value_user_profile(p_profile_8,p_user_id);
778 fetch value_user_profile
779 into p_value_8;
780 close value_user_profile;
781 end if;
782 if(p_profile_9 is not null) then
783 open value_user_profile(p_profile_9,p_user_id);
784 fetch value_user_profile
785 into p_value_9;
786 close value_user_profile;
787 end if;
788 if(p_profile_10 is not null) then
789 open value_user_profile(p_profile_10,p_user_id);
790 fetch value_user_profile
791 into p_value_10;
792 close value_user_profile;
793 end if;
794 if(p_profile_11 is not null) then
795 open value_user_profile(p_profile_11,p_user_id);
796 fetch value_user_profile
797 into p_value_11;
798 close value_user_profile;
799 end if;
800 if(p_profile_12 is not null) then
801 open value_user_profile(p_profile_12,p_user_id);
802 fetch value_user_profile
803 into p_value_12;
804 close value_user_profile;
805 end if;
806 if(p_profile_13 is not null) then
807 open value_user_profile(p_profile_13,p_user_id);
808 fetch value_user_profile
809 into p_value_13;
813 open value_user_profile(p_profile_14,p_user_id);
810 close value_user_profile;
811 end if;
812 if(p_profile_14 is not null) then
814 fetch value_user_profile
815 into p_value_14;
816 close value_user_profile;
817 end if;
818 END get_user_profile;
819 --
820 PROCEDURE get_user_email_profile (
821 p_user_id in number default null,
822 p_profile_1 in varchar2 default null,
823 p_profile_2 in varchar2 default null,
824 p_profile_3 in varchar2 default null,
825 p_profile_4 in varchar2 default null,
826 p_value_1 out nocopy varchar2,
827 p_value_2 out nocopy varchar2,
828 p_value_3 out nocopy varchar2,
829 p_value_4 out nocopy varchar2)
830 IS
831
832 l_defined_z boolean;
833
834 CURSOR value_user_profile(v_name varchar2, v_user_id number)
835 IS
836 SELECT valu.profile_option_value
837 FROM fnd_profile_options options
838 ,fnd_profile_option_values valu
839 WHERE options.profile_option_name = upper(v_name)
840 AND options.start_date_active <= sysdate
841 AND nvl(options.end_date_active, sysdate) >= sysdate
842 AND options.profile_option_id = valu.profile_option_id
843 AND valu.application_id = 805
844 AND valu.level_id = 10004
845 AND valu.level_value = v_user_id;
846
847 BEGIN
848 if(p_profile_1 is not null) then
849 open value_user_profile(p_profile_1,p_user_id);
850 fetch value_user_profile
851 into p_value_1;
852 close value_user_profile;
853 end if;
854 if(p_profile_2 is not null) then
855 open value_user_profile(p_profile_2,p_user_id);
856 fetch value_user_profile
857 into p_value_2;
858 close value_user_profile;
859 end if;
860 if(p_profile_3 is not null) then
861 open value_user_profile(p_profile_3,p_user_id);
862 fetch value_user_profile
863 into p_value_3;
864 close value_user_profile;
865 end if;
866 if(p_profile_4 is not null) then
867 open value_user_profile(p_profile_4,p_user_id);
868 fetch value_user_profile
869 into p_value_4;
870 close value_user_profile;
871 end if;
872 END get_user_email_profile;
873 --
874 FUNCTION get_bdgt_pct_of_elig_sal_decs return number is
875 l_return_value number;
876 begin
877 l_return_value := to_number(get_profile('BEN_CWB_BS_PCT_ES_DECS_DISP'));
878 if l_return_value is null then
879 return 2;
880 elsif l_return_value > 10 then
881 return 10;
882 else
883 return l_return_value;
884 end if;
885 exception
886 when others then
887 return 2;
888 end get_bdgt_pct_of_elig_sal_decs;
889 --
890 FUNCTION get_alloc_pct_of_elig_sal_decs return number is
891 l_return_value number;
892 begin
893 l_return_value := to_number(get_profile('BEN_CWB_WS_PCT_ES_DECS_DISP'));
894 if l_return_value is null then
895 return 2;
896 elsif l_return_value > 10 then
897 return 10;
898 else
899 return l_return_value;
900 end if;
901 exception
902 when others then
903 return 2;
904 end get_alloc_pct_of_elig_sal_decs;
905 --
906 FUNCTION is_person_switchable(p_person_id in number,
907 p_effective_date in date)
908 return varchar2
909 is
910 CURSOR is_person_in_secured_view
911 is
912 SELECT 'x'
913 FROM per_people_f ppf,
914 per_person_types ppt
915 WHERE ppf.person_id = p_person_id
916 AND ppt.person_type_id = ppf.person_type_id
917 AND ppt.system_person_type <> 'EX_EMP_APL'
918 AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
919
920 l_switch varchar2(1);
921
922 begin
923 OPEN is_person_in_secured_view;
924 FETCH is_person_in_secured_view INTO l_switch;
925 if is_person_in_secured_view%NOTFOUND then
926 CLOSE is_person_in_secured_view;
927 return 'N';
928 else
929 CLOSE is_person_in_secured_view;
930 return 'Y';
931 end if;
932 END is_person_switchable;
933
934
935 function add_number_with_null_check(p_orig_val in number,
936 p_new_val in number) return number is
937 begin
938 if p_orig_val is null then
939 return p_new_val;
940 else
941 return p_orig_val + nvl(p_new_val,0);
942 end if;
943 end add_number_with_null_check;
944
945
946
947 /* ---------------------------------------------------------------------
948 Procedures/Functions Below are defined for Document Management
949 Enhancements to support Printable Documents (PDF)
950 BEGIN
951 --------------------------------------------------------------------- */
952
953
954 Function get_option1_name(
955 p_group_plan_id in number,
956 p_lf_evnt_ocrd_dt in Date,
957 p_oipl_id in number,
958 p_group_per_in_ler_id in number,
959 p_pl_id in number,
960 p_ws_sub_acty_typ_cd in varchar2) return varchar2
961 is
962 cursor cur_option1_name is
963 Select name
964 From ben_cwb_pl_dsgn
965 Where pl_id = p_group_plan_id
966 and group_pl_id = p_group_plan_id
967 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
968 --And oipl_id <> -1
969 --And opt_count = 1;
973 Begin
970 and oipl_ordr_num = 1;
971
972 l_option1_name varchar2(240);
974
975 open cur_option1_name;
976 fetch cur_option1_name into l_option1_name;
977 close cur_option1_name;
978 return l_option1_name;
979 End;
980
981
982 Procedure populate_person_option1_rec(
983 p_group_plan_id in number,
984 p_lf_evt_ocrd_dt in Date,
985 p_oipl_id in number,
986 p_group_per_in_ler_id in number,
987 p_pl_id in number,
988 p_ws_sub_acty_typ_cd in varchar2)
989 Is
990 cursor cur_option1_rate is
991 Select bcpr.*
992 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
993 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
994 And bcpd.group_pl_id = p_group_plan_id
995 -- AND bcpd.pl_id = p_group_plan_id
996 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
997 --And bcpd.oipl_id <> -1
998 --And bcpd.opt_count = 1
999 and oipl_ordr_num = 1
1000 And bcpr.pl_id = bcpd.pl_id
1001 And bcpr.group_pl_id = bcpd.group_pl_id
1002 And bcpr.oipl_id = bcpd.oipl_id
1003 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1004 and bcpr.elig_flag = 'Y';
1005 Begin
1006
1007 g_opt1_person_rates_rec := null;
1008 open cur_option1_rate;
1009 fetch cur_option1_rate into g_opt1_person_rates_rec;
1010 close cur_option1_rate;
1011
1012 End;
1013
1014 Function get_option1_rate_ws_amt(
1015 p_group_plan_id in number,
1016 p_lf_evnt_ocrd_dt in Date,
1017 p_oipl_id in number,
1018 p_group_per_in_ler_id in number,
1019 p_pl_id in number,
1020 p_ws_sub_acty_typ_cd in varchar2) return number
1021 is
1022 Begin
1023 populate_person_option1_rec(
1024 p_group_plan_id,
1025 p_lf_evnt_ocrd_dt,
1026 p_oipl_id,
1027 p_group_per_in_ler_id,
1028 p_pl_id,
1029 p_ws_sub_acty_typ_cd);
1030 return g_opt1_person_rates_rec.ws_val;
1031
1032 End get_option1_rate_ws_amt;
1033
1034 Function get_option1_unit(
1035 p_group_plan_id in number,
1036 p_lf_evnt_ocrd_dt in Date,
1037 p_oipl_id in number,
1038 p_group_per_in_ler_id in number,
1039 p_pl_id in number,
1040 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1041 Is
1042 --- ws_nmmntry_uom need to be decode from lookup table
1043 Cursor cur_option1_units is
1044 Select decode(ws_nnmntry_uom,null,currency, hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom) )
1045 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1046 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1047 And bcpd.group_pl_id = p_group_plan_id
1048 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1049 And bcpr.pl_id = bcpd.pl_id
1050 And bcpr.group_pl_id = bcpd.group_pl_id
1051 And bcpr.oipl_id = bcpd.oipl_id
1052 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1053 and bcpr.elig_flag = 'Y'
1054 and oipl_ordr_num = 1;
1055
1056 l_option1_unit varchar2(30);
1057 Begin
1058 open cur_option1_units;
1059 fetch cur_option1_units into l_option1_unit;
1060 close cur_option1_units;
1061 return l_option1_unit;
1062 End get_option1_unit;
1063
1064
1065 Function get_option1_elg_sal(
1066 p_group_plan_id in number,
1067 p_lf_evnt_ocrd_dt in Date,
1068 p_oipl_id in number,
1069 p_group_per_in_ler_id in number,
1070 p_pl_id in number,
1071 p_ws_sub_acty_typ_cd in varchar2) return number
1072 is
1073 Begin
1074 populate_person_option1_rec(
1075 p_group_plan_id,
1076 p_lf_evnt_ocrd_dt,
1077 p_oipl_id,
1078 p_group_per_in_ler_id,
1079 p_pl_id,
1080 p_ws_sub_acty_typ_cd);
1081 return g_opt1_person_rates_rec.ELIG_SAL_VAL;
1082
1083 End get_option1_elg_sal;
1084
1085 Function get_option1_elg_per_sal(
1086 p_group_plan_id in number,
1087 p_lf_evnt_ocrd_dt in Date,
1088 p_oipl_id in number,
1089 p_group_per_in_ler_id in number,
1090 p_pl_id in number,
1091 p_ws_sub_acty_typ_cd in varchar2) return number
1092 Is
1093 Begin
1094
1095 populate_person_option1_rec(
1096 p_group_plan_id,
1097 p_lf_evnt_ocrd_dt,
1098 p_oipl_id,
1099 p_group_per_in_ler_id,
1100 p_pl_id,
1101 p_ws_sub_acty_typ_cd);
1102
1103 if (g_opt1_person_rates_rec.elig_sal_val <> 0) then
1104 return round( (g_opt1_person_rates_rec.ws_val / g_opt1_person_rates_rec.elig_sal_val) * 100,2);
1105 else
1106 return to_number(null);
1107 end if;
1108
1109 End get_option1_elg_per_sal;
1110
1111 Function get_option1_rate_reco_amt(
1112 p_group_plan_id in number,
1113 p_lf_evnt_ocrd_dt in Date,
1114 p_oipl_id in number,
1115 p_group_per_in_ler_id in number,
1116 p_pl_id in number,
1117 p_ws_sub_acty_typ_cd in varchar2) return number
1121 p_group_plan_id,
1118 is
1119 Begin
1120 populate_person_option1_rec(
1122 p_lf_evnt_ocrd_dt,
1123 p_oipl_id,
1124 p_group_per_in_ler_id,
1125 p_pl_id,
1126 p_ws_sub_acty_typ_cd);
1127 return g_opt1_person_rates_rec.REC_VAL;
1128
1129 End get_option1_rate_reco_amt;
1130
1131 Function get_option1_rate_oth_sal(
1132 p_group_plan_id in number,
1133 p_lf_evnt_ocrd_dt in Date,
1134 p_oipl_id in number,
1135 p_group_per_in_ler_id in number,
1136 p_pl_id in number,
1137 p_ws_sub_acty_typ_cd in varchar2) return number
1138 is
1139 Begin
1140 populate_person_option1_rec(
1141 p_group_plan_id,
1142 p_lf_evnt_ocrd_dt,
1143 p_oipl_id,
1144 p_group_per_in_ler_id,
1145 p_pl_id,
1146 p_ws_sub_acty_typ_cd);
1147 return g_opt1_person_rates_rec.OTH_COMP_VAL;
1148
1149 End get_option1_rate_oth_sal;
1150
1151 Function get_option1_rate_sta_sal(
1152 p_group_plan_id in number,
1153 p_lf_evnt_ocrd_dt in Date,
1154 p_oipl_id in number,
1155 p_group_per_in_ler_id in number,
1156 p_pl_id in number,
1157 p_ws_sub_acty_typ_cd in varchar2) return number
1158 is
1159 Begin
1160 populate_person_option1_rec(
1161 p_group_plan_id,
1162 p_lf_evnt_ocrd_dt,
1163 p_oipl_id,
1164 p_group_per_in_ler_id,
1165 p_pl_id,
1166 p_ws_sub_acty_typ_cd);
1167 return g_opt1_person_rates_rec.STAT_SAL_VAL;
1168
1169 End get_option1_rate_sta_sal;
1170
1171 Function get_option1_rate_tot_comp(
1172 p_group_plan_id in number,
1173 p_lf_evnt_ocrd_dt in Date,
1174 p_oipl_id in number,
1175 p_group_per_in_ler_id in number,
1176 p_pl_id in number,
1177 p_ws_sub_acty_typ_cd in varchar2) return number
1178 is
1179 Begin
1180 populate_person_option1_rec(
1181 p_group_plan_id,
1182 p_lf_evnt_ocrd_dt,
1183 p_oipl_id,
1184 p_group_per_in_ler_id,
1185 p_pl_id,
1186 p_ws_sub_acty_typ_cd);
1187 return g_opt1_person_rates_rec.TOT_COMP_VAL;
1188
1189 End get_option1_rate_tot_comp;
1190
1191
1192 Function get_option1_rate_misc1(
1193 p_group_plan_id in number,
1194 p_lf_evnt_ocrd_dt in Date,
1195 p_oipl_id in number,
1196 p_group_per_in_ler_id in number,
1197 p_pl_id in number,
1198 p_ws_sub_acty_typ_cd in varchar2) return number
1199 is
1200 Begin
1201 populate_person_option1_rec(
1202 p_group_plan_id,
1203 p_lf_evnt_ocrd_dt,
1204 p_oipl_id,
1205 p_group_per_in_ler_id,
1206 p_pl_id,
1207 p_ws_sub_acty_typ_cd);
1208 return g_opt1_person_rates_rec.MISC1_VAL;
1209
1210 End get_option1_rate_misc1;
1211
1212 Function get_option1_rate_misc2(
1213 p_group_plan_id in number,
1214 p_lf_evnt_ocrd_dt in Date,
1215 p_oipl_id in number,
1216 p_group_per_in_ler_id in number,
1217 p_pl_id in number,
1218 p_ws_sub_acty_typ_cd in varchar2) return number
1219 is
1220 Begin
1221 populate_person_option1_rec(
1222 p_group_plan_id,
1223 p_lf_evnt_ocrd_dt,
1224 p_oipl_id,
1225 p_group_per_in_ler_id,
1226 p_pl_id,
1227 p_ws_sub_acty_typ_cd);
1228 return g_opt1_person_rates_rec.MISC2_VAL;
1229
1230 End get_option1_rate_misc2;
1231
1232 Function get_option1_rate_misc3(
1233 p_group_plan_id in number,
1234 p_lf_evnt_ocrd_dt in Date,
1235 p_oipl_id in number,
1236 p_group_per_in_ler_id in number,
1237 p_pl_id in number,
1238 p_ws_sub_acty_typ_cd in varchar2) return number
1239 is
1240 Begin
1241 populate_person_option1_rec(
1242 p_group_plan_id,
1243 p_lf_evnt_ocrd_dt,
1244 p_oipl_id,
1245 p_group_per_in_ler_id,
1246 p_pl_id,
1247 p_ws_sub_acty_typ_cd);
1248 return g_opt1_person_rates_rec.MISC3_VAL;
1249
1250 End get_option1_rate_misc3;
1251
1252
1253 Function get_option2_name(
1254 p_group_plan_id in number,
1255 p_lf_evnt_ocrd_dt in Date,
1256 p_oipl_id in number,
1257 p_group_per_in_ler_id in number,
1258 p_pl_id in number,
1259 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1260 is
1261 cursor cur_option2_name is
1262 Select name
1263 From ben_cwb_pl_dsgn
1264 Where pl_id = p_group_plan_id
1265 And group_pl_id = p_group_plan_id
1266 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1267 -- And oipl_id <> -1
1268 -- And opt_count = 2;
1269 and oipl_ordr_num = 2;
1270
1271 l_option2_name varchar2(240);
1272 Begin
1273
1274 open cur_option2_name;
1275 fetch cur_option2_name into l_option2_name;
1279
1276 close cur_option2_name;
1277 return l_option2_name;
1278 End;
1280 Procedure populate_person_option2_rec(
1281 p_group_plan_id in number,
1282 p_lf_evt_ocrd_dt in Date,
1283 p_oipl_id in number,
1284 p_group_per_in_ler_id in number,
1285 p_pl_id in number,
1286 p_ws_sub_acty_typ_cd in varchar2)
1287 Is
1288 cursor cur_option2_rate is
1289 Select bcpr.*
1290 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1291 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1292 And bcpd.group_pl_id = p_group_plan_id
1293 --AND bcpd.pl_id = p_group_plan_id
1294 And bcpd.lf_evt_ocrd_dt= p_lf_evt_ocrd_dt
1295 --And bcpd.oipl_id <> -1
1296 -- And bcpd.opt_count = 2
1297 and oipl_ordr_num = 2
1298 And bcpd.pl_id = bcpr.pl_id
1299 And bcpd.group_pl_id = bcpr.group_pl_id
1300 And bcpd.oipl_id = bcpr.oipl_id
1301 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
1302 and bcpr.elig_flag = 'Y';
1303 Begin
1304
1305 g_opt2_person_rates_rec := null;
1306 open cur_option2_rate;
1307 fetch cur_option2_rate into g_opt2_person_rates_rec;
1308 close cur_option2_rate;
1309
1310 End populate_person_option2_rec;
1311
1312 Function get_option2_rate_ws_amt(
1313 p_group_plan_id in number,
1314 p_lf_evnt_ocrd_dt in Date,
1315 p_oipl_id in number,
1316 p_group_per_in_ler_id in number,
1317 p_pl_id in number,
1318 p_ws_sub_acty_typ_cd in varchar2) return number
1319 is
1320 Begin
1321 populate_person_option2_rec(
1322 p_group_plan_id,
1323 p_lf_evnt_ocrd_dt,
1324 p_oipl_id,
1325 p_group_per_in_ler_id,
1326 p_pl_id,
1327 p_ws_sub_acty_typ_cd);
1328 return g_opt2_person_rates_rec.ws_val;
1329
1330 End get_option2_rate_ws_amt;
1331
1332 Function get_option2_unit(
1333 p_group_plan_id in number,
1334 p_lf_evnt_ocrd_dt in Date,
1335 p_oipl_id in number,
1336 p_group_per_in_ler_id in number,
1337 p_pl_id in number,
1338 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1339 Is
1340 ----- ws_nnmntry_uom needs to be decoded from lookup table
1341 Cursor cur_option2_units is
1342 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
1343 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1344 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1345 And bcpd.group_pl_id = p_group_plan_id
1346 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1347 And bcpr.pl_id = bcpd.pl_id
1348 And bcpr.group_pl_id = bcpd.group_pl_id
1349 And bcpr.oipl_id = bcpd.oipl_id
1350 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1351 and bcpr.elig_flag = 'Y'
1352 and oipl_ordr_num = 2;
1353
1354 l_option2_unit varchar2(30);
1355 Begin
1356 open cur_option2_units;
1357 fetch cur_option2_units into l_option2_unit;
1358 close cur_option2_units;
1359 return l_option2_unit;
1360 End get_option2_unit;
1361
1362
1363 Function get_option2_elg_sal(
1364 p_group_plan_id in number,
1365 p_lf_evnt_ocrd_dt in Date,
1366 p_oipl_id in number,
1367 p_group_per_in_ler_id in number,
1368 p_pl_id in number,
1369 p_ws_sub_acty_typ_cd in varchar2) return number
1370 is
1371 Begin
1372
1373 populate_person_option2_rec(
1374 p_group_plan_id,
1375 p_lf_evnt_ocrd_dt,
1376 p_oipl_id,
1377 p_group_per_in_ler_id,
1378 p_pl_id,
1379 p_ws_sub_acty_typ_cd);
1380 return g_opt2_person_rates_rec.ELIG_SAL_VAL;
1381
1382 End get_option2_elg_sal;
1383
1384 Function get_option2_elg_per_sal(
1385 p_group_plan_id in number,
1386 p_lf_evnt_ocrd_dt in Date,
1387 p_oipl_id in number,
1388 p_group_per_in_ler_id in number,
1389 p_pl_id in number,
1390 p_ws_sub_acty_typ_cd in varchar2) return number
1391 Is
1392 Begin
1393
1394 populate_person_option2_rec(
1395 p_group_plan_id,
1396 p_lf_evnt_ocrd_dt,
1397 p_oipl_id,
1398 p_group_per_in_ler_id,
1399 p_pl_id,
1400 p_ws_sub_acty_typ_cd);
1401
1402 if (g_opt2_person_rates_rec.elig_sal_val <> 0) then
1403 return round( (g_opt2_person_rates_rec.ws_val / g_opt2_person_rates_rec.elig_sal_val) * 100,2);
1404 else
1405 return to_number(null);
1406 end if;
1407
1408 End get_option2_elg_per_sal;
1409
1410 Function get_option2_rate_reco_amt(
1411 p_group_plan_id in number,
1412 p_lf_evnt_ocrd_dt in Date,
1413 p_oipl_id in number,
1414 p_group_per_in_ler_id in number,
1415 p_pl_id in number,
1416 p_ws_sub_acty_typ_cd in varchar2) return number
1417 is
1418 Begin
1419 populate_person_option2_rec(
1420 p_group_plan_id,
1421 p_lf_evnt_ocrd_dt,
1422 p_oipl_id,
1423 p_group_per_in_ler_id,
1424 p_pl_id,
1425 p_ws_sub_acty_typ_cd);
1426 return g_opt2_person_rates_rec.REC_VAL;
1430 Function get_option2_rate_oth_sal(
1427
1428 End get_option2_rate_reco_amt;
1429
1431 p_group_plan_id in number,
1432 p_lf_evnt_ocrd_dt in Date,
1433 p_oipl_id in number,
1434 p_group_per_in_ler_id in number,
1435 p_pl_id in number,
1436 p_ws_sub_acty_typ_cd in varchar2) return number
1437 is
1438 Begin
1439 populate_person_option2_rec(
1440 p_group_plan_id,
1441 p_lf_evnt_ocrd_dt,
1442 p_oipl_id,
1443 p_group_per_in_ler_id,
1444 p_pl_id,
1445 p_ws_sub_acty_typ_cd);
1446 return g_opt2_person_rates_rec.OTH_COMP_VAL;
1447
1448 End get_option2_rate_oth_sal;
1449
1450 Function get_option2_rate_sta_sal(
1451 p_group_plan_id in number,
1452 p_lf_evnt_ocrd_dt in Date,
1453 p_oipl_id in number,
1454 p_group_per_in_ler_id in number,
1455 p_pl_id in number,
1456 p_ws_sub_acty_typ_cd in varchar2) return number
1457 is
1458 Begin
1459 populate_person_option2_rec(
1460 p_group_plan_id,
1461 p_lf_evnt_ocrd_dt,
1462 p_oipl_id,
1463 p_group_per_in_ler_id,
1464 p_pl_id,
1465 p_ws_sub_acty_typ_cd);
1466 return g_opt2_person_rates_rec.STAT_SAL_VAL;
1467
1468 End get_option2_rate_sta_sal;
1469
1470 Function get_option2_rate_tot_comp(
1471 p_group_plan_id in number,
1472 p_lf_evnt_ocrd_dt in Date,
1473 p_oipl_id in number,
1474 p_group_per_in_ler_id in number,
1475 p_pl_id in number,
1476 p_ws_sub_acty_typ_cd in varchar2) return number
1477 is
1478 Begin
1479 populate_person_option2_rec(
1480 p_group_plan_id,
1481 p_lf_evnt_ocrd_dt,
1482 p_oipl_id,
1483 p_group_per_in_ler_id,
1484 p_pl_id,
1485 p_ws_sub_acty_typ_cd);
1486 return g_opt2_person_rates_rec.TOT_COMP_VAL;
1487
1488 End get_option2_rate_tot_comp;
1489
1490
1491 Function get_option2_rate_misc1(
1492 p_group_plan_id in number,
1493 p_lf_evnt_ocrd_dt in Date,
1494 p_oipl_id in number,
1495 p_group_per_in_ler_id in number,
1496 p_pl_id in number,
1497 p_ws_sub_acty_typ_cd in varchar2) return number
1498 is
1499 Begin
1500 populate_person_option2_rec(
1501 p_group_plan_id,
1502 p_lf_evnt_ocrd_dt,
1503 p_oipl_id,
1504 p_group_per_in_ler_id,
1505 p_pl_id,
1506 p_ws_sub_acty_typ_cd);
1507 return g_opt2_person_rates_rec.MISC1_VAL;
1508
1509 End get_option2_rate_misc1;
1510
1511 Function get_option2_rate_misc2(
1512 p_group_plan_id in number,
1513 p_lf_evnt_ocrd_dt in Date,
1514 p_oipl_id in number,
1515 p_group_per_in_ler_id in number,
1516 p_pl_id in number,
1517 p_ws_sub_acty_typ_cd in varchar2) return number
1518 is
1519 Begin
1520 populate_person_option2_rec(
1521 p_group_plan_id,
1522 p_lf_evnt_ocrd_dt,
1523 p_oipl_id,
1524 p_group_per_in_ler_id,
1525 p_pl_id,
1526 p_ws_sub_acty_typ_cd);
1527 return g_opt2_person_rates_rec.MISC2_VAL;
1528
1529 End get_option2_rate_misc2;
1530
1531 Function get_option2_rate_misc3(
1532 p_group_plan_id in number,
1533 p_lf_evnt_ocrd_dt in Date,
1534 p_oipl_id in number,
1535 p_group_per_in_ler_id in number,
1536 p_pl_id in number,
1537 p_ws_sub_acty_typ_cd in varchar2) return number
1538 is
1539 Begin
1540 populate_person_option2_rec(
1541 p_group_plan_id,
1542 p_lf_evnt_ocrd_dt,
1543 p_oipl_id,
1544 p_group_per_in_ler_id,
1545 p_pl_id,
1546 p_ws_sub_acty_typ_cd);
1547 return g_opt2_person_rates_rec.MISC3_VAL;
1548
1549 End get_option2_rate_misc3;
1550
1551
1552 Function get_option3_name(
1553 p_group_plan_id in number,
1554 p_lf_evnt_ocrd_dt in Date,
1555 p_oipl_id in number,
1556 p_group_per_in_ler_id in number,
1557 p_pl_id in number,
1558 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1559 is
1560 cursor cur_option3_name is
1561 Select name
1562 From ben_cwb_pl_dsgn
1563 Where pl_id = p_group_plan_id
1564 And group_pl_id = p_group_plan_id
1565 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1566 -- And oipl_id <> -1
1567 -- And opt_count = 3;
1568 and oipl_ordr_num = 3;
1569
1570 l_option3_name varchar2(240);
1571 Begin
1572
1573 open cur_option3_name;
1574 fetch cur_option3_name into l_option3_name;
1575 close cur_option3_name;
1576 return l_option3_name;
1577 End get_option3_name;
1578
1579 Procedure populate_person_option3_rec(
1580 p_group_plan_id in number,
1581 p_lf_evt_ocrd_dt in Date,
1582 p_oipl_id in number,
1583 p_group_per_in_ler_id in number,
1584 p_pl_id in number,
1585 p_ws_sub_acty_typ_cd in varchar2)
1586 Is
1587 cursor cur_option3_rate is
1588 Select bcpr.*
1589 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1590 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1591 And bcpd.group_pl_id = p_group_plan_id
1592 -- AND bcpd.pl_id = p_group_plan_id
1593 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1594 --And bcpd.oipl_id <> -1
1595 --And bcpd.opt_count = 3
1596 and oipl_ordr_num = 3
1597 And bcpd.pl_id = bcpr.pl_id
1598 And bcpd.group_pl_id = bcpr.group_pl_id
1599 And bcpd.oipl_id = bcpr.oipl_id
1600 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
1601 and bcpr.elig_flag = 'Y';
1602 Begin
1603
1604 g_opt3_person_rates_rec := null;
1605 open cur_option3_rate;
1606 fetch cur_option3_rate into g_opt3_person_rates_rec;
1607 close cur_option3_rate;
1608
1609 End populate_person_option3_rec;
1610
1611 Function get_option3_rate_ws_amt(
1612 p_group_plan_id in number,
1613 p_lf_evnt_ocrd_dt in Date,
1614 p_oipl_id in number,
1615 p_group_per_in_ler_id in number,
1616 p_pl_id in number,
1617 p_ws_sub_acty_typ_cd in varchar2) return number
1618 is
1619 Begin
1620 populate_person_option3_rec(
1621 p_group_plan_id,
1622 p_lf_evnt_ocrd_dt,
1623 p_oipl_id,
1624 p_group_per_in_ler_id,
1625 p_pl_id,
1626 p_ws_sub_acty_typ_cd);
1627 return g_opt3_person_rates_rec.ws_val;
1628
1629 End get_option3_rate_ws_amt;
1630
1631 Function get_option3_unit(
1632 p_group_plan_id in number,
1633 p_lf_evnt_ocrd_dt in Date,
1634 p_oipl_id in number,
1635 p_group_per_in_ler_id in number,
1636 p_pl_id in number,
1637 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1638 Is
1639 -- ws_nnmntry_uom needs to be decoded from lookup table
1640 Cursor cur_option3_units is
1641 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
1642 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1643 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1644 And bcpd.group_pl_id = p_group_plan_id
1645 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1646 And bcpr.pl_id = bcpd.pl_id
1647 And bcpr.group_pl_id = bcpd.group_pl_id
1648 And bcpr.oipl_id = bcpd.oipl_id
1649 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1650 and bcpr.elig_flag = 'Y'
1651 and oipl_ordr_num = 3;
1652
1653 l_option3_unit varchar2(30);
1654 Begin
1655 open cur_option3_units;
1656 fetch cur_option3_units into l_option3_unit;
1657 close cur_option3_units;
1658 return l_option3_unit;
1659 End get_option3_unit;
1660
1661
1662 Function get_option3_elg_sal(
1663 p_group_plan_id in number,
1664 p_lf_evnt_ocrd_dt in Date,
1665 p_oipl_id in number,
1666 p_group_per_in_ler_id in number,
1667 p_pl_id in number,
1668 p_ws_sub_acty_typ_cd in varchar2) return number
1669 is
1670 Begin
1671
1672 populate_person_option3_rec(
1673 p_group_plan_id,
1674 p_lf_evnt_ocrd_dt,
1675 p_oipl_id,
1676 p_group_per_in_ler_id,
1677 p_pl_id,
1678 p_ws_sub_acty_typ_cd);
1682
1679 return g_opt3_person_rates_rec.ELIG_SAL_VAL;
1680
1681 End get_option3_elg_sal;
1683 Function get_option3_elg_per_sal(
1684 p_group_plan_id in number,
1685 p_lf_evnt_ocrd_dt in Date,
1686 p_oipl_id in number,
1687 p_group_per_in_ler_id in number,
1688 p_pl_id in number,
1689 p_ws_sub_acty_typ_cd in varchar2) return number
1690 Is
1691 Begin
1692
1693 populate_person_option3_rec(
1694 p_group_plan_id,
1695 p_lf_evnt_ocrd_dt,
1696 p_oipl_id,
1697 p_group_per_in_ler_id,
1698 p_pl_id,
1699 p_ws_sub_acty_typ_cd);
1700 if (g_opt3_person_rates_rec.elig_sal_val <> 0) then
1701 return round( (g_opt3_person_rates_rec.ws_val / g_opt3_person_rates_rec.elig_sal_val) * 100,2);
1702 else
1703 return to_number(null);
1704 end if;
1705
1706
1707 End get_option3_elg_per_sal;
1708
1709 Function get_option3_rate_reco_amt(
1710 p_group_plan_id in number,
1711 p_lf_evnt_ocrd_dt in Date,
1712 p_oipl_id in number,
1713 p_group_per_in_ler_id in number,
1714 p_pl_id in number,
1715 p_ws_sub_acty_typ_cd in varchar2) return number
1716 is
1717 Begin
1718 populate_person_option3_rec(
1719 p_group_plan_id,
1720 p_lf_evnt_ocrd_dt,
1721 p_oipl_id,
1722 p_group_per_in_ler_id,
1723 p_pl_id,
1724 p_ws_sub_acty_typ_cd);
1725 return g_opt3_person_rates_rec.REC_VAL;
1726
1727 End get_option3_rate_reco_amt;
1728
1729 Function get_option3_rate_oth_sal(
1730 p_group_plan_id in number,
1731 p_lf_evnt_ocrd_dt in Date,
1732 p_oipl_id in number,
1733 p_group_per_in_ler_id in number,
1734 p_pl_id in number,
1735 p_ws_sub_acty_typ_cd in varchar2) return number
1736 is
1737 Begin
1738 populate_person_option3_rec(
1739 p_group_plan_id,
1740 p_lf_evnt_ocrd_dt,
1741 p_oipl_id,
1742 p_group_per_in_ler_id,
1743 p_pl_id,
1744 p_ws_sub_acty_typ_cd);
1745 return g_opt3_person_rates_rec.OTH_COMP_VAL;
1746
1747 End get_option3_rate_oth_sal;
1748
1749 Function get_option3_rate_sta_sal(
1750 p_group_plan_id in number,
1751 p_lf_evnt_ocrd_dt in Date,
1752 p_oipl_id in number,
1753 p_group_per_in_ler_id in number,
1754 p_pl_id in number,
1755 p_ws_sub_acty_typ_cd in varchar2) return number
1756 is
1757 Begin
1758 populate_person_option3_rec(
1759 p_group_plan_id,
1760 p_lf_evnt_ocrd_dt,
1761 p_oipl_id,
1762 p_group_per_in_ler_id,
1763 p_pl_id,
1764 p_ws_sub_acty_typ_cd);
1765 return g_opt3_person_rates_rec.STAT_SAL_VAL;
1766
1767 End get_option3_rate_sta_sal;
1768
1769 Function get_option3_rate_tot_comp(
1770 p_group_plan_id in number,
1771 p_lf_evnt_ocrd_dt in Date,
1772 p_oipl_id in number,
1773 p_group_per_in_ler_id in number,
1774 p_pl_id in number,
1775 p_ws_sub_acty_typ_cd in varchar2) return number
1776 is
1777 Begin
1778 populate_person_option3_rec(
1779 p_group_plan_id,
1780 p_lf_evnt_ocrd_dt,
1781 p_oipl_id,
1782 p_group_per_in_ler_id,
1783 p_pl_id,
1784 p_ws_sub_acty_typ_cd);
1785 return g_opt3_person_rates_rec.TOT_COMP_VAL;
1786
1787 End get_option3_rate_tot_comp;
1788
1789
1790 Function get_option3_rate_misc1(
1791 p_group_plan_id in number,
1792 p_lf_evnt_ocrd_dt in Date,
1793 p_oipl_id in number,
1794 p_group_per_in_ler_id in number,
1795 p_pl_id in number,
1796 p_ws_sub_acty_typ_cd in varchar2) return number
1797 is
1798 Begin
1799 populate_person_option3_rec(
1800 p_group_plan_id,
1801 p_lf_evnt_ocrd_dt,
1802 p_oipl_id,
1803 p_group_per_in_ler_id,
1804 p_pl_id,
1805 p_ws_sub_acty_typ_cd);
1806 return g_opt3_person_rates_rec.MISC1_VAL;
1807
1808 End get_option3_rate_misc1;
1809
1810 Function get_option3_rate_misc2(
1811 p_group_plan_id in number,
1812 p_lf_evnt_ocrd_dt in Date,
1813 p_oipl_id in number,
1814 p_group_per_in_ler_id in number,
1815 p_pl_id in number,
1816 p_ws_sub_acty_typ_cd in varchar2) return number
1817 is
1818 Begin
1819 populate_person_option3_rec(
1820 p_group_plan_id,
1821 p_lf_evnt_ocrd_dt,
1822 p_oipl_id,
1823 p_group_per_in_ler_id,
1824 p_pl_id,
1825 p_ws_sub_acty_typ_cd);
1826 return g_opt3_person_rates_rec.MISC2_VAL;
1827
1828 End get_option3_rate_misc2;
1829
1830 Function get_option3_rate_misc3(
1831 p_group_plan_id in number,
1832 p_lf_evnt_ocrd_dt in Date,
1833 p_oipl_id in number,
1834 p_group_per_in_ler_id in number,
1835 p_pl_id in number,
1836 p_ws_sub_acty_typ_cd in varchar2) return number
1837 is
1838 Begin
1839 populate_person_option3_rec(
1840 p_group_plan_id,
1841 p_lf_evnt_ocrd_dt,
1842 p_oipl_id,
1843 p_group_per_in_ler_id,
1844 p_pl_id,
1845 p_ws_sub_acty_typ_cd);
1846 return g_opt3_person_rates_rec.MISC3_VAL;
1847 End get_option3_rate_misc3;
1848
1849 Function get_option4_name(
1850 p_group_plan_id in number,
1851 p_lf_evnt_ocrd_dt in Date,
1852 p_oipl_id in number,
1853 p_group_per_in_ler_id in number,
1854 p_pl_id in number,
1855 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1856 is
1857 cursor cur_option4_name is
1858 Select name
1859 From ben_cwb_pl_dsgn
1860 Where pl_id = p_group_plan_id
1861 And group_pl_id = p_group_plan_id
1862 And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1863 -- And oipl_id <> -1
1864 -- And opt_count = 4;
1865 and oipl_ordr_num = 4 ;
1866
1867 l_option4_name varchar2(240);
1868 Begin
1869
1870 open cur_option4_name;
1871 fetch cur_option4_name into l_option4_name;
1872 close cur_option4_name;
1873 return l_option4_name;
1874 End get_option4_name;
1875
1876 Procedure populate_person_option4_rec(
1877 p_group_plan_id in number,
1878 p_lf_evt_ocrd_dt in Date,
1879 p_oipl_id in number,
1880 p_group_per_in_ler_id in number,
1881 p_pl_id in number,
1882 p_ws_sub_acty_typ_cd in varchar2)
1883 Is
1884 cursor cur_option4_rate is
1885 Select bcpr.*
1886 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1887 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1888 And bcpd.group_pl_id = p_group_plan_id
1889 -- AND bcpd.pl_id = p_group_plan_id
1890 And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1891 --And bcpd.oipl_id <> -1
1892 -- And bcpd.opt_count = 4
1893 and oipl_ordr_num = 4
1894 And bcpd.pl_id = bcpr.pl_id
1895 And bcpd.group_pl_id = bcpr.group_pl_id
1896 And bcpd.oipl_id = bcpr.oipl_id
1897 And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
1898 and bcpr.elig_flag = 'Y';
1899 Begin
1900 g_opt4_person_rates_rec := null;
1901 open cur_option4_rate;
1902 fetch cur_option4_rate into g_opt4_person_rates_rec;
1903 close cur_option4_rate;
1904
1905 End populate_person_option4_rec;
1906
1907 Function get_option4_rate_ws_amt(
1908 p_group_plan_id in number,
1909 p_lf_evnt_ocrd_dt in Date,
1910 p_oipl_id in number,
1911 p_group_per_in_ler_id in number,
1912 p_pl_id in number,
1913 p_ws_sub_acty_typ_cd in varchar2) return number
1914 is
1915 Begin
1916 populate_person_option4_rec(
1917 p_group_plan_id,
1918 p_lf_evnt_ocrd_dt,
1919 p_oipl_id,
1920 p_group_per_in_ler_id,
1921 p_pl_id,
1922 p_ws_sub_acty_typ_cd);
1923 return g_opt4_person_rates_rec.ws_val;
1924
1925 End get_option4_rate_ws_amt;
1926
1927 Function get_option4_unit(
1928 p_group_plan_id in number,
1929 p_lf_evnt_ocrd_dt in Date,
1930 p_oipl_id in number,
1931 p_group_per_in_ler_id in number,
1932 p_pl_id in number,
1933 p_ws_sub_acty_typ_cd in varchar2) return varchar2
1934 Is
1935 -- ws_nnmntry_uom needs to be decoded from lookup table
1936 Cursor cur_option4_units is
1937 Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
1938 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
1939 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
1940 And bcpd.group_pl_id = p_group_plan_id
1941 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
1942 And bcpr.pl_id = bcpd.pl_id
1943 And bcpr.group_pl_id = bcpd.group_pl_id
1947 and oipl_ordr_num = 4;
1944 And bcpr.oipl_id = bcpd.oipl_id
1945 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
1946 and bcpr.elig_flag = 'Y'
1948
1949 l_option4_unit varchar2(30);
1950 Begin
1951 open cur_option4_units;
1952 fetch cur_option4_units into l_option4_unit;
1953 close cur_option4_units;
1954 return l_option4_unit;
1955 End get_option4_unit;
1956
1957
1958 Function get_option4_elg_sal(
1959 p_group_plan_id in number,
1960 p_lf_evnt_ocrd_dt in Date,
1961 p_oipl_id in number,
1962 p_group_per_in_ler_id in number,
1963 p_pl_id in number,
1964 p_ws_sub_acty_typ_cd in varchar2) return number
1965 is
1966 Begin
1967
1968 populate_person_option4_rec(
1969 p_group_plan_id,
1970 p_lf_evnt_ocrd_dt,
1971 p_oipl_id,
1972 p_group_per_in_ler_id,
1973 p_pl_id,
1974 p_ws_sub_acty_typ_cd);
1975 return g_opt4_person_rates_rec.ELIG_SAL_VAL;
1976
1977 End get_option4_elg_sal;
1978
1979 Function get_option4_elg_per_sal(
1980 p_group_plan_id in number,
1981 p_lf_evnt_ocrd_dt in Date,
1982 p_oipl_id in number,
1983 p_group_per_in_ler_id in number,
1984 p_pl_id in number,
1985 p_ws_sub_acty_typ_cd in varchar2) return number
1986 Is
1987 Begin
1988
1989 populate_person_option4_rec(
1990 p_group_plan_id,
1991 p_lf_evnt_ocrd_dt,
1992 p_oipl_id,
1993 p_group_per_in_ler_id,
1994 p_pl_id,
1995 p_ws_sub_acty_typ_cd);
1996 if (g_opt4_person_rates_rec.elig_sal_val <> 0) then
1997 return round( (g_opt4_person_rates_rec.ws_val / g_opt4_person_rates_rec.elig_sal_val) * 100,2);
1998 else
1999 return to_number(null);
2000 end if;
2001
2002 End get_option4_elg_per_sal;
2003
2004 Function get_option4_rate_reco_amt(
2005 p_group_plan_id in number,
2006 p_lf_evnt_ocrd_dt in Date,
2007 p_oipl_id in number,
2008 p_group_per_in_ler_id in number,
2009 p_pl_id in number,
2010 p_ws_sub_acty_typ_cd in varchar2) return number
2011 is
2012 Begin
2013 populate_person_option4_rec(
2014 p_group_plan_id,
2015 p_lf_evnt_ocrd_dt,
2016 p_oipl_id,
2017 p_group_per_in_ler_id,
2018 p_pl_id,
2019 p_ws_sub_acty_typ_cd);
2020 return g_opt4_person_rates_rec.REC_VAL;
2021
2022 End get_option4_rate_reco_amt;
2023
2024 Function get_option4_rate_oth_sal(
2025 p_group_plan_id in number,
2026 p_lf_evnt_ocrd_dt in Date,
2027 p_oipl_id in number,
2028 p_group_per_in_ler_id in number,
2029 p_pl_id in number,
2030 p_ws_sub_acty_typ_cd in varchar2) return number
2031 is
2032 Begin
2033 populate_person_option4_rec(
2034 p_group_plan_id,
2035 p_lf_evnt_ocrd_dt,
2036 p_oipl_id,
2037 p_group_per_in_ler_id,
2038 p_pl_id,
2039 p_ws_sub_acty_typ_cd);
2040 return g_opt4_person_rates_rec.OTH_COMP_VAL;
2041
2042 End get_option4_rate_oth_sal;
2043
2044 Function get_option4_rate_sta_sal(
2045 p_group_plan_id in number,
2046 p_lf_evnt_ocrd_dt in Date,
2047 p_oipl_id in number,
2048 p_group_per_in_ler_id in number,
2049 p_pl_id in number,
2050 p_ws_sub_acty_typ_cd in varchar2) return number
2051 is
2052 Begin
2053 populate_person_option4_rec(
2054 p_group_plan_id,
2055 p_lf_evnt_ocrd_dt,
2056 p_oipl_id,
2057 p_group_per_in_ler_id,
2058 p_pl_id,
2059 p_ws_sub_acty_typ_cd);
2060 return g_opt4_person_rates_rec.STAT_SAL_VAL;
2061
2062 End get_option4_rate_sta_sal;
2063
2064 Function get_option4_rate_tot_comp(
2065 p_group_plan_id in number,
2066 p_lf_evnt_ocrd_dt in Date,
2067 p_oipl_id in number,
2068 p_group_per_in_ler_id in number,
2069 p_pl_id in number,
2070 p_ws_sub_acty_typ_cd in varchar2) return number
2071 is
2072 Begin
2073 populate_person_option4_rec(
2074 p_group_plan_id,
2075 p_lf_evnt_ocrd_dt,
2076 p_oipl_id,
2077 p_group_per_in_ler_id,
2078 p_pl_id,
2079 p_ws_sub_acty_typ_cd);
2080 return g_opt4_person_rates_rec.TOT_COMP_VAL;
2081
2082 End get_option4_rate_tot_comp;
2083
2084
2085 Function get_option4_rate_misc1(
2086 p_group_plan_id in number,
2087 p_lf_evnt_ocrd_dt in Date,
2088 p_oipl_id in number,
2089 p_group_per_in_ler_id in number,
2090 p_pl_id in number,
2091 p_ws_sub_acty_typ_cd in varchar2) return number
2092 is
2093 Begin
2094 populate_person_option4_rec(
2095 p_group_plan_id,
2096 p_lf_evnt_ocrd_dt,
2097 p_oipl_id,
2098 p_group_per_in_ler_id,
2099 p_pl_id,
2100 p_ws_sub_acty_typ_cd);
2101 return g_opt4_person_rates_rec.MISC1_VAL;
2105 Function get_option4_rate_misc2(
2102
2103 End get_option4_rate_misc1;
2104
2106 p_group_plan_id in number,
2107 p_lf_evnt_ocrd_dt in Date,
2108 p_oipl_id in number,
2109 p_group_per_in_ler_id in number,
2110 p_pl_id in number,
2111 p_ws_sub_acty_typ_cd in varchar2) return number
2112 is
2113 Begin
2114 populate_person_option4_rec(
2115 p_group_plan_id,
2116 p_lf_evnt_ocrd_dt,
2117 p_oipl_id,
2118 p_group_per_in_ler_id,
2119 p_pl_id,
2120 p_ws_sub_acty_typ_cd);
2121 return g_opt4_person_rates_rec.MISC2_VAL;
2122
2123 End get_option4_rate_misc2;
2124
2125 Function get_option4_rate_misc3(
2126 p_group_plan_id in number,
2127 p_lf_evnt_ocrd_dt in Date,
2128 p_oipl_id in number,
2129 p_group_per_in_ler_id in number,
2130 p_pl_id in number,
2131 p_ws_sub_acty_typ_cd in varchar2) return number
2132 is
2133 Begin
2134 populate_person_option4_rec(
2135 p_group_plan_id,
2136 p_lf_evnt_ocrd_dt,
2137 p_oipl_id,
2138 p_group_per_in_ler_id,
2139 p_pl_id,
2140 p_ws_sub_acty_typ_cd);
2141 return g_opt4_person_rates_rec.MISC3_VAL;
2142 End get_option4_rate_misc3;
2143
2144
2145 --
2146 PROCEDURE populate_person_rates_rec(
2147 p_group_plan_id in number,
2148 p_lf_evt_ocrd_dt in Date,
2149 p_oipl_id in number,
2150 p_group_per_in_ler_id in number,
2151 p_pl_id in number,
2152 p_ws_sub_acty_typ_cd in varchar2,
2153 p_new_or_prior in varchar2 ) is
2154
2155 -- *********
2156 -- This next cursor never retreives any record as the person
2157 -- always gets a new group_per_in_ler_id for each cycle. So for
2158 -- one group_per_in_ler_id, we can find only one lf_evt_ocrd_dt
2159 cursor c_prior_person_rate_dt is
2160 select max(lf_evt_ocrd_dt)
2161 from ben_cwb_person_rates
2162 where group_per_in_ler_id = p_group_per_in_ler_id
2163 and group_pl_id = p_group_plan_id
2164 --and pl_id = p_pl_id
2165 and lf_evt_ocrd_dt < p_lf_evt_ocrd_dt
2166 and elig_flag = 'Y';
2167
2168 -- *** change here. commented out group_pl_id and using pl_id
2169 cursor c_person_rates (c_lf_evt_ocrd_dt Date) is
2170 select * from ben_cwb_person_rates
2171 where group_per_in_ler_id = p_group_per_in_ler_id
2172 and group_pl_id = p_group_plan_id
2173 --and pl_id = p_pl_id
2174 and oipl_id = p_oipl_id
2175 and lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
2176 and elig_flag = 'Y';
2177
2178
2179 l_lf_evt_ocrd_dt date;
2180 begin
2181 hr_utility.set_location('Entering populate_person_rates_rec. p_ws_sub_acty_typ_cd='||p_ws_sub_acty_typ_cd,10);
2182 -- if (p_ws_sub_acty_typ_cd ='ICM7') then -- Salary Plan
2183 hr_utility.set_location('p_new_or_prior='||p_ws_sub_acty_typ_cd,10);
2184 if (p_new_or_prior ='NEW') then
2185
2186 g_person_rates_rec := null;
2187 open c_person_rates (p_lf_evt_ocrd_dt);
2188 fetch c_person_rates into g_person_rates_rec;
2189 close c_person_rates;
2190
2191 elsif (p_new_or_prior ='PRIOR') then
2192 open c_prior_person_rate_dt;
2193 fetch c_prior_person_rate_dt into l_lf_evt_ocrd_dt;
2194 close c_prior_person_rate_dt;
2195
2196 if (l_lf_evt_ocrd_dt is not null) then
2197 g_prior_person_rates_rec := null;
2198 open c_person_rates (l_lf_evt_ocrd_dt);
2199 fetch c_person_rates into g_prior_person_rates_rec;
2200 close c_person_rates;
2201 end if;
2202
2203 end if;
2204 -- end if; then -- Salary Plan
2205 end;
2206
2207 FUNCTION get_pay_rate (
2208 p_group_plan_id in number,
2209 p_lf_evnt_ocrd_dt in Date,
2210 p_oipl_id in number,
2211 p_group_per_in_ler_id in number,
2212 p_pl_id in number,
2213 p_ws_sub_acty_typ_cd in varchar2,
2214 p_new_or_prior in varchar2) return number is
2215
2216
2217 l_proposed_salary number := 0;
2218
2219 cursor c_pay_proposal is
2220 select max(nvl(proposed_salary_n,0))
2221 from per_pay_proposals ppp ,ben_cwb_person_rates rts
2222 where rts.pay_proposal_id = ppp.pay_proposal_id
2223 and rts.group_per_in_ler_id = p_group_per_in_ler_id
2224 and rts.group_pl_id = p_group_plan_id
2225 and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
2226 and rts.ws_val is not null
2227 and rts.elig_flag = 'Y';
2228 cursor c_prior_pay_proposal is
2229 select nvl(base_salary,0)
2230 from ben_cwb_person_info
2231 where group_per_in_ler_id = p_group_per_in_ler_id;
2232
2233 begin
2234
2235 if (p_new_or_prior = 'NEW') then
2236 open c_pay_proposal;
2237 fetch c_pay_proposal into l_proposed_salary;
2238 close c_pay_proposal;
2239 else
2240 -- we need to get the prior salary
2241 open c_prior_pay_proposal;
2242 fetch c_prior_pay_proposal into l_proposed_salary;
2243 close c_prior_pay_proposal;
2244 end if;
2245 return l_proposed_salary;
2246 end get_pay_rate;
2247
2248
2249
2250 FUNCTION get_pay_rate_change_amount (
2251 p_group_plan_id in number,
2252 p_lf_evt_ocrd_dt in Date,
2253 p_oipl_id in number,
2257
2254 p_group_per_in_ler_id in number,
2255 p_pl_id in number,
2256 p_ws_sub_acty_typ_cd in varchar2) return number is
2258 l_new_proposed_salary number;
2259 l_prior_proposed_salary number;
2260 l_pay_proposal varchar2(30);
2261 begin
2262 l_new_proposed_salary :=
2263 get_pay_rate (
2264 p_group_plan_id,p_lf_evt_ocrd_dt,
2265 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2266 p_ws_sub_acty_typ_cd, 'NEW');
2267
2268 l_prior_proposed_salary :=
2269 get_pay_rate (
2270 p_group_plan_id,p_lf_evt_ocrd_dt,
2271 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2272 p_ws_sub_acty_typ_cd, 'PRIOR');
2273
2274 if (l_new_proposed_salary = l_prior_proposed_salary) then
2275 return 0;
2276 else
2277 return l_new_proposed_salary - l_prior_proposed_salary;
2278 end if;
2279 end get_pay_rate_change_amount;
2280
2281
2282 FUNCTION get_pay_rate_change_percent (
2283 p_group_plan_id in number,
2284 p_lf_evt_ocrd_dt in Date,
2285 p_oipl_id in number,
2286 p_group_per_in_ler_id in number,
2287 p_pl_id in number,
2288 p_ws_sub_acty_typ_cd in varchar2) return number is
2289
2290 l_new_proposed_salary number;
2291 l_prior_proposed_salary number;
2292 l_pay_proposal varchar2(30);
2293 begin
2294 l_new_proposed_salary :=
2295 get_pay_rate (
2296 p_group_plan_id,p_lf_evt_ocrd_dt,
2297 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2298 p_ws_sub_acty_typ_cd, 'NEW');
2299
2300 l_prior_proposed_salary :=
2301 get_pay_rate (
2302 p_group_plan_id,p_lf_evt_ocrd_dt,
2303 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2304 p_ws_sub_acty_typ_cd, 'PRIOR');
2305
2306
2307 if (l_new_proposed_salary = l_prior_proposed_salary) then
2308 return 0;
2309 else
2310 if (l_new_proposed_salary = 0 ) then
2311 return 0;
2312 elsif (l_prior_proposed_salary = 0) then
2313 return 100;
2314 end if;
2315 return round(((l_new_proposed_salary - l_prior_proposed_salary) / l_prior_proposed_salary) * 100,2);
2316 end if;
2317 end get_pay_rate_change_percent;
2318
2319 FUNCTION get_pay_rate_change_date (
2320 p_group_plan_id in number,
2321 p_lf_evnt_ocrd_dt in Date,
2322 p_oipl_id in number,
2323 p_group_per_in_ler_id in number,
2324 p_pl_id in number,
2325 p_ws_sub_acty_typ_cd in varchar2,
2326 p_new_or_prior in varchar2) return varchar2 is
2327
2328
2329 l_change_date varchar2(30);
2330
2331 -- return the comp_posting_date
2332 cursor c_pay_proposal_date is
2333 select max(change_date)
2334 from per_pay_proposals ppp
2335 ,ben_cwb_person_rates rts
2336 where rts.pay_proposal_id = ppp.pay_proposal_id
2337 and rts.group_per_in_ler_id = p_group_per_in_ler_id
2338 and rts.group_pl_id = p_group_plan_id
2339 and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
2340 and rts.ws_val is not null
2341 and rts.elig_flag = 'Y';
2342
2343
2344 cursor c_prior_pay_proposal_date is
2345 select BASE_SALARY_CHANGE_DATE
2346 from ben_cwb_person_info
2347 where group_per_in_ler_id = p_group_per_in_ler_id;
2348
2349 begin
2350 -- *********
2351 if (p_new_or_prior = 'NEW') then
2352 open c_pay_proposal_date;
2353 fetch c_pay_proposal_date into l_change_date;
2354 close c_pay_proposal_date;
2355 else
2356 -- we need to get the prior salary
2357 open c_prior_pay_proposal_date;
2358 fetch c_prior_pay_proposal_date into l_change_date;
2359 close c_prior_pay_proposal_date;
2360 end if;
2361
2362 return l_change_date;
2363
2364 end get_pay_rate_change_date;
2365
2366
2367 FUNCTION get_pay_rate_basis (
2368 p_group_plan_id in number,
2369 p_lf_evnt_ocrd_dt in Date,
2370 p_oipl_id in number,
2371 p_group_per_in_ler_id in number,
2372 p_pl_id in number,
2373 p_ws_sub_acty_typ_cd in varchar2) return Varchar2 is
2374
2375 Cursor c_pay_rate_basis is
2376 select initcap(pay_basis)
2377 from per_pay_bases ppb,
2378 per_all_assignments_f paaf
2379 where ppb.pay_basis_id = paaf.pay_basis_id
2380 and sysdate between paaf.effective_start_date and paaf.effective_end_date
2381 and paaf.primary_flag = 'Y'
2382 and paaf.assignment_id = g_person_rates_rec.assignment_id;
2383
2384 l_pay_basis per_pay_bases.pay_basis%Type;
2385 BEGIN
2386
2387 populate_person_rates_rec (
2388 p_group_plan_id,p_lf_evnt_ocrd_dt,
2389 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2390 p_ws_sub_acty_typ_cd, 'NEW');
2391
2392 OPEN c_pay_rate_basis;
2393 fetch c_pay_rate_basis into l_pay_basis;
2394 close c_pay_rate_basis;
2395
2396 return l_pay_basis;
2397 end get_pay_rate_basis;
2398
2399 FUNCTION get_plan_rate_ws_amt (
2400 p_group_plan_id in number,
2401 p_lf_evnt_ocrd_dt in Date,
2402 p_oipl_id in number,
2403 p_group_per_in_ler_id in number,
2404 p_pl_id in number,
2405 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2406 begin
2407 populate_person_rates_rec (
2408 p_group_plan_id,p_lf_evnt_ocrd_dt,
2409 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2413 end get_plan_rate_ws_amt;
2410 p_ws_sub_acty_typ_cd, 'NEW');
2411
2412 return g_person_rates_rec.ws_val;
2414
2415 FUNCTION get_plan_rate_elig_sal (
2416 p_group_plan_id in number,
2417 p_lf_evnt_ocrd_dt in Date,
2418 p_oipl_id in number,
2419 p_group_per_in_ler_id in number,
2420 p_pl_id in number,
2421 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2422 begin
2423 populate_person_rates_rec (
2424 p_group_plan_id,p_lf_evnt_ocrd_dt,
2425 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2426 p_ws_sub_acty_typ_cd, 'NEW');
2427
2428 return g_person_rates_rec.elig_sal_val;
2429 end get_plan_rate_elig_sal;
2430
2431 FUNCTION get_plan_percent_elig_sal (
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 number is
2438 begin
2439 populate_person_rates_rec (
2440 p_group_plan_id,p_lf_evnt_ocrd_dt,
2441 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2442 p_ws_sub_acty_typ_cd, 'NEW');
2443
2444 if (g_person_rates_rec.elig_sal_val <>0 ) then
2445 return round( (g_person_rates_rec.ws_val / g_person_rates_rec.elig_sal_val) * 100,2);
2446 else
2447 return to_number(null);
2448 end if;
2449
2450 end get_plan_percent_elig_sal;
2451
2452 FUNCTION get_plan_rate_rec_amt (
2453 p_group_plan_id in number,
2454 p_lf_evnt_ocrd_dt in Date,
2455 p_oipl_id in number,
2456 p_group_per_in_ler_id in number,
2457 p_pl_id in number,
2458 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2459 begin
2460 populate_person_rates_rec (
2461 p_group_plan_id,p_lf_evnt_ocrd_dt,
2462 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2463 p_ws_sub_acty_typ_cd, 'NEW');
2464
2465 return g_person_rates_rec.rec_val;
2466 end get_plan_rate_rec_amt;
2467
2468 FUNCTION get_plan_rate_other_sal (
2469 p_group_plan_id in number,
2470 p_lf_evnt_ocrd_dt in Date,
2471 p_oipl_id in number,
2472 p_group_per_in_ler_id in number,
2473 p_pl_id in number,
2474 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2475 begin
2476 populate_person_rates_rec (
2477 p_group_plan_id,p_lf_evnt_ocrd_dt,
2478 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2479 p_ws_sub_acty_typ_cd, 'NEW');
2480
2481 return g_person_rates_rec.oth_comp_val;
2482 end get_plan_rate_other_sal;
2483
2484 FUNCTION get_plan_rate_stat_sal (
2485 p_group_plan_id in number,
2486 p_lf_evnt_ocrd_dt in Date,
2487 p_oipl_id in number,
2488 p_group_per_in_ler_id in number,
2489 p_pl_id in number,
2490 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2491 begin
2492 populate_person_rates_rec (
2493 p_group_plan_id,p_lf_evnt_ocrd_dt,
2494 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2495 p_ws_sub_acty_typ_cd, 'NEW');
2496
2497 return g_person_rates_rec.stat_sal_val;
2498 end get_plan_rate_stat_sal;
2499
2500 FUNCTION get_plan_rate_total_comp (
2501 p_group_plan_id in number,
2502 p_lf_evnt_ocrd_dt in Date,
2503 p_oipl_id in number,
2504 p_group_per_in_ler_id in number,
2505 p_pl_id in number,
2506 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2507 begin
2508 populate_person_rates_rec (
2509 p_group_plan_id,p_lf_evnt_ocrd_dt,
2510 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2511 p_ws_sub_acty_typ_cd, 'NEW');
2512
2513 return g_person_rates_rec.tot_comp_val;
2514 end get_plan_rate_total_comp;
2515
2516 FUNCTION get_plan_rate_misc1 (
2517 p_group_plan_id in number,
2518 p_lf_evnt_ocrd_dt in Date,
2519 p_oipl_id in number,
2520 p_group_per_in_ler_id in number,
2521 p_pl_id in number,
2522 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2523 begin
2524 populate_person_rates_rec (
2525 p_group_plan_id,p_lf_evnt_ocrd_dt,
2526 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2527 p_ws_sub_acty_typ_cd, 'NEW');
2528
2529 return g_person_rates_rec.misc1_val;
2530 end get_plan_rate_misc1;
2531
2532 FUNCTION get_plan_rate_misc2 (
2533 p_group_plan_id in number,
2534 p_lf_evnt_ocrd_dt in Date,
2535 p_oipl_id in number,
2536 p_group_per_in_ler_id in number,
2537 p_pl_id in number,
2538 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2539 begin
2540 populate_person_rates_rec (
2541 p_group_plan_id,p_lf_evnt_ocrd_dt,
2542 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2543 p_ws_sub_acty_typ_cd, 'NEW');
2544
2545 return g_person_rates_rec.misc2_val;
2546 end get_plan_rate_misc2;
2547
2548 FUNCTION get_plan_rate_misc3 (
2549 p_group_plan_id in number,
2550 p_lf_evnt_ocrd_dt in Date,
2551 p_oipl_id in number,
2552 p_group_per_in_ler_id in number,
2553 p_pl_id in number,
2554 p_ws_sub_acty_typ_cd in varchar2 ) return number is
2555 begin
2556 populate_person_rates_rec (
2557 p_group_plan_id,p_lf_evnt_ocrd_dt,
2558 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2559 p_ws_sub_acty_typ_cd, 'NEW');
2560
2561 return g_person_rates_rec.misc3_val;
2562 end get_plan_rate_misc3;
2563
2564 PROCEDURE populate_asgn_txn_rec (
2568 BEGIN
2565 p_assignment_id in number,
2566 p_asg_updt_eff_date in date) IS
2567
2569
2570 --IF (nvl(g_asgn_txn_rec.assignment_id,-1) <> p_assignment_id OR
2571 -- 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
2572
2573 g_asgn_txn_rec := null;
2574 OPEN g_cursor_asgn_txn (p_assignment_id, to_char(p_asg_updt_eff_date,'RRRR/MM/DD'));
2575 FETCH g_cursor_asgn_txn into g_asgn_txn_rec;
2576 CLOSE g_cursor_asgn_txn;
2577
2578 --END IF;
2579 END populate_asgn_txn_rec;
2580
2581 FUNCTION get_new_job (
2582 p_assignment_id in number,
2583 p_asg_updt_eff_date in date) return varchar2 IS
2584 begin
2585 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2586
2587 if ( g_asgn_txn_rec.job_id is not null) then
2588 return ( hr_general.decode_job(g_asgn_txn_rec.job_id));
2589 end if;
2590
2591 return null;
2592 end get_new_job;
2593
2594 FUNCTION get_new_position (
2595 p_assignment_id in number,
2596 p_asg_updt_eff_date in date) return varchar2 IS
2597 begin
2598 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2599 -- Using DECODE_POSITION_LATEST_NAME instead of DECODE_POSITION : Anadi
2600 if ( g_asgn_txn_rec.position_id is not null) then
2601 return ( hr_general.DECODE_POSITION_LATEST_NAME(g_asgn_txn_rec.position_id));
2602 end if;
2603
2604 return null;
2605 end get_new_position;
2606
2607 FUNCTION get_new_grade (
2608 p_assignment_id in number,
2609 p_asg_updt_eff_date in date) return varchar2 IS
2610 begin
2611 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2612
2613 if ( g_asgn_txn_rec.grade_id is not null) then
2614 return ( hr_general.decode_grade (g_asgn_txn_rec.grade_id));
2615 end if;
2616
2617 return null;
2618 end get_new_grade ;
2619
2620
2621 FUNCTION get_new_people_group(
2622 p_assignment_id in number,
2623 p_asg_updt_eff_date in date) return varchar2 IS
2624 begin
2625 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2626
2627 if ( g_asgn_txn_rec.people_group_id is not null) then
2628 return ( hr_general.decode_people_group (g_asgn_txn_rec.people_group_id));
2629 end if;
2630
2631 return null;
2632 end get_new_people_group ;
2633
2634 FUNCTION get_new_asgn_flex(
2635 p_assignment_id in number,
2636 p_asg_updt_eff_date in date,
2637 p_asg_flex_num in number
2638 ) return varchar2 IS
2639
2640 l_asgn_flex ben_transaction.attribute11%Type;
2641 begin
2642 populate_asgn_txn_rec (p_assignment_id, p_asg_updt_eff_date);
2643
2644 if (p_asg_flex_num = 1) then
2645 l_asgn_flex := g_asgn_txn_rec.asgn_flex1;
2646 elsif (p_asg_flex_num = 2) then
2647 l_asgn_flex := g_asgn_txn_rec.asgn_flex2;
2648 elsif (p_asg_flex_num = 3) then
2649 l_asgn_flex := g_asgn_txn_rec.asgn_flex3;
2650 elsif (p_asg_flex_num = 4) then
2651 l_asgn_flex := g_asgn_txn_rec.asgn_flex4;
2652 elsif (p_asg_flex_num = 5) then
2653 l_asgn_flex := g_asgn_txn_rec.asgn_flex5;
2654 elsif (p_asg_flex_num = 6) then
2655 l_asgn_flex := g_asgn_txn_rec.asgn_flex6;
2656 elsif (p_asg_flex_num = 7) then
2657 l_asgn_flex := g_asgn_txn_rec.asgn_flex7;
2658 elsif (p_asg_flex_num = 8) then
2659 l_asgn_flex := g_asgn_txn_rec.asgn_flex8;
2660 elsif (p_asg_flex_num = 9) then
2661 l_asgn_flex := g_asgn_txn_rec.asgn_flex9;
2662 elsif (p_asg_flex_num = 10) then
2663 l_asgn_flex := g_asgn_txn_rec.asgn_flex10;
2664 elsif (p_asg_flex_num = 11) then
2665 l_asgn_flex := g_asgn_txn_rec.asgn_flex11;
2666 elsif (p_asg_flex_num = 12) then
2667 l_asgn_flex := g_asgn_txn_rec.asgn_flex12;
2668 elsif (p_asg_flex_num = 13) then
2669 l_asgn_flex := g_asgn_txn_rec.asgn_flex13;
2670 elsif (p_asg_flex_num = 14) then
2671 l_asgn_flex := g_asgn_txn_rec.asgn_flex14;
2672 elsif (p_asg_flex_num = 15) then
2673 l_asgn_flex := g_asgn_txn_rec.asgn_flex15;
2674 elsif (p_asg_flex_num = 16) then
2675 l_asgn_flex := g_asgn_txn_rec.asgn_flex16;
2676 elsif (p_asg_flex_num = 17) then
2677 l_asgn_flex := g_asgn_txn_rec.asgn_flex17;
2678 elsif (p_asg_flex_num = 18) then
2679 l_asgn_flex := g_asgn_txn_rec.asgn_flex18;
2680 elsif (p_asg_flex_num = 19) then
2681 l_asgn_flex := g_asgn_txn_rec.asgn_flex19;
2682 elsif (p_asg_flex_num = 20) then
2683 l_asgn_flex := g_asgn_txn_rec.asgn_flex20;
2684 elsif (p_asg_flex_num = 21) then
2685 l_asgn_flex := g_asgn_txn_rec.asgn_flex21;
2686 elsif (p_asg_flex_num = 22) then
2687 l_asgn_flex := g_asgn_txn_rec.asgn_flex22;
2688 elsif (p_asg_flex_num = 23) then
2689 l_asgn_flex := g_asgn_txn_rec.asgn_flex23;
2690 elsif (p_asg_flex_num = 24) then
2691 l_asgn_flex := g_asgn_txn_rec.asgn_flex24;
2692 elsif (p_asg_flex_num = 25) then
2693 l_asgn_flex := g_asgn_txn_rec.asgn_flex25;
2694 elsif (p_asg_flex_num = 26) then
2695 l_asgn_flex := g_asgn_txn_rec.asgn_flex26;
2696 elsif (p_asg_flex_num = 27) then
2697 l_asgn_flex := g_asgn_txn_rec.asgn_flex27;
2698 elsif (p_asg_flex_num = 28) then
2699 l_asgn_flex := g_asgn_txn_rec.asgn_flex28;
2700 elsif (p_asg_flex_num = 29) then
2701 l_asgn_flex := g_asgn_txn_rec.asgn_flex29;
2702 elsif (p_asg_flex_num = 30) then
2703 l_asgn_flex := g_asgn_txn_rec.asgn_flex30;
2704 end if;
2705
2706 return l_asgn_flex;
2707
2708 end get_new_asgn_flex;
2709
2710 function get_new_perf_rating (
2714
2711 p_assignment_id in number,
2712 p_perf_revw_strt_dt in date,
2713 p_emp_interview_typ_cd in varchar2 ) return varchar2 IS
2715 CURSOR c_perf_rate is
2716 select hr_general.decode_lookup('PERFORMANCE_RATING',attribute3)
2717 From ben_transaction
2718 where transaction_id = p_assignment_id
2719 and transaction_type = 'CWBPERF'||to_char(p_perf_revw_strt_dt,'rrrr/mm/dd')
2720 ||p_emp_interview_typ_cd;
2721
2722 l_perf_rating ben_transaction.attribute3%Type;
2723 begin
2724 open c_perf_rate;
2725 fetch c_perf_rate into l_perf_rating;
2726 close c_perf_rate;
2727
2728 return l_perf_rating;
2729
2730 end get_new_perf_rating;
2731
2732 --
2733 function get_group_short_name (
2734 p_plan_id in number ,
2735 p_lf_evt_ocrd_dt in date ) return varchar2 is
2736 cursor c_doc_short_name is
2737 select pqh_document_short_name
2738 from ben_cwb_pl_dsgn
2739 where pl_id = p_plan_id
2740 and oipl_id = -1
2741 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
2742
2743 l_short_name pqh_documents_f.short_name%type;
2744 begin
2745
2746 if (p_plan_id is null) then
2747 return null;
2748 end if;
2749
2750 open c_doc_short_name;
2751 fetch c_doc_short_name into l_short_name;
2752 close c_doc_short_name;
2753
2754 return l_short_name;
2755 end get_group_short_name;
2756
2757
2758 procedure populate_ws_mgr(p_group_per_in_ler_id in number) is
2759
2760 cursor c_ws_mgr_name is
2761 select info.full_name, info.brief_name, info.custom_name
2762 from ben_cwb_person_info info,
2763 ben_cwb_group_hrchy hrchy
2764 where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
2765 and hrchy.mgr_per_in_ler_id = info.group_per_in_ler_id
2766 and hrchy.lvl_num = 1;
2767 begin
2768
2769 open c_ws_mgr_name;
2770 g_ws_mgr_full_name := null;
2771 g_ws_mgr_brief_name := null;
2772 g_ws_mgr_custom_name := null;
2773 fetch c_ws_mgr_name into g_ws_mgr_full_name,g_ws_mgr_brief_name,g_ws_mgr_custom_name;
2774 close c_ws_mgr_name;
2775
2776 end populate_ws_mgr;
2777
2778
2779 function get_ws_mgr_full_name(p_group_per_in_ler_id in number) return varchar2 is
2780 begin
2781 --if (g_ws_mgr_full_name is null) then
2782 populate_ws_mgr(p_group_per_in_ler_id);
2783 --end if;
2784 return g_ws_mgr_full_name;
2785 end get_ws_mgr_full_name;
2786
2787
2788 function get_ws_mgr_brief_name(p_group_per_in_ler_id in number) return varchar2 is
2789 begin
2790 --if (g_ws_mgr_brief_name is null) then
2791 populate_ws_mgr(p_group_per_in_ler_id);
2792 --end if;
2793 return g_ws_mgr_brief_name;
2794 end get_ws_mgr_brief_name;
2795
2796
2797 function get_ws_mgr_custom_name(p_group_per_in_ler_id in number) return varchar2 is
2798 begin
2799 --if (g_ws_mgr_custom_name is null) then
2800 populate_ws_mgr(p_group_per_in_ler_id);
2801 --end if;
2802 return g_ws_mgr_custom_name;
2803 end get_ws_mgr_custom_name;
2804
2805
2806 --
2807
2808 Function get_option_currency(
2809 p_group_plan_id in number,
2810 p_lf_evnt_ocrd_dt in Date,
2811 p_oipl_id in number,
2812 p_group_per_in_ler_id in number,
2813 p_pl_id in number,
2814 p_ws_sub_acty_typ_cd in varchar2,
2815 p_oipl_ordr_num in number) return varchar2
2816 Is
2817
2818 Cursor cur_option_currency is
2819 Select currency
2820 From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
2821 Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
2822 And bcpd.group_pl_id = p_group_plan_id
2823 And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
2824 And bcpr.pl_id = bcpd.pl_id
2825 And bcpr.group_pl_id = bcpd.group_pl_id
2826 And bcpr.oipl_id = bcpd.oipl_id
2827 And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
2828 And bcpr.elig_flag = 'Y'
2829 And oipl_ordr_num = p_oipl_ordr_num;
2830
2831 l_option_currency varchar2(30);
2832 Begin
2833 open cur_option_currency;
2834 fetch cur_option_currency into l_option_currency;
2835 close cur_option_currency;
2836 return l_option_currency;
2837 End get_option_currency;
2838
2839
2840
2841 Function get_option1_currency(
2842 p_group_plan_id in number,
2843 p_lf_evnt_ocrd_dt in Date,
2844 p_oipl_id in number,
2845 p_group_per_in_ler_id in number,
2846 p_pl_id in number,
2847 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2848 Is
2849 l_option1_currency varchar2(30);
2850 Begin
2851 l_option1_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2852 p_oipl_id,p_group_per_in_ler_id,
2853 p_pl_id,p_ws_sub_acty_typ_cd,1);
2854 return l_option1_currency;
2855 End get_option1_currency;
2856
2857 Function get_option2_currency(
2858 p_group_plan_id in number,
2859 p_lf_evnt_ocrd_dt in Date,
2860 p_oipl_id in number,
2861 p_group_per_in_ler_id in number,
2862 p_pl_id in number,
2863 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2864 Is
2865 l_option2_currency varchar2(30);
2866 Begin
2867 l_option2_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2868 p_oipl_id,p_group_per_in_ler_id,
2869 p_pl_id,p_ws_sub_acty_typ_cd,2);
2870 return l_option2_currency;
2871 End get_option2_currency;
2872
2876 p_oipl_id in number,
2873 Function get_option3_currency(
2874 p_group_plan_id in number,
2875 p_lf_evnt_ocrd_dt in Date,
2877 p_group_per_in_ler_id in number,
2878 p_pl_id in number,
2879 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2880 Is
2881 l_option3_currency varchar2(30);
2882 Begin
2883 l_option3_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2884 p_oipl_id,p_group_per_in_ler_id,
2885 p_pl_id,p_ws_sub_acty_typ_cd,3);
2886 return l_option3_currency;
2887 End get_option3_currency;
2888
2889 Function get_option4_currency(
2890 p_group_plan_id in number,
2891 p_lf_evnt_ocrd_dt in Date,
2892 p_oipl_id in number,
2893 p_group_per_in_ler_id in number,
2894 p_pl_id in number,
2895 p_ws_sub_acty_typ_cd in varchar2) return varchar2
2896 Is
2897 l_option4_currency varchar2(30);
2898 Begin
2899 l_option4_currency := get_option_currency(p_group_plan_id,p_lf_evnt_ocrd_dt,
2900 p_oipl_id,p_group_per_in_ler_id,
2901 p_pl_id,p_ws_sub_acty_typ_cd,4);
2902 return l_option4_currency;
2903 End get_option4_currency;
2904
2905 FUNCTION get_plan_rate_start_dt (
2906 p_group_plan_id in number,
2907 p_lf_evnt_ocrd_dt in Date,
2908 p_oipl_id in number,
2909 p_group_per_in_ler_id in number,
2910 p_pl_id in number,
2911 p_ws_sub_acty_typ_cd in varchar2 ) return varchar2 is
2912 begin
2913 populate_person_rates_rec (
2914 p_group_plan_id,p_lf_evnt_ocrd_dt,
2915 p_oipl_id,p_group_per_in_ler_id, p_pl_id,
2916 p_ws_sub_acty_typ_cd, 'NEW');
2917 return g_person_rates_rec.ws_rt_start_date;
2918 end get_plan_rate_start_dt;
2919
2920 Function get_option1_rate_start_dt(
2921 p_group_plan_id in number,
2922 p_lf_evnt_ocrd_dt in Date,
2923 p_oipl_id in number,
2924 p_group_per_in_ler_id in number,
2925 p_pl_id in number,
2926 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2927 Begin
2928 populate_person_option1_rec(
2929 p_group_plan_id,
2930 p_lf_evnt_ocrd_dt,
2931 p_oipl_id,
2932 p_group_per_in_ler_id,
2933 p_pl_id,
2934 p_ws_sub_acty_typ_cd);
2935 return g_opt1_person_rates_rec.ws_rt_start_date;
2936 end get_option1_rate_start_dt;
2937
2938 Function get_option2_rate_start_dt(
2939 p_group_plan_id in number,
2940 p_lf_evnt_ocrd_dt in Date,
2941 p_oipl_id in number,
2942 p_group_per_in_ler_id in number,
2943 p_pl_id in number,
2944 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2945 Begin
2946 populate_person_option2_rec(
2947 p_group_plan_id,
2948 p_lf_evnt_ocrd_dt,
2949 p_oipl_id,
2950 p_group_per_in_ler_id,
2951 p_pl_id,
2952 p_ws_sub_acty_typ_cd);
2953 return g_opt2_person_rates_rec.ws_rt_start_date;
2954 end get_option2_rate_start_dt;
2955
2956 Function get_option3_rate_start_dt(
2957 p_group_plan_id in number,
2958 p_lf_evnt_ocrd_dt in Date,
2959 p_oipl_id in number,
2960 p_group_per_in_ler_id in number,
2961 p_pl_id in number,
2962 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2963 Begin
2964 populate_person_option3_rec(
2965 p_group_plan_id,
2966 p_lf_evnt_ocrd_dt,
2967 p_oipl_id,
2968 p_group_per_in_ler_id,
2969 p_pl_id,
2970 p_ws_sub_acty_typ_cd);
2971 return g_opt3_person_rates_rec.ws_rt_start_date;
2972 end get_option3_rate_start_dt;
2973
2974 Function get_option4_rate_start_dt(
2975 p_group_plan_id in number,
2976 p_lf_evnt_ocrd_dt in Date,
2977 p_oipl_id in number,
2978 p_group_per_in_ler_id in number,
2979 p_pl_id in number,
2980 p_ws_sub_acty_typ_cd in varchar2) return varchar2 is
2981 Begin
2982 populate_person_option4_rec(
2983 p_group_plan_id,
2984 p_lf_evnt_ocrd_dt,
2985 p_oipl_id,
2986 p_group_per_in_ler_id,
2987 p_pl_id,
2988 p_ws_sub_acty_typ_cd);
2989 return g_opt4_person_rates_rec.ws_rt_start_date;
2990 end get_option4_rate_start_dt;
2991
2992 Function get_custom_segment_message(
2993 p_custom_seg_text in varchar2 ) return varchar2
2994 is
2995 l_return_text varchar2(200) := null;
2996 l_prod_name varchar2(5) := null;
2997 l_msg_name varchar2(100) := null;
2998 begin
2999 l_return_text := p_custom_seg_text;
3000 if(p_custom_seg_text is not null) then
3001 if nvl(substr(p_custom_seg_text,1,4),'XXX') = 'INFO' then
3002 if((substr(p_custom_seg_text,6,3) is not null) AND (substr(p_custom_seg_text,10) is not null)) then
3003 l_prod_name := substr(p_custom_seg_text,6,3);
3004 l_msg_name := substr(p_custom_seg_text,10);
3005 l_return_text := fnd_message.get_string(l_prod_name,l_msg_name);
3006 end if;
3007 end if;
3008 end if;
3009 return l_return_text;
3010 end get_custom_segment_message;
3011
3012 --
3013
3014 /* ---------------------------------------------------------------------
3015 END -- Changes for Printable document
3016 --------------------------------------------------------------------- */
3017 END;
[Home] [Help]