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