[Home] [Help]
PACKAGE BODY: APPS.BEN_MNG_DPNT_BNF
Source
1 Package Body ben_mng_dpnt_bnf as
2 /* $Header: benmndep.pkb 120.8.12010000.2 2008/08/05 14:48:43 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------Private Global Definitions-----------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_mng_dpnt_bnf.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< recycle_dpnt_bnf >---------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure inherits dependents for the enrollment result
16 -- from the set of dependents previously covered by this result
17 -- according to the set of rules.
18 -- This process also performes required synchronization for outdated
19 -- dependent and beneficiary rows that were attached to the enrollment
20 -- result previously.
21 -- ----------------------------------------------------------------------------
22 --
23 procedure recycle_dpnt_bnf
24 (p_validate in boolean default false
25 ,p_new_prtt_enrt_rslt_id in number
26 ,p_new_enrt_rslt_ovn in out nocopy number
27 ,p_old_prtt_enrt_rslt_id in number
28 ,p_new_elig_per_elctbl_chc_id in number
29 ,p_person_id in number
30 ,p_return_to_exist_cvg_flag in varchar2
31 ,p_old_pl_id in number
32 ,p_new_pl_id in number
33 ,p_old_oipl_id in number
34 ,p_new_oipl_id in number
35 ,p_old_pl_typ_id in number
36 ,p_new_pl_typ_id in number
37 ,p_pgm_id in number
38 ,p_ler_id in number
39 ,p_per_in_ler_id in number default null
40 ,p_dpnt_cvg_strt_dt_cd in varchar2
41 ,p_dpnt_cvg_strt_dt_rl in number
42 ,p_enrt_cvg_strt_dt in date
43 ,p_business_group_id in number
44 ,p_effective_date in date
45 ,p_datetrack_mode in varchar2
46 ,p_multi_row_actn in boolean default false
47 ,p_process_dpnt in boolean default true
48 ,p_process_bnf in boolean default true)
49 is
50 --
51 l_proc varchar2(72) := g_package||'recycle_dpnt_bnf';
52 --
53 l_effective_start_date date;
54 l_effective_end_date date;
55 l_object_version_number number(15);
56 l_inherit boolean := TRUE;
57 l_ttl_rqmt_exist boolean := FALSE;
58 --
59 l_num_cov_dpnt_elig_new number(15);
60 l_num_cov_elig_rlshp number(15);
61
62 l_bnf_effective_start_date date;
63 l_bnf_effective_end_date date;
64 l_bnf_object_version_number number(9);
65 l_pl_bnf_id number(15);
66 --
67 l_cvg_strt_dt date;
68 l_dpnt_elig varchar2(1);
69 --
70 l_ttl_max_num number(15);
71 l_ttl_no_max_flag varchar2(30);
72 l_grp_rlshp_cd ben_dsgn_rqmt_f.grp_rlshp_cd%type ;
73 l_dsgn_rqmt_id number;
74 --
75
76 --
77 --
78 -- total designation requirement (new comp object):
79 --
80 cursor total_rqmt_c is
81 select r.mx_dpnts_alwd_num,
82 r.no_mx_num_dfnd_flag,
83 r.dsgn_rqmt_id,
84 r.grp_rlshp_cd
85 from ben_dsgn_rqmt_f r
86 where ((r.pl_id = p_new_pl_id)
87 or
88 (r.oipl_id = p_new_oipl_id)
89 or
90 (r.opt_id = (select opt_id
91 from ben_oipl_f
92 where oipl_id = p_new_oipl_id
93 and p_effective_date between effective_start_date
94 and effective_end_date
95 and business_group_id = p_business_group_id)))
96 and r.dsgn_typ_cd = 'DPNT'
97 -- and r.grp_rlshp_cd is null
98 and r.business_group_id = p_business_group_id
99 and p_effective_date between r.effective_start_date
100 and r.effective_end_date;
101 --
102 --
103 -- Cursor to pick the total number of covered dependents from old comp object
104 -- eligible for the new comp object
105 --
106 -- it's just used to check if there are any before continuing.
107 --
108 cursor c_num_cvd_dpnt is
109 select count(old.elig_cvrd_dpnt_id)
110 from ben_elig_cvrd_dpnt_f old,
111 ben_elig_dpnt new,
112 ben_per_in_ler pil,
113 ben_per_in_ler pil2
114 where old.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
115 and old.cvg_strt_dt is not null
116 and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
117 hr_api.g_eot)
118 and old.business_group_id = p_business_group_id
119 and p_effective_date between old.effective_start_date
120 and old.effective_end_date
121 and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
122 and new.business_group_id = p_business_group_id
123 and old.dpnt_person_id = new.dpnt_person_id
124 and pil.per_in_ler_id=old.per_in_ler_id
125 and pil.business_group_id=p_business_group_id
126 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
127 and pil2.per_in_ler_id=new.per_in_ler_id
128 and pil2.business_group_id=p_business_group_id
129 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
130 --
131 --
132 -- get all dependents that can be copied, based upon the fact that there
133 -- are no individual rlshp restrictions or we meet the restrictions.
134 cursor copy_dpnt_c is
135 select old.elig_cvrd_dpnt_id old_dpnt,
136 new.elig_dpnt_id new_dpnt
137 from ben_elig_cvrd_dpnt_f old,
138 ben_elig_dpnt new,
139 per_contact_relationships pcr,
140 ben_per_in_ler pil,
141 ben_per_in_ler pil2
142 where old.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
143 and old.cvg_strt_dt is not null
144 and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
145 hr_api.g_eot)
146 and old.business_group_id = p_business_group_id
147 and p_effective_date between old.effective_start_date
148 and old.effective_end_date
149 and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
150 and new.business_group_id = p_business_group_id
151 and old.dpnt_person_id = new.dpnt_person_id
152 and new.dpnt_person_id = pcr.contact_person_id
153 and pcr.person_id = p_person_id
154 and pcr.personal_flag = 'Y' -- Bug 3137774
155 and
156 (pcr.contact_type in
157 (select c.rlshp_typ_cd
158 from ben_dsgn_rqmt_f p,
159 ben_dsgn_rqmt_rlshp_typ c
160 where p.dsgn_rqmt_id = c.dsgn_rqmt_id
161 and ((p.pl_id = p_new_pl_id)
162 or
163 (p.oipl_id = p_new_oipl_id)
164 or
165 (p.opt_id = (select opt_id
166 from ben_oipl_f
167 where oipl_id = p_new_oipl_id
168 and p_effective_date between effective_start_date
169 and effective_end_date
170 and business_group_id = p_business_group_id)))
171 and p.dsgn_typ_cd = 'DPNT'
172 and p.grp_rlshp_cd is not null
173 and p.business_group_id = p_business_group_id
174 and p_effective_date between p.effective_start_date
175 and p.effective_end_date
176 and nvl(p.mx_dpnts_alwd_num,999) >=
177 (select count('s')
178 from ben_elig_dpnt new2,
179 per_contact_relationships pcr2
180 where new2.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
181 and new2.business_group_id = p_business_group_id
182 and new2.dpnt_person_id in
183 -- Make sure that the dpnt being counted was covered before
184 (select dpnt_person_id
185 from ben_elig_cvrd_dpnt_f ecd
186 where prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
187 and cvg_strt_dt is not null
188 and nvl(cvg_thru_dt, hr_api.g_eot) >=
189 nvl(pil2.lf_evt_ocrd_dt - 1, hr_api.g_eot)
190 and business_group_id = p_business_group_id
191 and p_effective_date between effective_start_date
192 and effective_end_date)
193 and new2.dpnt_person_id = pcr2.contact_person_id
194 and pcr2.person_id = p_person_id
195 and pcr2.contact_type in
196 (select rlshp_typ_cd
197 from ben_dsgn_rqmt_rlshp_typ c2
198 where c2.dsgn_rqmt_id = p.dsgn_rqmt_id)))
199 or not exists
200 (select 's'
201 from ben_dsgn_rqmt_f p3
202 where p3.grp_rlshp_cd is not null
203 and ((p3.pl_id = p_new_pl_id)
204 or
205 (p3.oipl_id = p_new_oipl_id)
206 or
207 (p3.opt_id = (select opt_id
208 from ben_oipl_f
209 where oipl_id = p_new_oipl_id
210 and p_effective_date between effective_start_date
211 and effective_end_date
212 and business_group_id = p_business_group_id)))
213 and p3.dsgn_typ_cd = 'DPNT'
214 and p3.business_group_id = p_business_group_id
215 and p_effective_date between p3.effective_start_date
216 and p3.effective_end_date))
217 and pil.per_in_ler_id=old.per_in_ler_id
218 and pil.business_group_id=p_business_group_id
219 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
220 and pil2.per_in_ler_id=new.per_in_ler_id
221 and pil2.business_group_id=p_business_group_id
222 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
223 ;
224 --
225 -- for copying dpnts when staying in the same coverage he was in before.
226 cursor exist_dpnt_c is
227 select ecd.elig_cvrd_dpnt_id,
228 ecd.dpnt_person_id,
229 ecd.elig_per_elctbl_chc_id,
230 ecd.object_version_number
231 from ben_elig_cvrd_dpnt_f ecd,
232 ben_per_in_ler pil
233 where ecd.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
234 and ecd.cvg_strt_dt is not null
235 and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
236 and ecd.business_group_id = p_business_group_id
237 and p_effective_date between ecd.effective_start_date
238 and ecd.effective_end_date
239 and pil.per_in_ler_id=ecd.per_in_ler_id
240 and pil.business_group_id=p_business_group_id
241 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
242 --
243 cursor old_bnf_c is
244 select pbn.*
245 from ben_pl_bnf_f pbn,
246 ben_per_in_ler pil
247 where pbn.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
248 and pbn.business_group_id = p_business_group_id
249 and p_effective_date between pbn.effective_start_date
250 and pbn.effective_end_date
251 and pil.per_in_ler_id=pbn.per_in_ler_id
252 and pil.business_group_id=pbn.business_group_id
253 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
254 ;
255
256 cursor c_tot_elig_dpnt
257 ( v_dsgn_rqmt_id number ,
258 v_grp_rlshp_cd varchar2 ) is
259 select count(old.elig_cvrd_dpnt_id)
260 from ben_elig_cvrd_dpnt_f old,
261 ben_elig_dpnt new,
262 ben_per_in_ler pil,
263 ben_per_in_ler pil2,
264 per_contact_relationships pcr
265 where old.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
266 and old.cvg_strt_dt is not null
267 and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
268 hr_api.g_eot)
269 and old.business_group_id = p_business_group_id
270 and p_effective_date between old.effective_start_date
271 and old.effective_end_date
272 and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
273 and new.business_group_id = p_business_group_id
274 and old.dpnt_person_id = new.dpnt_person_id
275 and pil.per_in_ler_id=old.per_in_ler_id
276 and pil.business_group_id=p_business_group_id
277 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
278 and pil2.per_in_ler_id=new.per_in_ler_id
279 and pil2.business_group_id=p_business_group_id
280 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
281 and pcr.person_id = p_person_id
282 and pcr.contact_person_id =new.dpnt_person_id
283 and p_effective_date between nvl(pcr.date_start,p_effective_date)
284 and nvl(pcr.date_end,p_effective_date)
285 --- validate the no of dpnt for the grp
286 and ( pcr.contact_type in
287 ( select drt.rlshp_typ_cd
288 from ben_dsgn_rqmt_f bdr ,
289 ben_dsgn_rqmt_rlshp_typ drt
290 where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
291 and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
292 and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
293 (bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
294 )
295 and p_effective_date between bdr.effective_start_date
296 and bdr.effective_end_date
297 )
298 --- if there is no relation typ defind take all
299 or
300 not exists
301 (select 'x' from ben_dsgn_rqmt_rlshp_typ drt
302 where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
303 )
304 ) ;
305
306 /* Bug: 3812994: If the new Option is Waive (or Plan is waived..),
307 then we do not copy the beneficiaries to the new result.
308 */
309 CURSOR c_waive_pl_opt IS
310 SELECT NULL
311 FROM ben_oipl_f oipl,
312 ben_opt_f opt
313 WHERE oipl.opt_id = opt.opt_id
314 AND oipl.oipl_id = p_new_oipl_id
315 AND p_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
316 AND p_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
317 AND NVL (opt.invk_wv_opt_flag, 'N') = 'Y'
318 UNION
319 SELECT NULL
320 FROM ben_pl_f pln
321 WHERE pln.pl_id = p_new_pl_id
322 AND p_effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date
323 AND NVL (pln.invk_dcln_prtn_pl_flag, 'N') = 'Y';
324
325 l_waive_pl_opt VARCHAR2(1);
326
327 --
328 begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332
333 -- If participant is returning to his prior coverage that is
334 -- in effect at the time of the p_effective_date then we need to
335 -- go back to the old dependents (update existing cvrd dep rows by
336 -- updating cvg_thru_dt to null. This code assumes that dsgn requirements
337 -- did not change at the time of open enrollment (otherwise the
338 -- inheritance logic should still apply).
339 --
340 if p_return_to_exist_cvg_flag = 'Y' and
341 p_process_dpnt then
342 hr_utility.set_location('Restore existing cvg', 10);
343
344 FOR dpnt in exist_dpnt_c LOOP
345 --
346 if dpnt.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id then
347 --
348 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
349 (p_validate => p_validate
350 ,p_business_group_id => p_business_group_id
351 ,p_elig_cvrd_dpnt_id => dpnt.elig_cvrd_dpnt_id
352 ,p_effective_start_date => l_effective_start_date
353 ,p_effective_end_date => l_effective_end_date
354 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
355 ,p_cvg_thru_dt => null
356 ,p_per_in_ler_id => p_per_in_ler_id
357 ,p_object_version_number => l_object_version_number
358 ,p_effective_date => p_effective_date
359 ,p_datetrack_mode => p_datetrack_mode
360 ,p_request_id => fnd_global.conc_request_id
361 ,p_program_application_id => fnd_global.prog_appl_id
362 ,p_program_id => fnd_global.conc_program_id
363 ,p_program_update_date => sysdate);
364 --
365 end if;
366 --
367 END LOOP;
368 --
369 -- Bug 1418754
370 --
371 ben_ELIG_CVRD_DPNT_api.chk_max_num_dpnt_for_pen (
372 p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id,
373 p_effective_date => p_effective_date,
374 p_business_group_id => p_business_group_id);
375 --
376 /*
377 -- same for bnf
378 --
379 FOR bnf in exist_bnf_c LOOP
380 --
381 ben_plan_beneficiary_api.update_plan_beneficiary
382 (p_validate => p_validate
383 ,p_pl_bnf_id => bnf.pl_bnf_id
384 ,P_DSG_THRU_DT => null
385 ,p_effective_start_date => l_effective_start_date
386 ,p_effective_end_date => l_effective_end_date
387 ,p_object_version_number => l_object_version_number
388 ,p_effective_date => p_effective_date
389 ,p_per_in_ler_id => p_per_in_ler_id
390 ,p_datetrack_mode => p_datetrack_mode
391 ,p_request_id => fnd_global.conc_request_id
392 ,p_program_application_id => fnd_global.prog_appl_id
393 ,p_program_id => fnd_global.conc_program_id
394 ,p_program_update_date => sysdate);
395 --
396 END LOOP;
397 */
398 --
399 else
400 -- We are not returning the the same comp object, try to inherit.
401 --
402 if p_process_dpnt then
403 -- Check if inheritance for dependents will apply.
404 -- Are there those previously covered that are eligible for new the chc_id?
405 --
406 open c_num_cvd_dpnt;
407 fetch c_num_cvd_dpnt into l_num_cov_dpnt_elig_new;
408 close c_num_cvd_dpnt;
409 --
410 -- Is updated result Plan type is the same as an old result Plan type?
411 --
412 if l_num_cov_dpnt_elig_new = 0 or
413 p_old_pl_typ_id <> p_new_pl_typ_id then
414 --
415 -- no inheritance
416 --
417 hr_utility.set_location('No inheritance possible:' || l_proc, 15);
418 --
419 l_inherit := FALSE;
420 --
421 else
422 --
423 -- check total max requirements
424 -- # 2646284
425 --- if conodtion was chnged to loop to validate all the
426 --- Relationship groups
427 open total_rqmt_c;
428 Loop
429 fetch total_rqmt_c into l_ttl_max_num, l_ttl_no_max_flag,
430 l_dsgn_rqmt_id,l_grp_rlshp_cd ;
431 --
432 if total_rqmt_c%notfound then
433 Exit ;
434 End if ;
435 hr_utility.set_location('grp_rlshp_cd ' || l_grp_rlshp_cd, 99 );
436 hr_utility.set_location('l_ttl_max_num '|| l_ttl_max_num, 99 );
437 --
438 --
439 l_ttl_rqmt_exist := TRUE;
440 --
441 open c_tot_elig_dpnt (l_dsgn_rqmt_id,l_grp_rlshp_cd) ;
442 fetch c_tot_elig_dpnt into l_num_cov_dpnt_elig_new ;
443 close c_tot_elig_dpnt ;
444 hr_utility.set_location(' total eligible ' || l_num_cov_dpnt_elig_new, 99 );
445
446 if l_ttl_no_max_flag = 'N' and
447 l_num_cov_dpnt_elig_new > l_ttl_max_num then
448 --
449 hr_utility.set_location('No inheritance, total max exceeded : ',99 );
450 --
451 -- no inheritance
452 --
453 l_inherit := FALSE;
454 exit ;
455 --
456 hr_utility.set_location(' inherit false ', 99 );
457 end if;
458 --
459 end loop; -- if a total requirement is found.
460 --
461 close total_rqmt_c;
462
463 end if;
464 --
465 if l_inherit then
466
467 hr_utility.set_location('Get cvg start dt'||l_proc, 26);
468 --
469 -- Calculate Dependents Coverage Start Date
470 -- dbms_output.put_line('Calculating cvg strt dt');
471 --
472 ben_determine_date.main
473 (p_date_cd => p_dpnt_cvg_strt_dt_cd
474 ,p_per_in_ler_id => null
475 ,p_person_id => null
476 ,p_pgm_id => null
477 ,p_pl_id => null
478 ,p_oipl_id => null
479 ,p_elig_per_elctbl_chc_id => p_new_elig_per_elctbl_chc_id
480 ,p_business_group_id => p_business_group_id
481 ,p_formula_id => p_dpnt_cvg_strt_dt_rl
482 ,p_effective_date => p_effective_date
483 ,p_returned_date => l_cvg_strt_dt);
484 --
485 if l_cvg_strt_dt is null then
486 -- error
487 --
488 fnd_message.set_name('BEN', 'BEN_91657_DPNT_CVG_STRT_DT');
489 fnd_message.raise_error;
490 end if;
491 --
492 -- Take the latter of the calculated date and p_enrt_cvg_strt_dt
493 --
494 if l_cvg_strt_dt > p_enrt_cvg_strt_dt then
495 --
496 l_cvg_strt_dt := p_enrt_cvg_strt_dt;
497 --
498 end if;
499 --
500 hr_utility.set_location('Cvg start dt ='||to_char(l_cvg_strt_dt), 25);
501
502 -- Loop thru dependents previously covered by the result:
503 --
504 hr_utility.set_location('Loop thru old dependents:'||l_proc, 30);
505 -- dbms_output.put_line('Start loop for old dpnts');
506 --
507 FOR copy_dpnt_rec in copy_dpnt_c LOOP
508 --
509 hr_utility.set_location('Inherit dependent, rlshp max OK:'||l_proc, 45);
510 --
511 hook_dpnt
512 (p_validate => FALSE
513 ,p_elig_dpnt_id => copy_dpnt_rec.new_dpnt
514 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
515 ,p_old_prtt_enrt_rslt_id => p_old_prtt_enrt_rslt_id
516 ,p_new_enrt_rslt_ovn => p_new_enrt_rslt_ovn
517 ,p_pgm_id => p_pgm_id
518 ,p_cvg_strt_dt => l_cvg_strt_dt
519 ,p_effective_date => p_effective_date
520 ,p_old_elig_cvrd_dpnt_id => copy_dpnt_rec.old_dpnt
521 ,p_per_in_ler_id => p_per_in_ler_id
522 ,p_business_group_id => p_business_group_id
523 ,p_datetrack_mode => p_datetrack_mode
524 ,p_multi_row_actn => p_multi_row_actn);
525 --
526 END LOOP;
527 end if; -- if l-inherit
528 end if; -- if p_process_dpnt
529 --
530 if p_process_bnf then
531 --
532 -- For now, since beneficiaries are attached to the plan inherit them if
533 -- no plan change:
534 --
535 If p_old_pl_id = p_new_pl_id then
536 --
537 hr_utility.set_location('Plans the same, copy bnfs', 55);
538 --
539 /* Bug: 3812994: If the new Option is Waive (or Plan is waived..),
540 then we do not copy the beneficiaries to the new result.
541 */
542 OPEN c_waive_pl_opt;
543 FETCH c_waive_pl_opt INTO l_waive_pl_opt;
544 IF c_waive_pl_opt%FOUND THEN
545 hr_utility.set_location('Waive Opt/Plan. Need not carry bnf ', 60);
546 hr_utility.set_location('Exiting'||l_proc, 99);
547 return;
548 END IF;
549 -- End 3812994 changes.
550 --
551 FOR bnf in old_bnf_c LOOP
552 --
553 ben_plan_beneficiary_api.create_plan_beneficiary
554 (p_validate => p_validate
555 ,p_pl_bnf_id => l_pl_bnf_id
556 ,p_effective_start_date => l_bnf_effective_start_date
557 ,p_effective_end_date => l_bnf_effective_end_date
558 ,p_business_group_id => p_business_group_id
559 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
560 ,p_bnf_person_id => bnf.bnf_person_id
561 ,p_organization_id => bnf.organization_id
562 ,p_ttee_person_id => bnf.ttee_person_id
563 ,p_prmry_cntngnt_cd => bnf.prmry_cntngnt_cd
564 ,p_pct_dsgd_num => bnf.pct_dsgd_num
565 ,p_amt_dsgd_val => bnf.amt_dsgd_val
566 ,p_amt_dsgd_uom => bnf.amt_dsgd_uom
567 ,p_addl_instrn_txt => bnf.addl_instrn_txt
568 ,p_per_in_ler_id => p_per_in_ler_id
569 ,p_dsgn_strt_dt => p_effective_date
570 ,p_pbn_attribute_category => bnf.pbn_attribute_category
571 ,p_pbn_attribute1 => bnf.pbn_attribute1
572 ,p_pbn_attribute2 => bnf.pbn_attribute2
573 ,p_pbn_attribute3 => bnf.pbn_attribute3
574 ,p_pbn_attribute4 => bnf.pbn_attribute4
575 ,p_pbn_attribute5 => bnf.pbn_attribute5
576 ,p_pbn_attribute6 => bnf.pbn_attribute6
577 ,p_pbn_attribute7 => bnf.pbn_attribute7
578 ,p_pbn_attribute8 => bnf.pbn_attribute8
579 ,p_pbn_attribute9 => bnf.pbn_attribute9
580 ,p_pbn_attribute10 => bnf.pbn_attribute10
581 ,p_pbn_attribute11 => bnf.pbn_attribute11
582 ,p_pbn_attribute12 => bnf.pbn_attribute12
583 ,p_pbn_attribute13 => bnf.pbn_attribute13
584 ,p_pbn_attribute14 => bnf.pbn_attribute14
585 ,p_pbn_attribute15 => bnf.pbn_attribute15
586 ,p_pbn_attribute16 => bnf.pbn_attribute16
587 ,p_pbn_attribute17 => bnf.pbn_attribute17
588 ,p_pbn_attribute18 => bnf.pbn_attribute18
589 ,p_pbn_attribute19 => bnf.pbn_attribute19
590 ,p_pbn_attribute20 => bnf.pbn_attribute20
591 ,p_pbn_attribute21 => bnf.pbn_attribute21
592 ,p_pbn_attribute22 => bnf.pbn_attribute22
593 ,p_pbn_attribute23 => bnf.pbn_attribute23
594 ,p_pbn_attribute24 => bnf.pbn_attribute24
595 ,p_pbn_attribute25 => bnf.pbn_attribute25
596 ,p_pbn_attribute26 => bnf.pbn_attribute26
597 ,p_pbn_attribute27 => bnf.pbn_attribute27
598 ,p_pbn_attribute28 => bnf.pbn_attribute28
599 ,p_pbn_attribute29 => bnf.pbn_attribute29
600 ,p_pbn_attribute30 => bnf.pbn_attribute30
601 ,p_request_id => fnd_global.conc_request_id
602 ,p_program_application_id => fnd_global.prog_appl_id
603 ,p_program_id => fnd_global.conc_program_id
604 ,p_program_update_date => sysdate
605 ,p_object_version_number => l_bnf_object_version_number
606 ,p_multi_row_actn => p_multi_row_actn
607 ,p_effective_date => p_effective_date);
608 --
609 END LOOP;
610 --
611 End if;
612 end if; -- if p_process_bnf
613 --
614 end if;
615 --
616 hr_utility.set_location('Exiting'||l_proc, 99);
617
618 --
619
620 --
621 End recycle_dpnt_bnf;
622 --
623 -- ----------------------------------------------------------------------------
624 -- |----------------------------< hook_dpnt >---------------------------------|
625 -- ----------------------------------------------------------------------------
626 --
627 Procedure hook_dpnt
628 (p_validate in boolean default false
629 ,p_elig_dpnt_id in number
630 ,p_prtt_enrt_rslt_id in number
631 ,p_old_prtt_enrt_rslt_id in number
632 ,p_new_enrt_rslt_ovn in out nocopy number
633 ,p_pgm_id in number
634 ,p_cvg_strt_dt in date
635 ,p_effective_date in date
636 ,p_old_elig_cvrd_dpnt_id in number
637 ,p_per_in_ler_id in number
638 ,p_business_group_id in number
639 ,p_datetrack_mode in varchar2
640 ,p_multi_row_actn in BOOLEAN default FALSE)
641 IS
642 --
643 l_proc varchar2(72) := g_package||'hook_dpnt';
644 l_cvg_strt_dt date;
645 l_effective_start_date date;
646 l_effective_end_date date;
647 l_object_version_number number(9);
648 l_cvrd_dpnt_ctfn_prvdd_id number(15);
649 l_dsgn_lvl_cd varchar2(30);
650 l_actn_typ_id number(15);
651 l_prtt_enrt_actn_id number(15);
652 l_cmpltd_dt date;
653 l_actn_object_version_number number(15);
654 l_actn_effective_start_date date;
655 l_actn_effective_end_date date;
656 l_ctfn_rqd_flag varchar2(30);
657 l_pdp_object_version_number number(9);
658 l_elig_cvrd_dpnt_id number(15);
659 --
660 -- Cursor to fetch the designation level code
661 --
662 cursor dsgn_lvl_c
663 is
664 select dpnt_dsgn_lvl_cd
665 from ben_pgm_f
666 where pgm_id = p_pgm_id
667 and business_group_id = p_business_group_id
668 and p_effective_date between effective_start_date
669 and effective_end_date;
670 --
671 -- Cursor to retrieve dependant ctfn required flags at the pgm level
672 --
673 cursor c_dpnt_pgm
674 is
675 select pgm.dpnt_dsgn_no_ctfn_rqd_flag
676 from ben_pgm_f pgm
677 where pgm.pgm_id = p_pgm_id
678 and pgm.business_group_id = p_business_group_id
679 and p_effective_date between pgm.effective_start_date
680 and pgm.effective_end_date;
681 --
682 -- cursor to retrieve dpnts' required-info-flags at the ptip level
683 --
684 cursor c_dpnt_ptip
685 is
686 select ptip.dpnt_cvg_no_ctfn_rqd_flag
687 from ben_ptip_f ptip
688 where ptip.ptip_id = (select ptip_id
689 from ben_prtt_enrt_rslt_f
690 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
691 and business_group_id =p_business_group_id
692 and prtt_enrt_rslt_stat_cd is null
693 and p_effective_date between effective_start_date
694 and effective_end_date)
695 and ptip.business_group_id = p_business_group_id
696 and p_effective_date between
697 ptip.effective_start_date and ptip.effective_end_date;
698 --
699 -- Cursor to fetch certifications provided
700 --
701 cursor dpnt_ctfn_c
702 is
703 select *
704 from ben_cvrd_dpnt_ctfn_prvdd_f
705 where elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
706 and business_group_id = p_business_group_id
707 and p_effective_date between effective_start_date
708 and effective_end_date;
709 --
710 --Bug# 5572910
711 --
712 cursor c_dpnt_pea is
713 select pea.prtt_enrt_rslt_id,
714 pea.prtt_enrt_actn_id,
715 pea.actn_typ_id,
716 pea.object_version_number,
717 pea.effective_start_date,
718 pea.effective_end_date
719 from ben_prtt_enrt_actn_f pea,
720 ben_actn_typ eat
721 where pea.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
722 and pea.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
723 and pea.cmpltd_dt is not null
724 and eat.type_cd in ('DDCTFN')
725 and eat.actn_typ_id=pea.actn_typ_id
726 and p_effective_date
727 between pea.effective_start_date and pea.effective_end_date; -- bug 6793512
728 -- order by pea.prtt_enrt_actn_id,pea.effective_start_date,pea.effective_end_date;
729 --
730 l_dpnt_pea c_dpnt_pea%rowtype;
731 l_pea_object_version_number ben_prtt_enrt_actn_f.object_version_number%TYPE;
732 l_pea_effective_start_date ben_prtt_enrt_actn_f.effective_start_date%TYPE;
733 l_pea_effective_end_date ben_prtt_enrt_actn_f.effective_end_date%TYPE;
734 --
735 cursor c_ccp (ll_prtt_enrt_actn_id number) is
736 select ccp.cvrd_dpnt_ctfn_prvdd_id
737 ,ccp.effective_start_date
738 ,ccp.effective_end_date
739 ,ccp.object_version_number
740 ,ccp.prtt_enrt_actn_id
741 from ben_cvrd_dpnt_ctfn_prvdd_f ccp
742 where ccp.prtt_enrt_actn_id = ll_prtt_enrt_actn_id
743 and ccp.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
744 and ccp.business_group_id=p_business_group_id
745 and ccp.dpnt_dsgn_ctfn_recd_dt is not null
746 and p_effective_date between ccp.effective_start_date
747 and ccp.effective_end_date;
748 --
749 l_ccp_rec c_ccp%rowtype;
750 l_ccp_object_version_number ben_cvrd_dpnt_ctfn_prvdd_f.object_version_number%TYPE;
751 l_ccp_effective_start_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_start_date%TYPE;
752 l_ccp_effective_end_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_end_date%TYPE;
753 l_ccp_update_flag varchar2(30) := 'N'; --6613891
754 --
755 -- Bug 6793512
756 l_correction boolean;
757 l_update boolean;
758 l_update_override boolean;
759 l_update_change_insert boolean;
760 l_datetrack_mode varchar2(20);
761 -- Bug 6793512
762
763 --Bug# 5572910
764 --
765
766 begin
767 --
768 hr_utility.set_location('Entering'||l_proc, 5);
769 --
770 --
771 -- update cvrd_flag, rslt_id, etc. ...
772 --
773 hr_utility.set_location('Update dpnt info'||l_proc, 20);
774 if p_multi_row_actn then
775 hr_utility.set_location('LAMC manage multi row', 20);
776 else hr_utility.set_location('LAMC manage NOT multi row', 20);
777 end if;
778
779 ben_ELIG_DPNT_api.process_dependent(
780 p_elig_dpnt_id => p_elig_dpnt_id,
781 p_business_group_id => p_business_group_id,
782 p_effective_date => p_effective_date,
783 p_cvg_strt_dt => p_cvg_strt_dt,
784 p_cvg_thru_dt => hr_api.g_eot,
785 p_datetrack_mode => p_datetrack_mode,
786 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
787 p_effective_start_date => l_effective_start_date,
788 p_effective_end_date => l_effective_end_date,
789 p_object_version_number => l_pdp_object_version_number,
790 p_multi_row_actn => p_multi_row_actn);
791 --
792 -- dbms_output.put_line('Dpnt Info Done');
793 --
794 --Bug# 5572910
795 --
796 open c_dpnt_pea;
797 --
798 fetch c_dpnt_pea into l_dpnt_pea;
799 --
800 hr_utility.set_location('l_dpnt_pea.prtt_enrt_actn_id'||l_dpnt_pea.prtt_enrt_actn_id,1114);
801 --
802 if c_dpnt_pea%found then
803 l_ccp_update_flag := 'N'; --6613891
804 hr_utility.set_location('In c_dpnt_pea. l_ccp_update_flag = '|| l_ccp_update_flag,8085);
805 --
806 open c_ccp(l_dpnt_pea.prtt_enrt_actn_id);
807 loop
808 fetch c_ccp into l_ccp_rec;
809 exit when c_ccp%notfound;
810 l_ccp_update_flag := 'Y'; --6613891
811 --
812 hr_utility.set_location('l_elig_cvrd_dpnt_id'||l_elig_cvrd_dpnt_id,1114);
813 --
814 -- Bug 6793512
815 --
816 dt_api.find_dt_upd_modes
817 (p_effective_date => p_effective_date,
818 p_base_table_name => 'ben_cvrd_dpnt_ctfn_prvdd_f',
819 p_base_key_column => 'cvrd_dpnt_ctfn_prvdd_id',
820 p_base_key_value => l_ccp_rec.cvrd_dpnt_ctfn_prvdd_id,
821 p_correction => l_correction,
822 p_update => l_update,
823 p_update_override => l_update_override,
824 p_update_change_insert => l_update_change_insert);
825 --
826 if l_update_override then
827 l_datetrack_mode := hr_api.g_update_override;
828 elsif l_update then
829 l_datetrack_mode := hr_api.g_update;
830 else
831 l_datetrack_mode := hr_api.g_correction;
832 end if;
833 --
834 hr_utility.set_location('rtagarra '||l_datetrack_mode,9653);
835 -- Bug 6793512
836 ben_CVRD_DPNT_CTFN_PRVDD_api.update_CVRD_DPNT_CTFN_PRVDD
837 (
838 p_validate => FALSE
839 ,p_cvrd_dpnt_ctfn_prvdd_id => l_ccp_rec.cvrd_dpnt_ctfn_prvdd_id
840 ,p_effective_start_date => l_ccp_effective_start_date
841 ,p_effective_end_date => l_ccp_effective_end_date
842 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
843 ,p_prtt_enrt_actn_id => l_ccp_rec.prtt_enrt_actn_id
844 ,p_object_version_number => l_ccp_rec.object_version_number
845 ,p_effective_date => p_effective_date
846 ,p_datetrack_mode => l_datetrack_mode
847 );
848 --
849 end loop;
850 --
851 close c_ccp;
852
853 --6613891
854 hr_utility.set_location('l_ccp_update_flag = ' || l_ccp_update_flag,8085);
855 --
856
857 if l_ccp_update_flag = 'Y' then
858 --
859 -- Bug 6793512
860 --
861 dt_api.find_dt_upd_modes
862 (p_effective_date => p_effective_date,
863 p_base_table_name => 'ben_prtt_enrt_actn_f',
864 p_base_key_column => 'prtt_enrt_actn_id',
865 p_base_key_value => l_dpnt_pea.prtt_enrt_actn_id,
866 p_correction => l_correction,
867 p_update => l_update,
868 p_update_override => l_update_override,
869 p_update_change_insert => l_update_change_insert);
870 --
871 if l_update_override then
872 l_datetrack_mode := hr_api.g_update_override;
873 elsif l_update then
874 l_datetrack_mode := hr_api.g_update;
875 else
876 l_datetrack_mode := hr_api.g_correction;
877 end if;
878 --
879 hr_utility.set_location('rtagarra '||l_datetrack_mode,9653);
880 --
881 -- Bug 6793512
882 ben_PRTT_ENRT_ACTN_api.update_PRTT_ENRT_ACTN
883 (
884 p_validate => FALSE
885 ,p_effective_start_date => l_pea_effective_start_date
886 ,p_effective_end_date => l_pea_effective_end_date
887 ,p_prtt_enrt_actn_id => l_dpnt_pea.prtt_enrt_actn_id
888 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
889 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
890 ,p_object_version_number => l_dpnt_pea.object_version_number
891 ,p_effective_date => p_effective_date
892 ,p_datetrack_mode => l_datetrack_mode
893 ,p_rslt_object_version_number => l_pea_object_version_number
894 );
895 end if;
896 --
897 end if;
898 --
899 close c_dpnt_pea;
900
901 --Bug# 5572910
902
903 -- copy certifications at Program or Plan Type in Program levels
904 --
905 if p_pgm_id is not null then
906 --
907 open dsgn_lvl_c;
908 fetch dsgn_lvl_c into l_dsgn_lvl_cd;
909 --
910 if dsgn_lvl_c%FOUND then
911 --
912 if l_dsgn_lvl_cd <> 'PL' then
913 --
914 if l_dsgn_lvl_cd = 'PGM' then
915 --
916 open c_dpnt_pgm;
917 fetch c_dpnt_pgm into l_ctfn_rqd_flag;
918 close c_dpnt_pgm;
919 --
920 elsif l_dsgn_lvl_cd = 'PTIP' then
921 --
922 open c_dpnt_ptip;
923 fetch c_dpnt_ptip into l_ctfn_rqd_flag;
924 close c_dpnt_ptip;
925 --
926 end if;
927 --
928 -- Get the actn type id
929 --
930 l_actn_typ_id := ben_enrollment_action_items.get_actn_typ_id
931 (p_type_cd => 'DDCTFN'
932 ,p_business_group_id => p_business_group_id);
933 --
934 ben_enrollment_action_items.get_prtt_enrt_actn_id
935 (p_actn_typ_id => l_actn_typ_id,
936 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
937 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
938 p_effective_date => p_effective_date,
939 p_business_group_id => p_business_group_id,
940 p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
941 p_cmpltd_dt => l_cmpltd_dt,
942 p_object_version_number => l_actn_object_version_number);
943 --
944 if l_prtt_enrt_actn_id is null then
945 --
946 FOR ctfn_rec in dpnt_ctfn_c LOOP
947 --
948 -- Certification needs to be created, create a action item
949 -- as none exists.
950 -- Create it ONCE.
951 --
952 if l_prtt_enrt_actn_id is null then
953 --
954 ben_enrollment_action_items.write_new_action_item
955 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
956 ,p_rslt_object_version_number => p_new_enrt_rslt_ovn
957 ,p_actn_typ_id => l_actn_typ_id
958 ,p_effective_date => p_effective_date
959 ,p_post_rslt_flag => 'N'
960 ,p_business_group_id => p_business_group_id
961 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
962 ,p_rqd_flag => l_ctfn_rqd_flag
963 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
964 ,p_object_version_number => l_actn_object_version_number);
965 --
966 end if;
967 --
968 ben_cvrd_dpnt_ctfn_prvdd_api.create_cvrd_dpnt_ctfn_prvdd
969 (p_validate => false
970 ,p_cvrd_dpnt_ctfn_prvdd_id => l_cvrd_dpnt_ctfn_prvdd_id
971 ,p_effective_start_date => l_effective_start_date
972 ,p_effective_end_date => l_effective_end_date
973 ,p_dpnt_dsgn_ctfn_typ_cd => ctfn_rec.dpnt_dsgn_ctfn_typ_cd
974 ,p_dpnt_dsgn_ctfn_rqd_flag => ctfn_rec.dpnt_dsgn_ctfn_rqd_flag
975 ,p_dpnt_dsgn_ctfn_recd_dt => ctfn_rec.dpnt_dsgn_ctfn_recd_dt
976 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
977 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
978 ,p_business_group_id => ctfn_rec.business_group_id
979 ,p_ccp_attribute_category => ctfn_rec.ccp_attribute_category
980 ,p_ccp_attribute1 => ctfn_rec.ccp_attribute1
981 ,p_ccp_attribute2 => ctfn_rec.ccp_attribute2
982 ,p_ccp_attribute3 => ctfn_rec.ccp_attribute3
983 ,p_ccp_attribute4 => ctfn_rec.ccp_attribute4
984 ,p_ccp_attribute5 => ctfn_rec.ccp_attribute5
985 ,p_ccp_attribute6 => ctfn_rec.ccp_attribute6
986 ,p_ccp_attribute7 => ctfn_rec.ccp_attribute7
987 ,p_ccp_attribute8 => ctfn_rec.ccp_attribute8
988 ,p_ccp_attribute9 => ctfn_rec.ccp_attribute9
989 ,p_ccp_attribute10 => ctfn_rec.ccp_attribute10
990 ,p_ccp_attribute11 => ctfn_rec.ccp_attribute11
991 ,p_ccp_attribute12 => ctfn_rec.ccp_attribute12
992 ,p_ccp_attribute13 => ctfn_rec.ccp_attribute13
993 ,p_ccp_attribute14 => ctfn_rec.ccp_attribute14
994 ,p_ccp_attribute15 => ctfn_rec.ccp_attribute15
995 ,p_ccp_attribute16 => ctfn_rec.ccp_attribute16
996 ,p_ccp_attribute17 => ctfn_rec.ccp_attribute17
997 ,p_ccp_attribute18 => ctfn_rec.ccp_attribute18
998 ,p_ccp_attribute19 => ctfn_rec.ccp_attribute19
999 ,p_ccp_attribute20 => ctfn_rec.ccp_attribute20
1000 ,p_ccp_attribute21 => ctfn_rec.ccp_attribute21
1001 ,p_ccp_attribute22 => ctfn_rec.ccp_attribute22
1002 ,p_ccp_attribute23 => ctfn_rec.ccp_attribute23
1003 ,p_ccp_attribute24 => ctfn_rec.ccp_attribute24
1004 ,p_ccp_attribute25 => ctfn_rec.ccp_attribute25
1005 ,p_ccp_attribute26 => ctfn_rec.ccp_attribute26
1006 ,p_ccp_attribute27 => ctfn_rec.ccp_attribute27
1007 ,p_ccp_attribute28 => ctfn_rec.ccp_attribute28
1008 ,p_ccp_attribute29 => ctfn_rec.ccp_attribute29
1009 ,p_ccp_attribute30 => ctfn_rec.ccp_attribute30
1010 ,p_object_version_number => l_object_version_number
1011 ,p_effective_date => p_effective_date
1012 ,p_request_id => fnd_global.conc_request_id
1013 ,p_program_application_id => fnd_global.prog_appl_id
1014 ,p_program_id => fnd_global.conc_program_id
1015 ,p_program_update_date => sysdate);
1016 --
1017 END LOOP;
1018 --
1019 end if;
1020 --
1021 end if;
1022 --
1023 end if;
1024 --
1025 close dsgn_lvl_c;
1026 --
1027 end if;
1028 --
1029 hr_utility.set_location('Exiting'||l_proc, 25);
1030 --
1031 End hook_dpnt;
1032 --
1033 end ben_mng_dpnt_bnf;