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