[Home] [Help]
PACKAGE BODY: APPS.BENUTILS
Source
1 package body benutils as
2 /* $Header: benutils.pkb 120.20 2008/01/24 07:00:12 sallumwa ship $ */
3 --
4 g_package varchar2(80) := 'benutils';
5
6 type t_pkey_table is table of varchar2(30) index by binary_integer;
7 --
8 g_part_of_pkey t_pkey_table;
9 g_primary_key_count number(9);
10 g_batch_elig_table_count number := 0;
11 g_batch_ler_table_count number := 0;
12 g_batch_action_table_count number := 0;
13 g_batch_elctbl_table_count number := 0;
14 g_batch_rate_table_count number := 0;
15 g_batch_dpnt_table_count number := 0;
16 g_batch_commu_table_count number := 0;
17 g_report_table_count number := 0;
18 g_batch_elig_table_object g_batch_elig_table := g_batch_elig_table();
19 g_batch_ler_table_object g_batch_ler_table := g_batch_ler_table();
20 g_batch_elctbl_table_object g_batch_elctbl_table := g_batch_elctbl_table();
21 g_batch_rate_table_object g_batch_rate_table := g_batch_rate_table();
22 g_batch_dpnt_table_object g_batch_dpnt_table := g_batch_dpnt_table();
23 --
24 ----------------------------------------------------------------------------
25 -- rt_typ_calc
26 ----------------------------------------------------------------------------
27 PROCEDURE rt_typ_calc
28 (p_val IN number,
29 p_val_2 IN number,
30 p_rt_typ_cd IN varchar2,
31 p_calculated_val OUT NOCOPY number) is
32 --
33 l_package varchar2(80) := g_package||'.rt_typ_calc';
34 --
35 BEGIN
36 --
37 -- hr_utility.set_location ('Entering '||l_package,10);
38 --
39 if p_val is null /*or p_val = 0 commented for 3497676*/then
40 --
41 -- hr_utility.set_location ('Leaving '||l_package,1);
42 return;
43 --
44 end if;
45 --
46 if p_val_2 is null /*or p_val_2 = 0 commented for 3497676*/then
47 --
48 -- hr_utility.set_location ('Leaving '||l_package,2);
49 return;
50 --
51 end if;
52 --
53 if p_rt_typ_cd = 'MLT' then
54 --
55 p_calculated_val := p_val * p_val_2;
56 --
57 elsif p_rt_typ_cd in ('PCT','PERHNDRD') then
58 --
59 p_calculated_val := (p_val/100) * p_val_2;
60 --
61 elsif p_rt_typ_cd = 'PERTEN' then
62 --
63 p_calculated_val := (p_val/10) * p_val_2;
64 --
65 elsif p_rt_typ_cd = 'PERTHSND' then
66 --
67 p_calculated_val := (p_val/1000) * p_val_2;
68 --
69 elsif p_rt_typ_cd = 'PERTTHSND' then
70 --
71 p_calculated_val := (p_val/10000) * p_val_2;
72 --
73 else
74 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
75 fnd_message.set_token('PROC',l_package);
76 fnd_message.set_token('CODE1',p_rt_typ_cd);
77 fnd_message.raise_error;
78 end if;
79 --
80 -- hr_utility.set_location ('Leaving '||l_package,10);
81 --
82 END rt_typ_calc;
83 --
84 ------------------------------------------------------------------------
85 -- limit_checks
86 ------------------------------------------------------------------------
87 --
88 PROCEDURE limit_checks (p_lwr_lmt_val in number,
89 p_lwr_lmt_calc_rl in number,
90 p_upr_lmt_val in number,
91 p_upr_lmt_calc_rl in number,
92 p_effective_date in date,
93 p_assignment_id in number,
94 p_organization_id in number,
95 p_business_group_id in number,
96 p_pgm_id in number,
97 p_pl_id in number,
98 p_pl_typ_id in number,
99 p_opt_id in number,
100 p_ler_id in number,
101 p_acty_base_rt_id in number ,
102 p_elig_per_elctbl_chc_id in number ,
103 p_val in out nocopy number,
104 p_state in varchar2) is
105 --
106 l_lwr_outputs ff_exec.outputs_t;
107 l_upr_outputs ff_exec.outputs_t;
108 l_package varchar2(80) := g_package||'.limit_checks';
109 l_jurisdiction PAY_CA_EMP_PROV_TAX_INFO_F.JURISDICTION_CODE%type := null;
110 --
111 BEGIN
112 --
113 hr_utility.set_location('Entering '||l_package,20);
114 --
115 hr_utility.set_location('Floor/Ceiling Rule Checking'||l_package,30);
116 --
117 -- Bug 1949361 : jurisdiction code is fetched inside formula function
118 -- call.
119 --
120 /*
121 if p_state is not null then
122 l_jurisdiction := pay_mag_utils.lookup_jurisdiction_code
123 (p_state => p_state);
124 end if;
125 */
126 --
127 if p_lwr_lmt_calc_rl is not NULL then
128 --
129 l_lwr_outputs := benutils.formula
130 (p_formula_id => p_lwr_lmt_calc_rl,
131 p_effective_date => p_effective_date,
132 p_assignment_id => p_assignment_id,
133 p_organization_id => p_organization_id,
134 p_business_group_id => p_business_group_id,
135 p_pgm_id => p_pgm_id,
136 p_pl_id => p_pl_id,
137 p_pl_typ_id => p_pl_typ_id,
138 p_opt_id => p_opt_id,
139 p_ler_id => p_ler_id,
140 p_acty_base_rt_id => p_acty_base_rt_id,
141 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
142 -- FONM
143 p_param1 => 'BEN_IV_RT_STRT_DT',
144 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
145 p_param2 => 'BEN_IV_CVG_STRT_DT',
146 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt),
147 p_jurisdiction_code => l_jurisdiction);
148 --
149 end if;
150 --
151 if p_upr_lmt_calc_rl is not NULL then
152 --
153 l_upr_outputs := benutils.formula
154 (p_formula_id => p_upr_lmt_calc_rl,
155 p_effective_date => p_effective_date,
156 p_assignment_id => p_assignment_id,
157 p_organization_id => p_organization_id,
158 p_business_group_id => p_business_group_id,
159 p_pgm_id => p_pgm_id,
160 p_pl_id => p_pl_id,
161 p_pl_typ_id => p_pl_typ_id,
162 p_opt_id => p_opt_id,
163 p_ler_id => p_ler_id,
164 p_acty_base_rt_id => p_acty_base_rt_id,
165 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
166 -- FONM
167 p_param1 => 'BEN_IV_RT_STRT_DT',
168 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
169 p_param2 => 'BEN_IV_CVG_STRT_DT',
170 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt),
171 p_jurisdiction_code => l_jurisdiction);
172 --
173 end if;
174 --
175 hr_utility.set_location('Floor/Ceiling Val Checking'||l_package,40);
176 --
177 if p_val > nvl(p_upr_lmt_val,p_val+1) then
178 --
179 p_val := p_upr_lmt_val;
180 --
181 elsif (p_upr_lmt_calc_rl is not NULL) and p_val >
182 nvl(l_upr_outputs(l_upr_outputs.first).value,p_val+1) then
183 --
184 p_val := l_upr_outputs(l_upr_outputs.first).value;
185 --
186 elsif p_val < nvl(p_lwr_lmt_val,p_val-1) then
187 --
188 p_val := p_lwr_lmt_val;
189 --
190 elsif (p_lwr_lmt_calc_rl is not NULL) and p_val <
191 nvl(l_lwr_outputs(l_lwr_outputs.first).value,p_val-1) then
192 --
193 p_val := l_lwr_outputs(l_lwr_outputs.first).value;
194 --
195 end if;
196 --
197 hr_utility.set_location('Leaving '||l_package,20);
198 --
199 END limit_checks;
200
201 ------------------------------------------------------------------------
202 -- init_lookups
203 ------------------------------------------------------------------------
204 procedure init_lookups(p_lookup_type_1 in varchar2 ,
205 p_lookup_type_2 in varchar2 ,
206 p_lookup_type_3 in varchar2 ,
207 p_lookup_type_4 in varchar2 ,
208 p_lookup_type_5 in varchar2 ,
209 p_lookup_type_6 in varchar2 ,
210 p_lookup_type_7 in varchar2 ,
211 p_lookup_type_8 in varchar2 ,
212 p_lookup_type_9 in varchar2 ,
213 p_lookup_type_10 in varchar2 ,
214 p_effective_date in date) is
215 --
216 l_proc varchar2(80) := 'benutils.init_lookups';
217 l_count number := 0;
218 --
219 cursor c_lookups is
220 select lookup_type,
221 lookup_code
222 from hr_lookups
223 where lookup_type in (nvl(p_lookup_type_1,'DUMMY_VALUE'),
224 nvl(p_lookup_type_2,'DUMMY_VALUE'),
225 nvl(p_lookup_type_3,'DUMMY_VALUE'),
226 nvl(p_lookup_type_4,'DUMMY_VALUE'),
227 nvl(p_lookup_type_5,'DUMMY_VALUE'),
228 nvl(p_lookup_type_6,'DUMMY_VALUE'),
229 nvl(p_lookup_type_7,'DUMMY_VALUE'),
230 nvl(p_lookup_type_8,'DUMMY_VALUE'),
231 nvl(p_lookup_type_9,'DUMMY_VALUE'),
232 nvl(p_lookup_type_10,'DUMMY_VALUE'))
233 and enabled_flag = 'Y'
234 and p_effective_date
235 between nvl(start_date_active,p_effective_date)
236 and nvl(end_date_active, p_effective_date);
237 --
238 l_lookups c_lookups%rowtype;
239 --
240 begin
241 --
242 hr_utility.set_location('Entering:'||l_proc, 5);
243 --
244 -- First clear old cache
245 --
246 g_cache_lookup_object.delete;
247 --
248 -- Now load cache structure
249 --
250 open c_lookups;
251 --
252 hr_utility.set_location('open c_lookups: '||l_proc, 10);
253 --
254 loop
255 --
256 fetch c_lookups into l_lookups;
257 exit when c_lookups%notfound;
258 --
259 -- Load cache structure
260 --
261 l_count := l_count + 1;
262 g_cache_lookup_object(l_count).lookup_type := l_lookups.lookup_type;
263 g_cache_lookup_object(l_count).lookup_code := l_lookups.lookup_code;
264 --
265 end loop;
266 --
267 close c_lookups;
268 --
269 hr_utility.set_location('Leaving:'||l_proc, 5);
270 --
271 end init_lookups;
272
273 ------------------------------------------------------------------------
274 -- get_lf_evt_ocrd_dt
275 ------------------------------------------------------------------------
276 function get_lf_evt_ocrd_dt(p_person_id in number,
277 p_business_group_id in number,
278 p_ler_id in number ,
279 p_effective_date in date) return date is
280 --
281 l_proc varchar2(80) := 'benutils.get_lf_evt_ocrd_dt';
282 l_lf_evt_ocrd_dt date;
283 --
284 cursor c_lf_evt_ocrd_dt is
285 select pil.lf_evt_ocrd_dt
286 from ben_per_in_ler pil
287 where pil.person_id = p_person_id
288 and pil.business_group_id + 0 = p_business_group_id
289 and pil.ler_id = nvl(p_ler_id,pil.ler_id)
290 and pil.per_in_ler_stat_cd = 'STRTD';
291 --
292 begin
293 --
294 hr_utility.set_location('Entering:'||l_proc, 5);
295 --
296 open c_lf_evt_ocrd_dt;
297 --
298 fetch c_lf_evt_ocrd_dt into l_lf_evt_ocrd_dt;
299 --
300 close c_lf_evt_ocrd_dt;
301 --
302 hr_utility.set_location('Leaving:'||l_proc, 5);
303 --
304 return l_lf_evt_ocrd_dt;
305 --
306 end get_lf_evt_ocrd_dt;
307 --
308 ------------------------------------------------------------------------
309 -- get_per_in_ler_id
310 -- returns active non-unrestricted life event
311 -- function is called only in benauten.pkb and which is applicable only
312 -- 'L' or 'C' modes of benmnlge
313 ------------------------------------------------------------------------
314 function get_per_in_ler_id(p_person_id in number,
315 p_business_group_id in number,
316 p_ler_id in number ,
317 p_effective_date in date) return number is
318 --
319 l_proc varchar2(80) := 'benutils.get_per_in_ler_id';
320 l_per_in_ler_id number;
321 --
322 cursor c_per_in_ler_id is
323 select pil.per_in_ler_id
324 from ben_per_in_ler pil,
325 ben_ler_f ler
326 where pil.person_id = p_person_id
327 and pil.ler_id = nvl(p_ler_id,pil.ler_id)
328 and pil.ler_id = ler.ler_id
329 and pil.per_in_ler_stat_cd = 'STRTD'
330 and ler.typ_cd <> 'SCHEDDU'
331 and p_effective_date between
332 ler.effective_start_date and ler.effective_end_date;
333 --
334 begin
335 --
336 hr_utility.set_location('Entering:'||l_proc, 5);
337 --
338 open c_per_in_ler_id;
339 --
340 fetch c_per_in_ler_id into l_per_in_ler_id;
341 --
342 close c_per_in_ler_id;
343 --
344 hr_utility.set_location('Leaving:'||l_proc, 5);
345 --
346 return l_per_in_ler_id;
347 --
348 end get_per_in_ler_id;
349
350 ------------------------------------------------------------------------
351 -- CWB Changes
352 -- get_active_life_event
353 -- returns compensation type active life event
354 ------------------------------------------------------------------------
355 procedure get_active_life_event(p_person_id in number,
356 p_business_group_id in number,
357 p_effective_date in date,
358 p_lf_evt_ocrd_dt in date,
359 p_ler_id in number,
360 p_rec out nocopy g_active_life_event) is
361 --
362 l_proc varchar2(80) := 'benutils.get_active_life_event';
363 --
364 cursor c_active_life_event is
365 select pil.per_in_ler_id,
366 pil.lf_evt_ocrd_dt,
367 pil.ntfn_dt,
368 pil.ler_id,
369 ler.name,
370 ler.typ_cd,
371 ler.ovridg_le_flag,
372 ler.ptnl_ler_trtmt_cd,
373 pil.object_version_number,
374 pil.ptnl_ler_for_per_id,
375 ler.qualg_evt_flag
376 from ben_per_in_ler pil,
377 ben_ler_f ler
378 where pil.person_id = p_person_id
379 and ler.ler_id = pil.ler_id
380 and ler.ler_id = p_ler_id
381 and p_effective_date
382 between ler.effective_start_date
383 and ler.effective_end_date
384 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
385 and pil.per_in_ler_stat_cd = 'STRTD'
386 and ler.typ_cd = 'COMP';
387 --
388 begin
389 --
390 hr_utility.set_location('Entering:'||l_proc, 5);
391 --
392 open c_active_life_event;
393 --
394 fetch c_active_life_event into p_rec;
395 --
396 close c_active_life_event;
397 --
398 hr_utility.set_location('Leaving:'||l_proc, 5);
399 --
400 end get_active_life_event;
401 -----------------------------------------------------------------------
402 -- get_per_in_ler_id
403 -- returns active unrestricted life event
404 ------------------------------------------------------------------------
405 function get_per_in_ler_id(p_person_id in number,
406 p_business_group_id in number,
407 p_ler_id in number ,
408 p_lf_event_mode in varchar2 ,
409 p_effective_date in date) return number is
410 --
411 l_proc varchar2(80) := 'benutils.get_per_in_ler_id_u';
412 l_per_in_ler_id number;
413 --
414 cursor c_per_in_ler_id is
415 select pil.per_in_ler_id
416 from ben_per_in_ler pil,
417 ben_ler_f ler
418 where pil.person_id = p_person_id
419 and pil.business_group_id = p_business_group_id
420 and pil.ler_id = nvl(p_ler_id,pil.ler_id)
421 and pil.ler_id = ler.ler_id
422 and pil.per_in_ler_stat_cd = 'STRTD'
423 and ler.typ_cd = 'SCHEDDU'
424 and p_effective_date between
425 ler.effective_start_date and ler.effective_end_date;
426 --
427 begin
428 --
429 hr_utility.set_location('Entering:'||l_proc, 5);
430 --
431 open c_per_in_ler_id;
432 --
433 fetch c_per_in_ler_id into l_per_in_ler_id;
434 --
435 close c_per_in_ler_id;
436 --
437 hr_utility.set_location('Leaving:'||l_proc, 5);
438 return l_per_in_ler_id;
439 --
440 end get_per_in_ler_id;
441 ------------------------------------------------------------------------
442 -- get_active_life_event
443 -- returns non-unrestricted active life event
444 ------------------------------------------------------------------------
445 procedure get_active_life_event(p_person_id in number,
446 p_business_group_id in number,
447 p_effective_date in date,
448 p_rec out nocopy g_active_life_event) is
449 --
450 l_proc varchar2(80) := 'benutils.get_active_life_event';
451 --
452 cursor c_active_life_event is
453 select pil.per_in_ler_id,
454 pil.lf_evt_ocrd_dt,
455 pil.ntfn_dt,
456 pil.ler_id,
457 ler.name,
458 ler.typ_cd,
459 ler.ovridg_le_flag,
460 ler.ptnl_ler_trtmt_cd,
461 pil.object_version_number,
462 pil.ptnl_ler_for_per_id,
463 ler.qualg_evt_flag
464 from ben_per_in_ler pil,
465 ben_ler_f ler
466 where pil.person_id = p_person_id
467 and ler.ler_id = pil.ler_id
468 and p_effective_date
469 between ler.effective_start_date
470 and ler.effective_end_date
471 and pil.per_in_ler_stat_cd = 'STRTD'
472 --
473 -- CWB Changes GRADE - added 2 more values.
474 -- iRec Added mode iRecruitment (I)
475 and ler.typ_cd not in ('SCHEDDU', 'COMP', 'GSP', 'ABS', 'IREC');
476 --
477 begin
478 --
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 open c_active_life_event;
482 --
483 fetch c_active_life_event into p_rec;
484 --
485 close c_active_life_event;
486 --
487 hr_utility.set_location('Leaving:'||l_proc, 5);
488 --
489 end get_active_life_event;
490
491 ------------------------------------------------------------------------
492 -- get_active_life_event - overloaded with life_event_mode parameter
493 -- returns active unrestricted life event
494 ------------------------------------------------------------------------
495 procedure get_active_life_event(p_person_id in number,
496 p_business_group_id in number,
497 p_effective_date in date,
498 p_lf_event_mode in varchar2 ,
499 p_rec out nocopy g_active_life_event) is
500 --
501 l_proc varchar2(80) := 'benutils.get_active_life_event';
502 --
503 cursor c_active_life_event is
504 select pil.per_in_ler_id,
505 pil.lf_evt_ocrd_dt,
506 pil.ntfn_dt,
507 pil.ler_id,
508 ler.name,
509 ler.typ_cd,
510 ler.ovridg_le_flag,
511 ler.ptnl_ler_trtmt_cd,
512 pil.object_version_number,
513 pil.ptnl_ler_for_per_id,
514 ler.qualg_evt_flag
515 from ben_per_in_ler pil,
516 ben_ler_f ler
517 where pil.person_id = p_person_id
518 and pil.business_group_id = p_business_group_id
519 and ler.ler_id = pil.ler_id
520 and ler.business_group_id = pil.business_group_id
521 and p_effective_date
522 between ler.effective_start_date
523 and ler.effective_end_date
524 and pil.per_in_ler_stat_cd = 'STRTD'
525 -- GSP : make use of the same function for GSP
526 and ((p_lf_event_mode in ('U','D') and ler.typ_cd = 'SCHEDDU') or -- ICM Change
527 (p_lf_event_mode = 'M' and ler.typ_cd = 'ABS') or
528 (p_lf_event_mode = 'G' and ler.typ_cd = 'GSP') or
529 (p_lf_event_mode = 'I' and ler.typ_cd = 'IREC'
530 and pil.assignment_id = ben_manage_life_events.g_irec_ass_rec.assignment_id) ); -- iRec
531 --
532 begin
533 --
534 hr_utility.set_location('Entering:'||l_proc, 5);
535 --
536 open c_active_life_event;
537 --
538 fetch c_active_life_event into p_rec;
539 --
540 close c_active_life_event;
541 --
542 hr_utility.set_location('Leaving:'||l_proc, 5);
543 --
544 end get_active_life_event;
545
546
547 ------------------------------------------------------------------------
548 -- get_ler
549 ------------------------------------------------------------------------
550 procedure get_ler(p_business_group_id in number,
551 p_ler_id in number,
552 p_effective_date in date,
553 p_rec out nocopy g_ler) is
554 --
555 l_proc varchar2(80) := 'benutils.get_ler';
556 --
557 cursor c_ler is
558 select ler.ler_id,
559 ler.ler_eval_rl,
560 ler.name
561 from ben_ler_f ler
562 where ler.business_group_id = p_business_group_id
563 and ler.ler_id = p_ler_id
564 and p_effective_date
565 between ler.effective_start_date
566 and ler.effective_end_date;
567 --
568 begin
569 --
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 open c_ler;
573 --
574 fetch c_ler into p_rec;
575 --
576 close c_ler;
577 --
578 hr_utility.set_location('Leaving:'||l_proc, 5);
579 --
580 end get_ler;
581 --
582 procedure get_ler(p_business_group_id in number,
583 p_typ_cd in varchar2,
584 p_effective_date in date,
585 p_lf_evt_oper_cd in varchar2 default null, /* GSP Rate Sync */
586 p_rec out nocopy g_ler) is
587 --
588 l_proc varchar2(80) := 'benutils.get_ler';
589 --
590 cursor c_ler is
591 select ler.ler_id,
592 ler.ler_eval_rl,
593 ler.name
594 from ben_ler_f ler
595 where ler.business_group_id = p_business_group_id
596 and ler.typ_cd = p_typ_cd
597 and p_effective_date
598 between ler.effective_start_date
599 and ler.effective_end_date
600 and ( p_typ_cd <> 'GSP' /* GSP Rate Sync */
601 or ( p_typ_cd = 'GSP' and nvl(ler.lf_evt_oper_cd, 'PROG') = nvl(p_lf_evt_oper_cd, 'PROG') ) /* GSP Rate Sync */
602 );
603
604 --
605 begin
606 --
607 hr_utility.set_location('Entering:'||l_proc, 5);
608 hr_utility.set_location('ACE p_typ_cd : ' || p_typ_cd, 5);
609 hr_utility.set_location('ACE p_lf_evt_oper_cd : ' || p_lf_evt_oper_cd, 5);
610 --
611 open c_ler;
612 --
613 fetch c_ler into p_rec;
614 --
615 close c_ler;
616 --
617 hr_utility.set_location('ACE p_rec.name : ' || p_rec.name, 5);
618 hr_utility.set_location('Leaving:'||l_proc, 5);
619 --
620 end get_ler;
621
622 ------------------------------------------------------------------------
623 -- get_ptnl_ler
624 ------------------------------------------------------------------------
625 procedure get_ptnl_ler(p_business_group_id in number,
626 p_person_id in number,
627 p_ler_id in number,
628 p_effective_date in date,
629 p_rec out nocopy g_ptnl_ler) is
630 --
631 l_proc varchar2(80) := 'benutils.get_ptnl_ler';
632 --
633 cursor c_ptnl is
634 select ptnl_ler_for_per_id,
635 object_version_number
636 from ben_ptnl_ler_for_per ptn
637 where ptn.business_group_id = p_business_group_id
638 and ptn.person_id = p_person_id
639 and ptn.ler_id = p_ler_id
640 and ptn.lf_evt_ocrd_dt = p_effective_date;
641 --
642 begin
643 --
644 hr_utility.set_location('Leaving:'||l_proc, 5);
645 --
646 open c_ptnl;
647 --
648 fetch c_ptnl into p_rec;
649 --
650 close c_ptnl;
651 --
652 hr_utility.set_location('Leaving:'||l_proc, 5);
653 --
654 end get_ptnl_ler;
655
656 ------------------------------------------------------------------------
657 -- get_assignment_id
658 ------------------------------------------------------------------------
659 function get_assignment_id(p_person_id in number,
660 p_business_group_id in number,
661 p_effective_date in date) return number is
662 --
663 l_proc varchar2(80) := 'benutils.get_assignment_id';
664 l_assignment_id number;
665 --
666 cursor c_assignment is
667 select paf.assignment_id
668 from per_all_assignments_f paf, per_assignment_status_types pat
669 where paf.primary_flag = 'Y'
670 and paf.assignment_type <> 'C'
671 and paf.business_group_id = p_business_group_id
672 and paf.person_id = p_person_id
673 and paf.assignment_status_type_id = pat.assignment_status_type_id(+)
674 and pat.per_system_status(+) = 'ACTIVE_ASSIGN'
675 and p_effective_date between paf.effective_start_date and paf.effective_end_date
676 and hr_security.show_record ('PER_ALL_ASSIGNMENTS_F',
677 paf.assignment_id,
678 paf.person_id,
679 paf.assignment_type
680 )
681 = 'TRUE'
682 order by assignment_type desc, effective_start_date desc;
683 --
684 cursor c_all_assignment is
685 select paf.assignment_id
686 from per_all_assignments_f paf, per_assignment_status_types pat
687 where paf.primary_flag = 'Y'
688 and paf.assignment_type <> 'C'
689 and paf.business_group_id = p_business_group_id
690 and paf.person_id = p_person_id
691 and paf.assignment_status_type_id = pat.assignment_status_type_id(+)
692 and pat.per_system_status(+) = 'ACTIVE_ASSIGN'
693 and p_effective_date between paf.effective_start_date and paf.effective_end_date
694 order by assignment_type desc, effective_start_date desc;
695 --
696 begin
697 --
698 hr_utility.set_location('Entering:'||l_proc, 5);
699 -- Perf changes
700 if hr_security.view_all = 'Y' and hr_general.get_xbg_profile = 'Y'
701 then
702 open c_all_assignment;
703 --
704 fetch c_all_assignment into l_assignment_id;
705 --
706 close c_all_assignment;
707 else
708 open c_assignment;
709 --
710 fetch c_assignment into l_assignment_id;
711 --
712 close c_assignment;
713 end if;
714 --
715 hr_utility.set_location('Leaving:'||l_proc, 5);
716 --
717 return l_assignment_id;
718 --
719 end get_assignment_id;
720
721 ------------------------------------------------------------------------
722 -- not_exists_in_hr_lookups
723 ------------------------------------------------------------------------
724 function not_exists_in_hr_lookups(p_lookup_type in varchar2,
725 p_lookup_code in varchar2) return boolean is
726 --
727 l_proc varchar2(80) := 'benutils.not_exists_in_hr_lookups';
728 --
729 begin
730 --
731 hr_utility.set_location('Entering:'||l_proc, 5);
732 --
733 -- Check if lookup type and lookup code can be found in cache structure
734 --
735 for l_count in g_cache_lookup_object.first..g_cache_lookup_object.last loop
736 --
737 if g_cache_lookup_object(l_count).lookup_type = p_lookup_type and
738 g_cache_lookup_object(l_count).lookup_code = p_lookup_code then
739 --
740 hr_utility.set_location('Leaving:'||l_proc, 3);
741 return false;
742 --
743 end if;
744 --
745 end loop;
746 --
747 hr_utility.set_location('Leaving:'||l_proc, 5);
748 return true;
749 --
750 end not_exists_in_hr_lookups;
751
752 ------------------------------------------------------------------------
753 -- formula_exists
754 ------------------------------------------------------------------------
755 function formula_exists(p_formula_id in number,
756 p_formula_type_id in number,
757 p_business_group_id in number,
758 p_effective_date in date) return boolean is
759 --
760 l_proc varchar2(80) := 'benutils.formula_exists';
761 l_dummy varchar2(1);
762 --
763 cursor c1 is
764 select null
765 from ff_formulas_f ff,
766 per_business_groups pbg
767 where ff.formula_id = p_formula_id
768 and ff.formula_type_id = p_formula_type_id
769 and pbg.business_group_id = p_business_group_id
770 and nvl(ff.business_group_id,p_business_group_id) =
771 p_business_group_id
772 and nvl(ff.legislation_code,pbg.legislation_code) =
773 pbg.legislation_code
774 and p_effective_date
775 between ff.effective_start_date
776 and ff.effective_end_date;
777 --
778 begin
779 --
780 hr_utility.set_location('Entering:'||l_proc, 5);
781 --
782 open c1;
783 --
784 fetch c1 into l_dummy;
785 if c1%notfound then
786 --
787 close c1;
788 hr_utility.set_location('Leaving:'||l_proc, 3);
789 return false;
790 --
791 end if;
792 --
793 close c1;
794 --
795 hr_utility.set_location('Leaving:'||l_proc, 5);
796 return true;
797 --
798 end formula_exists;
799
800 ------------------------------------------------------------------------
801 -- get_ler_name
802 ------------------------------------------------------------------------
803 function get_ler_name(p_typ_cd in varchar2,
804 p_business_group_id in number) return varchar2 is
805 --
806 cursor c1 is
807 select ler.name
808 from ben_ler_f ler
809 where ler.business_group_id = p_business_group_id
810 and ler.typ_cd = p_typ_cd
811 and sysdate
812 between ler.effective_start_date
813 and ler.effective_end_date;
814 --
815 l_name ben_ler_f.name%type; -- UTF8 Change Bug 2254683
816 begin
817 --
818 open c1;
819 --
820 fetch c1 into l_name;
821 --
822 close c1;
823 --
824 return l_name;
825 --
826 end get_ler_name;
827
828 ------------------------------------------------------------------------
829 -- set_cache_record_position
830 ------------------------------------------------------------------------
831 procedure set_cache_record_position is
832 --
833 l_proc varchar2(80) := 'benutils.set_cache_record_position';
834 --
835 begin
836 --
837 hr_utility.set_location('Entering:'||l_proc, 5);
838
839 -- Set '_count' parms to indicate the last global record written
840 -- to each global table for a particular person.
841
842 g_report_table_count := nvl(g_report_table_object.count,0);
843 g_batch_elig_table_count := nvl(g_batch_elig_table_object.count,0);
844 g_batch_rate_table_count := nvl(g_batch_rate_table_object.count,0);
845 g_batch_dpnt_table_count := nvl(g_batch_dpnt_table_object.count,0);
846 g_batch_ler_table_count := nvl(g_batch_ler_table_object.count,0);
847 g_batch_action_table_count := nvl(g_batch_action_table_object.count,0);
848 g_batch_elctbl_table_count := nvl(g_batch_elctbl_table_object.count,0);
849 g_batch_commu_table_count := nvl(g_batch_commu_table_object.count,0);
850
851 ben_warnings.g_oab_warnings_count := nvl(ben_warnings.g_oab_warnings.count, 0);
852 --
853 hr_utility.set_location('Leaving:'||l_proc, 5);
854 --
855 end set_cache_record_position;
856
857 ------------------------------------------------------------------------
858 -- rollback_cache
859 ------------------------------------------------------------------------
860 procedure rollback_cache is
861 --
862 l_proc varchar2(80) := 'benutils.rollback_cache';
863 --
864 begin
865 hr_utility.set_location('Entering:'||l_proc, 5);
866
867 -- The '_count' parms are set in set_cache_record_position above each
868 -- time we finish with a person. It indicates the last record written
869 -- for a person.
870 -- The '.count' function returns the last record written. We esentially
871 -- are rolling back the global records to the last record written for the
872 -- last person that didn't error.
873 --
874 if g_batch_elig_table_count > 0 then
875 --
876 g_batch_elig_table_object.trim(g_batch_elig_table_object.count-
877 g_batch_elig_table_count+1);
878 --
879 end if;
880 --
881 if g_batch_ler_table_count > 0 then
882 --
883 g_batch_ler_table_object.trim(g_batch_ler_table_object.count-
884 g_batch_ler_table_count);
885 --
886 end if;
887 --
888 if g_batch_action_table_count > 0 then
889 --
890 g_batch_action_table_object.trim(g_batch_action_table_object.count-
891 g_batch_action_table_count);
892 --
893 end if;
894 --
895 if g_batch_elctbl_table_count > 0 then
896 --
897 g_batch_elctbl_table_object.trim(g_batch_elctbl_table_object.count-
898 g_batch_elctbl_table_count);
899 --
900 end if;
901 --
902 if g_batch_rate_table_count > 0 then
903 --
904 g_batch_rate_table_object.trim(g_batch_rate_table_object.count-
905 g_batch_rate_table_count);
906 --
907 end if;
908 --
909 if g_batch_dpnt_table_count > 0 then
910 --
911 g_batch_dpnt_table_object.trim(g_batch_dpnt_table_object.count-
912 g_batch_dpnt_table_count);
913 --
914 end if;
915 --
916 if g_batch_commu_table_count > 0 then
917 --
918 g_batch_commu_table_object.trim(g_batch_commu_table_object.count-
919 g_batch_commu_table_count);
920 --
921 end if;
922
923 if ben_warnings.g_oab_warnings_count > 0 then
924 ben_warnings.trim_warnings
925 (ben_warnings.g_oab_warnings.count-
926 ben_warnings.g_oab_warnings_count);
927 end if;
928
929 hr_utility.set_location('Leaving:'||l_proc, 5);
930 end rollback_cache;
931 --
932 procedure clear_down_cache is
933 --
934 begin
935 --
936 g_report_table_object.delete;
937 g_batch_elig_table_object.delete;
938 g_batch_ler_table_object.delete;
939 g_batch_proc_table_object.delete;
940 g_batch_action_table_object.delete;
941 g_batch_elctbl_table_object.delete;
942 g_batch_rate_table_object.delete;
943 g_batch_dpnt_table_object.delete;
944 g_batch_commu_table_object.delete;
945 --
946 end clear_down_cache;
947 ------------------------------------------------------------------------
948 -- write_table_and_file
949 ------------------------------------------------------------------------
950 procedure write_table_and_file(p_table in boolean ,
951 p_file in boolean ) is
952 --
953 l_proc varchar2(80) := 'benutils.write_table_and_file';
954 l_num1_col g_number_table := g_number_table();
955 l_num2_col g_number_table := g_number_table();
956 l_num3_col g_number_table := g_number_table();
957 l_num4_col g_number_table := g_number_table();
958 l_num5_col g_number_table := g_number_table();
959 l_num6_col g_number_table := g_number_table();
960 l_num7_col g_number_table := g_number_table();
961 l_num8_col g_number_table := g_number_table();
962 l_num9_col g_number_table := g_number_table();
963 l_num10_col g_number_table := g_number_table();
964 l_num11_col g_number_table := g_number_table();
965 l_num12_col g_number_table := g_number_table();
966 l_num13_col g_number_table := g_number_table();
967 l_num14_col g_number_table := g_number_table();
968 l_num15_col g_number_table := g_number_table();
969 l_num16_col g_number_table := g_number_table();
970 l_num17_col g_number_table := g_number_table();
971 l_num18_col g_number_table := g_number_table();
972 l_var1_col g_varchar2_table := g_varchar2_table();
973 l_var2_col g_varchar2_table := g_varchar2_table();
974 l_var3_col g_varchar2_table := g_varchar2_table();
975 l_var4_col g_varchar2_table := g_varchar2_table();
976 l_var5_col g_varchar2_table := g_varchar2_table();
977 l_var6_col g_varchar2_table := g_varchar2_table();
978 l_var7_col g_varchar2_table := g_varchar2_table();
979 l_var8_col g_varchar2_table := g_varchar2_table();
980 l_var9_col g_varchar2_table := g_varchar2_table();
981 l_var10_col g_varchar2_table := g_varchar2_table();
982 l_var11_col g_varchar2_table := g_varchar2_table();
983 l_var12_col g_varchar2_table := g_varchar2_table();
984 l_dat1_col g_date_table := g_date_table();
985 l_dat2_col g_date_table := g_date_table();
986 l_dat3_col g_date_table := g_date_table();
987 l_dat4_col g_date_table := g_date_table();
988 l_dat5_col g_date_table := g_date_table();
989 l_num_recs number;
990 l_table_name varchar2(30);
991 --
992 --
993 table_full EXCEPTION;
994 index_full EXCEPTION;
995 --
996 pragma exception_init(table_full,-1653);
997 pragma exception_init(index_full,-1654);
998 begin
999 --
1000 hr_utility.set_location('Entering:'||l_proc, 5);
1001 --
1002 -- Loop through cache routine and write to ben_reporting table and
1003 -- to the output file
1004 --
1005 if p_table = false and
1006 p_file = false then
1007 --
1008 hr_utility.set_location('Leaving:'||l_proc, 2);
1009 return;
1010 --
1011 end if;
1012
1013 ben_warnings.write_warnings_batch ;
1014
1015 if nvl(g_report_table_object.count,0) > 0 then
1016 --
1017 l_num_recs := g_report_table_object.count;
1018 --
1019 for l_count in 1..l_num_recs loop
1020 --
1021 if fnd_global.conc_request_id <> -1 and p_file then
1022 --
1023 fnd_file.put_line
1024 (which => fnd_file.log,
1025 buff => g_report_table_object(l_count).text);
1026 --
1027 end if;
1028 --
1029 if p_table then
1030 --
1031 -- Copy all varray to single column varrays.
1032 --
1033 l_num1_col.extend(1);
1034 --
1035 select ben_reporting_s.nextval into
1036 l_num1_col(l_count)
1037 from sys.dual;
1038 /*
1039 l_num1_col(l_count) :=
1040 g_report_table_object(l_count).reporting_id;
1041 */
1042 l_num2_col.extend(1);
1043 l_num2_col(l_count) :=
1044 g_report_table_object(l_count).benefit_action_id;
1045 l_num3_col.extend(1);
1046 l_num3_col(l_count) :=
1047 g_report_table_object(l_count).thread_id;
1048 l_num4_col.extend(1);
1049 l_num4_col(l_count) :=
1050 g_report_table_object(l_count).sequence;
1051 l_var1_col.extend(1);
1052 l_var1_col(l_count) :=
1053 g_report_table_object(l_count).text;
1054 l_num5_col.extend(1);
1055 l_num5_col(l_count) :=
1056 g_report_table_object(l_count).object_version_number;
1057 l_var2_col.extend(1);
1058 l_var2_col(l_count) :=
1059 g_report_table_object(l_count).rep_typ_cd;
1060 l_var3_col.extend(1);
1061 l_var3_col(l_count) :=
1062 g_report_table_object(l_count).error_message_code;
1063 l_var4_col.extend(1);
1064 l_var4_col(l_count) :=
1065 g_report_table_object(l_count).national_identifier;
1066 l_num6_col.extend(1);
1067 l_num6_col(l_count) :=
1068 g_report_table_object(l_count).related_person_ler_id;
1069 l_num7_col.extend(1);
1070 l_num7_col(l_count) :=
1071 g_report_table_object(l_count).temporal_ler_id;
1072 l_num8_col.extend(1);
1073 l_num8_col(l_count) :=
1074 g_report_table_object(l_count).ler_id;
1075 l_num9_col.extend(1);
1076 l_num9_col(l_count) :=
1077 g_report_table_object(l_count).person_id;
1078 l_num10_col.extend(1);
1079 l_num10_col(l_count) :=
1080 g_report_table_object(l_count).pgm_id;
1081 l_num11_col.extend(1);
1082 l_num11_col(l_count) :=
1083 g_report_table_object(l_count).pl_id;
1084 l_num12_col.extend(1);
1085 l_num12_col(l_count) :=
1086 g_report_table_object(l_count).related_person_id;
1087 l_num13_col.extend(1);
1088 l_num13_col(l_count) :=
1089 g_report_table_object(l_count).oipl_id;
1090 l_num14_col.extend(1);
1091 l_num14_col(l_count) :=
1092 g_report_table_object(l_count).pl_typ_id;
1093 l_num15_col.extend(1);
1094 l_num15_col(l_count) :=
1095 g_report_table_object(l_count).actl_prem_id;
1096 l_num16_col.extend(1);
1097 l_num16_col(l_count) :=
1098 g_report_table_object(l_count).val;
1099 l_num17_col.extend(1);
1100 l_num17_col(l_count) :=
1101 g_report_table_object(l_count).mo_num;
1102 l_num18_col.extend(1);
1103 l_num18_col(l_count) :=
1104 g_report_table_object(l_count).yr_num;
1105 --
1106 end if;
1107 --
1108 end loop;
1109 --
1110 if p_table then
1111 --
1112 hr_utility.set_location('BEN_REP Ins: '||l_proc, 6);
1113 l_table_name := 'BEN_REPORTING';
1114 forall l_count in 1..l_num_recs
1115 insert into ben_reporting
1116 (reporting_id,
1117 benefit_action_id,
1118 thread_id,
1119 sequence,
1120 text,
1121 object_version_number,
1122 rep_typ_cd,
1123 error_message_code,
1124 national_identifier,
1125 related_person_ler_id,
1126 temporal_ler_id,
1127 ler_id,
1128 person_id,
1129 pgm_id,
1130 pl_id,
1131 related_person_id,
1132 oipl_id,
1133 pl_typ_id,
1134 actl_prem_id,
1135 val,
1136 mo_num,
1137 yr_num)
1138 values
1139 (l_num1_col(l_count),
1140 l_num2_col(l_count),
1141 l_num3_col(l_count),
1142 l_num4_col(l_count),
1143 l_var1_col(l_count),
1144 l_num5_col(l_count),
1145 l_var2_col(l_count),
1146 l_var3_col(l_count),
1147 l_var4_col(l_count),
1148 l_num6_col(l_count),
1149 l_num7_col(l_count),
1150 l_num8_col(l_count),
1151 l_num9_col(l_count),
1152 l_num10_col(l_count),
1153 l_num11_col(l_count),
1154 l_num12_col(l_count),
1155 l_num13_col(l_count),
1156 l_num14_col(l_count),
1157 l_num15_col(l_count),
1158 l_num16_col(l_count),
1159 l_num17_col(l_count),
1160 l_num18_col(l_count));
1161 hr_utility.set_location('Dn BEN_REP Ins: '||l_proc, 7);
1162 --
1163 end if;
1164 --
1165 g_report_table_object.delete;
1166 --
1167 end if;
1168 --
1169 --
1170 hr_utility.set_location(l_proc||' Elig: ', 10);
1171 if nvl(g_batch_elig_table_object.count,0) > 0 then
1172 --
1173 if p_table then
1174 --
1175 -- Clear any existing host varrays
1176 --
1177 l_num1_col.delete;
1178 l_num2_col.delete;
1179 l_num3_col.delete;
1180 l_num4_col.delete;
1181 l_num5_col.delete;
1182 l_num6_col.delete;
1183 l_num7_col.delete;
1184 l_num8_col.delete;
1185 l_var1_col.delete;
1186 l_var2_col.delete;
1187 l_num_recs := g_batch_elig_table_object.count;
1188 --
1189 for l_count in 1..l_num_recs loop
1190 --
1191 -- Copy varrays to singular varrays
1192 --
1193 l_num1_col.extend(1);
1194 l_num1_col(l_count) :=
1195 g_batch_elig_table_object(l_count).batch_elig_id;
1196 l_num2_col.extend(1);
1197 l_num2_col(l_count) :=
1198 g_batch_elig_table_object(l_count).benefit_action_id;
1199 l_num3_col.extend(1);
1200 l_num3_col(l_count) :=
1201 g_batch_elig_table_object(l_count).person_id;
1202 l_num4_col.extend(1);
1203 l_num4_col(l_count) :=
1204 g_batch_elig_table_object(l_count).pgm_id;
1205 l_num5_col.extend(1);
1206 l_num5_col(l_count) :=
1207 g_batch_elig_table_object(l_count).pl_id;
1208 l_num6_col.extend(1);
1209 l_num6_col(l_count) :=
1210 g_batch_elig_table_object(l_count).oipl_id;
1211 l_var1_col.extend(1);
1212 l_var1_col(l_count) :=
1213 g_batch_elig_table_object(l_count).elig_flag;
1214 l_var2_col.extend(1);
1215 l_var2_col(l_count) :=
1216 g_batch_elig_table_object(l_count).inelig_text;
1217 l_num7_col.extend(1);
1218 l_num7_col(l_count) :=
1219 g_batch_elig_table_object(l_count).business_group_id;
1220 l_num8_col.extend(1);
1221 l_num8_col(l_count) :=
1222 g_batch_elig_table_object(l_count).object_version_number;
1223 --
1224 end loop;
1225 --
1226 -- Bind and populate table
1227 --
1228 l_table_name :='BEN_BATCH_ELIG_INFO';
1229 forall l_count in 1..l_num_recs
1230 --
1231 insert into ben_batch_elig_info
1232 (batch_elig_id,
1233 benefit_action_id,
1234 person_id,
1235 pgm_id,
1236 pl_id,
1237 oipl_id,
1238 elig_flag,
1239 inelig_text,
1240 business_group_id,
1241 object_version_number)
1242 values
1243 (l_num1_col(l_count),
1244 l_num2_col(l_count),
1245 l_num3_col(l_count),
1246 l_num4_col(l_count),
1247 l_num5_col(l_count),
1248 l_num6_col(l_count),
1249 l_var1_col(l_count),
1250 l_var2_col(l_count),
1251 l_num7_col(l_count),
1252 l_num8_col(l_count));
1253 --
1254 end if;
1255 --
1256 g_batch_elig_table_object.delete;
1257 --
1258 end if;
1259 --
1260 hr_utility.set_location(l_proc||' Ler: ', 20);
1261 if nvl(g_batch_ler_table_object.count,0) > 0 then
1262 --
1263 if p_table then
1264 --
1265 l_num1_col.delete;
1266 l_num2_col.delete;
1267 l_num3_col.delete;
1268 l_num4_col.delete;
1269 l_num5_col.delete;
1270 l_num6_col.delete;
1271 l_num7_col.delete;
1272 l_var1_col.delete;
1273 l_var2_col.delete;
1274 l_var3_col.delete;
1275 l_var4_col.delete;
1276 l_var5_col.delete;
1277 l_var6_col.delete;
1278 l_var7_col.delete;
1279 l_var8_col.delete;
1280 l_var9_col.delete;
1281 l_var10_col.delete;
1282 l_var11_col.delete;
1283 l_var12_col.delete;
1284 l_dat1_col.delete;
1285 l_num_recs := g_batch_ler_table_object.count;
1286 --
1287 for l_count in 1..l_num_recs loop
1288 --
1289 l_num1_col.extend(1);
1290 l_num1_col(l_count) :=
1291 g_batch_ler_table_object(l_count).batch_ler_id;
1292 l_num2_col.extend(1);
1293 l_num2_col(l_count) :=
1294 g_batch_ler_table_object(l_count).benefit_action_id;
1295 l_num3_col.extend(1);
1296 l_num3_col(l_count) :=
1297 g_batch_ler_table_object(l_count).person_id;
1298 l_num4_col.extend(1);
1299 l_num4_col(l_count) :=
1300 g_batch_ler_table_object(l_count).ler_id;
1301 l_dat1_col.extend(1);
1302 l_dat1_col(l_count) :=
1303 g_batch_ler_table_object(l_count).lf_evt_ocrd_dt;
1304 l_var1_col.extend(1);
1305 l_var1_col(l_count) :=
1306 g_batch_ler_table_object(l_count).replcd_flag;
1307 l_var2_col.extend(1);
1308 l_var2_col(l_count) :=
1309 g_batch_ler_table_object(l_count).crtd_flag;
1310 l_var3_col.extend(1);
1311 l_var3_col(l_count) :=
1312 g_batch_ler_table_object(l_count).tmprl_flag;
1313 l_var4_col.extend(1);
1314 l_var4_col(l_count) :=
1315 g_batch_ler_table_object(l_count).dltd_flag;
1316 l_var5_col.extend(1);
1317 l_var5_col(l_count) :=
1318 g_batch_ler_table_object(l_count).open_and_clsd_flag;
1319 l_var6_col.extend(1);
1320 l_var6_col(l_count) :=
1321 g_batch_ler_table_object(l_count).not_crtd_flag;
1322 l_var7_col.extend(1);
1323 l_var7_col(l_count) :=
1324 g_batch_ler_table_object(l_count).stl_actv_flag;
1325 l_var8_col.extend(1);
1326 l_var8_col(l_count) :=
1327 g_batch_ler_table_object(l_count).clsd_flag;
1328 l_var9_col.extend(1);
1329 l_var9_col(l_count) :=
1330 g_batch_ler_table_object(l_count).clpsd_flag;
1331 l_var10_col.extend(1);
1332 l_var10_col(l_count) :=
1333 g_batch_ler_table_object(l_count).clsn_flag;
1334 l_var11_col.extend(1);
1335 l_var11_col(l_count) :=
1336 g_batch_ler_table_object(l_count).no_effect_flag;
1337 l_var12_col.extend(1);
1338 l_var12_col(l_count) :=
1339 g_batch_ler_table_object(l_count).cvrge_rt_prem_flag;
1340 l_num5_col.extend(1);
1341 l_num5_col(l_count) :=
1342 g_batch_ler_table_object(l_count).per_in_ler_id;
1343 l_num6_col.extend(1);
1344 l_num6_col(l_count) :=
1345 g_batch_ler_table_object(l_count).business_group_id;
1346 l_num7_col.extend(1);
1347 l_num7_col(l_count) :=
1348 g_batch_ler_table_object(l_count).object_version_number;
1349 --
1350 end loop;
1351 --
1352 -- Bulk bind and insert
1353 --
1354 l_table_name :='BEN_BATCH_LER_INFO';
1355 forall l_count in 1..l_num_recs
1356 --
1357 insert into ben_batch_ler_info
1358 (batch_ler_id,
1359 benefit_action_id,
1360 person_id,
1361 ler_id,
1362 lf_evt_ocrd_dt,
1363 replcd_flag,
1364 crtd_flag,
1365 tmprl_flag,
1366 dltd_flag,
1367 open_and_clsd_flag,
1368 not_crtd_flag,
1369 stl_actv_flag,
1370 clsd_flag,
1371 clpsd_flag,
1372 clsn_flag,
1373 no_effect_flag,
1374 cvrge_rt_prem_flag,
1375 per_in_ler_id,
1376 business_group_id,
1377 object_version_number)
1378 values
1379 (l_num1_col(l_count),
1380 l_num2_col(l_count),
1381 l_num3_col(l_count),
1382 l_num4_col(l_count),
1383 l_dat1_col(l_count),
1384 l_var1_col(l_count),
1385 l_var2_col(l_count),
1386 l_var3_col(l_count),
1387 l_var4_col(l_count),
1388 l_var5_col(l_count),
1389 l_var6_col(l_count),
1390 l_var7_col(l_count),
1391 l_var8_col(l_count),
1392 l_var9_col(l_count),
1393 l_var10_col(l_count),
1394 l_var11_col(l_count),
1395 l_var12_col(l_count),
1396 l_num5_col(l_count),
1397 l_num6_col(l_count),
1398 l_num7_col(l_count));
1399 --
1400 end if;
1401 --
1402 g_batch_ler_table_object.delete;
1403 --
1404 end if;
1405 --
1406 hr_utility.set_location(l_proc||' Action: ', 30);
1407 if nvl(g_batch_action_table_object.count,0) > 0 then
1408 --
1409 if p_table then
1410 --
1411 l_num1_col.delete;
1412 l_var1_col.delete;
1413 l_num2_col.delete;
1414 l_num_recs := g_batch_action_table_object.count;
1415 --
1416 for l_count in 1..l_num_recs loop
1417 --
1418 l_num1_col.extend(1);
1419 l_num1_col(l_count) :=
1420 g_batch_action_table_object(l_count).person_action_id;
1421 l_var1_col.extend(1);
1422 l_var1_col(l_count) :=
1423 g_batch_action_table_object(l_count).action_status_cd;
1424 l_num2_col.extend(1);
1425 l_num2_col(l_count) :=
1426 g_batch_action_table_object(l_count).object_version_number+1;
1427 --
1428 end loop;
1429 --
1430 forall l_count in 1..l_num_recs
1431 --
1432 update ben_person_actions
1433 set action_status_cd = l_var1_col(l_count),
1434 object_version_number = l_num2_col(l_count)
1435 where person_action_id = l_num1_col(l_count);
1436 --
1437 end if;
1438 --
1439 g_batch_action_table_object.delete;
1440 --
1441 end if;
1442 --
1443 hr_utility.set_location(l_proc||' elctbl: ', 40);
1444 if nvl(g_batch_elctbl_table_object.count,0) > 0 then
1445 --
1446 if p_table then
1447 --
1448 l_num1_col.delete;
1449 l_num2_col.delete;
1450 l_num3_col.delete;
1451 l_num4_col.delete;
1452 l_num5_col.delete;
1453 l_num6_col.delete;
1454 l_num7_col.delete;
1455 l_num8_col.delete;
1456 l_var1_col.delete;
1457 l_var2_col.delete;
1458 l_var3_col.delete;
1459 l_var4_col.delete;
1460 l_dat1_col.delete;
1461 l_dat2_col.delete;
1462 l_dat3_col.delete;
1463 l_dat4_col.delete;
1464 l_dat5_col.delete;
1465 l_num_recs := g_batch_elctbl_table_object.count;
1466 --
1467 for l_count in 1..l_num_recs loop
1468 --
1469 l_num1_col.extend(1);
1470 l_num1_col(l_count) :=
1471 g_batch_elctbl_table_object(l_count).batch_elctbl_id;
1472 l_num2_col.extend(1);
1473 l_num2_col(l_count) :=
1474 g_batch_elctbl_table_object(l_count).benefit_action_id;
1475 l_num3_col.extend(1);
1476 l_num3_col(l_count) :=
1477 g_batch_elctbl_table_object(l_count).person_id;
1478 l_num4_col.extend(1);
1479 l_num4_col(l_count) :=
1480 g_batch_elctbl_table_object(l_count).pgm_id;
1481 l_num5_col.extend(1);
1482 l_num5_col(l_count) :=
1483 g_batch_elctbl_table_object(l_count).pl_id;
1484 l_num6_col.extend(1);
1485 l_num6_col(l_count) :=
1486 g_batch_elctbl_table_object(l_count).oipl_id;
1487 l_dat1_col.extend(1);
1488 l_dat1_col(l_count) :=
1489 g_batch_elctbl_table_object(l_count).enrt_cvg_strt_dt;
1490 l_dat2_col.extend(1);
1491 l_dat2_col(l_count) :=
1492 g_batch_elctbl_table_object(l_count).enrt_perd_strt_dt;
1493 l_dat3_col.extend(1);
1494 l_dat3_col(l_count) :=
1495 g_batch_elctbl_table_object(l_count).enrt_perd_end_dt;
1496 l_dat4_col.extend(1);
1497 l_dat4_col(l_count) :=
1498 g_batch_elctbl_table_object(l_count).erlst_deenrt_dt;
1499 l_dat5_col.extend(1);
1500 l_dat5_col(l_count) :=
1501 g_batch_elctbl_table_object(l_count).dflt_enrt_dt;
1502 l_var1_col.extend(1);
1503 l_var1_col(l_count) :=
1504 g_batch_elctbl_table_object(l_count).enrt_typ_cycl_cd;
1505 l_var2_col.extend(1);
1506 l_var2_col(l_count) :=
1507 g_batch_elctbl_table_object(l_count).comp_lvl_cd;
1508 l_var3_col.extend(1);
1509 l_var3_col(l_count) :=
1510 g_batch_elctbl_table_object(l_count).mndtry_flag;
1511 l_var4_col.extend(1);
1512 l_var4_col(l_count) :=
1513 g_batch_elctbl_table_object(l_count).dflt_flag;
1514 l_num7_col.extend(1);
1515 l_num7_col(l_count) :=
1516 g_batch_elctbl_table_object(l_count).business_group_id;
1517 l_num8_col.extend(1);
1518 l_num8_col(l_count) :=
1519 g_batch_elctbl_table_object(l_count).object_version_number;
1520 --
1521 end loop;
1522 --
1523 l_table_name := 'BEN_BATCH_ELCTBL_CHC_INFO';
1524 forall l_count in 1..l_num_recs
1525 --
1526 insert into ben_batch_elctbl_chc_info
1527 (batch_elctbl_id,
1528 benefit_action_id,
1529 person_id,
1530 pgm_id,
1531 pl_id,
1532 oipl_id,
1533 enrt_cvg_strt_dt,
1534 enrt_perd_strt_dt,
1535 enrt_perd_end_dt,
1536 erlst_deenrt_dt,
1537 dflt_enrt_dt,
1538 enrt_typ_cycl_cd,
1539 comp_lvl_cd,
1540 mndtry_flag,
1541 dflt_flag,
1542 business_group_id,
1543 object_version_number)
1544 values
1545 (l_num1_col(l_count),
1546 l_num2_col(l_count),
1547 l_num3_col(l_count),
1548 l_num4_col(l_count),
1549 l_num5_col(l_count),
1550 l_num6_col(l_count),
1551 l_dat1_col(l_count),
1552 l_dat2_col(l_count),
1553 l_dat3_col(l_count),
1554 l_dat4_col(l_count),
1555 l_dat5_col(l_count),
1556 l_var1_col(l_count),
1557 l_var2_col(l_count),
1558 l_var3_col(l_count),
1559 l_var4_col(l_count),
1560 l_num7_col(l_count),
1561 l_num8_col(l_count));
1562 --
1563 end if;
1564 --
1565 g_batch_elctbl_table_object.delete;
1566 --
1567 end if;
1568 --
1569 hr_utility.set_location(l_proc||' rate: ', 50);
1570 if nvl(g_batch_rate_table_object.count,0) > 0 then
1571 --
1572 if p_table then
1573 --
1574 l_num1_col.delete;
1575 l_num2_col.delete;
1576 l_num3_col.delete;
1577 l_num4_col.delete;
1578 l_num5_col.delete;
1579 l_num6_col.delete;
1580 l_num7_col.delete;
1581 l_num8_col.delete;
1582 l_num9_col.delete;
1583 l_num10_col.delete;
1584 l_num11_col.delete;
1585 l_num12_col.delete;
1586 l_num13_col.delete;
1587 l_num14_col.delete;
1588 l_var1_col.delete;
1589 l_var2_col.delete;
1590 l_var3_col.delete;
1591 l_var4_col.delete;
1592 l_var5_col.delete;
1593 l_dat1_col.delete;
1594 l_dat2_col.delete;
1595 l_dat3_col.delete;
1596 l_dat4_col.delete;
1597 l_num_recs := g_batch_rate_table_object.count;
1598 --
1599 hr_utility.set_location('batch rate'||l_num_recs,3333);
1600 for l_count in 1..l_num_recs loop
1601 --
1602 l_num1_col.extend(1);
1603 l_num1_col(l_count) :=
1604 g_batch_rate_table_object(l_count).batch_rt_id;
1605 l_num2_col.extend(1);
1606 l_num2_col(l_count) :=
1607 g_batch_rate_table_object(l_count).benefit_action_id;
1608 l_num3_col.extend(1);
1609 l_num3_col(l_count) :=
1610 g_batch_rate_table_object(l_count).person_id;
1611 l_num4_col.extend(1);
1612 l_num4_col(l_count) :=
1613 g_batch_rate_table_object(l_count).pgm_id;
1614 l_num5_col.extend(1);
1615 l_num5_col(l_count) :=
1616 g_batch_rate_table_object(l_count).pl_id;
1617 l_num6_col.extend(1);
1618 l_num6_col(l_count) :=
1619 g_batch_rate_table_object(l_count).oipl_id;
1620 l_var1_col.extend(1);
1621 l_var1_col(l_count) :=
1622 g_batch_rate_table_object(l_count).bnft_rt_typ_cd;
1623 l_var2_col.extend(1);
1624 l_var2_col(l_count) :=
1625 g_batch_rate_table_object(l_count).dflt_flag;
1626 l_num7_col.extend(1);
1627 l_num7_col(l_count) :=
1628 g_batch_rate_table_object(l_count).val;
1629 l_var3_col.extend(1);
1630 l_var3_col(l_count) :=
1631 g_batch_rate_table_object(l_count).tx_typ_cd;
1632 l_var4_col.extend(1);
1633 l_var4_col(l_count) :=
1634 g_batch_rate_table_object(l_count).acty_typ_cd;
1635 l_num8_col.extend(1);
1636 l_num8_col(l_count) :=
1637 g_batch_rate_table_object(l_count).mn_elcn_val;
1638 l_num9_col.extend(1);
1639 l_num9_col(l_count) :=
1640 g_batch_rate_table_object(l_count).mx_elcn_val;
1641 l_num10_col.extend(1);
1642 l_num10_col(l_count) :=
1643 g_batch_rate_table_object(l_count).incrmt_elcn_val;
1644 l_num11_col.extend(1);
1645 l_num11_col(l_count) :=
1646 g_batch_rate_table_object(l_count).dflt_val;
1647 l_dat1_col.extend(1);
1648 l_dat1_col(l_count) :=
1649 g_batch_rate_table_object(l_count).rt_strt_dt;
1650 l_dat2_col.extend(1);
1651 l_dat2_col(l_count) :=
1652 g_batch_rate_table_object(l_count).enrt_cvg_strt_dt;
1653 l_dat3_col.extend(1);
1654 l_dat3_col(l_count) :=
1655 g_batch_rate_table_object(l_count).enrt_cvg_thru_dt;
1656 l_var5_col.extend(1);
1657 l_var5_col(l_count) :=
1658 g_batch_rate_table_object(l_count).actn_cd;
1659 l_dat4_col.extend(1);
1660 l_dat4_col(l_count) :=
1661 g_batch_rate_table_object(l_count).close_actn_itm_dt;
1662 l_num12_col.extend(1);
1663 l_num12_col(l_count) :=
1664 g_batch_rate_table_object(l_count).business_group_id;
1665 l_num13_col.extend(1);
1666 l_num13_col(l_count) :=
1667 g_batch_rate_table_object(l_count).object_version_number;
1668 l_num14_col.extend(1);
1669 l_num14_col(l_count) :=
1670 g_batch_rate_table_object(l_count).old_val;
1671 --
1672 hr_utility.set_location(l_proc||' rate: ' ||
1673 g_batch_rate_table_object(l_count).old_val, 1111);
1674 end loop;
1675 --
1676 l_table_name :='BEN_BATCH_RATE_INFO';
1677 forall l_count in 1..l_num_recs
1678 --
1679 insert into ben_batch_rate_info
1680 (batch_rt_id,
1681 benefit_action_id,
1682 person_id,
1683 pgm_id,
1684 pl_id,
1685 oipl_id,
1686 bnft_rt_typ_cd,
1687 dflt_flag,
1688 val,
1689 tx_typ_cd,
1690 acty_typ_cd,
1691 mn_elcn_val,
1692 mx_elcn_val,
1693 incrmt_elcn_val,
1694 dflt_val,
1695 rt_strt_dt,
1696 enrt_cvg_strt_dt,
1697 enrt_cvg_thru_dt,
1698 actn_cd,
1699 close_actn_itm_dt,
1700 business_group_id,
1701 object_version_number,
1702 old_val)
1703 values
1704 (l_num1_col(l_count),
1705 l_num2_col(l_count),
1706 l_num3_col(l_count),
1707 l_num4_col(l_count),
1708 l_num5_col(l_count),
1709 l_num6_col(l_count),
1710 l_var1_col(l_count),
1711 l_var2_col(l_count),
1712 l_num7_col(l_count),
1713 l_var3_col(l_count),
1714 l_var4_col(l_count),
1715 l_num8_col(l_count),
1716 l_num9_col(l_count),
1717 l_num10_col(l_count),
1718 l_num11_col(l_count),
1719 l_dat1_col(l_count),
1720 l_dat2_col(l_count),
1721 l_dat3_col(l_count),
1722 l_var5_col(l_count),
1723 l_dat4_col(l_count),
1724 l_num12_col(l_count),
1725 l_num13_col(l_count),
1726 l_num14_col(l_count));
1727 --
1728 end if;
1729 --
1730 g_batch_rate_table_object.delete;
1731 --
1732 end if;
1733 --
1734 hr_utility.set_location(l_proc||' dpnt: ', 60);
1735 if nvl(g_batch_dpnt_table_object.count,0) > 0 then
1736 --
1737 if p_table then
1738 --
1739 l_num1_col.delete;
1740 l_num2_col.delete;
1741 l_num3_col.delete;
1742 l_num4_col.delete;
1743 l_num5_col.delete;
1744 l_num6_col.delete;
1745 l_num7_col.delete;
1746 l_num8_col.delete;
1747 l_num9_col.delete;
1748 l_var1_col.delete;
1749 l_var2_col.delete;
1750 l_dat1_col.delete;
1751 l_dat2_col.delete;
1752 --
1753 for l_count in g_batch_dpnt_table_object.first..
1754 g_batch_dpnt_table_object.last loop
1755 --
1756 l_num1_col.extend(1);
1757 l_num1_col(l_count) :=
1758 g_batch_dpnt_table_object(l_count).batch_dpnt_id;
1759 l_num2_col.extend(1);
1760 l_num2_col(l_count) :=
1761 g_batch_dpnt_table_object(l_count).benefit_action_id;
1762 l_num3_col.extend(1);
1763 l_num3_col(l_count) :=
1764 g_batch_dpnt_table_object(l_count).person_id;
1765 l_num4_col.extend(1);
1766 l_num4_col(l_count) :=
1767 g_batch_dpnt_table_object(l_count).pgm_id;
1768 l_num5_col.extend(1);
1769 l_num5_col(l_count) :=
1770 g_batch_dpnt_table_object(l_count).pl_id;
1771 l_num6_col.extend(1);
1772 l_num6_col(l_count) :=
1773 g_batch_dpnt_table_object(l_count).oipl_id;
1774 l_var1_col.extend(1);
1775 l_var1_col(l_count) :=
1776 g_batch_dpnt_table_object(l_count).contact_typ_cd;
1777 l_num7_col.extend(1);
1778 l_num7_col(l_count) :=
1779 g_batch_dpnt_table_object(l_count).dpnt_person_id;
1780 l_dat1_col.extend(1);
1781 l_dat1_col(l_count) :=
1782 g_batch_dpnt_table_object(l_count).enrt_cvg_strt_dt;
1783 l_dat2_col.extend(1);
1784 l_dat2_col(l_count) :=
1785 g_batch_dpnt_table_object(l_count).enrt_cvg_thru_dt;
1786 l_var2_col.extend(1);
1787 l_var2_col(l_count) :=
1788 g_batch_dpnt_table_object(l_count).actn_cd;
1789 l_num8_col.extend(1);
1790 l_num8_col(l_count) :=
1791 g_batch_dpnt_table_object(l_count).business_group_id;
1792 l_num9_col.extend(1);
1793 l_num9_col(l_count) :=
1794 g_batch_dpnt_table_object(l_count).object_version_number;
1795 --
1796 end loop;
1797 --
1798 l_table_name :='BEN_BATCH_DPNT_INFO';
1799 forall l_count in g_batch_dpnt_table_object.first..
1800 g_batch_dpnt_table_object.last
1801 --
1802 insert into ben_batch_dpnt_info
1803 (batch_dpnt_id,
1804 benefit_action_id,
1805 person_id,
1806 pgm_id,
1807 pl_id,
1808 oipl_id,
1809 contact_typ_cd,
1810 dpnt_person_id,
1811 enrt_cvg_strt_dt,
1812 enrt_cvg_thru_dt,
1813 actn_cd,
1814 business_group_id,
1815 object_version_number)
1816 values
1817 (l_num1_col(l_count),
1818 l_num2_col(l_count),
1819 l_num3_col(l_count),
1820 l_num4_col(l_count),
1821 l_num5_col(l_count),
1822 l_num6_col(l_count),
1823 l_var1_col(l_count),
1824 l_num7_col(l_count),
1825 l_dat1_col(l_count),
1826 l_dat2_col(l_count),
1827 l_var2_col(l_count),
1828 l_num8_col(l_count),
1829 l_num9_col(l_count));
1830 --
1831 end if;
1832 --
1833 g_batch_dpnt_table_object.delete;
1834 --
1835 end if;
1836 --
1837 hr_utility.set_location(l_proc||' commu: ', 70);
1838 if nvl(g_batch_commu_table_object.count,0) > 0 then
1839 --
1840 if p_table then
1841 --
1842 l_num1_col.delete;
1843 l_num2_col.delete;
1844 l_num3_col.delete;
1845 l_num4_col.delete;
1846 l_num5_col.delete;
1847 l_num6_col.delete;
1848 l_dat1_col.delete;
1849 l_num7_col.delete;
1850 l_num8_col.delete;
1851 --
1852 for l_count in g_batch_commu_table_object.first..
1853 g_batch_commu_table_object.last loop
1854 --
1855 l_num1_col.extend(1);
1856 l_num1_col(l_count) :=
1857 g_batch_commu_table_object(l_count).batch_commu_id;
1858 l_num2_col.extend(1);
1859 l_num2_col(l_count) :=
1860 g_batch_commu_table_object(l_count).benefit_action_id;
1861 l_num3_col.extend(1);
1862 l_num3_col(l_count) :=
1863 g_batch_commu_table_object(l_count).person_id;
1864 l_num4_col.extend(1);
1865 l_num4_col(l_count) :=
1866 g_batch_commu_table_object(l_count).per_cm_id;
1867 l_num5_col.extend(1);
1868 l_num5_col(l_count) :=
1869 g_batch_commu_table_object(l_count).cm_typ_id;
1870 l_num6_col.extend(1);
1871 l_num6_col(l_count) :=
1872 g_batch_commu_table_object(l_count).per_cm_prvdd_id;
1873 l_dat1_col.extend(1);
1874 l_dat1_col(l_count) :=
1875 g_batch_commu_table_object(l_count).to_be_sent_dt;
1876 l_num7_col.extend(1);
1877 l_num7_col(l_count) :=
1878 g_batch_commu_table_object(l_count).business_group_id;
1879 l_num8_col.extend(1);
1880 l_num8_col(l_count) :=
1881 g_batch_commu_table_object(l_count).object_version_number;
1882 --
1883 end loop;
1884 --
1885 l_table_name :='BEN_BATCH_COMMU_INFO';
1886 forall l_count in g_batch_commu_table_object.first..
1887 g_batch_commu_table_object.last
1888 --
1889 insert into ben_batch_commu_info
1890 (batch_commu_id,
1891 benefit_action_id,
1892 person_id,
1893 per_cm_id,
1894 cm_typ_id,
1895 per_cm_prvdd_id,
1896 to_be_sent_dt,
1897 business_group_id,
1898 object_version_number)
1899 values
1900 (l_num1_col(l_count),
1901 l_num2_col(l_count),
1902 l_num3_col(l_count),
1903 l_num4_col(l_count),
1904 l_num5_col(l_count),
1905 l_num6_col(l_count),
1906 l_dat1_col(l_count),
1907 l_num7_col(l_count),
1908 l_num8_col(l_count));
1909 --
1910 end if;
1911 --
1912 g_batch_commu_table_object.delete;
1913 --
1914 end if;
1915 --
1916 hr_utility.set_location('Leaving:'||l_proc, 5);
1917 --
1918 exception
1919 when table_full then
1920 fnd_message.set_name('BEN','BEN_92651_LOG_TABLE_FULL');
1921 fnd_message.set_token('TABLE_NAME',l_table_name);
1922 raise;
1923 when index_full then
1924 fnd_message.set_name('BEN','BEN_92652_LOG_INDEX_FULL');
1925 fnd_message.set_token('INDEX_NAME',l_table_name);
1926 raise;
1927 when others then
1928 raise;
1929 end write_table_and_file;
1930
1931 ------------------------------------------------------------------------
1932 -- write
1933 ------------------------------------------------------------------------
1934 procedure write(p_rec in out nocopy ben_type.g_report_rec) is
1935 --
1936 l_reporting_id number(38);
1937 l_object_version_number number(38);
1938 l_proc varchar2(80) := 'benutils.write 1';
1939 l_rec ben_type.g_report_rec;
1940 l_count number := 1;
1941 --
1942 begin
1943 --
1944 -- hr_utility.set_location('Entering:'||l_proc, 5);
1945 -- hr_utility.set_location(substr(p_rec.text,1,100),10);
1946 --
1947 g_sequence := g_sequence +1;
1948 --
1949 g_report_table_object.extend(1);
1950 l_count := g_report_table_object.count;
1951 /*
1952 select ben_reporting_s.nextval into
1953 g_report_table_object(l_count).reporting_id
1954 from sys.dual;
1955 */
1956 g_report_table_object(l_count).reporting_id := null;
1957 g_report_table_object(l_count).benefit_action_id := g_benefit_action_id;
1958 g_report_table_object(l_count).thread_id := g_thread_id;
1959 g_report_table_object(l_count).sequence := g_sequence;
1960 g_report_table_object(l_count).text := p_rec.text;
1961 g_report_table_object(l_count).rep_typ_cd := p_rec.rep_typ_cd;
1962 g_report_table_object(l_count).error_message_code := p_rec.error_message_code;
1963 g_report_table_object(l_count).national_identifier := p_rec.national_identifier;
1964 g_report_table_object(l_count).related_person_ler_id := p_rec.related_person_ler_id;
1965 g_report_table_object(l_count).temporal_ler_id := p_rec.temporal_ler_id;
1966 g_report_table_object(l_count).ler_id := p_rec.ler_id;
1967 g_report_table_object(l_count).person_id := p_rec.person_id;
1968 g_report_table_object(l_count).pgm_id := p_rec.pgm_id;
1969 g_report_table_object(l_count).pl_id := p_rec.pl_id;
1970 g_report_table_object(l_count).related_person_id := p_rec.related_person_id;
1971 g_report_table_object(l_count).oipl_id := p_rec.oipl_id;
1972 g_report_table_object(l_count).pl_typ_id := p_rec.pl_typ_id;
1973 g_report_table_object(l_count).object_version_number := 1;
1974 g_report_table_object(l_count).actl_prem_id := p_rec.actl_prem_id;
1975 g_report_table_object(l_count).val := p_rec.val;
1976 g_report_table_object(l_count).mo_num := p_rec.mo_num;
1977 g_report_table_object(l_count).yr_num := p_rec.yr_num;
1978 --
1979 -- Reset p_rec to null
1980 --
1981 p_rec := l_rec;
1982 --
1983 -- hr_utility.set_location('Leaving:'||l_proc, 40);
1984 --
1985 end write;
1986 ------------------------------------------------------------------------
1987 -- get_batch_parameters
1988 ------------------------------------------------------------------------
1989 procedure get_batch_parameters(p_benefit_action_id in number,
1990 p_rec in out nocopy g_batch_param_rec) is
1991 --
1992 l_proc varchar2(80) := 'benutils.get_batch_parameters';
1993 --
1994 cursor c1 is
1995 select PROCESS_DATE,
1996 MODE_CD,
1997 DERIVABLE_FACTORS_FLAG,
1998 VALIDATE_FLAG,
1999 PERSON_ID,
2000 PERSON_TYPE_ID,
2001 PGM_ID,
2002 BUSINESS_GROUP_ID,
2003 PL_ID,
2004 POPL_ENRT_TYP_CYCL_ID,
2005 NO_PROGRAMS_FLAG,
2006 NO_PLANS_FLAG,
2007 COMP_SELECTION_RL,
2008 PERSON_SELECTION_RL,
2009 LER_ID,
2010 ORGANIZATION_ID,
2011 BENFTS_GRP_ID,
2012 LOCATION_ID,
2013 PSTL_ZIP_RNG_ID,
2014 RPTG_GRP_ID,
2015 PL_TYP_ID,
2016 OPT_ID,
2017 ELIGY_PRFL_ID,
2018 VRBL_RT_PRFL_ID,
2019 LEGAL_ENTITY_ID,
2020 PAYROLL_ID,
2021 CM_TRGR_TYP_CD,
2022 DEBUG_MESSAGES_FLAG,
2023 CM_TYP_ID,
2024 AGE_FCTR_ID,
2025 MIN_AGE,
2026 MAX_AGE,
2027 LOS_FCTR_ID,
2028 MIN_LOS,
2029 MAX_LOS,
2030 CMBN_AGE_LOS_FCTR_ID,
2031 MIN_CMBN,
2032 MAX_CMBN,
2033 DATE_FROM,
2034 ELIG_ENROL_CD,
2035 ACTN_TYP_ID,
2036 AUDIT_LOG_FLAG,
2037 LF_EVT_OCRD_DT,
2038 LMT_PRPNIP_BY_ORG_FLAG,
2039 INELG_ACTION_CD
2040 from ben_benefit_actions
2041 where benefit_action_id = p_benefit_action_id;
2042 --
2043 begin
2044 --
2045 hr_utility.set_location('Entering:'||l_proc, 5);
2046 --
2047 if not g_batch_param_table_object.exists(1) then
2048 --
2049 open c1;
2050 --
2051 fetch c1 into g_batch_param_table_object(1);
2052 --
2053 --Bug 4998406
2054 if c1%found
2055 then
2056 --
2057 p_rec := g_batch_param_table_object(1);
2058 --
2059 end if;
2060 --
2061 --Bug 4998406
2062 close c1;
2063 --
2064 else
2065 --
2066 p_rec := g_batch_param_table_object(1); /* Bug 5009662 */
2067 --
2068 end if;
2069 --
2070 hr_utility.set_location('Leaving:'||l_proc, 5);
2071 --
2072 end get_batch_parameters;
2073
2074 ------------------------------------------------------------------------
2075 -- write
2076 ------------------------------------------------------------------------
2077 procedure write(p_rec in out nocopy g_batch_elig_rec) is
2078 --
2079 l_batch_elig_id number(38);
2080 l_object_version_number number(38);
2081 l_proc varchar2(80) := 'benutils.write 2';
2082 l_rec g_batch_elig_rec;
2083 l_count number := 1;
2084 l_oipl_rec ben_oipl_f%rowtype;
2085 l_params g_batch_param_rec;
2086 --
2087 begin
2088 --
2089 -- hr_utility.set_location('Entering:'||l_proc, 5);
2090 --
2091 if g_benefit_action_id is null then
2092 return;
2093 end if;
2094 --
2095 get_batch_parameters(p_benefit_action_id => g_benefit_action_id,
2096 p_rec => l_params);
2097 --
2098 if l_params.audit_log_flag <> 'Y' then
2099 --
2100 -- hr_utility.set_location('Leaving:'||l_proc, 4);
2101 return;
2102 --
2103 end if;
2104 --
2105 --
2106 g_batch_elig_table_object.extend(1);
2107 l_count := g_batch_elig_table_object.count;
2108 select ben_batch_elig_info_s.nextval into
2109 g_batch_elig_table_object(l_count).batch_elig_id
2110 from sys.dual;
2111 --
2112 g_batch_elig_table_object(l_count).benefit_action_id := g_benefit_action_id;
2113 g_batch_elig_table_object(l_count).person_id := p_rec.person_id;
2114 g_batch_elig_table_object(l_count).pgm_id := p_rec.pgm_id;
2115 if p_rec.pgm_id is null then
2116 --
2117 g_batch_elig_table_object(l_count).pgm_id := ben_manage_life_events.
2118 g_last_pgm_id;
2119 --
2120 end if;
2121 g_batch_elig_table_object(l_count).pl_id := p_rec.pl_id;
2122 if p_rec.oipl_id is not null then
2123 --
2124 ben_comp_object.get_object(p_oipl_id => p_rec.oipl_id,
2125 p_rec => l_oipl_rec);
2126 --
2127 g_batch_elig_table_object(l_count).pl_id := l_oipl_rec.pl_id;
2128 --
2129 end if;
2130 g_batch_elig_table_object(l_count).oipl_id := p_rec.oipl_id;
2131 g_batch_elig_table_object(l_count).elig_flag := p_rec.elig_flag;
2132 g_batch_elig_table_object(l_count).inelig_text := p_rec.inelig_text;
2133 g_batch_elig_table_object(l_count).business_group_id := p_rec.business_group_id;
2134 g_batch_elig_table_object(l_count).effective_date := p_rec.effective_date;
2135 g_batch_elig_table_object(l_count).object_version_number := 1;
2136 --
2137 -- Reset p_rec to null
2138 --
2139 p_rec := l_rec;
2140 --
2141 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2142 --
2143 end write;
2144 --
2145 procedure write(p_rec in out nocopy ben_type.g_batch_action_rec) is
2146 --
2147 l_proc varchar2(80) := 'benutils.write 3';
2148 l_rec ben_type.g_batch_action_rec;
2149 l_count number := 1;
2150 --
2151 begin
2152 --
2153 -- hr_utility.set_location('Entering:'||l_proc, 5);
2154 --
2155 g_batch_action_table_object.extend(1);
2156 l_count := g_batch_action_table_object.count;
2157 --
2158 g_batch_action_table_object(l_count).person_action_id := p_rec.person_action_id;
2159 g_batch_action_table_object(l_count).object_version_number := p_rec.object_version_number;
2160 g_batch_action_table_object(l_count).ler_id := p_rec.ler_id;
2161 g_batch_action_table_object(l_count).action_status_cd := p_rec.action_status_cd;
2162 g_batch_action_table_object(l_count).effective_date := p_rec.effective_date;
2163 --
2164 -- Reset p_rec to null
2165 --
2166 p_rec := l_rec;
2167 --
2168 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2169 --
2170 end write;
2171 --
2172 procedure write(p_rec in out nocopy g_batch_elctbl_rec) is
2173 --
2174 l_proc varchar2(80) := 'benutils.write 4';
2175 l_rec g_batch_elctbl_rec;
2176 l_count number := 1;
2177 l_params g_batch_param_rec;
2178 --
2179 begin
2180 --
2181 -- hr_utility.set_location('Entering:'||l_proc, 5);
2182 --
2183 if g_benefit_action_id is null then
2184 return;
2185 end if;
2186 --
2187 get_batch_parameters(p_benefit_action_id => g_benefit_action_id,
2188 p_rec => l_params);
2189 --
2190 if l_params.audit_log_flag <> 'Y' then
2191 -- hr_utility.set_location('Leaving:'||l_proc, 3);
2192 --
2193 return;
2194 --
2195 end if;
2196 --
2197 --
2198 g_batch_elctbl_table_object.extend(1);
2199 l_count := g_batch_elctbl_table_object.count;
2200 select ben_batch_elctbl_chc_info_s.nextval into
2201 g_batch_elctbl_table_object(l_count).batch_elctbl_id
2202 from sys.dual;
2203 --
2204 g_batch_elctbl_table_object(l_count).benefit_action_id := g_benefit_action_id;
2205 g_batch_elctbl_table_object(l_count).person_id := p_rec.person_id;
2206 g_batch_elctbl_table_object(l_count).pgm_id := p_rec.pgm_id;
2207 g_batch_elctbl_table_object(l_count).pl_id := p_rec.pl_id;
2208 g_batch_elctbl_table_object(l_count).oipl_id := p_rec.oipl_id;
2209 g_batch_elctbl_table_object(l_count).enrt_cvg_strt_dt := p_rec.enrt_cvg_strt_dt;
2210 g_batch_elctbl_table_object(l_count).enrt_perd_strt_dt := p_rec.enrt_perd_strt_dt;
2211 g_batch_elctbl_table_object(l_count).enrt_perd_end_dt := p_rec.enrt_perd_end_dt;
2212 g_batch_elctbl_table_object(l_count).erlst_deenrt_dt := p_rec.erlst_deenrt_dt;
2213 g_batch_elctbl_table_object(l_count).dflt_enrt_dt := p_rec.dflt_enrt_dt;
2214 g_batch_elctbl_table_object(l_count).enrt_typ_cycl_cd := p_rec.enrt_typ_cycl_cd;
2215 g_batch_elctbl_table_object(l_count).comp_lvl_cd := p_rec.comp_lvl_cd;
2216 g_batch_elctbl_table_object(l_count).mndtry_flag := p_rec.mndtry_flag;
2217 g_batch_elctbl_table_object(l_count).dflt_flag := p_rec.dflt_flag;
2218 g_batch_elctbl_table_object(l_count).business_group_id := p_rec.business_group_id;
2219 g_batch_elctbl_table_object(l_count).effective_date := p_rec.effective_date;
2220 g_batch_elctbl_table_object(l_count).object_version_number := 1;
2221 --
2222 -- Reset p_rec to null
2223 --
2224 p_rec := l_rec;
2225 --
2226 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2227 --
2228 end write;
2229 --
2230 procedure write(p_rec in out nocopy g_batch_rate_rec) is
2231 --
2232 l_proc varchar2(80) := 'benutils.write 5';
2233 l_rec g_batch_rate_rec;
2234 l_count number := 1;
2235 l_params g_batch_param_rec;
2236 --
2237 begin
2238 --
2239 -- hr_utility.set_location('Entering:'||l_proc, 5);
2240 --
2241 if g_benefit_action_id is null then
2242 return;
2243 end if;
2244 --
2245 get_batch_parameters(p_benefit_action_id => g_benefit_action_id,
2246 p_rec => l_params);
2247 --
2248 if l_params.audit_log_flag <> 'Y' then
2249 --
2250 -- hr_utility.set_location('Entering:'||l_proc, 3);
2251 return;
2252 --
2253 end if;
2254 --
2255 -- hr_utility.set_location('Writing Rate Record:'||l_proc, 5);
2256 --
2257 g_batch_rate_table_object.extend(1);
2258 l_count := g_batch_rate_table_object.count;
2259 select ben_batch_rate_info_s.nextval into
2260 g_batch_rate_table_object(l_count).batch_rt_id
2261 from sys.dual;
2262 --
2263 g_batch_rate_table_object(l_count).benefit_action_id := g_benefit_action_id;
2264 g_batch_rate_table_object(l_count).person_id := p_rec.person_id;
2265 g_batch_rate_table_object(l_count).pgm_id := p_rec.pgm_id;
2266 g_batch_rate_table_object(l_count).pl_id := p_rec.pl_id;
2267 g_batch_rate_table_object(l_count).oipl_id := p_rec.oipl_id;
2268 g_batch_rate_table_object(l_count).bnft_rt_typ_cd := p_rec.bnft_rt_typ_cd;
2269 g_batch_rate_table_object(l_count).dflt_flag := p_rec.dflt_flag;
2270 g_batch_rate_table_object(l_count).val := p_rec.val;
2271 g_batch_rate_table_object(l_count).old_val := p_rec.old_val;
2272 g_batch_rate_table_object(l_count).tx_typ_cd := p_rec.tx_typ_cd;
2273 g_batch_rate_table_object(l_count).acty_typ_cd := p_rec.acty_typ_cd;
2274 g_batch_rate_table_object(l_count).mn_elcn_val := p_rec.mn_elcn_val;
2275 g_batch_rate_table_object(l_count).mx_elcn_val := p_rec.mx_elcn_val;
2276 g_batch_rate_table_object(l_count).incrmt_elcn_val := p_rec.incrmt_elcn_val;
2277 g_batch_rate_table_object(l_count).dflt_val := p_rec.dflt_val;
2278 g_batch_rate_table_object(l_count).rt_strt_dt := p_rec.rt_strt_dt;
2279 g_batch_rate_table_object(l_count).business_group_id := p_rec.business_group_id;
2280 g_batch_rate_table_object(l_count).enrt_cvg_strt_dt := p_rec.enrt_cvg_strt_dt;
2281 g_batch_rate_table_object(l_count).enrt_cvg_thru_dt := p_rec.enrt_cvg_thru_dt;
2282 g_batch_rate_table_object(l_count).actn_cd := p_rec.actn_cd;
2283 g_batch_rate_table_object(l_count).close_actn_itm_dt := p_rec.close_actn_itm_dt;
2284 g_batch_rate_table_object(l_count).effective_date := p_rec.effective_date;
2285 g_batch_rate_table_object(l_count).object_version_number := 1;
2286 --
2287 -- Reset p_rec to null
2288 --
2289 p_rec := l_rec;
2290 --
2291 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2292 -- hr_utility.set_location('Number of Rate Records:'||g_batch_rate_table_object.count,5);
2293 --
2294 end write;
2295 --
2296 procedure write(p_rec in out nocopy g_batch_dpnt_rec) is
2297 --
2298 l_proc varchar2(80) := 'benutils.write 6';
2299 l_rec g_batch_dpnt_rec;
2300 l_count number := 1;
2301 l_params g_batch_param_rec;
2302 --
2303 begin
2304 --
2305 -- hr_utility.set_location('Entering:'||l_proc, 5);
2306 --
2307 if g_benefit_action_id is null then
2308 return;
2309 end if;
2310 --
2311 get_batch_parameters(p_benefit_action_id => g_benefit_action_id,
2312 p_rec => l_params);
2313 --
2314 if l_params.audit_log_flag <> 'Y' then
2315 --
2316 -- hr_utility.set_location('Leaving:'||l_proc, 3);
2317 return;
2318 --
2319 end if;
2320 --
2321 --
2322 g_batch_dpnt_table_object.extend(1);
2323 l_count := g_batch_dpnt_table_object.count;
2324 select ben_batch_dpnt_info_s.nextval into
2325 g_batch_dpnt_table_object(l_count).batch_dpnt_id
2326 from sys.dual;
2327 --
2328 g_batch_dpnt_table_object(l_count).benefit_action_id := g_benefit_action_id;
2329 g_batch_dpnt_table_object(l_count).person_id := p_rec.person_id;
2330 g_batch_dpnt_table_object(l_count).pgm_id := p_rec.pgm_id;
2331 g_batch_dpnt_table_object(l_count).pl_id := p_rec.pl_id;
2332 g_batch_dpnt_table_object(l_count).oipl_id := p_rec.oipl_id;
2333 g_batch_dpnt_table_object(l_count).contact_typ_cd := p_rec.contact_typ_cd;
2334 g_batch_dpnt_table_object(l_count).dpnt_person_id := p_rec.dpnt_person_id;
2335 g_batch_dpnt_table_object(l_count).business_group_id := p_rec.business_group_id;
2336 g_batch_dpnt_table_object(l_count).enrt_cvg_strt_dt := p_rec.enrt_cvg_strt_dt;
2337 g_batch_dpnt_table_object(l_count).enrt_cvg_thru_dt := p_rec.enrt_cvg_thru_dt;
2338 g_batch_dpnt_table_object(l_count).actn_cd := p_rec.actn_cd;
2339 g_batch_dpnt_table_object(l_count).effective_date := p_rec.effective_date;
2340 g_batch_dpnt_table_object(l_count).object_version_number := 1;
2341 --
2342 -- Reset p_rec to null
2343 --
2344 p_rec := l_rec;
2345 --
2346 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2347 --
2348 end write;
2349 --
2350 procedure write(p_rec in out nocopy ben_type.g_batch_commu_rec) is
2351 --
2352 l_proc varchar2(80) := 'benutils.write 7';
2353 l_rec ben_type.g_batch_commu_rec;
2354 l_count number := 1;
2355 --
2356 begin
2357 --
2358 -- hr_utility.set_location('Entering:'||l_proc, 5);
2359 --
2360 g_batch_commu_table_object.extend(1);
2361 l_count := g_batch_commu_table_object.count;
2362 select ben_batch_commu_info_s.nextval into
2363 g_batch_commu_table_object(l_count).batch_commu_id
2364 from sys.dual;
2365 --
2366 g_batch_commu_table_object(l_count).benefit_action_id := g_benefit_action_id;
2367 g_batch_commu_table_object(l_count).person_id := p_rec.person_id;
2368 g_batch_commu_table_object(l_count).per_cm_id := p_rec.per_cm_id;
2369 g_batch_commu_table_object(l_count).cm_typ_id := p_rec.cm_typ_id;
2370 g_batch_commu_table_object(l_count).per_cm_prvdd_id := p_rec.per_cm_prvdd_id;
2371 g_batch_commu_table_object(l_count).business_group_id :=p_rec.business_group_id;
2372 g_batch_commu_table_object(l_count).to_be_sent_dt := p_rec.to_be_sent_dt;
2373 g_batch_commu_table_object(l_count).object_version_number := 1;
2374 --
2375 -- Reset p_rec to null
2376 --
2377 p_rec := l_rec;
2378 --
2379 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2380 --
2381 end write;
2382 --
2383 procedure write(p_rec in out nocopy g_batch_ler_rec) is
2384 --
2385 l_batch_ler_id number(38);
2386 l_object_version_number number(38);
2387 l_proc varchar2(80) := 'benutils.write 8';
2388 l_rec g_batch_ler_rec;
2389 l_count number := 1;
2390 l_params g_batch_param_rec;
2391 --
2392 begin
2393 --
2394 -- hr_utility.set_location('Entering:'||l_proc, 5);
2395 --
2396 g_batch_ler_table_object.extend(1);
2397 l_count := g_batch_ler_table_object.count;
2398 select ben_batch_ler_info_s.nextval into
2399 g_batch_ler_table_object(l_count).batch_ler_id
2400 from sys.dual;
2401 --
2402 g_batch_ler_table_object(l_count).benefit_action_id := g_benefit_action_id;
2403 g_batch_ler_table_object(l_count).person_id := p_rec.person_id;
2404 g_batch_ler_table_object(l_count).ler_id := p_rec.ler_id;
2405 g_batch_ler_table_object(l_count).lf_evt_ocrd_dt := p_rec.lf_evt_ocrd_dt;
2406 g_batch_ler_table_object(l_count).replcd_flag := p_rec.replcd_flag;
2407 g_batch_ler_table_object(l_count).crtd_flag := p_rec.crtd_flag;
2408 g_batch_ler_table_object(l_count).tmprl_flag := p_rec.tmprl_flag;
2409 g_batch_ler_table_object(l_count).dltd_flag := p_rec.dltd_flag;
2410 g_batch_ler_table_object(l_count).open_and_clsd_flag := p_rec.open_and_clsd_flag;
2411 g_batch_ler_table_object(l_count).clsd_flag := p_rec.clsd_flag;
2412 g_batch_ler_table_object(l_count).clpsd_flag := p_rec.clpsd_flag;
2413 g_batch_ler_table_object(l_count).clsn_flag := p_rec.clsn_flag;
2414 g_batch_ler_table_object(l_count).no_effect_flag := p_rec.no_effect_flag;
2415 g_batch_ler_table_object(l_count).cvrge_rt_prem_flag := p_rec.cvrge_rt_prem_flag;
2416 g_batch_ler_table_object(l_count).not_crtd_flag := p_rec.not_crtd_flag;
2417 g_batch_ler_table_object(l_count).stl_actv_flag := p_rec.stl_actv_flag;
2418 g_batch_ler_table_object(l_count).per_in_ler_id := p_rec.per_in_ler_id;
2419 g_batch_ler_table_object(l_count).business_group_id := p_rec.business_group_id;
2420 g_batch_ler_table_object(l_count).effective_date := p_rec.effective_date;
2421 g_batch_ler_table_object(l_count).object_version_number := 1;
2422 --
2423 -- Reset p_rec to null
2424 --
2425 p_rec := l_rec;
2426 --
2427 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2428 --
2429 end write;
2430 --
2431 procedure write(p_rec in out nocopy ben_type.g_batch_proc_rec) is
2432 --
2433 l_batch_proc_id number(38);
2434 l_object_version_number number(38);
2435 l_proc varchar2(80) := 'benutils.write 9';
2436 l_rec ben_type.g_batch_proc_rec;
2437 l_count number := 1;
2438 --
2439 begin
2440 --
2441 -- hr_utility.set_location('Entering:'||l_proc, 5);
2442 --
2443 ben_batch_proc_info_api.create_batch_proc_info
2444 (p_validate => false
2445 ,p_batch_proc_id => l_batch_proc_id
2446 ,p_benefit_action_id => g_benefit_action_id
2447 ,p_strt_dt => p_rec.strt_dt
2448 ,p_end_dt => p_rec.end_dt
2449 ,p_strt_tm => p_rec.strt_tm
2450 ,p_end_tm => p_rec.end_tm
2451 ,p_elpsd_tm => p_rec.elpsd_tm
2452 ,p_per_slctd => p_rec.per_slctd
2453 ,p_per_proc => p_rec.per_proc
2454 ,p_per_unproc => p_rec.per_unproc
2455 ,p_per_proc_succ => p_rec.per_proc_succ
2456 ,p_per_err => p_rec.per_err
2457 ,p_business_group_id => p_rec.business_group_id
2458 ,p_object_version_number => l_object_version_number);
2459 --
2460 -- Reset p_rec to null
2461 --
2462 p_rec := l_rec;
2463 --
2464 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2465 --
2466 end write;
2467 --
2468 procedure write(p_text in varchar2,
2469 p_validate in boolean ) is
2470 --
2471 l_reporting_id number(38);
2472 l_object_version_number number(38);
2473 l_proc varchar2(80) := 'benutils.write 10';
2474 l_count number := 1;
2475 --
2476 begin
2477 --
2478 -- Check if the audit flag is set and is N. Do not
2479 -- log when N.
2480 --
2481 if g_batch_param_table_object.exists(1)
2482 then
2483 --
2484 if g_batch_param_table_object(1).audit_log_flag = 'N' then
2485 --
2486 return;
2487 --
2488 end if;
2489 --
2490 end if;
2491 --
2492 if g_benefit_action_id = -1 or g_benefit_action_id is null
2493 then
2494 --
2495 -- Assume no logging required
2496 --
2497 -- hr_utility.set_location('Leaving:'||l_proc, 4);
2498 return;
2499 --
2500 end if;
2501 --
2502 g_sequence := g_sequence +1;
2503 --
2504 -- hr_utility.set_location('Report Varray:'||l_proc, 5);
2505 g_report_table_object.extend(1);
2506 l_count := g_report_table_object.count;
2507 /*
2508 select ben_reporting_s.nextval into
2509 g_report_table_object(l_count).reporting_id
2510 from sys.dual;
2511 */
2512 g_report_table_object(l_count).reporting_id := null;
2513 g_report_table_object(l_count).benefit_action_id := g_benefit_action_id;
2514 g_report_table_object(l_count).thread_id := g_thread_id;
2515 g_report_table_object(l_count).sequence := g_sequence;
2516 g_report_table_object(l_count).text := p_text;
2517 g_report_table_object(l_count).rep_typ_cd := null;
2518 g_report_table_object(l_count).error_message_code := null;
2519 g_report_table_object(l_count).national_identifier := null;
2520 g_report_table_object(l_count).related_person_ler_id := null;
2521 g_report_table_object(l_count).temporal_ler_id := null;
2522 g_report_table_object(l_count).ler_id := null;
2523 g_report_table_object(l_count).person_id := null;
2524 g_report_table_object(l_count).pgm_id := null;
2525 g_report_table_object(l_count).pl_id := null;
2526 g_report_table_object(l_count).related_person_id := null;
2527 g_report_table_object(l_count).oipl_id := null;
2528 g_report_table_object(l_count).pl_typ_id := null;
2529 g_report_table_object(l_count).object_version_number := 1;
2530 g_report_table_object(l_count).actl_prem_id := null;
2531 g_report_table_object(l_count).val := null;
2532 g_report_table_object(l_count).mo_num := null;
2533 g_report_table_object(l_count).yr_num := null;
2534 --
2535 -- hr_utility.set_location('Leaving:'||l_proc, 40);
2536 --
2537 end write;
2538
2539 ------------------------------------------------------------------------
2540 -- update_life_event_cache
2541 ------------------------------------------------------------------------
2542 procedure update_life_event_cache
2543 (p_open_and_closed in varchar2 ) is
2544 --
2545 l_proc varchar2(80) := 'benutils.update_life_event_cache';
2546 --
2547 begin
2548 --
2549 hr_utility.set_location('Entering:'||l_proc, 5);
2550 --
2551 -- Special case where we have to update the open_and_clsd_flag if the
2552 -- life event was created and not replaced
2553 --
2554 if g_batch_ler_table_object.count > 0 then
2555 --
2556 g_batch_ler_table_object(g_batch_ler_table_object.last).crtd_flag := 'N';
2557 g_batch_ler_table_object(g_batch_ler_table_object.last).open_and_clsd_flag := 'Y';
2558 --
2559 if p_open_and_closed = 'Y' then
2560 --
2561 -- Update the open and closed flag to Y and set the created flag to N
2562 --
2563 g_batch_ler_table_object(g_batch_ler_table_object.last).no_effect_flag := 'Y';
2564 --
2565 else
2566 --
2567 g_batch_ler_table_object(g_batch_ler_table_object.last).cvrge_rt_prem_flag := 'Y';
2568 --
2569 end if;
2570 --
2571 end if;
2572 --
2573 hr_utility.set_location('Entering:'||l_proc, 5);
2574 --
2575 end update_life_event_cache;
2576
2577 ------------------------------------------------------------------------
2578 -- get_parameter
2579 ------------------------------------------------------------------------
2580 procedure get_parameter(p_business_group_id in number,
2581 p_batch_exe_cd in varchar2,
2582 p_threads out nocopy number,
2583 p_chunk_size out nocopy number,
2584 p_max_errors out nocopy number) is
2585 --
2586 l_proc varchar2(80) := 'benutils.get_parameter';
2587 --
2588 cursor c1 is
2589 select nvl(bbp.thread_cnt_num,
2590 decode(p_batch_exe_cd,'BENGCMOD',1,3)),
2591 nvl(bbp.chunk_size,10),
2592 nvl(bbp.max_err_num,20)
2593 from ben_batch_parameter bbp
2594 where bbp.batch_exe_cd = p_batch_exe_cd
2595 and bbp.business_group_id = p_business_group_id;
2596 --
2597 begin
2598 --
2599 hr_utility.set_location('Entering:'||l_proc, 5);
2600 --
2601 open c1;
2602 --
2603 fetch c1 into p_threads,
2604 p_chunk_size,
2605 p_max_errors;
2606 if c1%notfound then
2607 --
2608 -- Default all values
2609 --
2610 -- 5504516. For CWB Participation process, the default threads
2611 -- is 1. For others it is 3.
2612 -- If the customer defines a record, use the value from there.
2613 --
2614 if p_batch_exe_cd = 'BENGCMOD' then
2615 p_threads := 1;
2616 else
2617 p_threads := 3;
2618 end if;
2619 p_chunk_size := 10;
2620 p_max_errors := 20;
2621 --
2622 end if;
2623 --
2624 close c1;
2625 --
2626 hr_utility.set_location(l_proc||'p_threads : '||p_threads, 5);
2627 hr_utility.set_location('Leaving:'||l_proc, 5);
2628 --
2629 end get_parameter;
2630
2631 ------------------------------------------------------------------------
2632 -- iftrue
2633 ------------------------------------------------------------------------
2634 function iftrue(p_expression in boolean,
2635 p_true in varchar2,
2636 p_false in varchar2) return varchar2 is
2637 --
2638 l_proc varchar2(80) := 'benutils.iftrue';
2639 --
2640 begin
2641 --
2642 hr_utility.set_location('Entering:'||l_proc, 5);
2643 --
2644 if p_expression then
2645 --
2646 --hr_utility.set_location('Leaving:'||l_proc, 4);
2647 return p_true;
2648 --
2649 else
2650 --
2651 --hr_utility.set_location('Leaving:'||l_proc, 5);
2652 return p_false;
2653 --
2654 end if;
2655 --
2656 end iftrue;
2657
2658 ------------------------------------------------------------------------
2659 -- zero_to_null
2660 ------------------------------------------------------------------------
2661 function zero_to_null(p_value in number) return number is
2662 --
2663 l_value number;
2664 l_proc varchar2(80) := 'benutils.zero_to_null';
2665 --
2666 begin
2667 --
2668 --hr_utility.set_location('Entering:'||l_proc, 5);
2669 --
2670 l_value := iftrue(p_expression => p_value = 0,
2671 p_true => null,
2672 p_false => p_value);
2673 --
2674 --hr_utility.set_location('Leaving:'||l_proc, 5);
2675 return l_value;
2676 --
2677 --
2678 end zero_to_null;
2679
2680 ------------------------------------------------------------------------
2681 -- get_bp_name
2682 ------------------------------------------------------------------------
2683 function get_bp_name (p_tablename in varchar2) return varchar2 is
2684 --
2685 l_status varchar2(1);
2686 l_industry varchar2(1);
2687 l_application_short_name varchar2(30);
2688 l_oracle_schema varchar2(30);
2689 l_return boolean;
2690 --
2691 cursor c1(l_oracle_schema in varchar2) is
2692 select a.comments
2693 from all_tab_comments a
2694 where a.table_name = p_tablename
2695 and a.owner = upper(l_oracle_schema);
2696 --
2697 l_comments varchar2(2000);
2698 l_start number(9) := 0;
2699 l_end number(9) := 0;
2700 l_proc varchar2(80) := 'benutils.get_bp_name';
2701 --
2702 begin
2703 --
2704 hr_utility.set_location('Entering:'||l_proc, 5);
2705 --
2706 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
2707 -- following call
2708 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
2709 status => l_status,
2710 industry => l_industry,
2711 oracle_schema => l_oracle_schema);
2712
2713
2714
2715 open c1(l_oracle_schema);
2716 --
2717 fetch c1 into l_comments;
2718 if c1%found then
2719 --
2720 -- strip out bp name from string
2721 --
2722 l_start := instr(l_comments,'<<');
2723 l_end := instr(l_comments,'>>');
2724 l_comments := substr(l_comments,l_start+2,l_end-(l_start+2));
2725 --
2726 end if;
2727 --
2728 close c1;
2729 --
2730 if l_comments is null then
2731 --
2732 l_comments := 'BP_NOT_FOUND';
2733 --
2734 end if;
2735 --
2736 hr_utility.set_location('Leaving:'||l_proc, 5);
2737 return l_comments;
2738 --
2739 --
2740 end get_bp_name;
2741
2742 ------------------------------------------------------------------------
2743 -- part_of_pkey
2744 ------------------------------------------------------------------------
2745 function part_of_pkey(p_column_name in varchar2) return boolean is
2746 --
2747 l_proc varchar2(80) := 'benutils.part_of_pkey';
2748 --
2749 begin
2750 --
2751 --hr_utility.set_location('Entering:'||l_proc, 5);
2752 --
2753 -- Check if a column is part of the Primary Key
2754 --
2755 for l_counter in 1..g_primary_key_count loop
2756 --
2757 if p_column_name = g_part_of_pkey(l_counter) then
2758 --
2759 --hr_utility.set_location('Leaving:'||l_proc, 3);
2760 return true;
2761 --
2762 end if;
2763 --
2764 end loop;
2765 --
2766 --hr_utility.set_location('Leaving:'||l_proc, 5);
2767 return false;
2768 --
2769 end part_of_pkey;
2770
2771 ------------------------------------------------------------------------
2772 -- define_primary_key
2773 ------------------------------------------------------------------------
2774 procedure define_primary_key(p_tablename in varchar2) is
2775 --
2776 l_status varchar2(1);
2777 l_industry varchar2(1);
2778 l_application_short_name varchar2(30);
2779 l_oracle_schema varchar2(30);
2780 l_return boolean;
2781 --
2782 cursor c1(l_oracle_schema in varchar2) is
2783 select t.column_name
2784 from all_tab_columns t,
2785 all_cons_columns c,
2786 all_constraints a
2787 where a.constraint_type = 'P'
2788 and a.table_name = p_tablename
2789 and a.constraint_name = c.constraint_name
2790 and t.table_name = c.table_name
2791 and t.column_name = c.column_name
2792 and t.table_name = a.table_name
2793 and t.owner = upper(l_oracle_schema)
2794 and c.owner = upper(l_oracle_schema)
2795 and a.owner = upper(l_oracle_schema)
2796 order by c.position;
2797 --
2798 l_column varchar2(30);
2799 l_number_of_columns number(9) := 0;
2800 l_proc varchar2(80) := 'benutils.define_primary_key';
2801 --
2802 begin
2803 --
2804 hr_utility.set_location('Entering:'||l_proc, 5);
2805 --
2806 -- Get the column if is part of the primary key.
2807 --
2808 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
2809 -- following call
2810 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
2811 status => l_status,
2812 industry => l_industry,
2813 oracle_schema => l_oracle_schema);
2814 --
2815 open c1(l_oracle_schema);
2816 --
2817 loop
2818 --
2819 fetch c1 into l_column;
2820 exit when c1%notfound;
2821 --
2822 if c1%found then
2823 --
2824 l_number_of_columns := l_number_of_columns+1;
2825 g_part_of_pkey(l_number_of_columns) := l_column;
2826 --
2827 end if;
2828 --
2829 end loop;
2830 --
2831 close c1;
2832 --
2833 g_primary_key_count := l_number_of_columns;
2834 --
2835 hr_utility.set_location('Leaving:'||l_proc, 5);
2836 --
2837 end define_primary_key;
2838
2839 ------------------------------------------------------------------------
2840 -- lookups_exist
2841 ------------------------------------------------------------------------
2842 function lookups_exist(p_tablename in varchar2) return boolean is
2843 --
2844 l_status varchar2(1);
2845 l_industry varchar2(1);
2846 l_application_short_name varchar2(30);
2847 l_oracle_schema varchar2(30);
2848 l_return boolean;
2849 --
2850 cursor c1(l_oracle_schema in varchar2) is
2851 select null
2852 from all_tab_columns t
2853 where t.table_name = p_tablename
2854 and (substr(t.column_name,length(t.column_name)-2,3) = '_CD'
2855 or substr(t.column_name,length(t.column_name)-3,4) = '_IND'
2856 or substr(t.column_name,length(t.column_name)-3,4) = '_UOM'
2857 or substr(t.column_name,length(t.column_name)-2,3) = '_RL'
2858 or substr(t.column_name,length(t.column_name)-4,5) = '_FLAG')
2859 and t.owner = upper(l_oracle_schema);
2860 --
2861 l_dummy varchar2(1);
2862 l_proc varchar2(80) := 'benutils.lookups_exist';
2863 --
2864 begin
2865 --
2866 --hr_utility.set_location('Entering:'||l_proc, 5);
2867 --
2868 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
2869 -- following call
2870 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
2871 status => l_status,
2872 industry => l_industry,
2873 oracle_schema => l_oracle_schema);
2874 open c1(l_oracle_schema);
2875 --
2876 fetch c1 into l_dummy;
2877 if c1%found then
2878 --
2879 close c1;
2880 --hr_utility.set_location('Leaving:'||l_proc, 3);
2881 return true;
2882 --
2883 end if;
2884 --
2885 close c1;
2886 --
2887 --hr_utility.set_location('Leaving:'||l_proc, 5);
2888 return false;
2889 --
2890 end lookups_exist;
2891
2892 ------------------------------------------------------------------------
2893 -- get_primary_key
2894 ------------------------------------------------------------------------
2895 function get_primary_key(p_tablename in varchar2) return varchar2 is
2896 --
2897 l_primary_key all_cons_columns.column_name%type;
2898 l_proc varchar2(80) := 'benutils.get_primary_key';
2899 l_status varchar2(1);
2900 l_industry varchar2(1);
2901 l_application_short_name varchar2(30);
2902 l_oracle_schema varchar2(30);
2903 l_return boolean;
2904 --
2905 cursor c1(l_oracle_schema in varchar2) is
2906 select c.column_name
2907 from all_tab_columns t,
2908 all_cons_columns c,
2909 all_constraints a
2910 where a.constraint_type = 'P'
2911 and a.table_name = p_tablename
2912 and a.constraint_name = c.constraint_name
2913 and t.column_name = c.column_name
2914 and t.table_name = a.table_name
2915 and t.owner = upper(l_oracle_schema)
2916 and c.owner = upper(l_oracle_schema)
2917 and a.owner = upper(l_oracle_schema)
2918 order by c.position;
2919 --
2920 begin
2921 --
2922 --hr_utility.set_location('Entering:'||l_proc, 5);
2923 --
2924 -- Get the Primary Key for the table.
2925 --
2926 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
2927 -- following call
2928 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
2929 status => l_status,
2930 industry => l_industry,
2931 oracle_schema => l_oracle_schema);
2932 --
2933 open c1(l_oracle_schema);
2934 --
2935 fetch c1 into l_primary_key;
2936 --
2937 close c1;
2938 --
2939 --hr_utility.set_location('Leaving:'||l_proc, 5);
2940 return l_primary_key;
2941 --
2942 end get_primary_key;
2943
2944 ------------------------------------------------------------------------
2945 -- business_group_exists
2946 ------------------------------------------------------------------------
2947 function business_group_exists(p_tablename in varchar2) return boolean is
2948 --
2949 l_status varchar2(1);
2950 l_industry varchar2(1);
2951 l_application_short_name varchar2(30);
2952 l_oracle_schema varchar2(30);
2953 l_return boolean;
2954 --
2955 cursor c1(l_oracle_schema in varchar2) is
2956 select null
2957 from all_tab_columns a
2958 where a.column_name = 'BUSINESS_GROUP_ID'
2959 and a.table_name = p_tablename
2960 and a.owner = upper(l_oracle_schema);
2961 --
2962 l_dummy varchar2(1);
2963 l_result boolean := false;
2964 l_proc varchar2(80) := 'benutils.business_group_exists';
2965 --
2966 begin
2967 --
2968 --hr_utility.set_location('Entering:'||l_proc, 5);
2969 --
2970 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
2971 -- following call
2972 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
2973 status => l_status,
2974 industry => l_industry,
2975 oracle_schema => l_oracle_schema);
2976 --
2977 open c1(l_oracle_schema);
2978 --
2979 fetch c1 into l_dummy;
2980 --
2981 if c1%found then
2982 --
2983 l_result := true;
2984 --
2985 end if;
2986 --
2987 close c1;
2988 --
2989 --hr_utility.set_location('Leaving:'||l_proc, 5);
2990 return l_result;
2991 --
2992 end business_group_exists;
2993
2994 ------------------------------------------------------------------------
2995 -- table_datetracked
2996 ------------------------------------------------------------------------
2997 function table_datetracked(p_tablename in varchar2) return boolean is
2998 --
2999 l_proc varchar2(80) := 'benutils.table_datetracked';
3000 --
3001 begin
3002 --
3003 --hr_utility.set_location('Entering:'||l_proc, 5);
3004 --
3005 if substr(p_tablename,length(p_tablename)-1,2) = '_F' then
3006 --hr_utility.set_location('Leaving:'||l_proc, 3);
3007 return true;
3008 else
3009 --hr_utility.set_location('Leaving:'||l_proc, 5);
3010 return false;
3011 end if;
3012 --
3013 end table_datetracked;
3014
3015 ------------------------------------------------------------------------
3016 -- attributes_exist
3017 ------------------------------------------------------------------------
3018 function attributes_exist(p_tablename in varchar2) return boolean is
3019 --
3020 l_status varchar2(1);
3021 l_industry varchar2(1);
3022 l_application_short_name varchar2(30);
3023 l_oracle_schema varchar2(30);
3024 l_return boolean;
3025 --
3026 cursor c1(l_oracle_schema in varchar2) is
3027 select null
3028 from all_tab_columns utc
3029 where utc.table_name = p_tablename
3030 and utc.column_name like '%ATTRIBUTE%'
3031 and utc.owner = upper(l_oracle_schema);
3032 --
3033 l_dummy varchar2(1);
3034 l_found boolean := false;
3035 l_proc varchar2(80) := 'benutils.attributes_exist';
3036 --
3037 begin
3038 --
3039 --hr_utility.set_location('Entering:'||l_proc, 5);
3040 --
3041 -- See if attributes exist for the table
3042 --
3043 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
3044 -- following call
3045 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
3046 status => l_status,
3047 industry => l_industry,
3048 oracle_schema => l_oracle_schema);
3049 --
3050 open c1(l_oracle_schema);
3051 --
3052 fetch c1 into l_dummy;
3053 if c1%found then
3054 --
3055 l_found := true;
3056 --
3057 end if;
3058 --
3059 close c1;
3060 --
3061 --hr_utility.set_location('Leaving:'||l_proc, 5);
3062 return l_found;
3063 --
3064 end attributes_exist;
3065
3066 ------------------------------------------------------------------------
3067 -- get_pk_constraint_name
3068 ------------------------------------------------------------------------
3069 function get_pk_constraint_name(p_tablename in varchar2) return varchar2 is
3070 --
3071 l_status varchar2(1);
3072 l_industry varchar2(1);
3073 l_application_short_name varchar2(30);
3074 l_oracle_schema varchar2(30);
3075 l_return boolean;
3076 --
3077 cursor c1(l_oracle_schema in varchar2) is
3078 select con.constraint_name
3079 from all_constraints con
3080 where con.table_name = p_tablename
3081 and con.constraint_type = 'P'
3082 and con.owner = upper(l_oracle_schema);
3083 --
3084 l_constraint_name all_constraints.constraint_name%type;
3085 l_proc varchar2(80) := 'benutils.get_pk_constraint_name';
3086 --
3087 begin
3088 --
3089 --hr_utility.set_location('Entering:'||l_proc, 5);
3090 --
3091 -- get the Primary Key constraint name for the table
3092 --
3093 -- Bug 3431740 Parameter l_oracle_schema added to cursor c1, the value is got by the
3094 -- following call
3095 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
3096 status => l_status,
3097 industry => l_industry,
3098 oracle_schema => l_oracle_schema);
3099 --
3100 open c1(l_oracle_schema);
3101 --
3102 fetch c1 into l_constraint_name;
3103 --
3104 close c1;
3105 --
3106 --hr_utility.set_location('Leaving:'||l_proc, 5);
3107 return l_constraint_name;
3108 --
3109 end get_pk_constraint_name;
3110 --
3111 function column_changed(p_old_column in varchar2
3112 ,p_new_column in varchar2
3113 ,p_new_value in varchar2) return boolean is
3114 begin
3115 if ((p_new_column = p_old_column)
3116 OR ((p_old_column IS NULL)
3117 AND (p_new_column IS NULL)))
3118 then
3119 hr_utility.set_location('FALSE',10);
3120 return FALSE;
3121 --
3122 -- Value MUST have changed
3123 -- so if any value chosen return true
3124 -- elsif specific value test for it
3125 -- being new value.
3126 elsif p_new_value = 'OABANY'
3127 then
3128 hr_utility.set_location('TRUE',10);
3129 return TRUE;
3130 elsif ((p_new_value = 'NULL')
3131 AND (p_new_column IS NULL)
3132 )
3133 then
3134 hr_utility.set_location('TRUE',10);
3135 return TRUE;
3136 elsif ((p_new_value = 'NULL')
3137 AND (p_new_column IS NOT NULL))
3138 then
3139 hr_utility.set_location('FALSE',10);
3140 return FALSE;
3141 elsif ((p_new_column IS NOT NULL)
3142 AND (p_new_column = p_new_value)
3143 )
3144 then
3145 hr_utility.set_location('TRUE',10);
3146 return TRUE;
3147 end if;
3148 hr_utility.set_location('FALSE',10);
3149 return FALSE;
3150 end;
3151
3152 ------------------------------------------------------------------------
3153 -- column_changed
3154 ------------------------------------------------------------------------
3155 function column_changed(p_old_column in date
3156 ,p_new_column in date
3157 ,p_new_value in varchar2) return boolean is
3158 begin
3159 hr_utility.set_location('In routine',10);
3160 if ((p_new_column = p_old_column)
3161 OR ((p_old_column IS NULL)
3162 AND (p_new_column IS NULL)))
3163 then
3164 hr_utility.set_location('FALSE1',10);
3165 return FALSE;
3166 --
3167 -- Value MUST have changed
3168 -- so if any value chosen return true
3169 -- else if specific value test for it
3170 -- being new value.
3171 elsif p_new_value = 'OABANY'
3172 then
3173 -- Bug 1167917 Do not trigger date oabany's for null nor end of time.
3174 -- eot is a date that should work like null.
3175 -- Bug#2001857-null value must be treated differently as the condition above checks
3176 -- and both the values are null then false is returnedi-null condition masked
3177 if p_new_column = hr_api.g_eot then
3178 -- or p_new_column is null then
3179 hr_utility.set_location('FALSE2',10);
3180 return FALSE;
3181 else
3182 hr_utility.set_location('TRUE3',10);
3183 return TRUE;
3184 end if;
3185 elsif ((p_new_value = 'NULL')
3186 AND (p_new_column IS NULL or
3187 p_new_column = hr_api.g_eot)
3188 )
3189 then
3190 -- Bug 1167017, treat eot as null
3191 hr_utility.set_location('TRUE4',10);
3192 return TRUE;
3193 elsif ((p_new_value = 'NULL')
3194 AND (p_new_column IS NOT NULL) and
3195 p_new_column <> hr_api.g_eot)
3196 then
3197 hr_utility.set_location('FALSE5',10);
3198 return FALSE;
3199 elsif p_new_value = 'ENDTM' and p_new_column = hr_api.g_eot then
3200 hr_utility.set_location('TRUE5',10);
3201 return true;
3202 elsif ((p_new_column IS NOT NULL)
3203 AND (to_char(p_new_column) = p_new_value)
3204 )
3205 then
3206 hr_utility.set_location('TRUE6',10);
3207 return TRUE;
3208 end if;
3209 hr_utility.set_location('FALSE7',10);
3210 return FALSE;
3211 end;
3212 --
3213 function column_changed(p_old_column in number
3214 ,p_new_column in number
3215 ,p_new_value in varchar2) return boolean is
3216 begin
3217 if ((p_new_column = p_old_column)
3218 OR ((p_old_column IS NULL)
3219 AND (p_new_column IS NULL)))
3220 then
3221 return FALSE;
3222 --
3223 -- Value MUST have changed
3224 -- so if any value chosen return true
3225 -- elsif specific value test for it
3226 -- being new value.
3227 elsif p_new_value = 'OABANY'
3228 then
3229 return TRUE;
3230 elsif ((p_new_value = 'NULL')
3231 AND (p_new_column IS NULL)
3232 )
3233 then
3234 return TRUE;
3235 elsif ((p_new_value = 'NULL')
3236 AND (p_new_column IS NOT NULL))
3237 then
3238 return FALSE;
3239 elsif ((p_new_column IS NOT NULL)
3240 AND (p_new_column = p_new_value)
3241 )
3242 then
3243 return TRUE;
3244 end if;
3245 return FALSE;
3246 end;
3247
3248 ------------------------------------------------------------------------
3249 -- do_rounding
3250 ------------------------------------------------------------------------
3251 function do_rounding(p_rounding_cd in varchar2,
3252 p_rounding_rl in number ,
3253 p_assignment_id in number ,
3254 p_value in number,
3255 p_effective_date in date) return number is
3256 --
3257 l_proc varchar2(72) := 'benutils.do_rounding';
3258 --
3259 l_rndg_fctr number;
3260 l_rndg_type varchar2(10);
3261 l_modulus number;
3262 l_result number;
3263 l_val_chg_flag varchar2(1) := 'N' ;
3264 l_value number := p_value ;
3265 --
3266 -- Set up fast formula stuff
3267 --
3268 l_outputs ff_exec.outputs_t;
3269 --
3270 Begin
3271 --
3272 hr_utility.set_location('Entering:'||l_proc, 5);
3273 --
3274 if p_rounding_cd is null then
3275 --
3276 l_result := p_value;
3277 hr_utility.set_location('Leaving:'||l_proc, 5);
3278 return l_result;
3279 --
3280 elsif p_rounding_cd = 'RL' then
3281 --
3282 -- Call formula initialise routine
3283 --
3284 l_outputs := benutils.formula
3285 (p_formula_id => p_rounding_rl,
3286 p_effective_date => p_effective_date,
3287 p_assignment_id => p_assignment_id,
3288 p_param1 => 'VALUE',
3289 p_param1_value => p_value,
3290 -- FONM
3291 p_param2 => 'BEN_IV_RT_STRT_DT',
3292 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
3293 p_param3 => 'BEN_IV_CVG_STRT_DT',
3294 p_param3_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
3295 --
3296 l_result := l_outputs(l_outputs.first).value;
3297 --
3298 else
3299 --
3300 -- default rounding type code will override if needed
3301 --
3302 l_rndg_type := 'UP-DOWN';
3303 --
3304 if p_rounding_cd = 'RUTNHND' then
3305 --
3306 l_rndg_fctr:=100;
3307 l_rndg_type:='UP';
3308 --
3309 elsif p_rounding_cd = 'RUTNFHND' then
3310 --
3311 l_rndg_fctr:=500;
3312 l_rndg_type:='UP';
3313 --
3314 elsif p_rounding_cd = 'RUTNTHO' then
3315 --
3316 l_rndg_fctr:=1000;
3317 l_rndg_type:='UP';
3318 --
3319 elsif p_rounding_cd = 'RUTNFTHO' then
3320 --
3321 l_rndg_fctr:=5000;
3322 l_rndg_type:='UP';
3323 --
3324 elsif p_rounding_cd = 'RTNRTHTH' then
3325 --
3326 l_rndg_fctr:=.001;
3327 ---bug 2083228
3328 if p_value < 0 then
3329 l_value := p_value * -1 ;
3330 l_val_chg_flag := 'Y' ;
3331 end if ;
3332 --
3333 elsif p_rounding_cd = 'RTNRHNTH' then
3334 --
3335 l_rndg_fctr:=.01;
3336 ---bug 2083228
3337 if p_value < 0 then
3338 l_value := p_value * -1 ;
3339 l_val_chg_flag := 'Y' ;
3340 end if ;
3341 --
3342 elsif p_rounding_cd = 'RTNRTNTH' then
3343 --
3344 l_rndg_fctr:=.1;
3345 ---bug 2083228
3346 if p_value < 0 then
3347 l_value := p_value * -1 ;
3348 l_val_chg_flag := 'Y' ;
3349 end if ;
3350
3351 --
3352 elsif p_rounding_cd = 'RTNRONE' then
3353 --
3354 l_rndg_fctr:=1;
3355 --
3356 elsif p_rounding_cd = 'RTNRTEN' then
3357 --
3358 l_rndg_fctr:=10;
3359 --
3360 elsif p_rounding_cd = 'RTNRHND' then
3361 --
3362 l_rndg_fctr:=100;
3363 --
3364 elsif p_rounding_cd = 'RTNRTHO' then
3365 --
3366 l_rndg_fctr:=1000;
3367 --
3368 elsif p_rounding_cd = 'RTNRTTHO' then
3369 --
3370 l_rndg_fctr:=10000;
3371 --
3372 elsif p_rounding_cd = 'RTNRHTHO' then
3373 --
3374 l_rndg_fctr:=100000;
3375 --
3376 elsif p_rounding_cd = 'RTNRMLN' then
3377 --
3378 l_rndg_fctr:=1000000;
3379 --
3380 elsif p_rounding_cd = 'RDTNONE' then
3381 --
3382 l_rndg_fctr:=1;
3383 l_rndg_type:='DOWN';
3384 --
3385 else
3386 --
3387 -- Defensive programming
3388 --
3389 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
3390 fnd_message.set_token('PROC',l_proc);
3391 fnd_message.set_token('CODE1',p_rounding_cd);
3392 raise ben_manage_life_events.g_record_error;
3393 --
3394 end if;
3395 --
3396 l_modulus := mod(l_value,l_rndg_fctr);
3397 --
3398 if l_modulus <> 0 then
3399 --
3400 if l_rndg_type='UP' then
3401 --
3402 l_result:=l_value-l_modulus+l_rndg_fctr;
3403 --
3404 elsif l_rndg_type = 'DOWN' then
3405 --
3406 hr_utility.set_location('p_value:'||to_char(l_value), 5);
3407 hr_utility.set_location('l_modulus:'||to_char(l_modulus), 5);
3408 l_result:=l_value-l_modulus;
3409 hr_utility.set_location('l_result:'||l_result, 5);
3410 --
3411 else
3412 --
3413 -- go for nearest
3414 --
3415 if l_modulus >= (l_rndg_fctr/2) then
3416 --
3417 l_result:=l_value-l_modulus+l_rndg_fctr;
3418 --
3419 else
3420 --
3421 l_result:=l_value-l_modulus;
3422 --
3423 end if;
3424 --
3425 end if;
3426 --
3427 else
3428 --
3429 l_result:=l_value;
3430 --
3431 end if;
3432 --
3433 end if;
3434
3435 ---bug 2083228
3436 if l_val_chg_flag = 'Y' then
3437 l_result:=l_result * -1 ;
3438 hr_utility.set_location(' negetive value ' || l_result, 199 );
3439 end if ;
3440 --
3441 hr_utility.set_location('Leaving:'||l_proc, 99);
3442 return l_result;
3443 --
3444 end do_rounding;
3445
3446 ------------------------------------------------------------------------
3447 -- derive_date
3448 ------------------------------------------------------------------------
3449 function derive_date(p_date in date,
3450 p_uom in varchar2,
3451 p_min in number,
3452 p_max in number,
3453 p_value in varchar2,
3454 p_decimal_level in varchar2 ) return date is
3455 --
3456 l_proc varchar2(72) := 'benutils.derive_date';
3457 l_value date;
3458 l_val number;
3459 l_fractional number;
3460 --
3461 begin
3462 --
3463 hr_utility.set_location('Entering:'||l_proc, 5);
3464 hr_utility.set_location(' P_VALUE ' || p_value, 926);
3465 hr_utility.set_location(' P_max ' || p_max, 926);
3466 if p_value = 'LT_MIN' or
3467 p_value = 'GT_MIN' then
3468 --
3469 l_val := p_min;
3470 --
3471 /**
3472 else
3473 --
3474 --bug : 1743925 if the call is for decide le_evt_date, whic is create because of
3475 -- breach of max_min then apply the max + 1<
3476 if p_value is not null then
3477 l_val := ceil(p_max + 0.001) ;
3478 if p_decimal_level = 'Y' then
3479 -- whne there is deimal and decima level is controlled
3480 -- add decimal maximum
3481 if round( p_max,0) <> p_max then
3482 l_val := (( p_max * 100) + 1 ) / 100 ;
3483 end if ;
3484 end if ;
3485 else
3486 l_val := p_max;
3487 end if ;
3488 --
3489 end if;
3490 */
3491 elsif p_value = 'GT_MAX' then
3492 --
3493 if ( p_decimal_level = 'Y'
3494 OR p_min <> trunc(p_min)
3495 OR p_max <> trunc(p_max) ) then
3496 --
3497 l_val := p_max + 0.000000001 ;
3498 --
3499 else
3500 --
3501 l_val := p_max + 1 ;
3502 --
3503 end if;
3504 --
3505 else
3506 --
3507 l_val := p_max ;
3508 --
3509 end if;
3510 --
3511 hr_utility.set_location( p_value||' '||l_val, 926);
3512 if p_uom = 'YR' then
3513 --
3514 l_value := add_months(p_date,l_val*12);
3515 --bug#4156125 - the add_months function takes only integer to add months
3516 --so the fraction of a month needs to be converted as days and added
3517 if l_val <> trunc(l_val) then
3518 --
3519 l_fractional := (l_val * 12) - trunc(l_val * 12);
3520 -- l_value := l_value + ceil((l_fractional * 365));
3521 -- Bug 5499177
3522 l_value := l_value + ceil((l_fractional * to_number(to_char(last_day(l_value), 'DD'))));
3523 --
3524 end if;
3525 --
3526 elsif p_uom = 'MO' then
3527 --
3528 l_value := add_months(p_date,l_val);
3529 --
3530 elsif p_uom = 'QTR' then
3531 --
3532 l_value := add_months(p_date,l_val*3);
3533 --
3534 elsif p_uom = 'WK' then
3535 --
3536 l_value := p_date+(l_val*7);
3537 --
3538 elsif p_uom = 'DY' then
3539 --
3540 l_value := p_date+l_val;
3541 --
3542 else
3543 --
3544 -- Defensive programming
3545 --
3546 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
3547 fnd_message.set_token('PROC',l_proc);
3548 fnd_message.set_token('CODE1',p_uom);
3549 raise ben_manage_life_events.g_record_error;
3550 --
3551 end if;
3552 --
3553 hr_utility.set_location(' calcualted return ' || l_value, 926);
3554 hr_utility.set_location('Leaving:'||l_proc, 99);
3555 return l_value;
3556 --
3557 end derive_date;
3558
3559 ------------------------------------------------------------------------
3560 -- do_uom
3561 ------------------------------------------------------------------------
3562 function do_uom(p_date1 in date,
3563 p_date2 in date,
3564 p_uom in varchar2) return number is
3565 --
3566 l_value number;
3567 l_proc varchar2(72) := 'benutils.do_uom';
3568 --
3569 begin
3570 --
3571 hr_utility.set_location('Entering:'||l_proc, 5);
3572 --
3573 -- Calculate details based on UOM
3574 --
3575 -- All values expressed as days
3576 --
3577 if p_uom = 'YR' then
3578 --
3579 l_value := months_between(p_date1,p_date2) / 12;
3580 --
3581 elsif p_uom = 'MO' then
3582 --
3583 l_value := months_between(p_date1,p_date2);
3584 --
3585 -- months_between fails when calculated between 29th Jan,30th Jan AND 28th Feb,
3586 -- for months_between('28-Feb-RRRR','28-Jan-RRRR') it gives 1 but for months_between('28-Feb-RRRR','29/30-Jan-RRRR')
3587 -- it gives < 1 and again as per functionality of months_between for months_between('28-Feb-RRRR','31-Jan-RRRR') it gives 1.
3588 -- So code is made to work for this specific case.
3589 --Bug 5931412
3590 if substr(to_char(p_date1,'DD-MON-YYYY'),4,3) = 'FEB'
3591 and substr(to_char(p_date2,'DD-MON-YYYY'),1,2) > '28'
3592 and substr(to_char(p_date1,'DD-MON-YYYY'),1,2) in ('28','29') then
3593 --
3594 l_value := ceil(l_value);
3595 --
3596 end if;
3597 --
3598 --Bug 5931412
3599 elsif p_uom = 'QTR' then
3600 --
3601 l_value := months_between(p_date1,p_date2) / 4;
3602 --
3603 elsif p_uom = 'WK' then
3604 --
3605 l_value := to_number(p_date1 - p_date2) / 7;
3606 --
3607 elsif p_uom = 'DY' then
3608 --
3609 l_value := to_number(p_date1 - p_date2);
3610 --
3611 else
3612 --
3613 -- Defensive programming
3614 --
3615 hr_utility.set_location('BEN_91342_UNKNOWN_CODE_1', 99);
3616 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
3617 fnd_message.set_token('PROC',l_proc);
3618 fnd_message.set_token('CODE1',p_uom);
3619 raise ben_manage_life_events.g_record_error;
3620 --
3621 end if;
3622 --
3623 hr_utility.set_location('Leaving:'||l_proc, 99);
3624 return l_value;
3625 --
3626 end do_uom;
3627
3628 ------------------------------------------------------------------------
3629 -- id
3630 ------------------------------------------------------------------------
3631 function id(p_value in number) return varchar2 is
3632 --
3633 l_value varchar2(30);
3634 l_proc varchar2(80) := 'benutils.id';
3635 --
3636 begin
3637 --
3638 hr_utility.set_location('Entering:'||l_proc, 5);
3639 --
3640 if p_value is null then
3641 --
3642 hr_utility.set_location('Leaving:'||l_proc, 3);
3643 return null;
3644 --
3645 end if;
3646 --
3647 l_value := ' ('||p_value||')';
3648 --
3649 hr_utility.set_location('Leaving:'||l_proc, 5);
3650 return l_value;
3651 --
3652 end id;
3653
3654 ------------------------------------------------------------------------
3655 -- min_max_breach
3656 -- This function returns the paramter p_break ( GT_MIN, LT_MIN, GT_MAX, LT_MAX )
3657 -- which tells the actual boundary crossing.
3658 -- returns true only when there is a breach.
3659 ------------------------------------------------------------------------
3660 function min_max_breach(p_min_value in number,
3661 p_max_value in number,
3662 p_old_value in number,
3663 p_new_value in number,
3664 p_break out nocopy varchar2,
3665 p_decimal_level in varchar2 ) return boolean is
3666 --
3667 l_package varchar2(80) := 'benutils.min_max_breach';
3668 --
3669 l_return boolean := false;
3670 l_min_value number;
3671 l_max_value number;
3672 l_old_value number := p_old_value ;
3673 l_new_value number := p_new_value ;
3674 --
3675 begin
3676 --
3677 -- hr_utility.set_location ('Entering '||l_package,10);
3678 -- hr_utility.set_location ('Min '||p_min_value,10);
3679 -- hr_utility.set_location ('Max '||p_max_value,10);
3680 -- hr_utility.set_location ('Old Value '||p_old_value,10);
3681 -- hr_utility.set_location ('New Value '||p_new_value,10);
3682 --
3683 p_break := 'NONE';
3684 --
3685 if p_min_value is null then
3686 --
3687 l_min_value := 0;
3688 --
3689 else
3690 --
3691 l_min_value := p_min_value;
3692 --
3693 end if;
3694 --
3695 if p_max_value is null then
3696 --
3697 l_max_value := 999999999;
3698 --
3699 else
3700 --
3701 l_max_value := p_max_value;
3702 --
3703 end if;
3704 --- bug : 1540610
3705 --- if 1 to 20 it should contol 1 to 20.99
3706 --- if .1 to .2 it shound control .1 to .29
3707 /**
3708 if p_decimal_level = 'Y' then
3709 if round(l_max_value,0) <> l_max_value then
3710 l_max_value := ( l_max_value * 100) + 1 ;
3711 l_min_value := ( l_min_value * 100) ;
3712 l_old_value := ( nvl(l_old_value,0) * 100) ;
3713 l_new_value := ( nvl(l_new_value,0) * 100) ;
3714 else
3715 l_max_value := ceil(l_max_value + 0.001) ;
3716 end if ;
3717 else
3718 l_max_value := ceil(l_max_value + 0.001) ;
3719 end if ;
3720 */
3721 --Bug 2101937 Assumption here is, if the user uses a decimal value in his min/max definition,
3722 --he has to use proper rounding code to round the value to the appropriately to get it in their
3723 --desired range.
3724 --- if 1 to 20 it should control 1 to < 21
3725 --- if .1 to .2 it shound control .1 to .2 ONLY
3726 -- if the old or new value is .225, then the rounding code should get the value in the
3727 -- appropriate band.
3728 --
3729 -- Bug 239011: Corrected typo, comparing l_min_value with trunc(l_min_value)
3730 --
3731 if ( p_decimal_level = 'Y'
3732 OR l_max_value <> trunc(l_max_value)
3733 OR l_min_value <> trunc(l_min_value) ) then
3734 --
3735 l_max_value := l_max_value + 0.000000001 ;
3736 --
3737 else
3738 --
3739 l_max_value := l_max_value + 1 ;
3740 --
3741 end if;
3742
3743 --
3744 if p_old_value = p_new_value then
3745 --
3746 -- Values are same, so no boundary is crossed.
3747 --
3748 p_break := 'NONE';
3749 l_return := false;
3750 --
3751 elsif p_new_value is null then
3752 --
3753 p_break := 'NONE';
3754 l_return := false;
3755 --
3756 elsif p_old_value is null then
3757 --
3758 -- (maagrawa 12/20/99 Bug 4140) No breach when the old value is null.
3759 --
3760 p_break := 'NONE';
3761 l_return := false;
3762 --
3763 elsif l_old_value >= l_min_value and l_old_value < l_max_value then
3764 --
3765 -- Old value lies in the range.
3766 --
3767 if l_new_value >= l_min_value and l_new_value < l_max_value then
3768 --
3769 -- New value also in the range, so no crossing.
3770 --
3771 p_break := 'NONE';
3772 l_return := false;
3773 --
3774 elsif l_new_value >= l_max_value then
3775 --
3776 -- New value has crossed the maximum value.
3777 --
3778 p_break := 'GT_MAX';
3779 l_return := true;
3780 --
3781 elsif l_new_value < l_min_value then
3782 --
3783 -- New value has gone below the minimum, so minimum value crossing.
3784 --
3785 p_break := 'LT_MIN';
3786 l_return := true;
3787 --
3788 end if;
3789 --
3790 elsif l_old_value >= l_max_value then
3791 --
3792 -- Old value is above the maximum.
3793 --
3794 if l_new_value >= l_min_value and l_new_value < l_max_value then
3795 --
3796 -- New value returns within range, so maximum border is crossed.
3797 --
3798 p_break := 'LT_MAX';
3799 l_return := true;
3800 --
3801 else
3802 --
3803 -- Still not in range.
3804 --
3805 p_break := 'NONE';
3806 l_return := false;
3807 --
3808 end if;
3809 --
3810 elsif l_old_value < l_min_value then
3811 --
3812 -- Old value is below the minimum value.
3813 --
3814 if l_new_value >= l_min_value and l_new_value < l_max_value then
3815 --
3816 -- New value is in range, so we have crossed the minimum border.
3817 --
3818 p_break := 'GT_MIN';
3819 l_return := true;
3820 --
3821 else
3822 --
3823 -- Still not in range, so no crossing.
3824 --
3825 p_break := 'NONE';
3826 l_return := false;
3827 --
3828 end if;
3829 --
3830 end if;
3831 --
3832 -- hr_utility.set_location ('Leaving '||l_package,10);
3833 --
3834 return l_return;
3835 --
3836 end min_max_breach;
3837
3838 ------------------------------------------------------------------------
3839 -- eot_to_null
3840 ------------------------------------------------------------------------
3841 function eot_to_null(p_date in date) return date is
3842 --
3843 l_date date := null;
3844 l_package varchar2(80) := 'benutils.eot_to_null';
3845 --
3846 begin
3847 --
3848 hr_utility.set_location ('Entering '||l_package,10);
3849 --
3850 if p_date = hr_api.g_eot then
3851 --
3852 l_date := null;
3853 --
3854 else
3855 --
3856 l_date := p_date;
3857 --
3858 end if;
3859 --
3860 hr_utility.set_location ('Leaving '||l_package,10);
3861 return(l_date);
3862 --
3863 end eot_to_null;
3864 --
3865 function eot_to_null(p_date in varchar2) return varchar2 is
3866 --
3867 l_date date := null;
3868 l_package varchar2(80) := 'benutils.eot_to_null';
3869 --
3870 begin
3871 --
3872 hr_utility.set_location ('Entering '||l_package,5);
3873 --
3874 l_date := benutils.eot_to_null(to_date(p_date,'DD/MM/YYYY'));
3875 --
3876 hr_utility.set_location ('Leaving '||l_package,5);
3877 return(to_char(l_date,'DD/MM/YYYY'));
3878 --
3879 end eot_to_null;
3880 --
3881 --
3882 function get_message_name return varchar2 is
3883 --
3884 l_message varchar2(600);
3885 l_message_name varchar2(240);
3886 l_app_name varchar2(240);
3887 --
3888 begin
3889 --
3890 l_message := fnd_message.get_encoded;
3891 fnd_message.set_encoded(l_message);
3892 --
3893 fnd_message.parse_encoded(encoded_message => l_message,
3894 app_short_name => l_app_name,
3895 message_name => l_message_name);
3896 --
3897 return(l_message_name);
3898 --
3899 end get_message_name;
3900 --
3901 ------------------------------------------------------------------------
3902 -- set_to_oct1_prev_year
3903 ------------------------------------------------------------------------
3904 function set_to_oct1_prev_year(p_date in date) return date is
3905 --
3906 l_package varchar2(80) := 'set_to_oct1_prev_year';
3907 l_date date;
3908 l_months number := 12;
3909 --
3910 begin
3911 --
3912 hr_utility.set_location ('Entering '||l_package,10);
3913 --
3914 -- Set to prev year
3915 --
3916 l_months := to_number(to_char(p_date,'MM'))+3;
3917 --
3918 l_date := add_months(p_date,-l_months);
3919 --
3920 -- Set to first of month of October
3921 --
3922 l_date := last_day(l_date)+1;
3923 --
3924 hr_utility.set_location ('Leaving '||l_package,10);
3925 return l_date;
3926 --
3927 end set_to_oct1_prev_year;
3928
3929 ------------------------------------------------------------------------
3930 -- formula
3931 ------------------------------------------------------------------------
3932 function formula(p_formula_id in number,
3933 p_business_group_id in number ,
3934 p_payroll_id in number ,
3935 p_payroll_action_id in number ,
3936 p_assignment_id in number ,
3937 p_assignment_action_id in number ,
3938 p_org_pay_method_id in number ,
3939 p_per_pay_method_id in number ,
3940 p_organization_id in number ,
3941 p_tax_unit_id in number ,
3942 p_jurisdiction_code in varchar2 ,
3943 p_balance_date in date ,
3944 p_element_entry_id in number ,
3945 p_element_type_id in number ,
3946 p_original_entry_id in number ,
3947 p_tax_group in number ,
3948 p_pgm_id in number ,
3949 p_pl_id in number ,
3950 p_pl_typ_id in number ,
3951 p_opt_id in number ,
3952 p_ler_id in number ,
3953 p_communication_type_id in number ,
3954 p_action_type_id in number ,
3955 p_acty_base_rt_id in number ,
3956 p_elig_per_elctbl_chc_id in number ,
3957 p_enrt_bnft_id in number ,
3958 p_regn_id in number ,
3959 p_rptg_grp_id in number ,
3960 p_cm_dlvry_mthd_cd in varchar2 ,
3961 p_crt_ordr_typ_cd in varchar2 ,
3962 p_enrt_ctfn_typ_cd in varchar2 ,
3963 p_bnfts_bal_id in number ,
3964 p_elig_per_id in number ,
3965 p_per_cm_id in number ,
3966 p_prtt_enrt_actn_id in number ,
3967 p_effective_date in date,
3968 p_param1 in varchar2 ,
3969 p_param1_value in varchar2 ,
3970 p_param2 in varchar2 ,
3971 p_param2_value in varchar2 ,
3972 p_param3 in varchar2 ,
3973 p_param3_value in varchar2 ,
3974 p_param4 in varchar2 ,
3975 p_param4_value in varchar2 ,
3976 p_param5 in varchar2 ,
3977 p_param5_value in varchar2 ,
3978 p_param6 in varchar2 ,
3979 p_param6_value in varchar2 ,
3980 p_param7 in varchar2 ,
3981 p_param7_value in varchar2 ,
3982 p_param8 in varchar2 ,
3983 p_param8_value in varchar2 ,
3984 p_param9 in varchar2 ,
3985 p_param9_value in varchar2 ,
3986 p_param10 in varchar2 ,
3987 p_param10_value in varchar2 ,
3988 p_param11 in varchar2 ,
3989 p_param11_value in varchar2 ,
3990 p_param12 in varchar2 ,
3991 p_param12_value in varchar2 ,
3992 p_param13 in varchar2 ,
3993 p_param13_value in varchar2 ,
3994 p_param14 in varchar2 ,
3995 p_param14_value in varchar2 ,
3996 p_param15 in varchar2 ,
3997 p_param15_value in varchar2 ,
3998 p_param16 in varchar2 ,
3999 p_param16_value in varchar2 ,
4000 p_param17 in varchar2 ,
4001 p_param17_value in varchar2 ,
4002 p_param18 in varchar2 ,
4003 p_param18_value in varchar2 ,
4004 p_param19 in varchar2 ,
4005 p_param19_value in varchar2 ,
4006 p_param20 in varchar2 ,
4007 p_param20_value in varchar2 ,
4008 p_param21 in varchar2 ,
4009 p_param21_value in varchar2 ,
4010 p_param22 in varchar2 ,
4011 p_param22_value in varchar2 ,
4012 p_param23 in varchar2 ,
4013 p_param23_value in varchar2 ,
4014 p_param24 in varchar2 ,
4015 p_param24_value in varchar2 ,
4016 p_param25 in varchar2 ,
4017 p_param25_value in varchar2 ,
4018 p_param26 in varchar2 ,
4019 p_param26_value in varchar2 ,
4020 p_param27 in varchar2 ,
4021 p_param27_value in varchar2 ,
4022 p_param28 in varchar2 ,
4023 p_param28_value in varchar2 ,
4024 p_param29 in varchar2 ,
4025 p_param29_value in varchar2 ,
4026 p_param30 in varchar2 ,
4027 p_param30_value in varchar2 ,
4028 p_param31 in varchar2 ,
4029 p_param31_value in varchar2 ,
4030 p_param32 in varchar2 ,
4031 p_param32_value in varchar2 ,
4032 p_param33 in varchar2 ,
4033 p_param33_value in varchar2 ,
4034 p_param34 in varchar2 ,
4035 p_param34_value in varchar2 ,
4036 p_param35 in varchar2 ,
4037 p_param35_value in varchar2 ,
4038 p_param_tab in ff_exec.outputs_t
4039 )
4040 return ff_exec.outputs_t is
4041 --
4042 l_package varchar2(80) := 'formula';
4043 l_inputs ff_exec.inputs_t;
4044 l_outputs ff_exec.outputs_t;
4045 j int;
4046 l_param_tab_count number;
4047 --
4048 -- Bug 1949361 : Jurisdiction code should be fetched only for
4049 -- US legislation code.
4050 --
4051 cursor c_asg is
4052 select asg.assignment_id,asg.organization_id,loc.region_2
4053 from per_all_assignments_f asg,hr_locations_all loc
4054 where asg.assignment_id = p_assignment_id
4055 and asg.primary_flag = 'Y'
4056 and asg.location_id = loc.location_id(+)
4057 and p_effective_date
4058 between asg.effective_start_date
4059 and asg.effective_end_date;
4060 --
4061 l_asg c_asg%rowtype;
4062 --
4063 /* cursor c_leg is
4064 select bg.legislation_code
4065 from per_business_groups bg
4066 where bg.business_group_id = p_business_group_id;
4067 */
4068 --
4069 cursor c_leg is
4070 SELECT O3.ORG_INFORMATION9
4071 FROM HR_ALL_ORGANIZATION_UNITS O ,
4072 HR_ORGANIZATION_INFORMATION O3
4073 where O.ORGANIZATION_ID = O3.ORGANIZATION_ID
4074 and O3.ORG_INFORMATION_CONTEXT = 'Business Group Information'
4075 and o.ORGANIZATION_ID = p_business_group_id
4076 and o.business_group_id = p_business_group_id;
4077 --
4078 l_legislation_code varchar2(150);
4079 l_jurisdiction_code varchar2(150);
4080 --
4081 begin
4082 --
4083 -- hr_utility.set_location ('Entering '||l_package,10);
4084 --
4085 -- hr_utility.set_location ('Before Init Formula '||l_package,10);
4086 --
4087 -- Bug 1949361 : Get the jurisdiction code for US legislation only.
4088 --
4089 open c_leg;
4090 fetch c_leg into l_legislation_code;
4091 close c_leg;
4092 --
4093 --
4094 -- Enhancement only do this if they are in US using vertex validation
4095 -- for addresses.
4096 -- allows US business group to be used for a Global instance
4097 --
4098 if l_legislation_code = 'US' then
4099 if hr_general.chk_maintain_tax_records = 'Y' then
4100 --
4101 open c_asg;
4102 fetch c_asg into l_asg;
4103 close c_asg;
4104 --
4105 if l_asg.region_2 is not null then
4106
4107 l_jurisdiction_code :=
4108 pay_mag_utils.lookup_jurisdiction_code
4109 (p_state => l_asg.region_2);
4110
4111 end if; -- region 2 check
4112 --
4113 end if; -- Tax records check
4114 --
4115 end if; -- US Legislation check
4116 ff_exec.init_formula
4117 (p_formula_id => p_formula_id,
4118 p_effective_date => p_effective_date,
4119 p_inputs => l_inputs,
4120 p_outputs => l_outputs);
4121 -- hr_utility.set_location ('After Init Formula '||l_package,10);
4122 --
4123 -- NOTE that we use special parameter values in order to state which
4124 -- array locations we put the values into, this is because of the caching
4125 -- mechanism that formula uses.
4126 --
4127 -- hr_utility.set_location ('First Position'||l_inputs.first,10);
4128 -- hr_utility.set_location ('Last Position'||l_inputs.last,10);
4129 l_param_tab_count := p_param_tab.count;
4130 --
4131 -- Account for case where formula has no contexts or inputs
4132 --
4133 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
4134 --
4135 -- hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
4136 --
4137 if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
4138 --
4139 l_inputs(l_count).value := nvl(p_business_group_id, -1);
4140 --
4141 elsif l_inputs(l_count).name = 'PAYROLL_ID' then
4142 --
4143 l_inputs(l_count).value := nvl(p_bnfts_bal_id, nvl(p_rptg_grp_id, nvl(p_payroll_id,-1)));
4144 --
4145 elsif l_inputs(l_count).name = 'PAYROLL_ACTION_ID' then
4146 --
4147 l_inputs(l_count).value := nvl(p_acty_base_rt_id, nvl(p_payroll_action_id, -1));
4148 --
4149 elsif l_inputs(l_count).name = 'ASSIGNMENT_ID' then
4150 --
4151 l_inputs(l_count).value := nvl(p_assignment_id, -1);
4152 --
4153 elsif l_inputs(l_count).name = 'ASSIGNMENT_ACTION_ID' then
4154 --
4155 l_inputs(l_count).value := nvl(p_assignment_action_id, -1);
4156 --
4157 elsif l_inputs(l_count).name = 'ORG_PAY_METHOD_ID' then
4158 --
4159 l_inputs(l_count).value := nvl(p_per_cm_id,nvl(p_prtt_enrt_actn_id, nvl(p_enrt_bnft_id, nvl(p_org_pay_method_id, -1))));
4160 --
4161 elsif l_inputs(l_count).name = 'PER_PAY_METHOD_ID' then
4162 --
4163 l_inputs(l_count).value := nvl(p_elig_per_id, nvl(p_regn_id, nvl(p_per_pay_method_id, -1)));
4164 --
4165 elsif l_inputs(l_count).name = 'ORGANIZATION_ID' then
4166 --
4167 l_inputs(l_count).value := nvl(p_elig_per_elctbl_chc_id, nvl(p_organization_id, -1));
4168 --
4169 elsif l_inputs(l_count).name = 'TAX_UNIT_ID' then
4170 --
4171 l_inputs(l_count).value := nvl(p_tax_unit_id, -1);
4172 --
4173 elsif l_inputs(l_count).name = 'JURISDICTION_CODE' then
4174 --
4175 l_inputs(l_count).value := nvl(p_cm_dlvry_mthd_cd, nvl(p_crt_ordr_typ_cd,nvl(l_jurisdiction_code, 'xx')));
4176 --
4177 elsif l_inputs(l_count).name = 'SOURCE_TEXT' then
4178 --
4179 l_inputs(l_count).value := nvl(p_enrt_ctfn_typ_cd, 'xx');
4180 --
4181 elsif l_inputs(l_count).name = 'BALANCE_DATE' then
4182 --
4183 l_inputs(l_count).value := fnd_date.date_to_canonical(p_balance_date);
4184 --
4185 elsif l_inputs(l_count).name = 'ELEMENT_ENTRY_ID' then
4186 --
4187 l_inputs(l_count).value := nvl(p_element_entry_id, -1);
4188 --
4189 elsif l_inputs(l_count).name = 'ORIGINAL_ENTRY_ID' then
4190 --
4191 l_inputs(l_count).value := nvl(p_original_entry_id, -1);
4192 --
4193 elsif l_inputs(l_count).name = 'TAX_GROUP' then
4194 --
4195 l_inputs(l_count).value := p_tax_group;
4196 --
4197 elsif l_inputs(l_count).name = 'PGM_ID' then
4198 --
4199 l_inputs(l_count).value := nvl(p_pgm_id,-1);
4200 --
4201 elsif l_inputs(l_count).name = 'PL_ID' then
4202 --
4203 l_inputs(l_count).value := nvl(p_pl_id,-1);
4204 --
4205 elsif l_inputs(l_count).name = 'PL_TYP_ID' then
4206 --
4207 l_inputs(l_count).value := nvl(p_pl_typ_id,-1);
4208 --
4209 elsif l_inputs(l_count).name = 'OPT_ID' then
4210 --
4211 l_inputs(l_count).value := nvl(p_opt_id,-1);
4212 --
4213 elsif l_inputs(l_count).name = 'LER_ID' then
4214 --
4215 l_inputs(l_count).value := nvl(p_ler_id,-1);
4216 --
4217 elsif l_inputs(l_count).name = 'COMM_TYP_ID' then
4218 --
4219 l_inputs(l_count).value := nvl(p_communication_type_id,-1);
4220 --
4221 elsif l_inputs(l_count).name = 'ACT_TYP_ID' then
4222 --
4223 l_inputs(l_count).value := nvl(p_action_type_id,-1);
4224 --
4225 elsif l_inputs(l_count).name = 'DATE_EARNED' then
4226 --
4227 -- Note that you must pass the date as a string, that is because
4228 -- of the canonical date change of 11.5
4229 --
4230 -- hr_utility.set_location ('Date Earned '||to_char(p_effective_date),10);
4231 -- Still the fast formula does't accept the full canonical form.
4232 -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
4233 l_inputs(l_count).value := to_char(p_effective_date, 'YYYY/MM/DD');
4234 --
4235 -- Bug 6676772
4236 /* elsif l_param_tab_count >0 then
4237 for j in 1..l_param_tab_count
4238 loop
4239 if l_inputs(l_count).name = p_param_tab(j).name then
4240 l_inputs(l_count).value := p_param_tab(j).value;
4241 exit;
4242 end if;
4243 end loop;*/
4244 -- Bug 6676772
4245 elsif l_inputs(l_count).name = p_param1 then
4246 --
4247 l_inputs(l_count).value := p_param1_value;
4248 --
4249 elsif l_inputs(l_count).name = p_param2 then
4250 --
4251 l_inputs(l_count).value := p_param2_value;
4252 --
4253 elsif l_inputs(l_count).name = p_param3 then
4254 --
4255 l_inputs(l_count).value := p_param3_value;
4256 --
4257 elsif l_inputs(l_count).name = p_param4 then
4258 --
4259 l_inputs(l_count).value := p_param4_value;
4260 --
4261 elsif l_inputs(l_count).name = p_param5 then
4262 --
4263 l_inputs(l_count).value := p_param5_value;
4264 --
4265 elsif l_inputs(l_count).name = p_param6 then
4266 --
4267 l_inputs(l_count).value := p_param6_value;
4268 --
4269 elsif l_inputs(l_count).name = p_param7 then
4270 --
4271 l_inputs(l_count).value := p_param7_value;
4272 --
4273 elsif l_inputs(l_count).name = p_param8 then
4274 --
4275 l_inputs(l_count).value := p_param8_value;
4276 --
4277 elsif l_inputs(l_count).name = p_param9 then
4278 --
4279 l_inputs(l_count).value := p_param9_value;
4280 --
4281 elsif l_inputs(l_count).name = p_param10 then
4282 --
4283 l_inputs(l_count).value := p_param10_value;
4284 --
4285 elsif l_inputs(l_count).name = p_param11 then
4286 --
4287 l_inputs(l_count).value := p_param11_value;
4288 --
4289 elsif l_inputs(l_count).name = p_param12 then
4290 --
4291 l_inputs(l_count).value := p_param12_value;
4292 --
4293 elsif l_inputs(l_count).name = p_param13 then
4294 --
4295 l_inputs(l_count).value := p_param13_value;
4296 --
4297 elsif l_inputs(l_count).name = p_param14 then
4298 --
4299 l_inputs(l_count).value := p_param14_value;
4300 --
4301 elsif l_inputs(l_count).name = p_param15 then
4302 --
4303 l_inputs(l_count).value := p_param15_value;
4304 --
4305 elsif l_inputs(l_count).name = p_param16 then
4306 --
4307 l_inputs(l_count).value := p_param16_value;
4308 --
4309 elsif l_inputs(l_count).name = p_param17 then
4310 --
4311 l_inputs(l_count).value := p_param17_value;
4312 --
4313 elsif l_inputs(l_count).name = p_param18 then
4314 --
4315 l_inputs(l_count).value := p_param18_value;
4316 --
4317 elsif l_inputs(l_count).name = p_param19 then
4318 --
4319 l_inputs(l_count).value := p_param19_value;
4320 --
4321 elsif l_inputs(l_count).name = p_param20 then
4322 --
4323 l_inputs(l_count).value := p_param20_value;
4324 --
4325 elsif l_inputs(l_count).name = p_param21 then
4326 --
4327 l_inputs(l_count).value := p_param21_value;
4328 --
4329 elsif l_inputs(l_count).name = p_param22 then
4330 --
4331 l_inputs(l_count).value := p_param22_value;
4332 --
4333 elsif l_inputs(l_count).name = p_param23 then
4334 --
4335 l_inputs(l_count).value := p_param23_value;
4336 --
4337 elsif l_inputs(l_count).name = p_param24 then
4338 --
4339 l_inputs(l_count).value := p_param24_value;
4340 --
4341 elsif l_inputs(l_count).name = p_param25 then
4342 --
4343 l_inputs(l_count).value := p_param25_value;
4344 --
4345 elsif l_inputs(l_count).name = p_param26 then
4346 --
4347 l_inputs(l_count).value := p_param26_value;
4348 --
4349 elsif l_inputs(l_count).name = p_param27 then
4350 --
4351 l_inputs(l_count).value := p_param27_value;
4352 --
4353 elsif l_inputs(l_count).name = p_param28 then
4354 --
4355 l_inputs(l_count).value := p_param28_value;
4356 --
4357 elsif l_inputs(l_count).name = p_param29 then
4358 --
4359 l_inputs(l_count).value := p_param29_value;
4360 --
4361 elsif l_inputs(l_count).name = p_param30 then
4362 --
4363 l_inputs(l_count).value := p_param30_value;
4364 --
4365 elsif l_inputs(l_count).name = p_param31 then
4366 --
4367 l_inputs(l_count).value := p_param31_value;
4368 --
4369 elsif l_inputs(l_count).name = p_param32 then
4370 --
4371 l_inputs(l_count).value := p_param32_value;
4372 --
4373 elsif l_inputs(l_count).name = p_param33 then
4374 --
4375 l_inputs(l_count).value := p_param33_value;
4376 --
4377 elsif l_inputs(l_count).name = p_param34 then
4378 --
4379 l_inputs(l_count).value := p_param34_value;
4380 --
4381 elsif l_inputs(l_count).name = p_param35 then
4382 --
4383 l_inputs(l_count).value := p_param35_value;
4384 --
4385 -- Bug 6676772
4386 elsif l_param_tab_count >0 then
4387 for j in 1..l_param_tab_count
4388 loop
4389 if l_inputs(l_count).name = p_param_tab(j).name then
4390 l_inputs(l_count).value := p_param_tab(j).value;
4391 exit;
4392 end if;
4393 end loop;
4394 -- Bug 6676772
4395 --
4396 end if;
4397 --
4398 end loop;
4399 --
4400 -- Ok we have loaded the input record now run the formula.
4401 --
4402 ff_exec.run_formula(p_inputs => l_inputs,
4403 p_outputs => l_outputs,
4404 p_use_dbi_cache => false); -- bug# 2430017
4405 --
4406 -- hr_utility.set_location ('Leaving '||l_package,10);
4407 return l_outputs;
4408 --
4409 end formula;
4410 --
4411 -- This procedure is used to execute the rule : per_info_chg_cs_ler_rl
4412 -- This procedure is called from the trigger packages like
4413 -- ben_add_ler.
4414 --
4415 procedure exec_rule(
4416 p_formula_id in number,
4417 p_effective_date in date,
4418 p_lf_evt_ocrd_dt in date,
4419 p_business_group_id in number,
4420 p_person_id in number ,
4421 p_new_value in varchar2 ,
4422 p_old_value in varchar2 ,
4423 p_column_name in varchar2 ,
4424 p_pk_id in varchar2 ,
4425 p_param5 in varchar2 ,
4426 p_param5_value in varchar2 ,
4427 p_param6 in varchar2 ,
4428 p_param6_value in varchar2 ,
4429 p_param7 in varchar2 ,
4430 p_param7_value in varchar2 ,
4431 p_param8 in varchar2 ,
4432 p_param8_value in varchar2 ,
4433 p_param9 in varchar2 ,
4434 p_param9_value in varchar2 ,
4435 p_param10 in varchar2 ,
4436 p_param10_value in varchar2 ,
4437 p_param11 in varchar2 ,
4438 p_param11_value in varchar2 ,
4439 p_param12 in varchar2 ,
4440 p_param12_value in varchar2 ,
4441 p_param13 in varchar2 ,
4442 p_param13_value in varchar2 ,
4443 p_param14 in varchar2 ,
4444 p_param14_value in varchar2 ,
4445 p_param15 in varchar2 ,
4446 p_param15_value in varchar2 ,
4447 p_param16 in varchar2 ,
4448 p_param16_value in varchar2 ,
4449 p_param17 in varchar2 ,
4450 p_param17_value in varchar2 ,
4451 p_param18 in varchar2 ,
4452 p_param18_value in varchar2 ,
4453 p_param19 in varchar2 ,
4454 p_param19_value in varchar2 ,
4455 p_param20 in varchar2 ,
4456 p_param20_value in varchar2 ,
4457 p_param21 in varchar2 ,
4458 p_param21_value in varchar2 ,
4459 p_param22 in varchar2 ,
4460 p_param22_value in varchar2 ,
4461 p_param23 in varchar2 ,
4462 p_param23_value in varchar2 ,
4463 p_param24 in varchar2 ,
4464 p_param24_value in varchar2 ,
4465 p_param25 in varchar2 ,
4466 p_param25_value in varchar2 ,
4467 p_param26 in varchar2 ,
4468 p_param26_value in varchar2 ,
4469 p_param27 in varchar2 ,
4470 p_param27_value in varchar2 ,
4471 p_param28 in varchar2 ,
4472 p_param28_value in varchar2 ,
4473 p_param29 in varchar2 ,
4474 p_param29_value in varchar2 ,
4475 p_param30 in varchar2 ,
4476 p_param30_value in varchar2 ,
4477 p_param31 in varchar2 ,
4478 p_param31_value in varchar2 ,
4479 p_param32 in varchar2 ,
4480 p_param32_value in varchar2 ,
4481 p_param33 in varchar2 ,
4482 p_param33_value in varchar2 ,
4483 p_param34 in varchar2 ,
4484 p_param34_value in varchar2 ,
4485 p_param35 in varchar2 ,
4486 p_param35_value in varchar2 ,
4487 p_param_tab in ff_exec.outputs_t ,
4488 p_ret_val out nocopy varchar2) is
4489 --
4490
4491 l_package varchar2(80) := g_package||'.run_rule';
4492 l_outputs ff_exec.outputs_t;
4493 l_loc_rec hr_locations_all%rowtype;
4494 l_ass_rec per_all_assignments_f%rowtype;
4495 l_jurisdiction_code varchar2(30);
4496 l_env ben_env_object.g_global_env_rec_type;
4497 --
4498 begin
4499 --
4500 hr_utility.set_location ('Entering '||l_package,10);
4501 --
4502 -- Bug : 1656320 : context assignment id is not available.
4503 -- Add environment init procedure
4504 -- Work out if we are being called from a concurrent program
4505 -- otherwise we need to initialize the environment to set the business_goup_id
4506 -- and effective_date in the cache, so that assignment get_object
4507 -- routines work fine.
4508 --
4509 hr_utility.set_location('p_ff_date '||p_effective_date ||
4510 ' p_leod ' || p_lf_evt_ocrd_dt,11);
4511 --
4512 -- if fnd_global.conc_request_id = -1 then
4513 -- bug 4947096
4514 if ben_env_object.g_global_env_rec.business_group_id is NULL
4515 then
4516 --
4517 -- This makes sense for the calls made from the forms.
4518 --
4519 ben_env_object.init(p_business_group_id => p_business_group_id,
4520 p_effective_date => p_effective_date,
4521 p_thread_id => 1,
4522 p_chunk_size => 1,
4523 p_threads => 1,
4524 p_max_errors => 1,
4525 p_benefit_action_id => null);
4526 --
4527 end if;
4528 --
4529 -- Call formula initialise routine
4530 --
4531 if p_person_id is not null then
4532 ben_person_object.get_object(p_person_id => p_person_id,
4533 p_rec => l_ass_rec);
4534 end if;
4535 --
4536 if p_person_id is not null and l_ass_rec.assignment_id is null then
4537 --
4538 ben_person_object.get_benass_object(p_person_id => p_person_id,
4539 p_rec => l_ass_rec);
4540 --
4541 end if;
4542 --
4543 -- Bug 1949361 : jurisdiction_code is fetched inside the
4544 -- benutils.formula call.
4545 --
4546 /*
4547 if l_ass_rec.location_id is not null then
4548 --
4549 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
4550 p_rec => l_loc_rec);
4551 --
4552 if l_loc_rec.region_2 is not null then
4553 --
4554 l_jurisdiction_code :=
4555 pay_mag_utils.lookup_jurisdiction_code
4556 (p_state => l_loc_rec.region_2);
4557 --
4558 end if;
4559 --
4560 end if;
4561 */
4562 --
4563 hr_utility.set_location('assignment_id '||l_ass_rec.assignment_id , 13);
4564 l_outputs := benutils.formula
4565 (p_formula_id => p_formula_id,
4566 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
4567 p_assignment_id => l_ass_rec.assignment_id,
4568 p_organization_id => l_ass_rec.organization_id,
4569 p_business_group_id => p_business_group_id,
4570 p_param1 => 'NEW_VAL',
4571 p_param1_value => p_new_value,
4572 p_param2 => 'OLD_VAL',
4573 p_param2_value => p_old_value,
4574 p_param3 => 'COLUMN',
4575 p_param3_value => p_column_name,
4576 p_param4 => 'PK_ID',
4577 p_param4_value => p_pk_id,
4578 p_param5 => p_param5,
4579 p_param5_value => p_param5_value,
4580 p_param6 => p_param6,
4581 p_param6_value => p_param6_value,
4582 p_param7 => p_param7,
4583 p_param7_value => p_param7_value,
4584 p_param8 => p_param8,
4585 p_param8_value => p_param8_value,
4586 p_param9 => p_param9,
4587 p_param9_value => p_param9_value,
4588 p_param10 => p_param10,
4589 p_param10_value => p_param10_value,
4590 p_param11 => p_param11,
4591 p_param11_value => p_param11_value,
4592 p_param12 => p_param12,
4593 p_param12_value => p_param12_value,
4594 p_param13 => p_param13,
4595 p_param13_value => p_param13_value,
4596 p_param14 => p_param14,
4597 p_param14_value => p_param14_value,
4598 p_param15 => p_param15,
4599 p_param15_value => p_param15_value,
4600 p_param16 => p_param16,
4601 p_param16_value => p_param16_value,
4602 p_param17 => p_param17,
4603 p_param17_value => p_param17_value,
4604 p_param18 => p_param18,
4605 p_param18_value => p_param18_value,
4606 p_param19 => p_param19,
4607 p_param19_value => p_param19_value,
4608 p_param20 => p_param20,
4609 p_param20_value => p_param20_value,
4610 p_param21 => p_param21,
4611 p_param21_value => p_param21_value,
4612 p_param22 => p_param22,
4613 p_param22_value => p_param22_value,
4614 p_param23 => p_param23,
4615 p_param23_value => p_param23_value,
4616 p_param24 => p_param24,
4617 p_param24_value => p_param24_value,
4618 p_param25 => p_param25,
4619 p_param25_value => p_param25_value,
4620 p_param26 => p_param26,
4621 p_param26_value => p_param26_value,
4622 p_param27 => p_param27,
4623 p_param27_value => p_param27_value,
4624 p_param28 => p_param28,
4625 p_param28_value => p_param28_value,
4626 p_param29 => p_param29,
4627 p_param29_value => p_param29_value,
4628 p_param30 => p_param30,
4629 p_param30_value => p_param30_value,
4630 p_param31 => p_param31,
4631 p_param31_value => p_param31_value,
4632 p_param32 => p_param32,
4633 p_param32_value => p_param32_value,
4634 p_param33 => p_param33,
4635 p_param33_value => p_param33_value,
4636 p_param34 => p_param34,
4637 p_param34_value => p_param34_value,
4638 --
4639 -- Bug 1656320 : As so many params are not used and person_id is
4640 -- a good input value just pass it.
4641 --
4642 p_param35 => 'PERSON_ID', -- p_param35,
4643 p_param35_value => to_char(p_person_id), -- p_param35_value,
4644 p_param_tab => p_param_tab,
4645 p_jurisdiction_code => l_jurisdiction_code);
4646 --
4647 p_ret_val := l_outputs(l_outputs.first).value;
4648 --
4649 if p_ret_val <> 'Y' and p_ret_val <> 'N' then
4650 --
4651 -- Defensive coding : If formula returns other than Y/N then
4652 -- ptnl is created based on old val and new val.
4653 --
4654 p_ret_val := 'Y';
4655 --
4656 end if;
4657 --
4658 hr_utility.set_location ('Leaving '||l_package,10);
4659 --
4660 end exec_rule;
4661 --
4662 --
4663 function get_rt_val(p_per_in_ler_id in number,
4664 p_prtt_rt_val_id in number,
4665 p_effective_date in date)
4666 return number is
4667 -- 4710155 : Old cursor prior to non-recurring rate fix 4460101
4668 cursor c_prv1 is
4669 select nvl(prv.cmcd_rt_val,0)
4670 from ben_prtt_rt_val prv
4671 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4672 and prv.per_in_ler_id = p_per_in_ler_id
4673 and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
4674 and prv.rt_strt_dt <= prv.rt_end_dt;
4675 --Commented for Bug 6048854
4676 --and prv.rt_end_dt = hr_api.g_eot;
4677 --
4678 --
4679
4680 /* Commented for Bug 6048854
4681 cursor c_prv2 is
4682 select nvl(prv.cmcd_rt_val,0)
4683 from ben_prtt_rt_val prv
4684 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4685 and prv.per_in_ler_id = p_per_in_ler_id
4686 Bug 5376185 : Pick the latest non-recurring rate
4687 and prv.rt_strt_dt = prv.rt_end_dt
4688 and prv.rt_end_dt <> hr_api.g_eot;
4689 */
4690 --
4691 l_rt_val number := null;
4692 --
4693 begin
4694 --
4695 if p_per_in_ler_id is not null and p_prtt_rt_val_id is not null then
4696 -- 4710155 : Fetch the rate from old cursor first
4697 open c_prv1;
4698 fetch c_prv1 into l_rt_val;
4699 close c_prv1;
4700
4701 /* Commented for Bug 6048854
4702 --
4703 -- 4710155 : If the old cursor does not fetch rate then
4704 -- get the rate using new cursor
4705 if l_rt_val is null then
4706 open c_prv2;
4707 fetch c_prv2 into l_rt_val;
4708 close c_prv2;
4709 end if;
4710 --
4711 */
4712 end if;
4713 --
4714 return l_rt_val;
4715 --
4716 end get_rt_val;
4717 --
4718
4719 --
4720 function get_ann_rt_val(p_per_in_ler_id in number,
4721 p_prtt_rt_val_id in number,
4722 p_effective_date in date)
4723 return number is
4724 -- 4710155 : Old cursor prior to non-recurring rate fix 4460101
4725 cursor c_prv1 is
4726 select nvl(prv.ann_rt_val,0)
4727 from ben_prtt_rt_val prv
4728 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4729 and prv.per_in_ler_id = p_per_in_ler_id
4730 and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
4731 and prv.rt_strt_dt <= prv.rt_end_dt;
4732 --Commented for Bug 6048854
4733 --and prv.rt_end_dt = hr_api.g_eot;
4734 --
4735 --
4736 /* Commented for Bug 6048854
4737 cursor c_prv2 is
4738 select nvl(prv.ann_rt_val,0)
4739 from ben_prtt_rt_val prv
4740 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4741 and prv.per_in_ler_id = p_per_in_ler_id
4742 Bug 5376185 : Pick the latest non-recurring rate
4743 and prv.rt_strt_dt = prv.rt_end_dt
4744 and prv.rt_end_dt <> hr_api.g_eot;
4745 */
4746 --
4747 l_rt_val number := null;
4748 --
4749 begin
4750 --
4751 if p_per_in_ler_id is not null and p_prtt_rt_val_id is not null then
4752 -- 4710155 : Fetch the rate from old cursor first
4753 open c_prv1;
4754 fetch c_prv1 into l_rt_val;
4755 close c_prv1;
4756 --
4757 /* Commented for Bug 6048854
4758 -- 4710155 : If the old cursor does not fetch rate then
4759 -- get the rate using new cursor
4760 if l_rt_val is null then
4761 open c_prv2;
4762 fetch c_prv2 into l_rt_val;
4763 close c_prv2;
4764 end if;
4765 */
4766 --
4767 end if;
4768 --
4769 return l_rt_val;
4770 --
4771 end get_ann_rt_val;
4772 --
4773 --
4774 function get_concat_val(p_per_in_ler_id in number,
4775 p_prtt_rt_val_id in number)
4776 return varchar2 is
4777 --
4778 cursor c_prv is
4779 select to_char(nvl(prv.ann_rt_val,0))||'^'|| to_char(nvl(prv.cmcd_rt_val,0))||'^'||to_char(nvl(prv.rt_val,0))
4780 from ben_prtt_rt_val prv
4781 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4782 and prv.per_in_ler_id = p_per_in_ler_id
4783 and prv.rt_end_dt = hr_api.g_eot;
4784 --
4785 l_rt_val varchar2(100) := null;
4786 --
4787 begin
4788 --
4789 if p_per_in_ler_id is not null and p_prtt_rt_val_id is not null then
4790 --
4791 open c_prv;
4792 fetch c_prv into l_rt_val;
4793 close c_prv;
4794 --
4795 end if;
4796 --
4797 return l_rt_val;
4798 --
4799 end get_concat_val;
4800
4801 --The column in the table which gives the value of val is rt_val but the name is already
4802 -- being used by the function which gets value from the cmcd_rt_val hence name get_val.
4803 function get_val(p_per_in_ler_id in number,
4804 p_prtt_rt_val_id in number,
4805 p_effective_date in date)
4806 return number is
4807 -- 4710155 : Old cursor prior to non-recurring rate fix 4460101
4808 cursor c_prv1 is
4809 select nvl(prv.rt_val,0)
4810 from ben_prtt_rt_val prv
4811 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4812 and prv.per_in_ler_id = p_per_in_ler_id
4813 and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
4814 and prv.rt_strt_dt <= prv.rt_end_dt;
4815 --Commented for Bug 6048854
4816 --and prv.rt_end_dt = hr_api.g_eot;
4817 --
4818 --
4819 /*Commented for Bug 6048854
4820 cursor c_prv2 is
4821 select nvl(prv.rt_val,0)
4822 from ben_prtt_rt_val prv
4823 where prv.prtt_rt_val_id = p_prtt_rt_val_id
4824 and prv.per_in_ler_id = p_per_in_ler_id
4825 Bug 5376185 : Pick the latest non-recurring rate
4826 and prv.rt_strt_dt = prv.rt_end_dt
4827 and prv.rt_end_dt <> hr_api.g_eot;
4828 */
4829 --
4830 l_rt_val number := null;
4831 --
4832 begin
4833 --
4834 if p_per_in_ler_id is not null and p_prtt_rt_val_id is not null then
4835 -- 4710155 : Fetch the rate from old cursor first
4836 open c_prv1;
4837 fetch c_prv1 into l_rt_val;
4838 close c_prv1;
4839 --
4840 /*Commented for Bug 6048854
4841 -- 4710155 : If the old cursor does not fetch rate then
4842 -- get the rate using new cursor
4843 if l_rt_val is null then
4844 open c_prv2;
4845 fetch c_prv2 into l_rt_val;
4846 close c_prv2;
4847 end if;
4848 */
4849 --
4850 end if;
4851 --
4852 return l_rt_val;
4853 --
4854 end get_val;
4855 --
4856 --
4857 -- ----------------------------------------------------------------------------
4858 -- |---------------------< get_post_enrt_cvg_and_rt_val >---------------------|
4859 -- ----------------------------------------------------------------------------
4860 -- {Start Of Comments}
4861 --
4862 -- Description:
4863 -- Procedure to retrieve the Coverage amount and Rate Amount values for
4864 -- those coverage and rates whose Calculation method is 'Post-Enrollment
4865 -- Calculation Rule'
4866 --
4867 -- Pre-conditions: Specifically written for self-service and should be used
4868 -- only after Election Information and Post-Process is called.
4869 --
4870 -- In Arguments: choice id, bnft id, and rt ids.
4871 --
4872 -- Post Success: returns all relevant amounts.
4873 --
4874 -- Post Failure: returns null
4875 --
4876 -- Access Status:
4877 -- Internal Development Use Only.
4878 --
4879 -- {End Of Comments}
4880 -----------------------------------------------------------------------------
4881 --
4882 procedure get_post_enrt_cvg_and_rt_val
4883 (p_elig_per_elctbl_chc_id in number,
4884 p_enrt_bnft_id in number default null,
4885 p_effective_date in date,
4886 p_enrt_rt_id in number default null,
4887 p_enrt_rt_id2 in number default null,
4888 p_enrt_rt_id3 in number default null,
4889 p_enrt_rt_id4 in number default null,
4890 p_bnft_amt out nocopy number,
4891 p_val out nocopy number,
4892 p_rt_val out nocopy number,
4893 p_ann_rt_val out nocopy number,
4894 p_val2 out nocopy number,
4895 p_rt_val2 out nocopy number,
4896 p_ann_rt_val2 out nocopy number,
4897 p_val3 out nocopy number,
4898 p_rt_val3 out nocopy number,
4899 p_ann_rt_val3 out nocopy number,
4900 p_val4 out nocopy number,
4901 p_rt_val4 out nocopy number,
4902 p_ann_rt_val4 out nocopy number)
4903 is
4904 --
4905 --l_package varchar2(80) := g_package||'.get_post_enrt_cvg_and_rt_val';
4906 --
4907 cursor c_pen_bnft_amt is
4908 select pen.bnft_amt
4909 from ben_prtt_enrt_rslt_f pen,
4910 ben_elig_per_elctbl_chc epe,
4911 ben_enrt_bnft enb
4912 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
4913 and enb.enrt_bnft_id = p_enrt_bnft_id
4914 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
4915 -- and enb.mx_wo_ctfn_flag = 'N'
4916 -- and enb.cvg_mlt_cd = 'ERL'
4917 -- commented, so that we retrieve SAAEAR cvgs based on ERL rates.
4918 and nvl(epe.prtt_enrt_rslt_id,
4919 enb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
4920 and p_effective_date between
4921 pen.effective_start_date and pen.effective_end_date
4922 and pen.prtt_enrt_rslt_stat_cd is null
4923 and pen.enrt_cvg_thru_dt <= pen.effective_end_date;
4924 --
4925 l_bnft_amt number;
4926 cursor c_enrt_rt_val is
4927 select enrt_rt_id,
4928 nvl(prv.rt_val, 0) val,
4929 nvl(prv.cmcd_rt_val, 0) rt_val,
4930 nvl(prv.ann_rt_val, 0) ann_rt_val
4931 from ben_prtt_rt_val prv,
4932 ben_enrt_rt ecr
4933 where prv.prtt_rt_val_id = ecr.prtt_rt_val_id
4934 and ecr.enrt_rt_id in (p_enrt_rt_id, p_enrt_rt_id2,
4935 p_enrt_rt_id3, p_enrt_rt_id4);
4936 --and ecr.rt_mlt_cd = 'ERL';
4937 -- commented, so that we retrieve CVG rates based on ERL coverages.
4938 --
4939 --
4940 begin
4941 --
4942 --hr_utility.set_location('Entering ' ||l_package, 10);
4943 --hr_utility.set_location('p_elig_per_elctbl_chc_id ' || to_char(p_elig_per_elctbl_chc_id ), 20);
4944 --hr_utility.set_location('p_enrt_bnft_id ' || to_char(p_enrt_bnft_id ), 20);
4945 --hr_utility.set_location('p_effective_date ' || to_char(p_effective_date ), 20);
4946 --hr_utility.set_location('p_enrt_rt_id ' || to_char(p_enrt_rt_id ), 20);
4947 --hr_utility.set_location('p_enrt_rt_id2 ' || to_char(p_enrt_rt_id2 ), 20);
4948 --hr_utility.set_location('p_enrt_rt_id3 ' || to_char(p_enrt_rt_id3 ), 20);
4949 --hr_utility.set_location('p_enrt_rt_id4 ' || to_char(p_enrt_rt_id4 ), 20);
4950 --
4951 open c_pen_bnft_amt;
4952 fetch c_pen_bnft_amt into l_bnft_amt;
4953 if c_pen_bnft_amt%found then
4954 --
4955 --hr_utility.set_location(' bnft amt found ' || to_char(l_bnft_amt), 30);
4956 p_bnft_amt := l_bnft_amt ;
4957 --
4958 end if;
4959 close c_pen_bnft_amt;
4960 --
4961 --
4962 for l_enrt_rt_val_rec in c_enrt_rt_val
4963 loop
4964 --
4965 if l_enrt_rt_val_rec.enrt_rt_id = p_enrt_rt_id then
4966 --
4967 p_val := l_enrt_rt_val_rec.val;
4968 p_rt_val := l_enrt_rt_val_rec.rt_val;
4969 p_ann_rt_val := l_enrt_rt_val_rec.ann_rt_val;
4970 --
4971 elsif l_enrt_rt_val_rec.enrt_rt_id = p_enrt_rt_id2 then
4972 --
4973 p_val2 := l_enrt_rt_val_rec.val;
4974 p_rt_val2 := l_enrt_rt_val_rec.rt_val;
4975 p_ann_rt_val2:= l_enrt_rt_val_rec.ann_rt_val;
4976 --
4977 elsif l_enrt_rt_val_rec.enrt_rt_id = p_enrt_rt_id3 then
4978 --
4979 p_val3 := l_enrt_rt_val_rec.val;
4980 p_rt_val3 := l_enrt_rt_val_rec.rt_val;
4981 p_ann_rt_val3:= l_enrt_rt_val_rec.ann_rt_val;
4982 --
4983 elsif l_enrt_rt_val_rec.enrt_rt_id = p_enrt_rt_id4 then
4984 --
4985 p_val4 := l_enrt_rt_val_rec.val;
4986 p_rt_val4 := l_enrt_rt_val_rec.rt_val;
4987 p_ann_rt_val4:= l_enrt_rt_val_rec.ann_rt_val;
4988 --
4989 end if;
4990 --
4991 end loop;
4992 --
4993 --hr_utility.set_location('Entering ' ||l_package, 10);
4994 --
4995 exception
4996 --
4997 when others then
4998 --
4999 p_bnft_amt := null;
5000 p_val := null;
5001 p_rt_val := null;
5002 p_ann_rt_val := null;
5003 p_val2 := null;
5004 p_rt_val2 := null;
5005 p_ann_rt_val2 := null;
5006 p_val3 := null;
5007 p_rt_val3 := null;
5008 p_ann_rt_val3 := null;
5009 p_val4 := null;
5010 p_rt_val4 := null;
5011 p_ann_rt_val4 := null;
5012 --
5013 raise;
5014 end get_post_enrt_cvg_and_rt_val;
5015 --
5016 function get_choice_status(p_elig_per_elctbl_chc_id in number)
5017 return varchar2 is
5018 --
5019 cursor c_pending is
5020 select 'Y'
5021 from wf_item_activity_statuses process
5022 ,wf_item_attribute_values choice_attribute
5023 ,wf_item_attribute_values submit_attribute
5024 ,wf_process_activities activity
5025 ,hr_api_transaction_steps step
5026 where activity.activity_name = 'HR_INDIVIDUAL_COMP_PRC'
5027 and activity.process_item_type = activity.activity_item_type
5028 and activity.instance_id = process.process_activity
5029 and process.activity_status = 'ACTIVE'
5030 and process.item_key = choice_attribute.item_key
5031 and choice_attribute.item_type = process.item_type
5032 and choice_attribute.name = 'COMP_CHOICE_ID'
5033 and choice_attribute.text_value = p_elig_per_elctbl_chc_id
5034 and submit_attribute.item_key = process.item_key
5035 and submit_attribute.item_type = process.item_type
5036 and submit_attribute.name = 'TRAN_SUBMIT'
5037 and submit_attribute.text_value = 'Y'
5038 and step.item_type = choice_attribute.item_type
5039 and choice_attribute.item_key = step.item_key
5040 and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API';
5041 --
5042 l_return varchar2(30) := 'N';
5043 --
5044 begin
5045 --
5046 if p_elig_per_elctbl_chc_id is not null then
5047 --
5048 open c_pending;
5049 fetch c_pending into l_return;
5050 close c_pending;
5051 --
5052 end if;
5053 --
5054 return l_return;
5055 --
5056 end get_choice_status;
5057 --
5058 function in_workflow(p_person_id in number)
5059 return varchar2 is
5060 --
5061 cursor in_wf(p_person_id NUMBER) is
5062 -- maagrawa (23/Sep/2005)
5063 -- re-wrote query for performance in case this function is used.
5064 select 'Y'
5065 from wf_item_activity_statuses process ,
5066 wf_process_activities activity ,
5067 hr_api_transactions txn,
5068 hr_api_transaction_steps step ,
5069 wf_item_attribute_values submit_attribute
5070 where activity.process_name = 'ROOT'
5071 and activity.process_item_type = activity.activity_item_type
5072 and activity.instance_id = process.process_activity
5073 and process.activity_status = 'ACTIVE'
5074 and txn.item_type = process.item_type
5075 and txn.item_key = process.item_key
5076 and txn.selected_person_id = p_person_id
5077 and txn.transaction_id = step.transaction_id
5078 and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API'
5079 and submit_attribute.text_value = 'Y'
5080 and txn.item_type = submit_attribute.item_type
5081 and txn.item_key = submit_attribute.item_key
5082 and submit_attribute.name = 'TRAN_SUBMIT';
5083
5084 l_return VARCHAR2(1) :='N';
5085 begin
5086 --
5087 -- Bug 3116433 : This function's output is not used on the
5088 -- form. It is causing querying person on miscellaneous form
5089 -- to take too long.
5090 -- In case above cusror is needed the tuned sql is put in place
5091 -- above.
5092 /*
5093 open in_wf(p_person_id);
5094 --
5095 fetch in_wf into l_return;
5096 --
5097 close in_wf;
5098 --
5099 */
5100 return l_return;
5101 end in_workflow;
5102 --
5103 --
5104 -- Bug No 2258174
5105 --
5106 function basis_to_plan_conversion(p_pl_id in number,
5107 p_effective_date in date,
5108 p_amount in number,
5109 p_assignment_id in number
5110 ) return number is
5111 --
5112 -- Local variable declaration
5113 --
5114 l_ret_amount NUMBER;
5115 l_precision NUMBER;
5116 l_ref_perd_cd VARCHAR2(30);
5117 l_factor NUMBER;
5118 --
5119 -- Cursors declaration.
5120 --
5121 CURSOR c_ref_perd_cd IS
5122 select pl.nip_acty_ref_perd_cd
5123 ,nvl(cur.precision,2)
5124 from ben_pl_f pl
5125 ,fnd_currencies cur
5126 where pl.pl_id = p_pl_id
5127 and p_effective_date between pl.effective_start_date
5128 and pl.effective_end_date
5129 and cur.CURRENCY_CODE(+) = pl.nip_pl_uom
5130 ;
5131 CURSOR c_pay_basis IS
5132 select ppb.pay_annualization_factor
5133 from per_all_assignments_f asg
5134 ,per_pay_bases ppb
5135 where asg.assignment_id = p_assignment_id
5136 and p_effective_date between asg.effective_start_date
5137 and asg.effective_end_date
5138 and ppb.pay_basis_id = asg.pay_basis_id
5139 ;
5140
5141 --
5142 l_pay_annualization_factor number;
5143 --
5144 BEGIN
5145 --
5146 OPEN c_ref_perd_cd;
5147 FETCH c_ref_perd_cd into l_ref_perd_cd,l_precision;
5148 IF c_ref_perd_Cd%NOTFOUND THEN
5149 l_ref_perd_cd := 'NOVAL';
5150 END IF;
5151 CLOSE c_ref_perd_cd;
5152 --
5153 OPEN c_pay_basis;
5154 FETCH c_pay_basis into l_factor;
5155 IF l_factor is null THEN
5156 l_factor := 1;
5157 END IF;
5158 CLOSE c_pay_basis;
5159 --
5160 IF l_ref_perd_cd = 'PWK' THEN
5161 l_ret_amount := (p_amount*l_factor)/52;
5162 ELSIF l_ref_perd_cd = 'BWK' THEN
5163 l_ret_amount := (p_amount*l_factor)/26;
5164 ELSIF l_ref_perd_cd = 'SMO' THEN
5165 l_ret_amount := (p_amount*l_factor)/24;
5166 ELSIF l_ref_perd_cd = 'PQU' THEN
5167 l_ret_amount := (p_amount*l_factor)/4;
5168 ELSIF l_ref_perd_cd = 'PYR' THEN
5169 l_ret_amount := (p_amount*l_factor)/1;
5170 ELSIF l_ref_perd_cd = 'SAN' THEN
5171 l_ret_amount := (p_amount*l_factor)/2;
5172 ELSIF l_ref_perd_cd = 'MO' THEN
5173 l_ret_amount := (p_amount*l_factor)/12;
5174 ELSIF l_ref_perd_cd = 'NOVAL' THEN
5175 l_ret_amount := (p_amount*l_factor)/1;
5176 ELSIF l_ref_perd_cd = 'PHR' then
5177 --
5178 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
5179 if l_pay_annualization_factor is null then
5180 l_pay_annualization_factor := 2080;
5181 end if;
5182 --
5183 l_ret_amount := (p_amount*l_factor)/l_pay_annualization_factor;
5184 --
5185 ELSE
5186 l_ret_amount := (p_amount*l_factor)/1;
5187 END IF;
5188 --
5189 RETURN round(l_ret_amount,l_precision);
5190 END basis_to_plan_conversion;
5191 --
5192 function plan_to_basis_conversion(p_pl_id in number,
5193 p_effective_date in date,
5194 p_amount in number,
5195 p_assignment_id in number
5196 ) return number is
5197 --
5198 -- Local variable declaration
5199 --
5200 l_ret_amount NUMBER;
5201 l_precision NUMBER;
5202 l_ref_perd_cd VARCHAR2(30);
5203 l_factor NUMBER;
5204 --
5205 -- Cursors declaration.
5206 --
5207 CURSOR c_ref_perd_cd IS
5208 select pl.nip_acty_ref_perd_cd
5209 ,nvl(cur.precision,2)
5210 from ben_pl_f pl
5211 ,fnd_currencies cur
5212 where pl.pl_id = p_pl_id
5213 and p_effective_date between pl.effective_start_date
5214 and pl.effective_end_date
5215 and cur.CURRENCY_CODE(+) = pl.nip_pl_uom
5216 ;
5217 CURSOR c_pay_basis IS
5218 select ppb.pay_annualization_factor
5219 from per_all_assignments_f asg
5220 ,per_pay_bases ppb
5221 where asg.assignment_id = p_assignment_id
5222 and p_effective_date between asg.effective_start_date
5223 and asg.effective_end_date
5224 and ppb.pay_basis_id = asg.pay_basis_id
5225 ;
5226 --
5227 l_pay_annualization_factor number;
5228 --
5229 BEGIN
5230 --
5231 OPEN c_ref_perd_cd;
5232 FETCH c_ref_perd_cd into l_ref_perd_cd,l_precision;
5233 IF c_ref_perd_Cd%NOTFOUND THEN
5234 l_ref_perd_cd := 'NOVAL';
5235 END IF;
5236 CLOSE c_ref_perd_cd;
5237 --
5238 OPEN c_pay_basis;
5239 FETCH c_pay_basis into l_factor;
5240 IF l_factor is null THEN
5241 l_factor := 1;
5242 END IF;
5243 CLOSE c_pay_basis;
5244 --
5245 IF l_ref_perd_cd = 'PWK' THEN
5246 l_ret_amount := (p_amount*52)/l_factor;
5247 ELSIF l_ref_perd_cd = 'BWK' THEN
5248 l_ret_amount := (p_amount*26)/l_factor;
5249 ELSIF l_ref_perd_cd = 'SMO' THEN
5250 l_ret_amount := (p_amount*24)/l_factor;
5251 ELSIF l_ref_perd_cd = 'PQU' THEN
5252 l_ret_amount := (p_amount*4)/l_factor;
5253 ELSIF l_ref_perd_cd = 'PYR' THEN
5254 l_ret_amount := (p_amount*1)/l_factor;
5255 ELSIF l_ref_perd_cd = 'SAN' THEN
5256 l_ret_amount := (p_amount*2)/l_factor;
5257 ELSIF l_ref_perd_cd = 'MO' THEN
5258 l_ret_amount := (p_amount*12)/l_factor;
5259 ELSIF l_ref_perd_cd = 'NOVAL' THEN
5260 l_ret_amount := (p_amount*1)/l_factor;
5261 ELSIF l_ref_perd_cd = 'PHR' then
5262 --
5263 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
5264 if l_pay_annualization_factor is null then
5265 l_pay_annualization_factor := 2080;
5266 end if;
5267 --
5268 l_ret_amount := (p_amount * l_pay_annualization_factor)/l_factor;
5269 --
5270 ELSE
5271 l_ret_amount := (p_amount*1)/l_factor;
5272 END IF;
5273 --
5274 RETURN round(l_ret_amount,l_precision);
5275 END plan_to_basis_conversion;
5276 --
5277 --
5278 --
5279 function get_pl_annualization_factor(p_acty_ref_perd_cd in varchar2) return number is
5280 l_factor number := 1;
5281 begin
5282 if p_acty_ref_perd_cd = 'PWK' THEN
5283 l_factor := 52;
5284 elsif p_acty_ref_perd_cd = 'BWK' THEN
5285 l_factor := 26;
5286 elsif p_acty_ref_perd_cd = 'SMO' THEN
5287 l_factor := 24;
5288 elsif p_acty_ref_perd_cd = 'PQU' THEN
5289 l_factor := 4;
5290 elsif p_acty_ref_perd_cd = 'SAN' THEN
5291 l_factor := 2;
5292 elsif p_acty_ref_perd_cd = 'MO' THEN
5293 l_factor := 12;
5294 elsif p_acty_ref_perd_cd = 'PHR' then
5295 l_factor := nvl(to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR')),2080);
5296 else
5297 -- 'NOVAL', 'PYR', null , or anything else
5298 l_factor := 1;
5299 end if;
5300 --
5301 return l_factor;
5302 --
5303 END get_pl_annualization_factor;
5304 --
5305 --
5306 -- Bug 2016857
5307 procedure set_data_migrator_mode
5308 is
5309 --
5310 l_proc varchar2(72):=g_package||'set_data_migrator_mode';
5311 --
5312 cursor c_mode is
5313 select upper(substr(pap.parameter_value,1,1))
5314 from pay_action_parameters pap
5315 where pap.parameter_name = 'DATA_MIGRATOR_MODE';
5316 --
5317 l_mode varchar2(30) := 'N';
5318 --
5319 cursor c_pap_mode (p_pap_grp_id number ) is
5320 select upper(substr(pap.parameter_value,1,1))
5321 from pay_action_parameter_values pap
5322 where pap.parameter_name = 'DATA_MIGRATOR_MODE'
5323 and pap.ACTION_PARAMETER_GROUP_ID = p_pap_grp_id ;
5324 --
5325 l_profile_value number ;
5326 l_defined Boolean ;
5327 --
5328 begin
5329 hr_utility.set_location('Entering '||l_proc, 999);
5330 --
5331 -- check if the profile is set with PAP group
5332 --
5333 fnd_profile.get_specific( name_z => 'ACTION_PARAMETER_GROUPS'
5334 ,user_id_z => fnd_global.user_id
5335 ,responsibility_id_z => fnd_global.resp_id
5336 ,application_id_z => fnd_global.resp_appl_id
5337 ,val_z => l_profile_value
5338 ,defined_z => l_defined );
5339
5340 hr_utility.set_location('l_profile_value '||l_profile_value, 999);
5341 --
5342 -- If the profile is not set with PAP group then look for default
5343 --
5344 if (l_profile_value is null or l_defined = FALSE )
5345 then
5346 open c_mode;
5347 fetch c_mode into l_mode;
5348 close c_mode;
5349 --
5350 hr_utility.set_location('l_profile_value not defined '||l_mode, 999);
5351 elsif ( l_profile_value is not null or l_defined = TRUE ) then
5352 --
5353 open c_pap_mode(l_profile_value );
5354 fetch c_pap_mode into l_mode;
5355 close c_pap_mode;
5356 --
5357 hr_utility.set_location('l_profile_value defined '||l_mode, 999);
5358 end if ;
5359 --
5360 if l_mode not in ('P','Y','N') then
5361 --
5362 hr_general.g_data_migrator_mode := 'N';
5363 --
5364 else
5365 hr_general.g_data_migrator_mode := l_mode ;
5366 end if;
5367 --
5368 hr_utility.set_location('successful '||hr_general.g_data_migrator_mode, 999);
5369 hr_utility.set_location('Leaving '||l_proc, 999);
5370 exception
5371 --
5372 when others then
5373 --
5374 hr_general.g_data_migrator_mode := 'N';
5375 --
5376 hr_utility.set_location('when others value '||hr_general.g_data_migrator_mode, 999);
5377 end set_data_migrator_mode;
5378 --
5379 -- Bug 2016857
5380 --
5381 -- Bug 2428672
5382 Function ben_get_abp_plan_opt_names
5383 (p_bnft_prvdr_pool_id IN ben_bnft_prvdr_pool_f.bnft_prvdr_pool_id%TYPE,
5384 p_business_group_id IN ben_acty_base_rt_f.business_group_id%TYPE,
5385 p_acty_base_rt_id IN ben_acty_base_rt_f.acty_base_rt_id%TYPE,
5386 p_session_id IN fnd_sessions.session_id%TYPE,
5387 ret_flag IN varchar2)
5388 Return Varchar2
5389 Is
5390 lv_pgm_id ben_bnft_prvdr_pool_f.pgm_id%TYPE;
5391 lv_pl_name ben_pl_f.name%TYPE;
5392 lv_opt_name ben_opt_f.name%TYPE;
5393 lv_abr_name ben_acty_base_rt_f.name%TYPE;
5394 lv_meaning Varchar2(60);
5395
5396 Begin
5397 Begin
5398 Select bpp.pgm_id
5399 Into lv_pgm_id
5400 From ben_bnft_prvdr_pool_f bpp,
5401 fnd_sessions se
5402 Where se.session_id = p_session_id
5403 And bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
5404 And se.effective_date Between bpp.effective_start_date And bpp.effective_End_date;
5405
5406 Select bpp.pl_name, bpp.opt_name, bpp.abr_name, bpp.meaning
5407 Into lv_pl_name, lv_opt_name, lv_abr_name, lv_meaning
5408 From
5409 (Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
5410 abr.business_group_id business_group_id,
5411 pl.name pl_name, Null opt_name, abr.name abr_name,
5412 substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
5413 From ben_acty_base_rt_f abr,
5414 ben_plip_f plip,
5415 ben_pl_f pl,
5416 fnd_sessions se
5417 Where se.session_id = p_session_id
5418 And plip.pgm_id = lv_pgm_id
5419 And plip.pl_id = pl.pl_id
5420 And abr.pl_id = pl.pl_id
5421 And abr.acty_base_rt_id = p_acty_base_rt_id
5422 /* And pl.invk_dcln_prtn_pl_flag = 'N' */
5423 And pl.invk_flx_cr_pl_flag = 'N'
5424 And pl.imptd_incm_calc_cd is Null
5425 And abr.rt_usg_cd = 'STD'
5426 And abr.asn_on_enrt_flag = 'Y'
5427 And abr.business_group_id = p_business_group_id
5428 And se.effective_date Between abr.effective_start_date And abr.effective_End_date
5429 And se.effective_date Between plip.effective_start_date And plip.effective_End_date
5430 And se.effective_date Between pl.effective_start_date And pl.effective_End_date
5431 Union
5432 Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
5433 abr.business_group_id,
5434 pl.name pl_name, Null opt_name, abr.name abr_name,
5435 substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
5436 From ben_acty_base_rt_f abr,
5437 ben_plip_f plip,
5438 ben_pl_f pl,
5439 fnd_sessions se
5440 Where se.session_id = p_session_id
5441 And plip.pgm_id = lv_pgm_id
5442 And plip.pl_id = pl.pl_id
5443 And abr.plip_id = plip.plip_id
5444 And abr.acty_base_rt_id = p_acty_base_rt_id
5445 /* And pl.invk_dcln_prtn_pl_flag = 'N' */
5446 And pl.invk_flx_cr_pl_flag = 'N'
5447 And pl.imptd_incm_calc_cd is Null
5448 And abr.rt_usg_cd = 'STD'
5449 And abr.asn_on_enrt_flag = 'Y'
5450 And abr.business_group_id = p_business_group_id
5451 And se.effective_date Between abr.effective_start_date And abr.effective_End_date
5452 And se.effective_date Between plip.effective_start_date And plip.effective_End_date
5453 And se.effective_date Between pl.effective_start_date And pl.effective_End_date
5454 Union
5455 Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
5456 abr.business_group_id,
5457 pl.name pl_name, opt.name opt_name, abr.name abr_name,
5458 substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
5459 From ben_acty_base_rt_f abr,
5460 ben_plip_f plip,
5461 ben_pl_f pl,
5462 ben_oipl_f oipl,
5463 ben_opt_f opt,
5464 fnd_sessions se
5465 Where se.session_id = p_session_id
5466 And plip.pgm_id = lv_pgm_id
5467 And plip.pl_id = pl.pl_id
5468 And oipl.pl_id = pl.pl_id
5469 And abr.oipl_id = oipl.oipl_id
5470 And abr.acty_base_rt_id = p_acty_base_rt_id
5471 And oipl.opt_id = opt.opt_id
5472 /* And pl.invk_dcln_prtn_pl_flag = 'N' */
5473 And pl.invk_flx_cr_pl_flag = 'N'
5474 And pl.imptd_incm_calc_cd is Null
5475 And abr.rt_usg_cd = 'STD'
5476 And abr.asn_on_enrt_flag = 'Y'
5477 And abr.business_group_id = p_business_group_id
5478 And se.effective_date Between abr.effective_start_date And abr.effective_End_date
5479 And se.effective_date Between plip.effective_start_date And plip.effective_End_date
5480 And se.effective_date Between pl.effective_start_date And pl.effective_End_date
5481 And se.effective_date Between oipl.effective_start_date And oipl.effective_End_date
5482 And se.effective_date Between opt.effective_start_date And opt.effective_End_date
5483 Union
5484 Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
5485 abr.business_group_id,
5486 pl.name pl_name, opt.name opt_name, abr.name abr_name,
5487 substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
5488 From ben_acty_base_rt_f abr,
5489 ben_plip_f plip,
5490 ben_pl_f pl,
5491 ben_oipl_f oipl,
5492 ben_oiplip_f oiplip,
5493 ben_opt_f opt,
5494 fnd_sessions se
5495 Where se.session_id = p_session_id
5496 And plip.pgm_id = lv_pgm_id
5497 And plip.pl_id = pl.pl_id
5498 And oipl.pl_id = pl.pl_id
5499 And abr.oiplip_id = oiplip.oiplip_id
5500 And abr.acty_base_rt_id = p_acty_base_rt_id
5501 And oiplip.oipl_id = oipl.oipl_id
5502 And oipl.opt_id = opt.opt_id
5503 And plip.plip_id = oiplip.plip_id
5504 /* And pl.invk_dcln_prtn_pl_flag = 'N' */
5505 And pl.invk_flx_cr_pl_flag = 'N'
5506 And pl.imptd_incm_calc_cd is Null
5507 And abr.rt_usg_cd = 'STD'
5508 And abr.asn_on_enrt_flag = 'Y'
5509 And abr.business_group_id = p_business_group_id
5510 And se.effective_date Between abr.effective_start_date And abr.effective_End_date
5511 And se.effective_date Between plip.effective_start_date And plip.effective_End_date
5512 And se.effective_date Between pl.effective_start_date And pl.effective_End_date
5513 And se.effective_date Between oipl.effective_start_date And oipl.effective_End_date
5514 And se.effective_date Between opt.effective_start_date And opt.effective_End_date
5515 And se.effective_date Between oiplip.effective_start_date And oiplip.effective_End_date
5516 ) BPP;
5517
5518 Exception
5519 When Others Then
5520 Return Null;
5521 End;
5522
5523 If (ret_flag = 'PLAN') Then
5524 Return lv_pl_name;
5525 Elsif (ret_flag = 'OPTION') Then
5526 Return lv_opt_name;
5527 Elsif (ret_flag = 'ACTIVITY') Then
5528 Return lv_abr_name;
5529 Elsif (ret_flag = 'TAXABILITY') Then
5530 Return lv_meaning;
5531 Else
5532 Return Null;
5533 End If;
5534 Exception
5535 When Others Then
5536 Return Null;
5537 End ben_get_abp_plan_opt_names;
5538 -- Bug 2428672
5539
5540 --
5541 -- ----------------------------------------------------------------------------
5542 -- |---------------------< return_concat_kf_segments >------------------------|
5543 -- ----------------------------------------------------------------------------
5544 -- {Start Of Comments}
5545 --
5546 -- Description:
5547 -- Returns the display concatenated string for the segments1..30.
5548 -- The function calls hr_api.return_concat_kf_segments to get the
5549 -- concatenated segments.
5550 -- This function has been added to benutils as part of fix for bug 2599034
5551 -- Since there is a package HR_API present in PLD library and backend, it is
5552 -- conflicting with each other when we try to use the backend package from
5553 -- form. But hard-coding Apps.<package name> is not a good practice.
5554 -- Hence creating a wrapper for the hr_api.return_concat_kf_segments in
5555 -- benutils to accomplish the same.
5556 --
5557 -- Pre-conditions:
5558 -- The id_flex_num and segments have been fully validated.
5559 --
5560 -- In Arguments:
5561 -- p_rec
5562 --
5563 -- Post Success:
5564 --
5565 -- Post Failure:
5566 --
5567 -- Access Status:
5568 -- Internal Development Use Only.
5569 --
5570 -- {End Of Comments}
5571 -- ----------------------------------------------------------------------------
5572 function return_concat_kf_segments
5573 (p_id_flex_num in number,
5574 p_application_id in number,
5575 p_id_flex_code in varchar2,
5576 p_segment1 in varchar2 ,
5577 p_segment2 in varchar2 ,
5578 p_segment3 in varchar2 ,
5579 p_segment4 in varchar2 ,
5580 p_segment5 in varchar2 ,
5581 p_segment6 in varchar2 ,
5582 p_segment7 in varchar2 ,
5583 p_segment8 in varchar2 ,
5584 p_segment9 in varchar2 ,
5585 p_segment10 in varchar2 ,
5586 p_segment11 in varchar2 ,
5587 p_segment12 in varchar2 ,
5588 p_segment13 in varchar2 ,
5589 p_segment14 in varchar2 ,
5590 p_segment15 in varchar2 ,
5591 p_segment16 in varchar2 ,
5592 p_segment17 in varchar2 ,
5593 p_segment18 in varchar2 ,
5594 p_segment19 in varchar2 ,
5595 p_segment20 in varchar2 ,
5596 p_segment21 in varchar2 ,
5597 p_segment22 in varchar2 ,
5598 p_segment23 in varchar2 ,
5599 p_segment24 in varchar2 ,
5600 p_segment25 in varchar2 ,
5601 p_segment26 in varchar2 ,
5602 p_segment27 in varchar2 ,
5603 p_segment28 in varchar2 ,
5604 p_segment29 in varchar2 ,
5605 p_segment30 in varchar2 )
5606 Return Varchar2
5607 is
5608 begin
5609 --
5610 return hr_api.return_concat_kf_segments
5611 (p_id_flex_num,
5612 p_application_id,
5613 p_id_flex_code,
5614 p_segment1,
5615 p_segment2,
5616 p_segment3,
5617 p_segment4,
5618 p_segment5,
5619 p_segment6,
5620 p_segment7,
5621 p_segment8,
5622 p_segment9,
5623 p_segment10,
5624 p_segment11,
5625 p_segment12,
5626 p_segment13,
5627 p_segment14,
5628 p_segment15,
5629 p_segment16,
5630 p_segment17,
5631 p_segment18,
5632 p_segment19,
5633 p_segment20,
5634 p_segment21,
5635 p_segment22,
5636 p_segment23,
5637 p_segment24,
5638 p_segment25,
5639 p_segment26,
5640 p_segment27,
5641 p_segment28,
5642 p_segment29,
5643 p_segment30);
5644 --
5645 end return_concat_kf_segments;
5646
5647 --
5648 -- ----------------------------------------------------------------------------
5649 -- |---------------------< get_comp_obj_disp_dt >------------------------|
5650 -- ----------------------------------------------------------------------------
5651 -- {Start Of Comments}
5652 --
5653 -- Description:
5654 -- Function to return effective_date based on which the compensation object names
5655 -- can be retrieved. The function reads the profile value for BEN_DSPL_NAME_BASIS
5656 -- and based on the profile, return the correct date. Based on this date all
5657 -- Compensation Object name should be fetched.
5658 --
5659 -- Profile Value Return
5660 -- SESSION Will return the session date. All comp objects names
5661 -- displayed will be effective of session date
5662 -- LEOD Will return the Life Event Occured Date. All comp objects names
5663 -- displayed will be effective of the Life Event Occurred Date
5664 -- MXLECVG Will return the greatest of Life Event Occurred Date or the Coverage
5665 -- Start Date. All comp objects names displayed will be effective this date
5666 --
5667 --
5668 -- Pre-conditions:
5669 --
5670 -- In Arguments:
5671 --
5672 -- Post Success:
5673 --
5674 -- Post Failure:
5675 --
5676 -- Access Status:
5677 -- Internal Development Use Only.
5678 --
5679 -- {End Of Comments}
5680 -- ----------------------------------------------------------------------------
5681 FUNCTION get_comp_obj_disp_dt
5682 (p_session_date date default null,
5683 p_lf_evt_ocrd_dt date default null,
5684 p_cvg_strt_dt date default null)
5685 return date is
5686 --
5687 cursor c_session_date is
5688 select se.effective_date
5689 from fnd_sessions se
5690 where session_id = userenv('SESSIONID');
5691 --
5692 l_session_date date := p_session_date;
5693 --
5694 begin
5695 --
5696 if benutils.g_ben_dspl_name_basis is null then
5697 benutils.g_ben_dspl_name_basis := fnd_profile.value('BEN_DSPL_NAME_BASIS');
5698 end if;
5699 --
5700 if l_session_date is null then
5701 open c_session_date;
5702 fetch c_session_date into l_session_date;
5703 close c_session_date;
5704 end if;
5705 --
5706 if benutils.g_ben_dspl_name_basis = 'SESSION' then
5707 return l_session_date;
5708 elsif benutils.g_ben_dspl_name_basis = 'LEOD' then
5709 return nvl(p_lf_evt_ocrd_dt,l_session_date);
5710 elsif benutils.g_ben_dspl_name_basis = 'MXLECVG' then
5711 -- return greatest(nvl(p_cvg_strt_dt,l_session_date),nvl(p_lf_evt_ocrd_dt,l_session_date));
5712 return l_session_date;
5713 else
5714 return l_session_date;
5715 end if;
5716 --
5717 end get_comp_obj_disp_dt;
5718 --
5719
5720 --
5721 -- Overloaded Function
5722 --
5723 FUNCTION get_comp_obj_disp_dt
5724 (p_session_date date default null,
5725 p_per_in_ler_id number,
5726 p_cvg_strt_dt date default null)
5727 return date is
5728 --
5729 cursor c_per_in_ler is
5730 select pil.lf_evt_ocrd_dt
5731 from ben_per_in_ler pil
5732 where per_in_ler_id = p_per_in_ler_id;
5733 --
5734 l_lf_evt_ocrd_dt date;
5735 l_comp_obj_disp_dt date;
5736 --
5737 begin
5738 --
5739 if p_per_in_ler_id is not null then
5740 open c_per_in_ler;
5741 fetch c_per_in_ler into l_lf_evt_ocrd_dt;
5742 close c_per_in_ler;
5743 end if;
5744 --
5745 l_comp_obj_disp_dt := benutils.get_comp_obj_disp_dt(
5746 p_session_date => p_session_date,
5747 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
5748 p_cvg_strt_dt => p_cvg_strt_dt);
5749 --
5750 return l_comp_obj_disp_dt;
5751 --
5752 end get_comp_obj_disp_dt;
5753 --
5754
5755 function run_osb_benmngle_flag( p_person_id in number,
5756 p_business_group_id in number,
5757 p_effective_date in date) return boolean is
5758 --
5759 l_proc varchar2(80) := 'benutils.run_osb_benmngle_flag';
5760 l_per_last_upd_date date;
5761 l_pil_last_upd_date date;
5762 l_pil_lf_evt_ocrd_dt date;
5763 l_run_benmngle boolean := false;
5764 --
5765 cursor c_per_last_upd_date(p_pil_last_upd_date date) is
5766 select max(last_update_date)
5767 from (select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5768 from per_addresses
5769 where person_id = p_person_id
5770 and business_group_id = p_business_group_id
5771 union
5772 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5773 from per_all_assignments_f
5774 where person_id = p_person_id
5775 and business_group_id = p_business_group_id
5776 union
5777 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5778 from per_all_people_f
5779 where person_id = p_person_id
5780 and business_group_id = p_business_group_id
5781 union
5782 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5783 from per_contact_relationships
5784 where person_id = p_person_id
5785 and business_group_id = p_business_group_id
5786 union
5787 select max(nvl(psl.last_update_date,p_pil_last_upd_date)) last_update_date
5788 from per_pay_proposals psl, per_all_assignments_f asn
5789 where psl.assignment_id = asn.assignment_id
5790 and asn.person_id = p_person_id
5791 and asn.business_group_id = p_business_group_id
5792 union
5793 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5794 from per_periods_of_service
5795 where person_id = p_person_id
5796 and business_group_id = p_business_group_id
5797 union
5798 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5799 from per_qualifications
5800 where person_id = p_person_id
5801 and business_group_id = p_business_group_id
5802 union
5803 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5804 from ben_per_bnfts_bal_f
5805 where person_id = p_person_id
5806 and business_group_id = p_business_group_id
5807 union
5808 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5809 from per_absence_attendances
5810 where person_id = p_person_id
5811 and business_group_id = p_business_group_id
5812 union
5813 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
5814 from per_person_type_usages_f
5815 where person_id = p_person_id
5816 );
5817
5818 cursor c_pil_last_upd_date is
5819 select pil.lf_evt_ocrd_dt lf_evt_ocrd_dt,
5820 pil.last_update_date last_update_date
5821 from ben_per_in_ler pil , ben_ler_f ler
5822 where pil.person_id = p_person_id
5823 and pil.business_group_id = p_business_group_id
5824 and pil.per_in_ler_stat_cd = 'STRTD'
5825 and ler.ler_id = pil.ler_id
5826 and ler.typ_cd = 'SCHEDDU'
5827 and p_effective_date between ler.effective_start_date and ler.effective_end_date;
5828
5829 --
5830 begin
5831 --
5832 hr_utility.set_location('Entering:'||l_proc, 5);
5833 --
5834 -- Get the last updated date from pil record for Unrestricted LE run
5835 --
5836 open c_pil_last_upd_date;
5837 fetch c_pil_last_upd_date into l_pil_lf_evt_ocrd_dt, l_pil_last_upd_date;
5838 if c_pil_last_upd_date%NOTFOUND then
5839 --
5840 -- If Unrestricted life event was never run yet, we need to run Unrestricted
5841 -- now even if ssProcessUnrestricted flag is 'N'
5842 --
5843 l_run_benmngle := true;
5844 --
5845 else
5846 if l_pil_lf_evt_ocrd_dt is not null then
5847 --
5848 if (p_effective_date > l_pil_lf_evt_ocrd_dt) then
5849 --
5850 -- If session date is farther than the last Unrestricted run date,
5851 -- then also we need to run Unrest even if ssProcessUnrestricted flag is 'N'
5852 --
5853 hr_utility.set_location('p_effective_date = '||p_effective_date, 999);
5854 hr_utility.set_location('l_pil_lf_evt_ocrd_dt is '||l_pil_lf_evt_ocrd_dt, 999);
5855 --
5856 l_run_benmngle := true;
5857 --
5858 elsif (p_effective_date = l_pil_lf_evt_ocrd_dt
5859 and l_pil_last_upd_date is not null) then
5860 --
5861 -- If session date is same as Unrest LEOD
5862 -- then get the last updated date for Person related data changes
5863 --
5864 open c_per_last_upd_date(l_pil_last_upd_date);
5865 fetch c_per_last_upd_date into l_per_last_upd_date;
5866 close c_per_last_upd_date;
5867 --
5868 hr_utility.set_location('l_per_last_upd_date is '||l_per_last_upd_date, 999);
5869 hr_utility.set_location('l_pil_last_upd_date is '||l_pil_last_upd_date, 999);
5870 --
5871 if (nvl(l_per_last_upd_date,l_pil_last_upd_date) > l_pil_last_upd_date) then
5872 --
5873 -- If Person data has changed since the last Unrest LEOD then run benmngle
5874 --
5875 l_run_benmngle := true;
5876 end if;
5877 --
5878 else
5879 --
5880 l_run_benmngle := false;
5881 --
5882 end if;
5883 end if;
5884 --
5885 end if;
5886 close c_pil_last_upd_date;
5887 --
5888 hr_utility.set_location('Leaving:'||l_proc, 5);
5889 return l_run_benmngle;
5890 --
5891 end run_osb_benmngle_flag;
5892 --
5893 FUNCTION is_task_enabled
5894 (p_access_cd in varchar2,
5895 p_population_cd in varchar2,
5896 p_status_cd in varchar2,
5897 p_dist_bdgt_iss_dt in date,
5898 p_wksht_grp_cd in varchar2)
5899 return varchar2
5900 is
5901 begin
5902
5903 if (p_wksht_grp_cd = 'BDGT')
5904 then
5905 if (p_dist_bdgt_iss_dt is null)
5906 then
5907 return 'D';
5908 elsif (nvl(p_access_cd,'NA') = 'RO' and p_population_cd is null) then
5909 return 'D';
5910 end if;
5911 elsif (p_wksht_grp_cd = 'RVW')
5912 then
5913 if (p_status_cd = 'NS')
5914 then
5915 return 'D';
5916 end if;
5917 end if;
5918
5919 return 'Y';
5920 end is_task_enabled;
5921 --
5922 FUNCTION get_manager_name(p_emp_per_in_ler_id in number,
5923 p_level in number)
5924 return varchar2
5925 is
5926
5927 Cursor csr_mgr_name
5928 is
5929 Select bcpi.full_name,
5930 bcpi.brief_name,
5931 bcpi.custom_name
5932 From ben_cwb_person_info bcpi,
5933 ben_cwb_group_hrchy bcgh
5934 where bcgh.emp_per_in_ler_id = p_emp_per_in_ler_id
5935 and bcgh.lvl_num = (select max(lvl_num) - p_level + 1
5936 from ben_cwb_group_hrchy
5937 where emp_per_in_ler_id = p_emp_per_in_ler_id)
5938 and bcgh.lvl_num > 0
5939 and bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id;
5940
5941 name_profile varchar2(2000);
5942 manager_names csr_mgr_name%rowtype;
5943
5944 begin
5945
5946 name_profile := get_profile ('BEN_DISPLAY_EMPLOYEE_NAME');
5947
5948 open csr_mgr_name;
5949 fetch csr_mgr_name into manager_names;
5950 close csr_mgr_name;
5951
5952 if('FN' = name_profile)
5953 then
5954 return manager_names.full_name;
5955 elsif ('BN' = name_profile)
5956 then
5957 return manager_names.brief_name;
5958 else
5959 return manager_names.custom_name;
5960 end if;
5961
5962 end get_manager_name;
5963 --
5964
5965 FUNCTION get_profile(p_profile_name in varchar2)
5966 return varchar2
5967 is
5968 name_profile varchar2(2000);
5969 begin
5970 fnd_profile.get (p_profile_name, name_profile);
5971 return name_profile;
5972 end get_profile;
5973 --
5974
5975 --
5976 FUNCTION get_dpnt_prev_cvrd_flag(p_prtt_enrt_rslt_id in number,
5977 p_efective_date date,
5978 p_dpnt_person_id number,
5979 p_elig_per_elctbl_chc_id number,
5980 p_elig_cvrd_dpnt_id number,
5981 p_elig_dpnt_id number,
5982 p_per_in_ler_id number )
5983 return varchar2
5984 is
5985 l_exists_prev varchar2(30) := 'N';
5986 l_enrt_perd_strt_dt date;
5987 --
5988 cursor c_epe is
5989 select epo.ENRT_PERD_STRT_DT
5990 from ben_elig_per_elctbl_chc epe,
5991 ben_pil_elctbl_chc_popl epo
5992 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5993 and epe.pil_elctbl_chc_popl_id = epo.pil_elctbl_chc_popl_id ;
5994 --
5995 cursor c_exists_prev is
5996 select 'Y'
5997 from ben_elig_cvrd_dpnt_f pdp,
5998 ben_per_in_ler pil
5999 where pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
6000 and pdp.cvg_thru_dt = hr_api.g_eot
6001 -- and pdp.effective_end_date <> hr_api.g_eot In the unrestricted enrollment. may continue without per_in_ler update.why
6002 -- and pdp.per_in_ler_id = p_per_in_ler_id
6003 and (l_enrt_perd_strt_dt -1 ) between
6004 pdp.effective_start_date and pdp.effective_end_date
6005 and pdp.per_in_ler_id = pil.per_in_ler_id
6006 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
6007 --
6008 cursor c_exists_prev_other_option is
6009 select 'Y'
6010 from dual where exists( select null
6011 from ben_elig_cvrd_dpnt_f pdp,
6012 ben_per_in_ler pil,
6013 ben_prtt_enrt_rslt_f pnr,
6014 ben_prtt_enrt_rslt_f pen
6015 where pdp.dpnt_person_id = p_dpnt_person_id
6016 --and pdp.cvg_thru_dt = hr_api.g_eot
6017 and pdp.effective_end_date = hr_api.g_eot
6018 and pdp.prtt_enrt_rslt_id = pnr.prtt_enrt_rslt_id
6019 --and (l_enrt_perd_strt_dt -1 ) between pdp.effective_start_date
6020 -- and pdp.effective_end_date
6021 and pnr.pl_typ_id = pen.pl_typ_id
6022 and pnr.prtt_enrt_rslt_id <> pen.prtt_enrt_rslt_id
6023 and pnr.prtt_enrt_rslt_stat_cd IS NULL
6024 --and pen.prtt_enrt_rslt_stat_cd IS NULL
6025 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id -- epe.pen
6026 and pen.effective_end_date = hr_api.g_eot --new pen
6027 and pnr.effective_end_date = hr_api.g_eot --old
6028 -- and pdp.per_in_ler_id = p_per_in_ler_id -- doesnot work for LE
6029 and pdp.per_in_ler_id = pil.per_in_ler_id
6030 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') );
6031 --
6032 begin
6033 --
6034 open c_epe;
6035 fetch c_epe into l_enrt_perd_strt_dt;
6036 close c_epe;
6037 --
6038 open c_exists_prev ;
6039 fetch c_exists_prev into l_exists_prev ;
6040 close c_exists_prev ;
6041 if l_exists_prev = 'N' then
6042 --
6043 open c_exists_prev_other_option ;
6044 fetch c_exists_prev_other_option into l_exists_prev ;
6045 close c_exists_prev_other_option ;
6046 --
6047 end if;
6048 --
6049 return l_exists_prev ;
6050 --
6051 end get_dpnt_prev_cvrd_flag;
6052 --
6053 end benutils;