[Home] [Help]
PACKAGE BODY: APPS.PV_MATCH_V2_PUB
Source
1 package body PV_MATCH_V2_PUB as
2 /* $Header: pvxmtchb.pls 120.5 2005/12/15 14:25:34 amaram ship $*/
3
4 -- --------------------------------------------------------------
5 -- Used for inserting output messages to the message table.
6 -- --------------------------------------------------------------
7 PROCEDURE Debug(
8 p_msg_string IN VARCHAR2
9 );
10
11 PROCEDURE Set_Message(
12 p_msg_level IN NUMBER,
13 p_msg_name IN VARCHAR2,
14 p_token1 IN VARCHAR2,
15 p_token1_value IN VARCHAR2,
16 p_token2 IN VARCHAR2 := NULL,
17 p_token2_value IN VARCHAR2 := NULL,
18 p_token3 IN VARCHAR2 := NULL,
19 p_token3_value IN VARCHAR2 := NULL
20 );
21
22 -- %%%%%%%%%%%%%%%%%%%%%% Private Routines %%%%%%%%%%%%%%%%%%%%%%%
23 -- =================================================================
24 -- get_no_of_delimiter will return the no of delimiters in a given
25 -- string.
26 -- When p_attr_value is "abc+++def+++ghi" and the delimiter is
27 -- "+++" then the output from this function would be 2
28 -- which means there are two delimiters in this function
29 -- =================================================================
30
31 FUNCTION get_no_of_delimiter
32 (
33 p_attr_value IN VARCHAR2,
34 p_delimiter IN VARCHAR2
35 )
36 RETURN NUMBER;
37
38 PROCEDURE tokenize
39 (
40 p_attr_value IN VARCHAR2,
41 p_delimiter IN VARCHAR2,
42 p_attr_value_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_4000
43 );
44
45 -- %%%%%%%%%%%%%%%%%%%%%% End of Private Routines %%%%%%%%%%%%%%%%%%%%%%%
46
47 Procedure Manual_match(
48 p_api_version_number IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
50 p_commit IN VARCHAR2 := FND_API.G_FALSE,
51 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
52 p_attr_id_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
53 p_attr_value_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
54 p_attr_operator_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
55 p_attr_data_type_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
56 p_attr_selection_mode IN VARCHAR2,
57 p_att_delmter IN VARCHAR2,
58 p_selection_criteria IN VARCHAR2,
59 p_resource_id IN NUMBER,
60 p_lead_id IN NUMBER,
61 p_auto_match_flag IN VARCHAR2,
62 p_get_distance_flag IN VARCHAR2 := 'F',
63 x_matched_id OUT NOCOPY JTF_NUMBER_TABLE,
64 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
65 x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
66 x_distance_uom_returned OUT NOCOPY VARCHAR2,
67 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2,
71 p_top_n_rows_by_profile IN VARCHAR2 := 'T'
72 ) IS
73
74 l_api_name CONSTANT VARCHAR2(30) := 'Manual_Match';
75 l_api_version_number CONSTANT NUMBER := 1.0;
76
77
78 cursor lc_get_incumbent_pt (pc_lead_id number) is
79 select asla.INCUMBENT_PARTNER_PARTY_ID
80 from as_leads_all asla
81 where asla.lead_id = pc_lead_id;
82
83
84 l_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
85 l_new_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
86
87 l_incumbent_pt_party_id NUMBER;
88 l_matched_tbl_last_idx NUMBER := 0;
89 l_incumbent_idx NUMBER := 0;
90
91 l_prefered_partner_distance NUMBER;
92
93 l_distance_uom VARCHAR2(100);
94 l_customer_address pv_locator.party_address_rec_type;
95 --x_distance_uom_returned VARCHAR2(30);
96
97 l_no_of_prefered_pts NUMBER := 0;
98 l_prefered_pt_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
99 l_prefered_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
100 l_prefered_dist_uom VARCHAR2(200);
101 l_partner_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
102 l_tokenize_attr_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
103
104 l_locator_flag VARCHAR2(1) := 'Y';
105 BEGIN
106
107 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
108 debug('In '||l_api_name);
109 END IF;
110
111 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
112 p_api_version_number,
113 l_api_name,
114 G_PKG_NAME) THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116
117 END IF;
118 x_matched_id := JTF_NUMBER_TABLE();
119 x_partner_details := JTF_VARCHAR2_TABLE_4000();
120 x_distance_tbl := JTF_NUMBER_TABLE();
121 x_flagcount := JTF_VARCHAR2_TABLE_100();
122
123 -- Initialize message list if p_init_msg_list is set to TRUE.
124 IF FND_API.to_Boolean( p_init_msg_list ) THEN
125 fnd_msg_pub.initialize;
126 END IF;
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS ;
129
130 -- ================================================================================
131 -- Get matched Partner ID's for the specified attributes
132 -- ================================================================================
133
134 Form_Where_clause(
135 p_api_version_number => l_api_version_number
136 ,p_init_msg_list => p_init_msg_list
137 ,p_commit => p_commit
138 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
139 ,p_attr_id_tbl => p_attr_id_tbl
140 ,p_attr_value_tbl => p_attr_value_tbl
141 ,p_attr_operator_tbl => p_attr_operator_tbl
142 ,p_attr_data_type_tbl => p_attr_data_type_tbl
143 ,p_attr_selection_mode => p_attr_selection_mode
144 ,p_att_delmter => p_att_delmter
145 ,p_selection_criteria => p_selection_criteria
146 ,p_resource_id => p_resource_id
147 ,p_lead_id => p_lead_id
148 ,p_auto_match_flag => p_auto_match_flag
149 ,p_top_n_rows_by_profile => p_top_n_rows_by_profile
150 ,x_matched_id => x_matched_id
151 ,x_return_status => x_return_status
152 ,x_msg_count => x_msg_count
153 ,x_msg_data => x_msg_data);
154
155
156 -- ================================================================================
157 -- Get Preferred Partner Details
158 -- ================================================================================
159
160 open lc_get_incumbent_pt (p_lead_id);
161 fetch lc_get_incumbent_pt into l_incumbent_pt_party_id;
162 close lc_get_incumbent_pt;
163
164 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
165 debug('Preferred partner for lead :'||p_lead_id||' is '||l_incumbent_pt_party_id);
166 END IF;
167
168 -- Checking to see if the preferred partner already exists in the matched partner tbl
169
170 IF (x_matched_id.EXISTS(1) AND l_incumbent_pt_party_id IS NOT NULL) THEN
171 FOR x IN (
172 SELECT idx
173 FROM (SELECT rownum idx, column_value party_id
174 FROM (SELECT column_value
175 FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
176 WHERE a.party_id = l_incumbent_pt_party_id)
177 LOOP
178 l_incumbent_idx := x.idx;
179
180 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
181 debug('Preferred partner already exists at matched partner list, position is '||l_incumbent_idx);
182 END IF;
183
184
185 END LOOP;
186 END IF;
187
188 -- Adding preferred partner to matched partner id tbl
189 IF l_incumbent_idx = 0 and l_incumbent_pt_party_id IS NOT NULL THEN
190
191 x_matched_id.extend;
192 x_matched_id(x_matched_id.count) := l_incumbent_pt_party_id;
193
194 END IF;
195
196 l_matched_id := x_matched_id;
197
198 -- ------------------------------------------------------------------------
199 -- Retrieve customer-to-partner distance info...
200 --
201 -- Execute Geo Proximity API only when there is at least one partner
202 -- returned from Partner Matching above.
203 -- ------------------------------------------------------------------------
204 IF (p_get_distance_flag = 'T' AND
205 l_matched_id.EXISTS(1) AND l_matched_id.COUNT > 0)
206 THEN
207 -- -------------------------------------------------------------
208 -- Retrieve location_id for this opportunity.
209 -- -------------------------------------------------------------
210 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
211 Debug('before retrieving locator info');
212 END IF;
213 BEGIN
214 SELECT b.location_id
215 INTO l_customer_address.location_id
216 FROM as_leads_all a,
217 hz_party_sites b,
218 hz_locations l
219 WHERE a.lead_id = p_lead_id AND
220 a.customer_id = b.party_id AND
221 b.party_site_id = a.address_id AND
222 b.location_id = l.location_id AND
223 l.geometry IS NOT NULL;
224
225
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229
230 l_locator_flag := 'N';
231
232 END;
233
234
235 IF l_locator_flag = 'Y' THEN
236 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
237 Debug('Location ID: ' || l_customer_address.location_id);
238
239 Debug('..........................................................');
240 Debug('Calling pv_locator.Get_Partners..........................');
241 Debug('# of Partners Sent: ' || l_matched_id.COUNT);
242
243 for i in 1 .. x_matched_id.count
244 loop
245 Debug('Partner Id ' || l_matched_id(i));
246 end loop;
247 END IF;
248
249 -- -------------------------------------------------------------
250 -- Execute geo proximity API.
251 -- -------------------------------------------------------------
252
253 -- Default to mile?
254 l_distance_uom := pv_locator.g_distance_unit_mile;
255 pv_locator.Get_Partners (
256 p_api_version => 1.0,
257 p_init_msg_list => FND_API.g_false,
258 p_commit => FND_API.g_false,
259 p_validation_level => FND_API.g_valid_level_full,
260 p_customer_address => l_customer_address,
261 p_partner_tbl => l_matched_id,
262 p_max_no_partners => null,
263 p_distance => null,
264 p_distance_unit => l_distance_uom,
265 p_sort_by_distance => 'T',
266 x_partner_tbl => x_matched_id,
267 x_distance_tbl => x_distance_tbl,
268 x_distance_unit => x_distance_uom_returned,
269 x_return_status => x_return_status,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data
272 );
273
274 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
275 RAISE FND_API.G_EXC_ERROR;
276
277 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
278 RAISE FND_API.g_exc_unexpected_error;
279 END IF;
280
281 IF (x_distance_uom_returned = pv_locator.g_distance_unit_km) THEN
282 x_distance_uom_returned := 'KILOMETERS';
283
284 ELSIF (x_distance_uom_returned = pv_locator.g_distance_unit_mile) THEN
285 x_distance_uom_returned := 'MILES';
286 END IF;
287
288 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
289 Debug('Distance UOM returned is: ' || x_distance_uom_returned);
290 Debug('# of Partners Returned: ' || x_matched_id.COUNT);
291 END IF;
292
293
294
295
296 -- ------------------------------------------------------------------------
297 -- Adding preferred partner on top
298 -- ------------------------------------------------------------------------
299
300 IF l_incumbent_pt_party_id IS NOT NULL THEN
301
302 FOR x IN (
303 SELECT idx
304 FROM (SELECT rownum idx, column_value party_id
305 FROM (SELECT column_value
306 FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
307 WHERE a.party_id = l_incumbent_pt_party_id)
308 LOOP
309 l_incumbent_idx := x.idx;
310 END LOOP;
311
312 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
313 debug('location of Preferred partner in matched partner list '||l_incumbent_idx);
314 END IF;
315
316 l_prefered_partner_distance := x_distance_tbl(l_incumbent_idx);
317
318 IF (x_matched_id.COUNT > 1) THEN
319 FOR i IN REVERSE 1..(l_incumbent_idx - 1) LOOP
320 x_matched_id(i + 1) := x_matched_id(i);
321 x_distance_tbl(i+1) := x_distance_tbl(i);
322
323 END LOOP;
324
325 x_matched_id(1) := l_incumbent_pt_party_id;
326 x_distance_tbl(1) := l_prefered_partner_distance;
327 END IF;
328
329
330 END IF;
331
332 -- ------------------------------------------------------------------------
333 -- Getting the partner details
334 -- ------------------------------------------------------------------------
335
336 for i in 1 .. x_matched_id.count loop
337
338 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
339 debug('Partner ID after prefered partner on top '||x_matched_id(i));
340 debug('Distance after prefered partner on top '||x_distance_tbl(i));
341 END IF;
342 end loop;
343 END IF;
344
345 END IF;
346 IF x_matched_id.count > 0 THEN
347
348 g_from_match_lov_flag := TRUE;
349
350 Get_Matched_Partner_Details(
351 p_api_version_number => 1.0
352 ,p_init_msg_list => FND_API.G_FALSE
353 ,p_commit => FND_API.G_FALSE
354 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
355 ,p_lead_id => p_lead_id
356 ,p_extra_partner_details => null
357 ,p_matched_id => x_matched_id
358 ,x_partner_details => x_partner_details
359 ,x_flagcount => x_flagcount
360 ,x_return_status => x_return_status
361 ,x_msg_count => x_msg_count
362 ,x_msg_data => x_msg_data);
363
364 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
365 RAISE fnd_api.g_exc_error;
366 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
367 RAISE fnd_api.g_exc_unexpected_error;
368 END IF;
369
370 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
371 Debug('# of Partners Returned from matched_partner_details: ' || x_matched_id.COUNT);
372 END IF;
373
374 END IF;
375
376
377
378 IF FND_API.To_Boolean ( p_commit ) THEN
379 COMMIT WORK;
380 END IF;
381
382 -- Standard call to get message count and if count is 1, get message info.
383 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
384 p_count => x_msg_count,
385 p_data => x_msg_data);
386
387 EXCEPTION
388
389 WHEN FND_API.G_EXC_ERROR THEN
390
391 x_return_status := FND_API.G_RET_STS_ERROR ;
392
393 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
394 p_count => x_msg_count,
395 p_data => x_msg_data);
396
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400
401 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
402 p_count => x_msg_count,
403 p_data => x_msg_data);
404
405
406 WHEN OTHERS THEN
407
408 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
409
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411
412
413 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
414 p_count => x_msg_count,
415 p_data => x_msg_data);
416
417 END Manual_Match;
418
419 --=============================================================================+
420 --| Procedure |
421 --| |
422 --| Form_WHere_clause |
423 --| This procedure Takes attributes and their values and forms where |
424 --| condition to search for partners. It keeps on dropping attributes |
425 --| in where condition until a partner is found or they get exhausted |
426 --| |
427 --| Parameters |
428 --| IN |
429 --| OUT |
430 --| |
431 --| |
432 --| NOTES |
433 --| |
434 --| HISTORY |
435 --| |
436 --==============================================================================
437
438 procedure Form_Where_Clause(
439 p_api_version_number IN NUMBER,
440 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
441 p_commit IN VARCHAR2 := FND_API.G_FALSE,
442 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
443 p_attr_id_tbl IN OUT NOCOPY JTF_NUMBER_TABLE,
444 p_attr_value_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
445 p_attr_operator_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
446 p_attr_data_type_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
447 p_attr_selection_mode IN VARCHAR2,
448 p_att_delmter IN VARCHAR2,
449 p_selection_criteria IN VARCHAR2,
450 p_resource_id IN NUMBER,
451 p_lead_id IN NUMBER,
452 p_auto_match_flag IN VARCHAR2,
453 x_matched_id OUT NOCOPY JTF_NUMBER_TABLE,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2,
457 p_top_n_rows_by_profile IN VARCHAR2 := 'T')IS
458
459 Type l_tmp is Table of Varchar2(4000) index by binary_integer;
460
461 l_tmp_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
462 l_tmp_tbl1 l_tmp;
463 l_attr_val_cnt_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
464 l_where Varchar2(32000);
465 l_value_count Number := 0;
466 l_tmp_where Varchar2(32000);
467 attr_seq NUMBER := 1;
468 l_attr_val_count NUmber;
469 l_attr VARCHAR2(100);
470 l_attr_value VARCHAR2(20000);
471 l_prt_matched boolean := true;
472 l_matched_attr_cnt number;
473 l_rank_base_2 number := 1;
474 tbl_cnt Number;
475 cnt Number;
476 l_category Varchar2(30);
477 l_source_id Number;
478 isCm boolean := false;
479 isVad boolean := false;
480 isAm boolean := false;
481 l_cm_tmp varchar2(1);
482 l_delm_cnt Number := 0;
483 l_delm_and_cnt Number := 0;
484 l_delm_or_cnt Number := 0;
485 l_attr_val_len Number := 0;
486 l_comma_cnt Number := 1;
487 l_attr_operator varchar2(100);
488 l_delm_betwn_cnt Number := 0;
489 l_delm_length number := 0;
490 l_num_bet_point number := 0;
491 l_between_bef_val varchar2(10000);
492 l_between_aft_val varchar2(10000);
493 l_bet_bef_curr_val varchar2(10000);
494 l_bet_aft_curr_val varchar2(10000);
495 l_base_currency varchar2(10000);
496 isDate boolean := false;
497 l_date_num Number := 0;
498 j Number := 0;
499 l_attr_curr_value varchar2(10000);
500
501 cursor lc_get_resource_details (pc_resource_id number) is
502 select jtfre.category, jtfre.source_id
503 from jtf_rs_resource_extns jtfre
504 where jtfre.resource_id = pc_resource_id;
505
506 cursor lc_is_cm (pc_resource_id number, pc_lead_id number) is
507 select 'X'
508 from pv_party_notifications pvpn, pv_lead_assignments pvla , pv_lead_workflows pvlw
509 where pvlw.lead_id = pc_lead_id
510 and pvlw.entity = 'OPPORTUNITY'
511 and pvlw.LATEST_ROUTING_FLAG = 'Y'
512 and pvlw.routing_status = 'MATCHED'
513 and pvlw.wf_item_key = pvla.WF_ITEM_KEY
514 and pvlw.wf_item_type = pvla.wf_item_type
515 and pvla.lead_assignment_id = pvpn.lead_assignment_id
516 and pvpn.resource_id = pc_resource_id
517 and pvpn.notification_type = 'MATCHED_TO';
518
519
520 l_api_name CONSTANT VARCHAR2(30) := 'Form_Where_Clause';
521 l_api_version_number CONSTANT NUMBER := 1.0;
522
523 -- pklin
524 l_distance_uom VARCHAR2(30);
525 l_customer_address pv_locator.party_address_rec_type;
526 --x_distance_uom_returned VARCHAR2(30);
527
528 l_matched_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
529 l_no_of_prefered_pts NUMBER := 0;
530 l_prefered_pt_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
531 l_prefered_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
532 l_prefered_dist_uom VARCHAR2(200);
533 l_partner_dist_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
534 l_tokenize_attr_tbl JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000();
535 l_opr_meaning VARCHAR2(30);
536
537 -- --------------------------------------------------------------------------
538 -- l_bind_var_tbl is used for keeping track of all bind variables as we go
539 -- along in building the dynamic SQL for retrieving partners.
540 -- --------------------------------------------------------------------------
541 l_bind_var_tbl bind_var_tbl;
542 l_bind_count INTEGER;
543 l_bind_var varchar2(2000);
544
545 begin
546
547 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
548 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
549 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
550 fnd_msg_pub.Add;
551 END IF;
552
553 -- Standard call to check for call compatibility.
554
555 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
556 p_api_version_number,
557 l_api_name,
558 G_PKG_NAME) THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560
561 END IF;
562
563 -- Initialize message list if p_init_msg_list is set to TRUE.
564 IF FND_API.to_Boolean( p_init_msg_list ) THEN
565 fnd_msg_pub.initialize;
566 END IF;
567
568 x_return_status := FND_API.G_RET_STS_SUCCESS ;
569 x_matched_id := JTF_NUMBER_TABLE();
570
571 if p_attr_id_tbl.count() = 0
572 or p_attr_value_tbl.count() = 0
573 or p_attr_operator_tbl.count() = 0
574 or p_attr_data_type_tbl.count() = 0
575 then
576
577 fnd_message.SET_NAME ('PV', 'PV_MISSING_SEARCH_CRITERIA');
578 fnd_msg_pub.ADD;
579 raise FND_API.G_EXC_ERROR;
580
581 end if;
582
583 IF p_lead_id is null and p_resource_id is null THEN
584 isAm := true;
585 ELSE
586
587 /** Get Resource Details for resource id being passed in to the api **/
588
589 open lc_get_resource_details (pc_resource_id => p_resource_id);
590 fetch lc_get_resource_details into l_category, l_source_id;
591 close lc_get_resource_details;
592
593 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
594 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
595 fnd_message.Set_Token('TEXT', 'Resource Category: ' || nvl(l_category, 'NULL') || ' Source Id: ' ||
596 nvl(to_char(l_source_id), 'NULL') || ' for resource_id: ' || p_resource_id);
597 fnd_msg_pub.Add;
598 END IF;
599
600 /** If category of resource is PARTY.. skip this.
601 ** If EMPLOYEE, validate if resoruce is working as a CM for this lead id
602 **/
603 if l_category is NULL then
604
605 fnd_message.SET_NAME ('PV', 'PV_RESOURCE_NOT_FOUND');
606 fnd_message.SET_TOKEN ('P_RESOURCE_ID', p_resource_id);
607 fnd_msg_pub.ADD;
608
609 raise FND_API.G_EXC_ERROR;
610
611 elsif l_category = 'EMPLOYEE' then
612 open lc_is_cm (pc_resource_id => p_resource_id, pc_lead_id => p_lead_id);
613 fetch lc_is_cm into l_cm_tmp;
614 close lc_is_cm;
615
616 if l_cm_tmp='X' then
617 isCm := true;
618 else
619 isAm := true;
620 end if;
621
622
623 elsif l_category = 'PARTY' then
624 isVad := true;
625 end if;
626 END IF;
627
628 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
629 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
630 if isCm then
631 fnd_message.Set_Token('TEXT', 'User Is CM');
632 elsif isAm then
633 fnd_message.Set_Token('TEXT', 'User Is AM');
634 elsif isVad then
635 fnd_message.Set_Token('TEXT', 'User Is Vad');
636 end if;
637 fnd_msg_pub.Add;
638 END IF;
639
640 -- Standard call to check for call compatibility.
641 -- Form the select statement to search for partners based on received where Condition
642
643 -- The search criteria to pick the partners is based on the following assumptions
644
645 -- If the attribute is of type date the attribute length should be 16
646 -- The attribute will of format YYYYMMDDHH24MISS
647
648
649 IF p_attr_selection_mode = g_and_attr_select
650 AND p_selection_criteria = g_drop_attr_match
651 THEN
652
653 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
654 fnd_message.Set_Token('TEXT', 'This Attribute Selection Mode :'||g_and_attr_select||'and Selection Criteria :'||g_drop_attr_match||' Combination is not supported');
655 fnd_msg_pub.Add;
656
657 raise FND_API.G_EXC_ERROR;
658 END IF;
659
660 IF p_attr_selection_mode NOT IN (g_and_attr_select, g_or_attr_select) THEN
661
662 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
663 fnd_message.Set_Token('TEXT', 'Attribute Selection Mode is wrong. Please pass in the correct value ');
664 fnd_msg_pub.Add;
665
666 raise FND_API.G_EXC_ERROR;
667
668
669 END IF;
670
671 IF p_selection_criteria NOT IN (g_drop_attr_match, g_nodrop_attr_match) THEN
672
673 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
674 fnd_message.Set_Token('TEXT', 'Selection Criteria is wrong . Please pass in the correct value ');
675 fnd_msg_pub.Add;
676
677 raise FND_API.G_EXC_ERROR;
678
679
680 END IF;
681
682
683
684
685
686 l_value_count := p_attr_id_tbl.count;
687 l_base_currency := nvl(fnd_profile.value('PV_COMMON_CURRENCY'),'USD');
688
689 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
690 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
691 fnd_message.Set_Token('TEXT', 'Base Currency is '||l_base_currency);
692 fnd_msg_pub.Add;
693 END IF;
694
695 -- ======================================================================================
696 -- ======================================================================================
697 -- BEGIN LOOP
698 --
699 -- Loop through the input PL/SQL tables to build the dynamic SQL for retrieving partners.
700 -- ======================================================================================
701 -- ======================================================================================
702 l_bind_count := 0;
703
704 FOR attr_no in 1..l_value_count LOOP
705 IF p_attr_operator_tbl(attr_seq) IN ( g_null_opr
706 , g_not_null_opr
707 , g_not_equals_opr )
708 THEN
709
710 SELECT decode ( p_attr_operator_tbl(attr_seq) , g_null_opr, 'Is Null'
711 , g_not_null_opr, 'Is Not Null'
712 , g_not_equals_opr, 'Not Equals')
713 INTO l_opr_meaning
714 FROM DUAL;
715
716 fnd_message.Set_Name('PV', 'PV_OPERATOR_NOT_SUPPORTED');
717 fnd_message.Set_Token('P_OPERATOR',l_opr_meaning );
718 fnd_msg_pub.Add;
719 raise FND_API.G_EXC_ERROR;
720 END IF;
721
722
723 -- -------------------------------------------------------------------------------
724 -- Individual inner SQL statement. Each name-value pair constitutues one or more
725 -- inner SQL statements. Attributes that have multiple values (e.g. country =
726 -- 'US', 'GB') will have multiple inner SQL statements, one for each OR condition.
727 -- -------------------------------------------------------------------------------
728 l_bind_count := l_bind_count + 1;
729
730 l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
731 'from pv_search_attr_values t ' ||
732 'where t.attribute_id = :bv' || l_bind_count;
733
734 l_bind_var_tbl(l_bind_count) := p_attr_id_tbl(attr_seq);
735
736
737 IF p_attr_data_type_tbl(attr_seq) = g_currency_data_type THEN
738
739 l_attr_curr_value := p_attr_value_tbl(attr_seq);
740
741 l_attr_value := to_char(pv_check_match_pub.Currency_Conversion(
742 p_entity_attr_value => l_attr_curr_value,
743 p_rule_currency_code => l_base_currency));
744
745 ELSE
746
747 l_attr_value := p_attr_value_tbl(attr_seq);
748
749 END IF;
750
751
752 IF l_attr_value IS NULL THEN
753
754 fnd_message.Set_Name('PV', 'PV_BLANK_ATTR_TEXT');
755 fnd_message.Set_Token('P_ATTR_ID',p_attr_id_tbl(attr_seq) );
756 fnd_msg_pub.Add;
757 raise FND_API.G_EXC_ERROR;
758
759 END IF;
760
761
762
763 -- -------------------------------------------------------------------
764 -- If there are more than one OR condition in the attribute value,
765 -- break up the attribute value into individual values.
766 -- -------------------------------------------------------------------
767 l_delm_cnt := get_no_of_delimiter(p_attr_value_tbl(attr_no),p_att_delmter);
768
769 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
770 debug('Delimiter Count '||l_delm_cnt);
771 END IF;
772
773 l_delm_length := length(p_att_delmter);
774
775 IF l_delm_cnt > 0 THEN
776 -- -------------------------------------------------------------------------
777 -- Attr Value will be broken down into individual elements in PL/SQL table
778 -- -------------------------------------------------------------------------
779 tokenize
780 (
781 p_attr_value => l_attr_value,
782 p_delimiter => p_att_delmter,
783 p_attr_value_tbl => l_tokenize_attr_tbl
784 );
785 END IF;
786
787
788 -- -------------------------------------------------------------------
789 -- Forming the SQL Construct for Operators for String Data Type
790 -- -------------------------------------------------------------------
791 IF p_attr_data_type_tbl(attr_seq) = g_string_data_type THEN
792
793 l_tmp_where := l_tmp_where || ' and upper(attr_text) ';
794
795 IF p_attr_operator_tbl(attr_seq) = g_equals_opr THEN
796
797 IF l_delm_cnt = 0
798 OR (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
799 AND p_selection_criteria = g_nodrop_attr_match) THEN
800 l_attr_operator := 'like ';
801 ELSE
802 l_attr_operator := 'in (';
803 END IF;
804
805 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
806 debug('Operator '||l_attr_operator);
807 END IF;
808
809 ELSIF p_attr_operator_tbl(attr_seq) = g_not_equals_opr THEN
810
811 IF l_delm_cnt = 0
812 OR (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
813 AND p_selection_criteria = g_nodrop_attr_match) THEN
814
815 l_attr_operator := 'not like ';
816 ELSE
817 l_attr_operator := 'not in (';
818 END IF;
819
820
821 ELSIF p_attr_operator_tbl(attr_seq) = g_null_opr THEN
822
823 l_attr_operator := 'IS NULL';
824
825 ELSIF p_attr_operator_tbl(attr_seq) = g_not_null_opr THEN
826
827 l_attr_operator := 'IS NOT NULL';
828
829 ELSE
830
831 fnd_message.Set_Name('PV', 'PV_WRONG_OPR_FOR_STR');
832 fnd_message.Set_Token('P_ATTR_OPR', p_attr_operator_tbl(attr_seq));
833 fnd_msg_pub.Add;
834 raise FND_API.G_EXC_ERROR;
835
836 END IF;
837
838 -- -------------------------------------------------------------------
839 -- Forming the SQL Construct for Operators for Number and Date Data Type
840 -- -------------------------------------------------------------------
841
842 ELSE
843
844 IF l_delm_cnt >= 0 THEN
845
846 IF p_attr_operator_tbl(attr_seq) = g_equals_opr THEN
847
848 IF l_delm_cnt = 0 OR
849 (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
850 AND p_selection_criteria = g_nodrop_attr_match) THEN
851 l_attr_operator := ' = ';
852 ELSE
853 l_attr_operator := 'in (';
854 END IF;
855
856
857 ELSIF p_attr_operator_tbl(attr_seq) = g_not_equals_opr THEN
858
859 IF l_delm_cnt = 0 OR
860 (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
861 AND p_selection_criteria = g_nodrop_attr_match) THEN
862 l_attr_operator := ' <> ';
863 ELSE
864 l_attr_operator := 'not in (';
865 END IF;
866
867 END IF;
868
869 END IF;
870
871
872 IF l_delm_cnt = 0
873 OR ( p_selection_criteria = g_nodrop_attr_match AND l_delm_cnt > 0
874 AND p_attr_selection_mode = g_and_attr_select )
875 THEN
876
877 IF p_attr_operator_tbl(attr_seq) = g_greater_opr THEN
878
879 l_attr_operator := ' > ';
880
881 ELSIF p_attr_operator_tbl(attr_seq) = g_less_opr THEN
882
883 l_attr_operator := ' < ';
884
885 ELSIF p_attr_operator_tbl(attr_seq) = g_grt_or_equ_opr THEN
886
887 l_attr_operator := ' >= ';
888
889 ELSIF p_attr_operator_tbl(attr_seq) = g_less_or_equ_opr THEN
890
891 l_attr_operator := ' <= ';
892
893 END IF;
894
895 ELSIF l_delm_cnt > 0 AND p_selection_criteria = g_drop_attr_match
896 AND p_attr_operator_tbl(attr_seq) in ( g_greater_opr,
897 g_less_opr,
898 g_grt_or_equ_opr,
899 g_less_or_equ_opr )
900 THEN
901
902 fnd_message.Set_Name('PV', 'PV_WRONG_OPR_FOR_NUM_DATE');
903 fnd_message.Set_Token('P_ATTR_OPR', p_attr_operator_tbl(attr_seq));
904 fnd_msg_pub.Add;
905
906 raise FND_API.G_EXC_ERROR;
907
908 END IF;
909
910
911
912 IF p_attr_operator_tbl(attr_seq) = g_null_opr THEN
913
914 l_attr_operator := 'IS NULL';
915
916 ELSIF p_attr_operator_tbl(attr_seq) = g_not_null_opr THEN
917
918 l_attr_operator := 'IS NOT NULL';
919
920
921 END IF;
922
923 IF p_attr_data_type_tbl(attr_seq) in (g_number_data_type, g_currency_data_type)
924 THEN
925 l_tmp_where := l_tmp_where || ' and attr_value ' ;
926 END IF;
927
928
929 IF p_attr_data_type_tbl(attr_seq) = g_date_data_type THEN
930 l_tmp_where := l_tmp_where || ' and upper(attr_text) ';
931 l_date_num := to_number(l_attr_value);
932
933 IF NOT to_number(l_attr_value) = l_date_num THEN
934 fnd_message.Set_Name('PV', 'PV_NOT_DATE_FORMAT');
935 fnd_msg_pub.Add;
936 raise FND_API.G_EXC_ERROR;
937 END IF;
938
939
940 IF l_delm_cnt = 0 THEN
941 IF length(l_attr_value) > 16 THEN
942 fnd_message.Set_Name('PV', 'PV_NOT_DATE_VALUE');
943 fnd_msg_pub.Add;
944 raise FND_API.G_EXC_ERROR;
945 END IF;
946
947 ELSE
948 for i in 1 .. l_tokenize_attr_tbl.count
949 loop
950
951 IF length(l_tokenize_attr_tbl(i)) > 16
952 THEN
953 fnd_message.Set_Name('PV', 'PV_NOT_DATE_VALUE');
954 fnd_msg_pub.Add;
955 raise FND_API.G_EXC_ERROR;
956 END IF;
957 end loop;
958 END IF;
959
960 END IF;
961
962 END IF;
963
964
965 l_attr_val_count := p_attr_id_tbl.count;
966
967 -- =================================================================================
968 -- =================================================================================
969 -- Building WHERE Clause
970 -- =================================================================================
971 -- =================================================================================
972
973 -- ---------------------------------------------------------------------------------
974 -- Forming the Where Clause for BETWEEN Operator
975 -- ---------------------------------------------------------------------------------
976
977 IF (p_attr_operator_tbl(attr_seq) = g_between_opr) THEN
978 Debug('##########################################################################');
979 Debug('# Build where clause for BETWEEN operators');
980 Debug('##########################################################################');
981 l_attr_val_cnt_tbl.extend;
982 l_attr_val_cnt_tbl(attr_no) := 1;
983
984 l_rank_base_2 := l_rank_base_2 * 2;
985
986
987 IF l_tokenize_attr_tbl.count > 2 THEN
988
989 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
990 fnd_message.Set_Token('TEXT', 'Attr Value of this format is not supported for BETWEEN Operator '||l_attr_value);
991 fnd_msg_pub.Add;
992
993 raise FND_API.G_EXC_ERROR;
994 END IF;
995
996
997
998 -- ----------------------------------------------------------------------------------
999 -- Populate l_between_bef_val and l_between_aft_val
1000 -- ----------------------------------------------------------------------------------
1001 for i in 1 .. l_tokenize_attr_tbl.count
1002 loop
1003
1004 IF p_attr_data_type_tbl(attr_seq) = g_currency_data_type THEN
1005 l_between_bef_val := to_char(pv_check_match_pub.Currency_Conversion(
1006 p_entity_attr_value => l_tokenize_attr_tbl(i),
1007 p_rule_currency_code => l_base_currency));
1008
1009 l_between_aft_val := to_char(pv_check_match_pub.Currency_Conversion(
1010 p_entity_attr_value => l_tokenize_attr_tbl(i+1),
1011 p_rule_currency_code => l_base_currency));
1012 ELSIF p_attr_data_type_tbl(attr_seq) = g_string_data_type OR p_attr_data_type_tbl(attr_seq) = g_date_data_type THEN
1013
1014 l_between_bef_val := upper(l_tokenize_attr_tbl(i));
1015 l_between_aft_val := upper(l_tokenize_attr_tbl(i+1));
1016
1017 ELSE
1018
1019 l_between_bef_val := l_tokenize_attr_tbl(i);
1020 l_between_aft_val := l_tokenize_attr_tbl(i+1);
1021
1022 END IF;
1023
1024 EXIT WHEN i+1 = 2;
1025
1026 end loop;
1027
1028
1029 -- ----------------------------------------------------------------------------------
1030 -- Insert bind variables for between operators.
1031 -- ----------------------------------------------------------------------------------
1032 IF p_attr_data_type_tbl(attr_seq) = g_string_data_type THEN
1033 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1034 fnd_message.Set_Token('TEXT', 'BETWEEN operator is not supported for STRING data type');
1035 fnd_msg_pub.Add;
1036
1037 raise FND_API.G_EXC_ERROR;
1038
1039 ELSE
1040 l_bind_count := l_bind_count + 1;
1041 l_bind_var_tbl(l_bind_count) := l_between_bef_val;
1042
1043 l_tmp_where := l_tmp_where || ' BETWEEN :bv' || TO_CHAR(l_bind_count) ||
1044 ' AND :bv' || TO_CHAR(l_bind_count + 1);
1045
1046 -- ----------------------------------------------------------------------------
1047 -- Need to increment l_bind_count here because BETWEEN takes 2 bind variables.
1048 -- ----------------------------------------------------------------------------
1049 l_bind_count := l_bind_count + 1;
1050 l_bind_var_tbl(l_bind_count) := l_between_aft_val;
1051 END IF;
1052
1053 -- -------------------------------------------------------------------
1054 -- Forming the Where Clause where there is no delimiter in Attr Value
1055 -- -------------------------------------------------------------------
1056
1057
1058 ELSIF l_delm_cnt = 0 then
1059 Debug('##########################################################################');
1060 Debug('# Build where clause for attributes with single value');
1061 Debug('##########################################################################');
1062
1063 l_attr_val_cnt_tbl.extend;
1064 l_attr_val_cnt_tbl(l_attr_val_cnt_tbl.count) := 1;
1065
1066 IF p_selection_criteria = g_drop_attr_match THEN
1067
1068 l_rank_base_2 := l_rank_base_2 * 2;
1069
1070 END IF;
1071
1072 IF (p_attr_operator_tbl(attr_seq) = g_not_null_opr OR
1073 p_attr_operator_tbl(attr_seq) = g_null_opr)
1074 THEN
1075 l_tmp_where := l_tmp_where || l_attr_operator;
1076
1077 ELSE
1078 l_bind_count := l_bind_count + 1;
1079 l_bind_var_tbl(l_bind_count) := UPPER(l_attr_value);
1080
1081 l_tmp_where := l_tmp_where || l_attr_operator || ' :bv' || l_bind_count || ' ';
1082
1083 END IF;
1084
1085 -- -------------------------------------------------------------------
1086 -- Forming the Where Clause where there is delimiter in Attr Value
1087 -- and for ANY CONDITION ( it can be DROPPING of attributes as well
1088 -- as for NO Drop )
1089 -- -------------------------------------------------------------------
1090
1091
1092 ELSIF l_delm_cnt > 0 AND p_attr_selection_mode = g_or_attr_select THEN
1093 Debug('##########################################################################');
1094 Debug('# Build where clause for multiple values and p_attr_selection_mode = OR');
1095 Debug('##########################################################################');
1096
1097 IF p_selection_criteria = g_drop_attr_match THEN
1098 Debug('##########################################################################');
1099 Debug('# for p_selection_criteria = g_drop_attr_match');
1100 Debug('##########################################################################');
1101
1102 l_rank_base_2 := l_rank_base_2 * 2;
1103
1104 END IF;
1105
1106 l_attr_val_len := length(l_attr_value);
1107 l_tmp_where := l_tmp_where || l_attr_operator;
1108
1109 l_delm_or_cnt := l_delm_cnt;
1110
1111
1112 FOR k in 1..l_tokenize_attr_tbl.count LOOP
1113 IF l_delm_or_cnt > 0 THEN
1114 l_bind_count := l_bind_count + 1;
1115 l_tmp_where := l_tmp_where || ':bv' || l_bind_count || ',';
1116 l_bind_var_tbl(l_bind_count) := UPPER(l_tokenize_attr_tbl(k));
1117
1118 l_delm_or_cnt := l_delm_or_cnt - 1;
1119
1120 ELSIF ( l_delm_or_cnt = 0 AND k = l_tokenize_attr_tbl.COUNT ) THEN
1121 l_bind_count := l_bind_count + 1;
1122 l_tmp_where := l_tmp_where || ':bv' || l_bind_count || ')';
1123 l_bind_var_tbl(l_bind_count) := UPPER(l_tokenize_attr_tbl(k));
1124
1125 END IF;
1126 END LOOP;
1127
1128 l_attr_val_cnt_tbl.extend;
1129 l_attr_val_cnt_tbl(l_attr_val_cnt_tbl.count) := 1;
1130
1131 -- -------------------------------------------------------------------
1132 -- Forming the Where Clause where there is delimiter in Attr Value
1133 -- and for ALL and OR Condition. Not supported for ALL and AND condition
1134 -- -------------------------------------------------------------------
1135
1136 ELSIF l_delm_cnt > 0 AND p_selection_criteria = g_nodrop_attr_match
1137 AND p_attr_selection_mode = g_and_attr_select THEN
1138 Debug('##########################################################################');
1139 Debug('# Build where clause for multiple values and p_attr_selection_mode = AND');
1140 Debug('##########################################################################');
1141
1142 l_delm_and_cnt := l_delm_cnt;
1143
1144 FOR i IN 1 .. l_tokenize_attr_tbl.count
1145 LOOP
1146
1147 IF p_attr_data_type_tbl(attr_seq) in (g_number_data_type, g_currency_data_type) THEN
1148 -- ------------------------------------------------------------------------------
1149 -- Do not increment l_bind_count in when the operator is BETWEEN because
1150 -- the previous l_tmp_where is overwritten by this select statement here.
1151 -- ------------------------------------------------------------------------------
1152 IF (i > 1) THEN
1153 l_bind_count := l_bind_count + 1;
1154 END IF;
1155
1156 l_bind_var_tbl(l_bind_count) := p_attr_id_tbl(attr_seq);
1157
1158 l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
1159 'from pv_search_attr_values t ' ||
1160 'where t.attribute_id = :bv' || l_bind_count || ' and attr_value ';
1161
1162 ELSE
1163 -- ------------------------------------------------------------------------------
1164 -- Do not increment l_bind_count in when the operator is BETWEEN because
1165 -- the previous l_tmp_where is overwritten by this select statement here.
1166 -- ------------------------------------------------------------------------------
1167 IF (i > 1) THEN
1168 l_bind_count := l_bind_count + 1;
1169 END IF;
1170
1171 l_bind_var_tbl(l_bind_count) := p_attr_id_tbl(attr_seq);
1172
1173 l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
1174 'from pv_search_attr_values t ' ||
1175 'where t.attribute_id = :bv' || l_bind_count || ' and uppeR(attr_text) ';
1176
1177 END IF;
1178
1179 IF l_delm_and_cnt > 0 THEN
1180
1181 IF p_attr_data_type_tbl(attr_seq) in (g_number_data_type, g_currency_data_type) THEN
1182 l_bind_count := l_bind_count + 1;
1183 l_bind_var_tbl(l_bind_count) := l_tokenize_attr_tbl(i);
1184
1185 l_tmp_where := l_tmp_where || l_attr_operator || ' :bv' || l_bind_count;
1186
1187 ELSE
1188 l_bind_count := l_bind_count + 1;
1189 l_bind_var_tbl(l_bind_count) := UPPER(l_tokenize_attr_tbl(i));
1190
1191 l_tmp_where := l_tmp_where || l_attr_operator || ' :bv' || l_bind_count;
1192
1193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1194 debug('Temp Where '||l_tmp_where);
1195 END IF;
1196
1197 END IF;
1198
1199 l_tmp_tbl.extend;
1200 l_tmp_tbl(l_tmp_tbl.count) := l_tmp_where;
1201 l_delm_and_cnt := l_delm_and_cnt - 1;
1202
1203 ELSIF ( l_delm_and_cnt = 0 and i = l_tokenize_attr_tbl.COUNT ) THEN
1204 l_bind_count := l_bind_count + 1;
1205 l_bind_var_tbl(l_bind_count) := UPPER(l_tokenize_attr_tbl(i));
1206
1207 l_tmp_where := l_tmp_where || l_attr_operator || ' :bv' || l_bind_count;
1208
1209 END IF;
1210 END LOOP;
1211
1212 l_attr_val_cnt_tbl.extend;
1213 l_attr_val_cnt_tbl(l_attr_val_cnt_tbl.count) := 1;
1214 END IF;
1215
1216 IF l_value_count > 1 THEN
1217
1218 attr_seq := attr_seq+1;
1219
1220 END IF;
1221
1222 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1223 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1224 fnd_message.Set_Token('TEXT', 'l_tmp_where: ' || l_tmp_where);
1225 fnd_msg_pub.Add;
1226 END IF;
1227
1228
1229 l_tmp_tbl.extend;
1230 l_tmp_tbl(l_tmp_tbl.count) := l_tmp_where;
1231
1232
1233 end loop;
1234
1235 -- ======================================================================================
1236 -- ======================================================================================
1237 -- END LOOP
1238 --
1239 -- End of building the inner portion (based on attribute name-value pair) of the dynamic
1240 -- SQL.
1241 -- ======================================================================================
1242 -- ======================================================================================
1243
1244
1245
1246 -- -------------------------------------------------------------------------------
1247 -- Forming outer SELECT statement with no_merge hint.
1248 -- -------------------------------------------------------------------------------
1249 l_where := 'select /*+ no_merge(t) */ distinct t.party_id, rank from ( select party_id, sum(rank) rank from ( ';
1250
1251 -- -------------------------------------------------------------------------------
1252 -- Concatenating indiviaul SQL's into a big SQL with UNION ALL.
1253 -- -------------------------------------------------------------------------------
1254 for k in 1..l_tmp_tbl.count() loop
1255
1256 l_where := l_where || l_tmp_tbl(k);
1257
1258 if k <> l_tmp_tbl.count() then
1259
1260 l_where := l_where || ' union all ';
1261
1262 end if;
1263
1264 end loop;
1265
1266
1267 -- -------------------------------------------------------------------------------
1268 -- Forming group by statement for the inner SQL.
1269 -- -------------------------------------------------------------------------------
1270 IF p_selection_criteria = g_drop_attr_match THEN
1271
1272 l_where := l_where || ') group by party_id having mod(sum(rank),2) <> 0 ) t, pv_partner_profiles pvpp ';
1273
1274 ELSIF p_selection_criteria = g_nodrop_attr_match THEN
1275
1276 l_where := l_where || ') group by party_id ) t, pv_partner_profiles pvpp ';
1277
1278 END IF;
1279
1280
1281 -- -------------------------------------------------------------------------------
1282 -- Forming predicates for the outer SQL.
1283 --
1284 -- Usage of SALES_PARTNER_FLAG has been obsoleted since 11.5.10
1285 -- -------------------------------------------------------------------------------
1286 if (isAm) then
1287
1288 l_where := l_where || ' , hz_parties PARTNER '
1289 || ' where t.party_id = pvpp.partner_id '
1290 || ' and pvpp.partner_party_id = PARTNER.party_id '
1291 || ' and pvpp.status = ''A'' ';
1292
1293
1294
1295 if p_auto_match_flag = 'Y' then
1296
1297 l_where := l_where || ' and pvpp.auto_match_allowed_flag = ''Y'' ';
1298
1299 end if;
1300
1301 elsif (isCm) then
1302
1303 l_bind_count := l_bind_count + 1;
1304 l_bind_var_tbl(l_bind_count) := p_resource_id;
1305
1306 l_where := l_where || ' , hz_parties partner '
1307 || ' where t.party_id = pvpp.partner_id '
1308 || ' and pvpp.partner_party_id = PARTNER.party_id '
1309 || ' and pvpp.status = ''A'' '
1310 || ' and pvpp.partner_party_id in ( select a.customer_id from as_accesses_all a '
1311 || ' where a.salesforce_id = :bv' || l_bind_count
1312 || ' and a.sales_lead_id is null and a.lead_id is null) ';
1313
1314 elsif (isVad) then
1315
1316 l_bind_count := l_bind_count + 1;
1317 l_bind_var_tbl(l_bind_count) := l_source_id;
1318
1319 l_where := l_where || ' ,hz_relationships INDIRECT_TO_VAD, hz_relationships INDIRECT_TO_VENDOR, '
1320 || ' hz_relationships CONTACT_TO_VAD, hz_organization_profiles HZOP, hz_parties PARTNER '
1321 || ' where CONTACT_TO_VAD.party_id = :bv' || l_bind_count
1322 || ' and CONTACT_TO_VAD.object_id = INDIRECT_TO_VAD.object_id '
1323 || ' and CONTACT_TO_VAD.directional_flag = ''F'' '
1324 || ' and CONTACT_TO_VAD.subject_table_name = ''HZ_PARTIES'' '
1325 || ' and CONTACT_TO_VAD.object_table_name = ''HZ_PARTIES'' '
1326 || ' and CONTACT_TO_VAD.status = ''A'''
1327 || ' and CONTACT_TO_VAD.start_date <= sysdate '
1328 || ' and nvl(CONTACT_TO_VAD.end_date,sysdate) >= sysdate '
1329 || ' and INDIRECT_TO_VAD.relationship_type = ''PARTNER_MANAGED_CUSTOMER'' '
1330 || ' and INDIRECT_TO_VAD.subject_table_name = ''HZ_PARTIES'' '
1331 || ' and INDIRECT_TO_VAD.object_table_name = ''HZ_PARTIES'' '
1332 || ' and INDIRECT_TO_VAD.subject_id = INDIRECT_TO_VENDOR.subject_id '
1333 || ' and INDIRECT_TO_VAD.status = ''A'''
1334 || ' and INDIRECT_TO_VAD.start_date <= sysdate '
1335 || ' and nvl(INDIRECT_TO_VAD.end_date,sysdate) >= sysdate '
1336 || ' and INDIRECT_TO_VENDOR.relationship_type =''PARTNER'' '
1337 || ' and INDIRECT_TO_VENDOR.subject_table_name = ''HZ_PARTIES'' '
1338 || ' and INDIRECT_TO_VENDOR.object_table_name = ''HZ_PARTIES'' '
1339 || ' and INDIRECT_TO_VENDOR.object_id = HZOP.party_id '
1340 || ' and INDIRECT_TO_VENDOR.status = ''A'''
1341 || ' and INDIRECT_TO_VENDOR.start_date <= sysdate '
1342 || ' and nvl(INDIRECT_TO_VENDOR.end_date,sysdate) >= sysdate '
1343 || ' and INDIRECT_TO_VENDOR.subject_id = pvpp.partner_party_id '
1344 || ' and PARTNER.status = ''A'''
1345 || ' and HZOP.internal_flag = ''Y'' '
1346 || ' and HZOP.effective_end_date is null '
1347 || ' and INDIRECT_TO_VENDOR.party_id = pvpp.partner_id '
1348 || ' and pvpp.INDIRECTLY_MANAGED_FLAG = ''Y'' '
1349 || ' and t.party_id = pvpp.partner_id ';
1350
1351 if p_auto_match_flag = 'Y' then
1352 l_where := l_where || ' and pvpp.auto_match_allowed_flag = ''Y'' ';
1353 end if;
1354
1355 end if;
1356
1357 l_where := l_where || ' order by 2 desc ';
1358
1359 if (l_tmp_tbl.count() > 0) then
1360
1361 -- Match partners for this where condition
1362
1363 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1364 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1365 fnd_message.Set_Token('TEXT', 'l_where clause: ');
1366 fnd_msg_pub.Add;
1367 END IF;
1368
1369 for i in 1..ceil((length(l_where)/100)) loop
1370 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1371 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1372 fnd_message.Set_Token('TEXT', substr(l_where, (i-1)*100+1, 100));
1373 fnd_msg_pub.Add;
1374 END IF;
1375 end loop;
1376
1377
1378 -- ----------------------------------------------------------------------------------
1379 -- Print out the list of bind variables.
1380 -- ----------------------------------------------------------------------------------
1381 Debug('***************************************************************************');
1382 Debug('Bind Variables.............................................................');
1383 For j IN 1..l_bind_var_tbl.COUNT LOOP
1384 --since fnd_msg_pub supports debiug message of length 1972
1385 -- we are passing split of attribute value as it may exceed 2000 length
1386
1387 l_bind_var := l_bind_var_tbl(j);
1388 while (l_bind_var is not null) loop
1389 debug('l_bind_var_tbl(' || j || ') = ' ||substr( l_bind_var, 1, 1800 ));
1390 l_bind_var := substr( l_bind_var, 1801 );
1391 end loop;
1392 END LOOP;
1393 Debug('***************************************************************************');
1394
1395
1396
1397 Match_partner(
1398 p_api_version_number => 1.0
1399 ,p_init_msg_list => FND_API.G_FALSE
1400 ,p_commit => FND_API.G_FALSE
1401 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1402 ,p_sql => l_where
1403 ,p_selection_criteria => p_selection_criteria
1404 ,p_num_of_attrs => l_tmp_tbl.count
1405 ,p_bind_var_tbl => l_bind_var_tbl
1406 ,p_top_n_rows_by_profile => p_top_n_rows_by_profile
1407 ,x_matched_prt => x_matched_id
1408 ,x_prt_matched => l_prt_matched
1409 ,x_matched_attr_cnt => l_matched_attr_cnt
1410 ,x_return_status => x_return_status
1411 ,x_msg_count => x_msg_count
1412 ,x_msg_data => x_msg_data);
1413
1414 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1415 RAISE fnd_api.g_exc_error;
1416 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1417 RAISE fnd_api.g_exc_unexpected_error;
1418 END IF;
1419
1420 end if;
1421
1422 -- This section is needed to send matched attributes to Client.
1423
1424
1425 cnt := 0;
1426 for i in l_matched_attr_cnt+1..l_attr_val_cnt_tbl.count
1427 loop
1428 cnt := cnt + l_attr_val_cnt_tbl(i) ;
1429 end loop;
1430
1431
1432 p_attr_id_tbl.trim(cnt);
1433 p_attr_value_tbl.trim(cnt);
1434 p_attr_operator_tbl.trim(cnt);
1435 p_attr_data_type_tbl.trim(cnt);
1436
1437
1438 IF FND_API.To_Boolean ( p_commit ) THEN
1439 COMMIT WORK;
1440 END IF;
1441
1442 -- Standard call to get message count and if count is 1, get message info.
1443 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1444 p_count => x_msg_count,
1445 p_data => x_msg_data);
1446
1447 EXCEPTION
1448
1449 WHEN FND_API.G_EXC_ERROR THEN
1450
1451 x_return_status := FND_API.G_RET_STS_ERROR ;
1452
1453 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1454 p_count => x_msg_count,
1455 p_data => x_msg_data);
1456
1457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1458
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1460
1461 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1462 p_count => x_msg_count,
1463 p_data => x_msg_data);
1464
1465
1466 WHEN OTHERS THEN
1467
1468 IF SQLCODE = -06502 THEN
1469
1470 fnd_message.Set_Name('PV', 'PV_NOT_DATE_FORMAT');
1471 fnd_msg_pub.Add;
1472
1473 ELSE
1474
1475 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1476
1477 END IF;
1478
1479
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481
1482
1483 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1484 p_count => x_msg_count,
1485 p_data => x_msg_data);
1486
1487 END Form_Where_Clause;
1488
1489
1490
1491 -- ----------------------------------------------------------------------------------
1492 -- Procedure Match_Partner
1493 -- ----------------------------------------------------------------------------------
1494 Procedure Match_partner(
1495 p_api_version_number IN NUMBER,
1496 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1497 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1498 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1499 p_sql IN VARCHAR2,
1500 p_selection_criteria IN VARCHAR2,
1501 p_num_of_attrs IN NUMBER,
1502 p_bind_var_tbl IN bind_var_tbl,
1503 p_top_n_rows_by_profile IN VARCHAR2 := 'T',
1504 x_matched_prt OUT NOCOPY JTF_NUMBER_TABLE,
1505 x_prt_matched OUT NOCOPY BOOLEAN,
1506 x_matched_attr_cnt OUT NOCOPY NUMBER,
1507 x_return_status OUT NOCOPY VARCHAR2,
1508 x_msg_count OUT NOCOPY NUMBER,
1509 x_msg_data OUT NOCOPY VARCHAR2
1510 ) IS
1511
1512 l_possible_match_party_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1513 l_possible_match_rank_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1514 l_possible_match_count number := 0;
1515 l_possible_rank_high number := 0;
1516 l_match_count number := 0;
1517 l_top_n_rows number;
1518 l_rank_base_2 number;
1519 l_matching_rank number;
1520 l_combined_rank number;
1521 l_tmp_true_rank number;
1522 l_tmp_matching_rank number;
1523 l_tmp_combined_rank number;
1524 l_attr_count number;
1525 partner_id number;
1526 l_all_ranks varchar2(1000);
1527
1528 l_api_name CONSTANT VARCHAR2(30) := 'Match_partner';
1529 l_api_version_number CONSTANT NUMBER := 1.0;
1530
1531 -- ------------------------------------------------------------------------------
1532 -- Variables for processing dynamic SQL.
1533 -- ------------------------------------------------------------------------------
1534 l_theCursor INTEGER;
1535 l_column_party_id NUMBER DEFAULT NULL;
1536 l_column_rank NUMBER DEFAULT NULL;
1537 l_status INTEGER;
1538
1539
1540 begin
1541 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1542 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1543 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
1544 fnd_msg_pub.Add;
1545 END IF;
1546
1547 -- Standard call to check for call compatibility.
1548 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1549 p_api_version_number,
1550 l_api_name,
1551 G_PKG_NAME) THEN
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553
1554 END IF;
1555
1556 -- Initialize message list if p_init_msg_list is set to TRUE.
1557 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1558 fnd_msg_pub.initialize;
1559 END IF;
1560
1561 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1562 x_matched_prt := JTF_NUMBER_TABLE();
1563
1564 -- ------------------------------------------------------------------------
1565 -- Determines how many partners should be retrieved. This is determined
1566 -- by the profile PV_TOP_N_MATCH_PARTNERS. p_top_n_rows_by_profile
1567 -- determines whether we should get this value from the profile or just
1568 -- set it to a very large number.
1569 -- ------------------------------------------------------------------------
1570 -- ------------------------------------------------------------------------
1571 --IF (p_top_n_rows_by_profile = 'F') THEN
1572 -- l_top_n_rows := 1000000;
1573
1574 --ELSE
1575 -- l_top_n_rows :=nvl(fnd_profile.value('PV_TOP_N_MATCH_PARTNERS'), 1000);
1576 --END IF;
1577 -- ------------------------------------------------------------------------
1578
1579 --IN R12, Obsoleting profile option. PV_TOP_N_MATCH_PARTNERS
1580 --there is no longer a need to restrict the number of partners returned by the API. Remove the logic involved this profile option.
1581
1582 l_top_n_rows := 1000000;
1583
1584
1585 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1586 debug('no of partner to be retrieved '||l_top_n_rows);
1587 END IF;
1588
1589 l_matching_rank := 1;
1590 l_combined_rank := 1;
1591 l_all_ranks := ' 1 ';
1592
1593
1594 IF p_selection_criteria = g_drop_attr_match THEN
1595
1596 for i in 1..p_num_of_attrs - 1 loop
1597
1598 l_matching_rank := l_matching_rank * 2;
1599 l_combined_rank := l_combined_rank + l_matching_rank;
1600 l_all_ranks := l_all_ranks || ' ' || l_combined_rank || ' '; -- like ' 1 3 8 15 31...etc'
1601
1602 end loop;
1603
1604 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1605 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1606 fnd_message.Set_Token('TEXT', 'All ranks: ' || l_all_ranks);
1607 fnd_msg_pub.Add;
1608 END IF;
1609
1610 ELSIF p_selection_criteria = g_nodrop_attr_match THEN
1611
1612 l_matching_rank := p_num_of_attrs;
1613 l_combined_rank := l_matching_rank;
1614
1615
1616 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1617 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1618 fnd_message.Set_Token('TEXT', 'All ranks: ' || p_num_of_attrs);
1619 fnd_msg_pub.Add;
1620 END IF;
1621
1622 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1623 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1624 fnd_message.Set_Token('TEXT', 'Combined rank must match: ' || l_combined_rank);
1625 fnd_msg_pub.Add;
1626 END IF;
1627
1628
1629 END IF;
1630
1631
1632 -- ==============================================================================
1633 -- ==============================================================================
1634 -- Process the dynamic SQL to retrieve matching partner.
1635 -- ==============================================================================
1636 -- ==============================================================================
1637 l_theCursor := DBMS_SQL.OPEN_CURSOR;
1638
1639 DBMS_SQL.PARSE(c => l_theCursor,
1640 statement => p_sql,
1641 language_flag => DBMS_SQL.NATIVE);
1642
1643 -- -------------------------------------------------------------------------------
1644 -- Bind the bind variables.
1645 -- -------------------------------------------------------------------------------
1646 FOR i IN 1..p_bind_var_tbl.COUNT LOOP
1647 DBMS_SQL.BIND_VARIABLE(l_theCursor, ':bv' || i, p_bind_var_tbl(i));
1648 END LOOP;
1649
1650 -- -------------------------------------------------------------------------------
1651 -- Define output columns
1652 -- -------------------------------------------------------------------------------
1653 DBMS_SQL.DEFINE_COLUMN(c => l_theCursor,
1654 position => 1,
1655 column => l_column_party_id);
1656
1657 DBMS_SQL.DEFINE_COLUMN(c => l_theCursor,
1658 position => 2,
1659 column => l_column_rank);
1660
1661 -- -------------------------------------------------------------------------------
1662 -- Execute the dynamic SQL
1663 -- -------------------------------------------------------------------------------
1664 l_status := DBMS_SQL.EXECUTE(l_theCursor);
1665
1666
1667 -- -------------------------------------------------------------------------------
1668 -- Process SQL output row by row
1669 -- -------------------------------------------------------------------------------
1670 WHILE (DBMS_SQL.FETCH_ROWS(c => l_theCursor) > 0) LOOP
1671 DBMS_SQL.COLUMN_VALUE(c => l_theCursor,
1672 position => 1,
1673 value => partner_id);
1674
1675 DBMS_SQL.COLUMN_VALUE(c => l_theCursor,
1676 position => 2,
1677 value => l_rank_base_2);
1678
1679
1680 EXIT WHEN x_matched_prt.count = l_top_n_rows;
1681
1682 IF p_selection_criteria = g_nodrop_attr_match THEN
1683
1684
1685 if l_combined_rank = l_rank_base_2 then
1686
1687 if l_match_count < l_top_n_rows then
1688
1689 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1690 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1691 fnd_message.Set_Token('TEXT', 'Rank matches. Adding partner_id ' || partner_id);
1692 fnd_msg_pub.Add;
1693 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1694 fnd_message.Set_Token('TEXT', 'Current rank: ' || l_rank_base_2 || ' for partner_id: ' || partner_id);
1695 fnd_msg_pub.Add;
1696 END IF;
1697
1698 l_match_count := l_match_count + 1;
1699 x_matched_prt.extend;
1700 x_matched_prt(l_match_count) := partner_id;
1701
1702
1703 end if;
1704
1705 else
1706 if l_match_count > 0 then
1707 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1708 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1709 fnd_message.Set_Token('TEXT', 'Did not find any more matching partner. Exiting loop');
1710 fnd_msg_pub.Add;
1711 END IF;
1712 exit;
1713 end if;
1714 end if;
1715
1716 ELSIF p_selection_criteria = g_drop_attr_match THEN
1717
1718
1719 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1720 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1721 fnd_message.Set_Token('TEXT', 'Combined rank must match: ' || l_combined_rank);
1722 fnd_msg_pub.Add;
1723 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1724 fnd_message.Set_Token('TEXT', 'Current rank: ' || l_rank_base_2 || ' for partner_id: ' || partner_id);
1725 fnd_msg_pub.Add;
1726 END IF;
1727
1728 while (mod (l_rank_base_2, 2) <> 0 /* ignore even numbers which will never match */
1729 and l_combined_rank > l_rank_base_2 /* stop when combined rank drops below current rank */
1730 and l_match_count = 0) /* only decrease combined rank if no partners matched */
1731 loop
1732
1733 if instr(l_all_ranks, ' ' || l_rank_base_2 || ' ') = 0 then
1734
1735 -- not a complete match. eg. 'matches rank 1, 2, 4, 16. (missing 8). adds up to 23.
1736 -- true rank is 7. that is, sum of consecutive ranks
1737 -- will always match at least rank 1 if odd number rank. so find out the true rank
1738
1739 l_tmp_combined_rank := l_combined_rank;
1740 l_tmp_matching_rank := l_matching_rank;
1741 l_tmp_true_rank := l_rank_base_2;
1742
1743 while l_tmp_combined_rank <> l_tmp_true_rank
1744 loop
1745
1746 if l_tmp_combined_rank > l_tmp_true_rank then
1747
1748 l_tmp_combined_rank := l_tmp_combined_rank - l_tmp_matching_rank;
1749
1750 end if;
1751
1752 if l_tmp_true_rank > l_tmp_combined_rank then
1753
1754 l_tmp_true_rank := l_tmp_true_rank - l_tmp_matching_rank;
1755
1756 end if;
1757
1758 l_tmp_matching_rank := l_tmp_matching_rank / 2;
1759
1760 end loop;
1761
1762 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1763 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1764 fnd_message.Set_Token('TEXT', 'Found one guaranteed match for partner: ' || partner_id ||
1765 ' at rank: ' || l_tmp_true_rank);
1766 fnd_msg_pub.Add;
1767 END IF;
1768
1769 if l_tmp_true_rank > l_possible_rank_high then
1770 l_possible_rank_high := l_tmp_true_rank;
1771 end if;
1772
1773 l_possible_match_count := l_possible_match_count + 1;
1774 l_possible_match_party_tbl.extend();
1775 l_possible_match_rank_tbl.extend();
1776
1777 l_possible_match_party_tbl(l_possible_match_count) := partner_id;
1778 l_possible_match_rank_tbl(l_possible_match_count) := l_tmp_true_rank;
1779
1780 exit;
1781
1782 else
1783
1784 l_combined_rank := l_combined_rank - l_matching_rank;
1785 l_matching_rank := l_matching_rank / 2;
1786
1787 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1788 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1789 fnd_message.Set_Token('TEXT', 'Decreasing rank to ' || l_combined_rank);
1790 fnd_msg_pub.Add;
1791 END IF;
1792
1793 end if;
1794
1795 for i in 1..l_possible_match_count loop
1796
1797 if l_combined_rank = l_possible_match_rank_tbl(i) then
1798
1799 if l_match_count < l_top_n_rows then
1800
1801 l_match_count := l_match_count + 1;
1802 x_matched_prt.extend;
1803 x_matched_prt(l_match_count) := l_possible_match_party_tbl(i);
1804
1805 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1806 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1807 fnd_message.Set_Token('TEXT', 'Adding possible matches. Partner_id ' || l_possible_match_party_tbl(i) ||
1808 ' at ' || l_possible_match_rank_tbl(i));
1809 fnd_msg_pub.Add;
1810 END IF;
1811
1812 l_possible_match_rank_tbl(i) := 0; -- so that it doesn't get added again the next time around
1813
1814 else
1815
1816 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1817 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1818 fnd_message.Set_Token('TEXT', 'Reached max partners returned: ' || l_match_count );
1819 fnd_msg_pub.Add;
1820 END IF;
1821
1822 exit;
1823
1824 end if;
1825
1826 end if;
1827
1828 end loop;
1829
1830 end loop;
1831
1832 if l_combined_rank = l_rank_base_2 then
1833
1834 if l_match_count < l_top_n_rows then
1835
1836 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1837 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1838 fnd_message.Set_Token('TEXT', 'Rank matches. Adding partner_id ' || partner_id);
1839 fnd_msg_pub.Add;
1840 END IF;
1841
1842 l_match_count := l_match_count + 1;
1843 x_matched_prt.extend;
1844 x_matched_prt(l_match_count) := partner_id;
1845
1846 end if;
1847
1848 else
1849 if l_match_count > 0 then
1850 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1851 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1852 fnd_message.Set_Token('TEXT', 'Did not find any more matching partner. Exiting loop');
1853 fnd_msg_pub.Add;
1854 END IF;
1855 exit;
1856 end if;
1857 end if;
1858
1859
1860 end if;
1861
1862
1863 END LOOP;
1864
1865 DBMS_SQL.CLOSE_CURSOR(c => l_theCursor);
1866 -- ====================================================================================
1867 -- ====================================================================================
1868 -- End of processing the dynamic SQL for retrieving matching partners.
1869 -- ====================================================================================
1870 -- ====================================================================================
1871
1872
1873 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1874 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1875 fnd_message.Set_Token('TEXT', 'Exiting main loop');
1876 fnd_msg_pub.Add;
1877 END IF;
1878
1879
1880 IF p_selection_criteria = g_drop_attr_match THEN
1881
1882 if x_matched_prt.count = 0 then
1883
1884 l_matching_rank := l_possible_rank_high;
1885
1886 for i in 1..l_possible_match_count loop
1887
1888 if l_matching_rank = l_possible_match_rank_tbl(i) then
1889
1890 if l_match_count < l_top_n_rows then
1891
1892 l_match_count := l_match_count + 1;
1893 x_matched_prt.extend;
1894 x_matched_prt(l_match_count) := l_possible_match_party_tbl(i);
1895
1896 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1897 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1898 fnd_message.Set_Token('TEXT', 'Adding possible matches. Partner_id ' || l_possible_match_party_tbl(i) ||
1899 ' at ' || l_possible_match_rank_tbl(i));
1900 fnd_msg_pub.Add;
1901 END IF;
1902
1903 else
1904 exit;
1905 END IF;
1906 end if;
1907
1908 end loop;
1909
1910 end if;
1911
1912 END IF;
1913
1914 -- Set flag to true / false depending on whether partners are matched or not
1915
1916 if x_matched_prt.count > 0 then
1917
1918 x_prt_matched := true;
1919 l_attr_count := 0;
1920 while (l_matching_rank >= 1)
1921 loop
1922 l_attr_count := l_attr_count + 1;
1923
1924 IF p_selection_criteria = g_drop_attr_match THEN
1925 l_matching_rank := l_matching_rank / 2;
1926 ELSIF p_selection_criteria = g_nodrop_attr_match THEN
1927 l_matching_rank := l_matching_rank-1;
1928 END IF;
1929 end loop;
1930
1931 x_matched_attr_cnt := l_attr_count;
1932
1933 else
1934 x_prt_matched := false;
1935 x_matched_attr_cnt := 0;
1936 end if;
1937
1938 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1939 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1940 fnd_message.Set_Token('TEXT', 'Number of partners found: ' || x_matched_prt.count ||
1941 ' matched attr cnt:' || x_matched_attr_cnt);
1942 fnd_msg_pub.Add;
1943 END IF;
1944
1945 IF FND_API.To_Boolean ( p_commit ) THEN
1946 COMMIT WORK;
1947 END IF;
1948
1949 -- Standard call to get message count and if count is 1, get message info.
1950 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1951 p_count => x_msg_count,
1952 p_data => x_msg_data);
1953
1954 EXCEPTION
1955 WHEN FND_API.G_EXC_ERROR THEN
1956
1957 x_return_status := FND_API.G_RET_STS_ERROR ;
1958
1959 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1960 p_count => x_msg_count,
1961 p_data => x_msg_data);
1962
1963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1964
1965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1966
1967 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1968 p_count => x_msg_count,
1969 p_data => x_msg_data);
1970
1971 WHEN OTHERS THEN
1972
1973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974
1975 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1976
1977 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
1978 p_count => x_msg_count,
1979 p_data => x_msg_data);
1980
1981 END Match_partner;
1982
1983
1984
1985 --=============================================================================+
1986 --| Procedure |
1987 --| |
1988 --| Get_Matched_Partner_Details |
1989 --| This procedure Gets the Matched Partner Details required in the UI |
1990 --| |
1991 --| |
1992 --| |
1993 --| Parameters |
1994 --| IN |
1995 --| OUT |
1996 --| |
1997 --| |
1998 --| NOTES |
1999 --| |
2000 --| HISTORY |
2001 --| |
2002 --==============================================================================
2003
2004
2005 /*
2006 Following Assumptions are made for the following select statements.
2007 1. Flag values should each be a different power of 2 to ensure that
2008 each bit is used by only one flag.
2009 Also, these flag values should match with the flag constants defined
2010 in java API to resolve flags on the front end side.
2011
2012 REJECTED CURRENT OPPORTUNITY = 1
2013 PREFERRED OR INCUMBENT PARTNER FOR CURRENT OPPORTUNITY = 2
2014
2015 2. Most of the select statements assume that PT_APPROVED row for accepted
2016 partner exists in pv_lead_assignments until oppty is recycled by the partner
2017
2018 3. ISSUE : RECYCLED from_status does not have partner_id populated in
2019 pv_assignment_logs. So, rejected partner query may not give the correct result
2020
2021 PROPOSAL : We need to identify an assignment status when partner is
2022 rejecting an oppty
2023
2024 after accepting it . Then, we can populate partner_id in logs table
2025 to identify rejected partner
2026
2027 */
2028
2029
2030
2031 Procedure Get_Matched_Partner_Details(
2032 p_api_version_number IN NUMBER,
2033 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2034 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2035 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2036 p_lead_id IN NUMBER,
2037 p_extra_partner_details IN JTF_VARCHAR2_TABLE_1000,
2038 p_matched_id IN OUT NOCOPY JTF_NUMBER_TABLE,
2039 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
2040 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2041 x_return_status OUT NOCOPY VARCHAR2,
2042 x_msg_count OUT NOCOPY NUMBER,
2043 x_msg_data OUT NOCOPY VARCHAR2) IS
2044
2045 -- ACTIVE_OPPTY_FLAG CONSTANT NUMBER := 1;
2046 -- CLOSED_DEALS_FLAG CONSTANT NUMBER := 2;
2047 REJECTED_OPPTY_FLAG CONSTANT NUMBER := 1;
2048 INCUMBENT_PARTNER_FLAG CONSTANT NUMBER := 2;
2049 TOKEN CONSTANT VARCHAR2(3) := '~';
2050 NULLTOKEN CONSTANT VARCHAR2(3) := '===';
2051 l_party_name varchar2(360);
2052 l_city varchar2(60);
2053 l_state varchar2(60);
2054 l_country varchar2(60);
2055 l_postal_code varchar2(60);
2056 l_address1 varchar2(1000);
2057 l_address2 varchar2(240);
2058 l_address3 varchar2(240);
2059 l_attr_desc varchar2(60);
2060 l_partner_id number;
2061 l_oppty_last_offer_dt varchar2(20);
2062 l_party_id number;
2063 l_partner_count Number := 0;
2064 l_flag_count number := 0;
2065 l_incumbent_pt_party_id number;
2066 l_incumbent_exists_flag boolean;
2067 l_relationship_id NUMBER;
2068 l_partner_name VARCHAR2(3600);
2069 l_internal_org_name VARCHAR2(3600);
2070 l_internal_flag VARCHAR2(1);
2071 l_party_flag VARCHAR2(1);
2072 l_partner_names VARCHAR2(3600);
2073 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2074 l_count NUMBER;
2075 l_wf_status VARCHAR2(1000);
2076 l_active_flag VARCHAR2(1);
2077 l_partner_detail_sql VARCHAR2(4000);
2078
2079 Type partner_det_rec is REF CURSOR;
2080
2081 lc_partner_detail_cur partner_det_rec;
2082
2083
2084 Type l_tmp is Table of Varchar2(4000) index by binary_integer;
2085
2086 l_tmp_ids_tbl l_tmp;
2087 l_tmp_pt_details_tbl l_tmp;
2088
2089 l_incumbent_party_name VARCHAR2(3600);
2090
2091 cursor lc_get_incumbent_pt (pc_lead_id number) is
2092 select asla.INCUMBENT_PARTNER_PARTY_ID
2093 from as_leads_all asla
2094 where asla.lead_id = pc_lead_id;
2095
2096
2097 cursor lc_get_flag_count(pc_lead_id number , pc_partner_id number ,
2098 pc_incumbent_pt_party_id number,
2099 rejected_oppty_flag number,
2100 incumbent_partner_flag number) is
2101 select sum(flagvalue) flagcount
2102 from (
2103 select rejected_oppty_flag flagvalue
2104 from dual
2105 where exists
2106 (select rejected_oppty_flag
2107 from pv_lead_assignments pval
2108 where pval.lead_id = pc_lead_id
2109 and pval.status in ('PT_REJECTED', 'PT_ABANDONED', 'PT_TIMEOUT')
2110 and pval.partner_id = pc_partner_id
2111 )
2112 union
2113 select incumbent_partner_flag flagvalue
2114 from dual
2115 where pc_partner_id = pc_incumbent_pt_party_id
2116 );
2117
2118 cursor lc_duplicate_pt_count
2119 IS
2120 select pvpp.partner_id,
2121 hzp.party_name,
2122 hzop_pt.internal_flag pt_int_flag,
2123 vend.party_name,
2124 hzop_vend.internal_flag vend_int_flag
2125 from hz_parties hzp , pv_partner_profiles pvpp , hz_parties vend,
2126 hz_relationships hzr,
2127 hz_organization_profiles HZOP_pt,
2128 hz_organization_profiles hzop_vend
2129 where hzr.party_id = pvpp.partner_id
2130 and pvpp.partner_party_id = hzr.subject_id
2131 and hzr.subject_id = hzp.party_id
2132 and hzr.subject_table_name = 'HZ_PARTIES'
2133 and hzr.object_table_name = 'HZ_PARTIES'
2134 and hzr.status = 'A' and hzr.start_date <= sysdate and nvl(hzr.end_date,sysdate) >= sysdate
2135 and hzr.subject_id = HZOP_pt.party_id and nvl(hzop_pt.effective_end_date,sysdate) >= sysdate
2136 and hzr.object_id = HZOP_vend.party_id and nvl(hzop_vend.effective_end_date,sysdate) >= sysdate
2137 and (HZOP_vend.internal_flag = 'N' or hzop_pt.internal_flag = 'Y')
2138 and pvpp.partner_id in (
2139 SELECT * FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE))
2140 )
2141 and hzr.object_id = vend.party_id
2142 and hzr.relationship_code in ('PARTNER_OF','VAD_OF');
2143
2144 -- =================================================================================
2145 -- When the Partner Status is Inactive OR Relationship status is inactive
2146 -- OR if the relationship is end dated OR if the Vendor ORG is end dated
2147 -- then the active_flag's value would be 'Inactive'
2148 -- Uncomment this when the local databases are upgraded to 9i
2149 -- =================================================================================
2150
2151
2152 /*
2153 -- 11.5.9
2154 CURSOR l_partner_detail_cur
2155 IS
2156 select hzp.party_name, hzp.city, hzp.state ,
2157 hzp.postal_code, hzp.country, hzp.address1,
2158 hzp.address2, hzp.address3, hzp.party_id,
2159 pvpp.partner_id,
2160 to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
2161 pvac.description, hzr.relationship_id,
2162 (case when hzp.status = 'A'
2163 and hzr.status = 'A'
2164 and nvl(hzop.effective_start_date, sysdate) <= sysdate
2165 and nvl(hzop.effective_end_date, sysdate) >= sysdate
2166 and hzr.start_date <= SYSDATE and NVL(hzr.end_date,SYSDATE) >= SYSDATE
2167 then 'A'
2168 else 'I'
2169 end ) active_flag
2170 from hz_parties hzp , pv_partner_profiles pvpp ,
2171 pv_attribute_codes_vl pvac, hz_relationships hzr ,
2172 hz_organization_profiles HZOP,
2173 (SELECT rownum idx, column_value
2174 FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
2175 where pvpp_partner_id in (SELECT * FROM THE(select CAST(p_matched_id AS JTF_NUMBER_TABLE) from dual))
2176 and pvpp.partner_id = x_partner.column_value
2177 and hzr.party_id = pvpp.partner_id
2178 and hzr.subject_id = hzp.party_id
2179 and hzr.object_id = HZOP.party_id
2180 and HZOP.internal_flag = 'Y'
2181 and hzr.subject_table_name = 'HZ_PARTIES'
2182 and hzr.object_table_name = 'HZ_PARTIES'
2183 and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
2184 order by x_partner.idx;
2185
2186 -- 11.5.10 -- pklin
2187 CURSOR l_partner_detail_cur
2188 IS
2189 select hzp.party_name, hzp.city, hzp.state ,
2190 hzp.postal_code, hzp.country, hzp.address1,
2191 hzp.address2, hzp.address3, hzp.party_id,
2192 pvpp.partner_id,
2193 to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
2194 pvac.description,
2195 pvpp.status active_flag
2196 from hz_parties hzp,
2197 pv_partner_profiles pvpp,
2198 pv_attribute_codes_vl pvac,
2199 (SELECT rownum idx, column_value
2200 FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
2201 where pvpp.partner_id = x_partner.column_value
2202 and pvpp.partner_party_id = hzp.party_id
2203 and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
2204 order by x_partner.idx;
2205 */
2206
2207
2208
2209 /* CURSOR lc_get_pt_org_name(lc_partner_id NUMBER)
2210 IS
2211 select distinct party_name
2212 from hz_relationships hzr,
2213 hz_parties hzp,
2214 hz_organization_profiles HZOP
2215 where hzr.subject_id = hzp.party_id
2216 and hzr.object_id = HZOP.party_id
2217 and HZOP.internal_flag = 'Y'
2218 and hzr.subject_table_name = 'HZ_PARTIES'
2219 and hzr.object_table_name = 'HZ_PARTIES'
2220 and hzr.party_id = lc_partner_id; */
2221
2222 l_pt_id NUMBER;
2223 l_pt_count NUMBER;
2224 l_api_name CONSTANT VARCHAR2(30) := 'Get_Matched_Partner_Details';
2225 l_api_version_number CONSTANT NUMBER := 1.0;
2226
2227
2228 Begin
2229
2230 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2231 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2232 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
2233 fnd_msg_pub.Add;
2234 END IF;
2235
2236 -- Standard call to check for call compatibility.
2237
2238 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2239 p_api_version_number,
2240 l_api_name,
2241 G_PKG_NAME) THEN
2242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2243
2244 END IF;
2245
2246 -- Initialize message list if p_init_msg_list is set to TRUE.
2247 IF FND_API.to_Boolean( p_init_msg_list )
2248 THEN
2249 fnd_msg_pub.initialize;
2250 END IF;
2251
2252 x_flagcount := JTF_VARCHAR2_TABLE_100();
2253 x_partner_details := JTF_VARCHAR2_TABLE_4000();
2254
2255 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2256
2257
2258 -- to be removed later
2259 -- =============================================================================
2260 -- Main Aim of this SQL being dynamic is PL/SQL 8i does not support CASE WHEN.
2261 -- But since the parser of 9i is same for PL/SQL engine and SQL engine
2262 -- CASE WHEN is supported in 9i. Once the local databases are upgraded to 9i
2263 -- this SQL should be a static cursor instead of dynamic.
2264 -- =============================================================================
2265 -- Modified by pklin for 11.5.10. No need to join to hz_relationships and
2266 -- hz_organization_profiles to get the "active_flag". pv_partner_profile.status
2267 -- now contains the denormalized data that indicate whether a partner record
2268 -- is active or not.
2269 --
2270 -- Modified by pklin on 8/6/04 to add additional tables to the use_nl hint.
2271 -- Original hint: use_nl(x_partner)
2272 -- Revised hint: use_nl(x_partner pvpp hzp pvac)
2273 --
2274 -- Also the use of pv_attribute_codes_vl has been modified to use the base
2275 -- table pv_attribute_codes_tl instead.
2276 -- -----------------------------------------------------------------------------
2277
2278 l_partner_detail_sql :=
2279 'select /*+ leading(x_partner) use_nl(x_partner pvpp hzp pvac) */ ' ||
2280 'hzp.party_name, hzp.city, hzp.state , '||
2281 'hzp.postal_code, hzp.country, hzp.address1, hzp.address2, hzp.address3, '||
2282 'hzp.party_id, pvpp.partner_id, pvac.description, '||
2283 'to_char(pvpp.oppty_last_offered_date, ''YYYY-MM-DD HH:MI:SS''), '||
2284 'pvpp.status active_flag ' ||
2285 'from hz_parties hzp , pv_partner_profiles pvpp, pv_attribute_codes_tl pvac, '||
2286 '(SELECT rownum idx, column_value FROM '||
2287 '(SELECT column_value FROM TABLE (CAST(:1 AS JTF_NUMBER_TABLE)))) x_partner '||
2288 'where pvpp.partner_id = x_partner.column_value ' ||
2289 'and pvpp.partner_party_id = hzp.party_id ' ||
2290 'and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+) '||
2291 'and pvac.language(+) = USERENV(''LANG'') ' ||
2292 'order by x_partner.idx ';
2293
2294
2295
2296 for i in 1..ceil((length(l_partner_detail_sql)/100)) loop
2297 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2298 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2299 fnd_message.Set_Token('TEXT', substr(l_partner_detail_sql, (i-1)*100+1, 100));
2300 fnd_msg_pub.Add;
2301 END IF;
2302 end loop;
2303
2304 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2305 debug('Partner Id count before getting details '||p_matched_id.count);
2306 END IF;
2307
2308 if (p_matched_id.count > 0) then
2309
2310 open lc_partner_detail_cur for l_partner_detail_sql using p_matched_id;
2311
2312 loop
2313 fetch lc_partner_detail_cur into l_party_name,l_city, l_state,
2314 l_postal_code, l_country,l_address1,
2315 l_address2, l_address3,l_party_id,
2316 l_partner_id, l_attr_desc,
2317 l_oppty_last_offer_dt,l_active_flag;
2318
2319 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2320 debug('Row Count of details '||lc_partner_detail_cur%ROWCOUNT);
2321 END IF;
2322
2323 exit when lc_partner_detail_cur%NOTFOUND;
2324
2325 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2326
2327 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2328 fnd_message.Set_Token('TEXT', ' l_partner_id : ' || l_partner_id || fnd_global.local_chr(10) ||
2329 ' l_party_id : ' || l_party_id || fnd_global.local_chr(10) ||
2330 ' l_attr_desc : ' || l_attr_desc || fnd_global.local_chr(10) ||
2331 ' l_party_name : ' || l_party_name || fnd_global.local_chr(10) ||
2332 ' l_oppty_last_offer_dt: ' || l_oppty_last_offer_dt|| fnd_global.local_chr(10) ||
2333 ' l_active_flag : '|| l_active_flag);
2334 fnd_msg_pub.Add;
2335
2336 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2337 fnd_message.Set_Token('TEXT', ' l_city : ' || l_city || fnd_global.local_chr(10) ||
2338 ' l_state : ' || l_state || fnd_global.local_chr(10) ||
2339 ' l_postal_code : ' || l_postal_code || fnd_global.local_chr(10) ||
2340 ' l_country : ' || l_country || fnd_global.local_chr(10) ||
2341 ' l_address1 : ' || l_address1 || fnd_global.local_chr(10) ||
2342 ' l_address2 : ' || l_address2 || fnd_global.local_chr(10) ||
2343 ' l_address3 : ' || l_address3 || fnd_global.local_chr(10));
2344 fnd_msg_pub.Add;
2345
2346 END IF;
2347
2348 if l_address1 is not null then
2349 l_address1 := l_address1 || ',';
2350 end if;
2351
2352 if l_address2 is not null then
2353 l_address1 := l_address1 || l_address2 || ',';
2354 end if;
2355
2356 if l_address3 is not null then
2357 l_address1 := l_address1 || l_address3 || ',';
2358 end if;
2359
2360 if l_city is not null then
2361 l_address1 := l_address1 || l_city || ',';
2362 end if;
2363
2364 if l_state is not null then
2365 l_address1 := l_address1 || l_state || ',';
2366 end if;
2367
2368 if l_country is not null then
2369 l_address1 := l_address1 || l_country || ',';
2370 end if;
2371
2372 if l_postal_code is not null then
2373 l_address1 := l_address1 || l_postal_code || ',' ;
2374 end if;
2375
2376 if l_address1 is not null then
2377 l_address1 := replace(substr(l_address1, 1, length(l_address1) - 1), '~', '^');
2378 else
2379 l_address1 := NULLTOKEN;
2380 end if;
2381
2382 if l_party_name is null then
2383 l_party_name := NULLTOKEN;
2384 else
2385 l_party_name := replace(l_party_name, '~', '^');
2386 end if;
2387
2388 if l_attr_desc is null then
2389 l_attr_desc := NULLTOKEN;
2390 end if;
2391
2392 if l_oppty_last_offer_dt is null then
2393 l_oppty_last_offer_dt := NULLTOKEN;
2394 end if;
2395
2396 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2397 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2398 fnd_message.Set_Token('TEXT', 'Appended Address String : ');
2399 fnd_msg_pub.Add;
2400 END IF;
2401
2402 for i in 1..ceil((length(l_address1)/100)) loop
2403 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2404 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2405 fnd_message.Set_Token('TEXT', substr(l_address1, (i-1)*100+1, 100));
2406 fnd_msg_pub.Add;
2407 END IF;
2408 end loop;
2409
2410 l_partner_count := l_partner_count + 1;
2411 l_tmp_ids_tbl(l_partner_count) := l_partner_id;
2412
2413
2414 l_tmp_pt_details_tbl(l_partner_count) := l_party_name || TOKEN || l_party_id || TOKEN || l_partner_id ||
2415 TOKEN || l_address1 || TOKEN || l_attr_desc || TOKEN ||
2416 l_oppty_last_offer_dt ||TOKEN ||
2417 l_active_flag || TOKEN;
2418
2419 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2420
2421 Debug('Appended Matched Partner Info : ');
2422
2423
2424 for i in 1..ceil((length(l_tmp_pt_details_tbl(l_partner_count))/100)) loop
2425 Debug('Partner id '||substr( l_tmp_pt_details_tbl(l_partner_count),(i-1)*100+1, 100));
2426 end loop;
2427 END IF;
2428
2429
2430 end loop;
2431
2432 close lc_partner_detail_cur;
2433
2434 IF p_matched_id.count = l_tmp_ids_tbl.count THEN
2435
2436 x_partner_details.extend(p_matched_id.count);
2437
2438 ELSE
2439
2440 l_partner_names := NULL;
2441
2442 OPEN lc_duplicate_pt_count;
2443 LOOP
2444 FETCH lc_duplicate_pt_count INTO l_pt_id, l_partner_name, l_party_flag, l_internal_org_name, l_internal_flag;
2445 EXIT WHEN lc_duplicate_pt_count%NOTFOUND;
2446
2447 IF l_party_flag = 'Y' THEN
2448
2449 IF l_party_flag = l_internal_flag THEN
2450
2451
2452 IF l_partner_names is NULL THEN
2453 l_partner_names := l_partner_name ;
2454 ELSE
2455 l_partner_names := l_partner_names || ' , ' || l_partner_name ;
2456 END IF;
2457
2458
2459 END IF;
2460
2461 END IF;
2462
2463
2464
2465 END LOOP;
2466 CLOSE lc_duplicate_pt_count;
2467
2468 IF l_party_flag = 'Y' THEN
2469
2470 IF l_party_flag = l_internal_flag and l_partner_names is not null THEN
2471
2472 fnd_message.Set_Name('PV', 'PV_WRONG_INTRNL_ORG');
2473 fnd_message.Set_Token('P_PT_NAMES', l_partner_names);
2474 fnd_msg_pub.Add;
2475
2476
2477 raise FND_API.G_EXC_ERROR;
2478
2479
2480 END IF;
2481
2482 END IF;
2483
2484
2485 END IF;
2486
2487
2488 for i in 1 .. l_tmp_ids_tbl.count loop
2489
2490 IF (g_from_match_lov_flag) THEN -- For Matching Rows
2491
2492
2493 x_partner_details(i) := l_tmp_pt_details_tbl(i) || 'MATCHING';
2494
2495
2496
2497 ELSE -- For submitted routing rows
2498
2499 IF p_extra_partner_details.count = 0 THEN
2500
2501 x_partner_details(i) := l_tmp_pt_details_tbl(i);
2502 ELSE
2503
2504 x_partner_details(i) := l_tmp_pt_details_tbl(i) || p_extra_partner_details(i);
2505
2506 END IF;
2507
2508 END IF;
2509
2510 END LOOP;
2511
2512
2513 -- Reinitializaing matched ID table
2514
2515 p_matched_id.delete;
2516
2517
2518 for k in 1..l_tmp_ids_tbl.count loop
2519
2520 p_matched_id.extend;
2521 p_matched_id(p_matched_id.count) := l_tmp_ids_tbl(k);
2522
2523 end loop;
2524
2525 open lc_get_incumbent_pt (p_lead_id);
2526 fetch lc_get_incumbent_pt into l_incumbent_pt_party_id;
2527
2528 if l_incumbent_pt_party_id is null then
2529
2530 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2531 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2532 fnd_message.Set_Token('TEXT', 'Incumbent Partner party ID is null. So, setting it to be zero');
2533 fnd_msg_pub.Add;
2534 END IF;
2535
2536 l_incumbent_pt_party_id := 0;
2537 end if;
2538
2539
2540 close lc_get_incumbent_pt;
2541
2542 IF l_tmp_ids_tbl.count > 0 THEN
2543
2544 for i in l_tmp_ids_tbl.first .. l_tmp_ids_tbl.last loop
2545
2546 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2547 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2548 fnd_message.Set_Token('TEXT', 'Looking for Flag count for Partner Id : ' || l_tmp_ids_tbl(i));
2549 fnd_msg_pub.Add;
2550 END IF;
2551
2552 open lc_get_flag_count(p_lead_id, l_tmp_ids_tbl(i), l_incumbent_pt_party_id, REJECTED_OPPTY_FLAG, INCUMBENT_PARTNER_FLAG);
2553 fetch lc_get_flag_count into l_flag_count;
2554
2555 x_flagcount.extend;
2556
2557 if lc_get_flag_count%found
2558 and l_flag_count is not null then
2559 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2560 Debug( 'flag Count : ' || l_flag_count);
2561 END IF;
2562 x_flagcount(i) := l_flag_count;
2563 else
2564 x_flagcount(i) := 0;
2565 end if;
2566
2567 l_flag_count := 0;
2568 close lc_get_flag_count;
2569
2570 end loop;
2571
2572 end if;
2573
2574 end if;
2575
2576 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2577
2578 For i in 1 .. p_matched_id.count
2579 Loop
2580 Debug('Partner ID from p_matched_id tbl : ' || p_matched_id(i));
2581 end loop;
2582
2583 Debug('end of partner details : ' );
2584 END IF;
2585
2586 IF FND_API.To_Boolean ( p_commit ) THEN
2587 COMMIT WORK;
2588 END IF;
2589
2590 -- Standard call to get message count and if count is 1, get message info.
2591 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2592 p_count => x_msg_count,
2593 p_data => x_msg_data);
2594 EXCEPTION
2595
2596 WHEN FND_API.G_EXC_ERROR THEN
2597
2598 x_return_status := FND_API.G_RET_STS_ERROR ;
2599 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2600 p_count => x_msg_count,
2601 p_data => x_msg_data);
2602
2603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2604
2605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2606
2607 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2608 p_count => x_msg_count,
2609 p_data => x_msg_data);
2610
2611 WHEN OTHERS THEN
2612
2613
2614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2615
2616 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2617
2618 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
2619 p_count => x_msg_count,
2620 p_data => x_msg_data);
2621
2622 End get_matched_partner_details;
2623
2624
2625
2626 /*************************************************************************************/
2627
2628 /* public routines */
2629 /* */
2630 /*************************************************************************************/
2631 -- pklin
2632
2633 --=============================================================================+
2634 --| Procedure |
2635 --| |
2636 --| Get_Assigned_Partners |
2637 --| This procedure Gets the Assigned Partner and their details required |
2638 --| in UI |
2639 --| |
2640 --| |
2641 --| Parameters |
2642 --| IN |
2643 --| OUT |
2644 --| |
2645 --| |
2646 --| NOTES |
2647 --| |
2648 --| HISTORY |
2649 --| |
2650 --==============================================================================
2651
2652 Procedure Get_Assigned_Partners(
2653 p_api_version_number IN NUMBER,
2654 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2655 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2656 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2657 p_lead_id IN NUMBER,
2658 p_resource_id IN NUMBER,
2659 x_assigned_partner_id OUT NOCOPY JTF_NUMBER_TABLE,
2660 x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
2661 x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2662 x_return_status OUT NOCOPY VARCHAR2,
2663 x_msg_count OUT NOCOPY NUMBER,
2664 x_msg_data OUT NOCOPY VARCHAR2) IS
2665
2666 TOKEN CONSTANT VARCHAR2(3) := '~';
2667 NULLTOKEN CONSTANT VARCHAR2(3) := '===';
2668 DUMMY_NUMBER CONSTANT NUMBER := 99999999999;
2669 Type assignment_rec is REF CURSOR;
2670
2671 assignment_cur assignment_rec;
2672 l_tmp_tbl JTF_VARCHAR2_TABLE_1000;
2673 l_tmp varchar2(10);
2674 l_incumbent_pt_party_id number;
2675 l_assignment_started varchar2(1000);
2676 l_assigned_partners varchar2(2000);
2677 l_response varchar2(30);
2678 l_resource_id Number;
2679 l_primary_key Number;
2680 l_response_date varchar2(20);
2681 l_source varchar2(10);
2682 l_lock_flag varchar2(1);
2683 l_partnerid number;
2684 l_tmp_partnerid number := 0;
2685 l_previous_pt_count number;
2686 l_partner_cnt_pt_id number;
2687 l_party_reltn_type varchar2(100);
2688 l_object_id number;
2689 l_partner_count number := 1;
2690 l_pt_flag boolean := false;
2691 l_duplicate_pt boolean := false;
2692 l_wf_started varchar2(30) ;
2693 l_decision_maker varchar2(1);
2694 l_assign_sequence number;
2695 l_routing_status varchar2(30);
2696 l_wf_status varchar2(30);
2697
2698 cursor lc_get_lead_status ( pc_lead_id NUMBER) is
2699 select wf_status, routing_status
2700 from pv_lead_workflows
2701 where lead_id = pc_lead_id
2702 and latest_routing_flag = 'Y';
2703
2704 cursor lc_id_type (pc_party_rel_id number) is
2705 select
2706 pr.relationship_type,
2707 pr.object_id
2708 from hz_relationships pr,
2709 hz_parties pt
2710 where pr.party_id = pc_party_rel_id
2711 AND pr.subject_table_name = 'HZ_PARTIES'
2712 AND pr.object_table_name = 'HZ_PARTIES'
2713 AND pr.directional_flag = 'F'
2714 and pr.subject_id = pt.party_id;
2715
2716
2717
2718
2719 cursor lc_get_routed_partners (pc_lead_id number)
2720 is
2721 select PVLA.PARTNER_ID, PVPN.RESOURCE_ID, 'PN' source ,
2722 PVLA.STATUS, to_char(PVLA.STATUS_DATE, 'YYYY-MM-DD HH:MM:SS'),
2723 pvpn.DECISION_MAKER_FLAG
2724 from PV_LEAD_ASSIGNMENTS PVLA,
2725 PV_PARTY_NOTIFICATIONS PVPN,
2726 PV_LEAD_WORKFLOWS PVLW
2727 where pvlw.LEAD_ID = pc_lead_id
2728 and pvlw.LATEST_ROUTING_FLAG = 'Y'
2729 and pvlw.WF_ITEM_KEY = pvla.WF_ITEM_KEY
2730 and pvlw.WF_ITEM_TYPE = pvla.WF_ITEM_TYPE
2731 and PVLA.LEAD_ASSIGNMENT_ID = PVPN.LEAD_ASSIGNMENT_ID(+)
2732 and PVPN.NOTIFICATION_TYPE(+) = 'MATCHED_TO'
2733 ORDER BY PVLA.ASSIGN_SEQUENCE, PVLA.PARTNER_ID;
2734
2735 cursor lc_get_matched_partners (pc_lead_id number)
2736 is
2737 select asac.partner_customer_id ,
2738 'SALESTEAM' source , access_id, 99999999999
2739 from as_accesses asac
2740 where asac.lead_id = pc_lead_id
2741 and asac.sales_lead_id is null
2742 and (asac.partner_cont_party_id is not null
2743 or asac.partner_customer_id is not null )
2744 union
2745 select pvla.partner_id partner_id, pvla.source_type source
2746 , lead_assignment_id ,pvla.assign_sequence
2747 from pv_lead_assignments pvla
2748 where pvla.lead_id = pc_lead_id
2749 and pvla.status = 'UNASSIGNED'
2750 order by 4;
2751
2752
2753 l_api_name CONSTANT VARCHAR2(30) := 'Get_Assigned_Partners';
2754 l_api_version_number CONSTANT NUMBER := 1.0;
2755
2756 Begin
2757 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2758 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2759 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Lead id: ' || p_lead_id );
2760 fnd_msg_pub.Add;
2761 END IF;
2762
2763 -- Standard call to check for call compatibility.
2764
2765 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2766 p_api_version_number,
2767 l_api_name,
2768 G_PKG_NAME) THEN
2769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2770 END IF;
2771
2772 -- Initialize message list if p_init_msg_list is set to TRUE.
2773 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2774 fnd_msg_pub.initialize;
2775 END IF;
2776
2777 l_tmp_tbl := JTF_VARCHAR2_TABLE_1000();
2778 x_flagcount := JTF_VARCHAR2_TABLE_100();
2779 x_partner_details := JTF_VARCHAR2_TABLE_4000();
2780 x_assigned_partner_id := JTF_NUMBER_TABLE();
2781 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2782
2783 /**
2784 p_wf_started value passed into the API has to be either Y or N.
2785 All other values are ignored.
2786 **/
2787
2788
2789 open lc_get_lead_status(p_lead_id);
2790 loop
2791 fetch lc_get_lead_status into l_wf_status, l_routing_status;
2792
2793 IF lc_get_lead_status%ROWCOUNT > 1 THEN
2794 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2795 debug('There should be only one row in the lead workflows table. Check.........');
2796 END IF;
2797 END IF;
2798
2799 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2800 debug('Wf Started '||l_wf_started);
2801 END IF;
2802
2803 IF l_wf_status = 'CLOSED'
2804 AND l_routing_status in ( 'UNASSIGNED', 'ABANDONED','FAILED_AUTO_ASSIGN', 'WITHDRAWN', 'RECYCLED')
2805 THEN
2806 l_wf_started := 'N';
2807 ELSIF l_wf_status is null
2808 AND l_routing_status is null THEN
2809 l_wf_started := 'N';
2810 ELSIF l_wf_status = 'OPEN' THEN
2811 l_wf_started := 'Y';
2812 ELSE
2813 l_wf_started := 'Y';
2814 END IF;
2815 exit when lc_get_lead_status%NOTFOUND;
2816 end loop;
2817 close lc_get_lead_status;
2818
2819
2820 if l_wf_started = 'Y' then
2821
2822 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2823 Debug('WorkFlow is not started for '|| ' Lead ID: '|| p_lead_id);
2824 END IF;
2825
2826
2827 open lc_get_routed_partners(p_lead_id);
2828
2829 loop
2830 fetch lc_get_routed_partners into l_partnerid, l_resource_id, l_source,
2831 l_response, l_response_date , l_decision_maker;
2832
2833 exit when lc_get_routed_partners%notfound;
2834
2835 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2836 Debug( 'partner count : ' ||l_partner_count);
2837 END IF;
2838
2839
2840 if l_resource_id is null then
2841 l_resource_id :=DUMMY_NUMBER;
2842 end if;
2843
2844 if l_tmp_partnerid <> l_partnerid then
2845
2846 if l_partner_count > 1 then
2847 for x in l_previous_pt_count ..l_tmp_tbl.count loop
2848 l_tmp_tbl(x) := l_tmp_tbl(x) || l_lock_flag;
2849 end loop;
2850 end if;
2851
2852 l_tmp_partnerid := l_partnerid;
2853 l_lock_flag := 'Y';
2854 l_duplicate_pt := false;
2855 l_previous_pt_count := l_partner_count;
2856
2857 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2858 Debug('Prtnr Changed. Tmp Pt ID:' || L_tmp_partnerid || ' pt id: ' ||l_partnerid);
2859 Debug('previous pt count : ' || l_previous_pt_count || '. Lock Flag : ' || l_lock_flag);
2860 END IF;
2861
2862
2863 else
2864 l_duplicate_pt := true;
2865 end if;
2866
2867 if l_lock_flag = 'Y' then
2868
2869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2870 debug('l_resource_id : ' || l_resource_id || ' p_resource_id : ' || p_resource_id);
2871 debug('l_decision_maker : ' || l_decision_maker);
2872 END IF;
2873
2874 if (l_resource_id = p_resource_id and l_response <> 'CM_APP_FOR_PT' and l_response <> 'CM_ADD_APP_FOR_PT'
2875 and l_response <> 'CM_REJECTED' and l_response <> 'PT_APPROVED' and l_decision_maker = 'Y') then
2876 l_lock_flag := 'N';
2877 else
2878 l_lock_flag := 'Y';
2879 end if;
2880
2881 end if;
2882
2883 if not l_duplicate_pt then
2884
2885 if l_response is null then
2886 l_response := NULLTOKEN;
2887 end if;
2888
2889 if l_response_date is null then
2890 l_response_date := NULLTOKEN;
2891 end if;
2892
2893 if l_source is null then
2894 l_source := NULLTOKEN;
2895 end if;
2896
2897 l_tmp_tbl.extend;
2898 l_tmp_tbl(l_partner_count) := l_source || TOKEN || DUMMY_NUMBER || TOKEN || l_response || TOKEN ||
2899 l_response_date || TOKEN ;
2900
2901 x_assigned_partner_id.extend;
2902 x_assigned_partner_id(l_partner_count) := l_partnerid;
2903
2904 l_partner_count := l_partner_count + 1;
2905
2906 end if;
2907
2908 end loop;
2909
2910 if l_partner_count > 1 then
2911 for x in l_previous_pt_count ..l_tmp_tbl.count loop
2912 l_tmp_tbl(x) := l_tmp_tbl(x) || l_lock_flag;
2913 end loop;
2914 end if;
2915
2916 close lc_get_routed_partners;
2917
2918
2919 elsif l_wf_started ='N' then
2920
2921 /**
2922 ** Submit Routing in UI will save all the partners in pv_lead_assignments
2923 ** If there is an error in creating the assignment, we can't roll back the above as they are part of two separate
2924 ** transactions in UI. So, when users gets back to assignment detail UI page, we'll like do our original logic.
2925 ** i.e. Get Partnes from Sales Team and partners that are saved from matching.
2926 ** we would n't want to get sales team partners from pv_lead_assignments
2927 **/
2928
2929 -- Commenting out this delete statement as a fix for the ranking issue if the partners
2930 -- came from salesteam. The salesteam rows were being deleted and then queried from
2931 -- the as_accesses_all table. Because of this we could never rank the partners added
2932 -- from salesteam. This change in conjunction with a change in the java layer gets us
2933 -- the desired result. Please refer to bug 3614435 for more details.
2934 /*
2935 delete from pv_lead_assignments
2936 where lead_id = p_lead_id
2937 and source_type = 'SALESTEAM'
2938 and status = 'UNASSIGNED';
2939 */
2940
2941 /**
2942 if(SQL%Found) then
2943 fnd_message.SET_NAME ('PV', 'Just Deleted' || SQL%ROWCOUNT);
2944 fnd_msg_pub.ADD;
2945
2946 raise FND_API.G_EXC_ERROR;
2947 end if;
2948 **/
2949 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2950 Debug('WorkFlow is not started for '|| ' Lead ID: '|| p_lead_id);
2951 END IF;
2952
2953
2954
2955
2956 open lc_get_matched_partners(p_lead_id);
2957
2958 loop
2959 fetch lc_get_matched_partners into l_partnerid, l_source, l_primary_key, l_assign_sequence;
2960
2961 exit when lc_get_matched_partners%notfound;
2962
2963 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2964 Debug('partner id :' || l_partnerid );
2965 END IF;
2966
2967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2968 Debug( 'Source ' || l_source|| ' primary key : ' ||l_primary_key||' assign sequence :' ||l_assign_sequence);
2969 END IF;
2970
2971 if l_partnerid is not null then
2972 open lc_id_type(l_partnerid);
2973
2974 fetch lc_id_type into l_party_reltn_type, l_object_id ;
2975
2976 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2977 Debug('Party Relationship Type : '|| l_party_reltn_type || ' Object ID : ' || l_object_id);
2978 END IF;
2979
2980
2981
2982 if lc_id_type%found and l_party_reltn_type is not null then
2983
2984 if (l_party_reltn_type = 'PARTNER' or l_party_reltn_type = 'VAD') then
2985
2986 l_pt_flag := true;
2987
2988 end if;
2989
2990 end if;
2991 close lc_id_type;
2992 end if;
2993
2994 if x_assigned_partner_id.count > 0 then
2995
2996 for i in x_assigned_partner_id.FIRST ..x_assigned_partner_id.LAST loop
2997
2998 if x_assigned_partner_id(i) = l_partnerid then
2999 l_duplicate_pt := true;
3000
3001 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3002 Debug('Duplicate Partner');
3003 END IF;
3004
3005
3006 end if;
3007
3008 end loop;
3009
3010 end if;
3011
3012 if (not l_duplicate_pt) AND l_pt_flag then
3013
3014 l_tmp_tbl.extend;
3015 l_tmp_tbl(l_partner_count) := l_source || TOKEN || l_primary_key || TOKEN;
3016
3017 x_assigned_partner_id.extend;
3018 x_assigned_partner_id(l_partner_count) := l_partnerid;
3019
3020 l_partner_count := l_partner_count + 1;
3021
3022 end if;
3023 l_duplicate_pt := false;
3024 l_pt_flag := false;
3025
3026 end loop;
3027
3028 close lc_get_matched_partners;
3029
3030 end if;
3031
3032 IF x_assigned_partner_id.count > 0 then
3033
3034 g_from_match_lov_flag := FALSE;
3035 Get_Matched_Partner_Details(
3036 p_api_version_number => 1.0
3037 ,p_init_msg_list => FND_API.G_FALSE
3038 ,p_commit => FND_API.G_FALSE
3039 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3040 ,p_lead_id => p_lead_id
3041 ,p_extra_partner_details => l_tmp_tbl
3042 ,p_matched_id => x_assigned_partner_id
3043 ,x_partner_details => x_partner_details
3044 ,x_flagcount => x_flagcount
3045 ,x_return_status => x_return_status
3046 ,x_msg_count => x_msg_count
3047 ,x_msg_data => x_msg_data);
3048
3049 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3050 RAISE fnd_api.g_exc_error;
3051 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3052 RAISE fnd_api.g_exc_unexpected_error;
3053 END IF;
3054
3055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3056 Debug('# of Partners Returned from matched_partner_details: ' || x_assigned_partner_id.COUNT);
3057 END IF;
3058
3059
3060 for i in 1 .. l_tmp_tbl.count
3061 loop
3062 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3063 debug('Extra Partner Details :('||i ||' )'||l_tmp_tbl(i));
3064 END IF;
3065
3066 end loop;
3067
3068 END IF;
3069
3070 IF FND_API.To_Boolean ( p_commit ) THEN
3071 COMMIT WORK;
3072 END IF;
3073
3074 -- Standard call to get message count and if count is 1, get message info.
3075 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3076 p_count => x_msg_count,
3077 p_data => x_msg_data);
3078 EXCEPTION
3079
3080 WHEN FND_API.G_EXC_ERROR THEN
3081
3082 x_return_status := FND_API.G_RET_STS_ERROR ;
3083
3084 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3085 p_count => x_msg_count,
3086 p_data => x_msg_data);
3087
3088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3089
3090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3091
3092 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3093 p_count => x_msg_count,
3094 p_data => x_msg_data);
3095
3096 WHEN OTHERS THEN
3097
3098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3099
3100 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3101
3102 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3103 p_count => x_msg_count,
3104 p_data => x_msg_data);
3105
3106 End Get_Assigned_partners;
3107
3108
3109
3110 --=============================================================================+
3111 --| Procedure |
3112 --| |
3113 --| Create_Assignment |
3114 --| This procedure Gets the Assigned Partner and their details required |
3115 --| in UI |
3116 --| |
3117 --| |
3118 --| Parameters |
3119 --| IN |
3120 --| OUT |
3121 --| |
3122 --| |
3123 --| NOTES |
3124 --| |
3125 --| HISTORY |
3126 --| |
3127 --==============================================================================
3128
3129
3130 Procedure Create_Assignment(
3131 P_API_VERSION_NUMBER IN NUMBER,
3132 P_INIT_MSG_LIST IN VARCHAR2,
3133 P_COMMIT IN VARCHAR2,
3134 P_VALIDATION_LEVEL IN NUMBER,
3135 P_ENTITY IN VARCHAR2,
3136 P_LEAD_ID IN NUMBER,
3137 P_CREATING_USERNAME IN VARCHAR2,
3138 P_ASSIGNMENT_TYPE IN VARCHAR2,
3139 P_BYPASS_CM_OK_FLAG IN VARCHAR2,
3140 P_PROCESS_RULE_ID IN NUMBER,
3141 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3142 X_MSG_COUNT OUT NOCOPY NUMBER,
3143 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
3144
3145 l_partner_count number := 1;
3146 l_partner_id number;
3147 l_rank number;
3148 l_source_type VARCHAR2(30);
3149 l_partner_id_tbl JTF_NUMBER_TABLE;
3150 l_rank_tbl JTF_NUMBER_TABLE;
3151 l_source_type_tbl JTF_VARCHAR2_TABLE_100;
3152 l_party_id number;
3153 l_party_count number := 0;
3154
3155 cursor lc_get_saved_pts (pc_lead_id number) is
3156 select pvla.partner_id partner_id, pvla.ASSIGN_SEQUENCE, pvla.source_type
3157 from pv_lead_assignments pvla
3158 where pvla.lead_id = pc_lead_id
3159 and pvla.status = 'UNASSIGNED';
3160
3161 l_api_name CONSTANT VARCHAR2(30) := 'Create_Assignment';
3162 l_api_version_number CONSTANT NUMBER := 1.0;
3163
3164 Begin
3165 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3166 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3167 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Lead id: ' || p_lead_id);
3168 fnd_msg_pub.Add;
3169 END IF;
3170
3171 -- Standard call to check for call compatibility.
3172 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3173 p_api_version_number,
3174 l_api_name,
3175 G_PKG_NAME) THEN
3176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3177 END IF;
3178
3179 -- Initialize message list if p_init_msg_list is set to TRUE.
3180 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3181 fnd_msg_pub.initialize;
3182 END IF;
3183
3184 l_PARTNER_ID_TBL := JTF_NUMBER_TABLE();
3185 l_RANK_TBL := JTF_NUMBER_TABLE();
3186 l_source_type_tbl := jtf_varchar2_table_100();
3187 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3188
3189 open lc_get_saved_pts(p_lead_id);
3190 loop
3191 fetch lc_get_saved_pts into l_partner_id, l_rank, l_source_type;
3192 exit when lc_get_saved_pts%notfound;
3193 l_party_count := 0;
3194 IF l_partner_id_tbl.count > 0 THEN
3195 FOR x IN ( SELECT count(party_id) cnt
3196 FROM (SELECT rownum idx, column_value party_id
3197 FROM (SELECT column_value
3198 FROM TABLE (CAST(l_partner_id_tbl AS JTF_NUMBER_TABLE))
3199 )
3200 ) a
3201 WHERE a.party_id = l_partner_id
3202 GROUP BY A.PARTY_ID )
3203 LOOP
3204 l_party_count := x.cnt;
3205 END LOOP;
3206
3207 END IF;
3208 IF l_party_count = 0 THEN
3209 l_PARTNER_ID_TBL.extend;
3210 l_RANK_TBL.extend;
3211 l_source_type_tbl.extend;
3212
3213 l_PARTNER_ID_TBL(l_partner_count) := l_partner_id;
3214 l_RANK_TBL(l_partner_count) := l_rank;
3215 l_source_type_tbl(l_partner_count) := l_source_type;
3216
3217 l_partner_count := l_partner_count + 1;
3218 END IF;
3219 end loop;
3220 close lc_get_saved_pts;
3221
3222 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
3223 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3224 fnd_message.Set_Token('TEXT', 'Deleting Rows from pv_lead_assignments : ');
3225 fnd_msg_pub.Add;
3226 END IF;
3227
3228 delete from pv_lead_assignments
3229 where lead_id = p_lead_id
3230 and status = 'UNASSIGNED';
3231
3232 PV_ASSIGNMENT_PUB. CREATEASSIGNMENT(
3233 p_api_version_number => 1.0
3234 ,p_init_msg_list => FND_API.G_FALSE
3235 ,p_commit => FND_API.G_FALSE
3236 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3237 ,p_entity => p_entity
3238 ,p_lead_id => p_lead_id
3239 ,P_creating_username => p_creating_username
3240 ,P_assignment_type => p_assignment_type
3241 ,p_bypass_cm_ok_flag => p_bypass_cm_ok_flag
3242 ,p_partner_id_tbl => l_partner_id_tbl
3243 ,p_rank_tbl => l_rank_tbl
3244 ,p_partner_source_tbl => l_source_type_tbl
3245 ,p_process_rule_id => p_process_rule_id
3246 ,x_return_status => x_return_status
3247 ,x_msg_count => x_msg_count
3248 ,x_msg_data => x_msg_data);
3249
3250 IF FND_API.To_Boolean ( p_commit ) THEN
3251 COMMIT WORK;
3252 END IF;
3253
3254 -- Standard call to get message count and if count is 1, get message info.
3255 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3256 p_count => x_msg_count,
3257 p_data => x_msg_data);
3258 EXCEPTION
3259
3260 WHEN FND_API.G_EXC_ERROR THEN
3261
3262 x_return_status := FND_API.G_RET_STS_ERROR ;
3263
3264 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3265 p_count => x_msg_count,
3266 p_data => x_msg_data);
3267
3268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3269
3270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3271
3272 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3273 p_count => x_msg_count,
3274 p_data => x_msg_data);
3275
3276 WHEN OTHERS THEN
3277
3278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3279
3280 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3281
3282 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
3283 p_count => x_msg_count,
3284 p_data => x_msg_data);
3285
3286 End Create_Assignment;
3287
3288
3289 -- pklin
3290 --=============================================================================+
3291 --| Public Procedure |
3292 --| |
3293 --| Debug |
3294 --| |
3295 --| Parameters |
3296 --| IN |
3297 --| OUT |
3298 --| |
3299 --| |
3300 --| NOTES: |
3301 --| |
3302 --| HISTORY |
3303 --| |
3304 --==============================================================================
3305 PROCEDURE Debug(
3306 p_msg_string IN VARCHAR2
3307 )
3308 IS
3309
3310 BEGIN
3311 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
3312 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
3313 FND_MSG_PUB.Add;
3314 END Debug;
3315 -- =================================End of Debug================================
3316
3317 --=============================================================================+
3318 --| Public Procedure |
3319 --| |
3320 --| Set_Message |
3321 --| |
3322 --| Parameters |
3323 --| IN |
3324 --| OUT NOCOPY |
3325 --| |
3326 --| |
3327 --| NOTES: |
3328 --| |
3329 --| HISTORY |
3330 --| |
3331 --==============================================================================
3332 PROCEDURE Set_Message(
3333 p_msg_level IN NUMBER,
3334 p_msg_name IN VARCHAR2,
3335 p_token1 IN VARCHAR2,
3336 p_token1_value IN VARCHAR2,
3337 p_token2 IN VARCHAR2 := NULL ,
3338 p_token2_value IN VARCHAR2 := NULL,
3339 p_token3 IN VARCHAR2 := NULL,
3340 p_token3_value IN VARCHAR2 := NULL
3341 )
3342 IS
3343 BEGIN
3344 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
3345 FND_MESSAGE.Set_Name('PV', p_msg_name);
3346 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
3347
3348 IF (p_token2 IS NOT NULL) THEN
3349 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
3350 END IF;
3351
3352 IF (p_token3 IS NOT NULL) THEN
3353 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
3354 END IF;
3355
3356 FND_MSG_PUB.Add;
3357 END IF;
3358 END Set_Message;
3359 -- ==============================End of Set_Message==============================
3360
3361 -- %%%%%%%%%%%%%%%%%%%%%% Private Routines %%%%%%%%%%%%%%%%%%%%%%%
3362 -- =================================================================
3363 -- get_no_of_delimiter will return the no of delimiters in a given
3364 -- string.
3365 -- When p_attr_value is "abc+++def+++ghi" and the delimiter is
3366 -- "+++" then the output from this function would be 2
3367 -- which means there are two delimiters in this function
3368 -- =================================================================
3369
3370 FUNCTION get_no_of_delimiter
3371 (
3372 p_attr_value IN VARCHAR2,
3373 p_delimiter IN VARCHAR2
3374 )
3375 RETURN NUMBER
3376 IS
3377 return_value NUMBER := NULL;
3378 temp_string VARCHAR2(3000);
3379 l_attr_value varchar2(2000);
3380 BEGIN
3381 -- The two strings are the same.
3382
3383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3384 debug('in get_no_of_delimiter *******************');
3385 --since fnd_msg_pub supports debiug message of length 1972
3386 -- we are passing split of attribute value as it may exceed 2000 length
3387 l_attr_value := p_attr_value;
3388 while (l_attr_value is not null) loop
3389 debug('Attr Value(Multi line printed): '||substr( l_attr_value, 1, 1800 ));
3390 l_attr_value := substr( l_attr_value, 1801 );
3391 end loop;
3392
3393 debug('Delimiter '||p_delimiter);
3394 END IF;
3395
3396
3397 IF p_attr_value = p_delimiter
3398 THEN
3399 return_value := 1;
3400 ELSE
3401 temp_string := REPLACE (p_attr_value, p_delimiter);
3402
3403 IF temp_string IS NULL
3404 THEN
3405 return_value := LENGTH (p_attr_value) / LENGTH (p_delimiter);
3406 ELSE
3407 return_value := (LENGTH (p_attr_value) - LENGTH (temp_string))/ LENGTH (p_delimiter);
3408 END IF;
3409 END IF;
3410 RETURN return_value;
3411 END;
3412
3413 -- ====================================== End of get_no_of_delimiter ==========================================
3414
3415 -- =============================================================================================================
3416 -- Tokenize will break up a string seperated with delimiter into different entries and will insert into
3417 -- PL/SQL table
3418
3419 -- Eg :
3420 -- p_attr_value is 1000+++2000 and delimiter is +++
3421 -- then it will be inserted as 1000, 2000 as two seperate entries into PL/SQL table
3422 -- ===========================================================================================================
3423
3424
3425 PROCEDURE tokenize
3426 (
3427 p_attr_value IN VARCHAR2,
3428 p_delimiter IN VARCHAR2,
3429 p_attr_value_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_4000
3430 )
3431 IS
3432
3433 l_token VARCHAR2(4000);
3434 l_ctr PLS_INTEGER := 1;
3435 l_delm_leng NUMBER;
3436
3437
3438 BEGIN
3439 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3440 debug('p_attr_value '||p_attr_value);
3441 debug('p_delimiter '||p_delimiter);
3442 END IF;
3443
3444 l_delm_leng := length(p_delimiter);
3445
3446 p_attr_value_tbl := JTF_VARCHAR2_TABLE_4000();
3447
3448 FOR i IN 1..LENGTH(p_attr_value)
3449 LOOP
3450
3451 IF SUBSTR(p_attr_value,i,l_delm_leng) = p_delimiter THEN
3452
3453
3454 p_attr_value_tbl.extend;
3455 p_attr_value_tbl(l_ctr) := l_token;
3456 l_ctr := l_ctr + 1;
3457 l_token := NULL;
3458
3459 ELSIF i = LENGTH(p_attr_value) THEN
3460
3461
3462 p_attr_value_tbl.extend;
3463 l_token := l_token || SUBSTR(p_attr_value,i,1);
3464
3465 p_attr_value_tbl(l_ctr) := l_token;
3466 l_ctr := l_ctr + 1;
3467 l_token := NULL;
3468
3469 ELSE
3470
3471 l_token := l_token || SUBSTR(p_attr_value,i,1);
3472
3473 IF p_delimiter LIKE l_token ||'%' THEN
3474 l_token := null;
3475 END IF;
3476
3477 END IF;
3478
3479 end loop;
3480
3481
3482 for i in 1 .. p_attr_value_tbl.count
3483 loop
3484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3485 debug('tokens '|| p_attr_value_tbl(i));
3486 END IF;
3487 end loop;
3488
3489 END;
3490
3491
3492 -- ====================================== End of tokenize ==========================================
3493
3494 end PV_MATCH_V2_PUB;