[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_BNF
Source
1 Package Body BEN_EXT_BNF AS
2 /* $Header: benxbenf.pkb 120.0 2005/05/28 09:38:02 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_bnf.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< init_detl_globals >-----------------------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 Procedure init_detl_globals IS
15 --
16 l_proc varchar2(72) := g_package||'init_detl_globals';
17 --
18 Begin
19 --
20 hr_utility.set_location('Entering'||l_proc, 5);
21 --
22 ben_ext_person.g_bnf_pl_bnf_id := null;
23 ben_ext_person.g_bnf_ssn := null;
24 ben_ext_person.g_bnf_lst_nm := null;
25 ben_ext_person.g_bnf_fst_nm := null;
26 ben_ext_person.g_bnf_mid_nm := null;
27 ben_ext_person.g_bnf_fl_nm := null;
28 ben_ext_person.g_bnf_suffix := null;
29 ben_ext_person.g_bnf_prefix := null;
30 ben_ext_person.g_bnf_title := null;
31 ben_ext_person.g_bnf_prv_lst_nm := null;
32 ben_ext_person.g_bnf_pre_nm_adjunct := null;
33 ben_ext_person.g_bnf_prmy_cont := null;
34 ben_ext_person.g_bnf_pct_dsgd := null;
35 ben_ext_person.g_bnf_amt_dsgd := null;
36 ben_ext_person.g_bnf_amt_uom := null;
37 ben_ext_person.g_bnf_rlshp := null;
38 ben_ext_person.g_bnf_contact_seq_num := null;
39 ben_ext_person.g_bnf_shared_resd_flag := null;
40 ben_ext_person.g_bnf_email_address := null;
41 ben_ext_person.g_bnf_known_as := null;
42 ben_ext_person.g_bnf_nationality := null;
43 ben_ext_person.g_bnf_tobacco_usage := null;
44 ben_ext_person.g_bnf_gender := null;
45 ben_ext_person.g_bnf_date_of_birth := null;
46 ben_ext_person.g_bnf_marital_status := null;
47 ben_ext_person.g_bnf_disabled_flag := null;
48 ben_ext_person.g_bnf_student_status := null;
49 ben_ext_person.g_bnf_date_of_death := null;
50 ben_ext_person.g_bnf_language := null;
51 ben_ext_person.g_bnf_prim_address1 := null;
52 ben_ext_person.g_bnf_prim_address2 := null;
53 ben_ext_person.g_bnf_prim_address3 := null;
54 ben_ext_person.g_bnf_prim_city := null;
55 ben_ext_person.g_bnf_prim_state := null;
56 ben_ext_person.g_bnf_prim_postal_code := null;
57 ben_ext_person.g_bnf_prim_country := null;
58 ben_ext_person.g_bnf_prim_effect_date := null;
59 ben_ext_person.g_bnf_prim_region := null;
60 ben_ext_person.g_bnf_home_phone := null;
61 ben_ext_person.g_bnf_work_phone := null;
62 ben_ext_person.g_bnf_fax := null;
63 ben_ext_person.g_bnf_mobile := null;
64
65 --
66 hr_utility.set_location('Exiting'||l_proc, 15);
67 --
68 End init_detl_globals;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |--------------< main >-----------------------------------------------------|
72 -- ----------------------------------------------------------------------------
73 --
74 Procedure main
75 ( p_person_id in number,
76 p_prtt_enrt_rslt_id in number,
77 p_ext_rslt_id in number,
78 p_ext_file_id in number,
79 p_data_typ_cd in varchar2,
80 p_ext_typ_cd in varchar2,
81 p_chg_evt_cd in varchar2,
82 p_business_group_id in number,
83 p_effective_date in date) is
84 --
85 l_relatated_true varchar2(1) := 'N' ;
86 l_proc varchar2(72) := g_package||'main';
87 --
88 cursor c_bnfcry (l_prtt_enrt_rslt_id number) is
89 select
90 b.contact_type bnf_rlshp
91 , b.sequence_number bnf_contact_seq_num
92 , b.rltd_per_rsds_w_dsgntr_flag
93 , b.contact_person_id bnf_person_id
94 , c.last_name bnf_lst_nm
95 , c.first_name bnf_fst_nm
96 , c.full_name bnf_fl_nm
97 , c.middle_names bnf_mid_nm
98 , c.national_identifier bnf_ssn
99 , c.suffix bnf_suffix
100 , c.pre_name_adjunct bnf_prefix
101 , c.title bnf_title
102 , c.previous_last_name bnf_prv_lst_nm
103 , c.pre_name_adjunct bnf_pre_nm_adjunct
104 , c.email_address bnf_email
105 , c.known_as bnf_known_as
106 , c.nationality bnf_nationality
107 , c.uses_tobacco_flag bnf_tobacco_usage
108 , c.sex bnf_gender
109 , c.date_of_birth bnf_dob
110 , c.marital_status bnf_marital_status
111 , c.registered_disabled_flag bnf_disabled_flag
112 , c.student_status bnf_student_status
113 , c.date_of_death bnf_dod
114 , c.correspondence_language bnf_language
115 , a.prmry_cntngnt_cd bnf_prmy_cont
116 , a.pct_dsgd_num bnf_pct_dsgd
117 , a.amt_dsgd_val bnf_amt_dsgd
118 , a.amt_dsgd_uom bnf_amt_uom
119 , a.pl_bnf_id pl_bnf_id
120 from ben_pl_bnf_f a,
121 ben_per_in_ler pil,
122 per_contact_relationships b,
123 per_all_people_f c
124 where
125 a.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
126 and a.bnf_person_id = b.contact_person_id
127 and b.person_id = p_person_id
128 and c.person_id = a.bnf_person_id
129 and p_effective_date between nvl(a.dsgn_strt_dt, p_effective_date)
130 and nvl(a.dsgn_thru_dt, p_effective_date)
131 and p_effective_date between nvl(a.effective_start_date, p_effective_date)
132 and nvl(a.effective_end_date, p_effective_date)
133 and p_effective_date between nvl(c.effective_start_date, p_effective_date)
134 and nvl(c.effective_end_date, p_effective_date)
135 and pil.per_in_ler_id=a.per_in_ler_id
136 -- and pil.business_group_id+0=a.business_group_id
137 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
138
139 ;
140
141 --
142 --
143
144 cursor prim_address_c (c_person_id number) is
145 select
146 a.address_line1
147 , a.address_line2
148 , a.address_line3
149 , a.town_or_city
150 , a.region_2
151 , a.postal_code
152 , a.country
153 , a.date_from
154 , a.region_3
155 from per_addresses a
156 where
157 a.person_id = c_person_id
158 and p_effective_date between nvl(a.date_from, p_effective_date)
159 and nvl(a.date_to, p_effective_date)
160 and a.primary_flag = 'Y'
161 ;
162 --
163 cursor prim_rltd_address_c(c_person_id number) is
164 select
165 a.address_line1
166 , a.address_line2
167 , a.address_line3
168 , a.town_or_city
169 , a.region_2
170 , a.postal_code
171 , a.country
172 , a.date_from
173 , a.region_3
174 from per_addresses a,
175 per_contact_relationships r
176 where
177 r.contact_person_id = c_person_id
178 and r.person_id = a.person_id
179 and p_effective_date between nvl(a.date_from, p_effective_date)
180 and nvl(a.date_to, p_effective_date)
181 and a.primary_flag = 'Y'
182 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
183 ;
184 --
185 --
186 cursor ext_phone_c (c_person_id number) is
187 select
188 h.phone_number phone_home
189 , w.phone_number phone_work
190 , f.phone_number phone_fax
191 , m.phone_number phone_mobile
192 from per_all_people_f p
193 , per_phones h
194 , per_phones w
195 , per_phones f
196 , per_phones m
197 where p.person_id = c_person_id
198 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
199 and nvl(p.effective_end_date, p_effective_date)
200 and h.parent_id (+) = p.person_id
201 and w.parent_id (+) = p.person_id
202 and f.parent_id (+) = p.person_id
203 and m.parent_id (+) = p.person_id
204 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
205 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
206 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
207 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
208 and h.phone_type (+) = 'H1'
209 and w.phone_type (+) = 'W1'
210 and f.phone_type (+) = 'WF'
211 and m.phone_type (+) = 'M'
212 and p_effective_date between nvl(h.date_from, p_effective_date)
213 and nvl(h.date_to, p_effective_date)
214 and p_effective_date between nvl(w.date_from, p_effective_date)
215 and nvl(w.date_to, p_effective_date)
216 and p_effective_date between nvl(f.date_from, p_effective_date)
217 and nvl(f.date_to, p_effective_date)
218 and p_effective_date between nvl(m.date_from, p_effective_date)
219 and nvl(m.date_to, p_effective_date)
220 ;
221 --
222 cursor ext_related_phone_c (c_person_id number) is
223 select
224 h.phone_number phone_home
225 , w.phone_number phone_work
226 , f.phone_number phone_fax
227 , m.phone_number phone_mobile
228 from per_all_people_f p
229 , per_phones h
230 , per_phones w
231 , per_phones f
232 , per_phones m
233 , per_contact_relationships r
234 where r.contact_person_id = c_person_id
235 and p.person_id = r.person_id
236 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
237 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
238 and nvl(p.effective_end_date, p_effective_date)
239 and h.parent_id (+) = p.person_id
240 and w.parent_id (+) = p.person_id
241 and f.parent_id (+) = p.person_id
242 and m.parent_id (+) = p.person_id
243 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
244 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
245 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
246 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
247 and h.phone_type (+) = 'H1'
248 and w.phone_type (+) = 'W1'
249 and f.phone_type (+) = 'WF'
250 and m.phone_type (+) = 'M'
251 and p_effective_date between nvl(h.date_from, p_effective_date)
252 and nvl(h.date_to, p_effective_date)
253 and p_effective_date between nvl(w.date_from, p_effective_date)
254 and nvl(w.date_to, p_effective_date)
255 and p_effective_date between nvl(f.date_from, p_effective_date)
256 and nvl(f.date_to, p_effective_date)
257 and p_effective_date between nvl(m.date_from, p_effective_date)
258 and nvl(m.date_to, p_effective_date)
259 ;
260
261 Begin
262 --
263 hr_utility.set_location('Entering'||l_proc, 5);
264 --
265 init_detl_globals;
266 --
267 FOR bnfcry IN c_bnfcry(p_prtt_enrt_rslt_id) LOOP
268 -- assign beneficiary info to global variables
269 --
270 ben_ext_person.g_bnf_ssn := bnfcry.bnf_ssn;
271 ben_ext_person.g_bnf_lst_nm := bnfcry.bnf_lst_nm;
272 ben_ext_person.g_bnf_fst_nm := bnfcry.bnf_fst_nm;
273 ben_ext_person.g_bnf_mid_nm := bnfcry.bnf_mid_nm;
274 ben_ext_person.g_bnf_fl_nm := bnfcry.bnf_fl_nm;
275 ben_ext_person.g_bnf_suffix := bnfcry.bnf_suffix;
276 ben_ext_person.g_bnf_prefix := bnfcry.bnf_prefix;
277 ben_ext_person.g_bnf_title := bnfcry.bnf_title;
278 ben_ext_person.g_bnf_prv_lst_nm := bnfcry.bnf_prv_lst_nm;
279 ben_ext_person.g_bnf_pre_nm_adjunct := bnfcry.bnf_pre_nm_adjunct;
280 ben_ext_person.g_bnf_email_address := bnfcry.bnf_email;
281 ben_ext_person.g_bnf_known_as := bnfcry.bnf_known_as;
282 ben_ext_person.g_bnf_nationality := bnfcry.bnf_nationality;
283 ben_ext_person.g_bnf_tobacco_usage := bnfcry.bnf_tobacco_usage;
284 ben_ext_person.g_bnf_gender := bnfcry.bnf_gender;
285 ben_ext_person.g_bnf_date_of_birth := bnfcry.bnf_dob;
286 ben_ext_person.g_bnf_marital_status := bnfcry.bnf_marital_status;
287 ben_ext_person.g_bnf_disabled_flag := bnfcry.bnf_disabled_flag;
288 ben_ext_person.g_bnf_student_status := bnfcry.bnf_student_status;
289 ben_ext_person.g_bnf_date_of_death := bnfcry.bnf_dod;
290 ben_ext_person.g_bnf_language := bnfcry.bnf_language;
291 ben_ext_person.g_bnf_prmy_cont := bnfcry.bnf_prmy_cont;
292 ben_ext_person.g_bnf_pct_dsgd := bnfcry.bnf_pct_dsgd;
293 ben_ext_person.g_bnf_amt_dsgd := bnfcry.bnf_amt_dsgd;
294 ben_ext_person.g_bnf_amt_uom := bnfcry.bnf_amt_uom;
295 ben_ext_person.g_bnf_rlshp := bnfcry.bnf_rlshp;
296 ben_ext_person.g_bnf_contact_seq_num := bnfcry.bnf_contact_seq_num;
297 ben_ext_person.g_bnf_shared_resd_flag := bnfcry.rltd_per_rsds_w_dsgntr_flag;
298 ben_ext_person.g_bnf_pl_bnf_id := bnfcry.pl_bnf_id;
299
300 --
301 hr_utility.set_location('Beneficiary id ' || bnfcry.bnf_person_id,178);
302 --
303 -- retrieve beneficiary address if required
304 l_relatated_true := 'N' ;
305 if ben_extract.g_ba_csr = 'Y' then
306 open prim_address_c(bnfcry.bnf_person_id);
307 fetch prim_address_c into ben_ext_person.g_bnf_prim_address1
308 , ben_ext_person.g_bnf_prim_address2
309 , ben_ext_person.g_bnf_prim_address3
310 , ben_ext_person.g_bnf_prim_city
311 , ben_ext_person.g_bnf_prim_state
312 , ben_ext_person.g_bnf_prim_postal_code
313 , ben_ext_person.g_bnf_prim_country
314 , ben_ext_person.g_bnf_prim_effect_date
315 , ben_ext_person.g_bnf_prim_region;
316 if prim_address_c%notfound then
317 open prim_rltd_address_c(bnfcry.bnf_person_id);
318 fetch prim_rltd_address_c into ben_ext_person.g_bnf_prim_address1
319 , ben_ext_person.g_bnf_prim_address2
320 , ben_ext_person.g_bnf_prim_address3
321 , ben_ext_person.g_bnf_prim_city
322 , ben_ext_person.g_bnf_prim_state
323 , ben_ext_person.g_bnf_prim_postal_code
324 , ben_ext_person.g_bnf_prim_country
325 , ben_ext_person.g_bnf_prim_effect_date
326 , ben_ext_person.g_bnf_prim_region;
327 close prim_rltd_address_c;
328 l_relatated_true := 'Y' ;
329 end if;
330 close prim_address_c;
331 end if;
332 --
333 -- retrieve beneficiary phone numbers if required
334
335 if ben_extract.g_bp_csr = 'Y' then
336 if l_relatated_true = 'N' then
337 open ext_phone_c(bnfcry.bnf_person_id);
338 fetch ext_phone_c into ben_ext_person.g_bnf_home_phone
339 , ben_ext_person.g_bnf_work_phone
340 , ben_ext_person.g_bnf_fax
341 , ben_ext_person.g_bnf_mobile;
342 close ext_phone_c;
343 else
344
345 open ext_related_phone_c(bnfcry.bnf_person_id);
346 fetch ext_related_phone_c into ben_ext_person.g_bnf_home_phone
347 , ben_ext_person.g_bnf_work_phone
348 , ben_ext_person.g_bnf_fax
349 , ben_ext_person.g_bnf_mobile;
350 hr_utility.set_location('sec_ph'|| ben_ext_person.g_bnf_home_phone,178);
351 close ext_related_phone_c;
352 end if ;
353 end if ;
354 --
355 --
356 -- format and write
357 --
358 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
359 p_ext_file_id => p_ext_file_id,
360 p_data_typ_cd => p_data_typ_cd,
361 p_ext_typ_cd => p_ext_typ_cd,
362 p_rcd_typ_cd => 'D', --detail
363 p_low_lvl_cd => 'B', --beneficiary?
364 p_person_id => p_person_id,
365 p_chg_evt_cd => p_chg_evt_cd,
366 p_business_group_id => p_business_group_id,
367 p_effective_date => p_effective_date
368 );
369 --
370 END LOOP;
371 --
372 hr_utility.set_location('Exiting'||l_proc, 15);
373 --
374 END; --main
375 --
376 END; -- package