1 PACKAGE BODY OKL_FORMULAEVALUATE_PVT AS
2 /* $Header: OKLREVAB.pls 120.1 2005/10/30 04:33:36 appldev noship $ */
3
4
5 -- Start of comments
6 --
7 -- Function Name : EVA_IsFormulaExists
8 -- Description : Function checks whether the formula
9 -- exists in the system or not.
10 -- Business Rules :
11 -- Parameters : fma_id - formula identifier.
12 -- Returns boolean - TRUE/FALSE
13 -- Version : 1.0
14 --
15 -- End of comments
16
17 FUNCTION EVA_IsFormulaExists(
18 p_fma_id IN NUMBER )
19 RETURN BOOLEAN
20 IS
21 CURSOR fma_cur
22 IS
23 SELECT
24 '1'
25 FROM
26 okl_formulae_v
27 WHERE
28 id = p_fma_id;
29
30 l_result_var VARCHAR2(1) := '0';
31 l_retcode BOOLEAN := FALSE;
32 BEGIN
33 /** SBALASHA001 -
34 INFO: Close if the cursor is already open. **/
35 IF fma_cur%ISOPEN
36 THEN
37 CLOSE fma_cur;
38 END IF;
39
40 /** SBALASHA001 -
41 INFO: Check whether the given fma_id exists,
42 based on the results update the boolean flag
43 and return it to the calling API. **/
44 OPEN fma_cur;
45 FETCH fma_cur INTO l_result_var;
46 IF ( l_result_var <> '1' )
47 THEN
48 l_retcode := FALSE;
49 ELSE
50 l_retcode := TRUE;
51 END IF;
52 CLOSE fma_cur;
53 RETURN l_retcode;
54
55 END EVA_IsFormulaExists;
56
57
58 -- Start of comments
59 --
60 -- Function Name : EVA_GetAllFunctions
61 -- Description : Function populates function id, name, source and type
62 -- in Function_tbl.
63 -- Business Rules :
64 -- Parameters :
65 -- p_fma_id - formula identifier.
66 -- x_function_tbl - function table.
67 -- Returns NUMBER.
68 -- Version : 1.0
69 --
70 -- End of comments
71
72 FUNCTION EVA_GetAllFunctions(
73 p_fma_id IN NUMBER
74 , x_function_tbl OUT NOCOPY Function_tbl )
75 RETURN NUMBER
76 IS
77 CURSOR dsf_fma_cur
78 IS
79 SELECT
80 dsfv.id,
81 dsfv.name,
82 dsfv.source,
83 dsfv.fnctn_code
84 FROM
85 okl_formulae_v fmav,
86 okl_data_src_fnctns_v dsfv,
87 okl_operands_v opdv,
88 okl_fmla_oprnds_v fodv
89 WHERE
90 fmav.id = p_fma_id
91 AND fmav.id = fodv.fma_id
92 AND fodv.opd_id = opdv.id
93 AND opdv.dsf_id = dsfv.id;
94
95 l_Count NUMBER := 1;
96
97 BEGIN
98
99 FOR dsf_fma_rec in dsf_fma_cur
100 LOOP
101 x_function_tbl(l_Count).function_id :=
102 dsf_fma_rec.id;
103 x_function_tbl(l_Count).function_name :=
104 dsf_fma_rec.name;
105 x_function_tbl(l_Count).function_source :=
106 dsf_fma_rec.source;
107 x_function_tbl(l_Count).function_code :=
108 dsf_fma_rec.fnctn_code;
109 l_Count := l_Count + 1;
110 END LOOP;
111
112 END EVA_GetAllFunctions;
113
114
115 -- Start of comments
116 --
117 -- Function Name : EVA_GetParameterIDs
118 -- Description : Function populates parameter id and parameter name
119 -- in parameter table.
120 -- Business Rules :
121 -- Parameters : p_fma_id - formula identifier.
122 -- p_ctx_parameter_tbl - Context parameter table.
123 -- Returns NUMBER.
124 -- Version : 1.0
125 --
126 -- End of comments
127
128 FUNCTION EVA_GetParameterIDs(
129 p_fma_id IN NUMBER,
130 p_ctx_parameter_tbl OUT NOCOPY CtxParameter_tbl )
131 RETURN NUMBER
132 IS
133 CURSOR parameters_cur
134 IS
135 SELECT
136 cgrpv.pmr_id, pmrv.name
137 FROM
138 okl_formulae_v fmav,
139 okl_context_groups_v cgrv,
140 okl_cntx_grp_prmtrs_v cgrpv,
141 okl_parameters_v pmrv
142 WHERE
143 fmav.id = p_fma_id
144 AND cgrv.id = fmav.cgr_id
145 AND fmav.cgr_id = cgrpv.cgr_id
146 AND pmrv.id = cgrpv.pmr_id;
147
148 l_count NUMBER := 1;
149 BEGIN
150
151 /** SBALASHA001 -
152 INFO: Loop thru' the parameter cursor to populate
153 parameter table with parameter id and name. **/
154 FOR l_parameter_rec in parameters_cur
155 LOOP
156 p_ctx_parameter_tbl(l_count).parameter_id :=
157 l_parameter_rec.pmr_id;
158 p_ctx_parameter_tbl(l_count).parameter_name :=
159 l_parameter_rec.name;
160 l_count := l_count + 1;
161 END LOOP;
162
163 RETURN l_count;
164
165 END EVA_GetParameterIDs;
166
167 -- Start of comments
168 --
169 -- Function Name : EVA_ExecuteFunction
170 -- Description : Prepares a dynamic SQL and executes a given function.
171 -- Expects a SCALAR NUMERIC value to be returned from
172 -- the executed function.
173 -- This is a generic API used by both parameter
174 -- evaluator and operand/function evaluator.
175 -- This has been made as a seperate function inorder to
176 -- give the flexibility for the future developer to change
177 -- the way it executes a function.
178 --
179 -- Business Rules :
180 -- Parameters :
181 -- p_FunctionString - Function to be executed.
182 -- Returns NUMBER.
183 -- Version : 1.0
184 --
185 -- End of comments
186
187 FUNCTION EVA_ExecuteFunction(
188 p_function_string IN VARCHAR2)
189 RETURN NUMBER
190 IS
191 l_QueryString VARCHAR2(720);
192 l_RetValue NUMBER;
193 BEGIN
194
195 /** SBALASHA001 -
196 INFO: execute the function **/
197
198 l_QueryString := 'select ' || p_function_string || ' from dual';
199
200 EXECUTE IMMEDIATE l_QueryString INTO l_RetValue;
201
202 return l_RetValue;
203
204 END EVA_ExecuteFunction;
205
206 -- Start of comments
207 --
208 -- Function Name : EVA_GetFunctionString
209 -- Description : To get the PL/SQL function name
210 -- Business Rules :
211 -- Parameters :
212 -- p_dsf_id - Data Source Function identifier.
213 -- p_contract_id - Contract identifier.
214 -- p_line_id - Line identifier.
215 -- p_ctx_parameter_tbl - Parameter Table.
216 -- Returns CtxParameter_tbl.
217 -- Version : 1.0
218 --
219 -- End of comments
220
221 FUNCTION EVA_GetFunctionString(
222 p_function_rec Function_rec
223 ,p_contract_id NUMBER
224 ,p_line_id NUMBER
225 ,p_ctx_parameter_tbl CtxParameter_tbl )
226 RETURN VARCHAR2
227 IS
228 CURSOR dsf_pmr_cur
229 IS
230 SELECT
231 fprv.pmr_id, fprv.sequence_number
232 FROM
233 okl_parameters_v pmrv,
234 okl_data_src_fnctns_v dsfv,
235 okl_fnctn_prmtrs_v fprv
236 WHERE
237 dsfv.id = p_function_rec.function_id
238 AND dsfv.id = fprv.dsf_id
239 AND fprv.pmr_id = pmrv.id
240 ORDER BY
241 fprv.sequence_number;
242 l_FunctionString VARCHAR2(720);
243 l_count NUMBER;
244
245 BEGIN
246
247 /** SBALASHA001 -
248 INFO: Build a function string for a given data
249 source function.
250 ex: <function_name>(<contract_id>, <line_id>,
251 and other parameters...). **/
252
253
254 l_FunctionString := p_function_rec.function_source || '(' ||
255 TO_CHAR(p_contract_id) || ', ' ||
256 TO_CHAR(p_line_id);
257 FOR dsf_pmr_rec in dsf_pmr_cur
258 LOOP
259 FOR l_count IN 1 .. p_ctx_parameter_tbl.count
260 LOOP
261 l_FunctionString := l_FunctionString || ', ';
262 IF ( p_ctx_parameter_tbl(l_count).parameter_id
263 = dsf_pmr_rec.pmr_id)
264 THEN
265 l_FunctionString :=
266 l_FunctionString ||
267 p_ctx_parameter_tbl(l_count).parameter_value;
268 EXIT;
269 END IF;
270 END LOOP;
271 END LOOP;
272
273 l_FunctionString := l_FunctionString || ')';
274
275 RETURN ( l_FunctionString );
276
277 END EVA_GetFunctionString;
278
279 -- Start of comments
280 --
281 -- Function Name : EVA_EvaluateParameters
282 -- Description : Function populates parameter value in parameter table
283 -- Business Rules :
284 -- Parameters :
285 -- p_fma_id - formula identifier.
286 -- p_contract_id - contract id.
287 -- p_line_id - line id.
288 -- p_ctx_parameter_tbl - Parameter Table.
289 -- Returns CtxParameter_tbl.
290 -- Version : 1.0
291 --
292 -- End of comments
293
294 FUNCTION EVA_EvaluateParameters(
295 p_contract_id IN NUMBER
296 ,p_line_id IN NUMBER DEFAULT NULL
297 ,p_ctx_parameter_tbl IN OUT NOCOPY CtxParameter_tbl )
298 RETURN CtxParameter_tbl
299 IS
300 l_FunctionString VARCHAR2(720);
301 l_count NUMBER;
302 x_ctx_parameter_tbl CtxParameter_tbl;
303 BEGIN
304
305 /** SBALASHA001 -
306 INFO: Loop thru' the parameter table to execute
307 the parameter function and to populate the parameter
308 value. **/
309 FOR l_count IN 1 .. p_ctx_parameter_tbl.count
310 LOOP
311 l_FunctionString :=
312 G_FNCT_PKG_NAME || '.' || G_FNCT_PREFIX ||
313 p_ctx_parameter_tbl(l_count).parameter_name ||
314 G_FNCT_SUFFIX;
315
316 IF ( p_line_id IS NOT NULL ) -- CHG001
317 THEN
318 l_FunctionString := l_FunctionString || '(' ||
319 TO_CHAR(p_contract_id) || ', ' ||
320 TO_CHAR(p_line_id) || ')';
321 ELSE
322 l_FunctionString := l_FunctionString || '(' ||
323 TO_CHAR(p_contract_id) ||','||''''||''''||')';
324 END IF;
325 p_ctx_parameter_tbl(l_count).parameter_value :=
326 EVA_ExecuteFunction(
327 p_function_string => l_FunctionString);
328
329 END LOOP;
330
331 RETURN (p_ctx_parameter_tbl);
332
333 END EVA_EvaluateParameters;
334
335 -- Start of comments
336 --
337 -- Procedure Name : EVA_GetParameterValues
338 -- Description : Evaluates value for all the context
339 -- parameters attached to a formula.
340 -- Business Rules :
341 -- Parameters : fma_id - formula identifier
342 -- x_ctx_parameter_tbl - Parameter table
343 -- that will have the evaluated values.
344 -- Version : 1.0
345 --
346 -- End of comments
347
348 PROCEDURE EVA_GetParameterValues(
349 p_api_version IN NUMBER
350 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
351 ,x_return_status OUT NOCOPY VARCHAR2
352 ,x_msg_count OUT NOCOPY NUMBER
353 ,x_msg_data OUT NOCOPY VARCHAR2
354 ,p_fma_id IN NUMBER
355 ,p_contract_id IN NUMBER
356 ,x_ctx_parameter_tbl OUT NOCOPY CtxParameter_tbl
357 ,p_line_id IN NUMBER DEFAULT NULL )
358 IS
359 l_formula_exist BOOLEAN := FALSE;
360 l_count NUMBER;
361 BEGIN
362 x_return_status := OKL_API.G_RET_STS_SUCCESS;
363
364 /** SBALASHA001 -
365 INFO: Validate whether the formula
366 has context and the formula exists in
367 the system or not **/
368 l_formula_exist := EVA_IsFormulaExists(p_fma_id => p_fma_id);
369
370 IF ( l_formula_exist = TRUE )
371 THEN
372 /** SBALASHA001 -
373 INFO: Formula Exists, now call
374 EVA_GetParameterIDs function to populate
375 just the parameter ids and not the values **/
376
377 l_count := EVA_GetParameterIDs(
378 p_fma_id => p_fma_id,
379 p_ctx_parameter_tbl => x_ctx_parameter_tbl );
380 ELSE
381 /** SBALASHA001 -
382 INFO: raise formula not found exception **/
383 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
384 p_msg_name => G_FORMULA_NOT_FOUND,
385 p_token1 => G_EVALUATE_TOKEN,
386 p_token1_value => p_fma_id);
387 -- notify UNEXPECTED error for calling API.
388 x_return_status := OKL_API.G_RET_STS_ERROR;
389 END IF;
390
391 /** SBALASHA001 -
392 INFO: Call Evaluate Parameter function to
393 actually do the evaluation and populate
394 parameter table with values **/
395 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS )
396 THEN
397 x_ctx_parameter_tbl :=
398 EVA_EvaluateParameters(
399 p_contract_id => p_contract_id,
400 p_line_id => p_line_id,
401 p_ctx_parameter_tbl => x_ctx_parameter_tbl);
402 END IF;
403
404 EXCEPTION
405 WHEN G_EXCEPTION_HALT_VALIDATION THEN
406 -- no processing necessary;
407 NULL;
408 WHEN OTHERS THEN
409 -- store SQL error message on message stack
410 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
411 p_msg_name => g_unexpected_error,
412 p_token1 => g_sqlcode_token,
413 p_token1_value => sqlcode,
414 p_token2 => g_sqlerrm_token,
415 p_token2_value => sqlerrm);
416 -- notify UNEXPECTED error for calling API.
417 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
418 END EVA_GetParameterValues;
419
420
421 -- Start of comments
422 --
423 -- Procedure Name : EVA_GetFunctionValue
424 -- Description : Evaluates value for a given function using
425 -- the evaluated context parameters.
426 -- Business Rules :
427 -- Parameters : p_dsf_id - Data Source Function identifier.
428 -- x_ctx_parameter_tbl - Parameter table
429 -- that will have the evaluated values.
430 -- Version : 1.0
431 --
432 -- End of comments
433
434 PROCEDURE EVA_GetFunctionValue(
435 p_api_version IN NUMBER
436 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
437 ,x_return_status OUT NOCOPY VARCHAR2
438 ,x_msg_count OUT NOCOPY NUMBER
439 ,x_msg_data OUT NOCOPY VARCHAR2
440 ,p_fma_id IN NUMBER
441 ,p_contract_id IN NUMBER
442 ,p_line_id IN NUMBER
443 ,p_ctx_parameter_tbl IN CtxParameter_tbl
444 ,x_function_tbl OUT NOCOPY Function_tbl)
445 IS
446 l_FunctionString VARCHAR2(720);
447 l_dummy NUMBER;
448 BEGIN
449 x_return_status := OKL_API.G_RET_STS_SUCCESS;
450
451 /** SBALASHA001 -
452 INFO: Get all the PL/SQL function name for the given
453 formula identifier. **/
454
455 l_dummy := EVA_GetAllFunctions( p_fma_id => p_fma_id,
456 x_function_tbl => x_function_tbl );
457
458 FOR l_Count IN 1 .. x_function_tbl.count
459 LOOP
460 l_FunctionString :=
461 EVA_GetFunctionString(
462 p_function_rec => x_function_tbl(l_Count),
463 p_contract_id => p_contract_id,
464 p_line_id => p_line_id,
465 p_ctx_parameter_tbl => p_ctx_parameter_tbl);
466 x_function_tbl(l_Count).function_value :=
467 EVA_ExecuteFunction(
468 p_function_string => l_FunctionString);
469 END LOOP;
470
471 EXCEPTION
472 WHEN G_EXCEPTION_HALT_VALIDATION THEN
473 -- no processing necessary;
474 null;
475 WHEN OTHERS THEN
476 -- store SQL error message on message stack
477 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
478 p_msg_name => g_unexpected_error,
479 p_token1 => g_sqlcode_token,
480 p_token1_value => sqlcode,
481 p_token2 => g_sqlerrm_token,
482 p_token2_value => sqlerrm);
483 -- notify UNEXPECTED error for calling API.
484 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
485 END EVA_GetFunctionValue;
486
487 END OKL_FORMULAEVALUATE_PVT;
[Home] [Help]