eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKC_CHR_KEYWORD_PVT

Source


1 PACKAGE BODY okc_chr_keyword_pvt AS
2 /* $Header: OKCRCKWB.pls 120.6 2012/04/09 06:57:38 vechittu ship $ */
3 
4   retcode_success   CONSTANT VARCHAR2(1) := '0';
5   retcode_warning   CONSTANT VARCHAR2(1) := '1';
6   retcode_error     CONSTANT VARCHAR2(1) := '2';
7 
8   starting constant number := 0;
9   finish   constant number := 1;
10 
11   rindex   BINARY_INTEGER;
12   slno     BINARY_INTEGER;
13 
14   PROCEDURE sync IS
15   BEGIN
16     ad_ctx_ddl.set_effective_schema('okc');
17     ad_ctx_ddl.sync_index('okc_k_headers_tl_ctx');
18   EXCEPTION
19     WHEN OTHERS THEN
20       RAISE;
21   END;
22 
23   PROCEDURE optimize IS
24   BEGIN
25      ad_ctx_ddl.set_effective_schema('okc');
26      ad_ctx_ddl.optimize_index
27         ( idx_name => 'okc_k_headers_tl_ctx',
28         optlevel => ad_ctx_ddl.optlevel_full,
29         maxtime  => ad_ctx_ddl.maxtime_unlimited);
30   EXCEPTION
31     WHEN OTHERS THEN
32       RAISE;
33   END;
34 
35   PROCEDURE sync_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
36     l_api_name        CONSTANT VARCHAR2(30) := 'concurrent_sync_ctx';
37     l_api_version     CONSTANT VARCHAR2(30) := 1.0;
38   BEGIN
39     sync;
40     retcode := retcode_success;
41   EXCEPTION
42     WHEN OTHERS THEN
43       retcode := retcode_error;
44       errbuf := SUBSTR(sqlerrm,1,200);
45   END;
46 
47   PROCEDURE optimize_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
48     l_api_name        CONSTANT VARCHAR2(30) := 'concurrent_optimize_ctx';
49     l_api_version     CONSTANT VARCHAR2(30) := 1.0;
50   BEGIN
51     sync_ctx(errbuf, retcode);
52     IF retcode <> retcode_success THEN
53       RETURN;
54     END IF;
55     optimize;
56     retcode := retcode_success;
57   EXCEPTION
58     WHEN OTHERS THEN
59      retcode := retcode_error;
60      errbuf := SUBSTR(sqlerrm,1,200);
61   END;
62 
63   FUNCTION get_request_id(prog_name varchar2)
64   RETURN fnd_concurrent_requests.request_id%TYPE
65   IS
66     result fnd_concurrent_requests.request_id%TYPE;
67   BEGIN
68     SELECT request_id INTO result
69     FROM fnd_concurrent_requests
70     WHERE concurrent_program_id IN (SELECT concurrent_program_id
71                                     FROM fnd_concurrent_programs
72                                     WHERE concurrent_program_name = prog_name)
73     AND phase_code = 'R';
74     RETURN result;
75   EXCEPTION WHEN OTHERS THEN
76     return 0;
77   END;
78 
79   PROCEDURE create_ctx(x_return_status OUT NOCOPY VARCHAR2) IS
80     apps  sys.dba_objects.owner%TYPE;
81     okc   sys.dba_objects.owner%TYPE;
82     cmd   VARCHAR2(4000);
83 
84     anyexp  EXCEPTION;
85 
86     request_id fnd_concurrent_requests.request_id%TYPE;
87   BEGIN
88 
89     x_return_status := 'S';
90 
91     request_id := get_request_id('OKCCHRCRCTX');
92     fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL_CTX text index creation started');
93 
94     BEGIN
95       fnd_file.put_line(fnd_file.log,'Checking OKC_CHR_KEYWORD_PVT package exists or not...');
96       SELECT owner INTO apps
97       FROM sys.dba_objects
98       WHERE object_name = 'OKC_CHR_KEYWORD_PVT'
99       AND object_type = 'PACKAGE';
100       fnd_file.put_line(fnd_file.log,'OKC_CHR_KEYWORD_PVT package exists');
101       fnd_file.put_line(fnd_file.log,'OKC_CHR_KEYWORD_PVT package owner is '||apps);
102 
103       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 1, 13);
104     EXCEPTION WHEN OTHERS THEN
105       fnd_file.put_line(fnd_file.log,'ERROR: OKC_CHR__KEYWORD_PVT package doesn''t exist');
106       RAISE anyexp;
107     END;
108 
109     BEGIN
110       fnd_file.put_line(fnd_file.log,'Checking OKC_K_HEADERS_TL table ...');
111       SELECT owner INTO okc
112       FROM sys.dba_tables
113       WHERE table_name = 'OKC_K_HEADERS_TL';
114       fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL table exists');
115       fnd_file.put_line(fnd_file.log,'OKC_K_HEADERS_TL table owner is '||okc);
116 
117       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 2, 13);
118 
119     EXCEPTION WHEN OTHERS THEN
120       fnd_file.put_line(fnd_file.log,'ERROR: OKC_K_HEADERS_TL table doesn''t exist');
121       RAISE anyexp;
122     END;
123 
124     BEGIN
125       EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||apps||'.OKC_CHR_KEYWORD_PVT TO '||okc;
126       fnd_file.put_line(fnd_file.log,'Granted execute on '||apps||'.OKC_CHR_KEYWORD_PVT to '||okc);
127 
128       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 3, 13);
129 
130     EXCEPTION WHEN OTHERS THEN
131       fnd_file.put_line(fnd_file.log,'ERROR: Couldn''t grant execute on '||apps||'.OKC_CHR_KEYWORD_PVT to '||okc);
132       RAISE anyexp;
133     END;
134 
135 
136     -- Create context index preferences
137     -- Context index searches through columns
138     --    cognomen
139     --    short_description
140     --    description
141     -- Create datastore okc_k_headers_tl_datastore
142 
143     BEGIN
144       BEGIN
145         fnd_file.put_line(fnd_file.log,'Before drop preference');
146         ad_ctx_ddl.drop_preference('okc_k_headers_tl_datastore');
147         fnd_file.put_line(fnd_file.log,'Preference dropped successfully');
148       EXCEPTION
149         WHEN OTHERS THEN
150           NULL;
151       END;
152       fnd_file.put_line(fnd_file.log,'Before create preference');
153       ad_ctx_ddl.create_preference('okc_k_headers_tl_datastore','multi_column_datastore');
154       ad_ctx_ddl.set_attribute('okc_k_headers_tl_datastore','columns',
155                                'cognomen, short_description, description');
156       fnd_file.put_line(fnd_file.log,'Preference created successfully');
157       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 4, 13);
158     END;
159 
160     -- Creating all Lexers and sublexers
161     BEGIN
162       BEGIN
163         fnd_file.put_line(fnd_file.log,'Dropping multi lexer');
164         ad_ctx_ddl.drop_preference('okc_k_headers_tl_lexer');
165         fnd_file.put_line(fnd_file.log,'Multi lexer successfully');
166       EXCEPTION
167         WHEN OTHERS THEN
168           NULL;
169       END;
170       fnd_file.put_line(fnd_file.log,'Creating multi lexer');
171       ad_ctx_ddl.create_preference('okc_k_headers_tl_lexer','multi_lexer');
172       BEGIN
173         fnd_file.put_line(fnd_file.log,'Dropping basic lexer');
174         ad_ctx_ddl.drop_preference('okc_chrtl_blexer');
175         fnd_file.put_line(fnd_file.log,'Basic lexer dropped successfully');
176       EXCEPTION
177         WHEN OTHERS THEN
178           NULL;
179       END;
180       fnd_file.put_line(fnd_file.log,'Creating basic lexer with its attributes');
181       ad_ctx_ddl.create_preference('okc_chrtl_blexer','basic_lexer');
182       ad_ctx_ddl.set_attribute('okc_chrtl_blexer','index_themes','false');
183       ad_ctx_ddl.set_attribute('okc_chrtl_blexer','index_text','true');
184       ad_ctx_ddl.set_attribute('okc_chrtl_blexer','base_letter','true');
185       ad_ctx_ddl.set_attribute('okc_chrtl_blexer','mixed_case','false');
186       fnd_file.put_line(fnd_file.log,'Basic lexer with its attributes created successfully');
187 
188       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 5, 13);
189 
190       BEGIN
191         fnd_file.put_line(fnd_file.log,'Dropping Chinese lexer');
192         ad_ctx_ddl.drop_preference('okc_chrtl_clexer');
193         fnd_file.put_line(fnd_file.log,'Chinese lexer dropped successfully');
194       EXCEPTION
195         WHEN OTHERS THEN
196           NULL;
197       END;
198 
199       DECLARE
200         yes_no VARCHAR2(1);
201       BEGIN
202         BEGIN
203           SELECT 'Y' INTO yes_no
204           FROM ctx_objects
205           WHERE obj_class = 'LEXER'
206           AND obj_name = 'CHINESE_LEXER';
207         EXCEPTION
208           WHEN OTHERS THEN yes_no := 'N';
209         END;
210         fnd_file.put_line(fnd_file.log,'Chinese lexer: yes_no: ' || yes_no);
211         IF yes_no = 'Y' THEN
212           ad_ctx_ddl.create_preference('okc_chrtl_clexer','chinese_lexer');
213         ELSE
214           ad_ctx_ddl.create_preference('okc_chrtl_clexer','chinese_vgram_lexer');
215         END IF;
216         fnd_file.put_line(fnd_file.log,'Chinese lexer created successfully');
217         dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 6, 13);
218 
219       END;
220 
221       BEGIN
222         fnd_file.put_line(fnd_file.log,'Dropping Japanese lexer');
226         WHEN OTHERS THEN
223         ad_ctx_ddl.drop_preference('okc_chrtl_jlexer');
224         fnd_file.put_line(fnd_file.log,'Japanese lexer dropped successfully');
225       EXCEPTION
227           NULL;
228       END;
229 
230       DECLARE
231         yes_no VARCHAR2(1);
232       BEGIN
233         BEGIN
234           SELECT 'Y' INTO yes_no
235           FROM ctx_objects
236           WHERE obj_class = 'LEXER'
237           AND obj_name = 'JAPANESE_LEXER';
238         EXCEPTION
239           WHEN OTHERS THEN
240             yes_no := 'N';
241         END;
242         fnd_file.put_line(fnd_file.log,'Japanese lexer: yes_no: ' || yes_no);
243         IF yes_no = 'Y' THEN
244           ad_ctx_ddl.create_preference('okc_chrtl_jlexer','japanese_lexer');
245         ELSE
246           ad_ctx_ddl.create_preference('okc_chrtl_jlexer','japanese_vgram_lexer');
247         END IF;
248         fnd_file.put_line(fnd_file.log,'Japanese lexer created successfully');
249         dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 7, 13);
250       END;
251 
252       BEGIN
253         fnd_file.put_line(fnd_file.log,'Dropping korean lexer');
254         ad_ctx_ddl.drop_preference('okc_chrtl_klexer');
255         fnd_file.put_line(fnd_file.log,'Korean lexer dropped successfully');
256       EXCEPTION
257         WHEN OTHERS THEN
258           NULL;
259       END;
260 
261       DECLARE
262         yes_no varchar2(1);
263       BEGIN
264         BEGIN
265           SELECT 'Y' INTO yes_no
266           FROM ctx_objects
267           WHERE obj_class = 'LEXER'
268           AND obj_name = 'KOREAN_MORPH_LEXER';
269         EXCEPTION
270           WHEN OTHERS THEN
271             yes_no := 'N';
272         END;
273         fnd_file.put_line(fnd_file.log,'Korean lexer: yes_no: ' || yes_no);
274         IF yes_no = 'Y' THEN
275           ad_ctx_ddl.create_preference('okc_chrtl_klexer', 'korean_morph_lexer');
276           ad_ctx_ddl.set_attribute('okc_chrtl_klexer', 'one_char_word', 'true');
277           ad_ctx_ddl.set_attribute('okc_chrtl_klexer', 'number', 'true');
278         ELSE
279           ad_ctx_ddl.create_preference('okc_chrtl_klexer', 'korean_lexer');
280         END IF;
281 
282         fnd_file.put_line(fnd_file.log,'Korean lexer created successfully');
283         dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 8, 13);
284 
285       END;
286 
287       fnd_file.put_line(fnd_file.log,'Before Add Sub Lexer');
288       ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','default','okc_chrtl_blexer');
289       ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','ja','okc_chrtl_jlexer');
290       ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','ko','okc_chrtl_klexer');
291       ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','zhs','okc_chrtl_clexer');
292       ad_ctx_ddl.add_sub_lexer('okc_k_headers_tl_lexer','zht','okc_chrtl_clexer');
293 
294       fnd_file.put_line(fnd_file.log,'Sub Lexer Added Successfully');
295       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 9, 13);
296 
297     END;
298 
299     --Create wordlist okc_k_headers_tl_wordlist
300     BEGIN
301       BEGIN
302          fnd_file.put_line(fnd_file.log,'Dropping WordList');
303          ad_ctx_ddl.drop_preference('okc_k_headers_tl_wordlist');
304          fnd_file.put_line(fnd_file.log,'WordList dropped successfully');
305       EXCEPTION
306         WHEN OTHERS THEN
307           NULL;
308       END;
309       fnd_file.put_line(fnd_file.log,'Creating WordList...');
310       ad_ctx_ddl.create_preference('okc_k_headers_tl_wordlist','basic_wordlist');
311       ad_ctx_ddl.set_attribute('okc_k_headers_tl_wordlist','stemmer','auto');
312       ad_ctx_ddl.set_attribute('okc_k_headers_tl_wordlist','fuzzy_match','auto');
313       fnd_file.put_line(fnd_file.log,'WordList created succesfully');
314     END;
315 
316     dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 10, 13);
317 
318     -- Drop context index okc_k_headers_tl_ctx (if exists)
319     BEGIN
320       fnd_file.put_line(fnd_file.log,'Dropping INDEX okc_k_headers_tl_ctx...');
321       EXECUTE IMMEDIATE 'DROP INDEX ' || okc ||'.okc_k_headers_tl_ctx FORCE';
322       fnd_file.put_line(fnd_file.log,'INDEX okc_k_headers_tl_ctx succesfully dropped');
323     EXCEPTION
324       WHEN OTHERS THEN
325         NULL;
326     END;
327 
328     dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 11, 13);
329 
330     -- Create context index okc_k_headers_tl_ctx
331     DECLARE
335       fnd_file.put_line(fnd_file.log,'Index Creation Logic Begins');
332       l_max_memory    NUMBER;
333       l_cpu_count     NUMBER;
334     BEGIN
339       FROM ctx_parameters
336 
337     BEGIN
338       SELECT par_value INTO l_max_memory
340       WHERE par_name = 'MAX_INDEX_MEMORY';
341     EXCEPTION WHEN OTHERS THEN
342       NULL;
343     END;
344 
345       fnd_file.put_line(fnd_file.log,'Max Memory: '||l_max_memory);
346 
347     BEGIN
348       SELECT value INTO l_cpu_count
349       FROM v$parameter
350       WHERE name = 'cpu_count';
351     EXCEPTION WHEN OTHERS THEN
352       NULL;
353     END;
354 
355       fnd_file.put_line(fnd_file.log,'CPU Count: '||l_cpu_count);
356       fnd_file.put_line(fnd_file.log,'Before Index Creation');
357 
358       dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 12, 13);
359 
360 -- Changes for Bug# 6921424
361 -- l_max_memory and l_cpu_count should be an integer in order to
362 -- pass them as parameters to the create index statement
363       l_max_memory := round(l_max_memory/2);
364       l_cpu_count  := round(l_cpu_count/2);
365 -- Changes For Bug# 6921424 Ends
366 
367       EXECUTE IMMEDIATE
368       'CREATE INDEX ' || okc || '.okc_k_headers_tl_ctx ON ' || okc || '.okc_k_headers_tl(short_description)
369        INDEXTYPE IS CTXSYS.CONTEXT
370        PARAMETERS (''
371        DATASTORE  ' || apps || '.okc_k_headers_tl_datastore
372        FILTER		ctxsys.null_filter
373        LEXER ' || apps || '.okc_k_headers_tl_lexer language column source_lang
374        SECTION GROUP ctxsys.null_section_group
375        MEMORY ' || l_max_memory || -- For Bug# 6921424
376        ' STOPLIST ctxsys.default_stoplist
377        WORDLIST	' || apps || '.okc_k_headers_tl_wordlist'') PARALLEL ' || l_cpu_count; -- For Bug# 6921424
378 
379        fnd_file.put_line(fnd_file.log,'Index OKC_K_HEADERS_TL_CTX created successfully');
380        dbms_application_info.set_session_longops( rindex, slno, null, request_id, request_id, 13, 13);
381 
382     EXCEPTION
383       WHEN ANYEXP THEN
384 	   x_return_status := 'E';
385       WHEN OTHERS THEN
386         BEGIN
387           EXECUTE IMMEDIATE 'DROP INDEX ' || okc || '.okc_k_headers_tl_ctx FORCE';
388         EXCEPTION
389           WHEN OTHERS THEN
390             NULL;
391         END;
392     END;
393   END create_ctx;
394 
395   PROCEDURE create_ctx(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) AS
396     l_api_name        CONSTANT VARCHAR2(30) := 'concurrent_create_ctx';
397     l_api_version     CONSTANT VARCHAR2(30) := 1.0;
398     request_id        fnd_concurrent_requests.request_id%type;
399     l_return_status   VARCHAR2(3) := 'S';
400     stop_proc         EXCEPTION;
401   BEGIN
402     rindex := dbms_application_info.set_session_longops_nohint;
403     request_id := get_request_id('OKCCHRCRCTX');
404     dbms_application_info.set_session_longops
405          (rindex, slno, 'Create Contract Header Text Index', request_id, request_id, 0, 13,
406          'OKCCHRCRCTX concurrent program', 'steps');
407 
408     create_ctx(x_return_status => l_return_status);
409     IF l_return_status <> 'S'
410     THEN
411       RAISE stop_proc;
412     END IF;
413 
414     errbuf := 'OKC_K_HEADERS_TL_CTX text index has been created successfully';
415     retcode := retcode_success;
416   EXCEPTION
417     WHEN stop_proc THEN
418       retcode := retcode_error;
419       errbuf := 'ERROR: Couldn''t create OKC_K_HEADERS_TL_CTX text index';
420     WHEN OTHERS THEN
421       retcode := retcode_error;
422       errbuf := 'ERROR: Couldn''t create OKC_K_HEADERS_TL_CTX text index';
423   END create_ctx;
424 
425 END;