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