[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_INTERMEDIA_INDEX_PVT
Source
1 PACKAGE BODY ICX_CAT_INTERMEDIA_INDEX_PVT AS
2 /* $Header: ICXVCIIB.pls 120.7.12020000.2 2013/02/08 18:55:52 bpulivar ship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_INTERMEDIA_INDEX_PVT';
6
7 -- Cursor to fetch intalled languages
8 CURSOR installed_languages_csr IS
9 SELECT language_code,
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-lexer for each installed
18 ** language in FND_LANGUAGES, including the base language.
19 **/
20
21 PROCEDURE create_index
22 IS
23 l_api_name CONSTANT VARCHAR2(30):= 'create_index';
24 l_err_loc PLS_INTEGER := 0;
25
26 l_lang fnd_languages.language_code%TYPE;
27 l_nls_lang fnd_languages.nls_language%TYPE;
28
29 l_ctx_index_tbsp VARCHAR2(100):='USER_IDX';
30 l_ctx_data_tbsp VARCHAR2(100):='USER_DATA';
31
32 l_is_object_registered VARCHAR2(10);
33 l_ts_exists VARCHAR2(10);
34
35 l_ctx_section_group VARCHAR2(30) := 'ICX_CAT_SECTGRP_HDRS';
36 l_ctx_desc_tag VARCHAR2(30) := NULL;
37 l_ctx_pref_lexer VARCHAR2(30) := NULL;
38 l_ctx_pref_datastore VARCHAR2(30) := 'ICX_CAT_DATASTORE_HDRS';
39 l_apps_schema_name VARCHAR2(30) := NULL;
40 l_icx_schema_name VARCHAR2(30) := NULL;
41 l_parallel PLS_INTEGER;
42 l_mem PLS_INTEGER;
43 l_parameter VARCHAR2(50);
44
45 BEGIN
46 l_err_loc := 100;
47
48 l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
49 l_apps_schema_name := ICX_CAT_UTIL_PVT.getAppsSchemaName;
50
51 l_err_loc := 200;
52 -- Obtain the index tablespace to use.
53 AD_TSPACE_UTIL.get_object_tablespace(
54 x_product_short_name => 'ICX',
55 x_object_name => 'DR$ICX_CAT_ITEMSCTXDESC_HDRS$X',
56 x_object_type => 'TABLE',
57 x_index_lookup_flag => 'Y', -- obtain the index tspace
58 x_validate_ts_exists => 'Y',
59 x_is_object_registered => l_is_object_registered,
60 x_ts_exists => l_ts_exists,
61 x_tablespace => l_ctx_index_tbsp);
62
63 l_err_loc := 300;
64 -- Obtain the data tablespace to use.
65 AD_TSPACE_UTIL.get_object_tablespace(
66 x_product_short_name => 'ICX',
67 x_object_name => 'DR$ICX_CAT_ITEMSCTXDESC_HDRS$I',
68 x_object_type => 'TABLE',
69 x_index_lookup_flag => 'N',
70 x_validate_ts_exists => 'Y',
71 x_is_object_registered => l_is_object_registered,
72 x_ts_exists => l_ts_exists,
73 x_tablespace => l_ctx_data_tbsp);
74
75 -- First we drop the existing preferences
76 -- We need this in a separate block since if these don't exist
77 -- they will throw exception and we are ok with this
78 l_err_loc := 400;
79 BEGIN
80 ctx_ddl.drop_preference(l_ctx_pref_datastore);
81 l_err_loc := 410;
82 ctx_ddl.drop_preference('ICX_CAT_FILTER_HDRS');
83 l_err_loc := 420;
84 ctx_ddl.drop_preference('ICX_CAT_LEXER_GLOBAL_HDRS');
85 l_err_loc := 430;
86 ctx_ddl.drop_preference('ICX_CAT_WORDLIST_HDRS');
87 l_err_loc := 440;
88 ctx_ddl.drop_preference('ICX_CAT_STORAGE_HDRS');
89 l_err_loc := 450;
90 ctx_ddl.drop_section_group(l_ctx_section_group);
91 l_err_loc := 460;
92 EXCEPTION
93 WHEN OTHERS THEN
94 null;
95 END;
96
97 -- now we will go ahead and create the preferences
98 l_err_loc := 500;
99
100 -- create the detail datastore preferences
101 ctx_ddl.create_preference(l_ctx_pref_datastore, 'DETAIL_DATASTORE');
102
103 -- Set the attributes specific to DETAIL_DATASTORE.
104 l_err_loc := 510;
105 ctx_ddl.set_attribute(l_ctx_pref_datastore, 'binary', 'false');
106 l_err_loc := 520;
107 ctx_ddl.set_attribute(l_ctx_pref_datastore, 'detail_table',
108 l_icx_schema_name || '.ICX_CAT_ITEMS_CTX_DTLS_TLP');
109 l_err_loc := 530;
110 ctx_ddl.set_attribute(l_ctx_pref_datastore,'detail_key',
111 'po_line_id, inventory_item_id, req_template_name, req_template_line_num, org_id, language');
112 l_err_loc := 540;
113 ctx_ddl.set_attribute(l_ctx_pref_datastore, 'detail_lineno', 'sequence');
114 l_err_loc := 550;
115 ctx_ddl.set_attribute(l_ctx_pref_datastore,'detail_text', 'CTX_DESC');
116
117 -- create the other preferences
118 l_err_loc := 600;
119 ctx_ddl.create_preference('ICX_CAT_FILTER_HDRS', 'NULL_FILTER');
120
121 l_err_loc := 610;
122 ctx_ddl.create_preference('ICX_CAT_WORDLIST_HDRS', 'BASIC_WORDLIST');
123 l_err_loc := 620;
124 ctx_ddl.set_attribute('ICX_CAT_WORDLIST_HDRS', 'STEMMER', 'AUTO');
125 l_err_loc := 630;
126 ctx_ddl.set_attribute('ICX_CAT_WORDLIST_HDRS', 'FUZZY_MATCH', 'AUTO');
127
128 l_err_loc := 700;
129 ctx_ddl.create_preference('ICX_CAT_STORAGE_HDRS', 'BASIC_STORAGE');
130
131 -- Check if the tablespace exists before using it
132 l_err_loc := 710;
133 IF (l_ts_exists = 'Y') THEN
134 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','I_TABLE_CLAUSE',
135 'tablespace '||l_ctx_data_tbsp);
136 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','K_TABLE_CLAUSE',
137 'tablespace '||l_ctx_data_tbsp);
138 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','R_TABLE_CLAUSE',
139 'tablespace '||l_ctx_data_tbsp);
140 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','N_TABLE_CLAUSE',
141 'tablespace '||l_ctx_data_tbsp);
142 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','P_TABLE_CLAUSE',
143 'tablespace '||l_ctx_data_tbsp);
144 ctx_ddl.set_attribute('ICX_CAT_STORAGE_HDRS','I_INDEX_CLAUSE',
145 'tablespace '||l_ctx_index_tbsp);
146 END IF;
147
148 l_err_loc := 720;
149 ctx_ddl.create_section_group(l_ctx_section_group, 'basic_section_group');
150
151 -- add all the field sections
152 l_err_loc := 730;
153 ctx_ddl.add_field_section(l_ctx_section_group, 'source_type', 'source_type', FALSE);
154 l_err_loc := 740;
155 ctx_ddl.add_field_section(l_ctx_section_group, 'supid', 'supid', FALSE);
156 l_err_loc := 750;
157 ctx_ddl.add_field_section(l_ctx_section_group, 'ipcatid', 'ipcatid', FALSE);
158 l_err_loc := 760;
159 ctx_ddl.add_field_section(l_ctx_section_group, 'pocatid', 'pocatid', FALSE);
160 l_err_loc := 770;
161 ctx_ddl.add_field_section(l_ctx_section_group, 'siteid', 'siteid', FALSE);
162 l_err_loc := 780;
163 ctx_ddl.add_field_section(l_ctx_section_group, 'orgid', 'orgid', FALSE);
164 l_err_loc := 790;
165 ctx_ddl.add_field_section(l_ctx_section_group, 'purchorgid', 'purchorgid', FALSE);
166 l_err_loc := 800;
167 ctx_ddl.add_field_section(l_ctx_section_group, 'language', 'language', FALSE);
168 l_err_loc := 810;
169 ctx_ddl.add_field_section(l_ctx_section_group, 'item_type', 'item_type', FALSE);
170
171 l_err_loc := 900;
172 FOR counter in 1..100
173 LOOP
174 l_err_loc := 910;
175 l_ctx_desc_tag := counter;
176 ctx_ddl.add_zone_section(l_ctx_section_group, to_char(counter), l_ctx_desc_tag);
177 END LOOP;
178
179 FOR counter in 1000..1300
180 LOOP
181 l_err_loc := 920;
182 l_ctx_desc_tag := counter;
183 ctx_ddl.add_zone_section(l_ctx_section_group, to_char(counter), l_ctx_desc_tag);
184 END LOOP;
185
186 FOR counter in 5000..5150
187 LOOP
188 l_err_loc := 930;
189 l_ctx_desc_tag := counter;
190 ctx_ddl.add_zone_section(l_ctx_section_group, to_char(counter), l_ctx_desc_tag);
191 END LOOP;
192
193 l_err_loc := 940;
194 ctx_ddl.create_preference('ICX_CAT_LEXER_GLOBAL_HDRS', 'MULTI_LEXER');
195
196 -- we now loop through the installed languages and create
197 -- lexers for each of them
198 l_err_loc := 1000;
199 FOR language_row IN installed_languages_csr LOOP
200 l_lang := language_row.language_code;
201 l_nls_lang := language_row.nls_language;
202
203 l_err_loc := 1010;
204 l_ctx_pref_lexer := 'ICX_CAT_LEXER_HDRS_' || l_lang;
205
206 -- drop the existing lexer preference for this langauge
207 -- don't do anything on exception since preference may not exist
208 BEGIN
209 l_err_loc := 1020;
210 ctx_ddl.drop_preference(l_ctx_pref_lexer);
211 l_err_loc := 1030;
212 EXCEPTION
213 WHEN OTHERS THEN
214 null;
215 END;
216
217 l_err_loc := 1100;
218 -- Now create the lexer preferences and set appropriate attributes
219 IF (l_lang IN ('US', 'GB')) THEN
220 -- American English (US), English (GB)
221 l_err_loc := 1110;
222 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
223
224 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
225 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
226 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
227 ELSIF (l_lang = 'JA') THEN
228 -- Japanese (JA)
229 l_err_loc := 1120;
230 ctx_ddl.create_preference(l_ctx_pref_lexer, 'JAPANESE_VGRAM_LEXER');
231 ELSIF (l_lang = 'KO') THEN
232 -- Korean (KO)
233 l_err_loc := 1130;
234 ctx_ddl.create_preference(l_ctx_pref_lexer, 'KOREAN_MORPH_LEXER');
235
236 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'VERB_ADJECTIVE', 'TRUE');
237 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'ONE_CHAR_WORD', 'TRUE');
238 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'NUMBER', 'TRUE');
239 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'USER_DIC', 'TRUE');
240 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'STOP_DIC', 'TRUE');
241 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'COMPOSITE', 'COMPONENT_WORD');
242 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'MORPHEME', 'TRUE');
243 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'TO_UPPER', 'TRUE');
244 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'HANJA', 'FALSE');
245 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'LONG_WORD', 'FALSE');
246 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'JAPANESE', 'FALSE');
247 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'ENGLISH', 'TRUE');
248 ELSIF (l_lang IN ('ZHS', 'ZHT')) THEN
249 -- Simplified Chinese (ZHS), Traditional Chinese (ZHT)
250 l_err_loc := 1140;
251 ctx_ddl.create_preference(l_ctx_pref_lexer, 'CHINESE_VGRAM_LEXER');
252 ELSIF (l_lang IN ('F', 'FRC')) THEN
253 -- French (F), Canadian French (FRC)
254 l_err_loc := 1150;
255 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
256
257 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
258 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
259 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
260 ELSIF (l_lang = 'D') THEN
261 -- German (D)
262 l_err_loc := 1160;
263 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
264
265 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
266 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'ALTERNATE_SPELLING', 'GERMAN');
267 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
268 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
269 ELSIF (l_lang = 'I') THEN
270 -- Italian (I)
271 l_err_loc := 1170;
272 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
273
274 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
275 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
276 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
277 ELSIF (l_lang in ('E','ESA')) THEN
278 -- Spanish (E), Latin American Spanish (ESA)
279 l_err_loc := 1180;
280 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
281
282 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
283 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
284 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
285 ELSIF (l_lang = 'NL') THEN
286 -- Dutch (NL)
287 l_err_loc := 1190;
288 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
289
290 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
291 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
292 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
293 -- 16177309 Starts
294 ELSIF (l_lang = 'SQ' OR l_lang = 'AZ') then
295 -- AZERBAIJANI(AZ), Albanian (SQ)
296 l_err_loc := 11901;
297 ctx_ddl.create_preference(l_ctx_pref_lexer, 'WORLD_LEXER');
298 -- 16177309 Ends
299 ELSE
300 -- All other languages
301 l_err_loc := 1200;
302 ctx_ddl.create_preference(l_ctx_pref_lexer, 'BASIC_LEXER');
303 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'INDEX_THEMES', 'NO');
304 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'PRINTJOINS', '-_');
305 ctx_ddl.set_attribute(l_ctx_pref_lexer, 'BASE_LETTER', 'YES');
306 END IF;
307
308 -- 16177309 Starts
309 if (l_lang <> 'SQ' AND l_lang <> 'AZ') then
310 -- AZERBAIJANI(AZ), Albanian (SQ)
311 l_err_loc := 1210;
312 -- add a sub lexer for this language
313 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL_HDRS', l_nls_lang,
314 l_ctx_pref_lexer);
315
316 l_err_loc := 1220;
317 -- make the base language sub lexer the default
318 IF (language_row.installed_flag = 'B') THEN
319 ctx_ddl.add_sub_lexer('ICX_CAT_LEXER_GLOBAL_HDRS', 'default',
320 l_ctx_pref_lexer);
321 END IF;
322 END IF;
323 END LOOP;
324
325 -- Now we drop the index if it exists
326 l_err_loc := 1300;
327 BEGIN
328 execute immediate 'DROP INDEX '|| l_icx_schema_name || '.ICX_CAT_ITEMSCTXDESC_HDRS';
329 EXCEPTION
330 WHEN OTHERS THEN
331 null;
332 END;
333
334 l_err_loc := 1400;
335 BEGIN
336 -- Get the parameters to be used for create index to make it faster.
337 -- The SQL to get degree of parallelism is:
338 SELECT MIN(TO_NUMBER(value))
339 INTO l_parallel
340 FROM v$parameter
341 WHERE name IN ('parallel_max_servers', 'cpu_count');
342
343 l_err_loc := 1500;
344 -- The SQL to get memory / worker is:
345 SELECT ROUND(LEAST((LEAST(2147483648 , TO_NUMBER(sys_params.value)/3) / l_parallel), ctx_params.par_value) / 1048576)
346 INTO l_mem
347 FROM v$parameter sys_params, ctx_parameters ctx_params
348 WHERE sys_params.name IN ('pga_aggregate_target')
349 AND ctx_params.par_name = 'MAX_INDEX_MEMORY';
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_parallel := NULL;
353 l_mem := NULL;
354 ICX_CAT_UTIL_PVT.logUnexpectedException(
355 G_PKG_NAME, l_api_name,
356 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
357 END;
358
359 l_err_loc := 1600;
360 IF ( l_parallel IS NOT NULL AND
361 l_mem IS NOT NULL ) THEN
362 l_parameter := ' MEMORY ' || l_mem || 'M' || ''') parallel ' || l_parallel;
363 ELSE
364 l_parameter := ''')';
365 END IF;
366
367 l_err_loc := 1700;
368 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
370 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
371 'Intermedia Index parameters: l_parallel:' || l_parallel ||
372 ', l_mem:' || l_mem ||
373 ', l_parameter:' || l_parameter);
374 END IF;
375
376 l_err_loc := 1800;
377 BEGIN
378 EXECUTE IMMEDIATE
379 'CREATE INDEX ' || l_icx_schema_name || '.ICX_CAT_ITEMSCTXDESC_HDRS' ||
380 ' ON ' || l_icx_schema_name || '.ICX_CAT_ITEMS_CTX_HDRS_TLP(CTX_DESC' ||
381 ') INDEXTYPE IS CTXSYS.CONTEXT ' ||
382 'PARAMETERS(''DATASTORE ' || l_apps_schema_name || '.ICX_CAT_DATASTORE_HDRS' ||
383 ' FILTER ' || l_apps_schema_name || '.ICX_CAT_FILTER_HDRS' ||
384 ' LEXER ' || l_apps_schema_name || '.ICX_CAT_LEXER_GLOBAL_HDRS' ||
385 ' LANGUAGE COLUMN LANGUAGE' ||
386 ' WORDLIST ' || l_apps_schema_name || '.ICX_CAT_WORDLIST_HDRS' ||
387 ' STORAGE ' || l_apps_schema_name || '.ICX_CAT_STORAGE_HDRS' ||
388 ' STOPLIST CTXSYS.EMPTY_STOPLIST' ||
389 ' SECTION GROUP ' || l_apps_schema_name || '.ICX_CAT_SECTGRP_HDRS' ||
390 l_parameter;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 RAISE_APPLICATION_ERROR(-20000,
395 'Exception at ICX_CAT_INTERMEDIA_INDEX_PVT.create_index('
396 || l_err_loc || '): ' || SQLERRM );
397 END;
398
399 l_err_loc := 1900;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 RAISE_APPLICATION_ERROR(-20000,
404 'Exception at ICX_CAT_INTERMEDIA_INDEX_PVT.create_index('
405 || l_err_loc || '): ' || SQLERRM);
406
407 END create_index;
408
409 /**
410 ** Proc : drop_index
411 ** Desc : Drop the index for each installed language in FND_LANGUAGES,
412 ** including the base language.
413 **/
414
415 PROCEDURE drop_index
416 IS
417 l_err_loc PLS_INTEGER;
418
419 l_ctx_section_group VARCHAR2(30) := 'ICX_CAT_SECTGRP_HDRS';
420 l_ctx_pref_lexer VARCHAR2(30) := NULL;
421 l_ctx_pref_datastore VARCHAR2(30) := 'ICX_CAT_DATASTORE_HDRS';
422
423 l_icx_schema_name VARCHAR2(30) := NULL;
424 BEGIN
425 l_err_loc := 100;
426
427 l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
428
429 -- drop the existing preferences
430 BEGIN
431 l_err_loc := 110;
432 ctx_ddl.drop_preference(l_ctx_pref_datastore);
433 l_err_loc := 120;
434 ctx_ddl.drop_preference('ICX_CAT_FILTER_HDRS');
435 l_err_loc := 130;
436 ctx_ddl.drop_preference('ICX_CAT_LEXER_GLOBAL_HDRS');
437 l_err_loc := 140;
438 ctx_ddl.drop_preference('ICX_CAT_WORDLIST_HDRS');
439 l_err_loc := 150;
440 ctx_ddl.drop_preference('ICX_CAT_STORAGE_HDRS');
441 l_err_loc := 160;
442 ctx_ddl.drop_section_group(l_ctx_section_group);
443 l_err_loc := 170;
444 EXCEPTION
445 WHEN OTHERS THEN
446 NULL;
447 END;
448
449 FOR language_row IN installed_languages_csr LOOP
450 l_err_loc := 200;
451 -- language-specific preference settings
452 l_ctx_pref_lexer := 'ICX_CAT_LEXER_HDRS_' || language_row.language_code;
453
454 BEGIN
455 l_err_loc := 210;
456 ctx_ddl.drop_preference(l_ctx_pref_lexer);
457 EXCEPTION
458 WHEN OTHERS THEN
459 NULL;
460 END;
461 END LOOP;
462
463 -- finally drop the index
464 l_err_loc := 300;
465 BEGIN
466 execute immediate 'DROP INDEX ' || l_icx_schema_name || '.ICX_CAT_ITEMSCTXDESC_HDRS';
467 EXCEPTION
468 WHEN OTHERS THEN
469 NULL;
470 END;
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 RAISE_APPLICATION_ERROR(-20000,
475 'Exception at ICX_CAT_INTERMEDIA_INDEX_PVT.drop_index('
476 || l_err_loc || '): ' || SQLERRM);
477
478 END drop_index;
479
480 /**
481 ** Proc : rebuild_index
482 ** Desc : Rebuild the index for each installed language in FND_LANGUAGES,
483 ** including the base language.
484 **/
485
486 PROCEDURE rebuild_index IS
487 l_err_loc PLS_INTEGER;
488 l_icx_schema_name VARCHAR2(30) := NULL;
489 BEGIN
490 l_err_loc := 100;
491
492 l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
493
494 l_err_loc := 200;
495 ad_ctx_ddl.sync_index(l_icx_schema_name || '.ICX_CAT_ITEMSCTXDESC_HDRS');
496
497 l_err_loc := 300;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 RAISE_APPLICATION_ERROR(-20000,
502 'Exception at ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index('
503 || l_err_loc || '): ' || SQLERRM);
504
505 END rebuild_index;
506
507
508 END ICX_CAT_INTERMEDIA_INDEX_PVT;