1 package body AHL_ITEM_ASSOCIATIONS_H_PKG as
2 /* $Header: AHLLIAHB.pls 115.3 2003/08/29 18:25:03 cxcheng noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ITEM_ASSOCIATION_H_ID in NUMBER,
6 X_ITEM_ASSOCIATION_ID in NUMBER,
7 X_ITEM_GROUP_ID in NUMBER,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_INVENTORY_ITEM_ID in NUMBER,
10 X_INVENTORY_ORG_ID in NUMBER,
11 X_PRIORITY in NUMBER,
12 X_TRANSACTION_DATE in DATE,
13 X_ACTION in VARCHAR2,
14 X_SOURCE_ITEM_ASSOCIATION_ID in NUMBER,
15 X_VERSION_NUMBER in NUMBER,
16 X_UOM_CODE in VARCHAR2,
17 X_QUANTITY in NUMBER,
18 X_REVISION in VARCHAR2,
19 X_INTERCHANGE_TYPE_CODE in VARCHAR2,
20 X_ATTRIBUTE_CATEGORY in VARCHAR2,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_ATTRIBUTE11 in VARCHAR2,
32 X_ATTRIBUTE12 in VARCHAR2,
33 X_ATTRIBUTE13 in VARCHAR2,
34 X_ATTRIBUTE14 in VARCHAR2,
35 X_ATTRIBUTE15 in VARCHAR2,
36 X_INTERCHANGE_REASON in VARCHAR2,
37 X_CREATION_DATE in DATE,
38 X_CREATED_BY in NUMBER,
39 X_LAST_UPDATE_DATE in DATE,
40 X_LAST_UPDATED_BY in NUMBER,
41 X_LAST_UPDATE_LOGIN in NUMBER
42 ) is
43 cursor C is select ROWID from AHL_ITEM_ASSOCIATIONS_B_H
44 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID
45 ;
46 begin
47 insert into AHL_ITEM_ASSOCIATIONS_B_H (
48 ITEM_ASSOCIATION_H_ID,
49 ITEM_ASSOCIATION_ID,
50 ITEM_GROUP_ID,
51 OBJECT_VERSION_NUMBER,
52 INVENTORY_ITEM_ID,
53 INVENTORY_ORG_ID,
54 PRIORITY,
55 TRANSACTION_DATE,
56 ACTION,
57 SOURCE_ITEM_ASSOCIATION_ID,
58 VERSION_NUMBER,
59 UOM_CODE,
60 QUANTITY,
61 REVISION,
62 INTERCHANGE_TYPE_CODE,
63 ATTRIBUTE_CATEGORY,
64 ATTRIBUTE1,
65 ATTRIBUTE2,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ATTRIBUTE7,
71 ATTRIBUTE8,
72 ATTRIBUTE9,
73 ATTRIBUTE10,
74 ATTRIBUTE11,
75 ATTRIBUTE12,
76 ATTRIBUTE13,
77 ATTRIBUTE14,
78 ATTRIBUTE15,
79 CREATION_DATE,
80 CREATED_BY,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_LOGIN
84 ) values (
85 X_ITEM_ASSOCIATION_H_ID,
86 X_ITEM_ASSOCIATION_ID,
87 X_ITEM_GROUP_ID,
88 X_OBJECT_VERSION_NUMBER,
89 X_INVENTORY_ITEM_ID,
90 X_INVENTORY_ORG_ID,
91 X_PRIORITY,
92 X_TRANSACTION_DATE,
93 X_ACTION,
94 X_SOURCE_ITEM_ASSOCIATION_ID,
95 X_VERSION_NUMBER,
96 X_UOM_CODE,
97 X_QUANTITY,
98 X_REVISION,
99 X_INTERCHANGE_TYPE_CODE,
100 X_ATTRIBUTE_CATEGORY,
101 X_ATTRIBUTE1,
102 X_ATTRIBUTE2,
103 X_ATTRIBUTE3,
104 X_ATTRIBUTE4,
105 X_ATTRIBUTE5,
106 X_ATTRIBUTE6,
107 X_ATTRIBUTE7,
108 X_ATTRIBUTE8,
109 X_ATTRIBUTE9,
110 X_ATTRIBUTE10,
111 X_ATTRIBUTE11,
112 X_ATTRIBUTE12,
113 X_ATTRIBUTE13,
114 X_ATTRIBUTE14,
115 X_ATTRIBUTE15,
116 X_CREATION_DATE,
117 X_CREATED_BY,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_LAST_UPDATE_LOGIN
121 );
122
123 insert into AHL_ITEM_ASSOCIATIONS_TL_H (
124 ITEM_ASSOCIATION_H_ID,
125 LAST_UPDATE_DATE,
126 INTERCHANGE_REASON,
127 LAST_UPDATED_BY,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_LOGIN,
131 LANGUAGE,
132 SOURCE_LANG
133 ) select
134 X_ITEM_ASSOCIATION_H_ID,
135 X_LAST_UPDATE_DATE,
136 X_INTERCHANGE_REASON,
137 X_LAST_UPDATED_BY,
138 X_CREATION_DATE,
139 X_CREATED_BY,
140 X_LAST_UPDATE_LOGIN,
141 L.LANGUAGE_CODE,
142 userenv('LANG')
143 from FND_LANGUAGES L
144 where L.INSTALLED_FLAG in ('I', 'B')
145 and not exists
146 (select NULL
147 from AHL_ITEM_ASSOCIATIONS_TL_H T
148 where T.ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID
149 and T.LANGUAGE = L.LANGUAGE_CODE);
150
151 open c;
152 fetch c into X_ROWID;
153 if (c%notfound) then
154 close c;
155 raise no_data_found;
156 end if;
157 close c;
158
159 end INSERT_ROW;
160
161 procedure LOCK_ROW (
162 X_ITEM_ASSOCIATION_H_ID in NUMBER,
163 X_ITEM_ASSOCIATION_ID in NUMBER,
164 X_ITEM_GROUP_ID in NUMBER,
165 X_OBJECT_VERSION_NUMBER in NUMBER,
166 X_INVENTORY_ITEM_ID in NUMBER,
167 X_INVENTORY_ORG_ID in NUMBER,
168 X_PRIORITY in NUMBER,
169 X_TRANSACTION_DATE in DATE,
170 X_ACTION in VARCHAR2,
171 X_SOURCE_ITEM_ASSOCIATION_ID in NUMBER,
172 X_VERSION_NUMBER in NUMBER,
173 X_UOM_CODE in VARCHAR2,
174 X_QUANTITY in NUMBER,
175 X_REVISION in VARCHAR2,
176 X_INTERCHANGE_TYPE_CODE in VARCHAR2,
177 X_ATTRIBUTE_CATEGORY in VARCHAR2,
178 X_ATTRIBUTE1 in VARCHAR2,
179 X_ATTRIBUTE2 in VARCHAR2,
180 X_ATTRIBUTE3 in VARCHAR2,
181 X_ATTRIBUTE4 in VARCHAR2,
182 X_ATTRIBUTE5 in VARCHAR2,
183 X_ATTRIBUTE6 in VARCHAR2,
184 X_ATTRIBUTE7 in VARCHAR2,
185 X_ATTRIBUTE8 in VARCHAR2,
186 X_ATTRIBUTE9 in VARCHAR2,
187 X_ATTRIBUTE10 in VARCHAR2,
188 X_ATTRIBUTE11 in VARCHAR2,
189 X_ATTRIBUTE12 in VARCHAR2,
190 X_ATTRIBUTE13 in VARCHAR2,
191 X_ATTRIBUTE14 in VARCHAR2,
192 X_ATTRIBUTE15 in VARCHAR2,
193 X_INTERCHANGE_REASON in VARCHAR2
194 ) is
195 cursor c is select
196 ITEM_ASSOCIATION_ID,
197 ITEM_GROUP_ID,
198 OBJECT_VERSION_NUMBER,
199 INVENTORY_ITEM_ID,
200 INVENTORY_ORG_ID,
201 PRIORITY,
202 TRANSACTION_DATE,
203 ACTION,
204 SOURCE_ITEM_ASSOCIATION_ID,
205 VERSION_NUMBER,
206 UOM_CODE,
207 QUANTITY,
208 REVISION,
209 INTERCHANGE_TYPE_CODE,
210 ATTRIBUTE_CATEGORY,
211 ATTRIBUTE1,
212 ATTRIBUTE2,
213 ATTRIBUTE3,
214 ATTRIBUTE4,
215 ATTRIBUTE5,
216 ATTRIBUTE6,
217 ATTRIBUTE7,
218 ATTRIBUTE8,
219 ATTRIBUTE9,
220 ATTRIBUTE10,
221 ATTRIBUTE11,
222 ATTRIBUTE12,
223 ATTRIBUTE13,
224 ATTRIBUTE14,
225 ATTRIBUTE15
226 from AHL_ITEM_ASSOCIATIONS_B_H
227 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID
228 for update of ITEM_ASSOCIATION_H_ID nowait;
229 recinfo c%rowtype;
230
231 cursor c1 is select
232 INTERCHANGE_REASON,
233 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
234 from AHL_ITEM_ASSOCIATIONS_TL_H
235 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID
236 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
237 for update of ITEM_ASSOCIATION_H_ID nowait;
238 begin
239 open c;
240 fetch c into recinfo;
241 if (c%notfound) then
242 close c;
243 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
244 app_exception.raise_exception;
245 end if;
246 close c;
247 if ( (recinfo.ITEM_ASSOCIATION_ID = X_ITEM_ASSOCIATION_ID)
248 AND (recinfo.ITEM_GROUP_ID = X_ITEM_GROUP_ID)
249 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
250 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
251 AND (recinfo.INVENTORY_ORG_ID = X_INVENTORY_ORG_ID)
252 AND (recinfo.PRIORITY = X_PRIORITY)
253 AND (recinfo.TRANSACTION_DATE = X_TRANSACTION_DATE)
254 AND ((recinfo.ACTION = X_ACTION)
255 OR ((recinfo.ACTION is null) AND (X_ACTION is null)))
256 AND ((recinfo.SOURCE_ITEM_ASSOCIATION_ID = X_SOURCE_ITEM_ASSOCIATION_ID)
257 OR ((recinfo.SOURCE_ITEM_ASSOCIATION_ID is null) AND (X_SOURCE_ITEM_ASSOCIATION_ID is null)))
258 AND ((recinfo.VERSION_NUMBER = X_VERSION_NUMBER)
259 OR ((recinfo.VERSION_NUMBER is null) AND (X_VERSION_NUMBER is null)))
260 AND ((recinfo.UOM_CODE = X_UOM_CODE)
261 OR ((recinfo.UOM_CODE is null) AND (X_UOM_CODE is null)))
262 AND ((recinfo.QUANTITY = X_QUANTITY)
263 OR ((recinfo.QUANTITY is null) AND (X_QUANTITY is null)))
264 AND ((recinfo.REVISION = X_REVISION)
265 OR ((recinfo.REVISION is null) AND (X_REVISION is null)))
266 AND ((recinfo.INTERCHANGE_TYPE_CODE = X_INTERCHANGE_TYPE_CODE)
267 OR ((recinfo.INTERCHANGE_TYPE_CODE is null) AND (X_INTERCHANGE_TYPE_CODE is null)))
268 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
269 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
270 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
271 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
272 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
273 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
274 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
275 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
276 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
277 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
278 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
279 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
280 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
281 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
282 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
283 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
284 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
285 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
286 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
287 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
288 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
289 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
290 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
291 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
292 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
293 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
294 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
295 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
296 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
297 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
298 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
299 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
300 ) then
301 null;
302 else
303 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
304 app_exception.raise_exception;
305 end if;
306
307 for tlinfo in c1 loop
308 if (tlinfo.BASELANG = 'Y') then
309 if ( ((tlinfo.INTERCHANGE_REASON = X_INTERCHANGE_REASON)
310 OR ((tlinfo.INTERCHANGE_REASON is null) AND (X_INTERCHANGE_REASON is null)))
311 ) then
312 null;
313 else
314 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
315 app_exception.raise_exception;
316 end if;
317 end if;
318 end loop;
319 return;
320 end LOCK_ROW;
321
322 procedure UPDATE_ROW (
323 X_ITEM_ASSOCIATION_H_ID in NUMBER,
324 X_ITEM_ASSOCIATION_ID in NUMBER,
325 X_ITEM_GROUP_ID in NUMBER,
326 X_OBJECT_VERSION_NUMBER in NUMBER,
327 X_INVENTORY_ITEM_ID in NUMBER,
328 X_INVENTORY_ORG_ID in NUMBER,
329 X_PRIORITY in NUMBER,
330 X_TRANSACTION_DATE in DATE,
331 X_ACTION in VARCHAR2,
332 X_SOURCE_ITEM_ASSOCIATION_ID in NUMBER,
333 X_VERSION_NUMBER in NUMBER,
334 X_UOM_CODE in VARCHAR2,
335 X_QUANTITY in NUMBER,
336 X_REVISION in VARCHAR2,
337 X_INTERCHANGE_TYPE_CODE in VARCHAR2,
338 X_ATTRIBUTE_CATEGORY in VARCHAR2,
339 X_ATTRIBUTE1 in VARCHAR2,
340 X_ATTRIBUTE2 in VARCHAR2,
341 X_ATTRIBUTE3 in VARCHAR2,
342 X_ATTRIBUTE4 in VARCHAR2,
343 X_ATTRIBUTE5 in VARCHAR2,
344 X_ATTRIBUTE6 in VARCHAR2,
345 X_ATTRIBUTE7 in VARCHAR2,
346 X_ATTRIBUTE8 in VARCHAR2,
347 X_ATTRIBUTE9 in VARCHAR2,
348 X_ATTRIBUTE10 in VARCHAR2,
349 X_ATTRIBUTE11 in VARCHAR2,
350 X_ATTRIBUTE12 in VARCHAR2,
351 X_ATTRIBUTE13 in VARCHAR2,
352 X_ATTRIBUTE14 in VARCHAR2,
353 X_ATTRIBUTE15 in VARCHAR2,
354 X_INTERCHANGE_REASON in VARCHAR2,
355 X_LAST_UPDATE_DATE in DATE,
356 X_LAST_UPDATED_BY in NUMBER,
357 X_LAST_UPDATE_LOGIN in NUMBER
358 ) is
359 begin
360 update AHL_ITEM_ASSOCIATIONS_B_H set
361 ITEM_ASSOCIATION_ID = X_ITEM_ASSOCIATION_ID,
362 ITEM_GROUP_ID = X_ITEM_GROUP_ID,
363 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
364 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
365 INVENTORY_ORG_ID = X_INVENTORY_ORG_ID,
366 PRIORITY = X_PRIORITY,
367 TRANSACTION_DATE = X_TRANSACTION_DATE,
368 ACTION = X_ACTION,
369 SOURCE_ITEM_ASSOCIATION_ID = X_SOURCE_ITEM_ASSOCIATION_ID,
370 VERSION_NUMBER = X_VERSION_NUMBER,
371 UOM_CODE = X_UOM_CODE,
372 QUANTITY = X_QUANTITY,
373 REVISION = X_REVISION,
374 INTERCHANGE_TYPE_CODE = X_INTERCHANGE_TYPE_CODE,
375 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
376 ATTRIBUTE1 = X_ATTRIBUTE1,
377 ATTRIBUTE2 = X_ATTRIBUTE2,
378 ATTRIBUTE3 = X_ATTRIBUTE3,
379 ATTRIBUTE4 = X_ATTRIBUTE4,
380 ATTRIBUTE5 = X_ATTRIBUTE5,
381 ATTRIBUTE6 = X_ATTRIBUTE6,
382 ATTRIBUTE7 = X_ATTRIBUTE7,
383 ATTRIBUTE8 = X_ATTRIBUTE8,
384 ATTRIBUTE9 = X_ATTRIBUTE9,
385 ATTRIBUTE10 = X_ATTRIBUTE10,
386 ATTRIBUTE11 = X_ATTRIBUTE11,
387 ATTRIBUTE12 = X_ATTRIBUTE12,
388 ATTRIBUTE13 = X_ATTRIBUTE13,
389 ATTRIBUTE14 = X_ATTRIBUTE14,
390 ATTRIBUTE15 = X_ATTRIBUTE15,
394 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID;
391 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
392 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
393 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
395
396 if (sql%notfound) then
397 raise no_data_found;
398 end if;
399
400 update AHL_ITEM_ASSOCIATIONS_TL_H set
401 INTERCHANGE_REASON = X_INTERCHANGE_REASON,
402 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
403 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
404 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
405 SOURCE_LANG = userenv('LANG')
406 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID
407 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
408
409 if (sql%notfound) then
410 raise no_data_found;
411 end if;
412 end UPDATE_ROW;
413
414 procedure DELETE_ROW (
415 X_ITEM_ASSOCIATION_H_ID in NUMBER
416 ) is
417 begin
418 delete from AHL_ITEM_ASSOCIATIONS_TL_H
419 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID;
420
421 if (sql%notfound) then
422 raise no_data_found;
423 end if;
424
425 delete from AHL_ITEM_ASSOCIATIONS_B_H
426 where ITEM_ASSOCIATION_H_ID = X_ITEM_ASSOCIATION_H_ID;
427
428 if (sql%notfound) then
429 raise no_data_found;
430 end if;
431 end DELETE_ROW;
432
433 procedure ADD_LANGUAGE
434 is
435 begin
436 delete from AHL_ITEM_ASSOCIATIONS_TL_H T
437 where not exists
438 (select NULL
439 from AHL_ITEM_ASSOCIATIONS_B_H B
440 where B.ITEM_ASSOCIATION_H_ID = T.ITEM_ASSOCIATION_H_ID
441 );
442
443 update AHL_ITEM_ASSOCIATIONS_TL_H T set (
444 INTERCHANGE_REASON
445 ) = (select
446 B.INTERCHANGE_REASON
447 from AHL_ITEM_ASSOCIATIONS_TL_H B
448 where B.ITEM_ASSOCIATION_H_ID = T.ITEM_ASSOCIATION_H_ID
449 and B.LANGUAGE = T.SOURCE_LANG)
450 where (
451 T.ITEM_ASSOCIATION_H_ID,
452 T.LANGUAGE
453 ) in (select
454 SUBT.ITEM_ASSOCIATION_H_ID,
455 SUBT.LANGUAGE
456 from AHL_ITEM_ASSOCIATIONS_TL_H SUBB, AHL_ITEM_ASSOCIATIONS_TL_H SUBT
457 where SUBB.ITEM_ASSOCIATION_H_ID = SUBT.ITEM_ASSOCIATION_H_ID
458 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
459 and (SUBB.INTERCHANGE_REASON <> SUBT.INTERCHANGE_REASON
460 or (SUBB.INTERCHANGE_REASON is null and SUBT.INTERCHANGE_REASON is not null)
461 or (SUBB.INTERCHANGE_REASON is not null and SUBT.INTERCHANGE_REASON is null)
462 ));
463
464 insert into AHL_ITEM_ASSOCIATIONS_TL_H (
465 ITEM_ASSOCIATION_H_ID,
466 LAST_UPDATE_DATE,
467 INTERCHANGE_REASON,
468 LAST_UPDATED_BY,
469 CREATION_DATE,
470 CREATED_BY,
471 LAST_UPDATE_LOGIN,
472 LANGUAGE,
473 SOURCE_LANG
474 ) select
475 B.ITEM_ASSOCIATION_H_ID,
476 B.LAST_UPDATE_DATE,
477 B.INTERCHANGE_REASON,
478 B.LAST_UPDATED_BY,
479 B.CREATION_DATE,
480 B.CREATED_BY,
481 B.LAST_UPDATE_LOGIN,
482 L.LANGUAGE_CODE,
483 B.SOURCE_LANG
484 from AHL_ITEM_ASSOCIATIONS_TL_H B, FND_LANGUAGES L
485 where L.INSTALLED_FLAG in ('I', 'B')
486 and B.LANGUAGE = userenv('LANG')
487 and not exists
488 (select NULL
489 from AHL_ITEM_ASSOCIATIONS_TL_H T
490 where T.ITEM_ASSOCIATION_H_ID = B.ITEM_ASSOCIATION_H_ID
491 and T.LANGUAGE = L.LANGUAGE_CODE);
492 end ADD_LANGUAGE;
493
494 end AHL_ITEM_ASSOCIATIONS_H_PKG;