[Home] [Help]
PACKAGE BODY: APPS.HR_LOC_SHD
Source
1 PACKAGE BODY hr_loc_shd AS
2 /* $Header: hrlocrhi.pkb 120.7 2006/05/15 13:34:54 srenukun noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' hr_loc_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 FUNCTION return_api_dml_status RETURN BOOLEAN IS
14 --
15 l_proc VARCHAR2(72) := g_package||'return_api_dml_status';
16 --
17 BEGIN
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 RETURN (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 END return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 PROCEDURE constraint_error
29 (p_constraint_name IN all_constraints.constraint_name%type) IS
30 --
31 l_proc VARCHAR2(72) := g_package||'constraint_error';
32 --
33 BEGIN
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 IF (p_constraint_name = 'HR_HRLOC_BILL_TO_SITE_FLAG_CHK') THEN
37 hr_utility.set_message(800, 'PER_52500_INV_YES_NO_FLAG');
38 hr_utility.set_message_token('YES_NO_FLAG', 'Bill-to Site Flag');
39 hr_utility.raise_error;
40 ELSIF (p_constraint_name = 'HR_HRLOC_IN_ORGANIZATION_F_CHK') THEN
41 hr_utility.set_message(800, 'PER_52500_INV_YES_NO_FLAG');
42 hr_utility.set_message_token('YES_NO_FLAG', 'In-Organization Flag');
43 hr_utility.raise_error;
44 ELSIF (p_constraint_name = 'HR_HRLOC_OFFICE_SITE_FLAG_CHK') THEN
45 hr_utility.set_message(800, 'PER_52500_INV_YES_NO_FLAG');
46 hr_utility.set_message_token('YES_NO_FLAG', 'Office Site Flag');
47 hr_utility.raise_error;
48 ELSIF (p_constraint_name = 'HR_HRLOC_RECEIVING_SITE_FL_CHK') THEN
49 hr_utility.set_message(800, 'PER_52500_INV_YES_NO_FLAG');
50 hr_utility.set_message_token('YES_NO_FLAG', 'Receiving Site Flag');
51 hr_utility.raise_error;
52 ELSIF (p_constraint_name = 'HR_HRLOC_SHIP_TO_SITE_FLAG_CHK') THEN
53 hr_utility.set_message(800, 'PER_52500_INV_YES_NO_FLAG');
54 hr_utility.set_message_token('YES_NO_FLAG', 'Ship-to Site Flag');
55 hr_utility.raise_error;
56 ELSIF (p_constraint_name = 'HR_LOCATIONS_PK') THEN
57 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
58 hr_utility.set_message_token('PROCEDURE', l_proc);
59 hr_utility.set_message_token('STEP','30');
60 hr_utility.raise_error;
61 ELSIF (p_constraint_name = 'HR_LOCATIONS_UK2') THEN
62 hr_utility.set_message(800, 'PER_52507_INV_LOCATION_CODE');
63 hr_utility.set_message_token('PROCEDURE', l_proc);
64 hr_utility.set_message_token('STEP','35');
65 hr_utility.raise_error;
66 ELSE
67 hr_utility.set_message(800, 'HR_7877_API_INVALID_CONSTRAINT');
68 hr_utility.set_message_token('PROCEDURE', l_proc);
69 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
70 hr_utility.raise_error;
71 END IF;
72 --
73 hr_utility.set_location(' Leaving:'||l_proc, 10);
74 END constraint_error;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |-----------------------------< api_updating >-----------------------------|
78 -- ----------------------------------------------------------------------------
79 FUNCTION api_updating
80 (
81 p_location_id IN NUMBER,
82 p_object_version_number IN NUMBER
83 ) RETURN BOOLEAN IS
84 --
85 --
86 -- Cursor selects the 'current' row from the HR Schema
87 --
88 cursor csr_select_row IS
89 SELECT
90 location_id,
91 entered_by,
92 location_code,
93 timezone_code,
94 address_line_1,
95 address_line_2,
96 address_line_3,
97 bill_to_site_flag,
98 country,
99 description,
100 designated_receiver_id,
101 in_organization_flag,
102 inactive_date,
103 inventory_organization_id,
104 office_site_flag,
105 postal_code,
106 receiving_site_flag,
107 region_1,
108 region_2,
109 region_3,
110 ship_to_location_id,
111 ship_to_site_flag,
112 derived_locale,
113 style,
114 tax_name,
115 telephone_number_1,
116 telephone_number_2,
117 telephone_number_3,
118 town_or_city,
119 loc_information13,
120 loc_information14,
121 loc_information15,
122 loc_information16,
123 loc_information17,
124 loc_information18,
125 loc_information19,
126 loc_information20,
127 attribute_category,
128 attribute1,
129 attribute2,
130 attribute3,
131 attribute4,
132 attribute5,
133 attribute6,
134 attribute7,
135 attribute8,
136 attribute9,
137 attribute10,
138 attribute11,
139 attribute12,
140 attribute13,
141 attribute14,
142 attribute15,
143 attribute16,
144 attribute17,
145 attribute18,
146 attribute19,
147 attribute20,
148 global_attribute_category,
149 global_attribute1,
150 global_attribute2,
151 global_attribute3,
152 global_attribute4,
153 global_attribute5,
154 global_attribute6,
155 global_attribute7,
156 global_attribute8,
157 global_attribute9,
158 global_attribute10,
159 global_attribute11,
160 global_attribute12,
161 global_attribute13,
162 global_attribute14,
163 global_attribute15,
164 global_attribute16,
165 global_attribute17,
166 global_attribute18,
167 global_attribute19,
168 global_attribute20,
169 legal_address_flag,
170 tp_header_id,
171 ece_tp_location_code,
172 object_version_number,
173 business_group_id,
174 geometry
175 FROM hr_locations_all
176 WHERE location_id = p_location_id;
177 --
178 l_proc VARCHAR2(72) := g_package||'api_updating';
179 l_fct_ret BOOLEAN;
180 --
181 BEGIN
182 hr_utility.set_location('Entering:'||l_proc, 5);
183 --
184 IF (
185 p_location_id IS NULL AND
186 p_object_version_number IS NULL
187 ) THEN
188 --
189 -- One of the primary key arguments is null therefore we must
190 -- set the returning function value to false
191 --
192 l_fct_ret := false;
193 ELSE
194 IF (
195 p_location_id = g_old_rec.location_id AND
196 p_object_version_number = g_old_rec.object_version_number
197 ) THEN
198 hr_utility.set_location(l_proc, 10);
199 --
200 -- The g_old_rec is current therefore we must
201 -- set the returning function to true
202 --
203 l_fct_ret := true;
204 ELSE
205 --
206 -- Select the current row into g_old_rec
207 --
208 OPEN csr_select_row;
209 FETCH csr_select_row INTO g_old_rec;
210 IF csr_select_row%notfound THEN
211 CLOSE csr_select_row;
212 --
213 -- The primary key is invalid therefore we must error
214 --
215 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
216 hr_utility.raise_error;
217 END IF;
218 CLOSE csr_select_row;
219 IF (p_object_version_number <> g_old_rec.object_version_number) THEN
220 hr_utility.set_message(800, 'HR_7155_OBJECT_INVALID');
221 hr_utility.set_message_token('TABLE_NAME', 'HR_LOCATIONS_ALL');
222 hr_utility.raise_error;
223 END IF;
224 hr_utility.set_location(l_proc, 15);
225 l_fct_ret := true;
226 END IF;
227 END IF;
228 hr_utility.set_location(' Leaving:'||l_proc, 20);
229 RETURN (l_fct_ret);
230 --
231 END api_updating;
232 --
233 -- ----------------------------------------------------------------------------
234 -- |---------------------------------< lck >----------------------------------|
235 -- ----------------------------------------------------------------------------
236 PROCEDURE lck
237 (
238 p_location_id IN NUMBER,
239 p_object_version_number IN NUMBER
240 ) IS
241 --
242 -- Cursor selects the 'current' row from the HR Schema
243 --
244 cursor csr_lock_row IS
245 SELECT
246 location_id,
247 entered_by,
248 location_code,
249 timezone_code,
250 address_line_1,
251 address_line_2,
252 address_line_3,
253 bill_to_site_flag,
254 country,
255 description,
256 designated_receiver_id,
257 in_organization_flag,
258 inactive_date,
259 inventory_organization_id,
260 office_site_flag,
261 postal_code,
262 receiving_site_flag,
263 region_1,
264 region_2,
265 region_3,
266 ship_to_location_id,
267 ship_to_site_flag,
268 derived_locale,
269 style,
270 tax_name,
271 telephone_number_1,
272 telephone_number_2,
273 telephone_number_3,
274 town_or_city,
275 loc_information13,
276 loc_information14,
277 loc_information15,
278 loc_information16,
279 loc_information17,
280 loc_information18,
281 loc_information19,
282 loc_information20,
283 attribute_category,
284 attribute1,
285 attribute2,
286 attribute3,
287 attribute4,
288 attribute5,
289 attribute6,
290 attribute7,
291 attribute8,
292 attribute9,
293 attribute10,
294 attribute11,
295 attribute12,
296 attribute13,
297 attribute14,
298 attribute15,
299 attribute16,
300 attribute17,
301 attribute18,
302 attribute19,
303 attribute20,
304 global_attribute_category,
305 global_attribute1,
306 global_attribute2,
307 global_attribute3,
308 global_attribute4,
309 global_attribute5,
310 global_attribute6,
311 global_attribute7,
312 global_attribute8,
313 global_attribute9,
314 global_attribute10,
315 global_attribute11,
316 global_attribute12,
317 global_attribute13,
318 global_attribute14,
319 global_attribute15,
320 global_attribute16,
321 global_attribute17,
322 global_attribute18,
323 global_attribute19,
324 global_attribute20,
325 legal_address_flag,
326 tp_header_id,
327 ece_tp_location_code,
328 object_version_number,
329 business_group_id,
330 geometry
331 FROM hr_locations_all
332 WHERE location_id = p_location_id
333 FOR UPDATE nowait;
334 --
335 l_proc VARCHAR2(72) := g_package||'lck';
336 --
337 BEGIN
338 hr_utility.set_location('Entering:'|| l_proc || '. OVN :' || to_char (p_object_version_number)
339 || ', ID :' || to_char (p_location_id), 5);
340 --
341 -- Add any mandatory argument checking here:
342 -- Example:
343 -- hr_api.mandatory_arg_error
344 -- (p_api_name => l_proc,
345 -- p_argument => 'object_version_number',
346 -- p_argument_value => p_object_version_number);
347 --
348 hr_api.mandatory_arg_error
349 (p_api_name => l_proc,
350 p_argument => 'location_id',
351 p_argument_value => p_location_id);
352 --
353 hr_utility.set_location(l_proc, 6);
354 --
355 hr_api.mandatory_arg_error
356 (p_api_name => l_proc,
357 p_argument => 'object_version_number',
358 p_argument_value => p_object_version_number);
359 --
360 hr_utility.set_location(l_proc, 10);
361 --
362 --
363 OPEN csr_lock_row;
364 FETCH csr_lock_row INTO g_old_rec;
365 IF csr_lock_row%notfound THEN
366 CLOSE csr_lock_row;
367 --
368 -- The primary key is invalid therefore we must error
369 --
370 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
371 hr_utility.raise_error;
372 END IF;
373 CLOSE csr_lock_row;
374 --
375 hr_utility.set_location(l_proc, 15);
376 --
377 IF (p_object_version_number <> nvl (g_old_rec.object_version_number, hr_api.g_number) )
378 THEN
379 hr_utility.set_message(800, 'HR_7155_OBJECT_INVALID');
380 hr_utility.raise_error;
381 END IF;
382 --
383 hr_utility.set_location(' Leaving:'||l_proc, 20);
384 --
385 -- We need to trap the ORA LOCK exception
386 --
387 EXCEPTION
388 WHEN hr_api.object_locked THEN
389 --
390 -- The object is locked therefore we need to supply a meaningful
391 -- error message.
392 --
393 hr_utility.set_message(800, 'HR_7165_OBJECT_LOCKED');
394 hr_utility.set_message_token('TABLE_NAME', 'hr_locations_all');
395 hr_utility.raise_error;
396 END lck;
397 --
398 -- ----------------------------------------------------------------------------
399 -- |-----------------------------< convert_args >-----------------------------|
400 -- ----------------------------------------------------------------------------
401 FUNCTION convert_args
402 (
403 p_location_id IN NUMBER,
404 p_location_code IN VARCHAR2,
405 p_timezone_code IN VARCHAR2,
406 p_address_line_1 IN VARCHAR2,
407 p_address_line_2 IN VARCHAR2,
408 p_address_line_3 IN VARCHAR2,
409 p_bill_to_site_flag IN VARCHAR2,
410 p_country IN VARCHAR2,
411 p_description IN VARCHAR2,
412 p_designated_receiver_id IN NUMBER,
413 p_in_organization_flag IN VARCHAR2,
414 p_inactive_date IN DATE,
415 p_inventory_organization_id IN NUMBER,
416 p_office_site_flag IN VARCHAR2,
417 p_postal_code IN VARCHAR2,
418 p_receiving_site_flag IN VARCHAR2,
419 p_region_1 IN VARCHAR2,
420 p_region_2 IN VARCHAR2,
421 p_region_3 IN VARCHAR2,
422 p_ship_to_location_id IN NUMBER,
423 p_ship_to_site_flag IN VARCHAR2,
424 p_style IN VARCHAR2,
425 p_tax_name IN VARCHAR2,
426 p_telephone_number_1 IN VARCHAR2,
427 p_telephone_number_2 IN VARCHAR2,
428 p_telephone_number_3 IN VARCHAR2,
429 p_town_or_city IN VARCHAR2,
430 p_loc_information13 IN VARCHAR2,
431 p_loc_information14 IN VARCHAR2,
432 p_loc_information15 IN VARCHAR2,
433 p_loc_information16 IN VARCHAR2,
434 p_loc_information17 IN VARCHAR2,
435 p_loc_information18 IN VARCHAR2,
436 p_loc_information19 IN VARCHAR2,
437 p_loc_information20 IN VARCHAR2,
438 p_attribute_category IN VARCHAR2,
439 p_attribute1 IN VARCHAR2,
440 p_attribute2 IN VARCHAR2,
441 p_attribute3 IN VARCHAR2,
442 p_attribute4 IN VARCHAR2,
443 p_attribute5 IN VARCHAR2,
444 p_attribute6 IN VARCHAR2,
445 p_attribute7 IN VARCHAR2,
446 p_attribute8 IN VARCHAR2,
447 p_attribute9 IN VARCHAR2,
448 p_attribute10 IN VARCHAR2,
449 p_attribute11 IN VARCHAR2,
450 p_attribute12 IN VARCHAR2,
451 p_attribute13 IN VARCHAR2,
452 p_attribute14 IN VARCHAR2,
453 p_attribute15 IN VARCHAR2,
454 p_attribute16 IN VARCHAR2,
455 p_attribute17 IN VARCHAR2,
456 p_attribute18 IN VARCHAR2,
457 p_attribute19 IN VARCHAR2,
458 p_attribute20 IN VARCHAR2,
459 p_global_attribute_category IN VARCHAR2,
460 p_global_attribute1 IN VARCHAR2,
461 p_global_attribute2 IN VARCHAR2,
462 p_global_attribute3 IN VARCHAR2,
463 p_global_attribute4 IN VARCHAR2,
464 p_global_attribute5 IN VARCHAR2,
465 p_global_attribute6 IN VARCHAR2,
466 p_global_attribute7 IN VARCHAR2,
467 p_global_attribute8 IN VARCHAR2,
468 p_global_attribute9 IN VARCHAR2,
469 p_global_attribute10 IN VARCHAR2,
470 p_global_attribute11 IN VARCHAR2,
471 p_global_attribute12 IN VARCHAR2,
472 p_global_attribute13 IN VARCHAR2,
473 p_global_attribute14 IN VARCHAR2,
474 p_global_attribute15 IN VARCHAR2,
475 p_global_attribute16 IN VARCHAR2,
476 p_global_attribute17 IN VARCHAR2,
477 p_global_attribute18 IN VARCHAR2,
478 p_global_attribute19 IN VARCHAR2,
479 p_global_attribute20 IN VARCHAR2,
480 p_legal_address_flag IN VARCHAR2,
481 p_tp_header_id IN NUMBER,
482 p_ece_tp_location_code IN VARCHAR2,
483 p_object_version_number IN NUMBER,
484 p_business_group_id IN NUMBER
485 )
486 RETURN g_rec_type IS
487 --
488 l_rec g_rec_type;
489 l_proc VARCHAR2(72) := g_package||'convert_args';
490 --
491 BEGIN
492 --
493 hr_utility.set_location('Entering:'||l_proc, 5);
494 --
495 -- Convert arguments into local l_rec structure.
496 --
497 l_rec.location_id := p_location_id;
498 l_rec.entered_by := 1;
499 l_rec.location_code := p_location_code;
500 l_rec.timezone_code := p_timezone_code;
501 l_rec.address_line_1 := p_address_line_1;
502 l_rec.address_line_2 := p_address_line_2;
503 l_rec.address_line_3 := p_address_line_3;
504 l_rec.bill_to_site_flag := p_bill_to_site_flag;
505 l_rec.country := p_country;
506 l_rec.description := p_description;
507 l_rec.designated_receiver_id := p_designated_receiver_id;
508 l_rec.in_organization_flag := p_in_organization_flag;
509 l_rec.inactive_date := p_inactive_date;
510 l_rec.inventory_organization_id := p_inventory_organization_id;
511 l_rec.office_site_flag := p_office_site_flag;
512 l_rec.postal_code := p_postal_code;
513 l_rec.receiving_site_flag := p_receiving_site_flag;
514 l_rec.region_1 := p_region_1;
515 l_rec.region_2 := p_region_2;
516 l_rec.region_3 := p_region_3;
517 l_rec.ship_to_location_id := p_ship_to_location_id;
518 l_rec.ship_to_site_flag := p_ship_to_site_flag;
519 l_rec.style := p_style;
520 l_rec.tax_name := p_tax_name;
521 l_rec.telephone_number_1 := p_telephone_number_1;
522 l_rec.telephone_number_2 := p_telephone_number_2;
523 l_rec.telephone_number_3 := p_telephone_number_3;
524 l_rec.town_or_city := p_town_or_city;
525 l_rec.loc_information13 := p_loc_information13;
526 l_rec.loc_information14 := p_loc_information14;
527 l_rec.loc_information15 := p_loc_information15;
528 l_rec.loc_information16 := p_loc_information16;
529 l_rec.loc_information17 := p_loc_information17;
530 l_rec.loc_information18 := p_loc_information18;
531 l_rec.loc_information19 := p_loc_information19;
532 l_rec.loc_information20 := p_loc_information20;
533 l_rec.attribute_category := p_attribute_category;
534 l_rec.attribute1 := p_attribute1;
535 l_rec.attribute2 := p_attribute2;
536 l_rec.attribute3 := p_attribute3;
537 l_rec.attribute4 := p_attribute4;
538 l_rec.attribute5 := p_attribute5;
539 l_rec.attribute6 := p_attribute6;
540 l_rec.attribute7 := p_attribute7;
541 l_rec.attribute8 := p_attribute8;
542 l_rec.attribute9 := p_attribute9;
543 l_rec.attribute10 := p_attribute10;
544 l_rec.attribute11 := p_attribute11;
545 l_rec.attribute12 := p_attribute12;
546 l_rec.attribute13 := p_attribute13;
547 l_rec.attribute14 := p_attribute14;
548 l_rec.attribute15 := p_attribute15;
549 l_rec.attribute16 := p_attribute16;
550 l_rec.attribute17 := p_attribute17;
551 l_rec.attribute18 := p_attribute18;
552 l_rec.attribute19 := p_attribute19;
553 l_rec.attribute20 := p_attribute20;
554 l_rec.object_version_number := p_object_version_number;
555 l_rec.global_attribute_category := p_global_attribute_category;
556 l_rec.global_attribute1 := p_global_attribute1;
557 l_rec.global_attribute2 := p_global_attribute2;
558 l_rec.global_attribute3 := p_global_attribute3;
559 l_rec.global_attribute4 := p_global_attribute4;
560 l_rec.global_attribute5 := p_global_attribute5;
561 l_rec.global_attribute6 := p_global_attribute6;
562 l_rec.global_attribute7 := p_global_attribute7;
563 l_rec.global_attribute8 := p_global_attribute8;
564 l_rec.global_attribute9 := p_global_attribute9;
565 l_rec.global_attribute10 := p_global_attribute10;
566 l_rec.global_attribute11 := p_global_attribute11;
567 l_rec.global_attribute12 := p_global_attribute12;
568 l_rec.global_attribute13 := p_global_attribute13;
569 l_rec.global_attribute14 := p_global_attribute14;
570 l_rec.global_attribute15 := p_global_attribute15;
571 l_rec.global_attribute16 := p_global_attribute16;
572 l_rec.global_attribute17 := p_global_attribute17;
573 l_rec.global_attribute18 := p_global_attribute18;
574 l_rec.global_attribute19 := p_global_attribute19;
575 l_rec.global_attribute20 := p_global_attribute20;
576 hr_utility.set_location(' Leaving:'||l_proc, 101);
577 l_rec.legal_address_flag := p_legal_address_flag;
578 hr_utility.set_location(' Leaving:'||l_proc, 102);
579 l_rec.tp_header_id := p_tp_header_id;
580 l_rec.ece_tp_location_code := p_ece_tp_location_code;
581 l_rec.business_group_id := p_business_group_id;
582 --
583 -- Return the plsql record structure.
584 --
585 hr_utility.set_location(' Leaving:'||l_proc, 10);
586 RETURN(l_rec);
587 --
588 END convert_args;
589 --
590 --
591 -- ----------------------------------------------------------------------------
592 -- |----------------------------< derive_locale >-----------------------------|
593 -- ----------------------------------------------------------------------------
594 procedure derive_locale(p_rec in out nocopy hr_loc_shd.g_rec_type)
595 is
596 --
597 l_number_table dbms_describe.number_table;
598 l_varchar_table dbms_describe.varchar2_table;
599 l_package_exists boolean;
600 l_package_name varchar2(2000);
601 l_seperator varchar2(1);
602 --
603 begin
604 BEGIN
605 l_package_name := 'HR_'||substr(p_rec.style,1,2)||'_UTILITY.DERIVE_HR_LOC_ADDRESS';-- fix for bug 4518559.
606 hr_general.describe_procedure(
607 object_name => l_package_name,
608 reserved1 => null,
609 reserved2 => null,
610 overload => l_number_table,
611 position => l_number_table,
612 level => l_number_table,
613 argument_name => l_varchar_table,
614 datatype => l_number_table,
615 default_value => l_number_table,
616 in_out => l_number_table,
617 length => l_number_table,
618 precision => l_number_table,
619 scale => l_number_table,
620 radix => l_number_table,
621 spare => l_number_table
622 );
623 -- If no exception raised at this point, then ready to concatenate address
624 -- fields using legislative package.
625 l_package_exists := true;
626 EXCEPTION
627 when others then
628 -- Package doesn't exist.
629 l_package_exists := false;
630 END;
631
632 IF l_package_exists THEN
633 l_package_name := 'begin ' ||l_package_name ||
634 '(:tax_name,' ||
635 ':style,' ||
636 ':address_line_1,' ||
637 ':address_line_2,' ||
638 ':address_line_3,' ||
639 ':town_or_city,' ||
640 ':country,' ||
641 ':postal_code,' ||
642 ':region_1,' ||
643 ':region_2,' ||
644 ':region_3,' ||
645 ':telephone_number_1,' ||
646 ':telephone_number_2,' ||
647 ':telephone_number_3,' ||
648 ':loc_information13,' ||
649 ':loc_information14,' ||
650 ':loc_information15,' ||
651 ':loc_information16,' ||
652 ':loc_information17,' ||
653 ':attribute_category,' ||
654 ':attribute1,' ||
655 ':attribute2,' ||
656 ':attribute3,' ||
657 ':attribute4,' ||
658 ':attribute5,' ||
659 ':attribute6,' ||
660 ':attribute7,' ||
661 ':attribute8,' ||
662 ':attribute9,' ||
663 ':attribute10,' ||
664 ':attribute11,' ||
665 ':attribute12,' ||
666 ':attribute13,' ||
667 ':attribute14,' ||
668 ':attribute15,' ||
669 ':attribute16,' ||
670 ':attribute17,' ||
671 ':attribute18,' ||
672 ':attribute19,' ||
673 ':attribute20,' ||
674 ':global_attribute_category,' ||
675 ':global_attribute1,' ||
676 ':global_attribute2,' ||
677 ':global_attribute3,' ||
678 ':global_attribute4,' ||
679 ':global_attribute5,' ||
680 ':global_attribute6,' ||
681 ':global_attribute7,' ||
682 ':global_attribute8,' ||
683 ':global_attribute9,' ||
684 ':global_attribute10,' ||
685 ':global_attribute11,' ||
686 ':global_attribute12,' ||
687 ':global_attribute13,' ||
688 ':global_attribute14,' ||
689 ':global_attribute15,' ||
690 ':global_attribute16,' ||
691 ':global_attribute17,' ||
692 ':global_attribute18,' ||
693 ':global_attribute19,' ||
694 ':global_attribute20,' ||
695 ':loc_information18,' ||
696 ':loc_information19,' ||
697 ':loc_information20,' ||
698 ':derived_locale); ' ||
699 'end; ';
700 execute immediate l_package_name
701 using in p_rec.tax_name,
702 in p_rec.style,
703 in p_rec.address_line_1,
704 in p_rec.address_line_2,
705 in p_rec.address_line_3,
706 in p_rec.town_or_city,
707 in p_rec.country,
708 in p_rec.postal_code,
709 in p_rec.region_1,
710 in p_rec.region_2,
711 in p_rec.region_3,
712 in p_rec.telephone_number_1,
713 in p_rec.telephone_number_2,
714 in p_rec.telephone_number_3,
715 in p_rec.loc_information13,
716 in p_rec.loc_information14,
717 in p_rec.loc_information15,
718 in p_rec.loc_information16,
719 in p_rec.loc_information17,
720 in p_rec.attribute_category,
721 in p_rec.attribute1,
722 in p_rec.attribute2,
723 in p_rec.attribute3,
724 in p_rec.attribute4,
725 in p_rec.attribute5,
726 in p_rec.attribute6,
727 in p_rec.attribute7,
728 in p_rec.attribute8,
729 in p_rec.attribute9,
730 in p_rec.attribute10,
731 in p_rec.attribute11,
732 in p_rec.attribute12,
733 in p_rec.attribute13,
734 in p_rec.attribute14,
735 in p_rec.attribute15,
736 in p_rec.attribute16,
737 in p_rec.attribute17,
738 in p_rec.attribute18,
739 in p_rec.attribute19,
740 in p_rec.attribute20,
741 in p_rec.global_attribute_category,
742 in p_rec.global_attribute1,
743 in p_rec.global_attribute2,
744 in p_rec.global_attribute3,
745 in p_rec.global_attribute4,
746 in p_rec.global_attribute5,
747 in p_rec.global_attribute6,
748 in p_rec.global_attribute7,
749 in p_rec.global_attribute8,
750 in p_rec.global_attribute9,
751 in p_rec.global_attribute10,
752 in p_rec.global_attribute11,
753 in p_rec.global_attribute12,
754 in p_rec.global_attribute13,
755 in p_rec.global_attribute14,
756 in p_rec.global_attribute15,
757 in p_rec.global_attribute16,
758 in p_rec.global_attribute17,
759 in p_rec.global_attribute18,
760 in p_rec.global_attribute19,
761 in p_rec.global_attribute20,
762 in p_rec.loc_information18,
763 in p_rec.loc_information19,
764 in p_rec.loc_information20,
765 out p_rec.derived_locale;
766 ELSE
767 if (ltrim(p_rec.town_or_city) is null) OR
768 (ltrim(p_rec.country) is null) then
769 l_seperator := '';
770 else
771 l_seperator := ',';
772 end if;
773 p_rec.derived_locale := ltrim(p_rec.town_or_city) || l_seperator || ltrim(p_rec.country);
774 END IF;
775 end;
776
777 END hr_loc_shd;