[Home] [Help]
PACKAGE BODY: APPS.HR_PROCESS_ADDRESS_SS
Source
1 PACKAGE BODY hr_process_address_ss AS
2 /* $Header: hraddwrs.pkb 120.3.12010000.2 2008/08/06 08:31:13 ubhat ship $*/
3
4 -- Package scope global variables.
5 l_transaction_table hr_transaction_ss.transaction_table;
6 l_count INTEGER := 0;
7 l_praddr_ovrlap VARCHAR2(2);
8 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
9 l_trs_object_version_number hr_api_transaction_steps.object_version_number%type;
10 g_package varchar2(31) := 'HR_PROCESS_ADDRESS_SS';
11 g_data_error exception;
12 l_message_number VARCHAR2(10);
13 p_trans_rec_count integer;
14
15
16 /*
17 ||===========================================================================
18 || FUNCTION: is_a_personal_info_flow
19 ||---------------------------------------------------------------------------
20 ||
21 || Description:
22 || This function will will check if the current flow is a personal information flow or not.
23 ||
24 || Access Status:
25 || Private
26 ||
27 ||===========================================================================
28 */
29
30 function is_a_personal_info_flow (
31 p_person_id in number
32 , p_effective_date in date )
33 return boolean
34 is
35
36 CURSOR c_get_current_applicant_flag
37 (p_person_id in number
38 ,p_eff_date in date default trunc(sysdate))
39 IS
40 SELECT per.current_applicant_flag,
41 per.current_employee_flag,
42 per.current_npw_flag
43 FROM per_all_people_f per
44 WHERE per.person_id = p_person_id
45 AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
46
47 l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
48 l_current_employee_flag per_all_people_f.current_employee_flag%type;
49 l_current_npw_flag per_all_people_f.current_npw_flag%type;
50 l_applicant_hire boolean := false ;
51 p_result boolean ;
52 begin
53
54 if (p_person_id is null or p_person_id < 0 ) then
55 return false;
56 end if;
57
58 open c_get_current_applicant_flag(p_person_id, p_effective_date);
59 fetch c_get_current_applicant_flag into l_current_applicant_flag, l_current_employee_flag, l_current_npw_flag;
60 close c_get_current_applicant_flag;
61
62 -- for applicant hire ---> g_applicant_hire = TRUE
63 if (l_current_applicant_flag = 'Y' AND
64 nvl(l_current_employee_flag, 'N') <> 'Y' AND
65 nvl(l_current_npw_flag, 'N') <> 'Y') then
66 l_applicant_hire := true;
67 end if;
68
69 if l_applicant_hire = false then
70 p_result := true;
71 else
72 p_result := false;
73 end if;
74 return p_result;
75 end is_a_personal_info_flow;
76
77
78 /*
79 ||===========================================================================
80 || PROCEDURE: create_person_address
81 ||---------------------------------------------------------------------------
82 ||
83 || Description:
84 || This procedure will call the actual API -
85 || hr_person_address_api.create_person_address()
86 ||
87 || Access Status:
88 || Public.
89 ||
90 ||===========================================================================
91 */
92 PROCEDURE create_person_address
93 (p_validate in number default 0
94 ,p_effective_date in date
95 ,p_pradd_ovlapval_override in number default 0
96 ,p_validate_county in number default 1
97 ,p_person_id in number
98 --
99 -- PB Add :
100 -- The transaction steps have to be created by the login personid.
101 -- In case of adding address for contacts person_id is contact_person_id.
102 -- Login person id is say employee who is adding the address to his contact.
103 --
104 ,p_login_person_id in number default null
105 ,p_business_group_id in number default null
106 --
107 ,p_primary_flag in varchar2
108 ,p_style in varchar2
109 ,p_date_from in date
110 ,p_date_to in date default null
111 ,p_address_type in varchar2 default hr_api.g_varchar2
112 ,p_address_type_meaning in varchar2 default hr_api.g_varchar2
113 ,p_comments in long default hr_api.g_varchar2
114 ,p_address_line1 in varchar2 default hr_api.g_varchar2
115 ,p_address_line2 in varchar2 default hr_api.g_varchar2
116 ,p_address_line3 in varchar2 default hr_api.g_varchar2
117 ,p_town_or_city in varchar2 default hr_api.g_varchar2
118 ,p_region_1 in varchar2 default hr_api.g_varchar2
119 ,p_region_2 in varchar2 default hr_api.g_varchar2
120 ,p_region_3 in varchar2 default hr_api.g_varchar2
121 ,p_postal_code in varchar2 default hr_api.g_varchar2
122 ,p_country in varchar2 default hr_api.g_varchar2
123 ,p_country_meaning in varchar2 default hr_api.g_varchar2
124 ,p_telephone_number_1 in varchar2 default hr_api.g_varchar2
125 ,p_telephone_number_2 in varchar2 default hr_api.g_varchar2
126 ,p_telephone_number_3 in varchar2 default hr_api.g_varchar2
127 ,p_addr_attribute_category in varchar2 default hr_api.g_varchar2
128 ,p_addr_attribute1 in varchar2 default hr_api.g_varchar2
129 ,p_addr_attribute2 in varchar2 default hr_api.g_varchar2
130 ,p_addr_attribute3 in varchar2 default hr_api.g_varchar2
131 ,p_addr_attribute4 in varchar2 default hr_api.g_varchar2
132 ,p_addr_attribute5 in varchar2 default hr_api.g_varchar2
133 ,p_addr_attribute6 in varchar2 default hr_api.g_varchar2
134 ,p_addr_attribute7 in varchar2 default hr_api.g_varchar2
135 ,p_addr_attribute8 in varchar2 default hr_api.g_varchar2
136 ,p_addr_attribute9 in varchar2 default hr_api.g_varchar2
137 ,p_addr_attribute10 in varchar2 default hr_api.g_varchar2
138 ,p_addr_attribute11 in varchar2 default hr_api.g_varchar2
139 ,p_addr_attribute12 in varchar2 default hr_api.g_varchar2
140 ,p_addr_attribute13 in varchar2 default hr_api.g_varchar2
141 ,p_addr_attribute14 in varchar2 default hr_api.g_varchar2
142 ,p_addr_attribute15 in varchar2 default hr_api.g_varchar2
143 ,p_addr_attribute16 in varchar2 default hr_api.g_varchar2
144 ,p_addr_attribute17 in varchar2 default hr_api.g_varchar2
145 ,p_addr_attribute18 in varchar2 default hr_api.g_varchar2
146 ,p_addr_attribute19 in varchar2 default hr_api.g_varchar2
147 ,p_addr_attribute20 in varchar2 default hr_api.g_varchar2
148 ,p_add_information13 in varchar2 default hr_api.g_varchar2
149 ,p_add_information14 in varchar2 default hr_api.g_varchar2
150 ,p_add_information15 in varchar2 default hr_api.g_varchar2
151 ,p_add_information16 in varchar2 default hr_api.g_varchar2
152 ,p_add_information17 in varchar2 default hr_api.g_varchar2
153 ,p_add_information18 in varchar2 default hr_api.g_varchar2
154 ,p_add_information19 in varchar2 default hr_api.g_varchar2
155 ,p_add_information20 in varchar2 default hr_api.g_varchar2
156 ,p_address_id out nocopy number
157 ,p_object_version_number out nocopy number
158 -- StartRegistration
159 ,p_contact_or_person in varchar2 default null
160 -- EndRegistration
161 ,p_item_type in varchar2
162 ,p_item_key in varchar2
163 ,p_activity_id in number
164 ,p_action in varchar2
165 ,p_old_address_id in number default null
166 ,p_old_object_version_number in number default null
167 ,p_save_mode in varchar2 default null
168 ,p_error_message out nocopy long
169 , p_contact_relationship_id in number default hr_api.g_number
170 )
171 IS
172
173 l_proc varchar2(200) := g_package || 'create_person_address';
174 l_review_item_name varchar2(50);
175 l_date_to date;
176 l_date_from date;
177 l_transaction_id number default null;
178 l_result varchar2(100) default null;
179 -- PB : Added the variables
180 l_person_id number;
181 l_dummy_num number;
182 l_dummy_date date;
183 l_dummy_char varchar2(1000);
184 l_dummy_bool boolean;
185
186 -- StartRegistration
187
188 l_primary_flag varchar2(10);
189 -- StartRegistration
190 l_reg_per_ovn number default null;
191 l_reg_employee_number number default null;
192 l_reg_asg_ovn number default null;
193 l_reg_full_name per_all_people_f.full_name%type default null;
194 l_reg_assignment_id number;
195 l_reg_per_effective_start_date date;
196 l_reg_per_effective_end_date date;
197 l_reg_per_comment_id number;
198 l_reg_assignment_sequence number;
199 l_reg_assignment_number varchar2(50);
200 l_reg_name_combination_warning boolean;
201 l_reg_assign_payroll_warning boolean;
202 l_reg_orig_hire_warning boolean;
203 --Startregistration gsheelum
204 l_contact_set number;
205 -- EndRegistration
206 l_api_name varchar2(100);
207 l_validate_g_per_con_step_id number;
208 l_old_ovn number;
209
210 BEGIN
211 hr_utility.set_location(' Entering:' || l_proc,5);
212
213 --bug 5375749
214 --If user enter effective date for which is less than person joining date then error should come.
215 declare
216 error_flag boolean := false;
217 result boolean := false;
218 begin
219 result := is_a_personal_info_flow (p_person_id, p_effective_date);
220 error_flag := hr_perinfo_util_web.isDateLessThanCreationDate(p_effective_date,p_person_id);
221 if result = true then
222 hr_utility.trace('ORCL : inside the result true ');
223 end if;
224 if error_flag = true then
225 hr_utility.trace('ORCL : inside the error_flag true');
226 end if;
227 if error_flag= true and result= true then
228 fnd_message.set_name('PER', 'HR_PERINFO_INVALID_EFFEC_DATE');
229 fnd_message.raise_error;
230 end if;
231 end ;
232 --bug 5375749
233
234 -- Call the actual API.
235 --
236 -- PB : In case of adding a contact and address to contact then person_id
237 -- is null or less than 0, so to validate the address data, contact
238 -- have to be created.
239 --
240 l_person_id := p_person_id;
241 --
242 -- StartRegistration
243 l_primary_flag :=p_primary_flag;
244 -- EndRegistration
245 --
246 --startregistration anupam
247 -- If coming from overview page and creating a third address then the
248 -- value of primary flag could be "T". This is required to be stored in
249 -- transaction tables as "T" but the api validation will be as "N" only and
250 -- finally it will go in database as N in process_api
251 if p_primary_flag = 'T' then
252 hr_utility.set_location(l_proc,10);
253 l_primary_flag := 'N';
254 end if;
255 -- endregistration anupam
256
257 IF (p_save_mode = 'SAVE_FOR_LATER') THEN
258 hr_utility.set_location(l_proc,15);
259 GOTO only_transaction;
260 END IF;
261
262 savepoint create_address;
263 --
264 if (l_person_id is null or l_person_id < 0 ) then
265 hr_utility.set_location(l_proc,20);
266 --
267 -- Now create a dummy contact to the login person and
268 -- use the out contact person id to validate the phone.
269 --
270 l_primary_flag := 'Y';
271 --
272 -- bug # 2174876
273 if p_contact_or_person = 'CONTACT' or p_contact_or_person = 'EMER_CR_NEW_CONT' or p_contact_or_person = 'EMRG_OVRW_UPD' or p_contact_or_person = 'EMRG_OVRW_DEL' or p_contact_or_person = 'EMER_CR_NEW_REL'
274 or p_contact_or_person = 'DPDNT_CR_NEW_CONT' or p_contact_or_person = 'DPDNT_OVRW_UPD' or p_contact_or_person = 'DPDNT_OVRW_DEL' or p_contact_or_person = 'DPDNT_CR_NEW_REL'
275 or p_contact_or_person = 'COBRA' then
276 declare
277 l_object_version_number number;
278 l_effective_start_date date;
279 l_effective_end_date date;
280 l_full_name varchar2(255);
281 l_comment_id number;
282 l_name_combination_warning boolean;
283 l_orig_hire_warning boolean;
284 begin
285 hr_utility.set_location(l_proc,25);
286 --ignore the dff validations for Dummy Person created
287 hr_person_info_util_ss.create_ignore_df_validation('PER_PEOPLE');
288 hr_person_info_util_ss.create_ignore_df_validation('Person Developer DF');
289 hr_contact_api.create_person
290 (p_start_date => p_effective_date,
291 p_business_group_id =>p_business_group_id,
292 p_last_name => 'RegistrationDummy',
293 p_first_name => 'Dummy',
294 p_sex => 'M',
295 -- p_coord_ben_no_cvg_flag => 'Y',
296 p_person_id => l_person_id,
297 p_object_version_number => l_object_version_number,
298 p_effective_start_date => l_effective_start_date,
299 p_effective_end_date => l_effective_end_date,
300 p_full_name => l_full_name,
301 p_comment_id => l_comment_id,
302 p_name_combination_warning => l_name_combination_warning,
303 p_orig_hire_warning => l_orig_hire_warning);
304 hr_person_info_util_ss.remove_ignore_df_validation;
305 end;
306 end if;
307 -- end bug # 2174876
308 --end bug # 2138073/2115552
309 if p_contact_or_person = 'PERSON' then
310 hr_utility.set_location(l_proc,30);
311 -- we need to call only BD step to create the dummy person
312 -- and nothing else, so use process_selected_transaction
313 hr_new_user_reg_ss.process_selected_transaction(
314 p_item_type => p_item_type,
315 p_item_key => p_item_key,
316 p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
317 l_person_id := to_char(hr_process_person_ss.g_person_id);
318 end if;
319 end if;
320 --
321 -- StartRegistration : Changed the primary flag to validate the secondary
322 -- address. In case of new person, there is no primary address so while
323 -- validating the secondary address create_address errors out, so always
324 -- validate as primary address.
325
326 -- First end date the Secondary Address, before creating a new one
327 -- and do it in validate false mode, as we have a rollback down.
328
329 IF UPPER(p_action) = 'CHANGE' THEN
330 IF UPPER(l_primary_flag) like 'N%' THEN
331 hr_utility.set_location(l_proc,35);
332 l_old_ovn := p_old_object_version_number;
333 hr_person_address_api.update_person_address
334 (p_validate => false
335 ,p_effective_date => p_effective_date
336 ,p_address_id => p_old_address_id
337 ,p_object_version_number => l_old_ovn
338 ,p_date_to => p_date_to);
339 END IF;
340 END IF;
341
342 hr_person_address_api.create_person_address
343 (p_validate => hr_java_conv_util_ss.get_boolean (
344 p_number => p_validate
345 )
346 ,p_effective_date => p_effective_date
347 ,p_pradd_ovlapval_override => hr_java_conv_util_ss.get_boolean (
348 p_number => p_pradd_ovlapval_override
349 )
350 ,p_validate_county => hr_java_conv_util_ss.get_boolean (
351 p_number => p_validate_county
352 )
353 ,p_person_id => l_person_id -- PB : Modify p_person_id
354 ,p_primary_flag => l_primary_flag -- StartRegistration
355 ,p_style => p_style
356 ,p_date_from => p_effective_date
357 ,p_address_type => p_address_type
358 ,p_comments => p_comments
359 ,p_address_line1 => p_address_line1
360 ,p_address_line2 => p_address_line2
361 ,p_address_line3 => p_address_line3
362 ,p_town_or_city => p_town_or_city
363 ,p_region_1 => p_region_1
364 ,p_region_2 => p_region_2
365 ,p_region_3 => p_region_3
366 ,p_postal_code => p_postal_code
367 ,p_country => p_country
368 ,p_telephone_number_1 => p_telephone_number_1
369 ,p_telephone_number_2 => p_telephone_number_2
370 ,p_telephone_number_3 => p_telephone_number_3
371 ,p_addr_attribute_category => p_addr_attribute_category
372 ,p_addr_attribute1 => p_addr_attribute1
373 ,p_addr_attribute2 => p_addr_attribute2
374 ,p_addr_attribute3 => p_addr_attribute3
375 ,p_addr_attribute4 => p_addr_attribute4
376 ,p_addr_attribute5 => p_addr_attribute5
377 ,p_addr_attribute6 => p_addr_attribute6
378 ,p_addr_attribute7 => p_addr_attribute7
379 ,p_addr_attribute8 => p_addr_attribute8
380 ,p_addr_attribute9 => p_addr_attribute9
381 ,p_addr_attribute10 => p_addr_attribute10
382 ,p_addr_attribute11 => p_addr_attribute11
383 ,p_addr_attribute12 => p_addr_attribute12
384 ,p_addr_attribute13 => p_addr_attribute13
385 ,p_addr_attribute14 => p_addr_attribute14
386 ,p_addr_attribute15 => p_addr_attribute15
387 ,p_addr_attribute16 => p_addr_attribute16
388 ,p_addr_attribute17 => p_addr_attribute17
389 ,p_addr_attribute18 => p_addr_attribute18
390 ,p_addr_attribute19 => p_addr_attribute19
391 ,p_addr_attribute20 => p_addr_attribute20
392 ,p_add_information13 => p_add_information13
393 ,p_add_information14 => p_add_information14
394 ,p_add_information15 => p_add_information15
395 ,p_add_information16 => p_add_information16
396 ,p_add_information17 => p_add_information17
397 ,p_add_information18 => p_add_information18
398 ,p_add_information19 => p_add_information19
399 ,p_add_information20 => p_add_information20
400 --
401 -- PB : These out variables should not be written to transaction tables.
402 --
403 ,p_address_id => l_dummy_num -- PB : p_address_id
404 ,p_object_version_number => l_dummy_num -- PB : p_object_version_number
405 );
406 -- PB : Now rollback all the changes which are performed.
407 --
408
409 ROLLBACK to create_address;
410 --
411 -- -----------------------------------------------------------------------------
412 -- We will write the data to transaction tables.
413 -- Determine if a transaction step exists for this activity
414 -- if a transaction step does exist then the transaction_step_id and
415 -- object_version_number are set (i.e. not null).
416 -- -----------------------------------------------------------------------------
417
418 <<only_transaction>> -- label for GOTO
419
420 --
421 -- Setting the following two helps to see the actual effective
422 -- date of the transaction in All Actions Awaiting your attention
423 -- table.
424 -- Say for address user enters new address with an effective date
425 -- this date helps to show as said above and also makes sure
426 -- when SFL'd and started again uses this date.
427 -- If not set, WF on starting sets this dates to sysdate and it
428 -- shows that and also on retrieving SFL it performs as of sysdate
429 -- rather than the actual date of the transaction.
430 --
431 -- We don't support this for contacts as it is always by sysdate
432 -- if we end of setting it for contacts it even affects the start
433 -- date of the dependents created in registration flow see
434 -- bug 3784728 for more details
435 --
436
437 IF NOT (p_contact_or_person = 'CONTACT' or p_contact_or_person = 'EMER_CR_NEW_CONT' or p_contact_or_person = 'EMRG_OVRW_UPD' or p_contact_or_person = 'EMRG_OVRW_DEL' or p_contact_or_person = 'EMER_CR_NEW_REL'
438 or p_contact_or_person = 'DPDNT_CR_NEW_CONT' or p_contact_or_person = 'DPDNT_OVRW_UPD' or p_contact_or_person = 'DPDNT_OVRW_DEL' or p_contact_or_person = 'DPDNT_CR_NEW_REL') THEN
439
440 hr_utility.set_location(l_proc,40);
441 wf_engine.setItemAttrText (itemtype => p_item_type,
442 itemkey => p_item_key,
443 aname => 'P_EFFECTIVE_DATE',
444 avalue => to_char(p_effective_date,
445 g_date_format));
446
447 wf_engine.setItemAttrDate (itemtype => p_item_type,
448 itemkey => p_item_key,
449 aname => 'CURRENT_EFFECTIVE_DATE',
450 avalue => p_effective_date);
451 END IF;
452 --
453 -- First, check if transaction id exists or not
454 --
455 l_transaction_id := hr_transaction_ss.get_transaction_id
456 (p_item_type => p_item_type
457 ,p_item_key => p_item_key);
458 --
459 IF l_transaction_id is null THEN
460 hr_utility.set_location(l_proc,45);
461
462 -- Start a Transaction
463
464 hr_transaction_ss.start_transaction
465 (itemtype => p_item_type
466 ,itemkey => p_item_key
467 ,actid => p_activity_id
468 ,funmode => 'RUN'
469 ,p_api_addtnl_info => p_contact_or_person
470 ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB : Modification
471 ,result => l_result);
472
473 l_transaction_id := hr_transaction_ss.get_transaction_id
474 (p_item_type => p_item_type
475 ,p_item_key => p_item_key);
476 END IF;
477 --
478 -- Create a transaction step
479 --
480 hr_transaction_api.create_transaction_step
481 (p_validate => false
482 ,p_creator_person_id => nvl(p_login_person_id, p_person_id) -- PB : Modification
483 ,p_transaction_id => l_transaction_id
484 ,p_api_name => g_package || '.PROCESS_API'
485 ,p_item_type => p_item_type
486 ,p_item_key => p_item_key
487 ,p_activity_id => p_activity_id
488 ,p_transaction_step_id => l_transaction_step_id
489 ,p_object_version_number => l_trs_object_version_number);
490 --
491
492
493 l_count := 1;
494 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
495 l_transaction_table(l_count).param_value := p_person_id;
496 l_transaction_table(l_count).param_data_type := 'NUMBER';
497
498 -- IF UPPER(p_action) = 'DELETE' THEN
499 hr_utility.set_location(l_proc,50);
500 -- l_effective_date := to_char(trunc(sysdate),l_user_date_format);
501 -- ELSE
502 hr_utility.set_location(l_proc,55);
503 -- l_effective_date := p_effective_date;
504 -- END IF;
505
506 l_count := l_count + 1;
507 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
508 l_transaction_table(l_count).param_value := to_char(p_effective_date,
509 hr_transaction_ss.g_date_format);
510 l_transaction_table(l_count).param_data_type := 'DATE';
511
512 -- l_count := l_count + 1;
513 -- l_transaction_table(l_count).param_name := 'P_USER_DATE_FORMAT';
514 -- l_transaction_table(l_count).param_value := l_user_date_format;
515 -- l_transaction_table(l_count).param_data_type := 'VARCHAR2';
516
517 l_count := l_count + 1;
518 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
519 l_transaction_table(l_count).param_value := p_object_version_number;
520 l_transaction_table(l_count).param_data_type := 'NUMBER';
521
522 -- old object version number is same as object version number for create address
523
524 l_count := l_count + 1;
525 l_transaction_table(l_count).param_name := 'P_OLD_OBJECT_VERSION_NUMBER';
526 l_transaction_table(l_count).param_value := p_old_object_version_number;
527 l_transaction_table(l_count).param_data_type := 'NUMBER';
528
529 l_count := l_count + 1;
530 l_transaction_table(l_count).param_name := 'P_ADDRESS_ID';
531 l_transaction_table(l_count).param_value := p_address_id;
532 l_transaction_table(l_count).param_data_type := 'NUMBER';
533
534 -- old address id is same as address id for create address
535
536 l_count := l_count + 1;
537 l_transaction_table(l_count).param_name := 'P_OLD_ADDRESS_ID';
538 l_transaction_table(l_count).param_value := p_old_address_id;
539 l_transaction_table(l_count).param_data_type := 'NUMBER';
540
541 l_count := l_count + 1;
542 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE1';
543 l_transaction_table(l_count).param_value := p_address_line1;
544 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
545
546 l_count := l_count + 1;
547 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE2';
548 l_transaction_table(l_count).param_value := p_address_line2;
549 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
550
551 l_count := l_count + 1;
552 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE3';
553 l_transaction_table(l_count).param_value := p_address_line3;
554 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
555
556 l_count := l_count + 1;
557 l_transaction_table(l_count).param_name := 'P_CITY';
558 l_transaction_table(l_count).param_value := p_town_or_city;
559 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
560
561 l_count := l_count + 1;
562 l_transaction_table(l_count).param_name := 'P_REGION1';
563 l_transaction_table(l_count).param_value := p_region_1;
564 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
565
566 -- l_count := l_count + 1;
567 -- l_transaction_table(l_count).param_name := 'P_STATE';
568 -- l_transaction_table(l_count).param_value := p_state;
569 -- l_transaction_table(l_count).param_data_type := 'VARCHAR2';
570
571 l_count := l_count + 1;
572 l_transaction_table(l_count).param_name := 'P_REGION2';
573 l_transaction_table(l_count).param_value := p_region_2;
574 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
575
576 l_count := l_count + 1;
577 l_transaction_table(l_count).param_name := 'P_REGION3';
578 l_transaction_table(l_count).param_value := p_region_3;
579 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
580
581 l_count := l_count + 1;
582 l_transaction_table(l_count).param_name := 'P_COUNTRY';
583 l_transaction_table(l_count).param_value := p_country_meaning;
584 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
585
586 l_count := l_count + 1;
587 l_transaction_table(l_count).param_name := 'P_COUNTRY_CODE';
588 l_transaction_table(l_count).param_value := p_country;
589 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
590
591 l_count := l_count + 1;
592 l_transaction_table(l_count).param_name := 'P_POSTAL_CODE';
593 l_transaction_table(l_count).param_value := p_postal_code;
594 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
595
596 l_count := l_count + 1;
597 l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE';
598 l_transaction_table(l_count).param_value := p_address_type_meaning;
599 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
600
601 l_count := l_count + 1;
602 l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE_CODE';
603 l_transaction_table(l_count).param_value := p_address_type;
604 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
605
606 l_count := l_count + 1;
607 l_transaction_table(l_count).param_name := 'P_PRADD_OVLAPVAL_OVERRIDE';
608 IF (p_pradd_ovlapval_override = 1) THEN
609 hr_utility.set_location(l_proc,60);
610 l_transaction_table(l_count).param_value := 'Y';
611 ELSIF (p_pradd_ovlapval_override = 0) THEN
612 hr_utility.set_location(l_proc,65);
613 l_transaction_table(l_count).param_value := 'N';
614 END IF;
615
616 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
617
618 l_count := l_count + 1;
619 l_transaction_table(l_count).param_name := 'P_DATE_FROM';
620 l_transaction_table(l_count).param_value := to_char(p_date_from,
621 hr_transaction_ss.g_date_format);
622 l_transaction_table(l_count).param_data_type := 'DATE';
623
624 l_count := l_count + 1;
625 l_transaction_table(l_count).param_name := 'P_DATE_TO';
626 l_transaction_table(l_count).param_value := to_char(p_date_to,
627 hr_transaction_ss.g_date_format);
628 l_transaction_table(l_count).param_data_type := 'DATE';
629
630 l_count := l_count + 1;
631 l_transaction_table(l_count).param_name := 'P_STYLE';
632 l_transaction_table(l_count).param_value := p_style;
633 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
634
635 l_count := l_count + 1;
636 l_transaction_table(l_count).param_name := 'P_PRIMARY_FLAG';
637 l_transaction_table(l_count).param_value := p_primary_flag;
638 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
639
640 l_count := l_count + 1;
641 l_transaction_table(l_count).param_name := 'P_ACTION';
642 l_transaction_table(l_count).param_value := UPPER(p_action);
643 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
644
645 l_count := l_count + 1;
646 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER1';
647 l_transaction_table(l_count).param_value := p_telephone_number_1;
648 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
649
650 l_count := l_count + 1;
651 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER2';
652 l_transaction_table(l_count).param_value := p_telephone_number_2;
653 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
654
655 l_count := l_count + 1;
656 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER3';
657 l_transaction_table(l_count).param_value := p_telephone_number_3;
658 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
659
660 -- Now add all the Descriptive flex fields into transactions tables
661
662 l_count := l_count + 1; -- CONTEXT
663 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE_CATEGORY';
664 l_transaction_table(l_count).param_value := p_addr_attribute_category;
665 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
666
667 l_count := l_count + 1;
668 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE1';
669 l_transaction_table(l_count).param_value := p_addr_attribute1;
670 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
671
672 l_count := l_count + 1;
673 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE2';
674 l_transaction_table(l_count).param_value := p_addr_attribute2;
675 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
676
677 l_count := l_count + 1;
678 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE3';
679 l_transaction_table(l_count).param_value := p_addr_attribute3;
680 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
681
682 l_count := l_count + 1;
683 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE4';
684 l_transaction_table(l_count).param_value := p_addr_attribute4;
685 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
686
687 l_count := l_count + 1;
688 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE5';
689 l_transaction_table(l_count).param_value := p_addr_attribute5;
690 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
691
692 l_count := l_count + 1;
693 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE6';
694 l_transaction_table(l_count).param_value := p_addr_attribute6;
695 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
696
697 l_count := l_count + 1;
698 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE7';
699 l_transaction_table(l_count).param_value := p_addr_attribute7;
700 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
701
702 l_count := l_count + 1;
703 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE8';
704 l_transaction_table(l_count).param_value := p_addr_attribute8;
705 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
706
707 l_count := l_count + 1;
708 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE9';
709 l_transaction_table(l_count).param_value := p_addr_attribute9;
710 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
711
712 l_count := l_count + 1;
713 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE10';
714 l_transaction_table(l_count).param_value := p_addr_attribute10;
715 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
716
717 l_count := l_count + 1;
718 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE11';
719 l_transaction_table(l_count).param_value := p_addr_attribute11;
720 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
721
722 l_count := l_count + 1;
723 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE12';
724 l_transaction_table(l_count).param_value := p_addr_attribute12;
725 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
726
727 l_count := l_count + 1;
728 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE13';
729 l_transaction_table(l_count).param_value := p_addr_attribute13;
730 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
731
732 l_count := l_count + 1;
733 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE14';
734 l_transaction_table(l_count).param_value := p_addr_attribute14;
735 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
736
737 l_count := l_count + 1;
738 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE15';
739 l_transaction_table(l_count).param_value := p_addr_attribute15;
740 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
741
742 l_count := l_count + 1;
743 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE16';
744 l_transaction_table(l_count).param_value := p_addr_attribute16;
745 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
746
747 l_count := l_count + 1;
748 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE17';
749 l_transaction_table(l_count).param_value := p_addr_attribute17;
750 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
751
752 l_count := l_count + 1;
753 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE18';
754 l_transaction_table(l_count).param_value := p_addr_attribute18;
755 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
756
757 l_count := l_count + 1;
758 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE19';
759 l_transaction_table(l_count).param_value := p_addr_attribute19;
760 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
761
762 l_count := l_count + 1;
763 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE20';
764 l_transaction_table(l_count).param_value := p_addr_attribute20;
765 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
766
767
768 l_count := l_count + 1;
769 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION13';
770 l_transaction_table(l_count).param_value := p_add_information13;
771 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
772
773
774 l_count := l_count + 1;
775 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION14';
776 l_transaction_table(l_count).param_value := p_add_information14;
777 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
778
779
780 l_count := l_count + 1;
781 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION15';
782 l_transaction_table(l_count).param_value := p_add_information15;
783 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
784
785
786 l_count := l_count + 1;
787 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION16';
788 l_transaction_table(l_count).param_value := p_add_information16;
789 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
790
791
792 l_count := l_count + 1;
793 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION17';
794 l_transaction_table(l_count).param_value := p_add_information17;
795 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
796
797 l_count := l_count + 1;
798 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION18';
799 l_transaction_table(l_count).param_value := p_add_information18;
800 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
801
802 l_count := l_count + 1;
803 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION19';
804 l_transaction_table(l_count).param_value := p_add_information19;
805 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
806
807 l_count := l_count + 1;
808 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION20';
809 l_transaction_table(l_count).param_value := p_add_information20;
810 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
811
812 l_count := l_count + 1;
813 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
814 l_transaction_table(l_count).param_value := p_activity_id;
815 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
816
817 BEGIN
818 l_review_item_name := wf_engine.GetActivityAttrText(itemtype => p_item_type,
819 itemkey => p_item_key,
820 actid => p_activity_id,
821 aname => gv_wf_review_region_item);
822 EXCEPTION
823 WHEN OTHERS THEN
824 hr_utility.set_location(l_proc || 'EXCEPTION' ,555);
825 l_review_item_name := 'HrMainAddressReview';
826 END;
827
828 l_count := l_count + 1;
829 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
830 l_transaction_table(l_count).param_value := l_review_item_name;
831 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
832
833 -- StartRegistration gsheelum
834 l_count := l_count + 1;
835 l_transaction_table(l_count).param_name := 'P_CONTACT_OR_PERSON';
836 l_transaction_table(l_count).param_value := P_CONTACT_OR_PERSON;
837 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
838 -- EndRegistration
839 --
840 -- This is a marker for the contact person to be used to identify the Address
841 -- to be retrieved for the contact person in context in review page.
842 -- The HR_LAST_CONTACT_SET is in from the work flow attribute
843 begin
844 hr_utility.set_location(l_proc,70);
845 l_contact_set := wf_engine.GetItemAttrNumber(itemtype => p_item_type,
846 itemkey => p_item_key,
847 aname => 'HR_CONTACT_SET');
848
849 exception when others then
850 hr_utility.set_location(l_proc || 'EXCEPTION' ,560 );
851 l_contact_set := 0;
852
853 end;
854
855 l_count := l_count + 1;
856 l_transaction_table(l_count).param_name := 'P_CONTACT_SET';
857 l_transaction_table(l_count).param_value := l_contact_set;
858 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
859
860 if (p_contact_relationship_id > 0) then
861 hr_utility.set_location(l_proc,75);
862 l_count := l_count + 1;
863 l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
864 l_transaction_table(l_count).param_value := p_contact_relationship_id;
865 l_transaction_table(l_count).param_data_type := 'NUMBER';
866 end if;
867 --EndRegistration gsheelum
868 hr_transaction_ss.save_transaction_step
869 (p_item_type => p_item_type
870 ,p_item_key => p_item_key
871 ,p_actid => p_activity_id
872 ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB Modification
873 ,p_transaction_step_id => l_transaction_step_id
874 ,p_api_name => g_package || '.PROCESS_API'
875 ,p_transaction_data => l_transaction_table);
876
877
878 hr_utility.set_location(' Leaving:' || l_proc,80);
879
880
881 EXCEPTION
882 WHEN hr_utility.hr_error THEN
883 hr_utility.set_location(' Leaving:' || l_proc,565);
884
885 -- -------------------------------------------
886 -- an application error has been raised so we must
887 -- redisplay the web form to display the error
888 -- --------------------------------------------
889 hr_message.provide_error;
890 l_message_number := hr_message.last_message_number;
891 IF l_message_number = 'APP-7165' OR
892 l_message_number = 'APP-7155' THEN
893 --populate the p_error_message out variable
894 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
895 p_error_message => p_error_message,
896 p_attr_name => 'Page',
897 p_app_short_name => 'PER',
898 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
899 ELSIF l_message_number = 'APP-51139' THEN
900 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
901 p_error_message => p_error_message,
902 p_attr_name => 'AddressType',
903 p_app_short_name => 'PER',
904 p_message_name => 'HR_PERINFO_INVALID_ADDR_TYPE');
905 ELSIF l_message_number = 'APP-7952' OR
906 l_message_number = 'APP-7953' OR
907 l_message_number = 'APP-51276' OR
908 l_message_number = 'APP-51282' THEN
909 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
910 p_error_message => p_error_message,
911 p_app_short_name => 'PER',
912 p_message_name => 'HR_INVALID_CITYSTATEZIPCOUNTY');
913 ELSE
914 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
915 p_error_message => p_error_message);
916 END IF;
917 WHEN OTHERS THEN
918 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
919 p_error_message => p_error_message);
920 END create_person_address;
921
922 /*
923 ||===========================================================================
924 || PROCEDURE: update_person_address
925 ||---------------------------------------------------------------------------
926 ||
927 || Description:
928 || This procedure will call the actual API -
929 || hr_person_address_api.update_person_address()
930 ||
931 || Access Status:
932 || Public.
933 ||
934 ||===========================================================================
935 */
936
937 PROCEDURE update_person_address
938 (p_validate in number default 0
939 ,p_effective_date in date
940 ,p_validate_county in number default 1
941 ,p_address_id in number
942 ,p_object_version_number in out nocopy number
943 ,p_date_from in date default hr_api.g_date
944 ,p_date_to in date default hr_api.g_date
945 ,p_address_type in varchar2 default hr_api.g_varchar2
946 ,p_address_type_meaning in varchar2 default hr_api.g_varchar2
947 ,p_comments in long default hr_api.g_varchar2
948 ,p_address_line1 in varchar2 default hr_api.g_varchar2
949 ,p_address_line2 in varchar2 default hr_api.g_varchar2
950 ,p_address_line3 in varchar2 default hr_api.g_varchar2
951 ,p_town_or_city in varchar2 default hr_api.g_varchar2
952 ,p_region_1 in varchar2 default hr_api.g_varchar2
953 ,p_region_2 in varchar2 default hr_api.g_varchar2
954 ,p_region_3 in varchar2 default hr_api.g_varchar2
955 ,p_postal_code in varchar2 default hr_api.g_varchar2
956 ,p_country in varchar2 default hr_api.g_varchar2
957 ,p_country_meaning in varchar2 default hr_api.g_varchar2
958 ,p_telephone_number_1 in varchar2 default hr_api.g_varchar2
959 ,p_telephone_number_2 in varchar2 default hr_api.g_varchar2
960 ,p_telephone_number_3 in varchar2 default hr_api.g_varchar2
961 ,p_addr_attribute_category in varchar2 default hr_api.g_varchar2
962 ,p_addr_attribute1 in varchar2 default hr_api.g_varchar2
963 ,p_addr_attribute2 in varchar2 default hr_api.g_varchar2
964 ,p_addr_attribute3 in varchar2 default hr_api.g_varchar2
965 ,p_addr_attribute4 in varchar2 default hr_api.g_varchar2
966 ,p_addr_attribute5 in varchar2 default hr_api.g_varchar2
967 ,p_addr_attribute6 in varchar2 default hr_api.g_varchar2
968 ,p_addr_attribute7 in varchar2 default hr_api.g_varchar2
969 ,p_addr_attribute8 in varchar2 default hr_api.g_varchar2
970 ,p_addr_attribute9 in varchar2 default hr_api.g_varchar2
971 ,p_addr_attribute10 in varchar2 default hr_api.g_varchar2
972 ,p_addr_attribute11 in varchar2 default hr_api.g_varchar2
973 ,p_addr_attribute12 in varchar2 default hr_api.g_varchar2
974 ,p_addr_attribute13 in varchar2 default hr_api.g_varchar2
975 ,p_addr_attribute14 in varchar2 default hr_api.g_varchar2
976 ,p_addr_attribute15 in varchar2 default hr_api.g_varchar2
977 ,p_addr_attribute16 in varchar2 default hr_api.g_varchar2
978 ,p_addr_attribute17 in varchar2 default hr_api.g_varchar2
979 ,p_addr_attribute18 in varchar2 default hr_api.g_varchar2
980 ,p_addr_attribute19 in varchar2 default hr_api.g_varchar2
981 ,p_addr_attribute20 in varchar2 default hr_api.g_varchar2
982 ,p_add_information13 in varchar2 default hr_api.g_varchar2
983 ,p_add_information14 in varchar2 default hr_api.g_varchar2
984 ,p_add_information15 in varchar2 default hr_api.g_varchar2
985 ,p_add_information16 in varchar2 default hr_api.g_varchar2
986 ,p_add_information17 in varchar2 default hr_api.g_varchar2
987 ,p_add_information18 in varchar2 default hr_api.g_varchar2
988 ,p_add_information19 in varchar2 default hr_api.g_varchar2
989 ,p_add_information20 in varchar2 default hr_api.g_varchar2
990 ,p_item_type in varchar2
991 ,p_item_key in varchar2
992 ,p_activity_id in number
993 ,p_person_id in number
994 --
995 -- PB Add :
996 -- The transaction steps have to be created by the login personid.
997 -- In case of adding phones for contacts parent_is is contact_person_id.
998 -- Login person id is say employee who is adding the phones to his contact.
999 --
1000 --TEST
1001 ,p_contact_or_person in varchar2 default null
1002 ,p_login_person_id in number default null
1003 ,p_primary_flag in varchar2
1004 ,p_style in varchar2
1005 ,p_action in varchar2
1006 ,p_save_mode in varchar2 default null
1007 ,p_error_message out nocopy long
1008 , p_contact_relationship_id in number default hr_api.g_number
1009 )
1010 IS
1011
1012 l_proc varchar2(200) := g_package || 'update_person_address';
1013 l_old_ovn number;
1014 l_old_address_id per_addresses.address_id%TYPE;
1015 --startregistration anupam
1016 l_primary_flag per_addresses.primary_flag%TYPE;
1017 --endregistration anupam
1018 l_review_item_name varchar2(50);
1019 l_date_to date;
1020 l_date_from date;
1021 l_transaction_id number default null;
1022 l_result varchar2(100) default null;
1023
1024
1025 BEGIN
1026 hr_utility.set_location(' Entering:' || l_proc,5);
1027 -- save the the old address id and old object version number in temp variables
1028 l_old_ovn := p_object_version_number;
1029 l_old_address_id := p_address_id;
1030 --startregistration anupam
1031 -- If coming from overview page and updating the third address then the
1032 -- value of primary flag could be "T". This is required to be stored in
1033 -- transaction tables as "T" but the api validation will be as "N" only and
1034 -- finally it will go in database as N in process_api
1035 l_primary_flag := p_primary_flag;
1036 if p_primary_flag = 'T' then
1037 hr_utility.set_location( l_proc,10);
1038 l_primary_flag := 'N';
1039 end if;
1040 -- replacing the p_primary_flag with l_primary_flag in api_calls
1041 -- endregistration anupam
1042
1043 IF (p_save_mode = 'SAVE_FOR_LATER') THEN
1044 hr_utility.set_location( l_proc,15);
1045 GOTO only_transaction;
1046 END IF;
1047
1048
1049 -- Call the actual API.
1050 -- savepoint update_address;
1051 IF (UPPER(p_action) = 'DELETE') THEN
1052 hr_utility.set_location( l_proc,20);
1053 hr_person_address_api.update_person_address
1054 (p_validate => hr_java_conv_util_ss.get_boolean (
1055 p_number => p_validate
1056 )
1057 ,p_effective_date => p_effective_date
1058 ,p_validate_county => hr_java_conv_util_ss.get_boolean (
1059 p_number => p_validate_county
1060 )
1061 ,p_address_id => p_address_id
1062 ,p_object_version_number => p_object_version_number
1063 ,p_date_from => p_date_from
1064 ,p_date_to => p_date_to
1065 );
1066
1067 ELSE --Bug#3114508 start
1068 hr_utility.set_location( l_proc,25);
1069 hr_person_address_api.update_pers_addr_with_style --Bug#3114508 end
1070 (p_validate => hr_java_conv_util_ss.get_boolean (
1071 p_number => p_validate
1072 )
1073 ,p_effective_date => p_effective_date
1074 ,p_validate_county => hr_java_conv_util_ss.get_boolean (
1075 p_number => p_validate_county
1076 )
1077 ,p_address_id => p_address_id
1078 ,p_object_version_number => p_object_version_number
1079 ,p_date_from => p_date_from
1080 ,p_date_to => p_date_to
1081 ,p_address_type => p_address_type
1082 ,p_comments => p_comments
1083 ,p_address_line1 => p_address_line1
1084 ,p_address_line2 => p_address_line2
1085 ,p_address_line3 => p_address_line3
1086 ,p_town_or_city => p_town_or_city
1087 ,p_region_1 => p_region_1
1088 ,p_region_2 => p_region_2
1089 ,p_region_3 => p_region_3
1090 ,p_postal_code => p_postal_code
1091 ,p_country => p_country --Bug#3114508 start
1092 ,p_style => p_style --Bug#3114508 end
1093 ,p_telephone_number_1 => p_telephone_number_1
1094 ,p_telephone_number_2 => p_telephone_number_2
1095 ,p_telephone_number_3 => p_telephone_number_3
1096 ,p_addr_attribute_category => p_addr_attribute_category
1097 ,p_addr_attribute1 => p_addr_attribute1
1098 ,p_addr_attribute2 => p_addr_attribute2
1099 ,p_addr_attribute3 => p_addr_attribute3
1100 ,p_addr_attribute4 => p_addr_attribute4
1101 ,p_addr_attribute5 => p_addr_attribute5
1102 ,p_addr_attribute6 => p_addr_attribute6
1103 ,p_addr_attribute7 => p_addr_attribute7
1104 ,p_addr_attribute8 => p_addr_attribute8
1105 ,p_addr_attribute9 => p_addr_attribute9
1106 ,p_addr_attribute10 => p_addr_attribute10
1107 ,p_addr_attribute11 => p_addr_attribute11
1108 ,p_addr_attribute12 => p_addr_attribute12
1109 ,p_addr_attribute13 => p_addr_attribute13
1110 ,p_addr_attribute14 => p_addr_attribute14
1111 ,p_addr_attribute15 => p_addr_attribute15
1112 ,p_addr_attribute16 => p_addr_attribute16
1113 ,p_addr_attribute17 => p_addr_attribute17
1114 ,p_addr_attribute18 => p_addr_attribute18
1115 ,p_addr_attribute19 => p_addr_attribute19
1116 ,p_addr_attribute20 => p_addr_attribute20
1117 ,p_add_information13 => p_add_information13
1118 ,p_add_information14 => p_add_information14
1119 ,p_add_information15 => p_add_information15
1120 ,p_add_information16 => p_add_information16
1121 ,p_add_information17 => p_add_information17
1122 ,p_add_information18 => p_add_information18
1123 ,p_add_information19 => p_add_information19
1124 ,p_add_information20 => p_add_information20
1125 );
1126 END IF;
1127
1128 -- -----------------------------------------------------------------------------
1129 -- We will write the data to transaction tables.
1130 -- Determine if a transaction step exists for this activity
1131 -- if a transaction step does exist then the transaction_step_id and
1132 -- object_version_number are set (i.e. not null).
1133 -- -----------------------------------------------------------------------------
1134
1135 <<only_transaction>> -- label for GOTO
1136 hr_utility.set_location( l_proc,30);
1137 -- Set the P_EFFECTIVE_DATE and CURRENT_EFFECTIVE_DATE in wf item attributes to be retreived
1138 -- in review page
1139
1140 wf_engine.setItemAttrText (itemtype => p_item_type,
1141 itemkey => p_item_key,
1142 aname => 'P_EFFECTIVE_DATE',
1143 avalue => to_char(p_effective_date,
1144 g_date_format));
1145
1146 wf_engine.setItemAttrDate (itemtype => p_item_type,
1147 itemkey => p_item_key,
1148 aname => 'CURRENT_EFFECTIVE_DATE',
1149 avalue => p_effective_date);
1150
1151 -- First, check if transaction id exists or not
1152 l_transaction_id := hr_transaction_ss.get_transaction_id
1153 (p_item_type => p_item_type
1154 ,p_item_key => p_item_key);
1155 --
1156 IF l_transaction_id is null THEN
1157 hr_utility.set_location( l_proc,35);
1158 -- Start a Transaction
1159 hr_transaction_ss.start_transaction
1160 (itemtype => p_item_type
1161 ,itemkey => p_item_key
1162 ,actid => p_activity_id
1163 ,funmode => 'RUN'
1164 --TEST
1165 ,p_api_addtnl_info => p_contact_or_person
1166 -- PB : For creating address for a contact use
1167 -- use login person id.
1168 --
1169 ,p_login_person_id => nvl(p_login_person_id, p_person_id)
1170 ,result => l_result);
1171
1172 l_transaction_id := hr_transaction_ss.get_transaction_id
1173 (p_item_type => p_item_type
1174 ,p_item_key => p_item_key);
1175 END IF;
1176 --
1177 -- Create a transaction step
1178 --
1179 hr_transaction_api.create_transaction_step
1180 (p_validate => false
1181 -- PB : For creating address for a contact use
1182 -- use login person id.
1183 --
1184 ,p_creator_person_id => nvl(p_login_person_id, p_person_id) -- p_person_id
1185 ,p_transaction_id => l_transaction_id
1186 ,p_api_name => g_package || '.PROCESS_API'
1187 ,p_item_type => p_item_type
1188 ,p_item_key => p_item_key
1189 ,p_activity_id => p_activity_id
1190 ,p_transaction_step_id => l_transaction_step_id
1191 ,p_object_version_number => l_trs_object_version_number);
1192 --
1193
1194 l_count := 1;
1195 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
1196 l_transaction_table(l_count).param_value := p_person_id;
1197 l_transaction_table(l_count).param_data_type := 'NUMBER';
1198
1199 -- IF UPPER(p_action) = 'DELETE' THEN
1200 hr_utility.set_location( l_proc,40);
1201 -- l_effective_date := to_char(trunc(sysdate),l_user_date_format);
1202 -- ELSE
1203 hr_utility.set_location( l_proc,45);
1204 -- l_effective_date := p_effective_date;
1205 -- END IF;
1206
1207 l_count := l_count + 1;
1208 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
1209 l_transaction_table(l_count).param_value := to_char(p_effective_date,
1210 hr_transaction_ss.g_date_format);
1211 l_transaction_table(l_count).param_data_type := 'DATE';
1212
1213
1214 -- l_count := l_count + 1;
1215 -- l_transaction_table(l_count).param_name := 'P_USER_DATE_FORMAT';
1216 -- l_transaction_table(l_count).param_value := l_user_date_format;
1217 -- l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1218
1219 l_count := l_count + 1;
1220 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
1221 l_transaction_table(l_count).param_value := p_object_version_number;
1222 l_transaction_table(l_count).param_data_type := 'NUMBER';
1223
1224 l_count := l_count + 1;
1225 l_transaction_table(l_count).param_name
1226 := 'P_OLD_OBJECT_VERSION_NUMBER';
1227 l_transaction_table(l_count).param_value := l_old_ovn;
1228 l_transaction_table(l_count).param_data_type := 'NUMBER';
1229
1230 l_count := l_count + 1;
1231 l_transaction_table(l_count).param_name := 'P_ADDRESS_ID';
1232 l_transaction_table(l_count).param_value := p_address_id;
1233 l_transaction_table(l_count).param_data_type := 'NUMBER';
1234
1235 l_count := l_count + 1;
1236 l_transaction_table(l_count).param_name := 'P_OLD_ADDRESS_ID';
1237 l_transaction_table(l_count).param_value := l_old_address_id;
1238 l_transaction_table(l_count).param_data_type := 'NUMBER';
1239
1240 l_count := l_count + 1;
1241 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE1';
1242 l_transaction_table(l_count).param_value := p_address_line1;
1243 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1244
1245 l_count := l_count + 1;
1246 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE2';
1247 l_transaction_table(l_count).param_value := p_address_line2;
1248 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1249
1250 l_count := l_count + 1;
1251 l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE3';
1252 l_transaction_table(l_count).param_value := p_address_line3;
1253 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1254
1255 l_count := l_count + 1;
1256 l_transaction_table(l_count).param_name := 'P_CITY';
1257 l_transaction_table(l_count).param_value := p_town_or_city;
1258 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1259
1260 l_count := l_count + 1;
1261 l_transaction_table(l_count).param_name := 'P_REGION1';
1262 l_transaction_table(l_count).param_value := p_region_1;
1263 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1264
1265 -- l_count := l_count + 1;
1266 -- l_transaction_table(l_count).param_name := 'P_STATE';
1267 -- l_transaction_table(l_count).param_value := p_state;
1268 -- l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1269
1270 l_count := l_count + 1;
1271 l_transaction_table(l_count).param_name := 'P_REGION2';
1272 l_transaction_table(l_count).param_value := p_region_2;
1273 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1274
1275 l_count := l_count + 1;
1276 l_transaction_table(l_count).param_name := 'P_REGION3';
1277 l_transaction_table(l_count).param_value := p_region_3;
1278 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1279
1280 l_count := l_count + 1;
1281 l_transaction_table(l_count).param_name := 'P_COUNTRY';
1282 l_transaction_table(l_count).param_value := p_country_meaning;
1283 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1284
1285 l_count := l_count + 1;
1286 l_transaction_table(l_count).param_name := 'P_COUNTRY_CODE';
1287 l_transaction_table(l_count).param_value := p_country;
1288 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1289
1290 l_count := l_count + 1;
1291 l_transaction_table(l_count).param_name := 'P_POSTAL_CODE';
1292 l_transaction_table(l_count).param_value := p_postal_code;
1293 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1294
1295 l_count := l_count + 1;
1296 l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE';
1297 l_transaction_table(l_count).param_value := p_address_type_meaning;
1298 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1299
1300 l_count := l_count + 1;
1301 l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE_CODE';
1302 l_transaction_table(l_count).param_value := p_address_type;
1303 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1304
1305 l_count := l_count + 1;
1306 l_transaction_table(l_count).param_name := 'P_PRADD_OVLAPVAL_OVERRIDE';
1307 l_transaction_table(l_count).param_value := 'N';
1308 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1309
1310 l_count := l_count + 1;
1311 l_transaction_table(l_count).param_name := 'P_DATE_FROM';
1312 l_transaction_table(l_count).param_value := to_char(p_date_from,
1313 hr_transaction_ss.g_date_format);
1314 l_transaction_table(l_count).param_data_type := 'DATE';
1315
1316 l_count := l_count + 1;
1317 l_transaction_table(l_count).param_name := 'P_DATE_TO';
1318 l_transaction_table(l_count).param_value := to_char(p_date_to,
1319 hr_transaction_ss.g_date_format);
1320 l_transaction_table(l_count).param_data_type := 'DATE';
1321
1322 l_count := l_count + 1;
1323 l_transaction_table(l_count).param_name := 'P_STYLE';
1324 l_transaction_table(l_count).param_value := p_style;
1325 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1326
1327 l_count := l_count + 1;
1328 l_transaction_table(l_count).param_name := 'P_PRIMARY_FLAG';
1329 l_transaction_table(l_count).param_value := p_primary_flag;
1330 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1331
1332 l_count := l_count + 1;
1333 l_transaction_table(l_count).param_name := 'P_ACTION';
1334 l_transaction_table(l_count).param_value := UPPER(p_action);
1335 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1336
1337 l_count := l_count + 1;
1338 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER1';
1339 l_transaction_table(l_count).param_value := p_telephone_number_1;
1340 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1341
1342 l_count := l_count + 1;
1343 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER2';
1344 l_transaction_table(l_count).param_value := p_telephone_number_2;
1345 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1346
1347 l_count := l_count + 1;
1348 l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER3';
1349 l_transaction_table(l_count).param_value := p_telephone_number_3;
1350 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1351
1352 -- Now add all the Descriptive flex fields into transactions tables
1353
1354 l_count := l_count + 1; -- CONTEXT
1355 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE_CATEGORY';
1356 l_transaction_table(l_count).param_value := p_addr_attribute_category;
1357 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1358
1359 l_count := l_count + 1;
1360 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE1';
1361 l_transaction_table(l_count).param_value := p_addr_attribute1;
1362 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1363
1364 l_count := l_count + 1;
1365 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE2';
1366 l_transaction_table(l_count).param_value := p_addr_attribute2;
1367 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1368
1369 l_count := l_count + 1;
1370 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE3';
1371 l_transaction_table(l_count).param_value := p_addr_attribute3;
1372 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1373
1374 l_count := l_count + 1;
1375 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE4';
1376 l_transaction_table(l_count).param_value := p_addr_attribute4;
1377 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1378
1379 l_count := l_count + 1;
1380 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE5';
1381 l_transaction_table(l_count).param_value := p_addr_attribute5;
1382 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1383
1384 l_count := l_count + 1;
1385 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE6';
1386 l_transaction_table(l_count).param_value := p_addr_attribute6;
1387 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1388
1389 l_count := l_count + 1;
1390 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE7';
1391 l_transaction_table(l_count).param_value := p_addr_attribute7;
1392 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1393
1394 l_count := l_count + 1;
1395 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE8';
1396 l_transaction_table(l_count).param_value := p_addr_attribute8;
1397 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1398
1399 l_count := l_count + 1;
1400 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE9';
1401 l_transaction_table(l_count).param_value := p_addr_attribute9;
1402 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1403
1404 l_count := l_count + 1;
1405 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE10';
1406 l_transaction_table(l_count).param_value := p_addr_attribute10;
1407 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1408
1409 l_count := l_count + 1;
1410 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE11';
1411 l_transaction_table(l_count).param_value := p_addr_attribute11;
1412 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1413
1414 l_count := l_count + 1;
1415 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE12';
1416 l_transaction_table(l_count).param_value := p_addr_attribute12;
1417 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1418
1419 l_count := l_count + 1;
1420 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE13';
1421 l_transaction_table(l_count).param_value := p_addr_attribute13;
1422 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1423
1424 l_count := l_count + 1;
1425 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE14';
1426 l_transaction_table(l_count).param_value := p_addr_attribute14;
1427 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1428
1429 l_count := l_count + 1;
1430 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE15';
1431 l_transaction_table(l_count).param_value := p_addr_attribute15;
1432 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1433
1434 l_count := l_count + 1;
1435 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE16';
1436 l_transaction_table(l_count).param_value := p_addr_attribute16;
1437 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1438
1439 l_count := l_count + 1;
1440 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE17';
1441 l_transaction_table(l_count).param_value := p_addr_attribute17;
1442 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1443
1444 l_count := l_count + 1;
1445 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE18';
1446 l_transaction_table(l_count).param_value := p_addr_attribute18;
1447 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1448
1449 l_count := l_count + 1;
1450 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE19';
1451 l_transaction_table(l_count).param_value := p_addr_attribute19;
1452 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1453
1454
1455 l_count := l_count + 1;
1456 l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE20';
1457 l_transaction_table(l_count).param_value := p_addr_attribute20;
1458 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1459
1460
1461 l_count := l_count + 1;
1462 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION13';
1463 l_transaction_table(l_count).param_value := p_add_information13;
1464 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1465
1466 l_count := l_count + 1;
1467 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION14';
1468 l_transaction_table(l_count).param_value := p_add_information14;
1469 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1470
1471
1472 l_count := l_count + 1;
1473 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION15';
1474 l_transaction_table(l_count).param_value := p_add_information15;
1475 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1476
1477
1478 l_count := l_count + 1;
1479 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION16';
1480 l_transaction_table(l_count).param_value := p_add_information16;
1481 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1482
1483
1484 l_count := l_count + 1;
1485 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION17';
1486 l_transaction_table(l_count).param_value := p_add_information17;
1487 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1488
1489 l_count := l_count + 1;
1490 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION18';
1491 l_transaction_table(l_count).param_value := p_add_information18;
1492 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1493
1494 l_count := l_count + 1;
1495 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION19';
1496 l_transaction_table(l_count).param_value := p_add_information19;
1497 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1498
1499 l_count := l_count + 1;
1500 l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION20';
1501 l_transaction_table(l_count).param_value := p_add_information20;
1502 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1503
1504 l_count := l_count + 1;
1505 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
1506 l_transaction_table(l_count).param_value := p_activity_id;
1507 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1508
1509 BEGIN
1510 hr_utility.set_location( l_proc,50);
1511 l_review_item_name := wf_engine.GetActivityAttrText(itemtype => p_item_type,
1512 itemkey => p_item_key,
1513 actid => p_activity_id,
1514 aname => gv_wf_review_region_item);
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 hr_utility.set_location( l_proc || 'EXCEPTION' ,555);
1518 l_review_item_name := 'HrMainAddressReview';
1519 END;
1520
1521 l_count := l_count + 1;
1522 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
1523 l_transaction_table(l_count).param_value := l_review_item_name;
1524 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1525 if (p_contact_relationship_id > 0) then
1526 hr_utility.set_location( l_proc,55);
1527 l_count := l_count + 1;
1528 l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
1529 l_transaction_table(l_count).param_value := p_contact_relationship_id;
1530 l_transaction_table(l_count).param_data_type := 'NUMBER';
1531 end if;
1532
1533
1534 hr_transaction_ss.save_transaction_step
1535 (p_item_type => p_item_type
1536 ,p_item_key => p_item_key
1537 ,p_login_person_id => nvl(p_login_person_id, p_person_id )
1538 ,p_actid => p_activity_id
1539 ,p_transaction_step_id => l_transaction_step_id
1540 ,p_api_name => g_package || '.PROCESS_API'
1541 ,p_transaction_data => l_transaction_table);
1542
1543
1544 hr_utility.set_location(' Leaving:' || l_proc,60);
1545
1546 EXCEPTION
1547 WHEN hr_utility.hr_error THEN
1548 hr_utility.set_location(' Leaving:' || l_proc,560);
1549
1550 -- -------------------------------------------
1551 -- an application error has been raised so we must
1552 -- redisplay the web form to display the error
1553 -- --------------------------------------------
1554 hr_message.provide_error;
1555 l_message_number := hr_message.last_message_number;
1556 IF l_message_number = 'APP-7165' OR
1557 l_message_number = 'APP-7155' THEN
1558 --populate the p_error_message out variable
1559 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1560 p_error_message => p_error_message,
1561 p_attr_name => 'Page',
1562 p_app_short_name => 'PER',
1563 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
1564 ELSIF l_message_number = 'APP-51139' THEN
1565 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1566 p_error_message => p_error_message,
1567 p_attr_name => 'AddressType',
1568 p_app_short_name => 'PER',
1569 p_message_name => 'HR_PERINFO_INVALID_ADDR_TYPE');
1570 ELSIF l_message_number = 'APP-7952' OR
1571 l_message_number = 'APP-7953' OR
1572 l_message_number = 'APP-51276' OR
1573 l_message_number = 'APP-51282' THEN
1574 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1575 p_error_message => p_error_message,
1576 p_app_short_name => 'PER',
1577 p_message_name => 'HR_INVALID_CITYSTATEZIPCOUNTY');
1578 ELSE
1579 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1580 p_error_message => p_error_message);
1581 END IF;
1582 WHEN OTHERS THEN
1583 hr_utility.set_location(' Leaving:' || l_proc,565);
1584
1585 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1586 p_error_message => p_error_message);
1587 END update_person_address;
1588
1589 -- ---------------------- < get_address_data_from_tt> -------------------------
1590 -- ---------------------------------------------------------------------------
1591 -- Purpose: This procedure will get transaction data which are pending for
1592 -- approval in workflow for a given person id, workflow process name
1593 -- and workflow activity name. This is the overloaded version.
1594 -- ---------------------------------------------------------------------------
1595 PROCEDURE get_address_data_from_tt
1596 (p_item_type in varchar2
1597 ,p_process_name in varchar2
1598 ,p_activity_name in varchar2
1599 ,p_current_person_id in varchar2
1600 ,p_effective_date out nocopy date
1601 ,p_person_id out nocopy number
1602 ,p_address_id out nocopy number
1603 ,p_object_version_number out nocopy number
1604 ,p_primary_flag out nocopy varchar2
1605 ,p_style out nocopy varchar2
1606 ,p_date_from out nocopy date
1607 ,p_date_to out nocopy date
1608 ,p_address_type out nocopy varchar2
1609 ,p_address_type_meaning out nocopy varchar2
1610 ,p_comments out nocopy varchar2
1611 ,p_address_line1 out nocopy varchar2
1612 ,p_address_line2 out nocopy varchar2
1613 ,p_address_line3 out nocopy varchar2
1614 ,p_town_or_city out nocopy varchar2
1615 ,p_region_1 out nocopy varchar2
1616 ,p_region_2 out nocopy varchar2
1617 ,p_region_3 out nocopy varchar2
1618 ,p_postal_code out nocopy varchar2
1619 ,p_country out nocopy varchar2
1620 ,p_country_meaning out nocopy varchar2
1621 ,p_telephone_number_1 out nocopy varchar2
1622 ,p_telephone_number_2 out nocopy varchar2
1623 ,p_telephone_number_3 out nocopy varchar2
1624 ,p_addr_attribute_category out nocopy varchar2
1625 ,p_addr_attribute1 out nocopy varchar2
1626 ,p_addr_attribute2 out nocopy varchar2
1627 ,p_addr_attribute3 out nocopy varchar2
1628 ,p_addr_attribute4 out nocopy varchar2
1629 ,p_addr_attribute5 out nocopy varchar2
1630 ,p_addr_attribute6 out nocopy varchar2
1631 ,p_addr_attribute7 out nocopy varchar2
1632 ,p_addr_attribute8 out nocopy varchar2
1633 ,p_addr_attribute9 out nocopy varchar2
1634 ,p_addr_attribute10 out nocopy varchar2
1635 ,p_addr_attribute11 out nocopy varchar2
1636 ,p_addr_attribute12 out nocopy varchar2
1637 ,p_addr_attribute13 out nocopy varchar2
1638 ,p_addr_attribute14 out nocopy varchar2
1639 ,p_addr_attribute15 out nocopy varchar2
1640 ,p_addr_attribute16 out nocopy varchar2
1641 ,p_addr_attribute17 out nocopy varchar2
1642 ,p_addr_attribute18 out nocopy varchar2
1643 ,p_addr_attribute19 out nocopy varchar2
1644 ,p_addr_attribute20 out nocopy varchar2
1645 ,p_add_information17 out nocopy varchar2
1646 ,p_add_information18 out nocopy varchar2
1647 ,p_add_information19 out nocopy varchar2
1648 ,p_add_information20 out nocopy varchar2
1649 ,p_action out nocopy varchar2
1650 ,p_old_address_id out nocopy varchar2
1651 ,p_add_information13 out nocopy varchar2
1652 ,p_add_information14 out nocopy varchar2
1653 ,p_add_information15 out nocopy varchar2
1654 ,p_add_information16 out nocopy varchar2
1655 )is
1656
1657 l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
1658 l_transaction_id number;
1659 l_trans_step_id number;
1660 l_trans_obj_vers_num number;
1661 l_active_wf_items_tbl hr_workflow_service.active_wf_items_list;
1662 l_active_item_keys_tbl hr_workflow_service.active_wf_items_list;
1663 l_count integer default 0;
1664 -- l_trans_rec_count integer default 0;
1665 l_trans_rec_count number;
1666 begin
1667
1668 hr_utility.set_location(' Entering:' || l_proc,5);
1669
1670 -- ------------------------------------------------------------------
1671 -- Check if there are any transactions waiting to be approved.
1672 -----------------------------------------------------------------------------
1673
1674 -- 1) Find all item keys which have a status of "ACTIVE" for p_process_name
1675 -- 2) Then for those item keys, check if there are any transaction steps
1676 -- exist. There can be defunct workflow processes. Therefore, we must
1677 -- match active processes with transaction tables.
1678 -- 3) If transaction steps are found, check that if the item key is for the
1679 -- particular activity (derive the activity_id from p_activity_name).
1680 -- If found, then there are pending approval transaction data.
1681 --
1682 -- Following function will return a PL/SQL table which has following
1683 -- fields : Item Key, Activity ID
1684 -----------------------------------------------------------------------------
1685 l_active_wf_items_tbl := hr_workflow_service.check_active_wf_items
1686 (p_item_type => p_item_type
1687 ,p_process_name => p_process_name
1688 ,p_current_person_id => p_current_person_id
1689 ,p_activity_name => p_activity_name);
1690
1691 l_count := l_active_wf_items_tbl.COUNT;
1692
1693 IF l_count > 0
1694 -- -------------------------------------------------------------------
1695 -- There are some transactions waiting to be approved for the given
1696 -- process, person and activity name.
1697 -- There can only be 1 row returned but not more for
1698 -- 'HR_MAINT_PERSONAL_DETAILS_FRM', 'HR_MAINT_MAIN_ADDRESS_FRM',
1699 -- 'HR_MAINT_SECONDARY_ADDRESS_FRM . For contacts, there can be ??? rows.
1700 -- -------------------------------------------------------------------
1701 THEN
1702 hr_utility.set_location(l_proc,10);
1703
1704
1705 -- For Personal Information, there can only be 1 transaction step per
1706 -- item type and item key, therefore, we don't need to handle the multiple
1707 -- rows situation.
1708
1709 FOR i in 1..l_count LOOP
1710 hr_transaction_api.get_transaction_step_info
1711 (p_item_type => p_item_type
1712 ,p_item_key => l_active_wf_items_tbl(i).active_item_key
1713 ,p_activity_id => l_active_wf_items_tbl(i).activity_id
1714 ,p_transaction_step_id => l_trans_step_id
1715 ,p_object_version_number => l_trans_obj_vers_num);
1716 END LOOP;
1717 ELSE
1718 hr_utility.set_location(l_proc,15);
1719 l_trans_rec_count := 0;
1720 -- p_trans_rec_count := to_char(l_trans_rec_count);
1721 p_trans_rec_count := l_trans_rec_count;
1722 RETURN;
1723 END IF;
1724
1725 -- If we are here, that means we've found a pending wf item and there should
1726 -- be matching transaction records. If we cannot find any transaction step,
1727 -- that means we have a data integrity error.
1728 IF l_trans_step_id is NULL OR
1729 l_trans_step_id = 0
1730 THEN
1731 hr_utility.set_location(l_proc,20);
1732 RAISE g_data_error;
1733 END IF;
1734 --
1735 -- Now get the transaction data for the given step
1736 get_address_data_from_tt(
1737 p_transaction_step_id => l_trans_step_id
1738 ,p_effective_date => p_effective_date
1739 ,p_person_id => p_person_id
1740 ,p_address_id => p_address_id
1741 ,p_object_version_number => p_object_version_number
1742 ,p_primary_flag => p_primary_flag
1743 ,p_style => p_style
1744 ,p_date_from => p_date_from
1745 ,p_date_to => p_date_to
1746 ,p_address_type => p_address_type
1747 ,p_address_type_meaning => p_address_type_meaning
1748 ,p_comments => p_comments
1749 ,p_address_line1 => p_address_line1
1750 ,p_address_line2 => p_address_line2
1751 ,p_address_line3 => p_address_line3
1752 ,p_town_or_city => p_town_or_city
1753 ,p_region_1 => p_region_1
1754 ,p_region_2 => p_region_2
1755 ,p_region_3 => p_region_3
1756 ,p_postal_code => p_postal_code
1757 ,p_country => p_country
1758 ,p_country_meaning => p_country_meaning
1759 ,p_telephone_number_1 => p_telephone_number_1
1760 ,p_telephone_number_2 => p_telephone_number_2
1761 ,p_telephone_number_3 => p_telephone_number_3
1762 ,p_addr_attribute_category => p_addr_attribute_category
1763 ,p_addr_attribute1 => p_addr_attribute1
1764 ,p_addr_attribute2 => p_addr_attribute2
1765 ,p_addr_attribute3 => p_addr_attribute3
1766 ,p_addr_attribute4 => p_addr_attribute4
1767 ,p_addr_attribute5 => p_addr_attribute5
1768 ,p_addr_attribute6 => p_addr_attribute6
1769 ,p_addr_attribute7 => p_addr_attribute7
1770 ,p_addr_attribute8 => p_addr_attribute8
1771 ,p_addr_attribute9 => p_addr_attribute9
1772 ,p_addr_attribute10 => p_addr_attribute10
1773 ,p_addr_attribute11 => p_addr_attribute11
1774 ,p_addr_attribute12 => p_addr_attribute12
1775 ,p_addr_attribute13 => p_addr_attribute13
1776 ,p_addr_attribute14 => p_addr_attribute14
1777 ,p_addr_attribute15 => p_addr_attribute15
1778 ,p_addr_attribute16 => p_addr_attribute16
1779 ,p_addr_attribute17 => p_addr_attribute17
1780 ,p_addr_attribute18 => p_addr_attribute18
1781 ,p_addr_attribute19 => p_addr_attribute19
1782 ,p_addr_attribute20 => p_addr_attribute20
1783 ,p_add_information17 => p_add_information17
1784 ,p_add_information18 => p_add_information18
1785 ,p_add_information19 => p_add_information19
1786 ,p_add_information20 => p_add_information20
1787 ,p_action => p_action
1788 ,p_old_address_id => p_old_address_id
1789 ,p_add_information13 => p_add_information13
1790 ,p_add_information14 => p_add_information14
1791 ,p_add_information15 => p_add_information15
1792 ,p_add_information16 => p_add_information16
1793 );
1794
1795 -- p_trans_rec_count := to_char(l_trans_rec_count);
1796 p_trans_rec_count := l_trans_rec_count;
1797
1798
1799 hr_utility.set_location(' Leaving:' || l_proc,25);
1800
1801 EXCEPTION
1802 WHEN g_data_error THEN
1803 hr_utility.set_location(' Leaving:' || l_proc,555);
1804 RAISE;
1805
1806 END get_address_data_from_tt;
1807
1808
1809 -- ---------------------------------------------------------------------------
1810 -- ---------------------- < get_address_data_from_tt> -------------------------
1811 -- ---------------------------------------------------------------------------
1812 -- Purpose: This procedure will get transaction data which are saved earlier
1813 -- in the current transaction. This is invoked when a user click BACK
1814 -- button to go back from the Review page to Update page to correct
1815 -- typos or make further changes. Hence, we need to use the item_type
1816 -- item_key passed in to retrieve the transaction record.
1817 -- This is an overloaded version.
1818 -- ---------------------------------------------------------------------------
1819 PROCEDURE get_address_data_from_tt
1820 (p_item_type in varchar2
1821 ,p_item_key in varchar2
1822 ,p_activity_id in varchar2
1823 ,p_trans_rec_count out nocopy number
1824 ,p_effective_date out nocopy date
1825 ,p_person_id out nocopy number
1826 ,p_address_id out nocopy number
1827 ,p_object_version_number out nocopy number
1828 ,p_primary_flag out nocopy varchar2
1829 ,p_style out nocopy varchar2
1830 ,p_date_from out nocopy date
1831 ,p_date_to out nocopy date
1832 ,p_address_type out nocopy varchar2
1833 ,p_address_type_meaning out nocopy varchar2
1834 ,p_comments out nocopy varchar2
1835 ,p_address_line1 out nocopy varchar2
1836 ,p_address_line2 out nocopy varchar2
1837 ,p_address_line3 out nocopy varchar2
1838 ,p_town_or_city out nocopy varchar2
1839 ,p_region_1 out nocopy varchar2
1840 ,p_region_2 out nocopy varchar2
1841 ,p_region_3 out nocopy varchar2
1842 ,p_postal_code out nocopy varchar2
1843 ,p_country out nocopy varchar2
1844 ,p_country_meaning out nocopy varchar2
1845 ,p_telephone_number_1 out nocopy varchar2
1846 ,p_telephone_number_2 out nocopy varchar2
1847 ,p_telephone_number_3 out nocopy varchar2
1848 ,p_addr_attribute_category out nocopy varchar2
1849 ,p_addr_attribute1 out nocopy varchar2
1850 ,p_addr_attribute2 out nocopy varchar2
1851 ,p_addr_attribute3 out nocopy varchar2
1852 ,p_addr_attribute4 out nocopy varchar2
1853 ,p_addr_attribute5 out nocopy varchar2
1854 ,p_addr_attribute6 out nocopy varchar2
1855 ,p_addr_attribute7 out nocopy varchar2
1856 ,p_addr_attribute8 out nocopy varchar2
1857 ,p_addr_attribute9 out nocopy varchar2
1858 ,p_addr_attribute10 out nocopy varchar2
1859 ,p_addr_attribute11 out nocopy varchar2
1860 ,p_addr_attribute12 out nocopy varchar2
1861 ,p_addr_attribute13 out nocopy varchar2
1862 ,p_addr_attribute14 out nocopy varchar2
1863 ,p_addr_attribute15 out nocopy varchar2
1864 ,p_addr_attribute16 out nocopy varchar2
1865 ,p_addr_attribute17 out nocopy varchar2
1866 ,p_addr_attribute18 out nocopy varchar2
1867 ,p_addr_attribute19 out nocopy varchar2
1868 ,p_addr_attribute20 out nocopy varchar2
1869 ,p_add_information17 out nocopy varchar2
1870 ,p_add_information18 out nocopy varchar2
1871 ,p_add_information19 out nocopy varchar2
1872 ,p_add_information20 out nocopy varchar2
1873 ,p_action out nocopy varchar2
1874 ,p_old_address_id out nocopy varchar2
1875 ,p_add_information13 out nocopy varchar2
1876 ,p_add_information14 out nocopy varchar2
1877 ,p_add_information15 out nocopy varchar2
1878 ,p_add_information16 out nocopy varchar2
1879 ) is
1880
1881 l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
1882 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1883 l_api_names hr_util_web.g_varchar2_tab_type;
1884 l_trans_step_rows NUMBER ;
1885
1886 l_trans_rec_count integer default 0;
1887
1888 BEGIN
1889
1890 hr_utility.set_location(' Entering:' || l_proc,5);
1891
1892 -- ------------------------------------------------------------------
1893 -- Check if there are any transaction rec already saved for the current
1894 -- transaction. This is used for re-display the Update page when a user
1895 -- clicks the Back button on the Review page to go back to the Update page
1896 -- to make further changes or to correct errors.
1897 -----------------------------------------------------------------------------
1898 hr_transaction_api.get_transaction_step_info
1899 (p_item_type => p_item_type
1900 ,p_item_key => p_item_key
1901 ,p_activity_id => p_activity_id
1902 ,p_transaction_step_id => l_trans_step_ids
1903 ,p_api_name => l_api_names
1904 ,p_rows => l_trans_step_rows);
1905
1906
1907 IF l_trans_step_rows IS NOT NULL OR
1908 l_trans_step_rows > 0
1909 THEN
1910 hr_utility.set_location(l_proc,10);
1911 l_trans_rec_count := l_trans_step_rows;
1912 ELSE
1913 hr_utility.set_location(l_proc,15);
1914 l_trans_rec_count := 0;
1915 hr_utility.set_location(' Leaving:' || l_proc,20);
1916
1917 return;
1918 END IF;
1919 --
1920 -- -------------------------------------------------------------------
1921 -- There are some changes made earlier in the transaction.
1922 -- Retrieve the data and return to caller.
1923 -- -------------------------------------------------------------------
1924 --
1925 -- Now get the transaction data for the given step
1926 -- Need to loop through l_trans_rec_count -1 as the index starts from 0
1927
1928 FOR i in 0..l_trans_rec_count-1 LOOP
1929 IF(l_api_names(i) = 'HR_PROCESS_ADDRESS_SS.PROCESS_API') THEN
1930 hr_utility.set_location(l_proc || 'LOOP' ,25);
1931 get_address_data_from_tt(
1932 p_transaction_step_id => l_trans_step_ids(i)
1933 ,p_effective_date => p_effective_date
1934 ,p_person_id => p_person_id
1935 ,p_address_id => p_address_id
1936 ,p_object_version_number => p_object_version_number
1937 ,p_primary_flag => p_primary_flag
1938 ,p_style => p_style
1939 ,p_date_from => p_date_from
1940 ,p_date_to => p_date_to
1941 ,p_address_type => p_address_type
1942 ,p_address_type_meaning => p_address_type_meaning
1943 ,p_comments => p_comments
1944 ,p_address_line1 => p_address_line1
1945 ,p_address_line2 => p_address_line2
1946 ,p_address_line3 => p_address_line3
1947 ,p_town_or_city => p_town_or_city
1948 ,p_region_1 => p_region_1
1949 ,p_region_2 => p_region_2
1950 ,p_region_3 => p_region_3
1951 ,p_postal_code => p_postal_code
1952 ,p_country => p_country
1953 ,p_country_meaning => p_country_meaning
1954 ,p_telephone_number_1 => p_telephone_number_1
1955 ,p_telephone_number_2 => p_telephone_number_2
1956 ,p_telephone_number_3 => p_telephone_number_3
1957 ,p_addr_attribute_category => p_addr_attribute_category
1958 ,p_addr_attribute1 => p_addr_attribute1
1959 ,p_addr_attribute2 => p_addr_attribute2
1960 ,p_addr_attribute3 => p_addr_attribute3
1961 ,p_addr_attribute4 => p_addr_attribute4
1962 ,p_addr_attribute5 => p_addr_attribute5
1963 ,p_addr_attribute6 => p_addr_attribute6
1964 ,p_addr_attribute7 => p_addr_attribute7
1965 ,p_addr_attribute8 => p_addr_attribute8
1966 ,p_addr_attribute9 => p_addr_attribute9
1967 ,p_addr_attribute10 => p_addr_attribute10
1968 ,p_addr_attribute11 => p_addr_attribute11
1969 ,p_addr_attribute12 => p_addr_attribute12
1970 ,p_addr_attribute13 => p_addr_attribute13
1971 ,p_addr_attribute14 => p_addr_attribute14
1972 ,p_addr_attribute15 => p_addr_attribute15
1973 ,p_addr_attribute16 => p_addr_attribute16
1974 ,p_addr_attribute17 => p_addr_attribute17
1975 ,p_addr_attribute18 => p_addr_attribute18
1976 ,p_addr_attribute19 => p_addr_attribute19
1977 ,p_addr_attribute20 => p_addr_attribute20
1978 ,p_add_information17 => p_add_information17
1979 ,p_add_information18 => p_add_information18
1980 ,p_add_information19 => p_add_information19
1981 ,p_add_information20 => p_add_information20
1982 ,p_action => p_action
1983 ,p_old_address_id => p_old_address_id
1984 ,p_add_information13 => p_add_information13
1985 ,p_add_information14 => p_add_information14
1986 ,p_add_information15 => p_add_information15
1987 ,p_add_information16 => p_add_information16
1988 );
1989 END IF;
1990 END LOOP;
1991
1992
1993
1994 -- p_trans_rec_count := to_char(l_trans_rec_count);
1995 p_trans_rec_count := l_trans_rec_count;
1996
1997 hr_utility.set_location(' Leaving:' || l_proc,30);
1998
1999 EXCEPTION
2000 WHEN g_data_error THEN
2001 hr_utility.set_location(' Leaving:' || l_proc,555);
2002
2003 RAISE;
2004
2005 END get_address_data_from_tt;
2006
2007
2008 -- ---------------------------------------------------------------------------
2009 -- ---------------------- < get_address_data_from_tt> -------------------------
2010 -- ---------------------------------------------------------------------------
2011 -- Purpose: This procedure will get transaction data which are pending for
2012 -- approval in workflow for a transaction step id.
2013 -- ---------------------------------------------------------------------------
2014 procedure get_address_data_from_tt
2015 (p_transaction_step_id in number
2016 ,p_effective_date out nocopy date
2017 ,p_person_id out nocopy number
2018 ,p_address_id out nocopy number
2019 ,p_object_version_number out nocopy number
2020 ,p_primary_flag out nocopy varchar2
2021 ,p_style out nocopy varchar2
2022 ,p_date_from out nocopy date
2023 ,p_date_to out nocopy date
2024 ,p_address_type out nocopy varchar2
2025 ,p_address_type_meaning out nocopy varchar2
2026 ,p_comments out nocopy varchar2
2027 ,p_address_line1 out nocopy varchar2
2028 ,p_address_line2 out nocopy varchar2
2029 ,p_address_line3 out nocopy varchar2
2030 ,p_town_or_city out nocopy varchar2
2031 ,p_region_1 out nocopy varchar2
2032 ,p_region_2 out nocopy varchar2
2033 ,p_region_3 out nocopy varchar2
2034 ,p_postal_code out nocopy varchar2
2035 ,p_country out nocopy varchar2
2036 ,p_country_meaning out nocopy varchar2
2037 ,p_telephone_number_1 out nocopy varchar2
2038 ,p_telephone_number_2 out nocopy varchar2
2039 ,p_telephone_number_3 out nocopy varchar2
2040 ,p_addr_attribute_category out nocopy varchar2
2041 ,p_addr_attribute1 out nocopy varchar2
2042 ,p_addr_attribute2 out nocopy varchar2
2043 ,p_addr_attribute3 out nocopy varchar2
2044 ,p_addr_attribute4 out nocopy varchar2
2045 ,p_addr_attribute5 out nocopy varchar2
2046 ,p_addr_attribute6 out nocopy varchar2
2047 ,p_addr_attribute7 out nocopy varchar2
2048 ,p_addr_attribute8 out nocopy varchar2
2049 ,p_addr_attribute9 out nocopy varchar2
2050 ,p_addr_attribute10 out nocopy varchar2
2051 ,p_addr_attribute11 out nocopy varchar2
2052 ,p_addr_attribute12 out nocopy varchar2
2053 ,p_addr_attribute13 out nocopy varchar2
2054 ,p_addr_attribute14 out nocopy varchar2
2055 ,p_addr_attribute15 out nocopy varchar2
2056 ,p_addr_attribute16 out nocopy varchar2
2057 ,p_addr_attribute17 out nocopy varchar2
2058 ,p_addr_attribute18 out nocopy varchar2
2059 ,p_addr_attribute19 out nocopy varchar2
2060 ,p_addr_attribute20 out nocopy varchar2
2061 ,p_add_information17 out nocopy varchar2
2062 ,p_add_information18 out nocopy varchar2
2063 ,p_add_information19 out nocopy varchar2
2064 ,p_add_information20 out nocopy varchar2
2065 ,p_action out nocopy varchar2
2066 ,p_old_address_id out nocopy varchar2
2067 ,p_add_information13 out nocopy varchar2
2068 ,p_add_information14 out nocopy varchar2
2069 ,p_add_information15 out nocopy varchar2
2070 ,p_add_information16 out nocopy varchar2
2071 )is
2072
2073 l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
2074
2075 begin
2076
2077 hr_utility.set_location(' Entering:' || l_proc,5);
2078
2079 --
2080 p_effective_date:= to_date(
2081 hr_transaction_ss.get_wf_effective_date
2082 (p_transaction_step_id => p_transaction_step_id),g_date_format);
2083 --
2084 p_person_id := hr_transaction_api.get_number_value
2085 (p_transaction_step_id => p_transaction_step_id
2086 ,p_name => 'P_PERSON_ID');
2087 --
2088 p_address_id := hr_transaction_api.get_number_value
2089 (p_transaction_step_id => p_transaction_step_id
2090 ,p_name => 'P_ADDRESS_ID');
2091 --
2092 p_object_version_number := hr_transaction_api.get_number_value
2093 (p_transaction_step_id => p_transaction_step_id
2094 ,p_name => 'P_OBJECT_VERSION_NUMBER');
2095 --
2096 p_primary_flag := hr_transaction_api.get_varchar2_value
2097 (p_transaction_step_id => p_transaction_step_id
2098 ,p_name => 'P_PRIMARY_FLAG');
2099 -- startregistration anupam
2100 if p_primary_flag = 'T' then
2101 p_primary_flag :='N';
2102 end if;
2103 -- endregistration anupam
2104 --
2105 p_style := hr_transaction_api.get_varchar2_value
2106 (p_transaction_step_id => p_transaction_step_id
2107 ,p_name => 'P_STYLE');
2108
2109 --
2110 p_date_from :=
2111 hr_transaction_api.get_date_value
2112 (p_transaction_step_id => p_transaction_step_id
2113 ,p_name => 'P_DATE_FROM');
2114 --
2115 p_date_to :=
2116 hr_transaction_api.get_date_value
2117 (p_transaction_step_id => p_transaction_step_id
2118 ,p_name => 'P_DATE_TO');
2119 --
2120 p_address_type :=
2121 hr_transaction_api.get_varchar2_value
2122 (p_transaction_step_id => p_transaction_step_id
2123 ,p_name => 'P_ADDRESS_TYPE_CODE');
2124 --
2125 p_address_type_meaning :=
2126 hr_transaction_api.get_varchar2_value
2127 (p_transaction_step_id => p_transaction_step_id
2128 ,p_name => 'P_ADDRESS_TYPE');
2129
2130 --
2131 -- p_comments :=
2132 -- hr_transaction_api.get_varchar2_value
2133 -- (p_transaction_step_id => p_transaction_step_id
2134 -- ,p_name => 'P_COMMENTS');
2135 --
2136 p_address_line1 :=
2137 hr_transaction_api.get_varchar2_value
2138 (p_transaction_step_id => p_transaction_step_id
2139 ,p_name => 'P_ADDRESS_LINE1');
2140 --
2141 p_address_line2 :=
2142 hr_transaction_api.get_varchar2_value
2143 (p_transaction_step_id => p_transaction_step_id
2144 ,p_name => 'P_ADDRESS_LINE2');
2145 --
2146 p_address_line3 :=
2147 hr_transaction_api.get_varchar2_value
2148 (p_transaction_step_id => p_transaction_step_id
2149 ,p_name => 'P_ADDRESS_LINE3');
2150 --
2151 p_town_or_city :=
2152 hr_transaction_api.get_varchar2_value
2153 (p_transaction_step_id => p_transaction_step_id
2154 ,p_name => 'P_CITY');
2155 --
2156 p_region_1 :=
2157 hr_transaction_api.get_varchar2_value
2158 (p_transaction_step_id => p_transaction_step_id
2159 ,p_name => 'P_REGION1');
2160 --
2161 p_region_2 :=
2162 hr_transaction_api.get_varchar2_value
2163 (p_transaction_step_id => p_transaction_step_id
2164 ,p_name => 'P_REGION2');
2165 --
2166 p_region_3 :=
2167 hr_transaction_api.get_varchar2_value
2168 (p_transaction_step_id => p_transaction_step_id
2169 ,p_name => 'P_REGION3');
2170 --
2171 p_postal_code :=
2172 hr_transaction_api.get_varchar2_value
2173 (p_transaction_step_id => p_transaction_step_id
2174 ,p_name => 'P_POSTAL_CODE');
2175 --
2176 p_country :=
2177 hr_transaction_api.get_varchar2_value
2178 (p_transaction_step_id => p_transaction_step_id
2179 ,p_name => 'P_COUNTRY_CODE');
2180 --
2181 p_country_meaning :=
2182 hr_transaction_api.get_varchar2_value
2183 (p_transaction_step_id => p_transaction_step_id
2184 ,p_name => 'P_COUNTRY');
2185 --
2186 p_telephone_number_1 :=
2187 hr_transaction_api.get_varchar2_value
2188 (p_transaction_step_id => p_transaction_step_id
2189 ,p_name => 'P_TELEPHONE_NUMBER1');
2190 --
2191 p_telephone_number_2 :=
2192 hr_transaction_api.get_varchar2_value
2193 (p_transaction_step_id => p_transaction_step_id
2194 ,p_name => 'P_TELEPHONE_NUMBER2');
2195 --
2196 p_telephone_number_3 :=
2197 hr_transaction_api.get_varchar2_value
2198 (p_transaction_step_id => p_transaction_step_id
2199 ,p_name => 'P_TELEPHONE_NUMBER3');
2200 --
2201 p_addr_attribute_category :=
2202 hr_transaction_api.get_varchar2_value
2203 (p_transaction_step_id => p_transaction_step_id
2204 ,p_name => 'P_ADDR_ATTRIBUTE_CATEGORY');
2205 --
2206 p_addr_attribute1 :=
2207 hr_transaction_api.get_varchar2_value
2208 (p_transaction_step_id => p_transaction_step_id
2209 ,p_name => 'P_ADDR_ATTRIBUTE1');
2210 --
2211 p_addr_attribute2 :=
2212 hr_transaction_api.get_varchar2_value
2213 (p_transaction_step_id => p_transaction_step_id
2214 ,p_name => 'P_ADDR_ATTRIBUTE2');
2215 --
2216 p_addr_attribute3 :=
2217 hr_transaction_api.get_varchar2_value
2218 (p_transaction_step_id => p_transaction_step_id
2219 ,p_name => 'P_ADDR_ATTRIBUTE3');
2220 --
2221 p_addr_attribute4 :=
2222 hr_transaction_api.get_varchar2_value
2223 (p_transaction_step_id => p_transaction_step_id
2224 ,p_name => 'P_ADDR_ATTRIBUTE4');
2225 --
2226 p_addr_attribute5 :=
2227 hr_transaction_api.get_varchar2_value
2228 (p_transaction_step_id => p_transaction_step_id
2229 ,p_name => 'P_ADDR_ATTRIBUTE5');
2230 --
2231 p_addr_attribute6 :=
2232 hr_transaction_api.get_varchar2_value
2233 (p_transaction_step_id => p_transaction_step_id
2234 ,p_name => 'P_ADDR_ATTRIBUTE6');
2235 --
2236 p_addr_attribute7 :=
2237 hr_transaction_api.get_varchar2_value
2238 (p_transaction_step_id => p_transaction_step_id
2239 ,p_name => 'P_ADDR_ATTRIBUTE7');
2240 --
2241 p_addr_attribute8 :=
2242 hr_transaction_api.get_varchar2_value
2243 (p_transaction_step_id => p_transaction_step_id
2244 ,p_name => 'P_ADDR_ATTRIBUTE8');
2245 --
2246 p_addr_attribute9 :=
2247 hr_transaction_api.get_varchar2_value
2248 (p_transaction_step_id => p_transaction_step_id
2249 ,p_name => 'P_ADDR_ATTRIBUTE9');
2250 --
2251 p_addr_attribute10 :=
2252 hr_transaction_api.get_varchar2_value
2253 (p_transaction_step_id => p_transaction_step_id
2254 ,p_name => 'P_ADDR_ATTRIBUTE10');
2255 --
2256 p_addr_attribute11 :=
2257 hr_transaction_api.get_varchar2_value
2258 (p_transaction_step_id => p_transaction_step_id
2259 ,p_name => 'P_ADDR_ATTRIBUTE11');
2260 --
2261 p_addr_attribute12 :=
2262 hr_transaction_api.get_varchar2_value
2263 (p_transaction_step_id => p_transaction_step_id
2264 ,p_name => 'P_ADDR_ATTRIBUTE12');
2265 --
2266 p_addr_attribute13 :=
2267 hr_transaction_api.get_varchar2_value
2268 (p_transaction_step_id => p_transaction_step_id
2269 ,p_name => 'P_ADDR_ATTRIBUTE13');
2270 --
2271 p_addr_attribute14 :=
2272 hr_transaction_api.get_varchar2_value
2273 (p_transaction_step_id => p_transaction_step_id
2274 ,p_name => 'P_ADDR_ATTRIBUTE14');
2275 --
2276 p_addr_attribute15 :=
2277 hr_transaction_api.get_varchar2_value
2278 (p_transaction_step_id => p_transaction_step_id
2279 ,p_name => 'P_ADDR_ATTRIBUTE15');
2280 --
2281 p_addr_attribute16 :=
2282 hr_transaction_api.get_varchar2_value
2283 (p_transaction_step_id => p_transaction_step_id
2284 ,p_name => 'P_ADDR_ATTRIBUTE16');
2285 --
2286 p_addr_attribute17 :=
2287 hr_transaction_api.get_varchar2_value
2288 (p_transaction_step_id => p_transaction_step_id
2289 ,p_name => 'P_ADDR_ATTRIBUTE17');
2290 --
2291 p_addr_attribute18 :=
2292 hr_transaction_api.get_varchar2_value
2293 (p_transaction_step_id => p_transaction_step_id
2294 ,p_name => 'P_ADDR_ATTRIBUTE18');
2295 --
2296 p_addr_attribute19 :=
2297 hr_transaction_api.get_varchar2_value
2298 (p_transaction_step_id => p_transaction_step_id
2299 ,p_name => 'P_ADDR_ATTRIBUTE19');
2300 --
2301 p_addr_attribute20 :=
2302 hr_transaction_api.get_varchar2_value
2303 (p_transaction_step_id => p_transaction_step_id
2304 ,p_name => 'P_ADDR_ATTRIBUTE20');
2305 --
2306
2307
2308 p_add_information17 :=
2309 hr_transaction_api.get_varchar2_value
2310 (p_transaction_step_id => p_transaction_step_id
2311 ,p_name => 'P_ADD_INFORMATION17');
2312 --
2313
2314 --
2315 p_add_information18 :=
2316 hr_transaction_api.get_varchar2_value
2317 (p_transaction_step_id => p_transaction_step_id
2318 ,p_name => 'P_ADD_INFORMATION18');
2319 --
2320 --
2321 p_add_information19 :=
2322 hr_transaction_api.get_varchar2_value
2323 (p_transaction_step_id => p_transaction_step_id
2324 ,p_name => 'P_ADD_INFORMATION19');
2325 --
2326 --
2327 p_add_information20 :=
2328 hr_transaction_api.get_varchar2_value
2329 (p_transaction_step_id => p_transaction_step_id
2330 ,p_name => 'P_ADD_INFORMATION20');
2331 --
2332 --
2333 p_action :=
2334 hr_transaction_api.get_varchar2_value
2335 (p_transaction_step_id => p_transaction_step_id
2336 ,p_name => 'P_ACTION');
2337 --
2338 p_old_address_id := hr_transaction_api.get_number_value
2339 (p_transaction_step_id => p_transaction_step_id
2340 ,p_name => 'P_OLD_ADDRESS_ID');
2341
2342 p_add_information13 :=
2343 hr_transaction_api.get_varchar2_value
2344 (p_transaction_step_id => p_transaction_step_id
2345 ,p_name => 'P_ADD_INFORMATION13');
2346 --
2347 p_add_information14 :=
2348 hr_transaction_api.get_varchar2_value
2349 (p_transaction_step_id => p_transaction_step_id
2350 ,p_name => 'P_ADD_INFORMATION14');
2351 --
2352 p_add_information15 :=
2353 hr_transaction_api.get_varchar2_value
2354 (p_transaction_step_id => p_transaction_step_id
2355 ,p_name => 'P_ADD_INFORMATION15');
2356 --
2357 p_add_information16 :=
2358 hr_transaction_api.get_varchar2_value
2359 (p_transaction_step_id => p_transaction_step_id
2360 ,p_name => 'P_ADD_INFORMATION16');
2361 --
2362
2363 hr_utility.set_location(' Leaving:' || l_proc,10);
2364
2365
2366 EXCEPTION
2367 WHEN OTHERS THEN
2368 -- Reset OUT parameters for nocopy.
2369
2370 p_effective_date := NULL;
2371 p_person_id := NULL;
2372 p_address_id := NULL;
2373 p_object_version_number := NULL;
2374 p_primary_flag := NULL;
2375 p_style := NULL;
2376 p_date_from := NULL;
2377 p_date_to := NULL;
2378 p_address_type := NULL;
2379 p_address_type_meaning := NULL;
2380 p_comments := NULL;
2381 p_address_line1 := NULL;
2382 p_address_line2 := NULL;
2383 p_address_line3 := NULL;
2384 p_town_or_city := NULL;
2385 p_region_1 := NULL;
2386 p_region_2 := NULL;
2387 p_region_3 := NULL;
2388 p_postal_code := NULL;
2389 p_country := NULL;
2390 p_country_meaning := NULL;
2391 p_telephone_number_1 := NULL;
2392 p_telephone_number_2 := NULL;
2393 p_telephone_number_3 := NULL;
2394 p_addr_attribute_category := NULL;
2395 p_addr_attribute1 := NULL;
2396 p_addr_attribute2 := NULL;
2397 p_addr_attribute3 := NULL;
2398 p_addr_attribute4 := NULL;
2399 p_addr_attribute5 := NULL;
2400 p_addr_attribute6 := NULL;
2401 p_addr_attribute7 := NULL;
2402 p_addr_attribute8 := NULL;
2403 p_addr_attribute9 := NULL;
2404 p_addr_attribute10 := NULL;
2405 p_addr_attribute11 := NULL;
2406 p_addr_attribute12 := NULL;
2407 p_addr_attribute13 := NULL;
2408 p_addr_attribute14 := NULL;
2409 p_addr_attribute15 := NULL;
2410 p_addr_attribute16 := NULL;
2411 p_addr_attribute17 := NULL;
2412 p_addr_attribute18 := NULL;
2413 p_addr_attribute19 := NULL;
2414 p_addr_attribute20 := NULL;
2415 p_add_information13 := NULL;
2416 p_add_information14 := NULL;
2417 p_add_information15 := NULL;
2418 p_add_information16 := NULL;
2419 p_add_information17 := NULL;
2420 p_add_information18 := NULL;
2421 p_add_information19 := NULL;
2422 p_add_information20 := NULL;
2423 p_action := NULL;
2424 p_old_address_id := NULL;
2425 hr_utility.set_location(' Leaving:' || l_proc,555);
2426
2427 RAISE;
2428
2429 END get_address_data_from_tt;
2430
2431 /*---------------------------------------------------------------------------+
2432 | |
2433 | Name : process_api |
2434 | |
2435 | Purpose : This will procedure is invoked whenever approver |
2436 | approves the address change. |
2437 | |
2438 +-----------------------------------------------------------------------------*/
2439 PROCEDURE process_api
2440 (p_validate in boolean default false
2441 ,p_transaction_step_id in number
2442 ,p_effective_date in varchar2 default null
2443 )
2444 IS
2445
2446 l_proc varchar2(200) := g_package || 'process_api';
2447 l_user_date_format varchar2(200);
2448 l_validate BOOLEAN := true; l_old_ovn NUMBER; l_ovn NUMBER;
2449 l_old_address_id per_addresses.address_id%TYPE;
2450 l_action VARCHAR2(100); l_effective_date date;
2451 l_address per_addresses%ROWTYPE;
2452 l_pradd_ovrlap BOOLEAN := FALSE;
2453 l_contact_or_person varchar2(100);
2454 l_check_for_sfl varchar2(10);
2455 l_sfl_g_contact_step_id NUMBER;
2456
2457 BEGIN
2458 hr_utility.set_location(' Entering:' || l_proc,5);
2459 if (p_effective_date is not null) then
2460 hr_utility.set_location( l_proc, 10);
2461 l_effective_date:= to_date(p_effective_date,g_date_format);
2462 else
2463 hr_utility.set_location( l_proc, 15);
2464 l_effective_date:= to_date(
2465 hr_transaction_ss.get_wf_effective_date
2466 (p_transaction_step_id => p_transaction_step_id),g_date_format);
2467 end if;
2468
2469 -- ------------------------------------------------------------
2470 -- get user date format
2471 -- ------------------------------------------------------------
2472 -- This call is causing an implicit commit and not being used at all
2473 -- commenting the method call
2474 -- l_user_date_format := hr_util_misc_web.get_user_date_format;
2475
2476 l_address.object_version_number :=
2477 hr_transaction_api.get_number_value
2478 (p_Transaction_step_id => p_transaction_step_id
2479 ,p_name => 'P_OBJECT_VERSION_NUMBER');
2480
2481 l_old_ovn :=
2482 hr_transaction_api.get_number_value
2483 (p_Transaction_step_id => p_transaction_step_id
2484 ,p_name => 'P_OLD_OBJECT_VERSION_NUMBER');
2485
2486 l_Address.address_id :=
2487 hr_transaction_api.get_number_value
2488 (p_Transaction_step_id => p_transaction_step_id
2489 ,p_name => 'P_ADDRESS_ID');
2490
2491 l_old_address_id :=
2492 hr_transaction_api.get_number_value
2493 (p_Transaction_step_id => p_transaction_step_id
2494 ,p_name => 'P_OLD_ADDRESS_ID');
2495
2496 l_action :=
2497 hr_transaction_api.get_varchar2_value
2498 (p_Transaction_step_id => p_transaction_step_id
2499 ,p_name => 'P_ACTION');
2500
2501 IF hr_transaction_api.get_varchar2_value
2502 (p_Transaction_step_id => p_transaction_step_id
2503 ,p_name => 'P_PRADD_OVLAPVAL_OVERRIDE') = 'Y' THEN
2504 hr_utility.set_location( l_proc, 20);
2505 l_pradd_ovrlap := TRUE;
2506 ELSE
2507 hr_utility.set_location( l_proc, 25);
2508 l_pradd_ovrlap := FALSE;
2509 END IF;
2510
2511
2512 l_address.date_from :=
2513 (hr_transaction_api.get_date_value
2514 (p_Transaction_step_id => p_transaction_step_id
2515 ,p_name => 'P_DATE_FROM'));
2516
2517 l_address.date_to :=
2518 (hr_transaction_api.get_date_value
2519 (p_Transaction_step_id => p_transaction_step_id
2520 ,p_name => 'P_DATE_TO'));
2521
2522 l_address.person_id :=
2523 hr_transaction_api.get_number_value
2524 (p_Transaction_step_id =>
2525 p_transaction_step_id
2526 ,p_name => 'P_PERSON_ID');
2527 --
2528 -- PB :
2529 --
2530 if l_address.person_id is null or l_address.person_id < 0 then
2531 hr_utility.set_location( l_proc, 30);
2532 --
2533 -- This is the case where the contact and address are created.
2534 -- So get contact person id from the global which is set by
2535 -- hrconwrs.pkb.
2536 --
2537 -- StartRegistration
2538 --
2539 l_contact_or_person :=
2540 hr_transaction_api.get_varchar2_value
2541 (p_Transaction_step_id => p_transaction_step_id
2542 ,p_name => 'P_CONTACT_OR_PERSON');
2543 --
2544 if l_contact_or_person = 'CONTACT' or l_contact_or_person = 'EMER_CR_NEW_CONT' or l_contact_or_person = 'EMRG_OVRW_UPD' or l_contact_or_person = 'EMRG_OVRW_DEL' or l_contact_or_person = 'EMER_CR_NEW_REL'
2545 or l_contact_or_person = 'DPDNT_CR_NEW_CONT' or l_contact_or_person = 'DPDNT_OVRW_UPD' or l_contact_or_person = 'DPDNT_OVRW_DEL' or l_contact_or_person = 'DPDNT_CR_NEW_REL' then
2546 hr_utility.set_location( l_proc, 35);
2547 --
2548 l_address.person_id := hr_process_contact_ss.g_contact_person_id;
2549 --
2550 -- In case of SaveForLater run process_create_contact_api
2551 -- in commit mode to get the g_contact_person_id
2552 -- this will be rolled back with the current step
2553 -- after validating the current transaction step
2554
2555 BEGIN
2556
2557 select 'NOT_SFL'
2558 into l_check_for_sfl
2559 from per_all_people_f
2560 where person_id =l_address.person_id;
2561
2562 EXCEPTION
2563 WHEN no_data_found THEN
2564 hr_utility.set_location( l_proc || 'EXCEPTION' , 555);
2565 l_check_for_sfl := 'SFL';
2566 END;
2567
2568
2569 IF l_check_for_sfl = 'SFL' THEN
2570 hr_utility.set_location( l_proc, 40);
2571 hr_utility.set_location('Address: process_api: It is a save for later step' , 1234);
2572 hr_utility.set_location('g_contact_person_id before :'||hr_process_contact_ss.g_contact_person_id , 1234);
2573
2574 BEGIN
2575 select nvl(hats1.transaction_step_id,0)
2576 into l_sfl_g_contact_step_id
2577 from hr_api_transaction_steps hats1
2578 where hats1.item_type = 'HRSSA'
2579 and hats1.item_key =
2580 (select hats2.item_key
2581 from hr_api_transaction_steps hats2
2582 where hats2.item_type = 'HRSSA'
2583 and hats2.transaction_step_id = p_transaction_step_id )
2584 and hats1.api_name = 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API';
2585
2586
2587
2588 HR_PROCESS_CONTACT_SS.process_create_contact_api (
2589 p_transaction_step_id => l_sfl_g_contact_step_id
2590 );
2591
2592 l_address.person_id := hr_process_contact_ss.g_contact_person_id;
2593 hr_utility.set_location('g_contact_person_id after :'||hr_process_contact_ss.g_contact_person_id , 1234);
2594 EXCEPTION
2595 WHEN others THEN
2596 hr_utility.set_location( l_proc || 'EXCEPTION' , 565);
2597 null;
2598 END;
2599 END IF;
2600
2601 else -- for person PERSON
2602 hr_utility.set_location( l_proc, 45);
2603 --
2604 -- StartRegistration : For Create person and address in one
2605 -- transaction get person id from the package global.
2606 --
2607 -- adding the session id check to avoid connection pooling problems.
2608 /* l_address.person_id := hr_process_person_ss.g_person_id; */
2609 if (( hr_process_person_ss.g_person_id is not null) and
2610 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
2611 hr_utility.set_location( l_proc, 50);
2612 l_address.person_id := hr_process_person_ss.g_person_id;
2613 --
2614 end if;
2615 --
2616 end if;
2617 --
2618 end if;
2619 --
2620 l_address.primary_flag :=
2621 hr_transaction_api.get_varchar2_value
2622 (p_Transaction_step_id => p_transaction_step_id
2623 ,p_name => 'P_PRIMARY_FLAG');
2624 -- startregistration anupam
2625 -- For tertiary address from overview page we have T for primary flag
2626 -- it should be changed to N in the database.
2627 if l_address.primary_flag = 'T' then
2628 hr_utility.set_location( l_proc, 55);
2629 l_address.primary_flag := 'N';
2630 end if;
2631 -- endregistration anupam
2632
2633
2634 l_Address.style :=
2635 hr_transaction_api.get_varchar2_value
2636 (p_Transaction_step_id => p_transaction_step_id
2637 ,p_name => 'P_STYLE');
2638 l_address.address_type :=
2639 hr_transaction_api.get_varchar2_value
2640 (p_Transaction_step_id => p_transaction_step_id
2641 ,p_name => 'P_ADDRESS_TYPE_CODE');
2642 l_Address.address_line1 :=
2643 hr_transaction_api.get_varchar2_value
2644 (p_Transaction_step_id => p_transaction_step_id
2645 ,p_name => 'P_ADDRESS_LINE1');
2646 l_Address.address_line2 :=
2647 hr_transaction_api.get_varchar2_value
2648 (p_Transaction_step_id => p_transaction_step_id
2649 ,p_name => 'P_ADDRESS_LINE2');
2650 l_Address.address_line3 :=
2651 hr_transaction_api.get_varchar2_value
2652 (p_Transaction_step_id => p_transaction_step_id
2653 ,p_name => 'P_ADDRESS_LINE3');
2654 l_address.town_or_city :=
2655 hr_transaction_api.get_varchar2_value
2656 (p_Transaction_step_id => p_transaction_step_id
2657 ,p_name => 'P_CITY');
2658 l_address.region_1 :=
2659 hr_transaction_api.get_varchar2_value
2660 (p_Transaction_step_id => p_transaction_step_id
2661 ,p_name => 'P_REGION1');
2662 l_address.region_2 :=
2663 hr_transaction_api.get_varchar2_value
2664 (p_Transaction_step_id => p_transaction_step_id
2665 ,p_name => 'P_REGION2');
2666 l_address.region_3 :=
2667 hr_transaction_api.get_varchar2_value
2668 (p_Transaction_step_id => p_transaction_step_id
2669 ,p_name => 'P_REGION3');
2670 l_address.postal_code :=
2671 hr_transaction_api.get_varchar2_value
2672 (p_Transaction_step_id => p_transaction_step_id
2673 ,p_name => 'P_POSTAL_CODE');
2674 l_address.country :=
2675 hr_transaction_api.get_varchar2_value
2676 (p_Transaction_step_id => p_transaction_step_id
2677 ,p_name => 'P_COUNTRY_CODE');
2678 l_address.telephone_number_1 :=
2679 hr_transaction_api.get_varchar2_value
2680 (p_Transaction_step_id => p_transaction_step_id
2681 ,p_name => 'P_TELEPHONE_NUMBER1');
2682 l_address.telephone_number_2 :=
2683 hr_transaction_api.get_varchar2_value
2684 (p_Transaction_step_id => p_transaction_step_id
2685 ,p_name => 'P_TELEPHONE_NUMBER2');
2686 l_address.telephone_number_3 :=
2687 hr_transaction_api.get_varchar2_value
2688 (p_Transaction_step_id => p_transaction_step_id
2689 ,p_name => 'P_TELEPHONE_NUMBER3');
2690
2691 -- Now get all the Descriptive Flex fields
2692 l_address.addr_attribute_category :=
2693 hr_transaction_api.get_varchar2_value
2694 (p_Transaction_step_id => p_transaction_step_id
2695 ,p_name => 'P_ADDR_ATTRIBUTE_CATEGORY');
2696 l_address.addr_attribute1 :=
2697 hr_transaction_api.get_varchar2_value
2698 (p_Transaction_step_id => p_transaction_step_id
2699 ,p_name => 'P_ADDR_ATTRIBUTE1');
2700 l_address.addr_attribute2 :=
2701 hr_transaction_api.get_varchar2_value
2702 (p_Transaction_step_id => p_transaction_step_id
2703 ,p_name => 'P_ADDR_ATTRIBUTE2');
2704 l_address.addr_attribute3 :=
2705 hr_transaction_api.get_varchar2_value
2706 (p_Transaction_step_id => p_transaction_step_id
2707 ,p_name => 'P_ADDR_ATTRIBUTE3');
2708 l_address.addr_attribute4 :=
2709 hr_transaction_api.get_varchar2_value
2710 (p_Transaction_step_id => p_transaction_step_id
2711 ,p_name => 'P_ADDR_ATTRIBUTE4');
2712 l_address.addr_attribute5 :=
2713 hr_transaction_api.get_varchar2_value
2714 (p_Transaction_step_id => p_transaction_step_id
2715 ,p_name => 'P_ADDR_ATTRIBUTE5');
2716 l_address.addr_attribute6 :=
2717 hr_transaction_api.get_varchar2_value
2718 (p_Transaction_step_id => p_transaction_step_id
2719 ,p_name => 'P_ADDR_ATTRIBUTE6');
2720 l_address.addr_attribute7 :=
2721 hr_transaction_api.get_varchar2_value
2722 (p_Transaction_step_id => p_transaction_step_id
2723 ,p_name => 'P_ADDR_ATTRIBUTE7');
2724 l_address.addr_attribute8 :=
2725 hr_transaction_api.get_varchar2_value
2726 (p_Transaction_step_id => p_transaction_step_id
2727 ,p_name => 'P_ADDR_ATTRIBUTE8');
2728 l_address.addr_attribute9 :=
2729 hr_transaction_api.get_varchar2_value
2730 (p_Transaction_step_id => p_transaction_step_id
2731 ,p_name => 'P_ADDR_ATTRIBUTE9');
2732 l_address.addr_attribute10 :=
2733 hr_transaction_api.get_varchar2_value
2734 (p_Transaction_step_id => p_transaction_step_id
2735 ,p_name => 'P_ADDR_ATTRIBUTE10');
2736 l_address.addr_attribute11 :=
2737 hr_transaction_api.get_varchar2_value
2738 (p_Transaction_step_id => p_transaction_step_id
2739 ,p_name => 'P_ADDR_ATTRIBUTE11');
2740 l_address.addr_attribute12 :=
2741 hr_transaction_api.get_varchar2_value
2742 (p_Transaction_step_id => p_transaction_step_id
2743 ,p_name => 'P_ADDR_ATTRIBUTE12');
2744 l_address.addr_attribute13 :=
2745 hr_transaction_api.get_varchar2_value
2746 (p_Transaction_step_id => p_transaction_step_id
2747 ,p_name => 'P_ADDR_ATTRIBUTE13');
2748 l_address.addr_attribute14 :=
2749 hr_transaction_api.get_varchar2_value
2750 (p_Transaction_step_id => p_transaction_step_id
2751 ,p_name => 'P_ADDR_ATTRIBUTE14');
2752 l_address.addr_attribute15 :=
2753 hr_transaction_api.get_varchar2_value
2754 (p_Transaction_step_id => p_transaction_step_id
2755 ,p_name => 'P_ADDR_ATTRIBUTE15');
2756 l_address.addr_attribute16 :=
2757 hr_transaction_api.get_varchar2_value
2758 (p_Transaction_step_id => p_transaction_step_id
2759 ,p_name => 'P_ADDR_ATTRIBUTE16');
2760 l_address.addr_attribute17 :=
2761 hr_transaction_api.get_varchar2_value
2762 (p_Transaction_step_id => p_transaction_step_id
2763 ,p_name => 'P_ADDR_ATTRIBUTE17');
2764 l_address.addr_attribute18 :=
2765 hr_transaction_api.get_varchar2_value
2766 (p_Transaction_step_id => p_transaction_step_id
2767 ,p_name => 'P_ADDR_ATTRIBUTE18');
2768 l_address.addr_attribute19 :=
2769 hr_transaction_api.get_varchar2_value
2770 (p_Transaction_step_id => p_transaction_step_id
2771 ,p_name => 'P_ADDR_ATTRIBUTE19');
2772 l_address.addr_attribute20 :=
2773 hr_transaction_api.get_varchar2_value
2774 (p_Transaction_step_id => p_transaction_step_id
2775 ,p_name => 'P_ADDR_ATTRIBUTE20');
2776 l_address.add_information17 :=
2777 hr_transaction_api.get_varchar2_value
2778 (p_Transaction_step_id => p_transaction_step_id
2779 ,p_name => 'P_ADD_INFORMATION17');
2780 l_address.add_information18 :=
2781 hr_transaction_api.get_varchar2_value
2782 (p_Transaction_step_id => p_transaction_step_id
2783 ,p_name => 'P_ADD_INFORMATION18');
2784 l_address.add_information19 :=
2785 hr_transaction_api.get_varchar2_value
2786 (p_Transaction_step_id => p_transaction_step_id
2787 ,p_name => 'P_ADD_INFORMATION19');
2788 l_address.add_information20 :=
2789 hr_transaction_api.get_varchar2_value
2790 (p_Transaction_step_id => p_transaction_step_id
2791 ,p_name => 'P_ADD_INFORMATION20');
2792 l_address.add_information13 :=
2793 hr_transaction_api.get_varchar2_value
2794 (p_Transaction_step_id => p_transaction_step_id
2795 ,p_name => 'P_ADD_INFORMATION13');
2796 l_address.add_information14 :=
2797 hr_transaction_api.get_varchar2_value
2798 (p_Transaction_step_id => p_transaction_step_id
2799 ,p_name => 'P_ADD_INFORMATION14');
2800 l_address.add_information15 :=
2801 hr_transaction_api.get_varchar2_value
2802 (p_Transaction_step_id => p_transaction_step_id
2803 ,p_name => 'P_ADD_INFORMATION15');
2804 l_address.add_information16 :=
2805 hr_transaction_api.get_varchar2_value
2806 (p_Transaction_step_id => p_transaction_step_id
2807 ,p_name => 'P_ADD_INFORMATION16');
2808
2809
2810 IF UPPER(l_action) = 'CHANGE' THEN
2811 IF UPPER(l_address.primary_flag) like 'N%' THEN
2812 hr_utility.set_location( l_proc, 60);
2813 hr_person_address_api.update_person_address
2814 (p_validate => false
2815 ,p_effective_date => l_effective_date
2816 ,p_address_id => l_old_address_id
2817 ,p_object_version_number => l_old_ovn
2818 ,p_date_to => l_Address.date_to);
2819 END IF;
2820 -- Now enter the new address.
2821 hr_person_address_api.create_person_address
2822 (p_validate => false
2823 ,p_effective_date => l_effective_date
2824 ,p_person_id => l_address.person_id
2825 ,p_primary_flag => l_address.primary_flag
2826 ,p_style => l_address.style
2827 ,p_date_from => l_effective_date
2828 ,p_address_type => l_address.address_type
2829 ,p_address_line1 => l_address.address_line1
2830 ,p_address_line2 => l_address.address_line2
2831 ,p_address_line3 => l_address.address_line3
2832 ,p_town_or_city => l_address.town_or_city
2833 ,p_region_1 => l_address.region_1
2834 ,p_region_2 => l_address.region_2
2835 ,p_region_3 => l_address.region_3
2836 ,p_postal_code => l_Address.postal_code
2837 ,p_country => l_address.country
2838 ,p_address_id => l_Address.address_id
2839 ,p_object_Version_number =>
2840 l_address.object_version_number
2841 ,p_telephone_number_1 => l_address.telephone_number_1
2842 ,p_telephone_number_2 => l_address.telephone_number_2
2843 ,p_telephone_number_3 => l_address.telephone_number_3
2844 ,p_pradd_ovlapval_override => l_pradd_ovrlap
2845 ,p_addr_attribute_category => l_address.addr_attribute_category
2846 ,p_addr_attribute1 => l_address.addr_attribute1
2847 ,p_addr_attribute2 => l_address.addr_attribute2
2848 ,p_addr_attribute3 => l_address.addr_attribute3
2849 ,p_addr_attribute4 => l_address.addr_attribute4
2850 ,p_addr_attribute5 => l_address.addr_attribute5
2851 ,p_addr_attribute6 => l_address.addr_attribute6
2852 ,p_addr_attribute7 => l_address.addr_attribute7
2853 ,p_addr_attribute8 => l_address.addr_attribute8
2854 ,p_addr_attribute9 => l_address.addr_attribute9
2855 ,p_addr_attribute10 => l_address.addr_attribute10
2856 ,p_addr_attribute11 => l_address.addr_attribute11
2857 ,p_addr_attribute12 => l_address.addr_attribute12
2858 ,p_addr_attribute13 => l_address.addr_attribute13
2859 ,p_addr_attribute14 => l_address.addr_attribute14
2860 ,p_addr_attribute15 => l_address.addr_attribute15
2861 ,p_addr_attribute16 => l_address.addr_attribute16
2862 ,p_addr_attribute17 => l_address.addr_attribute17
2863 ,p_addr_attribute18 => l_address.addr_attribute18
2864 ,p_addr_attribute19 => l_address.addr_attribute19
2865 ,p_addr_attribute20 => l_address.addr_attribute20
2866 ,p_add_information13 => l_address.add_information13
2867 ,p_add_information14 => l_address.add_information14
2868 ,p_add_information15 => l_address.add_information15
2869 ,p_add_information16 => l_address.add_information16
2870 ,p_add_information17 => l_address.add_information17
2871 ,p_add_information18 => l_address.add_information18
2872 ,p_add_information19 => l_address.add_information19
2873 ,p_add_information20 => l_address.add_information20);
2874 ELSIF UPPER(l_action) = 'CORRECT' THEN --Bug#3114508 start
2875 hr_utility.set_location( l_proc, 65);
2876 if (hr_process_contact_ss.g_is_address_updated = true) then
2877 l_address.object_version_number := l_address.object_version_number + 1;
2878 end if;
2879 hr_person_address_api.update_pers_addr_with_style --Bug#3114508 end
2880 (p_validate => false
2881 ,p_effective_date => trunc(sysdate)
2882 ,p_address_type => l_address.address_type
2883 ,p_address_line1 => l_address.address_line1
2884 ,p_address_line2 => l_address.address_line2
2885 ,p_address_line3 => l_address.address_line3
2886 ,p_town_or_city => l_address.town_or_city
2887 ,p_region_1 => l_address.region_1
2888 ,p_region_2 => l_address.region_2
2889 ,p_region_3 => l_address.region_3
2890 ,p_postal_code => l_Address.postal_code
2891 ,p_country => l_address.country --Bug#3114508 start
2892 ,p_style => l_Address.style --Bug#3114508 end
2893 ,p_address_id => l_Address.address_id
2894 ,p_object_version_number =>
2895 l_address.object_version_number
2896 ,p_telephone_number_1 => l_address.telephone_number_1
2897 ,p_telephone_number_2 => l_address.telephone_number_2
2898 ,p_telephone_number_3 => l_address.telephone_number_3
2899 ,p_addr_attribute_category => l_address.addr_attribute_category
2900 ,p_addr_attribute1 => l_address.addr_attribute1
2901 ,p_addr_attribute2 => l_address.addr_attribute2
2902 ,p_addr_attribute3 => l_address.addr_attribute3
2903 ,p_addr_attribute4 => l_address.addr_attribute4
2904 ,p_addr_attribute5 => l_address.addr_attribute5
2905 ,p_addr_attribute6 => l_address.addr_attribute6
2906 ,p_addr_attribute7 => l_address.addr_attribute7
2907 ,p_addr_attribute8 => l_address.addr_attribute8
2908 ,p_addr_attribute9 => l_address.addr_attribute9
2909 ,p_addr_attribute10 => l_address.addr_attribute10
2910 ,p_addr_attribute11 => l_address.addr_attribute11
2911 ,p_addr_attribute12 => l_address.addr_attribute12
2912 ,p_addr_attribute13 => l_address.addr_attribute13
2913 ,p_addr_attribute14 => l_address.addr_attribute14
2914 ,p_addr_attribute15 => l_address.addr_attribute15
2915 ,p_addr_attribute16 => l_address.addr_attribute16
2916 ,p_addr_attribute17 => l_address.addr_attribute17
2917 ,p_addr_attribute18 => l_address.addr_attribute18
2918 ,p_addr_attribute19 => l_address.addr_attribute19
2919 ,p_addr_attribute20 => l_address.addr_attribute20
2920 ,p_add_information13 => l_address.add_information13
2921 ,p_add_information14 => l_address.add_information14
2922 ,p_add_information15 => l_address.add_information15
2923 ,p_add_information16 => l_address.add_information16
2924 ,p_add_information17 => l_address.add_information17
2925 ,p_add_information18 => l_address.add_information18
2926 ,p_add_information19 => l_address.add_information19
2927 ,p_add_information20 => l_address.add_information20);
2928 hr_process_contact_ss.g_is_address_updated := false;
2929 ELSIF UPPER(l_action) = 'DELETE' THEN
2930 hr_utility.set_location( l_proc, 70);
2931 hr_person_address_api.update_person_address
2932 (p_validate => false
2933 ,p_address_id => l_address.address_id
2934 ,p_object_version_number => l_address.object_version_number
2935 ,p_effective_date => l_effective_date
2936 ,p_date_from => l_address.date_from
2937 ,p_date_to => l_address.date_to);
2938 ELSIF UPPER(l_action) = 'NEW' THEN
2939 hr_utility.set_location( l_proc, 75);
2940 hr_person_address_api.create_person_address
2941 (p_validate => false
2942 ,p_effective_date => l_effective_date
2943 ,p_person_id => l_address.person_id
2944 ,p_primary_flag => l_address.primary_flag
2945 ,p_style => l_address.style
2946 ,p_date_from => l_effective_date
2947 ,p_address_type => l_address.address_type
2948 ,p_address_line1 => l_address.address_line1
2949 ,p_address_line2 => l_address.address_line2
2950 ,p_address_line3 => l_address.address_line3
2951 ,p_town_or_city => l_address.town_or_city
2952 ,p_region_1 => l_address.region_1
2953 ,p_region_2 => l_address.region_2
2954 ,p_region_3 => l_address.region_3
2955 ,p_postal_code => l_address.postal_code
2956 ,p_country => l_address.country
2957 ,p_address_id => l_address.address_id
2958 ,p_object_Version_number =>
2959 l_address.object_version_number
2960 ,p_telephone_number_1 => l_address.telephone_number_1
2961 ,p_telephone_number_2 => l_address.telephone_number_2
2962 ,p_telephone_number_3 => l_address.telephone_number_3
2963 ,p_addr_attribute_category => l_address.addr_attribute_category
2964 ,p_addr_attribute1 => l_address.addr_attribute1
2965 ,p_addr_attribute2 => l_address.addr_attribute2
2966 ,p_addr_attribute3 => l_address.addr_attribute3
2967 ,p_addr_attribute4 => l_address.addr_attribute4
2968 ,p_addr_attribute5 => l_address.addr_attribute5
2969 ,p_addr_attribute6 => l_address.addr_attribute6
2970 ,p_addr_attribute7 => l_address.addr_attribute7
2971 ,p_addr_attribute8 => l_address.addr_attribute8
2972 ,p_addr_attribute9 => l_address.addr_attribute9
2973 ,p_addr_attribute10 => l_address.addr_attribute10
2974 ,p_addr_attribute11 => l_address.addr_attribute11
2975 ,p_addr_attribute12 => l_address.addr_attribute12
2976 ,p_addr_attribute13 => l_address.addr_attribute13
2977 ,p_addr_attribute14 => l_address.addr_attribute14
2978 ,p_addr_attribute15 => l_address.addr_attribute15
2979 ,p_addr_attribute16 => l_address.addr_attribute16
2980 ,p_addr_attribute17 => l_address.addr_attribute17
2981 ,p_addr_attribute18 => l_address.addr_attribute18
2982 ,p_addr_attribute19 => l_address.addr_attribute19
2983 ,p_addr_attribute20 => l_address.addr_attribute20
2984 ,p_add_information13 => l_address.add_information13
2985 ,p_add_information14 => l_address.add_information14
2986 ,p_add_information15 => l_address.add_information15
2987 ,p_add_information16 => l_address.add_information16
2988 ,p_add_information17 => l_address.add_information17
2989 ,p_add_information18 => l_address.add_information18
2990 ,p_add_information19 => l_address.add_information19
2991 ,p_add_information20 => l_address.add_information20);
2992 END IF;
2993
2994 hr_utility.set_location(' Leaving:' || l_proc,80);
2995
2996 END process_api;
2997
2998 END hr_process_address_ss;