[Home] [Help]
PACKAGE BODY: APPS.PV_OPP_MATCH_PUB
Source
1 package body pv_opp_match_pub as
2 /* $Header: pvxvompb.pls 120.6 2006/01/10 13:50:54 amaram ship $ */
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variables */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 g_no_more_rules CONSTANT NUMBER := 100000000000000;
14 g_rule_engine_trace_flag VARCHAR2(1);
15 g_failure_code VARCHAR2(100) := null;
16 g_matching_engine_type VARCHAR2(50);
17
18 g_e_buffer_too_small EXCEPTION;
19 PRAGMA EXCEPTION_INIT(g_e_buffer_too_small, -6502);
20
21
22 -- ------------------------------------------------------------------------------------
23 -- Used by the tie-breaking API.
24 -- The following types must exist in the database before this code can be compiled.
25 -- ------------------------------------------------------------------------------------
26 /*
27 connect system...
28 create or replace type system.PV_TIE_BREAKING_TYPE as object (
29 party_id NUMBER,
30 attr_value VARCHAR2(500),
31 concat_value_str VARCHAR2(4000)
32 --,idx NUMBER --> this is the index for preserving party_id order
33 );
34
35 grant all on pv_tie_breaking_type to apps;
36
37 connect apps...
38 create or replace type apps.PV_TIE_BREAKING_TBL as table of system.PV_TIE_BREAKING_TYPE;
39 */
40
41
42
43 /*************************************************************************************/
44 /* */
45 /* */
46 /* */
47 /* private routine declaration */
48 /* */
49 /* */
50 /* */
51 /*************************************************************************************/
52
53 -- --------------------------------------------------------------------
54 -- Tie-breaking API
55 -- --------------------------------------------------------------------
56 PROCEDURE Tie_Breaker(
57 p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
59 p_commit IN VARCHAR2 := FND_API.g_false,
60 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
61 p_process_rule_id IN NUMBER,
62 x_partner_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2
66 );
67
68 -- -----------------------------------------------------------------------------------
69 -- Find the index in the array for the next rule.
70 -- -----------------------------------------------------------------------------------
71 FUNCTION Get_Next_Rule_Index(
72 p_current_index NUMBER,
73 p_opp_selection_tab t_opp_selection_tab
74 )
75 RETURN NUMBER;
76
77 -- -----------------------------------------------------------------------------------
78 -- The following private routines are used by the tie-breaking API.
79 -- -----------------------------------------------------------------------------------
80 PROCEDURE Get_Attr_Length(
81 p_attr_value IN VARCHAR2,
82 p_left_length OUT NOCOPY NUMBER,
83 p_right_length OUT NOCOPY NUMBER)
84 ;
85
86 FUNCTION Convert_To_String(
87 p_attr_value NUMBER,
88 p_max_left_length NUMBER,
89 p_max_right_length NUMBER,
90 p_format_string VARCHAR2,
91 p_positive_format_string VARCHAR2,
92 p_min_max VARCHAR2)
93 RETURN VARCHAR2;
94
95
96 FUNCTION Build_Format_String (
97 p_max_left_length NUMBER,
98 p_max_right_length NUMBER)
99 RETURN VARCHAR2;
100
101
102 -- -----------------------------------------------------------------------------------
103 -- Use for inserting output messages to the message table.
104 -- -----------------------------------------------------------------------------------
105 PROCEDURE Debug(
106 p_msg_string IN VARCHAR2
107 );
108
109
110 PROCEDURE Set_Message(
111 p_msg_level IN NUMBER,
112 p_msg_name IN VARCHAR2,
113 p_token1 IN VARCHAR2,
114 p_token1_value IN VARCHAR2,
115 p_token2 IN VARCHAR2 := NULL,
116 p_token2_value IN VARCHAR2 := NULL,
117 p_token3 IN VARCHAR2 := NULL,
118 p_token3_value IN VARCHAR2 := NULL
119 );
120
121
122 /*************************************************************************************/
123 /* */
124 /* */
125 /* */
126 /* public routines */
127 /* */
128 /* */
129 /* */
130 /*************************************************************************************/
131
132
133
134 --=============================================================================+
135 --| Procedure |
136 --| |
137 --| Opportunity_Selection |
138 --| |
139 --| |
140 --| Parameters |
141 --| IN |
142 --| OUT |
143 --| |
144 --| |
145 --| NOTES |
146 --| |
147 --| HISTORY |
148 --| |
149 --==============================================================================
150 PROCEDURE Opportunity_Selection(
151 p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
153 p_commit IN VARCHAR2 := FND_API.g_false,
154 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
155 p_entity_id IN NUMBER,
156 p_entity IN VARCHAR2,
157 p_user_name IN VARCHAR2 := NULL,
158 p_resource_id IN NUMBER := NULL,
159 x_selected_rule_id OUT NOCOPY NUMBER,
160 x_matched_partner_count OUT NOCOPY NUMBER,
161 x_failure_code OUT NOCOPY VARCHAR2,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2
165 )
166 IS
167 l_api_name VARCHAR2(30) := 'Opportunity_Selection';
168 l_return_status VARCHAR2(100);
169 l_msg_count NUMBER;
170 l_msg_data VARCHAR2(500);
171 l_partner_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
172 l_partner_details JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
173 l_flagcount JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
174 l_distance_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
175 l_distance_uom VARCHAR2(30);
176 l_distance_uom_returned VARCHAR2(30);
177
178 BEGIN
179 g_failure_code := NULL;
180 g_matching_engine_type := 'BACKGROUND_PARTNER_MATCHING';
181
182 Opportunity_Selection(
183 p_api_version => p_api_version,
184 p_init_msg_list => p_init_msg_list,
185 p_commit => p_commit,
186 p_validation_level => p_validation_level,
187 p_entity_id => p_entity_id,
188 p_entity => p_entity,
189 p_user_name => p_user_name,
190 p_resource_id => p_resource_id,
191 p_routing_flag => 'Y',
192 x_partner_tbl => l_partner_tbl,
193 x_partner_details => l_partner_details,
194 x_flagcount => l_flagcount,
195 x_distance_tbl => l_distance_tbl,
196 x_distance_uom_returned => l_distance_uom_returned,
197 x_selected_rule_id => x_selected_rule_id,
198 x_return_status => x_return_status,
199 x_msg_count => x_msg_count,
200 x_msg_data => x_msg_data
201 );
202
203 IF (l_partner_tbl.EXISTS(1)) THEN
204 x_matched_partner_count := l_partner_tbl.COUNT;
205 ELSE
206 x_matched_partner_count := 0;
207 END IF;
208
209 -- ----------------------------------------------------------------------
210 -- Set the failure_code so the caller would know what exactly went wrong.
211 -- ----------------------------------------------------------------------
212 x_failure_code := g_failure_code;
213
214 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
215 RAISE FND_API.G_EXC_ERROR;
216
217 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
218 RAISE FND_API.g_exc_unexpected_error;
219 END IF;
220
221 -------------------- Exception --------------------------
222 EXCEPTION
223 WHEN FND_API.G_EXC_ERROR THEN
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count,
227 p_data => x_msg_data);
228
229 WHEN FND_API.g_exc_unexpected_error THEN
230 x_return_status := FND_API.g_ret_sts_unexp_error;
231 FND_MSG_PUB.count_and_get(
232 p_encoded => FND_API.g_false,
233 p_count => x_msg_count,
234 p_data => x_msg_data
235 );
236
237 WHEN OTHERS THEN
238 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
239 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
240 END IF;
241
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 FND_MSG_PUB.count_and_get(
244 p_encoded => FND_API.g_false,
245 p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248
249 END Opportunity_Selection;
250 -- ===========================End of Opportunity_Selection===========================
251
252
253
254 --=============================================================================+
255 --| Procedure |
256 --| |
257 --| Opportunity_Selection |
258 --| |
259 --| |
260 --| Parameters |
261 --| IN |
262 --| OUT |
263 --| |
264 --| |
265 --| NOTES |
266 --| |
267 --| HISTORY |
268 --| |
269 --==============================================================================
270 PROCEDURE Opportunity_Selection(
271 p_api_version IN NUMBER,
272 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
273 p_commit IN VARCHAR2 := FND_API.g_false,
274 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
275 p_entity_id IN NUMBER,
276 p_entity IN VARCHAR2,
277 p_user_name IN VARCHAR2 := NULL,
278 p_resource_id IN NUMBER := NULL,
279 p_routing_flag IN VARCHAR2 := 'N',
280 x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE,
281 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
282 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
283 x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
284 x_distance_uom_returned OUT NOCOPY VARCHAR2,
285 x_selected_rule_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2
289 )
290 IS
291 l_api_version NUMBER := 1.0;
292 l_api_name VARCHAR2(30) := 'Opportunity_Selection';
293
294 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
295 l_input_filter pv_check_match_pub.t_input_filter;
296 i NUMBER := 1;
297 j NUMBER;
298 l_next_index NUMBER;
299 l_next_rule_id NUMBER;
300 l_stop_flag BOOLEAN := FALSE;
301 l_matched BOOLEAN;
302 l_start NUMBER;
303 l_stop_at_index NUMBER;
304 l_concat_attr_val VARCHAR2(4000);
305 l_concat_to_attr_val VARCHAR2(4000);
306 l_dummy VARCHAR2(4000);
307 l_count NUMBER;
308 l_attribute_id NUMBER;
309 l_return_status VARCHAR2(100);
310 l_msg_count NUMBER;
311 l_msg_data VARCHAR2(500);
312 l_delimiter VARCHAR2(10) := '+++';
313
314 l_attr_val_temp varchar2(4000);
315
316 -- -----------------------------------------------------------------
317 -- Retrieve from a system profile, indicating the type of matching
318 -- to be used. Values to be determined:
319 --
320 -- EXHAUST_ALL_RULES
321 -- STOP_AT_FIRST_RULE
322 -- -----------------------------------------------------------------
323 l_matching_type VARCHAR2(30);
324
325 l_winning_rule_flag VARCHAR2(10);
326 l_entity_rule_applied_id NUMBER;
327
328
329 BEGIN
330 -------------------- initialize -------------------------
331 IF FND_API.to_boolean(p_init_msg_list) THEN
332 FND_MSG_PUB.initialize;
333 END IF;
334
335 IF NOT FND_API.compatible_api_call(
336 l_api_version,
337 p_api_version,
338 l_api_name,
339 g_pkg_name
340 ) THEN
341 RAISE FND_API.g_exc_unexpected_error;
342 END IF;
343
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 -------------------------- Source code --------------------
347
348 -- ------------------------------------------------------------------------
349 -- Make sure that either p_user_name or p_resource IS NOT NULL.
350 -- ------------------------------------------------------------------------
351 IF (p_user_name IS NULL AND p_resource_id IS NULL) THEN
352 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
353 p_msg_name => 'PV_NO_USERNAME_ID_DEFINED',
354 p_token1 => null,
355 p_token1_value => null,
356 p_token2 => null,
357 p_token2_value => null);
358
359 g_failure_code := 'OTHER';
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 -- ------------------------------------------------------------------------
364 -- Initialize OUT parameters.
365 -- ------------------------------------------------------------------------
366 x_partner_tbl := JTF_NUMBER_TABLE();
367 x_partner_details := JTF_VARCHAR2_TABLE_4000();
368 x_flagcount := JTF_VARCHAR2_TABLE_100();
369 x_distance_tbl := JTF_NUMBER_TABLE();
370
371 -- ------------------------------------------------------------------------
372 -- Retrieve profile value for stack trace profile option.
373 -- ------------------------------------------------------------------------
374 --g_rule_engine_trace_flag := NVL(FND_PROFILE.VALUE('PV_RULE_ENGINE_TRACE_ON'), 'N');
375
376 -- --------------------------------------------------------------------------
377 -- Retrieving Matching Type Profile Value, which can be one of the following:
378 --
379 -- EXHAUST_ALL_RULES
380 -- STOP_AT_FIRST_RULE
381 -- --------------------------------------------------------------------------
382 l_matching_type := NVL(FND_PROFILE.VALUE('PV_PARTNER_MATCHING_TYPE'), 'STOP_AT_FIRST_RULE');
383
384
385 -- ---------------------------------------------------------------------------------
386 -- Cache all the opportunity selection rules (attributes-values) in a global
387 -- PL/SQL table, g_opp_selection_tab.
388 -- ---------------------------------------------------------------------------------
389 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
390 Debug('----------------------------------------------------------------');
391 Debug('g_matching_engine_type: ' || g_matching_engine_type);
392 Debug('Matching Type: ' || l_matching_type);
393 Debug('----------------------------------------------------------------');
394
395 Debug('Clear Rules Cache.......................................');
396 END IF;
397
398 Clear_Rules_Cache;
399
400 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
401 Debug('Rule Caching............................................');
402 END IF;
403
404 l_start := dbms_utility.get_time;
405 Cache_Rules;
406
407 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
408 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
409 END IF;
410
411 l_count := g_opp_selection_tab.COUNT;
412
413 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
414 Debug('Number of Rule items Cached: ' || l_count);
415 END IF;
416
417 i := 1;
418
419 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
420 Debug('Opportunity Rule Selection Starts....................................');
421 END IF;
422
423 l_start := dbms_utility.get_time;
424
425 WHILE (i <= l_count AND (NOT l_stop_flag)) LOOP
426 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
427 Debug('******************************************************');
428 Debug('Rule # :::' || g_opp_selection_tab(i).process_rule_id);
429 END IF;
430
431 -- ---------------------------------------------------------------------------
432 -- If the opportunity's attribute value is not already retrieved, retrieve it.
433 -- ---------------------------------------------------------------------------
434 l_attribute_id := g_opp_selection_tab(i).attribute_id;
435
436 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
437 Debug('Attribute ID: ' || l_attribute_id);
438 Debug('Opportunity ID: ' || p_entity_id);
439 END IF;
440
441 pv_check_match_pub.Get_Entity_Attr_Values(
442 p_api_version_number => 1.0,
443 p_attribute_id => l_attribute_id,
444 p_entity => p_entity,
445 p_entity_id => p_entity_id,
446 p_delimiter => l_delimiter,
447 x_entity_attr_value => l_entity_attr_value,
448 x_return_status => l_return_status,
449 x_msg_count => l_msg_count,
450 x_msg_data => l_msg_data
451 );
452
453 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
454 RAISE FND_API.G_EXC_ERROR;
455
456 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
457 RAISE FND_API.g_exc_unexpected_error;
458 END IF;
459
460 -- ---------------------------------------------------------------------------
461 -- If the attribute value for this entity doesn't exist (which is different
462 -- from a NULL), there's a problem. It should have been caught in
463 -- Get_Entity_Attr_Values.
464 --
465 -- Advance to the next rule for evaluation.
466 -- ---------------------------------------------------------------------------
467 IF (NOT l_entity_attr_value.EXISTS(l_attribute_id)) THEN
468 i := Get_Next_Rule_Index(i, g_opp_selection_tab);
469
470 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
471 Debug('No attribute value for this attribute ' || l_attribute_id);
472 Debug('There is something wrong in the attribute setup.');
473 END IF;
474
475 ELSE
476
477 -- ---------------------------------------------------------------------------
478 -- Now we have the attribute value for this attribute, let's compare it with
479 -- the attribute value specified in the opportunity selection.
480 -- ---------------------------------------------------------------------------
481 l_matched := FALSE;
482
483 -- ---------------------------------------------------------------------------
484 -- AND logic...
485 -- ---------------------------------------------------------------------------
486 IF (g_opp_selection_tab(i).count = 1) THEN
487 -- ------------------------------------------------------------------------
488 -- Use operator to do the match. If the match fails, go to the next rule
489 -- until all rules are exhausted.
490 -- If the match succeeds, check if last_attr_flag = TRUE. If yes, there's
491 -- a match. set l_stop_flag = TRUE
492 -- ------------------------------------------------------------------------
493 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
494 Debug('Calling Check_Match...AND LOGIC...');
495 Debug('p_attribute_id: ' || l_attribute_id);
496 --Debug('p_entity_attr_value: ' || l_entity_attr_value(l_attribute_id).attribute_value);
497
498 l_attr_val_temp := l_entity_attr_value(l_attribute_id).attribute_value;
499 while (l_attr_val_temp is not null) loop
500 Debug('p_entity_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
501 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
502 end loop;
503
504 --Debug('p_rule_attr_value: ' || g_opp_selection_tab(i).attribute_value);
505 l_attr_val_temp := g_opp_selection_tab(i).attribute_value;
506 while (l_attr_val_temp is not null) loop
507 Debug('p_rule_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
508 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
509 end loop;
510
511 Debug('p_operator: ' || g_opp_selection_tab(i).operator);
512 Debug('p_delimiter: ' || l_delimiter);
513 Debug('p_return_type: ' || l_entity_attr_value(l_attribute_id).return_type);
514 END IF;
515
516
517 l_matched := pv_check_match_pub.Check_Match(
518 p_attribute_id => l_attribute_id,
519 p_entity_attr_value => l_entity_attr_value(l_attribute_id).attribute_value,
520 p_rule_attr_value => g_opp_selection_tab(i).attribute_value,
521 p_rule_to_attr_value => g_opp_selection_tab(i).attribute_to_value,
522 p_operator => g_opp_selection_tab(i).operator,
523 p_input_filter => l_input_filter,
524 p_delimiter => l_delimiter,
525 p_return_type => l_entity_attr_value(l_attribute_id).return_type,
526 p_rule_currency_code => g_opp_selection_tab(i).currency_code
527 );
528
529 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
530 IF (l_matched) THEN
531 Debug('Check_Match TRUE!');
532 ELSE
533 Debug('Check_Match FALSE!');
534 END IF;
535 END IF;
536
537 -- ---------------------------------------------------------------------------
538 -- We have an OR logic here. Need special processing...Concatenate all the
539 -- attribute values involved in the OR logic into one long string, and pass
540 -- this string as the attribute value into Check_Match function.
541 -- ---------------------------------------------------------------------------
542 ELSE
543 -- -------------------------------------------------------------
544 -- l_stop_at_index is the index where the current OR logic ends.
545 -- -------------------------------------------------------------
546 l_stop_at_index := i + g_opp_selection_tab(i).count - 1;
547 l_concat_attr_val := l_delimiter;
548
549 FOR j IN i..l_stop_at_index LOOP
550 l_concat_attr_val := l_concat_attr_val ||
551 g_opp_selection_tab(j).attribute_value || l_delimiter;
552
553 l_concat_to_attr_val := l_concat_to_attr_val ||
554 g_opp_selection_tab(j).attribute_to_value || l_delimiter;
555 END LOOP;
556
557 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
558 Debug('Calling Check_Match...OR Logic');
559 Debug('p_attribute_id: ' || l_attribute_id);
560 --Debug('p_entity_attr_value: ' || l_entity_attr_value(l_attribute_id).attribute_value);
561 l_attr_val_temp := l_entity_attr_value(l_attribute_id).attribute_value;
562 while (l_attr_val_temp is not null) loop
563 Debug('p_entity_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
564 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
565 end loop;
566
567 --Debug('p_rule_attr_value: ' || l_concat_attr_val);
568 l_attr_val_temp := l_concat_attr_val;
569 while (l_attr_val_temp is not null) loop
570 Debug('p_rule_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
571 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
572 end loop;
573
574 Debug('p_operator: ' || g_opp_selection_tab(i).operator);
575 Debug('p_delimiter: ' || l_delimiter);
576 Debug('p_return_type: ' || l_entity_attr_value(l_attribute_id).return_type);
577 END IF;
578
579 l_matched := pv_check_match_pub.Check_Match(
580 p_attribute_id => l_attribute_id,
581 p_entity_attr_value => l_entity_attr_value(l_attribute_id).attribute_value,
582 p_rule_attr_value => l_concat_attr_val,
583 p_rule_to_attr_value => l_concat_to_attr_val,
584 p_operator => g_opp_selection_tab(i).operator,
585 p_input_filter => l_input_filter,
586 p_delimiter => l_delimiter,
587 p_return_type => l_entity_attr_value(l_attribute_id).return_type,
588 p_rule_currency_code => g_opp_selection_tab(i).currency_code
589 );
590
591 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
592 IF (l_matched) THEN
593 Debug('Check_Match TRUE!');
594 ELSE
595 Debug('Check_Match FALSE!');
596 END IF;
597 END IF;
598
599 -- ------------------------------------------------------
600 -- Advance i to the last record involved in the OR logic.
601 -- ------------------------------------------------------
602 i := l_stop_at_index;
603 END IF;
604
605 -- ------------------------------------------------------------------------
606 -- The attribute value match fails. Advance to the next rule.
607 -- ------------------------------------------------------------------------
608 IF (NOT l_matched) THEN
609 -- ---------------------------------------------------------------------------
610 -- If there are no more rules in the group, it will be set to g_no_more_rules.
611 -- ---------------------------------------------------------------------------
612 i := Get_Next_Rule_Index(i, g_opp_selection_tab);
613
614 -- ------------------------------------------------------------------------
615 -- The attribute value match succeeds. Check if the current record is the
616 -- last record/attribute in the rule. If yes, there's a match between
617 -- this opportunity and the rule. Set l_stop_flag to TRUE to stop processing.
618 -- ------------------------------------------------------------------------
619 ELSE
620 IF (g_opp_selection_tab(i).last_attr_flag = 'Y') THEN
621 IF (l_matching_type = 'STOP_AT_FIRST_RULE') THEN
622 l_stop_flag := TRUE;
623 END IF;
624
625 -- -------------------------------------------------------------
626 -- The current rule is selected for this opportunity.
627 -- -------------------------------------------------------------
628 x_selected_rule_id := g_opp_selection_tab(i).process_rule_id;
629
630 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
631 Debug('%%%%%%%Selected Rule ID: ' || x_selected_rule_id);
632 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
633 Debug('Number of rule items scanned/evaluated before finding a matching rule: ' || i);
634 END IF;
635
636 -- -------------------------------------------------------------
637 -- Perform Partner Selection - call matching engine.
638 -- pv_match_pub package.form_where_clause --> pass in a record
639 -- of tables (operator, attribute, attribute_value) +
640 -- "selection mode" --> Only partners which match all search
641 -- attributes are returned.
642 -- -------------------------------------------------------------
643 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
644 Debug('..........................................................');
645 Debug('Calling Partner_Selection................................');
646 END IF;
647
648 Partner_Selection(
649 p_api_version => 1.0,
650 p_process_rule_id => x_selected_rule_id,
651 p_entity_id => p_entity_id,
652 p_entity => p_entity,
653 p_user_name => p_user_name,
654 p_resource_id => p_resource_id,
655 p_routing_flag => p_routing_flag,
656 p_incumbent_partner_only => 'N',
657 x_partner_tbl => x_partner_tbl,
658 x_partner_details => x_partner_details,
659 x_flagcount => x_flagcount,
660 x_distance_tbl => x_distance_tbl,
661 x_distance_uom_returned => x_distance_uom_returned,
662 x_return_status => l_return_status,
663 x_msg_count => l_msg_count,
664 x_msg_data => l_msg_data
665 );
666
667 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
668 RAISE FND_API.G_EXC_ERROR;
669
670 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
671 RAISE FND_API.g_exc_unexpected_error;
672 END IF;
673
674 -- ------------------------------------------------------------
675 -- Log the selected rules if the matching engine type is
676 -- 'BACKGROUND PARTNER MATCHING'. This provides a snapshot of what
677 -- actually occurred in partner matching.
678 -- ------------------------------------------------------------
679 IF (g_matching_engine_type = 'BACKGROUND_PARTNER_MATCHING') THEN
680 IF (x_partner_tbl.EXISTS(1) AND x_partner_tbl.COUNT > 0) THEN
681 l_winning_rule_flag := 'Y';
682 ELSE
683 l_winning_rule_flag := 'N';
684 END IF;
685
686 l_entity_rule_applied_id := null;
687
688 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
689 px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_id,
690 p_LAST_UPDATE_DATE => SYSDATE,
691 p_LAST_UPDATED_BY => p_resource_id,
692 p_CREATION_DATE => SYSDATE,
693 p_CREATED_BY => p_resource_id,
694 p_LAST_UPDATE_LOGIN => p_resource_id,
695 p_OBJECT_VERSION_NUMBER => 1,
696 p_REQUEST_ID => FND_API.G_MISS_NUM,
697 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
698 p_PROGRAM_ID => FND_API.G_MISS_NUM,
699 p_PROGRAM_UPDATE_DATE => SYSDATE,
700 p_ENTITY => p_entity,
701 p_ENTITY_ID => p_entity_id,
702 p_PROCESS_RULE_ID => x_selected_rule_id,
703 p_PARENT_PROCESS_RULE_ID => FND_API.G_MISS_NUM,
704 p_LATEST_FLAG => FND_API.G_MISS_CHAR,
705 p_ACTION_VALUE => FND_API.G_MISS_CHAR,
706 p_PROCESS_TYPE => 'BACKGROUND_PARTNER_MATCHING',
707 p_WINNING_RULE_FLAG => l_winning_rule_flag,
708 p_entity_detail => FND_API.G_MISS_CHAR,
709 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
710 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
711 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
712 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
713 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
714 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
715 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
716 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
717 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
718 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
719 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
720 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
721 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
722 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
723 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
724 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
725 p_PROCESS_STATUS => FND_API.G_MISS_CHAR
726 );
727 END IF;
728
729 -- ------------------------------------------------------------
730 -- If there are no partners returned, go on to the next rule
731 -- to find a matching partner until all rules are exhausted.
732 -- ------------------------------------------------------------
733 IF (l_matching_type = 'EXHAUST_ALL_RULES') THEN
734 IF (NOT x_partner_tbl.EXISTS(1) OR x_partner_tbl.COUNT = 0) THEN
735 -- reset failure code
736 g_failure_code := NULL;
737 i := i + 1;
738
739 ELSIF (x_partner_tbl.COUNT > 0) THEN
740 l_stop_flag := TRUE;
741 END IF;
742 END IF;
743
744 ELSE
745 -- ------------------------------
746 -- Advance to the next attribute
747 -- ------------------------------
748 i := i + 1;
749 END IF;
750 END IF;
751
752 END IF;
753 END LOOP;
754
755 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
756 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
757 END IF;
758
759 IF (NOT l_stop_flag) THEN
760 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
761 Debug('No matching rules found!!!!!');
762 END IF;
763
764 -- -------------------------------------------------------------------------
765 -- When no rules are found, still need to retrieve the incumbent (preferred)
766 -- partner, get its distance and details, and return it to the caller.
767 -- -------------------------------------------------------------------------
768 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
769 Debug('..........................................................');
770 Debug('Calling Partner_Selection for incumbent partner only......');
771 END IF;
772
773 Partner_Selection(
774 p_api_version => 1.0,
775 p_process_rule_id => null,
776 p_entity_id => p_entity_id,
777 p_entity => p_entity,
778 p_user_name => p_user_name,
779 p_resource_id => p_resource_id,
780 p_routing_flag => p_routing_flag,
781 p_incumbent_partner_only => 'Y',
782 x_partner_tbl => x_partner_tbl,
783 x_partner_details => x_partner_details,
784 x_flagcount => x_flagcount,
785 x_distance_tbl => x_distance_tbl,
786 x_distance_uom_returned => x_distance_uom_returned,
787 x_return_status => l_return_status,
788 x_msg_count => l_msg_count,
789 x_msg_data => l_msg_data
790 );
791
792
793 -- ----------------------------------------------------------
794 -- FAIL_TO_FIND_RULE...
795 -- ----------------------------------------------------------
796 IF (NOT x_partner_tbl.EXISTS(1)) THEN
797 g_failure_code := 'FAIL_TO_FIND_RULE';
798 END IF;
799
800 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
801 RAISE FND_API.G_EXC_ERROR;
802
803 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
804 RAISE FND_API.g_exc_unexpected_error;
805 END IF;
806 END IF;
807
808
809 IF (g_rule_engine_trace_flag = 'Y') THEN
810 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
811 p_count => x_msg_count,
812 p_data => x_msg_data);
813 END IF;
814
815 -------------------- Exception --------------------------
816 EXCEPTION
817 WHEN FND_API.G_EXC_ERROR THEN
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
820 p_count => x_msg_count,
821 p_data => x_msg_data);
822
823 WHEN FND_API.g_exc_unexpected_error THEN
824 x_return_status := FND_API.g_ret_sts_unexp_error;
825 FND_MSG_PUB.count_and_get(
826 p_encoded => FND_API.g_false,
827 p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830
831 WHEN g_e_buffer_too_small THEN
832 x_return_status := l_return_status;
833
834 WHEN OTHERS THEN
835 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
836 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
837 END IF;
838
839 x_return_status := FND_API.G_RET_STS_ERROR;
840 FND_MSG_PUB.count_and_get(
841 p_encoded => FND_API.g_false,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846
847 END Opportunity_Selection;
848 -- ===========================End of Opportunity_Selection===========================
849
850
851 --=============================================================================+
852 --| Procedure |
853 --| |
854 --| Partner_Selection |
855 --| |
856 --| |
857 --| Parameters |
858 --| IN |
859 --| OUT |
860 --| |
861 --| |
862 --| NOTES |
863 --| |
864 --| HISTORY |
865 --| |
866 --==============================================================================
867 PROCEDURE Partner_Selection(
868 p_api_version IN NUMBER,
869 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
870 p_commit IN VARCHAR2 := FND_API.g_false,
871 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
872 p_process_rule_id IN NUMBER,
873 p_entity_id IN NUMBER,
874 p_entity IN VARCHAR2,
875 p_user_name IN VARCHAR2 := NULL,
876 p_resource_id IN NUMBER := NULL,
877 p_routing_flag IN VARCHAR2,
878 p_incumbent_partner_only IN VARCHAR2 := 'N',
879 x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE,
880 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
881 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
882 x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
883 x_distance_uom_returned OUT NOCOPY VARCHAR2,
884 x_return_status OUT NOCOPY VARCHAR2,
885 x_msg_count OUT NOCOPY NUMBER,
886 x_msg_data OUT NOCOPY VARCHAR2
887 )
888 IS
889 -- -------------------------------------------------------------------------
890 -- Cursor for retrieving partner-to-opportunity mapping for a process rule.
891 -- -------------------------------------------------------------------------
892 CURSOR lc_partner_mapping IS
893 SELECT a.source_attr_id, a.target_attr_id, a.operator, b.return_type
894 FROM pv_entity_attr_mappings a,
895 pv_attributes_vl b
896 WHERE a.target_attr_id = b.attribute_id AND
897 a.process_rule_id = p_process_rule_id AND
898 a.source_attr_type = 'LEAD' AND
899 --a.source_attr_type = 'OPPORTUNITY' AND
900 a.target_attr_type = 'PARTNER';
901
902 -- -------------------------------------------------------------------------
903 -- Cursor for retrieving partner selection attribute-value pairs.
904 -- -------------------------------------------------------------------------
905 CURSOR lc_partner_selection IS
906 SELECT a.attribute_id, a.operator,
907 b.attribute_value, b.attribute_to_value,
908 a.selection_criteria_id,
909 c.return_type
910 FROM pv_enty_select_criteria a,
911 pv_selected_attr_values b,
912 pv_attributes_vl c
913 WHERE a.attribute_id = c.attribute_id AND
914 a.selection_criteria_id = b.selection_criteria_id (+) AND
915 a.selection_type_code = 'PARTNER_SELECTION' AND
916 a.process_rule_id = p_process_rule_id
917 ORDER BY a.attribute_id, b.selection_criteria_id;
918
919 -- -------------------------------------------------------------------------
920 -- Cursor for retrieving geo proximity and routing information.
921 -- -------------------------------------------------------------------------
922 CURSOR lc_entity_routings (p_selected_rule_id IN NUMBER) IS
923 SELECT entity_routing_id, max_nearest_partner, distance_from_customer,
924 distance_uom_code, routing_type,
925 NVL(bypass_cm_ok_flag, 'N') bypass_cm_ok_flag
926 FROM pv_entity_routings
927 WHERE process_rule_id = p_selected_rule_id;
928
929 -- -------------------------------------------------------------------------
930 -- Cursor for retrieving location_id for the opportunity (customer).
931 -- -------------------------------------------------------------------------
932 CURSOR lc_get_location_id (p_entity_id IN NUMBER) IS
933 SELECT b.location_id
934 FROM as_leads_all a,
935 hz_party_sites b,
936 hz_locations l
937 WHERE a.lead_id = p_entity_id AND
938 a.customer_id = b.party_id AND
939 b.party_site_id = a.address_id AND
940 b.location_id = l.location_id AND
941 l.geometry IS NOT NULL;
942
943 -- -------------------------------------------------------------------------
944 -- Cursor for retrieving the process rule name.
945 -- -------------------------------------------------------------------------
946 CURSOR lc_get_process_rule_name (p_process_rule_id IN NUMBER) IS
947 SELECT process_rule_name
948 FROM pv_process_rules_vl
949 WHERE process_rule_id = p_process_rule_id;
950
951
952 l_entity_routing lc_entity_routings%ROWTYPE;
953
954 l_api_version NUMBER := 1.0;
955 l_api_name VARCHAR2(30) := 'Partner_Selection';
956 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
957 l_temp VARCHAR2(4000);
958 l_num_of_tokens NUMBER;
959 l_attribute_id NUMBER;
960 l_attribute_value VARCHAR2(4000);
961 l_return_status VARCHAR2(100);
962 l_msg_count NUMBER;
963 l_msg_data VARCHAR2(500);
964 l_delimiter VARCHAR2(10) := '+++';
965 l_start NUMBER;
966 i NUMBER := 1;
967 j NUMBER;
968 k NUMBER := 1;
969 l_resource_id NUMBER;
970 l_user_name VARCHAR2(100);
971 l_first_record BOOLEAN := TRUE;
972 l_previous_attr_id NUMBER;
973 l_previous_sc_id NUMBER;
974 l_previous_operator VARCHAR2(100);
975 l_previous_return_type VARCHAR2(100);
976
977 l_customer_address pv_locator.party_address_rec_type;
978 l_distance_uom VARCHAR2(30);
979 --l_distance_uom_returned VARCHAR2(30);
980
981 l_attr_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
982 l_attr_value_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
983 l_attr_operator_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
984 l_attr_data_type_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
985
986 l_source_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
987 l_rank_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
988 l_extra_partner_details JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
989
990 -- l_partner_tbl_temp JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
991 l_partner_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
992 -- vansub
993 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
994 --
995 l_partner_details JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
996 l_flagcount JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
997
998 l_stop_flag BOOLEAN := FALSE;
999
1000 l_partner_distance_tbl DBMS_SQL.NUMBER_TABLE;
1001 l_preferred_partner_party_id NUMBER;
1002 l_distance_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1003 l_rule_currency_code VARCHAR2(15);
1004 l_entity_routings_exists BOOLEAN := TRUE;
1005 l_preferred_idx NUMBER;
1006 l_process_rule_name VARCHAR2(100);
1007 l_attr_val_temp VARCHAR2(4000);
1008
1009 BEGIN
1010 -------------------- initialize -------------------------
1011 IF FND_API.to_boolean(p_init_msg_list) THEN
1012 FND_MSG_PUB.initialize;
1013 END IF;
1014
1015 IF NOT FND_API.compatible_api_call(
1016 l_api_version,
1017 p_api_version,
1018 l_api_name,
1019 g_pkg_name
1020 ) THEN
1021 RAISE FND_API.g_exc_unexpected_error;
1022 END IF;
1023
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026 -------------------------- Source code --------------------
1027
1028 -- ------------------------------------------------------------------------
1029 -- Make sure that either p_user_name or p_resource IS NOT NULL.
1030 -- ------------------------------------------------------------------------
1031 IF (p_user_name IS NULL AND p_resource_id IS NULL) THEN
1032 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1033 p_msg_name => 'PV_NO_USERNAME_ID_DEFINED',
1034 p_token1 => null,
1035 p_token1_value => null,
1036 p_token2 => null,
1037 p_token2_value => null);
1038
1039 g_failure_code := 'OTHER';
1040 RAISE FND_API.G_EXC_ERROR;
1041 END IF;
1042
1043
1044 -- ------------------------------------------------------------------------
1045 -- Retrieve profile value for stack trace profile option.
1046 -- ------------------------------------------------------------------------
1047 --g_rule_engine_trace_flag := NVL(FND_PROFILE.VALUE('PV_RULE_ENGINE_TRACE_ON'), 'N');
1048
1049 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1050 Debug('............................................................');
1051 Debug('Stack trace turned on? ' || g_rule_engine_trace_flag);
1052 Debug('............................................................');
1053
1054 Debug(' ');
1055 Debug('Routing Flag is ' || p_routing_flag);
1056 Debug('***Rule ID Selected Is: ' || p_process_rule_id || '***');
1057 END IF;
1058
1059 -- -------------------------------------------------------------
1060 -- Retrieve Entity Routings info (geo proximity and routings)
1061 -- -------------------------------------------------------------
1062 OPEN lc_entity_routings(p_process_rule_id);
1063 FETCH lc_entity_routings INTO l_entity_routing;
1064
1065 IF (lc_entity_routings%NOTFOUND) THEN
1066 l_entity_routings_exists := FALSE;
1067 END IF;
1068
1069 CLOSE lc_entity_routings;
1070
1071 -- -------------------------------------------------------------
1072 -- Retrieve location_id for the opportunity (customer).
1073 -- -------------------------------------------------------------
1074 FOR x IN lc_get_location_id(p_entity_id) LOOP
1075 l_customer_address.location_id := x.location_id;
1076 END LOOP;
1077
1078 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1079 Debug('***l_entity_routing.max_nearest_partner: ' || l_entity_routing.max_nearest_partner || '***');
1080 Debug('***l_entity_routing.distance_from_customer: ' || l_entity_routing.distance_from_customer || '***');
1081 END IF;
1082
1083 IF (l_entity_routings_exists AND
1084 (l_entity_routing.max_nearest_partner IS NOT NULL OR
1085 l_entity_routing.distance_from_customer IS NOT NULL) AND
1086 l_customer_address.location_id IS NULL)
1087 THEN
1088 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1089 p_msg_name => 'PV_NO_GEOMETRY_INFO',
1090 p_token1 => 'TEXT',
1091 p_token1_value => 'Entity ID: ' || p_entity_id,
1092 p_token2 => null,
1093 p_token2_value => null);
1094
1095 g_failure_code := 'OTHER';
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099
1100 -- ------------------------------------------------------------------------
1101 -- Retrieve the rule's currency_code.
1102 -- ------------------------------------------------------------------------
1103 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1104 Debug ('p_incumbent_partner_only flag: ' || p_incumbent_partner_only);
1105 END IF;
1106
1107 IF (p_incumbent_partner_only = 'N') THEN
1108 BEGIN
1109 SELECT currency_code
1110 INTO l_rule_currency_code
1111 FROM pv_process_rules_b
1112 WHERE process_rule_id = p_process_rule_id;
1113
1114 EXCEPTION
1115 WHEN NO_DATA_FOUND THEN
1116 IF (l_process_rule_name IS NULL) THEN
1117 FOR x IN lc_get_process_rule_name(p_process_rule_id) LOOP
1118 l_process_rule_name := x.process_rule_name;
1119 END LOOP;
1120 END IF;
1121
1122 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1123 p_msg_name => 'PV_DEBUG_MSG',
1124 p_token1 => 'TEXT',
1125 p_token1_value => 'This rule "' || l_process_rule_name ||
1126 '" (ID: ' || p_process_rule_id || ') does not exist',
1127 p_token2 => null,
1128 p_token2_value => null);
1129
1130 g_failure_code := 'OTHER';
1131 RAISE FND_API.G_EXC_ERROR;
1132 END;
1133
1134 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1135 Debug('Rule Currency is: ' || l_rule_currency_code);
1136 END IF;
1137
1138 -- ========================================================================
1139 -- Opportunity-Partner Attribute Mapping =
1140 -- ========================================================================
1141 -- ------------------------------------------------------------------------
1142 -- Loop through opportunity-partner attribute mappings and retrieve the
1143 -- attribute values of each of the attributes in mapping.
1144 -- ------------------------------------------------------------------------
1145 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1146 Debug('Retrieve opportunity-to-partner attribute mapping..................');
1147 END IF;
1148
1149 FOR lc_cursor IN lc_partner_mapping LOOP
1150 l_attribute_id := lc_cursor.source_attr_id;
1151
1152 -- ---------------------------------------------------------------------
1153 -- Retrieve opportunity's attribute value if it hasn't already been
1154 -- retrieved.
1155 -- ---------------------------------------------------------------------
1156 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1157 Debug('Attribute ID before calling Get_Entity_Attr_Values: ' || l_attribute_id);
1158 --Debug('Entity Type: ' || p_entity);
1159 END IF;
1160
1161 pv_check_match_pub.Get_Entity_Attr_Values(
1162 p_api_version_number => 1.0,
1163 p_attribute_id => l_attribute_id,
1164 p_entity => p_entity,
1165 p_entity_id => p_entity_id,
1166 p_delimiter => l_delimiter,
1167 p_expand_attr_flag => 'N',
1168 x_entity_attr_value => l_entity_attr_value,
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data
1172 );
1173
1174 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1175 g_failure_code := 'OTHER';
1176 RAISE FND_API.G_EXC_ERROR;
1177
1178 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1179 g_failure_code := 'OTHER';
1180 RAISE FND_API.g_exc_unexpected_error;
1181
1182 ELSE
1183 -- ---------------------------------------------------------------------
1184 -- Note that l_entity_attr_value stores concatenated attribute values
1185 -- with delimiters in the beginning and end of the string.
1186 -- e.g. +++USA+++UK+++
1187 --
1188 -- However, pv_match_pub.form_where_clause API expects a string without
1189 -- leading and trailing delimiters:
1190 -- e.g. USA+++UK
1191 --
1192 -- Therefore, we need to take these two delimiters out of the string
1193 -- before passing it to the API as a parameter.
1194 -- ---------------------------------------------------------------------
1195 l_temp := l_entity_attr_value(l_attribute_id).attribute_value;
1196
1197 -- ---------------------------------------------------------------------
1198 -- Populate PL/SQL table only when the attribute value string IS NOT NULL.
1199 -- ---------------------------------------------------------------------
1200 IF (l_temp IS NOT NULL AND l_temp <> '++++++') THEN
1201 l_num_of_tokens := pv_check_match_pub.Get_Num_Of_Tokens(l_delimiter, l_temp);
1202
1203 l_attr_id_tbl.EXTEND(l_num_of_tokens);
1204 l_attr_value_tbl.EXTEND(l_num_of_tokens);
1205 l_attr_operator_tbl.EXTEND(l_num_of_tokens);
1206 l_attr_data_type_tbl.EXTEND(l_num_of_tokens);
1207
1208 -- ------------------------------------------------------------------
1209 -- Everything under the mapping section should be treated as AND
1210 -- condition, which requires one table element per item.
1211 -- ------------------------------------------------------------------
1212 FOR j IN 1..l_num_of_tokens LOOP
1213 l_attribute_value := pv_check_match_pub.Retrieve_Token(
1214 p_delimiter => l_delimiter,
1215 p_attr_value_string => l_temp,
1216 p_input_type => 'STD TOKEN',
1217 p_index => j
1218 );
1219
1220 /*
1221 IF (lc_cursor.return_type = 'CURRENCY') THEN
1222 l_attribute_value := l_attribute_value || ':::' || l_rule_currency_code ||
1223 TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1224 END IF;
1225 */
1226 l_attr_id_tbl(i) := lc_cursor.target_attr_id;
1227 l_attr_value_tbl(i) := l_attribute_value;
1228 l_attr_operator_tbl(i) := lc_cursor.operator;
1229 l_attr_data_type_tbl(i) := lc_cursor.return_type;
1230
1231 i := i + 1;
1232 END LOOP;
1233 END IF;
1234 END IF;
1235 END LOOP;
1236
1237 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1238 Debug('Partner Matching/Mapping Attributes...');
1239
1240 FOR i IN 1..l_attr_id_tbl.COUNT LOOP
1241
1242 l_attr_val_temp := l_attr_value_tbl(i);
1243 while (l_attr_val_temp is not null) loop
1244 Debug(i || '-->' ||l_attr_id_tbl(i) || ': ' || substr( l_attr_val_temp, 1, 1800 ) ||
1245 ':::' || l_attr_operator_tbl(i) || ':::' || l_attr_data_type_tbl(i));
1246 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
1247 end loop;
1248
1249
1250 END LOOP;
1251
1252 Debug('-----------------------------------------');
1253
1254 Debug('Appending Partner Selection Attributes...');
1255 END IF;
1256
1257 k := i;
1258
1259 -- ========================================================================
1260 -- Partner Selection Attributes =
1261 -- ========================================================================
1262 -- ------------------------------------------------------------------------
1263 -- Get partner selection attribute value and append them to the record
1264 -- of tables, l_match_attr_rec.
1265 -- The following code also performs AND/OR logic. Attribute values
1266 -- involved in an OR logic will be concatenated in a string separated
1267 -- by a delimiter.
1268 -- ------------------------------------------------------------------------
1269 FOR x IN lc_partner_selection LOOP
1270 IF (l_previous_attr_id = x.attribute_id AND
1271 l_previous_sc_id = x.selection_criteria_id)
1272 THEN
1273 l_attr_value_tbl(i - 1) := l_attr_value_tbl(i - 1) ||
1274 l_delimiter || x.attribute_value;
1275
1276 IF (x.return_type = 'CURRENCY') THEN
1277 l_attr_value_tbl(i - 1) := l_attr_value_tbl(i - 1) || ':::' ||
1278 l_rule_currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1279 END IF;
1280
1281 ELSE
1282 l_attr_id_tbl.EXTEND;
1283 l_attr_value_tbl.EXTEND;
1284 l_attr_operator_tbl.EXTEND;
1285 l_attr_data_type_tbl.EXTEND;
1286
1287 l_attr_value_tbl(i) := x.attribute_value;
1288 l_attr_id_tbl(i) := x.attribute_id;
1289 l_attr_data_type_tbl(i) := x.return_type;
1290 l_attr_operator_tbl(i) := x.operator;
1291
1292 IF (x.return_type = 'CURRENCY') THEN
1293 l_attr_value_tbl(i) := l_attr_value_tbl(i) || ':::' ||
1294 l_rule_currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1295 END IF;
1296
1297 IF (x.operator = 'BETWEEN') THEN
1298 l_attr_operator_tbl(i) := '>=';
1299
1300 i := i + 1;
1301 l_attr_id_tbl.EXTEND;
1302 l_attr_value_tbl.EXTEND;
1303 l_attr_operator_tbl.EXTEND;
1304 l_attr_data_type_tbl.EXTEND;
1305 l_attr_operator_tbl(i) := '<=';
1306 l_attr_id_tbl(i) := x.attribute_id;
1307 l_attr_data_type_tbl(i) := x.return_type;
1308 l_attr_value_tbl(i) := x.attribute_to_value;
1309
1310 IF (x.return_type = 'CURRENCY') THEN
1311 l_attr_value_tbl(i) := l_attr_value_tbl(i) ||
1312 ':::' || 'USD' || ':::' ||
1313 TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1314 END IF;
1315 END IF;
1316
1317 i := i + 1;
1318 END IF;
1319
1320 l_previous_attr_id := x.attribute_id;
1321 l_previous_sc_id := x.selection_criteria_id;
1322 END LOOP;
1323
1324
1325 -- ------------------------------------------------------------------------
1326 -- For debugging only...
1327 -- ------------------------------------------------------------------------
1328 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1329 FOR i IN k..l_attr_id_tbl.COUNT LOOP
1330 l_attr_val_temp := l_attr_value_tbl(i);
1331 while (l_attr_val_temp is not null) loop
1332 Debug(i || '-->' || l_attr_id_tbl(i) || ': ' || substr( l_attr_val_temp, 1, 1800 ) ||
1333 ':::' || l_attr_operator_tbl(i) || ':::' || l_attr_data_type_tbl(i));
1334 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
1335 end loop;
1336 END LOOP;
1337 END IF;
1338
1339 -- ------------------------------------------------------------------------
1340 -- Perform Partner Matching...
1341 -- ------------------------------------------------------------------------
1342 IF (p_resource_id IS NULL) THEN
1343 SELECT resource_id
1344 INTO l_resource_id
1345 FROM fnd_user a, jtf_rs_resource_extns b
1346 WHERE a.user_id = b.user_id AND
1347 a.user_name = p_user_name;
1348 ELSE
1349 l_resource_id := p_resource_id;
1350 END IF;
1351
1352 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1353 Debug('..........................................................');
1354 Debug('Calling Form_Where_Clause.......................');
1355 END IF;
1356
1357 l_start := DBMS_UTILITY.get_time;
1358
1359 pv_match_v2_pub.Form_Where_Clause(
1360 p_api_version_number => p_api_version,
1361 p_attr_id_tbl => l_attr_id_tbl,
1362 p_attr_value_tbl => l_attr_value_tbl,
1363 p_attr_operator_tbl => l_attr_operator_tbl,
1364 p_attr_data_type_tbl => l_attr_data_type_tbl,
1365 p_attr_selection_mode => 'OR',
1366 p_att_delmter => l_delimiter,
1367 p_selection_criteria => 'ALL',
1368 p_resource_id => l_resource_id,
1369 p_lead_id => p_entity_id,
1370 p_auto_match_flag => 'N',
1371 x_matched_id => x_partner_tbl,
1372 x_return_status => l_return_status,
1373 x_msg_count => l_msg_count,
1374 x_msg_data => l_msg_data
1375 );
1376
1377 -- -----------------------------------------------------------
1378 -- RULE_FOUND_NO_PARTNER...
1379 -- -----------------------------------------------------------
1380 IF (NOT x_partner_tbl.EXISTS(1)) THEN
1381 g_failure_code := 'RULE_FOUND_NO_PARTNER';
1382 END IF;
1383
1384 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1385 RAISE FND_API.G_EXC_ERROR;
1386
1387 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1388 RAISE FND_API.g_exc_unexpected_error;
1389 END IF;
1390
1391 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1392 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1393
1394 Debug('# of Partners Matched: ' || x_partner_tbl.COUNT);
1395 Debug('Partners Matched: ');
1396
1397 FOR i IN 1..x_partner_tbl.COUNT LOOP
1398 Debug(x_partner_tbl(i) || ',');
1399 END LOOP;
1400
1401 Debug('..........................................................');
1402 Debug('Retrieving GEO Proximity and Routings Info...');
1403 END IF;
1404
1405
1406 -- -------------------------------------------------------------
1407 -- Geographic proximity restrictions.
1408 -- -------------------------------------------------------------
1409 IF (l_entity_routing.entity_routing_id IS NOT NULL) THEN
1410 IF (l_entity_routing.distance_uom_code = 'KILOMETERS') THEN
1411 l_distance_uom := pv_locator.g_distance_unit_km;
1412
1413 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1414 Debug('Distance Unit: ' || l_distance_uom);
1415 END IF;
1416
1417 ELSIF (l_entity_routing.distance_uom_code = 'MILES') THEN
1418 l_distance_uom := pv_locator.g_distance_unit_mile;
1419
1420 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1421 Debug('Distance Unit: ' || l_distance_uom);
1422 END IF;
1423
1424 ELSE
1425 --l_distance_uom := pv_locator.g_distance_unit_mile;
1426
1427 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1428 Debug('No Distance Unit Specified. Use the default from the pv_locator profile.');
1429 END IF;
1430 END IF;
1431
1432 ELSE
1433 --l_distance_uom := pv_locator.g_distance_unit_mile;
1434
1435 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1436 Debug('No Distance Unit Specified. Use the default from the pv_locator profile.');
1437 END IF;
1438 END IF;
1439
1440
1441 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1442 Debug('Geo Proximity and Routing Parameters..............................');
1443 Debug('Location ID: ' || l_customer_address.location_id);
1444 Debug('Distance from partner: ' || l_entity_routing.distance_from_customer);
1445 Debug('Max # of partners to be returned: ' || l_entity_routing.max_nearest_partner);
1446 Debug('Distance UOM Code: ' || l_entity_routing.distance_uom_code);
1447 Debug('Routing Type: ' || l_entity_routing.routing_type);
1448 END IF;
1449
1450 -- ------------------------------------------------------------------------
1451 -- Perform Geo Proximity Restrictions...
1452 --
1453 -- Execute Geo Proximity API only when there is at least one partner
1454 -- returned from Partner Matching above.
1455 -- ------------------------------------------------------------------------
1456
1457 l_partner_id_tbl := x_partner_tbl;
1458
1459 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1460 Debug('# of Partners Sent: ' || x_partner_tbl.COUNT);
1461 END IF;
1462
1463
1464 IF (l_partner_id_tbl.EXISTS(1) AND l_partner_id_tbl.COUNT > 0) THEN
1465 -- -------------------------------------------------------------
1466 -- Execute geo proximity API.
1467 -- -------------------------------------------------------------
1468 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1469 Debug('..........................................................');
1470 Debug('Calling pv_locator.Get_Partners..........................');
1471 Debug('# of Partners Sent: ' || l_partner_id_tbl.COUNT);
1472 END IF;
1473
1474 l_start := DBMS_UTILITY.get_time;
1475
1476 pv_locator.Get_Partners (
1477 p_api_version => p_api_version,
1478 p_customer_address => l_customer_address,
1479 p_partner_tbl => l_partner_id_tbl,
1480 p_max_no_partners => l_entity_routing.max_nearest_partner,
1481 p_distance => l_entity_routing.distance_from_customer,
1482 p_distance_unit => l_distance_uom,
1483 x_partner_tbl => x_partner_tbl,
1484 x_distance_tbl => x_distance_tbl,
1485 x_distance_unit => x_distance_uom_returned,
1486 x_return_status => l_return_status,
1487 x_msg_count => l_msg_count,
1488 x_msg_data => l_msg_data
1489 );
1490
1491 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1492 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1493 RAISE FND_API.G_EXC_ERROR;
1494
1495 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1496 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1497 RAISE FND_API.g_exc_unexpected_error;
1498 END IF;
1499
1500 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1501 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1502 END IF;
1503
1504 IF (x_distance_uom_returned = pv_locator.g_distance_unit_km) THEN
1505 x_distance_uom_returned := 'KILOMETERS';
1506
1507 ELSIF (x_distance_uom_returned = pv_locator.g_distance_unit_mile) THEN
1508 x_distance_uom_returned := 'MILES';
1509 END IF;
1510
1511 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1512 Debug('Distance UOM returned is: ' || x_distance_uom_returned);
1513 Debug('# of Partners Returned: ' || x_partner_tbl.COUNT);
1514 Debug('Partners Matched and distance to customer: ');
1515 END IF;
1516
1517
1518 -- -------------------------------------------------------------
1519 -- Store partners' distance to customer.
1520 -- -------------------------------------------------------------
1521 FOR i IN 1..x_partner_tbl.COUNT LOOP
1522 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1523 Debug(x_partner_tbl(i) || ':::' || TRUNC(x_distance_tbl(i), 2));
1524 END IF;
1525
1526 l_partner_distance_tbl(x_partner_tbl(i)) := x_distance_tbl(i);
1527 END LOOP;
1528
1529
1530 --Debug('...........Saved partner distance info..............');
1531 i := l_partner_distance_tbl.FIRST;
1532
1533 WHILE (i <= l_partner_distance_tbl.LAST) LOOP
1534 --Debug(i || ':::' || TRUNC(l_partner_distance_tbl(i), 2));
1535 i := l_partner_distance_tbl.NEXT(i);
1536 END LOOP;
1537 END IF;
1538
1539 -- -------------------------------------------------------------
1540 -- Tie-breaker
1541 -- -------------------------------------------------------------
1542 -- IF (l_entity_routing.routing_type IN ('SERIAL', 'SINGLE') AND
1543 IF (x_partner_tbl.EXISTS(1) AND x_partner_tbl.COUNT > 1) THEN
1544 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1545 Debug('..........................................................');
1546 Debug('Calling Tie_Breaker......................................');
1547 Debug('# of Partners Sent: ' || x_partner_tbl.COUNT);
1548 END IF;
1549
1550 l_start := DBMS_UTILITY.get_time;
1551
1552 Tie_Breaker(
1553 p_api_version => p_api_version,
1554 p_process_rule_id => p_process_rule_id,
1555 x_partner_tbl => x_partner_tbl,
1556 x_return_status => l_return_status,
1557 x_msg_count => l_msg_count,
1558 x_msg_data => l_msg_data
1559 );
1560
1561 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1562 g_failure_code := 'OTHER';
1563 RAISE FND_API.G_EXC_ERROR;
1564
1565 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1566 g_failure_code := 'OTHER';
1567 RAISE FND_API.g_exc_unexpected_error;
1568 END IF;
1569
1570 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1571 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1572 END IF;
1573 END IF;
1574
1575 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1576 Debug('# of Partners Returned: ' || x_partner_tbl.COUNT);
1577 END IF;
1578
1579 -- ------------------------------------------------------------------------------
1580 -- Save the partner list order after tie-breaking.
1581 -- This may not be necessary anymore.............................................
1582 -- ------------------------------------------------------------------------------
1583 -- l_partner_tbl_temp := x_partner_tbl;
1584
1585
1586 END IF; -- IF (p_incumbent_partner_only = 'N')
1587 -- =======================p_incumbent_partner_only = 'N' ==========================
1588
1589 -- ------------------------------------------------------------------------------
1590 -- Preferred Partner.......................................................
1591 -- Find out if there are any preferred partner for this opportunity. If yes,
1592 -- retrieve the distance to customer of this partner and stick it in the
1593 -- beginning of the partner list.
1594 --
1595 -- Note that preferred partner(s) should always be placed on top of the
1596 -- partner list and it should never be involved in tie-breaking and
1597 -- geo proximity elimination.
1598 -- ------------------------------------------------------------------------------
1599 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1600 Debug('..........................................................');
1601 Debug('Retrieving the preferred partner..........................');
1602 END IF;
1603
1604 SELECT INCUMBENT_PARTNER_PARTY_ID
1605 INTO l_preferred_partner_party_id
1606 FROM as_leads_all asla
1607 WHERE lead_id = p_entity_id;
1608
1609 -- ------------------------------------------------------------------------------
1610 -- Find out if the preferred partner is already in x_partner_tbl partner list.
1611 -- If it is, the SQL will also retrieve the index of the preferred partner
1612 -- in x_partner_tbl PLSQL table.
1613 -- ------------------------------------------------------------------------------
1614 IF (x_partner_tbl.EXISTS(1) AND l_preferred_partner_party_id IS NOT NULL) THEN
1615 FOR x IN (
1616 SELECT idx
1617 FROM (SELECT rownum idx, column_value party_id
1618 FROM (SELECT column_value
1619 FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE)))) a
1620 WHERE a.party_id = l_preferred_partner_party_id)
1621 LOOP
1622 l_preferred_idx := x.idx;
1623 END LOOP;
1624 END IF;
1625
1626 -- ------------------------------------------------------------------------------
1627 -- There is no preferred partner for this opportunity.
1628 -- ------------------------------------------------------------------------------
1629 IF (l_preferred_partner_party_id IS NULL) THEN
1630 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1631 Debug('There is no preferred partner..........................');
1632 END IF;
1633
1634 -- ------------------------------------------------------------------------------
1635 -- The preferred partner IS in x_partner_tbl partner list. Move it to the front
1636 -- of the list.
1637 -- Note that l_preferred_idx is the index of the preferred partner in
1638 -- x_partner_tbl, NOT the partner ID of the preferred partner.
1639 -- ------------------------------------------------------------------------------
1640 ELSIF (l_preferred_idx IS NOT NULL) THEN
1641 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1642 Debug('Preferred Partner Party ID: ' || l_preferred_partner_party_id);
1643 END IF;
1644
1645 IF (x_partner_tbl.COUNT > 1) THEN
1646 FOR i IN REVERSE 1..(l_preferred_idx - 1) LOOP
1647 x_partner_tbl(i + 1) := x_partner_tbl(i);
1648 END LOOP;
1649
1650 x_partner_tbl(1) := l_preferred_partner_party_id;
1651 END IF;
1652
1653 -- ------------------------------------------------------------------------------
1654 -- The preferred partner is not in x_partner_tbl partner list.
1655 -- ------------------------------------------------------------------------------
1656 ELSE
1657 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1658 Debug('Preferred Partner Party ID: ' || l_preferred_partner_party_id);
1659 END IF;
1660 l_start := DBMS_UTILITY.get_time;
1661
1662 -- ----------------------------------------------------------------------------
1663 -- If the preferred partner already exists in x_partner_tbl, delete it from
1664 -- x_partner_tbl. The reason for doing this is that preferred partner should
1665 -- be placed at the top of the list as done below.
1666 -- ----------------------------------------------------------------------------
1667 l_partner_tbl.EXTEND;
1668 l_partner_tbl(1) := l_preferred_partner_party_id;
1669 l_partner_id_tbl := l_partner_tbl;
1670
1671 pv_locator.Get_Partners (
1672 p_api_version => p_api_version,
1673 p_customer_address => l_customer_address,
1674 p_partner_tbl => l_partner_id_tbl,
1675 p_max_no_partners => null,
1676 p_distance => null,
1677 p_distance_unit => l_distance_uom,
1678 x_partner_tbl => l_partner_tbl,
1679 x_distance_tbl => l_distance_tbl,
1680 x_distance_unit => x_distance_uom_returned,
1681 x_return_status => l_return_status,
1682 x_msg_count => l_msg_count,
1683 x_msg_data => l_msg_data
1684 );
1685
1686
1687
1688 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1689 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1690 RAISE FND_API.G_EXC_ERROR;
1691
1692 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1693 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1694 RAISE FND_API.g_exc_unexpected_error;
1695 END IF;
1696
1697 IF (x_distance_uom_returned = pv_locator.g_distance_unit_km) THEN
1698 x_distance_uom_returned := 'KILOMETERS';
1699
1700 ELSIF (x_distance_uom_returned = pv_locator.g_distance_unit_mile) THEN
1701 x_distance_uom_returned := 'MILES';
1702 END IF;
1703
1704 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1705 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1706 Debug('Distance Unit Returned: ' || x_distance_uom_returned);
1707 END IF;
1708
1709 -- ---------------------------------------------------------------------------
1710 -- Store distance info...
1711 -- ---------------------------------------------------------------------------
1712 FOR i IN 1..l_distance_tbl.COUNT LOOP
1713 l_partner_distance_tbl(l_partner_tbl(i)) := l_distance_tbl(i);
1714
1715 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1716 Debug('Preferred Partner ' || l_partner_tbl(i) || ':::' ||
1717 TRUNC(l_partner_distance_tbl(l_partner_tbl(i)), 2));
1718 END IF;
1719 END LOOP;
1720
1721 -- ---------------------------------------------------------------------------
1722 -- Combine with x_partner_tbl by sticking l_partner_tbl at the beginning
1723 -- of x_partner_tbl.
1724 -- ---------------------------------------------------------------------------
1725 j := l_partner_tbl.COUNT;
1726
1727 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1728 Debug('# of preferred partners: ' || j);
1729 END IF;
1730
1731 IF ((NOT x_partner_tbl.EXISTS(1)) OR x_partner_tbl.COUNT = 0) THEN
1732 x_partner_tbl := l_partner_tbl;
1733
1734 ELSE
1735 l_partner_tbl.EXTEND(x_partner_tbl.COUNT);
1736
1737 FOR i IN 1..x_partner_tbl.COUNT LOOP
1738 l_partner_tbl(i + j) := x_partner_tbl(i);
1739 END LOOP;
1740
1741 x_partner_tbl := l_partner_tbl;
1742 END IF;
1743
1744
1745 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1746 Debug('Combined partner list..........................................');
1747
1748 FOR i IN 1..x_partner_tbl.COUNT LOOP
1749 Debug(x_partner_tbl(i));
1750 END LOOP;
1751 END IF;
1752 END IF;
1753
1754 -- ------------------------------------------------------------------------------
1755 -- Retreive partner's detail info.
1756 -- ------------------------------------------------------------------------------
1757 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1758 Debug('Retreive partners detail info.................................................');
1759 END IF;
1760
1761 -- IF (x_partner_tbl.EXISTS(1) AND x_partner_tbl.COUNT > 1) THEN
1762 IF (x_partner_tbl.EXISTS(1)) THEN
1763 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1764 Debug('..........................................................');
1765 Debug('Calling pv_match_v2_pub.Get_Matched_Partner_Details..........................');
1766 END IF;
1767
1768 l_start := DBMS_UTILITY.get_time;
1769
1770 pv_match_v2_pub.Get_Matched_Partner_Details(
1771 p_api_version_number => p_api_version,
1772 p_init_msg_list => FND_API.G_FALSE,
1773 p_commit => FND_API.G_FALSE,
1774 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1775 p_lead_id => p_entity_id,
1776 p_extra_partner_details => l_extra_partner_details,
1777 p_matched_id => x_partner_tbl,
1778 x_partner_details => x_partner_details,
1779 x_flagcount => x_flagcount,
1780 x_return_status => l_return_status,
1781 x_msg_count => l_msg_count,
1782 x_msg_data => l_msg_data);
1783
1784 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1785 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1786 Debug('Return Status: ' || l_return_status);
1787 END IF;
1788
1789 g_failure_code := 'OTHER';
1790 RAISE FND_API.G_EXC_ERROR;
1791
1792 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1793 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1794 Debug('Return Status: ' || l_return_status);
1795 END IF;
1796
1797 g_failure_code := 'OTHER';
1798 RAISE FND_API.g_exc_unexpected_error;
1799 END IF;
1800
1801 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1802 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1803 END IF;
1804
1805
1806 -- -----------------------------------------------------------------
1807 -- Debug
1808 -- -----------------------------------------------------------------
1809 /*
1810 Debug('==============================================================');
1811 FOR i IN 1..x_partner_tbl.COUNT LOOP
1812 Debug(x_partner_tbl(i));
1813 Debug(x_partner_details(i));
1814 END LOOP;
1815 */
1816
1817 -- ------------------------------------------------------------------
1818 -- Reassign distance to each partner.
1819 -- ------------------------------------------------------------------
1820 IF (NOT x_distance_tbl.EXISTS(1)) THEN
1821 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1822 Debug('Initialize x_distance_tbl...');
1823 END IF;
1824
1825 x_distance_tbl := JTF_NUMBER_TABLE();
1826 END IF;
1827
1828 -- ------------------------------------------------------------------
1829 -- Extend x_distance by the number of preferred partner(s).
1830 -- Do this if both of the following conditions are met:
1831 -- (1). There is a preferred partner.
1832 -- (2). l_preferred_idx is null which means that the preferred partner
1833 -- does not exist in x_partner_tbl. If it does, x_distance_tbl
1834 -- would not need to be extended.
1835 -- ------------------------------------------------------------------
1836 IF (l_preferred_partner_party_id IS NOT NULL AND l_preferred_idx IS NULL) THEN
1837 --Debug('Extending x_distance_tbl by ' || l_partner_tbl.COUNT || ' .........');
1838 x_distance_tbl.EXTEND(l_partner_tbl.COUNT);
1839 END IF;
1840
1841 IF (x_distance_tbl.EXISTS(1)) THEN
1842 FOR i IN 1..x_partner_tbl.COUNT LOOP
1843 x_distance_tbl(i) := l_partner_distance_tbl(x_partner_tbl(i));
1844 END LOOP;
1845
1846 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1847 Debug('Partner Details and distance info...........................');
1848 FOR i IN 1..x_partner_tbl.COUNT LOOP
1849 Debug(x_partner_tbl(i) || ':::' || TRUNC(x_distance_tbl(i), 2));
1850 Debug(x_partner_details(i));
1851 END LOOP;
1852 END IF;
1853 END IF;
1854 END IF;
1855
1856 -- ------------------------------------------------------------------------------
1857 -- Start the routing process only when p_routing_flag is set to 'Y'.
1858 --
1859 -- If a rule is available, but no routing info defined --> ERROR
1860 -- If no rule is available (as in the case when p_incumbent_partner_only flag
1861 -- = 'Y'), use default routing type defined in the following profiles:
1862 -- PV: Default Routing Type (PV_DEFAULT_ASSIGNMENT_TYPE)
1863 -- PV: Require Vendor User (CM) Approval for Manual Routing (PV_CM_APPROVAL_REQUIRED)
1864 -- ------------------------------------------------------------------------------
1865 IF (p_routing_flag = 'Y' AND x_partner_tbl.EXISTS(1) AND x_partner_tbl.COUNT > 0) THEN
1866 IF (p_process_rule_id IS NOT NULL) THEN
1867 IF ((NOT l_entity_routings_exists) OR l_entity_routing.routing_type IS NULL) THEN
1868 IF (l_process_rule_name IS NULL) THEN
1869 FOR x IN lc_get_process_rule_name(p_process_rule_id) LOOP
1870 l_process_rule_name := x.process_rule_name;
1871 END LOOP;
1872 END IF;
1873
1874 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1875 p_msg_name => 'PV_NO_GEO_ROUTING_DEFINED',
1876 p_token1 => 'RULE_ID',
1877 p_token1_value => p_process_rule_id,
1878 p_token2 => 'RULE_NAME',
1879 p_token2_value => l_process_rule_name);
1880
1881 g_failure_code := 'OTHER';
1882 RAISE FND_API.G_EXC_ERROR;
1883 END IF;
1884
1885 ELSE
1886 l_entity_routing.routing_type := FND_PROFILE.VALUE('PV_DEFAULT_ASSIGNMENT_TYPE');
1887 l_entity_routing.bypass_cm_ok_flag :=
1888 NVL(FND_PROFILE.VALUE('PV_CM_APPROVAL_REQUIRED'), 'N');
1889 END IF;
1890
1891 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1892 Debug('Routing Type: ' || l_entity_routing.routing_type);
1893 Debug('Bypass CM Approval: ' || l_entity_routing.bypass_cm_ok_flag);
1894 END IF;
1895
1896 l_source_tbl.EXTEND(x_partner_tbl.COUNT);
1897 l_rank_tbl.EXTEND(x_partner_tbl.COUNT);
1898
1899 FOR i IN 1..x_partner_tbl.COUNT LOOP
1900 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1901 Debug(x_partner_tbl(i) || ',');
1902 END IF;
1903
1904 l_source_tbl(i) := 'MATCHING';
1905 l_rank_tbl(i) := i;
1906 END LOOP;
1907
1908 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1909 Debug('Routing the opportunity to partner(s)....................');
1910 END IF;
1911
1912 l_start := DBMS_UTILITY.get_time;
1913
1914 -- -------------------------------------------------------------
1915 -- Retrieve user name if it's NULL.
1916 -- -------------------------------------------------------------
1917 IF (p_user_name IS NULL) THEN
1918 SELECT a.user_name
1919 INTO l_user_name
1920 FROM fnd_user a, jtf_rs_resource_extns b
1921 WHERE b.resource_id = p_resource_id AND
1922 a.user_id = b.user_id;
1923
1924 ELSE
1925 l_user_name := p_user_name;
1926 END IF;
1927
1928 -- -------------------------------------------------------------
1929 -- Route opportunity to partner(s).
1930 -- -------------------------------------------------------------
1931 pv_assignment_pub.createassignment(
1932 p_api_version_number => p_api_version,
1933 p_init_msg_list => FND_API.G_FALSE,
1934 p_commit => FND_API.G_FALSE,
1935 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1936 p_entity => 'OPPORTUNITY',
1937 p_lead_id => p_entity_id,
1938 p_creating_username => p_user_name,
1939 p_assignment_type => l_entity_routing.routing_type,
1940 p_bypass_cm_ok_flag => NVL(l_entity_routing.bypass_cm_ok_flag, 'N'),
1941 p_partner_id_tbl => x_partner_tbl,
1942 p_rank_tbl => l_rank_tbl,
1943 p_partner_source_tbl => l_source_tbl,
1944 p_process_rule_id => p_process_rule_id,
1945 x_return_status => l_return_status,
1946 x_msg_count => l_msg_count,
1947 x_msg_data => l_msg_data
1948 );
1949
1950 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1951 g_failure_code := 'ROUTING_FAILED';
1952 RAISE FND_API.G_EXC_ERROR;
1953
1954 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1955 g_failure_code := 'ROUTING_FAILED';
1956 RAISE FND_API.g_exc_unexpected_error;
1957 END IF;
1958
1959 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1960 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1961 END IF;
1962 END IF;
1963
1964
1965 -- ---------------------------------------------------------------------------
1966 -- Get message count if the stack trace flag is turned on.
1967 -- ---------------------------------------------------------------------------
1968 IF (g_rule_engine_trace_flag = 'Y') THEN
1969 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1970 p_count => x_msg_count,
1971 p_data => x_msg_data);
1972 END IF;
1973
1974
1975 -------------------- Exception --------------------------
1976 EXCEPTION
1977 WHEN FND_API.G_EXC_ERROR THEN
1978 IF (g_failure_code IS NULL) THEN
1979 g_failure_code := 'OTHER';
1980 END IF;
1981
1982 x_return_status := FND_API.G_RET_STS_ERROR;
1983 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1984 p_count => x_msg_count,
1985 p_data => x_msg_data);
1986
1987 -- Cause the calling program to raise the same exception!
1988 --RAISE;
1989
1990 WHEN FND_API.g_exc_unexpected_error THEN
1991 IF (g_failure_code IS NULL) THEN
1992 g_failure_code := 'OTHER';
1993 END IF;
1994
1995 x_return_status := FND_API.g_ret_sts_unexp_error;
1996 FND_MSG_PUB.count_and_get(
1997 p_encoded => FND_API.g_false,
1998 p_count => x_msg_count,
1999 p_data => x_msg_data
2000 );
2001
2002 WHEN OTHERS THEN
2003 IF (g_failure_code IS NULL) THEN
2004 g_failure_code := 'OTHER';
2005 END IF;
2006
2007 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2008 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2009 END IF;
2010
2011 x_return_status := FND_API.G_RET_STS_ERROR;
2012 FND_MSG_PUB.count_and_get(
2013 p_encoded => FND_API.g_false,
2014 p_count => x_msg_count,
2015 p_data => x_msg_data
2016 );
2017
2018 END Partner_Selection;
2019 -- ===========================End of Partner_Selection=========================
2020
2021
2022 --=============================================================================+
2023 --| Public Procedure |
2024 --| |
2025 --| Tie_Breaker |
2026 --| |
2027 --| Parameters |
2028 --| IN |
2029 --| OUT |
2030 --| |
2031 --| |
2032 --| NOTES: |
2033 --| |
2034 --| HISTORY |
2035 --| |
2036 --==============================================================================
2037 PROCEDURE Tie_Breaker(
2038 p_api_version IN NUMBER,
2039 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2040 p_commit IN VARCHAR2 := FND_API.g_false,
2041 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2042 p_process_rule_id IN NUMBER,
2043 x_partner_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
2044 x_return_status OUT NOCOPY VARCHAR2,
2045 x_msg_count OUT NOCOPY NUMBER,
2046 x_msg_data OUT NOCOPY VARCHAR2)
2047 IS
2048
2049 l_api_version NUMBER := 1.0;
2050 l_api_name VARCHAR2(30) := 'Tie_Breaker';
2051 l_tie_breaking_tab PV_TIE_BREAKING_TBL := PV_TIE_BREAKING_TBL();
2052 l_sql_string VARCHAR2(10000);
2053 l_index NUMBER;
2054 l_last_index NUMBER;
2055 l_comma VARCHAR2(1) := ',';
2056 i NUMBER;
2057 l_max_left_length NUMBER; -- Length to the left of the decimal point
2058 l_max_right_length NUMBER; -- Length to the right of the decimal point
2059 l_left_length NUMBER;
2060 l_right_length NUMBER;
2061 l_attribute_id pv_search_attr_values.attribute_id%TYPE;
2062 l_format_string VARCHAR2(300);
2063 l_positive_format_string VARCHAR2(300);
2064 --l_any_negative BOOLEAN := FALSE;
2065 l_prev_party_id NUMBER;
2066
2067 l_party_id number;
2068
2069 l_start NUMBER;
2070 l_dup_count NUMBER;
2071 l_stop_flag BOOLEAN := FALSE;
2072
2073 l_attribute_value varchar2(2000);
2074
2075 -- --------------------------------------------------------------------
2076 -- Fetch all the tie-breaking attributes for a matching rule.
2077 -- --------------------------------------------------------------------
2078 CURSOR c_tie_breaking_attr IS
2079 SELECT a.attribute_id, a.operator, b.return_type
2080 FROM pv_enty_select_criteria a,
2081 pv_attributes_vl b
2082 WHERE a.process_rule_id = p_process_rule_id AND
2083 UPPER(a.selection_type_code) = 'TIE_BREAKING' AND
2084 a.attribute_id = b.attribute_id
2085 ORDER BY a.rank;
2086
2087 lc_cursor c_tie_breaking_attr%ROWTYPE;
2088
2089 -- --------------------------------------------------------------------
2090 -- For a specified attribute, fetch all the attribute values for all
2091 -- the partners in the list (x_partner_tbl).
2092 --
2093 -- The outer join to hz_parties is to trick the query so that NULL values
2094 -- are returned for partners that don't have a corresponding record in
2095 -- pv_search_attr_values.
2096 -- ---------------------------------------------------------------------
2097 /*
2098 CURSOR lc_attr_values IS
2099 SELECT b.party_id,
2100 DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value
2101 FROM pv_search_attr_values a,
2102 hz_parties b
2103 WHERE a.party_id (+) = b.party_id AND
2104 a.attribute_id (+) = l_attribute_id AND
2105 b.party_id IN (
2106 SELECT * FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))
2107 )
2108 ORDER BY b.party_id;
2109 */
2110
2111 -- --------------------------------------------------------------------
2112 -- The use of "rownum idx" is to preserve the order of party_id's as
2113 -- they were passed in through x_partner_tbl.
2114 --
2115 -- The "leading" hint is to make sure that the optimizer will make
2116 -- c (CAST PLSQL table) as the driving table as it is most likely the
2117 -- smallest "table" in the join. This, in most cases, speeds up the
2118 -- performance dramatically.
2119 -- ---------------------------------------------------------------------
2120 lc_attr_values_string varchar2(1000) :=
2121
2122 ' SELECT /*+ leading(c) */ ' ||
2123 ' b.party_id, ' ||
2124 ' DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value, ' ||
2125 ' c.idx ' ||
2126 ' FROM pv_search_attr_values a, ' ||
2127 ' hz_parties b, ' ||
2128 ' (SELECT * ' ||
2129 ' FROM (SELECT rownum idx, column_value party_id ' ||
2130 ' FROM (SELECT column_value ' ||
2131 ' FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))))) c ' ||
2132 ' WHERE a.party_id (+) = b.party_id AND ' ||
2133 ' a.attribute_id (+) = l_attribute_id AND ' ||
2134 ' b.party_id = c.party_id ' ||
2135 ' ORDER BY c.idx ' ;
2136
2137 TYPE t_attr_values_cursor IS REF CURSOR;
2138 lc_attr_values t_attr_values_cursor;
2139 l_idx number;
2140
2141 -- CURSOR lc_attr_values IS
2142 -- SELECT /*+ leading(c) */
2143 /* b.party_id,
2144 DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value,
2145 c.idx
2146 FROM pv_search_attr_values a,
2147 hz_parties b,
2148 (SELECT *
2149 FROM (SELECT rownum idx, column_value party_id
2150 FROM (SELECT column_value
2151 FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))))) c
2152 WHERE a.party_id (+) = b.party_id AND
2153 a.attribute_id (+) = l_attribute_id AND
2154 b.party_id = c.party_id
2155 ORDER BY c.idx;
2156 */
2157
2158 BEGIN
2159 -------------------- initialize -------------------------
2160 IF FND_API.to_boolean(p_init_msg_list) THEN
2161 FND_MSG_PUB.initialize;
2162 END IF;
2163
2164 IF NOT FND_API.compatible_api_call(
2165 l_api_version,
2166 p_api_version,
2167 l_api_name,
2168 g_pkg_name
2169 ) THEN
2170 RAISE FND_API.g_exc_unexpected_error;
2171 END IF;
2172
2173 x_return_status := FND_API.G_RET_STS_SUCCESS;
2174
2175 -------------------------- Source code --------------------
2176
2177 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2178 Debug('Inside Tie-Breaking................................');
2179 END IF;
2180
2181 -- ----------------------------------------------------------
2182 -- Loop through tie-breaking attributes one at a time to
2183 -- break the tie.
2184 -- ----------------------------------------------------------
2185 OPEN c_tie_breaking_attr;
2186 FETCH c_tie_breaking_attr INTO lc_cursor;
2187
2188 WHILE (c_tie_breaking_attr%FOUND AND (NOT l_stop_flag)) LOOP
2189 i := 1;
2190 l_attribute_id := lc_cursor.attribute_id;
2191
2192 l_max_left_length := 0;
2193 l_max_right_length := 0;
2194 -- l_any_negative := FALSE;
2195 l_prev_party_id := NULL;
2196
2197 --FOR lc_cursor_inner IN lc_attr_values LOOP
2198
2199 OPEN lc_attr_values FOR lc_attr_values_string ; --using p_attribute_id, p_entity, p_entity_id,p_attribute_id, p_entity, p_entity_id;
2200 LOOP
2201
2202 FETCH lc_attr_values INTO l_party_id, l_attribute_value, l_idx;
2203 EXIT WHEN lc_attr_values%NOTFOUND;
2204
2205 -- ----------------------------------------------------------------------------
2206 -- Raise an exception if there are more than 1 record with the same party_id.
2207 -- If this were the case, we won't be able to determine which attribute value
2208 -- to use for tie-breaking.
2209 -- ----------------------------------------------------------------------------
2210 IF (l_party_id = l_prev_party_id) THEN
2211 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2212 p_msg_name => 'PV_MULTIPLE_ATTR_VALUES',
2213 p_token1 => 'TEXT',
2214 p_token1_value => 'Party ID: ' || l_prev_party_id,
2215 p_token2 => 'TEXT',
2216 p_token2_value => 'Attribute ID: ' || l_attribute_id);
2217
2218 g_failure_code := 'OTHER';
2219 RAISE FND_API.G_EXC_ERROR;
2220 END IF;
2221
2222 l_prev_party_id := l_party_id;
2223
2224 -- ----------------------------------------------------------------------------
2225 -- Note: indexing the table this way (using i, which is sequential) will not
2226 -- mess up the party_id order because the cursor is sorted by party_id.
2227 -- Consistent read will guarantee that the order won't be changed.
2228 -- However, may want to change this in the future!!!
2229 -- ----------------------------------------------------------------------------
2230 IF (NOT l_tie_breaking_tab.EXISTS(i)) THEN
2231 l_tie_breaking_tab.EXTEND;
2232 l_tie_breaking_tab(i) := system.PV_TIE_BREAKING_TYPE(
2233 l_party_id,
2234 l_attribute_value,
2235 null
2236 --,l_idx
2237 );
2238 ELSE
2239 l_tie_breaking_tab(i).attr_value := l_attribute_value;
2240 END IF;
2241
2242 /* .....................................................................
2243 IF ((TO_NUMBER(l_tie_breaking_tab(i).attr_value) < 0) AND (NOT l_any_negative)) THEN
2244 l_any_negative := TRUE;
2245 END IF;
2246 * ..................................................................... */
2247
2248 Get_Attr_Length(
2249 p_attr_value => l_tie_breaking_tab(i).attr_value,
2250 p_left_length => l_left_length,
2251 p_right_length => l_right_length
2252 );
2253
2254 --Debug('LEFT : ' || l_left_length);
2255 --Debug('RIGHT: ' || l_right_length);
2256
2257 IF (l_left_length > l_max_left_length) THEN
2258 l_max_left_length := l_left_length;
2259 END IF;
2260
2261 IF (l_right_length > l_max_right_length) THEN
2262 l_max_right_length := l_right_length;
2263 END IF;
2264
2265 i := i + 1;
2266 END LOOP; -- lc_cursor_inner
2267
2268 --Debug('MAX RIGHT: ' || l_max_right_length);
2269 --Debug('MAX LEFT : ' || l_max_left_length);
2270
2271 -- -------------------------------------------------------
2272 -- Build the format string for converting the attribute
2273 -- value to the format that we desire.
2274 -- -------------------------------------------------------
2275 l_format_string := Build_Format_String (
2276 p_max_left_length => l_max_left_length,
2277 p_max_right_length => l_max_right_length
2278 );
2279
2280 -- -----------------------------------------------------------
2281 -- If there are any negative numbers in the list, make sure
2282 -- the format string for positive numbers is 1 digit more than
2283 -- the that of the negative numbers.
2284 -- -----------------------------------------------------------
2285 /*
2286 IF (l_any_negative) THEN
2287 l_positive_format_string :=
2288 SUBSTR(l_format_string, 1, 1) || '9' || SUBSTR(l_format_string, 2, LENGTH(l_format_string));
2289 ELSE
2290 l_positive_format_string := l_format_string;
2291 END IF;
2292 */
2293
2294 -- -----------------------------------------------------------
2295 -- Instead of figuring out if there are any negative numbers
2296 -- (which is difficult to do since the operator could be MIN
2297 -- which would revert a positive number to a negative one),
2298 -- always add an additonal '9' to the right side of a positive
2299 -- format filter.
2300 -- -----------------------------------------------------------
2301 l_positive_format_string :=
2302 SUBSTR(l_format_string, 1, 1) || '9' || SUBSTR(l_format_string, 2, LENGTH(l_format_string));
2303
2304 --Debug('Format String: ' || l_format_string);
2305
2306 -- -------------------------------------------------------
2307 -- Now we have the attribute value for a specified
2308 -- attribute for all the partners, we will find the max
2309 -- length of the attribute values in the set, convert
2310 -- the numeric attribute_value into a string, padding
2311 -- 0's if necessary. Depending on the operator for this
2312 -- attribute, we may need to do some special processing
2313 -- (see conver_to_string below) on the attribute value.
2314 -- -------------------------------------------------------
2315 l_index := l_tie_breaking_tab.FIRST;
2316 l_last_index := l_tie_breaking_tab.LAST;
2317
2318 WHILE (l_index <= l_last_index) LOOP
2319 l_tie_breaking_tab(l_index).concat_value_str :=
2320 l_tie_breaking_tab(l_index).concat_value_str ||
2321 Convert_To_String(p_attr_value => TO_NUMBER(l_tie_breaking_tab(l_index).attr_value),
2322 p_max_left_length => l_max_left_length,
2323 p_max_right_length => l_max_right_length,
2324 p_format_string => l_format_string,
2325 p_positive_format_string => l_positive_format_string,
2326 p_min_max => lc_cursor.operator) || '#';
2327
2328 l_index := l_tie_breaking_tab.NEXT(l_index);
2329 END LOOP;
2330
2331 -- -------------------------------------------------------
2332 -- Check for dupes. No need to continue if there are no
2333 -- dupes.
2334 -- -------------------------------------------------------
2335 l_start := dbms_utility.get_time;
2336
2337 BEGIN
2338 SELECT *
2339 INTO l_dup_count
2340 FROM (
2341 SELECT COUNT(*)
2342 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2343 FROM dual) a
2344 WHERE ROWNUM < 2
2345 GROUP BY concat_value_str
2346 HAVING COUNT(*) > 1) b;
2347
2348 EXCEPTION
2349 WHEN no_data_found THEN
2350 --Debug('There are no dupes.');
2351 l_stop_flag := TRUE;
2352
2353 END;
2354
2355
2356 FETCH c_tie_breaking_attr INTO lc_cursor;
2357
2358 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2359 Debug('Finding Dups Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2360 END IF;
2361 END LOOP;
2362
2363 CLOSE c_tie_breaking_attr;
2364
2365
2366 -- DEBUGGING -------------------------------------------------------------
2367 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2368 Debug('Print out concatenated strings...');
2369 l_index := l_tie_breaking_tab.FIRST;
2370 l_last_index := l_tie_breaking_tab.LAST;
2371
2372 WHILE (l_index <= l_last_index) LOOP
2373 Debug(l_tie_breaking_tab(l_index).party_id || ':::' ||
2374 l_tie_breaking_tab(l_index).concat_value_str);
2375 l_index := l_tie_breaking_tab.NEXT(l_index);
2376 END LOOP;
2377 END IF;
2378 -- DEBUGGING -------------------------------------------------------------
2379
2380
2381 -- ------------------------------------------------------------
2382 -- Sort the partners by their concatenated attribute values
2383 -- ------------------------------------------------------------
2384 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2385 Debug('Start sorting..............................');
2386 END IF;
2387
2388 l_start := dbms_utility.get_time;
2389
2390 l_index := 1;
2391
2392 FOR x IN (
2393 SELECT *
2394 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2395 FROM dual) a
2396 ORDER BY concat_value_str DESC)
2397 LOOP
2398 x_partner_tbl(l_index) := x.party_id;
2399 --Debug(x.party_id || ':::' || x.concat_value_str);
2400 l_index := l_index + 1;
2401 END LOOP;
2402
2403
2404 /* =====================================================================
2405 -- ------------------------------------------------------------
2406 -- Sorting by concat_value_str and then by idx will preserve the
2407 -- party_ids order as they are passed in through x_partner_tbl
2408 -- should there be a tie in tie-breaking (e.g. all tie-breaking
2409 -- attributes come up with NULL values).
2410 -- ------------------------------------------------------------
2411 FOR x IN (
2412 SELECT *
2413 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2414 FROM dual) a
2415 ORDER BY concat_value_str DESC, idx ASC)
2416 LOOP
2417 x_partner_tbl(l_index) := x.party_id;
2418 --Debug(x.party_id || ':::' || x.concat_value_str);
2419 l_index := l_index + 1;
2420 END LOOP;
2421 * ===================================================================== */
2422
2423
2424 -- DEBUGGING -------------------------------------------------------------
2425 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2426 Debug('Sorting Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2427
2428 Debug('Printing out partner IDs after sorting...');
2429 FOR i IN 1..x_partner_tbl.COUNT LOOP
2430 Debug(x_partner_tbl(i));
2431 END LOOP;
2432 END IF;
2433 -- DEBUGGING -------------------------------------------------------------
2434 END Tie_Breaker;
2435 -- =========================End of Tie_Breaker==================================
2436
2437
2438
2439 --=============================================================================+
2440 --| Public Procedure |
2441 --| |
2442 --| Debug |
2443 --| |
2444 --| Parameters |
2445 --| IN |
2446 --| OUT |
2447 --| |
2448 --| |
2449 --| NOTES: |
2450 --| |
2451 --| HISTORY |
2452 --| |
2453 --==============================================================================
2454 PROCEDURE Debug(
2455 p_msg_string IN VARCHAR2
2456 )
2457 IS
2458
2459 BEGIN
2460 /* --------------------------------------------------------------
2461 IF (g_rule_engine_trace_flag = 'Y') OR
2462 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2463 THEN
2464 * --------------------------------------------------------------- */
2465 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2466 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
2467 FND_MSG_PUB.Add;
2468
2469 -- END IF;
2470 END Debug;
2471 -- =================================End of Debug================================
2472
2473
2474
2475 --=============================================================================+
2476 --| Public Procedure |
2477 --| |
2478 --| Set_Message |
2479 --| |
2480 --| Parameters |
2481 --| IN |
2482 --| OUT |
2483 --| |
2484 --| |
2485 --| NOTES: |
2486 --| |
2487 --| HISTORY |
2488 --| |
2489 --==============================================================================
2490 PROCEDURE Set_Message(
2491 p_msg_level IN NUMBER,
2492 p_msg_name IN VARCHAR2,
2493 p_token1 IN VARCHAR2,
2494 p_token1_value IN VARCHAR2,
2495 p_token2 IN VARCHAR2 := NULL ,
2496 p_token2_value IN VARCHAR2 := NULL,
2497 p_token3 IN VARCHAR2 := NULL,
2498 p_token3_value IN VARCHAR2 := NULL
2499 )
2500 IS
2501 BEGIN
2502 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
2503 FND_MESSAGE.Set_Name('PV', p_msg_name);
2504 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
2505
2506 IF (p_token2 IS NOT NULL) THEN
2507 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
2508 END IF;
2509
2510 IF (p_token3 IS NOT NULL) THEN
2511 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
2512 END IF;
2513
2514 FND_MSG_PUB.Add;
2515 END IF;
2516 END Set_Message;
2517 -- ==============================End of Set_Message==============================
2518
2519
2520
2521 -- *****************************************************************************
2522 -- *****************************************************************************
2523 -- ********************* Private Routines Start Here...*************************
2524 -- *****************************************************************************
2525 -- *****************************************************************************
2526
2527
2528
2529 --=============================================================================+
2530 --| Public Procedure |
2531 --| |
2532 --| PROCEDURE Cache_Rules |
2533 --| |
2534 --| |
2535 --| Parameters |
2536 --| IN |
2537 --| OUT |
2538 --| |
2539 --| |
2540 --| NOTES |
2541 --| |
2542 --| HISTORY |
2543 --| |
2544 --==============================================================================
2545 PROCEDURE Cache_Rules IS
2546 -- --------------------------------------------------------------------
2547 -- Note that we need an outer join from pv_enty_select_criteria to
2548 -- pv_selected_attr_value
2549 -- since certain attributes (e.g. <condition> is not null) will not
2550 -- have attribute values.
2551 -- --------------------------------------------------------------------
2552
2553 CURSOR lc_opp_selection IS
2554 SELECT prr.process_rule_id,
2555 prr.rank,
2556 pesc.attribute_id,
2557 prr.currency_code,
2558 pesc.operator,
2559 pesc.selection_criteria_id,
2560 psav.attribute_value,
2561 psav.attribute_to_value
2562 FROM pv_process_rules_vl prr,
2563 pv_enty_select_criteria pesc,
2564 pv_selected_attr_values psav
2565 WHERE prr.process_rule_id = pesc.process_rule_id AND
2566 pesc.selection_criteria_id = psav.selection_criteria_id (+) AND
2567 prr.status_code = 'ACTIVE' AND
2568 TRUNC(SYSDATE) BETWEEN prr.start_date AND
2569 NVL(prr.end_date, to_DATE('31-12-4000', 'DD-MM-YYYY')) AND
2570 pesc.selection_type_code = 'OPPORTUNITY_SELECTION'
2571 ORDER BY prr.rank DESC, prr.process_rule_id, pesc.attribute_id,
2572 pesc.selection_criteria_id;
2573
2574 i NUMBER := 1;
2575 l_count NUMBER;
2576 l_or_count NUMBER;
2577 l_next_index NUMBER;
2578 l_next_rule_id NUMBER;
2579 l_next_attribute_id NUMBER;
2580 l_next_sc_id NUMBER;
2581 l_lower_index NUMBER;
2582
2583 BEGIN
2584 -- --------------------------------------------------------------------------
2585 -- Cache rules only if they are not already cached.
2586 -- --------------------------------------------------------------------------
2587 IF (g_opp_selection_tab.COUNT = 0) THEN
2588 FOR l_opp_selection IN lc_opp_selection LOOP
2589 g_opp_selection_tab(i).rank := l_opp_selection.rank;
2590 g_opp_selection_tab(i).process_rule_id := l_opp_selection.process_rule_id;
2591 g_opp_selection_tab(i).attribute_id := l_opp_selection.attribute_id;
2592 g_opp_selection_tab(i).currency_code := l_opp_selection.currency_code;
2593 g_opp_selection_tab(i).operator := l_opp_selection.operator;
2594 g_opp_selection_tab(i).selection_criteria_id := l_opp_selection.selection_criteria_id;
2595 g_opp_selection_tab(i).attribute_value := l_opp_selection.attribute_value;
2596 g_opp_selection_tab(i).attribute_to_value := l_opp_selection.attribute_to_value;
2597
2598 i := i + 1;
2599 END LOOP;
2600
2601 -- --------------------------------------------------------------------------------
2602 -- Set last_attr_flag and count in g_opp_selection_tab.
2603 -- --------------------------------------------------------------------------------
2604 l_count := g_opp_selection_tab.COUNT;
2605 l_or_count := 1;
2606
2607 FOR i IN 1..l_count LOOP
2608 IF (i = l_count) THEN
2609 g_opp_selection_tab(i).last_attr_flag := 'Y';
2610
2611 l_lower_index := i - l_or_count + 1;
2612
2613 FOR j IN l_lower_index..i LOOP
2614 g_opp_selection_tab(j).count := l_or_count;
2615 END LOOP;
2616
2617 ELSE
2618 l_next_index := g_opp_selection_tab.NEXT(i);
2619 l_next_rule_id := g_opp_selection_tab(l_next_index).process_rule_id;
2620 l_next_attribute_id := g_opp_selection_tab(l_next_index).attribute_id;
2621 l_next_sc_id := g_opp_selection_tab(l_next_index).selection_criteria_id;
2622
2623 -- ---------------------------------------------------------------------------
2624 -- If the current process_rule_id is not the same as the next
2625 -- process_rule_id, then this is the last attribute in the rule.
2626 -- ---------------------------------------------------------------------------
2627 IF (l_next_rule_id <> g_opp_selection_tab(i).process_rule_id) THEN
2628 g_opp_selection_tab(i).last_attr_flag := 'Y';
2629 ELSE
2630 g_opp_selection_tab(i).last_attr_flag := 'N';
2631 END IF;
2632
2633 -- ---------------------------------------------------------------------------
2634 -- Set g_opp_selection_tab.count. This field is used to indicate the number
2635 -- of records involved in an AND/OR logic. If the count is 1, then the logic
2636 -- is AND. If the count is greater than 1, the logic is OR.
2637 -- ---------------------------------------------------------------------------
2638 IF (l_next_rule_id = g_opp_selection_tab(i).process_rule_id) AND
2639 (l_next_attribute_id = g_opp_selection_tab(i).attribute_id) AND
2640 (l_next_sc_id = g_opp_selection_tab(i).selection_criteria_id)
2641 THEN
2642 l_or_count := l_or_count + 1;
2643
2644 ELSE
2645 l_lower_index := i - l_or_count + 1;
2646
2647 FOR j IN l_lower_index..i LOOP
2648 g_opp_selection_tab(j).count := l_or_count;
2649 END LOOP;
2650
2651 l_or_count := 1;
2652 END IF;
2653 END IF;
2654 END LOOP;
2655 END IF;
2656
2657 -- -------------------------------------------------------------------
2658 -- Debugging code
2659 -- -------------------------------------------------------------------
2660
2661 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2662 l_count := g_opp_selection_tab.COUNT;
2663
2664 FOR i IN 1..l_count LOOP
2665 Debug('===== ' || i || ' ===========================================');
2666 Debug(g_opp_selection_tab(i).rank || '::' ||
2667 g_opp_selection_tab(i).process_rule_id || '::' ||
2668 g_opp_selection_tab(i).attribute_id || '::' ||
2669 g_opp_selection_tab(i).currency_code || '::' ||
2670 g_opp_selection_tab(i).operator || '::' ||
2671 g_opp_selection_tab(i).selection_criteria_id || '::' ||
2672 g_opp_selection_tab(i).attribute_value || '::' ||
2673 g_opp_selection_tab(i).attribute_to_value || '::' ||
2674 g_opp_selection_tab(i).last_attr_flag || '::' ||
2675 g_opp_selection_tab(i).count
2676 );
2677 END LOOP;
2678 END IF;
2679
2680 END Cache_Rules;
2681 -- =============================End of Cache_Rules==============================
2682
2683
2684 --=============================================================================+
2685 --| Public Procedure |
2686 --| |
2687 --| PROCEDURE Clear_Rules_Cache |
2688 --| |
2689 --| |
2690 --| Parameters |
2691 --| IN |
2692 --| OUT |
2693 --| |
2694 --| |
2695 --| NOTES |
2696 --| |
2697 --| HISTORY |
2698 --| |
2699 --==============================================================================
2700 PROCEDURE Clear_Rules_Cache IS
2701 BEGIN
2702 g_opp_selection_tab.DELETE;
2703 END;
2704 -- ==========================End of Clear_Rules_Cache===========================
2705
2706
2707
2708 --=============================================================================+
2709 --| Private Function |
2710 --| |
2711 --| Get_Next_Rule_Index |
2712 --| Given an index in p_opp_selection_tab PLSQL table, returns the |
2713 --| indexs where the next rule first occurs. |
2714 --| |
2715 --| Parameters |
2716 --| IN |
2717 --| OUT |
2718 --| |
2719 --| |
2720 --| NOTES: |
2721 --| |
2722 --| HISTORY |
2723 --| |
2724 --==============================================================================
2725 FUNCTION Get_Next_Rule_Index(
2726 p_current_index NUMBER,
2727 p_opp_selection_tab t_opp_selection_tab
2728 )
2729 RETURN NUMBER
2730 IS
2731 i NUMBER := p_current_index;
2732 l_current_rule_id NUMBER := p_opp_selection_tab(p_current_index).process_rule_id;
2733 l_last_index NUMBER := p_opp_selection_tab.LAST;
2734
2735 BEGIN
2736 -- ---------------------------------------------------------------------------
2737 -- We're already at the end of the array. No more rules to evaluate.
2738 -- ---------------------------------------------------------------------------
2739 IF (i = l_last_index) THEN
2740 RETURN g_no_more_Rules;
2741 END IF;
2742
2743 -- ---------------------------------------------------------------------------
2744 -- Starting from the next item in the array, if the process_rule_id changes,
2745 -- this indicates the start of a new record. Return the index of this record.
2746 -- ---------------------------------------------------------------------------
2747 i := i + 1;
2748
2749 WHILE ((p_opp_selection_tab(i).process_rule_id = l_current_rule_id) AND
2750 (i < l_last_index))
2751 LOOP
2752 i := i + 1;
2753 END LOOP;
2754
2755 -- ---------------------------------------------------------------------------
2756 -- We've reached the end of the index, no more rules to evaluate.
2757 -- ---------------------------------------------------------------------------
2758 IF ((i = l_last_index) AND
2759 (p_opp_selection_tab(i).process_rule_id = l_current_rule_id))
2760 THEN
2761 i := g_no_more_rules;
2762 END IF;
2763
2764 RETURN i;
2765
2766 -- -------------------------------------------------------
2767 -- May want to capture INDEX OUT OF BOUND here by trapping
2768 -- ORA-01403: no data found
2769 -- -------------------------------------------------------
2770 END Get_Next_Rule_Index;
2771 -- ===========================End of Get_Next_Rule_Index===========================
2772
2773
2774 --=============================================================================+
2775 --| Private Procedure |
2776 --| |
2777 --| Get_Attr_Length |
2778 --| Given a "string of NUMBER", this procedure will return the length of|
2779 --| the string to the left of the decimal point as well as that of the |
2780 --| string to the right of the decimal point. |
2781 --| |
2782 --| Parameters |
2783 --| IN |
2784 --| OUT |
2785 --| |
2786 --| |
2787 --| NOTES: |
2788 --| |
2789 --| HISTORY |
2790 --| |
2791 --==============================================================================
2792 PROCEDURE Get_Attr_Length(
2793 p_attr_value IN VARCHAR2,
2794 p_left_length OUT NOCOPY NUMBER,
2795 p_right_length OUT NOCOPY NUMBER)
2796 IS
2797 l_attr_value VARCHAR2(500);
2798
2799 BEGIN
2800 -- --------------------------------------------------------------------------
2801 -- The negative sign should not be counted when counting string length.
2802 -- --------------------------------------------------------------------------
2803 l_attr_value := REPLACE(p_attr_value, '-', '');
2804
2805 IF (INSTR(p_attr_value, '.') = 0) THEN
2806 p_left_length := LENGTH(l_attr_value);
2807 p_right_length := 0;
2808
2809 ELSE
2810 p_left_length := LENGTH(SUBSTR(l_attr_value, 1, INSTR(l_attr_value, '.') - 1));
2811 p_right_length := LENGTH(SUBSTR(l_attr_value, INSTR(l_attr_value, '.') + 1, LENGTH(l_attr_value)));
2812 END IF;
2813 END Get_Attr_Length;
2814 -- ===========================End of Get_Attr_Length============================
2815
2816
2817
2818 --=============================================================================+
2819 --| Private Function |
2820 --| |
2821 --| Build_Format_String |
2822 --| |
2823 --| Parameters |
2824 --| IN |
2825 --| OUT |
2826 --| |
2827 --| |
2828 --| NOTES: |
2829 --| e.g. of value returned: |
2830 --| '09999999.9990', '09999.990' |
2831 --| |
2832 --| HISTORY |
2833 --| |
2834 --==============================================================================
2835 FUNCTION Build_Format_String (
2836 p_max_left_length NUMBER,
2837 p_max_right_length NUMBER)
2838 RETURN VARCHAR2 IS
2839 l_format_string VARCHAR2(100);
2840
2841 BEGIN
2842 l_format_string := '0';
2843
2844 FOR i IN 1..p_max_left_length - 1 LOOP
2845 l_format_string := l_format_string || '9';
2846 END LOOP;
2847
2848 IF (p_max_right_length > 0) THEN
2849 l_format_string := l_format_string || '.';
2850
2851 FOR i IN 1..p_max_right_length - 1 LOOP
2852 l_format_string := l_format_string || '9';
2853 END LOOP;
2854
2855 l_format_string := l_format_string || '0';
2856 END IF;
2857
2858 RETURN l_format_string;
2859 END;
2860 -- ====================End of Build_Format_String==========================
2861
2862
2863
2864 --=============================================================================+
2865 --| Private Function |
2866 --| |
2867 --| Convert_To_String |
2868 --| |
2869 --| Parameters |
2870 --| IN |
2871 --| OUT |
2872 --| |
2873 --| |
2874 --| NOTES: |
2875 --| |
2876 --| HISTORY |
2877 --| |
2878 --==============================================================================
2879 FUNCTION Convert_To_String(p_attr_value NUMBER,
2880 p_max_left_length NUMBER,
2881 p_max_right_length NUMBER,
2882 p_format_string VARCHAR2,
2883 p_positive_format_string VARCHAR2,
2884 p_min_max VARCHAR2)
2885 RETURN VARCHAR2 IS
2886 l_attr_value NUMBER := p_attr_value;
2887 l_format_string VARCHAR2(300);
2888 l_null_string VARCHAR2(300);
2889
2890 BEGIN
2891 -- -----------------------------------------------------------------
2892 -- If the attribute value is NULL, we want to assign it the
2893 -- "smallest" possible VARCHAR2 value with a length of the format
2894 -- string. The "smallest" value would be a string with all '-'s.
2895 -- e.g. '----------'
2896 -- -----------------------------------------------------------------
2897 IF (p_attr_value IS NULL) THEN
2898 FOR i IN 1..LENGTH(p_positive_format_string) LOOP
2899 l_null_string := l_null_string || '-';
2900 END LOOP;
2901
2902 RETURN l_null_string;
2903 END IF;
2904
2905 -- -----------------------------------------------------------------
2906 -- If p_min_max is 'MIN', which means that 40 is
2907 -- ranked higher than 60, then we need to substract the attribute
2908 -- value from x where x is:
2909 -- POWER(10, p_max_attr_length)
2910 --
2911 -- e.g.
2912 -- If p_max_left_length is 3 (e.g. 100, 200, 250, etc.), then
2913 -- x = POWER(10, 3) = 1000
2914 --
2915 -- The attribute value in this case would be (1000 - p_attr_value).
2916 --
2917 -- Also if the attribute value is a negative number, we need to the
2918 -- similar thing described above for 'MIN'. Of course, we want
2919 -- to leave the negative sign intact.
2920 --
2921 -- The reason for doing this is that we are doing string comparsion
2922 -- even though we are really comparing numbers. When a number is
2923 -- negative, a normal string comparsion would yield the opposite
2924 -- result.
2925 -- e.g.
2926 -- '-1.7' > '-1.6' ==> This is TRUE for string comparsion but
2927 -- FALSE for number comparsion.
2928 --
2929 -- In the case of 'MIN' and negative number, nothing needs to be
2930 -- done since it would just be doing the above operation twice,
2931 -- reverting it to its original result.
2932 --
2933 -- Here's the algorithm:
2934 -- IF (negative AND MIN) THEN
2935 -- Turn it into a positive number
2936 -- ELSIF (negative AND MAX) THEN
2937 -- Substract from x (see above) and keep the negative sign
2938 -- ELSIF (positive AND MIN) THEN
2939 -- Substract from x and prefix it with a negative sign
2940 -- ELSIF (positive and MAX) THEN
2941 -- Just pad with 0's
2942 -- END IF;
2943 -- -----------------------------------------------------------------
2944 IF (p_min_max = 'MIN') THEN
2945 -- --------------------------------------------------------------
2946 -- If a positive number.
2947 -- --------------------------------------------------------------
2948 IF (TO_NUMBER(p_attr_value) > 0) THEN
2949 l_attr_value := POWER(10, p_max_left_length) - p_attr_value;
2950 l_attr_value := -l_attr_value;
2951
2952 -- --------------------------------------------------------------
2953 -- If a negative number.
2954 -- --------------------------------------------------------------
2955 ELSIF (TO_NUMBER(p_attr_value) < 0) THEN
2956 l_attr_value := -l_attr_value;
2957 END IF;
2958
2959 ELSIF (p_min_max = 'MAX') THEN
2960 -- --------------------------------------------------------------
2961 -- If a negative number.
2962 -- --------------------------------------------------------------
2963 IF (TO_NUMBER(p_attr_value) < 0) THEN
2964 -- -----------------------------------------------------------
2965 -- Only substract the positive portion of a negative number
2966 -- string from POWER(10, p_max_left_length). This is
2967 -- equivalent of adding it.
2968 -- -----------------------------------------------------------
2969 l_attr_value := POWER(10, p_max_left_length) + p_attr_value;
2970 l_attr_value := -l_attr_value;
2971 END IF;
2972 END IF;
2973
2974 -- -----------------------------------------------------------------
2975 -- If the number is positive, apply the format string for positive
2976 -- numbers. This is to ensure that positive and negative numbers
2977 -- end up with a string with equal length.
2978 --
2979 -- e.g. TO_CHAR(100, '0999') ==> '0100'
2980 -- TO_CHAR(-100, '0999') ==> '-0100'
2981 --
2982 -- We want it to be like this for positive numbers:
2983 -- TO_CHAR(100, '09999') ==> '00100'
2984 -- TO_CHAR(-100, '0999') ==> '-0100'
2985 -- -----------------------------------------------------------------
2986 IF (l_attr_value >= 0) THEN
2987 l_format_string := p_positive_format_string;
2988 ELSE
2989 l_format_string := p_format_string;
2990 END IF;
2991
2992 -- -----------------------------------------------------------------
2993 -- It is important to use LTRIM() here because ORACLE always adds
2994 -- a blank space in front of the converted string unless the number
2995 -- is negative.
2996 -- -----------------------------------------------------------------
2997 RETURN LTRIM(TO_CHAR(l_attr_value, l_format_string));
2998 END Convert_To_String;
2999 -- ====================End of Convert_To_String==========================
3000
3001 END pv_opp_match_pub;