[Home] [Help]
PACKAGE BODY: APPS.FND_IMUTL
Source
1 package body FND_IMUTL as
2 /* $Header: AFIMUTLB.pls 120.3.12020000.2 2012/07/16 05:56:57 srinnakk 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(
207 REPLACE(
208 REPLACE(
209 REPLACE(p_search_token,
210 '\','\\'),
211 ',','\,'),
212 '&','\&'),
213 '(','\('),
214 '?','\?'),
215 ')','\)'),
216 '{','\{'),
217 '}','\}'),
218 '[','\['),
219 ']','\]'),
220 '-','\-'),
221 ';','\;'),
222 '~','\~'),
223 '|','\|'),
224 '$','\$'),
225 '!','\!'),
226 '>','\>'),
227 '_','\_'),
228 '*','\*'),
229 '%','\%'));
230
231 END process_imt_reserve_char;
232
233 -----------------------------------------------------------------------------
234 -- process_imt_reserve_word
235 -- Encloses all IMT reserve words in a set of curly braces.
236 -----------------------------------------------------------------------------
237 FUNCTION process_imt_reserve_word(p_search_token IN VARCHAR2) RETURN VARCHAR2
238 IS
239 BEGIN
240
241 -- BUG 2350209 : Instead of keep updating this list of reserved word, we will just
242 -- surround the keywords with a '{}'. For example, we should not see this "
243 -- contains(LOB.FILE_DATA,'$keyword',1)>0)" but contains(LOB.FILE_DATA,'${keyword}',1)>0"
244 -- The query will then work for both reserved and non-reserved words.
245 -- Keeping the old code for reference.
246
247
248
249 RETURN ( '{'||p_search_token||'}');
250
251 /*
252 IF (p_search_token = 'ABOUT') THEN
253 RETURN ( '{'||p_search_token||'}');
254 ELSIF (p_search_token = 'ACCUM')THEN
255 RETURN ( '{'||p_search_token||'}');
256 ELSIF (p_search_token = 'AND') THEN
257 RETURN ( '{'||p_search_token||'}');
258 ELSIF (p_search_token = 'BT') THEN
259 RETURN ( '{'||p_search_token||'}');
260 ELSIF (p_search_token = 'BTG') THEN
261 RETURN ( '{'||p_search_token||'}');
262 ELSIF (p_search_token = 'BTI') THEN
263 RETURN ( '{'||p_search_token||'}');
264 ELSIF (p_search_token = 'BTP') THEN
265 RETURN ( '{'||p_search_token||'}');
266 ELSIF (p_search_token = 'MINUS') THEN
267 RETURN ( '{'||p_search_token||'}');
268 ELSIF (p_search_token = 'NEAR') THEN
269 RETURN ( '{'||p_search_token||'}');
270 ELSIF (p_search_token = 'NOT') THEN
271 RETURN ( '{'||p_search_token||'}');
272 ELSIF (p_search_token = 'NT') THEN
273 RETURN ( '{'||p_search_token||'}');
274 ELSIF (p_search_token = 'NTG') THEN
275 RETURN ( '{'||p_search_token||'}');
276 ELSIF (p_search_token = 'NTI') THEN
277 RETURN ( '{'||p_search_token||'}');
278 ELSIF (p_search_token = 'NTP') THEN
279 RETURN ( '{'||p_search_token||'}');
280 ELSIF (p_search_token = 'OR') THEN
281 RETURN ( '{'||p_search_token||'}');
282 ELSIF (p_search_token = 'PT') THEN
283 RETURN ( '{'||p_search_token||'}');
284 ELSIF (p_search_token = 'SQE') THEN
285 RETURN ( '{'||p_search_token||'}');
286 ELSIF (p_search_token = 'SYN') THEN
287 RETURN ( '{'||p_search_token||'}');
288 ELSIF (p_search_token = 'TR') THEN
289 RETURN ( '{'||p_search_token||'}');
290 ELSIF (p_search_token = 'TRSYN') THEN
291 RETURN ( '{'||p_search_token||'}');
292 ELSIF (p_search_token = 'TT') THEN
293 RETURN ( '{'||p_search_token||'}');
294 ELSIF (p_search_token = 'WITHIN') THEN
295 RETURN ( '{'||p_search_token||'}');
296 ELSE
297 RETURN (p_search_token);
298 END IF;
299 */
300 END process_imt_reserve_word;
301
302 -----------------------------------------------------------------------------
303 -- Help_Cleanup
304 -- Purges orphaned rows from help tables
305 -----------------------------------------------------------------------------
306 PROCEDURE help_cleanup is
307 pragma autonomous_transaction;
308 begin
309 -----------------------------
310 -- delete any expired rows --
311 -----------------------------
312 fnd_gfm.purge_expired;
313
314 ------------------------------
315 -- delete any orphaned rows --
316 ------------------------------
317 delete from fnd_lobs l
318 where program_name = 'FND_HELP'
319 and not exists (select 'x' from fnd_help_documents d
320 where l.file_id = d.file_id);
321
322 delete from fnd_help_documents d
323 where not exists (select 'x' from fnd_lobs l
324 where l.file_id = d.file_id);
325
326 delete from fnd_help_targets t
327 where not exists (select 'x' from fnd_help_documents d
328 where t.file_id = d.file_id);
329 commit;
330 end help_cleanup;
331 -----------------------------------------------------------------------------
332 PROCEDURE maintain_index(p_index_name in varchar2,
333 p_callback in varchar2 default null,
334 p_app_short_name in varchar2 default 'FND',
335 p_mode in varchar2 default 'sync') is
336 own VARCHAR2(30);
337 cmd VARCHAR2(200);
338 begin
339 -- determine index schema --
340 select u.oracle_username into own
341 from fnd_product_installations inst,
342 fnd_oracle_userid u,
343 fnd_application a
344 where a.application_id = inst.application_id
345 and inst.oracle_id = u.oracle_id
346 and a.application_short_name = upper(p_app_short_name);
347
348 -- run callback if any specified --
349 if (p_callback is not null) then
350 begin
351 execute immediate 'begin '||p_callback||'(); end;';
352 exception
353 when others then
354 raise;
355
356 end;
357 end if;
358
359 /*****
360 ** Alter index is no longer recommended for InterMedia indexes.
361 ** Replacing with the ctx_ddl calls immediately following.
362
363 -- issue the appropriate alter index cmd --
364 cmd := 'alter index '||own||'.'||p_index_name||' rebuild online parameters(';
365
366 if (p_mode = 'FAST') then
367 cmd := cmd || '''optimize fast'')';
368 elsif (p_mode = 'FULL') then
369 cmd := cmd || '''optimize full maxtime 180'')';
370 else
371 cmd := cmd || '''sync'')';
372 end if;
373
374 execute immediate cmd;
375 ****/
376
377 -- Execute command using ctx_ddl
378 if (p_mode = 'FAST') then
379 ad_ctx_ddl.optimize_index(
380 idx_name => own||'.'||p_index_name,
381 optlevel => 'FAST',
382 maxtime => null,
383 token => null);
384 elsif (p_mode = 'FULL') then
385 ad_ctx_ddl.optimize_index(
386 idx_name => own||'.'||p_index_name,
387 optlevel => 'FULL',
388 maxtime => 180,
389 token => null);
390 else
391 ad_ctx_ddl.sync_index(
392 idx_name => own||'.'||p_index_name);
393 end if;
394
395 exception
396 when others then
397 execute immediate 'drop index '||own||'.'||p_index_name||' force';
398 raise;
399 end maintain_index;
400 -----------------------------------------------------------------------------
401
402 FUNCTION GET_WC_INDEX(p_search VARCHAR2, p_special_string OUT NOCOPY VARCHAR2)
403 RETURN NUMBER
404 IS
405 l_ind NUMBER;
406 l_wf_ind NUMBER;
407 l_quotebegin NUMBER;
408 l_quoteend NUMBER;
409
410 syntax_err exception;
411 pragma exception_init(syntax_err, -29902);
412 begin
413
414 l_quotebegin := instr(p_search,'"');
415 if (l_quotebegin <> 0) then
416 l_quoteend := instr(p_search,'"',l_quotebegin+1);
417
418 if (l_quoteend = 0) then
419 raise syntax_err;
420 end if;
421
422 p_special_string := substr(p_search, l_quotebegin, 1-l_quotebegin+l_quoteend);
423 return l_quotebegin;
424 end if;
425
426 l_ind := SPECIAL_TOKENS.first;
427 while (l_ind is not null)
428 loop
429 l_wf_ind := instr(upper(p_search) , SPECIAL_TOKENS(l_ind));
430 if ( l_wf_ind <> 0 )
431 then
432 p_special_string := SPECIAL_TOKENS(l_ind);
433 return l_wf_ind;
434 end if;
435 l_ind:= SPECIAL_TOKENS.next(l_ind);
436 end loop;
437
438 return l_wf_ind;
439 end get_wc_index;
440
441 procedure append_operator( ft in out nocopy tokens, count_ind NUMBER)
442 is
443 l_token varchar2(4000);
444 l_token_prev varchar2(4000);
445 begin
446 if(count_ind < 2) then
447 return;
448 end if;
449
450 l_token := substr(ft(count_ind ),1,1);
451 l_token_prev := substr(ft(count_ind - 1 ),1,1);
452
453 if( l_token_prev in ('$',')') AND l_token in ('$','(') )
454 then
455 ft(count_ind) := '&' || ft(count_ind) ;
456 end if;
457 end;
458
459
460 function replace_operator(p_spl_token varchar2)
461 return varchar2
462 is
463 TYPE operators is table of VARCHAR2(256) index by VARCHAR2(10);
464 l_op_used operators;
465 l_quoted_string VARCHAR2(4000);
466 begin
467 l_op_used('*') := '*' ;
468 l_op_used('(') := '(' ;
469 l_op_used(')') := ')' ;
470 l_op_used('&') := '&' ;
471 l_op_used('|') := '|' ;
472 l_op_used('+') := '&' ;
473 l_op_used('-') := '-' ;
474 l_op_used('~') := '~' ;
475 l_op_used(' AND NOT ') := '~' ;
476 l_op_used(' AND ') := '&' ;
477 l_op_used(' OR ') := '|' ;
478
479 if ( instr(p_spl_token,'"') = 1) then
480 l_quoted_string := '${'||substr(p_spl_token,2,length(p_spl_token)-2)||'}';
481 return l_quoted_string;
482 end if;
483
484 return l_op_used(p_spl_token);
485 end;
486
487
488
489 procedure text_filter(token in out nocopy varchar2)
490 is
491 l_length number :=0;
492 l_token varchar(4000);
493 begin
494
495 token := replace(token,'*','%');
496 token := ltrim(rtrim(token));
497
498 if(token is null) then
499 return;
500 end if;
501
502 loop
503 l_length:=instr(token,' ');
504 exit when l_length < 1;
505
506 if(instr(token,'%') = 0) then
507 l_token := l_token || '$' || process_imt_reserve_word(
508 process_imt_reserve_char(substr(token,1,l_length-1)))||' & ';
509 else
510 l_token := l_token || '$' || process_imt_reserve_char(substr(token,1,l_length-1))||' & ';
511 end if;
512 token := ltrim(rtrim(substr(token,l_length+1)));
513 end loop;
514
515 if(instr(token,'%') = 0) then
516 token := l_token || '$' || process_imt_reserve_word( process_imt_reserve_char(token)) ;
517 else
518 token := l_token || '$' || process_imt_reserve_char(token) ;
519 end if;
520 end;
521
522 procedure get_tokens(p_search varchar2, ft in out nocopy tokens, count_ind in out NUMBER)
523 is
524 l_ind NUMBER;
525 l_special_string VARCHAR2(4000);
526 l_search_string VARCHAR2(4000);
527 begin
528
529 l_search_string := p_search;
530
531 if(length(l_search_string) = 0 ) then
532 return;
533 end if;
534
535 if(l_search_string is null) then
536 return;
537 end if;
538
539 l_ind := get_wc_index(l_search_string , l_special_string);
540
541 if ( l_ind = 0 ) then
542 text_filter(l_search_string);
543 if(l_search_string is not null) then
544 ft(count_ind) := l_search_string;
545 append_operator(ft, count_ind);
546 count_ind := count_ind + 1;
547 end if;
548 return;
549 end if;
550
551 if(l_ind > 1) then
552 get_tokens(substr(l_search_string ,1,l_ind-1), ft, count_ind);
553 end if;
554
555 ft(count_ind) := replace_operator(l_special_string);
556 append_operator(ft, count_ind);
557 count_ind := count_ind + 1;
558
559 if(l_ind + length(l_special_string) -1 < length(l_search_string)) then
560 get_tokens(substr(l_search_string ,l_ind+length(l_special_string)), ft, count_ind);
561 end if;
562 end;
563
564 procedure Parse_Search(
565 search_string in varchar2,
566 select_clause in out nocopy varchar2,
567 and_clause in out nocopy varchar2,
568 index_col in varchar2)
569 is
570 ft tokens;
571 selc VARCHAR2(2000) := ' ';
572 andc VARCHAR2(2000) := ' ';
573 orc VARCHAR2(2000) := ' ';
574 stem VARCHAR2(1) := '';
575 lang VARCHAR2(4) := userenv('LANG');
576 src VARCHAR2(256);
577 icol VARCHAR2(100) := index_col;
578 contain_op VARCHAR2(4000);
579 l_ind NUMBER;
580 count_ind NUMBER := 1;
581 begin
582
583 src := rtrim(search_string, ' ');
584 src := ltrim(src, ' ');
585 src := replace(src, '''', '''''');
586
587 if (src is NULL) then
588 return;
589 end if;
590
591 if (lang in ('US','F','E','D','NL')) then
592 stem := '$';
593 end if;
594
595 get_tokens(src , ft ,count_ind);
596
597 l_ind := ft.first;
598 while (l_ind is not null)
599 loop
600 contain_op := contain_op || ft(l_ind);
601 l_ind:= ft.next(l_ind);
602 end loop;
603
604 if (contain_op is not null ) then
605 andc := 'AND contains('|| icol ||',''' || contain_op ||''',1)>0';
606 selc := 'select /*+index(lob FND_LOBS_CTX) USE_NL(lob hd)*/ score(1) pct,';
607 else
608 andc := 'AND 1=2';
609 selc := 'select 100 pct,'; -- should never get here --
610 end if;
611
612 and_clause := andc;
613 select_clause := selc;
614 end;
615 end FND_IMUTL;