[Home] [Help]
PACKAGE BODY: APPS.BEN_GLOBAL_FUNCTIONS
Source
4 /*
1 package body ben_global_functions as
2 /* $Header: beglbfnc.pkb 120.1 2006/05/02 07:09:16 rbingi noship $ */
3 --
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 18-Dec-00 mhoyes Created.
15 115.3 02-May-06 rbingi Bug5160398: Considering rates attatched to
16 opt_id. Added proc get_vpf_par_pgm_r_pl_id
17 -----------------------------------------------------------------------------
18 */
19 --
20 -- Globals.
21 --
22 g_package varchar2(50) := 'ben_global_functions.';
23 --
24 function is_plnip_related
25 (p_pl_id in number
26 ,p_oipl_id in number
27 )
28 return varchar2
29 is
30
31 cursor c_plnip
32 (c_pl_id in number
33 )
34 is
35 select null
36 from ben_pl_f pln
37 where pln.pl_id = c_pl_id
38 and not exists
39 (select null
40 from ben_plip_f cpp
41 where pln.pl_id = cpp.pl_id);
42
43 cursor c_oiplnip
44 (c_oipl_id in number
45 )
46 is
47 select null
48 from ben_oipl_f cop
49 where cop.oipl_id = c_oipl_id
50 and not exists
51 (select null
52 from ben_plip_f cpp
53 where cop.pl_id = cpp.pl_id);
54
55 l_dummy varchar2(1);
56 l_return varchar2(1);
57
58 begin
59 --
60 l_return := 'N';
61 --
62 if p_pl_id is not null then
63 --
64 open c_plnip
65 (c_pl_id => p_pl_id
66 );
67 fetch c_plnip into l_dummy;
68 if c_plnip%found then
69 --
70 l_return := 'Y';
71 --
72 end if;
73 close c_plnip;
74 --
75 elsif p_oipl_id is not null then
76 --
77 open c_oiplnip
78 (c_oipl_id => p_oipl_id
79 );
80 fetch c_oiplnip into l_dummy;
81 if c_oiplnip%found then
82 --
83 l_return := 'Y';
84 --
85 end if;
86 close c_oiplnip;
87 --
88 end if;
89 --
90 return l_return;
91 --
92 end is_plnip_related;
93 --
94 function get_par_plnip_id
95 (p_pl_id in number
96 ,p_oipl_id in number
97 ,p_opt_id in number default null
98 )
99 return number
100 is
101
102 cursor c_plnip
103 (c_pl_id in number
104 )
105 is
106 select pln.pl_id
107 from ben_pl_f pln
108 where pln.pl_id = c_pl_id
109 and not exists
110 (select null
111 from ben_plip_f cpp
112 where pln.pl_id = cpp.pl_id);
113
114 cursor c_oiplnip
115 (c_oipl_id in number
116 )
117 is
118 select cop.pl_id
119 from ben_oipl_f cop
120 where cop.oipl_id = c_oipl_id
121 and not exists
122 (select null
123 from ben_plip_f cpp
124 where cop.pl_id = cpp.pl_id);
125
126 cursor c_opt_id
127 (c_opt_id in number
128 )
129 is
130 select pl_id
131 from ben_oipl_f cop
132 where cop.opt_id = c_opt_id
133 and not exists
134 (select null
135 from ben_plip_f
136 where cop.pl_id = cop.pl_id)
137 order by pl_id;
138
139 l_return number;
140
141 begin
142 --
143 l_return := null;
144 --
145 if p_pl_id is not null then
146 --
147 open c_plnip
148 (c_pl_id => p_pl_id
149 );
150 fetch c_plnip into l_return;
151 close c_plnip;
152 --
153 elsif p_oipl_id is not null then
154 --
155 open c_oiplnip
156 (c_oipl_id => p_oipl_id
157 );
158 fetch c_oiplnip into l_return;
159 close c_oiplnip;
160 --
164 (p_opt_id
161 elsif p_opt_id is not null then
162 --
163 open c_opt_id
165 );
166 fetch c_opt_id into l_return;
167 close c_opt_id;
168 --
169 end if;
170 --
171 return l_return;
172 --
173 end get_par_plnip_id;
174 --
175 function get_par_pgm_id
176 (p_pgm_id in number
177 ,p_ptip_id in number
178 ,p_pl_id in number
179 ,p_plip_id in number
180 ,p_oipl_id in number
181 ,p_oiplip_id in number
182 ,p_opt_id in number default null
183 )
184 return number
185 is
186
187 cursor c_oipl
188 (c_oipl_id in number
189 )
190 is
191 select cpp.pgm_id
192 from ben_oipl_f cop,
193 ben_plip_f cpp
194 where cop.oipl_id = c_oipl_id
195 and cop.pl_id = cpp.pl_id;
196
197 cursor c_oiplip
198 (c_oiplip_id in number
199 )
200 is
201 select cpp.pgm_id
202 from ben_oiplip_f opp,
203 ben_plip_f cpp
204 where opp.oiplip_id = c_oiplip_id
205 and opp.plip_id = cpp.plip_id;
206
207 cursor c_pl
208 (c_pl_id in number
209 )
210 is
211 select cpp.pgm_id
212 from ben_plip_f cpp
213 where cpp.pl_id = c_pl_id;
214
215 cursor c_plip
216 (c_plip_id in number
217 )
218 is
219 select cpp.pgm_id
220 from ben_plip_f cpp
221 where cpp.plip_id = c_plip_id;
222
223 cursor c_ptip
224 (c_ptip_id in number
225 )
226 is
227 select cpp.pgm_id
228 from ben_ptip_f cpp
229 where cpp.ptip_id = c_ptip_id;
230
231 cursor c_opt
232 (c_opt_id in number
233 )
234 is
235 select cpp.pgm_id
236 from ben_oipl_f cop,
237 ben_plip_f cpp
238 where cop.opt_id = c_opt_id
239 and cpp.pl_id = cop.pl_id
240 order by cop.oipl_id, cpp.pl_id;
241
242 l_return number;
243
244 begin
245 --
246 l_return := null;
247 --
248 if p_oipl_id is not null then
249 --
250 open c_oipl
251 (c_oipl_id => p_oipl_id
252 );
253 fetch c_oipl into l_return;
254 close c_oipl;
255 --
256 elsif p_oiplip_id is not null then
257 --
258 open c_oiplip
259 (c_oiplip_id => p_oiplip_id
260 );
261 fetch c_oiplip into l_return;
262 close c_oiplip;
263 --
264 elsif p_plip_id is not null then
265 --
266 open c_plip
267 (c_plip_id => p_plip_id
268 );
269 fetch c_plip into l_return;
270 close c_plip;
271 --
272 elsif p_pl_id is not null then
273 --
274 open c_pl
275 (c_pl_id => p_pl_id
276 );
277 fetch c_pl into l_return;
278 close c_pl;
279 --
280 elsif p_ptip_id is not null then
281 --
282 open c_ptip
283 (c_ptip_id => p_ptip_id
284 );
285 fetch c_ptip into l_return;
286 close c_ptip;
287 --
288 elsif p_pgm_id is not null then
289 --
290 l_return := p_pgm_id;
291 --
292 elsif p_opt_id is not null then
293 --
294 open c_opt
295 (c_opt_id => p_opt_id
296 );
297 fetch c_opt into l_return;
298 close c_opt;
299 --
300 end if;
301 --
302 return l_return;
303 --
304 end get_par_pgm_id;
305 --
306 function is_monetary_abr
307 (p_acty_base_rt_id in number
308 )
309 return varchar2
310 is
311
312 cursor c_abr
313 (c_abr_id in number
314 )
315 is
316 select null
317 from ben_acty_base_rt_f abr
318 where abr.acty_base_rt_id = c_abr_id
319 and nnmntry_uom is null;
320
321 l_dummy varchar2(1);
322 l_return varchar2(1);
323
324 begin
325 --
326 l_return := 'N';
327 --
328 if p_acty_base_rt_id is not null then
329 --
330 open c_abr
331 (c_abr_id => p_acty_base_rt_id
332 );
333 fetch c_abr into l_dummy;
334 if c_abr%found then
335 --
336 l_return := 'Y';
337 --
338 end if;
339 close c_abr;
340 --
341 end if;
342 --
343 return l_return;
344 --
345 end is_monetary_abr;
346 --
347 function get_abr_par_pgm_id
348 (p_acty_base_rt_id in number
349 )
350 return number
351 is
352
353 cursor c_abr
354 (c_abr_id in number
355 )
356 is
357 select abr.pgm_id,
358 abr.ptip_id,
359 abr.pl_id,
360 abr.plip_id,
361 abr.oipl_id,
362 abr.oiplip_id
363 from ben_acty_base_rt_f abr
364 where abr.acty_base_rt_id = c_abr_id;
365
366 l_abr_row c_abr%rowtype;
367
368 l_return number;
369
370 begin
371 --
372 l_return := null;
373 --
374 open c_abr
375 (c_abr_id => p_acty_base_rt_id
376 );
377 fetch c_abr into l_abr_row;
378 close c_abr;
379 --
380 if p_acty_base_rt_id is not null then
381 --
382 l_return := ben_global_functions.get_par_pgm_id
383 (p_pgm_id => l_abr_row.pgm_id
387 ,p_oipl_id => l_abr_row.oipl_id
384 ,p_ptip_id => l_abr_row.ptip_id
385 ,p_pl_id => l_abr_row.pl_id
386 ,p_plip_id => l_abr_row.plip_id
388 ,p_oiplip_id => l_abr_row.oiplip_id
389 );
390 --
391 end if;
392 --
393 return l_return;
394 --
395 end get_abr_par_pgm_id;
396 --
397 function get_abr_par_plnip_id
398 (p_acty_base_rt_id in number
399 )
400 return number
401 is
402
403 cursor c_abr
404 (c_abr_id in number
405 )
406 is
407 select abr.pgm_id,
408 abr.ptip_id,
409 abr.pl_id,
410 abr.plip_id,
411 abr.oipl_id,
412 abr.oiplip_id
413 from ben_acty_base_rt_f abr
414 where abr.acty_base_rt_id = c_abr_id;
415
416 l_abr_row c_abr%rowtype;
417
418 l_return number;
419
420 begin
421 --
422 l_return := null;
423 --
424 open c_abr
425 (c_abr_id => p_acty_base_rt_id
426 );
427 fetch c_abr into l_abr_row;
428 close c_abr;
429 --
430 if p_acty_base_rt_id is not null then
431 --
432 l_return := ben_global_functions.get_par_plnip_id
433 (p_pl_id => l_abr_row.pl_id
434 ,p_oipl_id => l_abr_row.oipl_id
435 );
436 --
437 end if;
438 --
439 return l_return;
440 --
441 end get_abr_par_plnip_id;
442 --
443 function get_ecr_abrpar_pgm_id
444 (p_enrt_rt_id in number
445 )
446 return number
447 is
448
449 cursor c_ecr
450 (c_ecr_id in number
451 )
452 is
453 select ecr.acty_base_rt_id
454 from ben_enrt_rt ecr
455 where ecr.enrt_rt_id = c_ecr_id;
456
457 l_abr_id number;
458
459 l_return number;
460
461 begin
462 --
463 l_return := null;
464 --
465 open c_ecr
466 (c_ecr_id => p_enrt_rt_id
467 );
468 fetch c_ecr into l_abr_id;
469 close c_ecr;
470 --
471 if p_enrt_rt_id is not null then
472 --
473 l_return := ben_global_functions.get_abr_par_pgm_id
474 (p_acty_base_rt_id => l_abr_id
475 );
476 --
477 end if;
478 --
479 return l_return;
480 --
481 end get_ecr_abrpar_pgm_id;
482 --
483 function get_vpf_par_pgm_r_pl_id(
484 p_vrbl_rt_prfl_id in number,
485 p_vpf_usg_cd in varchar2,
486 p_pgm_nip_lvl in varchar2
487 )
488 return number
489 is
490 cursor c_get_rt_par_pgmpl_id(p_vpf_id number) is
491 select ben_global_functions.get_par_pgm_id
492 (abr.pgm_id,abr.ptip_id,abr.pl_id,abr.plip_id,abr.oipl_id,abr.oiplip_id,abr.opt_id) pgm_id,
493 ben_global_functions.get_par_plnip_id(abr.pl_id,abr.oipl_id,abr.opt_id) nip_id
494 from ben_acty_vrbl_rt_f avr
495 , ben_acty_base_rt_f abr
496 where avr.vrbl_rt_prfl_id = p_vpf_id
497 and avr.acty_base_rt_id = abr.acty_base_rt_id
498 and abr.nnmntry_uom is null
499 and avr.effective_start_date between abr.effective_start_date
500 and abr.effective_end_date;
501 --
502 cursor c_get_cvg_par_pgmpl_id(p_vpf_id number) is
503 select ben_global_functions.get_par_pgm_id
504 (null,null,ccm.pl_id,ccm.plip_id,ccm.oipl_id,null) pgm_id,
505 ben_global_functions.get_par_plnip_id(ccm.pl_id,ccm.oipl_id) nip_id
506 from ben_bnft_vrbl_rt_f bvr
507 , ben_cvg_amt_calc_mthd_f ccm
508 where bvr.vrbl_rt_prfl_id = p_vpf_id
509 and bvr.cvg_amt_calc_mthd_id = ccm.cvg_amt_calc_mthd_id
510 and bvr.effective_start_date between ccm.effective_start_date
511 and ccm.effective_end_date;
512 --
513 cursor c_get_acp_par_pgmpl_id(p_vpf_id number) is
514 select ben_global_functions.get_par_pgm_id
515 (null,null,apr.pl_id,null,apr.oipl_id,null) pgm_id,
516 ben_global_functions.get_par_plnip_id(apr.pl_id,apr.oipl_id) pl_id
517 from ben_actl_prem_vrbl_rt_f apv
518 , ben_actl_prem_f apr
519 where apv.vrbl_rt_prfl_id = p_vpf_id
520 and apv.actl_prem_id = apr.actl_prem_id
521 and apv.effective_start_date between apr.effective_start_date
522 and apr.effective_end_date;
523 --
524 l_par_pgm_id number;
525 l_par_nip_id number;
526 --
527 begin
528 --
529 if p_vpf_usg_cd = 'RT' then
530 --
531 open c_get_rt_par_pgmpl_id(p_vrbl_rt_prfl_id);
532 fetch c_get_rt_par_pgmpl_id
533 into l_par_pgm_id
534 ,l_par_nip_id;
535 close c_get_rt_par_pgmpl_id;
536 --
537 elsif p_vpf_usg_cd = 'CVG' then
538 --
539 Open c_get_cvg_par_pgmpl_id(p_vrbl_rt_prfl_id);
540 fetch c_get_cvg_par_pgmpl_id
541 into l_par_pgm_id
542 ,l_par_nip_id;
543 close c_get_cvg_par_pgmpl_id;
544 --
545 elsif p_vpf_usg_cd = 'ACP' then
546 --
547 Open c_get_acp_par_pgmpl_id(p_vrbl_rt_prfl_id);
548 fetch c_get_acp_par_pgmpl_id
549 into l_par_pgm_id
550 ,l_par_nip_id;
551 close c_get_acp_par_pgmpl_id;
552 --
553 end if;
554 --
555 if p_pgm_nip_lvl = 'PGM' then
556 return l_par_pgm_id;
557 elsif p_pgm_nip_lvl = 'PL' then
558 return l_par_nip_id;
559 end if;
560 --
561 end get_vpf_par_pgm_r_pl_id;
562 --
563 function round_monetary_value
567 ,p_effective_date in date
564 (p_rnd_code_type in varchar2
565 ,p_rounding_cd in varchar2
566 ,p_rounding_rl in varchar2
568 ,p_monetary_value in number
569 )
570 return number
571 is
572
573 cursor c_ecr
574 (c_ecr_id in number
575 )
576 is
577 select ecr.acty_base_rt_id
578 from ben_enrt_rt ecr
579 where ecr.enrt_rt_id = c_ecr_id;
580
581 l_return number;
582
583 begin
584 --
585 if p_rounding_cd is null then
586 --
587 return p_monetary_value;
588 --
589 end if;
590 --
591 if p_rnd_code_type = 'ABR' then
592 --
593 l_return := benutils.do_rounding
594 (p_rounding_cd => p_rounding_cd
595 ,p_rounding_rl => p_rounding_rl
596 ,p_value => p_monetary_value
597 ,p_effective_date => p_effective_date
598 );
599 --
600 end if;
601 --
602 return l_return;
603 --
604 end round_monetary_value;
605 --
606 end ben_global_functions;