[Home] [Help]
PACKAGE BODY: APPS.FF_FORMULA_WEBUI_PKG
Source
1 package body FF_FORMULA_WEBUI_PKG as
2 /* $Header: fffwebpk.pkb 120.2 2006/05/26 16:08:42 swinton noship $ */
3 --
4 g_debug boolean := hr_utility.debug_enabled;
5 --
6 procedure generate_unique_formula_name(
7 p_formula_type_id in varchar2,
8 p_business_group_id in number,
9 p_legislation_code in varchar2,
10 p_formula_name out nocopy varchar2
11 )
12 is
13 --
14 l_tmp_name varchar2(80);
15 l_startup_mode varchar2(10) := ffstup.get_mode (p_business_group_id,p_legislation_code);
16 l_dummy varchar2(1);
17 --
18 begin
19 --
20 if g_debug then
21 hr_utility.set_location('Entering ff_formula_webui_pkg.generate_unique_formula_name',10);
22 end if;
23 --
24 -- Loop until we have a unique name
25 loop
26 --
27 begin
28 --
29 -- Generate a temporary formula name based on the systimestamp
30 l_tmp_name := 'FF_TMP_'||to_char(systimestamp,'JHH24MISSFF6');
31 --
32 -- Now check the formula name is unique
33 -- It is highly unlikely that this name will clash, but we check
34 -- just to be on the safe side
35 select null into l_dummy from dual where exists
36 (select null
37 from ff_formulas_f a
38 where a.formula_name = l_tmp_name
39 and a.formula_type_id = p_formula_type_id
40 and
41 ( l_startup_mode = 'MASTER'
42 or
43 ( l_startup_mode = 'SEED'
44 and
45 ( a.legislation_code = p_legislation_code
46 or
47 (a.legislation_code is null and a.business_group_id is null)
48 or
49 p_legislation_code =
50 (select b.legislation_code
51 from per_business_groups_perf b
52 where b.business_group_id = a.business_group_id)
53 )
54 )
55 or
56 ( l_startup_mode = 'NON-SEED'
57 and
58 ( a.business_group_id = p_business_group_id
59 or
60 (a.legislation_code is null and a.business_group_id is null)
61 or
62 (a.business_group_id is null and a.legislation_code = p_legislation_code)
63 )
64 )
65 ));
66 -- No error was raised, so l_tmp_name must clash with an existing
67 -- name
68 -- Keep looping until we have a unique name
69 -- l_tmp_name is updated at the start of each loop iteration
70 --
71 exception
72 when no_data_found then
73 -- No data found, so l_tmp_name is unique
74 -- Exit the loop
75 exit;
76 end;
77 end loop;
78 --
79 if g_debug then
80 hr_utility.trace('p_formula_name: '||p_formula_name);
81 hr_utility.set_location('Leaving ff_formula_webui_pkg.generate_unique_formula_name',30);
82 end if;
83 --
84 p_formula_name := l_tmp_name;
85 --
86 end generate_unique_formula_name;
87 --
88 procedure validate_formula_name(
89 p_formula_name in out nocopy varchar2,
90 p_formula_type_id in number,
91 p_business_group_id in number,
92 p_legislation_code in varchar2,
93 p_effective_start_date in date,
94 p_effective_end_date in out nocopy date,
95 p_return_status out nocopy varchar2
96 )
97 is
98 --
99 l_formula_name varchar2(80);
100 l_formula_type_id number(9);
101 l_business_group_id number(15);
102 l_legislation_code varchar2(30);
103 l_effective_start_date date;
104 l_effective_end_date date;
105 --
106 begin
107 --
108 if g_debug then
109 hr_utility.set_location('Entering ff_formula_webui_pkg.validate_formula_name',10);
110 hr_utility.trace('p_formula_name: '||p_formula_name);
111 hr_utility.trace('p_formula_type_id: '||to_char(p_formula_type_id));
112 hr_utility.trace('p_business_group_id: '||to_char(p_business_group_id));
113 hr_utility.trace('p_legislation_code: '||p_legislation_code);
114 hr_utility.trace('p_effective_start_date: '||fnd_date.date_to_canonical(p_effective_start_date));
115 hr_utility.trace('p_effective_end_date: '||fnd_date.date_to_canonical(p_effective_end_date));
116 end if;
117 --
118 l_formula_name := p_formula_name;
119 l_formula_type_id := p_formula_type_id;
120 l_business_group_id := p_business_group_id;
121 l_legislation_code := p_legislation_code;
122 l_effective_start_date := p_effective_start_date;
123 l_effective_end_date := p_effective_end_date;
124 --
125 -- Enable multi-messaging
126 hr_multi_message.enable_message_list;
127 --
128 ffdict.validate_formula(
129 p_formula_name => l_formula_name,
130 p_formula_type_id => l_formula_type_id,
131 p_bus_grp => l_business_group_id,
132 p_leg_code => l_legislation_code,
133 p_effective_start_date => l_effective_start_date,
134 p_effective_end_date => l_effective_end_date
135 );
136 --
137 -- Set OUT parameters
138 p_formula_name := l_formula_name;
139 p_effective_end_date := l_effective_end_date;
140 --
141 if g_debug then
142 hr_utility.set_location('Leaving ff_formula_webui_pkg.validate_formula_name',20);
143 end if;
144 --
145 -- Get the return status and disable multi-messaging
146 p_return_status := hr_multi_message.get_return_status_disable;
147 --
148 exception
149 --
150 when hr_multi_message.error_message_exist then
151 p_return_status := hr_multi_message.get_return_status_disable;
152 --
153 when others then
154 hr_multi_message.add;
155 p_return_status := hr_multi_message.get_return_status_disable;
156 --
157 end validate_formula_name;
158 --
159 procedure insert_formula(
160 p_rowid in out nocopy varchar2,
161 p_formula_id in out nocopy varchar2,
162 p_effective_start_date in date,
163 p_effective_end_date in date,
164 p_business_group_id in number,
165 p_legislation_code in varchar2,
166 p_formula_type_id in varchar2,
167 p_formula_name in out nocopy varchar2,
168 p_description in varchar2,
169 p_formula_text in long,
170 p_sticky_flag in varchar2,
171 p_last_update_date in out nocopy date,
172 p_return_status out nocopy varchar2
173 )
174 is
175 --
176 begin
177 --
178 if g_debug then
179 hr_utility.set_location('Entering ff_formula_webui_pkg.insert_formula',10);
180 end if;
181 --
182 -- Enable multi-messaging
183 hr_multi_message.enable_message_list;
184 --
185 ff_formulas_f_pkg.insert_row(
186 x_rowid => p_rowid,
187 x_formula_id => p_formula_id,
188 x_effective_start_date => p_effective_start_date,
189 x_effective_end_date => p_effective_end_date,
190 x_business_group_id => p_business_group_id,
191 x_legislation_code => p_legislation_code,
192 x_formula_type_id => p_formula_type_id,
193 x_formula_name => p_formula_name,
194 x_description => p_description,
195 --x_formula_text => l_long_formula_text,
196 x_formula_text => p_formula_text,
197 --x_sticky_flag => p_sticky_flag,
198 x_sticky_flag => 'Y',
199 x_last_update_date => p_last_update_date
200 );
201 --
202 if g_debug then
203 hr_utility.set_location('Leaving ff_formula_webui_pkg.insert_formula',20);
204 end if;
205 --
206 -- Get the return status and disable multi-messaging
207 p_return_status := hr_multi_message.get_return_status_disable;
208 --
209 exception
210 --
211 when hr_multi_message.error_message_exist then
212 p_return_status := hr_multi_message.get_return_status_disable;
213 --
214 when others then
215 hr_multi_message.add;
216 p_return_status := hr_multi_message.get_return_status_disable;
217 --
218 end insert_formula;
219 --
220 procedure update_formula(
221 p_rowid in varchar2,
222 p_formula_id in number,
223 p_effective_start_date in date,
224 p_effective_end_date in date,
225 p_business_group_id in number,
226 p_legislation_code in varchar2,
227 p_formula_type_id in varchar2,
228 p_formula_name in varchar2,
229 p_description in varchar2,
230 p_formula_text in long,
231 p_sticky_flag in varchar2,
232 p_last_update_date in out nocopy date,
233 p_return_status out nocopy varchar2
234 )
235 is
236 --
237 begin
238 --
239 if g_debug then
240 hr_utility.set_location('Entering ff_formula_webui_pkg.update_formula',10);
241 end if;
242 --
243 -- Enable multi-messaging
244 hr_multi_message.enable_message_list;
245 --
246 ff_formulas_f_pkg.update_row(
247 x_rowid => p_rowid,
248 x_formula_id => p_formula_id,
249 x_effective_start_date => p_effective_start_date,
250 x_effective_end_date => p_effective_end_date,
251 x_business_group_id => p_business_group_id,
252 x_legislation_code => p_legislation_code,
253 x_formula_type_id => p_formula_type_id,
254 x_formula_name => p_formula_name,
255 x_description => p_description,
256 --x_formula_text => l_long_formula_text,
257 x_formula_text => p_formula_text,
258 x_sticky_flag => p_sticky_flag,
259 x_last_update_date => p_last_update_date
260 );
261 --
262 if g_debug then
263 hr_utility.set_location('Leaving ff_formula_webui_pkg.update_formula',20);
264 end if;
265 --
266 -- Get the return status and disable multi-messaging
267 p_return_status := hr_multi_message.get_return_status_disable;
268 --
269 exception
270 --
271 when hr_multi_message.error_message_exist then
272 p_return_status := hr_multi_message.get_return_status_disable;
273 --
274 when others then
275 hr_multi_message.add;
276 p_return_status := hr_multi_message.get_return_status_disable;
277 --
278 end update_formula;
279 --
280 procedure delete_formula(
281 p_rowid in varchar2,
282 p_formula_id in number,
283 p_dt_delete_mode in varchar2,
284 p_validation_start_date in date,
285 p_validation_end_date in date,
286 p_effective_date in date,
287 p_return_status out nocopy varchar2
288 )
289 is
290 begin
291 --
292 if g_debug then
293 hr_utility.set_location('Entering ff_formula_webui_pkg.delete_formula',10);
294 end if;
295 --
296 -- Enable multi-messaging
297 hr_multi_message.enable_message_list;
298 --
299 ff_formulas_f_pkg.delete_row(
300 x_rowid => p_rowid,
301 x_formula_id => p_formula_id,
302 x_dt_delete_mode => p_dt_delete_mode,
303 x_validation_start_date => p_validation_start_date,
304 x_validation_end_date => p_validation_end_date,
305 x_effective_date => p_effective_date
306 );
307 --
308 if g_debug then
309 hr_utility.set_location('Leaving ff_formula_webui_pkg.delete_formula',20);
310 end if;
311 --
312 -- Get the return status and disable multi-messaging
313 p_return_status := hr_multi_message.get_return_status_disable;
314 --
315 exception
316 --
317 when hr_multi_message.error_message_exist then
318 p_return_status := hr_multi_message.get_return_status_disable;
319 --
320 when others then
321 hr_multi_message.add;
322 p_return_status := hr_multi_message.get_return_status_disable;
323 --
324 end delete_formula;
325 --
326 procedure lock_formula(
327 p_rowid in varchar2,
328 p_last_update_date in date,
329 p_return_status out nocopy varchar2
330 )
331 is
332 begin
333 --
334 if g_debug then
335 hr_utility.set_location('Entering ff_formula_webui_pkg.lock_formula',10);
336 end if;
337 --
338 -- Enable multi-messaging
339 hr_multi_message.enable_message_list;
340 --
341 ff_formulas_f_pkg.lock_row(
342 x_rowid => p_rowid,
343 x_last_update_date => p_last_update_date
344 );
345 --
346 if g_debug then
347 hr_utility.set_location('Leaving ff_formula_webui_pkg.lock_formula',20);
348 end if;
349 --
350 -- Get the return status and disable multi-messaging
351 p_return_status := hr_multi_message.get_return_status_disable;
352 --
353 exception
354 --
355 when hr_multi_message.error_message_exist then
356 p_return_status := hr_multi_message.get_return_status_disable;
357 --
358 when others then
359 hr_multi_message.add;
360 p_return_status := hr_multi_message.get_return_status_disable;
361 --
362 end lock_formula;
363 --
364 procedure compile_formula(
365 p_formula_id in number,
366 p_effective_date in date,
367 p_outcome out nocopy varchar2,
368 p_message out nocopy varchar2
369 )
370 is
371 --
372 l_retval number;
373 l_timeout number := 120;
374 l_outcome varchar2(30);
375 l_message varchar2(240);
376 --
377 begin
378 --
379 if g_debug then
380 hr_utility.set_location('Entering ff_formula_webui_pkg.compile_formula',10);
381 end if;
382 --
383 l_retval := fnd_transaction.synchronous(
384 timeout => l_timeout,
385 outcome => l_outcome,
386 message => l_message,
387 application => 'FF',
388 program => 'FFTMSINGLECOMPILE',
389 arg_1 => to_char(p_formula_id),
390 arg_2 => fnd_date.date_to_canonical(p_effective_date)
391 );
392 --
393 -- Return values are either 0, 1, 2 or 3
394 -- 0 Indicates success - although formula compilation may have failed
395 -- 1 Indicates timeout error
396 -- 2 Indicates no transaction manager available
397 -- 3 Indicates some other error
398 if l_retval <> 0 then
399 --
400 if l_retval = 1 then
401 --
402 -- Timeout error
403 hr_utility.set_message(802, 'FF_WEB_TX_MGR_TIMEOUT_ERROR');
404 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
405 hr_utility.raise_error;
406 --
407 elsif l_retval = 2 then
408 --
409 -- No transaction manager error
410 hr_utility.set_message(802, 'FF_WEB_NO_TX_MGR_ERROR');
411 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
412 hr_utility.raise_error;
413 --
414 elsif l_retval = 3 then
415 --
416 -- Generic error
417 hr_utility.set_message(802, 'FF_WEB_GENERIC_TX_MGR_ERROR');
418 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
419 hr_utility.raise_error;
420 --
421 end if;
422 elsif l_outcome = 'SUCCESS' then
423 -- Formula compilation was successful
424 p_outcome := 'S';
425 else
426 -- Formula compilation error
427 p_outcome := 'E';
428 p_message := l_message;
429 end if;
430 --
431 if g_debug then
432 hr_utility.set_location('Leaving ff_formula_webui_pkg.compile_formula',20);
433 end if;
434 --
435 exception
436 --
437 when others then raise;
438 --
439 end compile_formula;
440 --
441 procedure do_autonomous_insert(
442 p_rowid in out nocopy varchar2,
443 p_formula_id in out nocopy number,
444 p_effective_start_date date,
445 p_effective_end_date date,
446 p_business_group_id number,
447 p_legislation_code varchar2,
448 p_formula_type_id number,
449 p_formula_name in out nocopy varchar2,
450 p_formula_text varchar2,
451 p_last_update_date in out nocopy date
452 )
453 is
454 --
455 pragma autonomous_transaction;
456 --
457 begin
458 --
459 ff_formulas_f_pkg.insert_row (
460 x_rowid => p_rowid,
461 x_formula_id => p_formula_id,
462 x_effective_start_date => p_effective_start_date,
463 x_effective_end_date => p_effective_end_date,
464 x_business_group_id => p_business_group_id,
465 x_legislation_code => p_legislation_code,
466 x_formula_type_id => p_formula_type_id,
467 x_formula_name => p_formula_name,
468 x_description => null,
469 x_formula_text => p_formula_text,
470 x_sticky_flag => 'Y',
471 x_last_update_date => p_last_update_date
472 );
473 --
474 commit;
475 --
476 end do_autonomous_insert;
477 --
478 procedure do_autonomous_delete(
479 p_rowid varchar2,
480 p_formula_id number,
481 p_effective_start_date date,
482 p_effective_end_date date
483 )
484 is
485 --
486 pragma autonomous_transaction;
487 --
488 begin
489 --
490 ff_formulas_f_pkg.delete_row(
491 x_rowid => p_rowid,
492 x_formula_id => p_formula_id,
493 x_dt_delete_mode => 'ZAP',
494 x_validation_start_date => fnd_date.canonical_to_date('0001/01/01'),
495 x_validation_end_date => p_effective_end_date,
496 x_effective_date => p_effective_start_date
497 );
498 --
499 commit;
500 --
501 end do_autonomous_delete;
502 --
503 procedure compile_formula_autonomously(
504 p_formula_type_id in number,
505 p_effective_start_date in date,
506 p_effective_end_date in date,
507 p_business_group_id in number,
508 p_legislation_code in varchar2,
509 p_formula_text in long,
510 p_outcome out nocopy varchar2,
511 p_message out nocopy varchar2,
512 p_return_status out nocopy varchar2
513 )
514 is
515 --
516 l_rowid varchar2(240);
517 l_formula_id number;
518 l_formula_name varchar2(80);
519 l_last_update_date date;
520 --
521 begin
522 --
523 if g_debug then
524 hr_utility.set_location('Entering ff_formula_webui_pkg.compile_formula_autonomously',10);
525 end if;
526 --
527 -- Enable multi-messaging
528 hr_multi_message.enable_message_list;
529 --
530 -- Generate a unique formula name for this formula type, business group
531 -- and legislation
532 generate_unique_formula_name(
533 p_formula_type_id => p_formula_type_id,
534 p_business_group_id => p_business_group_id,
535 p_legislation_code => p_legislation_code,
536 p_formula_name => l_formula_name
537 );
538 --
539 -- insert the formula
540 do_autonomous_insert (
541 p_rowid => l_rowid,
542 p_formula_id => l_formula_id,
543 p_effective_start_date => p_effective_start_date,
544 p_effective_end_date => p_effective_end_date,
545 p_business_group_id => p_business_group_id,
546 p_legislation_code => p_legislation_code,
547 p_formula_type_id => p_formula_type_id,
548 p_formula_name => l_formula_name,
549 p_formula_text => p_formula_text,
550 p_last_update_date => l_last_update_date
551 );
552 -- do the compile
553 compile_formula(
554 p_formula_id => l_formula_id,
555 p_effective_date => p_effective_start_date,
556 p_outcome => p_outcome,
557 p_message => p_message
558 );
559 -- delete the formula
560 do_autonomous_delete(
561 p_rowid => l_rowid,
562 p_formula_id => l_formula_id,
563 p_effective_start_date => p_effective_start_date,
564 p_effective_end_date => p_effective_end_date
565 );
566 --
567 if g_debug then
568 hr_utility.set_location('Leaving ff_formula_webui_pkg.compile_formula_autonomously',20);
569 end if;
570 --
571 -- Get the return status and disable multi-messaging
572 p_return_status := hr_multi_message.get_return_status_disable;
573 --
574 exception
575 --
576 when hr_multi_message.error_message_exist then
577 p_return_status := hr_multi_message.get_return_status_disable;
578 --
579 when others then raise;
580 --hr_multi_message.add;
581 --p_return_status := hr_multi_message.get_return_status_disable;
582 --
583 end compile_formula_autonomously;
584 --
585 procedure run_formula(
586 p_formula_id in number,
587 p_session_date in date,
588 p_input_name1 in varchar2,
589 p_input_name2 in varchar2,
590 p_input_name3 in varchar2,
591 p_input_name4 in varchar2,
592 p_input_name5 in varchar2,
593 p_input_name6 in varchar2,
594 p_input_name7 in varchar2,
595 p_input_name8 in varchar2,
596 p_input_name9 in varchar2,
597 p_input_name10 in varchar2,
598 p_input_name11 in varchar2,
599 p_input_name12 in varchar2,
600 p_input_name13 in varchar2,
601 p_input_name14 in varchar2,
602 p_input_name15 in varchar2,
603 p_input_name16 in varchar2,
604 p_input_name17 in varchar2,
605 p_input_name18 in varchar2,
606 p_input_name19 in varchar2,
607 p_input_name20 in varchar2,
608 p_input_name21 in varchar2,
609 p_input_name22 in varchar2,
610 p_input_name23 in varchar2,
611 p_input_name24 in varchar2,
612 p_input_name25 in varchar2,
613 p_input_name26 in varchar2,
614 p_input_name27 in varchar2,
615 p_input_name28 in varchar2,
616 p_input_name29 in varchar2,
617 p_input_name30 in varchar2,
618 p_input_value1 in varchar2,
619 p_input_value2 in varchar2,
620 p_input_value3 in varchar2,
621 p_input_value4 in varchar2,
622 p_input_value5 in varchar2,
623 p_input_value6 in varchar2,
624 p_input_value7 in varchar2,
625 p_input_value8 in varchar2,
626 p_input_value9 in varchar2,
627 p_input_value10 in varchar2,
628 p_input_value11 in varchar2,
629 p_input_value12 in varchar2,
630 p_input_value13 in varchar2,
631 p_input_value14 in varchar2,
632 p_input_value15 in varchar2,
633 p_input_value16 in varchar2,
634 p_input_value17 in varchar2,
635 p_input_value18 in varchar2,
636 p_input_value19 in varchar2,
637 p_input_value20 in varchar2,
638 p_input_value21 in varchar2,
639 p_input_value22 in varchar2,
640 p_input_value23 in varchar2,
641 p_input_value24 in varchar2,
642 p_input_value25 in varchar2,
643 p_input_value26 in varchar2,
644 p_input_value27 in varchar2,
645 p_input_value28 in varchar2,
646 p_input_value29 in varchar2,
647 p_input_value30 in varchar2,
648 p_output_name1 in out nocopy varchar2,
649 p_output_name2 in out nocopy varchar2,
650 p_output_name3 in out nocopy varchar2,
651 p_output_name4 in out nocopy varchar2,
652 p_output_name5 in out nocopy varchar2,
653 p_output_name6 in out nocopy varchar2,
654 p_output_name7 in out nocopy varchar2,
655 p_output_name8 in out nocopy varchar2,
656 p_output_name9 in out nocopy varchar2,
657 p_output_name10 in out nocopy varchar2,
658 p_output_name11 in out nocopy varchar2,
659 p_output_name12 in out nocopy varchar2,
660 p_output_name13 in out nocopy varchar2,
661 p_output_name14 in out nocopy varchar2,
662 p_output_name15 in out nocopy varchar2,
663 p_output_name16 in out nocopy varchar2,
664 p_output_name17 in out nocopy varchar2,
665 p_output_name18 in out nocopy varchar2,
666 p_output_name19 in out nocopy varchar2,
667 p_output_name20 in out nocopy varchar2,
668 p_output_name21 in out nocopy varchar2,
669 p_output_name22 in out nocopy varchar2,
670 p_output_name23 in out nocopy varchar2,
671 p_output_name24 in out nocopy varchar2,
672 p_output_name25 in out nocopy varchar2,
673 p_output_name26 in out nocopy varchar2,
674 p_output_name27 in out nocopy varchar2,
675 p_output_name28 in out nocopy varchar2,
676 p_output_name29 in out nocopy varchar2,
677 p_output_name30 in out nocopy varchar2,
678 p_output_value1 out nocopy varchar2,
679 p_output_value2 out nocopy varchar2,
680 p_output_value3 out nocopy varchar2,
681 p_output_value4 out nocopy varchar2,
682 p_output_value5 out nocopy varchar2,
683 p_output_value6 out nocopy varchar2,
684 p_output_value7 out nocopy varchar2,
685 p_output_value8 out nocopy varchar2,
686 p_output_value9 out nocopy varchar2,
687 p_output_value10 out nocopy varchar2,
688 p_output_value11 out nocopy varchar2,
689 p_output_value12 out nocopy varchar2,
690 p_output_value13 out nocopy varchar2,
691 p_output_value14 out nocopy varchar2,
692 p_output_value15 out nocopy varchar2,
693 p_output_value16 out nocopy varchar2,
694 p_output_value17 out nocopy varchar2,
695 p_output_value18 out nocopy varchar2,
696 p_output_value19 out nocopy varchar2,
697 p_output_value20 out nocopy varchar2,
698 p_output_value21 out nocopy varchar2,
699 p_output_value22 out nocopy varchar2,
700 p_output_value23 out nocopy varchar2,
701 p_output_value24 out nocopy varchar2,
702 p_output_value25 out nocopy varchar2,
703 p_output_value26 out nocopy varchar2,
704 p_output_value27 out nocopy varchar2,
705 p_output_value28 out nocopy varchar2,
706 p_output_value29 out nocopy varchar2,
707 p_output_value30 out nocopy varchar2,
708 p_return_status out nocopy varchar2
709 )
710 is
711 --
712 v_inputs ff_exec.inputs_t;
713 v_outputs ff_exec.outputs_t;
714 v_output_name varchar2(240);
715 --
716 begin
717 --
718 if g_debug then
719 hr_utility.set_location('Entering ff_formula_webui_pkg.run_formula',10);
720 end if;
721 --
722 -- Enable multi-messaging
723 hr_multi_message.enable_message_list;
724 --
725 ff_exec.init_formula(p_formula_id, p_session_date, v_inputs, v_outputs);
726 --
727 -- Check to see if any errors occurred, if so the
728 -- hr_multi_message.error_message_exist exception will be raised
729 hr_multi_message.end_validation_set;
730 --
731 -- Set up the inputs and contexts to formula.
732 for i in v_inputs.first..v_inputs.last loop
733 --
734 if upper(v_inputs(i).name) = upper(p_input_name1) then
735 v_inputs(i).value := p_input_value1;
736 elsif upper(v_inputs(i).name) = upper(p_input_name2) then
737 v_inputs(i).value := p_input_value2;
738 elsif upper(v_inputs(i).name) = upper(p_input_name3) then
739 v_inputs(i).value := p_input_value3;
740 elsif upper(v_inputs(i).name) = upper(p_input_name4) then
741 v_inputs(i).value := p_input_value4;
742 elsif upper(v_inputs(i).name) = upper(p_input_name5) then
743 v_inputs(i).value := p_input_value5;
744 elsif upper(v_inputs(i).name) = upper(p_input_name6) then
745 v_inputs(i).value := p_input_value6;
746 elsif upper(v_inputs(i).name) = upper(p_input_name7) then
747 v_inputs(i).value := p_input_value7;
748 elsif upper(v_inputs(i).name) = upper(p_input_name8) then
749 v_inputs(i).value := p_input_value8;
750 elsif upper(v_inputs(i).name) = upper(p_input_name9) then
751 v_inputs(i).value := p_input_value9;
752 elsif upper(v_inputs(i).name) = upper(p_input_name10) then
753 v_inputs(i).value := p_input_value10;
754 elsif upper(v_inputs(i).name) = upper(p_input_name11) then
755 v_inputs(i).value := p_input_value11;
756 elsif upper(v_inputs(i).name) = upper(p_input_name12) then
757 v_inputs(i).value := p_input_value12;
758 elsif upper(v_inputs(i).name) = upper(p_input_name13) then
759 v_inputs(i).value := p_input_value13;
760 elsif upper(v_inputs(i).name) = upper(p_input_name14) then
761 v_inputs(i).value := p_input_value14;
762 elsif upper(v_inputs(i).name) = upper(p_input_name15) then
763 v_inputs(i).value := p_input_value15;
764 elsif upper(v_inputs(i).name) = upper(p_input_name16) then
765 v_inputs(i).value := p_input_value16;
766 elsif upper(v_inputs(i).name) = upper(p_input_name17) then
767 v_inputs(i).value := p_input_value17;
768 elsif upper(v_inputs(i).name) = upper(p_input_name18) then
769 v_inputs(i).value := p_input_value18;
770 elsif upper(v_inputs(i).name) = upper(p_input_name19) then
771 v_inputs(i).value := p_input_value19;
772 elsif upper(v_inputs(i).name) = upper(p_input_name20) then
773 v_inputs(i).value := p_input_value20;
774 elsif upper(v_inputs(i).name) = upper(p_input_name21) then
775 v_inputs(i).value := p_input_value21;
776 elsif upper(v_inputs(i).name) = upper(p_input_name22) then
777 v_inputs(i).value := p_input_value22;
778 elsif upper(v_inputs(i).name) = upper(p_input_name23) then
779 v_inputs(i).value := p_input_value23;
780 elsif upper(v_inputs(i).name) = upper(p_input_name24) then
781 v_inputs(i).value := p_input_value24;
782 elsif upper(v_inputs(i).name) = upper(p_input_name25) then
783 v_inputs(i).value := p_input_value25;
784 elsif upper(v_inputs(i).name) = upper(p_input_name26) then
785 v_inputs(i).value := p_input_value26;
786 elsif upper(v_inputs(i).name) = upper(p_input_name27) then
787 v_inputs(i).value := p_input_value27;
788 elsif upper(v_inputs(i).name) = upper(p_input_name28) then
789 v_inputs(i).value := p_input_value28;
790 elsif upper(v_inputs(i).name) = upper(p_input_name29) then
791 v_inputs(i).value := p_input_value29;
792 elsif upper(v_inputs(i).name) = upper(p_input_name30) then
793 v_inputs(i).value := p_input_value30;
794 else
795 -- Input name not recognized
796 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
797 hr_utility.set_message_token('PROCEDURE',
798 'FF_FORMULA_WEBUI_PKG.run_formula');
799 hr_utility.set_message_token('STEP','10');
800 if hr_multi_message.exception_add then
801 hr_utility.raise_error;
802 end if;
803 end if;
804 --
805 end loop;
806 --
807 ff_exec.run_formula(v_inputs, v_outputs);
808 --
809 -- Now obtain the return values.
810 for i in v_outputs.first..v_outputs.last loop
811 --
812 v_output_name := upper(v_outputs(i).name);
813 --
814 if v_output_name = upper(p_output_name1) then
815 p_output_value1 := v_outputs(i).value;
816 elsif v_output_name = upper(p_output_name2) then
817 p_output_value2 := v_outputs(i).value;
818 elsif v_output_name = upper(p_output_name3) then
819 p_output_value3 := v_outputs(i).value;
820 elsif v_output_name = upper(p_output_name4) then
821 p_output_value4 := v_outputs(i).value;
822 elsif v_output_name = upper(p_output_name5) then
823 p_output_value5 := v_outputs(i).value;
824 elsif v_output_name = upper(p_output_name6) then
825 p_output_value6 := v_outputs(i).value;
826 elsif v_output_name = upper(p_output_name7) then
827 p_output_value7 := v_outputs(i).value;
828 elsif v_output_name = upper(p_output_name8) then
829 p_output_value8 := v_outputs(i).value;
830 elsif v_output_name = upper(p_output_name9) then
831 p_output_value9 := v_outputs(i).value;
832 elsif v_output_name = upper(p_output_name10) then
833 p_output_value10 := v_outputs(i).value;
834 elsif v_output_name = upper(p_output_name11) then
835 p_output_value11 := v_outputs(i).value;
836 elsif v_output_name = upper(p_output_name12) then
837 p_output_value12 := v_outputs(i).value;
838 elsif v_output_name = upper(p_output_name13) then
839 p_output_value13 := v_outputs(i).value;
840 elsif v_output_name = upper(p_output_name14) then
841 p_output_value14 := v_outputs(i).value;
842 elsif v_output_name = upper(p_output_name15) then
843 p_output_value15 := v_outputs(i).value;
844 elsif v_output_name = upper(p_output_name16) then
845 p_output_value16 := v_outputs(i).value;
846 elsif v_output_name = upper(p_output_name17) then
847 p_output_value17 := v_outputs(i).value;
848 elsif v_output_name = upper(p_output_name18) then
849 p_output_value18 := v_outputs(i).value;
850 elsif v_output_name = upper(p_output_name19) then
851 p_output_value19 := v_outputs(i).value;
852 elsif v_output_name = upper(p_output_name20) then
853 p_output_value20 := v_outputs(i).value;
854 elsif v_output_name = upper(p_output_name21) then
855 p_output_value21 := v_outputs(i).value;
856 elsif v_output_name = upper(p_output_name22) then
857 p_output_value22 := v_outputs(i).value;
858 elsif v_output_name = upper(p_output_name23) then
859 p_output_value23 := v_outputs(i).value;
860 elsif v_output_name = upper(p_output_name24) then
861 p_output_value24 := v_outputs(i).value;
862 elsif v_output_name = upper(p_output_name25) then
863 p_output_value25 := v_outputs(i).value;
864 elsif v_output_name = upper(p_output_name26) then
865 p_output_value26 := v_outputs(i).value;
866 elsif v_output_name = upper(p_output_name27) then
867 p_output_value27 := v_outputs(i).value;
868 elsif v_output_name = upper(p_output_name28) then
869 p_output_value28 := v_outputs(i).value;
870 elsif v_output_name = upper(p_output_name29) then
871 p_output_value29 := v_outputs(i).value;
872 elsif v_output_name = upper(p_output_name30) then
873 p_output_value30 := v_outputs(i).value;
874 else
875 -- Output name not recognized
876 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
877 hr_utility.set_message_token('PROCEDURE',
878 'FF_FORMULA_WEBUI_PKG.run_formula');
879 hr_utility.set_message_token('STEP','20');
880 if hr_multi_message.exception_add then
881 hr_utility.raise_error;
882 end if;
883 end if;
884 --
885 end loop;
886 --
887 if g_debug then
888 hr_utility.set_location('Leaving ff_formula_webui_pkg.run_formula',20);
889 end if;
890 --
891 -- Get the return status and disable multi-messaging
892 p_return_status := hr_multi_message.get_return_status_disable;
893 --
894 exception
895 --
896 when hr_multi_message.error_message_exist then
897 ff_exec.reset_caches;
898 p_return_status := hr_multi_message.get_return_status_disable;
899 --
900 when others then
901 ff_exec.reset_caches;
902 hr_multi_message.add;
903 p_return_status := hr_multi_message.get_return_status_disable;
904 --
905 end run_formula;
906 --
907 function isFormulaCompiled(
908 p_formula_id in number,
909 p_effective_date in date) return varchar2
910 is
911 --
912 cursor c_formula_compiled_info is
913 select 'Y'
914 from ff_compiled_info_f
915 where formula_id = p_formula_id
916 and p_effective_date between effective_start_date and effective_end_date;
917 --
918 l_formula_is_compiled varchar2(15) := 'N';
919 --
920 begin
921 --
922 if g_debug then
923 hr_utility.set_location('Entering ff_formula_webui_pkg.isFormulaCompiled',10);
924 end if;
925 --
926 open c_formula_compiled_info;
927 fetch c_formula_compiled_info into l_formula_is_compiled;
928 close c_formula_compiled_info;
929 --
930 --
931 if g_debug then
932 hr_utility.set_location('Leaving ff_formula_webui_pkg.isFormulaCompiled',20);
933 end if;
934 return l_formula_is_compiled;
935 end isFormulaCompiled;
936 --
937 function list_function_params(
938 p_function_id in number) return varchar2
939 is
940 --
941 cursor c_fn_name (p_fn_id number) is
942 select f.name
943 from ff_functions f
944 where f.function_id = p_fn_id;
945 --
946 cursor c_fn_params (p_fn_id number) is
947 select fp.name, lu.meaning data_type
948 from ff_function_parameters fp,
949 hr_lookups lu
950 where fp.function_id = p_fn_id
951 and fp.data_type = lu.lookup_code
952 and lu.lookup_type = 'DATA_TYPE'
953 order by fp.sequence_number;
954 --
955 l_param_list varchar2(4000);
956 l_separator varchar2(5);
957 --
958 begin
959 --
960 if g_debug then
961 hr_utility.set_location('Entering ff_formula_webui_pkg.list_function_params',10);
962 end if;
963 --
964 open c_fn_name(p_function_id);
965 fetch c_fn_name into l_param_list;
966 close c_fn_name;
967 --
968 l_param_list := l_param_list || '(';
969 --
970 for param in c_fn_params(p_function_id) loop
971 --
972 l_param_list := l_param_list || l_separator || param.name ||' '|| param.data_type;
973 l_separator := ', ';
974 --
975 end loop;
976 --
977 l_param_list := l_param_list || ')';
978 --
979 if g_debug then
980 hr_utility.trace('l_param_list: '||l_param_list);
981 hr_utility.set_location('Leaving ff_formula_webui_pkg.list_function_params',20);
982 end if;
983 --
984 return l_param_list;
985 --
986 end list_function_params;
987 --
988 end FF_FORMULA_WEBUI_PKG;