[Home] [Help]
PACKAGE BODY: APPS.CSD_SERVICE_CODES_PKG
Source
1 package body CSD_SERVICE_CODES_PKG as
2 /* $Header: csdtcscb.pls 115.4 2003/11/05 00:00:52 gilam noship $ */
3
4 procedure INSERT_ROW (
5 PX_ROWID in out nocopy VARCHAR2,
6 PX_SERVICE_CODE_ID in out nocopy NUMBER,
7 P_OBJECT_VERSION_NUMBER in NUMBER,
8 P_CREATED_BY in NUMBER,
9 P_CREATION_DATE in DATE,
10 P_LAST_UPDATED_BY in NUMBER,
11 P_LAST_UPDATE_DATE in DATE,
12 P_LAST_UPDATE_LOGIN in NUMBER,
13 P_SERVICE_CODE in VARCHAR2,
14 P_NAME in VARCHAR2,
15 P_DESCRIPTION in VARCHAR2,
16 P_ACTIVE_FROM in DATE,
17 P_ACTIVE_TO in DATE,
18 P_ATTRIBUTE_CATEGORY in VARCHAR2,
19 P_ATTRIBUTE1 in VARCHAR2,
20 P_ATTRIBUTE2 in VARCHAR2,
21 P_ATTRIBUTE3 in VARCHAR2,
22 P_ATTRIBUTE4 in VARCHAR2,
23 P_ATTRIBUTE5 in VARCHAR2,
24 P_ATTRIBUTE6 in VARCHAR2,
25 P_ATTRIBUTE7 in VARCHAR2,
26 P_ATTRIBUTE8 in VARCHAR2,
27 P_ATTRIBUTE9 in VARCHAR2,
28 P_ATTRIBUTE10 in VARCHAR2,
29 P_ATTRIBUTE11 in VARCHAR2,
30 P_ATTRIBUTE12 in VARCHAR2,
31 P_ATTRIBUTE13 in VARCHAR2,
32 P_ATTRIBUTE14 in VARCHAR2,
33 P_ATTRIBUTE15 in VARCHAR2
34 ) is
35 cursor C is select ROWID from CSD_SERVICE_CODES_B
36 where SERVICE_CODE_ID = PX_SERVICE_CODE_ID;
37
38 begin
39
40
41 select CSD_SERVICE_CODES_S1.nextval
42 into PX_SERVICE_CODE_ID
43 from dual;
44
45
46 insert into CSD_SERVICE_CODES_B (
47 SERVICE_CODE_ID,
48 OBJECT_VERSION_NUMBER,
49 CREATED_BY,
50 CREATION_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATE_LOGIN,
54 SERVICE_CODE,
55 ACTIVE_FROM,
56 ACTIVE_TO,
57 ATTRIBUTE_CATEGORY,
58 ATTRIBUTE1,
59 ATTRIBUTE2,
60 ATTRIBUTE3,
61 ATTRIBUTE4,
62 ATTRIBUTE5,
63 ATTRIBUTE6,
64 ATTRIBUTE7,
65 ATTRIBUTE8,
66 ATTRIBUTE9,
67 ATTRIBUTE10,
68 ATTRIBUTE11,
69 ATTRIBUTE12,
70 ATTRIBUTE13,
71 ATTRIBUTE14,
72 ATTRIBUTE15
73 ) values (
74 PX_SERVICE_CODE_ID,
75 P_OBJECT_VERSION_NUMBER,
76 P_CREATED_BY,
77 P_CREATION_DATE,
78 P_LAST_UPDATED_BY,
79 P_LAST_UPDATE_DATE,
80 P_LAST_UPDATE_LOGIN,
81 UPPER(P_SERVICE_CODE),
82 P_ACTIVE_FROM,
83 P_ACTIVE_TO,
84 P_ATTRIBUTE_CATEGORY,
85 P_ATTRIBUTE1,
86 P_ATTRIBUTE2,
87 P_ATTRIBUTE3,
88 P_ATTRIBUTE4,
89 P_ATTRIBUTE5,
90 P_ATTRIBUTE6,
91 P_ATTRIBUTE7,
92 P_ATTRIBUTE8,
93 P_ATTRIBUTE9,
94 P_ATTRIBUTE10,
95 P_ATTRIBUTE11,
96 P_ATTRIBUTE12,
97 P_ATTRIBUTE13,
98 P_ATTRIBUTE14,
99 P_ATTRIBUTE15 );
100
101 insert into CSD_SERVICE_CODES_TL (
102 SERVICE_CODE_ID,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 NAME,
109 DESCRIPTION,
110 LANGUAGE,
111 SOURCE_LANG
112 ) select
113 PX_SERVICE_CODE_ID,
114 P_CREATED_BY,
115 P_CREATION_DATE,
116 P_LAST_UPDATED_BY,
117 P_LAST_UPDATE_DATE,
118 P_LAST_UPDATE_LOGIN,
119 P_NAME,
120 P_DESCRIPTION,
121 L.LANGUAGE_CODE,
122 userenv('LANG')
123 from FND_LANGUAGES L
124 where L.INSTALLED_FLAG in ('I', 'B')
125 and not exists
126 (select NULL
127 from CSD_SERVICE_CODES_TL T
128 where T.SERVICE_CODE_ID = PX_SERVICE_CODE_ID
129 and T.LANGUAGE = L.LANGUAGE_CODE);
130
131 open c;
132 fetch c into PX_ROWID;
133 if (c%notfound) then
134 close c;
135 raise no_data_found;
136 end if;
137 close c;
138
139 end INSERT_ROW;
140
141 procedure LOCK_ROW (
142 PX_ROWID in out nocopy VARCHAR2,
143 P_SERVICE_CODE_ID in NUMBER,
144 P_OBJECT_VERSION_NUMBER in NUMBER
145
146 --commented out the rest of the record
147 /*,
148 P_SERVICE_CODE in VARCHAR2,
149 P_NAME in VARCHAR2,
150 P_DESCRIPTION in VARCHAR2,
151 P_ACTIVE_FROM in DATE,
152 P_ACTIVE_TO in DATE,
153 P_ATTRIBUTE_CATEGORY in VARCHAR2,
154 P_ATTRIBUTE1 in VARCHAR2,
155 P_ATTRIBUTE2 in VARCHAR2,
156 P_ATTRIBUTE3 in VARCHAR2,
157 P_ATTRIBUTE4 in VARCHAR2,
158 P_ATTRIBUTE5 in VARCHAR2,
159 P_ATTRIBUTE6 in VARCHAR2,
160 P_ATTRIBUTE7 in VARCHAR2,
161 P_ATTRIBUTE8 in VARCHAR2,
162 P_ATTRIBUTE9 in VARCHAR2,
163 P_ATTRIBUTE10 in VARCHAR2,
164 P_ATTRIBUTE11 in VARCHAR2,
165 P_ATTRIBUTE12 in VARCHAR2,
166 P_ATTRIBUTE13 in VARCHAR2,
167 P_ATTRIBUTE14 in VARCHAR2,
168 P_ATTRIBUTE15 in VARCHAR2
169 */
170 --
171 ) is
172 cursor c is select
173 SERVICE_CODE_ID,
174 OBJECT_VERSION_NUMBER
175
176 --commented out the rest of the fields
177 /*,
178 SERVICE_CODE,
179 ACTIVE_FROM,
180 ACTIVE_TO,
181 ATTRIBUTE_CATEGORY,
182 ATTRIBUTE1,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE4,
186 ATTRIBUTE5,
187 ATTRIBUTE6,
188 ATTRIBUTE7,
189 ATTRIBUTE8,
190 ATTRIBUTE9,
191 ATTRIBUTE10,
192 ATTRIBUTE11,
193 ATTRIBUTE12,
194 ATTRIBUTE13,
195 ATTRIBUTE14,
196 ATTRIBUTE15
197 */
198 --
199 from CSD_SERVICE_CODES_B
200 where SERVICE_CODE_ID = P_SERVICE_CODE_ID
201 for update of SERVICE_CODE_ID nowait;
202 recinfo c%rowtype;
203
204 --commented out the cursor for TL table
205 /*
206 cursor c1 is select
207 NAME,
208 DESCRIPTION,
209 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
210 from CSD_SERVICE_CODES_TL
211 where SERVICE_CODE_ID = P_SERVICE_CODE_ID
212 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
213 for update of SERVICE_CODE_ID nowait;
214 */
215 --
216 begin
217 open c;
218 fetch c into recinfo;
219 if (c%notfound) then
220 close c;
221 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
222 app_exception.raise_exception;
223 end if;
224 close c;
225 if (
226 (recinfo.SERVICE_CODE_ID = P_SERVICE_CODE_ID)
227 AND ((recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
228 OR (recinfo.OBJECT_VERSION_NUMBER IS NULL ))
229
230 --commented out the rest of the record
231 /*
232 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
233 OR ((recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
234 AND ((recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
235 OR ((recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
236 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
237 OR ((recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
238 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
239 OR ((recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
240 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
241 OR ((recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
242 AND ((recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
243 OR ((recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
244 AND (recinfo.SERVICE_CODE = P_SERVICE_CODE)
245
246 AND ((recinfo.ACTIVE_FROM = P_ACTIVE_FROM)
247 OR ((recinfo.ACTIVE_FROM is null) AND (P_ACTIVE_FROM = FND_API.G_MISS_DATE))
248 OR (P_ACTIVE_FROM is null))
249
250 AND ((recinfo.ACTIVE_TO = P_ACTIVE_TO)
251 OR ((recinfo.ACTIVE_TO is null) AND (P_ACTIVE_TO = FND_API.G_MISS_DATE))
252 OR (P_ACTIVE_TO is null))
253
254 AND ((recinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
255 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_ATTRIBUTE_CATEGORY is null)))
256 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
257 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
258 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
259 OR ((recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
260 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
261 OR ((recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
262 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
263 OR ((recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
264 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
265 OR ((recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
266 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
267 OR ((recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
268 AND ((recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
269 OR ((recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
270 AND ((recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
271 OR ((recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
272 AND ((recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
273 OR ((recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
274 */
275 --
276 ) then
277 null;
278 else
279 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
280 app_exception.raise_exception;
281 end if;
282
283 --commented out the rest of the record from TL
284 /*
285 for tlinfo in c1 loop
286 if (tlinfo.BASELANG = 'Y') then
287 if ( (tlinfo.NAME = P_NAME)
288 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
289 OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION = FND_API.G_MISS_CHAR))
290 OR (P_DESCRIPTION is null))
291 ) then
292 null;
293 else
294 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
295 app_exception.raise_exception;
296 end if;
297 end if;
298 end loop;
299 */
300 --
301 return;
302 end LOCK_ROW;
303
304 procedure UPDATE_ROW (
305 P_SERVICE_CODE_ID in NUMBER,
306 P_OBJECT_VERSION_NUMBER in NUMBER,
307 P_CREATED_BY in NUMBER,
308 P_CREATION_DATE in DATE,
309 P_LAST_UPDATED_BY in NUMBER,
310 P_LAST_UPDATE_DATE in DATE,
311 P_LAST_UPDATE_LOGIN in NUMBER,
312 P_SERVICE_CODE in VARCHAR2,
313 P_NAME in VARCHAR2,
314 P_DESCRIPTION in VARCHAR2,
315 P_ACTIVE_FROM in DATE,
316 P_ACTIVE_TO in DATE,
317 P_ATTRIBUTE_CATEGORY in VARCHAR2,
318 P_ATTRIBUTE1 in VARCHAR2,
319 P_ATTRIBUTE2 in VARCHAR2,
320 P_ATTRIBUTE3 in VARCHAR2,
321 P_ATTRIBUTE4 in VARCHAR2,
322 P_ATTRIBUTE5 in VARCHAR2,
323 P_ATTRIBUTE6 in VARCHAR2,
324 P_ATTRIBUTE7 in VARCHAR2,
325 P_ATTRIBUTE8 in VARCHAR2,
326 P_ATTRIBUTE9 in VARCHAR2,
327 P_ATTRIBUTE10 in VARCHAR2,
328 P_ATTRIBUTE11 in VARCHAR2,
329 P_ATTRIBUTE12 in VARCHAR2,
330 P_ATTRIBUTE13 in VARCHAR2,
331 P_ATTRIBUTE14 in VARCHAR2,
332 P_ATTRIBUTE15 in VARCHAR2
333 ) is
334 begin
335 update CSD_SERVICE_CODES_B set
336 OBJECT_VERSION_NUMBER = decode( P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, P_OBJECT_VERSION_NUMBER)
337 ,CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
338 ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
339 ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
340 ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
341 ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
342 ,SERVICE_CODE = decode( P_SERVICE_CODE, FND_API.G_MISS_CHAR, NULL, NULL, SERVICE_CODE, P_SERVICE_CODE)
343 ,ACTIVE_FROM = decode( P_ACTIVE_FROM, FND_API.G_MISS_DATE, NULL, NULL, ACTIVE_FROM, P_ACTIVE_FROM)
344 ,ACTIVE_TO = decode( P_ACTIVE_TO, FND_API.G_MISS_DATE, NULL, NULL, ACTIVE_TO, P_ACTIVE_TO)
345 ,ATTRIBUTE_CATEGORY = decode( P_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY)
346 ,ATTRIBUTE1 = decode( P_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, P_ATTRIBUTE1)
347 ,ATTRIBUTE2 = decode( P_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, P_ATTRIBUTE2)
348 ,ATTRIBUTE3 = decode( P_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, P_ATTRIBUTE3)
349 ,ATTRIBUTE4 = decode( P_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, P_ATTRIBUTE4)
350 ,ATTRIBUTE5 = decode( P_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, P_ATTRIBUTE5)
351 ,ATTRIBUTE6 = decode( P_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, P_ATTRIBUTE6)
352 ,ATTRIBUTE7 = decode( P_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, P_ATTRIBUTE7)
353 ,ATTRIBUTE8 = decode( P_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, P_ATTRIBUTE8)
354 ,ATTRIBUTE9 = decode( P_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, P_ATTRIBUTE9)
355 ,ATTRIBUTE10 = decode( P_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, P_ATTRIBUTE10)
356 ,ATTRIBUTE11 = decode( P_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, P_ATTRIBUTE11)
357 ,ATTRIBUTE12 = decode( P_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, P_ATTRIBUTE12)
358 ,ATTRIBUTE13 = decode( P_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, P_ATTRIBUTE13)
359 ,ATTRIBUTE14 = decode( P_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, P_ATTRIBUTE14)
360 ,ATTRIBUTE15 = decode( P_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, P_ATTRIBUTE15)
361 where SERVICE_CODE_ID = P_SERVICE_CODE_ID;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 update CSD_SERVICE_CODES_TL set
368 CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
369 ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
370 ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
371 ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
372 ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
373 ,NAME = decode( P_NAME, FND_API.G_MISS_CHAR, NULL, NULL, NAME, P_NAME)
374 ,DESCRIPTION = decode( P_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, NULL, DESCRIPTION, P_DESCRIPTION)
375 ,SOURCE_LANG = userenv('LANG')
376 where SERVICE_CODE_ID = P_SERVICE_CODE_ID
377 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
378
379 if (sql%notfound) then
380 raise no_data_found;
381 end if;
382 end UPDATE_ROW;
383
384 procedure DELETE_ROW (
385 P_SERVICE_CODE_ID in NUMBER
386 ) is
387 begin
388 delete from CSD_SERVICE_CODES_TL
389 where SERVICE_CODE_ID = P_SERVICE_CODE_ID;
390
391 if (sql%notfound) then
392 raise no_data_found;
393 end if;
394
395 delete from CSD_SERVICE_CODES_B
396 where SERVICE_CODE_ID = P_SERVICE_CODE_ID;
397
398 if (sql%notfound) then
399 raise no_data_found;
400 end if;
401 end DELETE_ROW;
402
403 procedure ADD_LANGUAGE
404 is
405 begin
406 delete from CSD_SERVICE_CODES_TL T
407 where not exists
408 (select NULL
409 from CSD_SERVICE_CODES_B B
410 where B.SERVICE_CODE_ID = T.SERVICE_CODE_ID
411 );
412
413 update CSD_SERVICE_CODES_TL T set (
414 NAME,
415 DESCRIPTION
416 ) = (select
417 B.NAME,
418 B.DESCRIPTION
419 from CSD_SERVICE_CODES_TL B
420 where B.SERVICE_CODE_ID = T.SERVICE_CODE_ID
421 and B.LANGUAGE = T.SOURCE_LANG)
422 where (
423 T.SERVICE_CODE_ID,
424 T.LANGUAGE
425 ) in (select
426 SUBT.SERVICE_CODE_ID,
427 SUBT.LANGUAGE
428 from CSD_SERVICE_CODES_TL SUBB, CSD_SERVICE_CODES_TL SUBT
429 where SUBB.SERVICE_CODE_ID = SUBT.SERVICE_CODE_ID
430 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
431 and (SUBB.NAME <> SUBT.NAME
432 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
433 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
434 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
435 ));
436
437 insert into CSD_SERVICE_CODES_TL (
438 SERVICE_CODE_ID,
439 CREATED_BY,
440 CREATION_DATE,
441 LAST_UPDATED_BY,
442 LAST_UPDATE_DATE,
443 LAST_UPDATE_LOGIN,
444 NAME,
445 DESCRIPTION,
446 LANGUAGE,
447 SOURCE_LANG
448 ) select /*+ ORDERED */
449 B.SERVICE_CODE_ID,
450 B.CREATED_BY,
451 B.CREATION_DATE,
452 B.LAST_UPDATED_BY,
453 B.LAST_UPDATE_DATE,
454 B.LAST_UPDATE_LOGIN,
455 B.NAME,
456 B.DESCRIPTION,
457 L.LANGUAGE_CODE,
458 B.SOURCE_LANG
459 from CSD_SERVICE_CODES_TL B, FND_LANGUAGES L
460 where L.INSTALLED_FLAG in ('I', 'B')
461 and B.LANGUAGE = userenv('LANG')
462 and not exists
463 (select NULL
464 from CSD_SERVICE_CODES_TL T
465 where T.SERVICE_CODE_ID = B.SERVICE_CODE_ID
466 and T.LANGUAGE = L.LANGUAGE_CODE);
467 end ADD_LANGUAGE;
468
469 end CSD_SERVICE_CODES_PKG;