[Home] [Help]
PACKAGE BODY: APPS.CS_CTX_PKG
Source
1 PACKAGE BODY CS_CTX_PKG AS
2 /* $Header: cscuctxb.pls 115.1 99/07/16 08:56:19 porting ship $ */
3
4 /***********************************************************************
5 -- Get_Query_Word
6 -- - Parse string
7 -- - Ignores stop-words.
8 ***********************************************************************/
9
10 FUNCTION Get_Query_Word(keywords VARCHAR2,
11 operator VARCHAR2,
12 stop_words VARCHAR2) RETURN VARCHAR2 IS
13 len INTEGER;
14 flag_first INTEGER;
15 remain_words VARCHAR2(2000);
16 word VARCHAR2(200);
17 q_word VARCHAR2(2000);
18 BEGIN
19
20 remain_words := keywords;
21 len := length(keywords);
22 -- Pad blank so that you can get the last word
23 remain_words := RPAD(remain_words, len+1);
24 remain_words := LTRIM(remain_words,' ');
25
26 --
27 flag_first := 0;
28
29 -- Parse string for AND / OR condition.
30
31 len := instr(remain_words, ' ');
32
33 WHILE (len > 0) LOOP
34 word := substr(remain_words, 1, len-1);
35
36 IF (operator <> 'PHRASE') THEN
37 -- If the word is a stop word then ignore it.
38 IF instr(stop_words,upper(word)) = 0 THEN
39 IF (flag_first = 0) THEN
40 q_word := '{' || word || '}';
41 ELSE
42 q_word := q_word || operator || '{' || word || '}';
43 END IF;
44 --
45 flag_first := 1;
46 --
47 END IF;
48 ELSE
49 IF (flag_first = 0) THEN
50 q_word := '{' || word || '}';
51 ELSE
52 q_word := q_word || ' {' || word || '}';
53 END IF;
54 --
55 flag_first := 1;
56 --
57 END IF;
58 remain_words := substr(remain_words,len);
59 remain_words := LTRIM(remain_words, ' ');
60 len:= instr(remain_words, ' ');
61
62 END LOOP;
63
64 RETURN q_word;
65
66 END get_query_word;
67
68
69 /***********************************************************************
70 Get_Context_Stop_Words: Returns a list of stop words.
71 ***********************************************************************/
72
73 PROCEDURE Get_Context_Stop_Words(stop_word_list OUT VARCHAR2,
74 policy1 IN VARCHAR2 default NULL,
75 policy2 IN VARCHAR2 default NULL,
76 policy3 IN VARCHAR2 default NULL,
77 policy4 IN VARCHAR2 default NULL)
78 IS
79 cursor_name INTEGER;
80 string VARCHAR2(500);
81 rows_processed INTEGER;
82 stop_word VARCHAR2(100);
83 policies VARCHAR2(2000);
84 all_stop_words VARCHAR2(2000) := NULL;
85 BEGIN
86 policies:= ''''||upper(policy1)||''', '''||upper(policy2)||''', '''||upper(policy3)||''', '''||upper(policy4)||'''' ;
87
88 string := 'Select unique pa.pat_value From ctxsys.dr$preference_attribute
89 pa, ctxsys.dr$preference pr, ctxsys.dr$policy po,
90 ctxsys.dr$preference_usage pu Where pa.pat_name = ''STOP_WORD''
91 AND pa.pat_pre_id = pu.pus_pre_id AND pu.pus_pol_id = po.pol_id
92 AND po.pol_name in (' || policies || ')' ;
93
94 cursor_name := dbms_sql.open_cursor;
95 dbms_sql.parse(cursor_name,string,dbms_sql.v7);
96 dbms_sql.define_column(cursor_name,1,stop_word,100);
97 rows_processed := dbms_sql.execute(cursor_name);
98 LOOP
99 IF dbms_sql.fetch_rows(cursor_name) >0 THEN
100 -- get column values of the row.
101 dbms_sql.column_value(cursor_name,1,stop_word);
102 all_stop_words := all_stop_words || stop_word || ' ';
103 ELSE
104 EXIT;
105 END IF;
106 END LOOP;
107 dbms_sql.close_cursor(cursor_name);
108 stop_word_list:=all_stop_words;
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF dbms_sql.is_open(cursor_name) THEN
112 dbms_sql.close_cursor(cursor_name);
113 END IF;
114 raise;
115 END; -- Get_Context_Stop_Words.
116
117
118 /***********************************************************************
119 Run_Ctx_Query:
120
121 ***********************************************************************/
122
123 PROCEDURE Run_Ctx_Query(policy1 IN VARCHAR2,
124 policy2 IN VARCHAR2,
125 policy3 IN VARCHAR2,
126 policy4 IN VARCHAR2,
127 search_string IN VARCHAR2,
128 results_table IN VARCHAR2,
129 conid1 IN NUMBER,-- unique id for policy1
130 conid2 IN NUMBER,-- unique id for policy2
131 conid3 IN NUMBER,-- unique id for policy3
132 conid4 IN NUMBER-- unique id for policy4
133 )
134 IS
135 str1 VARCHAR2(2000) := NULL ;
136 str2 VARCHAR2(2000) := NULL ;
137 str3 VARCHAR2(2000) := NULL ;
138 str4 VARCHAR2(2000) := NULL ;
139 cursor_name INTEGER;
140 rows_processed INTEGER; -- Not used currently
141 BEGIN
142
143 -- Create query strings:
144
145 IF (policy1 is not null) THEN
146 str1 := 'BEGIN CTX_QUERY.CONTAINS('''||policy1||''' ,''' || search_string
147 || ''', '''||results_table || ''', 1, ' || conid1 ||',NULL); END;';
148
149 -- The above string will really execute procedure:
150 -- execute CTX_QUERY.CONTAINS('policy1','search_string',
151 -- 'results_table', 1, condid1,NULL);
152
153 END IF;
154
155 IF (policy2 is not null) THEN
156 str2 := 'BEGIN CTX_QUERY.CONTAINS('''||policy2||''' ,''' || search_string
157 || ''', '''||results_table || ''', 1, ' || conid2 ||',NULL); END;';
158 END IF;
159
160 IF (policy3 is not null) THEN
161 str3 := 'BEGIN CTX_QUERY.CONTAINS('''||policy3||''' ,''' || search_string
162 || ''', '''||results_table || ''', 1, ' || conid3 ||',NULL); END;';
163 END IF;
164
165 IF (policy4 is not null) THEN
166 str4 := 'BEGIN CTX_QUERY.CONTAINS('''||policy4||''' ,''' || search_string
167 || ''', '''||results_table || ''', 1, ' || conid4 ||',NULL); END;';
168 END IF;
169
170
171 -- Call ctx's contains
172
173 IF str1 is NOT NULL THEN
174 cursor_name := dbms_sql.open_cursor;
175 dbms_sql.parse(cursor_name,str1,dbms_sql.v7);
176 rows_processed := dbms_sql.execute(cursor_name);
177 dbms_sql.close_cursor(cursor_name);
178 END IF;
179
180 IF str2 is NOT NULL THEN
181 cursor_name := dbms_sql.open_cursor;
182 dbms_sql.parse(cursor_name,str2,dbms_sql.v7);
183 rows_processed := dbms_sql.execute(cursor_name);
184 dbms_sql.close_cursor(cursor_name);
185 END IF;
186
187
188 IF str3 is NOT NULL THEN
189 cursor_name := dbms_sql.open_cursor;
190 dbms_sql.parse(cursor_name,str3,dbms_sql.v7);
191 rows_processed := dbms_sql.execute(cursor_name);
192 dbms_sql.close_cursor(cursor_name);
193 END IF;
194
195 IF str4 is NOT NULL THEN
196 cursor_name := dbms_sql.open_cursor;
197 dbms_sql.parse(cursor_name,str4,dbms_sql.v7);
198 rows_processed := dbms_sql.execute(cursor_name);
199 dbms_sql.close_cursor(cursor_name);
200 END IF;
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 IF instr(sqlerrm,'DRG-10308') <>0 THEN
205 IF dbms_sql.is_open(cursor_name) THEN
206 dbms_sql.close_cursor(cursor_name);
207 END IF;
208 fnd_message.set_name('CS','CS_INC_CTX_NO_SERVER');
209 app_exception.raise_exception;
210 ELSE
211 dbms_sql.close_cursor(cursor_name);
212 fnd_message.set_name('CS','CS_SR_CONTEXT_ERROR');
213 fnd_message.set_token('ERRORM',sqlerrm);
214 app_exception.raise_exception;
215 END IF;
216
217 END;
218
219
220 /***********************************************************************
221 Update_Context_Index: Reindex a policy for the given key.
222 ***********************************************************************/
223
224 PROCEDURE Update_Context_Index(policy_name IN VARCHAR2,
225 primary_key IN VARCHAR2)
226 IS
227 cursor_name INTEGER;
228 string VARCHAR2(200);
229 rows_processed INTEGER; -- Not used currently
230 BEGIN
231 string := 'BEGIN ctx_dml.reindex('''||policy_name||''','''||primary_key||'
232 ''); END;';
233 cursor_name := dbms_sql.open_cursor;
234 dbms_sql.parse(cursor_name,string,dbms_sql.v7);
235 rows_processed := dbms_sql.execute(cursor_name);
236 dbms_sql.close_cursor(cursor_name);
237 EXCEPTION
238 WHEN OTHERS THEN
239 IF dbms_sql.is_open(cursor_name) THEN
240 dbms_sql.close_cursor(cursor_name);
241 END IF;
242 raise;
243 END; -- update_context_index.
244
245
246 /***********************************************************************
247 Clean_Results_Table: Given a results table name this procedure
248 will remove the rows from the results table
249 for given CONIDs
250 NOTE: You can call this proc from KEY-EXIT and KEY-CLRFRM triggers.
251 Make sure to call forms_ddl('commit') after the call.
252 ************************************************************************/
253
254 PROCEDURE Clean_Results_Table(results_table IN VARCHAR2,
255 conid1 IN NUMBER DEFAULT 0,
256 conid2 IN NUMBER DEFAULT 0,
257 conid3 IN NUMBER DEFAULT 0,
258 conid4 IN NUMBER DEFAULT 0
259 )
260 IS
261 cursor_name INTEGER;
262 string VARCHAR2(200);
263 rows_processed INTEGER; -- Not used currently
264 BEGIN
265 cursor_name := dbms_sql.open_cursor;
266 dbms_sql.parse(cursor_name,'DELETE FROM '||results_table||' WHERE conid IN (:c1,:c2,:c3,:c4)',dbms_sql.v7);
267 dbms_sql.bind_variable(cursor_name, ':c1',conid1);
268 dbms_sql.bind_variable(cursor_name, ':c2',conid2);
269 dbms_sql.bind_variable(cursor_name, ':c3',conid3);
270 dbms_sql.bind_variable(cursor_name, ':c4',conid4);
271 rows_processed := dbms_sql.execute(cursor_name);
272 dbms_sql.close_cursor(cursor_name);
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF dbms_sql.is_open(cursor_name) THEN
276 dbms_sql.close_cursor(cursor_name);
277 END IF;
278 raise;
279
280 END;
281
282 /***********************************************************************
283 Get_Conids: Given a sequence name and no of con ids, this proc
284 will return upto 4 unique conids.
285 Note: This procedure is not working and is not being used.
286 ***********************************************************************/
287
291 conid2 OUT NUMBER,
288 PROCEDURE Get_Conids(sequence_name IN VARCHAR2,
289 no_of_conids IN NUMBER,
290 conid1 OUT NUMBER,
292 conid3 OUT NUMBER,
293 conid4 OUT NUMBER
294 )
295 IS
296 seqval VARCHAR2(100);
297 cursor_name INTEGER;
298 string VARCHAR2(200);
299 rows_processed INTEGER; -- Not used currently
300 c1 number :=0;
301
302 BEGIN
303
304 seqval := sequence_name||'.nextval';
305
306 IF no_of_conids >0 THEN
307 cursor_name := dbms_sql.open_cursor;
308 dbms_sql.parse(cursor_name,'SELECT CS_INCIDENTS_CTX_S.nextval FROM DUAL', dbms_sql.v7);
309 dbms_sql.define_column(cursor_name,1,c1);
310 -- dbms_sql.bind_variable(cursor_name, ':c1',conid1);
311 rows_processed := dbms_sql.execute_and_fetch(cursor_name);
312 dbms_sql.close_cursor(cursor_name);
313
314 conid1:=c1;
315
316 /***
317 SELECT cs_incidents_ctx_s.nextval
318 INTO conid1
319 FROM dual;
320 ****/
321 END IF;
322
323 /****
324 IF no_of_conids >1 THEN
325 SELECT cs_incidents_ctx_s.nextval
326 INTO conid2
327 FROM dual;
328 END IF;
329
330 IF no_of_conids >2 THEN
331 SELECT cs_incidents_ctx_s.nextval
332 INTO conid3
333 FROM dual;
334 END IF;
335
336 IF no_of_conids >3 THEN
337 SELECT cs_incidents_ctx_s.nextval
338 INTO conid4
339 FROM dual;
340 END IF;
341
342 ****/
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 raise;
346
347 END;
348
349
350 /***********************************************************************
351 Search:
352 ***********************************************************************/
353
354 PROCEDURE Search(policy1 in VARCHAR2,
355 policy2 in VARCHAR2,
356 policy3 in VARCHAR2,
357 policy4 in VARCHAR2,
358 stop_words in VARCHAR2,
359 search_string in VARCHAR2,
360 search_option in VARCHAR2, -- 'AND', 'OR', 'EXACT'
361 results_table in VARCHAR2,
362 conid1 in NUMBER,-- unique id for policy1
363 conid2 in NUMBER,-- unique id for policy2
364 conid3 in NUMBER,-- unique id for policy3
365 conid4 in NUMBER-- unique id for policy4
366 ) IS
367 l_search_string VARCHAR2(2000);
368 operator VARCHAR2(10);
369
370 BEGIN
371
372
373 IF (search_option in ('AND','OR','PHRASE')) THEN
374 IF (search_option = 'AND') THEN
375 operator := ' & ';
376 ELSIF (search_option = 'OR') THEN
377 operator := ' , ';
378 ELSE
379 operator := 'PHRASE';
380 END IF;
381 -- Get the parsed string.
382 l_search_string := Get_Query_Word(search_string, operator, stop_words);
383 ELSE
384 -- Send the input string as is for phrase and advanced searches.
385 l_search_string := search_string;
386 END IF;
387
388
389 -- RUN THE CONTEXT QUERY
390
391 IF l_search_string is NOT NULL THEN
392 Run_Ctx_Query(policy1,
393 policy2,
394 policy3,
395 policy4,
396 l_search_string,
397 results_table,
398 conid1,
399 conid2,
400 conid3,
401 conid4
402 );
403 END IF;
404 END Search;
405
406
407 /***********************************************************************
408 Get_Result_Table: Gets a results table for context search from the pool.
409 ***********************************************************************/
410
411 PROCEDURE Get_Result_Table(result_table OUT VARCHAR2) IS
412 str VARCHAR2(2000) := NULL ;
413 cursor_name INTEGER;
414 rows_processed INTEGER; -- Not used currently
415 BEGIN
416 str := 'BEGIN CTX_QUERY.GETTAB(CTX_QUERY.hittab,:res_tab); END;';
417
418 cursor_name := dbms_sql.open_cursor;
419 dbms_sql.parse(cursor_name,str,dbms_sql.v7);
420 dbms_sql.bind_variable(cursor_name,':res_tab',null,100);
421 rows_processed := dbms_sql.execute(cursor_name);
422 dbms_sql.variable_value(cursor_name,':res_tab',result_table);
423 dbms_sql.close_cursor(cursor_name);
424
425 END Get_Result_Table;
426
427
428 /***********************************************************************
429 Release_Result_Table: Releases the passed results table from the pool.
430 ***********************************************************************/
431
432 PROCEDURE Release_Result_Table(result_table IN VARCHAR2) IS
433 str VARCHAR2(2000) := NULL ;
434 cursor_name INTEGER;
435 rows_processed INTEGER; -- Not used currently
436 BEGIN
437 str := 'BEGIN CTX_QUERY.RELTAB('''|| result_table || '''); END;';
438
439 cursor_name := dbms_sql.open_cursor;
440 dbms_sql.parse(cursor_name,str,dbms_sql.v7);
441 rows_processed := dbms_sql.execute(cursor_name);
442 dbms_sql.close_cursor(cursor_name);
443 END Release_Result_Table;
444
445
446 END;