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