[Home] [Help]
PACKAGE BODY: APPS.XLE_LEGAL_ADDRESS_SWI
Source
1 PACKAGE BODY XLE_LEGAL_ADDRESS_SWI AS
2 /* $Header: xleaddrb.pls 120.10 2011/05/20 09:19:42 srsampat ship $ */
3
4 g_package CONSTANT VARCHAR2(33) := 'xle_legal_address_swi.';
5
6 PROCEDURE create_legal_address
7 (p_validate in number
8 ,p_effective_date in date
9 ,p_language_code in varchar2
10 ,p_location_code in varchar2
11 ,p_description in varchar2 default null
12 ,p_address_line_1 in varchar2 default null
13 ,p_address_line_2 in varchar2 default null
14 ,p_address_line_3 in varchar2 default null
18 ,p_region_1 in varchar2 default null
15 ,p_country in varchar2 default null
16 ,p_inactive_date in date default null
17 ,p_postal_code in varchar2 default null
19 ,p_region_2 in varchar2 default null
20 ,p_region_3 in varchar2 default null
21 ,p_style in varchar2 default null
22 ,p_town_or_city in varchar2 default NULL
23 -- Added for ER: 6116752
24 ,p_telephone_number_1 in varchar2 default null
25 ,p_telephone_number_2 in varchar2 default null
26 ,p_telephone_number_3 in varchar2 default null
27 ,p_loc_information13 in varchar2 default null
28 ,p_loc_information14 in varchar2 default null
29 ,p_loc_information15 in varchar2 default null
30 ,p_loc_information16 in varchar2 default null
31 ,p_loc_information17 in varchar2 default null
32 ,p_loc_information18 in varchar2 default null
33 ,p_loc_information19 in varchar2 default null
34 ,p_loc_information20 in varchar2 default null
35 --ER: 6116752 END
36 ,p_attribute_category in varchar2 default null
37 ,p_attribute1 in varchar2 default null
38 ,p_attribute2 in varchar2 default null
39 ,p_attribute3 in varchar2 default null
40 ,p_attribute4 in varchar2 default null
41 ,p_attribute5 in varchar2 default null
42 ,p_attribute6 in varchar2 default null
43 ,p_attribute7 in varchar2 default null
44 ,p_attribute8 in varchar2 default null
45 ,p_attribute9 in varchar2 default null
46 ,p_attribute10 in varchar2 default null
47 ,p_attribute11 in varchar2 default null
48 ,p_attribute12 in varchar2 default null
49 ,p_attribute13 in varchar2 default null
50 ,p_attribute14 in varchar2 default null
51 ,p_attribute15 in varchar2 default null
52 ,p_attribute16 in varchar2 default null
53 ,p_attribute17 in varchar2 default null
54 ,p_attribute18 in varchar2 default null
55 ,p_attribute19 in varchar2 default null
56 ,p_attribute20 in varchar2 default null
57 ,p_business_group_id in number default null
58 ,p_location_id OUT NOCOPY number
59 ,p_object_version_number OUT NOCOPY number
60 ) is
61
62 -- Variables for API Boolean parameters
63 l_validate boolean;
64
65 -- Variables for IN/OUT parameters
66
67 l_location_id number;
68 l_object_version_number number;
69 p_return_status varchar(1);
70 VALIDATE_ADDRESS_FAIL EXCEPTION;
71 l_proc CONSTANT VARCHAR2(72) := g_package ||'create_legal_address';
72 Begin
73
74 hr_utility.set_location(' Entering:' || l_proc,10);
75 --
76 -- Issue a savepoint
77 --
78 savepoint xle_legal_address_swi;
79 --
80 -- Initialise Multiple Message Detection
81 --
82 hr_multi_message.enable_message_list;
83 --
84 -- Remember IN OUT parameter IN values
85 --
86 --
87 -- Convert constant values to their corresponding boolean value
88 --
89 l_validate :=
90 hr_api.constant_to_boolean
91 (p_constant_value => p_validate);
92 --
93 -- Register Surrogate ID or user key values
94 --
95 -- hr_loc_ins.set_base_key_value
96 -- (p_phone_id => p_phone_id
97 -- );
98
99
100 -- hr_lot_ins.set_base_key_value
101 -- (p_phone_id => p_phone_id
102 -- );
103
104 --
105 -- Call API
106 --
107 hr_location_api.create_location_legal_adr
108 (p_validate => l_validate
109 ,p_effective_date => p_effective_date
110 ,p_language_code => p_language_code
111 ,p_location_code => p_location_code
112 ,p_description => p_description
113 ,p_address_line_1 => p_address_line_1
114 ,p_address_line_2 => p_address_line_2
115 ,p_address_line_3 => p_address_line_3
116 ,p_country => p_country
117 ,p_inactive_date => p_inactive_date
118 ,p_postal_code => p_postal_code
119 ,p_region_1 => p_region_1
120 ,p_region_2 => p_region_2
121 ,p_region_3 => p_region_3
122 ,p_style => p_style
123 ,p_town_or_city => p_town_or_city
124 -- Added for ER: 6116752
125 ,p_telephone_number_1 => p_telephone_number_1
126 ,p_telephone_number_2 => p_telephone_number_2
127 ,p_telephone_number_3 => p_telephone_number_3
128 ,p_loc_information13 => p_loc_information13
129 ,p_loc_information14 => p_loc_information14
130 ,p_loc_information15 => p_loc_information15
131 ,p_loc_information16 => p_loc_information16
132 ,p_loc_information17 => p_loc_information17
136 -- ER: 6116752 END
133 ,p_loc_information18 => p_loc_information18
134 ,p_loc_information19 => p_loc_information19
135 ,p_loc_information20 => p_loc_information20
137 ,p_attribute_category => p_attribute_category
138 ,p_attribute1 => p_attribute1
139 ,p_attribute2 => p_attribute2
140 ,p_attribute3 => p_attribute3
141 ,p_attribute4 => p_attribute4
142 ,p_attribute5 => p_attribute5
143 ,p_attribute6 => p_attribute6
144 ,p_attribute7 => p_attribute7
145 ,p_attribute8 => p_attribute8
146 ,p_attribute9 => p_attribute9
147 ,p_attribute10 => p_attribute10
148 ,p_attribute11 => p_attribute11
149 ,p_attribute12 => p_attribute12
150 ,p_attribute13 => p_attribute13
151 ,p_attribute14 => p_attribute14
152 ,p_attribute15 => p_attribute15
153 ,p_attribute16 => p_attribute16
154 ,p_attribute17 => p_attribute17
155 ,p_attribute18 => p_attribute18
156 ,p_attribute19 => p_attribute19
157 ,p_attribute20 => p_attribute20
158 ,p_location_id => l_location_id
159 ,p_object_version_number => l_object_version_number
160 -- ,p_duplicate_warning => l_duplicate_warning
161 );
162 --
163 -- Convert API warning boolean parameter values to specific
164 -- messages and add them to Multiple Message List
165 --
166 -- if l_duplicate_warning then
167 -- fnd_message.set_name('EDIT_HERE: APP_CODE', 'EDIT_HERE: MESSAGE_NAME');
168 -- hr_multi_message.add
169 -- (p_message_type => hr_multi_message.g_warning_msg
170 -- );
171 -- end if;
172 --
173 -- Convert API non-warning boolean parameter values
174 --
175 --
176 -- Derive the API return status value based on whether
177 -- messages of any type exist in the Multiple Message List.
178 -- Also disable Multiple Message Detection.
179 --
180 p_location_id := l_location_id;
181 p_object_version_number := l_object_version_number;
182 p_return_status := hr_multi_message.get_return_status_disable;
183
184
185 hr_utility.set_location(' Leaving:' || l_proc,20);
186 -- Bug: 8434653
187 IF( p_return_status = 'E') then
188 raise VALIDATE_ADDRESS_FAIL;
189 END IF;
190 --
191 exception
192 when hr_multi_message.error_message_exist then
193 --
194 -- Catch the Multiple Message List exception which
195 -- indicates API processing has been aborted because
196 -- at least one message exists in the list.
197 --
198 rollback to xle_legal_address_swi;
199 --
200 -- Reset IN OUT parameters and set OUT parameters
201 --
202 p_object_version_number := null;
203 p_location_id := null;
204 p_return_status := hr_multi_message.get_return_status_disable;
205 hr_utility.set_location(' Leaving:' || l_proc, 30);
206 when VALIDATE_ADDRESS_FAIL then
207
208 rollback to xle_legal_address_swi;
209 hr_utility.set_location(' Leaving:' || l_proc, 30);
210
211 when others then
212 --
213 -- When Multiple Message Detection is enabled catch
214 -- any Application specific or other unexpected
215 -- exceptions. Adding appropriate details to the
216 -- Multiple Message List. Otherwise re-raise the
217 -- error.
218 --
219 rollback to xle_legal_address_swi;
220 if hr_multi_message.unexpected_error_add(l_proc) then
221 hr_utility.set_location(' Leaving:' || l_proc,40);
222 raise;
223 end if;
224 --
225 -- Reset IN OUT and set OUT parameters
226 --
227 p_object_version_number := null;
228 p_location_id := null;
229 p_return_status := hr_multi_message.get_return_status_disable;
230 hr_utility.set_location(' Leaving:' || l_proc,50);
231 end create_legal_address;
232
233 PROCEDURE update_legal_address
234 (p_validate in number
235 ,p_effective_date in date
236 ,p_location_id in number
237 ,p_description in varchar2
238 ,p_address_line_1 in varchar2
239 ,p_address_line_2 in varchar2
240 ,p_address_line_3 in varchar2
241 ,p_inactive_date in date
242 ,p_postal_code in varchar2
243 ,p_region_1 in varchar2
244 ,p_region_2 in varchar2
245 ,p_region_3 in varchar2
246 ,p_style in varchar2
247 ,p_town_or_city in varchar2
248 -- Added for ER: 6116752
249 ,p_telephone_number_1 in varchar2
250 ,p_telephone_number_2 in varchar2
251 ,p_telephone_number_3 in varchar2
252 ,p_loc_information13 in varchar2
253 ,p_loc_information14 in varchar2
254 ,p_loc_information15 in varchar2
255 ,p_loc_information16 in varchar2
256 ,p_loc_information17 in varchar2
257 ,p_loc_information18 in varchar2
258 ,p_loc_information19 in varchar2
259 ,p_loc_information20 in varchar2
260 -- ER: 6116752 END
261 ,p_attribute_category in varchar2
265 ,p_attribute4 in varchar2
262 ,p_attribute1 in varchar2
263 ,p_attribute2 in varchar2
264 ,p_attribute3 in varchar2
266 ,p_attribute5 in varchar2
267 ,p_attribute6 in varchar2
268 ,p_attribute7 in varchar2
269 ,p_attribute8 in varchar2
270 ,p_attribute9 in varchar2
271 ,p_attribute10 in varchar2
272 ,p_attribute11 in varchar2
273 ,p_attribute12 in varchar2
274 ,p_attribute13 in varchar2
275 ,p_attribute14 in varchar2
276 ,p_attribute15 in varchar2
277 ,p_attribute16 in varchar2
278 ,p_attribute17 in varchar2
279 ,p_attribute18 in varchar2
280 ,p_attribute19 in varchar2
281 ,p_attribute20 in varchar2
282 ,p_object_version_number in out nocopy number
283 ) is
284 --
285 -- Variables for API Boolean parameters
286 l_validate boolean;
287 --l_duplicate_warning boolean;
288 --
289 -- Variables for IN/OUT parameters
290 --
291 -- Other variables
292 l_object_version_number number;
293 p_return_status varchar(1);
294 l_temp_ovn CONSTANT number(9) := p_object_version_number;
295 l_proc CONSTANT varchar2(72) := g_package ||'create_legal_address';
296 Begin
297 hr_utility.set_location(' Entering:' || l_proc,10);
298
299 l_object_version_number := p_object_version_number;
300 --
301 -- Issue a savepoint
302 --
303 savepoint xle_legal_address_swi;
304 --
305 -- Initialise Multiple Message Detection
306 --
307 hr_multi_message.enable_message_list;
308 --
309 -- Remember IN OUT parameter IN values
310 --
311 --
312 -- Convert constant values to their corresponding boolean value
313 --
314 l_validate :=
315 hr_api.constant_to_boolean
316 (p_constant_value => p_validate);
317 --
318 -- Register Surrogate ID or user key values
319 --
320 -- hr_loc_ins.set_base_key_value
321 -- (p_phone_id => p_phone_id
322 -- );
323
324
325 -- hr_lot_ins.set_base_key_value
326 -- (p_phone_id => p_phone_id
327 -- );
328
329 --
330 -- Call API
331 --
332 hr_location_api.update_location_legal_adr
333 (p_validate => l_validate
334 ,p_effective_date => p_effective_date
335 ,p_location_id => p_location_id
336 ,p_description => p_description
337 ,p_address_line_1 => p_address_line_1
338 ,p_address_line_2 => p_address_line_2
339 ,p_address_line_3 => p_address_line_3
340 -- For Bug # 6329263
341 -- ,p_inactive_date => p_inactive_date
342 ,p_postal_code => p_postal_code
343 ,p_region_1 => p_region_1
344 ,p_region_2 => p_region_2
345 ,p_region_3 => p_region_3
346 ,p_style => p_style
347 ,p_town_or_city => p_town_or_city
348 -- Added for ER: 6116752
349 ,p_telephone_number_1 => p_telephone_number_1
350 ,p_telephone_number_2 => p_telephone_number_2
351 ,p_telephone_number_3 => p_telephone_number_3
352 ,p_loc_information13 => p_loc_information13
353 ,p_loc_information14 => p_loc_information14
354 ,p_loc_information15 => p_loc_information15
355 ,p_loc_information16 => p_loc_information16
356 ,p_loc_information17 => p_loc_information17
357 ,p_loc_information18 => p_loc_information18
358 ,p_loc_information19 => p_loc_information19
359 ,p_loc_information20 => p_loc_information20
360 -- ER: 6116752 END
361 ,p_attribute_category => p_attribute_category
362 ,p_attribute1 => p_attribute1
363 ,p_attribute2 => p_attribute2
364 ,p_attribute3 => p_attribute3
365 ,p_attribute4 => p_attribute4
366 ,p_attribute5 => p_attribute5
367 ,p_attribute6 => p_attribute6
368 ,p_attribute7 => p_attribute7
369 ,p_attribute8 => p_attribute8
370 ,p_attribute9 => p_attribute9
371 ,p_attribute10 => p_attribute10
372 ,p_attribute11 => p_attribute11
373 ,p_attribute12 => p_attribute12
374 ,p_attribute13 => p_attribute13
375 ,p_attribute14 => p_attribute14
376 ,p_attribute15 => p_attribute15
377 ,p_attribute16 => p_attribute16
378 ,p_attribute17 => p_attribute17
379 ,p_attribute18 => p_attribute18
380 ,p_attribute19 => p_attribute19
381 ,p_attribute20 => p_attribute20
382 ,p_object_version_number => l_object_version_number
383 -- ,p_duplicate_warning => l_duplicate_warning
384 );
385 --
386 -- Convert API warning boolean parameter values to specific
387 -- messages and add them to Multiple Message List
388 --
389 -- if l_duplicate_warning then
390 -- fnd_message.set_name('EDIT_HERE: APP_CODE', 'EDIT_HERE: MESSAGE_NAME');
394 -- end if;
391 -- hr_multi_message.add
392 -- (p_message_type => hr_multi_message.g_warning_msg
393 -- );
395 --
396
397 -- Added for bug 8248634
398 hr_multi_message.end_validation_set;
399
400 -- Convert API non-warning boolean parameter values
401 --
402 --
403 -- Derive the API return status value based on whether
404 -- messages of any type exist in the Multiple Message List.
405 -- Also disable Multiple Message Detection.
406 --
407
408 p_object_version_number := l_object_version_number;
409 p_return_status := hr_multi_message.get_return_status_disable;
410
411
412 hr_utility.set_location(' Leaving:' || l_proc,20);
413 --
414 exception
415 when hr_multi_message.error_message_exist then
416 --
417 -- Catch the Multiple Message List exception which
418 -- indicates API processing has been aborted because
419 -- at least one message exists in the list.
420 --
421 rollback to xle_legal_address_swi;
422 --
423 -- Reset IN OUT parameters and set OUT parameters
424 --
425 p_object_version_number := l_temp_ovn;
426 p_return_status := hr_multi_message.get_return_status_disable;
427 hr_utility.set_location(' Leaving:' || l_proc, 30);
428 --raise;
429 when others then
430 --
431 -- When Multiple Message Detection is enabled catch
432 -- any Application specific or other unexpected
433 -- exceptions. Adding appropriate details to the
434 -- Multiple Message List. Otherwise re-raise the
435 -- error.
436 --
437 rollback to xle_legal_address_swi;
438 if hr_multi_message.unexpected_error_add(l_proc) then
439 hr_utility.set_location(' Leaving:' || l_proc,40);
440 raise;
441 end if;
442 --
443 -- Reset IN OUT and set OUT parameters
444 --
445 p_object_version_number := l_temp_ovn;
446 p_return_status := hr_multi_message.get_return_status_disable;
447 hr_utility.set_location(' Leaving:' || l_proc,50);
448 --raise;
449 end update_legal_address;
450
451
452 -- ER: 7445275
453 PROCEDURE enable_legal_address_flag
454 (p_location_id in number
455 ) is
456 -- variables
457 l_object_version_number number;
458 l_legal_address_flag hr_locations.legal_address_flag%TYPE;
459 l_return_status varchar(1);
460 l_temp_ovn number(9);
461 l_proc CONSTANT varchar2(72) := g_package ||'enable_legal_address_flag';
462 Begin
463
464 select object_version_number, legal_address_flag
465 into l_object_version_number, l_legal_address_flag
466 from hr_locations
467 where location_id = p_location_id;
468
469 if(l_legal_address_flag is null) then
470 l_legal_address_flag := 'N';
471 end if;
472
473 if( l_legal_address_flag <> 'Y') then
474
475 Begin
476
477 hr_utility.set_location(' Entering:' || l_proc,10);
478
479 l_temp_ovn := l_object_version_number;
480 --
481 -- Issue a savepoint
482 --
483 savepoint xle_legal_address_swi;
484 --
485 -- Initialise Multiple Message Detection
486 --
487 hr_multi_message.enable_message_list;
488 --
489 -- Call API
490 --
491
492 hr_location_api.enable_location_legal_adr
493 ( p_validate => false
494 ,p_effective_date => sysdate
495 ,p_location_id => p_location_id
496 ,p_object_version_number =>l_object_version_number
497 );
498
499 -- Convert API non-warning boolean parameter values
500 --
501 --
502 -- Derive the API return status value based on whether
503 -- messages of any type exist in the Multiple Message List.
504 -- Also disable Multiple Message Detection.
505 --
506
507 l_return_status := hr_multi_message.get_return_status_disable;
508
509 hr_utility.set_location(' Leaving:' || l_proc,20);
510
511 exception
512 when hr_multi_message.error_message_exist then
513
514 --
515 -- Catch the Multiple Message List exception which
516 -- indicates API processing has been aborted because
517 -- at least one message exists in the list.
518 --
519 rollback to xle_legal_address_swi;
520 --
521 -- Reset IN OUT parameters and set OUT parameters
522 --
523 l_object_version_number := l_temp_ovn;
524 l_return_status := hr_multi_message.get_return_status_disable;
525 hr_utility.set_location(' Leaving:' || l_proc, 30);
526 --raise;
527 when others then
528 --
529 -- When Multiple Message Detection is enabled catch
530 -- any Application specific or other unexpected
531 -- exceptions. Adding appropriate details to the
532 -- Multiple Message List. Otherwise re-raise the
533 -- error.
534 --
535 rollback to xle_legal_address_swi;
536 if hr_multi_message.unexpected_error_add(l_proc) then
537 hr_utility.set_location(' Leaving:' || l_proc,40);
538 raise;
539 end if;
540 --
541 -- Reset IN OUT and set OUT parameters
542 --
543 l_object_version_number := l_temp_ovn;
544 l_return_status := hr_multi_message.get_return_status_disable;
545 hr_utility.set_location(' Leaving:' || l_proc,50);
546 --raise;
547 end;
548 end if;
549 end enable_legal_address_flag;
550
551 -- Enter further code below as specified in the Package spec.
552 END;