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.MTL_ITEM_TEMPLATES_PKG

Source


1 PACKAGE BODY MTL_ITEM_TEMPLATES_PKG AS
2 /* $Header: INVVTEMB.pls 120.1.12010000.5 2008/11/17 23:17:00 akbharga ship $ */
3 
4 PROCEDURE INSERT_ROW(P_Item_Templates_Rec IN  MTL_ITEM_TEMPLATES_B%ROWTYPE,
5                      X_ROWID              OUT NOCOPY ROWID) IS
6 BEGIN
7 
8    INSERT INTO MTL_ITEM_TEMPLATES_B (
9       TEMPLATE_ID,
10       TEMPLATE_NAME,
11       DESCRIPTION,
12       LAST_UPDATE_DATE,
13       LAST_UPDATED_BY,
14       CREATION_DATE,
15       CREATED_BY,
16       LAST_UPDATE_LOGIN,
17       ATTRIBUTE_CATEGORY,
18       ATTRIBUTE1,
19       ATTRIBUTE2,
20       ATTRIBUTE3,
21       ATTRIBUTE4,
22       ATTRIBUTE5,
23       ATTRIBUTE6,
24       ATTRIBUTE7,
25       ATTRIBUTE8,
26       ATTRIBUTE9,
27       ATTRIBUTE10,
28       ATTRIBUTE11,
29       ATTRIBUTE12,
30       ATTRIBUTE13,
31       ATTRIBUTE14,
32       ATTRIBUTE15,
33       REQUEST_ID,
34       PROGRAM_APPLICATION_ID,
35       PROGRAM_ID,
36       PROGRAM_UPDATE_DATE,
37       CONTEXT_ORGANIZATION_ID,
38       GLOBAL_ATTRIBUTE_CATEGORY,
39       GLOBAL_ATTRIBUTE1,
40       GLOBAL_ATTRIBUTE2,
41       GLOBAL_ATTRIBUTE3,
42       GLOBAL_ATTRIBUTE4,
43       GLOBAL_ATTRIBUTE5,
44       GLOBAL_ATTRIBUTE6,
45       GLOBAL_ATTRIBUTE7,
46       GLOBAL_ATTRIBUTE8,
47       GLOBAL_ATTRIBUTE9,
48       GLOBAL_ATTRIBUTE10,
49       GLOBAL_ATTRIBUTE11,
50       GLOBAL_ATTRIBUTE12,
51       GLOBAL_ATTRIBUTE13,
52       GLOBAL_ATTRIBUTE14,
53       GLOBAL_ATTRIBUTE15,
54       GLOBAL_ATTRIBUTE16,
55       GLOBAL_ATTRIBUTE17,
56       GLOBAL_ATTRIBUTE18,
57       GLOBAL_ATTRIBUTE19,
58       GLOBAL_ATTRIBUTE20
59 )
60    VALUES (
61       P_Item_Templates_Rec.TEMPLATE_ID,
62       P_Item_Templates_Rec.TEMPLATE_NAME,
63       P_Item_Templates_Rec.DESCRIPTION,
64       P_Item_Templates_Rec.LAST_UPDATE_DATE,
65       P_Item_Templates_Rec.LAST_UPDATED_BY,
66       P_Item_Templates_Rec.CREATION_DATE,
67       P_Item_Templates_Rec.CREATED_BY,
68       P_Item_Templates_Rec.LAST_UPDATE_LOGIN,
69       P_Item_Templates_Rec.ATTRIBUTE_CATEGORY,
70       P_Item_Templates_Rec.ATTRIBUTE1,
71       P_Item_Templates_Rec.ATTRIBUTE2,
72       P_Item_Templates_Rec.ATTRIBUTE3,
73       P_Item_Templates_Rec.ATTRIBUTE4,
74       P_Item_Templates_Rec.ATTRIBUTE5,
75       P_Item_Templates_Rec.ATTRIBUTE6,
76       P_Item_Templates_Rec.ATTRIBUTE7,
77       P_Item_Templates_Rec.ATTRIBUTE8,
78       P_Item_Templates_Rec.ATTRIBUTE9,
79       P_Item_Templates_Rec.ATTRIBUTE10,
80       P_Item_Templates_Rec.ATTRIBUTE11,
81       P_Item_Templates_Rec.ATTRIBUTE12,
82       P_Item_Templates_Rec.ATTRIBUTE13,
83       P_Item_Templates_Rec.ATTRIBUTE14,
84       P_Item_Templates_Rec.ATTRIBUTE15,
85       P_Item_Templates_Rec.REQUEST_ID,
86       P_Item_Templates_Rec.PROGRAM_APPLICATION_ID,
87       P_Item_Templates_Rec.PROGRAM_ID,
88       P_Item_Templates_Rec.PROGRAM_UPDATE_DATE,
89       P_Item_Templates_Rec.CONTEXT_ORGANIZATION_ID,
90       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE_CATEGORY,
91       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE1,
92       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE2,
93       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE3,
94       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE4,
95       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE5,
96       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE6,
97       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE7,
98       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE8,
99       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE9,
100       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE10,
101       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE11,
102       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE12,
103       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE13,
104       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE14,
105       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE15,
106       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE16,
107       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE17,
108       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE18,
109       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE19,
110       P_Item_Templates_Rec.GLOBAL_ATTRIBUTE20
111 )
112    RETURNING ROWID INTO X_ROWID;
113 
114    INSERT INTO MTL_ITEM_TEMPLATES_TL (
115       TEMPLATE_ID,
116       TEMPLATE_NAME,
117       DESCRIPTION,
118       CREATION_DATE,
119       CREATED_BY,
120       LAST_UPDATE_DATE,
121       LAST_UPDATED_BY,
122       LAST_UPDATE_LOGIN,
123       LANGUAGE,
124       SOURCE_LANG)
125    SELECT   P_Item_Templates_Rec.TEMPLATE_ID,
126             P_Item_Templates_Rec.TEMPLATE_NAME,
127             P_Item_Templates_Rec.DESCRIPTION,
128             P_Item_Templates_Rec.CREATION_DATE,
129             P_Item_Templates_Rec.CREATED_BY,
130             P_Item_Templates_Rec.LAST_UPDATE_DATE,
131             P_Item_Templates_Rec.LAST_UPDATED_BY,
132             P_Item_Templates_Rec.LAST_UPDATE_LOGIN,
133             L.LANGUAGE_CODE,
134             USERENV('LANG')
135    FROM FND_LANGUAGES L
136    WHERE L.INSTALLED_FLAG in ('I', 'B')
137    AND NOT EXISTS (SELECT NULL
138                    FROM MTL_ITEM_TEMPLATES_TL T
139                    WHERE T.TEMPLATE_ID = P_Item_Templates_Rec.TEMPLATE_ID
140                    AND   T.LANGUAGE    = L.LANGUAGE_CODE);
141 END INSERT_ROW;
142 
143 PROCEDURE LOCK_ROW (P_Item_Templates_Rec IN  MTL_ITEM_TEMPLATES_B%ROWTYPE) IS
144 
145    CURSOR c_get_item_templates IS
146    SELECT
147         TEMPLATE_ID,
148         ATTRIBUTE_CATEGORY,
149         ATTRIBUTE1,
150         ATTRIBUTE2,
151         ATTRIBUTE3,
152         ATTRIBUTE4,
153         ATTRIBUTE5,
154         ATTRIBUTE6,
155         ATTRIBUTE7,
156         ATTRIBUTE8,
157         ATTRIBUTE9,
158         ATTRIBUTE10,
159         ATTRIBUTE11,
160         ATTRIBUTE12,
161         ATTRIBUTE13,
162         ATTRIBUTE14,
163         ATTRIBUTE15,
164         CONTEXT_ORGANIZATION_ID
165    FROM MTL_ITEM_TEMPLATES_B
166    WHERE TEMPLATE_ID = P_Item_Templates_Rec.TEMPLATE_ID;
167 
168    CURSOR c_get_templates_trans IS
169    SELECT
170        TEMPLATE_NAME,
171        DESCRIPTION,
172        DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
173    FROM MTL_ITEM_TEMPLATES_TL
174    WHERE TEMPLATE_ID = P_Item_Templates_Rec.TEMPLATE_ID
175    AND   USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
176 
177    recinfo c_get_item_templates%rowtype;
178 
179 BEGIN
180 
181    OPEN  c_get_item_templates;
182    FETCH c_get_item_templates INTO recinfo;
183    IF (c_get_item_templates%notfound) THEN
184       CLOSE c_get_item_templates;
185       fnd_message.set_name('FND','FORM_RECORD_DELETED');
186       Raise FND_API.g_EXC_UNEXPECTED_ERROR;
187    END IF;
188    CLOSE c_get_item_templates;
189 
190    IF ((recinfo.TEMPLATE_ID = P_Item_Templates_Rec.Template_Id)
191       AND ((recinfo.CONTEXT_ORGANIZATION_ID = P_Item_Templates_Rec.CONTEXT_ORGANIZATION_ID)
192            OR ((recinfo.CONTEXT_ORGANIZATION_ID is null) AND (P_Item_Templates_Rec.CONTEXT_ORGANIZATION_ID is null)))
193       AND ((recinfo.ATTRIBUTE_CATEGORY = P_Item_Templates_Rec.ATTRIBUTE_CATEGORY)
194            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_Item_Templates_Rec.ATTRIBUTE_CATEGORY is null)))
195       AND ((recinfo.ATTRIBUTE1 = P_Item_Templates_Rec.ATTRIBUTE1)
196            OR ((recinfo.ATTRIBUTE1 is null) AND (P_Item_Templates_Rec.ATTRIBUTE1 is null)))
197       AND ((recinfo.ATTRIBUTE2 = P_Item_Templates_Rec.ATTRIBUTE2)
198            OR ((recinfo.ATTRIBUTE2 is null) AND (P_Item_Templates_Rec.ATTRIBUTE2 is null)))
199       AND ((recinfo.ATTRIBUTE3 = P_Item_Templates_Rec.ATTRIBUTE3)
200            OR ((recinfo.ATTRIBUTE3 is null) AND (P_Item_Templates_Rec.ATTRIBUTE3 is null)))
201       AND ((recinfo.ATTRIBUTE4 = P_Item_Templates_Rec.ATTRIBUTE4)
202            OR ((recinfo.ATTRIBUTE4 is null) AND (P_Item_Templates_Rec.ATTRIBUTE4 is null)))
203       AND ((recinfo.ATTRIBUTE5 = P_Item_Templates_Rec.ATTRIBUTE5)
204            OR ((recinfo.ATTRIBUTE5 is null) AND (P_Item_Templates_Rec.ATTRIBUTE5 is null)))
205       AND ((recinfo.ATTRIBUTE6 = P_Item_Templates_Rec.ATTRIBUTE6)
206            OR ((recinfo.ATTRIBUTE6 is null) AND (P_Item_Templates_Rec.ATTRIBUTE6 is null)))
207       AND ((recinfo.ATTRIBUTE7 = P_Item_Templates_Rec.ATTRIBUTE7)
208            OR ((recinfo.ATTRIBUTE7 is null) AND (P_Item_Templates_Rec.ATTRIBUTE7 is null)))
209       AND ((recinfo.ATTRIBUTE8 = P_Item_Templates_Rec.ATTRIBUTE8)
210            OR ((recinfo.ATTRIBUTE8 is null) AND (P_Item_Templates_Rec.ATTRIBUTE8 is null)))
211       AND ((recinfo.ATTRIBUTE9 = P_Item_Templates_Rec.ATTRIBUTE9)
212            OR ((recinfo.ATTRIBUTE9 is null) AND (P_Item_Templates_Rec.ATTRIBUTE9 is null)))
213       AND ((recinfo.ATTRIBUTE10 = P_Item_Templates_Rec.ATTRIBUTE10)
214            OR ((recinfo.ATTRIBUTE10 is null) AND (P_Item_Templates_Rec.ATTRIBUTE10 is null)))
215       AND ((recinfo.ATTRIBUTE11 = P_Item_Templates_Rec.ATTRIBUTE11)
216            OR ((recinfo.ATTRIBUTE11 is null) AND (P_Item_Templates_Rec.ATTRIBUTE11 is null)))
217       AND ((recinfo.ATTRIBUTE12 = P_Item_Templates_Rec.ATTRIBUTE12)
218            OR ((recinfo.ATTRIBUTE12 is null) AND (P_Item_Templates_Rec.ATTRIBUTE12 is null)))
219       AND ((recinfo.ATTRIBUTE13 = P_Item_Templates_Rec.ATTRIBUTE13)
220            OR ((recinfo.ATTRIBUTE13 is null) AND (P_Item_Templates_Rec.ATTRIBUTE13 is null)))
221       AND ((recinfo.ATTRIBUTE14 = P_Item_Templates_Rec.ATTRIBUTE14)
222            OR ((recinfo.ATTRIBUTE14 is null) AND (P_Item_Templates_Rec.ATTRIBUTE14 is null)))
223       AND ((recinfo.ATTRIBUTE15 = P_Item_Templates_Rec.ATTRIBUTE15)
224            OR ((recinfo.ATTRIBUTE15 is null) AND (P_Item_Templates_Rec.ATTRIBUTE15 is null))))
225    THEN
226       NULL;
227    ELSE
228       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
229       Raise FND_API.g_EXC_UNEXPECTED_ERROR;
230    END IF;
231 
232    FOR tlinfo IN c_get_templates_trans
233    LOOP
234       IF (tlinfo.BASELANG = 'Y') THEN
235          IF((tlinfo.DESCRIPTION = P_Item_Templates_Rec.DESCRIPTION)
236              OR ((tlinfo.DESCRIPTION is null) AND (P_Item_Templates_Rec.DESCRIPTION is null))
237          AND ((tlinfo.template_name = P_Item_Templates_Rec.TEMPLATE_NAME)
238              OR ((tlinfo.template_name is null) AND (P_Item_Templates_Rec.TEMPLATE_NAME is null))))
239          THEN
240             NULL;
241          ELSE
242             fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
243             Raise FND_API.g_EXC_UNEXPECTED_ERROR;
244          END IF;
245       END IF;
246    END LOOP;
247 
248 EXCEPTION
249    WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
250       IF ( c_get_item_templates%ISOPEN ) THEN
251         CLOSE c_get_item_templates;
252       END IF;
253       IF ( c_get_templates_trans%ISOPEN ) THEN
254         CLOSE c_get_templates_trans;
255       END IF;
256       app_exception.raise_exception;
257 END LOCK_ROW;
258 
259 PROCEDURE UPDATE_ROW (P_Item_Templates_Rec IN  MTL_ITEM_Templates_B%ROWTYPE) IS
260 BEGIN
261    UPDATE MTL_ITEM_TEMPLATES_B
262    SET
263         LAST_UPDATE_DATE = P_Item_Templates_Rec.LAST_UPDATE_DATE,
264         LAST_UPDATED_BY = P_Item_Templates_Rec.LAST_UPDATED_BY,
265         CREATION_DATE = P_Item_Templates_Rec.CREATION_DATE,
266         CREATED_BY = P_Item_Templates_Rec.CREATED_BY,
267         LAST_UPDATE_LOGIN = P_Item_Templates_Rec.LAST_UPDATE_LOGIN,
268         ATTRIBUTE_CATEGORY = P_Item_Templates_Rec.ATTRIBUTE_CATEGORY,
269         ATTRIBUTE1 = P_Item_Templates_Rec.ATTRIBUTE1,
270         ATTRIBUTE2 = P_Item_Templates_Rec.ATTRIBUTE2,
271         ATTRIBUTE3 = P_Item_Templates_Rec.ATTRIBUTE3,
272         ATTRIBUTE4 = P_Item_Templates_Rec.ATTRIBUTE4,
273         ATTRIBUTE5 = P_Item_Templates_Rec.ATTRIBUTE5,
274         ATTRIBUTE6 = P_Item_Templates_Rec.ATTRIBUTE6,
275         ATTRIBUTE7 = P_Item_Templates_Rec.ATTRIBUTE7,
276         ATTRIBUTE8 = P_Item_Templates_Rec.ATTRIBUTE8,
277         ATTRIBUTE9 = P_Item_Templates_Rec.ATTRIBUTE9,
278         ATTRIBUTE10 = P_Item_Templates_Rec.ATTRIBUTE10,
279         ATTRIBUTE11 = P_Item_Templates_Rec.ATTRIBUTE11,
280         ATTRIBUTE12 = P_Item_Templates_Rec.ATTRIBUTE12,
281         ATTRIBUTE13 = P_Item_Templates_Rec.ATTRIBUTE13,
282         ATTRIBUTE14 = P_Item_Templates_Rec.ATTRIBUTE14,
283         ATTRIBUTE15 = P_Item_Templates_Rec.ATTRIBUTE15,
284         REQUEST_ID = P_Item_Templates_Rec.REQUEST_ID,
285         PROGRAM_APPLICATION_ID = P_Item_Templates_Rec.PROGRAM_APPLICATION_ID,
286         PROGRAM_ID = P_Item_Templates_Rec.PROGRAM_ID,
287         PROGRAM_UPDATE_DATE = P_Item_Templates_Rec.PROGRAM_UPDATE_DATE ,
288         CONTEXT_ORGANIZATION_ID = P_Item_Templates_Rec.CONTEXT_ORGANIZATION_ID,
289         GLOBAL_ATTRIBUTE_CATEGORY = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE_CATEGORY ,
290         GLOBAL_ATTRIBUTE1 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE1,
291         GLOBAL_ATTRIBUTE2 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE2,
292         GLOBAL_ATTRIBUTE3 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE3,
293         GLOBAL_ATTRIBUTE4 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE4,
294         GLOBAL_ATTRIBUTE5 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE5,
295         GLOBAL_ATTRIBUTE6 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE6,
296         GLOBAL_ATTRIBUTE7 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE7,
297         GLOBAL_ATTRIBUTE8 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE8,
298         GLOBAL_ATTRIBUTE9 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE9,
299         GLOBAL_ATTRIBUTE10 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE10,
300         GLOBAL_ATTRIBUTE11 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE11,
301         GLOBAL_ATTRIBUTE12 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE12,
302         GLOBAL_ATTRIBUTE13 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE13,
303         GLOBAL_ATTRIBUTE14 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE14,
304         GLOBAL_ATTRIBUTE15 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE15,
305         GLOBAL_ATTRIBUTE16 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE16,
306         GLOBAL_ATTRIBUTE17 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE17,
307         GLOBAL_ATTRIBUTE18 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE18,
308         GLOBAL_ATTRIBUTE19 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE19,
309         GLOBAL_ATTRIBUTE20 = P_Item_Templates_Rec.GLOBAL_ATTRIBUTE20
310    WHERE TEMPLATE_ID = P_Item_Templates_Rec.TEMPLATE_ID;
311 
312    IF (SQL%NOTFOUND) THEN
313       RAISE NO_DATA_FOUND;
314    END IF;
315 
316    UPDATE MTL_ITEM_TEMPLATES_TL set
317       TEMPLATE_NAME     = P_Item_Templates_Rec.TEMPLATE_NAME,
318       DESCRIPTION       = P_Item_Templates_Rec.DESCRIPTION,
319       LAST_UPDATE_DATE  = P_Item_Templates_Rec.LAST_UPDATE_DATE,
320       LAST_UPDATED_BY   = P_Item_Templates_Rec.LAST_UPDATED_BY,
321       LAST_UPDATE_LOGIN = P_Item_Templates_Rec.LAST_UPDATE_LOGIN,
322       SOURCE_LANG       = USERENV('LANG')
323    WHERE TEMPLATE_ID = P_Item_Templates_Rec.TEMPLATE_ID
324    AND  USERENV('LANG')   IN (LANGUAGE, SOURCE_LANG);
325 
326    IF (SQL%NOTFOUND) THEN
327       RAISE NO_DATA_FOUND;
328    END IF;
329 
330 EXCEPTION
331   WHEN NO_DATA_FOUND THEN
332        app_exception.raise_exception;
333 
334 END UPDATE_ROW;
335 
336 PROCEDURE DELETE_ROW (P_Template_Id IN NUMBER) IS
337 BEGIN
338 
339    DELETE FROM MTL_ITEM_TEMPLATES_TL
340    WHERE TEMPLATE_ID = P_Template_Id;
341 
342    IF (SQL%NOTFOUND) THEN
343       RAISE NO_DATA_FOUND;
344    END IF;
345 
346    DELETE FROM MTL_ITEM_TEMPLATES_B
347    WHERE TEMPLATE_ID = P_Template_Id;
348 
349    IF (SQL%NOTFOUND) THEN
350       RAISE NO_DATA_FOUND;
351    END IF;
352 
353 EXCEPTION
354   WHEN NO_DATA_FOUND THEN
355        app_exception.raise_exception;
356 END DELETE_ROW;
357 
358 PROCEDURE ADD_LANGUAGE IS
359 BEGIN
360    DELETE FROM MTL_ITEM_TEMPLATES_TL T
361    WHERE NOT EXISTS(SELECT NULL
362                     FROM MTL_ITEM_TEMPLATES_B B
363                     WHERE B.TEMPLATE_ID = T.TEMPLATE_ID);
364 
365    UPDATE MTL_ITEM_TEMPLATES_TL T
366    SET (TEMPLATE_NAME,DESCRIPTION) = (SELECT B.TEMPLATE_NAME,B.DESCRIPTION
367                         FROM   MTL_ITEM_TEMPLATES_TL B
368                         WHERE  B.TEMPLATE_ID = T.TEMPLATE_ID
369                         AND    B.LANGUAGE          = T.SOURCE_LANG)
370    WHERE (T.TEMPLATE_ID,
371           T.LANGUAGE) IN (SELECT SUBT.TEMPLATE_ID,
372                                  SUBT.LANGUAGE
373                           FROM   MTL_ITEM_TEMPLATES_TL SUBT,
374                   MTL_ITEM_TEMPLATES_TL SUBB
375                           WHERE  SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
376                           AND    SUBB.LANGUAGE = SUBT.SOURCE_LANG
377                           AND   (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
378                                 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
379                                 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
380            AND (SUBB.TEMPLATE_NAME <> SUBT.TEMPLATE_NAME
381                                 or (SUBB.TEMPLATE_NAME is null and SUBT.TEMPLATE_NAME is not null)
382             or (SUBB.TEMPLATE_NAME is not null and SUBT.TEMPLATE_NAME is null)));
383 
384    INSERT INTO MTL_ITEM_TEMPLATES_TL (
385     TEMPLATE_ID,
386     TEMPLATE_NAME,
387     DESCRIPTION,
388     CREATION_DATE,
389     CREATED_BY,
390     LAST_UPDATE_DATE,
391     LAST_UPDATED_BY,
392     LAST_UPDATE_LOGIN,
393     LANGUAGE,
394     SOURCE_LANG
395    ) SELECT B.TEMPLATE_ID,
396             B.TEMPLATE_NAME,
397             B.DESCRIPTION,
398             B.CREATION_DATE,
399             B.CREATED_BY,
400             B.LAST_UPDATE_DATE,
401             B.LAST_UPDATED_BY,
402             B.LAST_UPDATE_LOGIN,
403             L.LANGUAGE_CODE,
404             B.SOURCE_LANG
405      FROM  MTL_ITEM_TEMPLATES_TL B,
406            FND_LANGUAGES L
407      WHERE L.INSTALLED_FLAG IN ('I', 'B')
408      AND   B.LANGUAGE = USERENV('LANG')
409      AND  NOT EXISTS (SELECT NULL
410                       FROM MTL_ITEM_TEMPLATES_TL T
411                       WHERE T.TEMPLATE_ID = B.TEMPLATE_ID
412                       AND T.LANGUAGE      = L.LANGUAGE_CODE);
413 end ADD_LANGUAGE;
414 
415 end MTL_ITEM_TEMPLATES_PKG;