1 PACKAGE BODY HR_CONTACT_RELATIONSHIPS as
2 /* $Header: pecon02t.pkb 120.2 2006/02/13 06:08:37 asgugupt noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 /*---------------------------------------------------------------------------
9 Description
10 -----------
11
12 Date Author Version Description
13 --------- --------- ------- -------------------------------------------
14 09-May-94 JRhodes 80.0 Created Initial Version
15 09-Nov-94 RFine 70.3 Suppressed index on business_group_id
16 25-Jan-95 JRhodes 70.5 Added ATTRIBUTE21-30 for People
17 Added REGISTERED_DISABLED_FLAG
18 05-Jul-95 TMathers 70.6 Added validate_address and check_ppm.
19 13-Jul-95 TMathers 70.7 Added extra parameters to check_ppm.
20 do it could be used for both insert and update
21 13-Jul-95 TMathers 70.8 Added Message HR_7874_GARNISHMENT_IS_PAYEE
22 14-Jul-95 TMathers 70.9 Add check for mirror relationship.
23 11-Sep-95 SSDesai 70.10 Added check_beneficiary, check_dependent.
24 10-Oct-95 SSDesai 70.12 Added messages for check_beneficiary/dependent.
25 18-Jul-97 RKamiyam 70.13 Added per_information1 to 30 and known_as cols.
26 02-Mar-98 SKamatka 110.3 Bug #622399: Changed contact_only function.
27 Contact won't be deleted if it has relationship
28 with more than one person.
29 25-Nov-98 ASahay 110.4 Bug 768997 Modified if condition to check for
30 greater than 1 only
31 05-Feb-99 LSigrist 110.5 Updated cursors 'get_person', and 'c' with MLS
32 changes. Also, checked to ensure date formats
33 are release 11.5 compliant.
34 16-Feb-99 ASahay 115.3 Bug 820655 Added function multiple_contacts
35 to_check for multiple relationships
36 10-Apr-00 KSivagur 115.4 added parameters p_pre_name_adjunct and p_suffix
37 to the get_person_details procedure.
38 13-Mar-01 KSivagur 115.5 added parameter per_information_catergory.
39 21-Aug-01 adhunter 115.6 PTU changes to get person type from new function.
40 12-Oct-01 adhunter 115.7 1766066: Redid multiple contacts function.
41 14-Nov-02 MGettins 115.9 Added npw_number parameter to get_person_details.
42 15-NOV-02 MGettins 115.10 Added dbdrv lines.
43 04-DEC-02 PKakar 115.11 Added nocopy changes
44 25-JAN-03 DCasemor 115.12 Overloaded get_person_details so that
45 p_npw_number is not mandatory.
46 10-JUL-03 MGettins 115.13 Modified cursor in Contact_Only procedure as
47 part of fix for bug 3040059.
48 14-OCT-03 jpthomas 115.13 Modified the cursor to exclude the system person types
49 for the benefits.
50 21-JUN-04 adudekul 115.14 Bug 3648765. Fix to performance issues.
51 13-FEB-05 asgugupt 115.16 Overloaded get_person_details for bug no 4957699.
52 ---------------------------------------------------------------------------*/
53
54
55 PROCEDURE get_person_details
56 (p_person_id IN NUMBER
57 ,p_session_date IN DATE
58 ,p_first_name IN OUT NOCOPY varchar2
59 ,p_middle_names IN OUT NOCOPY varchar2
60 ,p_pre_name_adjunct IN OUT NOCOPY varchar2
61 ,p_suffix IN OUT NOCOPY varchar2
62 ,p_title IN OUT NOCOPY varchar2
63 ,p_sex IN OUT NOCOPY varchar2
64 ,p_date_of_birth IN OUT NOCOPY DATE
65 ,p_age IN OUT NOCOPY number
66 ,p_employee_number IN OUT NOCOPY varchar2
67 ,p_applicant_number IN OUT NOCOPY varchar2
68 ,p_title_desc IN OUT NOCOPY varchar2
69 ,p_national_identifier IN OUT NOCOPY VARCHAR2
70 ,p_person_type_id IN OUT NOCOPY number
71 ,p_user_person_type IN OUT NOCOPY varchar2
72 ,p_system_person_type IN OUT NOCOPY varchar2
73 ,p_current_employee_flag IN OUT NOCOPY varchar2
74 ,p_current_applicant_flag IN OUT NOCOPY varchar2
75 ,p_current_emp_or_apl_flag IN OUT NOCOPY varchar2
76 ,p_registered_disabled_flag IN OUT NOCOPY varchar2
77 ,p_attribute_category IN OUT NOCOPY varchar2
78 ,p_attribute1 IN OUT NOCOPY varchar2
79 ,p_attribute2 IN OUT NOCOPY varchar2
80 ,p_attribute3 IN OUT NOCOPY varchar2
81 ,p_attribute4 IN OUT NOCOPY varchar2
82 ,p_attribute5 IN OUT NOCOPY varchar2
83 ,p_attribute6 IN OUT NOCOPY varchar2
84 ,p_attribute7 IN OUT NOCOPY varchar2
85 ,p_attribute8 IN OUT NOCOPY varchar2
86 ,p_attribute9 IN OUT NOCOPY varchar2
87 ,p_attribute10 IN OUT NOCOPY varchar2
88 ,p_attribute11 IN OUT NOCOPY varchar2
89 ,p_attribute12 IN OUT NOCOPY varchar2
90 ,p_attribute13 IN OUT NOCOPY varchar2
91 ,p_attribute14 IN OUT NOCOPY varchar2
92 ,p_attribute15 IN OUT NOCOPY varchar2
93 ,p_attribute16 IN OUT NOCOPY varchar2
94 ,p_attribute17 IN OUT NOCOPY varchar2
95 ,p_attribute18 IN OUT NOCOPY varchar2
96 ,p_attribute19 IN OUT NOCOPY varchar2
97 ,p_attribute20 IN OUT NOCOPY varchar2
98 ,p_attribute21 IN OUT NOCOPY varchar2
99 ,p_attribute22 IN OUT NOCOPY varchar2
100 ,p_attribute23 IN OUT NOCOPY varchar2
101 ,p_attribute24 IN OUT NOCOPY varchar2
102 ,p_attribute25 IN OUT NOCOPY varchar2
103 ,p_attribute26 IN OUT NOCOPY varchar2
104 ,p_attribute27 IN OUT NOCOPY varchar2
105 ,p_attribute28 IN OUT NOCOPY varchar2
106 ,p_attribute29 IN OUT NOCOPY varchar2
107 ,p_attribute30 IN OUT NOCOPY varchar2
108 ,p_comment_id IN OUT NOCOPY number
109 ,p_contact_only IN OUT NOCOPY varchar2
110 ,p_per_information_category IN OUT NOCOPY varchar2
111 ,p_per_information1 IN OUT NOCOPY varchar2
112 ,p_per_information2 IN OUT NOCOPY varchar2
113 ,p_per_information3 IN OUT NOCOPY varchar2
114 ,p_per_information4 IN OUT NOCOPY varchar2
115 ,p_per_information5 IN OUT NOCOPY varchar2
116 ,p_per_information6 IN OUT NOCOPY varchar2
117 ,p_per_information7 IN OUT NOCOPY varchar2
118 ,p_per_information8 IN OUT NOCOPY varchar2
119 ,p_per_information9 IN OUT NOCOPY varchar2
120 ,p_per_information10 IN OUT NOCOPY varchar2
121 ,p_per_information11 IN OUT NOCOPY varchar2
122 ,p_per_information12 IN OUT NOCOPY varchar2
123 ,p_per_information13 IN OUT NOCOPY varchar2
124 ,p_per_information14 IN OUT NOCOPY varchar2
125 ,p_per_information15 IN OUT NOCOPY varchar2
126 ,p_per_information16 IN OUT NOCOPY varchar2
127 ,p_per_information17 IN OUT NOCOPY varchar2
128 ,p_per_information18 IN OUT NOCOPY varchar2
129 ,p_per_information19 IN OUT NOCOPY varchar2
130 ,p_per_information20 IN OUT NOCOPY varchar2
131 ,p_per_information21 IN OUT NOCOPY varchar2
132 ,p_per_information22 IN OUT NOCOPY varchar2
133 ,p_per_information23 IN OUT NOCOPY varchar2
134 ,p_per_information24 IN OUT NOCOPY varchar2
135 ,p_per_information25 IN OUT NOCOPY varchar2
136 ,p_per_information26 IN OUT NOCOPY varchar2
137 ,p_per_information27 IN OUT NOCOPY varchar2
138 ,p_per_information28 IN OUT NOCOPY varchar2
139 ,p_per_information29 IN OUT NOCOPY varchar2
140 ,p_per_information30 IN OUT NOCOPY varchar2
141 ,p_known_as IN OUT NOCOPY varchar2
142 ) is
143
144 l_npw_number per_all_people_f.npw_number%TYPE;
145
146 BEGIN
147
148 /* Call the overloaded procedure. */
149 get_person_details
150 (p_person_id => p_person_id
151 ,p_session_date => p_session_date
152 ,p_first_name => p_first_name
153 ,p_middle_names => p_middle_names
154 ,p_pre_name_adjunct => p_pre_name_adjunct
155 ,p_suffix => p_suffix
156 ,p_title => p_title
157 ,p_sex => p_sex
158 ,p_date_of_birth => p_date_of_birth
159 ,p_age => p_age
160 ,p_employee_number => p_employee_number
161 ,p_applicant_number => p_applicant_number
162 ,p_title_desc => p_title_desc
163 ,p_national_identifier => p_national_identifier
164 ,p_person_type_id => p_person_type_id
165 ,p_user_person_type => p_user_person_type
166 ,p_system_person_type => p_system_person_type
167 ,p_current_employee_flag => p_current_employee_flag
168 ,p_current_applicant_flag => p_current_applicant_flag
169 ,p_current_emp_or_apl_flag => p_current_emp_or_apl_flag
170 ,p_registered_disabled_flag => p_registered_disabled_flag
171 ,p_attribute_category => p_attribute_category
172 ,p_attribute1 => p_attribute1
173 ,p_attribute2 => p_attribute2
174 ,p_attribute3 => p_attribute3
175 ,p_attribute4 => p_attribute4
176 ,p_attribute5 => p_attribute5
177 ,p_attribute6 => p_attribute6
178 ,p_attribute7 => p_attribute7
179 ,p_attribute8 => p_attribute8
180 ,p_attribute9 => p_attribute9
181 ,p_attribute10 => p_attribute10
182 ,p_attribute11 => p_attribute11
183 ,p_attribute12 => p_attribute12
184 ,p_attribute13 => p_attribute13
185 ,p_attribute14 => p_attribute14
186 ,p_attribute15 => p_attribute15
187 ,p_attribute16 => p_attribute16
188 ,p_attribute17 => p_attribute17
189 ,p_attribute18 => p_attribute18
190 ,p_attribute19 => p_attribute19
191 ,p_attribute20 => p_attribute20
192 ,p_attribute21 => p_attribute21
193 ,p_attribute22 => p_attribute22
194 ,p_attribute23 => p_attribute23
195 ,p_attribute24 => p_attribute24
196 ,p_attribute25 => p_attribute25
197 ,p_attribute26 => p_attribute26
198 ,p_attribute27 => p_attribute27
199 ,p_attribute28 => p_attribute28
200 ,p_attribute29 => p_attribute29
201 ,p_attribute30 => p_attribute30
202 ,p_comment_id => p_comment_id
203 ,p_contact_only => p_contact_only
204 ,p_per_information_category => p_per_information_category
205 ,p_per_information1 => p_per_information1
206 ,p_per_information2 => p_per_information2
207 ,p_per_information3 => p_per_information3
208 ,p_per_information4 => p_per_information4
209 ,p_per_information5 => p_per_information5
210 ,p_per_information6 => p_per_information6
211 ,p_per_information7 => p_per_information7
212 ,p_per_information8 => p_per_information8
213 ,p_per_information9 => p_per_information9
214 ,p_per_information10 => p_per_information10
215 ,p_per_information11 => p_per_information11
216 ,p_per_information12 => p_per_information12
217 ,p_per_information13 => p_per_information13
218 ,p_per_information14 => p_per_information14
219 ,p_per_information15 => p_per_information15
220 ,p_per_information16 => p_per_information16
221 ,p_per_information17 => p_per_information17
222 ,p_per_information18 => p_per_information18
223 ,p_per_information19 => p_per_information19
224 ,p_per_information20 => p_per_information20
225 ,p_per_information21 => p_per_information21
226 ,p_per_information22 => p_per_information22
227 ,p_per_information23 => p_per_information23
228 ,p_per_information24 => p_per_information24
229 ,p_per_information25 => p_per_information25
230 ,p_per_information26 => p_per_information26
231 ,p_per_information27 => p_per_information27
232 ,p_per_information28 => p_per_information28
233 ,p_per_information29 => p_per_information29
234 ,p_per_information30 => p_per_information30
235 ,p_known_as => p_known_as
236 ,p_npw_number => l_npw_number);
237
238 END get_person_details;
239
240 /* Overloaded, includes p_npw_number */
241 PROCEDURE get_person_details
242 (p_person_id IN NUMBER
243 ,p_session_date IN DATE
244 ,p_first_name IN OUT NOCOPY varchar2
245 ,p_middle_names IN OUT NOCOPY varchar2
246 ,p_pre_name_adjunct IN OUT NOCOPY varchar2
247 ,p_suffix IN OUT NOCOPY varchar2
248 ,p_title IN OUT NOCOPY varchar2
249 ,p_sex IN OUT NOCOPY varchar2
250 ,p_date_of_birth IN OUT NOCOPY DATE
251 ,p_age IN OUT NOCOPY number
252 ,p_employee_number IN OUT NOCOPY varchar2
253 ,p_applicant_number IN OUT NOCOPY varchar2
254 ,p_title_desc IN OUT NOCOPY varchar2
255 ,p_national_identifier IN OUT NOCOPY VARCHAR2
256 ,p_person_type_id IN OUT NOCOPY number
257 ,p_user_person_type IN OUT NOCOPY varchar2
258 ,p_system_person_type IN OUT NOCOPY varchar2
259 ,p_current_employee_flag IN OUT NOCOPY varchar2
260 ,p_current_applicant_flag IN OUT NOCOPY varchar2
261 ,p_current_emp_or_apl_flag IN OUT NOCOPY varchar2
262 ,p_registered_disabled_flag IN OUT NOCOPY varchar2
263 ,p_attribute_category IN OUT NOCOPY varchar2
264 ,p_attribute1 IN OUT NOCOPY varchar2
265 ,p_attribute2 IN OUT NOCOPY varchar2
266 ,p_attribute3 IN OUT NOCOPY varchar2
267 ,p_attribute4 IN OUT NOCOPY varchar2
268 ,p_attribute5 IN OUT NOCOPY varchar2
269 ,p_attribute6 IN OUT NOCOPY varchar2
270 ,p_attribute7 IN OUT NOCOPY varchar2
271 ,p_attribute8 IN OUT NOCOPY varchar2
272 ,p_attribute9 IN OUT NOCOPY varchar2
273 ,p_attribute10 IN OUT NOCOPY varchar2
274 ,p_attribute11 IN OUT NOCOPY varchar2
275 ,p_attribute12 IN OUT NOCOPY varchar2
276 ,p_attribute13 IN OUT NOCOPY varchar2
277 ,p_attribute14 IN OUT NOCOPY varchar2
278 ,p_attribute15 IN OUT NOCOPY varchar2
279 ,p_attribute16 IN OUT NOCOPY varchar2
280 ,p_attribute17 IN OUT NOCOPY varchar2
281 ,p_attribute18 IN OUT NOCOPY varchar2
282 ,p_attribute19 IN OUT NOCOPY varchar2
283 ,p_attribute20 IN OUT NOCOPY varchar2
284 ,p_attribute21 IN OUT NOCOPY varchar2
285 ,p_attribute22 IN OUT NOCOPY varchar2
286 ,p_attribute23 IN OUT NOCOPY varchar2
287 ,p_attribute24 IN OUT NOCOPY varchar2
288 ,p_attribute25 IN OUT NOCOPY varchar2
289 ,p_attribute26 IN OUT NOCOPY varchar2
290 ,p_attribute27 IN OUT NOCOPY varchar2
291 ,p_attribute28 IN OUT NOCOPY varchar2
292 ,p_attribute29 IN OUT NOCOPY varchar2
293 ,p_attribute30 IN OUT NOCOPY varchar2
294 ,p_comment_id IN OUT NOCOPY number
295 ,p_contact_only IN OUT NOCOPY varchar2
296 ,p_per_information_category IN OUT NOCOPY varchar2
297 ,p_per_information1 IN OUT NOCOPY varchar2
298 ,p_per_information2 IN OUT NOCOPY varchar2
299 ,p_per_information3 IN OUT NOCOPY varchar2
300 ,p_per_information4 IN OUT NOCOPY varchar2
301 ,p_per_information5 IN OUT NOCOPY varchar2
302 ,p_per_information6 IN OUT NOCOPY varchar2
303 ,p_per_information7 IN OUT NOCOPY varchar2
304 ,p_per_information8 IN OUT NOCOPY varchar2
305 ,p_per_information9 IN OUT NOCOPY varchar2
306 ,p_per_information10 IN OUT NOCOPY varchar2
307 ,p_per_information11 IN OUT NOCOPY varchar2
308 ,p_per_information12 IN OUT NOCOPY varchar2
309 ,p_per_information13 IN OUT NOCOPY varchar2
310 ,p_per_information14 IN OUT NOCOPY varchar2
311 ,p_per_information15 IN OUT NOCOPY varchar2
312 ,p_per_information16 IN OUT NOCOPY varchar2
313 ,p_per_information17 IN OUT NOCOPY varchar2
314 ,p_per_information18 IN OUT NOCOPY varchar2
315 ,p_per_information19 IN OUT NOCOPY varchar2
316 ,p_per_information20 IN OUT NOCOPY varchar2
317 ,p_per_information21 IN OUT NOCOPY varchar2
318 ,p_per_information22 IN OUT NOCOPY varchar2
319 ,p_per_information23 IN OUT NOCOPY varchar2
320 ,p_per_information24 IN OUT NOCOPY varchar2
321 ,p_per_information25 IN OUT NOCOPY varchar2
322 ,p_per_information26 IN OUT NOCOPY varchar2
323 ,p_per_information27 IN OUT NOCOPY varchar2
324 ,p_per_information28 IN OUT NOCOPY varchar2
325 ,p_per_information29 IN OUT NOCOPY varchar2
326 ,p_per_information30 IN OUT NOCOPY varchar2
327 ,p_known_as IN OUT NOCOPY varchar2
328 ,p_npw_number IN OUT NOCOPY varchar2) is
329
330 l_date_of_death per_all_people_f.date_of_death%TYPE;
331
332 BEGIN
333 /* Call the overloaded procedure. */
334 get_person_details
335 (p_person_id => p_person_id
336 ,p_session_date => p_session_date
337 ,p_first_name => p_first_name
338 ,p_middle_names => p_middle_names
339 ,p_pre_name_adjunct => p_pre_name_adjunct
340 ,p_suffix => p_suffix
341 ,p_title => p_title
342 ,p_sex => p_sex
343 ,p_date_of_birth => p_date_of_birth
344 ,p_age => p_age
345 ,p_employee_number => p_employee_number
346 ,p_applicant_number => p_applicant_number
347 ,p_title_desc => p_title_desc
348 ,p_national_identifier => p_national_identifier
349 ,p_person_type_id => p_person_type_id
350 ,p_user_person_type => p_user_person_type
351 ,p_system_person_type => p_system_person_type
352 ,p_current_employee_flag => p_current_employee_flag
353 ,p_current_applicant_flag => p_current_applicant_flag
354 ,p_current_emp_or_apl_flag => p_current_emp_or_apl_flag
355 ,p_registered_disabled_flag => p_registered_disabled_flag
356 ,p_attribute_category => p_attribute_category
357 ,p_attribute1 => p_attribute1
358 ,p_attribute2 => p_attribute2
359 ,p_attribute3 => p_attribute3
360 ,p_attribute4 => p_attribute4
361 ,p_attribute5 => p_attribute5
362 ,p_attribute6 => p_attribute6
363 ,p_attribute7 => p_attribute7
364 ,p_attribute8 => p_attribute8
365 ,p_attribute9 => p_attribute9
366 ,p_attribute10 => p_attribute10
367 ,p_attribute11 => p_attribute11
368 ,p_attribute12 => p_attribute12
369 ,p_attribute13 => p_attribute13
370 ,p_attribute14 => p_attribute14
371 ,p_attribute15 => p_attribute15
372 ,p_attribute16 => p_attribute16
373 ,p_attribute17 => p_attribute17
374 ,p_attribute18 => p_attribute18
375 ,p_attribute19 => p_attribute19
376 ,p_attribute20 => p_attribute20
377 ,p_attribute21 => p_attribute21
378 ,p_attribute22 => p_attribute22
379 ,p_attribute23 => p_attribute23
380 ,p_attribute24 => p_attribute24
381 ,p_attribute25 => p_attribute25
382 ,p_attribute26 => p_attribute26
383 ,p_attribute27 => p_attribute27
384 ,p_attribute28 => p_attribute28
385 ,p_attribute29 => p_attribute29
386 ,p_attribute30 => p_attribute30
387 ,p_comment_id => p_comment_id
388 ,p_contact_only => p_contact_only
389 ,p_per_information_category => p_per_information_category
390 ,p_per_information1 => p_per_information1
391 ,p_per_information2 => p_per_information2
392 ,p_per_information3 => p_per_information3
393 ,p_per_information4 => p_per_information4
394 ,p_per_information5 => p_per_information5
395 ,p_per_information6 => p_per_information6
396 ,p_per_information7 => p_per_information7
397 ,p_per_information8 => p_per_information8
398 ,p_per_information9 => p_per_information9
399 ,p_per_information10 => p_per_information10
400 ,p_per_information11 => p_per_information11
401 ,p_per_information12 => p_per_information12
402 ,p_per_information13 => p_per_information13
403 ,p_per_information14 => p_per_information14
404 ,p_per_information15 => p_per_information15
405 ,p_per_information16 => p_per_information16
406 ,p_per_information17 => p_per_information17
407 ,p_per_information18 => p_per_information18
408 ,p_per_information19 => p_per_information19
409 ,p_per_information20 => p_per_information20
410 ,p_per_information21 => p_per_information21
411 ,p_per_information22 => p_per_information22
412 ,p_per_information23 => p_per_information23
413 ,p_per_information24 => p_per_information24
414 ,p_per_information25 => p_per_information25
415 ,p_per_information26 => p_per_information26
416 ,p_per_information27 => p_per_information27
417 ,p_per_information28 => p_per_information28
418 ,p_per_information29 => p_per_information29
419 ,p_per_information30 => p_per_information30
420 ,p_known_as => p_known_as
421 ,p_npw_number => p_npw_number
422 ,p_date_of_death => l_date_of_death);
423
424 END get_person_details;
425
426 -- fix for bug 4957699 starts here.
427 PROCEDURE get_person_details
428 (p_person_id IN NUMBER
429 ,p_session_date IN DATE
430 ,p_first_name IN OUT NOCOPY varchar2
431 ,p_middle_names IN OUT NOCOPY varchar2
432 ,p_pre_name_adjunct IN OUT NOCOPY varchar2
433 ,p_suffix IN OUT NOCOPY varchar2
434 ,p_title IN OUT NOCOPY varchar2
435 ,p_sex IN OUT NOCOPY varchar2
436 ,p_date_of_birth IN OUT NOCOPY DATE
437 ,p_age IN OUT NOCOPY number
438 ,p_employee_number IN OUT NOCOPY varchar2
439 ,p_applicant_number IN OUT NOCOPY varchar2
440 ,p_title_desc IN OUT NOCOPY varchar2
441 ,p_national_identifier IN OUT NOCOPY VARCHAR2
442 ,p_person_type_id IN OUT NOCOPY number
443 ,p_user_person_type IN OUT NOCOPY varchar2
444 ,p_system_person_type IN OUT NOCOPY varchar2
445 ,p_current_employee_flag IN OUT NOCOPY varchar2
446 ,p_current_applicant_flag IN OUT NOCOPY varchar2
447 ,p_current_emp_or_apl_flag IN OUT NOCOPY varchar2
448 ,p_registered_disabled_flag IN OUT NOCOPY varchar2
449 ,p_attribute_category IN OUT NOCOPY varchar2
450 ,p_attribute1 IN OUT NOCOPY varchar2
451 ,p_attribute2 IN OUT NOCOPY varchar2
452 ,p_attribute3 IN OUT NOCOPY varchar2
453 ,p_attribute4 IN OUT NOCOPY varchar2
454 ,p_attribute5 IN OUT NOCOPY varchar2
455 ,p_attribute6 IN OUT NOCOPY varchar2
456 ,p_attribute7 IN OUT NOCOPY varchar2
457 ,p_attribute8 IN OUT NOCOPY varchar2
458 ,p_attribute9 IN OUT NOCOPY varchar2
459 ,p_attribute10 IN OUT NOCOPY varchar2
460 ,p_attribute11 IN OUT NOCOPY varchar2
461 ,p_attribute12 IN OUT NOCOPY varchar2
462 ,p_attribute13 IN OUT NOCOPY varchar2
463 ,p_attribute14 IN OUT NOCOPY varchar2
464 ,p_attribute15 IN OUT NOCOPY varchar2
465 ,p_attribute16 IN OUT NOCOPY varchar2
466 ,p_attribute17 IN OUT NOCOPY varchar2
467 ,p_attribute18 IN OUT NOCOPY varchar2
468 ,p_attribute19 IN OUT NOCOPY varchar2
469 ,p_attribute20 IN OUT NOCOPY varchar2
470 ,p_attribute21 IN OUT NOCOPY varchar2
471 ,p_attribute22 IN OUT NOCOPY varchar2
472 ,p_attribute23 IN OUT NOCOPY varchar2
473 ,p_attribute24 IN OUT NOCOPY varchar2
474 ,p_attribute25 IN OUT NOCOPY varchar2
475 ,p_attribute26 IN OUT NOCOPY varchar2
476 ,p_attribute27 IN OUT NOCOPY varchar2
480 ,p_comment_id IN OUT NOCOPY number
477 ,p_attribute28 IN OUT NOCOPY varchar2
478 ,p_attribute29 IN OUT NOCOPY varchar2
479 ,p_attribute30 IN OUT NOCOPY varchar2
481 ,p_contact_only IN OUT NOCOPY varchar2
482 ,p_per_information_category IN OUT NOCOPY varchar2
483 ,p_per_information1 IN OUT NOCOPY varchar2
484 ,p_per_information2 IN OUT NOCOPY varchar2
485 ,p_per_information3 IN OUT NOCOPY varchar2
486 ,p_per_information4 IN OUT NOCOPY varchar2
487 ,p_per_information5 IN OUT NOCOPY varchar2
488 ,p_per_information6 IN OUT NOCOPY varchar2
489 ,p_per_information7 IN OUT NOCOPY varchar2
490 ,p_per_information8 IN OUT NOCOPY varchar2
491 ,p_per_information9 IN OUT NOCOPY varchar2
492 ,p_per_information10 IN OUT NOCOPY varchar2
493 ,p_per_information11 IN OUT NOCOPY varchar2
494 ,p_per_information12 IN OUT NOCOPY varchar2
495 ,p_per_information13 IN OUT NOCOPY varchar2
496 ,p_per_information14 IN OUT NOCOPY varchar2
497 ,p_per_information15 IN OUT NOCOPY varchar2
498 ,p_per_information16 IN OUT NOCOPY varchar2
499 ,p_per_information17 IN OUT NOCOPY varchar2
500 ,p_per_information18 IN OUT NOCOPY varchar2
501 ,p_per_information19 IN OUT NOCOPY varchar2
502 ,p_per_information20 IN OUT NOCOPY varchar2
503 ,p_per_information21 IN OUT NOCOPY varchar2
504 ,p_per_information22 IN OUT NOCOPY varchar2
505 ,p_per_information23 IN OUT NOCOPY varchar2
506 ,p_per_information24 IN OUT NOCOPY varchar2
507 ,p_per_information25 IN OUT NOCOPY varchar2
508 ,p_per_information26 IN OUT NOCOPY varchar2
509 ,p_per_information27 IN OUT NOCOPY varchar2
510 ,p_per_information28 IN OUT NOCOPY varchar2
511 ,p_per_information29 IN OUT NOCOPY varchar2
512 ,p_per_information30 IN OUT NOCOPY varchar2
513 ,p_known_as IN OUT NOCOPY varchar2
514 ,p_npw_number IN OUT NOCOPY varchar2
515 -- fix for bug 4957699 starts here.
516 ,p_date_of_death IN OUT NOCOPY DATE
517 ) is
518 -- fix for bug 4957699 ends here.
519 cursor get_person is
520 select per.last_name
521 ,per.first_name
522 ,per.middle_names
523 ,per.pre_name_adjunct
524 ,per.suffix
525 ,per.title
526 ,per.sex
527 ,per.date_of_birth
528 ,trunc(months_between(p_session_date, per.date_of_birth)/12) age
529 ,per.employee_number
530 ,per.applicant_number
531 ,per.full_name
532 ,t.meaning title_desc
533 ,per.national_identifier
534 ,per.person_type_id
535 ,pttl.user_person_type
536 ,pt.system_person_type
537 ,per.current_employee_flag
538 ,per.current_applicant_flag
539 ,per.current_emp_or_apl_flag
540 ,per.registered_disabled_flag
541 ,per.attribute_category
542 ,per.attribute1
543 ,per.attribute2
544 ,per.attribute3
545 ,per.attribute4
546 ,per.attribute5
547 ,per.attribute6
548 ,per.attribute7
549 ,per.attribute8
550 ,per.attribute9
551 ,per.attribute10
552 ,per.attribute11
553 ,per.attribute12
554 ,per.attribute13
555 ,per.attribute14
556 ,per.attribute15
557 ,per.attribute16
558 ,per.attribute17
559 ,per.attribute18
560 ,per.attribute19
561 ,per.attribute20
562 ,per.attribute21
563 ,per.attribute22
564 ,per.attribute23
565 ,per.attribute24
566 ,per.attribute25
567 ,per.attribute26
568 ,per.attribute27
569 ,per.attribute28
570 ,per.attribute29
571 ,per.attribute30
572 ,per.comment_id
573 ,per.per_information_category
574 ,per.per_information1
575 ,per.per_information2
576 ,per.per_information3
577 ,per.per_information4
578 ,per.per_information5
579 ,per.per_information6
580 ,per.per_information7
581 ,per.per_information8
582 ,per.per_information9
583 ,per.per_information10
584 ,per.per_information11
585 ,per.per_information12
586 ,per.per_information13
587 ,per.per_information14
588 ,per.per_information15
589 ,per.per_information16
590 ,per.per_information17
591 ,per.per_information18
592 ,per.per_information19
593 ,per.per_information20
594 ,per.per_information21
595 ,per.per_information22
596 ,per.per_information23
597 ,per.per_information24
598 ,per.per_information25
599 ,per.per_information26
600 ,per.per_information27
601 ,per.per_information28
602 ,per.per_information29
603 ,per.per_information30
604 ,per.known_as
605 -- fix for bug 4957699 starts here.
606 ,per.date_of_death
607 -- fix for bug 4957699 starts here.
608 ,npw_number
609 from hr_lookups t
610 ,per_person_types_tl pttl
611 ,per_person_types pt
612 ,per_all_people_f per
613 where pt.person_type_id = pttl.person_type_id
614 and t.lookup_type(+) = 'TITLE'
615 and t.lookup_code(+) = per.title
616 and per.person_type_id = pt.person_type_id
617 and per.person_id = p_person_id
618 and p_session_date between
619 per.effective_start_date and per.effective_end_date
620 and pttl.LANGUAGE = userenv('LANG');
621 --
622 person_row get_person%rowtype;
623 --
624 begin
628 --
625 open get_person;
626 fetch get_person into person_row;
627 close get_person;
629 p_first_name := person_row.first_name;
630 p_middle_names := person_row.middle_names;
631 p_pre_name_adjunct := person_row.pre_name_adjunct;
632 p_suffix := person_row.suffix;
633 p_title := person_row.title;
634 p_sex := person_row.sex;
635 p_date_of_birth := person_row.date_of_birth;
636 p_age := person_row.age;
637 p_employee_number := person_row.employee_number;
638 p_applicant_number := person_row.applicant_number;
639 p_title_desc := person_row.title_desc;
640 p_national_identifier := person_row.national_identifier;
641 p_person_type_id := person_row.person_type_id;
642 p_user_person_type := --person_row.user_person_type;
643 hr_person_type_usage_info.get_user_person_type
644 (p_session_date,p_person_id);
645 p_system_person_type := person_row.system_person_type;
646 p_current_employee_flag := person_row.current_employee_flag;
647 p_current_applicant_flag := person_row.current_applicant_flag;
648 p_current_emp_or_apl_flag := person_row.current_emp_or_apl_flag;
649 p_registered_disabled_flag := person_row.registered_disabled_flag;
650 p_attribute_category := person_row.attribute_category;
651 p_attribute1 := person_row.attribute1;
652 p_attribute2 := person_row.attribute2;
653 p_attribute3 := person_row.attribute3;
654 p_attribute4 := person_row.attribute4;
655 p_attribute5 := person_row.attribute5;
656 p_attribute6 := person_row.attribute6;
657 p_attribute7 := person_row.attribute7;
658 p_attribute8 := person_row.attribute8;
659 p_attribute9 := person_row.attribute9;
660 p_attribute10 := person_row.attribute10;
661 p_attribute11 := person_row.attribute11;
662 p_attribute12 := person_row.attribute12;
663 p_attribute13 := person_row.attribute13;
664 p_attribute14 := person_row.attribute14;
665 p_attribute15 := person_row.attribute15;
666 p_attribute16 := person_row.attribute16;
667 p_attribute17 := person_row.attribute17;
668 p_attribute18 := person_row.attribute18;
669 p_attribute19 := person_row.attribute19;
670 p_attribute20 := person_row.attribute20;
671 p_attribute21 := person_row.attribute21;
672 p_attribute22 := person_row.attribute22;
673 p_attribute23 := person_row.attribute23;
674 p_attribute24 := person_row.attribute24;
675 p_attribute25 := person_row.attribute25;
679 p_attribute29 := person_row.attribute29;
676 p_attribute26 := person_row.attribute26;
677 p_attribute27 := person_row.attribute27;
678 p_attribute28 := person_row.attribute28;
680 p_attribute30 := person_row.attribute30;
681 p_comment_id := person_row.comment_id;
682 p_per_information_category := person_row.per_information_category;
683 p_per_information1 := person_row.per_information1;
684 p_per_information2 := person_row.per_information2;
685 p_per_information3 := person_row.per_information3;
686 p_per_information4 := person_row.per_information4;
687 p_per_information5 := person_row.per_information5;
688 p_per_information6 := person_row.per_information6;
689 p_per_information7 := person_row.per_information7;
690 p_per_information8 := person_row.per_information8;
691 p_per_information9 := person_row.per_information9;
692 p_per_information10 := person_row.per_information10;
693 p_per_information11 := person_row.per_information11;
694 p_per_information12 := person_row.per_information12;
695 p_per_information13 := person_row.per_information13;
696 p_per_information14 := person_row.per_information14;
697 p_per_information15 := person_row.per_information15;
698 p_per_information16 := person_row.per_information16;
699 p_per_information17 := person_row.per_information17;
700 p_per_information18 := person_row.per_information18;
701 p_per_information19 := person_row.per_information19;
702 p_per_information20 := person_row.per_information20;
703 p_per_information21 := person_row.per_information21;
704 p_per_information22 := person_row.per_information22;
705 p_per_information23 := person_row.per_information23;
706 p_per_information24 := person_row.per_information24;
707 p_per_information25 := person_row.per_information25;
708 p_per_information26 := person_row.per_information26;
709 p_per_information27 := person_row.per_information27;
710 p_per_information28 := person_row.per_information28;
711 p_per_information29 := person_row.per_information29;
712 p_per_information30 := person_row.per_information30;
713 p_known_as := person_row.known_as;
714 p_npw_number := person_row.npw_number;
715 -- fix for bug 4957699 starts here.
716 p_date_of_death := person_row.date_of_death;
717 -- fix for bug 4957699 ends here.
718 --
719 p_contact_only := contact_only(p_person_id);
720 end get_person_details;
721
722
723 FUNCTION multiple_contacts(p_person_id number) return VARCHAR2 is
724
725 -- Bug 820655
726 -- this function has been created from contact_only to check
727 -- for for more than one relationships
728
729 -- Following cursor will return the number relationships
730 -- existing for a contact. ( Ref Bug # 622399 )
731
732 CURSOR csr_count_relationships IS
733 select count(*)
734 from per_contact_relationships
735 where contact_person_id = p_person_id;
736
737 l_exists varchar2(1);
738 l_count number;
739
740 begin
741
742 -- Bug 2017198: changed the check, if check is done after the delete of rship we need only
743 -- see that no rows remain for the person as the contact_person_id
744 -- Mirrors don't necessarily exist so no need to check against person_id
745 open csr_count_relationships;
746 fetch csr_count_relationships into l_count;
747 close csr_count_relationships;
748 if l_count >= 1 then
749 return('Y');
750 end if;
751 return('N');
752 end multiple_contacts;
753
754 FUNCTION contact_only(p_person_id number) return VARCHAR2 is
755
756 --
757
758 /* Drive of person type usages table */
759 /*
760 CURSOR C is
761 select 'Y'
762 from per_all_people_f papf, per_person_types ppt
763 where papf.person_id = p_person_id
764 and papf.person_type_id = ppt.person_type_id
765 and ppt.system_person_type = 'OTHER'
766 and papf.effective_end_date = hr_general.end_of_time;*/
767 --
768 --Bug 3179566 Start here
769 --Description : Modified the cursor to include all the varriants of the
770 -- system person types 'EMP', 'APL','CWK'.
771 --
772
773 CURSOR c IS
774 SELECT 'Y'
775 FROM per_person_type_usages_f ptu,
776 per_person_types ppt
777 WHERE ( ppt.system_person_type like '%EMP%'
778 or ppt.system_person_type like '%APL%'
779 or ppt.system_person_type like '%CWK%')
780 AND ppt.person_type_Id = ptu.person_type_id
781 AND ptu.person_id = p_person_id
782 AND ptu.effective_end_Date = hr_general.end_of_time;
783
784 --
785 --Bug 3179566 End Here
786 --
787 l_exists varchar2(1);
788 --
789 begin
790
791 open C;
792 fetch C into l_exists;
793 close C;
794 if l_exists = 'Y' then
795 return('N');
796 end if;
797 return('Y');
798
799 end contact_only;
800
801 PROCEDURE get_default_ctype(p_business_group_id number
802 ,p_def_type_id IN OUT NOCOPY number
803 ,p_def_type IN OUT NOCOPY varchar2
804 ,p_def_sys_type IN OUT NOCOPY varchar2) is
805 CURSOR c is
806 select pt.person_type_id
807 , pttl.user_person_type
808 , pt.system_person_type
809 from per_person_types_tl pttl,
810 per_person_types pt
811 where pt.system_person_type = 'OTHER'
812 and pt.default_flag = 'Y'
813 and pt.active_flag = 'Y'
814 and pt.business_group_id = p_business_group_id
815 and pt.person_type_id = pttl.person_type_id
816 and pttl.LANGUAGE = userenv('LANG');
817 --
818 begin
819 open c;
820 fetch c into p_def_type_id, p_def_type, p_def_sys_type;
821 close c;
822 end get_default_ctype;
823
824 FUNCTION get_application(p_form_name VARCHAR2) return VARCHAR2 is
825 l_appl_short_name varchar2(30);
826 --
827 CURSOR application is
828 select a.application_short_name
829 from fnd_application a
830 , fnd_form f
831 where a.application_id = f.application_id
832 and f.form_name = p_form_name;
833 --
834 begin
835 open application;
836 fetch application into l_appl_short_name;
837 close application;
838 --
839 return(l_appl_short_name);
840 end;
841 --
842 FUNCTION validate_address(p_contact_id NUMBER) RETURN BOOLEAN
843 is
844 l_dummy VARCHAR2(1);
845 cursor address_exists
846 is
847 select 'Y'
848 from per_addresses pa
849 where pa.person_id = p_contact_id;
850 --
851 Begin
852 open address_exists;
853 fetch address_exists into l_dummy;
854 if address_exists%NOTFOUND
855 then
856 hr_utility.set_message(801,'PAY_ASSIGNEE_NO_ADDRESS');
857 return FALSE;
858 end if;
859 close address_exists;
860 RETURN TRUE;
861 end validate_address;
862 --
863 PROCEDURE check_ppm(p_contact_id NUMBER
864 ,p_contact_relationship_id NUMBER
865 ,p_mode VARCHAR2)
866 is
867 l_dummy VARCHAR2(1);
868 l_contact_relationship_id NUMBER;
869 l_contact_id NUMBER;
870 --
871 Cursor get_mirror is
872 select pcr1.contact_relationship_id
873 , pcr1.contact_person_id
874 from per_contact_relationships pcr1
875 where exists (select 1
876 from per_contact_relationships pcr2
877 where contact_relationship_id = p_contact_relationship_id
878 and pcr1.contact_person_id = pcr2.person_id
879 );
880 --
881 Cursor check_garnishor(l_contact_id NUMBER,l_contact_relationship_id NUMBER)
882 is select '1'
883 from pay_personal_payment_methods_f ppm
884 , per_assignments_f paf
885 , per_contact_relationships pcr
886 , fnd_sessions f
887 where ppm.payee_type = 'P'
888 and ppm.payee_id = l_contact_id
889 and ppm.assignment_id = paf.assignment_id
890 and paf.person_id = pcr.person_id
891 and pcr.contact_relationship_id = l_contact_relationship_id
892 and f.session_id = userenv('sessionid')
893 and f.effective_date between
894 ppm.effective_start_date and ppm.effective_end_date;
895 Begin
896 open get_mirror;
897 fetch get_mirror into l_contact_relationship_id,l_contact_id;
898 close get_mirror;
899 --
900 open check_garnishor(p_contact_id,p_contact_relationship_id);
901 fetch check_garnishor into l_dummy;
902 if check_garnishor%FOUND
903 then
904 if p_mode = 'D' -- delete message
905 then
906 hr_utility.set_message(801,'PAY_GARNISHOR_PAYMENT_METHOD');
907 hr_utility.raise_error;
908 else
909 hr_utility.set_message(801,'HR_7874_GARNISHMENT_IS_PAYEE');
910 hr_utility.raise_error;
911 end if;
912 end if;
913 close check_garnishor;
914 open check_garnishor(l_contact_id,l_contact_relationship_id);
915 fetch check_garnishor into l_dummy;
916 if check_garnishor%FOUND
917 then
918 if p_mode = 'D' -- delete message
919 then
920 hr_utility.set_message(801,'PAY_GARNISHOR_PAYMENT_METHOD');
921 hr_utility.raise_error;
922 else
923 hr_utility.set_message(801,'HR_7874_GARNISHMENT_IS_PAYEE');
924 hr_utility.raise_error;
925 end if;
926 end if;
927 close check_garnishor;
928 end check_ppm;
929 --
930 --
931 PROCEDURE check_beneficiary(p_person_id NUMBER, p_contact_id NUMBER) is
932 --
933 -- when unchecking the beneficiary flag in the contact form, check that
934 -- the contact is not designated as a beneficiary for the person.
935 --
936 cursor cur_ben is
937 select 'Y'
938 from
939 fnd_sessions fnd,
940 per_assignments_f asg,
941 pay_element_entries_f ent,
942 ben_beneficiaries_f ben
943 where ben.source_id = p_contact_id
944 and ben.source_type = 'P'
945 and ben.element_entry_id = ent.element_entry_id
946 and ent.assignment_id = asg.assignment_id
947 and asg.person_id + 0 = p_person_id
948 and fnd.session_id = userenv('sessionid')
949 --
950 -- check that the contact is currently designated as a beneficiary
951 -- or is designated as a beneficiary in the future.
952 --
953 and (fnd.effective_date between asg.effective_start_date
954 and asg.effective_end_date
955 or fnd.effective_date < asg.effective_start_date)
956 and (fnd.effective_date between ent.effective_start_date
957 and ent.effective_end_date
958 or fnd.effective_date < ent.effective_start_date)
959 and (fnd.effective_date between ben.effective_start_date
960 and ben.effective_end_date
961 or fnd.effective_date < ent.effective_start_date);
962 --
963 l_check varchar2(1);
964 --
965 begin
966 open cur_ben;
967 fetch cur_ben into l_check;
968 close cur_ben;
969 --
970 if l_check = 'Y' then
971 hr_utility.set_message(801,'HR_CONTACT_MARKED_AS_BENF');
972 hr_utility.raise_error;
973 end if;
974 --
975 end check_beneficiary;
976 --
977 PROCEDURE check_dependent (p_contact_relationship_id NUMBER) is
978 cursor cur_dep is
979 select 'Y'
980 from
981 fnd_sessions fnd,
982 ben_covered_dependents_f dep
983 where dep.contact_relationship_id = p_contact_relationship_id
984 and fnd.session_id = userenv('sessionid')
985 and (fnd.effective_date between dep.effective_start_date
986 and dep.effective_end_date
987 or fnd.effective_date < dep.effective_start_date);
988 --
989 l_check varchar2(1);
990 --
991 begin
992 open cur_dep;
993 fetch cur_dep into l_check;
994 close cur_dep;
995 --
996 if l_check = 'Y' then
997 hr_utility.set_message(801,'HR_CONTACT_MARKED_AS_COVDEP');
998 hr_utility.raise_error;
999 end if;
1000 --
1001 end check_dependent;
1002 --
1003 --
1004 END HR_CONTACT_RELATIONSHIPS;
[Home] [Help]