1 package per_formula_functions AUTHID CURRENT_USER as
2 /* $Header: pefmlfnc.pkh 115.4 2002/12/05 16:31:17 pkakar ship $ */
3
4 TYPE varchar_80_tbl IS TABLE OF VARCHAR(80) INDEX BY BINARY_INTEGER;
5 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
7
8 --
9 /* =====================================================================
10 Name : Cache Formulas
11 Purpose : Populates the PL/SQL table with the given formula_name. If
12 the table is already cached, the formula is added.
13 Returns : Nothing.
14 ---------------------------------------------------------------------*/
15 procedure cache_formulas (p_formula_name in varchar2);
16 --
17 /* =====================================================================
18 Name : Cache Formulas (overloaded)
19 Purpose : Populates the PL/SQL table with the given formula_id. If
20 the table is already cached, the formula is added.
21 Returns : Nothing.
22 ---------------------------------------------------------------------*/
23 procedure cache_formulas (p_formula_id in number);
24 --
25 /* =====================================================================
26 Name : Get Cache Formula
27 Purpose : Gets the formula_id from a cached pl/sql table to prevent
28 a full table scan on ff_formulas_f for each person in the
29 payroll run.
30 Returns : formula_id if found, otherwise 0.
31 ---------------------------------------------------------------------*/
32 function get_cache_formula(p_formula_name in varchar2,
33 p_business_group_id in number,
34 p_calculation_date in date)
35 return number;
36 --
37 /* =====================================================================
38 Name : Get Cache Formula (overloaded)
39 Purpose : Gets the formula_name from a cached pl/sql table to prevent
40 a hit on ff_formulas_f and ff_compiled_info_f.
41 Returns : formula_name if found, otherwise null.
42 ---------------------------------------------------------------------*/
43 function get_cache_formula(p_formula_id in number,
44 p_calculation_date in date)
45 return varchar2;
46 --
47 /* =====================================================================
48 Name : Get Formula
49 Purpose : Gets the formula_id from a cached pl/sql table to prevent
50 a full table scan on ff_formulas_f for each person in the
51 payroll run.
52 Returns : formula_id if found, otherwise null.
53 ---------------------------------------------------------------------*/
54 function get_formula(p_formula_name in varchar2,
55 p_business_group_id in number,
56 p_calculation_date in date)
57 return number;
58 --
59 /* =====================================================================
60 Name : Get Formula (overloaded)
61 Purpose : Gets the formula_name from a cached pl/sql table to prevent
62 a hit on ff_formulas_f and ff_compiled_info_f.
63 Returns : formula_name if found, otherwise null.
64 ---------------------------------------------------------------------*/
65 function get_formula(p_formula_id in number,
66 p_calculation_date in date)
67 return varchar2;
68 --
69 /* =====================================================================
70 Name : Loop Control
71 Purpose : To repeatedly run a formula while the CONTINUE_PROCESSING_FLAG
72 output parameter is set to 'Y'. If the value is 'N' then the
73 function will end normally otherwise it will abort.
74 Returns : 0 if successful, 1 otherwise
75 ---------------------------------------------------------------------*/
76 function loop_control(p_business_group_id number
77 ,p_calculation_date date
78 ,p_assignment_id number
79 ,p_payroll_id number
80 ,p_accrual_plan_id number
81 ,p_formula_name varchar2) return number;
82 --
83 /* =====================================================================
84 Name : call_formula
85 Purpose : To run a named formula, with no inputs and no outputs
86 Returns : 0 if successful, 1 otherwise
87 ---------------------------------------------------------------------*/
88 function call_formula
89 (p_business_group_id number
90 ,p_calculation_date date
91 ,p_assignment_id number
92 ,p_payroll_id number
93 ,p_accrual_plan_id number
94 ,p_formula_name varchar2) return number;
95 --
96 /* =====================================================================
97 Name : run_formula
98 Purpose : To run a named formula, handling the input and output
99 parameters.
100 ---------------------------------------------------------------------*/
101 procedure run_formula
102 (p_formula_name varchar2
103 ,p_business_group_id number
104 ,p_calculation_date date
105 ,p_inputs ff_exec.inputs_t
106 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
107 --
108 /* =====================================================================
109 Name : run_formula
110 Purpose : To run a named formula, handling the input and output
111 parameters.
112 ---------------------------------------------------------------------*/
113 procedure run_formula
114 (p_formula_id number
115 ,p_calculation_date date
116 ,p_inputs ff_exec.inputs_t
117 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
118 --
119 /* =====================================================================
120 Name : get_number
121 Purpose : To retrieve the value of a numeric global variable
122 Returns : The value of the varibale if found, NULL otherwise
123 ---------------------------------------------------------------------*/
124 function get_number
125 (p_name varchar2) return number;
126 --
127 /* =====================================================================
128 Name : set_number
129 Purpose : To set the value of a numeric global variable
130 Returns : 0 if successful, 1 otherwise
131 ---------------------------------------------------------------------*/
132 function set_number
133 (p_name varchar2
134 ,p_value number) return number;
135 --
136 /* =====================================================================
137 Name : get_date
138 Purpose : To retrieve the value of a date global variable
139 Returns : The value of the varibale if found, NULL otherwise
140 ---------------------------------------------------------------------*/
141 function get_date
142 (p_name varchar2) return date;
143 --
144 /* =====================================================================
145 Name : set_date
146 Purpose : To set the value of a date global variable
147 Returns : 0 if successful, 1 otherwise
148 ---------------------------------------------------------------------*/
149 function set_date
150 (p_name varchar2
151 ,p_value date) return number;
152 --
153 /* =====================================================================
154 Name : get_text
155 Purpose : To retrieve the value of a text global variable
156 Returns : The value of the varibale if found, NULL otherwise
157 ---------------------------------------------------------------------*/
158 function get_text
159 (p_name varchar2) return varchar2;
160 --
161 /* =====================================================================
162 Name : set_text
163 Purpose : To set the value of a text global variable
164 Returns : 0 if successful, 1 otherwise
165 ---------------------------------------------------------------------*/
166 function set_text
167 (p_name varchar2
168 ,p_value varchar2) return number;
169 --
170 /* =====================================================================
171 Name : isnull
172 Purpose : To evaluate whether a text variable is NULL
173 Returns : 'Y' if it is null, 'N' otherwise
174 ---------------------------------------------------------------------*/
175 function isnull
176 (p_value varchar2) return varchar2;
177 --
178 /* =====================================================================
179 Name : isnull
180 Purpose : To evaluate whether a numeric variable is NULL
181 Returns : 'Y' if it is null, 'N' otherwise
182 ---------------------------------------------------------------------*/
183 function isnull
184 (p_value number) return varchar2;
185 --
186 /* =====================================================================
187 Name : isnull
188 Purpose : To evaluate whether a date variable is NULL
189 Returns : 'Y' if it is null, 'N' otherwise
190 ---------------------------------------------------------------------*/
191 function isnull
192 (p_value date) return varchar2;
193 --
194 /* =====================================================================
195 Name : remove_globals
196 Purpose : To delete all global variables
197 Returns : 0 if successful, 1 otherwise
198 ---------------------------------------------------------------------*/
199 function remove_globals return number;
200 --
201 /* =====================================================================
202 Name : clear_globals
203 Purpose : To set the value of all global variables to NULL
204 Returns : 0 if successful, 1 otherwise
205 ---------------------------------------------------------------------*/
206 function clear_globals return number;
207 --
208 /* =====================================================================
209 Name : debug
210 Purpose : To output a string using DBMS_OUTPUT
211 Returns : 0 if successful, 1 otherwise
212 ---------------------------------------------------------------------*/
213 function debug
214 (p_message varchar2) return number;
215 --
216 /* =====================================================================
217 Name : raise_error
218 Purpose : To raise an applications error
219 Returns : 0 if successful, 1 otherwise
220 ---------------------------------------------------------------------*/
221 function raise_error
222 (p_application_id number
223 ,p_message_name varchar2) return number;
224 --
225 end per_formula_functions;
[Home] [Help]