eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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)
375          ,SOURCE_LANG = userenv('LANG')
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)
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;