1 PACKAGE ben_determine_dpnt_elig_ss as
2 /* $Header: bendpels.pkh 120.0 2005/05/28 04:09:26 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Dependent Eligibility for Self Service
13 Purpose
14 This package loops through all electable choices for a passed in per_in_ler
15 and determines if the dependent (person_contact_id is passed in) is eligible. This
16 is called from self service enrollment. This logic is similar to the Dependent
17 Designation Form logic so changes made here should also be made in the forms library
18 for the Dependent Designation Form.
19
20 History
21 Date Who Version What?
22 ---- --- ------- -----
23 03 Aug 00 Thayden 115.0 Created.
24 12 Feb 02 Shdas 115.1 Added fmly_mmbr_cd_exist proc.
25 04 Dec 02 kmullapu 115.2 Added create_contact_w,update_person_w
26 NOCOPY changes
27 09 Mar 05 vborkar 115.4 Bug 4218944 - Added procedure
28 update_contact_w
29 */
30 --------------------------------------------------------------------------------
31
32
33 procedure main
34 (p_pgm_id in number
35 ,p_per_in_ler_id in number
36 ,p_person_id in number
37 ,p_contact_person_id in number
38 ,p_contact_relationship_id in number
39 ,p_effective_date in date
40 );
41 --
42 procedure fmly_mmbr_cd_exist
43 (p_business_group_id in number
44 ,p_effective_date in date
45 ,p_fmly_mmbr_exist out NOCOPY varchar2
46 );
47 --
48 procedure create_contact_w
49 (p_validate in varchar2 default 'N'
50 ,p_start_date in date
51 ,p_business_group_id in number
52 ,p_person_id in number
53 ,p_contact_person_id in number default null
54 ,p_contact_type in varchar2
55 ,p_ctr_comments in varchar2 default null
56 ,p_primary_contact_flag in varchar2 default 'N'
57 ,p_date_start in date default null
58 ,p_start_life_reason_id in number default null
59 ,p_date_end in date default null
60 ,p_end_life_reason_id in number default null
61 ,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default 'N'
62 ,p_personal_flag in varchar2 default 'N'
63 ,p_sequence_number in number default null
64 ,p_cont_attribute_category in varchar2 default null
65 ,p_cont_attribute1 in varchar2 default null
66 ,p_cont_attribute2 in varchar2 default null
67 ,p_cont_attribute3 in varchar2 default null
68 ,p_cont_attribute4 in varchar2 default null
69 ,p_cont_attribute5 in varchar2 default null
70 ,p_cont_attribute6 in varchar2 default null
71 ,p_cont_attribute7 in varchar2 default null
72 ,p_cont_attribute8 in varchar2 default null
73 ,p_cont_attribute9 in varchar2 default null
74 ,p_cont_attribute10 in varchar2 default null
75 ,p_cont_attribute11 in varchar2 default null
76 ,p_cont_attribute12 in varchar2 default null
77 ,p_cont_attribute13 in varchar2 default null
78 ,p_cont_attribute14 in varchar2 default null
79 ,p_cont_attribute15 in varchar2 default null
80 ,p_cont_attribute16 in varchar2 default null
81 ,p_cont_attribute17 in varchar2 default null
82 ,p_cont_attribute18 in varchar2 default null
83 ,p_cont_attribute19 in varchar2 default null
84 ,p_cont_attribute20 in varchar2 default null
85 ,p_cont_information_category in varchar2 default null
86 ,p_cont_information1 in varchar2 default null
87 ,p_cont_information2 in varchar2 default null
88 ,p_cont_information3 in varchar2 default null
89 ,p_cont_information4 in varchar2 default null
90 ,p_cont_information5 in varchar2 default null
91 ,p_cont_information6 in varchar2 default null
92 ,p_cont_information7 in varchar2 default null
93 ,p_cont_information8 in varchar2 default null
94 ,p_cont_information9 in varchar2 default null
95 ,p_cont_information10 in varchar2 default null
96 ,p_cont_information11 in varchar2 default null
97 ,p_cont_information12 in varchar2 default null
98 ,p_cont_information13 in varchar2 default null
99 ,p_cont_information14 in varchar2 default null
100 ,p_cont_information15 in varchar2 default null
101 ,p_cont_information16 in varchar2 default null
102 ,p_cont_information17 in varchar2 default null
103 ,p_cont_information18 in varchar2 default null
104 ,p_cont_information19 in varchar2 default null
105 ,p_cont_information20 in varchar2 default null
106 ,p_third_party_pay_flag in varchar2 default 'N'
107 ,p_bondholder_flag in varchar2 default 'N'
108 ,p_dependent_flag in varchar2 default 'N'
109 ,p_beneficiary_flag in varchar2 default 'N'
110 ,p_last_name in varchar2 default null
111 ,p_sex in varchar2 default null
112 ,p_person_type_id in number default null
113 ,p_per_comments in varchar2 default null
114 ,p_date_of_birth in date default null
115 ,p_email_address in varchar2 default null
116 ,p_first_name in varchar2 default null
117 ,p_known_as in varchar2 default null
118 ,p_marital_status in varchar2 default null
119 ,p_middle_names in varchar2 default null
120 ,p_nationality in varchar2 default null
121 ,p_national_identifier in varchar2 default null
122 ,p_previous_last_name in varchar2 default null
123 ,p_registered_disabled_flag in varchar2 default null
124 ,p_title in varchar2 default null
125 ,p_work_telephone in varchar2 default null
126 ,p_attribute_category in varchar2 default null
127 ,p_attribute1 in varchar2 default null
128 ,p_attribute2 in varchar2 default null
129 ,p_attribute3 in varchar2 default null
130 ,p_attribute4 in varchar2 default null
131 ,p_attribute5 in varchar2 default null
132 ,p_attribute6 in varchar2 default null
133 ,p_attribute7 in varchar2 default null
134 ,p_attribute8 in varchar2 default null
135 ,p_attribute9 in varchar2 default null
136 ,p_attribute10 in varchar2 default null
137 ,p_attribute11 in varchar2 default null
138 ,p_attribute12 in varchar2 default null
139 ,p_attribute13 in varchar2 default null
140 ,p_attribute14 in varchar2 default null
141 ,p_attribute15 in varchar2 default null
142 ,p_attribute16 in varchar2 default null
143 ,p_attribute17 in varchar2 default null
144 ,p_attribute18 in varchar2 default null
145 ,p_attribute19 in varchar2 default null
146 ,p_attribute20 in varchar2 default null
147 ,p_attribute21 in varchar2 default null
148 ,p_attribute22 in varchar2 default null
149 ,p_attribute23 in varchar2 default null
150 ,p_attribute24 in varchar2 default null
151 ,p_attribute25 in varchar2 default null
152 ,p_attribute26 in varchar2 default null
153 ,p_attribute27 in varchar2 default null
154 ,p_attribute28 in varchar2 default null
155 ,p_attribute29 in varchar2 default null
156 ,p_attribute30 in varchar2 default null
157 ,p_per_information_category in varchar2 default null
158 ,p_per_information1 in varchar2 default null
159 ,p_per_information2 in varchar2 default null
160 ,p_per_information3 in varchar2 default null
161 ,p_per_information4 in varchar2 default null
162 ,p_per_information5 in varchar2 default null
163 ,p_per_information6 in varchar2 default null
164 ,p_per_information7 in varchar2 default null
165 ,p_per_information8 in varchar2 default null
166 ,p_per_information9 in varchar2 default null
167 ,p_per_information10 in varchar2 default null
168 ,p_per_information11 in varchar2 default null
169 ,p_per_information12 in varchar2 default null
170 ,p_per_information13 in varchar2 default null
171 ,p_per_information14 in varchar2 default null
172 ,p_per_information15 in varchar2 default null
173 ,p_per_information16 in varchar2 default null
174 ,p_per_information17 in varchar2 default null
175 ,p_per_information18 in varchar2 default null
176 ,p_per_information19 in varchar2 default null
177 ,p_per_information20 in varchar2 default null
178 ,p_per_information21 in varchar2 default null
179 ,p_per_information22 in varchar2 default null
180 ,p_per_information23 in varchar2 default null
181 ,p_per_information24 in varchar2 default null
182 ,p_per_information25 in varchar2 default null
183 ,p_per_information26 in varchar2 default null
184 ,p_per_information27 in varchar2 default null
185 ,p_per_information28 in varchar2 default null
186 ,p_per_information29 in varchar2 default null
187 ,p_per_information30 in varchar2 default null
188 ,p_correspondence_language in varchar2 default null
189 ,p_honors in varchar2 default null
190 ,p_pre_name_adjunct in varchar2 default null
191 ,p_suffix in varchar2 default null
192 ,p_create_mirror_flag in varchar2 default 'N'
193 ,p_mirror_type in varchar2 default null
194
195 ,p_contact_relationship_id out NOCOPY number
196 ,p_ctr_object_version_number out NOCOPY number
197 ,p_per_person_id out NOCOPY number
198 ,p_per_object_version_number out NOCOPY number
199 ,p_per_effective_start_date out NOCOPY date
200 ,p_per_effective_end_date out NOCOPY date
201 ,p_full_name out NOCOPY varchar2
202 ,p_per_comment_id out NOCOPY number
203 ,p_name_combination_warning out NOCOPY varchar2
204 ,p_orig_hire_warning out NOCOPY varchar2
205 ,p_return_status out NOCOPY varchar2
206 );
207 --
208 procedure update_contact_w
209 (p_validate in varchar2 default 'N'
210 ,p_effective_date in date
211 ,p_contact_relationship_id in number
212 ,p_contact_type in varchar2 default hr_api.g_varchar2
213 ,p_comments in long default hr_api.g_varchar2
214 ,p_primary_contact_flag in varchar2 default hr_api.g_varchar2
215 ,p_third_party_pay_flag in varchar2 default hr_api.g_varchar2
216 ,p_bondholder_flag in varchar2 default hr_api.g_varchar2
217 ,p_date_start in date default hr_api.g_date
218 ,p_start_life_reason_id in number default hr_api.g_number
219 ,p_date_end in date default hr_api.g_date
220 ,p_end_life_reason_id in number default hr_api.g_number
221 ,p_rltd_per_rsds_w_dsgntr_flag in varchar2 default hr_api.g_varchar2
222 ,p_personal_flag in varchar2 default hr_api.g_varchar2
223 ,p_sequence_number in number default hr_api.g_number
224 ,p_dependent_flag in varchar2 default hr_api.g_varchar2
225 ,p_beneficiary_flag in varchar2 default hr_api.g_varchar2
226 ,p_cont_attribute_category in varchar2 default hr_api.g_varchar2
227 ,p_cont_attribute1 in varchar2 default hr_api.g_varchar2
228 ,p_cont_attribute2 in varchar2 default hr_api.g_varchar2
229 ,p_cont_attribute3 in varchar2 default hr_api.g_varchar2
230 ,p_cont_attribute4 in varchar2 default hr_api.g_varchar2
231 ,p_cont_attribute5 in varchar2 default hr_api.g_varchar2
232 ,p_cont_attribute6 in varchar2 default hr_api.g_varchar2
233 ,p_cont_attribute7 in varchar2 default hr_api.g_varchar2
234 ,p_cont_attribute8 in varchar2 default hr_api.g_varchar2
235 ,p_cont_attribute9 in varchar2 default hr_api.g_varchar2
236 ,p_cont_attribute10 in varchar2 default hr_api.g_varchar2
237 ,p_cont_attribute11 in varchar2 default hr_api.g_varchar2
238 ,p_cont_attribute12 in varchar2 default hr_api.g_varchar2
239 ,p_cont_attribute13 in varchar2 default hr_api.g_varchar2
240 ,p_cont_attribute14 in varchar2 default hr_api.g_varchar2
241 ,p_cont_attribute15 in varchar2 default hr_api.g_varchar2
242 ,p_cont_attribute16 in varchar2 default hr_api.g_varchar2
243 ,p_cont_attribute17 in varchar2 default hr_api.g_varchar2
244 ,p_cont_attribute18 in varchar2 default hr_api.g_varchar2
245 ,p_cont_attribute19 in varchar2 default hr_api.g_varchar2
246 ,p_cont_attribute20 in varchar2 default hr_api.g_varchar2
247 ,p_cont_information_category in varchar2 default hr_api.g_varchar2
248 ,p_cont_information1 in varchar2 default hr_api.g_varchar2
249 ,p_cont_information2 in varchar2 default hr_api.g_varchar2
250 ,p_cont_information3 in varchar2 default hr_api.g_varchar2
251 ,p_cont_information4 in varchar2 default hr_api.g_varchar2
252 ,p_cont_information5 in varchar2 default hr_api.g_varchar2
253 ,p_cont_information6 in varchar2 default hr_api.g_varchar2
254 ,p_cont_information7 in varchar2 default hr_api.g_varchar2
255 ,p_cont_information8 in varchar2 default hr_api.g_varchar2
256 ,p_cont_information9 in varchar2 default hr_api.g_varchar2
257 ,p_cont_information10 in varchar2 default hr_api.g_varchar2
258 ,p_cont_information11 in varchar2 default hr_api.g_varchar2
259 ,p_cont_information12 in varchar2 default hr_api.g_varchar2
260 ,p_cont_information13 in varchar2 default hr_api.g_varchar2
264 ,p_cont_information17 in varchar2 default hr_api.g_varchar2
261 ,p_cont_information14 in varchar2 default hr_api.g_varchar2
262 ,p_cont_information15 in varchar2 default hr_api.g_varchar2
263 ,p_cont_information16 in varchar2 default hr_api.g_varchar2
265 ,p_cont_information18 in varchar2 default hr_api.g_varchar2
266 ,p_cont_information19 in varchar2 default hr_api.g_varchar2
267 ,p_cont_information20 in varchar2 default hr_api.g_varchar2
268 ,p_object_version_number in out nocopy number
269 ,p_return_status out nocopy varchar2
270 );
271 --
272 procedure update_person_w
273 (p_validate in varchar2 default 'N'
274 ,p_effective_date in date
275 ,p_datetrack_update_mode in varchar2
276 ,p_person_id in number
277 ,p_object_version_number in out NOCOPY number
278 ,p_person_type_id in number default hr_api.g_number
279 ,p_last_name in varchar2 default hr_api.g_varchar2
280 ,p_applicant_number in varchar2 default hr_api.g_varchar2
281 ,p_comments in varchar2 default hr_api.g_varchar2
282 ,p_date_employee_data_verified in date default hr_api.g_date
283 ,p_date_of_birth in date default hr_api.g_date
284 ,p_email_address in varchar2 default hr_api.g_varchar2
285 ,p_employee_number in out NOCOPY varchar2
286 ,p_expense_check_send_to_addres in varchar2 default hr_api.g_varchar2
287 ,p_first_name in varchar2 default hr_api.g_varchar2
288 ,p_known_as in varchar2 default hr_api.g_varchar2
289 ,p_marital_status in varchar2 default hr_api.g_varchar2
290 ,p_middle_names in varchar2 default hr_api.g_varchar2
291 ,p_nationality in varchar2 default hr_api.g_varchar2
292 ,p_national_identifier in varchar2 default hr_api.g_varchar2
293 ,p_previous_last_name in varchar2 default hr_api.g_varchar2
294 ,p_registered_disabled_flag in varchar2 default hr_api.g_varchar2
295 ,p_sex in varchar2 default hr_api.g_varchar2
296 ,p_title in varchar2 default hr_api.g_varchar2
297 ,p_vendor_id in number default hr_api.g_number
298 ,p_work_telephone in varchar2 default hr_api.g_varchar2
299 ,p_attribute_category in varchar2 default hr_api.g_varchar2
300 ,p_attribute1 in varchar2 default hr_api.g_varchar2
301 ,p_attribute2 in varchar2 default hr_api.g_varchar2
302 ,p_attribute3 in varchar2 default hr_api.g_varchar2
303 ,p_attribute4 in varchar2 default hr_api.g_varchar2
304 ,p_attribute5 in varchar2 default hr_api.g_varchar2
305 ,p_attribute6 in varchar2 default hr_api.g_varchar2
306 ,p_attribute7 in varchar2 default hr_api.g_varchar2
307 ,p_attribute8 in varchar2 default hr_api.g_varchar2
308 ,p_attribute9 in varchar2 default hr_api.g_varchar2
309 ,p_attribute10 in varchar2 default hr_api.g_varchar2
310 ,p_attribute11 in varchar2 default hr_api.g_varchar2
311 ,p_attribute12 in varchar2 default hr_api.g_varchar2
312 ,p_attribute13 in varchar2 default hr_api.g_varchar2
313 ,p_attribute14 in varchar2 default hr_api.g_varchar2
314 ,p_attribute15 in varchar2 default hr_api.g_varchar2
315 ,p_attribute16 in varchar2 default hr_api.g_varchar2
316 ,p_attribute17 in varchar2 default hr_api.g_varchar2
317 ,p_attribute18 in varchar2 default hr_api.g_varchar2
318 ,p_attribute19 in varchar2 default hr_api.g_varchar2
319 ,p_attribute20 in varchar2 default hr_api.g_varchar2
320 ,p_attribute21 in varchar2 default hr_api.g_varchar2
321 ,p_attribute22 in varchar2 default hr_api.g_varchar2
322 ,p_attribute23 in varchar2 default hr_api.g_varchar2
323 ,p_attribute24 in varchar2 default hr_api.g_varchar2
324 ,p_attribute25 in varchar2 default hr_api.g_varchar2
325 ,p_attribute26 in varchar2 default hr_api.g_varchar2
326 ,p_attribute27 in varchar2 default hr_api.g_varchar2
327 ,p_attribute28 in varchar2 default hr_api.g_varchar2
328 ,p_attribute29 in varchar2 default hr_api.g_varchar2
329 ,p_attribute30 in varchar2 default hr_api.g_varchar2
330 ,p_per_information_category in varchar2 default hr_api.g_varchar2
331 ,p_per_information1 in varchar2 default hr_api.g_varchar2
332 ,p_per_information2 in varchar2 default hr_api.g_varchar2
333 ,p_per_information3 in varchar2 default hr_api.g_varchar2
334 ,p_per_information4 in varchar2 default hr_api.g_varchar2
335 ,p_per_information5 in varchar2 default hr_api.g_varchar2
336 ,p_per_information6 in varchar2 default hr_api.g_varchar2
337 ,p_per_information7 in varchar2 default hr_api.g_varchar2
338 ,p_per_information8 in varchar2 default hr_api.g_varchar2
339 ,p_per_information9 in varchar2 default hr_api.g_varchar2
340 ,p_per_information10 in varchar2 default hr_api.g_varchar2
341 ,p_per_information11 in varchar2 default hr_api.g_varchar2
342 ,p_per_information12 in varchar2 default hr_api.g_varchar2
343 ,p_per_information13 in varchar2 default hr_api.g_varchar2
344 ,p_per_information14 in varchar2 default hr_api.g_varchar2
345 ,p_per_information15 in varchar2 default hr_api.g_varchar2
346 ,p_per_information16 in varchar2 default hr_api.g_varchar2
347 ,p_per_information17 in varchar2 default hr_api.g_varchar2
348 ,p_per_information18 in varchar2 default hr_api.g_varchar2
349 ,p_per_information19 in varchar2 default hr_api.g_varchar2
350 ,p_per_information20 in varchar2 default hr_api.g_varchar2
351 ,p_per_information21 in varchar2 default hr_api.g_varchar2
352 ,p_per_information22 in varchar2 default hr_api.g_varchar2
353 ,p_per_information23 in varchar2 default hr_api.g_varchar2
354 ,p_per_information24 in varchar2 default hr_api.g_varchar2
355 ,p_per_information25 in varchar2 default hr_api.g_varchar2
356 ,p_per_information26 in varchar2 default hr_api.g_varchar2
360 ,p_per_information30 in varchar2 default hr_api.g_varchar2
357 ,p_per_information27 in varchar2 default hr_api.g_varchar2
358 ,p_per_information28 in varchar2 default hr_api.g_varchar2
359 ,p_per_information29 in varchar2 default hr_api.g_varchar2
361 ,p_date_of_death in date default hr_api.g_date
362 ,p_background_check_status in varchar2 default hr_api.g_varchar2
363 ,p_background_date_check in date default hr_api.g_date
364 ,p_blood_type in varchar2 default hr_api.g_varchar2
365 ,p_correspondence_language in varchar2 default hr_api.g_varchar2
366 ,p_fast_path_employee in varchar2 default hr_api.g_varchar2
367 ,p_fte_capacity in number default hr_api.g_number
368 ,p_hold_applicant_date_until in date default hr_api.g_date
369 ,p_honors in varchar2 default hr_api.g_varchar2
370 ,p_internal_location in varchar2 default hr_api.g_varchar2
371 ,p_last_medical_test_by in varchar2 default hr_api.g_varchar2
372 ,p_last_medical_test_date in date default hr_api.g_date
373 ,p_mailstop in varchar2 default hr_api.g_varchar2
374 ,p_office_number in varchar2 default hr_api.g_varchar2
375 ,p_on_military_service in varchar2 default hr_api.g_varchar2
376 ,p_pre_name_adjunct in varchar2 default hr_api.g_varchar2
377 ,p_projected_start_date in date default hr_api.g_date
378 ,p_rehire_authorizor in varchar2 default hr_api.g_varchar2
379 ,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
380 ,p_resume_exists in varchar2 default hr_api.g_varchar2
381 ,p_resume_last_updated in date default hr_api.g_date
382 ,p_second_passport_exists in varchar2 default hr_api.g_varchar2
383 ,p_student_status in varchar2 default hr_api.g_varchar2
384 ,p_work_schedule in varchar2 default hr_api.g_varchar2
385 ,p_rehire_reason in varchar2 default hr_api.g_varchar2
386 ,p_suffix in varchar2 default hr_api.g_varchar2
387 ,p_benefit_group_id in number default hr_api.g_number
388 ,p_receipt_of_death_cert_date in date default hr_api.g_date
389 ,p_coord_ben_med_pln_no in varchar2 default hr_api.g_varchar2
390 ,p_coord_ben_no_cvg_flag in varchar2 default hr_api.g_varchar2
394 ,p_coord_ben_med_insr_crr_ident in varchar2 default hr_api.g_varchar2
391 ,p_coord_ben_med_ext_er in varchar2 default hr_api.g_varchar2
392 ,p_coord_ben_med_pl_name in varchar2 default hr_api.g_varchar2
393 ,p_coord_ben_med_insr_crr_name in varchar2 default hr_api.g_varchar2
395 ,p_coord_ben_med_cvg_strt_dt in date default hr_api.g_date
396 ,p_coord_ben_med_cvg_end_dt in date default hr_api.g_date
397 ,p_uses_tobacco_flag in varchar2 default hr_api.g_varchar2
398 ,p_dpdnt_adoption_date in date default hr_api.g_date
399 ,p_dpdnt_vlntry_svce_flag in varchar2 default hr_api.g_varchar2
400 ,p_original_date_of_hire in date default hr_api.g_date
401 ,p_adjusted_svc_date in date default hr_api.g_date
402 ,p_town_of_birth in varchar2 default hr_api.g_varchar2
403 ,p_region_of_birth in varchar2 default hr_api.g_varchar2
404 ,p_country_of_birth in varchar2 default hr_api.g_varchar2
405 ,p_global_person_id in varchar2 default hr_api.g_varchar2
406 ,p_party_id in number default hr_api.g_number
407 ,p_npw_number in varchar2 default hr_api.g_varchar2
408 ,p_effective_start_date out NOCOPY date
409 ,p_effective_end_date out NOCOPY date
410 ,p_full_name out NOCOPY varchar2
411 ,p_comment_id out NOCOPY number
412 ,p_name_combination_warning out NOCOPY varchar2
413 ,p_assign_payroll_warning out NOCOPY varchar2
414 ,p_orig_hire_warning out NOCOPY varchar2
415 ,p_return_status out NOCOPY varchar2
416 );
417 --
418 end ben_determine_dpnt_elig_ss;