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