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