[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_TEXT_UTIL
Source
1 PACKAGE BODY EGO_ITEM_TEXT_UTIL AS
2 /* $Header: EGOUIMTB.pls 120.8 2006/05/15 16:37:51 eletuchy noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_TEXT_UTIL';
5
6 -- -----------------------------------------------------------------------------
7 -- Private Globals
8 -- -----------------------------------------------------------------------------
9
10 g_Prod_Short_Name CONSTANT VARCHAR2(30) := 'EGO';
11 g_Prod_Schema VARCHAR2(30);
12 g_Index_Owner VARCHAR2(30);
13 g_Index_Name VARCHAR2(30) := 'EGO_ITEM_TEXT_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_Ego_Appl_Id CONSTANT NUMBER := 431;
27 c_Ego_DFF_Name CONSTANT VARCHAR2(30) := 'EGO_ITEMMGMT_GROUP';
28
29 --Bug 4045988
30 l_DB_Version_Str VARCHAR2(30) := NULL;
31 l_DB_Numeric_Character VARCHAR2(30) := NULL;
32 --Bug 4045988
33
34 -- Global debug flag
35 g_Debug BOOLEAN := FALSE;
36
37 -- Document section tags
38
39 Tag_itemcode CONSTANT VARCHAR2(30) := 'itemcode';
40 Tag_begin_itemcode CONSTANT VARCHAR2(30) := '<' || Tag_itemcode || '>';
41 Tag_end_itemcode CONSTANT VARCHAR2(30) := '</' || Tag_itemcode || '>';
42
43 Tag_description CONSTANT VARCHAR2(30) := 'description';
44 Tag_begin_description CONSTANT VARCHAR2(30) := '<' || Tag_description || '>';
45 Tag_end_description CONSTANT VARCHAR2(30) := '</' || Tag_description || '>';
46
47 Tag_shortdescr CONSTANT VARCHAR2(30) := 'shortdescr';
48 Tag_begin_shortdescr CONSTANT VARCHAR2(30) := '<' || Tag_shortdescr || '>';
49 Tag_end_shortdescr CONSTANT VARCHAR2(30) := '</' || Tag_shortdescr || '>';
50
51 Tag_longdescr CONSTANT VARCHAR2(30) := 'longdescr';
52 Tag_begin_longdescr CONSTANT VARCHAR2(30) := '<' || Tag_longdescr || '>';
53 Tag_end_longdescr CONSTANT VARCHAR2(30) := '</' || Tag_longdescr || '>';
54
55 Tag_internal CONSTANT VARCHAR2(30) := 'internalitem';
56 Tag_begin_internal CONSTANT VARCHAR2(30) := '<' || Tag_internal || '>';
57 Tag_end_internal CONSTANT VARCHAR2(30) := '</' || Tag_internal || '>';
58
59 Tag_cataloggroupid_prefix CONSTANT VARCHAR2(30) := 'cataloggroup';
60 l_Tag_catalog_group VARCHAR2(30);
61 l_Tag_begin_catalog_group VARCHAR2(30);
62 l_Tag_end_catalog_group VARCHAR2(30);
63
64 Tag_customer CONSTANT VARCHAR2(30) := 'customeritem';
65 Tag_begin_customer CONSTANT VARCHAR2(30) := '<' || Tag_customer || '>';
66 Tag_end_customer CONSTANT VARCHAR2(30) := '</' || Tag_customer || '>';
67
68 Tag_reference CONSTANT VARCHAR2(30) := 'referenceitem';
69 Tag_begin_reference CONSTANT VARCHAR2(30) := '<' || Tag_reference || '>';
70 Tag_end_reference CONSTANT VARCHAR2(30) := '</' || Tag_reference || '>';
71
72 Tag_userattribute CONSTANT VARCHAR2(30) := 'ua';
73 Tag_begin_userattribute CONSTANT VARCHAR2(30) := '<' || Tag_userattribute || '>';
74 Tag_end_userattribute CONSTANT VARCHAR2(30) := '</' || Tag_userattribute || '>';
75 /*
76 Tag_CategoryAssign CONSTANT VARCHAR2(30) := 'catassign';
77 Tag_CategorySet CONSTANT VARCHAR2(30) := 'categoryset';
78 Tag_Category CONSTANT VARCHAR2(30) := 'category';
79 */
80
81 -- Variable used to buffer text strings before writing into LOB.
82 --
83 g_Buffer VARCHAR2(32767);
84 g_Buffer_Length INTEGER;
85
86 /*
87 TYPE Char_Tbl_Type IS TABLE OF VARCHAR2(32767)
88 INDEX BY BINARY_INTEGER;
89
90 g_Item_Ext_Text_Tbl Char_Tbl_Type;
91 */
92 -- -----------------------------------------------------------------------------
93 -- Debug
94 -- -----------------------------------------------------------------------------
95 PROCEDURE Debug
96 (
97 p_item_id IN NUMBER
98 , p_org_id IN NUMBER
99 , p_msg_name IN VARCHAR2
100 , p_error_text IN VARCHAR2
101 );
102
103 -- -----------------------------------------------------------------------------
104 -- Set_Context
105 -- -----------------------------------------------------------------------------
106
107 PROCEDURE Set_Context ( p_context IN VARCHAR2 )
108 IS
109 BEGIN
110 g_Indexing_Context := p_context;
111 END Set_Context;
112
113 -- -----------------------------------------------------------------------------
114 -- Append_VARCHAR_to_LOB
115 -- -----------------------------------------------------------------------------
116
117 PROCEDURE Append_VARCHAR_to_LOB
118 (
119 x_tlob IN OUT NOCOPY CLOB
120 , p_string IN VARCHAR2
121 , p_action IN VARCHAR2 DEFAULT 'APPEND'
122 )
123 IS
124 start_writing BOOLEAN := TRUE;
125 l_offset INTEGER := 1;
126 l_Max_Length INTEGER := 32767;
127 l_String_Length INTEGER;
128 BEGIN
129
130 IF ( p_action = 'BEGIN' ) THEN
131
132 -- Empty the LOB, if this is the first chunk of text to append
133 DBMS_LOB.Trim ( lob_loc => x_tlob, newlen => 0 );
134
135 g_Buffer := p_string;
136 g_Buffer_Length := -1;
137
138 ELSIF ( p_action IN ('APPEND', 'END') ) THEN
139
140 start_writing := ( g_Buffer_Length = -1 );
141 IF ( start_writing ) THEN
142 g_Buffer_Length := Length (g_Buffer);
143 END IF;
144
145 l_String_Length := Length (p_string);
146
147 -- Write buffer to LOB if required
148
149 IF ( g_Buffer_Length + l_String_Length >= l_Max_Length ) THEN
150 IF ( start_writing ) THEN
151 DBMS_LOB.Write ( lob_loc => x_tlob
152 , amount => Length (g_Buffer)
153 , offset => l_offset
154 , buffer => g_Buffer
155 );
156 ELSE
157 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
158 , amount => Length (g_Buffer)
159 , buffer => g_Buffer
160 );
161 END IF;
162
163 -- Reset buffer
164 g_Buffer := p_string;
165 g_Buffer_Length := Length (g_Buffer);
166 ELSE
167 g_Buffer := g_Buffer || p_string;
168 g_Buffer_Length := g_Buffer_Length + l_String_Length;
169 END IF; -- Max_Length reached
170
171 IF ( p_action = 'END' ) THEN
172 start_writing := ( g_Buffer_Length = -1 );
173 IF ( start_writing ) THEN
174 DBMS_LOB.Write ( lob_loc => x_tlob
175 , amount => Length (g_Buffer)
176 , offset => l_offset
177 , buffer => g_Buffer
178 );
179 ELSE
180 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
181 , amount => Length (g_Buffer)
182 , buffer => g_Buffer
183 );
184 END IF;
185 -- Reset buffer
186 g_Buffer := '';
187 g_Buffer_Length := -1;
188 END IF;
189
190 END IF; -- p_action
191
192 END Append_VARCHAR_to_LOB;
193
194 -- -----------------------------------------------------------------------------
195 -- Get_Item_Text
196 -- -----------------------------------------------------------------------------
197
198 PROCEDURE Get_Item_Text
199 (
200 p_rowid IN ROWID
201 , p_output_type IN VARCHAR2
202 , x_tlob IN OUT NOCOPY CLOB
203 , x_tchar IN OUT NOCOPY VARCHAR2
204 )
205 IS
206 TYPE t_varchar_table IS TABLE OF VARCHAR2(150)
207 INDEX BY BINARY_INTEGER;
208 l_mfg_table t_varchar_table;
209 l_mpn_table t_varchar_table;
210
211 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Text';
212 l_return_status VARCHAR2(1);
213
214 l_id_type VARCHAR2(30);
215 l_item_id NUMBER;
216 l_item_code VARCHAR2(2000);
217 l_item_segments VARCHAR2(2000);
218 l_org_id NUMBER;
219 l_language VARCHAR2(4);
220 l_source_lang VARCHAR2(4);
221 l_item_catalog_group_id NUMBER;
222
223 --v_item_code VARCHAR2(2000) := NULL;
224 --v_description VARCHAR2(4000) := NULL;
225 --v_long_description VARCHAR2(4000) := NULL;
226
227 l_description VARCHAR2(240) := NULL;
228 l_long_description VARCHAR2(4000) := NULL;
229 l_item_catalog_group VARCHAR2(40) := NULL;
230
231 l_text VARCHAR2(32767);
232 l_amount BINARY_INTEGER;
233 --l_buffer VARCHAR2(32767) := NULL;
234 --pos1 INTEGER;
235 --pos2 INTEGER;
236
237 l_Tag_Id_type VARCHAR2(30);
238 --l_Tag_begin_Id_type VARCHAR2(30);
239 --l_Tag_end_Id_type VARCHAR2(30);
240
241 BEGIN
242
243 ------------------------------------------------------------------------
244 -- Get item identifier record for a subsequent retrieval of inventory,
245 -- customer, or cross_reference item (depending on the identifier type).
246 ------------------------------------------------------------------------
247
248 IF (p_output_type = 'VARCHAR2') THEN
249
250 BEGIN
251
252 SELECT
253 eitl.id_type
254 , eitl.item_id
255 --, eitl.item_code
256 --, eitl.org_id
257 , eitl.language
258 --, eitl.source_lang
259 --, NVL(eitl.item_catalog_group_id, 0)
260 --, eitl.item_code ||' '|| TRANSLATE(eitl.item_code, g_MSTK_Flex_Delimiter, ' ')
261 , eitl.item_code ||' '|| msitl.description ||' '|| msitl.long_description
262 INTO
263 l_id_type
264 , l_item_id
265 --, l_item_code
266 --, l_org_id
267 , l_language
268 --, l_source_lang
269 --, l_item_catalog_group_id
270 --, l_item_segments
271 , l_text
272 FROM
273 ego_item_text_tl eitl
274 , mtl_system_items_tl msitl
275 WHERE
276 eitl.rowid = p_rowid
277 AND msitl.inventory_item_id = eitl.item_id
278 AND msitl.organization_id = eitl.org_id
279 AND msitl.language = eitl.language;
280
281 EXCEPTION
282 WHEN no_data_found THEN
283 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 0: ' || SQLERRM); END IF;
284 END;
285
286 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
287 l_text := TRANSLATE(l_text, '_*~^.$#@:|&', '----+++++++');
288 END IF;
289
290 x_tchar := l_text;
291
292 ELSE
293
294 -- This will be used to generate section tags;
295 -- we return the text as a CLOB in case it gets large.
296 -- (eg. an item with many associated MPN's)
297 BEGIN
298
299 -- Here we collect item_code, description, and long description
300 -- for the row being processed; we also find its PK's and other
301 -- information we will use to query other tables.
302 SELECT
303 eitl.item_id
304 , eitl.org_id
305 , eitl.item_code
306 , msitl.description
307 , msitl.long_description
308 , eitl.item_catalog_group_id
309 , eitl.language
310 INTO
311 l_item_id
312 , l_org_id
313 , l_item_code
314 , l_description
315 , l_long_description
316 , l_item_catalog_group_id
317 , l_language
318 FROM
319 ego_item_text_tl eitl
320 , mtl_system_items_tl msitl
321 WHERE
322 eitl.rowid = p_rowid
323 AND msitl.inventory_item_id = eitl.item_id
324 AND msitl.organization_id = eitl.org_id
325 AND msitl.language = eitl.language;
326
327 -- Here we obtain the catalog category name.
328 BEGIN
329 SELECT micg.concatenated_segments item_catalog_group
330 INTO l_item_catalog_group
331 FROM mtl_item_catalog_groups_kfv micg
332 WHERE micg.item_catalog_group_id = l_item_catalog_group_id;
333 EXCEPTION
334 WHEN no_data_found THEN
335 l_item_catalog_group := NULL;
336 END;
337
338 -- Here we obtain a collection of manufacturer names
339 -- and their corresponding part numbers, all of which are
340 -- associated with the currently selected item
341 SELECT mmpn.mfg_part_num
342 , mm.manufacturer_name
343 BULK COLLECT INTO
344 l_mpn_table
345 , l_mfg_table
346 FROM MTL_MANUFACTURERS mm
347 , MTL_MFG_PART_NUMBERS mmpn
348 WHERE mm.manufacturer_id = mmpn.manufacturer_id
349 AND mmpn.inventory_item_id = l_item_id
350 AND mmpn.organization_id = l_org_id;
351
352 -- Finally, we generate our indexed text, which consists
353 -- of basic XML-style tags to enclose the different information
354 l_text := '<item>' || l_item_code || '</item>' ||
355 '<desc><shortdesc>' || l_description || '</shortdesc>' ||
356 '<longdesc>' || l_long_description || '</longdesc></desc>' ||
357 '<cat>' || l_item_catalog_group || '</cat>';
358
359 IF (l_mfg_table.count = 0) THEN
360 l_text := l_text || '<aml><mfg></mfg><mpn></mpn></aml>';
361 ELSE
362 FOR i IN 1..l_mfg_table.count
363 LOOP
364 l_text := l_text || '<aml><mfg>' || l_mfg_table(i) || '</mfg><mpn>' ||
365 l_mpn_table(i) || '</mpn></aml>';
366 END LOOP;
367 END IF;
368
369 --Bug 5094325 begin
370 --Now adding section data for lang and org sections
371 l_text := l_text || '<lang>:' || l_language || '</lang>';
372 l_text := l_text || '<org>:' || l_org_id || '</org>';
373 --Bug 5094325 end
374
375 IF ( l_language IN ('JA', 'KO', 'ZHS', 'ZHT') ) THEN
376 l_text := TRANSLATE(l_text, '_*~^.$#@:|&', '----+++++++');
377 END IF;
378
379 EXCEPTION
380 WHEN no_data_found THEN
381 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 0: ' || SQLERRM); END IF;
382 END;
383
384 EGO_ITEM_TEXT_PVT.Log_Line('Get_Item_Text: returning text->'||l_text);
385 Append_VARCHAR_to_LOB (x_tlob, '', 'BEGIN');
386 Append_VARCHAR_to_LOB (x_tlob, l_text);
387 Append_VARCHAR_to_LOB (x_tlob, ' ', 'END');
388
389 END IF;
390
391 /*
392 -----------------------------------------------------------
393 -- Concatenate section data and write into LOB
394 -----------------------------------------------------------
395
396 IF ( l_id_type = g_Internal_Type ) THEN
397 l_Tag_Id_type := Tag_internal;
398 ELSIF ( l_id_type = g_Customer_Type ) THEN
399 l_Tag_Id_type := Tag_customer;
400 ELSE
401 l_Tag_Id_type := Tag_reference;
402 END IF;
403
404 l_Tag_catalog_group := Tag_cataloggroupid_prefix || TO_CHAR(l_item_catalog_group_id);
405 l_Tag_begin_catalog_group := '<' || l_Tag_catalog_group || '>';
406 l_Tag_end_catalog_group := '</' || l_Tag_catalog_group || '>';
407
408 --v_item_code := l_item_code || ' ' || l_item_segments;
409
410 x_tchar := '<' || l_Tag_Id_type || '>' ||
411 l_Tag_begin_catalog_group ||
412 Tag_begin_itemcode || v_item_code || Tag_end_itemcode ||
413 Tag_begin_description ||
414 Tag_begin_shortdescr || v_description || Tag_end_shortdescr ||
415 Tag_begin_longdescr || v_long_description || Tag_end_longdescr ||
416 Tag_end_description ||
417 l_Tag_end_catalog_group ||
418 '</' || l_Tag_Id_type || '>';
419 */
420
421 /*
422 ------------------------------------------------------------------------
423 -- Get item text data for inventory, customer, or cross_reference item.
424 ------------------------------------------------------------------------
425
426 IF ( l_id_type = g_Internal_Type ) THEN
427
428 l_Tag_Id_type := Tag_internal;
429
430 l_Tag_catalog_group := Tag_cataloggroupid_prefix || TO_CHAR(l_item_catalog_group_id);
431 l_Tag_begin_catalog_group := '<' || l_Tag_catalog_group || '>';
432 l_Tag_end_catalog_group := '</' || l_Tag_catalog_group || '>';
433
434 ---------------------------------------------------------------
435 -- (1) Get text for inventory item row
436 ---------------------------------------------------------------
437
438 BEGIN
439
440 SELECT
441 SEGMENT1 ||' '|| SEGMENT2 ||' '|| SEGMENT3 ||' '|| SEGMENT4 ||' '|| SEGMENT5 ||' '||
442 SEGMENT6 ||' '|| SEGMENT7 ||' '|| SEGMENT8 ||' '|| SEGMENT9 ||' '|| SEGMENT10 ||' '||
443 SEGMENT11 ||' '|| SEGMENT12 ||' '|| SEGMENT13 ||' '|| SEGMENT14 ||' '|| SEGMENT15 ||' '||
444 SEGMENT16 ||' '|| SEGMENT17 ||' '|| SEGMENT18 ||' '|| SEGMENT19 ||' '|| SEGMENT20
445 INTO
446 l_item_segments
447 FROM
448 mtl_system_items_b msib
449 WHERE
450 msib.inventory_item_id = l_item_id
451 AND msib.organization_id = l_org_id;
452
453 SELECT
454 msitl.description, msitl.long_description
455 INTO
456 v_description, v_long_description
457 FROM
458 mtl_system_items_tl msitl
459 WHERE
460 msitl.inventory_item_id = l_item_id
461 AND msitl.organization_id = l_org_id
462 AND msitl.language = l_language;
463
464 -- Only include item code if found in the referenced table
465 v_item_code := l_item_code || ' ' || l_item_segments;
466
467 EXCEPTION
468 WHEN no_data_found THEN
469 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 1: ' || SQLERRM); END IF;
470 END;
471
472 ELSIF ( l_id_type = g_Customer_Type ) THEN
473
474 l_Tag_Id_type := Tag_customer;
475
476 ---------------------------------------------------------------
477 -- (2) Get text data for customer item;
478 -- customer_item_id is the Unique Key column;
479 -- customer item is org-independent (eitl.organization_id = 0).
480 ---------------------------------------------------------------
481
482 BEGIN
483
484 SELECT
485 customer_item_desc, NULL
486 INTO
487 v_description, v_long_description
488 FROM
489 mtl_customer_items
490 WHERE
491 customer_item_id = l_item_id;
492
493 -- ego_item_text_tl would not contain inactive customer items, so this is commented out:
494 -- AND inactive_flag = 'N';
495
496 -- Only include item code if found in the referenced table
497 v_item_code := l_item_code;
498
499 EXCEPTION
500 WHEN no_data_found THEN
501 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 2: ' || SQLERRM); END IF;
502 END;
503
504 ELSE
505 -- All reference types
506
507 l_Tag_Id_type := Tag_reference;
508
509 ---------------------------------------------------------------
510 -- (3) Get text data for cross_reference item;
511 -- the Unique Key columns are:
512 -- cross_reference_type
513 -- cross_reference
514 -- organization_id
515 -- inventory_item_id;
516 -- cross_reference item can be either org-dependent
517 -- or org-independent (org_id = 0).
518 ---------------------------------------------------------------
519
520 BEGIN
521
522 SELECT
523 description, NULL
524 INTO
525 v_description, v_long_description
526 FROM
527 mtl_cross_references
528 WHERE
529 cross_reference_type = l_id_type
530 AND cross_reference = l_item_code
531 AND inventory_item_id = l_item_id
532 AND org_independent_flag = DECODE(l_org_id, 0, 'Y', 'N')
533 AND ( organization_id = l_org_id
534 OR ( organization_id IS NULL AND l_org_id = 0 )
535 )
536 ;
537
538 -- Only include item code if found in the referenced table
539 v_item_code := l_item_code;
540
541 EXCEPTION
542 WHEN no_data_found THEN
543 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 3: ' || SQLERRM); END IF;
544 END;
545
546 END IF; -- identifier type
547
548 l_Tag_begin_Id_type := '<' || l_Tag_Id_type || '>';
549 l_Tag_end_Id_type := '</' || l_Tag_Id_type || '>';
550
551 -----------------------------------------------------------
552 -- Concatenate section data and write into LOB
553 -----------------------------------------------------------
554
555 IF ( p_output_type = 'VARCHAR2' ) THEN
556 x_tchar := '';
557 ELSE
558 Append_VARCHAR_to_LOB (x_tlob, '', 'BEGIN');
559 END IF;
560
561 IF ( ( Length(NVL(v_item_code,0)) + Length(NVL(v_description,0)) + Length(NVL(v_long_description,0)) ) > 0 ) THEN
562
563 IF ( l_id_type = g_Internal_Type ) THEN
564
565 ----------------------------------------------------
566 -- For id type INTERNAL, add catalog group section
567 ----------------------------------------------------
568
569 l_buffer := l_Tag_begin_Id_type || l_Tag_begin_catalog_group ||
570 Tag_begin_itemcode || v_item_code || Tag_end_itemcode ||
571 Tag_begin_description ||
572 Tag_begin_shortdescr || v_description || Tag_end_shortdescr ||
573 Tag_begin_longdescr || v_long_description || Tag_end_longdescr ||
574 Tag_end_description;
575
576 IF ( p_output_type = 'VARCHAR2' ) THEN x_tchar := x_tchar || l_buffer;
577 ELSE Append_VARCHAR_to_LOB (x_tlob, l_buffer); END IF;
578 */
579
580 /*
581 ------------------------------------------------------------------------------------
582 -- Get user-defined attribute display names and values (for id type INTERNAL only)
583 ------------------------------------------------------------------------------------
584
585 BEGIN
586
587 -- If this is index creation, use different approach to retrieve
588 -- user-defined attribute data.
589
590 IF ( g_Indexing_Context = 'CREATE_INDEX' ) THEN
591
592 FOR ext_id_rec IN Ext_Attr_Extension_Ids_cur ( p_inventory_item_id => l_item_id
593 , p_organization_id => l_org_id )
594 LOOP
595 l_buffer := g_Item_Ext_Text_Tbl (ext_id_rec.EXTENSION_ID);
596
597 IF ( p_output_type = 'VARCHAR2' ) THEN x_tchar := x_tchar || l_buffer;
598 ELSE Append_VARCHAR_to_LOB (x_tlob, l_buffer); END IF;
599
600 END LOOP; -- Ext_Attr_Extension_Ids_cur
601
602 ELSE
603
604 FOR attr_rec IN Ext_Attr_Internal_Values_cur (l_item_id, l_org_id, l_language) LOOP
605
606 IF ( attr_rec.FLEX_VALUE_SET_ID IS NULL ) THEN
607
608 l_buffer := Tag_begin_userattribute ||
609 attr_rec.ATTR_DISPLAY_NAME || ' ' || attr_rec.ATTR_INTERNAL_VALUE ||
610 Tag_end_userattribute;
611
612 ELSE
613
614 FOR lookup_rec IN Ext_Attr_Lookup_Values_cur ( attr_rec.FLEX_VALUE_SET_ID
615 , attr_rec.ATTR_INTERNAL_VALUE
616 , l_language)
617 LOOP
618
619 l_buffer := Tag_begin_userattribute ||
620 attr_rec.ATTR_DISPLAY_NAME || ' ' || lookup_rec.ATTR_LOOKUP_VALUE ||
621 Tag_end_userattribute;
622
623 END LOOP; -- Ext_Attr_Lookup_Values_cur
624
625 END IF; -- FLEX_VALUE_SET_ID IS NULL
626
627 IF ( p_output_type = 'VARCHAR2' ) THEN x_tchar := x_tchar || l_buffer;
628 ELSE Append_VARCHAR_to_LOB (x_tlob, l_buffer); END IF;
629
630 END LOOP; -- Ext_Attr_Internal_Values_cur
631
632 END IF; -- g_Indexing_Context
633
634 EXCEPTION
635 WHEN no_data_found THEN
636 IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 1: ' || SQLERRM); END IF;
637
638 END; -- user-defined attributes
639 */
640
641 /*
642 l_buffer := l_Tag_end_catalog_group || l_Tag_end_Id_type;
643
644 IF ( p_output_type = 'VARCHAR2' ) THEN x_tchar := x_tchar || l_buffer;
645 ELSE Append_VARCHAR_to_LOB (x_tlob, l_buffer); END IF;
646
647 ELSE
648
649 l_buffer := l_Tag_begin_Id_type ||
650 Tag_begin_itemcode || v_item_code || Tag_end_itemcode ||
651 Tag_begin_description ||
652 Tag_begin_shortdescr || v_description || Tag_end_shortdescr ||
653 Tag_begin_longdescr || v_long_description || Tag_end_longdescr ||
654 Tag_end_description ||
655 l_Tag_end_Id_type;
656
657 IF ( p_output_type = 'VARCHAR2' ) THEN x_tchar := x_tchar || l_buffer;
658 ELSE Append_VARCHAR_to_LOB (x_tlob, l_buffer); END IF;
659
660 END IF; -- Id type is Internal
661
662 -- Complete writing item text data into LOB.
663 --
664 IF ( p_output_type = 'VARCHAR2' ) THEN
665 x_tchar := x_tchar || ' ';
666 ELSE
667 Append_VARCHAR_to_LOB (x_tlob, ' ', 'END');
668 END IF;
669
670 ELSE
671 -- If v_item_code, v_description, v_long_description is null/empty,
672 -- just write a space.
673
674 -- Complete writing item text data into LOB.
675 --
676 IF ( p_output_type = 'VARCHAR2' ) THEN
677 x_tchar := x_tchar || ' ';
678 ELSE
679 Append_VARCHAR_to_LOB (x_tlob, ' ', 'END');
680 END IF;
681
682 END IF; -- item text data is not null
683 */
684
685 EXCEPTION
686
687 WHEN others THEN
688 --EGO_ITEM_TEXT_UTIL.Log_Error ('SQL_ERROR', SQLERRM);
689 --IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 9: ' || SQLERRM); END IF;
690 RAISE;
691
692 END Get_Item_Text;
693
694
695 -- -----------------------------------------------------------------------------
696 -- Debug
697 -- -----------------------------------------------------------------------------
698
699 PROCEDURE Debug
700 (
701 p_item_id IN NUMBER
702 , p_org_id IN NUMBER
703 , p_msg_name IN VARCHAR2
704 , p_error_text IN VARCHAR2
705 )
706 IS
707 l_sysdate DATE := SYSDATE;
708 BEGIN
709
710 INSERT INTO mtl_interface_errors
711 (
712 transaction_id
713 , unique_id
714 , organization_id
715 , table_name
716 , message_name
717 , error_message
718 , creation_date
719 , created_by
720 , last_update_date
721 , last_updated_by
722 )
723 VALUES
724 (
725 mtl_system_items_interface_s.NEXTVAL
726 , p_item_id
727 , p_org_id
728 , 'EGO_ITEM_TEXT_TL'
729 , p_msg_name
730 , SUBSTRB(p_error_text, 1,240)
731 , l_sysdate
732 , 1
733 , l_sysdate
734 , 1
735 );
736
737 END Debug;
738
739 -- -----------------------------------------------------------------------------
740 -- Print_Lob
741 -- -----------------------------------------------------------------------------
742
743 PROCEDURE Print_Lob ( p_tlob_loc IN CLOB )
744 IS
745 l_amount BINARY_INTEGER := 255;
746 l_offset INTEGER := 1;
747 l_offset_max INTEGER := 32767;
748 l_buffer VARCHAR2(32767);
749 BEGIN
750
751 --DBMS_OUTPUT.put_line('LOB contents:');
752
753 -- Read portions of LOB
754 LOOP
755 DBMS_LOB.Read ( lob_loc => p_tlob_loc
756 , amount => l_amount
757 , offset => l_offset
758 , buffer => l_buffer
759 );
760
761 --DBMS_OUTPUT.put_line(l_buffer);
762
763 l_offset := l_offset + l_amount;
764 EXIT WHEN l_offset > l_offset_max;
765 END LOOP;
766
767 EXCEPTION
768 WHEN no_data_found THEN
769 NULL;
770
771 END Print_Lob;
772
773 -- -----------------------------------------------------------------------------
774 -- Sync_Index
775 -- -----------------------------------------------------------------------------
776
777 PROCEDURE Sync_Index ( p_idx_name IN VARCHAR2 DEFAULT NULL )
778 IS
779 BEGIN
780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
781 fnd_log.string(log_level => FND_LOG.LEVEL_PROCEDURE
782 ,module => 'fnd.plsql.EGO_ITEM_TEXT_UTIL.SYNC_INDEX'
783 ,message => 'Before Calling the AD_CTX_DDL.Sync_Index'
784 );
785 END IF;
786
787 -------------------------------------------------------------------------------
788 -- Use CTX API instead of alter index to resolve a problem of separate
789 -- sync and optimize jobs causing conflict because two alter index operations
790 -- cannot cannot be run at the same time for a single index.
791 -------------------------------------------------------------------------------
792 --EXECUTE IMMEDIATE 'ALTER INDEX ' || g_Index_Owner ||'.'|| g_Index_Name || ' REBUILD ONLINE PARAMETERS (''SYNC'')';
793
794 AD_CTX_DDL.Sync_Index ( idx_name => NVL(p_idx_name, g_Index_Owner ||'.'|| g_Index_Name) );
795
796 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
797 fnd_log.string(log_level => FND_LOG.LEVEL_PROCEDURE
798 ,module => 'fnd.plsql.EGO_ITEM_TEXT_UTIL.SYNC_INDEX'
799 ,message => 'After Calling the AD_CTX_DDL.Sync_Index'
800 );
801 END IF;
802 /*
803 EXCEPTION
804 WHEN others THEN
805 NULL;
806 DBMS_OUTPUT.put_line('==> Sync_Index(p_idx_name): EXCEPTION: ' || SQLERRM);
807 */
808 END Sync_Index;
809
810 -- -----------------------------------------------------------------------------
811 -- Optimize_Index
812 -- -----------------------------------------------------------------------------
813
814 -- Start : Concurrent Program for Optimize iM index
815 PROCEDURE Optimize_Index
816 (
817 ERRBUF OUT NOCOPY VARCHAR2
818 , RETCODE OUT NOCOPY NUMBER
819 , p_optlevel IN VARCHAR2 DEFAULT AD_CTX_DDL.Optlevel_Full
820 , p_dummy IN VARCHAR2 DEFAULT NULL
821 , p_maxtime IN NUMBER DEFAULT AD_CTX_DDL.Maxtime_Unlimited
822 )
823 IS
824
825 Mctx INV_ITEM_MSG.Msg_Ctx_type;
826 l_api_name CONSTANT VARCHAR2(30) := 'Optimize_Index';
827 l_success CONSTANT NUMBER := 0;
828 l_error CONSTANT NUMBER := 2;
829 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
830 l_maxtime NUMBER := NVL(p_maxtime,AD_CTX_DDL.Maxtime_Unlimited);
831
832 BEGIN
833
834 INV_ITEM_MSG.Initialize;
835 INV_ITEM_MSG.set_Message_Mode ('CP_LOG');
836
837 -- Set message level
838 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
839
840 -- Define message context
841 Mctx.Package_Name := G_PKG_NAME;
842 Mctx.Procedure_Name := l_api_name;
843
844 IF (l_debug = 1) THEN
845 INV_ITEM_MSG.Debug(Mctx, 'Started AD_CTX_DDL.Optimize_Index..');
846 INV_ITEM_MSG.Debug(Mctx, 'Optimization Level :'||p_optlevel);
847 INV_ITEM_MSG.Debug(Mctx, 'Maximum Optimization Time :'||p_maxtime);
848 END IF;
849
850 --3067433: Maxtime should be null for FAST Optimize mode
851 IF p_optlevel ='FAST' THEN
852 l_maxtime := NULL;
853 END IF;
854
855 AD_CTX_DDL.Optimize_Index ( idx_name => g_Index_Owner ||'.'|| g_Index_Name
856 , optlevel => NVL(p_optlevel,AD_CTX_DDL.Optlevel_Full)
857 , maxtime => l_maxtime);
858
859 IF (l_debug = 1) THEN
860 INV_ITEM_MSG.Debug(Mctx, 'Completed AD_CTX_DDL.Optimize_Index..');
861 END IF;
862
863 RETCODE := l_success;
864 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_OPTIMINDEX_SUCCESS');
865
866 -- Write all accumulated messages
867 INV_ITEM_MSG.Write_List (p_delete => TRUE);
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 RETCODE := l_error;
872 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_OPTIMINDEX_FAILURE');
873
874 INV_ITEM_MSG.Add_Message
875 ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
876 , p_token1 => 'PACKAGE_NAME'
877 , p_value1 => G_PKG_NAME
878 , p_token2 => 'PROCEDURE_NAME'
879 , p_value2 => l_api_name
880 , p_token3 => 'ERROR_TEXT'
881 , p_value3 => SUBSTRB(SQLERRM, 1,240));
882
883 -- Write all accumulated messages
884 INV_ITEM_MSG.Write_List (p_delete => TRUE);
885
886 END Optimize_Index;
887 -- End : Concurrent Program for Optimize iM index
888
889 -- -----------------------------------------------------------------------------
890 -- Process_Source_Table_Event (Wrapper)
891 -- -----------------------------------------------------------------------------
892
893 PROCEDURE Process_Source_Table_Event
894 (
895 p_table_name IN VARCHAR2
896 , p_event IN VARCHAR2
897 , p_scope IN VARCHAR2
898 , p_manufacturer_id IN NUMBER
899 , p_old_item_id IN NUMBER
900 , p_item_id IN NUMBER
901 , p_org_id IN NUMBER
902 , p_language IN VARCHAR2
903 , p_source_lang IN VARCHAR2
904 , p_last_update_date IN VARCHAR2
905 , p_last_updated_by IN VARCHAR2
906 , p_last_update_login IN VARCHAR2
907 , p_id_type IN VARCHAR2
908 , p_item_code IN VARCHAR2
909 , p_item_catalog_group_id IN VARCHAR2
910 )
911 IS
912 l_id_type VARCHAR2(30);
913 l_text_ins VARCHAR2(1) := '1';
914 l_text_upd VARCHAR2(1) := '2';
915 BEGIN
916 --DBMS_OUTPUT.put_line('==> p_table_name = '|| p_table_name || ' p_event = ' || p_event || ' p_scope = ' || p_scope);
917
918 IF ( p_scope = 'ROW' ) THEN
919
920 IF ( p_table_name IN ('MTL_SYSTEM_ITEMS_B', 'MTL_SYSTEM_ITEMS_TL',
921 'EGO_MTL_SY_ITEMS_EXT_B', 'EGO_MTL_SY_ITEMS_EXT_TL',
922 'MTL_ITEM_CATALOG_GROUPS_B') ) THEN
923 l_id_type := g_Internal_Type;
924 ELSIF ( p_table_name = 'MTL_CUSTOMER_ITEMS' ) THEN
925 l_id_type := g_Customer_Type;
926 ELSIF ( p_table_name = 'MTL_CROSS_REFERENCES' ) THEN
927 l_id_type := p_id_type;
928 ELSIF( p_table_name IN ('MTL_MANUFACTURERS', 'MTL_MFG_PART_NUMBERS') ) THEN
929 l_id_type := g_Internal_Type;
930 END IF;--( p_scope = 'ROW' )
931
932 ------------------------------------
933 -- Table MTL_SYSTEM_ITEMS_B events
934 ------------------------------------
935
936 IF ( p_table_name = 'MTL_SYSTEM_ITEMS_B' ) THEN
937
938 IF ( p_event = 'UPDATE' ) THEN
939
940 -- Item Code is passed in through a parameter.
941 -- Update rows for all languages.
942
943 UPDATE ego_item_text_tl
944 SET
945 item_code = DECODE(p_item_code, FND_API.G_MISS_CHAR, item_code, p_item_code)
946 , item_catalog_group_id = DECODE(p_item_catalog_group_id, FND_API.G_MISS_NUM, item_catalog_group_id, p_item_catalog_group_id)
947 , text = l_text_upd
948 , last_update_date = SYSDATE
949 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
950 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
951 WHERE
952 id_type = l_id_type
953 AND item_id = p_item_id
954 AND org_id = p_org_id;
955
956 END IF; -- p_event
957
958 -------------------------------------
959 -- Table MTL_SYSTEM_ITEMS_TL events
960 -------------------------------------
961
962 ELSIF ( p_table_name = 'MTL_SYSTEM_ITEMS_TL' ) THEN
963
964 --------------------------------------------------------------------
965 -- When invoked from the trigger, "mutating table" is not an issue
966 -- here because there is no select from mtl_system_items_tl.
967 --------------------------------------------------------------------
968
969 IF ( p_event = 'INSERT' ) THEN
970
971 INSERT INTO ego_item_text_tl
972 (
973 id_type
974 , item_id
975 , item_code
976 , org_id
977 , language
978 , source_lang
979 , item_catalog_group_id
980 , inventory_item_id
981 , text
982 , creation_date
983 , created_by
984 , last_update_date
985 , last_updated_by
986 , last_update_login
987 )
988 SELECT
989 l_id_type
990 , msik.inventory_item_id
991 , msik.concatenated_segments
992 , msik.organization_id
993 , p_language
994 , DECODE(p_source_lang, FND_API.G_MISS_CHAR, p_language, p_source_lang)
995 , msik.item_catalog_group_id
996 , msik.inventory_item_id
997 , l_text_ins
998 , SYSDATE
999 , msik.created_by
1000 , SYSDATE
1001 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, msik.last_updated_by, p_last_updated_by)
1002 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, msik.last_update_login, p_last_update_login)
1003 FROM
1004 mtl_system_items_b_kfv msik
1005 WHERE
1006 msik.inventory_item_id = p_item_id
1007 AND msik.organization_id = p_org_id;
1008 /* Bug: 4667452 Commenting out following conditions
1009 AND msik.concatenated_segments IS NOT NULL
1010 AND NOT EXISTS
1011 ( SELECT 1 FROM ego_item_text_tl eitl1
1012 WHERE
1013 eitl1.id_type = l_id_type
1014 AND eitl1.item_id = msik.inventory_item_id
1015 AND eitl1.item_code = msik.concatenated_segments
1016 AND eitl1.org_id = msik.organization_id
1017 AND eitl1.language = p_language
1018 );
1019 End Bug: 4667452 */
1020
1021 ELSIF ( p_event = 'UPDATE' ) THEN
1022
1023 UPDATE ego_item_text_tl
1024 SET
1025 source_lang = DECODE(p_source_lang, FND_API.G_MISS_CHAR, source_lang, p_source_lang)
1026 , text = l_text_upd
1027 , last_update_date = SYSDATE
1028 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
1029 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
1030 WHERE
1031 id_type = l_id_type
1032 AND item_id = p_item_id
1033 AND org_id = p_org_id
1034 AND language = p_language;
1035
1036 ELSIF ( p_event = 'DELETE' ) THEN
1037
1038 DELETE FROM ego_item_text_tl
1039 WHERE
1040 id_type = l_id_type
1041 AND item_id = p_item_id
1042 AND org_id = p_org_id
1043 AND language = p_language;
1044
1045 END IF; -- p_event
1046
1047
1048 -------------------------------------
1049 -- Table MTL_MANUFACTURERS events
1050 -------------------------------------
1051
1052 ELSIF ( p_table_name = 'MTL_MANUFACTURERS' ) THEN
1053 IF (p_manufacturer_id IS NOT NULL) THEN
1054
1055 UPDATE EGO_ITEM_TEXT_TL
1056 SET text = l_text_upd
1057 , last_update_date = SYSDATE
1058 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
1059 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
1060 WHERE
1061 id_type = l_id_type
1062 AND (item_id,org_id) IN ( SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
1063 FROM MTL_MFG_PART_NUMBERS
1064 WHERE MANUFACTURER_ID = p_manufacturer_id );
1065 END IF;
1066
1067
1068
1069 -------------------------------------
1070 -- Table MTL_MFG_PART_NUMBERS events
1071 -------------------------------------
1072
1073 ELSIF ( p_table_name = 'MTL_MFG_PART_NUMBERS' ) THEN
1074
1075 IF ( p_event = 'UPDATE' OR p_event= 'INSERT' ) THEN
1076
1077
1078 UPDATE EGO_ITEM_TEXT_TL
1079 SET text = l_text_upd
1080 , last_update_date = SYSDATE
1081 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
1082 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
1083 WHERE
1084 id_type = l_id_type
1085 AND org_id = p_org_id
1086 AND item_id IN (NVL(p_old_item_id,p_item_id),p_item_id);
1087
1088 ELSE
1089 UPDATE EGO_ITEM_TEXT_TL
1090 SET text = l_text_upd
1091 , last_update_date = SYSDATE
1092 WHERE
1093 id_type = l_id_type
1094 AND org_id = p_org_id
1095 AND item_id IN (NVL(p_old_item_id,p_item_id),p_item_id);
1096 END IF; -- p_event
1097
1098
1099
1100
1101 /*
1102 ----------------------------------------
1103 -- Table EGO_MTL_SY_ITEMS_EXT_% events
1104 ----------------------------------------
1105
1106 ELSIF ( p_table_name = 'EGO_MTL_SY_ITEMS_EXT_B' ) THEN
1107 IF ( p_event IN ('INSERT', 'UPDATE', 'DELETE') ) THEN
1108 --
1109 -- Update rows for all languages
1110 --
1111 UPDATE ego_item_text_tl
1112 SET
1113 text = l_text_upd
1114 , last_update_date = SYSDATE
1115 WHERE
1116 id_type = l_id_type
1117 AND item_id = p_item_id
1118 AND org_id = p_org_id;
1119
1120 END IF;
1121
1122 ELSIF ( p_table_name = 'EGO_MTL_SY_ITEMS_EXT_TL' ) THEN
1123 IF ( p_event IN ('INSERT', 'UPDATE', 'DELETE') ) THEN
1124 --
1125 -- Update rows for a single language
1126 --
1127 UPDATE ego_item_text_tl
1128 SET
1129 text = l_text_upd
1130 , last_update_date = SYSDATE
1131 WHERE
1132 id_type = l_id_type
1133 AND item_id = p_item_id
1134 AND org_id = p_org_id
1135 AND language = p_language;
1136
1137 END IF;
1138 */
1139 ELSIF ( p_table_name = 'MTL_ITEM_CATALOG_GROUPS_B' ) THEN
1140
1141 IF ( p_event = 'UPDATE' ) THEN
1142
1143 -- updated item catalog group ID is passed in through a parameter;
1144 -- update rows for all languages.
1145
1146 UPDATE ego_item_text_tl
1147 SET
1148 text = l_text_upd
1149 , last_update_date = SYSDATE
1150 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
1151 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
1152 WHERE
1153 id_type = l_id_type
1154 AND item_catalog_group_id = NVL(p_item_catalog_group_id, -1);
1155
1156 END IF; -- p_event
1157
1158 END IF; -- p_table_name
1159
1160 -------------------------------------------------------------------------
1161 -- Sync the index after the statement level event on a source table.
1162 -- This should no longer be used.
1163 -------------------------------------------------------------------------
1164 --ELSIF ( p_scope IN ('STATEMENT', 'STMT') ) THEN
1165 -- AD_CTX_DDL.Sync_Index ( idx_name => g_Index_Owner ||'.'|| g_Index_Name );
1166
1167 ELSE
1168 Raise_Application_Error (-20001, 'Process_Source_Table_Event: Invalid parameter value: p_scope = ' || p_scope);
1169
1170 END IF; -- p_scope
1171
1172 EXCEPTION
1173
1174 WHEN others THEN
1175 --Raise_Application_Error (-20001, 'Process_Source_Table_Event: ' || SQLERRM);
1176 RAISE;
1177
1178 END Process_Source_Table_Event;
1179
1180 -- -----------------------------------------------------------------------------
1181 -- get_Prod_Schema
1182 -- -----------------------------------------------------------------------------
1183
1184 FUNCTION get_Prod_Schema
1185 RETURN VARCHAR2
1186 IS
1187 BEGIN
1188 RETURN (g_Prod_Schema);
1189 END get_Prod_Schema;
1190
1191 -- -----------------------------------------------------------------------------
1192 -- Process_Source_Table_Event
1193 -- -----------------------------------------------------------------------------
1194
1195 PROCEDURE Process_Source_Table_Event
1196 (
1197 p_table_name IN VARCHAR2
1198 , p_event IN VARCHAR2
1199 , p_scope IN VARCHAR2
1200 , p_item_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1201 , p_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1202 , p_language IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1203 , p_source_lang IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1204 , p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
1205 , p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
1206 , p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
1207 , p_id_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1208 , p_item_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1209 , p_item_catalog_group_id IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
1210 )
1211 IS
1212
1213 l_manufacturer_id NUMBER :=NULL;
1214 l_old_item_id NUMBER :=NULL;
1215 BEGIN
1216 EGO_ITEM_TEXT_UTIL.Process_Source_Table_Event
1217 (
1218 p_table_name => p_table_name
1219 , p_event =>p_event
1220 , p_scope => p_scope
1221 , p_manufacturer_id => l_manufacturer_id
1222 , p_old_item_id => l_old_item_id
1223 , p_item_id => p_item_id
1224 , p_org_id => p_org_id
1225 , p_language => p_language
1226 , p_source_lang => p_source_lang
1227 , p_last_update_date => p_last_update_date
1228 , p_last_updated_by => p_last_updated_by
1229 , p_last_update_login => p_last_update_login
1230 , p_id_type => p_id_type
1231 , p_item_code => p_item_code
1232 , p_item_catalog_group_id => p_item_catalog_group_id
1233 );
1234 END Process_Source_Table_Event;
1235
1236
1237 -- -----------------------------------------------------------------------------
1238 -- get_DB_Version_Num
1239 -- -----------------------------------------------------------------------------
1240
1241 FUNCTION get_DB_Version_Num
1242 RETURN NUMBER
1243 IS
1244 BEGIN
1245 RETURN (g_DB_Version_Num);
1246 END get_DB_Version_Num;
1247
1248 FUNCTION get_DB_Version_Str
1249 RETURN VARCHAR2
1250 IS
1251 BEGIN
1252 RETURN (g_DB_Version_Str);
1253 END get_DB_Version_Str;
1254
1255 -- *****************************************************************************
1256 -- ** Package initialization block **
1257 -- *****************************************************************************
1258
1259 BEGIN
1260
1261 ------------------------------------------------------------------
1262 -- Determine index schema and store in a private global variable
1263 ------------------------------------------------------------------
1264
1265 g_installed := FND_INSTALLATION.Get_App_Info ('EGO', g_inst_status, g_industry, g_Prod_Schema);
1266
1267 g_Index_Owner := g_Prod_Schema;
1268
1269 -------------------------
1270 -- Determine DB version
1271 -------------------------
1272 --Bug 4045988: We need to convert the db version string to be compativle with the
1273 --numeric characters of that language. Eg. '9.2' need to be changed to '9F2'
1274 -- in French before we can use it in TO_NUMBER
1275 DBMS_UTILITY.db_Version (g_DB_Version_Str, g_compatibility);
1276 l_DB_Version_Str := SUBSTR(g_DB_Version_Str, 1, INSTR(g_DB_Version_Str, '.', 1, 2) - 1);
1277 SELECT SUBSTR(VALUE,0,1) into l_DB_Numeric_Character
1278 FROM V$NLS_PARAMETERS
1279 Where PARAMETER = 'NLS_NUMERIC_CHARACTERS';
1280 g_DB_Version_Num := TO_NUMBER( REPLACE(l_DB_Version_Str, '.', l_DB_Numeric_Character) );
1281
1282 /*
1283 BEGIN
1284 SELECT concatenated_segment_delimiter
1285 INTO g_MSTK_Flex_Delimiter
1286 FROM fnd_id_flex_structures
1287 WHERE
1288 application_id = 401
1289 AND id_flex_code = 'MSTK'
1290 AND id_flex_num = 101
1291 AND enabled_flag = 'Y';
1292 EXCEPTION
1293 WHEN others THEN
1294 g_MSTK_Flex_Delimiter := ' ';
1295 END;
1296 */
1297
1298 END EGO_ITEM_TEXT_UTIL;