DBA Data[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;