[Home] [Help]
PACKAGE BODY: APPS.PAY_BALANCE_TYPES_PKG
Source
1 PACKAGE BODY PAY_BALANCE_TYPES_PKG as
2 /* $Header: pyblt01t.pkb 120.1 2005/11/07 09:45:38 arashid noship $ */
3 --
4 -- dummy variable for values returned from cursors that are not needed
5 --
6 g_dummy_number number(30);
7 --
8 g_business_group_id number(15); -- For validating translation.
9 g_legislation_code varchar2(150);-- For validating translation.
10 --
11 -------------------------------------------------------------------------------
12 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
13 p_legislation_code IN VARCHAR2) IS
14 BEGIN
15 g_business_group_id := p_business_group_id;
16 g_legislation_code := p_legislation_code;
17 END;
18 -------------------------------------------------------------------------------
19 procedure validate_translation(balance_type_id IN NUMBER,
20 language IN VARCHAR2,
21 balance_name IN VARCHAR2,
22 reporting_name IN VARCHAR2) IS
23 /*
24
25 This procedure fails if a balance name or reporting name translation is already
26 present in the table for a given language. Otherwise, no action is performed.
27 It is used to ensure uniqueness of translated balance and reporting names.
28
29 */
30
31 --
32 -- This cursor implements the validation we require,
33 -- and expects that the various package globals are set before
34 -- the call to this procedure is made. This is done from the
35 -- user-named trigger 'TRANSLATIONS' in the form
36
37
38 cursor c_translation(p_language IN VARCHAR2,
39 p_balance_name IN VARCHAR2,
40 p_reporting_name IN VARCHAR2,
41 p_balance_type_id IN NUMBER,
42 p_mode IN VARCHAR2) IS
43 SELECT 1
44 FROM pay_balance_types_tl bttl,
45 pay_balance_types bt
46 WHERE ((p_mode = 'BALANCE_NAME' and
47 upper(bttl.balance_name) = upper(translate(p_balance_name,
48 '_',' '))) or
49 (p_mode = 'REPORTING_NAME' and
50 upper(bttl.reporting_name) = upper(translate(p_reporting_name,
51 '_',' '))))
52 AND bttl.balance_type_id = bt.balance_type_id
53 AND bttl.language = p_language
54 AND ( bt.balance_type_id <> p_balance_type_id OR p_balance_type_id is null )
55 AND ( g_business_group_id = bt.business_group_id + 0 OR g_business_group_id is null )
56 AND ( g_legislation_code = bt.legislation_code OR g_legislation_code is null );
57
58
59 l_package_name VARCHAR2(80) := 'PAY_BALANCE_TYPES_PKG.VALIDATE_TRANSLATION';
60 l_name pay_balance_types.balance_name%type := balance_name;
61 l_dummy varchar2(100);
62
63 BEGIN
64 hr_utility.set_location (l_package_name,1);
65
66 BEGIN
67 hr_chkfmt.checkformat (l_name,
68 'PAY_NAME',
69 l_dummy, null, null, 'N', l_dummy, null);
70 hr_utility.set_location (l_package_name,2);
71
72 EXCEPTION
73 when app_exception.application_exception then
74 hr_utility.set_location (l_package_name,3);
75 fnd_message.set_name ('PAY','PAY_6365_ELEMENT_NO_DB_NAME'); -- checkformat failure
76 fnd_message.raise_error;
77 END;
78
79 hr_utility.set_location (l_package_name,10);
80 OPEN c_translation(language
81 , balance_name
82 , reporting_name
83 , balance_type_id
84 , 'BALANCE_NAME');
85 hr_utility.set_location (l_package_name,20);
86
87 FETCH c_translation INTO g_dummy_number;
88 hr_utility.set_location (l_package_name,25);
89
90 IF c_translation%NOTFOUND THEN
91 hr_utility.set_location (l_package_name,30);
92 CLOSE c_translation;
93 ELSE
94 hr_utility.set_location (l_package_name,40);
95 CLOSE c_translation;
96 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
97 fnd_message.raise_error;
98 END IF;
99
100 OPEN c_translation(language
101 , balance_name
102 , reporting_name
103 , balance_type_id
104 , 'REPORTING_NAME');
105 hr_utility.set_location (l_package_name,50);
106 FETCH c_translation INTO g_dummy_number;
107 hr_utility.set_location (l_package_name,55);
108
109 IF c_translation%NOTFOUND THEN
110 hr_utility.set_location (l_package_name,60);
111 CLOSE c_translation;
112 ELSE
113 hr_utility.set_location (l_package_name,70);
114 CLOSE c_translation;
115 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
116 fnd_message.raise_error;
117 END IF;
118
119 hr_utility.set_location ('Leaving: '||l_package_name,140);
120 END validate_translation;
121
122 -----------------------------------------------------------------------------
123 -- Name
124 -- chk_balance_category_rule
125 --
126 -- Purpose
127 -- Checks whether column balance_category_id is mandatory for the current
128 -- legislation. It will only be mandatory when the legislation delivers
129 -- the legislation rule row and an upgrade script for populating all balances
130 -- with a balance category.
131 -----------------------------------------------------------------------------
132 function chk_balance_category_rule
133 (p_legislation_code varchar2
134 ,p_business_group_id number default null
135 )
136 return boolean is
137 --
138 l_proc varchar2(72) := 'pay_balance_types_pkg.chk_balance_category';
139 l_leg_code pay_balance_types.legislation_code%type;
140 --
141 cursor get_legislation(p_bg_id number)
142 is
143 select pbg.legislation_code
144 from per_business_groups pbg
145 where pbg.business_group_id = p_bg_id;
146 --
147 cursor get_leg_rule(p_leg_code varchar2)
148 is
149 select rule_mode
150 from pay_legislation_rules
151 where rule_type = 'BAL_CATEGORY_MANDATORY'
152 and legislation_code = p_leg_code;
153 --
154 l_rule_mode pay_legislation_rules.rule_mode%type;
155 --
156 begin
157 hr_utility.set_location('Entering '||l_proc, 5);
158 --
159 hr_utility.trace('leg code '||p_legislation_code);
160 hr_utility.trace('bg: '||to_char(p_business_group_id));
161 if p_legislation_code is null then
162 if p_business_group_id is not null then
163 open get_legislation(p_business_group_id);
164 fetch get_legislation into l_leg_code;
165 if get_legislation%notfound then
166 --
167 close get_legislation;
168 hr_utility.set_location(l_proc, 10);
169 hr_utility.set_message(801, 'PAY_34260_BG_HAS_NO_LEG');
170 hr_utility.raise_error;
171 --
172 else
173 hr_utility.set_location(l_proc, 15);
174 close get_legislation;
175 end if;
176 else -- bg is null, so global row being checked, category cannot be mandatory
177 hr_utility.set_location(l_proc, 20);
178 return false;
179 end if;
180 else -- p_legislation is not null
181 hr_utility.set_location(l_proc, 25);
182 l_leg_code := p_legislation_code;
183 end if;
184 --
185 -- check the legislation_rule BAL_CATEGORY_MANDATORY
186 --
187 open get_leg_rule(l_leg_code);
188 fetch get_leg_rule into l_rule_mode;
189 if get_leg_rule%notfound
190 or l_rule_mode = 'N' then
191 --
192 hr_utility.set_location(l_proc, 30);
193 close get_leg_rule;
194 return false;
195 --
196 else
197 hr_utility.set_location(l_proc, 35);
198 close get_leg_rule;
199 return true;
200 end if;
201 end chk_balance_category_rule;
202 -----------------------------------------------------------------------------
203 -- Name --
204 -- chk_balance_type --
205 -- Purpose --
206 -- Validates the balance type ie. unique name, only one remuneration --
207 -- balance etc ... --
208 -- Arguments --
209 -- See below. --
210 -- Notes --
211 -- --
212 -----------------------------------------------------------------------------
213 --
214 procedure chk_balance_type
215 (
216 p_row_id varchar2,
217 p_business_group_id number,
218 p_legislation_code varchar2,
219 p_balance_name varchar2,
220 p_reporting_name varchar2,
221 p_assignment_remuneration_flag varchar2
222 ) is
223 --
224 v_bal_type_id number;
225 v_result_text varchar2(80);
226 v_bg_leg_code varchar2(30);
227 --
228 cursor csr_unique_check(p_mode varchar2) is
229 select bt.balance_type_id
230 from pay_balance_types bt
231 , per_business_groups_perf bg
232 where ((p_mode = 'BALANCE_NAME'
233 and upper(bt.balance_name) = upper(translate(p_balance_name,'_',' ')))
234 or (p_mode = 'REPORTING_NAME'
235 and upper(bt.reporting_name) = upper(translate(p_reporting_name,'_',' ')))
236 or (p_mode = 'ASSIGNMENT_RENUMERATION_ALLOWED_FLAG'
237 and bt.assignment_remuneration_flag = 'Y'))
238 and bt.business_group_id = bg.business_group_id (+)
239 and ((p_business_group_id is not null
240 and nvl(bt.business_group_id,-1) = p_business_group_id
241 or nvl(bt.legislation_code,' ') = v_bg_leg_code)
242 or (p_legislation_code is not null
243 and nvl(bt.legislation_code,' ') = p_legislation_code
244 or bt.business_group_id is not null
245 and bt.legislation_code = p_legislation_code)
246 or bt.business_group_id is null
247 and bt.legislation_code is null)
248 and (p_row_id is null
249 or (p_row_id is not null
250 and chartorowid(p_row_id) <> bt.rowid));
251 --
252 cursor csr_bg_leg_code is
253 select legislation_code
254 from per_business_groups
255 where business_group_id = p_business_group_id;
256 --
257 begin
258 --
259 if ((p_business_group_id is not null) and
260 (p_legislation_code is null)) then
261 open csr_bg_leg_code;
262 fetch csr_bg_leg_code into v_bg_leg_code;
263 close csr_bg_leg_code;
264 else
265 v_bg_leg_code := p_legislation_code;
266 end if;
267 --
268 -- BALANCE_NAME has been set.
269 if p_balance_name is not null then
270 --
271 -- Make sure format of BALANCE_NAME is correct ie. can create an database
272 -- item.
273 begin
274 v_result_text := p_balance_name;
275 hr_chkfmt.checkformat
276 (v_result_text,
277 'PAY_NAME',
278 v_result_text,
279 null,
280 null,
281 'N',
282 v_result_text,
283 null);
284 exception
285 when hr_utility.hr_error then
286 hr_utility.set_message(801, 'HR_6016_ALL_RES_WORDS');
287 hr_utility.set_message_token('VALUE_NAME', 'This');
288 raise;
289 end;
290 --
291 -- Make sure balance name being created is unique within BG / LEG CODE.
292 open csr_unique_check('BALANCE_NAME');
293 fetch csr_unique_check into v_bal_type_id;
294 if csr_unique_check%found then
295 close csr_unique_check;
296 hr_utility.set_message(801, 'HR_6108_BAL_UNI_BALANCE');
297 hr_utility.raise_error;
298 else
299 close csr_unique_check;
300 end if;
301 --
302 end if;
303 --
304 -- REPORTING_NAME has been set.
305 if p_reporting_name is not null then
306 --
307 --
308 -- Make sure reporting name being created is unique within BG / LEG CODE.
309 open csr_unique_check('REPORTING_NAME');
310 fetch csr_unique_check into v_bal_type_id;
311 if csr_unique_check%found then
312 close csr_unique_check;
313 hr_utility.set_message(801, 'HR_6108_BAL_UNI_BALANCE');
314 hr_utility.raise_error;
315 else
316 close csr_unique_check;
317 end if;
318 --
319 end if;
320 --
321 -- ASSIGNMENT_RENUMERATION_ALLOWED_FLAG has been set to 'Y'.
322 if p_assignment_remuneration_flag = 'Y' then
323 --
324 -- Make sure there is only one balance that can be used for remuneration
325 -- within BG / LEG CODE.
326 open csr_unique_check('ASSIGNMENT_RENUMERATION_ALLOWED_FLAG');
327 fetch csr_unique_check into v_bal_type_id;
328 if csr_unique_check%found then
329 close csr_unique_check;
330 hr_utility.set_message(801, 'HR_6957_PAY_ONLY_ONE_RENUM');
331 hr_utility.raise_error;
332 else
333 close csr_unique_check;
334 end if;
335 --
336 end if;
337 --
338 end chk_balance_type;
339 --
340 -----------------------------------------------------------------------------
341 -- Name --
342 -- balance_type_cascade_delete --
343 -- Purpose --
344 -- Removes children of balance type on removal of a balance. --
345 -- Arguments --
346 -- See below. --
347 -- Notes --
348 -- --
349 -----------------------------------------------------------------------------
350 --
351 procedure balance_type_cascade_delete
352 (
353 p_balance_type_id number
354 ) is
355 --
356 cursor get_pbas(p_def_bal number) is
357 select balance_attribute_id
358 from pay_balance_attributes
359 where defined_balance_id = p_def_bal;
360 --
361 cursor csr_def_bals is
362 select db.defined_balance_id
363 from pay_defined_balances db
364 where db.balance_type_id = p_balance_type_id
365 for update;
366 --
367 begin
368 --
369 hr_utility.set_location('Entering balance_type_cascade_delete', 5);
370 --
371 delete from pay_balance_feeds_f bf
372 where bf.balance_type_id = p_balance_type_id;
373 --
374 hr_utility.set_location('balance_type_cascade_delete', 10);
375 --
376 delete from pay_balance_classifications bc
377 where bc.balance_type_id = p_balance_type_id;
378 --
379 hr_utility.set_location('balance_type_cascade_delete', 15);
380 --
381 for v_db_rec in csr_def_bals loop
382 --
383 hr_utility.set_location('balance_type_cascade_delete', 20);
384 --
385 -- Make sure defined balance is not used by an organization payment
386 -- method or a backpay set.
387 pay_defined_balances_pkg.chk_delete_defined_balance
388 (v_db_rec.defined_balance_id);
389 --
390 -- need to delete child rows of pay_defined_balances. User entities are don
391 -- in trigger pay_defined_balances_brd, going to delete for new table
392 -- pay_balance_attributes here.
393 --
394 hr_utility.set_location('balance_type_cascade_delete',2);
395 --
396 for each_pba in get_pbas(v_db_rec.defined_balance_id) loop
397 pay_balance_attribute_api.delete_balance_attribute
398 (p_balance_attribute_id => each_pba.balance_attribute_id);
399 end loop;
400 --
401 hr_utility.set_location('balance_type_cascade_delete',3);
402 delete from pay_defined_balances
403 where current of csr_def_bals;
404 --
405 end loop;
406 --
407 end balance_type_cascade_delete;
408 --
409 -----------------------------------------------------------------------------
410 -- Name --
411 -- Insert_Row --
412 -- Purpose --
413 -- Table handler procedure that supports the insert of a balance via the --
414 -- Define Balance Type form. --
415 -- Arguments --
416 -- See below. --
417 -- Notes --
418 -- --
419 -----------------------------------------------------------------------------
420 --
421 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
422 X_Balance_Type_Id IN OUT NOCOPY NUMBER,
423 X_Business_Group_Id NUMBER,
424 X_Legislation_Code VARCHAR2,
425 X_Currency_Code VARCHAR2,
426 X_Assignment_Remuneration_Flag VARCHAR2,
427 X_Balance_Name VARCHAR2,
428 -- --
429 X_Base_Balance_Name VARCHAR2,
430 -- --
431 X_Balance_Uom VARCHAR2,
432 X_Comments VARCHAR2,
433 X_Legislation_Subgroup VARCHAR2,
434 X_Reporting_Name VARCHAR2,
435 X_Attribute_Category VARCHAR2,
436 X_Attribute1 VARCHAR2,
437 X_Attribute2 VARCHAR2,
438 X_Attribute3 VARCHAR2,
439 X_Attribute4 VARCHAR2,
440 X_Attribute5 VARCHAR2,
441 X_Attribute6 VARCHAR2,
442 X_Attribute7 VARCHAR2,
443 X_Attribute8 VARCHAR2,
444 X_Attribute9 VARCHAR2,
445 X_Attribute10 VARCHAR2,
446 X_Attribute11 VARCHAR2,
447 X_Attribute12 VARCHAR2,
448 X_Attribute13 VARCHAR2,
449 X_Attribute14 VARCHAR2,
450 X_Attribute15 VARCHAR2,
451 X_Attribute16 VARCHAR2,
452 X_Attribute17 VARCHAR2,
453 X_Attribute18 VARCHAR2,
454 X_Attribute19 VARCHAR2,
455 X_Attribute20 VARCHAR2,
456 x_balance_category_id number default null,
457 x_base_balance_type_id number default null,
458 x_input_value_id number default null)
459 IS
460 --
461 CURSOR C IS SELECT rowid FROM pay_balance_types
462 WHERE balance_type_id = X_Balance_Type_Id;
463 --
464 CURSOR C2 IS SELECT pay_balance_types_s.nextval FROM sys.dual;
465 --
466 BEGIN
467 --
468 -- Make sure balance type is valid ie. unique name only one remuneration
469 -- balance etc ...
470 chk_balance_type
471 (X_Rowid,
472 X_Business_Group_Id,
473 X_Legislation_Code,
474 X_Balance_Name,
475 X_Reporting_Name,
476 X_Assignment_Remuneration_Flag);
477 --
478 -- Check if balance_category should be mandatory
479 --
480 if chk_balance_category_rule(x_legislation_code
481 ,x_business_group_id) then
482 --
483 if x_balance_category_id is null then
484 --
485 hr_utility.set_location('pay_balance_types_pkg.insert_row', 10);
486 hr_utility.set_message(801, 'PAY_34261_CAT_IS_MANDATORY');
487 hr_utility.raise_error;
488 --
489 end if;
490 end if;
491 --
492 if (X_Balance_Type_Id is NULL) then
493 OPEN C2;
494 FETCH C2 INTO X_Balance_Type_Id;
495 CLOSE C2;
496 end if;
497 --
498 INSERT INTO pay_balance_types
499 (balance_type_id,
500 business_group_id,
501 legislation_code,
502 currency_code,
503 assignment_remuneration_flag,
504 balance_name,
505 balance_uom,
506 comments,
507 legislation_subgroup,
508 reporting_name,
509 attribute_category,
510 attribute1,
511 attribute2,
512 attribute3,
513 attribute4,
514 attribute5,
515 attribute6,
516 attribute7,
517 attribute8,
518 attribute9,
519 attribute10,
520 attribute11,
521 attribute12,
522 attribute13,
523 attribute14,
524 attribute15,
525 attribute16,
526 attribute17,
527 attribute18,
528 attribute19,
529 attribute20,
530 balance_category_id,
531 base_balance_type_id,
532 input_value_id)
533 VALUES
534 (X_Balance_Type_Id,
535 X_Business_Group_Id,
536 X_Legislation_Code,
537 X_Currency_Code,
538 X_Assignment_Remuneration_Flag,
539 --X_Balance_Name,
540 -- --
541 X_Base_Balance_Name,
542 -- --
543 X_Balance_Uom,
544 X_Comments,
545 X_Legislation_Subgroup,
546 X_Reporting_Name,
547 X_Attribute_Category,
548 X_Attribute1,
549 X_Attribute2,
550 X_Attribute3,
551 X_Attribute4,
552 X_Attribute5,
553 X_Attribute6,
554 X_Attribute7,
555 X_Attribute8,
556 X_Attribute9,
557 X_Attribute10,
558 X_Attribute11,
559 X_Attribute12,
560 X_Attribute13,
561 X_Attribute14,
562 X_Attribute15,
563 X_Attribute16,
564 X_Attribute17,
565 X_Attribute18,
566 X_Attribute19,
567 X_Attribute20,
568 x_balance_category_id,
569 x_base_balance_type_id,
570 x_input_value_id);
571 --
572 -- **************************************************************************
573 -- insert into MLS table (TL)
574 --
575 insert into PAY_BALANCE_TYPES_TL (
576 BALANCE_TYPE_ID,
577 BALANCE_NAME,
578 REPORTING_NAME,
579 LAST_UPDATE_DATE,
580 CREATION_DATE,
581 LANGUAGE,
582 SOURCE_LANG
583 ) select
584 X_Balance_Type_Id,
585 X_Balance_Name,
586 X_Reporting_Name,
587 sysdate,
588 sysdate,
589 L.LANGUAGE_CODE,
590 userenv('LANG')
591 from FND_LANGUAGES L
592 where L.INSTALLED_FLAG in ('I', 'B')
593 and not exists
594 (select NULL
595 from PAY_BALANCE_TYPES_TL T
596 where T.BALANCE_TYPE_ID = X_Balance_Type_Id
597 and T.LANGUAGE = L.LANGUAGE_CODE);
598 --
599 --
600 -- *******************************************************************************
601 --
602 OPEN C;
603 FETCH C INTO X_Rowid;
604 if (C%NOTFOUND) then
605 CLOSE C;
606 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
607 hr_utility.set_message_token('PROCEDURE',
608 'pay_balance_types_pkg.insert_row');
609 hr_utility.set_message_token('STEP','1');
610 hr_utility.raise_error;
611 end if;
612 CLOSE C;
613 --
614 END Insert_Row;
615 --
616 -----------------------------------------------------------------------------
617 -- Name --
618 -- Lock_Row --
619 -- Purpose --
620 -- Table handler procedure that supports the insert , update and delete --
621 -- of a balance by applying a lock on a balance in the Define Balance --
622 -- Type form. --
623 -- Arguments --
624 -- See below. --
625 -- Notes --
626 -- None. --
627 -----------------------------------------------------------------------------
628 --
629 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
630 X_Balance_Type_Id NUMBER,
631 X_Business_Group_Id NUMBER,
632 X_Legislation_Code VARCHAR2,
633 X_Currency_Code VARCHAR2,
634 X_Assignment_Remuneration_Flag VARCHAR2,
635 --X_Balance_Name VARCHAR2,
636 -- --
637 X_Base_Balance_Name VARCHAR2,
638 -- --
639 X_Balance_Uom VARCHAR2,
640 X_Comments VARCHAR2,
641 X_Legislation_Subgroup VARCHAR2,
642 X_Reporting_Name VARCHAR2,
643 X_Attribute_Category VARCHAR2,
644 X_Attribute1 VARCHAR2,
645 X_Attribute2 VARCHAR2,
646 X_Attribute3 VARCHAR2,
647 X_Attribute4 VARCHAR2,
648 X_Attribute5 VARCHAR2,
649 X_Attribute6 VARCHAR2,
650 X_Attribute7 VARCHAR2,
651 X_Attribute8 VARCHAR2,
652 X_Attribute9 VARCHAR2,
653 X_Attribute10 VARCHAR2,
654 X_Attribute11 VARCHAR2,
655 X_Attribute12 VARCHAR2,
656 X_Attribute13 VARCHAR2,
657 X_Attribute14 VARCHAR2,
658 X_Attribute15 VARCHAR2,
659 X_Attribute16 VARCHAR2,
660 X_Attribute17 VARCHAR2,
661 X_Attribute18 VARCHAR2,
662 X_Attribute19 VARCHAR2,
663 X_Attribute20 VARCHAR2,
664 x_balance_category_id number default null,
665 x_base_balance_type_id number default null,
666 x_input_value_id number default null)
667 IS
668 --
669 CURSOR C IS SELECT * FROM pay_balance_types
670 WHERE rowid = X_Rowid FOR UPDATE of Balance_Type_Id NOWAIT;
671 --
672 --
673 -- ***************************************************************************
674 -- cursor for MLS
675 --
676 cursor csr_balance_type_tl is
677 select BALANCE_NAME,
678 REPORTING_NAME,
679 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
680 from PAY_BALANCE_TYPES_TL
681 where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID
682 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
683 for update of BALANCE_TYPE_ID nowait;
684 --
685 -- ***************************************************************************
686 l_mls_count NUMBER :=0;
687 --
688 Recinfo C%ROWTYPE;
689 --
690 BEGIN
691 --
692 OPEN C;
693 FETCH C INTO Recinfo;
694 if (C%NOTFOUND) then
695 CLOSE C;
696 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
697 hr_utility.set_message_token('PROCEDURE',
698 'pay_balance_types_pkg.lock_row');
699 hr_utility.set_message_token('STEP','1');
700 hr_utility.raise_error;
701 end if;
702 CLOSE C;
703 --
704 /** sbilling **/
705 -- removed explicit lock of _TL table,
706 -- the MLS strategy requires that the base table is locked before update of the
707 -- _TL table can take place,
708 -- which implies it is not necessary to lock both tables.
709 -- ***************************************************************************
710 -- code for MLS
711 --
712 -- for tlinfo in csr_balance_type_tl LOOP
713 -- l_mls_count := l_mls_count +1;
714 -- if (tlinfo.BASELANG = 'Y') then
715 -- if ( (tlinfo.BALANCE_NAME = X_BALANCE_NAME)
716 -- AND ((tlinfo.REPORTING_NAME = X_REPORTING_NAME)
717 -- OR ((tlinfo.REPORTING_NAME is null) AND (X_REPORTING_NAME is null)))
718 -- ) then
719 -- null;
720 -- else
721 -- fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
722 -- app_exception.raise_exception;
723 -- end if;
724 -- end if;
725 --end loop;
726 --
727 --if (l_mls_count=0) then -- Trap system errors
728 -- close csr_balance_type_tl;
729 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
730 -- hr_utility.set_message_token ('PROCEDURE','PAY_BALANCE_TYPES_PKG.LOCK_TL_ROW');
731 --end if;
732 --
733 -- ***************************************************************************
734 -- Remove trailing spaces.
735 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
736 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
737 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
738 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
739 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
740 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
741 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
742 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
743 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
744 Recinfo.currency_code := rtrim(Recinfo.currency_code);
745 Recinfo.assignment_remuneration_flag :=
746 rtrim(Recinfo.assignment_remuneration_flag);
747 Recinfo.balance_name := rtrim(Recinfo.balance_name);
748 Recinfo.balance_uom := rtrim(Recinfo.balance_uom);
749 Recinfo.comments := rtrim(Recinfo.comments);
750 Recinfo.legislation_subgroup := rtrim(Recinfo.legislation_subgroup);
751 Recinfo.reporting_name := rtrim(Recinfo.reporting_name);
752 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
753 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
754 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
755 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
756 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
757 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
758 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
759 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
760 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
761 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
762 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
763 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
764 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
765 Recinfo.balance_category_id := rtrim(Recinfo.balance_category_id);
766 Recinfo.base_balance_type_id := rtrim(Recinfo.base_balance_type_id);
767 Recinfo.input_value_id := rtrim(Recinfo.input_value_id);
768 --
769 if ( ( (Recinfo.balance_type_id = X_Balance_Type_Id)
770 OR ( (Recinfo.balance_type_id IS NULL)
771 AND (X_Balance_Type_Id IS NULL)))
772 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
773 OR ( (Recinfo.business_group_id IS NULL)
774 AND (X_Business_Group_Id IS NULL)))
775 AND ( (Recinfo.legislation_code = X_Legislation_Code)
776 OR ( (Recinfo.legislation_code IS NULL)
777 AND (X_Legislation_Code IS NULL)))
778 AND ( (Recinfo.currency_code = X_Currency_Code)
779 OR ( (Recinfo.currency_code IS NULL)
780 AND (X_Currency_Code IS NULL)))
781 AND ( (Recinfo.assignment_remuneration_flag =
782 X_Assignment_Remuneration_Flag)
783 OR ( (Recinfo.assignment_remuneration_flag IS NULL)
784 AND (X_Assignment_Remuneration_Flag IS NULL)))
785 -- --
786 -- AND ( (Recinfo.balance_name = X_Balance_Name)
787 -- OR ( (Recinfo.balance_name IS NULL)
788 -- AND (X_Balance_Name IS NULL)))
789 AND ( (Recinfo.balance_name = X_Base_Balance_Name)
790 OR ( (Recinfo.balance_name IS NULL)
791 AND (X_Base_Balance_Name IS NULL)))
792 -- --
793 AND ( (Recinfo.balance_uom = X_Balance_Uom)
794 OR ( (Recinfo.balance_uom IS NULL)
795 AND (X_Balance_Uom IS NULL)))
796 AND ( (Recinfo.comments = X_Comments)
797 OR ( (Recinfo.comments IS NULL)
798 AND (X_Comments IS NULL)))
799 AND ( (Recinfo.legislation_subgroup = X_Legislation_Subgroup)
800 OR ( (Recinfo.legislation_subgroup IS NULL)
801 AND (X_Legislation_Subgroup IS NULL)))
802 AND ( (Recinfo.reporting_name = X_Reporting_Name)
803 OR ( (Recinfo.reporting_name IS NULL)
804 AND (X_Reporting_Name IS NULL)))
805 AND ( (Recinfo.attribute_category = X_Attribute_Category)
806 OR ( (Recinfo.attribute_category IS NULL)
807 AND (X_Attribute_Category IS NULL)))
808 AND ( (Recinfo.attribute1 = X_Attribute1)
809 OR ( (Recinfo.attribute1 IS NULL)
810 AND (X_Attribute1 IS NULL)))
811 AND ( (Recinfo.attribute2 = X_Attribute2)
812 OR ( (Recinfo.attribute2 IS NULL)
813 AND (X_Attribute2 IS NULL)))
814 AND ( (Recinfo.attribute3 = X_Attribute3)
815 OR ( (Recinfo.attribute3 IS NULL)
816 AND (X_Attribute3 IS NULL)))
817 AND ( (Recinfo.attribute4 = X_Attribute4)
818 OR ( (Recinfo.attribute4 IS NULL)
819 AND (X_Attribute4 IS NULL)))
820 AND ( (Recinfo.attribute5 = X_Attribute5)
821 OR ( (Recinfo.attribute5 IS NULL)
822 AND (X_Attribute5 IS NULL)))
823 AND ( (Recinfo.attribute6 = X_Attribute6)
824 OR ( (Recinfo.attribute6 IS NULL)
825 AND (X_Attribute6 IS NULL)))
826 AND ( (Recinfo.attribute7 = X_Attribute7)
827 OR ( (Recinfo.attribute7 IS NULL)
828 AND (X_Attribute7 IS NULL)))
829 AND ( (Recinfo.attribute8 = X_Attribute8)
830 OR ( (Recinfo.attribute8 IS NULL)
831 AND (X_Attribute8 IS NULL)))
832 AND ( (Recinfo.attribute9 = X_Attribute9)
833 OR ( (Recinfo.attribute9 IS NULL)
834 AND (X_Attribute9 IS NULL)))
835 AND ( (Recinfo.attribute10 = X_Attribute10)
836 OR ( (Recinfo.attribute10 IS NULL)
837 AND (X_Attribute10 IS NULL)))
838 AND ( (Recinfo.attribute11 = X_Attribute11)
839 OR ( (Recinfo.attribute11 IS NULL)
840 AND (X_Attribute11 IS NULL)))
841 AND ( (Recinfo.attribute12 = X_Attribute12)
842 OR ( (Recinfo.attribute12 IS NULL)
843 AND (X_Attribute12 IS NULL)))
844 AND ( (Recinfo.attribute13 = X_Attribute13)
845 OR ( (Recinfo.attribute13 IS NULL)
846 AND (X_Attribute13 IS NULL)))
847 AND ( (Recinfo.attribute14 = X_Attribute14)
848 OR ( (Recinfo.attribute14 IS NULL)
849 AND (X_Attribute14 IS NULL)))
850 AND ( (Recinfo.attribute15 = X_Attribute15)
851 OR ( (Recinfo.attribute15 IS NULL)
852 AND (X_Attribute15 IS NULL)))
853 AND ( (Recinfo.attribute16 = X_Attribute16)
854 OR ( (Recinfo.attribute16 IS NULL)
855 AND (X_Attribute16 IS NULL)))
856 AND ( (Recinfo.attribute17 = X_Attribute17)
857 OR ( (Recinfo.attribute17 IS NULL)
858 AND (X_Attribute17 IS NULL)))
859 AND ( (Recinfo.attribute18 = X_Attribute18)
860 OR ( (Recinfo.attribute18 IS NULL)
861 AND (X_Attribute18 IS NULL)))
862 AND ( (Recinfo.attribute19 = X_Attribute19)
863 OR ( (Recinfo.attribute19 IS NULL)
864 AND (X_Attribute19 IS NULL)))
865 AND ( (Recinfo.attribute20 = X_Attribute20)
866 OR ( (Recinfo.attribute20 IS NULL)
867 AND (X_Attribute20 IS NULL)))
868 AND ( (Recinfo.balance_category_id = x_balance_category_id)
869 OR ( (Recinfo.balance_category_id IS NULL)
870 AND (x_balance_category_id IS NULL)))
871 AND ( (Recinfo.base_balance_type_id = x_base_balance_type_id)
872 OR ( (Recinfo.base_balance_type_id IS NULL)
873 AND (x_base_balance_type_id IS NULL)))
874 AND ( (Recinfo.input_value_id = x_input_value_id)
875 OR ( (Recinfo.input_value_id IS NULL)
876 AND (x_input_value_id IS NULL)))
877 ) then
878 return;
879 else
880 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
881 APP_EXCEPTION.RAISE_EXCEPTION;
882 end if;
883 --
884 END Lock_Row;
885 --
886 -----------------------------------------------------------------------------
887 -- Name --
888 -- Update_Row --
889 -- Purpose --
890 -- Table handler procedure that supports the update of a balance via the --
891 -- Define Balance Type form. --
892 -- Arguments --
893 -- See below. --
894 -- Notes --
895 -- None. --
896 -----------------------------------------------------------------------------
897 --
898 PROCEDURE Update_Row(X_Rowid VARCHAR2,
899 X_Balance_Type_Id NUMBER,
900 X_Business_Group_Id NUMBER,
901 X_Legislation_Code VARCHAR2,
902 X_Currency_Code VARCHAR2,
903 X_Assignment_Remuneration_Flag VARCHAR2,
904 X_Balance_Name VARCHAR2,
905 X_Base_Balance_Name VARCHAR2,
906 X_Balance_Uom VARCHAR2,
907 X_Comments VARCHAR2,
908 X_Legislation_Subgroup VARCHAR2,
909 X_Reporting_Name VARCHAR2,
910 X_Attribute_Category VARCHAR2,
911 X_Attribute1 VARCHAR2,
912 X_Attribute2 VARCHAR2,
913 X_Attribute3 VARCHAR2,
914 X_Attribute4 VARCHAR2,
915 X_Attribute5 VARCHAR2,
916 X_Attribute6 VARCHAR2,
917 X_Attribute7 VARCHAR2,
918 X_Attribute8 VARCHAR2,
919 X_Attribute9 VARCHAR2,
920 X_Attribute10 VARCHAR2,
921 X_Attribute11 VARCHAR2,
922 X_Attribute12 VARCHAR2,
923 X_Attribute13 VARCHAR2,
924 X_Attribute14 VARCHAR2,
925 X_Attribute15 VARCHAR2,
926 X_Attribute16 VARCHAR2,
927 X_Attribute17 VARCHAR2,
928 X_Attribute18 VARCHAR2,
929 X_Attribute19 VARCHAR2,
930 X_Attribute20 VARCHAR2,
931 x_balance_category_id number default null,
932 x_base_balance_type_id number default null,
933 x_input_value_id number default null)
934 IS
935 --
936 BEGIN
937 --
938 -- Make sure balance type is valid ie. unique name only one remuneration
939 -- balance etc ...
940 chk_balance_type
941 (X_Rowid,
942 X_Business_Group_Id,
943 X_Legislation_Code,
944 X_Balance_Name,
945 X_Reporting_Name,
946 X_Assignment_Remuneration_Flag);
947 --
948 -- Check if balance_category should be mandatory
949 --
950 hr_utility.trace('upd_leg: '||x_legislation_code);
951 hr_utility.trace('upd_bg: '||to_char(x_business_group_id));
952 hr_utility.trace('upd_cat: '||x_balance_category_id);
953 if chk_balance_category_rule(x_legislation_code
954 ,x_business_group_id) then
955 --
956 if x_balance_category_id is null then
957 --
958 hr_utility.set_location('pay_balance_types_pkg.insert_row', 10);
959 hr_utility.set_message(801, 'PAY_34261_CAT_IS_MANDATORY');
960 hr_utility.raise_error;
961 --
962 end if;
963 end if;
964 --
965 UPDATE pay_balance_types
966 SET balance_type_id = X_Balance_Type_Id,
967 business_group_id = X_Business_Group_Id,
968 legislation_code = X_Legislation_Code,
969 currency_code = X_Currency_Code,
970 assignment_remuneration_flag = X_Assignment_Remuneration_Flag,
971 -- --
972 balance_name = X_Base_Balance_Name,
973 -- --
974 balance_uom = X_Balance_Uom,
975 comments = X_Comments,
976 legislation_subgroup = X_Legislation_Subgroup,
977 reporting_name = X_Reporting_Name,
978 attribute_category = X_Attribute_Category,
979 attribute1 = X_Attribute1,
980 attribute2 = X_Attribute2,
981 attribute3 = X_Attribute3,
982 attribute4 = X_Attribute4,
983 attribute5 = X_Attribute5,
984 attribute6 = X_Attribute6,
985 attribute7 = X_Attribute7,
986 attribute8 = X_Attribute8,
987 attribute9 = X_Attribute9,
988 attribute10 = X_Attribute10,
989 attribute11 = X_Attribute11,
990 attribute12 = X_Attribute12,
991 attribute13 = X_Attribute13,
992 attribute14 = X_Attribute14,
993 attribute15 = X_Attribute15,
994 attribute16 = X_Attribute16,
995 attribute17 = X_Attribute17,
996 attribute18 = X_Attribute18,
997 attribute19 = X_Attribute19,
998 attribute20 = X_Attribute20,
999 balance_category_id = x_balance_category_id,
1000 base_balance_type_id = x_base_balance_type_id,
1001 input_value_id = x_input_value_id
1002
1003 WHERE rowid = X_rowid;
1004 --
1005 if (SQL%NOTFOUND) then
1006 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1007 hr_utility.set_message_token('PROCEDURE',
1008 'pay_balance_types_pkg.update_row');
1009 hr_utility.set_message_token('STEP','1');
1010 hr_utility.raise_error;
1011 end if;
1012 --
1013 -- ****************************************************************************************
1014 --
1015 -- update MLS table (TL)
1016 --
1017 update PAY_BALANCE_TYPES_TL
1018 set BALANCE_NAME = X_BALANCE_NAME,
1019 REPORTING_NAME = X_REPORTING_NAME,
1020 LAST_UPDATE_DATE = sysdate,
1021 SOURCE_LANG = userenv('LANG')
1022 where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID
1023 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1024 --
1025 --
1026 if (sql%notfound) then -- trap system errors during update
1027 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1028 hr_utility.set_message_token ('PROCEDURE','PAY_BALANCE_TYPES_PKG.UPDATE_TL_ROW');
1029 end if;
1030 --
1031 -- ***************************************************************************************
1032 --
1033 END Update_Row;
1034 --
1035 -----------------------------------------------------------------------------
1036 -- Name --
1037 -- Delete_Row --
1038 -- Purpose --
1039 -- Table handler procedure that supports the delete of a balance via the --
1040 -- Define Balance Type form. --
1041 -- Arguments --
1042 -- See below. --
1043 -- Notes --
1044 -- --
1045 -----------------------------------------------------------------------------
1046 --
1047 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1048 -- Extra Columns
1049 X_Balance_Type_Id NUMBER) IS
1050 --
1051 BEGIN
1052 --
1053 -- Remove balance feeds, balance classifications and defined balances.
1054 balance_type_cascade_delete(X_Balance_Type_Id);
1055 --
1056 DELETE FROM pay_balance_types
1057 WHERE rowid = X_Rowid;
1058 --
1059 if (SQL%NOTFOUND) then
1060 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1061 hr_utility.set_message_token('PROCEDURE',
1062 'pay_balance_types_pkg.delete_row');
1063 hr_utility.set_message_token('STEP','1');
1064 hr_utility.raise_error;
1065 end if;
1066 --
1067 -- ****************************************************************************
1068 --
1069 -- delete from MLS table (TL)
1070 --
1071 --
1072 -- bugfix 1229606
1073 -- only delete data from the translated tables if the date track mode is ZAP,
1074 -- for all other date track modes the data should remain untouched
1075 --
1076 --if p_delete_mode = 'ZAP' then
1077
1078 delete from PAY_BALANCE_TYPES_TL
1079 where BALANCE_TYPE_ID = X_BALANCE_TYPE_ID;
1080 --
1081 if sql%notfound then -- trap system errors during deletion
1082 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1083 hr_utility.set_message_token ('PROCEDURE','PAY_BALANCE_TYPES_PKG.DELETE_TL_ROW');
1084 end if;
1085
1086 --end if;
1087 --
1088 -- ****************************************************************************
1089 --
1090 END Delete_Row;
1091 --
1092 --
1093 --
1094 ------------------------------------------------------------------------------------
1095 procedure ADD_LANGUAGE
1096 is
1097 begin
1098 delete from PAY_BALANCE_TYPES_TL T
1099 where not exists
1100 (select NULL
1101 from PAY_BALANCE_TYPES B
1102 where B.BALANCE_TYPE_ID = T.BALANCE_TYPE_ID
1103 );
1104
1105 update PAY_BALANCE_TYPES_TL T set (
1106 BALANCE_NAME,
1107 REPORTING_NAME
1108 ) = (select
1109 B.BALANCE_NAME,
1110 B.REPORTING_NAME
1111 from PAY_BALANCE_TYPES_TL B
1112 where B.BALANCE_TYPE_ID = T.BALANCE_TYPE_ID
1113 and B.LANGUAGE = T.SOURCE_LANG)
1114 where (
1115 T.BALANCE_TYPE_ID,
1116 T.LANGUAGE
1117 ) in (select
1118 SUBT.BALANCE_TYPE_ID,
1119 SUBT.LANGUAGE
1120 from PAY_BALANCE_TYPES_TL SUBB, PAY_BALANCE_TYPES_TL SUBT
1121 where SUBB.BALANCE_TYPE_ID = SUBT.BALANCE_TYPE_ID
1122 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1123 and (SUBB.BALANCE_NAME <> SUBT.BALANCE_NAME
1124 or SUBB.REPORTING_NAME <> SUBT.REPORTING_NAME
1125 or (SUBB.REPORTING_NAME is null and SUBT.REPORTING_NAME is not null)
1126 or (SUBB.REPORTING_NAME is not null and SUBT.REPORTING_NAME is null)
1127 ));
1128
1129 insert into PAY_BALANCE_TYPES_TL (
1130 BALANCE_TYPE_ID,
1131 BALANCE_NAME,
1132 REPORTING_NAME,
1133 LAST_UPDATE_DATE,
1134 LAST_UPDATED_BY,
1135 LAST_UPDATE_LOGIN,
1136 CREATED_BY,
1137 CREATION_DATE,
1138 LANGUAGE,
1139 SOURCE_LANG
1140 ) select
1141 B.BALANCE_TYPE_ID,
1142 B.BALANCE_NAME,
1143 B.REPORTING_NAME,
1144 B.LAST_UPDATE_DATE,
1145 B.LAST_UPDATED_BY,
1146 B.LAST_UPDATE_LOGIN,
1147 B.CREATED_BY,
1148 B.CREATION_DATE,
1149 L.LANGUAGE_CODE,
1150 B.SOURCE_LANG
1151 from PAY_BALANCE_TYPES_TL B, FND_LANGUAGES L
1152 where L.INSTALLED_FLAG in ('I', 'B')
1153 and B.LANGUAGE = userenv('LANG')
1154 and not exists
1155 (select NULL
1156 from PAY_BALANCE_TYPES_TL T
1157 where T.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
1158 and T.LANGUAGE = L.LANGUAGE_CODE);
1159 end ADD_LANGUAGE;
1160 --
1161 -----------------------------------------------------------------------------
1162 procedure unique_chk(X_B_BALANCE_NAME in VARCHAR2, X_B_LEGISLATION_CODE in VARCHAR2)
1163 is
1164 result varchar2(255);
1165 Begin
1166 SELECT count(*) INTO result
1167 FROM pay_balance_types
1168 WHERE nvl(BALANCE_NAME,'~null~') = nvl(X_B_BALANCE_NAME,'~null~')
1169 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
1170 and BUSINESS_GROUP_ID is NULL;
1171 --
1172 IF (result>1) THEN
1173 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1174 hr_utility.set_message_token('PROCEDURE','PAY_BALANCE_TYPES_PKG.UNIQUE_CHK');
1175 hr_utility.set_message_token('STEP','1');
1176 hr_utility.raise_error;
1177 END IF;
1178 EXCEPTION
1179 when NO_DATA_FOUND then
1180 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1181 hr_utility.set_message_token('PROCEDURE','PAY_BALANCE_TYPES_PKG.UNIQUE_CHK');
1182 hr_utility.set_message_token('STEP','1');
1183 hr_utility.raise_error;
1184 end unique_chk;
1185 --------------------------------------------------------------------------------
1186 procedure TRANSLATE_ROW (
1187 X_B_BALANCE_NAME in VARCHAR2,
1188 X_B_LEGISLATION_CODE in VARCHAR2,
1189 X_BALANCE_NAME in VARCHAR2,
1190 X_REPORTING_NAME in VARCHAR2,
1191 X_OWNER in VARCHAR2) is
1192 --
1193 -- Fetch the element_type_id. This used to be a sub-query in the update
1194 -- statement.
1195 --
1196 cursor csr_bal_id is
1197 select balance_type_id
1198 from pay_balance_types
1199 where nvl(balance_name,'~null~')=nvl(x_b_balance_name,'~null~')
1200 and nvl(legislation_code,'~null~') = nvl(x_b_legislation_code,'~null~')
1201 and business_group_id is null
1202 ;
1203 --
1204 -- Fetch information for the _TL rows that will be affected by the update.
1205 --
1206 cursor csr_tl_info
1207 (p_balance_type_id in number
1208 ,p_language in varchar2
1209 ) is
1210 select balance_name
1211 , language
1212 from pay_balance_types_tl
1213 where balance_type_id = p_balance_type_id
1214 and p_language in (language, source_lang)
1215 ;
1216 --
1217 l_balance_type_id number;
1218 l_found boolean;
1219 i binary_integer := 1;
1220 l_langs dbms_sql.varchar2s;
1221 l_lang varchar2(100);
1222 begin
1223 --
1224 -- Fetch the balance_type_id.
1225 --
1226 open csr_bal_id;
1227 fetch csr_bal_id
1228 into l_balance_type_id
1229 ;
1230 l_found := csr_bal_id%found;
1231 close csr_bal_id;
1232
1233 l_lang := userenv('LANG');
1234
1235 if l_found then
1236 --
1237 -- Check if database item translations are supported.
1238 --
1239 if ff_dbi_utils_pkg.translations_supported
1240 (p_legislation_code => x_b_legislation_code
1241 ) then
1242 for crec in csr_tl_info
1243 (p_balance_type_id => l_balance_type_id
1244 ,p_language => l_lang
1245 ) loop
1246 if upper(crec.balance_name) <> upper(x_balance_name) then
1247 l_langs(i) := crec.language;
1248 i := i + 1;
1249 end if;
1250 end loop;
1251 end if;
1252
1253 UPDATE pay_balance_types_tl
1254 SET balance_name = nvl(x_balance_name,balance_name),
1255 reporting_name = nvl(x_reporting_name,reporting_name),
1256 last_update_date = Sysdate,
1257 last_updated_by = decode(x_owner,'SEED',1,0),
1258 last_update_login = 0,
1259 source_lang = userenv('LANG')
1260 WHERE userenv('LANG') IN (language,source_lang)
1261 AND balance_type_id = l_balance_type_id
1262 ;
1263
1264 --
1265 -- Write any changes to PAY_DYNDBI_CHANGES.
1266 --
1267 if l_langs.count <> 0 then
1268 pay_dyndbi_changes_pkg.balance_type_change
1269 (p_balance_type_id => l_balance_type_id
1270 ,p_languages => l_langs
1271 );
1272 end if;
1273 end if;
1274 end TRANSLATE_ROW;
1275 --------------------------------------------------------------------------------
1276 END PAY_BALANCE_TYPES_PKG;