[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_ELIG_DPNT
Source
1 package body BEN_EXT_ELIG_DPNT as
2 /* $Header: benxeldp.pkb 120.2 2007/07/16 23:46:52 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_elig_dpnt.'; -- Global package name
9
10
11 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12 TYPE t_varchar2_30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
13 TYPE t_varchar2_600 IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
14 TYPE t_date IS TABLE OF Date INDEX BY BINARY_INTEGER;
15
16 --
17 -- procedure to initialize the globals - May, 99
18 -- ----------------------------------------------------------------------------
19 -- |---------------------< initialize_globals >-------------------------------|
20 -- ----------------------------------------------------------------------------
21 PROCEDURE initialize_globals IS
22 --
23 l_proc varchar2(72) := g_package||'initialize_globals';
24 --
25 Begin
26 --
27 --
28 hr_utility.set_location('Entering'||l_proc, 5);
29 --
30 --
31 ben_ext_person.g_elig_dpnt_elig_strt_dt := null;
32 ben_ext_person.g_elig_dpnt_elig_thru_dt := null;
33 ben_ext_person.g_elig_dpnt_create_dt := null;
34 ben_ext_person.g_elig_dpnt_ovrdn_flag := null;
35 ben_ext_person.g_elig_dpnt_ovrdn_thru_dt := null;
36 ben_ext_person.g_elig_dpnt_rlshp_type := null;
37 ben_ext_person.g_elig_dpnt_contact_seq_num := null;
38 ben_ext_person.g_elig_dpnt_shared_resd_flag := null;
39 ben_ext_person.g_elig_dpnt_national_ident := null;
40 ben_ext_person.g_elig_dpnt_last_name := null;
41 ben_ext_person.g_elig_dpnt_first_name := null;
42 ben_ext_person.g_elig_dpnt_middle_names := null;
43 ben_ext_person.g_elig_dpnt_full_name := null;
44 ben_ext_person.g_elig_dpnt_suffix := null;
45 ben_ext_person.g_elig_dpnt_title := null;
46 ben_ext_person.g_elig_dpnt_date_of_birth := null;
47 ben_ext_person.g_elig_dpnt_marital_status := null;
48 ben_ext_person.g_elig_dpnt_sex := null;
49 ben_ext_person.g_elig_dpnt_disabled_flag := null;
50 ben_ext_person.g_elig_dpnt_student_status := null;
51 ben_ext_person.g_elig_dpnt_date_of_death := null;
52 ben_ext_person.g_elig_dpnt_language := null;
53 ben_ext_person.g_elig_dpnt_nationality := null;
54 ben_ext_person.g_elig_dpnt_email_address := null;
55 ben_ext_person.g_elig_dpnt_known_as := null;
56 ben_ext_person.g_elig_dpnt_pre_name_adjunct := null;
57 ben_ext_person.g_elig_dpnt_tobacco_usage := null;
58 ben_ext_person.g_elig_dpnt_prev_last_name := null;
59 ben_ext_person.g_elig_dpnt_prim_address1 := null;
60 ben_ext_person.g_elig_dpnt_prim_address2 := null;
61 ben_ext_person.g_elig_dpnt_prim_address3 := null;
62 ben_ext_person.g_elig_dpnt_prim_city := null;
63 ben_ext_person.g_elig_dpnt_prim_state := null;
64 ben_ext_person.g_elig_dpnt_prim_postal_code := null;
65 ben_ext_person.g_elig_dpnt_prim_country := null;
66 ben_ext_person.g_elig_dpnt_prim_effect_date := null;
67 ben_ext_person.g_elig_dpnt_prim_region := null;
68 ben_ext_person.g_elig_dpnt_home_phone := null;
69 ben_ext_person.g_elig_dpnt_work_phone := null;
70 ben_ext_person.g_elig_dpnt_fax := null;
71 ben_ext_person.g_elig_dpnt_mobile := null;
72 ben_ext_person.g_elig_dpnt_id := null;
73 --
74 --
75 hr_utility.set_location('Exiting'||l_proc, 15);
76 --
77 End initialize_globals;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |---------------------< main >---------------------------------------------|
81 -- ----------------------------------------------------------------------------
82 PROCEDURE main
83 ( p_person_id in number,
84 p_elig_per_elctbl_chc_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 -- Declare bulk bind variable
95 l_dpel_create_dt_va t_date ;
96 l_dpel_elig_cvrd_dpnt_id_va t_number ;
97 l_dpel_elig_strt_dt_va t_date ;
98 l_dpel_elig_thru_dt_va t_date ;
99 l_dpel_ovrdn_flag_va t_varchar2_30 ;
100 l_dpel_ovrdn_thru_dt_va t_date ;
101 l_dpel_dpnt_person_id_va t_number ;
102 l_dpel_elig_dpnt_id_va t_number ;
103 l_dpel_contact_type_va t_varchar2_30 ;
104 l_dpel_sequence_number_va t_number ;
105 l_dpel_rltd_per_dsgntr_flag_va t_varchar2_30 ;
106 l_dpel_last_name_va t_varchar2_600 ;
107 l_dpel_correspondence_lang_va t_varchar2_600 ;
108 l_dpel_date_of_birth_va t_date ;
109 l_dpel_email_address_va t_varchar2_600 ;
110 l_dpel_first_name_va t_varchar2_600 ;
111 l_dpel_full_name_va t_varchar2_600 ;
112 l_dpel_marital_status_va t_varchar2_30 ;
113 l_dpel_middle_names_va t_varchar2_600 ;
114 l_dpel_nationality_va t_varchar2_600 ;
115 l_dpel_national_identifier_va t_varchar2_30 ;
116 l_dpel_disabled_flag_va t_varchar2_30 ;
117 l_dpel_sex_va t_varchar2_30 ;
118 l_dpel_student_status_va t_varchar2_30 ;
119 l_dpel_suffix_va t_varchar2_30 ;
120 l_dpel_title_va t_varchar2_30 ;
121 l_dpel_date_of_death_va t_date ;
122 l_dpel_known_as_va t_varchar2_600 ;
123 l_dpel_pre_name_adjunct_va t_varchar2_30 ;
124 l_dpel_uses_tobacco_flag_va t_varchar2_30 ;
125 l_dpel_previous_last_name_va t_varchar2_600 ;
126 --
127
128 --
129 cursor c_dpnt (l_elig_per_elctbl_chc_id number) is
130 select
131 a.create_dt
132 , a.elig_cvrd_dpnt_id
133 , a.elig_strt_dt
134 , a.elig_thru_dt
135 , a.ovrdn_flag
136 , a.ovrdn_thru_dt
137 , a.dpnt_person_id
138 , a.elig_dpnt_id
139 , b.contact_type
140 , b.sequence_number
141 , b.rltd_per_rsds_w_dsgntr_flag
142 , c.last_name
143 , c.correspondence_language
144 , c.date_of_birth
145 , c.email_address
146 , c.first_name
147 , c.full_name
148 , c.marital_status
149 , c.middle_names
150 , c.nationality
151 , c.national_identifier
152 , c.registered_disabled_flag
153 , c.sex
154 , c.student_status
155 , c.suffix
156 , c.title
157 , c.date_of_death
158 , c.known_as
159 , c.pre_name_adjunct
160 , c.uses_tobacco_flag
161 , c.previous_last_name
162 from ben_elig_dpnt a,
163 per_contact_relationships b,
164 per_all_people_f c
165 where
166 a.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
167 and a.dpnt_inelig_flag = 'N'
168 and a.dpnt_person_id = b.contact_person_id
169 and b.person_id = p_person_id
170 and c.person_id = a.dpnt_person_id
171 and p_effective_date between c.effective_start_date
172 and c.effective_end_date
173 and p_effective_date between b.date_start
174 and nvl( b.date_end,p_effective_date)
175 ;
176
177 --
178 cursor prim_address_c(p_dpnt_person_id number) is
179 select
180 a.address_line1
181 , a.address_line2
182 , a.address_line3
183 , a.town_or_city
184 , a.region_2
185 , a.postal_code
186 , a.country
187 , a.date_from
188 , a.region_3
189 from per_addresses a
190 where
191 a.person_id = p_dpnt_person_id
192 and p_effective_date between nvl(a.date_from, p_effective_date)
193 and nvl(a.date_to, p_effective_date)
194 and a.primary_flag = 'Y'
195 ;
196 --
197 cursor prim_rltd_address_c(p_dpnt_person_id number) is
198 select
199 a.address_line1
200 , a.address_line2
201 , a.address_line3
202 , a.town_or_city
203 , a.region_2
204 , a.postal_code
205 , a.country
206 , a.date_from
207 , a.region_3
208 from per_addresses a,
209 per_contact_relationships r
210 where
211 r.contact_person_id = p_dpnt_person_id
212 and r.person_id = p_person_id
213 and r.person_id = a.person_id
214 and p_effective_date between nvl(a.date_from, p_effective_date)
215 and nvl(a.date_to, p_effective_date)
216 and a.primary_flag = 'Y'
217 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
218 ;
219 --
220 cursor ext_phone_c (p_dpnt_person_id number) is
221 select
222 h.phone_number phone_home
223 , w.phone_number phone_work
224 , f.phone_number phone_fax
225 , m.phone_number phone_mobile
226 from per_all_people_f p
227 , per_phones h
228 , per_phones w
229 , per_phones f
230 , per_phones m
231 where p.person_id = p_dpnt_person_id
232 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
233 and nvl(p.effective_end_date, p_effective_date)
234 and h.parent_id (+) = p.person_id
235 and w.parent_id (+) = p.person_id
236 and f.parent_id (+) = p.person_id
237 and m.parent_id (+) = p.person_id
238 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
239 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
240 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
241 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
242 and h.phone_type (+) = 'H1'
243 and w.phone_type (+) = 'W1'
244 and f.phone_type (+) = 'WF'
245 and m.phone_type (+) = 'M'
246 and p_effective_date between nvl(h.date_from, p_effective_date)
250 and p_effective_date between nvl(f.date_from, p_effective_date)
247 and nvl(h.date_to, p_effective_date)
248 and p_effective_date between nvl(w.date_from, p_effective_date)
249 and nvl(w.date_to, p_effective_date)
251 and nvl(f.date_to, p_effective_date)
252 and p_effective_date between nvl(m.date_from, p_effective_date)
253 and nvl(m.date_to, p_effective_date)
254 ;
255 --
256 BEGIN
257 --
258 hr_utility.set_location('Entering'||l_proc, 5);
259 --
260 --FOR dpnt IN c_dpnt(p_elig_per_elctbl_chc_id) LOOP
261 open c_dpnt(p_elig_per_elctbl_chc_id) ;
262 fetch c_dpnt bulk collect into
263 l_dpel_create_dt_va ,
264 l_dpel_elig_cvrd_dpnt_id_va ,
265 l_dpel_elig_strt_dt_va ,
266 l_dpel_elig_thru_dt_va ,
267 l_dpel_ovrdn_flag_va ,
268 l_dpel_ovrdn_thru_dt_va ,
269 l_dpel_dpnt_person_id_va ,
270 l_dpel_elig_dpnt_id_va ,
271 l_dpel_contact_type_va ,
272 l_dpel_sequence_number_va ,
273 l_dpel_rltd_per_dsgntr_flag_va ,
274 l_dpel_last_name_va ,
275 l_dpel_correspondence_lang_va ,
276 l_dpel_date_of_birth_va ,
277 l_dpel_email_address_va ,
278 l_dpel_first_name_va ,
279 l_dpel_full_name_va ,
280 l_dpel_marital_status_va ,
281 l_dpel_middle_names_va ,
282 l_dpel_nationality_va ,
283 l_dpel_national_identifier_va ,
284 l_dpel_disabled_flag_va ,
285 l_dpel_sex_va ,
286 l_dpel_student_status_va ,
287 l_dpel_suffix_va ,
288 l_dpel_title_va ,
289 l_dpel_date_of_death_va ,
290 l_dpel_known_as_va ,
291 l_dpel_pre_name_adjunct_va ,
292 l_dpel_uses_tobacco_flag_va ,
293 l_dpel_previous_last_name_va ;
294
295 close c_dpnt ;
296 --
297 for i in 1 .. l_dpel_elig_dpnt_id_va.count
298 Loop
299
300 initialize_globals;
301 --
302 -- fetch dependent information into globals
303 --
304 ben_ext_person.g_elig_dpnt_id := l_dpel_elig_dpnt_id_va(i) ;
305 ben_ext_person.g_elig_dpnt_create_dt := l_dpel_create_dt_va(i) ;
306 ben_ext_person.g_elig_dpnt_elig_strt_dt := l_dpel_elig_strt_dt_va(i) ;
307 ben_ext_person.g_elig_dpnt_elig_thru_dt := l_dpel_elig_thru_dt_va(i) ;
308 ben_ext_person.g_elig_dpnt_ovrdn_flag := l_dpel_ovrdn_flag_va(i) ;
309 ben_ext_person.g_elig_dpnt_ovrdn_thru_dt := l_dpel_ovrdn_thru_dt_va(i) ;
310 ben_ext_person.g_elig_dpnt_rlshp_type := l_dpel_contact_type_va(i) ;
311 ben_ext_person.g_elig_dpnt_contact_seq_num := l_dpel_sequence_number_va(i) ;
312 ben_ext_person.g_elig_dpnt_shared_resd_flag := l_dpel_rltd_per_dsgntr_flag_va(i) ;
313 ben_ext_person.g_elig_dpnt_national_ident := l_dpel_national_identifier_va(i) ;
314 ben_ext_person.g_elig_dpnt_last_name := l_dpel_last_name_va(i) ;
315 ben_ext_person.g_elig_dpnt_first_name := l_dpel_first_name_va(i) ;
316 ben_ext_person.g_elig_dpnt_middle_names := l_dpel_middle_names_va(i) ;
317 ben_ext_person.g_elig_dpnt_full_name := l_dpel_full_name_va(i) ;
318 ben_ext_person.g_elig_dpnt_suffix := l_dpel_suffix_va(i) ;
319 ben_ext_person.g_elig_dpnt_title := l_dpel_title_va(i) ;
320 ben_ext_person.g_elig_dpnt_date_of_birth := l_dpel_date_of_birth_va(i) ;
321 ben_ext_person.g_elig_dpnt_marital_status := l_dpel_marital_status_va(i) ;
322 ben_ext_person.g_elig_dpnt_sex := l_dpel_sex_va(i) ;
323 ben_ext_person.g_elig_dpnt_disabled_flag := l_dpel_disabled_flag_va(i) ;
324 ben_ext_person.g_elig_dpnt_student_status := l_dpel_student_status_va(i) ;
325 ben_ext_person.g_elig_dpnt_date_of_death := l_dpel_date_of_death_va(i) ;
326 ben_ext_person.g_elig_dpnt_language := l_dpel_correspondence_lang_va(i) ;
327 ben_ext_person.g_elig_dpnt_nationality := l_dpel_nationality_va(i) ;
328 ben_ext_person.g_elig_dpnt_email_address := l_dpel_email_address_va(i) ;
329 ben_ext_person.g_elig_dpnt_known_as := l_dpel_known_as_va(i) ;
330 ben_ext_person.g_elig_dpnt_pre_name_adjunct := l_dpel_pre_name_adjunct_va(i) ;
331 ben_ext_person.g_elig_dpnt_tobacco_usage := l_dpel_uses_tobacco_flag_va(i) ;
332 ben_ext_person.g_elig_dpnt_prev_last_name := l_dpel_previous_last_name_va(i) ;
333 --
334 -- retrieve dependent address info if required
335 if ben_extract.g_eda_csr = 'Y' then
336 open prim_address_c (l_dpel_dpnt_person_id_va(i));
337 fetch prim_address_c into ben_ext_person.g_elig_dpnt_prim_address1
338 ,ben_ext_person.g_elig_dpnt_prim_address2
339 ,ben_ext_person.g_elig_dpnt_prim_address3
340 ,ben_ext_person.g_elig_dpnt_prim_city
341 ,ben_ext_person.g_elig_dpnt_prim_state
342 ,ben_ext_person.g_elig_dpnt_prim_postal_code
343 ,ben_ext_person.g_elig_dpnt_prim_country
344 ,ben_ext_person.g_elig_dpnt_prim_effect_date
345 ,ben_ext_person.g_elig_dpnt_prim_region;
346 if prim_address_c%notfound then
347 open prim_rltd_address_c(l_dpel_dpnt_person_id_va(i));
348 fetch prim_rltd_address_c into ben_ext_person.g_elig_dpnt_prim_address1
349 , ben_ext_person.g_elig_dpnt_prim_address2
350 , ben_ext_person.g_elig_dpnt_prim_address3
351 , ben_ext_person.g_elig_dpnt_prim_city
352 , ben_ext_person.g_elig_dpnt_prim_state
353 , ben_ext_person.g_elig_dpnt_prim_postal_code
354 , ben_ext_person.g_elig_dpnt_prim_country
355 , ben_ext_person.g_elig_dpnt_prim_effect_date
356 , ben_ext_person.g_elig_dpnt_prim_region;
357 close prim_rltd_address_c;
358 end if;
359 close prim_address_c;
360 end if;
361 --
362 -- retrieve dependent phone numbers if required
363 if ben_extract.g_edp_csr = 'Y' then
364 open ext_phone_c(l_dpel_dpnt_person_id_va(i));
365 fetch ext_phone_c into ben_ext_person.g_elig_dpnt_home_phone
366 ,ben_ext_person.g_elig_dpnt_work_phone
367 ,ben_ext_person.g_elig_dpnt_fax
368 ,ben_ext_person.g_elig_dpnt_mobile;
369 close ext_phone_c;
370 end if;
371 --
372 -- format and write
373 --
374 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
375 p_ext_file_id => p_ext_file_id,
376 p_data_typ_cd => p_data_typ_cd,
377 p_ext_typ_cd => p_ext_typ_cd,
378 p_rcd_typ_cd => 'D',
379 p_low_lvl_cd => 'ED',--eligible dependent
380 p_person_id => p_person_id,
381 p_chg_evt_cd => p_chg_evt_cd,
382 p_business_group_id => p_business_group_id,
383 p_effective_date => p_effective_date
384 );
385 --
386 END LOOP;
387 --
388 hr_utility.set_location('Exiting'||l_proc, 15);
389
390 END; -- main
391 --
392 END; -- package