1 PACKAGE BODY PER_PERSON_TYPES_PKG as
2 /* $Header: pedpt01t.pkb 120.1 2011/04/28 10:13:45 sidsaxen ship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 --
9 PROCEDURE check_duplicate_name(p_business_group_id in number,
10 p_user_person_type in varchar2,
11 p_rowid in varchar2) is
12 --
13 -- Suppression of BG index(+ 0) is removed
14 -- to avoid FTS
15 -- Bug #3646157
16 --
17 cursor csr_user_name is select null
18 from per_person_types_tl pttl,
19 per_person_types pt
20 where pt.business_group_id = p_business_group_id
21 and upper(pttl.user_person_type) = upper(p_user_person_type)
22 and (pt.rowid <> p_rowid
23 or p_rowid is null)
24 and pt.person_type_id = pttl.person_type_id
25 and pttl.LANGUAGE = userenv('LANG');
26 --
27 g_dummy_number number;
28 v_not_unique boolean := FALSE;
29 --
30 -- Check the user name is unique
31 --
32 begin
33 --
34 open csr_user_name;
35 fetch csr_user_name into g_dummy_number;
36 v_not_unique := csr_user_name%FOUND;
37 close csr_user_name;
38 --
39 if v_not_unique then
40 hr_utility.set_message(801,'HR_6163_SETUP_DUP');
41 hr_utility.raise_error;
42 end if;
43 --
44 end check_duplicate_name;
45 --
46 PROCEDURE check_duplicate_system_name (p_business_group_id in number,
47 p_system_name in varchar2,
48 p_default_flag in varchar2,
49 p_rowid in varchar2) is
50 --
51 -- Suppression of BG index(+ 0) is removed
52 -- to avoid FTS
53 -- Bug #3646157
54 --
55 cursor csr_system_name is select null
56 from hr_lookups lu, per_person_types pt
57 where pt.business_group_id = p_business_group_id
58 and lu.meaning = p_system_name
59 and lu.lookup_type = 'PERSON_TYPE'
60 and lu.lookup_code = pt.system_person_type
61 and pt.default_flag = 'Y'
62 and p_default_flag = 'Y'
63 and (pt.rowid <> p_rowid
64 or p_rowid is null);
65
66 dummy_number number;
67 not_unique boolean := FALSE;
68
69 begin
73 hr_utility.set_location('row id'||p_rowid,4);
70 hr_utility.set_location('BG id '||to_char(p_business_group_Id),1);
71 hr_utility.set_location('system name '||p_system_name,2);
72 hr_utility.set_location('default flag'||p_default_flag,3);
74
75 open csr_system_name;
76 fetch csr_system_name into dummy_number;
77 not_unique := csr_system_name%FOUND;
78 close csr_system_name;
79
80 if not_unique then
81 hr_utility.set_message (800,'HR_52775_SYS_PRIM_DEFAULT');
82 hr_utility.raise_error;
83 end if;
84
85 end check_duplicate_system_name;
86 --
87 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
88 X_Person_Type_Id IN OUT NOCOPY NUMBER,
89 X_Business_Group_Id NUMBER,
90 X_Active_Flag VARCHAR2,
91 X_Default_Flag VARCHAR2,
92 X_System_Person_Type VARCHAR2,
93 X_System_Name VARCHAR2,
94 X_User_Person_Type VARCHAR2
95 ) IS
96 CURSOR C IS SELECT rowid FROM per_person_types
97 WHERE person_type_id = X_Person_Type_Id;
98
99 CURSOR C2 IS SELECT per_person_types_s.nextval FROM sys.dual;
100
101 BEGIN
102
103 if (X_Person_Type_Id is NULL) then
104 OPEN C2;
105 FETCH C2 INTO X_Person_Type_Id;
106 CLOSE C2;
107 end if;
108
109 --
110 -- Added the following code as a part of Zero Downtime Patching Project.
111 -- Code Starts Here.
112 --
113
114 BEGIN
115 PER_RIC_PKG.chk_integrity (
116 p_entity_name => 'PER_PERSON_TYPES',
117 p_ref_entity => 'HR_ALL_ORGANIZATION_UNITS',
118 p_ref_column_name => 'BUSINESS_GROUP_ID',
119 p_ref_col_value_number => X_BUSINESS_GROUP_ID,
120 p_ref_col_value_varchar => NULL,
121 p_ref_col_value_date => NULL,
122 p_ref_type => 'INS');
123
124 END;
125 --
126 -- Code Ends Here.
127 --
128
129 INSERT INTO per_person_types(
130 person_type_id,
131 business_group_id,
132 active_flag,
133 default_flag,
134 system_person_type,
135 user_person_type
136 ) VALUES (
137 X_Person_Type_Id,
138 X_Business_Group_Id,
139 X_Active_Flag,
140 X_Default_Flag,
141 X_System_Person_Type,
142 X_User_Person_Type
143
144 );
145 -- MLS
146
147 --
148 -- Added the following code as a part of Zero Downtime Patching Project.
149 -- Code Starts Here.
150 --
151
152 BEGIN
153 PER_RIC_PKG.chk_integrity (
154 p_entity_name => 'PER_PERSON_TYPES_TL',
155 p_ref_entity => 'PER_PERSON_TYPES',
156 p_ref_column_name => 'PERSON_TYPE_ID',
157 p_ref_col_value_number => X_PERSON_TYPE_ID,
158 p_ref_col_value_varchar => NULL,
159 p_ref_col_value_date => NULL,
160 p_ref_type => 'INS');
161
162 END;
163 --
164 -- Code Ends Here.
165 --
166
167 insert into PER_PERSON_TYPES_TL (
168 PERSON_TYPE_ID,
169 USER_PERSON_TYPE,
170 -- LAST_UPDATE_DATE,
171 -- LAST_UPDATED_BY,
172 -- LAST_UPDATE_LOGIN,
173 -- CREATED_BY,
174 -- CREATION_DATE,
175 LANGUAGE,
176 SOURCE_LANG
177 ) select
178 X_Person_Type_Id,
179 X_User_Person_Type,
180 -- X_LAST_UPDATE_DATE,
181 -- X_LAST_UPDATED_BY,
182 -- X_LAST_UPDATE_LOGIN,
183 -- X_CREATED_BY,
184 -- X_CREATION_DATE,
185 L.LANGUAGE_CODE,
186 userenv('LANG')
187 from FND_LANGUAGES L
188 where L.INSTALLED_FLAG in ('I', 'B')
189 and not exists
190 (select NULL
191 from PER_PERSON_TYPES_TL T
192 where T.PERSON_TYPE_ID = X_Person_Type_Id
193 and T.LANGUAGE = L.LANGUAGE_CODE);
194 --
195 OPEN C;
196 FETCH C INTO X_Rowid;
197 if (SQL%NOTFOUND) then
198 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
199 hr_utility.set_message_token('PROCEDURE','Insert_Row');
200 hr_utility.set_message_token('STEP','1');
201 hr_utility.raise_error;
202 end if;
203 CLOSE C;
204 END Insert_Row;
205
206 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
207 X_Person_Type_Id NUMBER,
208 X_Business_Group_Id NUMBER,
209 X_Active_Flag VARCHAR2,
210 X_Default_Flag VARCHAR2,
211 X_System_Person_Type VARCHAR2,
212 X_User_Person_Type VARCHAR2
213 ) IS
214 CURSOR C IS
215 SELECT ppt.person_type_id,
216 ppt.business_group_id,
217 ppt.active_flag,
218 ppt.default_flag,
219 ppt.system_person_type,
220 ppt_tl.user_person_type
221 FROM per_person_types ppt,
222 per_person_types_tl ppt_tl
223 WHERE ppt.rowid = X_Rowid
224 AND ppt.person_type_id = ppt_tl.person_type_id
225 AND ppt_tl.language = userenv('LANG')
226 FOR UPDATE of ppt.person_type_id NOWAIT;
230 USER_PERSON_TYPE,
227 Recinfo C%ROWTYPE;
228 --MLS
229 cursor c1 is select
231 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
232 from PER_PERSON_TYPES_TL
233 where PERSON_TYPE_ID = X_Person_Type_Id
234 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
235 for update of PERSON_TYPE_ID nowait;
236 --
237 BEGIN
238 OPEN C;
239 FETCH C INTO Recinfo;
240 if (SQL%NOTFOUND) then
241 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
242 hr_utility.set_message_token('PROCEDURE','Lock_Row');
243 hr_utility.set_message_token('STEP','1');
244 hr_utility.raise_error;
245 end if;
246 CLOSE C;
247 --
248 Recinfo.active_flag := rtrim(Recinfo.active_flag);
249 Recinfo.default_flag := rtrim(Recinfo.default_flag);
250 Recinfo.system_person_type := rtrim(Recinfo.system_person_type);
251 Recinfo.user_person_type := rtrim(Recinfo.user_person_type);
252 --
253 if (
254 ( (Recinfo.person_type_id = X_Person_Type_Id)
255 OR ( (Recinfo.person_type_id IS NULL)
256 AND (X_Person_Type_Id IS NULL)))
257 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
258 OR ( (Recinfo.business_group_id IS NULL)
259 AND (X_Business_Group_Id IS NULL)))
260 AND ( (Recinfo.active_flag = X_Active_Flag)
261 OR ( (Recinfo.active_flag IS NULL)
262 AND (X_Active_Flag IS NULL)))
263 AND ( (Recinfo.default_flag = X_Default_Flag)
264 OR ( (Recinfo.default_flag IS NULL)
265 AND (X_Default_Flag IS NULL)))
266 AND ( (Recinfo.system_person_type = X_System_Person_Type)
267 OR ( (Recinfo.system_person_type IS NULL)
268 AND (X_System_Person_Type IS NULL)))
269 AND ( (Recinfo.user_person_type = X_User_Person_Type)
270 OR ( (Recinfo.user_person_type IS NULL)
271 AND (X_User_Person_Type IS NULL)))
272 ) then
273 -- return;
274 null;
275 else
276 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
277 APP_EXCEPTION.RAISE_EXCEPTION;
278 end if;
279
280 -- MLS
281 for tlinfo in c1 loop
282 if (tlinfo.BASELANG = 'Y') then
283 if ( (tlinfo.USER_PERSON_TYPE = X_User_Person_Type)
284 ) then
285 null;
286 else
287 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
288 app_exception.raise_exception;
289 end if;
290 end if;
291 end loop;
292 return;
293 --
294 END Lock_Row;
295
296 PROCEDURE Update_Row(X_Rowid VARCHAR2,
297 X_Person_Type_Id NUMBER,
298 X_Business_Group_Id NUMBER,
299 X_Active_Flag VARCHAR2,
300 X_Default_Flag VARCHAR2,
301 X_System_Person_Type VARCHAR2,
302 X_System_Name VARCHAR2,
303 X_User_Person_Type VARCHAR2
304 ) IS
305
306 BEGIN
307
308 UPDATE per_person_types
309 SET
310
311 person_type_id = X_Person_Type_Id,
312 business_group_id = X_Business_Group_Id,
313 active_flag = X_Active_Flag,
314 default_flag = X_Default_Flag,
315 system_person_type = X_System_Person_Type,
316 user_person_type = X_User_Person_Type
317 WHERE rowid = X_rowid;
318
319 if (SQL%NOTFOUND) then
320 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
321 hr_utility.set_message_token('PROCEDURE','Update_Row');
322 hr_utility.set_message_token('STEP','1');
323 hr_utility.raise_error;
324 end if;
325
326 -- MLS
327 update PER_PERSON_TYPES_TL set
328 USER_PERSON_TYPE = X_User_Person_Type,
329 -- LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
330 -- LAST_UPDATED_BY = X_LAST_UPDATED_BY,
331 -- LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
332 SOURCE_LANG = userenv('LANG')
333 where PERSON_TYPE_ID = X_Person_Type_Id
334 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
335
336 if (sql%notfound) then
337 raise no_data_found;
338 end if;
339 --
340
341 END Update_Row;
342
343 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
344 X_Default_flag varchar2,
345 X_Person_type_Id number) IS
346 BEGIN
347 --
348 if X_Default_flag = 'Y' then
349 hr_utility.set_message(801,'HR_6618_PERSON_TYPE_NO_DEL_DEF');
350 hr_utility.raise_error;
351 end if;
352 --
353 -- if the system name is in use then disallow the deletion
354 --
355 Check_System_Delete(X_Person_type_Id);
356 --
357
358 -- MLS
359 delete from PER_PERSON_TYPES_TL
360 where PERSON_TYPE_ID = X_Person_Type_Id;
361
362 if (sql%notfound) then
363 raise no_data_found;
364 end if;
365 --
366
367 --
368 -- Added the following code as a part of Zero Downtime Patching Project.
369 -- Code Starts Here.
370 --
371
372 BEGIN
373 PER_RIC_PKG.chk_integrity (
374 p_entity_name => 'PER_PERSON_TYPES',
375 p_ref_entity => 'PER_PERSON_TYPES_TL',
376 p_ref_column_name => 'PERSON_TYPE_ID',
380 p_ref_type => 'DEL');
377 p_ref_col_value_number => X_PERSON_TYPE_ID,
378 p_ref_col_value_varchar => NULL,
379 p_ref_col_value_date => NULL,
381
382 END;
383 --
384 -- Code Ends Here.
385 --
386
387 DELETE FROM per_person_types
388 WHERE rowid = X_Rowid;
389
390 if (SQL%NOTFOUND) then
391 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
392 hr_utility.set_message_token('PROCEDURE','Delete_Row');
393 hr_utility.set_message_token('STEP','1');
394 hr_utility.raise_error;
395 end if;
396 END Delete_Row;
397
398 PROCEDURE Check_Delete (X_Business_Group_Id NUMBER) IS
399 System_Name VARCHAR2(30);
400 -- Suppression of BG index(+ 0) is removed
401 -- to avoid FTS
402 -- Bug #3646157
403 CURSOR C IS SELECT hr.meaning
404 from hr_lookups hr
405 WHERE hr.lookup_type = 'PERSON_TYPE'
406 and not exists
407 (select null
408 from per_person_types ppt
409 where hr.lookup_code = ppt.system_person_type
410 AND PPT.business_group_id = X_Business_Group_Id
411 AND PPT.active_flag = 'Y'
412 AND PPT.default_flag = 'Y'
413 HAVING COUNT(PPT.system_person_type) = 1);
414
415 BEGIN
416 --
417 -- There must be at least one type of system name in existence
418 --
419 OPEN C;
420 FETCH C INTO System_Name;
421 CLOSE C;
422
423 if System_Name is not null then
424 hr_utility.set_message(801,'HR_6318_SYS_PRIM_DEFAULT');
425 hr_utility.set_message_token('SYSTEM_NAME',System_Name);
426 hr_utility.raise_error;
427 end if;
428
429 END Check_Delete;
430
431 PROCEDURE Check_Default (X_Business_Group_Id IN NUMBER) IS
432 --
433 l_system_name_nodefault hr_lookups.meaning%TYPE;
434 l_system_name_default hr_lookups.meaning%TYPE;
435 --
436 -- Cursor to pull back all of the System Names that don't have a default
437 -- Person Type record.
438 --
439 -- Suppression of BG index(+ 0) is removed
440 -- to avoid FTS
441 -- Bug #3646157
442 --
443 CURSOR csr_nodefault IS
444 SELECT hr.meaning
445 FROM hr_lookups hr
446 WHERE hr.lookup_type = 'PERSON_TYPE'
447 AND EXISTS
448 (SELECT null
449 FROM per_person_types ppt
450 WHERE hr.lookup_code = ppt.system_person_type
451 AND ppt.business_group_id = X_Business_Group_Id
452 AND ppt.active_flag = 'Y'
453 AND ppt.default_flag = 'Y'
454 HAVING COUNT(PPT.system_person_type) < 1);
455 --
456 -- Cursor to pull back all of the System Names that have more than one
457 -- default Person Type record.
458 --
459 -- Suppression of BG index(+ 0) is removed
460 -- to avoid FTS
461 -- Bug #3646157
462 --
463 CURSOR csr_default IS
464 SELECT hr.meaning
465 FROM hr_lookups hr
466 WHERE hr.lookup_type = 'PERSON_TYPE'
467 AND EXISTS
468 (SELECT null
469 FROM per_person_types ppt
470 WHERE hr.lookup_code = ppt.system_person_type
471 AND ppt.business_group_id = X_Business_Group_Id
472 AND ppt.active_flag = 'Y'
473 AND ppt.default_flag = 'Y'
474 HAVING COUNT(PPT.system_person_type) > 1);
475 --
476 BEGIN
477
478 --
479 -- Check to see if there are any system names with no default records
480 -- and raise an error if there are.
481 --
482
483 OPEN csr_nodefault;
484 FETCH csr_nodefault INTO l_system_name_nodefault;
485 CLOSE csr_nodefault;
486
487 IF l_system_name_nodefault IS NOT NULL THEN
488 hr_utility.set_message(800,'HR_289007_SYS_PRIM_NODEFAULT');
489 hr_utility.set_message_token('SYSTEM_NAME',l_system_name_nodefault);
490 hr_utility.raise_error;
491 END IF;
492
493 --
494 -- Check to see if there are any system names with more than one default
495 -- record and raise an error message if there are.
496 --
497
498 OPEN csr_default;
499 FETCH csr_default INTO l_system_name_default;
500 CLOSE csr_default;
501
502 IF l_system_name_default IS NOT NULL THEN
503 hr_utility.set_message(800,'HR_6318_SYS_PRIM_DEFAULT');
504 hr_utility.set_message_token('SYSTEM_NAME',l_system_name_default);
505 hr_utility.raise_error;
506 END IF;
507
508 END Check_Default;
509
510 PROCEDURE Check_System_Delete(X_Person_Type_Id in NUMBER) IS
511 --
512 -- Cursor modifed to include the check on per_person_type_usages_f
513 -- Bug# 2561337
514 --
515 -- modified the cursor definition for better performance
516 -- Bug #3646157
517 cursor csr_system is
518 select null
519 from dual
520 where exists (
521 (select null
522 from per_people_f
523 where person_type_id = X_person_type_id)
524 UNION
525 (select null
526 from per_person_type_usages_f
527 where person_type_id= X_person_type_id)
528 );
529 --
533 begin
530 g_dummy_number number;
531 v_system_used boolean := FALSE;
532 --
534 --
535 -- Check the person type is not being used before the record is
536 -- deleted
537 --
538 open csr_system;
539 fetch csr_system into g_dummy_number;
540 v_system_used := csr_system%FOUND;
541 close csr_system;
542 --
543 if v_system_used then
544 hr_utility.set_message(801,'HR_6619_PERSON_TYPE_EXISTS');
545 hr_utility.raise_error;
546 end if;
547 --
548 END Check_System_Delete;
549 --
550 procedure LOAD_ROW
551 (X_PERSON_TYPE in VARCHAR2
552 ,X_BUSINESS_GROUP_NAME in VARCHAR2
553 ,X_ACTIVE_FLAG in VARCHAR2
554 ,X_DEFAULT_FLAG in VARCHAR2
555 ,X_SYSTEM_PERSON_TYPE in VARCHAR2
556 ,X_USER_PERSON_TYPE in VARCHAR2
557 ,X_OWNER in VARCHAR2
558 )
559 is
560 cursor csr_business_group
561 (x_name in hr_all_organization_units.name%TYPE
562 )
563 is
564 select org.organization_id
565 from per_business_groups org
566 where org.name = x_name;
567 l_business_group csr_business_group%ROWTYPE;
568 cursor csr_person_type
569 (x_user_person_type in per_person_types.user_person_type%TYPE
570 ,x_business_group_id in per_person_types.business_group_id%TYPE
571 )
572 is
573 select ptp.person_type_id
574 ,ptp.rowid
575 from per_person_types ptp
576 where ptp.user_person_type = x_user_person_type
577 and ptp.business_group_id = x_business_group_id;
578 l_person_type csr_person_type%ROWTYPE;
579 begin
580 -- Validate input paramneters
581 open csr_business_group(x_business_group_name);
582 fetch csr_business_group into l_business_group;
583 if csr_business_group%notfound then
584 close csr_business_group;
585 hr_utility.set_message(800,'HR_7208_API_BUS_GRP_INVALID');
586 hr_utility.raise_error;
587 end if;
588 close csr_business_group;
589 -- Insert or update as appropriate
590 open csr_person_type(x_person_type,l_business_group.organization_id);
591 fetch csr_person_type into l_person_type;
592 if csr_person_type%found then
593 close csr_person_type;
594 UPDATE_ROW
595 (X_ROWID => l_person_type.rowid
596 ,X_PERSON_TYPE_ID => l_person_type.person_type_id
597 ,X_BUSINESS_GROUP_ID => l_business_group.organization_id
598 ,X_ACTIVE_FLAG => X_ACTIVE_FLAG
599 ,X_DEFAULT_FLAG => X_DEFAULT_FLAG
600 ,X_SYSTEM_PERSON_TYPE => X_SYSTEM_PERSON_TYPE
601 ,X_SYSTEM_NAME => NULL
602 ,X_USER_PERSON_TYPE => X_USER_PERSON_TYPE
603 );
604 else
605 close csr_person_type;
606 INSERT_ROW
607 (X_ROWID => l_person_type.rowid
608 ,X_PERSON_TYPE_ID => l_person_type.person_type_id
609 ,X_BUSINESS_GROUP_ID => l_business_group.organization_id
610 ,X_ACTIVE_FLAG => X_ACTIVE_FLAG
611 ,X_DEFAULT_FLAG => X_DEFAULT_FLAG
612 ,X_SYSTEM_PERSON_TYPE => X_SYSTEM_PERSON_TYPE
613 ,X_SYSTEM_NAME => NULL
614 ,X_USER_PERSON_TYPE => X_USER_PERSON_TYPE
615 );
616 end if;
617 end LOAD_ROW;
618 --
619 procedure TRANSLATE_ROW
620 (X_PERSON_TYPE in VARCHAR2
621 ,X_BUSINESS_GROUP_NAME in VARCHAR2
622 ,X_USER_PERSON_TYPE in VARCHAR2
623 ,X_OWNER in VARCHAR2
624 )
625 is
626 cursor csr_person_type
627 (x_user_person_type in per_person_types.user_person_type%TYPE
628 ,x_name in hr_all_organization_units.name%TYPE
629 )
630 is
631 select ptp.person_type_id
632 from per_person_types ptp
633 ,per_business_groups org
634 where ptp.business_group_id = org.organization_id
635 and ptp.user_person_type = x_user_person_type
636 and org.name = x_name;
637 l_person_type csr_person_type%ROWTYPE;
638 begin
639 -- Translate keys to internal ids
640 open csr_person_type(x_person_type,x_business_group_name);
641 fetch csr_person_type into l_person_type;
642 close csr_person_type;
643 -- Update table
644 UPDATE per_person_types_tl
645 SET user_person_type = X_USER_PERSON_TYPE
646 ,last_update_date = SYSDATE
647 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
648 ,last_update_login = 1
649 ,source_lang = USERENV('LANG')
650 WHERE USERENV('LANG') in (language,source_lang)
651 AND person_type_id = l_person_type.person_type_id;
652 end TRANSLATE_ROW;
653 --
654 procedure ADD_LANGUAGE
655 is
656 begin
657 -- process PER_PERSON_TYPES_TL table
658 delete from PER_PERSON_TYPES_TL T
659 where not exists
660 (select NULL
661 from PER_PERSON_TYPES B
662 where B.PERSON_TYPE_ID = T.PERSON_TYPE_ID
663 );
664
665 update PER_PERSON_TYPES_TL T set (
666 USER_PERSON_TYPE
667 ) = (select
668 B.USER_PERSON_TYPE
669 from PER_PERSON_TYPES_TL B
670 where B.PERSON_TYPE_ID = T.PERSON_TYPE_ID
671 and B.LANGUAGE = T.SOURCE_LANG)
672 where (
673 T.PERSON_TYPE_ID,
674 T.LANGUAGE
675 ) in (select
676 SUBT.PERSON_TYPE_ID,
677 SUBT.LANGUAGE
678 from PER_PERSON_TYPES_TL SUBB, PER_PERSON_TYPES_TL SUBT
679 where SUBB.PERSON_TYPE_ID = SUBT.PERSON_TYPE_ID
680 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
681 and (SUBB.USER_PERSON_TYPE <> SUBT.USER_PERSON_TYPE
682 ));
683
684 insert into PER_PERSON_TYPES_TL (
685 PERSON_TYPE_ID,
686 USER_PERSON_TYPE,
687 LAST_UPDATE_DATE,
688 LAST_UPDATED_BY,
689 LAST_UPDATE_LOGIN,
690 CREATED_BY,
691 CREATION_DATE,
692 LANGUAGE,
693 SOURCE_LANG
694 ) select
695 B.PERSON_TYPE_ID,
696 B.USER_PERSON_TYPE,
697 B.LAST_UPDATE_DATE,
698 B.LAST_UPDATED_BY,
699 B.LAST_UPDATE_LOGIN,
700 B.CREATED_BY,
701 B.CREATION_DATE,
702 L.LANGUAGE_CODE,
703 B.SOURCE_LANG
704 from PER_PERSON_TYPES_TL B, FND_LANGUAGES L
705 where L.INSTALLED_FLAG in ('I', 'B')
706 and B.LANGUAGE = userenv('LANG')
707 and not exists
708 (select NULL
709 from PER_PERSON_TYPES_TL T
710 where T.PERSON_TYPE_ID = B.PERSON_TYPE_ID
711 and T.LANGUAGE = L.LANGUAGE_CODE);
712
713 -- process PER_STARTUP_PERSON_TYPES_TL table
714 -- Removed the insertion as it is redundant
715 --
716 end ADD_LANGUAGE;
717 --
718 --------------------------------------------------------------------------------
719 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
720 p_legislation_code IN VARCHAR2) IS
721 BEGIN
722 g_business_group_id := p_business_group_id;
723 g_legislation_code := p_legislation_code;
724 END;
725 --------------------------------------------------------------------------------
726 --------------------------------------------------------------------------------
727 procedure validate_translation(person_type_id IN NUMBER,
728 language IN VARCHAR2,
729 user_person_type IN VARCHAR2,
730 p_business_group_id IN NUMBER )
731 IS
732 /*
733
734 This procedure fails if a user person type translation is already present in
735 the table for a given language. Otherwise, no action is performed. It is
736 used to ensure uniqueness of translated user person types.
737
738 */
739
740 --
741 -- This cursor implements the validation we require,
742 -- and expects that the various package globals are set before
743 -- the call to this procedure is made. This is done from the
744 -- user-named trigger 'TRANSLATIONS' in the form
745 --
746 cursor c_translation(p_language IN VARCHAR2,
747 p_user_person_type IN VARCHAR2,
748 p_person_type_id IN NUMBER,
749 p_bus_grp_id IN NUMBER)
750 IS
751 SELECT 1
752 FROM per_person_types_tl pptt,
753 per_person_types ppt
754 WHERE upper(pptt.user_person_type)=upper(p_user_person_type)
755 AND pptt.person_type_id = ppt.person_type_id
756 AND pptt.language = p_language
757 AND (ppt.person_type_id <> p_person_type_id OR p_person_type_id IS NULL)
758 AND (ppt.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
759 ;
760
761 l_package_name VARCHAR2(80) := 'PER_PERSON_TYPES_PKG.VALIDATE_TRANSLATION';
762 l_business_group_id NUMBER := nvl(p_business_group_id, g_business_group_id);
763
764 BEGIN
765 hr_utility.set_location (l_package_name,10);
766 OPEN c_translation(language, user_person_type,person_type_id,
767 l_business_group_id);
768 hr_utility.set_location (l_package_name,50);
769 FETCH c_translation INTO g_dummy;
770
771 IF c_translation%NOTFOUND THEN
772 hr_utility.set_location (l_package_name,60);
773 CLOSE c_translation;
774 ELSE
775 hr_utility.set_location (l_package_name,70);
776 CLOSE c_translation;
777 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
778 fnd_message.raise_error;
779 END IF;
780 hr_utility.set_location ('Leaving:'||l_package_name,80);
781 END validate_translation;
782 --------------------------------------------------------------------------------
783
784 END PER_PERSON_TYPES_PKG;
[Home] [Help]