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