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