[Home] [Help]
PACKAGE BODY: APPS.BEN_MANAGE_OVERRIDE
Source
1 package body ben_manage_override as
2 /* $Header: benovrrd.pkb 120.0.12010000.2 2008/08/05 14:49:18 ubhat ship $ */
3 --
4 /*
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 16-Apr-02 ikasire Created .
15 115.1 20-May-02 ikasire GSCC Warnings fixed
16 115.2 07-Aug-02 ikasire Bug 2502236 fixes added call to
17 ben_determine_actual_premium.main
18 115.3 06-Dec-02 tjesumic nocopy
19 115.5 26-Dec-02 tjesumic nocopy
20 115.6 08-Apr-03 ikasire Bug 2852325 End dating the rates and dependets
21 when coverage is ended and
22 reopen_rate_and_dependents.
23 115.7 16-Apr-03 ikasire Bug 2859290 Added new procedure to handle
24 participant premium computaion. Also adde
25 calls in the end and open procedures for
26 premium computation.
27 115.8 11-Apr-04 tjesumic fonm changes
28 115.9 05-Sep-04 ikasire FIDOML Override Enhancements
29 115.10 09-Sep-04 ikasire FIDOML Bug 3882059 -- Validatation rates
30 115.11 13-Sep-04 ikasire FIDOML Need to end the elements properly when
31 any rate is changed.Bug 3888225
32 115.12 09-Feb-05 ikasire Bug 4158017 changes
33 115.13 13-Apr-05 ikasire Added a new parameter to manage_enrt_bmft call
34 115.14 22-Feb-2008 rtagarra Bug 6840074
35 -----------------------------------------------------------------------------
36 */
37 --
38 -- Globals.
39 --
40 --
41 procedure create_electable_choices
42 (p_called_from_key_listval in varchar2 default 'N'
43 ,p_person_id in number
44 ,p_per_in_ler_id in number
45 ,p_run_mode in varchar2 default 'V'
46 ,p_business_group_id in number
47 ,p_effective_date in date
48 ,p_lf_evt_ocrd_dt in date
49 ,p_ler_id in number
50 ,p_pl_id in number
51 ,p_pgm_id in number default null
52 ,p_oipl_id in number default null
53 ,p_ptip_id in number default null
54 ,p_plip_id in number default null
55 ,p_create_anyhow_flag in varchar2 default 'N'
56 ,p_asnd_lf_evt_dt in date default null
57 ,p_electable_flag out nocopy varchar2
58 ,p_elig_per_elctbl_chc_id out nocopy number
59 ,p_enrt_cvg_strt_dt out nocopy date
60 ,p_enrt_bnft_id out nocopy number
61 ,p_bnft_amt out nocopy number
62 ,p_bnft_typ_cd out nocopy varchar2
63 ,p_bnft_nnmntry_uom out nocopy varchar2
64 )
65 is
66 --Declare local variables
67 l_electable_flag varchar2(30);
68 l_elig_per_elctbl_chc_id number;
69 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
70 l_enb_valrow ben_determine_coverage.ENBValType;
71 l_date_dummy date ;
72 --
73 l_package varchar2(500) := 'Ben_Manage_Override.Create_Electable_Choices';
74 l_char_dummy varchar2(30) ;
75 l_flx_pl_id number ;
76 l_flx_elig_per_elctbl_chc_id number ;
77 l_flx_ptip_id number;
78 l_flx_plip_id number;
79 l_flx_electable_flag varchar2(30);
80 l_imp_pl_id number ;
81 l_imp_elig_per_elctbl_chc_id number ;
82 l_imp_ptip_id number;
83 l_imp_plip_id number;
84 l_imp_electable_flag varchar2(30);
85 l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
86 l_ptip_rec ben_cobj_cache.g_ptip_inst_row;
87 l_plip_rec ben_cobj_cache.g_plip_inst_row;
88 l_pl_rec ben_cobj_cache.g_pl_inst_row;
89 l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
90 --
91 cursor c_epe(p_epe_id number) is
92 select enrt_cvg_strt_dt,pl_typ_id
93 from ben_elig_per_elctbl_chc epe
94 where epe.elig_per_elctbl_chc_id = p_epe_id ;
95 --
96 l_epe c_epe%rowtype;
97 --
98 cursor c_enb(p_enrt_bnft_id number ) is
99 select val,
100 bnft_typ_cd,
101 nnmntry_uom
102 from ben_enrt_bnft enb
103 where enb.enrt_bnft_id = p_enrt_bnft_id ;
104 --
105 l_enb c_enb%rowtype;
106 --
107 cursor c_enrt_rt(p_epe_id number, p_enrt_bnft_id number ) is
108 select ecr.*
109 from ben_enrt_rt ecr
110 where ecr.elig_per_elctbl_chc_id = p_epe_id
111 union
112 select ecr.*
113 from ben_enrt_rt ecr
114 where ecr.elig_per_elctbl_chc_id is null
115 and ecr.enrt_bnft_id = p_enrt_bnft_id ;
116 --
117 cursor c_acty_base_rt(c_acty_base_rt_id number) is
118 select abr.*
119 from ben_acty_base_rt_f abr
120 where abr.acty_base_rt_id = c_acty_base_rt_id
121 and p_effective_date between abr.effective_start_date and abr.effective_end_date ;
122 --
123 cursor c_flx_epe_ecr(c_per_in_ler_id number ) is
124 select epe.elig_per_elctbl_chc_id
125 from ben_elig_per_elctbl_chc epe
126 where epe.per_in_ler_id = c_per_in_ler_id
127 and epe.bnft_prvdr_pool_id is not null
128 and epe.elig_per_elctbl_chc_id
129 not in ( select ecr.elig_per_elctbl_chc_id from ben_enrt_rt ecr
130 where ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
131 and ecr.rt_usg_cd = 'FLXCR'
132 ) ;
133 --
134 -- Cursor to check whether the program is a flex program.
135 --
136 cursor c_flx_pgm(c_pgm_id number ) is
137 select 'x'
138 from ben_pgm_f pgm
139 where
140 pgm.pgm_id = c_pgm_id
141 and pgm.pgm_typ_cd in ('COBRAFLX','FLEX', 'FPC' )
142 and p_effective_date between
143 pgm.effective_start_date and pgm.effective_end_date ;
144 -- check for shell plan
145 cursor c_flx_plan_exists is
146 select pln.pl_id,plip.plip_id,ptip.ptip_id
147 from ben_pl_f pln,
148 ben_plip_f plip,
149 ben_ptip_f ptip
150 where
151 plip.pgm_id = p_pgm_id
152 and plip.pl_id = pln.pl_id
153 and pln.invk_flx_cr_pl_flag = 'Y'
154 and pln.pl_typ_id = ptip.pl_typ_id
155 and ptip.pgm_id = p_pgm_id
156 and p_effective_date between
157 plip.effective_start_date and plip.effective_end_date
158 and p_effective_date between
159 pln.effective_start_date and pln.effective_end_date
160 and p_effective_date between
161 ptip.effective_start_date and ptip.effective_end_date
162 and not exists ( select elig_per_elctbl_chc_id
163 from ben_elig_per_elctbl_chc epe
164 where epe.pgm_id = p_pgm_id
165 and epe.pl_id = pln.pl_id
166 and epe.per_in_ler_id = p_per_in_ler_id
167 and epe.comp_lvl_cd = 'PLANFC' );
168 --check for imputed income plan
169 cursor c_imp_plan_exists is
170 select pln.pl_id,plip.plip_id,ptip.ptip_id
171 from ben_pl_f pln,
172 ben_plip_f plip,
173 ben_ptip_f ptip
174 where
175 plip.pgm_id = p_pgm_id
176 and plip.pl_id = pln.pl_id
177 and pln.imptd_incm_calc_cd is not null
178 and pln.pl_typ_id = ptip.pl_typ_id
179 and ptip.pgm_id = p_pgm_id
180 and p_effective_date between
181 plip.effective_start_date and plip.effective_end_date
182 and p_effective_date between
183 pln.effective_start_date and pln.effective_end_date
184 and p_effective_date between
185 ptip.effective_start_date and ptip.effective_end_date
186 and not exists ( select elig_per_elctbl_chc_id
187 from ben_elig_per_elctbl_chc epe
188 where epe.pgm_id = p_pgm_id
189 and epe.pl_id = pln.pl_id
190 and epe.per_in_ler_id = p_per_in_ler_id
191 and epe.comp_lvl_cd = 'PLANIMP' );
192 --
193 l_enrt_rt c_enrt_rt%rowtype;
194 l_acty_base_rt c_acty_base_rt%rowtype;
195 l_dummy_date date;
196 l_dummy_number number;
197 l_dummy_varchar2 varchar2(30);
198 l_enrt_cvg_strt_dt date;
199 --
200 begin
201 -- When a pl_id is passed,
202 -- 1. we need to create the electable choices for that comp object
203 -- 2. if that plan is subjected to imputed income and there is no epe for shell create one
204 -- 3. If the selected comp object is from a flex program, and there is no electable
205 -- choice for the shell program we need to create one for that
206 -- 4. If the selected comp object [pl_id] has option, and enroll in plan and options flag
207 -- we need to create the electable choices for the options of that plan also.
208 -- pl.ENRT_PL_OPT_FLAG is 'Y'for plan and option
209 -- 5. We create the electable choices for all the flex credits - we call the flxii everytime
210 -- if the program is not a flex program it will come out immidiately.Even if we call this
211 -- routine multiple times, there will not be any duplicate records created [make sure???]
212 --
213 -- Set the env
214 --
215 if p_called_from_key_listval = 'Y' then
216 savepoint called_from_key_listval ;
217 end if;
218 --
219 hr_utility.set_location('Entering '||l_package ,10);
220 hr_utility.set_location('Calling ben_env_object.init',20);
221 ben_env_object.init(p_business_group_id => p_business_group_id ,
222 p_effective_date => p_effective_date,
223 p_thread_id => 1,
224 p_chunk_size => 1,
225 p_threads => 1,
226 p_max_errors => 1,
227 p_benefit_action_id => null,
228 p_audit_log_flag => 'N');
229 --
230 -- Create epe /popl records
231 -- Set the cache values for bendenrr
232 l_comp_obj_tree_row.par_ptip_id := p_ptip_id;
233 l_comp_obj_tree_row.par_plip_id := p_plip_id;
234 --
235 if p_oipl_id is not null then
236 --
237 ben_cobj_cache.get_oipl_dets (
238 p_business_group_id => p_business_group_id
239 ,p_effective_date => p_effective_date
240 ,p_oipl_id => p_oipl_id
241 ,p_inst_row => l_oipl_rec
242 ) ;
243 ben_cobj_cache.g_oipl_currow := l_oipl_rec ;
244 --
245 end if ;
246 --
247 if p_pl_id is not null then
248 --
249 ben_cobj_cache.get_pl_dets (
250 p_business_group_id => p_business_group_id
251 ,p_effective_date => p_effective_date
252 ,p_pl_id => p_pl_id
253 ,p_inst_row => l_pl_rec
254 ) ;
255 ben_cobj_cache.g_pl_currow := l_pl_rec ;
256 --
257 end if ;
258 --
259 if p_plip_id is not null then
260 --
261 ben_cobj_cache.get_plip_dets (
262 p_business_group_id => p_business_group_id
263 ,p_effective_date => p_effective_date
264 ,p_plip_id => p_plip_id
265 ,p_inst_row => l_plip_rec
266 ) ;
267 ben_cobj_cache.g_plip_currow := l_plip_rec ;
268 --
269 end if ;
270 --
271 --
272 if p_ptip_id is not null then
273 --
274 ben_cobj_cache.get_ptip_dets (
275 p_business_group_id => p_business_group_id
276 ,p_effective_date => p_effective_date
277 ,p_ptip_id => p_ptip_id
278 ,p_inst_row => l_ptip_rec
279 ) ;
280 ben_cobj_cache.g_ptip_currow := l_ptip_rec ;
281 --
282 end if ;
283 --
284 if p_pgm_id is not null then
285 --
286 ben_cobj_cache.get_pgm_dets (
287 p_business_group_id => p_business_group_id
288 ,p_effective_date => p_effective_date
289 ,p_pgm_id => p_pgm_id
290 ,p_inst_row => l_pgm_rec
291 ) ;
292 ben_cobj_cache.g_pgm_currow := l_pgm_rec ;
293 --
294 end if ;
295 --
296 hr_utility.set_location('Calling ben_enrolment_requirements_ik.enrolment_requirements',30);
297 ben_enrolment_requirements.enrolment_requirements
298 (p_comp_obj_tree_row =>l_comp_obj_tree_row
299 ,p_run_mode =>p_run_mode
300 ,p_business_group_id =>p_business_group_id
301 ,p_effective_date =>p_effective_date
302 ,p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt
303 ,p_ler_id =>p_ler_id
304 ,p_per_in_ler_id =>p_per_in_ler_id
305 ,p_person_id =>p_person_id
306 ,p_pl_id =>p_pl_id
307 ,p_pgm_id =>p_pgm_id --number default null
308 ,p_oipl_id =>p_oipl_id --number default null
309 ,p_electable_flag =>l_electable_flag
310 ,p_elig_per_elctbl_chc_id =>l_elig_per_elctbl_chc_id
311 ) ;
312 p_elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id ;
313 p_electable_flag := l_electable_flag ;
314 -- Create enrt_bnft records
315 -- Set cache for bencvrge.pkb cache
316 if l_elig_per_elctbl_chc_id is not null then
317 --
318 open c_epe(l_elig_per_elctbl_chc_id);
319 fetch c_epe into l_epe ;
320 close c_epe;
321 p_enrt_cvg_strt_dt := l_epe.enrt_cvg_strt_dt;
322 --
323 ben_determine_date.rate_and_coverage_dates
324 (p_which_dates_cd => 'C'
325 ,p_business_group_id => p_business_group_id
326 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
327 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
328 ,p_enrt_cvg_strt_dt_cd => l_dummy_varchar2
329 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
330 ,p_rt_strt_dt => l_dummy_date
331 ,p_rt_strt_dt_cd => l_dummy_varchar2
332 ,p_rt_strt_dt_rl => l_dummy_number
333 ,p_enrt_cvg_end_dt => l_dummy_date
334 ,p_enrt_cvg_end_dt_cd => l_dummy_varchar2
335 ,p_enrt_cvg_end_dt_rl => l_dummy_number
336 ,p_rt_end_dt => l_dummy_date
337 ,p_rt_end_dt_cd => l_dummy_varchar2
338 ,p_rt_end_dt_rl => l_dummy_number
339 ,p_acty_base_rt_id => null
340 ,p_effective_date => p_effective_date
341 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
342 );
343 --
344 p_enrt_cvg_strt_dt := l_enrt_cvg_strt_dt;
345 --
346 ben_epe_cache.g_currcobjepe_row.pl_id := p_pl_id ;
347 ben_epe_cache.g_currcobjepe_row.plip_id := p_plip_id ;
348 ben_epe_cache.g_currcobjepe_row.oipl_id := p_oipl_id ;
349 --
350 hr_utility.set_location('Calling ben_determine_coverage.main ',40);
351 ben_determine_coverage.main
352 ( p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
353 ,p_effective_date => p_effective_date
354 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
355 ,p_perform_rounding_flg => true
356 ,p_enb_valrow => l_enb_valrow
357 );
358 --
359 -- p_enb_valrow := l_enb_valrow ;
360 --
361 if l_enb_valrow.enrt_bnft_id is not null then
362 --
363 open c_enb(l_enb_valrow.enrt_bnft_id);
364 fetch c_enb into l_enb ;
365 --
366 p_bnft_amt := l_enb.val ;
367 p_bnft_typ_cd := l_enb.bnft_typ_cd ;
368 p_bnft_nnmntry_uom := l_enb.nnmntry_uom ;
369 p_enrt_bnft_id := l_enb_valrow.enrt_bnft_id;
370 --
371 close c_enb;
372 --
373 end if;
374 --
375 hr_utility.set_location('premium calculation from Override ',111);
376 ben_determine_actual_premium.g_computed_prem_val := null ;
377 ben_determine_actual_premium.main
378 (p_person_id => p_person_id,
379 p_effective_date => p_effective_date,
380 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
381 p_pgm_id => p_pgm_id,
382 p_pl_id => p_pl_id,
383 p_pl_typ_id => l_epe.pl_typ_id,
384 p_oipl_id => p_oipl_id,
385 p_per_in_ler_id => p_per_in_ler_id,
386 p_ler_id => p_ler_id,
387 p_bnft_amt => p_bnft_amt,
388 p_business_group_id => p_business_group_id );
389 --
390 hr_utility.set_location('premium calculation from override ',112);
391 -- Create enrt_rt records
392 hr_utility.set_location('Calling ben_determine_rates.main ',50);
393 hr_utility.set_location(' l_elig_per_elctbl_chc_id '||l_elig_per_elctbl_chc_id,35);
394 --
395 ben_epe_cache.clear_down_cache ;
396 --
397 ben_determine_rates.main
398 (p_effective_date => p_effective_date
399 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
400 ,p_person_id => p_person_id
401 ,p_per_in_ler_id => p_per_in_ler_id
402 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
403 );
404 --
405 end if;
406 --
407 hr_utility.set_location(' p_pgm_id '||p_pgm_id,123);
408 if p_pgm_id is not null then
409 --
410 open c_flx_pgm(p_pgm_id);
411 fetch c_flx_pgm into l_char_dummy ;
412 if c_flx_pgm%found then
413 --
414 hr_utility.set_location(' Flex Program ',123);
415 open c_flx_plan_exists ;
416 fetch c_flx_plan_exists into l_flx_pl_id ,l_flx_plip_id,l_flx_ptip_id ;
417 if c_flx_plan_exists%found then
418 --
419 hr_utility.set_location(' No epe exists ' ,123);
420 --Call bendenrr
421 l_comp_obj_tree_row.par_ptip_id := l_flx_ptip_id;
422 l_comp_obj_tree_row.par_plip_id := l_flx_plip_id;
423 --
424 hr_utility.set_location('Call bendenrr create flexshell epefor plan '||l_flx_pl_id,30);
425 ben_enrolment_requirements.enrolment_requirements
426 (p_comp_obj_tree_row =>l_comp_obj_tree_row
427 ,p_run_mode =>p_run_mode
428 ,p_business_group_id =>p_business_group_id
429 ,p_effective_date =>p_effective_date
430 ,p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt
431 ,p_ler_id =>p_ler_id
432 ,p_per_in_ler_id =>p_per_in_ler_id
433 ,p_person_id =>p_person_id
434 ,p_pl_id =>l_flx_pl_id
435 ,p_pgm_id =>p_pgm_id --number default null
436 ,p_oipl_id =>null --number default null
437 ,p_electable_flag =>l_flx_electable_flag
438 ,p_elig_per_elctbl_chc_id =>l_flx_elig_per_elctbl_chc_id
439 ) ;
440 --Call benrates
441 --
442 if l_flx_elig_per_elctbl_chc_id is not null then
443 --
444 ben_epe_cache.clear_down_cache ;
445 --
446 ben_determine_rates.main
447 (p_effective_date => p_effective_date
448 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
449 ,p_person_id => p_person_id
450 ,p_per_in_ler_id => p_per_in_ler_id
451 ,p_elig_per_elctbl_chc_id => l_flx_elig_per_elctbl_chc_id
452 );
453 --
454 end if;
455 --
456 end if; -- -- if no electable choices exist for the flex shell plan
457 close c_flx_plan_exists ;
458 --
459 end if; -- if a the program is Flex
460 close c_flx_pgm ;
461 --
462 end if ; -- if p_pgm_id exists
463 -- We need to run only if they don't exist
464 -- If we call from Override, even called multiple times
465 -- it creates electable choises only if they don't exist.
466 ben_determine_elct_chc_flx_imp.main
467 (p_person_id => p_person_id,
468 p_business_group_id => p_business_group_id,
469 p_per_in_ler_id => p_per_in_ler_id,
470 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
471 p_enrt_perd_strt_dt => l_date_dummy, -- This is not used in benflxii.pkb
472 p_effective_date => p_effective_date,
473 p_called_from => 'O' );
474 --
475 -- To create the ben_enrt_rt records for the flex credits setup
476 --
477 for l_flx_epe in c_flx_epe_ecr( p_per_in_ler_id ) loop
478 --
479 ben_epe_cache.clear_down_cache ;
480 --
481 ben_determine_rates.main
482 (p_effective_date => p_effective_date
483 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
484 ,p_person_id => p_person_id
485 ,p_per_in_ler_id => p_per_in_ler_id
486 ,p_elig_per_elctbl_chc_id => l_flx_epe.elig_per_elctbl_chc_id
487 );
488 --
489 end loop;
490 --
491 -- imputed income epe and ecr rows
492 --
493 for r_imp_plans in c_imp_plan_exists loop
494 --
495 hr_utility.set_location(' No epe exists ' ,123);
496 --Call bendenrr
497 l_comp_obj_tree_row.par_ptip_id := r_imp_plans.ptip_id;
498 l_comp_obj_tree_row.par_plip_id := r_imp_plans.plip_id;
499 --
500 hr_utility.set_location('Call bendenrr create imp epefor plan '||r_imp_plans.pl_id,30);
501 ben_enrolment_requirements.enrolment_requirements
502 (p_comp_obj_tree_row =>l_comp_obj_tree_row
503 ,p_run_mode =>p_run_mode
504 ,p_business_group_id =>p_business_group_id
505 ,p_effective_date =>p_effective_date
506 ,p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt
507 ,p_ler_id =>p_ler_id
508 ,p_per_in_ler_id =>p_per_in_ler_id
509 ,p_person_id =>p_person_id
510 ,p_pl_id =>r_imp_plans.pl_id
511 ,p_pgm_id =>p_pgm_id
512 ,p_oipl_id =>null
513 ,p_electable_flag =>l_imp_electable_flag
514 ,p_elig_per_elctbl_chc_id =>l_imp_elig_per_elctbl_chc_id
515 ) ;
516 --Call benrates
517 --
518 if l_imp_elig_per_elctbl_chc_id is not null then
519 --
520 ben_epe_cache.clear_down_cache ;
521 --
522 ben_determine_rates.main
523 (p_effective_date => p_effective_date
524 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
525 ,p_person_id => p_person_id
526 ,p_per_in_ler_id => p_per_in_ler_id
527 ,p_elig_per_elctbl_chc_id => l_imp_elig_per_elctbl_chc_id
528 );
529 --
530 end if;
531 end loop ; --imputed income
532 --
533 hr_utility.set_location('Leaving '||l_package,60);
534 --
535 --- no copy
536 exception
537 when others then
538 p_electable_flag := null ;
539 p_elig_per_elctbl_chc_id := null ;
540 p_enrt_cvg_strt_dt := null ;
541 p_enrt_bnft_id := null ;
542 p_bnft_amt := null ;
543 p_bnft_typ_cd := null ;
544 p_bnft_nnmntry_uom := null ;
545 raise ;
546 end create_electable_choices ;
547 --
548 procedure post_override
549 ( p_elig_per_elctbl_chc_id in number
550 ,p_prtt_enrt_rslt_id in number
551 ,p_effective_date in date
552 -- for manage enrt_bnft
553 ,p_enrt_bnft_id in number default null
554 ,p_business_group_id in number
555 )
556 is
557 -- Local variables
558 l_package varchar2(500) := 'Ben_Manage_Override.post_override ';
559 l_epe_object_version_number number;
560 l_enb_object_version_number number;
561 -- cursors declaration
562 cursor c_epe is
563 select object_version_number,per_in_ler_id
564 from ben_elig_per_elctbl_chc epe
565 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
566 --
567 cursor c_enb is
568 select object_version_number
569 from ben_enrt_bnft enb
570 where enb.enrt_bnft_id = p_enrt_bnft_id ;
571 --
572 l_per_in_ler_id number;
573 --
574 begin
575 hr_utility.set_location('Entering '||l_package,10);
576 -- Update epe with the prtt_enrt_rslt_id
577 open c_epe ;
578 fetch c_epe into l_epe_object_version_number,l_per_in_ler_id ;
579 close c_epe;
580 --
581 hr_utility.set_location('Calling ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC ',20);
582 hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,22);
583 hr_utility.set_location(' l_epe_object_version_number '||l_epe_object_version_number,23);
584 --
585 ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
586 (p_validate => FALSE
587 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
588 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
589 ,p_object_version_number => l_epe_object_version_number
590 ,p_effective_date => p_effective_date
591 ,p_request_id => fnd_global.conc_request_id
592 ,p_program_application_id => fnd_global.prog_appl_id
593 ,p_program_id => fnd_global.conc_program_id
594 ,p_program_update_date => sysdate
595 );
596 --
597 -- Update enrt_bnft with the prtt_enrt_rslt_id
598 if p_enrt_bnft_id is not null then
599 --
600 open c_enb ;
601 fetch c_enb into l_enb_object_version_number ;
602 close c_enb ;
603 --
604 hr_utility.set_location('Calling ben_election_information.manage_enrt_bnft ',30);
605 ben_election_information.manage_enrt_bnft
606 ( p_enrt_bnft_id => p_enrt_bnft_id
607 ,p_effective_date => p_effective_date
608 ,p_object_version_number => l_enb_object_version_number
609 ,p_business_group_id => p_business_group_id
610 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
611 ,p_creation_date => null
612 ,p_created_by => null
613 ,p_per_in_ler_id => l_per_in_ler_id
614 );
615 --
616 end if;
617 --
618 hr_utility.set_location('Leaving '||l_package,40);
619 end post_override ;
620 --
621 -- Wrapper for update_elig_dependents call
622 --
623 procedure update_elig_dpnt
624 ( p_elig_dpnt_id in number
625 ,p_elig_cvrd_dpnt_id in number
626 ,p_effective_date in date
627 ,p_business_group_id in number
628 ,p_object_version_number in out nocopy number
629 ) is
630 l_package varchar2(500) := 'Ben_Manage_Override.update_elig_dpnt' ;
631 begin
632 --
633 hr_utility.set_location('Calling '||l_package,10);
634 ben_elig_dpnt_api.update_elig_dpnt(
635 p_elig_dpnt_id => p_elig_dpnt_id,
636 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
637 p_effective_date => p_effective_date,
638 p_business_group_id => p_business_group_id,
639 p_object_version_number => p_object_version_number,
640 p_program_application_id => fnd_global.prog_appl_id,
641 p_program_id => fnd_global.conc_program_id,
642 p_request_id => fnd_global.conc_request_id,
643 p_program_update_date => sysdate
644 );
645 hr_utility.set_location('Leaving '||l_package,20);
646 --
647 end;
648
649 procedure rollback_choices is
650 begin
651 --
652 rollback to called_from_key_listval;
653 --
654 end ;
655 -- Procedure end dating the rates and coverages when result is end dated.
656 procedure end_rate_and_dependents
657 (p_person_id in number
658 ,p_per_in_ler_id in number
659 ,p_prtt_enrt_rslt_id in number
660 ,p_enrt_cvg_thru_dt in date
661 ,p_effective_date in date
662 ) is
663 --Cursor to get the ben_prtt_rt_val records with the rt_end_dt as end of time
664 --
665 l_package varchar2(500) := 'Ben_Manage_Override.end_rate_and_dependents';
666 --
667 cursor c_prv(cv_prtt_enrt_rslt_id number) is
668 select prtt_rt_val_id,
669 object_version_number,
670 acty_base_rt_id,
671 per_in_ler_id,
672 business_group_id,
673 element_entry_value_id
674 from ben_prtt_rt_val prv
675 where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
676 and prv.prtt_rt_val_stat_cd is null
677 and prv.rt_end_dt = to_date('31/12/4712','DD/MM/RRRR') ;
678 --
679 --Cursor to get the input value and the element type id from abr
680 cursor c_abr(cv_acty_base_rt_id number,
681 cv_effective_date date ) is
682 select input_value_id,
683 element_type_id
684 from ben_acty_base_rt_f abr
685 where abr.acty_base_rt_id = cv_acty_base_rt_id
686 and cv_effective_date between abr.effective_start_date
687 and abr.effective_end_date ;
688 --
689 l_abr c_abr%ROWTYPE;
690 --
691 --Cursor to get the ben_elig_cvrd_dpnt_f with cvg_thru_dt as end of time
692 cursor c_pdp(cv_prtt_enrt_rslt_id number,
693 cv_effective_date date) is
694 select pdp.elig_cvrd_dpnt_id,
695 pdp.object_version_number,
696 pdp.business_group_id,
697 pdp.per_in_ler_id
698 from ben_elig_cvrd_dpnt_f pdp
699 where pdp.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
700 and cv_effective_date between pdp.effective_start_date
701 and pdp.effective_end_date
702 and pdp.cvg_thru_dt = to_date('31/12/4712','DD/MM/RRRR') ;
703 --
704 --Bug 2859290 Ending Coverage should end the Participant premiums also.
705 --
706 cursor c_ppe (p_ppe_dt_to_use IN DATE) is
707 select ppe.prtt_prem_id,
708 ppe.object_version_number,
709 ppe.effective_start_date,
710 ppe.effective_end_date,
711 ppe.actl_prem_id
712 from ben_prtt_prem_f ppe,
713 ben_per_in_ler pil
714 where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
715 and pil.per_in_ler_id=ppe.per_in_ler_id
716 and pil.business_group_id=ppe.business_group_id
717 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
718 and p_ppe_dt_to_use between ppe.effective_start_date and ppe.effective_end_date
719 UNION
720 select ppe1.prtt_prem_id,
721 ppe1.object_version_number,
722 ppe1.effective_start_date,
723 ppe1.effective_end_date,
724 ppe1.actl_prem_id
725 from ben_prtt_prem_f ppe1,
726 ben_per_in_ler pil
727 where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
728 and pil.per_in_ler_id=ppe1.per_in_ler_id
729 and pil.business_group_id=ppe1.business_group_id
730 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
731 and ppe1.effective_start_date > p_ppe_dt_to_use
732 and not exists
733 (select 1
734 from ben_prtt_prem_f ppe2,
735 ben_per_in_ler pil
736 where ppe2.prtt_prem_id = ppe1.prtt_prem_id
737 and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
738 and pil.per_in_ler_id=ppe2.per_in_ler_id
739 and pil.business_group_id=ppe2.business_group_id
740 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
741 and p_ppe_dt_to_use between
742 ppe2.effective_start_date and ppe2.effective_end_date);
743 --
744 l_ppe c_ppe%rowtype;
745 --
746 l_effective_start_date date;
747 l_effective_end_date date;
748 l_ppe_dt_mode varchar2(30);
749 --
750 l_zap boolean;
751 l_delete boolean;
752 l_future_change boolean;
753 l_delete_next_change boolean;
754 --
755 begin
756 hr_utility.set_location('Entering '||l_package,10);
757 -- End dependents
758 for l_pdp in c_pdp(p_prtt_enrt_rslt_id,p_effective_date) loop
759 --
760 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
761 p_elig_cvrd_dpnt_id => l_pdp.elig_cvrd_dpnt_id,
762 p_effective_start_date => l_effective_start_date,
763 p_effective_end_date => l_effective_end_date,
764 p_business_group_id => l_pdp.business_group_id,
765 p_per_in_ler_id => l_pdp.per_in_ler_id,
766 p_cvg_thru_dt => p_enrt_cvg_thru_dt,
767 p_ovrdn_flag => 'Y',
768 p_ovrdn_thru_dt => p_enrt_cvg_thru_dt,
769 p_object_version_number => l_pdp.object_version_number,
770 p_datetrack_mode => 'CORRECTION',
771 p_request_id => fnd_global.conc_request_id,
772 p_program_application_id => fnd_global.prog_appl_id,
773 p_program_id => fnd_global.conc_program_id,
774 p_program_update_date => sysdate,
775 p_effective_date => p_effective_date,
776 p_multi_row_actn => FALSE);
777 --
778 end loop ;
779 --
780 for l_prv in c_prv(p_prtt_enrt_rslt_id) loop
781 --
782 if l_prv.element_entry_value_id is not null then
783 --
784 open c_abr(l_prv.acty_base_rt_id,p_effective_date ) ;
785 fetch c_abr into l_abr ;
786 close c_abr ;
787 --
788 if l_abr.input_value_id is not null and l_abr.element_type_id is not null then
789 ben_prtt_rt_val_api.update_prtt_rt_val
790 (p_validate => false
791 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
792 ,p_rt_end_dt => p_enrt_cvg_thru_dt
793 ,p_rt_ovridn_flag => 'Y'
794 ,p_rt_ovridn_thru_dt => p_enrt_cvg_thru_dt
795 ,p_person_id => p_person_id
796 ,p_acty_base_rt_id => l_prv.acty_base_rt_id
797 ,p_input_value_id => l_abr.input_value_id
798 ,p_element_type_id => l_abr.element_type_id
799 ,p_ended_per_in_ler_id => l_prv.per_in_ler_id
800 ,p_business_group_id => l_prv.business_group_id
801 ,p_object_version_number => l_prv.object_version_number
802 ,p_effective_date => p_effective_date
803 );
804 --
805 else
806 --
807 update ben_prtt_rt_val
808 set rt_end_dt = p_enrt_cvg_thru_dt,
809 rt_ovridn_flag = 'Y',
810 rt_ovridn_thru_dt = p_enrt_cvg_thru_dt
811 where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
812 --
813 end if;
814 --
815 else
816 --
817 update ben_prtt_rt_val
818 set rt_end_dt = p_enrt_cvg_thru_dt,
819 rt_ovridn_flag = 'Y',
820 rt_ovridn_thru_dt = p_enrt_cvg_thru_dt
821 where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
822 --
823 end if;
824 --
825 end loop;
826 --
827 --Bug 2859290 End date the participant premium records also.
828 --
829 for l_ppe in c_ppe(p_enrt_cvg_thru_dt) loop
830 --
831 /*
832 ben_ppe_shd.find_dt_del_modes
833 (p_effective_date => p_enrt_cvg_thru_dt,
834 p_base_key_value => l_ppe.prtt_prem_id,
835 p_zap => l_zap,
836 p_delete => l_delete,
837 p_future_change => l_future_change,
838 p_delete_next_change => l_delete_next_change );
839 */
840 --
841 if p_enrt_cvg_thru_dt < l_ppe.effective_start_date then
842 l_ppe_dt_mode := hr_api.g_zap ;
843 else
844 l_ppe_dt_mode := hr_api.g_delete ;
845 end if;
846 --
847 if l_ppe.effective_end_date > p_enrt_cvg_thru_dt then
848 --
849 ben_prtt_prem_api.delete_prtt_prem
850 ( p_validate => false,
851 p_prtt_prem_id => l_ppe.prtt_prem_id,
852 p_object_version_number => l_ppe.object_version_number,
853 p_effective_date => p_enrt_cvg_thru_dt,
854 p_effective_start_date => l_ppe.effective_end_date,
855 p_effective_end_date => l_ppe.effective_start_date,
856 p_datetrack_mode => l_ppe_dt_mode
857 );
858 --
859 end if;
860 --
861 end loop ;
862 --
863 hr_utility.set_location('Leaving '||l_package,20);
864 --
865 end end_rate_and_dependents;
866 --
867 -- Procedure reopen the rates and coverages when result is end dated.
868 procedure reopen_rate_and_dependents
869 (p_person_id in number
870 ,p_per_in_ler_id in number
871 ,p_prtt_enrt_rslt_id in number
872 ,p_effective_date in date
873 ) is
874 --Cursor to get the ben_prtt_rt_val records with the rt_end_dt as not end of time
875 --
876 l_package varchar2(500) := 'Ben_Manage_Override.reopen_rate_and_dependents';
877 --
878 cursor c_prv(cv_prtt_enrt_rslt_id number) is
879 select prtt_rt_val_id,
880 object_version_number,
881 acty_base_rt_id,
882 per_in_ler_id,
883 business_group_id,
884 element_entry_value_id
885 from ben_prtt_rt_val prv
886 where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
887 and prv.prtt_rt_val_stat_cd is null
888 and prv.rt_end_dt <> to_date('31/12/4712','DD/MM/RRRR') and
889 not exists ( select 'x' from
890 ben_prtt_rt_val prv1
891 where prv.rowid <> prv1.rowid
892 and prv1.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
893 and prv1.acty_base_rt_id = prv.acty_base_rt_id
894 and prv1.prtt_rt_val_stat_cd is null
895 and prv1.rt_strt_dt > prv.rt_strt_dt ) ;
896 --
897 --Cursor to get the input value and the element type id from abr
898 cursor c_abr(cv_acty_base_rt_id number,
899 cv_effective_date date ) is
900 select input_value_id,
901 element_type_id
902 from ben_acty_base_rt_f abr
903 where abr.acty_base_rt_id = cv_acty_base_rt_id
904 and cv_effective_date between abr.effective_start_date
905 and abr.effective_end_date ;
906 --
907 l_abr c_abr%ROWTYPE;
908 --
909 --Cursor to get the ben_elig_cvrd_dpnt_f with cvg_thru_dt as end of time
910 cursor c_pdp(cv_prtt_enrt_rslt_id number,
911 cv_effective_date date) is
912 select pdp.elig_cvrd_dpnt_id,
913 pdp.object_version_number,
914 pdp.business_group_id,
915 pdp.per_in_ler_id
916 from ben_elig_cvrd_dpnt_f pdp
917 where pdp.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
918 and cv_effective_date between pdp.effective_start_date
919 and pdp.effective_end_date
920 and pdp.cvg_thru_dt <> to_date('31/12/4712','DD/MM/RRRR')
921 and not exists (
922 select 'x' from ben_elig_cvrd_dpnt_f pdp1
923 where pdp.elig_cvrd_dpnt_id = pdp1.elig_cvrd_dpnt_id
924 and pdp.rowid <> pdp1.rowid
925 and pdp1.effective_start_date > pdp.effective_start_date
926 and pdp1.cvg_strt_dt > pdp.cvg_thru_dt ) ;
927 --
928
929 --Bug 2859290 Ending Coverage should end the Participant premiums also.
930 --
931 cursor c_ppe (p_ppe_dt_to_use IN DATE) is
932 select ppe.prtt_prem_id,
933 ppe.object_version_number,
934 ppe.effective_start_date,
935 ppe.effective_end_date,
936 ppe.actl_prem_id,
937 ppe.business_group_id
938 from ben_prtt_prem_f ppe,
939 ben_per_in_ler pil
940 where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
941 and pil.per_in_ler_id=ppe.per_in_ler_id
942 and pil.business_group_id=ppe.business_group_id
943 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
944 and p_ppe_dt_to_use between ppe.effective_start_date and ppe.effective_end_date
945 and ppe.effective_end_date <> hr_api.g_eot
946 UNION
947 select ppe1.prtt_prem_id,
948 ppe1.object_version_number,
949 ppe1.effective_start_date,
950 ppe1.effective_end_date,
951 ppe1.actl_prem_id,
952 ppe1.business_group_id
953 from ben_prtt_prem_f ppe1,
954 ben_per_in_ler pil
955 where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
956 and pil.per_in_ler_id=ppe1.per_in_ler_id
957 and pil.business_group_id=ppe1.business_group_id
958 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
959 and ppe1.effective_start_date > p_ppe_dt_to_use
960 and ppe1.effective_end_date <> hr_api.g_eot
961 and not exists
962 (select 1
963 from ben_prtt_prem_f ppe2,
964 ben_per_in_ler pil
965 where ppe2.prtt_prem_id = ppe1.prtt_prem_id
966 and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
967 and pil.per_in_ler_id=ppe2.per_in_ler_id
968 and pil.business_group_id=ppe2.business_group_id
969 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
970 and p_ppe_dt_to_use between
971 ppe2.effective_start_date and ppe2.effective_end_date
972 and ppe2.effective_end_date <> hr_api.g_eot );
973 --
974 l_ppe c_ppe%rowtype;
975 l_effective_start_date date;
976 l_effective_end_date date;
977 --
978 l_zap boolean;
979 l_delete boolean;
980 l_future_change boolean;
981 l_delete_next_change boolean;
982 --
983 begin
984 hr_utility.set_location('Entering '||l_package,10);
985 -- End dependents
986 for l_pdp in c_pdp(p_prtt_enrt_rslt_id,p_effective_date) loop
987 --
988 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
989 p_elig_cvrd_dpnt_id => l_pdp.elig_cvrd_dpnt_id,
990 p_effective_start_date => l_effective_start_date,
991 p_effective_end_date => l_effective_end_date,
992 p_business_group_id => l_pdp.business_group_id,
993 p_per_in_ler_id => l_pdp.per_in_ler_id,
994 p_cvg_thru_dt => to_date('31/12/4712','DD/MM/RRRR'),
995 p_ovrdn_flag => 'Y',
996 p_ovrdn_thru_dt => null,
997 p_object_version_number => l_pdp.object_version_number,
998 p_datetrack_mode => 'CORRECTION',
999 p_request_id => fnd_global.conc_request_id,
1000 p_program_application_id => fnd_global.prog_appl_id,
1001 p_program_id => fnd_global.conc_program_id,
1002 p_program_update_date => sysdate,
1003 p_effective_date => p_effective_date,
1004 p_multi_row_actn => FALSE);
1005 --
1006 end loop ;
1007 --
1008 for l_prv in c_prv(p_prtt_enrt_rslt_id) loop
1009 --
1010 if l_prv.element_entry_value_id is not null then
1011 --
1012 open c_abr(l_prv.acty_base_rt_id,p_effective_date ) ;
1013 fetch c_abr into l_abr ;
1014 close c_abr ;
1015 --
1016 if l_abr.input_value_id is not null and l_abr.element_type_id is not null then
1017 ben_prtt_rt_val_api.update_prtt_rt_val
1018 (p_validate => false
1019 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
1020 ,p_rt_end_dt => to_date('31/12/4712','DD/MM/RRRR')
1021 ,p_rt_ovridn_flag => 'Y'
1022 ,p_rt_ovridn_thru_dt => null
1023 ,p_person_id => p_person_id
1024 ,p_acty_base_rt_id => l_prv.acty_base_rt_id
1025 ,p_input_value_id => l_abr.input_value_id
1026 ,p_element_type_id => l_abr.element_type_id
1027 ,p_ended_per_in_ler_id => l_prv.per_in_ler_id
1028 ,p_business_group_id => l_prv.business_group_id
1029 ,p_object_version_number => l_prv.object_version_number
1030 ,p_effective_date => p_effective_date
1031 );
1032 --
1033 else
1034 --
1035 update ben_prtt_rt_val
1036 set rt_end_dt = to_date('31/12/4712','DD/MM/RRRR'),
1037 rt_ovridn_flag = 'Y',
1038 rt_ovridn_thru_dt = null
1039 where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
1040 --
1041 end if;
1042 --
1043 else
1044 --
1045 update ben_prtt_rt_val
1046 set rt_end_dt = to_date('31/12/4712','DD/MM/RRRR'),
1047 rt_ovridn_flag = 'Y',
1048 rt_ovridn_thru_dt = null
1049 where prtt_rt_val_id = l_prv.prtt_rt_val_id ;
1050 --
1051 end if;
1052 --
1053 end loop;
1054 --
1055 --Bug 2859290 reopen the participant premium records also.
1056 --
1057 for l_ppe in c_ppe(p_effective_date) loop
1058 --
1059 ben_ppe_shd.find_dt_del_modes
1060 (p_effective_date => l_ppe.effective_end_date,
1061 p_base_key_value => l_ppe.prtt_prem_id,
1062 p_zap => l_zap,
1063 p_delete => l_delete,
1064 p_future_change => l_future_change,
1065 p_delete_next_change => l_delete_next_change );
1066 --
1067 if l_future_change then
1068 ben_prtt_prem_api.delete_prtt_prem
1069 ( p_validate => false,
1070 p_prtt_prem_id => l_ppe.prtt_prem_id,
1071 p_object_version_number => l_ppe.object_version_number,
1072 p_effective_date => l_ppe.effective_end_date,
1073 p_effective_start_date => l_effective_end_date,
1074 p_effective_end_date => l_effective_start_date,
1075 p_datetrack_mode => hr_api.g_future_change
1076 );
1077 --
1078 end if;
1079 --
1080 end loop ;
1081 --
1082 hr_utility.set_location('Leaving '||l_package,20);
1083 --
1084 end reopen_rate_and_dependents;
1085 --
1086 procedure override_prtt_prem
1087 (p_person_id in number
1088 ,p_per_in_ler_id in number
1089 ,p_pgm_id in number default null
1090 ,p_pl_id in number
1091 ,p_oipl_id in number default null
1092 ,p_enrt_bnft_id in number default null
1093 ,p_prtt_enrt_rslt_id in number
1094 ,p_elig_per_elctbl_chc_id in number
1095 ,p_effective_date in date
1096 ,p_business_group_id in number
1097 ,p_enrt_cvg_strt_dt in date
1098 ) is
1099 --cursors
1100 --
1101 l_package varchar2(500) := 'Ben_Manage_Override.override_prtt_prem';
1102 --
1103 cursor c_prem is
1104 select ecr.val,
1105 ecr.uom,
1106 ecr.actl_prem_id,
1107 pil.lf_evt_ocrd_dt,
1108 pil.ler_id,
1109 epe.elig_per_elctbl_chc_id
1110 from ben_enrt_prem ecr,
1111 ben_per_in_ler pil,
1112 ben_elig_per_elctbl_chc epe
1113 where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1114 and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
1115 and pil.per_in_ler_id = epe.per_in_ler_id
1116 and pil.per_in_ler_id = p_per_in_ler_id ;
1117 --
1118 l_prem c_prem%rowtype;
1119 --
1120 cursor c_ppe (p_prtt_enrt_rslt_id in number,
1121 p_actl_prem_id in number,
1122 p_ppe_dt_to_use in date) is
1123 select ppe.prtt_prem_id,
1124 ppe.std_prem_uom,
1125 ppe.std_prem_val,
1126 ppe.actl_prem_id,
1127 ppe.object_version_number,
1128 ppe.effective_start_date
1129 from ben_prtt_prem_f ppe,
1130 ben_per_in_ler pil
1131 where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1132 and ppe.actl_prem_id = p_actl_prem_id
1133 and p_ppe_dt_to_use between
1134 ppe.effective_start_date and ppe.effective_end_date
1135 and pil.per_in_ler_id=ppe.per_in_ler_id
1136 and pil.business_group_id=ppe.business_group_id
1137 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1138 ;
1139 --
1140 l_ppe c_ppe%rowtype;
1141 --
1142 cursor c_pel is
1143 select pel.enrt_perd_id,
1144 pel.lee_rsn_id
1145 from ben_pil_elctbl_chc_popl pel,
1146 ben_elig_per_elctbl_chc epe
1147 where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1148 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
1149
1150 l_pel c_pel%rowtype;
1151 --
1152 l_ppe_datetrack_mode varchar2(30);
1153 l_ppe_dt_to_use date;
1154 lb_correction boolean;
1155 lb_update boolean;
1156 lb_update_override boolean;
1157 lb_update_change_insert boolean;
1158 l_enrt_cvg_strt_dt date;
1159 l_enrt_cvg_strt_dt_cd varchar2(30);
1160 l_enrt_cvg_strt_dt_rl number;
1161 l_rt_strt_dt date;
1162 l_rt_strt_dt_cd varchar2(30);
1163 l_rt_strt_dt_rl number;
1164 l_enrt_cvg_end_dt date;
1165 l_enrt_cvg_end_dt_cd varchar2(30);
1166 l_enrt_cvg_end_dt_rl number;
1167 l_rt_end_dt date;
1168 l_rt_end_dt_cd varchar2(30);
1169 l_rt_end_dt_rl number;
1170 --
1171 l_datetrack_mode varchar2(30) ;
1172 l_correction boolean;
1173 l_update boolean;
1174 l_update_override boolean;
1175 l_update_change_insert boolean;
1176 l_step varchar2(30);
1177 l_effective_start_date date;
1178 l_effective_end_date date;
1179 begin
1180 hr_utility.set_location('Entering '||l_package,10);
1181 for l_prem in c_prem loop
1182 l_step := 70;
1183 l_ppe.prtt_prem_id:=null;
1184 open c_pel;
1185 fetch c_pel into l_pel;
1186 close c_pel;
1187
1188 ben_determine_date.rate_and_coverage_dates
1189 (p_which_dates_cd => 'R'
1190 ,p_date_mandatory_flag => 'Y'
1191 ,p_compute_dates_flag => 'Y'
1192 ,p_business_group_id => p_business_group_id
1193 ,P_PER_IN_LER_ID => p_per_in_ler_id
1194 ,P_PERSON_ID => p_person_id
1195 ,P_PGM_ID => p_pgm_id
1196 ,P_PL_ID => p_pl_id
1197 ,P_OIPL_ID => p_oipl_id
1198 ,P_LEE_RSN_ID => l_pel.lee_rsn_id
1199 ,P_ENRT_PERD_ID => l_pel.enrt_perd_id
1200 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt --out
1201 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd --out
1202 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl --out
1203 ,p_rt_strt_dt => l_rt_strt_dt --out
1204 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd --out
1205 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl --out
1206 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt --out
1207 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd --out
1208 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl --out
1209 ,p_rt_end_dt => l_rt_end_dt --out
1210 ,p_rt_end_dt_cd => l_rt_end_dt_cd --out
1211 ,p_rt_end_dt_rl => l_rt_end_dt_rl --out
1212 ,p_effective_date => p_effective_date
1213 ,p_lf_evt_ocrd_dt => nvl(l_prem.lf_evt_ocrd_dt,p_effective_date)
1214 );
1215
1216 l_ppe_dt_to_use := greatest(p_enrt_cvg_strt_dt,l_rt_strt_dt);
1217 open c_ppe(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use);
1218 fetch c_ppe into l_ppe;
1219 close c_ppe;
1220 l_step := 71;
1221 if l_ppe.prtt_prem_id is not null then
1222 -- Because the benefit amount could have changed, and the premiums
1223 -- can be based on the benefit amount, re-calc it. It does a recalc
1224 -- if the benefit amount is entered at enrollment.
1225 -- PPE is from prtt-prem. prem is from enrt-prem.
1226 ben_PRTT_PREM_api.recalc_PRTT_PREM
1227 (p_prtt_prem_id => l_ppe.prtt_prem_id
1228 ,p_std_prem_uom => l_prem.uom
1229 ,p_std_prem_val => l_prem.val -- in/out
1230 ,p_actl_prem_id => l_prem.actl_prem_id
1231 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1232 ,p_per_in_ler_id => p_per_in_ler_id
1233 ,p_ler_id => l_prem.ler_id
1234 ,p_lf_evt_ocrd_dt => l_prem.lf_evt_ocrd_dt
1235 ,p_elig_per_elctbl_chc_id => l_prem.elig_per_elctbl_chc_id
1236 ,p_enrt_bnft_id => p_enrt_bnft_id
1237 ,p_business_group_id => p_business_group_id
1238 ,p_effective_date => p_effective_date
1239 -- bof FONM
1240 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1241 ,p_rt_strt_dt => l_rt_strt_dt
1242 -- eof FONM
1243 );
1244
1245 l_step := 72;
1246 --
1247 -- Find the valid datetrack modes.
1248 --
1249 dt_api.find_dt_upd_modes
1250 (p_effective_date => l_ppe_dt_to_use,
1251 p_base_table_name => 'BEN_PRTT_PREM_F',
1252 p_base_key_column => 'prtt_prem_id',
1253 p_base_key_value => l_ppe.prtt_prem_id,
1254 p_correction => l_correction,
1255 p_update => l_update,
1256 p_update_override => l_update_override,
1257 p_update_change_insert => l_update_change_insert);
1258
1259 if l_update_override then
1260 --
1261 l_ppe_datetrack_mode := hr_api.g_update_override;
1262 --
1263 elsif l_update then
1264 --
1265 l_ppe_datetrack_mode := hr_api.g_update;
1266 --
1267 else
1268 --
1269 l_ppe_datetrack_mode := hr_api.g_correction;
1270 end if;
1271 --
1272 ben_prtt_prem_api.update_prtt_prem
1273 ( p_validate => FALSE
1274 ,p_prtt_prem_id => l_ppe.prtt_prem_id
1275 ,p_effective_start_date => l_effective_start_date
1276 ,p_effective_end_date => l_effective_end_date
1277 ,p_std_prem_uom => l_prem.uom
1278 ,p_std_prem_val => l_prem.val
1279 ,p_actl_prem_id => l_prem.actl_prem_id
1280 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1281 ,p_per_in_ler_id => p_per_in_ler_id
1282 ,p_business_group_id => p_business_group_id
1283 ,p_object_version_number => l_ppe.object_version_number
1284 ,p_request_id => fnd_global.conc_request_id
1285 ,p_program_application_id => fnd_global.prog_appl_id
1286 ,p_program_id => fnd_global.conc_program_id
1287 ,p_program_update_date => sysdate
1288 ,p_effective_date => l_ppe_dt_to_use
1289 ,p_datetrack_mode => l_ppe_datetrack_mode
1290 );
1291 else
1292 ben_PRTT_PREM_api.recalc_PRTT_PREM
1293 (p_prtt_prem_id => null
1294 ,p_std_prem_uom => l_prem.uom
1295 ,p_std_prem_val => l_prem.val -- in/out
1296 ,p_actl_prem_id => l_prem.actl_prem_id
1297 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1298 ,p_per_in_ler_id => p_per_in_ler_id
1299 ,p_ler_id => l_prem.ler_id
1300 ,p_lf_evt_ocrd_dt => l_prem.lf_evt_ocrd_dt
1301 ,p_elig_per_elctbl_chc_id => l_prem.elig_per_elctbl_chc_id
1302 ,p_enrt_bnft_id => p_enrt_bnft_id
1303 ,p_business_group_id => p_business_group_id
1304 ,p_effective_date => p_effective_date
1305 -- bof FONM
1306 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1307 ,p_rt_strt_dt => l_rt_strt_dt
1308 -- eof FONM
1309 );
1310 l_step := 130;
1311 ben_prtt_prem_api.create_prtt_prem
1312 ( p_validate => FALSE
1313 ,p_prtt_prem_id => l_ppe.prtt_prem_id
1314 ,p_effective_start_date => l_effective_start_date
1315 ,p_effective_end_date => l_effective_end_date
1316 ,p_std_prem_uom => l_prem.uom
1317 ,p_std_prem_val => l_prem.val
1318 ,p_actl_prem_id => l_prem.actl_prem_id
1319 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1320 ,p_per_in_ler_id => p_per_in_ler_id
1321 ,p_business_group_id => p_business_group_id
1322 ,p_object_version_number => l_ppe.object_version_number
1323 ,p_request_id => fnd_global.conc_request_id
1324 ,p_program_application_id => fnd_global.prog_appl_id
1325 ,p_program_id => fnd_global.conc_program_id
1326 ,p_program_update_date => sysdate
1327 ,p_effective_date => l_ppe_dt_to_use
1328 );
1329 --
1330 end if;
1331 end loop;
1332 hr_utility.set_location('Leaving '||l_package,20);
1333 end override_prtt_prem ;
1334 --
1335 procedure correct_prtt_enrt_rslt
1336 (p_prtt_enrt_rslt_id in number
1337 ,p_enrt_cvg_strt_dt in date default hr_api.g_date
1338 ,p_enrt_cvg_thru_dt in date default hr_api.g_date
1339 ,p_bnft_amt in number default hr_api.g_number
1340 ,p_enrt_ovridn_flag in varchar2 default hr_api.g_varchar2
1341 ,p_enrt_ovrid_thru_dt in date default hr_api.g_date
1342 ,p_enrt_ovrid_rsn_cd in varchar2 default hr_api.g_varchar2
1343 ,p_orgnl_enrt_dt in date default hr_api.g_date
1344 ,p_effective_date in date
1345 ) is
1346 --
1347 cursor c_pen(p_prtt_enrt_rslt_id number) is
1348 select *
1349 from ben_prtt_enrt_rslt_f pen
1350 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1351 and pen.prtt_enrt_rslt_stat_cd IS NULL
1352 order by pen.effective_start_date;
1353 --
1354 l_effective_date date;
1355 l_object_version_number number;
1356 l_dummy_date date;
1357 l_desired_datetrack_mode varchar2(30) := hr_api.g_correction;
1358 l_datetrack_mode varchar2(30);
1359 --
1360 begin
1361 --
1362 for l_pen in c_pen( p_prtt_enrt_rslt_id ) loop
1363 --
1364 if p_effective_date < l_pen.effective_start_date OR
1365 p_effective_date > l_pen.effective_end_date THEN
1366 --
1367 ben_prtt_enrt_result_api.get_ben_pen_upd_dt_mode
1368 (p_effective_date => l_pen.effective_start_date
1369 ,p_base_key_value => p_prtt_enrt_rslt_id
1370 ,P_desired_datetrack_mode => l_desired_datetrack_mode
1371 ,P_datetrack_allow => l_datetrack_mode
1372 );
1373 --
1374 hr_utility.set_location('l_datetrack_mode '||l_datetrack_mode,10);
1375 l_object_version_number := l_pen.object_version_number;
1376 --
1377 ben_prtt_enrt_result_api.update_prtt_enrt_result
1378 (p_validate => FALSE,
1379 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
1380 p_effective_start_date => l_dummy_date,
1381 p_effective_end_date => l_dummy_date,
1382 p_business_group_id => l_pen.business_group_id,
1383 p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
1384 -- p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt, This needs to be updated for the latest only
1385 p_bnft_amt => p_bnft_amt,
1386 p_enrt_ovridn_flag => p_enrt_ovridn_flag,
1387 p_enrt_ovrid_thru_dt => p_enrt_ovrid_thru_dt,
1388 p_enrt_ovrid_rsn_cd => p_enrt_ovrid_rsn_cd,
1389 p_orgnl_enrt_dt => p_orgnl_enrt_dt,
1390 p_object_version_number => l_object_version_number,
1391 p_effective_date => l_pen.effective_start_date,
1392 p_datetrack_mode => l_datetrack_mode,
1393 p_multi_row_validate => FALSE,
1394 p_program_application_id => fnd_global.prog_appl_id,
1395 p_program_id => fnd_global.conc_program_id,
1396 p_request_id => fnd_global.conc_request_id,
1397 p_program_update_date => sysdate);
1398 --
1399 end if;
1400 --
1401 end loop;
1402 --
1403 end correct_prtt_enrt_rslt;
1404 --
1405 procedure override_debit_ledger_entry
1406 (p_validate in boolean default false
1407 ,p_calculate_only_mode in boolean default false
1408 ,p_person_id in number
1409 ,p_per_in_ler_id in number
1410 ,p_elig_per_elctbl_chc_id in number
1411 ,p_prtt_enrt_rslt_id in number
1412 ,p_decr_bnft_prvdr_pool_id in number
1413 ,p_acty_base_rt_id in number
1414 ,p_prtt_rt_val_id in number
1415 ,p_enrt_mthd_cd in varchar2
1416 ,p_val in number
1417 ,p_bnft_prvdd_ldgr_id in out nocopy number
1418 ,p_business_group_id in number
1419 ,p_effective_date in date
1420 --
1421 ,p_bpl_used_val out nocopy number
1422 ) is
1423 --
1424 l_package varchar2(500) := 'Ben_Manage_Override.override_debit_ledger_entry';
1425 l_epe_rec ben_epe_shd.g_rec_type;
1426 l_ecr_rec ben_ecr_shd.g_rec_type;
1427 l_prtt_enrt_rslt_id number;
1428 l_prtt_rt_val_id number;
1429 l_pgm_id number;
1430 --
1431 l_effective_start_date date;
1432 l_effective_end_date date;
1433 l_object_version_number number;
1434 l_val number;
1435 l_datetrack_mode varchar2(30);
1436 --
1437 l_correction boolean := TRUE;
1438 l_update boolean := FALSE;
1439 l_update_override boolean := FALSE;
1440 l_update_change_insert boolean := FALSE;
1441 --
1442 l_per_in_ler_id number;
1443 l_used_val number := 0;
1444
1445 cursor c_old_ledger is
1446 select bpl.bnft_prvdd_ldgr_id,
1447 bpl.per_in_ler_id,
1448 bpl.used_val,
1449 bpl.object_version_number,
1450 bpl.effective_start_date
1451 from ben_bnft_prvdd_ldgr_f bpl,
1452 ben_per_in_ler pil
1453 where bpl.bnft_prvdr_pool_id=l_ecr_rec.decr_bnft_prvdr_pool_id
1454 and bpl.business_group_id=l_epe_rec.business_group_id
1455 and bpl.acty_base_rt_id=l_ecr_rec.acty_base_rt_id
1456 and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
1457 and bpl.used_val is not null
1458 and p_effective_date between bpl.effective_start_date
1459 and bpl.effective_end_date
1460 and pil.per_in_ler_id=bpl.per_in_ler_id
1461 and pil.business_group_id=bpl.business_group_id
1462 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
1463 --
1464 cursor c_prtt_rt_val
1465 is
1466 select rt_val
1467 from ben_prtt_rt_val
1468 where prtt_rt_val_id=l_ecr_rec.prtt_rt_val_id;
1469 --
1470 begin
1471 --
1472 hr_utility.set_location('Entering:'|| l_package, 15);
1473 --
1474 l_epe_rec.elig_per_elctbl_chc_id:= p_elig_per_elctbl_chc_id;
1475 l_epe_rec.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1476 l_epe_rec.business_group_id := p_business_group_id;
1477 l_epe_rec.per_in_ler_id := p_per_in_ler_id;
1478 --
1479 l_ecr_rec.decr_bnft_prvdr_pool_id := p_decr_bnft_prvdr_pool_id;
1480 l_ecr_rec.acty_base_rt_id := p_acty_base_rt_id;
1481 l_ecr_rec.prtt_rt_val_id := p_prtt_rt_val_id;
1482 l_ecr_rec.val := p_val;
1483 --
1484 if l_ecr_rec.prtt_rt_val_id is null then
1485 l_val:=l_ecr_rec.val;
1486 else
1487 open c_prtt_rt_val ;
1488 fetch c_prtt_rt_val into l_val;
1489 if c_prtt_rt_val%notfound then
1490 l_val:=l_ecr_rec.val;
1491 end if;
1492 close c_prtt_rt_val;
1493 end if;
1494 --
1495 open c_old_ledger;
1496 fetch c_old_ledger into
1497 p_bnft_prvdd_ldgr_id,
1498 l_per_in_ler_id,
1499 l_used_val,
1500 l_object_version_number,
1501 l_effective_start_date;
1502 --
1503 if not p_calculate_only_mode then
1504 --
1505 if c_old_ledger%notfound then
1506 ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
1507 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1508 ,p_effective_start_date => l_effective_start_date
1509 ,p_effective_end_date => l_effective_end_date
1510 ,p_prtt_ro_of_unusd_amt_flag => 'N'
1511 ,p_frftd_val => null
1512 ,p_prvdd_val => null
1513 ,p_used_val => l_val
1514 ,p_bnft_prvdr_pool_id => l_ecr_rec.decr_bnft_prvdr_pool_id
1515 ,p_acty_base_rt_id => l_ecr_rec.acty_base_rt_id
1516 ,p_per_in_ler_id => l_epe_rec.per_in_ler_id
1517 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1518 ,p_person_id => p_person_id
1519 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1520 ,p_business_group_id => l_epe_rec.business_group_id
1521 ,p_object_version_number => l_object_version_number
1522 ,p_cash_recd_val => null
1523 ,p_effective_date => p_effective_date
1524 );
1525 hr_utility.set_location('CREATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),41);
1526 else
1527 --
1528 DT_Api.Find_DT_Upd_Modes(
1529 p_effective_date => p_effective_date,
1530 p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1531 p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
1532 p_base_key_value => p_bnft_prvdd_ldgr_id,
1533 p_correction => l_correction,
1534 p_update => l_update,
1535 p_update_override => l_update_override,
1536 p_update_change_insert => l_update_change_insert);
1537 --
1538 if l_update_override or l_update_change_insert then
1539 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1540 elsif l_update then
1541 l_datetrack_mode := 'UPDATE';
1542 elsif l_correction then
1543 l_datetrack_mode := 'CORRECTION';
1544 end if;
1545 --
1546 if l_per_in_ler_id <> l_epe_rec.per_in_ler_id or
1547 l_used_val <> l_val then
1548 --
1549 hr_utility.set_location('UPDATING LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),51);
1550 --
1551 ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
1552 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1553 ,p_effective_start_date => l_effective_start_date
1554 ,p_effective_end_date => l_effective_end_date
1555 ,p_frftd_val => null
1556 ,p_prvdd_val => null
1557 ,p_used_val => l_val
1558 ,p_bnft_prvdr_pool_id => l_ecr_rec.decr_bnft_prvdr_pool_id
1559 ,p_acty_base_rt_id => l_ecr_rec.acty_base_rt_id
1560 ,p_per_in_ler_id => l_epe_rec.per_in_ler_id
1561 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1562 ,p_business_group_id => l_epe_rec.business_group_id
1563 ,p_object_version_number => l_object_version_number
1564 ,p_cash_recd_val => null
1565 ,p_effective_date => p_effective_date
1566 ,p_datetrack_mode => l_datetrack_mode
1567 );
1568 hr_utility.set_location('UPDATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),55);
1569 end if;
1570 end if;
1571 close c_old_ledger;
1572 end if;
1573 --
1574 end override_debit_ledger_entry ;
1575 --
1576 procedure override_prtt_rt_val
1577 (
1578 p_validate in boolean default false
1579 ,p_prtt_rt_val_id in number
1580 ,p_person_id in number default hr_api.g_number
1581 ,p_input_value_id in number default hr_api.g_number
1582 ,p_element_type_id in number default hr_api.g_number
1583 ,p_enrt_rt_id in number default hr_api.g_number
1584 ,p_rt_strt_dt in date default hr_api.g_date
1585 ,p_rt_end_dt in date default hr_api.g_date
1586 ,p_rt_typ_cd in varchar2 default hr_api.g_varchar2
1587 ,p_tx_typ_cd in varchar2 default hr_api.g_varchar2
1588 ,p_ordr_num in number default hr_api.g_number
1589 ,p_acty_typ_cd in varchar2 default hr_api.g_varchar2
1590 ,p_mlt_cd in varchar2 default hr_api.g_varchar2
1591 ,p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2
1592 ,p_rt_val in number default hr_api.g_number
1593 ,p_ann_rt_val in number default hr_api.g_number
1594 ,p_cmcd_rt_val in number default hr_api.g_number
1595 ,p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2
1596 ,p_bnft_rt_typ_cd in varchar2 default hr_api.g_varchar2
1597 ,p_dsply_on_enrt_flag in varchar2 default hr_api.g_varchar2
1598 ,p_rt_ovridn_flag in varchar2 default hr_api.g_varchar2
1599 ,p_rt_ovridn_thru_dt in date default hr_api.g_date
1600 ,p_elctns_made_dt in date default hr_api.g_date
1601 ,p_prtt_rt_val_stat_cd in varchar2 default hr_api.g_varchar2
1602 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
1603 ,p_cvg_amt_calc_mthd_id in number default hr_api.g_number
1604 ,p_actl_prem_id in number default hr_api.g_number
1605 ,p_comp_lvl_fctr_id in number default hr_api.g_number
1606 ,p_element_entry_value_id in number default hr_api.g_number
1607 ,p_per_in_ler_id in number default hr_api.g_number
1608 ,p_ended_per_in_ler_id in number default hr_api.g_number
1609 ,p_acty_base_rt_id in number default hr_api.g_number
1610 ,p_prtt_reimbmt_rqst_id in number default hr_api.g_number
1611 ,p_prtt_rmt_aprvd_fr_pymt_id in number default hr_api.g_number
1612 ,p_pp_in_yr_used_num in number default hr_api.g_number
1613 ,p_business_group_id in number default hr_api.g_number
1614 ,p_prv_attribute_category in varchar2 default hr_api.g_varchar2
1615 ,p_prv_attribute1 in varchar2 default hr_api.g_varchar2
1616 ,p_prv_attribute2 in varchar2 default hr_api.g_varchar2
1617 ,p_prv_attribute3 in varchar2 default hr_api.g_varchar2
1618 ,p_prv_attribute4 in varchar2 default hr_api.g_varchar2
1619 ,p_prv_attribute5 in varchar2 default hr_api.g_varchar2
1620 ,p_prv_attribute6 in varchar2 default hr_api.g_varchar2
1621 ,p_prv_attribute7 in varchar2 default hr_api.g_varchar2
1622 ,p_prv_attribute8 in varchar2 default hr_api.g_varchar2
1623 ,p_prv_attribute9 in varchar2 default hr_api.g_varchar2
1624 ,p_prv_attribute10 in varchar2 default hr_api.g_varchar2
1625 ,p_prv_attribute11 in varchar2 default hr_api.g_varchar2
1626 ,p_prv_attribute12 in varchar2 default hr_api.g_varchar2
1627 ,p_prv_attribute13 in varchar2 default hr_api.g_varchar2
1628 ,p_prv_attribute14 in varchar2 default hr_api.g_varchar2
1629 ,p_prv_attribute15 in varchar2 default hr_api.g_varchar2
1630 ,p_prv_attribute16 in varchar2 default hr_api.g_varchar2
1631 ,p_prv_attribute17 in varchar2 default hr_api.g_varchar2
1632 ,p_prv_attribute18 in varchar2 default hr_api.g_varchar2
1633 ,p_prv_attribute19 in varchar2 default hr_api.g_varchar2
1634 ,p_prv_attribute20 in varchar2 default hr_api.g_varchar2
1635 ,p_prv_attribute21 in varchar2 default hr_api.g_varchar2
1636 ,p_prv_attribute22 in varchar2 default hr_api.g_varchar2
1637 ,p_prv_attribute23 in varchar2 default hr_api.g_varchar2
1638 ,p_prv_attribute24 in varchar2 default hr_api.g_varchar2
1639 ,p_prv_attribute25 in varchar2 default hr_api.g_varchar2
1640 ,p_prv_attribute26 in varchar2 default hr_api.g_varchar2
1641 ,p_prv_attribute27 in varchar2 default hr_api.g_varchar2
1642 ,p_prv_attribute28 in varchar2 default hr_api.g_varchar2
1643 ,p_prv_attribute29 in varchar2 default hr_api.g_varchar2
1644 ,p_prv_attribute30 in varchar2 default hr_api.g_varchar2
1645 ,p_pk_id_table_name in varchar2 default hr_api.g_varchar2
1646 ,p_pk_id in number default hr_api.g_number
1647 ,p_no_end_element in boolean default false
1648 ,p_old_rt_strt_dt in date default hr_api.g_date
1649 ,p_old_rt_end_dt in date default hr_api.g_date
1650 ,p_object_version_number in out nocopy number
1651 ,p_effective_date in date
1652 ) is
1653 --
1654 l_object_version_number number := p_object_version_number ;
1655 l_recurring_rt boolean default false ;
1656 l_rt_end_dt date := p_rt_end_dt;
1657 l_rslt_suspended varchar2(30) := 'N';
1658 l_dummy_number number;
1659 l_effective_start_date date;
1660 l_effective_end_date date;
1661 l_delete_warning boolean;
1662 --
1663 cursor c_current_prv is
1664 select prv.*
1665 from ben_prtt_rt_val prv
1666 where prv.prtt_rt_val_id = p_prtt_rt_val_id;
1667 --
1668 l_current_prv c_current_prv%ROWTYPE;
1669 --
1670 cursor c_zap_future_ee
1671 (p_element_type_id in number
1672 ,p_person_id in number
1673 ,p_effective_date in date
1674 ) is
1675 select distinct
1676 ele.element_entry_id,
1677 ele.effective_start_date,
1678 ele.effective_end_date,
1679 ele.object_version_number,
1680 ele.creator_type,
1681 ele.creator_id
1682 from pay_element_entries_f ele,
1683 pay_element_links_f elk,
1684 pay_element_types_f elt,
1685 per_all_assignments_f asg
1686 where ele.effective_start_date = (select min(ele2.effective_start_date)
1687 from pay_element_entries_f ele2
1688 where ele2.element_entry_id
1689 = ele.element_entry_id)
1690 and ele.effective_start_date > p_effective_date
1691 and asg.person_id = p_person_id
1692 and ele.assignment_id = asg.assignment_id
1693 and ele.effective_start_date between asg.effective_start_date
1694 and asg.effective_end_date
1695 and nvl(ele.creator_id,-1) <> p_prtt_enrt_rslt_id
1696 and ele.entry_type = 'E'
1697 and ele.element_link_id = elk.element_link_id
1698 and ele.effective_start_date between elk.effective_start_date
1699 and elk.effective_end_date
1700 and elk.element_type_id = p_element_type_id
1701 and elt.element_type_id = elk.element_type_id
1702 and elk.effective_start_date between elt.effective_start_date
1703 and elt.effective_end_date
1704 order by ele.effective_start_date desc;
1705 --
1706 l_zap_future_ee c_zap_future_ee%rowtype;
1707 --
1708 cursor c_future_prv(p_person_id number,
1709 p_rt_strt_dt date,
1710 p_element_type_id number ) is
1711 select distinct
1712 prv.prtt_rt_val_id,
1713 prv.prtt_enrt_rslt_id,
1714 prv.rt_val,
1715 prv.cmcd_rt_val,
1716 prv.ann_rt_val,
1717 prv.rt_strt_dt,
1718 abr.acty_base_rt_id,
1719 abr.element_type_id,
1720 abr.input_value_id,
1721 prv.business_group_id,
1722 prv.acty_ref_perd_cd,
1723 prv.object_version_number
1724 from ben_prtt_rt_val prv,
1725 ben_acty_base_rt_f abr,
1726 ben_prtt_enrt_rslt_f pen
1727 where prv.prtt_rt_val_id <> p_prtt_rt_val_id
1728 and prv.prtt_rt_val_stat_cd IS NULL
1729 and prv.rt_strt_dt > p_rt_strt_dt
1730 and abr.element_type_id = p_element_type_id
1731 and prv.acty_base_rt_id = abr.acty_base_rt_id
1732 and prv.rt_strt_dt between abr.effective_start_date
1733 and abr.effective_end_date
1734 and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1735 and pen.person_id = p_person_id
1736 and pen.prtt_enrt_rslt_stat_cd IS NULL
1737 order by prv.rt_strt_dt ;
1738 --
1739 l_future_prv c_future_prv%rowtype;
1740 --
1741 begin
1742 --
1743 --Check Overlapping rates
1744 --TEMP 9999
1745 open c_current_prv ;
1746 fetch c_current_prv into l_current_prv;
1747 close c_current_prv;
1748 --
1749 --
1750 ben_prtt_rt_val_api.get_non_recurring_end_dt
1751 (p_rt_end_dt => l_rt_end_dt
1752 ,p_rt_strt_dt => p_rt_strt_dt
1753 ,p_acty_base_rt_id => p_acty_base_rt_id
1754 ,p_business_group_id => p_business_group_id
1755 ,p_recurring_rt => l_recurring_rt
1756 ,p_effective_date => p_effective_date
1757 ) ;
1758 --
1759 if l_recurring_rt then
1760 --
1761 ben_prtt_rt_val_api.chk_overlapping_dates
1762 (p_acty_base_rt_id => p_acty_base_rt_id
1763 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1764 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1765 ,p_new_rt_strt_dt => p_rt_strt_dt
1766 ,p_new_rt_end_dt => l_rt_end_dt );
1767 --
1768 end if;
1769 --
1770 ben_prv_upd.upd
1771 (
1772 p_prtt_rt_val_id => p_prtt_rt_val_id
1773 ,p_enrt_rt_id => p_enrt_rt_id
1774 ,p_rt_strt_dt => p_rt_strt_dt
1775 ,p_rt_end_dt => l_rt_end_dt
1776 ,p_rt_typ_cd => p_rt_typ_cd
1777 ,p_tx_typ_cd => p_tx_typ_cd
1778 ,p_ordr_num => p_ordr_num
1779 ,p_acty_typ_cd => p_acty_typ_cd
1780 ,p_mlt_cd => p_mlt_cd
1781 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1782 ,p_rt_val => p_rt_val
1783 ,p_ann_rt_val => p_ann_rt_val
1784 ,p_cmcd_rt_val => p_cmcd_rt_val
1785 ,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
1786 ,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
1787 ,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
1788 ,p_rt_ovridn_flag => p_rt_ovridn_flag
1789 ,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
1790 ,p_elctns_made_dt => p_elctns_made_dt
1791 ,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
1792 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1793 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
1794 ,p_actl_prem_id => p_actl_prem_id
1795 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
1796 ,p_element_entry_value_id => p_element_entry_value_id
1797 ,p_per_in_ler_id => p_per_in_ler_id
1798 ,p_ended_per_in_ler_id => p_ended_per_in_ler_id
1799 ,p_acty_base_rt_id => p_acty_base_rt_id
1800 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1801 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1802 ,p_pp_in_yr_used_num => p_pp_in_yr_used_num
1803 ,p_business_group_id => p_business_group_id
1804 ,p_prv_attribute_category => p_prv_attribute_category
1805 ,p_prv_attribute1 => p_prv_attribute1
1806 ,p_prv_attribute2 => p_prv_attribute2
1807 ,p_prv_attribute3 => p_prv_attribute3
1808 ,p_prv_attribute4 => p_prv_attribute4
1809 ,p_prv_attribute5 => p_prv_attribute5
1810 ,p_prv_attribute6 => p_prv_attribute6
1811 ,p_prv_attribute7 => p_prv_attribute7
1812 ,p_prv_attribute8 => p_prv_attribute8
1813 ,p_prv_attribute9 => p_prv_attribute9
1814 ,p_prv_attribute10 => p_prv_attribute10
1815 ,p_prv_attribute11 => p_prv_attribute11
1816 ,p_prv_attribute12 => p_prv_attribute12
1817 ,p_prv_attribute13 => p_prv_attribute13
1818 ,p_prv_attribute14 => p_prv_attribute14
1819 ,p_prv_attribute15 => p_prv_attribute15
1820 ,p_prv_attribute16 => p_prv_attribute16
1821 ,p_prv_attribute17 => p_prv_attribute17
1822 ,p_prv_attribute18 => p_prv_attribute18
1823 ,p_prv_attribute19 => p_prv_attribute19
1824 ,p_prv_attribute20 => p_prv_attribute20
1825 ,p_prv_attribute21 => p_prv_attribute21
1826 ,p_prv_attribute22 => p_prv_attribute22
1827 ,p_prv_attribute23 => p_prv_attribute23
1828 ,p_prv_attribute24 => p_prv_attribute24
1829 ,p_prv_attribute25 => p_prv_attribute25
1830 ,p_prv_attribute26 => p_prv_attribute26
1831 ,p_prv_attribute27 => p_prv_attribute27
1832 ,p_prv_attribute28 => p_prv_attribute28
1833 ,p_prv_attribute29 => p_prv_attribute29
1834 ,p_prv_attribute30 => p_prv_attribute30
1835 ,p_pk_id_table_name => p_pk_id_table_name
1836 ,p_pk_id => p_pk_id
1837 ,p_object_version_number => l_object_version_number
1838 ,p_effective_date => p_effective_date
1839 );
1840 --
1841 --Delete all future element entries
1842 if l_recurring_rt Then
1843 --
1844 open c_zap_future_ee(p_element_type_id,
1845 p_person_id,
1846 p_effective_date);
1847 loop
1848 fetch c_zap_future_ee into l_zap_future_ee;
1849 if c_zap_future_ee%notfound then
1850 exit;
1851 end if;
1852 --
1853 hr_utility.set_location('future ee:'||l_zap_future_ee.element_entry_id,6);
1854 hr_utility.set_location('creator type:'||l_zap_future_ee.creator_type,6);
1855 hr_utility.set_location('creator id:'||l_zap_future_ee.creator_id,6);
1856 --
1857 if l_zap_future_ee.creator_type ='F' and l_zap_future_ee.creator_id is not null then
1858 --
1859 py_element_entry_api.delete_element_entry
1860 (p_validate =>p_validate
1861 ,p_datetrack_delete_mode =>hr_api.g_zap
1862 ,p_effective_date =>l_zap_future_ee.effective_end_date
1863 ,p_element_entry_id =>l_zap_future_ee.element_entry_id
1864 ,p_object_version_number =>l_zap_future_ee.object_version_number
1865 ,p_effective_start_date =>l_effective_start_date
1866 ,p_effective_end_date =>l_effective_end_date
1867 ,p_delete_warning =>l_delete_warning);
1868 --
1869 end if;
1870 --
1871 end loop;
1872 close c_zap_future_ee;
1873 --
1874 end if;
1875 --
1876 l_rslt_suspended := ben_prtt_rt_val_api.result_is_suspended
1877 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
1878 p_person_id => p_person_id,
1879 p_business_group_id => p_business_group_id,
1880 p_effective_date => p_effective_date) ;
1881 --
1882 ben_element_entry.end_enrollment_element
1883 (p_business_group_id => p_business_group_id
1884 ,p_person_id => p_person_id
1885 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
1886 ,p_acty_ref_perd => p_acty_ref_perd_cd
1887 ,p_element_link_id => null
1888 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1889 ,p_rt_end_date => p_old_rt_strt_dt -1
1890 ,p_effective_date => p_old_rt_strt_dt
1891 ,p_dt_delete_mode => null
1892 ,p_acty_base_rt_id => p_acty_base_rt_id
1893 ,p_amt => p_rt_val
1894 );
1895 --
1896 if l_rslt_suspended = 'N' then
1897 --
1898 ben_element_entry.create_enrollment_element
1899 (p_business_group_id => p_business_group_id
1900 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1901 ,p_person_id => p_person_id
1902 ,p_acty_ref_perd => p_acty_ref_perd_cd
1903 ,p_acty_base_rt_id => p_acty_base_rt_id
1904 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
1905 ,p_rt_start_date => p_rt_strt_dt
1906 ,p_rt => p_rt_val
1907 ,p_cmncd_rt => p_cmcd_rt_val
1908 ,p_ann_rt => p_ann_rt_val
1909 ,p_prv_object_version_number=> l_object_version_number
1910 ,p_effective_date => p_effective_date
1911 ,p_eev_screen_entry_value => l_dummy_number
1912 ,p_element_entry_value_id => l_dummy_number
1913 );
1914 --
1915 ben_prv_shd.lck
1916 (
1917 p_prtt_rt_val_id => p_prtt_rt_val_id
1918 ,p_object_version_number => l_object_version_number
1919 );
1920 --
1921 end if;
1922 --
1923 --Even the amount is changed, we need to end the element properly if required
1924 --
1925 if p_rt_end_dt < p_rt_strt_dt or
1926 (l_rt_end_dt <> hr_api.g_eot and
1927 l_rt_end_dt <> p_old_rt_end_dt ) or
1928 (l_rt_end_dt <> hr_api.g_eot and (
1929 l_current_prv.rt_val <> p_rt_val or
1930 l_current_prv.ann_rt_val <> p_ann_rt_val or
1931 l_current_prv.cmcd_rt_val <> p_cmcd_rt_val ))then
1932 --
1933 ben_element_entry.end_enrollment_element
1934 (p_business_group_id => p_business_group_id
1935 ,p_person_id => p_person_id
1936 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
1937 ,p_acty_ref_perd => p_acty_ref_perd_cd
1938 ,p_element_link_id => null
1939 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1940 ,p_rt_end_date => l_rt_end_dt
1941 ,p_effective_date => p_effective_date
1942 ,p_dt_delete_mode => null
1943 ,p_acty_base_rt_id => p_acty_base_rt_id
1944 ,p_amt => p_rt_val
1945 );
1946 --
1947 end if;
1948 --
1949 p_object_version_number := l_object_version_number ;
1950 --
1951 --Now reprocess the future element entries
1952 open c_future_prv(p_person_id, p_rt_strt_dt, p_element_type_id) ;
1953 loop
1954 fetch c_future_prv into l_future_prv;
1955 if c_future_prv%notfound then
1956 exit;
1957 end if;
1958 --
1959 l_rslt_suspended := ben_prtt_rt_val_api.result_is_suspended
1960 (p_prtt_enrt_rslt_id => l_future_prv.prtt_enrt_rslt_id,
1961 p_person_id => p_person_id,
1962 p_business_group_id => p_business_group_id,
1963 p_effective_date => l_future_prv.rt_strt_dt ) ;
1964 --
1965 if l_rslt_suspended = 'N' then
1966 --
1967 ben_element_entry.create_enrollment_element
1968 (p_business_group_id => p_business_group_id
1969 ,p_prtt_rt_val_id => l_future_prv.prtt_rt_val_id
1970 ,p_person_id => p_person_id
1971 ,p_acty_ref_perd => l_future_prv.acty_ref_perd_cd
1972 ,p_acty_base_rt_id => l_future_prv.acty_base_rt_id
1973 ,p_enrt_rslt_id => l_future_prv.prtt_enrt_rslt_id
1974 ,p_rt_start_date => l_future_prv.rt_strt_dt
1975 ,p_rt => l_future_prv.rt_val
1976 ,p_cmncd_rt => l_future_prv.cmcd_rt_val
1977 ,p_ann_rt => l_future_prv.ann_rt_val
1978 ,p_prv_object_version_number=> l_future_prv.object_version_number
1979 ,p_effective_date => l_future_prv.rt_strt_dt
1980 ,p_eev_screen_entry_value => l_dummy_number
1981 ,p_element_entry_value_id => l_dummy_number
1982 );
1983 --
1984 end if;
1985 --
1986 end loop;
1987 close c_future_prv;
1988 --
1989 end override_prtt_rt_val ;
1990 --
1991 procedure override_certifications
1992 (p_prtt_enrt_rslt_id in number
1993 ,p_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
1994 ,p_effective_date in date
1995 ,p_business_group_id in number
1996 ) is
1997 --
1998 l_effective_date date;
1999 l_object_version_number number;
2000 l_dummy_date date;
2001 l_desired_datetrack_mode varchar2(30) := hr_api.g_correction;
2002 l_datetrack_mode varchar2(30);
2003 l_pea_effective_start_date date;
2004 l_pea_effective_end_date date;
2005 --
2006 cursor c_pen(p_prtt_enrt_rslt_id number) is
2007 select object_version_number
2008 from ben_prtt_enrt_rslt_f pen
2009 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2010 and pen.sspndd_flag = 'Y'
2011 and pen.prtt_enrt_rslt_stat_cd IS NULL
2012 and p_effective_date between pen.effective_start_date
2013 and pen.effective_end_date;
2014 --
2015 cursor c_pea(p_prtt_enrt_rslt_id number) is
2016 select
2017 pea.prtt_enrt_actn_id
2018 ,pea.actn_typ_id
2019 ,pea.rqd_flag
2020 ,pea.business_group_id
2021 ,pea.object_version_number pea_object_version_number
2022 ,pen.object_version_number pen_object_version_number
2023 ,pea.effective_start_date pea_effective_date
2024 from ben_prtt_enrt_actn_f pea,
2025 ben_prtt_enrt_rslt_f pen
2026 where
2027 pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2028 and p_effective_date between pen.effective_start_date and
2029 pen.effective_end_date
2030 and pen.prtt_enrt_rslt_stat_cd is null
2031 and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
2032 and pea.rqd_flag = 'Y' ;
2033 --
2034 l_pea c_pea%rowtype;
2035 --
2036 begin
2037 --Make the certifications optional
2038 open c_pen(p_prtt_enrt_rslt_id);
2039 fetch c_pen into l_object_version_number;
2040 if c_pen%found then
2041 --
2042 open c_pea(p_prtt_enrt_rslt_id) ;
2043 loop
2044 --
2045 fetch c_pea into l_pea ;
2046 if c_pea%notfound then
2047 exit ;
2048 end if;
2049 --
2050 hr_utility.set_location('Updating the Required Flag to No ',5);
2051 ben_prtt_enrt_actn_api.update_prtt_enrt_actn
2052 (p_prtt_enrt_actn_id => l_pea.prtt_enrt_actn_id
2053 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2054 ,p_rslt_object_version_number => l_pea.pen_object_version_number
2055 ,p_actn_typ_id => l_pea.actn_typ_id
2056 ,p_rqd_flag => 'N'
2057 ,p_effective_date => l_pea.pea_effective_date
2058 ,p_post_rslt_flag => 'N' -- 99999 p_post_rslt_flag
2059 ,p_business_group_id => p_business_group_id
2060 ,p_effective_start_date => l_pea_effective_start_date
2061 ,p_effective_end_date => l_pea_effective_end_date
2062 ,p_object_version_number => l_pea.pea_object_version_number
2063 ,p_datetrack_mode => hr_api.g_correction
2064 );
2065 hr_utility.set_location('After ben_prtt_enrt_actn_api.update_prtt_enrt_actn ',20);
2066 --
2067 end loop ;
2068 close c_pea ;
2069 --
2070 --Unsuspend the enrollment
2071 --
2072 ben_sspndd_enrollment.unsuspend_enrollment
2073 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2074 ,p_effective_date => p_effective_date
2075 ,p_post_rslt_flag => 'N'
2076 ,p_business_group_id => p_business_group_id
2077 ,p_object_version_number => l_object_version_number
2078 ,p_datetrack_mode => hr_api.g_correction
2079 ,p_called_from => 'BENEOPEH' );
2080 --
2081 end if;
2082 close c_pen;
2083 --
2084 end override_certifications;
2085 --
2086 end ben_manage_override;