[Home] [Help]
PACKAGE BODY: APPS.PV_MATCH_PARTNER
Source
1 package body PV_MATCH_PARTNER as
2 /* $Header: pvxpmatb.pls 115.26 2004/05/25 20:32:12 dhii ship $*/
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* privateroutines */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13
14
15
16 --=============================================================================+
17 --| Procedure |
18 --| |
19 --| Match_partner |
20 --| This procedure Matches partner for a given where condition. |
21 --| to insert rows into pv_enty_attr_text for each partner |
22 --| |
23 --| Parameters |
24 --| IN |
25 --| OUT |
26 --| |
27 --| |
28 --| NOTES |
29 --| |
30 --| HISTORY |
31 --| |
32 --==============================================================================
33
34
35
36 Procedure Match_partner(p_api_version_number IN NUMBER,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 p_commit IN VARCHAR2 := FND_API.G_FALSE,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 p_sql IN VArchar2,
41 p_num_of_attrs IN NUMBER,
42 x_matched_prt OUT JTF_VARCHAR2_TABLE_100,
43 x_prt_matched OUT boolean,
44 x_matched_attr_cnt OUT NUMBER,
45 x_return_status OUT VARCHAR2,
46 x_msg_count OUT NUMBER,
47 x_msg_data OUT VARCHAR2) IS
48
49 Type Match_Partner_Rec is REF CURSOR;
50
51 match_partner_cur Match_Partner_Rec;
52 l_possible_match_party_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
53 l_possible_match_rank_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
54 l_possible_match_count number := 0;
55 l_possible_rank_high number := 0;
56 l_match_count number := 0;
57 l_top_n_rows number;
58 l_rank_base_2 number;
59 l_matching_rank number;
60 l_combined_rank number;
61 l_tmp_true_rank number;
62 l_tmp_matching_rank number;
63 l_tmp_combined_rank number;
64 l_attr_count number;
65 partner_id number;
66 l_all_ranks varchar2(1000);
67
68 l_api_name CONSTANT VARCHAR2(30) := 'Match_partner';
69 l_api_version_number CONSTANT NUMBER := 1.0;
70
71 begin
72
73 -- Standard call to check for call compatibility.
74
75 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
76 p_api_version_number,
77 l_api_name,
78 G_PKG_NAME) THEN
79
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81
82 END IF;
83
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 fnd_msg_pub.initialize;
88 END IF;
89
90 x_return_status := FND_API.G_RET_STS_SUCCESS ;
91 x_matched_prt := JTF_VARCHAR2_TABLE_100();
92
93
94 l_top_n_rows :=nvl(fnd_profile.value('PV_TOP_N_MATCH_PARTNERS'), 10);
95 l_matching_rank := 1;
96 l_combined_rank := 1;
97 l_all_ranks := ' 1 ';
98
99 for i in 1..p_num_of_attrs - 1 loop
100
101 l_matching_rank := l_matching_rank * 2;
102 l_combined_rank := l_combined_rank + l_matching_rank;
103 l_all_ranks := l_all_ranks || ' ' || l_combined_rank || ' '; -- like ' 1 3 8 15 31...etc'
104
105 end loop;
106
107
108 open match_partner_cur for p_sql;
109 loop
110
111 fetch match_partner_cur into partner_id, l_rank_base_2;
112 exit when match_partner_cur%NOTFOUND or x_matched_prt.count = l_top_n_rows;
113
114
115 while (mod (l_rank_base_2, 2) <> 0 /* ignore even numbers which will never match */
116 and l_combined_rank > l_rank_base_2 /* stop when combined rank drops below current rank */
117 and l_match_count = 0) /* only decrease combined rank if no partners matched */
118 loop
119
120 if instr(l_all_ranks, ' ' || l_rank_base_2 || ' ') = 0 then
121
122 -- not a complete match. eg. 'matches rank 1, 2, 4, 16. (missing 8). adds up to 23.
123 -- true rank is 7. that is, sum of consecutive ranks
124 -- will always match at least rank 1 if odd number rank. so find out the true rank
125
126 l_tmp_combined_rank := l_combined_rank;
127 l_tmp_matching_rank := l_matching_rank;
128 l_tmp_true_rank := l_rank_base_2;
129
130 while l_tmp_combined_rank <> l_tmp_true_rank
131 loop
132
133 if l_tmp_combined_rank > l_tmp_true_rank then
134
135 l_tmp_combined_rank := l_tmp_combined_rank - l_tmp_matching_rank;
136
137 end if;
138
139 if l_tmp_true_rank > l_tmp_combined_rank then
140
141 l_tmp_true_rank := l_tmp_true_rank - l_tmp_matching_rank;
142
143 end if;
144
145 l_tmp_matching_rank := l_tmp_matching_rank / 2;
146
147 end loop;
148
149
150 if l_tmp_true_rank > l_possible_rank_high then
151 l_possible_rank_high := l_tmp_true_rank;
152 end if;
153
154 l_possible_match_count := l_possible_match_count + 1;
155 l_possible_match_party_tbl.extend();
156 l_possible_match_rank_tbl.extend();
157
158 l_possible_match_party_tbl(l_possible_match_count) := partner_id;
159 l_possible_match_rank_tbl(l_possible_match_count) := l_tmp_true_rank;
160
161 exit;
162
163 else
164
165 l_combined_rank := l_combined_rank - l_matching_rank;
166 l_matching_rank := l_matching_rank / 2;
167
168
169 end if;
170
171 for i in 1..l_possible_match_count loop
172
173 if l_combined_rank = l_possible_match_rank_tbl(i) then
174
175 l_match_count := l_match_count + 1;
176 x_matched_prt.extend;
177 x_matched_prt(l_match_count) := l_possible_match_party_tbl(i);
178
179
183
180 l_possible_match_rank_tbl(i) := 0; -- so that it doesn't get added again the next time around
181
182 end if;
184 end loop;
185
186 end loop;
187
188 if l_combined_rank = l_rank_base_2 then
189
190
191 l_match_count := l_match_count + 1;
192 x_matched_prt.extend;
193 x_matched_prt(l_match_count) := partner_id;
194
195 else
196 if l_match_count > 0 then
197 exit;
198 end if;
199 end if;
200
201 end loop;
202
203 close match_partner_cur;
204
205 if x_matched_prt.count = 0 then
206
207 l_matching_rank := l_possible_rank_high;
208
209 for i in 1..l_possible_match_count loop
210
211 if l_matching_rank = l_possible_match_rank_tbl(i) then
212
213 l_match_count := l_match_count + 1;
214 x_matched_prt.extend;
215 x_matched_prt(l_match_count) := l_possible_match_party_tbl(i);
216
217
218 end if;
219
220 end loop;
221
222 end if;
223
224 -- Set flag to true / false depending on whether partners are matched or not
225
226 if x_matched_prt.count > 0 then
227
228 x_prt_matched := true;
229
230 l_attr_count := 0;
231 while (l_matching_rank >= 1)
232 loop
233 l_attr_count := l_attr_count + 1;
234 l_matching_rank := l_matching_rank / 2;
235 end loop;
236
237 x_matched_attr_cnt := l_attr_count;
238
239 else
240 x_prt_matched := false;
241 x_matched_attr_cnt := 0;
242 end if;
243
244
245 IF FND_API.To_Boolean ( p_commit ) THEN
246 COMMIT WORK;
247 END IF;
248
249 -- Standard call to get message count and if count is 1, get message info.
250 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
251 p_count => x_msg_count,
252 p_data => x_msg_data);
253
254 EXCEPTION
255 WHEN FND_API.G_EXC_ERROR THEN
256
257 x_return_status := FND_API.G_RET_STS_ERROR ;
258
259 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
260 p_count => x_msg_count,
261 p_data => x_msg_data);
262
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
266
267 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
268 p_count => x_msg_count,
269 p_data => x_msg_data);
270
271 WHEN OTHERS THEN
272
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274
275 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
276
277 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
278 p_count => x_msg_count,
279 p_data => x_msg_data);
280
281 END Match_partner;
282
283
284
285 /*************************************************************************************/
286
287 /* public routines */
288 /* */
289 /*************************************************************************************/
290
291
292
293 --=============================================================================+
294 --| Procedure |
295 --| |
296 --| Form_WHere_clause |
297 --| This procedure Takes attributes and their values and forms where |
298 --| condition to search for partners. It keeps on dropping attributes |
299 --| in where condition until a partner is found or they get exhausted |
300 --| |
301 --| Parameters |
302 --| IN |
303 --| OUT |
304 --| |
305 --| |
306 --| NOTES |
307 --| |
308 --| HISTORY |
309 --| |
310 --==============================================================================
311
312
313 procedure Form_Where_Clause(
314 p_api_version_number IN NUMBER,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_commit IN VARCHAR2 := FND_API.G_FALSE,
317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
318 p_attr_tbl IN OUT JTF_VARCHAR2_TABLE_100,
319 p_attr_val_count IN JTF_VARCHAR2_TABLE_100,
320 p_val_attr_tbl IN OUT JTF_VARCHAR2_TABLE_100,
321 p_cm_id IN number,
322 p_lead_id IN number,
323 p_auto_match_flag IN varchar2,
324 x_iterations OUT varchar2,
325 x_matched_id_tbl OUT JTF_VARCHAR2_TABLE_100,
326 x_return_status OUT VARCHAR2,
327 x_msg_count OUT NUMBER,
328 x_msg_data OUT VARCHAR2) IS
329
330
331 Type l_tmp is Table of Varchar2(4000) index by binary_integer;
332
333 l_tmp_tbl l_tmp;
334 l_tmp_tbl1 l_tmp;
335 l_where Varchar2(32000);
336 l_value_count Number;
337 l_tmp_where Varchar2(4000);
338 attr_seq NUMBER := 1;
339 l_attr_val_count NUmber;
340 l_attr VARCHAR2(100);
341 l_attr_value VARCHAR2(100);
342 l_prt_matched boolean := true;
343 l_incumbent_pt_party_id number;
344 l_matched_attr_cnt number;
345 l_rank_base_2 number := 1;
346 l_incumbent_exists_flag boolean;
347 tbl_cnt Number;
348 cnt Number;
349 l_iterations Number := 0;
350
351 l_api_name CONSTANT VARCHAR2(30) := 'Form_Where_Clause';
352 l_api_version_number CONSTANT NUMBER := 1.0;
353
354 cursor lc_get_incumbent_pt (pc_lead_id number) is
355 select INCUMBENT_PARTNER_PARTY_ID
356 from as_leads_all
357 where lead_id = pc_lead_id;
358
359 begin
360
361
362 -- Standard call to check for call compatibility.
363
364 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
365 p_api_version_number,
366 l_api_name,
367 G_PKG_NAME) THEN
368
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370
371 END IF;
372
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF FND_API.to_Boolean( p_init_msg_list )
375 THEN
376 fnd_msg_pub.initialize;
377 END IF;
378
379 x_return_status := FND_API.G_RET_STS_SUCCESS ;
380 x_matched_id_tbl := JTF_VARCHAR2_TABLE_100();
381
382
383 IF FND_API.To_Boolean ( p_commit ) THEN
384 COMMIT WORK;
385 END IF;
386
387 -- Standard call to get message count and if count is 1, get message info.
388 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
389 p_count => x_msg_count,
390 p_data => x_msg_data);
391 EXCEPTION
392
393 WHEN FND_API.G_EXC_ERROR THEN
394
395 x_return_status := FND_API.G_RET_STS_ERROR ;
396
397 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
398 p_count => x_msg_count,
399 p_data => x_msg_data);
400
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
404
405 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
406 p_count => x_msg_count,
407 p_data => x_msg_data);
408
409 WHEN OTHERS THEN
410
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412
413 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
414
415 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
416 p_count => x_msg_count,
417 p_data => x_msg_data);
418
419 END Form_Where_Clause;
420
421
422
423
424 --=============================================================================+
425 --| Procedure |
426 --| |
427 --| Auto_match_criteria |
428 --| This procedure creats attributes and their values in case of |
429 --| Automatic search of partners |
430 --| |
431 --| |
432 --| Parameters |
433 --| IN |
434 --| OUT |
435 --| |
436 --| |
437 --| NOTES |
438 --| |
439 --| HISTORY |
440 --| |
441 --==============================================================================
442
443
444 Procedure Auto_Match_Criteria (
445 p_api_version_number IN NUMBER,
446 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
447 p_commit IN VARCHAR2 := FND_API.G_FALSE,
448 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
449 p_lead_id IN Number,
450 x_matched_attr OUT JTF_VARCHAR2_TABLE_100,
451 x_matched_attr_val OUT JTF_VARCHAR2_TABLE_100,
452 x_original_attr OUT JTF_VARCHAR2_TABLE_100,
453 x_original_attr_val OUT JTF_VARCHAR2_TABLE_100,
454 x_iterations OUT varchar2,
455 x_matched_id OUT JTF_VARCHAR2_TABLE_100,
456 x_return_status OUT VARCHAR2,
457 x_msg_count OUT NUMBER,
458 x_msg_data OUT VARCHAR2) IS
459
460 l_attr_val_cnt JTF_VARCHAR2_TABLE_100;
461
462 cursor attr_cur is
463 select a.attribute_id , a.sql_text , upper(a.src_pkcol_name) src_pkcol_name, v.short_name
464 from pv_entity_attrs a, pv_attributes_vl v
465 where a.attribute_id = v.attribute_id
466 and a.entity = 'LEAD'
467 and a.enabled_flag= 'Y'
468 and a.auto_assign_flag='Y'
469 and v.enabled_flag='Y'
470 order by a.rank;
471
472 type cur_type is REF CURSOR;
473 c cur_type;
474
475 l_val_count Number;
476 l_value VARCHAR2(100);
477 l_total_count Number := 1;
478 l_query Varchar2(2000);
479
480 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Match_Criteria';
481 l_api_version_number CONSTANT NUMBER := 1.0;
482
483 Begin
484
485 -- Standard call to check for call compatibility.
486
487 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
488 p_api_version_number,
489 l_api_name,
490 G_PKG_NAME) THEN
491
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493
494 END IF;
495
496 -- Initialize message list if p_init_msg_list is set to TRUE.
497 IF FND_API.to_Boolean( p_init_msg_list )
498 THEN
499 fnd_msg_pub.initialize;
500 END IF;
501
502 x_matched_attr_val := JTF_VARCHAR2_TABLE_100();
503 x_matched_attr := JTF_VARCHAR2_TABLE_100();
504 x_original_attr := JTF_VARCHAR2_TABLE_100();
505 x_original_attr_val := JTF_VARCHAR2_TABLE_100();
506 l_attr_val_cnt := JTF_VARCHAR2_TABLE_100();
507 x_matched_id := JTF_VARCHAR2_TABLE_100();
508
509 x_return_status := FND_API.G_RET_STS_SUCCESS ;
510
511 for attr_rec IN attr_cur loop
512
513 l_val_count := 0;
514
515 l_query := replace(attr_rec.sql_text, ' ', ' ');
516
517 if l_query is null then
518
519 fnd_message.SET_NAME ('PV', 'PV_SQLTEXT_NULL');
520 fnd_message.SET_TOKEN ('P_ENTITY' , 'LEAD');
521 fnd_message.SET_TOKEN ('P_ATTRIBUTE' , attr_rec.short_name);
522 fnd_msg_pub.ADD;
523
524 raise FND_API.G_EXC_ERROR;
525
526 end if;
527
528 if(attr_rec.src_pkcol_name = 'LEAD_ID' ) then
529
530 open c for l_query using p_lead_id;
531
532 elsif (attr_rec.src_pkcol_name = 'ATTRIBUTE_ID' ) then
533
534 open c for l_query using attr_rec.attribute_id;
535 else
536 open c for l_query;
537 end if;
538
539 for i in 1..ceil((length(l_query)/100)) loop
540 null;
541 end loop;
542
543 loop
544
545 fetch c into l_value ;
546 exit when c%notfound;
547
548 if (l_value is not null) then
549
550 l_val_count := l_val_count + 1;
551
552 x_matched_attr.extend;
553 x_original_attr.extend;
554 x_matched_attr(x_matched_attr.count) := attr_rec.short_name;
555 x_original_attr(x_matched_attr.count) := attr_rec.short_name;
556
557 x_matched_attr_val.extend;
558 x_original_attr_val.extend;
559
560 x_matched_attr_val(l_total_count) := l_value;
561 x_original_attr_val(l_total_count) := l_value;
562
563 l_total_count := l_total_count + 1;
564 end if;
565 end loop;
566 close c;
567
568 if (l_val_count <> 0 ) then
569 l_attr_val_cnt.extend;
570 l_attr_val_cnt(l_attr_val_cnt.count) := l_val_count;
571 end if;
572
573 end loop;
574
575 Form_Where_clause(
576 p_api_version_number => l_api_version_number
577 ,p_init_msg_list => p_init_msg_list
578 ,p_commit => p_commit
579 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
580 ,p_attr_tbl => x_matched_attr
581 ,p_attr_val_count => l_attr_val_cnt
582 ,p_val_attr_tbl => x_matched_attr_val
583 ,p_cm_id => 0
584 ,p_lead_id => p_lead_id
585 ,p_auto_match_flag => 'Y'
586 ,x_iterations => x_iterations
587 ,x_matched_id_tbl => x_matched_id
588 ,x_return_status => x_return_status
589 ,x_msg_count => x_msg_count
590 ,x_msg_data => x_msg_data);
591
592 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
593 RAISE fnd_api.g_exc_error;
594 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
595 RAISE fnd_api.g_exc_unexpected_error;
596 END IF;
597
598 IF FND_API.To_Boolean ( p_commit ) THEN
599 COMMIT WORK;
600 END IF;
601
602 -- Standard call to get message count and if count is 1, get message info.
603 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
604 p_count => x_msg_count,
605 p_data => x_msg_data);
606 EXCEPTION
607
608 WHEN FND_API.G_EXC_ERROR THEN
609
610 x_return_status := FND_API.G_RET_STS_ERROR ;
611
612 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
613 p_count => x_msg_count,
614 p_data => x_msg_data);
615
616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
617
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
619
620 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
621 p_count => x_msg_count,
622 p_data => x_msg_data);
623
624 WHEN OTHERS THEN
625
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627
628 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
629
630 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
631 p_count => x_msg_count,
632 p_data => x_msg_data);
633
634 End Auto_Match_Criteria;
635
636 end PV_MATCH_PARTNER;