[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_ENRT_SPCL
Source
1 package body BEN_EXT_ENRT_SPCL as
2 /* $Header: benxensp.pkb 120.2 2006/06/15 23:04:37 tjesumic ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_enrt_spcl.'; -- Global package name
9 --
10 --
11 -- procedure to initialize globals - May, 99
12 -- ----------------------------------------------------------------------------
13 -- |--------------------< initialize_globals >--------------------------------------|
14 -- ----------------------------------------------------------------------------
15 PROCEDURE initialize_globals IS
16 --
17 l_proc varchar2(72) := g_package||'initialize_globals';
18 --
19 Begin
20 --
21 hr_utility.set_location('Entering'||l_proc, 5);
22 --
23 ben_ext_person.g_enrt_prtt_enrt_rslt_id := null;
24 ben_ext_person.g_enrt_pl_name := null;
25 ben_ext_person.g_enrt_opt_name := null;
26 ben_ext_person.g_enrt_pl_id := null;
27 ben_ext_person.g_enrt_opt_id := null;
28 ben_ext_person.g_enrt_cvg_strt_dt := null;
29 ben_ext_person.g_enrt_cvg_thru_dt := null;
30 ben_ext_person.g_enrt_orgcovg_strdt := null;
31 ben_ext_person.g_enrt_prt_orgcovg_strdt := null;
32 ben_ext_person.g_enrt_cvg_amt := null;
33 ben_ext_person.g_enrt_pgm_id := null;
34 ben_ext_person.g_enrt_benefit_order_num := null;
35 ben_ext_person.g_enrt_method := null;
36 ben_ext_person.g_enrt_ovrd_flag := null;
37 ben_ext_person.g_enrt_ovrd_thru_dt := null;
38 ben_ext_person.g_enrt_ovrd_reason := null;
39 ben_ext_person.g_enrt_suspended_flag := null;
40 ben_ext_person.g_enrt_rslt_effct_strdt := null;
41 ben_ext_person.g_enrt_pgm_name := null;
42 ben_ext_person.g_enrt_pl_typ_id := null;
43 ben_ext_person.g_enrt_pl_typ_name := null;
44 ben_ext_person.g_enrt_attr_1 := null;
45 ben_ext_person.g_enrt_attr_2 := null;
46 ben_ext_person.g_enrt_attr_3 := null;
47 ben_ext_person.g_enrt_attr_4 := null;
48 ben_ext_person.g_enrt_attr_5 := null;
49 ben_ext_person.g_enrt_attr_6 := null;
50 ben_ext_person.g_enrt_attr_7 := null;
51 ben_ext_person.g_enrt_attr_8 := null;
52 ben_ext_person.g_enrt_attr_9 := null;
53 ben_ext_person.g_enrt_attr_10 := null;
54 ben_ext_person.g_pl_attr_1 := null;
55 ben_ext_person.g_pl_attr_2 := null;
56 ben_ext_person.g_pl_attr_3 := null;
57 ben_ext_person.g_pl_attr_4 := null;
58 ben_ext_person.g_pl_attr_5 := null;
59 ben_ext_person.g_pl_attr_6 := null;
60 ben_ext_person.g_pl_attr_7 := null;
61 ben_ext_person.g_pl_attr_8 := null;
62 ben_ext_person.g_pl_attr_9 := null;
63 ben_ext_person.g_pl_attr_10 := null;
64 ben_ext_person.g_pgm_attr_1 := null;
65 ben_ext_person.g_pgm_attr_2 := null;
66 ben_ext_person.g_pgm_attr_3 := null;
67 ben_ext_person.g_pgm_attr_4 := null;
68 ben_ext_person.g_pgm_attr_5 := null;
69 ben_ext_person.g_pgm_attr_6 := null;
70 ben_ext_person.g_pgm_attr_7 := null;
71 ben_ext_person.g_pgm_attr_8 := null;
72 ben_ext_person.g_pgm_attr_9 := null;
73 ben_ext_person.g_pgm_attr_10 := null;
74 ben_ext_person.g_ptp_attr_1 := null;
75 ben_ext_person.g_ptp_attr_2 := null;
76 ben_ext_person.g_ptp_attr_3 := null;
77 ben_ext_person.g_ptp_attr_4 := null;
78 ben_ext_person.g_ptp_attr_5 := null;
79 ben_ext_person.g_ptp_attr_6 := null;
80 ben_ext_person.g_ptp_attr_7 := null;
81 ben_ext_person.g_ptp_attr_8 := null;
82 ben_ext_person.g_ptp_attr_9 := null;
83 ben_ext_person.g_ptp_attr_10 := null;
84 ben_ext_person.g_plip_attr_1 := null;
85 ben_ext_person.g_plip_attr_2 := null;
86 ben_ext_person.g_plip_attr_3 := null;
87 ben_ext_person.g_plip_attr_4 := null;
88 ben_ext_person.g_plip_attr_5 := null;
89 ben_ext_person.g_plip_attr_6 := null;
90 ben_ext_person.g_plip_attr_7 := null;
91 ben_ext_person.g_plip_attr_8 := null;
92 ben_ext_person.g_plip_attr_9 := null;
93 ben_ext_person.g_plip_attr_10 := null;
94 ben_ext_person.g_oipl_attr_1 := null;
95 ben_ext_person.g_oipl_attr_2 := null;
96 ben_ext_person.g_oipl_attr_3 := null;
97 ben_ext_person.g_oipl_attr_4 := null;
98 ben_ext_person.g_oipl_attr_5 := null;
99 ben_ext_person.g_oipl_attr_6 := null;
100 ben_ext_person.g_oipl_attr_7 := null;
101 ben_ext_person.g_oipl_attr_8 := null;
102 ben_ext_person.g_oipl_attr_9 := null;
103 ben_ext_person.g_oipl_attr_10 := null;
104 ben_ext_person.g_enrt_plcy_r_grp := null;
105 ben_ext_person.g_ppr_name := null;
106 ben_ext_person.g_ppr_ident := null;
107 ben_ext_person.g_ppr_typ := null;
108 ben_ext_person.g_ppr_strt_dt := null;
109 ben_ext_person.g_ppr_end_dt := null;
110 ben_ext_person.g_enrt_lfevt_name := null;
111 ben_ext_person.g_enrt_lfevt_status := null;
112 ben_ext_person.g_enrt_lfevt_note_dt := null;
113 ben_ext_person.g_enrt_lfevt_ocrd_dt := null;
114 ben_ext_person.g_dpnt_cvrd_dpnt_id := null;
115 --
116 --
117 hr_utility.set_location('Exiting'||l_proc, 15);
118 --
119 End initialize_globals;
120 --
121 -- ----------------------------------------------------------------------------
122 -- |--------------------< main >----------------------------------------------|
123 -- ----------------------------------------------------------------------------
124 PROCEDURE main
125 ( p_dpnt_person_id in number,
126 p_prtt_person_id in number,
127 p_ext_rslt_id in number,
128 p_ext_file_id in number,
129 p_data_typ_cd in varchar2,
130 p_ext_typ_cd in varchar2,
131 p_chg_evt_cd in varchar2,
132 p_business_group_id in number,
133 p_effective_date in date) is
134 --
135 l_proc varchar2(72) := g_package||'main';
136 --
137 l_include varchar2(1) := 'Y';
138 --
139 cursor c_dpnt_enrt is
140 select
141 dpnt.elig_cvrd_dpnt_id elig_cvrd_dpnt_id,
142 enrt.prtt_enrt_rslt_id prtt_enrt_rslt_id,
143 enrt.pl_id pl_id,
144 enrt.oipl_id oipl_id,
145 enrt.orgnl_enrt_dt orgn_strdt,
146 dpnt.cvg_strt_dt cvg_strt_dt,
147 dpnt.cvg_thru_dt cvg_thru_dt,
148 enrt.bnft_amt bnft_amt,
149 enrt.pgm_id pgm_id,
150 enrt.bnft_ordr_num bnft_order_num,
151 enrt.enrt_mthd_cd mthd_cd,
152 enrt.enrt_ovridn_flag ovridn_flag,
153 enrt.enrt_ovrid_thru_dt ovrid_thru_dt,
154 enrt.enrt_ovrid_rsn_cd ovrid_rsn_cd,
155 enrt.sspndd_flag sspndd_flag,
156 enrt.effective_start_date effct_strdt,
157 enrt.last_update_date,
158 enrt.prtt_enrt_rslt_stat_cd,
159 enrt.per_in_ler_id,
160 enrt.pen_attribute1,
161 enrt.pen_attribute2,
162 enrt.pen_attribute3,
163 enrt.pen_attribute4,
164 enrt.pen_attribute5,
165 enrt.pen_attribute6,
166 enrt.pen_attribute7,
167 enrt.pen_attribute8,
168 enrt.pen_attribute9,
169 enrt.pen_attribute10,
170 pl.pl_typ_id pl_typ_id,
171 pl.name pl_name,
172 pl.pln_attribute1,
173 pl.pln_attribute2,
174 pl.pln_attribute3,
175 pl.pln_attribute4,
176 pl.pln_attribute5,
177 pl.pln_attribute6,
178 pl.pln_attribute7,
179 pl.pln_attribute8,
180 pl.pln_attribute9,
181 pl.pln_attribute10,
182 ptp.name pl_typ_name,
183 ptp.ptp_attribute1,
184 ptp.ptp_attribute2,
185 ptp.ptp_attribute3,
186 ptp.ptp_attribute4,
187 ptp.ptp_attribute5,
188 ptp.ptp_attribute6,
189 ptp.ptp_attribute7,
190 ptp.ptp_attribute8,
191 ptp.ptp_attribute9,
192 ptp.ptp_attribute10,
193 pil.per_in_ler_stat_cd,
194 pil.lf_evt_ocrd_dt,
195 pil.ntfn_dt,
196 pil.ler_id,
197 ler.name ler_name
198 from
199 ben_elig_cvrd_dpnt_f dpnt,
200 ben_prtt_enrt_rslt_f enrt,
201 ben_pl_f pl,
202 ben_pl_typ_f ptp,
203 ben_per_in_ler pil,
204 ben_ler_f ler
205 Where
206 dpnt.dpnt_person_id = p_dpnt_person_id
207 and enrt.person_id = p_prtt_person_id
208 and enrt.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
209 and p_effective_date between enrt.effective_start_date
210 and enrt.effective_end_date
211 and p_effective_date between dpnt.effective_start_date
212 and dpnt.effective_end_date
213 and enrt.pl_id = pl.pl_id
214 and pl.invk_flx_cr_pl_flag = 'N'
215 and pl.imptd_incm_calc_cd is null
216 and pl.invk_dcln_prtn_pl_flag = 'N'
217 and p_effective_date between pl.effective_start_date
218 and pl.effective_end_date
219 and pl.pl_typ_id = ptp.pl_typ_id
220 and p_effective_date between ptp.effective_start_date
221 and ptp.effective_end_date
222 and pil.per_in_ler_id=dpnt.per_in_ler_id
223 and pil.ler_id = ler.ler_id
224 and p_effective_date between ler.effective_start_date
225 and ler.effective_end_date
226 ;
227 --
228 cursor c_pgm_enrt (p_pgm_id number) is
229 select
230 pgm.name pgm_name,
231 pgm.pgm_attribute1,
232 pgm.pgm_attribute2,
233 pgm.pgm_attribute3,
234 pgm.pgm_attribute4,
235 pgm.pgm_attribute5,
236 pgm.pgm_attribute6,
237 pgm.pgm_attribute7,
238 pgm.pgm_attribute8,
239 pgm.pgm_attribute9,
240 pgm.pgm_attribute10
241 from ben_pgm_f pgm
242 where p_pgm_id = pgm.pgm_id
243 and p_effective_date between pgm.effective_start_date
244 and pgm.effective_end_date ;
245
246
247 cursor c_plip_enrt (p_pl_id number ,
248 p_pgm_id number ) is
249 select cpp.cpp_attribute1,
250 cpp.cpp_attribute2,
251 cpp.cpp_attribute3,
252 cpp.cpp_attribute4,
253 cpp.cpp_attribute5,
254 cpp.cpp_attribute6,
255 cpp.cpp_attribute7,
256 cpp.cpp_attribute8,
257 cpp.cpp_attribute9,
258 cpp.cpp_attribute10
259 from ben_plip_f cpp
260 where p_pl_id = cpp.pl_id
261 and p_pgm_id = cpp.pgm_id
262 and p_effective_date between cpp.effective_start_date
263 and cpp.effective_end_date
264 ;
265
266 --
267 cursor c_oipl_enrt (p_oipl_id number) is
268 select
269 opt.opt_id opt_id,
270 opt.name opt_name,
271 oipl.cop_attribute1,
272 oipl.cop_attribute2,
273 oipl.cop_attribute3,
274 oipl.cop_attribute4,
275 oipl.cop_attribute5,
276 oipl.cop_attribute6,
277 oipl.cop_attribute7,
278 oipl.cop_attribute8,
279 oipl.cop_attribute9,
280 oipl.cop_attribute10
281 from
282 ben_oipl_f oipl,
283 ben_opt_f opt
284 where p_oipl_id = oipl.oipl_id
285 and p_effective_date between oipl.effective_start_date
286 and oipl.effective_end_date
287 and opt.opt_id = oipl.opt_id
288 and p_effective_date between opt.effective_start_date
289 and opt.effective_end_date
290 ;
291
292 cursor plcy_c (l_pl_id Number , l_asg_id number) is
293 select ppl.plcy_r_grp
294 from ben_popl_org_f ppl,
295 per_all_assignments_f asg
296 where pl_id = l_pl_id
297 and plcy_r_grp is not null
298 and asg.assignment_id = l_asg_id
299 and ppl.organization_id = asg.organization_id
300 and p_effective_date between ppl.effective_start_date
301 and ppl.effective_end_date
302 and p_effective_date between asg.effective_start_date
303 and asg.effective_end_date ;
304
305 --
306 cursor c_dpnt_prmry_care_prvdr(p_elig_cvrd_dpnt_id number) is
307 SELECT name
308 ,ext_ident
309 ,prmry_care_prvdr_typ_cd
310 ,effective_start_date
311 ,effective_end_date
312 FROM ben_prmry_care_prvdr_f ppr
313 WHERE ppr.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
314 AND p_effective_date between ppr.effective_start_date
315 and ppr.effective_end_date;
316 --
317 cursor c_prtt_asg is
318 select assignment_id
319 from per_all_assignments_f asg
320 where person_id = p_prtt_person_id
321 and p_effective_date between asg.effective_start_date
322 and asg.effective_end_date
323 and asg.primary_flag = 'Y' ;
324
325 l_prtt_asg_id number ;
326 --
327 l_process varchar2(1) := 'Y';
328 --
329
330 BEGIN
331 --
332 hr_utility.set_location('Entering'||l_proc, 5);
333 --
334 FOR dpnt_enrt IN c_dpnt_enrt LOOP
335 -- initialize the globals - May, 99
336 initialize_globals;
337 --
338 ben_ext_evaluate_inclusion.Evaluate_Benefit_Incl
339 (p_pl_id => dpnt_enrt.pl_id,
340 p_sspndd_flag => dpnt_enrt.sspndd_flag,
341 p_enrt_cvg_strt_dt => dpnt_enrt.cvg_strt_dt,
342 p_enrt_cvg_thru_dt => dpnt_enrt.cvg_thru_dt,
343 p_prtt_enrt_rslt_stat_cd => dpnt_enrt.prtt_enrt_rslt_stat_cd,
344 p_enrt_mthd_cd => dpnt_enrt.mthd_cd,
345 p_pgm_id => dpnt_enrt.pgm_id,
346 p_pl_typ_id => dpnt_enrt.pl_typ_id,
347 p_last_update_date => dpnt_enrt.last_update_date,
348 p_ler_id => dpnt_enrt.ler_id,
349 p_ntfn_dt => dpnt_enrt.ntfn_dt,
350 p_lf_evt_ocrd_dt => dpnt_enrt.lf_evt_ocrd_dt,
351 p_per_in_ler_stat_cd => dpnt_enrt.per_in_ler_stat_cd,
352 p_per_in_ler_id => dpnt_enrt.per_in_ler_id,
353 p_prtt_enrt_rslt_id => dpnt_enrt.prtt_enrt_rslt_id,
354 p_effective_date => p_effective_date,
355 p_include => l_include
356 );
357
358 IF l_include = 'Y' THEN
359 --
360 -- assign enrollment info to global variables
361 --
362 ben_ext_person.g_enrt_prtt_enrt_rslt_id := dpnt_enrt.prtt_enrt_rslt_id ;
363 ben_ext_person.g_dpnt_cvrd_dpnt_id := dpnt_enrt.elig_cvrd_dpnt_id;
364 ben_ext_person.g_enrt_pl_name := dpnt_enrt.pl_name;
365 ben_ext_person.g_enrt_pl_id := dpnt_enrt.pl_id;
366 ben_ext_person.g_enrt_prt_orgcovg_strdt := dpnt_enrt.orgn_strdt ;
367 ben_ext_person.g_enrt_cvg_strt_dt := dpnt_enrt.cvg_strt_dt;
368 ben_ext_person.g_enrt_cvg_thru_dt := dpnt_enrt.cvg_thru_dt;
369 ben_ext_person.g_enrt_cvg_amt := dpnt_enrt.bnft_amt;
370 ben_ext_person.g_enrt_pgm_id := dpnt_enrt.pgm_id;
371 ben_ext_person.g_enrt_benefit_order_num := dpnt_enrt.bnft_order_num;
372 ben_ext_person.g_enrt_method := dpnt_enrt.mthd_cd;
373 ben_ext_person.g_enrt_ovrd_flag := dpnt_enrt.ovridn_flag;
374 ben_ext_person.g_enrt_ovrd_thru_dt := dpnt_enrt.ovrid_thru_dt;
375 ben_ext_person.g_enrt_ovrd_reason := dpnt_enrt.ovrid_rsn_cd;
376 ben_ext_person.g_enrt_suspended_flag := dpnt_enrt.sspndd_flag;
377 ben_ext_person.g_enrt_rslt_effct_strdt := dpnt_enrt.effct_strdt;
378 ben_ext_person.g_enrt_pl_typ_id := dpnt_enrt.pl_typ_id;
379 ben_ext_person.g_enrt_pl_typ_name := dpnt_enrt.pl_typ_name;
380 ben_ext_person.g_enrt_lfevt_name := dpnt_enrt.ler_name;
381 ben_ext_person.g_enrt_lfevt_status := dpnt_enrt.per_in_ler_stat_cd;
382 ben_ext_person.g_enrt_lfevt_note_dt := dpnt_enrt.ntfn_dt;
383 ben_ext_person.g_enrt_lfevt_ocrd_dt := dpnt_enrt.lf_evt_ocrd_dt;
384 ben_ext_person.g_enrt_attr_1 := dpnt_enrt.pen_attribute1;
385 ben_ext_person.g_enrt_attr_2 := dpnt_enrt.pen_attribute2;
386 ben_ext_person.g_enrt_attr_3 := dpnt_enrt.pen_attribute3;
387 ben_ext_person.g_enrt_attr_4 := dpnt_enrt.pen_attribute4;
388 ben_ext_person.g_enrt_attr_5 := dpnt_enrt.pen_attribute5;
389 ben_ext_person.g_enrt_attr_6 := dpnt_enrt.pen_attribute6;
390 ben_ext_person.g_enrt_attr_7 := dpnt_enrt.pen_attribute7;
391 ben_ext_person.g_enrt_attr_8 := dpnt_enrt.pen_attribute8;
392 ben_ext_person.g_enrt_attr_9 := dpnt_enrt.pen_attribute9;
393 ben_ext_person.g_enrt_attr_10 := dpnt_enrt.pen_attribute10;
394 ben_ext_person.g_pl_attr_1 := dpnt_enrt.pln_attribute1;
395 ben_ext_person.g_pl_attr_2 := dpnt_enrt.pln_attribute2;
396 ben_ext_person.g_pl_attr_3 := dpnt_enrt.pln_attribute3;
397 ben_ext_person.g_pl_attr_4 := dpnt_enrt.pln_attribute4;
398 ben_ext_person.g_pl_attr_5 := dpnt_enrt.pln_attribute5;
399 ben_ext_person.g_pl_attr_6 := dpnt_enrt.pln_attribute6;
400 ben_ext_person.g_pl_attr_7 := dpnt_enrt.pln_attribute7;
401 ben_ext_person.g_pl_attr_8 := dpnt_enrt.pln_attribute8;
402 ben_ext_person.g_pl_attr_9 := dpnt_enrt.pln_attribute9;
403 ben_ext_person.g_pl_attr_10 := dpnt_enrt.pln_attribute10;
404 ben_ext_person.g_ptp_attr_1 := dpnt_enrt.ptp_attribute1;
405 ben_ext_person.g_ptp_attr_2 := dpnt_enrt.ptp_attribute2;
406 ben_ext_person.g_ptp_attr_3 := dpnt_enrt.ptp_attribute3;
407 ben_ext_person.g_ptp_attr_4 := dpnt_enrt.ptp_attribute4;
408 ben_ext_person.g_ptp_attr_5 := dpnt_enrt.ptp_attribute5;
409 ben_ext_person.g_ptp_attr_6 := dpnt_enrt.ptp_attribute6;
410 ben_ext_person.g_ptp_attr_7 := dpnt_enrt.ptp_attribute7;
411 ben_ext_person.g_ptp_attr_8 := dpnt_enrt.ptp_attribute8;
412 ben_ext_person.g_ptp_attr_9 := dpnt_enrt.ptp_attribute9;
413 ben_ext_person.g_ptp_attr_10 := dpnt_enrt.ptp_attribute10;
414 --
415
416 --
417 if dpnt_enrt.pgm_id is not null then
418 open c_pgm_enrt (dpnt_enrt.pgm_id) ;
419 fetch c_pgm_enrt into
420 ben_ext_person.g_enrt_pgm_name
421 ,ben_ext_person.g_pgm_attr_1
422 ,ben_ext_person.g_pgm_attr_2
423 ,ben_ext_person.g_pgm_attr_3
424 ,ben_ext_person.g_pgm_attr_4
425 ,ben_ext_person.g_pgm_attr_5
426 ,ben_ext_person.g_pgm_attr_6
427 ,ben_ext_person.g_pgm_attr_7
428 ,ben_ext_person.g_pgm_attr_8
429 ,ben_ext_person.g_pgm_attr_9
430 ,ben_ext_person.g_pgm_attr_10
431 ;
432 close c_pgm_enrt ;
433 end if ;
434
435 if dpnt_enrt.pgm_id is not null and dpnt_enrt.pl_id is not null then
436 open c_plip_enrt (dpnt_enrt.pl_id ,
437 dpnt_enrt.pgm_id ) ;
438 fetch c_plip_enrt into
439 ben_ext_person.g_plip_attr_1
440 ,ben_ext_person.g_plip_attr_2
441 ,ben_ext_person.g_plip_attr_3
442 ,ben_ext_person.g_plip_attr_4
443 ,ben_ext_person.g_plip_attr_5
444 ,ben_ext_person.g_plip_attr_6
445 ,ben_ext_person.g_plip_attr_7
446 ,ben_ext_person.g_plip_attr_8
447 ,ben_ext_person.g_plip_attr_9
448 ,ben_ext_person.g_plip_attr_10
449 ;
450 close c_plip_enrt ;
451
452 end if ;
453
454 if dpnt_enrt.oipl_id is not null then
455
456 open c_oipl_enrt(dpnt_enrt.oipl_id) ;
457 fetch c_oipl_enrt into
458 ben_ext_person.g_enrt_opt_id
459 ,ben_ext_person.g_enrt_opt_name
460 ,ben_ext_person.g_oipl_attr_1
461 ,ben_ext_person.g_oipl_attr_2
462 ,ben_ext_person.g_oipl_attr_3
463 ,ben_ext_person.g_oipl_attr_4
464 ,ben_ext_person.g_oipl_attr_5
465 ,ben_ext_person.g_oipl_attr_6
466 ,ben_ext_person.g_oipl_attr_7
467 ,ben_ext_person.g_oipl_attr_8
468 ,ben_ext_person.g_oipl_attr_9
469 ,ben_ext_person.g_oipl_attr_10
470 ;
471 close c_oipl_enrt ;
472 end if ;
473 --
474
475
476 -- retrieve additional enrollment information
477 --
478 -- retrieve policy or group number if required
479 if ben_extract.g_pgn_csr = 'Y' then
480
481 open c_prtt_asg ;
482 fetch c_prtt_asg into l_prtt_asg_id ;
483 close c_prtt_asg ;
484
485 open plcy_c(dpnt_enrt.pl_id , nvl(ben_ext_person.g_assignment_id,l_prtt_asg_id));
486 fetch plcy_c into ben_ext_person.g_enrt_plcy_r_grp;
487 close plcy_c;
488 end if;
489 --
490 -- retrieve primary care provider info if required
491 --
492 if ben_extract.g_ppcp_csr = 'Y' then
493 open c_dpnt_prmry_care_prvdr(dpnt_enrt.elig_cvrd_dpnt_id);
494 fetch c_dpnt_prmry_care_prvdr into ben_ext_person.g_ppr_name
495 ,ben_ext_person.g_ppr_ident
496 ,ben_ext_person.g_ppr_typ
497 ,ben_ext_person.g_ppr_strt_dt
498 ,ben_ext_person.g_ppr_end_dt;
499 close c_dpnt_prmry_care_prvdr;
500 end if;
501 --
502 --
503 IF ben_extract.g_enrt_lvl = 'Y' THEN
504 --
505 -- format and write enrollment
506 -- ===========================
507 --
508 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
509 p_ext_file_id => p_ext_file_id,
510 p_data_typ_cd => p_data_typ_cd,
511 p_ext_typ_cd => p_ext_typ_cd,
512 p_rcd_typ_cd => 'D',
513 p_low_lvl_cd => 'E',
514 p_person_id => p_dpnt_person_id,
515 p_chg_evt_cd => p_chg_evt_cd,
516 p_business_group_id => p_business_group_id,
517 p_effective_date => p_effective_date
518 );
519 --
520 END IF;
521 --
522 END IF; -- l_include = 'Y'
523 --
524 END LOOP;
525 --
526 hr_utility.set_location('Exiting'||l_proc, 15);
527
528 END; -- main
529
530 --
531 END; -- package