1 PACKAGE BODY EGO_ITEM_USERATTRS_PUB AS
2 /* $Header: EGOPIUAB.pls 120.1 2008/03/11 11:16:49 deegupta ship $ */
3
4 ------------------------------------
5 -- Global Variables and Constants --
6 ------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_USERATTRS_PUB';
9 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
10 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
11
12 ----------------------------------------------------------------------
13 PROCEDURE ADD_LANGUAGE
14 IS
15 BEGIN
16 /* Solving Perf. Bug 4191046 */
17 /* The following delete and update statements are commented out */
18 /* as a quick workaround to fix the time-consuming table handler issue */
19 /*
20 delete from EGO_MTL_SY_ITEMS_EXT_TL T
21 where not exists
22 (select NULL
23 from EGO_MTL_SY_ITEMS_EXT_B B
24 where B.EXTENSION_ID = T.EXTENSION_ID
25 );
26
27 update EGO_MTL_SY_ITEMS_EXT_TL T set (
28 TL_EXT_ATTR1,
29 TL_EXT_ATTR2,
30 TL_EXT_ATTR3,
31 TL_EXT_ATTR4,
32 TL_EXT_ATTR5,
33 TL_EXT_ATTR6,
34 TL_EXT_ATTR7,
35 TL_EXT_ATTR8,
36 TL_EXT_ATTR9,
37 TL_EXT_ATTR10,
38 TL_EXT_ATTR11,
39 TL_EXT_ATTR12,
40 TL_EXT_ATTR13,
41 TL_EXT_ATTR14,
42 TL_EXT_ATTR15,
43 TL_EXT_ATTR16,
44 TL_EXT_ATTR17,
45 TL_EXT_ATTR18,
46 TL_EXT_ATTR19,
47 TL_EXT_ATTR20,
48 TL_EXT_ATTR21,
49 TL_EXT_ATTR22,
50 TL_EXT_ATTR23,
51 TL_EXT_ATTR24,
52 TL_EXT_ATTR25,
53 TL_EXT_ATTR26,
54 TL_EXT_ATTR27,
55 TL_EXT_ATTR28,
56 TL_EXT_ATTR29,
57 TL_EXT_ATTR30,
58 TL_EXT_ATTR31,
59 TL_EXT_ATTR32,
60 TL_EXT_ATTR33,
61 TL_EXT_ATTR34,
62 TL_EXT_ATTR35,
63 TL_EXT_ATTR36,
64 TL_EXT_ATTR37,
65 TL_EXT_ATTR38,
66 TL_EXT_ATTR39,
67 TL_EXT_ATTR40
68 ) =
69 (select
70 B.TL_EXT_ATTR1,
71 B.TL_EXT_ATTR2,
72 B.TL_EXT_ATTR3,
73 B.TL_EXT_ATTR4,
74 B.TL_EXT_ATTR5,
75 B.TL_EXT_ATTR6,
76 B.TL_EXT_ATTR7,
77 B.TL_EXT_ATTR8,
78 B.TL_EXT_ATTR9,
79 B.TL_EXT_ATTR10,
80 B.TL_EXT_ATTR11,
81 B.TL_EXT_ATTR12,
82 B.TL_EXT_ATTR13,
83 B.TL_EXT_ATTR14,
84 B.TL_EXT_ATTR15,
85 B.TL_EXT_ATTR16,
86 B.TL_EXT_ATTR17,
87 B.TL_EXT_ATTR18,
88 B.TL_EXT_ATTR19,
89 B.TL_EXT_ATTR20,
90 B.TL_EXT_ATTR21,
91 B.TL_EXT_ATTR22,
92 B.TL_EXT_ATTR23,
93 B.TL_EXT_ATTR24,
94 B.TL_EXT_ATTR25,
95 B.TL_EXT_ATTR26,
96 B.TL_EXT_ATTR27,
97 B.TL_EXT_ATTR28,
98 B.TL_EXT_ATTR29,
99 B.TL_EXT_ATTR30,
100 B.TL_EXT_ATTR31,
101 B.TL_EXT_ATTR32,
102 B.TL_EXT_ATTR33,
103 B.TL_EXT_ATTR34,
104 B.TL_EXT_ATTR35,
105 B.TL_EXT_ATTR36,
106 B.TL_EXT_ATTR37,
107 B.TL_EXT_ATTR38,
108 B.TL_EXT_ATTR39,
109 B.TL_EXT_ATTR40
110 from EGO_MTL_SY_ITEMS_EXT_TL B
111 where B.EXTENSION_ID = T.EXTENSION_ID
112 and B.LANGUAGE = T.SOURCE_LANG)
113 where (
114 T.EXTENSION_ID,
115 T.LANGUAGE
116 ) in (select
117 SUBT.EXTENSION_ID,
118 SUBT.LANGUAGE
119 from EGO_MTL_SY_ITEMS_EXT_TL SUBB, EGO_MTL_SY_ITEMS_EXT_TL SUBT
120 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
121 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
122 and (SUBB.TL_EXT_ATTR1 <> SUBT.TL_EXT_ATTR1
123 or (SUBB.TL_EXT_ATTR1 is null and SUBT.TL_EXT_ATTR1 is not null)
124 or (SUBB.TL_EXT_ATTR1 is not null and SUBT.TL_EXT_ATTR1 is null)
125 or SUBB.TL_EXT_ATTR2 <> SUBT.TL_EXT_ATTR2
126 or (SUBB.TL_EXT_ATTR2 is null and SUBT.TL_EXT_ATTR2 is not null)
127 or (SUBB.TL_EXT_ATTR2 is not null and SUBT.TL_EXT_ATTR2 is null)
128 or SUBB.TL_EXT_ATTR3 <> SUBT.TL_EXT_ATTR3
129 or (SUBB.TL_EXT_ATTR3 is null and SUBT.TL_EXT_ATTR3 is not null)
130 or (SUBB.TL_EXT_ATTR3 is not null and SUBT.TL_EXT_ATTR3 is null)
131 or SUBB.TL_EXT_ATTR4 <> SUBT.TL_EXT_ATTR4
132 or (SUBB.TL_EXT_ATTR4 is null and SUBT.TL_EXT_ATTR4 is not null)
133 or (SUBB.TL_EXT_ATTR4 is not null and SUBT.TL_EXT_ATTR4 is null)
134 or SUBB.TL_EXT_ATTR5 <> SUBT.TL_EXT_ATTR5
135 or (SUBB.TL_EXT_ATTR5 is null and SUBT.TL_EXT_ATTR5 is not null)
136 or (SUBB.TL_EXT_ATTR5 is not null and SUBT.TL_EXT_ATTR5 is null)
137 or SUBB.TL_EXT_ATTR6 <> SUBT.TL_EXT_ATTR6
138 or (SUBB.TL_EXT_ATTR6 is null and SUBT.TL_EXT_ATTR6 is not null)
139 or (SUBB.TL_EXT_ATTR6 is not null and SUBT.TL_EXT_ATTR6 is null)
140 or SUBB.TL_EXT_ATTR7 <> SUBT.TL_EXT_ATTR7
141 or (SUBB.TL_EXT_ATTR7 is null and SUBT.TL_EXT_ATTR7 is not null)
142 or (SUBB.TL_EXT_ATTR7 is not null and SUBT.TL_EXT_ATTR7 is null)
143 or SUBB.TL_EXT_ATTR8 <> SUBT.TL_EXT_ATTR8
144 or (SUBB.TL_EXT_ATTR8 is null and SUBT.TL_EXT_ATTR8 is not null)
145 or (SUBB.TL_EXT_ATTR8 is not null and SUBT.TL_EXT_ATTR8 is null)
146 or SUBB.TL_EXT_ATTR9 <> SUBT.TL_EXT_ATTR9
147 or (SUBB.TL_EXT_ATTR9 is null and SUBT.TL_EXT_ATTR9 is not null)
148 or (SUBB.TL_EXT_ATTR9 is not null and SUBT.TL_EXT_ATTR9 is null)
149 or SUBB.TL_EXT_ATTR10 <> SUBT.TL_EXT_ATTR10
150 or (SUBB.TL_EXT_ATTR10 is null and SUBT.TL_EXT_ATTR10 is not null)
151 or (SUBB.TL_EXT_ATTR10 is not null and SUBT.TL_EXT_ATTR10 is null)
152 or SUBB.TL_EXT_ATTR11 <> SUBT.TL_EXT_ATTR11
153 or (SUBB.TL_EXT_ATTR11 is null and SUBT.TL_EXT_ATTR11 is not null)
154 or (SUBB.TL_EXT_ATTR11 is not null and SUBT.TL_EXT_ATTR11 is null)
155 or SUBB.TL_EXT_ATTR12 <> SUBT.TL_EXT_ATTR12
156 or (SUBB.TL_EXT_ATTR12 is null and SUBT.TL_EXT_ATTR12 is not null)
157 or (SUBB.TL_EXT_ATTR12 is not null and SUBT.TL_EXT_ATTR12 is null)
158 or SUBB.TL_EXT_ATTR13 <> SUBT.TL_EXT_ATTR13
159 or (SUBB.TL_EXT_ATTR13 is null and SUBT.TL_EXT_ATTR13 is not null)
160 or (SUBB.TL_EXT_ATTR13 is not null and SUBT.TL_EXT_ATTR13 is null)
161 or SUBB.TL_EXT_ATTR14 <> SUBT.TL_EXT_ATTR14
162 or (SUBB.TL_EXT_ATTR14 is null and SUBT.TL_EXT_ATTR14 is not null)
163 or (SUBB.TL_EXT_ATTR14 is not null and SUBT.TL_EXT_ATTR14 is null)
164 or SUBB.TL_EXT_ATTR15 <> SUBT.TL_EXT_ATTR15
165 or (SUBB.TL_EXT_ATTR15 is null and SUBT.TL_EXT_ATTR15 is not null)
166 or (SUBB.TL_EXT_ATTR15 is not null and SUBT.TL_EXT_ATTR15 is null)
167 or SUBB.TL_EXT_ATTR16 <> SUBT.TL_EXT_ATTR16
168 or (SUBB.TL_EXT_ATTR16 is null and SUBT.TL_EXT_ATTR16 is not null)
169 or (SUBB.TL_EXT_ATTR16 is not null and SUBT.TL_EXT_ATTR16 is null)
170 or SUBB.TL_EXT_ATTR17 <> SUBT.TL_EXT_ATTR17
171 or (SUBB.TL_EXT_ATTR17 is null and SUBT.TL_EXT_ATTR17 is not null)
172 or (SUBB.TL_EXT_ATTR17 is not null and SUBT.TL_EXT_ATTR17 is null)
173 or SUBB.TL_EXT_ATTR18 <> SUBT.TL_EXT_ATTR18
174 or (SUBB.TL_EXT_ATTR18 is null and SUBT.TL_EXT_ATTR18 is not null)
175 or (SUBB.TL_EXT_ATTR18 is not null and SUBT.TL_EXT_ATTR18 is null)
176 or SUBB.TL_EXT_ATTR19 <> SUBT.TL_EXT_ATTR19
177 or (SUBB.TL_EXT_ATTR19 is null and SUBT.TL_EXT_ATTR19 is not null)
178 or (SUBB.TL_EXT_ATTR19 is not null and SUBT.TL_EXT_ATTR19 is null)
179 or SUBB.TL_EXT_ATTR20 <> SUBT.TL_EXT_ATTR20
180 or (SUBB.TL_EXT_ATTR20 is null and SUBT.TL_EXT_ATTR20 is not null)
181 or (SUBB.TL_EXT_ATTR20 is not null and SUBT.TL_EXT_ATTR20 is null)
182 or SUBB.TL_EXT_ATTR21 <> SUBT.TL_EXT_ATTR21
183 or (SUBB.TL_EXT_ATTR21 is null and SUBT.TL_EXT_ATTR21 is not null)
184 or (SUBB.TL_EXT_ATTR21 is not null and SUBT.TL_EXT_ATTR21 is null)
185 or SUBB.TL_EXT_ATTR22 <> SUBT.TL_EXT_ATTR22
186 or (SUBB.TL_EXT_ATTR22 is null and SUBT.TL_EXT_ATTR22 is not null)
187 or (SUBB.TL_EXT_ATTR22 is not null and SUBT.TL_EXT_ATTR22 is null)
188 or SUBB.TL_EXT_ATTR23 <> SUBT.TL_EXT_ATTR23
189 or (SUBB.TL_EXT_ATTR23 is null and SUBT.TL_EXT_ATTR23 is not null)
190 or (SUBB.TL_EXT_ATTR23 is not null and SUBT.TL_EXT_ATTR23 is null)
191 or SUBB.TL_EXT_ATTR24 <> SUBT.TL_EXT_ATTR24
192 or (SUBB.TL_EXT_ATTR24 is null and SUBT.TL_EXT_ATTR24 is not null)
193 or (SUBB.TL_EXT_ATTR24 is not null and SUBT.TL_EXT_ATTR24 is null)
194 or SUBB.TL_EXT_ATTR25 <> SUBT.TL_EXT_ATTR25
195 or (SUBB.TL_EXT_ATTR25 is null and SUBT.TL_EXT_ATTR25 is not null)
196 or (SUBB.TL_EXT_ATTR25 is not null and SUBT.TL_EXT_ATTR25 is null)
197 or SUBB.TL_EXT_ATTR26 <> SUBT.TL_EXT_ATTR26
198 or (SUBB.TL_EXT_ATTR26 is null and SUBT.TL_EXT_ATTR26 is not null)
199 or (SUBB.TL_EXT_ATTR26 is not null and SUBT.TL_EXT_ATTR26 is null)
200 or SUBB.TL_EXT_ATTR27 <> SUBT.TL_EXT_ATTR27
201 or (SUBB.TL_EXT_ATTR27 is null and SUBT.TL_EXT_ATTR27 is not null)
202 or (SUBB.TL_EXT_ATTR27 is not null and SUBT.TL_EXT_ATTR27 is null)
203 or SUBB.TL_EXT_ATTR28 <> SUBT.TL_EXT_ATTR28
204 or (SUBB.TL_EXT_ATTR28 is null and SUBT.TL_EXT_ATTR28 is not null)
205 or (SUBB.TL_EXT_ATTR28 is not null and SUBT.TL_EXT_ATTR28 is null)
206 or SUBB.TL_EXT_ATTR29 <> SUBT.TL_EXT_ATTR29
207 or (SUBB.TL_EXT_ATTR29 is null and SUBT.TL_EXT_ATTR29 is not null)
208 or (SUBB.TL_EXT_ATTR29 is not null and SUBT.TL_EXT_ATTR29 is null)
209 or SUBB.TL_EXT_ATTR30 <> SUBT.TL_EXT_ATTR30
210 or (SUBB.TL_EXT_ATTR30 is null and SUBT.TL_EXT_ATTR30 is not null)
211 or (SUBB.TL_EXT_ATTR30 is not null and SUBT.TL_EXT_ATTR30 is null)
212 or SUBB.TL_EXT_ATTR31 <> SUBT.TL_EXT_ATTR31
213 or (SUBB.TL_EXT_ATTR31 is null and SUBT.TL_EXT_ATTR31 is not null)
214 or (SUBB.TL_EXT_ATTR31 is not null and SUBT.TL_EXT_ATTR31 is null)
215 or SUBB.TL_EXT_ATTR32 <> SUBT.TL_EXT_ATTR32
216 or (SUBB.TL_EXT_ATTR32 is null and SUBT.TL_EXT_ATTR32 is not null)
217 or (SUBB.TL_EXT_ATTR32 is not null and SUBT.TL_EXT_ATTR32 is null)
218 or SUBB.TL_EXT_ATTR33 <> SUBT.TL_EXT_ATTR33
219 or (SUBB.TL_EXT_ATTR33 is null and SUBT.TL_EXT_ATTR33 is not null)
220 or (SUBB.TL_EXT_ATTR33 is not null and SUBT.TL_EXT_ATTR33 is null)
221 or SUBB.TL_EXT_ATTR34 <> SUBT.TL_EXT_ATTR34
222 or (SUBB.TL_EXT_ATTR34 is null and SUBT.TL_EXT_ATTR34 is not null)
223 or (SUBB.TL_EXT_ATTR34 is not null and SUBT.TL_EXT_ATTR34 is null)
224 or SUBB.TL_EXT_ATTR35 <> SUBT.TL_EXT_ATTR35
225 or (SUBB.TL_EXT_ATTR35 is null and SUBT.TL_EXT_ATTR35 is not null)
226 or (SUBB.TL_EXT_ATTR35 is not null and SUBT.TL_EXT_ATTR35 is null)
227 or SUBB.TL_EXT_ATTR36 <> SUBT.TL_EXT_ATTR36
228 or (SUBB.TL_EXT_ATTR36 is null and SUBT.TL_EXT_ATTR36 is not null)
229 or (SUBB.TL_EXT_ATTR36 is not null and SUBT.TL_EXT_ATTR36 is null)
230 or SUBB.TL_EXT_ATTR37 <> SUBT.TL_EXT_ATTR37
231 or (SUBB.TL_EXT_ATTR37 is null and SUBT.TL_EXT_ATTR37 is not null)
232 or (SUBB.TL_EXT_ATTR37 is not null and SUBT.TL_EXT_ATTR37 is null)
233 or SUBB.TL_EXT_ATTR38 <> SUBT.TL_EXT_ATTR38
234 or (SUBB.TL_EXT_ATTR38 is null and SUBT.TL_EXT_ATTR38 is not null)
235 or (SUBB.TL_EXT_ATTR38 is not null and SUBT.TL_EXT_ATTR38 is null)
236 or SUBB.TL_EXT_ATTR39 <> SUBT.TL_EXT_ATTR39
237 or (SUBB.TL_EXT_ATTR39 is null and SUBT.TL_EXT_ATTR39 is not null)
238 or (SUBB.TL_EXT_ATTR39 is not null and SUBT.TL_EXT_ATTR39 is null)
239 or SUBB.TL_EXT_ATTR40 <> SUBT.TL_EXT_ATTR40
240 or (SUBB.TL_EXT_ATTR40 is null and SUBT.TL_EXT_ATTR40 is not null)
241 or (SUBB.TL_EXT_ATTR40 is not null and SUBT.TL_EXT_ATTR40 is null)
242 ));
243 */
244 insert into EGO_MTL_SY_ITEMS_EXT_TL (
245 TL_EXT_ATTR23,
246 TL_EXT_ATTR24,
247 TL_EXT_ATTR25,
248 TL_EXT_ATTR26,
249 TL_EXT_ATTR27,
250 TL_EXT_ATTR28,
251 TL_EXT_ATTR29,
252 TL_EXT_ATTR30,
253 TL_EXT_ATTR31,
254 TL_EXT_ATTR32,
255 TL_EXT_ATTR33,
256 TL_EXT_ATTR34,
257 TL_EXT_ATTR35,
258 TL_EXT_ATTR36,
259 TL_EXT_ATTR37,
260 TL_EXT_ATTR38,
261 TL_EXT_ATTR39,
262 TL_EXT_ATTR40,
263 EXTENSION_ID,
264 ORGANIZATION_ID,
265 INVENTORY_ITEM_ID,
266 REVISION_ID,
267 ITEM_CATALOG_GROUP_ID,
268 ATTR_GROUP_ID,
269 DATA_LEVEL_ID,
270 CREATED_BY,
271 CREATION_DATE,
272 LAST_UPDATED_BY,
273 LAST_UPDATE_DATE,
274 LAST_UPDATE_LOGIN,
275 TL_EXT_ATTR1,
276 TL_EXT_ATTR2,
277 TL_EXT_ATTR3,
278 TL_EXT_ATTR4,
279 TL_EXT_ATTR5,
280 TL_EXT_ATTR6,
281 TL_EXT_ATTR7,
282 TL_EXT_ATTR8,
283 TL_EXT_ATTR9,
284 TL_EXT_ATTR10,
285 TL_EXT_ATTR11,
286 TL_EXT_ATTR12,
287 TL_EXT_ATTR13,
288 TL_EXT_ATTR14,
289 TL_EXT_ATTR15,
290 TL_EXT_ATTR16,
291 TL_EXT_ATTR17,
292 TL_EXT_ATTR18,
293 TL_EXT_ATTR19,
294 TL_EXT_ATTR20,
295 TL_EXT_ATTR21,
296 TL_EXT_ATTR22,
297 LANGUAGE,
298 SOURCE_LANG
299 ) select
300 B.TL_EXT_ATTR23,
301 B.TL_EXT_ATTR24,
302 B.TL_EXT_ATTR25,
303 B.TL_EXT_ATTR26,
304 B.TL_EXT_ATTR27,
305 B.TL_EXT_ATTR28,
306 B.TL_EXT_ATTR29,
307 B.TL_EXT_ATTR30,
308 B.TL_EXT_ATTR31,
309 B.TL_EXT_ATTR32,
310 B.TL_EXT_ATTR33,
311 B.TL_EXT_ATTR34,
312 B.TL_EXT_ATTR35,
313 B.TL_EXT_ATTR36,
314 B.TL_EXT_ATTR37,
315 B.TL_EXT_ATTR38,
316 B.TL_EXT_ATTR39,
317 B.TL_EXT_ATTR40,
318 B.EXTENSION_ID,
319 B.ORGANIZATION_ID,
320 B.INVENTORY_ITEM_ID,
321 B.REVISION_ID,
322 B.ITEM_CATALOG_GROUP_ID,
323 B.ATTR_GROUP_ID,
324 B.DATA_LEVEL_ID,
325 B.CREATED_BY,
326 B.CREATION_DATE,
327 B.LAST_UPDATED_BY,
328 B.LAST_UPDATE_DATE,
329 B.LAST_UPDATE_LOGIN,
330 B.TL_EXT_ATTR1,
331 B.TL_EXT_ATTR2,
332 B.TL_EXT_ATTR3,
333 B.TL_EXT_ATTR4,
334 B.TL_EXT_ATTR5,
335 B.TL_EXT_ATTR6,
336 B.TL_EXT_ATTR7,
337 B.TL_EXT_ATTR8,
338 B.TL_EXT_ATTR9,
339 B.TL_EXT_ATTR10,
340 B.TL_EXT_ATTR11,
341 B.TL_EXT_ATTR12,
342 B.TL_EXT_ATTR13,
343 B.TL_EXT_ATTR14,
344 B.TL_EXT_ATTR15,
345 B.TL_EXT_ATTR16,
346 B.TL_EXT_ATTR17,
347 B.TL_EXT_ATTR18,
348 B.TL_EXT_ATTR19,
349 B.TL_EXT_ATTR20,
350 B.TL_EXT_ATTR21,
351 B.TL_EXT_ATTR22,
352 L.LANGUAGE_CODE,
353 B.SOURCE_LANG
354 from EGO_MTL_SY_ITEMS_EXT_TL B, FND_LANGUAGES L
355 where L.INSTALLED_FLAG in ('I', 'B')
356 and B.LANGUAGE = userenv('LANG')
357 and not exists
358 (select NULL
359 from EGO_MTL_SY_ITEMS_EXT_TL T
360 where T.EXTENSION_ID = B.EXTENSION_ID
361 and T.LANGUAGE = L.LANGUAGE_CODE);
362 END ADD_LANGUAGE;
363
364
365 ----------------------------------------------------------------------
366
367 END EGO_ITEM_USERATTRS_PUB;
368
[Home] [Help]