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