[Home] [Help]
PACKAGE BODY: APPS.PER_ADD_SHD
Source
1 Package Body per_add_shd as
2 /* $Header: peaddrhi.pkb 120.1.12010000.3 2008/08/06 08:53:04 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_add_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
30 )
31 Is
32 --
33 l_proc varchar2(72) := g_package||'constraint_error';
34 --
35 Begin
36 hr_utility.set_location('Entering:'||l_proc, 5);
37 --
38 -- 70.2 change d start.
39 --
40 If (p_constraint_name = 'PER_ADDRESSES_PK') Then
41 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
42 hr_utility.raise_error;
43 ElsIf (p_constraint_name = 'PER_ADDR_PRIMARY_FLAG_CHK') Then
44 hr_utility.set_message(801, 'HR_7325_ADD_INVALID_PRIM_FLAG');
45 hr_utility.raise_error;
46 Else
47 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
48 hr_utility.set_message_token('PROCEDURE', l_proc);
49 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
50 hr_utility.raise_error;
51 End If;
52 --
53 -- 70.2 change d end.
54 --
55 hr_utility.set_location(' Leaving:'||l_proc, 10);
56 End constraint_error;
57 --
58 -- ----------------------------------------------------------------------------
59 -- |-----------------------------< api_updating >-----------------------------|
60 -- ----------------------------------------------------------------------------
61 Function api_updating
62 (
63 p_address_id in number,
64 p_object_version_number in number
65 ) Return Boolean Is
66 --
67 --
68 -- Cursor selects the 'current' row from the HR Schema
69 --
70 Cursor C_Sel1 is
71 select
72 address_id,
73 business_group_id,
74 person_id,
75 date_from,
76 primary_flag,
77 derived_locale,
78 style,
79 address_line1,
80 address_line2,
81 address_line3,
82 address_type,
83 comments,
84 country,
85 date_to,
86 postal_code,
87 region_1,
88 region_2,
89 region_3,
90 telephone_number_1,
91 telephone_number_2,
92 telephone_number_3,
93 town_or_city,
94 request_id,
95 program_application_id,
96 program_id,
97 program_update_date,
98 addr_attribute_category,
99 addr_attribute1,
100 addr_attribute2,
101 addr_attribute3,
102 addr_attribute4,
103 addr_attribute5,
104 addr_attribute6,
105 addr_attribute7,
106 addr_attribute8,
107 addr_attribute9,
108 addr_attribute10,
109 addr_attribute11,
110 addr_attribute12,
111 addr_attribute13,
112 addr_attribute14,
113 addr_attribute15,
114 addr_attribute16,
115 addr_attribute17,
116 addr_attribute18,
117 addr_attribute19,
118 addr_attribute20,
119 add_information13,
120 add_information14,
121 add_information15,
122 add_information16,
123 add_information17,
124 add_information18,
125 add_information19,
126 add_information20,
127 object_version_number,
128 party_id, -- HR/TCA merge
129 geometry
130 from per_addresses
131 where address_id = p_address_id;
132 --
133 l_proc varchar2(72) := g_package||'api_updating';
134 l_fct_ret boolean;
135 --
136 Begin
137 hr_utility.set_location('Entering:'||l_proc, 5);
138 --
139 If (
140 p_address_id is null and
141 p_object_version_number is null
142 ) Then
143 --
144 -- One of the primary key arguments is null therefore we must
145 -- set the returning function value to false
146 --
147 l_fct_ret := false;
148 Else
149 If (
150 p_address_id = g_old_rec.address_id and
151 p_object_version_number = g_old_rec.object_version_number
152 ) Then
153 hr_utility.set_location(l_proc, 10);
154 --
155 -- The g_old_rec is current therefore we must
156 -- set the returning function to true
157 --
158 l_fct_ret := true;
159 Else
160 --
161 -- Select the current row into g_old_rec
162 --
163 Open C_Sel1;
164 Fetch C_Sel1 Into g_old_rec;
165 If C_Sel1%notfound Then
166 Close C_Sel1;
167 --
168 -- The primary key is invalid therefore we must error
169 --
170 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
171 hr_utility.raise_error;
172 End If;
173 Close C_Sel1;
174 If (p_object_version_number <> g_old_rec.object_version_number) Then
175 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
176 hr_utility.raise_error;
177 End If;
178 hr_utility.set_location(l_proc, 15);
179 l_fct_ret := true;
180 End If;
181 End If;
182 hr_utility.set_location(' Leaving:'||l_proc, 20);
183 Return (l_fct_ret);
184 --
185 End api_updating;
186 --
187 -- ----------------------------------------------------------------------------
188 -- |---------------------------------< lck >----------------------------------|
189 -- ----------------------------------------------------------------------------
190 Procedure lck
191 (
192 p_address_id in number,
193 p_object_version_number in number
194 ) is
195 --
196 -- Cursor selects the 'current' row from the HR Schema
197 --
198 Cursor C_Sel1 is
199 select address_id,
200 business_group_id,
201 person_id,
202 date_from,
203 primary_flag,
204 derived_locale,
205 style,
206 address_line1,
207 address_line2,
208 address_line3,
209 address_type,
210 comments,
211 country,
212 date_to,
213 postal_code,
214 region_1,
215 region_2,
216 region_3,
217 telephone_number_1,
218 telephone_number_2,
219 telephone_number_3,
220 town_or_city,
221 request_id,
222 program_application_id,
223 program_id,
224 program_update_date,
225 addr_attribute_category,
226 addr_attribute1,
227 addr_attribute2,
228 addr_attribute3,
229 addr_attribute4,
230 addr_attribute5,
231 addr_attribute6,
232 addr_attribute7,
233 addr_attribute8,
234 addr_attribute9,
235 addr_attribute10,
236 addr_attribute11,
237 addr_attribute12,
238 addr_attribute13,
239 addr_attribute14,
240 addr_attribute15,
241 addr_attribute16,
242 addr_attribute17,
243 addr_attribute18,
244 addr_attribute19,
245 addr_attribute20,
246 add_information13,
247 add_information14,
248 add_information15,
249 add_information16,
250 add_information17,
251 add_information18,
252 add_information19,
253 add_information20,
254 object_version_number,
255 party_id, -- HR/TCA merge
256 geometry
257 from per_addresses
258 where address_id = p_address_id
259 for update nowait;
260 --
261 l_proc varchar2(72) := g_package||'lck';
262 --
263 Begin
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 hr_api.mandatory_arg_error
267 (p_api_name => l_proc
268 ,p_argument => 'address_id'
269 , p_argument_value => p_address_id);
270 --
271 hr_api.mandatory_arg_error
272 (p_api_name => l_proc,
273 p_argument => 'object_version_number',
274 p_argument_value => p_object_version_number);
275 --
276 Open C_Sel1;
277 Fetch C_Sel1 Into g_old_rec;
278 If C_Sel1%notfound then
279 Close C_Sel1;
280 --
281 -- The primary key is invalid therefore we must error
282 --
283 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
284 hr_utility.raise_error;
285 End If;
286 Close C_Sel1;
287 If (p_object_version_number <> g_old_rec.object_version_number) Then
288 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
289 hr_utility.raise_error;
290 End If;
291 --
292 hr_utility.set_location(' Leaving:'||l_proc, 10);
293 --
294 -- We need to trap the ORA LOCK exception
295 --
296 Exception
297 When HR_Api.Object_Locked then
298 --
299 -- The object is locked therefore we need to supply a meaningful
300 -- error message.
301 --
302 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
303 hr_utility.set_message_token('TABLE_NAME', 'per_addresses');
304 hr_utility.raise_error;
305 End lck;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |-----------------------------< convert_args >-----------------------------|
309 -- ----------------------------------------------------------------------------
310 Function convert_args
311 (
312 p_address_id in number,
313 p_business_group_id in number,
314 p_person_id in number,
315 p_date_from in date,
316 p_primary_flag in varchar2,
317 p_style in varchar2,
318 p_address_line1 in varchar2,
319 p_address_line2 in varchar2,
320 p_address_line3 in varchar2,
321 p_address_type in varchar2,
322 p_comments in long,
323 p_country in varchar2,
324 p_date_to in date,
325 p_postal_code in varchar2,
326 p_region_1 in varchar2,
327 p_region_2 in varchar2,
328 p_region_3 in varchar2,
329 p_telephone_number_1 in varchar2,
330 p_telephone_number_2 in varchar2,
331 p_telephone_number_3 in varchar2,
332 p_town_or_city in varchar2,
333 p_request_id in number,
334 p_program_application_id in number,
335 p_program_id in number,
336 p_program_update_date in date,
337 p_addr_attribute_category in varchar2,
338 p_addr_attribute1 in varchar2,
339 p_addr_attribute2 in varchar2,
340 p_addr_attribute3 in varchar2,
341 p_addr_attribute4 in varchar2,
342 p_addr_attribute5 in varchar2,
343 p_addr_attribute6 in varchar2,
344 p_addr_attribute7 in varchar2,
345 p_addr_attribute8 in varchar2,
346 p_addr_attribute9 in varchar2,
347 p_addr_attribute10 in varchar2,
348 p_addr_attribute11 in varchar2,
349 p_addr_attribute12 in varchar2,
350 p_addr_attribute13 in varchar2,
351 p_addr_attribute14 in varchar2,
352 p_addr_attribute15 in varchar2,
353 p_addr_attribute16 in varchar2,
354 p_addr_attribute17 in varchar2,
355 p_addr_attribute18 in varchar2,
356 p_addr_attribute19 in varchar2,
357 p_addr_attribute20 in varchar2,
358 p_add_information13 in varchar2,
359 p_add_information14 in varchar2,
360 p_add_information15 in varchar2,
361 p_add_information16 in varchar2,
362 p_add_information17 in varchar2,
363 p_add_information18 in varchar2,
364 p_add_information19 in varchar2,
365 p_add_information20 in varchar2,
366 p_object_version_number in number,
367 p_party_id in number default null -- HR/TCA merge
368 )
369 Return g_rec_type is
370 --
371 l_rec g_rec_type;
372 l_proc varchar2(72) := g_package||'convert_args';
373 --
374 Begin
375 --
376 hr_utility.set_location('Entering:'||l_proc, 5);
377 --
378 -- Convert arguments into local l_rec structure.
379 --
380 l_rec.address_id := p_address_id;
381 l_rec.business_group_id := p_business_group_id;
382 l_rec.person_id := p_person_id;
383 l_rec.date_from := p_date_from;
384 l_rec.primary_flag := p_primary_flag;
385 l_rec.style := p_style;
386 l_rec.address_line1 := p_address_line1;
387 l_rec.address_line2 := p_address_line2;
388 l_rec.address_line3 := p_address_line3;
389 l_rec.address_type := p_address_type;
390 l_rec.comments := p_comments;
391 l_rec.country := p_country;
392 l_rec.date_to := p_date_to;
393 l_rec.postal_code := p_postal_code;
394 l_rec.region_1 := p_region_1;
395 l_rec.region_2 := p_region_2;
396 l_rec.region_3 := p_region_3;
397 l_rec.telephone_number_1 := p_telephone_number_1;
398 l_rec.telephone_number_2 := p_telephone_number_2;
399 l_rec.telephone_number_3 := p_telephone_number_3;
400 l_rec.town_or_city := p_town_or_city;
401 l_rec.request_id := p_request_id;
402 l_rec.program_application_id := p_program_application_id;
403 l_rec.program_id := p_program_id;
404 l_rec.program_update_date := p_program_update_date;
405 l_rec.addr_attribute_category := p_addr_attribute_category;
406 l_rec.addr_attribute1 := p_addr_attribute1;
407 l_rec.addr_attribute2 := p_addr_attribute2;
408 l_rec.addr_attribute3 := p_addr_attribute3;
409 l_rec.addr_attribute4 := p_addr_attribute4;
413 l_rec.addr_attribute8 := p_addr_attribute8;
410 l_rec.addr_attribute5 := p_addr_attribute5;
411 l_rec.addr_attribute6 := p_addr_attribute6;
412 l_rec.addr_attribute7 := p_addr_attribute7;
414 l_rec.addr_attribute9 := p_addr_attribute9;
415 l_rec.addr_attribute10 := p_addr_attribute10;
416 l_rec.addr_attribute11 := p_addr_attribute11;
417 l_rec.addr_attribute12 := p_addr_attribute12;
418 l_rec.addr_attribute13 := p_addr_attribute13;
419 l_rec.addr_attribute14 := p_addr_attribute14;
420 l_rec.addr_attribute15 := p_addr_attribute15;
421 l_rec.addr_attribute16 := p_addr_attribute16;
422 l_rec.addr_attribute17 := p_addr_attribute17;
423 l_rec.addr_attribute18 := p_addr_attribute18;
424 l_rec.addr_attribute19 := p_addr_attribute19;
425 l_rec.addr_attribute20 := p_addr_attribute20;
426 l_rec.add_information13 := p_add_information13;
427 l_rec.add_information14 := p_add_information14;
428 l_rec.add_information15 := p_add_information15;
429 l_rec.add_information16 := p_add_information16;
430 l_rec.add_information17 := p_add_information17;
431 l_rec.add_information18 := p_add_information18;
432 l_rec.add_information19 := p_add_information19;
433 l_rec.add_information20 := p_add_information20;
434 l_rec.object_version_number := p_object_version_number;
435 l_rec.party_id := p_party_id; -- HR/TCA merge
436 --
437 -- Return the plsql record structure.
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 10);
440 Return(l_rec);
441 --
442 End convert_args;
443
444 --
445 -- ----------------------------------------------------------------------------
446 -- |----------------------------< derive_locale >-----------------------------|
447 -- ----------------------------------------------------------------------------
448 procedure derive_locale(p_rec in out nocopy per_add_shd.g_rec_type)
449 is
450 --
451 l_number_table dbms_describe.number_table;
452 l_varchar_table dbms_describe.varchar2_table;
453 l_package_exists boolean;
454 l_package_name varchar2(2000);
455 l_seperator varchar2(1);
456 --
457 begin
458 BEGIN
459 l_package_name := 'HR_'||substr(p_rec.style,1,2)||'_UTILITY.DERIVE_PER_ADD_ADDRESS';
460 -- Package name will be derived using the first 2 characters of the style
461 -- bug# 2803638
462 hr_general.describe_procedure(
463 object_name => l_package_name,
464 reserved1 => null,
465 reserved2 => null,
466 overload => l_number_table,
467 position => l_number_table,
468 level => l_number_table,
469 argument_name => l_varchar_table,
470 datatype => l_number_table,
471 default_value => l_number_table,
472 in_out => l_number_table,
473 length => l_number_table,
474 precision => l_number_table,
475 scale => l_number_table,
476 radix => l_number_table,
477 spare => l_number_table
478 );
479 -- If no exception raised at this point, then ready to concatenate address
480 -- fields using legislative package.
481 l_package_exists := true;
482 EXCEPTION
483 when others then
484 -- Package doesn't exist.
485 l_package_exists := false;
486 END;
487 IF l_package_exists THEN
488 l_package_name := 'begin ' ||l_package_name ||
489 '(:style, '||
490 ':address_line1,'||
491 ':address_line2,'||
492 ':address_line3,'||
493 ':country,'||
494 ':date_to,'||
495 ':postal_code,'||
496 ':region_1,'||
497 ':region_2,'||
498 ':region_3,'||
499 ':telephone_number_1,'||
500 ':telephone_number_2,'||
501 ':telephone_number_3,'||
502 ':town_or_city,'||
503 ':addr_attribute_category,'||
504 ':addr_attribute1,'||
505 ':addr_attribute2,'||
506 ':addr_attribute3,'||
507 ':addr_attribute4,'||
508 ':addr_attribute5,'||
509 ':addr_attribute6,'||
510 ':addr_attribute7,'||
511 ':addr_attribute8,'||
512 ':addr_attribute9,'||
513 ':addr_attribute10,'||
514 ':addr_attribute11,'||
518 ':addr_attribute15,'||
515 ':addr_attribute12,'||
516 ':addr_attribute13,'||
517 ':addr_attribute14,'||
519 ':addr_attribute16,'||
520 ':addr_attribute17,'||
521 ':addr_attribute18,'||
522 ':addr_attribute19,'||
523 ':addr_attribute20,'||
524 ':add_information13,'||
525 ':add_information14,'||
526 ':add_information15,'||
527 ':add_information16,'||
528 ':add_information17,'||
529 ':add_information18,'||
530 ':add_information19,'||
531 ':add_information20,'||
532 ':derived_locale); '||
533 'end;';
534 execute immediate l_package_name
535 using in p_rec.style,
536 in p_rec.address_line1,
537 in p_rec.address_line2,
538 in p_rec.address_line3,
539 in p_rec.country,
540 in p_rec.date_to,
541 in p_rec.postal_code,
542 in p_rec.region_1,
543 in p_rec.region_2,
544 in p_rec.region_3,
545 in p_rec.telephone_number_1,
546 in p_rec.telephone_number_2,
547 in p_rec.telephone_number_3,
548 in p_rec.town_or_city,
549 in p_rec.addr_attribute_category,
550 in p_rec.addr_attribute1,
551 in p_rec.addr_attribute2,
552 in p_rec.addr_attribute3,
553 in p_rec.addr_attribute4,
554 in p_rec.addr_attribute5,
555 in p_rec.addr_attribute6,
556 in p_rec.addr_attribute7,
557 in p_rec.addr_attribute8,
558 in p_rec.addr_attribute9,
559 in p_rec.addr_attribute10,
560 in p_rec.addr_attribute11,
561 in p_rec.addr_attribute12,
562 in p_rec.addr_attribute13,
563 in p_rec.addr_attribute14,
564 in p_rec.addr_attribute15,
565 in p_rec.addr_attribute16,
566 in p_rec.addr_attribute17,
567 in p_rec.addr_attribute18,
568 in p_rec.addr_attribute19,
569 in p_rec.addr_attribute20,
570 in p_rec.add_information13,
571 in p_rec.add_information14,
572 in p_rec.add_information15,
573 in p_rec.add_information16,
574 in p_rec.add_information17,
575 in p_rec.add_information18,
576 in p_rec.add_information19,
577 in p_rec.add_information20,
578 out p_rec.derived_locale;
579 ELSE
580 if (ltrim(p_rec.town_or_city) is null) OR
581 (ltrim(p_rec.country) is null) then
582 l_seperator := '';
583 else
584 l_seperator := ',';
585 end if;
586 p_rec.derived_locale := ltrim(p_rec.town_or_city) || l_seperator || ltrim(p_rec.country);
587 END IF;
588 end;
589
590
591 --
592 end per_add_shd;