[Home] [Help]
PACKAGE BODY: APPS.PER_CONTACT_INFO_TYPES_PKG
Source
1 PACKAGE BODY per_contact_info_types_pkg AS
2 /* $Header: pecit01t.pkb 115.6 2002/12/04 12:18:16 pkakar noship $ */
3 -- +-------------------------------------------------------------------------+
4 -- | Global variables |
5 -- +-------------------------------------------------------------------------+
6 g_business_group_id NUMBER(15);
7 g_legislation_code VARCHAR2(150);
8 g_dummy NUMBER(1);
9 -- ---------------------------------------------------------------------------
10 -- |-----------------------< chk_active_inactive_flag >----------------------|
11 -- ---------------------------------------------------------------------------
12 -- {Start Of Comments}
13 --
14 -- Description:
15 -- Validates that active_inactive_flag is 'Y' or 'N'.
16 --
17 -- Prerequisites:
18 -- None.
19 --
20 -- In Parameters:
21 -- Name Reqd Type Description
22 -- x_active_inactive_flag Yes VARCHAR2 Active or Inactive
23 -- Flag.
24 --
25 -- Out Parameters:
26 -- None.
27 --
28 -- Post Success:
29 -- The process succeeds.
30 --
31 -- Post Failure:
32 -- The process will be terminated.
33 --
34 -- Developer Implementation Notes:
35 -- None.
36 --
37 -- Access Status:
38 -- Public.
39 --
40 -- {End Of Comments}
41 -- ---------------------------------------------------------------------------
42 PROCEDURE chk_active_inactive_flag(
43 x_active_inactive_flag IN per_contact_info_types.active_inactive_flag%TYPE) IS
44 BEGIN
45
46 -- = Raise an error when specified active_inactive_flag is not 'Y' or 'N'.
47 IF x_active_inactive_flag NOT IN ('Y','N') THEN
48 --
49 hr_utility.set_message(
50 applid => 800,
51 l_message_name => 'PER_52500_INV_YES_NO_FLAG');
52 --
53 hr_utility.set_message_token(
54 l_token_name => 'YES_NO_FLAG',
55 l_token_value => 'active_inactive_flag');
56 --
57 hr_utility.raise_error;
58 END IF;
59 -- =
60
61 END chk_active_inactive_flag;
62 --
63 -- ---------------------------------------------------------------------------
64 -- |---------------------< chk_multiple_occurences_flag >--------------------|
65 -- ---------------------------------------------------------------------------
66 -- {Start Of Comments}
67 --
68 -- Description:
69 -- Validates that multiple_occurences_flag is 'Y' or 'N'.
70 --
71 -- Prerequisites:
72 -- None.
73 --
74 -- In Parameters:
75 -- Name Reqd Type Description
76 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
77 -- Flag.
78 --
79 -- Out Parameters:
80 -- None.
81 --
82 -- Post Success:
83 -- The process succeeds.
84 --
85 -- Post Failure:
86 -- The process will be terminated.
87 --
88 -- Developer Implementation Notes:
89 -- None.
90 --
91 -- Access Status:
92 -- Public.
93 --
94 -- {End Of Comments}
95 -- ---------------------------------------------------------------------------
96 PROCEDURE chk_multiple_occurences_flag(
97 x_multiple_occurences_flag IN per_contact_info_types.multiple_occurences_flag%TYPE) IS
98 BEGIN
99
100 -- = Raise an error when specified multiple_occurences_flag is not 'Y' or 'N'.
101 IF x_multiple_occurences_flag NOT IN ('Y','N') THEN
102 --
103 hr_utility.set_message(
104 applid => 800,
105 l_message_name => 'PER_52500_INV_YES_NO_FLAG');
106 --
107 hr_utility.set_message_token(
108 l_token_name => 'YES_NO_FLAG',
109 l_token_value => 'multiple_occurences_flag');
110 --
111 hr_utility.raise_error;
112 END IF;
113 -- =
114
115 END chk_multiple_occurences_flag;
116 -- ---------------------------------------------------------------------------
117 -- |---------------------< chk_update_mltpl_occrncs_flg >--------------------|
118 -- ---------------------------------------------------------------------------
119 -- {Start Of Comments}
120 --
121 -- Description:
122 -- Error when multiple_occurences_flag is updated to 'Y' from 'N'.
123 --
124 -- Prerequisites:
125 -- This procedure must be called after chk_multiple_occurences_flag before
126 -- the update DML.
127 --
128 -- In Parameters:
129 -- Name Reqd Type Description
130 -- x_information_type Yes VARCHAR2 Information Type.
131 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
132 -- Flag.
133 --
134 -- Out Parameters:
135 -- None.
136 --
137 -- Post Success:
138 -- The process succeeds.
139 --
140 -- Post Failure:
141 -- The process will be terminated.
142 --
143 -- Developer Implementation Notes:
144 -- None.
145 --
146 -- Access Status:
147 -- Public.
148 --
149 -- {End Of Comments}
150 -- ---------------------------------------------------------------------------
151 PROCEDURE chk_update_mltpl_occrncs_flg(
152 x_information_type IN per_contact_info_types.information_type%TYPE,
153 x_multiple_occurences_flag IN per_contact_info_types.multiple_occurences_flag%TYPE) IS
154 --
155 CURSOR cel_old_value(
156 p_information_type IN per_contact_info_types.information_type%TYPE) IS
157 SELECT multiple_occurences_flag
158 FROM per_contact_info_types
159 WHERE information_type = p_information_type;
160 --
161 l_old_value per_contact_info_types.multiple_occurences_flag%TYPE;
162 --
163 BEGIN
164
165 -- = Validate only when multiple_occurences_flag is updated to 'Y'.
166 IF x_multiple_occurences_flag = 'Y' THEN
167 --
168 OPEN cel_old_value(x_information_type);
169 FETCH cel_old_value INTO l_old_value;
170
171 -- == Raise error when the original multiple_occurences_flag value is 'N'.
172 IF l_old_value = 'N' THEN
173 --
174 CLOSE cel_old_value;
175 --
176 hr_utility.set_message(
177 applid => 800,
178 l_message_name => 'PER_50048_UPD_MULTI_OCCRNCS_NO');
179 --
180 hr_utility.raise_error;
181 --
182 END IF;
183 -- ==
184
185 CLOSE cel_old_value;
186 --
187 END IF;
188 -- =
189
190 END chk_update_mltpl_occrncs_flg;
191 --
192 -- ---------------------------------------------------------------------------
193 -- |------------------------------< insert_row >-----------------------------|
194 -- ---------------------------------------------------------------------------
195 -- {Start Of Comments}
196 --
197 -- Description:
198 -- Create a contact information type.
199 --
200 -- Prerequisites:
201 -- None.
202 --
203 -- In Parameters:
204 -- Name Reqd Type Description
205 -- x_rowid Yes VARCHAR2 Row ID.
206 -- x_information_type Yes VARCHAR2 Contact Information
207 -- Type.
208 -- x_active_inactive_flag Yes VARCHAR2 Active or Inactive
209 -- Flag.
210 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
211 -- Flag.
212 -- x_legislation_code Yes VARCHAR2 Legislation Code.
213 -- x_description Yes VARCHAR2 Description.
214 -- x_last_update_date Yes DATE Last Update Date.
215 -- x_last_updated_by Yes NUMBER User ID who last
216 -- updates the row.
217 -- x_last_update_login Yes NUMBER Login ID who last
218 -- updates the row.
219 -- x_created_by Yes NUMBER User ID who creates
220 -- the row.
221 -- x_creation_date Yes DATE Creation Date.
222 -- x_request_id Yes NUMBER Request ID that
223 -- updates the row.
224 -- x_program_application_id Yes NUMBER Application ID to which
225 -- the concurrent program
226 -- that updates the row
227 -- belongs.
228 -- x_program_id Yes NUMBER Concurrent Program ID
229 -- that updates the row.
230 -- x_program_update_date Yes DATE Date concurrent program
231 -- updates the row.
232 -- x_object_version_number Yes NUMBER Version number of the
233 -- row.
234 --
235 -- Out Parameters:
236 -- Name Type Description
237 -- p_row_id VARCHAR2 Row ID.
238 --
239 -- Post Success:
240 -- The process succeeds.
241 --
242 -- Post Failure:
243 -- The process will be terminated.
244 --
245 -- Developer Implementation Notes:
246 -- None.
247 --
248 -- Access Status:
249 -- Public.
250 --
251 -- {End Of Comments}
252 -- ---------------------------------------------------------------------------
253 PROCEDURE insert_row(
254 x_rowid IN OUT NOCOPY VARCHAR2,
255 x_information_type IN VARCHAR2,
256 x_active_inactive_flag IN VARCHAR2,
257 x_multiple_occurences_flag IN VARCHAR2,
258 x_legislation_code IN VARCHAR2,
259 x_description IN VARCHAR2,
260 x_last_update_date IN DATE,
261 x_last_updated_by IN NUMBER,
262 x_last_update_login IN NUMBER,
263 x_created_by IN NUMBER,
264 x_creation_date IN DATE,
265 x_request_id IN NUMBER,
266 x_program_application_id IN NUMBER,
267 x_program_id IN NUMBER,
268 x_program_update_date IN DATE,
269 x_object_version_number IN NUMBER) IS
270 --
271 CURSOR c IS
272 SELECT ROWID
273 FROM per_contact_info_types
274 WHERE information_type = x_information_type;
275 --
276 BEGIN
277 --
278 chk_active_inactive_flag(
279 x_active_inactive_flag => x_active_inactive_flag);
280 --
281 chk_multiple_occurences_flag(
282 x_multiple_occurences_flag => x_multiple_occurences_flag);
283 --
284 INSERT INTO per_contact_info_types(
285 information_type,
286 active_inactive_flag,
287 multiple_occurences_flag,
288 legislation_code,
289 -- last_update_date,
290 -- last_updated_by,
291 -- last_update_login,
292 -- created_by,
293 -- creation_date,
294 -- request_id,
295 -- program_application_id,
296 -- program_id,
297 -- program_update_date,
298 object_version_number)
299 VALUES(
300 x_information_type,
301 x_active_inactive_flag,
302 x_multiple_occurences_flag,
303 x_legislation_code,
304 -- x_last_update_date,
305 -- x_last_updated_by,
306 -- x_last_update_login,
307 -- x_created_by,
308 -- x_creation_date,
309 -- x_request_id,
310 -- x_program_application_id,
311 -- x_program_id,
312 -- x_program_update_date,
313 x_object_version_number);
314 --
315 INSERT INTO per_contact_info_types_tl(
316 information_type,
317 language,
318 source_lang,
319 description,
320 last_update_date,
321 last_updated_by,
322 last_update_login,
323 created_by,
324 creation_date)
325 SELECT
326 x_information_type,
327 l.language_code,
328 USERENV('LANG'),
329 x_description,
330 x_last_update_date,
331 x_last_updated_by,
332 x_last_update_login,
333 x_created_by,
334 x_creation_date
335 FROM fnd_languages l
336 WHERE l.installed_flag IN ('I','B')
337 AND NOT EXISTS(
338 SELECT NULL
339 FROM per_contact_info_types_tl t
340 WHERE t.information_type = x_information_type
341 AND t.language = l.language_code);
342 --
343 OPEN c;
344 FETCH c INTO x_rowid;
345
346 -- = Raise error if the insert to the base table fails.
347 IF c%NOTFOUND THEN
348 close c;
349 RAISE NO_DATA_FOUND;
350 END IF;
351 -- =
352
353 CLOSE c;
354 END insert_row;
355 -- ---------------------------------------------------------------------------
356 -- |-------------------------------< lock_row >------------------------------|
357 -- ---------------------------------------------------------------------------
358 -- {Start Of Comments}
359 --
360 -- Description:
361 -- Lock a contact information type.
362 --
363 -- Prerequisites:
364 -- None.
365 --
366 -- In Parameters:
367 -- Name Reqd Type Description
368 -- x_information_type Yes VARCHAR2 Contact Information
369 -- Type.
370 -- x_active_inactive_flag Yes VARCHAR2 Active or Inactive
371 -- Flag.
372 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
373 -- Flag.
374 -- x_legislation_code Yes VARCHAR2 Legislation Code.
375 -- x_description Yes VARCHAR2 Description.
376 -- x_object_version_number Yes NUMBER Version number of the
377 -- row.
378 --
379 -- Out Parameters:
380 -- None.
381 --
382 -- Post Success:
383 -- The process succeeds.
384 --
385 -- Post Failure:
386 -- The process will be terminated.
387 --
388 -- Developer Implementation Notes:
389 -- None.
390 --
391 -- Access Status:
392 -- Public.
393 --
394 -- {End Of Comments}
395 -- ---------------------------------------------------------------------------
396 PROCEDURE lock_row(
397 x_information_type IN VARCHAR2,
398 x_active_inactive_flag IN VARCHAR2,
399 x_multiple_occurences_flag IN VARCHAR2,
400 x_legislation_code IN VARCHAR2,
401 x_description IN VARCHAR2,
402 x_object_version_number IN NUMBER) IS
403 --
404 CURSOR c IS
405 SELECT
406 active_inactive_flag,
407 multiple_occurences_flag,
408 legislation_code,
412 FOR UPDATE OF information_type NOWAIT;
409 object_version_number
410 FROM per_contact_info_types
411 WHERE information_type = x_information_type
413 --
414 recinfo c%ROWTYPE;
415 --
416 CURSOR c1 IS
417 SELECT
418 description,
419 DECODE(language,USERENV('LANG'),'Y',
420 'N') baselang
421 FROM per_contact_info_types_tl
422 WHERE information_type = x_information_type
423 AND USERENV('LANG') IN (language,source_lang)
424 FOR UPDATE OF information_type NOWAIT;
425 --
426 BEGIN
427 OPEN c;
428 FETCH c INTO recinfo;
429
430 -- = Raise error when specified information type does not exist in the base table.
431 IF c%NOTFOUND THEN
432 CLOSE c;
433 --
434 fnd_message.set_name(
435 application => 'FND',
436 name => 'FORM_RECORD_DELETED');
437 --
438 app_exception.raise_exception;
439 END IF;
440 -- =
441
442 CLOSE c;
443
444 -- = Raise error when record is updated by validating object version number.
445 IF x_object_version_number <> recinfo.object_version_number THEN
446 --
447 fnd_message.set_name(
448 application => 'PAY',
449 name => 'HR_7155_OBJECT_INVALID');
450 --
451 fnd_message.raise_error;
452 END IF;
453 -- =
454
455 -- = Raise error when at least one of active_inactive_flag, multiple_occurences_flag,
456 -- = object_version_number, legislation_code is updated.
457 IF recinfo.active_inactive_flag = x_active_inactive_flag
458 AND recinfo.multiple_occurences_flag = x_multiple_occurences_flag
459 AND NVL(recinfo.legislation_code,'x') = NVL(x_legislation_code,'x')
460 AND NVL(recinfo.object_version_number,0) = NVL(x_object_version_number,0) THEN
461 --
462 NULL;
463 ELSE
464 --
465 fnd_message.set_name(
466 application => 'FND',
467 name => 'FORM_RECORD_CHANGED');
468 --
469 app_exception.raise_exception;
470 END IF;
471 -- =
472
473 FOR tlinfo IN c1 LOOP
474
475 -- = Check if description is updated for base language.
476 IF tlinfo.baselang = 'Y' THEN
477
478 -- == Raise error when description is updated.
479 IF tlinfo.description = x_description
480 OR (tlinfo.description IS NULL
481 AND x_description IS NULL) THEN
482 --
483 NULL;
484 ELSE
485 --
486 fnd_message.set_name(
487 application => 'FND',
488 name => 'FORM_RECORD_CHANGED');
489 --
490 app_exception.raise_exception;
491 END IF;
492 -- ==
493
494 END IF;
495 -- =
496
497 END LOOP;
498 RETURN;
499 END lock_row;
500 -- ---------------------------------------------------------------------------
501 -- |------------------------------< update_row >-----------------------------|
502 -- ---------------------------------------------------------------------------
503 -- {Start Of Comments}
504 --
505 -- Description:
506 -- Update a contact information type.
507 --
508 -- Prerequisites:
509 -- None.
510 --
511 -- In Parameters:
512 -- Name Reqd Type Description
513 -- x_information_type Yes VARCHAR2 Contact Information
514 -- Type.
515 -- x_active_inactive_flag Yes VARCHAR2 Active or Inactive
516 -- Flag.
517 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
518 -- Flag.
519 -- x_legislation_code Yes VARCHAR2 Legislation Code.
520 -- x_description Yes VARCHAR2 Description.
521 -- x_last_update_date Yes DATE Last Update Date.
522 -- x_last_updated_by Yes NUMBER User ID who last
523 -- updates the row.
524 -- x_last_update_login Yes NUMBER Login ID who last
525 -- updates the row.
526 -- x_request_id Yes NUMBER Request ID that
527 -- updates the row.
528 -- x_program_application_id Yes NUMBER Application ID to which
529 -- the concurrent program
530 -- that updates the row
531 -- belongs.
532 -- x_program_id Yes NUMBER Concurrent Program ID
533 -- that updates the row.
534 -- x_program_update_date Yes DATE Date concurrent program
535 -- updates the row.
536 -- x_object_version_number Yes NUMBER Version number of the
537 -- row.
538 --
539 -- Out Parameters:
540 -- Name Type Description
544 -- Post Success:
541 -- x_object_version_number NUMBER Set to the version number of this
542 -- contact information type.
543 --
545 -- The process succeeds.
546 --
547 -- Post Failure:
548 -- The process will be terminated.
549 --
550 -- Developer Implementation Notes:
551 -- None.
552 --
553 -- Access Status:
554 -- Public.
555 --
556 -- {End Of Comments}
557 -- ---------------------------------------------------------------------------
558 PROCEDURE update_row(
559 x_information_type IN VARCHAR2,
560 x_active_inactive_flag IN VARCHAR2,
561 x_multiple_occurences_flag IN VARCHAR2,
562 x_legislation_code IN VARCHAR2,
563 x_description IN VARCHAR2,
564 x_last_update_date IN DATE,
565 x_last_updated_by IN NUMBER,
566 x_last_update_login IN NUMBER,
567 x_request_id IN NUMBER,
568 x_program_application_id IN NUMBER,
569 x_program_id IN NUMBER,
570 x_program_update_date IN DATE,
571 x_object_version_number IN OUT NOCOPY NUMBER) IS
572 --
573 CURSOR get_object_version_number IS
574 SELECT NVL(MAX(object_version_number),0)+1
575 FROM per_contact_info_types
576 WHERE information_type = x_information_type;
577 --
578 l_ovn NUMBER;
579 BEGIN
580 --
581 chk_active_inactive_flag(
582 x_active_inactive_flag => x_active_inactive_flag);
583 --
584 chk_multiple_occurences_flag(
585 x_multiple_occurences_flag => x_multiple_occurences_flag);
586 --
587 chk_update_mltpl_occrncs_flg(
588 x_information_type => x_information_type,
589 x_multiple_occurences_flag => x_multiple_occurences_flag);
590 --
591 OPEN get_object_version_number;
592 FETCH get_object_version_number INTO l_ovn;
593 CLOSE get_object_version_number;
594 --
595 UPDATE per_contact_info_types
596 SET
597 active_inactive_flag = x_active_inactive_flag,
598 multiple_occurences_flag = x_multiple_occurences_flag,
599 legislation_code = x_legislation_code,
600 object_version_number = l_ovn
601 -- last_update_date = x_last_update_date,
602 -- last_updated_by = x_last_updated_by,
603 -- last_update_login = x_last_update_login,
604 -- request_id = x_request_id,
605 -- program_application_id = x_program_application_id,
606 -- program_id = x_program_id,
607 -- program_update_date = x_program_update_date
608 WHERE information_type = x_information_type;
609 --
610
611 -- = Raise error when the specified information type does not exist.
612 IF SQL%NOTFOUND THEN
613 RAISE NO_DATA_FOUND;
614 END IF;
615 -- =
616
617 --
618 UPDATE per_contact_info_types_tl
619 SET
620 description = x_description,
621 last_update_date = x_last_update_date,
622 last_updated_by = x_last_updated_by,
623 last_update_login = x_last_update_login,
624 source_lang = USERENV('LANG')
625 WHERE information_type = x_information_type
626 AND USERENV('LANG') IN (language,source_lang);
627 --
628
629 -- = Raise error when the row for the language of user's environment does
630 -- = not exist.
631 IF SQL%NOTFOUND THEN
632 RAISE NO_DATA_FOUND;
633 END IF;
634 -- =
635
636 x_object_version_number := l_ovn;
637 END update_row;
638 --
639 -- ---------------------------------------------------------------------------
640 -- |------------------------------< delete_row >-----------------------------|
641 -- ---------------------------------------------------------------------------
642 -- {Start Of Comments}
643 --
644 -- Description:
645 -- Delete a contact information type.
646 --
647 -- Prerequisites:
648 -- None.
649 --
650 -- In Parameters:
651 -- Name Reqd Type Description
652 -- x_information_type Yes VARCHAR2 Contact Information
653 -- Type.
654 --
655 -- Out Parameters:
656 -- None.
657 --
658 -- Post Success:
659 -- The process succeeds.
660 --
661 -- Post Failure:
662 -- The process will be terminated.
663 --
664 -- Developer Implementation Notes:
665 -- None.
666 --
667 -- Access Status:
668 -- Public.
669 --
670 -- {End Of Comments}
671 -- ---------------------------------------------------------------------------
672 PROCEDURE delete_row(
673 x_information_type IN VARCHAR2) IS
674 BEGIN
675 --
676 DELETE FROM per_contact_extra_info_f
677 WHERE information_type = x_information_type;
678 --
679 DELETE FROM per_contact_info_types_tl
680 WHERE information_type = x_information_type;
681 --
682
683 -- = Raise error when the specified information type does not exist.
684 IF SQL%NOTFOUND THEN
685 RAISE NO_DATA_FOUND;
686 END IF;
687 -- =
688
689 --
690 DELETE FROM per_contact_info_types
691 WHERE information_type = x_information_type;
692 --
693
697 RAISE NO_DATA_FOUND;
694 -- = Raise error when the row for the language of user's environment does
695 -- = not exist.
696 IF SQL%NOTFOUND THEN
698 END IF;
699 -- =
700
701 END delete_row;
702 -- ---------------------------------------------------------------------------
703 -- |-------------------------------< load_row >------------------------------|
704 -- ---------------------------------------------------------------------------
705 -- {Start Of Comments}
706 --
707 -- Description:
708 -- Update or insert row as appropriate.
709 --
710 -- Prerequisites:
711 -- None.
712 --
713 -- In Parameters:
714 -- Name Reqd Type Description
715 -- x_information_type Yes VARCHAR2 Contact Information
716 -- Type.
717 -- x_active_inactive_flag Yes VARCHAR2 Active or Inactive
718 -- Flag.
719 -- x_multiple_occurences_flag Yes VARCHAR2 Multiple Occurrences
720 -- Flag.
721 -- x_description Yes VARCHAR2 Description.
722 -- x_legislation_code Yes VARCHAR2 Legislation Code.
723 -- x_object_version_number Yes NUMBER Version number of the
724 -- row.
725 -- x_owner Yes VARCHAR2 'SEED' or 'CUSTOM'.
726 --
727 -- Out Parameters:
728 -- None.
729 --
730 -- Post Success:
731 -- The process succeeds. No parameters are returned.
732 --
733 -- Post Failure:
734 -- None.
735 --
736 -- Developer Implementation Notes:
737 -- This procedure is called from percit.lct.
738 --
739 -- Access Status:
740 -- Public.
741 --
742 -- {End Of Comments}
743 -- ---------------------------------------------------------------------------
744 PROCEDURE load_row(
745 x_information_type IN VARCHAR2,
746 x_active_inactive_flag IN VARCHAR2,
747 x_multiple_occurences_flag IN VARCHAR2,
748 x_description IN VARCHAR2,
749 x_legislation_code IN VARCHAR2,
750 x_object_version_number IN NUMBER,
751 x_owner IN VARCHAR2) IS
752 --
753 l_proc VARCHAR2(61) := 'PER_CONTACT_INFO_TYPES_PKG.LOAD_ROW';
754 l_rowid ROWID;
755 l_request_id per_contact_info_types.request_id%TYPE;
756 l_program_application_id per_contact_info_types.program_application_id%TYPE;
757 l_program_id per_contact_info_types.program_id%TYPE;
758 l_program_update_date per_contact_info_types.program_update_date%TYPE;
759 l_created_by per_contact_info_types.created_by%TYPE := 0;
760 l_creation_date per_contact_info_types.creation_date%TYPE := SYSDATE;
761 l_last_update_date per_contact_info_types.last_update_date%TYPE := SYSDATE;
762 l_last_updated_by per_contact_info_types.last_updated_by%TYPE := 0;
763 l_last_update_login per_contact_info_types.last_update_login%TYPE := 0;
764 l_object_version_number per_contact_info_types.object_version_number%TYPE;
765 --
766 BEGIN
767 --
768 -- Translate developer keys to internal parameters.
769 --
770 IF x_owner = 'SEED' THEN
771 --
772 l_created_by := 1;
773 l_last_updated_by := 1;
774 --
775 END IF;
776 --
777 -- Update or insert row as appropriate.
778 --
779 BEGIN
780 --
781 l_object_version_number := x_object_version_number;
782 --
786 x_multiple_occurences_flag => x_multiple_occurences_flag,
783 update_row(
784 x_information_type => x_information_type,
785 x_active_inactive_flag => x_active_inactive_flag,
787 x_legislation_code => x_legislation_code,
788 x_description => x_description,
789 x_last_update_date => l_last_update_date,
790 x_last_updated_by => l_last_updated_by,
791 x_last_update_login => l_last_update_login,
792 x_request_id => l_request_id,
793 x_program_application_id => l_program_application_id,
794 x_program_id => l_program_id,
795 x_program_update_date => l_program_update_date,
796 x_object_version_number => l_object_version_number);
797 --
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 --
801 insert_row(
802 x_rowid => l_rowid,
803 x_information_type => x_information_type,
804 x_active_inactive_flag => x_active_inactive_flag,
805 x_multiple_occurences_flag => x_multiple_occurences_flag,
806 x_legislation_code => x_legislation_code,
807 x_description => x_description,
808 x_last_update_date => l_last_update_date,
809 x_last_updated_by => l_last_updated_by,
810 x_last_update_login => l_last_update_login,
811 x_created_by => l_created_by,
812 x_creation_date => l_creation_date,
813 x_request_id => l_request_id,
814 x_program_application_id => l_program_application_id,
815 x_program_id => l_program_id,
816 x_program_update_date => l_program_update_date,
817 x_object_version_number => x_object_version_number);
818 --
819 END;
820 END load_row;
821 -- ---------------------------------------------------------------------------
822 -- |---------------------------< translate_row >-----------------------------|
823 -- ---------------------------------------------------------------------------
824 -- {Start Of Comments}
825 --
826 -- Description:
827 -- Update translatable column.
828 --
829 -- Prerequisites:
830 -- None.
831 --
832 -- In Parameters:
833 -- Name Reqd Type Description
834 -- x_information_type Yes VARCHAR2 Contact Information
835 -- Type.
836 -- x_description Yes VARCHAR2 Description.
837 -- x_owner Yes VARCHAR2 'SEED' or 'CUSTOM'.
838 --
839 -- Out Parameters:
840 -- None.
841 --
842 -- Post Success:
843 -- The process succeeds. No parameters are returned.
844 --
845 -- Post Failure:
846 -- None.
847 --
848 -- Developer Implementation Notes:
849 -- This procedure is called from pecit.lct.
850 --
851 -- Access Status:
852 -- Public.
853 --
854 -- {End Of Comments}
855 -- ---------------------------------------------------------------------------
856 PROCEDURE translate_row(
857 x_information_type IN VARCHAR2,
858 x_description IN VARCHAR2,
859 x_owner IN VARCHAR2) IS
860 BEGIN
861 --
862 UPDATE per_contact_info_types_tl SET
863 description = x_description,
864 last_update_date = SYSDATE,
865 last_updated_by = DECODE(x_owner,'SEED',1,0),
866 last_update_login = 0,
867 source_lang = USERENV('lang')
868 WHERE userenv('lang') IN (language,source_lang)
869 AND information_type = x_information_type;
870 --
871 END translate_row;
872 -- ---------------------------------------------------------------------------
873 -- |----------------------------< add_language >-----------------------------|
874 -- ---------------------------------------------------------------------------
875 -- {Start Of Comments}
876 --
877 -- Description:
878 -- Update translation table for all languages.
879 --
880 -- Prerequisites:
881 -- None.
882 --
883 -- In Parameters:
884 -- None.
885 --
886 -- Out Parameters:
887 -- None.
888 --
889 -- Post Success:
890 -- The process succeeds. No parameters are returned.
891 --
892 -- Post Failure:
893 -- None.
894 --
895 -- Developer Implementation Notes:
896 -- None.
897 --
898 -- Access Status:
899 -- Public.
900 --
901 -- {End Of Comments}
902 -- ---------------------------------------------------------------------------
903 PROCEDURE add_language IS
904 BEGIN
905 --
906 DELETE FROM per_contact_info_types_tl pcitt
907 WHERE NOT EXISTS(
908 SELECT NULL FROM per_contact_info_types pcit
909 WHERE pcit.information_type = pcitt.information_type);
910 --
911 UPDATE per_contact_info_types_tl pcitt_t SET
915 AND pcitt_b.language = pcitt_t.source_lang)
912 description = (SELECT pcitt_b.description
913 FROM per_contact_info_types_tl pcitt_b
914 WHERE pcitt_b.information_type = pcitt_t.information_type
916 WHERE (pcitt_t.information_type, pcitt_t.language) IN
917 (SELECT pcitt_sub_t.information_type, pcitt_sub_t.language
918 FROM per_contact_info_types_tl pcitt_sub_b, per_contact_info_types_tl pcitt_sub_t
919 WHERE pcitt_sub_b.information_type = pcitt_sub_t.information_type
920 AND pcitt_sub_b.language = pcitt_sub_t.source_lang
921 AND (pcitt_sub_b.description <> pcitt_sub_t.description
922 OR (pcitt_sub_b.description IS NULL AND pcitt_sub_t.description IS NOT NULL)
923 OR (pcitt_sub_b.description IS NOT NULL AND pcitt_sub_t.description IS NULL)));
924 --
925 INSERT INTO per_contact_info_types_tl(
926 information_type,
927 language,
928 source_lang,
929 description,
930 last_update_date,
931 last_updated_by,
932 last_update_login,
933 created_by,
934 creation_date)
935 SELECT
936 pcitt.information_type,
937 fl.language_code,
938 pcitt.source_lang,
939 pcitt.description,
940 pcitt.last_update_date,
941 pcitt.last_updated_by,
942 pcitt.last_update_login,
943 pcitt.created_by,
944 pcitt.creation_date
945 FROM
946 per_contact_info_types_tl pcitt,
947 fnd_languages fl
948 WHERE fl.installed_flag IN ('I', 'B')
949 AND pcitt.language = USERENV('LANG')
950 AND NOT EXISTS(
951 SELECT NULL FROM per_contact_info_types_tl pcitt_t
952 WHERE pcitt_t.information_type = pcitt.information_type
953 AND pcitt_t.language = fl.language_code);
954 --
955 END add_language;
956 -- ---------------------------------------------------------------------------
957 -- |-----------------------< set_translation_globals >-----------------------|
958 -- ---------------------------------------------------------------------------
959 -- {Start Of Comments}
960 --
961 -- Description:
962 -- Set global variables used in MLS validation.
963 --
964 -- Prerequisites:
965 -- None.
966 --
967 -- In Parameters:
968 -- Name Reqd Type Description
969 -- p_business_group_id Yes NUMBER Business Group ID.
970 -- p_legislation_code Yes VARCHAR2 Legislation Code.
971 --
972 -- Out Parameters:
973 -- None.
974 --
975 -- Post Success:
976 -- The process succeeds. No parameters are returned.
977 --
978 -- Post Failure:
979 -- None.
980 --
981 -- Developer Implementation Notes:
982 -- None.
983 --
984 -- Access Status:
985 -- Public.
986 --
987 -- {End Of Comments}
988 -- ---------------------------------------------------------------------------
989 PROCEDURE set_translation_globals(
990 p_business_group_id IN NUMBER,
991 p_legislation_code IN VARCHAR2) IS
992 BEGIN
993 g_business_group_id := p_business_group_id;
994 g_legislation_code := p_legislation_code;
995 END set_translation_globals;
996 -- ---------------------------------------------------------------------------
997 -- |-------------------------< validate_translation >------------------------|
998 -- ---------------------------------------------------------------------------
999 -- {Start Of Comments}
1000 --
1001 -- Description:
1002 -- Validate if the translation of specified language for contact information
1003 -- type description is unique.
1004 --
1005 -- Prerequisites:
1006 -- Global variable g_dummy must be set before executing this procedure.
1007 --
1008 -- In Parameters:
1009 -- Name Reqd Type Description
1010 -- information_type Yes VARCHAR2 Contact Information Type.
1011 -- language Yes VARCHAR2 Language.
1012 -- description Yes VARCHAR2 Description for Contact
1013 -- Information Type.
1014 -- Out Parameters:
1015 -- None.
1016 --
1017 -- Post Success:
1018 -- The process succeeds. No parameters are returned.
1019 --
1020 -- Post Failure:
1021 -- The process will be terminated.
1022 --
1023 -- Developer Implementation Notes:
1024 -- This procedure is called in user-named trigger 'TRANSLATIONS' of Enter
1025 -- Contact Relationship Extra Information form.
1026 --
1027 -- Access Status:
1028 -- Public.
1029 --
1030 -- {End Of Comments}
1031 -- ---------------------------------------------------------------------------
1032 PROCEDURE validate_translation(
1033 information_type IN VARCHAR2,
1034 language IN VARCHAR2,
1035 description IN VARCHAR2) IS
1036 --
1037 l_package_name VARCHAR2(80) := 'PER_CONTACT_INFO_TYPES_PKG.VALIDATE_TRANSLATION';
1038 --
1039 CURSOR c_translation(
1040 p_language IN VARCHAR2,
1041 p_description IN VARCHAR2,
1042 p_information_type IN VARCHAR2) IS
1043 SELECT 1 FROM
1044 per_contact_info_types_tl citt,
1045 per_contact_info_types cit
1046 WHERE UPPER(citt.description) = UPPER(p_description)
1047 AND citt.information_type = cit.information_type
1048 AND citt.language = p_language
1049 AND (cit.information_type <> p_information_type
1050 OR p_information_type IS NULL);
1051 --
1052 BEGIN
1053 --
1054 hr_utility.set_location(
1055 procedure_name => l_package_name,
1056 stage => 10);
1057 --
1058 OPEN c_translation(language, description, information_type);
1059 --
1060 hr_utility.set_location(
1061 procedure_name => l_package_name,
1062 stage => 50);
1063 --
1064 FETCH c_translation INTO g_dummy;
1065 -- = fail if a description translation is already present in the table for a
1066 -- = given language. Otherwise, no action is performed.
1067 IF c_translation%NOTFOUND THEN
1068 --
1069 hr_utility.set_location(
1070 procedure_name => l_package_name,
1071 stage => 60);
1072 --
1073 CLOSE c_translation;
1074 ELSE
1075 --
1076 hr_utility.set_location(
1077 procedure_name => l_package_name,
1078 stage => 70);
1079 --
1080 fnd_message.set_name(
1081 application => 'PAY',
1082 name => 'HR_TRANSLATION_EXISTS');
1083 --
1084 fnd_message.raise_error;
1085 END IF;
1086 -- =
1087 --
1088 hr_utility.set_location(
1089 procedure_name => 'Leaving:' || l_package_name,
1090 stage => 80);
1091 --
1092 END validate_translation;
1093 END per_contact_info_types_pkg;