[Home] [Help]
PACKAGE: APPS.BENUTILS
Source
1 package benutils AUTHID CURRENT_USER as
2 /* $Header: benutils.pkh 120.1.12000000.1 2007/01/19 19:09:41 appldev noship $ */
3 --
4 g_banner_asterix varchar2(70) := rpad('*',70,'*');
5 g_banner_plus varchar2(70) := rpad('+',70,'+');
6 g_banner_minus varchar2(70) := rpad('-',70,'-');
7 g_sequence number := 0;
8 g_thread_id number := 0;
9 g_benefit_action_id number;
10 g_inelg_action_cd varchar2(40);
11 g_empty_tab ff_exec.outputs_t; -- donot populate. Only to be used a default value
12 type g_number_table is varray(10000000) of number;
13 type g_varchar2_table is varray(10000000) of varchar2(2000);
14 type g_v2_30_table is varray(10000000) of varchar2(30);
15 type g_v2_150_table is varray(10000000) of varchar2(150);
16 type g_date_table is varray(10000000) of date;
17 --
18 -- Cache structure for lookups
19 --
20 type g_cache_lookup_object_rec is record
21 (lookup_type hr_lookups.lookup_type%type,
22 lookup_code hr_lookups.lookup_code%type);
23 --
24 type g_cache_lookup_object_table is table of g_cache_lookup_object_rec
25 index by binary_integer;
26 --
27 g_cache_lookup_object g_cache_lookup_object_table;
28 --
29 type g_active_life_event is record
30 (per_in_ler_id ben_per_in_ler.per_in_ler_id%type,
31 lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type,
32 ntfn_dt ben_per_in_ler.ntfn_dt%type,
33 ler_id ben_per_in_ler.ler_id%type,
34 name ben_ler_f.name%type,
35 typ_cd ben_ler_f.typ_cd%type,
36 ovridg_le_flag ben_ler_f.ovridg_le_flag%type,
37 ptnl_ler_trtmt_cd ben_ler_f.ptnl_ler_trtmt_cd%type,
38 object_version_number ben_per_in_ler.object_version_number%type,
39 ptnl_ler_for_per_id ben_per_in_ler.ptnl_ler_for_per_id%type,
40 qualg_evt_flag ben_ler_f.qualg_evt_flag%type);
41 --
42 type g_ler is record
43 (ler_id ben_ler_f.ler_id%type,
44 ler_eval_rl ben_ler_f.ler_eval_rl%type,
45 name ben_ler_f.name%type);
46 --
47 type g_ptnl_ler is record
48 (ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%type,
49 object_version_number ben_ptnl_ler_for_per.object_version_number%type);
50 --
51 -- Create eligibility record
52 --
53 type g_batch_elig_rec is record
54 (batch_elig_id number,
55 benefit_action_id number,
56 person_id number,
57 pgm_id number,
58 pl_id number,
59 oipl_id number,
60 elig_flag varchar2(30),
61 inelig_text varchar2(2000),
62 business_group_id number,
63 effective_date date,
64 object_version_number number);
65 --
66 type g_batch_elig_table is varray(10000000) of g_batch_elig_rec;
67 --
68 type g_batch_ler_rec is record
69 (batch_ler_id number,
70 benefit_action_id number,
71 person_id number,
72 ler_id number,
73 lf_evt_ocrd_dt date,
74 replcd_flag varchar2(30),
75 crtd_flag varchar2(30),
76 tmprl_flag varchar2(30),
77 dltd_flag varchar2(30),
78 open_and_clsd_flag varchar2(30),
79 clsd_flag varchar2(30),
80 not_crtd_flag varchar2(30),
81 stl_actv_flag varchar2(30),
82 clpsd_flag varchar2(30),
83 clsn_flag varchar2(30),
84 no_effect_flag varchar2(30),
85 cvrge_rt_prem_flag varchar2(30),
86 per_in_ler_id number,
87 business_group_id number,
88 effective_date date,
89 object_version_number number);
90 --
91 type g_batch_ler_table is varray(10000000) of g_batch_ler_rec;
92 --
93 type g_batch_elctbl_rec is record
94 (batch_elctbl_id number,
95 benefit_action_id number,
96 person_id number,
97 pgm_id number,
98 pl_id number,
99 oipl_id number,
100 enrt_cvg_strt_dt date,
101 enrt_perd_strt_dt date,
102 enrt_perd_end_dt date,
103 erlst_deenrt_dt date,
104 dflt_enrt_dt date,
105 enrt_typ_cycl_cd varchar2(30),
106 comp_lvl_cd varchar2(30),
107 mndtry_flag varchar2(30),
108 dflt_flag varchar2(30),
109 business_group_id number,
110 object_version_number number,
111 effective_date date);
112 --
113 type g_batch_elctbl_table is varray(10000000) of g_batch_elctbl_rec;
114 --
115 type g_batch_rate_rec is record
116 (batch_rt_id number,
117 benefit_action_id number,
118 person_id number,
119 pgm_id number,
120 pl_id number,
121 oipl_id number,
122 bnft_rt_typ_cd varchar2(30),
123 dflt_flag varchar2(30),
124 val number,
125 old_val number,
126 tx_typ_cd varchar2(30),
127 acty_typ_cd varchar2(30),
128 mn_elcn_val number,
129 mx_elcn_val number,
130 incrmt_elcn_val number,
131 dflt_val number,
132 rt_strt_dt date,
133 business_group_id number,
134 object_version_number number,
135 enrt_cvg_strt_dt date,
136 enrt_cvg_thru_dt date,
137 actn_cd varchar2(30),
138 close_actn_itm_dt date,
139 effective_date date);
140 --
141 type g_batch_rate_table is varray(10000000) of g_batch_rate_rec;
142 --
143 type g_batch_dpnt_rec is record
144 (batch_dpnt_id number,
145 benefit_action_id number,
146 person_id number,
147 pgm_id number,
148 pl_id number,
149 oipl_id number,
150 contact_typ_cd varchar2(30),
151 dpnt_person_id number,
152 business_group_id number,
153 object_version_number number,
154 enrt_cvg_strt_dt date,
155 enrt_cvg_thru_dt date,
156 actn_cd varchar2(30),
157 effective_date date);
158 --
159 type g_batch_dpnt_table is varray(10000000) of g_batch_dpnt_rec;
160 --
161 type g_batch_param_rec is record
162 (PROCESS_DATE date,
163 MODE_CD varchar2(30),
164 DERIVABLE_FACTORS_FLAG varchar2(30),
165 VALIDATE_FLAG varchar2(30),
166 PERSON_ID number,
167 PERSON_TYPE_ID number,
168 PGM_ID number,
169 BUSINESS_GROUP_ID number,
170 PL_ID number,
171 POPL_ENRT_TYP_CYCL_ID number,
172 NO_PROGRAMS_FLAG varchar2(30),
173 NO_PLANS_FLAG varchar2(30),
174 COMP_SELECTION_RL number,
175 PERSON_SELECTION_RL number,
176 LER_ID number,
177 ORGANIZATION_ID number,
178 BENFTS_GRP_ID number,
179 LOCATION_ID number,
180 PSTL_ZIP_RNG_ID number,
181 RPTG_GRP_ID number,
182 PL_TYP_ID number,
183 OPT_ID number,
184 ELIGY_PRFL_ID number,
185 VRBL_RT_PRFL_ID number,
186 LEGAL_ENTITY_ID number,
187 PAYROLL_ID number,
188 CM_TRGR_TYP_CD varchar2(30),
189 DEBUG_MESSAGES_FLAG varchar2(30),
190 CM_TYP_ID number,
191 AGE_FCTR_ID number,
192 MIN_AGE number,
193 MAX_AGE number,
194 LOS_FCTR_ID number,
195 MIN_LOS number,
196 MAX_LOS number,
197 CMBN_AGE_LOS_FCTR_ID number,
198 MIN_CMBN number,
199 MAX_CMBN number,
200 DATE_FROM date,
201 ELIG_ENROL_CD varchar2(30),
202 ACTN_TYP_ID number,
203 AUDIT_LOG_FLAG varchar2(30),
204 --
205 -- PB : 5422
206 -- added.
207 LF_EVT_OCRD_DT varchar2(240),
208 --
209 -- PB : Healthnet change : added LMT_PRPNIP_BY_ORG_FLAG
210 --
211 LMT_PRPNIP_BY_ORG_FLAG varchar2(30),
212 INELG_ACTION_CD varchar2(30));
213 --
214 type g_batch_param_table is table of g_batch_param_rec
215 index by binary_integer;
216 /*
217 --
218 -- Remove in ben_type
219 --
220 type g_batch_action_rec is record
221 (person_action_id number,
222 action_status_cd varchar2(30),
223 ler_id number,
224 object_version_number number,
225 effective_date date);
226 --
227 type g_batch_action_table is varray(10000000) of g_batch_action_rec;
228 --
229 type g_batch_proc_rec is record
230 (batch_ler_id number,
231 benefit_action_id number,
232 strt_dt date,
233 end_dt date,
234 strt_tm varchar2(30),
235 end_tm varchar2(30),
236 elpsd_tm varchar2(30),
237 per_slctd number,
238 per_proc number,
239 per_unproc number,
240 per_proc_succ number,
241 per_err number,
242 business_group_id number,
243 object_version_number number);
244 --
245 type g_batch_proc_table is varray(10000000) of g_batch_proc_rec;
246 --
247 type g_batch_commu_rec is record
248 (batch_commu_id number,
249 benefit_action_id number,
250 person_id number,
251 per_cm_id number,
252 cm_typ_id number,
253 per_cm_prvdd_id number,
254 to_be_sent_dt date,
255 business_group_id number,
256 object_version_number number);
257 --
258 type g_batch_commu_table is varray(10000000) of g_batch_commu_rec;
259 */
260 --
261 -- Varrays
262 --
263 g_report_table_object ben_type.g_report_table := ben_type.g_report_table();
264 g_batch_action_table_object ben_type.g_batch_action_table
265 := ben_type.g_batch_action_table();
266 g_batch_proc_table_object ben_type.g_batch_proc_table
267 := ben_type.g_batch_proc_table();
268 g_batch_commu_table_object ben_type.g_batch_commu_table
269 := ben_type.g_batch_commu_table();
270 --
271 g_batch_param_table_object g_batch_param_table;
272 ----------------------------------------------------------------------------
273 -- rt_typ_calc
274 ----------------------------------------------------------------------------
275 PROCEDURE rt_typ_calc
276 (p_val IN number,
277 p_val_2 IN number,
278 p_rt_typ_cd IN varchar2,
279 p_calculated_val OUT NOCOPY number) ;
280
281 ------------------------------------------------------------------------
282 -- limit_checks
283 ------------------------------------------------------------------------
284 PROCEDURE limit_checks (p_lwr_lmt_val in number,
285 p_lwr_lmt_calc_rl in number,
286 p_upr_lmt_val in number,
287 p_upr_lmt_calc_rl in number,
288 p_effective_date in date,
289 p_assignment_id in number,
290 p_organization_id in number,
291 p_business_group_id in number,
292 p_pgm_id in number,
293 p_pl_id in number,
294 p_pl_typ_id in number,
295 p_opt_id in number,
296 p_ler_id in number,
297 p_acty_base_rt_id in number default null,
298 p_elig_per_elctbl_chc_id in number default null,
299 p_val in out nocopy number,
300 p_state in varchar2) ;
301
302 ------------------------------------------------------------------------
303 --
304 ------------------------------------------------------------------------
305 procedure write(p_rec in out nocopy ben_type.g_report_rec);
306 --
307 procedure write(p_rec in out nocopy g_batch_elig_rec);
308 --
309 procedure write(p_rec in out nocopy g_batch_ler_rec);
310 --
311 procedure write(p_rec in out nocopy ben_type.g_batch_proc_rec);
312 --
313 procedure write(p_rec in out nocopy ben_type.g_batch_action_rec);
314 --
315 procedure write(p_rec in out nocopy g_batch_elctbl_rec);
316 --
317 procedure write(p_rec in out nocopy g_batch_rate_rec);
318 --
319 procedure write(p_rec in out nocopy g_batch_dpnt_rec);
320 --
321 procedure write(p_rec in out nocopy ben_type.g_batch_commu_rec);
322 --
323 procedure write_table_and_file(p_table in boolean default true,
324 p_file in boolean default true);
325 --
326 procedure get_batch_parameters(p_benefit_action_id in number,
327 p_rec in out nocopy g_batch_param_rec);
328 --
329 function get_ler_name(p_typ_cd in varchar2,
330 p_business_group_id in number) return varchar2;
331 pragma restrict_references (get_ler_name, RNPS, WNPS,WNDS);
332 --
333 procedure set_cache_record_position;
334 --
335 procedure rollback_cache;
336 --
337 procedure update_life_event_cache(p_open_and_closed in varchar2 default null);
338 --
339 function get_assignment_id(p_person_id in number,
340 p_business_group_id in number,
341 p_effective_date in date) return number;
342 --
343 function get_lf_evt_ocrd_dt(p_person_id in number,
344 p_business_group_id in number,
345 p_ler_id in number default null,
346 p_effective_date in date) return date;
347 --
348 function get_per_in_ler_id(p_person_id in number,
349 p_business_group_id in number,
350 p_ler_id in number default null,
351 p_effective_date in date) return number;
352 --
353 -- added for unrestricted enhancement
354 function get_per_in_ler_id(p_person_id in number,
355 p_business_group_id in number,
356 p_ler_id in number default null,
357 p_lf_event_mode in varchar2,
358 p_effective_date in date) return number;
359 --
360 procedure get_active_life_event(p_person_id in number,
361 p_business_group_id in number,
362 p_effective_date in date,
366 procedure get_active_life_event(p_person_id in number,
363 p_rec out nocopy g_active_life_event);
364 --
365 -- added for unrestricted enhancement
367 p_business_group_id in number,
368 p_effective_date in date,
369 p_lf_event_mode in varchar2,
370 p_rec out nocopy g_active_life_event);
371 --
372 procedure get_ler(p_business_group_id in number,
373 p_typ_cd in varchar2,
374 p_effective_date in date,
375 p_lf_evt_oper_cd in varchar2 default null, /* GSP Rate Sync */
376 p_rec out nocopy g_ler);
377 --
378 procedure get_ler(p_business_group_id in number,
379 p_ler_id in number,
380 p_effective_date in date,
381 p_rec out nocopy g_ler);
382 --
383 procedure get_ptnl_ler(p_business_group_id in number,
384 p_person_id in number,
385 p_ler_id in number,
386 p_effective_date in date,
387 p_rec out nocopy g_ptnl_ler);
388 --
389 function get_primary_key(p_tablename in varchar2) return varchar2;
390 --
391 function lookups_exist(p_tablename in varchar2) return boolean;
392 --
393 procedure define_primary_key(p_tablename in varchar2);
394 --
395 function part_of_pkey(p_column_name in varchar2) return boolean;
396 --
397 function get_bp_name (p_tablename in varchar2) return varchar2;
398 --
399 function iftrue(p_expression in boolean,
400 p_true in varchar2,
401 p_false in varchar2) return varchar2;
402 --
403 function business_group_exists(p_tablename in varchar2) return boolean;
404 --
405 function table_datetracked(p_tablename in varchar2) return boolean;
406 --
407 function attributes_exist(p_tablename in varchar2) return boolean;
408 --
409 function get_pk_constraint_name(p_tablename in varchar2) return varchar2;
410 --
411 function column_changed(p_old_column in varchar2,
412 p_new_column in varchar2,
413 p_new_value in varchar2) return boolean;
414 --
415 function column_changed(p_old_column in date,
416 p_new_column in date,
417 p_new_value in varchar2) return boolean;
418 --
419 function column_changed(p_old_column in number,
420 p_new_column in number,
421 p_new_value in varchar2) return boolean;
422 --
423 procedure write(p_text in varchar2,
424 p_validate in boolean default false);
425 --
426 function do_rounding(p_rounding_cd in varchar2,
427 p_rounding_rl in number default null,
428 p_assignment_id in number default null,
429 p_value in number,
430 p_effective_date in date) return number;
431 --
432 function do_uom(p_date1 in date,
433 p_date2 in date,
434 p_uom in varchar2) return number;
435 --
436 function derive_date(p_date in date,
437 p_uom in varchar2,
438 p_min in number,
439 p_max in number,
440 p_value in varchar2,
441 p_decimal_level in varchar2 default 'N' ) return date;
442 --
443 function id(p_value in number) return varchar2;
444 --
445 function zero_to_null(p_value in number) return number;
446 --
447 function min_max_breach(p_min_value in number,
448 p_max_value in number,
449 p_old_value in number,
450 p_new_value in number,
451 p_break out nocopy varchar2,
452 p_decimal_level in varchar2 default 'N' ) return boolean;
453 --
454 procedure get_parameter(p_business_group_id in number,
455 p_batch_exe_cd in varchar2,
456 p_threads out nocopy number,
457 p_chunk_size out nocopy number,
458 p_max_errors out nocopy number);
459 --
460 function eot_to_null(p_date in date) return date;
461 --
462 function eot_to_null(p_date in varchar2) return varchar2;
463 --
464 function get_message_name return varchar2;
465 --
466 function set_to_oct1_prev_year(p_date in date) return date;
467 --
468 procedure init_lookups(p_lookup_type_1 in varchar2 default null,
469 p_lookup_type_2 in varchar2 default null,
470 p_lookup_type_3 in varchar2 default null,
471 p_lookup_type_4 in varchar2 default null,
472 p_lookup_type_5 in varchar2 default null,
473 p_lookup_type_6 in varchar2 default null,
474 p_lookup_type_7 in varchar2 default null,
475 p_lookup_type_8 in varchar2 default null,
476 p_lookup_type_9 in varchar2 default null,
477 p_lookup_type_10 in varchar2 default null,
478 p_effective_date in date);
479 --
480 function not_exists_in_hr_lookups(p_lookup_type in varchar2,
481 p_lookup_code in varchar2)
482 return boolean;
483 --
484 function formula_exists(p_formula_id in number,
485 p_formula_type_id in number,
486 p_business_group_id in number,
487 p_effective_date in date) return boolean;
488 --
489 function formula(p_formula_id in number,
490 p_business_group_id in number default null,
491 p_payroll_id in number default null,
492 p_payroll_action_id in number default null,
493 p_assignment_id in number default null,
494 p_assignment_action_id in number default null,
495 p_org_pay_method_id in number default null,
496 p_per_pay_method_id in number default null,
497 p_organization_id in number default null,
498 p_tax_unit_id in number default null,
499 p_jurisdiction_code in varchar2 default null,
500 p_balance_date in date default null,
501 p_element_entry_id in number default null,
502 p_element_type_id in number default null,
503 p_original_entry_id in number default null,
504 p_tax_group in number default null,
505 p_pgm_id in number default null,
506 p_pl_id in number default null,
507 p_pl_typ_id in number default null,
508 p_opt_id in number default null,
509 p_ler_id in number default null,
510 p_communication_type_id in number default null,
511 p_action_type_id in number default null,
512 p_acty_base_rt_id in number default null,
513 p_elig_per_elctbl_chc_id in number default null,
514 p_enrt_bnft_id in number default null,
515 p_regn_id in number default null,
516 p_rptg_grp_id in number default null,
517 p_cm_dlvry_mthd_cd in varchar2 default null,
518 p_crt_ordr_typ_cd in varchar2 default null,
519 p_enrt_ctfn_typ_cd in varchar2 default null,
520 p_bnfts_bal_id in number default null,
521 p_elig_per_id in number default null,
522 p_per_cm_id in number default null,
523 p_prtt_enrt_actn_id in number default null,
524 p_effective_date in date,
525 p_param1 in varchar2 default null,
526 p_param1_value in varchar2 default null,
527 p_param2 in varchar2 default null,
528 p_param2_value in varchar2 default null,
529 p_param3 in varchar2 default null,
530 p_param3_value in varchar2 default null,
531 p_param4 in varchar2 default null,
532 p_param4_value in varchar2 default null,
533 p_param5 in varchar2 default null,
534 p_param5_value in varchar2 default null,
535 p_param6 in varchar2 default null,
536 p_param6_value in varchar2 default null,
537 p_param7 in varchar2 default null,
538 p_param7_value in varchar2 default null,
539 p_param8 in varchar2 default null,
540 p_param8_value in varchar2 default null,
541 p_param9 in varchar2 default null,
542 p_param9_value in varchar2 default null,
543 p_param10 in varchar2 default null,
544 p_param10_value in varchar2 default null,
545 p_param11 in varchar2 default null,
546 p_param11_value in varchar2 default null,
547 p_param12 in varchar2 default null,
548 p_param12_value in varchar2 default null,
549 p_param13 in varchar2 default null,
550 p_param13_value in varchar2 default null,
551 p_param14 in varchar2 default null,
552 p_param14_value in varchar2 default null,
553 p_param15 in varchar2 default null,
554 p_param15_value in varchar2 default null,
555 p_param16 in varchar2 default null,
556 p_param16_value in varchar2 default null,
557 p_param17 in varchar2 default null,
558 p_param17_value in varchar2 default null,
559 p_param18 in varchar2 default null,
560 p_param18_value in varchar2 default null,
561 p_param19 in varchar2 default null,
562 p_param19_value in varchar2 default null,
563 p_param20 in varchar2 default null,
564 p_param20_value in varchar2 default null,
565 p_param21 in varchar2 default null,
566 p_param21_value in varchar2 default null,
567 p_param22 in varchar2 default null,
568 p_param22_value in varchar2 default null,
569 p_param23 in varchar2 default null,
570 p_param23_value in varchar2 default null,
571 p_param24 in varchar2 default null,
572 p_param24_value in varchar2 default null,
573 p_param25 in varchar2 default null,
574 p_param25_value in varchar2 default null,
575 p_param26 in varchar2 default null,
576 p_param26_value in varchar2 default null,
577 p_param27 in varchar2 default null,
578 p_param27_value in varchar2 default null,
579 p_param28 in varchar2 default null,
580 p_param28_value in varchar2 default null,
581 p_param29 in varchar2 default null,
582 p_param29_value in varchar2 default null,
583 p_param30 in varchar2 default null,
584 p_param30_value in varchar2 default null,
585 p_param31 in varchar2 default null,
586 p_param31_value in varchar2 default null,
587 p_param32 in varchar2 default null,
588 p_param32_value in varchar2 default null,
589 p_param33 in varchar2 default null,
590 p_param33_value in varchar2 default null,
591 p_param34 in varchar2 default null,
592 p_param34_value in varchar2 default null,
593 p_param35 in varchar2 default null,
594 p_param35_value in varchar2 default null,
595 p_param_tab in ff_exec.outputs_t default g_empty_tab
596 ) return ff_exec.outputs_t;
597 --
598 procedure clear_down_cache;
599 --
600 -- This procedure is used to execute the rule : per_info_chg_cs_ler_rl
601 -- This procedure is called from the trigger packages like
602 -- ben_add_ler.
603 --
604 procedure exec_rule(
605 p_formula_id in number,
606 p_effective_date in date,
607 p_lf_evt_ocrd_dt in date,
608 p_business_group_id in number,
609 p_person_id in number default null,
610 p_new_value in varchar2 default null,
611 p_old_value in varchar2 default null,
612 p_column_name in varchar2 default null,
613 p_pk_id in varchar2 default null,
614 p_param5 in varchar2 default null,
615 p_param5_value in varchar2 default null,
616 p_param6 in varchar2 default null,
617 p_param6_value in varchar2 default null,
618 p_param7 in varchar2 default null,
619 p_param7_value in varchar2 default null,
620 p_param8 in varchar2 default null,
621 p_param8_value in varchar2 default null,
622 p_param9 in varchar2 default null,
623 p_param9_value in varchar2 default null,
624 p_param10 in varchar2 default null,
625 p_param10_value in varchar2 default null,
626 p_param11 in varchar2 default null,
627 p_param11_value in varchar2 default null,
628 p_param12 in varchar2 default null,
629 p_param12_value in varchar2 default null,
630 p_param13 in varchar2 default null,
631 p_param13_value in varchar2 default null,
632 p_param14 in varchar2 default null,
633 p_param14_value in varchar2 default null,
634 p_param15 in varchar2 default null,
635 p_param15_value in varchar2 default null,
636 p_param16 in varchar2 default null,
637 p_param16_value in varchar2 default null,
638 p_param17 in varchar2 default null,
639 p_param17_value in varchar2 default null,
640 p_param18 in varchar2 default null,
641 p_param18_value in varchar2 default null,
642 p_param19 in varchar2 default null,
643 p_param19_value in varchar2 default null,
644 p_param20 in varchar2 default null,
645 p_param20_value in varchar2 default null,
646 p_param21 in varchar2 default null,
647 p_param21_value in varchar2 default null,
648 p_param22 in varchar2 default null,
649 p_param22_value in varchar2 default null,
650 p_param23 in varchar2 default null,
651 p_param23_value in varchar2 default null,
652 p_param24 in varchar2 default null,
653 p_param24_value in varchar2 default null,
654 p_param25 in varchar2 default null,
655 p_param25_value in varchar2 default null,
656 p_param26 in varchar2 default null,
657 p_param26_value in varchar2 default null,
658 p_param27 in varchar2 default null,
659 p_param27_value in varchar2 default null,
660 p_param28 in varchar2 default null,
661 p_param28_value in varchar2 default null,
662 p_param29 in varchar2 default null,
663 p_param29_value in varchar2 default null,
664 p_param30 in varchar2 default null,
665 p_param30_value in varchar2 default null,
666 p_param31 in varchar2 default null,
667 p_param31_value in varchar2 default null,
668 p_param32 in varchar2 default null,
669 p_param32_value in varchar2 default null,
670 p_param33 in varchar2 default null,
671 p_param33_value in varchar2 default null,
672 p_param34 in varchar2 default null,
673 p_param34_value in varchar2 default null,
674 p_param35 in varchar2 default null,
675 p_param35_value in varchar2 default null,
676 p_param_tab in ff_exec.outputs_t default g_empty_tab,
677 p_ret_val out nocopy varchar2);
678 --
679 function get_rt_val(p_per_in_ler_id in number,
680 p_prtt_rt_val_id in number,
681 p_effective_date in date)
682 return number;
683 --
684 --
685 function get_ann_rt_val(p_per_in_ler_id in number,
686 p_prtt_rt_val_id in number,
687 p_effective_date in date)
688 return number;
689 --
690 --
691 function get_val(p_per_in_ler_id in number,
692 p_prtt_rt_val_id in number,
693 p_effective_date in date)
694 return number;
695
696 --
697 --
698 function get_concat_val(p_per_in_ler_id in number,
699 p_prtt_rt_val_id in number)
700 return varchar2;
701
702 function get_choice_status(p_elig_per_elctbl_chc_id in number)
703 return varchar2;
704 --
705 function in_workflow(p_person_id in number)
706 return varchar2;
707 --
708 ------------------------------------------------------------------------
709 -- CWB Changes
710 -- get_active_life_event
711 -- returns compensation type active life event
712 ------------------------------------------------------------------------
713 procedure get_active_life_event(p_person_id in number,
714 p_business_group_id in number,
715 p_effective_date in date,
716 p_lf_evt_ocrd_dt in date,
717 p_ler_id in number,
718 p_rec out nocopy g_active_life_event);
719 --
720 -- Bug No 2258174
721 --
722 function basis_to_plan_conversion(p_pl_id in number,
723 p_effective_date in date,
724 p_amount in number,
725 p_assignment_id in number
726 ) return number;
727 --
728 function plan_to_basis_conversion(p_pl_id in number,
729 p_effective_date in date,
730 p_amount in number,
731 p_assignment_id in number
732 ) return number;
733 --
734 -- Bug 2016857
735 procedure set_data_migrator_mode;
736 -- Bug 2016857
737
738 -- Bug 2428672
739 function ben_get_abp_plan_opt_names
740 (p_bnft_prvdr_pool_id IN ben_bnft_prvdr_pool_f.bnft_prvdr_pool_id%TYPE,
741 p_business_group_id IN ben_acty_base_rt_f.business_group_id%TYPE,
742 p_acty_base_rt_id IN ben_acty_base_rt_f.acty_base_rt_id%TYPE,
743 p_session_id IN fnd_sessions.session_id%TYPE,
744 ret_flag IN varchar2)
745 return varchar2;
746 --
747 -- ----------------------------------------------------------------------------
748 -- |---------------------< return_concat_kf_segments >------------------------|
749 -- ----------------------------------------------------------------------------
750 -- {Start Of Comments}
751 --
752 -- Description:
753 -- Returns the display concatenated string for the segments1..30.
754 -- The function calls hr_api.return_concat_kf_segments to get the
755 -- concatenated segments.
756 -- This function has been added to benutils as part of fix for bug 2599034
757 -- Since there is a package HR_API present in PLD library and backend, it is
758 -- conflicting with each other when we try to use the backend package from
759 -- form. But hard-coding Apps.<package name> is not a good practice.
760 -- Hence creating a wrapper for the hr_api.return_concat_kf_segments in
761 -- benutils to accomplish the same.
762 --
763 -- Pre-conditions:
764 -- The id_flex_num and segments have been fully validated.
765 --
766 -- In Arguments:
767 -- p_rec
768 --
769 -- Post Success:
770 --
771 -- Post Failure:
772 --
773 -- Access Status:
774 -- Internal Development Use Only.
775 --
776 -- {End Of Comments}
777 -- ----------------------------------------------------------------------------
778 function return_concat_kf_segments
779 (p_id_flex_num in number,
780 p_application_id in number,
781 p_id_flex_code in varchar2,
782 p_segment1 in varchar2 default null,
783 p_segment2 in varchar2 default null,
784 p_segment3 in varchar2 default null,
785 p_segment4 in varchar2 default null,
786 p_segment5 in varchar2 default null,
787 p_segment6 in varchar2 default null,
788 p_segment7 in varchar2 default null,
789 p_segment8 in varchar2 default null,
790 p_segment9 in varchar2 default null,
791 p_segment10 in varchar2 default null,
792 p_segment11 in varchar2 default null,
793 p_segment12 in varchar2 default null,
794 p_segment13 in varchar2 default null,
795 p_segment14 in varchar2 default null,
796 p_segment15 in varchar2 default null,
797 p_segment16 in varchar2 default null,
798 p_segment17 in varchar2 default null,
799 p_segment18 in varchar2 default null,
800 p_segment19 in varchar2 default null,
801 p_segment20 in varchar2 default null,
802 p_segment21 in varchar2 default null,
803 p_segment22 in varchar2 default null,
804 p_segment23 in varchar2 default null,
805 p_segment24 in varchar2 default null,
806 p_segment25 in varchar2 default null,
807 p_segment26 in varchar2 default null,
808 p_segment27 in varchar2 default null,
809 p_segment28 in varchar2 default null,
810 p_segment29 in varchar2 default null,
811 p_segment30 in varchar2 default null)
812 return varchar2;
813
814 --
815 -- ----------------------------------------------------------------------------
816 -- |---------------------< get_comp_obj_disp_dt >------------------------|
817 -- ----------------------------------------------------------------------------
818 -- {Start Of Comments}
819 --
820 -- Description:
821 -- Function to return effective_date based on which the compensation object names
822 -- can be retrieved. The function takes the profile value for BEN_DSPL_NAME_BASIS
823 -- and based on the profile set, return the correct date
824 --
825 -- Profile Value Return
826 -- SESSION Will return the session date. All comp objects names
827 -- displayed will be effective of session date
828 -- LEOD Will return the Life Event Occured Date. All comp objects names
829 -- displayed will be effective of the Life Event Occurred Date
830 -- MXLECVG Will return the greatest of Life Event Occurred Date or the Coverage
831 -- Start Date. All comp objects names displayed will be effective this date
832 --
833 --
834 -- Pre-conditions:
835 --
836 -- In Arguments:
837 --
838 -- Post Success:
839 --
840 -- Post Failure:
841 --
842 -- Access Status:
843 -- Internal Development Use Only.
844 --
845 -- {End Of Comments}
846 -----------------------------------------------------------------------------
847 --
848 FUNCTION get_comp_obj_disp_dt
849 (p_session_date date default null,
850 p_lf_evt_ocrd_dt date default null,
851 p_cvg_strt_dt date default null)
852 return date;
853 --
854 -- Over Loaded Function
855 --
856 FUNCTION get_comp_obj_disp_dt
857 (p_session_date date default null,
858 p_per_in_ler_id number,
859 p_cvg_strt_dt date default null)
860 return date;
861 --
862 --
863 -- g_ben_dspl_name_basis will store the value of profile BEN_DSPL_NAME_BASIS
864 --
865 g_ben_dspl_name_basis varchar2(100);
866 --
867 --
868 -- ----------------------------------------------------------------------------
869 -- |---------------------< get_post_enrt_cvg_and_rt_val >---------------------|
870 -- ----------------------------------------------------------------------------
871 -- {Start Of Comments}
872 --
873 -- Description:
874 -- Procedure to retrieve the Coverage amount and Rate Amount values for
875 -- those coverage and rates whose Calculation method is 'Post-Enrollment
876 -- Calculation Rule'
877 --
878 -- Pre-conditions: Specifically written for self-service and should be used
879 -- only after Election Information and Post-Process is called.
880 --
881 -- In Arguments: choice id, bnft id, and rt ids.
882 --
883 -- Post Success: returns all relevant amounts.
884 --
885 -- Post Failure: returns null
886 --
890 -- {End Of Comments}
887 -- Access Status:
888 -- Internal Development Use Only.
889 --
891 -----------------------------------------------------------------------------
892 --
893 procedure get_post_enrt_cvg_and_rt_val
894 (p_elig_per_elctbl_chc_id in number,
895 p_enrt_bnft_id in number default null,
896 p_effective_date in date,
897 p_enrt_rt_id in number default null,
898 p_enrt_rt_id2 in number default null,
899 p_enrt_rt_id3 in number default null,
900 p_enrt_rt_id4 in number default null,
901 p_bnft_amt out nocopy number,
902 p_val out nocopy number,
903 p_rt_val out nocopy number,
904 p_ann_rt_val out nocopy number,
905 p_val2 out nocopy number,
906 p_rt_val2 out nocopy number,
907 p_ann_rt_val2 out nocopy number,
908 p_val3 out nocopy number,
909 p_rt_val3 out nocopy number,
910 p_ann_rt_val3 out nocopy number,
911 p_val4 out nocopy number,
912 p_rt_val4 out nocopy number,
913 p_ann_rt_val4 out nocopy number) ;
914 --
915 --
916
917 function run_osb_benmngle_flag
918 ( p_person_id in number,
919 p_business_group_id in number,
920 p_effective_date in date)
921 return boolean;
922
923 function get_pl_annualization_factor(p_acty_ref_perd_cd in varchar2) return number;
924
925 --
926 FUNCTION is_task_enabled
927 (p_access_cd in varchar2,
928 p_population_cd in varchar2,
929 p_status_cd in varchar2,
930 p_dist_bdgt_iss_dt in date,
931 p_wksht_grp_cd in varchar2)
932 return varchar2;
933 --
934 FUNCTION get_manager_name(p_emp_per_in_ler_id in number,
935 p_level in number)
936 return varchar2;
937 --
938 FUNCTION get_profile(p_profile_name in varchar2)
939 return varchar2;
940
941 -- Function get_dpnt_prev_cvrd_flag will find out if a dependent is covered in a
942 -- previous lifevent. Currently this is used by SSBEN in the DependentsPeopleVO query.
943 --
944 FUNCTION get_dpnt_prev_cvrd_flag(p_prtt_enrt_rslt_id in number,
945 p_efective_date date,
946 p_dpnt_person_id number,
947 p_elig_per_elctbl_chc_id number,
948 p_elig_cvrd_dpnt_id number,
949 p_elig_dpnt_id number,
950 p_per_in_ler_id number )
951 return varchar2;
952 --
953 end benutils;