[Home] [Help]
PACKAGE BODY: APPS.HR_PROCESS_PHONE_NUMBERS_SS
Source
1 PACKAGE BODY hr_process_phone_numbers_ss AS
2 /* $Header: hrphnwrs.pkb 120.0 2005/05/31 02:10:29 appldev noship $*/
3
4 g_package varchar2(30) := 'HR_PROCESS_PHONE_NUMBERS_SS';
5 l_result varchar2(100) default null;
6 g_save_transaction_error exception;
7
8 /*
9 ||===========================================================================
10 || PROCEDURE: save_transaction
11 ||---------------------------------------------------------------------------
12 ||
13 || Description: Common procedure to save transaction
14 */
15
16 procedure save_transaction(
17 p_phone_id in number
18 , p_item_type in varchar2
19 , p_item_key in varchar2
20 , p_activity_id in number
21 , p_date_from in date default trunc(sysdate)
22 , p_phone_type in varchar2
23 , p_phone_number in varchar2
24 , p_parent_id in number
25 , p_parent_table in VARCHAR2 default 'PER_ALL_PEOPLE_F'
26 , p_login_person_id in NUMBER
27 , p_per_or_contact in varchar2 default null
28 , p_contact_set in varchar2 default '1'
29 , p_effective_date in date default trunc(sysdate)
30 , p_object_version_number in number
31 , p_phone_type_meaning in varchar2
32 , p_attribute_category in varchar2 default null
33 , p_attribute1 in varchar2 default null
34 , p_attribute2 in varchar2 default null
35 , p_attribute3 in varchar2 default null
36 , p_attribute4 in varchar2 default null
37 , p_attribute5 in varchar2 default null
38 , p_attribute6 in varchar2 default null
39 , p_attribute7 in varchar2 default null
40 , p_attribute8 in varchar2 default null
41 , p_attribute9 in varchar2 default null
42 , p_attribute10 in varchar2 default null
43 , p_attribute11 in varchar2 default null
44 , p_attribute12 in varchar2 default null
45 , p_attribute13 in varchar2 default null
46 , p_attribute14 in varchar2 default null
47 , p_attribute15 in varchar2 default null
48 , p_attribute16 in varchar2 default null
49 , p_attribute17 in varchar2 default null
50 , p_attribute18 in varchar2 default null
51 , p_attribute19 in varchar2 default null
52 , p_attribute20 in varchar2 default null
53 , p_attribute21 in varchar2 default null
54 , p_attribute22 in varchar2 default null
55 , p_attribute23 in varchar2 default null
56 , p_attribute24 in varchar2 default null
57 , p_attribute25 in varchar2 default null
58 , p_attribute26 in varchar2 default null
59 , p_attribute27 in varchar2 default null
60 , p_attribute28 in varchar2 default null
61 , p_attribute29 in varchar2 default null
62 , p_attribute30 in varchar2 default null
63 , p_error_message out nocopy varchar2
64 , p_contact_relationship_id in number default hr_api.g_number
65 )
66 as
67 l_transaction_id number default null;
68 l_transaction_step_id number default null;
69 l_trans_obj_vers_num number default null;
70 l_count integer default 0;
71 l_transaction_table hr_transaction_ss.transaction_table;
72 l_review_item_name varchar2(50);
73 l_proc varchar2(72) := g_package||'save_transaction';
74
75 begin
76 --
77 -- First, check if transaction id exists or not
78 hr_utility.set_location('Entering:'||l_proc, 5);
79 l_transaction_id := hr_transaction_ss.get_transaction_id
80 (p_item_type => p_item_type
81 ,p_item_key => p_item_key);
82 --
83 IF l_transaction_id is null THEN
84 -- Start a Transaction
85 hr_utility.set_location('l_transaction_id is null THEN:'||l_proc,10);
86 hr_transaction_ss.start_transaction
87 (itemtype => p_item_type
88 ,itemkey => p_item_key
89 ,actid => p_activity_id
90 ,funmode => 'RUN'
91 ,p_api_addtnl_info => p_per_or_contact
92 ,p_login_person_id => nvl(p_login_person_id, p_parent_id)
93 ,result => l_result);
94
95 l_transaction_id := hr_transaction_ss.get_transaction_id
96 (p_item_type => p_item_type
97 ,p_item_key => p_item_key);
98 END IF;
99 --
100 -- Create a transaction step
101 --
102 hr_transaction_api.create_transaction_step
103 (p_validate => false
104 ,p_creator_person_id => nvl(p_login_person_id, p_parent_id)
105 ,p_transaction_id => l_transaction_id
106 ,p_api_name => g_package || '.PROCESS_API'
107 ,p_item_type => p_item_type
108 ,p_item_key => p_item_key
109 ,p_activity_id => p_activity_id
110 ,p_transaction_step_id => l_transaction_step_id
111 ,p_object_version_number => l_trans_obj_vers_num);
112 --
113 l_count := 1;
114 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
115 l_transaction_table(l_count).param_value := p_parent_id;
116 l_transaction_table(l_count).param_data_type := 'NUMBER';
117
118 l_count := l_count + 1;
119 l_transaction_table(l_count).param_name := 'P_PHONE_ID';
120 l_transaction_table(l_count).param_value := p_phone_id;
121 l_transaction_table(l_count).param_data_type := 'NUMBER';
122
123 l_count := l_count + 1;
124 l_transaction_table(l_count).param_name := 'P_PHONE_TYPE';
125 l_transaction_table(l_count).param_value := p_phone_type;
126 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
127
128 l_count := l_count + 1;
129 l_transaction_table(l_count).param_name := 'P_PHONE_TYPE_MEANING';
130 l_transaction_table(l_count).param_value := p_phone_type_meaning;
131 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
132
133 l_count := l_count + 1;
134 l_transaction_table(l_count).param_name := 'P_PHONE_NUMBER';
135 l_transaction_table(l_count).param_value := p_phone_number;
136 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
137
138 l_count := l_count + 1;
139 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
140 l_transaction_table(l_count).param_value := p_object_version_number;
141 l_transaction_table(l_count).param_data_type := 'NUMBER';
142
143 l_count := l_count + 1;
144 l_transaction_table(l_count).param_name := 'P_DATE_FROM';
145 l_transaction_table(l_count).param_value := to_char(p_date_from,
146 hr_transaction_ss.g_date_format);
147 l_transaction_table(l_count).param_data_type := 'DATE';
148
149 l_count := l_count + 1;
150 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
151 l_transaction_table(l_count).param_value := to_char(p_effective_date,
152 hr_transaction_ss.g_date_format);
153 l_transaction_table(l_count).param_data_type := 'DATE';
154
155 l_count := l_count + 1;
156 l_transaction_table(l_count).param_name := 'P_PARENT_TABLE';
157 l_transaction_table(l_count).param_value := p_parent_table;
158 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
159
160 l_count := l_count + 1;
161 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
162 l_transaction_table(l_count).param_value := p_activity_id;
163 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
164
165 BEGIN
166 l_review_item_name := wf_engine.GetActivityAttrText(itemtype => p_item_type,
167 itemkey => p_item_key,
168 actid => p_activity_id,
169 aname => gv_wf_review_region_item);
170 hr_utility.set_location('Exiting:'||l_proc, 15);
171 EXCEPTION
172 WHEN OTHERS THEN
173 hr_utility.set_location('Exception:Others'||l_proc,555);
174 l_review_item_name := 'HrPhoneNumbersReview';
175 END;
176
177 l_count := l_count + 1;
178 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
179 l_transaction_table(l_count).param_value := l_review_item_name;
180 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
181
182 -- Dff fields start
183 l_count := l_count + 1;
184 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE_CATEGORY';
185 l_transaction_table(l_count).param_value := p_attribute_category;
186 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
187
188 l_count := l_count + 1;
189 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE1';
190 l_transaction_table(l_count).param_value := p_attribute1;
191 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
192
193 l_count := l_count + 1;
194 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE2';
195 l_transaction_table(l_count).param_value := p_attribute2;
196 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
197
198 l_count := l_count + 1;
199 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE3';
200 l_transaction_table(l_count).param_value := p_attribute3;
201 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
202
203 l_count := l_count + 1;
204 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE4';
205 l_transaction_table(l_count).param_value := p_attribute4;
206 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
207
208 l_count := l_count + 1;
209 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE5';
210 l_transaction_table(l_count).param_value := p_attribute5;
211 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
212
213 l_count := l_count + 1;
214 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE6';
215 l_transaction_table(l_count).param_value := p_attribute6;
216 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
217
218 l_count := l_count + 1;
219 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE7';
220 l_transaction_table(l_count).param_value := p_attribute7;
221 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
222
223 l_count := l_count + 1;
224 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE8';
225 l_transaction_table(l_count).param_value := p_attribute8;
226 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
227
228 l_count := l_count + 1;
229 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE9';
230 l_transaction_table(l_count).param_value := p_attribute9;
231 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
232
233 l_count := l_count + 1;
234 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE10';
235 l_transaction_table(l_count).param_value := p_attribute10;
236 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
237
238 l_count := l_count + 1;
239 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE11';
240 l_transaction_table(l_count).param_value := p_attribute11;
241 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
242
243 l_count := l_count + 1;
244 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE12';
245 l_transaction_table(l_count).param_value := p_attribute12;
246 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
247
248 l_count := l_count + 1;
249 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE13';
250 l_transaction_table(l_count).param_value := p_attribute13;
251 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
252
253 l_count := l_count + 1;
254 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE14';
255 l_transaction_table(l_count).param_value := p_attribute14;
256 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
257
258 l_count := l_count + 1;
259 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE15';
260 l_transaction_table(l_count).param_value := p_attribute15;
261 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
262
263 l_count := l_count + 1;
264 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE16';
265 l_transaction_table(l_count).param_value := p_attribute16;
266 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
267
268 l_count := l_count + 1;
269 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE17';
270 l_transaction_table(l_count).param_value := p_attribute17;
271 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
272
273 l_count := l_count + 1;
274 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE18';
275 l_transaction_table(l_count).param_value := p_attribute18;
276 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
277
278 l_count := l_count + 1;
279 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE19';
280 l_transaction_table(l_count).param_value := p_attribute19;
281 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
282
283 l_count := l_count + 1;
284 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE20';
285 l_transaction_table(l_count).param_value := p_attribute20;
286 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
287
288 l_count := l_count + 1;
289 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE21';
290 l_transaction_table(l_count).param_value := p_attribute21;
291 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
292
293 l_count := l_count + 1;
294 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE22';
295 l_transaction_table(l_count).param_value := p_attribute22;
296 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
297
298 l_count := l_count + 1;
299 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE23';
300 l_transaction_table(l_count).param_value := p_attribute23;
301 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
302
303 l_count := l_count + 1;
304 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE24';
305 l_transaction_table(l_count).param_value := p_attribute24;
306 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
307
308 l_count := l_count + 1;
309 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE25';
310 l_transaction_table(l_count).param_value := p_attribute25;
311 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
312
313 l_count := l_count + 1;
314 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE26';
315 l_transaction_table(l_count).param_value := p_attribute26;
316 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
317
318 l_count := l_count + 1;
319 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE27';
320 l_transaction_table(l_count).param_value := p_attribute27;
321 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
322
323 l_count := l_count + 1;
324 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE28';
325 l_transaction_table(l_count).param_value := p_attribute28;
326 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
327
328 l_count := l_count + 1;
329 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE29';
330 l_transaction_table(l_count).param_value := p_attribute29;
331 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
332
333 l_count := l_count + 1;
334 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE30';
335 l_transaction_table(l_count).param_value := p_attribute30;
336 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
337
338 -- Dff fields end
339
340 -- Contact specific start
341 l_count := l_count + 1;
342 l_transaction_table(l_count).param_name := 'P_PER_OR_CONTACT';
343 l_transaction_table(l_count).param_value := p_per_or_contact;
344 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
345
346 l_count := l_count + 1;
347 l_transaction_table(l_count).param_name := 'P_CONTACT_SET';
348 l_transaction_table(l_count).param_value := p_contact_set;
349 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
350
351 if (p_contact_relationship_id > 0 ) then
352 hr_utility.set_location('p_contact_relationship_id > 0:'||l_proc,25);
353 l_count := l_count + 1;
354 l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
355 l_transaction_table(l_count).param_value := p_contact_relationship_id;
356 l_transaction_table(l_count).param_data_type := 'NUMBER';
357 end if;
358 -- Contact specific end
359
360
361 hr_transaction_ss.save_transaction_step
362 (p_item_type => p_item_type
363 ,p_item_key => p_item_key
364 ,p_actid => p_activity_id
365 ,p_login_person_id => nvl(p_login_person_id, p_parent_id)
366 ,p_transaction_step_id => l_transaction_step_id
367 ,p_api_name => g_package || '.PROCESS_API'
368 ,p_transaction_data => l_transaction_table);
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 hr_utility.set_location('Exception:Others'||l_proc,555);
373 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
374 p_error_message => p_error_message);
375
376 end save_transaction;
377
378 /*
379 ||===========================================================================
380 || PROCEDURE: create_phone
381 ||---------------------------------------------------------------------------
382 ||
383 || Description:
384 || Description:
385 || This procedure will call the actual API -
386 || hr_phone_api.create_phone()
387 ||
388 || Pre Conditions:
389 ||
390 || In Arguments:
391 || Contains entire list of parameters that are defined in the actual
392 || API. For details see pephnapi.pkb file.
393 ||
394 || out nocopy Arguments:
395 ||
396 || In out nocopy Arguments:
397 ||
398 || Post Success:
399 || Executes the API call.
400 ||
401 || Post Failure:
402 || Raises an exception
403 ||
404 || Access Status:
405 || Public.
406 ||
407 ||===========================================================================
408 */
409
410 procedure create_phone(p_date_from date
411 , p_date_to date default null
412 , p_phone_type VARCHAR2
413 , p_phone_number VARCHAR2
414 , p_parent_id NUMBER
415 , p_parent_table VARCHAR2
416 --
417 -- PB Add :
418 -- The transaction steps have to be created by the login personid.
419 -- In case of adding phones for contacts parent_is is contact_person_id.
420 -- Login person id is say employee who is adding the phones to his contact.
421 --
422 , p_login_person_id NUMBER default null
423 , p_business_group_id number default null
424 , p_attribute_category VARCHAR2 default hr_api.g_varchar2
425 , p_attribute1 VARCHAR2 default hr_api.g_varchar2
426 , p_attribute2 VARCHAR2 default hr_api.g_varchar2
427 , p_attribute3 VARCHAR2 default hr_api.g_varchar2
428 , p_attribute4 VARCHAR2 default hr_api.g_varchar2
429 , p_attribute5 VARCHAR2 default hr_api.g_varchar2
430 , p_attribute6 VARCHAR2 default hr_api.g_varchar2
431 , p_attribute7 VARCHAR2 default hr_api.g_varchar2
432 , p_attribute8 VARCHAR2 default hr_api.g_varchar2
433 , p_attribute9 VARCHAR2 default hr_api.g_varchar2
434 , p_attribute10 VARCHAR2 default hr_api.g_varchar2
435 , p_attribute11 VARCHAR2 default hr_api.g_varchar2
436 , p_attribute12 VARCHAR2 default hr_api.g_varchar2
437 , p_attribute13 VARCHAR2 default hr_api.g_varchar2
438 , p_attribute14 VARCHAR2 default hr_api.g_varchar2
439 , p_attribute15 VARCHAR2 default hr_api.g_varchar2
440 , p_attribute16 VARCHAR2 default hr_api.g_varchar2
441 , p_attribute17 VARCHAR2 default hr_api.g_varchar2
442 , p_attribute18 VARCHAR2 default hr_api.g_varchar2
443 , p_attribute19 VARCHAR2 default hr_api.g_varchar2
444 , p_attribute20 VARCHAR2 default hr_api.g_varchar2
445 , p_attribute21 VARCHAR2 default hr_api.g_varchar2
446 , p_attribute22 VARCHAR2 default hr_api.g_varchar2
447 , p_attribute23 VARCHAR2 default hr_api.g_varchar2
448 , p_attribute24 VARCHAR2 default hr_api.g_varchar2
449 , p_attribute25 VARCHAR2 default hr_api.g_varchar2
450 , p_attribute26 VARCHAR2 default hr_api.g_varchar2
451 , p_attribute27 VARCHAR2 default hr_api.g_varchar2
452 , p_attribute28 VARCHAR2 default hr_api.g_varchar2
453 , p_attribute29 VARCHAR2 default hr_api.g_varchar2
454 , p_attribute30 VARCHAR2 default hr_api.g_varchar2
455 -- StartRegistration
456 , p_per_or_contact varchar2 default null
457 , p_validate number
458 , p_effective_date date
459 , p_object_version_number out nocopy NUMBER
460 , p_phone_id out nocopy NUMBER
461 , p_item_type in varchar2
462 , p_item_key in varchar2
463 , p_activity_id in number
464 , p_phone_type_meaning in varchar2
465 , p_save_mode in varchar2 default null
466 , p_error_message out nocopy varchar2
467 , p_contact_relationship_id in number default hr_api.g_number
468 )
469 as
470
471 l_transaction_id number default null;
472 l_transaction_step_id number default null;
473 l_trans_obj_vers_num number default null;
474 l_count integer default 0;
475 l_transaction_table hr_transaction_ss.transaction_table;
476 l_review_item_name varchar2(50);
477 l_message_number VARCHAR2(10);
478 --
479 l_parent_id number;
480 l_dummy_num number;
481 l_dummy_date date;
482 l_dummy_char varchar2(1000);
483 l_dummy_bool boolean;
484 --
485 -- StartRegistration
486 l_reg_per_ovn number default null;
487 l_reg_employee_number number default null;
488 l_reg_asg_ovn number default null;
489 l_reg_full_name per_all_people_f.full_name%type default null;
490 l_reg_assignment_id number;
491 l_reg_per_effective_start_date date;
492 l_reg_per_effective_end_date date;
493 l_reg_per_comment_id number;
494 l_reg_assignment_sequence number;
495 l_reg_assignment_number varchar2(50);
496 l_reg_name_combination_warning boolean;
497 l_reg_assign_payroll_warning boolean;
498 l_reg_orig_hire_warning boolean;
499 l_contact_set number;
500 -- EndRegistration
501 l_api_name varchar2(100);
502 l_validate_g_per_con_step_id number;
503 --
504 l_error_message long default null;
505 l_proc varchar2(72) := g_package||'create_phone';
506
507 begin
508
509 hr_utility.set_location('Entering:'||l_proc, 5);
510 IF (p_save_mode = 'SAVE_FOR_LATER') THEN
511 hr_utility.set_location('SFL:GOTO only_txn'||l_proc,10);
512 GOTO only_transaction;
513 END IF;
514
515 SAVEPOINT create_contact_and_phone;
516 l_parent_id := p_parent_id;
517 --
518 if (l_parent_id is null or l_parent_id < 0 ) then
519 --
520 -- bug # 2174876
521 hr_utility.set_location('l_parent_id is null or l_parent_id < 0 :'||l_proc,15);
522
523 if p_per_or_contact = 'CONTACT' or p_per_or_contact = 'EMER_CR_NEW_CONT' or p_per_or_contact = 'EMRG_OVRW_UPD' or p_per_or_contact = 'EMRG_OVRW_DEL' or p_per_or_contact = 'EMER_CR_NEW_REL'
524 or p_per_or_contact = 'DPDNT_CR_NEW_CONT' or p_per_or_contact = 'DPDNT_OVRW_UPD' or p_per_or_contact = 'DPDNT_OVRW_DEL' or p_per_or_contact = 'DPDNT_CR_NEW_REL'
525 or p_per_or_contact = 'COBRA' then
526 declare
527 l_object_version_number number;
528 l_effective_start_date date;
529 l_effective_end_date date;
530 l_full_name varchar2(255);
531 l_comment_id number;
532 l_name_combination_warning boolean;
533 l_orig_hire_warning boolean;
534 begin
535 --ignore the dff validations for Dummy Person created
536 hr_person_info_util_ss.create_ignore_df_validation('PER_PEOPLE');
537 hr_person_info_util_ss.create_ignore_df_validation('Person Developer DF');
538 hr_contact_api.create_person
539 (p_start_date => p_effective_date,
540 p_business_group_id =>p_business_group_id,
541 p_last_name => 'RegistrationDummy',
542 p_first_name => 'Dummy',
543 p_sex => 'M',
544 -- p_coord_ben_no_cvg_flag => 'Y',
545 p_person_id => l_parent_id,
546 p_object_version_number => l_object_version_number,
547 p_effective_start_date => l_effective_start_date,
548 p_effective_end_date => l_effective_end_date,
549 p_full_name => l_full_name,
550 p_comment_id => l_comment_id,
551 p_name_combination_warning => l_name_combination_warning,
552 p_orig_hire_warning => l_orig_hire_warning);
553 hr_person_info_util_ss.remove_ignore_df_validation;
554 end;
555 end if;
556 -- end bug # 2174876
557 --end bug # 2138073/2115552
558 if p_per_or_contact = 'PERSON' then
559 hr_utility.set_location('p_per_or_contact = PERSON:'||l_proc,20);
560 hr_new_user_reg_ss.processNewUserTransaction(
561 WfItemType => p_item_type,
562 WfItemKey => p_item_key,
563 PersonId => l_parent_id,
564 AssignmentId => l_reg_assignment_id);
565 end if;
566 --
567 end if;
568 --
569 -- here's the delegated call to the old PL/SQL routine
570 --
571 hr_phone_api.create_phone(
572 p_date_from => p_date_from,
573 p_phone_type => p_phone_type,
574 p_phone_number => p_phone_number,
575 -- PB Add
576 -- In case of create of a new contact p_parent_id comes as -1 so
577 -- use p_login_person_id to validate the phone addition.
578 --
579 p_parent_id => l_parent_id, -- PB : Modify p_parent_id,
580 p_parent_table => p_parent_table,
581 p_attribute_category => p_attribute_category,
582 p_attribute1 => p_attribute1,
583 p_attribute2 => p_attribute2,
584 p_attribute3 => p_attribute3,
585 p_attribute4 => p_attribute4,
586 p_attribute5 => p_attribute5,
587 p_attribute6 => p_attribute6,
588 p_attribute7 => p_attribute7,
589 p_attribute8 => p_attribute8,
590 p_attribute9 => p_attribute9,
591 p_attribute10 => p_attribute10,
592 p_attribute11 => p_attribute11,
593 p_attribute12 => p_attribute12,
594 p_attribute13 => p_attribute13,
595 p_attribute14 => p_attribute14,
596 p_attribute15 => p_attribute15,
597 p_attribute16 => p_attribute16,
598 p_attribute17 => p_attribute17,
599 p_attribute18 => p_attribute18,
600 p_attribute19 => p_attribute19,
601 p_attribute20 => p_attribute20,
602 p_attribute21 => p_attribute21,
603 p_attribute22 => p_attribute22,
604 p_attribute23 => p_attribute23,
605 p_attribute24 => p_attribute24,
606 p_attribute25 => p_attribute25,
607 p_attribute26 => p_attribute26,
608 p_attribute27 => p_attribute27,
609 p_attribute28 => p_attribute28,
610 p_attribute29 => p_attribute29,
611 p_attribute30 => p_attribute30,
612 --
613 -- PB : Use the savepoint and rollback rather than
614 -- the parameter.
615 --
616 -- p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate),
617 p_validate => false,
618 p_effective_date => p_effective_date,
619 p_object_version_number => l_dummy_num, -- p_object_version_number,
620 p_phone_id => l_dummy_num); -- p_phone_id);
621 --
622
623 hr_utility.set_location('rollback to create_contact_and_phone:'||l_proc,25);
624 rollback to create_contact_and_phone;
625
626 <<only_transaction>> -- label for GOTO
627
628 -- This is a marker for the contact person to be used to identify the phone numbers
629 -- to be retrieved for the contact person in context in review page.
630 -- The HR_LAST_CONTACT_SET is got from the work flow attribute
631 begin
632
633 l_contact_set := wf_engine.GetItemAttrNumber(itemtype => p_item_type,
634 itemkey => p_item_key,
635 aname => 'HR_CONTACT_SET');
636
637 exception when others then
638 hr_utility.set_location('Exception:others'||l_proc,555);
639 l_contact_set := 0;
640
641 end;
642
643 save_transaction(
644 p_phone_id => p_phone_id
645 , p_item_type => p_item_type
646 , p_item_key => p_item_key
647 , p_activity_id => p_activity_id
648 , p_date_from => p_date_from
649 , p_phone_type => p_phone_type
650 , p_phone_number => p_phone_number
651 , p_parent_id => p_parent_id
652 , p_parent_table => p_parent_table
653 , p_login_person_id => p_login_person_id
654 , p_per_or_contact => p_per_or_contact
655 , p_contact_set => l_contact_set
656 , p_effective_date => p_effective_date
657 , p_object_version_number => p_object_version_number
658 , p_phone_type_meaning => p_phone_type_meaning
659 , p_attribute_category => p_attribute_category
660 , p_attribute1 => p_attribute1
661 , p_attribute2 => p_attribute2
662 , p_attribute3 => p_attribute3
663 , p_attribute4 => p_attribute4
664 , p_attribute5 => p_attribute5
665 , p_attribute6 => p_attribute6
666 , p_attribute7 => p_attribute7
667 , p_attribute8 => p_attribute8
668 , p_attribute9 => p_attribute9
669 , p_attribute10 => p_attribute10
670 , p_attribute11 => p_attribute11
671 , p_attribute12 => p_attribute12
672 , p_attribute13 => p_attribute13
673 , p_attribute14 => p_attribute14
674 , p_attribute15 => p_attribute15
675 , p_attribute16 => p_attribute16
676 , p_attribute17 => p_attribute17
677 , p_attribute18 => p_attribute18
678 , p_attribute19 => p_attribute19
679 , p_attribute20 => p_attribute20
680 , p_attribute21 => p_attribute21
681 , p_attribute22 => p_attribute22
682 , p_attribute23 => p_attribute23
683 , p_attribute24 => p_attribute24
684 , p_attribute25 => p_attribute25
685 , p_attribute26 => p_attribute26
686 , p_attribute27 => p_attribute27
687 , p_attribute28 => p_attribute28
688 , p_attribute29 => p_attribute29
689 , p_attribute30 => p_attribute30
690 , p_error_message => l_error_message
691 , p_contact_relationship_id =>p_contact_relationship_id);
692
693 -- check if we got any errors
694 IF (l_error_message IS NOT NULL) THEN
695
696 raise g_save_transaction_error;
697 END IF;
698
699 EXCEPTION
700 WHEN g_save_transaction_error THEN
701 -- No need to call formatted_error_message, as the messages is already
702 -- formatted.
703 hr_utility.set_location('Exception:g_save_transaction_error'||l_proc,560);
704 p_error_message := l_error_message;
705 WHEN hr_utility.hr_error THEN
706 -- -------------------------------------------
707 -- an application error has been raised so we must
708 -- redisplay the web form to display the error
709 -- --------------------------------------------
710 hr_utility.set_location('Exception:hr_utility.hr_error THEN'||l_proc,565);
711 hr_message.provide_error;
712 l_message_number := hr_message.last_message_number;
713 IF l_message_number = 'APP-7165' OR
714 l_message_number = 'APP-7155' THEN
715 --populate the p_error_message out variable
716 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
717 p_error_message => p_error_message,
718 p_attr_name => 'Page',
719 p_app_short_name => 'PER',
720 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
721 ELSE
722 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
723 p_error_message => p_error_message);
724 END IF;
725 WHEN OTHERS THEN
726 hr_utility.set_location('Exception:Others'||l_proc,570);
727 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
728 p_error_message => p_error_message);
729
730 end create_phone;
731
732 /*
733 ||===========================================================================
734 || PROCEDURE: update_phone
735 ||---------------------------------------------------------------------------
736 ||
737 || Description:
738 || Description:
739 || This procedure will call the actual API -
740 || hr_phone_api.update_phone()
741 ||
742 || Pre Conditions:
743 ||
744 || In Arguments:
745 || Contains entire list of parameters that are defined in the actual
746 || API. For details see pephnapi.pkb file.
747 ||
748 || out nocopy Arguments:
749 ||
750 || In out nocopy Arguments:
751 ||
752 || Post Success:
753 || Executes the API call.
754 ||
755 || Post Failure:
756 || Raises an exception
757 ||
758 || Access Status:
759 || Public.
760 ||
761 ||===========================================================================
762 */
763
764 procedure update_phone(p_phone_id NUMBER
765 , p_date_from date
766 , p_date_to date
767 , p_phone_type VARCHAR2
768 , p_phone_number VARCHAR2
769 , p_per_or_contact varchar2 default null
770 --
771 -- PB Add :
772 -- The transaction steps have to be created by the login personid.
773 -- In case of adding phones for contacts parent_is is contact_person_id.
774 -- Login person id is say employee who is adding the phones to his contact.
775 --
776 , p_login_person_id NUMBER default hr_api.g_number
777 , p_attribute_category VARCHAR2 default hr_api.g_varchar2
778 , p_attribute1 VARCHAR2 default hr_api.g_varchar2
779 , p_attribute2 VARCHAR2 default hr_api.g_varchar2
780 , p_attribute3 VARCHAR2 default hr_api.g_varchar2
781 , p_attribute4 VARCHAR2 default hr_api.g_varchar2
782 , p_attribute5 VARCHAR2 default hr_api.g_varchar2
783 , p_attribute6 VARCHAR2 default hr_api.g_varchar2
784 , p_attribute7 VARCHAR2 default hr_api.g_varchar2
785 , p_attribute8 VARCHAR2 default hr_api.g_varchar2
786 , p_attribute9 VARCHAR2 default hr_api.g_varchar2
787 , p_attribute10 VARCHAR2 default hr_api.g_varchar2
788 , p_attribute11 VARCHAR2 default hr_api.g_varchar2
789 , p_attribute12 VARCHAR2 default hr_api.g_varchar2
790 , p_attribute13 VARCHAR2 default hr_api.g_varchar2
791 , p_attribute14 VARCHAR2 default hr_api.g_varchar2
792 , p_attribute15 VARCHAR2 default hr_api.g_varchar2
793 , p_attribute16 VARCHAR2 default hr_api.g_varchar2
794 , p_attribute17 VARCHAR2 default hr_api.g_varchar2
795 , p_attribute18 VARCHAR2 default hr_api.g_varchar2
796 , p_attribute19 VARCHAR2 default hr_api.g_varchar2
797 , p_attribute20 VARCHAR2 default hr_api.g_varchar2
798 , p_attribute21 VARCHAR2 default hr_api.g_varchar2
799 , p_attribute22 VARCHAR2 default hr_api.g_varchar2
800 , p_attribute23 VARCHAR2 default hr_api.g_varchar2
801 , p_attribute24 VARCHAR2 default hr_api.g_varchar2
802 , p_attribute25 VARCHAR2 default hr_api.g_varchar2
803 , p_attribute26 VARCHAR2 default hr_api.g_varchar2
804 , p_attribute27 VARCHAR2 default hr_api.g_varchar2
805 , p_attribute28 VARCHAR2 default hr_api.g_varchar2
806 , p_attribute29 VARCHAR2 default hr_api.g_varchar2
807 , p_attribute30 VARCHAR2 default hr_api.g_varchar2
808 , p_object_version_number in out nocopy NUMBER
809 , p_validate number
810 , p_effective_date date
811 , p_parent_id NUMBER
812 , p_item_type in varchar2
813 , p_item_key in varchar2
814 , p_activity_id in number
815 , p_phone_type_meaning in varchar2
816 , p_save_mode in varchar2 default null
817 , p_error_message out nocopy varchar2
818 , p_contact_relationship_id in number default hr_api.g_number
819 )
820 as
821
822 l_transaction_id number default null;
823 l_transaction_step_id number default null;
824 l_trans_obj_vers_num number default null;
825 l_count integer default 0;
826 l_transaction_table hr_transaction_ss.transaction_table;
827 l_review_item_name varchar2(50);
828 l_message_number VARCHAR2(10);
829 l_error_message long default null;
830 l_proc varchar2(72) := g_package||'update_phone';
831
832 begin
833
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 IF (p_save_mode = 'SAVE_FOR_LATER') THEN
836 hr_utility.set_location('SFL:Goto only_txn'||l_proc,10);
837 GOTO only_transaction;
838 END IF;
839
840
841 -- here's the delegated call to the old PL/SQL routine
842 hr_phone_api.update_phone(
843 p_phone_id => p_phone_id,
844 p_date_from => p_date_from,
845 p_phone_type => p_phone_type,
846 p_phone_number => p_phone_number,
847 p_attribute_category => p_attribute_category,
848 p_attribute1 => p_attribute1,
849 p_attribute2 => p_attribute2,
850 p_attribute3 => p_attribute3,
851 p_attribute4 => p_attribute4,
852 p_attribute5 => p_attribute5,
853 p_attribute6 => p_attribute6,
854 p_attribute7 => p_attribute7,
855 p_attribute8 => p_attribute8,
856 p_attribute9 => p_attribute9,
857 p_attribute10 => p_attribute10,
858 p_attribute11 => p_attribute11,
859 p_attribute12 => p_attribute12,
860 p_attribute13 => p_attribute13,
861 p_attribute14 => p_attribute14,
862 p_attribute15 => p_attribute15,
863 p_attribute16 => p_attribute16,
864 p_attribute17 => p_attribute17,
865 p_attribute18 => p_attribute18,
866 p_attribute19 => p_attribute19,
867 p_attribute20 => p_attribute20,
868 p_attribute21 => p_attribute21,
869 p_attribute22 => p_attribute22,
870 p_attribute23 => p_attribute23,
871 p_attribute24 => p_attribute24,
872 p_attribute25 => p_attribute25,
873 p_attribute26 => p_attribute26,
874 p_attribute27 => p_attribute27,
875 p_attribute28 => p_attribute28,
876 p_attribute29 => p_attribute29,
877 p_attribute30 => p_attribute30,
878 p_object_version_number => p_object_version_number,
879 p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate),
880 p_effective_date => p_effective_date);
881
882 <<only_transaction>> -- label for GOTO
883
884 save_transaction(
885 p_phone_id => p_phone_id
886 , p_item_type => p_item_type
887 , p_item_key => p_item_key
888 , p_activity_id => p_activity_id
889 , p_date_from => p_date_from
890 , p_phone_type => p_phone_type
891 , p_phone_number => p_phone_number
892 , p_parent_id => p_parent_id
893 , p_login_person_id => p_login_person_id
894 , p_effective_date => p_effective_date
895 , p_object_version_number => p_object_version_number
896 , p_phone_type_meaning => p_phone_type_meaning
897 , p_attribute_category => p_attribute_category
898 ,p_per_or_contact => p_per_or_contact
899 , p_attribute1 => p_attribute1
900 , p_attribute2 => p_attribute2
901 , p_attribute3 => p_attribute3
902 , p_attribute4 => p_attribute4
903 , p_attribute5 => p_attribute5
904 , p_attribute6 => p_attribute6
905 , p_attribute7 => p_attribute7
906 , p_attribute8 => p_attribute8
907 , p_attribute9 => p_attribute9
908 , p_attribute10 => p_attribute10
909 , p_attribute11 => p_attribute11
910 , p_attribute12 => p_attribute12
911 , p_attribute13 => p_attribute13
912 , p_attribute14 => p_attribute14
913 , p_attribute15 => p_attribute15
914 , p_attribute16 => p_attribute16
915 , p_attribute17 => p_attribute17
916 , p_attribute18 => p_attribute18
917 , p_attribute19 => p_attribute19
918 , p_attribute20 => p_attribute20
919 , p_attribute21 => p_attribute21
920 , p_attribute22 => p_attribute22
921 , p_attribute23 => p_attribute23
922 , p_attribute24 => p_attribute24
923 , p_attribute25 => p_attribute25
924 , p_attribute26 => p_attribute26
925 , p_attribute27 => p_attribute27
926 , p_attribute28 => p_attribute28
927 , p_attribute29 => p_attribute29
928 , p_attribute30 => p_attribute30
929 , p_error_message => l_error_message
930 , p_contact_relationship_id=>p_contact_relationship_id
931 );
932
933 -- check if we got any errors
934 IF (l_error_message IS NOT NULL) THEN
935 hr_utility.set_location('l_error_message IS NOT NULL:'||l_proc,15);
936 raise g_save_transaction_error;
937 END IF;
938
939 hr_utility.set_location('Exiting:'||l_proc, 20);
940 EXCEPTION
941 WHEN g_save_transaction_error THEN
942 -- No need to call formatted_error_message, as the messages is already
943 -- formatted.
944 hr_utility.set_location('Exception:g_save_transaction_error'||l_proc,555);
945 p_error_message := l_error_message;
946 WHEN hr_utility.hr_error THEN
947 -- -------------------------------------------
948 -- an application error has been raised so we must
949 -- redisplay the web form to display the error
950 -- --------------------------------------------
951 hr_utility.set_location('Exception:hr_utility.hr_error'||l_proc,560);
952 hr_message.provide_error;
953 l_message_number := hr_message.last_message_number;
954 IF l_message_number = 'APP-7165' OR
955 l_message_number = 'APP-7155' THEN
956 --populate the p_error_message out variable
957 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
958 p_error_message => p_error_message,
959 p_attr_name => 'Page',
960 p_app_short_name => 'PER',
961 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
962 ELSE
963 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
964 p_error_message => p_error_message);
965 END IF;
966 WHEN OTHERS THEN
967 hr_utility.set_location('Exception:Others'||l_proc,565);
968 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
969 p_error_message => p_error_message);
970
971 end update_phone;
972
973 /*
974 ||===========================================================================
975 || PROCEDURE: delete_phone
976 ||---------------------------------------------------------------------------
977 ||
978 || Description:
979 || Description:
980 || This procedure will call the actual API -
981 || hr_phone_api.delete_phone()
982 ||
983 || Pre Conditions:
984 ||
985 || In Arguments:
986 || Contains entire list of parameters that are defined in the actual
987 || API. For details see pephnapi.pkb file.
988 ||
989 || out nocopy Arguments:
990 ||
991 || In out nocopy Arguments:
992 ||
993 || Post Success:
994 || Executes the API call.
995 ||
996 || Post Failure:
997 || Raises an exception
998 ||
999 || Access Status:
1000 || Public.
1001 ||
1002 ||===========================================================================
1003 */
1004
1005 procedure delete_phone(p_validate number
1006 , p_phone_id NUMBER
1007 , p_object_version_number NUMBER
1008 , p_parent_id in number
1009 --
1010 -- PB Add :
1011 -- The transaction steps have to be created by the login personid.
1012 -- In case of adding phones for contacts parent_is is contact_person_id.
1013 -- Login person id is say employee who is adding the phones to his contact.
1014 --
1015 , p_login_person_id NUMBER default hr_api.g_number
1016 , p_item_type in varchar2
1017 , p_item_key in varchar2
1018 , p_activity_id in number
1019 , p_phone_type_meaning in varchar2
1020 , p_save_mode in varchar2 default null
1021 , p_error_message out nocopy varchar2
1022 , p_per_or_contact varchar2 default null
1023 )
1024 as
1025
1026 l_transaction_id number default null;
1027 l_transaction_step_id number default null;
1028 l_trans_obj_vers_num number default null;
1029 l_count integer default 0;
1030 l_transaction_table hr_transaction_ss.transaction_table;
1031 l_review_item_name varchar2(50);
1032 l_message_number VARCHAR2(10);
1033 l_error_message long default null;
1034 l_proc varchar2(72) := g_package||'delete_phone';
1035
1036 begin
1037 hr_utility.set_location('Entering:'||l_proc, 5);
1038 IF (p_save_mode = 'SAVE_FOR_LATER') THEN
1039 hr_utility.set_location('SFL:Goto only_txn'||l_proc,10);
1040 GOTO only_transaction;
1041 END IF;
1042
1043 -- here's the delegated call to the old PL/SQL routine
1044 hr_phone_api.delete_phone(
1045 p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate),
1046 p_phone_id => p_phone_id,
1047 p_object_version_number => p_object_version_number);
1048
1049 <<only_transaction>> -- label for GOTO
1050
1051 save_transaction(
1052 p_phone_id => p_phone_id
1053 , p_item_type => p_item_type
1054 , p_item_key => p_item_key
1055 , p_activity_id => p_activity_id
1056 , p_phone_type => 'DELETE'
1057 , p_phone_number => 'DELETE_NUMBER'
1058 , p_parent_id => p_parent_id
1059 , p_login_person_id => p_login_person_id
1060 , p_per_or_contact => p_per_or_contact
1061 , p_object_version_number => p_object_version_number
1062 , p_phone_type_meaning => p_phone_type_meaning
1063 , p_error_message => l_error_message);
1064
1065 -- check if we got any errors
1066 IF (l_error_message IS NOT NULL) THEN
1067 hr_utility.set_location('l_error_message IS NOT NULL:'||l_proc,15);
1068 raise g_save_transaction_error;
1069 END IF;
1070
1071 hr_utility.set_location('Exiting:'||l_proc, 20);
1072 EXCEPTION
1073 WHEN g_save_transaction_error THEN
1074 -- No need to call formatted_error_message, as the messages is already
1075 -- formatted.
1076 hr_utility.set_location('Exception:g_save_transaction_error'||l_proc,555);
1077 p_error_message := l_error_message;
1078 WHEN hr_utility.hr_error THEN
1079 -- -------------------------------------------
1080 -- an application error has been raised so we must
1081 -- redisplay the web form to display the error
1082 -- --------------------------------------------
1083 hr_utility.set_location('Exception:hr_utility.hr_error'||l_proc,560);
1084 hr_message.provide_error;
1085 l_message_number := hr_message.last_message_number;
1086 IF l_message_number = 'APP-7165' OR
1087 l_message_number = 'APP-7155' THEN
1088 --populate the p_error_message out variable
1089 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1090 p_error_message => p_error_message,
1091 p_attr_name => 'Page',
1092 p_app_short_name => 'PER',
1093 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
1094 ELSE
1095 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1096 p_error_message => p_error_message);
1097 END IF;
1098 WHEN OTHERS THEN
1099 hr_utility.set_location('Exception:Others'||l_proc,565);
1100 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1101 p_error_message => p_error_message);
1102
1103 end delete_phone;
1104
1105 /*
1106 ||===========================================================================
1107 || PROCEDURE: create_or_update_phone
1108 ||---------------------------------------------------------------------------
1109 ||
1110 || Description:
1111 || Description:
1112 || This procedure will call the actual API -
1113 || hr_phone_api.create_or_update_phone()
1114 ||
1115 || Pre Conditions:
1116 ||
1117 || In Arguments:
1118 || Contains entire list of parameters that are defined in the actual
1119 || API. For details see pephnapi.pkb file.
1120 ||
1121 || out nocopy Arguments:
1122 ||
1123 || In out nocopy Arguments:
1124 ||
1125 || Post Success:
1126 || Executes the API call.
1127 ||
1128 || Post Failure:
1129 || Raises an exception
1130 ||
1131 || Access Status:
1132 || Public.
1133 ||
1134 ||===========================================================================
1135 */
1136
1137 procedure create_or_update_phone(p_update_mode VARCHAR2
1138 , p_phone_id in out nocopy NUMBER
1139 , p_object_version_number in out nocopy NUMBER
1140 , p_date_from date
1141 , p_date_to date
1142 , p_phone_type VARCHAR2
1143 , p_phone_number VARCHAR2
1144 , p_parent_id NUMBER
1145 , p_parent_table VARCHAR2
1146 , p_attribute_category VARCHAR2
1147 , p_attribute1 VARCHAR2 default hr_api.g_varchar2
1148 , p_attribute2 VARCHAR2 default hr_api.g_varchar2
1149 , p_attribute3 VARCHAR2 default hr_api.g_varchar2
1150 , p_attribute4 VARCHAR2 default hr_api.g_varchar2
1151 , p_attribute5 VARCHAR2 default hr_api.g_varchar2
1152 , p_attribute6 VARCHAR2 default hr_api.g_varchar2
1153 , p_attribute7 VARCHAR2 default hr_api.g_varchar2
1154 , p_attribute8 VARCHAR2 default hr_api.g_varchar2
1155 , p_attribute9 VARCHAR2 default hr_api.g_varchar2
1156 , p_attribute10 VARCHAR2 default hr_api.g_varchar2
1157 , p_attribute11 VARCHAR2 default hr_api.g_varchar2
1158 , p_attribute12 VARCHAR2 default hr_api.g_varchar2
1159 , p_attribute13 VARCHAR2 default hr_api.g_varchar2
1160 , p_attribute14 VARCHAR2 default hr_api.g_varchar2
1161 , p_attribute15 VARCHAR2 default hr_api.g_varchar2
1162 , p_attribute16 VARCHAR2 default hr_api.g_varchar2
1163 , p_attribute17 VARCHAR2 default hr_api.g_varchar2
1164 , p_attribute18 VARCHAR2 default hr_api.g_varchar2
1165 , p_attribute19 VARCHAR2 default hr_api.g_varchar2
1166 , p_attribute20 VARCHAR2 default hr_api.g_varchar2
1167 , p_attribute21 VARCHAR2 default hr_api.g_varchar2
1168 , p_attribute22 VARCHAR2 default hr_api.g_varchar2
1169 , p_attribute23 VARCHAR2 default hr_api.g_varchar2
1170 , p_attribute24 VARCHAR2 default hr_api.g_varchar2
1171 , p_attribute25 VARCHAR2 default hr_api.g_varchar2
1172 , p_attribute26 VARCHAR2 default hr_api.g_varchar2
1173 , p_attribute27 VARCHAR2 default hr_api.g_varchar2
1174 , p_attribute28 VARCHAR2 default hr_api.g_varchar2
1175 , p_attribute29 VARCHAR2 default hr_api.g_varchar2
1176 , p_attribute30 VARCHAR2 default hr_api.g_varchar2
1177 , p_validate number
1178 , p_effective_date date
1179 , p_item_type in varchar2
1180 , p_item_key in varchar2
1181 , p_activity_id in number
1182 , p_phone_type_meaning in varchar2
1183 )
1184 as
1185 l_proc varchar2(72) := g_package||'create_or_update_phone';
1186
1187 begin
1188
1189 -- here's the delegated call to the old PL/SQL routine
1190 hr_utility.set_location('Entering:'||l_proc, 5);
1191 hr_phone_api.create_or_update_phone(
1192 p_update_mode => p_update_mode,
1193 p_phone_id => p_phone_id,
1194 p_object_version_number => p_object_version_number,
1195 p_date_from => p_date_from,
1196 p_phone_type => p_phone_type,
1197 p_phone_number => p_phone_number,
1198 p_parent_id => p_parent_id,
1199 p_parent_table => p_parent_table,
1200 p_attribute_category => p_attribute_category,
1201 p_attribute1 => p_attribute1,
1202 p_attribute2 => p_attribute2,
1203 p_attribute3 => p_attribute3,
1204 p_attribute4 => p_attribute4,
1205 p_attribute5 => p_attribute5,
1206 p_attribute6 => p_attribute6,
1207 p_attribute7 => p_attribute7,
1208 p_attribute8 => p_attribute8,
1209 p_attribute9 => p_attribute9,
1210 p_attribute10 => p_attribute10,
1211 p_attribute11 => p_attribute11,
1212 p_attribute12 => p_attribute12,
1213 p_attribute13 => p_attribute13,
1214 p_attribute14 => p_attribute14,
1215 p_attribute15 => p_attribute15,
1216 p_attribute16 => p_attribute16,
1217 p_attribute17 => p_attribute17,
1218 p_attribute18 => p_attribute18,
1219 p_attribute19 => p_attribute19,
1220 p_attribute20 => p_attribute20,
1221 p_attribute21 => p_attribute21,
1222 p_attribute22 => p_attribute22,
1223 p_attribute23 => p_attribute23,
1224 p_attribute24 => p_attribute24,
1225 p_attribute25 => p_attribute25,
1226 p_attribute26 => p_attribute26,
1227 p_attribute27 => p_attribute27,
1228 p_attribute28 => p_attribute28,
1229 p_attribute29 => p_attribute29,
1230 p_attribute30 => p_attribute30,
1231 p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate),
1232 p_effective_date => p_effective_date);
1233 hr_utility.set_location('Exiting:'||l_proc, 10);
1234 end create_or_update_phone;
1235
1236 -- ---------------------------------------------------------------------------
1237 -- ---------------------- < get_phone_data_from_tt> -------------------------
1238 -- ---------------------------------------------------------------------------
1239 -- Purpose: This procedure will get transaction data which are saved earlier
1240 -- in the current transaction. This is invoked when a user click BACK
1241 -- button to go back from the Review page to Update page to correct
1242 -- typos or make further changes or vice-versa. Hence, we need to use
1243 -- the item_type item_key passed in to retrieve the transaction record.
1244 -- ---------------------------------------------------------------------------
1245 PROCEDURE get_phone_data_from_tt
1246 (p_item_type in varchar2
1247 ,p_item_key in varchar2
1248 ,p_activity_id in varchar2
1249 ,p_trans_rec_count out nocopy number
1250 ,p_person_id out nocopy number
1251 ,p_phone_numbers_data out nocopy varchar2
1252 ) is
1253
1254 --
1255 -- Cursor to detect phone type transaction data.
1256 cursor csr_hatv(cv_transaction_step_id in number,
1257 cv_name in varchar2) is
1258 select hatv.number_value
1259 from hr_api_transaction_values hatv
1260 where hatv.transaction_step_id = cv_transaction_step_id
1261 and hatv.name = cv_name;
1262 --
1263 l_trans_step_id number default null;
1264 -- l_trans_obj_vers_num number default null;
1265 l_trans_rec_count integer default 0;
1266 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1267 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
1268 l_trans_step_rows NUMBER ;
1269 ln_index number default 0;
1270 l_phone_data varchar2(4000);
1271 l_dummy number;
1272 l_proc varchar2(72) := g_package||'get_phone_data_from_tt';
1273
1274 BEGIN
1275 -- ------------------------------------------------------------------
1276 -- Check if there are any transaction rec already saved for the current
1277 -- transaction. This is used for re-display the Update page when a user
1278 -- clicks the Back button on the Review page to go back to the Update page
1279 -- to make further changes or to correct errors.
1280 -----------------------------------------------------------------------------
1281 -- For a given item key, there could be multiple transaction steps saved.
1282
1283 hr_utility.set_location('Entering:'||l_proc, 5);
1284 hr_transaction_api.get_transaction_step_info
1285 (p_item_type => p_item_type
1286 ,p_item_key => p_item_key
1287 ,p_activity_id => p_activity_id
1288 ,p_transaction_step_id => l_trans_step_ids
1289 ,p_object_version_number => l_trans_obj_vers_nums
1290 ,p_rows => l_trans_step_rows);
1291
1292 --
1293 -- ---------------------------------------------------------------------
1294 -- NOTE:We need to adjust the index which referrences l_trans_step_ids
1295 -- by 1 because that table was created with the index starts at 0
1296 -- in hr_transaction_api.get_transaction_step_info.
1297 -- ---------------------------------------------------------------------
1298 --
1299 ln_index := 0;
1300
1301 hr_utility.set_location('Entering For Loop 1..l_trans_step_rows:'||l_proc,10);
1302 FOR j in 1..l_trans_step_rows
1303 LOOP
1304 --
1305 -- Now get the transaction data for the given step
1306 -- Get the Phone transaction data only.
1307 --
1308 begin
1309 --
1310 -- We can't relay on the exception from the procedure to
1311 -- determine whether the step is of phone or not use a
1312 -- direct cursor.
1313 --
1314 /*
1315 l_dummy := hr_transaction_api.get_number_Value
1316 (p_transaction_step_id =>
1317 l_trans_step_ids(ln_index)
1318 ,p_name => 'P_PHONE_ID');
1319 */
1320 --
1321 for l_rec in csr_hatv(l_trans_step_ids(ln_index), 'P_PHONE_ID') loop
1322 --
1323 get_phone_data_from_tt(
1324 p_transaction_step_id => l_trans_step_ids(ln_index)
1325 ,p_person_id => p_person_id
1326 ,p_phone_data => l_phone_data);
1327 --
1328 l_trans_rec_count := l_trans_rec_count + 1;
1329 p_phone_numbers_data := p_phone_numbers_data||l_phone_data||'~';
1330 --
1331 end loop;
1332 --
1333 exception
1334 when others then
1335 hr_utility.set_location('Exception:Others'||l_proc,555);
1336 null;
1337 end;
1338 ln_index := ln_index + 1;
1339 END LOOP;
1340 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
1341
1342 p_trans_rec_count := l_trans_rec_count;
1343 hr_utility.set_location('Exiting:'||l_proc, 20);
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 hr_utility.set_location('Exception:Others'||l_proc,555);
1347 RAISE;
1348
1349 END get_phone_data_from_tt;
1350
1351 -- ---------------------------------------------------------------------------
1352 -- ---------------------- < get_phone_data_from_tt> -------------------------
1353 -- ---------------------------------------------------------------------------
1354 -- Purpose: This procedure will get transaction data which are pending for
1355 -- approval in workflow for a transaction step id.
1356 -- This is a overloaded version
1357 -- ---------------------------------------------------------------------------
1358 procedure get_phone_data_from_tt
1359 (p_transaction_step_id in number
1360 ,p_person_id out nocopy number
1361 ,p_phone_data out nocopy varchar2
1362 )is
1363
1364 l_phone_id per_phones.phone_id%type;
1365 l_phone_type per_phones.phone_type%type;
1366 l_phone_number per_phones.phone_number%type;
1367 l_object_version_number per_phones.object_version_number%type;
1368 l_phone_type_meaning hr_lookups.meaning%TYPE;
1369 --StartRgistration
1370 l_contact_set varchar2(20);
1371 l_proc varchar2(72) := g_package||'get_phone_data_from_tt';
1372
1373 begin
1374
1375
1376 --
1377 hr_utility.set_location('Entering:'||l_proc, 5);
1378 p_person_id := hr_transaction_api.get_number_value
1379 (p_transaction_step_id => p_transaction_step_id
1380 ,p_name => 'P_PERSON_ID');
1381 --
1382 l_phone_id := hr_transaction_api.get_number_value
1383 (p_transaction_step_id => p_transaction_step_id
1384 ,p_name => 'P_PHONE_ID');
1385 --
1386 l_phone_type := hr_transaction_api.get_varchar2_value
1387 (p_transaction_step_id => p_transaction_step_id
1388 ,p_name => 'P_PHONE_TYPE');
1389 --
1390 l_phone_type_meaning := hr_transaction_api.get_varchar2_Value
1391 (p_transaction_step_id => p_transaction_step_id
1392 ,p_name => 'P_PHONE_TYPE_MEANING');
1393 --
1394 l_phone_number := hr_transaction_api.get_varchar2_value
1395 (p_transaction_step_id => p_transaction_step_id
1396 ,p_name => 'P_PHONE_NUMBER');
1397 --
1398 l_object_version_number := hr_transaction_api.get_number_value
1399 (p_transaction_step_id => p_transaction_step_id
1400 ,p_name => 'P_OBJECT_VERSION_NUMBER');
1401 --
1402 --StartRegistration
1403 --
1404 begin
1405 l_contact_set := hr_transaction_api.get_varchar2_value
1406 (p_transaction_step_id => p_transaction_step_id
1407 ,p_name => 'P_CONTACT_SET');
1408 exception
1409 when others then
1410 hr_utility.set_location('Exception:Others'||l_proc,555);
1411 l_contact_set := 1;
1412 end;
1413 if l_contact_set is null then
1414 l_contact_set := 1;
1415 end if;
1416
1417 --
1418 --EndRegistration
1419 --
1420 -- Now string all the retreived items into phone_data
1421 -- Added more data for Registration
1422 --
1423
1424 p_phone_data := nvl(l_phone_id,0)||'^'||l_phone_type||'^'||l_phone_type_meaning||'^'||l_phone_number||'^'||nvl(l_object_version_number,0)||'^'||nvl(l_contact_set,0)||'^'||nvl(l_contact_set,0); -- Packer
1425 hr_utility.set_location('Exiting:'||l_proc, 15);
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 hr_utility.set_location('Exception:Others'||l_proc,555);
1430 RAISE;
1431
1432 END get_phone_data_from_tt;
1433
1434 PROCEDURE get_transaction_details
1435 (p_item_type in varchar2
1436 ,p_item_key in varchar2
1437 ,p_activity_id in varchar2
1438 ,p_trans_rec_count out nocopy number
1439 ,p_person_id out nocopy number
1440 ,p_phone_numbers_details in out nocopy sshr_phone_details_tab_typ
1441 ) IS
1442 --
1443 -- Cursor to detect phone type transaction data.
1444 cursor csr_hatv(cv_transaction_step_id in number,
1445 cv_name in varchar2) is
1446 select hatv.number_value
1447 from hr_api_transaction_values hatv
1448 where hatv.transaction_step_id = cv_transaction_step_id
1449 and hatv.name = cv_name;
1450 --
1451 l_trans_step_id number default null;
1452 -- l_trans_obj_vers_num number default null;
1453 l_trans_rec_count integer default 0;
1454 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1455 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
1456 l_trans_step_rows NUMBER ;
1457 ln_index number default 0;
1458 i number default 1;
1459 l_proc varchar2(72) := g_package||'get_transaction_details';
1460
1461 BEGIN
1462 -- ------------------------------------------------------------------
1463 -- Check if there are any transaction rec already saved for the current
1464 -- transaction. This is used for re-display the Update page when a user
1465 -- clicks the Back button on the Review page to go back to the Update page
1466 -- to make further changes or to correct errors.
1467 -----------------------------------------------------------------------------
1468 -- For a given item key, there could be multiple transaction steps saved.
1469
1470 hr_utility.set_location('Entering:'||l_proc, 5);
1471 hr_transaction_api.get_transaction_step_info
1472 (p_item_type => p_item_type
1473 ,p_item_key => p_item_key
1474 ,p_activity_id => p_activity_id
1475 ,p_transaction_step_id => l_trans_step_ids
1476 ,p_object_version_number => l_trans_obj_vers_nums
1477 ,p_rows => l_trans_step_rows);
1478
1479 --
1480 -- ---------------------------------------------------------------------
1481 -- NOTE:We need to adjust the index which referrences l_trans_step_ids
1482 -- by 1 because that table was created with the index starts at 0
1483 -- in hr_transaction_api.get_transaction_step_info.
1484 -- ---------------------------------------------------------------------
1485 --
1486 ln_index := 0;
1487
1488 hr_utility.set_location('Entering For Loop:1..l_trans_step_rows'||l_proc,10);
1489 FOR j in 1..l_trans_step_rows
1490 LOOP
1491 --
1492 -- Now get the transaction data for the given step
1493 -- Get the Phone transaction data only.
1494 --
1495 BEGIN
1496 FOR l_rec in csr_hatv(l_trans_step_ids(ln_index), 'P_PHONE_ID')
1497 LOOP
1498 ----
1499 p_person_id := hr_transaction_api.get_number_value
1500 (p_transaction_step_id => l_trans_step_ids(ln_index)
1501 ,p_name => 'P_PERSON_ID');
1502 --
1503 p_phone_numbers_details(i).phone_id := to_char(nvl(hr_transaction_api.get_number_value
1504 (p_transaction_step_id => l_trans_step_ids(ln_index)
1505 ,p_name => 'P_PHONE_ID'), 0));
1506 --
1507 p_phone_numbers_details(i).phone_type := hr_transaction_api.get_varchar2_value
1508 (p_transaction_step_id => l_trans_step_ids(ln_index)
1509 ,p_name => 'P_PHONE_TYPE');
1510 --
1511 p_phone_numbers_details(i).phone_type_meaning := hr_transaction_api.get_varchar2_Value
1512 (p_transaction_step_id => l_trans_step_ids(ln_index)
1513 ,p_name => 'P_PHONE_TYPE_MEANING');
1514 --
1515 p_phone_numbers_details(i).phone_number := hr_transaction_api.get_varchar2_value
1516 (p_transaction_step_id => l_trans_step_ids(ln_index)
1517 ,p_name => 'P_PHONE_NUMBER');
1518 --
1519 p_phone_numbers_details(i).object_version_number := to_char(nvl(hr_transaction_api.get_number_value
1520 (p_transaction_step_id => l_trans_step_ids(ln_index)
1521 ,p_name => 'P_OBJECT_VERSION_NUMBER'), 0));
1522
1523 p_phone_numbers_details(i).attribute_category := hr_transaction_api.get_varchar2_Value
1524 (p_transaction_step_id => l_trans_step_ids(ln_index)
1525 ,p_name => 'P_ATTRIBUTE_CATEGORY');
1526 --
1527 p_phone_numbers_details(i).attribute1 := hr_transaction_api.get_varchar2_Value
1528 (p_transaction_step_id => l_trans_step_ids(ln_index)
1529 ,p_name => 'P_ATTRIBUTE1');
1530 --
1531 p_phone_numbers_details(i).attribute2 := hr_transaction_api.get_varchar2_Value
1532 (p_transaction_step_id => l_trans_step_ids(ln_index)
1533 ,p_name => 'P_ATTRIBUTE2');
1534 --
1535 p_phone_numbers_details(i).attribute3 := hr_transaction_api.get_varchar2_Value
1536 (p_transaction_step_id => l_trans_step_ids(ln_index)
1537 ,p_name => 'P_ATTRIBUTE3');
1538 --
1539 p_phone_numbers_details(i).attribute4 := hr_transaction_api.get_varchar2_Value
1540 (p_transaction_step_id => l_trans_step_ids(ln_index)
1541 ,p_name => 'P_ATTRIBUTE4');
1542 --
1543 p_phone_numbers_details(i).attribute5 := hr_transaction_api.get_varchar2_Value
1544 (p_transaction_step_id => l_trans_step_ids(ln_index)
1545 ,p_name => 'P_ATTRIBUTE5');
1546 --
1547 p_phone_numbers_details(i).attribute6 := hr_transaction_api.get_varchar2_Value
1548 (p_transaction_step_id => l_trans_step_ids(ln_index)
1549 ,p_name => 'P_ATTRIBUTE6');
1550 --
1551 p_phone_numbers_details(i).attribute7 := hr_transaction_api.get_varchar2_Value
1552 (p_transaction_step_id => l_trans_step_ids(ln_index)
1553 ,p_name => 'P_ATTRIBUTE7');
1554 --
1555 p_phone_numbers_details(i).attribute8 := hr_transaction_api.get_varchar2_Value
1556 (p_transaction_step_id => l_trans_step_ids(ln_index)
1557 ,p_name => 'P_ATTRIBUTE8');
1558 --
1559 p_phone_numbers_details(i).attribute9 := hr_transaction_api.get_varchar2_Value
1560 (p_transaction_step_id => l_trans_step_ids(ln_index)
1561 ,p_name => 'P_ATTRIBUTE9');
1562 --
1563 p_phone_numbers_details(i).attribute10 := hr_transaction_api.get_varchar2_Value
1564 (p_transaction_step_id => l_trans_step_ids(ln_index)
1565 ,p_name => 'P_ATTRIBUTE10');
1566 --
1567 p_phone_numbers_details(i).attribute11 := hr_transaction_api.get_varchar2_Value
1568 (p_transaction_step_id => l_trans_step_ids(ln_index)
1569 ,p_name => 'P_ATTRIBUTE11');
1570 --
1571 p_phone_numbers_details(i).attribute12 := hr_transaction_api.get_varchar2_Value
1572 (p_transaction_step_id => l_trans_step_ids(ln_index)
1573 ,p_name => 'P_ATTRIBUTE12');
1574 --
1575 p_phone_numbers_details(i).attribute13 := hr_transaction_api.get_varchar2_Value
1576 (p_transaction_step_id => l_trans_step_ids(ln_index)
1577 ,p_name => 'P_ATTRIBUTE13');
1578 --
1579 p_phone_numbers_details(i).attribute14 := hr_transaction_api.get_varchar2_Value
1580 (p_transaction_step_id => l_trans_step_ids(ln_index)
1581 ,p_name => 'P_ATTRIBUTE14');
1582 --
1583 p_phone_numbers_details(i).attribute15 := hr_transaction_api.get_varchar2_Value
1584 (p_transaction_step_id => l_trans_step_ids(ln_index)
1585 ,p_name => 'P_ATTRIBUTE15');
1586 --
1587 p_phone_numbers_details(i).attribute16 := hr_transaction_api.get_varchar2_Value
1588 (p_transaction_step_id => l_trans_step_ids(ln_index)
1589 ,p_name => 'P_ATTRIBUTE16');
1590 --
1591 p_phone_numbers_details(i).attribute17 := hr_transaction_api.get_varchar2_Value
1592 (p_transaction_step_id => l_trans_step_ids(ln_index)
1593 ,p_name => 'P_ATTRIBUTE17');
1594 --
1595 p_phone_numbers_details(i).attribute18 := hr_transaction_api.get_varchar2_Value
1596 (p_transaction_step_id => l_trans_step_ids(ln_index)
1597 ,p_name => 'P_ATTRIBUTE18');
1598 --
1599 p_phone_numbers_details(i).attribute19 := hr_transaction_api.get_varchar2_Value
1600 (p_transaction_step_id => l_trans_step_ids(ln_index)
1601 ,p_name => 'P_ATTRIBUTE19');
1602 --
1603 p_phone_numbers_details(i).attribute20 := hr_transaction_api.get_varchar2_Value
1604 (p_transaction_step_id => l_trans_step_ids(ln_index)
1605 ,p_name => 'P_ATTRIBUTE20');
1606 --
1607 p_phone_numbers_details(i).attribute21 := hr_transaction_api.get_varchar2_Value
1608 (p_transaction_step_id => l_trans_step_ids(ln_index)
1609 ,p_name => 'P_ATTRIBUTE21');
1610 --
1611 p_phone_numbers_details(i).attribute22 := hr_transaction_api.get_varchar2_Value
1612 (p_transaction_step_id => l_trans_step_ids(ln_index)
1613 ,p_name => 'P_ATTRIBUTE22');
1614 --
1615 p_phone_numbers_details(i).attribute23 := hr_transaction_api.get_varchar2_Value
1616 (p_transaction_step_id => l_trans_step_ids(ln_index)
1617 ,p_name => 'P_ATTRIBUTE23');
1618 --
1619 p_phone_numbers_details(i).attribute24 := hr_transaction_api.get_varchar2_Value
1620 (p_transaction_step_id => l_trans_step_ids(ln_index)
1621 ,p_name => 'P_ATTRIBUTE24');
1622 --
1623 p_phone_numbers_details(i).attribute25 := hr_transaction_api.get_varchar2_Value
1624 (p_transaction_step_id => l_trans_step_ids(ln_index)
1625 ,p_name => 'P_ATTRIBUTE25');
1626 --
1627 p_phone_numbers_details(i).attribute26 := hr_transaction_api.get_varchar2_Value
1628 (p_transaction_step_id => l_trans_step_ids(ln_index)
1629 ,p_name => 'P_ATTRIBUTE26');
1630 --
1631 p_phone_numbers_details(i).attribute27 := hr_transaction_api.get_varchar2_Value
1632 (p_transaction_step_id => l_trans_step_ids(ln_index)
1633 ,p_name => 'P_ATTRIBUTE27');
1634 --
1635 p_phone_numbers_details(i).attribute28 := hr_transaction_api.get_varchar2_Value
1636 (p_transaction_step_id => l_trans_step_ids(ln_index)
1637 ,p_name => 'P_ATTRIBUTE28');
1638 --
1639 p_phone_numbers_details(i).attribute29 := hr_transaction_api.get_varchar2_Value
1640 (p_transaction_step_id => l_trans_step_ids(ln_index)
1641 ,p_name => 'P_ATTRIBUTE29');
1642 --
1643 p_phone_numbers_details(i).attribute30 := hr_transaction_api.get_varchar2_Value
1644 (p_transaction_step_id => l_trans_step_ids(ln_index)
1645 ,p_name => 'P_ATTRIBUTE30');
1646 --
1647 p_phone_numbers_details(i).parent_id := hr_transaction_api.get_number_value
1648 (p_transaction_step_id => l_trans_step_ids(ln_index)
1649 ,p_name => 'P_PERSON_ID');
1650
1651 i := i + 1;
1652 l_trans_rec_count := l_trans_rec_count + 1;
1653 --
1654 END LOOP;
1655 --
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 hr_utility.set_location('Exception:Others'||l_proc,555);
1659 RAISE;
1660 END;
1661 ln_index := ln_index + 1;
1662 END LOOP;
1663 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
1664
1665 p_trans_rec_count := l_trans_rec_count;
1666 hr_utility.set_location('Exiting:'||l_proc, 20);
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 hr_utility.set_location('Exception:Others'||l_proc,555);
1671 RAISE;
1672 END get_transaction_details;
1673
1674 PROCEDURE get_transaction_details
1675 (p_item_type in varchar2
1676 ,p_item_key in varchar2
1677 ,p_activity_id in varchar2
1678 ,p_trans_rec_count out nocopy number
1679 ,p_person_id out nocopy number
1680 ,p_con_phone_numbers_details in out nocopy sshr_con_phone_details_tab_typ
1681 ) IS
1682 --
1683 -- Cursor to detect phone type transaction data.
1684 cursor csr_hatv(cv_transaction_step_id in number,
1685 cv_name in varchar2) is
1686 select hatv.number_value
1687 from hr_api_transaction_values hatv
1688 where hatv.transaction_step_id = cv_transaction_step_id
1689 and hatv.name = cv_name;
1690 --
1691 l_trans_step_id number default null;
1692 -- l_trans_obj_vers_num number default null;
1693 l_trans_rec_count integer default 0;
1694 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1695 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
1696 l_trans_step_rows NUMBER ;
1697 ln_index number default 0;
1698 i number default 1;
1699 l_proc varchar2(72) := g_package||'get_transaction_details';
1700
1701 BEGIN
1702 -- ------------------------------------------------------------------
1703 -- Check if there are any transaction rec already saved for the current
1704 -- transaction. This is used for re-display the Update page when a user
1705 -- clicks the Back button on the Review page to go back to the Update page
1706 -- to make further changes or to correct errors.
1707 -----------------------------------------------------------------------------
1708 -- For a given item key, there could be multiple transaction steps saved.
1709
1710 hr_utility.set_location('Entering:'||l_proc, 5);
1711 hr_transaction_api.get_transaction_step_info
1712 (p_item_type => p_item_type
1713 ,p_item_key => p_item_key
1714 ,p_activity_id => p_activity_id
1715 ,p_transaction_step_id => l_trans_step_ids
1716 ,p_object_version_number => l_trans_obj_vers_nums
1717 ,p_rows => l_trans_step_rows);
1718
1719 --
1720 -- ---------------------------------------------------------------------
1721 -- NOTE:We need to adjust the index which referrences l_trans_step_ids
1722 -- by 1 because that table was created with the index starts at 0
1723 -- in hr_transaction_api.get_transaction_step_info.
1724 -- ---------------------------------------------------------------------
1725 --
1726 ln_index := 0;
1727
1728 hr_utility.set_location('Entering For Loop:1..l_trans_step_rows'||l_proc,10);
1729 FOR j in 1..l_trans_step_rows
1730 LOOP
1731 --
1732 -- Now get the transaction data for the given step
1733 -- Get the Phone transaction data only.
1734 --
1735 BEGIN
1736 FOR l_rec in csr_hatv(l_trans_step_ids(ln_index), 'P_PHONE_ID')
1737 LOOP
1738 ----
1739 p_person_id := hr_transaction_api.get_number_value
1740 (p_transaction_step_id => l_trans_step_ids(ln_index)
1741 ,p_name => 'P_PERSON_ID');
1742 --
1743 p_con_phone_numbers_details(i).phone_id := to_char(nvl(hr_transaction_api.get_number_value
1744 (p_transaction_step_id => l_trans_step_ids(ln_index)
1745 ,p_name => 'P_PHONE_ID'), 0));
1746 --
1747 p_con_phone_numbers_details(i).phone_type := hr_transaction_api.get_varchar2_value
1748 (p_transaction_step_id => l_trans_step_ids(ln_index)
1749 ,p_name => 'P_PHONE_TYPE');
1750 --
1751 p_con_phone_numbers_details(i).phone_type_meaning := hr_transaction_api.get_varchar2_Value
1752 (p_transaction_step_id => l_trans_step_ids(ln_index)
1753 ,p_name => 'P_PHONE_TYPE_MEANING');
1754 --
1755 p_con_phone_numbers_details(i).phone_number := hr_transaction_api.get_varchar2_value
1756 (p_transaction_step_id => l_trans_step_ids(ln_index)
1757 ,p_name => 'P_PHONE_NUMBER');
1758 --
1759 p_con_phone_numbers_details(i).object_version_number := to_char(nvl(hr_transaction_api.get_number_value
1760 (p_transaction_step_id => l_trans_step_ids(ln_index)
1761 ,p_name => 'P_OBJECT_VERSION_NUMBER'), 0));
1762
1763 p_con_phone_numbers_details(i).attribute_category := hr_transaction_api.get_varchar2_Value
1764 (p_transaction_step_id => l_trans_step_ids(ln_index)
1765 ,p_name => 'P_ATTRIBUTE_CATEGORY');
1766 --
1767 p_con_phone_numbers_details(i).attribute1 := hr_transaction_api.get_varchar2_Value
1768 (p_transaction_step_id => l_trans_step_ids(ln_index)
1769 ,p_name => 'P_ATTRIBUTE1');
1770 --
1771 p_con_phone_numbers_details(i).attribute2 := hr_transaction_api.get_varchar2_Value
1772 (p_transaction_step_id => l_trans_step_ids(ln_index)
1773 ,p_name => 'P_ATTRIBUTE2');
1774 --
1775 p_con_phone_numbers_details(i).attribute3 := hr_transaction_api.get_varchar2_Value
1776 (p_transaction_step_id => l_trans_step_ids(ln_index)
1777 ,p_name => 'P_ATTRIBUTE3');
1778 --
1779 p_con_phone_numbers_details(i).attribute4 := hr_transaction_api.get_varchar2_Value
1780 (p_transaction_step_id => l_trans_step_ids(ln_index)
1781 ,p_name => 'P_ATTRIBUTE4');
1782 --
1783 p_con_phone_numbers_details(i).attribute5 := hr_transaction_api.get_varchar2_Value
1784 (p_transaction_step_id => l_trans_step_ids(ln_index)
1785 ,p_name => 'P_ATTRIBUTE5');
1786 --
1787 p_con_phone_numbers_details(i).attribute6 := hr_transaction_api.get_varchar2_Value
1788 (p_transaction_step_id => l_trans_step_ids(ln_index)
1789 ,p_name => 'P_ATTRIBUTE6');
1790 --
1791 p_con_phone_numbers_details(i).attribute7 := hr_transaction_api.get_varchar2_Value
1792 (p_transaction_step_id => l_trans_step_ids(ln_index)
1793 ,p_name => 'P_ATTRIBUTE7');
1794 --
1795 p_con_phone_numbers_details(i).attribute8 := hr_transaction_api.get_varchar2_Value
1796 (p_transaction_step_id => l_trans_step_ids(ln_index)
1797 ,p_name => 'P_ATTRIBUTE8');
1798 --
1799 p_con_phone_numbers_details(i).attribute9 := hr_transaction_api.get_varchar2_Value
1800 (p_transaction_step_id => l_trans_step_ids(ln_index)
1801 ,p_name => 'P_ATTRIBUTE9');
1802 --
1803 p_con_phone_numbers_details(i).attribute10 := hr_transaction_api.get_varchar2_Value
1804 (p_transaction_step_id => l_trans_step_ids(ln_index)
1805 ,p_name => 'P_ATTRIBUTE10');
1806 --
1807 p_con_phone_numbers_details(i).attribute11 := hr_transaction_api.get_varchar2_Value
1808 (p_transaction_step_id => l_trans_step_ids(ln_index)
1809 ,p_name => 'P_ATTRIBUTE11');
1810 --
1811 p_con_phone_numbers_details(i).attribute12 := hr_transaction_api.get_varchar2_Value
1812 (p_transaction_step_id => l_trans_step_ids(ln_index)
1813 ,p_name => 'P_ATTRIBUTE12');
1814 --
1815 p_con_phone_numbers_details(i).attribute13 := hr_transaction_api.get_varchar2_Value
1816 (p_transaction_step_id => l_trans_step_ids(ln_index)
1817 ,p_name => 'P_ATTRIBUTE13');
1818 --
1819 p_con_phone_numbers_details(i).attribute14 := hr_transaction_api.get_varchar2_Value
1820 (p_transaction_step_id => l_trans_step_ids(ln_index)
1821 ,p_name => 'P_ATTRIBUTE14');
1822 --
1823 p_con_phone_numbers_details(i).attribute15 := hr_transaction_api.get_varchar2_Value
1824 (p_transaction_step_id => l_trans_step_ids(ln_index)
1825 ,p_name => 'P_ATTRIBUTE15');
1826 --
1827 p_con_phone_numbers_details(i).attribute16 := hr_transaction_api.get_varchar2_Value
1828 (p_transaction_step_id => l_trans_step_ids(ln_index)
1829 ,p_name => 'P_ATTRIBUTE16');
1830 --
1831 p_con_phone_numbers_details(i).attribute17 := hr_transaction_api.get_varchar2_Value
1832 (p_transaction_step_id => l_trans_step_ids(ln_index)
1833 ,p_name => 'P_ATTRIBUTE17');
1834 --
1835 p_con_phone_numbers_details(i).attribute18 := hr_transaction_api.get_varchar2_Value
1836 (p_transaction_step_id => l_trans_step_ids(ln_index)
1837 ,p_name => 'P_ATTRIBUTE18');
1838 --
1839 p_con_phone_numbers_details(i).attribute19 := hr_transaction_api.get_varchar2_Value
1840 (p_transaction_step_id => l_trans_step_ids(ln_index)
1841 ,p_name => 'P_ATTRIBUTE19');
1842 --
1843 p_con_phone_numbers_details(i).attribute20 := hr_transaction_api.get_varchar2_Value
1844 (p_transaction_step_id => l_trans_step_ids(ln_index)
1845 ,p_name => 'P_ATTRIBUTE20');
1846 --
1847 p_con_phone_numbers_details(i).attribute21 := hr_transaction_api.get_varchar2_Value
1848 (p_transaction_step_id => l_trans_step_ids(ln_index)
1849 ,p_name => 'P_ATTRIBUTE21');
1850 --
1851 p_con_phone_numbers_details(i).attribute22 := hr_transaction_api.get_varchar2_Value
1852 (p_transaction_step_id => l_trans_step_ids(ln_index)
1853 ,p_name => 'P_ATTRIBUTE22');
1854 --
1855 p_con_phone_numbers_details(i).attribute23 := hr_transaction_api.get_varchar2_Value
1856 (p_transaction_step_id => l_trans_step_ids(ln_index)
1857 ,p_name => 'P_ATTRIBUTE23');
1858 --
1859 p_con_phone_numbers_details(i).attribute24 := hr_transaction_api.get_varchar2_Value
1860 (p_transaction_step_id => l_trans_step_ids(ln_index)
1861 ,p_name => 'P_ATTRIBUTE24');
1862 --
1863 p_con_phone_numbers_details(i).attribute25 := hr_transaction_api.get_varchar2_Value
1864 (p_transaction_step_id => l_trans_step_ids(ln_index)
1865 ,p_name => 'P_ATTRIBUTE25');
1866 --
1867 p_con_phone_numbers_details(i).attribute26 := hr_transaction_api.get_varchar2_Value
1868 (p_transaction_step_id => l_trans_step_ids(ln_index)
1869 ,p_name => 'P_ATTRIBUTE26');
1870 --
1871 p_con_phone_numbers_details(i).attribute27 := hr_transaction_api.get_varchar2_Value
1872 (p_transaction_step_id => l_trans_step_ids(ln_index)
1873 ,p_name => 'P_ATTRIBUTE27');
1874 --
1875 p_con_phone_numbers_details(i).attribute28 := hr_transaction_api.get_varchar2_Value
1876 (p_transaction_step_id => l_trans_step_ids(ln_index)
1877 ,p_name => 'P_ATTRIBUTE28');
1878 --
1879 p_con_phone_numbers_details(i).attribute29 := hr_transaction_api.get_varchar2_Value
1880 (p_transaction_step_id => l_trans_step_ids(ln_index)
1881 ,p_name => 'P_ATTRIBUTE29');
1882 --
1883 p_con_phone_numbers_details(i).attribute30 := hr_transaction_api.get_varchar2_Value
1884 (p_transaction_step_id => l_trans_step_ids(ln_index)
1885 ,p_name => 'P_ATTRIBUTE30');
1886 --
1887 p_con_phone_numbers_details(i).parent_id := hr_transaction_api.get_number_value
1888 (p_transaction_step_id => l_trans_step_ids(ln_index)
1889 ,p_name => 'P_PERSON_ID');
1890
1891 --bug#3542613
1892 p_con_phone_numbers_details(i).contact_set := hr_transaction_api.get_varchar2_Value
1893 (p_transaction_step_id => l_trans_step_ids(ln_index)
1894 ,p_name => 'P_CONTACT_SET');
1895
1896 i := i + 1;
1897 l_trans_rec_count := l_trans_rec_count + 1;
1898 --
1899 END LOOP;
1900 --
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 hr_utility.set_location('Exception:Others'||l_proc,555);
1904 RAISE;
1905 END;
1906 ln_index := ln_index + 1;
1907 END LOOP;
1908 hr_utility.set_location('Exiting For Loop:'||l_proc,15);
1909
1910 p_trans_rec_count := l_trans_rec_count;
1911 hr_utility.set_location('Exiting:'||l_proc, 20);
1912
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 hr_utility.set_location('Exception:Others'||l_proc,555);
1916 RAISE;
1917 END get_transaction_details;
1918
1919
1920
1921
1922 /*---------------------------------------------------------------------------+
1923 | |
1924 | Name : process_api |
1925 | |
1926 | Purpose : This will procedure is invoked whenever approver |
1927 | approves the address change. |
1928 | |
1929 +-----------------------------------------------------------------------------*/
1930 PROCEDURE process_api
1931 (p_validate in boolean default false
1932 ,p_transaction_step_id in number
1933 ,p_effective_date in varchar2 default null
1934 )
1935 IS
1936 l_count INTEGER;
1937 l_deleted_phone_count INTEGER;
1938 l_phone_id per_phones.phone_id%TYPE;
1939 l_phone_ovn per_phones.object_version_number%TYPE;
1940 l_phone_type per_phones.phone_type%TYPE;
1941 l_phone_number per_phones.phone_number%TYPE;
1942 l_deleted_ovn per_phones.object_version_number%TYPE;
1943 l_effective_date date;
1944 l_person_id per_all_people_f.person_id%TYPE;
1945 l_per_or_contact varchar2(30);
1946 l_trs_object_version_number
1947 hr_api_transaction_steps.object_version_number%type;
1948 l_transaction_step_id
1949 hr_api_transaction_steps.transaction_step_id%type;
1950 l_check_for_sfl varchar2(10);
1951 l_sfl_g_contact_step_id NUMBER;
1952 l_proc varchar2(72) := g_package||'process_api';
1953
1954
1955 BEGIN
1956 hr_utility.set_location('Entering:'||l_proc, 5);
1957 if (p_effective_date is not null) then
1958 hr_utility.set_location('p_effective_date is not null:'||l_proc,10);
1959 l_effective_date:= to_date(p_effective_date,g_date_format);
1960 else
1961 hr_utility.set_location('p_effective_date is null:'||l_proc,15);
1962 l_effective_date:= to_date(
1963 hr_transaction_ss.get_wf_effective_date
1964 (p_transaction_step_id => p_transaction_step_id),g_date_format);
1965 end if;
1966
1967 l_person_id :=
1968 hr_transaction_api.get_number_Value
1969 (p_transaction_step_id =>
1970 p_transaction_step_id
1971 ,p_name => 'P_PERSON_ID');
1972
1973 savepoint process_phones;
1974 l_phone_number :=
1975 hr_transaction_api.get_varchar2_Value
1976 (p_transaction_step_id =>
1977 p_transaction_step_id
1978 ,p_name => 'P_PHONE_NUMBER');
1979 l_phone_ovn :=
1980 hr_transaction_api.get_number_Value
1981 (p_transaction_step_id =>
1982 p_transaction_step_id
1983 ,p_name => 'P_OBJECT_VERSION_NUMBER');
1984 l_phone_type :=
1985 hr_transaction_api.get_varchar2_Value
1986 (p_transaction_step_id =>
1987 p_transaction_step_id
1988 ,p_name => 'P_PHONE_TYPE');
1989 l_phone_id :=
1990 hr_transaction_api.get_number_Value
1991 (p_transaction_step_id =>
1992 p_transaction_step_id
1993 ,p_name => 'P_PHONE_ID');
1994 IF l_phone_id IS NULL THEN
1995 hr_utility.set_location('IF l_phone_id IS NULL THEN:'||l_proc,20);
1996 -- It's a new phone number.
1997 --
1998 -- PB : Add
1999 --
2000 --
2001 if l_person_id is null or l_person_id < 0 then
2002 --
2003 -- This is the case where the contact was created.
2004 -- So get contact person id from the global.
2005 --
2006 -- StartRegistration
2007 hr_utility.set_location('l_person_id is null or l_person_id < 0:'||l_proc,25);
2008 l_per_or_contact :=
2009 hr_transaction_api.get_varchar2_Value
2010 (p_transaction_step_id =>
2011 p_transaction_step_id
2012 ,p_name => 'P_PER_OR_CONTACT');
2013 --
2014 hr_utility.set_location('Phone.process_api l_per_or_contact : '
2015 || l_per_or_contact, 22);
2016 --
2017 if l_per_or_contact = 'CONTACT' or l_per_or_contact = 'EMER_CR_NEW_CONT' or l_per_or_contact = 'EMRG_OVRW_UPD' or l_per_or_contact = 'EMRG_OVRW_DEL' or l_per_or_contact = 'EMER_CR_NEW_REL'
2018 or l_per_or_contact = 'DPDNT_CR_NEW_CONT' or l_per_or_contact = 'DPDNT_OVRW_UPD' or l_per_or_contact = 'DPDNT_OVRW_DEL' or l_per_or_contact = 'DPDNT_CR_NEW_REL' then
2019
2020 l_person_id := hr_process_contact_ss.g_contact_person_id;
2021
2022 -- In case of SaveForLater run process_create_contact_api
2023 -- in commit mode to get the g_contact_person_id
2024 -- this will be rolled back with the current step
2025 -- after validating the current transaction step
2026
2027 -- In Case of SFL also person id is available due to recent changes
2028 -- in hr_transaction_ss v 115.18
2029 -- So the SFL specific code is not required anymore.
2030
2031 else
2032 -- l_per_or_contact = 'PERSON'
2033 -- l_person_id := hr_process_person_ss.g_person_id;
2034 -- Adding the session id check to avoid connection pooling problems.
2035 if (( hr_process_person_ss.g_person_id is not null) and
2036 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
2037 hr_utility.set_location('hr_process_person_ss.g_person_id is not null AND hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID:'||l_proc,30);
2038 l_person_id := hr_process_person_ss.g_person_id;
2039 end if;
2040 end if;
2041 --
2042 end if;
2043 --
2044 hr_utility.set_location('Phone.process_api l_person_id : '
2045 || l_person_id, 22);
2046 hr_phone_api.create_phone
2047 (p_validate => FALSE
2048 ,p_phone_id => l_phone_id
2049 ,p_phone_number => l_phone_number
2050 ,p_object_version_number => l_phone_ovn
2051 ,p_phone_type => l_phone_type
2052 ,p_effective_date => l_effective_date
2053 ,p_parent_id => l_person_id
2054 ,p_parent_table => 'PER_ALL_PEOPLE_F'
2055 ,p_date_from => l_Effective_date
2056 ,p_attribute_category => hr_transaction_api.get_varchar2_value
2057 (p_transaction_step_id => p_transaction_step_id
2058 ,p_name => 'P_ATTRIBUTE_CATEGORY')
2059 ,p_attribute1 => hr_transaction_api.get_varchar2_value
2060 (p_transaction_step_id => p_transaction_step_id
2061 ,p_name => 'P_ATTRIBUTE1')
2062 ,p_attribute2 => hr_transaction_api.get_varchar2_value
2063 (p_transaction_step_id => p_transaction_step_id
2064 ,p_name => 'P_ATTRIBUTE2')
2065 ,p_attribute3 => hr_transaction_api.get_varchar2_value
2066 (p_transaction_step_id => p_transaction_step_id
2067 ,p_name => 'P_ATTRIBUTE3')
2068 ,p_attribute4 => hr_transaction_api.get_varchar2_value
2069 (p_transaction_step_id => p_transaction_step_id
2070 ,p_name => 'P_ATTRIBUTE4')
2071 ,p_attribute5 => hr_transaction_api.get_varchar2_value
2072 (p_transaction_step_id => p_transaction_step_id
2073 ,p_name => 'P_ATTRIBUTE5')
2074 ,p_attribute6 => hr_transaction_api.get_varchar2_value
2075 (p_transaction_step_id => p_transaction_step_id
2076 ,p_name => 'P_ATTRIBUTE6')
2077 ,p_attribute7 => hr_transaction_api.get_varchar2_value
2078 (p_transaction_step_id => p_transaction_step_id
2079 ,p_name => 'P_ATTRIBUTE7')
2080 ,p_attribute8 => hr_transaction_api.get_varchar2_value
2081 (p_transaction_step_id => p_transaction_step_id
2082 ,p_name => 'P_ATTRIBUTE8')
2083 ,p_attribute9 => hr_transaction_api.get_varchar2_value
2084 (p_transaction_step_id => p_transaction_step_id
2085 ,p_name => 'P_ATTRIBUTE9')
2086 ,p_attribute10 => hr_transaction_api.get_varchar2_value
2087 (p_transaction_step_id => p_transaction_step_id
2088 ,p_name => 'P_ATTRIBUTE10')
2089 ,p_attribute11 => hr_transaction_api.get_varchar2_value
2090 (p_transaction_step_id => p_transaction_step_id
2091 ,p_name => 'P_ATTRIBUTE11')
2092 ,p_attribute12 => hr_transaction_api.get_varchar2_value
2093 (p_transaction_step_id => p_transaction_step_id
2094 ,p_name => 'P_ATTRIBUTE12')
2095 ,p_attribute13 => hr_transaction_api.get_varchar2_value
2096 (p_transaction_step_id => p_transaction_step_id
2097 ,p_name => 'P_ATTRIBUTE13')
2098 ,p_attribute14 => hr_transaction_api.get_varchar2_value
2099 (p_transaction_step_id => p_transaction_step_id
2100 ,p_name => 'P_ATTRIBUTE14')
2101 ,p_attribute15 => hr_transaction_api.get_varchar2_value
2102 (p_transaction_step_id => p_transaction_step_id
2103 ,p_name => 'P_ATTRIBUTE15')
2104 ,p_attribute16 => hr_transaction_api.get_varchar2_value
2105 (p_transaction_step_id => p_transaction_step_id
2106 ,p_name => 'P_ATTRIBUTE16')
2107 ,p_attribute17 => hr_transaction_api.get_varchar2_value
2108 (p_transaction_step_id => p_transaction_step_id
2109 ,p_name => 'P_ATTRIBUTE17')
2110 ,p_attribute18 => hr_transaction_api.get_varchar2_value
2111 (p_transaction_step_id => p_transaction_step_id
2112 ,p_name => 'P_ATTRIBUTE18')
2113 ,p_attribute19 => hr_transaction_api.get_varchar2_value
2114 (p_transaction_step_id => p_transaction_step_id
2115 ,p_name => 'P_ATTRIBUTE19')
2116 ,p_attribute20 => hr_transaction_api.get_varchar2_value
2117 (p_transaction_step_id => p_transaction_step_id
2118 ,p_name => 'P_ATTRIBUTE20')
2119 ,p_attribute21 => hr_transaction_api.get_varchar2_value
2120 (p_transaction_step_id => p_transaction_step_id
2121 ,p_name => 'P_ATTRIBUTE21')
2122 ,p_attribute22 => hr_transaction_api.get_varchar2_value
2123 (p_transaction_step_id => p_transaction_step_id
2124 ,p_name => 'P_ATTRIBUTE22')
2125 ,p_attribute23 => hr_transaction_api.get_varchar2_value
2126 (p_transaction_step_id => p_transaction_step_id
2127 ,p_name => 'P_ATTRIBUTE23')
2128 ,p_attribute24 => hr_transaction_api.get_varchar2_value
2129 (p_transaction_step_id => p_transaction_step_id
2130 ,p_name => 'P_ATTRIBUTE24')
2131 ,p_attribute25 => hr_transaction_api.get_varchar2_value
2132 (p_transaction_step_id => p_transaction_step_id
2133 ,p_name => 'P_ATTRIBUTE25')
2134 ,p_attribute26 => hr_transaction_api.get_varchar2_value
2135 (p_transaction_step_id => p_transaction_step_id
2136 ,p_name => 'P_ATTRIBUTE26')
2137 ,p_attribute27 => hr_transaction_api.get_varchar2_value
2138 (p_transaction_step_id => p_transaction_step_id
2139 ,p_name => 'P_ATTRIBUTE27')
2140 ,p_attribute28 => hr_transaction_api.get_varchar2_value
2141 (p_transaction_step_id => p_transaction_step_id
2142 ,p_name => 'P_ATTRIBUTE28')
2143 ,p_attribute29 => hr_transaction_api.get_varchar2_value
2144 (p_transaction_step_id => p_transaction_step_id
2145 ,p_name => 'P_ATTRIBUTE29')
2146 ,p_attribute30 => hr_transaction_api.get_varchar2_value
2147 (p_transaction_step_id => p_transaction_step_id
2148 ,p_name => 'P_ATTRIBUTE30'));
2149 ELSIF l_phone_type = 'DELETE' THEN
2150 -- Delete the existing phone nuber.
2151 hr_utility.set_location('l_phone_type = DELETE THEN:'||l_proc,35);
2152 hr_phone_api.delete_phone
2153 (p_validate => FALSE
2154 ,p_phone_id => l_phone_id
2155 ,p_object_version_number => l_phone_ovn
2156 );
2157 ELSE
2158 -- Update the existing phone number.
2159 hr_phone_api.update_phone
2160 (p_validate => FALSE
2161 ,p_phone_id => l_phone_id
2162 ,p_phone_number => l_phone_number
2163 ,p_phone_type => l_phone_type
2164 ,p_object_version_number => l_phone_ovn
2165 ,p_effective_date => l_effective_date
2166 ,p_attribute_category => hr_transaction_api.get_varchar2_value
2167 (p_transaction_step_id => p_transaction_step_id
2168 ,p_name => 'P_ATTRIBUTE_CATEGORY')
2169 ,p_attribute1 => hr_transaction_api.get_varchar2_value
2170 (p_transaction_step_id => p_transaction_step_id
2171 ,p_name => 'P_ATTRIBUTE1')
2172 ,p_attribute2 => hr_transaction_api.get_varchar2_value
2173 (p_transaction_step_id => p_transaction_step_id
2174 ,p_name => 'P_ATTRIBUTE2')
2175 ,p_attribute3 => hr_transaction_api.get_varchar2_value
2176 (p_transaction_step_id => p_transaction_step_id
2177 ,p_name => 'P_ATTRIBUTE3')
2178 ,p_attribute4 => hr_transaction_api.get_varchar2_value
2179 (p_transaction_step_id => p_transaction_step_id
2180 ,p_name => 'P_ATTRIBUTE4')
2181 ,p_attribute5 => hr_transaction_api.get_varchar2_value
2182 (p_transaction_step_id => p_transaction_step_id
2183 ,p_name => 'P_ATTRIBUTE5')
2184 ,p_attribute6 => hr_transaction_api.get_varchar2_value
2185 (p_transaction_step_id => p_transaction_step_id
2186 ,p_name => 'P_ATTRIBUTE6')
2187 ,p_attribute7 => hr_transaction_api.get_varchar2_value
2188 (p_transaction_step_id => p_transaction_step_id
2189 ,p_name => 'P_ATTRIBUTE7')
2190 ,p_attribute8 => hr_transaction_api.get_varchar2_value
2191 (p_transaction_step_id => p_transaction_step_id
2192 ,p_name => 'P_ATTRIBUTE8')
2193 ,p_attribute9 => hr_transaction_api.get_varchar2_value
2194 (p_transaction_step_id => p_transaction_step_id
2195 ,p_name => 'P_ATTRIBUTE9')
2196 ,p_attribute10 => hr_transaction_api.get_varchar2_value
2197 (p_transaction_step_id => p_transaction_step_id
2198 ,p_name => 'P_ATTRIBUTE10')
2199 ,p_attribute11 => hr_transaction_api.get_varchar2_value
2200 (p_transaction_step_id => p_transaction_step_id
2201 ,p_name => 'P_ATTRIBUTE11')
2202 ,p_attribute12 => hr_transaction_api.get_varchar2_value
2203 (p_transaction_step_id => p_transaction_step_id
2204 ,p_name => 'P_ATTRIBUTE12')
2205 ,p_attribute13 => hr_transaction_api.get_varchar2_value
2206 (p_transaction_step_id => p_transaction_step_id
2207 ,p_name => 'P_ATTRIBUTE13')
2208 ,p_attribute14 => hr_transaction_api.get_varchar2_value
2209 (p_transaction_step_id => p_transaction_step_id
2210 ,p_name => 'P_ATTRIBUTE14')
2211 ,p_attribute15 => hr_transaction_api.get_varchar2_value
2212 (p_transaction_step_id => p_transaction_step_id
2213 ,p_name => 'P_ATTRIBUTE15')
2214 ,p_attribute16 => hr_transaction_api.get_varchar2_value
2215 (p_transaction_step_id => p_transaction_step_id
2216 ,p_name => 'P_ATTRIBUTE16')
2217 ,p_attribute17 => hr_transaction_api.get_varchar2_value
2218 (p_transaction_step_id => p_transaction_step_id
2219 ,p_name => 'P_ATTRIBUTE17')
2220 ,p_attribute18 => hr_transaction_api.get_varchar2_value
2221 (p_transaction_step_id => p_transaction_step_id
2222 ,p_name => 'P_ATTRIBUTE18')
2223 ,p_attribute19 => hr_transaction_api.get_varchar2_value
2224 (p_transaction_step_id => p_transaction_step_id
2225 ,p_name => 'P_ATTRIBUTE19')
2226 ,p_attribute20 => hr_transaction_api.get_varchar2_value
2227 (p_transaction_step_id => p_transaction_step_id
2228 ,p_name => 'P_ATTRIBUTE20')
2229 ,p_attribute21 => hr_transaction_api.get_varchar2_value
2230 (p_transaction_step_id => p_transaction_step_id
2231 ,p_name => 'P_ATTRIBUTE21')
2232 ,p_attribute22 => hr_transaction_api.get_varchar2_value
2233 (p_transaction_step_id => p_transaction_step_id
2234 ,p_name => 'P_ATTRIBUTE22')
2235 ,p_attribute23 => hr_transaction_api.get_varchar2_value
2236 (p_transaction_step_id => p_transaction_step_id
2237 ,p_name => 'P_ATTRIBUTE23')
2238 ,p_attribute24 => hr_transaction_api.get_varchar2_value
2239 (p_transaction_step_id => p_transaction_step_id
2240 ,p_name => 'P_ATTRIBUTE24')
2241 ,p_attribute25 => hr_transaction_api.get_varchar2_value
2242 (p_transaction_step_id => p_transaction_step_id
2243 ,p_name => 'P_ATTRIBUTE25')
2244 ,p_attribute26 => hr_transaction_api.get_varchar2_value
2245 (p_transaction_step_id => p_transaction_step_id
2246 ,p_name => 'P_ATTRIBUTE26')
2247 ,p_attribute27 => hr_transaction_api.get_varchar2_value
2248 (p_transaction_step_id => p_transaction_step_id
2249 ,p_name => 'P_ATTRIBUTE27')
2250 ,p_attribute28 => hr_transaction_api.get_varchar2_value
2251 (p_transaction_step_id => p_transaction_step_id
2252 ,p_name => 'P_ATTRIBUTE28')
2253 ,p_attribute29 => hr_transaction_api.get_varchar2_value
2254 (p_transaction_step_id => p_transaction_step_id
2255 ,p_name => 'P_ATTRIBUTE29')
2256 ,p_attribute30 => hr_transaction_api.get_varchar2_value
2257 (p_transaction_step_id => p_transaction_step_id
2258 ,p_name => 'P_ATTRIBUTE30'));
2259 END IF; -- If it's a new of existing phone number ?
2260
2261 IF p_validate = TRUE THEN
2262 hr_utility.set_location('IF p_validate = TRUE THEN:'||l_proc,40);
2263 ROLLBACK TO process_phones;
2264 END IF;
2265 hr_utility.set_location('Exiting:'||l_proc,45);
2266
2267 EXCEPTION
2268 WHEN hr_utility.hr_error THEN
2269 -- ---------------------------------------------------
2270 -- ---------------------------------------------------
2271 -- an application error has been raised so we must
2272 -- redisplay the web form to display the error
2273 -- ----------------------------------------------------
2274 hr_utility.set_location('Exception:hr_utility.hr_error'||l_proc,555);
2275 RAISE;
2276 WHEN OTHERS THEN
2277 hr_utility.set_location('Exception:Others'||l_proc,560);
2278 RAISE;
2279 /*
2280 hr_util_disp_web.display_fatal_errors
2281 (p_message => UPPER(g_package || '.process_api: '
2282 || SQLERRM));
2283 */
2284 END process_api;
2285 end hr_process_phone_numbers_ss;