[Home] [Help]
PACKAGE BODY: APPS.MTL_MATERIAL_STATUSES_PKG
Source
1 package body MTL_MATERIAL_STATUSES_PKG as
2 /* $Header: INVMSMLB.pls 120.3 2008/02/15 10:22:17 aambulka ship $ */
3
4 --Bugfix 2396883. This flag will determine where the update failed.
5 X_UPDATE_FLAG VARCHAR2(1) := 'N';
6
7 procedure INSERT_ROW (
8 X_ROWID in out NOCOPY VARCHAR2,
9 X_STATUS_ID in NUMBER,
10 X_ATTRIBUTE15 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_LOCATOR_CONTROL in NUMBER,
24 X_LOT_CONTROL in NUMBER,
25 X_SERIAL_CONTROL in NUMBER,
26 X_ONHAND_CONTROL in NUMBER, -- Onhand Material Status Support Bug #6633612
27 X_ZONE_CONTROL in NUMBER,
28 X_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE14 in VARCHAR2,
30 X_REQUEST_ID in NUMBER,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2,
32 X_ENABLED_FLAG in NUMBER,
33 X_STATUS_CODE in VARCHAR2,
34 X_DESCRIPTION in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER,
40 X_LPN_CONTROL in NUMBER,
41 --INVCONV KKILLAMS
42 X_inventory_atp_code IN NUMBER,
43 X_reservable_type IN NUMBER,
44 X_availability_type IN NUMBER
45 --END INVCONV KKILLAMS
46 ) is
47 cursor C is select ROWID from MTL_MATERIAL_STATUSES_B
48 where STATUS_ID = X_STATUS_ID
49 ;
50 begin
51 insert into MTL_MATERIAL_STATUSES_B (
52 ATTRIBUTE15,
53 ATTRIBUTE2,
54 ATTRIBUTE3,
55 ATTRIBUTE4,
56 ATTRIBUTE5,
57 ATTRIBUTE6,
58 ATTRIBUTE7,
59 ATTRIBUTE8,
60 ATTRIBUTE9,
61 ATTRIBUTE10,
62 ATTRIBUTE11,
63 ATTRIBUTE12,
64 ATTRIBUTE13,
65 LOCATOR_CONTROL,
66 LOT_CONTROL,
67 SERIAL_CONTROL,
68 ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
69 STATUS_ID,
70 ZONE_CONTROL,
71 ATTRIBUTE1,
72 ATTRIBUTE14,
73 REQUEST_ID,
74 ATTRIBUTE_CATEGORY,
75 ENABLED_FLAG,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN,
81 LPN_CONTROL,
82 --INVCONV KKILLAMS
83 INVENTORY_ATP_CODE,
84 RESERVABLE_TYPE,
85 AVAILABILITY_TYPE
86 --END INVCONV KKILLAMS
87 ) values (
88 X_ATTRIBUTE15,
89 X_ATTRIBUTE2,
90 X_ATTRIBUTE3,
91 X_ATTRIBUTE4,
92 X_ATTRIBUTE5,
93 X_ATTRIBUTE6,
94 X_ATTRIBUTE7,
95 X_ATTRIBUTE8,
96 X_ATTRIBUTE9,
97 X_ATTRIBUTE10,
98 X_ATTRIBUTE11,
99 X_ATTRIBUTE12,
100 X_ATTRIBUTE13,
101 X_LOCATOR_CONTROL,
102 X_LOT_CONTROL,
103 X_SERIAL_CONTROL,
104 X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
105 X_STATUS_ID,
106 X_ZONE_CONTROL,
107 X_ATTRIBUTE1,
108 X_ATTRIBUTE14,
109 X_REQUEST_ID,
110 X_ATTRIBUTE_CATEGORY,
111 X_ENABLED_FLAG,
112 X_CREATION_DATE,
113 X_CREATED_BY,
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 X_LAST_UPDATE_LOGIN,
117 X_LPN_CONTROL,
118 --INVCONV KKILLAMS
119 X_INVENTORY_ATP_CODE,
120 X_RESERVABLE_TYPE,
121 X_AVAILABILITY_TYPE
122 --END INVCONV KKILLAMS
123 );
124
125 insert into MTL_MATERIAL_STATUSES_TL (
126 LAST_UPDATE_DATE,
127 CREATED_BY,
128 CREATION_DATE,
129 LAST_UPDATE_LOGIN,
130 STATUS_CODE,
131 DESCRIPTION,
132 STATUS_ID,
133 LAST_UPDATED_BY,
134 LANGUAGE,
135 SOURCE_LANG
136 ) select
137 X_LAST_UPDATE_DATE,
138 X_CREATED_BY,
139 X_CREATION_DATE,
140 X_LAST_UPDATE_LOGIN,
141 X_STATUS_CODE,
142 X_DESCRIPTION,
143 X_STATUS_ID,
144 X_LAST_UPDATED_BY,
145 L.LANGUAGE_CODE,
146 userenv('LANG')
147 from FND_LANGUAGES L
148 where L.INSTALLED_FLAG in ('I', 'B')
149 and not exists
150 (select NULL
151 from MTL_MATERIAL_STATUSES_TL T
152 where T.STATUS_ID = X_STATUS_ID
153 and T.LANGUAGE = L.LANGUAGE_CODE);
154
155 open c;
156 fetch c into X_ROWID;
157 if (c%notfound) then
158 close c;
159 raise no_data_found;
160 end if;
161 close c;
162 end INSERT_ROW;
163
164 -- Bugfix 2396883
165 procedure INSERT_TL_ROW (
166 X_ROWID in out NOCOPY VARCHAR2,
167 X_STATUS_ID in NUMBER,
168 X_STATUS_CODE in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2,
170 X_CREATION_DATE in DATE,
171 X_CREATED_BY in NUMBER,
172 X_LAST_UPDATE_DATE in DATE,
173 X_LAST_UPDATED_BY in NUMBER,
174 X_LAST_UPDATE_LOGIN in NUMBER)
175 IS
176 cursor C is select ROWID from MTL_MATERIAL_STATUSES_TL
177 where STATUS_ID = X_STATUS_ID
178 ;
179 BEGIN
180 insert into MTL_MATERIAL_STATUSES_TL (
181 LAST_UPDATE_DATE,
182 CREATED_BY,
183 CREATION_DATE,
184 LAST_UPDATE_LOGIN,
185 STATUS_CODE,
186 DESCRIPTION,
187 STATUS_ID,
188 LAST_UPDATED_BY,
189 LANGUAGE,
190 SOURCE_LANG
191 ) select
192 X_LAST_UPDATE_DATE,
193 X_CREATED_BY,
194 X_CREATION_DATE,
195 X_LAST_UPDATE_LOGIN,
196 X_STATUS_CODE,
197 X_DESCRIPTION,
198 X_STATUS_ID,
199 X_LAST_UPDATED_BY,
200 L.LANGUAGE_CODE,
201 userenv('LANG')
202 from FND_LANGUAGES L
203 where L.INSTALLED_FLAG in ('I', 'B')
204 and not exists
205 (select NULL
206 from MTL_MATERIAL_STATUSES_TL T
207 where T.STATUS_ID = X_STATUS_ID
208 and T.LANGUAGE = L.LANGUAGE_CODE);
209
210 open c;
211 fetch c into X_ROWID;
212 if (c%notfound) then
213 close c;
214 raise no_data_found;
215 end if;
216 close c;
217
218 end INSERT_TL_ROW;
219
220 procedure LOCK_ROW (
221 X_STATUS_ID in NUMBER,
222 X_ATTRIBUTE15 in VARCHAR2,
223 X_ATTRIBUTE2 in VARCHAR2,
224 X_ATTRIBUTE3 in VARCHAR2,
225 X_ATTRIBUTE4 in VARCHAR2,
226 X_ATTRIBUTE5 in VARCHAR2,
227 X_ATTRIBUTE6 in VARCHAR2,
228 X_ATTRIBUTE7 in VARCHAR2,
229 X_ATTRIBUTE8 in VARCHAR2,
230 X_ATTRIBUTE9 in VARCHAR2,
231 X_ATTRIBUTE10 in VARCHAR2,
232 X_ATTRIBUTE11 in VARCHAR2,
233 X_ATTRIBUTE12 in VARCHAR2,
234 X_ATTRIBUTE13 in VARCHAR2,
235 X_LOCATOR_CONTROL in NUMBER,
236 X_LOT_CONTROL in NUMBER,
237 X_SERIAL_CONTROL in NUMBER,
238 X_ONHAND_CONTROL in NUMBER, -- Onhand Material Status Support Bug #6633612
239 X_ZONE_CONTROL in NUMBER,
240 X_ATTRIBUTE1 in VARCHAR2,
241 X_ATTRIBUTE14 in VARCHAR2,
242 X_REQUEST_ID in NUMBER,
243 X_ATTRIBUTE_CATEGORY in VARCHAR2,
244 X_ENABLED_FLAG in NUMBER,
245 X_STATUS_CODE in VARCHAR2,
246 X_DESCRIPTION in VARCHAR2,
247 X_LPN_CONTROL in NUMBER,
248 --INVCONV KKILLAMS
249 X_INVENTORY_ATP_CODE IN NUMBER,
250 X_RESERVABLE_TYPE IN NUMBER,
251 X_AVAILABILITY_TYPE IN NUMBER
252 --END INVCONV KKILLAMS
253 ) is
254 cursor c is select
255 ATTRIBUTE15,
256 ATTRIBUTE2,
257 ATTRIBUTE3,
258 ATTRIBUTE4,
259 ATTRIBUTE5,
260 ATTRIBUTE6,
261 ATTRIBUTE7,
262 ATTRIBUTE8,
263 ATTRIBUTE9,
264 ATTRIBUTE10,
265 ATTRIBUTE11,
266 ATTRIBUTE12,
267 ATTRIBUTE13,
268 LOCATOR_CONTROL,
269 LOT_CONTROL,
270 SERIAL_CONTROL,
271 ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
272 ZONE_CONTROL,
273 ATTRIBUTE1,
274 ATTRIBUTE14,
275 REQUEST_ID,
276 ATTRIBUTE_CATEGORY,
277 ENABLED_FLAG,
278 LPN_CONTROL,
279 --INVCONV KKILLAMS
280 INVENTORY_ATP_CODE,
281 RESERVABLE_TYPE,
282 AVAILABILITY_TYPE
283 --END INVCONV KKILLAMS
284 from MTL_MATERIAL_STATUSES_B
285 where STATUS_ID = X_STATUS_ID
286 for update of STATUS_ID nowait;
287 recinfo c%rowtype;
288
289 cursor c1 is select
290 STATUS_CODE,
291 DESCRIPTION,
292 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
293 from MTL_MATERIAL_STATUSES_TL
294 where STATUS_ID = X_STATUS_ID
295 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
296 for update of STATUS_ID nowait;
297 begin
298 open c;
299 fetch c into recinfo;
300 if (c%notfound) then
301 close c;
302 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
303 app_exception.raise_exception;
304 end if;
305 close c;
306 if ( ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
307 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
308 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
309 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
310 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
311 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
312 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
313 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
314 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
315 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
316 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
317 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
318 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
319 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
320 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
321 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
322 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
323 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
324 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
325 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
326 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
327 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
328 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
329 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
330 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
331 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
332 AND (recinfo.LOCATOR_CONTROL = X_LOCATOR_CONTROL)
333 AND (recinfo.LOT_CONTROL = X_LOT_CONTROL)
334 AND (recinfo.SERIAL_CONTROL = X_SERIAL_CONTROL)
335 AND (recinfo.ONHAND_CONTROL = X_ONHAND_CONTROL) -- Onhand Material Status Support
336 AND (recinfo.ZONE_CONTROL = X_ZONE_CONTROL)
337 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
338 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
339 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
340 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
341 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
342 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
343 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
344 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null
345 )))
346 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
347 AND (recinfo.LPN_CONTROL = X_LPN_CONTROL)
348 --INVCONV KKILLAMS
349 AND ((recinfo.INVENTORY_ATP_CODE = X_INVENTORY_ATP_CODE)
350 OR ((recinfo.INVENTORY_ATP_CODE is null) AND (X_INVENTORY_ATP_CODE is null)))
351 AND ((recinfo.RESERVABLE_TYPE = X_RESERVABLE_TYPE)
352 OR ((recinfo.RESERVABLE_TYPE is null) AND (X_RESERVABLE_TYPE is null)))
353 AND ((recinfo.AVAILABILITY_TYPE = X_AVAILABILITY_TYPE)
354 OR ((recinfo.AVAILABILITY_TYPE is null) AND (X_AVAILABILITY_TYPE is null)))
355 --END INVCONV KKILLAMS
356 ) then
357 null;
358 else
359 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
360 app_exception.raise_exception;
361 end if;
362
363 for tlinfo in c1 loop
364 if (tlinfo.BASELANG = 'Y') then
365 if ( (tlinfo.STATUS_CODE = X_STATUS_CODE)
366 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
367 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
368 ) then
369 null;
370 else
371 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
372 app_exception.raise_exception;
373 end if;
374 end if;
375 end loop;
376 return;
377 end LOCK_ROW;
378
379 procedure UPDATE_ROW (
380 X_STATUS_ID in NUMBER,
381 X_ATTRIBUTE15 in VARCHAR2,
382 X_ATTRIBUTE2 in VARCHAR2,
383 X_ATTRIBUTE3 in VARCHAR2,
384 X_ATTRIBUTE4 in VARCHAR2,
385 X_ATTRIBUTE5 in VARCHAR2,
386 X_ATTRIBUTE6 in VARCHAR2,
387 X_ATTRIBUTE7 in VARCHAR2,
388 X_ATTRIBUTE8 in VARCHAR2,
389 X_ATTRIBUTE9 in VARCHAR2,
390 X_ATTRIBUTE10 in VARCHAR2,
391 X_ATTRIBUTE11 in VARCHAR2,
392 X_ATTRIBUTE12 in VARCHAR2,
393 X_ATTRIBUTE13 in VARCHAR2,
394 X_LOCATOR_CONTROL in NUMBER,
395 X_LOT_CONTROL in NUMBER,
396 X_SERIAL_CONTROL in NUMBER,
397 X_ONHAND_CONTROL in NUMBER, -- Onhand Material Status Support Bug #6633612
398 X_ZONE_CONTROL in NUMBER,
399 X_ATTRIBUTE1 in VARCHAR2,
400 X_ATTRIBUTE14 in VARCHAR2,
401 X_REQUEST_ID in NUMBER,
402 X_ATTRIBUTE_CATEGORY in VARCHAR2,
403 X_ENABLED_FLAG in NUMBER,
404 X_STATUS_CODE in VARCHAR2,
405 X_DESCRIPTION in VARCHAR2,
406 X_LAST_UPDATE_DATE in DATE,
407 X_LAST_UPDATED_BY in NUMBER,
408 X_LAST_UPDATE_LOGIN in NUMBER,
409 X_LPN_CONTROL in NUMBER,
410 --INVCONV KKILLAMS
411 X_INVENTORY_ATP_CODE IN NUMBER,
412 X_RESERVABLE_TYPE IN NUMBER,
413 X_AVAILABILITY_TYPE IN NUMBER
414 --END INVCONV KKILLAMS
415 ) is
416 --INVCONV KKILLAMS
417 --Cursor is to verify the atp,reservable and available flags are getting modified or not.
418 CURSOR cur_status IS SELECT 1 FROM MTL_MATERIAL_STATUSES_B
419 WHERE STATUS_ID = X_STATUS_ID
420 AND (INVENTORY_ATP_CODE <> X_INVENTORY_ATP_CODE
421 OR RESERVABLE_TYPE <> X_RESERVABLE_TYPE
422 OR AVAILABILITY_TYPE <> X_AVAILABILITY_TYPE );
423 l_dummy NUMBER;
424 --END INVCONV KKILLAMS
425 BEGIN
426 --INVCONV KKILLAMS
427 OPEN cur_status;
428 FETCH cur_status INTO l_dummy;
429 --END INVCONV KKILLAMS
430 update MTL_MATERIAL_STATUSES_B set
431 ATTRIBUTE15 = X_ATTRIBUTE15,
432 ATTRIBUTE2 = X_ATTRIBUTE2,
433 ATTRIBUTE3 = X_ATTRIBUTE3,
434 ATTRIBUTE4 = X_ATTRIBUTE4,
435 ATTRIBUTE5 = X_ATTRIBUTE5,
436 ATTRIBUTE6 = X_ATTRIBUTE6,
437 ATTRIBUTE7 = X_ATTRIBUTE7,
438 ATTRIBUTE8 = X_ATTRIBUTE8,
439 ATTRIBUTE9 = X_ATTRIBUTE9,
440 ATTRIBUTE10 = X_ATTRIBUTE10,
441 ATTRIBUTE11 = X_ATTRIBUTE11,
442 ATTRIBUTE12 = X_ATTRIBUTE12,
443 ATTRIBUTE13 = X_ATTRIBUTE13,
444 LOCATOR_CONTROL = X_LOCATOR_CONTROL,
445 LOT_CONTROL = X_LOT_CONTROL,
446 SERIAL_CONTROL = X_SERIAL_CONTROL,
447 ONHAND_CONTROL = X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
448 ZONE_CONTROL = X_ZONE_CONTROL,
449 ATTRIBUTE1 = X_ATTRIBUTE1,
450 ATTRIBUTE14 = X_ATTRIBUTE14,
451 REQUEST_ID = X_REQUEST_ID,
452 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
453 ENABLED_FLAG = X_ENABLED_FLAG,
454 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
455 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
456 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
457 LPN_CONTROL = X_LPN_CONTROL,
458 --INVCONV KKILLAMS
459 INVENTORY_ATP_CODE = X_INVENTORY_ATP_CODE,
460 RESERVABLE_TYPE = X_RESERVABLE_TYPE,
461 AVAILABILITY_TYPE = X_AVAILABILITY_TYPE
462 --END INVCONV KKILLAMS
463 where STATUS_ID = X_STATUS_ID;
464
465 if (sql%notfound) then
466 raise no_data_found;
467 end if;
468
469 update MTL_MATERIAL_STATUSES_TL set
470 STATUS_CODE = X_STATUS_CODE,
471 DESCRIPTION = X_DESCRIPTION,
472 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
473 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
474 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
475 SOURCE_LANG = userenv('LANG')
476 where STATUS_ID = X_STATUS_ID
477 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
478
479 if (sql%notfound) then
480 -- Setting the flag to indicate that update failed for MTL_MATERIAL_STATUSES_TL Table
481 X_UPDATE_FLAG := 'T';
482 raise no_data_found;
483 end if;
484 --INVCONV KKILLAMS
485 --Update the Sub Inventory, Lot Number, Serial Number and Location table only if
486 --ATP, Availablity type, Reservable flags modified.
487 IF cur_status%FOUND THEN
488 UPDATE MTL_LOT_NUMBERS SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
489 AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
490 RESERVABLE_TYPE =X_AVAILABILITY_TYPE
491 WHERE STATUS_ID = X_STATUS_ID;
492 UPDATE MTL_ITEM_LOCATIONS SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
493 AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
494 RESERVABLE_TYPE =X_AVAILABILITY_TYPE
495 WHERE STATUS_ID = X_STATUS_ID;
496 UPDATE MTL_SECONDARY_INVENTORIES SET INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
497 AVAILABILITY_TYPE =X_RESERVABLE_TYPE,
498 RESERVABLE_TYPE =X_AVAILABILITY_TYPE
499 WHERE STATUS_ID = X_STATUS_ID;
500 END IF;
501 CLOSE cur_status;
502 --END INVCONV KKILLAMS
503 end UPDATE_ROW;
504
505 procedure DELETE_ROW (
506 X_STATUS_ID in NUMBER
507 ) is
508 begin
509 delete from MTL_MATERIAL_STATUSES_TL
510 where STATUS_ID = X_STATUS_ID;
511
512 if (sql%notfound) then
513 raise no_data_found;
514 end if;
515
516 delete from MTL_MATERIAL_STATUSES_B
517 where STATUS_ID = X_STATUS_ID;
518
519 if (sql%notfound) then
520 raise no_data_found;
521 end if;
522 end DELETE_ROW;
523
524 -- Bugfix 2354241
525 PROCEDURE Translate_row
526 ( X_STATUS_ID IN VARCHAR2,
527 X_OWNER IN VARCHAR2,
528 X_DESCRIPTION IN VARCHAR2,
529 X_STATUS_CODE IN VARCHAR2)
530 IS
531 BEGIN
532 update mtl_material_statuses_tl set
533 status_code = X_STATUS_CODE,
534 description = X_DESCRIPTION,
535 last_update_date = sysdate,
536 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
537 last_update_login = 0,
538 source_lang = userenv('LANG')
539 where status_id = fnd_number.canonical_to_number(x_status_id)
540 and userenv('LANG') IN (language, source_lang);
541 END translate_row;
542
543
544 PROCEDURE load_row
545 (X_STATUS_ID IN VARCHAR2,
546 X_OWNER IN VARCHAR2,
547 X_ZONE_CONTROL IN VARCHAR2,
548 X_LOCATOR_CONTROL IN VARCHAR2,
549 X_LOT_CONTROL IN VARCHAR2,
550 X_SERIAL_CONTROL IN VARCHAR2,
551 X_ONHAND_CONTROL IN VARCHAR2, -- Onhand Material Status Support Bug #6633612
552 X_ENABLED_FLAG IN VARCHAR2,
553 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
554 X_ATTRIBUTE1 IN VARCHAR2,
555 X_ATTRIBUTE2 IN VARCHAR2,
556 X_ATTRIBUTE3 IN VARCHAR2,
557 X_ATTRIBUTE4 IN VARCHAR2,
558 X_ATTRIBUTE5 IN VARCHAR2,
559 X_ATTRIBUTE6 IN VARCHAR2,
560 X_ATTRIBUTE7 IN VARCHAR2,
561 X_ATTRIBUTE8 IN VARCHAR2,
562 X_ATTRIBUTE9 IN VARCHAR2,
563 X_ATTRIBUTE10 IN VARCHAR2,
564 X_ATTRIBUTE11 IN VARCHAR2,
565 X_ATTRIBUTE12 IN VARCHAR2,
566 X_ATTRIBUTE13 IN VARCHAR2,
567 X_ATTRIBUTE14 IN VARCHAR2,
568 X_ATTRIBUTE15 IN VARCHAR2,
569 X_DESCRIPTION IN VARCHAR2,
570 X_STATUS_CODE IN VARCHAR2,
571 X_LPN_CONTROL IN NUMBER,
572 --INVCONV KKILLAMS
573 X_INVENTORY_ATP_CODE IN NUMBER,
574 X_RESERVABLE_TYPE IN NUMBER,
575 X_AVAILABILITY_TYPE IN NUMBER
576 --END INVCONV KKILLAMS
577 ) IS
578 BEGIN
579 DECLARE
580 l_status_id NUMBER;
581 l_user_id NUMBER := 0;
582 l_zone_control NUMBER;
583 l_locator_control NUMBER;
584 l_lot_control NUMBER;
585 l_serial_control NUMBER;
586 l_onhand_control NUMBER; --Onhand Material Status Control Bug #6633612
587 l_lpn_control NUMBER;
588 l_enabled_flag NUMBER;
589 l_row_id VARCHAR2(64);
590 l_sysdate DATE;
591 ---INVCONV kkillams
592 l_inventory_atp_code NUMBER;
593 l_reservable_type NUMBER;
594 l_availability_type NUMBER;
595 ---END INVCONV kkillams
596 BEGIN
597 IF (x_owner = 'SEED') THEN
598 l_user_id := 1;
599 END IF;
600
601 SELECT SYSDATE INTO l_sysdate FROM dual;
602 l_status_id := fnd_number.canonical_to_number(x_status_id);
603 l_zone_control := fnd_number.canonical_to_number(x_zone_control);
604 l_locator_control := fnd_number.canonical_to_number(x_locator_control);
605 l_lot_control := fnd_number.canonical_to_number(x_lot_control);
606 l_serial_control := fnd_number.canonical_to_number(x_serial_control);
607 l_enabled_flag := fnd_number.canonical_to_number(x_enabled_flag);
608 l_lpn_control := fnd_number.canonical_to_number(x_lpn_control);
609 --INVCONV kkillams
610 l_inventory_atp_code := fnd_number.canonical_to_number(x_inventory_atp_code);
611 l_reservable_type := fnd_number.canonical_to_number(x_reservable_type);
612 l_availability_type := fnd_number.canonical_to_number(x_availability_type);
613
614 l_onhand_control := fnd_number.canonical_to_number(x_onhand_control); --Onhand Material Status Control Bug #6633612
615 --END INVCONV kkillams
616
617 MTL_MATERIAL_STATUSES_PKG.update_row
618 (
619 X_STATUS_ID => l_status_id,
620 X_ATTRIBUTE15 => X_ATTRIBUTE15,
621 X_ATTRIBUTE2 => X_ATTRIBUTE2,
622 X_ATTRIBUTE3 => X_ATTRIBUTE3,
623 X_ATTRIBUTE4 => X_ATTRIBUTE4,
624 X_ATTRIBUTE5 => X_ATTRIBUTE5,
625 X_ATTRIBUTE6 => X_ATTRIBUTE6,
626 X_ATTRIBUTE7 => X_ATTRIBUTE7,
627 X_ATTRIBUTE8 => X_ATTRIBUTE8,
628 X_ATTRIBUTE9 => X_ATTRIBUTE9,
629 X_ATTRIBUTE10 => X_ATTRIBUTE10,
630 X_ATTRIBUTE11 => X_ATTRIBUTE11,
631 X_ATTRIBUTE12 => X_ATTRIBUTE12,
632 X_ATTRIBUTE13 => X_ATTRIBUTE13,
633 X_LOCATOR_CONTROL => l_locator_control,
634 X_LOT_CONTROL => l_lot_control,
635 X_SERIAL_CONTROL => l_serial_control,
636 X_ONHAND_CONTROL => l_onhand_control, -- Onhand Material Status Support Bug #6633612
637 X_ZONE_CONTROL => l_zone_control,
638 X_ATTRIBUTE1 => X_ATTRIBUTE1,
639 X_ATTRIBUTE14 => X_ATTRIBUTE14,
640 X_REQUEST_ID => NULL,
641 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
642 X_ENABLED_FLAG => l_enabled_flag,
643 X_STATUS_CODE => x_status_code,
644 X_DESCRIPTION => x_description,
645 X_LAST_UPDATE_DATE => l_sysdate,
646 X_LAST_UPDATED_BY => l_user_id,
647 X_LAST_UPDATE_LOGIN => 0,
648 X_LPN_CONTROL => l_lpn_control,
649 --INVCONV kkillams
650 X_INVENTORY_ATP_CODE => l_inventory_atp_code,
651 X_RESERVABLE_TYPE => l_reservable_type,
652 X_AVAILABILITY_TYPE => l_availability_type
653 --END INVCONV kkillams
654 );
655 EXCEPTION
656 WHEN no_data_found THEN
657 -- Bugfix 2396883.
658 -- If the update failed for MTL_MATERIAL_STATUSES_TL Table then insert recoreds into that table alone
659 -- else insert records into both the tables.
660 IF X_UPDATE_FLAG = 'T' THEN
661
662 MTL_MATERIAL_STATUSES_PKG.insert_tl_row
663 (
664 X_ROWID => l_row_id,
665 X_STATUS_ID => l_status_id,
666 X_STATUS_CODE => x_status_code,
667 X_DESCRIPTION => x_description,
668 X_CREATION_DATE => l_sysdate,
669 X_CREATED_BY => l_user_id,
670 X_LAST_UPDATE_DATE => l_sysdate,
671 X_LAST_UPDATED_BY => l_user_id,
672 X_LAST_UPDATE_LOGIN => 0
673 );
674
675 ELSE
676
677 MTL_MATERIAL_STATUSES_PKG.insert_row
678 (
679 X_ROWID => l_row_id,
680 X_STATUS_ID => l_status_id,
681 X_ATTRIBUTE15 => X_ATTRIBUTE15,
682 X_ATTRIBUTE2 => X_ATTRIBUTE2,
683 X_ATTRIBUTE3 => X_ATTRIBUTE3,
684 X_ATTRIBUTE4 => X_ATTRIBUTE4,
685 X_ATTRIBUTE5 => X_ATTRIBUTE5,
686 X_ATTRIBUTE6 => X_ATTRIBUTE6,
687 X_ATTRIBUTE7 => X_ATTRIBUTE7,
688 X_ATTRIBUTE8 => X_ATTRIBUTE8,
689 X_ATTRIBUTE9 => X_ATTRIBUTE9,
690 X_ATTRIBUTE10 => X_ATTRIBUTE10,
691 X_ATTRIBUTE11 => X_ATTRIBUTE11,
692 X_ATTRIBUTE12 => X_ATTRIBUTE12,
693 X_ATTRIBUTE13 => X_ATTRIBUTE13,
694 X_LOCATOR_CONTROL => l_locator_control,
695 X_LOT_CONTROL => l_lot_control,
696 X_SERIAL_CONTROL => l_serial_control,
697 X_ONHAND_CONTROL => l_onhand_control, -- Onhand Material Status Support Bug #6633612
698 X_ZONE_CONTROL => l_zone_control,
699 X_ATTRIBUTE1 => X_ATTRIBUTE1,
700 X_ATTRIBUTE14 => X_ATTRIBUTE14,
701 X_REQUEST_ID => NULL,
702 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
703 X_ENABLED_FLAG => l_enabled_flag,
704 X_STATUS_CODE => x_status_code,
705 X_DESCRIPTION => x_description,
706 X_CREATION_DATE => l_sysdate,
707 X_CREATED_BY => l_user_id,
708 X_LAST_UPDATE_DATE => l_sysdate,
709 X_LAST_UPDATED_BY => l_user_id,
710 X_LAST_UPDATE_LOGIN => 0,
711 X_LPN_CONTROL => l_lpn_control,
712 --INVCONV kkillams
713 X_INVENTORY_ATP_CODE => l_inventory_atp_code,
714 X_RESERVABLE_TYPE => l_reservable_type,
715 X_AVAILABILITY_TYPE => l_availability_type
716 --END INVCONV kkillams
717 );
718
719 END IF;
720
721 END;
722
723 END load_row;
724
725 procedure ADD_LANGUAGE
726 is
727 begin
728 delete from MTL_MATERIAL_STATUSES_TL T
729 where not exists
730 (select NULL
731 from MTL_MATERIAL_STATUSES_B B
732 where B.STATUS_ID = T.STATUS_ID
733 );
734
735 update MTL_MATERIAL_STATUSES_TL T set (
736 STATUS_CODE,
737 DESCRIPTION
738 ) = (select
739 B.STATUS_CODE,
740 B.DESCRIPTION
741 from MTL_MATERIAL_STATUSES_TL B
742 where B.STATUS_ID = T.STATUS_ID
743 and B.LANGUAGE = T.SOURCE_LANG)
744 where (
745 T.STATUS_ID,
746 T.LANGUAGE
747 ) in (select
748 SUBT.STATUS_ID,
749 SUBT.LANGUAGE
750 from MTL_MATERIAL_STATUSES_TL SUBB, MTL_MATERIAL_STATUSES_TL SUBT
751 where SUBB.STATUS_ID = SUBT.STATUS_ID
752 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
753 and (SUBB.STATUS_CODE <> SUBT.STATUS_CODE
754 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
755 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
756 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
757 ));
758
759 insert into MTL_MATERIAL_STATUSES_TL (
760 LAST_UPDATE_DATE,
761 CREATED_BY,
762 CREATION_DATE,
763 LAST_UPDATE_LOGIN,
764 STATUS_CODE,
765 DESCRIPTION,
766 STATUS_ID,
767 LAST_UPDATED_BY,
768 LANGUAGE,
769 SOURCE_LANG
770 ) select
771 B.LAST_UPDATE_DATE,
772 B.CREATED_BY,
773 B.CREATION_DATE,
774 B.LAST_UPDATE_LOGIN,
775 B.STATUS_CODE,
776 B.DESCRIPTION,
777 B.STATUS_ID,
778 B.LAST_UPDATED_BY,
779 L.LANGUAGE_CODE,
780 B.SOURCE_LANG
781 from MTL_MATERIAL_STATUSES_TL B, FND_LANGUAGES L
782 where L.INSTALLED_FLAG in ('I', 'B')
783 and B.LANGUAGE = userenv('LANG')
784 and not exists
785 (select NULL
786 from MTL_MATERIAL_STATUSES_TL T
787 where T.STATUS_ID = B.STATUS_ID
788 and T.LANGUAGE = L.LANGUAGE_CODE);
789 end ADD_LANGUAGE;
790
791 end MTL_MATERIAL_STATUSES_PKG;