[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_DPNT
Source
1 package body BEN_EXT_DPNT as
2 /* $Header: benxdpnt.pkb 120.1 2007/10/16 23:18:59 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_dpnt.'; -- Global package name
9 --
10 -- procedure to initialize the globals - May, 99
11 -- ----------------------------------------------------------------------------
12 -- |---------------------< initialize_globals >-------------------------------|
13 -- ----------------------------------------------------------------------------
14 PROCEDURE initialize_globals IS
15 --
16 l_proc varchar2(72) := g_package||'initialize_globals';
17 --
18 Begin
19 --
20 --
21 hr_utility.set_location('Entering'||l_proc, 5);
22 --
23 --
24 ben_ext_person.g_dpnt_cvrd_dpnt_id := null;
25 ben_ext_person.g_dpnt_cvg_strt_dt := null;
26 ben_ext_person.g_dpnt_cvg_thru_dt := null;
27 ben_ext_person.g_dpnt_rlshp_type := null;
28 ben_ext_person.g_dpnt_contact_seq_num := null;
29 ben_ext_person.g_dpnt_shared_resd_flag := null;
30 ben_ext_person.g_dpnt_national_identifier := null;
31 ben_ext_person.g_dpnt_last_name := null;
32 ben_ext_person.g_dpnt_first_name := null;
33 ben_ext_person.g_dpnt_middle_names := null;
34 ben_ext_person.g_dpnt_full_name := null;
35 ben_ext_person.g_dpnt_suffix := null;
36 ben_ext_person.g_dpnt_prefix := null;
37 ben_ext_person.g_dpnt_title := null;
38 ben_ext_person.g_dpnt_date_of_birth := null;
39 ben_ext_person.g_dpnt_marital_status := null;
40 ben_ext_person.g_dpnt_sex := null;
41 ben_ext_person.g_dpnt_disabled_flag := null;
42 ben_ext_person.g_dpnt_student_status := null;
43 ben_ext_person.g_dpnt_date_of_death := null;
44 ben_ext_person.g_dpnt_language := null;
45 ben_ext_person.g_dpnt_nationality := null;
46 ben_ext_person.g_dpnt_email_address := null;
47 ben_ext_person.g_dpnt_known_as := null;
48 ben_ext_person.g_dpnt_known_as := null;
49 ben_ext_person.g_dpnt_pre_name_adjunct := null;
50 ben_ext_person.g_dpnt_tobacco_usage := null;
51 ben_ext_person.g_dpnt_prev_last_name := null;
52 ben_ext_person.g_dpnt_prim_address1 := null;
53 ben_ext_person.g_dpnt_prim_address2 := null;
54 ben_ext_person.g_dpnt_prim_address3 := null;
55 ben_ext_person.g_dpnt_prim_city := null;
56 ben_ext_person.g_dpnt_prim_state := null;
57 ben_ext_person.g_dpnt_prim_postal_code := null;
58 ben_ext_person.g_dpnt_prim_country := null;
59 ben_ext_person.g_dpnt_prim_effect_date := null;
60 ben_ext_person.g_dpnt_prim_region := null;
61 ben_ext_person.g_dpnt_home_phone := null;
62 ben_ext_person.g_dpnt_work_phone := null;
63 ben_ext_person.g_dpnt_fax := null;
64 ben_ext_person.g_dpnt_mobile := null;
65 ben_ext_person.g_dpnt_ppr_name := null;
66 ben_ext_person.g_dpnt_ppr_ident := null;
67 ben_ext_person.g_dpnt_ppr_typ := null;
68 ben_ext_person.g_dpnt_ppr_strt_dt := null;
69 ben_ext_person.g_dpnt_ppr_end_dt := null;
70 --
71 --
72 hr_utility.set_location('Exiting'||l_proc, 15);
73 --
74 End initialize_globals;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |---------------------< main >---------------------------------------------|
78 -- ----------------------------------------------------------------------------
79 PROCEDURE main
80 ( p_person_id in number,
81 p_prtt_enrt_rslt_id in number,
82 p_ext_rslt_id in number,
83 p_ext_file_id in number,
84 p_data_typ_cd in varchar2,
85 p_ext_typ_cd in varchar2,
86 p_chg_evt_cd in varchar2,
87 p_business_group_id in number,
88 p_effective_date in date) is
89 --
90 l_proc varchar2(72) := g_package||'main';
91 l_include varchar2(1) ;
92 --
93 cursor c_dpnt (l_prtt_enrt_rslt_id number) is
94 select
95 a.elig_cvrd_dpnt_id
96 , a.cvg_strt_dt
97 , a.cvg_thru_dt
98 , a.dpnt_person_id
99 , a.per_in_ler_id
100 , a.prtt_enrt_rslt_id
101 , a.last_update_date
102 , b.contact_type
103 , b.sequence_number
104 , b.rltd_per_rsds_w_dsgntr_flag
105 , c.last_name
106 , c.correspondence_language
107 , c.date_of_birth
108 , c.email_address
109 , c.first_name
110 , c.full_name
111 , c.marital_status
112 , c.middle_names
113 , c.nationality
114 , c.national_identifier
115 , c.registered_disabled_flag
116 , c.sex
117 , c.student_status
118 , c.suffix
119 , c.pre_name_adjunct prefix
120 , c.title
121 , c.date_of_death
122 , c.known_as
123 , c.pre_name_adjunct
124 , c.uses_tobacco_flag
125 , c.previous_last_name
126 from ben_elig_cvrd_dpnt_f a,
127 per_contact_relationships b,
128 per_all_people_f c
129 where
130 a.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
131 and a.cvg_pndg_flag = 'N'
132 -- remarked for bug 1394604
133 -- and p_effective_date between a.cvg_strt_dt
134 -- and a.cvg_thru_dt
135 -- and a.cvg_thru_dt <= a.effective_end_date
136 --
137 and a.dpnt_person_id = b.contact_person_id
138 and b.person_id = p_person_id
139 --
140 and c.person_id = a.dpnt_person_id
141 and p_effective_date between c.effective_start_date
142 and c.effective_end_date
143 and p_effective_date between a.effective_start_date
144 and a.effective_end_date
145 and p_effective_date between nvl(b.date_Start,p_effective_date)
146 and nvl(b.date_end ,p_effective_date)
147 ;
148
149
150 cursor c_pil (p_per_in_ler_id number) is
151 select pil.PER_IN_LER_STAT_CD,
152 pil.LF_EVT_OCRD_DT,
153 pil.NTFN_DT,
154 pil.LER_ID
155 from ben_per_in_ler pil
156 where pil.per_in_ler_id = p_per_in_ler_id ;
157
158 l_per_in_ler_stat_cd ben_per_in_ler.PER_IN_LER_STAT_CD%Type ;
159 l_LF_EVT_OCRD_DT ben_per_in_ler.LF_EVT_OCRD_DT%type ;
160 l_NTFN_DT ben_per_in_ler.NTFN_DT%type ;
161 l_ler_id ben_per_in_ler.ler_id%type ;
162
163
164
165
166 --
167 cursor prim_address_c(p_dpnt_person_id number) is
168 select
169 a.address_line1
170 , a.address_line2
171 , a.address_line3
172 , a.town_or_city
173 , a.region_2
174 , a.postal_code
175 , a.country
176 , a.date_from
177 , a.region_3
178 from per_addresses a
179 where
180 a.person_id = p_dpnt_person_id
181 and p_effective_date between nvl(a.date_from, p_effective_date)
182 and nvl(a.date_to, p_effective_date)
183 and a.primary_flag = 'Y'
184 ;
185 --
186 cursor prim_rltd_address_c(p_dpnt_person_id number) is
187 select
188 a.address_line1
189 , a.address_line2
190 , a.address_line3
191 , a.town_or_city
192 , a.region_2
193 , a.postal_code
194 , a.country
195 , a.date_from
196 , a.region_3
197 from per_addresses a,
198 per_contact_relationships r
199 where
200 r.contact_person_id = p_dpnt_person_id
201 and r.person_id = p_person_id
202 and r.person_id = a.person_id
203 and p_effective_date between nvl(a.date_from, p_effective_date)
204 and nvl(a.date_to, p_effective_date)
205 and a.primary_flag = 'Y'
206 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
207 /* Start of Changes for WWBUG: 1856611: addition */
208 and p_effective_date between r.date_start
209 and nvl(r.date_end,hr_api.g_eot)
210 /* End of Changes for WWBUG: 1856611: addition */
211 ;
212 --
213 cursor ext_phone_c(p_dpnt_person_id number) is
214 select
215 h.phone_number phone_home
216 , w.phone_number phone_work
217 , f.phone_number phone_fax
218 , m.phone_number phone_mobile
219 from per_all_people_f p
220 , per_phones h
221 , per_phones w
222 , per_phones f
223 , per_phones m
224 where p.person_id = p_dpnt_person_id
225 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
226 and nvl(p.effective_end_date, p_effective_date)
227 and h.parent_id (+) = p.person_id
228 and w.parent_id (+) = p.person_id
229 and f.parent_id (+) = p.person_id
230 and m.parent_id (+) = p.person_id
231 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
232 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
233 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
234 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
235 and h.phone_type (+) = 'H1'
236 and w.phone_type (+) = 'W1'
237 and f.phone_type (+) = 'WF'
238 and m.phone_type (+) = 'M'
239 and p_effective_date between nvl(h.date_from, p_effective_date)
240 and nvl(h.date_to, p_effective_date)
241 and p_effective_date between nvl(w.date_from, p_effective_date)
242 and nvl(w.date_to, p_effective_date)
243 and p_effective_date between nvl(f.date_from, p_effective_date)
244 and nvl(f.date_to, p_effective_date)
245 and p_effective_date between nvl(m.date_from, p_effective_date)
246 and nvl(m.date_to, p_effective_date)
247 ;
248
249 --
250 cursor c_dpnt_prmry_care_prvdr(p_elig_cvrd_dpnt_id number) is
251 SELECT name
252 ,ext_ident
253 ,prmry_care_prvdr_typ_cd
254 ,effective_start_date
255 ,effective_end_date
256 FROM ben_prmry_care_prvdr_f ppr
257 WHERE ppr.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
258 AND p_effective_date between ppr.effective_start_date
259 and ppr.effective_end_date;
260 --
261 --
262 BEGIN
263 --
264 hr_utility.set_location('Entering'||l_proc, 5);
265 hr_utility.set_location('before loop', 604);
266 --
267 FOR dpnt IN c_dpnt(p_prtt_enrt_rslt_id) LOOP
268 --
269 hr_utility.set_location('internal loop', 604);
270 -- initialize the globals - May, 99
271 initialize_globals;
272 --validationg the coverage date
273 --if it is change event and for the dependent then
274 --extract only the dependent
275 hr_utility.set_location('param ' || ben_ext_person.g_chg_prmtr_06 ,88);
276 hr_utility.set_location('dependent ' || dpnt.dpnt_person_id ,88);
277 hr_utility.set_location('rslt ' || ben_ext_person.g_chg_prmtr_06 ,88);
278 hr_utility.set_location('prm rslt ' || p_prtt_enrt_rslt_id ,88);
279
280
281 --- get the pil info from ecdpnt per in ler id
282 open c_pil (dpnt.per_in_ler_id) ;
283 fetch c_pil into
284 l_per_in_ler_stat_cd,
285 l_LF_EVT_OCRD_DT,
286 l_NTFN_DT ,
287 l_ler_id ;
288 close c_pil ;
289
290
291
292 ben_ext_evaluate_inclusion.evaluate_benefit_incl(
293 p_enrt_cvg_strt_dt => dpnt.cvg_strt_dt,
294 p_enrt_cvg_thru_dt => dpnt.cvg_thru_dt,
295 p_pl_id => ben_ext_person.g_enrt_pl_id ,
296 p_sspndd_flag => ben_ext_person.g_enrt_suspended_flag,
297 p_prtt_enrt_rslt_stat_cd => ben_ext_person.g_enrt_status_cd,
298 p_enrt_mthd_cd => ben_ext_person.g_enrt_method,
299 p_pgm_id => ben_ext_person.g_enrt_pgm_id ,
300 p_pl_typ_id => ben_ext_person.g_enrt_pl_typ_id,
301 p_last_update_date => dpnt.last_update_date,
302 p_ler_id => nvl(l_ler_id,ben_ext_person.g_enrt_ler_id),
303 p_ntfn_dt => nvl(l_NTFN_DT,ben_ext_person.g_enrt_lfevt_note_dt),
304 p_lf_evt_ocrd_dt => nvl(l_LF_EVT_OCRD_DT,ben_ext_person.g_enrt_lfevt_ocrd_dt) ,
305 p_per_in_ler_stat_cd => nvl(l_per_in_ler_stat_cd,ben_ext_person.g_enrt_lfevt_status),
306 p_per_in_ler_id => dpnt.per_in_ler_id,
307 p_prtt_enrt_rslt_id => dpnt.prtt_enrt_rslt_id,
308 p_effective_date => p_effective_date,
309 p_dpnt_id => dpnt.dpnt_person_id,
310 p_include => l_include);
311
312 hr_utility.set_location('inclide '||l_include , 604);
313
314 IF l_include = 'Y' THEN
315 --
316 hr_utility.set_location(' internal loo include '||dpnt.full_name ,604);
317 -- fetch dependent information into globals
318 --
319 --RCHASE - Bug#6669 - Must initialize the g_dpnt_cvrd_dpnt_id cache entry
320 ben_ext_person.g_dpnt_cvrd_dpnt_id := dpnt.elig_cvrd_dpnt_id;
321 --RCHASE - End
322 ben_ext_person.g_dpnt_cvg_strt_dt := dpnt.cvg_strt_dt;
323 ben_ext_person.g_dpnt_cvg_thru_dt := dpnt.cvg_thru_dt;
324 ben_ext_person.g_dpnt_rlshp_type := dpnt.contact_type;
325 ben_ext_person.g_dpnt_contact_seq_num := dpnt.sequence_number;
329 ben_ext_person.g_dpnt_first_name := dpnt.first_name;
326 ben_ext_person.g_dpnt_shared_resd_flag := dpnt.rltd_per_rsds_w_dsgntr_flag;
327 ben_ext_person.g_dpnt_national_identifier := dpnt.national_identifier;
328 ben_ext_person.g_dpnt_last_name := dpnt.last_name;
330 ben_ext_person.g_dpnt_middle_names := dpnt.middle_names;
331 ben_ext_person.g_dpnt_full_name := dpnt.full_name;
332 ben_ext_person.g_dpnt_suffix := dpnt.suffix;
333 ben_ext_person.g_dpnt_prefix := dpnt.prefix;
334 ben_ext_person.g_dpnt_title := dpnt.title;
335 ben_ext_person.g_dpnt_date_of_birth := dpnt.date_of_birth;
336 ben_ext_person.g_dpnt_marital_status := dpnt.marital_status;
337 ben_ext_person.g_dpnt_sex := dpnt.sex;
338 ben_ext_person.g_dpnt_disabled_flag := dpnt.registered_disabled_flag;
339 ben_ext_person.g_dpnt_student_status := dpnt.student_status;
340 ben_ext_person.g_dpnt_date_of_death := dpnt.date_of_death;
341 ben_ext_person.g_dpnt_language := dpnt.correspondence_language;
342 ben_ext_person.g_dpnt_nationality := dpnt.nationality;
343 ben_ext_person.g_dpnt_email_address := dpnt.email_address;
344 ben_ext_person.g_dpnt_known_as := dpnt.known_as;
345 ben_ext_person.g_dpnt_pre_name_adjunct := dpnt.pre_name_adjunct;
346 ben_ext_person.g_dpnt_tobacco_usage := dpnt.uses_tobacco_flag;
347 ben_ext_person.g_dpnt_prev_last_name := dpnt.previous_last_name;
348 -- end if ;
349 --
350 -- retrieve dependent address info if required
351 if ben_extract.g_da_csr = 'Y' then
352 open prim_address_c(dpnt.dpnt_person_id);
353 fetch prim_address_c into ben_ext_person.g_dpnt_prim_address1
354 ,ben_ext_person.g_dpnt_prim_address2
355 ,ben_ext_person.g_dpnt_prim_address3
356 ,ben_ext_person.g_dpnt_prim_city
357 ,ben_ext_person.g_dpnt_prim_state
358 ,ben_ext_person.g_dpnt_prim_postal_code
359 ,ben_ext_person.g_dpnt_prim_country
360 ,ben_ext_person.g_dpnt_prim_effect_date
361 ,ben_ext_person.g_dpnt_prim_region;
362 if prim_address_c%notfound then
363 open prim_rltd_address_c(dpnt.dpnt_person_id);
364 fetch prim_rltd_address_c into ben_ext_person.g_dpnt_prim_address1
365 , ben_ext_person.g_dpnt_prim_address2
366 , ben_ext_person.g_dpnt_prim_address3
367 , ben_ext_person.g_dpnt_prim_city
368 , ben_ext_person.g_dpnt_prim_state
369 , ben_ext_person.g_dpnt_prim_postal_code
370 , ben_ext_person.g_dpnt_prim_country
371 , ben_ext_person.g_dpnt_prim_effect_date
372 , ben_ext_person.g_dpnt_prim_region;
373 close prim_rltd_address_c;
374 end if;
375 close prim_address_c;
376 end if;
377 --
378 -- retrieve dependent phone numbers if required
379 if ben_extract.g_dp_csr = 'Y' then
380 open ext_phone_c(dpnt.dpnt_person_id);
381 fetch ext_phone_c into ben_ext_person.g_dpnt_home_phone
382 ,ben_ext_person.g_dpnt_work_phone
383 ,ben_ext_person.g_dpnt_fax
384 ,ben_ext_person.g_dpnt_mobile;
385 close ext_phone_c;
386 end if;
387 --
388 -- retrieve dependent primary care provider info if required
389 if ben_extract.g_dpcp_csr = 'Y' then
390 open c_dpnt_prmry_care_prvdr(dpnt.elig_cvrd_dpnt_id);
391 fetch c_dpnt_prmry_care_prvdr into ben_ext_person.g_dpnt_ppr_name
392 ,ben_ext_person.g_dpnt_ppr_ident
393 ,ben_ext_person.g_dpnt_ppr_typ
394 ,ben_ext_person.g_dpnt_ppr_strt_dt
395 ,ben_ext_person.g_dpnt_ppr_end_dt;
396 close c_dpnt_prmry_care_prvdr;
397 end if;
398 --
399 -- format and write
400 --
401 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
402 p_ext_file_id => p_ext_file_id,
403 p_data_typ_cd => p_data_typ_cd,
404 p_ext_typ_cd => p_ext_typ_cd,
405 p_rcd_typ_cd => 'D',
406 p_low_lvl_cd => 'D',
407 p_person_id => p_person_id,
408 p_chg_evt_cd => p_chg_evt_cd,
409 p_business_group_id => p_business_group_id,
410 p_effective_date => p_effective_date
411 );
412 end if ;
413 --
414 END LOOP;
418 END; -- main
415 --
416 hr_utility.set_location('Exiting'||l_proc, 15);
417
419 --
420 END; -- package