[Home] [Help]
PACKAGE BODY: APPS.PER_SHT_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_sht_shd as
2 /* $Header: peshtrhi.pkb 120.1 2011/04/28 09:50:48 sidsaxen ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sht_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PER_SHARED_TYPES_PK') Then
22 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','5');
25 hr_utility.raise_error;
26 ElsIf (p_constraint_name = 'PER_SHARED_TYPE_UK1') Then
27 hr_utility.set_message(801, 'PER_9999_DUP_USR_TYP_CODE_COMB');
28 hr_utility.set_message_token('PROCEDURE', l_proc);
29 hr_utility.set_message_token('STEP','10');
30 hr_utility.raise_error;
31 Else
32 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
33 hr_utility.set_message_token('PROCEDURE', l_proc);
34 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
35 hr_utility.raise_error;
36 End If;
37 --
38 hr_utility.set_location(' Leaving:'||l_proc, 10);
39 End constraint_error;
40 --
41 -- ----------------------------------------------------------------------------
42 -- |-----------------------------< api_updating >-----------------------------|
43 -- ----------------------------------------------------------------------------
47 p_object_version_number in number
44 Function api_updating
45 (
46 p_shared_type_id in number,
48 ) Return Boolean Is
49 --
50 --
51 -- Cursor selects the 'current' row from the HR Schema
52 --
53 Cursor C_Sel1 is
54 select
55 shared_type_id,
56 business_group_id,
57 shared_type_name,
58 shared_type_code,
59 system_type_cd,
60 information1,
61 information2,
62 information3,
63 information4,
64 information5,
65 information6,
66 information7,
67 information8,
68 information9,
69 information10,
70 information11,
71 information12,
72 information13,
73 information14,
74 information15,
75 information16,
76 information17,
77 information18,
78 information19,
79 information20,
80 information21,
81 information22,
82 information23,
83 information24,
84 information25,
85 information26,
86 information27,
87 information28,
88 information29,
89 information30,
90 information_category,
91 object_version_number,
92 lookup_type
93 from per_shared_types
94 where shared_type_id = p_shared_type_id;
95 --
96 l_proc varchar2(72) := g_package||'api_updating';
97 l_fct_ret boolean;
98 --
99 Begin
100 hr_utility.set_location('Entering:'||l_proc, 5);
101 --
102 If (
103 p_shared_type_id is null and
104 p_object_version_number is null
105 ) Then
106 --
107 -- One of the primary key arguments is null therefore we must
108 -- set the returning function value to false
109 --
110 l_fct_ret := false;
111 Else
112 If (
113 p_shared_type_id = g_old_rec.shared_type_id and
114 p_object_version_number = g_old_rec.object_version_number
115 ) Then
116 hr_utility.set_location(l_proc, 10);
117 --
118 -- The g_old_rec is current therefore we must
119 -- set the returning function to true
120 --
121 l_fct_ret := true;
122 Else
123 --
124 -- Select the current row into g_old_rec
125 --
126 Open C_Sel1;
127 Fetch C_Sel1 Into g_old_rec;
128 If C_Sel1%notfound Then
129 Close C_Sel1;
130 --
131 -- The primary key is invalid therefore we must error
132 --
133 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
134 hr_utility.raise_error;
135 End If;
136 Close C_Sel1;
137 If (p_object_version_number <> g_old_rec.object_version_number) Then
138 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
139 hr_utility.raise_error;
140 End If;
141 hr_utility.set_location(l_proc, 15);
142 l_fct_ret := true;
143 End If;
144 End If;
145 hr_utility.set_location(' Leaving:'||l_proc, 20);
146 Return (l_fct_ret);
147 --
148 End api_updating;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |---------------------------------< lck >----------------------------------|
152 -- ----------------------------------------------------------------------------
153 Procedure lck
154 (
155 p_shared_type_id in number,
156 p_object_version_number in number
157 ) is
158 --
159 -- Cursor selects the 'current' row from the HR Schema
160 --
161 Cursor C_Sel1 is
162 select shared_type_id,
163 business_group_id,
164 shared_type_name,
165 shared_type_code,
166 system_type_cd,
167 information1,
168 information2,
169 information3,
170 information4,
171 information5,
172 information6,
173 information7,
174 information8,
175 information9,
176 information10,
177 information11,
178 information12,
179 information13,
180 information14,
181 information15,
182 information16,
183 information17,
184 information18,
185 information19,
186 information20,
187 information21,
188 information22,
189 information23,
190 information24,
191 information25,
192 information26,
193 information27,
194 information28,
195 information29,
196 information30,
197 information_category,
198 object_version_number,
199 lookup_type
200 from per_shared_types
201 where shared_type_id = p_shared_type_id
202 for update nowait;
203 --
204 l_proc varchar2(72) := g_package||'lck';
205 --
206 Begin
207 hr_utility.set_location('Entering:'||l_proc, 5);
208 --
209 -- Add any mandatory argument checking here:
210 -- Example:
211 -- hr_api.mandatory_arg_error
212 -- (p_api_name => l_proc,
213 -- p_argument => 'object_version_number',
214 -- p_argument_value => p_object_version_number);
215 --
216 Open C_Sel1;
217 Fetch C_Sel1 Into g_old_rec;
218 If C_Sel1%notfound then
219 Close C_Sel1;
220 --
221 -- The primary key is invalid therefore we must error
222 --
223 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
224 hr_utility.raise_error;
225 End If;
226 Close C_Sel1;
230 End If;
227 If (p_object_version_number <> g_old_rec.object_version_number) Then
228 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
229 hr_utility.raise_error;
231 --
232 hr_utility.set_location(' Leaving:'||l_proc, 10);
233 --
234 -- We need to trap the ORA LOCK exception
235 --
236 Exception
237 When HR_Api.Object_Locked then
238 --
239 -- The object is locked therefore we need to supply a meaningful
240 -- error message.
241 --
242 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
243 hr_utility.set_message_token('TABLE_NAME', 'per_shared_types');
244 hr_utility.raise_error;
245 End lck;
246 --
247 -- ----------------------------------------------------------------------------
248 -- |-----------------------------< convert_args >-----------------------------|
249 -- ----------------------------------------------------------------------------
250 Function convert_args
251 (
252 p_shared_type_id in number,
253 p_business_group_id in number,
254 p_shared_type_name in varchar2,
255 p_shared_type_code in varchar2,
256 p_system_type_cd in varchar2,
257 p_information1 in varchar2,
258 p_information2 in varchar2,
259 p_information3 in varchar2,
260 p_information4 in varchar2,
261 p_information5 in varchar2,
262 p_information6 in varchar2,
263 p_information7 in varchar2,
264 p_information8 in varchar2,
265 p_information9 in varchar2,
266 p_information10 in varchar2,
267 p_information11 in varchar2,
268 p_information12 in varchar2,
269 p_information13 in varchar2,
270 p_information14 in varchar2,
271 p_information15 in varchar2,
272 p_information16 in varchar2,
273 p_information17 in varchar2,
274 p_information18 in varchar2,
275 p_information19 in varchar2,
276 p_information20 in varchar2,
277 p_information21 in varchar2,
278 p_information22 in varchar2,
279 p_information23 in varchar2,
280 p_information24 in varchar2,
281 p_information25 in varchar2,
282 p_information26 in varchar2,
283 p_information27 in varchar2,
284 p_information28 in varchar2,
285 p_information29 in varchar2,
286 p_information30 in varchar2,
287 p_information_category in varchar2,
288 p_object_version_number in number,
289 p_lookup_type in varchar2
290 )
291 Return g_rec_type is
292 --
293 l_rec g_rec_type;
294 l_proc varchar2(72) := g_package||'convert_args';
295 --
296 Begin
297 --
298 hr_utility.set_location('Entering:'||l_proc, 5);
299 --
300 -- Convert arguments into local l_rec structure.
301 --
302 l_rec.shared_type_id := p_shared_type_id;
303 l_rec.business_group_id := p_business_group_id;
304 l_rec.shared_type_name := p_shared_type_name;
305 l_rec.shared_type_code := p_shared_type_code;
306 l_rec.system_type_cd := p_system_type_cd;
307 l_rec.information1 := p_information1;
308 l_rec.information2 := p_information2;
309 l_rec.information3 := p_information3;
310 l_rec.information4 := p_information4;
311 l_rec.information5 := p_information5;
312 l_rec.information6 := p_information6;
313 l_rec.information7 := p_information7;
314 l_rec.information8 := p_information8;
315 l_rec.information9 := p_information9;
316 l_rec.information10 := p_information10;
317 l_rec.information11 := p_information11;
318 l_rec.information12 := p_information12;
319 l_rec.information13 := p_information13;
320 l_rec.information14 := p_information14;
321 l_rec.information15 := p_information15;
322 l_rec.information16 := p_information16;
323 l_rec.information17 := p_information17;
324 l_rec.information18 := p_information18;
325 l_rec.information19 := p_information19;
326 l_rec.information20 := p_information20;
327 l_rec.information21 := p_information21;
328 l_rec.information22 := p_information22;
329 l_rec.information23 := p_information23;
330 l_rec.information24 := p_information24;
331 l_rec.information25 := p_information25;
332 l_rec.information26 := p_information26;
333 l_rec.information27 := p_information27;
334 l_rec.information28 := p_information28;
335 l_rec.information29 := p_information29;
336 l_rec.information30 := p_information30;
337 l_rec.information_category := p_information_category;
338 l_rec.object_version_number := p_object_version_number;
339 l_rec.lookup_type := p_lookup_type;
340 --
344 Return(l_rec);
341 -- Return the plsql record structure.
342 --
343 hr_utility.set_location(' Leaving:'||l_proc, 10);
345 --
346 End convert_args;
347 --
348 procedure load_row
349 (
350 p_shared_type_name in varchar2 default null
351 ,p_shared_type_code in varchar2 default null
352 ,p_system_type_cd in varchar2 default null
353 ,p_information1 in varchar2 default null
354 ,p_information2 in varchar2 default null
355 ,p_information3 in varchar2 default null
356 ,p_information4 in varchar2 default null
357 ,p_information5 in varchar2 default null
358 ,p_information6 in varchar2 default null
359 ,p_information7 in varchar2 default null
360 ,p_information8 in varchar2 default null
361 ,p_information9 in varchar2 default null
362 ,p_information10 in varchar2 default null
363 ,p_information11 in varchar2 default null
364 ,p_information12 in varchar2 default null
365 ,p_information13 in varchar2 default null
366 ,p_information14 in varchar2 default null
367 ,p_information15 in varchar2 default null
368 ,p_information16 in varchar2 default null
369 ,p_information17 in varchar2 default null
370 ,p_information18 in varchar2 default null
371 ,p_information19 in varchar2 default null
372 ,p_information20 in varchar2 default null
373 ,p_information21 in varchar2 default null
374 ,p_information22 in varchar2 default null
375 ,p_information23 in varchar2 default null
376 ,p_information24 in varchar2 default null
377 ,p_information25 in varchar2 default null
378 ,p_information26 in varchar2 default null
379 ,p_information27 in varchar2 default null
380 ,p_information28 in varchar2 default null
381 ,p_information29 in varchar2 default null
382 ,p_information30 in varchar2 default null
383 ,p_information_category in varchar2 default null
384 ,p_lookup_type in varchar2 default null
385 ,p_owner in varchar2 default 'CUSTOM'
386 ) is
387 cursor c1 is select userenv('LANG') from dual;
388
389 cursor c2 is select shared_type_id,last_updated_by from per_shared_types
390 where shared_type_code = p_shared_type_code
391 and lookup_type = p_lookup_type
392 and system_type_cd = p_system_type_cd
393 and business_group_id is null;
394
395 cursor csr_shared_name_match is select shared_type_id,last_updated_by from per_shared_types
396 where shared_type_name = p_shared_type_name
397 and lookup_type = p_lookup_type
398 and system_type_cd = p_system_type_cd
399 and business_group_id is null;
400
401 l_shared_type_id number;
402 l_last_update_login number;
403 l_last_update_date date;
404 l_last_updated_by number;
405 l_record_owner number;
406 l_rec_owner number;
407 l_created_by number;
408 l_creation_date date;
409 l_object_version_number number := 1;
410 l_effective_date date := sysdate;
411 l_language varchar2(30);
412
413 begin
414 if p_owner = 'SEED' then
415 l_created_by := 1;
416 l_last_updated_by := -1;
417 else
418 l_created_by := 0;
419 l_last_updated_by := 0;
420 end if;
421 l_last_update_login := 0;
422 l_creation_date := sysdate;
423 l_last_update_date := sysdate;
424
425 -- whether the key records exist or not
426 for i in c2 loop
427 l_shared_type_id := i.shared_type_id ;
428 l_record_owner := i.last_updated_by;
429 end loop;
430
431 if l_shared_type_id is not null then
432 -- Key record do exists , both the users , this is for clearing some old data
433 if l_record_owner in (-1,1) then
434 -- update the entry
435 update per_shared_types
436 set shared_type_name = p_shared_type_name
437 , information1 = p_information1
438 , information2 = p_information2
439 , information3 = p_information3
440 , information4 = p_information4
441 , information5 = p_information5
442 , information6 = p_information6
443 , information7 = p_information7
444 , information8 = p_information8
445 , information9 = p_information9
446 , information10 = p_information10
447 , information11 = p_information11
448 , information12 = p_information12
449 , information13 = p_information13
450 , information14 = p_information14
451 , information15 = p_information15
452 , information16 = p_information16
453 , information17 = p_information17
454 , information18 = p_information18
455 , information19 = p_information19
456 , information20 = p_information20
457 , information21 = p_information21
458 , information22 = p_information22
459 , information23 = p_information23
460 , information24 = p_information24
461 , information25 = p_information25
462 , information26 = p_information26
466 , information30 = p_information30
463 , information27 = p_information27
464 , information28 = p_information28
465 , information29 = p_information29
467 , information_category = p_information_category
468 , last_update_date = l_last_update_date
469 , last_updated_by = l_last_updated_by
470 , last_update_login = l_last_update_login
471 where shared_type_id = l_shared_type_id;
472
473 update per_shared_types_tl
474 set shared_type_name = p_shared_type_name
475 , last_update_date = l_last_update_date
476 , last_updated_by = l_last_updated_by
477 , last_update_login = l_last_update_login
478 , source_lang = userenv('LANG')
479 where shared_type_id = l_shared_type_id
480 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
481 if sql%notfound then
482 insert into per_shared_types_tl
483 (shared_type_id,
484 shared_type_name,
485 language,
486 source_lang,
487 creation_date,
488 created_by,
489 last_updated_by,
490 last_update_date,
491 last_update_login)
492 select l_shared_type_id,
493 p_shared_type_name,
494 l.language_code,
495 userenv('LANG'),
496 l_creation_date,
497 l_created_by,
498 l_last_updated_by,
499 l_last_update_date,
500 l_last_update_login from fnd_languages l
501 where l.installed_flag in ('I','B')
502 and not exists (select null from per_shared_types_tl t
503 where t.shared_type_id = l_shared_type_id
504 and t.language = l.language_code);
505 end if;
506 else
507 -- record has been updated by custom
508 null;
509 end if;
510 else
511 -- key record does not exist.
512 -- check whether lookup_type and system_type_cd combination exists for
513 -- the shared_type_name
514 open csr_shared_name_match;
515 fetch csr_shared_name_match into l_shared_type_id,l_rec_owner;
516 if csr_shared_name_match%found then
517 -- shared_type_name exists for combination
518 if l_rec_owner in (-1,1) then
519 -- the owner is seed , update the shared_type_code and other info.
520 update per_shared_types
521 set shared_type_code = p_shared_type_code
522 , information1 = p_information1
523 , information2 = p_information2
524 , information3 = p_information3
525 , information4 = p_information4
526 , information5 = p_information5
527 , information6 = p_information6
528 , information7 = p_information7
529 , information8 = p_information8
530 , information9 = p_information9
531 , information10 = p_information10
532 , information11 = p_information11
533 , information12 = p_information12
534 , information13 = p_information13
535 , information14 = p_information14
536 , information15 = p_information15
537 , information16 = p_information16
538 , information17 = p_information17
539 , information18 = p_information18
540 , information19 = p_information19
541 , information20 = p_information20
542 , information21 = p_information21
543 , information22 = p_information22
544 , information23 = p_information23
545 , information24 = p_information24
546 , information25 = p_information25
547 , information26 = p_information26
548 , information27 = p_information27
549 , information28 = p_information28
550 , information29 = p_information29
551 , information30 = p_information30
552 , information_category = p_information_category
553 , last_update_date = l_last_update_date
554 , last_updated_by = l_last_updated_by
555 , last_update_login = l_last_update_login
556 where shared_type_id = l_shared_type_id;
557
558 update per_shared_types_tl
559 set shared_type_name = p_shared_type_name
560 , last_update_date = l_last_update_date
561 , last_updated_by = l_last_updated_by
562 , last_update_login = l_last_update_login
563 , source_lang = userenv('LANG')
564 where shared_type_id = l_shared_type_id
565 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
566 if sql%notfound then
567 insert into per_shared_types_tl
568 (shared_type_id,
569 shared_type_name,
570 language,
571 source_lang,
572 creation_date,
573 created_by,
574 last_updated_by,
575 last_update_date,
576 last_update_login)
577 select l_shared_type_id,
578 p_shared_type_name,
579 l.language_code,
580 userenv('LANG'),
581 l_creation_date,
582 l_created_by,
583 l_last_updated_by,
584 l_last_update_date,
585 l_last_update_login from fnd_languages l
586 where l.installed_flag in ('I','B')
587 and not exists (select null from per_shared_types_tl t
588 where t.shared_type_id = l_shared_type_id
589 and t.language = l.language_code);
590 else
591 -- record has been updated by custom, update the share_type_code only.
592 update per_shared_types
593 set shared_type_code = p_shared_type_code
594 where shared_type_id = l_shared_type_id;
595 end if;
596 end if;
597 else
598 -- neither key record, nor lookup_type and system_type_cd combination exists
599 -- creating a new record for share_type
600
601 select per_shared_types_s.nextval into l_shared_type_id from dual;
602 insert into per_shared_types (shared_type_id,
603 shared_type_name,
604 shared_type_code,
605 lookup_type,
606 system_type_cd,
607 information1,
608 information2,
609 information3,
610 information4,
611 information5,
612 information6,
613 information7,
614 information8,
615 information9,
616 information10,
617 information11,
618 information12,
619 information13,
620 information14,
621 information15,
622 information16,
623 information17,
624 information18,
625 information19,
626 information20,
627 information21,
628 information22,
629 information23,
630 information24,
631 information25,
632 information26,
633 information27,
634 information28,
635 information29,
636 information30,
637 information_category,
638 last_updated_by,
639 created_by,
640 last_update_login,
641 creation_date,
642 last_update_date,
643 object_version_number
644 )
645 values
646 (l_shared_type_id,
647 p_shared_type_name,
648 p_shared_type_code,
649 p_lookup_type,
650 p_system_type_cd,
651 p_information1,
652 p_information2,
653 p_information3,
654 p_information4,
655 p_information5,
656 p_information6,
657 p_information7,
658 p_information8,
659 p_information9,
660 p_information10,
661 p_information11,
662 p_information12,
663 p_information13,
664 p_information14,
665 p_information15,
666 p_information16,
667 p_information17,
668 p_information18,
669 p_information19,
670 p_information20,
671 p_information21,
672 p_information22,
673 p_information23,
674 p_information24,
675 p_information25,
676 p_information26,
677 p_information27,
678 p_information28,
679 p_information29,
680 p_information30,
681 p_information_category,
682 l_last_updated_by,
683 l_created_by,
684 l_last_update_login,
685 l_creation_date,
686 l_last_update_date,
687 l_object_version_number);
688 insert into per_shared_types_tl
689 (shared_type_id,
690 shared_type_name,
691 language,
692 source_lang,
693 creation_date,
694 created_by,
695 last_updated_by,
696 last_update_date,
697 last_update_login)
698 select l_shared_type_id,
699 p_shared_type_name,
700 l.language_code,
701 userenv('LANG'),
702 l_creation_date,
703 l_created_by,
704 l_last_updated_by,
705 l_last_update_date,
706 l_last_update_login from fnd_languages l
707 where l.installed_flag in ('I','B')
708 and not exists (select null from per_shared_types_tl t
709 where t.shared_type_id = l_shared_type_id
710 and t.language = l.language_code);
711 end if;
712 end if;
713 end load_row;
714 --
715 end per_sht_shd;