[Home] [Help]
Skip to content
PACKAGE BODY: APPS.ICX_POR_INTERMEDIA_INDEX
Source
1 PACKAGE BODY ICX_POR_INTERMEDIA_INDEX AS
2 /* $Header: ICXCGITB.pls 120.0.12020000.2 2013/02/08 19:59:43 bpulivar ship $*/
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 -- 13822612 Starts
309 elsif (xLang = 'SQ') then
310 -- Albanian (SQ)
311 xErrLoc := 590;
312 ctx_ddl.create_preference(ctx_pref_lexer, 'WORLD_LEXER');
313 -- 13822612 Ends
314 else
315 -- All other languages
316 xErrLoc := 4590;
317 ctx_ddl.create_preference(ctx_pref_lexer, 'BASIC_LEXER');
318 ctx_ddl.set_attribute(ctx_pref_lexer, 'INDEX_THEMES', 'NO');
319 ctx_ddl.set_attribute(ctx_pref_lexer, 'PRINTJOINS', '-_');
320 -- bug1898152, jingyu. Treat accented characters as regular ones.
321 ctx_ddl.set_attribute(ctx_pref_lexer, 'BASE_LETTER', 'YES');
322 end if;
323
324 -- 13822612 Starts
325 if (xLang <> 'SQ') then
326 -- Albanian (SQ)
327 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
328 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL', xNLSLang,
329 ctx_pref_lexer);
330 if (language_row.installed_flag = 'B') then
331 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL', 'default',
332 ctx_pref_lexer);
333 end if;
334 end if;
335 -- 13822612 Ends
336
337 END LOOP;
338
339 -- Bug 2249721, Multi-Lexer, ZXZHANG, Mar-04-02
340 xErrLoc := 600;
341 BEGIN
342 execute immediate 'DROP INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC';
343 EXCEPTION
344 WHEN OTHERS THEN
345 null;
346 END;
347
348 xErrLoc := 700;
349 BEGIN
350 -- Bug#3260189
351 execute immediate
352 'CREATE INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC' ||
353 ' ON ICX_CAT_ITEMS_TLP(CTX_DESC' ||
354 ') INDEXTYPE IS CTXSYS.CONTEXT ' ||
355 'PARAMETERS(''DATASTORE ' || xAppsSchemaName || '.ICX_CAT_DATASTORE' ||
356 ' FILTER ' || xAppsSchemaName || '.ICX_CAT_FILTER' ||
357 ' LEXER ' || xAppsSchemaName || '.ICX_CAT_LEXER_GLOBAL' ||
358 ' LANGUAGE COLUMN LANGUAGE' ||
359 ' WORDLIST ' || xAppsSchemaName || '.ICX_CAT_WORDLIST' ||
360 ' STORAGE ' || xAppsSchemaName || '.ICX_CAT_STORAGE' ||
361 ' STOPLIST CTXSYS.EMPTY_STOPLIST' ||
362 ' SECTION GROUP ' || xAppsSchemaName || '.ICX_CAT_SECTGRP' || ''')';
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 RAISE_APPLICATION_ERROR(-20000,
367 'Exception at ICX_POR_INTERMEDIA_INDEX.create_index('
368 || xErrLoc || '): ' || SQLERRM );
369 END;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 RAISE_APPLICATION_ERROR(-20000,
374 'Exception at ICX_POR_INTERMEDIA_INDEX.create_index('
375 || xErrLoc || '): ' || SQLERRM);
376
377 END create_index;
378
379 /**
380 ** Proc : drop_index
381 ** Desc : Drop the index for each installed language in FND_LANGUAGES,
382 ** including the base language.
383 **/
384
385 PROCEDURE drop_index IS
386 xErrLoc INTEGER := 0;
387
388 ctx_section_group VARCHAR2(30) := 'ICX_CAT_SECTGRP';
389 ctx_pref_lexer VARCHAR2(30) := NULL;
390 ctx_pref_datastore VARCHAR2(30) := 'ICX_CAT_DATASTORE';
391
392 --Bug 4353520
393 xIcxSchemaName varchar2(30):=ICX_POR_EXT_UTL.getIcxSchema;
394 BEGIN
395 xErrLoc := 100;
396
397 -- drop the existing preferences
398 BEGIN
399 ctx_ddl.drop_preference(ctx_pref_datastore);
400 xErrLoc := 110;
401 ctx_ddl.drop_preference('ICX_CAT_FILTER');
402 xErrLoc := 120;
403 ctx_ddl.drop_preference('ICX_CAT_LEXER_GLOBAL');
404 xErrLoc := 125;
405 ctx_ddl.drop_preference('ICX_CAT_WORDLIST');
406 xErrLoc := 140;
407 ctx_ddl.drop_preference('ICX_CAT_STORAGE');
408 xErrLoc := 150;
409 ctx_ddl.drop_section_group(ctx_section_group);
410 xErrLoc := 170;
411 EXCEPTION
412 WHEN OTHERS THEN
413 NULL;
414 END;
415
416 FOR language_row IN installed_languages_csr LOOP
417 xErrLoc := 200;
418 -- language-specific preference settings
419 ctx_pref_lexer := 'ICX_CAT_LEXER_' || language_row.language_code;
420
421 BEGIN
422 xErrLoc := 210;
423 ctx_ddl.drop_preference(ctx_pref_lexer);
424 EXCEPTION
425 WHEN OTHERS THEN
426 NULL;
427 END;
428 END LOOP;
429
430 xErrLoc := 400;
431 BEGIN
432 execute immediate 'DROP INDEX '||xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC';
433 EXCEPTION
434 WHEN OTHERS THEN
435 NULL;
436 END;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 RAISE_APPLICATION_ERROR(-20000,
441 'Exception at ICX_POR_INTERMEDIA_INDEX.drop_index('
442 || xErrLoc || '): ' || SQLERRM);
443
444 END drop_index;
445
446 /**
447 ** Proc : rebuild_index
448 ** Desc : Rebuild the index for each installed language in FND_LANGUAGES,
449 ** including the base language.
450 **/
451
452 PROCEDURE rebuild_index IS
453
454 xErrLoc INTEGER := 0;
455
456 --Bug 4353520
457 xIcxSchemaName varchar2(30):=ICX_POR_EXT_UTL.getIcxSchema;
458 BEGIN
459 xErrLoc := 100;
460 -- bug 1663129: to avoid deadlock
461 -- bug 1854624: calling ctxsys.ICX_CAT_index_rebuild....
462 ad_ctx_ddl.sync_index(xIcxSchemaName||'.ICX_CAT_ITEMS_CTX_DESC');
463 xErrLoc := 200;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 RAISE_APPLICATION_ERROR(-20000,
468 'Exception at ICX_POR_INTERMEDIA_INDEX.rebuild_index('
469 || xErrLoc || '): ' || SQLERRM);
470
471 END rebuild_index;
472
473
474 END ICX_POR_INTERMEDIA_INDEX;