1 package body DOM_DOCUMENT_CATEGORIES_PKG as
2 /* $Header: DOMCATGB.pls 120.0 2006/02/23 02:35 rkhasa noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_CATALOG_ID in NUMBER,
6 X_CATEGORY_ID in NUMBER,
7 X_PARENT_CATEGORY_ID in NUMBER,
8 X_DOC_CREATION_ALLOWED in VARCHAR2,
9 X_INACTIVE_ON in DATE,
10 X_DEFAULT_FOLDER_OPTION in VARCHAR2,
11 X_DEFAULT_REPOSITORY_ID in NUMBER,
12 X_DEFAULT_FOLDER_LOCATION in VARCHAR2,
13 X_DEF_FOLDER_NAMING_METHOD in VARCHAR2,
14 X_DEF_FOLDER_PREFIX in VARCHAR2,
15 X_DEF_FOLDER_SUFFIX in VARCHAR2,
16 X_DOC_NUM_SCHEME in VARCHAR2,
17 X_DOC_NUM_PREFIX in VARCHAR2,
18 X_DOC_NUM_START_NUMBER in NUMBER,
19 X_DOC_NUM_INCR in NUMBER,
20 X_DOC_NUM_SUFFIX in VARCHAR2,
21 X_DOC_NUM_FUNC_ACTION_ID in NUMBER,
22 X_DOC_REV_SCHEME in VARCHAR2,
23 X_DOC_REV_SEEDED_SEQ_CODE in VARCHAR2,
24 X_DOC_REV_PREFIX in VARCHAR2,
25 X_DOC_REV_START_NUMBER in NUMBER,
26 X_DOC_REV_INCR in NUMBER,
27 X_DOC_REV_SUFFIX in VARCHAR2,
28 X_DOC_REV_FUNC_ACTION_ID in NUMBER,
29 X_DOC_NAME_SCHEME in VARCHAR2,
30 X_DOC_NAME_FUNC_ACTION_ID in NUMBER,
31 X_NAME in VARCHAR2,
32 X_DESCRIPTION in VARCHAR2,
33 X_CREATION_DATE in DATE,
34 X_CREATED_BY in NUMBER,
35 X_LAST_UPDATE_DATE in DATE,
36 X_LAST_UPDATED_BY in NUMBER,
37 X_LAST_UPDATE_LOGIN in NUMBER
38 ) is
39 cursor C is select ROWID from DOM_DOCUMENT_CATEGORIES
40 where CATALOG_ID = X_CATALOG_ID
41 and CATEGORY_ID = X_CATEGORY_ID
42 ;
43 begin
44 insert into DOM_DOCUMENT_CATEGORIES (
45 CATALOG_ID,
46 CATEGORY_ID,
47 PARENT_CATEGORY_ID,
48 DOC_CREATION_ALLOWED,
49 INACTIVE_ON,
50 DEFAULT_FOLDER_OPTION,
51 DEFAULT_REPOSITORY_ID,
52 DEFAULT_FOLDER_LOCATION,
53 DEF_FOLDER_NAMING_METHOD,
54 DEF_FOLDER_PREFIX,
55 DEF_FOLDER_SUFFIX,
56 DOC_NUM_SCHEME,
57 DOC_NUM_PREFIX,
58 DOC_NUM_START_NUMBER,
59 DOC_NUM_INCR,
60 DOC_NUM_SUFFIX,
61 DOC_NUM_FUNC_ACTION_ID,
62 DOC_REV_SCHEME,
63 DOC_REV_SEEDED_SEQ_CODE,
64 DOC_REV_PREFIX,
65 DOC_REV_START_NUMBER,
66 DOC_REV_INCR,
67 DOC_REV_SUFFIX,
68 DOC_REV_FUNC_ACTION_ID,
69 DOC_NAME_SCHEME,
70 DOC_NAME_FUNC_ACTION_ID,
71 CREATION_DATE,
72 CREATED_BY,
73 LAST_UPDATE_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_LOGIN
76 ) values (
77 X_CATALOG_ID,
78 X_CATEGORY_ID,
79 X_PARENT_CATEGORY_ID,
80 X_DOC_CREATION_ALLOWED,
81 X_INACTIVE_ON,
82 X_DEFAULT_FOLDER_OPTION,
83 X_DEFAULT_REPOSITORY_ID,
84 X_DEFAULT_FOLDER_LOCATION,
85 X_DEF_FOLDER_NAMING_METHOD,
86 X_DEF_FOLDER_PREFIX,
87 X_DEF_FOLDER_SUFFIX,
88 X_DOC_NUM_SCHEME,
89 X_DOC_NUM_PREFIX,
90 X_DOC_NUM_START_NUMBER,
91 X_DOC_NUM_INCR,
92 X_DOC_NUM_SUFFIX,
93 X_DOC_NUM_FUNC_ACTION_ID,
94 X_DOC_REV_SCHEME,
95 X_DOC_REV_SEEDED_SEQ_CODE,
96 X_DOC_REV_PREFIX,
97 X_DOC_REV_START_NUMBER,
98 X_DOC_REV_INCR,
99 X_DOC_REV_SUFFIX,
100 X_DOC_REV_FUNC_ACTION_ID,
101 X_DOC_NAME_SCHEME,
102 X_DOC_NAME_FUNC_ACTION_ID,
103 X_CREATION_DATE,
104 X_CREATED_BY,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_LAST_UPDATE_LOGIN
108 );
109
110 insert into DOM_DOCUMENT_CATEGORIES_TL (
111 LAST_UPDATE_LOGIN,
112 CATALOG_ID,
113 CATEGORY_ID,
114 NAME,
115 DESCRIPTION,
116 CREATION_DATE,
117 CREATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATED_BY,
120 LANGUAGE,
121 SOURCE_LANG
122 ) select
123 X_LAST_UPDATE_LOGIN,
124 X_CATALOG_ID,
125 X_CATEGORY_ID,
126 X_NAME,
127 X_DESCRIPTION,
128 X_CREATION_DATE,
129 X_CREATED_BY,
130 X_LAST_UPDATE_DATE,
131 X_LAST_UPDATED_BY,
132 L.LANGUAGE_CODE,
133 userenv('LANG')
134 from FND_LANGUAGES L
135 where L.INSTALLED_FLAG in ('I', 'B')
136 and not exists
137 (select NULL
138 from DOM_DOCUMENT_CATEGORIES_TL T
139 where T.CATALOG_ID = X_CATALOG_ID
140 and T.CATEGORY_ID = X_CATEGORY_ID
141 and T.LANGUAGE = L.LANGUAGE_CODE);
142
143 open c;
144 fetch c into X_ROWID;
145 if (c%notfound) then
146 close c;
147 raise no_data_found;
148 end if;
149 close c;
150
151 end INSERT_ROW;
152
153 procedure LOCK_ROW (
154 X_CATALOG_ID in NUMBER,
155 X_CATEGORY_ID in NUMBER,
156 X_PARENT_CATEGORY_ID in NUMBER,
157 X_DOC_CREATION_ALLOWED in VARCHAR2,
158 X_INACTIVE_ON in DATE,
159 X_DEFAULT_FOLDER_OPTION in VARCHAR2,
160 X_DEFAULT_REPOSITORY_ID in NUMBER,
161 X_DEFAULT_FOLDER_LOCATION in VARCHAR2,
162 X_DEF_FOLDER_NAMING_METHOD in VARCHAR2,
163 X_DEF_FOLDER_PREFIX in VARCHAR2,
164 X_DEF_FOLDER_SUFFIX in VARCHAR2,
165 X_DOC_NUM_SCHEME in VARCHAR2,
166 X_DOC_NUM_PREFIX in VARCHAR2,
167 X_DOC_NUM_START_NUMBER in NUMBER,
168 X_DOC_NUM_INCR in NUMBER,
169 X_DOC_NUM_SUFFIX in VARCHAR2,
170 X_DOC_NUM_FUNC_ACTION_ID in NUMBER,
171 X_DOC_REV_SCHEME in VARCHAR2,
172 X_DOC_REV_SEEDED_SEQ_CODE in VARCHAR2,
173 X_DOC_REV_PREFIX in VARCHAR2,
174 X_DOC_REV_START_NUMBER in NUMBER,
175 X_DOC_REV_INCR in NUMBER,
176 X_DOC_REV_SUFFIX in VARCHAR2,
177 X_DOC_REV_FUNC_ACTION_ID in NUMBER,
178 X_DOC_NAME_SCHEME in VARCHAR2,
179 X_DOC_NAME_FUNC_ACTION_ID in NUMBER,
180 X_NAME in VARCHAR2,
181 X_DESCRIPTION in VARCHAR2
182 ) is
183 cursor c is select
184 PARENT_CATEGORY_ID,
185 DOC_CREATION_ALLOWED,
186 INACTIVE_ON,
187 DEFAULT_FOLDER_OPTION,
188 DEFAULT_REPOSITORY_ID,
189 DEFAULT_FOLDER_LOCATION,
190 DEF_FOLDER_NAMING_METHOD,
191 DEF_FOLDER_PREFIX,
192 DEF_FOLDER_SUFFIX,
193 DOC_NUM_SCHEME,
194 DOC_NUM_PREFIX,
195 DOC_NUM_START_NUMBER,
196 DOC_NUM_INCR,
197 DOC_NUM_SUFFIX,
198 DOC_NUM_FUNC_ACTION_ID,
199 DOC_REV_SCHEME,
200 DOC_REV_SEEDED_SEQ_CODE,
201 DOC_REV_PREFIX,
202 DOC_REV_START_NUMBER,
203 DOC_REV_INCR,
204 DOC_REV_SUFFIX,
205 DOC_REV_FUNC_ACTION_ID,
206 DOC_NAME_SCHEME,
207 DOC_NAME_FUNC_ACTION_ID
208 from DOM_DOCUMENT_CATEGORIES
209 where CATALOG_ID = X_CATALOG_ID
210 and CATEGORY_ID = X_CATEGORY_ID
211 for update of CATALOG_ID nowait;
212 recinfo c%rowtype;
213
214 cursor c1 is select
215 NAME,
216 DESCRIPTION,
217 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
218 from DOM_DOCUMENT_CATEGORIES_TL
219 where CATALOG_ID = X_CATALOG_ID
220 and CATEGORY_ID = X_CATEGORY_ID
221 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
222 for update of CATALOG_ID nowait;
223 begin
224 open c;
225 fetch c into recinfo;
226 if (c%notfound) then
227 close c;
228 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
229 app_exception.raise_exception;
230 end if;
231 close c;
232 if ( ((recinfo.PARENT_CATEGORY_ID = X_PARENT_CATEGORY_ID)
233 OR ((recinfo.PARENT_CATEGORY_ID is null) AND (X_PARENT_CATEGORY_ID is null)))
234 AND ((recinfo.DOC_CREATION_ALLOWED = X_DOC_CREATION_ALLOWED)
235 OR ((recinfo.DOC_CREATION_ALLOWED is null) AND (X_DOC_CREATION_ALLOWED is null)))
236 AND ((recinfo.INACTIVE_ON = X_INACTIVE_ON)
237 OR ((recinfo.INACTIVE_ON is null) AND (X_INACTIVE_ON is null)))
238 AND ((recinfo.DEFAULT_FOLDER_OPTION = X_DEFAULT_FOLDER_OPTION)
239 OR ((recinfo.DEFAULT_FOLDER_OPTION is null) AND (X_DEFAULT_FOLDER_OPTION is null)))
240 AND ((recinfo.DEFAULT_REPOSITORY_ID = X_DEFAULT_REPOSITORY_ID)
241 OR ((recinfo.DEFAULT_REPOSITORY_ID is null) AND (X_DEFAULT_REPOSITORY_ID is null)))
242 AND ((recinfo.DEFAULT_FOLDER_LOCATION = X_DEFAULT_FOLDER_LOCATION)
243 OR ((recinfo.DEFAULT_FOLDER_LOCATION is null) AND (X_DEFAULT_FOLDER_LOCATION is null)))
244 AND ((recinfo.DEF_FOLDER_NAMING_METHOD = X_DEF_FOLDER_NAMING_METHOD)
245 OR ((recinfo.DEF_FOLDER_NAMING_METHOD is null) AND (X_DEF_FOLDER_NAMING_METHOD is null)))
246 AND ((recinfo.DEF_FOLDER_PREFIX = X_DEF_FOLDER_PREFIX)
247 OR ((recinfo.DEF_FOLDER_PREFIX is null) AND (X_DEF_FOLDER_PREFIX is null)))
248 AND ((recinfo.DEF_FOLDER_SUFFIX = X_DEF_FOLDER_SUFFIX)
249 OR ((recinfo.DEF_FOLDER_SUFFIX is null) AND (X_DEF_FOLDER_SUFFIX is null)))
250 AND ((recinfo.DOC_NUM_SCHEME = X_DOC_NUM_SCHEME)
251 OR ((recinfo.DOC_NUM_SCHEME is null) AND (X_DOC_NUM_SCHEME is null)))
252 AND ((recinfo.DOC_NUM_PREFIX = X_DOC_NUM_PREFIX)
253 OR ((recinfo.DOC_NUM_PREFIX is null) AND (X_DOC_NUM_PREFIX is null)))
254 AND ((recinfo.DOC_NUM_START_NUMBER = X_DOC_NUM_START_NUMBER)
255 OR ((recinfo.DOC_NUM_START_NUMBER is null) AND (X_DOC_NUM_START_NUMBER is null)))
256 AND ((recinfo.DOC_NUM_INCR = X_DOC_NUM_INCR)
257 OR ((recinfo.DOC_NUM_INCR is null) AND (X_DOC_NUM_INCR is null)))
258 AND ((recinfo.DOC_NUM_SUFFIX = X_DOC_NUM_SUFFIX)
259 OR ((recinfo.DOC_NUM_SUFFIX is null) AND (X_DOC_NUM_SUFFIX is null)))
260 AND ((recinfo.DOC_NUM_FUNC_ACTION_ID = X_DOC_NUM_FUNC_ACTION_ID)
261 OR ((recinfo.DOC_NUM_FUNC_ACTION_ID is null) AND (X_DOC_NUM_FUNC_ACTION_ID is null)))
262 AND ((recinfo.DOC_REV_SCHEME = X_DOC_REV_SCHEME)
263 OR ((recinfo.DOC_REV_SCHEME is null) AND (X_DOC_REV_SCHEME is null)))
264 AND ((recinfo.DOC_REV_SEEDED_SEQ_CODE = X_DOC_REV_SEEDED_SEQ_CODE)
265 OR ((recinfo.DOC_REV_SEEDED_SEQ_CODE is null) AND (X_DOC_REV_SEEDED_SEQ_CODE is null)))
266 AND ((recinfo.DOC_REV_PREFIX = X_DOC_REV_PREFIX)
267 OR ((recinfo.DOC_REV_PREFIX is null) AND (X_DOC_REV_PREFIX is null)))
268 AND ((recinfo.DOC_REV_START_NUMBER = X_DOC_REV_START_NUMBER)
269 OR ((recinfo.DOC_REV_START_NUMBER is null) AND (X_DOC_REV_START_NUMBER is null)))
270 AND ((recinfo.DOC_REV_INCR = X_DOC_REV_INCR)
271 OR ((recinfo.DOC_REV_INCR is null) AND (X_DOC_REV_INCR is null)))
272 AND ((recinfo.DOC_REV_SUFFIX = X_DOC_REV_SUFFIX)
273 OR ((recinfo.DOC_REV_SUFFIX is null) AND (X_DOC_REV_SUFFIX is null)))
274 AND ((recinfo.DOC_REV_FUNC_ACTION_ID = X_DOC_REV_FUNC_ACTION_ID)
275 OR ((recinfo.DOC_REV_FUNC_ACTION_ID is null) AND (X_DOC_REV_FUNC_ACTION_ID is null)))
276 AND ((recinfo.DOC_NAME_SCHEME = X_DOC_NAME_SCHEME)
277 OR ((recinfo.DOC_NAME_SCHEME is null) AND (X_DOC_NAME_SCHEME is null)))
278 AND ((recinfo.DOC_NAME_FUNC_ACTION_ID = X_DOC_NAME_FUNC_ACTION_ID)
279 OR ((recinfo.DOC_NAME_FUNC_ACTION_ID is null) AND (X_DOC_NAME_FUNC_ACTION_ID is null)))
280 ) then
281 null;
282 else
283 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
284 app_exception.raise_exception;
285 end if;
286
287 for tlinfo in c1 loop
288 if (tlinfo.BASELANG = 'Y') then
289 if ( (tlinfo.NAME = X_NAME)
290 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
291 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
292 ) then
293 null;
294 else
295 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
296 app_exception.raise_exception;
297 end if;
298 end if;
299 end loop;
300 return;
301 end LOCK_ROW;
302
303 procedure UPDATE_ROW (
304 X_CATALOG_ID in NUMBER,
305 X_CATEGORY_ID in NUMBER,
306 X_PARENT_CATEGORY_ID in NUMBER,
307 X_DOC_CREATION_ALLOWED in VARCHAR2,
308 X_INACTIVE_ON in DATE,
309 X_DEFAULT_FOLDER_OPTION in VARCHAR2,
310 X_DEFAULT_REPOSITORY_ID in NUMBER,
311 X_DEFAULT_FOLDER_LOCATION in VARCHAR2,
312 X_DEF_FOLDER_NAMING_METHOD in VARCHAR2,
313 X_DEF_FOLDER_PREFIX in VARCHAR2,
314 X_DEF_FOLDER_SUFFIX in VARCHAR2,
315 X_DOC_NUM_SCHEME in VARCHAR2,
316 X_DOC_NUM_PREFIX in VARCHAR2,
317 X_DOC_NUM_START_NUMBER in NUMBER,
318 X_DOC_NUM_INCR in NUMBER,
319 X_DOC_NUM_SUFFIX in VARCHAR2,
320 X_DOC_NUM_FUNC_ACTION_ID in NUMBER,
321 X_DOC_REV_SCHEME in VARCHAR2,
322 X_DOC_REV_SEEDED_SEQ_CODE in VARCHAR2,
323 X_DOC_REV_PREFIX in VARCHAR2,
324 X_DOC_REV_START_NUMBER in NUMBER,
325 X_DOC_REV_INCR in NUMBER,
326 X_DOC_REV_SUFFIX in VARCHAR2,
327 X_DOC_REV_FUNC_ACTION_ID in NUMBER,
328 X_DOC_NAME_SCHEME in VARCHAR2,
329 X_DOC_NAME_FUNC_ACTION_ID in NUMBER,
330 X_NAME in VARCHAR2,
331 X_DESCRIPTION in VARCHAR2,
332 X_LAST_UPDATE_DATE in DATE,
333 X_LAST_UPDATED_BY in NUMBER,
334 X_LAST_UPDATE_LOGIN in NUMBER
335 ) is
336 begin
337 update DOM_DOCUMENT_CATEGORIES set
338 PARENT_CATEGORY_ID = X_PARENT_CATEGORY_ID,
339 DOC_CREATION_ALLOWED = X_DOC_CREATION_ALLOWED,
340 INACTIVE_ON = X_INACTIVE_ON,
341 DEFAULT_FOLDER_OPTION = X_DEFAULT_FOLDER_OPTION,
342 DEFAULT_REPOSITORY_ID = X_DEFAULT_REPOSITORY_ID,
343 DEFAULT_FOLDER_LOCATION = X_DEFAULT_FOLDER_LOCATION,
344 DEF_FOLDER_NAMING_METHOD = X_DEF_FOLDER_NAMING_METHOD,
345 DEF_FOLDER_PREFIX = X_DEF_FOLDER_PREFIX,
346 DEF_FOLDER_SUFFIX = X_DEF_FOLDER_SUFFIX,
347 DOC_NUM_SCHEME = X_DOC_NUM_SCHEME,
348 DOC_NUM_PREFIX = X_DOC_NUM_PREFIX,
349 DOC_NUM_START_NUMBER = X_DOC_NUM_START_NUMBER,
350 DOC_NUM_INCR = X_DOC_NUM_INCR,
351 DOC_NUM_SUFFIX = X_DOC_NUM_SUFFIX,
352 DOC_NUM_FUNC_ACTION_ID = X_DOC_NUM_FUNC_ACTION_ID,
353 DOC_REV_SCHEME = X_DOC_REV_SCHEME,
354 DOC_REV_SEEDED_SEQ_CODE = X_DOC_REV_SEEDED_SEQ_CODE,
355 DOC_REV_PREFIX = X_DOC_REV_PREFIX,
356 DOC_REV_START_NUMBER = X_DOC_REV_START_NUMBER,
357 DOC_REV_INCR = X_DOC_REV_INCR,
358 DOC_REV_SUFFIX = X_DOC_REV_SUFFIX,
359 DOC_REV_FUNC_ACTION_ID = X_DOC_REV_FUNC_ACTION_ID,
360 DOC_NAME_SCHEME = X_DOC_NAME_SCHEME,
361 DOC_NAME_FUNC_ACTION_ID = X_DOC_NAME_FUNC_ACTION_ID,
362 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
363 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
364 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
365 where CATALOG_ID = X_CATALOG_ID
366 and CATEGORY_ID = X_CATEGORY_ID;
367
368 if (sql%notfound) then
369 raise no_data_found;
370 end if;
371
372 update DOM_DOCUMENT_CATEGORIES_TL set
373 NAME = X_NAME,
374 DESCRIPTION = X_DESCRIPTION,
375 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
376 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
378 SOURCE_LANG = userenv('LANG')
379 where CATALOG_ID = X_CATALOG_ID
380 and CATEGORY_ID = X_CATEGORY_ID
381 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
382
383 if (sql%notfound) then
384 raise no_data_found;
385 end if;
386 end UPDATE_ROW;
387
388 procedure DELETE_ROW (
389 X_CATALOG_ID in NUMBER,
390 X_CATEGORY_ID in NUMBER
391 ) is
392 begin
393 delete from DOM_DOCUMENT_CATEGORIES_TL
394 where CATALOG_ID = X_CATALOG_ID
395 and CATEGORY_ID = X_CATEGORY_ID;
396
397 if (sql%notfound) then
398 raise no_data_found;
399 end if;
400
401 delete from DOM_DOCUMENT_CATEGORIES
402 where CATALOG_ID = X_CATALOG_ID
403 and CATEGORY_ID = X_CATEGORY_ID;
404
405 if (sql%notfound) then
406 raise no_data_found;
407 end if;
408 end DELETE_ROW;
409
410 procedure ADD_LANGUAGE
411 is
412 begin
413 delete from DOM_DOCUMENT_CATEGORIES_TL T
414 where not exists
415 (select NULL
416 from DOM_DOCUMENT_CATEGORIES B
417 where B.CATALOG_ID = T.CATALOG_ID
418 and B.CATEGORY_ID = T.CATEGORY_ID
419 );
420
421 update DOM_DOCUMENT_CATEGORIES_TL T set (
422 NAME,
423 DESCRIPTION
424 ) = (select
425 B.NAME,
426 B.DESCRIPTION
427 from DOM_DOCUMENT_CATEGORIES_TL B
428 where B.CATALOG_ID = T.CATALOG_ID
429 and B.CATEGORY_ID = T.CATEGORY_ID
430 and B.LANGUAGE = T.SOURCE_LANG)
431 where (
432 T.CATALOG_ID,
433 T.CATEGORY_ID,
434 T.LANGUAGE
435 ) in (select
436 SUBT.CATALOG_ID,
437 SUBT.CATEGORY_ID,
438 SUBT.LANGUAGE
439 from DOM_DOCUMENT_CATEGORIES_TL SUBB, DOM_DOCUMENT_CATEGORIES_TL SUBT
440 where SUBB.CATALOG_ID = SUBT.CATALOG_ID
441 and SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
442 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
443 and (SUBB.NAME <> SUBT.NAME
444 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
445 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
446 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
447 ));
448
449 insert into DOM_DOCUMENT_CATEGORIES_TL (
450 LAST_UPDATE_LOGIN,
451 CATALOG_ID,
452 CATEGORY_ID,
453 NAME,
454 DESCRIPTION,
455 CREATION_DATE,
456 CREATED_BY,
457 LAST_UPDATE_DATE,
458 LAST_UPDATED_BY,
459 LANGUAGE,
460 SOURCE_LANG
461 ) select /*+ ORDERED */
462 B.LAST_UPDATE_LOGIN,
463 B.CATALOG_ID,
464 B.CATEGORY_ID,
465 B.NAME,
466 B.DESCRIPTION,
467 B.CREATION_DATE,
468 B.CREATED_BY,
469 B.LAST_UPDATE_DATE,
470 B.LAST_UPDATED_BY,
471 L.LANGUAGE_CODE,
472 B.SOURCE_LANG
473 from DOM_DOCUMENT_CATEGORIES_TL B, FND_LANGUAGES L
474 where L.INSTALLED_FLAG in ('I', 'B')
475 and B.LANGUAGE = userenv('LANG')
476 and not exists
477 (select NULL
478 from DOM_DOCUMENT_CATEGORIES_TL T
479 where T.CATALOG_ID = B.CATALOG_ID
480 and T.CATEGORY_ID = B.CATEGORY_ID
481 and T.LANGUAGE = L.LANGUAGE_CODE);
482 end ADD_LANGUAGE;
483
484 end DOM_DOCUMENT_CATEGORIES_PKG;