1 package body per_formula_functions as
2 /* $Header: pefmlfnc.pkb 120.0 2005/05/31 08:49:45 appldev noship $ */
3 --
4 g_package varchar2(33) := ' per_formula_functions.'; -- Global package name
5 hr_formula_application_id number;
6 hr_formula_message varchar2(80);
7 hr_formula_error exception;
8 /* Use a random user-defined errnum, using -20001 affects fnd_message calls */
9 pragma exception_init (hr_formula_error, -201);
10
11 --
12 /* =====================================================================
13 Define a global table to hold the relevant looping formulas required
14 to calculate pto accruals.
15 ---------------------------------------------------------------------*/
16 TYPE formula_cache_r is RECORD
17 (
18 formula_id number_tbl,
19 formula_name varchar_80_tbl,
20 business_group_id number_tbl,
21 effective_start_date date_tbl,
22 effective_end_date date_tbl,
23 sz number
24 );
25
26 g_formula_cache formula_cache_r;
27 g_formulas_cached boolean := FALSE;
28
29 /* =====================================================================
30 Define a package global record and table type of numeric values.
31 Declare an instance of the table.
32 ---------------------------------------------------------------------*/
33 TYPE global_number_r is RECORD
34 (name varchar2(30)
35 ,value number);
36 --
37 TYPE global_number_t is TABLE OF global_number_r INDEX BY BINARY_INTEGER;
38 --
39 global_number global_number_t;
40 --
41 /* =====================================================================
42 Define a package global record and table type of date values.
43 Declare an instance of the table.
44 ---------------------------------------------------------------------*/
45 TYPE global_date_r is RECORD
46 (name varchar2(30)
47 ,value date);
48 --
49 TYPE global_date_t is TABLE OF global_date_r INDEX BY BINARY_INTEGER;
50 --
51 global_date global_date_t;
52 --
53 /* =====================================================================
54 Define a package global record and table type of text values.
55 Declare an instance of the table.
56 ---------------------------------------------------------------------*/
57 TYPE global_text_r is RECORD
58 (name varchar2(30)
59 ,value varchar2(80));
60 --
61 TYPE global_text_t is TABLE OF global_text_r INDEX BY BINARY_INTEGER;
62 --
63 global_text global_text_t;
64 --
65 /* =====================================================================
66 Name : Cache Formulas
67 Purpose : Populates the PL/SQL table with the given formula_name. If
68 the table is already cached, the formula is added.
69 Returns : Nothing.
70 ---------------------------------------------------------------------*/
71 procedure cache_formulas (p_formula_name in varchar2) is
72
73 cursor c_get_formulas is
74 select ff.formula_id,
75 ff.formula_name,
76 ff.business_group_id,
77 ff.effective_start_date,
78 ff.effective_end_date
79 from ff_formulas_f ff
80 ,ff_compiled_info_f ffci
81 where ff.formula_id = ffci.formula_id
82 and ff.effective_start_date = ffci.effective_start_date
83 and ff.effective_end_date = ffci.effective_end_date
84 and ff.formula_name = p_formula_name;
85
86 l_proc varchar2(80) := g_package||'cache_formulas';
87 --
88 begin
89 --
90
91 if g_formulas_cached = FALSE then
92 g_formula_cache.sz := 0;
93 end if;
94 --
95 for ff_rec in c_get_formulas loop
96 --
97 g_formula_cache.sz := g_formula_cache.sz + 1;
98 g_formula_cache.formula_id(g_formula_cache.sz) := ff_rec.formula_id;
99 g_formula_cache.formula_name(g_formula_cache.sz) := ff_rec.formula_name;
100 g_formula_cache.business_group_id(g_formula_cache.sz) := ff_rec.business_group_id;
101 g_formula_cache.effective_start_date(g_formula_cache.sz) := ff_rec.effective_start_date;
102 g_formula_cache.effective_end_date(g_formula_cache.sz) := ff_rec.effective_end_date;
103 --
104 end loop;
105 --
106 g_formulas_cached := TRUE;
107 --
108 end cache_formulas;
109 --
110 /* =====================================================================
111 Name : Cache Formulas (overloaded)
112 Purpose : Populates the PL/SQL table with the given formula_id. If
113 the table is already cached, the formula is added.
114 Returns : Nothing.
115 ---------------------------------------------------------------------*/
116 procedure cache_formulas (p_formula_id in number) is
117
118 cursor c_get_formulas is
119 select ff.formula_id,
120 ff.formula_name,
121 ff.business_group_id,
122 ff.effective_start_date,
123 ff.effective_end_date
124 from ff_formulas_f ff
125 ,ff_compiled_info_f ffci
126 where ff.formula_id = ffci.formula_id
127 and ff.effective_start_date = ffci.effective_start_date
128 and ff.effective_end_date = ffci.effective_end_date
129 and ff.formula_id = p_formula_id;
130
131 l_proc varchar2(80) := g_package||'cache_formulas';
132 --
133 begin
134 --
135 if g_formulas_cached = FALSE then
136 g_formula_cache.sz := 0;
137 end if;
138 --
139 for ff_rec in c_get_formulas loop
140 --
141 g_formula_cache.sz := g_formula_cache.sz + 1;
142 g_formula_cache.formula_id(g_formula_cache.sz) := ff_rec.formula_id;
143 g_formula_cache.formula_name(g_formula_cache.sz) := ff_rec.formula_name;
144 g_formula_cache.business_group_id(g_formula_cache.sz) := ff_rec.business_group_id;
145 g_formula_cache.effective_start_date(g_formula_cache.sz) := ff_rec.effective_start_date;
146 g_formula_cache.effective_end_date(g_formula_cache.sz) := ff_rec.effective_end_date;
147
148 end loop;
149
150 g_formulas_cached := TRUE;
151
152 end cache_formulas;
153 --
154 /* =====================================================================
155 Name : Get Cache Formula
156 /* =====================================================================
157 Name : Get Cache Formula
158 Purpose : Gets the formula_id from a cached pl/sql table to prevent
159 a full table scan on ff_formulas_f for each person in the
160 payroll run.
161 Returns : formula_id if found, otherwise 0.
162 ---------------------------------------------------------------------*/
163 function get_cache_formula(p_formula_name in varchar2,
164 p_business_group_id in number,
165 p_calculation_date in date)
166 return number is
167
168 ff_rec number;
169 l_formula_id number := 0;
170
171 begin
172 --
173
174 for ff_rec in 1..g_formula_cache.sz loop
175
176 if (g_formula_cache.formula_name(ff_rec) = p_formula_name)
177 and (nvl(g_formula_cache.business_group_id(ff_rec), p_business_group_id) = p_business_group_id)
178 and (p_calculation_date between g_formula_cache.effective_start_date(ff_rec) and
179 g_formula_cache.effective_end_date(ff_rec))
180 then
181 l_formula_id := g_formula_cache.formula_id(ff_rec);
182 end if;
183
184 end loop;
185
186 return l_formula_id;
187 -- This will be zero if the formula is not in the cached formulas
188
189 --
190 end get_cache_formula;
191 /* =====================================================================
192 Name : Get Cache Formula (overloaded)
193 Purpose : Gets the formula_id from a cached pl/sql table to prevent
194 a hit on ff_formulas_f for each person in the
195 payroll run.
196 Returns : formula_id if found, otherwise 0.
197 ---------------------------------------------------------------------*/
198 function get_cache_formula(p_formula_id in number,
199 p_calculation_date in date)
200 return varchar2 is
201
202 ff_rec number;
203 l_formula_name ff_formulas_f.formula_name%TYPE;
204
205 begin
206 --
207
208 for ff_rec in 1..g_formula_cache.sz loop
209
210 if (g_formula_cache.formula_id(ff_rec) = p_formula_id)
211 and (p_calculation_date between g_formula_cache.effective_start_date(ff_rec) and
212 g_formula_cache.effective_end_date(ff_rec))
213 then
214 l_formula_name := g_formula_cache.formula_name(ff_rec);
215 end if;
216
217 end loop;
218
219 return l_formula_name;
220 -- This will be null if the formula is not in the cached formulas
221
222 end get_cache_formula;
223 --
224 /* =====================================================================
225 Name : Get Formula
226 Purpose : Gets the formula_id from a cached pl/sql table to prevent
227 a full table scan on ff_formulas_f for each person in the
228 payroll run.
229 Returns : formula_id if found, otherwise null.
230 ---------------------------------------------------------------------*/
231 function get_formula(p_formula_name in varchar2,
232 p_business_group_id in number,
233 p_calculation_date in date)
234 return number is
235
236 l_formula_id number;
237
238 begin
239 --
240 if g_formulas_cached = FALSE then
241 cache_formulas (p_formula_name => p_formula_name);
242 end if;
243
244 l_formula_id := get_cache_formula (
245 p_formula_name => p_formula_name,
246 p_business_group_id => p_business_group_id,
247 p_calculation_date => p_calculation_date
248 );
249
250 if l_formula_id = 0 then
251 -- Formula not found in existing cached table. This probably means
252 -- that payroll is processing several different accrual plans
253 -- such as Vacation and Sick plans which are using different
254 -- formulae. We continue adding to the cached plsql table
255 -- until we have all the formula required.
256 cache_formulas (p_formula_name => p_formula_name);
257
258 -- Again search the cached table for the newly added formula records.
259 l_formula_id := get_cache_formula (
260 p_formula_name => p_formula_name,
261 p_business_group_id => p_business_group_id,
262 p_calculation_date => p_calculation_date
263 );
264
265 end if;
266
267 return l_formula_id;
268 -- This will be zero if formula does not exist or is not compiled.
269
270 --
271 end get_formula;
272 /* =====================================================================
273 Name : Get Formula (overloaded)
274 Purpose : Gets the formula_name from a cached pl/sql table to prevent
275 a hit on ff_formulas_f for each PTO formula used.
276 Returns : formula_name if found, otherwise null.
277 ---------------------------------------------------------------------*/
278 function get_formula(p_formula_id in number,
279 p_calculation_date in date)
280 return varchar2 is
281
282 l_formula_name ff_formulas_f.formula_name%TYPE;
283
284 begin
285 --
286 if g_formulas_cached = FALSE then
287 cache_formulas (p_formula_id => p_formula_id);
288 end if;
289
290 l_formula_name := get_cache_formula (
291 p_formula_id => p_formula_id,
292 p_calculation_date => p_calculation_date
293 );
294
295 if l_formula_name is null then
296 -- Formula not found in existing cached table. Add the formula to
297 -- the cache.
298 cache_formulas (p_formula_id => p_formula_id);
299
300 -- Again search the cached table for the newly added formula records.
301 l_formula_name := get_cache_formula (
302 p_formula_id => p_formula_id,
303 p_calculation_date => p_calculation_date
304 );
305
306 end if;
307
308 return l_formula_name;
309 -- This will be null if formula does not exist or is not compiled.
310 --
311 end get_formula;
312 --
313 /* =====================================================================
314 Name : Loop Control
315 Purpose : To repeatedly run a formula while the CONTINUE_PROCESSING_FLAG
316 output parameter is set to 'Y'. If the value is 'N' then the
317 function will end normally otherwise it will abort.
318 Returns : 0 if successful, 1 otherwise
319 ---------------------------------------------------------------------*/
320 function loop_control(p_business_group_id number
321 ,p_calculation_date date
322 ,p_assignment_id number
323 ,p_payroll_id number
324 ,p_accrual_plan_id number
325 ,p_formula_name varchar2) return number is
326 --
327 l_proc varchar2(72) := g_package||'loop_control';
328 --
329 l_continue_loop varchar2(1);
330 l_inputs ff_exec.inputs_t;
331 l_get_outputs ff_exec.outputs_t;
332 l_formula_id number;
333 --
334
335 begin
336 --
337 hr_utility.set_location(l_proc, 5);
338
339
340 -- Get the formula ID from a a plsql table instead of ff_formulas_f
341 -- to improve performance of batch processes.
342 l_formula_id := get_formula (
343 p_formula_name => p_formula_name,
344 p_business_group_id => p_business_group_id,
345 p_calculation_date => p_calculation_date
346 );
347
348 if l_formula_id = 0 then
349 --
350 hr_utility.set_location(l_proc, 10);
351 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
352 fnd_message.set_token('1', p_formula_name);
353 fnd_message.raise_error;
354 --
355 else
356 --
357
358 -----------------------------
359 -- Initialise the formula. --
360 -----------------------------
361 --
362 l_inputs(1).name := 'ASSIGNMENT_ID';
363 l_inputs(1).value := p_assignment_id;
364 l_inputs(2).name := 'DATE_EARNED';
365 -- Start of fix 3047532
366 --l_inputs(2).value := to_char(p_calculation_date, 'DD-MON-YYYY');
367 l_inputs(2).value := fnd_date.date_to_canonical(p_calculation_date);
368 -- End of fix 2047532
369 l_inputs(3).name := 'ACCRUAL_PLAN_ID';
370 l_inputs(3).value := p_accrual_plan_id;
371 l_inputs(4).name := 'BUSINESS_GROUP_ID';
372 l_inputs(4).value := p_business_group_id;
373 l_inputs(5).name := 'PAYROLL_ID';
374 l_inputs(5).value := p_payroll_id;
375
376 l_get_outputs(1).name := 'CONTINUE_PROCESSING_FLAG';
377
378 while true loop
379 --
380 ----------------------
381 -- Run the formula. --
382 ----------------------
383 hr_utility.set_location('Prior to Run Formula '||l_proc, 10);
384 --
385 per_formula_functions.run_formula (p_formula_id => l_formula_id
386 ,p_calculation_date => p_calculation_date
387 ,p_inputs => l_inputs
388 ,p_outputs => l_get_outputs);
389
390 l_continue_loop := l_get_outputs(1).value;
391 --
392 hr_utility.set_location('Run Formula Complete '||l_proc, 15);
393 -------------------------------
394 -- Test the output parameter --
395 -------------------------------
396 if l_continue_loop = 'Y' then
400 else
397 null; -- continue processing
398 elsif l_continue_loop = 'N' then
399 exit; -- exit the loop and end looping sucessfully
401 return 1;
402 end if;
403 --
404 end loop;
405 --
406 end if;
407
408 hr_utility.set_location('Successful Exit '||l_proc, 20);
409 return 0;
410 --
411 end loop_control;
412 --
413 /* =====================================================================
414 Name : call_formula
415 Purpose : To run a named formula, with no inputs and no outputs
416 Returns : 0 if successful, 1 otherwise
417 ---------------------------------------------------------------------*/
418 function call_formula
419 (p_business_group_id number
420 ,p_calculation_date date
421 ,p_assignment_id number
422 ,p_payroll_id number
423 ,p_accrual_plan_id number
424 ,p_formula_name varchar2) return number is
425 --
426 l_proc varchar2(72) := g_package||'call_formula';
427 l_inputs ff_exec.inputs_t;
428 l_get_outputs ff_exec.outputs_t;
429 --
430 begin
431 -----------------------------
432 -- Initialise the formula. --
433 -----------------------------
434 --
435 l_inputs(1).name := 'ASSIGNMENT_ID';
436 l_inputs(1).value := p_assignment_id;
437 l_inputs(2).name := 'DATE_EARNED';
438 l_inputs(2).value := to_char(p_calculation_date, 'DD-MON-YYYY');
439 l_inputs(3).name := 'ACCRUAL_PLAN_ID';
440 l_inputs(3).value := p_accrual_plan_id;
441 l_inputs(4).name := 'BUSINESS_GROUP_ID';
442 l_inputs(4).value := p_business_group_id;
443 l_inputs(5).name := 'PAYROLL_ID';
444 l_inputs(5).value := p_payroll_id;
445
446 l_get_outputs(1).name := 'CONTINUE_PROCESSING_FLAG';
447 ----------------------
448 -- Run the formula. --
449 ----------------------
450 hr_utility.set_location('Prior to Run Formula '||l_proc, 10);
451 --
452 per_formula_functions.run_formula (p_formula_name => p_formula_name
453 ,p_business_group_id => p_business_group_id
454 ,p_calculation_date => p_calculation_date
455 ,p_inputs => l_inputs
456 ,p_outputs => l_get_outputs);
457 hr_utility.set_location('Run Formula Complete '||l_proc, 15);
458 return 0;
459 end call_formula;
460 --
461 /* =====================================================================
462 Name : run_formula
463 Purpose : To run a named formula, handling the input and output
464 parameters.
465 ---------------------------------------------------------------------*/
466 procedure run_formula
467 (p_formula_name varchar2
468 ,p_business_group_id number
469 ,p_calculation_date date
470 ,p_inputs ff_exec.inputs_t
471 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) is
472 --
473 l_proc varchar2(72) := g_package||'run_formula';
474 l_inputs ff_exec.inputs_t;
475 l_outputs ff_exec.outputs_t;
476 l_formula_id number;
477
478 begin
479
480 hr_utility.set_location('Entering '||l_proc, 5);
481 --
482 ------------------------
483 -- Get the formula id --
484 ------------------------
485
486 -- Get the formula ID from a a plsql table instead of ff_formulas_f
487 -- to improve performance of batch processes.
488 l_formula_id := get_formula (
489 p_formula_name => p_formula_name,
490 p_business_group_id => p_business_group_id,
491 p_calculation_date => p_calculation_date
492 );
493
494 if l_formula_id = 0 then
495 hr_utility.set_location(l_proc, 10);
496 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
497 fnd_message.set_token('1', p_formula_name);
498 fnd_message.raise_error;
499 else
500 run_formula(p_formula_id => l_formula_id,
501 p_calculation_date => p_calculation_date,
502 p_inputs => p_inputs,
503 p_outputs => p_outputs);
504 end if;
505 --
506 end run_formula;
507 --
508 /* =====================================================================
509 Name : run_formula
510 Purpose : To run a named formula, handling the input and output
511 parameters.
512 ---------------------------------------------------------------------*/
513 procedure run_formula
514 (p_formula_id number
515 ,p_calculation_date date
516 ,p_inputs ff_exec.inputs_t
517 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) is
518 --
519
520 /* Gets the FF name when a FF is not compiled. */
521 cursor csr_get_ff_name is
522 select ff.formula_name
523 from ff_formulas_f ff
524 where ff.formula_id = p_formula_id
525 and p_calculation_date between
526 ff.effective_start_date and ff.effective_end_date;
527
528 l_formula_name ff_formulas_f.formula_name%TYPE;
529 l_proc varchar2(72) := g_package||'run_formula';
530 l_inputs ff_exec.inputs_t;
531 l_outputs ff_exec.outputs_t;
532 --
533 begin
534
535 hr_utility.set_location('Entering '||l_proc, 5);
536
537 -- Cache this formula. The purpose of this is to fetch the formula name
538 -- (if the formula does not exist or is not compiled it is listed in the
539 -- error message). It is cached to prevent frequent hits on ff_formulas_f
540 -- and ff_compiled_info_f.
541 l_formula_name := get_formula (
542 p_formula_id => p_formula_id,
543 p_calculation_date => p_calculation_date
544 );
545 if l_formula_name is null then
546
550 fetch csr_get_ff_name into l_formula_name;
547 hr_utility.set_location(l_proc, 8);
548
549 open csr_get_ff_name;
551 close csr_get_ff_name;
552
553 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
554 fnd_message.set_token('1', l_formula_name);
555 fnd_message.raise_error;
556 else
557 --
558 ----------------------------
559 -- Initialize the formula --
560 ----------------------------
561 ff_exec.init_formula(p_formula_id, p_calculation_date, l_inputs, l_outputs);
562 --
563 hr_utility.set_location('Handle inputs '||l_proc, 10);
564 -----------------------------
565 -- Set up the input values --
566 -----------------------------
567 if l_inputs.count > 0 and p_inputs.count > 0 then
568 for i in l_inputs.first..l_inputs.last loop
569 for j in p_inputs.first..p_inputs.last loop
570 if l_inputs(i).name = p_inputs(j).name then
571 l_inputs(i).value := p_inputs(j).value;
572 exit;
573 end if;
574 end loop;
575 end loop;
576 end if;
577 --
578 hr_utility.set_location('Run Formula '||l_proc, 15);
579 ---------------------
580 -- Run the formula --
581 ---------------------
582 ff_exec.run_formula(l_inputs,l_outputs);
583 --
584 hr_utility.set_location('Handle outputs '||l_proc, 20);
585 -------------------------------
586 -- Populate the output table --
587 -------------------------------
588 if l_outputs.count > 0 and p_inputs.count > 0 then
589 for i in l_outputs.first..l_outputs.last loop
590 for j in p_outputs.first..p_outputs.last loop
591 if l_outputs(i).name = p_outputs(j).name then
592 p_outputs(j).value := l_outputs(i).value;
593 exit;
594 end if;
595 end loop;
596 end loop;
597 end if;
598
599 end if;
600
601 exception
602 when hr_formula_error then
603 hr_utility.set_location(l_proc, 98);
604 hr_utility.set_message(hr_formula_application_id,hr_formula_message);
605 hr_utility.raise_error;
606 when others then
607 hr_utility.set_location(l_proc, 99);
608 raise;
609 end run_formula;
610 --
611 /* =====================================================================
612 Name : get_number
613 Purpose : To retrieve the value of a numeric global variable
614 Returns : The value of the varibale if found, NULL otherwise
615 ---------------------------------------------------------------------*/
616 function get_number
617 (p_name varchar2) return number IS
618 --
619 l_proc varchar2(72) := g_package||'get_number';
620 --
621 begin
622 hr_utility.set_location(l_proc, 1);
623
624 if global_number.count>0 then
625 --
626 for i in global_number.first..global_number.last loop
627 if global_number(i).name = p_name then
628 hr_utility.set_location(
629 p_name||'='||global_number(i).value||' '||l_proc, 5);
630 return global_number(i).value;
631 end if;
632 end loop;
633 --
634 end if;
635
636 hr_utility.set_location(p_name||' Not Found '||l_proc, 10);
637 return null;
638 end get_number;
639 --
640 /* =====================================================================
641 Name : set_number
642 Purpose : To set the value of a numeric global variable
643 Returns : 0 if successful, 1 otherwise
644 ---------------------------------------------------------------------*/
645 function set_number
646 (p_name varchar2
647 ,p_value number) return number IS
648 --
649 l_proc varchar2(72) := g_package||'set_number';
650 j number;
651 --
652 begin
653 hr_utility.set_location(
654 'Setting '||p_name||'='||to_char(p_value)||' '||l_proc, 5);
655 j := 0;
656 if global_number.count > 0 then
657 for i in global_number.first..global_number.last loop
658 j := j + 1;
659 if global_number(i).name = p_name then
660 global_number(i).value := p_value;
661 return 0;
662 end if;
663 end loop;
664 end if;
665 global_number(j).name := p_name;
666 global_number(j).value := p_value;
667 return 0;
668 exception
669 when others then
670 hr_utility.set_location('Error '||l_proc, 10);
671 return 1;
672 end set_number;
673 /* =====================================================================
674 Name : get_date
675 Purpose : To retrieve the value of a date global variable
676 Returns : The value of the varibale if found, NULL otherwise
677 ---------------------------------------------------------------------*/
678 function get_date
679 (p_name varchar2) return date IS
680 --
681 l_proc varchar2(72) := g_package||'get_date';
682 --
683 begin
684 --
685 hr_utility.set_location(l_proc, 1);
686
687 if global_date.count>0 then
688 --
689 for i in global_date.first..global_date.last loop
690 if global_date(i).name = p_name then
691 hr_utility.set_location(
692 p_name||'='||global_date(i).value||' '||l_proc, 5);
693 return global_date(i).value;
694 end if;
695 end loop;
696 --
697 end if;
698 --
699 hr_utility.set_location(p_name||' Not Found '||l_proc, 10);
700 return null;
701 end get_date;
702 --
703 /* =====================================================================
704 Name : set_date
708 function set_date
705 Purpose : To set the value of a date global variable
706 Returns : 0 if successful, 1 otherwise
707 ---------------------------------------------------------------------*/
709 (p_name varchar2
710 ,p_value date) return number IS
711 --
712 l_proc varchar2(72) := g_package||'set_date';
713 --
714 j number;
715 begin
716 hr_utility.set_location(
717 'Setting '||p_name||'='||to_char(p_value,'DD-MM-YYYY')||' '||l_proc, 5);
718 j := 0;
719 if global_date.count > 0 then
720 for i in global_date.first..global_date.last loop
721 j := j + 1;
722 if global_date(i).name = p_name then
723 global_date(i).value := p_value;
724 return 0;
725 end if;
726 end loop;
727 end if;
728 global_date(j).name := p_name;
729 global_date(j).value := p_value;
730 return 0;
731 exception
732 when others then
733 hr_utility.set_location('Error '||l_proc, 10);
734 return 1;
735 end set_date;
736 --
737 /* =====================================================================
738 Name : get_text
739 Purpose : To retrieve the value of a text global variable
740 Returns : The value of the varibale if found, NULL otherwise
741 ---------------------------------------------------------------------*/
742 function get_text
743 (p_name varchar2) return varchar2 IS
744 --
745 l_proc varchar2(72) := g_package||'get_text';
746 --
747 begin
748
749 hr_utility.set_location(l_proc, 1);
750
751 if global_text.count>0 then
752 --
753 for i in global_text.first..global_text.last loop
754 if global_text(i).name = p_name then
755 hr_utility.set_location(
756 p_name||'='||global_text(i).value||' '||l_proc, 5);
757 return global_text(i).value;
758 end if;
759 end loop;
760 --
761 end if;
762
763 hr_utility.set_location(p_name||' Not Found '||l_proc, 10);
764 return null;
765 end get_text;
766 --
767 /* =====================================================================
768 Name : set_text
769 Purpose : To set the value of a text global variable
770 Returns : 0 if successful, 1 otherwise
771 ---------------------------------------------------------------------*/
772 function set_text
773 (p_name varchar2
774 ,p_value varchar2) return number IS
775 --
776 l_proc varchar2(72) := g_package||'set_text';
777 --
778 j number;
779 begin
780 hr_utility.set_location(
781 'Setting '||p_name||'='||p_value||' '||l_proc, 5);
782 j := 0;
783 if global_text.count > 0 then
784 for i in global_text.first..global_text.last loop
785 j := j + 1;
786 if global_text(i).name = p_name then
787 global_text(i).value := p_value;
788 return 0;
789 end if;
790 end loop;
791 end if;
792 global_text(j).name := p_name;
793 global_text(j).value := p_value;
794 return 0;
795 exception
796 when others then
797 hr_utility.set_location('Error '||l_proc, 10);
798 return 1;
799 end set_text;
800 --
801 /* =====================================================================
802 Name : isnull
803 Purpose : To evaluate whether a text variable is NULL
804 Returns : 'Y' if it is null, 'N' otherwise
805 ---------------------------------------------------------------------*/
806 function isnull (p_value varchar2) return varchar2 is
807 begin
808 if p_value is null then
809 return 'Y';
810 else
811 return 'N';
812 end if;
813 end isnull;
814 --
815 /* =====================================================================
816 Name : isnull
817 Purpose : To evaluate whether a numeric variable is NULL
818 Returns : 'Y' if it is null, 'N' otherwise
819 ---------------------------------------------------------------------*/
820 function isnull (p_value number) return varchar2 is
821 begin
822 return isnull(to_char(p_value));
823 end isnull;
824 --
825 /* =====================================================================
826 Name : isnull
827 Purpose : To evaluate whether a date variable is NULL
828 Returns : 'Y' if it is null, 'N' otherwise
829 ---------------------------------------------------------------------*/
830 function isnull (p_value date) return varchar2 is
831 begin
832 return isnull(to_char(p_value,'DDMMYYYY'));
833 end isnull;
834 --
835 /* =====================================================================
836 Name : remove_globals
837 Purpose : To delete all global variables
838 Returns : 0 if successful, 1 otherwise
839 ---------------------------------------------------------------------*/
840 function remove_globals return number is
841 --
842 l_proc varchar2(72) := g_package||'remove_globals';
843 --
844 init_global_number global_number_t;
845 init_global_date global_date_t;
846 init_global_text global_text_t;
847 --
848 begin
849 hr_utility.set_location(l_proc, 5);
850 global_number := init_global_number;
851 global_date := init_global_date;
852 global_text := init_global_text;
853 return 0;
854 exception
855 when others then
856 hr_utility.set_location('Error '||l_proc, 10);
857 return 1;
858 end remove_globals;
859 --
860 /* =====================================================================
861 Name : clear_globals
862 Purpose : To set the value of all global variables to NULL
863 Returns : 0 if successful, 1 otherwise
864 ---------------------------------------------------------------------*/
865 function clear_globals return number is
866 --
867 l_proc varchar2(72) := g_package||'clear_globals';
868 --
869 j number;
870 begin
871 hr_utility.set_location('Clearing Numeric Globals '||l_proc, 5);
872 j := 0;
873 if global_number.count > 0 then
874 for i in global_number.first..global_number.last loop
875 j := j + 1;
876 global_number(i).value := null;
877 end loop;
878 end if;
879 --
880 hr_utility.set_location('Clearing Date Globals '||l_proc, 10);
881 j := 0;
882 if global_date.count > 0 then
883 for i in global_date.first..global_date.last loop
884 j := j + 1;
885 global_date(i).value := null;
886 end loop;
887 end if;
888 --
889 hr_utility.set_location('Clearing Text Globals '||l_proc, 15);
890 j := 0;
891 if global_text.count > 0 then
892 for i in global_text.first..global_text.last loop
893 j := j + 1;
894 global_text(i).value := null;
895 end loop;
896 end if;
897 --
898 return 0;
899 exception
900 when others then
901 hr_utility.set_location('Error '||l_proc, 10);
902 return 1;
903 end clear_globals;
904 --
905 /* =====================================================================
906 Name : debug
907 Purpose : To output a string using DBMS_OUTPUT
908 Returns : 0 if successful, 1 otherwise
909 ---------------------------------------------------------------------*/
910 function debug(p_message varchar2) return number IS
911 --
912 l_proc varchar2(72) := g_package||'debug';
913 --
914 begin
915 hr_utility.set_location(l_proc, 5);
916 -- Bug#885806
917 -- dbms_output.put_line(p_message);
918 hr_utility.trace(p_message);
919 hr_utility.set_location(l_proc, 10);
920 return 0;
921 exception
922 when others then
923 hr_utility.set_location('Error '||l_proc, 10);
924 return 1;
925 end debug;
926 --
927 /* =====================================================================
928 Name : raise_error
929 Purpose : To raise an applications error
930 Returns : 0 if successful, 1 otherwise
931 ---------------------------------------------------------------------*/
932 function raise_error
933 (p_application_id number
934 ,p_message_name varchar2) return number is
935 --
936 l_proc varchar2(72) := g_package||'raise_error';
937 --
938 begin
939 hr_utility.set_location(l_proc, 10);
940 hr_formula_application_id := p_application_id;
941 hr_formula_message := p_message_name;
942 -- Start of 3294192
943 --raise hr_formula_error;
944 hr_utility.set_message(p_application_id, p_message_name);
945 hr_utility.raise_error;
946 -- End of 3294192
947 return 0;
948 end raise_error;
949 --
950 end per_formula_functions;
[Home] [Help]