[Home] [Help]
PACKAGE BODY: APPS.ARW_SEARCH_CUSTOMERS
Source
1 PACKAGE BODY ARW_SEARCH_CUSTOMERS AS
2 /*$Header: ARWCUSRB.pls 120.14.12010000.3 2009/01/08 06:57:29 avepati ship $*/
3 --
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARW_SEARCH_CUSTOMERS';
8
9 -- bugfix 2235673 - setting query limit to 200
10 MAX_BUFFERED_ROWS BINARY_INTEGER := NVL(FND_PROFILE.value('VO_MAX_FETCH_SIZE'), 200);
11 --
12 -- Cursor to get all site records for given customer
13 --
14 CURSOR cust_cur (p_customer_id VARCHAR2) IS
15 SELECT
16 cus.customer_id,
17 cus.DETAILS_LEVEL,
18 cus.CUSTOMER_NUMBER,
19 substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
20 cus.ADDRESS_ID,
21 cus.CONCATENATED_ADDRESS,
22 cus.CONTACT_NAME,
23 cus.CONTACT_PHONE,
24 cus.BILL_TO_SITE_USE_ID,
25 ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
26 cus.ORG_ID,
27 'N' selected,
28 ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
29 FROM
30 ari_customer_search_v cus
31 WHERE
32 cus.customer_id = p_customer_id
33 ORDER BY DETAILS_LEVEL DESC, cus.address_id;
34
35 /* <Temporary fix for bug number 2235656> */
36 CURSOR cust_cur_trx (p_search_criteria VARCHAR2) IS
37 SELECT DISTINCT
38 cus.cust_account_id customer_id,
39 'CUST' DETAILS_LEVEL,
40 cus.ACCOUNT_NUMBER customer_number,
41 substrb(party.party_name, 1, 50) CUSTOMER_NAME,
42 -1 address_id,
43 'ALL_LOCATIONS' CONCATENATED_ADDRESS,
44 ari_utilities.get_contact(cus.cust_account_id, null, 'ALL') CONTACT_NAME,
45 ari_utilities.get_phone(cus.cust_account_id, null, 'ALL','GEN') CONTACT_PHONE,
46 -1 BILL_TO_SITE_USE_ID,
47 NULL SITE_USES,
48 cus.org_id,
49 'N' selected,
50 '' location
51 FROM
52 hz_cust_accounts cus,
53 hz_parties party,
54 ra_customer_trx ct
55 WHERE
56 ct.trx_number = p_search_criteria
57 and ct.bill_to_customer_id = cus.cust_account_id
58 and party.party_id = cus.party_id;
59
60 /* <Added against Bug# 5877217 Search By Customer Name and Customer Number>*/
61 CURSOR cust_cur_by_name_number (p_customer_name_number VARCHAR2) IS
62 SELECT
63 cus.customer_id,
64 cus.DETAILS_LEVEL,
65 cus.CUSTOMER_NUMBER,
66 substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
67 cus.ADDRESS_ID,
68 cus.CONCATENATED_ADDRESS,
69 cus.CONTACT_NAME,
70 cus.CONTACT_PHONE,
71 cus.BILL_TO_SITE_USE_ID,
72 ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
73 cus.ORG_ID,
74 'N' selected,
75 ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
76 FROM
77 ari_customer_search_v cus
78 WHERE
79 cus.CUSTOMER_NUMBER like p_customer_name_number
80 UNION
81 SELECT
82 cus.customer_id,
83 cus.DETAILS_LEVEL,
84 cus.CUSTOMER_NUMBER,
85 substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
86 cus.ADDRESS_ID,
87 cus.CONCATENATED_ADDRESS,
88 cus.CONTACT_NAME,
89 cus.CONTACT_PHONE,
90 cus.BILL_TO_SITE_USE_ID,
91 ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
92 cus.ORG_ID,
93 'N' selected,
94 ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
95 FROM
96 ari_customer_search_v cus
97 WHERE
98 cus.customer_name like p_customer_name_number
99 ORDER BY 2 DESC, 5;
100
101 /* </Temporary fix for bug number 2235656> */
102
103 --
104 -- Get ALL matching addresses returned by the context search
105 --
106 -- srajasek 06-APR-00 Changed ra_addresses_all to hz_cust_acct_sites_all because of tca changes in 11.5.1
107
108 -- modified for tca uptake. replaced ra_customers with hz_cust_accounts and
109 -- hz_parties.
110
111 -- Bug 2094233
112 -- krmenon 28 Dec 2001 Removed the order by clause for performance issues
113 /*--
114 -- krmenon 07 Jan 2002 Changed the Cursor to remove extra joins and reintroduce
115 -- the order by score since this is necessary to get the high score results
116 CURSOR ctx_cur (p_keyword VARCHAR2) IS
117 SELECT adr.cust_acct_site_id address_id, adr.cust_account_id customer_id, score(1) total_score
118 FROM hz_cust_acct_sites_all adr,
119 hz_cust_accounts cus,
120 hz_parties party,
121 ar_system_parameters_all sys
122 WHERE ctxsys.CONTAINS (address_text, NVL(p_keyword, '%') , 1) > 0
123 AND adr.cust_account_id = cus.cust_account_id
124 AND cus.party_id = party.party_id
125 AND adr.org_id = sys.org_id ;
126 ORDER BY score(1) desc, cus.cust_account_id,
127 party.party_name, cus.account_number, adr.cust_acct_site_id;
128 --*/
129 /* Bug2288089: Removed _all for hz_cust_acct_sites */
130 CURSOR ctx_cur (p_keyword VARCHAR2) IS
131 SELECT adr.cust_acct_site_id address_id, adr.cust_account_id customer_id, score(1) total_score
132 FROM hz_cust_acct_sites adr
133 WHERE ctxsys.CONTAINS (address_text, NVL(p_keyword, '%') , 1) > 0
134 ORDER BY score(1) desc;
135
136 l_cust_tab cust_tab;
137 l_rev_cust_tab rev_cust_tab;
138 --
139 l_addr_tab addr_tab;
140 --
141 l_curr_ix BINARY_INTEGER;
142 l_end_ix BINARY_INTEGER;
143 --
144 /***
145 -- Load the scores in the PL/SQL tables
146 ***/
147
148 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
149
150 PROCEDURE load_scores (p_keyword IN VARCHAR2 ) IS
151 l_cus_seq_num BINARY_INTEGER:= 1;
152 l_keyword VARCHAR2(100) DEFAULT '%';
153 BEGIN
154 l_cust_tab.DELETE;
155 l_rev_cust_tab.DELETE;
156 l_addr_tab.DELETE;
157
158 IF ( p_keyword IS NOT NULL ) THEN
159 l_keyword := p_keyword;
160 END IF;
161
162 FOR ctx_rec IN ctx_cur (l_keyword) LOOP
163 --
164 IF NOT l_cust_tab.EXISTS(ctx_rec.customer_id) THEN
165 l_cust_tab(ctx_rec.customer_id).cus_seq_num := l_cus_seq_num;
166 l_cust_tab(ctx_rec.customer_id).addr_cnt := 1;
167 l_rev_cust_tab(l_cus_seq_num).customer_id := ctx_rec.customer_id;
168 l_cus_seq_num := l_cus_seq_num + 1;
169 ELSE
170 l_cust_tab(ctx_rec.customer_id).addr_cnt := l_cust_tab(ctx_rec.customer_id).addr_cnt + 1;
171 END IF;
172 --
173 l_rev_cust_tab(l_cust_tab(ctx_rec.customer_id).cus_seq_num).addr_cnt := l_cust_tab(ctx_rec.customer_id).addr_cnt;
174 --
175 l_addr_tab (ctx_rec.address_id).customer_id := ctx_rec.customer_id;
176 l_addr_tab (ctx_rec.address_id).total_score := ctx_rec.total_score;
177 l_addr_tab (ctx_rec.address_id).addr_seq_num := l_cust_tab(ctx_rec.customer_id).addr_cnt;
178 --
179 END LOOP;
180
181 END load_scores;
182
183
184 /* Bug2202580: Added these functions to avoid interMedia parse errors.
185 These are borrowed from FND_IMUTL pkg and modified to suit irec . */
186 FUNCTION process_reserve_char(p_search_token IN VARCHAR2) RETURN VARCHAR2 IS
187
188 BEGIN
189
190 RETURN(
191 REPLACE(
192 REPLACE(
193 REPLACE(
194 REPLACE(
195 REPLACE(
196 REPLACE(
197 REPLACE(
198 REPLACE(
199 REPLACE(
200 REPLACE(
201 REPLACE(
202 REPLACE(p_search_token,
203 '\','\\'),
204 '&','\&'),
205 '{','\{'),
206 '}','\}'),
207 '[','\['),
208 ']','\]'),
209 ';','\;'),
210 '|','\|'),
211 '$','\$'),
212 '!','\!'),
213 '=','\='),
214 '>','\>'));
215
216 END process_reserve_char;
217
218 FUNCTION process_reserve_word(p_search_token IN VARCHAR2) RETURN VARCHAR2 IS
219 l_search_token varchar2(100) ;
220 BEGIN
221 l_search_token := UPPER(p_search_token);
222
223 IF (l_search_token = 'ACCUM')THEN
224 RETURN ( '{'||p_search_token||'}');
225 ELSIF (l_search_token = 'BT') THEN
226 RETURN ( '{'||p_search_token||'}');
227 ELSIF (l_search_token = 'BTG') THEN
228 RETURN ( '{'||p_search_token||'}');
229 ELSIF (l_search_token = 'BTI') THEN
230 RETURN ( '{'||p_search_token||'}');
231 ELSIF (l_search_token = 'BTP') THEN
232 RETURN ( '{'||p_search_token||'}');
233 ELSIF (l_search_token = 'MINUS') THEN
234 RETURN ( '{'||p_search_token||'}');
235 ELSIF (l_search_token = 'NEAR') THEN
236 RETURN ( '{'||p_search_token||'}');
237 ELSIF (l_search_token = 'NOT') THEN
238 RETURN ( '{'||p_search_token||'}');
239 ELSIF (l_search_token = 'NT') THEN
240 RETURN ( '{'||p_search_token||'}');
241 ELSIF (l_search_token = 'NTG') THEN
242 RETURN ( '{'||p_search_token||'}');
243 ELSIF (l_search_token = 'NTI') THEN
244 RETURN ( '{'||p_search_token||'}');
245 ELSIF (l_search_token = 'NTP') THEN
246 RETURN ( '{'||p_search_token||'}');
247 ELSIF (l_search_token = 'PT') THEN
248 RETURN ( '{'||p_search_token||'}');
249 ELSIF (l_search_token = 'SQE') THEN
250 RETURN ( '{'||p_search_token||'}');
251 ELSIF (l_search_token = 'SYN') THEN
252 RETURN ( '{'||p_search_token||'}');
253 ELSIF (l_search_token = 'TR') THEN
254 RETURN ( '{'||p_search_token||'}');
255 ELSIF (l_search_token = 'TRSYN') THEN
256 RETURN ( '{'||p_search_token||'}');
257 ELSIF (l_search_token = 'TT') THEN
258 RETURN ( '{'||p_search_token||'}');
259 ELSIF (l_search_token = 'WITHIN') THEN
260 RETURN ( '{'||p_search_token||'}');
261 ELSE
262 RETURN (p_search_token);
263 END IF;
264
265 END process_reserve_word;
266
267 FUNCTION parse_search_string(p_search_string in varchar2 ) RETURN VARCHAR2 IS
268
269 TYPE tokens is table of varchar2(256) index by binary_integer;
270 string_token tokens;
271
272 l_search_string varchar2(256):= '';
273 l_str_token varchar2(256):= '';
274 l_new_search_string varchar2(256):= '';
275 j number :=0;
276 i number :=0;
277 space number :=0;
278 l_last_char varchar2(256):= '';
279 l_last_char_new varchar2(256):= '';
280
281 BEGIN
282 l_search_string := rtrim(p_search_string, ' ');
283 l_search_string := ltrim(l_search_string, ' ');
284
285 if (l_search_string is NULL) then
286 return null;
287 end if;
288
289 l_search_string := l_search_string || ' @@'; -- identifies final token --
290 l_search_string := replace(l_search_string,'*','%'); -- translate wildcard symbols --
291
292 IF (PG_DEBUG = 'Y') THEN
293 arp_standard.debug('l_search_string : '||l_search_string);
294 END IF;
295 -----------------------------
296 -- Parse the search string --
297 -----------------------------
298 WHILE (TRUE) LOOP
299 l_search_string := ltrim(l_search_string, ' ');
300 --------------------------------
301 -- Check to see if we're done --
302 --------------------------------
303 if ( instrb(l_search_string, '@@') = 1) then
304 exit;
305 end if;
306 ---------------------------------------------------------------------
307 -- Create a list of tokens delimited by spaces .
308 --------------------------------------------------------------------
309 space := instrb(l_search_string, ' ');
310 string_token(j) := substrb(l_search_string, 1, space-1);
311 l_search_string := substrb(l_search_string, space+1);
312
313 j := j + 1;
314
315 END LOOP;
316
317 i := 0;
318 WHILE ( i < j) LOOP
319
320 l_str_token := process_reserve_word(process_reserve_char(string_token(i)));
321
322 IF (i=j-1) THEN
323 l_last_char := substrb(l_str_token,-1,1);
324 l_last_char_new := '\'||l_last_char ;
325 IF l_last_char in ('?',',','~','-') THEN
326 l_str_token := replace(l_str_token,l_last_char,l_last_char_new);
327 END IF;
328 IF UPPER(l_str_token) in ('AND', 'OR', 'ABOUT') THEN
329 l_str_token := '{' || l_str_token || '}' ;
330 END IF;
331 END IF;
332
333 l_new_search_string := l_new_search_string || ' '|| l_str_token;
334 i := i + 1;
335
336 END LOOP;
337
338 IF (PG_DEBUG = 'Y') THEN
339 arp_standard.debug('l_new_search_string: '||l_new_search_string);
340 END IF;
341
342 RETURN ltrim(l_new_search_string,' ');
343
344 END parse_search_string ;
345
346
347 /*----------------------------------------------------------------------------*
348 | PUBLIC FUNCTION |
349 | search_customers |
350 | |
351 | DESCRIPTION |
352 | This function returns the customer table which will contain the |
353 | customer records. Sorted in the following order : |
354 | - Customers containing best matches will appear at top of the |
355 | list. |
356 | - Within a customer, best address matches will appear at the top |
357 | |
358 | REQUIRES |
359 | |
360 | RETURNS |
361 | Customer Record table in pre-defined sorted order |
362 | |
363 | EXCEPTIONS RAISED |
364 | |
365 | KNOWN BUGS |
366 | <none> |
367 | |
368 | NOTES |
369 | |
370 | HISTORY |
371 | 15-APR-99 Ramakant Alat Created |
372 *----------------------------------------------------------------------------*/
373
374 FUNCTION search_customers(
375 i_keyword IN varchar2 ,
376 i_first_row IN binary_integer ,
377 i_last_row IN binary_integer
378 ) RETURN customer_tabletype IS
379
380 l_cust_ix binary_integer := 1;
381 l_cust_st_ix binary_integer := 1;
382
383 no_match_cnt binary_integer := 0;
384 addr_offset binary_integer := 0;
385 l_keyword varchar2(256) DEFAULT NULL;
386
387 l_cust_table customer_tabletype;
388
389 BEGIN
390 l_keyword := parse_search_string(i_keyword);
391
392 -- Load Scores into PL/SQL tables
393 load_scores (l_keyword);
394
395 FOR i IN 1..l_rev_cust_tab.COUNT LOOP
396 --
397 l_cust_st_ix := l_cust_ix;
398 --
399
400 /***
401 *** Get address offset = No of addr matches + 1
402 ***/
403
404 addr_offset := l_rev_cust_tab(i).addr_cnt + 1;
405 --
406 no_match_cnt := 0;
407 --
408 ----------------------------------------
409 -- **** Get all customer records ***
410 ----------------------------------------
411 --
412 FOR l_cust_rec IN cust_cur (l_rev_cust_tab(i).customer_id) LOOP
413 --
414 IF l_addr_tab.EXISTS(l_cust_rec.address_id) THEN -- Matched Address
415 l_cust_rec.selected := 'Y';
416 END IF;
417 --
421 IF l_addr_tab.EXISTS(l_cust_rec.address_id) THEN
418 IF l_cust_st_ix = l_cust_ix THEN --- Process first record ("All Locations" for more than one)
419 l_cust_table( l_cust_ix ) := l_cust_rec;
420 ELSE
422
423 -- For Matched address use address seq to copy
424
425 l_cust_table(l_cust_st_ix + l_addr_tab(l_cust_rec.address_id).addr_seq_num) := l_cust_rec;
426
427 ELSE
428
429 -- For Unmatched use offset to copy
430
431 l_cust_table( l_cust_st_ix + addr_offset + no_match_cnt) := l_cust_rec;
432 no_match_cnt := no_match_cnt + 1;
433
434 END IF;
435 END IF;
436 --
437 l_cust_ix := l_cust_ix + 1;
438 --
439 -- bugfix 2235673 : reversing bugfix 2175758
440 IF l_cust_ix > nvl(i_last_row ,MAX_BUFFERED_ROWS) THEN
441 /* bug 2175758 : remove NVL on i_last_row, because it is limiting the number
442 of customers returned for blind queries
443 IF l_cust_ix > i_last_row THEN*/
444 GOTO limited_rows;
445 END IF;
446 --
447 END LOOP;
448 --
449 END LOOP;
450
451 <<limited_rows>>
452
453 RETURN l_cust_table;
454
455 END search_customers;
456
457
458 /* <Added against Bug# 5877217 Search By Customer Name and Customer Number> */
459
460
461 FUNCTION search_by_name_num(
462 i_customer_name_number IN varchar2
463 ) RETURN customer_tabletype IS
464
465 no_match_cnt binary_integer := 0;
466 l_cust_table customer_tabletype;
467
468 BEGIN
469 ----------------------------------------
470 -- **** Get all customer records ***
471 ----------------------------------------
472 --
473 FOR l_cust_rec IN cust_cur_by_name_number (i_customer_name_number) LOOP
474 IF no_match_cnt > MAX_BUFFERED_ROWS THEN
475 RETURN l_cust_table;
476 ELSE
477 l_cust_table(no_match_cnt) := l_cust_rec;
478 no_match_cnt := no_match_cnt + 1;
479 END IF;
480 END LOOP;
481
482 RETURN l_cust_table;
483
484 END search_by_name_num;
485
486
487 /* <Temporary fix for bug number 2235656> */
488
489
490 /*----------------------------------------------------------------------------*
491 | PUBLIC FUNCTION |
492 | search_customers_by_trx |
493 | |
494 | DESCRIPTION |
495 | This function returns the customer who has an invoice number that |
496 | matches i_keyword. |
497 | |
498 | |
499 | |
500 | |
501 | REQUIRES |
502 | |
503 | RETURNS |
504 | The customer as a row in a Customer Record table |
505 | |
506 | EXCEPTIONS RAISED |
507 | |
508 | KNOWN BUGS |
509 | <none> |
510 | |
511 | NOTES |
512 | |
513 | HISTORY |
514 | 19-APR-02 Joe Albowicz Created |
515 *----------------------------------------------------------------------------*/
516
517 FUNCTION search_customers_by_trx (
518 i_keyword IN varchar2
519 ) RETURN customer_tabletype IS
520
521 l_cust_table customer_tabletype;
522 i_index binary_integer;
523 BEGIN
524
525 OPEN cust_cur_trx(i_keyword);
526
527 i_index := 1;
528
529 LOOP
530 FETCH cust_cur_trx INTO l_cust_table(i_index);
531 EXIT WHEN cust_cur_trx%NOTFOUND;
532 i_index := i_index +1;
533 END LOOP;
534
535 CLOSE cust_cur_trx;
536
537 RETURN l_cust_table;
538
539 END search_customers_by_trx;
540
541 /* </Temporary fix for bug number 2235656> */
542
543
544
545
546 /*----------------------------------------------------------------------------*
547 | PUBLIC PROCEDURE |
548 | ari_search |
552 | invokes the search_customers function for a given keyword, and inserts |
549 | |
550 | DESCRIPTION |
551 | This procedure is a wrapper for the search_customers function. It |
553 | the result data from the PL/SQL table into a global temporary table. |
554 | |
555 | REQUIRES |
556 | |
557 | RETURNS |
558 | |
559 | EXCEPTIONS RAISED |
560 | |
561 | KNOWN BUGS |
562 | <none> |
563 | |
564 | NOTES |
565 | This wrapper was necessary because the new techstack did not have any |
566 | way to exchange PL/SQL table types b/w java beans and PL/SQL procedures.|
567 | HISTORY |
568 | 15-Dec-00 Krishnakumar Menon Created |
569 | 25-Oct-04 vnb Bug 3926187 - Modified to handle |
570 | exceptions |
571 *----------------------------------------------------------------------------*/
572 PROCEDURE ari_search ( i_keyword IN varchar2,
573 i_name_num IN VARCHAR2,
574 x_status OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2 ) is
577
578 l_search_result_table customer_tabletype;
579
580 l_all_locations VARCHAR2(100) := icx_util.getPrompt(
581 p_region_application_id => 222,
582 p_region_code => 'ARW_COMMON',
583 p_attribute_application_id => 222,
584 p_attribute_code => 'ARW_TEXT_ALL_LOCATIONS'
585 );
586 l_tab_idx BINARY_INTEGER;
587 l_keyword VARCHAR2(128);
588 l_prefix CHAR;
589 l_contact_id NUMBER;
590 l_procedure_name VARCHAR2(30);
591 l_debug_info VARCHAR2(200);
592
593 BEGIN
594 l_procedure_name := '.ari_search';
595 x_msg_count := 0;
596 x_msg_data := '';
597 x_status := FND_API.G_RET_STS_ERROR;
598
599 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
600 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,
601 'Begin+');
602 end if;
603
604 --------------------------------------------------------------------------
605 l_debug_info := 'Delete all entries from the table for the current session';
606 --------------------------------------------------------------------------
607
608 IF (PG_DEBUG = 'Y') THEN
609 arp_standard.debug(l_debug_info);
610 END IF;
611
612 -- Delete all entries from the table for the current session
613 delete from ar_cust_search_gt;
614
615 l_keyword := ltrim(rtrim(i_keyword));
616 l_prefix := substrb(l_keyword,1,1);
617
618
619 --------------------------------------------------------------------------
620 l_debug_info := 'Call to the search customer by name and number function';
621 --------------------------------------------------------------------------
622 IF (PG_DEBUG = 'Y') THEN
623 arp_standard.debug(l_debug_info);
624 END IF;
625
626 if i_name_num = 'YES' then
627
628 l_search_result_table := arw_search_customers.search_by_name_num(
629 i_customer_name_number => l_keyword);
630
631 else
632
633 --------------------------------------------------------------------------
634 l_debug_info := 'Call the search customer function';
635 --------------------------------------------------------------------------
636 IF (PG_DEBUG = 'Y') THEN
637 arp_standard.debug(l_debug_info);
638 END IF;
639 -- Call the search customer function
640 -- Use search_customers_by_trx if search string prefaced by '#'
641 /* <Temporary fix for bug number 2235656> */
642 if l_prefix = '#' then
643 l_keyword := substrb(l_keyword, 2, lengthb(l_keyword)-1);
644 l_search_result_table := arw_search_customers.search_customers_by_trx(i_keyword => l_keyword);
645 else
646 /* </Temporary fix for bug number 2235656> */
647 l_search_result_table := arw_search_customers.search_customers(
648 i_keyword => l_keyword);
649 end if;
650
651 end if;
652
653 -- Insert returned rows into the global temporary table.
654 -- Bug Fix 1920131 [Cannot loop sequencially since certain indexes may not be populated]
655 l_tab_idx := l_search_result_table.FIRST;
656
657
658 LOOP
659 -- Exit if there are no more records
660 EXIT WHEN l_tab_idx IS NULL;
661
662 l_contact_id := ari_utilities.get_contact_id(l_search_result_table(l_tab_idx).customer_id,l_search_result_table(l_tab_idx).address_id, 'SELF_SERVICE_USER');
663
664 l_search_result_table(l_tab_idx).contact_name := ari_utilities.get_contact(l_contact_id);
665 l_search_result_table(l_tab_idx).contact_phone := ari_utilities.get_phone(l_contact_id, 'GEN');
666 l_search_result_table(l_tab_idx).site_uses := ari_utilities.get_site_uses(l_search_result_table(l_tab_idx).address_id);
667 l_tab_idx := l_search_result_table.NEXT(l_tab_idx);
668
669 END LOOP;
670 --------------------------------------------------------------------------
671 l_debug_info := 'Insert returned rows into the global temporary table';
672 --------------------------------------------------------------------------
673 IF (PG_DEBUG = 'Y') THEN
674 arp_standard.debug(l_debug_info);
675 END IF;
676
677 l_tab_idx := l_search_result_table.FIRST;
678
679 LOOP
680 BEGIN
681 -- Exit if there are no more records
682 EXIT WHEN l_tab_idx IS NULL;
683
684 INSERT INTO ar_cust_search_gt (
685 customer_id,
686 address_id,
687 bill_to_site_use_id,
688 details_level,
689 customer_number,
690 customer_name,
691 contact_name,
692 contact_phone,
693 site_uses,
694 org_id,
695 concatenated_address,
696 location
697 )
698 VALUES (
699 l_search_result_table(l_tab_idx).customer_id,
700 l_search_result_table(l_tab_idx).address_id,
701 decode(l_search_result_table(l_tab_idx).bill_to_site_use_id,-1,null,
702 l_search_result_table(l_tab_idx).bill_to_site_use_id),
703 l_search_result_table(l_tab_idx).details_level,
707 l_search_result_table(l_tab_idx).contact_phone,
704 l_search_result_table(l_tab_idx).customer_number,
705 l_search_result_table(l_tab_idx).customer_name,
706 l_search_result_table(l_tab_idx).contact_name,
708 l_search_result_table(l_tab_idx).site_uses,
709 l_search_result_table(l_tab_idx).org_id,
710 decode(l_search_result_table(l_tab_idx).address_id, -1, l_all_locations,
711 substrb(l_search_result_table(l_tab_idx).concatenated_address,1,255)),
712 l_search_result_table(l_tab_idx).location
713
714 );
715
716 l_tab_idx := l_search_result_table.NEXT(l_tab_idx);
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 BEGIN
721 x_msg_data := SQLERRM;
722 x_msg_count := x_msg_count + 1;
723 IF (PG_DEBUG = 'Y') THEN
724 arp_standard.debug('Unexpected Exception in ari_search: Loop and Insert');
725 arp_standard.debug('- Search Key: '||i_keyword );
726 arp_standard.debug('- Current Index: '||to_char(l_tab_idx));
727 arp_standard.debug(SQLERRM);
728 END IF;
729 END;
730
731 END;
732
733 END LOOP;
734
735 x_status := FND_API.G_RET_STS_SUCCESS;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 -- Handle Oracle Text errors (specific to the InterMedia index)
740 IF (SQLCODE = -20000) THEN
741 -- If the customer index has not been built...
742 IF INSTRB(SQLERRM,'DRG-10599')>0 THEN
743 FND_MESSAGE.SET_NAME ('AR','ARI_CUST_SEARCH_INDEX_ERROR');
744 x_msg_data := FND_MESSAGE.GET;
745 x_msg_count := x_msg_count + 1;
746 --If the wildcard search returns too many matches...
747 ELSIF INSTRB(SQLERRM,'DRG-51030')>0 THEN
748 FND_MESSAGE.SET_NAME ('AR','HZ_DQM_WILDCARD_ERR');
749 x_msg_data := FND_MESSAGE.GET;
750 x_msg_count := x_msg_count + 1;
751 ELSE
752 x_msg_data := SQLERRM;
753 x_msg_count := x_msg_count + 1;
754 END IF;
755 ELSE
756 x_msg_data := SQLERRM;
757 x_msg_count := x_msg_count + 1;
758 END IF;
759
760 IF (PG_DEBUG = 'Y') THEN
761 arp_standard.debug('Unexpected Exception in ARI_SEARCH');
762 arp_standard.debug('- Search Key: '||i_keyword );
763 arp_standard.debug(SQLERRM);
764 END IF;
765
766 END ari_search;
767
768 PROCEDURE initialize_account_sites ( p_custsite_rec_tbl in CustSite_tbl,
769 p_party_id in number,
770 p_session_id in number,
771 p_user_id in number ,
772 p_org_id in number ,
773 p_is_internal_user in varchar2
774 )
775 IS
776
777 l_curr_index NUMBER;
778 l_index NUMBER := 0;
779 p_customer_id NUMBER;
780 p_site_use_id NUMBER;
781
782 CURSOR FETCH_SITES_ID_CURSOR IS
783 SELECT
784 Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
785 FROM
786 hz_cust_acct_sites acct_sites,
787 hz_party_sites party_sites,
788 hz_cust_accounts Cust,
789 ar_sites_assigned_v Sites_assigned
790 WHERE Sites_assigned.party_id = p_party_id
791 AND Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
792 AND cust.cust_account_id = Sites_assigned.cust_account_id
793 AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
794 AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
795 AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id;
796
797 pragma autonomous_transaction ;
798
799 BEGIN
800 delete from ar_irec_user_acct_sites_all where (session_id=p_session_id
801 or trunc(CREATION_DATE)<=trunc(sysdate-2));
802
803 l_curr_index := p_custsite_rec_tbl.first;
804
805 -- bug #5858769
806 -- iterating for each customerid and siteid pair and populating the table.
807
808 WHILE (l_curr_index <= p_custsite_rec_tbl.last) LOOP
809
810 p_customer_id := p_custsite_rec_tbl(l_curr_index).CustomerId;
814 ------------------------------------------------
811 p_site_use_id := p_custsite_rec_tbl(l_curr_index).SiteUseId;
812
813
815 IF (p_is_internal_user='Y') THEN
816
817 IF ( p_site_use_id is null and p_customer_id is not null) THEN
818 FOR account_assigned_site IN (
819 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
820 )LOOP
821
822 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
823 INSERT INTO ar_irec_user_acct_sites_all
824 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
825 VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));
826 END IF;
827 END LOOP;
828
829 ELSIF (( p_site_use_id is not null ) and (p_customer_id is not null)) THEN
830 INSERT INTO ar_irec_user_acct_sites_all
831 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
832 VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));
833
834 END IF;
835 ELSE
836 IF ( p_site_use_id is null ) THEN
837
838 /* insert all the sites this party is having direct access */
839
840 FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
841 IF
842 FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
843 AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
844 THEN
845 INSERT INTO ar_irec_user_acct_sites_all
846 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
847 VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));
848 END IF;
849 END LOOP;
850
851 /* Check for account level access and insert all bill to sites */
852
853 FOR customer_assigned_record IN (
854 select cust_account_id from ar_customers_assigned_v where party_id=p_party_id AND cust_account_id=nvl(p_customer_id,cust_account_id)
855 )LOOP
856
857 FOR account_assigned_site IN (
858 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=customer_assigned_record.cust_account_id
859 )LOOP
860
861 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
862 INSERT INTO ar_irec_user_acct_sites_all
863 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
864 VALUES(p_session_id,customer_assigned_record.cust_account_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));
865 END IF;
866 END LOOP;
867 END LOOP;
868
869
870
871
872 ELSIF (( p_site_use_id is not null ) and (p_customer_id is not null)) THEN
873 INSERT INTO ar_irec_user_acct_sites_all
874 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
875 VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));
876
877 END IF;
878 END IF;
879 ------------------------------------------------
880
881 l_curr_index := p_custsite_rec_tbl.next(l_curr_index);
882
883 END LOOP;
884
885 /* REMOVE DUPLICATE ROWS IF ANY */
886 DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
887 SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
888 WHERE A.org_id = B.org_id
889 AND A.SESSION_ID=B.SESSION_ID
890 AND A.USER_ID=B.USER_ID
891 AND A.CUSTOMER_ID=B.CUSTOMER_ID
892 AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
893 AND A.CREATION_DATE=B.CREATION_DATE
894 );
895
896 commit;
897
898 END initialize_account_sites;
899
900
901 PROCEDURE init_acct_sites_anon_login ( p_customer_id in number,
902 p_site_use_id in number,
903 p_party_id in number,
904 p_session_id in number,
905 p_user_id in number ,
906 p_org_id in number ,
907 p_is_internal_user in varchar2
908 )
909 IS
910
911 l_curr_index NUMBER;
912 l_index NUMBER := 0;
913 l_org_id NUMBER;
914
915 CURSOR FETCH_SITES_ID_CURSOR IS
916 SELECT
917 Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
918 FROM
919 hz_cust_acct_sites acct_sites,
920 hz_party_sites party_sites,
921 hz_cust_accounts Cust,
922 ar_sites_assigned_v Sites_assigned
923 WHERE -- Sites_assigned.party_id = p_party_id AND
924 Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
925 AND cust.cust_account_id = Sites_assigned.cust_account_id
926 AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
927 AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
928 AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id;
929
930 pragma autonomous_transaction ;
931
932 BEGIN
933 delete from ar_irec_user_acct_sites_all where (session_id=p_session_id
934 or trunc(CREATION_DATE)<=trunc(sysdate-2));
935
936 if(p_org_id is null) then
937 l_org_id := FND_PROFILE.value('ORG_ID');
938 else
939 l_org_id := p_org_id;
940 end if;
941
942 IF (p_is_internal_user='Y') THEN
943
944 IF ( p_site_use_id is null and p_customer_id is not null) THEN
945 FOR account_assigned_site IN (
946 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
947 )LOOP
948
952 VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));
949 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
950 INSERT INTO ar_irec_user_acct_sites_all
951 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
953 END IF;
954 END LOOP;
955
956 ELSIF (( p_site_use_id is not null ) and (p_customer_id is not null)) THEN
957 INSERT INTO ar_irec_user_acct_sites_all
958 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
959 VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));
960
961 END IF;
962 ELSE
963 IF ( p_site_use_id is null ) THEN
964
965 /* insert all the sites this party is having direct access */
966
967 FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
968 IF
969 FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
970 AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
971 THEN
972 INSERT INTO ar_irec_user_acct_sites_all
973 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
974 VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));
975 END IF;
976 END LOOP;
977
978 /* Check for account level access and insert all bill to sites */
979
980 FOR customer_assigned_record IN (
981 select cust_account_id from ar_customers_assigned_v where cust_account_id=nvl(p_customer_id,cust_account_id)
982 )LOOP
983
984 FOR account_assigned_site IN (
985 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=customer_assigned_record.cust_account_id
986 )LOOP
987
988 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
989 INSERT INTO ar_irec_user_acct_sites_all
990 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
991 VALUES(p_session_id,customer_assigned_record.cust_account_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));
992 END IF;
993 END LOOP;
994 END LOOP;
995
996
997
998
999 ELSIF (( p_site_use_id is not null ) and (p_customer_id is not null)) THEN
1000 INSERT INTO ar_irec_user_acct_sites_all
1001 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
1002 VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));
1003
1004 END IF;
1005 END IF;
1006 ------------------------------------------------
1007
1008
1009 /* REMOVE DUPLICATE ROWS IF ANY */
1010 DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
1011 SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
1012 WHERE A.org_id = B.org_id
1013 AND A.SESSION_ID=B.SESSION_ID
1014 AND A.USER_ID=B.USER_ID
1015 AND A.CUSTOMER_ID=B.CUSTOMER_ID
1016 AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
1017 AND A.CREATION_DATE=B.CREATION_DATE
1018 );
1019
1020 commit;
1021 END init_acct_sites_anon_login;
1022
1023 PROCEDURE update_account_sites ( p_customer_id in number,
1024 p_session_id in number,
1025 p_user_id in number ,
1026 p_org_id in number ,
1027 p_is_internal_user in varchar2
1028 )
1029 IS
1030
1031 l_curr_index NUMBER;
1032 l_index NUMBER := 0;
1033 -- p_customer_id NUMBER;
1034 p_site_use_id NUMBER;
1035 p_party_id NUMBER;
1036
1037 CURSOR FETCH_SITES_ID_CURSOR IS
1038 SELECT
1039 Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
1040 FROM
1041 hz_cust_acct_sites acct_sites,
1042 hz_party_sites party_sites,
1043 hz_cust_accounts Cust,
1044 ar_sites_assigned_v Sites_assigned
1045 WHERE Sites_assigned.party_id = p_party_id
1046 AND Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
1047 AND cust.cust_account_id = Sites_assigned.cust_account_id
1048 AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
1049 AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
1050 AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id;
1051
1052 pragma autonomous_transaction ;
1053
1054 BEGIN
1055 delete from ar_irec_user_acct_sites_all where session_id=p_session_id AND RELATED_CUSTOMER_FLAG = 'Y';
1056
1057 select person_party_id into p_party_id from fnd_user where user_id = p_user_id;
1058
1059 IF (p_is_internal_user='Y') THEN
1060 FOR account_assigned_site IN (
1061 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
1062 )LOOP
1063 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
1064 INSERT INTO ar_irec_user_acct_sites_all
1065 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG)
1066 VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');
1067 END IF;
1068 END LOOP;
1069
1070 ELSE
1071 /* insert all the sites this party is having direct access */
1072 FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
1073 IF FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
1074 AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
1075 THEN
1076 INSERT INTO ar_irec_user_acct_sites_all
1077 (SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG )
1078 VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');
1079 END IF;
1080 END LOOP;
1081 /* Check for account level access and insert all bill to sites */
1082 FOR account_assigned_site IN (
1083 select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=p_customer_id
1084 )LOOP
1085
1086 IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
1087 INSERT INTO ar_irec_user_acct_sites_all
1088 (SESSION_ID,CUSTOMER_ID, CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG)
1089 VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');
1090 END IF;
1091 END LOOP;
1092
1093 END IF;
1094
1095
1096 /* REMOVE DUPLICATE ROWS IF ANY */
1097 DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
1098 SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
1099 WHERE A.org_id = B.org_id
1100 AND A.SESSION_ID=B.SESSION_ID
1101 AND A.USER_ID=B.USER_ID
1102 AND A.CUSTOMER_ID=B.CUSTOMER_ID
1103 AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
1104 AND A.CREATION_DATE=B.CREATION_DATE
1105 );
1106
1107 commit;
1108
1109 END update_account_sites;
1110
1111 END arw_search_customers;