1 PACKAGE BODY PV_MATCH_V3_PUB AS
2 /* $Header: pvxmtv3b.pls 120.15 2006/09/21 22:27:38 amaram noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_MATCH_V3_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxmtv3b.pls';
6
7 /*************************************************************
8 Almost all of this code has been taken from PV_MATCH_V2_PUB
9 and PV_OPP_MATCH_PUB. Small modifications have been made to them
10 to remove the preferred partner logic. This code was duplicated
11 so that we could have a new set of PL/SQL APIs to handle the new
12 OA UIs. These UIs expect slightly different things from the APIs
13 So even thought most of the funtionality is the same it was not
14 possible to stictly call the older APIs. So we wrote this new set
15 which for most part is the old code but has the minor mosifications
16 to cater to the new functionality.
17 **************************************************************/
18
19 g_no_more_rules CONSTANT NUMBER := 100000000000000;
20 g_rule_engine_trace_flag VARCHAR2(1);
21 g_failure_code VARCHAR2(100) := null;
22 g_matching_engine_type VARCHAR2(50);
23
24 g_e_buffer_too_small EXCEPTION;
25 PRAGMA EXCEPTION_INIT(g_e_buffer_too_small, -6502);
26
27 PROCEDURE debug(p_msg_string IN VARCHAR2) IS
28 BEGIN
29 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
30 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
31 FND_MSG_PUB.Add;
32 END debug;
33
34
35 PROCEDURE Cache_Rules;
36
37 FUNCTION Get_Next_Rule_Index(
38 p_current_index NUMBER,
39 p_opp_selection_tab t_opp_selection_tab
40 )
41 RETURN NUMBER;
42
43 -- --------------------------------------------------------------------
44 -- Tie-breaking API
45 -- --------------------------------------------------------------------
46 PROCEDURE Tie_Breaker(
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
49 p_commit IN VARCHAR2 := FND_API.g_false,
50 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
51 p_process_rule_id IN NUMBER,
52 x_partner_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2
56 );
57
58 -- -----------------------------------------------------------------------------------
59 -- The following private routines are used by the tie-breaking API.
60 -- -----------------------------------------------------------------------------------
61 PROCEDURE Set_Message(
62 p_msg_level IN NUMBER,
63 p_msg_name IN VARCHAR2,
64 p_token1 IN VARCHAR2,
65 p_token1_value IN VARCHAR2,
66 p_token2 IN VARCHAR2 := NULL,
67 p_token2_value IN VARCHAR2 := NULL,
68 p_token3 IN VARCHAR2 := NULL,
69 p_token3_value IN VARCHAR2 := NULL
70 );
71
72 PROCEDURE Get_Attr_Length(
73 p_attr_value IN VARCHAR2,
74 p_left_length OUT NOCOPY NUMBER,
75 p_right_length OUT NOCOPY NUMBER)
76 ;
77
78 FUNCTION Convert_To_String(
79 p_attr_value NUMBER,
80 p_max_left_length NUMBER,
81 p_max_right_length NUMBER,
82 p_format_string VARCHAR2,
83 p_positive_format_string VARCHAR2,
84 p_min_max VARCHAR2)
85 RETURN VARCHAR2;
86
87
88 FUNCTION Build_Format_String (
89 p_max_left_length NUMBER,
90 p_max_right_length NUMBER)
91 RETURN VARCHAR2;
92
93 -- The Procudures Start Here =================================================
94
95 -- ===========================End of Opportunity_Selection=========================
96 PROCEDURE Opportunity_Selection
97 (
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
100 p_commit IN VARCHAR2 := FND_API.g_false,
101 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
102 p_entity_id IN NUMBER,
103 p_entity IN VARCHAR2,
104 p_user_name IN VARCHAR2 := NULL,
105 p_resource_id IN NUMBER := NULL,
106 x_selected_rule_id OUT NOCOPY NUMBER,
107 x_matched_partner_count OUT NOCOPY NUMBER,
108 x_failure_code OUT NOCOPY VARCHAR2,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_count OUT NOCOPY NUMBER,
111 x_msg_data OUT NOCOPY VARCHAR2
112 ) IS
113 l_api_name VARCHAR2(30) := 'Opportunity_Selection';
114 l_return_status VARCHAR2(100);
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(500);
117 l_partner_cnt NUMBER;
118 l_partner_details JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
119 l_flagcount JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
120 l_distance_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
121 l_distance_uom VARCHAR2(30);
122 l_distance_uom_returned VARCHAR2(30);
123
124
125 BEGIN
126 g_failure_code := NULL;
127 g_matching_engine_type := 'BACKGROUND_PARTNER_MATCHING';
128
129 Opportunity_Selection(
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 p_commit => p_commit,
133 p_validation_level => p_validation_level,
134 p_entity_id => p_entity_id,
135 p_entity => p_entity,
136 p_user_name => p_user_name,
137 p_resource_id => p_resource_id,
138 p_routing_flag => 'Y',
139 x_partner_cnt => l_partner_cnt,
140 x_partner_details => l_partner_details,
141 x_flagcount => l_flagcount,
142 x_distance_tbl => l_distance_tbl,
143 x_distance_uom_returned => l_distance_uom_returned,
144 x_selected_rule_id => x_selected_rule_id,
145 x_return_status => x_return_status,
146 x_msg_count => x_msg_count,
147 x_msg_data => x_msg_data
148 );
149
150 x_matched_partner_count := l_partner_cnt;
151
152 -- ----------------------------------------------------------------------
153 -- Set the failure_code so the caller would know what exactly went wrong.
154 -- ----------------------------------------------------------------------
155 x_failure_code := g_failure_code;
156
157 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
158 RAISE FND_API.G_EXC_ERROR;
159
160 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
161 RAISE FND_API.g_exc_unexpected_error;
162 END IF;
163
164 -------------------- Exception --------------------------
165 EXCEPTION
166 WHEN FND_API.G_EXC_ERROR THEN
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
169 p_count => x_msg_count,
170 p_data => x_msg_data);
171
172 WHEN FND_API.g_exc_unexpected_error THEN
173 x_return_status := FND_API.g_ret_sts_unexp_error;
174 FND_MSG_PUB.count_and_get(
175 p_encoded => FND_API.g_false,
176 p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179
180 WHEN OTHERS THEN
181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
182 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
183 END IF;
184
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 FND_MSG_PUB.count_and_get(
187 p_encoded => FND_API.g_false,
188 p_count => x_msg_count,
189 p_data => x_msg_data
190 );
191 END Opportunity_Selection;
192 -- ===========================End of Opportunity_Selection=========================
193
194
195 --=============================================================================+
196 --| Procedure |
197 --| |
198 --| Opportunity_Selection |
199 --| |
200 --| |
201 --| Parameters |
202 --| IN |
203 --| OUT |
204 --| |
205 --| |
206 --| NOTES |
207 --| |
208 --| HISTORY |
209 --| |
210 --==============================================================================
211 PROCEDURE Opportunity_Selection(
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
214 p_commit IN VARCHAR2 := FND_API.g_false,
215 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
216 p_entity_id IN NUMBER,
217 p_entity IN VARCHAR2,
218 p_user_name IN VARCHAR2 := NULL,
219 p_resource_id IN NUMBER := NULL,
220 p_routing_flag IN VARCHAR2 := 'N',
221 x_partner_cnt OUT NOCOPY NUMBER,
222 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
223 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
224 x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
225 x_distance_uom_returned OUT NOCOPY VARCHAR2,
226 x_selected_rule_id OUT NOCOPY NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2
230 )
231 IS
232 l_api_version NUMBER := 1.0;
233 l_api_name VARCHAR2(30) := 'Opportunity_Selection';
234
235 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
236 l_input_filter pv_check_match_pub.t_input_filter;
237 i NUMBER := 1;
238 j NUMBER;
239 l_next_index NUMBER;
240 l_next_rule_id NUMBER;
241 l_stop_flag BOOLEAN := FALSE;
242 l_matched BOOLEAN;
243 l_start NUMBER;
244 l_stop_at_index NUMBER;
245 l_concat_attr_val VARCHAR2(4000);
246 l_concat_to_attr_val VARCHAR2(4000);
247 l_dummy VARCHAR2(4000);
248 l_count NUMBER;
249 l_attribute_id NUMBER;
250 l_return_status VARCHAR2(100);
251 l_msg_count NUMBER;
252 l_msg_data VARCHAR2(500);
253 l_delimiter VARCHAR2(10) := '+++';
254 l_partner_cnt NUMBER;
255
256 -- -----------------------------------------------------------------
257 -- Retrieve from a system profile, indicating the type of matching
258 -- to be used. Values to be determined:
259 --
260 -- EXHAUST_ALL_RULES
261 -- STOP_AT_FIRST_RULE
262 -- -----------------------------------------------------------------
263 l_matching_type VARCHAR2(30);
264
265 l_winning_rule_flag VARCHAR2(10);
266 l_entity_rule_applied_id NUMBER;
267 l_attr_val_temp varchar2(4000);
268
269 BEGIN
270 -------------------- initialize -------------------------
271 IF FND_API.to_boolean(p_init_msg_list) THEN
272 FND_MSG_PUB.initialize;
273 END IF;
274
275 IF NOT FND_API.compatible_api_call(
276 l_api_version,
277 p_api_version,
278 l_api_name,
279 g_pkg_name
280 ) THEN
281 RAISE FND_API.g_exc_unexpected_error;
282 END IF;
283
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 -------------------------- Source code --------------------
287
288 -- ------------------------------------------------------------------------
289 -- Make sure that either p_user_name or p_resource IS NOT NULL.
290 -- ------------------------------------------------------------------------
291 IF (p_user_name IS NULL AND p_resource_id IS NULL) THEN
292 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
293 p_msg_name => 'PV_NO_USERNAME_ID_DEFINED',
294 p_token1 => null,
295 p_token1_value => null,
296 p_token2 => null,
297 p_token2_value => null);
298
299 g_failure_code := 'OTHER';
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 -- ------------------------------------------------------------------------
304 -- Initialize OUT parameters.
305 -- ------------------------------------------------------------------------
306 x_partner_details := JTF_VARCHAR2_TABLE_4000();
307 x_flagcount := JTF_VARCHAR2_TABLE_100();
308 x_distance_tbl := JTF_NUMBER_TABLE();
309
310 -- ------------------------------------------------------------------------
311 -- Retrieve profile value for stack trace profile option.
312 -- ------------------------------------------------------------------------
313 --g_rule_engine_trace_flag := NVL(FND_PROFILE.VALUE('PV_RULE_ENGINE_TRACE_ON'), 'N');
314
315 -- --------------------------------------------------------------------------
316 -- Retrieving Matching Type Profile Value, which can be one of the following:
317 --
318 -- EXHAUST_ALL_RULES
319 -- STOP_AT_FIRST_RULE
320 -- --------------------------------------------------------------------------
321 l_matching_type := NVL(FND_PROFILE.VALUE('PV_PARTNER_MATCHING_TYPE'), 'STOP_AT_FIRST_RULE');
322
323
324 -- ---------------------------------------------------------------------------------
325 -- Cache all the opportunity selection rules (attributes-values) in a global
326 -- PL/SQL table, g_opp_selection_tab.
327 -- ---------------------------------------------------------------------------------
328 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
329 Debug('----------------------------------------------------------------');
330 Debug('g_matching_engine_type: ' || g_matching_engine_type);
331 Debug('Matching Type: ' || l_matching_type);
332 Debug('----------------------------------------------------------------');
333
334 Debug('Clear Rules Cache.......................................');
335 END IF;
336
337 Clear_Rules_Cache;
338
339 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
340 Debug('Rule Caching............................................');
341 END IF;
342
343 l_start := dbms_utility.get_time;
344 Cache_Rules;
345
346 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
347 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
348 END IF;
349
350 l_count := g_opp_selection_tab.COUNT;
351
352 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
353 Debug('Number of Rule items Cached: ' || l_count);
354 END IF;
355
356 i := 1;
357
358 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
359 Debug('Opportunity Rule Selection Starts....................................');
360 END IF;
361
362 l_start := dbms_utility.get_time;
363
364 WHILE (i <= l_count AND (NOT l_stop_flag)) LOOP
365 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
366 Debug('******************************************************');
367 Debug('Rule # :::' || g_opp_selection_tab(i).process_rule_id);
368 END IF;
369
370 -- ---------------------------------------------------------------------------
371 -- If the opportunity's attribute value is not already retrieved, retrieve it.
372 -- ---------------------------------------------------------------------------
373 l_attribute_id := g_opp_selection_tab(i).attribute_id;
374
375 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
376 Debug('Attribute ID: ' || l_attribute_id);
377 Debug('Opportunity ID: ' || p_entity_id);
378 END IF;
379
380 pv_check_match_pub.Get_Entity_Attr_Values(
381 p_api_version_number => 1.0,
382 p_attribute_id => l_attribute_id,
383 p_entity => p_entity,
384 p_entity_id => p_entity_id,
385 p_delimiter => l_delimiter,
386 x_entity_attr_value => l_entity_attr_value,
387 x_return_status => l_return_status,
388 x_msg_count => l_msg_count,
389 x_msg_data => l_msg_data
390 );
391
392 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
393 RAISE FND_API.G_EXC_ERROR;
394
395 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
396 RAISE FND_API.g_exc_unexpected_error;
397 END IF;
398
399 -- ---------------------------------------------------------------------------
400 -- If the attribute value for this entity doesn't exist (which is different
401 -- from a NULL), there's a problem. It should have been caught in
402 -- Get_Entity_Attr_Values.
403 --
404 -- Advance to the next rule for evaluation.
405 -- ---------------------------------------------------------------------------
406 IF (NOT l_entity_attr_value.EXISTS(l_attribute_id)) THEN
407 i := Get_Next_Rule_Index(i, g_opp_selection_tab);
408
409 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
410 Debug('No attribute value for this attribute ' || l_attribute_id);
411 Debug('There is something wrong in the attribute setup.');
412 END IF;
413
414 ELSE
415
416 -- ---------------------------------------------------------------------------
417 -- Now we have the attribute value for this attribute, let's compare it with
418 -- the attribute value specified in the opportunity selection.
419 -- ---------------------------------------------------------------------------
420 l_matched := FALSE;
421
422 -- ---------------------------------------------------------------------------
423 -- AND logic...
424 -- ---------------------------------------------------------------------------
425 IF (g_opp_selection_tab(i).count = 1) THEN
426 -- ------------------------------------------------------------------------
427 -- Use operator to do the match. If the match fails, go to the next rule
428 -- until all rules are exhausted.
429 -- If the match succeeds, check if last_attr_flag = TRUE. If yes, there's
430 -- a match. set l_stop_flag = TRUE
431 -- ------------------------------------------------------------------------
432 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
433 Debug('Calling Check_Match...AND LOGIC...');
434 Debug('p_attribute_id: ' || l_attribute_id);
435 --Debug('p_entity_attr_value: ' || l_entity_attr_value(l_attribute_id).attribute_value);
436
437 l_attr_val_temp := l_entity_attr_value(l_attribute_id).attribute_value;
438 while (l_attr_val_temp is not null) loop
439 Debug('p_entity_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
440 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
441 end loop;
442
443 --Debug('p_rule_attr_value: ' || g_opp_selection_tab(i).attribute_value);
444 l_attr_val_temp := g_opp_selection_tab(i).attribute_value;
445 while (l_attr_val_temp is not null) loop
446 Debug('p_rule_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
447 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
448 end loop;
449
450 Debug('p_operator: ' || g_opp_selection_tab(i).operator);
451 Debug('p_delimiter: ' || l_delimiter);
452 Debug('p_return_type: ' || l_entity_attr_value(l_attribute_id).return_type);
453 END IF;
454
455 l_matched := pv_check_match_pub.Check_Match(
456 p_attribute_id => l_attribute_id,
457 p_entity_attr_value => l_entity_attr_value(l_attribute_id).attribute_value,
458 p_rule_attr_value => g_opp_selection_tab(i).attribute_value,
459 p_rule_to_attr_value => g_opp_selection_tab(i).attribute_to_value,
460 p_operator => g_opp_selection_tab(i).operator,
461 p_input_filter => l_input_filter,
462 p_delimiter => l_delimiter,
463 p_return_type => l_entity_attr_value(l_attribute_id).return_type,
464 p_rule_currency_code => g_opp_selection_tab(i).currency_code
465 );
466
467 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
468 IF (l_matched) THEN
469 Debug('Check_Match TRUE!');
470 ELSE
471 Debug('Check_Match FALSE!');
472 END IF;
473 END IF;
474
475 -- ---------------------------------------------------------------------------
476 -- We have an OR logic here. Need special processing...Concatenate all the
477 -- attribute values involved in the OR logic into one long string, and pass
478 -- this string as the attribute value into Check_Match function.
479 -- ---------------------------------------------------------------------------
480 ELSE
481 -- -------------------------------------------------------------
482 -- l_stop_at_index is the index where the current OR logic ends.
483 -- -------------------------------------------------------------
484 l_stop_at_index := i + g_opp_selection_tab(i).count - 1;
485 l_concat_attr_val := l_delimiter;
486
487 FOR j IN i..l_stop_at_index LOOP
488 l_concat_attr_val := l_concat_attr_val ||
489 g_opp_selection_tab(j).attribute_value || l_delimiter;
490
491 l_concat_to_attr_val := l_concat_to_attr_val ||
492 g_opp_selection_tab(j).attribute_to_value || l_delimiter;
493 END LOOP;
494
495 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
496 Debug('Calling Check_Match...OR Logic');
497 Debug('p_attribute_id: ' || l_attribute_id);
498 --Debug('p_entity_attr_value: ' || l_entity_attr_value(l_attribute_id).attribute_value);
499 l_attr_val_temp := l_entity_attr_value(l_attribute_id).attribute_value;
500 while (l_attr_val_temp is not null) loop
501 Debug('p_entity_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
502 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
503 end loop;
504
505 --Debug('p_rule_attr_value: ' || l_concat_attr_val);
506 l_attr_val_temp := l_concat_attr_val;
507 while (l_attr_val_temp is not null) loop
508 Debug('p_rule_attr_value: ' || substr( l_attr_val_temp, 1, 1800 ) );
509 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
510 end loop;
511
512 Debug('p_operator: ' || g_opp_selection_tab(i).operator);
513 Debug('p_delimiter: ' || l_delimiter);
514 Debug('p_return_type: ' || l_entity_attr_value(l_attribute_id).return_type);
515 END IF;
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 => l_concat_attr_val,
521 p_rule_to_attr_value => l_concat_to_attr_val,
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 -- Advance i to the last record involved in the OR logic.
539 -- ------------------------------------------------------
540 i := l_stop_at_index;
541 END IF;
542
543 -- ------------------------------------------------------------------------
544 -- The attribute value match fails. Advance to the next rule.
545 -- ------------------------------------------------------------------------
546 IF (NOT l_matched) THEN
547 -- ---------------------------------------------------------------------------
548 -- If there are no more rules in the group, it will be set to g_no_more_rules.
549 -- ---------------------------------------------------------------------------
550 i := Get_Next_Rule_Index(i, g_opp_selection_tab);
551
552 -- ------------------------------------------------------------------------
553 -- The attribute value match succeeds. Check if the current record is the
554 -- last record/attribute in the rule. If yes, there's a match between
555 -- this opportunity and the rule. Set l_stop_flag to TRUE to stop processing.
556 -- ------------------------------------------------------------------------
557 ELSE
558 IF (g_opp_selection_tab(i).last_attr_flag = 'Y') THEN
559 IF (l_matching_type = 'STOP_AT_FIRST_RULE') THEN
560 l_stop_flag := TRUE;
561 END IF;
562
563 -- -------------------------------------------------------------
564 -- The current rule is selected for this opportunity.
565 -- -------------------------------------------------------------
566 x_selected_rule_id := g_opp_selection_tab(i).process_rule_id;
567
568 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
569 Debug('%%%%%%%Selected Rule ID: ' || x_selected_rule_id);
570 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
571 Debug('Number of rule items scanned/evaluated before finding a matching rule: ' || i);
572 END IF;
573
574 -- -------------------------------------------------------------
575 -- Perform Partner Selection - call matching engine.
576 -- pv_match_pub package.form_where_clause --> pass in a record
577 -- of tables (operator, attribute, attribute_value) +
578 -- "selection mode" --> Only partners which match all search
579 -- attributes are returned.
580 -- -------------------------------------------------------------
581 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
582 Debug('..........................................................');
583 Debug('Calling Partner_Selection................................');
584 END IF;
585
586 Partner_Selection(
587 p_api_version => 1.0,
588 p_process_rule_id => x_selected_rule_id,
589 p_entity_id => p_entity_id,
590 p_entity => p_entity,
591 p_user_name => p_user_name,
592 p_resource_id => p_resource_id,
593 p_routing_flag => p_routing_flag,
594 x_partner_cnt => l_partner_cnt,
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data
598 );
599
600 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
601 RAISE FND_API.G_EXC_ERROR;
602
603 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
604 RAISE FND_API.g_exc_unexpected_error;
605 END IF;
606
607 -- ------------------------------------------------------------
608 -- Log the selected rules if the matching engine type is
609 -- 'BACKGROUND PARTNER MATCHING'. This provides a snapshot of what
610 -- actually occurred in partner matching.
611 -- ------------------------------------------------------------
612 IF (g_matching_engine_type = 'BACKGROUND_PARTNER_MATCHING') THEN
613 IF (l_partner_cnt > 0) THEN
614 l_winning_rule_flag := 'Y';
615 ELSE
616 l_winning_rule_flag := 'N';
617 END IF;
618
619 l_entity_rule_applied_id := null;
620
621 PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
622 px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_id,
623 p_LAST_UPDATE_DATE => SYSDATE,
624 p_LAST_UPDATED_BY => p_resource_id,
625 p_CREATION_DATE => SYSDATE,
626 p_CREATED_BY => p_resource_id,
627 p_LAST_UPDATE_LOGIN => p_resource_id,
628 p_OBJECT_VERSION_NUMBER => 1,
629 p_REQUEST_ID => FND_API.G_MISS_NUM,
630 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
631 p_PROGRAM_ID => FND_API.G_MISS_NUM,
632 p_PROGRAM_UPDATE_DATE => SYSDATE,
633 p_ENTITY => p_entity,
634 p_ENTITY_ID => p_entity_id,
635 p_PROCESS_RULE_ID => x_selected_rule_id,
636 p_PARENT_PROCESS_RULE_ID => FND_API.G_MISS_NUM,
637 p_LATEST_FLAG => FND_API.G_MISS_CHAR,
638 p_ACTION_VALUE => FND_API.G_MISS_CHAR,
639 p_PROCESS_TYPE => 'BACKGROUND_PARTNER_MATCHING',
640 p_WINNING_RULE_FLAG => l_winning_rule_flag,
641 p_entity_detail => FND_API.G_MISS_CHAR,
642 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
643 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
644 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
645 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
646 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
647 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
648 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
649 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
650 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
651 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
652 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
653 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
654 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
655 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
656 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
657 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
658 p_PROCESS_STATUS => FND_API.G_MISS_CHAR
659 );
660 END IF;
661
662 -- ------------------------------------------------------------
663 -- If there are no partners returned, go on to the next rule
664 -- to find a matching partner until all rules are exhausted.
665 -- ------------------------------------------------------------
666 IF (l_matching_type = 'EXHAUST_ALL_RULES') THEN
667 IF (l_partner_cnt = 0) THEN
668 -- reset failure code
669 g_failure_code := NULL;
670 i := i + 1;
671
672 ELSIF (l_partner_cnt > 0) THEN
673 l_stop_flag := TRUE;
674 END IF;
675 END IF;
676
677 ELSE
678 -- ------------------------------
679 -- Advance to the next attribute
680 -- ------------------------------
681 i := i + 1;
682 END IF;
683
684 END IF;
685 END IF;
686 END LOOP;
687
688 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
689 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
690 END IF;
691
692 IF (NOT l_stop_flag) THEN
693 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
694 Debug('No matching rules found!!!!!');
695 END IF;
696 END IF;
697
698 x_partner_cnt := l_partner_cnt;
699
700 IF (g_rule_engine_trace_flag = 'Y') THEN
701 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
702 p_count => x_msg_count,
703 p_data => x_msg_data);
704 END IF;
705
706 -------------------- Exception --------------------------
707 EXCEPTION
708 WHEN FND_API.G_EXC_ERROR THEN
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
711 p_count => x_msg_count,
712 p_data => x_msg_data);
713
714 WHEN FND_API.g_exc_unexpected_error THEN
715 x_return_status := FND_API.g_ret_sts_unexp_error;
716 FND_MSG_PUB.count_and_get(
717 p_encoded => FND_API.g_false,
718 p_count => x_msg_count,
719 p_data => x_msg_data
720 );
721
722 WHEN g_e_buffer_too_small THEN
723 x_return_status := l_return_status;
724
725 WHEN OTHERS THEN
726 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
727 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
728 END IF;
729
730 x_return_status := FND_API.G_RET_STS_ERROR;
731 FND_MSG_PUB.count_and_get(
732 p_encoded => FND_API.g_false,
733 p_count => x_msg_count,
734 p_data => x_msg_data
735 );
736
737
738 END Opportunity_Selection;
739 -- ===========================End of Opportunity_Selection===========================
740
741
742 -- ===========================End of Partner_Selection=============================
743 PROCEDURE Partner_Selection(
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
746 p_commit IN VARCHAR2 := FND_API.g_false,
747 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
748 p_process_rule_id IN NUMBER,
749 p_entity_id IN NUMBER,
750 p_entity IN VARCHAR2,
751 p_user_name IN VARCHAR2 := NULL,
752 p_resource_id IN NUMBER := NULL,
753 p_routing_flag IN VARCHAR2,
754 x_partner_cnt OUT NOCOPY NUMBER,
755 x_return_status OUT NOCOPY VARCHAR2,
756 x_msg_count OUT NOCOPY NUMBER,
757 x_msg_data OUT NOCOPY VARCHAR2
758 ) IS
759 -- -------------------------------------------------------------------------
760 -- Cursor for retrieving partner-to-opportunity mapping for a process rule.
761 -- -------------------------------------------------------------------------
762 CURSOR lc_partner_mapping IS
763 SELECT a.source_attr_id, a.target_attr_id, a.operator, b.return_type
764 FROM pv_entity_attr_mappings a,
765 pv_attributes_vl b
766 WHERE a.target_attr_id = b.attribute_id AND
767 a.process_rule_id = p_process_rule_id AND
768 a.source_attr_type = 'LEAD' AND
769 --a.source_attr_type = 'OPPORTUNITY' AND
770 a.target_attr_type = 'PARTNER';
771
772 -- -------------------------------------------------------------------------
773 -- Cursor for retrieving partner selection attribute-value pairs.
774 -- -------------------------------------------------------------------------
775 CURSOR lc_partner_selection IS
776 SELECT a.attribute_id, a.operator,
777 b.attribute_value, b.attribute_to_value,
778 a.selection_criteria_id,
779 c.return_type
780 FROM pv_enty_select_criteria a,
781 pv_selected_attr_values b,
782 pv_attributes_vl c
783 WHERE a.attribute_id = c.attribute_id AND
784 a.selection_criteria_id = b.selection_criteria_id (+) AND
785 a.selection_type_code = 'PARTNER_SELECTION' AND
786 a.process_rule_id = p_process_rule_id
787 ORDER BY a.attribute_id, b.selection_criteria_id;
788
789 -- -------------------------------------------------------------------------
790 -- Cursor for retrieving geo proximity and routing information.
791 -- -------------------------------------------------------------------------
792 CURSOR lc_entity_routings (p_selected_rule_id IN NUMBER) IS
793 SELECT entity_routing_id, max_nearest_partner, distance_from_customer,
794 distance_uom_code, routing_type,
795 NVL(bypass_cm_ok_flag, 'N') bypass_cm_ok_flag
796 FROM pv_entity_routings
797 WHERE process_rule_id = p_selected_rule_id;
798
799 -- -------------------------------------------------------------------------
800 -- Cursor for retrieving location_id for the opportunity (customer).
801 -- -------------------------------------------------------------------------
802 CURSOR lc_get_location_id (p_entity_id IN NUMBER) IS
803 SELECT b.location_id
804 FROM as_leads_all a,
805 hz_party_sites b,
806 hz_locations l
807 WHERE a.lead_id = p_entity_id AND
808 a.customer_id = b.party_id AND
809 b.party_site_id = a.address_id AND
810 b.location_id = l.location_id AND
811 l.geometry IS NOT NULL;
812
813 -- -------------------------------------------------------------------------
814 -- Cursor for retrieving the process rule name.
815 -- -------------------------------------------------------------------------
816 CURSOR lc_get_process_rule_name (p_process_rule_id IN NUMBER) IS
817 SELECT process_rule_name
818 FROM pv_process_rules_vl
819 WHERE process_rule_id = p_process_rule_id;
820
821 x_partner_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
822 x_distance_uom_returned VARCHAR2(30);
823 x_distance_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
824
825 l_entity_routing lc_entity_routings%ROWTYPE;
826
827 l_api_version NUMBER := 1.0;
828 l_api_name VARCHAR2(30) := 'Partner_Selection';
829 l_entity_attr_value pv_check_match_pub.t_entity_attr_value;
830 l_temp VARCHAR2(4000);
831 l_num_of_tokens NUMBER;
832 l_attribute_id NUMBER;
833 l_attribute_value VARCHAR2(4000);
834 l_return_status VARCHAR2(100);
835 l_msg_count NUMBER;
836 l_msg_data VARCHAR2(500);
837 l_delimiter VARCHAR2(10) := '+++';
838 l_start NUMBER;
839 i NUMBER := 1;
840 j NUMBER;
841 k NUMBER := 1;
842 l_resource_id NUMBER;
843 l_user_name VARCHAR2(100);
844 l_first_record BOOLEAN := TRUE;
845 l_previous_attr_id NUMBER;
846 l_previous_sc_id NUMBER;
847 l_previous_operator VARCHAR2(100);
848 l_previous_return_type VARCHAR2(100);
849
850 l_customer_address pv_locator.party_address_rec_type;
851 l_distance_uom VARCHAR2(30);
852 --l_distance_uom_returned VARCHAR2(30);
853
854 l_attr_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
855 l_attr_value_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
856 l_attr_operator_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
857 l_attr_data_type_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
858
859 l_source_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
860 l_rank_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
861 l_extra_partner_details JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
862
863 -- l_partner_tbl_temp JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
864 l_partner_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
865 -- vansub
866 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
867 --
868 l_partner_details JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
869 l_flagcount JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
870
871 l_stop_flag BOOLEAN := FALSE;
872
873 l_partner_distance_tbl DBMS_SQL.NUMBER_TABLE;
874 l_preferred_partner_party_id NUMBER;
875 l_distance_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
876 l_rule_currency_code VARCHAR2(15);
877 l_entity_routings_exists BOOLEAN := TRUE;
878 l_preferred_idx NUMBER;
879 l_process_rule_name VARCHAR2(100);
880 l_attr_val_temp VARCHAR2(4000);
881 BEGIN
882 -------------------- initialize -------------------------
883 IF FND_API.to_boolean(p_init_msg_list) THEN
884 FND_MSG_PUB.initialize;
885 END IF;
886
887 IF NOT FND_API.compatible_api_call(
888 l_api_version,
889 p_api_version,
890 l_api_name,
891 g_pkg_name
892 ) THEN
893 RAISE FND_API.g_exc_unexpected_error;
894 END IF;
895
896 x_return_status := FND_API.G_RET_STS_SUCCESS;
897
898 -------------------------- Source code --------------------
899
900 -- ------------------------------------------------------------------------
901 -- Make sure that either p_user_name or p_resource IS NOT NULL.
902 -- ------------------------------------------------------------------------
903 IF (p_user_name IS NULL AND p_resource_id IS NULL) THEN
904 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
905 p_msg_name => 'PV_NO_USERNAME_ID_DEFINED',
906 p_token1 => null,
907 p_token1_value => null,
908 p_token2 => null,
909 p_token2_value => null);
910
911 g_failure_code := 'OTHER';
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914
915
916 -- ------------------------------------------------------------------------
917 -- Retrieve profile value for stack trace profile option.
918 -- ------------------------------------------------------------------------
919 --g_rule_engine_trace_flag := NVL(FND_PROFILE.VALUE('PV_RULE_ENGINE_TRACE_ON'), 'N');
920
921 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
922 Debug('............................................................');
923 Debug('Stack trace turned on? ' || g_rule_engine_trace_flag);
924 Debug('............................................................');
925
926 Debug(' ');
927 Debug('Routing Flag is ' || p_routing_flag);
928 Debug('***Rule ID Selected Is: ' || p_process_rule_id || '***');
929 END IF;
930
931 -- -------------------------------------------------------------
932 -- Retrieve Entity Routings info (geo proximity and routings)
933 -- -------------------------------------------------------------
934 OPEN lc_entity_routings(p_process_rule_id);
935 FETCH lc_entity_routings INTO l_entity_routing;
936
937 IF (lc_entity_routings%NOTFOUND) THEN
938 l_entity_routings_exists := FALSE;
939 END IF;
940
941 CLOSE lc_entity_routings;
942
943 -- -------------------------------------------------------------
944 -- Retrieve location_id for the opportunity (customer).
945 -- -------------------------------------------------------------
946 FOR x IN lc_get_location_id(p_entity_id) LOOP
947 l_customer_address.location_id := x.location_id;
948 END LOOP;
949
950 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
951 Debug('***l_entity_routing.max_nearest_partner: ' || l_entity_routing.max_nearest_partner || '***');
952 Debug('***l_entity_routing.distance_from_customer: ' || l_entity_routing.distance_from_customer || '***');
953 END IF;
954
955 IF (l_entity_routings_exists AND
956 (l_entity_routing.max_nearest_partner IS NOT NULL OR
957 l_entity_routing.distance_from_customer IS NOT NULL) AND
958 l_customer_address.location_id IS NULL)
959 THEN
960 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
961 p_msg_name => 'PV_NO_GEOMETRY_INFO',
962 p_token1 => 'TEXT',
963 p_token1_value => 'Entity ID: ' || p_entity_id,
964 p_token2 => null,
965 p_token2_value => null);
966
967 g_failure_code := 'OTHER';
968 RAISE FND_API.G_EXC_ERROR;
969 END IF;
970
971
972 -- ------------------------------------------------------------------------
973 -- Retrieve the rule's currency_code.
974 -- ------------------------------------------------------------------------
975
976 BEGIN
977 SELECT currency_code
978 INTO l_rule_currency_code
979 FROM pv_process_rules_b
980 WHERE process_rule_id = p_process_rule_id;
981
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 IF (l_process_rule_name IS NULL) THEN
985 FOR x IN lc_get_process_rule_name(p_process_rule_id) LOOP
986 l_process_rule_name := x.process_rule_name;
987 END LOOP;
988 END IF;
989
990 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
991 p_msg_name => 'PV_DEBUG_MSG',
992 p_token1 => 'TEXT',
993 p_token1_value => 'This rule "' || l_process_rule_name ||
994 '" (ID: ' || p_process_rule_id || ') does not exist',
995 p_token2 => null,
996 p_token2_value => null);
997
998 g_failure_code := 'OTHER';
999 RAISE FND_API.G_EXC_ERROR;
1000 END;
1001
1002 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1003 Debug('Rule Currency is: ' || l_rule_currency_code);
1004 END IF;
1005
1006 -- ========================================================================
1007 -- Opportunity-Partner Attribute Mapping =
1008 -- ========================================================================
1009 -- ------------------------------------------------------------------------
1010 -- Loop through opportunity-partner attribute mappings and retrieve the
1011 -- attribute values of each of the attributes in mapping.
1012 -- ------------------------------------------------------------------------
1013 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1014 Debug('Retrieve opportunity-to-partner attribute mapping..................');
1015 END IF;
1016
1017 FOR lc_cursor IN lc_partner_mapping LOOP
1018 l_attribute_id := lc_cursor.source_attr_id;
1019
1020 -- ---------------------------------------------------------------------
1021 -- Retrieve opportunity's attribute value if it hasn't already been
1022 -- retrieved.
1023 -- ---------------------------------------------------------------------
1024 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1025 Debug('Attribute ID before calling Get_Entity_Attr_Values: ' || l_attribute_id);
1026 --Debug('Entity Type: ' || p_entity);
1027 END IF;
1028
1029 pv_check_match_pub.Get_Entity_Attr_Values(
1030 p_api_version_number => 1.0,
1031 p_attribute_id => l_attribute_id,
1032 p_entity => p_entity,
1033 p_entity_id => p_entity_id,
1034 p_delimiter => l_delimiter,
1035 p_expand_attr_flag => 'N',
1036 x_entity_attr_value => l_entity_attr_value,
1037 x_return_status => l_return_status,
1038 x_msg_count => l_msg_count,
1039 x_msg_data => l_msg_data
1040 );
1041
1042 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1043 g_failure_code := 'OTHER';
1044 RAISE FND_API.G_EXC_ERROR;
1045
1046 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1047 g_failure_code := 'OTHER';
1048 RAISE FND_API.g_exc_unexpected_error;
1049
1050 ELSE
1051 -- ---------------------------------------------------------------------
1052 -- Note that l_entity_attr_value stores concatenated attribute values
1053 -- with delimiters in the beginning and end of the string.
1054 -- e.g. +++USA+++UK+++
1055 --
1056 -- However, pv_match_pub.form_where_clause API expects a string without
1057 -- leading and trailing delimiters:
1058 -- e.g. USA+++UK
1059 --
1060 -- Therefore, we need to take these two delimiters out of the string
1061 -- before passing it to the API as a parameter.
1062 -- ---------------------------------------------------------------------
1063 l_temp := l_entity_attr_value(l_attribute_id).attribute_value;
1064
1065 -- ---------------------------------------------------------------------
1066 -- Populate PL/SQL table only when the attribute value string IS NOT NULL.
1067 -- ---------------------------------------------------------------------
1068 IF (l_temp IS NOT NULL AND l_temp <> '++++++') THEN
1069 l_num_of_tokens := pv_check_match_pub.Get_Num_Of_Tokens(l_delimiter, l_temp);
1070
1071 l_attr_id_tbl.EXTEND(l_num_of_tokens);
1072 l_attr_value_tbl.EXTEND(l_num_of_tokens);
1073 l_attr_operator_tbl.EXTEND(l_num_of_tokens);
1074 l_attr_data_type_tbl.EXTEND(l_num_of_tokens);
1075
1076 -- ------------------------------------------------------------------
1077 -- Everything under the mapping section should be treated as AND
1078 -- condition, which requires one table element per item.
1079 -- ------------------------------------------------------------------
1080 FOR j IN 1..l_num_of_tokens LOOP
1081 l_attribute_value := pv_check_match_pub.Retrieve_Token(
1082 p_delimiter => l_delimiter,
1083 p_attr_value_string => l_temp,
1084 p_input_type => 'STD TOKEN',
1085 p_index => j
1086 );
1087
1088 /*
1089 IF (lc_cursor.return_type = 'CURRENCY') THEN
1090 l_attribute_value := l_attribute_value || ':::' || l_rule_currency_code ||
1091 TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1092 END IF;
1093 */
1094 l_attr_id_tbl(i) := lc_cursor.target_attr_id;
1095 l_attr_value_tbl(i) := l_attribute_value;
1096 l_attr_operator_tbl(i) := lc_cursor.operator;
1097 l_attr_data_type_tbl(i) := lc_cursor.return_type;
1098
1099 i := i + 1;
1100 END LOOP;
1101 END IF;
1102 END IF;
1103 END LOOP;
1104
1105 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1106 Debug('Partner Matching/Mapping Attributes...');
1107
1108 FOR i IN 1..l_attr_id_tbl.COUNT LOOP
1109
1110 l_attr_val_temp := l_attr_value_tbl(i);
1111 while (l_attr_val_temp is not null) loop
1112 Debug(i || '-->' ||l_attr_id_tbl(i) || ': ' || substr( l_attr_val_temp, 1, 1800 ) ||
1113 ':::' || l_attr_operator_tbl(i) || ':::' || l_attr_data_type_tbl(i));
1114 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
1115 end loop;
1116 END LOOP;
1117
1118 Debug('-----------------------------------------');
1119
1120 Debug('Appending Partner Selection Attributes...');
1121 END IF;
1122
1123 k := i;
1124
1125 -- ========================================================================
1126 -- Partner Selection Attributes =
1127 -- ========================================================================
1128 -- ------------------------------------------------------------------------
1129 -- Get partner selection attribute value and append them to the record
1130 -- of tables, l_match_attr_rec.
1131 -- The following code also performs AND/OR logic. Attribute values
1132 -- involved in an OR logic will be concatenated in a string separated
1133 -- by a delimiter.
1134 -- ------------------------------------------------------------------------
1135 FOR x IN lc_partner_selection LOOP
1136 IF (l_previous_attr_id = x.attribute_id AND
1137 l_previous_sc_id = x.selection_criteria_id)
1138 THEN
1139 l_attr_value_tbl(i - 1) := l_attr_value_tbl(i - 1) ||
1140 l_delimiter || x.attribute_value;
1141
1142 IF (x.return_type = 'CURRENCY') THEN
1143 l_attr_value_tbl(i - 1) := l_attr_value_tbl(i - 1) || ':::' ||
1144 l_rule_currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1145 END IF;
1146
1147 ELSE
1148 l_attr_id_tbl.EXTEND;
1149 l_attr_value_tbl.EXTEND;
1150 l_attr_operator_tbl.EXTEND;
1151 l_attr_data_type_tbl.EXTEND;
1152
1153 l_attr_value_tbl(i) := x.attribute_value;
1154 l_attr_id_tbl(i) := x.attribute_id;
1155 l_attr_data_type_tbl(i) := x.return_type;
1156 l_attr_operator_tbl(i) := x.operator;
1157
1158 IF (x.return_type = 'CURRENCY') THEN
1159 l_attr_value_tbl(i) := l_attr_value_tbl(i) || ':::' ||
1160 l_rule_currency_code || ':::' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1161 END IF;
1162
1163 IF (x.operator = 'BETWEEN') THEN
1164 l_attr_operator_tbl(i) := '>=';
1165
1166 i := i + 1;
1167 l_attr_id_tbl.EXTEND;
1168 l_attr_value_tbl.EXTEND;
1169 l_attr_operator_tbl.EXTEND;
1170 l_attr_data_type_tbl.EXTEND;
1171 l_attr_operator_tbl(i) := '<=';
1172 l_attr_id_tbl(i) := x.attribute_id;
1173 l_attr_data_type_tbl(i) := x.return_type;
1174 l_attr_value_tbl(i) := x.attribute_to_value;
1175
1176 IF (x.return_type = 'CURRENCY') THEN
1177 l_attr_value_tbl(i) := l_attr_value_tbl(i) ||
1178 ':::' || 'USD' || ':::' ||
1179 TO_CHAR(SYSDATE, 'yyyymmddhh24miss');
1180 END IF;
1181 END IF;
1182
1183 i := i + 1;
1184 END IF;
1185
1186 l_previous_attr_id := x.attribute_id;
1187 l_previous_sc_id := x.selection_criteria_id;
1188 END LOOP;
1189
1190
1191 -- ------------------------------------------------------------------------
1192 -- For debugging only...
1193 -- ------------------------------------------------------------------------
1194 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1195 FOR i IN k..l_attr_id_tbl.COUNT LOOP
1196 --since fnd_msg_pub supports debiug message of length 1972
1197 -- we are passing split of attribute value as it may exceed 2000 length
1198 l_attr_val_temp := l_attr_value_tbl(i);
1199 while (l_attr_val_temp is not null) loop
1200 Debug(i || '-->' || l_attr_id_tbl(i) || ': ' || substr( l_attr_val_temp, 1, 1800 ) ||
1201 ':::' || l_attr_operator_tbl(i) || ':::' || l_attr_data_type_tbl(i));
1202 l_attr_val_temp := substr( l_attr_val_temp, 1801 );
1203 end loop;
1204 END LOOP;
1205 END IF;
1206
1207 -- ------------------------------------------------------------------------
1208 -- Perform Partner Matching...
1209 -- ------------------------------------------------------------------------
1210 IF (p_resource_id IS NULL) THEN
1211 SELECT resource_id
1212 INTO l_resource_id
1213 FROM fnd_user a, jtf_rs_resource_extns b
1214 WHERE a.user_id = b.user_id AND
1215 a.user_name = p_user_name;
1216 ELSE
1217 l_resource_id := p_resource_id;
1218 END IF;
1219
1220 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1221 Debug('..........................................................');
1222 Debug('Calling Form_Where_Clause.......................');
1223 END IF;
1224
1225 l_start := DBMS_UTILITY.get_time;
1226
1227 pv_match_v2_pub.Form_Where_Clause(
1228 p_api_version_number => p_api_version,
1229 p_attr_id_tbl => l_attr_id_tbl,
1230 p_attr_value_tbl => l_attr_value_tbl,
1231 p_attr_operator_tbl => l_attr_operator_tbl,
1232 p_attr_data_type_tbl => l_attr_data_type_tbl,
1233 p_attr_selection_mode => 'OR',
1234 p_att_delmter => l_delimiter,
1235 p_selection_criteria => 'ALL',
1236 p_resource_id => l_resource_id,
1237 p_lead_id => p_entity_id,
1238 p_auto_match_flag => 'N',
1239 x_matched_id => x_partner_tbl,
1240 x_return_status => l_return_status,
1241 x_msg_count => l_msg_count,
1242 x_msg_data => l_msg_data
1243 );
1244
1245 -- -----------------------------------------------------------
1246 -- RULE_FOUND_NO_PARTNER...
1247 -- -----------------------------------------------------------
1248 IF (NOT x_partner_tbl.EXISTS(1)) THEN
1249 g_failure_code := 'RULE_FOUND_NO_PARTNER';
1250 END IF;
1251
1252 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1253 RAISE FND_API.G_EXC_ERROR;
1254
1255 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1256 RAISE FND_API.g_exc_unexpected_error;
1257 END IF;
1258
1259 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1260 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1261
1262 Debug('# of Partners Matched: ' || x_partner_tbl.COUNT);
1263 Debug('Partners Matched: ');
1264
1265 FOR i IN 1..x_partner_tbl.COUNT LOOP
1266 Debug(x_partner_tbl(i) || ',');
1267 END LOOP;
1268
1269 Debug('..........................................................');
1270 Debug('Retrieving GEO Proximity and Routings Info...');
1271 END IF;
1272
1273
1274 -- -------------------------------------------------------------
1275 -- Geographic proximity restrictions.
1276 -- -------------------------------------------------------------
1277 IF (l_entity_routing.entity_routing_id IS NOT NULL) THEN
1278 IF (l_entity_routing.distance_uom_code = 'KILOMETERS') THEN
1279 l_distance_uom := pv_locator.g_distance_unit_km;
1280
1281 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1282 Debug('Distance Unit: ' || l_distance_uom);
1283 END IF;
1284
1285 ELSIF (l_entity_routing.distance_uom_code = 'MILES') THEN
1286 l_distance_uom := pv_locator.g_distance_unit_mile;
1287
1288 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1289 Debug('Distance Unit: ' || l_distance_uom);
1290 END IF;
1291
1292 ELSE
1293 --l_distance_uom := pv_locator.g_distance_unit_mile;
1294
1295 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1296 Debug('No Distance Unit Specified. Use the default from the pv_locator profile.');
1297 END IF;
1298 END IF;
1299
1300 ELSE
1301 --l_distance_uom := pv_locator.g_distance_unit_mile;
1302
1303 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1304 Debug('No Distance Unit Specified. Use the default from the pv_locator profile.');
1305 END IF;
1306 END IF;
1307
1308
1309 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1310 Debug('Geo Proximity and Routing Parameters..............................');
1311 Debug('Location ID: ' || l_customer_address.location_id);
1312 Debug('Distance from partner: ' || l_entity_routing.distance_from_customer);
1313 Debug('Max # of partners to be returned: ' || l_entity_routing.max_nearest_partner);
1314 Debug('Distance UOM Code: ' || l_entity_routing.distance_uom_code);
1315 Debug('Routing Type: ' || l_entity_routing.routing_type);
1316 END IF;
1317
1318 -- ------------------------------------------------------------------------
1319 -- Perform Geo Proximity Restrictions...
1320 --
1321 -- Execute Geo Proximity API only when there is at least one partner
1322 -- returned from Partner Matching above.
1323 -- ------------------------------------------------------------------------
1324
1325 l_partner_id_tbl := x_partner_tbl;
1326
1327 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1328 Debug('# of Partners Sent: ' || x_partner_tbl.COUNT);
1329 END IF;
1330
1331
1332 IF (l_partner_id_tbl.EXISTS(1) AND l_partner_id_tbl.COUNT > 0) THEN
1333 -- -------------------------------------------------------------
1334 -- Execute geo proximity API.
1335 -- -------------------------------------------------------------
1336 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1337 Debug('..........................................................');
1338 Debug('Calling pv_locator.Get_Partners..........................');
1339 Debug('# of Partners Sent: ' || l_partner_id_tbl.COUNT);
1340 END IF;
1341
1342 l_start := DBMS_UTILITY.get_time;
1343
1344 pv_locator.Get_Partners (
1345 p_api_version => p_api_version,
1346 p_customer_address => l_customer_address,
1347 p_partner_tbl => l_partner_id_tbl,
1348 p_max_no_partners => l_entity_routing.max_nearest_partner,
1349 p_distance => l_entity_routing.distance_from_customer,
1350 p_distance_unit => l_distance_uom,
1351 x_partner_tbl => x_partner_tbl,
1352 x_distance_tbl => x_distance_tbl,
1353 x_distance_unit => x_distance_uom_returned,
1354 x_return_status => l_return_status,
1355 x_msg_count => l_msg_count,
1356 x_msg_data => l_msg_data
1357 );
1358
1359 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1360 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1361 RAISE FND_API.G_EXC_ERROR;
1362
1363 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1364 g_failure_code := 'ELOCATION_LOOKUP_FAILURE';
1365 RAISE FND_API.g_exc_unexpected_error;
1366 END IF;
1367
1368 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1369 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1370 END IF;
1371
1372 IF (x_distance_uom_returned = pv_locator.g_distance_unit_km) THEN
1373 x_distance_uom_returned := 'KILOMETERS';
1374
1375 ELSIF (x_distance_uom_returned = pv_locator.g_distance_unit_mile) THEN
1376 x_distance_uom_returned := 'MILES';
1377 END IF;
1378
1379 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1380 Debug('Distance UOM returned is: ' || x_distance_uom_returned);
1381 Debug('# of Partners Returned: ' || x_partner_tbl.COUNT);
1382 Debug('Partners Matched and distance to customer: ');
1383 END IF;
1384
1385
1386 -- -------------------------------------------------------------
1387 -- Store partners' distance to customer.
1388 -- -------------------------------------------------------------
1389 FOR i IN 1..x_partner_tbl.COUNT LOOP
1390 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1391 Debug(x_partner_tbl(i) || ':::' || TRUNC(x_distance_tbl(i), 2));
1392 END IF;
1393
1394 l_partner_distance_tbl(x_partner_tbl(i)) := x_distance_tbl(i);
1395 END LOOP;
1396
1397
1398 --Debug('...........Saved partner distance info..............');
1399 i := l_partner_distance_tbl.FIRST;
1400
1401 WHILE (i <= l_partner_distance_tbl.LAST) LOOP
1402 --Debug(i || ':::' || TRUNC(l_partner_distance_tbl(i), 2));
1403 i := l_partner_distance_tbl.NEXT(i);
1404 END LOOP;
1405 END IF;
1406
1407 -- -------------------------------------------------------------
1408 -- Tie-breaker
1409 -- -------------------------------------------------------------
1410 -- IF (l_entity_routing.routing_type IN ('SERIAL', 'SINGLE') AND
1411 IF (x_partner_tbl.EXISTS(1) AND x_partner_tbl.COUNT > 1) THEN
1412 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1413 Debug('..........................................................');
1414 Debug('Calling Tie_Breaker......................................');
1415 Debug('# of Partners Sent: ' || x_partner_tbl.COUNT);
1416 END IF;
1417
1418 l_start := DBMS_UTILITY.get_time;
1419
1420 Tie_Breaker(
1421 p_api_version => p_api_version,
1422 p_process_rule_id => p_process_rule_id,
1423 x_partner_tbl => x_partner_tbl,
1424 x_return_status => l_return_status,
1425 x_msg_count => l_msg_count,
1426 x_msg_data => l_msg_data
1427 );
1428
1429 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1430 g_failure_code := 'OTHER';
1431 RAISE FND_API.G_EXC_ERROR;
1432
1433 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1434 g_failure_code := 'OTHER';
1435 RAISE FND_API.g_exc_unexpected_error;
1436 END IF;
1437
1438 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1439 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1440 END IF;
1441 END IF;
1442
1443 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1444 Debug('# of Partners Returned: ' || x_partner_tbl.COUNT);
1445 END IF;
1446
1447 -- ------------------------------------------------------------------------------
1448 -- Retreive partner's detail info.
1449 -- ------------------------------------------------------------------------------
1450 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1451 Debug('Retrieve partners detail info.................................................');
1452 END IF;
1453
1454 IF (x_partner_tbl.EXISTS(1)) THEN
1455 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1456 Debug('..........................................................');
1457 Debug('Calling Get_Matched_Partner_Details..........................');
1458 END IF;
1459
1460 l_start := DBMS_UTILITY.get_time;
1461
1462 IF (x_distance_tbl.EXISTS(1)) THEN
1463 FOR i IN 1..x_partner_tbl.COUNT LOOP
1464 x_distance_tbl(i) := l_partner_distance_tbl(x_partner_tbl(i));
1465 END LOOP;
1466
1467 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1468 Debug('Partner Details and distance info...........................');
1469 FOR i IN 1..x_partner_tbl.COUNT LOOP
1470 Debug(x_partner_tbl(i) || ':::' || TRUNC(x_distance_tbl(i), 2));
1471 --Debug(x_partner_details(i));
1472 END LOOP;
1473 END IF;
1474 END IF;
1475
1476 Get_Matched_Partner_Details(
1477 p_api_version_number => 1.0,
1478 p_init_msg_list => FND_API.G_FALSE,
1479 p_commit => FND_API.G_FALSE,
1480 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1481 p_lead_id => p_entity_id,
1482 p_matched_id => x_partner_tbl,
1483 p_distance_tbl => x_distance_tbl,
1484 x_return_status => l_return_status,
1485 x_msg_count => l_msg_count,
1486 x_msg_data => l_msg_data
1487 );
1488
1489 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1490 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1491 Debug('Return Status: ' || l_return_status);
1492 END IF;
1493
1494 g_failure_code := 'OTHER';
1495 RAISE FND_API.G_EXC_ERROR;
1496
1497 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1498 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1499 Debug('Return Status: ' || l_return_status);
1500 END IF;
1501
1502 g_failure_code := 'OTHER';
1503 RAISE FND_API.g_exc_unexpected_error;
1504 END IF;
1505
1506 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1507 Debug('Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
1508 END IF;
1509
1510 -- ------------------------------------------------------------------
1511 -- Reassign distance to each partner.
1512 -- ------------------------------------------------------------------
1513 IF (NOT x_distance_tbl.EXISTS(1)) THEN
1514 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1515 Debug('Initialize x_distance_tbl...');
1516 END IF;
1517
1518 x_distance_tbl := JTF_NUMBER_TABLE();
1519 END IF;
1520
1521 END IF;
1522
1523 x_partner_cnt := x_partner_tbl.count;
1524
1525 -- ---------------------------------------------------------------------------
1526 -- Get message count if the stack trace flag is turned on.
1527 -- ---------------------------------------------------------------------------
1528 IF (g_rule_engine_trace_flag = 'Y') THEN
1529 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1530 p_count => x_msg_count,
1531 p_data => x_msg_data);
1532 END IF;
1533
1534
1535 -------------------- Exception --------------------------
1536 EXCEPTION
1537 WHEN FND_API.G_EXC_ERROR THEN
1538 IF (g_failure_code IS NULL) THEN
1539 g_failure_code := 'OTHER';
1540 END IF;
1541
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1544 p_count => x_msg_count,
1545 p_data => x_msg_data);
1546
1547 -- Cause the calling program to raise the same exception!
1548 --RAISE;
1549
1550 WHEN FND_API.g_exc_unexpected_error THEN
1551 IF (g_failure_code IS NULL) THEN
1552 g_failure_code := 'OTHER';
1553 END IF;
1554
1555 x_return_status := FND_API.g_ret_sts_unexp_error;
1556 FND_MSG_PUB.count_and_get(
1557 p_encoded => FND_API.g_false,
1558 p_count => x_msg_count,
1559 p_data => x_msg_data
1560 );
1561
1562 WHEN OTHERS THEN
1563 IF (g_failure_code IS NULL) THEN
1564 g_failure_code := 'OTHER';
1565 END IF;
1566
1567 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1568 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1569 END IF;
1570
1571 x_return_status := FND_API.G_RET_STS_ERROR;
1572 FND_MSG_PUB.count_and_get(
1573 p_encoded => FND_API.g_false,
1574 p_count => x_msg_count,
1575 p_data => x_msg_data
1576 );
1577
1578 END Partner_Selection;
1579 -- ===========================End of Partner_Selection=========================
1580
1581
1582 -- ===========================Begin of Manual_match============================
1583 Procedure Manual_match(
1584 p_api_version_number IN NUMBER,
1585 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1586 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1587 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1588 p_attr_id_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
1589 p_attr_value_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
1590 p_attr_operator_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1591 p_attr_data_type_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1592 p_attr_selection_mode IN VARCHAR2,
1593 p_att_delmter IN VARCHAR2,
1594 p_selection_criteria IN VARCHAR2,
1595 p_resource_id IN NUMBER,
1596 p_lead_id IN NUMBER,
1597 p_auto_match_flag IN VARCHAR2,
1598 p_get_distance_flag IN VARCHAR2 := 'F',
1599 x_return_status OUT NOCOPY VARCHAR2,
1600 x_msg_count OUT NOCOPY NUMBER,
1601 x_msg_data OUT NOCOPY VARCHAR2,
1602 p_top_n_rows_by_profile IN VARCHAR2 := 'T'
1603 ) IS
1604
1605 l_api_name CONSTANT VARCHAR2(30) := 'Manual_Match';
1606 l_api_version_number CONSTANT NUMBER := 1.0;
1607
1608 CURSOR lc_get_incumbent_pt (pc_lead_id NUMBER) IS
1609 SELECT asla.INCUMBENT_PARTNER_PARTY_ID
1610 FROM as_leads_all asla
1611 WHERE asla.lead_id = pc_lead_id;
1612
1613 l_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1614 l_new_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1615
1616 l_incumbent_pt_party_id NUMBER;
1617 l_matched_tbl_last_idx NUMBER := 0;
1618 l_incumbent_idx NUMBER := 0;
1619
1620 l_distance_uom VARCHAR2(100);
1621 l_customer_address pv_locator.party_address_rec_type;
1622
1623 x_matched_id JTF_NUMBER_TABLE;
1624 x_partner_details JTF_VARCHAR2_TABLE_4000;
1625 x_distance_tbl JTF_NUMBER_TABLE;
1626 x_distance_uom_returned VARCHAR2(30);
1627 x_flagcount JTF_VARCHAR2_TABLE_100;
1628
1629 l_no_of_prefered_pts NUMBER := 0;
1630 l_prefered_pt_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1631 l_prefered_partner_distance NUMBER;
1632 l_prefered_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1633 l_prefered_dist_uom VARCHAR2(200);
1634
1635 l_partner_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1636 l_tokenize_attr_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
1637
1638 l_locator_flag VARCHAR2(1) := 'Y';
1639 BEGIN
1640
1641 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1642 debug('In '||l_api_name);
1643 END IF;
1644
1645 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1646 p_api_version_number,
1647 l_api_name,
1648 G_PKG_NAME) THEN
1649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1650
1651 END IF;
1652
1653 x_matched_id := JTF_NUMBER_TABLE();
1654 x_partner_details := JTF_VARCHAR2_TABLE_4000();
1655 x_distance_tbl := JTF_NUMBER_TABLE();
1656 x_flagcount := JTF_VARCHAR2_TABLE_100();
1657
1658 -- Initialize message list if p_init_msg_list is set to TRUE.
1659 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1660 fnd_msg_pub.initialize;
1661 END IF;
1662
1663 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1664
1665 -- ================================================================================
1666 -- Get matched Partner ID's for the specified attributes
1667 -- ================================================================================
1668
1669 PV_MATCH_V2_PUB.Form_Where_clause(
1670 p_api_version_number => l_api_version_number
1671 ,p_init_msg_list => p_init_msg_list
1672 ,p_commit => p_commit
1673 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1674 ,p_attr_id_tbl => p_attr_id_tbl
1675 ,p_attr_value_tbl => p_attr_value_tbl
1676 ,p_attr_operator_tbl => p_attr_operator_tbl
1677 ,p_attr_data_type_tbl => p_attr_data_type_tbl
1678 ,p_attr_selection_mode => p_attr_selection_mode
1679 ,p_att_delmter => p_att_delmter
1680 ,p_selection_criteria => p_selection_criteria
1681 ,p_resource_id => p_resource_id
1682 ,p_lead_id => p_lead_id
1683 ,p_auto_match_flag => p_auto_match_flag
1684 ,p_top_n_rows_by_profile => p_top_n_rows_by_profile
1685 ,x_matched_id => x_matched_id
1686 ,x_return_status => x_return_status
1687 ,x_msg_count => x_msg_count
1688 ,x_msg_data => x_msg_data
1689 );
1690
1691 -- l_matched_id and x_matched_id are 2 tables which maintain matched partners.
1692 -- They are identical tables to begin with. Refer to the below statement.
1693 --
1694 -- One of them (l_matched_id) is sent as an IN parameter to elocation and the
1695 -- x_matched_id returns from elocation with distance information. The x_matched_id
1696 -- x_matched_id table could be in a different order than l_matched_id when it returns.
1697 -- So l_matched_id table contains partner list as we got it from FORM_WHERE_CALUSE
1698 -- and x_matched_id contains partner list as we had it from Elocation.
1699 l_matched_id := x_matched_id;
1700
1701 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1702 Debug('Partner from FORM_WHERE_CLAUSE '||x_matched_id.COUNT);
1703 END IF;
1704
1705 /******************Begin of preferred partner Code comments ****************
1706 -- Rivendell UI does not have Preferred partner as a part of the Matching LOV
1707 -- The preferred partner is directly added to the matching table instead of
1708 -- being added from the matching LOV.
1709 -- So the whole section of the code involving preferred partner will be commented
1710 -- out.
1711 -- ================================================================================
1712 -- Get Preferred Partner Details
1713 -- ================================================================================
1714 OPEN lc_get_incumbent_pt (p_lead_id);
1715 FETCH lc_get_incumbent_pt INTO l_incumbent_pt_party_id;
1716 CLOSE lc_get_incumbent_pt;
1717
1718 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1719 debug('Preferred partner for lead :'||p_lead_id||' is '||l_incumbent_pt_party_id);
1720 END IF;
1721
1722 -- Checking to see if the preferred partner already exists in the matched partner tbl
1723 IF (x_matched_id.EXISTS(1) AND l_incumbent_pt_party_id IS NOT NULL) THEN
1724 FOR x IN (
1725 SELECT idx
1726 FROM (SELECT rownum idx, column_value party_id
1727 FROM (SELECT column_value
1728 FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
1729 WHERE a.party_id = l_incumbent_pt_party_id)
1730 LOOP
1731 l_incumbent_idx := x.idx;
1732
1733 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1734 debug('Preferred partner already exists at matched partner list, position is '||l_incumbent_idx);
1735 END IF;
1736 END LOOP;
1737 END IF;
1738
1739 -- Adding preferred partner to matched partner id tbl if there is an
1740 -- incumbent partner and that partner was not already there in the table.
1741 IF l_incumbent_idx = 0 and l_incumbent_pt_party_id IS NOT NULL THEN
1742 x_matched_id.extend;
1743 x_matched_id(x_matched_id.count) := l_incumbent_pt_party_id;
1744 END IF;
1745 ******************End of preferred partner Code comments ****************/
1746
1747 -- ------------------------------------------------------------------------
1748 -- Retrieve customer-to-partner distance info...
1749 --
1750 -- Execute Geo Proximity API only when there is at least one partner
1751 -- returned from Partner Matching above.
1752 -- ------------------------------------------------------------------------
1753 IF (p_get_distance_flag = 'T' AND
1754 l_matched_id.EXISTS(1) AND l_matched_id.COUNT > 0)
1755 THEN
1756 -- -------------------------------------------------------------
1757 -- Retrieve location_id for this opportunity.
1758 -- -------------------------------------------------------------
1759 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1760 Debug('before retrieving locator info');
1761 END IF;
1762
1763 BEGIN
1764 SELECT b.location_id
1765 INTO l_customer_address.location_id
1766 FROM as_leads_all a,
1767 hz_party_sites b,
1768 hz_locations l
1769 WHERE a.lead_id = p_lead_id AND
1770 a.customer_id = b.party_id AND
1771 b.party_site_id = a.address_id AND
1772 b.location_id = l.location_id AND
1773 l.geometry IS NOT NULL;
1774
1775 EXCEPTION
1776 WHEN NO_DATA_FOUND THEN
1777 l_locator_flag := 'N';
1778 END;
1779
1780 Debug('Locator Flag :::' || l_locator_flag);
1781
1782 IF l_locator_flag = 'Y' THEN
1783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1784 Debug('Location ID: ' || l_customer_address.location_id);
1785 Debug('..........................................................');
1786 Debug('Calling pv_locator.Get_Partners..........................');
1787 Debug('# of Partners Sent: ' || l_matched_id.COUNT);
1788
1789 FOR i IN 1 .. x_matched_id.COUNT
1790 LOOP
1791 Debug('Partner Id ' || l_matched_id(i));
1792 END LOOP;
1793 END IF;
1794
1795 -- -------------------------------------------------------------
1796 -- Execute geo proximity API.
1797 -- -------------------------------------------------------------
1798 -- Default to mile
1799 l_distance_uom := pv_locator.g_distance_unit_mile;
1800 pv_locator.Get_Partners (
1801 p_api_version => 1.0,
1802 p_init_msg_list => FND_API.g_false,
1803 p_commit => FND_API.g_false,
1804 p_validation_level => FND_API.g_valid_level_full,
1805 p_customer_address => l_customer_address,
1806 p_partner_tbl => l_matched_id,
1807 p_max_no_partners => null,
1808 p_distance => null,
1809 p_distance_unit => l_distance_uom,
1810 p_sort_by_distance => 'T',
1811 x_partner_tbl => x_matched_id,
1812 x_distance_tbl => x_distance_tbl,
1813 x_distance_unit => x_distance_uom_returned,
1814 x_return_status => x_return_status,
1815 x_msg_count => x_msg_count,
1816 x_msg_data => x_msg_data
1817 );
1818
1819 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1820 RAISE FND_API.G_EXC_ERROR;
1821 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1822 RAISE FND_API.g_exc_unexpected_error;
1823 END IF;
1824
1825 IF (x_distance_uom_returned = pv_locator.g_distance_unit_km) THEN
1826 x_distance_uom_returned := 'KILOMETERS';
1827
1828 ELSIF (x_distance_uom_returned = pv_locator.g_distance_unit_mile) THEN
1829 x_distance_uom_returned := 'MILES';
1830 END IF;
1831
1832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1833 Debug('Distance UOM returned is: ' || x_distance_uom_returned);
1834 Debug('# of Partners Returned: ' || x_matched_id.COUNT);
1835 END IF;
1836
1837 /****************Begin of preferred partner Code comments ****************
1838 -- ----------------------------------------------------------------
1839 -- Adding preferred partner on top
1840 -- ----------------------------------------------------------------
1841 IF l_incumbent_pt_party_id IS NOT NULL THEN
1842
1843 FOR x IN (
1844 SELECT idx
1845 FROM (SELECT rownum idx, column_value party_id
1846 FROM (SELECT column_value
1847 FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
1848 WHERE a.party_id = l_incumbent_pt_party_id)
1849 LOOP
1850 l_incumbent_idx := x.idx;
1851 END LOOP;
1852
1853 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1854 debug('location of Preferred partner in matched partner list '
1855 ||l_incumbent_idx);
1856 END IF;
1857
1858 l_prefered_partner_distance := x_distance_tbl(l_incumbent_idx);
1859
1860 IF (x_matched_id.COUNT > 1) THEN
1861 FOR i IN REVERSE 1..(l_incumbent_idx - 1) LOOP
1862 x_matched_id(i + 1) := x_matched_id(i);
1863 x_distance_tbl(i+1) := x_distance_tbl(i);
1864 END LOOP;
1865 x_matched_id(1) := l_incumbent_pt_party_id;
1866 x_distance_tbl(1) := l_prefered_partner_distance;
1867 END IF;
1868 END IF;
1869 ************************End of preferred partner Code comments **********/
1870
1871 -- ------------------------------------------------------------------------
1872 -- Getting the partner details
1873 -- ------------------------------------------------------------------------
1874 FOR i IN 1 .. x_matched_id.COUNT LOOP
1875 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1876 debug('Partner ID after prefered partner on top '||x_matched_id(i));
1877 debug('Distance after prefered partner on top '||x_distance_tbl(i));
1878 END IF;
1879 END LOOP;
1880
1881 END IF; -- Locator flag is 'Y'
1882
1883 END IF;
1884
1885 IF x_matched_id.count > 0 THEN
1886
1887 g_from_match_lov_flag := TRUE;
1888
1889 Get_Matched_Partner_Details(
1890 p_api_version_number => 1.0
1891 ,p_init_msg_list => FND_API.G_FALSE
1892 ,p_commit => FND_API.G_FALSE
1893 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1894 ,p_lead_id => p_lead_id
1895 ,p_matched_id => x_matched_id
1896 ,p_distance_tbl => x_distance_tbl
1897 ,x_return_status => x_return_status
1898 ,x_msg_count => x_msg_count
1899 ,x_msg_data => x_msg_data
1900 );
1901
1902 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1903 RAISE fnd_api.g_exc_error;
1904 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1905 RAISE fnd_api.g_exc_unexpected_error;
1906 END IF;
1907
1908 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1909 Debug('# of Partners Returned from matched_partner_details: ' || x_matched_id.COUNT);
1910 END IF;
1911
1912 END IF;
1913
1914 IF FND_API.To_Boolean ( p_commit ) THEN
1915 COMMIT WORK;
1916 END IF;
1917
1918 -- Standard call to get message count and if count is 1, get message info.
1919 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1920 p_count => x_msg_count,
1921 p_data => x_msg_data);
1922
1923 EXCEPTION
1924
1925 WHEN FND_API.G_EXC_ERROR THEN
1926
1927 x_return_status := FND_API.G_RET_STS_ERROR ;
1928
1929 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1930 p_count => x_msg_count,
1931 p_data => x_msg_data);
1932
1933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1934
1935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1936
1937 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1938 p_count => x_msg_count,
1939 p_data => x_msg_data);
1940
1941
1942 WHEN OTHERS THEN
1943
1944 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1945
1946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1947
1948
1949 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1950 p_count => x_msg_count,
1951 p_data => x_msg_data);
1952
1953 END Manual_Match;
1954 -- ===========================Begin of Manual_match============================
1955
1956 --=============================================================================+
1957 --| Procedure |
1958 --| |
1959 --| Get_Matched_Partner_Details |
1960 --| This procedure Gets the Matched Partner Details required in the UI |
1961 --| |
1962 --| |
1963 --| |
1964 --| Parameters |
1965 --| IN |
1966 --| OUT |
1967 --| |
1968 --| |
1969 --| NOTES |
1970 --| |
1971 --| HISTORY |
1972 --| |
1973 --==============================================================================
1974
1975
1976 /*
1977 Following Assumptions are made for the following select statements.
1978 1. Flag values should each be a different power of 2 to ensure that
1979 each bit is used by only one flag.
1980 Also, these flag values should match with the flag constants defined
1981 in java API to resolve flags on the front end side.
1982
1983 REJECTED CURRENT OPPORTUNITY = 1
1984 PREFERRED OR INCUMBENT PARTNER FOR CURRENT OPPORTUNITY = 2
1985
1986 2. Most of the select statements assume that PT_APPROVED row for accepted
1987 partner exists in pv_lead_assignments until oppty is recycled by the partner
1988
1989 3. ISSUE : RECYCLED from_status does not have partner_id populated in
1990 pv_assignment_logs. So, rejected partner query may not give the correct result
1991
1992 PROPOSAL : We need to identify an assignment status when partner is
1993 rejecting an oppty
1994
1995 after accepting it . Then, we can populate partner_id in logs table
1996 to identify rejected partner
1997
1998 */
1999
2000 Procedure Get_Matched_Partner_Details(
2001 p_api_version_number IN NUMBER,
2002 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2003 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2004 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2005 p_lead_id IN NUMBER,
2006 p_matched_id IN OUT NOCOPY JTF_NUMBER_TABLE,
2007 p_distance_tbl IN JTF_NUMBER_TABLE,
2008 x_return_status OUT NOCOPY VARCHAR2,
2009 x_msg_count OUT NOCOPY NUMBER,
2010 x_msg_data OUT NOCOPY VARCHAR2)
2011 IS
2012
2013
2014 l_api_name CONSTANT VARCHAR2(30) := 'Get_Matched_Partner_Details';
2015 l_api_version_number CONSTANT NUMBER := 1.0;
2016
2017
2018 -- In this API we get details for the partner and insert into
2019 -- an oracle temp table. any commit/rollback action will empty this
2020 -- table since this table maintains data for only the current transaction.
2021 -- so we are setting the l_commit flag to false so that the commit routine
2022 -- is never executed.
2023 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2024
2025 REJECTED_OPPTY_FLAG CONSTANT NUMBER := 1;
2026 INCUMBENT_PARTNER_FLAG CONSTANT NUMBER := 2;
2027 TOKEN CONSTANT VARCHAR2(3) := '~';
2028 NULLTOKEN CONSTANT VARCHAR2(3) := '===';
2029 l_party_name VARCHAR2(360);
2030 l_city VARCHAR2(60);
2031 l_state VARCHAR2(60);
2032 l_country VARCHAR2(60);
2033 l_postal_code VARCHAR2(60);
2034 l_address1 VARCHAR2(1000);
2035 l_address2 VARCHAR2(240);
2036 l_address3 VARCHAR2(240);
2037 l_attr_desc VARCHAR2(60);
2038 l_partner_id NUMBER;
2039 l_oppty_last_offer_dt VARCHAR2(20);
2040 l_party_id NUMBER;
2041 l_partner_count NUMBER := 0;
2042 l_recycled_flag VARCHAR2(4);
2043 l_incumbent_pt_party_id NUMBER;
2044 l_incumbent_exists_flag boolean;
2045 l_relationship_id NUMBER;
2046 l_partner_name VARCHAR2(3600);
2047 l_internal_org_name VARCHAR2(3600);
2048 l_internal_flag VARCHAR2(1);
2049 l_party_flag VARCHAR2(1);
2050 l_partner_names VARCHAR2(3600);
2051 l_count NUMBER;
2052 l_wf_status VARCHAR2(1000);
2053 l_active_flag VARCHAR2(1);
2054
2055 l_party_name_tbl JTF_VARCHAR2_TABLE_1000 := JTF_VARCHAR2_TABLE_1000();
2056 l_party_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2057 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2058 l_address1_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
2059 l_attr_desc_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
2060 l_oppty_last_offer_dt_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2061 l_active_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2062 l_order_table JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2063
2064 l_membertype_table JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2065 l_industry_table JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2066 l_geography_table JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2067 l_capacityrating_table JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2068
2069 l_incumbent_party_name VARCHAR2(3600);
2070 l_pt_id NUMBER;
2071 l_pt_count NUMBER;
2072
2073 l_dist_exists BOOLEAN;
2074
2075 x_partner_details JTF_VARCHAR2_TABLE_4000;
2076 x_recycled_flag_tbl JTF_VARCHAR2_TABLE_100;
2077 x_incumbent_flag_tbl JTF_VARCHAR2_TABLE_100;
2078
2079 TYPE partner_det_rec is REF CURSOR;
2080 lc_partner_detail_cur partner_det_rec;
2081
2082 /*
2083 TYPE l_tmp IS TABLE OF VARCHAR2(4000) INDEX BY binary_integer;
2084 l_tmp_ids_tbl l_tmp;
2085 l_tmp_pt_details_tbl l_tmp;
2086 */
2087
2088 CURSOR lc_get_incumbent_pt (pc_lead_id NUMBER) IS
2089 SELECT asla.INCUMBENT_PARTNER_PARTY_ID
2090 FROM as_leads_all asla
2091 WHERE asla.lead_id = pc_lead_id;
2092
2093 CURSOR lc_get_recycled_flag(pc_lead_id NUMBER , pc_partner_id NUMBER)
2094 IS
2095 SELECT DECODE(status
2096 ,'PT_REJECTED','Y'
2097 ,'PT_ABANDONED','Y'
2098 ,'PT_TIMEOUT','Y'
2099 ,'N')
2100 FROM pv_lead_assignments pval
2101 WHERE pval.lead_id = pc_lead_id
2102 AND pval.status in ('PT_REJECTED', 'PT_ABANDONED', 'PT_TIMEOUT')
2103 AND pval.partner_id = pc_partner_id;
2104
2105 /*
2106 CURSOR lc_duplicate_pt_count
2107 IS
2108 SELECT pvpp.partner_id,
2109 hzp.party_name,
2110 hzop_pt.internal_flag pt_int_flag,
2111 vend.party_name,
2112 hzop_vend.internal_flag vend_int_flag
2113 FROM hz_parties hzp , pv_partner_profiles pvpp , hz_parties vend,
2114 hz_relationships hzr,
2115 hz_organization_profiles HZOP_pt,
2116 hz_organization_profiles hzop_vend
2117 WHERE hzr.party_id = pvpp.partner_id
2118 AND pvpp.partner_party_id = hzr.subject_id
2119 AND hzr.subject_id = hzp.party_id
2120 AND hzr.subject_table_name = 'HZ_PARTIES'
2121 AND hzr.object_table_name = 'HZ_PARTIES'
2122 AND hzr.status = 'A' and hzr.start_date <= sysdate and nvl(hzr.end_date,sysdate) >= sysdate
2123 AND hzr.subject_id = HZOP_pt.party_id and nvl(hzop_pt.effective_end_date,sysdate) >= sysdate
2124 AND hzr.object_id = HZOP_vend.party_id and nvl(hzop_vend.effective_end_date,sysdate) >= sysdate
2125 AND (HZOP_vend.internal_flag = 'N' or hzop_pt.internal_flag = 'Y')
2126 AND pvpp.partner_id in (
2127 SELECT * FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE))
2128 )
2129 AND hzr.object_id = vend.party_id
2130 AND hzr.relationship_code in ('PARTNER_OF','VAD_OF');
2131 */
2132 -- =================================================================================
2133 -- When the Partner Status is Inactive OR Relationship status is inactive
2134 -- OR if the relationship is end dated OR if the Vendor ORG is end dated
2135 -- then the active_flag's value would be 'Inactive'
2136 -- Uncomment this when the local databases are upgraded to 9i
2137 -- =================================================================================
2138 /*
2139 -- 11.5.9
2140 CURSOR l_partner_detail_cur
2141 IS
2142 select hzp.party_name, hzp.city, hzp.state ,
2143 hzp.postal_code, hzp.country, hzp.address1,
2144 hzp.address2, hzp.address3, hzp.party_id,
2145 pvpp.partner_id,
2146 to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
2147 pvac.description, hzr.relationship_id,
2148 (case when hzp.status = 'A'
2149 and hzr.status = 'A'
2150 and nvl(hzop.effective_start_date, sysdate) <= sysdate
2151 and nvl(hzop.effective_end_date, sysdate) >= sysdate
2152 and hzr.start_date <= SYSDATE and NVL(hzr.end_date,SYSDATE) >= SYSDATE
2153 then 'A'
2154 else 'I'
2155 end ) active_flag
2156 from hz_parties hzp , pv_partner_profiles pvpp ,
2157 pv_attribute_codes_vl pvac, hz_relationships hzr ,
2158 hz_organization_profiles HZOP,
2159 (SELECT rownum idx, column_value
2160 FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
2161 where pvpp_partner_id in (SELECT * FROM THE(select CAST(p_matched_id AS JTF_NUMBER_TABLE) from dual))
2162 and pvpp.partner_id = x_partner.column_value
2163 and hzr.party_id = pvpp.partner_id
2164 and hzr.subject_id = hzp.party_id
2165 and hzr.object_id = HZOP.party_id
2166 and HZOP.internal_flag = 'Y'
2167 and hzr.subject_table_name = 'HZ_PARTIES'
2168 and hzr.object_table_name = 'HZ_PARTIES'
2169 and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
2170 order by x_partner.idx;
2171
2172 -- 11.5.10 -- pklin
2173 CURSOR l_partner_detail_cur
2174 IS
2175 select hzp.party_name, hzp.city, hzp.state ,
2176 hzp.postal_code, hzp.country, hzp.address1,
2177 hzp.address2, hzp.address3, hzp.party_id,
2178 pvpp.partner_id,
2179 to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
2180 pvac.description,
2181 pvpp.status active_flag
2182 from hz_parties hzp,
2183 pv_partner_profiles pvpp,
2184 pv_attribute_codes_vl pvac,
2185 (SELECT rownum idx, column_value
2186 FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
2187 where pvpp.partner_id = x_partner.column_value
2188 and pvpp.partner_party_id = hzp.party_id
2189 and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
2190 order by x_partner.idx;
2191 */
2192
2193
2194
2195 /* CURSOR lc_get_pt_org_name(lc_partner_id NUMBER)
2196 IS
2197 select distinct party_name
2198 from hz_relationships hzr,
2199 hz_parties hzp,
2200 hz_organization_profiles HZOP
2201 where hzr.subject_id = hzp.party_id
2202 and hzr.object_id = HZOP.party_id
2203 and HZOP.internal_flag = 'Y'
2204 and hzr.subject_table_name = 'HZ_PARTIES'
2205 and hzr.object_table_name = 'HZ_PARTIES'
2206 and hzr.party_id = lc_partner_id; */
2207
2208 -- =================================================================================
2209
2210 BEGIN
2211
2212 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2213 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2214 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
2215 fnd_msg_pub.Add;
2216 END IF;
2217
2218 -- Standard call to check for call compatibility.
2219
2220 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2221 p_api_version_number,
2222 l_api_name,
2223 G_PKG_NAME) THEN
2224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2225
2226 END IF;
2227
2228 -- Initialize message list if p_init_msg_list is set to TRUE.
2229 IF FND_API.to_Boolean( p_init_msg_list )
2230 THEN
2231 fnd_msg_pub.initialize;
2232 END IF;
2233
2234 x_incumbent_flag_tbl := JTF_VARCHAR2_TABLE_100();
2235 x_recycled_flag_tbl := JTF_VARCHAR2_TABLE_100();
2236 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2237
2238 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2239 debug('Partner Id count before getting details '||p_matched_id.count);
2240 END IF;
2241
2242 l_partner_id_tbl.EXTEND(p_matched_id.count);
2243 l_order_table.EXTEND(p_matched_id.count);
2244 x_incumbent_flag_tbl.EXTEND(p_matched_id.count);
2245 x_recycled_flag_tbl.EXTEND(p_matched_id.count);
2246 l_industry_table.EXTEND(p_matched_id.count);
2247 l_membertype_table.EXTEND(p_matched_id.count);
2248 l_geography_table.EXTEND(p_matched_id.count);
2249 l_capacityrating_table.EXTEND(p_matched_id.count);
2250
2251 FOR i IN 1..p_matched_id.COUNT LOOP
2252 l_partner_id_tbl(i) := p_matched_id(i);
2253 END LOOP;
2254
2255 FOR i IN 1..l_partner_id_tbl.COUNT LOOP
2256 debug('i '||i);
2257 debug('l_partner_id length '||l_partner_id_tbl(i));
2258 END LOOP;
2259
2260
2261 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2262 debug('l_partner_id length '||l_partner_id_tbl.count);
2263 END IF;
2264
2265 OPEN lc_get_incumbent_pt (p_lead_id);
2266 FETCH lc_get_incumbent_pt INTO l_incumbent_pt_party_id;
2267 IF l_incumbent_pt_party_id IS NULL THEN
2268 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2269 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2270 fnd_message.Set_Token('TEXT', 'Incumbent Partner party ID is null. So, setting it to be zero');
2271 fnd_msg_pub.Add;
2272 END IF;
2273 l_incumbent_pt_party_id := 0;
2274 END IF;
2275 CLOSE lc_get_incumbent_pt;
2276
2277 IF l_partner_id_tbl.count > 0 THEN
2278
2279 FOR j IN 1 .. l_partner_id_tbl.COUNT LOOP
2280
2281 l_order_table(j) := j;
2282
2283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2284 debug('Value of index '||j);
2285 END IF;
2286
2287 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2288 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2289 fnd_message.Set_Token('TEXT', 'Checking if preferred Partner: ' || l_incumbent_pt_party_id );
2290 fnd_msg_pub.Add;
2291 END IF;
2292
2293 IF l_partner_id_tbl(j) = l_incumbent_pt_party_id THEN
2294 x_incumbent_flag_tbl(j) := 'Y';
2295 ELSE
2296 x_incumbent_flag_tbl(j) := 'N';
2297 END IF;
2298
2299
2300
2301 -- This section gets the values for
2302 -- Membertype
2303 -- PartnerSince
2304 -- Phone
2305 -- Industry
2306 -- Geographic coverage
2307 -- capacity Rating
2308 -- Size of Company
2309 -- All these attributes have the same SQL_TEXT for the Partner entity
2310 -- So we are using it to query diff attr values and popluating it into
2311 -- the temp table.
2312 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2313 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2314 fnd_message.Set_Token('TEXT', 'Getting Personalized columns for : ' || l_partner_id_tbl(j));
2315 fnd_msg_pub.Add;
2316 END IF;
2317
2318 l_industry_table(j) := get_attribute_value(5, l_partner_id_tbl(j));
2319 l_membertype_table(j) := get_attribute_value(6, l_partner_id_tbl(j));
2320 l_geography_table(j) := get_attribute_value(8, l_partner_id_tbl(j));
2321 l_capacityrating_table(j) := get_attribute_value(18, l_partner_id_tbl(j));
2322
2323 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2324 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2325 fnd_message.Set_Token('TEXT', 'Values for Attrs :5 ' ||l_industry_table(j)||
2326 'Values for Attrs :6 ' ||l_membertype_table(j)||
2327 'Values for Attrs :8 ' || l_geography_table(j)||
2328 'Values for Attrs :18 ' || l_capacityrating_table(j) );
2329 fnd_msg_pub.Add;
2330 END IF;
2331
2332
2333 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2334 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2335 fnd_message.Set_Token('TEXT', 'Looking for recycled flag for Partner Id : ' || l_partner_id_tbl(j));
2336 fnd_msg_pub.Add;
2337 END IF;
2338
2339 -- Check if this lead has been recycled by this partner before.
2340 -- If it has then set the recycled column to 'Y'.
2341 OPEN lc_get_recycled_flag(p_lead_id, l_partner_id_tbl(j));
2342 FETCH lc_get_recycled_flag INTO l_recycled_flag;
2343
2344 x_recycled_flag_tbl.extend;
2345 IF lc_get_recycled_flag%FOUND AND l_recycled_flag IS NOT NULL THEN
2346 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2347 Debug( 'Recycled flag : ' || l_recycled_flag);
2348 END IF;
2349 x_recycled_flag_tbl(j) := l_recycled_flag;
2350 ELSE
2351 x_recycled_flag_tbl(j) := 'N';
2352 END IF;
2353
2354 l_recycled_flag := 'N';
2355
2356 CLOSE lc_get_recycled_flag;
2357 END LOOP;
2358 END IF;
2359
2360 BEGIN
2361 IF p_distance_tbl.COUNT > 0 THEN
2362 FORALL i IN 1..p_matched_id.COUNT
2363 INSERT INTO pv_matched_partners_gt
2364 VALUES (
2365 l_partner_id_tbl(i)
2366 ,p_distance_tbl(i)
2367 ,x_recycled_flag_tbl(i)
2368 ,x_incumbent_flag_tbl(i)
2369 ,l_order_table(i)
2370 ,l_industry_table(i)
2371 ,l_membertype_table(i)
2372 ,l_geography_table(i)
2373 ,l_capacityrating_table(i)
2374 );
2375 ELSE
2376 FORALL i IN 1..p_matched_id.COUNT
2377 INSERT INTO pv_matched_partners_gt
2378 (
2379 PARTNER_ID
2380 ,RECYCLED_FLAG
2381 ,INCUMBENT_FLAG
2382 ,PARTNER_ORDER
2383 ,INDUSTRY
2384 ,MEMBER_TYPE
2385 ,GEOGRAPHY
2386 ,CAPACITY_RATING
2387 )
2388 VALUES (
2389 l_partner_id_tbl(i)
2390 ,x_recycled_flag_tbl(i)
2391 ,x_incumbent_flag_tbl(i)
2392 ,l_order_table(i)
2393 ,l_industry_table(i)
2394 ,l_membertype_table(i)
2395 ,l_geography_table(i)
2396 ,l_capacityrating_table(i)
2397 );
2398 END IF;
2399 EXCEPTION
2400 WHEN others THEN
2401 Debug('Bad row index = ' || (1 +sql%rowcount) ||' ' || sqlerrm);
2402 END;
2403
2404 IF FND_API.To_Boolean ( l_commit ) THEN
2405 COMMIT WORK;
2406 END IF;
2407
2408 -- Standard call to get message count and if count is 1, get message info.
2409 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2410 p_count => x_msg_count,
2411 p_data => x_msg_data);
2412
2413 EXCEPTION
2414
2415 WHEN FND_API.G_EXC_ERROR THEN
2416
2417 x_return_status := FND_API.G_RET_STS_ERROR ;
2418 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2419 p_count => x_msg_count,
2420 p_data => x_msg_data);
2421
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423
2424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2425
2426 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2427 p_count => x_msg_count,
2428 p_data => x_msg_data);
2429
2430 WHEN OTHERS THEN
2431
2432
2433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2434
2435 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2436
2437 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2438 p_count => x_msg_count,
2439 p_data => x_msg_data);
2440
2441 End get_matched_partner_details;
2442 --====================End of get_matched_partner_details ======================
2443
2444 --=============================================================================+
2445 --| Public Procedure |
2446 --| |
2447 --| Tie_Breaker |
2448 --| |
2449 --| Parameters |
2450 --| IN |
2451 --| OUT |
2452 --| |
2453 --| |
2454 --| NOTES: |
2455 --| |
2456 --| HISTORY |
2457 --| |
2458 --==============================================================================
2459 PROCEDURE Tie_Breaker(
2460 p_api_version IN NUMBER,
2461 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2462 p_commit IN VARCHAR2 := FND_API.g_false,
2463 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2464 p_process_rule_id IN NUMBER,
2465 x_partner_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
2466 x_return_status OUT NOCOPY VARCHAR2,
2467 x_msg_count OUT NOCOPY NUMBER,
2468 x_msg_data OUT NOCOPY VARCHAR2)
2469 IS
2470
2471 l_api_version NUMBER := 1.0;
2472 l_api_name VARCHAR2(30) := 'Tie_Breaker';
2473 l_tie_breaking_tab PV_TIE_BREAKING_TBL := PV_TIE_BREAKING_TBL();
2474 l_sql_string VARCHAR2(10000);
2475 l_index NUMBER;
2476 l_last_index NUMBER;
2477 l_comma VARCHAR2(1) := ',';
2478 i NUMBER;
2479 l_max_left_length NUMBER; -- Length to the left of the decimal point
2480 l_max_right_length NUMBER; -- Length to the right of the decimal point
2481 l_left_length NUMBER;
2482 l_right_length NUMBER;
2483 l_attribute_id NUMBER;
2484 l_format_string VARCHAR2(300);
2485 l_positive_format_string VARCHAR2(300);
2486 --l_any_negative BOOLEAN := FALSE;
2487 l_prev_party_id NUMBER;
2488 l_party_id NUMBER;
2489 l_attribute_value varchar2(2000);
2490
2491 l_start NUMBER;
2492 l_dup_count NUMBER;
2493 l_stop_flag BOOLEAN := FALSE;
2494
2495 -- --------------------------------------------------------------------
2496 -- Fetch all the tie-breaking attributes for a matching rule.
2497 -- --------------------------------------------------------------------
2498 CURSOR c_tie_breaking_attr IS
2499 SELECT a.attribute_id, a.operator, b.return_type
2500 FROM pv_enty_select_criteria a,
2501 pv_attributes_vl b
2502 WHERE a.process_rule_id = p_process_rule_id AND
2503 UPPER(a.selection_type_code) = 'TIE_BREAKING' AND
2504 a.attribute_id = b.attribute_id
2505 ORDER BY a.rank;
2506
2507 lc_cursor c_tie_breaking_attr%ROWTYPE;
2508
2509 -- --------------------------------------------------------------------
2510 -- For a specified attribute, fetch all the attribute values for all
2511 -- the partners in the list (x_partner_tbl).
2512 --
2513 -- The outer join to hz_parties is to trick the query so that NULL values
2514 -- are returned for partners that don't have a corresponding record in
2515 -- pv_search_attr_values.
2516 -- ---------------------------------------------------------------------
2517 /*
2518 CURSOR lc_attr_values IS
2519 SELECT b.party_id,
2520 DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value
2521 FROM pv_search_attr_values a,
2522 hz_parties b
2523 WHERE a.party_id (+) = b.party_id AND
2524 a.attribute_id (+) = l_attribute_id AND
2525 b.party_id IN (
2526 SELECT * FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))
2527 )
2528 ORDER BY b.party_id;
2529 */
2530
2531 -- --------------------------------------------------------------------
2532 -- The use of "rownum idx" is to preserve the order of party_id's as
2533 -- they were passed in through x_partner_tbl.
2534 --
2535 -- The "leading" hint is to make sure that the optimizer will make
2536 -- c (CAST PLSQL table) as the driving table as it is most likely the
2537 -- smallest "table" in the join. This, in most cases, speeds up the
2538 -- performance dramatically.
2539 -- ---------------------------------------------------------------------
2540 lc_attr_values_string varchar2(1000) :=
2541 ' select ' ||
2542 ' c.party_id, '||
2543 ' (select decode(a.attr_text, null, to_char(a.attr_value), ' ||
2544 ' a.attr_text) from pv_search_attr_values a where a.party_id = c.party_id and ' ||
2545 ' a.attribute_id = :1) attribute_value, ' ||
2546 ' c.idx ' ||
2547 ' from ' ||
2548 ' (select * from (select rownum idx, ' ||
2549 ' column_value party_id from (select column_value from table (cast(:2 as ' ||
2550 ' jtf_number_table))))) c ' ||
2551 ' order by c.idx ' ;
2552
2553 /*
2554 ' SELECT ' ||
2555 ' b.party_id, ' ||
2556 ' DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value, ' ||
2557 ' c.idx ' ||
2558 ' FROM pv_search_attr_values a, ' ||
2559 ' hz_parties b, ' ||
2560 ' (SELECT * ' ||
2561 ' FROM (SELECT rownum idx, column_value party_id ' ||
2562 ' FROM (SELECT column_value ' ||
2563 ' FROM TABLE (CAST(:1 AS JTF_NUMBER_TABLE))))) c ' ||
2564 ' WHERE a.party_id (+) = b.party_id AND ' ||
2565 ' a.attribute_id (+) = :2 AND ' ||
2566 ' b.party_id = c.party_id ' ||
2567 ' ORDER BY c.idx ' ;
2568 */
2569
2570 TYPE t_attr_values_cursor IS REF CURSOR;
2571 lc_attr_values t_attr_values_cursor;
2572 l_idx number;
2573
2574 -- CURSOR lc_attr_values IS
2575 -- SELECT /*+ leading(c) */
2576 /* b.party_id,
2577 DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value,
2578 c.idx
2579 FROM pv_search_attr_values a,
2580 hz_parties b,
2581 (SELECT *
2582 FROM (SELECT rownum idx, column_value party_id
2583 FROM (SELECT column_value
2584 FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))))) c
2585 WHERE a.party_id (+) = b.party_id AND
2586 a.attribute_id (+) = l_attribute_id AND
2587 b.party_id = c.party_id
2588 ORDER BY c.idx;
2589 */
2590
2591
2592 BEGIN
2593 -------------------- initialize -------------------------
2594 IF FND_API.to_boolean(p_init_msg_list) THEN
2595 FND_MSG_PUB.initialize;
2596 END IF;
2597
2598 IF NOT FND_API.compatible_api_call(
2599 l_api_version,
2600 p_api_version,
2601 l_api_name,
2602 g_pkg_name
2603 ) THEN
2604 RAISE FND_API.g_exc_unexpected_error;
2605 END IF;
2606
2607 x_return_status := FND_API.G_RET_STS_SUCCESS;
2608
2609 -------------------------- Source code --------------------
2610
2611 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2612 Debug('Inside Tie-Breaking................................');
2613 END IF;
2614
2615 -- ----------------------------------------------------------
2616 -- Loop through tie-breaking attributes one at a time to
2617 -- break the tie.
2618 -- ----------------------------------------------------------
2619 OPEN c_tie_breaking_attr;
2620 FETCH c_tie_breaking_attr INTO lc_cursor;
2621
2622 WHILE (c_tie_breaking_attr%FOUND AND (NOT l_stop_flag)) LOOP
2623 i := 1;
2624 l_attribute_id := lc_cursor.attribute_id;
2625
2626 l_max_left_length := 0;
2627 l_max_right_length := 0;
2628 -- l_any_negative := FALSE;
2629 l_prev_party_id := NULL;
2630
2631 --FOR lc_cursor_inner IN lc_attr_values LOOP
2632
2633 OPEN lc_attr_values FOR lc_attr_values_string using l_attribute_id, x_partner_tbl ;
2634 LOOP
2635
2636 FETCH lc_attr_values INTO l_party_id, l_attribute_value, l_idx;
2637 EXIT WHEN lc_attr_values%NOTFOUND;
2638
2639 -- ----------------------------------------------------------------------------
2640 -- Raise an exception if there are more than 1 record with the same party_id.
2641 -- If this were the case, we won't be able to determine which attribute value
2642 -- to use for tie-breaking.
2643 -- ----------------------------------------------------------------------------
2644 IF (l_party_id = l_prev_party_id) THEN
2645 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2646 p_msg_name => 'PV_MULTIPLE_ATTR_VALUES',
2647 p_token1 => 'TEXT',
2648 p_token1_value => 'Party ID: ' || l_prev_party_id,
2649 p_token2 => 'TEXT',
2650 p_token2_value => 'Attribute ID: ' || l_attribute_id);
2651
2652 g_failure_code := 'OTHER';
2653 RAISE FND_API.G_EXC_ERROR;
2654 END IF;
2655
2656 l_prev_party_id := l_party_id;
2657
2658 -- ----------------------------------------------------------------------------
2659 -- Note: indexing the table this way (using i, which is sequential) will not
2660 -- mess up the party_id order because the cursor is sorted by party_id.
2661 -- Consistent read will guarantee that the order won't be changed.
2662 -- However, may want to change this in the future!!!
2663 -- ----------------------------------------------------------------------------
2664 IF (NOT l_tie_breaking_tab.EXISTS(i)) THEN
2665 l_tie_breaking_tab.EXTEND;
2666 l_tie_breaking_tab(i) := system.PV_TIE_BREAKING_TYPE(
2667 l_party_id,
2668 l_attribute_value,
2669 null
2670 --,l_idx
2671 );
2672 ELSE
2673 l_tie_breaking_tab(i).attr_value := l_attribute_value;
2674 END IF;
2675
2676 /* .....................................................................
2677 IF ((TO_NUMBER(l_tie_breaking_tab(i).attr_value) < 0) AND (NOT l_any_negative)) THEN
2678 l_any_negative := TRUE;
2679 END IF;
2680 * ..................................................................... */
2681
2682 Get_Attr_Length(
2683 p_attr_value => l_tie_breaking_tab(i).attr_value,
2684 p_left_length => l_left_length,
2685 p_right_length => l_right_length
2686 );
2687
2688 --Debug('LEFT : ' || l_left_length);
2689 --Debug('RIGHT: ' || l_right_length);
2690
2691 IF (l_left_length > l_max_left_length) THEN
2692 l_max_left_length := l_left_length;
2693 END IF;
2694
2695 IF (l_right_length > l_max_right_length) THEN
2696 l_max_right_length := l_right_length;
2697 END IF;
2698
2699 i := i + 1;
2700 END LOOP; -- lc_cursor_inner -- end of OPEN lc_attr_values FOR lc_attr_values_string
2701
2702 --Debug('MAX RIGHT: ' || l_max_right_length);
2703 --Debug('MAX LEFT : ' || l_max_left_length);
2704
2705 -- -------------------------------------------------------
2706 -- Build the format string for converting the attribute
2707 -- value to the format that we desire.
2708 -- -------------------------------------------------------
2709 l_format_string := Build_Format_String (
2710 p_max_left_length => l_max_left_length,
2711 p_max_right_length => l_max_right_length
2712 );
2713
2714 -- -----------------------------------------------------------
2715 -- If there are any negative numbers in the list, make sure
2716 -- the format string for positive numbers is 1 digit more than
2717 -- the that of the negative numbers.
2718 -- -----------------------------------------------------------
2719 /*
2720 IF (l_any_negative) THEN
2721 l_positive_format_string :=
2722 SUBSTR(l_format_string, 1, 1) || '9' || SUBSTR(l_format_string, 2, LENGTH(l_format_string));
2723 ELSE
2724 l_positive_format_string := l_format_string;
2725 END IF;
2726 */
2727
2728 -- -----------------------------------------------------------
2729 -- Instead of figuring out if there are any negative numbers
2730 -- (which is difficult to do since the operator could be MIN
2731 -- which would revert a positive number to a negative one),
2732 -- always add an additonal '9' to the right side of a positive
2733 -- format filter.
2734 -- -----------------------------------------------------------
2735 l_positive_format_string :=
2736 SUBSTR(l_format_string, 1, 1) || '9' || SUBSTR(l_format_string, 2, LENGTH(l_format_string));
2737
2738 --Debug('Format String: ' || l_format_string);
2739
2740 -- -------------------------------------------------------
2741 -- Now we have the attribute value for a specified
2742 -- attribute for all the partners, we will find the max
2743 -- length of the attribute values in the set, convert
2744 -- the numeric attribute_value into a string, padding
2745 -- 0's if necessary. Depending on the operator for this
2746 -- attribute, we may need to do some special processing
2747 -- (see conver_to_string below) on the attribute value.
2748 -- -------------------------------------------------------
2749 l_index := l_tie_breaking_tab.FIRST;
2750 l_last_index := l_tie_breaking_tab.LAST;
2751
2752 WHILE (l_index <= l_last_index) LOOP
2753 l_tie_breaking_tab(l_index).concat_value_str :=
2754 l_tie_breaking_tab(l_index).concat_value_str ||
2755 Convert_To_String(p_attr_value => TO_NUMBER(l_tie_breaking_tab(l_index).attr_value),
2756 p_max_left_length => l_max_left_length,
2757 p_max_right_length => l_max_right_length,
2758 p_format_string => l_format_string,
2759 p_positive_format_string => l_positive_format_string,
2760 p_min_max => lc_cursor.operator) || '#';
2761
2762 l_index := l_tie_breaking_tab.NEXT(l_index);
2763 END LOOP;
2764
2765 -- -------------------------------------------------------
2766 -- Check for dupes. No need to continue if there are no
2767 -- dupes.
2768 -- -------------------------------------------------------
2769 l_start := dbms_utility.get_time;
2770
2771 BEGIN
2772 SELECT *
2773 INTO l_dup_count
2774 FROM (
2775 SELECT COUNT(*)
2776 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2777 FROM dual) a
2778 WHERE ROWNUM < 2
2779 GROUP BY concat_value_str
2780 HAVING COUNT(*) > 1) b;
2781
2782 EXCEPTION
2783 WHEN no_data_found THEN
2784 --Debug('There are no dupes.');
2785 l_stop_flag := TRUE;
2786
2787 END;
2788
2789
2790 FETCH c_tie_breaking_attr INTO lc_cursor;
2791
2792 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2793 Debug('Finding Dups Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2794 END IF;
2795 END LOOP;
2796
2797 CLOSE c_tie_breaking_attr;
2798
2799
2800 -- DEBUGGING -------------------------------------------------------------
2801 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2802 Debug('Print out concatenated strings...');
2803 l_index := l_tie_breaking_tab.FIRST;
2804 l_last_index := l_tie_breaking_tab.LAST;
2805
2806 WHILE (l_index <= l_last_index) LOOP
2807 Debug(l_tie_breaking_tab(l_index).party_id || ':::' ||
2808 l_tie_breaking_tab(l_index).concat_value_str);
2809 l_index := l_tie_breaking_tab.NEXT(l_index);
2810 END LOOP;
2811 END IF;
2812 -- DEBUGGING -------------------------------------------------------------
2813
2814
2815 -- ------------------------------------------------------------
2816 -- Sort the partners by their concatenated attribute values
2817 -- ------------------------------------------------------------
2818 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2819 Debug('Start sorting..............................');
2820 END IF;
2821
2822 l_start := dbms_utility.get_time;
2823
2824 l_index := 1;
2825
2826 FOR x IN (
2827 SELECT *
2828 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2829 FROM dual) a
2830 ORDER BY concat_value_str DESC)
2831 LOOP
2832 x_partner_tbl(l_index) := x.party_id;
2833 --Debug(x.party_id || ':::' || x.concat_value_str);
2834 l_index := l_index + 1;
2835 END LOOP;
2836
2837
2838 /* =====================================================================
2839 -- ------------------------------------------------------------
2840 -- Sorting by concat_value_str and then by idx will preserve the
2841 -- party_ids order as they are passed in through x_partner_tbl
2842 -- should there be a tie in tie-breaking (e.g. all tie-breaking
2843 -- attributes come up with NULL values).
2844 -- ------------------------------------------------------------
2845 FOR x IN (
2846 SELECT *
2847 FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
2848 FROM dual) a
2849 ORDER BY concat_value_str DESC, idx ASC)
2850 LOOP
2851 x_partner_tbl(l_index) := x.party_id;
2852 --Debug(x.party_id || ':::' || x.concat_value_str);
2853 l_index := l_index + 1;
2854 END LOOP;
2855 * ===================================================================== */
2856
2857
2858 -- DEBUGGING -------------------------------------------------------------
2859 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2860 Debug('Sorting Elapsed Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsec');
2861
2862 Debug('Printing out partner IDs after sorting...');
2863 FOR i IN 1..x_partner_tbl.COUNT LOOP
2864 Debug(x_partner_tbl(i));
2865 END LOOP;
2866 END IF;
2867 -- DEBUGGING -------------------------------------------------------------
2868 END Tie_Breaker;
2869 -- =========================End of Tie_Breaker==================================
2870
2871 --=============================================================================+
2872 --| Public Procedure |
2873 --| |
2874 --| Set_Message |
2875 --| |
2876 --| Parameters |
2877 --| IN |
2878 --| OUT |
2879 --| |
2880 --| |
2881 --| NOTES: |
2882 --| |
2883 --| HISTORY |
2884 --| |
2885 --==============================================================================
2886 PROCEDURE Set_Message(
2887 p_msg_level IN NUMBER,
2888 p_msg_name IN VARCHAR2,
2889 p_token1 IN VARCHAR2,
2890 p_token1_value IN VARCHAR2,
2891 p_token2 IN VARCHAR2 := NULL ,
2892 p_token2_value IN VARCHAR2 := NULL,
2893 p_token3 IN VARCHAR2 := NULL,
2894 p_token3_value IN VARCHAR2 := NULL
2895 )
2896 IS
2897 BEGIN
2898 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
2899 FND_MESSAGE.Set_Name('PV', p_msg_name);
2900 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
2901
2902 IF (p_token2 IS NOT NULL) THEN
2903 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
2904 END IF;
2905
2906 IF (p_token3 IS NOT NULL) THEN
2907 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
2908 END IF;
2909
2910 FND_MSG_PUB.Add;
2911 END IF;
2912 END Set_Message;
2913
2914 --=============================================================================+
2915 --| Private Procedure |
2916 --| |
2917 --| Get_Attr_Length |
2918 --| Given a "string of NUMBER", this procedure will return the length of|
2919 --| the string to the left of the decimal point as well as that of the |
2920 --| string to the right of the decimal point. |
2921 --| |
2922 --| Parameters |
2923 --| IN |
2924 --| OUT |
2925 --| |
2926 --| |
2927 --| NOTES: |
2928 --| |
2929 --| HISTORY |
2930 --| |
2931 --==============================================================================
2932 PROCEDURE Get_Attr_Length(
2933 p_attr_value IN VARCHAR2,
2934 p_left_length OUT NOCOPY NUMBER,
2935 p_right_length OUT NOCOPY NUMBER)
2936 IS
2937 l_attr_value VARCHAR2(2000);
2938
2939 BEGIN
2940 -- --------------------------------------------------------------------------
2941 -- The negative sign should not be counted when counting string length.
2942 -- --------------------------------------------------------------------------
2943 l_attr_value := REPLACE(p_attr_value, '-', '');
2944
2945 IF (INSTR(p_attr_value, '.') = 0) THEN
2946 p_left_length := LENGTH(l_attr_value);
2947 p_right_length := 0;
2948
2949 ELSE
2950 p_left_length := LENGTH(SUBSTR(l_attr_value, 1, INSTR(l_attr_value, '.') - 1));
2951 p_right_length := LENGTH(SUBSTR(l_attr_value, INSTR(l_attr_value, '.') + 1, LENGTH(l_attr_value)));
2952 END IF;
2953 END Get_Attr_Length;
2954 -- ===========================End of Get_Attr_Length============================
2955
2956
2957
2958 --=============================================================================+
2959 --| Private Function |
2960 --| |
2961 --| Build_Format_String |
2962 --| |
2963 --| Parameters |
2964 --| IN |
2965 --| OUT |
2966 --| |
2967 --| |
2968 --| NOTES: |
2969 --| e.g. of value returned: |
2970 --| '09999999.9990', '09999.990' |
2971 --| |
2972 --| HISTORY |
2973 --| |
2974 --==============================================================================
2975 FUNCTION Build_Format_String (
2976 p_max_left_length NUMBER,
2977 p_max_right_length NUMBER)
2978 RETURN VARCHAR2 IS
2979 l_format_string VARCHAR2(100);
2980
2981 BEGIN
2982 l_format_string := '0';
2983
2984 FOR i IN 1..p_max_left_length - 1 LOOP
2985 l_format_string := l_format_string || '9';
2986 END LOOP;
2987
2988 IF (p_max_right_length > 0) THEN
2989 l_format_string := l_format_string || '.';
2990
2991 FOR i IN 1..p_max_right_length - 1 LOOP
2992 l_format_string := l_format_string || '9';
2993 END LOOP;
2994
2995 l_format_string := l_format_string || '0';
2996 END IF;
2997
2998 RETURN l_format_string;
2999 END;
3000 -- ====================End of Build_Format_String==========================
3001
3002
3003
3004 --=============================================================================+
3005 --| Private Function |
3006 --| |
3007 --| Convert_To_String |
3008 --| |
3009 --| Parameters |
3010 --| IN |
3011 --| OUT |
3012 --| |
3013 --| |
3014 --| NOTES: |
3015 --| |
3016 --| HISTORY |
3017 --| |
3018 --==============================================================================
3019 FUNCTION Convert_To_String(p_attr_value NUMBER,
3020 p_max_left_length NUMBER,
3021 p_max_right_length NUMBER,
3022 p_format_string VARCHAR2,
3023 p_positive_format_string VARCHAR2,
3024 p_min_max VARCHAR2)
3025 RETURN VARCHAR2 IS
3026 l_attr_value NUMBER := p_attr_value;
3027 l_format_string VARCHAR2(300);
3028 l_null_string VARCHAR2(300);
3029
3030 BEGIN
3031 -- -----------------------------------------------------------------
3032 -- If the attribute value is NULL, we want to assign it the
3033 -- "smallest" possible VARCHAR2 value with a length of the format
3034 -- string. The "smallest" value would be a string with all '-'s.
3035 -- e.g. '----------'
3036 -- -----------------------------------------------------------------
3037 IF (p_attr_value IS NULL) THEN
3038 FOR i IN 1..LENGTH(p_positive_format_string) LOOP
3039 IF (p_min_max = 'MIN') THEN
3040 l_null_string := l_null_string || '9';
3041 ELSE
3042 l_null_string := l_null_string || '-';
3043 END IF;
3044 END LOOP;
3045
3046 RETURN l_null_string;
3047 END IF;
3048
3049 -- -----------------------------------------------------------------
3050 -- If p_min_max is 'MIN', which means that 40 is
3051 -- ranked higher than 60, then we need to substract the attribute
3052 -- value from x where x is:
3053 -- POWER(10, p_max_attr_length)
3054 --
3055 -- e.g.
3056 -- If p_max_left_length is 3 (e.g. 100, 200, 250, etc.), then
3057 -- x = POWER(10, 3) = 1000
3058 --
3059 -- The attribute value in this case would be (1000 - p_attr_value).
3060 --
3061 -- Also if the attribute value is a negative number, we need to the
3062 -- similar thing described above for 'MIN'. Of course, we want
3063 -- to leave the negative sign intact.
3064 --
3065 -- The reason for doing this is that we are doing string comparsion
3066 -- even though we are really comparing numbers. When a number is
3067 -- negative, a normal string comparsion would yield the opposite
3068 -- result.
3069 -- e.g.
3070 -- '-1.7' > '-1.6' ==> This is TRUE for string comparsion but
3071 -- FALSE for number comparsion.
3072 --
3073 -- In the case of 'MIN' and negative number, nothing needs to be
3074 -- done since it would just be doing the above operation twice,
3075 -- reverting it to its original result.
3076 --
3077 -- Here's the algorithm:
3078 -- IF (negative AND MIN) THEN
3079 -- Turn it into a positive number
3080 -- ELSIF (negative AND MAX) THEN
3081 -- Substract from x (see above) and keep the negative sign
3082 -- ELSIF (positive AND MIN) THEN
3083 -- Substract from x and prefix it with a negative sign
3084 -- ELSIF (positive and MAX) THEN
3085 -- Just pad with 0's
3086 -- END IF;
3087 -- -----------------------------------------------------------------
3088 IF (p_min_max = 'MIN') THEN
3089 -- --------------------------------------------------------------
3090 -- If a positive number.
3091 -- --------------------------------------------------------------
3092 IF (TO_NUMBER(p_attr_value) > 0) THEN
3093 l_attr_value := POWER(10, p_max_left_length) - p_attr_value;
3094 l_attr_value := -l_attr_value;
3095
3096 -- --------------------------------------------------------------
3097 -- If a negative number.
3098 -- --------------------------------------------------------------
3099 ELSIF (TO_NUMBER(p_attr_value) < 0) THEN
3100 l_attr_value := -l_attr_value;
3101 END IF;
3102
3103 ELSIF (p_min_max = 'MAX') THEN
3104 -- --------------------------------------------------------------
3105 -- If a negative number.
3106 -- --------------------------------------------------------------
3107 IF (TO_NUMBER(p_attr_value) < 0) THEN
3108 -- -----------------------------------------------------------
3109 -- Only substract the positive portion of a negative number
3110 -- string from POWER(10, p_max_left_length). This is
3111 -- equivalent of adding it.
3112 -- -----------------------------------------------------------
3113 l_attr_value := POWER(10, p_max_left_length) + p_attr_value;
3114 l_attr_value := -l_attr_value;
3115 END IF;
3116 END IF;
3117
3118 -- -----------------------------------------------------------------
3119 -- If the number is positive, apply the format string for positive
3120 -- numbers. This is to ensure that positive and negative numbers
3121 -- end up with a string with equal length.
3122 --
3123 -- e.g. TO_CHAR(100, '0999') ==> '0100'
3124 -- TO_CHAR(-100, '0999') ==> '-0100'
3125 --
3126 -- We want it to be like this for positive numbers:
3127 -- TO_CHAR(100, '09999') ==> '00100'
3128 -- TO_CHAR(-100, '0999') ==> '-0100'
3129 -- -----------------------------------------------------------------
3130 IF (l_attr_value >= 0) THEN
3131 l_format_string := p_positive_format_string;
3132 ELSE
3133 l_format_string := p_format_string;
3134 END IF;
3135
3136 -- -----------------------------------------------------------------
3137 -- It is important to use LTRIM() here because ORACLE always adds
3138 -- a blank space in front of the converted string unless the number
3139 -- is negative.
3140 -- -----------------------------------------------------------------
3141 RETURN LTRIM(TO_CHAR(l_attr_value, l_format_string));
3142 END Convert_To_String;
3143 -- ====================End of Convert_To_String==========================
3144
3145 -- ====================Start of get_attribute_value========================
3146 FUNCTION get_attribute_value (attribute_id NUMBER ,partner_id NUMBER)
3147 RETURN VARCHAR2
3148 as
3149 CURSOR lc_get_pt_type (pc_attribute_id NUMBER,pc_partner_id NUMBER) is
3150 SELECT pac.description
3151 FROM pv_attribute_codes_vl pac,
3152 pv_enty_attr_values peav
3153 WHERE peav.entity_id = pc_partner_id
3154 AND peav.entity(+) = 'PARTNER'
3155 AND peav.attribute_id(+) = pc_attribute_id
3156 AND peav.attribute_id = pac.attribute_id(+)
3157 AND pac.attr_code(+) = peav.attr_value
3158 AND peav.latest_flag = 'Y'
3159 AND peav.enabled_flag = 'Y';
3160
3161 l_partner_type_str VARCHAR2(500);
3162 BEGIN
3163
3164 FOR c1 IN lc_get_pt_type(pc_attribute_id => attribute_id, pc_partner_id => partner_id)
3165 LOOP
3166 IF c1.description IS NOT NULL THEN
3167 l_partner_type_str := l_partner_type_str || ',' || c1.description;
3168 END IF;
3169
3170 END LOOP;
3171
3172 RETURN substr(l_partner_type_str,2);
3173 END get_attribute_value;
3174 -- ====================End of get_attribute_value==========================
3175
3176 -- ====================Start of get_metric_value========================
3177 FUNCTION get_metric_value(attribute_id NUMBER ,partner_id NUMBER)
3178 RETURN NUMBER
3179 AS
3180 --CURSOR lc_get_metric_raw_value (pc_attribute_id NUMBER,pc_partner_id NUMBER) is
3181
3182 l_get_metric_raw_value varchar2(500) :=
3183
3184 'SELECT pav.return_type, pav.display_style, psav.attr_value,psav.attr_text, pav.decimal_points ' ||
3185 'FROM pv_attributes_vl pav, ' ||
3186 ' pv_search_attr_values psav ' ||
3187 'WHERE psav.PARTY_ID = :1 ' ||
3188 'AND psav.attribute_id = :2 ' ||
3189 'AND psav.attribute_id = pav.attribute_id ' ;
3190
3191 TYPE t_metric_raw_value_cursor IS REF CURSOR;
3192 lc_get_metric_raw_value t_metric_raw_value_cursor;
3193
3194
3195 l_return_type VARCHAR2(100);
3196 l_display_style VARCHAR2(100);
3197 l_attr_value NUMBER;
3198 l_attr_text VARCHAR2(1000);
3199 l_metric_value NUMBER;
3200 l_decimals NUMBER;
3201 BEGIN
3202
3203 --OPEN lc_get_metric_raw_value(pc_attribute_id => attribute_id , pc_partner_id => partner_id);
3204
3205 OPEN lc_get_metric_raw_value FOR l_get_metric_raw_value using partner_id, attribute_id ;
3206 LOOP
3207 FETCH lc_get_metric_raw_value INTO l_return_type,l_display_style,l_attr_value,l_attr_text,l_decimals;
3208 EXIT; -- WHEN lc_get_metric_raw_value%NOTFOUND;
3209 END LOOP;
3210
3211 IF (lc_get_metric_raw_value%NOTFOUND) THEN
3212 l_metric_value := null;
3213 ELSE
3214 IF l_return_type = 'NUMBER' THEN
3215
3216 IF l_display_style = 'PERCENTAGE' THEN
3217
3218 l_metric_value := l_attr_value * 100;
3219 ELSE
3220 l_metric_value := l_attr_value;
3221 END IF;
3222 IF l_decimals IS NOT NULL THEN
3223 l_metric_value := trunc(l_metric_value,l_decimals);
3224 ELSE
3225 l_metric_value := trunc(l_metric_value,2);
3226 END IF;
3227
3228
3229 ELSIF l_return_type = 'CURRENCY' THEN
3230 l_metric_value := pv_check_match_pub.currency_conversion(l_attr_text);
3231
3232 ELSE
3233 l_metric_value := 0;
3234 END IF;
3235
3236
3237 END IF;
3238
3239 RETURN l_metric_value;
3240
3241 END get_metric_value;
3242 -- ====================End of get_metric_value==========================
3243
3244 -- ====================Start of get_currency_metric_value========================
3245 FUNCTION get_currency_metric_value(attribute_id NUMBER ,partner_id NUMBER)
3246 RETURN varchar2
3247 AS
3248 --CURSOR lc_get_metric_raw_value (pc_attribute_id NUMBER,pc_partner_id NUMBER) is
3249
3250 l_get_metric_raw_value varchar2(500) :=
3251
3252 'SELECT pav.return_type, pav.display_style, psav.attr_value,psav.attr_text, pav.decimal_points ' ||
3253 'FROM pv_attributes_vl pav, ' ||
3254 ' pv_search_attr_values psav ' ||
3255 'WHERE psav.PARTY_ID = :1 ' ||
3256 'AND psav.attribute_id = :2 ' ||
3257 'AND psav.attribute_id = pav.attribute_id ' ;
3258
3259 TYPE t_metric_raw_value_cursor IS REF CURSOR;
3260 lc_get_metric_raw_value t_metric_raw_value_cursor;
3261
3262
3263 l_return_type VARCHAR2(100);
3264 l_display_style VARCHAR2(100);
3265 l_attr_value NUMBER;
3266 l_attr_text VARCHAR2(1000);
3267 l_metric_value VARCHAR2(100);
3268 l_decimals NUMBER;
3269 l_user_currency_code VARCHAR(30) :=nvl(fnd_profile.value('ICX_PREFERRED_CURRENCY'), 'USD');
3270 --l_curr_value VARCHAR2(80);
3271 BEGIN
3272
3273 --OPEN lc_get_metric_raw_value(pc_attribute_id => attribute_id , pc_partner_id => partner_id);
3274
3275 OPEN lc_get_metric_raw_value FOR l_get_metric_raw_value using partner_id, attribute_id ;
3276 LOOP
3277 FETCH lc_get_metric_raw_value INTO l_return_type,l_display_style,l_attr_value,l_attr_text,l_decimals;
3278 EXIT; -- WHEN lc_get_metric_raw_value%NOTFOUND;
3279 END LOOP;
3280
3281 IF (lc_get_metric_raw_value%NOTFOUND) THEN
3282 l_metric_value := null;
3283 ELSE
3284 IF l_return_type = 'CURRENCY' THEN
3285 begin
3286 --select name into l_curr_value from fnd_currencies_vl
3287 --where currency_code=l_user_currency_code;
3288
3289 l_metric_value := pv_check_match_pub.Currency_Conversion(
3290 l_attr_text,
3291 l_user_currency_code
3292 ) || ' ' || l_user_currency_code;
3293
3294 exception
3295 when others then
3296 --dbms_output.put_line('error:' || SQLERRM);
3297 l_metric_value:= '';
3298 end;
3299
3300
3301 ELSE
3302 l_metric_value := '';
3303 END IF;
3304
3305
3306 END IF;
3307
3308 RETURN l_metric_value;
3309
3310 END get_currency_metric_value;
3311
3312 -- ====================End of get_currency_metric_value===============
3313
3314
3315 -- ====================Start of get_assign_status_meaning===============
3316 FUNCTION get_assign_status_meaning (p_lead_id NUMBER, p_partner_id NUMBER)
3317 RETURN VARCHAR2
3318 AS
3319 CURSOR lc_assign_status (pc_lead_id NUMBER, pc_partner_id NUMBER) is
3320 SELECT DECODE(pla.status,'MATCH_WITHDRAWN','UNASSIGNED'
3321 ,'MATCH_WITHDRAWN','UNASSIGNED'
3322 ,'OFFER_WITHDRAWN','UNASSIGNED'
3323 ,'PT_TIMEOUT','UNASSIGNED'
3324 ,'ACTIVE_WITHDRAWN','UNASSIGNED'
3325 ,'LOST_CHANCE','UNASSIGNED'
3326 ,'CM_ADDED','ASSIGNED'
3327 ,'PT_REJECTED','CM_REJECTED'
3328 ,'CM_BYPASSED','CM_APPROVED'
3329 ,'CM_TIMEOUT','CM_APPROVED'
3330 ,'CM_APP_FOR_PT','PT_APPROVED'
3331 ,'CM_ADD_APP_FOR_PT','PT_APPROVED'
3332 ,'PT_CREATED','PT_APPROVED'
3333 ,pla.status) assign_status
3334 , plw.wf_status
3335 , plw.routing_status
3336 , pla.status
3337 , plw.routing_type
3338 , pla.wf_item_key
3339 , pla.assign_sequence
3340 FROM pv_lead_assignments pla,
3341 pv_lead_workflows plw
3342 WHERE plw.lead_id = pc_lead_id
3343 AND plw.wf_item_key = pla.wf_item_key
3344 AND plw.wf_item_type = pla.wf_item_type
3345 AND pla.partner_id = pc_partner_id
3346 AND plw.latest_routing_flag = 'Y';
3347
3348 CURSOR lc_status_meaning (pc_status VARCHAR2) IS
3349 SELECT MEANING
3350 FROM PV_LOOKUPS
3351 WHERE LOOKUP_TYPE = 'PV_ASN_ASSIGNMENT_STATUS'
3352 AND LOOKUP_CODE = pc_status;
3353
3354 CURSOR lc_curr_rank (p_wf_item_key NUMBER) IS
3355 SELECT wf_engine.GetItemAttrNumber( 'PVASGNMT',p_wf_item_key,'PV_SERIAL_RANK_ATTR')
3356 FROM dual;
3357
3358 l_assign_status VARCHAR2(500) := 'UNASSIGNED';
3359 l_wf_status VARCHAR2(500);
3360 l_routing_status VARCHAR2(500);
3361 l_routing_type VARCHAR2(50);
3362 l_wf_item_key VARCHAR2(10);
3363 l_curr_offered_rank NUMBER;
3364 l_curr_partner_in_serial VARCHAR2(10);
3365 l_assign_seq NUMBER;
3366
3367 l_assign_status_mean VARCHAR2(500);
3368 BEGIN
3369
3370 FOR c1 IN lc_assign_status(pc_lead_id => p_lead_id
3371 , pc_partner_id => p_partner_id)
3372 LOOP
3373 l_wf_status := c1.wf_status;
3374 l_routing_status := c1.routing_status;
3375 l_routing_type := c1.routing_type;
3376 l_wf_item_key := c1.wf_item_key;
3377 l_assign_seq := c1.assign_sequence;
3378 l_assign_status := c1.assign_status;
3379
3380 -- Cases like recycled, abandonded, partner timeout will come
3381 -- under this case. the wf is closed and not active.
3382 IF l_wf_status = 'CLOSED' AND l_routing_status <> 'ACTIVE' THEN
3383 l_assign_status := 'UNASSIGNED';
3384 -- If wf_status is null that means it is unassigned
3385 ELSIF l_wf_status IS NULL THEN
3386 l_assign_status := 'UNASSIGNED';
3387 -- This works only in serial case. If the routing is serial and
3388 -- the current partner is the one to whom it is offered then it
3389 -- must say offered not active
3390 ELSIF l_routing_type = 'SERIAL' THEN
3391 BEGIN
3392 OPEN lc_curr_rank(l_wf_item_key);
3393 FETCH lc_curr_rank into l_curr_offered_rank;
3394 CLOSE lc_curr_rank;
3395
3396 IF l_assign_seq = l_curr_offered_rank THEN
3397 l_curr_partner_in_serial := 'Y';
3398 END IF;
3399
3400 EXCEPTION
3401 WHEN OTHERS THEN
3402 l_curr_partner_in_serial := 'N';
3403 END;
3404
3405 /*
3406 IF l_curr_partner_in_serial = 'Y' THEN
3407 l_assign_status := 'OFFERED';
3408 END IF;
3409 */
3410 --for bug# 4217950
3411 IF(l_wf_status = 'OPEN' and l_curr_partner_in_serial = 'Y') THEN
3412 l_assign_status := 'OFFERED';
3413 END IF;
3414 -- end of change fir bug# 4217950
3415
3416 -- If the routing status is Offered then all rows that are Approved
3417 -- should say Offered.
3418 ELSIF l_routing_status = 'OFFERED'
3419 AND c1.assign_status = 'CM_APPROVED'
3420 AND l_routing_type <> 'SERIAL' THEN
3421 l_assign_status := 'OFFERED';
3422 ELSE
3423 l_assign_status := c1.assign_status;
3424 END IF;
3425 END LOOP;
3426
3427 OPEN lc_status_meaning(pc_status => l_assign_status);
3428 FETCH lc_status_meaning INTO l_assign_status_mean;
3429
3430 IF (lc_status_meaning%NOTFOUND) THEN
3431 l_assign_status_mean := null;
3432 END IF;
3433
3434 RETURN l_assign_status_mean;
3435 END get_assign_status_meaning;
3436 -- ====================End of get_assign_status_meaning==================
3437 -- ====================Start of get_partner_types========================
3438 FUNCTION get_partner_types (partner_id NUMBER)
3439 RETURN VARCHAR2
3440 as
3441 CURSOR lc_get_pt_type (pc_partner_id NUMBER) is
3442 SELECT pac.description
3443 FROM pv_attribute_codes_vl pac,
3444 pv_enty_attr_values peav
3445 WHERE peav.entity_id = pc_partner_id
3446 AND peav.entity(+) = 'PARTNER'
3447 AND peav.attribute_id(+) = 3
3448 AND peav.attribute_id = pac.attribute_id(+)
3449 AND pac.attr_code(+) = peav.attr_value
3450 AND peav.latest_flag = 'Y'
3451 AND peav.enabled_flag = 'Y'
3452 AND peav.attr_value_extn = 'Y' ;
3453
3454 l_partner_type_str VARCHAR2(500);
3455 BEGIN
3456
3457 FOR c1 IN lc_get_pt_type(pc_partner_id => partner_id)
3458 LOOP
3459 l_partner_type_str := l_partner_type_str || ',' || c1.description;
3460 END LOOP;
3461
3462 RETURN substr(l_partner_type_str,2);
3463 END get_partner_types;
3464 -- ====================End of get_partner_types==========================
3465
3466 -- ====================Start of pref_partner_flag========================
3467 FUNCTION pref_partner_flag (p_lead_id NUMBER, p_partner_id NUMBER)
3468 RETURN VARCHAR2
3469 AS
3470 CURSOR lc_incumbent_part (pc_lead_id NUMBER, pc_partner_id NUMBER) is
3471 SELECT 'Y'
3472 FROM as_leads_all
3473 WHERE lead_id = pc_lead_id
3474 AND incumbent_partner_party_id = pc_partner_id;
3475
3476 l_pref_part_str VARCHAR2(1);
3477 BEGIN
3478
3479 l_pref_part_str := 'N';
3480
3481 OPEN lc_incumbent_part(p_lead_id , p_partner_id);
3482 FETCH lc_incumbent_part INTO l_pref_part_str;
3483
3484 IF (lc_incumbent_part%NOTFOUND) THEN
3485 l_pref_part_str := 'N';
3486 END IF;
3487
3488 CLOSE lc_incumbent_part;
3489
3490 RETURN l_pref_part_str;
3491 END pref_partner_flag;
3492 -- ====================End of pref_partner_flag==========================
3493 -- ====================Begin of is_cm_flag==========================
3494 FUNCTION lock_flag (p_lead_assign_id NUMBER, p_wf_item_key VARCHAR2)
3495 RETURN VARCHAR2
3496 AS
3497 CURSOR lc_cm_for_partner (pc_lead_assign_id NUMBER, pc_wf_item_key VARCHAR2)
3498 IS
3499 SELECT 'N'
3500 FROM pv_party_notifications
3501 WHERE lead_assignment_id = pc_lead_assign_id
3502 AND wf_item_type = 'PVASGNMT'
3503 AND wf_item_key = pc_wf_item_key
3504 --AND notification_type = 'MATCHED_TO'
3505 AND user_id = FND_GLOBAL.user_id;
3506
3507 CURSOR lc_routing_status (pc_wf_item_key VARCHAR2)
3508 IS
3509 SELECT routing_status
3510 FROM pv_lead_workflows
3511 WHERE wf_item_key = pc_wf_item_key
3512 AND wf_item_type = 'PVASGNMT'
3513 AND latest_routing_flag = 'Y';
3514
3515 CURSOR lc_user_type
3516 IS
3517 SELECT jtfre.category
3518 FROM jtf_rs_resource_extns jtfre
3519 WHERE jtfre.user_id = FND_GLOBAL.user_id
3520 AND start_date_active <= sysdate
3521 AND (end_date_active IS NULL OR end_date_active > sysdate);
3522
3523
3524 l_lock_flag VARCHAR2(1);
3525 l_routing_status VARCHAR2(50);
3526 l_user_type VARCHAR2(50);
3527 BEGIN
3528 -- In the UI we are using an API which locks the
3529 -- row for value 'Y' and leaves it enabled for value 'N'
3530
3531 -- Added fix to make sure checking for the vendor and statuses
3532 -- before showing lock flag. This was found when testing fix for
3533 -- Bug 4115728
3534 l_lock_flag := 'Y';
3535
3536 OPEN lc_routing_status(p_wf_item_key);
3537 FETCH lc_routing_status INTO l_routing_status;
3538
3539 IF (lc_routing_status%NOTFOUND
3540 OR l_routing_status = 'UNASSIGNED'
3541 OR l_routing_status = 'WITHDRAWN'
3542 OR l_routing_status = 'RECYCLED'
3543 OR l_routing_status = 'ABANDONED'
3544 OR l_routing_status = 'ACTIVE') THEN
3545
3546 OPEN lc_user_type;
3547 FETCH lc_user_type INTO l_user_type;
3548
3549 IF l_user_type = 'EMPLOYEE' THEN
3550 l_lock_flag := 'N';
3551 ELSE
3552 OPEN lc_cm_for_partner(p_lead_assign_id , p_wf_item_key);
3553 FETCH lc_cm_for_partner INTO l_lock_flag;
3554
3555 IF (lc_cm_for_partner%NOTFOUND) THEN
3556 l_lock_flag := 'Y';
3557 END IF;
3558 CLOSE lc_cm_for_partner;
3559 END IF;
3560
3561 CLOSE lc_user_type;
3562
3563 ELSE
3564 OPEN lc_cm_for_partner(p_lead_assign_id , p_wf_item_key);
3565 FETCH lc_cm_for_partner INTO l_lock_flag;
3566
3567 IF (lc_cm_for_partner%NOTFOUND) THEN
3568 l_lock_flag := 'Y';
3569 END IF;
3570 CLOSE lc_cm_for_partner;
3571
3572 END IF;
3573
3574 CLOSE lc_routing_status;
3575
3576
3577 RETURN l_lock_flag;
3578 END lock_flag;
3579 -- ====================End of is_cm_flag==========================
3580 --=============================================================================+
3581 --| Public Procedure |
3582 --| |
3583 --| PROCEDURE Clear_Rules_Cache |
3584 --| |
3585 --| |
3586 --| Parameters |
3587 --| IN |
3588 --| OUT |
3589 --| |
3590 --| |
3591 --| NOTES |
3592 --| |
3593 --| HISTORY |
3594 --| |
3595 --==============================================================================
3596 PROCEDURE Clear_Rules_Cache IS
3597 BEGIN
3598 g_opp_selection_tab.DELETE;
3599 END;
3600 -- ==========================End of Clear_Rules_Cache===========================
3601
3602 FUNCTION Get_Next_Rule_Index(
3603 p_current_index NUMBER,
3604 p_opp_selection_tab t_opp_selection_tab
3605 )
3606 RETURN NUMBER
3607 IS
3608 i NUMBER := p_current_index;
3609 l_current_rule_id NUMBER := p_opp_selection_tab(p_current_index).process_rule_id;
3610 l_last_index NUMBER := p_opp_selection_tab.LAST;
3611
3612 BEGIN
3613 -- ---------------------------------------------------------------------------
3614 -- We're already at the end of the array. No more rules to evaluate.
3615 -- ---------------------------------------------------------------------------
3616 IF (i = l_last_index) THEN
3617 RETURN g_no_more_Rules;
3618 END IF;
3619
3620 -- ---------------------------------------------------------------------------
3621 -- Starting from the next item in the array, if the process_rule_id changes,
3622 -- this indicates the start of a new record. Return the index of this record.
3623 -- ---------------------------------------------------------------------------
3624 i := i + 1;
3625
3626 WHILE ((p_opp_selection_tab(i).process_rule_id = l_current_rule_id) AND
3627 (i < l_last_index))
3628 LOOP
3629 i := i + 1;
3630 END LOOP;
3631
3632 -- ---------------------------------------------------------------------------
3633 -- We've reached the end of the index, no more rules to evaluate.
3634 -- ---------------------------------------------------------------------------
3635 IF ((i = l_last_index) AND
3636 (p_opp_selection_tab(i).process_rule_id = l_current_rule_id))
3637 THEN
3638 i := g_no_more_rules;
3639 END IF;
3640
3641 RETURN i;
3642
3643 -- -------------------------------------------------------
3644 -- May want to capture INDEX OUT OF BOUND here by trapping
3645 -- ORA-01403: no data found
3646 -- -------------------------------------------------------
3647 END Get_Next_Rule_Index;
3648 -- ===========================End of Get_Next_Rule_Index===========================
3649
3650 PROCEDURE Cache_Rules IS
3651 -- --------------------------------------------------------------------
3652 -- Note that we need an outer join from pv_enty_select_criteria to
3653 -- pv_selected_attr_value
3654 -- since certain attributes (e.g. <condition> is not null) will not
3655 -- have attribute values.
3656 -- --------------------------------------------------------------------
3657
3658 CURSOR lc_opp_selection IS
3659 SELECT prr.process_rule_id,
3660 prr.rank,
3661 pesc.attribute_id,
3662 prr.currency_code,
3663 pesc.operator,
3664 pesc.selection_criteria_id,
3665 psav.attribute_value,
3666 psav.attribute_to_value
3667 FROM pv_process_rules_vl prr,
3668 pv_enty_select_criteria pesc,
3669 pv_selected_attr_values psav
3670 WHERE prr.process_rule_id = pesc.process_rule_id AND
3671 pesc.selection_criteria_id = psav.selection_criteria_id (+) AND
3672 prr.status_code = 'ACTIVE' AND
3673 TRUNC(SYSDATE) BETWEEN prr.start_date AND
3674 NVL(prr.end_date, to_DATE('31-12-4000', 'DD-MM-YYYY')) AND
3675 pesc.selection_type_code = 'OPPORTUNITY_SELECTION'
3676 ORDER BY prr.rank DESC, prr.process_rule_id, pesc.attribute_id,
3677 pesc.selection_criteria_id;
3678
3679 i NUMBER := 1;
3680 l_count NUMBER;
3681 l_or_count NUMBER;
3682 l_next_index NUMBER;
3683 l_next_rule_id NUMBER;
3684 l_next_attribute_id NUMBER;
3685 l_next_sc_id NUMBER;
3686 l_lower_index NUMBER;
3687
3688 BEGIN
3689 -- --------------------------------------------------------------------------
3690 -- Cache rules only if they are not already cached.
3691 -- --------------------------------------------------------------------------
3692 IF (g_opp_selection_tab.COUNT = 0) THEN
3693 FOR l_opp_selection IN lc_opp_selection LOOP
3694 g_opp_selection_tab(i).rank := l_opp_selection.rank;
3695 g_opp_selection_tab(i).process_rule_id := l_opp_selection.process_rule_id;
3696 g_opp_selection_tab(i).attribute_id := l_opp_selection.attribute_id;
3697 g_opp_selection_tab(i).currency_code := l_opp_selection.currency_code;
3698 g_opp_selection_tab(i).operator := l_opp_selection.operator;
3699 g_opp_selection_tab(i).selection_criteria_id := l_opp_selection.selection_criteria_id;
3700 g_opp_selection_tab(i).attribute_value := l_opp_selection.attribute_value;
3701 g_opp_selection_tab(i).attribute_to_value := l_opp_selection.attribute_to_value;
3702
3703 i := i + 1;
3704 END LOOP;
3705
3706 -- --------------------------------------------------------------------------------
3707 -- Set last_attr_flag and count in g_opp_selection_tab.
3708 -- --------------------------------------------------------------------------------
3709 l_count := g_opp_selection_tab.COUNT;
3710 l_or_count := 1;
3711
3712 FOR i IN 1..l_count LOOP
3713 IF (i = l_count) THEN
3714 g_opp_selection_tab(i).last_attr_flag := 'Y';
3715
3716 l_lower_index := i - l_or_count + 1;
3717
3718 FOR j IN l_lower_index..i LOOP
3719 g_opp_selection_tab(j).count := l_or_count;
3720 END LOOP;
3721
3722 ELSE
3723 l_next_index := g_opp_selection_tab.NEXT(i);
3724 l_next_rule_id := g_opp_selection_tab(l_next_index).process_rule_id;
3725 l_next_attribute_id := g_opp_selection_tab(l_next_index).attribute_id;
3726 l_next_sc_id := g_opp_selection_tab(l_next_index).selection_criteria_id;
3727
3728 -- ---------------------------------------------------------------------------
3729 -- If the current process_rule_id is not the same as the next
3730 -- process_rule_id, then this is the last attribute in the rule.
3731 -- ---------------------------------------------------------------------------
3732 IF (l_next_rule_id <> g_opp_selection_tab(i).process_rule_id) THEN
3733 g_opp_selection_tab(i).last_attr_flag := 'Y';
3734 ELSE
3735 g_opp_selection_tab(i).last_attr_flag := 'N';
3736 END IF;
3737
3738 -- ---------------------------------------------------------------------------
3739 -- Set g_opp_selection_tab.count. This field is used to indicate the number
3740 -- of records involved in an AND/OR logic. If the count is 1, then the logic
3741 -- is AND. If the count is greater than 1, the logic is OR.
3742 -- ---------------------------------------------------------------------------
3743 IF (l_next_rule_id = g_opp_selection_tab(i).process_rule_id) AND
3744 (l_next_attribute_id = g_opp_selection_tab(i).attribute_id) AND
3745 (l_next_sc_id = g_opp_selection_tab(i).selection_criteria_id)
3746 THEN
3747 l_or_count := l_or_count + 1;
3748
3749 ELSE
3750 l_lower_index := i - l_or_count + 1;
3751
3752 FOR j IN l_lower_index..i LOOP
3753 g_opp_selection_tab(j).count := l_or_count;
3754 END LOOP;
3755
3756 l_or_count := 1;
3757 END IF;
3758 END IF;
3759 END LOOP;
3760 END IF;
3761
3762 -- -------------------------------------------------------------------
3763 -- Debugging code
3764 -- -------------------------------------------------------------------
3765
3766 IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3767 l_count := g_opp_selection_tab.COUNT;
3768
3769 FOR i IN 1..l_count LOOP
3770 Debug('===== ' || i || ' ===========================================');
3771 Debug(g_opp_selection_tab(i).rank || '::' ||
3772 g_opp_selection_tab(i).process_rule_id || '::' ||
3773 g_opp_selection_tab(i).attribute_id || '::' ||
3774 g_opp_selection_tab(i).currency_code || '::' ||
3775 g_opp_selection_tab(i).operator || '::' ||
3776 g_opp_selection_tab(i).selection_criteria_id || '::' ||
3777 g_opp_selection_tab(i).attribute_value || '::' ||
3778 g_opp_selection_tab(i).attribute_to_value || '::' ||
3779 g_opp_selection_tab(i).last_attr_flag || '::' ||
3780 g_opp_selection_tab(i).count
3781 );
3782 END LOOP;
3783 END IF;
3784
3785 END Cache_Rules;
3786 -- =============================End of Cache_Rules==============================
3787
3788 END PV_MATCH_V3_PUB;
[Home] [Help]