[Home] [Help]
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,
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
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:
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: --
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:
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;
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:
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 . */
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: --
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;