[Home] [Help]
PACKAGE BODY: APPS.IBE_SEARCH_SETUP_PVT
Source
1 PACKAGE BODY ibe_search_setup_pvt AS
2 /* $Header: IBEVCSIB.pls 120.5 2008/04/16 08:41:57 mgiridha ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | File name |
9 | IBEVCSIB.pls |
10 | Body file for the iStore Search Insert Concurrent Program |
11 | Modification is not recommended. |
12 | |
13 | Description |
14 | |
15 | |
16 | SYTONG - bug fix 2550147 -- change the query to check if index exists |
17 | SYTONG - bug fix 2926852 -- use bulk fetch and insert |
18 | abhandar - bug fix 3168087 -catalog search performance |
19 | madesai - bug fix 3871664 - GSCC warning fix - remove apps reference |
20 | madesai - bug fix 4585787 - Remove KOREAN_LEXER for 10Gr12 |
21 | madesai - bug fix 4674288 -explicitly remove korean lobs lexer |
22 | mgiridha - bug 6924793 - changes to FP 11510 bug 6777665 |
23 |___________________________________________________________________________|*/
24
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ibe_search_setup_pvt';
26 G_FETCH_LIMIT CONSTANT NUMBER := 1000;
27
28 -- for the test harness
29 G_TEST_FLAG_ON boolean := false;
30 G_TEST_SEARCH_WEB_STATUS VARCHAR2(30) := null;
31 G_TEST_SEARCH_CATEGORY_SET VARCHAR2(30) := null;
32
33
34
35 FUNCTION WriteToLob(param1 IN VARCHAR2,
36 param2 IN VARCHAR2,
37 param3 IN VARCHAR2)
38 RETURN CLOB IS
39
40 lob_loc CLOB;
41 buffer VARCHAR2(32000);
42 amount BINARY_INTEGER;
43 pos INTEGER;
44
45
46 BEGIN
47 /* Create Temporary Lob */
48 DBMS_LOB.CREATETEMPORARY(lob_loc,TRUE);
49 /* Open the Lob for read write */
50 DBMS_LOB.OPEN(lob_loc,DBMS_LOB.LOB_READWRITE);
51
52 buffer := param1 ||' '|| param2 ||' '|| param3;
53 amount := length(buffer);
54 pos := 1;
55
56
57 /* Write to the Lob */
58 DBMS_LOB.WRITE(lob_loc,amount,pos,buffer);
59
60 /*Close Lob */
61 DBMS_LOB.CLOSE(lob_loc);
62
63 RETURN lob_loc;
64
65 END;
66
67 procedure Test_Search_Move_Data(testno IN NUMBER,
68 errbuf OUT NOCOPY VARCHAR2,
69 retcode OUT NOCOPY NUMBER)
70
71 is --{
72
73 l_test_search_web_status varchar2(30) := null;
74 l_test_search_category_set varchar2(30) := null;
75 l_errbuf varchar2(300);
76 l_retcode number;
77
78 begin --{
79
80 /*
81 Test Scenarios
82 ~~~~~~~~~~~~~~
83 CASE SEARCH_CATEGORY_SET WEB_STATUS
84 ---- ------------------- ---------
85 1 null ALL
86 2 null PUBLISHED/null
87 3 null PUBLISHED_UNPUBLISHED
88 4 27 ALL
89 5 27 PUBLISHED/null
90 6 27 PUBLISHED_UNPUBLISHED
91
92 27 is the current profile value of IBE_SEARCH_CATEGORY_SET
93 'NULL' is used to denote null in the test cases
94 */
95
96 G_TEST_FLAG_ON := true;
97
98
99 if (testno = 1) --Case 1
100 then
101 G_TEST_SEARCH_WEB_STATUS := 'ALL';
102 G_TEST_SEARCH_CATEGORY_SET := null;
103
104 elsif (testno = 2) --Case 2
105 then
106 G_TEST_SEARCH_WEB_STATUS := 'PUBLISHED';
107 G_TEST_SEARCH_CATEGORY_SET := null;
108
109 elsif (testno = 3) --Case 3
110 then
111 G_TEST_SEARCH_WEB_STATUS := 'PUBLISHED_UNPUBLISHED';
112 G_TEST_SEARCH_CATEGORY_SET := null;
113
114 elsif (testno = 4) --Case 4
115 then
116 G_TEST_SEARCH_WEB_STATUS := 'ALL';
117 G_TEST_SEARCH_CATEGORY_SET := 27;
118
119 elsif (testno = 5) --Case 5
120 then
121 G_TEST_SEARCH_WEB_STATUS := 'PUBLISHED';
122 G_TEST_SEARCH_CATEGORY_SET := 27;
123
124 elsif (testno = 6) --Case 6
125 then
126 G_TEST_SEARCH_WEB_STATUS := 'PUBLISHED_UNPUBLISHED';
127 G_TEST_SEARCH_CATEGORY_SET := 27;
128
129 end if;
130
131
132 ibe_search_setup_pvt.search_move_data(l_errbuf,l_retcode);
133
134 end Test_Search_Move_Data; --}
135 --}
136
137 procedure Search_Move_Data(
138 errbuf OUT NOCOPY VARCHAR2,
139 retcode OUT NOCOPY NUMBER
140 ) is
141
142 l_search_category_set varchar2(30);
143 l_search_web_status VARCHAR2(30);
144 l_use_category_search VARCHAR2(30);
145 l_use_fuzzy_search VARCHAR2(30);
146 l_fuzzy_count NUMBER := 0 ;
147 l_index_exists NUMBER := 0 ;
148 l_create_fuzzy_index1 VARCHAR2(1000);
149 l_create_fuzzy_index2 VARCHAR2(1000) ;
150 l_create_fuzzy_index VARCHAR2(1000);
151 l_create_index1 VARCHAR2(1000);
152 l_create_index2 VARCHAR2(1000) ;
153 l_create_index VARCHAR2(1000) ;
154 l_base_language VARCHAR2(30);
155
156 cursor old_multi_lexer is select pre_name from ctxsys.ctx_preferences
157 where pre_name = 'IBE_GLOBAL_LEXER';
158
159 cursor old_sub_lexers is select pre_name from ctxsys.ctx_preferences
160 where pre_name like 'IBE_LOBS__LEXER';
161 -- sytong, bug fix 2926852
162
163 -- TYPE searchCurType IS REF CURSOR;
164 -- search_insert_cur searchCurType;
165
166 l_status_AppInfo varchar2(300) ;
167 l_industry_AppInfo varchar2(300) ;
168 l_oracle_schema_AppInfo varchar2(300) ;
169 l_application_short_name varchar2(300) ;
170 l_trunc_tab varchar2(50);
171
172 l_db_version NUMBER := null;
173 l_db_version_str VARCHAR2(100) := null;
174 l_compatibility VARCHAR2(100) := null;
175
176 begin
177
178 /*
179 w
180 p
181 added to check for intermedia index before trying to drop it as otherwise script will fail
182 */
183 -- sytong, bug fix 2550147
184
185 l_search_category_set :='';
186 l_search_web_status := 'PUBLISHED';
187 l_use_category_search := 'N ';
188 l_use_fuzzy_search := 'N';
189 l_create_fuzzy_index1 := 'create index IBE_CT_IMEDIA_SEARCH_IM on ibe_ct_imedia_search(INDEXED_SEARCH) indextype is ctxsys.context ' ;
190 l_create_fuzzy_index2 := ' parameters ('' lexer ibe_global_lexer language column language Wordlist IBE_STEM_FUZZY_PREF '') ' ;
191 l_create_fuzzy_index := l_create_fuzzy_index1 || l_create_fuzzy_index2 ;
192 l_create_index1 := 'create index IBE_CT_IMEDIA_SEARCH_IM on ibe_ct_imedia_search(INDEXED_SEARCH) indextype is ctxsys.context ' ;
193 l_create_index2 := ' parameters('' lexer ibe_global_lexer language column language '') ' ;
194 l_create_index := l_create_index1 || l_create_index2 ;
195 l_base_language := ' ';
196
197
198 select count(*)
199 into l_index_exists
200 from user_indexes
201 where index_name = 'IBE_CT_IMEDIA_SEARCH_IM';
202
203
204 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
205 FND_FILE.PUT_LINE(FND_FILE.LOG,' DB VErsion ='||l_db_version_str);
206
207 If (l_db_version_str is null) Then
208 l_db_version := 8;
209 Else
210 l_db_version := to_number(substr(l_db_version_str, 1,(instr(l_db_version_str,'.'))-1));
211
212 End If;
213
214 if(l_index_exists > 0 )
215 then
216
217 FND_FILE.PUT_LINE(FND_FILE.LOG,' Intermedia index exists , dropping intermedia index ');
218 ------dbms_output.put_line(' Intermedia index exists , dropping intermedia index ');
219 execute immediate 'drop index IBE_CT_IMEDIA_SEARCH_IM force';
220 end if ;
221
222 FND_FILE.PUT_LINE(FND_FILE.LOG,' deleting data from Search table ');
223 --dbms_output.put_line(' deleting data from Search table ');
224
225
226 --truncatapplication_short_name
227 select application_short_name
228 into l_application_short_name
229 from fnd_application
230 where application_id = 671 ;
231
232 if (fnd_installation.get_app_info(l_application_short_name,
233 l_status_AppInfo ,
234 l_industry_AppInfo ,
235 l_oracle_schema_AppInfo )) then
236 --execute immediate 'truncate table ibe_ct_imedia_search';
237 l_trunc_tab := 'truncate table '||l_oracle_schema_AppInfo||'.ibe_ct_imedia_search';
238 execute immediate l_trunc_tab;
239 commit;
240 end if;
241
242 ----dbms_output.put_line(' Populating search table IBE_CT_IMEDIA_SEARCH');
243
244 FND_FILE.PUT_LINE(FND_FILE.LOG,' Populating search table IBE_CT_IMEDIA_SEARCH');
245 FND_FILE.PUT_LINE(FND_FILE.LOG,' This may take a while depending on how many rows');
246 FND_FILE.PUT_LINE(FND_FILE.LOG,' you have in mtl_system_items_tl table');
247
248 l_use_category_search := FND_PROFILE.VALUE_specific('IBE_USE_CATEGORY_SEARCH',671,0,671);
249
250 /* Profile for fuzzy search is defaulted to 'No' so if found to be null
251 it is set to 'No' in the next line
252 */
253
254 l_use_fuzzy_search := FND_PROFILE.VALUE_specific('IBE_FUZZY_SEARCH',671,0,671);
255
256 if(l_use_fuzzy_search is null )
257 then
258 l_use_fuzzy_search := 'N' ;
259 end if ;
260
261 FND_FILE.PUT_LINE(FND_FILE.LOG,' IBE_USE_CATEGORY_SEARCH = ' || l_use_category_search );
262 FND_FILE.PUT_LINE(FND_FILE.LOG,' IBE_FUZZY_SEARCH = ' || l_use_fuzzy_search );
263
264 --Get Search Category Set profile IBE_SEARCH_CATEGORY_SET;
265 if (G_TEST_FLAG_ON) --{
266 then
267 l_search_category_set := G_TEST_SEARCH_CATEGORY_SET;
268 else
269 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
270 end if; --}
271
272 --Get product status profile IBE_SEARCH_WEB_STATUS;
273 if (G_TEST_FLAG_ON)
274 then
275 l_search_web_status := G_TEST_SEARCH_WEB_STATUS;
276 else
277 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
278 end if; --}
279
280 FND_FILE.PUT_LINE(FND_FILE.LOG,' IBE_SEARCH_CATEGORY_SET = ' || l_search_category_set);
281 FND_FILE.PUT_LINE(FND_FILE.LOG,' IBE_SEARCH_WEB_STATUS = ' || l_search_web_status);
282
283 -----------
284
285
286 If (l_search_category_set is null and l_search_web_status='ALL')
287 Then --{
288
289 INSERT INTO IBE_CT_IMEDIA_SEARCH(
290 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
291 CREATED_BY,CREATION_DATE,
292 LAST_UPDATED_BY,LAST_UPDATE_DATE,
293 LAST_UPDATE_LOGIN,CATEGORY_ID,
294 ORGANIZATION_ID,CATEGORY_SET_ID,
295 INVENTORY_ITEM_ID,LANGUAGE,
296 DESCRIPTION,LONG_DESCRIPTION,
297 INDEXED_SEARCH,WEB_STATUS,
298 SECURITY_GROUP_ID)
299 (SELECT
300 ibe_ct_imedia_search_s1.nextval,
301 1,
302 FND_GLOBAL.user_id,
303 SYSDATE,
304 FND_GLOBAL.user_id,
305 SYSDATE,
306 FND_GLOBAL.conc_login_id,
307 c.CATEGORY_ID,
308 b.ORGANIZATION_ID,
309 c.CATEGORY_SET_ID,--bug 6924793
310 b.INVENTORY_ITEM_ID,
311 b.LANGUAGE,
312 b.DESCRIPTION,
313 b.LONG_DESCRIPTION,
314 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
315 a.web_status,
316 null
317 FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
318 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and
319 b.organization_id = c.organization_id and
320 c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
321 c.organization_id = a.organization_id and
322 exists (select 1
323 from oe_system_parameters_all osp
324 where osp.master_organization_id = b.organization_id));
325
326 Elsif (l_search_category_set is null and
327 (l_search_web_status='PUBLISHED' or l_search_web_status is null))
328 Then
329
330 INSERT INTO IBE_CT_IMEDIA_SEARCH(
331 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
332 CREATED_BY,CREATION_DATE,
333 LAST_UPDATED_BY,LAST_UPDATE_DATE,
334 LAST_UPDATE_LOGIN,CATEGORY_ID,
335 ORGANIZATION_ID,CATEGORY_SET_ID,
336 INVENTORY_ITEM_ID,LANGUAGE,
337 DESCRIPTION,LONG_DESCRIPTION,
338 INDEXED_SEARCH,WEB_STATUS,
339 SECURITY_GROUP_ID)
340 (SELECT ibe_ct_imedia_search_s1.nextval,
341 1,
342 FND_GLOBAL.user_id,
343 SYSDATE,
344 FND_GLOBAL.user_id,
345 SYSDATE,
346 FND_GLOBAL.conc_login_id,
347 c.CATEGORY_ID,
348 b.ORGANIZATION_ID,
349 c.CATEGORY_SET_ID,
350 b.INVENTORY_ITEM_ID,
351 b.LANGUAGE,
352 b.DESCRIPTION,
353 b.LONG_DESCRIPTION,
354 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
355 a.web_status,
356 null
357 FROM mtl_system_items_b_kfv a,mtl_system_items_tl b,mtl_item_categories c
358 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id = c.organization_id
359 and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id = a.organization_id
360 and exists ( select 1
361 from oe_system_parameters_all osp
362 where osp.master_organization_id = b.organization_id)
363 and a.web_status='PUBLISHED');
364
365
366 Elsif (l_search_category_set is null and l_search_web_status='PUBLISHED_UNPUBLISHED')
367 Then
368
369 INSERT INTO IBE_CT_IMEDIA_SEARCH(
370 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
371 CREATED_BY,CREATION_DATE,
372 LAST_UPDATED_BY,LAST_UPDATE_DATE,
373 LAST_UPDATE_LOGIN,CATEGORY_ID,
374 ORGANIZATION_ID,CATEGORY_SET_ID,
375 INVENTORY_ITEM_ID,LANGUAGE,
376 DESCRIPTION,LONG_DESCRIPTION,
377 INDEXED_SEARCH,WEB_STATUS,
378 SECURITY_GROUP_ID)
379 (SELECT ibe_ct_imedia_search_s1.nextval,
380 1,
381 FND_GLOBAL.user_id,
382 SYSDATE,
383 FND_GLOBAL.user_id,
384 SYSDATE,
385 FND_GLOBAL.conc_login_id,
386 c.CATEGORY_ID,
387 b.ORGANIZATION_ID,
388 c.CATEGORY_SET_ID,
389 b.INVENTORY_ITEM_ID,
390 b.LANGUAGE,
391 b.DESCRIPTION,
392 b.LONG_DESCRIPTION,
393 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
394 a.web_status,
395 null
396 FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
397 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id = c.organization_id
398 and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id = a.organization_id
399 and exists ( select 1
400 from oe_system_parameters_all osp
401 where osp.master_organization_id = b.organization_id)
402 and a.web_status in ('PUBLISHED','UNPUBLISHED'));
403
404
405 Elsif (l_search_category_set is not null and l_search_web_status='ALL')
406 Then
407
408 INSERT INTO IBE_CT_IMEDIA_SEARCH(
409 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
410 CREATED_BY,CREATION_DATE,
411 LAST_UPDATED_BY,LAST_UPDATE_DATE,
412 LAST_UPDATE_LOGIN,CATEGORY_ID,
413 ORGANIZATION_ID,CATEGORY_SET_ID,
414 INVENTORY_ITEM_ID,LANGUAGE,
415 DESCRIPTION,LONG_DESCRIPTION,
416 INDEXED_SEARCH,WEB_STATUS,
417 SECURITY_GROUP_ID)
418 (SELECT ibe_ct_imedia_search_s1.nextval,
419 1,
420 FND_GLOBAL.user_id,
421 SYSDATE,
422 FND_GLOBAL.user_id,
423 SYSDATE,
424 FND_GLOBAL.conc_login_id,
425 c.CATEGORY_ID,
426 b.ORGANIZATION_ID,
427 c.CATEGORY_SET_ID,
428 b.INVENTORY_ITEM_ID,
429 b.LANGUAGE,
430 b.DESCRIPTION,
431 b.LONG_DESCRIPTION,
432 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
433 a.web_status,
434 null
435 FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
436 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id = c.organization_id
437 and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id = a.organization_id
438 and exists (select 1
439 from oe_system_parameters_all osp
440 where osp.master_organization_id = b.organization_id)
441 and c.category_set_id = l_search_category_set);
442
443 Elsif (l_search_category_set is not null and
444 (l_search_web_status='PUBLISHED' OR l_search_web_status is null))
445 Then
446
447 INSERT INTO IBE_CT_IMEDIA_SEARCH(
448 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
449 CREATED_BY,CREATION_DATE,
450 LAST_UPDATED_BY,LAST_UPDATE_DATE,
451 LAST_UPDATE_LOGIN,CATEGORY_ID,
452 ORGANIZATION_ID,CATEGORY_SET_ID,
453 INVENTORY_ITEM_ID,LANGUAGE,
454 DESCRIPTION,LONG_DESCRIPTION,
455 INDEXED_SEARCH,WEB_STATUS,
456 SECURITY_GROUP_ID)
457 (SELECT ibe_ct_imedia_search_s1.nextval,
458 1,
459 FND_GLOBAL.user_id,
460 SYSDATE,
461 FND_GLOBAL.user_id,
462 SYSDATE,
463 FND_GLOBAL.conc_login_id,
464 c.CATEGORY_ID,
465 b.ORGANIZATION_ID,
466 c.CATEGORY_SET_ID,
467 b.INVENTORY_ITEM_ID,
468 b.LANGUAGE,
469 b.DESCRIPTION,
470 b.LONG_DESCRIPTION,
471 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
472 a.web_status,
473 null
474 FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
475 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id = c.organization_id
476 and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id = a.organization_id
477 and exists (select 1
478 from oe_system_parameters_all osp
479 where osp.master_organization_id = b.organization_id)
480 and c.category_set_id = l_search_category_set
481 and a.web_status = 'PUBLISHED');
482
483
484 Elsif (l_search_category_set is not null and l_search_web_status= 'PUBLISHED_UNPUBLISHED')
485 Then
486
487 INSERT INTO IBE_CT_IMEDIA_SEARCH(
488 IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
489 CREATED_BY,CREATION_DATE,
490 LAST_UPDATED_BY,LAST_UPDATE_DATE,
491 LAST_UPDATE_LOGIN,CATEGORY_ID,
492 ORGANIZATION_ID,CATEGORY_SET_ID,
493 INVENTORY_ITEM_ID,LANGUAGE,
494 DESCRIPTION,LONG_DESCRIPTION,
495 INDEXED_SEARCH,WEB_STATUS,
496 SECURITY_GROUP_ID)
497 (SELECT ibe_ct_imedia_search_s1.nextval,
498 1,
499 FND_GLOBAL.user_id,
500 SYSDATE,
501 FND_GLOBAL.user_id,
502 SYSDATE,
503 FND_GLOBAL.conc_login_id,
504 c.CATEGORY_ID,
505 b.ORGANIZATION_ID,
506 c.CATEGORY_SET_ID,
507 b.INVENTORY_ITEM_ID,
508 b.LANGUAGE,
509 b.DESCRIPTION,
510 b.LONG_DESCRIPTION,
511 ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
512 a.web_status,
513 null
514 FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
515 WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id = c.organization_id
516 and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id = a.organization_id
517 and exists (select 1
518 from oe_system_parameters_all osp
519 where osp.master_organization_id = b.organization_id)
520 and c.category_set_id = l_search_category_set
521 and a.web_status IN ('PUBLISHED', 'UNPUBLISHED'));
522
523 End if; --}
524
525 commit;
526
527 /*----------------------------------------------------------------------
528 -- Create the multi lexer preference and its constituent components --
529 ----------------------------------------------------------------------*/
530
531 for dropit in old_multi_lexer loop
532 ctx_ddl.drop_preference('IBE_GLOBAL_LEXER');
533 end loop;
534
535 for dropit in old_sub_lexers loop
536 ctx_ddl.drop_preference(dropit.pre_name);
537 end loop;
538
539 FND_FILE.PUT_LINE(FND_FILE.LOG,' Creating GLobal Lexer ');
540
541 ctx_ddl.create_preference('ibe_global_lexer','multi_lexer');
542
543 ctx_ddl.create_preference('ibe_lobs_blexer', 'basic_lexer');
544 ctx_ddl.create_preference('ibe_lobs_clexer', 'chinese_vgram_lexer');
545 ctx_ddl.create_preference('ibe_lobs_jlexer', 'japanese_vgram_lexer');
546
547 /* bug fix 4585787 */
548 If l_db_version > 8 Then
549 -- bug 4674288 - 10gR2 compatibility issue force explicit KOREAN_LEXER drop
550 -- since it will not show in the CTX_PREFERENCES post 10gR2 upgrade.
551 if ( l_db_version >= 10 ) then
552 begin
553 ctx_ddl.drop_preference('IBE_LOBS_KLEXER');
554 exception
555 -- We need to try the drop, no worries if it is not there ...
556 when others then null;
557 end;
558 end if;
559
560 ctx_ddl.create_preference('ibe_lobs_klexer', 'korean_morph_lexer');
561
562 else
563 ctx_ddl.create_preference('ibe_lobs_klexer', 'korean_lexer');
564 end if;
565
566 ctx_ddl.add_sub_lexer('ibe_global_lexer', 'JA', 'ibe_lobs_jlexer');
567 ctx_ddl.add_sub_lexer('ibe_global_lexer', 'KO', 'ibe_lobs_klexer');
568 ctx_ddl.add_sub_lexer('ibe_global_lexer', 'ZHS', 'ibe_lobs_clexer');
569 ctx_ddl.add_sub_lexer('ibe_global_lexer', 'ZHT', 'ibe_lobs_clexer');
570 ctx_ddl.add_sub_lexer('ibe_global_lexer', 'default', 'ibe_lobs_blexer');
571
572 /*----------------end of mulit lexer ------------ */
573
574
575 /* --------------------------------------------------------------------------
576 If Fuzzy search profile is on the create the fuzzy preference and then use that
577 in the index creation . if the option is OFF then create the intermedia index
578 without the fuzzy preference
579 --------------------------------------------------------------------------
580 */
581
582 if(l_use_FUZZY_search = 'Y')
583 then
584
585
586 /* Checking if Preference exists before trying to drop it
587 */
588
589 select count(*) into l_fuzzy_count
590 from CTX_PREFERENCES
591 where
592 pre_name = 'IBE_STEM_FUZZY_PREF' ;
593
594
595 if(l_fuzzy_count = 1 )
596 then
597 Ctx_Ddl.Drop_Preference('IBE_STEM_FUZZY_PREF');
598 FND_FILE.PUT_LINE(FND_FILE.LOG,' Dropped existing IBE_STEM_FUZZY_PREF preference ');
599 end if ;
600
601 Ctx_Ddl.Create_Preference('IBE_STEM_FUZZY_PREF', 'BASIC_WORDLIST');
602 /* Changed from l_base_language to 'AUTO' to support muliple languages fuzzy 06/07/01
603 */
604 ctx_ddl.set_attribute('IBE_STEM_FUZZY_PREF','FUZZY_MATCH','AUTO');
605 ctx_ddl.set_attribute('IBE_STEM_FUZZY_PREF','FUZZY_SCORE','0');
606 ctx_ddl.set_attribute('IBE_STEM_FUZZY_PREF','FUZZY_NUMRESULTS','5000');
607 ctx_ddl.set_attribute('IBE_STEM_FUZZY_PREF','STEMMER','AUTO');
608
609
610
611
612 FND_FILE.PUT_LINE(FND_FILE.LOG,' Creating intermedia index on IBE_CT_IMEDIA_SEARCH table with fuzzy ');
613 FND_FILE.PUT_LINE(FND_FILE.LOG,'This may take a while ');
614 execute immediate l_create_fuzzy_index;
615
616
617 else
618 FND_FILE.PUT_LINE(FND_FILE.LOG,' Creating intermedia index on IBE_CT_IMEDIA_SEARCH table');
619 FND_FILE.PUT_LINE(FND_FILE.LOG,'This may take a while ');
620
621 execute immediate l_create_index;
622
623 end if;
624
625 FND_FILE.PUT_LINE(FND_FILE.LOG,'Intermedia Index created , procedure completed sucessfully');
626 ----dbms_output.put_line('Intermedia Index created , procedure completed sucessfully');
627 end Search_Move_Data;
628
629 end ibe_search_setup_pvt ;