[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_TEXT_PVT
Source
1 PACKAGE BODY EGO_ITEM_TEXT_PVT AS
2 /* $Header: EGOVIDXB.pls 120.10.12020000.4 2012/07/27 22:30:39 trudave ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_TEXT_PVT';
5
6 -- -----------------------------------------------------------------------------
7 -- Private Globals
8 -- -----------------------------------------------------------------------------
9
10 g_Ctx_Schema CONSTANT VARCHAR2(30) := 'CTXSYS';
11 --g_Apps_Schema VARCHAR2(30);
12 g_Prod_Schema VARCHAR2(30);
13 g_Index_Owner VARCHAR2(30);
14 g_Pref_Owner VARCHAR2(30);
15
16 g_installed BOOLEAN;
17 g_inst_status VARCHAR2(1);
18 g_industry VARCHAR2(1);
19
20 --g_Debug VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
21 --g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
22
23 -- Log mode
24 g_Log_Mode VARCHAR2(30) := NULL;
25 g_Conc_Req_flag BOOLEAN := TRUE;
26 g_Log_Sqlplus_Flag BOOLEAN := FALSE;
27 g_Log_File_Flag BOOLEAN := FALSE;
28 g_Log_Dbdrv_Flag BOOLEAN := FALSE;
29
30 g_Msg_Text VARCHAR2(1000);
31
32 -- Log directory
33 g_Log_Dir v$parameter.value%TYPE;
34 g_Dump_Dir v$parameter.value%TYPE;
35
36 -- Log files for Sqlplus
37 g_Log_File VARCHAR2(30) := 'ego_item_text_tl_srs.log';
38 g_Out_File VARCHAR2(30) := 'ego_item_text_tl_srs.out';
39
40 -- -----------------------------------------------------------------------------
41 -- Log_Line
42 -- -----------------------------------------------------------------------------
43
44 PROCEDURE Log_Line ( p_Buffer IN VARCHAR2 )
45 IS
46 BEGIN
47 IF ( g_Log_File_Flag ) THEN
48 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
49 FND_FILE.Put_Line (FND_FILE.Log, SUBSTRB(p_Buffer, l*240 + 1, 240));
50 END LOOP;
51 END IF;
52
53 IF ( g_Log_Sqlplus_Flag ) THEN
54 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
55 --DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
56 NULL;
57 END LOOP;
58 END IF;
59
60 IF ( g_Log_Dbdrv_Flag ) THEN
61 --IF ( (INSTR(p_Buffer, 'Error:') + INSTR(p_Buffer, 'Warning:')) > 0 ) THEN
62 IF ( ( INSTR(p_Buffer, 'Error:') > 0 )
63 OR ( INSTR(p_Buffer, 'Warning:') > 0 )
64 OR ( INSTR(p_Buffer, 'Executing:') > 0 )
65 OR ( INSTR(p_Buffer, 'Done.') > 0 )
66 OR ( INSTR(p_Buffer, 'Upgrade:') > 0 ) )
67 THEN
68 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
69 END IF;
70 END IF;
71 END Log_Line;
72
73 PROCEDURE Out_Line ( p_Buffer IN VARCHAR2 )
74 IS
75 BEGIN
76 IF ( g_Log_File_Flag ) THEN
77 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/240 ) ) LOOP
78 FND_FILE.Put_Line (FND_FILE.Output, SUBSTRB(p_Buffer, l*240 + 1, 240));
79 END LOOP;
80 END IF;
81
82 IF ( g_Log_Sqlplus_Flag ) THEN
83 FOR l IN 0 .. ( FLOOR( (NVL(LENGTH(p_Buffer),0) - 1)/255 ) ) LOOP
84 --DBMS_OUTPUT.Put_Line( SUBSTR(p_Buffer, l*255 + 1, 255) );
85 NULL;
86 END LOOP;
87 END IF;
88
89 IF ( g_Log_Dbdrv_Flag ) THEN
90 IF ( INSTR(p_Buffer, 'Completed ') > 0 ) THEN
91 g_Msg_Text := g_Msg_Text || SUBSTRB(p_Buffer, 1, 255) || FND_GLOBAL.Newline;
92 END IF;
93 END IF;
94 END Out_Line;
95
96 -- -----------------------------------------------------------------------------
97 -- set_Log_Mode
98 -- -----------------------------------------------------------------------------
99
100 FUNCTION set_Log_Mode ( p_Mode IN VARCHAR2 )
101 RETURN VARCHAR2
102 IS
103 l_api_name CONSTANT VARCHAR2(30) := 'set_Log_Mode';
104 BEGIN
105 g_Log_Mode := p_Mode;
106
107 g_Log_Sqlplus_Flag := ( INSTR(g_Log_Mode, 'SQLPLUS') > 0 );
108 g_Log_File_Flag := ( (INSTR(g_Log_Mode, 'FILE') + INSTR(g_Log_Mode, 'SRS')) > 0 );
109 g_Log_Dbdrv_Flag := ( INSTR(g_Log_Mode, 'DBDRV') > 0 );
110
111 g_Msg_Text := NULL;
112
113 -- Determine log directory
114
115 BEGIN
116 SELECT value INTO g_Dump_Dir
117 FROM v$parameter WHERE name = 'user_dump_dest';
118
119 SELECT TRANSLATE(LTRIM(value), ',', ' ') INTO g_Log_Dir
120 FROM v$parameter
121 WHERE name = 'utl_file_dir';
122
123 IF ( g_Log_Dir IS NOT NULL ) THEN
124 IF ( INSTR(g_Log_Dir, ' ') > 0 ) THEN
125 g_Log_Dir := SUBSTRB(g_Log_Dir, 1, INSTR(g_Log_Dir,' ')-1);
126 END IF;
127 END IF;
128
129 --EXCEPTION
130 -- WHEN others THEN
131 -- Log_Line ('Error determining CTX log directory: ' || SQLERRM);
132 END;
133
134 IF ( NVL(FND_GLOBAL.CONC_REQUEST_ID,-1) < 0 ) THEN
135 g_Conc_Req_flag := FALSE;
136 FND_FILE.Put_Names (g_Log_File, g_Out_File, g_Log_Dir);
137 Log_Line (l_api_name || ': standalone execution');
138 ELSE
139 Log_Line (l_api_name || ': concurrent request');
140 END IF;
141
142 Log_Line (l_api_name || ': log mode: ' || g_Log_Mode);
143 Log_Line (l_api_name || ': dump directory: ' || g_Dump_Dir);
144 Log_Line (l_api_name || ': CTX log directory: ' || g_Log_Dir);
145
146 RETURN (NULL);
147
148 END set_Log_Mode;
149
150 PROCEDURE set_Log_Mode ( p_Mode IN VARCHAR2 )
151 IS
152 l_output_name VARCHAR2(255);
153 BEGIN
154 l_output_name := set_Log_Mode (p_Mode);
155 END set_Log_Mode;
156
157 --exclude delimiters in KFF SYSTEM_ITEMS, DFF MTL_ITEM_CATALOG_GROUPS, Profile EGO_ITEM_IM_SEARCH_DELIMTER for bug 10274574
158 FUNCTION get_printjoins (p_printjoins IN VARCHAR2)
159 RETURN VARCHAR2
160 IS
161 CURSOR delimiter
162 IS
163 SELECT id_flex_num, fnd_flex_ext.get_delimiter ('INV', 'MSTK', id_flex_num) delimiter
164 FROM fnd_id_flex_structures
165 WHERE id_flex_code = 'MSTK';
166
167 p_result VARCHAR2 (30) := NULL;
168 p_dff VARCHAR2 (30) := NULL;
169 p_profile VARCHAR2 (30) := fnd_profile.VALUE ('EGO_ITEM_IM_SEARCH_DELIMTER');
170 BEGIN
171 FOR i IN delimiter
172 LOOP
173 IF (INSTR (p_printjoins, i.delimiter) > -1)
174 THEN
175 p_result := REPLACE (p_printjoins, i.delimiter, '');
176 END IF;
177 END LOOP;
178
179 SELECT concatenated_segment_delimiter
180 INTO p_dff
181 FROM fnd_descriptive_flexs
182 WHERE descriptive_flexfield_name LIKE 'MTL_ITEM_CATALOG_GROUPS';
183
184 IF (p_dff IS NOT NULL)
185 THEN
186 p_result := REPLACE (p_result, p_dff, '');
187 END IF;
188
189 IF (p_profile IS NOT NULL)
190 THEN
191 FOR i IN 0 .. LENGTH (p_profile) - 1
192 LOOP
193 p_result := REPLACE (p_result, SUBSTR (p_profile, 1 + i, 1), '');
194 END LOOP;
195 END IF;
196
197 RETURN p_result;
198 END get_printjoins;
199
200 -- -----------------------------------------------------------------------------
201 -- Process_Index_Preferences
202 -- -----------------------------------------------------------------------------
203
204 PROCEDURE Process_Index_Preferences
205 (
206 p_Index_Name IN VARCHAR2
207 , x_return_status OUT NOCOPY VARCHAR2
208 )
209 IS
210 l_api_name CONSTANT VARCHAR2(30) := 'Process_Index_Preferences';
211 l_return_status VARCHAR2(1);
212
213 tspace_tbl_param VARCHAR2(256);
214 tspace_idx_param VARCHAR2(256);
215
216 Lang_Code VARCHAR2(4);
217 Lexer_Name VARCHAR2(30);
218
219 l_pref_name VARCHAR2(30);
220
221 TYPE Lang_Code_List_type IS TABLE OF VARCHAR2(4);
222 --TYPE Lang_ISO_List_type IS TABLE OF VARCHAR2(2);
223
224 Lang_Code_List Lang_Code_List_type := Lang_Code_List_type
225 ( 'US', 'GB', 'NL', 'D', 'DK', 'S', 'N',
226 'F', 'I', 'E', 'ESA', 'EL',
227 'JA', 'KO', 'ZHS', 'ZHT' );
228
229 --Lang_ISO_List Lang_ISO_List_type :=
230 -- Lang_ISO_List_type ('EN', '', 'DE', 'SV', 'NO', 'FR', '', '');
231
232 BEGIN
233 Log_Line (l_api_name || ': begin');
234
235 l_return_status := G_STATUS_SUCCESS;
236
237 ------------------------------
238 -- Drop existing preferences
239 ------------------------------
240
241 Log_Line (l_api_name || ': dropping all existing preferences ...');
242
243 l_pref_name := 'EGO_ITEM_MULTI_LEXER';
244 FOR multi_lexer_rec IN ( SELECT pre_owner, pre_name
245 FROM ctxsys.ctx_preferences
246 WHERE pre_name = l_pref_name )
247 LOOP
248 ad_ctx_ddl.drop_preference (multi_lexer_rec.pre_owner ||'.'|| multi_lexer_rec.pre_name);
249 END LOOP;
250
251 FOR sub_lexer_rec IN ( SELECT pre_owner, pre_name
252 FROM ctxsys.ctx_preferences
253 WHERE pre_name LIKE 'EGO_ITEM_LEXER%' )
254 LOOP
255 ad_ctx_ddl.drop_preference (sub_lexer_rec.pre_owner ||'.'|| sub_lexer_rec.pre_name);
256 END LOOP;
257
258 l_pref_name := 'EGO_ITEM_WORDLIST';
259 FOR wordlist_rec IN ( SELECT pre_owner, pre_name
260 FROM ctxsys.ctx_preferences
261 WHERE pre_name = l_pref_name )
262 LOOP
263 ad_ctx_ddl.drop_preference (wordlist_rec.pre_owner ||'.'|| wordlist_rec.pre_name);
264 END LOOP;
265
266 FOR stoplist_rec IN ( SELECT spl_owner, spl_name
267 FROM ctxsys.ctx_stoplists
268 WHERE spl_name = 'EGO_ITEM_STOPLIST' )
269 LOOP
270 ad_ctx_ddl.Drop_Stoplist (stoplist_rec.spl_owner || '.EGO_ITEM_STOPLIST');
271 END LOOP;
272
273 FOR section_group_rec IN ( SELECT sgp_owner, sgp_name
274 FROM ctxsys.ctx_section_groups
275 WHERE sgp_name = 'EGO_ITEM_SECTION_GROUP' )
276 LOOP
277 ad_ctx_ddl.Drop_Section_Group (section_group_rec.sgp_owner ||'.'|| section_group_rec.sgp_name);
278 END LOOP;
279
280 l_pref_name := 'EGO_ITEM_DATASTORE';
281 FOR datastore_rec IN ( SELECT pre_owner, pre_name
282 FROM ctxsys.ctx_preferences
283 WHERE pre_name = l_pref_name )
284 LOOP
285 ad_ctx_ddl.drop_preference (datastore_rec.pre_owner ||'.'|| datastore_rec.pre_name);
286 END LOOP;
287
288 l_pref_name := 'EGO_ITEM_STORAGE';
289 FOR storage_rec IN ( SELECT pre_owner, pre_name
290 FROM ctxsys.ctx_preferences
291 WHERE pre_name = l_pref_name )
292 LOOP
293 ad_ctx_ddl.drop_preference (storage_rec.pre_owner ||'.'|| storage_rec.pre_name);
294 END LOOP;
295
296 ------------------------------
297 -- Create STORAGE preference
298 ------------------------------
299
300 -- Index tables use the same tablespaces used by other EGO tables and indexes
301 -- or use logical tablespace for indexes (TRANSACTION_INDEXES).
302
303 Log_Line (l_api_name || ': querying tablespace parameters ...');
304
305 SELECT 'tablespace ' || tablespace_name ||
306 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
307 INTO tspace_tbl_param
308 FROM all_tables
309 WHERE owner = g_Prod_Schema AND table_name = 'EGO_ITEM_TEXT_TL';
310
311 SELECT 'tablespace ' || tablespace_name ||
312 ' storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)'
313 INTO tspace_idx_param
314 FROM all_indexes
315 WHERE owner = g_Prod_Schema
316 AND index_name = 'EGO_ITEM_TEXT_TL_U1'
317 AND table_name = 'EGO_ITEM_TEXT_TL';
318
319 Log_Line (l_api_name || ': creating STORAGE preference ...');
320
321 ad_ctx_ddl.create_preference (g_Pref_Owner || '.EGO_ITEM_STORAGE', 'BASIC_STORAGE');
322
323 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
324 'I_TABLE_CLAUSE', tspace_tbl_param);
325
326 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
327 'K_TABLE_CLAUSE', tspace_tbl_param);
328
329 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
330 'R_TABLE_CLAUSE', tspace_tbl_param || ' LOB (data) STORE AS (CACHE)');
331
332 -- Caching the "data" LOB column is the default (at later versions of Oracle Text).
333 -- For index specific STORAGE preference, setting the clause "lob (data) (cache reads)"
334 -- should be ensured (the "lob .. store as" clause is only for newly added LOB columns).
335 --alter table dr$prd_ctx_index$r modify lob (data) (cache reads);
336
337 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
338 'N_TABLE_CLAUSE', tspace_tbl_param);
339
340 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
341 'P_TABLE_CLAUSE', tspace_tbl_param);
342
343 ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
344 'I_INDEX_CLAUSE', tspace_idx_param || ' COMPRESS 2');
345
346 --ad_ctx_ddl.set_attribute (g_Pref_Owner || '.EGO_ITEM_STORAGE',
347 -- 'I_ROWID_INDEX_CLAUSE', tspace_idx_param);
348
349 --------------------------------
350 -- Create DATASTORE preference
351 --------------------------------
352
353 Log_Line (l_api_name || ': creating DATASTORE preference ...');
354
355 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'USER_DATASTORE');
356
357 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'OUTPUT_TYPE', 'CLOB');
358 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'PROCEDURE', 'APPS.EGO_ITEM_TEXT_CTX_PKG.Get_Item_Text_CLOB'); --remove double quotes for bug 13396394
359
360 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'OUTPUT_TYPE', 'VARCHAR2');
361 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_DATASTORE', 'PROCEDURE', '"EGO_ITEM_TEXT_CTX_PKG"."Get_Item_Text_VARCHAR2"');
362
363 ------------------------------------
364 -- Create SECTION GROUP preference
365 ------------------------------------
366
367 Log_Line (l_api_name || ': creating SECTION_GROUP preference ...');
368
369 ad_ctx_ddl.Create_Section_Group (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'BASIC_SECTION_GROUP');
370 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_ITEM_SECTION', 'item');
371 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_DESC_SECTION', 'desc');
372 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_SHORTDESC_SECTION', 'shortdesc');
373 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_LONGDESC_SECTION', 'longdesc');
377 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_MPN_SECTION', 'mpn');
374 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_CAT_SECTION', 'cat');
375 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_AML_SECTION', 'aml');
376 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_MFG_SECTION', 'mfg');
378 --Bug 5094325 begin
379 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_ORG_SECTION', 'org');
380 ad_ctx_ddl.Add_Zone_Section (g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP', 'EGO_ITEM_LANG_SECTION', 'lang');
381 --Bug 5094325 end
382
383 -------------------------------
384 -- Create STOPLIST preference
385 -------------------------------
386
387 --We are sticking with the default stoplist, containing several common English words and abbreviations
388 --Log_Line (l_api_name || ': creating STOPLIST preference ...');
389
390 -- This should create stoplist equivalent to CTXSYS.EMPTY_STOPLIST
391 --ad_ctx_ddl.Create_Stoplist (g_Pref_Owner || '.EGO_ITEM_STOPLIST');
392
393 -------------------------------
394 -- Create WORDLIST preference
395 -------------------------------
396
397 Log_Line (l_api_name || ': creating WORDLIST preference ...');
398
399 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'BASIC_WORDLIST');
400
401 -- Enable prefix indexing to improve performance for wildcard searches
402 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_INDEX', 'TRUE');
403 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_MIN_LENGTH', 2);
404 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_LENGTH_MIN', 2);
405 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_MAX_LENGTH', 32);
406 --ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'PREFIX_LENGTH_MAX', 32);
407 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'WILDCARD_MAXTERMS', 5000);
408
409 -- This option should be TRUE only when left-truncated wildcard searching is expected
410 -- to be frequent and needs to be fast (at the cost of increased index time and space).
411 --
412 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'SUBSTRING_INDEX', 'FALSE');
413
414 -- WORDLIST attribute defaults: STEMMER: 'ENGLISH'; FUZZY_MATCH: 'GENERIC'
415 -- Use automatic language detection for stemming and fuzzy matching
416 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'STEMMER', 'AUTO');
417 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_MATCH', 'AUTO');
418 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_SCORE', 40);
419 ad_ctx_ddl.Set_Attribute (g_Pref_Owner || '.EGO_ITEM_WORDLIST', 'FUZZY_NUMRESULTS', 120);
420
421 -----------------------------------------------
422 -- Create language-specific LEXER preferences
423 -----------------------------------------------
424
425 Log_Line (l_api_name || ': creating language-specific LEXER preferences ...');
426
427 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
428 LOOP
429 Lexer_Name := g_Pref_Owner || '.EGO_ITEM_LEXER_' || Lang_Code_List(i);
430
431 IF ( Lang_Code_List(i) = 'JA' ) THEN
432
433 -- Use JAPANESE_LEXER if db charset is UTF8, JA16SJIS, or JA16EUC.
434 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.0 ) THEN
435 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_LEXER');
436 ELSE
437 ad_ctx_ddl.Create_Preference (Lexer_Name, 'JAPANESE_VGRAM_LEXER');
438 END IF;
439
440 ELSIF ( Lang_Code_List(i) = 'KO' ) THEN
441
442 -- Use KOREAN_MORPH_LEXER if db charset is UTF8 or KO16KSC5601.
443 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.0 ) THEN
444 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_MORPH_LEXER');
445 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB_ADJECTIVE', 'TRUE');
446 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONE_CHAR_WORD', 'TRUE');
447 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
448 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'NGRAM');
449 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
450 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'HANJA', 'FALSE');
451 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'LONG_WORD', 'TRUE');
452 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'JAPANESE', 'FALSE');
453 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ENGLISH', 'TRUE');
454 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TO_UPPER', 'TRUE');
455 ELSE
456 ad_ctx_ddl.Create_Preference (Lexer_Name, 'KOREAN_LEXER');
457 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'VERB', 'TRUE');
458 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADJECTIVE', 'TRUE');
459 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ADVERB', 'TRUE');
460 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ONECHAR', 'TRUE');
461 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMBER', 'TRUE');
462 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'TRUE');
463 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MORPHEME', 'TRUE');
464 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOHANGEUL', 'TRUE');
465 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'TOUPPER', 'TRUE');
466 END IF;
467
468 ELSIF ( Lang_Code_List(i) IN ('ZHS', 'ZHT') ) THEN
469
470 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
471 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_LEXER');
472 ELSE
473 ad_ctx_ddl.Create_Preference (Lexer_Name, 'CHINESE_VGRAM_LEXER');
474 END IF;
475
476 ELSE
477 -- All other languages use basic lexer.
478
479 ad_ctx_ddl.Create_Preference (Lexer_Name, 'BASIC_LEXER');
483
480
481 -- The following language-independent attributes are
482 -- common to the BASIC_LEXER preference object.
484 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_TEXT', 'YES');
485 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_THEMES', 'NO');
486
487 -- For printjoin characters include all possible flex segment separators
488 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PRINTJOINS', get_printjoins('-_*~^+.$#@:|&=')); --for bug 8465088, append '=' as PRINTJOINS; for bug 10274574, we exclude KFF delimiters
489 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'CONTINUATION', '-\');
490 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'PUNCTUATIONS', '.?!');
491
492 -- The default values for numjoin and numgroup are determined by
493 -- the NLS initialization parameters that are specified for the database.
494 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMGROUP', ',');
495 --ad_ctx_ddl.Set_Attribute (Lexer_Name, 'NUMJOIN', '.');
496
497 -- Stem indexing stems tokens at indexing time to a single base form in addition
498 -- to the normal forms. This enables better query performance for stem ($) queries.
499
500 -- Disable stem indexing to improve index creation performance.
501 -- This would not affect stem expansion (with $) at query time.
502 --
503 --IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
504 -- IF ( Lang_Code_List(i) IN ('US', 'GB') ) THEN
505 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ENGLISH');
506 -- ELSIF ( Lang_Code_List(i) = 'NL' ) THEN
507 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'DUTCH');
508 -- ELSIF ( Lang_Code_List(i) = 'D' ) THEN
509 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'GERMAN');
510 -- ELSIF ( Lang_Code_List(i) = 'F' ) THEN
511 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'FRENCH');
512 -- ELSIF ( Lang_Code_List(i) = 'I' ) THEN
513 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'ITALIAN');
514 -- ELSIF ( Lang_Code_List(i) IN ('E', 'ESA') ) THEN
515 -- ad_ctx_ddl.Set_Attribute (Lexer_Name, 'INDEX_STEMS', 'SPANISH');
516 -- END IF;
517 --END IF;
518
519 -- Language-specific attribute values for BASIC_LEXER preference object
520
521 IF ( Lang_Code_List(i) = 'NL' ) THEN
522 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'DUTCH');
523
524 ELSIF ( Lang_Code_List(i) = 'D' ) THEN
525 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'COMPOSITE', 'GERMAN');
526 -- Basic lexer in 8.1.7 allows the MIXED_CASE to be FALSE when COMPOSITE is set.
527 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'MIXED_CASE', 'NO');
528 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'GERMAN');
529
530 ELSIF ( Lang_Code_List(i) = 'DK' ) THEN
531 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
532
533 ELSIF ( Lang_Code_List(i) = 'S' ) THEN
534 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'SWEDISH');
535
536 ELSIF ( Lang_Code_List(i) = 'N' ) THEN
537 -- Both Norwegian and Danish use the same special characters that are
538 -- rendered alternatively as "aa", "ae", and "oe".
539 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'DANISH');
540
541 ELSIF ( Lang_Code_List(i) = 'F' ) THEN
542 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'BASE_LETTER', 'YES');
543
544 ELSE
545 ad_ctx_ddl.Set_Attribute (Lexer_Name, 'ALTERNATE_SPELLING', 'NONE');
546
547 END IF;
548 END IF;
549
550 END LOOP; -- Lang_Code_List
551
552 ----------------------------------
553 -- Create MULTI_LEXER preference
554 ----------------------------------
555
556 Log_Line (l_api_name || ': creating MULTI_LEXER preference ...');
557
558 ad_ctx_ddl.Create_Preference (g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER', 'MULTI_LEXER');
559
560 FOR i IN Lang_Code_List.FIRST .. Lang_Code_List.LAST
561 LOOP
562 Lexer_Name := g_Pref_Owner || '.EGO_ITEM_LEXER_' || Lang_Code_List(i);
563
564 -- The language column is case-independent, and can contain either the NLS name
565 -- or abbreviation of the language. If the table uses some other value for the
566 -- language, that alternate value needs to be specified as the fourth argument
567 -- when adding the sub lexers:
568
569 IF ( Lang_Code_List(i) = 'US' ) THEN
570 -- US English lexer to handle everything else.
571 ad_ctx_ddl.Add_Sub_Lexer ( g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER', 'DEFAULT'
572 , g_Pref_Owner || '.EGO_ITEM_LEXER_US' );
573 ELSE
574 ad_ctx_ddl.Add_Sub_Lexer
575 ( lexer_name => g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER'
576 , language => Lang_Code_List(i)
577 , sub_lexer => Lexer_Name
578 --, alt_value => Lang_ISO_List(i)
579 );
580 END IF;
581
582 END LOOP;
583
584 x_return_status := l_return_status;
585
586 Log_Line (l_api_name || ': end');
587
588 EXCEPTION
589
590 WHEN G_EXC_ERROR THEN
591 x_return_status := G_STATUS_ERROR;
592
593 WHEN others THEN
594 Log_Line (l_api_name || ': Error: ' || SQLERRM);
595 x_return_status := G_STATUS_UNEXP_ERROR;
596
597 END Process_Index_Preferences;
598
599 -- -----------------------------------------------------------------------------
600 -- Process_Item_Text_Index
601 -- -----------------------------------------------------------------------------
602
606 , p_Policy_Name IN VARCHAR2
603 PROCEDURE Process_Item_Text_Index
604 (
605 p_Index_Name IN VARCHAR2
607 , p_Action IN VARCHAR2
608 , x_return_status OUT NOCOPY VARCHAR2
609 )
610 IS
611 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Text_Index';
612 l_return_status VARCHAR2(1);
613 ctx_Log_File_Name VARCHAR2(512) := NULL;
614
615 l_index_exists BOOLEAN := FALSE;
616 l_index_valid BOOLEAN := TRUE;
617
618 l_create_index BOOLEAN := TRUE;
619 l_drop_index BOOLEAN := FALSE;
620
621 l_rows_processed INTEGER;
622
623 -- Limit the indexing memory in case the default parameter max value is higher.
624 --l_Index_Memory ctx_parameters.par_value%TYPE := '67108864'; -- '64M'
625 l_Index_Memory ctx_parameters.par_value%TYPE := '134217728'; -- '128M'
626 l_Index_Memory_Max ctx_parameters.par_value%TYPE;
627
628 l_idx_docid_count NUMBER;
629 l_idx_status VARCHAR2(256);
630
631 l_index_populate VARCHAR2(30) := 'POPULATE';
632
633 -- Use parallel indexing when this issue is resolved
634 c_parallel_clause CONSTANT VARCHAR2(30) := 'PARALLEL 4';
635 --c_parallel_clause CONSTANT VARCHAR2(30) := NULL;
636 l_index_parallel VARCHAR2(30) := NULL;
637 sql_stmt VARCHAR2(32767);
638
639 BEGIN
640
641 Log_Line (l_api_name || ': begin: Index_Name=' || p_Index_Name || ' Action=' || p_Action);
642
643 l_return_status := G_STATUS_SUCCESS;
644
645 IF ( p_Action NOT IN ('CREATE', 'REBUILD', 'UPGRADE', 'DROP') ) THEN
646 Log_Line (l_api_name || ': Error: invalid value for parameter p_Action: ' || p_Action);
647 RAISE G_EXC_ERROR;
648 END IF;
649
650 -- Check for existing indexes in the EGO product, APPS Universal, and interMedia schemas.
651 --
652 FOR index_rec IN ( SELECT owner, index_name, status, domidx_status, domidx_opstatus
653 FROM all_indexes
654 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
655 AND table_name = 'EGO_ITEM_TEXT_TL'
656 AND index_name = p_Index_Name )
657 LOOP
658 -- Check index schema
659 --
660 IF ( index_rec.owner <> g_Index_Owner )
661 THEN
662 Log_Line (l_api_name || ': Error: index exists in wrong schema: ' || index_rec.owner);
663 BEGIN
664 Log_Line (l_api_name || ': dropping index: ' || index_rec.owner || '.' || p_Index_Name);
665 EXECUTE IMMEDIATE 'DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE';
666
667 EXCEPTION
668 WHEN others THEN
669 Log_Line (l_api_name || ': Error: DROP INDEX ' || index_rec.owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
670 l_return_status := G_STATUS_ERROR;
671 END;
672
673 ELSE
674 l_index_exists := TRUE;
675
676 -- Check status of an existing index, if any.
677 --
678 IF ( (NVL(index_rec.status, 'FAILED') <> 'VALID') OR
679 (NVL(index_rec.domidx_status, 'FAILED') <> 'VALID') OR
680 (NVL(index_rec.domidx_opstatus, 'FAILED') <> 'VALID') )
681 THEN
682 l_index_valid := FALSE;
683 Log_Line (l_api_name || ': Warning: existing index status is invalid:'
684 || ' status=' || index_rec.status
685 || ' domidx_status=' || index_rec.domidx_status
686 || ' domidx_opstatus=' || index_rec.domidx_opstatus);
687 ELSE
688 Log_Line (l_api_name || ': valid index exists: ' || index_rec.owner || '.' || p_Index_Name);
689 END IF;
690
691 END IF; -- index owner
692
693 END LOOP; -- check for any existing indexes
694
695 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
696 RAISE G_EXC_ERROR;
697 END IF;
698
699 -- x_return_status := l_return_status;
700
701 -- Set indexing flags depending on the action and the index status
702
703 IF ( p_Action = 'CREATE' ) THEN
704 IF ( l_index_exists ) THEN
705 IF ( l_index_valid ) THEN
706 Log_Line (l_api_name || ': Error: cannot execute ' || p_Action || ' because the index exists.');
707 RAISE G_EXC_ERROR;
708 ELSE
709 l_drop_index := TRUE;
710 END IF;
711 END IF;
712
713 ELSIF ( p_Action = 'REBUILD' ) THEN
714 l_drop_index := l_index_exists;
715
716 ELSIF ( p_Action = 'UPGRADE' ) THEN
717 IF ( l_index_exists ) THEN
718 IF ( l_index_valid ) THEN
719 Log_Line (l_api_name || ': Upgrade: skipping index creation because valid index exists.');
720 l_create_index := FALSE;
721 ELSE
722 l_drop_index := TRUE;
723 END IF;
724 END IF;
725
726 ELSIF ( p_Action = 'DROP' ) THEN
727 l_create_index := FALSE;
728 IF ( l_index_exists ) THEN
729 l_drop_index := TRUE;
730 ELSE
731 Log_Line (l_api_name || ': Warning: cannot execute ' || p_Action || ' because the index does not exist.');
732 l_return_status := G_STATUS_WARNING;
733 END IF;
734
735 END IF; -- Action
736
737 IF ( l_drop_index ) THEN
738 BEGIN
739 Log_Line (l_api_name || ': dropping index: ' || g_Index_Owner || '.' || p_Index_Name);
740 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
741
745 ctx_ddl.Drop_Policy(g_Index_Owner || '.' || p_Policy_Name);
742 -- Drop the policy too, if it exists
743 IF (p_Policy_Name IS NOT NULL) THEN
744 BEGIN
746 EXCEPTION
747 WHEN OTHERS THEN
748 Log_Line (l_api_name || ': policy does not exist; cannot be dropped.');
749 END;
750 END IF;
751
752 IF ( l_index_valid ) THEN
753 Log_Line (l_api_name || ': existing index has been successfully dropped.');
754 Out_Line ('Existing index has been successfully dropped: ' || g_Index_Owner || '.' || p_Index_Name);
755 ELSE
756 Log_Line (l_api_name || ': invalid index has been dropped.');
757 Out_Line ('Invalid index has been dropped: ' || g_Index_Owner || '.' || p_Index_Name);
758 END IF;
759 EXCEPTION
760 WHEN others THEN
761 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
762 l_return_status := G_STATUS_ERROR;
763 END;
764 END IF; -- drop index
765
766 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
767 RAISE G_EXC_ERROR;
768 END IF;
769
770 -- Build index
771
772 IF ( l_create_index ) THEN
773
774 -- Determine indexing memory limit parameter value
775 BEGIN
776 SELECT par_value INTO l_Index_Memory_Max
777 FROM ctx_parameters
778 WHERE par_name = 'MAX_INDEX_MEMORY';
779 EXCEPTION
780 WHEN no_data_found THEN
781 Log_Line (l_api_name || ': Error: MAX_INDEX_MEMORY parameter record not found.');
782 RAISE G_EXC_ERROR;
783 END;
784
785 IF ( TO_NUMBER(l_Index_Memory) > TO_NUMBER(l_Index_Memory_Max) ) THEN
786 --l_Index_Memory := l_Index_Memory_Max;
787 -- Adjust limit on memory used for indexing
788 ctxsys.CTX_ADM.Set_Parameter ('MAX_INDEX_MEMORY', l_Index_Memory);
789 END IF;
790
791 Log_Line (l_api_name || ': CTX index_memory: ' || l_Index_Memory);
792
793 -- Decide whether parallel indexing can be used, depending on DB version.
794
795 IF ( EGO_ITEM_TEXT_UTIL.get_DB_Version_Num >= 9.2 ) THEN
796 l_index_parallel := c_parallel_clause;
797 Log_Line (l_api_name || ': DB version: ' || EGO_ITEM_TEXT_UTIL.get_DB_Version_Str || ', using parallel clause: ' || l_index_parallel);
798 END IF;
799
800 -- Start logging indexing progress
801
802 IF ( g_Log_Dir IS NOT NULL ) THEN
803 BEGIN
804 Log_Line (l_api_name || ': CTX Log_Directory: ' || g_Log_Dir);
805 ctxsys.CTX_ADM.Set_Parameter ( 'LOG_DIRECTORY', g_Log_Dir );
806 Log_Line (l_api_name || ': CTX Start_Log');
807 CTX_OUTPUT.Start_Log ( LOWER(p_Index_Name) || '.log' );
808 ctx_Log_File_Name := CTX_OUTPUT.LogFileName;
809 Log_Line (l_api_name || ': CTX LogFileName: ' || ctx_Log_File_Name);
810 EXCEPTION
811 WHEN others THEN
812 Log_Line (l_api_name || ': Warning: CTX Start_Log: ' || SQLERRM);
813 l_return_status := G_STATUS_WARNING;
814 END;
815 END IF;
816
817 -- Choose indexing method
818 --IF ( ) THEN
819 -- l_index_populate := 'NOPOPULATE';
820 --END IF;
821
822 IF ( l_create_index ) THEN
823 BEGIN
824
825 EGO_ITEM_TEXT_UTIL.Set_Context ('CREATE_INDEX');
826
827 sql_stmt :=
828 'CREATE INDEX ' || g_Index_Owner || '.' || p_Index_Name ||
829 ' ON ' || g_Prod_Schema || '.EGO_ITEM_TEXT_TL (text) ' ||
830 ' INDEXTYPE IS CTXSYS.context ' ||
831 ' PARAMETERS ' ||
832 ' (''DATASTORE ' || g_Pref_Owner || '.EGO_ITEM_DATASTORE ' ||
833 ' WORDLIST ' || g_Pref_Owner || '.EGO_ITEM_WORDLIST ' ||
834 --' STOPLIST ' || g_Pref_Owner || '.EGO_ITEM_STOPLIST ' ||
835 ' LEXER ' || g_Pref_Owner || '.EGO_ITEM_MULTI_LEXER ' ||
836 ' LANGUAGE COLUMN language ' ||
837 ' SECTION GROUP ' || g_Pref_Owner || '.EGO_ITEM_SECTION_GROUP ' ||
838 --' SECTION GROUP CTXSYS.NULL_SECTION_GROUP ' ||
839 ' STORAGE ' || g_Pref_Owner || '.EGO_ITEM_STORAGE ' ||
840 ' MEMORY ' || l_Index_Memory ||
841 --' ' || l_index_populate ||
842 ' '')' || ' ' || l_index_parallel ;
843
844 Log_Line (l_api_name || ': creating index ' || g_Index_Owner || '.' || p_Index_Name || ' ...');
845 Log_Line (l_api_name || ': sql_stmt = ' || sql_stmt || ' /* End SQL */');
846
847 EXECUTE IMMEDIATE sql_stmt;
848
849 Log_Line (l_api_name || ': done creating index.');
850
851 EXCEPTION
852
853 WHEN others THEN
854 Log_Line (l_api_name || ': Error creating index ' || g_Index_Owner || '.' || p_Index_Name || ': ' || SQLERRM);
855
856 -- Drop the index in case of an error during index creation to prevent the table lock.
857 BEGIN
858 EXECUTE IMMEDIATE 'DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE';
859 EXCEPTION
860 WHEN others THEN
861 Log_Line (l_api_name || ': Error: DROP INDEX ' || g_Index_Owner || '.' || p_Index_Name || ' FORCE: ' || SQLERRM);
862 RAISE G_EXC_ERROR;
863 END;
864
865 RAISE G_EXC_ERROR;
866
867 END; -- execute sql
871
868 END IF; -- create index
869 /*
870 Log_Line (l_api_name || ': calling Incremental_Sync');
872 EGO_ITEM_TEXT_UTIL.Incremental_Sync
873 (
874 p_Index_Name => p_Index_Name
875 , p_batch_size => 40000
876 , x_rows_processed => l_rows_processed
877 , x_return_status => l_return_status
878 );
879 */
880 -- End logging
881
882 IF ( ctx_Log_File_Name IS NOT NULL ) THEN
883 BEGIN
884 CTX_OUTPUT.End_Log;
885 Log_Line (l_api_name || ': CTX End_Log');
886 EXCEPTION
887 WHEN others THEN
888 Log_Line (l_api_name || ': Warning: CTX End_Log: ' || SQLERRM);
889 l_return_status := G_STATUS_WARNING;
890 END;
891 END IF;
892
893 Out_Line ('Completed building Catalog Item Text Index.');
894
895 -- Check the created index status
896
897 SELECT idx_docid_count, idx_status
898 INTO l_idx_docid_count, l_idx_status
899 FROM ctxsys.ctx_indexes
900 WHERE idx_owner = g_Index_Owner AND idx_name = p_Index_Name
901 AND idx_table = 'EGO_ITEM_TEXT_TL';
902
903 IF NOT( l_idx_status = 'INDEXED' ) THEN
904 Log_Line (l_api_name || ': Error: Index status is ' || l_idx_status || '.');
905 l_return_status := G_STATUS_ERROR;
906 END IF;
907
908 IF ( NVL(l_idx_docid_count, 0) = 0 ) THEN
909 Log_Line (l_api_name || ': Error: Indexed document count is ' || TO_CHAR(l_idx_docid_count) || '.');
910 l_return_status := G_STATUS_ERROR;
911 END IF;
912
913 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
914 RAISE G_EXC_ERROR;
915 END IF;
916
917 -- Copy the policy too, if it doesn't already exist
918 IF (p_Policy_Name IS NOT NULL) THEN
919 BEGIN
920 Log_Line (l_api_name || ': Copying policy from index...');
921 ctx_ddl.Copy_Policy(g_Index_Owner || '.' || p_Index_Name, g_Index_Owner || '.' || p_Policy_Name);
922 EXCEPTION
923 WHEN OTHERS THEN
924 Log_Line (l_api_name || ': policy could not be copied - probably already exists.');
925 END;
926 END IF;
927
928 END IF; -- build index
929
930 x_return_status := l_return_status;
931 /*
932 Out_Line ('Product Catalog Text Index');
933 Out_Line ('--------------------------');
934 Out_Line ('Number of rows indexed: ' || 0);
935 */
936 Log_Line (l_api_name || ': end');
937
938 EXCEPTION
939
940 WHEN G_EXC_ERROR THEN
941 x_return_status := G_STATUS_ERROR;
942
943 WHEN others THEN
944 Log_Line (l_api_name || ': Error: ' || SQLERRM);
945 x_return_status := G_STATUS_UNEXP_ERROR;
946
947 END Process_Item_Text_Index;
948
949 -- -----------------------------------------------------------------------------
950 -- Build_Item_Text_Index
951 -- -----------------------------------------------------------------------------
952
953 PROCEDURE Build_Item_Text_Index
954 (
955 ERRBUF OUT NOCOPY VARCHAR2
956 , RETCODE OUT NOCOPY NUMBER
957 , p_Action IN VARCHAR2
958 )
959 IS
960 l_api_name CONSTANT VARCHAR2(30) := 'Build_Item_Text_Index';
961 l_Index_Name VARCHAR2(30) := 'EGO_ITEM_TEXT_TL_CTX1';
962 l_Policy_Name VARCHAR2(30) := 'EGO_ITEM_TEXT_TL_POL1';
963 l_return_status VARCHAR2(1);
964 BEGIN
965 Log_Line (l_api_name || ': begin: Action=' || p_Action);
966
967 IF ( g_Log_Mode IS NULL ) THEN
968 set_Log_Mode ('FILE');
969 END IF;
970
971 l_return_status := G_STATUS_SUCCESS;
972
973 IF ( p_Action IN ('CREATE', 'REBUILD', 'UPGRADE') ) THEN
974 Log_Line (l_api_name || ': calling Process_Index_Preferences ...');
975
976 Process_Index_Preferences
977 (
978 p_Index_Name => l_Index_Name
979 , x_return_status => l_return_status
980 );
981
982 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
983 RAISE G_EXC_ERROR;
984 END IF;
985
986 Out_Line ('Re-created Text Index preferences.');
987 END IF;
988
989 Log_Line (l_api_name || ': calling Process_Item_Text_Index ...');
990
991 Process_Item_Text_Index
992 (
993 p_Index_Name => l_Index_Name
994 , p_Policy_Name => l_Policy_Name
995 , p_Action => p_Action
996 , x_return_status => l_return_status
997 );
998
999 IF ( l_return_status NOT IN (G_STATUS_SUCCESS, G_STATUS_WARNING) ) THEN
1000 RAISE G_EXC_ERROR;
1001 END IF;
1002
1003 -- Assign conc request return code
1004
1005 IF ( l_return_status = G_STATUS_SUCCESS ) THEN
1006 RETCODE := G_RETCODE_SUCCESS;
1007 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_SUCCESS');
1008 ELSIF ( l_return_status = G_STATUS_WARNING ) THEN
1009 RETCODE := G_RETCODE_WARNING;
1010 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_WARNING');
1011 ELSE
1012 RETCODE := G_RETCODE_ERROR;
1013 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1014 END IF;
1015
1016 IF NOT(g_Conc_Req_flag) THEN
1017 FND_FILE.Close;
1018 END IF;
1019
1020 Log_Line (l_api_name || ': end');
1021
1022 EXCEPTION
1023
1024 WHEN G_EXC_ERROR THEN
1025 Log_Line (l_api_name || ': Error.');
1026 RETCODE := G_RETCODE_ERROR;
1030 IF NOT(g_Conc_Req_flag) THEN
1027 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1028 --ERRBUF := 'Build of Catalog Item Text Index failed. Please check error messages for further details.';
1029
1031 FND_FILE.Close;
1032 END IF;
1033
1034 WHEN others THEN
1035 Log_Line (l_api_name || ': Unexpected Error: ' || SQLERRM);
1036 RETCODE := G_RETCODE_ERROR;
1037 ERRBUF := 'Build of Catalog Item Text Index failed due to unexpected error. Please check error messages for further details.';
1038
1039 IF NOT(g_Conc_Req_flag) THEN
1040 FND_FILE.Close;
1041 END IF;
1042
1043 END Build_Item_Text_Index;
1044
1045 -- -----------------------------------------------------------------------------
1046 -- Report_Item_Text_Index
1047 -- -----------------------------------------------------------------------------
1048 /*
1049 PROCEDURE Report_Item_Text_Index
1050 (
1051 ERRBUF OUT NOCOPY VARCHAR2
1052 , RETCODE OUT NOCOPY NUMBER
1053 )
1054 IS
1055 l_api_name CONSTANT VARCHAR2(30) := 'Report_Item_Text_Index';
1056 l_index_exists BOOLEAN := FALSE;
1057 l_return_status VARCHAR2(1);
1058 BEGIN
1059
1060 Log_Line (l_api_name || ': begin');
1061
1062 IF ( g_Log_Mode IS NULL ) THEN
1063 set_Log_Mode ('FILE');
1064 END IF;
1065
1066 l_return_status := G_STATUS_SUCCESS;
1067
1068 Out_Line ('Product Catalog Text Index');
1069 Out_Line ('--------------------------');
1070
1071 Log_Line (l_api_name || ': loop through DOMAIN type indexes');
1072
1073 FOR index_rec IN ( SELECT owner, index_name, index_type, status, domidx_opstatus
1074 FROM all_indexes
1075 WHERE ( owner = g_Prod_Schema OR owner = USER OR owner = g_Ctx_Schema )
1076 AND table_name = 'EGO_ITEM_TEXT_TL'
1077 AND ( index_type = 'DOMAIN'
1078 OR index_name LIKE 'EGO_ITEM_TEXT_TL_CTX%' )
1079 )
1080 LOOP
1081 l_index_exists := TRUE;
1082
1083 Out_Line ('Index: ' || index_rec.owner || '.' || index_rec.index_name ||
1084 'Type: ' || index_rec.index_type);
1085 Out_Line ('status = ' || index_rec.status || ' opstatus = ' || index_rec.domidx_opstatus);
1086
1087 END LOOP;
1088
1089 IF ( NOT(l_index_exists) ) THEN
1090 Log_Line (l_api_name || ': Warning: no DOMAIN or ''EGO_ITEM_TEXT_TL_CTX%'' indexes exist');
1091 l_return_status := G_STATUS_WARNING;
1092 --RAISE G_EXC_ERROR;
1093 END IF;
1094
1095 -- Assign conc request return code
1096
1097 IF ( l_return_status = G_STATUS_SUCCESS ) THEN
1098 RETCODE := G_RETCODE_SUCCESS;
1099 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_SUCCESS');
1100 ELSIF ( l_return_status = G_STATUS_WARNING ) THEN
1101 RETCODE := G_RETCODE_WARNING;
1102 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_WARNING');
1103 ELSE
1104 RETCODE := G_RETCODE_ERROR;
1105 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1106 END IF;
1107
1108 IF NOT(g_Conc_Req_flag) THEN
1109 FND_FILE.Close;
1110 END IF;
1111
1112 Log_Line (l_api_name || ': end');
1113
1114 EXCEPTION
1115
1116 WHEN G_EXC_ERROR THEN
1117 Log_Line (l_api_name || ': Error.');
1118 RETCODE := G_RETCODE_ERROR;
1119 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_CP_FAILURE');
1120 --ERRBUF := 'Catalog Item Text Index report failed. Please check error messages for further details.';
1121
1122 IF NOT(g_Conc_Req_flag) THEN
1123 FND_FILE.Close;
1124 END IF;
1125
1126 WHEN others THEN
1127 Log_Line (l_api_name || ': Unexpected Error: ' || SQLERRM);
1128 RETCODE := G_RETCODE_ERROR;
1129 ERRBUF := 'Catalog Item Text Index report failed due to unexpected error. Please check error messages for further details.';
1130
1131 IF NOT(g_Conc_Req_flag) THEN
1132 FND_FILE.Close;
1133 END IF;
1134
1135 END Report_Item_Text_Index;
1136 */
1137
1138 -- -----------------------------------------------------------------------------
1139 -- get_Msg_Text
1140 -- -----------------------------------------------------------------------------
1141
1142 FUNCTION get_Msg_Text
1143 RETURN VARCHAR2
1144 IS
1145 BEGIN
1146 RETURN (g_Msg_Text);
1147 END get_Msg_Text;
1148
1149
1150 -- *****************************************************************************
1151 -- ** Package initialization block **
1152 -- *****************************************************************************
1153
1154 BEGIN
1155
1156 -- Get EGO product schema name
1157 --
1158 g_installed := FND_INSTALLATION.Get_App_Info ('EGO', g_inst_status, g_industry, g_Prod_Schema);
1159
1160 g_Index_Owner := g_Prod_Schema;
1161 g_Pref_Owner := g_Prod_Schema;
1162
1163 END EGO_ITEM_TEXT_PVT;