[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_INTERMEDIA_INDEX
Source
1 PACKAGE BODY ICX_POR_INTERMEDIA_INDEX AS
2 /* $Header: ICXCGITB.pls 120.0 2006/02/23 11:10:45 sbgeorge noship $*/
3
4 --
5 -- Cursor to fetch intalled languages
6 --
7 CURSOR installed_languages_csr IS
8 select language_code,
9 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
10 nls_language,
11 installed_flag
12 from fnd_languages
13 where installed_flag in ('B', 'I');
14
15 /**
16 ** Proc : create_index
17 ** Desc : Create interMedia index with multi-lex for each installed
18 ** language in FND_LANGUAGES, including the base language.
19 **/
20
21 PROCEDURE create_index IS
22
23 xErrLoc INTEGER := 0;
24
25 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
26 xLang fnd_languages.language_code%TYPE;
27 xNLSLang fnd_languages.nls_language%TYPE;
28
29 -- Bug 1802907, zxzhang, May-24-01
30 ctx_index_tbsp VARCHAR2(100):='USER_IDX';
31 ctx_data_tbsp VARCHAR2(100):='USER_DATA';
32
33 -- sosingha bug 3175547 to use AD API for getting the tablespace
34 if_registered VARCHAR2(10);
35 check_tspace_exist VARCHAR2(10);
36
37 ctx_section_group VARCHAR2(30) := 'ICX_CAT_SECTGRP';
38 ctx_desc_tag VARCHAR2(30) := NULL;
39 ctx_pref_lexer VARCHAR2(30) := NULL;
40 ctx_pref_datastore VARCHAR2(30) := 'ICX_CAT_DATASTORE';
41 xAppsSchemaName VARCHAR2(30) := NULL;
42
43 --Bug 4353520
44 xIcxSchemaName varchar2(30):=ICX_POR_EXT_UTL.getIcxSchema;
45 BEGIN
46 -- get the schema name from fnd_oracle_userid insted of hardcoding it.
47 SELECT oracle_username
48 INTO xAppsSchemaName
49 FROM fnd_oracle_userid
50 WHERE read_only_flag = 'U';
51
52 -- Bug 1802907, zxzhang, May-24-01
53 xErrLoc := 50;
54 /* sosingha bug 3175547 to use AD API for getting the tablespace
55 select index_tablespace
56 into ctx_index_tbsp
57 from fnd_product_installations
58 where application_id = '178';
59
60 -- bug 2050141
61 xErrLoc := 60;
62 select tablespace
63 into ctx_data_tbsp
64 from fnd_product_installations
65 where application_id = '178';
66 */
67
68 -- Obtain the index tablespace to use.
69 AD_TSPACE_UTIL.get_object_tablespace(
70 x_product_short_name => 'ICX',
71 x_object_name => 'DR$ICX_CAT_ITEMS_CTX_DESC$X',
72 x_object_type => 'TABLE',
73 x_index_lookup_flag => 'Y', -- obtain the index tspace
74 x_validate_ts_exists => 'Y',
75 x_is_object_registered => if_registered,
76 x_ts_exists => check_tspace_exist,
77 x_tablespace => ctx_index_tbsp);
78
79 xErrLoc := 60;
80 -- Obtain the data tablespace to use.
81 AD_TSPACE_UTIL.get_object_tablespace(
82 x_product_short_name => 'ICX',
83 x_object_name => 'DR$ICX_CAT_ITEMS_CTX_DESC$I',
84 x_object_type => 'TABLE',
85 x_index_lookup_flag => 'N',
86 x_validate_ts_exists => 'Y',
87 x_is_object_registered => if_registered,
88 x_ts_exists => check_tspace_exist,
89 x_tablespace => ctx_data_tbsp);
90
91 xErrLoc := 100;
92 ctx_section_group := 'ICX_CAT_SECTGRP';
93
94 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
95 -- drop the existing preferences
96 BEGIN
97 ctx_ddl.drop_preference(ctx_pref_datastore);
98 xErrLoc := 110;
99 ctx_ddl.drop_preference('ICX_CAT_FILTER');
100 xErrLoc := 120;
101 ctx_ddl.drop_preference('ICX_CAT_LEXER_GLOBAL');
102 xErrLoc := 125;
103 ctx_ddl.drop_preference('ICX_CAT_WORDLIST');
104 xErrLoc := 140;
105 ctx_ddl.drop_preference('ICX_CAT_STORAGE');
106 xErrLoc := 150;
107 ctx_ddl.drop_section_group(ctx_section_group);
108 xErrLoc := 170;
109 EXCEPTION
110 WHEN OTHERS THEN
111 null;
112 END;
113
114 xErrLoc := 200;
115 -- create preferences
116 ctx_ddl.create_preference(ctx_pref_datastore, 'DETAIL_DATASTORE');
117 xErrLoc := 210;
118 -- Set the attributes specific to DETAIL_DATASTORE.
119 ctx_ddl.set_attribute(ctx_pref_datastore, 'binary', 'false');
120 xErrLoc := 211;
121 ctx_ddl.set_attribute(ctx_pref_datastore, 'detail_table',
122 xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_TLP');
123 xErrLoc := 212;
124 ctx_ddl.set_attribute(ctx_pref_datastore,'detail_key',
125 'rt_item_id, language');
126 xErrLoc := 213;
127 ctx_ddl.set_attribute(ctx_pref_datastore, 'detail_lineno', 'sequence');
128 xErrLoc := 214;
129 ctx_ddl.set_attribute(ctx_pref_datastore,'detail_text', 'CTX_DESC');
130
131 xErrLoc := 220;
132 ctx_ddl.create_preference('ICX_CAT_FILTER', 'NULL_FILTER');
133 xErrLoc := 230;
134 ctx_ddl.create_preference('ICX_CAT_WORDLIST', 'BASIC_WORDLIST');
135 xErrLoc := 240;
136 ctx_ddl.set_attribute('ICX_CAT_WORDLIST', 'STEMMER', 'AUTO');
137 xErrLoc := 260;
138 ctx_ddl.set_attribute('ICX_CAT_WORDLIST', 'FUZZY_MATCH', 'AUTO');
139
140 -- Bug 1802907, zxzhang, May-24-01
141 -- Enabled the storage clause to have the index created in
142 -- the INDEX tablespace
143 xErrLoc := 270;
144 ctx_ddl.create_preference('ICX_CAT_STORAGE', 'BASIC_STORAGE');
145
146 -- sosingha bug 3175547 to use AD API for getting the tablespace
147 -- Check if the tablespace exists before using it
148 IF (check_tspace_exist = 'Y') THEN
149 ctx_ddl.set_attribute('ICX_CAT_STORAGE','I_TABLE_CLAUSE',
150 'tablespace '||ctx_data_tbsp);
151 ctx_ddl.set_attribute('ICX_CAT_STORAGE','K_TABLE_CLAUSE',
152 'tablespace '||ctx_data_tbsp);
153 ctx_ddl.set_attribute('ICX_CAT_STORAGE','R_TABLE_CLAUSE',
154 'tablespace '||ctx_data_tbsp);
155 ctx_ddl.set_attribute('ICX_CAT_STORAGE','N_TABLE_CLAUSE',
156 'tablespace '||ctx_data_tbsp);
157 ctx_ddl.set_attribute('ICX_CAT_STORAGE','P_TABLE_CLAUSE',
158 'tablespace '||ctx_data_tbsp);
159 ctx_ddl.set_attribute('ICX_CAT_STORAGE','I_INDEX_CLAUSE',
160 'tablespace '||ctx_index_tbsp);
161 END IF;
162
163
164 xErrLoc := 280;
165 ctx_ddl.create_section_group(ctx_section_group, 'basic_section_group');
166 xErrLoc := 290;
167 ctx_ddl.add_field_section(ctx_section_group, 'SEARCH_TYPE',
168 'SEARCH_TYPE', FALSE);
169
170 xErrLoc := 300;
171 ctx_ddl.add_field_section(ctx_section_group, 'supid',
172 'supid', FALSE);
173 ctx_ddl.add_field_section(ctx_section_group, 'catid',
174 'catid', FALSE);
175 ctx_ddl.add_field_section(ctx_section_group, 'orgid',
176 'orgid', FALSE);
177 ctx_ddl.add_field_section(ctx_section_group, 'language',
178 'language', FALSE);
179 xErrLoc := 310;
180 ctx_ddl.add_zone_section(ctx_section_group, 'catnm',
181 'catnm' );
182
183 FOR counter in 1..100
184 LOOP
185 xErrLoc := 320;
186 ctx_desc_tag := counter;
187 ctx_ddl.add_zone_section(ctx_section_group,to_char(counter),
188 ctx_desc_tag);
189 END LOOP;
190
191 FOR counter in 1000..1300
192 LOOP
193 xErrLoc := 330;
194 ctx_desc_tag := counter;
195 ctx_ddl.add_zone_section(ctx_section_group, to_char(counter),
196 ctx_desc_tag);
197 END LOOP;
198
199 FOR counter in 5000..5150
200 LOOP
201 xErrLoc := 340;
202 -- OEX_IP_PORTING
203 ctx_desc_tag := counter;
204 ctx_ddl.add_zone_section(ctx_section_group,to_char(counter),
205 ctx_desc_tag);
206 END LOOP;
207
208
209 xErrLoc := 400;
210 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
211 ctx_ddl.create_preference('ICX_CAT_LEXER_GLOBAL', 'MULTI_LEXER');
212
213 xErrLoc := 420;
214 FOR language_row IN installed_languages_csr LOOP
215 xLang := language_row.language_code;
216 xNLSLang := language_row.nls_language;
217
218 xErrLoc := 440;
219 ctx_pref_lexer := 'ICX_CAT_LEXER_' || xLang;
220
221 -- drop the existing preference
222 BEGIN
223 xErrLoc := 450;
224 ctx_ddl.drop_preference(ctx_pref_lexer);
225 xErrLoc := 460;
226 EXCEPTION
227 WHEN OTHERS THEN
228 null;
229 END;
230
231 xErrLoc := 480;
232 -- create preferences
233 if (xLang IN ('US', 'GB')) then
234 -- American English (US), English (GB)
235 xErrLoc := 3490;
236 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
237 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
238 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
239 xErrLoc := 495;
240 -- bug1898152, jingyu. Treat accented characters as regular ones.
241 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
242 elsif (xLang = 'JA') then
243 -- Japanese (JA)
244 xErrLoc := 500;
245 ctx_ddl.create_preference(ctx_pref_lexer, 'JAPANESE_VGRAM_LEXER');
246 elsif (xLang = 'KO') then
247 -- Korean (KO)
248 xErrLoc := 410;
249 ctx_ddl.create_preference(ctx_pref_lexer, 'KOREAN_MORPH_LEXER');
250 xErrLoc := 520;
251 ctx_ddl.set_attribute(ctx_pref_lexer, 'VERB_ADJECTIVE', 'TRUE');
252 ctx_ddl.set_attribute(ctx_pref_lexer, 'ONE_CHAR_WORD', 'TRUE');
253 ctx_ddl.set_attribute(ctx_pref_lexer, 'NUMBER', 'TRUE');
254 ctx_ddl.set_attribute(ctx_pref_lexer, 'USER_DIC', 'TRUE');
255 ctx_ddl.set_attribute(ctx_pref_lexer, 'STOP_DIC', 'TRUE');
256 ctx_ddl.set_attribute(ctx_pref_lexer, 'COMPOSITE', 'COMPONENT_WORD');
257 ctx_ddl.set_attribute(ctx_pref_lexer, 'MORPHEME', 'TRUE');
258 ctx_ddl.set_attribute(ctx_pref_lexer, 'TO_UPPER', 'TRUE');
259 ctx_ddl.set_attribute(ctx_pref_lexer, 'HANJA', 'FALSE');
260 ctx_ddl.set_attribute(ctx_pref_lexer, 'LONG_WORD', 'FALSE');
261 ctx_ddl.set_attribute(ctx_pref_lexer, 'JAPANESE', 'FALSE');
262 ctx_ddl.set_attribute(ctx_pref_lexer, 'ENGLISH', 'TRUE');
263 elsif (xLang IN ('ZHS', 'ZHT')) then
264 -- Simplified Chinese (ZHS), Traditional Chinese (ZHT)
265 xErrLoc := 530;
266 ctx_ddl.create_preference(ctx_pref_lexer, 'CHINESE_VGRAM_LEXER');
267 elsif (xLang IN ('F', 'FRC')) then
268 -- French (F), Canadian French (FRC)
269 xErrLoc := 540;
270 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
271 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
272 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
273 -- bug1898152, jingyu. Treat accented characters as regular ones.
274 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
275 elsif (xLang = 'D') then
276 -- German (D)
277 xErrLoc := 550;
278 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
279 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
280 ctx_ddl.set_attribute(ctx_pref_lexer, 'ALTERNATE_SPELLING', 'GERMAN');
281 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
282 -- bug1898152, jingyu. Treat accented characters as regular ones.
283 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
284 elsif (xLang = 'I') then
285 -- Italian (I)
286 xErrLoc := 560;
287 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
288 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
289 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
290 -- bug1898152, jingyu. Treat accented characters as regular ones.
291 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
292 elsif (xLang in ('E','ESA')) then
293 -- Spanish (E)
294 xErrLoc := 570;
295 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
296 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
297 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
298 -- bug1898152, jingyu. Treat accented characters as regular ones.
299 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
300 elsif (xLang = 'NL') then
301 -- Dutch (NL)
302 xErrLoc := 580;
303 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
304 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
305 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
306 -- bug1898152, jingyu. Treat accented characters as regular ones.
307 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
308 else
309 -- All other languages
310 xErrLoc := 4590;
311 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
312 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
313 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
314 -- bug1898152, jingyu. Treat accented characters as regular ones.
315 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
316 end if;
317
318 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
319 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL', xNLSLang,
320 ctx_pref_lexer);
321 if (language_row.installed_flag = 'B') then
322 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL', 'default',
323 ctx_pref_lexer);
324 end if;
325
326 END LOOP;
327
328 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
329 xErrLoc := 600;
330 BEGIN
331 execute immediate 'DROP INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC';
332 EXCEPTION
333 WHEN OTHERS THEN
334 null;
335 END;
336
337 xErrLoc := 700;
338 BEGIN
339 -- Bug#3260189
340 execute immediate
341 'CREATE INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC' ||
342 ' ON ICX_CAT_ITEMS_TLP(CTX_DESC' ||
343 ') INDEXTYPE IS CTXSYS.CONTEXT ' ||
344 'PARAMETERS(''DATASTORE ' || xAppsSchemaName || '.ICX_CAT_DATASTORE' ||
345 ' FILTER ' || xAppsSchemaName || '.ICX_CAT_FILTER' ||
346 ' LEXER ' || xAppsSchemaName || '.ICX_CAT_LEXER_GLOBAL' ||
347 ' LANGUAGE COLUMN LANGUAGE' ||
348 ' WORDLIST ' || xAppsSchemaName || '.ICX_CAT_WORDLIST' ||
349 ' STORAGE ' || xAppsSchemaName || '.ICX_CAT_STORAGE' ||
350 ' STOPLIST CTXSYS.EMPTY_STOPLIST' ||
351 ' SECTION GROUP ' || xAppsSchemaName || '.ICX_CAT_SECTGRP' || ''')';
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 RAISE_APPLICATION_ERROR(-20000,
356 'Exception at ICX_POR_INTERMEDIA_INDEX.create_index('
357 || xErrLoc || '): ' || SQLERRM );
358 END;
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 RAISE_APPLICATION_ERROR(-20000,
363 'Exception at ICX_POR_INTERMEDIA_INDEX.create_index('
364 || xErrLoc || '): ' || SQLERRM);
365
366 END create_index;
367
368 /**
369 ** Proc : drop_index
370 ** Desc : Drop the index for each installed language in FND_LANGUAGES,
371 ** including the base language.
372 **/
373
374 PROCEDURE drop_index IS
375 xErrLoc INTEGER := 0;
376
377 ctx_section_group VARCHAR2(30) := 'ICX_CAT_SECTGRP';
378 ctx_pref_lexer VARCHAR2(30) := NULL;
379 ctx_pref_datastore VARCHAR2(30) := 'ICX_CAT_DATASTORE';
380
381 --Bug 4353520
382 xIcxSchemaName varchar2(30):=ICX_POR_EXT_UTL.getIcxSchema;
383 BEGIN
384 xErrLoc := 100;
385
386 -- drop the existing preferences
387 BEGIN
388 ctx_ddl.drop_preference(ctx_pref_datastore);
389 xErrLoc := 110;
390 ctx_ddl.drop_preference('ICX_CAT_FILTER');
391 xErrLoc := 120;
392 ctx_ddl.drop_preference('ICX_CAT_LEXER_GLOBAL');
393 xErrLoc := 125;
394 ctx_ddl.drop_preference('ICX_CAT_WORDLIST');
395 xErrLoc := 140;
396 ctx_ddl.drop_preference('ICX_CAT_STORAGE');
397 xErrLoc := 150;
398 ctx_ddl.drop_section_group(ctx_section_group);
399 xErrLoc := 170;
400 EXCEPTION
401 WHEN OTHERS THEN
402 NULL;
403 END;
404
405 FOR language_row IN installed_languages_csr LOOP
406 xErrLoc := 200;
407 -- language-specific preference settings
408 ctx_pref_lexer := 'ICX_CAT_LEXER_' || language_row.language_code;
409
410 BEGIN
411 xErrLoc := 210;
412 ctx_ddl.drop_preference(ctx_pref_lexer);
413 EXCEPTION
414 WHEN OTHERS THEN
415 NULL;
416 END;
417 END LOOP;
418
419 xErrLoc := 400;
420 BEGIN
421 execute immediate 'DROP INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC';
422 EXCEPTION
423 WHEN OTHERS THEN
424 NULL;
425 END;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 RAISE_APPLICATION_ERROR(-20000,
430 'Exception at ICX_POR_INTERMEDIA_INDEX.drop_index('
431 || xErrLoc || '): ' || SQLERRM);
432
433 END drop_index;
434
435 /**
436 ** Proc : rebuild_index
437 ** Desc : Rebuild the index for each installed language in FND_LANGUAGES,
438 ** including the base language.
439 **/
440
441 PROCEDURE rebuild_index IS
442
443 xErrLoc INTEGER := 0;
444
445 --Bug 4353520
446 xIcxSchemaName varchar2(30):=ICX_POR_EXT_UTL.getIcxSchema;
447 BEGIN
448 xErrLoc := 100;
449 -- bug 1663129: to avoid deadlock
450 -- bug 1854624: calling ctxsys.ICX_CAT_index_rebuild....
451 ad_ctx_ddl.sync_index(xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC');
452 xErrLoc := 200;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 RAISE_APPLICATION_ERROR(-20000,
457 'Exception at ICX_POR_INTERMEDIA_INDEX.rebuild_index('
458 || xErrLoc || '): ' || SQLERRM);
459
460 END rebuild_index;
461
462
463 END ICX_POR_INTERMEDIA_INDEX;