[Home] [Help]
PACKAGE BODY: APPS.OKL_EC_EVALUATE_PVT
Source
1 PACKAGE BODY okl_ec_evaluate_pvt AS
2 /* $Header: OKLRECUB.pls 120.3 2005/08/23 05:38:27 asawanka noship $ */
3 /**
4 This function returs the lookup meaning for the given lookup type and code.
5 **/
6 FUNCTION get_lookup_meaning(p_lookup_type fnd_lookups.lookup_type%TYPE
7 ,p_lookup_code fnd_lookups.lookup_code%TYPE) RETURN varchar2 IS
8
9 CURSOR fnd_lookup_csr(p_lookup_type fnd_lookups.lookup_type%TYPE
10 ,p_lookup_code fnd_lookups.lookup_code%TYPE) IS
11 SELECT meaning
12 FROM fnd_lookups fnd
13 WHERE fnd.lookup_type = p_lookup_type
14 AND fnd.lookup_code = p_lookup_code;
15 l_return_value varchar2(200) := okl_api.g_miss_char;
16 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_lookup_meaning';
17 l_debug_enabled varchar2(10);
18 is_debug_procedure_on boolean;
19 is_debug_statement_on boolean;
20
21 BEGIN
22 l_debug_enabled := okl_debug_pub.check_log_enabled;
23 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
24 ,fnd_log.level_procedure);
25
26 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
27 okl_debug_pub.log_debug(fnd_log.level_procedure
28 ,l_module
29 ,'begin debug OKLRECUB.pls.pls call get_lookup_meaning');
30 END IF;
31
32 IF (p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL) THEN
33 OPEN fnd_lookup_csr(p_lookup_type, p_lookup_code);
34 FETCH fnd_lookup_csr INTO l_return_value ;
35 CLOSE fnd_lookup_csr;
36 END IF;
37
38 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
39 okl_debug_pub.log_debug(fnd_log.level_procedure
40 ,l_module
41 ,'end debug OKLRECUB.pls.pls call evaluate_territory');
42 END IF;
43 RETURN l_return_value;
44 END get_lookup_meaning;
45 /**
46 This function sets the message name and tokens in fnd_message and returns the
47 retrieved message text.
48 **/
49 PROCEDURE set_fnd_message(p_msg_name IN varchar2
50 ,p_token1 IN varchar2 DEFAULT NULL
51 ,p_value1 IN varchar2 DEFAULT NULL
52 ,p_token2 IN varchar2 DEFAULT NULL
53 ,p_value2 IN varchar2 DEFAULT NULL
54 ,p_token3 IN varchar2 DEFAULT NULL
55 ,p_value3 IN varchar2 DEFAULT NULL
56 ,p_token4 IN varchar2 DEFAULT NULL
57 ,p_value4 IN varchar2 DEFAULT NULL) IS
58 l_msg varchar2(2700);
59 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.set_fnd_message';
60 l_debug_enabled varchar2(10);
61 is_debug_procedure_on boolean;
62 is_debug_statement_on boolean;
63
64 BEGIN
65 l_debug_enabled := okl_debug_pub.check_log_enabled;
66 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
67 ,fnd_log.level_procedure);
68
69 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
70 okl_debug_pub.log_debug(fnd_log.level_procedure
71 ,l_module
72 ,'begin debug OKLRECUB.pls.pls call set_fnd_message');
73 END IF;
74 fnd_message.set_name(g_app_name, p_msg_name);
75
76 IF (p_token1 IS NOT NULL) THEN
77 fnd_message.set_token(token => p_token1, value => p_value1);
78 END IF;
79
80 IF (p_token2 IS NOT NULL) THEN
81 fnd_message.set_token(token => p_token2, value => p_value2);
82 END IF;
83
84 IF (p_token3 IS NOT NULL) THEN
85 fnd_message.set_token(token => p_token3, value => p_value3);
86 END IF;
87
88 IF (p_token4 IS NOT NULL) THEN
89 fnd_message.set_token(token => p_token4, value => p_value4);
90 END IF;
91
92 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
93 okl_debug_pub.log_debug(fnd_log.level_procedure
94 ,l_module
95 ,'end debug OKLRECUB.pls.pls call set_fnd_message');
96 END IF;
97
98 END set_fnd_message;
99 /**
100 This function returns the message text for validation failure of eligibility criteria
101 having value type SINGLE.
102 **/
103 FUNCTION get_msg_single(p_ec_name IN varchar2
104 ,p_operator IN varchar2
105 ,p_val IN varchar2
106 ,p_src_name IN varchar2) RETURN varchar2 IS
107
108 CURSOR get_op IS
109 SELECT meaning
110 FROM fnd_lookups
111 WHERE lookup_type = 'OKL_FE_OPERATORS' AND lookup_code = p_operator;
112 l_operator varchar2(30);
113 l_msg varchar2(240);
114 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_msg_single';
115 l_debug_enabled varchar2(10);
116 is_debug_procedure_on boolean;
117 is_debug_statement_on boolean;
118
119 BEGIN
120 l_debug_enabled := okl_debug_pub.check_log_enabled;
121 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
122 ,fnd_log.level_procedure);
123
124 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
125 okl_debug_pub.log_debug(fnd_log.level_procedure
126 ,l_module
127 ,'begin debug OKLRECUB.pls.pls call get_msg_single');
128 END IF;
129 OPEN get_op;
130 FETCH get_op INTO l_operator ;
131 CLOSE get_op;
132 set_fnd_message(p_msg_name => 'OKL_EC_QA_SINGLE'
133 ,p_token1 => 'EC'
134 ,p_value1 => p_ec_name
135 ,p_token2 => 'OPERATOR'
136 ,p_value2 => l_operator
137 ,p_token3 => 'VALUE'
138 ,p_value3 => p_val
139 ,p_token4 => 'SOURCE'
140 ,p_value4 => p_src_name);
141
142 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
143 okl_debug_pub.log_debug(fnd_log.level_procedure
144 ,l_module
145 ,'end debug OKLRECUB.pls.pls call get_msg_single');
146 END IF;
147 RETURN fnd_message.get;
148 END get_msg_single;
149 /**
150 This function returns the message text for validation failure of eligibility criteria
151 having value type RANGE.
152 **/
153 FUNCTION get_msg_range(p_ec_name IN varchar2
154 ,p_val1 IN varchar2
155 ,p_val2 IN varchar2
156 ,p_src_name IN varchar2) RETURN varchar2 IS
157 l_msg varchar2(240);
158 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_msg_range';
159 l_debug_enabled varchar2(10);
160 is_debug_procedure_on boolean;
161 is_debug_statement_on boolean;
162
163 BEGIN
164 l_debug_enabled := okl_debug_pub.check_log_enabled;
165 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
166 ,fnd_log.level_procedure);
167
168 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
169 okl_debug_pub.log_debug(fnd_log.level_procedure
170 ,l_module
171 ,'begin debug OKLRECUB.pls.pls call get_msg_range');
172 END IF;
173 set_fnd_message(p_msg_name => 'OKL_EC_QA_RANGE'
174 ,p_token1 => 'EC'
175 ,p_value1 => p_ec_name
176 ,p_token2 => 'VALUE1'
177 ,p_value2 => p_val1
178 ,p_token3 => 'VALUE2'
179 ,p_value3 => p_val2
180 ,p_token4 => 'SOURCE'
181 ,p_value4 => p_src_name);
182
183 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
184 okl_debug_pub.log_debug(fnd_log.level_procedure
185 ,l_module
186 ,'end debug OKLRECUB.pls.pls call get_msg_range');
187 END IF;
188 RETURN fnd_message.get;
189 END get_msg_range;
190 /**
191 This function returns the message text for validation failure of eligibility criteria
192 having value type MULTIPLE.
193 **/
194 FUNCTION get_msg_multiple(p_ec_name IN varchar2
195 ,p_src_name IN varchar2) RETURN varchar2 IS
196 l_msg varchar2(240);
197 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_msg_multiple';
198 l_debug_enabled varchar2(10);
199 is_debug_procedure_on boolean;
200 is_debug_statement_on boolean;
201
202 BEGIN
203 l_debug_enabled := okl_debug_pub.check_log_enabled;
204 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
205 ,fnd_log.level_procedure);
206
207 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
208 okl_debug_pub.log_debug(fnd_log.level_procedure
209 ,l_module
210 ,'begin debug OKLRECUB.pls.pls call get_msg_multiple');
211 END IF;
212 set_fnd_message(p_msg_name => 'OKL_EC_QA_MULTIPLE'
213 ,p_token1 => 'EC'
214 ,p_value1 => p_ec_name
215 ,p_token2 => 'SOURCE'
216 ,p_value2 => p_src_name);
217
218 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
219 okl_debug_pub.log_debug(fnd_log.level_procedure
220 ,l_module
221 ,'end debug OKLRECUB.pls.pls call get_msg_multiple');
222 END IF;
223 RETURN fnd_message.get;
224 END get_msg_multiple;
225 /**
226 This function returns the message text for validation failure of user defined
227 eligibility criteria
228 **/
229 FUNCTION get_msg_user(p_ec_name IN varchar2
230 ,p_src_name IN varchar2) RETURN varchar2 IS
231 l_msg varchar2(240);
232 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_msg_user';
233 l_debug_enabled varchar2(10);
234 is_debug_procedure_on boolean;
235 is_debug_statement_on boolean;
236
237 BEGIN
238 l_debug_enabled := okl_debug_pub.check_log_enabled;
239 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
240 ,fnd_log.level_procedure);
241
242 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
243 okl_debug_pub.log_debug(fnd_log.level_procedure
244 ,l_module
245 ,'begin debug OKLRECUB.pls.pls call get_msg_user');
246 END IF;
247 set_fnd_message(p_msg_name => 'OKL_EC_QA_USER'
248 ,p_token1 => 'EC'
249 ,p_value1 => p_ec_name
250 ,p_token2 => 'SOURCE'
251 ,p_value2 => p_src_name);
252
253 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
254 okl_debug_pub.log_debug(fnd_log.level_procedure
255 ,l_module
256 ,'end debug OKLRECUB.pls.pls call get_msg_user');
257 END IF;
258 RETURN fnd_message.get;
259 END get_msg_user;
260 /**
261 This function returns the message text for validation succcess of eligibility criteria
262 **/
263 FUNCTION get_msg_success(p_ec_name IN varchar2
264 ,p_src_name IN varchar2) RETURN varchar2 IS
265 l_msg varchar2(240);
266 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_msg_success';
267 l_debug_enabled varchar2(10);
268 is_debug_procedure_on boolean;
269 is_debug_statement_on boolean;
270
271 BEGIN
272 l_debug_enabled := okl_debug_pub.check_log_enabled;
273 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
274 ,fnd_log.level_procedure);
275
276 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
277 okl_debug_pub.log_debug(fnd_log.level_procedure
278 ,l_module
279 ,'begin debug OKLRECUB.pls.pls call get_msg_success');
280 END IF;
281 set_fnd_message(p_msg_name => 'OKL_EC_QA_SUCCESS'
282 ,p_token1 => 'EC'
283 ,p_value1 => p_ec_name
284 ,p_token2 => 'SOURCE'
285 ,p_value2 => p_src_name);
286
287 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
288 okl_debug_pub.log_debug(fnd_log.level_procedure
289 ,l_module
290 ,'end debug OKLRECUB.pls.pls call get_msg_success');
291 END IF;
292 RETURN fnd_message.get;
293 END get_msg_success;
294 /**
295 This is the functions which validates the seeded eligibility criteria ADVANCE_RENT.
296 **/
297
298 FUNCTION validate_advance_rent(p_operator_code IN varchar2
299 ,p_value1 IN varchar2
300 ,p_advance_rent IN number) RETURN number IS
301 l_numval1 number;
302 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_advance_rent';
303 l_debug_enabled varchar2(10);
304 is_debug_procedure_on boolean;
305 is_debug_statement_on boolean;
306
307 BEGIN
308 l_debug_enabled := okl_debug_pub.check_log_enabled;
309 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
310 ,fnd_log.level_procedure);
311
312 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
313 okl_debug_pub.log_debug(fnd_log.level_procedure
314 ,l_module
315 ,'begin debug OKLRECUB.pls.pls call validate_advance_rent');
316 END IF;
317 l_numval1 := to_number(p_value1);
318
319 IF p_operator_code = 'EQ' THEN
320 IF p_advance_rent = l_numval1 THEN
321 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
322 okl_debug_pub.log_debug(fnd_log.level_procedure
323 ,l_module
324 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
325 END IF;
326 RETURN 1;
327 ELSE
328 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
329 okl_debug_pub.log_debug(fnd_log.level_procedure
330 ,l_module
331 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
332 END IF;
333 RETURN 0;
334 END IF;
335 ELSIF p_operator_code = 'NE' THEN
336 IF p_advance_rent <> l_numval1 THEN
337 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
338 okl_debug_pub.log_debug(fnd_log.level_procedure
339 ,l_module
340 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
341 END IF;
342 RETURN 1;
343 ELSE
344 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
345 okl_debug_pub.log_debug(fnd_log.level_procedure
346 ,l_module
347 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
348 END IF;
349 RETURN 0;
350 END IF;
351 ELSIF p_operator_code = 'LT' THEN
352 IF p_advance_rent < l_numval1 THEN
353 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
354 okl_debug_pub.log_debug(fnd_log.level_procedure
355 ,l_module
356 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
357 END IF;
358 RETURN 1;
359 ELSE
360 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
361 okl_debug_pub.log_debug(fnd_log.level_procedure
362 ,l_module
363 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
364 END IF;
365 RETURN 0;
366 END IF;
367 ELSIF p_operator_code = 'GT' THEN
368 IF p_advance_rent > l_numval1 THEN
369 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
370 okl_debug_pub.log_debug(fnd_log.level_procedure
371 ,l_module
372 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
373 END IF;
374 RETURN 1;
375 ELSE
376 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
377 okl_debug_pub.log_debug(fnd_log.level_procedure
378 ,l_module
379 ,'end debug OKLRECUB.pls.pls call validate_advance_rent');
380 END IF;
381 RETURN 0;
382 END IF;
383 END IF;
384
385 END validate_advance_rent;
386 /**
387 This is the functions which validates the seeded eligibility criteria DOWN_PAYMENT.
388 **/
389
390 FUNCTION validate_down_payment(p_operator_code IN varchar2
391 ,p_value1 IN varchar2
392 ,p_down_payment IN number) RETURN number IS
393 l_numval1 number;
394 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_down_payment';
395 l_debug_enabled varchar2(10);
396 is_debug_procedure_on boolean;
397 is_debug_statement_on boolean;
398
399 BEGIN
400 l_debug_enabled := okl_debug_pub.check_log_enabled;
401 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
402 ,fnd_log.level_procedure);
403
404 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
405 okl_debug_pub.log_debug(fnd_log.level_procedure
406 ,l_module
407 ,'begin debug OKLRECUB.pls.pls call validate_down_payment');
408 END IF;
409 l_numval1 := to_number(p_value1);
410
411 IF p_operator_code = 'EQ' THEN
412 IF p_down_payment = l_numval1 THEN
413 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
414 okl_debug_pub.log_debug(fnd_log.level_procedure
415 ,l_module
416 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
417 END IF;
418 RETURN 1;
419 ELSE
420 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
421 okl_debug_pub.log_debug(fnd_log.level_procedure
422 ,l_module
423 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
424 END IF;
425 RETURN 0;
426 END IF;
427 ELSIF p_operator_code = 'NE' THEN
428 IF p_down_payment <> l_numval1 THEN
429 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
430 okl_debug_pub.log_debug(fnd_log.level_procedure
431 ,l_module
432 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
433 END IF;
434 RETURN 1;
435 ELSE
436 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
437 okl_debug_pub.log_debug(fnd_log.level_procedure
438 ,l_module
439 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
440 END IF;
441 RETURN 0;
442 END IF;
443 ELSIF p_operator_code = 'LT' THEN
444 IF p_down_payment < l_numval1 THEN
445 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
446 okl_debug_pub.log_debug(fnd_log.level_procedure
447 ,l_module
448 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
449 END IF;
450 RETURN 1;
451 ELSE
452 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
453 okl_debug_pub.log_debug(fnd_log.level_procedure
454 ,l_module
455 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
456 END IF;
457 RETURN 0;
458 END IF;
459 ELSIF p_operator_code = 'GT' THEN
460 IF p_down_payment > l_numval1 THEN
461 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
462 okl_debug_pub.log_debug(fnd_log.level_procedure
463 ,l_module
464 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
465 END IF;
466 RETURN 1;
467 ELSE
468 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
469 okl_debug_pub.log_debug(fnd_log.level_procedure
470 ,l_module
471 ,'end debug OKLRECUB.pls.pls call validate_down_payment');
472 END IF;
473 RETURN 0;
474 END IF;
475 END IF;
476
477 END validate_down_payment;
478 /**
479 This is the functions which validates the seeded eligibility criteria TRADE_IN_VALUE.
480 **/
481
482 FUNCTION validate_trade_in_value(p_operator_code IN varchar2
483 ,p_value1 IN varchar2
484 ,p_value2 IN varchar2
485 ,p_trade_in_value IN number) RETURN number IS
486 l_numval1 number;
487 l_numval2 number;
488 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_trade_in_value';
489 l_debug_enabled varchar2(10);
490 is_debug_procedure_on boolean;
491 is_debug_statement_on boolean;
492
493 BEGIN
494 l_debug_enabled := okl_debug_pub.check_log_enabled;
495 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
496 ,fnd_log.level_procedure);
497
498 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
499 okl_debug_pub.log_debug(fnd_log.level_procedure
500 ,l_module
501 ,'begin debug OKLRECUB.pls.pls call validate_trade_in_value');
502 END IF;
503 l_numval1 := fnd_number.canonical_to_number(p_value1);
504 l_numval2 := fnd_number.canonical_to_number(p_value2);
505
506 IF (p_trade_in_value >= l_numval1 AND p_trade_in_value <= l_numval2) THEN
507 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
508 okl_debug_pub.log_debug(fnd_log.level_procedure
509 ,l_module
510 ,'end debug OKLRECUB.pls.pls call validate_trade_in_value');
511 END IF;
512 RETURN 1;
513 ELSE
514 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
515 okl_debug_pub.log_debug(fnd_log.level_procedure
516 ,l_module
517 ,'end debug OKLRECUB.pls.pls call validate_trade_in_value');
518 END IF;
519 RETURN 0;
520 END IF;
521
522 END validate_trade_in_value;
523
524 FUNCTION validate_term(p_value_tbl IN okl_number_table_type
525 ,p_match_criteria IN varchar2
526 ,p_term IN number) RETURN number IS
527 l_result varchar2(1);
528 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_term';
529 l_debug_enabled varchar2(10);
530 is_debug_procedure_on boolean;
531 is_debug_statement_on boolean;
532
533 BEGIN
534 l_debug_enabled := okl_debug_pub.check_log_enabled;
535 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
536 ,fnd_log.level_procedure);
537
538 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
539 okl_debug_pub.log_debug(fnd_log.level_procedure
540 ,l_module
541 ,'begin debug OKLRECUB.pls.pls call validate_term');
542 END IF;
543 l_result := 'S';
544
545 IF p_match_criteria = 'INCLUDE' THEN
546
547 <<outer1>>
548 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
549
550 -- dbms_output.put_line('p_term_tbl(i)= '||p_term_tbl(i)|| 'p_value_tbl(j) = '||p_value_tbl(j));
551
552 IF p_term = p_value_tbl(j) THEN
553 l_result := 'S';
554 EXIT outer1;
555 ELSE
556 l_result := 'E';
557 END IF;
558 END LOOP;
559
560
561 END IF;
562
563 IF p_match_criteria = 'EXCLUDE' THEN
564
565 <<outer2>>
566 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
567 IF p_term = p_value_tbl(j) THEN
568 l_result := 'E';
569 EXIT outer2;
570 ELSE
571 l_result := 'S';
572 END IF;
573 END LOOP;
574
575 END IF;
576
577 IF l_result = 'S' THEN
578 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
579 okl_debug_pub.log_debug(fnd_log.level_procedure
580 ,l_module
581 ,'end debug OKLRECUB.pls.pls call validate_term');
582 END IF;
583 RETURN 1;
584 ELSE
585 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
586 okl_debug_pub.log_debug(fnd_log.level_procedure
587 ,l_module
588 ,'end debug OKLRECUB.pls.pls call validate_term');
589 END IF;
590 RETURN 0;
591 END IF;
592
593 END validate_term;
594 /**
595 This is the functions which validates the seeded eligibility criteria TERRITORY.
596 **/
597
598 FUNCTION validate_territory(p_value_tbl IN okl_varchar2_table_type
599 ,p_match_criteria IN varchar2
600 ,p_territory IN varchar2) RETURN number IS
601 l_result varchar2(1);
602 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_territory';
603 l_debug_enabled varchar2(10);
604 is_debug_procedure_on boolean;
605 is_debug_statement_on boolean;
606
607 BEGIN
608 l_debug_enabled := okl_debug_pub.check_log_enabled;
609 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
610 ,fnd_log.level_procedure);
611
612 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
613 okl_debug_pub.log_debug(fnd_log.level_procedure
614 ,l_module
615 ,'begin debug OKLRECUB.pls.pls call validate_territory');
616 END IF;
617 l_result := 'S';
618
619 IF p_match_criteria = 'INCLUDE' THEN
620
621 <<outer1>>
622 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
623 IF p_territory = p_value_tbl(j) THEN
624 l_result := 'S';
625 EXIT outer1;
626 ELSE
627 l_result := 'E';
628 END IF;
629 END LOOP;
630
631 END IF;
632
633 IF p_match_criteria = 'EXCLUDE' THEN
634
635 <<outer2>>
636 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
637 IF p_territory = p_value_tbl(j) THEN
638 l_result := 'E';
639 EXIT outer2;
640 ELSE
641 l_result := 'S';
642 END IF;
643 END LOOP;
644
645 END IF;
646
647 IF l_result = 'S' THEN
648 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
649 okl_debug_pub.log_debug(fnd_log.level_procedure
650 ,l_module
651 ,'end debug OKLRECUB.pls.pls call validate_territory');
652 END IF;
653 RETURN 1;
654 ELSE
655 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
656 okl_debug_pub.log_debug(fnd_log.level_procedure
657 ,l_module
658 ,'end debug OKLRECUB.pls.pls call validate_territory');
659 END IF;
660 RETURN 0;
661 END IF;
662
663 END validate_territory;
664 /**
665 This is the functions which validates the seeded eligibility criteria CUSTOMER_CREDIT_CLASS.
666 **/
667
668 FUNCTION validate_customer_credit_class(p_value_tbl IN okl_varchar2_table_type
669 ,p_match_criteria IN varchar2
670 ,p_cust_credit_class IN varchar2) RETURN number IS
671 l_result varchar2(1);
672 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_customer_credit_class';
673 l_debug_enabled varchar2(10);
674 is_debug_procedure_on boolean;
675 is_debug_statement_on boolean;
676
677 BEGIN
678 l_debug_enabled := okl_debug_pub.check_log_enabled;
679 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
680 ,fnd_log.level_procedure);
681
682 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
683 okl_debug_pub.log_debug(fnd_log.level_procedure
684 ,l_module
685 ,'begin debug OKLRECUB.pls.pls call validate_customer_credit_class');
686 END IF;
687 l_result := 'S';
688
689 IF p_match_criteria = 'INCLUDE' THEN
690
691 <<outer1>>
692 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
693 IF p_cust_credit_class = p_value_tbl(j) THEN
694 l_result := 'S';
695 EXIT outer1;
696 ELSE
697 l_result := 'E';
698 END IF;
699 END LOOP;
700
701 END IF;
702
703 IF p_match_criteria = 'EXCLUDE' THEN
704
705 <<outer2>>
706 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
707 IF p_cust_credit_class = p_value_tbl(j) THEN
708 l_result := 'E';
709 EXIT outer2;
710 ELSE
711 l_result := 'S';
712 END IF;
713 END LOOP;
714
715 END IF;
716
717 IF l_result = 'S' THEN
718 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
719 okl_debug_pub.log_debug(fnd_log.level_procedure
720 ,l_module
721 ,'end debug OKLRECUB.pls.pls call validate_customer_credit_class');
722 END IF;
723 RETURN 1;
724 ELSE
725 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
726 okl_debug_pub.log_debug(fnd_log.level_procedure
727 ,l_module
728 ,'end debug OKLRECUB.pls.pls call validate_customer_credit_class');
729 END IF;
730 RETURN 0;
731 END IF;
732
733 END validate_customer_credit_class;
734 /**
735 This is the functions which validates the seeded eligibility criteria ITEM.
736 **/
737
738 FUNCTION validate_item(p_value_tbl okl_number_table_type
739 ,p_match_criteria IN varchar2
740 ,p_item_tbl okl_number_table_type) RETURN number IS
741 l_result varchar2(1);
742 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_item';
743 l_debug_enabled varchar2(10);
744 is_debug_procedure_on boolean;
745 is_debug_statement_on boolean;
746
747 BEGIN
748 l_debug_enabled := okl_debug_pub.check_log_enabled;
749 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
750 ,fnd_log.level_procedure);
751
752 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
753 okl_debug_pub.log_debug(fnd_log.level_procedure
754 ,l_module
755 ,'begin debug OKLRECUB.pls.pls call validate_item');
756 END IF;
757 l_result := 'S';
758
759 IF p_match_criteria = 'INCLUDE' THEN
760
761 <<outer1>>
762 FOR i IN p_item_tbl.FIRST..p_item_tbl.LAST LOOP
763 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
764 IF p_item_tbl(i) = p_value_tbl(j) THEN
765 l_result := 'S';
766 EXIT outer1;
767 ELSE
768 l_result := 'E';
769 END IF;
770 END LOOP;
771 END LOOP;
772
773 END IF;
774
775 IF p_match_criteria = 'EXCLUDE' THEN
776
777 <<outer2>>
778 FOR i IN p_item_tbl.FIRST..p_item_tbl.LAST LOOP
779 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
780 IF p_item_tbl(i) = p_value_tbl(j) THEN
781 l_result := 'E';
782 EXIT outer2;
783 ELSE
784 l_result := 'S';
785 END IF;
786 END LOOP;
787 END LOOP;
788
789 END IF;
790
791 IF l_result = 'S' THEN
792 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
793 okl_debug_pub.log_debug(fnd_log.level_procedure
794 ,l_module
795 ,'end debug OKLRECUB.pls.pls call validate_item');
796 END IF;
797 RETURN 1;
798 ELSE
799 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
800 okl_debug_pub.log_debug(fnd_log.level_procedure
801 ,l_module
802 ,'end debug OKLRECUB.pls.pls call validate_item');
803 END IF;
804 RETURN 0;
805 END IF;
806
807 END validate_item;
808 /**
809 This is the functions which validates the seeded eligibility criteria ITEM_CATEGORIES.
810 **/
811
812 FUNCTION validate_item_categories(p_value_tbl okl_number_table_type
813 ,p_match_criteria IN varchar2
814 ,p_item_categories_tbl okl_number_table_type) RETURN number IS
815 l_result varchar2(1);
816 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_item_categories';
817 l_debug_enabled varchar2(10);
818 is_debug_procedure_on boolean;
819 is_debug_statement_on boolean;
820
821 BEGIN
822 l_debug_enabled := okl_debug_pub.check_log_enabled;
823 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
824 ,fnd_log.level_procedure);
825
826 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
827 okl_debug_pub.log_debug(fnd_log.level_procedure
828 ,l_module
829 ,'begin debug OKLRECUB.pls.pls call validate_item_categories');
830 END IF;
831 l_result := 'S';
832
833 IF p_match_criteria = 'INCLUDE' THEN
834
835 <<outer1>>
836 FOR i IN p_item_categories_tbl.FIRST..p_item_categories_tbl.LAST LOOP
837 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
838 IF p_item_categories_tbl(i) = p_value_tbl(j) THEN
839 l_result := 'S';
840 EXIT outer1;
841 ELSE
842 l_result := 'E';
843 END IF;
844 END LOOP;
845 END LOOP;
846
847 END IF;
848
849 IF p_match_criteria = 'EXCLUDE' THEN
850
851 <<outer2>>
852 FOR i IN p_item_categories_tbl.FIRST..p_item_categories_tbl.LAST LOOP
853 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
854 IF p_item_categories_tbl(i) = p_value_tbl(j) THEN
855 l_result := 'E';
856 EXIT outer2;
857 ELSE
858 l_result := 'S';
859 END IF;
860 END LOOP;
861 END LOOP;
862
863 END IF;
864
865 IF l_result = 'S' THEN
866 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
867 okl_debug_pub.log_debug(fnd_log.level_procedure
868 ,l_module
869 ,'end debug OKLRECUB.pls.pls call validate_item_categories');
870 END IF;
871 RETURN 1;
872 ELSE
873 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
874 okl_debug_pub.log_debug(fnd_log.level_procedure
875 ,l_module
876 ,'end debug OKLRECUB.pls.pls call validate_item_categories');
877 END IF;
878 RETURN 0;
879 END IF;
880
881 END validate_item_categories;
882 /**
883 This is the functions which validates the seeded eligibility criteria DEAL_SIZE.
884 **/
885
886 FUNCTION validate_deal_size(p_value1 IN varchar2
887 ,p_value2 IN varchar2
888 ,p_deal_size IN number) RETURN number IS
889 l_numval1 number;
890 l_numval2 number;
891 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate_deal_size';
892 l_debug_enabled varchar2(10);
893 is_debug_procedure_on boolean;
894 is_debug_statement_on boolean;
895
896 BEGIN
897 l_debug_enabled := okl_debug_pub.check_log_enabled;
898 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
899 ,fnd_log.level_procedure);
900
901 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
902 okl_debug_pub.log_debug(fnd_log.level_procedure
903 ,l_module
904 ,'begin debug OKLRECUB.pls.pls call validate_deal_size');
905 END IF;
906 l_numval1 := fnd_number.canonical_to_number(p_value1);
907 l_numval2 := fnd_number.canonical_to_number(p_value2);
908
909 IF (p_deal_size >= l_numval1 AND p_deal_size <= l_numval2) THEN
910 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
911 okl_debug_pub.log_debug(fnd_log.level_procedure
912 ,l_module
913 ,'end debug OKLRECUB.pls.pls call validate_deal_size');
914 END IF;
915 RETURN 1;
916 ELSE
917 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
918 okl_debug_pub.log_debug(fnd_log.level_procedure
919 ,l_module
920 ,'end debug OKLRECUB.pls.pls call validate_deal_size');
921 END IF;
922 RETURN 0;
923 END IF;
924
925 END validate_deal_size;
926 /**
927 This function evaluates the seeded adjustment category TERM
928 This function evluates whether the p_term passed is present
929 in p_value_tbl.
930 It returns the matching index in p_value_tbl. If no match found
931 then it returns Zero. p_value_tbl contains the adjustment factor corressponding
932 to different term values.
933 **/
934
935 FUNCTION evaluate_term(p_value_tbl IN okl_number_table_type
936 ,p_term IN number) RETURN number IS
937 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.evaluate_term';
938 l_debug_enabled varchar2(10);
939 is_debug_procedure_on boolean;
940 is_debug_statement_on boolean;
941
942 BEGIN
943 l_debug_enabled := okl_debug_pub.check_log_enabled;
944 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
945 ,fnd_log.level_procedure);
946
947 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
948 okl_debug_pub.log_debug(fnd_log.level_procedure
949 ,l_module
950 ,'begin debug OKLRECUB.pls.pls call evaluate_term');
951 END IF;
952
953 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
954
955 -- dbms_output.put_line('p_term_tbl(i)= '||p_term_tbl(i)|| 'p_value_tbl(j) = '||p_value_tbl(j));
956
957 IF p_term = p_value_tbl(j) THEN
958 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
959 okl_debug_pub.log_debug(fnd_log.level_procedure
960 ,l_module
961 ,'end debug OKLRECUB.pls.pls call evaluate_term');
962 END IF;
963 RETURN j;
964 END IF;
965
966 END LOOP;
967
968 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
969 okl_debug_pub.log_debug(fnd_log.level_procedure
970 ,l_module
971 ,'end debug OKLRECUB.pls.pls call evaluate_term');
972 END IF;
973 RETURN 0;
974 END evaluate_term;
975 /**
976 This function evaluates the seeded adjustment category TERRITORY
977 This function evaluates whether the p_territory passed is present
978 in p_value_tbl.
979 It returns the matching index in p_value_tbl. If no match found
980 then it returns Zero. p_value_tbl contains the adjustment factor corressponding
981 to different term values.
982 **/
983
984 FUNCTION evaluate_territory(p_value_tbl IN okl_varchar2_table_type
985 ,p_territory IN varchar2) RETURN number IS
986 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.evaluate_territory';
987 l_debug_enabled varchar2(10);
988 is_debug_procedure_on boolean;
989 is_debug_statement_on boolean;
990
991 BEGIN
992 l_debug_enabled := okl_debug_pub.check_log_enabled;
993 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
994 ,fnd_log.level_procedure);
995
996 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
997 okl_debug_pub.log_debug(fnd_log.level_procedure
998 ,l_module
999 ,'begin debug OKLRECUB.pls.pls call evaluate_territory');
1000 END IF;
1001
1002 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
1003
1004 -- dbms_output.put_line('p_term_tbl(i)= '||p_term_tbl(i)|| 'p_value_tbl(j) = '||p_value_tbl(j));
1005
1006 IF p_territory = p_value_tbl(j) THEN
1007 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1008 okl_debug_pub.log_debug(fnd_log.level_procedure
1009 ,l_module
1010 ,'end debug OKLRECUB.pls.pls call evaluate_territory');
1011 END IF;
1012 RETURN j;
1013 END IF;
1014
1015 END LOOP;
1016
1017 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1018 okl_debug_pub.log_debug(fnd_log.level_procedure
1019 ,l_module
1020 ,'end debug OKLRECUB.pls.pls call evaluate_territory');
1021 END IF;
1022 RETURN 0;
1023 END evaluate_territory;
1024 /**
1025 This function evaluates the seeded adjustment category CUSTOMER_CREDIT_CLASS
1026 This function evaluates whether the p_customer_credit_class passed is present
1027 in p_value_tbl.
1028 It returns the matching index in p_value_tbl. If no match found
1029 then it returns Zero. p_value_tbl contains the adjustment factor corressponding
1030 to different term values.
1031 **/
1032
1033 FUNCTION evaluate_customer_credit_class(p_value_tbl IN okl_varchar2_table_type
1034 ,p_customer_credit_class IN varchar2) RETURN number IS
1035 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.evaluate_customer_credit_class';
1036 l_debug_enabled varchar2(10);
1037 is_debug_procedure_on boolean;
1038 is_debug_statement_on boolean;
1039
1040 BEGIN
1041 l_debug_enabled := okl_debug_pub.check_log_enabled;
1042 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1043 ,fnd_log.level_procedure);
1044
1045 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1046 okl_debug_pub.log_debug(fnd_log.level_procedure
1047 ,l_module
1048 ,'begin debug OKLRECUB.pls.pls call evaluate_customer_credit_class');
1049 END IF;
1050
1051 -- check for logging on STATEMENT level
1052
1053 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1054 ,fnd_log.level_statement);
1055
1056 FOR j IN p_value_tbl.FIRST..p_value_tbl.LAST LOOP
1057
1058 -- dbms_output.put_line('p_term_tbl(i)= '||p_term_tbl(i)|| 'p_value_tbl(j) = '||p_value_tbl(j));
1059
1060 IF p_customer_credit_class = p_value_tbl(j) THEN
1061 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1062 okl_debug_pub.log_debug(fnd_log.level_procedure
1063 ,l_module
1064 ,'end debug OKLRECUB.pls.pls call evaluate_customer_credit_class');
1065 END IF;
1066 RETURN j;
1067 END IF;
1068
1069 END LOOP;
1070
1071 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1072 okl_debug_pub.log_debug(fnd_log.level_procedure
1073 ,l_module
1074 ,'end debug OKLRECUB.pls.pls call evaluate_customer_credit_class');
1075 END IF;
1076 RETURN 0;
1077 END evaluate_customer_credit_class;
1078 /**
1079 This function evaluates the seeded adjustment category DEAL_SIZE
1080 This function evluates whether the p_deal_size passed is within
1081 range p_value1 to p_value2
1082 If yes Returns 1 else returns 0.
1083 **/
1084
1085 FUNCTION evaluate_deal_size(p_value1 IN varchar2
1086 ,p_value2 IN varchar2
1087 ,p_deal_size IN number) RETURN number IS
1088 l_numval1 number;
1089 l_numval2 number;
1090 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.evaluate_deal_size';
1091 l_debug_enabled varchar2(10);
1092 is_debug_procedure_on boolean;
1093 is_debug_statement_on boolean;
1094
1095 BEGIN
1096 l_debug_enabled := okl_debug_pub.check_log_enabled;
1097 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1098 ,fnd_log.level_procedure);
1099
1100 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1101 okl_debug_pub.log_debug(fnd_log.level_procedure
1102 ,l_module
1103 ,'begin debug OKLRECUB.pls.pls call evaluate_deal_size');
1104 END IF;
1105
1106 -- check for logging on STATEMENT level
1107
1108 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1109 ,fnd_log.level_statement);
1110 l_numval1 := fnd_number.canonical_to_number(p_value1);
1111 l_numval2 := fnd_number.canonical_to_number(p_value2);
1112
1113 IF (p_deal_size >= l_numval1 AND p_deal_size <= l_numval2) THEN
1114 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1115 okl_debug_pub.log_debug(fnd_log.level_procedure
1116 ,l_module
1117 ,'end debug OKLRECUB.pls.pls call evaluate_deal_size');
1118 END IF;
1119 RETURN 1;
1120 ELSE
1121 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1122 okl_debug_pub.log_debug(fnd_log.level_procedure
1123 ,l_module
1124 ,'end debug OKLRECUB.pls.pls call evaluate_deal_size');
1125 END IF;
1126 RETURN 0;
1127 END IF;
1128
1129 END evaluate_deal_size;
1130 /**
1131 This procedure validates the eligibility criteria present on source object.
1132 The fields in p_okl_ec_rec, co.rressponding to the eligibility criteria category
1133 applicable to the source type, should be filled in p_okl_ec_rec.
1134 **/
1135
1136 PROCEDURE validate(
1137 p_api_version IN number
1138 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1139 ,x_return_status OUT nocopy varchar2
1140 ,x_msg_count OUT nocopy number
1141 ,x_msg_data OUT nocopy varchar2
1142 ,p_okl_ec_rec IN OUT nocopy okl_ec_rec_type
1143 ,x_eligible OUT nocopy boolean
1144 ) IS
1145
1146 CURSOR get_ec(src_id IN number
1147 ,src_obj_code IN varchar2) IS
1148 SELECT ech.match_criteria_code mc_hdr
1149 ,ech.validation_code
1150 ,fun.name evaluation_function
1151 ,ecl.criteria_id
1152 ,ecl.match_criteria_code
1153 ,ecl.effective_from_date
1154 ,ecl.effective_to_date
1155 ,ecc.value_type_code
1156 ,ecc.seeded_yn
1157 ,ecc.crit_cat_name ec_name
1158 FROM okl_fe_criteria_set ech
1159 ,okl_fe_criteria ecl
1160 ,okl_fe_crit_cat_def_v ecc
1161 ,okl_data_src_fnctns_v fun
1162 WHERE ech.criteria_set_id = ecl.criteria_set_id
1163 AND ech.source_id = src_id
1164 AND ech.source_object_code = src_obj_code
1165 AND ecl.crit_cat_def_id = ecc.crit_cat_def_id
1166 AND ecc.ecc_ac_flag = 'ECC'
1167 AND ecc.function_id = fun.id;
1168
1169 TYPE ec_tbl_type IS TABLE OF get_ec%ROWTYPE INDEX BY BINARY_INTEGER;
1170
1171 CURSOR get_ec_values(ec_ln_id IN number) IS
1172 SELECT operator_code
1173 ,crit_cat_value1
1174 ,crit_cat_value2
1175 FROM okl_fe_criterion_values
1176 WHERE criteria_id = ec_ln_id;
1177 ec_tbl ec_tbl_type;
1178 ret boolean;
1179 fun_ret number;
1180 call_user boolean;
1181 i number;
1182 k number;
1183 l_operator_code varchar2(30);
1184 l_value1 varchar2(240);
1185 l_value2 varchar2(240);
1186 l_formatted_amt varchar2(40);
1187 l_formatted_amt1 varchar2(40);
1188 l_formatted_amt2 varchar2(40);
1189 l_index number;
1190 l_varchar_value_tbl okl_varchar2_table_type;
1191 l_ec_values_tbl okl_ec_values_tbl_type;
1192 l_num_value_tbl okl_number_table_type;
1193 l_api_version CONSTANT number := 1.0;
1194 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1195 l_msg_count number;
1196 l_msg_data varchar2(2000);
1197 l_init_msg_list varchar2(1) DEFAULT OKL_API.G_FALSE;
1198 l_function_name okl_data_src_fnctns_v.name%TYPE;
1199 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.validate';
1200 l_debug_enabled varchar2(10);
1201 is_debug_procedure_on boolean;
1202 is_debug_statement_on boolean;
1203
1204 BEGIN
1205 l_debug_enabled := okl_debug_pub.check_log_enabled;
1206 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1207 ,fnd_log.level_procedure);
1208
1209 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1210 okl_debug_pub.log_debug(fnd_log.level_procedure
1211 ,l_module
1212 ,'begin debug OKLRECUB.pls.pls call validate');
1213 END IF;
1214 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1215 ,fnd_log.level_statement);
1216 --dbms_output.put_line('start of new ');
1217
1218 --log the incoming values
1219 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1220 okl_debug_pub.log_debug(fnd_log.level_statement
1221 ,l_module
1222 ,' p_okl_ec_rec.SRC_ID = ' || p_okl_ec_rec.SRC_ID ||
1223 ' p_okl_ec_rec.SOURCE_NAME = ' || p_okl_ec_rec.SOURCE_NAME ||
1224 ' p_okl_ec_rec.TARGET_ID = ' || p_okl_ec_rec.TARGET_ID ||
1225 ' p_okl_ec_rec.SRC_TYPE = ' || p_okl_ec_rec.SRC_TYPE ||
1226 ' p_okl_ec_rec.TARGET_TYPE = ' || p_okl_ec_rec.TARGET_TYPE ||
1227 ' p_okl_ec_rec.TARGET_EFF_FROM = ' || p_okl_ec_rec.TARGET_EFF_FROM ||
1228 ' p_okl_ec_rec.TERM = ' || p_okl_ec_rec.TERM ||
1229 ' p_okl_ec_rec.TERRITORY = ' || p_okl_ec_rec.TERRITORY ||
1230 ' p_okl_ec_rec.DEAL_SIZE = ' || p_okl_ec_rec.DEAL_SIZE ||
1231 ' p_okl_ec_rec.CUSTOMER_CREDIT_CLASS = ' || p_okl_ec_rec.CUSTOMER_CREDIT_CLASS ||
1232 ' p_okl_ec_rec.DOWN_PAYMENT = ' || p_okl_ec_rec.DOWN_PAYMENT ||
1233 ' p_okl_ec_rec.ADVANCE_RENT = ' || p_okl_ec_rec.ADVANCE_RENT ||
1234 ' p_okl_ec_rec.TRADE_IN_VALUE = ' || p_okl_ec_rec.TRADE_IN_VALUE ||
1235 ' p_okl_ec_rec.ITEM_TABLE_COUNT = ' || p_okl_ec_rec.ITEM_TABLE.COUNT ||
1236 ' p_okl_ec_rec.item_cat_table_count = ' || p_okl_ec_rec.item_categories_table.COUNT
1237 );
1238 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1239
1240
1241
1242 ret := true;
1243 fun_ret := 1;
1244
1245 --get all the ec defined on the source object of type= src_type
1246
1247 i := 1;
1248
1249 FOR ec_rec IN get_ec(p_okl_ec_rec.src_id, p_okl_ec_rec.src_type) LOOP
1250 ec_tbl(i).mc_hdr := ec_rec.mc_hdr;
1251 ec_tbl(i).match_criteria_code := ec_rec.match_criteria_code;
1252 ec_tbl(i).validation_code := ec_rec.validation_code;
1253 ec_tbl(i).evaluation_function := ec_rec.evaluation_function;
1254 ec_tbl(i).criteria_id := ec_rec.criteria_id;
1255 ec_tbl(i).effective_from_date := ec_rec.effective_from_date;
1256 ec_tbl(i).effective_to_date := ec_rec.effective_to_date;
1257 ec_tbl(i).value_type_code := ec_rec.value_type_code;
1258 ec_tbl(i).seeded_yn := ec_rec.seeded_yn;
1259 ec_tbl(i).ec_name := ec_rec.ec_name;
1260 i := i + 1;
1261 END LOOP; -- For each ec defined on the source object do
1262 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1263
1264 IF ec_tbl.COUNT > 0 THEN
1265
1266 FOR i IN ec_tbl.FIRST..ec_tbl.LAST LOOP
1267
1268 -- dbms_output.put_line('ec_tbl(i).EC_NAME = ' || ec_tbl(i).EC_NAME);
1269
1270 IF ec_tbl(i).seeded_yn = 'Y' THEN
1271 IF (ec_tbl(i).ec_name = 'ADVANCE RENT') THEN
1272
1273 --dbms_output.put_line('ADV RENT p_okl_ec_rec.TARGET_EFF_FROM = '||p_okl_ec_rec.TARGET_EFF_FROM);
1274 --dbms_output.put_line('ADV RENT ec_tbl(i).effective_from_date = '||ec_tbl(i).effective_from_date);
1275 --dbms_output.put_line('ADV RENT ec_tbl(i).effective_to_date ='||ec_tbl(i).effective_to_date);
1276
1277 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1278 ,to_date('01-01-9999'
1279 ,'dd-mm-yyyy')))
1280 AND (p_okl_ec_rec.advance_rent IS NOT NULL)) THEN
1281
1282 --dbms_output.put_line('passed date validations ');
1283
1284 OPEN get_ec_values(ec_tbl(i).criteria_id);
1285 FETCH get_ec_values INTO l_operator_code
1286 ,l_value1
1287 ,l_value2 ;
1288 CLOSE get_ec_values;
1289
1290 --this validate function will return either 1 or 0 only
1291
1292 fun_ret := validate_advance_rent(l_operator_code
1293 ,l_value1
1294 ,p_okl_ec_rec.advance_rent);
1295 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1296 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1297 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1298 okl_debug_pub.log_debug(fnd_log.level_procedure
1299 ,l_module
1300 ,'end debug OKLRECUB.pls.pls call validate');
1301 END IF;
1302 x_eligible := true;
1303 x_return_status := okl_api.g_ret_sts_success;
1304 RETURN;
1305 END IF;
1306 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1307 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1308 okl_debug_pub.log_debug(fnd_log.level_procedure
1309 ,l_module
1310 ,'end debug OKLRECUB.pls.pls call validate');
1311 END IF;
1312 x_eligible := false;
1313 x_return_status := okl_api.g_ret_sts_success;
1314 RETURN;
1315 END IF;
1316 END IF;
1317 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1318 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1319 IF fun_ret = 0 THEN
1320 l_formatted_amt := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value1)
1321 ,p_okl_ec_rec.currency_code);
1322 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_single(ec_tbl(i).ec_name
1323 ,l_operator_code
1324 ,l_formatted_amt
1325 ,p_okl_ec_rec.source_name);
1326 IF ec_tbl(i).validation_code = 'WARNING' THEN
1327 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1328 ,'WARNING');
1329 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1330 ,'WARNING');
1331 ELSE
1332 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1333 ,'ERROR');
1334 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1335 ,'ERROR');
1336 END IF;
1337 ELSE
1338 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1339 ,p_okl_ec_rec.source_name);
1340 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1341 END IF;
1342 END IF;
1343 ELSE
1344 fun_ret := 1;
1345 END IF;
1346 END IF;
1347 IF (ec_tbl(i).ec_name = 'CUSTOMER CREDIT CLASS') THEN
1348 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1349 ,to_date('01-01-9999'
1350 ,'dd-mm-yyyy')))
1351 AND (p_okl_ec_rec.customer_credit_class IS NOT NULL)) THEN
1352 k := 1;
1353 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1354 l_varchar_value_tbl(k) := ec_val_rec.crit_cat_value2;
1355 k := k + 1;
1356 END LOOP;
1357
1358 --this validate function will return either 1 or 0 only
1359
1360 fun_ret := validate_customer_credit_class(l_varchar_value_tbl
1361 ,ec_tbl(i).match_criteria_code
1362 ,p_okl_ec_rec.customer_credit_class);
1363 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1364 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1365 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1366 okl_debug_pub.log_debug(fnd_log.level_procedure
1367 ,l_module
1368 ,'end debug OKLRECUB.pls.pls call validate');
1369 END IF;
1370 x_eligible := true;
1371 x_return_status := okl_api.g_ret_sts_success;
1372 RETURN;
1373 END IF;
1374 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1375 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1376 okl_debug_pub.log_debug(fnd_log.level_procedure
1377 ,l_module
1378 ,'end debug OKLRECUB.pls.pls call validate');
1379 END IF;
1380 x_eligible := false;
1381 x_return_status := okl_api.g_ret_sts_success;
1382 RETURN;
1383 END IF;
1384 END IF;
1385 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1386 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1387 IF fun_ret = 0 THEN
1388 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_multiple(ec_tbl(i).ec_name
1389 ,p_okl_ec_rec.source_name);
1390 IF ec_tbl(i).validation_code = 'WARNING' THEN
1391 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1392 ,'WARNING');
1393 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1394 ,'WARNING');
1395 ELSE
1396 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1397 ,'ERROR');
1398 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1399 ,'ERROR');
1400 END IF;
1401 ELSE
1402 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1403 ,p_okl_ec_rec.source_name);
1404 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1405 END IF;
1406 END IF;
1407 ELSE
1408 fun_ret := 1;
1409 END IF;
1410 END IF;
1411 IF (ec_tbl(i).ec_name = 'DEAL SIZE') THEN
1412 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1413 ,to_date('01-01-9999'
1414 ,'dd-mm-yyyy')))
1415 AND (p_okl_ec_rec.deal_size IS NOT NULL)) THEN
1416 OPEN get_ec_values(ec_tbl(i).criteria_id);
1417 FETCH get_ec_values INTO l_operator_code
1418 ,l_value1
1419 ,l_value2 ;
1420 CLOSE get_ec_values;
1421
1422 --this validate function will return either 1 or 0 only
1423
1424 fun_ret := validate_deal_size(l_value1
1425 ,l_value2
1426 ,p_okl_ec_rec.deal_size); --dbms_output.put_line('fun_ret deal size = '||fun_ret);
1427 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1428 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1429 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1430 okl_debug_pub.log_debug(fnd_log.level_procedure
1431 ,l_module
1432 ,'end debug OKLRECUB.pls.pls call validate');
1433 END IF;
1434 x_eligible := true;
1435 x_return_status := okl_api.g_ret_sts_success;
1436 RETURN;
1437 END IF;
1438 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1439 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1440 okl_debug_pub.log_debug(fnd_log.level_procedure
1441 ,l_module
1442 ,'end debug OKLRECUB.pls.pls call validate');
1443 END IF;
1444 x_eligible := false;
1445 x_return_status := okl_api.g_ret_sts_success;
1446 RETURN;
1447 END IF;
1448 END IF;
1449 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1450 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1451 IF fun_ret = 0 THEN
1452 l_formatted_amt1 := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value1)
1453 ,p_okl_ec_rec.currency_code);
1454 l_formatted_amt2 := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value2)
1455 ,p_okl_ec_rec.currency_code);
1456 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_range(ec_tbl(i).ec_name
1457 ,l_formatted_amt1
1458 ,l_formatted_amt2
1459 ,p_okl_ec_rec.source_name);
1460 IF ec_tbl(i).validation_code = 'WARNING' THEN
1461 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1462 ,'WARNING');
1463 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1464 ,'WARNING');
1465 ELSE
1466 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1467 ,'ERROR');
1468 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1469 ,'ERROR');
1470 END IF;
1471 ELSE
1472 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1473 ,p_okl_ec_rec.source_name);
1474 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1475 END IF;
1476 END IF;
1477 ELSE
1478 fun_ret := 1;
1479 END IF;
1480 END IF;
1481 IF (ec_tbl(i).ec_name = 'DOWN PAYMENT') THEN
1482 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1483 ,to_date('01-01-9999'
1484 ,'dd-mm-yyyy')))
1485 AND (p_okl_ec_rec.down_payment IS NOT NULL)) THEN
1486 OPEN get_ec_values(ec_tbl(i).criteria_id);
1487 FETCH get_ec_values INTO l_operator_code
1488 ,l_value1
1489 ,l_value2 ;
1490 CLOSE get_ec_values;
1491
1492 --this validate function will return either 1 or 0 only
1493
1494 fun_ret := validate_down_payment(l_operator_code
1495 ,l_value1
1496 ,p_okl_ec_rec.down_payment);
1497
1498 --dbms_output.put_line('fun_ret of down payment= '||fun_ret);
1499 --dbms_output.put_line('l_value1 = '||l_value1);
1500
1501 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1502 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1503 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1504 okl_debug_pub.log_debug(fnd_log.level_procedure
1505 ,l_module
1506 ,'end debug OKLRECUB.pls.pls call validate');
1507 END IF;
1508 x_eligible := true;
1509 x_return_status := okl_api.g_ret_sts_success;
1510 RETURN;
1511 END IF;
1512 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1513 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1514 okl_debug_pub.log_debug(fnd_log.level_procedure
1515 ,l_module
1516 ,'end debug OKLRECUB.pls.pls call validate');
1517 END IF;
1518 x_eligible := false;
1519 x_return_status := okl_api.g_ret_sts_success;
1520 RETURN;
1521 END IF;
1522 END IF;
1523 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1524 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1525 IF fun_ret = 0 THEN
1526 l_formatted_amt := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value1)
1527 ,p_okl_ec_rec.currency_code);
1528 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_single(ec_tbl(i).ec_name
1529 ,l_operator_code
1530 ,l_formatted_amt
1531 ,p_okl_ec_rec.source_name);
1532 IF ec_tbl(i).validation_code = 'WARNING' THEN
1533 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1534 ,'WARNING');
1535 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1536 ,'WARNING');
1537 ELSE
1538 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1539 ,'ERROR');
1540 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1541 ,'ERROR');
1542 END IF;
1543 ELSE
1544 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1545 ,p_okl_ec_rec.source_name);
1546 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1547 END IF;
1548 END IF;
1549 ELSE
1550 fun_ret := 1;
1551 END IF;
1552 END IF;
1553 IF (ec_tbl(i).ec_name = 'ITEM') THEN
1554 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1555 ,to_date('01-01-9999'
1556 ,'dd-mm-yyyy')))
1557 AND (p_okl_ec_rec.item_table.COUNT > 0)) THEN
1558 k := 1;
1559 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1560 l_num_value_tbl(k) := fnd_number.canonical_to_number(ec_val_rec.crit_cat_value2);
1561 k := k + 1;
1562 END LOOP;
1563
1564 --this validate function will return either 1 or 0 only
1565
1566 fun_ret := validate_item(l_num_value_tbl
1567 ,ec_tbl(i).match_criteria_code
1568 ,p_okl_ec_rec.item_table);
1569 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1570 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1571 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1572 okl_debug_pub.log_debug(fnd_log.level_procedure
1573 ,l_module
1574 ,'end debug OKLRECUB.pls.pls call validate');
1575 END IF;
1576 x_eligible := true;
1577 x_return_status := okl_api.g_ret_sts_success;
1578 RETURN;
1579 END IF;
1580 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1581 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1582 okl_debug_pub.log_debug(fnd_log.level_procedure
1583 ,l_module
1584 ,'end debug OKLRECUB.pls.pls call validate');
1585 END IF;
1586 x_eligible := false;
1587 x_return_status := okl_api.g_ret_sts_success;
1588 RETURN;
1589 END IF;
1590 END IF;
1591 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1592 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1593 IF fun_ret = 0 THEN
1594 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_multiple(ec_tbl(i).ec_name
1595 ,p_okl_ec_rec.source_name);
1596 IF ec_tbl(i).validation_code = 'WARNING' THEN
1597 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1598 ,'WARNING');
1599 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1600 ,'WARNING');
1601 ELSE
1602 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1603 ,'ERROR');
1604 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1605 ,'ERROR');
1606 END IF;
1607 ELSE
1608 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1609 ,p_okl_ec_rec.source_name);
1610 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1611 END IF;
1612 END IF;
1613 ELSE
1614 fun_ret := 1;
1615 END IF;
1616 END IF;
1617 IF (ec_tbl(i).ec_name = 'ITEM CATEGORIES') THEN
1618 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1619 ,to_date('01-01-9999'
1620 ,'dd-mm-yyyy')))
1621 AND (p_okl_ec_rec.item_categories_table.COUNT > 0)) THEN
1622 k := 1;
1623 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1624 l_num_value_tbl(k) := fnd_number.canonical_to_number(ec_val_rec.crit_cat_value2);
1625 k := k + 1;
1626 END LOOP;
1627
1628 --this validate function will return either 1 or 0 only
1629
1630 fun_ret := validate_item_categories(l_num_value_tbl
1631 ,ec_tbl(i).match_criteria_code
1632 ,p_okl_ec_rec.item_categories_table);
1633 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1634 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1635 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1636 okl_debug_pub.log_debug(fnd_log.level_procedure
1637 ,l_module
1638 ,'end debug OKLRECUB.pls.pls call validate');
1639 END IF;
1640 x_eligible := true;
1641 x_return_status := okl_api.g_ret_sts_success;
1642 RETURN;
1643 END IF;
1644 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1645 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1646 okl_debug_pub.log_debug(fnd_log.level_procedure
1647 ,l_module
1648 ,'end debug OKLRECUB.pls.pls call validate');
1649 END IF;
1650 x_eligible := false;
1651 x_return_status := okl_api.g_ret_sts_success;
1652 RETURN;
1653 END IF;
1654 END IF;
1655 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1656 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1657 IF fun_ret = 0 THEN
1658 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_multiple(ec_tbl(i).ec_name
1659 ,p_okl_ec_rec.source_name);
1660 IF ec_tbl(i).validation_code = 'WARNING' THEN
1661 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1662 ,'WARNING');
1663 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1664 ,'WARNING');
1665 ELSE
1666 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1667 ,'ERROR');
1668 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1669 ,'ERROR');
1670 END IF;
1671 ELSE
1672 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1673 ,p_okl_ec_rec.source_name);
1674 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1675 END IF;
1676 END IF;
1677 ELSE
1678 fun_ret := 1;
1679 END IF;
1680 END IF;
1681 IF (ec_tbl(i).ec_name = 'TERM') THEN
1682 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1683 ,to_date('01-01-9999'
1684 ,'dd-mm-yyyy')))
1685 AND (p_okl_ec_rec.term IS NOT NULL)) THEN
1686 k := 1;
1687 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1688 l_num_value_tbl(k) := fnd_number.canonical_to_number(ec_val_rec.crit_cat_value2);
1689
1690 --dbms_output.put_line('l_num_value_tbl(k) = '||l_num_value_tbl(k));
1691
1692 k := k + 1;
1693 END LOOP;
1694
1695 --dbms_output.put_line('ec_tbl(i).match_criteria_code= '||ec_tbl(i).match_criteria_code);
1696 --this validate function will return either 1 or 0 only
1697
1698 fun_ret := validate_term(l_num_value_tbl
1699 ,ec_tbl(i).match_criteria_code
1700 ,p_okl_ec_rec.term);
1701 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1702
1703 --dbms_output.put_line('ec_tbl(i).mc_hdr = '||ec_tbl(i).mc_hdr || 'fun_ret = '|| fun_ret);
1704
1705 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1706 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1707 okl_debug_pub.log_debug(fnd_log.level_procedure
1708 ,l_module
1709 ,'end debug OKLRECUB.pls.pls call validate');
1710 END IF;
1711 x_eligible := true;
1712 x_return_status := okl_api.g_ret_sts_success;
1713 RETURN;
1714 END IF;
1715 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1716 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1717 okl_debug_pub.log_debug(fnd_log.level_procedure
1718 ,l_module
1719 ,'end debug OKLRECUB.pls.pls call validate');
1720 END IF;
1721 x_eligible := false;
1722 x_return_status := okl_api.g_ret_sts_success;
1723 RETURN;
1724 END IF;
1725 END IF;
1726 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1727 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1728 IF fun_ret = 0 THEN
1729 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_multiple(ec_tbl(i).ec_name
1730 ,p_okl_ec_rec.source_name);
1731 IF ec_tbl(i).validation_code = 'WARNING' THEN
1732 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1733 ,'WARNING');
1734 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1735 ,'WARNING');
1736 ELSE
1737 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1738 ,'ERROR');
1739 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1740 ,'ERROR');
1741 END IF;
1742 ELSE
1743 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1744 ,p_okl_ec_rec.source_name);
1745 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1746 END IF;
1747 END IF;
1748 ELSE
1749 fun_ret := 1;
1750 END IF;
1751 END IF;
1752 IF (ec_tbl(i).ec_name = 'TERRITORY') THEN
1753 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1754 ,to_date('01-01-9999'
1755 ,'dd-mm-yyyy')))
1756 AND (p_okl_ec_rec.territory IS NOT NULL)) THEN
1757 k := 1;
1758 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1759 l_varchar_value_tbl(k) := ec_val_rec.crit_cat_value2;
1760 k := k + 1;
1761 END LOOP;
1762
1763 --this validate function will return either 1 or 0 only
1764
1765 fun_ret := validate_territory(l_varchar_value_tbl
1766 ,ec_tbl(i).match_criteria_code
1767 ,p_okl_ec_rec.territory);
1768 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1769 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1770 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1771 okl_debug_pub.log_debug(fnd_log.level_procedure
1772 ,l_module
1773 ,'end debug OKLRECUB.pls.pls call validate');
1774 END IF;
1775 x_eligible := true;
1776 x_return_status := okl_api.g_ret_sts_success;
1777 RETURN;
1778 END IF;
1779 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1780 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1781 okl_debug_pub.log_debug(fnd_log.level_procedure
1782 ,l_module
1783 ,'end debug OKLRECUB.pls.pls call validate');
1784 END IF;
1785 x_eligible := false;
1786 x_return_status := okl_api.g_ret_sts_success;
1787 RETURN;
1788 END IF;
1789 END IF;
1790 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1791 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1792 IF fun_ret = 0 THEN
1793 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_multiple(ec_tbl(i).ec_name
1794 ,p_okl_ec_rec.source_name);
1795 IF ec_tbl(i).validation_code = 'WARNING' THEN
1796 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1797 ,'WARNING');
1798 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1799 ,'WARNING');
1800 ELSE
1801 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1802 ,'ERROR');
1803 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1804 ,'ERROR');
1805 END IF;
1806 ELSE
1807 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1808 ,p_okl_ec_rec.source_name);
1809 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1810 END IF;
1811 END IF;
1812 ELSE
1813 fun_ret := 1;
1814 END IF;
1815 END IF;
1816 IF (ec_tbl(i).ec_name = 'TRADE IN VALUE') THEN
1817 IF ((p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1818 ,to_date('01-01-9999'
1819 ,'dd-mm-yyyy')))
1820 AND (p_okl_ec_rec.trade_in_value IS NOT NULL)) THEN
1821 OPEN get_ec_values(ec_tbl(i).criteria_id);
1822 FETCH get_ec_values INTO l_operator_code
1823 ,l_value1
1824 ,l_value2 ;
1825 CLOSE get_ec_values;
1826
1827 --this validate function will return either 1 or 0 only
1828
1829 fun_ret := validate_trade_in_value(l_operator_code
1830 ,l_value1
1831 ,l_value2
1832 ,p_okl_ec_rec.trade_in_value);
1833 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1834 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1835 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1836 okl_debug_pub.log_debug(fnd_log.level_procedure
1837 ,l_module
1838 ,'end debug OKLRECUB.pls.pls call validate');
1839 END IF;
1840 x_eligible := true;
1841 x_return_status := okl_api.g_ret_sts_success;
1842 RETURN;
1843 END IF;
1844 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1845 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1846 okl_debug_pub.log_debug(fnd_log.level_procedure
1847 ,l_module
1848 ,'end debug OKLRECUB.pls.pls call validate');
1849 END IF;
1850 x_eligible := false;
1851 x_return_status := okl_api.g_ret_sts_success;
1852 RETURN;
1853 END IF;
1854 END IF;
1855 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1856 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1857 IF fun_ret = 0 THEN
1858 l_formatted_amt1 := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value1)
1859 ,p_okl_ec_rec.currency_code);
1860 l_formatted_amt2 := okl_accounting_util.format_amount(fnd_number.canonical_to_number(l_value2)
1861 ,p_okl_ec_rec.currency_code);
1862 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_range(ec_tbl(i).ec_name
1863 ,l_formatted_amt1
1864 ,l_formatted_amt2
1865 ,p_okl_ec_rec.source_name);
1866 IF ec_tbl(i).validation_code = 'WARNING' THEN
1867 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1868 ,'WARNING');
1869 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1870 ,'WARNING');
1871 ELSE
1872 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1873 ,'ERROR');
1874 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1875 ,'ERROR');
1876 END IF;
1877 ELSE
1878 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1879 ,p_okl_ec_rec.source_name);
1880 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1881 END IF;
1882 END IF;
1883 ELSE
1884 fun_ret := 1;
1885 END IF;
1886 END IF;
1887 END IF; --of Seeded_yn= Y
1888 END LOOP;
1889
1890 --set G_EC_REC global variable to be used in user defined function to access values of p_okl_ec_rec
1891
1892 g_ec_rec := p_okl_ec_rec;
1893
1894 --call evaluation function attached to user defined criteria
1895
1896 FOR i IN ec_tbl.FIRST..ec_tbl.LAST LOOP
1897 IF ec_tbl(i).seeded_yn = 'N' THEN
1898 IF (p_okl_ec_rec.target_eff_from BETWEEN ec_tbl(i).effective_from_date AND nvl(ec_tbl(i).effective_to_date
1899 ,to_date('01-01-9999'
1900 ,'dd-mm-yyyy'))) THEN
1901 k := 1;
1902 FOR ec_val_rec IN get_ec_values(ec_tbl(i).criteria_id) LOOP
1903 l_ec_values_tbl(k).operator_code := ec_val_rec.operator_code;
1904 l_ec_values_tbl(k).value1 := ec_val_rec.crit_cat_value1;
1905 l_ec_values_tbl(k).value2 := ec_val_rec.crit_cat_value2;
1906 l_ec_values_tbl(k).match_criteria_code := ec_tbl(i).match_criteria_code;
1907 k := k + 1;
1908 END LOOP;
1909 g_ec_values_tbl := l_ec_values_tbl;
1910
1911 --call the execute function API
1912 l_function_name := ec_tbl(i).EVALUATION_FUNCTION;
1913 okl_execute_formula_pvt.execute_eligibility_criteria ( p_api_version => l_api_version,
1914 p_init_msg_list => l_init_msg_list,
1915 x_return_status => x_return_status,
1916 x_msg_count => x_msg_count,
1917 x_msg_data => x_msg_data,
1918 p_function_name => l_function_name,
1919 x_value => fun_ret
1920 );
1921 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1922 okl_debug_pub.log_debug(fnd_log.level_statement
1923 ,l_module
1924 ,'okl_execute_formula_pvt.execute_eligibility_criteria returned with status ' ||
1925 x_return_status ||
1926 ' x_msg_data ' ||
1927 x_msg_data);
1928 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1929
1930 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1931 RAISE okl_api.g_exception_unexpected_error;
1932 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1933 RAISE okl_api.g_exception_error;
1934 END IF;
1935
1936 IF p_okl_ec_rec.validation_mode = 'LOV' THEN
1937 IF (ec_tbl(i).mc_hdr = 'ONE' AND fun_ret = 1) THEN
1938 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1939 okl_debug_pub.log_debug(fnd_log.level_procedure
1940 ,l_module
1941 ,'end debug OKLRECUB.pls.pls call validate');
1942 END IF;
1943 x_eligible := true;
1944 x_return_status := okl_api.g_ret_sts_success;
1945 RETURN;
1946 END IF;
1947 IF (ec_tbl(i).mc_hdr = 'ALL' AND fun_ret = 0) THEN
1948 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1949 okl_debug_pub.log_debug(fnd_log.level_procedure
1950 ,l_module
1951 ,'end debug OKLRECUB.pls.pls call validate');
1952 END IF;
1953 x_eligible := false;
1954 x_return_status := okl_api.g_ret_sts_success;
1955 RETURN;
1956 END IF;
1957 END IF;
1958 IF p_okl_ec_rec.validation_mode = 'QA' THEN
1959 l_index := p_okl_ec_rec.qa_result_tbl.COUNT + 1;
1960 IF fun_ret = 0 THEN
1961 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_user(ec_tbl(i).ec_name
1962 ,p_okl_ec_rec.source_name);
1963 IF ec_tbl(i).validation_code = 'WARNING' THEN
1964 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1965 ,'WARNING');
1966 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1967 ,'WARNING');
1968 ELSE
1969 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1970 ,'ERROR');
1971 p_okl_ec_rec.consolidated_status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT'
1972 ,'ERROR');
1973 END IF;
1974 ELSE
1975 p_okl_ec_rec.qa_result_tbl(l_index).message := get_msg_success(ec_tbl(i).ec_name
1976 ,p_okl_ec_rec.source_name);
1977 p_okl_ec_rec.qa_result_tbl(l_index).status := get_lookup_meaning('OKL_EC_VALIDATION_RESULT','SUCCESS');
1978 END IF;
1979 END IF;
1980 ELSE
1981 fun_ret := 1;
1982 END IF;
1983 END IF; -- of Seeded_yn =N
1984 END LOOP;
1985
1986 END IF; -- of If ec_tbl.count > 0
1987
1988 IF fun_ret = 1 THEN
1989 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1990 okl_debug_pub.log_debug(fnd_log.level_procedure
1991 ,l_module
1992 ,'end debug OKLRECUB.pls.pls call validate');
1993 END IF;
1994 x_return_status := okl_api.g_ret_sts_success;
1995 x_eligible := true;
1996 ELSE
1997 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1998 okl_debug_pub.log_debug(fnd_log.level_procedure
1999 ,l_module
2000 ,'end debug OKLRECUB.pls.pls call validate');
2001 END IF;
2002 x_return_status := okl_api.g_ret_sts_success;
2003 x_eligible := false;
2004 END IF;
2005
2006 EXCEPTION
2007 WHEN okl_api.g_exception_error THEN
2008 IF get_ec%ISOPEN THEN
2009 CLOSE get_ec;
2010 END IF;
2011
2012 IF get_ec_values%ISOPEN THEN
2013 CLOSE get_ec_values;
2014 END IF;
2015 x_return_status := okl_api.g_ret_sts_error;
2016
2017 WHEN okl_api.g_exception_unexpected_error THEN
2018 IF get_ec%ISOPEN THEN
2019 CLOSE get_ec;
2020 END IF;
2021
2022 IF get_ec_values%ISOPEN THEN
2023 CLOSE get_ec_values;
2024 END IF;
2025 x_return_status := okl_api.g_ret_sts_unexp_error;
2026
2027 WHEN OTHERS THEN
2028
2029 IF get_ec%ISOPEN THEN
2030 CLOSE get_ec;
2031 END IF;
2032
2033 IF get_ec_values%ISOPEN THEN
2034 CLOSE get_ec_values;
2035 END IF;
2036 -- unexpected error
2037 OKL_API.set_message(p_app_name => g_app_name,
2038 p_msg_name => g_unexpected_error,
2039 p_token1 => g_sqlcode_token,
2040 p_token1_value => sqlcode,
2041 p_token2 => g_sqlerrm_token,
2042 p_token2_value => sqlerrm);
2043
2044 END validate;
2045
2046 -- the fields in p_okl_ec_rec, corressponding to the ecc applicable to the source type,
2047 -- should be filled in p_okl_ec_rec
2048
2049
2050 /**
2051 This procedure evaluates the adjustment categories attached to source object
2052 and returns the adjustment factor in out variable x_adjustment_factor.
2053 **/
2054
2055 PROCEDURE get_adjustment_factor(
2056 p_api_version In number
2057 ,P_init_msg_list In varchar2 Default Okl_api.G_false
2058 ,x_return_status Out Nocopy varchar2
2059 ,x_msg_count Out Nocopy number
2060 ,x_msg_data Out Nocopy varchar2
2061 ,p_okl_ac_rec In okl_ac_rec_type
2062 ,x_adjustment_factor Out Nocopy number
2063 ) IS
2064
2065 CURSOR get_ac(src_id IN number
2066 ,src_obj_code IN varchar2) IS
2067 SELECT fun.name evaluation_function
2068 ,ecl.criteria_id
2069 ,ecl.effective_from_date
2070 ,ecl.effective_to_date
2071 ,ecc.value_type_code
2072 ,ecc.seeded_yn
2073 ,ecc.crit_cat_name ac_name
2074 FROM okl_fe_criteria_set ech
2075 ,okl_fe_criteria ecl
2076 ,okl_fe_crit_cat_def_v ecc
2077 ,okl_data_src_fnctns_v fun
2078 WHERE ech.criteria_set_id = ecl.criteria_set_id
2079 AND ech.source_id = src_id
2080 AND ech.source_object_code = src_obj_code
2081 AND ecl.crit_cat_def_id = ecc.crit_cat_def_id
2082 AND ecc.ecc_ac_flag = 'AC'
2083 AND ecc.function_id = fun.id;
2084
2085 TYPE ac_tbl_type IS TABLE OF get_ac%ROWTYPE INDEX BY BINARY_INTEGER;
2086
2087 CURSOR get_ac_values(ec_ln_id IN number) IS
2088 SELECT operator_code
2089 ,crit_cat_value1
2090 ,crit_cat_value2
2091 ,adjustment_factor
2092 FROM okl_fe_criterion_values
2093 WHERE criteria_id = ec_ln_id;
2094 ac_tbl ac_tbl_type;
2095 ret boolean;
2096 fun_ret number;
2097 call_user boolean;
2098 i number;
2099 k number;
2100 l_operator_code varchar2(30);
2101 l_value1 varchar2(240);
2102 l_value2 varchar2(240);
2103 l_adjustment_factor number;
2104 l_index number;
2105 l_varchar_value_tbl okl_varchar2_table_type;
2106 l_num_value_tbl okl_number_table_type;
2107 l_adjustment_factors_tbl okl_number_table_type;
2108 l_ac_values_tbl okl_ec_values_tbl_type;
2109 l_adj_fctr number;
2110 l_api_version CONSTANT number := 1.0;
2111 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2112 l_msg_count number;
2113 l_msg_data varchar2(2000);
2114 l_init_msg_list varchar2(1) DEFAULT OKL_API.G_FALSE;
2115 l_function_name okl_data_src_fnctns_v.name%TYPE;
2116 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.get_adjustment_factor';
2117 l_debug_enabled varchar2(10);
2118 is_debug_procedure_on boolean;
2119 is_debug_statement_on boolean;
2120
2121 BEGIN
2122 l_debug_enabled := okl_debug_pub.check_log_enabled;
2123 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2124 ,fnd_log.level_procedure);
2125
2126 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2127 okl_debug_pub.log_debug(fnd_log.level_procedure
2128 ,l_module
2129 ,'begin debug OKLRECUB.pls.pls call get_adjustment_factor');
2130 END IF;
2131
2132 --dbms_output.put_line('start of new ');
2133 --log the incoming values
2134 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
2135 okl_debug_pub.log_debug(fnd_log.level_statement
2136 ,l_module
2137 ,' p_okl_ac_rec.SRC_ID = ' || p_okl_ac_rec.SRC_ID ||
2138 ' p_okl_ac_rec.SOURCE_NAME = ' || p_okl_ac_rec.SOURCE_NAME ||
2139 ' p_okl_ac_rec.TARGET_ID = ' || p_okl_ac_rec.TARGET_ID ||
2140 ' p_okl_ac_rec.SRC_TYPE = ' || p_okl_ac_rec.SRC_TYPE ||
2141 ' p_okl_ac_rec.TARGET_TYPE = ' || p_okl_ac_rec.TARGET_TYPE ||
2142 ' p_okl_ac_rec.TARGET_EFF_FROM = ' || p_okl_ac_rec.TARGET_EFF_FROM ||
2143 ' p_okl_ac_rec.TERM = ' || p_okl_ac_rec.TERM ||
2144 ' p_okl_ac_rec.TERRITORY = ' || p_okl_ac_rec.TERRITORY ||
2145 ' p_okl_ac_rec.DEAL_SIZE = ' || p_okl_ac_rec.DEAL_SIZE ||
2146 ' p_okl_ac_rec.CUSTOMER_CREDIT_CLASS = ' || p_okl_ac_rec.CUSTOMER_CREDIT_CLASS
2147 );
2148 END IF; -- end of NVL(l_debug_enabled,'N')='Y
2149
2150 ret := true;
2151 fun_ret := 1;
2152 l_adjustment_factor := 0;
2153
2154 --get all the adjustment categories defined on the source object of type= source_type
2155
2156 i := 1;
2157
2158 FOR ac_rec IN get_ac(p_okl_ac_rec.src_id, p_okl_ac_rec.src_type) LOOP
2159 ac_tbl(i).evaluation_function := ac_rec.evaluation_function;
2160 ac_tbl(i).criteria_id := ac_rec.criteria_id;
2161 ac_tbl(i).effective_from_date := ac_rec.effective_from_date;
2162 ac_tbl(i).effective_to_date := ac_rec.effective_to_date;
2163 ac_tbl(i).value_type_code := ac_rec.value_type_code;
2164 ac_tbl(i).seeded_yn := ac_rec.seeded_yn;
2165 ac_tbl(i).ac_name := ac_rec.ac_name;
2166 i := i + 1;
2167 END LOOP; -- For each ec defined on the source object do
2168
2169 IF ac_tbl.COUNT > 0 THEN
2170
2171 FOR i IN ac_tbl.FIRST..ac_tbl.LAST LOOP
2172
2173 -- dbms_output.put_line('ec_tbl(i).EC_NAME = ' || ec_tbl(i).EC_NAME);
2174
2175 IF ac_tbl(i).seeded_yn = 'Y' THEN
2176 IF (ac_tbl(i).ac_name = 'CUSTOMER CREDIT CLASS') THEN
2177 IF ((p_okl_ac_rec.target_eff_from BETWEEN ac_tbl(i).effective_from_date AND nvl(ac_tbl(i).effective_to_date
2178 ,to_date('01-01-9999'
2179 ,'dd-mm-yyyy')))
2180 AND (p_okl_ac_rec.customer_credit_class IS NOT NULL)) THEN
2181 k := 1;
2182 FOR ac_val_rec IN get_ac_values(ac_tbl(i).criteria_id) LOOP
2183 l_varchar_value_tbl(k) := ac_val_rec.crit_cat_value2;
2184 l_adjustment_factors_tbl(k) := ac_val_rec.adjustment_factor;
2185 k := k + 1;
2186 END LOOP;
2187
2188 --this validate function will return either index in l_adjustment_factors_tbl
2189 --if match is found else it returns 0
2190
2191 fun_ret := evaluate_customer_credit_class(l_varchar_value_tbl
2192 ,p_okl_ac_rec.customer_credit_class);
2193 IF fun_ret <> 0 THEN
2194 l_adjustment_factor := l_adjustment_factor + l_adjustment_factors_tbl(fun_ret);
2195 END IF;
2196 END IF;
2197 END IF;
2198 IF (ac_tbl(i).ac_name = 'DEAL SIZE') THEN
2199 IF ((p_okl_ac_rec.target_eff_from BETWEEN ac_tbl(i).effective_from_date AND nvl(ac_tbl(i).effective_to_date
2200 ,to_date('01-01-9999'
2201 ,'dd-mm-yyyy')))
2202 AND (p_okl_ac_rec.deal_size IS NOT NULL)) THEN
2203 OPEN get_ac_values(ac_tbl(i).criteria_id);
2204 FETCH get_ac_values INTO l_operator_code
2205 ,l_value1
2206 ,l_value2
2207 ,l_adj_fctr ;
2208 CLOSE get_ac_values;
2209
2210 --this function will return either 1 or 0
2211
2212 fun_ret := evaluate_deal_size(l_value1
2213 ,l_value2
2214 ,p_okl_ac_rec.deal_size);
2215 IF fun_ret <> 0 THEN
2216 l_adjustment_factor := l_adjustment_factor + l_adj_fctr;
2217 END IF;
2218
2219 --dbms_output.put_line('fun_ret deal size = '||fun_ret);
2220
2221 END IF;
2222 END IF;
2223 IF (ac_tbl(i).ac_name = 'TERM') THEN
2224 IF ((p_okl_ac_rec.target_eff_from BETWEEN ac_tbl(i).effective_from_date AND nvl(ac_tbl(i).effective_to_date
2225 ,to_date('01-01-9999'
2226 ,'dd-mm-yyyy')))
2227 AND (p_okl_ac_rec.term IS NOT NULL)) THEN
2228 k := 1;
2229 FOR ac_val_rec IN get_ac_values(ac_tbl(i).criteria_id) LOOP
2230 l_num_value_tbl(k) := fnd_number.canonical_to_number(ac_val_rec.crit_cat_value1);
2231 l_adjustment_factors_tbl(k) := ac_val_rec.adjustment_factor;
2232
2233 --dbms_output.put_line('l_num_value_tbl(k) = '||l_num_value_tbl(k));
2234
2235 k := k + 1;
2236 END LOOP;
2237
2238 --dbms_output.put_line('ac_tbl(i).match_criteria_code= '||ac_tbl(i).match_criteria_code);
2239 --this validate function will return either index in l_adjustment_factors_tbl
2240 --if match is found else it returns 0
2241
2242 fun_ret := evaluate_term(l_num_value_tbl, p_okl_ac_rec.term);
2243 IF fun_ret <> 0 THEN
2244 l_adjustment_factor := l_adjustment_factor + l_adjustment_factors_tbl(fun_ret);
2245 END IF;
2246 END IF;
2247 END IF;
2248 IF (ac_tbl(i).ac_name = 'TERRITORY') THEN
2249 IF ((p_okl_ac_rec.target_eff_from BETWEEN ac_tbl(i).effective_from_date AND nvl(ac_tbl(i).effective_to_date
2250 ,to_date('01-01-9999'
2251 ,'dd-mm-yyyy')))
2252 AND (p_okl_ac_rec.territory IS NOT NULL)) THEN
2253 k := 1;
2254 FOR ac_val_rec IN get_ac_values(ac_tbl(i).criteria_id) LOOP
2255 l_varchar_value_tbl(k) := ac_val_rec.crit_cat_value2;
2256 l_adjustment_factors_tbl(k) := ac_val_rec.adjustment_factor;
2257 k := k + 1;
2258 END LOOP;
2259
2260 --this validate function will return either 1 or 0 only
2261 --this validate function will return either index in l_adjustment_factors_tbl
2262 --if match is found else it returns 0
2263
2264 fun_ret := evaluate_territory(l_varchar_value_tbl
2265 ,p_okl_ac_rec.territory);
2266 IF fun_ret <> 0 THEN
2267 l_adjustment_factor := l_adjustment_factor + l_adjustment_factors_tbl(fun_ret);
2268 END IF;
2269 END IF;
2270 END IF;
2271 END IF; --of Seeded_yn= Y
2272 END LOOP;
2273
2274 --set G_ac_REC global variable to be used in user defined function to access values of p_okl_ac_rec
2275
2276 g_ac_rec := p_okl_ac_rec;
2277
2278 --call evaluation function attached to user defined criteri
2279
2280 FOR i IN ac_tbl.FIRST..ac_tbl.LAST LOOP
2281 IF ac_tbl(i).seeded_yn = 'N' THEN
2282 IF (p_okl_ac_rec.target_eff_from BETWEEN ac_tbl(i).effective_from_date AND nvl(ac_tbl(i).effective_to_date
2283 ,to_date('01-01-9999'
2284 ,'dd-mm-yyyy'))) THEN
2285 k := 1;
2286 FOR ac_val_rec IN get_ac_values(ac_tbl(i).criteria_id) LOOP
2287 l_ac_values_tbl(k).operator_code := ac_val_rec.operator_code;
2288 l_ac_values_tbl(k).value1 := ac_val_rec.crit_cat_value1;
2289 l_ac_values_tbl(k).value2 := ac_val_rec.crit_cat_value2;
2290 l_adjustment_factors_tbl(k) := ac_val_rec.adjustment_factor;
2291 k := k + 1;
2292 END LOOP;
2293 g_ac_values_tbl := l_ac_values_tbl;
2294
2295 --call the execute function API
2296 l_function_name := ac_tbl(i).EVALUATION_FUNCTION;
2297 okl_execute_formula_pvt.execute_eligibility_criteria ( p_api_version => l_api_version,
2298 p_init_msg_list => l_init_msg_list,
2299 x_return_status => l_return_status,
2300 x_msg_count => l_msg_count,
2301 x_msg_data => l_msg_data,
2302 p_function_name => l_function_name,
2303 x_value => fun_ret
2304 );
2305 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
2306 okl_debug_pub.log_debug(fnd_log.level_statement
2307 ,l_module
2308 ,'okl_execute_formula_pvt.execute_eligibility_criteria returned with status ' ||
2309 l_return_status ||
2310 ' x_msg_data ' ||
2311 l_msg_data);
2312 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
2313
2314 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2315 RAISE okl_api.g_exception_unexpected_error;
2316 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2317 RAISE okl_api.g_exception_error;
2318 END IF;
2319
2320 IF fun_ret <> 0 AND l_adjustment_factors_tbl.EXISTS(fun_ret) THEN
2321 l_adjustment_factor := l_adjustment_factor + l_adjustment_factors_tbl(fun_ret);
2322 END IF;
2323 END IF;
2324 END IF; -- of Seeded_yn =N
2325 END LOOP;
2326
2327 END IF; -- of If ac_tbl.count > 0
2328
2329 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2330 okl_debug_pub.log_debug(fnd_log.level_procedure
2331 ,l_module
2332 ,'end debug OKLRECUB.pls.pls call get_adjustment_factor');
2333 END IF;
2334 x_adjustment_factor := l_adjustment_factor;
2335 x_return_status := okl_api.g_ret_sts_success;
2336
2337 EXCEPTION
2338 WHEN okl_api.g_exception_error THEN
2339 IF get_ac%ISOPEN THEN
2340 CLOSE get_ac;
2341 END IF;
2342
2343 IF get_ac_values%ISOPEN THEN
2344 CLOSE get_ac_values;
2345 END IF;
2346 x_return_status := okl_api.g_ret_sts_error;
2347
2348 WHEN okl_api.g_exception_unexpected_error THEN
2349 IF get_ac%ISOPEN THEN
2350 CLOSE get_ac;
2351 END IF;
2352
2353 IF get_ac_values%ISOPEN THEN
2354 CLOSE get_ac_values;
2355 END IF;
2356 x_return_status := okl_api.g_ret_sts_unexp_error;
2357
2358 WHEN OTHERS THEN
2359
2360 IF get_ac%ISOPEN THEN
2361 CLOSE get_ac;
2362 END IF;
2363
2364 IF get_ac_values%ISOPEN THEN
2365 CLOSE get_ac_values;
2366 END IF;
2367 -- unexpected error
2368 OKL_API.set_message(p_app_name => g_app_name,
2369 p_msg_name => g_unexpected_error,
2370 p_token1 => g_sqlcode_token,
2371 p_token1_value => sqlcode,
2372 p_token2 => g_sqlerrm_token,
2373 p_token2_value => sqlerrm);
2374 END get_adjustment_factor;
2375 /**
2376 This function checks the existenace of at lease one scenario wherein
2377 common eligibility criteria defined on both the sources can be passed
2378 successfully.If such scenario exists function returns true else false.
2379 If there are no common eligibility criteria no comparison is done and
2380 function returns true.
2381 **/
2382
2383 FUNCTION compare_eligibility_criteria(p_source_id1 IN number
2384 ,p_source_type1 IN varchar2
2385 ,p_source_id2 IN number
2386 ,p_source_type2 IN varchar2) RETURN boolean IS
2387
2388 CURSOR get_ec(src_id IN number
2389 ,src_obj_code IN varchar2) IS
2390 SELECT a.validation_code
2391 ,b.criteria_id
2392 ,b.match_criteria_code
2393 ,b.effective_from_date
2394 ,b.effective_to_date
2395 ,c.value_type_code
2396 ,c.data_type_code
2397 ,c.crit_cat_name ec_name
2398 ,c.crit_cat_def_id
2399 FROM okl_fe_criteria_set a
2400 ,okl_fe_criteria b
2401 ,okl_fe_crit_cat_def_v c
2402 WHERE a.criteria_set_id = b.criteria_set_id AND a.source_id = src_id
2403 AND a.source_object_code = src_obj_code
2404 AND b.crit_cat_def_id = c.crit_cat_def_id
2405 AND c.ecc_ac_flag = 'ECC';
2406
2407 TYPE ec_tbl_type IS TABLE OF get_ec%ROWTYPE INDEX BY BINARY_INTEGER;
2408
2409 CURSOR get_ec_values(ec_ln_id IN number) IS
2410 SELECT operator_code
2411 ,crit_cat_value1
2412 ,crit_cat_value2
2413 FROM okl_fe_criterion_values
2414 WHERE criteria_id = ec_ln_id;
2415 ec1_tbl ec_tbl_type;
2416 ec2_tbl ec_tbl_type;
2417 l_ec1_value_tbl okl_varchar2_table_type;
2418 l_ec2_value_tbl okl_varchar2_table_type;
2419 l_ec1_operator_code varchar2(30);
2420 l_ec2_operator_code varchar2(30);
2421 l_ec1_value1 varchar2(240);
2422 l_ec2_value1 varchar2(240);
2423 l_ec1_value2 varchar2(240);
2424 l_ec2_value2 varchar2(240);
2425 l_ec1_numval1 number;
2426 l_ec2_numval1 number;
2427 l_ec1_numval2 number;
2428 l_ec2_numval2 number;
2429 l_ec1_dateval1 date;
2430 l_ec2_dateval1 date;
2431 l_ec1_dateval2 date;
2432 l_ec2_dateval2 date;
2433 l_match_found boolean;
2434 i number;
2435 j number;
2436 k number;
2437 l number;
2438 m number;
2439 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_evaluate_pvt.compare_eligibility_criteria';
2440 l_debug_enabled varchar2(10);
2441 is_debug_procedure_on boolean;
2442 is_debug_statement_on boolean;
2443
2444 BEGIN
2445 l_debug_enabled := okl_debug_pub.check_log_enabled;
2446 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2447 ,fnd_log.level_procedure);
2448
2449 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2450 okl_debug_pub.log_debug(fnd_log.level_procedure
2451 ,l_module
2452 ,'begin debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2453 END IF;
2454
2455 -- check for logging on STATEMENT level
2456
2457 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2458 ,fnd_log.level_statement);
2459 i := 1;
2460
2461 FOR ec_rec IN get_ec(p_source_id1, p_source_type1) LOOP
2462 ec1_tbl(i).match_criteria_code := ec_rec.match_criteria_code;
2463 ec1_tbl(i).validation_code := ec_rec.validation_code;
2464 ec1_tbl(i).criteria_id := ec_rec.criteria_id;
2465 ec1_tbl(i).effective_from_date := ec_rec.effective_from_date;
2466 ec1_tbl(i).effective_to_date := ec_rec.effective_to_date;
2467 ec1_tbl(i).value_type_code := ec_rec.value_type_code;
2468 ec1_tbl(i).data_type_code := ec_rec.data_type_code;
2469 ec1_tbl(i).ec_name := ec_rec.ec_name;
2470 ec1_tbl(i).crit_cat_def_id := ec_rec.crit_cat_def_id;
2471 i := i + 1;
2472 END LOOP;
2473 i := 1;
2474
2475 FOR ec_rec IN get_ec(p_source_id2, p_source_type2) LOOP
2476 ec2_tbl(i).match_criteria_code := ec_rec.match_criteria_code;
2477 ec2_tbl(i).validation_code := ec_rec.validation_code;
2478 ec2_tbl(i).criteria_id := ec_rec.criteria_id;
2479 ec2_tbl(i).effective_from_date := ec_rec.effective_from_date;
2480 ec2_tbl(i).effective_to_date := ec_rec.effective_to_date;
2481 ec2_tbl(i).value_type_code := ec_rec.value_type_code;
2482 ec2_tbl(i).data_type_code := ec_rec.data_type_code;
2483 ec2_tbl(i).ec_name := ec_rec.ec_name;
2484 ec2_tbl(i).crit_cat_def_id := ec_rec.crit_cat_def_id;
2485 i := i + 1;
2486 END LOOP;
2487
2488 --dbms_output.put_line('ec1_tbl.count= '||ec1_tbl.count);
2489 --dbms_output.put_line('ec2_tbl.count = '||ec2_tbl.count);
2490
2491 IF ec1_tbl.COUNT > 0 AND ec2_tbl.COUNT > 0 THEN
2492 IF ec1_tbl(1).validation_code = 'ERROR' AND ec2_tbl(1).validation_code = 'ERROR' THEN
2493
2494 FOR i IN ec1_tbl.FIRST..ec1_tbl.LAST LOOP
2495 FOR j IN ec2_tbl.FIRST..ec2_tbl.LAST LOOP
2496
2497 --dbms_output.put_line('ec1_tbl(i).crit_Cat_def_id = '||ec1_tbl(i).crit_Cat_def_id );
2498 --dbms_output.put_line(' ec2_tbl(j).crit_Cat_def_id = '||ec2_tbl(j).crit_Cat_def_id);
2499
2500 IF ec1_tbl(i).crit_cat_def_id = ec2_tbl(j).crit_cat_def_id THEN
2501 IF ec1_tbl(i).effective_from_date BETWEEN ec2_tbl(j).effective_from_date AND nvl(ec2_tbl(j).effective_to_date
2502 ,to_date('01-01-9999'
2503 ,'dd-mm-yyyy'))
2504 OR ec2_tbl(j).effective_from_date BETWEEN ec1_tbl(i).effective_from_date AND nvl(ec1_tbl(i).effective_to_date
2505 ,to_date('01-01-9999'
2506 ,'dd-mm-yyyy')) THEN
2507
2508 --dbms_output.put_line('Common EC Found ec1_tbl(i).crit_Cat_def_id = '||ec1_tbl(i).crit_Cat_def_id );
2509
2510 IF ec1_tbl(i).value_type_code = 'RANGE' THEN
2511
2512 --dbms_output.put_line('value type= range ');
2513
2514 OPEN get_ec_values(ec1_tbl(i).criteria_id);
2515 FETCH get_ec_values INTO l_ec1_operator_code
2516 ,l_ec1_value1
2517 ,l_ec1_value2 ;
2518 CLOSE get_ec_values;
2519 OPEN get_ec_values(ec2_tbl(j).criteria_id);
2520 FETCH get_ec_values INTO l_ec2_operator_code
2521 ,l_ec2_value1
2522 ,l_ec2_value2 ;
2523 CLOSE get_ec_values;
2524 IF ec1_tbl(i).data_type_code = 'NUMBER' THEN
2525
2526 --dbms_output.put_line('datatype = number ');
2527
2528 l_ec1_numval1 := fnd_number.canonical_to_number(l_ec1_value1);
2529 l_ec1_numval2 := fnd_number.canonical_to_number(l_ec1_value2);
2530 l_ec2_numval1 := fnd_number.canonical_to_number(l_ec2_value1);
2531 l_ec2_numval2 := fnd_number.canonical_to_number(l_ec2_value2);
2532
2533 --dbms_output.put_line(' l_ec1_numval1= '||l_ec1_numval1);
2534 --dbms_output.put_line(' l_ec1_numval2= '||l_ec1_numval2);
2535 --dbms_output.put_line(' l_ec2_numval1= '||l_ec2_numval1);
2536 --dbms_output.put_line(' l_ec2_numval2= '||l_ec2_numval2);
2537
2538 IF NOT (l_ec1_numval1 BETWEEN l_ec2_numval1 AND l_ec2_numval2
2539 OR l_ec2_numval1 BETWEEN l_ec1_numval1 AND l_ec1_numval2) THEN
2540 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2541 okl_debug_pub.log_debug(fnd_log.level_procedure
2542 ,l_module
2543 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2544 END IF;
2545 RETURN false;
2546 END IF;
2547 ELSIF ec1_tbl(i).data_type_code = 'DATE' THEN
2548 l_ec1_dateval1 := fnd_date.canonical_to_date(l_ec1_value1);
2549 l_ec1_dateval2 := fnd_date.canonical_to_date(l_ec1_value2);
2550 l_ec2_dateval1 := fnd_date.canonical_to_date(l_ec2_value1);
2551 l_ec2_dateval2 := fnd_date.canonical_to_date(l_ec2_value2);
2552 IF NOT (l_ec1_dateval1 BETWEEN l_ec2_dateval1 AND l_ec2_dateval2
2553 OR l_ec2_dateval1 BETWEEN l_ec1_dateval1 AND l_ec1_dateval2) THEN
2554 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2555 okl_debug_pub.log_debug(fnd_log.level_procedure
2556 ,l_module
2557 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2558 END IF;
2559 RETURN false;
2560 END IF;
2561 END IF;
2562 ELSIF ec1_tbl(i).value_type_code = 'MULTIPLE' THEN
2563 k := 1;
2564 FOR ec_val_rec IN get_ec_values(ec1_tbl(i).criteria_id) LOOP
2565 l_ec1_value_tbl(k) := ec_val_rec.crit_cat_value2;
2566 k := k + 1;
2567 END LOOP;
2568 k := 1;
2569 FOR ec_val_rec IN get_ec_values(ec2_tbl(j).criteria_id) LOOP
2570 l_ec2_value_tbl(k) := ec_val_rec.crit_cat_value2;
2571 k := k + 1;
2572 END LOOP;
2573 IF ec1_tbl(i).match_criteria_code = 'INCLUDE' AND ec2_tbl(j).match_criteria_code = 'INCLUDE' THEN
2574 l_match_found := false;
2575
2576 <<loop1>>
2577 FOR l IN l_ec1_value_tbl.FIRST..l_ec1_value_tbl.LAST LOOP
2578 FOR m IN l_ec2_value_tbl.FIRST..l_ec2_value_tbl.LAST LOOP
2579 IF l_ec1_value_tbl(l) = l_ec2_value_tbl(m) THEN
2580 l_match_found := true;
2581 EXIT loop1;
2582 END IF;
2583 END LOOP;
2584 END LOOP;
2585 IF NOT l_match_found THEN
2586 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2587 okl_debug_pub.log_debug(fnd_log.level_procedure
2588 ,l_module
2589 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2590 END IF;
2591 RETURN false;
2592 END IF;
2593 ELSIF ec1_tbl(i).match_criteria_code = 'INCLUDE'
2594 AND ec2_tbl(j).match_criteria_code = 'EXCLUDE' THEN
2595
2596 <<loop2>>
2597 FOR l IN l_ec1_value_tbl.FIRST..l_ec1_value_tbl.LAST LOOP
2598 l_match_found := false;
2599 FOR m IN l_ec2_value_tbl.FIRST..l_ec2_value_tbl.LAST LOOP
2600 IF l_ec1_value_tbl(l) = l_ec2_value_tbl(m) THEN
2601 l_match_found := true;
2602 END IF;
2603 END LOOP;
2604 IF NOT l_match_found THEN
2605 EXIT loop2;
2606 END IF;
2607 END LOOP;
2608 IF l_match_found THEN
2609 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2610 okl_debug_pub.log_debug(fnd_log.level_procedure
2611 ,l_module
2612 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2613 END IF;
2614 RETURN false;
2615 END IF;
2616 ELSIF ec1_tbl(i).match_criteria_code = 'EXCLUDE'
2617 AND ec2_tbl(j).match_criteria_code = 'INCLUDE' THEN
2618
2619 <<loop3>>
2620 FOR l IN l_ec2_value_tbl.FIRST..l_ec2_value_tbl.LAST LOOP
2621 l_match_found := false;
2622 FOR m IN l_ec1_value_tbl.FIRST..l_ec1_value_tbl.LAST LOOP
2623 IF l_ec2_value_tbl(l) = l_ec1_value_tbl(m) THEN
2624 l_match_found := true;
2625 END IF;
2626 END LOOP;
2627 IF NOT l_match_found THEN
2628 EXIT loop3;
2629 END IF;
2630 END LOOP;
2631 IF l_match_found THEN
2632 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2633 okl_debug_pub.log_debug(fnd_log.level_procedure
2634 ,l_module
2635 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2636 END IF;
2637 RETURN false;
2638 END IF;
2639 END IF;
2640 ELSE -- value_type= 'SINGLE'
2641 OPEN get_ec_values(ec1_tbl(i).criteria_id);
2642 FETCH get_ec_values INTO l_ec1_operator_code
2643 ,l_ec1_value1
2644 ,l_ec1_value2 ;
2645 CLOSE get_ec_values;
2646 OPEN get_ec_values(ec2_tbl(j).criteria_id);
2647 FETCH get_ec_values INTO l_ec2_operator_code
2648 ,l_ec2_value1
2649 ,l_ec2_value2 ;
2650 CLOSE get_ec_values;
2651 IF ec1_tbl(i).data_type_code = 'NUMBER' THEN
2652 l_ec1_numval1 := fnd_number.canonical_to_number(l_ec1_value1);
2653 l_ec2_numval1 := fnd_number.canonical_to_number(l_ec2_value1);
2654 IF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'EQ' THEN
2655 IF l_ec1_numval1 <> l_ec2_numval1 THEN
2656 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2657 okl_debug_pub.log_debug(fnd_log.level_procedure
2658 ,l_module
2659 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2660 END IF;
2661 RETURN false;
2662 END IF;
2663 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'NE' THEN
2664 IF l_ec1_numval1 >= l_ec2_numval1 THEN
2665 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2666 okl_debug_pub.log_debug(fnd_log.level_procedure
2667 ,l_module
2668 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2669 END IF;
2670 RETURN false;
2671 END IF;
2672 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'LT' THEN
2673 IF l_ec1_numval1 >= l_ec2_numval1 THEN
2674 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2675 okl_debug_pub.log_debug(fnd_log.level_procedure
2676 ,l_module
2677 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2678 END IF;
2679 RETURN false;
2680 END IF;
2681 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'GT' THEN
2682 IF l_ec1_numval1 <= l_ec2_numval1 THEN
2683 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2684 okl_debug_pub.log_debug(fnd_log.level_procedure
2685 ,l_module
2686 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2687 END IF;
2688 RETURN false;
2689 END IF;
2690 ELSIF l_ec1_operator_code = 'NE' AND l_ec2_operator_code = 'EQ' THEN
2691 IF l_ec1_numval1 = l_ec2_numval1 THEN
2692 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2693 okl_debug_pub.log_debug(fnd_log.level_procedure
2694 ,l_module
2695 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2696 END IF;
2697 RETURN false;
2698 END IF;
2699 ELSIF l_ec1_operator_code = 'LT' AND l_ec2_operator_code = 'EQ' THEN
2700 IF l_ec1_numval1 <= l_ec2_numval1 THEN
2701 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2702 okl_debug_pub.log_debug(fnd_log.level_procedure
2703 ,l_module
2704 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2705 END IF;
2706 RETURN false;
2707 END IF;
2708 ELSIF l_ec1_operator_code = 'LT' AND l_ec2_operator_code = 'GT' THEN
2709 IF l_ec1_numval1 - l_ec2_numval1 <= 1 THEN
2710 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2711 okl_debug_pub.log_debug(fnd_log.level_procedure
2712 ,l_module
2713 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2714 END IF;
2715 RETURN false;
2716 END IF;
2717 ELSIF l_ec1_operator_code = 'GT' AND l_ec2_operator_code = 'EQ' THEN
2718 IF l_ec1_numval1 >= l_ec2_numval1 THEN
2719 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2720 okl_debug_pub.log_debug(fnd_log.level_procedure
2721 ,l_module
2722 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2723 END IF;
2724 RETURN false;
2725 END IF;
2726 ELSIF l_ec1_operator_code = 'GT' AND l_ec2_operator_code = 'LT' THEN
2727 IF l_ec2_numval1 - l_ec1_numval1 <= 1 THEN
2728 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2729 okl_debug_pub.log_debug(fnd_log.level_procedure
2730 ,l_module
2731 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2732 END IF;
2733 RETURN false;
2734 END IF;
2735 END IF;
2736 ELSIF ec1_tbl(i).data_type_code = 'DATE' THEN
2737 l_ec1_dateval1 := fnd_date.canonical_to_date(l_ec1_value1);
2738 l_ec2_dateval1 := fnd_date.canonical_to_date(l_ec2_value1);
2739 IF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'EQ' THEN
2740 IF l_ec1_dateval1 <> l_ec2_dateval1 THEN
2741 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2742 okl_debug_pub.log_debug(fnd_log.level_procedure
2743 ,l_module
2744 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2745 END IF;
2746 RETURN false;
2747 END IF;
2748 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'NE' THEN
2749 IF l_ec1_dateval1 >= l_ec2_dateval1 THEN
2750 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2751 okl_debug_pub.log_debug(fnd_log.level_procedure
2752 ,l_module
2753 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2754 END IF;
2755 RETURN false;
2756 END IF;
2757 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'LT' THEN
2758 IF l_ec1_dateval1 >= l_ec2_dateval1 THEN
2759 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2760 okl_debug_pub.log_debug(fnd_log.level_procedure
2761 ,l_module
2762 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2763 END IF;
2764 RETURN false;
2765 END IF;
2766 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'GT' THEN
2767 IF l_ec1_dateval1 <= l_ec2_dateval1 THEN
2768 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2769 okl_debug_pub.log_debug(fnd_log.level_procedure
2770 ,l_module
2771 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2772 END IF;
2773 RETURN false;
2774 END IF;
2775 ELSIF l_ec1_operator_code = 'NE' AND l_ec2_operator_code = 'EQ' THEN
2776 IF l_ec1_dateval1 = l_ec2_dateval1 THEN
2777 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2778 okl_debug_pub.log_debug(fnd_log.level_procedure
2779 ,l_module
2780 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2781 END IF;
2782 RETURN false;
2783 END IF;
2784 ELSIF l_ec1_operator_code = 'LT' AND l_ec2_operator_code = 'EQ' THEN
2785 IF l_ec1_dateval1 <= l_ec2_dateval1 THEN
2786 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2787 okl_debug_pub.log_debug(fnd_log.level_procedure
2788 ,l_module
2789 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2790 END IF;
2791 RETURN false;
2792 END IF;
2793 ELSIF l_ec1_operator_code = 'LT' AND l_ec2_operator_code = 'GT' THEN
2794 IF l_ec1_dateval1 - l_ec2_dateval1 <= 1 THEN
2795 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2796 okl_debug_pub.log_debug(fnd_log.level_procedure
2797 ,l_module
2798 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2799 END IF;
2800 RETURN false;
2801 END IF;
2802 ELSIF l_ec1_operator_code = 'GT' AND l_ec2_operator_code = 'EQ' THEN
2803 IF l_ec1_dateval1 >= l_ec2_dateval1 THEN
2804 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2805 okl_debug_pub.log_debug(fnd_log.level_procedure
2806 ,l_module
2807 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2808 END IF;
2809 RETURN false;
2810 END IF;
2811 ELSIF l_ec1_operator_code = 'GT' AND l_ec2_operator_code = 'LT' THEN
2812 IF l_ec2_dateval1 - l_ec1_dateval1 <= 1 THEN
2813 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2814 okl_debug_pub.log_debug(fnd_log.level_procedure
2815 ,l_module
2816 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2817 END IF;
2818 RETURN false;
2819 END IF;
2820 END IF;
2821 ELSIF ec1_tbl(i).data_type_code = 'VARCHAR2' THEN
2822 IF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'EQ' THEN
2823 IF l_ec1_value1 <> l_ec2_value1 THEN
2824 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2825 okl_debug_pub.log_debug(fnd_log.level_procedure
2826 ,l_module
2827 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2828 END IF;
2829 RETURN false;
2830 END IF;
2831 ELSIF l_ec1_operator_code = 'EQ' AND l_ec2_operator_code = 'NE' THEN
2832 IF l_ec1_value1 >= l_ec2_value1 THEN
2833 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2834 okl_debug_pub.log_debug(fnd_log.level_procedure
2835 ,l_module
2836 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2837 END IF;
2838 RETURN false;
2839 END IF;
2840 ELSIF l_ec1_operator_code = 'NE' AND l_ec2_operator_code = 'EQ' THEN
2841 IF l_ec1_value1 = l_ec2_value1 THEN
2842 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2843 okl_debug_pub.log_debug(fnd_log.level_procedure
2844 ,l_module
2845 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2846 END IF;
2847 RETURN false;
2848 END IF;
2849 END IF;
2850 END IF; --of data type check
2851 END IF; --of value type check
2852 END IF; --of date overlap check
2853 END IF; --of same ec check
2854 END LOOP;
2855 END LOOP;
2856
2857 END IF;
2858 END IF;
2859
2860 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2861 okl_debug_pub.log_debug(fnd_log.level_procedure
2862 ,l_module
2863 ,'end debug OKLRECUB.pls.pls call compare_eligibility_criteria');
2864 END IF;
2865 RETURN true;
2866 END compare_eligibility_criteria;
2867
2868 END okl_ec_evaluate_pvt;