[Home] [Help]
PACKAGE BODY: APPS.DOM_DOC_TEXT_UTIL
Source
1 PACKAGE BODY DOM_DOC_TEXT_UTIL AS
2 /* $Header: DOMUIMTB.pls 120.3 2006/11/01 07:20:27 rkhasa noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DOM_DOC_TEXT_UTIL';
5
6 -- -----------------------------------------------------------------------------
7 -- Private Globals
8 -- -----------------------------------------------------------------------------
9
10 g_Prod_Short_Name CONSTANT VARCHAR2(30) := 'DOM';
11 g_Prod_Schema VARCHAR2(30);
12 g_Index_Owner VARCHAR2(30);
13 g_Index_Name VARCHAR2(30) := 'DOM_IMTEXT_TL_CTX1';
14 g_Indexing_Context VARCHAR2(30) := 'SYNC_INDEX';
15
16 g_installed BOOLEAN;
17 g_inst_status VARCHAR2(1);
18 g_industry VARCHAR2(1);
19
20 g_DB_Version_Num NUMBER := NULL;
21 g_DB_Version_Str VARCHAR2(30) := NULL;
22 g_compatibility VARCHAR2(30) := NULL;
23
24 g_MSTK_Flex_Delimiter VARCHAR2(1) := NULL;
25
26 --c_Dom_Appl_Id CONSTANT NUMBER := 703;
27 --c_Dom_DFF_Name CONSTANT VARCHAR2(30) := 'DOM_DOC_GROUP';
28
29 -- Global debug flag
30 g_Debug BOOLEAN := TRUE;
31
32 t_temp NUMBER := 0;
33
34 --Bug 4045988
35 l_DB_Version_Str VARCHAR2(30) := NULL;
36 l_DB_Numeric_Character VARCHAR2(30) := NULL;
37 --Bug 4045988
38
39
40 -- Variable used to buffer text strings before writing into LOB.
41 --
42 g_Buffer VARCHAR2(32767);
43 g_Buffer_Length INTEGER;
44
45 /*
46 -- -----------------------------------------------------------------------------
47 -- Debug
48 -- -----------------------------------------------------------------------------
49
50 PROCEDURE Debug
51 (
52 p_change_id IN NUMBER
53 , p_org_id IN NUMBER
54 , p_msg_name IN VARCHAR2
55 , p_error_text IN VARCHAR2
56 );
57 */
58 -- -----------------------------------------------------------------------------
59 -- Set_Context
60 -- -----------------------------------------------------------------------------
61
62 PROCEDURE Set_Context ( p_context IN VARCHAR2 )
63 IS
64 BEGIN
65 g_Indexing_Context := p_context;
66 END Set_Context;
67
68 -- -----------------------------------------------------------------------------
69 -- Append_VARCHAR_to_LOB
70 -- -----------------------------------------------------------------------------
71
72 PROCEDURE Append_VARCHAR_to_LOB
73 (
74 x_tlob IN OUT NOCOPY CLOB
75 , p_string IN VARCHAR2
76 , p_action IN VARCHAR2 DEFAULT 'APPEND'
77 )
78 IS
79 start_writing BOOLEAN := TRUE;
80 l_offset INTEGER := 1;
81 l_Max_Length INTEGER := 32767;
82 l_String_Length INTEGER;
83 BEGIN
84
85 IF ( p_action = 'BEGIN' ) THEN
86
87 -- Empty the LOB, if this is the first chunk of text to append
88 DBMS_LOB.Trim ( lob_loc => x_tlob, newlen => 0 );
89
90 g_Buffer := p_string;
91 g_Buffer_Length := -1;
92
93 ELSIF ( p_action IN ('APPEND', 'END') ) THEN
94
95 start_writing := ( g_Buffer_Length = -1 );
96 IF ( start_writing ) THEN
97 g_Buffer_Length := Length (g_Buffer);
98 END IF;
99
100 l_String_Length := Length (p_string);
101
102 -- Write buffer to LOB if required
103
104 IF ( g_Buffer_Length + l_String_Length >= l_Max_Length ) THEN
105 IF ( start_writing ) THEN
106 DBMS_LOB.Write ( lob_loc => x_tlob
107 , amount => Length (g_Buffer)
108 , offset => l_offset
109 , buffer => g_Buffer
110 );
111 ELSE
112 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
113 , amount => Length (g_Buffer)
114 , buffer => g_Buffer
115 );
116 END IF;
117
118 -- Reset buffer
119 g_Buffer := p_string;
120 g_Buffer_Length := Length (g_Buffer);
121 ELSE
122 g_Buffer := g_Buffer || p_string;
123 g_Buffer_Length := g_Buffer_Length + l_String_Length;
124 END IF; -- Max_Length reached
125
126 IF ( p_action = 'END' ) THEN
127 start_writing := ( g_Buffer_Length = -1 );
128 IF ( start_writing ) THEN
129 DBMS_LOB.Write ( lob_loc => x_tlob
130 , amount => Length (g_Buffer)
131 , offset => l_offset
132 , buffer => g_Buffer
133 );
134 ELSE
135 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
136 , amount => Length (g_Buffer)
137 , buffer => g_Buffer
138 );
139 END IF;
140 -- Reset buffer
141 g_Buffer := '';
142 g_Buffer_Length := -1;
143 END IF;
144
145 END IF; -- p_action
146
147 END Append_VARCHAR_to_LOB;
148
149 -- -----------------------------------------------------------------------------
150 -- Get_doc_Text
151 -- -----------------------------------------------------------------------------
152
153 PROCEDURE Get_doc_Text
154 (
155 p_rowid IN ROWID
156 , p_output_type IN VARCHAR2
157 , x_tlob IN OUT NOCOPY CLOB
158 , x_tchar IN OUT NOCOPY VARCHAR2
159 )
160 IS
161 l_api_name CONSTANT VARCHAR2(30) := 'Get_doc_Text';
162 l_return_status VARCHAR2(1);
163
164 l_doc_id NUMBER;
165 l_doc_notice VARCHAR2(2000);
166 l_doc_name VARCHAR2(50);
167 l_org_id NUMBER;
168 l_language VARCHAR2(4);
169 l_source_lang VARCHAR2(4);
170 doc_mgmt_type_code NUMBER;
171 l_doc_number VARCHAR2 (50);
172 l_description VARCHAR2(4000);
173
174
175 l_text VARCHAR2(200);
176 l_amount BINARY_INTEGER;
177 --l_buffer VARCHAR2(32767) := NULL;
178 --pos1 INTEGER;
179 --pos2 INTEGER;
180
181
182 -- Gopi select correct data.
183 /*
184 cursor l_line_text(L_CHG_ID NUMBER,L_LANG VARCHAR2) is
185 SELECT NAME , NUMBER, DESCRIPTION
186 FROM DOM_DOCUMENTS
187 WHERE
188 DOCUMENT_NUMBER= l_doc_number;
189
190
191 cursor l_actions_text(L_CHG_ID NUMBER,L_LANG VARCHAR2) is
192 SELECT DESCRIPTION FROM DOM_DOCUMENT_ACTIONS_TL TL ,DOM_DOCUMENT_ACTIONS B
193 WHERE
194 TL.ACTION_ID = B.ACTION_ID
195 AND B.OBJECT_ID1 = L_CHG_ID
196 AND B.OBJECT_NAME = 'DOM_DOCUMENT'
197 AND TL.LANGUAGE = L_LANG
198 AND DESCRIPTION IS NOT NULL;
199 */
200
201 BEGIN
202
203
204 BEGIN
205
206 SELECT ddt.NAME , dd.DOC_NUMBER, ddt.DESCRIPTION
207 , (dd.DOC_NUMBER||' '||ddt.NAME||' '||ddt.DESCRIPTION)
208 , ddt.language
209 INTO
210 l_doc_name
211 , l_doc_number
212 , l_description
213 , l_text
214 , l_language
215 FROM
216 DOM_DOCUMENTS dd,
217 DOM_DOCUMENTS_TL ddt,
218 DOM_DOCUMENTS_IMTEXT_TL ddit
219 WHERE
220 ddit.document_id = ddt.document_id
221 AND ddt.document_id = dd.document_id
222 AND ddt.language = ddit.language
223 AND ddit.rowid = p_rowid;
224
225 /*
226 DOM_DOCUMENTS dd,
227 DOM_DOCUMENTS_TL ddt
228 WHERE
229 ddt.rowid = p_rowid;
230 */
231
232
233 -- use fnd_languages
234
235
236 EXCEPTION
237 WHEN no_data_found THEN
238 -- IF (g_Debug) THEN Debug(l_doc_id, l_org_id, l_doc_notice, '** 1: ' || SQLERRM); END IF;
239 DOM_DOC_TEXT_PVT.Log_Line (l_api_name || ': CTX End_Log');
240
241 WHEN others THEN
242 DOM_DOC_TEXT_PVT.Log_Line (l_api_name || SQLERRM);
243
244 END;
245 -- debug ( ' DX document_id : ' || l_doc_id );
246
247 -- Append_VARCHAR_to_LOB (x_tlob, ' ', 'BEGIN');
248
249
250 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
251 l_text := TRANSLATE(l_text, '_*~^.$#@:|&', '----+++++++');
252 END IF;
253 -- Append_VARCHAR_to_LOB (x_tchar, l_text);
254
255 /*
256 FOR LINE_REC IN L_LINE_TEXT(L_CHANGE_ID , L_LANGUAGE)
257 LOOP
258 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
259 l_text := TRANSLATE(LINE_REC.NAME, '_*~^.$#@:|&', '----+++++++');
260 ELSE
261 l_text := LINE_REC.NAME;
262 END IF;
263 Append_VARCHAR_to_LOB (x_tlob, ' ');
264 Append_VARCHAR_to_LOB (x_tlob, l_text);
265 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
266 l_text := TRANSLATE(LINE_REC.DESCRIPTION, '_*~^.$#@:|&', '----+++++++');
267 ELSE
268 l_text := LINE_REC.DESCRIPTION;
269 END IF;
270 Append_VARCHAR_to_LOB (x_tlob, ' ');
271 Append_VARCHAR_to_LOB (x_tlob, l_text);
272
273 END LOOP;
274
275 FOR ACTION_REC IN L_ACTIONS_TEXT(L_CHANGE_ID , L_LANGUAGE)
276 LOOP
277
278 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
279 l_text := TRANSLATE(ACTION_REC.DESCRIPTION, '_*~^.$#@:|&', '----+++++++');
280 ELSE
281 l_text := ACTION_REC.DESCRIPTION;
282 END IF;
283
284 Append_VARCHAR_to_LOB (x_tlob, ' ');
285 Append_VARCHAR_to_LOB (x_tlob, l_text);
286
287 END LOOP;
288
289 */
290 x_tchar := l_text;
291
292 -- Append_VARCHAR_to_LOB (x_tchar, ' ','END');
293
294 EXCEPTION
295
296 WHEN others THEN
297 NULL;
298
299 END Get_doc_Text;
300
301 /*
302 -- -----------------------------------------------------------------------------
303 -- Debug
304 -- -----------------------------------------------------------------------------
305
306 PROCEDURE Debug
307 (
308 p_change_id IN NUMBER
309 , p_org_id IN NUMBER
310 , p_msg_name IN VARCHAR2
311 , p_error_text IN VARCHAR2
312 )
313 IS
314 l_sysdate DATE := SYSDATE;
315 BEGIN
316
317 INSERT INTO mtl_interface_errors
318 (
319 transaction_id
320 , unique_id
321 , organization_id
322 , table_name
323 , message_name
324 , error_message
325 , creation_date
326 , created_by
327 , last_update_date
328 , last_updated_by
329 )
330 VALUES
331 (
332 mtl_system_items_interface_s.NEXTVAL
333 , p_change_id
334 , p_org_id
335 , 'DOM_DOCUMENT_IMTEXT_TL'
336 , p_msg_name
337 , SUBSTRB(p_error_text, 1,240)
338 , l_sysdate
339 , 1
340 , l_sysdate
341 , 1
342 );
343
344 END Debug;
345 */
346 -- -----------------------------------------------------------------------------
347 -- Print_Lob
348 -- -----------------------------------------------------------------------------
349
350 PROCEDURE Print_Lob ( p_tlob_loc IN CLOB )
351 IS
352 l_amount BINARY_INTEGER := 255;
353 l_offset INTEGER := 1;
354 l_offset_max INTEGER := 32767;
355 l_buffer VARCHAR2(32767);
356 BEGIN
357
358 --DBMS_OUTPUT.put_line('LOB contents:');
359
360 -- Read portions of LOB
361 LOOP
362 DBMS_LOB.Read ( lob_loc => p_tlob_loc
363 , amount => l_amount
364 , offset => l_offset
365 , buffer => l_buffer
366 );
367
368 --DBMS_OUTPUT.put_line(l_buffer);
369
370 l_offset := l_offset + l_amount;
371 EXIT WHEN l_offset > l_offset_max;
372 END LOOP;
373
374 EXCEPTION
375 WHEN no_data_found THEN
376 NULL;
377
378 END Print_Lob;
379
380
381
382 -- -----------------------------------------------------------------------------
383 -- Sync_Index
384 -- -----------------------------------------------------------------------------
385
386 PROCEDURE Sync_Index ( p_idx_name IN VARCHAR2 DEFAULT NULL )
387 IS
388 BEGIN
389
390 AD_CTX_DDL.Sync_Index ( idx_name => NVL(g_Index_Owner || '.' || p_idx_name, g_Index_Owner ||'.'|| g_Index_Name) );
391
392 EXCEPTION
393 WHEN others THEN
394 DOM_DOC_TEXT_PVT.Log_Line ('DOM_DOC_TEXT_UTIL : Error in Sync Index');
395
396 END Sync_Index;
397
398 -- -----------------------------------------------------------------------------
399 -- Sync_Index_For_Forms
400 -- -----------------------------------------------------------------------------
401
402 PROCEDURE Sync_Index_For_Forms ( p_idx_name IN VARCHAR2 DEFAULT NULL )
403 IS
404 BEGIN
405
406 EXECUTE IMMEDIATE 'ALTER INDEX ' || g_Index_Owner ||'.'|| g_Index_Name || ' REBUILD ONLINE PARAMETERS (''SYNC'')';
407
408 EXCEPTION
409 WHEN others THEN
410 NULL;
411
412 DOM_DOC_TEXT_PVT.Log_Line ('DOM_DOC_TEXT_UTIL : Error in Sync_Index_For_Forms');
413
414 END Sync_Index_For_Forms;
415 -- -----------------------------------------------------------------------------
416 -- Optimize_Index
417 -- -----------------------------------------------------------------------------
418
419 -- Start : Concurrent Program for Optimize iM index
420 PROCEDURE Optimize_Index
421 (
422 ERRBUF OUT NOCOPY VARCHAR2
423 , RETCODE OUT NOCOPY NUMBER
424 , p_optlevel IN VARCHAR2 DEFAULT AD_CTX_DDL.Optlevel_Full
425 , p_dummy IN VARCHAR2 DEFAULT NULL
426 , p_maxtime IN NUMBER DEFAULT AD_CTX_DDL.Maxtime_Unlimited
427 )
428 IS
429
430 Mctx INV_ITEM_MSG.Msg_Ctx_type;
431 l_api_name CONSTANT VARCHAR2(30) := 'Optimize_Index';
432 l_success CONSTANT NUMBER := 0;
433 l_error CONSTANT NUMBER := 2;
434 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
435 l_maxtime NUMBER := NVL(p_maxtime,AD_CTX_DDL.Maxtime_Unlimited);
436
437 BEGIN
438
439 IF p_optlevel ='FAST' THEN
440 l_maxtime := NULL;
441 END IF;
442
443 AD_CTX_DDL.Optimize_Index ( idx_name => g_Index_Owner ||'.'|| g_Index_Name
444 , optlevel => NVL(p_optlevel,AD_CTX_DDL.Optlevel_Full)
445 , maxtime => l_maxtime);
446 --gyedla Create Message
447 RETCODE := l_success;
448 ERRBUF := FND_MESSAGE.Get_String('DOM', 'DOM_OPTIMINDEX_SUCCESS');
449
450 --gyedla Create Message
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 RETCODE := l_error;
455 ERRBUF := FND_MESSAGE.Get_String('DOM', 'DOM_OPTIMINDEX_FAILURE');
456 DOM_DOC_TEXT_PVT.Log_Line ('DOM_DOC_TEXT_UTIL : Error in Optimize Index for CM Text Index');
457
458 END Optimize_Index;
459 -- End : Concurrent Program for Optimize iM index
460
461
462 -- -----------------------------------------------------------------------------
463 -- get_Prod_Schema
464 -- -----------------------------------------------------------------------------
465
466 FUNCTION get_Prod_Schema
467 RETURN VARCHAR2
468 IS
469 BEGIN
470 RETURN (g_Prod_Schema);
471 END get_Prod_Schema;
472
473 -- -----------------------------------------------------------------------------
474 -- get_DB_Version_Num
475 -- -----------------------------------------------------------------------------
476
477 FUNCTION get_DB_Version_Num
478 RETURN NUMBER
479 IS
480 BEGIN
481 RETURN (g_DB_Version_Num);
482 END get_DB_Version_Num;
483
484 FUNCTION get_DB_Version_Str
485 RETURN VARCHAR2
486 IS
487 BEGIN
488 RETURN (g_DB_Version_Str);
489 END get_DB_Version_Str;
490
491 -- -----------------------------------------------------------------------------
492 -- insert_document
493 -- -----------------------------------------------------------------------------
494
495 PROCEDURE Insert_Update_Doc
496 (
497 p_doc_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
498 )
499 IS
500 l_doc_id NUMBER;
501 l_language VARCHAR2(4);
502 temp VARCHAR2(500);
503 l_text VARCHAR2(1);
504 cursor EEC_REC is
505 SELECT document_id, text
506 FROM DOM_DOCUMENTS_IMTEXT_TL
507 WHERE
508 document_id = p_doc_id;
509 BEGIN
510 OPEN EEC_REC;
511 LOOP
512 FETCH EEC_REC INTO l_doc_id,l_text;
513 EXIT WHEN EEC_REC%NOTFOUND;
514 UPDATE DOM_DOCUMENTS_IMTEXT_TL SET TEXT = DECODE(l_text,'1','0','1') WHERE document_id = l_doc_id;
515 -- debug ( p_doc_id , 0471 , 'update','updated ' || l_text );
516 END LOOP;
517
518 IF ( EEC_REC%ROWCOUNT = 0 ) THEN
519 -- debug ( p_doc_id , 0471 , 'create','created ' || l_text );
520 INSERT INTO DOM_DOCUMENTS_IMTEXT_TL
521 (
522 DOCUMENT_ID ,
523 LANGUAGE ,
524 TEXT ,
525 CREATED_BY ,
526 CREATION_DATE ,
527 LAST_UPDATED_BY ,
528 LAST_UPDATE_DATE ,
529 LAST_UPDATE_LOGIN )
530 SELECT DOC.DOCUMENT_ID,
531 FLA.LANGUAGE_CODE,
532 TEXT,
533 DOC.CREATED_BY ,
534 DOC.CREATION_DATE ,
535 DOC.LAST_UPDATED_BY ,
536 DOC.LAST_UPDATE_DATE ,
537 DOC.LAST_UPDATE_LOGIN
538 FROM
539 (SELECT DOM.DOCUMENT_ID, 1 TEXT,
540 DOM.CREATED_BY ,
541 DOM.CREATION_DATE ,
542 DOM.LAST_UPDATED_BY ,
543 DOM.LAST_UPDATE_DATE ,
544 DOM.LAST_UPDATE_LOGIN FROM DOM_DOCUMENTS DOM
545 WHERE
546 DOCUMENT_ID = p_doc_id ) DOC, FND_LANGUAGES FLA
547 WHERE FLA.INSTALLED_FLAG IN ('I','B');
548 END IF;
549
550 CLOSE EEC_REC;
551
552 EXCEPTION
553 WHEN others THEN
554 BEGIN
555 NULL;
556 DOM_DOC_TEXT_PVT.Log_Line ('DOM_DOC_TEXT_UTIL : Error in Insert_Update_Document');
557 END;
558
559 END Insert_Update_Doc;
560
561 -- *****************************************************************************
562 -- ** Package initialization block **
563 -- *****************************************************************************
564
565 BEGIN
566
567 ------------------------------------------------------------------
568 -- Determine index schema and store in a private global variable
569 ------------------------------------------------------------------
570
571 g_installed := FND_INSTALLATION.Get_App_Info ('DOM', g_inst_status, g_industry, g_Prod_Schema);
572
573 g_Index_Owner := g_Prod_Schema;
574
575 -------------------------
576 -- Determine DB version
577 -------------------------
578
579 --Bug 5514824/4045988: We need to convert the db version string to be compativle with the
580 --numeric characters of that language. Eg. '9.2' need to be changed to '9F2'
581 -- in French before we can use it in TO_NUMBER
582
583 DBMS_UTILITY.db_Version (g_DB_Version_Str, g_compatibility);
584 l_DB_Version_Str := SUBSTR(g_DB_Version_Str, 1, INSTR(g_DB_Version_Str, '.', 1, 2) - 1);
585
586 SELECT SUBSTR(VALUE,0,1) into l_DB_Numeric_Character
587 FROM V$NLS_PARAMETERS
588 Where PARAMETER = 'NLS_NUMERIC_CHARACTERS';
589 g_DB_Version_Num := TO_NUMBER( REPLACE(l_DB_Version_Str, '.', l_DB_Numeric_Character) );
590
591
592 END DOM_DOC_TEXT_UTIL;