[Home] [Help]
PACKAGE BODY: APPS.FF_DATABASE_ITEMS_PKG
Source
1 package body FF_DATABASE_ITEMS_PKG as
2 /* $Header: ffdbi01t.pkb 120.14 2012/04/18 11:29:35 vmaripal ship $ */
3
4 procedure insert_tl_rows
5 (x_user_name in varchar2
6 ,x_user_entity_id in number
7 ,x_language in varchar2
8 ,x_translated_user_name in varchar2
9 ,x_description in varchar2
10 ) is
11 l_disable_triggers varchar2(10);
12 begin
13 --
14 -- Disable trigger validation.
15 --
16 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
17 ff_database_items_pkg.g_disable_triggers := 'Y';
18
19 insert into ff_database_items_tl (
20 user_name,
21 user_entity_id,
22 translated_user_name,
23 description,
24 language,
25 source_lang
26 ) select
27 x_user_name,
28 x_user_entity_id,
29 x_translated_user_name,
30 x_description,
31 l.language_code,
32 x_language
33 from fnd_languages l
34 where l.installed_flag in ('I', 'B')
35 and not exists
36 (select null
37 from ff_database_items_tl t
38 where t.user_name = x_user_name
39 and t.user_entity_id = x_user_entity_id
40 and t.language = l.language_code);
41
42 --
43 -- Reset the trigger code.
44 --
45 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
46
47 exception
48 when others then
49 --
50 -- Reset the trigger code.
51 --
52 if l_disable_triggers is not null then
53 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
54 end if;
55
56 raise;
57 end insert_tl_rows;
58
59 procedure insert_row
60 (x_rowid in out nocopy varchar2
61 ,x_user_name in out nocopy varchar2
62 ,x_user_entity_id in number
63 ,x_data_type in varchar2
64 ,x_definition_text in varchar2
65 ,x_null_allowed_flag in varchar2
66 ,x_translated_user_name in out nocopy varchar2
67 ,x_description in varchar2
68 ) is
69 l_disable_triggers varchar2(2000);
70 l_user_name varchar2(2000);
71 l_translated_user_name varchar2(2000);
72 begin
73 l_user_name := upper(x_user_name);
74
75 --
76 -- validate database item names.
77 --
78 ffdict.validate_dbitem
79 (p_dbi_name => l_user_name
80 ,p_user_entity_id => x_user_entity_id
81 );
82
83 l_translated_user_name := upper(x_translated_user_name);
84 if l_translated_user_name <> l_user_name then
85 ffdict.validate_tl_dbi
86 (p_user_name => l_user_name
87 ,p_user_entity_id => x_user_entity_id
88 ,p_tl_user_name => l_translated_user_name
89 );
90 end if;
91
92 --
93 -- Disable trigger validation.
94 --
95 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
96 ff_database_items_pkg.g_disable_triggers := 'Y';
97
98 insert into ff_database_items (
99 user_name,
100 user_entity_id,
101 data_type,
102 definition_text,
103 null_allowed_flag
104 ) values (
105 l_user_name,
106 x_user_entity_id,
107 upper(x_data_type),
108 x_definition_text,
109 upper(x_null_allowed_flag)
110 ) returning rowid
111 into x_rowid
112 ;
113
114 insert_tl_rows
115 (x_user_name => l_user_name
116 ,x_user_entity_id => x_user_entity_id
117 ,x_language => userenv('LANG')
118 ,x_translated_user_name => l_translated_user_name
119 ,x_description => x_description
120 );
121
122 --
123 -- Reset the trigger code.
124 --
125 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
126
127 exception
128 when others then
129 --
130 -- Reset the trigger code.
131 --
132 if l_disable_triggers is not null then
133 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
134 end if;
135
136 raise;
137
138 end insert_row;
139
140 ------------------------- in_use_replaced_names ---------------------------
141 --
142 -- NAME
143 -- in_use_replaced_names
144 --
145 -- DESCRIPTION
146 -- Returns a list of translated DBI names that would disappear because
147 -- of the new translation, but are actually referenced by compiled
148 -- Formulas.
149 --
150 -- NOTES
151 -- USER_NAME, and TRANSLATED_USER_NAME must be in valid DBI format.
152 --
153 -- REPLACED_NAMES populated with indexes in sequence 1, 2, 3 ...
154 --
155 procedure in_use_replaced_names
156 (x_user_name in varchar2
157 ,x_user_entity_id in number
158 ,x_language in varchar2
159 ,x_translated_user_name in varchar2
160 ,x_replaced_names out nocopy dbms_sql.varchar2s
161 ) is
162 l_debug boolean := hr_utility.debug_enabled;
163 l_found varchar2(1);
164 i binary_integer;
165 --
166 -- Cursor to fetch affected language rows.
167 --
168 cursor csr_affected_rows
169 (x_user_name in varchar2
170 ,x_user_entity_id in number
171 ,x_new_name in varchar2
172 ,x_language in varchar2
173 ) is
174 select distinct translated_user_name
175 from ff_database_items_tl
176 where user_name = x_user_name
177 and user_entity_id = x_user_entity_id
178 and translated_user_name <> x_new_name
179 and translated_user_name <> x_user_name
180 and x_language in (language, source_lang)
181 ;
182 --
183 -- Cursor to check that an old name remains.
184 --
185 cursor csr_name_remains
186 (x_user_name in varchar2
187 ,x_user_entity_id in number
188 ,x_old_name in varchar2
189 ,x_language in varchar2
190 ) is
191 select null
192 from ff_database_items_tl
193 where user_name = x_user_name
194 and user_entity_id = x_user_entity_id
195 and translated_user_name = x_old_name
196 and language <> x_language
197 and source_lang <> x_language
198 ;
199 begin
200 if l_debug then
201 hr_utility.set_location('in_use_replaced_names', 10);
202 end if;
203
204 --
205 -- Verify that the existing names aren't used in any formulas.
206 --
207 i := 1;
208 for crec in csr_affected_rows
209 (x_user_name => x_user_name
210 ,x_user_entity_id => x_user_entity_id
211 ,x_new_name => x_translated_user_name
212 ,x_language => x_language
213 ) loop
214 --
215 -- See if the old name remains on any unaffected rows.
216 --
217 open csr_name_remains
218 (x_user_name => x_user_name
219 ,x_user_entity_id => x_user_entity_id
220 ,x_old_name => crec.translated_user_name
221 ,x_language => x_language
222 );
223 fetch csr_name_remains
224 into l_found;
225 if csr_name_remains%notfound then
226 --
227 -- All instances of this name will be overwritten. Check whether or
228 -- not the name is referenced in any formulas.
229 --
230 if ffdict.dbitl_used_in_formula
231 (p_tl_user_name => crec.translated_user_name
232 ,p_user_name => x_user_name
233 ,p_user_entity_id => x_user_entity_id
234 ,p_language => null
235 ) then
236
237 if l_debug then
238 hr_utility.trace
239 ('Replaced name ' || crec.translated_user_name ||
240 ' is still referenced by a Formula.'
241 );
242 end if;
243
244 x_replaced_names(i) := crec.translated_user_name;
245 i := i + 1;
246 end if;
247 end if;
248
249 close csr_name_remains;
250 end loop;
251
252 exception
253 when others then
254 if csr_name_remains%isopen then
255 close csr_name_remains;
256 end if;
257
258 raise;
259 end in_use_replaced_names;
260
261 ----------------------- handle_referencing_formulas -----------------------
262 --
263 -- NOTES
264 -- X_DONT_UPDATE is required because we cannot afford to invalidate
265 -- seeded Oracle Formulas.
266 --
267 -- X_MESSAGE_ROWIDS is used to hold customer Formula message rowids -
268 -- these messages would be invalid if the update is not performed.
269 --
270 procedure handle_referencing_formulas
271 (x_user_name in varchar2
272 ,x_user_entity_id in number
273 ,x_tl_user_name in varchar2
274 ,x_old_tl_name in varchar2
275 ,x_language in varchar2
276 ,x_dont_update in out nocopy boolean
277 ,x_message_rowids in out nocopy dbms_sql.varchar2s
278 ) is
279 l_formula_ids dbms_sql.number_table;
280 l_formula_names dbms_sql.varchar2s;
281 l_eff_start_dates dbms_sql.date_table;
282 l_eff_end_dates dbms_sql.date_table;
283 l_bus_group_ids dbms_sql.number_table;
284 l_leg_codes dbms_sql.varchar2s;
285 l_encoded_message varchar2(2000);
286 l_rowid varchar2(2000);
287 l_debug boolean := hr_utility.debug_enabled;
288 begin
289
290 if l_debug then
291 hr_utility.set_location('handle_referencing_formulas', 10);
292 end if;
293
294
295 --
296 -- Pass compared name to FETCH_REFERENCING_FORMULAS. Note: X_OLD_TL_NAME
297 -- is used if NOT NULL.
298 --
299 ffdict.fetch_referencing_formulas
300 (p_tl_user_name => nvl(x_old_tl_name, x_tl_user_name)
301 ,p_user_name => x_user_name
302 ,p_user_entity_id => x_user_entity_id
303 ,p_language => x_language
304 ,p_formula_ids => l_formula_ids
305 ,p_formula_names => l_formula_names
306 ,p_eff_start_dates => l_eff_start_dates
307 ,p_eff_end_dates => l_eff_end_dates
308 ,p_bus_group_ids => l_bus_group_ids
309 ,p_leg_codes => l_leg_codes
310 );
311
312 if l_debug then
313 hr_utility.set_location('handle_referencing_formulas', 20);
314 end if;
315
316 if l_formula_ids.count = 0 then
317
318 if l_debug then
319 hr_utility.set_location('handle_referencing_formulas', 30);
320 end if;
321
322 return;
323 end if;
324
325 for i in 1 .. l_formula_ids.count loop
326 --
327 -- Seeded Formula case:
328 --
329 if l_bus_group_ids(i) is null then
330
331 if l_debug then
332 hr_utility.set_location('handle_referencing_formulas', 40);
333 end if;
334
335 --
336 -- Cannot allow seeded Formulas to become invalid.
337 --
338 x_dont_update := true;
339
340 if x_old_tl_name is not null then
341
342 if l_debug then
343 hr_utility.set_location('handle_referencing_formulas', 50);
344 end if;
345
346 fnd_message.set_name ('FF','FF_33438_OLDDBINAME_IN_SEED_FF');
347 fnd_message.set_token('FORMULA', 'FF93_FORMULA', true);
348 fnd_message.set_token('DBI','FF91_DBITEM_NAME', true);
349 fnd_message.set_token('OLD_DBI_NAME', x_old_tl_name, false);
350 fnd_message.set_token('FF_NAME', l_formula_names(i), false);
351 fnd_message.set_token('EFF_DATE', to_char(l_eff_start_dates(i)), false);
352 l_encoded_message := fnd_message.get_encoded;
353 else
354
355 if l_debug then
356 hr_utility.set_location('handle_referencing_formulas', 60);
357 end if;
358
359 fnd_message.set_name ('FF','FF_33440_NEWDBINAME_IN_SEED_FF');
360 fnd_message.set_token('FORMULA', 'FF93_FORMULA', true);
361 fnd_message.set_token('DBI','FF91_DBITEM_NAME', true);
362 fnd_message.set_token('NEW_DBI_NAME', x_tl_user_name, false);
363 fnd_message.set_token('FF_NAME', l_formula_names(i), false);
364 fnd_message.set_token('EFF_DATE', to_char(l_eff_start_dates(i)), false);
365 l_encoded_message := fnd_message.get_encoded;
366 end if;
367 --
368 -- Customer Formula case:
369 --
370 else
371
372 if l_debug then
373 hr_utility.set_location('handle_referencing_formulas', 70);
374 end if;
375
376 if x_old_tl_name is not null then
377
378 if l_debug then
379 hr_utility.set_location('handle_referencing_formulas', 80);
380 end if;
381
382 fnd_message.set_name ('FF','FF_33437_OLDDBINAME_IN_CUST_FF');
383 fnd_message.set_token('FORMULA', 'FF93_FORMULA', true);
384 fnd_message.set_token('DBI','FF91_DBITEM_NAME', true);
385 fnd_message.set_token('NEW_DBI_NAME', x_tl_user_name, false);
386 fnd_message.set_token('OLD_DBI_NAME', x_old_tl_name, false);
387 fnd_message.set_token('FF_NAME', l_formula_names(i), false);
388 fnd_message.set_token('EFF_DATE', to_char(l_eff_start_dates(i)), false);
389 l_encoded_message := fnd_message.get_encoded;
390 else
391
392 if l_debug then
393 hr_utility.set_location('handle_referencing_formulas', 90);
394 end if;
395
396 fnd_message.set_name ('FF','FF_33439_NEWDBINAME_IN_CUST_FF');
397 fnd_message.set_token('FORMULA', 'FF93_FORMULA', true);
398 fnd_message.set_token('DBI','FF91_DBITEM_NAME', true);
399 fnd_message.set_token('NEW_DBI_NAME', x_tl_user_name, false);
400 fnd_message.set_token('FF_NAME', l_formula_names(i), false);
401 fnd_message.set_token('EFF_DATE', to_char(l_eff_start_dates(i)), false);
402 l_encoded_message := fnd_message.get_encoded;
403 end if;
407 hr_utility.set_location('handle_referencing_formulas', 100);
404 end if;
405
406 if l_debug then
408 end if;
409
410
411 --
412 -- Update log file.
413 --
414 pay_dbitl_update_errors_pkg.insert_row
415 (p_user_name => x_user_name
416 ,p_user_entity_id => x_user_entity_id
417 ,p_translated_name => x_tl_user_name
418 ,p_message_text => l_encoded_message
419 ,p_rowid => l_rowid
420 );
421
422 --
423 -- Invalidate the customer Formula and save log message rowid in case it needs to
424 -- be deleted.
425 --
426 if l_bus_group_ids(i) is not null then
427
428 if l_debug then
429 hr_utility.set_location('handle_referencing_formulas', 110);
430 end if;
431
432 x_message_rowids(x_message_rowids.count + 1) := l_rowid;
433
434 delete ff_compiled_info_f fci
435 where fci.formula_id = l_formula_ids(i)
436 and fci.effective_start_date = l_eff_start_dates(i)
437 and fci.effective_end_date = l_eff_end_dates(i)
438 ;
439
440 delete ff_fdi_usages_f fdi
441 where fdi.formula_id = l_formula_ids(i)
442 and fdi.effective_start_date = l_eff_start_dates(i)
443 and fdi.effective_end_date = l_eff_end_dates(i)
444 and fdi.item_name = x_user_name
445 ;
446 end if;
447 end loop;
448
449 if l_debug then
450 hr_utility.set_location('handle_referencing_formulas', 200);
451 end if;
452
453 end handle_referencing_formulas;
454
455 --------------------------- core_update_tl_rows ---------------------------
456 --
457 -- NAME
458 -- core_update_tl_rows
459 --
460 -- DESCRIPTION
461 -- Internal procedure for updating _TL rows. Saves repeating complex
462 -- code.
463 --
464 -- NOTES
465 -- If x_raise_errors and x_seed_update are both false then the
466 -- code will act as if x_raise_errors is true.
467 --
468 -- x_got_error is only set if there is an error and x_raise_errors is
469 -- false.
470 --
471 procedure core_update_tl_rows
472 (x_raise_errors in boolean
473 ,x_seed_update in boolean
474 ,x_user_name in varchar2
475 ,x_user_entity_id in number
476 ,x_language in varchar2
477 ,x_tl_user_name in out nocopy varchar2
478 ,x_description in varchar2
479 ,x_last_update_date in date
480 ,x_last_updated_by in number
481 ,x_last_update_login in number
482 ,x_got_error in out nocopy boolean
483 ) is
484 l_disable_triggers varchar2(2000);
485 l_user_name varchar2(2000);
486 l_tl_user_name varchar2(2000);
487 l_outcome varchar2(10);
488 l_replaced_names dbms_sql.varchar2s;
489 l_message_rowids dbms_sql.varchar2s;
490 l_encoded_message varchar2(2000);
491 l_debug boolean;
492 l_raise_errors boolean;
493 l_dont_update boolean;
494 begin
495 l_debug := hr_utility.debug_enabled;
496
497 if l_debug then
498 hr_utility.set_location('ffdict.core_update_tl_rows',10);
499 end if;
500
501 --
502 -- Always raise errors for non-seeded update.
503 --
504 l_raise_errors := (x_raise_errors or not x_seed_update);
505 if l_debug then
506 if l_raise_errors then
507 hr_utility.trace('RAISE (instead of LOG) errors.');
508 else
509 hr_utility.trace('LOG (instead of RAISE) errors.');
510 end if;
511 end if;
512
513 l_user_name := upper(x_user_name);
514 l_tl_user_name := x_tl_user_name;
515
516 ----------------------------------------------------------
517 -- Validate the proposed translated database item name. --
518 ----------------------------------------------------------
519 if l_raise_errors then
520 if l_debug then
521 hr_utility.set_location('ffdict.core_update_tl_rows',20);
522 end if;
523
524 ffdict.validate_tl_dbi
525 (p_user_name => l_user_name
526 ,p_user_entity_id => x_user_entity_id
527 ,p_tl_user_name => l_tl_user_name
528 );
529 else
530 if l_debug then
531 hr_utility.set_location('ffdict.core_update_tl_rows',30);
532 end if;
533
534 ffdict.core_validate_tl_dbitem
535 (p_user_name => l_user_name
536 ,p_user_entity_id => x_user_entity_id
537 ,p_tl_user_name => l_tl_user_name
538 ,p_outcome => l_outcome
539 );
540
541 --
542 -- New DB item name cannot be same as existing database item visible from
543 -- business group and legislation of current user_entity.
544 --
545 if l_outcome = 'D' then
546 fnd_message.set_name ('FF','FF52_NAME_ALREADY_USED');
547 fnd_message.set_token('1', l_tl_user_name, false);
548 fnd_message.set_token('2','FF91_DBITEM_NAME', true);
549 l_encoded_message := fnd_message.get_encoded;
550
551 pay_dbitl_update_errors_pkg.insert_row
552 (p_user_name => l_user_name
553 ,p_user_entity_id => x_user_entity_id
554 ,p_translated_name => l_tl_user_name
555 ,p_message_text => l_encoded_message
556 );
557
558 if l_debug then
559 hr_utility.set_location('ffdict.core_update_tl_rows',35);
560 end if;
561
562 x_got_error := true;
563 return;
564 end if;
565
566 if l_debug then
567 hr_utility.set_location('ffdict.core_update_tl_rows',40);
568 end if;
569
573 if l_outcome = 'C' then
570 --
571 -- New DB item name cannot be same as existing context name.
572 --
574 fnd_message.set_name ('FF','FF52_NAME_ALREADY_USED');
575 fnd_message.set_token('1', l_tl_user_name, false);
576 fnd_message.set_token('2','FF92_CONTEXT', true);
577 l_encoded_message := fnd_message.get_encoded;
578
579 pay_dbitl_update_errors_pkg.insert_row
580 (p_user_name => l_user_name
581 ,p_user_entity_id => x_user_entity_id
582 ,p_translated_name => l_tl_user_name
583 ,p_message_text => l_encoded_message
584 );
585
586 if l_debug then
587 hr_utility.set_location('ffdict.core_update_tl_rows',45);
588 end if;
589
590 x_got_error := true;
591 return;
592 end if;
593
594 if l_debug then
595 hr_utility.set_location('ffdict.core_update_tl_rows',50);
596 end if;
597
598 --
599 -- New DB item name cannot be same as existing item in any verified
600 -- formula. Need to ensure that there is no clash with non-DBI names
601 -- used by the formula (inputs, outputs, locals).
602 --
603 if l_outcome = 'F' then
604 l_dont_update := false;
605
606 handle_referencing_formulas
607 (x_user_name => l_user_name
608 ,x_user_entity_id => x_user_entity_id
609 ,x_tl_user_name => l_tl_user_name
610 ,x_old_tl_name => null
611 ,x_language => x_language
612 ,x_dont_update => l_dont_update
613 ,x_message_rowids => l_message_rowids
614 );
615
616 if l_dont_update then
617
618 --
619 -- Delete messages that apply to custom Formulas.
620 --
621 pay_dbitl_update_errors_pkg.delete_rows
622 (p_rowids => l_message_rowids
623 );
624
625 if l_debug then
626 hr_utility.set_location('ffdict.core_update_tl_rows',55);
627 end if;
628
629 x_got_error := true;
630 return;
631 end if;
632 end if;
633
634 if l_debug then
635 hr_utility.set_location('ffdict.core_update_tl_rows',60);
636 end if;
637 end if;
638
639 -----------------------------------------------------------------------
640 -- Check if any names that will disappear because of this change are --
641 -- not being used by compiled formulas. --
642 -----------------------------------------------------------------------
643
644 --
645 -- This test always needs to be performed.
646 --
647 if l_debug then
648 hr_utility.set_location('ffdict.core_update_tl_rows',70);
649 end if;
650
651 --
652 -- Verify that the existing names aren't used in any formulas.
653 --
654 in_use_replaced_names
655 (x_user_name => l_user_name
656 ,x_user_entity_id => x_user_entity_id
657 ,x_language => nvl(x_language, userenv('LANG'))
658 ,x_translated_user_name => l_tl_user_name
659 ,x_replaced_names => l_replaced_names
660 );
661
662 if l_replaced_names.count <> 0 then
663 if l_raise_errors then
664 if l_debug then
665 hr_utility.set_location('ffdict.core_update_tl_rows',75);
666 end if;
667
668 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
669 hr_utility.set_message_token('1', l_replaced_names(1));
670 hr_utility.raise_error;
671 else
672 l_dont_update := false;
673 --
674 -- Note: not reinitialising l_message_rowids here because all
675 -- messages for custom Formulas will be deleted if there is a
676 -- problem with seeded Formulas.
677 --
678 for i in 1 .. l_replaced_names.count loop
679 handle_referencing_formulas
680 (x_user_name => l_user_name
681 ,x_user_entity_id => x_user_entity_id
682 ,x_tl_user_name => l_tl_user_name
683 ,x_old_tl_name => l_replaced_names(i)
684 ,x_language => x_language
685 ,x_dont_update => l_dont_update
686 ,x_message_rowids => l_message_rowids
687 );
688 end loop;
689
690 if l_dont_update then
691
692 --
693 -- Delete messages that apply to custom Formulas.
694 --
695 pay_dbitl_update_errors_pkg.delete_rows
696 (p_rowids => l_message_rowids
697 );
698
699 if l_debug then
700 hr_utility.set_location('ffdict.core_update_tl_rows',85);
701 end if;
702
703 x_got_error := true;
704 return;
705 end if;
706 end if;
707 end if;
708
709 if l_debug then
710 hr_utility.set_location('ffdict.core_update_tl_rows',90);
711 end if;
712
713 ----------------------------------
714 -- Update the translated value. --
715 ----------------------------------
716
717 x_tl_user_name := l_tl_user_name;
718
719 --
720 -- Disable trigger validation.
721 --
722 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
723 ff_database_items_pkg.g_disable_triggers := 'Y';
724
725 update ff_database_items_tl set
726 translated_user_name = l_tl_user_name,
727 description = x_description,
728 last_update_date = x_last_update_date,
729 last_updated_by = x_last_updated_by,
730 last_update_login = x_last_update_login,
731 source_lang = x_language
732 where user_name = l_user_name
733 and user_entity_id = x_user_entity_id
737 -- Reset the trigger code.
734 and x_language in (language, source_lang);
735
736 --
738 --
739 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
740
741 if l_debug then
742 hr_utility.set_location('ffdict.core_update_tl_rows',200);
743 end if;
744
745 exception
746 when others then
747
748 if l_debug then
749 hr_utility.set_location('ffdict.core_update_tl_rows',500);
750 end if;
751
752 --
753 -- Reset the trigger code.
754 --
755 if l_disable_triggers is not null then
756 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
757 end if;
758
759 raise;
760 end core_update_tl_rows;
761
762 ----------------------------- update_tl_rows ------------------------------
763 --
764 -- NAME
765 -- update_tl_rows
766 --
767 -- DESCRIPTION
768 -- Procedure for updating _TL rows.
769 --
770 -- NOTES
771 -- Private routine exposing WHO columns.
772 --
773 procedure update_tl_rows
774 (x_user_name in varchar2
775 ,x_user_entity_id in number
776 ,x_language in varchar2
777 ,x_translated_user_name in out nocopy varchar2
778 ,x_description in varchar2
779 ,x_last_update_date in date
780 ,x_last_updated_by in number
781 ,x_last_update_login in number
782 ) is
783 l_got_error boolean := false;
784 begin
785 core_update_tl_rows
786 (x_raise_errors => true
787 ,x_seed_update => false
788 ,x_user_name => x_user_name
789 ,x_user_entity_id => x_user_entity_id
790 ,x_language => x_language
791 ,x_tl_user_name => x_translated_user_name
792 ,x_description => x_description
793 ,x_last_update_date => x_last_update_date
794 ,x_last_updated_by => x_last_updated_by
795 ,x_last_update_login => x_last_update_login
796 ,x_got_error => l_got_error
797 );
798 end update_tl_rows;
799
800 ----------------------------- update_tl_rows ------------------------------
801 procedure update_tl_rows
802 (x_user_name in varchar2
803 ,x_user_entity_id in number
804 ,x_language in varchar2
805 ,x_translated_user_name in out nocopy varchar2
806 ,x_description in varchar2
807 ) is
808 begin
809 update_tl_rows
810 (x_user_name => x_user_name
811 ,x_user_entity_id => x_user_entity_id
812 ,x_language => x_language
813 ,x_translated_user_name => x_translated_user_name
814 ,x_description => x_description
815 ,x_last_updated_by => fnd_global.user_id
816 ,x_last_update_date => sysdate
817 ,x_last_update_login => fnd_global.login_id
818 );
819 end update_tl_rows;
820
821 procedure update_tl_row
822 (x_user_name in varchar2
823 ,x_user_entity_id in number
824 ,x_language in varchar2
825 ,x_source_lang in varchar2
826 ,x_translated_user_name in varchar2
827 ,x_description in varchar2
828 ) is
829 l_disable_triggers varchar2(2000);
830 l_user_name varchar2(2000);
831 l_tl_name varchar2(2000);
832 l_old_tl_name varchar2(2000);
833 l_dummy varchar2(2000);
834 l_found boolean;
835 --
836 -- Cursor to fetch the old name.
837 --
838 cursor csr_old_name
839 (x_user_name in varchar2
840 ,x_user_entity_id in number
841 ,x_language in varchar2
842 ,x_new_name in varchar2
843 ) is
844 select translated_user_name
845 from ff_database_items_tl
846 where user_name = x_user_name
847 and user_entity_id = x_user_entity_id
848 and translated_user_name <> x_new_name
849 and translated_user_name <> x_user_name
850 and language = x_language
851 ;
852 --
853 -- Cursor to check that the old name remains.
854 --
855 cursor csr_name_remains
856 (x_user_name in varchar2
857 ,x_user_entity_id in number
858 ,x_old_name in varchar2
859 ,x_language in varchar2
860 ) is
861 select null
862 from ff_database_items_tl
863 where user_name = x_user_name
864 and user_entity_id = x_user_entity_id
865 and translated_user_name = x_old_name
866 and language <> x_language
867 ;
868 begin
869 --
870 -- Disable trigger validation. In the future, the triggers may
871 -- disappear altogether.
872 --
873 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
874 ff_database_items_pkg.g_disable_triggers := 'Y';
875 l_user_name := upper(x_user_name);
876
877 --
878 -- Verify that the new name isn't going to clash with anything.
879 --
880 l_tl_name := x_translated_user_name;
881 ffdict.validate_tl_dbi
882 (p_user_name => l_user_name
883 ,p_user_entity_id => x_user_entity_id
884 ,p_tl_user_name => l_tl_name
885 );
886
887 --
888 -- Verify that the old name isn't used in a formula.
889 --
890 open csr_old_name
891 (x_user_name => l_user_name
892 ,x_user_entity_id => x_user_entity_id
893 ,x_language => x_language
894 ,x_new_name => l_tl_name
895 );
896 fetch csr_old_name
897 into l_old_tl_name
898 ;
899 l_found := csr_old_name%found;
900 close csr_old_name;
901 if l_found then
902 open csr_name_remains
903 (x_user_name => l_user_name
907 );
904 ,x_user_entity_id => x_user_entity_id
905 ,x_old_name => l_old_tl_name
906 ,x_language => x_language
908 fetch csr_name_remains
909 into l_dummy
910 ;
911 l_found := csr_name_remains%found;
912 close csr_name_remains;
913
914 if not l_found and
915 ffdict.dbitl_used_in_formula
916 (p_tl_user_name => l_old_tl_name
917 ,p_user_name => l_user_name
918 ,p_user_entity_id => x_user_entity_id
919 ,p_language => x_language
920 ) then
921 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
922 hr_utility.set_message_token('1', l_old_tl_name);
923 hr_utility.raise_error;
924 end if;
925 end if;
926
927 update ff_database_items_tl set
928 translated_user_name = l_tl_name,
929 description = x_description,
930 source_lang = x_source_lang
931 where user_name = l_user_name
932 and user_entity_id = x_user_entity_id
933 and language = x_language;
934
935 --
936 -- Reset the trigger code.
937 --
938 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
939
940 exception
941 when others then
942 --
943 -- Reset the trigger code.
944 --
945 if l_disable_triggers is not null then
946 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
947 end if;
948
949 raise;
950 end update_tl_row;
951
952 procedure update_row
953 (x_user_name in varchar2
954 ,x_user_entity_id in number
955 ,x_data_type in varchar2
956 ,x_definition_text in varchar2
957 ,x_null_allowed_flag in varchar2
958 ,x_translated_user_name in out nocopy varchar2
959 ,x_description in varchar2
960 ) is
961 l_update varchar2(2000);
962 l_language varchar2(2000);
963 l_disable_triggers varchar2(2000);
964 l_user_name varchar2(2000);
965 --
966 -- Cursor to check if any ff_database_item changes are required.
967 --
968 cursor csr_update_base
969 (x_user_name in varchar2
970 ,x_user_entity_id in number
971 ,x_data_type in varchar2
972 ,x_definition_text in varchar2
973 ,x_null_allowed_flag in varchar2
974 ) is
975 select 'Y'
976 from ff_database_items dbi
977 where dbi.user_name = x_user_name
978 and dbi.user_entity_id = x_user_entity_id
979 and (dbi.data_type <> x_data_type or
980 dbi.definition_text <> x_definition_text or
981 dbi.null_allowed_flag <> x_null_allowed_flag
982 )
983 ;
984 begin
985 --
986 -- Disable trigger validation. In the future, the triggers may
987 -- disappear altogether.
988 --
989 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
990 ff_database_items_pkg.g_disable_triggers := 'Y';
991
992 --
993 -- If anything is changed on the base table then it is necessary
994 -- to check whether or not this database item is referenced by
995 -- a compiled formula.
996 --
997 l_user_name := upper(x_user_name);
998
999 open csr_update_base
1000 (x_user_name => l_user_name
1001 ,x_user_entity_id => x_user_entity_id
1002 ,x_data_type => upper(x_data_type)
1003 ,x_definition_text => x_definition_text
1004 ,x_null_allowed_flag => upper(x_null_allowed_flag)
1005 );
1006 fetch csr_update_base into l_update;
1007 close csr_update_base;
1008
1009 if l_update = 'Y' then
1010 if ffdict.dbi_used_in_formula(l_user_name, x_user_entity_id) then
1011 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
1012 hr_utility.set_message_token('1',x_user_name);
1013 hr_utility.raise_error;
1014 end if;
1015
1016 update ff_database_items set
1017 data_type = upper(x_data_type),
1018 definition_text = x_definition_text,
1019 null_allowed_flag = upper(x_null_allowed_flag)
1023
1020 where user_name = l_user_name
1021 and user_entity_id = x_user_entity_id;
1022 end if;
1024 --
1025 -- Now handle the translations.
1026 --
1027 update_tl_rows
1028 (x_user_name => l_user_name
1029 ,x_user_entity_id => x_user_entity_id
1030 ,x_language => userenv('LANG')
1031 ,x_translated_user_name => x_translated_user_name
1032 ,x_description => x_description
1033 );
1034
1035 --
1036 -- Reset the trigger code.
1037 --
1038 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1039
1040 exception
1041 when others then
1042 --
1043 -- Reset the trigger code.
1044 --
1045 if l_disable_triggers is not null then
1046 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1047 end if;
1048
1049 if csr_update_base%isopen then
1050 close csr_update_base;
1051 end if;
1052 --
1053 raise;
1054 end update_row;
1055
1056 ------------------------- update_seeded_tl_rows ---------------------------
1057 procedure update_seeded_tl_rows
1058 (x_user_name in varchar2
1059 ,x_user_entity_id in number
1060 ,x_language in varchar2
1061 ,x_translated_user_name in out nocopy varchar2
1062 ,x_description in varchar2
1063 ,x_got_error out nocopy boolean
1064 ) is
1065 l_got_error boolean := false;
1066 begin
1067 x_got_error := true;
1068 core_update_tl_rows
1069 (x_raise_errors => false
1070 ,x_seed_update => true
1071 ,x_user_name => x_user_name
1072 ,x_user_entity_id => x_user_entity_id
1073 ,x_language => x_language
1074 ,x_tl_user_name => x_translated_user_name
1075 ,x_description => x_description
1076 ,x_last_update_date => sysdate
1077 ,x_last_updated_by => 1
1078 ,x_last_update_login => 0
1079 ,x_got_error => l_got_error
1080 );
1081 x_got_error := l_got_error;
1082 end update_seeded_tl_rows;
1083
1084 procedure delete_tl_rows
1085 (x_user_name in varchar2
1086 ,x_user_entity_id in number
1087 ) is
1088 l_user_name varchar2(2000);
1089 begin
1090 l_user_name := upper(x_user_name);
1091
1092 delete /*+ INDEX(dbitl FF_DATABASE_ITEMS_TL_PK) */
1093 from ff_database_items_tl dbitl
1094 where dbitl.user_name = l_user_name
1095 and dbitl.user_entity_id = x_user_entity_id;
1096 end delete_tl_rows;
1097
1098 procedure delete_row
1099 (x_user_name in varchar2
1100 ,x_user_entity_id in number
1101 ) is
1102 l_disable_triggers varchar2(2000);
1103 l_user_name varchar2(2000);
1104 begin
1105 l_user_name := upper(x_user_name);
1106
1107 ffdict.delete_dbitem_check
1108 (p_item_name => l_user_name
1109 ,p_user_entity_id => x_user_entity_id
1110 );
1111
1112 --
1113 -- Disable trigger validation. In the future, the triggers may
1114 -- disappear altogether.
1115 --
1116 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
1117 ff_database_items_pkg.g_disable_triggers := 'Y';
1118
1119 ff_database_items_pkg.delete_tl_rows
1120 (x_user_name => l_user_name
1121 ,x_user_entity_id => x_user_entity_id
1122 );
1123
1124 delete from ff_database_items
1125 where user_name = l_user_name
1126 and user_entity_id = x_user_entity_id;
1127
1128 --
1129 -- Reset the trigger code.
1130 --
1131 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1132
1133 exception
1134 when others then
1135 --
1136 -- Reset the trigger code.
1137 --
1138 if l_disable_triggers is not null then
1139 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1140 end if;
1141
1142 raise;
1143
1144 end delete_row;
1145
1146 procedure add_language
1147 is
1148 l_disable_triggers varchar2(100);
1149 --
1150 l_userenv_lang varchar2(2000);
1151 --
1152 ueids dbms_sql.number_table;
1153 --
1154 l_debug boolean;
1155 --
1156 l_limit number := 2000;
1157 --
1158 -- Drive off ff_user_entities.
1159 --
1160 cursor csr_user_entity_ids is
1161 select a.user_entity_id
1162 from ff_user_entities a
1163 where exists
1164 (
1165 select null
1166 from ff_database_items b
1167 where a.user_entity_id = b.user_entity_id
1168 )
1169 ;
1170 begin
1171 select userenv('LANG')
1172 into l_userenv_lang
1173 from dual
1174 ;
1175
1176 l_debug := hr_utility.debug_enabled;
1177
1178 --
1179 -- Disable trigger code by setting ff_database_items_pkg.g_disable_triggers.
1180 --
1181 l_disable_triggers := ff_database_items_pkg.g_disable_triggers;
1182 ff_database_items_pkg.g_disable_triggers := 'Y';
1183
1184 if l_debug then
1185 hr_utility.trace('add_language:delete');
1186 end if;
1187
1188 -- bug 13739722 remove delete
1189 -- delete from ff_database_items_tl t
1190 -- where (t.user_name, t.user_entity_id) not in
1191 -- (
1192 -- select /*+ parallel(b) use_hash(b) index_ffs(b) */ b.user_name
1193 -- , b.user_entity_id
1194 -- from ff_database_items b
1195 -- )
1196 -- ;
1197 -- commit;
1198
1199 open csr_user_entity_ids;
1200 loop
1201 fetch csr_user_entity_ids bulk collect into ueids limit l_limit;
1202
1203 if l_debug then
1204 hr_utility.trace('add_language:update');
1205 end if;
1206
1207 /* bug 13739722 remove update
1211 description
1208 forall i in ueids.first .. ueids.last
1209 update ff_database_items_tl t set (
1210 translated_user_name,
1212 ) = (select
1213 b.translated_user_name,
1214 b.description
1215 from ff_database_items_tl b
1216 where b.user_entity_id = ueids(i)
1217 and b.user_name = t.user_name
1218 and b.user_entity_id = t.user_entity_id
1219 and b.language = t.source_lang)
1220 where (
1221 t.user_name,
1222 t.user_entity_id,
1223 t.language
1224 ) in (select
1225 subt.user_name,
1226 subt.user_entity_id,
1227 subt.language
1228 from ff_database_items_tl subb, ff_database_items_tl subt
1229 where subb.user_entity_id = ueids(i)
1230 and subb.user_name = subt.user_name
1231 and subb.user_entity_id = subt.user_entity_id
1232 and subb.language = subt.source_lang
1233 and (subb.translated_user_name <> subt.translated_user_name
1234 or subb.description <> subt.description
1235 or (subb.description is null and subt.description is not null)
1236 or (subb.description is not null and subt.description is null)
1237 ));
1238
1239 commit;
1240 */
1241 if l_debug then
1242 hr_utility.trace('add_language:insert');
1243 end if;
1244
1245 forall i in ueids.first .. ueids.last
1246 insert /*+ append parallel(tt) */ into ff_database_items_tl tt (
1247 user_name,
1248 user_entity_id,
1249 translated_user_name,
1250 description,
1251 last_update_date,
1252 last_updated_by,
1253 last_update_login,
1254 created_by,
1255 creation_date,
1256 language,
1257 source_lang
1258 )
1259 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
1260 ( select /*+ no_merge ordered parallel(b) */
1261 b.user_name,
1262 b.user_entity_id,
1263 b.translated_user_name,
1264 b.description,
1265 b.last_update_date,
1266 b.last_updated_by,
1267 b.last_update_login,
1268 b.created_by,
1269 b.creation_date,
1270 l.language_code,
1271 b.source_lang
1272 from ff_database_items_tl b,
1273 fnd_languages l
1274 where l.installed_flag in ('I', 'B')
1275 and b.user_entity_id = ueids(i)
1276 and b.language = l_userenv_lang ) v,
1277 ff_database_items_tl t
1278 where t.user_name(+) = v.user_name
1279 and t.user_entity_id(+) = v.user_entity_id
1280 and t.language(+) = v.language_code
1281 and t.user_entity_id IS NULL
1282 ;
1283
1284 commit;
1285
1286 --
1287 -- Do we need to exit the loop ?
1288 --
1289 exit when ueids.count < l_limit or csr_user_entity_ids%notfound;
1290 end loop;
1291
1292 --
1293 -- Reset triggers.
1294 --
1295 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1296
1297 close csr_user_entity_ids;
1298
1299 return;
1300
1301 exception
1302 when others then
1303 --
1304 -- Reset triggers.
1305 --
1306 if l_disable_triggers is not null then
1307 ff_database_items_pkg.g_disable_triggers := l_disable_triggers;
1308 end if;
1309
1310 if csr_user_entity_ids%isopen then
1311 close csr_user_entity_ids;
1312 end if;
1313 --
1314 raise;
1315 end add_language;
1316
1317 procedure translate_row
1318 (x_user_name in varchar2
1319 ,x_legislation_code in varchar2
1320 ,x_translated_user_name in varchar2
1321 ,x_description in varchar2
1322 ,x_language in varchar2
1323 ,x_owner in varchar2
1324 ) is
1325 l_user_name varchar2(512);
1326 l_language varchar2(32);
1327 l_translated_user_name varchar2(512);
1328 l_user_entity_id number;
1329 l_updated_by number;
1330 l_found boolean;
1331 l_debug boolean;
1332 --
1333 -- Cursor to find the database item in question.
1334 --
1335 cursor csr_find_dbi
1336 (x_user_name in varchar2
1337 ,x_legislation_code in varchar2
1338 ) is
1339 select ue.user_entity_id
1340 from ff_database_items dbi
1341 , ff_user_entities ue
1342 where dbi.user_name = x_user_name
1343 and ue.user_entity_id = dbi.user_entity_id
1344 and (ue.legislation_code = x_legislation_code or
1345 ue.legislation_code is null)
1346 ;
1347 --
1348 l_got_error boolean := false;
1349 begin
1350 l_debug := hr_utility.debug_enabled;
1351
1352 --
1353 -- Set the language.
1354 --
1355 if x_language is null then
1356 l_language := userenv('LANG');
1357 else
1358 l_language := x_language;
1359 end if;
1360
1361 if l_debug then
1362 hr_utility.trace('LANGUAGE: ' || l_language);
1363 end if;
1364
1365 --
1366 -- Find the database item.
1367 --
1368 l_user_name := upper(x_user_name);
1369 open csr_find_dbi
1370 (x_user_name => l_user_name
1371 ,x_legislation_code => x_legislation_code
1372 );
1373 fetch csr_find_dbi
1374 into l_user_entity_id
1375 ;
1376 l_found := csr_find_dbi%found;
1377 close csr_find_dbi;
1378
1379 --
1380 -- The database item was not found so there is nothing to translate.
1381 --
1382 if not l_found then
1383 if l_debug then
1384 hr_utility.trace
1388 return;
1385 ('Could not find ' || l_user_name || ':' || x_legislation_code
1386 );
1387 end if;
1389 end if;
1390
1391 --
1392 -- Now handle the translation. CORE_UPDATE_TL_ROWS will perform any
1393 -- necessary name format conversions.
1394 --
1395 l_translated_user_name := x_translated_user_name;
1396
1397 if x_owner = 'SEED' then
1398 l_updated_by := 1;
1399 else
1400 l_updated_by := 0;
1401 end if;
1402
1403 core_update_tl_rows
1404 (x_raise_errors => (x_owner <> 'SEED')
1405 ,x_seed_update => (x_owner = 'SEED')
1406 ,x_user_name => l_user_name
1407 ,x_user_entity_id => l_user_entity_id
1408 ,x_language => l_language
1409 ,x_tl_user_name => l_translated_user_name
1410 ,x_description => x_description
1411 ,x_last_update_date => sysdate
1412 ,x_last_updated_by => l_updated_by
1413 ,x_last_update_login => 0
1414 ,x_got_error => l_got_error
1415 );
1416
1417 if l_debug then
1418 if l_got_error then
1419 hr_utility.trace('No update with name ' || l_translated_user_name);
1420 else
1421 hr_utility.trace('Update with name ' || l_translated_user_name);
1422 end if;
1423 end if;
1424
1425 exception
1426 when others then
1427 if csr_find_dbi%isopen then
1428 close csr_find_dbi;
1429 end if;
1430 --
1431 raise;
1432
1433 end translate_row;
1434
1435 end ff_database_items_pkg;