[Home] [Help]
PACKAGE BODY: APPS.PV_RULE_EVALUATION_PUB
Source
1 package body PV_RULE_EVALUATION_PUB as
2 /* $Header: pvxpprgb.pls 120.5 2006/05/16 17:44:46 dhii noship $*/
3
4 -- --------------------------------------------------------------
5 -- Used for inserting output messages to the message table.
6 -- --------------------------------------------------------------
7 PROCEDURE Debug(
8 p_msg_string IN VARCHAR2,
9 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT
10 );
11
12 PROCEDURE Set_Message(
13 p_msg_level IN NUMBER,
14 p_msg_name IN VARCHAR2,
15 p_token1 IN VARCHAR2,
16 p_token1_value IN VARCHAR2,
17 p_token2 IN VARCHAR2 := NULL,
18 p_token2_value IN VARCHAR2 := NULL,
19 p_token3 IN VARCHAR2 := NULL,
20 p_token3_value IN VARCHAR2 := NULL
21 );
22
23 function in_list ( p_string in varchar2 ) return jtf_varchar2_table_32767;
24
25
26 -- #############################################################################
27 -- Partner Evaluation Outcome
28 -- #############################################################################
29
30
31 PROCEDURE partner_evaluation_outcome(
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
34 p_commit IN VARCHAR2 := FND_API.g_false,
35 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
36 p_partner_id IN NUMBER,
37 p_rule_id_tbl IN JTF_NUMBER_TABLE,
38 x_attr_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
39 x_attr_evaluation_result_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
40 x_partner_attr_value_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
41 x_evaluation_criteria_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
42 x_rule_pass_flag OUT NOCOPY VARCHAR2,
43 x_delimiter OUT NOCOPY VARCHAR2,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2
47 )
48
49 IS
50
51 TYPE c_attr_mean IS REF CURSOR;
52
53 l_rule_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
54 l_attr_val_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
55 l_attr_to_val JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
56 l_operator_tbl JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
57 l_currency_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
58 l_criterion_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
59 l_cri_condition_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
60 l_attribute_value_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
61
62
63 l_input_filter pv_check_match_pub.t_input_filter;
64 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
65
66 lc_attr_cursor c_attr_mean;
67 l_lov_string VARCHAR2(4000) := null;
68
69 l_rule_id NUMBER;
70 l_attribute_id NUMBER;
71 l_currency VARCHAR2(30);
72 l_operator VARCHAR2(30);
73 l_cri_id NUMBER;
74 l_att_val VARCHAR2(4000);
75 l_att_to_val VARCHAR2(1000);
76 l_att_val_mean VARCHAR2(1000);
77 l_opr_meaning VARCHAR2(1000);
78 l_attribute_name VARCHAR2(1000);
79 l_attr_in_string VARCHAR2(4000);
80
81 l_prev_rule_id NUMBER := NULL;
82 l_prev_attr_id NUMBER := NULL;
83 l_prev_criteria_id NUMBER := NULL;
84 l_prev_currency VARCHAR2(1000);
85 l_prev_operator VARCHAR2(1000);
86 l_prev_att_val VARCHAR2(4000) := NULL;
87 l_prev_att_to_val VARCHAR2(1000);
88 l_prev_att_name VARCHAR2(1000);
89
90 l_attr_value VARCHAR2(4100) := NULL;
91 l_attr_val_meaning VARCHAR2(1000) := NULL;
92 l_attr_to_val_mean VARCHAR2(1000) := NULL;
93 l_attrib_to_val VARCHAR2(1000) := NULL;
94 l_att_code VARCHAR2(1000) := NULL;
95 l_concat_attr_mean VARCHAR2(1000) := NULL;
96
97
98 l_attribute_value VARCHAR2(4100) := NULL;
99
100 l_rule_pass_flag BOOLEAN := FALSE;
101 l_total_rows NUMBER;
102 l_criteria_count NUMBER := 0;
103 l_non_cri_count NUMBER := 0;
104 l_last_cri_cnt NUMBER := 0;
105 l_total_criteria NUMBER := 0;
106 l_temp_variable varchar2(4000);
107 j NUMBER;
108 l_cnt NUMBER := 0;
109 l_count NUMBER := 0;
110
111
112 l_result BOOLEAN;
113
114
115 -- =============================================================================================
116 -- This query evaluates the attribute Id for the given Rule ID Set
117 -- Rules are hierarchical.
118 -- e.g.
119 -- Rule1(root) --> Rule2(Level 1 Child) --> Rule3(Level 2 Child)
120 -- -----------------------------------------------------------------------
121 -- Rule1 (attribute1 > 30, attribute2 > 40)
122 -- Rule2 (attribute3 > 50, attribute4 > 60)
123 -- Rule3 (attribute1 > 45, attribute4 > 50)
124
125 -- Combined Attribute List (which will be used as the basis of evaluation:
126 -- attribute1 > 45 (R3 overwrites R1)
127 -- attribute2 > 40
128 -- attribute3 > 50
129 -- attribute4 > 60
130
131 -- Query Description :
132
133
134 -- Inline View Z :
135
136 -- "SELECT a.process_rule_id, b.attribute_id, x.rank, x.process_rule_id rule_id
137 -- FROM pv_process_rules_vl a, pv_enty_select_criteria b,
138 -- (SELECT rownum rank, column_value process_rule_id
139 -- FROM (SELECT column_value FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE))) ) x
140 -- WHERE a.process_rule_id = x.process_rule_id
141 -- AND a.process_rule_id = b.process_rule_id "
142
143 -- " SELECT column_value FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE "
144 -- In the above select statement CAST ensures the order of Rule ID set from the query
145 -- remains the same as that of INPUT RULE ID TABLE.
146
147 --
148 -- Since rules are hierarchical rank is assigned to them starting with the Parent (Parent
149 -- Rule ID gets the least priority )
150
151 -- Rownum evaluates the rank of the attribute ID based on how many times it is repeated in the rule.
152 -- Result of the above inline view Z would be
153 -- -----------------------------------------------------------------------------------------------
154 -- Rule ID Attribute ID Rank
155 -- ================================================================================================
156 -- 1 attribute1 1
157 -- 1 attribute2 1
158 -- 2 attribute3 2
159 -- 2 attribute4 2
160 -- 3 attribute1 3
161 -- 3 attribute4 3
162 -- ------------------------------------------------------------------------------------------------
163 -- When we take the MAX(rank) grouped by attribute ID
164 -- for the above set the combined attribute list is achieved
165
166 -- Final combined attribute list from INLINE VIEW XY would be
167 -- -------------------------------------------------------------------------------------------------
168 -- Rule ID Attribute ID Rank
169 -- =================================================================================================
170 -- 1 attribute2 1
171 -- 2 attribute3 2
172 -- 3 attribute1 3
173 -- 3 attribute4 3
174 -- --------------------------------------------------------------------------------------------------
175
176 -- ====================================================================================================
177
178
179 cursor lc_get_rule_details
180 is
181 SELECT /*+ leading(x) */ xy.attribute_id Attribute_ID,
182 xy.rule_id Rule_ID,
183 rule.currency_code Currency_Code,
184 criterion.operator Operator,
185 criterion.selection_criteria_id Criteria_ID,
186 AttVal.attribute_value Attr_Value,
187 AttVal.attribute_to_value Attr_To_Value
188 FROM pv_process_rules_vl Rule,
189 pv_enty_select_criteria Criterion,
190 pv_selected_attr_values AttVal,
191 ( SELECT attribute_id,TO_NUMBER(LTRIM(substr(rule_rank, 1, 10), 0)) rank,TO_NUMBER(LTRIM(substr(rule_rank, 11, LENGTH(rule_rank)), 0)) rule_id
192 FROM (
193 SELECT z.attribute_id, MAX(LPAD(z.rank, 10, 0) || LPAD(z.process_rule_id, 100, 0)) rule_rank
194 FROM (
195 SELECT a.process_rule_id, b.attribute_id, x.rank, x.process_rule_id rule_id
196 FROM pv_process_rules_vl a,
197 pv_enty_select_criteria b,
198 (SELECT rownum rank, column_value process_rule_id
199 FROM (SELECT column_value
200 FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE))) ) x
201 WHERE a.process_rule_id = x.process_rule_id
202 AND a.process_rule_id = b.process_rule_id ) z
203 group by z.attribute_id
204 )) xy
205 WHERE Rule.process_rule_id = Criterion.process_rule_id
206 AND Criterion.selection_criteria_id = Attval.selection_criteria_id
207 AND Criterion.selection_type_code = 'PARTNER_SELECTION'
208 AND Rule.process_rule_id = xy.rule_id
209 AND criterion.attribute_id = xy.attribute_id
210 AND criterion.process_rule_id = xy.rule_id
211 order by xy.rank;
212
213 CURSOR lc_attr_mean_cursor(pc_attribute_id NUMBER) IS
214 SELECT lov_string
215 FROM pv_entity_attrs
216 WHERE lov_string IS NOT NULL
217 AND entity = 'PARTNER'
218 AND enabled_flag = 'Y'
219 AND attribute_id = pc_attribute_id;
220
221 l_api_name CONSTANT VARCHAR2(30) := 'partner_evaluation_outcome';
222 l_api_version_number CONSTANT NUMBER := 1.0;
223
224 begin
225
226 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 Debug(l_api_name,FND_LOG.LEVEL_PROCEDURE);
228 END IF;
229
230 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
231 p_api_version,
232 l_api_name,
233 G_PKG_NAME) THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235
236 END IF;
237
238 -- Initialize message list if p_init_msg_list is set to TRUE.
239 IF FND_API.to_Boolean( p_init_msg_list ) THEN
240 fnd_msg_pub.initialize;
241 END IF;
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS ;
244 x_delimiter := '+++';
245
246 IF p_rule_id_tbl.count = 0 THEN
247 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
248 p_msg_name => 'PV_DEBUG_MESSAGE',
249 p_token1 => 'TEXT',
250 p_token1_value => 'Rule ID is not passed in',
251 p_token2 => NULL,
252 p_token2_value => NULL);
253
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256
257 x_attr_id_tbl := JTF_NUMBER_TABLE();
258 x_attr_evaluation_result_tbl := JTF_VARCHAR2_TABLE_100();
259 x_partner_attr_value_tbl := JTF_VARCHAR2_TABLE_4000();
260 x_evaluation_criteria_tbl := JTF_VARCHAR2_TABLE_4000();
261
262
263 open lc_get_rule_details;
264 loop
265
266 fetch lc_get_rule_details into l_attribute_id, l_rule_id, l_currency,l_operator,l_cri_id, l_att_val, l_att_to_val;
267
268 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 debug(' Rule ID '||l_rule_id ||' Attribute ID '||l_attribute_id||' Criteria ID '||l_cri_id);
270 l_temp_variable := l_att_val;
271 while (l_temp_variable is not null) loop
272 debug('attr Val' ||substr( l_temp_variable, 1, 1800 ));
273 l_temp_variable := substr( l_temp_variable, 1801 );
274 end loop;
275 END IF;
276
277 l_total_rows := lc_get_rule_details%ROWCOUNT;
278
279 IF lc_get_rule_details%NOTFOUND AND l_total_rows = 0 THEN
280 x_rule_pass_flag := 'PASS';
281 return;
282 END IF;
283
284 -- ===========================================================================================
285 -- If a rule has same attributes with two criteria, they would be considered as AND Condition
286 -- In that case Attribute ID tbl will have same entry twice. If not then it is considered to be
287 -- OR condition.
288 -- Attr Value for OR Condition would be like US,UK ( Concatnated Value of all the attribute value
289 -- for that Rule ID and Attribute ID combination
290 -- =============================================================================================
291
292 -- ==============================================================================================
293 -- Evaluating for OR Condition
294 -- ==============================================================================================
295
296 IF l_cri_id is not null THEN
297
298 IF (l_prev_rule_id = l_rule_id AND l_prev_attr_id = l_attribute_id
299 AND l_prev_criteria_id = l_cri_id ) THEN
300
301 IF l_attr_value is null THEN
302 l_attr_value := l_prev_att_val;
303 l_attribute_value := ''''||l_prev_att_val||'''';
304 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
305 l_temp_variable := l_attribute_value;
306 while (l_temp_variable is not null) loop
307 debug('l_attribute_value' ||substr( l_temp_variable, 1, 1800 ));
308 l_temp_variable := substr( l_temp_variable, 1801 );
309 end loop;
310 END IF;
311
312 END IF;
313
314 l_criteria_count := l_criteria_count + 1;
315 l_attr_value := l_attr_value || x_delimiter ||l_att_val;
316 l_attribute_value := l_attribute_value || ',' || '''' || l_att_val || '''';
317
318 IF lc_get_rule_details%NOTFOUND THEN
319
320 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 debug('At the last record ................');
322 END IF;
323
324 l_criteria_count := l_criteria_count - 1;
325
326 -- =================================================================================================
327 -- Since the loop is not exited even after the last record the values of previous and current record
328 -- will be the same hence attr_value will have the value appended twice for the last record
329 -- Hence removing it
330 -- ==================================================================================================
331
332 l_attr_value := substr(l_attr_value, 1, length(l_attr_value)-length(l_att_val)-length(x_delimiter));
333 l_attribute_value := substr(l_attribute_value, 1, length(l_attribute_value)-length(l_att_val)-3);
334
335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336 l_temp_variable := l_attribute_value;
337 while (l_temp_variable is not null) loop
338 debug('l_attribute_value for the last record' ||substr( l_temp_variable, 1, 1800 ));
339 l_temp_variable := substr( l_temp_variable, 1801 );
340 end loop;
341 END IF;
342
343 -- ========================================================================================
344 -- Here the values will be inserted to the PL/SQL table only for the last record
345 -- and also if it is an OR condition
346 -- ========================================================================================
347
348 IF l_criteria_count >= 1 THEN
349
350 l_cnt := l_cnt + 1;
351
352 l_rule_id_tbl.extend;
353 x_attr_id_tbl.extend;
354 l_currency_tbl.extend;
355 l_operator_tbl.extend;
356 l_criterion_id_tbl.extend;
357 l_attr_val_tbl.extend;
358 l_attr_to_val.extend;
359 l_cri_condition_tbl.extend;
360 l_attribute_value_tbl.extend;
361
362 l_rule_id_tbl(l_cnt) := l_rule_id;
363 x_attr_id_tbl(l_cnt) := l_attribute_id;
364 l_currency_tbl(l_cnt) := l_currency;
365 l_operator_tbl(l_cnt) := l_operator;
366 l_criterion_id_tbl(l_cnt) := l_cri_id;
367 l_attr_val_tbl(l_cnt) := x_delimiter||l_attr_value||x_delimiter;
368 l_attribute_value_tbl(l_cnt) := l_attribute_value;
369
370 IF l_att_to_val is not null THEN
371 l_attr_to_val(l_cnt) := l_att_to_val;
372 ELSE
373 l_attr_to_val(l_cnt) := NULL;
374 END IF;
375
376 l_cri_condition_tbl(l_cnt) := 'OR';
377
378 END IF;
379 END IF;
380
381 ELSE
382
383 IF l_prev_rule_id is not null AND l_prev_attr_id is not null
384 AND l_prev_criteria_id is not null THEN
385
386 -- ============================================================================
387 -- Making sure that first record does not get inserted always with criteria
388 -- condition check.For the first record all the previous values will be null.
389 -- ============================================================================
390
391 l_cnt := l_cnt + 1;
392
393 l_rule_id_tbl.extend;
394 x_attr_id_tbl.extend;
395 l_currency_tbl.extend;
396 l_operator_tbl.extend;
397 l_criterion_id_tbl.extend;
398 l_attr_val_tbl.extend;
399 l_attr_to_val.extend;
400 l_cri_condition_tbl.extend;
401 l_attribute_value_tbl.extend;
402
403 l_total_criteria := l_criteria_count;
404 l_criteria_count := 0;
405
406 l_rule_id_tbl(l_cnt) := l_prev_rule_id;
407 x_attr_id_tbl(l_cnt) := l_prev_attr_id;
408 l_currency_tbl(l_cnt) := l_prev_currency;
409 l_operator_tbl(l_cnt) := l_prev_operator;
410 l_criterion_id_tbl(l_cnt) := l_prev_criteria_id;
411
412 IF l_total_criteria > 0 THEN
413
414 l_attr_val_tbl(l_cnt) := x_delimiter|| l_attr_value ||x_delimiter;
415 l_attribute_value_tbl(l_cnt):= l_attribute_value;
416
417 IF l_prev_att_to_val is not null THEN
418 l_attr_to_val(l_cnt) := l_prev_att_to_val;
419 ELSE
420 l_attr_to_val(l_cnt) := NULL;
421 END IF;
422
423 l_cri_condition_tbl(l_cnt) := 'OR';
424
425 ELSE
426 -- ===================================================================================
427 -- Criteria Condition Will be '0' for the AND condition which means that the Criteria ID
428 -- has changed.
429 -- ===================================================================================
430
431 l_attr_value := l_att_val;
432
433 IF l_prev_att_val IS NOT NULL THEN
434 l_attr_val_tbl(l_cnt) := x_delimiter||l_prev_att_val||x_delimiter;
435 l_attribute_value_tbl(l_cnt) := ''||l_prev_att_val||'';
436 ELSE
437 l_attr_val_tbl(l_cnt) := NULL;
438 l_attribute_value_tbl(l_cnt) := NULL;
439 END IF;
440 l_cri_condition_tbl(l_cnt) := 'AND';
441
442 IF l_prev_att_to_val is not null THEN
443 l_attr_to_val(l_cnt) := l_prev_att_to_val;
444 ELSE
445 l_attr_to_val(l_cnt) := NULL;
446 END IF;
447
448 END IF;
449
450 ELSE
451 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 debug('At first record ');
453 END IF;
454
455 l_non_cri_count := l_non_cri_count + 1;
456
457 END IF;
458
459 l_total_criteria := 0;
460 l_attr_value := null;
461 l_attribute_value := null;
462
463 END IF;
464
465 END IF;
466
467 -- ========================================================================================
468 -- Here the values will be inserted to the PL/SQL table only for the last record
469 -- and also if it is an AND condition
470 -- ========================================================================================
471
472 IF (lc_get_rule_details%NOTFOUND
473 AND l_total_rows > 0 AND l_criteria_count = 0) THEN
474
475 l_last_cri_cnt := l_criteria_count ;
476 l_attr_value := l_att_val;
477
478 l_rule_id_tbl.extend;
479 x_attr_id_tbl.extend;
480 l_currency_tbl.extend;
481 l_operator_tbl.extend;
482 l_criterion_id_tbl.extend;
483 l_attr_val_tbl.extend;
484 l_attr_to_val.extend;
485 l_cri_condition_tbl.extend;
486 l_attribute_value_tbl.extend;
487
488 l_cnt := l_cnt+1;
489
490 l_rule_id_tbl(l_cnt) := l_rule_id;
491 x_attr_id_tbl(l_cnt) := l_attribute_id;
492 l_currency_tbl(l_cnt) := l_currency;
493 l_operator_tbl(l_cnt) := l_operator;
494 l_criterion_id_tbl(l_cnt) := l_cri_id;
495
496 IF l_attr_value IS NOT NULL THEN
497 l_attr_val_tbl(l_cnt) := x_delimiter|| l_attr_value ||x_delimiter;
498 l_attribute_value_tbl(l_cnt) := ''||l_attr_value||'';
499 ELSE
500 l_attr_val_tbl(l_cnt) := NULL;
501 END IF;
502
503 IF l_att_to_val is not null THEN
504 l_attr_to_val(l_cnt) := l_att_to_val;
505 ELSE
506 l_attr_to_val(l_cnt) := NULL;
507 END IF;
508
509 l_cri_condition_tbl(l_cnt) := 'AND';
510
511 END IF;
512
513 l_prev_attr_id := l_attribute_id;
514 l_prev_rule_id := l_rule_id;
515 l_prev_criteria_id := l_cri_id;
516 l_prev_currency := l_currency;
517 l_prev_operator := l_operator;
518 l_prev_att_to_val := l_att_to_val;
519 l_prev_att_val := l_att_val;
520
521 exit when lc_get_rule_details%notfound;
522
523 end loop;
524 close lc_get_rule_details;
525
526 l_rule_pass_flag := TRUE;
527 x_rule_pass_flag := 'PASS';
528
529 IF l_rule_id_tbl.count > 0 THEN
530
531 for i in 1 .. l_rule_id_tbl.count
532 loop
533 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 debug(' Rule ID ####################### '|| l_rule_id_tbl(i));
535 debug(' Attribute ID ################## '|| x_attr_id_tbl(i));
536 debug(' Criteria ID ################### '|| l_criterion_id_tbl(i));
537 debug(' Currency ###################### '|| l_currency_tbl(i));
538 debug(' Operator ###################### '|| l_operator_tbl(i));
539 debug(' Attr to Val ################### '|| l_attr_to_val(i));
540 debug(' Criteria Condition ############ '|| l_cri_condition_tbl(i));
541 --debug(' Attr Value #################### '|| l_attr_val_tbl(i));
542 l_temp_variable := l_attr_val_tbl(i);
543 while (l_temp_variable is not null) loop
544 debug('Attr Value #################### ' ||substr( l_temp_variable, 1, 1800 ));
545 l_temp_variable := substr( l_temp_variable, 1801 );
546 end loop;
547
548 --debug(' Attribute Value ############### '|| l_attribute_value_tbl(i));
549
550 l_temp_variable := l_attribute_value_tbl(i);
551 while (l_temp_variable is not null) loop
552 debug('Attribute Value ############### ' ||substr( l_temp_variable, 1, 1800 ));
553 l_temp_variable := substr( l_temp_variable, 1801 );
554 end loop;
555 END IF;
556
557 l_count := l_count + 1;
558
559 -- =======================================================================
560 -- Retrieve meaning for operator
561 -- =======================================================================
562
563 select meaning
564 into l_opr_meaning
565 from pv_lookups
566 where lookup_type = 'PV_NUM_DATE_OPERATOR'
567 and lookup_code = l_operator_tbl(i);
568
569 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 debug(' Operator Meaning '|| l_opr_meaning );
571 END IF;
572
573 -- ==========================================================================
574 -- Retrieve the LOV String for the attribute to get the meaning of Attribute
575 -- Value
576 -- ==========================================================================
577
578 FOR v_sql_cursor IN lc_attr_mean_cursor(x_attr_id_tbl(i))
579 LOOP
580 l_lov_string := v_sql_cursor.lov_string;
581 END LOOP;
582
583 -- debug(' LOV String from pv_entity_attrs '|| l_lov_string );
584
585
586 -- ==========================================================================
587 -- LOV String is appended with the evaluated attribute value to get meaning
588 -- only for selected attribute values
589 -- ==========================================================================
590 IF (l_lov_string IS NULL OR LENGTH(l_lov_string) = 0) THEN
591
592 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
593 Debug ('Value could be NUMBER, DATE OR CURRENCY or STRING would be free text');
594 END IF;
595
596 END IF;
597
598
599
600
601 IF (l_lov_string IS NOT NULL OR LENGTH(l_lov_string) <> 0) THEN
602
603 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
604 debug(' in the If Condition ');
605 END IF;
606
607 -- ==========================================================================
608 -- Retrieved LOV_String has an '?' replacing that with ':X' since SQL cannot
609 -- recoganize '?' as an input
610 -- ==========================================================================
611
612 l_lov_string := replace(l_lov_string,'?',':X');
613
614 IF l_operator_tbl(i) = 'BETWEEN' THEN
615
616 l_lov_string := 'SELECT decode(t.code, :1, t.meaning, t.code) Attr_Value ,'||
617 'decode(t.code, :2, t.meaning, t.code) Attr_to_Value, t.code ' ||
618 'FROM ('|| l_lov_string ||' ) t WHERE t.code in ( :4,:5)';
619
620
621
622 ELSE
623
624 l_lov_string := 'SELECT t.meaning FROM (' || l_lov_string ||' ) t WHERE t.code IN '||
625 '( SELECT * FROM THE ( SELECT CAST( :Y as JTF_VARCHAR2_TABLE_32767 ) from dual ))';
626
627 END IF;
628
629 l_attr_in_string := replace(l_attribute_value_tbl(i),'''','');
630
631 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
632 --debug(' Attribute in string '||l_attr_in_string);
633 l_temp_variable := l_attr_in_string;
634 while (l_temp_variable is not null) loop
635 debug('Attribute in string ' ||substr( l_temp_variable, 1, 1800 ));
636 l_temp_variable := substr( l_temp_variable, 1801 );
637 end loop;
638 END IF;
639 IF l_operator_tbl(i) = 'BETWEEN' THEN
640 l_attr_val_meaning := null;
641 OPEN lc_attr_cursor FOR l_lov_string USING l_attr_in_string,l_attr_to_val(i),x_attr_id_tbl(i),l_attr_in_string, l_attr_to_val(i);
642 LOOP
643 FETCH lc_attr_cursor INTO l_att_val_mean, l_attrib_to_val, l_att_code;
644 EXIT WHEN lc_attr_cursor%NOTFOUND;
645
646
647 IF l_att_val_mean = l_att_code THEN
648 l_attr_to_val_mean := l_attrib_to_val;
649 END IF;
650
651 IF l_attrib_to_val = l_att_code THEN
652 l_attr_val_meaning := l_att_val_mean;
653 END IF;
654 END LOOP;
655 CLOSE lc_attr_cursor;
656
657 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 debug('Attribute Meaning for Between '|| l_attr_val_meaning ||'AND' ||l_attr_to_val_mean);
659 END IF;
660
661 l_concat_attr_mean := l_attr_val_meaning || ' and ' || l_attr_to_val_mean;
662
663
664 ELSE
665
666 OPEN lc_attr_cursor FOR l_lov_string USING x_attr_id_tbl(i),in_list(l_attr_in_string);
667 LOOP
668 FETCH lc_attr_cursor INTO l_attr_val_meaning;
669 EXIT WHEN lc_attr_cursor%NOTFOUND;
670
671 IF l_concat_attr_mean IS NOT NULL THEN
672 l_concat_attr_mean := l_concat_attr_mean ||','|| l_attr_val_meaning;
673 ELSE
674 l_concat_attr_mean := l_attr_val_meaning;
675 END IF;
676 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
677 debug('line Attribute Meaning '||l_attr_val_meaning);
678 END IF;
679 END LOOP;
680 CLOSE lc_attr_cursor;
681
682 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 debug('Attribute Meaning '||l_concat_attr_mean);
684 END IF;
685
686
687 END IF;
688
689 IF l_concat_attr_mean IS NULL THEN
690 l_concat_attr_mean := fnd_message.get_string('PV', 'PV_UNKNOWN');
691 END IF;
692
693 x_evaluation_criteria_tbl.extend;
694 x_evaluation_criteria_tbl(l_count) := l_opr_meaning || ' '|| l_concat_attr_mean;
695
696
697 l_concat_attr_mean := null;
698 l_lov_string := null;
699
700 ELSE
701
702 x_evaluation_criteria_tbl.extend;
703
704 IF l_operator_tbl(i) = 'BETWEEN' THEN
705 x_evaluation_criteria_tbl(l_count) := l_opr_meaning || ' '||
706 replace(l_attribute_value_tbl(i),'''') || ' and ' || l_attr_to_val(i);
707 ELSE
708 x_evaluation_criteria_tbl(l_count) := l_opr_meaning ||' '|| replace(l_attribute_value_tbl(i),'''');
709 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 debug(' x_evaluation_criteria_tbl(l_count) '|| x_evaluation_criteria_tbl(l_count));
711 END IF;
712 END IF;
713
714 END IF;
715
716 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
717 Debug('Before calling check_match partner_id '||p_partner_id||
718 ' l_attr_val_tbl(i) ' ||l_attr_val_tbl(i) ||'p_operator '|| l_operator_tbl(i),FND_LOG.LEVEL_PROCEDURE );
719 END IF;
720
721 l_result := pv_check_match_pub.Check_Match
722 (
723 p_attribute_id => x_attr_id_tbl(i),
724 p_entity => 'PARTNER',
725 p_entity_id => p_partner_id,
726 p_rule_attr_value => l_attr_val_tbl(i),
727 p_rule_to_attr_value => x_delimiter||l_attr_to_val(i)||x_delimiter,
728 p_operator => l_operator_tbl(i),
729 p_input_filter => l_input_filter,
730 p_delimiter => '+++',
731 p_rule_currency_code => l_currency_tbl(i),
732 x_entity_attr_value => l_entity_attr_value
733 );
734
735 x_attr_evaluation_result_tbl.extend;
736
737 IF (l_result) THEN
738
739 x_attr_evaluation_result_tbl(x_attr_evaluation_result_tbl.count) := 'PASS';
740
741 ELSE
742
743 x_attr_evaluation_result_tbl(x_attr_evaluation_result_tbl.count) := 'FAIL';
744 l_rule_pass_flag := FALSE;
745 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
746 Debug('Failed evaluating ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^',FND_LOG.LEVEL_PROCEDURE);
747 END IF;
748 x_rule_pass_flag := 'FAIL';
749 END IF;
750
751
752 x_partner_attr_value_tbl.extend;
753 x_partner_attr_value_tbl(x_partner_attr_value_tbl.count) := l_entity_attr_value(x_attr_id_tbl(i)).attribute_value;
754
755 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
756 --debug('Partner Attribute Value %%%%%%%% :'||l_entity_attr_value(x_attr_id_tbl(i)).attribute_value,FND_LOG.LEVEL_PROCEDURE);
757 l_temp_variable := l_entity_attr_value(x_attr_id_tbl(i)).attribute_value;
758 while (l_temp_variable is not null) loop
759 debug('Partner Attribute Value %%%%%%%% :' ||substr( l_temp_variable, 1, 1800 ) ,FND_LOG.LEVEL_PROCEDURE);
760 l_temp_variable := substr( l_temp_variable, 1801 );
761 end loop;
762
763 debug('Return Type %%%%%%%%%%%%%%%%%%%% :'||l_entity_attr_value(x_attr_id_tbl(i)).return_type,FND_LOG.LEVEL_PROCEDURE);
764 END IF;
765
766 END LOOP;
767
768
769 END IF;
770
771 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 debug('End of partner_eval_outcome',FND_LOG.LEVEL_PROCEDURE);
773 END IF;
774
775
776
777
778 EXCEPTION
779 WHEN FND_API.G_EXC_ERROR THEN
780 x_return_status := FND_API.G_RET_STS_ERROR;
781
782 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
783 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_ERROR );
784 END IF;
785 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
786 p_count => x_msg_count,
787 p_data => x_msg_data);
788
789 WHEN FND_API.g_exc_unexpected_error THEN
790 x_return_status := FND_API.g_ret_sts_unexp_error;
791
792 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
793 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_UNEXPECTED );
794 END IF;
795
796 FND_MSG_PUB.count_and_get(
797 p_encoded => FND_API.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802 WHEN OTHERS THEN
803 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
804 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
805 END IF;
806
807 x_return_status := FND_API.G_RET_STS_ERROR;
808
809 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
810 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_UNEXPECTED );
811 END IF;
812
813 FND_MSG_PUB.count_and_get(
814 p_encoded => FND_API.g_false,
815 p_count => x_msg_count,
816 p_data => x_msg_data
817 );
818
819
820 END;
821 --=============================================================================+
822 --| Public Procedure |
823 --| |
824 --| quick_partner_eval_outcome |
825 --| |
826 --| Parameters |
827 --| IN |
828 --| OUT |
829 --| |
830 --| |
831 --| NOTES: |
832 --| |
833 --| HISTORY |
834 --| |
835 --==============================================================================
836
837 PROCEDURE quick_partner_eval_outcome(
838 p_api_version IN NUMBER,
839 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
840 p_commit IN VARCHAR2 := FND_API.g_false,
841 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
842 p_partner_id IN NUMBER,
843 p_rule_id_tbl IN JTF_NUMBER_TABLE,
844 x_rule_pass_flag OUT NOCOPY VARCHAR2,
845 x_return_status OUT NOCOPY VARCHAR2,
846 x_msg_count OUT NOCOPY NUMBER,
847 x_msg_data OUT NOCOPY VARCHAR2
848 )
849
850 IS
851 x_attr_id_tbl JTF_NUMBER_TABLE;
852 x_attr_evaluation_result_tbl JTF_VARCHAR2_TABLE_100;
853 x_partner_attr_value_tbl JTF_VARCHAR2_TABLE_4000;
854 x_evaluation_criteria_tbl JTF_VARCHAR2_TABLE_4000;
855 x_delimiter VARCHAR2(10);
856
857 TYPE c_attr_mean IS REF CURSOR;
858
859 l_rule_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
860 l_attr_val_tbl JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
861 l_attr_to_val JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
862 l_operator_tbl JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
863 l_currency_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
864 l_criterion_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
865 l_cri_condition_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
866 l_attribute_value_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
867
868
869 l_input_filter pv_check_match_pub.t_input_filter;
870 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
871
872 lc_attr_cursor c_attr_mean;
873 l_lov_string VARCHAR2(32000) := null;
874
875 l_rule_id NUMBER;
876 l_attribute_id NUMBER;
877 l_currency VARCHAR2(30);
878 l_operator VARCHAR2(30);
879 l_cri_id NUMBER;
880 l_att_val VARCHAR2(4000);
881 l_att_to_val VARCHAR2(1000);
882 l_att_val_mean VARCHAR2(1000);
883 l_opr_meaning VARCHAR2(1000);
884 l_attribute_name VARCHAR2(1000);
885 l_attr_in_string VARCHAR2(4000);
886
887 l_prev_rule_id NUMBER := NULL;
888 l_prev_attr_id NUMBER := NULL;
889 l_prev_criteria_id NUMBER := NULL;
890 l_prev_currency VARCHAR2(1000);
891 l_prev_operator VARCHAR2(1000);
892 l_prev_att_val VARCHAR2(4000) := NULL;
893 l_prev_att_to_val VARCHAR2(1000);
894 l_prev_att_name VARCHAR2(1000);
895
896 l_attr_value VARCHAR2(4000) := NULL;
897 l_attr_val_meaning VARCHAR2(1000) := NULL;
898 l_attr_to_val_mean VARCHAR2(1000) := NULL;
899 l_attrib_to_val VARCHAR2(1000) := NULL;
900 l_att_code VARCHAR2(1000) := NULL;
901 l_concat_attr_mean VARCHAR2(1000) := NULL;
902
903
904 l_attribute_value VARCHAR2(4000) := NULL;
905
906 l_rule_pass_flag BOOLEAN := FALSE;
907 l_total_rows NUMBER;
908 l_criteria_count NUMBER := 0;
909 l_non_cri_count NUMBER := 0;
910 l_last_cri_cnt NUMBER := 0;
911 l_total_criteria NUMBER := 0;
912 l_temp_variable varchar2(4000);
913 j NUMBER;
914 l_cnt NUMBER := 0;
915 l_count NUMBER := 0;
916
917
918 l_result BOOLEAN;
919
920
921 -- =============================================================================================
922 -- This query evaluates the attribute Id for the given Rule ID Set
923 -- Rules are hierarchical.
924 -- e.g.
925 -- Rule1(root) --> Rule2(Level 1 Child) --> Rule3(Level 2 Child)
926 -- -----------------------------------------------------------------------
927 -- Rule1 (attribute1 > 30, attribute2 > 40)
928 -- Rule2 (attribute3 > 50, attribute4 > 60)
929 -- Rule3 (attribute1 > 45, attribute4 > 50)
930
931 -- Combined Attribute List (which will be used as the basis of evaluation:
932 -- attribute1 > 45 (R3 overwrites R1)
933 -- attribute2 > 40
934 -- attribute3 > 50
935 -- attribute4 > 60
936
937 -- Query Description :
938
939
940 -- Inline View Z :
941
942 -- "SELECT a.process_rule_id, b.attribute_id, x.rank, x.process_rule_id rule_id
943 -- FROM pv_process_rules_vl a, pv_enty_select_criteria b,
944 -- (SELECT rownum rank, column_value process_rule_id
945 -- FROM (SELECT column_value FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE))) ) x
946 -- WHERE a.process_rule_id = x.process_rule_id
947 -- AND a.process_rule_id = b.process_rule_id "
948
949 -- " SELECT column_value FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE "
950 -- In the above select statement CAST ensures the order of Rule ID set from the query
951 -- remains the same as that of INPUT RULE ID TABLE.
952
953 --
954 -- Since rules are hierarchical rank is assigned to them starting with the Parent (Parent
955 -- Rule ID gets the least priority )
956
957 -- Rownum evaluates the rank of the attribute ID based on how many times it is repeated in the rule.
958 -- Result of the above inline view Z would be
959 -- -----------------------------------------------------------------------------------------------
960 -- Rule ID Attribute ID Rank
961 -- ================================================================================================
962 -- 1 attribute1 1
963 -- 1 attribute2 1
964 -- 2 attribute3 2
965 -- 2 attribute4 2
966 -- 3 attribute1 3
967 -- 3 attribute4 3
968 -- ------------------------------------------------------------------------------------------------
969 -- When we take the MAX(rank) grouped by attribute ID
970 -- for the above set the combined attribute list is achieved
971
972 -- Final combined attribute list from INLINE VIEW XY would be
973 -- -------------------------------------------------------------------------------------------------
974 -- Rule ID Attribute ID Rank
975 -- =================================================================================================
976 -- 1 attribute2 1
977 -- 2 attribute3 2
978 -- 3 attribute1 3
979 -- 3 attribute4 3
980 -- --------------------------------------------------------------------------------------------------
981
982 -- ====================================================================================================
983
984
985 cursor lc_get_rule_details
986 is
987 SELECT xy.attribute_id Attribute_ID,
988 xy.rule_id Rule_ID,
989 rule.currency_code Currency_Code,
990 criterion.operator Operator,
991 criterion.selection_criteria_id Criteria_ID,
992 AttVal.attribute_value Attr_Value,
993 AttVal.attribute_to_value Attr_To_Value
994 FROM pv_process_rules_vl Rule,
995 pv_enty_select_criteria Criterion,
996 pv_selected_attr_values AttVal,
997 ( SELECT attribute_id,TO_NUMBER(LTRIM(substr(rule_rank, 1, 10), 0)) rank,TO_NUMBER(LTRIM(substr(rule_rank, 11, LENGTH(rule_rank)), 0)) rule_id
998 FROM (
999 SELECT z.attribute_id, MAX(LPAD(z.rank, 10, 0) || LPAD(z.process_rule_id, 100, 0)) rule_rank
1000 FROM (
1001 SELECT a.process_rule_id, b.attribute_id, x.rank, x.process_rule_id rule_id
1002 FROM pv_process_rules_vl a,
1003 pv_enty_select_criteria b,
1004 (SELECT rownum rank, column_value process_rule_id
1005 FROM (SELECT column_value
1006 FROM TABLE (CAST(p_rule_id_tbl AS JTF_NUMBER_TABLE))) ) x
1007 WHERE a.process_rule_id = x.process_rule_id
1008 AND a.process_rule_id = b.process_rule_id ) z
1009 group by z.attribute_id
1010 )) xy
1011 WHERE Rule.process_rule_id = Criterion.process_rule_id
1012 AND Criterion.selection_criteria_id = Attval.selection_criteria_id
1013 AND Criterion.selection_type_code = 'PARTNER_SELECTION'
1014 AND Rule.process_rule_id = xy.rule_id
1015 AND criterion.attribute_id = xy.attribute_id
1016 AND criterion.process_rule_id = xy.rule_id
1017 order by xy.rank;
1018
1019 CURSOR lc_attr_mean_cursor(pc_attribute_id NUMBER) IS
1020 SELECT lov_string
1021 FROM pv_entity_attrs
1022 WHERE lov_string IS NOT NULL
1023 AND entity = 'PARTNER'
1024 AND enabled_flag = 'Y'
1025 AND attribute_id = pc_attribute_id;
1026
1027
1028 l_api_name CONSTANT VARCHAR2(30) := 'quick_partner_eval_outcome';
1029 l_api_version_number CONSTANT NUMBER := 1.0;
1030
1031
1032
1033 begin
1034
1035
1036
1037
1038 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1039 Debug(l_api_name,FND_LOG.LEVEL_PROCEDURE);
1040 END IF;
1041
1042 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1043 p_api_version,
1044 l_api_name,
1045 G_PKG_NAME) THEN
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047
1048 END IF;
1049
1050 -- Initialize message list if p_init_msg_list is set to TRUE.
1051 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1052 fnd_msg_pub.initialize;
1053 END IF;
1054
1055 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1056 x_delimiter := '+++';
1057
1058
1059 IF p_rule_id_tbl.count = 0 THEN
1060
1061 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1062 p_msg_name => 'PV_DEBUG_MESSAGE',
1063 p_token1 => 'TEXT',
1064 p_token1_value => 'Rule ID is not passed in',
1065 p_token2 => NULL,
1066 p_token2_value => NULL);
1067
1068 RAISE FND_API.G_EXC_ERROR;
1069
1070 END IF;
1071
1072
1073 x_attr_id_tbl := JTF_NUMBER_TABLE();
1074 x_attr_evaluation_result_tbl := JTF_VARCHAR2_TABLE_100();
1075 x_partner_attr_value_tbl := JTF_VARCHAR2_TABLE_4000();
1076 x_evaluation_criteria_tbl := JTF_VARCHAR2_TABLE_4000();
1077
1078 open lc_get_rule_details;
1079 loop
1080
1081 fetch lc_get_rule_details into l_attribute_id, l_rule_id, l_currency,l_operator,l_cri_id, l_att_val, l_att_to_val;
1082
1083 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 debug(' Rule ID '||l_rule_id ||' Attribute ID '||l_attribute_id||' Criteria ID '||l_cri_id);
1085 --||' Attr Value '||l_att_val);
1086 l_temp_variable := l_att_val;
1087 while (l_temp_variable is not null) loop
1088 debug('attr Val ' ||substr( l_temp_variable, 1, 1800 ));
1089 l_temp_variable := substr( l_temp_variable, 1801 );
1090 end loop;
1091
1092 END IF;
1093
1094
1095 l_total_rows := lc_get_rule_details%ROWCOUNT;
1096
1097
1098 IF lc_get_rule_details%NOTFOUND AND l_total_rows = 0 THEN
1099
1100 x_rule_pass_flag := 'PASS';
1101 return;
1102
1103 END IF;
1104
1105
1106
1107 -- ===========================================================================================
1108 -- If a rule has same attributes with two criteria, they would be considered as AND Condition
1109 -- In that case Attribute ID tbl will have same entry twice. If not then it is considered to be
1110 -- OR condition.
1111 -- Attr Value for OR Condition would be like US,UK ( Concatnated Value of all the attribute value
1112 -- for that Rule ID and Attribute ID combination
1113 -- =============================================================================================
1114
1115
1116 -- ==============================================================================================
1117 -- Evaluating for OR Condition
1118 -- ==============================================================================================
1119
1120 IF l_cri_id is not null THEN
1121
1122 IF (l_prev_rule_id = l_rule_id AND l_prev_attr_id = l_attribute_id
1123 AND l_prev_criteria_id = l_cri_id ) THEN
1124
1125
1126 IF l_attr_value is null THEN
1127
1128 l_attr_value := l_prev_att_val;
1129
1130 l_attribute_value := ''''||l_prev_att_val||'''';
1131
1132 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1133 --debug('l_attribute_value '||l_attribute_value);
1134 l_temp_variable := l_attribute_value;
1135 while (l_temp_variable is not null) loop
1136 debug('l_attribute_value ' ||substr( l_temp_variable, 1, 1800 ));
1137 l_temp_variable := substr( l_temp_variable, 1801 );
1138 end loop;
1139 END IF;
1140
1141 END IF;
1142
1143 l_criteria_count := l_criteria_count + 1;
1144
1145
1146
1147 l_attr_value := l_attr_value || x_delimiter ||l_att_val;
1148
1149 l_attribute_value := l_attribute_value || ',' || '''' || l_att_val || '''';
1150
1151
1152
1153 IF lc_get_rule_details%NOTFOUND THEN
1154
1155 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 debug('At the last record ................');
1157 END IF;
1158
1159
1160 l_criteria_count := l_criteria_count - 1;
1161
1162 -- =================================================================================================
1163 -- Since the loop is not exited even after the last record the values of previous and current record
1164 -- will be the same hence attr_value will have the value appended twice for the last record
1165 -- Hence removing it
1166 -- ==================================================================================================
1167
1168
1169 l_attr_value := substr(l_attr_value, 1, length(l_attr_value)-length(l_att_val)-length(x_delimiter));
1170 l_attribute_value := substr(l_attribute_value, 1, length(l_attribute_value)-length(l_att_val)-3);
1171
1172 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1173 --debug('l_attribute_value for last record '|| l_attribute_value);
1174 l_temp_variable := l_attribute_value;
1175 while (l_temp_variable is not null) loop
1176 debug('l_attribute_value for the last record' ||substr( l_temp_variable, 1, 1800 ));
1177 l_temp_variable := substr( l_temp_variable, 1801 );
1178 end loop;
1179
1180 END IF;
1181
1182 -- ========================================================================================
1183 -- Here the values will be inserted to the PL/SQL table only for the last record
1184 -- and also if it is an OR condition
1185 -- ========================================================================================
1186
1187 IF l_criteria_count >= 1 THEN
1188
1189 l_cnt := l_cnt + 1;
1190
1191 l_rule_id_tbl.extend;
1192 x_attr_id_tbl.extend;
1193 l_currency_tbl.extend;
1194 l_operator_tbl.extend;
1195 l_criterion_id_tbl.extend;
1196 l_attr_val_tbl.extend;
1197 l_attr_to_val.extend;
1198 l_cri_condition_tbl.extend;
1199 l_attribute_value_tbl.extend;
1200
1201 l_rule_id_tbl(l_cnt) := l_rule_id;
1202 x_attr_id_tbl(l_cnt) := l_attribute_id;
1203 l_currency_tbl(l_cnt) := l_currency;
1204 l_operator_tbl(l_cnt) := l_operator;
1205 l_criterion_id_tbl(l_cnt) := l_cri_id;
1206 l_attr_val_tbl(l_cnt) := x_delimiter||l_attr_value||x_delimiter;
1207 l_attribute_value_tbl(l_cnt) := l_attribute_value;
1208
1209 IF l_att_to_val is not null THEN
1210 l_attr_to_val(l_cnt) := l_att_to_val;
1211 ELSE
1212 l_attr_to_val(l_cnt) := NULL;
1213 END IF;
1214
1215 l_cri_condition_tbl(l_cnt) := 'OR';
1216
1217 END IF;
1218
1219 END IF;
1220
1221 ELSE
1222
1223
1224 IF l_prev_rule_id is not null AND l_prev_attr_id is not null
1225 AND l_prev_criteria_id is not null THEN
1226
1227 -- ============================================================================
1228 -- Making sure that first record does not get inserted always with criteria
1229 -- condition check.For the first record all the previous values will be null.
1230 -- ============================================================================
1231
1232
1233 l_cnt := l_cnt + 1;
1234
1235 l_rule_id_tbl.extend;
1236 x_attr_id_tbl.extend;
1237 l_currency_tbl.extend;
1238 l_operator_tbl.extend;
1239 l_criterion_id_tbl.extend;
1240 l_attr_val_tbl.extend;
1241 l_attr_to_val.extend;
1242 l_cri_condition_tbl.extend;
1243 l_attribute_value_tbl.extend;
1244
1245
1246 l_total_criteria := l_criteria_count;
1247 l_criteria_count := 0;
1248
1249 l_rule_id_tbl(l_cnt) := l_prev_rule_id;
1250 x_attr_id_tbl(l_cnt) := l_prev_attr_id;
1251 l_currency_tbl(l_cnt) := l_prev_currency;
1252 l_operator_tbl(l_cnt) := l_prev_operator;
1253 l_criterion_id_tbl(l_cnt) := l_prev_criteria_id;
1254
1255 IF l_total_criteria > 0 THEN
1256
1257 l_attr_val_tbl(l_cnt) := x_delimiter|| l_attr_value ||x_delimiter;
1258 l_attribute_value_tbl(l_cnt):= l_attribute_value;
1259
1260 IF l_prev_att_to_val is not null THEN
1261 l_attr_to_val(l_cnt) := l_prev_att_to_val;
1262 ELSE
1263 l_attr_to_val(l_cnt) := NULL;
1264 END IF;
1265
1266 l_cri_condition_tbl(l_cnt) := 'OR';
1267
1268
1269
1270 ELSE
1271
1272 -- ===================================================================================
1273 -- Criteria Condition Will be '0' for the AND condition which means that the Criteria ID
1274 -- has changed.
1275 -- ===================================================================================
1276
1277
1278
1279 l_attr_value := l_att_val;
1280
1281
1282 IF l_prev_att_val IS NOT NULL THEN
1283 l_attr_val_tbl(l_cnt) := x_delimiter||l_prev_att_val||x_delimiter;
1284 l_attribute_value_tbl(l_cnt) := ''''||l_prev_att_val||'''';
1285 ELSE
1286 l_attr_val_tbl(l_cnt) := NULL;
1287 l_attribute_value_tbl(l_cnt) := NULL;
1288 END IF;
1289
1290 l_cri_condition_tbl(l_cnt) := 'AND';
1291
1292 IF l_prev_att_to_val is not null THEN
1293 l_attr_to_val(l_cnt) := l_prev_att_to_val;
1294 ELSE
1295 l_attr_to_val(l_cnt) := NULL;
1296 END IF;
1297
1298
1299
1300 END IF;
1301
1302 ELSE
1303 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304 debug('At first record ');
1305 END IF;
1306
1307 l_non_cri_count := l_non_cri_count + 1;
1308
1309 END IF;
1310
1311 l_total_criteria := 0;
1312 l_attr_value := null;
1313 l_attribute_value := null;
1314
1315 END IF;
1316
1317 END IF;
1318
1319 -- ========================================================================================
1320 -- Here the values will be inserted to the PL/SQL table only for the last record
1321 -- and also if it is an AND condition
1322 -- ========================================================================================
1323
1324 IF (lc_get_rule_details%NOTFOUND
1325 AND l_total_rows > 0 AND l_criteria_count = 0) THEN
1326
1327
1328 l_last_cri_cnt := l_criteria_count ;
1329
1330 l_attr_value := l_att_val;
1331
1332
1333 l_rule_id_tbl.extend;
1334 x_attr_id_tbl.extend;
1335 l_currency_tbl.extend;
1336 l_operator_tbl.extend;
1337 l_criterion_id_tbl.extend;
1338 l_attr_val_tbl.extend;
1339 l_attr_to_val.extend;
1340 l_cri_condition_tbl.extend;
1341 l_attribute_value_tbl.extend;
1342
1343
1344 l_cnt := l_cnt+1;
1345
1346 l_rule_id_tbl(l_cnt) := l_rule_id;
1347 x_attr_id_tbl(l_cnt) := l_attribute_id;
1348 l_currency_tbl(l_cnt) := l_currency;
1349 l_operator_tbl(l_cnt) := l_operator;
1350 l_criterion_id_tbl(l_cnt) := l_cri_id;
1351
1352 IF l_attr_value IS NOT NULL THEN
1353 l_attr_val_tbl(l_cnt) := x_delimiter|| l_attr_value ||x_delimiter;
1354 l_attribute_value_tbl(l_cnt) := ''''||l_attr_value||'''';
1355 ELSE
1356 l_attr_val_tbl(l_cnt) := NULL;
1357 END IF;
1358
1359 IF l_att_to_val is not null THEN
1360 l_attr_to_val(l_cnt) := l_att_to_val;
1361 ELSE
1362 l_attr_to_val(l_cnt) := NULL;
1363 END IF;
1364
1365 l_cri_condition_tbl(l_cnt) := 'AND';
1366
1367
1368 END IF;
1369
1370 l_prev_attr_id := l_attribute_id;
1371 l_prev_rule_id := l_rule_id;
1372 l_prev_criteria_id := l_cri_id;
1373 l_prev_currency := l_currency;
1374 l_prev_operator := l_operator;
1375 l_prev_att_to_val := l_att_to_val;
1376 l_prev_att_val := l_att_val;
1377
1378
1379 exit when lc_get_rule_details%notfound;
1380
1381
1382 END LOOP;
1383
1384
1385 close lc_get_rule_details;
1386
1387 l_rule_pass_flag := TRUE;
1388 x_rule_pass_flag := 'PASS';
1389
1390 IF l_rule_id_tbl.count > 0 THEN
1391
1392 for i in 1 .. l_rule_id_tbl.count
1393 loop
1394 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1395 debug(' Rule ID ####################### '|| l_rule_id_tbl(i));
1396 debug(' Attribute ID ################## '|| x_attr_id_tbl(i));
1397 debug(' Criteria ID ################### '|| l_criterion_id_tbl(i));
1398 debug(' Currency ###################### '|| l_currency_tbl(i));
1399 debug(' Operator ###################### '|| l_operator_tbl(i));
1400 debug(' Attr to Val ################### '|| l_attr_to_val(i));
1401 debug(' Criteria Condition ############ '|| l_cri_condition_tbl(i));
1402 --debug(' Attr Value #################### '|| l_attr_val_tbl(i));
1403
1404 l_temp_variable := l_attr_val_tbl(i);
1405 while (l_temp_variable is not null) loop
1406 debug('Attr Value ###############' ||substr( l_temp_variable, 1, 1800 ));
1407 l_temp_variable := substr( l_temp_variable, 1801 );
1408 end loop;
1409
1410 --debug(' Attribute Value ############### '|| l_attribute_value_tbl(i));
1411
1412 l_temp_variable := l_attribute_value_tbl(i);
1413 while (l_temp_variable is not null) loop
1414 debug('Attribute Value ###############' ||substr( l_temp_variable, 1, 1800 ));
1415 l_temp_variable := substr( l_temp_variable, 1801 );
1416 end loop;
1417 END IF;
1418
1419 l_count := l_count + 1;
1420
1421 -- =======================================================================
1422 -- Retrieve meaning for operator
1423 -- =======================================================================
1424
1425 select meaning
1426 into l_opr_meaning
1427 from pv_lookups
1428 where lookup_type = 'PV_NUM_DATE_OPERATOR'
1429 and lookup_code = l_operator_tbl(i);
1430
1431 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 debug(' Operator Meaning '|| l_opr_meaning );
1433 END IF;
1434
1435 -- ==========================================================================
1436 -- Retrieve the LOV String for the attribute to get the meaning of Attribute
1437 -- Value
1438 -- ==========================================================================
1439
1440 FOR v_sql_cursor IN lc_attr_mean_cursor(x_attr_id_tbl(i)) LOOP
1441 l_lov_string := v_sql_cursor.lov_string;
1442 END LOOP;
1443
1444 -- debug(' LOV String from pv_entity_attrs '|| l_lov_string );
1445
1446
1447 -- ==========================================================================
1448 -- LOV String is appended with the evaluated attribute value to get meaning
1449 -- only for selected attribute values
1450 -- ==========================================================================
1451 IF (l_lov_string IS NULL OR LENGTH(l_lov_string) = 0) THEN
1452
1453 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1454 Debug ('Value could be NUMBER, DATE OR CURRENCY or STRING would be free text');
1455 END IF;
1456
1457 END IF;
1458
1459
1460 IF (l_lov_string IS NOT NULL OR LENGTH(l_lov_string) <> 0) THEN
1461
1462 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 debug(' in the If Condition ');
1464 END IF;
1465
1466 -- ==========================================================================
1467 -- Retrieved LOV_String has an '?' replacing that with ':X' since SQL cannot
1468 -- recoganize '?' as an input
1469 -- ==========================================================================
1470
1471 l_lov_string := replace(l_lov_string,'?',':X');
1472
1473
1474 IF l_operator_tbl(i) = 'BETWEEN' THEN
1475
1476 l_lov_string := 'SELECT decode(t.code, :1, t.meaning, t.code) Attr_Value ,'||
1477 'decode(t.code, :2, t.meaning, t.code) Attr_to_Value, t.code ' ||
1478 'FROM ('|| l_lov_string ||' ) t WHERE t.code in ( :4,:5)';
1479
1480
1481
1482 ELSE
1483
1484 l_lov_string := 'SELECT t.meaning FROM (' || l_lov_string ||' ) t WHERE t.code IN '||
1485 '( SELECT * FROM THE ( SELECT CAST( :Y as JTF_VARCHAR2_TABLE_32767 ) from dual ))';
1486
1487 END IF;
1488
1489 l_attr_in_string := replace(l_attribute_value_tbl(i),'''','');
1490
1491 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 --debug(' Attribute in string '||l_attr_in_string);
1493 l_temp_variable := l_attr_in_string;
1494 while (l_temp_variable is not null) loop
1495 debug('Attribute in String ' ||substr( l_temp_variable, 1, 1800 ));
1496 l_temp_variable := substr( l_temp_variable, 1801 );
1497 end loop;
1498 END IF;
1499
1500 IF l_operator_tbl(i) = 'BETWEEN' THEN
1501 -- --------------------------------------------------------------------------
1502 -- The bind variables in l_lov_string are as follows:
1503 -- 1 = l_attribute_value_tbl(i)
1504 -- 2 = l_attr_to_val(i)
1505 -- 3 = :attribute_id in l_lov_string
1506 -- 4 = l_attribute_value_tbl(i)
1507 -- 5 = l_attr_to_val(i)
1508 -- --------------------------------------------------------------------------
1509 OPEN lc_attr_cursor FOR l_lov_string USING l_attr_in_string,l_attr_to_val(i),x_attr_id_tbl(i),l_attr_in_string, l_attr_to_val(i);
1510 LOOP
1511 FETCH lc_attr_cursor INTO l_att_val_mean, l_attrib_to_val, l_att_code;
1512 EXIT WHEN lc_attr_cursor%NOTFOUND;
1513
1514
1515 IF l_att_val_mean = l_att_code THEN
1516 l_attr_to_val_mean := l_attrib_to_val;
1517 END IF;
1518
1519 IF l_attrib_to_val = l_att_code THEN
1520 l_attr_val_meaning := l_att_val_mean;
1521 END IF;
1522 END LOOP;
1523 CLOSE lc_attr_cursor;
1524
1525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526 debug('Attribute Meaning for Between '|| l_attr_val_meaning ||'AND' ||l_attr_to_val_mean);
1527 END IF;
1528
1529 l_concat_attr_mean := l_attr_val_meaning || ' and ' || l_attr_to_val_mean;
1530
1531
1532 ELSE
1533 -- --------------------------------------------------------------------------
1534 -- The bind variables in l_lov_string are as follows:
1535 -- 1 = :attribute_id in l_lov_string
1536 -- 2 = l_attribute_value_tbl(i)
1537 -- --------------------------------------------------------------------------
1538
1539
1540 OPEN lc_attr_cursor FOR l_lov_string USING x_attr_id_tbl(i),in_list(l_attr_in_string);
1541 LOOP
1542 FETCH lc_attr_cursor INTO l_attr_val_meaning;
1543 EXIT WHEN lc_attr_cursor%NOTFOUND;
1544
1545 IF l_concat_attr_mean IS NOT NULL THEN
1546 l_concat_attr_mean := l_concat_attr_mean ||','|| l_attr_val_meaning;
1547 ELSE
1548 l_concat_attr_mean := l_attr_val_meaning;
1549 END IF;
1550 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1551 debug('line Attribute Meaning '||l_attr_val_meaning);
1552 END IF;
1553 END LOOP;
1554 CLOSE lc_attr_cursor;
1555
1556 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1557 debug('Attribute Meaning '||l_concat_attr_mean);
1558 END IF;
1559
1560
1561 END IF;
1562 /* ------------------------------------------------------------------
1563 IF l_concat_attr_mean IS NULL THEN
1564 l_concat_attr_mean := fnd_message.get_string('PV', 'PV_UNKNOWN');
1565 END IF;
1566 * -------------------------------------------------------------------- */
1567
1568 x_evaluation_criteria_tbl.extend;
1569 x_evaluation_criteria_tbl(l_count) := l_opr_meaning || ' '|| l_concat_attr_mean;
1570
1571
1572 l_concat_attr_mean := null;
1573 l_lov_string := null;
1574
1575 ELSE
1576
1577 x_evaluation_criteria_tbl.extend;
1578
1579 IF l_operator_tbl(i) = 'BETWEEN' THEN
1580 x_evaluation_criteria_tbl(l_count) := l_opr_meaning || ' '||
1581 replace(l_attribute_value_tbl(i),'''') || ' and ' || l_attr_to_val(i);
1582 ELSE
1583 x_evaluation_criteria_tbl(l_count) := l_opr_meaning ||' '|| replace(l_attribute_value_tbl(i),'''');
1584
1585 END IF;
1586
1587 END IF;
1588
1589 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1590 debug('Start of pv_check_match_pub.Check_Match ',FND_LOG.LEVEL_PROCEDURE);
1591 debug('p_entity_id '||p_partner_id);
1592 --||' p_rule_attr_value '||l_attr_val_tbl(i)
1593 l_temp_variable := l_attr_val_tbl(i);
1594 while (l_temp_variable is not null) loop
1595 debug('p_rule_attr_value ' ||substr( l_temp_variable, 1, 1800 ));
1596 l_temp_variable := substr( l_temp_variable, 1801 );
1597 end loop;
1598
1599 debug(' p_attribute_id '||x_attr_id_tbl(i)||' p_rule_to_attr_value '||l_attr_to_val(i)
1600 ||' p_operator '||l_operator_tbl(i),FND_LOG.LEVEL_PROCEDURE );
1601
1602
1603 END IF;
1604
1605 l_result := pv_check_match_pub.Check_Match
1606 (
1607 p_attribute_id => x_attr_id_tbl(i),
1608 p_entity => 'PARTNER',
1609 p_entity_id => p_partner_id,
1610 p_rule_attr_value => l_attr_val_tbl(i),
1611 p_rule_to_attr_value => x_delimiter||l_attr_to_val(i)||x_delimiter,
1612 p_operator => l_operator_tbl(i),
1613 p_input_filter => l_input_filter,
1614 p_delimiter => '+++',
1615 p_rule_currency_code => l_currency_tbl(i),
1616 x_entity_attr_value => l_entity_attr_value
1617 );
1618
1619 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1620 --debug('Partner Attribute Value %%%%%%%% :'||l_entity_attr_value(x_attr_id_tbl(i)).attribute_value,FND_LOG.LEVEL_PROCEDURE);
1621 l_temp_variable := l_entity_attr_value(x_attr_id_tbl(i)).attribute_value;
1622 while (l_temp_variable is not null) loop
1623 debug('Partner Attribute Value %%%%%%%% : ' ||substr( l_temp_variable, 1, 1800 ),FND_LOG.LEVEL_PROCEDURE);
1624 l_temp_variable := substr( l_temp_variable, 1801 );
1625 end loop;
1626 END IF;
1627
1628 x_attr_evaluation_result_tbl.extend;
1629
1630 IF (l_result) THEN
1631
1632 x_attr_evaluation_result_tbl(x_attr_evaluation_result_tbl.count) := 'PASS';
1633
1634 ELSE
1635
1636 x_attr_evaluation_result_tbl(x_attr_evaluation_result_tbl.count) := 'FAIL';
1637 l_rule_pass_flag := FALSE;
1638
1639 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1640 Debug('Failed evaluating ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^');
1641 END IF;
1642
1643 x_rule_pass_flag := 'FAIL';
1644
1645 -- -------------------------------------------------------------------------------
1646 -- Exit the code as soon as there is a mismatch
1647 -- -------------------------------------------------------------------------------
1648 RETURN;
1649 END IF;
1650
1651
1652 x_partner_attr_value_tbl.extend;
1653 x_partner_attr_value_tbl(x_partner_attr_value_tbl.count)
1654 := l_entity_attr_value(x_attr_id_tbl(i)).attribute_value;
1655
1656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1657 --debug('Partner Attribute Value %%%%%%%% :'||l_entity_attr_value(x_attr_id_tbl(i)).attribute_value,FND_LOG.LEVEL_PROCEDURE);
1658 l_temp_variable := l_entity_attr_value(x_attr_id_tbl(i)).attribute_value;
1659 while (l_temp_variable is not null) loop
1660 debug('Partner Attribute Value %%%%%%%% : ' ||substr( l_temp_variable, 1, 1800 ) ,FND_LOG.LEVEL_PROCEDURE);
1661 l_temp_variable := substr( l_temp_variable, 1801 );
1662 end loop;
1663 debug('Return Type %%%%%%%%%%%%%%%%%%%% :'||l_entity_attr_value(x_attr_id_tbl(i)).return_type,FND_LOG.LEVEL_PROCEDURE);
1664
1665
1666 END IF;
1667
1668 END LOOP;
1669
1670 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1671 debug('End of quick_partner_eval_outcome',FND_LOG.LEVEL_PROCEDURE);
1672 END IF;
1673
1674 END IF;
1675 EXCEPTION
1676 WHEN FND_API.G_EXC_ERROR THEN
1677 x_return_status := FND_API.G_RET_STS_ERROR;
1678
1679 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1680 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_ERROR );
1681 END IF;
1682 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1683 p_count => x_msg_count,
1684 p_data => x_msg_data);
1685
1686 WHEN FND_API.g_exc_unexpected_error THEN
1687 x_return_status := FND_API.g_ret_sts_unexp_error;
1688
1689 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1690 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_UNEXPECTED );
1691 END IF;
1692
1693 FND_MSG_PUB.count_and_get(
1694 p_encoded => FND_API.g_false,
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698
1699 WHEN OTHERS THEN
1700 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1701 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1702 END IF;
1703
1704 x_return_status := FND_API.G_RET_STS_ERROR;
1705
1706 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1707 debug( fnd_msg_pub.get(p_encoded => 'F'),FND_LOG.LEVEL_UNEXPECTED );
1708 END IF;
1709
1710 FND_MSG_PUB.count_and_get(
1711 p_encoded => FND_API.g_false,
1712 p_count => x_msg_count,
1713 p_data => x_msg_data
1714 );
1715
1716
1717
1718 END;
1719
1720
1721
1722 --=============================================================================+
1723 --| Public Procedure |
1724 --| |
1725 --| Debug |
1726 --| |
1727 --| Parameters |
1728 --| IN |
1729 --| OUT |
1730 --| |
1731 --| |
1732 --| NOTES: |
1733 --| |
1734 --| HISTORY |
1735 --| |
1736 --==============================================================================
1737 PROCEDURE Debug(
1738 p_msg_string IN VARCHAR2,
1739 p_msg_level IN NUMBER := FND_LOG.LEVEL_STATEMENT
1740 )
1741 IS
1742 l_module_source VARCHAR2(100) := 'pv.plsql.program.rule.evaluation';
1743 BEGIN
1744
1745 IF (p_msg_level >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1746 THEN
1747 fnd_log.string(p_msg_level, l_module_source,p_msg_string);
1748 END IF;
1749 END Debug;
1750 -- =================================End of Debug================================
1751
1752 --=============================================================================+
1753 --| Public Procedure |
1754 --| |
1755 --| Set_Message |
1756 --| |
1757 --| Parameters |
1758 --| IN |
1759 --| OUT |
1760 --| |
1761 --| |
1762 --| NOTES: |
1763 --| |
1764 --| HISTORY |
1765 --| |
1766 --==============================================================================
1767 PROCEDURE Set_Message(
1768 p_msg_level IN NUMBER,
1769 p_msg_name IN VARCHAR2,
1770 p_token1 IN VARCHAR2,
1771 p_token1_value IN VARCHAR2,
1772 p_token2 IN VARCHAR2 := NULL ,
1773 p_token2_value IN VARCHAR2 := NULL,
1774 p_token3 IN VARCHAR2 := NULL,
1775 p_token3_value IN VARCHAR2 := NULL
1776 )
1777 IS
1778 BEGIN
1779 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
1780 FND_MESSAGE.Set_Name('PV', p_msg_name);
1781 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1782
1783 IF (p_token2 IS NOT NULL) THEN
1784 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1785 END IF;
1786
1787 IF (p_token3 IS NOT NULL) THEN
1788 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1789 END IF;
1790
1791 FND_MSG_PUB.Add;
1792 END IF;
1793 END Set_Message;
1794 -- ==============================End of Set_Message==============================
1795
1796 --=============================================================================+
1797 --| private function |
1798 --| |
1799 --| in_list |
1800 --| |
1801 --| Parameters |
1802 --| IN p_string |
1803 --| OUT |
1804 --| jtf_varchar2_table_32767 |
1805 --| |
1806 --| NOTES: |
1807 --| converts string to pl/sql table |
1808 --| HISTORY |
1809 --| |
1810 --==============================================================================
1811 FUNCTION in_list
1812 ( p_string IN VARCHAR2 )
1813 RETURN jtf_varchar2_table_32767
1814 as
1815 l_data jtf_varchar2_table_32767 := jtf_varchar2_table_32767();
1816 l_string long default p_string || ',';
1817 l_n number;
1818 BEGIN
1819
1820 LOOP
1821 EXIT WHEN l_string IS NULL;
1822 l_data.EXTEND;
1823 l_n := INSTR( l_string, ',' );
1824 l_data( l_data.count ) := SUBSTR( l_string, 1, l_n-1 );
1825 l_string := SUBSTR( l_string, l_n+1 );
1826 END LOOP;
1827 RETURN l_data;
1828 END;
1829 -- ==============================End of in_lisr=============================
1830
1831
1832 END PV_RULE_EVALUATION_PUB;