[Home] [Help]
PACKAGE BODY: APPS.OKL_EXECUTE_FORMULA_PVT
Source
1 PACKAGE BODY OKL_EXECUTE_FORMULA_PVT AS
2 /* $Header: OKLRFMLB.pls 120.6 2007/05/25 11:55:26 prasjain noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- Start of comments
9 --
10 -- Procedure Name : execute_eligibility_Criteria
11 -- Description : Evaluates the function and returns a scalar value.
12 -- Business Rules :
13 -- Parameters : Function Name
14 -- Version : 1.0
15 -- End of comments
16
17
18 PROCEDURE execute_eligibility_Criteria(p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,p_function_name IN okl_data_src_fnctns_v.name%TYPE
24 ,x_value OUT NOCOPY NUMBER
25 ) IS
26
27 -- Exception declarations
28 FUNCTION_DATA_INVALID EXCEPTION;
29 FUNCTION_RETURNS_NULL EXCEPTION;
30
31 -- Local Variable Declarations
32 l_value NUMBER;
33 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
34 l_return_status VARCHAR2(1);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(2000);
37 l_evaluated_string okl_formulae_v.formula_string%TYPE;
38 l_no_dml_message VARCHAR2(200) := 'OKL_FORMULAE_NO_DML';
39 l_function_name okl_data_src_fnctns_v.name%TYPE;
40 l_function_source okl_data_src_fnctns_v.source%TYPE;
41 l_api_version CONSTANT NUMBER := 1.0;
42
43 l_program_name CONSTANT VARCHAR2(30) := 'execute_eligibility_Criteria';
44 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
45
46 l_flag BOOLEAN DEFAULT FALSE;
47
48 CURSOR data_src_fnctns_csr(cp_function_name IN okl_data_src_fnctns_v.name%TYPE)
49 IS
50 SELECT source
51 FROM okl_data_src_fnctns_v
52 WHERE name = cp_function_name
53 AND fnctn_code = 'ELIGIBILITY_CRITERIA';
54
55 BEGIN
56
57
58 l_function_name := p_function_name;
59
60 FOR l_data_src_fnctns_csr IN data_src_fnctns_csr(cp_function_name => l_function_name)
61 LOOP
62 l_flag := TRUE;
63 l_function_source := l_data_src_fnctns_csr.source;
64 EXIT;
65 END LOOP;
66
67
68 IF l_flag THEN
69 l_flag := FALSE;
70 ELSE
71 RAISE NO_DATA_FOUND;
72 END IF;
73
74 l_evaluated_string := l_function_source;
75 --DBMS_OUTPUT.PUT_LINE('l_evaluated_string '||l_evaluated_string);
76
77
78 IF l_evaluated_string IS NULL THEN
79 RAISE FUNCTION_DATA_INVALID;
80 ELSE
81 l_evaluated_string := 'SELECT '||l_evaluated_string ||' FROM dual';
82 END IF;
83
84 EXECUTE IMMEDIATE l_evaluated_string
85 INTO l_value;
86 -- DBMS_OUTPUT.PUT_LINE('function eval is '||l_evaluated_string||' values is '||l_value);
87
88 IF l_value IS NULL THEN
89 RAISE FUNCTION_RETURNS_NULL;
90 ELSE
91 x_return_status := OKC_API.G_RET_STS_SUCCESS;
92 x_value := l_value;
93 END IF;
94
95 EXCEPTION
96 WHEN FUNCTION_RETURNS_NULL THEN
97 OKC_API.SET_MESSAGE(p_app_name => g_app_name
98 ,p_msg_name => 'OKL_FUNCTION_RETURNS_NULL'
99 ,p_token1 => 'FUNCTION'
100 ,p_token1_value => l_function_name );
101 x_return_status := OKC_API.G_RET_STS_ERROR;
102
103 WHEN FUNCTION_DATA_INVALID THEN
104 OKC_API.SET_MESSAGE(p_app_name => g_app_name
105 ,p_msg_name => g_function_data_invalid
106 ,p_token1 => 'FUNCTION'
107 ,p_token1_value => l_function_name );
108 x_return_status := OKC_API.G_RET_STS_ERROR;
109
110 WHEN NO_DATA_FOUND THEN
111 OKC_API.SET_MESSAGE(p_app_name => g_app_name
112 ,p_msg_name => g_invalid_function
113 ,p_token1 => 'FUNCTION'
114 ,p_token1_value => l_function_name );
115 x_return_status := OKC_API.G_RET_STS_ERROR;
116
117 WHEN OTHERS THEN
118 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
119 p_msg_name => G_DB_ERROR,
120 p_token1 => G_PROG_NAME_TOKEN,
121 p_token1_value => l_api_name,
122 p_token2 => G_SQLCODE_TOKEN,
123 p_token2_value => sqlcode,
124 p_token3 => G_SQLERRM_TOKEN,
125 p_token3_value => sqlerrm);
126
127 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
128 END execute_eligibility_Criteria;
129
130
131 -- Start of comments
132 --
133 -- Procedure Name : execute_function
134 -- Description : Evaluates the function and returns a scalar value used internally by package
135 -- Business Rules :
136 -- Parameters : Function_ID, Table of Context parameter Values
137 -- Version : 1.0
138 -- End of comments
139
140 PROCEDURE execute_function(p_api_version IN NUMBER
141 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
142 ,x_return_status OUT NOCOPY VARCHAR2
143
144 ,x_msg_count OUT NOCOPY NUMBER
145 ,x_msg_data OUT NOCOPY VARCHAR2
146 ,p_dsf_id IN okl_operands_v.dsf_id%TYPE
147 ,p_ctx_parameter_tbl IN ctxt_parameter_tbl_type
148 ,p_contract_id IN okl_k_headers_v.id%TYPE
149 ,p_line_id IN okl_k_lines_v.id%TYPE
150 ,x_value OUT NOCOPY NUMBER
151
152
153 ) IS
154 -- Type Declarations
155 TYPE fnctn_prmtrs_val_rec_type IS RECORD(pmr_id okl_fnctn_prmtrs_v.pmr_id%TYPE
156 ,value okl_fnctn_prmtrs_v.value%TYPE
157 ,fpr_type okl_fnctn_prmtrs_v.fpr_type%TYPE
158 );
159 TYPE fnctn_prmtrs_val_tbl_type IS TABLE OF fnctn_prmtrs_val_rec_type
160 INDEX BY BINARY_INTEGER;
161
162 -- Exception declarations
163 NO_DML_EXCEPTION EXCEPTION;
164 NO_CONSTANT_SPECIFIED EXCEPTION;
165 FUNCTION_DATA_INVALID EXCEPTION;
166 FUNCTION_DOES_NOT_EXIST EXCEPTION;
167 FUNCTION_RETURNS_NULL EXCEPTION;
168
169 PRAGMA EXCEPTION_INIT(NO_DML_EXCEPTION,-14551);
170 PRAGMA EXCEPTION_INIT(FUNCTION_DOES_NOT_EXIST,-904);
171
172 -- Local Variable Declarations
173 l_formula_id okl_formulae_v.id%TYPE;
174 l_contract_id okl_k_headers_v.id%TYPE;
175 l_line_id okl_k_lines_v.id%TYPE;
176 l_value NUMBER;
177 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
178 l_return_status VARCHAR2(1);
179 l_msg_count NUMBER;
180 l_msg_data VARCHAR2(2000);
181 l_formula_string okl_formulae_v.formula_string%TYPE;
182 l_evaluated_string okl_formulae_v.formula_string%TYPE;
183 l_evaluated_string2 okl_formulae_v.formula_string%TYPE;
184 l_function_source okl_data_src_fnctns_v.source%TYPE;
185 l_function_source2 okl_data_src_fnctns_v.source%TYPE;
186 l_function_source3 okl_data_src_fnctns_v.source%TYPE;
187 l_fnctn_prmtrs_val_tbl fnctn_prmtrs_val_tbl_type;
188 l_no_dml_message VARCHAR2(200) := 'OKL_FORMULAE_NO_DML';
189 l_dsf_id okl_data_src_fnctns_v.id%TYPE;
190 i PLS_INTEGER DEFAULT 1;
191 j PLS_INTEGER DEFAULT 1;
192 l_api_version CONSTANT NUMBER := 1.0;
193 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_FUNCTION';
194 l_flag BOOLEAN DEFAULT FALSE;
195 l_function_name okl_data_src_fnctns_v.name%TYPE;
196 v1c varchar2(400);
197 v2c varchar2(400);
198 v3c varchar2(400);
199 v4c varchar2(400);
200 v5c varchar2(400);
201
202 CURSOR data_src_fnctns_csr(cp_dsf_id IN okl_data_src_fnctns_v.id%TYPE) IS
203 SELECT fnctn_code
204 ,name
205 ,source
206 FROM okl_data_src_fnctns_v
207 WHERE id = cp_dsf_id;
208
209 CURSOR fnctn_prmtrs_csr(cp_dsf_id IN okl_fnctn_prmtrs_v.dsf_id%TYPE) IS
210 SELECT dsf_id
211 ,pmr_id
212 ,sequence_number
213 ,value
214 ,fpr_type
215 FROM okl_fnctn_prmtrs_v
216 WHERE dsf_id = cp_dsf_id
217 ORDER BY sequence_number;
218
219 BEGIN
220 IF (G_DEBUG_ENABLED = 'Y') THEN
221 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
222 END IF;
223
224 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
225 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In execute_function... p_dsf_id=' || p_dsf_id);
226 END IF;
227 l_dsf_id := p_dsf_id;
228 l_contract_id := p_contract_id;
229 l_line_id := p_line_id;
230 FOR l_data_src_fnctns_csr IN data_src_fnctns_csr(cp_dsf_id => l_dsf_id)
231 LOOP
232 l_flag := TRUE;
233 l_function_source := l_data_src_fnctns_csr.source;
234 l_function_name := l_data_src_fnctns_csr.name;
235 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
236 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_function_source=' || l_function_source || ' l_function_name='||l_function_name);
237 END IF;
238 EXIT;
239 END LOOP;
240 IF l_flag THEN
241 l_flag := FALSE;
242 ELSE
243 RAISE NO_DATA_FOUND;
244 END IF;
245 i := 1;
246 FOR l_fnctn_prmtrs_csr IN fnctn_prmtrs_csr(cp_dsf_id => l_dsf_id)
247 LOOP
248 l_fnctn_prmtrs_val_tbl(i).pmr_id := l_fnctn_prmtrs_csr.pmr_id;
249 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
250 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'pmr_id=' || l_fnctn_prmtrs_val_tbl(i).pmr_id || ' fpr_type='||l_fnctn_prmtrs_csr.fpr_type);
251 END IF;
252
253 IF l_fnctn_prmtrs_csr.fpr_type='STATIC' THEN
254 IF l_fnctn_prmtrs_csr.value IS NULL THEN
255 RAISE NO_CONSTANT_SPECIFIED;
256 ELSE
257 l_fnctn_prmtrs_val_tbl(i).value := l_fnctn_prmtrs_csr.value;
258 END IF;
259 ELSE
260 IF p_ctx_parameter_tbl.EXISTS(1) THEN
261 j := p_ctx_parameter_tbl.FIRST;
262
263 FOR j IN p_ctx_parameter_tbl.FIRST .. p_ctx_parameter_tbl.LAST
264 LOOP
265 IF p_ctx_parameter_tbl(j).parameter_id = l_fnctn_prmtrs_csr.pmr_id THEN
266 l_fnctn_prmtrs_val_tbl(i).value := p_ctx_parameter_tbl(j).parameter_value;
267 EXIT;
268 ELSE
269
270 NULL;
271 END IF;
272 END LOOP;
273 END IF;
274 END IF;
275 i := i+1;
276 END LOOP;
277 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_fnctn_prmtrs_val_tbl.count='||l_fnctn_prmtrs_val_tbl.count);
279 END IF;
280
281 l_function_source2 := l_function_source;
282 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
283 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'1.l_function_source2=' || l_function_source2);
284 END IF;
285 IF l_line_id IS NULL THEN
286 --Changed by kthiruva for the bug 3671523 . l_contract_id and l_line_id are passed as strings
287 l_function_source := l_function_source ||'('||''''||TO_CHAR(l_contract_id)||''''||','||''''||'''';
288 ELSE
289 --Changed by kthiruva for the bug 3671523 . l_contract_id and l_line_id are passed as strings
290 l_function_source := l_function_source ||'('||''''||TO_CHAR(l_contract_id)||''''||','||''''||TO_CHAR(l_line_id)||'''';
291 END IF;
292 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
293 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_function_source=' || l_function_source);
294 END IF;
295 --l_function_source2 := 'begin :3 := ' || l_function_source2 || '(:1, :2); end;'
296 l_function_source2 := 'begin :3 := ' || l_function_source2 || '(:1, :2';
297 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
298 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'2.l_function_source2=' || l_function_source2);
299 END IF;
300
301 /*
302 IF l_line_id IS NULL THEN
303 l_function_source := l_function_source ||'('||TO_CHAR(l_contract_id)||','||''''||'''';
304 ELSE
305 l_function_source := l_function_source ||'('||TO_CHAR(l_contract_id)||','||TO_CHAR(l_line_id);
306 END IF;
307 */
308
309
310 IF l_fnctn_prmtrs_val_tbl.EXISTS(1) THEN
311 l_evaluated_string := l_function_source ||',';
312 --l_evaluated_string2 := l_function_source2 ||',';
313 i := l_fnctn_prmtrs_val_tbl.FIRST;
314 FOR i IN l_fnctn_prmtrs_val_tbl.FIRST .. l_fnctn_prmtrs_val_tbl.LAST
315 LOOP
316 IF i = l_fnctn_prmtrs_val_tbl.FIRST THEN
317 l_evaluated_string := l_evaluated_string ||l_fnctn_prmtrs_val_tbl(i).value;
318 ELSE
319 l_evaluated_string := l_evaluated_string ||','||l_fnctn_prmtrs_val_tbl(i).value;
320 END IF;
321 l_evaluated_string2 := l_function_source2 ||', :' || i+2 ;
322 END LOOP;
323 l_evaluated_string := l_evaluated_string ||')';
324 l_evaluated_string2 := l_evaluated_string2 ||'); end;';
325 ELSE
326 l_evaluated_string := l_function_source||')';
327 l_evaluated_string2 := l_function_source2||'); end;';
328 END IF;
329 IF l_evaluated_string IS NULL THEN
330 RAISE FUNCTION_DATA_INVALID;
331 END IF;
332 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
333 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_evaluated_string=' || l_evaluated_string);
334 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_evaluated_string2=' || l_evaluated_string2);
335 END IF;
336
337 -- Commented by Santonyr on 22-Oct-2003 to fix bug 3214171
338
339 /* ELSE
340 l_evaluated_string := 'SELECT '||l_evaluated_string ||' FROM dual';
341 END IF;
342
343 -- EXECUTE IMMEDIATE l_evaluated_string INTO l_value;
344 -- DBMS_OUTPUT.PUT_LINE('function eval is '||l_evaluated_string||' values is '||l_value);
345
346 */
347
348
349 -- Changed by Santonyr on 22-Oct-2003 to fix bug 3214171
350
351 v1c := l_contract_id;
352 v2c := l_line_id; --dkagrawa removed the condition to fix bug# 4593579
353 l_evaluated_string := 'BEGIN :l_output_value := ' || l_evaluated_string || '; end;' ;
354 --EXECUTE IMMEDIATE l_evaluated_string USING OUT l_value;
355 if (l_fnctn_prmtrs_val_tbl.count = 0) then
356 EXECUTE IMMEDIATE l_evaluated_string2 USING OUT l_value, IN v1c, IN v2c;
357 end if;
358
359
360 IF l_value IS NULL THEN
361 RAISE FUNCTION_RETURNS_NULL;
362 ELSE
363 x_return_status := OKC_API.G_RET_STS_SUCCESS;
364 x_value := l_value;
365 END IF;
366
367 EXCEPTION
368 WHEN FUNCTION_RETURNS_NULL THEN
369 OKC_API.SET_MESSAGE(p_app_name => g_app_name
370 ,p_msg_name => 'OKL_FUNCTION_RETURNS_NULL'
371 ,p_token1 => 'FUNCTION'
372 ,p_token1_value => l_function_name );
373 x_return_status := OKC_API.G_RET_STS_ERROR;
374
375 WHEN NO_DML_EXCEPTION THEN
376 OKC_API.SET_MESSAGE(p_app_name => g_app_name
377 ,p_msg_name => g_formulae_no_dml
378 ,p_token1 => 'FUNCTION'
379 ,p_token1_value => l_function_name );
380 x_return_status := OKC_API.G_RET_STS_ERROR;
381
382 WHEN FUNCTION_DOES_NOT_EXIST THEN
383 OKC_API.SET_MESSAGE(p_app_name => g_app_name
384 ,p_msg_name => g_function_does_not_exist
385 ,p_token1 => 'FUNCTION'
386 ,p_token1_value => l_function_name );
387 x_return_status := OKC_API.G_RET_STS_ERROR;
388
389 WHEN FUNCTION_DATA_INVALID THEN
390 OKC_API.SET_MESSAGE(p_app_name => g_app_name
391 ,p_msg_name => g_function_data_invalid
392 ,p_token1 => 'FUNCTION'
393 ,p_token1_value => l_function_name );
394 x_return_status := OKC_API.G_RET_STS_ERROR;
395
396 WHEN G_EXCEPTION_HALT_PROCESSING THEN
397 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
398
399 WHEN G_EXCEPTION_ERROR THEN
400 x_return_status := OKC_API.G_RET_STS_ERROR;
401
402 WHEN NO_CONSTANT_SPECIFIED THEN
403 OKC_API.SET_MESSAGE(p_app_name => g_app_name
404 ,p_msg_name => g_no_constant_function
405 ,p_token1 => 'FUNCTION'
406 ,p_token1_value => l_function_name );
407 x_return_status := OKC_API.G_RET_STS_ERROR;
408
409 WHEN NO_DATA_FOUND THEN
410
411 OKC_API.SET_MESSAGE(p_app_name => g_app_name
412 ,p_msg_name => g_invalid_function
413 ,p_token1 => 'FUNCTION'
414 ,p_token1_value => l_function_name );
415 x_return_status := OKC_API.G_RET_STS_ERROR;
416
417 WHEN VALUE_ERROR THEN
418 OKC_API.SET_MESSAGE(p_app_name => g_app_name
419 ,p_msg_name => 'OKL_FUNCTION_VALUE_ERROR'
420 ,p_token1 => 'FUNCTION'
421 ,p_token1_value => l_function_name );
422 x_return_status := OKC_API.G_RET_STS_ERROR;
423
424 WHEN OTHERS THEN
425 OKC_API.SET_MESSAGE(p_app_name => g_app_name
426 ,p_msg_name => g_unexpected_error
427 ,p_token1 => g_sqlcode_token
428 ,p_token1_value => sqlcode
429 ,p_token2 => g_sqlerrm_token
430 ,p_token2_value => sqlerrm);
431 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
432 END execute_function;
433
434 -- Start of comments
435 --
436 -- Procedure Name : execute_formula
437 -- Description : Evaluates the formula and returns a scalar value and a table of operand id, label and
438 -- value. Used internally by package for recursive calling
439 -- Business Rules :
440 -- Parameters : Formula_ID, Formula String, Contract ID, Line ID,Table of Context parameter Values
441 --
442 -- Version : 1.0
443 -- End of comments
444
445 PROCEDURE execute_formula(p_api_version IN NUMBER
446 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
447 ,x_return_status OUT NOCOPY VARCHAR2
448 ,x_msg_count OUT NOCOPY NUMBER
449 ,x_msg_data OUT NOCOPY VARCHAR2
450 ,p_formula_id IN okl_formulae_v.id%TYPE
451 ,p_formula_string IN okl_formulae_v.formula_string%TYPE
452 ,p_contract_id IN okl_k_headers_v.id%TYPE
453 ,p_line_id IN okl_k_lines_v.id%TYPE
454 ,p_ctx_parameter_tbl IN ctxt_parameter_tbl_type
455 ,x_operand_val_tbl OUT NOCOPY operand_val_tbl_type
456 ,x_value OUT NOCOPY NUMBER
457 ) IS
458
459
460 -- Local Variable Declarations
461 l_ctxt_parameter_tbl ctxt_parameter_tbl_type;
462 l_operand_val_tbl operand_val_tbl_type;
463 l_operand_val_tbl_null operand_val_tbl_type;
464 l_formula_id okl_formulae_v.id%TYPE;
465 l_formula_name okl_formulae_v.name%TYPE;
466 l_contract_id okl_k_headers_v.id%TYPE;
467 l_line_id okl_k_lines_v.id%TYPE;
468 l_value NUMBER;
469 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
470 l_return_status VARCHAR2(1);
471 l_msg_count NUMBER;
472 l_msg_data VARCHAR2(2000);
473 l_formula_string okl_formulae_v.formula_string%TYPE;
474 l_temp_string okl_formulae_v.formula_string%TYPE DEFAULT NULL;
475 l_temp_string1 okl_formulae_v.formula_string%TYPE DEFAULT NULL;
476 l_evaluated_string okl_formulae_v.formula_string%TYPE;
477 i NUMBER DEFAULT 1;
478 l_api_version CONSTANT NUMBER := 1.0;
479 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_FORMULA';
480 l_flag BOOLEAN DEFAULT FALSE;
481 l_operand_name okl_operands_v.name%TYPE;
482
483 -- Exception declarations
484 NO_OPERAND_FOUND EXCEPTION;
485 NO_FORMULA_OPERAND_FOUND EXCEPTION;
486 NO_CONSTANT_SPECIFIED EXCEPTION;
487 OPERAND_DATA_INVALID EXCEPTION;
488
489 -- Cursor Declarations
490
491 CURSOR formula_operand_csr(cp_fma_id IN okl_fmla_oprnds_v.fma_id%TYPE) IS
492 SELECT label
493 ,opd_id
494 FROM okl_fmla_oprnds_v
495 WHERE fma_id = cp_fma_id;
496
497 CURSOR operand_csr(cp_operand_id IN okl_operands_v.id%TYPE) IS
498 SELECT fma_id
499 ,dsf_id
500 ,name
501 ,source
502 ,opd_type
503
504 FROM okl_operands_v
505 WHERE id = cp_operand_id;
506
507 CURSOR formula_csr(cp_formula_id IN okl_formulae_v.id%TYPE) IS
508 SELECT name
509 ,formula_string
510 FROM okl_formulae_v
511
512 WHERE id = cp_formula_id;
513
514 BEGIN
515 IF (G_DEBUG_ENABLED = 'Y') THEN
516 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
517 END IF;
518
519 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
520 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In execute_formula...');
521 END IF;
522 l_formula_id := p_formula_id;
523 l_formula_string := p_formula_string;
524 l_contract_id := p_contract_id;
525 l_line_id := p_line_id;
526 l_ctxt_parameter_tbl := p_ctx_parameter_tbl;
527 l_operand_val_tbl := l_operand_val_tbl_null;
528 i := 1;
529
530 -- Added by Santonyr Jul 12th, 2002.To get the formula name which will be used as a token.
531
532 FOR formula_rec IN formula_csr (l_formula_id) LOOP
533 l_formula_name := formula_rec.name;
534 END LOOP;
535
536 FOR l_formula_operand_csr IN formula_operand_csr(cp_fma_id => l_formula_id)
537 LOOP
538 l_flag := TRUE;
539 l_operand_val_tbl(i).id := l_formula_operand_csr.opd_id;
540 l_operand_val_tbl(i).label := l_formula_operand_csr.label;
541 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
542 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_operand_val_tbl('||i||').id=' || l_operand_val_tbl(i).id);
543 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_operand_val_tbl('||i||').label=' || l_operand_val_tbl(i).label);
544 END IF;
545 i := i+1;
546 END LOOP;
547 IF l_flag THEN
548 l_flag := FALSE;
549 ELSE
550 RAISE NO_FORMULA_OPERAND_FOUND;
551 END IF;
552 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
553 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_operand_val_tbl.count=' || l_operand_val_tbl.count);
554 END IF;
555 i := 1;
556 FOR i in l_operand_val_tbl.FIRST .. l_operand_val_tbl.LAST
557 LOOP
558 FOR l_operand_csr IN operand_csr(cp_operand_id => l_operand_val_tbl(i).id)
559 LOOP
560 l_value := NULL;
561 l_operand_name := l_operand_csr.name;
562 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
563 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i=' || i || ' l_operand_name='|| l_operand_name || ' operand_type=' || l_operand_csr.opd_type);
564 END IF;
565 IF l_operand_csr.opd_type = 'CNST' THEN
566 IF l_operand_csr.source IS NULL THEN
567 RAISE NO_CONSTANT_SPECIFIED;
568 ELSE
569
570 l_operand_val_tbl(i).value := l_operand_csr.source;
571 END IF;
572 ELSIF l_operand_csr.opd_type = 'FCNT' THEN
573 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
574 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Calling execute_function...');
575 END IF;
576 execute_function(p_api_version => l_api_version
577 ,p_init_msg_list => l_init_msg_list
578 ,x_return_status => l_return_status
579 ,x_msg_count => l_msg_count
580 ,x_msg_data => l_msg_data
581 ,p_dsf_id => l_operand_csr.dsf_id
582 ,p_ctx_parameter_tbl => l_ctxt_parameter_tbl
583 ,p_contract_id => l_contract_id
584 ,p_line_id => l_line_id
585 ,x_value => l_value
586 );
587 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
588 RAISE G_EXCEPTION_HALT_PROCESSING;
589 ELSE
590 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
591 x_return_status := l_return_status;
592 -- commented by prasjain for bug #5951213
593 -- l_operand_val_tbl(i).value := l_value;
594 -- added by prasjain for bug #5951213
595 l_operand_val_tbl(i).value := fnd_number.number_to_canonical(l_value);
596 ELSE
597 RAISE G_EXCEPTION_ERROR;
598 END IF;
599 END IF;
600 ELSIF l_operand_csr.opd_type = 'FMLA' THEN
601 l_formula_id := l_operand_csr.fma_id;
602 OPEN formula_csr(cp_formula_id => l_formula_id);
603 FETCH formula_csr
604 INTO l_formula_name
605 ,l_formula_string;
606 IF formula_csr%NOTFOUND THEN
607 CLOSE formula_csr;
608 RAISE NO_DATA_FOUND;
609 ELSE
610 CLOSE formula_csr;
611 END IF;
612 execute_formula(p_api_version => l_api_version
613 ,p_init_msg_list => l_init_msg_list
614
615 ,x_return_status => l_return_status
616 ,x_msg_count => l_msg_count
617 ,x_msg_data => l_msg_data
618 ,p_formula_id => l_formula_id
619 ,p_formula_string => l_formula_string
620 ,p_contract_id => l_contract_id
621 ,p_line_id => l_line_id
622 ,p_ctx_parameter_tbl => l_ctxt_parameter_tbl
623 ,x_operand_val_tbl => l_operand_val_tbl_null
624 ,x_value => l_value);
625 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
626 RAISE G_EXCEPTION_HALT_PROCESSING;
627 ELSE
628 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
629 x_return_status := l_return_status;
630 -- commented by prasjain for bug #5951213
631 -- l_operand_val_tbl(i).value := l_value;
632 -- added by prasjain for bug #5951213
633 l_operand_val_tbl(i).value := fnd_number.number_to_canonical(l_value);
634 ELSE
635 RAISE G_EXCEPTION_ERROR;
636 END IF;
637 END IF;
638 END IF;
639 l_flag := TRUE;
640 END LOOP;
641 IF l_flag THEN
642 l_flag := FALSE;
643 ELSE
644 RAISE NO_OPERAND_FOUND;
645 END IF;
646 END LOOP;
647
648 -- Added to take care of complex operand names
649
650 l_formula_string := p_formula_string;
651 FOR i IN 1 .. LENGTH(l_formula_string)
652 LOOP
653 l_temp_string1 := SUBSTR(l_formula_string,i,1);
654 IF l_temp_string1 IN ('(','+','-','*','/',')') THEN
655 IF l_temp_string IS NULL THEN
656 l_evaluated_string := l_evaluated_string||l_temp_string1;
657 ELSE
658 FOR i IN l_operand_val_tbl.FIRST .. l_operand_val_tbl.LAST
659 LOOP
660 IF l_operand_val_tbl(i).label = l_temp_string THEN
661 l_evaluated_string := l_evaluated_string||l_operand_val_tbl(i).value||l_temp_string1;
662 l_flag := TRUE;
663 END IF;
664 END LOOP;
665 IF l_flag THEN
666 l_flag := FALSE;
667 ELSE
668 l_evaluated_string := l_evaluated_string||l_temp_string||l_temp_string1;
669 END IF;
670 END IF;
671 l_temp_string := NULL;
672 ELSE
673 l_temp_string := l_temp_string||l_temp_string1;
674 END IF;
675 END LOOP;
676
677
678 FOR i IN l_operand_val_tbl.FIRST .. l_operand_val_tbl.LAST
679 LOOP
680 IF l_operand_val_tbl(i).label = l_temp_string THEN
681 l_evaluated_string := l_evaluated_string||l_operand_val_tbl(i).value;
682 l_flag := TRUE;
683 END IF;
684 END LOOP;
685 IF l_flag THEN
686 l_flag := FALSE;
687 ELSE
688 l_evaluated_string := l_evaluated_string||l_temp_string;
689 l_temp_string := NULL;
690 END IF;
691
692 -- Function/Formula returns negative and operation is positive
693 -- then follow basic arithmetic rules
694
695 l_evaluated_string := REPLACE(l_evaluated_string
696 ,'+-'
697 ,'-'
698 );
699
700 l_evaluated_string := REPLACE(l_evaluated_string
701 ,'--'
702 ,'+'
703 );
704 IF l_evaluated_string IS NULL THEN
705 RAISE OPERAND_DATA_INVALID;
706 END IF;
707
708 -- Commented by Santonyr on 22-Oct-2003 to fix bug 3214171
709 /* ELSE
710 l_evaluated_string := 'SELECT '||l_evaluated_string ||' FROM dual';
711 END IF;
712
713 EXECUTE IMMEDIATE l_evaluated_string INTO l_value;
714 dbms_output.put_line('Evaluated String is '||l_evaluated_string);
715
716 */
717
718
719 -- Changed by Santonyr on 22-Oct-2003 to fix bug 3214171
720 l_evaluated_string := 'BEGIN :l_output_value := ' || l_evaluated_string || '; END;';
721 EXECUTE IMMEDIATE l_evaluated_string USING OUT l_value;
722
723 x_operand_val_tbl := l_operand_val_tbl;
724 x_return_status := OKC_API.G_RET_STS_SUCCESS;
725 x_value := l_value;
726
727 EXCEPTION
728 WHEN NO_FORMULA_OPERAND_FOUND THEN
729 OKC_API.SET_MESSAGE(p_app_name => g_app_name
730 ,p_msg_name => g_invalid_formula_operand
731 ,p_token1 => 'FORMULA'
732 ,p_token1_value => l_formula_name );
733
734
735 x_return_status := OKC_API.G_RET_STS_ERROR;
736
737 WHEN NO_OPERAND_FOUND THEN
738 OKC_API.SET_MESSAGE(p_app_name => g_app_name
739 ,p_msg_name => g_invalid_operand
740 ,p_token1 => 'FORMULA'
741 ,p_token1_value => l_formula_name );
742 x_return_status := OKC_API.G_RET_STS_ERROR;
743
744 WHEN NO_CONSTANT_SPECIFIED THEN
745 OKC_API.SET_MESSAGE(p_app_name => g_app_name
746 ,p_msg_name => g_no_constant_operand
747 ,p_token1 => 'OPERAND'
748 ,p_token1_value => l_operand_name );
749 x_return_status := OKC_API.G_RET_STS_ERROR;
750
751 WHEN OPERAND_DATA_INVALID THEN
752 OKC_API.SET_MESSAGE(p_app_name => g_app_name
753 ,p_msg_name => g_operand_data_invalid
754 ,p_token1 => 'OPERAND'
755 ,p_token1_value => l_operand_name );
756 x_return_status := OKC_API.G_RET_STS_ERROR;
757
758 WHEN G_EXCEPTION_HALT_PROCESSING THEN
759 IF formula_csr%ISOPEN THEN
760 CLOSE formula_csr;
761 END IF;
762 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
763
764 WHEN G_EXCEPTION_ERROR THEN
765 x_return_status := OKC_API.G_RET_STS_ERROR;
766
767 WHEN NO_DATA_FOUND THEN
768 IF formula_csr%ISOPEN THEN
769 CLOSE formula_csr;
770 END IF;
771 OKC_API.SET_MESSAGE(p_app_name => g_app_name
772 ,p_msg_name => g_invalid_fmla_in_operand
773 ,p_token1 => 'OPERAND'
774 ,p_token1_value => l_operand_name );
775 x_return_status := OKC_API.G_RET_STS_ERROR;
776
777
778 WHEN VALUE_ERROR THEN
779 IF formula_csr%ISOPEN THEN
780 CLOSE formula_csr;
781 END IF;
782 OKC_API.SET_MESSAGE(p_app_name => g_app_name
783 ,p_msg_name => g_value_error
784 ,p_token1 => 'FORMULA'
785 ,p_token1_value => l_formula_name );
786 x_return_status := OKC_API.G_RET_STS_ERROR;
787
788 WHEN OTHERS THEN
789 IF formula_csr%ISOPEN THEN
790 CLOSE formula_csr;
791 END IF;
792 OKC_API.SET_MESSAGE(p_app_name => g_app_name
793 ,p_msg_name => g_unexpected_error
794 ,p_token1 => g_sqlcode_token
795 ,p_token1_value => sqlcode
796 ,p_token2 => g_sqlerrm_token
797 ,p_token2_value => sqlerrm);
798 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
799 END execute_formula;
800
801 -- Start of comments
802 --
803 -- Procedure Name : execute
804 -- Description : Evaluates the formula and returns a scalar value. This procedure is overloaded
805 -- to return the operand id, label and value. This procedure is exposed to public.
806 -- Business Rules :
807 -- Parameters : Formula_ID, Formula String, Contract ID, Line ID,Table of Context parameter Values
808 --
809 -- Version : 1.0
810 -- End of comments
811
812 PROCEDURE execute(p_api_version IN NUMBER
813 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
814
815 ,x_return_status OUT NOCOPY VARCHAR2
816 ,x_msg_count OUT NOCOPY NUMBER
817 ,x_msg_data OUT NOCOPY VARCHAR2
818 ,p_formula_name IN okl_formulae_v.name%TYPE
819 ,p_contract_id IN okl_k_headers_v.id%TYPE
820 ,p_line_id IN okl_k_lines_v.id%TYPE DEFAULT NULL
821 ,p_additional_parameters IN ctxt_val_tbl_type DEFAULT g_additional_parameters_null
822 ,x_value OUT NOCOPY NUMBER
823 ) IS
824
825 -- Exception declarations
826 ERROR_IN_EVALUATE_PARAM EXCEPTION;
827
828 -- Local Variable Declarations
829 l_ctxt_value_tbl ctxt_val_tbl_type;
830 l_ctxt_parameter_tbl ctxt_parameter_tbl_type;
831 l_operand_val_tbl operand_val_tbl_type;
832 l_formula_id okl_formulae_v.id%TYPE;
833 l_formula_string okl_formulae_v.formula_string%TYPE;
834 l_formula_name okl_formulae_v.name%TYPE;
835
836 l_contract_id okl_k_headers_v.id%TYPE;
837 l_line_id okl_k_lines_v.id%TYPE;
838 l_value NUMBER;
839 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
840 l_return_status VARCHAR2(1);
841 l_msg_count NUMBER;
842 l_msg_data VARCHAR2(2000);
843 i PLS_INTEGER DEFAULT 1;
844 l_api_version CONSTANT NUMBER := 1.0;
845 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE';
846
847 -- Cursor Declarations
848 CURSOR formula_csr(cp_formula_name IN okl_formulae_v.name%TYPE) IS
849 SELECT id
850 ,formula_string
851 FROM okl_formulae_v
852 WHERE name = cp_formula_name
853 AND start_date <= sysdate
854 AND (end_date IS NULL OR end_date >= sysdate);
855 BEGIN
856 IF (G_DEBUG_ENABLED = 'Y') THEN
857 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
858 END IF;
859 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
860 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In okl_execute_formula_pvt2.execute...');
861 END IF;
862 l_formula_name := p_formula_name;
863 l_contract_id := p_contract_id;
864 l_line_id := p_line_id;
865
866 OPEN formula_csr(cp_formula_name => l_formula_name);
867 FETCH formula_csr
868 INTO l_formula_id
869 ,l_formula_string;
870 IF formula_csr%NOTFOUND THEN
871 CLOSE formula_csr;
872 RAISE NO_DATA_FOUND;
873 ELSE
874 CLOSE formula_csr;
875 END IF;
876
877 -- Commented by Santonyr on 22-Oct-2003 to fix bug 3214171
878 -- okl_execute_formula_pub.g_additional_parameters := p_additional_parameters;
879
880 okl_formulaevaluate_pub.eva_getparametervalues(p_api_version => l_api_version
881
882 ,p_init_msg_list => l_init_msg_list
883 ,x_return_status => l_return_status
884 ,x_msg_count => l_msg_count
885 ,x_msg_data => l_msg_data
886 ,p_fma_id => l_formula_id
887 ,p_contract_id => l_contract_id
888 ,p_line_id => l_line_id
889 ,x_ctx_parameter_tbl => l_ctxt_parameter_tbl
890 );
891
892 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
893 RAISE ERROR_IN_EVALUATE_PARAM;
894 ELSE
895 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
896 x_return_status := l_return_status;
897 ELSE
898 RAISE ERROR_IN_EVALUATE_PARAM;
899 END IF;
900 END IF;
901 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
902 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After calling okl_formulaevaluate_pub.eva_getparametervalues l_ctxt_parameter_tbl.count='||l_ctxt_parameter_tbl.count);
903 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Calling execute_formula...');
904 END IF;
905 execute_formula(p_api_version => l_api_version
906 ,p_init_msg_list => l_init_msg_list
907 ,x_return_status => l_return_status
908 ,x_msg_count => l_msg_count
909 ,x_msg_data => l_msg_data
910 ,p_formula_id => l_formula_id
911 ,p_formula_string => l_formula_string
912 ,p_contract_id => l_contract_id
913 ,p_line_id => l_line_id
914 ,p_ctx_parameter_tbl => l_ctxt_parameter_tbl
915 ,x_operand_val_tbl => l_operand_val_tbl
916 ,x_value => l_value
917 );
918
919 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
920 RAISE G_EXCEPTION_HALT_PROCESSING;
921 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
922 x_return_status := l_return_status;
923 x_value := l_value;
924 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
925 RAISE G_EXCEPTION_ERROR;
926
927 ELSE
928 RAISE G_EXCEPTION_HALT_PROCESSING;
929 END IF;
930 EXCEPTION
931 WHEN ERROR_IN_EVALUATE_PARAM THEN
932 IF formula_csr%ISOPEN THEN
933 CLOSE formula_csr;
934 END IF;
935 OKC_API.SET_MESSAGE(p_app_name => g_app_name
936 ,p_msg_name => g_error_in_evaluate_param
937 ,p_token1 => 'FORMULA'
938 ,p_token1_value => l_formula_name );
939 x_return_status := l_return_status;
940
941 WHEN G_EXCEPTION_HALT_PROCESSING THEN
942 IF formula_csr%ISOPEN THEN
943 CLOSE formula_csr;
944 END IF;
945 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
946
947
948 WHEN G_EXCEPTION_ERROR THEN
949 x_return_status := OKC_API.G_RET_STS_ERROR;
950
951 WHEN NO_DATA_FOUND THEN
952 IF formula_csr%ISOPEN THEN
953 CLOSE formula_csr;
954 END IF;
955 OKC_API.SET_MESSAGE(p_app_name => g_app_name
956 ,p_msg_name => g_invalid_formula
957 ,p_token1 => 'FORMULA'
958 ,p_token1_value => l_formula_name );
959 x_return_status := OKC_API.G_RET_STS_ERROR;
960
961
962 WHEN OTHERS THEN
963 IF formula_csr%ISOPEN THEN
964 CLOSE formula_csr;
965 END IF;
966 OKC_API.SET_MESSAGE(p_app_name => g_app_name
967 ,p_msg_name => g_unexpected_error
968 ,p_token1 => g_sqlcode_token
969 ,p_token1_value => sqlcode
970 ,p_token2 => g_sqlerrm_token
971 ,p_token2_value => sqlerrm);
972 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
973 END execute;
974
975 -- Start of comments
976 --
977 -- Procedure Name : execute
978 -- Description : Evaluates the formula and returns a scalar value. This procedure is overloaded
979 -- to return the operand id, label and value. This procedure is exposed to public.
980 -- This procedure is overloaded to allow more granular results at the operand level
981 -- It is used for validate formula screen where we can get values for
982 -- each of the operand.
983
984 -- Business Rules :
985 -- Parameters : Formula_ID, Formula String, Contract ID, Line ID,Table of Context parameter Values
986 --
987 -- Version : 1.0
988 -- End of comments
989
990 PROCEDURE execute(p_api_version IN NUMBER
991 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
992 ,x_return_status OUT NOCOPY VARCHAR2
993 ,x_msg_count OUT NOCOPY NUMBER
994 ,x_msg_data OUT NOCOPY VARCHAR2
995 ,p_formula_name IN okl_formulae_v.name%TYPE
996 ,p_contract_id IN okl_k_headers_v.id%TYPE
997 ,p_line_id IN okl_k_lines_v.id%TYPE DEFAULT NULL
998 ,p_additional_parameters IN ctxt_val_tbl_type DEFAULT g_additional_parameters_null
999 ,x_operand_val_tbl OUT NOCOPY operand_val_tbl_type
1000 ,x_value OUT NOCOPY NUMBER
1001 ) IS
1002
1003 -- Exception declarations
1004 ERROR_IN_EVALUATE_PARAM EXCEPTION;
1005
1006 -- Local Variable Declarations
1007 l_ctxt_value_tbl ctxt_val_tbl_type;
1008 l_ctxt_parameter_tbl ctxt_parameter_tbl_type;
1009 l_operand_val_tbl operand_val_tbl_type;
1010 l_formula_id okl_formulae_v.id%TYPE;
1011 l_formula_string okl_formulae_v.formula_string%TYPE;
1012 l_formula_name okl_formulae_v.name%TYPE;
1013 l_contract_id okl_k_headers_v.id%TYPE;
1014 l_line_id okl_k_lines_v.id%TYPE;
1015 l_value NUMBER;
1016 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
1017 l_return_status VARCHAR2(1);
1018 l_msg_count NUMBER;
1019 l_msg_data VARCHAR2(2000);
1020 i PLS_INTEGER DEFAULT 1;
1021 l_api_version CONSTANT NUMBER := 1.0;
1022 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE';
1023
1024 -- Cursor Declarations
1025 CURSOR formula_csr(cp_formula_name IN okl_formulae_v.name%TYPE) IS
1026 SELECT id
1027 ,formula_string
1028
1029 FROM okl_formulae_v
1030 WHERE name = cp_formula_name
1031 AND start_date <= sysdate
1032 AND (end_date IS NULL OR end_date >= sysdate);
1033 BEGIN
1034 IF (G_DEBUG_ENABLED = 'Y') THEN
1035 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1036 END IF;
1037 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1038 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In okl_execute_formula_pvt2.execute2...');
1039 END IF;
1040 l_formula_name := p_formula_name;
1041 l_contract_id := p_contract_id;
1042 l_line_id := p_line_id;
1043
1044 OPEN formula_csr(cp_formula_name => l_formula_name);
1045 FETCH formula_csr
1046 INTO l_formula_id
1047 ,l_formula_string;
1048 IF formula_csr%NOTFOUND THEN
1049 CLOSE formula_csr;
1050 RAISE NO_DATA_FOUND;
1051 ELSE
1052 CLOSE formula_csr;
1053
1054 END IF;
1055
1056 -- Commented by Santonyr on 22-Oct-2003 to fix bug 3214171
1057 -- okl_execute_formula_pub.g_additional_parameters := p_additional_parameters;
1058
1059 okl_formulaevaluate_pub.eva_getparametervalues(p_api_version => l_api_version
1060 ,p_init_msg_list => l_init_msg_list
1061 ,x_return_status => l_return_status
1062 ,x_msg_count => l_msg_count
1063 ,x_msg_data => l_msg_data
1064 ,p_fma_id => l_formula_id
1065 ,p_contract_id => l_contract_id
1066 ,p_line_id => l_line_id
1067 ,x_ctx_parameter_tbl => l_ctxt_parameter_tbl
1068 );
1069 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1070 RAISE ERROR_IN_EVALUATE_PARAM;
1071 ELSE
1072 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1073 x_return_status := l_return_status;
1074 ELSE
1075 RAISE ERROR_IN_EVALUATE_PARAM;
1076 END IF;
1077 END IF;
1078 execute_formula(p_api_version => l_api_version
1079 ,p_init_msg_list => l_init_msg_list
1080 ,x_return_status => l_return_status
1081 ,x_msg_count => l_msg_count
1082 ,x_msg_data => l_msg_data
1083 ,p_formula_id => l_formula_id
1084 ,p_formula_string => l_formula_string
1085 ,p_contract_id => l_contract_id
1086 ,p_line_id => l_line_id
1087 ,p_ctx_parameter_tbl => l_ctxt_parameter_tbl
1088
1089 ,x_operand_val_tbl => l_operand_val_tbl
1090 ,x_value => l_value
1091 );
1092 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1093 RAISE G_EXCEPTION_HALT_PROCESSING;
1094 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1095 x_operand_val_tbl := l_operand_val_tbl;
1096 x_return_status := l_return_status;
1097 x_value := l_value;
1098 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1099 RAISE G_EXCEPTION_ERROR;
1100 ELSE
1101 RAISE G_EXCEPTION_HALT_PROCESSING;
1102 END IF;
1103 EXCEPTION
1104 WHEN ERROR_IN_EVALUATE_PARAM THEN
1105 IF formula_csr%ISOPEN THEN
1106 CLOSE formula_csr;
1107 END IF;
1108 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1109 ,p_msg_name => g_error_in_evaluate_param
1110 ,p_token1 => 'FORMULA'
1111 ,p_token1_value => l_formula_name );
1112 x_return_status := l_return_status;
1113
1114 WHEN G_EXCEPTION_HALT_PROCESSING THEN
1115 IF formula_csr%ISOPEN THEN
1116 CLOSE formula_csr;
1117 END IF;
1118 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1119
1120 WHEN G_EXCEPTION_ERROR THEN
1121 x_return_status := OKC_API.G_RET_STS_ERROR;
1122
1123 WHEN NO_DATA_FOUND THEN
1124 IF formula_csr%ISOPEN THEN
1125 CLOSE formula_csr;
1126 END IF;
1127 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1128 ,p_msg_name => g_invalid_formula
1129 ,p_token1 => 'FORMULA'
1130 ,p_token1_value => l_formula_name );
1131 x_return_status := OKC_API.G_RET_STS_ERROR;
1132
1133
1134 WHEN OTHERS THEN
1135 IF formula_csr%ISOPEN THEN
1136
1137 CLOSE formula_csr;
1138 END IF;
1139 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1140 ,p_msg_name => g_unexpected_error
1141 ,p_token1 => g_sqlcode_token
1142 ,p_token1_value => sqlcode
1143 ,p_token2 => g_sqlerrm_token
1144 ,p_token2_value => sqlerrm);
1145 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1146 END execute;
1147
1148 END OKL_EXECUTE_FORMULA_PVT;