1 package body EGO_ITEM_GTN_CHG_ATTRS_PKG as
2 /* $Header: EGOCGTIB.pls 120.0 2005/11/04 16:46 sshrikha noship $ */
3
4
5 procedure ADD_LANGUAGE
6 is
7 begin
8 delete from EGO_GTN_ATTR_CHG_TL T
9 where not exists
10 (select NULL
11 from EGO_GTN_ATTR_CHG_B B
12 where B.EXTENSION_ID = T.EXTENSION_ID
13 );
14
15 update EGO_GTN_ATTR_CHG_TL T set (
16 INVOICE_NAME,
17 DESCRIPTIVE_SIZE,
18 FUNCTIONAL_NAME,
19 TRADE_ITEM_FORM_DESCRIPTION,
20 WARRANTY_DESCRIPTION,
21 TRADE_ITEM_FINISH_DESCRIPTION,
22 DESCRIPTION_SHORT
23 ) = (select
24 B.INVOICE_NAME,
25 B.DESCRIPTIVE_SIZE,
26 B.FUNCTIONAL_NAME,
27 B.TRADE_ITEM_FORM_DESCRIPTION,
28 B.WARRANTY_DESCRIPTION,
29 B.TRADE_ITEM_FINISH_DESCRIPTION,
30 B.DESCRIPTION_SHORT
31 from EGO_GTN_ATTR_CHG_TL B
32 where B.EXTENSION_ID = T.EXTENSION_ID
33 and B.LANGUAGE = T.SOURCE_LANG)
34 where (
35 T.EXTENSION_ID,
36 T.LANGUAGE
37 ) in (select
38 SUBT.EXTENSION_ID,
39 SUBT.LANGUAGE
40 from EGO_GTN_ATTR_CHG_TL SUBB, EGO_GTN_ATTR_CHG_TL SUBT
41 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
42 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
43 and (SUBB.INVOICE_NAME <> SUBT.INVOICE_NAME
44 or (SUBB.INVOICE_NAME is null and SUBT.INVOICE_NAME is not null)
45 or (SUBB.INVOICE_NAME is not null and SUBT.INVOICE_NAME is null)
46 or SUBB.DESCRIPTIVE_SIZE <> SUBT.DESCRIPTIVE_SIZE
47 or (SUBB.DESCRIPTIVE_SIZE is null and SUBT.DESCRIPTIVE_SIZE is not null)
48 or (SUBB.DESCRIPTIVE_SIZE is not null and SUBT.DESCRIPTIVE_SIZE is null)
49 or SUBB.FUNCTIONAL_NAME <> SUBT.FUNCTIONAL_NAME
50 or (SUBB.FUNCTIONAL_NAME is null and SUBT.FUNCTIONAL_NAME is not null)
51 or (SUBB.FUNCTIONAL_NAME is not null and SUBT.FUNCTIONAL_NAME is null)
52 or SUBB.TRADE_ITEM_FORM_DESCRIPTION <> SUBT.TRADE_ITEM_FORM_DESCRIPTION
53 or (SUBB.TRADE_ITEM_FORM_DESCRIPTION is null and SUBT.TRADE_ITEM_FORM_DESCRIPTION is not null)
54 or (SUBB.TRADE_ITEM_FORM_DESCRIPTION is not null and SUBT.TRADE_ITEM_FORM_DESCRIPTION is null)
55 or SUBB.WARRANTY_DESCRIPTION <> SUBT.WARRANTY_DESCRIPTION
56 or (SUBB.WARRANTY_DESCRIPTION is null and SUBT.WARRANTY_DESCRIPTION is not null)
57 or (SUBB.WARRANTY_DESCRIPTION is not null and SUBT.WARRANTY_DESCRIPTION is null)
58 or SUBB.TRADE_ITEM_FINISH_DESCRIPTION <> SUBT.TRADE_ITEM_FINISH_DESCRIPTION
59 or (SUBB.TRADE_ITEM_FINISH_DESCRIPTION is null and SUBT.TRADE_ITEM_FINISH_DESCRIPTION is not null)
60 or (SUBB.TRADE_ITEM_FINISH_DESCRIPTION is not null and SUBT.TRADE_ITEM_FINISH_DESCRIPTION is null)
61 or SUBB.DESCRIPTION_SHORT <> SUBT.DESCRIPTION_SHORT
62 or (SUBB.DESCRIPTION_SHORT is null and SUBT.DESCRIPTION_SHORT is not null)
63 or (SUBB.DESCRIPTION_SHORT is not null and SUBT.DESCRIPTION_SHORT is null)
64 ));
65
66 insert into EGO_GTN_ATTR_CHG_TL (
67 EXTENSION_ID,
68 INVENTORY_ITEM_ID,
69 ORGANIZATION_ID,
70 CREATED_BY,
71 CREATION_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_DATE,
74 LAST_UPDATE_LOGIN,
75 ITEM_CATALOG_GROUP_ID,
76 REVISION_ID,
77 INVOICE_NAME,
78 DESCRIPTIVE_SIZE,
79 FUNCTIONAL_NAME,
80 TRADE_ITEM_FORM_DESCRIPTION,
81 WARRANTY_DESCRIPTION,
82 TRADE_ITEM_FINISH_DESCRIPTION,
83 DESCRIPTION_SHORT,
84 LANGUAGE,
85 SOURCE_LANG,
86 CHANGE_ID,
87 CHANGE_LINE_ID,
88 IMPLEMENTATION_DATE,
89 ACD_TYPE
90 ) select /*+ ORDERED */
91 B.EXTENSION_ID,
92 B.INVENTORY_ITEM_ID,
93 B.ORGANIZATION_ID,
94 B.CREATED_BY,
95 B.CREATION_DATE,
96 B.LAST_UPDATED_BY,
97 B.LAST_UPDATE_DATE,
98 B.LAST_UPDATE_LOGIN,
99 B.ITEM_CATALOG_GROUP_ID,
100 B.REVISION_ID,
101 B.INVOICE_NAME,
102 B.DESCRIPTIVE_SIZE,
103 B.FUNCTIONAL_NAME,
104 B.TRADE_ITEM_FORM_DESCRIPTION,
105 B.WARRANTY_DESCRIPTION,
106 B.TRADE_ITEM_FINISH_DESCRIPTION,
107 B.DESCRIPTION_SHORT,
108 L.LANGUAGE_CODE,
109 B.SOURCE_LANG,
110 B.CHANGE_ID,
111 B.CHANGE_LINE_ID,
112 B.IMPLEMENTATION_DATE,
113 B.ACD_TYPE
114 from EGO_GTN_ATTR_CHG_TL B, FND_LANGUAGES L
115 where L.INSTALLED_FLAG in ('I', 'B')
116 and B.LANGUAGE = userenv('LANG')
117 and not exists
118 (select NULL
119 from EGO_GTN_ATTR_CHG_TL T
120 where T.EXTENSION_ID = B.EXTENSION_ID
121 and T.LANGUAGE = L.LANGUAGE_CODE);
122 end ADD_LANGUAGE;
123
124 end EGO_ITEM_GTN_CHG_ATTRS_PKG;