[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_ENRT
Source
1 package body BEN_EXT_ENRT as
2 /* $Header: benxenrt.pkb 120.4 2008/02/22 05:15:00 vkodedal ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_enrt.'; -- Global package name
9 --
10 -- procedure to initialize enrt globals - May, 99
11 -- ----------------------------------------------------------------------------
12 -- |------< initialize_enrt_globals >------------------------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 Procedure initialize_enrt_globals IS
16 --
17 l_proc varchar2(72) := g_package||'initialize_enrt_globals';
18 --
19 Begin
20 --
21 hr_utility.set_location('Entering'||l_proc, 5);
22 --
23 /* Start of Changes for WWBUG: 1828349: addition */
24 ben_ext_person.g_enrt_prtt_enrt_rslt_id := null;
25 /* End of Changes for WWBUG: 1828349 */
26 ben_ext_person.g_enrt_pl_name := null;
27 ben_ext_person.g_enrt_opt_name := null;
28 ben_ext_person.g_enrt_orgcovg_strdt := null;
29 ben_ext_person.g_enrt_prt_orgcovg_strdt := null;
30 ben_ext_person.g_enrt_status_cd := null;
31 ben_ext_person.g_enrt_pl_id := null;
32 ben_ext_person.g_enrt_pl_seq_num := null;
33 ben_ext_person.g_enrt_pip_seq_num := null;
34 ben_ext_person.g_enrt_ptp_seq_num := null;
35 ben_ext_person.g_enrt_oip_seq_num := null;
36 ben_ext_person.g_enrt_opt_id := null;
37 ben_ext_person.g_enrt_cvg_strt_dt := null;
38 ben_ext_person.g_enrt_cvg_thru_dt := null;
39 ben_ext_person.g_enrt_method := null;
40 ben_ext_person.g_enrt_ovrd_flag := null;
41 ben_ext_person.g_enrt_ovrd_thru_dt := null;
42 ben_ext_person.g_enrt_ovrd_reason := null;
43 ben_ext_person.g_enrt_suspended_flag := null;
44 ben_ext_person.g_enrt_rslt_effct_strdt := null;
45 ben_ext_person.g_enrt_total_premium_amt := null;
46 ben_ext_person.g_enrt_total_premium_uom := null;
47 ben_ext_person.g_enrt_mntot_prem_amt := null;
48 ben_ext_person.g_enrt_mntot_prem_cramt := null;
49 ben_ext_person.g_enrt_cvg_amt := null;
50 ben_ext_person.g_enrt_benefit_order_num := null;
51 ben_ext_person.g_enrt_pgm_id := null;
52 ben_ext_person.g_enrt_pgm_name := null;
53 ben_ext_person.g_enrt_pl_typ_id := null;
54 ben_ext_person.g_enrt_pl_typ_name := null;
55 ben_ext_person.g_enrt_rpt_group_name := null;
56 ben_ext_person.g_enrt_rpt_group_id := null;
57 ben_ext_person.g_enrt_pl_yr_strdt := null;
58 ben_ext_person.g_enrt_pl_yr_enddt := null;
59 ben_ext_person.g_enrt_elec_made_dt := null;
60 ben_ext_person.g_enrt_intrcovg_flag := null;
61 ben_ext_person.g_enrt_attr_1 := null;
62 ben_ext_person.g_enrt_attr_2 := null;
63 ben_ext_person.g_enrt_attr_3 := null;
64 ben_ext_person.g_enrt_attr_4 := null;
65 ben_ext_person.g_enrt_attr_5 := null;
66 ben_ext_person.g_enrt_attr_6 := null;
67 ben_ext_person.g_enrt_attr_7 := null;
68 ben_ext_person.g_enrt_attr_8 := null;
69 ben_ext_person.g_enrt_attr_9 := null;
70 ben_ext_person.g_enrt_attr_10 := null;
71 ben_ext_person.g_enrt_ler_id := null;
72 ben_ext_person.g_enrt_assignment_id := null;
73 ben_ext_person.g_enrt_uom := null;
74 ben_ext_person.g_pl_attr_1 := null;
75 ben_ext_person.g_pl_attr_2 := null;
76 ben_ext_person.g_pl_attr_3 := null;
77 ben_ext_person.g_pl_attr_4 := null;
78 ben_ext_person.g_pl_attr_5 := null;
79 ben_ext_person.g_pl_attr_6 := null;
80 ben_ext_person.g_pl_attr_7 := null;
81 ben_ext_person.g_pl_attr_8 := null;
82 ben_ext_person.g_pl_attr_9 := null;
83 ben_ext_person.g_pl_attr_10 := null;
84 ben_ext_person.g_pgm_attr_1 := null;
85 ben_ext_person.g_pgm_attr_2 := null;
86 ben_ext_person.g_pgm_attr_3 := null;
87 ben_ext_person.g_pgm_attr_4 := null;
88 ben_ext_person.g_pgm_attr_5 := null;
89 ben_ext_person.g_pgm_attr_6 := null;
90 ben_ext_person.g_pgm_attr_7 := null;
91 ben_ext_person.g_pgm_attr_8 := null;
92 ben_ext_person.g_pgm_attr_9 := null;
93 ben_ext_person.g_pgm_attr_10 := null;
94 ben_ext_person.g_ptp_attr_1 := null;
95 ben_ext_person.g_ptp_attr_2 := null;
96 ben_ext_person.g_ptp_attr_3 := null;
97 ben_ext_person.g_ptp_attr_4 := null;
98 ben_ext_person.g_ptp_attr_5 := null;
99 ben_ext_person.g_ptp_attr_6 := null;
100 ben_ext_person.g_ptp_attr_7 := null;
101 ben_ext_person.g_ptp_attr_8 := null;
102 ben_ext_person.g_ptp_attr_9 := null;
103 ben_ext_person.g_ptp_attr_10 := null;
104 ben_ext_person.g_plip_attr_1 := null;
105 ben_ext_person.g_plip_attr_2 := null;
106 ben_ext_person.g_plip_attr_3 := null;
107 ben_ext_person.g_plip_attr_4 := null;
108 ben_ext_person.g_plip_attr_5 := null;
109 ben_ext_person.g_plip_attr_6 := null;
110 ben_ext_person.g_plip_attr_7 := null;
111 ben_ext_person.g_plip_attr_8 := null;
112 ben_ext_person.g_plip_attr_9 := null;
113 ben_ext_person.g_plip_attr_10 := null;
114 ben_ext_person.g_oipl_attr_1 := null;
115 ben_ext_person.g_oipl_attr_2 := null;
116 ben_ext_person.g_oipl_attr_3 := null;
117 ben_ext_person.g_oipl_attr_4 := null;
118 ben_ext_person.g_oipl_attr_5 := null;
119 ben_ext_person.g_oipl_attr_6 := null;
120 ben_ext_person.g_oipl_attr_7 := null;
121 ben_ext_person.g_oipl_attr_8 := null;
122 ben_ext_person.g_oipl_attr_9 := null;
123 ben_ext_person.g_oipl_attr_10 := null;
124 ben_ext_person.g_opt_attr_1 := null;
125 ben_ext_person.g_opt_attr_2 := null;
126 ben_ext_person.g_opt_attr_3 := null;
127 ben_ext_person.g_opt_attr_4 := null;
128 ben_ext_person.g_opt_attr_5 := null;
129 ben_ext_person.g_opt_attr_6 := null;
130 ben_ext_person.g_opt_attr_7 := null;
131 ben_ext_person.g_opt_attr_8 := null;
132 ben_ext_person.g_opt_attr_9 := null;
133 ben_ext_person.g_opt_attr_10 := null;
134 ben_ext_person.g_enrt_plcy_r_grp := null;
135 ben_ext_person.g_ppr_name := null;
136 ben_ext_person.g_ppr_ident := null;
137 ben_ext_person.g_ppr_typ := null;
138 ben_ext_person.g_ppr_strt_dt := null;
139 ben_ext_person.g_ppr_end_dt := null;
140 ben_ext_person.g_enrt_lfevt_name := null;
141 ben_ext_person.g_enrt_lfevt_status := null;
142 ben_ext_person.g_enrt_lfevt_note_dt := null;
143 ben_ext_person.g_enrt_lfevt_ocrd_dt := null;
144 --
145 ben_ext_person.g_enrt_pl_fd_name := null;
146 ben_ext_person.g_enrt_pl_fd_code := null;
147 ben_ext_person.g_enrt_pgm_fd_name := null;
148 ben_ext_person.g_enrt_pgm_fd_code := null;
149 ben_ext_person.g_enrt_pl_typ_fd_name := null;
150 ben_ext_person.g_enrt_pl_typ_fd_code := null;
151 ben_ext_person.g_enrt_opt_fd_name := null;
152 ben_ext_person.g_enrt_opt_fd_code := null;
153 ben_ext_person.g_enrt_opt_pl_fd_name := null;
154 ben_ext_person.g_enrt_opt_pl_fd_code := null;
155 ben_ext_person.g_enrt_pl_pgm_fd_name := null;
156 ben_ext_person.g_enrt_pl_pgm_fd_code := null;
157 ben_ext_person.g_enrt_pl_typ_pgm_fd_name := null;
158 ben_ext_person.g_enrt_pl_typ_pgm_fd_code := null;
159
160
161
162 hr_utility.set_location('Exiting'||l_proc, 15);
163 --
164 End initialize_enrt_globals;
165 --
166 -- procedure to initialize rt info globals - May, 99
167 -- ----------------------------------------------------------------------------
168 -- |------< initialize_rt_info_globals >--------------------------------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 Procedure initialize_rt_info_globals IS
172 --
173 l_proc varchar2(72) := g_package||'initialize_rt_info_globals';
174 --
175 Begin
176 --
177 hr_utility.set_location('Entering'||l_proc, 5);
178 --
179 ben_ext_person.g_ee_pre_tax_cost := null;
180 ben_ext_person.g_ee_after_tax_cost := null;
181 ben_ext_person.g_ee_ttl_cost := null;
182 ben_ext_person.g_er_ttl_cost := null;
183 ben_ext_person.g_ee_ttl_distribution := null;
184 ben_ext_person.g_er_ttl_distribution := null;
185 ben_ext_person.g_ttl_other_rate := null;
186 ben_ext_person.g_pev_ee_pre_tax_contr := null;
187 ben_ext_person.g_pev_ee_after_tax_contr := null;
188 ben_ext_person.g_pev_ee_ttl_contr := null;
189 ben_ext_person.g_pev_er_ttl_contr := null;
190 ben_ext_person.g_pev_ee_ttl_distribution := null;
191 ben_ext_person.g_pev_er_ttl_distribution := null;
192 ben_ext_person.g_pev_ttl_other_rate := null;
193 ---cwb 2832419
194 ben_ext_person.g_er_cwb_dst_bdgt := null ;
195 ben_ext_person.g_er_cwb_misc_rate_1 := null ;
196 ben_ext_person.g_er_cwb_elig_salary := null ;
197 ben_ext_person.g_er_cwb_misc_rate_2 := null ;
198 ben_ext_person.g_er_cwb_grant_price := null ;
199 ben_ext_person.g_er_cwb_other_salary := null ;
200 ben_ext_person.g_er_cwb_reserve := null ;
201 ben_ext_person.g_er_cwb_recomond_amt := null ;
202 ben_ext_person.g_er_cwb_stated_salary := null ;
203 ben_ext_person.g_er_cwb_tot_compensation := null ;
204 ben_ext_person.g_er_cwb_worksheet_bdgt := null ;
205 ben_ext_person.g_er_cwb_elig_salary := null ;
206 ben_ext_person.g_er_cwb_misc_rate_3 := null ;
207 ben_ext_person.g_er_reimbursement := null ;
208 ben_ext_person.g_pev_er_reimbursement := null ;
209 ben_ext_person.g_er_forfeited := null ;
210 ben_ext_person.g_pev_er_forfeited := null ;
211
212
213 --
214 hr_utility.set_location('Exiting'||l_proc, 15);
215 --
216 End initialize_rt_info_globals;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |------< get_rt_info >-----------------------------------------------------|
220 -- ----------------------------------------------------------------------------
221 --
222 Procedure get_rt_info(
223 p_prtt_enrt_rslt_id in number,
224 p_effective_date in date
225 ) IS
226 --
227 cursor ee_pre_tax_c is
228 select sum(b.rt_val), sum(p.screen_entry_value)
229 from ben_prtt_rt_val b, pay_element_entry_values_f p
230 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
231 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
232 and nvl(b.rt_end_dt, p_effective_date)
233 and b.tx_typ_cd = 'PRETAX'
234 and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
235 and b.element_entry_value_id = p.element_entry_value_id (+)
236 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
237 and nvl(p.effective_end_date, p_effective_date);
238 --
239 cursor ee_after_tax_c is
240 select sum(b.rt_val), sum(p.screen_entry_value)
241 from ben_prtt_rt_val b, pay_element_entry_values_f p
242 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
243 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
244 and nvl(b.rt_end_dt, p_effective_date)
245 and b.tx_typ_cd = 'AFTERTAX'
246 and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
247 and b.element_entry_value_id = p.element_entry_value_id (+)
248 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
249 and nvl(p.effective_end_date, p_effective_date);
250 --
251 cursor ee_ttl_c is
252 select sum(b.rt_val), sum(p.screen_entry_value)
253 from ben_prtt_rt_val b, pay_element_entry_values_f p
254 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
255 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
256 and nvl(b.rt_end_dt, p_effective_date)
257 and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
258 and b.element_entry_value_id = p.element_entry_value_id (+)
259 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
260 and nvl(p.effective_end_date, p_effective_date);
261 --
262 cursor er_ttl_c is
263 select sum(b.rt_val), sum(p.screen_entry_value)
264 from ben_prtt_rt_val b, pay_element_entry_values_f p
265 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
266 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
267 and nvl(b.rt_end_dt, p_effective_date)
268 and b.acty_typ_cd IN ('ERPYC', 'ERMPLC', 'ERC')
269 and b.element_entry_value_id = p.element_entry_value_id (+)
270 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
271 and nvl(p.effective_end_date, p_effective_date);
272 --
273 cursor ee_ttl_dist_c is
274 select sum(b.rt_val), sum(p.screen_entry_value)
275 from ben_prtt_rt_val b, pay_element_entry_values_f p
276 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
277 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
278 and nvl(b.rt_end_dt, p_effective_date)
279 and b.acty_typ_cd IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD', 'PXD1')
280 and b.element_entry_value_id = p.element_entry_value_id (+)
281 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
282 and nvl(p.effective_end_date, p_effective_date);
283 --
284 cursor er_ttl_dist_c is
285 select sum(b.rt_val), sum(p.screen_entry_value)
286 from ben_prtt_rt_val b, pay_element_entry_values_f p
287 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
288 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
289 and nvl(b.rt_end_dt, p_effective_date)
290 and b.acty_typ_cd IN ('ERPYD', 'ERD')
291 and b.element_entry_value_id = p.element_entry_value_id (+)
292 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
293 and nvl(p.effective_end_date, p_effective_date);
294 --
295 cursor ttl_oth_rt_c is
296 select sum(b.rt_val), sum(p.screen_entry_value)
297 from ben_prtt_rt_val b, pay_element_entry_values_f p
298 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
299 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
300 and nvl(b.rt_end_dt, p_effective_date)
301 and b.acty_typ_cd NOT IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD', 'PXD1', 'ERPYD',
302 'ERD', 'EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2',
303 'PXC', 'ERPYC', 'ERMPLC', 'ERC')
304 and b.element_entry_value_id = p.element_entry_value_id (+)
305 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
306 and nvl(p.effective_end_date, p_effective_date);
307 -- cwb 2832419
308 cursor ttl_type_cd_c (c_acty_typ_cd varchar2) is
309 select sum(b.rt_val), sum(p.screen_entry_value)
310 from ben_prtt_rt_val b, pay_element_entry_values_f p
311 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
312 /* if the type is non recirring rate is creatted for one day so it may not return any row
313 and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
314 and nvl(b.rt_end_dt, p_effective_date) */
315 and b.acty_typ_cd = c_acty_typ_cd
316 and b.element_entry_value_id = p.element_entry_value_id (+)
317 and p_effective_date between p.effective_start_date (+)
318 and p.effective_end_date (+);
319
320 cursor ttl_reimb_c is
321 select sum(b.rt_val), sum(p.screen_entry_value)
322 from ben_prtt_rt_val b, pay_element_entry_values_f p
323 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
324 and b.acty_typ_cd IN ('PRDPER', 'PRDPPR', 'PRDPR')
325 and b.element_entry_value_id = p.element_entry_value_id (+)
326 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
327 and nvl(p.effective_end_date, p_effective_date);
328
329 l_dummy ben_prtt_rt_val.rt_val%TYPE;
330
331 l_proc varchar2(72) := g_package||'get_rt_info';
332 --
333 Begin
334 --
335 hr_utility.set_location('Entering'||l_proc, 5);
336 --
337 --
338 -- Initialize rt info globals - May, 99
339 initialize_rt_info_globals;
340 --
341 open ee_pre_tax_c;
342 fetch ee_pre_tax_c into ben_ext_person.g_ee_pre_tax_cost, ben_ext_person.g_pev_ee_pre_tax_contr;
343 close ee_pre_tax_c;
344 --
345 open ee_after_tax_c;
346 fetch ee_after_tax_c into ben_ext_person.g_ee_after_tax_cost, ben_ext_person.g_pev_ee_after_tax_contr;
347 close ee_after_tax_c;
348 --
349 open ee_ttl_c;
350 fetch ee_ttl_c into ben_ext_person.g_ee_ttl_cost, ben_ext_person.g_pev_ee_ttl_contr;
351 close ee_ttl_c;
352 --
353 open er_ttl_c;
354 fetch er_ttl_c into ben_ext_person.g_er_ttl_cost, ben_ext_person.g_pev_er_ttl_contr;
355 close er_ttl_c;
356 --
357 open ee_ttl_dist_c;
358 fetch ee_ttl_dist_c into ben_ext_person.g_ee_ttl_distribution, ben_ext_person.g_pev_ee_ttl_distribution;
359 close ee_ttl_dist_c;
360 --
361 open er_ttl_dist_c;
362 fetch er_ttl_dist_c into ben_ext_person.g_er_ttl_distribution, ben_ext_person.g_pev_er_ttl_distribution;
363 close er_ttl_dist_c;
364 --
365 open ttl_oth_rt_c;
366 fetch ttl_oth_rt_c into ben_ext_person.g_ttl_other_rate, ben_ext_person.g_pev_ttl_other_rate;
367 close ttl_oth_rt_c;
368
369 -- cwb 2832419
370 -- reimbursement
371 open ttl_reimb_c;
372 fetch ttl_reimb_c into ben_ext_person.g_er_reimbursement , ben_ext_person.g_pev_er_reimbursement;
373 close ttl_reimb_c;
374
375 hr_utility.set_location( 'reimb ' || ben_ext_person.g_er_reimbursement , 911 );
376 hr_utility.set_location( 'reimb ' || ben_ext_person.g_pev_er_reimbursement,911 );
377 --- Forfeiture
378 open ttl_type_cd_c('PRTRFS');
379 fetch ttl_type_cd_c into ben_ext_person.g_er_forfeited , ben_ext_person.g_pev_er_forfeited;
380 close ttl_type_cd_c ;
381
382 -- CWB
383 open ttl_type_cd_c('CWBDB');
384 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_dst_bdgt,l_dummy ;
385 close ttl_type_cd_c ;
386
387 open ttl_type_cd_c('CWBES');
388 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_elig_salary,l_dummy ;
389 close ttl_type_cd_c ;
390
391 open ttl_type_cd_c('CWBGP');
392 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_grant_price,l_dummy ;
393 close ttl_type_cd_c ;
394
395 hr_utility.set_location( 'CWBGP ' || ben_ext_person.g_er_cwb_grant_price , 911 );
396
397 open ttl_type_cd_c('CWBMR1');
398 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_misc_rate_1,l_dummy ;
399 close ttl_type_cd_c ;
400
401 open ttl_type_cd_c('CWBMR2');
402 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_misc_rate_2,l_dummy ;
403 close ttl_type_cd_c ;
404
405 open ttl_type_cd_c('CWBMR3');
406 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_misc_rate_3,l_dummy ;
407 close ttl_type_cd_c ;
408
409 open ttl_type_cd_c('CWBOS');
410 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_other_salary,l_dummy ;
411 close ttl_type_cd_c ;
412
413 open ttl_type_cd_c('CWBR');
414 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_reserve ,l_dummy ;
415 close ttl_type_cd_c ;
416
417 open ttl_type_cd_c('CWBRA');
418 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_recomond_amt,l_dummy ;
419 close ttl_type_cd_c ;
420
421 open ttl_type_cd_c('CWBSS');
422 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_stated_salary,l_dummy ;
423 close ttl_type_cd_c ;
424
425 open ttl_type_cd_c('CWBTC');
426 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_tot_compensation,l_dummy ;
427 close ttl_type_cd_c ;
428
429 open ttl_type_cd_c('CWBWB');
430 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_worksheet_bdgt,l_dummy ;
431 close ttl_type_cd_c ;
432
433 open ttl_type_cd_c('CWBWS');
434 fetch ttl_type_cd_c into ben_ext_person.g_er_cwb_worksheet_amt,l_dummy ;
435 close ttl_type_cd_c ;
436
437 hr_utility.set_location( 'CWBWS ' || ben_ext_person.g_er_cwb_worksheet_amt , 911 );
438
439 --
440 hr_utility.set_location('Exiting'||l_proc, 15);
441 --
442 End get_rt_info;
443 -- ----------------------------------------------------------------------------
444 -- |--------------------< main >----------------------------------------------|
445 -- ----------------------------------------------------------------------------
446 PROCEDURE main
447 ( p_person_id in number,
448 p_ext_rslt_id in number,
449 p_ext_file_id in number,
450 p_data_typ_cd in varchar2,
451 p_ext_typ_cd in varchar2,
452 p_chg_evt_cd in varchar2,
453 p_business_group_id in number,
454 p_effective_date in date) is
455 --
456 l_proc varchar2(72) := g_package||'main';
457 --
458 l_include varchar2(1) := 'Y';
459
460
461 --
462
463 cursor c_enrt_rslt is
464 select
465 enrt.prtt_enrt_rslt_id prtt_enrt_rslt_id,
466 enrt.pl_id pl_id,
467 enrt.oipl_id oipl_id,
468 enrt.orgnl_enrt_dt orgn_strdt,
469 enrt.prtt_enrt_rslt_stat_cd status_cd,
470 enrt.enrt_cvg_strt_dt cvg_strt_dt,
471 enrt.enrt_cvg_thru_dt cvg_thru_dt,
472 enrt.enrt_mthd_cd mthd_cd,
473 enrt.enrt_ovridn_flag ovridn_flag,
474 enrt.enrt_ovrid_thru_dt ovrid_thru_dt,
475 enrt.enrt_ovrid_rsn_cd ovrid_rsn_cd,
476 enrt.sspndd_flag sspndd_flag,
477 enrt.effective_start_date effct_strdt,
478 enrt.bnft_amt bnft_amt,
479 enrt.bnft_ordr_num bnft_order_num,
480 enrt.pgm_id pgm_id,
481 enrt.last_update_date,
482 enrt.prtt_enrt_rslt_stat_cd,
483 enrt.per_in_ler_id,
484 enrt.pl_ordr_num pl_seq_num,
485 enrt.plip_ordr_num plip_seq_num,
486 enrt.ptip_ordr_num ptip_seq_num,
487 enrt.oipl_ordr_num oipl_seq_num,
488 enrt.ler_id enrt_ler_id,
489 enrt.assignment_id assignment_id,
490 enrt.rplcs_sspndd_rslt_id ,
491 enrt.uom uom,
492 enrt.pen_attribute1,
493 enrt.pen_attribute2,
494 enrt.pen_attribute3,
495 enrt.pen_attribute4,
496 enrt.pen_attribute5,
497 enrt.pen_attribute6,
498 enrt.pen_attribute7,
499 enrt.pen_attribute8,
500 enrt.pen_attribute9,
501 enrt.pen_attribute10,
502 pl.name pl_name,
503 pl.short_name pl_fd_name,
504 pl.short_code pl_fd_code,
505 pl.pl_typ_id pl_typ_id,
506 pl.pln_attribute1,
507 pl.pln_attribute2,
508 pl.pln_attribute3,
509 pl.pln_attribute4,
510 pl.pln_attribute5,
511 pl.pln_attribute6,
512 pl.pln_attribute7,
513 pl.pln_attribute8,
514 pl.pln_attribute9,
515 pl.pln_attribute10,
516 ptp.name pl_typ_name,
517 ptp.short_name ptp_fd_name,
518 ptp.short_code ptp_fd_code,
519 ptp.ptp_attribute1,
520 ptp.ptp_attribute2,
521 ptp.ptp_attribute3,
522 ptp.ptp_attribute4,
523 ptp.ptp_attribute5,
524 ptp.ptp_attribute6,
525 ptp.ptp_attribute7,
526 ptp.ptp_attribute8,
527 ptp.ptp_attribute9,
528 ptp.ptp_attribute10
529 from ben_prtt_enrt_rslt_f enrt,
530 ben_pl_f pl,
531 ben_pl_typ_f ptp
532 where enrt.person_id = p_person_id
533 and p_effective_date between enrt.effective_start_date
534 and enrt.effective_end_date
535 and enrt.pl_id = pl.pl_id
536 and p_effective_date between pl.effective_start_date and pl.effective_end_date
537 and pl.pl_typ_id = ptp.pl_typ_id
538 and p_effective_date between ptp.effective_start_date
539 and ptp.effective_end_date
540 ;
541
542
543
544
545 cursor c_pil_rslt (p_pil_id number) is
546 select pil.lf_evt_ocrd_dt,
547 pil.ntfn_dt,
548 pil.ler_id,
549 pil.per_in_ler_stat_cd,
550 ler.name ler_name
551 from ben_per_in_ler pil,
552 ben_ler_f ler
553 where pil.per_in_ler_id = p_pil_id
554 and ler.ler_id = pil.ler_id
555 and p_effective_date between ler.effective_start_date and ler.effective_end_date
556 ;
557
558 l_pil_rslt c_pil_rslt%rowtype ;
559
560 cursor c_pgm_rslt (p_pgm_id number) is
561 select pgm.name pgm_name,
562 pgm.short_name pgm_fd_name,
563 pgm.short_code pgm_fd_code,
564 pgm.pgm_attribute1,
565 pgm.pgm_attribute2,
566 pgm.pgm_attribute3,
567 pgm.pgm_attribute4,
568 pgm.pgm_attribute5,
569 pgm.pgm_attribute6,
570 pgm.pgm_attribute7,
571 pgm.pgm_attribute8,
572 pgm.pgm_attribute9,
573 pgm.pgm_attribute10
574 from ben_pgm_f pgm
575 where p_pgm_id = pgm.pgm_id
576 and p_effective_date between pgm.effective_start_date
577 and pgm.effective_end_date
578 ;
579
580
581 cursor c_oipl_rslt (p_oipl_id number) is
582 select oipl.short_name oipl_fd_name,
583 oipl.short_code oipl_fd_code,
584 oipl.cop_attribute1,
585 oipl.cop_attribute2,
586 oipl.cop_attribute3,
587 oipl.cop_attribute4,
588 oipl.cop_attribute5,
589 oipl.cop_attribute6,
590 oipl.cop_attribute7,
591 oipl.cop_attribute8,
592 oipl.cop_attribute9,
593 oipl.cop_attribute10,
594 opt.opt_id opt_id,
595 opt.name opt_name,
596 opt.short_name opt_fd_name,
597 opt.short_code opt_fd_code,
598 opt.opt_attribute1,
599 opt.opt_attribute2,
600 opt.opt_attribute3,
601 opt.opt_attribute4,
602 opt.opt_attribute5,
603 opt.opt_attribute6,
604 opt.opt_attribute7,
605 opt.opt_attribute8,
606 opt.opt_attribute9,
607 opt.opt_attribute10
608 from ben_oipl_f oipl,
609 ben_opt_f opt
610 where p_oipl_id = oipl.oipl_id
611 and p_effective_date between oipl.effective_start_date
612 and oipl.effective_end_date
613 and opt.opt_id = oipl.opt_id
614 and p_effective_date between opt.effective_start_date
615 and opt.effective_end_date
616 ;
617
618
619 l_oipl_rslt c_oipl_rslt%rowtype ;
620
621 cursor c_plip_rslt (p_pgm_id number ,
622 p_pl_id number ) is
623 select cpp.cpp_attribute1,
624 cpp.cpp_attribute2,
625 cpp.cpp_attribute3,
626 cpp.cpp_attribute4,
627 cpp.cpp_attribute5,
628 cpp.cpp_attribute6,
629 cpp.cpp_attribute7,
630 cpp.cpp_attribute8,
631 cpp.cpp_attribute9,
632 cpp.cpp_attribute10,
633 cpp.short_name plip_fd_name,
634 cpp.short_code plip_fd_code
635 from ben_plip_f cpp
636 where p_pl_id = cpp.pl_id
637 and p_pgm_id = cpp.pgm_id
638 and p_effective_date between cpp.effective_start_date
639 and cpp.effective_end_date
640 ;
641
642 /*
643 cursor c_popl_rslt (p_pgm_id number ,
644 p_pl_id number ,
645 p_oipl_id number ,
646 p_pil_id number ) is
647 select ppopl.elcns_made_dt elec_made_dt
648 from ben_elig_per_elctbl_chc ece,
649 ben_pil_elctbl_chc_popl ppopl
650 where (p_pgm_id is null or p_pgm_id = ece.pgm_id)
651 and p_pl_id = ece.pl_id
652 and (p_oipl_id is null or p_oipl_id = ece.oipl_id)
653 and (p_pil_id is null or p_pil_id = ece.per_in_ler_id )
654 and ece.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
655 ;
656
657 */
658
659
660
661
662 cursor c_popl_rslt (p_pgm_id number ,
663 p_pl_id number ,
664 p_pil_id number ) is
665 select popl.elcns_made_dt elec_made_dt
666 from ben_pil_elctbl_chc_popl popl
667 where ( (p_pgm_id is not null and p_pgm_id = popl.pgm_id)
668 or ( p_pgm_id is null and p_pl_id = popl.pl_id )
669 )
670 and p_pil_id = popl.per_in_ler_id
671 ;
672
673 cursor c_ptip_fd ( l_pgm_id number ,
674 l_pl_typ_id number ) is
675 select short_name , short_code
676 from ben_ptip_f ptip
677 where pl_typ_id = l_pl_typ_id
678 and pgm_id = l_pgm_id
679 and p_effective_date between nvl(ptip.effective_start_date, p_effective_date)
680 and nvl(ptip.effective_end_date, p_effective_date)
681 ;
682
683
684 --
685 cursor plcy_c (l_pl_id Number) is
686 select ppl.plcy_r_grp
687 from ben_popl_org_f ppl ,
688 per_all_assignments_f asg
689 where pl_id = l_pl_id
690 and plcy_r_grp is not null
691 and asg.assignment_id = ben_ext_person.g_assignment_id
692 and ppl.organization_id = asg.organization_id
693 and p_effective_date between ppl.effective_start_date
694 and ppl.effective_end_date
695 and p_effective_date between asg.effective_start_date
696 and asg.effective_end_date
697 ;
698
699 --
700 cursor c_prmry_care_prvdr(l_prtt_enrt_rslt_id number) is
701 SELECT name
702 ,ext_ident
703 ,prmry_care_prvdr_typ_cd
704 ,effective_start_date
705 ,effective_end_date
706 FROM ben_prmry_care_prvdr_f ppr
707 WHERE ppr.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
708 AND p_effective_date between ppr.effective_start_date
709 and ppr.effective_end_date;
710 --
711 cursor prem_tot_c(l_prtt_enrt_rslt_id number) is
712 select
713 sum(ppr.std_prem_val)
714 , ppr.std_prem_uom
715 from ben_prtt_prem_f ppr,
716 ben_per_in_ler pil
717 where ppr.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
718 and p_effective_date between nvl(ppr.effective_start_date, p_effective_date)
719 and nvl(ppr.effective_end_date, p_effective_date)
720 and pil.per_in_ler_id=ppr.per_in_ler_id
721 and pil.business_group_id+0=ppr.business_group_id+0
722 group by ppr.std_prem_uom
723 ;
724
725 --
726 cursor c_rpt_grp(l_prtt_enrt_rslt_id number) is
727 select grp.rptg_grp_id,
728 grp.name
729 from ben_prtt_enrt_rslt_f prst,
730 ben_popl_rptg_grp_f prpg,
731 ben_rptg_grp grp
732 where
733 prst.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
734 and prst.pl_id = prpg.pl_id
735 and prpg.rptg_grp_id = grp.rptg_grp_id;
736 --
737 --Bill Burns 05-Jul-2001
738 --
739 cursor c_pl_yr(l_prtt_enrt_rslt_id number) is
740 select yrp.start_date,yrp.end_date
741 from ben_yr_perd yrp,
742 ben_popl_yr_perd pop,
743 ben_prtt_enrt_rslt_f pen
744 where pop.yr_perd_id = yrp.yr_perd_id
745 and pop.pl_id = pen.pl_id
746 and p_effective_date between yrp.start_date and nvl(yrp.end_date,p_effective_date)
747 and p_effective_date between pen.effective_start_date and pen.effective_end_date
748 and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
749 and yrp.business_group_id = pen.business_group_id
750 and pop.business_group_id = pen.business_group_id;
751
752 cursor c_pgm_yr(l_prtt_enrt_rslt_id number) is
753 select yrp.start_date,yrp.end_date
754 from ben_yr_perd yrp,
755 ben_popl_yr_perd pop,
756 ben_prtt_enrt_rslt_f pen
757 where pop.yr_perd_id = yrp.yr_perd_id
758 and pop.pgm_id = pen.pgm_id
759 and p_effective_date between yrp.start_date and nvl(yrp.end_date,p_effective_date)
760 and p_effective_date between pen.effective_start_date and pen.effective_end_date
761 and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
762 and yrp.business_group_id = pen.business_group_id
763 and pop.business_group_id = pen.business_group_id;
764 --
765 --End Bill Burns 05-Jul-2001
766 --
767 cursor c_mntot_prem(l_prtt_enrt_rslt_id number) is
768 select sum(prm.val)
769 ,sum(prm.cr_val)
770 from ben_prtt_prem_by_mo_f prm
771 , ben_prtt_prem_f ppe
772 where ppe.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
773 and ppe.prtt_prem_id = prm.prtt_prem_id
774 and prm.yr_num = to_number(to_char(p_effective_date,'YYYY'))
775 and prm.mo_num = to_number(to_char(p_effective_date,'MM'));
776 --
777 cursor c_intrm(l_prtt_enrt_rslt_id number) is
778 select 1
779 from ben_prtt_enrt_rslt_f enrt
780 where enrt.rplcs_sspndd_rslt_id = l_prtt_enrt_rslt_id
781 and enrt.person_id = p_person_id
782 and sspndd_flag = 'Y'
783 and p_effective_date between enrt.effective_start_date
784 and enrt.effective_end_date
785 and p_effective_date between enrt.ENRT_CVG_STRT_DT
786 and enrt.ENRT_CVG_THRU_DT ;
787 --
788 cursor c_interim(l_rplcs_sspndd_rslt_id number) is
789 select
790 enrt.pl_id pl_id,
791 pl.name pl_name,
792 enrt.oipl_id,
793 opt.opt_id opt_id,
794 opt.name opt_name,
795 enrt.bnft_amt cvg_amt
796 from ben_prtt_enrt_rslt_f enrt,
797 ben_pl_f pl,
798 ben_oipl_f oipl,
799 ben_opt_f opt
800 where enrt.prtt_enrt_rslt_id = l_rplcs_sspndd_rslt_id
801 and enrt.pl_id = pl.pl_id
802 and enrt.oipl_id = oipl.oipl_id (+)
803 and opt.opt_id (+) = oipl.opt_id
804 and p_effective_date between enrt.effective_start_date
805 and enrt.effective_end_date
806 and p_effective_date between pl.effective_start_date
807 and pl.effective_end_date
808 and p_effective_date between nvl(oipl.effective_start_date, p_effective_date)
809 and nvl(oipl.effective_end_date, p_effective_date)
810 and p_effective_date between opt.effective_start_date
811 and opt.effective_end_date
812 and enrt.person_id = p_person_id;
813 --
814 l_interim c_interim%rowtype;
815 --
816 cursor ext_rcd_cnt_c is
817 select count(*) from
818 ben_ext_rcd a,
819 ben_ext_rcd_in_file b
820 where a.ext_rcd_id = b.ext_rcd_id
821 and b.ext_file_id = p_ext_file_id
822 and b.rqd_flag = 'Y';
823
824 l_cnt number;
825 l_dummy number;
826 --
827
828 BEGIN
829 --
830 hr_utility.set_location('Entering'||l_proc, 5);
831 --
832 FOR enrt IN c_enrt_rslt LOOP
833 --
834 -- initialize enrt globals - May, 99
835 initialize_enrt_globals;
836
837 -- intialize the cursor variable
838 l_pil_rslt := null ;
839 l_oipl_rslt := null ;
840
841 --- get /pil/ler date
842 if enrt.per_in_ler_id is not null then
843 open c_pil_rslt (enrt.per_in_ler_id) ;
844 fetch c_pil_rslt into l_pil_rslt ;
845 close c_pil_rslt ;
846 end if ;
847
848 --- get option information
849 if enrt.oipl_id is not null then
850 open c_oipl_rslt (enrt.oipl_id) ;
851 fetch c_oipl_rslt into l_oipl_rslt ;
852 close c_oipl_rslt ;
853
854 end if ;
855
856
857
858
859 ben_ext_evaluate_inclusion.Evaluate_Benefit_Incl
860 (p_pl_id => enrt.pl_id,
861 p_sspndd_flag => enrt.sspndd_flag,
862 p_enrt_cvg_strt_dt => enrt.cvg_strt_dt,
863 p_enrt_cvg_thru_dt => enrt.cvg_thru_dt,
864 p_prtt_enrt_rslt_stat_cd => enrt.prtt_enrt_rslt_stat_cd,
865 p_enrt_mthd_cd => enrt.mthd_cd,
866 p_pgm_id => enrt.pgm_id,
867 p_pl_typ_id => enrt.pl_typ_id,
868 p_opt_id => l_oipl_rslt.opt_id,
869 p_last_update_date => trunc(enrt.last_update_date),
870 p_ler_id => l_pil_rslt.ler_id,
871 p_ntfn_dt => l_pil_rslt.ntfn_dt,
872 p_lf_evt_ocrd_dt => l_pil_rslt.lf_evt_ocrd_dt,
873 p_per_in_ler_stat_cd => l_pil_rslt.per_in_ler_stat_cd,
874 p_per_in_ler_id => enrt.per_in_ler_id,
875 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
876 p_effective_date => p_effective_date,
877 p_include => l_include
878 );
879 --
880 IF l_include = 'Y' THEN
881 --
882 -- assign enrollment info to global variables
883 -- after the include calidation get the ptip name and code
884 if enrt.pgm_id is not null and enrt.pl_typ_id is not null then
885
886 open c_ptip_fd(l_pgm_id => enrt.pgm_id ,
887 l_pl_typ_id => enrt.pl_typ_id ) ;
888 fetch c_ptip_fd
889 into ben_ext_person.g_enrt_pl_typ_pgm_fd_name
890 ,ben_ext_person.g_enrt_pl_typ_pgm_fd_code ;
891 close c_ptip_fd ;
892
893 end if ;
894 --
895 /* Start of Changes for WWBUG: 1828349 */
896 ben_ext_person.g_enrt_prtt_enrt_rslt_id := enrt.prtt_enrt_rslt_id;
897 /* End of Changes for WWBUG: 1828349 */
898 ben_ext_person.g_enrt_pl_name := enrt.pl_name;
899 ben_ext_person.g_enrt_orgcovg_strdt := enrt.orgn_strdt;
900 ben_ext_person.g_enrt_prt_orgcovg_strdt := enrt.orgn_strdt;
901 ben_ext_person.g_enrt_status_cd := enrt.status_cd;
902 ben_ext_person.g_enrt_pl_id := enrt.pl_id;
903 ben_ext_person.g_enrt_pl_seq_num := enrt.pl_seq_num;
904 ben_ext_person.g_enrt_pip_seq_num := enrt.plip_seq_num;
905 ben_ext_person.g_enrt_ptp_seq_num := enrt.ptip_seq_num;
906 ben_ext_person.g_enrt_oip_seq_num := enrt.oipl_seq_num;
907 ben_ext_person.g_enrt_cvg_strt_dt := enrt.cvg_strt_dt;
908 ben_ext_person.g_enrt_cvg_thru_dt := enrt.cvg_thru_dt;
909 ben_ext_person.g_enrt_method := enrt.mthd_cd;
910 ben_ext_person.g_enrt_ovrd_flag := enrt.ovridn_flag;
911 ben_ext_person.g_enrt_ovrd_thru_dt := enrt.ovrid_thru_dt;
912 ben_ext_person.g_enrt_ovrd_reason := enrt.ovrid_rsn_cd;
913 ben_ext_person.g_enrt_suspended_flag := enrt.sspndd_flag;
914 ben_ext_person.g_enrt_rslt_effct_strdt := enrt.effct_strdt;
915 ben_ext_person.g_enrt_cvg_amt := enrt.bnft_amt;
916 ben_ext_person.g_enrt_benefit_order_num := enrt.bnft_order_num;
917 ben_ext_person.g_enrt_pgm_id := enrt.pgm_id;
918 ben_ext_person.g_enrt_pl_typ_id := enrt.pl_typ_id;
919 ben_ext_person.g_enrt_pl_typ_name := enrt.pl_typ_name;
920 ben_ext_person.g_enrt_attr_1 := enrt.pen_attribute1;
921 ben_ext_person.g_enrt_attr_2 := enrt.pen_attribute2;
922 ben_ext_person.g_enrt_attr_3 := enrt.pen_attribute3;
923 ben_ext_person.g_enrt_attr_4 := enrt.pen_attribute4;
924 ben_ext_person.g_enrt_attr_5 := enrt.pen_attribute5;
925 ben_ext_person.g_enrt_attr_6 := enrt.pen_attribute6;
926 ben_ext_person.g_enrt_attr_7 := enrt.pen_attribute7;
927 ben_ext_person.g_enrt_attr_8 := enrt.pen_attribute8;
928 ben_ext_person.g_enrt_attr_9 := enrt.pen_attribute9;
929 ben_ext_person.g_enrt_attr_10 := enrt.pen_attribute10;
930 ben_ext_person.g_enrt_ler_id := enrt.enrt_ler_id;
931 ben_ext_person.g_enrt_assignment_id := enrt.assignment_id;
932 ben_ext_person.g_enrt_uom := enrt.uom;
933 ben_ext_person.g_pl_attr_1 := enrt.pln_attribute1;
934 ben_ext_person.g_pl_attr_2 := enrt.pln_attribute2;
935 ben_ext_person.g_pl_attr_3 := enrt.pln_attribute3;
936 ben_ext_person.g_pl_attr_4 := enrt.pln_attribute4;
937 ben_ext_person.g_pl_attr_5 := enrt.pln_attribute5;
938 ben_ext_person.g_pl_attr_6 := enrt.pln_attribute6;
939 ben_ext_person.g_pl_attr_7 := enrt.pln_attribute7;
940 ben_ext_person.g_pl_attr_8 := enrt.pln_attribute8;
941 ben_ext_person.g_pl_attr_9 := enrt.pln_attribute9;
942 ben_ext_person.g_pl_attr_10 := enrt.pln_attribute10;
943 ben_ext_person.g_ptp_attr_1 := enrt.ptp_attribute1;
944 ben_ext_person.g_ptp_attr_2 := enrt.ptp_attribute2;
945 ben_ext_person.g_ptp_attr_3 := enrt.ptp_attribute3;
946 ben_ext_person.g_ptp_attr_4 := enrt.ptp_attribute4;
947 ben_ext_person.g_ptp_attr_5 := enrt.ptp_attribute5;
948 ben_ext_person.g_ptp_attr_6 := enrt.ptp_attribute6;
949 ben_ext_person.g_ptp_attr_7 := enrt.ptp_attribute7;
950 ben_ext_person.g_ptp_attr_8 := enrt.ptp_attribute8;
951 ben_ext_person.g_ptp_attr_9 := enrt.ptp_attribute9;
952 ben_ext_person.g_ptp_attr_10 := enrt.ptp_attribute10;
953
954 --
955 ben_ext_person.g_enrt_lfevt_name := l_pil_rslt.ler_name;
956 ben_ext_person.g_enrt_lfevt_status := l_pil_rslt.per_in_ler_stat_cd;
957 ben_ext_person.g_enrt_lfevt_note_dt := l_pil_rslt.ntfn_dt;
958 ben_ext_person.g_enrt_lfevt_ocrd_dt := l_pil_rslt.lf_evt_ocrd_dt;
959 --
960 ben_ext_person.g_enrt_opt_id := l_oipl_rslt.opt_id;
961 ben_ext_person.g_enrt_opt_name := l_oipl_rslt.opt_name;
962 ben_ext_person.g_oipl_attr_1 := l_oipl_rslt.cop_attribute1;
963 ben_ext_person.g_oipl_attr_2 := l_oipl_rslt.cop_attribute2;
964 ben_ext_person.g_oipl_attr_3 := l_oipl_rslt.cop_attribute3;
965 ben_ext_person.g_oipl_attr_4 := l_oipl_rslt.cop_attribute4;
966 ben_ext_person.g_oipl_attr_5 := l_oipl_rslt.cop_attribute5;
967 ben_ext_person.g_oipl_attr_6 := l_oipl_rslt.cop_attribute6;
968 ben_ext_person.g_oipl_attr_7 := l_oipl_rslt.cop_attribute7;
969 ben_ext_person.g_oipl_attr_8 := l_oipl_rslt.cop_attribute8;
970 ben_ext_person.g_oipl_attr_9 := l_oipl_rslt.cop_attribute9;
971 ben_ext_person.g_oipl_attr_10 := l_oipl_rslt.cop_attribute10;
972 ben_ext_person.g_opt_attr_1 := l_oipl_rslt.opt_attribute1;
973 ben_ext_person.g_opt_attr_2 := l_oipl_rslt.opt_attribute2;
974 ben_ext_person.g_opt_attr_3 := l_oipl_rslt.opt_attribute3;
975 ben_ext_person.g_opt_attr_4 := l_oipl_rslt.opt_attribute4;
976 ben_ext_person.g_opt_attr_5 := l_oipl_rslt.opt_attribute5;
977 ben_ext_person.g_opt_attr_6 := l_oipl_rslt.opt_attribute6;
978 ben_ext_person.g_opt_attr_7 := l_oipl_rslt.opt_attribute7;
979 ben_ext_person.g_opt_attr_8 := l_oipl_rslt.opt_attribute8;
980 ben_ext_person.g_opt_attr_9 := l_oipl_rslt.opt_attribute9;
981 ben_ext_person.g_opt_attr_10 := l_oipl_rslt.opt_attribute10;
982 ben_ext_person.g_enrt_opt_fd_name := l_oipl_rslt.opt_fd_name ;
983 ben_ext_person.g_enrt_opt_fd_code := l_oipl_rslt.opt_fd_code ;
984 ben_ext_person.g_enrt_opt_pl_fd_name := l_oipl_rslt.oipl_fd_name ;
985 ben_ext_person.g_enrt_opt_pl_fd_code := l_oipl_rslt.oipl_fd_code ;
986 --2559743
987 ben_ext_person.g_enrt_pl_fd_name := enrt.pl_fd_name ;
988 ben_ext_person.g_enrt_pl_fd_code := enrt.pl_fd_code ;
989 ben_ext_person.g_enrt_pl_typ_fd_name := enrt.ptp_fd_name ;
990 ben_ext_person.g_enrt_pl_typ_fd_code := enrt.ptp_fd_code ;
991 ----
992
993 if enrt.pgm_id is not null then
994 open c_pgm_rslt(enrt.pgm_id ) ;
995 fetch c_pgm_rslt into
996 ben_ext_person.g_enrt_pgm_name
997 , ben_ext_person.g_enrt_pgm_fd_name
998 , ben_ext_person.g_enrt_pgm_fd_code
999 , ben_ext_person.g_pgm_attr_1
1000 , ben_ext_person.g_pgm_attr_2
1001 , ben_ext_person.g_pgm_attr_3
1002 , ben_ext_person.g_pgm_attr_4
1003 , ben_ext_person.g_pgm_attr_5
1004 , ben_ext_person.g_pgm_attr_6
1005 , ben_ext_person.g_pgm_attr_7
1006 , ben_ext_person.g_pgm_attr_8
1007 , ben_ext_person.g_pgm_attr_9
1008 , ben_ext_person.g_pgm_attr_10
1009 ;
1010 close c_pgm_rslt ;
1011 end if ;
1012
1013 if enrt.pgm_id is not null and enrt.pl_id is not null then
1014 open c_plip_rslt (enrt.pgm_id ,
1015 enrt.pl_id ) ;
1016 fetch c_plip_rslt into
1017 ben_ext_person.g_plip_attr_1
1018 ,ben_ext_person.g_plip_attr_2
1019 ,ben_ext_person.g_plip_attr_3
1020 ,ben_ext_person.g_plip_attr_4
1021 ,ben_ext_person.g_plip_attr_5
1022 ,ben_ext_person.g_plip_attr_6
1023 ,ben_ext_person.g_plip_attr_7
1024 ,ben_ext_person.g_plip_attr_8
1025 ,ben_ext_person.g_plip_attr_9
1026 ,ben_ext_person.g_plip_attr_10
1027 ,ben_ext_person.g_enrt_pl_pgm_fd_name
1028 ,ben_ext_person.g_enrt_pl_pgm_fd_code
1029 ;
1030 close c_plip_rslt ;
1031 end if ;
1032
1033 open c_popl_rslt(enrt.pgm_id ,
1034 enrt.pl_id ,
1035 enrt.per_in_ler_id ) ;
1036 fetch c_popl_rslt into ben_ext_person.g_enrt_elec_made_dt ;
1037
1038 close c_popl_rslt ;
1039
1040
1041
1042 --
1043 -- retrieve additional enrollment information
1044 --
1045 -- retrieve policy or group number if required
1046 if ben_extract.g_pgn_csr = 'Y' then
1047 open plcy_c(enrt.pl_id);
1048 fetch plcy_c into ben_ext_person.g_enrt_plcy_r_grp;
1049 close plcy_c;
1050 end if;
1051 --
1052 -- retrieve primary care provider info if required
1053 if ben_extract.g_ppcp_csr = 'Y' then
1054 open c_prmry_care_prvdr(enrt.prtt_enrt_rslt_id);
1055 fetch c_prmry_care_prvdr into ben_ext_person.g_ppr_name
1056 ,ben_ext_person.g_ppr_ident
1057 ,ben_ext_person.g_ppr_typ
1058 ,ben_ext_person.g_ppr_strt_dt
1059 ,ben_ext_person.g_ppr_end_dt;
1060 close c_prmry_care_prvdr;
1061 end if;
1062 --
1063 if ben_extract.g_rt_csr = 'Y' then
1064 --
1065 hr_utility.set_location(' person Id ' || p_person_id , 991) ;
1066 hr_utility.set_location(' result id ' || enrt.prtt_enrt_rslt_id , 991 ) ;
1067 get_rt_info(p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1068 p_effective_date => p_effective_date);
1069 --
1070 end if;
1071 --
1072 -- retrieve total premium info if required
1073 if ben_extract.g_pprem_csr = 'Y' then
1074 open prem_tot_c(enrt.prtt_enrt_rslt_id);
1075 fetch prem_tot_c into ben_ext_person.g_enrt_total_premium_amt
1076 ,ben_ext_person.g_enrt_total_premium_uom;
1077 close prem_tot_c;
1078 end if;
1079 --
1080 -- this is for person level so moving benxpers
1081 -- retrieve total flex credit info
1082 /*
1083 if ben_extract.g_flxcr_csr = 'Y' then
1084 hr_utility.set_location('entering to open flex credit ' ,160);
1085 hr_utility.set_location('entering to open flex credit '|| enrt.prtt_enrt_rslt_id ,160);
1086 open flex_cred_info_c(enrt.prtt_enrt_rslt_id);
1087 fetch flex_cred_info_c into ben_ext_person.g_flex_credit_provided
1088 ,ben_ext_person.g_flex_credit_forfited
1089 ,ben_ext_person.g_flex_credit_used;
1090
1091 ben_ext_person.g_flex_credit_excess :=
1092 nvl(ben_ext_person.g_flex_credit_provided,0) -
1093 nvl(ben_ext_person.g_flex_credit_forfited,0) -
1094 nvl(ben_ext_person.g_flex_credit_used,0);
1095 close flex_cred_info_c;
1096 end if;
1097 */
1098 --
1099 --
1100 -- Reporting group informations
1101 --
1102 if ben_extract.g_prgrp_csr = 'Y' then
1103 open c_rpt_grp(enrt.prtt_enrt_rslt_id);
1104 fetch c_rpt_grp into ben_ext_person.g_enrt_rpt_group_id,
1105 ben_ext_person.g_enrt_rpt_group_name;
1106 close c_rpt_grp;
1107 end if;
1108 --
1109 --
1110 -- Plan Year Informations
1111 -- --B.Burns #: 1641610
1112 if ben_extract.g_pplyr_csr = 'Y' then
1113 open c_pl_yr(enrt.prtt_enrt_rslt_id);
1114 fetch c_pl_yr into ben_ext_person.g_enrt_pl_yr_strdt,
1115 ben_ext_person.g_enrt_pl_yr_enddt;
1116 if c_pl_yr%notfound then
1117 open c_pgm_yr(enrt.prtt_enrt_rslt_id);
1118 fetch c_pgm_yr into ben_ext_person.g_enrt_pl_yr_strdt,
1119 ben_ext_person.g_enrt_pl_yr_enddt;
1120 close c_pgm_yr;
1121 end if;
1122 close c_pl_yr;
1123 end if;
1124 --end B.Burns
1125 --
1126 -- Monthly Total Premium Informations
1127 --
1128 if ben_extract.g_pmtpr_csr = 'Y' then
1129 ben_ext_prem.premium_total(
1130 p_person_id => p_person_id,
1131 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1132 p_ext_rslt_id => p_ext_rslt_id,
1133 p_ext_file_id => p_ext_file_id,
1134 p_data_typ_cd => p_data_typ_cd,
1135 p_ext_typ_cd => p_ext_typ_cd,
1136 p_chg_evt_cd => p_chg_evt_cd,
1137 p_business_group_id => p_business_group_id,
1138 p_effective_date => p_effective_date
1139 );
1140
1141
1142 end if;
1143 --
1144 if ben_extract.g_int_csr = 'Y' then
1145 if enrt.rplcs_sspndd_rslt_id is not null and enrt.sspndd_flag = 'Y' then
1146 open c_interim(enrt.rplcs_sspndd_rslt_id);
1147 fetch c_interim into l_interim;
1148 if c_interim%FOUND then
1149 ben_ext_person.g_enrt_int_pl_id := l_interim.pl_id;
1150 ben_ext_person.g_enrt_int_pl_name := l_interim.pl_name;
1151 ben_ext_person.g_enrt_int_opt_id := l_interim.opt_id;
1152 ben_ext_person.g_enrt_int_opt_name:= l_interim.opt_name;
1153 ben_ext_person.g_enrt_int_cvg_amt := l_interim.cvg_amt;
1154 close c_interim;
1155 else
1156 close c_interim;
1157 end if;
1158 end if;
1159 end if;
1160
1161 -- Interim Flag
1162 --
1163 if ben_extract.g_intrm_csr = 'Y' then
1164 open c_intrm(enrt.prtt_enrt_rslt_id);
1165 fetch c_intrm into l_dummy;
1166 if c_intrm%FOUND then
1167 ben_ext_person.g_enrt_intrcovg_flag := 'Y';
1168 else
1169 ben_ext_person.g_enrt_intrcovg_flag := 'N';
1170 end if;
1171 close c_intrm;
1172 end if;
1173 --
1174 --
1175 --
1176 IF ben_extract.g_enrt_lvl = 'Y' THEN
1177 --
1178 -- format and write enrollment
1179 -- ===========================
1180 --
1181 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
1182 p_ext_file_id => p_ext_file_id,
1183 p_data_typ_cd => p_data_typ_cd,
1184 p_ext_typ_cd => p_ext_typ_cd,
1185 p_rcd_typ_cd => 'D',
1186 p_low_lvl_cd => 'E',
1187 p_person_id => p_person_id,
1188 p_chg_evt_cd => p_chg_evt_cd,
1189 p_business_group_id => p_business_group_id,
1190 p_effective_date => p_effective_date
1191 );
1192 --
1193 END IF;
1194
1195
1196 -- call premium level process
1197 --
1198 IF ben_extract.g_prem_lvl = 'Y' then
1199 --
1200 ben_ext_prem.main(
1201 p_person_id => p_person_id,
1202 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1203 p_ext_rslt_id => p_ext_rslt_id,
1204 p_ext_file_id => p_ext_file_id,
1205 p_data_typ_cd => p_data_typ_cd,
1206 p_ext_typ_cd => p_ext_typ_cd,
1207 p_chg_evt_cd => p_chg_evt_cd,
1208 p_business_group_id => p_business_group_id,
1209 p_effective_date => p_effective_date
1210 );
1211 --
1212 END IF;
1213
1214
1215 --
1216 -- call dependent level process
1217 -- ====================================
1218 --
1219 IF ben_extract.g_dpnt_lvl = 'Y' then
1220 --
1221 ben_ext_dpnt.main(
1222 p_person_id => p_person_id,
1223 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1224 p_ext_rslt_id => p_ext_rslt_id,
1225 p_ext_file_id => p_ext_file_id,
1226 p_data_typ_cd => p_data_typ_cd,
1227 p_ext_typ_cd => p_ext_typ_cd,
1228 p_chg_evt_cd => p_chg_evt_cd,
1229 p_business_group_id => p_business_group_id,
1230 p_effective_date => p_effective_date
1231 );
1232 --
1233 END IF;
1234 --
1235 -- call beneficiary level process
1236 -- =================================================
1237 --
1238 IF ben_extract.g_bnf_lvl = 'Y' then
1239 --
1240 ben_ext_bnf.main(
1241 p_person_id => p_person_id,
1242 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1243 p_ext_rslt_id => p_ext_rslt_id,
1244 p_ext_file_id => p_ext_file_id,
1245 p_data_typ_cd => p_data_typ_cd,
1246 p_ext_typ_cd => p_ext_typ_cd,
1247 p_chg_evt_cd => p_chg_evt_cd,
1248 p_business_group_id => p_business_group_id,
1249 p_effective_date => p_effective_date
1250 );
1251 --
1252 END IF;
1253 --
1254 --
1255 -- call actions level process
1256 -- =================================================
1257 --
1258 IF ben_extract.g_actn_lvl = 'Y' then
1259 --
1260 ben_ext_actn.main(
1261 p_person_id => p_person_id,
1262 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
1263 p_ext_rslt_id => p_ext_rslt_id,
1264 p_ext_file_id => p_ext_file_id,
1265 p_data_typ_cd => p_data_typ_cd,
1266 p_ext_typ_cd => p_ext_typ_cd,
1267 p_chg_evt_cd => p_chg_evt_cd,
1268 p_business_group_id => p_business_group_id,
1269 p_effective_date => p_effective_date
1270 );
1271 --
1272 END IF;
1273 --
1274 END IF; -- l_include = 'Y'
1275 --
1276 END LOOP;
1277 hr_utility.set_location('Exiting'||l_proc, 15);
1278
1279 END; -- main
1280 --
1281 END; -- package