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