[Home] [Help]
PACKAGE BODY: APPS.FF_FORMULA_WEBUI_PKG
Source
1 package body FF_FORMULA_WEBUI_PKG as
2 /* $Header: fffwebpk.pkb 120.5 2012/02/08 15:04:11 asnell ship $ */
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 clob,
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 clob,
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 --
384 -- Bug 11849712 Test if online patching is in progress, if so, block execution
385 --
386 if ad_zd.get_edition('PATCH') is not null then
387 -- an online patch is in progress, return error
388 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
389 raise_application_error ('-20000', fnd_message.get);
390 end if;
391 --
392 l_retval := fnd_transaction.synchronous(
393 timeout => l_timeout,
394 outcome => l_outcome,
395 message => l_message,
396 application => 'FF',
397 program => 'FFTMSINGLECOMPILE',
398 arg_1 => to_char(p_formula_id),
399 arg_2 => fnd_date.date_to_canonical(p_effective_date)
400 );
401 --
402 -- Return values are either 0, 1, 2 or 3
403 -- 0 Indicates success - although formula compilation may have failed
404 -- 1 Indicates timeout error
405 -- 2 Indicates no transaction manager available
406 -- 3 Indicates some other error
407 if l_retval <> 0 then
408 --
409 if l_retval = 1 then
410 --
411 -- Timeout error
412 hr_utility.set_message(802, 'FF_WEB_TX_MGR_TIMEOUT_ERROR');
413 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
414 hr_utility.raise_error;
415 --
416 elsif l_retval = 2 then
417 --
418 -- No transaction manager error
419 hr_utility.set_message(802, 'FF_WEB_NO_TX_MGR_ERROR');
420 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
421 hr_utility.raise_error;
422 --
423 elsif l_retval = 3 then
424 --
425 -- Generic error
426 hr_utility.set_message(802, 'FF_WEB_GENERIC_TX_MGR_ERROR');
427 hr_utility.set_message_token(802, 'ERROR_MESSAGE', l_message);
428 hr_utility.raise_error;
429 --
430 end if;
431 elsif l_outcome = 'SUCCESS' then
432 -- Formula compilation was successful
433 p_outcome := 'S';
434 else
435 -- Formula compilation error
436 p_outcome := 'E';
437 p_message := l_message;
438 end if;
439 --
440 if g_debug then
441 hr_utility.set_location('Leaving ff_formula_webui_pkg.compile_formula',20);
442 end if;
443 --
444 exception
445 --
446 when others then raise;
447 --
448 end compile_formula;
449 --
450 procedure do_autonomous_insert(
451 p_rowid in out nocopy varchar2,
452 p_formula_id in out nocopy number,
453 p_effective_start_date date,
454 p_effective_end_date date,
455 p_business_group_id number,
456 p_legislation_code varchar2,
457 p_formula_type_id number,
458 p_formula_name in out nocopy varchar2,
459 p_formula_text varchar2,
460 p_last_update_date in out nocopy date
461 )
462 is
463 --
464 pragma autonomous_transaction;
465 --
466 begin
467 --
468 ff_formulas_f_pkg.insert_row (
469 x_rowid => p_rowid,
470 x_formula_id => p_formula_id,
471 x_effective_start_date => p_effective_start_date,
472 x_effective_end_date => p_effective_end_date,
473 x_business_group_id => p_business_group_id,
474 x_legislation_code => p_legislation_code,
475 x_formula_type_id => p_formula_type_id,
476 x_formula_name => p_formula_name,
477 x_description => null,
478 x_formula_text => p_formula_text,
479 x_sticky_flag => 'Y',
480 x_last_update_date => p_last_update_date
481 );
482 --
483 commit;
484 --
485 end do_autonomous_insert;
486 --
487 procedure do_autonomous_delete(
488 p_rowid varchar2,
489 p_formula_id number,
490 p_effective_start_date date,
491 p_effective_end_date date
492 )
493 is
494 --
495 pragma autonomous_transaction;
496 --
497 begin
498 --
499 ff_formulas_f_pkg.delete_row(
500 x_rowid => p_rowid,
501 x_formula_id => p_formula_id,
502 x_dt_delete_mode => 'ZAP',
503 x_validation_start_date => fnd_date.canonical_to_date('0001/01/01'),
504 x_validation_end_date => p_effective_end_date,
505 x_effective_date => p_effective_start_date
506 );
507 --
508 commit;
509 --
510 end do_autonomous_delete;
511 --
512 procedure compile_formula_autonomously(
513 p_formula_type_id in number,
514 p_effective_start_date in date,
515 p_effective_end_date in date,
516 p_business_group_id in number,
517 p_legislation_code in varchar2,
518 p_formula_text in clob,
519 p_outcome out nocopy varchar2,
520 p_message out nocopy varchar2,
521 p_return_status out nocopy varchar2
522 )
523 is
524 --
525 l_rowid varchar2(240);
526 l_formula_id number;
527 l_formula_name varchar2(80);
528 l_last_update_date date;
529 --
530 begin
531 --
532 if g_debug then
533 hr_utility.set_location('Entering ff_formula_webui_pkg.compile_formula_autonomously',10);
534 end if;
535
536 --
537 -- Bug 11849712 Test if online patching is in progress, if so, block execution
538 --
539 if ad_zd.get_edition('PATCH') is not null then
540 -- an online patch is in progress, return error
541 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
542 raise_application_error ('-20000', fnd_message.get);
543 end if;
544
545 --
546 -- Enable multi-messaging
547 hr_multi_message.enable_message_list;
548 --
549 -- Generate a unique formula name for this formula type, business group
550 -- and legislation
551 generate_unique_formula_name(
552 p_formula_type_id => p_formula_type_id,
553 p_business_group_id => p_business_group_id,
554 p_legislation_code => p_legislation_code,
555 p_formula_name => l_formula_name
556 );
557 --
558 -- insert the formula
559 do_autonomous_insert (
560 p_rowid => l_rowid,
561 p_formula_id => l_formula_id,
562 p_effective_start_date => p_effective_start_date,
563 p_effective_end_date => p_effective_end_date,
564 p_business_group_id => p_business_group_id,
565 p_legislation_code => p_legislation_code,
566 p_formula_type_id => p_formula_type_id,
567 p_formula_name => l_formula_name,
568 p_formula_text => p_formula_text,
569 p_last_update_date => l_last_update_date
570 );
571 -- do the compile
572 compile_formula(
573 p_formula_id => l_formula_id,
574 p_effective_date => p_effective_start_date,
575 p_outcome => p_outcome,
576 p_message => p_message
577 );
578 -- delete the formula
579 do_autonomous_delete(
580 p_rowid => l_rowid,
581 p_formula_id => l_formula_id,
582 p_effective_start_date => p_effective_start_date,
583 p_effective_end_date => p_effective_end_date
584 );
585 --
586 if g_debug then
587 hr_utility.set_location('Leaving ff_formula_webui_pkg.compile_formula_autonomously',20);
588 end if;
589 --
590 -- Get the return status and disable multi-messaging
591 p_return_status := hr_multi_message.get_return_status_disable;
592 --
593 exception
594 --
595 when hr_multi_message.error_message_exist then
596 p_return_status := hr_multi_message.get_return_status_disable;
597 --
598 when others then raise;
599 --hr_multi_message.add;
600 --p_return_status := hr_multi_message.get_return_status_disable;
601 --
602 end compile_formula_autonomously;
603 --
604 procedure run_formula(
605 p_formula_id in number,
606 p_session_date in date,
607 p_input_name1 in varchar2,
608 p_input_name2 in varchar2,
609 p_input_name3 in varchar2,
610 p_input_name4 in varchar2,
611 p_input_name5 in varchar2,
612 p_input_name6 in varchar2,
613 p_input_name7 in varchar2,
614 p_input_name8 in varchar2,
615 p_input_name9 in varchar2,
616 p_input_name10 in varchar2,
617 p_input_name11 in varchar2,
618 p_input_name12 in varchar2,
619 p_input_name13 in varchar2,
620 p_input_name14 in varchar2,
621 p_input_name15 in varchar2,
622 p_input_name16 in varchar2,
623 p_input_name17 in varchar2,
624 p_input_name18 in varchar2,
625 p_input_name19 in varchar2,
626 p_input_name20 in varchar2,
627 p_input_name21 in varchar2,
628 p_input_name22 in varchar2,
629 p_input_name23 in varchar2,
630 p_input_name24 in varchar2,
631 p_input_name25 in varchar2,
632 p_input_name26 in varchar2,
633 p_input_name27 in varchar2,
634 p_input_name28 in varchar2,
635 p_input_name29 in varchar2,
636 p_input_name30 in varchar2,
637 p_input_value1 in varchar2,
638 p_input_value2 in varchar2,
639 p_input_value3 in varchar2,
640 p_input_value4 in varchar2,
641 p_input_value5 in varchar2,
642 p_input_value6 in varchar2,
643 p_input_value7 in varchar2,
644 p_input_value8 in varchar2,
645 p_input_value9 in varchar2,
646 p_input_value10 in varchar2,
647 p_input_value11 in varchar2,
648 p_input_value12 in varchar2,
649 p_input_value13 in varchar2,
650 p_input_value14 in varchar2,
651 p_input_value15 in varchar2,
652 p_input_value16 in varchar2,
653 p_input_value17 in varchar2,
654 p_input_value18 in varchar2,
655 p_input_value19 in varchar2,
656 p_input_value20 in varchar2,
657 p_input_value21 in varchar2,
658 p_input_value22 in varchar2,
659 p_input_value23 in varchar2,
660 p_input_value24 in varchar2,
661 p_input_value25 in varchar2,
662 p_input_value26 in varchar2,
663 p_input_value27 in varchar2,
664 p_input_value28 in varchar2,
665 p_input_value29 in varchar2,
666 p_input_value30 in varchar2,
667 p_output_name1 in out nocopy varchar2,
668 p_output_name2 in out nocopy varchar2,
669 p_output_name3 in out nocopy varchar2,
670 p_output_name4 in out nocopy varchar2,
671 p_output_name5 in out nocopy varchar2,
672 p_output_name6 in out nocopy varchar2,
673 p_output_name7 in out nocopy varchar2,
674 p_output_name8 in out nocopy varchar2,
675 p_output_name9 in out nocopy varchar2,
676 p_output_name10 in out nocopy varchar2,
677 p_output_name11 in out nocopy varchar2,
678 p_output_name12 in out nocopy varchar2,
679 p_output_name13 in out nocopy varchar2,
680 p_output_name14 in out nocopy varchar2,
681 p_output_name15 in out nocopy varchar2,
682 p_output_name16 in out nocopy varchar2,
683 p_output_name17 in out nocopy varchar2,
684 p_output_name18 in out nocopy varchar2,
685 p_output_name19 in out nocopy varchar2,
686 p_output_name20 in out nocopy varchar2,
687 p_output_name21 in out nocopy varchar2,
688 p_output_name22 in out nocopy varchar2,
689 p_output_name23 in out nocopy varchar2,
690 p_output_name24 in out nocopy varchar2,
691 p_output_name25 in out nocopy varchar2,
692 p_output_name26 in out nocopy varchar2,
693 p_output_name27 in out nocopy varchar2,
694 p_output_name28 in out nocopy varchar2,
695 p_output_name29 in out nocopy varchar2,
696 p_output_name30 in out nocopy varchar2,
697 p_output_value1 out nocopy varchar2,
698 p_output_value2 out nocopy varchar2,
699 p_output_value3 out nocopy varchar2,
700 p_output_value4 out nocopy varchar2,
701 p_output_value5 out nocopy varchar2,
702 p_output_value6 out nocopy varchar2,
703 p_output_value7 out nocopy varchar2,
704 p_output_value8 out nocopy varchar2,
705 p_output_value9 out nocopy varchar2,
706 p_output_value10 out nocopy varchar2,
707 p_output_value11 out nocopy varchar2,
708 p_output_value12 out nocopy varchar2,
709 p_output_value13 out nocopy varchar2,
710 p_output_value14 out nocopy varchar2,
711 p_output_value15 out nocopy varchar2,
712 p_output_value16 out nocopy varchar2,
713 p_output_value17 out nocopy varchar2,
714 p_output_value18 out nocopy varchar2,
715 p_output_value19 out nocopy varchar2,
716 p_output_value20 out nocopy varchar2,
717 p_output_value21 out nocopy varchar2,
718 p_output_value22 out nocopy varchar2,
719 p_output_value23 out nocopy varchar2,
720 p_output_value24 out nocopy varchar2,
721 p_output_value25 out nocopy varchar2,
722 p_output_value26 out nocopy varchar2,
723 p_output_value27 out nocopy varchar2,
724 p_output_value28 out nocopy varchar2,
725 p_output_value29 out nocopy varchar2,
726 p_output_value30 out nocopy varchar2,
727 p_return_status out nocopy varchar2
728 )
729 is
730 --
731 v_inputs ff_exec.inputs_t;
732 v_outputs ff_exec.outputs_t;
733 v_output_name varchar2(240);
734 --
735 begin
736 --
737 if g_debug then
738 hr_utility.set_location('Entering ff_formula_webui_pkg.run_formula',10);
739 end if;
740 --
741 -- Enable multi-messaging
742 hr_multi_message.enable_message_list;
743 --
744 ff_exec.init_formula(p_formula_id, p_session_date, v_inputs, v_outputs);
745 --
746 -- Check to see if any errors occurred, if so the
747 -- hr_multi_message.error_message_exist exception will be raised
748 hr_multi_message.end_validation_set;
749 --
750 -- Set up the inputs and contexts to formula.
751 for i in v_inputs.first..v_inputs.last loop
752 --
753 if upper(v_inputs(i).name) = upper(p_input_name1) then
754 v_inputs(i).value := p_input_value1;
755 elsif upper(v_inputs(i).name) = upper(p_input_name2) then
756 v_inputs(i).value := p_input_value2;
757 elsif upper(v_inputs(i).name) = upper(p_input_name3) then
758 v_inputs(i).value := p_input_value3;
759 elsif upper(v_inputs(i).name) = upper(p_input_name4) then
760 v_inputs(i).value := p_input_value4;
761 elsif upper(v_inputs(i).name) = upper(p_input_name5) then
762 v_inputs(i).value := p_input_value5;
763 elsif upper(v_inputs(i).name) = upper(p_input_name6) then
764 v_inputs(i).value := p_input_value6;
765 elsif upper(v_inputs(i).name) = upper(p_input_name7) then
766 v_inputs(i).value := p_input_value7;
767 elsif upper(v_inputs(i).name) = upper(p_input_name8) then
768 v_inputs(i).value := p_input_value8;
769 elsif upper(v_inputs(i).name) = upper(p_input_name9) then
770 v_inputs(i).value := p_input_value9;
771 elsif upper(v_inputs(i).name) = upper(p_input_name10) then
772 v_inputs(i).value := p_input_value10;
773 elsif upper(v_inputs(i).name) = upper(p_input_name11) then
774 v_inputs(i).value := p_input_value11;
775 elsif upper(v_inputs(i).name) = upper(p_input_name12) then
776 v_inputs(i).value := p_input_value12;
777 elsif upper(v_inputs(i).name) = upper(p_input_name13) then
778 v_inputs(i).value := p_input_value13;
779 elsif upper(v_inputs(i).name) = upper(p_input_name14) then
780 v_inputs(i).value := p_input_value14;
781 elsif upper(v_inputs(i).name) = upper(p_input_name15) then
782 v_inputs(i).value := p_input_value15;
783 elsif upper(v_inputs(i).name) = upper(p_input_name16) then
784 v_inputs(i).value := p_input_value16;
785 elsif upper(v_inputs(i).name) = upper(p_input_name17) then
786 v_inputs(i).value := p_input_value17;
787 elsif upper(v_inputs(i).name) = upper(p_input_name18) then
788 v_inputs(i).value := p_input_value18;
789 elsif upper(v_inputs(i).name) = upper(p_input_name19) then
790 v_inputs(i).value := p_input_value19;
791 elsif upper(v_inputs(i).name) = upper(p_input_name20) then
792 v_inputs(i).value := p_input_value20;
793 elsif upper(v_inputs(i).name) = upper(p_input_name21) then
794 v_inputs(i).value := p_input_value21;
795 elsif upper(v_inputs(i).name) = upper(p_input_name22) then
796 v_inputs(i).value := p_input_value22;
797 elsif upper(v_inputs(i).name) = upper(p_input_name23) then
798 v_inputs(i).value := p_input_value23;
799 elsif upper(v_inputs(i).name) = upper(p_input_name24) then
800 v_inputs(i).value := p_input_value24;
801 elsif upper(v_inputs(i).name) = upper(p_input_name25) then
802 v_inputs(i).value := p_input_value25;
803 elsif upper(v_inputs(i).name) = upper(p_input_name26) then
804 v_inputs(i).value := p_input_value26;
805 elsif upper(v_inputs(i).name) = upper(p_input_name27) then
806 v_inputs(i).value := p_input_value27;
807 elsif upper(v_inputs(i).name) = upper(p_input_name28) then
808 v_inputs(i).value := p_input_value28;
809 elsif upper(v_inputs(i).name) = upper(p_input_name29) then
810 v_inputs(i).value := p_input_value29;
811 elsif upper(v_inputs(i).name) = upper(p_input_name30) then
812 v_inputs(i).value := p_input_value30;
813 else
814 -- Input name not recognized
815 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
816 hr_utility.set_message_token('PROCEDURE',
817 'FF_FORMULA_WEBUI_PKG.run_formula');
818 hr_utility.set_message_token('STEP','10');
819 if hr_multi_message.exception_add then
820 hr_utility.raise_error;
821 end if;
822 end if;
823 --
824 end loop;
825 --
826 ff_exec.run_formula(v_inputs, v_outputs);
827 --
828 -- Now obtain the return values.
829 for i in v_outputs.first..v_outputs.last loop
830 --
831 v_output_name := upper(v_outputs(i).name);
832 --
833 if v_output_name = upper(p_output_name1) then
834 p_output_value1 := v_outputs(i).value;
835 elsif v_output_name = upper(p_output_name2) then
836 p_output_value2 := v_outputs(i).value;
837 elsif v_output_name = upper(p_output_name3) then
838 p_output_value3 := v_outputs(i).value;
839 elsif v_output_name = upper(p_output_name4) then
840 p_output_value4 := v_outputs(i).value;
841 elsif v_output_name = upper(p_output_name5) then
842 p_output_value5 := v_outputs(i).value;
843 elsif v_output_name = upper(p_output_name6) then
844 p_output_value6 := v_outputs(i).value;
845 elsif v_output_name = upper(p_output_name7) then
846 p_output_value7 := v_outputs(i).value;
847 elsif v_output_name = upper(p_output_name8) then
848 p_output_value8 := v_outputs(i).value;
849 elsif v_output_name = upper(p_output_name9) then
850 p_output_value9 := v_outputs(i).value;
851 elsif v_output_name = upper(p_output_name10) then
852 p_output_value10 := v_outputs(i).value;
853 elsif v_output_name = upper(p_output_name11) then
854 p_output_value11 := v_outputs(i).value;
855 elsif v_output_name = upper(p_output_name12) then
856 p_output_value12 := v_outputs(i).value;
857 elsif v_output_name = upper(p_output_name13) then
858 p_output_value13 := v_outputs(i).value;
859 elsif v_output_name = upper(p_output_name14) then
860 p_output_value14 := v_outputs(i).value;
861 elsif v_output_name = upper(p_output_name15) then
862 p_output_value15 := v_outputs(i).value;
863 elsif v_output_name = upper(p_output_name16) then
864 p_output_value16 := v_outputs(i).value;
865 elsif v_output_name = upper(p_output_name17) then
866 p_output_value17 := v_outputs(i).value;
867 elsif v_output_name = upper(p_output_name18) then
868 p_output_value18 := v_outputs(i).value;
869 elsif v_output_name = upper(p_output_name19) then
870 p_output_value19 := v_outputs(i).value;
871 elsif v_output_name = upper(p_output_name20) then
872 p_output_value20 := v_outputs(i).value;
873 elsif v_output_name = upper(p_output_name21) then
874 p_output_value21 := v_outputs(i).value;
875 elsif v_output_name = upper(p_output_name22) then
876 p_output_value22 := v_outputs(i).value;
877 elsif v_output_name = upper(p_output_name23) then
878 p_output_value23 := v_outputs(i).value;
879 elsif v_output_name = upper(p_output_name24) then
880 p_output_value24 := v_outputs(i).value;
881 elsif v_output_name = upper(p_output_name25) then
882 p_output_value25 := v_outputs(i).value;
883 elsif v_output_name = upper(p_output_name26) then
884 p_output_value26 := v_outputs(i).value;
885 elsif v_output_name = upper(p_output_name27) then
886 p_output_value27 := v_outputs(i).value;
887 elsif v_output_name = upper(p_output_name28) then
888 p_output_value28 := v_outputs(i).value;
889 elsif v_output_name = upper(p_output_name29) then
890 p_output_value29 := v_outputs(i).value;
891 elsif v_output_name = upper(p_output_name30) then
892 p_output_value30 := v_outputs(i).value;
893 else
894 -- Output name not recognized
895 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
896 hr_utility.set_message_token('PROCEDURE',
897 'FF_FORMULA_WEBUI_PKG.run_formula');
898 hr_utility.set_message_token('STEP','20');
899 if hr_multi_message.exception_add then
900 hr_utility.raise_error;
901 end if;
902 end if;
903 --
904 end loop;
905 --
906 if g_debug then
907 hr_utility.set_location('Leaving ff_formula_webui_pkg.run_formula',20);
908 end if;
909 --
910 -- Get the return status and disable multi-messaging
911 p_return_status := hr_multi_message.get_return_status_disable;
912 --
913 exception
914 --
915 when hr_multi_message.error_message_exist then
916 ff_exec.reset_caches;
917 p_return_status := hr_multi_message.get_return_status_disable;
918 --
919 when others then
920 ff_exec.reset_caches;
921 hr_multi_message.add;
922 p_return_status := hr_multi_message.get_return_status_disable;
923 --
924 end run_formula;
925 --
926 function isFormulaCompiled(
927 p_formula_id in number,
928 p_effective_date in date) return varchar2
929 is
930 --
931 cursor c_formula_compiled_info is
932 select 'Y'
933 from ff_compiled_info_f
934 where formula_id = p_formula_id
935 and p_effective_date between effective_start_date and effective_end_date;
936 --
937 l_formula_is_compiled varchar2(15) := 'N';
938 --
939 begin
940 --
941 if g_debug then
942 hr_utility.set_location('Entering ff_formula_webui_pkg.isFormulaCompiled',10);
943 end if;
944 --
945 open c_formula_compiled_info;
946 fetch c_formula_compiled_info into l_formula_is_compiled;
947 close c_formula_compiled_info;
948 --
949 --
950 if g_debug then
951 hr_utility.set_location('Leaving ff_formula_webui_pkg.isFormulaCompiled',20);
952 end if;
953 return l_formula_is_compiled;
954 end isFormulaCompiled;
955 --
956 function list_function_params(
957 p_function_id in number) return varchar2
958 is
959 --
960 cursor c_fn_name (p_fn_id number) is
961 select f.name
962 from ff_functions f
963 where f.function_id = p_fn_id;
964 --
965 cursor c_fn_params (p_fn_id number) is
966 select fp.name, lu.meaning data_type
967 from ff_function_parameters fp,
968 hr_lookups lu
969 where fp.function_id = p_fn_id
970 and fp.data_type = lu.lookup_code
971 and lu.lookup_type = 'DATA_TYPE'
972 order by fp.sequence_number;
973 --
974 l_param_list varchar2(4000);
975 l_separator varchar2(5);
976 --
977 begin
978 --
979 if g_debug then
980 hr_utility.set_location('Entering ff_formula_webui_pkg.list_function_params',10);
981 end if;
982 --
983 open c_fn_name(p_function_id);
984 fetch c_fn_name into l_param_list;
985 close c_fn_name;
986 --
987 l_param_list := l_param_list || '(';
988 --
989 for param in c_fn_params(p_function_id) loop
990 --
991 l_param_list := l_param_list || l_separator || param.name ||' '|| param.data_type;
992 l_separator := ', ';
993 --
994 end loop;
995 --
996 l_param_list := l_param_list || ')';
997 --
998 if g_debug then
999 hr_utility.trace('l_param_list: '||l_param_list);
1000 hr_utility.set_location('Leaving ff_formula_webui_pkg.list_function_params',20);
1001 end if;
1002 --
1003 return l_param_list;
1004 --
1005 end list_function_params;
1006 --
1007 function submit_singlecompile(
1008 p_formula_type in varchar2,
1009 p_formula_name in varchar2
1010 ) return number is
1011
1012 --
1013 l_request_id number;
1014 --
1015 begin
1016 --
1017 if g_debug then
1018 hr_utility.set_location('Entering ff_formula_webui_pkg.submit_singlecompile',10);
1019 end if;
1020
1021 --
1022 -- Bug 13435428 Test if online patching is in progress, if so, block execution
1023 --
1024 if ad_zd.get_edition('PATCH') is not null then
1025 -- an online patch is in progress, return error
1026 hr_utility.trace('ff_formula_webui_pkg.submit_singlecompile patch in progress , cant submit formula compile');
1027 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
1028 raise_application_error ('-20000', fnd_message.get);
1029 end if;
1030 --
1031 l_request_id := fnd_request.submit_request
1032 (application => 'FF'
1033 ,program => 'SINGLECOMPILE'
1034 ,argument1 => p_formula_type
1035 ,argument2 => p_formula_name);
1036
1037 if g_debug then
1038 hr_utility.set_location('ff_formula_webui_pkg.submit_singlecompile. Request Id :'||l_request_id, 25);
1039 hr_utility.set_location('Leaving: ff_formula_webui_pkg.submit_singlecompile', 30);
1040 end if;
1041 return l_request_id;
1042
1043 exception
1044 when others then
1045 hr_utility.set_location('ff_formula_webui_pkg.submit_singlecompile. Entering exception when others ', 80);
1046 hr_utility.set_location('Leaving: ff_formula_webui_pkg.submit_singlecompile', 90);
1047 raise;
1048
1049 end submit_singlecompile;
1050 --
1051
1052 end FF_FORMULA_WEBUI_PKG;