1 package body ff_exec as
2 /* $Header: ffexec.pkb 120.4.12000000.1 2007/01/17 17:44:40 appldev noship $ */
3 /*
4 NOTES
5 See ffrunf.lld for design documentation.
6 */
7 /*---------------------------------------------------------------------------*/
8 /*----------------------- constant definitions ------------------------------*/
9 /*---------------------------------------------------------------------------*/
10 /*
11 * Values that formula indicator variables can hold.
12 * The FF_WAS_DEFAULTED is to indicate when a database
13 * item was defaulted.
14 */
15 FF_NULL constant binary_integer := 0;
16 FF_NOT_NULL constant binary_integer := -1;
17 FF_WAS_DEFAULTED constant binary_integer := -2;
18
19 /*
20 * Cache size limits and default. No maximum specified, as
21 * should let the user find that!
22 */
23 FF_DFLT_CACHE_SIZE constant binary_integer := 300;
24 FF_MIN_CACHE_SIZE constant binary_integer := 5;
25 FF_MAX_CACHE_SIZE constant binary_integer := 5120;
26
27 /* increment value for database item cache values */
28 DBI_CACHE_INCR constant binary_integer := 64;
29
30 /* Invalid position in parameter table. */
31 INVALID_POS constant binary_integer := -1;
32
33 /* Action parameter to control formula execution mode. */
34 USE_FF_WRAPPER constant varchar2(128) := 'FF_USE_PLSQL_WRAPPER';
35
36 /* Action parameter to control formula cache size. */
37 FF_FMLA_CACHE_SIZE constant varchar2(128) := 'FF_PLSQL_CACHE_SIZE';
38
39 /* Number of bits in a context level mask. */
40 C_MASK_BITS constant binary_integer := 31;
41
42 /* Maximum number of active contexts in the cache. */
43 C_MAX_CONTEXTS constant binary_integer := 62;
44
45 /* Data types. */
46 C_DATE constant varchar2(10) := 'DATE';
47 C_NUMBER constant varchar2(10) := 'NUMBER';
48 C_TEXT constant varchar2(10) := 'TEXT';
49
50 --
51 -- Exception raised when the wrapper package is invalid or does not exist.
52 --
53 CANNOT_FIND_PROG_UNIT exception;
54 pragma exception_init(CANNOT_FIND_PROG_UNIT, -6508);
55 --
56 -- Exception raised when attempting to execute a formula compiled with an
57 -- out-of-date formula compiler (missing FORMULA interfaces).
58 --
59 BAD_PLSQL exception;
60 pragma exception_init(BAD_PLSQL, -6550);
61
62 /*---------------------------------------------------------------------------*/
63 /*------------- internal execution engine data structures -------------------*/
64 /*---------------------------------------------------------------------------*/
65 type t_binary_integer is table of binary_integer index by binary_integer;
66 type t_small_varchar2 is table of varchar2(10) index by binary_integer;
67 type t_big_varchar2 is table of varchar2(300) index by binary_integer;
68
69 /*
70 * This structure holds state information
71 * about the execution engine.
72 */
73 type exec_info_r is record
74 (
75 formula_id binary_integer, -- currently executing formula.
76 cache_slot binary_integer, -- cache slot in use.
77 effective_date date, -- effective date used for formula execution.
78
79 fmla_cache_size number, -- size of the formula cache.
80
81 /* following allow sanity checking */
82 input_rows number, -- rows in the inputs table.
83 output_rows number -- rows in the outputs table.
84
85 /* Use FF_WRAPPER_PKG instead of dynamic SQL. */
86 ,use_ff_wrapper boolean
87 );
88
89 --
90 -- Lookup type for mapping formula_id -> collection slot.
91 --
92 type fmla_lookup_t is table of binary_integer index by binary_integer;
93
94 /*-------------- internal execution cache data structures -------------------*/
95 /*
96 * The Fast Formula cache types.
97 * The cache consists of the following things:
98 * 1) A table of records that is essentially a combination of
99 * info from ff_formulas_f and ff_compiled_info_f.
100 * The formula cache size is limited, and is controlled by
101 * an internal variable. It defaults to FF_DFLT_CACHE_SIZE.
102 * In addition, a table of records that holds information derived
103 * from the ff_fdi_usages_f table.
104 * NOTE - performula, the in core fdiu is held, ordered by
105 * a combination of class and item name. i.e.
106 * U (Contexts)
107 * I (inputs), O (outputs), B (both),
108 * D (Database Items)
109 * Within this, the individual entries are ordered by item name.
110 * The reason for this is:
111 * a) Contexts must come before database items (to allow db item
112 * invalidation to work correctly).
113 * b) Make the ordering of the entries in the inputs and outputs
114 * tables predictable to the user, which may be of some benefit.
115 * 2) If the cache is full, then the cache behaves as a circular list.
116 * Formulas are evicted from the cache starting from the first cache
117 * entry and cycling around as necessary. The sticky flag is no
118 * longer used.
119 */
120 type fmla_info_r is record /* Formula info */
121 (
122 formula_id binary_integer,
123 effective_start_date date,
124 effective_end_date date,
125 formula_name varchar2(80),
126 package_name varchar2(80), -- pack.proc we execute.
127 first_fdiu binary_integer, -- first entry in fdiu for formula.
128 fdiu_count binary_integer, -- number of fdiu rows.
129 context_count binary_integer, -- how many contexts?
130 input_count binary_integer, -- how many inputs?
131 output_count binary_integer -- how many outputs?
132 );
133
134 --
135 -- The formula cache. Note: the size must match FF_MAX_CACHE_SIZE.
136 --
137 type fmla_info_t is varray(5120) of fmla_info_r;
138
139 type fdiu_info_r is record /* in core fdiu */
140 (
141 name varchar2(240), -- the 'real' name of the item.
142 varpos binary_integer, -- Position in variable table.
143 indpos binary_integer, -- Position in indicator table.
144 data_type varchar2(6), -- valid C_DATE, C_TEXT, C_NUMBER.
145 usage varchar2(1), -- 'I', 'O', 'B', 'U', 'D'.
146 context_sum1 binary_integer, -- for database items and contexts.
147 context_sum2 binary_integer, -- for database items and contexts.
148 context_id binary_integer, -- for database items and contexts.
149 route_id binary_integer -- for database items
150 );
151
152 type fdiu_info_t is table of fdiu_info_r index by binary_integer;
153
154 type free_chunk_t is table of binary_integer index by binary_integer;
155
156 /*
157 * The database item cache types.
158 * The database item cache consists of three tables that holds details
159 * about contexts and database items as appropriate. The details of
160 * these are:
161 * o A record and table type to hold the details of the contexts
162 * and database items in the cache. A separate table is used
163 * for contexts and database items. In the case of contexts,
164 * it is indexed directly by the context_id.
165 * o Since FF_DATABASE_ITEMS does not have a surrogate primary key,
166 * the formula compiler generates a hashed value, which is not
167 * guaranteed to be unique. Therefore, the database item cache
168 * is not indexed directly. Instead, there is a 'hash' data
169 * structure that points to the first possible entry in the
170 * database item cache table and the number of entries for this
171 * hash value. This allows 'row chaining' to be implemented.
172 * NOTES:
173 * The context_level field is either the context_level of the
174 * context or sum of context dependencies for database items.
175 * o The value field stores the current value for that item.
176 * If it is NULL, it indicates that the value is currently
177 * invalid. In the case of a database item, it means that we
178 * will need to fetch the value from the database.
179 */
180 type dbi_hash_r is record
181 (
182 first_entry binary_integer,
183 chain_count binary_integer
184 );
185
186 type dbi_hash_t is table of dbi_hash_r index by binary_integer;
187
188 type dbi_cache_r is record /* dbi and context info */
189 (
190 item_name ff_database_items.user_name%type,
191 context_level1 binary_integer,
192 context_level2 binary_integer,
193 data_type varchar2(10),
194 dvalue date,
195 nvalue number,
196 tvalue varchar2(255), -- matches ff_exec.FF_BIND_LEN
197 indicator binary_integer
198 );
199
200 type dbi_cache_t is table of dbi_cache_r index by binary_integer;
201 type ctx_cache_t is table of dbi_cache_r index by binary_integer;
202
203 /*
204 * Data structures for holding dynamic context level sums for routes.
205 */
206 type context_sums_r is record
207 (
208 context_sum1 binary_integer,
209 context_sum2 binary_integer
210 );
211
212 type route_ctx_sums_t is table of context_sums_r index by binary_integer;
213
214 /*---------------------------------------------------------------------------*/
215 /*----------------------- execution engine globals --------------------------*/
216 /*---------------------------------------------------------------------------*/
217 g_decpoint varchar2(100); -- Decimal point character for number/string conversion.
218 g_exec_info exec_info_r;
219 g_inited boolean := false; -- Cache initialised.
220 g_fmla_lookups fmla_lookup_t; -- Lookup table for formula information table.
221 g_fmla_info fmla_info_t; -- formula information.
222 g_lru_slot binary_integer; -- Least-Recently-Used formula slot.
223 g_fdiu_info fdiu_info_t; -- in core fdiu information.
224 g_free_fdiu free_chunk_t; -- Table of free FDIU chunks.
225 g_hash_info dbi_hash_t; -- Hash table for database item cache entries.
226 g_dbi_info dbi_cache_t; -- database item cache entries.
227 g_ctx_info dbi_cache_t; -- context entries.
228
229 --
230 -- g_ctx_levels1 and g_ctx_levels2 are allocated context levels.
231 -- g_route_ctx_sums caches the context levels for each route.
232 --
233 g_route_ctx_sums route_ctx_sums_t;
234 g_ctx_levels1 binary_integer := 0;
235 g_ctx_levels2 binary_integer := 0;
236
237 -- The next value for entry in database item cache table.
238 g_next_dbi_index binary_integer := 1;
239 /*---------------------------------------------------------------------------*/
240 /*------------------ local functions and procedures -------------------------*/
241 /*---------------------------------------------------------------------------*/
242
243 procedure set_use_ff_wrapper is
244 l_pap_found boolean;
245 l_pap_value varchar2(2000);
246 begin
247 --
248 -- Look for configuration information in PAY_ACTION_PARAMETERS.
249 --
250 pay_core_utils.get_action_parameter
251 (p_para_name => USE_FF_WRAPPER
252 ,p_para_value => l_pap_value
253 ,p_found => l_pap_found
254 );
255 --
256 g_exec_info.use_ff_wrapper := not l_pap_found or upper(l_pap_value) <> 'N';
257 end set_use_ff_wrapper;
258
259 procedure set_cache_size is
260 l_pap_found boolean;
261 l_pap_value varchar2(2000);
262 begin
263 --
264 -- Look for configuration information in PAY_ACTION_PARAMETERS.
265 --
266 begin
267 pay_core_utils.get_action_parameter
268 (p_para_name => FF_FMLA_CACHE_SIZE
269 ,p_para_value => l_pap_value
270 ,p_found => l_pap_found
271 );
272 if l_pap_found then
273 g_exec_info.fmla_cache_size := trunc(to_number(l_pap_value));
274 else
275 g_exec_info.fmla_cache_size := null;
276 end if;
277 exception
278 when others then
279 g_exec_info.fmla_cache_size := null;
280 end;
281
282 if g_exec_info.fmla_cache_size is null then
283 g_exec_info.fmla_cache_size := FF_DFLT_CACHE_SIZE;
284 elsif g_exec_info.fmla_cache_size < FF_MIN_CACHE_SIZE then
285 g_exec_info.fmla_cache_size := FF_MIN_CACHE_SIZE;
286 elsif g_exec_info.fmla_cache_size > FF_MAX_CACHE_SIZE then
287 g_exec_info.fmla_cache_size := FF_MAX_CACHE_SIZE;
288 end if;
289 end set_cache_size;
290
291 /*
292 * Checks if the FF_DEBUG profile has been set, and if so,
293 * whether any of the PLSQL execution engine debug settings
294 * should be enabled.
295 */
296 procedure check_profile_debug is
297 l_value varchar2(300);
298 l_exec_debug boolean := FALSE;
299 l_routing boolean := FALSE;
300 l_ff_debug boolean := FALSE;
301 l_ff_cache boolean := FALSE;
302 l_dbi_cache boolean := FALSE;
303 l_mru boolean := FALSE;
304 l_io boolean := FALSE;
305 l_char varchar2(1);
306 l_debug_level binary_integer := 0;
307 begin
308 -- Look for setting of FF_DEBUG profile.
309 fnd_profile.get('FF_DEBUG', l_value);
310
311 if(l_value is null) then
312 return;
313 end if;
314
315 -- Debug setting is possible, process which ones.
316 for l_pos in 1..length(l_value) loop
317 l_char := substr(l_value, l_pos, 1); -- get a single character.
318
319 if(l_char = 'X' or l_char = 'x') then
320 l_exec_debug := TRUE;
321 elsif(l_char = 'R' or l_char = 'r') then
322 l_routing := TRUE;
323 elsif(l_char = 'F' or l_char = 'f') then
324 l_ff_debug := TRUE;
325 elsif(l_char = 'C' or l_char = 'c') then
326 l_ff_cache := TRUE;
327 elsif(l_char = 'D' or l_char = 'd') then
328 l_dbi_cache := TRUE;
329 elsif(l_char = 'M' or l_char = 'm') then
330 l_mru := TRUE;
331 elsif(l_char = 'I' or l_char = 'i') then
332 l_io := TRUE;
333 else
334 null; -- ignore spurious characters.
335 end if;
336
337 end loop;
338
339 -- Need to have general execution engine logging set
340 -- before we can use the other options.
341 if(l_exec_debug) then
342 -- Set the appropriate flags.
343 if(l_routing) then
344 l_debug_level := l_debug_level + ff_utils.ROUTING;
345 end if;
346
347 if(l_ff_debug) then
348 l_debug_level := l_debug_level + ff_exec.FF_DBG;
349 end if;
350
351 if(l_ff_cache) then
352 l_debug_level := l_debug_level + ff_exec.FF_CACHE_DBG;
353 end if;
354
355 if(l_dbi_cache) then
356 l_debug_level := l_debug_level + ff_exec.DBI_CACHE_DBG;
357 end if;
358
359 if(l_mru) then
360 l_debug_level := l_debug_level + ff_exec.MRU_DBG;
361 end if;
362
363 if(l_io) then
364 l_debug_level := l_debug_level + ff_exec.IO_TABLE_DBG;
365 end if;
366
367 -- Set the global flag.
368 ff_utils.g_debug_level := l_debug_level;
369
370 end if;
371
372 end check_profile_debug;
373
374 /*
375 * Debug proceure to output information about formula
376 * cache contents for a specific formula.
377 * This procedure is overloaded.
378 */
379 procedure fmla_cache_debug
380 (
381 fid in binary_integer, -- formula_id
382 p_fmla_lookups in fmla_lookup_t,
383 p_fmla_info in fmla_info_t,
384 p_fdiu_info in fdiu_info_t
385 ) is
386
387 name varchar2(240);
388 varpos varchar2(100);
389 indpos varchar2(100);
390 data_type varchar2(10);
391 usage varchar2(10);
392 class varchar2(10);
393 context_sum1 varchar2(30);
394 context_sum2 varchar2(30);
395 context_id varchar2(10);
396 first binary_integer;
397 last binary_integer;
398 i binary_integer;
399 fmla_info fmla_info_r;
400 fdiu_info fdiu_info_r;
401
402 begin
403 -- Check the debug flag settting.
404 if(ff_utils.g_debug_level is null or ff_utils.g_debug_level = 0) then
405 return;
406 end if;
407
408 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
409 -- Debug level set correctly, output debug information for
410 -- specific formula.
411
412 hr_utility.trace('');
413 hr_utility.trace('FMLA CACHE info for formula_id ' || fid);
414 hr_utility.trace('--------------------------------------');
415 fmla_info := p_fmla_info(p_fmla_lookups(fid));
416 hr_utility.trace('Eff Start : ' ||
417 to_char(fmla_info.effective_start_date, 'DD-MON-YYYY'));
418 hr_utility.trace('Eff End : ' ||
419 to_char(fmla_info.effective_end_date, 'DD-MON-YYYY'));
420 hr_utility.trace('Fmla Name : ' || fmla_info.formula_name);
421 hr_utility.trace('Package : ' || fmla_info.package_name);
422 hr_utility.trace('First FDIU : ' || fmla_info.first_fdiu);
423 hr_utility.trace('FDIU Count : ' || fmla_info.fdiu_count);
424 hr_utility.trace('Ctx count : ' || fmla_info.context_count);
425 hr_utility.trace('In count : ' || fmla_info.input_count);
426 hr_utility.trace('Out count : ' || fmla_info.output_count);
427
428 -- Output information about the fdiu rows.
429 first := fmla_info.first_fdiu;
430 last := first + fmla_info.fdiu_count - 1;
431
432 hr_utility.trace('');
433 hr_utility.trace('FDIU ROWS');
434 hr_utility.trace('[FDIU]Item Name V[POS] I[POS] ' ||
435 'Dtype U CSum1 CSum2 ContextId');
436 hr_utility.trace('------------------------- ---------- ---------- ' ||
437 '------ - --------- --------- ----------');
438
439 for i in first..last loop
440 fdiu_info := p_fdiu_info(i);
441
442 /* build up strings first, for clarity */
443 name := rpad(fdiu_info.name, 25) || ' ';
444 varpos := rpad(fdiu_info.varpos, 10) || ' ';
445 indpos := rpad(fdiu_info.indpos, 10) || ' ';
446 data_type := rpad(fdiu_info.data_type, 6 ) || ' ';
447 usage := rpad(fdiu_info.usage, 1 ) || ' ';
448 context_sum1 := lpad(fdiu_info.context_sum1, 9 ) || ' ';
449 context_sum2 := lpad(fdiu_info.context_sum2, 9 ) || ' ';
450 context_id := lpad(fdiu_info.context_id, 10);
451
452 hr_utility.trace(name || varpos || indpos || data_type || usage ||
453 class || context_sum1 || context_sum2 ||
454 context_id);
455
456 end loop;
457
458 end if;
459 end fmla_cache_debug;
460
461 /*
462 * Debug procedure for db item cache.
463 */
464 procedure dbi_cache_debug
465 (
466 p_ctx_info in dbi_cache_t,
467 p_dbi_info in dbi_cache_t,
468 p_hash_info in dbi_hash_t
469 ) is
470 l_index varchar2(20);
471 item_name varchar2(240);
472 context_level1 varchar2(30);
473 context_level2 varchar2(30);
474 value varchar2(255);
475 indicator number;
476 first_entry varchar2(80);
477 chain_count varchar2(80);
478 i binary_integer;
479
480 begin
481 -- Check the debug flag settting.
482 if(ff_utils.g_debug_level is null or ff_utils.g_debug_level = 0) then
483 return;
484 end if;
485
486 if(bitand(ff_utils.g_debug_level, ff_exec.DBI_CACHE_DBG) <> 0) then
487 -- Debug level set correctly, output debug information for
488 -- database item cache.
489 hr_utility.trace('');
490 hr_utility.trace('[DBI]Indx DbItem/Context Name Ctx Level1 Ctx Level2' ||
491 ' Value');
492 hr_utility.trace('--------- ------------------- ----------- ----------- ' ||
493 '-----------------------------');
494
495 /*
496 * Information about the contexts.
497 */
498 i := p_ctx_info.first;
499
500 while(i is not null) loop
501
502 value := '<NULL>';
503 if p_ctx_info(i).data_type = C_DATE then
504 if p_ctx_info(i).dvalue is not null then
505 value := fnd_date.date_to_canonical( p_ctx_info(i).dvalue );
506 end if;
507 elsif p_ctx_info(i).data_type = C_NUMBER then
508 if p_ctx_info(i).nvalue is not null then
509 value := replace(to_char(p_ctx_info(i).nvalue), g_decpoint, '.');
510 end if;
511 else
512 if p_ctx_info(i).tvalue is not null then
513 value := p_ctx_info(i).tvalue;
514 end if;
515 end if;
516
517 -- Set up variables first, for clarity.
518 l_index := lpad(i, 9) || ' ';
519 item_name := rpad(p_ctx_info(i).item_name, 20) || ' ';
520 context_level1 := lpad(p_ctx_info(i).context_level1, 11) || ' ';
521 context_level2 := lpad(p_ctx_info(i).context_level2, 11) || ' ';
522
523 -- Now output the string.
524 hr_utility.trace(l_index || item_name || context_level1 || context_level2 ||
525 value);
526
527 i := p_ctx_info.next(i);
528
529 end loop;
530
531 /*
532 * Information about the database items.
533 */
534 i := p_dbi_info.first;
535
536 while(i is not null) loop
537
538 value := null;
539 if p_dbi_info(i).indicator = FF_NOT_NULL then
540 if p_dbi_info(i).data_type = C_DATE then
541 value := fnd_date.date_to_canonical( p_dbi_info(i).dvalue );
542 elsif p_dbi_info(i).data_type = C_NUMBER then
543 value := replace(to_char(p_dbi_info(i).nvalue), g_decpoint, '.');
544 else
545 value := p_dbi_info(i).tvalue;
546 end if;
547 end if;
548 value := nvl(value, '<NULL>');
549 value := value || ' ';
550
551 -- Set up variables first, for clarity.
552 l_index := lpad(i, 9) || ' ';
553 item_name := rpad(p_dbi_info(i).item_name, 20) || ' ';
554 context_level1 := lpad(p_dbi_info(i).context_level1, 11) || ' ';
555 context_level2 := lpad(p_dbi_info(i).context_level2, 11) || ' ';
556 indicator := p_dbi_info(i).indicator;
557
558 -- Now output the string.
559 hr_utility.trace(l_index || item_name || context_level1 || context_level2 ||
560 value || indicator);
561
562 i := p_dbi_info.next(i);
563
564 end loop;
565
566 /*
567 * Information about db item hash table.
568 */
569
570 hr_utility.trace('[HSH]Indx First Count');
571 hr_utility.trace('--------- --------- ---------');
572
573 i := p_hash_info.first;
574
575 while(i is not null) loop
576
577 -- Set up variables first, for clarity.
578 l_index := lpad(i, 9) || ' ';
579 first_entry := lpad(p_hash_info(i).first_entry, 9) || ' ';
580 chain_count := lpad(p_hash_info(i).chain_count, 9);
581
582 -- Now output the string.
583 hr_utility.trace(l_index || first_entry || chain_count);
584
585 i := p_hash_info.next(i);
586
587 end loop;
588
589 end if;
590
591 end dbi_cache_debug;
592
593 /*
594 * Output some info about the invalidation of database
595 * items in the cache.
596 */
597 procedure dbi_invalid_debug
598 (
599 p_dbi_info in dbi_cache_r,
600 p_context_sum1 in binary_integer,
601 p_context_sum2 in binary_integer
602 ) is
603 item_name varchar2(240);
604 context_level1 varchar2(30);
605 context_sum1 varchar2(30);
606 context_level2 varchar2(30);
607 context_sum2 varchar2(30);
608 value varchar2(255);
609 begin
610 -- Check the debug flag settting.
611 if(ff_utils.g_debug_level is null or ff_utils.g_debug_level = 0) then
612 return;
613 end if;
614
615 if(bitand(ff_utils.g_debug_level, ff_exec.DBI_CACHE_DBG) <> 0) then
616
617 item_name := rpad(p_dbi_info.item_name, 20) || ' ';
618 context_level1 := lpad(p_dbi_info.context_level1,10) || ' ';
619 context_sum1 := lpad(p_context_sum1, 10) || ' ';
620 context_level2 := lpad(p_dbi_info.context_level2,10) || ' ';
621 context_sum2 := lpad(p_context_sum2, 10) || ' ';
622
623
624 value := null;
625 if p_dbi_info.indicator = FF_NOT_NULL then
626 if p_dbi_info.data_type = C_DATE then
627 value := fnd_date.date_to_canonical( p_dbi_info.dvalue );
628 elsif p_dbi_info.data_type = C_NUMBER then
629 value := replace(to_char(p_dbi_info.nvalue), g_decpoint, '.');
630 else
631 value := p_dbi_info.tvalue;
632 end if;
633 end if;
634 value := nvl(value, '<NULL>');
635
636 hr_utility.trace('INVAL: ' || item_name || context_level1 ||
637 context_level2 || context_sum1 || context_sum2 ||
638 value);
639
640 end if;
641
642 end dbi_invalid_debug;
643
644 /*
645 * Log information about a changed context.
646 */
647 procedure ctx_change_debug
648 (
649 p_item_name in varchar2,
650 p_context_level1 in binary_integer,
651 p_context_level2 in binary_integer,
652 p_old_value in varchar2,
653 p_new_value in varchar2
654 ) is
655 item_name varchar2(240);
656 context_level1 varchar2(30);
657 context_level2 varchar2(30);
658 old_value varchar2(255);
659 new_value varchar2(255);
660 begin
661 -- Check the debug flag settting.
662 if(ff_utils.g_debug_level is null or ff_utils.g_debug_level = 0) then
663 return;
664 end if;
665
666 if(bitand(ff_utils.g_debug_level, ff_exec.DBI_CACHE_DBG) <> 0) then
667
668 item_name := rpad(p_item_name, 20) || ' ';
669 context_level1 := lpad(p_context_level1, 10) || ' ';
670 context_level2 := lpad(p_context_level2, 10) || ' ';
671 old_value := nvl(p_old_value, '<NULL>') || ' ';
672 new_value := nvl(p_new_value, '<NULL>');
673
674 hr_utility.trace('CTXCH: ' || item_name || context_level1 ||
675 context_level2 || old_value || new_value);
676 end if;
677
678 end ctx_change_debug;
679
680 /*
681 * Input and Output table information.
682 */
683 procedure io_table_debug
684 (
685 p_inputs in ff_exec.inputs_t,
686 p_outputs in ff_exec.outputs_t,
687 p_type in varchar2
688 ) is
689 l_index varchar2(20);
690 name varchar2(240);
691 data_type varchar2(20);
692 class varchar2(20);
693 value varchar2(255);
694 i binary_integer;
695
696 begin
697 -- Check the debug flag settting.
698 if(ff_utils.g_debug_level is null or ff_utils.g_debug_level = 0) then
699 return;
700 end if;
701
702 if(bitand(ff_utils.g_debug_level, ff_exec.IO_TABLE_DBG) <> 0) then
703 -- Debug level set correctly, output debug information for
704 -- input table information.
705
706 if(p_type = 'INPUT') then
707
708 /*
709 * Inputs table information.
710 */
711
712 hr_utility.trace('');
713 hr_utility.trace('[IT]Index Input/Context Name ' ||
714 'Dtype Class Value');
715 hr_utility.trace('--------- ------------------------------ ' ||
716 '------- ------- -----------------------');
717
718 i := p_inputs.first;
719
720 while(i is not null) loop
721
722 -- Set up variables first, for clarity.
723 l_index := lpad(i, 9) || ' ';
724 name := rpad(p_inputs(i).name, 30) || ' ';
725 data_type := rpad(p_inputs(i).datatype, 7) || ' ';
726 class := rpad(p_inputs(i).class, 7) || ' ';
727 value := nvl(p_inputs(i).value, '<NULL>');
728
729 -- Now output the string.
730 hr_utility.trace(l_index || name || data_type || class || value);
731
732 i := p_inputs.next(i);
733
734 end loop;
735
736 elsif(p_type = 'OUTPUT') then
737
738 /*
739 * Outputs table information.
740 */
741
742 hr_utility.trace('');
743 hr_utility.trace('[OT]Index Input/Context Name ' ||
744 'Dtype Value');
745 hr_utility.trace('--------- ------------------------------ ' ||
746 '------- -------------------------------');
747
748 i := p_outputs.first;
749
750 while(i is not null) loop
751
752 -- Set up variables first, for clarity.
753 l_index := lpad(i, 9) || ' ';
754 name := rpad(p_outputs(i).name, 30) || ' ';
755 data_type := rpad(p_outputs(i).datatype, 7) || ' ';
756 value := nvl(p_outputs(i).value, '<NULL>');
757
758 -- Now output the string.
759 hr_utility.trace(l_index || name || data_type || value);
760
761 i := p_outputs.next(i);
762
763 end loop;
764
765 end if;
766
767 end if;
768
769 end io_table_debug;
770
771 ------------------------------- find_free_chunk -------------------------------
772 /*
773 NAME
774 find_free_chunk
775 DESCRIPTION
776 Finds a free chunk to reuse and updates the free chunk list
777 accordingly.
778 */
779 procedure find_free_chunk
780 (p_chunk_size in number
781 ,p_free_chunks in out nocopy free_chunk_t
782 ,p_start out nocopy number
783 ) is
784 l_first_free binary_integer;
785 l_candidate binary_integer := null;
786 i binary_integer;
787 begin
788
789 --
790 -- Dump out the free chunk list.
791 --
792 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
793 hr_utility.trace('<- Free Chunk List ->');
794 i := p_free_chunks.first;
795 loop
796 exit when not p_free_chunks.exists(i);
797
798 hr_utility.trace('(' || i || ',' || p_free_chunks(i) || ')');
799
800 i := p_free_chunks.next(i);
801 end loop;
802 end if;
803
804 p_start := null;
805
806 if p_chunk_size = 0 then
807 return;
808 end if;
809
810 l_first_free := p_free_chunks.first;
811 loop
812 exit when not p_free_chunks.exists(l_first_free);
813
814 --
815 -- Got a suitable candidate free chunk. It should be
816 -- as small as possible.
817 --
818 if p_free_chunks(l_first_free) >= p_chunk_size then
819 if l_candidate is null or
820 p_free_chunks(l_first_free) < p_free_chunks(l_candidate) then
821 l_candidate := l_first_free;
822 end if;
823 end if;
824
825 l_first_free := p_free_chunks.next(l_first_free);
826 end loop;
827
828 --
829 -- Got a candidate.
830 --
831 if l_candidate is not null then
832
833 --
834 -- Adjust chunk if space is left. Take space from the end of the
835 -- chunk to avoid having to delete the record.
836 --
837 if p_free_chunks(l_candidate) > p_chunk_size then
838 p_free_chunks(l_candidate) :=
839 p_free_chunks(l_candidate) - p_chunk_size;
840
841 p_start := l_candidate + p_free_chunks(l_candidate);
842 --
843 -- The chunk is exactly p_chunk_size in length. Delete the
844 -- record.
845 --
846 else
847 p_start := l_candidate;
848 p_free_chunks.delete(l_candidate);
849 end if;
850
851 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
852 hr_utility.trace('Reuse chunk:'||l_candidate||','||p_chunk_size);
853 end if;
854
855 end if;
856 end find_free_chunk;
857
858 ------------------------------ add_to_free_list ------------------------------
859 /*
860 NAME
861 add_to_free_list
862 DESCRIPTION
863 Puts a range of rows onto the free list.
864 */
865 procedure add_to_free_list
866 (p_first_chunk in binary_integer
867 ,p_chunk_count in binary_integer
868 ,p_free_chunks in out nocopy free_chunk_t
869 ) is
870 l_first_chunk binary_integer;
871 l_assertion boolean;
872 l_next binary_integer;
873 --
874 procedure chunk_merge
875 (p_free_chunks in out nocopy free_chunk_t
876 ,p_merge_index in binary_integer
877 ,p_count1 in binary_integer
878 ,p_count2 in binary_integer
879 ,p_delete_index in binary_integer
880 ) is
881 begin
882 p_free_chunks(p_merge_index) := p_count1 + p_count2;
883
884 if p_delete_index is not null then
885 p_free_chunks.delete(p_delete_index);
886 end if;
887 end chunk_merge;
888 --
889 begin
890 if p_chunk_count = 0 then
891 return;
892 end if;
893
894 l_first_chunk := p_free_chunks.first;
895 loop
896 exit when not p_free_chunks.exists(l_first_chunk);
897
898 --
899 -- Look for overlap errors.
900 --
901 l_assertion :=
902 (p_first_chunk + p_chunk_count - 1 < l_first_chunk) or
903 (l_first_chunk + p_free_chunks(l_first_chunk) - 1 < p_first_chunk);
904
905 if not l_assertion then
906 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
907 hr_utility.trace
908 ('AddToFreeList assert failed:'||p_first_chunk||','||
909 to_char(p_chunk_count-1)|| ' overlaps :'||l_first_chunk||','||
910 to_char(p_free_chunks(l_first_chunk)-1));
911 end if;
912 end if;
913
914 ff_utils.assert
915 (p_expression => l_assertion
916 ,p_location => 'add_to_free_list:1'
917 );
918
919 --
920 -- Merge at the end of an existing chunk.
921 --
922 if p_first_chunk =
923 l_first_chunk + p_free_chunks(l_first_chunk) then
924
925 chunk_merge
926 (p_free_chunks => p_free_chunks
927 ,p_merge_index => l_first_chunk
928 ,p_count1 => p_free_chunks(l_first_chunk)
929 ,p_count2 => p_chunk_count
930 ,p_delete_index => null
931 );
932
933 --
934 -- Merged at low end, now see if it is possible to merge with
935 -- the next record to keep the list as short as possible.
936 --
937 l_next := p_free_chunks.next(l_first_chunk);
938 if p_free_chunks.exists(l_next) then
939 if p_free_chunks(l_first_chunk) + l_first_chunk = l_next then
940
941 chunk_merge
942 (p_free_chunks => p_free_chunks
943 ,p_merge_index => l_first_chunk
944 ,p_count1 => p_free_chunks(l_first_chunk)
945 ,p_count2 => p_free_chunks(l_next)
946 ,p_delete_index => l_next
947 );
948
949 end if;
950 end if;
951
952 return;
953 end if;
954
955 --
956 -- Merge at the start of an existing chunk.
957 --
958 if p_first_chunk + p_chunk_count = l_first_chunk then
959 chunk_merge
960 (p_free_chunks => p_free_chunks
961 ,p_merge_index => p_first_chunk
962 ,p_count1 => p_free_chunks(l_first_chunk)
963 ,p_count2 => p_chunk_count
964 ,p_delete_index => l_first_chunk
965 );
966 return;
967 end if;
968
969 l_first_chunk := p_free_chunks.next(l_first_chunk);
970 end loop;
971
972 --
973 -- No merge possible so create a new record.
974 --
975 p_free_chunks(p_first_chunk) := p_chunk_count;
976 end add_to_free_list;
977
978 ---------------------------- find_dbi_cache_entry -----------------------------
979 /*
980 NAME
981 find_dbi_cache_entry
982 DESCRIPTION
983 Finds the real index entry in database item cache.
984 NOTES
985 Passed the hashed context_id and returns the appropriate index
986 value for the database item we are looking for, else null.
987 */
988
989 function find_dbi_cache_entry
990 (
991 p_context_id in binary_integer,
992 p_item_name in varchar2
993 ) return binary_integer is
994 l_start binary_integer;
995 l_end binary_integer;
996 l_index binary_integer;
997 begin
998
999 ff_utils.entry('find_dbi_cache_entry');
1000
1001 if(not g_hash_info.exists(p_context_id)) then
1002 -- No entry at all for database item.
1003 ff_utils.exit('find_dbi_cache_entry');
1004 return(null);
1005 end if;
1006
1007 -- We know there is an entry, need to find out which one.
1008 l_start := g_hash_info(p_context_id).first_entry;
1009 l_end := l_start + g_hash_info(p_context_id).chain_count - 1;
1010
1011 -- Now search for the appropriate entry.
1012 for l_count in l_start..l_end loop
1013 if(g_dbi_info(l_count).item_name = p_item_name) then
1014 l_index := l_count;
1015 exit;
1016 end if;
1017 end loop;
1018
1019 ff_utils.exit('find_dbi_cache_entry');
1020
1021 return(l_index);
1022
1023 end find_dbi_cache_entry;
1024
1025 ------------------------------- read_dbi_cache --------------------------------
1026 /*
1027 NAME
1028 read_dbi_cache
1029 DESCRIPTION
1030 Reads value from database item cache.
1031 NOTES
1032 Returns value from database item cache, based on the hashed
1033 context_id and the name.
1034 */
1035
1036 procedure read_dbi_cache
1037 (
1038 p_context_id in binary_integer,
1039 p_item_name in varchar2
1040 ,p_data_type in varchar2
1041 ,p_dvalue out nocopy date
1042 ,p_nvalue out nocopy number
1043 ,p_tvalue out nocopy varchar2
1044 ,p_indicator out nocopy binary_integer
1045 ) is
1046 l_index binary_integer;
1047 begin
1048
1049 if g_debug then
1050 ff_utils.entry('read_dbi_cache');
1051 end if;
1052
1053 l_index := find_dbi_cache_entry(p_context_id, p_item_name);
1054
1055 -- Entry should exist when reading from the cache.
1056 ff_utils.assert((l_index is not null), 'read_dbi_cache:1');
1057
1058 if g_debug then
1059 ff_utils.exit('read_dbi_cache');
1060 end if;
1061
1062 -- Simply return the value from cache.
1063 if g_dbi_info(l_index).indicator = FF_NOT_NULL then
1064 if p_data_type = C_DATE then
1065 p_dvalue := g_dbi_info(l_index).dvalue;
1066 elsif p_data_type = C_NUMBER then
1067 p_nvalue := g_dbi_info(l_index).nvalue;
1068 else
1069 p_tvalue := g_dbi_info(l_index).tvalue;
1070 end if;
1071 else
1072 p_nvalue := NULL;
1073 p_dvalue := NULL;
1074 p_tvalue := NULL;
1075 end if;
1076
1077 p_indicator := g_dbi_info(l_index).indicator;
1078
1079 end read_dbi_cache;
1080
1081 ------------------------------- write_dbi_cache -------------------------------
1082 /*
1083 NAME
1084 write_dbi_cache
1085 DESCRIPTION
1086 Writes a value to db item cache.
1087 NOTES
1088 If the entry already exists, the current value is overwritten
1089 otherwise a new entry is created. The chaining from clashing
1090 hash values is dealt with.
1091
1092 Note that we default the context level to null, because
1093 we only need to pass this when the entry is first created.
1094
1095 The default is not to write to an existing entry.
1096 */
1097
1098 procedure write_dbi_cache
1099 (
1100 p_context_id in binary_integer,
1101 p_item_name in varchar2,
1102 p_data_type in varchar2,
1103 p_dvalue in date,
1104 p_nvalue in number,
1105 p_tvalue in varchar2,
1106 p_context_level1 in binary_integer default null,
1107 p_context_level2 in binary_integer default null,
1108 p_force_write in boolean default true
1109 ,p_indicator in binary_integer default FF_NULL
1110 ) is
1111 l_index binary_integer;
1112 begin
1113
1114 if g_debug then
1115 ff_utils.entry('write_dbi_cache');
1116 end if;
1117
1118 -- Look for an existing entry in the dbi cache.
1119 l_index := find_dbi_cache_entry(p_context_id, p_item_name);
1120
1121 if(l_index is not null) then
1122
1123 ff_utils.assert
1124 (p_expression => p_data_type = g_dbi_info(l_index).data_type
1125 ,p_location => 'write_dbi_cache:0'
1126 );
1127
1128 -- An entry exists, but only write if told to.
1129 if(p_force_write) then
1130 if p_indicator <> FF_NOT_NULL then
1131 if g_dbi_info(l_index).data_type = C_DATE then
1132 g_dbi_info(l_index).dvalue := NULL;
1133 elsif g_dbi_info(l_index).data_type = C_NUMBER then
1134 g_dbi_info(l_index).nvalue := NULL;
1135 else
1136 g_dbi_info(l_index).tvalue := NULL;
1137 end if;
1138 else
1139 if g_dbi_info(l_index).data_type = C_DATE then
1140 g_dbi_info(l_index).dvalue := p_dvalue;
1141 elsif g_dbi_info(l_index).data_type = C_NUMBER then
1142 g_dbi_info(l_index).nvalue := p_nvalue;
1143 else
1144 g_dbi_info(l_index).tvalue := p_tvalue;
1145 end if;
1146 end if;
1147
1148 g_dbi_info(l_index).indicator := p_indicator;
1149 end if;
1150 else
1151 /*
1152 * No entry exists, so create new one. Note that there
1153 * are two conditions that could cause this:
1154 * a) There is no hash table entry at all, in which
1155 * case both hash and dbi cache entries need to be
1156 * created.
1157 * b) There is a hash table entry, but no dbi cache
1158 * entry, in which case just the dbi cache entry
1159 * is needed.
1160 */
1161 if(not g_hash_info.exists(p_context_id)) then
1162 -- Create new hash entry.
1163 g_hash_info(p_context_id).first_entry := g_next_dbi_index;
1164 g_hash_info(p_context_id).chain_count := 1;
1165 l_index := g_next_dbi_index;
1166
1167 -- Point to next possible entry point.
1168 g_next_dbi_index := g_next_dbi_index + DBI_CACHE_INCR;
1169 else
1170 -- There is a hash index, but no entry in the cache
1171 -- itself.
1172 l_index := g_hash_info(p_context_id).first_entry +
1173 g_hash_info(p_context_id).chain_count;
1174
1175 -- Now have another entry on the chain.
1176 g_hash_info(p_context_id).chain_count :=
1177 g_hash_info(p_context_id).chain_count + 1;
1178
1179 end if;
1180
1181 -- For new create, the context level should be not null.
1182 ff_utils.assert((p_context_level1 is not null), 'write_dbi_cache:1');
1183 ff_utils.assert((p_context_level2 is not null), 'write_dbi_cache:2');
1184
1185 -- Now create the first dbi cache entry.
1186 g_dbi_info(l_index).item_name := p_item_name;
1187 g_dbi_info(l_index).context_level1 := p_context_level1;
1188 g_dbi_info(l_index).context_level2 := p_context_level2;
1189 g_dbi_info(l_index).data_type := p_data_type;
1190
1191 if p_indicator <> FF_NOT_NULL then
1192 if g_dbi_info(l_index).data_type = C_DATE then
1193 g_dbi_info(l_index).dvalue := NULL;
1194 elsif g_dbi_info(l_index).data_type = C_NUMBER then
1195 g_dbi_info(l_index).nvalue := NULL;
1196 else
1197 g_dbi_info(l_index).tvalue := NULL;
1198 end if;
1199 else
1200 if g_dbi_info(l_index).data_type = C_DATE then
1201 g_dbi_info(l_index).dvalue := p_dvalue;
1202 elsif g_dbi_info(l_index).data_type = C_NUMBER then
1203 g_dbi_info(l_index).nvalue := p_nvalue;
1204 else
1205 g_dbi_info(l_index).tvalue := p_tvalue;
1206 end if;
1207 end if;
1208
1209 g_dbi_info(l_index).indicator := p_indicator;
1210 end if;
1211
1212 if g_debug then
1213 ff_utils.exit('write_dbi_cache');
1214 end if;
1215
1216 end write_dbi_cache;
1217
1218 ------------------------ get_next_context_level -------------------------------
1219 /*
1220 NAME
1221 get_next_context_level
1222 DESCRIPTION
1223 Allocates the next formula context level bit.
1224 */
1225 procedure get_next_context_level
1226 (p_context_level1 out nocopy binary_integer
1227 ,p_context_level2 out nocopy binary_integer
1228 ) is
1229 begin
1230 if g_ctx_levels1 < C_MASK_BITS then
1231 p_context_level1 := 2 ** g_ctx_levels1;
1232 p_context_level2 := 0;
1233 g_ctx_levels1 := g_ctx_levels1 + 1;
1234 elsif g_ctx_levels2 < C_MASK_BITS then
1235 p_context_level2 := 2 ** g_ctx_levels2;
1236 p_context_level1 := 0;
1237 g_ctx_levels2 := g_ctx_levels2 + 1;
1238 else
1239 hr_utility.set_message(801, 'FF_33289_CONTEXT_CACHE_FULL');
1240 hr_utility.set_message_token('1', C_MAX_CONTEXTS);
1241 hr_utility.raise_error;
1242 end if;
1243 end get_next_context_level;
1244
1245 ----------------------------- dbi2route_id ------------------------------------
1246 /*
1247 NAME
1248 dbi2route_id
1249 DESCRIPTION
1250 Fetches the route_id given a database item name.
1251 NOTES
1252 p_business_group_id and p_legislation_code must come direct from
1253 ff_formulas_f i.e. p_legislation_code must not be derived from
1254 p_business_group_id.
1255 */
1256 function dbi2route_id
1257 (p_formula_name in varchar2
1258 ,p_dbi_name in varchar2
1259 ,p_business_group_id in number
1260 ,p_legislation_code in varchar2
1261 ) return binary_integer is
1262 cursor csr_route_id
1263 (p_dbi_name in varchar2
1264 ,p_business_group_id in number
1265 ,p_legislation_code in varchar2
1266 ) is
1267 select u.route_id
1268 from ff_database_items d
1269 , ff_user_entities u
1270 where d.user_name = p_dbi_name
1271 and d.user_entity_id = u.user_entity_id
1272 and (
1273 u.legislation_code is null and u.business_group_id is null or
1274 u.legislation_code = p_legislation_code or
1275 u.business_group_id = p_business_group_id or
1276 u.legislation_code =
1277 (
1278 select b.legislation_code
1279 from per_business_groups_perf b
1280 where b.business_group_id = p_business_group_id
1281 )
1282 )
1283 ;
1284 l_route_id binary_integer;
1285 begin
1286 open csr_route_id
1287 (p_dbi_name => p_dbi_name
1288 ,p_business_group_id => p_business_group_id
1289 ,p_legislation_code => p_legislation_code
1290 );
1291 fetch csr_route_id
1292 into l_route_id
1293 ;
1294 if csr_route_id%notfound then
1295 close csr_route_id;
1296 --
1297 -- Ask user to recompile because of potential data dictionary
1298 -- problem.
1299 --
1300 hr_utility.set_message(801, 'FFXBIF71_NEED_TO_REVERIFY');
1301 hr_utility.set_message_token('1', p_formula_name);
1302 hr_utility.raise_error;
1303 end if;
1304 close csr_route_id;
1305
1306 return l_route_id;
1307 end dbi2route_id;
1308
1309 ---------------------------- get_route_info -----------------------------------
1310 /*
1311 NAME
1312 get_route_info
1313 DESCRIPTION
1314 Fetches the route context information for given a route_id.
1315 */
1316 procedure get_route_info
1317 (p_formula_name in varchar2
1318 ,p_route_id in binary_integer
1319 ,p_context_sum1 out nocopy binary_integer
1320 ,p_context_sum2 out nocopy binary_integer
1321 ) is
1322 cursor csr_route_exists(p_route_id in binary_integer) is
1323 select null
1324 from ff_routes r
1325 where r.route_id = p_route_id
1326 ;
1327 --
1328 cursor csr_route_contexts
1329 (p_route_id in number
1330 ) is
1331 select rcu.context_id
1332 from ff_route_context_usages rcu
1333 where rcu.route_id = p_route_id
1334 ;
1335
1336 l_context_ids t_binary_integer;
1337 l_sum1 binary_integer := 0;
1338 l_sum2 binary_integer := 0;
1339 l_dummy varchar2(1);
1340 begin
1341 --
1342 -- Are the context sums for p_route_id already cached ?
1343 --
1344 if not g_route_ctx_sums.exists(p_route_id) then
1345 -- Validate that the route itself exists.
1346 open csr_route_exists(p_route_id => p_route_id);
1347 fetch csr_route_exists
1348 into l_dummy;
1349 if csr_route_exists%notfound then
1350 close csr_route_exists;
1351 --
1352 -- Ask user to recompile because of potential data dictionary
1353 -- problem.
1354 --
1355 hr_utility.set_message(801, 'FFXBIF71_NEED_TO_REVERIFY');
1356 hr_utility.set_message_token('1', p_formula_name);
1357 hr_utility.raise_error;
1358 end if;
1359 close csr_route_exists;
1360
1361 open csr_route_contexts(p_route_id => p_route_id);
1362 fetch csr_route_contexts bulk collect
1363 into l_context_ids;
1364 close csr_route_contexts;
1365 for i in 1 .. l_context_ids.count loop
1366 if not g_ctx_info.exists(l_context_ids(i)) then
1367 --
1368 -- Ask user to recompile because of potential data dictionary
1369 -- problem.
1370 --
1371 hr_utility.set_message(801, 'FFXBIF71_NEED_TO_REVERIFY');
1372 hr_utility.set_message_token('1', p_formula_name);
1373 hr_utility.raise_error;
1374 end if;
1375
1376 l_sum1 := l_sum1 + g_ctx_info(l_context_ids(i)).context_level1;
1377 l_sum2 := l_sum2 + g_ctx_info(l_context_ids(i)).context_level2;
1378 end loop;
1379
1380 --
1381 -- Cache the context level sums.
1382 --
1383 g_route_ctx_sums(p_route_id).context_sum1 := l_sum1;
1384 g_route_ctx_sums(p_route_id).context_sum2 := l_sum2;
1385 end if;
1386
1387 p_context_sum1 := g_route_ctx_sums(p_route_id).context_sum1;
1388 p_context_sum2 := g_route_ctx_sums(p_route_id).context_sum2;
1389 end get_route_info;
1390
1391 ------------------------------- ff_fetch --------------------------------------
1392 /*
1393 NAME
1394 ff_fetch
1395 DESCRIPTION
1396 Fetches specified formula information.
1397 NOTES
1398 Performs the actual fetches to put load the data
1399 for a specified formula into the internal
1400 cache structures.
1401 */
1402
1403 procedure ff_fetch
1404 (
1405 p_free_slot in binary_integer,
1406 p_formula_id in binary_integer,
1407 p_effective_date in date,
1408 p_fmla_lookups in out nocopy fmla_lookup_t,
1409 p_fmla_info in out nocopy fmla_info_t,
1410 p_fdiu_info in out nocopy fdiu_info_t,
1411 p_ctx_info in out nocopy dbi_cache_t
1412 ) is
1413
1414 cursor fdiuc1 is
1415 select fdiu.item_name name,
1416 to_number(substr(fdiu.item_generated_name, 2)) varpos,
1417 decode(fdiu.indicator_var_name, NULL, INVALID_POS,
1418 to_number(substr(fdiu.indicator_var_name, 2))) indpos,
1419 decode(fdiu.data_type,
1420 'D', 'DATE',
1421 'N', 'NUMBER',
1422 'T', 'TEXT', 'XXX') data_type,
1423 fdiu.usage,
1424 decode(fdiu.usage,
1425 'U', 1,
1426 'I', 2, 'B', 2, 'O', 2,
1427 'D', 3) usageorder,
1428 0 context_sum1,
1429 0 context_sum2,
1430 fdiu.context_id,
1431 fdiu.route_id
1432 from ff_fdi_usages_f fdiu
1433 where fdiu.formula_id = p_formula_id
1434 and p_effective_date between
1435 fdiu.effective_start_date and fdiu.effective_end_date
1436 and fdiu.load_when_running = 'Y'
1437 order by 6, fdiu.item_name; -- *** IMPORTANT.
1438
1439 l_effective_start_date date;
1440 l_effective_end_date date;
1441 l_formula_name ff_formulas_f.formula_name%type;
1442 l_fdiu_entry_count ff_compiled_info_f.fdiu_entry_count%type;
1443 l_package_name varchar2(60);
1444 l_first_fdiu binary_integer;
1445 l_fdiu_row binary_integer; -- latest fdiu row.
1446 l_fdiu_count binary_integer;
1447 l_input_count binary_integer := 0; -- count inputs for formula.
1448 l_output_count binary_integer := 0; -- count outputs for formula.
1449 l_context_count binary_integer := 0; -- count contexts for formula.
1450 l_business_group_id number;
1451 l_legislation_code varchar2(30);
1452
1453 --
1454 -- Separate tables making up the in-core FDIU to allow bulk binds.
1455 --
1456 l_fdiu_name t_big_varchar2;
1457 l_fdiu_varpos t_binary_integer;
1458 l_fdiu_indpos t_binary_integer;
1459 l_fdiu_data_type t_small_varchar2;
1460 l_fdiu_usage t_small_varchar2;
1461 l_fdiu_usage_order t_small_varchar2;
1462 l_fdiu_context_sum1 t_binary_integer;
1463 l_fdiu_context_sum2 t_binary_integer;
1464 l_fdiu_context_id t_binary_integer;
1465 l_fdiu_route_id t_binary_integer;
1466
1467 --
1468 -- Local copies of the parameters which are only updated at the end
1469 -- of the procedure call to avoid exceptions.
1470 --
1471 l_fmla_info fmla_info_r;
1472 l_fdiu_info fdiu_info_t;
1473 i binary_integer;
1474 l_got_fdiu_chunks boolean := false;
1475 l_reused boolean := false;
1476 l_assertion boolean;
1477
1478 begin
1479
1480 if g_debug then
1481 ff_utils.entry('ff_fetch');
1482 end if;
1483
1484 /*
1485 * Fetch formula information.
1486 */
1487 begin
1488 select 'FFP' || fff.formula_id || '_' ||
1489 to_char(fff.effective_start_date, 'DDMMYYYY'),
1490 fff.effective_start_date,
1491 fff.effective_end_date,
1492 fff.formula_name,
1493 fff.business_group_id,
1494 fff.legislation_code,
1495 fci.fdiu_entry_count
1496 into l_package_name,
1497 l_effective_start_date,
1498 l_effective_end_date,
1499 l_formula_name,
1500 l_business_group_id,
1501 l_legislation_code,
1502 l_fdiu_entry_count
1503 from ff_formulas_f fff,
1504 ff_compiled_info_f fci
1505 where fff.formula_id = p_formula_id
1506 and p_effective_date between
1507 fff.effective_start_date and fff.effective_end_date
1508 and fci.formula_id = fff.formula_id
1509 and p_effective_date between
1510 fci.effective_start_date and fci.effective_end_date;
1511 exception
1512 --
1513 -- Handle the case where there is no compiled formula.
1514 --
1515 when no_data_found then
1516 hr_utility.set_message(801, 'FFX22J_FORMULA_NOT_FOUND');
1517 hr_utility.set_message_token('1', p_formula_id);
1518 hr_utility.raise_error;
1519 when others then
1520 raise;
1521 end;
1522
1523 /*
1524 * Load the formula cache structure.
1525 * Note that the fdiu_count and mru_entry members
1526 * are set up later in this procedure.
1527 */
1528 l_fmla_info.formula_id := p_formula_id;
1529 l_fmla_info.effective_start_date := l_effective_start_date;
1530 l_fmla_info.effective_end_date := l_effective_end_date;
1531 l_fmla_info.formula_name := l_formula_name;
1532 l_fmla_info.package_name := l_package_name;
1533
1534 open fdiuc1;
1535 fetch fdiuc1 bulk collect
1536 into l_fdiu_name,
1537 l_fdiu_varpos,
1538 l_fdiu_indpos,
1539 l_fdiu_data_type,
1540 l_fdiu_usage,
1541 l_fdiu_usage_order,
1542 l_fdiu_context_sum1,
1543 l_fdiu_context_sum2,
1544 l_fdiu_context_id,
1545 l_fdiu_route_id;
1546 close fdiuc1;
1547
1548 -- Check that number of fdiu entries matches the fdiu count
1549 ff_utils.assert((l_fdiu_entry_count = l_fdiu_name.count),
1550 'ff_fetch:1');
1551
1552 --
1553 -- See if it is possible to reuse free fdiu records.
1554 --
1555 find_free_chunk
1556 (p_chunk_size => l_fdiu_entry_count
1557 ,p_free_chunks => g_free_fdiu
1558 ,p_start => l_first_fdiu
1559 );
1560 if l_first_fdiu is null then
1561 l_first_fdiu := nvl(p_fdiu_info.last, 0) + 1;
1562
1563 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
1564 hr_utility.trace
1565 ('ff_fetch add to FDIU from end of list:' || l_first_fdiu || ',' ||
1566 l_fdiu_entry_count
1567 );
1568 end if;
1569
1570 --
1571 -- This is a check for free FDIU list consistency.
1572 --
1573 if g_free_fdiu.last is not null then
1574 l_assertion :=
1575 l_first_fdiu > g_free_fdiu.last + g_free_fdiu(g_free_fdiu.last) - 1;
1576
1577 if not l_assertion and
1578 (bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
1579 hr_utility.trace
1580 ('ff_fetch assertion 2 failed:'||l_first_fdiu||','||
1581 to_char(g_free_fdiu.last + g_free_fdiu(g_free_fdiu.last) - 1)
1582 );
1583 end if;
1584
1585 ff_utils.assert(l_assertion, 'ff_fetch:2');
1586 end if;
1587 else
1588 l_reused := true;
1589 end if;
1590
1591 -- Init the counters as required.
1592 l_first_fdiu := nvl(l_first_fdiu, 0);
1593 l_fmla_info.first_fdiu := l_first_fdiu;
1594 l_fdiu_row := l_first_fdiu;
1595
1596 -- Fetch the rows into fdiu for formula.
1597 for i in 1 .. l_fdiu_name.count loop
1598
1599 -- Process according to usage.
1600 if(l_fdiu_usage(i) = 'U' or l_fdiu_usage(i) = 'D')
1601 then
1602 -- There MUST be a context_id for 'U' or 'D'.
1603 if(l_fdiu_context_id(i) is null) then
1604 hr_utility.set_message(801, 'FFPLX01_CONTEXT_ID_NULL');
1605 hr_utility.set_message_token('FMLA_NAME', l_formula_name);
1606 hr_utility.set_message_token('ITEM_NAME', l_fdiu_name(i));
1607 hr_utility.raise_error;
1608 end if;
1609
1610 if(l_fdiu_usage(i) = 'U') then
1611 l_context_count := l_context_count + 1; -- count contexts.
1612
1613 -- Write to context cache if no existing entry.
1614 if(not p_ctx_info.exists(l_fdiu_context_id(i))) then
1615
1616 -- Dynamically allocate the context level.
1617 get_next_context_level
1618 (p_context_level1 => l_fdiu_context_sum1(i)
1619 ,p_context_level2 => l_fdiu_context_sum2(i)
1620 );
1621
1622 p_ctx_info(l_fdiu_context_id(i)).item_name := l_fdiu_name(i);
1623 p_ctx_info(l_fdiu_context_id(i)).dvalue := NULL;
1624 p_ctx_info(l_fdiu_context_id(i)).nvalue := NULL;
1625 p_ctx_info(l_fdiu_context_id(i)).tvalue := NULL;
1626 p_ctx_info(l_fdiu_context_id(i)).context_level1 :=
1627 l_fdiu_context_sum1(i);
1628 p_ctx_info(l_fdiu_context_id(i)).context_level2 :=
1629 l_fdiu_context_sum2(i);
1630 else
1631
1632 -- Get context level from the context cache.
1633 l_fdiu_context_sum1(i) :=
1634 p_ctx_info(l_fdiu_context_id(i)).context_level1;
1635 l_fdiu_context_sum2(i) :=
1636 p_ctx_info(l_fdiu_context_id(i)).context_level2;
1637 end if;
1638 else
1639 -- Must be a database item. Write an entry to cache
1640 -- if it doesn't exist.
1641
1642 --
1643 -- First perform some route_id validation.
1644 --
1645 if l_fdiu_route_id(i) is null then
1646 l_fdiu_route_id(i) :=
1647 dbi2route_id
1648 (p_formula_name => l_formula_name
1649 ,p_dbi_name => l_fdiu_name(i)
1650 ,p_business_group_id => l_business_group_id
1651 ,p_legislation_code => l_legislation_code
1652 );
1653 end if;
1654 --
1655 get_route_info
1656 (p_formula_name => l_formula_name
1657 ,p_route_id => l_fdiu_route_id(i)
1658 ,p_context_sum1 => l_fdiu_context_sum1(i)
1659 ,p_context_sum2 => l_fdiu_context_sum2(i)
1660 );
1661 --
1662 write_dbi_cache
1663 (p_context_id => l_fdiu_context_id(i)
1664 ,p_item_name => l_fdiu_name(i)
1665 ,p_data_type => l_fdiu_data_type(i)
1666 ,p_dvalue => NULL
1667 ,p_nvalue => NULL
1668 ,p_tvalue => NULL
1669 ,p_context_level1 => l_fdiu_context_sum1(i)
1670 ,p_context_level2 => l_fdiu_context_sum2(i)
1671 ,p_force_write => FALSE
1672 ,p_indicator => FF_NULL
1673 );
1674 end if;
1675
1676 end if;
1677
1678 if(l_fdiu_usage(i) in ('I', 'B')) then
1679 l_input_count := l_input_count + 1; -- count inputs and outputs.
1680 end if;
1681
1682 if(l_fdiu_usage(i) in ('O', 'B')) then -- count outputs.
1683 l_output_count := l_output_count + 1;
1684 end if;
1685
1686 /*
1687 * Set up in core fdiu entries.
1688 * It is safe to update the global structure because it can't be used
1689 * until the formula information structures are updated.
1690 */
1691 p_fdiu_info(l_fdiu_row).name := l_fdiu_name(i);
1692 p_fdiu_info(l_fdiu_row).varpos := l_fdiu_varpos(i);
1693 p_fdiu_info(l_fdiu_row).indpos := l_fdiu_indpos(i);
1694 p_fdiu_info(l_fdiu_row).data_type := l_fdiu_data_type(i);
1695 p_fdiu_info(l_fdiu_row).usage := l_fdiu_usage(i);
1696 p_fdiu_info(l_fdiu_row).context_sum1 := l_fdiu_context_sum1(i);
1697 p_fdiu_info(l_fdiu_row).context_sum2 := l_fdiu_context_sum2(i);
1698 p_fdiu_info(l_fdiu_row).context_id := l_fdiu_context_id(i);
1699 --
1700 if not l_got_fdiu_chunks then
1701 l_got_fdiu_chunks := true;
1702 end if;
1703
1704 -- Increment the row counter.
1705 l_fdiu_row := l_fdiu_row + 1;
1706
1707 end loop;
1708
1709 -- Set up the number of rows in fdiu for this formula.
1710 l_fmla_info.fdiu_count := l_fdiu_row - l_first_fdiu;
1711
1712 -- Record the context, input and output count.
1713 l_fmla_info.context_count := l_context_count;
1714 l_fmla_info.input_count := l_input_count;
1715 l_fmla_info.output_count := l_output_count;
1716
1717 --
1718 -- Can safely update the global information now.
1719 --
1720 p_fmla_info(p_free_slot) := l_fmla_info;
1721 p_fmla_lookups(p_formula_id) := p_free_slot;
1722
1723 if g_debug then
1724 ff_utils.exit('ff_fetch');
1725 end if;
1726
1727 exception
1728 when others then
1729 if fdiuc1%isopen then
1730 close fdiuc1;
1731 end if;
1732 --
1733 -- If chunks were reused then return them to the free list.
1734 --
1735 if l_reused then
1736 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
1737 hr_utility.trace('ff_fetch return reused rows to free list.');
1738 end if;
1739
1740 add_to_free_list
1741 (p_first_chunk => l_fmla_info.first_fdiu
1742 ,p_chunk_count => l_fdiu_entry_count
1743 ,p_free_chunks => g_free_fdiu
1744 );
1745 --
1746 -- If the chunks weren't reused then they were allocated from
1747 -- the end of the fdiu list.
1748 --
1749 elsif l_got_fdiu_chunks then
1750 if(bitand(ff_utils.g_debug_level, ff_exec.FF_CACHE_DBG) <> 0) then
1751 hr_utility.trace('ff_fetch add new rows to free list.');
1752 end if;
1753
1754 add_to_free_list
1755 (p_first_chunk => l_fmla_info.first_fdiu
1756 ,p_chunk_count => p_fdiu_info.last - l_fmla_info.first_fdiu
1757 ,p_free_chunks => g_free_fdiu
1758 );
1759 end if;
1760 --
1761 if g_debug then
1762 ff_utils.exit('ff_fetch:2');
1763 end if;
1764 --
1765 raise;
1766 end ff_fetch;
1767
1768 -------------------------------- set_lru_slot ---------------------------------
1769 /*
1770 NAME
1771 set_lru_slot
1772 DESCRIPTION
1773 Set the LRU slot to (for deleting when the cache is full).
1774 NOTES
1775 */
1776 procedure set_lru_slot
1777 (p_fmla_lookups in fmla_lookup_t
1778 ,p_exec_info in exec_info_r
1779 ,p_mru_slot in binary_integer
1780 ,p_lru_slot in out nocopy binary_integer
1781 ) is
1782 begin
1783 --
1784 -- The cache is full - LRU slot is used to cycle through a circular
1785 -- buffer. Only change the LRU slot if it's the same as the MRU slot.
1786 --
1787 if p_fmla_lookups.count >= p_exec_info.fmla_cache_size and
1788 p_lru_slot = p_mru_slot then
1789
1790 -- Handle getting to the end of the buffer.
1791 if p_lru_slot = p_exec_info.fmla_cache_size then
1792 if(bitand(ff_utils.g_debug_level, ff_exec.MRU_DBG) <> 0) then
1793 hr_utility.trace('LRU = 1');
1794 end if;
1795
1796 p_lru_slot := 1;
1797 else
1798 if(bitand(ff_utils.g_debug_level, ff_exec.MRU_DBG) <> 0) then
1799 hr_utility.trace('LRU := LRU + 1:' || to_char(p_lru_slot + 1));
1800 end if;
1801
1802 p_lru_slot := p_lru_slot + 1;
1803 end if;
1804 end if;
1805 end set_lru_slot;
1806 ---------------------------- load_formula -------------------------------------
1807 /*
1808 NAME
1809 load_formula
1810 DESCRIPTION
1811 Loads information for specific formula into formula cache.
1812 NOTES
1813 Formula information is loaded as appropriate into the
1814 various cache structures.
1815 */
1816
1817 procedure load_formula
1818 (
1819 p_formula_id in number,
1820 p_effective_date in date,
1821 p_exec_info in out nocopy exec_info_r,
1822 p_fmla_lookups in out nocopy fmla_lookup_t,
1823 p_fmla_info in out nocopy fmla_info_t,
1824 p_lru_slot in out nocopy binary_integer,
1825 p_fdiu_info in out nocopy fdiu_info_t,
1826 p_ctx_info in out nocopy dbi_cache_t
1827 ) is
1828 -- Local variable defintions.
1829 l_cache_slot binary_integer;
1830 l_need_to_fetch boolean;
1831
1832 /*
1833 * Local procedure to zap cache rows
1834 * for a specified formula.
1835 */
1836 procedure zap_fcr (p_free_slot in binary_integer,
1837 p_fmla_lookups in out nocopy fmla_lookup_t,
1838 p_fmla_info in out nocopy fmla_info_t,
1839 p_fdiu_info in out nocopy fdiu_info_t) is
1840 begin
1841
1842 if g_debug then
1843 ff_utils.entry('zap_fcr');
1844 end if;
1845
1846 ff_utils.assert(p_fmla_info.exists(p_free_slot), 'zap_fcr:1');
1847
1848 --
1849 -- Check that this slot has not been zapped previously.
1850 --
1851 if p_fmla_info(p_free_slot).formula_id is null then
1852 return;
1853 end if;
1854
1855 --
1856 -- Make sure that the reference to this slot is removed.
1857 --
1858 p_fmla_lookups(p_fmla_info(p_free_slot).formula_id) := null;
1859
1860 --
1861 -- Mark the FDIU rows as free for reuse.
1862 --
1863 if(p_fmla_info(p_free_slot).fdiu_count > 0) then
1864 add_to_free_list
1865 (p_first_chunk => p_fmla_info(p_free_slot).first_fdiu
1866 ,p_chunk_count => p_fmla_info(p_free_slot).fdiu_count
1867 ,p_free_chunks => g_free_fdiu
1868 );
1869 end if;
1870
1871 --
1872 -- Make sure that this slot cannot be used.
1873 --
1874 p_fmla_info(p_free_slot).formula_id := null;
1875 p_fmla_info(p_free_slot).fdiu_count := null;
1876 p_fmla_info(p_free_slot).first_fdiu := null;
1877
1878 if g_debug then
1879 ff_utils.exit('zap_fcr');
1880 end if;
1881
1882 end zap_fcr;
1883
1884 begin
1885
1886 if g_debug then
1887 ff_utils.entry('load_formula');
1888 end if;
1889
1890 /*
1891 * Load formula information into internal structures.
1892 */
1893 l_need_to_fetch := true;
1894
1895 --
1896 -- Check for an entry for the formula in the cache and validate
1897 -- the date range.
1898 --
1899 if p_fmla_lookups.exists(p_formula_id) and
1900 p_fmla_lookups(p_formula_id) is not null then
1901 l_cache_slot := p_fmla_lookups(p_formula_id);
1902 if p_effective_date < p_fmla_info(l_cache_slot).effective_start_date or
1903 p_effective_date > p_fmla_info(l_cache_slot).effective_end_date
1904 then
1905 -- Zap rows from cache for this formula.
1906 zap_fcr(l_cache_slot, p_fmla_lookups, p_fmla_info, p_fdiu_info);
1907 else
1908 -- The dates match so there is no need to fetch.
1909 l_need_to_fetch := false;
1910 end if;
1911 --
1912 -- Handle the case where the formula is not in the cache, but the cache is
1913 -- full.
1914 --
1915 elsif p_fmla_lookups.count >= p_exec_info.fmla_cache_size then
1916 --
1917 -- Get the formula_id from the lru slot.
1918 --
1919 zap_fcr(p_lru_slot, p_fmla_lookups, p_fmla_info, p_fdiu_info);
1920 l_cache_slot := p_lru_slot;
1921 --
1922 -- Empty slots are available, so put onto the end of the cache list.
1923 --
1924 else
1925 l_cache_slot := p_fmla_info.count + 1;
1926 p_fmla_info.extend(1);
1927 end if;
1928
1929 --
1930 -- Fetch formula details.
1931 --
1932 if (l_need_to_fetch) then
1933 ff_fetch (l_cache_slot, p_formula_id, p_effective_date, p_fmla_lookups,
1934 p_fmla_info, p_fdiu_info, p_ctx_info);
1935 end if;
1936
1937 -- Debugging output.
1938 if g_debug then
1939 fmla_cache_debug(p_formula_id, p_fmla_lookups, p_fmla_info, p_fdiu_info);
1940 end if;
1941
1942 -- Set up the global environment with current formula details.
1943 p_exec_info.formula_id := p_formula_id;
1944 p_exec_info.cache_slot := l_cache_slot;
1945 p_exec_info.effective_date := p_effective_date;
1946
1947 -- Indicate to outside world how many contexts, inputs and
1948 -- outputs the formula is expecting.
1949 ff_exec.context_count := p_fmla_info(l_cache_slot).context_count;
1950 ff_exec.input_count := p_fmla_info(l_cache_slot).input_count;
1951 ff_exec.output_count := p_fmla_info(l_cache_slot).output_count;
1952
1953 -- Reset the LRU slot.
1954 set_lru_slot(p_fmla_lookups, p_exec_info, l_cache_slot, p_lru_slot);
1955
1956 if g_debug then
1957 ff_utils.exit('load_formula');
1958 end if;
1959
1960 end load_formula;
1961
1962 ----------------------------- invalidate_db_items -----------------------------
1963 /*
1964 NAME
1965 invalidate_db_items
1966 DESCRIPTION
1967 Invalidate db items if necessary, following context value change.
1968 NOTES
1969 Skulls through datbase item cache list, invalidating (writing null to)
1970 the item if necessary.
1971 */
1972
1973 procedure invalidate_db_items
1974 (
1975 p_context_sum1 in binary_integer,
1976 p_context_sum2 in binary_integer
1977 ) is
1978 l_index binary_integer;
1979 l_path binary_integer;
1980 begin
1981 if g_debug then
1982 ff_utils.entry('invalidate_db_items');
1983 end if;
1984
1985 if p_context_sum1 <> 0 and p_context_sum2 = 0 then
1986 l_path := 1;
1987 elsif p_context_sum1 = 0 and p_context_sum2 <> 0 then
1988 l_path := 2;
1989 elsif p_context_sum1 <> 0 and p_context_sum2 <> 0 then
1990 l_path := 3;
1991 else
1992 --
1993 -- The bit masks are both 0 so no need to do anything.
1994 --
1995 return;
1996 end if;
1997
1998 if l_path = 1 then
1999 l_index := g_dbi_info.first;
2000
2001 while(l_index is not null) loop
2002
2003 if(bitand(p_context_sum1, g_dbi_info(l_index).context_level1) <> 0)
2004 then
2005 if g_debug then
2006 dbi_invalid_debug
2007 (g_dbi_info(l_index)
2008 ,p_context_sum1
2009 ,p_context_sum2
2010 );
2011 end if;
2012 g_dbi_info(l_index).indicator := FF_NULL;
2013 g_dbi_info(l_index).dvalue := NULL;
2014 g_dbi_info(l_index).nvalue := NULL;
2015 g_dbi_info(l_index).tvalue := NULL;
2016 end if;
2017
2018 l_index := g_dbi_info.next(l_index);
2019 end loop;
2020 elsif l_path = 2 then
2021
2022 l_index := g_dbi_info.first;
2023
2024 while(l_index is not null) loop
2025
2026 if(bitand(p_context_sum2, g_dbi_info(l_index).context_level2) <> 0)
2027 then
2028 if g_debug then
2029 dbi_invalid_debug
2030 (g_dbi_info(l_index)
2031 ,p_context_sum1
2032 ,p_context_sum2
2033 );
2034 end if;
2035 g_dbi_info(l_index).indicator := FF_NULL;
2036 g_dbi_info(l_index).dvalue := NULL;
2037 g_dbi_info(l_index).nvalue := NULL;
2038 g_dbi_info(l_index).tvalue := NULL;
2039 end if;
2040
2041 l_index := g_dbi_info.next(l_index);
2042 end loop;
2043 else
2044 l_index := g_dbi_info.first;
2045
2046 while(l_index is not null) loop
2047
2048 if(bitand(p_context_sum1, g_dbi_info(l_index).context_level1) <> 0 or
2049 bitand(p_context_sum2, g_dbi_info(l_index).context_level2) <> 0)
2050 then
2051 if g_debug then
2052 dbi_invalid_debug
2053 (g_dbi_info(l_index)
2054 ,p_context_sum1
2055 ,p_context_sum2
2056 );
2057 end if;
2058 g_dbi_info(l_index).indicator := FF_NULL;
2059 g_dbi_info(l_index).dvalue := NULL;
2060 g_dbi_info(l_index).nvalue := NULL;
2061 g_dbi_info(l_index).tvalue := NULL;
2062 end if;
2063
2064 l_index := g_dbi_info.next(l_index);
2065 end loop;
2066 end if;
2067
2068 if g_debug then
2069 ff_utils.exit('invalidate_db_items');
2070 end if;
2071
2072 end invalidate_db_items;
2073
2074 ------------------------------ bind_variables ---------------------------------
2075 /*
2076 NAME
2077 bind_variables
2078 DESCRIPTION
2079 Deals with binding the anonymous block PLSQL variables.
2080 NOTES
2081 Called from the run_formula procedure.
2082 */
2083
2084 procedure bind_variables
2085 (
2086 p_cache_slot in binary_integer,
2087 p_inputs in ff_exec.inputs_t,
2088 p_outputs in ff_exec.outputs_t,
2089 p_fmla_info in fmla_info_t,
2090 p_fdiu_info in fdiu_info_t,
2091 p_ctx_info in out nocopy dbi_cache_t,
2092 p_d in out nocopy ff_wrapper_pkg.t_date,
2093 p_n in out nocopy ff_wrapper_pkg.t_number,
2094 p_t in out nocopy ff_wrapper_pkg.t_text,
2095 p_i in out nocopy ff_wrapper_pkg.t_number,
2096 p_use_dbi_cache in boolean
2097 ) is
2098
2099 l_context_total1 binary_integer;
2100 l_context_total2 binary_integer;
2101 l_first_fdiu binary_integer;
2102 l_last_fdiu binary_integer;
2103 l_count binary_integer;
2104 l_item_name varchar2(240);
2105 l_varpos binary_integer;
2106 l_indpos binary_integer;
2107 l_indic_value binary_integer;
2108 l_dvalue date;
2109 l_nvalue number;
2110 l_tvalue varchar2(255); -- matches ff_exec.FF_BIND_LEN
2111 l_data_type varchar2(7);
2112 l_usage varchar2(1);
2113 l_context_id binary_integer; -- context_id.
2114 l_context_sum1 binary_integer;
2115 l_context_sum2 binary_integer;
2116 l_context_count binary_integer;
2117 l_in_index binary_integer; -- only interested in count of inputs.
2118 begin
2119
2120 if g_debug then
2121 ff_utils.entry('bind_variables');
2122 end if;
2123
2124 l_context_total1 := 0;
2125 l_context_total2 := 0;
2126 l_context_count := 0;
2127 l_in_index := 0;
2128
2129 /*
2130 * Bind variables body.
2131 */
2132 l_first_fdiu := p_fmla_info(p_cache_slot).first_fdiu;
2133 l_last_fdiu := l_first_fdiu + p_fmla_info(p_cache_slot).fdiu_count - 1;
2134
2135 for l_count in l_first_fdiu..l_last_fdiu loop
2136 /*
2137 * Get the value to bind.
2138 * Where we get this from depends on the usage.
2139 * Note how the fdiu is ordered, this is important.
2140 * Assign some values to local variables to make the
2141 * code a little easier to understand.
2142 */
2143 l_item_name := p_fdiu_info(l_count).name;
2144 l_varpos := p_fdiu_info(l_count).varpos;
2145 l_indpos := p_fdiu_info(l_count).indpos;
2146 l_indic_value := null;
2147 l_data_type := p_fdiu_info(l_count).data_type;
2148 l_usage := p_fdiu_info(l_count).usage;
2149 l_context_id := p_fdiu_info(l_count).context_id;
2150 l_context_sum1 := p_fdiu_info(l_count).context_sum1;
2151 l_context_sum2 := p_fdiu_info(l_count).context_sum2;
2152
2153 -- Initialise the bind variables to NULL each time.
2154 l_nvalue := NULL;
2155 l_dvalue := NULL;
2156 l_tvalue := NULL;
2157
2158 /*
2159 * Take action, depending on the current usage.
2160 * We are attempting to set a value for l_value.
2161 */
2162 if(l_usage = 'U') then
2163 /* context */
2164
2165 -- Keep count of how many contexts we have processed.
2166 l_context_count := l_context_count + 1;
2167
2168 l_in_index := l_in_index + 1; -- Count index of inputs table.
2169
2170 /* user not allowed to supply NULL value for context */
2171 if(p_inputs(l_in_index).value is null) then
2172 hr_utility.set_message(801, 'FFX02_UIDCOL_MISSING');
2173 hr_utility.set_message_token('1',
2174 p_fmla_info(p_cache_slot).formula_name);
2175 hr_utility.set_message_token('2', l_item_name);
2176 hr_utility.raise_error;
2177 end if;
2178
2179 -- read context value from cache.
2180 l_tvalue := p_ctx_info(l_context_id).tvalue;
2181
2182 -- See if value is either NULL or different from
2183 -- the value the user has supplied.
2184 -- Note the value should only be NULL if this is
2185 -- the first time we have read the value.
2186 if(l_tvalue is null or l_tvalue <> p_inputs(l_in_index).value) then
2187
2188 -- Log changed context.
2189 if g_debug then
2190 ctx_change_debug(l_item_name, l_context_sum1, l_context_sum2,
2191 l_tvalue, p_inputs(l_in_index).value);
2192 end if;
2193
2194 -- Add the current context level to the context total.
2195 if l_context_sum1 <> 0 then
2196 l_context_total1 := l_context_total1 + l_context_sum1;
2197 else
2198 l_context_total2 := l_context_total2 + l_context_sum2;
2199 end if;
2200
2201 --
2202 -- Always set p_ctx_info(l_context_id).tvalue as it is used for the
2203 -- the comparison above.
2204 --
2205 p_ctx_info(l_context_id).tvalue := p_inputs(l_in_index).value;
2206 l_tvalue := p_inputs(l_in_index).value;
2207
2208 --
2209 -- Get the value from the inputs table and write to the contexts
2210 -- cache.
2211 --
2212 if l_data_type = C_DATE then
2213 p_ctx_info(l_context_id).dvalue :=
2214 fnd_date.canonical_to_date(l_tvalue);
2215 elsif l_data_type = C_NUMBER then
2216 p_ctx_info(l_context_id).nvalue := replace(l_tvalue, '.', g_decpoint);
2217 end if;
2218
2219 end if;
2220
2221 --
2222 -- Set the context values for binding.
2223 --
2224 if l_data_type = C_DATE then
2225 l_dvalue := p_ctx_info(l_context_id).dvalue;
2226 elsif l_data_type = C_NUMBER then
2227 l_nvalue := p_ctx_info(l_context_id).nvalue;
2228 else
2229 l_tvalue := p_ctx_info(l_context_id).tvalue;
2230 end if;
2231
2232 -- Have we reached the final context?
2233 if(l_context_count = p_fmla_info(p_cache_slot).context_count) then
2234
2235 -- Have processed last context, see if need to invalidate
2236 -- any database items as a consequence.
2237 invalidate_db_items(l_context_total1, l_context_total2);
2238
2239 end if;
2240
2241 -- Following all this, value should NOT be null.
2242 ff_utils.assert((l_tvalue is not null), 'bind_variables:1');
2243
2244 elsif(l_usage in ('I', 'B')) then
2245 /* An input variable (or input and output). */
2246 l_in_index := l_in_index + 1; -- Count index of inputs table.
2247
2248 -- Convert the values from the inputs table as set by user.
2249 if l_data_type = C_DATE then
2250 l_dvalue := fnd_date.canonical_to_date(p_inputs(l_in_index).value);
2251 elsif l_data_type = C_NUMBER then
2252 l_nvalue := replace(p_inputs(l_in_index).value, '.', g_decpoint);
2253 elsif l_data_type = C_TEXT then
2254 l_tvalue := p_inputs(l_in_index).value;
2255 end if;
2256
2257 elsif(l_usage = 'O') then
2258 /* Output (return) variable. */
2259 -- Simply leave the value as NULL.
2260 null;
2261
2262 elsif(l_usage = 'D') then
2263 /* Database item. */
2264 -- Get the value for database item.
2265 -- If the db item cache is in use, read the value from
2266 -- there, otherwise, set it to NULL.
2267 if(p_use_dbi_cache) then
2268 read_dbi_cache
2269 (p_context_id => l_context_id
2270 ,p_item_name => l_item_name
2271 ,p_data_type => l_data_type
2272 ,p_dvalue => l_dvalue
2273 ,p_nvalue => l_nvalue
2274 ,p_tvalue => l_tvalue
2275 ,p_indicator => l_indic_value
2276 );
2277 else
2278 --
2279 -- The bind variables are already set to NULL. Set the indicator
2280 -- to NULL.
2281 --
2282 l_indic_value := FF_NULL;
2283 end if;
2284 else
2285 -- Houston, we have a problem.
2286 ff_utils.assert(FALSE, 'bind_variables:2');
2287 end if;
2288
2289 /*
2290 * Bind the appropriate variable (and indicator)
2291 * with the value we have obtained.
2292 */
2293 if(l_data_type = C_DATE) then
2294 p_d(l_varpos) := l_dvalue;
2295 elsif(l_data_type = C_NUMBER) then
2296 p_n(l_varpos) := l_nvalue;
2297 elsif(l_data_type = C_TEXT) then
2298 p_t(l_varpos) := l_tvalue;
2299 else
2300 hr_utility.set_message(801, 'FFIC874_UNKNOWN_DATATYPE');
2301 hr_utility.set_message_token('1', l_item_name);
2302 hr_utility.set_message_token('2', l_data_type);
2303 hr_utility.raise_error;
2304 end if;
2305
2306 /*
2307 * Bind the indicator (if appropriate).
2308 */
2309 if(l_indpos <> INVALID_POS) then
2310 -- Set indicator dependent on the value passed in.
2311 if l_indic_value is not null then
2312 p_i(l_indpos) := l_indic_value;
2313 else
2314 if l_dvalue is not null or
2315 l_nvalue is not null or
2316 l_tvalue is not null then
2317 p_i(l_indpos) := FF_NOT_NULL;
2318 else
2319 p_i(l_indpos) := FF_NULL;
2320 end if;
2321 end if;
2322 end if;
2323 end loop;
2324
2325 if g_debug then
2326 ff_utils.exit('bind_variables');
2327 end if;
2328
2329 end bind_variables;
2330
2331 -------------------------------- set_outputs ----------------------------------
2332 /*
2333 NAME
2334 set_outputs
2335 DESCRIPTION
2336 Sets outputs in outputs table from returned values.
2337 NOTES
2338 Called from the run_formula procedure.
2339 */
2340 procedure set_outputs
2341 (
2342 p_cache_slot in binary_integer,
2343 p_fmla_info in fmla_info_t,
2344 p_fdiu_info in fdiu_info_t,
2345 p_d in ff_wrapper_pkg.t_date,
2346 p_n in ff_wrapper_pkg.t_number,
2347 p_t in ff_wrapper_pkg.t_text,
2348 p_i in ff_wrapper_pkg.t_number,
2349 p_outputs in out nocopy ff_exec.outputs_t
2350 ) is
2351 l_first_fdiu binary_integer;
2352 l_last_fdiu binary_integer;
2353 l_count_fdiu binary_integer;
2354 l_out_index binary_integer := 0;
2355 l_varpos binary_integer;
2356 l_indpos binary_integer;
2357 l_indic_value binary_integer;
2358 l_usage varchar2(1);
2359 l_data_type varchar2(6);
2360 l_dvalue date;
2361 l_nvalue number;
2362 l_value varchar2(255); -- matches ff_exec.FF_BIND_LEN
2363 begin
2364
2365 if g_debug then
2366 ff_utils.entry('set_outputs');
2367 end if;
2368
2369 /*
2370 * Set the output table from returned values.
2371 */
2372 l_first_fdiu := p_fmla_info(p_cache_slot).first_fdiu;
2373 l_last_fdiu := l_first_fdiu + p_fmla_info(p_cache_slot).fdiu_count - 1;
2374
2375 for l_count_fdiu in l_first_fdiu..l_last_fdiu loop
2376
2377 -- We only wish to process anything for
2378 -- output variables or database items.
2379 if(p_fdiu_info(l_count_fdiu).usage in ('O', 'B', 'D')) then
2380
2381 -- Set up some locals for convenience.
2382 l_varpos := p_fdiu_info(l_count_fdiu).varpos;
2383 l_indpos := p_fdiu_info(l_count_fdiu).indpos;
2384 l_usage := p_fdiu_info(l_count_fdiu).usage;
2385 l_data_type := p_fdiu_info(l_count_fdiu).data_type;
2386
2387 /* Get the variable value. */
2388 if l_usage <> 'D' then
2389 if(l_data_type = C_DATE) then
2390 -- Dates converted to apps Canonical format.
2391 l_value := fnd_date.date_to_canonical(p_d(l_varpos));
2392 elsif(l_data_type = C_NUMBER) then
2393 -- Numbers converted to canonical format.
2394 l_value := replace(to_char(p_n(l_varpos)),g_decpoint,'.');
2395 else
2396 l_value := p_t(l_varpos);
2397 end if;
2398 else
2399 if(l_data_type = C_DATE) then
2400 l_dvalue := p_d(l_varpos);
2401 l_nvalue := NULL;
2402 l_value := NULL;
2403 elsif(l_data_type = C_NUMBER) then
2404 l_nvalue := p_n(l_varpos);
2405 l_dvalue := NULL;
2406 l_value := NULL;
2407 else
2408 l_value := p_t(l_varpos);
2409 l_dvalue := NULL;
2410 l_nvalue := NULL;
2411 end if;
2412 end if;
2413
2414 /* Get the indicator value. */
2415 l_indic_value := p_i(l_indpos);
2416
2417 /* Process the indicator. */
2418 if(l_indic_value = FF_NULL) then
2419 -- The indicator shows we need to set the value to null.
2420 l_value := NULL;
2421 l_dvalue := NULL;
2422 l_nvalue := NULL;
2423 end if;
2424
2425 /* process dependent on the usage */
2426 if(l_usage in ('O', 'B')) then
2427
2428 l_out_index := l_out_index + 1; -- Where are we in output table?
2429
2430 -- The variable is an output type, so set output table entry.
2431 p_outputs(l_out_index).value := l_value;
2432
2433 else /* usage is 'D' */
2434
2435 --
2436 -- Database item was returned
2437 -- If the database item was not defaulted, we want to write
2438 -- the returned value to the db item cache. If it was defaulted
2439 -- then, we want to update the indicator value in the cache.
2440 --
2441 if(l_indic_value = FF_WAS_DEFAULTED) then
2442 l_value := NULL;
2443 l_nvalue := NULL;
2444 l_dvalue := NULL;
2445 end if;
2446
2447 write_dbi_cache
2448 (p_context_id => p_fdiu_info(l_count_fdiu).context_id
2449 ,p_item_name => p_fdiu_info(l_count_fdiu).name
2450 ,p_data_type => l_data_type
2451 ,p_dvalue => l_dvalue
2452 ,p_nvalue => l_nvalue
2453 ,p_tvalue => l_value
2454 ,p_force_write => TRUE
2455 ,p_indicator => l_indic_value
2456 );
2457
2458 end if;
2459
2460 end if;
2461
2462 end loop;
2463
2464 if g_debug then
2465 ff_utils.exit('set_outputs');
2466 end if;
2467
2468 exception
2469 when others then
2470 --
2471 -- NOCOPY change. The issue is to avoid the FF Outputs having partially
2472 -- filled-in values now that FF_EXEC.RUN_FORMULA is passing them in
2473 -- by reference i.e. using NOCOPY.
2474 -- Solution: Set the outputs to NULL.
2475 --
2476 l_out_index := 0;
2477 for l_count_fdiu in l_first_fdiu..l_last_fdiu loop
2478 --
2479 -- Only interested in values that get set on output. DBI behaviour is
2480 -- unchanged before because the code has used NOCOPY internally for
2481 -- some time.
2482 --
2483 if(p_fdiu_info(l_count_fdiu).usage in ('O', 'B')) then
2484 l_out_index := l_out_index + 1;
2485 p_outputs(l_out_index).value := null;
2486 end if;
2487 end loop;
2488 raise;
2489 end set_outputs;
2490
2491 /*---------------------------------------------------------------------------*/
2492 /*------------------ global functions and procedures ------------------------*/
2493 /*---------------------------------------------------------------------------*/
2494
2495 ------------------------------- reset_caches ----------------------------------
2496 /*
2497 NAME
2498 reset_caches
2499 DESCRIPTION
2500 Resets the internal caches to their initial states.
2501 NOTES
2502 */
2503 procedure reset_caches is
2504 begin
2505 g_fmla_lookups.delete;
2506 if g_inited then
2507 g_fmla_info.delete;
2508 end if;
2509 g_inited := true;
2510 g_fmla_info := fmla_info_t();
2511 g_lru_slot := 1;
2512 g_free_fdiu.delete;
2513 g_fdiu_info.delete;
2514 g_hash_info.delete;
2515 g_dbi_info.delete;
2516 g_ctx_info.delete;
2517 g_next_dbi_index := 1;
2518
2519 g_route_ctx_sums.delete;
2520 g_ctx_levels1 := 0;
2521 g_ctx_levels2 := 0;
2522
2523 --
2524 -- Set debugging profile.
2525 --
2526 check_profile_debug;
2527
2528 --
2529 -- Set the USE_FF_WRAPPER flag.
2530 --
2531 set_use_ff_wrapper;
2532
2533 --
2534 -- Set the cache size.
2535 --
2536 set_cache_size;
2537 end reset_caches;
2538
2539 ---------------------------- init_formula -------------------------------------
2540 /*
2541 NAME
2542 init_formula
2543 DESCRIPTION
2544 Initialises data structures for a specific formula.
2545 NOTES
2546 Very straight-forward. Is really a cover for the formula
2547 information cache load function.
2548
2549 As far as the user is concerned, the reason for calling
2550 this procedure is to obtain information about the inputs
2551 and outputs required by the formula.
2552 */
2553
2554 procedure init_formula
2555 (
2556 p_formula_id in number,
2557 p_effective_date in date,
2558 p_inputs in out nocopy ff_exec.inputs_t,
2559 p_outputs in out nocopy ff_exec.outputs_t
2560 ) is
2561 l_count binary_integer;
2562 l_first_fdiu binary_integer;
2563 l_last_fdiu binary_integer;
2564 l_in_index binary_integer := 0;
2565 l_out_index binary_integer := 0;
2566 l_cache_slot binary_integer;
2567 begin
2568 /* Set decimal point character. */
2569 g_decpoint := substr(to_char(1.1),2,1);
2570
2571 g_debug := hr_utility.debug_enabled;
2572 if g_debug then
2573 ff_utils.entry('init_formula');
2574 end if;
2575
2576 if g_debug then
2577 hr_utility.trace('fmla cache size: ' || g_exec_info.fmla_cache_size);
2578 end if;
2579
2580 -- Load formula information into cache if necessary.
2581 -- Global variables are passed to aid modularity.
2582 load_formula (p_formula_id, p_effective_date, g_exec_info,
2583 g_fmla_lookups, g_fmla_info, g_lru_slot,
2584 g_fdiu_info, g_ctx_info);
2585 l_cache_slot := g_exec_info.cache_slot;
2586
2587 /*
2588 * Move the inputs and outputs into the table
2589 */
2590 p_inputs.delete; -- must start with empty inputs table.
2591 p_outputs.delete; -- must start with empty outputs table.
2592
2593 -- Get parameters for loop
2594 l_first_fdiu := g_fmla_info(l_cache_slot).first_fdiu;
2595 l_last_fdiu := l_first_fdiu + g_fmla_info(l_cache_slot).fdiu_count - 1;
2596
2597 -- Loop round the appropriate fdiu rows for the formula.
2598 -- Note that the index numbers for the input and output tables
2599 -- will start from 1 and be contiguous.
2600 for l_count in l_first_fdiu..l_last_fdiu loop
2601 if (g_fdiu_info(l_count).usage in ('I', 'U', 'B')) then
2602 -- Entry for inputs table.
2603 l_in_index := l_in_index + 1;
2604 p_inputs(l_in_index).name := g_fdiu_info(l_count).name;
2605 p_inputs(l_in_index).datatype := g_fdiu_info(l_count).data_type;
2606
2607 -- Set up the class for input.
2608 if(g_fdiu_info(l_count).usage in ('I', 'B')) then
2609 p_inputs(l_in_index).class := 'INPUT';
2610 else
2611 p_inputs(l_in_index).class := 'CONTEXT';
2612 end if;
2613 end if;
2614
2615 if (g_fdiu_info(l_count).usage in ('O', 'B')) then
2616 -- Entry for output table.
2617 l_out_index := l_out_index + 1;
2618 p_outputs(l_out_index).name := g_fdiu_info(l_count).name;
2619 p_outputs(l_out_index).datatype := g_fdiu_info(l_count).data_type;
2620 end if;
2621 end loop;
2622
2623 /*
2624 * Record the number of rows in the inputs and
2625 * outputs tables. This allows the run time
2626 * system to perform a sanity check later on.
2627 */
2628 g_exec_info.input_rows := p_inputs.count;
2629 g_exec_info.output_rows := p_outputs.count;
2630
2631 if g_debug then
2632 ff_utils.exit('init_formula');
2633 end if;
2634
2635 end init_formula;
2636
2637 ------------------------------ run_formula ------------------------------------
2638 /*
2639 NAME
2640 run_formula
2641 DESCRIPTION
2642 Uses data structures built up to execute Fast Formula.
2643 NOTES
2644 <none>
2645 */
2646
2647 procedure run_formula
2648 (
2649 p_inputs in ff_exec.inputs_t,
2650 p_outputs in out nocopy ff_exec.outputs_t,
2651 p_use_dbi_cache in boolean default true
2652 ) is
2653 l_formula_id number;
2654 l_cache_slot binary_integer;
2655 l_line_number number := 0;
2656 l_err_number number := 0;
2657 l_error_message varchar2(255) := null;
2658 l_rows_processed number;
2659 l_first_fdiu binary_integer;
2660 l_last_fdiu binary_integer;
2661 l_count_fdiu binary_integer;
2662 l_fmla_name varchar2(80);
2663 l_d ff_wrapper_pkg.t_date;
2664 l_n ff_wrapper_pkg.t_number;
2665 l_t ff_wrapper_pkg.t_text;
2666 l_i ff_wrapper_pkg.t_number;
2667 begin
2668 g_debug := hr_utility.debug_enabled;
2669 if g_debug then
2670 ff_utils.entry('run_formula');
2671 end if;
2672
2673 -- Check that the execution engine is initialised.
2674 if(g_exec_info.formula_id is null) then
2675 hr_utility.set_message(801, 'FFPLX03_NO_INIT');
2676 hr_utility.raise_error;
2677 end if;
2678
2679 -- Sanity checks on the number of rows in the
2680 -- inputs and outputs tables. This helps to
2681 -- ensure the caller hasn't messed with them.
2682 ff_utils.assert((g_exec_info.input_rows = p_inputs.count), 'run_formula:1');
2683 ff_utils.assert((g_exec_info.output_rows = p_outputs.count), 'run_formula:2');
2684
2685 /*
2686 * Set up a few useful variables.
2687 */
2688 l_cache_slot := g_exec_info.cache_slot;
2689 l_fmla_name := g_fmla_info(l_cache_slot).formula_name;
2690
2691 -- Show the inputs.
2692 if g_debug then
2693 io_table_debug(p_inputs, p_outputs, 'INPUT');
2694 end if;
2695
2696 -- DBI cache debug.
2697 if g_debug then
2698 dbi_cache_debug(g_ctx_info, g_dbi_info, g_hash_info);
2699 end if;
2700
2701 /*
2702 * Call routine to bind variables from user supplied info.
2703 */
2704 bind_variables(l_cache_slot, p_inputs, p_outputs, g_fmla_info,
2705 g_fdiu_info, g_ctx_info, l_d, l_n, l_t, l_i,
2706 p_use_dbi_cache);
2707
2708 /*
2709 * Execute PLSQL block.
2710 */
2711
2712 -- Actually run the formula.
2713 -- Use local exception block to catch any oracle error.
2714 if g_exec_info.use_ff_wrapper then
2715 begin
2716 ff_wrapper_main_pkg.formula
2717 (p_formula_name => l_fmla_name
2718 ,p_ff_package_name => g_fmla_info(l_cache_slot).package_name
2719 ,p_d => l_d
2720 ,p_n => l_n
2721 ,p_t => l_t
2722 ,p_i => l_i
2723 ,p_fferln => l_line_number
2724 ,p_ffercd => l_err_number
2725 ,p_ffermt => l_error_message
2726 );
2727 exception
2728 when CANNOT_FIND_PROG_UNIT then
2729 -- Wrapper package body needs to be regenerated.
2730 hr_utility.set_message(802, 'FF_33186_GENERATE_WRAPPER');
2731 hr_utility.set_message_token('1', l_fmla_name);
2732 hr_utility.raise_error;
2733
2734 when others then
2735 -- Getting an unhandled exception from PLSQL.
2736 hr_utility.set_message(801, 'FFX18_PLSQL_ERROR');
2737 hr_utility.set_message_token('1', l_fmla_name);
2738 hr_utility.set_message_token('2', sqlerrm);
2739 hr_utility.raise_error;
2740
2741 end;
2742 else
2743 begin
2744 ff_wrapper_pkg.g_d := l_d;
2745 ff_wrapper_pkg.g_i := l_i;
2746 ff_wrapper_pkg.g_n := l_n;
2747 ff_wrapper_pkg.g_t := l_t;
2748 ff_wrapper_pkg.g_fferln := null;
2749 ff_wrapper_pkg.g_ffercd := null;
2750 ff_wrapper_pkg.g_ffermt := null;
2751 execute immediate
2752 'begin ' || g_fmla_info(l_cache_slot).package_name || '.formula; end;';
2753 exception
2754 when bad_plsql then
2755 hr_utility.set_message(801,'FFX22J_FORMULA_NOT_FOUND');
2756 hr_utility.set_message_token('1',l_fmla_name);
2757 hr_utility.raise_error;
2758
2759 when others then
2760 -- Getting an unhandled exception from PLSQL.
2761 hr_utility.set_message(801, 'FFX18_PLSQL_ERROR');
2762 hr_utility.set_message_token('1', l_fmla_name);
2763 hr_utility.set_message_token('2', sqlerrm);
2764 hr_utility.raise_error;
2765 end;
2766 --
2767 l_d := ff_wrapper_pkg.g_d;
2768 l_i := ff_wrapper_pkg.g_i;
2769 l_n := ff_wrapper_pkg.g_n;
2770 l_t := ff_wrapper_pkg.g_t;
2771 l_line_number := ff_wrapper_pkg.g_fferln;
2772 l_err_number := ff_wrapper_pkg.g_ffercd;
2773 l_error_message := ff_wrapper_pkg.g_ffermt;
2774 end if;
2775
2776 /*
2777 * Check for specific error conditions.
2778 */
2779 if(l_err_number > 0) then
2780
2781 -- Precise error detected in plsql, set up tokens.
2782 if(l_err_number = 1) then
2783 hr_utility.set_message(801, 'FFX00_LOCAL_NOT_INITIALIZED');
2784 elsif(l_err_number = 2) then
2785 hr_utility.set_message(801, 'FFX00_ZERO_DIVISION');
2786 elsif(l_err_number = 3) then
2787 hr_utility.set_message(801, 'FFX00_DATA_NOT_FOUND');
2788 elsif(l_err_number = 4) then
2789 hr_utility.set_message(801, 'FFX00_TOO_MANY_ROWS');
2790 elsif(l_err_number = 5) then
2791 hr_utility.set_message(801, 'FFX00_VALUE_RANGE_ERROR');
2792 elsif(l_err_number = 6) then
2793 hr_utility.set_message(801, 'FFX00_INVALID_NUMBER');
2794 elsif(l_err_number = 7) then
2795 hr_utility.set_message(801, 'FFX00_NULL_VALUE');
2796 elsif(l_err_number = 8) then
2797 hr_utility.set_message(801, 'FFX00_UDF_ERROR');
2798 else
2799 hr_utility.set_message(801, 'FFPLX02_UNKNOWN_PLSQL_ERR');
2800 hr_utility.set_message_token('ERROR', l_err_number);
2801 hr_utility.raise_error;
2802 end if;
2803
2804 hr_utility.set_message_token('1', l_fmla_name);
2805 hr_utility.set_message_token('2', l_line_number);
2806 hr_utility.set_message_token('3', l_error_message);
2807 hr_utility.raise_error;
2808
2809 elsif(l_err_number < 0) then
2810
2811 -- Oracle error has been trapped by Formula.
2812 hr_utility.set_message(801, 'FFX18_ORA_PLSQL');
2813 hr_utility.set_message_token('1', l_fmla_name);
2814 hr_utility.set_message_token('2', l_err_number);
2815 hr_utility.set_message_token('3', l_line_number);
2816 hr_utility.raise_error;
2817
2818 end if;
2819
2820 /*
2821 * Set values in the outputs table and
2822 * write to db item cache if necessary.
2823 */
2824 set_outputs
2825 ( l_cache_slot, g_fmla_info, g_fdiu_info, l_d, l_n, l_t, l_i, p_outputs);
2826
2827 -- Show the contents of inputs and outputs table.
2828 if g_debug then
2829 io_table_debug(p_inputs, p_outputs, 'OUTPUT');
2830 end if;
2831
2832 /*
2833 * Finished!
2834 * Now the user should have all his values in the outputs
2835 * table and be able to process them.
2836 */
2837
2838 if g_debug then
2839 ff_utils.exit('run_formula');
2840 end if;
2841
2842 end run_formula;
2843
2844 /*
2845 * Global initialisation section.
2846 */
2847 begin
2848 --
2849 -- Set the formula caches to their initial values.
2850 --
2851 reset_caches;
2852 end ff_exec;
[Home] [Help]