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