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