1 PACKAGE BODY PAY_ELEMENT_SETS_PKG as
2 /* $Header: pyels01t.pkb 120.1 2005/09/30 00:32:03 tvankayl noship $ */
3 --
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +===========================================================================+
9 Name
10 pay_element_sets_pkg
11 Purpose
12 Supports the ELS block in the form PAYWSDRP (Define Element and
13 Distributuion Set.
14 Notes
15
16 History
17 24-Mar-94 J.S.Hobbs 40.0 Date created.
18 22-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
19 05-Mar-97 J.Alloun 40.7 Changed all occurances of system.dual
20 to sys.dual for next release requirements.
21 22-Sep-05 Shisriva 115.3 Changes for MLS enabling and dual maintenance.
22 26-Sep-05 Shisriva 115.4 Added dbdrv commands.
23 26-Sep-05 Shisriva 115.5 Removed gscc violation for in out parameters.
24 Added NOCOPY for two parameters.
25 30-Sep-05 tvankayl 115.6 Added delete validation for element
26 sets of element_set_type = 'E'.
27 The changes were initially made in
28 the branch version 115.2.1159.2.
29 ============================================================================*/
30 --
31 -----------------------------------------------------------------------------
32 -- Name --
33 -- lock_element_set --
34 -- Purpose --
35 -- Places a lock on the element set. --
36 -- Arguments --
37 -- See Be;ow. --
38 -- Notes --
39 -- Used when maintaining the members of an element set ie. when dealing --
40 -- with element type rules and classification rules. --
41 -----------------------------------------------------------------------------
42 --
43 g_dummy number(1); -- Dummy for cursor returns which are not needed.
44 g_business_group_id number(15); -- For validating translation.
45 g_legislation_code varchar2(150); -- For validating translation.
46 --
47 procedure lock_element_set
48 (
49 p_element_set_id number
50 ) is
51 --
52 cursor csr_element_set is
53 select els.element_set_id
54 from pay_element_sets els
55 where els.element_set_id = p_element_set_id
56 for update;
57 --
58 v_dummy number;
59 --
60 begin
61 --
62 open csr_element_set;
63 fetch csr_element_set into v_dummy;
64 if csr_element_set%notfound then
65 close csr_element_set;
66 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
67 hr_utility.set_message_token('PROCEDURE',
68 'pay_element_sets_pkg.lock_element_set');
69 hr_utility.set_message_token('STEP','1');
70 hr_utility.raise_error;
71 else
72 close csr_element_set;
73 end if;
74 --
75 end lock_element_set;
76 --
77 -----------------------------------------------------------------------------
78 -- Name --
79 -- chk_debit_or_credit --
80 -- Purpose --
81 -- When creating a distribution set all the elements within the set must --
82 -- have the same debit or credit status ie. the combination of element --
83 -- rules and classification rules should result in a set of elements --
84 -- that have the same debit or credit status. --
85 -- Arguments --
86 -- p_classification_id - If adding an element type rule then this is the --
87 -- classification_of the element being added. --
88 -- If adding a classification rule then this is --
89 -- classification_of of the classification being --
90 -- added. --
91 -- Notes --
92 -- This is used when adding element type or classification rules. --
93 -----------------------------------------------------------------------------
94 --
95 procedure chk_debit_or_credit
96 (
97 p_element_set_id number,
98 p_classification_id number
99 ) is
100 --
101 cursor csr_debit_or_credit_ele_rule is
102 select ecl.costing_debit_or_credit
103 from pay_element_classifications ecl
104 where ecl.classification_id = p_classification_id
105 and exists
106 (select null
110 where etr.element_set_id = p_element_set_id
107 from pay_element_type_rules etr,
108 pay_element_types_f et,
109 pay_element_classifications ecl2
111 and et.element_type_id = etr.element_type_id
112 and ecl2.classification_id = et.classification_id
113 and ecl2.costing_debit_or_credit <>
114 ecl.costing_debit_or_credit);
115 --
116 cursor csr_debit_or_credit_class_rule is
117 select ecl.costing_debit_or_credit
118 from pay_element_classifications ecl
119 where ecl.classification_id = p_classification_id
120 and exists
121 (select null
122 from pay_ele_classification_rules ecr,
123 pay_element_classifications ecl2
124 where ecr.element_set_id = p_element_set_id
125 and ecl2.classification_id = ecr.classification_id
126 and ecl2.costing_debit_or_credit <>
127 ecl.costing_debit_or_credit);
128 --
129 v_debit_or_credit_code varchar2(30);
130 --
131 begin
132 --
133 -- Make sure that all elements are the same regarding debit or credit
134 -- status NB. this checks for existing element type rules for the element
135 -- set.
136 open csr_debit_or_credit_ele_rule;
137 fetch csr_debit_or_credit_ele_rule into v_debit_or_credit_code;
138 if csr_debit_or_credit_ele_rule%found then
139 close csr_debit_or_credit_ele_rule;
140 hr_utility.set_message(801, 'HR_6547_ELE_SET_CR_OR_DB');
141 hr_utility.set_message_token('CREDIT_OR_DEBIT', '???');
142 hr_utility.raise_error;
143 else
144 close csr_debit_or_credit_ele_rule;
145 end if;
146 --
147 -- Make sure that all elements are the same regarding debit or credit
148 -- status NB. this checks for existing element classification rules for
149 -- the element set.
150 open csr_debit_or_credit_class_rule;
151 fetch csr_debit_or_credit_class_rule into v_debit_or_credit_code;
152 if csr_debit_or_credit_class_rule%found then
153 close csr_debit_or_credit_class_rule;
154 hr_utility.set_message(801, 'HR_6547_ELE_SET_CR_OR_DB');
155 hr_utility.set_message_token('CREDIT_OR_DEBIT', '???');
156 hr_utility.raise_error;
157 else
158 close csr_debit_or_credit_class_rule;
159 end if;
160 --
161 end chk_debit_or_credit;
162 --
163 -----------------------------------------------------------------------------
164 -- Name --
165 -- check_unique_set --
166 -- Purpose --
167 -- Makes sure the element set name is unique within the legislation and --
168 -- business group. --
169 -- Arguments --
170 -- See below. --
171 -- Notes --
172 -- None. --
173 -----------------------------------------------------------------------------
174 --
175 procedure check_unique_set
176 (
177 p_rowid varchar2,
178 p_business_group_id number,
179 p_legislation_code varchar2,
180 p_element_set_name varchar2
181 ) is
182 --
183 cursor csr_unique_set is
184 select els.element_set_id
185 from pay_element_sets els
186 where upper(els.element_set_name) = upper(p_element_set_name)
187 and nvl(els.business_group_id,nvl(p_business_group_id,0)) =
188 nvl(p_business_group_id,0)
189 and nvl(els.legislation_code,nvl(p_legislation_code,' ')) =
190 nvl(p_legislation_code,' ')
191 and (p_rowid is null or
192 (p_rowid is not null and chartorowid(p_rowid) <> els.rowid));
193 --
194 v_dummy number;
195 --
196 begin
197 --
198 open csr_unique_set;
199 fetch csr_unique_set into v_dummy;
200 if csr_unique_set%found then
201 close csr_unique_set;
202 hr_utility.set_message(801, 'HR_6055_ELE_SET_UNIQUE_NAME');
203 hr_utility.raise_error;
204 else
205 close csr_unique_set;
206 end if;
207 --
208 end check_unique_set;
209 --
210 -----------------------------------------------------------------------------
211 -- Name --
212 -- chk_delete_element_set --
213 -- Purpose --
214 -- Checks to see if the element set is being used. --
215 -- Arguments --
216 -- See below. --
217 -- Notes --
218 -- Checks PAY_RESTRICTION_VALUES, PAY_ELEMENT_LINKS_F and --
219 -- PAY_PAYROLL_ACTIONS. --
220 -----------------------------------------------------------------------------
221 --
222 procedure chk_delete_element_set
223 (
224 p_element_set_id number,
225 p_element_set_type varchar2
226 ) is
227 --
228 cursor csr_element_links is
229 select el.element_set_id
230 from pay_element_links_f el
231 where el.element_set_id = p_element_set_id;
232 --
236 where pa.element_set_id = p_element_set_id;
233 cursor csr_payroll_actions is
234 select pa.element_set_id
235 from pay_payroll_actions pa
237 --
238 cursor csr_restriction_values is
239 select fnd_number.canonical_to_number(rv.value)
240 from pay_restriction_values rv
241 where rv.restriction_code = 'ELEMENT_SET'
242 and rv.value = to_char(p_element_set_id);
243 --
244 cursor csr_element_set_usages is
245 select egu.element_set_id
246 from pay_event_group_usages egu
247 where egu.element_set_id = p_element_set_id;
248 --
249 v_dummy number;
250 --
251 begin
252 --
253 -- Element links can only use Distribution Sets.
254 if p_element_set_type = 'D' then
255 --
256 open csr_element_links;
257 fetch csr_element_links into v_dummy;
258 if csr_element_links%found then
259 close csr_element_links;
260 hr_utility.set_message(801, 'HR_6051_ELE_SET_SET_DELETES');
261 hr_utility.raise_error;
262 else
263 close csr_element_links;
264 end if;
265 --
266 -- Payroll actions can only use Run Sets.
267 elsif p_element_set_type = 'R' then
268 --
269 open csr_payroll_actions;
270 fetch csr_payroll_actions into v_dummy;
271 if csr_payroll_actions%found then
272 close csr_payroll_actions;
273 hr_utility.set_message(801, 'HR_6054_ELE_SET_SET_DELETES');
274 hr_utility.raise_error;
275 else
276 close csr_payroll_actions;
277 end if;
278 --
279 -- Forms customization can only use Customization Sets.
280 elsif p_element_set_type = 'C' then
281 --
282 open csr_restriction_values;
283 fetch csr_restriction_values into v_dummy;
284 if csr_restriction_values%found then
285 close csr_restriction_values;
286 hr_utility.set_message(801, 'HR_6050_ELE_SET_SET_DELETES');
287 hr_utility.raise_error;
288 else
289 close csr_restriction_values;
290 end if;
291 elsif p_element_set_type = 'E' then
292
293 open csr_element_set_usages;
294 fetch csr_element_set_usages into v_dummy;
295 if csr_element_set_usages%found then
296 close csr_element_set_usages;
297 hr_utility.set_message(801, 'PAY_294526_ECU_CHILD_EXISTS');
298 hr_utility.raise_error;
299 else
300 close csr_element_set_usages;
301 end if;
302 --
303 end if;
304 --
305 end chk_delete_element_set;
306 --
307 -----------------------------------------------------------------------------
308 -- Name --
309 -- delete_element_set_cascade --
310 -- Purpose --
311 -- Removes all children of an element set. --
312 -- Arguments --
313 -- See below. --
314 -- Notes --
315 -- Removes PAY_ELEMENT_TYPE_RULES and PAY_ELE_CLASSIFICATION_RULES. --
316 -----------------------------------------------------------------------------
317 --
318 procedure delete_element_set_cascade
319 (
320 p_element_set_id number
321 ) is
322 --
323 begin
324 --
325 delete from pay_element_type_rules
326 where element_set_id = p_element_set_id;
327 --
328 delete from pay_ele_classification_rules
329 where element_set_id = p_element_set_id;
330 --
331 end delete_element_set_cascade;
332 --
333 -----------------------------------------------------------------------------
334 -- Name --
335 -- Insert_Row --
336 -- Purpose --
337 -- Table handler procedure that supports the insert of an element set --
338 -- via the Define Element and Distributuion Set form. --
339 -- Arguments --
340 -- See below. --
341 -- Notes --
342 -- None. --
343 -----------------------------------------------------------------------------
344 --
345 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
346 X_Element_Set_Id IN OUT NOCOPY NUMBER,
347 X_Business_Group_Id NUMBER,
348 X_Legislation_Code VARCHAR2,
349 X_Element_Set_Name VARCHAR2,
350 X_Element_Set_Type VARCHAR2,
351 X_Comments VARCHAR2,
352 -- Extra Columns
353 X_Session_Business_Group_Id NUMBER,
354 X_Session_Legislation_Code VARCHAR2) IS
355 --
356 CURSOR C IS SELECT rowid FROM pay_element_sets
357 WHERE element_set_id = X_Element_Set_Id;
358 --
359 CURSOR C2 IS SELECT pay_element_sets_s.nextval FROM sys.dual;
360 --
361 BEGIN
362 --
363 check_unique_set
364 (X_Rowid,
365 X_Session_Business_Group_Id,
366 X_Session_Legislation_Code,
367 X_Element_Set_Name);
371 FETCH C2 INTO X_Element_Set_Id;
368 --
369 if (X_Element_Set_Id is NULL) then
370 OPEN C2;
372 CLOSE C2;
373 end if;
374 --
375 --MLS validation for uniqueness-----------------
376 validate_translation (X_Element_Set_Id,
377 userenv('lang'),
378 X_Element_Set_Name,
379 X_Business_Group_Id,
380 X_Legislation_Code);
381 ---
382 INSERT INTO pay_element_sets
383 (element_set_id,
384 business_group_id,
385 legislation_code,
386 element_set_name,
387 element_set_type,
388 comments
389 )
390 VALUES
391 (X_Element_Set_Id,
392 X_Business_Group_Id,
393 X_Legislation_Code,
394 X_Element_Set_Name,
395 X_Element_Set_Type,
396 X_Comments
397 );
398 --
399 ---For MLS----------------------------------------------------------------------
400 if(PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
401 pay_est_ins.ins_tl(userenv('LANG'),x_element_set_id,x_element_set_name);
402 end if;
403 --------------------------------------------------------------------------------
404 --
405 OPEN C;
406 FETCH C INTO X_Rowid;
407 if (C%NOTFOUND) then
408 CLOSE C;
409 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
410 hr_utility.set_message_token('PROCEDURE',
411 'pay_element_sets_pkg.insert_row');
412 hr_utility.set_message_token('STEP','1');
413 hr_utility.raise_error;
414 end if;
415 CLOSE C;
416 --
417 END Insert_Row;
418 --
419 -----------------------------------------------------------------------------
420 -- Name --
421 -- Lock_Row --
422 -- Purpose --
423 -- Table handler procedure that supports the insert , update and delete --
424 -- of an element set by applying a lock on an element set within the --
425 -- Define Element and Distribution Set form. --
426 -- Arguments --
427 -- See below. --
428 -- None. --
429 -----------------------------------------------------------------------------
430 --
431 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
432 X_Element_Set_Id NUMBER,
433 X_Business_Group_Id NUMBER,
434 X_Legislation_Code VARCHAR2,
435 X_Element_Set_Name VARCHAR2,
436 X_Element_Set_Type VARCHAR2,
437 X_Comments VARCHAR2) IS
438 --
439 CURSOR C IS SELECT * FROM pay_element_sets
440 WHERE rowid = X_Rowid FOR UPDATE of Element_Set_Id NOWAIT;
441 --
442 Recinfo C%ROWTYPE;
443 --
444 BEGIN
445 --
446 OPEN C;
447 FETCH C INTO Recinfo;
448 if (C%NOTFOUND) then
449 CLOSE C;
450 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
451 hr_utility.set_message_token('PROCEDURE',
452 'pay_element_sets_pkg.lock_row');
453 hr_utility.set_message_token('STEP','1');
454 hr_utility.raise_error;
455 end if;
456 CLOSE C;
457 --
458 -- Remove trailing spaces.
459 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
460 Recinfo.element_set_name := rtrim(Recinfo.element_set_name);
461 Recinfo.element_set_type := rtrim(Recinfo.element_set_type);
462 Recinfo.comments := rtrim(Recinfo.comments);
463 --
464 if ( ( (Recinfo.element_set_id = X_Element_Set_Id)
465 OR ( (Recinfo.element_set_id IS NULL)
466 AND (X_Element_Set_Id IS NULL)))
467 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
468 OR ( (Recinfo.business_group_id IS NULL)
469 AND (X_Business_Group_Id IS NULL)))
470 AND ( (Recinfo.legislation_code = X_Legislation_Code)
471 OR ( (Recinfo.legislation_code IS NULL)
472 AND (X_Legislation_Code IS NULL)))
473 AND ( (Recinfo.element_set_name = X_Element_Set_Name)
474 OR ( (Recinfo.element_set_name IS NULL)
475 AND (X_Element_Set_Name IS NULL)))
476 AND ( (Recinfo.element_set_type = X_Element_Set_Type)
477 OR ( (Recinfo.element_set_type IS NULL)
478 AND (X_Element_Set_Type IS NULL)))
479 AND ( (Recinfo.comments = X_Comments)
480 OR ( (Recinfo.comments IS NULL)
481 AND (X_Comments IS NULL)))
482 ) then
483 return;
484 else
485 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
486 APP_EXCEPTION.RAISE_EXCEPTION;
487 end if;
488 --
489 END Lock_Row;
490 --
491 -----------------------------------------------------------------------------
492 -- Name --
493 -- Update_Row --
494 -- Purpose --
495 -- Table handler procedure that supports the update of an element set --
496 -- via the Define Element and Distributuion Set form. --
500 -- None. --
497 -- Arguments --
498 -- See below. --
499 -- Notes --
501 -----------------------------------------------------------------------------
502 --
503 PROCEDURE Update_Row(X_Rowid VARCHAR2,
504 X_Element_Set_Id NUMBER,
505 X_Business_Group_Id NUMBER,
506 X_Legislation_Code VARCHAR2,
507 X_Element_Set_Name VARCHAR2,
508 X_Element_Set_Type VARCHAR2,
509 X_Comments VARCHAR2,
510 -- Extra Columns
511 X_Session_Business_Group_Id NUMBER,
512 X_Session_Legislation_Code VARCHAR2,
513 X_Base_Element_Set_Name in varchar2 default hr_api.g_varchar2) IS
514 BEGIN
515 --
516 check_unique_set
517 (X_Rowid,
518 X_Session_Business_Group_Id,
519 X_Session_Legislation_Code,
520 X_Element_Set_Name);
521 --
522 UPDATE pay_element_sets
523 SET element_set_id = X_Element_Set_Id,
524 business_group_id = X_Business_Group_Id,
525 legislation_code = X_Legislation_Code,
526 element_set_name = X_Element_Set_Name,
527 element_set_type = X_Element_Set_Type,
528 comments = X_Comments
529 WHERE rowid = X_rowid;
530
531 --For MLS-----------------------------------------------------------------------
532 ---For sustaining dual maintenance----------
533 if(PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
534 --MLS validation for uniqueness-----------------
535
536 validate_translation (X_Element_Set_Id,
537 userenv('lang'),
538 X_Element_Set_Name,
539 X_Business_Group_Id,
540 X_Legislation_Code);
541 ---
542 UPDATE pay_element_sets
543 SET element_set_id = X_Element_Set_Id,
544 business_group_id = X_Business_Group_Id,
545 legislation_code = X_Legislation_Code,
546 element_set_name = X_Base_Element_Set_Name,
547 element_set_type = X_Element_Set_Type,
548 comments = X_Comments
549 WHERE rowid = X_rowid;
550 --
551 pay_est_upd.upd_tl(userenv('LANG'),x_element_set_id,X_Element_Set_Name);
552 end if;
553 --------------------------------------------------------------------------------
554 --
555 if (SQL%NOTFOUND) then
556 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
557 hr_utility.set_message_token('PROCEDURE',
558 'pay_element_sets_pkg.update_row');
559 hr_utility.set_message_token('STEP','1');
560 hr_utility.raise_error;
561 end if;
562 --
563 END Update_Row;
564 --
565 -----------------------------------------------------------------------------
566 -- Name --
567 -- Delete_Row --
568 -- Purpose --
569 -- Table handler procedure that supports the delete of an element set --
570 -- via the Define Element and Distributuion Set form. --
571 -- Arguments --
572 -- See below. --
573 -- Notes --
574 -- None. --
575 -----------------------------------------------------------------------------
576 --
577 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
578 -- Extra Columns
579 X_Element_Set_Id NUMBER,
580 X_Element_Set_Type VARCHAR2) IS
581 BEGIN
582 --
583 chk_delete_element_set
584 (X_Element_Set_Id,
585 X_Element_Set_Type);
586 --
587 delete_element_set_cascade
588 (X_Element_Set_Id);
589 --
590 ---For MLS----------------------------------------------------------------------
591 if(PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
592 pay_est_del.del_tl(x_element_set_id);
593 end if;
594 --------------------------------------------------------------------------------
595 --
596 DELETE FROM pay_element_sets
597 WHERE rowid = X_Rowid;
598 --
599 if (SQL%NOTFOUND) then
600 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
601 hr_utility.set_message_token('PROCEDURE',
602 'pay_element_sets_pkg.delete_row');
603 hr_utility.set_message_token('STEP','1');
604 hr_utility.raise_error;
605 end if;
606 --
607 END Delete_Row;
608 --
609 --MLS Additions---
610 --
611 procedure ADD_LANGUAGE
612 is
613 begin
614 delete from PAY_ELEMENT_SETS_TL T
615 where not exists
616 (select NULL
617 from PAY_ELEMENT_SETS B
618 where B.ELEMENT_SET_ID = T.ELEMENT_SET_ID
619 );
620 update PAY_ELEMENT_SETS_TL T
621 set (ELEMENT_SET_NAME) =
622 (select B.ELEMENT_SET_NAME
623 from PAY_ELEMENT_SETS_TL B
624 where B.ELEMENT_SET_ID = T.ELEMENT_SET_ID
625 and B.LANGUAGE = T.SOURCE_LANG)
626 where (T.ELEMENT_SET_ID,T.LANGUAGE) in
627 (select SUBT.ELEMENT_SET_ID,SUBT.LANGUAGE
628 from PAY_ELEMENT_SETS_TL SUBB, PAY_ELEMENT_SETS_TL SUBT
629 where SUBB.ELEMENT_SET_ID = SUBT.ELEMENT_SET_ID
630 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
631 and (SUBB.ELEMENT_SET_NAME <> SUBT.ELEMENT_SET_NAME
632 ));
633
634 insert into PAY_ELEMENT_SETS_TL (
635 ELEMENT_SET_ID,
636 ELEMENT_SET_NAME,
637 LAST_UPDATE_DATE,
638 LAST_UPDATED_BY,
639 LAST_UPDATE_LOGIN,
640 CREATED_BY,
641 CREATION_DATE,
642 LANGUAGE,
643 SOURCE_LANG
644 ) select
645 B.ELEMENT_SET_ID,
646 B.ELEMENT_SET_NAME,
647 B.LAST_UPDATE_DATE,
648 B.LAST_UPDATED_BY,
649 B.LAST_UPDATE_LOGIN,
650 B.CREATED_BY,
651 B.CREATION_DATE,
652 L.LANGUAGE_CODE,
653 B.SOURCE_LANG
654 from PAY_ELEMENT_SETS_TL B, FND_LANGUAGES L
655 where L.INSTALLED_FLAG in ('I', 'B')
656 and B.LANGUAGE = userenv('LANG')
657 and not exists
658 (select NULL
659 from PAY_ELEMENT_SETS_TL T
660 where T.ELEMENT_SET_ID = B.ELEMENT_SET_ID
661 and T.LANGUAGE = L.LANGUAGE_CODE);
662 end ADD_LANGUAGE;
663
664 --
665 procedure TRANSLATE_ROW (X_B_ELEMENT_SET_NAME in VARCHAR2,
666 X_B_LEGISLATION_CODE in VARCHAR2,
667 X_ELEMENT_SET_NAME in VARCHAR2,
668 X_OWNER in VARCHAR2) is
669 begin
670 UPDATE PAY_ELEMENT_SETS_TL
671 SET ELEMENT_SET_NAME = nvl(X_ELEMENT_SET_NAME,ELEMENT_SET_NAME),
672 last_update_date = SYSDATE,
673 last_updated_by = decode(x_owner,'SEED',1,0),
674 last_update_login = 0,
675 source_lang = userenv('LANG')
676 WHERE userenv('LANG') IN (language,source_lang)
677 AND ELEMENT_SET_ID in
678 (select ELEMENT_SET_ID
679 from PAY_ELEMENT_SETS
680 where nvl(ELEMENT_SET_NAME,'~null~')=nvl(X_B_ELEMENT_SET_NAME,'~null~')
681 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
682 and BUSINESS_GROUP_ID is NULL);
683 if (sql%notfound) then
684 null;
685 end if;
686 end TRANSLATE_ROW;
687 --
688 --
689 procedure set_translation_globals( p_business_group_id IN NUMBER
690 , p_legislation_code IN NUMBER) IS
691 BEGIN
692 g_business_group_id := p_business_group_id;
693 g_legislation_code := p_legislation_code;
694 END;
695 --
696 procedure validate_translation(element_set_id NUMBER,
697 language VARCHAR2,
698 element_set_name VARCHAR2,
699 p_business_group_id IN NUMBER DEFAULT NULL,
700 p_legislation_code IN VARCHAR2 DEFAULT NULL) IS
701 /*
702
703 This procedure fails if a user_table translation is already present in
704 the table for a given language. Otherwise, no action is performed. It is
705 used to ensure uniqueness of translated user_table names.
706
707 */
708
709 --
710 -- This cursor implements the validation we require,
711 -- and expects that the various package globals are set before
712 -- the call to this procedure is made. This is done from the
713 -- user-named trigger 'TRANSLATIONS' in the form
714 --
715 cursor c_translation(p_language IN VARCHAR2,
716 p_element_set_name IN VARCHAR2,
717 p_element_set_id IN NUMBER,
718 p_bus_grp_id IN NUMBER,
719 p_leg_code IN varchar2) IS
720 SELECT 1
721 FROM pay_element_sets_tl est,
722 pay_element_sets els
723 WHERE upper(est.element_set_name)=upper(p_element_set_name)
724 AND est.element_set_id = els.element_set_id
725 AND est.language = p_language
726 AND (els.element_set_id <> p_element_set_id OR p_element_set_id IS NULL)
727 AND (nvl(els.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
728 AND (nvl(els.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);
729
730 l_package_name VARCHAR2(80);
731 l_business_group_id NUMBER;
732 l_legislation_code VARCHAR2(150);
733
734 BEGIN
735 l_package_name := 'PAY_ELEMENT_SETS_PKG.VALIDATE_TRANSLATION';
736 l_business_group_id := p_business_group_id;
737 l_legislation_code := p_legislation_code;
738 hr_utility.set_location (l_package_name,10);
739 OPEN c_translation(language, element_set_name,element_set_id,
740 l_business_group_id,l_legislation_code);
741 hr_utility.set_location (l_package_name,50);
742 FETCH c_translation INTO g_dummy;
743
744 IF c_translation%NOTFOUND THEN
745 hr_utility.set_location (l_package_name,60);
746 CLOSE c_translation;
747 ELSE
748 hr_utility.set_location (l_package_name,70);
749 CLOSE c_translation;
750 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
751 fnd_message.raise_error;
752 END IF;
753 hr_utility.set_location ('Leaving:'||l_package_name,80);
754 END validate_translation;
755
756 --
757 function return_dml_status
758 return boolean
759 IS
760 begin
761 return g_dml_status;
762 end return_dml_status;
763 --
764 --
765 END PAY_ELEMENT_SETS_PKG;
[Home] [Help]