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_DIAGNOSTIC_CODES_PKG

Source


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