1 package body PA_CI_TYPES_PKG as
2 /* $Header: PACITYTB.pls 120.2 2005/08/22 05:16:13 sukhanna noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_CI_TYPE_ID in NUMBER,
7 X_CI_TYPE_CLASS_CODE in VARCHAR2,
8 X_AUTO_NUMBER_FLAG in VARCHAR2,
9 X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
10 X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
11 X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
12 X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
13 X_RECORD_VERSION_NUMBER in NUMBER,
14 X_START_DATE_ACTIVE in DATE,
15 X_END_DATE_ACTIVE in DATE,
16 X_CLASSIFICATION_CATEGORY in VARCHAR2,
17 X_REASON_CATEGORY in VARCHAR2,
18 X_RESOLUTION_CATEGORY in VARCHAR2,
19 X_ATTRIBUTE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 X_NAME in VARCHAR2,
36 X_SHORT_NAME in VARCHAR2,
37 X_DESCRIPTION in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER
43 ) is
44 cursor C is select ROWID from PA_CI_TYPES_B
45 where CI_TYPE_ID = X_CI_TYPE_ID
46 ;
47 l_rowid rowid; --Added for bug#4565156
48 begin
49
50 l_rowid := x_rowid; --Added for bug#4565156
51 insert into PA_CI_TYPES_B (
52 CI_TYPE_ID,
53 CI_TYPE_CLASS_CODE,
54 AUTO_NUMBER_FLAG,
55 RESOLUTION_REQUIRED_FLAG,
56 APPROVAL_REQUIRED_FLAG,
57 SOURCE_ATTRS_ENABLED_FLAG,
58 ALLOW_ALL_USAGE_FLAG,
59 RECORD_VERSION_NUMBER,
60 START_DATE_ACTIVE,
61 END_DATE_ACTIVE,
62 CLASSIFICATION_CATEGORY,
63 REASON_CATEGORY,
64 RESOLUTION_CATEGORY,
65 ATTRIBUTE_CATEGORY,
66 ATTRIBUTE1,
67 ATTRIBUTE2,
68 ATTRIBUTE3,
69 ATTRIBUTE4,
70 ATTRIBUTE5,
71 ATTRIBUTE6,
72 ATTRIBUTE7,
73 ATTRIBUTE8,
74 ATTRIBUTE9,
75 ATTRIBUTE10,
76 ATTRIBUTE11,
77 ATTRIBUTE12,
78 ATTRIBUTE13,
79 ATTRIBUTE14,
80 ATTRIBUTE15,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN
86 ) values (
87 X_CI_TYPE_ID,
88 X_CI_TYPE_CLASS_CODE,
89 X_AUTO_NUMBER_FLAG,
90 X_RESOLUTION_REQUIRED_FLAG,
91 X_APPROVAL_REQUIRED_FLAG,
92 X_SOURCE_ATTRS_ENABLED_FLAG,
93 X_ALLOW_ALL_USAGE_FLAG,
94 X_RECORD_VERSION_NUMBER,
95 X_START_DATE_ACTIVE,
96 X_END_DATE_ACTIVE,
97 X_CLASSIFICATION_CATEGORY,
98 X_REASON_CATEGORY,
99 X_RESOLUTION_CATEGORY,
100 X_ATTRIBUTE_CATEGORY,
101 X_ATTRIBUTE1,
102 X_ATTRIBUTE2,
103 X_ATTRIBUTE3,
104 X_ATTRIBUTE4,
105 X_ATTRIBUTE5,
106 X_ATTRIBUTE6,
107 X_ATTRIBUTE7,
108 X_ATTRIBUTE8,
109 X_ATTRIBUTE9,
110 X_ATTRIBUTE10,
111 X_ATTRIBUTE11,
112 X_ATTRIBUTE12,
113 X_ATTRIBUTE13,
114 X_ATTRIBUTE14,
115 X_ATTRIBUTE15,
116 X_CREATION_DATE,
117 X_CREATED_BY,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_LAST_UPDATE_LOGIN
121 );
122
123 insert into PA_CI_TYPES_TL (
124 CI_TYPE_ID,
125 NAME,
126 SHORT_NAME,
127 LAST_UPDATED_BY,
128 CREATED_BY,
129 CREATION_DATE,
130 LAST_UPDATE_DATE,
131 LAST_UPDATE_LOGIN,
132 DESCRIPTION,
133 LANGUAGE,
134 SOURCE_LANG
135 ) select
136 X_CI_TYPE_ID,
137 X_NAME,
138 X_SHORT_NAME,
139 X_LAST_UPDATED_BY,
140 X_CREATED_BY,
141 X_CREATION_DATE,
142 X_LAST_UPDATE_DATE,
143 X_LAST_UPDATE_LOGIN,
144 X_DESCRIPTION,
145 L.LANGUAGE_CODE,
146 userenv('LANG')
147 from FND_LANGUAGES L
148 where L.INSTALLED_FLAG in ('I', 'B')
149 and not exists
150 (select NULL
151 from PA_CI_TYPES_TL T
152 where T.CI_TYPE_ID = X_CI_TYPE_ID
153 and T.LANGUAGE = L.LANGUAGE_CODE);
154
155 open c;
156 fetch c into X_ROWID;
157 if (c%notfound) then
158 close c;
159 raise no_data_found;
160 end if;
161 close c;
162
163 exception --Added for bug 4565156
164 when others then
165 x_rowid := l_rowid;
166 raise;
167
168 end INSERT_ROW;
169
170 procedure LOCK_ROW (
171 X_CI_TYPE_ID in NUMBER,
172 X_CI_TYPE_CLASS_CODE in VARCHAR2,
173 X_AUTO_NUMBER_FLAG in VARCHAR2,
174 X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
175 X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
176 X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
177 X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
178 X_RECORD_VERSION_NUMBER in NUMBER,
179 X_START_DATE_ACTIVE in DATE,
180 X_END_DATE_ACTIVE in DATE,
181 X_CLASSIFICATION_CATEGORY in VARCHAR2,
182 X_REASON_CATEGORY in VARCHAR2,
183 X_RESOLUTION_CATEGORY in VARCHAR2,
184 X_ATTRIBUTE_CATEGORY in VARCHAR2,
185 X_ATTRIBUTE1 in VARCHAR2,
186 X_ATTRIBUTE2 in VARCHAR2,
187 X_ATTRIBUTE3 in VARCHAR2,
188 X_ATTRIBUTE4 in VARCHAR2,
189 X_ATTRIBUTE5 in VARCHAR2,
190 X_ATTRIBUTE6 in VARCHAR2,
191 X_ATTRIBUTE7 in VARCHAR2,
192 X_ATTRIBUTE8 in VARCHAR2,
193 X_ATTRIBUTE9 in VARCHAR2,
194 X_ATTRIBUTE10 in VARCHAR2,
195 X_ATTRIBUTE11 in VARCHAR2,
196 X_ATTRIBUTE12 in VARCHAR2,
197 X_ATTRIBUTE13 in VARCHAR2,
198 X_ATTRIBUTE14 in VARCHAR2,
199 X_ATTRIBUTE15 in VARCHAR2,
200 X_NAME in VARCHAR2,
201 X_SHORT_NAME in VARCHAR2,
202 X_DESCRIPTION in VARCHAR2
203 ) is
204 cursor c is select
205 CI_TYPE_CLASS_CODE,
206 AUTO_NUMBER_FLAG,
207 RESOLUTION_REQUIRED_FLAG,
208 APPROVAL_REQUIRED_FLAG,
209 SOURCE_ATTRS_ENABLED_FLAG,
210 ALLOW_ALL_USAGE_FLAG,
211 RECORD_VERSION_NUMBER,
212 START_DATE_ACTIVE,
213 END_DATE_ACTIVE,
214 CLASSIFICATION_CATEGORY,
215 REASON_CATEGORY,
216 RESOLUTION_CATEGORY,
217 ATTRIBUTE_CATEGORY,
218 ATTRIBUTE1,
219 ATTRIBUTE2,
220 ATTRIBUTE3,
221 ATTRIBUTE4,
222 ATTRIBUTE5,
223 ATTRIBUTE6,
224 ATTRIBUTE7,
225 ATTRIBUTE8,
226 ATTRIBUTE9,
227 ATTRIBUTE10,
228 ATTRIBUTE11,
229 ATTRIBUTE12,
230 ATTRIBUTE13,
231 ATTRIBUTE14,
232 ATTRIBUTE15
233 from PA_CI_TYPES_B
234 where CI_TYPE_ID = X_CI_TYPE_ID
235 for update of CI_TYPE_ID nowait;
236 recinfo c%rowtype;
237
238 cursor c1 is select
239 NAME,
240 SHORT_NAME,
241 DESCRIPTION,
242 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
243 from PA_CI_TYPES_TL
244 where CI_TYPE_ID = X_CI_TYPE_ID
245 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
246 for update of CI_TYPE_ID nowait;
247 begin
248 open c;
249 fetch c into recinfo;
250 if (c%notfound) then
251 close c;
252 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
253 app_exception.raise_exception;
254 end if;
255 close c;
256 if ( (recinfo.CI_TYPE_CLASS_CODE = X_CI_TYPE_CLASS_CODE)
257 AND (recinfo.AUTO_NUMBER_FLAG = X_AUTO_NUMBER_FLAG)
258 AND (recinfo.RESOLUTION_REQUIRED_FLAG = X_RESOLUTION_REQUIRED_FLAG)
259 AND (recinfo.APPROVAL_REQUIRED_FLAG = X_APPROVAL_REQUIRED_FLAG)
260 AND (recinfo.SOURCE_ATTRS_ENABLED_FLAG = X_SOURCE_ATTRS_ENABLED_FLAG)
261 AND (recinfo.ALLOW_ALL_USAGE_FLAG = X_ALLOW_ALL_USAGE_FLAG)
262 AND (recinfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER)
263 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
264 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
265 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
266 AND ((recinfo.CLASSIFICATION_CATEGORY = X_CLASSIFICATION_CATEGORY)
267 OR ((recinfo.CLASSIFICATION_CATEGORY is null) AND (X_CLASSIFICATION_CATEGORY is null)))
268 AND ((recinfo.REASON_CATEGORY = X_REASON_CATEGORY)
269 OR ((recinfo.REASON_CATEGORY is null) AND (X_REASON_CATEGORY is null)))
270 AND ((recinfo.RESOLUTION_CATEGORY = X_RESOLUTION_CATEGORY)
271 OR ((recinfo.RESOLUTION_CATEGORY is null) AND (X_RESOLUTION_CATEGORY is null)))
272 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
273 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
274 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
275 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
276 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
277 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
278 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
279 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
280 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
281 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
282 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
283 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
284 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
285 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
286 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
287 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
288 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
289 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
290 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
291 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
292 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
293 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
294 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
295 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
296 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
297 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
298 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
299 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
300 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
301 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
302 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
303 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
304 ) then
305 null;
306 else
307 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
308 app_exception.raise_exception;
309 end if;
310
311 for tlinfo in c1 loop
312 if (tlinfo.BASELANG = 'Y') then
313 if ( (tlinfo.NAME = X_NAME)
314 AND (tlinfo.SHORT_NAME = X_SHORT_NAME)
315 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
316 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
317 ) then
318 null;
319 else
320 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
321 app_exception.raise_exception;
322 end if;
323 end if;
324 end loop;
325 return;
326 end LOCK_ROW;
327
328 procedure UPDATE_ROW (
329 X_CI_TYPE_ID in NUMBER,
330 X_CI_TYPE_CLASS_CODE in VARCHAR2,
331 X_AUTO_NUMBER_FLAG in VARCHAR2,
332 X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
333 X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
334 X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
335 X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
336 X_RECORD_VERSION_NUMBER in NUMBER,
337 X_START_DATE_ACTIVE in DATE,
338 X_END_DATE_ACTIVE in DATE,
339 X_CLASSIFICATION_CATEGORY in VARCHAR2,
340 X_REASON_CATEGORY in VARCHAR2,
341 X_RESOLUTION_CATEGORY in VARCHAR2,
342 X_ATTRIBUTE_CATEGORY in VARCHAR2,
343 X_ATTRIBUTE1 in VARCHAR2,
344 X_ATTRIBUTE2 in VARCHAR2,
345 X_ATTRIBUTE3 in VARCHAR2,
346 X_ATTRIBUTE4 in VARCHAR2,
347 X_ATTRIBUTE5 in VARCHAR2,
348 X_ATTRIBUTE6 in VARCHAR2,
349 X_ATTRIBUTE7 in VARCHAR2,
350 X_ATTRIBUTE8 in VARCHAR2,
351 X_ATTRIBUTE9 in VARCHAR2,
352 X_ATTRIBUTE10 in VARCHAR2,
353 X_ATTRIBUTE11 in VARCHAR2,
354 X_ATTRIBUTE12 in VARCHAR2,
355 X_ATTRIBUTE13 in VARCHAR2,
356 X_ATTRIBUTE14 in VARCHAR2,
357 X_ATTRIBUTE15 in VARCHAR2,
358 X_NAME in VARCHAR2,
359 X_SHORT_NAME in VARCHAR2,
360 X_DESCRIPTION in VARCHAR2,
361 X_LAST_UPDATE_DATE in DATE,
362 X_LAST_UPDATED_BY in NUMBER,
363 X_LAST_UPDATE_LOGIN in NUMBER
364 ) is
365 begin
366 update PA_CI_TYPES_B set
367 CI_TYPE_CLASS_CODE = X_CI_TYPE_CLASS_CODE,
368 AUTO_NUMBER_FLAG = X_AUTO_NUMBER_FLAG,
369 RESOLUTION_REQUIRED_FLAG = X_RESOLUTION_REQUIRED_FLAG,
370 APPROVAL_REQUIRED_FLAG = X_APPROVAL_REQUIRED_FLAG,
371 SOURCE_ATTRS_ENABLED_FLAG = X_SOURCE_ATTRS_ENABLED_FLAG,
372 ALLOW_ALL_USAGE_FLAG = X_ALLOW_ALL_USAGE_FLAG,
373 RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER,
374 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
375 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
376 CLASSIFICATION_CATEGORY = X_CLASSIFICATION_CATEGORY,
377 REASON_CATEGORY = X_REASON_CATEGORY,
378 RESOLUTION_CATEGORY = X_RESOLUTION_CATEGORY,
379 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
380 ATTRIBUTE1 = X_ATTRIBUTE1,
381 ATTRIBUTE2 = X_ATTRIBUTE2,
382 ATTRIBUTE3 = X_ATTRIBUTE3,
383 ATTRIBUTE4 = X_ATTRIBUTE4,
384 ATTRIBUTE5 = X_ATTRIBUTE5,
385 ATTRIBUTE6 = X_ATTRIBUTE6,
386 ATTRIBUTE7 = X_ATTRIBUTE7,
387 ATTRIBUTE8 = X_ATTRIBUTE8,
388 ATTRIBUTE9 = X_ATTRIBUTE9,
389 ATTRIBUTE10 = X_ATTRIBUTE10,
390 ATTRIBUTE11 = X_ATTRIBUTE11,
391 ATTRIBUTE12 = X_ATTRIBUTE12,
392 ATTRIBUTE13 = X_ATTRIBUTE13,
393 ATTRIBUTE14 = X_ATTRIBUTE14,
394 ATTRIBUTE15 = X_ATTRIBUTE15,
395 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
396 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
397 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
398 where CI_TYPE_ID = X_CI_TYPE_ID;
399
400 if (sql%notfound) then
401 raise no_data_found;
402 end if;
403
404 update PA_CI_TYPES_TL set
405 NAME = X_NAME,
406 SHORT_NAME = X_SHORT_NAME,
407 DESCRIPTION = X_DESCRIPTION,
408 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
409 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
410 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
411 SOURCE_LANG = userenv('LANG')
412 where CI_TYPE_ID = X_CI_TYPE_ID
413 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
414
415 if (sql%notfound) then
416 raise no_data_found;
417 end if;
418 end UPDATE_ROW;
419
420 procedure DELETE_ROW (
421 X_CI_TYPE_ID in NUMBER
422 ) is
423 begin
424 delete from PA_CI_TYPES_TL
425 where CI_TYPE_ID = X_CI_TYPE_ID;
426
427 if (sql%notfound) then
428 raise no_data_found;
429 end if;
430
431 delete from PA_CI_TYPES_B
432 where CI_TYPE_ID = X_CI_TYPE_ID;
433
434 if (sql%notfound) then
435 raise no_data_found;
436 end if;
437 end DELETE_ROW;
438
439 procedure ADD_LANGUAGE
440 is
441 begin
442 delete from PA_CI_TYPES_TL T
443 where not exists
444 (select NULL
445 from PA_CI_TYPES_B B
446 where B.CI_TYPE_ID = T.CI_TYPE_ID
447 );
448
449 update PA_CI_TYPES_TL T set (
450 NAME,
451 SHORT_NAME,
452 DESCRIPTION
453 ) = (select
454 B.NAME,
455 B.SHORT_NAME,
456 B.DESCRIPTION
457 from PA_CI_TYPES_TL B
458 where B.CI_TYPE_ID = T.CI_TYPE_ID
459 and B.LANGUAGE = T.SOURCE_LANG)
460 where (
461 T.CI_TYPE_ID,
462 T.LANGUAGE
463 ) in (select
464 SUBT.CI_TYPE_ID,
465 SUBT.LANGUAGE
466 from PA_CI_TYPES_TL SUBB, PA_CI_TYPES_TL SUBT
467 where SUBB.CI_TYPE_ID = SUBT.CI_TYPE_ID
468 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
469 and (SUBB.NAME <> SUBT.NAME
470 or SUBB.SHORT_NAME <> SUBT.SHORT_NAME
471 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
472 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
473 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
474 ));
475
476 insert into PA_CI_TYPES_TL (
477 CI_TYPE_ID,
478 NAME,
479 SHORT_NAME,
480 LAST_UPDATED_BY,
481 CREATED_BY,
482 CREATION_DATE,
483 LAST_UPDATE_DATE,
484 LAST_UPDATE_LOGIN,
485 DESCRIPTION,
486 LANGUAGE,
487 SOURCE_LANG
488 ) select
489 B.CI_TYPE_ID,
490 B.NAME,
491 B.SHORT_NAME,
492 B.LAST_UPDATED_BY,
493 B.CREATED_BY,
494 B.CREATION_DATE,
495 B.LAST_UPDATE_DATE,
496 B.LAST_UPDATE_LOGIN,
497 B.DESCRIPTION,
498 L.LANGUAGE_CODE,
499 B.SOURCE_LANG
500 from PA_CI_TYPES_TL B, FND_LANGUAGES L
501 where L.INSTALLED_FLAG in ('I', 'B')
502 and B.LANGUAGE = userenv('LANG')
503 and not exists
504 (select NULL
505 from PA_CI_TYPES_TL T
506 where T.CI_TYPE_ID = B.CI_TYPE_ID
507 and T.LANGUAGE = L.LANGUAGE_CODE);
508 end ADD_LANGUAGE;
509
510 end PA_CI_TYPES_PKG;