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