1 PACKAGE BODY GMD_ITEM_SUBSTITUTION_HDR_PKG as
2 /* $Header: GMDITSHB.pls 120.1 2005/07/15 06:08:50 kkillams noship $ */
3 PROCEDURE INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SUBSTITUTION_ID in NUMBER,
6 X_SUBSTITUTION_NAME in VARCHAR2,
7 X_SUBSTITUTION_VERSION in NUMBER,
8 X_SUBSTITUTION_STATUS in VARCHAR2,
9 X_ORIGINAL_INVENTORY_ITEM_ID in NUMBER,
10 X_ORIGINAL_UOM in VARCHAR2,
11 X_ORIGINAL_QTY in NUMBER,
12 X_PREFERENCE in NUMBER,
13 X_START_DATE in DATE,
14 X_END_DATE in DATE,
15 X_OWNER_ORGANIZATION_ID in NUMBER,
16 X_REPLACEMENT_UOM_TYPE in NUMBER,
17 X_ATTRIBUTE_CATEGORY in VARCHAR2,
18 X_ATTRIBUTE1 in VARCHAR2,
19 X_ATTRIBUTE2 in VARCHAR2,
20 X_ATTRIBUTE3 in VARCHAR2,
21 X_ATTRIBUTE4 in VARCHAR2,
22 X_ATTRIBUTE5 in VARCHAR2,
23 X_ATTRIBUTE6 in VARCHAR2,
24 X_ATTRIBUTE7 in VARCHAR2,
25 X_ATTRIBUTE8 in VARCHAR2,
26 X_ATTRIBUTE9 in VARCHAR2,
27 X_ATTRIBUTE10 in VARCHAR2,
28 X_ATTRIBUTE11 in VARCHAR2,
29 X_ATTRIBUTE12 in VARCHAR2,
30 X_ATTRIBUTE13 in VARCHAR2,
31 X_ATTRIBUTE14 in VARCHAR2,
32 X_ATTRIBUTE15 in VARCHAR2,
33 X_ATTRIBUTE16 in VARCHAR2,
34 X_ATTRIBUTE17 in VARCHAR2,
35 X_ATTRIBUTE18 in VARCHAR2,
36 X_ATTRIBUTE19 in VARCHAR2,
37 X_ATTRIBUTE20 in VARCHAR2,
38 X_ATTRIBUTE21 in VARCHAR2,
39 X_ATTRIBUTE22 in VARCHAR2,
40 X_ATTRIBUTE23 in VARCHAR2,
41 X_ATTRIBUTE24 in VARCHAR2,
42 X_ATTRIBUTE25 in VARCHAR2,
43 X_ATTRIBUTE26 in VARCHAR2,
44 X_ATTRIBUTE27 in VARCHAR2,
45 X_ATTRIBUTE28 in VARCHAR2,
46 X_ATTRIBUTE29 in VARCHAR2,
47 X_ATTRIBUTE30 in VARCHAR2,
48 X_SUBSTITUTION_DESCRIPTION in VARCHAR2,
49 X_CREATION_DATE in DATE,
50 X_CREATED_BY in NUMBER,
51 X_LAST_UPDATE_DATE in DATE,
52 X_LAST_UPDATED_BY in NUMBER,
53 X_LAST_UPDATE_LOGIN in NUMBER
54 ) IS
55 CURSOR C IS SELECT ROWID FROM GMD_ITEM_SUBSTITUTION_HDR_B
56 WHERE SUBSTITUTION_ID = X_SUBSTITUTION_ID
57 ;
58 BEGIN
59 INSERT INTO GMD_ITEM_SUBSTITUTION_HDR_B (
60 SUBSTITUTION_ID,
61 SUBSTITUTION_NAME,
62 SUBSTITUTION_VERSION,
63 SUBSTITUTION_STATUS,
64 ORIGINAL_INVENTORY_ITEM_ID,
65 ORIGINAL_UOM,
66 ORIGINAL_QTY,
67 PREFERENCE,
68 START_DATE,
69 END_DATE,
70 OWNER_ORGANIZATION_ID,
71 REPLACEMENT_UOM_TYPE,
72 ATTRIBUTE_CATEGORY,
73 ATTRIBUTE1,
74 ATTRIBUTE2,
75 ATTRIBUTE3,
76 ATTRIBUTE4,
77 ATTRIBUTE5,
78 ATTRIBUTE6,
79 ATTRIBUTE7,
80 ATTRIBUTE8,
81 ATTRIBUTE9,
82 ATTRIBUTE10,
83 ATTRIBUTE11,
84 ATTRIBUTE12,
85 ATTRIBUTE13,
86 ATTRIBUTE14,
87 ATTRIBUTE15,
88 ATTRIBUTE16,
89 ATTRIBUTE17,
90 ATTRIBUTE18,
91 ATTRIBUTE19,
92 ATTRIBUTE20,
93 ATTRIBUTE21,
94 ATTRIBUTE22,
95 ATTRIBUTE23,
96 ATTRIBUTE24,
97 ATTRIBUTE25,
98 ATTRIBUTE26,
99 ATTRIBUTE27,
100 ATTRIBUTE28,
101 ATTRIBUTE29,
102 ATTRIBUTE30,
103 CREATION_DATE,
104 CREATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN
108 ) VALUES (
109 X_SUBSTITUTION_ID,
110 X_SUBSTITUTION_NAME,
111 X_SUBSTITUTION_VERSION,
112 X_SUBSTITUTION_STATUS,
113 X_ORIGINAL_INVENTORY_ITEM_ID,
114 X_ORIGINAL_UOM,
115 X_ORIGINAL_QTY,
116 X_PREFERENCE,
117 X_START_DATE,
118 X_END_DATE,
119 X_OWNER_ORGANIZATION_ID,
120 X_REPLACEMENT_UOM_TYPE,
121 X_ATTRIBUTE_CATEGORY,
122 X_ATTRIBUTE1,
123 X_ATTRIBUTE2,
124 X_ATTRIBUTE3,
125 X_ATTRIBUTE4,
126 X_ATTRIBUTE5,
127 X_ATTRIBUTE6,
128 X_ATTRIBUTE7,
129 X_ATTRIBUTE8,
130 X_ATTRIBUTE9,
131 X_ATTRIBUTE10,
132 X_ATTRIBUTE11,
133 X_ATTRIBUTE12,
134 X_ATTRIBUTE13,
135 X_ATTRIBUTE14,
136 X_ATTRIBUTE15,
137 X_ATTRIBUTE16,
138 X_ATTRIBUTE17,
139 X_ATTRIBUTE18,
140 X_ATTRIBUTE19,
141 X_ATTRIBUTE20,
142 X_ATTRIBUTE21,
143 X_ATTRIBUTE22,
144 X_ATTRIBUTE23,
145 X_ATTRIBUTE24,
146 X_ATTRIBUTE25,
147 X_ATTRIBUTE26,
148 X_ATTRIBUTE27,
149 X_ATTRIBUTE28,
150 X_ATTRIBUTE29,
151 X_ATTRIBUTE30,
152 X_CREATION_DATE,
153 X_CREATED_BY,
154 X_LAST_UPDATE_DATE,
155 X_LAST_UPDATED_BY,
156 X_LAST_UPDATE_LOGIN
157 );
158
159 insert into GMD_ITEM_SUBSTITUTION_HDR_TL (
160 LAST_UPDATE_LOGIN,
161 LAST_UPDATE_DATE,
162 LAST_UPDATED_BY,
163 CREATED_BY,
164 CREATION_DATE,
165 SUBSTITUTION_ID,
166 SUBSTITUTION_DESCRIPTION,
167 LANGUAGE,
168 SOURCE_LANG
169 ) select
170 X_LAST_UPDATE_LOGIN,
171 X_LAST_UPDATE_DATE,
172 X_LAST_UPDATED_BY,
173 X_CREATED_BY,
174 X_CREATION_DATE,
175 X_SUBSTITUTION_ID,
176 X_SUBSTITUTION_DESCRIPTION,
177 L.LANGUAGE_CODE,
178 userenv('LANG')
179 from FND_LANGUAGES L
180 where L.INSTALLED_FLAG in ('I', 'B')
181 and not exists
182 (select NULL
183 from GMD_ITEM_SUBSTITUTION_HDR_TL T
184 where T.SUBSTITUTION_ID = X_SUBSTITUTION_ID
185 and T.LANGUAGE = L.LANGUAGE_CODE);
186
187 open c;
188 fetch c into X_ROWID;
189 if (c%notfound) then
190 close c;
191 raise no_data_found;
192 end if;
193 close c;
194
195 end INSERT_ROW;
196
197 procedure LOCK_ROW (
198 X_SUBSTITUTION_ID in NUMBER,
199 X_SUBSTITUTION_NAME in VARCHAR2,
200 X_SUBSTITUTION_VERSION in NUMBER,
201 X_SUBSTITUTION_STATUS in VARCHAR2,
202 X_ORIGINAL_INVENTORY_ITEM_ID in NUMBER,
203 X_ORIGINAL_UOM in VARCHAR2,
204 X_ORIGINAL_QTY in NUMBER,
205 X_PREFERENCE in NUMBER,
206 X_START_DATE in DATE,
207 X_END_DATE in DATE,
208 X_OWNER_ORGANIZATION_ID in NUMBER,
209 X_REPLACEMENT_UOM_TYPE in NUMBER,
210 X_ATTRIBUTE_CATEGORY in VARCHAR2,
211 X_ATTRIBUTE1 in VARCHAR2,
212 X_ATTRIBUTE2 in VARCHAR2,
213 X_ATTRIBUTE3 in VARCHAR2,
214 X_ATTRIBUTE4 in VARCHAR2,
215 X_ATTRIBUTE5 in VARCHAR2,
216 X_ATTRIBUTE6 in VARCHAR2,
217 X_ATTRIBUTE7 in VARCHAR2,
218 X_ATTRIBUTE8 in VARCHAR2,
219 X_ATTRIBUTE9 in VARCHAR2,
220 X_ATTRIBUTE10 in VARCHAR2,
221 X_ATTRIBUTE11 in VARCHAR2,
222 X_ATTRIBUTE12 in VARCHAR2,
223 X_ATTRIBUTE13 in VARCHAR2,
224 X_ATTRIBUTE14 in VARCHAR2,
225 X_ATTRIBUTE15 in VARCHAR2,
226 X_ATTRIBUTE16 in VARCHAR2,
227 X_ATTRIBUTE17 in VARCHAR2,
228 X_ATTRIBUTE18 in VARCHAR2,
229 X_ATTRIBUTE19 in VARCHAR2,
230 X_ATTRIBUTE20 in VARCHAR2,
231 X_ATTRIBUTE21 in VARCHAR2,
232 X_ATTRIBUTE22 in VARCHAR2,
233 X_ATTRIBUTE23 in VARCHAR2,
234 X_ATTRIBUTE24 in VARCHAR2,
235 X_ATTRIBUTE25 in VARCHAR2,
236 X_ATTRIBUTE26 in VARCHAR2,
237 X_ATTRIBUTE27 in VARCHAR2,
238 X_ATTRIBUTE28 in VARCHAR2,
239 X_ATTRIBUTE29 in VARCHAR2,
240 X_ATTRIBUTE30 in VARCHAR2,
241 X_SUBSTITUTION_DESCRIPTION in VARCHAR2
242 ) is
243 cursor c is select
244 SUBSTITUTION_NAME,
245 SUBSTITUTION_VERSION,
246 SUBSTITUTION_STATUS,
247 ORIGINAL_INVENTORY_ITEM_ID,
248 ORIGINAL_UOM,
249 ORIGINAL_QTY,
250 PREFERENCE,
251 START_DATE,
252 END_DATE,
253 OWNER_ORGANIZATION_ID,
254 REPLACEMENT_UOM_TYPE,
255 ATTRIBUTE_CATEGORY,
256 ATTRIBUTE1,
257 ATTRIBUTE2,
258 ATTRIBUTE3,
259 ATTRIBUTE4,
260 ATTRIBUTE5,
261 ATTRIBUTE6,
262 ATTRIBUTE7,
263 ATTRIBUTE8,
264 ATTRIBUTE9,
265 ATTRIBUTE10,
266 ATTRIBUTE11,
267 ATTRIBUTE12,
268 ATTRIBUTE13,
269 ATTRIBUTE14,
270 ATTRIBUTE15,
271 ATTRIBUTE16,
272 ATTRIBUTE17,
273 ATTRIBUTE18,
274 ATTRIBUTE19,
275 ATTRIBUTE20,
276 ATTRIBUTE21,
277 ATTRIBUTE22,
278 ATTRIBUTE23,
279 ATTRIBUTE24,
280 ATTRIBUTE25,
281 ATTRIBUTE26,
282 ATTRIBUTE27,
283 ATTRIBUTE28,
284 ATTRIBUTE29,
285 ATTRIBUTE30
286 from GMD_ITEM_SUBSTITUTION_HDR_B
287 where SUBSTITUTION_ID = X_SUBSTITUTION_ID
288 for update of SUBSTITUTION_ID nowait;
289 recinfo c%rowtype;
290
291 cursor c1 is select
292 SUBSTITUTION_DESCRIPTION,
293 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
294 from GMD_ITEM_SUBSTITUTION_HDR_TL
295 where SUBSTITUTION_ID = X_SUBSTITUTION_ID
296 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
297 for update of SUBSTITUTION_ID nowait;
298 begin
299 open c;
300 fetch c into recinfo;
301 if (c%notfound) then
302 close c;
303 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
304 app_exception.raise_exception;
305 end if;
306 close c;
307 if ( (recinfo.SUBSTITUTION_NAME = X_SUBSTITUTION_NAME)
308 AND (recinfo.SUBSTITUTION_VERSION = X_SUBSTITUTION_VERSION)
309 AND (recinfo.SUBSTITUTION_STATUS = X_SUBSTITUTION_STATUS)
310 AND (recinfo.ORIGINAL_INVENTORY_ITEM_ID = X_ORIGINAL_INVENTORY_ITEM_ID)
311 AND (recinfo.ORIGINAL_UOM = X_ORIGINAL_UOM)
312 AND (recinfo.ORIGINAL_QTY = X_ORIGINAL_QTY)
313 AND (recinfo.PREFERENCE = X_PREFERENCE)
314 AND (recinfo.START_DATE = X_START_DATE)
315 AND ((recinfo.END_DATE = X_END_DATE)
316 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
317 AND ((recinfo.OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID)
318 OR ((recinfo.OWNER_ORGANIZATION_ID is null) AND (X_OWNER_ORGANIZATION_ID is null)))
319 AND ((recinfo.REPLACEMENT_UOM_TYPE = X_REPLACEMENT_UOM_TYPE)
320 OR ((recinfo.REPLACEMENT_UOM_TYPE is null) AND (X_REPLACEMENT_UOM_TYPE is null)))
321 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
322 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
323 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
324 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
325 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
326 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
327 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
328 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
329 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
330 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
331 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
332 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
333 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
334 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
335 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
336 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
337 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
338 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
339 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
340 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
341 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
342 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
343 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
344 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
345 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
346 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
347 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
348 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
349 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
350 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
351 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
352 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
353 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
354 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
355 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
356 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
357 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
358 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
359 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
360 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
361 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
362 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
363 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
364 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
365 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
366 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
367 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
368 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
369 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
370 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
371 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
372 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
373 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
374 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
375 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
376 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
377 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
378 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
379 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
380 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
381 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
382 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
383 ) then
384 null;
385 else
386 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
387 app_exception.raise_exception;
388 end if;
389
390 for tlinfo in c1 loop
391 if (tlinfo.BASELANG = 'Y') then
392 if ( (tlinfo.SUBSTITUTION_DESCRIPTION = X_SUBSTITUTION_DESCRIPTION)
393 ) then
394 null;
395 else
396 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
397 app_exception.raise_exception;
398 end if;
399 end if;
400 end loop;
401 return;
402 end LOCK_ROW;
403
404 procedure UPDATE_ROW (
405 X_SUBSTITUTION_ID in NUMBER,
406 X_SUBSTITUTION_NAME in VARCHAR2,
407 X_SUBSTITUTION_VERSION in NUMBER,
408 X_SUBSTITUTION_STATUS in VARCHAR2,
409 X_ORIGINAL_INVENTORY_ITEM_ID in NUMBER,
410 X_ORIGINAL_UOM in VARCHAR2,
411 X_ORIGINAL_QTY in NUMBER,
415 X_OWNER_ORGANIZATION_ID in NUMBER,
412 X_PREFERENCE in NUMBER,
413 X_START_DATE in DATE,
414 X_END_DATE in DATE,
416 X_REPLACEMENT_UOM_TYPE in NUMBER,
417 X_ATTRIBUTE_CATEGORY in VARCHAR2,
418 X_ATTRIBUTE1 in VARCHAR2,
419 X_ATTRIBUTE2 in VARCHAR2,
420 X_ATTRIBUTE3 in VARCHAR2,
421 X_ATTRIBUTE4 in VARCHAR2,
422 X_ATTRIBUTE5 in VARCHAR2,
423 X_ATTRIBUTE6 in VARCHAR2,
424 X_ATTRIBUTE7 in VARCHAR2,
425 X_ATTRIBUTE8 in VARCHAR2,
426 X_ATTRIBUTE9 in VARCHAR2,
427 X_ATTRIBUTE10 in VARCHAR2,
428 X_ATTRIBUTE11 in VARCHAR2,
429 X_ATTRIBUTE12 in VARCHAR2,
430 X_ATTRIBUTE13 in VARCHAR2,
431 X_ATTRIBUTE14 in VARCHAR2,
432 X_ATTRIBUTE15 in VARCHAR2,
433 X_ATTRIBUTE16 in VARCHAR2,
434 X_ATTRIBUTE17 in VARCHAR2,
435 X_ATTRIBUTE18 in VARCHAR2,
436 X_ATTRIBUTE19 in VARCHAR2,
437 X_ATTRIBUTE20 in VARCHAR2,
438 X_ATTRIBUTE21 in VARCHAR2,
439 X_ATTRIBUTE22 in VARCHAR2,
440 X_ATTRIBUTE23 in VARCHAR2,
441 X_ATTRIBUTE24 in VARCHAR2,
442 X_ATTRIBUTE25 in VARCHAR2,
443 X_ATTRIBUTE26 in VARCHAR2,
444 X_ATTRIBUTE27 in VARCHAR2,
445 X_ATTRIBUTE28 in VARCHAR2,
446 X_ATTRIBUTE29 in VARCHAR2,
447 X_ATTRIBUTE30 in VARCHAR2,
448 X_SUBSTITUTION_DESCRIPTION in VARCHAR2,
449 X_LAST_UPDATE_DATE in DATE,
450 X_LAST_UPDATED_BY in NUMBER,
451 X_LAST_UPDATE_LOGIN in NUMBER
452 ) is
453 begin
454 update GMD_ITEM_SUBSTITUTION_HDR_B set
455 SUBSTITUTION_NAME = X_SUBSTITUTION_NAME,
456 SUBSTITUTION_VERSION = X_SUBSTITUTION_VERSION,
457 SUBSTITUTION_STATUS = X_SUBSTITUTION_STATUS,
458 ORIGINAL_INVENTORY_ITEM_ID = X_ORIGINAL_INVENTORY_ITEM_ID,
459 ORIGINAL_UOM = X_ORIGINAL_UOM,
460 ORIGINAL_QTY = X_ORIGINAL_QTY,
461 PREFERENCE = X_PREFERENCE,
462 START_DATE = X_START_DATE,
463 END_DATE = X_END_DATE,
464 OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID,
465 REPLACEMENT_UOM_TYPE = X_REPLACEMENT_UOM_TYPE,
466 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
467 ATTRIBUTE1 = X_ATTRIBUTE1,
468 ATTRIBUTE2 = X_ATTRIBUTE2,
469 ATTRIBUTE3 = X_ATTRIBUTE3,
470 ATTRIBUTE4 = X_ATTRIBUTE4,
471 ATTRIBUTE5 = X_ATTRIBUTE5,
472 ATTRIBUTE6 = X_ATTRIBUTE6,
473 ATTRIBUTE7 = X_ATTRIBUTE7,
474 ATTRIBUTE8 = X_ATTRIBUTE8,
475 ATTRIBUTE9 = X_ATTRIBUTE9,
476 ATTRIBUTE10 = X_ATTRIBUTE10,
477 ATTRIBUTE11 = X_ATTRIBUTE11,
478 ATTRIBUTE12 = X_ATTRIBUTE12,
479 ATTRIBUTE13 = X_ATTRIBUTE13,
480 ATTRIBUTE14 = X_ATTRIBUTE14,
481 ATTRIBUTE15 = X_ATTRIBUTE15,
482 ATTRIBUTE16 = X_ATTRIBUTE16,
483 ATTRIBUTE17 = X_ATTRIBUTE17,
484 ATTRIBUTE18 = X_ATTRIBUTE18,
485 ATTRIBUTE19 = X_ATTRIBUTE19,
486 ATTRIBUTE20 = X_ATTRIBUTE20,
487 ATTRIBUTE21 = X_ATTRIBUTE21,
488 ATTRIBUTE22 = X_ATTRIBUTE22,
489 ATTRIBUTE23 = X_ATTRIBUTE23,
490 ATTRIBUTE24 = X_ATTRIBUTE24,
491 ATTRIBUTE25 = X_ATTRIBUTE25,
492 ATTRIBUTE26 = X_ATTRIBUTE26,
493 ATTRIBUTE27 = X_ATTRIBUTE27,
494 ATTRIBUTE28 = X_ATTRIBUTE28,
495 ATTRIBUTE29 = X_ATTRIBUTE29,
496 ATTRIBUTE30 = X_ATTRIBUTE30,
497 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
498 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
499 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
500 where SUBSTITUTION_ID = X_SUBSTITUTION_ID;
501
502 if (sql%notfound) then
503 raise no_data_found;
504 end if;
505
506 update GMD_ITEM_SUBSTITUTION_HDR_TL set
507 SUBSTITUTION_DESCRIPTION = X_SUBSTITUTION_DESCRIPTION,
508 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
509 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
510 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
511 SOURCE_LANG = userenv('LANG')
512 where SUBSTITUTION_ID = X_SUBSTITUTION_ID
513 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
514
515 if (sql%notfound) then
516 raise no_data_found;
517 end if;
518 end UPDATE_ROW;
519
520 procedure DELETE_ROW (
521 X_SUBSTITUTION_ID in NUMBER
522 ) is
523 begin
524 delete from GMD_ITEM_SUBSTITUTION_HDR_TL
525 where SUBSTITUTION_ID = X_SUBSTITUTION_ID;
526
527 if (sql%notfound) then
528 raise no_data_found;
529 end if;
530
531 delete from GMD_ITEM_SUBSTITUTION_HDR_B
532 where SUBSTITUTION_ID = X_SUBSTITUTION_ID;
533
534 if (sql%notfound) then
535 raise no_data_found;
536 end if;
537 end DELETE_ROW;
538
539 procedure ADD_LANGUAGE
540 is
541 begin
542 delete from GMD_ITEM_SUBSTITUTION_HDR_TL T
543 where not exists
544 (select NULL
545 from GMD_ITEM_SUBSTITUTION_HDR_B B
546 where B.SUBSTITUTION_ID = T.SUBSTITUTION_ID
547 );
548
549 update GMD_ITEM_SUBSTITUTION_HDR_TL T set (
550 SUBSTITUTION_DESCRIPTION
551 ) = (select
552 B.SUBSTITUTION_DESCRIPTION
553 from GMD_ITEM_SUBSTITUTION_HDR_TL B
554 where B.SUBSTITUTION_ID = T.SUBSTITUTION_ID
555 and B.LANGUAGE = T.SOURCE_LANG)
556 where (
557 T.SUBSTITUTION_ID,
558 T.LANGUAGE
559 ) in (select
560 SUBT.SUBSTITUTION_ID,
561 SUBT.LANGUAGE
562 from GMD_ITEM_SUBSTITUTION_HDR_TL SUBB, GMD_ITEM_SUBSTITUTION_HDR_TL SUBT
563 where SUBB.SUBSTITUTION_ID = SUBT.SUBSTITUTION_ID
564 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
565 and (SUBB.SUBSTITUTION_DESCRIPTION <> SUBT.SUBSTITUTION_DESCRIPTION
566 ));
567
568 insert into GMD_ITEM_SUBSTITUTION_HDR_TL (
569 LAST_UPDATE_LOGIN,
570 LAST_UPDATE_DATE,
571 LAST_UPDATED_BY,
572 CREATED_BY,
573 CREATION_DATE,
574 SUBSTITUTION_ID,
575 SUBSTITUTION_DESCRIPTION,
576 LANGUAGE,
577 SOURCE_LANG
578 ) select /*+ ORDERED */
579 B.LAST_UPDATE_LOGIN,
580 B.LAST_UPDATE_DATE,
581 B.LAST_UPDATED_BY,
582 B.CREATED_BY,
583 B.CREATION_DATE,
584 B.SUBSTITUTION_ID,
585 B.SUBSTITUTION_DESCRIPTION,
586 L.LANGUAGE_CODE,
587 B.SOURCE_LANG
588 from GMD_ITEM_SUBSTITUTION_HDR_TL B, FND_LANGUAGES L
589 where L.INSTALLED_FLAG in ('I', 'B')
590 and B.LANGUAGE = userenv('LANG')
591 and not exists
592 (select NULL
593 from GMD_ITEM_SUBSTITUTION_HDR_TL T
594 where T.SUBSTITUTION_ID = B.SUBSTITUTION_ID
595 and T.LANGUAGE = L.LANGUAGE_CODE);
596 end ADD_LANGUAGE;
597
598 end GMD_ITEM_SUBSTITUTION_HDR_PKG;
[Home] [Help]