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