1 package body ffdict as
2 /* $Header: ffdict.pkb 120.14 2006/10/13 14:13:52 arashid noship $ */
3 --
4 -- Temporary storage for UE details to be used in cascade delete of DB items
5 -- to get round mutating ff_user_entities
6 --
7 tmp_ue_id ff_user_entities.user_entity_id%TYPE;
8 tmp_bg_id ff_user_entities.business_group_id%TYPE;
9 tmp_leg_code ff_user_entities.legislation_code%TYPE;
10 --
11 -- Temporary storage for global database item information.
12 --
13 g_glb_id ff_globals_f.global_id%TYPE;
14 g_glb_ueid ff_user_entities.user_entity_id%TYPE;
15 g_glb_dbi ff_database_items.user_name%TYPE;
16 --
17 g_debug boolean := hr_utility.debug_enabled;
18 ------------------------------ fetch_ue_details -------------------------------
19 --
20 -- NAME
21 -- fetch_ue_details
22 -- DESCRIPTION
23 -- Fetches UE details (assuming a cascade delete from ff_user_entities is
24 -- not in progress).
25 --
26 -------------------------------------------------------------------------------
27 --
28 procedure fetch_ue_details(p_user_entity_id in number) is
29 l_business_group_id number;
30 l_legislation_code varchar2(30);
31 begin
32 g_debug := hr_utility.debug_enabled;
33 if g_debug then
34 hr_utility.set_location('ffdict.fetch_ue_details',1);
35 end if;
36 --
37 if tmp_ue_id is null or tmp_ue_id <> p_user_entity_id then
38 begin
39 select ue.business_group_id
40 , nvl(ue.legislation_code, bg.legislation_code)
41 into l_business_group_id
42 , l_legislation_code
43 from ff_user_entities ue
44 , per_business_groups_perf bg
45 where ue.user_entity_id = p_user_entity_id
46 and bg.business_group_id (+)= ue.business_group_id
47 ;
48 exception
49 when no_data_found then
50 if g_debug then
51 hr_utility.set_location('ffdict.fetch_ue_details',2);
52 end if;
53 --
54 hr_utility.set_message (802, 'FF_33021_ORPHANED_DBITEMS');
55 hr_utility.raise_error;
56 end;
57 --
58 set_ue_details
59 (user_entity_id => p_user_entity_id
60 ,business_group_id => l_business_group_id
61 ,legislation_code => l_legislation_code
62 );
63 end if;
64 --
65 if g_debug then
66 hr_utility.set_location('ffdict.fetch_ue_details',3);
67 end if;
68 end fetch_ue_details;
69 --
70 --------------------------- set_global_dbi_details ----------------------------
71 --
72 -- NAME
73 -- set_global_dbi_details
74 -- DESCRIPTION
75 -- Stores global dbi's details.
76 --
77 -------------------------------------------------------------------------------
78 --
79 procedure set_global_dbi_details (global_id in number) is
80 cursor csr_ueid(p_global_id in number) is
81 select /*+ INDEX(ue FF_USER_ENTITIES_N51) */ dbi.user_entity_id
82 , dbi.user_name
83 from ff_user_entities ue
84 , ff_database_items dbi
85 where ue.creator_type = 'S'
86 and ue.creator_id = p_global_id
87 and dbi.user_entity_id = ue.user_entity_id
88 ;
89 l_glb_dbi varchar2(2000);
90 l_glb_ueid number;
91 l_debug boolean;
92 begin
93 l_debug := hr_utility.debug_enabled;
94 if l_debug then
95 hr_utility.set_location('ffdict.set_global_dbi_details',1);
96 end if;
97
98 --
99 -- Only fetch the details if necessary.
100 --
101 if g_glb_id is null or g_glb_id <> global_id then
102 open csr_ueid(p_global_id => global_id);
103 fetch csr_ueid
104 into l_glb_ueid
105 , l_glb_dbi
106 ;
107 if csr_ueid%found then
108 g_glb_id := global_id;
109 g_glb_ueid := l_glb_ueid;
110 g_glb_dbi := l_glb_dbi;
111 else
112 if l_debug then
113 hr_utility.set_location('ffdict.set_global_dbi_details',2);
114 end if;
115 end if;
116 close csr_ueid;
117 end if;
118 exception
119 when others then
120 if csr_ueid%isopen then
121 close csr_ueid;
122 end if;
123 --
124 raise;
125 end set_global_dbi_details;
126 --
127 ------------------------------ get_context_level ------------------------------
128 --
129 -- NAME
130 -- get_context_level
131 -- DESCRIPTION
132 -- Effectively a stub function as context levels are dynamically allocated
133 -- in the formula engines. The return value is always 1.
134 -------------------------------------------------------------------------------
135 --
136 function get_context_level return number is
137 --
138 begin
139 return 1;
140 end get_context_level;
141 --
142 --------------------------- will_clash_with_formula ---------------------------
143 --
144 -- NAME
145 -- will_clash_with_formula
146 -- DESCRIPTION
147 -- Determines whether formula name will clash with other formulas in the
148 -- formula type passed within other business groups or legislations which
149 -- cannot be seen from current business group or legislation.
150 -- eg If bus grp and leg code are both null, the item to be added will be
151 -- visible from all other business groups and legislations, so if we add it
152 -- it may clash with an existing name, even though that name is not visible
153 -- from null business group.
154 -- If a clash is present, return TRUE, otherwise return FALSE.
155 --
156 -------------------------------------------------------------------------------
157 --
158 function will_clash_with_formula(p_item_name in varchar2,
159 p_formula_type_id in number,
160 p_bus_grp in number,
161 p_leg_code in varchar2) return boolean is
162 dummy varchar2(1);
163 startup_mode varchar2(10);
164 begin
165 -- set error tracking information
166 hr_utility.set_location('ffdict.will_clash_with_formula',1);
167 --
168 -- get startup_mode based on current business group and legislation
169 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
170 --
171 -- Ensure name does not clash with anywhere which will be able to 'see' the
172 -- formula being validated.
173 --
174 select null into dummy from dual where exists
175 (select null
176 from ff_formulas_f a
177 where a.formula_name = p_item_name
178 and a.formula_type_id = p_formula_type_id
179 and
180 ( startup_mode = 'MASTER'
181 or
182 ( startup_mode = 'SEED'
183 and
184 ( a.legislation_code = p_leg_code
185 or
186 (a.legislation_code is null and a.business_group_id is null)
187 or
188 p_leg_code =
189 (select b.legislation_code
190 from per_business_groups_perf b
191 where b.business_group_id = a.business_group_id)
192 )
193 )
194 or
195 ( startup_mode = 'NON-SEED'
196 and
197 ( a.business_group_id = p_bus_grp
198 or
199 (a.legislation_code is null and a.business_group_id is null)
200 or
201 (a.business_group_id is null and a.legislation_code = p_leg_code)
202 )
203 )
204 ));
205 -- Exception not raised, so name will clash - return TRUE
206 return TRUE;
207 exception
208 when no_data_found then
209 -- No data found, so name will not clash - return FALSE
210 return FALSE;
211 end will_clash_with_formula;
212 --
213 ----------------------------- is_used_in_formula ------------------------------
214 --
215 function is_used_in_formula (p_item_name in varchar2,
216 p_bus_grp in number,
217 p_leg_code in varchar2) return boolean is
218 dummy varchar2(1);
219 startup_mode varchar2(10);
220 begin
221 -- set error tracking information
222 hr_utility.set_location('ffdict.is_used_in_formula',1);
223 --
224 -- get startup_mode based on current business group and legislation
225 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
226 --
227 -- set error tracking information
228 hr_utility.set_location('ffdict.is_used_in_formula',2);
229 -- Check if there any rows in FDIU for this item but take into account
230 -- the business group of the formula
231 select 'X' into dummy from dual where exists
232 (select null
233 from ff_formulas_f a,
234 ff_fdi_usages_f b
235 where a.formula_id = b.formula_id
236 and (b.item_name = p_item_name or
237 b.alternative_item_name = p_item_name)
238 and
239 ( startup_mode = 'MASTER'
240 or
241 ( startup_mode = 'SEED'
242 and
243 ( a.legislation_code = p_leg_code
244 or
245 (a.legislation_code is null and a.business_group_id is null)
246 or
247 p_leg_code =
248 (select c.legislation_code
249 from per_business_groups_perf c
250 where c.business_group_id = a.business_group_id
251 )
252 )
253 )
254 or
255 ( startup_mode = 'NON-SEED'
256 and
257 ( a.business_group_id = p_bus_grp
258 or
259 (a.legislation_code is null and a.business_group_id is null)
260 or
261 (a.business_group_id is null and a.legislation_code = p_leg_code)
262 )
263 )
264 ));
265
266 -- Exception not raised, so item is used in formulae - return TRUE
267 return TRUE;
268 exception
269 when no_data_found then
270 -- No data found, so item not used - return FALSE
271 return FALSE;
272 end is_used_in_formula;
273 --
274 ---------------------------- dbitl_used_in_formula ----------------------------
275 --
276 -- NOTES
277 -- p_language is currently unused. This may change if a purer solution is
278 -- tried sometime in the future.
279 --
280 function dbitl_used_in_formula (p_tl_user_name in varchar2
281 ,p_user_name in varchar2
282 ,p_user_entity_id in number
283 ,p_language in varchar2
284 ) return boolean is
285 dummy varchar2(1);
286 startup_mode varchar2(10);
287 begin
288 g_debug := hr_utility.debug_enabled;
289
290 if g_debug then
291 hr_utility.set_location('ffdict.dbitl_used_in_formula',1);
292 end if;
293
294 --
295 -- Get the startup mode based upon the business group and legislation.
296 --
297 if (tmp_ue_id is null or tmp_ue_id <> p_user_entity_id) then
298 fetch_ue_details(p_user_entity_id);
299 end if;
300
301 if g_debug then
302 hr_utility.set_location('ffdict.dbitl_used_in_formula',2);
303 end if;
304
305 startup_mode := ffstup.get_mode(tmp_bg_id, tmp_leg_code);
306
307 --
308 -- Check if there any rows in FDIU for this item but take into account
309 -- the business group of the formula
310 --
311 select 'X' into dummy from dual where exists
312 (select null
313 from ff_fdi_usages_f b,
314 ff_formulas_f a
315 where b.alternative_item_name = p_tl_user_name
316 and a.formula_id = b.formula_id
317 and a.effective_start_date = b.effective_start_date
318 and
319 ( startup_mode = 'MASTER'
320 or
321 ( startup_mode = 'SEED'
322 and
323 ( a.legislation_code = tmp_leg_code
324 or
325 (a.legislation_code is null and a.business_group_id is null)
326 or
327 tmp_leg_code =
328 (select c.legislation_code
329 from per_business_groups_perf c
330 where c.business_group_id = a.business_group_id
331 )
332 )
333 )
334 or
335 ( startup_mode = 'NON-SEED'
336 and
337 ( a.business_group_id = tmp_bg_id
338 or
339 (a.legislation_code is null and a.business_group_id is null)
340 or
341 (a.business_group_id is null and
342 a.legislation_code = tmp_leg_code)
343 )
344 )
345 )
346 );
347
348 if g_debug then
349 hr_utility.set_location('ffdict.dbitl_used_in_formula',3);
350 end if;
351
352 return TRUE;
353
354 exception
355 when no_data_found then
356 if g_debug then
357 hr_utility.set_location('ffdict.dbitl_used_in_formula',4);
358 end if;
359 return FALSE;
360
361 end dbitl_used_in_formula;
362 ------------------------- fetch_referencing_formulas --------------------------
363 -- NOTES
364 -- p_language is currently unused. This may change if a purer solution is
365 -- tried sometime in the future.
366 -------------------------------------------------------------------------------
367 --
368 procedure fetch_referencing_formulas
369 (p_tl_user_name in varchar2
370 ,p_user_name in varchar2
371 ,p_user_entity_id in number
372 ,p_language in varchar2
373 ,p_formula_ids out nocopy dbms_sql.number_table
374 ,p_formula_names out nocopy dbms_sql.varchar2s
375 ,p_eff_start_dates out nocopy dbms_sql.date_table
376 ,p_eff_end_dates out nocopy dbms_sql.date_table
377 ,p_bus_group_ids out nocopy dbms_sql.number_table
378 ,p_leg_codes out nocopy dbms_sql.varchar2s
379 ) is
383 ,p_leg_code in varchar2
380 cursor csr_ref_formulas
381 (p_tl_user_name in varchar2
382 ,p_bus_group_id in number
384 ,p_startup_mode in varchar2
385 ) is
386 select b.formula_id
387 , a.formula_name
388 , b.effective_start_date
389 , b.effective_end_date
390 , a.business_group_id
391 , a.legislation_code
392 from ff_fdi_usages_f b,
393 ff_formulas_f a
394 where b.alternative_item_name = p_tl_user_name
395 and a.formula_id = b.formula_id
396 and a.effective_start_date = b.effective_start_date
397 and a.effective_end_date = b.effective_end_date
398 and
399 ( p_startup_mode = 'MASTER'
400 or
401 ( p_startup_mode = 'SEED'
402 and
403 ( a.legislation_code = p_leg_code
404 or
405 (a.legislation_code is null and a.business_group_id is null)
406 or
407 p_leg_code =
408 (select c.legislation_code
409 from per_business_groups_perf c
410 where c.business_group_id = a.business_group_id
411 )
412 )
413 )
414 or
415 ( p_startup_mode = 'NON-SEED'
416 and
417 ( a.business_group_id = p_bus_group_id
418 or
419 (a.legislation_code is null and a.business_group_id is null)
420 or
421 (a.business_group_id is null and
422 a.legislation_code = p_leg_code)
423 )
424 )
425 )
426 union
427 select b.formula_id
428 , a.formula_name
429 , b.effective_start_date
430 , b.effective_end_date
431 , a.business_group_id
432 , a.legislation_code
433 from ff_fdi_usages_f b,
434 ff_formulas_f a
435 where b.item_name = p_tl_user_name
436 and a.formula_id = b.formula_id
437 and a.effective_start_date = b.effective_start_date
438 and a.effective_end_date = b.effective_end_date
439 and
440 ( p_startup_mode = 'MASTER'
441 or
442 ( p_startup_mode = 'SEED'
443 and
444 ( a.legislation_code = p_leg_code
445 or
446 (a.legislation_code is null and a.business_group_id is null)
447 or
448 p_leg_code =
449 (select c.legislation_code
450 from per_business_groups_perf c
451 where c.business_group_id = a.business_group_id
452 )
453 )
454 )
455 or
456 ( p_startup_mode = 'NON-SEED'
457 and
458 ( a.business_group_id = p_bus_group_id
459 or
460 (a.legislation_code is null and a.business_group_id is null)
461 or
462 (a.business_group_id is null and
463 a.legislation_code = p_leg_code)
464 )
465 )
466 )
467 ;
468 --
469 l_startup_mode varchar2(20);
470 begin
471 g_debug := hr_utility.debug_enabled;
472
473 if g_debug then
474 hr_utility.set_location('ffdict.fetch_referencing_formulas',10);
475 end if;
476
477 --
478 -- Get the startup mode based upon the business group and legislation.
479 --
480 if (tmp_ue_id is null or tmp_ue_id <> p_user_entity_id) then
481 fetch_ue_details(p_user_entity_id);
482 end if;
483
484 l_startup_mode := ffstup.get_mode(tmp_bg_id, tmp_leg_code);
485
486 if g_debug then
487 hr_utility.set_location('ffdict.fetch_referencing_formulas',20);
488 hr_utility.trace('BG_ID: ' || tmp_bg_id || ' LEG_CODE: ' || tmp_leg_code);
489 hr_utility.trace('DBI: ' || p_tl_user_name);
490 hr_utility.trace('MODE: ' || l_startup_mode);
491 end if;
492
493 open csr_ref_formulas
494 (p_tl_user_name => p_tl_user_name
495 ,p_bus_group_id => tmp_bg_id
496 ,p_leg_code => tmp_leg_code
497 ,p_startup_mode => l_startup_mode
498 );
499
500 fetch csr_ref_formulas bulk collect
501 into p_formula_ids
502 , p_formula_names
503 , p_eff_start_dates
504 , p_eff_end_dates
505 , p_bus_group_ids
506 , p_leg_codes
507 ;
508
509 close csr_ref_formulas;
510
511 if g_debug then
512 hr_utility.set_location('ffdict.fetch_referencing_formulas',30);
513 end if;
514
515 exception
516 when others then
517 if g_debug then
518 hr_utility.set_location('ffdict.fetch_referencing_formulas',40);
519 end if;
520
521 if csr_ref_formulas%isopen then
522 close csr_ref_formulas;
523 end if;
524
525 raise;
526 end fetch_referencing_formulas;
527 --
528 ----------------------------- dbi_used_in_formula -----------------------------
529 --
530 -- NAME
531 -- dbi_used_in_formula
532 -- DESCRIPTION
533 -- Returns TRUE if a base database item name is used in a formula
534 -- (ie is referenced in the FDIU table) visible from the current business
535 -- group and legislation.
536 -- NOTES
537 -- The purpose of this interface is to avoid a formula becoming invalid
538 -- upon the update or deletion of a database item.
539 --
540 -------------------------------------------------------------------------------
541 --
542 function dbi_used_in_formula (p_user_name in varchar2
546 startup_mode varchar2(10);
543 ,p_user_entity_id in number
544 ) return boolean is
545 dummy varchar2(1);
547 begin
548 g_debug := hr_utility.debug_enabled;
549
550 if g_debug then
551 hr_utility.set_location('ffdict.dbi_used_in_formula',1);
552 end if;
553
554 --
555 -- Get the startup mode based upon the business group and legislation.
556 --
557 if (tmp_ue_id is null or tmp_ue_id <> p_user_entity_id) then
558 fetch_ue_details(p_user_entity_id);
559 end if;
560
561 if g_debug then
562 hr_utility.set_location('ffdict.dbi_used_in_formula',2);
563 end if;
564
565 startup_mode := ffstup.get_mode(tmp_bg_id, tmp_leg_code);
566
567 --
568 -- Check if there any rows in FDIU for this item but take into account
569 -- the business group of the formula
570 --
571 select 'X' into dummy from dual where exists
572 (select null
573 from ff_fdi_usages_f b,
574 ff_formulas_f a
575 where b.item_name = p_user_name
576 and a.formula_id = b.formula_id
577 and a.effective_start_date = b.effective_start_date
578 and
579 ( startup_mode = 'MASTER'
580 or
581 ( startup_mode = 'SEED'
582 and
583 ( a.legislation_code = tmp_leg_code
584 or
585 (a.legislation_code is null and a.business_group_id is null)
586 or
587 tmp_leg_code =
588 (select c.legislation_code
589 from per_business_groups_perf c
590 where c.business_group_id = a.business_group_id
591 )
592 )
593 )
594 or
595 ( startup_mode = 'NON-SEED'
596 and
597 ( a.business_group_id = tmp_bg_id
598 or
599 (a.legislation_code is null and a.business_group_id is null)
600 or
601 (a.business_group_id is null and
602 a.legislation_code = tmp_leg_code)
603 )
604 )
605 )
606 );
607
608 if g_debug then
609 hr_utility.set_location('ffdict.dbi_used_in_formula',3);
610 end if;
611
612 return TRUE;
613
614 exception
615 when no_data_found then
616 if g_debug then
617 hr_utility.set_location('ffdict.dbi_used_in_formula',4);
618 end if;
619 return FALSE;
620 end dbi_used_in_formula;
621 --
622 --------------------------- non_dbi_used_in_formula ---------------------------
623 --
624 -- NAME
625 -- non_dbi_used_in_formula
626 --
627 -- DESCRIPTION
628 -- Returns TRUE if a potential translated database item name is used in a
629 -- formula (ie is referenced in the FDIU table) by something other than
630 -- a database item. The formula is visible from the current business
631 -- group and legislation.
632 --
633 -- NOTES
634 -- The purpose of this interface is to allow for updates where a
635 -- translated database item is being updated with a name it is already
636 -- using.
637 --
638 -- The database item clash validation (tl_dbi_will_clash) must have
639 -- already been performed.
640 --
641 -------------------------------------------------------------------------------
642 --
643 function non_dbi_used_in_formula
644 (p_item_name in varchar2
645 ,p_bus_grp in number
646 ,p_leg_code in varchar2
647 ) return boolean is
648 dummy varchar2(1);
649 startup_mode varchar2(10);
650 begin
651 g_debug := hr_utility.debug_enabled;
652
653 if g_debug then
654 hr_utility.set_location('ffdict.non_dbi_used_in_formula',1);
655 end if;
656
657 startup_mode := ffstup.get_mode(p_bus_grp, p_leg_code);
658
659 --
660 -- Check if there any rows in FDIU for this item but take into account
661 -- the business group of the formula.
662 --
663 select 'X' into dummy from dual where exists
664 (select null
665 from ff_fdi_usages_f b,
666 ff_formulas_f a
667 where b.item_name = p_item_name
668 and b.usage <> 'D'
669 and a.formula_id = b.formula_id
670 and a.effective_start_date = b.effective_start_date
671 and
672 ( startup_mode = 'MASTER'
673 or
674 ( startup_mode = 'SEED'
675 and
676 ( a.legislation_code = p_leg_code
677 or
678 (a.legislation_code is null and a.business_group_id is null)
679 or
680 p_leg_code =
681 (select c.legislation_code
682 from per_business_groups_perf c
683 where c.business_group_id = a.business_group_id
684 )
685 )
686 )
687 or
688 ( startup_mode = 'NON-SEED'
689 and
690 ( a.business_group_id = p_bus_grp
691 or
692 (a.legislation_code is null and a.business_group_id is null)
693 or
694 (a.business_group_id is null and
695 a.legislation_code = p_leg_code)
696 )
697 )
698 )
699 );
700
701 if g_debug then
702 hr_utility.set_location('ffdict.non_dbi_used_in_formula',2);
703 end if;
707 exception
704
705 return TRUE;
706
708 when no_data_found then
709 if g_debug then
710 hr_utility.set_location('ffdict.non_dbi_used_in_formula',3);
711 end if;
712 return FALSE;
713
714 end non_dbi_used_in_formula;
715 --
716 --------------------------- will_clash_with_dbitem ---------------------------
717 --
718 -- NAME
719 -- will_clash_with_dbitem
720 -- DESCRIPTION
721 -- Determines whether named item will clash with dbitems in other
722 -- business groups or legislations which may not be visible from current
723 -- business group or legislation. eg If bus grp and leg code are both null,
724 -- the item to be added will be visible from all other business groups and
725 -- legislations, so if we add it it may clash with an existing name, even
726 -- though that name is not visible from null business group.
727 -- If a clash is present, return TRUE, otherwise return FALSE.
728 ------------------------------------------------------------------------------
729 --
730 function will_clash_with_dbitem(p_item_name in varchar2,
731 p_bus_grp in number,
732 p_leg_code in varchar2) return boolean is
733 dummy varchar2(1);
734 startup_mode varchar2(10);
735 begin
736 -- set error tracking information
737 hr_utility.set_location('ffdict.will_clash_with_dbitem',1);
738 --
739 -- get startup_mode based on current business group and legislation
740 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
741
742 --
743 -- Ensure name does not clash with anywhere which will be able to 'see' the
744 -- database item being validated.
745 --
746 begin
747 select null into dummy from dual where exists
748 (select /*+ ORDERED
749 INDEX(a FF_DATABASE_ITEMS_PK)
750 INDEX(b FF_USER_ENTITIES_PK) */ null
751 from ff_database_items a,
752 ff_user_entities b
753 where a.user_entity_id = b.user_entity_id
754 and a.user_name = p_item_name
755 and
756 ( startup_mode = 'MASTER'
757 or
758 ( startup_mode = 'SEED'
759 and
760 ( b.legislation_code = p_leg_code
761 or
762 (b.legislation_code is null and b.business_group_id is null)
763 or
764 p_leg_code =
765 (select c.legislation_code
766 from per_business_groups_perf c
767 where c.business_group_id = b.business_group_id
768 )
769 )
770 )
771 or
772 ( startup_mode = 'NON-SEED'
773 and
774 ( b.business_group_id = p_bus_grp
775 or
776 (b.legislation_code is null and b.business_group_id is null)
777 or
778 (b.business_group_id is null and b.legislation_code = p_leg_code)
779 )
780 )
781 ));
782 exception
783 when no_data_found then
784 select null into dummy from dual where exists
785 (select /*+ ORDERED
786 INDEX(a FF_DATABASE_ITEMS_TL_N1)
787 INDEX(b FF_USER_ENTITIES_PK) */ null
788 from ff_database_items_tl a,
789 ff_user_entities b
790 where a.user_entity_id = b.user_entity_id
791 and a.translated_user_name = p_item_name
792 and
793 ( startup_mode = 'MASTER'
794 or
795 ( startup_mode = 'SEED'
796 and
797 ( b.legislation_code = p_leg_code
798 or
799 (b.legislation_code is null and b.business_group_id is null)
800 or
801 p_leg_code =
802 (select c.legislation_code
803 from per_business_groups_perf c
804 where c.business_group_id = b.business_group_id
805 )
806 )
807 )
808 or
809 ( startup_mode = 'NON-SEED'
810 and
811 ( b.business_group_id = p_bus_grp
812 or
813 (b.legislation_code is null and b.business_group_id is null)
814 or
815 (b.business_group_id is null and b.legislation_code = p_leg_code)
816 )
817 )
818 ));
819 when others then
820 raise;
821 end;
822 -- Exception not raised, so name will clash - return TRUE
823 return TRUE;
824 exception
825 when no_data_found then
826 -- No data found, so name will not clash - return FALSE
827 return FALSE;
828 end will_clash_with_dbitem;
829 --
830 ------------------------------ tl_dbi_will_clash -----------------------------
831 --
832 -- NAME
833 -- tl_dbi_will_clash
834 -- DESCRIPTION
835 -- Returns TRUE if a translated user name, p_tl_user_name, clashes with
836 -- another database item's base or translated user name(s).
837 --
838 ------------------------------------------------------------------------------
839 --
840 function tl_dbi_will_clash
841 (p_tl_user_name in varchar2
842 ,p_user_name in varchar2
843 ,p_user_entity_id in number
844 ,p_business_group_id in number
845 ,p_legislation_code in varchar2
849 begin
846 ) return boolean is
847 dummy varchar2(1);
848 startup_mode varchar2(10);
850 g_debug := hr_utility.debug_enabled;
851
852 if g_debug then
853 hr_utility.set_location('ffdict.tl_dbi_will_clash',1);
854 end if;
855
856 --
857 -- If p_user_name = p_tl_user_name then return FALSE as p_user_name
858 -- will already have been validated.
859 --
860 if p_user_name = p_tl_user_name then
861 if g_debug then
862 hr_utility.set_location('ffdict.tl_dbi_will_clash',2);
863 end if;
864 return FALSE;
865 end if;
866
867 --
868 -- Get startup_mode based on current business group and legislation.
869 --
870 startup_mode :=
871 ffstup.get_mode(p_business_group_id, p_legislation_code);
872
873 begin
874 --
875 -- Check for clash with other FF_DATABASE_ITEMS rows.
876 --
877 select null into dummy from dual where exists
878 (select /*+ ORDERED
879 INDEX(a FF_DATABASE_ITEMS_PK)
880 INDEX(b FF_USER_ENTITIES_PK) */ null
881 from ff_database_items a,
882 ff_user_entities b
883 where a.user_name = p_tl_user_name
884 and a.user_entity_id <> p_user_entity_id
885 and a.user_entity_id = b.user_entity_id
886 and
887 ( startup_mode = 'MASTER'
888 or
889 ( startup_mode = 'SEED'
890 and
891 ( b.legislation_code = p_legislation_code
892 or
893 (b.legislation_code is null and b.business_group_id is null)
894 or
895 p_legislation_code =
896 (select c.legislation_code
897 from per_business_groups_perf c
898 where c.business_group_id = b.business_group_id
899 )
900 )
901 )
902 or
903 ( startup_mode = 'NON-SEED'
904 and
905 ( b.business_group_id = p_business_group_id
906 or
907 (b.legislation_code is null and b.business_group_id is null)
908 or
909 (b.business_group_id is null and b.legislation_code = p_legislation_code)
910 )
911 )
912 ));
913 exception
914 when no_data_found then
915 if g_debug then
916 hr_utility.set_location('ffdict.tl_dbi_will_clash',3);
917 end if;
918
919 --
920 -- No clash against FF_DATABASE_ITEMS so check against FF_DATABASE_ITEMS_TL.
921 --
922 select null into dummy from dual where exists
923 (select /*+ ORDERED
924 INDEX(a FF_DATABASE_ITEMS_TL_N1)
925 INDEX(b FF_USER_ENTITIES_PK) */ null
926 from ff_database_items_tl a,
927 ff_user_entities b
928 where a.translated_user_name = p_tl_user_name
929 and (a.user_name <> p_user_name or a.user_entity_id <> p_user_entity_id)
930 and a.user_entity_id = b.user_entity_id
931 and
932 ( startup_mode = 'MASTER'
933 or
934 ( startup_mode = 'SEED'
935 and
936 ( b.legislation_code = p_legislation_code
937 or
938 (b.legislation_code is null and b.business_group_id is null)
939 or
940 p_legislation_code =
941 (select c.legislation_code
942 from per_business_groups_perf c
943 where c.business_group_id = b.business_group_id
944 )
945 )
946 )
947 or
948 ( startup_mode = 'NON-SEED'
949 and
950 ( b.business_group_id = p_business_group_id
951 or
952 (b.legislation_code is null and b.business_group_id is null)
953 or
954 (b.business_group_id is null and b.legislation_code = p_legislation_code)
955 )
956 )
957 ));
958 end;
959
960 --
961 -- There is a clash.
962 --
963 if g_debug then
964 hr_utility.set_location('ffdict.tl_dbi_will_clash',4);
965 end if;
966
967 return TRUE;
968 exception
969 when no_data_found then
970 if g_debug then
971 hr_utility.set_location('ffdict.tl_dbi_will_clash',5);
972 end if;
973 return FALSE;
974 end tl_dbi_will_clash;
975 --
976 --------------------------- will_clash_with_global ---------------------------
977 --
978 -- NAME
979 -- will_clash_with_global
980 -- DESCRIPTION
981 -- Determines whether global will clash with other globals in other
982 -- business groups or legislations which cannot be seen from current
983 -- business group or legislation. eg If bus grp and leg code are both null,
984 -- the item to be added will be visible from all other business groups and
985 -- legislations, so if we add it it may clash with an existing name, even
986 -- though that name is not visible from null business group.
987 -- If a clash is present, return TRUE, otherwise return FALSE.
988 -----------------------------------------------------------------------------
989 --
993 dummy varchar2(1);
990 function will_clash_with_global(p_item_name in varchar2,
991 p_bus_grp in number,
992 p_leg_code in varchar2) return boolean is
994 startup_mode varchar2(10);
995 begin
996 -- set error tracking information
997 hr_utility.set_location('ffdict.will_clash_with_global',1);
998 --
999 -- get startup_mode based on current business group and legislation
1000 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
1001 --
1002 -- Ensure name does not clash with anywhere which will be able to 'see' the
1003 -- formula being validated.
1004 --
1005 begin
1006 select null into dummy from dual where exists
1007 (select null
1008 from ff_globals_f a
1009 where a.global_name = p_item_name
1010 and
1011 ( startup_mode = 'MASTER'
1012 or
1013 ( startup_mode = 'SEED'
1014 and
1015 ( a.legislation_code = p_leg_code
1016 or
1017 (a.legislation_code is null and a.business_group_id is null)
1018 or
1019 p_leg_code =
1020 (select c.legislation_code
1021 from per_business_groups_perf c
1022 where c.business_group_id = a.business_group_id
1023 )
1024 )
1025 )
1026 or
1027 ( startup_mode = 'NON-SEED'
1028 and
1029 ( a.business_group_id = p_bus_grp
1030 or
1031 (a.legislation_code is null and a.business_group_id is null)
1032 or
1033 (a.business_group_id is null and a.legislation_code = p_leg_code)
1034 )
1035 )
1036 ));
1037 exception
1038 when no_data_found then
1039 select null into dummy from dual where exists
1040 (select null
1041 from ff_globals_f a
1042 , ff_globals_f_tl b
1043 where b.global_name = p_item_name
1044 and a.global_id = b.global_id
1045 and
1046 ( startup_mode = 'MASTER'
1047 or
1048 ( startup_mode = 'SEED'
1049 and
1050 ( a.legislation_code = p_leg_code
1051 or
1052 (a.legislation_code is null and a.business_group_id is null)
1053 or
1054 p_leg_code =
1055 (select c.legislation_code
1056 from per_business_groups_perf c
1057 where c.business_group_id = a.business_group_id
1058 )
1059 )
1060 )
1061 or
1062 ( startup_mode = 'NON-SEED'
1063 and
1064 ( a.business_group_id = p_bus_grp
1065 or
1066 (a.legislation_code is null and a.business_group_id is null)
1067 or
1068 (a.business_group_id is null and a.legislation_code = p_leg_code)
1069 )
1070 )
1071 ));
1072
1073 when others then
1074 raise;
1075 end;
1076 -- Exception not raised, so name will clash - return TRUE
1077 return TRUE;
1078 exception
1079 when no_data_found then
1080 -- No data found, so name will not clash - return FALSE
1081 return FALSE;
1082 end will_clash_with_global;
1083 --
1084 --------------------------- will_clash_with_global ---------------------------
1085 --
1086 -- NAME
1087 -- will_clash_with_global
1088 -- DESCRIPTION
1089 -- Same principle as the original will_clash_with_global, except that
1090 -- translated names may clash with values for the same global_id.
1091 -----------------------------------------------------------------------------
1092 --
1093 function will_clash_with_global(p_global_id in number,
1094 p_item_name in varchar2,
1095 p_bus_grp in number,
1096 p_leg_code in varchar2) return boolean is
1097 dummy varchar2(1);
1098 startup_mode varchar2(10);
1099 l_debug boolean := hr_utility.debug_enabled;
1100 begin
1101 -- set error tracking information
1102 if l_debug then
1103 hr_utility.set_location('ffdict.will_clash_with_global:2',1);
1104 end if;
1105 --
1106 -- get startup_mode based on current business group and legislation
1107 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
1108 --
1109 -- Ensure name does not clash with anywhere which will be able to 'see' the
1110 -- formula being validated.
1111 --
1112 begin
1113 select null into dummy from dual where exists
1114 (select null
1115 from ff_globals_f a
1116 where a.global_name = p_item_name
1117 and a.global_id <> p_global_id
1118 and
1119 ( startup_mode = 'MASTER'
1120 or
1121 ( startup_mode = 'SEED'
1122 and
1123 ( a.legislation_code = p_leg_code
1124 or
1125 (a.legislation_code is null and a.business_group_id is null)
1126 or
1127 p_leg_code =
1128 (select c.legislation_code
1129 from per_business_groups_perf c
1130 where c.business_group_id = a.business_group_id
1131 )
1132 )
1133 )
1134 or
1138 or
1135 ( startup_mode = 'NON-SEED'
1136 and
1137 ( a.business_group_id = p_bus_grp
1139 (a.legislation_code is null and a.business_group_id is null)
1140 or
1141 (a.business_group_id is null and a.legislation_code = p_leg_code)
1142 )
1143 )
1144 ));
1145 exception
1146 when no_data_found then
1147 select null into dummy from dual where exists
1148 (select null
1149 from ff_globals_f a
1150 , ff_globals_f_tl b
1151 where b.global_name = p_item_name
1152 and a.global_id = b.global_id
1153 and a.global_id <> p_global_id
1154 and
1155 ( startup_mode = 'MASTER'
1156 or
1157 ( startup_mode = 'SEED'
1158 and
1159 ( a.legislation_code = p_leg_code
1160 or
1161 (a.legislation_code is null and a.business_group_id is null)
1162 or
1163 p_leg_code =
1164 (select c.legislation_code
1165 from per_business_groups_perf c
1166 where c.business_group_id = a.business_group_id
1167 )
1168 )
1169 )
1170 or
1171 ( startup_mode = 'NON-SEED'
1172 and
1173 ( a.business_group_id = p_bus_grp
1174 or
1175 (a.legislation_code is null and a.business_group_id is null)
1176 or
1177 (a.business_group_id is null and a.legislation_code = p_leg_code)
1178 )
1179 )
1180 ));
1181
1182 when others then
1183 raise;
1184 end;
1185 -- Exception not raised, so name will clash - return TRUE
1186 return TRUE;
1187 exception
1188 when no_data_found then
1189 -- No data found, so name will not clash - return FALSE
1190 return FALSE;
1191 end will_clash_with_global;
1192 --
1193 --------------------------- will_clash_with_entity ---------------------------
1194 --
1195 -- NAME
1196 -- will_clash_with_entity
1197 -- DESCRIPTION
1198 -- Determines whether the name will clash with user entities in other
1199 -- business groups or legislations which cannot be seen from current
1200 -- business group or legislation. eg If bus grp and leg code are both null,
1201 -- the item to be added will be visible from all other business groups and
1202 -- legislations, so if we add it it may clash with an existing name, even
1203 -- though that name is not visible from null business group.
1204 -- If a clash is present, return TRUE, otherwise return FALSE.
1205 --
1206 ------------------------------------------------------------------------------
1207 --
1208 function will_clash_with_entity(p_item_name in varchar2,
1209 p_bus_grp in number,
1210 p_leg_code in varchar2) return boolean is
1211 dummy varchar2(1);
1212 startup_mode varchar2(10);
1213 begin
1214 -- set error tracking information
1215 hr_utility.set_location('ffdict.will_clash_with_entity',1);
1216 --
1217 -- get startup_mode based on current business group and legislation
1218 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
1219 --
1220 -- Ensure name does not clash with anywhere which will be able to 'see' the
1221 -- formula being validated.
1222 --
1223 select null into dummy from dual where exists
1224 (select null
1225 from ff_user_entities a
1226 where a.user_entity_name = p_item_name
1227 and
1228 ( startup_mode = 'MASTER'
1229 or
1230 ( startup_mode = 'SEED'
1231 and
1232 ( a.legislation_code = p_leg_code
1233 or
1234 (a.legislation_code is null and a.business_group_id is null)
1235 or
1236 p_leg_code =
1237 (select c.legislation_code
1238 from per_business_groups_perf c
1239 where c.business_group_id = a.business_group_id
1240 )
1241 )
1242 )
1243 or
1244 ( startup_mode = 'NON-SEED'
1245 and
1246 ( a.business_group_id = p_bus_grp
1247 or
1248 (a.legislation_code is null and a.business_group_id is null)
1249 or
1250 (a.business_group_id is null and a.legislation_code = p_leg_code)
1251 )
1252 )
1253 ));
1254 -- Exception not raised, so name will clash - return TRUE
1255 return TRUE;
1256 exception
1257 when no_data_found then
1258 -- No data found, so name will not clash - return FALSE
1259 return FALSE;
1260 end will_clash_with_entity;
1261 --
1262 -------------------------- will_clash_with_function --------------------------
1263 --
1264 -- NAME
1265 -- will_clash_with_function
1266 -- DESCRIPTION
1267 -- Determines whether the name will clash with functions in other
1268 -- business groups or legislations which cannot be seen from current
1269 -- business group or legislation. Function names must not be duplicated
1270 -- for functions with class 'U' (User Defined).
1271 -- Also checks for clash against ANY function alias subject to business
1272 -- group and legislation visibility.
1273 -- If a clash is present, return TRUE, otherwise return FALSE.
1274 --
1278 p_class in varchar2,
1275 ------------------------------------------------------------------------------
1276 --
1277 function will_clash_with_function(p_item_name in varchar2,
1279 p_bus_grp in number,
1280 p_leg_code in varchar2) return boolean is
1281 dummy varchar2(1);
1282 startup_mode varchar2(10);
1283 begin
1284 -- set error tracking information
1285 hr_utility.set_location('ffdict.will_clash_with_function',1);
1286 --
1287 -- get startup_mode based on current business group and legislation
1288 startup_mode := ffstup.get_mode (p_bus_grp,p_leg_code);
1289 --
1290 -- Check for functions which have same name as p_item_name and class of 'U'
1291 -- or functions which have alias the same as p_item_name
1292 -- within the business group criteria
1293 --
1294 select null into dummy from dual where exists
1295 (select null
1296 from ff_functions a
1297 where
1298 (
1299 (a.name = p_item_name and a.class = p_class and p_class = 'U')
1300 or
1301 (a.alias_name = p_item_name)
1302 )
1303 and
1304 ( startup_mode = 'MASTER'
1305 or
1306 ( startup_mode = 'SEED'
1307 and
1308 ( a.legislation_code = p_leg_code
1309 or
1310 (a.legislation_code is null and a.business_group_id is null)
1311 or
1312 p_leg_code =
1313 (select c.legislation_code
1314 from per_business_groups_perf c
1315 where c.business_group_id = a.business_group_id
1316 )
1317 )
1318 )
1319 or
1320 ( startup_mode = 'NON-SEED'
1321 and
1322 ( a.business_group_id = p_bus_grp
1323 or
1324 (a.legislation_code is null and a.business_group_id is null)
1325 or
1326 (a.business_group_id is null and a.legislation_code = p_leg_code)
1327 )
1328 )
1329 ));
1330 -- Exception not raised, so name will clash - return TRUE
1331 return TRUE;
1332 exception
1333 when no_data_found then
1334 -- No data found, so name will not clash - return FALSE
1335 return FALSE;
1336 end will_clash_with_function;
1337 --
1338 -------------------------- will_clash_with_context ---------------------------
1339 --
1340 -- NAME
1341 -- will_clash_with_context
1342 -- DESCRIPTION
1343 -- Returns TRUE if named item will clash with a name used as a context.
1344 -- Otherwise returns FALSE.
1345 --
1346 ------------------------------------------------------------------------------
1347 --
1348 function will_clash_with_context(p_item_name in varchar2) return boolean is
1349 dummy varchar2(1);
1350 begin
1351 -- set error tracking information
1352 hr_utility.set_location('ffdict.will_clash_with_context',1);
1353 -- Check if there any rows in FF_CONTEXTS for this name
1354 --
1355 select 'X' into dummy from dual where exists
1356 (select null from ff_contexts where context_name = upper(p_item_name));
1357 --
1358 -- Exception not raised, so item is a context - return TRUE.
1359 return TRUE;
1360 exception
1361 when no_data_found then
1362 -- No data found, so item not used - return FALSE
1363 return FALSE;
1364 end will_clash_with_context;
1365 --
1366 ------------------------------ validate_formula -------------------------------
1367 --
1368 -- NAME
1369 -- validate_formula
1370 -- DESCRIPTION
1371 -- Procedure which succeeds if name supplied will make a valid formula
1372 -- name. Fails with exception and error if name is invalid.
1373 --
1374 -------------------------------------------------------------------------------
1375 --
1376 procedure validate_formula(p_formula_name in out nocopy varchar2,
1377 p_formula_type_id in number,
1378 p_bus_grp in number,
1379 p_leg_code in varchar2) is
1380 rgeflg varchar2(1);
1381 begin
1382 -- set error tracking information
1383 hr_utility.set_location('ffdict.validate_formula',1);
1384 --
1385 begin
1386 -- Check if name legal format eg no spaces, or special characters
1387 hr_chkfmt.checkformat (p_formula_name, 'DB_ITEM_NAME', p_formula_name,
1388 null,null,'Y',rgeflg,null);
1389 exception
1390 when hr_utility.hr_error then
1391 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1392 hr_utility.set_message_token(802,'VALUE_NAME','FF93_FORMULA');
1393 hr_utility.raise_error;
1394 end;
1395 --
1396 -- set error tracking information
1397 hr_utility.set_location('ffdict.validate_formula',2);
1401 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1398 --
1399 if (will_clash_with_formula(p_formula_name, p_formula_type_id,
1400 p_bus_grp,p_leg_code)) then
1402 hr_utility.set_message_token('1',p_formula_name);
1403 hr_utility.set_message_token(802,'2','FF93_FORMULA');
1404 hr_utility.raise_error;
1405 end if;
1406 end validate_formula;
1407 --
1408 ------------------------------ validate_formula -------------------------------
1409 --
1410 -- NAME
1411 -- validate_formula - Overload
1412 -- DESCRIPTION
1413 -- Procedure which succeeds if name supplied will make a valid formula
1414 -- name. Fails with exception and error if name is invalid.
1415 -- Overloaded to allow date effective formula creation.
1416 --
1417 -------------------------------------------------------------------------------
1418 --
1419 procedure validate_formula
1420 (p_formula_name in out nocopy varchar2
1421 ,p_formula_type_id in number
1422 ,p_bus_grp in number
1423 ,p_leg_code in varchar2
1424 ,p_effective_start_date in date
1425 ,p_effective_end_date in out nocopy date
1426 ) is
1427 --
1428 rgeflg varchar2(1);
1429 --
1430 cursor csr_formula_clash(p_startup_mode varchar2) is
1431 select 'X'
1432 from ff_formulas_f ff
1433 where upper(ff.formula_name) = upper(p_formula_name)
1434 and ff.formula_type_id = p_formula_type_id
1435 and p_effective_start_date between ff.effective_start_date
1436 and ff.effective_end_date
1437 and (p_startup_mode = 'MASTER'
1438 or (p_startup_mode = 'SEED' and
1439 (ff.legislation_code = p_leg_code
1440 or
1441 (ff.legislation_code is null and ff.business_group_id is null)
1442 or
1443 p_leg_code =
1444 (select c.legislation_code
1445 from per_business_groups_perf c
1446 where c.business_group_id = ff.business_group_id
1447 )
1448 )
1449 )
1450 or (p_startup_mode = 'NON-SEED'
1451 and (ff.business_group_id = p_bus_grp
1452 or (ff.legislation_code is null
1453 and ff.business_group_id is null)
1454 or (ff.business_group_id is null
1455 and ff.legislation_code = p_leg_code))));
1456 --
1457 cursor csr_new_end_date(p_startup_mode varchar2) is
1458 select (min(ff.effective_start_date)-1)
1459 from ff_formulas_f ff
1460 where ff.formula_name = p_formula_name
1461 and ff.formula_type_id = p_formula_type_id
1462 and p_effective_end_date between ff.effective_start_date
1463 and ff.effective_end_date
1464 and (p_startup_mode = 'MASTER'
1465 or (p_startup_mode = 'SEED' and
1466 (ff.legislation_code = p_leg_code
1467 or
1468 (ff.legislation_code is null and ff.business_group_id is null)
1469 or
1470 p_leg_code =
1471 (select c.legislation_code
1472 from per_business_groups_perf c
1473 where c.business_group_id = ff.business_group_id
1474 )
1475 )
1476 )
1477 or (p_startup_mode = 'NON-SEED'
1478 and (ff.business_group_id = p_bus_grp
1479 or (ff.legislation_code is null
1480 and ff.business_group_id is null)
1481 or (ff.business_group_id is null
1482 and ff.legislation_code = p_leg_code))));
1483 --
1484 l_dummy varchar2(1);
1485 l_effective_end_date date := null;
1486 l_startup_mode varchar2(10);
1487 l_name ff_formulas_f.formula_name%type := p_formula_name;
1488 l_pdummy varchar2(80);
1489 --
1490 begin
1491 --
1492 -- set error tracking information
1493 hr_utility.set_location('ffdict.validate_formula',1);
1494 --
1495 begin
1496 --
1497 -- Check if name legal format eg no spaces, or special characters
1498 -- hr_chkfmt.checkformat (p_formula_name, 'DB_ITEM_NAME', p_formula_name,
1499 -- null,null,'Y',rgeflg,null);
1500
1501 -- Allowing spaces in formula names(Bug Fix: 4768014)
1502 hr_chkfmt.checkformat (l_name,'PAY_NAME',l_pdummy, null, null, 'N', l_pdummy, null);
1503
1504 exception
1505 when hr_utility.hr_error then
1506 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1507 hr_utility.set_message_token(802,'VALUE_NAME','FF93_FORMULA');
1508 hr_utility.raise_error;
1509 --
1510 end;
1511 --
1512 -- set error tracking information
1513 hr_utility.set_location('ffdict.validate_formula',2);
1514 --
1515 l_startup_mode := ffstup.get_mode(p_bus_grp,p_leg_code);
1516 --
1517 open csr_formula_clash(l_startup_mode);
1518 fetch csr_formula_clash into l_dummy;
1519 if csr_formula_clash%found then
1520 --
1521 close csr_formula_clash;
1522 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1523 hr_utility.set_message_token('1',p_formula_name);
1524 hr_utility.set_message_token(802,'2','FF93_FORMULA');
1525 hr_utility.raise_error;
1526 --
1527 else
1528 --
1529 close csr_formula_clash;
1530 --
1531 end if;
1532 --
1533 open csr_new_end_date(l_startup_mode);
1534 fetch csr_new_end_date into l_effective_end_date;
1538 p_effective_end_date := l_effective_end_date;
1535 if l_effective_end_date is not null then
1536 --
1537 close csr_new_end_date;
1539 --
1540 else
1541 --
1542 close csr_new_end_date;
1543 p_effective_end_date := p_effective_end_date;
1544 --
1545 end if;
1546 --
1547 end validate_formula;
1548 --
1549 ------------------------------ validate_dbitem -------------------------------
1550 --
1551 -- NAME
1552 -- validate_dbitem
1553 -- DESCRIPTION
1554 -- Procedure which succeeds if name supplied will make a valid database
1555 -- item name. Fails with exception and error if name is invalid.
1556 --
1557 ------------------------------------------------------------------------------
1558 --
1559 procedure validate_dbitem(p_dbi_name in out nocopy varchar2,
1560 p_user_entity_id in number) is
1561 bg_id number;
1562 leg_code varchar2(30);
1563 rgeflg varchar2(1);
1564 begin
1565 -- set error tracking information
1566 hr_utility.set_location('ffdict.validate_dbitem',1);
1567 --
1568 -- Check if name legal format eg no spaces, or special characters
1569 begin
1570 -- Check if name legal format eg no spaces, or special characters
1571 hr_chkfmt.checkformat (p_dbi_name, 'DB_ITEM_NAME', p_dbi_name,
1572 null,null,'Y',rgeflg,null);
1573 exception
1574 when hr_utility.hr_error then
1575 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1576 hr_utility.set_message_token(802,'VALUE_NAME','FF91_DBITEM_NAME');
1577 hr_utility.raise_error;
1578 end;
1579 --
1580 -- set error tracking information
1581 hr_utility.set_location('ffdict.validate_dbitem',2);
1582 --
1583 -- Fetch business group and legislation details for current user entity
1584 select business_group_id, legislation_code
1585 into bg_id, leg_code
1586 from ff_user_entities
1587 where user_entity_id = p_user_entity_id;
1588 --
1589 -- set error tracking information
1590 hr_utility.set_location('ffdict.validate_dbitem',3);
1591 --
1592 -- New DB item name cannot be same as existing database item visible from
1593 -- business group and legislation of current user_entity
1594 if (will_clash_with_dbitem(p_dbi_name, bg_id, leg_code)) then
1595 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1596 hr_utility.set_message_token('1',p_dbi_name);
1597 hr_utility.set_message_token(802,'2','FF91_DBITEM_NAME');
1598 hr_utility.raise_error;
1599 end if;
1600 -- set error tracking information
1601 hr_utility.set_location('ffdict.validate_dbitem',4);
1602 --
1603 -- New DB item name cannot be same as existing item in any verified formula
1604 if (ffdict.non_dbi_used_in_formula(p_dbi_name, bg_id, leg_code)) then
1605 hr_utility.set_message (802,'FF75_ITEM_USED_IN_FORMULA');
1606 hr_utility.set_message_token('1',p_dbi_name);
1607 hr_utility.raise_error;
1608 end if;
1609 -- set error tracking information
1610 hr_utility.set_location('ffdict.validate_dbitem',5);
1611 --
1612 -- New DB item name cannot be same as existing context name
1613 if (ffdict.will_clash_with_context(p_dbi_name)) then
1614 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1615 hr_utility.set_message_token('1',p_dbi_name);
1616 hr_utility.set_message_token(802,'2','FF92_CONTEXT');
1617 hr_utility.raise_error;
1618 end if;
1619 end validate_dbitem;
1620 --
1621 -------------------------- core_validate_tl_dbitem ---------------------------
1622 --
1623 procedure core_validate_tl_dbitem
1624 (p_user_name in varchar2
1625 ,p_user_entity_id in number
1626 ,p_tl_user_name in out nocopy varchar2
1627 ,p_outcome out nocopy varchar2
1628 ) is
1629 l_tl_user_name varchar2(2000);
1630 i binary_integer;
1631 j binary_integer;
1632 begin
1633 g_debug := hr_utility.debug_enabled;
1634
1635 if g_debug then
1636 hr_utility.set_location('ffdict.core_validate_tl_dbi',1);
1637 end if;
1638
1639 --
1640 -- Initialise outcome to SUCCESS.
1641 --
1642 p_outcome := 'S';
1643
1644 --
1645 -- Validate (and possibly reformat) the name so that it is a valid
1646 -- database item name.
1647 --
1648 l_tl_user_name :=
1649 ff_dbi_utils_pkg.str2dbiname(p_str => p_tl_user_name);
1650 p_tl_user_name := l_tl_user_name;
1651
1652 if g_debug then
1653 hr_utility.set_location('ffdict.core_validate_tl_dbi',2);
1654 end if;
1655
1656 --
1657 -- Fetch business group and legislation details for current user entity.
1658 --
1659 if (tmp_ue_id is null or tmp_ue_id <> p_user_entity_id) then
1660 fetch_ue_details(p_user_entity_id);
1661 end if;
1662
1663 --
1664 -- New DB item name cannot be same as existing database item visible from
1665 -- business group and legislation of current user_entity.
1666 --
1667 if tl_dbi_will_clash
1668 (p_tl_user_name => l_tl_user_name
1669 ,p_user_name => p_user_name
1670 ,p_user_entity_id => p_user_entity_id
1671 ,p_business_group_id => tmp_bg_id
1672 ,p_legislation_code => tmp_leg_code
1673 ) then
1674
1675 if g_debug then
1676 hr_utility.set_location('ffdict.core_validate_tl_dbi',3);
1677 end if;
1678
1679 p_outcome := 'D';
1680 return;
1681 end if;
1682
1683 if g_debug then
1687 --
1684 hr_utility.set_location('ffdict.core_validate_tl_dbi',4);
1685 end if;
1686
1688 -- New DB item name cannot be same as a Formula Context name.
1689 --
1690 if ffdict.will_clash_with_context(l_tl_user_name) then
1691
1692 if g_debug then
1693 hr_utility.set_location('ffdict.core_validate_tl_dbi',5);
1694 end if;
1695
1696 p_outcome := 'C';
1697 return;
1698 end if;
1699
1700 if g_debug then
1701 hr_utility.set_location('ffdict.core_validate_tl_dbi',6);
1702 end if;
1703
1704 --
1705 -- New DB item name cannot be same as existing item in any verified formula.
1706 -- Need to ensure that there is no clash with non-DBI and Context item
1707 -- names used by the formula (inputs, outputs, locals).
1708 --
1709 if ffdict.non_dbi_used_in_formula
1710 (p_item_name => l_tl_user_name
1711 ,p_bus_grp => tmp_bg_id
1712 ,p_leg_code => tmp_leg_code
1713 ) then
1714 if g_debug then
1715 hr_utility.set_location('ffdict.core_validate_tl_dbi',7);
1716 end if;
1717
1718 p_outcome := 'F';
1719 return;
1720 end if;
1721
1722 if g_debug then
1723 hr_utility.set_location('ffdict.core_validate_tl_dbi',8);
1724 end if;
1725
1726 if g_debug then
1727 hr_utility.set_location('ffdict.core_validate_tl_dbi',9);
1728 end if;
1729 end core_validate_tl_dbitem;
1730 --
1731 ------------------------------ validate_tl_dbi -------------------------------
1732 --
1733 procedure validate_tl_dbi
1734 (p_user_name in varchar2
1735 ,p_user_entity_id in number
1736 ,p_tl_user_name in out nocopy varchar2
1737 ) is
1738 l_tl_user_name varchar2(2000);
1739 l_outcome varchar2(10);
1740 begin
1741 g_debug := hr_utility.debug_enabled;
1742
1743 if g_debug then
1744 hr_utility.set_location('ffdict.validate_tl_dbi',1);
1745 end if;
1746
1747 l_tl_user_name := p_tl_user_name;
1748
1749 if g_debug then
1750 hr_utility.set_location('ffdict.validate_tl_dbi',2);
1751 end if;
1752
1753 core_validate_tl_dbitem
1754 (p_user_name => p_user_name
1755 ,p_user_entity_id => p_user_entity_id
1756 ,p_tl_user_name => l_tl_user_name
1757 ,p_outcome => l_outcome
1758 );
1759
1763
1760 if g_debug then
1761 hr_utility.set_location('ffdict.validate_tl_dbi',3);
1762 end if;
1764 --
1765 -- New DB item name cannot be same as existing database item visible from
1766 -- business group and legislation of current user_entity.
1767 --
1768 if l_outcome = 'D' then
1769 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1770 hr_utility.set_message_token('1', l_tl_user_name);
1771 hr_utility.set_message_token(802,'2','FF91_DBITEM_NAME');
1772 hr_utility.raise_error;
1773 end if;
1774
1775 if g_debug then
1776 hr_utility.set_location('ffdict.validate_tl_dbi',4);
1777 end if;
1778
1779 --
1780 -- New DB item name cannot be same as existing context name.
1781 --
1782 if l_outcome = 'C' then
1783 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1784 hr_utility.set_message_token('1', l_tl_user_name);
1785 hr_utility.set_message_token(802,'2','FF92_CONTEXT');
1786 hr_utility.raise_error;
1787 end if;
1788
1789 if g_debug then
1790 hr_utility.set_location('ffdict.validate_tl_dbi',5);
1791 end if;
1792
1793 --
1794 -- New DB item name cannot be same as existing item in any verified
1795 -- formula. Need to ensure that there is no clash with non-DBI and
1796 -- Context item names used by the formula (inputs, outputs, locals).
1797 --
1798 if l_outcome = 'F' then
1799 hr_utility.set_message (802,'FF75_ITEM_USED_IN_FORMULA');
1800 hr_utility.set_message_token('1', l_tl_user_name);
1801 hr_utility.raise_error;
1802 end if;
1803
1804 p_tl_user_name := l_tl_user_name;
1805 end validate_tl_dbi;
1806 --
1807 ------------------------------ validate_context -------------------------------
1808 --
1809 -- NAME
1810 -- validate_context
1811 -- DESCRIPTION
1812 -- Procedure which succeeds if name supplied will make a valid context
1813 -- name. Fails with exception and error if name is invalid.
1814 --
1815 -------------------------------------------------------------------------------
1816 --
1817 procedure validate_context(p_ctx_name in out nocopy varchar2) is
1818 rgeflg varchar2(1);
1819 begin
1820 -- set error tracking information
1821 hr_utility.set_location('ffdict.validate_context',1);
1822 --
1823 -- Check if name legal format eg no spaces, or special characters
1824 begin
1825 -- Check if name legal format eg no spaces, or special characters
1826 hr_chkfmt.checkformat (p_ctx_name, 'DB_ITEM_NAME', p_ctx_name,
1827 null,null,'Y',rgeflg,null);
1828 exception
1829 when hr_utility.hr_error then
1830 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1831 hr_utility.set_message_token(802,'VALUE_NAME','FF92_CONTEXT');
1832 hr_utility.raise_error;
1833 end;
1834 -- set error tracking information
1835 hr_utility.set_location('ffdict.validate_context',2);
1836 --
1837 -- New DB item name cannot be same as existing context name
1838 if (ffdict.will_clash_with_context(p_ctx_name)) then
1839 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1840 hr_utility.set_message_token('1',p_ctx_name);
1841 hr_utility.set_message_token(802,'2','FF92_CONTEXT');
1842 hr_utility.raise_error;
1843 end if;
1844 -- set error tracking information
1845 hr_utility.set_location('ffdict.validate_context',3);
1846 --
1847 -- Pass null so all formulae are considered
1848 if (ffdict.is_used_in_formula(p_ctx_name, null, null)) then
1849 hr_utility.set_message (802,'FF75_ITEM_USED_IN_FORMULA');
1850 hr_utility.set_message_token('1',p_ctx_name);
1851 hr_utility.raise_error;
1852 end if;
1853 -- set error tracking information
1854 hr_utility.set_location('ffdict.validate_context',4);
1855 --
1859 hr_utility.set_message_token('1',p_ctx_name);
1856 -- Pass null bus grp and leg code so all DB items are considered
1857 if (ffdict.will_clash_with_dbitem(p_ctx_name, null, null)) then
1858 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
1860 hr_utility.set_message_token(802,'2','FF91_DBITEM_NAME');
1861 hr_utility.raise_error;
1862 end if;
1863 --
1864 end validate_context;
1865 --
1866 ---------------------------- validate_user_entity -----------------------------
1867 --
1868 -- NAME
1869 -- validate_user_entity
1870 -- DESCRIPTION
1871 -- Procedure which succeeds if name supplied will make a valid user
1872 -- entity name. Fails with exception and error if name is invalid.
1873 --
1874 -------------------------------------------------------------------------------
1875 --
1876 procedure validate_user_entity(p_ue_name in out nocopy varchar2,
1877 p_bus_grp in number,
1878 p_leg_code in varchar2) is
1879 rgeflg varchar2(1);
1880 begin
1881 -- set error tracking information
1882 hr_utility.set_location('ffdict.validate_user_entity',1);
1883 --
1884 -- Check if name legal format eg no spaces, or special characters
1885 begin
1886 -- Check if name legal format eg no spaces, or special characters
1887 hr_chkfmt.checkformat (p_ue_name, 'DB_ITEM_NAME', p_ue_name,
1888 null,null,'Y',rgeflg,null);
1889 exception
1890 when hr_utility.hr_error then
1891 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1892 hr_utility.set_message_token(802,'VALUE_NAME','FF94_USER_ENTITY');
1893 hr_utility.raise_error;
1894 end;
1895 -- set error tracking information
1896 hr_utility.set_location('ffdict.validate_user_entity',2);
1897 --
1898 if (will_clash_with_entity(p_ue_name,p_bus_grp,p_leg_code)) then
1899 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1900 hr_utility.set_message_token('1',p_ue_name);
1901 hr_utility.set_message_token(802,'2','FF94_USER_ENTITY');
1902 hr_utility.raise_error;
1903 end if;
1904 end validate_user_entity;
1908 -- NAME
1905 --
1906 ----------------------------- validate_function ------------------------------
1907 --
1909 -- validate_function
1910 -- DESCRIPTION
1911 -- Procedure which succeeds if name supplied will make a valid function
1912 -- name. Fails with exception and error if name is invalid.
1913 --
1914 ------------------------------------------------------------------------------
1915 --
1916 procedure validate_function(p_func_name in out nocopy varchar2,
1917 p_class in varchar2,
1918 p_alias in varchar2,
1919 p_bus_grp in number,
1920 p_leg_code in varchar2) is
1921 rgeflg varchar2(1);
1922 begin
1923 -- set error tracking information
1924 hr_utility.set_location('ffdict.validate_function',1);
1925 --
1926 -- Check if name legal format eg no spaces, or special characters
1927 begin
1928 -- Check if name legal format eg no spaces, or special characters
1929 hr_chkfmt.checkformat (p_func_name, 'DB_ITEM_NAME', p_func_name,
1930 null,null,'Y',rgeflg,null);
1931 exception
1932 when hr_utility.hr_error then
1933 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1934 hr_utility.set_message_token(802,'VALUE_NAME','FF95_FUNCTION');
1935 hr_utility.raise_error;
1936 end;
1937 -- set error tracking information
1938 hr_utility.set_location('ffdict.validate_function',2);
1939 --
1940 -- Check function name
1941 if (will_clash_with_function(p_func_name,p_class, p_bus_grp,p_leg_code)) then
1942 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1943 hr_utility.set_message_token('1',p_func_name);
1944 hr_utility.set_message_token(802,'2','FF95_FUNCTION');
1945 hr_utility.raise_error;
1946 end if;
1947 -- set error tracking information
1948 hr_utility.set_location('ffdict.validate_function',3);
1949 --
1950 -- Check alias - cannot be same as another alias or function name
1951 if (p_alias is not null) then
1952 if (will_clash_with_function(p_alias,p_class, p_bus_grp,p_leg_code)) then
1953 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1954 hr_utility.set_message_token('1',p_func_name);
1955 hr_utility.set_message_token(802,'2','FF95_FUNCTION');
1956 hr_utility.raise_error;
1957 end if;
1958 end if;
1959 end validate_function;
1960 --
1961 ------------------------------ validate_global -------------------------------
1962 --
1963 -- NAME
1964 -- validate_global
1965 -- DESCRIPTION
1966 -- Procedure which succeeds if name supplied will make a valid global
1967 -- variable name. Fails with exception and error if name is invalid.
1968 --
1969 ------------------------------------------------------------------------------
1970 --
1971 procedure validate_global(p_glob_name in out nocopy varchar2,
1972 p_bus_grp in number,
1973 p_leg_code in varchar2) is
1974 rgeflg varchar2(1);
1975 begin
1976 -- set error tracking information
1977 hr_utility.set_location('ffdict.validate_global',1);
1978 --
1979 -- Check if name legal format eg no spaces, or special characters
1980 begin
1981 -- Check if name legal format eg no spaces, or special characters
1982 hr_chkfmt.checkformat (p_glob_name, 'DB_ITEM_NAME', p_glob_name,
1983 null,null,'Y',rgeflg,null);
1984 exception
1985 when hr_utility.hr_error then
1986 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
1987 hr_utility.set_message_token(802,'VALUE_NAME','FF90_GLOBAL_NAME');
1988 hr_utility.raise_error;
1989 end;
1990
1991 -- set error tracking information
1992 hr_utility.set_location('ffdict.validate_global',2);
1993 --
1994 if (will_clash_with_global(p_glob_name,p_bus_grp,p_leg_code)) then
1995 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
1996 hr_utility.set_message_token('1',p_glob_name);
1997 hr_utility.set_message_token(802,'2','FF90_GLOBAL_NAME');
1998 hr_utility.raise_error;
1999 end if;
2000 -- set error tracking information
2001 hr_utility.set_location('ffdict.validate_global',3);
2002 --
2003 if (is_used_in_formula(p_glob_name, p_bus_grp, p_leg_code)) then
2004 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
2005 hr_utility.set_message_token('1',p_glob_name);
2006 hr_utility.raise_error;
2007 end if;
2008 -- set error tracking information
2009 hr_utility.set_location('ffdict.validate_global',4);
2010 --
2011 if (will_clash_with_dbitem(p_glob_name, p_bus_grp, p_leg_code)) then
2012 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
2013 hr_utility.set_message_token('1',p_glob_name);
2014 hr_utility.set_message_token(802,'2','FF91_DBITEM_NAME');
2015 hr_utility.raise_error;
2016 end if;
2017 -- set error tracking information
2018 hr_utility.set_location('ffdict.validate_global',5);
2019 --
2020 if (will_clash_with_context(p_glob_name)) then
2021 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
2022 hr_utility.set_message_token('1',p_glob_name);
2023 hr_utility.set_message_token(802,'2','FF92_CONTEXT');
2024 hr_utility.raise_error;
2025 end if;
2026 end validate_global;
2027 --
2028 ---------------------------- validate_tl_global -------------------------------
2029 procedure validate_tl_global(p_global_id in number,
2030 p_glob_name in varchar2,
2031 p_bus_grp in number,
2032 p_leg_code in varchar2) is
2033 l_glob_name varchar2(2000);
2034 l_dbi varchar2(2000);
2035 begin
2036 l_glob_name := p_glob_name;
2037 --
2038 -- This is an existing global - the usual case.
2039 --
2040 if p_global_id is not null then
2044 ,p_leg_code => p_leg_code
2041 if will_clash_with_global(p_global_id => p_global_id
2042 ,p_item_name => p_glob_name
2043 ,p_bus_grp => p_bus_grp
2045 ) then
2046 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
2047 hr_utility.set_message_token('1',p_glob_name);
2048 hr_utility.set_message_token(802,'2','FF90_GLOBAL_NAME');
2049 hr_utility.raise_error;
2050 end if;
2051
2052 if g_glb_id is null or g_glb_id <> p_global_id then
2053 set_global_dbi_details(global_id => p_global_id);
2054 if g_glb_id is null or g_glb_id <> p_global_id then
2055 --
2056 -- For some reason, the database item does not exist.
2057 --
2058 return;
2059 end if;
2060 end if;
2061
2062 ffdict.validate_tl_dbi
2063 (p_user_name => g_glb_dbi
2064 ,p_user_entity_id => g_glb_ueid
2065 ,p_tl_user_name => l_glob_name
2066 );
2067 --
2068 -- This is a new global e.g. global translation button from form before
2069 -- the record has been saved.
2070 --
2071 else
2072 if will_clash_with_global(p_item_name => p_glob_name
2073 ,p_bus_grp => p_bus_grp
2074 ,p_leg_code => p_leg_code
2075 ) then
2076 hr_utility.set_message(802,'FF52_NAME_ALREADY_USED');
2077 hr_utility.set_message_token('1',p_glob_name);
2078 hr_utility.set_message_token(802,'2','FF90_GLOBAL_NAME');
2079 hr_utility.raise_error;
2080 end if;
2081
2082 --
2083 -- Now look at the generated database item name.
2084 --
2085 l_dbi := ff_dbi_utils_pkg.str2dbiname(p_str => l_glob_name);
2086
2087 if (will_clash_with_dbitem(l_dbi, tmp_bg_id, tmp_leg_code)) then
2088 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
2089 hr_utility.set_message_token('1',l_dbi);
2090 hr_utility.set_message_token(802,'2','FF91_DBITEM_NAME');
2091 hr_utility.raise_error;
2092 end if;
2093
2094 if (ffdict.non_dbi_used_in_formula(l_dbi, tmp_bg_id, tmp_leg_code)) then
2095 hr_utility.set_message (802,'FF75_ITEM_USED_IN_FORMULA');
2096 hr_utility.set_message_token('1',l_dbi);
2097 hr_utility.raise_error;
2098 end if;
2099
2100 if (ffdict.will_clash_with_context(l_dbi)) then
2101 hr_utility.set_message (802,'FF52_NAME_ALREADY_USED');
2102 hr_utility.set_message_token('1',l_dbi);
2103 hr_utility.set_message_token(802,'2','FF92_CONTEXT');
2104 hr_utility.raise_error;
2105 end if;
2106 end if;
2107 end validate_tl_global;
2108 --
2109 -------------------------------- validate_rcu ---------------------------------
2110 --
2111 -- NAME
2112 -- validate_rcu
2113 -- DESCRIPTION
2114 -- Check adding route context usage does not make any compiled formulae
2115 -- invalid. Can also be used for checking route parameters.
2116 -- Returns TRUE if OK, FALSE if not OK
2117 --
2118 -------------------------------------------------------------------------------
2119 --
2120 procedure validate_rcu(p_route_id in number) is
2121 dummy varchar2(1);
2122 begin
2123 -- set error tracking information
2124 hr_utility.set_location('ffdict.validate_rcu',1);
2125 --
2126 -- Look for any compiled formulas which use database items based on this
2127 -- route.
2128 select 'X' into dummy from dual
2129 where exists
2130 (
2131 select /*+ ORDERED
2132 INDEX(C FF_USER_ENTITIES_FK1)
2133 INDEX(B FF_DATABASE_ITEMS_FK1)
2134 INDEX(A FF_FDI_USAGES_F_N50) */
2135 null
2136 from ff_user_entities c,
2137 ff_database_items b,
2138 ff_fdi_usages_f a
2139 where a.item_name = b.user_name
2140 and a.usage = 'D'
2141 and b.user_entity_id = c.user_entity_id
2142 and c.route_id = p_route_id
2143 );
2144 -- if select succeeds, then at least one compiled formula uses route
2145 -- so raise an error
2146 hr_utility.set_message (802,'FF76_WILL_INVALIDATE_FORMULA');
2147 hr_utility.raise_error;
2148
2149 exception
2150 -- route items not used, so succeed
2151 when no_data_found then
2152 null;
2153 end validate_rcu;
2154 --
2155 -------------------------------- validate_rpv ---------------------------------
2156 --
2157 -- NAME
2158 -- validate_rpv
2159 -- DESCRIPTION
2160 -- Check adding route parameter value does not make any compiled formulae
2161 -- invalid. Returns TRUE if OK, FALSE if not OK
2162 -- Only do validation if operation is not resulting from a cascade delete
2163 -- of user entities when this validation will have bee done already
2164 --
2165 -------------------------------------------------------------------------------
2166 --
2167 procedure validate_rpv(p_user_entity_id in number) is
2168 dummy varchar2(1);
2169 bg_id number;
2170 leg_code varchar2(30);
2171 startup_mode varchar2(10);
2172 mutating_table exception;
2173 invalid_cursor exception;
2174 pragma exception_init (mutating_table, -4091);
2175 pragma exception_init (invalid_cursor, -1001);
2176 begin
2177 -- set error tracking information
2178 hr_utility.set_location('ffdict.validate_rpv',1);
2179 --
2180 -- attempt to get BG details from ff_user_entities, which might be mutating
2181 -- so trap error. If it is mutating, can skip validation because a cascade
2182 -- delete is in progress and the deletion will have been validated already
2183 --
2184 select business_group_id, legislation_code
2185 into bg_id, leg_code
2186 from ff_user_entities
2187 where user_entity_id = p_user_entity_id;
2188 --
2192 startup_mode := ffstup.get_mode (bg_id,leg_code);
2189 -- set error tracking information
2190 hr_utility.set_location('ffdict.validate_rpv',2);
2191 -- get startup_mode based on current business group and legislation
2193 --
2194 -- set error tracking information
2195 hr_utility.set_location('ffdict.validate_rpv',3);
2196 -- Look for any compiled formulas which use database items based on this
2197 -- user entity. Need to consider business group and legislation
2198 select 'X' into dummy from dual
2199 where exists
2200 (
2201 select /*+ ORDERED
2202 INDEX(C FF_DATABASE_ITEMS_FK1)
2203 INDEX(A FF_FDI_USAGES_F_N50)
2204 INDEX(B FF_FORMULAS_F_PK) */ null
2205 from ff_database_items c,
2206 ff_fdi_usages_f a,
2207 ff_formulas_f b
2208 where a.formula_id = b.formula_id
2209 and a.item_name = c.user_name
2210 and a.usage = 'D'
2211 and c.user_entity_id = p_user_entity_id
2212 and
2213 ( startup_mode = 'MASTER'
2214 or
2215 ( startup_mode = 'SEED'
2216 and
2217 ( b.legislation_code = leg_code
2218 or
2219 (legislation_code is null and business_group_id is null)
2220 or
2221 leg_code =
2222 (select d.legislation_code
2223 from per_business_groups_perf d
2224 where d.business_group_id = b.business_group_id
2225 )
2226 )
2227 )
2228 or
2229 ( startup_mode = 'NON-SEED'
2230 and
2231 ( b.business_group_id = bg_id
2232 or
2233 (b.legislation_code is null and b.business_group_id is null)
2234 or
2235 (b.business_group_id is null and b.legislation_code = leg_code)
2236 )
2237 )
2238 )
2239 );
2240 -- if select succeeds, then at least one compiled formula uses route
2241 -- so raise an error
2242 hr_utility.set_message (802,'FF76_WILL_INVALIDATE_FORMULA');
2243 hr_utility.raise_error;
2244 exception
2245 -- route items not used, so succeed
2246 when no_data_found then
2247 -- set error tracking information
2248 hr_utility.set_location('ffdict.validate_rpv',4);
2249 when mutating_table then
2250 -- set error tracking information
2251 hr_utility.set_location('ffdict.validate_rpv',5);
2252 when invalid_cursor then
2253 hr_utility.set_location('ffdict.validate_rpv',6);
2254 end validate_rpv;
2255 --
2256 ---------------------------- create_global_dbitem -----------------------------
2257 --
2258 -- NAME
2259 -- create_global_dbitem
2260 -- DESCRIPTION
2261 -- Does third party inserts to create a database item which is used within
2262 -- formulae to access the global variable value
2263 --
2264 -------------------------------------------------------------------------------
2265 --
2266 procedure create_global_dbitem(p_name in varchar2,
2267 p_data_type in varchar2,
2268 p_global_id in number,
2269 p_business_group_id in number,
2270 p_legislation_code in varchar2,
2271 p_created_by in number,
2272 p_creation_date in date) is
2273 l_route_id number;
2274 l_route_parameter_id number;
2275 item_present exception;
2276 begin
2277 -- set error tracking information
2278 hr_utility.set_location('ffdict.create_global_dbitem',1);
2279 --
2280 -- Check whether DB item has already been created for this global
2281 begin
2282 select creator_id
2283 into l_route_id
2284 from ff_user_entities
2285 where creator_id = p_global_id
2286 and creator_type = 'S';
2287 raise item_present;
2288 exception
2289 when no_data_found then
2290 null;
2291 end;
2292 -- set error tracking information
2293 hr_utility.set_location('ffdict.create_global_dbitem',2);
2294 --
2295 -- Get route id for globals route. If not exists, create new one.
2296 begin
2297 select route_id
2298 into l_route_id
2299 from ff_routes
2300 where route_name = 'FF_GLOBALS';
2301 exception
2302 when no_data_found then
2303 l_route_id := null;
2304 end;
2305 --
2306 if l_route_id is null then
2307 -- set error tracking information
2308 hr_utility.set_location('ffdict.create_global_dbitem',3);
2309 --
2310 insert into ff_routes
2311 (route_id,
2312 route_name,
2313 user_defined_flag,
2314 description,
2315 text,
2316 created_by,
2317 creation_date
2318 )
2319 values
2320 (
2321 ff_routes_s.nextval,
2322 'FF_GLOBALS',
2323 'N',
2324 'Route for globals',
2325 'ff_globals where global_id = &U1',
2326 p_created_by,
2327 p_creation_date
2328 );
2329 -- set error tracking information
2330 hr_utility.set_location('ffdict.create_global_dbitem',4);
2331 --
2332 insert into ff_route_parameters
2333 (route_parameter_id,
2334 route_id,
2335 data_type,
2336 parameter_name,
2337 sequence_no
2338 )
2339 values
2340 (ff_route_parameters_s.nextval,
2341 ff_routes_s.currval,
2342 'T',
2343 'GLOBAL_ID',
2344 1
2345 );
2346 -- set error tracking information
2347 hr_utility.set_location('ffdict.create_global_dbitem',5);
2348 --
2349 select ff_routes_s.currval, ff_route_parameters_s.currval
2350 into l_route_id, l_route_parameter_id
2351 from sys.dual;
2355 --
2352 else
2353 -- set error tracking information
2354 hr_utility.set_location('ffdict.create_global_dbitem',6);
2356 -- Route exists so select parameter id (should only be one)
2357 select route_parameter_id
2358 into l_route_parameter_id
2359 from ff_route_parameters
2360 where route_id = l_route_id;
2361 end if;
2362 --
2363 -- set error tracking information
2364 hr_utility.set_location('ffdict.create_global_dbitem',7);
2365 --
2366 -- Create user entity for this global
2367 insert into ff_user_entities
2368 (user_entity_id,
2369 business_group_id,
2370 legislation_code,
2371 route_id,
2372 notfound_allowed_flag,
2373 user_entity_name,
2374 creator_id,
2375 creator_type,
2376 entity_description,
2377 created_by,
2378 creation_date
2379 )
2380 values
2381 (ff_user_entities_s.nextval,
2382 p_business_group_id,
2383 p_legislation_code,
2384 l_route_id,
2385 'N',
2386 p_name||'_GLOBAL_UE',
2387 p_global_id,
2388 'S',
2389 'User entity for global '||p_name,
2390 p_created_by,
2391 p_creation_date
2392 );
2393 -- set error tracking information
2394 hr_utility.set_location('ffdict.create_global_dbitem',8);
2395 --
2396 -- insert parameter value for this user entity. Don't forget to add quotes
2397 -- for parameter value (global name)
2398 insert into ff_route_parameter_values
2399 (route_parameter_id,
2400 user_entity_id,
2401 value,
2402 created_by,
2403 creation_date
2404 )
2405 values
2406 (l_route_parameter_id,
2407 ff_user_entities_s.currval,
2408 p_global_id,
2409 p_created_by,
2410 p_creation_date
2411 );
2412 -- set error tracking information
2413 hr_utility.set_location('ffdict.create_global_dbitem',9);
2414 --
2415 insert into ff_database_items
2416 (user_name,
2417 user_entity_id,
2418 data_type,
2419 definition_text,
2420 null_allowed_flag,
2421 description,
2422 created_by,
2423 creation_date
2424 )
2425 values
2426 (p_name,
2427 ff_user_entities_s.currval,
2428 p_data_type,
2429 decode(p_data_type,'D',
2430 'FFFUNC.CD(DECODE(DATA_TYPE,''D'',GLOBAL_VALUE,NULL))',
2431 'N',
2432 'FFFUNC.CN(DECODE(DATA_TYPE,''N'',GLOBAL_VALUE,NULL))',
2433 'GLOBAL_VALUE'),
2434 'N',
2435 'Database Item for '||p_name,
2436 p_created_by,
2437 p_creation_date
2438 );
2439 exception
2440 when item_present then
2441 null;
2442 end create_global_dbitem;
2443 --
2444 ---------------------------- delete_global_dbitem -----------------------------
2445 --
2446 -- NAME
2447 -- delete_global_dbitem
2448 -- DESCRIPTION
2449 -- Does third party deletes to remove a database item which is used within
2450 -- formulae to access the global variable value
2451 --
2452 -------------------------------------------------------------------------------
2453 --
2454 procedure delete_global_dbitem(p_global_id in number) is
2455 begin
2456 -- set error tracking information
2457 hr_utility.set_location('ffdict.delete_global_dbitem',1);
2458 --
2459 -- Delete user entity created for this global with a cascade delete of
2460 -- child records in ff_database_items and ff_route_parameter_values
2461 --
2462 delete from ff_user_entities
2463 where creator_id = p_global_id
2464 and creator_type = 'S';
2465 end delete_global_dbitem;
2466 --
2467 ----------------------------- delete_ftcu_check ------------------------------
2468 --
2469 -- NAME
2470 -- delete_ftcu_check
2471 -- DESCRIPTION
2472 -- Check deleting formula type context usage does not make any compiled
2473 -- formulae invalid. Returns TRUE if OK, FALSE if not OK
2474 --
2475 ------------------------------------------------------------------------------
2476 --
2477 procedure delete_ftcu_check(p_ftype_id in number,
2478 p_context_id in number) is
2479 dummy varchar2(1);
2480 begin
2481 -- set error tracking information
2482 hr_utility.set_location('ffdict.delete_ftcu_check',1);
2483 --
2484 -- Look for any compiled formulas belonging to the formula type passed
2485 -- which use the context
2486 select 'X' into dummy from dual
2487 where exists
2488 (
2489 select null from ff_formulas_f a,
2490 ff_fdi_usages_f b,
2491 ff_contexts c
2492 where a.formula_type_id = p_ftype_id
2493 and a.formula_id = b.formula_id
2494 and b.item_name = upper(c.context_name)
2495 and c.context_id = p_context_id
2496 and b.usage = 'U'
2497 );
2498 -- if select succeeds, then at least one compiled formula uses context
2499 -- so raise an error
2500 hr_utility.set_message (802,'FF76_WILL_INVALIDATE_FORMULA');
2501 hr_utility.raise_error;
2502
2503 exception
2504 -- route items not used, so succeed
2505 when no_data_found then
2506 null;
2507 end delete_ftcu_check;
2508 --
2509 ---------------------------- delete_dbitem_check -----------------------------
2510 --
2511 -- NAME
2512 -- delete_dbitem_check
2513 -- DESCRIPTION
2514 -- Procedure which succeeds if it is OK to delete named DB item.
2515 -- Overloaded because sometimes business group and legislation are known
2516 --
2517 ------------------------------------------------------------------------------
2518 --
2519 procedure delete_dbitem_check(p_item_name in varchar2,
2523 -- set error tracking information
2520 p_business_group_id in number,
2521 p_legislation_code in varchar2) is
2522 begin
2524 hr_utility.set_location('ffdict.delete_dbitem_check(2)',1);
2525 --
2526 -- if database item used in a formula, fail
2527 if (ffdict.is_used_in_formula(p_item_name, p_business_group_id,
2528 p_legislation_code)) then
2529 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
2530 hr_utility.set_message_token('1',p_item_name);
2531 hr_utility.raise_error;
2532 end if;
2533 end delete_dbitem_check;
2534 --
2535 ---------------------------- delete_dbitem_check -----------------------------
2536 --
2537 -- NAME
2538 -- delete_dbitem_check
2539 -- DESCRIPTION
2540 -- Procedure which succeeds if it is OK to delete named DB item.
2541 --
2542 ------------------------------------------------------------------------------
2543 --
2544 procedure delete_dbitem_check(p_item_name in varchar2,
2545 p_user_entity_id in number) is
2546 begin
2547 -- set error tracking information
2548 hr_utility.set_location('ffdict.delete_dbitem_check',1);
2549
2550 -- if database item used in a formula, fail
2551 if ffdict.dbi_used_in_formula(p_item_name, p_user_entity_id) then
2552 hr_utility.set_message(802,'FF75_ITEM_USED_IN_FORMULA');
2553 hr_utility.set_message_token('1',p_item_name);
2554 hr_utility.raise_error;
2555 end if;
2556 end delete_dbitem_check;
2557 --
2558 ------------------------------- set_ue_details --------------------------------
2559 --
2560 -- NAME
2561 -- set_ue_details
2562 -- DESCRIPTION
2563 -- Stores details of UE pending a delete (for use by delete_dbitem_check)
2564 --
2565 -------------------------------------------------------------------------------
2566 --
2567 procedure set_ue_details (user_entity_id in number,
2568 business_group_id in number,
2569 legislation_code in varchar2) is
2570 begin
2571 -- set error tracking information
2572 hr_utility.set_location('ffdict.set_ue_details',1);
2573 --
2574 tmp_ue_id := user_entity_id;
2575 tmp_bg_id := business_group_id;
2576 tmp_leg_code := legislation_code;
2577 end set_ue_details;
2578 --
2579 ------------------------------ clear_ue_details -------------------------------
2580 --
2581 -- NAME
2582 -- clear_ue_details
2583 -- DESCRIPTION
2584 -- Clears details of UE following a delete
2585 --
2586 -------------------------------------------------------------------------------
2587 --
2588 procedure clear_ue_details is
2589 begin
2590 -- set error tracking information
2591 hr_utility.set_location('ffdict.clear_ue_details',1);
2592 --
2593 tmp_ue_id := null;
2594 tmp_bg_id := null;
2595 tmp_leg_code := null;
2596 end clear_ue_details;
2597 --
2598 ---------------------------- update_global_dbitem -----------------------------
2599 --
2600 procedure update_global_dbitem(p_global_id in number,
2601 p_new_name in varchar2,
2602 p_description in varchar2,
2603 p_source_lang in varchar2,
2604 p_language in varchar2) is
2605 l_dbi_tl_name varchar2(2000);
2606 begin
2607 if g_glb_id is null or g_glb_id <> p_global_id then
2608 set_global_dbi_details(global_id => p_global_id);
2609 if g_glb_id is null or g_glb_id <> p_global_id then
2610 --
2611 -- For some reason, the database item does not exist.
2612 --
2613 return;
2614 end if;
2615 end if;
2616
2617 --
2618 -- Convert the global name to a dbi name.
2619 --
2620 ff_database_items_pkg.update_tl_row
2621 (x_user_name => g_glb_dbi
2622 ,x_user_entity_id => g_glb_ueid
2623 ,x_language => p_language
2624 ,x_source_lang => p_source_lang
2625 ,x_translated_user_name => p_new_name
2626 ,x_description => p_description
2627 );
2628 end update_global_dbitem;
2629 --
2630 end ffdict;
[Home] [Help]