[Home] [Help]
Skip to content
PACKAGE BODY: APPS.HR_PHONE_API
Source
1 Package Body hr_phone_api as
2 /* $Header: pephnapi.pkb 120.0.12010000.2 2009/03/12 10:25:20 dparthas ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_phone_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------< create_phone >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_phone
13 (p_date_from in date,
14 p_date_to in date default null,
15 p_phone_type in varchar2,
16 p_phone_number in varchar2,
17 p_parent_id in number default null, -- HR/TCA merge
18 p_parent_table in varchar2 default null, --
19 p_attribute_category in varchar2 default null,
20 p_attribute1 in varchar2 default null,
21 p_attribute2 in varchar2 default null,
22 p_attribute3 in varchar2 default null,
23 p_attribute4 in varchar2 default null,
24 p_attribute5 in varchar2 default null,
25 p_attribute6 in varchar2 default null,
26 p_attribute7 in varchar2 default null,
27 p_attribute8 in varchar2 default null,
28 p_attribute9 in varchar2 default null,
29 p_attribute10 in varchar2 default null,
30 p_attribute11 in varchar2 default null,
31 p_attribute12 in varchar2 default null,
32 p_attribute13 in varchar2 default null,
33 p_attribute14 in varchar2 default null,
34 p_attribute15 in varchar2 default null,
35 p_attribute16 in varchar2 default null,
36 p_attribute17 in varchar2 default null,
37 p_attribute18 in varchar2 default null,
38 p_attribute19 in varchar2 default null,
39 p_attribute20 in varchar2 default null,
40 p_attribute21 in varchar2 default null,
41 p_attribute22 in varchar2 default null,
42 p_attribute23 in varchar2 default null,
43 p_attribute24 in varchar2 default null,
44 p_attribute25 in varchar2 default null,
45 p_attribute26 in varchar2 default null,
46 p_attribute27 in varchar2 default null,
47 p_attribute28 in varchar2 default null,
48 p_attribute29 in varchar2 default null,
49 p_attribute30 in varchar2 default null,
50 p_validate in boolean default false,
51 p_effective_date in date,
52 p_party_id in number default null, -- HR/TCA merge
53 p_validity in varchar2 default null,
54 p_object_version_number out nocopy number,
55 p_phone_id out nocopy number)
56 is
57 --
58 l_proc varchar2(72) := g_package||'create_phone';
59 l_object_version_number number;
60 l_phone_id number;
64 --
61 --
62 begin
63 hr_utility.set_location('Entering:'|| l_proc, 5);
65 -- Issue a savepoint.
66 --
67 savepoint create_phone;
68 --
69 hr_utility.set_location(l_proc, 7);
70 --
71 -- Process Logic
72 --
73 --
74 -- Start of API User Hook for the before hook of create_phone.
75 --
76 -- Start of Bug Fix for 2396117
77 begin
78 hr_phone_bk1.create_phone_b
79 (p_date_from => p_date_from,
80 p_date_to => p_date_to ,
81 p_phone_type => p_phone_type,
82 p_phone_number => p_phone_number ,
83 p_parent_id => p_parent_id ,
84 p_parent_table => p_parent_table ,
85 p_attribute_category => p_attribute_category ,
86 p_attribute1 => p_attribute1 ,
87 p_attribute2 => p_attribute2 ,
88 p_attribute3 => p_attribute3 ,
89 p_attribute4 => p_attribute4 ,
90 p_attribute5 => p_attribute5 ,
91 p_attribute6 => p_attribute6 ,
92 p_attribute7 => p_attribute7 ,
93 p_attribute8 => p_attribute8 ,
94 p_attribute9 => p_attribute9 ,
95 p_attribute10 => p_attribute10 ,
96 p_attribute11 => p_attribute11 ,
97 p_attribute12 => p_attribute12 ,
98 p_attribute13 => p_attribute13 ,
99 p_attribute14 => p_attribute14 ,
100 p_attribute15 => p_attribute15 ,
101 p_attribute16 => p_attribute16 ,
102 p_attribute17 => p_attribute17 ,
103 p_attribute18 => p_attribute18 ,
104 p_attribute19 => p_attribute19 ,
105 p_attribute20 => p_attribute20 ,
106 p_attribute21 => p_attribute21 ,
107 p_attribute22 => p_attribute22 ,
108 p_attribute23 => p_attribute23 ,
109 p_attribute24 => p_attribute24 ,
110 p_attribute25 => p_attribute25 ,
111 p_attribute26 => p_attribute26 ,
112 p_attribute27 => p_attribute27 ,
113 p_attribute28 => p_attribute28 ,
114 p_attribute29 => p_attribute29 ,
115 p_attribute30 => p_attribute30 ,
116 p_effective_date => p_effective_date ,
117 p_party_id => p_party_id , -- HR/TCA merge
118 p_validity => p_validity);
119 exception
120 when hr_api.cannot_find_prog_unit then
121 hr_api.cannot_find_prog_unit_error
122 (p_module_name => 'CREATE_PHONE',
123 p_hook_type => 'BP'
124 );
125 end;
126 -- End of Fix for Bug 2396117
127 --
128 per_phn_ins.ins
129 (p_phone_id => l_phone_id
130 ,p_date_from => trunc(p_date_from)
131 ,p_date_to => trunc(p_date_to)
132 ,p_phone_type => p_phone_type
133 ,p_phone_number => p_phone_number
134 ,p_parent_id => p_parent_id
135 ,p_parent_table => p_parent_table
136 ,p_attribute_category => p_attribute_category
137 ,p_attribute1 => p_attribute1
138 ,p_attribute2 => p_attribute2
139 ,p_attribute3 => p_attribute3
140 ,p_attribute4 => p_attribute4
141 ,p_attribute5 => p_attribute5
142 ,p_attribute6 => p_attribute6
143 ,p_attribute7 => p_attribute7
144 ,p_attribute8 => p_attribute8
145 ,p_attribute9 => p_attribute9
146 ,p_attribute10 => p_attribute10
147 ,p_attribute11 => p_attribute11
148 ,p_attribute12 => p_attribute12
149 ,p_attribute13 => p_attribute13
150 ,p_attribute14 => p_attribute14
151 ,p_attribute15 => p_attribute15
152 ,p_attribute16 => p_attribute16
153 ,p_attribute17 => p_attribute17
154 ,p_attribute18 => p_attribute18
155 ,p_attribute19 => p_attribute19
156 ,p_attribute20 => p_attribute20
157 ,p_attribute21 => p_attribute21
158 ,p_attribute22 => p_attribute22
159 ,p_attribute23 => p_attribute23
160 ,p_attribute24 => p_attribute24
161 ,p_attribute25 => p_attribute25
162 ,p_attribute26 => p_attribute26
163 ,p_attribute27 => p_attribute27
164 ,p_attribute28 => p_attribute28
165 ,p_attribute29 => p_attribute29
166 ,p_attribute30 => p_attribute30
167 ,p_object_version_number => l_object_version_number
168 ,p_validate => p_validate
169 ,p_effective_date => p_effective_date
170 ,p_party_id => p_party_id -- HR/TCA merge
171 ,p_validity => p_validity
172 );
173 --
174 hr_utility.set_location(l_proc, 8);
175 --
176 -- Start of API User Hook for the after hook of create_phone
177 --
178 -- Start of Fix for Bug 2396117
179 --
183 p_date_to => p_date_to ,
180 begin
181 hr_phone_bk1.create_phone_a
182 (p_date_from => p_date_from,
184 p_phone_type => p_phone_type,
185 p_phone_number => p_phone_number ,
186 p_parent_id => p_parent_id ,
187 p_parent_table => p_parent_table ,
188 p_attribute_category => p_attribute_category ,
189 p_attribute1 => p_attribute1 ,
190 p_attribute2 => p_attribute2 ,
191 p_attribute3 => p_attribute3 ,
192 p_attribute4 => p_attribute4 ,
193 p_attribute5 => p_attribute5 ,
194 p_attribute6 => p_attribute6 ,
195 p_attribute7 => p_attribute7 ,
196 p_attribute8 => p_attribute8 ,
197 p_attribute9 => p_attribute9 ,
198 p_attribute10 => p_attribute10 ,
199 p_attribute11 => p_attribute11 ,
200 p_attribute12 => p_attribute12 ,
201 p_attribute13 => p_attribute13 ,
202 p_attribute14 => p_attribute14 ,
203 p_attribute15 => p_attribute15 ,
204 p_attribute16 => p_attribute16 ,
205 p_attribute17 => p_attribute17 ,
206 p_attribute18 => p_attribute18 ,
207 p_attribute19 => p_attribute19 ,
208 p_attribute20 => p_attribute20 ,
209 p_attribute21 => p_attribute21 ,
210 p_attribute22 => p_attribute22 ,
211 p_attribute23 => p_attribute23 ,
212 p_attribute24 => p_attribute24 ,
213 p_attribute25 => p_attribute25 ,
214 p_attribute26 => p_attribute26 ,
215 p_attribute27 => p_attribute27 ,
216 p_attribute28 => p_attribute28 ,
217 p_attribute29 => p_attribute29 ,
218 p_attribute30 => p_attribute30 ,
219 p_effective_date => p_effective_date,
220 p_object_version_number => l_object_version_number,
221 p_phone_id => l_phone_id ,
222 p_party_id => p_party_id , -- HR/TCA merge
223 p_validity => p_validity);
224
225 exception
226 when hr_api.cannot_find_prog_unit then
227 hr_api.cannot_find_prog_unit_error
228 (p_module_name => 'CREATE_PHONE',
229 p_hook_type => 'AP'
230 );
231 end;
232 -- End of Fix for Bug 2396117
233 --
234 -- End of API User Hook for the after hook of create_phone.
235 --
236 --
237 -- When in validation only mode raise the Validate_Enabled exception
238 --
239 if p_validate then
240 raise hr_api.validate_enabled;
241 end if;
242 --
243 p_object_version_number := l_object_version_number;
244 p_phone_id := l_phone_id;
245 --
246 hr_utility.set_location(' Leaving:'||l_proc, 11);
247 exception
248 when hr_api.validate_enabled then
249 --
250 -- As the Validate_Enabled exception has been raised
251 -- we must rollback to the savepoint
252 --
253 ROLLBACK TO create_phone;
254 --
255 -- Only set output warning arguments
256 -- (Any key or derived arguments must be set to null
257 -- when validation only mode is being used.)
258 --
259 p_phone_id := null;
260 p_object_version_number := null;
261 --
262 --
263 when others then
264 --
265 -- A validation or unexpected error has occurred
266 --
267 -- Added as part of fix to bug 632479
268 --
269 p_phone_id := null;
270 p_object_version_number := null;
271
272 ROLLBACK TO create_phone;
273 raise;
274 --
275 -- End of fix.
276 --
277 end create_phone;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |----------------------------< update_phone >------------------------------|
281 -- ----------------------------------------------------------------------------
282 --
283 procedure update_phone
284 (p_phone_id in number,
285 p_date_from in date default hr_api.g_date,
286 p_date_to in date default hr_api.g_date,
287 p_phone_type in varchar2 default hr_api.g_varchar2,
288 p_phone_number in varchar2 default hr_api.g_varchar2,
289 p_attribute_category in varchar2 default hr_api.g_varchar2,
290 p_attribute1 in varchar2 default hr_api.g_varchar2,
291 p_attribute2 in varchar2 default hr_api.g_varchar2,
292 p_attribute3 in varchar2 default hr_api.g_varchar2,
293 p_attribute4 in varchar2 default hr_api.g_varchar2,
294 p_attribute5 in varchar2 default hr_api.g_varchar2,
295 p_attribute6 in varchar2 default hr_api.g_varchar2,
296 p_attribute7 in varchar2 default hr_api.g_varchar2,
297 p_attribute8 in varchar2 default hr_api.g_varchar2,
298 p_attribute9 in varchar2 default hr_api.g_varchar2,
299 p_attribute10 in varchar2 default hr_api.g_varchar2,
300 p_attribute11 in varchar2 default hr_api.g_varchar2,
301 p_attribute12 in varchar2 default hr_api.g_varchar2,
302 p_attribute13 in varchar2 default hr_api.g_varchar2,
303 p_attribute14 in varchar2 default hr_api.g_varchar2,
304 p_attribute15 in varchar2 default hr_api.g_varchar2,
305 p_attribute16 in varchar2 default hr_api.g_varchar2,
306 p_attribute17 in varchar2 default hr_api.g_varchar2,
307 p_attribute18 in varchar2 default hr_api.g_varchar2,
308 p_attribute19 in varchar2 default hr_api.g_varchar2,
309 p_attribute20 in varchar2 default hr_api.g_varchar2,
310 p_attribute21 in varchar2 default hr_api.g_varchar2,
311 p_attribute22 in varchar2 default hr_api.g_varchar2,
312 p_attribute23 in varchar2 default hr_api.g_varchar2,
313 p_attribute24 in varchar2 default hr_api.g_varchar2,
314 p_attribute25 in varchar2 default hr_api.g_varchar2,
315 p_attribute26 in varchar2 default hr_api.g_varchar2,
316 p_attribute27 in varchar2 default hr_api.g_varchar2,
317 p_attribute28 in varchar2 default hr_api.g_varchar2,
318 p_attribute29 in varchar2 default hr_api.g_varchar2,
319 p_attribute30 in varchar2 default hr_api.g_varchar2,
320 p_object_version_number in out nocopy number,
321 p_validate in boolean default false,
322 p_effective_date in date,
323 p_party_id in number default hr_api.g_number,
324 p_validity in varchar2 default hr_api.g_varchar2
325 )
326
327 is
328 --
329 -- Declare cursors and local variables
330 --
331 l_object_version_number per_phones.object_version_number%TYPE;
332 l_object_version_number_temp per_phones.object_version_number%TYPE;
333 l_proc varchar2(72) := g_package||'update_phone';
334 l_temp_ovn number := p_object_version_number;
335 --
336 begin
337 hr_utility.set_location('Entering:'|| l_proc, 5);
338 --
339 -- Issue a savepoint.
340 --
341 savepoint update_phone;
342 hr_utility.set_location(l_proc, 6);
343 --
344 l_object_version_number_temp := p_object_version_number;
345 l_object_version_number := p_object_version_number;
346 --
347 -- Start of API User Hook for the before hook of update_phone.
348 --
349 -- Start of Fix for Bug 2396117
350 begin
351 hr_phone_bk2.update_phone_b
352 (p_phone_id => p_phone_id,
353 p_date_from => p_date_from,
354 p_date_to => p_date_to ,
355 p_phone_type => p_phone_type,
356 p_phone_number => p_phone_number ,
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_object_version_number => p_object_version_number,
389 p_effective_date => p_effective_date ,
390 p_validity => p_validity);
391 exception
392 when hr_api.cannot_find_prog_unit then
393 hr_api.cannot_find_prog_unit_error
394 (p_module_name => 'UPDATE_PHONE',
395 p_hook_type => 'BP'
396 );
397 end;
398 --
399 -- End of Fix for Bug 2396117
400 --
401 -- Update Phone details.
402 --
403 per_phn_upd.upd
404 (p_phone_id => p_phone_id
405 ,p_date_from => trunc(p_date_from)
406 ,p_date_to => trunc(p_date_to)
407 ,p_phone_type => p_phone_type
408 ,p_phone_number => p_phone_number
409 ,p_attribute_category => p_attribute_category
410 ,p_attribute1 => p_attribute1
411 ,p_attribute2 => p_attribute2
412 ,p_attribute3 => p_attribute3
413 ,p_attribute4 => p_attribute4
414 ,p_attribute5 => p_attribute5
415 ,p_attribute6 => p_attribute6
416 ,p_attribute7 => p_attribute7
417 ,p_attribute8 => p_attribute8
418 ,p_attribute9 => p_attribute9
419 ,p_attribute10 => p_attribute10
420 ,p_attribute11 => p_attribute11
421 ,p_attribute12 => p_attribute12
422 ,p_attribute13 => p_attribute13
423 ,p_attribute14 => p_attribute14
424 ,p_attribute15 => p_attribute15
425 ,p_attribute16 => p_attribute16
426 ,p_attribute17 => p_attribute17
427 ,p_attribute18 => p_attribute18
428 ,p_attribute19 => p_attribute19
429 ,p_attribute20 => p_attribute20
430 ,p_attribute21 => p_attribute21
431 ,p_attribute22 => p_attribute22
432 ,p_attribute23 => p_attribute23
433 ,p_attribute24 => p_attribute24
434 ,p_attribute25 => p_attribute25
435 ,p_attribute26 => p_attribute26
436 ,p_attribute27 => p_attribute27
437 ,p_attribute28 => p_attribute28
438 ,p_attribute29 => p_attribute29
439 ,p_attribute30 => p_attribute30
440 ,p_object_version_number => l_object_version_number
441 ,p_validate => p_validate
442 ,p_effective_date => p_effective_date
443 ,p_party_id => p_party_id
444 ,p_validity => p_validity
445 );
446 --
447 hr_utility.set_location(l_proc, 8);
448 --
449 -- Start of API User Hook for the after hook of update_phone
450 -- Start of Fix for Bug 2396117
451 --
452 begin
453 hr_phone_bk2.update_phone_a
454 (p_phone_id => p_phone_id,
455 p_date_from => p_date_from,
456 p_date_to => p_date_to ,
457 p_phone_type => p_phone_type,
458 p_phone_number => p_phone_number ,
459 p_attribute_category => p_attribute_category ,
460 p_attribute1 => p_attribute1 ,
461 p_attribute2 => p_attribute2 ,
462 p_attribute3 => p_attribute3 ,
463 p_attribute4 => p_attribute4 ,
464 p_attribute5 => p_attribute5 ,
465 p_attribute6 => p_attribute6 ,
466 p_attribute7 => p_attribute7 ,
467 p_attribute8 => p_attribute8 ,
468 p_attribute9 => p_attribute9 ,
469 p_attribute10 => p_attribute10 ,
470 p_attribute11 => p_attribute11 ,
471 p_attribute12 => p_attribute12 ,
472 p_attribute13 => p_attribute13 ,
473 p_attribute14 => p_attribute14 ,
474 p_attribute15 => p_attribute15 ,
475 p_attribute16 => p_attribute16 ,
476 p_attribute17 => p_attribute17 ,
477 p_attribute18 => p_attribute18 ,
478 p_attribute19 => p_attribute19 ,
479 p_attribute20 => p_attribute20 ,
480 p_attribute21 => p_attribute21 ,
481 p_attribute22 => p_attribute22 ,
482 p_attribute23 => p_attribute23 ,
483 p_attribute24 => p_attribute24 ,
484 p_attribute25 => p_attribute25 ,
485 p_attribute26 => p_attribute26 ,
486 p_attribute27 => p_attribute27 ,
487 p_attribute28 => p_attribute28 ,
488 p_attribute29 => p_attribute29 ,
489 p_attribute30 => p_attribute30 ,
490 p_object_version_number => l_object_version_number,
491 p_effective_date => p_effective_date ,
492 p_validity => p_validity);
493
494 exception
495 when hr_api.cannot_find_prog_unit then
496 hr_api.cannot_find_prog_unit_error
497 (p_module_name => 'UPDATE_PHONE',
498 p_hook_type => 'AP'
499 );
500 end;
501 --
502 -- End of Fix for Bug 2396117
503 -- End of API User Hook for the after hook of update_phone.
504 --
505 --
506 -- When in validation only mode raise the Validate_Enabled exception
507 --
508 if p_validate then
509 raise hr_api.validate_enabled;
510 end if;
511 --
512 p_object_version_number := l_object_version_number;
513 hr_utility.set_location(' Leaving:'||l_proc, 11);
514 exception
515 when hr_api.validate_enabled then
516 --
517 -- As the Validate_Enabled exception has been raised
518 -- we must rollback to the savepoint
519 --
520 ROLLBACK TO update_phone;
521 --
522 -- Only set output warning arguments
523 -- (Any key or derived arguments must be set to null
524 -- when validation only mode is being used.)
525 --
526 p_object_version_number := l_object_version_number_temp;
527 --
528 when others then
529 --
530 -- A validation or unexpected error has occurred
531 --
532 -- Added as part of fix to bug 632479
533 --
534 p_object_version_number := l_temp_ovn;
535 ROLLBACK TO update_phone;
536 raise;
537 --
538 -- End of fix.
539 --
540 end update_phone;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |----------------------------< delete_phone >------------------------------|
544 -- ----------------------------------------------------------------------------
545 --
546 procedure delete_phone
547 (p_validate in boolean default false
548 ,p_phone_id in number
549 ,p_object_version_number in number
550 ) is
551 --
552 -- dparthas
553
554 CURSOR get_person_info IS
555 select pa.person_id
556 from per_phones pp, per_all_people_f pa
557 where pa.party_id = pp.party_id
558 and phone_id = p_phone_id;
559
560 l_person_id number := -1;
561
562 -- dparthas
563
564 l_proc varchar2(72) := g_package||'delete_phone';
565
566 --
567 begin
568 hr_utility.set_location('Entering:'|| l_proc, 5);
569
570 --dparthas
571 OPEN get_person_info;
572 FETCH get_person_info INTO l_person_id;
573 CLOSE get_person_info;
574 --dparthas
575 --
576 -- Issue a savepoint.
577 --
578 savepoint delete_phone;
579 --
580 hr_utility.set_location(l_proc, 7);
581 --
582 -- Process Logic
583 --
584 --
585 -- Start of API User Hook for the before hook of delete_phone.
586 -- Start of Fix for Bug 2396117
587 --
588 begin
589 hr_phone_bk3.delete_phone_b
590 (p_phone_id => p_phone_id
591 ,p_object_version_number => p_object_version_number
592 );
593 exception
594 when hr_api.cannot_find_prog_unit then
595 hr_api.cannot_find_prog_unit_error
596 (p_module_name => 'DELETE_PHONE',
597 p_hook_type => 'BP'
598 );
599 end;
600 --
601 -- End of Fix for Bug 2396117
602
603 per_phn_del.del
604 (p_phone_id => p_phone_id
605 ,p_object_version_number => p_object_version_number
606 ,p_validate => false);
607 --
608 hr_utility.set_location(l_proc, 8);
609 --
610 -- Start of API User Hook for the after hook of delete_phone
611 -- Start of Fix for Bug 2396117
612 --
613 begin
614 hr_phone_bk3.delete_phone_a
615 (p_phone_id => p_phone_id
616 ,p_object_version_number => p_object_version_number
617 ,p_person_id => l_person_id
618 );
619 exception
620 when hr_api.cannot_find_prog_unit then
621 hr_api.cannot_find_prog_unit_error
622 (p_module_name => 'DELETE_PHONE',
623 p_hook_type => 'AP'
624 );
625 end;
626 -- End of Fix for Bug 2396117
627 --
628 -- End of API User Hook for the after hook of delete_phone.
629 --
630 --
631 -- When in validation only mode raise the Validate_Enabled exception
632 --
633 if p_validate then
634 raise hr_api.validate_enabled;
635 end if;
636 --
637 hr_utility.set_location(' Leaving:'||l_proc, 11);
638 exception
639 when hr_api.validate_enabled then
640 --
641 -- As the Validate_Enabled exception has been raised
642 -- we must rollback to the savepoint
643 --
644 ROLLBACK TO delete_phone;
645 --
646 --
647 when others then
648 --
649 -- A validation or unexpected error has occurred
650 --
651 -- Added as part of fix to bug 632479
652 --
653 ROLLBACK TO delete_phone;
654 raise;
655 --
656 -- End of fix.
657 --
658 end delete_phone;
659 --
660 -- ----------------------------------------------------------------------------
661 -- |------------------------< create_or_update_phone >------------------------|
662 -- ----------------------------------------------------------------------------
663 procedure create_or_update_phone
664 (p_update_mode in varchar2 default hr_api.g_correction,
665 p_phone_id in out nocopy number,
666 p_object_version_number in out nocopy number,
667 p_date_from in date default hr_api.g_date,
668 p_date_to in date default hr_api.g_date,
669 p_phone_type in varchar2 default hr_api.g_varchar2,
670 p_phone_number in varchar2 default hr_api.g_varchar2,
671 p_parent_id in number default hr_api.g_number,
672 p_parent_table in varchar2 default hr_api.g_varchar2,
673 p_attribute_category in varchar2 default hr_api.g_varchar2,
674 p_attribute1 in varchar2 default hr_api.g_varchar2,
675 p_attribute2 in varchar2 default hr_api.g_varchar2,
676 p_attribute3 in varchar2 default hr_api.g_varchar2,
677 p_attribute4 in varchar2 default hr_api.g_varchar2,
678 p_attribute5 in varchar2 default hr_api.g_varchar2,
679 p_attribute6 in varchar2 default hr_api.g_varchar2,
680 p_attribute7 in varchar2 default hr_api.g_varchar2,
681 p_attribute8 in varchar2 default hr_api.g_varchar2,
682 p_attribute9 in varchar2 default hr_api.g_varchar2,
683 p_attribute10 in varchar2 default hr_api.g_varchar2,
684 p_attribute11 in varchar2 default hr_api.g_varchar2,
685 p_attribute12 in varchar2 default hr_api.g_varchar2,
686 p_attribute13 in varchar2 default hr_api.g_varchar2,
687 p_attribute14 in varchar2 default hr_api.g_varchar2,
688 p_attribute15 in varchar2 default hr_api.g_varchar2,
689 p_attribute16 in varchar2 default hr_api.g_varchar2,
690 p_attribute17 in varchar2 default hr_api.g_varchar2,
691 p_attribute18 in varchar2 default hr_api.g_varchar2,
692 p_attribute19 in varchar2 default hr_api.g_varchar2,
693 p_attribute20 in varchar2 default hr_api.g_varchar2,
694 p_attribute21 in varchar2 default hr_api.g_varchar2,
695 p_attribute22 in varchar2 default hr_api.g_varchar2,
696 p_attribute23 in varchar2 default hr_api.g_varchar2,
697 p_attribute24 in varchar2 default hr_api.g_varchar2,
698 p_attribute25 in varchar2 default hr_api.g_varchar2,
699 p_attribute26 in varchar2 default hr_api.g_varchar2,
700 p_attribute27 in varchar2 default hr_api.g_varchar2,
701 p_attribute28 in varchar2 default hr_api.g_varchar2,
702 p_attribute29 in varchar2 default hr_api.g_varchar2,
703 p_attribute30 in varchar2 default hr_api.g_varchar2,
704 p_validate in boolean default false,
705 p_effective_date in date,
706 p_party_id in number default hr_api.g_number,
707 p_validity in varchar2 default hr_api.g_varchar2
708 ) is
709 --
710 l_proc varchar2(72) := g_package||'create_or_update_phone';
711 l_api_updating boolean;
712 l_phn_rec per_phn_shd.g_rec_type;
713 l_null_phn_rec per_phn_shd.g_rec_type;
714 l_update_mode varchar2(30);
715 l_effective_date date;
716 --
717 begin
718 --
719 hr_utility.set_location('Entering:'|| l_proc, 10);
720 --
721 savepoint create_or_update_phone;
722 --
723 l_update_mode:=p_update_mode;
724 l_effective_date:=trunc(p_effective_date);
725 l_api_updating := per_phn_shd.api_updating
726 (p_phone_id => p_phone_id
727 ,p_object_version_number => p_object_version_number);
728 hr_utility.set_location(l_proc, 20);
729 --
730 -- set the record
731 --
732 l_phn_rec:=
733 per_phn_shd.convert_args
734 (p_phone_id,
735 trunc(p_date_from),
736 trunc(p_date_to),
737 p_phone_type,
738 p_phone_number,
739 p_parent_id,
740 p_parent_table,
741 p_attribute_category,
742 p_attribute1,
743 p_attribute2,
744 p_attribute3,
745 p_attribute4,
746 p_attribute5,
747 p_attribute6,
748 p_attribute7,
749 p_attribute8,
750 p_attribute9,
751 p_attribute10,
752 p_attribute11,
753 p_attribute12,
754 p_attribute13,
755 p_attribute14,
756 p_attribute15,
757 p_attribute16,
758 p_attribute17,
759 p_attribute18,
760 p_attribute19,
761 p_attribute20,
762 p_attribute21,
763 p_attribute22,
764 p_attribute23,
765 p_attribute24,
766 p_attribute25,
767 p_attribute26,
768 p_attribute27,
769 p_attribute28,
770 p_attribute29,
771 p_attribute30,
772 p_party_id, -- HR/TCA merge
773 p_validity,
774 p_object_version_number
775 );
776 if not l_api_updating then
777 --
778 -- set g_old_rec to null
779 --
780 per_phn_shd.g_old_rec:=l_null_phn_rec;
781 hr_utility.set_location(l_proc, 30);
782 --
783 -- convert the null values
784 --
785 hr_utility.set_location(l_proc, 40);
786 per_phn_upd.convert_defs(l_phn_rec);
787 --
788 -- insert the data
789 --
790 hr_utility.set_location(l_proc||l_phn_rec.parent_id, 50);
791 hr_phone_api.create_phone
792 (p_date_from => l_phn_rec.date_from
793 ,p_date_to => l_phn_rec.date_to
794 ,p_phone_type => l_phn_rec.phone_type
795 ,p_phone_number => l_phn_rec.phone_number
796 ,p_parent_id => l_phn_rec.parent_id
797 ,p_parent_table => l_phn_rec.parent_table
798 ,p_attribute_category => l_phn_rec.attribute_category
799 ,p_attribute1 => l_phn_rec.attribute1
800 ,p_attribute2 => l_phn_rec.attribute2
801 ,p_attribute3 => l_phn_rec.attribute3
802 ,p_attribute4 => l_phn_rec.attribute4
803 ,p_attribute5 => l_phn_rec.attribute5
804 ,p_attribute6 => l_phn_rec.attribute6
805 ,p_attribute7 => l_phn_rec.attribute7
806 ,p_attribute8 => l_phn_rec.attribute8
807 ,p_attribute9 => l_phn_rec.attribute9
808 ,p_attribute10 => l_phn_rec.attribute10
809 ,p_attribute11 => l_phn_rec.attribute11
810 ,p_attribute12 => l_phn_rec.attribute12
811 ,p_attribute13 => l_phn_rec.attribute13
812 ,p_attribute14 => l_phn_rec.attribute14
813 ,p_attribute15 => l_phn_rec.attribute15
814 ,p_attribute16 => l_phn_rec.attribute16
815 ,p_attribute17 => l_phn_rec.attribute17
816 ,p_attribute18 => l_phn_rec.attribute18
817 ,p_attribute19 => l_phn_rec.attribute19
818 ,p_attribute20 => l_phn_rec.attribute20
819 ,p_attribute21 => l_phn_rec.attribute21
820 ,p_attribute22 => l_phn_rec.attribute22
821 ,p_attribute23 => l_phn_rec.attribute23
822 ,p_attribute24 => l_phn_rec.attribute24
823 ,p_attribute25 => l_phn_rec.attribute25
824 ,p_attribute26 => l_phn_rec.attribute26
825 ,p_attribute27 => l_phn_rec.attribute27
826 ,p_attribute28 => l_phn_rec.attribute28
827 ,p_attribute29 => l_phn_rec.attribute29
828 ,p_attribute30 => l_phn_rec.attribute30
829 ,p_validate => FALSE
830 ,p_effective_date => l_effective_date
831 ,p_object_version_number => l_phn_rec.object_version_number
832 ,p_phone_id => l_phn_rec.phone_id
833 ,p_party_id => l_phn_rec.party_id -- HR/TCA merge
834 ,p_validity => l_phn_rec.validity
835 );
836 hr_utility.set_location(l_proc, 60);
837 else
838 hr_utility.set_location(l_proc, 70);
839 --
840 -- updating not inserting
841 --
842 -- Validating update_mode values
843 if (l_update_mode not in (hr_api.g_update,hr_api.g_correction)) then
844 hr_utility.set_location(l_proc, 80);
845 hr_utility.set_message(800, 'HR_52858_PHN_CHK_MODE');
846 hr_utility.raise_error;
847 end if;
848 hr_utility.set_location(l_proc, 90);
849 --
850 -- Validating effective_date
851 --
852 if ((p_date_to <> hr_api.g_date) AND ( l_effective_date > p_date_to ))
853 or ((p_date_from <> hr_api.g_date) AND ( l_effective_date < p_date_from ))
854 then
855 hr_utility.set_location(l_proc, 100);
856 hr_utility.set_message(800, 'HR_52859_PHN_INVALID_EFF_DATE');
857 hr_utility.raise_error;
858 end if;
859 hr_utility.set_location(l_proc, 110);
860 --
861 per_phn_shd.lck
862 (p_phone_id => p_phone_id
863 ,p_object_version_number => p_object_version_number);
864 --
865 -- convert the null values
866 --
867 hr_utility.set_location(l_proc, 130);
868 per_phn_upd.convert_defs(l_phn_rec);
869 --
870 -- check to see if the start date hasn't changed and is equal to the
871 -- effective date. This will always be a correction.
872 --
873 if per_phn_shd.g_old_rec.date_from = l_phn_rec.date_from
874 and l_phn_rec.date_from = l_effective_date then
875 l_update_mode:= hr_api.g_correction;
876 end if;
877 --
878 -- check for the modes mode
879 --
880 if l_update_mode = hr_api.g_correction then
881 --
882 -- correct the data
883 --
884 hr_utility.set_location(l_proc, 140);
885 --
886 hr_phone_api.update_phone
887 (p_date_from => l_phn_rec.date_from
888 ,p_date_to => l_phn_rec.date_to
889 ,p_phone_type => l_phn_rec.phone_type
890 ,p_phone_number => l_phn_rec.phone_number
891 ,p_attribute_category => l_phn_rec.attribute_category
892 ,p_attribute1 => l_phn_rec.attribute1
893 ,p_attribute2 => l_phn_rec.attribute2
894 ,p_attribute3 => l_phn_rec.attribute3
895 ,p_attribute4 => l_phn_rec.attribute4
896 ,p_attribute5 => l_phn_rec.attribute5
897 ,p_attribute6 => l_phn_rec.attribute6
898 ,p_attribute7 => l_phn_rec.attribute7
899 ,p_attribute8 => l_phn_rec.attribute8
900 ,p_attribute9 => l_phn_rec.attribute9
901 ,p_attribute10 => l_phn_rec.attribute10
902 ,p_attribute11 => l_phn_rec.attribute11
903 ,p_attribute12 => l_phn_rec.attribute12
904 ,p_attribute13 => l_phn_rec.attribute13
905 ,p_attribute14 => l_phn_rec.attribute14
906 ,p_attribute15 => l_phn_rec.attribute15
907 ,p_attribute16 => l_phn_rec.attribute16
908 ,p_attribute17 => l_phn_rec.attribute17
909 ,p_attribute18 => l_phn_rec.attribute18
910 ,p_attribute19 => l_phn_rec.attribute19
911 ,p_attribute20 => l_phn_rec.attribute20
912 ,p_attribute21 => l_phn_rec.attribute21
913 ,p_attribute22 => l_phn_rec.attribute22
914 ,p_attribute23 => l_phn_rec.attribute23
915 ,p_attribute24 => l_phn_rec.attribute24
916 ,p_attribute25 => l_phn_rec.attribute25
917 ,p_attribute26 => l_phn_rec.attribute26
918 ,p_attribute27 => l_phn_rec.attribute27
919 ,p_attribute28 => l_phn_rec.attribute28
920 ,p_attribute29 => l_phn_rec.attribute29
921 ,p_attribute30 => l_phn_rec.attribute30
922 ,p_validate => FALSE
923 ,p_effective_date => l_effective_date
924 ,p_object_version_number => l_phn_rec.object_version_number
925 ,p_phone_id => l_phn_rec.phone_id
926 ,p_validity => l_phn_rec.validity
927 );
928 --
929 hr_utility.set_location(l_proc, 150);
930 --
931 else
932 --
933 -- update mode
934 --
935 hr_utility.set_location(l_proc, 160);
936 --
937 -- if the start date has changed and it is not the effective date then
938 -- we have an error. A change of start date is the new start date for
939 -- the new record, so must be the effective date so that the phone numbers
940 -- are continuous.
941 --
942 if per_phn_shd.g_old_rec.date_from <> l_phn_rec.date_from
943 and l_phn_rec.date_from <> l_effective_date then
944 hr_utility.set_location(l_proc, 170);
945 hr_utility.set_message(800, 'HR_52859_PHN_INVALID_EFF_DATE');
946 hr_utility.raise_error;
947 end if;
948 --
949 hr_utility.set_location(l_proc, 180);
950 --
951 -- end the old phone number
952 --
953 hr_phone_api.update_phone
954 (p_phone_id => l_phn_rec.phone_id
955 ,p_date_to => l_effective_date-1
956 ,p_object_version_number => l_phn_rec.object_version_number
957 ,p_validate => FALSE
958 ,p_effective_date => l_effective_date);
959 --
960 hr_utility.set_location(l_proc, 190);
961 --
962 -- insert the new phone number
963 --
964 hr_phone_api.create_phone
965 (p_date_from => l_effective_date
966 ,p_date_to => l_phn_rec.date_to
967 ,p_phone_type => l_phn_rec.phone_type
968 ,p_phone_number => l_phn_rec.phone_number
969 ,p_parent_id => l_phn_rec.parent_id
970 ,p_parent_table => l_phn_rec.parent_table
971 ,p_attribute_category => l_phn_rec.attribute_category
972 ,p_attribute1 => l_phn_rec.attribute1
973 ,p_attribute2 => l_phn_rec.attribute2
974 ,p_attribute3 => l_phn_rec.attribute3
975 ,p_attribute4 => l_phn_rec.attribute4
976 ,p_attribute5 => l_phn_rec.attribute5
977 ,p_attribute6 => l_phn_rec.attribute6
978 ,p_attribute7 => l_phn_rec.attribute7
979 ,p_attribute8 => l_phn_rec.attribute8
980 ,p_attribute9 => l_phn_rec.attribute9
981 ,p_attribute10 => l_phn_rec.attribute10
982 ,p_attribute11 => l_phn_rec.attribute11
983 ,p_attribute12 => l_phn_rec.attribute12
984 ,p_attribute13 => l_phn_rec.attribute13
985 ,p_attribute14 => l_phn_rec.attribute14
986 ,p_attribute15 => l_phn_rec.attribute15
987 ,p_attribute16 => l_phn_rec.attribute16
988 ,p_attribute17 => l_phn_rec.attribute17
989 ,p_attribute18 => l_phn_rec.attribute18
990 ,p_attribute19 => l_phn_rec.attribute19
991 ,p_attribute20 => l_phn_rec.attribute20
992 ,p_attribute21 => l_phn_rec.attribute21
993 ,p_attribute22 => l_phn_rec.attribute22
994 ,p_attribute23 => l_phn_rec.attribute23
995 ,p_attribute24 => l_phn_rec.attribute24
996 ,p_attribute25 => l_phn_rec.attribute25
997 ,p_attribute26 => l_phn_rec.attribute26
998 ,p_attribute27 => l_phn_rec.attribute27
999 ,p_attribute28 => l_phn_rec.attribute28
1000 ,p_attribute29 => l_phn_rec.attribute29
1001 ,p_attribute30 => l_phn_rec.attribute30
1002 ,p_validate => FALSE
1003 ,p_effective_date => l_effective_date
1004 ,p_object_version_number => l_phn_rec.object_version_number
1005 ,p_phone_id => l_phn_rec.phone_id
1006 ,p_party_id => l_phn_rec.party_id -- HR/TCA merge
1007 ,p_validity => l_phn_rec.validity
1008 );
1009 --
1010 hr_utility.set_location(l_proc, 190);
1011 --
1012 end if;
1013 end if;
1014 --
1015 if p_validate then
1016 raise hr_api.validate_enabled;
1017 end if;
1018 --
1019 p_phone_id:=l_phn_rec.phone_id;
1020 p_object_version_number:=l_phn_rec.object_version_number;
1021 hr_utility.set_location('Leaving:'||l_proc, 200);
1022 --
1023 exception
1024 when hr_api.validate_enabled then
1025 rollback to create_or_update_phone;
1026 p_phone_id:=null;
1027 p_object_version_number:=null;
1028 hr_utility.set_location('Leaving:'||l_proc, 220);
1029 --
1030 when others then
1031 rollback to create_or_update_phone;
1032 hr_utility.set_location('Leaving:'||l_proc, 230);
1033 raise;
1034 --
1035 end create_or_update_phone;
1036 --
1037 end hr_phone_api;