[Home] [Help]
PACKAGE BODY: APPS.BEN_PERSON_RECORD
Source
6 procedure GET_BEN_DETAILS (p_ben_details in out NOCOPY ben_record_details,
1 PACKAGE BODY BEN_PERSON_RECORD AS
2 /* $Header: benperrec.pkb 120.0.12010000.3 2009/04/10 12:55:37 pvelvano noship $ */
3
4
5 -- Procedure to populate the record structure with Person Benefits Data
7 p_person_id NUMBER,p_effective_date DATE,
8 p_business_group_id NUMBER) is
9
10 /*Cursor to fetch Dependent Enrollments Data*/
11 cursor c_get_dep_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
12
13 select dependent_full_name, relation, plan_type_name, 'COVERED'
14 from
15 (
16 select pln.name Plan_Name,
17 opt.name Option_Name,
18 plt.name plan_type_name,
19 (select name from ben_pgm_f pgm
20 where p_effective_date between pgm.effective_start_date
21 and pgm.effective_end_date
22 and pgm.pgm_id=epe.pgm_id
23 and pgm.business_group_id = p_business_group_id) Program_Name,
24 ppf.first_name||' '||ppf.last_name || ' ' || ppf.suffix Dependent,
25 ppf.national_identifier Ssn,
26 (select HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type)
27 from per_contact_relationships pcr
28 where pcr.personal_flag = 'Y'
29 and pcr.person_id = pen.person_id
30 and pcr.contact_person_id = pdp.dpnt_person_id
31 and p_effective_date between nvl(pcr.date_start, p_effective_date )
32 and nvl(pcr.date_end, p_effective_date )
33 and decode(pcr.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8) <=
34 (select decode(pcr2.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8)
35 from per_contact_relationships pcr2
36 where pcr2.person_id = pcr.person_id
37 and pcr2.contact_person_id = pcr.contact_person_id
38 and p_effective_date between nvl(pcr2.date_start, p_effective_date )
39 and nvl(pcr2.date_end, p_effective_date )
40 and pcr2.personal_flag = 'Y'
41 )
42 and rownum = 1
43 ) Relation,
44 epe.per_in_ler_id,
45 epe.pgm_id,
46 ler.name le_name,
47 ppf.full_name dependent_full_name,
48 pdp.cvg_strt_dt,
49 to_date(null) cvg_thru_dt
50 from ben_elig_per_elctbl_chc epe,
51 ben_prtt_enrt_rslt_f pen,
52 ben_elig_cvrd_dpnt_f pdp,
53 per_contact_relationships pcr,
54 per_people_f ppf,
55 ben_pl_typ_f plt,
56 ben_pl_f pln,
57 ben_opt_f opt,
58 ben_oipl_f oipl,
59 ben_per_in_ler pil,
60 ben_ler_f ler
61 where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
62 and epe.pl_id = pln.pl_id
63 and epe.pl_typ_id = plt.pl_typ_id
64 and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
65 and pen.prtt_enrt_rslt_stat_cd is null
66 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
67 and pdp.dpnt_person_id = pcr.contact_person_id
68 and pcr.contact_person_id = ppf.person_id
69 and pcr.personal_flag = 'Y'
70 and epe.prtt_enrt_rslt_id is not null
71 and epe.per_in_ler_id = pil.per_in_ler_id
72 and pil.ler_id = ler.ler_id
73 and pcr.person_id = pil.person_id
74 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
75 -- Code Changes for bug 7689952 - Start
76 --and pil.per_in_ler_id = l_per_in_ler_id
77 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
78 and pil.per_in_ler_id = pen.per_in_ler_id
79 and pen.enrt_cvg_thru_dt >= p_effective_date
80 -- Code Changes for bug 7689952 - End
81 --and epe.pgm_id = :8
82 and pil.person_id = p_person_id
83 and p_effective_date between ler.effective_start_date
84 and ler.effective_end_date
85 and p_effective_date between pdp.effective_start_date
86 and pdp.effective_end_date
87 and p_effective_date between pln.effective_start_date
88 and pln.effective_end_date
89 and p_effective_date between plt.effective_start_date
90 and plt.effective_end_date
91 and p_effective_date between nvl(pcr.date_start, p_effective_date )
92 and nvl(pcr.date_end, p_effective_date )
93 and p_effective_date between ppf.effective_start_date
94 and ppf.effective_end_date
95 and p_effective_date between pen.effective_start_date
96 and pen.effective_end_date
97 and pdp.cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
98 and pdp.per_in_ler_id = pil.per_in_ler_id
99 and pen.oipl_id = oipl.oipl_id(+)
100 and oipl.opt_id = opt.opt_id(+)
101 and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
102 and p_effective_date between
103 oipl.effective_start_date (+) and
104 oipl.effective_end_date (+)
105 and p_effective_date between
106 opt.effective_start_date (+) and
107 opt.effective_end_date (+)
108 and pcr.business_group_id = p_business_group_id
109 and ppf.business_group_id = p_business_group_id
110 and plt.business_group_id = p_business_group_id
111 and pln.business_group_id = p_business_group_id
112 and opt.business_group_id = p_business_group_id
113 and oipl.business_group_id = p_business_group_id
114 and pil.business_group_id = p_business_group_id
115 and epe.business_group_id = p_business_group_id
116 and pen.business_group_id = p_business_group_id
117 and pdp.business_group_id = p_business_group_id
118 and ler.business_group_id = p_business_group_id
119 );
120
121 /*Cursor to fetch Beneficaries Data*/
125 from (select plt.name Plan_Type_Name,pln.name Plan_Name, opt.name Option_Name,
122 cursor c_get_ben_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
123 select Plan_Type_Name,Plan_Name, Option_Name, Beneficiary, Ssn, Relation, Primary_Bnf, Contingent_Bnf,
124 le_name,beneficiary_full_name,Primary_Bnf_Amt,Contingent_Bnf_Amt
126 ppf.last_name last_name,
127 ppf.first_name first_name,
128 decode(pbn.organization_id, null, ppf.first_name||
129 ' '||ppf.last_name || ' ' || ppf.suffix,
130 org.name) Beneficiary,
131 nvl(ppf.full_name, org.name) beneficiary_full_name,
132 ppf.national_identifier Ssn,
133 nvl(HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type),
134 decode(pbn.organization_id, null,
135 HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','SLF'),
136 HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','TP'))) Relation, sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.pct_dsgd_num,0)) Primary_Bnf, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.pct_dsgd_num,0)) Contingent_Bnf,
137 sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.amt_dsgd_val,0)) Primary_Bnf_Amt, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.amt_dsgd_val,0)) Contingent_Bnf_Amt,
138 pcr.contact_type contact_type,
139 ler.name le_name,
140 ppf.date_of_birth date_of_birth,
141 pen.ptip_ordr_num ptip_ordr_num,
142 pen.plip_ordr_num plip_ordr_num,
143 pen.pl_ordr_num pl_ordr_num,
144 pen.oipl_ordr_num oipl_ordr_num,
145 pen.bnft_ordr_num bnft_ordr_num
146 from per_people_f ppf,
147 per_contact_relationships pcr,
148 ben_prtt_enrt_rslt_f pen,
149 ben_pl_bnf_f pbn,
150 ben_pl_typ_f plt,
151 ben_pl_f pln,
152 hr_all_organization_units org,
153 ben_opt_f opt,
154 ben_oipl_f oipl,
155 ben_per_in_ler pil,
156 ben_ler_f ler
157 where pen.pl_id = pln.pl_id
158 -- Code Changes for bug 7689952 - Start
159 --and (pen.per_in_ler_id = l_per_in_ler_id)
160 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
161 and pen.enrt_cvg_thru_dt >= p_effective_date
162 -- Code Changes for bug 7689952 - End
163 and pen.oipl_id = oipl.oipl_id(+)
164 and oipl.opt_id = opt.opt_id(+)
165 and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
166 and pln.invk_dcln_prtn_pl_flag = 'N'
167 and pen.person_id = p_person_id
168 and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
169 and pbn.per_in_ler_id = pil.per_in_ler_id
170 and pil.ler_id = ler.ler_id
171 and exists (select null from ben_per_in_ler pil
172 where pil.per_in_ler_id = pbn.per_in_ler_id
173 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
174 and pen.prtt_enrt_rslt_stat_cd IS NULL
175 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
176 and pen.pl_typ_id = plt.pl_typ_id
177 and pcr.personal_flag(+) = 'Y'
178 and pcr.person_id(+) = p_person_id
179 and pbn.bnf_person_id = pcr.contact_person_id(+)
180 and pbn.bnf_person_id = ppf.person_id(+)
181 and pbn.organization_id = org.organization_id(+)
182 and p_effective_date between ler.effective_start_date
183 and ler.effective_end_date
184 and p_effective_date between plt.effective_start_date
185 and plt.effective_end_date
186 and p_effective_date between pbn.effective_start_date
187 and pbn.effective_end_date
188 and p_effective_date between
189 nvl(ppf.effective_start_date, p_effective_date ) and
190 nvl(ppf.effective_end_date, p_effective_date )
191 and p_effective_date between pen.effective_start_date
192 and pen.effective_end_date
193 and p_effective_date between pln.effective_start_date
194 and pln.effective_end_date
195 and p_effective_date between
196 nvl(org.date_from, p_effective_date ) and
197 nvl(org.date_to, p_effective_date )
198 and p_effective_date between
199 oipl.effective_start_date (+) and
200 oipl.effective_end_date (+)
201 and p_effective_date between
202 opt.effective_start_date (+) and
203 opt.effective_end_date (+)
204 and ppf.business_group_id = p_business_group_id
205 and pcr.business_group_id = p_business_group_id
206 and pen.business_group_id = p_business_group_id
207 and pbn.business_group_id = p_business_group_id
208 and plt.business_group_id = p_business_group_id
209 and pln.business_group_id = p_business_group_id
210 and opt.business_group_id = p_business_group_id
211 and oipl.business_group_id = p_business_group_id
212 and org.business_group_id(+) = p_business_group_id
213 and ler.business_group_id = p_business_group_id
214 and (pcr.contact_relationship_id is null or
215 (pcr.contact_relationship_id is not null and
216 (p_effective_date between
217 nvl(pcr.date_start, p_effective_date ) and
218 nvl(pcr.date_end, p_effective_date )) or
219 ((pcr.date_start = (select max(pcr2.date_start)
220 from per_contact_relationships pcr2
221 where pcr2.contact_person_id = pcr.contact_person_id
222 and pcr2.person_id = pcr.person_id
223 and pcr2.personal_flag = 'Y')) and
224 not exists (select null
225 from PER_CONTACT_RELATIONSHIPS pcr3
226 where pcr3.contact_person_id = pcr.contact_person_id
227 and pcr3.person_id = pcr.person_id
228 and pcr3.personal_flag = 'Y'
229 and p_effective_date between
230 nvl(pcr3.date_start, p_effective_date )
231 and nvl(pcr3.date_end, p_effective_date )))
232 ))
233 group by plt.name,
234 pln.name,
235 opt.name,
236 ppf.first_name,
237 ppf.last_name,
241 org.name,
238 ppf.suffix,
239 ppf.full_name,
240 pbn.organization_id,
242 ppf.national_identifier,
243 pcr.contact_type,
244 ler.name,
245 ppf.date_of_birth,
246 pln.bnf_cntngt_bnfs_alwd_flag,
247 pen.ptip_ordr_num,
248 pen.plip_ordr_num,
249 pen.pl_ordr_num,
250 pen.oipl_ordr_num,
251 pen.bnft_ordr_num
252 )order by ptip_ordr_num,
253 plip_ordr_num,
254 pl_ordr_num,
255 oipl_ordr_num,
256 bnft_ordr_num,
257 Plan_Name,
258 decode(contact_type,'S',1,'D',1,'A',2,
259 'C',2,'O',2,'T',2,'P',3,4),
260 date_of_birth,
261 last_name,
262 first_name,
263 Beneficiary;
264
265 /*Cursor to fetch Person Enrollments Data*/
266 cursor c_get_pgm_enrt_details(p_person_id NUMBER,p_effective_date DATE,p_business_group_id NUMBER) is
267 SELECT plan_type_name,
268 plan_name,
269 option_name
270 FROM
271 (
272 SELECT elc.plt_name plan_type_name,
273 pln.name plan_name,
274 opt.name option_name,
275 elc.Program_Name,
276 elc.Coverage,
277 elc.le_name,
278 elc.per_in_ler_id,
279 elc.pgm_id,
280 elc.sspndd_flag,
281 elc.crntly_enrd_flag,
282 elc.elctbl_flag,
283 elc.enrt_cvg_strt_dt,
284 elc.enrt_cvg_thru_dt,
285 decode(elc.sspndd_flag,'Y','Suspended') suspended,
286 decode(elc.interim, 'Y', 'Interim') interim
287 FROM
288 (
289 SELECT epe.ptip_ordr_num ptip_ordr_num,
290 epe.plip_ordr_num plip_ordr_num,
291 pen.pl_ordr_num pl_ordr_num,
292 epe.oipl_ordr_num oipl_ordr_num,
293 pen.bnft_ordr_num bnft_ordr_num,
294 plt.name plt_name,
295 pen.person_id person_id,
296 pen.bnft_amt Coverage,
297 epe.per_in_ler_id per_in_ler_id,
298 pen.sspndd_flag,
299 epe.crntly_enrd_flag,
300 epe.elctbl_flag,
301 pen.enrt_cvg_strt_dt ,
302 decode(pen.enrt_cvg_thru_dt,to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
303 decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
304 ler.name le_name,
305 (select name from ben_pgm_f pgm
306 where p_effective_date between pgm.effective_start_date
307 and pgm.effective_end_date
308 and pgm.pgm_id=epe.pgm_id
309 and pgm.business_group_id = p_business_group_id) Program_Name,
310 epe.pgm_id pgm_id,
311 epe.ptip_id ptip_id,
312 epe.plip_id plip_id,
313 epe.oiplip_id oiplip_id,
314 epe.pl_id,
315 epe.oipl_id
316 FROM ben_elig_per_elctbl_chc epe,
317 ben_enrt_bnft beb,
318 ben_prtt_enrt_rslt_f pen,
319 ben_pl_typ_f plt,
320 ben_pil_elctbl_chc_popl pel,
321 ben_prtt_enrt_rslt_f pen1,
322 ben_ler_f ler
323 WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
324 and ( p_effective_date) between pen1.effective_start_date (+)
325 and pen1.effective_end_date (+)
326 and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
327 and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
328 -- Code Changes for bug 7689952 - Start
329 --and epe.per_in_ler_id = l_per_in_ler_id
330 and pen.per_in_ler_id = epe.per_in_ler_id
331 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
332 and pen.enrt_cvg_thru_dt >= p_effective_date
333 --Code Changes for bug 7689952 - End
334 and pen.person_id = p_person_id
335 and epe.business_group_id = p_business_group_id
336 and plt.business_group_id = p_business_group_id
337 and ler.business_group_id = p_business_group_id
338 AND ((epe.elctbl_flag = 'N' and
339 (nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
340 or epe.auto_enrt_flag = 'Y'))
341 or epe.elctbl_flag = 'Y')
342 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
343 AND epe.pl_typ_id = plt.pl_typ_id
344 AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
345 AND epe.business_group_id = beb.business_group_id(+)
346 AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
347 AND ( p_effective_date) BETWEEN pen.effective_start_date
348 AND pen.effective_end_date
349 AND ( p_effective_date) BETWEEN plt.effective_start_date
350 AND plt.effective_end_date
351 AND ( p_effective_date) BETWEEN ler.effective_start_date
352 AND ler.effective_end_date
353 AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
354 and pen.ler_id=ler.ler_id
355 AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
356 AND pen.prtt_enrt_rslt_stat_cd is null
357 UNION
358 SELECT epe.ptip_ordr_num ptip_ordr_num,
359 epe.plip_ordr_num plip_ordr_num,
360 pen.pl_ordr_num pl_ordr_num,
361 epe.oipl_ordr_num oipl_ordr_num,
362 pen.bnft_ordr_num bnft_ordr_num,
363 plt.name plt_name,
364 pen.person_id person_id,
365 pen.bnft_amt Coverage,
366 epe.per_in_ler_id per_in_ler_id,
367 pen.sspndd_flag,
368 epe.crntly_enrd_flag,
369 epe.elctbl_flag,
370 pen.enrt_cvg_strt_dt ,
371 decode(pen.enrt_cvg_thru_dt,
372 to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
373 decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
374 ler.name le_name,
378 and pgm.pgm_id=epe.pgm_id
375 (select name from ben_pgm_f pgm
376 where p_effective_date between pgm.effective_start_date
377 and pgm.effective_end_date
379 and pgm.business_group_id = p_business_group_id) Program_Name,
380 epe.pgm_id pgm_id,
381 epe.ptip_id ptip_id,
382 epe.plip_id plip_id,
383 epe.oiplip_id oiplip_id,
384 epe.pl_id,
385 epe.oipl_id
386 FROM ben_elig_per_elctbl_chc epe,
387 ben_enrt_bnft beb,
388 ben_prtt_enrt_rslt_f pen,
389 ben_pl_typ_f plt,
390 ben_pil_elctbl_chc_popl pel,
391 ben_prtt_enrt_rslt_f pen1,
392 ben_ler_f ler
393 WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
394 and (p_effective_date) between pen1.effective_start_date (+)
395 and pen1.effective_end_date (+)
396 and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
397 and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
398 -- Code Changes for bug 7689952 - Start
399 -- and epe.per_in_ler_id = l_per_in_ler_id
400 and pen.enrt_cvg_thru_dt >= pen.effective_start_date
401 and pen.per_in_ler_id=epe.per_in_ler_id
402 and pen.enrt_cvg_thru_dt >= p_effective_date
403 -- Code Changes for bug 7689952 - End
404 and pen.person_id = p_person_id
405 and epe.business_group_id = p_business_group_id
406 and plt.business_group_id = p_business_group_id
407 and ler.business_group_id = p_business_group_id
408 AND ((epe.elctbl_flag = 'N' and
409 (nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
410 or epe.auto_enrt_flag = 'Y'))
411 or epe.elctbl_flag = 'Y')
412 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
413 AND epe.pl_typ_id = plt.pl_typ_id
414 AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
415 AND epe.business_group_id = beb.business_group_id(+)
416 AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
417 AND (p_effective_date) BETWEEN pen.effective_start_date
418 AND pen.effective_end_date
419 AND (p_effective_date) BETWEEN plt.effective_start_date
420 AND plt.effective_end_date
421 AND ( p_effective_date) BETWEEN ler.effective_start_date
422 AND ler.effective_end_date
423 AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
424 and pen.ler_id=ler.ler_id
425 AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
426 AND pen.prtt_enrt_rslt_stat_cd is null
427 ) elc,
428 ben_pl_f pln,
429 ben_oipl_f oipl,
430 ben_opt_f opt,
431 ben_ptip_f ptip,
432 ben_plip_f plip
433 where elc.pl_id = pln.pl_id
434 AND elc.ptip_id = ptip.ptip_id
438 and pln.business_group_id = p_business_group_id
435 AND elc.plip_id = plip.plip_id
436 AND elc.oipl_id = oipl.oipl_id(+)
437 AND oipl.opt_id = opt.opt_id(+)
439 and oipl.business_group_id = p_business_group_id
440 and opt.business_group_id = p_business_group_id
441 and ptip.business_group_id = p_business_group_id
442 and plip.business_group_id = p_business_group_id
443 AND ( p_effective_date) BETWEEN pln.effective_start_date
444 AND pln.effective_end_date
445 AND ( p_effective_date) BETWEEN oipl.effective_start_date(+)
446 AND oipl.effective_end_date(+)
447 AND ( p_effective_date) BETWEEN opt.effective_start_date(+)
448 AND opt.effective_end_date(+)
449 AND ( p_effective_date) BETWEEN ptip.effective_start_date
450 AND ptip.effective_end_date
451 AND ( p_effective_date) BETWEEN plip.effective_start_date
452 AND plip.effective_end_date
453 ORDER BY elc.ptip_ordr_num,
454 elc.plip_ordr_num,
455 elc.pl_ordr_num,
456 elc.oipl_ordr_num,
457 elc.bnft_ordr_num
458 );
459 p_cnt number := 1;
460 begin
461 open c_get_dep_details(p_person_id,p_effective_date,p_business_group_id);
462 FETCH c_get_dep_details BULK COLLECT INTO p_ben_details.dependent;
463 close c_get_dep_details;
464
465 p_cnt := p_ben_details.dependent.count + 1;
466
467 for h in c_get_ben_details(p_person_id,p_effective_date,p_business_group_id)
468 loop
469 p_ben_details.dependent(p_cnt).name := h.beneficiary_full_name;
470 p_ben_details.dependent(p_cnt).relationship := h.Relation;
471 p_ben_details.dependent(p_cnt).type_of_benefit := h.plan_name;
472 if h.Primary_Bnf is not null then
476 elsif h.Primary_Bnf_amt is not null then
473 p_ben_details.dependent(p_cnt).coverage := to_char(h.Primary_Bnf);
474 elsif h.Contingent_Bnf is not null then
475 p_ben_details.dependent(p_cnt).coverage := to_char(h.Contingent_Bnf);
477 p_ben_details.dependent(p_cnt).coverage := to_char(h.Primary_Bnf_amt);
478 elsif h.Contingent_Bnf_amt is not null then
479 p_ben_details.dependent(p_cnt).coverage := to_char(h.Contingent_Bnf_amt);
480 end if;
481 p_cnt := p_cnt + 1;
482 end loop;
483
484 open c_get_pgm_enrt_details(p_person_id,p_effective_date,p_business_group_id);
485 FETCH c_get_pgm_enrt_details BULK COLLECT INTO p_ben_details.benefit;
486 close c_get_pgm_enrt_details;
487
488 end GET_BEN_DETAILS ;
489 --
490
491 END BEN_PERSON_RECORD;
492 --