[Home] [Help]
PACKAGE BODY: APPS.PV_CHECK_MATCH_PUB
Source
1 package body pv_check_match_pub as
2 /* $Header: pvxvcmpb.pls 120.7 2006/09/20 09:57:09 rdsharma ship $ */
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variables */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 -- ----------------------------------------------------------------------------------
14 -- Used in the Retrieve_Token function to indicate that an index is out of bound.
15 -- ----------------------------------------------------------------------------------
16 g_out_of_bound CONSTANT VARCHAR2(20) := 'OUT OF BOUND';
17
18 -- ----------------------------------------------------------------------------------
19 -- Used by MATCH_FUE so that it can use Check_Match public function.
20 -- g_attribute_type is normally 'NORMAL'. MATCH_FUE sets it to 'MATCH_FUE' in
21 -- the beginning of its code and sets it back to 'NORMAL' after it finishes
22 -- its processing.
23 -- ----------------------------------------------------------------------------------
24 g_attribute_type VARCHAR2(30) := 'NORMAL';
25
26 -- ----------------------------------------------------------------------------------
27 -- Trap the following error:
28 -- "ORA-01006: bind variable does not exist"
29 --
30 -- This can happen when the # of bind variables specified in the USING clause is
31 -- more than the # of bind variables in the actual SQL statement.
32 -- ----------------------------------------------------------------------------------
33 g_e_no_bind_variable EXCEPTION;
34 PRAGMA EXCEPTION_INIT(g_e_no_bind_variable, -1006);
35
36
37 -- ----------------------------------------------------------------------------------
38 -- ORA-06502: PL/SQL: numeric or value error: character to number conversion error
39 -- ----------------------------------------------------------------------------------
40 g_e_numeric_conversion EXCEPTION;
41 PRAGMA EXCEPTION_INIT(g_e_numeric_conversion, -6502);
42
43
44 /*************************************************************************************/
45 /* */
46 /* */
47 /* */
48 /* private routine declaration */
49 /* */
50 /* */
51 /* */
52 /*************************************************************************************/
53 -- -----------------------------------------------------------------------------------
54 -- Provide the logic for checking if there's a match between the opportunity's
55 -- attribute value and opportunity selection's attribute value.
56 -- -----------------------------------------------------------------------------------
57 FUNCTION Check_Match_Logic(
58 p_attribute_id NUMBER,
59 p_attribute_type VARCHAR2,
60 p_operator VARCHAR2,
61 p_entity_attr_value VARCHAR2,
62 p_rule_attr_value VARCHAR2,
63 p_rule_to_attr_value VARCHAR2,
64 p_return_type VARCHAR2,
65 p_rule_currency_code VARCHAR2
66 )
67 RETURN BOOLEAN;
68 /*
69 -- -----------------------------------------------------------------------------------
70 -- Given a string that contains attribute values separated by p_delimiter, retrieve
71 -- the n th (p_index) token in the string.
72 -- e.g.
73 -- p_attr_value_string = '+++abc+++def+++'; p_delimiter = '+++'; p_index = 2.
74 -- This function will return 'def'.
75 -- If p_index is out of bound, return g_out_of_bound.
76 --
77 -- There are 2 types (p_input_type) of p_attr_value_string:
78 -- (1) +++abc+++def+++ ==> 'STD TOKEN'
79 -- (2) 1000000:::USD:::20011225164500 ==> 'In Token'
80 --
81 -- When the p_input_type is 'In Token', we will pad p_attr_value_string with
82 -- p_delimiter like the following:
83 -- :::1000000:::USD:::20011225164500:::
84 -- -----------------------------------------------------------------------------------
85 FUNCTION Retrieve_Token(
86 p_delimiter VARCHAR2,
87 p_attr_value_string VARCHAR2,
88 p_input_type VARCHAR2,
89 p_index NUMBER
90 )
91 RETURN VARCHAR2;
92
93 -- -----------------------------------------------------------------------------------
94 -- Given a string, p_string, search for the number of tokens separated by the
95 -- delimiter, p_delimiter.
96 --
97 -- e.g.
98 -- p_string = '+++abc+++def+++ghi+++'
99 -- p_delimiter = '+++'
100 -- The function will return 3 because there are 3 tokens in the string.
101 -- -----------------------------------------------------------------------------------
102 FUNCTION Get_Num_Of_Tokens (
103 p_delimiter VARCHAR2,
104 p_string VARCHAR2
105 )
106 RETURN NUMBER;
107 */
108
109 -- -----------------------------------------------------------------------------------
110 -- This function is used when the attribute_id = 1 which is FUE (Functional
111 -- Expertise). It takes the attribute value of an FUE attribute and return the
112 -- expanded version of it separated by p_delimiter.
113 -- e.g.
114 -- If p_attr_value = 'SW/App/CRM', the return string would be:
115 -- +++SW+++SW/App+++SW/App/CRM+++
116 -- -----------------------------------------------------------------------------------
117 FUNCTION Expand_FUE_Values (
118 p_attr_value VARCHAR2,
119 p_delimiter VARCHAR2,
120 p_additional_token VARCHAR2 DEFAULT null
121 )
122 RETURN VARCHAR2;
123
124
125 -- -----------------------------------------------------------------------------------
126 -- This function is used when the attribute is purchase_amount or purchase_quantity.
127 -- It takes an attribute value and returns the expanded version of it separated by
128 -- p_delimiter.
129 -- e.g.
130 -- SW/APP:10000:USD:20020115142534
131 -- (Product Category:::Line Amount:::Currency Code:::Currency Date)
132 --
133 -- Will get expanded into:
134 -- +++SW:10000:USD:20020115142534+++SW/APP:10000:USD:20020115142534+++
135 -- -----------------------------------------------------------------------------------
136 FUNCTION Process_Purchase_Amt_Qty (
137 p_attribute_id IN VARCHAR2,
138 p_attr_value IN VARCHAR2,
139 p_delimiter IN VARCHAR2
140 )
141 RETURN VARCHAR2;
142
143 -- -----------------------------------------------------------------------------------
144 --
145 -- -----------------------------------------------------------------------------------
146 FUNCTION Match_FUE (
147 p_attribute_id IN NUMBER,
148 p_entity_attr_value IN VARCHAR2,
149 p_rule_attr_value IN VARCHAR2,
150 p_rule_to_attr_value IN VARCHAR2,
151 p_operator IN VARCHAR2,
152 p_input_filter IN t_input_filter,
153 p_delimiter IN VARCHAR2,
154 p_return_type IN VARCHAR2,
155 p_rule_currency_code IN VARCHAR2
156 )
157 RETURN BOOLEAN;
158
159
160 -- -----------------------------------------------------------------------------------
161 -- Use for inserting output messages to the message table.
162 -- -----------------------------------------------------------------------------------
163 PROCEDURE Debug(
164 p_msg_string IN VARCHAR2
165 );
166
167 PROCEDURE Set_Message(
168 p_msg_level IN NUMBER,
169 p_msg_name IN VARCHAR2,
170 p_token1 IN VARCHAR2,
171 p_token1_value IN VARCHAR2,
172 p_token2 IN VARCHAR2 := NULL ,
173 p_token2_value IN VARCHAR2 := NULL,
174 p_token3 IN VARCHAR2 := NULL,
175 p_token3_value IN VARCHAR2 := NULL
176 );
177
178
179 /*************************************************************************************/
180 /* */
181 /* */
182 /* */
183 /* public routines */
184 /* */
185 /* */
186 /* */
187 /*************************************************************************************/
188
189
190
191 --=============================================================================+
192 --| Procedure |
193 --| |
194 --| Check_Match |
195 --| |
196 --| |
197 --| Parameters |
198 --| IN |
199 --| OUT |
200 --| |
201 --| |
202 --| NOTES |
203 --| |
204 --| HISTORY |
205 --| |
206 --==============================================================================
207 FUNCTION Check_Match (
208 p_attribute_id IN NUMBER,
209 p_entity IN VARCHAR2,
210 p_entity_id IN NUMBER,
211 p_rule_attr_value IN VARCHAR2,
212 p_rule_to_attr_value IN VARCHAR2,
213 p_operator IN VARCHAR2,
214 p_input_filter IN t_input_filter,
215 p_delimiter IN VARCHAR2,
216 p_rule_currency_code IN VARCHAR2,
217 x_entity_attr_value IN OUT NOCOPY t_entity_attr_value
218 )
219 RETURN BOOLEAN
220 IS
221 l_matched BOOLEAN := FALSE;
222 l_entity_attr_value VARCHAR2(4000);
223 l_return_type VARCHAR2(30);
224 l_return_status VARCHAR2(30);
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2(1000);
227 l_api_name VARCHAR2(30) := 'Check_Match';
228
229 BEGIN
230 -- ---------------------------------------------------------------------
231 -- Get entity's attribute value if it doesn't already exist.
232 -- ---------------------------------------------------------------------
233 Get_Entity_Attr_Values (
234 p_api_version_number => 1.0,
235 p_attribute_id => p_attribute_id,
236 p_entity => p_entity,
237 p_entity_id => p_entity_id,
238 p_delimiter => p_delimiter,
239 x_entity_attr_value => x_entity_attr_value,
240 x_return_status => l_return_status,
241 x_msg_count => l_msg_count,
242 x_msg_data => l_msg_data
243 );
244
245 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
246 RAISE FND_API.G_EXC_ERROR;
247
248 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
249 RAISE FND_API.g_exc_unexpected_error;
250 END IF;
251
252 IF (NOT x_entity_attr_value.EXISTS(p_attribute_id)) THEN
253 RETURN FALSE;
254 END IF;
255
256 l_entity_attr_value := x_entity_attr_value(p_attribute_id).attribute_value;
257 l_return_type := x_entity_attr_value(p_attribute_id).return_type;
258
259 -- ---------------------------------------------------------------------
260 -- Call the overloading Check_Match to perform attribute value matching.
261 -- ---------------------------------------------------------------------
262 l_matched := Check_Match(
263 p_attribute_id => p_attribute_id,
264 p_entity_attr_value => l_entity_attr_value,
265 p_rule_attr_value => p_rule_attr_value,
266 p_rule_to_attr_value => p_rule_to_attr_value,
267 p_operator => p_operator,
268 p_input_filter => p_input_filter,
269 p_delimiter => p_delimiter,
270 p_return_type => l_return_type,
271 p_rule_currency_code => p_rule_currency_code
272 );
273
274 RETURN l_matched;
275
276 EXCEPTION
277 WHEN FND_API.G_EXC_ERROR THEN
278 RETURN FALSE;
279 --RAISE;
280
281 WHEN FND_API.g_exc_unexpected_error THEN
282 RETURN FALSE;
283 -- RAISE;
284
285 WHEN OTHERS THEN
286 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
287 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
288 END IF;
289
290 --RETURN FALSE;
291 RAISE;
292 END Check_Match;
293 -- ==============================Check_Match====================================
294
295
296 --=============================================================================+
297 --| Procedure |
298 --| |
299 --| Check_Match |
300 --| |
301 --| |
302 --| Parameters |
303 --| IN |
304 --| OUT |
305 --| |
306 --| |
307 --| NOTES |
308 --| |
309 --| HISTORY |
310 --| |
311 --==============================================================================
312 FUNCTION Check_Match (
313 p_attribute_id IN NUMBER,
314 p_entity_attr_value IN VARCHAR2,
315 p_rule_attr_value IN VARCHAR2,
316 p_rule_to_attr_value IN VARCHAR2,
317 p_operator IN VARCHAR2,
318 p_input_filter IN t_input_filter,
319 p_delimiter IN VARCHAR2,
320 p_return_type IN VARCHAR2,
321 p_rule_currency_code IN VARCHAR2
322 )
323 RETURN BOOLEAN
324 IS
325 l_api_name VARCHAR2(30) := 'Check_Match';
326 l_counter NUMBER;
327 l_outer_counter NUMBER;
328 l_matched BOOLEAN;
329 l_is_matched BOOLEAN;
330 l_num_of_tokens NUMBER;
331 l_num_of_to_tokens NUMBER;
332 l_num_of_entity_tokens NUMBER;
333 l_matching_tokens NUMBER;
334 l_rule_attr_value VARCHAR2(4000);
335 l_rule_attr_value_temp VARCHAR2(4000) := p_rule_attr_value;
336 l_rule_to_attr_value VARCHAR2(4000);
337 l_entity_attr_value VARCHAR2(4000);
338 l_entity_attr_value_temp VARCHAR2(4000) := p_entity_attr_value;
339 l_attribute_type VARCHAR2(30) := 'NORMAL';
340 l_FUE_matched BOOLEAN;
341
342 l_operator VARCHAR2(50);
343 l_stop_flag BOOLEAN := FALSE;
344
345 BEGIN
346 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
347 Debug('Inside Check_Match =====================================');
348 Debug('Operator is: ' || p_operator);
349 Debug('Entity Attribute Value (80 chars): ' || substr(p_entity_attr_value,1,80));
350 Debug('Rule Attribute Value (80 chars): ' || substr(p_rule_attr_value,1,80));
351 END IF;
352
353 IF (p_rule_attr_value IS NULL) THEN
354 l_rule_attr_value := p_delimiter || p_delimiter;
355 l_rule_attr_value_temp := l_rule_attr_value;
356 END IF;
357
358 IF (p_entity_attr_value IS NULL) THEN
359 l_entity_attr_value_temp := p_delimiter || p_delimiter;
360 END IF;
361
362 -- ---------------------------------------------------------------------------
363 -- Process Input Filter if it exists.
364 -- ---------------------------------------------------------------------------
365 /*
366 IF (p_attribute_id IN (g_purchase_amount_attr_id, g_purchase_quantity_attr_id,
367 g_PSS_amount_attr_id, g_pss_quantity_attr_id))
368 THEN
369 */
370 IF (p_attribute_id IN (g_a_Purchase_Amount_Product, g_a_Purchase_Quantity_Product,
371 g_a_Purchase_Amount_Solutions, g_a_Purchase_Qty_Solutions))
372 THEN
373 -- ------------------------------------------------------------------------
374 -- If there is no input filter for this purchase amount, this is
375 -- considered a LINE_AMOUNT, which means that we do not need to match
376 -- purchase amount by product categories.
377 -- ------------------------------------------------------------------------
378 IF (p_input_filter IS NULL OR p_input_filter.COUNT = 0) THEN
379 l_attribute_type := 'LINE_AMOUNT';
380 ELSE
381 -- l_attribute_type := 'FILTER_AMOUNT';
382
383 -- ---------------------------------------------------------------------
384 -- Process input filter and identify for a match between the product
385 -- interest (FUE) of the input filter and that of the entity lines.
386 --
387 -- If there's no match at all, we don't need to evaluate this entity
388 -- attribute value. We will immediately return FALSE (no match).
389 -- If there's a match in FUE, go on to evaluate the match between
390 -- the purchase amount.
391 -- ---------------------------------------------------------------------
392 l_FUE_matched := Match_FUE(p_attribute_id => p_attribute_id,
393 p_entity_attr_value => l_entity_attr_value_temp,
394 p_rule_attr_value => l_rule_attr_value_temp,
395 p_rule_to_attr_value => p_rule_to_attr_value,
396 p_operator => p_operator,
397 p_input_filter => p_input_filter,
398 p_delimiter => p_delimiter,
399 p_return_type => p_return_type,
400 p_rule_currency_code => p_rule_currency_code);
401
402 IF (l_FUE_matched) THEN
403 RETURN TRUE;
404 ELSE
405 RETURN FALSE;
406 END IF;
407 END IF;
408
409 ELSIF (p_attribute_id = g_dummy_attr_id) THEN
410 l_attribute_type := 'FILTER_AMOUNT';
411 END IF;
412
413 l_num_of_tokens := Get_Num_Of_Tokens(p_delimiter, l_rule_attr_value_temp);
414
415 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
416 Debug('# of Rule Tokens: ' || l_num_of_tokens);
417 END IF;
418
419 IF (UPPER(p_operator) = 'BETWEEN') THEN
420 l_num_of_to_tokens := Get_Num_Of_Tokens(p_delimiter, p_rule_to_attr_value);
421
422 IF (l_num_of_tokens <> l_num_of_to_tokens) THEN
423 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
424 p_msg_name => 'PV_UNMATCHED_TOKEN_NUMBER',
425 p_token1 => 'attribute_value string',
426 p_token1_value => l_rule_attr_value_temp,
427 p_token2 => 'attribute_to_value string',
428 p_token2_value => p_rule_to_attr_value);
429
430 RAISE FND_API.G_EXC_ERROR;
431 END IF;
432 END IF;
433
434
435 l_outer_counter := 1;
436
437 -- -----------------------------------------------------------------------
438 -- 'NOT EQUAL' has a different logic. For token strings like
439 -- '+++US+++UK+++', the engine usually treats this as OR logic.
440 -- In the case of 'NOT EQUAL', however, the engine will treat it as
441 -- 'NOT IN'.
442 --
443 -- This means that when '+++US+++' is not equal to '+++US+++UK+++',
444 -- the engine evaluates the condition to be FALSE. Under OR logic, it
445 -- would have been evaluated to TRUE since 'US' is not equal to 'UK'
446 -- is TRUE.
447 --
448 -- This change was made for bug #3374554 for 11.5.10.
449 -- -----------------------------------------------------------------------
450 IF (p_operator = g_not_equal) THEN
451 l_matched := TRUE;
452 ELSE
453 l_matched := FALSE;
454 END IF;
455
456
457 FOR i IN 1..l_num_of_tokens LOOP
458 l_rule_attr_value := Retrieve_Token (
459 p_delimiter => p_delimiter,
460 p_attr_value_string => l_rule_attr_value_temp,
461 p_input_type => 'STD TOKEN',
462 p_index => i
463 );
464
465 IF (UPPER(p_operator) = 'BETWEEN') THEN
466 l_rule_to_attr_value := Retrieve_Token (
467 p_delimiter => p_delimiter,
468 p_attr_value_string => p_rule_to_attr_value,
469 p_input_type => 'STD TOKEN',
470 p_index => i
471 );
472
473 IF (UPPER(l_rule_to_attr_value) = 'NULL') THEN
474 l_rule_to_attr_value := NULL;
475 END IF;
476 END IF;
477
478 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
479 Debug('Rule Token #' || i || '(80 chars): ' || substr(l_rule_attr_value,1,80));
480 Debug('Operator: ' || p_operator);
481 END IF;
482
483 l_num_of_entity_tokens := Get_Num_Of_Tokens(p_delimiter, l_entity_attr_value_temp);
484
485
486 -- -------------------------------------------------------------------------------
487 -- Inner Loop
488 -- -------------------------------------------------------------------------------
489 FOR j IN 1..l_num_of_entity_tokens LOOP
490 l_entity_attr_value := Retrieve_Token (
491 p_delimiter => p_delimiter,
492 p_attr_value_string => l_entity_attr_value_temp,
493 p_input_type => 'STD TOKEN',
494 p_index => j
495 );
496
497 IF (p_operator = g_not_equal) THEN
498 l_operator := g_equal;
499 ELSE
500 l_operator := p_operator;
501 END IF;
502
503 l_is_matched := Check_Match_Logic (
504 p_attribute_id => p_attribute_id,
505 p_attribute_type => l_attribute_type,
506 p_operator => l_operator,
507 p_entity_attr_value => l_entity_attr_value,
508 p_rule_attr_value => l_rule_attr_value,
509 p_rule_to_attr_value => l_rule_to_attr_value,
510 p_return_type => p_return_type,
511 p_rule_currency_code => p_rule_currency_code
512 );
513
514 IF (l_is_matched) THEN
515 IF (p_operator = g_not_equal) THEN
516 l_matched := FALSE;
517 ELSE
518 l_matched := TRUE;
519 END IF;
520
521 l_stop_flag := TRUE;
522 EXIT;
523 END IF;
524 END LOOP; -- ------------------------ Inner Loop --------------------------
525
526 IF (l_stop_flag) THEN
527 EXIT;
528 END IF;
529 END LOOP; -- ------------------------ Outer Loop -----------------------------
530
531 RETURN l_matched;
532
533
534 -------------------- Exception --------------------------
535 EXCEPTION
536 WHEN FND_API.G_EXC_ERROR THEN
537 RETURN FALSE;
538 --RAISE;
539
540 WHEN FND_API.g_exc_unexpected_error THEN
541 RETURN FALSE;
542 --RAISE;
543
544 WHEN OTHERS THEN
545 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
546 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
547 END IF;
548
549 --RETURN FALSE;
550 RAISE;
551 END Check_Match;
552 -- ==============================Check_Match====================================
553
554
555 --=============================================================================+
556 --| Procedure |
557 --| |
558 --| Get_Entity_Attr_Values |
559 --| |
560 --| |
561 --| Parameters |
562 --| IN |
563 --| OUT |
564 --| |
565 --| |
566 --| NOTES |
567 --| |
568 --| HISTORY |
569 --| |
570 --==============================================================================
571 PROCEDURE Get_Entity_Attr_Values (
572 p_api_version_number IN NUMBER,
573 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
574 p_commit IN VARCHAR2 := FND_API.G_FALSE,
575 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
576 p_attribute_id IN NUMBER,
577 p_entity IN VARCHAR2,
578 p_entity_id IN NUMBER,
579 p_delimiter IN VARCHAR2,
580 p_expand_attr_flag IN VARCHAR2 := 'Y',
581 x_entity_attr_value IN OUT NOCOPY t_entity_attr_value,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2
585 )
586 IS
587 TYPE c_attr_type IS REF CURSOR;
588
589 l_api_version CONSTANT NUMBER := 1.0;
590 l_api_name CONSTANT VARCHAR2(30) := 'Get_Entity_Attr_Values';
591
592 lc_attr_cursor c_attr_type;
593 l_sql_text VARCHAR2(2000);
594 l_return_type VARCHAR2(30);
595 l_attribute_type VARCHAR2(30);
596 l_output JTF_VARCHAR2_TABLE_4000;
597 l_first_record BOOLEAN := TRUE;
598 l_attr_value VARCHAR2(32000);
599 l_msg_string VARCHAR2(4000);
600 l_num_of_tokens NUMBER;
601 l_token_value VARCHAR2(2000);
602
603 -- -----------------------------------------------------------------
604 -- q1.entity
605 -- 'LEAD' = opportunity
606 -- 'SALES_LEAD' = lead
607 -- -----------------------------------------------------------------
608 CURSOR lc_sql_cursor IS
609 SELECT q1.sql_text, q2.return_type, q2.attribute_type
610 FROM pv_entity_attrs q1,
611 pv_attributes_vl q2
612 WHERE q1.sql_text IS NOT NULL AND
613 q1.entity = UPPER(p_entity) AND
614 q1.attribute_id = q2.attribute_id AND
615 q1.enabled_flag = 'Y' AND
616 q2.enabled_flag = 'Y' AND
617 q1.attribute_id = p_attribute_id;
618
619 BEGIN
620 -------------------- initialize -------------------------
621 IF FND_API.to_boolean(p_init_msg_list) THEN
622 FND_MSG_PUB.initialize;
623 END IF;
624
625 IF NOT FND_API.compatible_api_call(
626 l_api_version,
627 p_api_version_number,
628 l_api_name,
629 g_pkg_name
630 ) THEN
631 RAISE FND_API.g_exc_unexpected_error;
632 END IF;
633
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 -------------------------- Source code --------------------
637
638 -- --------------------------------------------------------------------------
639 -- If the attribute entry exists, but the return_type is NULL, retrieve
640 -- the return_type from pv_attributes_vl.
641 -- --------------------------------------------------------------------------
642 IF (x_entity_attr_value.EXISTS(p_attribute_id)) THEN
643 IF (x_entity_attr_value(p_attribute_id).return_type IS NULL) THEN
644 SELECT return_type
645 INTO x_entity_attr_value(p_attribute_id).return_type
646 FROM pv_attributes_vl
647 WHERE attribute_id = p_attribute_id;
648 END IF;
649
650 -- --------------------------------------------------------------------------
651 -- If this attribute is not cached (in PL/SQL table). Retrieve its
652 -- attribute value(s) and cache it.
653 -- --------------------------------------------------------------------------
654 ELSE
655 -- -----------------------------------------------------------------------
656 -- Retrieve SQL Program used for retrieving attribute value(s).
657 -- It may not be efficient to retrieve the same sql_text every time.
658 -- We may be able to cache the sql_text much the same way we cache
659 -- entity's attribute value.
660 --
661 -- Note that there will only be one row returned from the following
662 -- cursor. However, still need to use FOR LOOP to get around the
663 -- following Oracle error:
664 -- ORA-00600: internal error code, arguments: [12261]
665 --
666 -- THis is a bug that's fixed in 8.172.
667 -- -----------------------------------------------------------------------
668 FOR v_sql_cursor IN lc_sql_cursor LOOP
669 l_sql_text := v_sql_cursor.sql_text;
670 l_return_type := v_sql_cursor.return_type;
671 l_attribute_type := v_sql_cursor.attribute_type;
672 END LOOP;
673
674 --Debug('l_return_type: ' || l_return_type);
675 --Debug('l_attribute_type: ' || l_attribute_type);
676 --Debug('SQL TEXT:::' );
677 --Debug(l_sql_text);
678
679 IF (l_sql_text IS NULL OR LENGTH(l_sql_text) = 0) THEN
680 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
681 p_msg_name => 'PV_ABSENT_SQL_TEXT',
682 p_token1 => 'TEXT',
683 p_token1_value => 'There is no SQL TEXT for this attribute: ' || p_attribute_id,
684 p_token2 => 'TEXT',
685 p_token2_value => 'Entity Type: ' || p_entity);
686
687 RAISE FND_API.G_EXC_ERROR;
688 END IF;
689
690 --BEGIN
691 -- -------------------------------------------------------------------------
692 -- Execute SQL_TEXT only if there is something in sql_text.
693 -- -------------------------------------------------------------------------
694 IF (l_sql_text IS NOT NULL OR LENGTH(l_sql_text) <> 0) THEN
695 -- =================================================
696 -- Handling 'FUNCTION' (program/derived) attributes.
697 -- =================================================
698 IF (l_attribute_type = 'FUNCTION') THEN
699 l_sql_text := 'BEGIN ' || l_sql_text || '; END;';
700 EXECUTE IMMEDIATE l_sql_text USING p_entity_id, OUT l_output;
701
702 FOR i IN 1..l_output.COUNT LOOP
703 IF (l_first_record) THEN
704 x_entity_attr_value(p_attribute_id).return_type := l_return_type;
705 x_entity_attr_value(p_attribute_id).attribute_value := p_delimiter;
706 l_first_record := FALSE;
707 END IF;
708
709 x_entity_attr_value(p_attribute_id).attribute_value :=
710 x_entity_attr_value(p_attribute_id).attribute_value ||
711 RTRIM(LTRIM(l_output(i))) || p_delimiter;
712
713 END LOOP;
714
715
716 -- =================================================
717 -- All other attributes (non-function attributes).
718 -- =================================================
719 ELSE -- =============Begin Processing non-function attributes============
720 OPEN lc_attr_cursor FOR l_sql_text USING p_attribute_id, p_entity, p_entity_id;
721
722 -- ------------------------------------------------------------------------
723 -- Note this will not eliminate duplicate values. Do we need to de-dup?
724 -- ------------------------------------------------------------------------
725 LOOP
726 FETCH lc_attr_cursor INTO l_attr_value;
727 EXIT WHEN lc_attr_cursor%NOTFOUND;
728
729 --Debug('************Original Attribute Value: ' || l_attr_value);
730
731 -- --------------------------------------------------------------------
732 -- Check the attribute value returned. If it contains multiple tokens
733 -- as in the case of currency and purchase amount attributes, make
734 -- sure there are no tokens with NULL values.
735 -- e.g.
736 --
737 -- 2 tokens:
738 -- SW/APP:::10 is ok,
739 -- SW/APP::: is also ok,
740 -- :::10 is not. Throws an error.
741 --
742 -- 3 tokens:
743 -- 100000:::USD:::20020103145100 is ok, but
744 -- 100000::::::20020103145100 has a missing currency_code.
745 -- :::USD:::20020103145100 has a missing amount, but we will
746 -- not raise an exception for this. Set the
747 -- entire string to NULL.
748 -- ::::::20020103145100 the amount is null, don't raise the
749 -- exception even when other tokens are null.
750 --
751 -- 4 tokens:
752 -- SW/APP:::10000:::USD:::20020123101600 is ok, but
753 -- SW/APP:::10000::::::20020123101600 not ok.
754 -- SW/APP:::10000:::USD::: not ok.
755 -- SW/APP::::::USD:::20020123101600 is ok. leaves it the way it is.
756 -- :::10000:::USD:::20020123101600 technically, impossible, but throw
757 -- an error it this case.
758 --
759 -- Raise an exception when any of the tokens are missing.
760 --
761 -- When the number of tokens is 0, it means one of the two possible
762 -- things:
763 -- (1). The attribute value is NULL as in the case of '::::::'.
764 -- (2). The attribute is not a multi-token attribute.
765 -- --------------------------------------------------------------------
766 IF (INSTR(l_attr_value, g_token_delimiter) > 0) THEN
767 l_num_of_tokens := (LENGTH(l_attr_value) -
768 LENGTH(REPLACE(l_attr_value, g_token_delimiter, '')))
769 /LENGTH(g_token_delimiter)
770 + 1;
771
772 -- ..................................................................
773 -- 3 tokens
774 -- ..................................................................
775 IF (l_num_of_tokens = 3) THEN
776 l_token_value := Retrieve_Token (
777 p_delimiter => g_token_delimiter,
778 p_attr_value_string => l_attr_value,
779 p_input_type => 'IN TOKEN',
780 p_index => 1
781 );
782
783 IF (l_token_value IS NULL) THEN
784 l_attr_value := NULL;
785
786 ELSE
787 l_token_value := Retrieve_Token (
788 p_delimiter => g_token_delimiter,
789 p_attr_value_string => l_attr_value,
790 p_input_type => 'IN TOKEN',
791 p_index => 2
792 );
793
794 IF (l_token_value IS NOT NULL) THEN
795 l_token_value := Retrieve_Token (
796 p_delimiter => g_token_delimiter,
797 p_attr_value_string => l_attr_value,
798 p_input_type => 'IN TOKEN',
799 p_index => 3
800 );
801 END IF;
802
803 IF (l_token_value IS NULL) THEN
804 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
805 Debug('Null Token Found in: ' || l_attr_value);
806 END IF;
807
808 fnd_message.SET_NAME('PV', 'PV_NULL_TOKEN');
809 fnd_msg_pub.ADD;
810 RAISE FND_API.G_EXC_ERROR;
811
812 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
813 p_msg_name => 'PV_NULL_TOKEN',
814 p_token1 => 'Attribute Value',
815 p_token1_value => l_attr_value);
816
817 RAISE FND_API.G_EXC_ERROR;
818 END IF;
819 END IF;
820
821 -- ..................................................................
822 -- 2 tokens
823 -- ..................................................................
824 ELSIF (l_num_of_tokens = 2) THEN
825 l_token_value := Retrieve_Token (
826 p_delimiter => g_token_delimiter,
827 p_attr_value_string => l_attr_value,
828 p_input_type => 'IN TOKEN',
829 p_index => 1
830 );
831
832 IF (l_token_value IS NULL) THEN
833 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
834 Debug('Null Token Found in: ' || l_attr_value);
835 END IF;
836
837 fnd_message.SET_NAME('PV', 'PV_NULL_TOKEN');
838 fnd_msg_pub.ADD;
839 RAISE FND_API.G_EXC_ERROR;
840
841 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
842 p_msg_name => 'PV_NULL_TOKEN',
843 p_token1 => 'Attribute Value',
844 p_token1_value => l_attr_value);
845
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848
849 -- ..................................................................
850 -- 4 tokens
851 -- ..................................................................
852 ELSIF (l_num_of_tokens = 4) THEN
853 l_token_value := Retrieve_Token (
854 p_delimiter => g_token_delimiter,
855 p_attr_value_string => l_attr_value,
856 p_input_type => 'IN TOKEN',
857 p_index => 1
858 );
859
860 IF (l_token_value IS NOT NULL) THEN
861 l_token_value := Retrieve_Token (
862 p_delimiter => g_token_delimiter,
863 p_attr_value_string => l_attr_value,
864 p_input_type => 'IN TOKEN',
865 p_index => 3
866 );
867
868 IF (l_token_value IS NOT NULL) THEN
869 l_token_value := Retrieve_Token (
870 p_delimiter => g_token_delimiter,
871 p_attr_value_string => l_attr_value,
872 p_input_type => 'IN TOKEN',
873 p_index => 4
874 );
875 END IF;
876 END IF;
877
878 IF (l_token_value IS NULL) THEN
879 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
880 Debug('Null Token Found in: ' || l_attr_value);
881 END IF;
882
883 fnd_message.SET_NAME('PV', 'PV_NULL_TOKEN');
884 fnd_msg_pub.ADD;
885 RAISE FND_API.G_EXC_ERROR;
886
887 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
888 p_msg_name => 'PV_NULL_TOKEN',
889 p_token1 => 'Attribute Value',
890 p_token1_value => l_attr_value);
891
892 RAISE FND_API.G_EXC_ERROR;
893 END IF;
894 END IF;
895 END IF;
896
897
898 -- --------------------------------------------------------------------
899 -- Pass NULL token check. Start processing/concatenating the retrieved
900 -- attribute value if necessary.
901 -- --------------------------------------------------------------------
902 IF (l_first_record) THEN
903 x_entity_attr_value(p_attribute_id).return_type := l_return_type;
904 x_entity_attr_value(p_attribute_id).attribute_value := p_delimiter;
905 l_first_record := FALSE;
906 END IF;
907
908 -- --------------------------------------------------------------------
909 -- If attribute_id is 1 (which is FUE), we need to expand the
910 -- attribute value as follows:
911 -- e.g. if the attribute value retrieved for FUE is
912 -- SW/APP/CRM, we
913 -- will expand this so that the result will become:
914 -- SW, SW/APP, SW/APP/CRM. Therefore, when attribute_id is 1,
915 -- the attribute value will be a concatenated string
916 -- separted by a delimiter:
917 -- +++SW+++SW/APP+++SW/APP/CRM+++
918 -- --------------------------------------------------------------------
919 -- IF (p_attribute_id = g_FUE_attr_id) THEN
920 IF (p_attribute_id IN (g_a_FUE, g_a_Product_Interest) AND
921 p_expand_attr_flag = 'Y')
922 THEN
923 x_entity_attr_value(p_attribute_id).attribute_value :=
924 x_entity_attr_value(p_attribute_id).attribute_value ||
925 Expand_FUE_Values(
926 p_attr_value => RTRIM(LTRIM(l_attr_value)),
927 p_delimiter => p_delimiter,
928 p_additional_token => null);
929
930 -- --------------------------------------------------------------------
931 -- If the attribute is "Purchase Amount/Quantity", the attribute value
932 -- will be expanded as follows:
933 --
934 -- e.g.
935 --
936 -- Suppose that the sql_text returns something like this:
937 -- SW/APP:10000:USD:20020123101600
938 -- (Product Category:Line Amount:Currency Code:Currency Date)
939 --
940 -- This string will be expanded into:
941 -- +++SW:10000:USD:20020123101600+++SW/APP:10000:USD:20020123101600+++
942 -- --------------------------------------------------------------------
943 ELSIF (p_attribute_id IN
944 (g_a_Purchase_Amount_Product, g_a_Purchase_Quantity_Product))
945 THEN
946 x_entity_attr_value(p_attribute_id).attribute_value :=
947 x_entity_attr_value(p_attribute_id).attribute_value ||
948 Process_Purchase_Amt_Qty(p_attribute_id,
949 RTRIM(LTRIM(l_attr_value)),
950 p_delimiter);
951
952 -- --------------------------------------------------------------------
953 -- All the other attributes will simply have each of the
954 -- attribute values concatenated and separated by a delimiter.
955 -- --------------------------------------------------------------------
956 ELSE
957 x_entity_attr_value(p_attribute_id).attribute_value :=
958 x_entity_attr_value(p_attribute_id).attribute_value ||
959 RTRIM(LTRIM(l_attr_value)) || p_delimiter;
960 END IF;
961 END LOOP;
962
963 CLOSE lc_attr_cursor;
964
965 END IF; -- =============End Processing non-function attributes============
966
967 END IF;
968
969 -- -----------------------------------------------------------------------------------
970 -- Even if there are no attribute value returned for this attribute, we still want
971 -- to populate x_entity_attr_value with a null value so the caller of this API
972 -- will be able to use this to compare to a rule's attribute value in the event
973 -- that the operator is IS_NULL or IS_NOT_NULL.
974 -- -----------------------------------------------------------------------------------
975 IF (NOT x_entity_attr_value.EXISTS(p_attribute_id) OR
976 x_entity_attr_value(p_attribute_id).attribute_value IS NULL)
977 THEN
978 x_entity_attr_value(p_attribute_id).attribute_value := p_delimiter || p_delimiter;
979 x_entity_attr_value(p_attribute_id).return_type := l_return_type;
980 END IF;
981
982 /*-------------------------------------------------------------------------
983 EXCEPTION
984 WHEN g_e_no_bind_variable THEN
985 Debug('This SQL Text does not the right numbers of bind variables: ' );
986 Debug(l_sql_text);
987
988 WHEN others THEN
989 Debug(SQLCODE || ': ' || SQLERRM);
990 END; -- End of the BEGIN-END.
991 *-------------------------------------------------------------------------*/
992 END IF;
993
994 -- -------------------------------------------------------------
995 -- Debug Message.
996 -- -------------------------------------------------------------
997 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
998 Debug('********************* Get_Entity_Attr_Values ******************');
999 l_msg_string := 'Attr Value: ' || x_entity_attr_value(p_attribute_id).attribute_value;
1000 -- Debug(l_msg_string);
1001 Debug('Return Type: ' || x_entity_attr_value(p_attribute_id).return_type);
1002 Debug('********************************************************************');
1003 END IF;
1004
1005 -------------------- finish --------------------------
1006 FND_MSG_PUB.count_and_get(
1007 p_encoded => FND_API.g_false,
1008 p_count => x_msg_count,
1009 p_data => x_msg_data
1010 );
1011
1012 -------------------- Exception --------------------------
1013 EXCEPTION
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1016 p_msg_name => 'PV_DEBUG_MSG',
1017 p_token1 => 'TEXT',
1018 p_token1_value => 'Exception raised while evaluating attribute ID: ' || p_attribute_id,
1019 p_token2 => null,
1020 p_token2_value => null);
1021
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1024 p_count => x_msg_count,
1025 p_data => x_msg_data);
1026
1027 RAISE;
1028
1029 WHEN FND_API.g_exc_unexpected_error THEN
1030 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1031 p_msg_name => 'PV_DEBUG_MSG',
1032 p_token1 => 'TEXT',
1033 p_token1_value => 'Exception raised while evaluating attribute ID: ' || p_attribute_id,
1034 p_token2 => null,
1035 p_token2_value => null);
1036
1037 x_return_status := FND_API.g_ret_sts_unexp_error;
1038 FND_MSG_PUB.count_and_get(
1039 p_encoded => FND_API.g_false,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data
1042 );
1043
1044 RAISE;
1045
1046 WHEN g_e_no_bind_variable THEN
1047 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1048 Debug('This SQL Text does not have the right numbers of bind variables: ' );
1049 Debug(l_sql_text);
1050 END IF;
1051
1052 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1053 p_msg_name => 'PV_DEBUG_MSG',
1054 p_token1 => 'TEXT',
1055 p_token1_value => 'Exception raised while evaluating attribute ID: ' || p_attribute_id,
1056 p_token2 => null,
1057 p_token2_value => null);
1058
1059
1060 x_return_status := FND_API.g_ret_sts_unexp_error;
1061 FND_MSG_PUB.count_and_get(
1062 p_encoded => FND_API.g_false,
1063 p_count => x_msg_count,
1064 p_data => x_msg_data
1065 );
1066
1067 RAISE;
1068
1069 WHEN OTHERS THEN
1070 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1071 p_msg_name => 'PV_DEBUG_MSG',
1072 p_token1 => 'TEXT',
1073 p_token1_value => 'Exception raised while evaluating attribute ID: ' || p_attribute_id,
1074 p_token2 => null,
1075 p_token2_value => null);
1076
1077 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1078 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1079 END IF;
1080
1081 x_return_status := FND_API.g_ret_sts_unexp_error;
1082 FND_MSG_PUB.count_and_get(
1083 p_encoded => FND_API.g_false,
1084 p_count => x_msg_count,
1085 p_data => x_msg_data
1086 );
1087
1088 RAISE;
1089
1090
1091 END Get_Entity_Attr_Values;
1092 -- ========================End of Get_Entity_Attr_Values=======================
1093
1094
1095
1096 --=============================================================================+
1097 --| Public Procedure |
1098 --| |
1099 --| Retrieve_Input_Filter |
1100 --| |
1101 --| Parameters |
1102 --| IN |
1103 --| OUT |
1104 --| |
1105 --| |
1106 --| NOTES: |
1107 --| |
1108 --| HISTORY |
1109 --| |
1110 --==============================================================================
1111 PROCEDURE Retrieve_Input_Filter (
1112 p_api_version_number IN NUMBER,
1113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1114 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1116 p_process_rule_id IN NUMBER,
1117 p_delimiter IN VARCHAR2 := '+++',
1118 x_input_filter IN OUT NOCOPY t_input_filter,
1119 x_return_status OUT NOCOPY VARCHAR2,
1120 x_msg_count OUT NOCOPY NUMBER,
1121 x_msg_data OUT NOCOPY VARCHAR2
1122 )
1123 IS
1124 -- -------------------------------------------------------------------
1125 -- Only retrieve necessary input filter components (FUE and PSS)
1126 -- -------------------------------------------------------------------
1127 CURSOR lc_input_filter IS
1128 SELECT a.attribute_id, a.selection_criteria_id, b.attribute_value
1129 FROM pv_enty_select_criteria a, pv_selected_attr_values b
1130 WHERE a.selection_criteria_id = b.selection_criteria_id AND
1131 a.process_rule_id = p_process_rule_id AND
1132 --a.attribute_id IN (g_FUE_attr_id, g_PSS_attr_id) AND
1133 a.attribute_id IN (g_a_Product_Interest, g_a_FUE, g_a_PSS) AND
1134 a.selection_type_code = 'INPUT_FILTER';
1135
1136 l_first_record BOOLEAN := TRUE;
1137 l_attribute_value VARCHAR2(3000);
1138 l_previous_attr_id NUMBER;
1139 l_previous_sc_id NUMBER;
1140 i NUMBER := 1;
1141 l_api_version NUMBER := 1;
1142 l_api_name VARCHAR2(30) := 'RETRIEVE_INPUT_FILTER';
1143
1144 BEGIN
1145 -------------------- initialize -------------------------
1146 IF FND_API.to_boolean(p_init_msg_list) THEN
1147 FND_MSG_PUB.initialize;
1148 END IF;
1149
1150 IF NOT FND_API.compatible_api_call(
1151 l_api_version,
1152 p_api_version_number,
1153 l_api_name,
1154 g_pkg_name
1155 ) THEN
1156 RAISE FND_API.g_exc_unexpected_error;
1157 END IF;
1158
1159 x_return_status := FND_API.G_RET_STS_SUCCESS;
1160
1161
1162 -------------------------- Source code --------------------
1163
1164 x_input_filter.DELETE;
1165
1166 FOR lc_cursor IN lc_input_filter LOOP
1167 IF (l_first_record) THEN
1168 l_attribute_value := p_delimiter || lc_cursor.attribute_value;
1169 l_first_record := FALSE;
1170
1171 ELSIF (lc_cursor.attribute_id = l_previous_attr_id AND
1172 lc_cursor.selection_criteria_id = l_previous_sc_id)
1173 THEN
1174 l_attribute_value := l_attribute_value || p_delimiter ||
1175 lc_cursor.attribute_value;
1176 ELSE
1177 x_input_filter(i).attribute_id := l_previous_attr_id;
1178 x_input_filter(i).attribute_value := l_attribute_value || p_delimiter;
1179 i := i + 1;
1180 l_attribute_value := p_delimiter || lc_cursor.attribute_value;
1181 END IF;
1182
1183 l_previous_attr_id := lc_cursor.attribute_id;
1184 l_previous_sc_id := lc_cursor.selection_criteria_id;
1185 END LOOP;
1186
1187 IF (l_previous_attr_id IS NOT NULL) THEN
1188 x_input_filter(i).attribute_id := l_previous_attr_id;
1189 x_input_filter(i).attribute_value := l_attribute_value || p_delimiter;
1190 END IF;
1191
1192 -------------------- Exception --------------------------
1193 EXCEPTION
1194 WHEN FND_API.G_EXC_ERROR THEN
1195 x_return_status := FND_API.G_RET_STS_ERROR;
1196 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1197 p_count => x_msg_count,
1198 p_data => x_msg_data);
1199
1200 WHEN FND_API.g_exc_unexpected_error THEN
1201 x_return_status := FND_API.g_ret_sts_unexp_error;
1202 FND_MSG_PUB.count_and_get(
1203 p_encoded => FND_API.g_false,
1204 p_count => x_msg_count,
1205 p_data => x_msg_data
1206 );
1207
1208 WHEN OTHERS THEN
1209 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1210 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1211 END IF;
1212
1213 x_return_status := FND_API.G_RET_STS_ERROR;
1214 FND_MSG_PUB.count_and_get(
1215 p_encoded => FND_API.g_false,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219
1220 END Retrieve_Input_Filter;
1221 -- ===========================End of Retrieve_Input_Filter=========================
1222
1223
1224
1225 --=============================================================================+
1226 --| Public Function |
1227 --| |
1228 --| Currency_Conversion |
1229 --| |
1230 --| Parameters |
1231 --| IN |
1232 --| OUT |
1233 --| |
1234 --| NOTES: |
1235 --| When the attribute is of currency type, the attribute value has 3 tokens:|
1236 --| 100000:::USD:::20020115142503 |
1237 --| (Line Amount:::Currency:::Currency Date) |
1238 --| |
1239 --| HISTORY |
1240 --| |
1241 --==============================================================================
1242 FUNCTION Currency_Conversion(
1243 p_amount IN NUMBER,
1244 p_currency_code IN VARCHAR2,
1245 p_currency_conversion_date IN DATE := SYSDATE,
1246 p_rule_currency_code IN VARCHAR2,
1247 p_no_exception_flag IN VARCHAR2 := 'N'
1248 )
1249 RETURN NUMBER
1250 IS
1251 l_api_name VARCHAR2(30) := 'Currency_Conversion';
1252 l_converted_attr_value NUMBER;
1253 l_conversion_status_flag NUMBER;
1254
1255 BEGIN
1256 IF (p_amount IS NULL) THEN
1257 RETURN p_amount;
1258 END IF;
1259
1260
1261 -- ---------------------------------------------------------------------------
1262 -- If the currency_code of the "from" currency is same as that of the "to"
1263 -- currency, no conversion is necessary.
1264 -- ---------------------------------------------------------------------------
1265 IF (p_currency_code = p_rule_currency_code) THEN
1266 RETURN p_amount;
1267 END IF;
1268
1269
1270 -- ---------------------------------------------------------------------------
1271 -- Package Global Variables needed for doing currency conversion.
1272 -- ---------------------------------------------------------------------------
1273 IF (g_period_set_name IS NULL) THEN
1274 g_period_set_name := FND_PROFILE.Value('AS_FORECAST_CALENDAR');
1275 END IF;
1276
1277 IF (g_period_type IS NULL) THEN
1278 g_period_type := FND_PROFILE.Value('AS_DEFAULT_PERIOD_TYPE');
1279 END IF;
1280
1281
1282 BEGIN
1283 SELECT round((p_amount/rate.denominator_rate) * rate.numerator_rate,2),
1284 rate.conversion_status_flag
1285 INTO l_converted_attr_value, l_conversion_status_flag
1286 FROM as_period_rates rate,
1287 as_period_days day
1288 WHERE rate.from_currency = p_currency_code AND
1289 rate.to_currency = p_rule_currency_code AND
1290 day.period_name = rate.period_name AND
1291 day.period_set_name = g_period_set_name AND
1292 day.period_type = g_period_type AND
1293 day.period_day = TRUNC(p_currency_conversion_date)
1294 --- Condition added to fix the bug # 5509934
1295 and exists (select 1 from gl_periods glp
1296 where glp.period_set_name=g_period_set_name
1297 and glp.adjustment_period_flag='N'
1298 and glp.period_type = g_period_type
1299 and glp.period_name = day.period_name
1300 );
1301
1302 EXCEPTION
1303 WHEN NO_DATA_FOUND THEN
1304 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1305 p_msg_name => 'PV_NO_CURRENCY_RATE_INFO',
1306 p_token1 => 'Rule Currency Code',
1307 p_token1_value => p_rule_currency_code,
1308 p_token2 => 'Entity Currency Code',
1309 p_token2_value => p_currency_code,
1310 p_token3 => 'Conversion Date',
1311 p_token3_value => p_currency_conversion_date);
1312
1313 IF (p_no_exception_flag = 'Y') THEN
1314 RETURN null;
1315
1316 ELSE
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319 END; -- End of BEGIN-END
1320
1321 IF (l_conversion_status_flag = 1) THEN
1322 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1323 p_msg_name => 'PV_NO_CURRENCY_RATE_INFO',
1324 p_token1 => 'Rule Currency Code',
1325 p_token1_value => p_rule_currency_code,
1326 p_token2 => 'Entity Currency Code',
1327 p_token2_value => p_currency_code,
1328 p_token3 => 'Conversion Date',
1329 p_token3_value => p_currency_conversion_date);
1330
1331 IF (p_no_exception_flag = 'Y') THEN
1332 RETURN null;
1333
1334 ELSE
1335 RAISE FND_API.G_EXC_ERROR;
1336 END IF;
1337 END IF;
1338
1339 RETURN l_converted_attr_value;
1340
1341 -------------------- Exception --------------------------
1342 EXCEPTION
1343 WHEN FND_API.G_EXC_ERROR THEN
1344 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1345 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1346 END IF;
1347
1348 RAISE;
1349
1350 WHEN FND_API.g_exc_unexpected_error THEN
1351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1352 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1353 END IF;
1354
1355 RAISE;
1356
1357 WHEN OTHERS THEN
1358 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1359 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1360 END IF;
1361
1362 RAISE;
1363
1364 END Currency_Conversion;
1365 -- ===========================End of Currency_Conversion=========================
1366
1367 --=============================================================================+
1368 --| Public Function |
1369 --| |
1370 --| Currency_Conversion |
1371 --| |
1372 --==============================================================================
1373 FUNCTION Currency_Conversion(
1374 p_entity_attr_value IN VARCHAR2
1375 )
1376 RETURN NUMBER
1377 IS
1378 l_entity_attr_value NUMBER;
1379
1380 BEGIN
1381 l_entity_attr_value :=
1382 Currency_Conversion(
1383 p_entity_attr_value => p_entity_attr_value,
1384 p_rule_currencY_code => fnd_profile.value('ICX_PREFERRED_CURRENCY'),
1385 p_no_exception_flag => 'Y'
1386 );
1387
1388 RETURN l_entity_attr_value;
1389 END Currency_Conversion;
1390 -- ===========================End of Currency_Conversion========================
1391
1392
1393 --=============================================================================+
1394 --| Public Function |
1395 --| |
1396 --| Currency_Conversion |
1397 --| |
1398 --| Parameters |
1399 --| IN |
1400 --| OUT |
1401 --| |
1402 --| NOTES: |
1403 --| When the attribute is of currency type, the attribute value has 3 tokens:|
1404 --| 100000:::USD:::20020115142503 |
1405 --| (Line Amount:::Currency:::Currency Date) |
1406 --| |
1407 --| HISTORY |
1408 --| |
1409 --==============================================================================
1410 FUNCTION Currency_Conversion(
1411 p_entity_attr_value IN VARCHAR2,
1412 p_rule_currency_code IN VARCHAR2,
1413 p_no_exception_flag IN VARCHAR2 := 'N'
1414 )
1415 RETURN NUMBER
1416 IS
1417 l_api_name VARCHAR2(30) := 'Currency_Conversion';
1418 l_converted_attr_value NUMBER;
1419 l_entity_attr_value NUMBER;
1420 l_entity_currency_code VARCHAR2(10);
1421 l_currency_date DATE;
1422 l_num_of_tokens NUMBER;
1423 l_conversion_status_flag NUMBER;
1424 le_rate_not_found EXCEPTION;
1425 le_wrong_token_numbers EXCEPTION;
1426
1427 BEGIN
1428 --fnd_msg_pub.g_msg_level_threshold := fnd_msg_pub.g_msg_lvl_debug_low;
1429
1430 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) AND
1431 (g_display_message))
1432 THEN
1433 Debug('Currency Conversion..........................................');
1434 END IF;
1435
1436 IF (p_entity_attr_value IS NULL) THEN
1437 RETURN null;
1438 END IF;
1439
1440 -- ----------------------------------------------------------------------------
1441 -- Parse out tokens in the string.
1442 -- ----------------------------------------------------------------------------
1443 l_num_of_tokens := (LENGTH(p_entity_attr_value) -
1444 LENGTH(REPLACE(p_entity_attr_value, g_token_delimiter, '')))
1445 /LENGTH(g_token_delimiter)
1446 + 1;
1447
1448 IF (l_num_of_tokens <> 3) THEN
1449 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1450 p_msg_name => 'PV_CURRENCY_WRONG_FORMAT',
1451 p_token1 => 'TEXT',
1452 p_token1_value => 'Entity Attribute Value: ' || p_entity_attr_value,
1453 p_token2 => null,
1454 p_token2_value => null);
1455
1456 RAISE FND_API.G_EXC_ERROR;
1457 END IF;
1458
1459
1460 -- ---------------------------------------------------------------------------
1461 -- The format mask '999999999999.99' is to ensure that no matter what
1462 -- NLS_NUMERIC_CHARACTERS is set to in the current session, TO_NUMBER()
1463 -- function can still interpret it correctly.
1464 -- ---------------------------------------------------------------------------
1465 l_entity_attr_value := TO_NUMBER(Retrieve_Token (
1466 p_delimiter => g_token_delimiter,
1467 p_attr_value_string => p_entity_attr_value,
1468 p_input_type => 'IN TOKEN',
1469 p_index => 1
1470 ),
1471 '999999999999.99'
1472 );
1473
1474
1475 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) AND
1476 (g_display_message))
1477 THEN
1478 Debug('Entity Attribute Value: ' || l_entity_attr_value);
1479 END IF;
1480
1481 -- ----------------------------------------------------------------------------
1482 -- If the amount is NULL, just return NULL.
1483 -- ----------------------------------------------------------------------------
1484 IF (l_entity_attr_value IS NULL) THEN
1485 RETURN NULL;
1486 END IF;
1487
1488
1489 l_entity_currency_code := Retrieve_Token (
1490 p_delimiter => g_token_delimiter,
1491 p_attr_value_string => p_entity_attr_value,
1492 p_input_type => 'IN TOKEN',
1493 p_index => 2
1494 );
1495
1496
1497 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) AND
1498 (g_display_message))
1499 THEN
1500 Debug('Entity Currency Code: ' || l_entity_currency_code);
1501 END IF;
1502
1503 -- ----------------------------------------------------------------------------
1504 -- Check for the existence of currency code.
1505 -- ----------------------------------------------------------------------------
1506 IF (l_entity_currency_code IS NULL) THEN
1507 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1508 p_msg_name => 'PV_NULL_TOKEN',
1509 p_token1 => 'TEXT',
1510 p_token1_value => 'Entity Attribute Value: ' || p_entity_attr_value,
1511 p_token2 => 'TEXT',
1512 p_token2_value => 'This attribute value does not have a currency code!');
1513
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
1516
1517
1518 l_currency_date := TO_DATE(Retrieve_Token (
1519 p_delimiter => g_token_delimiter,
1520 p_attr_value_string => p_entity_attr_value,
1521 p_input_type => 'IN TOKEN',
1522 p_index => 3
1523 ),
1524 'yyyymmddhh24miss');
1525
1526
1527
1528 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) AND
1529 (g_display_message))
1530 THEN
1531 Debug('Currency Date: ' || l_currency_date);
1532 END IF;
1533
1534 -- ----------------------------------------------------------------------------
1535 -- If the currency_code of the entity is the same as that of the rule,
1536 -- no conversion is necessary.
1537 -- ----------------------------------------------------------------------------
1538 IF (l_entity_currency_code = p_rule_currency_code) THEN
1539 RETURN l_entity_attr_value;
1540 END IF;
1541
1542 -- ----------------------------------------------------------------------------
1543 -- Check for the existence of currency conversion date.
1544 -- ----------------------------------------------------------------------------
1545 IF (l_currency_date IS NULL) THEN
1546 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1547 p_msg_name => 'PV_NULL_TOKEN',
1548 p_token1 => 'TEXT',
1549 p_token1_value => 'Entity Attribute Value: ' || p_entity_attr_value,
1550 p_token2 => 'TEXT',
1551 p_token2_value => 'This attribute value does not have a currency conversion date!');
1552
1553 RAISE FND_API.G_EXC_ERROR;
1554 END IF;
1555
1556
1557 -- ----------------------------------------------------------------------------
1558 -- Check for the existence of currency conversion date.
1559 -- ----------------------------------------------------------------------------
1560 IF (g_period_set_name IS NULL) THEN
1561 g_period_set_name := FND_PROFILE.Value('AS_FORECAST_CALENDAR');
1562 END IF;
1563
1564 IF (g_period_type IS NULL) THEN
1565 g_period_type := FND_PROFILE.Value('AS_DEFAULT_PERIOD_TYPE');
1566 END IF;
1567
1568
1569 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) AND
1570 (g_display_message))
1571 THEN
1572 Debug('Period Set Name: ' || g_period_set_name);
1573 Debug('Period Type: ' || g_period_type);
1574 END IF;
1575
1576
1577
1578 BEGIN
1579 SELECT round((l_entity_attr_value/rate.denominator_rate) * rate.numerator_rate,2),
1580 rate.conversion_status_flag
1581 INTO l_converted_attr_value, l_conversion_status_flag
1582 FROM as_period_rates rate,
1583 as_period_days day
1584 WHERE rate.from_currency = l_entity_currency_code AND
1585 rate.to_currency = p_rule_currency_code AND
1586 day.period_name = rate.period_name AND
1587 day.period_set_name = g_period_set_name AND
1588 day.period_type = g_period_type AND
1589 day.period_day = TRUNC(l_currency_date) AND
1590 --- Condition added to fix the bug # 5509934
1591 exists (select 1 from gl_periods glp
1592 where glp.period_set_name=g_period_set_name
1593 and glp.adjustment_period_flag='N'
1594 and glp.period_type = g_period_type
1595 and glp.period_name = day.period_name
1596 );
1597
1598
1599 EXCEPTION
1600 WHEN NO_DATA_FOUND THEN
1601 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1602 p_msg_name => 'PV_NO_CURRENCY_RATE_INFO',
1603 p_token1 => 'Rule Currency Code',
1604 p_token1_value => p_rule_currency_code,
1605 p_token2 => 'Entity Currency Code',
1606 p_token2_value => l_entity_currency_code,
1607 p_token3 => 'Conversion Date',
1608 p_token3_value => l_currency_date);
1609
1610 IF (p_no_exception_flag = 'Y') THEN
1611 RETURN null;
1612
1613 ELSE
1614 RAISE FND_API.G_EXC_ERROR;
1615 END IF;
1616 END; -- End of BEGIN-END
1617
1618 IF (l_conversion_status_flag = 1) THEN
1619 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1620 p_msg_name => 'PV_NO_CURRENCY_RATE_INFO',
1621 p_token1 => 'Rule Currency Code',
1622 p_token1_value => p_rule_currency_code,
1623 p_token2 => 'Entity Currency Code',
1624 p_token2_value => l_entity_currency_code,
1625 p_token3 => 'Conversion Date',
1626 p_token3_value => l_currency_date);
1627
1628 IF (p_no_exception_flag = 'Y') THEN
1629 RETURN null;
1630
1631 ELSE
1632 RAISE FND_API.G_EXC_ERROR;
1633 END IF;
1634 END IF;
1635
1636 RETURN l_converted_attr_value;
1637
1638 -------------------- Exception --------------------------
1639 EXCEPTION
1640 WHEN FND_API.G_EXC_ERROR THEN
1641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1642 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1643 END IF;
1644
1645 RAISE;
1646
1647 WHEN FND_API.g_exc_unexpected_error THEN
1648 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1649 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1650 END IF;
1651
1652 RAISE;
1653
1654 WHEN OTHERS THEN
1655 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1656 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1657 END IF;
1658
1659 RAISE;
1660 END Currency_Conversion;
1661 -- ===========================End of Currency_Conversion========================
1662
1663
1664
1665 --=============================================================================+
1666 --| Public Procedure |
1667 --| |
1668 --| Debug |
1669 --| |
1670 --| Parameters |
1671 --| IN |
1672 --| OUT |
1673 --| |
1674 --| |
1675 --| NOTES: |
1676 --| |
1677 --| HISTORY |
1678 --| |
1679 --==============================================================================
1680 PROCEDURE Debug(
1681 p_msg_string IN VARCHAR2
1682 )
1683 IS
1684
1685 BEGIN
1686 --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1687 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1688 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
1689 FND_MSG_PUB.Add;
1690 --END IF;
1691 END Debug;
1692 -- =================================End of Debug================================
1693
1694 --=============================================================================+
1695 --| Public Procedure |
1696 --| |
1697 --| Set_Message |
1698 --| |
1699 --| Parameters |
1700 --| IN |
1701 --| OUT |
1702 --| |
1703 --| |
1704 --| NOTES: |
1705 --| |
1706 --| HISTORY |
1707 --| |
1708 --==============================================================================
1709 PROCEDURE Set_Message(
1710 p_msg_level IN NUMBER,
1711 p_msg_name IN VARCHAR2,
1712 p_token1 IN VARCHAR2,
1713 p_token1_value IN VARCHAR2,
1714 p_token2 IN VARCHAR2 := NULL ,
1715 p_token2_value IN VARCHAR2 := NULL,
1716 p_token3 IN VARCHAR2 := NULL,
1717 p_token3_value IN VARCHAR2 := NULL
1718 )
1719 IS
1720 BEGIN
1721 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
1722 FND_MESSAGE.Set_Name('PV', p_msg_name);
1723 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1724
1725 IF (p_token2 IS NOT NULL) THEN
1726 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1727 END IF;
1728
1729 IF (p_token3 IS NOT NULL) THEN
1730 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1731 END IF;
1732
1733 FND_MSG_PUB.Add;
1734 END IF;
1735 END Set_Message;
1736 -- ==============================End of Set_Message==============================
1737
1738
1739
1740
1741 -- *****************************************************************************
1742 -- *****************************************************************************
1743 -- ********************* Private Routines Start Here...*************************
1744 -- *****************************************************************************
1745 -- *****************************************************************************
1746
1747
1748 --=============================================================================+
1749 --| Private Function |
1750 --| |
1751 --| Check_Match_Logic |
1752 --| Provide the logic for checking if there's a match between the |
1753 --| opportunity's attribute value and opportunity selection's |
1754 --| attribute value. |
1755 --| |
1756 --| Parameters |
1757 --| IN |
1758 --| OUT |
1759 --| |
1760 --| |
1761 --| NOTES: |
1762 --| When the attribute is purchase_amount, the attribute |
1763 --| value has 4 tokens: |
1764 --| SW/APP:::100000:::USD:::20020115142503 |
1765 --| (Product Category:::Line Amount:::Currency:::Currency Date) |
1766 --| |
1767 --| When the attribute is of currency type, the attribute value has 3 tokens:|
1768 --| 100000:USD:20020115142503 |
1769 --| (Line Amount:::Currency:::Currency Date) |
1770 --| |
1771 --| HISTORY |
1772 --| |
1773 --==============================================================================
1774 FUNCTION Check_Match_Logic(
1775 p_attribute_id NUMBER,
1776 p_attribute_type VARCHAR2,
1777 p_operator VARCHAR2,
1778 p_entity_attr_value VARCHAR2,
1779 p_rule_attr_value VARCHAR2,
1780 p_rule_to_attr_value VARCHAR2,
1781 p_return_type VARCHAR2,
1782 p_rule_currency_code VARCHAR2
1783 )
1784 RETURN BOOLEAN
1785 IS
1786 l_matched BOOLEAN := FALSE;
1787 l_entity_num_attr_value NUMBER;
1788 l_rule_num_attr_value NUMBER;
1789 l_rule_num_to_value NUMBER;
1790 l_entity_attr_value VARCHAR2(2010) := UPPER(p_entity_attr_value);
1791
1792 BEGIN
1793 Debug('Inside Check_Match_Logic...');
1794 Debug('Return Type: ' || p_return_type);
1795 Debug('l_entity_attr_value (80 chars): ' || substr(l_entity_attr_value,1,80));
1796
1797 -- ----------------------------------------------------------------------
1798 -- 'MATCH_FUE' is a special process for matching the input filter portion
1799 -- of a rule. When the attibute is this type, retrieve the first token
1800 -- (FUE - Product Interest) of p_entity_attr_value.
1801 -- ----------------------------------------------------------------------
1802 IF (g_attribute_type = 'MATCH_FUE') THEN
1803 l_entity_attr_value :=
1804 SUBSTR(p_entity_attr_value, 1,
1805 INSTR(p_entity_attr_value, g_token_delimiter) - 1);
1806
1807 -- ----------------------------------------------------------------------
1808 -- If attribute is a purchase_amount (LINE_AMOUNT or FILTER_AMOUNT),
1809 -- strip the first token (product interest)
1810 -- off the attribute value string.
1811 -- ----------------------------------------------------------------------
1812 ELSIF (p_attribute_type IN ('LINE_AMOUNT', 'FILTER_AMOUNT')) THEN
1813 l_entity_attr_value :=
1814 SUBSTR(p_entity_attr_value,
1815 INSTR(p_entity_attr_value, g_token_delimiter) + LENGTH(g_token_delimiter),
1816 LENGTH(p_entity_attr_value));
1817 END IF;
1818
1819 -- -------------------------------------------------------------------------
1820 -- Process 'currency' attributes.
1821 -- -------------------------------------------------------------------------
1822 IF (p_return_type = 'CURRENCY') THEN
1823 l_entity_num_attr_value :=
1824 Currency_Conversion(
1825 p_entity_attr_value => l_entity_attr_value,
1826 p_rule_currency_code => p_rule_currency_code
1827 );
1828
1829 IF (l_entity_num_attr_value = g_currency_conversion_error) THEN
1830 RAISE FND_API.G_EXC_ERROR;
1831 END IF;
1832
1833 /* ---------------------------------------------------------------
1834 l_rule_num_attr_value :=
1835 Currency_Conversion(
1836 p_entity_attr_value => p_rule_attr_value,
1837 p_rule_currency_code => p_rule_currency_code
1838 );
1839
1840 l_rule_num_to_value :=
1841 Currency_Conversion(
1842 p_entity_attr_value => p_rule_to_attr_value,
1843 p_rule_currency_code => p_rule_currency_code
1844 );
1845 * ---------------------------------------------------------------- */
1846
1847 -- ----------------------------------------------------------------
1848 -- Rule's attribute value should not be in the 3 token format.
1849 -- It should just be an amount.
1850 -- Commented out above lines whose modification was made in
1851 -- 115.26. Reverted back to the logic used in that release.
1852 -- ----------------------------------------------------------------
1853 l_rule_num_attr_value := TO_NUMBER(p_rule_attr_value);
1854 l_rule_num_to_value := TO_NUMBER(p_rule_to_attr_value);
1855
1856 --Debug('l_rule_num_attr_value = ' || l_rule_num_attr_value);
1857 --Debug('l_entity_num_attr_value = ' || l_entity_num_attr_value);
1858
1859
1860 ELSIF (p_return_type = 'NUMBER') THEN
1861 l_entity_num_attr_value := TO_NUMBER(l_entity_attr_value);
1862 l_rule_num_attr_value := TO_NUMBER(p_rule_attr_value);
1863 l_rule_num_to_value := TO_NUMBER(p_rule_to_attr_value);
1864 END IF;
1865
1866 -- Debug('Operator: ' || p_operator);
1867 -- Debug('l_entity_attr_value: ' || l_entity_attr_value);
1868
1869 -- ----------------------------------------------------------
1870 -- operator -> EQUALS
1871 -- ----------------------------------------------------------
1872 IF (p_operator = g_equal) THEN
1873 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1874 IF (l_entity_num_attr_value = l_rule_num_attr_value) THEN
1875 l_matched := TRUE;
1876 END IF;
1877
1878 ELSE
1879 IF (l_entity_attr_value = UPPER(p_rule_attr_value)) THEN
1880 l_matched := TRUE;
1881 END IF;
1882 END IF;
1883
1884 -- ----------------------------------------------------------
1885 -- operator -> GREATER_THAN
1886 -- ----------------------------------------------------------
1887 ELSIF (p_operator = g_greater_than) THEN
1888 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1889 IF (l_entity_num_attr_value > l_rule_num_attr_value) THEN
1890 l_matched := TRUE;
1891 END IF;
1892
1893 ELSIF (p_return_type = 'DATE') THEN
1894 IF (l_entity_attr_value > UPPER(p_rule_attr_value)) THEN
1895 l_matched := TRUE;
1896 END IF;
1897
1898 ELSE
1899 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1900 p_msg_name => 'PV_ILLEGAL_OPERATOR',
1901 p_token1 => 'Operator',
1902 p_token1_value => p_operator,
1903 p_token2 => 'Attribute Return Type',
1904 p_token2_value => p_return_type);
1905
1906 RAISE FND_API.G_EXC_ERROR;
1907 END IF;
1908
1909 -- ----------------------------------------------------------
1910 -- operator -> GREATER_THAN_OR_EQUALS
1911 -- ----------------------------------------------------------
1912 ELSIF (p_operator = g_greater_than_equal) THEN
1913 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1914 IF (l_entity_num_attr_value >= l_rule_num_attr_value) THEN
1915 l_matched := TRUE;
1916 END IF;
1917
1918 ELSIF (p_return_type = 'DATE') THEN
1919 IF (l_entity_attr_value >= UPPER(p_rule_attr_value)) THEN
1920 l_matched := TRUE;
1921 END IF;
1922
1923 ELSE
1924 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1925 p_msg_name => 'PV_ILLEGAL_OPERATOR',
1926 p_token1 => 'Operator',
1927 p_token1_value => p_operator,
1928 p_token2 => 'Attribute Return Type',
1929 p_token2_value => p_return_type);
1930
1931 RAISE FND_API.G_EXC_ERROR;
1932 END IF;
1933
1934 -- ----------------------------------------------------------
1935 -- operator -> LESS_THAN
1936 -- ----------------------------------------------------------
1937 ELSIF (p_operator = g_less_than) THEN
1938 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1939 IF (l_entity_num_attr_value < l_rule_num_attr_value) THEN
1940 l_matched := TRUE;
1941 END IF;
1942
1943 ELSIF (p_return_type = 'DATE') THEN
1944 IF (l_entity_attr_value < UPPER(p_rule_attr_value)) THEN
1945 l_matched := TRUE;
1946 END IF;
1947
1948 ELSE
1949 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1950 p_msg_name => 'PV_ILLEGAL_OPERATOR',
1951 p_token1 => 'Operator',
1952 p_token1_value => p_operator,
1953 p_token2 => 'Attribute Return Type',
1954 p_token2_value => p_return_type);
1955
1956 RAISE FND_API.G_EXC_ERROR;
1957 END IF;
1958
1959 -- ----------------------------------------------------------
1960 -- operator -> LESS_THAN_OR_EQUALS
1961 -- ----------------------------------------------------------
1962 ELSIF (p_operator = g_less_than_equal) THEN
1963 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1964 IF (l_entity_num_attr_value <= l_rule_num_attr_value) THEN
1965 l_matched := TRUE;
1966 END IF;
1967
1968 ELSIF (p_return_type = 'DATE') THEN
1969 IF (l_entity_attr_value <= UPPER(p_rule_attr_value)) THEN
1970 l_matched := TRUE;
1971 END IF;
1972
1973 ELSE
1974 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1975 p_msg_name => 'PV_ILLEGAL_OPERATOR',
1976 p_token1 => 'Operator',
1977 p_token1_value => p_operator,
1978 p_token2 => 'Attribute Return Type',
1979 p_token2_value => p_return_type);
1980
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 -- ----------------------------------------------------------
1985 -- operator -> NOT_EQUALS
1986 -- ----------------------------------------------------------
1987 ELSIF (p_operator = g_not_equal) THEN
1988 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
1989 IF (l_entity_num_attr_value IS NULL OR
1990 l_entity_num_attr_value <> l_rule_num_attr_value) THEN
1991 l_matched := TRUE;
1992 END IF;
1993
1994 ELSE
1995 IF (l_entity_attr_value IS NULL OR
1996 l_entity_attr_value <> UPPER(p_rule_attr_value)) THEN
1997 l_matched := TRUE;
1998 END IF;
1999 END IF;
2000
2001 -- ----------------------------------------------------------
2002 -- operator -> BETWEEN
2003 -- ----------------------------------------------------------
2004 ELSIF (UPPER(p_operator) = g_between) THEN
2005 IF (p_return_type IN ('NUMBER', 'CURRENCY')) THEN
2006 IF (l_entity_num_attr_value BETWEEN
2007 l_rule_num_attr_value AND l_rule_num_to_value) THEN
2008 l_matched := TRUE;
2009 END IF;
2010
2011 ELSIF (p_return_type IN ('DATE', 'STRING')) THEN
2012 -- ----------------------------------------------------------------------------
2013 -- POSTAL CODE |
2014 -- -------------
2015 -- Make a special case for postal code. Use numeric comparision for postal code
2016 -- if possible. If not, as in the case of Canadian postal code, use string
2017 -- comparison (handled in the exception).
2018 -- ----------------------------------------------------------------------------
2019 IF (p_attribute_id = g_a_Postal_Code) THEN
2020 BEGIN
2021 l_rule_num_attr_value := TO_NUMBER(REPLACE(p_rule_attr_value, '-', '.'));
2022 l_rule_num_to_value := TO_NUMBER(REPLACE(p_rule_to_attr_value, '-', '.'));
2023 l_entity_num_attr_value := TO_NUMBER(REPLACE(l_entity_attr_value, '-', '.'));
2024
2025
2026 IF (l_entity_num_attr_value BETWEEN
2027 l_rule_num_attr_value AND l_rule_num_to_value) THEN
2028 l_matched := TRUE;
2029 END IF;
2030
2031 -- --------------------------------------------------------------------------
2032 -- If the postal code cannot be converted to a number, use string comparison.
2033 -- --------------------------------------------------------------------------
2034 EXCEPTION
2035 WHEN g_e_numeric_conversion THEN
2036 IF (l_entity_attr_value BETWEEN
2037 UPPER(p_rule_attr_value) AND UPPER(p_rule_to_attr_value)) THEN
2038 l_matched := TRUE;
2039 END IF;
2040 END;
2041
2042 -- ----------------------------------------------------------------------------
2043 -- Non-Postal-Code comparison.
2044 -- ----------------------------------------------------------------------------
2045 ELSE
2046 IF (l_entity_attr_value BETWEEN
2047 UPPER(p_rule_attr_value) AND UPPER(p_rule_to_attr_value)) THEN
2048 l_matched := TRUE;
2049 END IF;
2050 END IF;
2051
2052 ELSE
2053 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2054 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2055 p_token1 => 'Operator',
2056 p_token1_value => p_operator,
2057 p_token2 => 'Attribute Return Type',
2058 p_token2_value => p_return_type);
2059
2060 RAISE FND_API.G_EXC_ERROR;
2061 END IF;
2062
2063
2064 -- ----------------------------------------------------------
2065 -- operator -> BEGINS_WITH
2066 -- ----------------------------------------------------------
2067 ELSIF (UPPER(p_operator) = g_begins_with) THEN
2068 IF (p_return_type = 'STRING') THEN
2069 -- --------------------------------------------------------
2070 -- If the matching position starts with 1, then there is a
2071 -- match.
2072 -- --------------------------------------------------------
2073 IF (INSTR(l_entity_attr_value, UPPER(p_rule_attr_value)) = 1) THEN
2074 l_matched := TRUE;
2075 END IF;
2076
2077 ELSE
2078 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2079 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2080 p_token1 => 'Operator',
2081 p_token1_value => p_operator,
2082 p_token2 => 'Attribute Return Type',
2083 p_token2_value => p_return_type);
2084
2085 RAISE FND_API.G_EXC_ERROR;
2086 END IF;
2087
2088
2089 -- ----------------------------------------------------------
2090 -- operator -> ENDS_WITH
2091 -- ----------------------------------------------------------
2092 ELSIF (UPPER(p_operator) = g_ends_with) THEN
2093 IF (p_return_type = 'STRING') THEN
2094 -- --------------------------------------------------------
2095 -- If the matching position starts with 1, then there is a
2096 -- match.
2097 -- --------------------------------------------------------
2098 IF (SUBSTR(l_entity_attr_value,
2099 LENGTH(l_entity_attr_value) - LENGTH(p_rule_attr_value) + 1,
2100 LENGTH(p_rule_attr_value))
2101 = UPPER(p_rule_attr_value))
2102 THEN
2103 l_matched := TRUE;
2104 END IF;
2105
2106 ELSE
2107 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2108 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2109 p_token1 => 'Operator',
2110 p_token1_value => p_operator,
2111 p_token2 => 'Attribute Return Type',
2112 p_token2_value => p_return_type);
2113
2114 RAISE FND_API.G_EXC_ERROR;
2115 END IF;
2116
2117
2118 -- ----------------------------------------------------------
2119 -- operator -> CONTAINS
2120 -- ----------------------------------------------------------
2121 ELSIF (UPPER(p_operator) = g_contains) THEN
2122 IF (p_return_type = 'STRING') THEN
2123 IF (INSTR(l_entity_attr_value, UPPER(p_rule_attr_value)) > 0) THEN
2124 l_matched := TRUE;
2125 END IF;
2126
2127 ELSE
2128 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2129 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2130 p_token1 => 'Operator',
2131 p_token1_value => p_operator,
2132 p_token2 => 'Attribute Return Type',
2133 p_token2_value => p_return_type);
2134
2135 RAISE FND_API.G_EXC_ERROR;
2136 END IF;
2137
2138
2139 -- ----------------------------------------------------------
2140 -- operator -> NOT CONTAINS
2141 -- ----------------------------------------------------------
2142 ELSIF (UPPER(p_operator) = g_not_contains) THEN
2143 IF (p_return_type = 'STRING') THEN
2144 IF (INSTR(l_entity_attr_value, UPPER(p_rule_attr_value)) = 0) THEN
2145 l_matched := TRUE;
2146 END IF;
2147
2148 ELSE
2149 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2150 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2151 p_token1 => 'Operator',
2152 p_token1_value => p_operator,
2153 p_token2 => 'Attribute Return Type',
2154 p_token2_value => p_return_type);
2155
2156 RAISE FND_API.G_EXC_ERROR;
2157 END IF;
2158
2159
2160 -- ----------------------------------------------------------
2161 -- operator -> IS NULL
2162 -- ----------------------------------------------------------
2163 ELSIF (UPPER(p_operator) = g_is_null) THEN
2164 IF (l_entity_attr_value IS NULL) THEN
2165 l_matched := TRUE;
2166 END IF;
2167
2168 -- ----------------------------------------------------------
2169 -- operator -> IS NOT NULL
2170 -- ----------------------------------------------------------
2171 ELSIF (UPPER(p_operator) = g_is_not_null) THEN
2172 IF (l_entity_attr_value IS NOT NULL) THEN
2173 l_matched := TRUE;
2174 END IF;
2175
2176 -- ----------------------------------------------------------
2177 -- WRONG OPERATOR!
2178 -- ----------------------------------------------------------
2179 ELSE
2180 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2181 p_msg_name => 'PV_ILLEGAL_OPERATOR',
2182 p_token1 => 'Operator',
2183 p_token1_value => p_operator,
2184 p_token2 => 'Attribute Return Type',
2185 p_token2_value => p_return_type);
2186
2187 RAISE FND_API.G_EXC_ERROR;
2188 END IF;
2189
2190 RETURN l_matched;
2191 END Check_Match_Logic;
2192 -- ========================End of Check_Match_Logic=============================
2193
2194
2195 --=============================================================================+
2196 --| Private Function |
2197 --| |
2198 --| Retrieve_Token |
2199 --| Given a string that contains attribute values separated by |
2200 --| p_delimiter, retrieve the n th token in the string. |
2201 --| |
2202 --| Parameters |
2203 --| IN |
2204 --| OUT |
2205 --| |
2206 --| |
2207 --| NOTES: This function assumes that p_string is always preceeded and ended |
2208 --| with a p_delimiter. e.g. '+++abc+++def+++'. |
2209 --| The only time that p_string is allowed to not preceed or end with |
2210 --| a p_delimiter is when there's only one token in the string. |
2211 --| |
2212 --| HISTORY |
2213 --| |
2214 --==============================================================================
2215 FUNCTION Retrieve_Token (
2216 p_delimiter VARCHAR2,
2217 p_attr_value_string VARCHAR2,
2218 p_input_type VARCHAR2,
2219 p_index NUMBER
2220 )
2221 RETURN VARCHAR2
2222 IS
2223 l_start_position NUMBER := 1;
2224 l_token_length NUMBER;
2225 l_attr_value_string VARCHAR2(32000);
2226
2227 BEGIN
2228 Debug('Retrieving Token (80 chars): ' || substr(p_attr_value_string,1,80));
2229
2230 IF (p_attr_value_string IS NULL) THEN
2231 RETURN null;
2232 END IF;
2233
2234 -- --------------------------------------------------------------------------
2235 -- Pad 'IN TOKEN' string with p_delimiter in the beginning and the end of
2236 -- the string so we would only need one algorithm for token retrieval.
2237 -- --------------------------------------------------------------------------
2238 IF (p_input_type = 'IN TOKEN') THEN
2239 l_attr_value_string := p_delimiter || p_attr_value_string || p_delimiter;
2240 ELSE
2241 l_attr_value_string := p_attr_value_string;
2242 END IF;
2243
2244 -- --------------------------------------------------------------------------
2245 -- If the index is out of bound, return g_out_of_bound.
2246 -- --------------------------------------------------------------------------
2247 IF (p_index > Get_Num_Of_Tokens(p_delimiter, l_attr_value_string)) THEN
2248 RETURN g_out_of_bound;
2249 END IF;
2250
2251 -- --------------------------------------------------------------------------
2252 -- There's only one token in the string. Return it the way it is.
2253 -- --------------------------------------------------------------------------
2254 IF (INSTR(l_attr_value_string, p_delimiter) = 0) THEN
2255 RETURN l_attr_value_string;
2256 END IF;
2257
2258 -- --------------------------------------------------------------------------
2259 -- Retrieve the token by locating its start position and the length.
2260 -- --------------------------------------------------------------------------
2261 FOR i IN 1..p_index LOOP
2262 l_start_position := INSTR(l_attr_value_string, p_delimiter,
2263 l_start_position, 1) + LENGTH(p_delimiter);
2264 END LOOP;
2265
2266 l_token_length := INSTR(l_attr_value_string, p_delimiter, l_start_position, 1) -
2267 l_start_position;
2268
2269 RETURN SUBSTR(l_attr_value_string, l_start_position, l_token_length);
2270
2271 EXCEPTION
2272 WHEN others THEN
2273 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2274 Debug('Retrieve Token: ' || p_attr_value_string);
2275 Debug(SQLCODE || ': ' || SQLERRM);
2276 END IF;
2277
2278 END Retrieve_Token;
2279 -- ========================End of Retrieve_Token==============================
2280
2281
2282 --=============================================================================+
2283 --| Private Function |
2284 --| |
2285 --| Get_Num_Of_Tokens |
2286 --| Given a string, p_string, search for the number of tokens separated |
2287 --| by the delimiter, p_delimiter. |
2288 --| |
2289 --| Parameters |
2290 --| IN |
2291 --| OUT |
2292 --| |
2293 --| |
2294 --| NOTES: This function assumes that p_string is always preceeded and ended |
2295 --| with a p_delimiter. e.g. '+++abc+++def+++'. |
2296 --| The only time that p_string is allowed to not preceed or end with |
2297 --| a p_delimiter is when there's only one token in the string. |
2298 --| |
2299 --| HISTORY |
2300 --| |
2301 --==============================================================================
2302 FUNCTION Get_Num_Of_Tokens (
2303 p_delimiter VARCHAR2,
2304 p_string VARCHAR2
2305 ) RETURN NUMBER
2306 IS
2307 l_num_of_tokens NUMBER;
2308
2309 BEGIN
2310 IF (INSTR(p_string, p_delimiter) = 0) THEN
2311 RETURN 1;
2312 END IF;
2313
2314 l_num_of_tokens := ((LENGTH(p_string) - NVL(LENGTH(REPLACE(p_string, p_delimiter, '')), 0))
2315 /LENGTH(p_delimiter)) - 1;
2316
2317 IF (l_num_of_tokens = -1) THEN
2318 l_num_of_tokens := 1;
2319 END IF;
2320
2321 RETURN l_num_of_tokens;
2322
2323 -- --------------------------------------------------------------------------
2324 -- Exception Handling: Should check for p_string with more than 1 token, but
2325 -- does not precced and end with p_delimiter.
2326 -- --------------------------------------------------------------------------
2327 END Get_Num_Of_Tokens;
2328 -- ===========================End of Get_Num_Of_Token===========================
2329
2330
2331 --=============================================================================+
2332 --| Private Function |
2333 --| |
2334 --| Expand_FUE_Values |
2335 --| Pre-11.5.10 |
2336 --| This function is used when the attribute_id = 1 which is FUE |
2337 --| (Functional Expertise). It takes the attribute value of an FUE |
2338 --| attribute and return the expanded version of it separted by |
2339 --| p_delimiter. |
2340 --| e.g. |
2341 --| If p_attr_value = 'SW/APP/CRM', the return string would be: |
2342 --| SW+++SW/App+++SW/APP/CRM+++ |
2343 --| |
2344 --| Since 11.5.10 |
2345 --| Since 11.5.10, we've moved to single product hierarchy. There will |
2346 --| no longer be just 3 levels like 'SW/APP/CRM', but will have n levels|
2347 --| using category_id. p_attr_value will have store |
2348 --| <category_id> |
2349 --| e.g. |
2350 --| '1140' |
2351 --| |
2352 --| Depending on the level that the category_id is at, it may return |
2353 --| something like the following: |
2354 --| 1139+++1140+++ |
2355 --| |
2356 --| Parameters |
2357 --| IN |
2358 --| OUT |
2359 --| |
2360 --| |
2361 --| NOTES: |
2362 --| |
2363 --| HISTORY |
2364 --| |
2365 --==============================================================================
2366 FUNCTION Expand_FUE_Values (
2367 p_attr_value VARCHAR2,
2368 p_delimiter VARCHAR2,
2369 p_additional_token VARCHAR2 DEFAULT null
2370 )
2371 RETURN VARCHAR2
2372 IS
2373 -- ---------------------------------------------------------------------------
2374 -- The SQL uses the denormalized single product hierarchy view to retrieve
2375 -- all the parents in the tree. It will also retrieve itself.
2376 -- ---------------------------------------------------------------------------
2377 CURSOR c IS
2378 SELECT a.parent_id attr_value
2379 FROM eni_prod_denorm_hrchy_v a,
2380 ENI_PROD_DEN_HRCHY_PARENTS_V b
2381 WHERE a.child_id = TO_NUMBER(p_attr_value) AND
2382 a.parent_id = b.category_id AND
2383 b.purchase_interest = 'Y' AND
2384 (b.disable_date IS NULL OR b.disable_date > SYSDATE);
2385
2386 l_concat_value VARCHAR2(1000);
2387
2388 BEGIN
2389 FOR x IN c LOOP
2390 l_concat_value := l_concat_value || x.attr_value ||
2391 p_additional_token || p_delimiter;
2392 END LOOP;
2393
2394 Debug('l_concat_value = ' || l_concat_value);
2395
2396 RETURN l_concat_value;
2397 END Expand_FUE_Values;
2398 -- ===========================End of Expand_FUE_Values===========================
2399
2400
2401
2402 --=============================================================================+
2403 --| Private Function |
2404 --| |
2405 --| Process_Purchase_Amt_Qty |
2406 --| |
2407 --| Parameters |
2408 --| IN |
2409 --| OUT |
2410 --| |
2411 --| |
2412 --| NOTES: |
2413 --| For Purchase Amount, |
2414 --| the expected format of the attribute value, p_attr_value, is: |
2415 --| SW/APP:::100000:::USD:::20020115142502 |
2416 --| (Product Category:Line Amount:Currency:Date) |
2417 --| |
2418 --| For Purchase Quantity, |
2419 --| the expected format of the attribute value, p_attr_value, is: |
2420 --| SW/APP:::10 |
2421 --| (Product Category:Quantity) |
2422 --| |
2423 --| HISTORY |
2424 --| |
2425 --==============================================================================
2426 FUNCTION Process_Purchase_Amt_Qty (
2427 p_attribute_id IN VARCHAR2,
2428 p_attr_value IN VARCHAR2,
2429 p_delimiter IN VARCHAR2
2430 )
2431 RETURN VARCHAR2
2432 IS
2433 l_num_of_tokens NUMBER;
2434 l_product_category VARCHAR2(1000);
2435 l_additional_token VARCHAR2(1000);
2436
2437 BEGIN
2438 l_num_of_tokens := (LENGTH(p_attr_value) -
2439 LENGTH(REPLACE(p_attr_value, g_token_delimiter, '')))
2440 /LENGTH(g_token_delimiter)
2441 + 1;
2442
2443 -- IF (p_attribute_id = g_purchase_amount_attr_id) THEN
2444 IF (p_attribute_id = g_a_Purchase_Amount_Product) THEN
2445 IF (l_num_of_tokens <> 4) THEN
2446 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2447 p_msg_name => 'PV_WRONG_PSS_FORMAT',
2448 p_token1 => 'Attribute ID',
2449 p_token1_value => p_attribute_id,
2450 p_token2 => 'Attribute Value',
2451 p_token2_value => p_attr_value);
2452 END IF;
2453
2454 -- ELSIF (p_attribute_id = g_purchase_quantity_attr_id) THEN
2455 ELSIF (p_attribute_id = g_a_Purchase_Quantity_Product) THEN
2456 IF (l_num_of_tokens <> 2) THEN
2457 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2458 p_msg_name => 'PV_WRONG_PSS_QTY_FORMAT',
2459 p_token1 => 'Attribute ID',
2460 p_token1_value => p_attribute_id,
2461 p_token2 => 'Attribute Value',
2462 p_token2_value => p_attr_value);
2463 END IF;
2464 END IF;
2465
2466 l_product_category := SUBSTR(p_attr_value, 1, INSTR(p_attr_value, g_token_delimiter) - 1);
2467 l_additional_token := SUBSTR(p_attr_value, INSTR(p_attr_value, g_token_delimiter),
2468 LENGTH(p_attr_value));
2469
2470 RETURN Expand_FUE_Values(p_attr_value => l_product_category,
2471 p_delimiter => p_delimiter,
2472 p_additional_token => l_additional_token);
2473 END Process_Purchase_Amt_Qty;
2474 -- ===========================End of Process_Purchase_Amt_Qty===========================
2475
2476
2477
2478 --=============================================================================+
2479 --| Private Function |
2480 --| |
2481 --| Match_FUE |
2482 --| |
2483 --| Parameters |
2484 --| IN |
2485 --| OUT |
2486 --| |
2487 --| |
2488 --| NOTES: |
2489 --| |
2490 --| HISTORY |
2491 --| |
2492 --==============================================================================
2493 FUNCTION Match_FUE (
2494 p_attribute_id IN NUMBER,
2495 p_entity_attr_value IN VARCHAR2,
2496 p_rule_attr_value IN VARCHAR2,
2497 p_rule_to_attr_value IN VARCHAR2,
2498 p_operator IN VARCHAR2,
2499 p_input_filter IN t_input_filter,
2500 p_delimiter IN VARCHAR2,
2501 p_return_type IN VARCHAR2,
2502 p_rule_currency_code IN VARCHAR2
2503 )
2504
2505 RETURN BOOLEAN
2506 IS
2507 l_count NUMBER := p_input_filter.COUNT;
2508 i NUMBER := 1;
2509 l_matched BOOLEAN := FALSE;
2510 l_token_matched BOOLEAN;
2511 l_token_matched_count NUMBER := 0;
2512 l_counter NUMBER;
2513 l_outer_counter NUMBER;
2514 l_rule_attr_value VARCHAR2(500);
2515 l_num_of_tokens NUMBER;
2516 l_num_of_entity_tokens NUMBER;
2517 l_entity_attr_value VARCHAR2(500);
2518 l_input_filter t_input_filter;
2519 l_num_of_input_filter NUMBER := 0;
2520 l_attr_id_interested NUMBER;
2521
2522 BEGIN
2523 -- Debug('Inside MATCH_FUE...');
2524 -- Debug('Operator is ' || p_operator);
2525
2526 g_attribute_type := 'MATCH_FUE';
2527 l_token_matched := TRUE;
2528
2529 -- -------------------------------------------------------------------------
2530 -- Determine which attribute we are evaluting.
2531 -- -------------------------------------------------------------------------
2532 /*
2533 IF (p_attribute_id IN (g_purchase_amount_attr_id, g_purchase_quantity_attr_id)) THEN
2534 l_attr_id_interested := g_FUE_attr_id;
2535
2536 ELSIF (p_attribute_id IN (g_PSS_amount_attr_id, g_PSS_quantity_attr_id)) THEN
2537 l_attr_id_interested := g_PSS_attr_id;
2538 END IF;
2539 */
2540
2541 IF (p_attribute_id IN (g_a_Purchase_Amount_Product, g_a_Purchase_Quantity_Product)) THEN
2542 l_attr_id_interested := g_a_Product_Interest;
2543
2544 ELSIF (p_attribute_id IN (g_a_Purchase_Amount_Solutions, g_a_Purchase_Qty_Solutions)) THEN
2545 l_attr_id_interested := g_a_PSS;
2546 END IF;
2547
2548 -- Debug('MATCH_FUE Attribute ID interested is: ' || l_attr_id_interested);
2549
2550 WHILE (i <= l_count AND l_token_matched) LOOP
2551 -- -----------------------------------------------------------------------
2552 -- Do comparison only if the attribute is "product category" (FUE) or
2553 -- "PSS".
2554 -- -----------------------------------------------------------------------
2555 IF (p_input_filter(i).attribute_id = l_attr_id_interested) THEN
2556 l_num_of_input_filter := l_num_of_input_filter + 1;
2557 l_outer_counter := 1;
2558 l_num_of_tokens := Get_Num_Of_Tokens(p_delimiter, p_input_filter(i).attribute_value);
2559 l_token_matched := FALSE;
2560
2561 -- ---------------------------------------------------------------------
2562 -- Going through the input filter tokens one at a time (OR condition).
2563 -- ---------------------------------------------------------------------
2564 WHILE ((NOT l_token_matched) AND l_outer_counter <= l_num_of_tokens) LOOP
2565 l_rule_attr_value := Retrieve_Token (
2566 p_delimiter => p_delimiter,
2567 p_attr_value_string => p_input_filter(i).attribute_value,
2568 p_input_type => 'STD TOKEN',
2569 p_index => l_outer_counter
2570 );
2571
2572 l_num_of_entity_tokens := Get_Num_Of_Tokens(p_delimiter, p_entity_attr_value);
2573 l_counter := 1;
2574 l_matched := FALSE;
2575
2576 --Debug('Input Filter/Rule Attribute Value: ' || l_rule_attr_value);
2577
2578 -- ---------------------------------------------------------------------
2579 -- Going through the lead's attribute value (FUE) one at a time.
2580 -- ---------------------------------------------------------------------
2581 WHILE ((NOT l_token_matched) AND l_counter <= l_num_of_entity_tokens) LOOP
2582 l_entity_attr_value := Retrieve_Token (
2583 p_delimiter => p_delimiter,
2584 p_attr_value_string => p_entity_attr_value,
2585 p_input_type => 'STD TOKEN',
2586 p_index => l_counter
2587 );
2588
2589 -- ------------------------------------------------------------------
2590 -- Compare Product Category Attribute for the lead and the input
2591 -- filter.
2592 -- ------------------------------------------------------------------
2593 l_matched := Check_Match_Logic (
2594 p_attribute_id => p_attribute_id,
2595 p_attribute_type => 'DUMMY',
2596 p_operator => g_equal,
2597 p_entity_attr_value => l_entity_attr_value,
2598 p_rule_attr_value => l_rule_attr_value,
2599 p_rule_to_attr_value => null,
2600 p_return_type => 'STRING',
2601 p_rule_currency_code => null
2602 );
2603
2604
2605 -- Debug('--------------------------------------------------');
2606 -- Debug('Input Filter Index: ' || i);
2607 -- Debug('Rule Entity Outer Counter: ' || l_outer_counter);
2608 -- Debug('Lead Token Counter : ' || l_counter);
2609
2610 -- -----------------------------------------------------------------------
2611 -- Compare PURCHASE AMOUNT/QUANTITY of the CRITERIA to that of the lead's.
2612 -- -----------------------------------------------------------------------
2613 IF (l_matched) THEN
2614 g_attribute_type := 'NORMAL';
2615
2616 l_token_matched := Check_Match(
2617 p_attribute_id => g_dummy_attr_id, -- Dummy Value
2618 p_entity_attr_value => l_entity_attr_value,
2619 p_rule_attr_value => p_rule_attr_value,
2620 p_rule_to_attr_value => p_rule_to_attr_value,
2621 p_operator => p_operator,
2622 p_input_filter => l_input_filter,
2623 p_delimiter => p_delimiter,
2624 p_return_type => p_return_type,
2625 p_rule_currency_code => p_rule_currency_code
2626 );
2627
2628 g_attribute_type := 'MATCH_FUE';
2629
2630 IF (l_token_matched) THEN
2631 l_token_matched_count := l_token_matched_count + 1;
2632 -- Debug('TOKEN MATCHED!!!!!!!');
2633 END IF;
2634 END IF;
2635
2636 l_counter := l_counter + 1;
2637 END LOOP;
2638
2639 l_outer_counter := l_outer_counter + 1;
2640 END LOOP;
2641 END IF;
2642
2643 i := i + 1;
2644 END LOOP;
2645
2646 IF (l_token_matched_count < l_num_of_input_filter) THEN
2647 l_matched := FALSE;
2648 -- Debug('Final MATCH_FUE MATCH? ' || 'FALSE');
2649
2650 ELSE
2651 l_matched := TRUE;
2652 -- Debug('Final MATCH_FUE MATCH? ' || 'TRUE');
2653 END IF;
2654
2655 g_attribute_type := 'NORMAL';
2656
2657 RETURN l_matched;
2658 END Match_FUE;
2659 -- ===========================End of Match_FUE==================================
2660
2661 -- -----------------------------------------------------------------------------------
2662 -- For given a Partner_Id, Attribute_id, it returns value for currency related attribute
2663 --
2664 -- -----------------------------------------------------------------------------------
2665 FUNCTION get_attr_curr(p_partner_id IN NUMBER , p_attribute_id IN NUMBER)
2666 RETURN VARCHAR2
2667 IS
2668
2669 l_attr_curr VARCHAR2(2000) ;
2670 l_sql_attr_curr VARCHAR2(4000) ;
2671
2672 BEGIN
2673
2674 l_attr_curr := NULL;
2675 l_sql_attr_curr := 'SELECT attr_text
2676 FROM PV_SEARCH_ATTR_VALUES
2677 WHERE party_id(+) = :1 AND attribute_id(+) = :2 ';
2678
2679 EXECUTE IMMEDIATE l_sql_attr_curr
2680 INTO l_attr_curr
2681 USING p_partner_id, p_attribute_id;
2682
2683 return l_attr_curr;
2684
2685 END get_attr_curr;
2686
2687 -- -----------------------------------------------------------------------------------
2688 -- For given a Partner_Id, Attribute_id, it returns value for count related attribute
2689 --
2690 -- -----------------------------------------------------------------------------------
2691 FUNCTION get_attr_cnt(
2692 p_partner_id IN NUMBER ,
2693 p_attribute_id IN NUMBER)
2694 RETURN NUMBER
2695 IS
2696
2697 l_attr_cnt NUMBER;
2698 l_sql_attr_cnt VARCHAR2(4000);
2699
2700 BEGIN
2701
2702 l_attr_cnt := NULL;
2703 l_sql_attr_cnt := 'SELECT attr_value
2704 FROM PV_SEARCH_ATTR_VALUES
2705 WHERE party_id(+) = :1 AND attribute_id(+) = :2 ' ;
2706
2707 EXECUTE IMMEDIATE l_sql_attr_cnt
2708 INTO l_attr_cnt
2709 USING p_partner_id, p_attribute_id;
2710
2711 return l_attr_cnt;
2712 END get_attr_cnt;
2713
2714
2715 -- -----------------------------------------------------------------------------------
2716 -- For given a Partner_Id, Attribute_id, it returns value for rate related attribute
2717 --
2718 -- -----------------------------------------------------------------------------------
2719 FUNCTION get_attr_rate(
2720 p_partner_id IN NUMBER ,
2721 p_attribute_id IN NUMBER)
2722 RETURN NUMBER
2723 IS
2724
2725 l_attr_rate NUMBER;
2726 l_sql_attr_rate VARCHAR2(4000);
2727
2728 BEGIN
2729
2730 l_attr_rate := NULL;
2731 l_sql_attr_rate := 'SELECT round(psav.attr_value*100,pav.decimal_points)
2732 FROM PV_SEARCH_ATTR_VALUES psav, pv_attributes_b pav
2733 WHERE party_id(+) = :1
2734 AND pav.attribute_id=psav.attribute_id
2735 AND psav.attribute_id(+) = :2';
2736
2737 EXECUTE IMMEDIATE l_sql_attr_rate
2738 INTO l_attr_rate
2739 USING p_partner_id, p_attribute_id;
2740
2741 return l_attr_rate;
2742 END get_attr_rate;
2743
2744 -- -----------------------------------------------------------------------------------
2745 -- For given a ATTRIBUTE_ID and ATTR_CODE_ID, it returns description of the attribute
2746 -- code. E.g. It's mainly used for Partner_Level code only.
2747 --
2748 -- -----------------------------------------------------------------------------------
2749 FUNCTION get_partner_level_desc(
2750 p_attribute_id IN NUMBER,
2751 p_attr_code_id IN NUMBER
2752 )
2753 RETURN VARCHAR2
2754 IS
2755
2756 l_partner_level_desc VARCHAR2(240);
2757
2758 CURSOR l_get_partner_level(cv_attribute_id NUMBER, cv_attr_code_id NUMBER) IS
2759 SELECT description
2760 FROM pv_attribute_codes_vl
2761 WHERE attribute_id = cv_attribute_id
2762 AND attr_code_id = cv_attr_code_id ;
2763
2764 BEGIN
2765
2766 l_partner_level_desc := NULL;
2767
2768 OPEN l_get_partner_level(p_attribute_id, p_attr_code_id );
2769 FETCH l_get_partner_level INTO l_partner_level_desc;
2770 CLOSE l_get_partner_level;
2771
2772 RETURN l_partner_level_desc;
2773
2774 END get_partner_level_desc;
2775
2776 -- -----------------------------------------------------------------------------------
2777 -- For a given a ATTRIBUTE_ID, and ATTR_CODE, it returns description of the attribute
2778 -- code. E.g. It's mainly used for all attributes other than Partner_Level code.
2779 --
2780 -- -----------------------------------------------------------------------------------
2781 FUNCTION get_attr_code_desc(
2782 p_attribute_id IN NUMBER,
2783 p_attr_code IN VARCHAR2
2784 )
2785 RETURN VARCHAR2
2786 IS
2787
2788 l_attr_code_desc VARCHAR2(240);
2789
2790 CURSOR lc_attr_code_desc(cv_attribute_id NUMBER, cv_attr_code VARCHAR2) IS
2791 SELECT description
2792 FROM pv_attribute_codes_vl
2793 WHERE attribute_id = cv_attribute_id
2794 AND attr_code = cv_attr_code;
2795
2796 BEGIN
2797
2798 l_attr_code_desc := NULL;
2799
2800 OPEN lc_attr_code_desc(p_attribute_id, p_attr_code );
2801 FETCH lc_attr_code_desc INTO l_attr_code_desc;
2802 CLOSE lc_attr_code_desc;
2803
2804 RETURN l_attr_code_desc;
2805
2806 END get_attr_code_desc;
2807
2808 END pv_check_match_pub;