[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_CONTACT
Source
1 package body BEN_EXT_CONTACT as
2 /* $Header: benxcnct.pkb 120.0 2005/05/28 09:38:55 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_contact.'; -- 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_contact_rlshp_id := null;
25 ben_ext_person.g_contact_rlshp_type := null;
26 ben_ext_person.g_contact_seq_num := null;
27 ben_ext_person.g_contact_national_ident := null;
28 ben_ext_person.g_contact_last_name := null;
29 ben_ext_person.g_contact_first_name := null;
30 ben_ext_person.g_contact_middle_names := null;
31 ben_ext_person.g_contact_full_name := null;
32 ben_ext_person.g_contact_suffix := null;
33 ben_ext_person.g_contact_prefix := null;
34 ben_ext_person.g_contact_title := null;
35 ben_ext_person.g_contact_date_of_birth := null;
36 ben_ext_person.g_contact_marital_status := null;
37 ben_ext_person.g_contact_sex := null;
38 ben_ext_person.g_contact_disabled_flag := null;
39 ben_ext_person.g_contact_student_status := null;
40 ben_ext_person.g_contact_date_of_death := null;
41 ben_ext_person.g_contact_language := null;
42 ben_ext_person.g_contact_nationality := null;
43 ben_ext_person.g_contact_email_address := null;
44 ben_ext_person.g_contact_known_as := null;
45 ben_ext_person.g_contact_pre_name_adjunct := null;
46 ben_ext_person.g_contact_tobacco_usage := null;
47 ben_ext_person.g_contact_prev_last_name := null;
48 ben_ext_person.g_contact_prim_address1 := null;
49 ben_ext_person.g_contact_prim_address2 := null;
50 ben_ext_person.g_contact_prim_address3 := null;
51 ben_ext_person.g_contact_prim_city := null;
52 ben_ext_person.g_contact_prim_state := null;
53 ben_ext_person.g_contact_prim_postal_code := null;
54 ben_ext_person.g_contact_prim_country := null;
55 ben_ext_person.g_contact_prim_effect_date := null;
56 ben_ext_person.g_contact_prim_region := null;
57 ben_ext_person.g_contact_home_phone := null;
58 ben_ext_person.g_contact_work_phone := null;
59 ben_ext_person.g_contact_fax := null;
60 ben_ext_person.g_contact_mobile := null;
61 ben_ext_person.g_contact_prmy_contact_flag := null;
62 ben_ext_person.g_contact_shared_resd_flag := null;
63 ben_ext_person.g_contact_personal_flag := null;
64 ben_ext_person.g_contact_pymts_rcpnt_flag := null;
65 ben_ext_person.g_contact_start_date := null;
66 ben_ext_person.g_contact_end_date := null;
67 ben_ext_person.g_contact_start_life_evt := null;
68 ben_ext_person.g_contact_start_ler_id := null;
69 ben_ext_person.g_contact_end_life_evt := null;
70 ben_ext_person.g_contact_end_ler_id := null;
71 ben_ext_person.g_contact_is_elig_dpnt_flag := null;
72 ben_ext_person.g_contact_is_cvrd_dpnt_flag := null;
73 ben_ext_person.g_contact_is_bnfcry_flag := null;
74 --
75 --
76 hr_utility.set_location('Exiting'||l_proc, 15);
77 --
78 End initialize_globals;
79 --
80 -- ----------------------------------------------------------------------------
81 -- |---------------------< main >---------------------------------------------|
82 -- ----------------------------------------------------------------------------
83 PROCEDURE main
84 ( p_person_id in number,
85 p_ext_rslt_id in number,
86 p_ext_file_id in number,
87 p_data_typ_cd in varchar2,
88 p_ext_typ_cd in varchar2,
89 p_chg_evt_cd in varchar2,
90 p_business_group_id in number,
91 p_effective_date in date) is
92 --
93 l_proc varchar2(72) := g_package||'main';
94 --
95 cursor c_contact is
96 select
97 b.contact_relationship_id
98 , b.contact_person_id
99 , b.contact_type
100 , b.sequence_number
101 , b.PRIMARY_CONTACT_FLAG
102 , b.RLTD_PER_RSDS_W_DSGNTR_FLAG
103 , b.PERSONAL_FLAG
104 , b.THIRD_PARTY_PAY_FLAG
105 , b.DATE_START
106 , b.DATE_END
107 , b.start_life_reason_id
108 , b.end_life_reason_id
109 , c.last_name
110 , c.correspondence_language
111 , c.date_of_birth
112 , c.email_address
113 , c.first_name
114 , c.full_name
115 , c.marital_status
116 , c.middle_names
117 , c.nationality
118 , c.national_identifier
119 , c.registered_disabled_flag
120 , c.sex
121 , c.student_status
122 , c.suffix
123 , c.pre_name_adjunct prefix
124 , c.title
125 , c.date_of_death
126 , c.known_as
127 , c.pre_name_adjunct
128 , c.uses_tobacco_flag
129 , c.previous_last_name
130 from
131 per_contact_relationships b,
132 per_all_people_f c
133 where
134 c.person_id = b.contact_person_id
135 and b.person_id = p_person_id
136 and p_effective_date between c.effective_start_date
137 and c.effective_end_date
138 ;
139
140 --
141 cursor prim_address_c(p_contact_person_id number) is
142 select
143 a.address_line1
144 , a.address_line2
145 , a.address_line3
146 , a.town_or_city
147 , a.region_2
148 , a.postal_code
149 , a.country
150 , a.date_from
151 , a.region_3
152 from per_addresses a
153 where
154 a.person_id = p_contact_person_id
155 and p_effective_date between nvl(a.date_from, p_effective_date)
156 and nvl(a.date_to, p_effective_date)
157 and a.primary_flag = 'Y'
158 ;
159 --
160 cursor prim_rltd_address_c(p_contact_person_id number) is
161 select
162 a.address_line1
163 , a.address_line2
164 , a.address_line3
165 , a.town_or_city
166 , a.region_2
167 , a.postal_code
168 , a.country
169 , a.date_from
170 , a.region_3
171 from per_addresses a,
172 per_contact_relationships r
173 where
174 r.contact_person_id = p_contact_person_id
175 and r.person_id = p_person_id
176 and r.person_id = a.person_id
177 and p_effective_date between nvl(a.date_from, p_effective_date)
178 and nvl(a.date_to, p_effective_date)
179 and a.primary_flag = 'Y'
180 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
181 ;
182 --
183 cursor ext_phone_c (p_contact_person_id number) is
184 select
185 h.phone_number phone_home
186 , w.phone_number phone_work
187 , f.phone_number phone_fax
188 , m.phone_number phone_mobile
189 from per_all_people_f p
190 , per_phones h
191 , per_phones w
192 , per_phones f
193 , per_phones m
194 where p.person_id = p_contact_person_id
195 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
196 and nvl(p.effective_end_date, p_effective_date)
197 and h.parent_id (+) = p.person_id
198 and w.parent_id (+) = p.person_id
199 and f.parent_id (+) = p.person_id
200 and m.parent_id (+) = p.person_id
201 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
202 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
203 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
204 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
205 and h.phone_type (+) = 'H1'
206 and w.phone_type (+) = 'W1'
207 and f.phone_type (+) = 'WF'
208 and m.phone_type (+) = 'M'
209 and p_effective_date between nvl(h.date_from, p_effective_date)
210 and nvl(h.date_to, p_effective_date)
211 and p_effective_date between nvl(w.date_from, p_effective_date)
212 and nvl(w.date_to, p_effective_date)
213 and p_effective_date between nvl(f.date_from, p_effective_date)
214 and nvl(f.date_to, p_effective_date)
215 and p_effective_date between nvl(m.date_from, p_effective_date)
216 and nvl(m.date_to, p_effective_date)
217 ;
218
219 --
220 -- Return 'Y' if the contact is currently an eligible dependent in any
221 -- Comp object for the related person.
222 --
223 cursor c_elig_dpnt (p_contact_person_id number) is
224 select 'Y'
225 from ben_elig_dpnt egd,
226 ben_elig_per_elctbl_chc epe,
227 ben_per_in_ler pil
228 where egd.dpnt_person_id = p_contact_person_id
229 and pil.person_id = p_person_id
230 and pil.per_in_ler_id = epe.per_in_ler_id
231 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
232 and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
233 and p_effective_date between egd.elig_strt_dt and egd.elig_thru_dt;
234 --
235 -- Return 'Y' if the contact is currently a covered dependent in any
236 -- Comp object for the related person.
237 --
238 cursor c_cvrd_dpnt (p_contact_person_id number) is
239 select 'Y'
240 from ben_elig_cvrd_dpnt_f pdp,
241 ben_prtt_enrt_rslt_f pen
242 where pdp.dpnt_person_id = p_contact_person_id
243 and pen.person_id = p_person_id
244 and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
245 and pen.sspndd_flag = 'N'
246 and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
247 and p_effective_date between pdp.cvg_strt_dt and pdp.cvg_thru_dt
248 and p_effective_date between pdp.effective_start_date and pdp.effective_end_date
249 and p_effective_date between pen.effective_start_date and pen.effective_end_date;
250 --
251 -- Return 'Y' if the contact is currently a beneficiary in any
252 -- Comp object for the related person.
253 --
254 cursor c_beneficiary (p_contact_person_id number) is
255 select 'Y'
256 from ben_pl_bnf_f pbn,
257 ben_prtt_enrt_rslt_f pen
258 where pbn.bnf_person_id = p_contact_person_id
259 and pen.person_id = p_person_id
260 and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
261 and pen.sspndd_flag = 'N'
262 and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
263 and p_effective_date between pbn.dsgn_strt_dt and pbn.dsgn_thru_dt
264 and p_effective_date between pbn.effective_start_date and pbn.effective_end_date
265 and p_effective_date between pen.effective_start_date and pen.effective_end_date;
266
267 cursor c_start_ler (p_start_life_reason_id number) is
268 select name
269 from ben_ler_f ler
270 where ler_id = p_start_life_reason_id
271 and p_effective_date between ler.effective_start_date and ler.effective_end_date;
272
273 cursor c_end_ler (p_end_life_reason_id number) is
274 select name
275 from ben_ler_f ler
276 where ler_id = p_end_life_reason_id
277 and p_effective_date between ler.effective_start_date and ler.effective_end_date;
278
279 --
280 BEGIN
281 --
282 hr_utility.set_location('Entering'||l_proc, 5);
283 --
284 FOR contact IN c_contact LOOP
285 --
286 initialize_globals;
287 --
288 -- fetch dependent information into globals
289 --
290 ben_ext_person.g_contact_rlshp_id := contact.contact_relationship_id;
291 ben_ext_person.g_contact_rlshp_type := contact.contact_type;
292 ben_ext_person.g_contact_seq_num := contact.sequence_number;
293 ben_ext_person.g_contact_prmy_contact_flag := contact.PRIMARY_CONTACT_FLAG;
294 ben_ext_person.g_contact_shared_resd_flag := contact.RLTD_PER_RSDS_W_DSGNTR_FLAG;
295 ben_ext_person.g_contact_personal_flag := contact.PERSONAL_FLAG;
296 ben_ext_person.g_contact_pymts_rcpnt_flag := contact.THIRD_PARTY_PAY_FLAG;
297 ben_ext_person.g_contact_start_date := contact.DATE_START;
298 ben_ext_person.g_contact_end_date := contact.DATE_END;
299 ben_ext_person.g_contact_national_ident := contact.national_identifier;
300 ben_ext_person.g_contact_last_name := contact.last_name;
301 ben_ext_person.g_contact_first_name := contact.first_name;
302 ben_ext_person.g_contact_middle_names := contact.middle_names;
303 ben_ext_person.g_contact_full_name := contact.full_name;
304 ben_ext_person.g_contact_suffix := contact.suffix;
305 ben_ext_person.g_contact_prefix := contact.prefix;
306 ben_ext_person.g_contact_title := contact.title;
307 ben_ext_person.g_contact_date_of_birth := contact.date_of_birth;
308 ben_ext_person.g_contact_marital_status := contact.marital_status;
309 ben_ext_person.g_contact_sex := contact.sex;
310 ben_ext_person.g_contact_disabled_flag := contact.registered_disabled_flag;
311 ben_ext_person.g_contact_student_status := contact.student_status;
312 ben_ext_person.g_contact_date_of_death := contact.date_of_death;
313 ben_ext_person.g_contact_language := contact.correspondence_language;
314 ben_ext_person.g_contact_nationality := contact.nationality;
315 ben_ext_person.g_contact_email_address := contact.email_address;
316 ben_ext_person.g_contact_known_as := contact.known_as;
317 ben_ext_person.g_contact_pre_name_adjunct := contact.pre_name_adjunct;
318 ben_ext_person.g_contact_tobacco_usage := contact.uses_tobacco_flag;
319 ben_ext_person.g_contact_prev_last_name := contact.previous_last_name;
320 ben_ext_person.g_contact_start_ler_id := contact.start_life_reason_id;
321 ben_ext_person.g_contact_end_ler_id := contact.end_life_reason_id;
322 --
323 -- retrieve dependent address info if required
324 if ben_extract.g_coa_csr = 'Y' then
325 open prim_address_c (contact.contact_person_id);
326 fetch prim_address_c into ben_ext_person.g_contact_prim_address1
327 ,ben_ext_person.g_contact_prim_address2
328 ,ben_ext_person.g_contact_prim_address3
329 ,ben_ext_person.g_contact_prim_city
330 ,ben_ext_person.g_contact_prim_state
331 ,ben_ext_person.g_contact_prim_postal_code
332 ,ben_ext_person.g_contact_prim_country
333 ,ben_ext_person.g_contact_prim_effect_date
334 ,ben_ext_person.g_contact_prim_region;
335 if prim_address_c%notfound then
336 open prim_rltd_address_c(contact.contact_person_id);
337 fetch prim_rltd_address_c into ben_ext_person.g_contact_prim_address1
338 , ben_ext_person.g_contact_prim_address2
339 , ben_ext_person.g_contact_prim_address3
340 , ben_ext_person.g_contact_prim_city
341 , ben_ext_person.g_contact_prim_state
342 , ben_ext_person.g_contact_prim_postal_code
343 , ben_ext_person.g_contact_prim_country
344 , ben_ext_person.g_contact_prim_effect_date
345 , ben_ext_person.g_contact_prim_region;
346 close prim_rltd_address_c;
347 end if;
348 close prim_address_c;
349 end if;
350 --
351 -- retrieve dependent phone numbers if required
352 if ben_extract.g_cop_csr = 'Y' then
353 open ext_phone_c(contact.contact_person_id);
354 fetch ext_phone_c into ben_ext_person.g_contact_home_phone
355 ,ben_ext_person.g_contact_work_phone
356 ,ben_ext_person.g_contact_fax
357 ,ben_ext_person.g_contact_mobile;
358 close ext_phone_c;
359 end if;
360
361 -- retrieve eligible dependent flag
362 if ben_extract.g_coed_csr = 'Y' then
363 ben_ext_person.g_contact_is_elig_dpnt_flag := 'N';
364 open c_elig_dpnt(contact.contact_person_id);
365 fetch c_elig_dpnt into ben_ext_person.g_contact_is_elig_dpnt_flag;
366 close c_elig_dpnt;
367 end if;
368
369 -- retrieve covered dependent flag
370 if ben_extract.g_cocd_csr = 'Y' then
371 ben_ext_person.g_contact_is_cvrd_dpnt_flag := 'N';
372 open c_cvrd_dpnt(contact.contact_person_id);
373 fetch c_cvrd_dpnt into ben_ext_person.g_contact_is_cvrd_dpnt_flag;
374 close c_cvrd_dpnt;
375 end if;
376
377 -- retrieve beneficiary flag
378 if ben_extract.g_cob_csr = 'Y' then
379 ben_ext_person.g_contact_is_bnfcry_flag := 'N';
380 open c_beneficiary(contact.contact_person_id);
381 fetch c_beneficiary into ben_ext_person.g_contact_is_bnfcry_flag;
382 close c_beneficiary;
383 end if;
384 --
385 -- retrieve life reason start
386 if ben_extract.g_cosl_csr = 'Y' then
387 open c_start_ler(contact.start_life_reason_id);
388 fetch c_start_ler into ben_ext_person.g_contact_start_life_evt;
389 close c_start_ler;
390 end if;
391 --
392 -- retrieve life reason end
393 if ben_extract.g_coel_csr = 'Y' then
394 open c_end_ler(contact.end_life_reason_id);
395 fetch c_end_ler into ben_ext_person.g_contact_end_life_evt;
396 close c_end_ler;
397 end if;
398 -- format and write
399 --
400 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
401 p_ext_file_id => p_ext_file_id,
402 p_data_typ_cd => p_data_typ_cd,
403 p_ext_typ_cd => p_ext_typ_cd,
404 p_rcd_typ_cd => 'D',
405 p_low_lvl_cd => 'CO',--contact
406 p_person_id => p_person_id,
407 p_chg_evt_cd => p_chg_evt_cd,
408 p_business_group_id => p_business_group_id,
409 p_effective_date => p_effective_date
410 );
411 --
412 END LOOP;
413 --
414 hr_utility.set_location('Exiting'||l_proc, 15);
415
416 END; -- main
417 --
418 END; -- package