[Home] [Help]
PACKAGE BODY: APPS.BEN_MNG_DPNT_BNF
Source
1 Package Body ben_mng_dpnt_bnf as
2 /* $Header: benmndep.pkb 120.12.12020000.4 2012/10/16 10:55:41 usaraswa 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 l_old_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type; -- Bug 9888746
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 = l_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 = l_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 /*Bug 13408187 : To check whether the dependent is manually deenrolled:
156 Participant coverage continues, but dep coverage is end dated
157 and dependent is eligible to be covered for the electable choice that
158 he is previously enrolled.This will ensure end dated dependent coverages
159 will not be carry forwarded*/
160 and not exists(
161 select '1' from dual where
162 (
163 ('Y' = (select 'Y' from dual where exists
164 (select '1' from ben_prtt_enrt_rslt_f pen1
165 where pen1.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
166 and pen1.prtt_enrt_rslt_stat_cd is NULL
167 and pen1.effective_end_date = hr_api.g_eot
168 and pen1.enrt_cvg_thru_dt = hr_api.g_eot)
169 )) --'Y'
170 and old.cvg_thru_dt <> hr_api.g_eot
171 and ('Y' = (select 'Y' from dual where exists
172 (select '1' from ben_elig_dpnt edp,
173 ben_elig_per_elctbl_chc epe
174 where edp.dpnt_person_id = old.dpnt_person_id
175 and edp.per_in_ler_id = new.per_in_ler_id
176 and epe.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
177 and epe.per_in_ler_id = new.per_in_ler_id
178 and epe.elig_per_elctbl_chc_id = edp.elig_per_elctbl_chc_id
179 )
180 )
181 )
182 )
183 ) --not exists
184 /* Bug 13568008: If recycle dependents is called when enrollments are reinstated,
185 check whether there exists in the backup table for the deenrolled dependent coverage row
186 for the backedout per_in_ler_id and for the old prtt_enrt_rslt_id. If exists then do not carry forward
187 the dependent*/
188 /*Bug 14119948: When the Coverage code is 'Current can keep or Choose But Start New', prttt_enrt_rslt_id changes,
189 hence while reimstating the enrollments check which dependents should be carry forwarded and when defaults are applied during
190 reinstatement, skip the below cursor checks as above portion of the cursor takes care of which dependents should be carry forwarded*/
191 and ( (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL and ben_lf_evt_clps_restore.g_dflt_during_reinstate = 'N'
192 and (
193 (l_old_prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
194 and ('1' = (select '1' from dual where
195 not exists
196 (select '1' from ben_le_clsn_n_rstr pdp_old
197 where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
198 and pdp_old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
199 and pdp_old.business_group_id = p_business_group_id
200 and pdp_old.dpnt_person_id = old.dpnt_person_id
201 and pdp_old.cvg_thru_dt <> hr_api.g_eot
202 and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'))))
203 or (l_old_prtt_enrt_rslt_id <> p_new_prtt_enrt_rslt_id
204 and ('1' = (select '1' from dual where
205 exists
206 (select '1' from ben_le_clsn_n_rstr pdp_old
207 ,ben_prtt_enrt_rslt_f pen5
208 where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
209 and pdp_old.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
210 and pdp_old.business_group_id = p_business_group_id
211 and pdp_old.dpnt_person_id = old.dpnt_person_id
212 and pdp_old.cvg_thru_dt = hr_api.g_eot
213 and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
214 and pen5.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
215 and pen5.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
216 and pen5.business_group_id = p_business_group_id
217 and pen5.prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id
218 and pen5.pl_typ_id in (select pl_typ_id from ben_elig_per_elctbl_chc
219 where elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id)
220 union
221 select '1' from ben_elig_cvrd_dpnt_f pdp_old
222 ,ben_prtt_enrt_rslt_f pen5
223 where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
224 and pdp_old.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
225 and pdp_old.business_group_id = p_business_group_id
226 and pdp_old.dpnt_person_id = old.dpnt_person_id
227 and pdp_old.cvg_thru_dt = hr_api.g_eot
228 and pen5.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
229 and pen5.prtt_enrt_rslt_id <> l_old_prtt_enrt_rslt_id
230 and pen5.business_group_id = p_business_group_id
231 and pen5.prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id
232 and pen5.pl_typ_id in (select pl_typ_id from ben_elig_per_elctbl_chc
233 where elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id) )))
234
235 )
236 )
237 ) or (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL and ben_lf_evt_clps_restore.g_dflt_during_reinstate = 'Y')
238 or (ben_manage_life_events.g_bckdt_per_in_ler_id is NULL )
239 )
240 and
241 (pcr.contact_type in
242 (select c.rlshp_typ_cd
243 from ben_dsgn_rqmt_f p,
244 ben_dsgn_rqmt_rlshp_typ c
245 where p.dsgn_rqmt_id = c.dsgn_rqmt_id
246 and ((p.pl_id = p_new_pl_id)
247 or
248 (p.oipl_id = p_new_oipl_id)
249 or
250 (p.opt_id = (select opt_id
251 from ben_oipl_f
252 where oipl_id = p_new_oipl_id
253 and p_effective_date between effective_start_date
254 and effective_end_date
255 and business_group_id = p_business_group_id)))
256 and p.dsgn_typ_cd = 'DPNT'
257 and p.grp_rlshp_cd is not null
258 and p.business_group_id = p_business_group_id
259 and p_effective_date between p.effective_start_date
260 and p.effective_end_date
261 and nvl(p.mx_dpnts_alwd_num,999) >=
262 (select count('s')
263 from ben_elig_dpnt new2,
264 per_contact_relationships pcr2
265 where new2.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
266 and new2.business_group_id = p_business_group_id
267 and new2.dpnt_person_id in
268 -- Make sure that the dpnt being counted was covered before
269 (select dpnt_person_id
270 from ben_elig_cvrd_dpnt_f ecd
271 where prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
272 and cvg_strt_dt is not null
273 and nvl(cvg_thru_dt, hr_api.g_eot) >=
274 nvl(pil2.lf_evt_ocrd_dt - 1, hr_api.g_eot)
275 and business_group_id = p_business_group_id
276 and p_effective_date between effective_start_date
277 and effective_end_date)
278 and new2.dpnt_person_id = pcr2.contact_person_id
279 and pcr2.person_id = p_person_id
280 and pcr2.contact_type in
281 (select rlshp_typ_cd
282 from ben_dsgn_rqmt_rlshp_typ c2
283 where c2.dsgn_rqmt_id = p.dsgn_rqmt_id)))
284 or not exists
285 (select 's'
286 from ben_dsgn_rqmt_f p3
287 where p3.grp_rlshp_cd is not null
288 and ((p3.pl_id = p_new_pl_id)
289 or
290 (p3.oipl_id = p_new_oipl_id)
291 or
292 (p3.opt_id = (select opt_id
293 from ben_oipl_f
294 where oipl_id = p_new_oipl_id
295 and p_effective_date between effective_start_date
296 and effective_end_date
297 and business_group_id = p_business_group_id)))
298 and p3.dsgn_typ_cd = 'DPNT'
299 and p3.business_group_id = p_business_group_id
300 and p_effective_date between p3.effective_start_date
301 and p3.effective_end_date))
302 and pil.per_in_ler_id=old.per_in_ler_id
303 and pil.business_group_id=p_business_group_id
304 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
305 and pil2.per_in_ler_id=new.per_in_ler_id
306 and pil2.business_group_id=p_business_group_id
307 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
308 ;
309 --
310 -- for copying dpnts when staying in the same coverage he was in before.
311 cursor exist_dpnt_c is
312 select ecd.elig_cvrd_dpnt_id,
313 ecd.dpnt_person_id,
314 ecd.elig_per_elctbl_chc_id,
315 ecd.object_version_number
316 from ben_elig_cvrd_dpnt_f ecd,
317 ben_per_in_ler pil
318 where ecd.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
319 and ecd.cvg_strt_dt is not null
320 and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
321 and ecd.business_group_id = p_business_group_id
322 and p_effective_date between ecd.effective_start_date
323 and ecd.effective_end_date
324 and pil.per_in_ler_id=ecd.per_in_ler_id
325 and pil.business_group_id=p_business_group_id
326 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
327 --
328 cursor old_bnf_c is
329 select pbn.*
330 from ben_pl_bnf_f pbn,
331 ben_per_in_ler pil
332 where pbn.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
333 and pbn.business_group_id = p_business_group_id
334 and p_effective_date between pbn.effective_start_date
335 and pbn.effective_end_date
336 and pil.per_in_ler_id=pbn.per_in_ler_id
337 and pil.business_group_id=pbn.business_group_id
338 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
339 ;
340
341 cursor c_tot_elig_dpnt
342 ( v_dsgn_rqmt_id number ,
343 v_grp_rlshp_cd varchar2 ) is
344 select count(old.elig_cvrd_dpnt_id)
345 from ben_elig_cvrd_dpnt_f old,
346 ben_elig_dpnt new,
347 ben_per_in_ler pil,
348 ben_per_in_ler pil2,
349 per_contact_relationships pcr
350 where old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
351 and old.cvg_strt_dt is not null
352 and nvl(old.cvg_thru_dt, hr_api.g_eot) >= nvl(pil2.lf_evt_ocrd_dt - 1,
353 hr_api.g_eot)
354 and old.business_group_id = p_business_group_id
355 and p_effective_date between old.effective_start_date
356 and old.effective_end_date
357 and new.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id
358 and new.business_group_id = p_business_group_id
359 and old.dpnt_person_id = new.dpnt_person_id
360 and pil.per_in_ler_id=old.per_in_ler_id
361 and pil.business_group_id=p_business_group_id
362 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
363 and pil2.per_in_ler_id=new.per_in_ler_id
364 and pil2.business_group_id=p_business_group_id
365 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
366 and pcr.person_id = p_person_id
367 and pcr.contact_person_id =new.dpnt_person_id
368 and p_effective_date between nvl(pcr.date_start,p_effective_date)
369 and nvl(pcr.date_end,p_effective_date)
370 --- validate the no of dpnt for the grp
371 and ( pcr.contact_type in
372 ( select drt.rlshp_typ_cd
373 from ben_dsgn_rqmt_f bdr ,
374 ben_dsgn_rqmt_rlshp_typ drt
375 where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
376 and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
377 and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
378 (bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
379 )
380 and p_effective_date between bdr.effective_start_date
381 and bdr.effective_end_date
382 )
383 --- if there is no relation typ defind take all
384 or
385 not exists
386 (select 'x' from ben_dsgn_rqmt_rlshp_typ drt
387 where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
388 )
389 ) ;
390
391 /* Bug: 3812994: If the new Option is Waive (or Plan is waived..),
392 then we do not copy the beneficiaries to the new result.
393 */
394 CURSOR c_waive_pl_opt IS
395 SELECT NULL
396 FROM ben_oipl_f oipl,
397 ben_opt_f opt
398 WHERE oipl.opt_id = opt.opt_id
399 AND oipl.oipl_id = p_new_oipl_id
400 AND p_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
401 AND p_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
402 AND NVL (opt.invk_wv_opt_flag, 'N') = 'Y'
403 UNION
404 SELECT NULL
405 FROM ben_pl_f pln
406 WHERE pln.pl_id = p_new_pl_id
407 AND p_effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date
408 AND NVL (pln.invk_dcln_prtn_pl_flag, 'N') = 'Y';
409
410 l_waive_pl_opt VARCHAR2(1);
411
412 -- Bug 9888746 :
413
414 -- try to see if for the p_new_prtt_enrt_rslt_id (newly created pen), there exists another pen correpsonding to the same
415 -- comp object that was created against this life event and is in the voided status. If yes, and if right now,
416 -- p_old_prtt_enrt_rslt_id is null, then initialize p_old_prtt_enrt_rslt_id with the pen_id of the voided pen record.
417
418 --Bug 14740884
419 cursor c_prev_pil(c_per_in_ler_id number ) is
420 select pil.per_in_ler_id
421 from ben_per_in_ler pil,
422 ben_ler_f ler
423 where pil.per_in_ler_id not in (c_per_in_ler_id)
424 and pil.person_id = p_person_id
425 and pil.ler_id = ler.ler_id
426 and p_effective_date between
427 ler.effective_start_date and ler.effective_end_date
428 and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
429 and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
430 order by pil.lf_evt_ocrd_dt desc;
431
432 l_prev_pil_id number(15);
433 --Bug 14740884
434 -- Getting the Pen_id of the comp object of the previous Per_in_ler
435
436 CURSOR c_check_old_pen IS
437 SELECT old_pen.prtt_enrt_rslt_id
438 from ben_prtt_enrt_rslt_f old_pen, ben_prtt_enrt_rslt_f new_pen
439 where old_pen.pgm_id = new_pen.pgm_id
440 -- and old_pen.pl_id = new_pen.pl_id
441 and old_pen.ptip_id = new_pen.ptip_id --Bug 14740884
442 -- and nvl(old_pen.oipl_id,1) = nvl(new_pen.oipl_id,1)
443 -- and old_pen.per_in_ler_id = new_pen.per_in_ler_id --Bug 14740884
444 and old_pen.per_in_ler_id = l_prev_pil_id --Bug 14740884
445 and new_pen.per_in_ler_id = p_per_in_ler_id
446 and new_pen.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
447 -- and old_pen.prtt_enrt_rslt_stat_cd = 'VOIDD' --Bug 14740884
448 and old_pen.prtt_enrt_rslt_stat_cd is null --Bug 14740884
449 and old_pen.sspndd_flag = 'N'
450 and new_pen.sspndd_flag = 'N' ;
451
452 -- Bug 9888746
453 begin
454 --
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 --Bug 14740884
458 open c_prev_pil(p_per_in_ler_id);
459 fetch c_prev_pil into l_prev_pil_id;
460 close c_prev_pil;
461 --Bug 14740884
462
463
464 -- Bug 9888746
465 l_old_prtt_enrt_rslt_id := p_old_prtt_enrt_rslt_id;
466
467 hr_utility.set_location('l_old_prtt_enrt_rslt_id '||l_old_prtt_enrt_rslt_id, 5);
468
469 if l_old_prtt_enrt_rslt_id is null then
470
471 open c_check_old_pen;
472 fetch c_check_old_pen into l_old_prtt_enrt_rslt_id;
473 close c_check_old_pen;
474
475 end if;
476
477 hr_utility.set_location('After c_check_old_pen l_old_prtt_enrt_rslt_id '||l_old_prtt_enrt_rslt_id, 12);
478
479 -- Bug 9888746
480
481 -- If participant is returning to his prior coverage that is
482 -- in effect at the time of the p_effective_date then we need to
483 -- go back to the old dependents (update existing cvrd dep rows by
484 -- updating cvg_thru_dt to null. This code assumes that dsgn requirements
485 -- did not change at the time of open enrollment (otherwise the
486 -- inheritance logic should still apply).
487 --
488 if p_return_to_exist_cvg_flag = 'Y' and
489 p_process_dpnt then
490 hr_utility.set_location('Restore existing cvg', 10);
491
492 FOR dpnt in exist_dpnt_c LOOP
493 --
494 if dpnt.elig_per_elctbl_chc_id = p_new_elig_per_elctbl_chc_id then
495 --
496 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
497 (p_validate => p_validate
498 ,p_business_group_id => p_business_group_id
499 ,p_elig_cvrd_dpnt_id => dpnt.elig_cvrd_dpnt_id
500 ,p_effective_start_date => l_effective_start_date
501 ,p_effective_end_date => l_effective_end_date
502 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
503 ,p_cvg_thru_dt => null
504 ,p_per_in_ler_id => p_per_in_ler_id
505 ,p_object_version_number => l_object_version_number
506 ,p_effective_date => p_effective_date
507 ,p_datetrack_mode => p_datetrack_mode
508 ,p_request_id => fnd_global.conc_request_id
509 ,p_program_application_id => fnd_global.prog_appl_id
510 ,p_program_id => fnd_global.conc_program_id
511 ,p_program_update_date => sysdate);
512 --
513 end if;
514 --
515 END LOOP;
516 --
517 -- Bug 1418754
518 --
519 ben_ELIG_CVRD_DPNT_api.chk_max_num_dpnt_for_pen (
520 p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id,
521 p_effective_date => p_effective_date,
522 p_business_group_id => p_business_group_id);
523 --
524 /*
525 -- same for bnf
526 --
527 FOR bnf in exist_bnf_c LOOP
528 --
529 ben_plan_beneficiary_api.update_plan_beneficiary
530 (p_validate => p_validate
531 ,p_pl_bnf_id => bnf.pl_bnf_id
532 ,P_DSG_THRU_DT => null
533 ,p_effective_start_date => l_effective_start_date
534 ,p_effective_end_date => l_effective_end_date
535 ,p_object_version_number => l_object_version_number
536 ,p_effective_date => p_effective_date
537 ,p_per_in_ler_id => p_per_in_ler_id
538 ,p_datetrack_mode => p_datetrack_mode
539 ,p_request_id => fnd_global.conc_request_id
540 ,p_program_application_id => fnd_global.prog_appl_id
541 ,p_program_id => fnd_global.conc_program_id
542 ,p_program_update_date => sysdate);
543 --
544 END LOOP;
545 */
546 --
547 else
548 -- We are not returning the the same comp object, try to inherit.
549 --
550 if p_process_dpnt then
551 -- Check if inheritance for dependents will apply.
552 -- Are there those previously covered that are eligible for new the chc_id?
553 --
554 open c_num_cvd_dpnt;
555 fetch c_num_cvd_dpnt into l_num_cov_dpnt_elig_new;
556 close c_num_cvd_dpnt;
557 --
558 -- Is updated result Plan type is the same as an old result Plan type?
559 --
560 if l_num_cov_dpnt_elig_new = 0 or
561 p_old_pl_typ_id <> p_new_pl_typ_id then
562 --
563 -- no inheritance
564 --
565 hr_utility.set_location('No inheritance possible:' || l_proc, 15);
566 --
567 l_inherit := FALSE;
568 --
569 else
570 --
571 -- check total max requirements
572 -- # 2646284
573 --- if conodtion was chnged to loop to validate all the
574 --- Relationship groups
575 open total_rqmt_c;
576 Loop
577 fetch total_rqmt_c into l_ttl_max_num, l_ttl_no_max_flag,
578 l_dsgn_rqmt_id,l_grp_rlshp_cd ;
579 --
580 if total_rqmt_c%notfound then
581 Exit ;
582 End if ;
583 hr_utility.set_location('grp_rlshp_cd ' || l_grp_rlshp_cd, 99 );
584 hr_utility.set_location('l_ttl_max_num '|| l_ttl_max_num, 99 );
585 --
586 --
587 l_ttl_rqmt_exist := TRUE;
588 --
589 open c_tot_elig_dpnt (l_dsgn_rqmt_id,l_grp_rlshp_cd) ;
590 fetch c_tot_elig_dpnt into l_num_cov_dpnt_elig_new ;
591 close c_tot_elig_dpnt ;
592 hr_utility.set_location(' total eligible ' || l_num_cov_dpnt_elig_new, 99 );
593
594 if l_ttl_no_max_flag = 'N' and
595 l_num_cov_dpnt_elig_new > l_ttl_max_num then
596 --
597 hr_utility.set_location('No inheritance, total max exceeded : ',99 );
598 --
599 -- no inheritance
600 --
601 l_inherit := FALSE;
602 exit ;
603 --
604 hr_utility.set_location(' inherit false ', 99 );
605 end if;
606 --
607 end loop; -- if a total requirement is found.
608 --
609 close total_rqmt_c;
610
611 end if;
612 --
613 if l_inherit then
614
615 hr_utility.set_location('Get cvg start dt'||l_proc, 26);
616 --
617 -- Calculate Dependents Coverage Start Date
618 -- dbms_output.put_line('Calculating cvg strt dt');
619 --
620 ben_determine_date.main
621 (p_date_cd => p_dpnt_cvg_strt_dt_cd
622 ,p_per_in_ler_id => null
623 ,p_person_id => null
624 ,p_pgm_id => null
625 ,p_pl_id => null
626 ,p_oipl_id => null
627 ,p_elig_per_elctbl_chc_id => p_new_elig_per_elctbl_chc_id
628 ,p_business_group_id => p_business_group_id
629 ,p_formula_id => p_dpnt_cvg_strt_dt_rl
630 ,p_effective_date => p_effective_date
631 ,p_returned_date => l_cvg_strt_dt);
632 --
633 if l_cvg_strt_dt is null then
634 -- error
635 --
636 fnd_message.set_name('BEN', 'BEN_91657_DPNT_CVG_STRT_DT');
637 fnd_message.raise_error;
638 end if;
639 --
640 -- Take the latter of the calculated date and p_enrt_cvg_strt_dt
641 --
642 if l_cvg_strt_dt > p_enrt_cvg_strt_dt then
643 --
644 l_cvg_strt_dt := p_enrt_cvg_strt_dt;
645 --
646 end if;
647 --
648 hr_utility.set_location('Cvg start dt ='||to_char(l_cvg_strt_dt), 25);
649
650 -- Loop thru dependents previously covered by the result:
651 --
652 hr_utility.set_location('Loop thru old dependents:'||l_proc, 30);
653 -- dbms_output.put_line('Start loop for old dpnts');
654 --
655 FOR copy_dpnt_rec in copy_dpnt_c LOOP
656 --
657 hr_utility.set_location('Inherit dependent, rlshp max OK:'||l_proc, 45);
658 --
659 hook_dpnt
660 (p_validate => FALSE
661 ,p_elig_dpnt_id => copy_dpnt_rec.new_dpnt
662 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
663 ,p_old_prtt_enrt_rslt_id => l_old_prtt_enrt_rslt_id
664 ,p_new_enrt_rslt_ovn => p_new_enrt_rslt_ovn
665 ,p_pgm_id => p_pgm_id
666 ,p_cvg_strt_dt => l_cvg_strt_dt
667 ,p_effective_date => p_effective_date
668 ,p_old_elig_cvrd_dpnt_id => copy_dpnt_rec.old_dpnt
669 ,p_per_in_ler_id => p_per_in_ler_id
670 ,p_business_group_id => p_business_group_id
671 ,p_datetrack_mode => p_datetrack_mode
672 ,p_multi_row_actn => p_multi_row_actn);
673 --
674 END LOOP;
675 end if; -- if l-inherit
676 end if; -- if p_process_dpnt
677 --
678 if p_process_bnf then
679 --
680 -- For now, since beneficiaries are attached to the plan inherit them if
681 -- no plan change:
682 --
683 If p_old_pl_id = p_new_pl_id then
684 --
685 hr_utility.set_location('Plans the same, copy bnfs', 55);
686 --
687 /* Bug: 3812994: If the new Option is Waive (or Plan is waived..),
688 then we do not copy the beneficiaries to the new result.
689 */
690 OPEN c_waive_pl_opt;
691 FETCH c_waive_pl_opt INTO l_waive_pl_opt;
692 IF c_waive_pl_opt%FOUND THEN
693 hr_utility.set_location('Waive Opt/Plan. Need not carry bnf ', 60);
694 hr_utility.set_location('Exiting'||l_proc, 99);
695 return;
696 END IF;
697 -- End 3812994 changes.
698 --
699 FOR bnf in old_bnf_c LOOP
700 --
701 ben_plan_beneficiary_api.create_plan_beneficiary
702 (p_validate => p_validate
703 ,p_pl_bnf_id => l_pl_bnf_id
704 ,p_effective_start_date => l_bnf_effective_start_date
705 ,p_effective_end_date => l_bnf_effective_end_date
706 ,p_business_group_id => p_business_group_id
707 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
708 ,p_bnf_person_id => bnf.bnf_person_id
709 ,p_organization_id => bnf.organization_id
710 ,p_ttee_person_id => bnf.ttee_person_id
711 ,p_prmry_cntngnt_cd => bnf.prmry_cntngnt_cd
712 ,p_pct_dsgd_num => bnf.pct_dsgd_num
713 ,p_amt_dsgd_val => bnf.amt_dsgd_val
714 ,p_amt_dsgd_uom => bnf.amt_dsgd_uom
715 ,p_addl_instrn_txt => bnf.addl_instrn_txt
716 ,p_per_in_ler_id => p_per_in_ler_id
717 ,p_dsgn_strt_dt => p_effective_date
718 ,p_pbn_attribute_category => bnf.pbn_attribute_category
719 ,p_pbn_attribute1 => bnf.pbn_attribute1
720 ,p_pbn_attribute2 => bnf.pbn_attribute2
721 ,p_pbn_attribute3 => bnf.pbn_attribute3
722 ,p_pbn_attribute4 => bnf.pbn_attribute4
723 ,p_pbn_attribute5 => bnf.pbn_attribute5
724 ,p_pbn_attribute6 => bnf.pbn_attribute6
725 ,p_pbn_attribute7 => bnf.pbn_attribute7
726 ,p_pbn_attribute8 => bnf.pbn_attribute8
727 ,p_pbn_attribute9 => bnf.pbn_attribute9
728 ,p_pbn_attribute10 => bnf.pbn_attribute10
729 ,p_pbn_attribute11 => bnf.pbn_attribute11
730 ,p_pbn_attribute12 => bnf.pbn_attribute12
731 ,p_pbn_attribute13 => bnf.pbn_attribute13
732 ,p_pbn_attribute14 => bnf.pbn_attribute14
733 ,p_pbn_attribute15 => bnf.pbn_attribute15
734 ,p_pbn_attribute16 => bnf.pbn_attribute16
735 ,p_pbn_attribute17 => bnf.pbn_attribute17
736 ,p_pbn_attribute18 => bnf.pbn_attribute18
737 ,p_pbn_attribute19 => bnf.pbn_attribute19
738 ,p_pbn_attribute20 => bnf.pbn_attribute20
739 ,p_pbn_attribute21 => bnf.pbn_attribute21
740 ,p_pbn_attribute22 => bnf.pbn_attribute22
741 ,p_pbn_attribute23 => bnf.pbn_attribute23
742 ,p_pbn_attribute24 => bnf.pbn_attribute24
743 ,p_pbn_attribute25 => bnf.pbn_attribute25
744 ,p_pbn_attribute26 => bnf.pbn_attribute26
745 ,p_pbn_attribute27 => bnf.pbn_attribute27
746 ,p_pbn_attribute28 => bnf.pbn_attribute28
747 ,p_pbn_attribute29 => bnf.pbn_attribute29
748 ,p_pbn_attribute30 => bnf.pbn_attribute30
749 ,p_request_id => fnd_global.conc_request_id
750 ,p_program_application_id => fnd_global.prog_appl_id
751 ,p_program_id => fnd_global.conc_program_id
752 ,p_program_update_date => sysdate
753 ,p_object_version_number => l_bnf_object_version_number
754 ,p_multi_row_actn => p_multi_row_actn
755 ,p_effective_date => p_effective_date);
756 --
757 END LOOP;
758 --
759 End if;
760 end if; -- if p_process_bnf
761 --
762 end if;
763 --
764 hr_utility.set_location('Exiting'||l_proc, 99);
765
766 --
767
768 --
769 End recycle_dpnt_bnf;
770 --
771 -- ----------------------------------------------------------------------------
772 -- |----------------------------< hook_dpnt >---------------------------------|
773 -- ----------------------------------------------------------------------------
774 --
775 Procedure hook_dpnt
776 (p_validate in boolean default false
777 ,p_elig_dpnt_id in number
778 ,p_prtt_enrt_rslt_id in number
779 ,p_old_prtt_enrt_rslt_id in number
780 ,p_new_enrt_rslt_ovn in out nocopy number
781 ,p_pgm_id in number
782 ,p_cvg_strt_dt in date
783 ,p_effective_date in date
784 ,p_old_elig_cvrd_dpnt_id in number
785 ,p_per_in_ler_id in number
786 ,p_business_group_id in number
787 ,p_datetrack_mode in varchar2
788 ,p_multi_row_actn in BOOLEAN default FALSE)
789 IS
790 --
791 l_proc varchar2(72) := g_package||'hook_dpnt';
792 l_cvg_strt_dt date;
793 l_effective_start_date date;
794 l_effective_end_date date;
795 l_object_version_number number(9);
796 l_cvrd_dpnt_ctfn_prvdd_id number(15);
797 l_dsgn_lvl_cd varchar2(30);
798 l_actn_typ_id number(15);
799 l_prtt_enrt_actn_id number(15);
800 l_cmpltd_dt date;
801 l_actn_object_version_number number(15);
802 l_actn_effective_start_date date;
803 l_actn_effective_end_date date;
804 l_ctfn_rqd_flag varchar2(30);
805 l_pdp_object_version_number number(9);
806 l_elig_cvrd_dpnt_id number(15);
807 --
808 -- Cursor to fetch the designation level code
809 --
810 cursor dsgn_lvl_c
811 is
812 select dpnt_dsgn_lvl_cd
813 from ben_pgm_f
814 where pgm_id = p_pgm_id
815 and business_group_id = p_business_group_id
816 and p_effective_date between effective_start_date
817 and effective_end_date;
818 --
819 -- Cursor to retrieve dependant ctfn required flags at the pgm level
820 --
821 cursor c_dpnt_pgm
822 is
823 select pgm.dpnt_dsgn_no_ctfn_rqd_flag
824 from ben_pgm_f pgm
825 where pgm.pgm_id = p_pgm_id
826 and pgm.business_group_id = p_business_group_id
827 and p_effective_date between pgm.effective_start_date
828 and pgm.effective_end_date;
829 --
830 -- cursor to retrieve dpnts' required-info-flags at the ptip level
831 --
832 cursor c_dpnt_ptip
833 is
834 select ptip.dpnt_cvg_no_ctfn_rqd_flag
835 from ben_ptip_f ptip
836 where ptip.ptip_id = (select ptip_id
837 from ben_prtt_enrt_rslt_f
838 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
839 and business_group_id =p_business_group_id
840 and prtt_enrt_rslt_stat_cd is null
841 and p_effective_date between effective_start_date
842 and effective_end_date)
843 and ptip.business_group_id = p_business_group_id
844 and p_effective_date between
845 ptip.effective_start_date and ptip.effective_end_date;
846 --
847 -- Cursor to fetch certifications provided
848 --
849 cursor dpnt_ctfn_c
850 is
851 select *
852 from ben_cvrd_dpnt_ctfn_prvdd_f
853 where elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
854 and business_group_id = p_business_group_id
855 and p_effective_date between effective_start_date
856 and effective_end_date;
857 --
858 --Bug# 5572910
859 --
860 cursor c_dpnt_pea is
861 select pea.prtt_enrt_rslt_id,
862 pea.prtt_enrt_actn_id,
863 pea.actn_typ_id,
864 pea.object_version_number,
865 pea.effective_start_date,
866 pea.effective_end_date
867 from ben_prtt_enrt_actn_f pea,
868 ben_actn_typ eat
869 where pea.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
870 and pea.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
871 and pea.cmpltd_dt is not null
872 and eat.type_cd in ('DDCTFN')
873 and eat.actn_typ_id=pea.actn_typ_id
874 and p_effective_date
875 between pea.effective_start_date and pea.effective_end_date; -- bug 6793512
876 -- order by pea.prtt_enrt_actn_id,pea.effective_start_date,pea.effective_end_date;
877 --
878 l_dpnt_pea c_dpnt_pea%rowtype;
879 l_pea_object_version_number ben_prtt_enrt_actn_f.object_version_number%TYPE;
880 l_pea_effective_start_date ben_prtt_enrt_actn_f.effective_start_date%TYPE;
881 l_pea_effective_end_date ben_prtt_enrt_actn_f.effective_end_date%TYPE;
882 --
883 cursor c_ccp (ll_prtt_enrt_actn_id number) is
884 select ccp.cvrd_dpnt_ctfn_prvdd_id
885 ,ccp.effective_start_date
886 ,ccp.effective_end_date
887 ,ccp.object_version_number
888 ,ccp.prtt_enrt_actn_id
889 from ben_cvrd_dpnt_ctfn_prvdd_f ccp
890 where ccp.prtt_enrt_actn_id = ll_prtt_enrt_actn_id
891 and ccp.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
892 and ccp.business_group_id=p_business_group_id
893 and ccp.dpnt_dsgn_ctfn_recd_dt is not null
894 and p_effective_date between ccp.effective_start_date
895 and ccp.effective_end_date;
896 --
897 l_ccp_rec c_ccp%rowtype;
898 l_ccp_object_version_number ben_cvrd_dpnt_ctfn_prvdd_f.object_version_number%TYPE;
899 l_ccp_effective_start_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_start_date%TYPE;
900 l_ccp_effective_end_date ben_cvrd_dpnt_ctfn_prvdd_f.effective_end_date%TYPE;
901 l_ccp_update_flag varchar2(30) := 'N'; --6613891
902 --
903 -- Bug 6793512
904 l_correction boolean;
905 l_update boolean;
906 l_update_override boolean;
907 l_update_change_insert boolean;
908 l_datetrack_mode varchar2(20);
909 -- Bug 6793512
910
911 --Bug# 5572910
912 --
913
914 begin
915 --
916 hr_utility.set_location('Entering'||l_proc, 5);
917 --
918 --
919 -- update cvrd_flag, rslt_id, etc. ...
920 --
921 hr_utility.set_location('Update dpnt info'||l_proc, 20);
922 if p_multi_row_actn then
923 hr_utility.set_location('LAMC manage multi row', 20);
924 else hr_utility.set_location('LAMC manage NOT multi row', 20);
925 end if;
926
927 ben_ELIG_DPNT_api.process_dependent(
928 p_elig_dpnt_id => p_elig_dpnt_id,
929 p_business_group_id => p_business_group_id,
930 p_effective_date => p_effective_date,
931 p_cvg_strt_dt => p_cvg_strt_dt,
932 p_cvg_thru_dt => hr_api.g_eot,
933 p_datetrack_mode => p_datetrack_mode,
934 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
935 p_effective_start_date => l_effective_start_date,
936 p_effective_end_date => l_effective_end_date,
937 p_object_version_number => l_pdp_object_version_number,
938 p_multi_row_actn => p_multi_row_actn);
939 --
940 -- dbms_output.put_line('Dpnt Info Done');
941 --
942 --Bug# 5572910
943 --
944 hr_utility.set_location('10297977 p_old_prtt_enrt_rslt_id' || p_old_prtt_enrt_rslt_id ,1114);
945 hr_utility.set_location('10297977 p_prtt_enrt_rslt_id' || p_prtt_enrt_rslt_id ,1114);
946
947 if (p_old_prtt_enrt_rslt_id = p_prtt_enrt_rslt_id) then --10297977
948
949 open c_dpnt_pea;
950 --
951 fetch c_dpnt_pea into l_dpnt_pea;
952 --
953 hr_utility.set_location('l_dpnt_pea.prtt_enrt_actn_id'||l_dpnt_pea.prtt_enrt_actn_id,1114);
954 --
955 if c_dpnt_pea%found then
956 l_ccp_update_flag := 'N'; --6613891
957 hr_utility.set_location('In c_dpnt_pea. l_ccp_update_flag = '|| l_ccp_update_flag,8085);
958 --
959 open c_ccp(l_dpnt_pea.prtt_enrt_actn_id);
960 loop
961 fetch c_ccp into l_ccp_rec;
962 exit when c_ccp%notfound;
963 l_ccp_update_flag := 'Y'; --6613891
964 --
965 hr_utility.set_location('l_elig_cvrd_dpnt_id'||l_elig_cvrd_dpnt_id,1114);
966 --
967 -- Bug 6793512
968 --
969 dt_api.find_dt_upd_modes
970 (p_effective_date => p_effective_date,
971 p_base_table_name => 'ben_cvrd_dpnt_ctfn_prvdd_f',
972 p_base_key_column => 'cvrd_dpnt_ctfn_prvdd_id',
973 p_base_key_value => l_ccp_rec.cvrd_dpnt_ctfn_prvdd_id,
974 p_correction => l_correction,
975 p_update => l_update,
976 p_update_override => l_update_override,
977 p_update_change_insert => l_update_change_insert);
978 --
979 if l_update_override then
980 l_datetrack_mode := hr_api.g_update_override;
981 elsif l_update then
982 l_datetrack_mode := hr_api.g_update;
983 else
984 l_datetrack_mode := hr_api.g_correction;
985 end if;
986 --
987 hr_utility.set_location('rtagarra '||l_datetrack_mode,9653);
988 -- Bug 6793512
989 ben_CVRD_DPNT_CTFN_PRVDD_api.update_CVRD_DPNT_CTFN_PRVDD
990 (
991 p_validate => FALSE
992 ,p_cvrd_dpnt_ctfn_prvdd_id => l_ccp_rec.cvrd_dpnt_ctfn_prvdd_id
993 ,p_effective_start_date => l_ccp_effective_start_date
994 ,p_effective_end_date => l_ccp_effective_end_date
995 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
996 ,p_prtt_enrt_actn_id => l_ccp_rec.prtt_enrt_actn_id
997 ,p_object_version_number => l_ccp_rec.object_version_number
998 ,p_effective_date => p_effective_date
999 ,p_datetrack_mode => l_datetrack_mode
1000 );
1001 --
1002 end loop;
1003 --
1004 close c_ccp;
1005
1006 --6613891
1007 hr_utility.set_location('l_ccp_update_flag = ' || l_ccp_update_flag,8085);
1008 --
1009
1010 if l_ccp_update_flag = 'Y' then
1011 --
1012 -- Bug 6793512
1013 --
1014 dt_api.find_dt_upd_modes
1015 (p_effective_date => p_effective_date,
1016 p_base_table_name => 'ben_prtt_enrt_actn_f',
1017 p_base_key_column => 'prtt_enrt_actn_id',
1018 p_base_key_value => l_dpnt_pea.prtt_enrt_actn_id,
1019 p_correction => l_correction,
1020 p_update => l_update,
1021 p_update_override => l_update_override,
1022 p_update_change_insert => l_update_change_insert);
1023 --
1024 if l_update_override then
1025 l_datetrack_mode := hr_api.g_update_override;
1026 elsif l_update then
1027 l_datetrack_mode := hr_api.g_update;
1028 else
1029 l_datetrack_mode := hr_api.g_correction;
1030 end if;
1031 --
1032 hr_utility.set_location('rtagarra '||l_datetrack_mode,9653);
1033 --
1034 -- Bug 6793512
1035 ben_PRTT_ENRT_ACTN_api.update_PRTT_ENRT_ACTN
1036 (
1037 p_validate => FALSE
1038 ,p_effective_start_date => l_pea_effective_start_date
1039 ,p_effective_end_date => l_pea_effective_end_date
1040 ,p_prtt_enrt_actn_id => l_dpnt_pea.prtt_enrt_actn_id
1041 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1042 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1043 ,p_object_version_number => l_dpnt_pea.object_version_number
1044 ,p_effective_date => p_effective_date
1045 ,p_datetrack_mode => l_datetrack_mode
1046 ,p_rslt_object_version_number => l_pea_object_version_number
1047 );
1048 end if;
1049 --
1050 end if;
1051 --
1052 close c_dpnt_pea;
1053
1054 end if; --10297977
1055
1056 --Bug# 5572910
1057
1058 -- copy certifications at Program or Plan Type in Program levels
1059 --
1060 if p_pgm_id is not null then
1061 --
1062 open dsgn_lvl_c;
1063 fetch dsgn_lvl_c into l_dsgn_lvl_cd;
1064 --
1065 if dsgn_lvl_c%FOUND then
1066 --
1067 if l_dsgn_lvl_cd <> 'PL' then
1068 --
1069 if l_dsgn_lvl_cd = 'PGM' then
1070 --
1071 open c_dpnt_pgm;
1072 fetch c_dpnt_pgm into l_ctfn_rqd_flag;
1073 close c_dpnt_pgm;
1074 --
1075 elsif l_dsgn_lvl_cd = 'PTIP' then
1076 --
1077 open c_dpnt_ptip;
1078 fetch c_dpnt_ptip into l_ctfn_rqd_flag;
1079 close c_dpnt_ptip;
1080 --
1081 end if;
1082 --
1083 -- Get the actn type id
1084 --
1085 l_actn_typ_id := ben_enrollment_action_items.get_actn_typ_id
1086 (p_type_cd => 'DDCTFN'
1087 ,p_business_group_id => p_business_group_id);
1088 --
1089 ben_enrollment_action_items.get_prtt_enrt_actn_id
1090 (p_actn_typ_id => l_actn_typ_id,
1091 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
1092 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
1093 p_effective_date => p_effective_date,
1094 p_business_group_id => p_business_group_id,
1095 p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
1096 p_cmpltd_dt => l_cmpltd_dt,
1097 p_object_version_number => l_actn_object_version_number);
1098 --
1099 if l_prtt_enrt_actn_id is null then
1100 --
1101 FOR ctfn_rec in dpnt_ctfn_c LOOP
1102 --
1103 -- Certification needs to be created, create a action item
1104 -- as none exists.
1105 -- Create it ONCE.
1106 --
1107 if l_prtt_enrt_actn_id is null then
1108 --
1109 ben_enrollment_action_items.write_new_action_item
1110 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1111 ,p_rslt_object_version_number => p_new_enrt_rslt_ovn
1112 ,p_actn_typ_id => l_actn_typ_id
1113 ,p_effective_date => p_effective_date
1114 ,p_post_rslt_flag => 'N'
1115 ,p_business_group_id => p_business_group_id
1116 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1117 ,p_rqd_flag => l_ctfn_rqd_flag
1118 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
1119 ,p_object_version_number => l_actn_object_version_number);
1120 --
1121 end if;
1122 --
1123 ben_cvrd_dpnt_ctfn_prvdd_api.create_cvrd_dpnt_ctfn_prvdd
1124 (p_validate => false
1125 ,p_cvrd_dpnt_ctfn_prvdd_id => l_cvrd_dpnt_ctfn_prvdd_id
1126 ,p_effective_start_date => l_effective_start_date
1127 ,p_effective_end_date => l_effective_end_date
1128 ,p_dpnt_dsgn_ctfn_typ_cd => ctfn_rec.dpnt_dsgn_ctfn_typ_cd
1129 ,p_dpnt_dsgn_ctfn_rqd_flag => ctfn_rec.dpnt_dsgn_ctfn_rqd_flag
1130 ,p_dpnt_dsgn_ctfn_recd_dt => ctfn_rec.dpnt_dsgn_ctfn_recd_dt
1131 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1132 ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
1133 ,p_business_group_id => ctfn_rec.business_group_id
1134 ,p_ccp_attribute_category => ctfn_rec.ccp_attribute_category
1135 ,p_ccp_attribute1 => ctfn_rec.ccp_attribute1
1136 ,p_ccp_attribute2 => ctfn_rec.ccp_attribute2
1137 ,p_ccp_attribute3 => ctfn_rec.ccp_attribute3
1138 ,p_ccp_attribute4 => ctfn_rec.ccp_attribute4
1139 ,p_ccp_attribute5 => ctfn_rec.ccp_attribute5
1140 ,p_ccp_attribute6 => ctfn_rec.ccp_attribute6
1141 ,p_ccp_attribute7 => ctfn_rec.ccp_attribute7
1142 ,p_ccp_attribute8 => ctfn_rec.ccp_attribute8
1143 ,p_ccp_attribute9 => ctfn_rec.ccp_attribute9
1144 ,p_ccp_attribute10 => ctfn_rec.ccp_attribute10
1145 ,p_ccp_attribute11 => ctfn_rec.ccp_attribute11
1146 ,p_ccp_attribute12 => ctfn_rec.ccp_attribute12
1147 ,p_ccp_attribute13 => ctfn_rec.ccp_attribute13
1148 ,p_ccp_attribute14 => ctfn_rec.ccp_attribute14
1149 ,p_ccp_attribute15 => ctfn_rec.ccp_attribute15
1150 ,p_ccp_attribute16 => ctfn_rec.ccp_attribute16
1151 ,p_ccp_attribute17 => ctfn_rec.ccp_attribute17
1152 ,p_ccp_attribute18 => ctfn_rec.ccp_attribute18
1153 ,p_ccp_attribute19 => ctfn_rec.ccp_attribute19
1154 ,p_ccp_attribute20 => ctfn_rec.ccp_attribute20
1155 ,p_ccp_attribute21 => ctfn_rec.ccp_attribute21
1156 ,p_ccp_attribute22 => ctfn_rec.ccp_attribute22
1157 ,p_ccp_attribute23 => ctfn_rec.ccp_attribute23
1158 ,p_ccp_attribute24 => ctfn_rec.ccp_attribute24
1159 ,p_ccp_attribute25 => ctfn_rec.ccp_attribute25
1160 ,p_ccp_attribute26 => ctfn_rec.ccp_attribute26
1161 ,p_ccp_attribute27 => ctfn_rec.ccp_attribute27
1162 ,p_ccp_attribute28 => ctfn_rec.ccp_attribute28
1163 ,p_ccp_attribute29 => ctfn_rec.ccp_attribute29
1164 ,p_ccp_attribute30 => ctfn_rec.ccp_attribute30
1165 ,p_object_version_number => l_object_version_number
1166 ,p_effective_date => p_effective_date
1167 ,p_request_id => fnd_global.conc_request_id
1168 ,p_program_application_id => fnd_global.prog_appl_id
1169 ,p_program_id => fnd_global.conc_program_id
1170 ,p_program_update_date => sysdate);
1171 --
1172 END LOOP;
1173 --
1174 end if;
1175 --
1176 end if;
1177 --
1178 end if;
1179 --
1180 close dsgn_lvl_c;
1181 --
1182 end if;
1183 --
1184 hr_utility.set_location('Exiting'||l_proc, 25);
1185 --
1186 End hook_dpnt;
1187 --
1188 end ben_mng_dpnt_bnf;