1 PACKAGE BODY MTL_CROSS_REFERENCES_PKG AS
2 /* $Header: INVIDXRB.pls 120.0 2005/06/22 23:08:19 lparihar noship $ */
3
4 PROCEDURE INSERT_ROW (
5 P_SOURCE_SYSTEM_ID IN NUMBER
6 ,P_START_DATE_ACTIVE IN DATE
7 ,P_END_DATE_ACTIVE IN DATE
8 ,P_OBJECT_VERSION_NUMBER IN NUMBER
9 ,P_UOM_CODE IN VARCHAR2
10 ,P_REVISION_ID IN NUMBER
11 ,P_EPC_GTIN_SERIAL IN NUMBER
12 ,P_INVENTORY_ITEM_ID IN NUMBER
13 ,P_ORGANIZATION_ID IN NUMBER
14 ,P_CROSS_REFERENCE_TYPE IN VARCHAR2
15 ,P_CROSS_REFERENCE IN VARCHAR2
16 ,P_ORG_INDEPENDENT_FLAG IN VARCHAR2
17 ,P_REQUEST_ID IN NUMBER
18 ,P_ATTRIBUTE1 IN VARCHAR2
19 ,P_ATTRIBUTE2 IN VARCHAR2
20 ,P_ATTRIBUTE3 IN VARCHAR2
21 ,P_ATTRIBUTE4 IN VARCHAR2
22 ,P_ATTRIBUTE5 IN VARCHAR2
23 ,P_ATTRIBUTE6 IN VARCHAR2
24 ,P_ATTRIBUTE7 IN VARCHAR2
25 ,P_ATTRIBUTE8 IN VARCHAR2
26 ,P_ATTRIBUTE9 IN VARCHAR2
27 ,P_ATTRIBUTE10 IN VARCHAR2
28 ,P_ATTRIBUTE11 IN VARCHAR2
29 ,P_ATTRIBUTE12 IN VARCHAR2
30 ,P_ATTRIBUTE13 IN VARCHAR2
31 ,P_ATTRIBUTE14 IN VARCHAR2
32 ,P_ATTRIBUTE15 IN VARCHAR2
33 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2
34 ,P_DESCRIPTION IN VARCHAR2
35 ,P_CREATION_DATE IN DATE
36 ,P_CREATED_BY IN NUMBER
37 ,P_LAST_UPDATE_DATE IN DATE
38 ,P_LAST_UPDATED_BY IN NUMBER
39 ,P_LAST_UPDATE_LOGIN IN NUMBER
40 ,P_PROGRAM_APPLICATION_ID IN NUMBER
41 ,P_PROGRAM_ID IN NUMBER
42 ,P_PROGRAM_UPDATE_DATE IN DATE
43 ,X_CROSS_REFERENCE_ID OUT NOCOPY NUMBER) IS
44
45 CURSOR C_CHECK_INSERT IS
46 SELECT 'Y'
47 FROM MTL_CROSS_REFERENCES_B
48 WHERE CROSS_REFERENCE_ID = X_CROSS_REFERENCE_ID;
49
50 l_exists VARCHAR2(1);
51
52 BEGIN
53
54 INSERT INTO MTL_CROSS_REFERENCES_B (
55 SOURCE_SYSTEM_ID
56 ,START_DATE_ACTIVE
57 ,END_DATE_ACTIVE
58 ,OBJECT_VERSION_NUMBER
59 ,UOM_CODE
60 ,REVISION_ID
61 ,CROSS_REFERENCE_ID
62 ,EPC_GTIN_SERIAL
63 ,INVENTORY_ITEM_ID
64 ,ORGANIZATION_ID
65 ,CROSS_REFERENCE_TYPE
66 ,CROSS_REFERENCE
67 ,ORG_INDEPENDENT_FLAG
68 ,REQUEST_ID
69 ,ATTRIBUTE1
70 ,ATTRIBUTE2
71 ,ATTRIBUTE3
72 ,ATTRIBUTE4
73 ,ATTRIBUTE5
74 ,ATTRIBUTE6
75 ,ATTRIBUTE7
76 ,ATTRIBUTE8
77 ,ATTRIBUTE9
78 ,ATTRIBUTE10
79 ,ATTRIBUTE11
80 ,ATTRIBUTE12
81 ,ATTRIBUTE13
82 ,ATTRIBUTE14
83 ,ATTRIBUTE15
84 ,ATTRIBUTE_CATEGORY
85 ,CREATION_DATE
86 ,CREATED_BY
87 ,LAST_UPDATE_DATE
88 ,LAST_UPDATED_BY
89 ,LAST_UPDATE_LOGIN
90 ,PROGRAM_APPLICATION_ID
91 ,PROGRAM_ID
92 ,PROGRAM_UPDATE_DATE)
93 VALUES(
94 P_SOURCE_SYSTEM_ID
95 ,P_START_DATE_ACTIVE
96 ,P_END_DATE_ACTIVE
97 ,NVL(P_OBJECT_VERSION_NUMBER,1)
98 ,P_UOM_CODE
99 ,P_REVISION_ID
100 ,MTL_CROSS_REFERENCES_B_S.NEXTVAL
101 ,NVL(P_EPC_GTIN_SERIAL,0)
102 ,P_INVENTORY_ITEM_ID
103 ,P_ORGANIZATION_ID
104 ,P_CROSS_REFERENCE_TYPE
105 ,P_CROSS_REFERENCE
106 ,P_ORG_INDEPENDENT_FLAG
107 ,P_REQUEST_ID
108 ,P_ATTRIBUTE1
109 ,P_ATTRIBUTE2
110 ,P_ATTRIBUTE3
111 ,P_ATTRIBUTE4
112 ,P_ATTRIBUTE5
113 ,P_ATTRIBUTE6
114 ,P_ATTRIBUTE7
115 ,P_ATTRIBUTE8
116 ,P_ATTRIBUTE9
117 ,P_ATTRIBUTE10
118 ,P_ATTRIBUTE11
119 ,P_ATTRIBUTE12
120 ,P_ATTRIBUTE13
121 ,P_ATTRIBUTE14
122 ,P_ATTRIBUTE15
123 ,P_ATTRIBUTE_CATEGORY
124 ,NVL(P_CREATION_DATE,SYSDATE)
125 ,NVL(P_CREATED_BY,FND_GLOBAL.USER_ID)
126 ,NVL(P_LAST_UPDATE_DATE,SYSDATE)
127 ,NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID)
128 ,NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
129 ,P_PROGRAM_APPLICATION_ID
130 ,P_PROGRAM_ID
131 ,P_PROGRAM_UPDATE_DATE)
132 RETURNING CROSS_REFERENCE_ID INTO X_CROSS_REFERENCE_ID ;
133
134 INSERT INTO MTL_CROSS_REFERENCES_TL (
135 LAST_UPDATE_LOGIN
136 ,DESCRIPTION
137 ,CREATION_DATE
138 ,CREATED_BY
139 ,LAST_UPDATE_DATE
140 ,LAST_UPDATED_BY
141 ,CROSS_REFERENCE_ID
142 ,LANGUAGE
143 ,SOURCE_LANG)
144 SELECT
145 NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
146 P_DESCRIPTION,
147 NVL(P_CREATION_DATE,SYSDATE),
148 NVL(P_CREATED_BY,FND_GLOBAL.USER_ID),
149 NVL(P_LAST_UPDATE_DATE,SYSDATE),
150 NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
151 X_CROSS_REFERENCE_ID,
152 L.LANGUAGE_CODE,
153 USERENV('LANG')
154 FROM FND_LANGUAGES L
155 WHERE L.INSTALLED_FLAG in ('I', 'B')
156 AND NOT EXISTS (SELECT NULL
157 FROM MTL_CROSS_REFERENCES_TL T
158 WHERE T.CROSS_REFERENCE_ID = X_CROSS_REFERENCE_ID
159 AND T.LANGUAGE = L.LANGUAGE_CODE);
160
161 OPEN C_CHECK_INSERT;
162 FETCH C_CHECK_INSERT INTO l_exists;
163 IF (C_CHECK_INSERT%NOTFOUND) THEN
164 CLOSE C_CHECK_INSERT;
165 RAISE NO_DATA_FOUND;
166 END IF;
167 CLOSE C_CHECK_INSERT;
168 END INSERT_ROW;
169
170 PROCEDURE LOCK_ROW (
171 P_CROSS_REFERENCE_ID IN NUMBER
172 ,P_SOURCE_SYSTEM_ID IN NUMBER
173 ,P_START_DATE_ACTIVE IN DATE
174 ,P_END_DATE_ACTIVE IN DATE
175 ,P_OBJECT_VERSION_NUMBER IN NUMBER
176 ,P_UOM_CODE IN VARCHAR2
177 ,P_REVISION_ID IN NUMBER
178 ,P_EPC_GTIN_SERIAL IN NUMBER
179 ,P_INVENTORY_ITEM_ID IN NUMBER
180 ,P_ORGANIZATION_ID IN NUMBER
181 ,P_CROSS_REFERENCE_TYPE IN VARCHAR2
182 ,P_CROSS_REFERENCE IN VARCHAR2
183 ,P_ORG_INDEPENDENT_FLAG IN VARCHAR2
184 ,P_ATTRIBUTE1 IN VARCHAR2
185 ,P_ATTRIBUTE2 IN VARCHAR2
186 ,P_ATTRIBUTE3 IN VARCHAR2
187 ,P_ATTRIBUTE4 IN VARCHAR2
188 ,P_ATTRIBUTE5 IN VARCHAR2
189 ,P_ATTRIBUTE6 IN VARCHAR2
190 ,P_ATTRIBUTE7 IN VARCHAR2
191 ,P_ATTRIBUTE8 IN VARCHAR2
192 ,P_ATTRIBUTE9 IN VARCHAR2
193 ,P_ATTRIBUTE10 IN VARCHAR2
194 ,P_ATTRIBUTE11 IN VARCHAR2
195 ,P_ATTRIBUTE12 IN VARCHAR2
196 ,P_ATTRIBUTE13 IN VARCHAR2
197 ,P_ATTRIBUTE14 IN VARCHAR2
198 ,P_ATTRIBUTE15 IN VARCHAR2
199 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2
200 ,P_DESCRIPTION IN VARCHAR2) IS
201
202 CURSOR C_CROSS_REF_B IS
203 SELECT
204 SOURCE_SYSTEM_ID
205 ,START_DATE_ACTIVE
206 ,END_DATE_ACTIVE
207 ,OBJECT_VERSION_NUMBER
208 ,UOM_CODE
209 ,REVISION_ID
210 ,EPC_GTIN_SERIAL
211 ,INVENTORY_ITEM_ID
212 ,ORGANIZATION_ID
213 ,CROSS_REFERENCE_TYPE
214 ,CROSS_REFERENCE
215 ,ORG_INDEPENDENT_FLAG
216 ,REQUEST_ID
217 ,ATTRIBUTE1
218 ,ATTRIBUTE2
219 ,ATTRIBUTE3
220 ,ATTRIBUTE4
221 ,ATTRIBUTE5
222 ,ATTRIBUTE6
223 ,ATTRIBUTE7
224 ,ATTRIBUTE8
225 ,ATTRIBUTE9
226 ,ATTRIBUTE10
227 ,ATTRIBUTE11
228 ,ATTRIBUTE12
229 ,ATTRIBUTE13
230 ,ATTRIBUTE14
231 ,ATTRIBUTE15
232 ,ATTRIBUTE_CATEGORY
233 FROM MTL_CROSS_REFERENCES_B
234 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
235 FOR UPDATE OF CROSS_REFERENCE_ID NOWAIT;
236
237 CURSOR C_CROSS_REF_TL IS
238 SELECT
239 DESCRIPTION
240 ,DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
241 FROM MTL_CROSS_REFERENCES_TL
242 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
243 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
244 FOR UPDATE OF CROSS_REFERENCE_ID NOWAIT;
245
246 l_recinfo C_CROSS_REF_B%ROWTYPE;
247
248 BEGIN
249
250 OPEN C_CROSS_REF_B;
251 FETCH C_CROSS_REF_B INTO l_recinfo;
252 IF (C_CROSS_REF_B%NOTFOUND) THEN
253 CLOSE C_CROSS_REF_B;
254 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
255 APP_EXCEPTION.RAISE_EXCEPTION;
256 END IF;
257 CLOSE C_CROSS_REF_B;
258
259 IF (((l_recinfo.SOURCE_SYSTEM_ID = P_SOURCE_SYSTEM_ID)
260 OR ((l_recinfo.SOURCE_SYSTEM_ID is null) AND (P_SOURCE_SYSTEM_ID is null)))
261 AND ((l_recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
262 OR ((l_recinfo.OBJECT_VERSION_NUMBER is null) AND (P_OBJECT_VERSION_NUMBER is null)))
263 AND ((l_recinfo.UOM_CODE = P_UOM_CODE)
264 OR ((l_recinfo.UOM_CODE is null) AND (P_UOM_CODE is null)))
265 AND ((l_recinfo.REVISION_ID = P_REVISION_ID)
266 OR ((l_recinfo.REVISION_ID is null) AND (P_REVISION_ID is null)))
267 AND ((l_recinfo.EPC_GTIN_SERIAL = P_EPC_GTIN_SERIAL)
268 OR ((l_recinfo.EPC_GTIN_SERIAL is null) AND (P_EPC_GTIN_SERIAL is null)))
269 AND (l_recinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
270 AND ((l_recinfo.ORGANIZATION_ID = P_ORGANIZATION_ID)
271 OR ((l_recinfo.ORGANIZATION_ID is null) AND (P_ORGANIZATION_ID is null)))
272 AND (l_recinfo.CROSS_REFERENCE_TYPE = P_CROSS_REFERENCE_TYPE)
273 AND (l_recinfo.CROSS_REFERENCE = P_CROSS_REFERENCE)
274 AND (l_recinfo.ORG_INDEPENDENT_FLAG = P_ORG_INDEPENDENT_FLAG)
275 AND ((l_recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
276 OR ((l_recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
277 AND ((l_recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
278 OR ((l_recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
279 AND ((l_recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
280 OR ((l_recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
281 AND ((l_recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
282 OR ((l_recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
283 AND ((l_recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
284 OR ((l_recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
285 AND ((l_recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
286 OR ((l_recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
287 AND ((l_recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
288 OR ((l_recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
289 AND ((l_recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
290 OR ((l_recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
291 AND ((l_recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
292 OR ((l_recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
293 AND ((l_recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
294 OR ((l_recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
295 AND ((l_recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
296 OR ((l_recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
297 AND ((l_recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
298 OR ((l_recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
299 AND ((l_recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
300 OR ((l_recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
301 AND ((l_recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
302 OR ((l_recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
303 AND ((l_recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
304 OR ((l_recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
305 AND ((l_recinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
306 OR ((l_recinfo.ATTRIBUTE_CATEGORY is null) AND (P_ATTRIBUTE_CATEGORY is null))))
307 THEN
308 NULL;
309 ELSE
310 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
311 APP_EXCEPTION.RAISE_EXCEPTION;
312 END IF;
313
314 FOR cur IN C_CROSS_REF_TL LOOP
315 IF (cur.BASELANG = 'Y') THEN
316 IF (((cur.DESCRIPTION = P_DESCRIPTION)
317 OR ((cur.DESCRIPTION is null) AND (P_DESCRIPTION is null))))
318 THEN
319 NULL;
320 ELSE
321 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
322 APP_EXCEPTION.RAISE_EXCEPTION;
323 END IF;
324 END IF;
325 END LOOP;
326
327 END LOCK_ROW;
328
329 PROCEDURE UPDATE_ROW (
330 P_CROSS_REFERENCE_ID IN NUMBER
331 ,P_SOURCE_SYSTEM_ID IN NUMBER
332 ,P_START_DATE_ACTIVE IN DATE
333 ,P_END_DATE_ACTIVE IN DATE
334 ,P_UOM_CODE IN VARCHAR2
335 ,P_REVISION_ID IN NUMBER
336 ,P_EPC_GTIN_SERIAL IN NUMBER
337 ,P_INVENTORY_ITEM_ID IN NUMBER
338 ,P_ORGANIZATION_ID IN NUMBER
339 ,P_CROSS_REFERENCE_TYPE IN VARCHAR2
340 ,P_CROSS_REFERENCE IN VARCHAR2
341 ,P_ORG_INDEPENDENT_FLAG IN VARCHAR2
342 ,P_REQUEST_ID IN NUMBER
343 ,P_ATTRIBUTE1 IN VARCHAR2
344 ,P_ATTRIBUTE2 IN VARCHAR2
345 ,P_ATTRIBUTE3 IN VARCHAR2
346 ,P_ATTRIBUTE4 IN VARCHAR2
347 ,P_ATTRIBUTE5 IN VARCHAR2
348 ,P_ATTRIBUTE6 IN VARCHAR2
349 ,P_ATTRIBUTE7 IN VARCHAR2
350 ,P_ATTRIBUTE8 IN VARCHAR2
351 ,P_ATTRIBUTE9 IN VARCHAR2
352 ,P_ATTRIBUTE10 IN VARCHAR2
353 ,P_ATTRIBUTE11 IN VARCHAR2
354 ,P_ATTRIBUTE12 IN VARCHAR2
355 ,P_ATTRIBUTE13 IN VARCHAR2
356 ,P_ATTRIBUTE14 IN VARCHAR2
357 ,P_ATTRIBUTE15 IN VARCHAR2
358 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2
359 ,P_DESCRIPTION IN VARCHAR2
360 ,P_LAST_UPDATE_DATE IN DATE
361 ,P_LAST_UPDATED_BY IN NUMBER
362 ,P_LAST_UPDATE_LOGIN IN NUMBER
363 ,X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER) IS
364 BEGIN
365 UPDATE MTL_CROSS_REFERENCES_B
366 SET
367 SOURCE_SYSTEM_ID = P_SOURCE_SYSTEM_ID
368 ,START_DATE_ACTIVE = P_START_DATE_ACTIVE
369 ,END_DATE_ACTIVE = P_END_DATE_ACTIVE
373 ,EPC_GTIN_SERIAL = NVL(P_EPC_GTIN_SERIAL,EPC_GTIN_SERIAL)
370 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
371 ,UOM_CODE = P_UOM_CODE
372 ,REVISION_ID = P_REVISION_ID
374 ,INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
375 ,ORGANIZATION_ID = P_ORGANIZATION_ID
376 ,CROSS_REFERENCE_TYPE = P_CROSS_REFERENCE_TYPE
377 ,CROSS_REFERENCE = P_CROSS_REFERENCE
378 ,ORG_INDEPENDENT_FLAG = P_ORG_INDEPENDENT_FLAG
379 ,REQUEST_ID = P_REQUEST_ID
380 ,ATTRIBUTE1 = P_ATTRIBUTE1
381 ,ATTRIBUTE2 = P_ATTRIBUTE2
382 ,ATTRIBUTE3 = P_ATTRIBUTE3
383 ,ATTRIBUTE4 = P_ATTRIBUTE4
384 ,ATTRIBUTE5 = P_ATTRIBUTE5
385 ,ATTRIBUTE6 = P_ATTRIBUTE6
386 ,ATTRIBUTE7 = P_ATTRIBUTE7
387 ,ATTRIBUTE8 = P_ATTRIBUTE8
388 ,ATTRIBUTE9 = P_ATTRIBUTE9
389 ,ATTRIBUTE10 = P_ATTRIBUTE10
390 ,ATTRIBUTE11 = P_ATTRIBUTE11
391 ,ATTRIBUTE12 = P_ATTRIBUTE12
392 ,ATTRIBUTE13 = P_ATTRIBUTE13
393 ,ATTRIBUTE14 = P_ATTRIBUTE14
394 ,ATTRIBUTE15 = P_ATTRIBUTE15
395 ,ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY
396 ,LAST_UPDATE_DATE = NVL(P_LAST_UPDATE_DATE,SYSDATE)
397 ,LAST_UPDATED_BY = NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID)
398 ,LAST_UPDATE_LOGIN = NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
399 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
400 RETURNING OBJECT_VERSION_NUMBER INTO X_OBJECT_VERSION_NUMBER;
401
402 IF (SQL%NOTFOUND) THEN
403 RAISE NO_DATA_FOUND;
404 END IF;
405
406 UPDATE MTL_CROSS_REFERENCES_TL
407 SET DESCRIPTION = P_DESCRIPTION,
408 LAST_UPDATE_DATE = NVL(P_LAST_UPDATE_DATE,SYSDATE),
409 LAST_UPDATED_BY = NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID),
410 LAST_UPDATE_LOGIN = NVL(P_LAST_UPDATED_BY,FND_GLOBAL.LOGIN_ID),
411 SOURCE_LANG = USERENV('LANG')
412 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID
413 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
414
415 IF (SQL%NOTFOUND) THEN
416 RAISE NO_DATA_FOUND;
417 END IF;
418
419 END UPDATE_ROW;
420
421 PROCEDURE DELETE_ROW (P_CROSS_REFERENCE_ID IN NUMBER) IS
422 BEGIN
423
424 DELETE FROM MTL_CROSS_REFERENCES_TL
425 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID;
426
427 IF (SQL%NOTFOUND) THEN
428 RAISE NO_DATA_FOUND;
429 END IF;
430
431 DELETE FROM MTL_CROSS_REFERENCES_B
432 WHERE CROSS_REFERENCE_ID = P_CROSS_REFERENCE_ID;
433
434 IF (SQL%NOTFOUND) THEN
435 RAISE NO_DATA_FOUND;
436 END IF;
437
438 END DELETE_ROW;
439
440 PROCEDURE ADD_LANGUAGE IS
441 BEGIN
442 DELETE MTL_CROSS_REFERENCES_TL T
443 WHERE NOT EXISTS (SELECT NULL
444 FROM MTL_CROSS_REFERENCES_B B
445 WHERE B.CROSS_REFERENCE_ID = T.CROSS_REFERENCE_ID);
446
447 UPDATE MTL_CROSS_REFERENCES_TL T
448 SET (DESCRIPTION) = (SELECT B.DESCRIPTION
449 FROM MTL_CROSS_REFERENCES_TL B
450 WHERE B.CROSS_REFERENCE_ID = T.CROSS_REFERENCE_ID
451 AND B.LANGUAGE = T.SOURCE_LANG)
452 WHERE (T.CROSS_REFERENCE_ID,T.LANGUAGE) IN
453 (SELECT SUBT.CROSS_REFERENCE_ID
454 ,SUBT.LANGUAGE
455 FROM MTL_CROSS_REFERENCES_TL SUBB,
456 MTL_CROSS_REFERENCES_TL SUBT
457 WHERE SUBB.CROSS_REFERENCE_ID = SUBT.CROSS_REFERENCE_ID
458 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
459 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
460 OR (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
461 OR (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)));
462
463 INSERT INTO MTL_CROSS_REFERENCES_TL (
464 LAST_UPDATE_LOGIN
465 ,DESCRIPTION
466 ,CREATION_DATE
467 ,CREATED_BY
468 ,LAST_UPDATE_DATE
469 ,LAST_UPDATED_BY
470 ,CROSS_REFERENCE_ID
471 ,LANGUAGE
472 ,SOURCE_LANG)
473 SELECT /*+ ORDERED */
474 B.LAST_UPDATE_LOGIN
475 ,B.DESCRIPTION
476 ,B.CREATION_DATE
477 ,B.CREATED_BY
478 ,B.LAST_UPDATE_DATE
479 ,B.LAST_UPDATED_BY
480 ,B.CROSS_REFERENCE_ID
481 ,L.LANGUAGE_CODE
482 ,B.SOURCE_LANG
483 FROM MTL_CROSS_REFERENCES_TL B,
484 FND_LANGUAGES L
485 WHERE L.INSTALLED_FLAG in ('I', 'B')
486 AND B.LANGUAGE = USERENV('LANG')
487 AND NOT EXISTS (SELECT NULL
488 FROM MTL_CROSS_REFERENCES_TL T
489 WHERE T.CROSS_REFERENCE_ID = B.CROSS_REFERENCE_ID
490 AND T.LANGUAGE = L.LANGUAGE_CODE);
491 END ADD_LANGUAGE;
492
493 END MTL_CROSS_REFERENCES_PKG;
[Home] [Help]