[Home] [Help]
PACKAGE BODY: APPS.MTL_ITEM_REVISIONS_UTIL
Source
1 PACKAGE BODY MTL_ITEM_REVISIONS_UTIL AS
2 /* $Header: INVIRVUB.pls 120.2 2011/09/16 23:09:49 akbharga ship $ */
3
4
5 --Added for bug 5435229
6 Procedure copy_rev_UDA(p_organization_id IN NUMBER
7 ,p_inventory_item_id IN NUMBER
8 ,p_revision_id IN NUMBER
9 ,p_revision IN VARCHAR2) IS
10
11 CURSOR c_get_effective_revision(cp_inventory_item_id NUMBER
12 ,cp_organization_id NUMBER
13 ,cp_revision VARCHAR2) IS
14 SELECT revision_id
15 FROM mtl_item_revisions_b
16 WHERE inventory_item_id = cp_inventory_item_id
17 AND organization_id = cp_organization_id
18 AND revision < cp_revision
19 AND implementation_date IS NOT NULL
20 AND effectivity_date <= sysdate
21 ORDER BY effectivity_date desc;
22
23 l_source_revision_id mtl_item_revisions_b.revision_id%TYPE;
24 l_return_status VARCHAR2(100);
25 l_error_code NUMBER;
26 l_msg_count NUMBER ;
27 l_msg_data VARCHAR2(100);
28 l_pk_item_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
29 l_pk_item_rev_pairs_src EGO_COL_NAME_VALUE_PAIR_ARRAY;
30 l_pk_item_rev_pairs_dst EGO_COL_NAME_VALUE_PAIR_ARRAY;
31
32 BEGIN
33 OPEN c_get_effective_revision(cp_inventory_item_id => p_inventory_item_id
34 ,cp_organization_id => p_organization_id
35 ,cp_revision => p_revision);
36 FETCH c_get_effective_revision INTO l_source_revision_id;
37 CLOSE c_get_effective_revision;
38
39 IF l_source_revision_id IS NOT NULL THEN
40 l_pk_item_pairs :=EGO_COL_NAME_VALUE_PAIR_ARRAY(
41 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_inventory_item_id)
42 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id));
43
44 l_pk_item_rev_pairs_src := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'REVISION_ID' , l_source_revision_id));
45 l_pk_item_rev_pairs_dst := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'REVISION_ID' , p_revision_id));
46 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data(
47 p_api_version => 1.0
48 ,p_application_id => 431
49 ,p_object_name => 'EGO_ITEM'
50 ,p_old_pk_col_value_pairs => l_pk_item_pairs
51 ,p_old_dtlevel_col_value_pairs => l_pk_item_rev_pairs_src
52 ,p_new_pk_col_value_pairs => l_pk_item_pairs
53 ,p_new_dtlevel_col_value_pairs => l_pk_item_rev_pairs_dst
54 ,x_return_status => l_return_status
55 ,x_errorcode => l_error_code
56 ,x_msg_count => l_msg_count
57 ,x_msg_data => l_msg_data);
58
59 END IF; --l_source_revision_id
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 NULL;
64 END copy_rev_UDA;
65
66 PROCEDURE INSERT_ROW(P_Item_Revision_Rec IN MTL_ITEM_REVISIONS_B%ROWTYPE,
67 X_ROWID OUT NOCOPY VARCHAR2) IS
68
69 BEGIN
70
71 INSERT INTO MTL_ITEM_REVISIONS_B (
72 REVISION_ID,
73 REVISION_LABEL,
74 REVISION_REASON,
75 LIFECYCLE_ID,
76 CURRENT_PHASE_ID,
77 INVENTORY_ITEM_ID,
78 ORGANIZATION_ID,
79 REVISION,
80 CHANGE_NOTICE,
81 ECN_INITIATION_DATE,
82 IMPLEMENTATION_DATE,
83 IMPLEMENTED_SERIAL_NUMBER,
84 EFFECTIVITY_DATE,
85 ATTRIBUTE_CATEGORY,
86 ATTRIBUTE1,
87 ATTRIBUTE2,
88 ATTRIBUTE3,
89 ATTRIBUTE4,
90 ATTRIBUTE5,
91 ATTRIBUTE6,
92 ATTRIBUTE7,
93 ATTRIBUTE8,
94 ATTRIBUTE9,
95 ATTRIBUTE10,
96 ATTRIBUTE11,
97 ATTRIBUTE12,
98 ATTRIBUTE13,
99 ATTRIBUTE14,
100 ATTRIBUTE15,
101 REQUEST_ID,
102 REVISED_ITEM_SEQUENCE_ID,
103 OBJECT_VERSION_NUMBER,
104 CREATION_DATE,
105 CREATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATED_BY,
108 LAST_UPDATE_LOGIN
109 ) VALUES (
110 P_Item_Revision_Rec.REVISION_ID,
111 P_Item_Revision_Rec.REVISION_LABEL,
112 P_Item_Revision_Rec.REVISION_REASON,
113 P_Item_Revision_Rec.LIFECYCLE_ID,
114 P_Item_Revision_Rec.CURRENT_PHASE_ID,
115 P_Item_Revision_Rec.INVENTORY_ITEM_ID,
116 P_Item_Revision_Rec.ORGANIZATION_ID,
117 P_Item_Revision_Rec.REVISION,
118 P_Item_Revision_Rec.CHANGE_NOTICE,
119 P_Item_Revision_Rec.ECN_INITIATION_DATE,
120 P_Item_Revision_Rec.IMPLEMENTATION_DATE,
121 P_Item_Revision_Rec.IMPLEMENTED_SERIAL_NUMBER,
122 P_Item_Revision_Rec.EFFECTIVITY_DATE,
123 P_Item_Revision_Rec.ATTRIBUTE_CATEGORY,
124 P_Item_Revision_Rec.ATTRIBUTE1,
125 P_Item_Revision_Rec.ATTRIBUTE2,
126 P_Item_Revision_Rec.ATTRIBUTE3,
127 P_Item_Revision_Rec.ATTRIBUTE4,
128 P_Item_Revision_Rec.ATTRIBUTE5,
129 P_Item_Revision_Rec.ATTRIBUTE6,
130 P_Item_Revision_Rec.ATTRIBUTE7,
131 P_Item_Revision_Rec.ATTRIBUTE8,
132 P_Item_Revision_Rec.ATTRIBUTE9,
133 P_Item_Revision_Rec.ATTRIBUTE10,
134 P_Item_Revision_Rec.ATTRIBUTE11,
135 P_Item_Revision_Rec.ATTRIBUTE12,
136 P_Item_Revision_Rec.ATTRIBUTE13,
137 P_Item_Revision_Rec.ATTRIBUTE14,
138 P_Item_Revision_Rec.ATTRIBUTE15,
139 P_Item_Revision_Rec.REQUEST_ID,
140 P_Item_Revision_Rec.REVISED_ITEM_SEQUENCE_ID,
141 NVL(P_Item_Revision_Rec.OBJECT_VERSION_NUMBER,1),
142 P_Item_Revision_Rec.CREATION_DATE,
143 P_Item_Revision_Rec.CREATED_BY,
144 P_Item_Revision_Rec.LAST_UPDATE_DATE,
145 P_Item_Revision_Rec.LAST_UPDATED_BY,
146 P_Item_Revision_Rec.LAST_UPDATE_LOGIN
147 ) RETURNING ROWID INTO X_ROWID;
148
149 INSERT INTO MTL_ITEM_REVISIONS_TL (
150 INVENTORY_ITEM_ID,
151 ORGANIZATION_ID,
152 REVISION_ID,
153 DESCRIPTION,
154 CREATION_DATE,
155 CREATED_BY,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 LAST_UPDATE_LOGIN,
159 LANGUAGE,
160 SOURCE_LANG
161 ) SELECT P_Item_Revision_Rec.INVENTORY_ITEM_ID,
162 P_Item_Revision_Rec.ORGANIZATION_ID,
163 P_Item_Revision_Rec.REVISION_ID,
164 P_Item_Revision_Rec.DESCRIPTION,
165 P_Item_Revision_Rec.CREATION_DATE,
166 P_Item_Revision_Rec.CREATED_BY,
167 P_Item_Revision_Rec.LAST_UPDATE_DATE,
168 P_Item_Revision_Rec.LAST_UPDATED_BY,
169 P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
170 L.LANGUAGE_CODE,
171 USERENV('LANG')
172 FROM FND_LANGUAGES L
173 WHERE L.INSTALLED_FLAG in ('I', 'B')
174 AND NOT EXISTS (SELECT NULL
175 FROM MTL_ITEM_REVISIONS_TL T
176 WHERE T.INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
177 AND T.ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
178 AND T.REVISION_ID = P_Item_Revision_Rec.REVISION_ID
179 AND T.LANGUAGE = L.LANGUAGE_CODE);
180
181 -- Bug 5435229
182 -- Copy revision UDA
183 copy_rev_UDA(p_organization_id => p_Item_Revision_rec.organization_id
184 ,p_inventory_item_id => p_Item_Revision_rec.inventory_item_id
185 ,p_revision_id => p_Item_Revision_rec.revision_id
186 ,p_revision => p_Item_Revision_rec.revision);
187
188 -- R12: Business Event Enhancement : Raise Event if Revision got Created successfully
189 BEGIN
190 INV_ITEM_EVENTS_PVT.Raise_Events(
191 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
192 ,p_dml_type => 'CREATE'
193 ,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
194 ,p_organization_id => p_Item_Revision_rec.Organization_Id
195 ,p_revision_id => p_Item_Revision_rec.revision_id);
196 EXCEPTION
197 WHEN OTHERS THEN
198 NULL;
199 END;
200 --R12: Business Event Enhancement : Raise Event if Revision got Created successfully
201 END INSERT_ROW;
202
203 PROCEDURE LOCK_ROW (P_Item_Revision_Rec IN MTL_ITEM_REVISIONS_B%ROWTYPE) IS
204
205 CURSOR c_get_item_revision IS
206 SELECT
207 REVISION_LABEL,
208 REVISION_REASON,
209 LIFECYCLE_ID,
210 CURRENT_PHASE_ID,
211 REVISION,
212 CHANGE_NOTICE,
213 ECN_INITIATION_DATE,
214 IMPLEMENTATION_DATE,
215 IMPLEMENTED_SERIAL_NUMBER,
216 EFFECTIVITY_DATE,
217 ATTRIBUTE_CATEGORY,
218 ATTRIBUTE1,
219 ATTRIBUTE2,
220 ATTRIBUTE3,
221 ATTRIBUTE4,
222 ATTRIBUTE5,
223 ATTRIBUTE6,
224 ATTRIBUTE7,
225 ATTRIBUTE8,
226 ATTRIBUTE9,
227 ATTRIBUTE10,
228 ATTRIBUTE11,
229 ATTRIBUTE12,
230 ATTRIBUTE13,
231 ATTRIBUTE14,
232 ATTRIBUTE15,
233 REQUEST_ID,
234 REVISED_ITEM_SEQUENCE_ID,
235 OBJECT_VERSION_NUMBER
236 FROM MTL_ITEM_REVISIONS_B
237 WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
238 AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
239 AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
240 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT;
241
242 CURSOR c_get_revision_desc IS
243 SELECT
244 DESCRIPTION,
245 DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
246 FROM MTL_ITEM_REVISIONS_TL
247 WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
248 AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
249 AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
250 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
251 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT;
252
253 recinfo c_get_item_revision%rowtype;
254 BEGIN
255
256 OPEN c_get_item_revision;
257 FETCH c_get_item_revision INTO recinfo;
258 IF (c_get_item_revision%notfound) THEN
259 CLOSE c_get_item_revision;
260 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
261 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
262 END IF;
263 CLOSE c_get_item_revision;
264
265 IF ((recinfo.REVISION = P_Item_Revision_Rec.REVISION)
266 AND (recinfo.REVISION_LABEL = P_Item_Revision_Rec.REVISION_LABEL)--Bug: 3017253
267 AND ((recinfo.CHANGE_NOTICE = P_Item_Revision_Rec.CHANGE_NOTICE)
268 OR ((recinfo.CHANGE_NOTICE is null) AND (P_Item_Revision_Rec.CHANGE_NOTICE is null)))
269 AND ((TRUNC(recinfo.ECN_INITIATION_DATE) = TRUNC(P_Item_Revision_Rec.ECN_INITIATION_DATE))
270 OR ((recinfo.ECN_INITIATION_DATE is null) AND (P_Item_Revision_Rec.ECN_INITIATION_DATE is null)))
271 AND ((TRUNC(recinfo.IMPLEMENTATION_DATE) = TRUNC(P_Item_Revision_Rec.IMPLEMENTATION_DATE))
272 OR ((recinfo.IMPLEMENTATION_DATE is null) AND (P_Item_Revision_Rec.IMPLEMENTATION_DATE is null)))
273 AND (TRUNC(recinfo.EFFECTIVITY_DATE) = TRUNC(P_Item_Revision_Rec.EFFECTIVITY_DATE))
274 AND ((recinfo.ATTRIBUTE_CATEGORY = P_Item_Revision_Rec.ATTRIBUTE_CATEGORY)
275 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_Item_Revision_Rec.ATTRIBUTE_CATEGORY is null)))
276 AND ((recinfo.ATTRIBUTE1 = P_Item_Revision_Rec.ATTRIBUTE1)
277 OR ((recinfo.ATTRIBUTE1 is null) AND (P_Item_Revision_Rec.ATTRIBUTE1 is null)))
278 AND ((recinfo.ATTRIBUTE2 = P_Item_Revision_Rec.ATTRIBUTE2)
279 OR ((recinfo.ATTRIBUTE2 is null) AND (P_Item_Revision_Rec.ATTRIBUTE2 is null)))
280 AND ((recinfo.ATTRIBUTE3 = P_Item_Revision_Rec.ATTRIBUTE3)
281 OR ((recinfo.ATTRIBUTE3 is null) AND (P_Item_Revision_Rec.ATTRIBUTE3 is null)))
282 AND ((recinfo.ATTRIBUTE4 = P_Item_Revision_Rec.ATTRIBUTE4)
283 OR ((recinfo.ATTRIBUTE4 is null) AND (P_Item_Revision_Rec.ATTRIBUTE4 is null)))
284 AND ((recinfo.ATTRIBUTE5 = P_Item_Revision_Rec.ATTRIBUTE5)
285 OR ((recinfo.ATTRIBUTE5 is null) AND (P_Item_Revision_Rec.ATTRIBUTE5 is null)))
286 AND ((recinfo.ATTRIBUTE6 = P_Item_Revision_Rec.ATTRIBUTE6)
287 OR ((recinfo.ATTRIBUTE6 is null) AND (P_Item_Revision_Rec.ATTRIBUTE6 is null)))
288 AND ((recinfo.ATTRIBUTE7 = P_Item_Revision_Rec.ATTRIBUTE7)
289 OR ((recinfo.ATTRIBUTE7 is null) AND (P_Item_Revision_Rec.ATTRIBUTE7 is null)))
290 AND ((recinfo.ATTRIBUTE8 = P_Item_Revision_Rec.ATTRIBUTE8)
291 OR ((recinfo.ATTRIBUTE8 is null) AND (P_Item_Revision_Rec.ATTRIBUTE8 is null)))
292 AND ((recinfo.ATTRIBUTE9 = P_Item_Revision_Rec.ATTRIBUTE9)
293 OR ((recinfo.ATTRIBUTE9 is null) AND (P_Item_Revision_Rec.ATTRIBUTE9 is null)))
294 AND ((recinfo.ATTRIBUTE10 = P_Item_Revision_Rec.ATTRIBUTE10)
295 OR ((recinfo.ATTRIBUTE10 is null) AND (P_Item_Revision_Rec.ATTRIBUTE10 is null)))
296 AND ((recinfo.ATTRIBUTE11 = P_Item_Revision_Rec.ATTRIBUTE11)
297 OR ((recinfo.ATTRIBUTE11 is null) AND (P_Item_Revision_Rec.ATTRIBUTE11 is null)))
298 AND ((recinfo.ATTRIBUTE12 = P_Item_Revision_Rec.ATTRIBUTE12)
299 OR ((recinfo.ATTRIBUTE12 is null) AND (P_Item_Revision_Rec.ATTRIBUTE12 is null)))
300 AND ((recinfo.ATTRIBUTE13 = P_Item_Revision_Rec.ATTRIBUTE13)
301 OR ((recinfo.ATTRIBUTE13 is null) AND (P_Item_Revision_Rec.ATTRIBUTE13 is null)))
302 AND ((recinfo.ATTRIBUTE14 = P_Item_Revision_Rec.ATTRIBUTE14)
303 OR ((recinfo.ATTRIBUTE14 is null) AND (P_Item_Revision_Rec.ATTRIBUTE14 is null)))
304 AND ((recinfo.ATTRIBUTE15 = P_Item_Revision_Rec.ATTRIBUTE15)
305 OR ((recinfo.ATTRIBUTE15 is null) AND (P_Item_Revision_Rec.ATTRIBUTE15 is null))))
306 THEN
307 NULL;
308 ELSE
309 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
310 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
311 END IF;
312
313 FOR tlinfo IN c_get_revision_desc
314 LOOP
315 IF (tlinfo.BASELANG = 'Y') THEN
316 IF (((tlinfo.DESCRIPTION = P_Item_Revision_Rec.DESCRIPTION)
317 OR ((tlinfo.DESCRIPTION is null) AND (P_Item_Revision_Rec.DESCRIPTION is null))))
318 THEN
319 NULL;
320 ELSE
321 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
322 Raise FND_API.g_EXC_UNEXPECTED_ERROR;
323 END IF;
324 END IF;
325 END LOOP;
326
327 EXCEPTION
328 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
329 IF ( c_get_item_revision%ISOPEN ) THEN
330 CLOSE c_get_item_revision;
331 END IF;
332 IF ( c_get_revision_desc%ISOPEN ) THEN
333 CLOSE c_get_revision_desc;
334 END IF;
335 app_exception.raise_exception;
336 END LOCK_ROW;
337
338 PROCEDURE UPDATE_ROW (P_Item_Revision_Rec IN MTL_ITEM_REVISIONS_B%ROWTYPE) IS
339
340 BEGIN
341 UPDATE MTL_ITEM_REVISIONS_B
342 SET
343 REVISION = P_Item_Revision_Rec.REVISION,
344 REVISION_LABEL = P_Item_Revision_Rec.REVISION_LABEL,--Bug: 3017253
345 CHANGE_NOTICE = P_Item_Revision_Rec.CHANGE_NOTICE,
346 ECN_INITIATION_DATE = P_Item_Revision_Rec.ECN_INITIATION_DATE,
347 IMPLEMENTATION_DATE = P_Item_Revision_Rec.IMPLEMENTATION_DATE,
348 EFFECTIVITY_DATE = DECODE(TRUNC(P_Item_Revision_Rec.EFFECTIVITY_DATE),TRUNC(EFFECTIVITY_DATE),EFFECTIVITY_DATE,TRUNC(SYSDATE),SYSDATE,P_Item_Revision_Rec.EFFECTIVITY_DATE),
349 ATTRIBUTE_CATEGORY = P_Item_Revision_Rec.ATTRIBUTE_CATEGORY,
350 ATTRIBUTE1 = P_Item_Revision_Rec.ATTRIBUTE1,
351 ATTRIBUTE2 = P_Item_Revision_Rec.ATTRIBUTE2,
352 ATTRIBUTE3 = P_Item_Revision_Rec.ATTRIBUTE3,
353 ATTRIBUTE4 = P_Item_Revision_Rec.ATTRIBUTE4,
354 ATTRIBUTE5 = P_Item_Revision_Rec.ATTRIBUTE5,
355 ATTRIBUTE6 = P_Item_Revision_Rec.ATTRIBUTE6,
356 ATTRIBUTE7 = P_Item_Revision_Rec.ATTRIBUTE7,
357 ATTRIBUTE8 = P_Item_Revision_Rec.ATTRIBUTE8,
358 ATTRIBUTE9 = P_Item_Revision_Rec.ATTRIBUTE9,
359 ATTRIBUTE10 = P_Item_Revision_Rec.ATTRIBUTE10,
360 ATTRIBUTE11 = P_Item_Revision_Rec.ATTRIBUTE11,
361 ATTRIBUTE12 = P_Item_Revision_Rec.ATTRIBUTE12,
362 ATTRIBUTE13 = P_Item_Revision_Rec.ATTRIBUTE13,
363 ATTRIBUTE14 = P_Item_Revision_Rec.ATTRIBUTE14,
364 ATTRIBUTE15 = P_Item_Revision_Rec.ATTRIBUTE15,
365 LAST_UPDATE_DATE = P_Item_Revision_Rec.LAST_UPDATE_DATE,
366 LAST_UPDATED_BY = P_Item_Revision_Rec.LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN = P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
368 /* Bug 4224512 : Incrementing OBJECT_VERSION_NUMBER each time revision is updated - Anmurali*/
369 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1
370 WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
371 AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
372 AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID;
373
374 IF (SQL%NOTFOUND) THEN
375 RAISE NO_DATA_FOUND;
376 END IF;
377
378 UPDATE MTL_ITEM_REVISIONS_TL set
379 DESCRIPTION = P_Item_Revision_Rec.DESCRIPTION,
380 LAST_UPDATE_DATE = P_Item_Revision_Rec.LAST_UPDATE_DATE,
381 LAST_UPDATED_BY = P_Item_Revision_Rec.LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN = P_Item_Revision_Rec.LAST_UPDATE_LOGIN,
383 SOURCE_LANG = USERENV('LANG')
384 WHERE INVENTORY_ITEM_ID = P_Item_Revision_Rec.INVENTORY_ITEM_ID
388
385 AND ORGANIZATION_ID = P_Item_Revision_Rec.ORGANIZATION_ID
386 AND REVISION_ID = P_Item_Revision_Rec.REVISION_ID
387 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
389 IF (SQL%NOTFOUND) THEN
390 RAISE NO_DATA_FOUND;
391 END IF;
392
393 --R12: Business Event Enhancement : Raise Event if Revision got Updated successfully
394 BEGIN
395 INV_ITEM_EVENTS_PVT.Raise_Events(
396 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
397 ,p_dml_type => 'UPDATE'
398 ,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
399 ,p_organization_id => p_Item_Revision_rec.Organization_Id
400 ,p_revision_id => p_Item_Revision_rec.revision_id);
401 EXCEPTION
402 WHEN OTHERS THEN
403 NULL;
404 END;
405 --R12: Business Event Enhancement : Raise Event if Revision got Updated successfully
406
407 END UPDATE_ROW;
408
409 PROCEDURE ADD_LANGUAGE IS
410 BEGIN
411
412 -- Comment out as part of SQL Repositry fix. Bug: 4256727
413
414 /* DELETE FROM MTL_ITEM_REVISIONS_TL T
415 WHERE NOT EXISTS(SELECT NULL
416 FROM MTL_ITEM_REVISIONS_B B
417 WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
418 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
419 AND B.REVISION_ID = T.REVISION_ID);
420
421 UPDATE MTL_ITEM_REVISIONS_TL T
422 SET (DESCRIPTION) = (SELECT B.DESCRIPTION
423 FROM MTL_ITEM_REVISIONS_TL B
424 WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
425 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
426 AND B.REVISION_ID = T.REVISION_ID
427 AND B.LANGUAGE = T.SOURCE_LANG)
428 WHERE (T.INVENTORY_ITEM_ID,
429 T.ORGANIZATION_ID,
430 T.REVISION_ID,
431 T.LANGUAGE) IN (SELECT SUBT.INVENTORY_ITEM_ID,
432 SUBT.ORGANIZATION_ID,
433 SUBT.REVISION_ID,
434 SUBT.LANGUAGE
435 FROM MTL_ITEM_REVISIONS_TL SUBB,
436 MTL_ITEM_REVISIONS_TL SUBT
437 WHERE SUBB.INVENTORY_ITEM_ID = SUBT.INVENTORY_ITEM_ID
438 AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
439 AND SUBB.REVISION_ID = SUBT.REVISION_ID
440 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
441 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
442 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
443 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)));
444 */
445 INSERT /*+ append parallel(tl) */
446 INTO MTL_ITEM_REVISIONS_TL tl (
447 INVENTORY_ITEM_ID,
448 ORGANIZATION_ID,
449 REVISION_ID,
450 DESCRIPTION,
451 CREATION_DATE,
452 CREATED_BY,
453 LAST_UPDATE_DATE,
454 LAST_UPDATED_BY,
455 LAST_UPDATE_LOGIN,
456 LANGUAGE,
457 SOURCE_LANG
458 ) SELECT /*+ PARALLEL(B) PARALLEL(L) */
459 B.INVENTORY_ITEM_ID,
460 B.ORGANIZATION_ID,
461 B.REVISION_ID,
462 B.DESCRIPTION,
463 B.CREATION_DATE,
464 B.CREATED_BY,
465 B.LAST_UPDATE_DATE,
466 B.LAST_UPDATED_BY,
467 B.LAST_UPDATE_LOGIN,
468 L.LANGUAGE_CODE,
469 B.SOURCE_LANG
470 FROM MTL_ITEM_REVISIONS_TL B,
471 FND_LANGUAGES L
472 WHERE L.INSTALLED_FLAG IN ('I', 'B')
473 AND B.LANGUAGE = USERENV('LANG')
474 AND NOT EXISTS (SELECT /*+ parallel(T) */ NULL
475 FROM MTL_ITEM_REVISIONS_TL T
476 WHERE T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
477 AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
478 AND T.REVISION_ID = B.REVISION_ID
479 AND T.LANGUAGE = L.LANGUAGE_CODE);
480 end ADD_LANGUAGE;
481
482 end MTL_ITEM_REVISIONS_UTIL;