[Home] [Help]
PACKAGE BODY: APPS.FND_IMUTL
Source
1 package body FND_IMUTL as
2 /* $Header: AFIMUTLB.pls 120.3 2006/01/09 03:05:03 skghosh ship $ */
3
4 TYPE TOKENS is table of VARCHAR2(256) index by binary_integer;
5 TYPE RESERVED_TOKENS is table of VARCHAR2(256);
6
7 SPECIAL_TOKENS RESERVED_TOKENS := RESERVED_TOKENS(
8 '(' ,
9 ')' ,
10 '&' ,
11 '|' ,
12 '+' ,
13 '-' ,
14 '~' ,
15 ' AND NOT ',
16 ' AND ',
17 ' OR '
18 );
19
20
21 -----------------------------------------------------------------------------
22 -- Parse_Search
23 -- Format search string to support more browser-like functionality
24 -----------------------------------------------------------------------------
25 procedure Parse_Search1(
26 search_string in varchar2,
27 select_clause in out nocopy varchar2,
28 and_clause in out nocopy varchar2,
29 index_col in varchar2)
30 is
31 TYPE TOKENS is table of VARCHAR2(256) index by binary_integer;
32 ft TOKENS;
33
34 syntax_err exception;
35 pragma exception_init(syntax_err, -29902);
36
37 selc VARCHAR2(2000) := ' ';
38 andc VARCHAR2(2000) := ' ';
39 orc VARCHAR2(2000) := ' ';
40 stem VARCHAR2(1) := '';
41 lang VARCHAR2(4) := userenv('LANG');
42 src VARCHAR2(256);
43 icol VARCHAR2(100) := index_col;
44 j NUMBER :=0;
45 i NUMBER :=0;
46 space NUMBER :=0;
47 quote NUMBER :=0;
48 sccnt NUMBER :=0;
49
50 begin
51 src := rtrim(search_string, ' ');
52 src := ltrim(src, ' ');
53 src := replace(src, '''', '''''');
54
55 if (src is NULL) then
56 return;
57 end if;
58
59 if (lang in ('US','F','E','D','NL')) then
60 stem := '$';
61 end if;
62
63 src := src || ' @@'; -- identifies final token --
64 --src := replace(src,'*','%'); -- translate wildcard symbols --
65
66 -----------------------------
67 -- Parse the search string --
68 -----------------------------
69 while (TRUE) loop
70 src := ltrim(src, ' ');
71 --------------------------------
72 -- Check to see if we're done --
73 --------------------------------
74 if (instr(src, '@@') = 1) then
75 exit;
76 end if;
77 -----------------------------------------------------------------
78 -- Create a list of tokens delimited by either double quotes --
79 -- or spaces. Double quotes take precedence. That is, tokens --
80 -- may contain spaces if surrounded by double quotes --
81 -----------------------------------------------------------------
82 if (instr(src, '"') = 1) then
83 src := substr(src, 2);
84 quote := instr(src, '"');
85 if (quote = 0) then
86 raise syntax_err;
87 end if;
88 ft(j) := substr(src, 1, quote-1);
89 src := substr(src, quote+1);
90 else
91 space := instr(src, ' ');
92 ft(j) := substr(src, 1, space-1);
93 src := substr(src, space+1);
94 end if;
95 j := j + 1;
96 end loop;
97
98 ---------------------------------------------
99 -- Handle any AND, OR, or AND NOT keywords --
100 ---------------------------------------------
101 while (i < j) loop
102 if ( (upper(ft(i)) = 'AND') AND (upper(ft(i+1)) <> 'NOT') ) then
103 --------------------------------------------
104 -- previous and next tokens are mandatory --
105 --------------------------------------------
106 if ( (instr(ft(i-1), '+') <> 1) AND (instr(ft(i-1), '-') <> 1) ) then
107 ft(i-1) := '+'||ft(i-1);
108 end if;
109 if ( (instr(ft(i+1), '+') <> 1) AND (instr(ft(i+1), '-') <> 1) ) then
110 ft(i+1) := '+'||ft(i+1);
111 end if;
112 elsif ( (upper(ft(i)) = 'AND') AND (upper(ft(i+1)) = 'NOT') ) then
113 ---------------------------------------------------------------
114 -- previous token is mandatory, next token must not be there --
115 ---------------------------------------------------------------
116 if ( (instr(ft(i-1), '+') <> 1) AND (instr(ft(i-1), '-') <> 1) ) then
117 ft(i-1) := '+'||ft(i-1);
118 end if;
119 if ( (instr(ft(i+2), '+') <> 1) AND (instr(ft(i+2), '-') <> 1) ) then
120 ft(i+2) := '-'||ft(i+2);
121 end if;
122 end if;
123 i := i + 1;
124 end loop;
125
126 -----------------------------------
127 -- Handle any + or - key symbols --
128 -----------------------------------
129 i := 0;
130 while (i < j) loop
131 src := ft(i);
132 i := i + 1;
133
134 if (instr(src, '-') = 1) then
135 -- word MUST NOT be there --
136 --src := substr(src, 2);
137 /* Checking for IMT reserve char and word - Phani 12/9/99 */
138 src := stem||process_imt_reserve_word(
139 process_imt_reserve_char(substr(src, 2)));
140 andc := andc||' AND NOT (contains('||icol||','''||src||''')>0)';
141 elsif (instr(src, '+') = 1) then
142 -- word MUST be there --
143 --src := substr(src, 2);
144 src := stem||process_imt_reserve_word(
145 process_imt_reserve_char(substr(src, 2)));
146 andc := andc||' AND (contains('||icol||','''||src||''','||i||')>0)';
147 selc := selc || ' + score('||i||')';
148 sccnt := sccnt + 1;
149 elsif ( (upper(src)='AND') or
150 (upper(src)='NOT') or
151 (upper(src)='OR') ) then
152 null;
153 else
154 src := process_imt_reserve_word(
155 process_imt_reserve_char(src));
156 /* End: Changes */
157 orc := orc||' OR (contains('||icol||','''||stem||src||''','||i||')>0)';
158 selc := selc || ' + score('||i||')';
159 sccnt := sccnt + 1;
160 end if;
161 end loop;
162
163 -------------------------------------------
164 -- Finish the dynamic score and or clauses --
165 -------------------------------------------
166 if (sccnt > 0) then
167 selc := 'select /*+index(lob FND_LOBS_CTX) USE_NL(lob hd)*/ round( (0 '||selc||')/'||sccnt||', 0 ) pct,';
168 else
169 selc := 'select 100 pct,'; -- should never get here --
170 end if;
171
172 if (length(orc) > 1) then
173 orc := ' AND (1=2'|| orc || ')';
174 end if;
175
176 and_clause := andc||orc;
177 select_clause := selc;
178 end Parse_Search1;
179
180 -----------------------------------------------------------------------------
181 -- process_imt_reserve_char
182 -- Appends a mask for all IMT reserve characters
183 -----------------------------------------------------------------------------
184 FUNCTION process_imt_reserve_char(p_search_token IN VARCHAR2) RETURN VARCHAR2
185 IS
186
187 BEGIN
188
189 RETURN(
190 REPLACE(
191 REPLACE(
192 REPLACE(
193 REPLACE(
194 REPLACE(
195 REPLACE(
196 REPLACE(
197 REPLACE(
198 REPLACE(
199 REPLACE(
200 REPLACE(
201 REPLACE(
202 REPLACE(
203 REPLACE(
204 REPLACE(
205 REPLACE(
206 REPLACE(p_search_token,
207 '\','\\'),
208 ',','\,'),
209 '&','\&'),
210 '(','\('),
211 '?','\?'),
212 ')','\)'),
213 '{','\{'),
214 '}','\}'),
215 '[','\['),
216 ']','\]'),
217 '-','\-'),
218 ';','\;'),
219 '~','\~'),
220 '|','\|'),
221 '$','\$'),
222 '!','\!'),
223 '>','\>'));
224
225 END process_imt_reserve_char;
226
227 -----------------------------------------------------------------------------
228 -- process_imt_reserve_word
229 -- Encloses all IMT reserve words in a set of curly braces.
230 -----------------------------------------------------------------------------
231 FUNCTION process_imt_reserve_word(p_search_token IN VARCHAR2) RETURN VARCHAR2
232 IS
233 BEGIN
234
235 -- BUG 2350209 : Instead of keep updating this list of reserved word, we will just
236 -- surround the keywords with a '{}'. For example, we should not see this "
237 -- contains(LOB.FILE_DATA,'$keyword',1)>0)" but contains(LOB.FILE_DATA,'${keyword}',1)>0"
238 -- The query will then work for both reserved and non-reserved words.
239 -- Keeping the old code for reference.
240
241
242
243 RETURN ( '{'||p_search_token||'}');
244
245 /*
246 IF (p_search_token = 'ABOUT') THEN
247 RETURN ( '{'||p_search_token||'}');
248 ELSIF (p_search_token = 'ACCUM')THEN
249 RETURN ( '{'||p_search_token||'}');
250 ELSIF (p_search_token = 'AND') THEN
251 RETURN ( '{'||p_search_token||'}');
252 ELSIF (p_search_token = 'BT') THEN
253 RETURN ( '{'||p_search_token||'}');
254 ELSIF (p_search_token = 'BTG') THEN
255 RETURN ( '{'||p_search_token||'}');
256 ELSIF (p_search_token = 'BTI') THEN
257 RETURN ( '{'||p_search_token||'}');
258 ELSIF (p_search_token = 'BTP') THEN
259 RETURN ( '{'||p_search_token||'}');
260 ELSIF (p_search_token = 'MINUS') THEN
261 RETURN ( '{'||p_search_token||'}');
262 ELSIF (p_search_token = 'NEAR') THEN
263 RETURN ( '{'||p_search_token||'}');
264 ELSIF (p_search_token = 'NOT') THEN
265 RETURN ( '{'||p_search_token||'}');
266 ELSIF (p_search_token = 'NT') THEN
267 RETURN ( '{'||p_search_token||'}');
268 ELSIF (p_search_token = 'NTG') THEN
269 RETURN ( '{'||p_search_token||'}');
270 ELSIF (p_search_token = 'NTI') THEN
271 RETURN ( '{'||p_search_token||'}');
272 ELSIF (p_search_token = 'NTP') THEN
273 RETURN ( '{'||p_search_token||'}');
274 ELSIF (p_search_token = 'OR') THEN
275 RETURN ( '{'||p_search_token||'}');
276 ELSIF (p_search_token = 'PT') THEN
277 RETURN ( '{'||p_search_token||'}');
278 ELSIF (p_search_token = 'SQE') THEN
279 RETURN ( '{'||p_search_token||'}');
280 ELSIF (p_search_token = 'SYN') THEN
281 RETURN ( '{'||p_search_token||'}');
282 ELSIF (p_search_token = 'TR') THEN
283 RETURN ( '{'||p_search_token||'}');
284 ELSIF (p_search_token = 'TRSYN') THEN
285 RETURN ( '{'||p_search_token||'}');
286 ELSIF (p_search_token = 'TT') THEN
287 RETURN ( '{'||p_search_token||'}');
288 ELSIF (p_search_token = 'WITHIN') THEN
289 RETURN ( '{'||p_search_token||'}');
290 ELSE
291 RETURN (p_search_token);
292 END IF;
293 */
294 END process_imt_reserve_word;
295
296 -----------------------------------------------------------------------------
297 -- Help_Cleanup
298 -- Purges orphaned rows from help tables
299 -----------------------------------------------------------------------------
300 PROCEDURE help_cleanup is
301 pragma autonomous_transaction;
302 begin
303 -----------------------------
304 -- delete any expired rows --
305 -----------------------------
306 fnd_gfm.purge_expired;
307
308 ------------------------------
309 -- delete any orphaned rows --
310 ------------------------------
311 delete from fnd_lobs l
312 where program_name = 'FND_HELP'
313 and not exists (select 'x' from fnd_help_documents d
314 where l.file_id = d.file_id);
315
316 delete from fnd_help_documents d
317 where not exists (select 'x' from fnd_lobs l
318 where l.file_id = d.file_id);
319
320 delete from fnd_help_targets t
321 where not exists (select 'x' from fnd_help_documents d
322 where t.file_id = d.file_id);
323 commit;
324 end help_cleanup;
325 -----------------------------------------------------------------------------
326 PROCEDURE maintain_index(p_index_name in varchar2,
327 p_callback in varchar2 default null,
328 p_app_short_name in varchar2 default 'FND',
329 p_mode in varchar2 default 'sync') is
330 own VARCHAR2(30);
331 cmd VARCHAR2(200);
332 begin
333 -- determine index schema --
334 select u.oracle_username into own
335 from fnd_product_installations inst,
336 fnd_oracle_userid u,
337 fnd_application a
338 where a.application_id = inst.application_id
339 and inst.oracle_id = u.oracle_id
340 and a.application_short_name = upper(p_app_short_name);
341
342 -- run callback if any specified --
343 if (p_callback is not null) then
344 begin
345 execute immediate 'begin '||p_callback||'(); end;';
346 exception
347 when others then
348 raise;
349
350 end;
351 end if;
352
353 /*****
354 ** Alter index is no longer recommended for InterMedia indexes.
355 ** Replacing with the ctx_ddl calls immediately following.
356
357 -- issue the appropriate alter index cmd --
358 cmd := 'alter index '||own||'.'||p_index_name||' rebuild online parameters(';
359
360 if (p_mode = 'FAST') then
361 cmd := cmd || '''optimize fast'')';
362 elsif (p_mode = 'FULL') then
363 cmd := cmd || '''optimize full maxtime 180'')';
364 else
365 cmd := cmd || '''sync'')';
366 end if;
367
368 execute immediate cmd;
369 ****/
370
371 -- Execute command using ctx_ddl
372 if (p_mode = 'FAST') then
373 ad_ctx_ddl.optimize_index(
374 idx_name => own||'.'||p_index_name,
375 optlevel => 'FAST',
376 maxtime => null,
377 token => null);
378 elsif (p_mode = 'FULL') then
379 ad_ctx_ddl.optimize_index(
380 idx_name => own||'.'||p_index_name,
381 optlevel => 'FULL',
382 maxtime => 180,
383 token => null);
384 else
385 ad_ctx_ddl.sync_index(
386 idx_name => own||'.'||p_index_name);
387 end if;
388
389 exception
390 when others then
391 execute immediate 'drop index '||own||'.'||p_index_name||' force';
392 raise;
393 end maintain_index;
394 -----------------------------------------------------------------------------
395
396 FUNCTION GET_WC_INDEX(p_search VARCHAR2, p_special_string OUT NOCOPY VARCHAR2)
397 RETURN NUMBER
398 IS
399 l_ind NUMBER;
400 l_wf_ind NUMBER;
401 l_quotebegin NUMBER;
402 l_quoteend NUMBER;
403
404 syntax_err exception;
405 pragma exception_init(syntax_err, -29902);
406 begin
407
408 l_quotebegin := instr(p_search,'"');
409 if (l_quotebegin <> 0) then
410 l_quoteend := instr(p_search,'"',l_quotebegin+1);
411
412 if (l_quoteend = 0) then
413 raise syntax_err;
414 end if;
415
416 p_special_string := substr(p_search, l_quotebegin, 1-l_quotebegin+l_quoteend);
417 return l_quotebegin;
418 end if;
419
420 l_ind := SPECIAL_TOKENS.first;
421 while (l_ind is not null)
422 loop
423 l_wf_ind := instr(upper(p_search) , SPECIAL_TOKENS(l_ind));
424 if ( l_wf_ind <> 0 )
425 then
426 p_special_string := SPECIAL_TOKENS(l_ind);
427 return l_wf_ind;
428 end if;
429 l_ind:= SPECIAL_TOKENS.next(l_ind);
430 end loop;
431
432 return l_wf_ind;
433 end get_wc_index;
434
435 procedure append_operator( ft in out nocopy tokens, count_ind NUMBER)
436 is
437 l_token varchar2(4000);
438 l_token_prev varchar2(4000);
439 begin
440 if(count_ind < 2) then
441 return;
442 end if;
443
444 l_token := substr(ft(count_ind ),1,1);
445 l_token_prev := substr(ft(count_ind - 1 ),1,1);
446
447 if( l_token_prev in ('$',')') AND l_token in ('$','(') )
448 then
449 ft(count_ind) := '&' || ft(count_ind) ;
450 end if;
451 end;
452
453
454 function replace_operator(p_spl_token varchar2)
455 return varchar2
456 is
457 TYPE operators is table of VARCHAR2(256) index by VARCHAR2(10);
458 l_op_used operators;
459 l_quoted_string VARCHAR2(4000);
460 begin
461 l_op_used('*') := '*' ;
462 l_op_used('(') := '(' ;
463 l_op_used(')') := ')' ;
464 l_op_used('&') := '&' ;
465 l_op_used('|') := '|' ;
466 l_op_used('+') := '&' ;
467 l_op_used('-') := '-' ;
468 l_op_used('~') := '~' ;
469 l_op_used(' AND NOT ') := '~' ;
470 l_op_used(' AND ') := '&' ;
471 l_op_used(' OR ') := '|' ;
472
473 if ( instr(p_spl_token,'"') = 1) then
474 l_quoted_string := '${'||substr(p_spl_token,2,length(p_spl_token)-2)||'}';
475 return l_quoted_string;
476 end if;
477
478 return l_op_used(p_spl_token);
479 end;
480
481
482
483 procedure text_filter(token in out nocopy varchar2)
484 is
485 l_length number :=0;
486 l_token varchar(4000);
487 begin
488
489 token := replace(token,'*','%');
490 token := ltrim(rtrim(token));
491
492 if(token is null) then
493 return;
494 end if;
495
496 loop
497 l_length:=instr(token,' ');
498 exit when l_length < 1;
499
500 if(instr(token,'%') = 0) then
501 l_token := l_token || '$' || process_imt_reserve_word(
502 process_imt_reserve_char(substr(token,1,l_length-1)))||' & ';
503 else
504 l_token := l_token || '$' || process_imt_reserve_char(substr(token,1,l_length-1))||' & ';
505 end if;
506 token := ltrim(rtrim(substr(token,l_length+1)));
507 end loop;
508
509 if(instr(token,'%') = 0) then
510 token := l_token || '$' || process_imt_reserve_word( process_imt_reserve_char(token)) ;
511 else
512 token := l_token || '$' || process_imt_reserve_char(token) ;
513 end if;
514 end;
515
516 procedure get_tokens(p_search varchar2, ft in out nocopy tokens, count_ind in out NUMBER)
517 is
518 l_ind NUMBER;
519 l_special_string VARCHAR2(4000);
520 l_search_string VARCHAR2(4000);
521 begin
522
523 l_search_string := p_search;
524
525 if(length(l_search_string) = 0 ) then
526 return;
527 end if;
528
529 if(l_search_string is null) then
530 return;
531 end if;
532
533 l_ind := get_wc_index(l_search_string , l_special_string);
534
535 if ( l_ind = 0 ) then
536 text_filter(l_search_string);
537 if(l_search_string is not null) then
538 ft(count_ind) := l_search_string;
539 append_operator(ft, count_ind);
540 count_ind := count_ind + 1;
541 end if;
542 return;
543 end if;
544
545 if(l_ind > 1) then
546 get_tokens(substr(l_search_string ,1,l_ind-1), ft, count_ind);
547 end if;
548
549 ft(count_ind) := replace_operator(l_special_string);
550 append_operator(ft, count_ind);
551 count_ind := count_ind + 1;
552
553 if(l_ind + length(l_special_string) -1 < length(l_search_string)) then
554 get_tokens(substr(l_search_string ,l_ind+length(l_special_string)), ft, count_ind);
555 end if;
556 end;
557
558 procedure Parse_Search(
559 search_string in varchar2,
560 select_clause in out nocopy varchar2,
561 and_clause in out nocopy varchar2,
562 index_col in varchar2)
563 is
564 ft tokens;
565 selc VARCHAR2(2000) := ' ';
566 andc VARCHAR2(2000) := ' ';
567 orc VARCHAR2(2000) := ' ';
568 stem VARCHAR2(1) := '';
569 lang VARCHAR2(4) := userenv('LANG');
570 src VARCHAR2(256);
571 icol VARCHAR2(100) := index_col;
572 contain_op VARCHAR2(4000);
573 l_ind NUMBER;
574 count_ind NUMBER := 1;
575 begin
576
577 src := rtrim(search_string, ' ');
578 src := ltrim(src, ' ');
579 src := replace(src, '''', '''''');
580
581 if (src is NULL) then
582 return;
583 end if;
584
585 if (lang in ('US','F','E','D','NL')) then
586 stem := '$';
587 end if;
588
589 get_tokens(src , ft ,count_ind);
590
591 l_ind := ft.first;
592 while (l_ind is not null)
593 loop
594 contain_op := contain_op || ft(l_ind);
595 l_ind:= ft.next(l_ind);
596 end loop;
597
598 if (contain_op is not null ) then
599 andc := 'AND contains('|| icol ||',''' || contain_op ||''',1)>0';
600 selc := 'select /*+index(lob FND_LOBS_CTX) USE_NL(lob hd)*/ score(1) pct,';
601 else
602 andc := 'AND 1=2';
603 selc := 'select 100 pct,'; -- should never get here --
604 end if;
605
606 and_clause := andc;
607 select_clause := selc;
608 end;
609 end FND_IMUTL;