[Home] [Help]
PACKAGE BODY: APPS.INV_STATUS_PKG
Source
1 PACKAGE BODY INV_STATUS_PKG AS
2 /* $Header: INVUPMSB.pls 120.17.12010000.1 2008/07/24 01:50:57 appldev ship $ */
3
4 -- START SCHANDRU INVERES
5 function get_from_status_code ( p_org_id in number default null,
6 p_item_id in number default null,
7 p_sub_inv in varchar2 default null,
8 p_locator_id in number default null,
9 p_lot in varchar2 default null,
10 p_serial in varchar2 default null) return varchar2
11 as
12
13 PRAGMA AUTONOMOUS_TRANSACTION;
14
15 x_status_code varchar2(100) := NULL;
16
17 begin
18
19
20 if (p_sub_inv IS NOT NULL ) then
21
22 select mms.status_code
23 into x_status_code
24 from mtl_material_statuses mms ,
25 MTL_SECONDARY_INVENTORIES msi
26 where mms.status_id = msi.status_id
27 and msi.SECONDARY_INVENTORY_NAME = p_sub_inv
28 and msi.organization_id = p_org_id;
29
30 elsif (p_locator_id IS NOT NULL ) then
31
32 select mms.status_code
33 into x_status_code
34 from mtl_material_statuses mms ,
35 MTL_ITEM_LOCATIONS_KFV mil
36 where mms.status_id = mil.status_id
37 and mil.INVENTORY_LOCATION_ID = p_locator_id
38 and mil.organization_id = p_org_id;
39
40 elsif (p_serial IS NOT NULL) then
41
42 select mms.status_code
43 into x_status_code
44 from mtl_material_statuses mms ,
45 MTL_SERIAL_NUMBERS msn
46 where mms.status_id = msn.status_id
47 and msn.SERIAL_NUMBER = p_serial
48 and msn.current_organization_id = p_org_id
49 and msn.inventory_item_id = p_item_id;
50
51 elsIF (p_lot IS NOT NULL) then
52
53 select mms.status_code
54 into x_status_code
55 from mtl_material_statuses mms ,
56 MTL_LOT_NUMBERS mln
57 where mms.status_id = mln.status_id
58 and mln.LOT_NUMBER = p_lot
59 and mln.organization_id = p_org_id
60 and mln.inventory_item_id = p_item_id;
61
62 end if;
63
64 return x_status_code;
65
66 exception when others then
67 return NULL;
68
69 end get_from_status_code ;
70 --END SCHANDRU INVERES
71
72 PROCEDURE mdebug(msg in varchar2)
73 IS
74 l_msg VARCHAR2(5100);
75 l_ts VARCHAR2(30);
76 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
77 BEGIN
78 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
79
80 l_msg:=l_ts||' '||msg;
81
82 IF (l_debug = 1) THEN
83 inv_mobile_helper_functions.tracelog
84 (p_err_msg => l_msg,
85 p_module => 'INV_STATUS_PKG',
86 p_level => 4);
87 END IF;
88
89 --dbms_output.put_line(msg);
90 -- null;
91 END;
92
93 PROCEDURE check_lot_range_status(
94 p_org_id IN NUMBER,
95 p_item_id IN NUMBER,
96 p_from_lot IN VARCHAR2,
97 p_to_lot IN VARCHAR2,
98 x_Status OUT nocopy VARCHAR2,
99 x_Message OUT nocopy VARCHAR2,
100 x_Status_Code OUT nocopy VARCHAR2
101 ) IS
102 lot_status_id NUMBER:=0;
103 first_row BOOLEAN := TRUE;
104 -- Bug# 1520495
105 l_lot_status_enabled VARCHAR2(1);
106 l_default_lot_status_id NUMBER;
107 l_serial_status_enabled VARCHAR2(1);
108 l_default_serial_status_id NUMBER;
109 l_return_status VARCHAR2(1);
110 l_msg_data VARCHAR2(2000);
111 l_msg_count NUMBER;
112
113
114 cursor lot_cur is
115 SELECT status_id
116 FROM MTL_LOT_NUMBERS
117 WHERE organization_id = p_org_id
118 AND inventory_item_id = p_item_id
119 AND lot_number BETWEEN p_from_lot AND p_to_lot;
120 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
121 BEGIN
122 x_Status := 'C';
123 FOR cc IN lot_cur LOOP
124 if first_row then
125 lot_status_id := cc.status_id;
126 first_row := FALSE;
127 elsif cc.status_id <> lot_status_id then
128 x_Status := 'E';
129 FND_MESSAGE.SET_NAME('WMS','WMS_MULTI_STATUS');
130 x_Message := FND_MESSAGE.GET;
131 -- x_Message := 'Multiple lot status';
132 end if;
133 END LOOP;
134
135 -- Bug# 1520495
136 -- From the above fetch, it is possible that lot_status_id is NULL in which
137 -- case get the default lot status id for the organization item.
138
139 if (x_Status <> 'E') AND (lot_status_id is null) then
140 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
141 p_organization_id => p_org_id
142 , p_inventory_item_id => p_item_id
143 , x_return_status => l_return_status
144 , x_msg_count => l_msg_count
145 , x_msg_data => l_msg_data
146 , x_lot_status_enabled => l_lot_status_enabled
147 , x_default_lot_status_id => l_default_lot_status_id
148 , x_serial_status_enabled => l_serial_status_enabled
149 , x_default_serial_status_id => l_default_serial_status_id);
150
151 if ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
152 x_Status:= 'E';
153 x_Message := l_msg_data;
154 end if;
155 if (NVL(l_lot_status_enabled, 'Y')='Y') then
156 lot_status_id := l_default_lot_status_id;
157 end if;
158 end if;
159 if x_Status = 'C' then
160 SELECT status_code
161 INTO x_status_code
162 FROM MTL_MATERIAL_STATUSES_VL
163 WHERE status_id = lot_status_id;
164 end if;
165
166 END check_lot_range_status;
167
168
169 PROCEDURE check_serial_range_status(
170 p_org_id IN NUMBER,
171 p_item_id IN NUMBER,
172 p_from_serial IN VARCHAR2,
173 p_to_serial IN VARCHAR2,
174 x_Status OUT nocopy VARCHAR2,
175 x_Message OUT nocopy VARCHAR2,
176 x_Status_Code OUT nocopy VARCHAR2
177 ) IS
178 serial_status_id NUMBER:=0;
179 first_row BOOLEAN := TRUE;
180 -- Bug# 1520495
181 l_lot_status_enabled VARCHAR2(1);
182 l_default_lot_status_id NUMBER;
183 l_serial_status_enabled VARCHAR2(1);
184 l_default_serial_status_id NUMBER;
185 l_return_status VARCHAR2(1);
186 l_msg_data VARCHAR2(2000);
187 l_msg_count NUMBER;
188
189 /* FP-J Lot/Serial Support Enhancements
190 * Add current status of resides in receiving
191 */
192 cursor serial_cur is
193 SELECT status_id
194 FROM MTL_SERIAL_NUMBERS
195 WHERE current_organization_id = p_org_id
196 AND inventory_item_id = p_item_id
197 --AND current_status in (1, 3, 5)
198 AND current_status in (1, 3, 5, 7)
199 AND serial_number BETWEEN p_from_serial AND p_to_serial;
200
201 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
202 BEGIN
203 x_Status := 'C';
204 FOR cc IN serial_cur LOOP
205 if first_row then
206 serial_status_id := cc.status_id;
207 first_row := FALSE;
208 elsif cc.status_id <> serial_status_id then
209 x_Status := 'E';
210 FND_MESSAGE.SET_NAME('WMS','WMS_MULTI_STATUS');
211 x_Message := FND_MESSAGE.GET;
212 -- x_Message := 'Multiple serial status';
213 end if;
214 END LOOP;
215
216 -- Bug# 1520495
217 -- From the above fetch, it is possible that serial_status_id is NULL in which
218 -- case get the default serial status id for the organization item.
219
220 if (x_Status <> 'E') AND (serial_status_id is null) then
221 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
222 p_organization_id => p_org_id
223 , p_inventory_item_id => p_item_id
224 , x_return_status => l_return_status
225 , x_msg_count => l_msg_count
226 , x_msg_data => l_msg_data
227 , x_lot_status_enabled => l_lot_status_enabled
228 , x_default_lot_status_id => l_default_lot_status_id
229 , x_serial_status_enabled => l_serial_status_enabled
230 , x_default_serial_status_id => l_default_serial_status_id);
231
232 if ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
233 x_Status:= 'E';
234 x_Message := l_msg_data;
235 end if;
236 if (NVL(l_serial_status_enabled, 'Y')='Y') then
237 serial_status_id := l_default_serial_status_id;
238 end if;
239 end if;
240
241 if x_Status = 'C' then
242 SELECT status_code
243 INTO x_status_code
244 FROM MTL_MATERIAL_STATUSES_VL
245 WHERE status_id = serial_status_id;
246 end if;
247
248 END check_serial_range_status;
249
250 PROCEDURE update_status(
251 p_update_method IN NUMBER
252 , p_organization_id IN NUMBER
253 , p_inventory_item_id IN NUMBER
254 , p_sub_code IN VARCHAR2
255 , p_sub_status_id IN NUMBER
256 , p_sub_reason_id IN NUMBER
257 , p_locator_id IN NUMBER
258 , p_loc_status_id IN NUMBER
259 , p_loc_reason_id IN NUMBER
260 , p_from_lot_number IN VARCHAR2
261 , p_to_lot_number IN VARCHAR2
262 , p_lot_status_id IN NUMBER
263 , p_lot_reason_id IN NUMBER
264 , p_from_SN IN VARCHAR2
265 , p_to_SN IN VARCHAR2
266 , p_serial_status_id IN NUMBER
267 , p_serial_reason_id IN NUMBER
268 , x_Status OUT nocopy VARCHAR2
269 , x_Message OUT nocopy VARCHAR2
270 , p_update_from_mobile IN VARCHAR2 DEFAULT 'Y'
271 -- NSRIVAST, INVCONV , Start
272 , p_grade_code IN VARCHAR2 DEFAULT NULL
273 , p_primary_onhand IN NUMBER DEFAULT NULL
274 , p_secondary_onhand IN NUMBER DEFAULT NULL
275 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
276 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
277 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
278 -- NSRIVAST, INVCONV , End
279 )
280 IS
281 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
282 dummy varchar2(100);
283 -- Added below two variables for LPN status Project
284 l_serial_controlled NUMBER;
285 l_serial_status_enabled NUMBER;
286
287
288 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
289 rowid_list rowidtab;
290 -- Added for bug # 6882196
291 TYPE rowidtab1 IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
292 rowid_list1 rowidtab1;
293
294 cursor cur_lot_number is
295 SELECT lot_number
296 FROM MTL_LOT_NUMBERS
297 WHERE organization_id = p_organization_id
298 AND inventory_item_id = p_inventory_item_id
299 AND lot_number between p_from_lot_number and p_to_lot_number;
300
301 -- Added the cur_onhand for bug 6633612
302 -- Added the cursor CUR_ONHSERIAL and modified the exists clause in the cusror CUR_ONHAND for bug# 6633612
303
304 CURSOR cur_onhand IS
305 select moqd.rowid FROM mtl_onhand_quantities_detail moqd
306 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
307 and organization_id = p_organization_id
308 and subinventory_code = Nvl(p_sub_code, subinventory_code)
309 and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
310 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) -- Bug 7012984, Modified the lpn_id condition
311 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
312 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
313 and exists
314 (select 1 from mtl_system_items_b msi
315 where moqd.inventory_item_id = msi.inventory_item_id
316 AND moqd.organization_id = msi.organization_id
317 AND msi.serial_number_control_code in (1,6)
318 )
319 FOR UPDATE NOWAIT;
320
321 CURSOR cur_onhserial IS
322 select msn.rowid FROM mtl_serial_numbers msn
323 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
324 and current_organization_id = p_organization_id
325 and current_subinventory_code = Nvl(p_sub_code, current_subinventory_code)
326 and nvl(current_locator_id, -9999) = nvl(p_locator_id, Nvl(current_locator_id, -9999))
327 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) -- Bug 7012984, Modified the lpn_id condition
328 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
329 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
330 and current_status = 3
331 and exists
332 (select 1 from mtl_system_items_b msi
333 where msn.inventory_item_id = msi.inventory_item_id
334 AND msn.current_organization_id = msi.organization_id
335 AND nvl(msi.serial_status_enabled, 'N') = 'Y'
336 )
337 FOR UPDATE NOWAIT;
338 --LPN status project
339
340 CURSOR wlc_cur
341 IS
342 SELECT *
343 FROM wms_lpn_contents wlc
344 WHERE wlc.parent_lpn_id IN
345 (SELECT lpn_id
346 FROM wms_license_plate_numbers plpn
347 start with lpn_id = p_lpn_id
348 connect by parent_lpn_id = prior lpn_id
349 )
350 order by serial_summary_entry
351 FOR UPDATE NOWAIT;
352
353 --LPN status project end
354
355
356
357 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
358 l_allow_mixed_status NUMBER := NVL(FND_PROFILE.VALUE('WMS_ALLOW_MIXED_STATUS'),2);--lpn status project
359 l_ret_status varchar2(20) := 'S';
360 l_outermost_lpn_id NUMBER;
361 l_count NUMBER :=0; -- Bug 6798024
362 l_serial_status_control NUMBER := 1;--bug 6952533
363 BEGIN
364 --BEGIN SCHANDRU INVERES
365 IF p_update_from_mobile = 'Y' THEN
366 SAVEPOINT INV_UPDATE_STATUS;
367 END IF;
368 -- SAVEPOINT INV_UPDATE_STATUS;
369 --END SCHANDRU INVERES
370 IF (l_debug = 1) THEN
371 mdebug('in update status');
372 END IF;
373 x_Status := 'C';
374 l_status_rec.organization_id := p_organization_id;
375 l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_manual;
376
377 IF (l_debug = 1) THEN
378 mdebug('p_sub_status_id: '||p_sub_status_id);
379 END IF;
380 if p_sub_status_id >0 then
381 update mtl_secondary_inventories
382 set status_id = p_sub_status_id
383 , last_updated_by = FND_GLOBAL.USER_ID
384 , last_update_date = SYSDATE
385 , last_update_login = FND_GLOBAL.LOGIN_ID
386 where organization_id = p_organization_id
387 and secondary_inventory_name = p_sub_code;
388
389 l_status_rec.zone_code := p_sub_code;
390 l_status_rec.status_id := p_sub_status_id;
391 l_status_rec.update_reason_id := p_sub_reason_id;
392 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
393 l_status_rec.initial_status_flag := 'N';
394 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
395 -- update the status history
396 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
397 end if;
398 IF (l_debug = 1) THEN
399 mdebug('p_loc_status_id: '||p_loc_status_id);
400 END IF;
401 if p_loc_status_id >0 then
402 update mtl_item_locations
403 set status_id = p_loc_status_id
404 , last_updated_by = FND_GLOBAL.USER_ID
405 , last_update_date = SYSDATE
406 , last_update_login = FND_GLOBAL.LOGIN_ID
407 where organization_id = p_organization_id
408 and inventory_location_id = p_locator_id;
409
410 l_status_rec.zone_code := p_sub_code;
411 l_status_rec.locator_id := p_locator_id;
412 l_status_rec.status_id := p_loc_status_id;
413 l_status_rec.update_reason_id := p_loc_reason_id;
414 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
415 l_status_rec.initial_status_flag := 'N';
416 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
417 -- update the status history
418 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
419 end if;
420 IF (l_debug = 1) THEN
421 mdebug('p_lot_status_id: '||p_lot_status_id);
422 END IF;
423 if p_lot_status_id >0 then
424 update mtl_lot_numbers
425 set status_id = p_lot_status_id
426 , last_updated_by = FND_GLOBAL.USER_ID
427 , last_update_date = SYSDATE
428 , last_update_login = FND_GLOBAL.LOGIN_ID
429 where organization_id = p_organization_id
430 and inventory_item_id = p_inventory_item_id
431 and lot_number BETWEEN p_from_lot_number and p_to_lot_number ;
432
433 -- update status history
434 l_status_rec.inventory_item_id := p_inventory_item_id;
435 l_status_rec.status_id := p_lot_status_id;
436 l_status_rec.update_reason_id := p_lot_reason_id;
437 -- NSRIVAST, INVCONV , Start
438 l_status_rec.grade_code := p_grade_code ;
439 l_status_rec.primary_onhand := p_primary_onhand ;
440 l_status_rec.secondary_onhand := p_secondary_onhand ;
441 -- NSRIVAST, INVCONV , End
442 FOR cc IN cur_lot_number LOOP
443 l_status_rec.lot_number := cc.lot_number;
444 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
445 l_status_rec.initial_status_flag := 'N';
446 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
447 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
448 END LOOP;
449 end if;
450 IF (l_debug = 1) THEN
451 mdebug('p_serial_status_id: '||p_serial_status_id);
452 END IF;
453 if p_serial_status_id >0 then
454 update mtl_serial_numbers
455 set status_id = p_serial_status_id
456 , last_updated_by = FND_GLOBAL.USER_ID
457 , last_update_date = SYSDATE
458 , last_update_login = FND_GLOBAL.LOGIN_ID
459 where current_organization_id = p_organization_id
460 and inventory_item_id = p_inventory_item_id
461 and serial_number BETWEEN p_from_SN AND p_to_SN;
462
463 l_status_rec.inventory_item_id := p_inventory_item_id;
464 l_status_rec.status_id := p_serial_status_id;
465 l_status_rec.update_reason_id := p_serial_reason_id;
466 l_status_rec.serial_number := p_from_SN;
467 l_status_rec.to_serial_number := p_to_SN;
468 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
469 l_status_rec.initial_status_flag := 'N';
470 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
471 -- update the status history
472 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
473
474 end if;
475
476 ------Start of changes for # 6633612---------------
477 IF (l_debug = 1) THEN
478 mdebug('p_onhand_status_id: '||p_onhand_status_id);
479 END IF;
480
481 if p_onhand_status_id >0 and p_serial_status_id = 0 then
482 ---lpn status project start for updating full lpn case
483 IF (p_lpn_id IS NOT NULL AND p_inventory_item_id IS NULL)THEN
484 --bug 6952533
485 SELECT serial_control into l_serial_status_control
486 from mtl_material_statuses
487 WHERE status_id = p_onhand_status_id;
488 --end of bug 6952533
489 IF(l_allow_mixed_status = 2)THEN
490 SELECT outermost_lpn_id into l_outermost_lpn_id
491 FROM wms_license_plate_numbers
492 WHERE lpn_id = p_lpn_id ;
493 l_ret_status := get_mixed_status(p_lpn_id => p_lpn_id,
494 p_organization_id =>p_organization_id,
495 p_outermost_lpn_id => l_outermost_lpn_id,
496 p_inventory_item_id => NULL,
497 p_lot_number => NULL,
498 p_status_id =>p_onhand_status_id);
499 IF (l_ret_status = 'M') THEN
500 x_status := 'M';
501 RETURN;
502 END IF;
503 END IF;
504 FOR l_wlc_cur IN wlc_cur LOOP
505 l_serial_controlled := 0;
506 l_serial_status_enabled := 0;
507 mdebug('in mass update of lpn '||p_onhand_status_id);
508 IF inv_cache.set_item_rec(p_organization_id, l_wlc_cur.inventory_item_id) THEN
509 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
510 l_serial_controlled := 1; -- Item is serial controlled
511 END IF;
512 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
513 l_serial_status_enabled := 1;
514 --bug 6952533
515 IF(l_serial_status_control = 2)THEN
516 x_status := 'E';
517 FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
518 x_Message := FND_MESSAGE.GET;
519 RETURN;
520 END IF;
521 --end of bug 6952533
522 END IF;
523 END IF;
524 IF(l_serial_controlled = 0)THEN
525 UPDATE mtl_onhand_quantities_detail
526 SET status_id = p_onhand_status_id
527 , last_updated_by = FND_GLOBAL.USER_ID
528 , last_update_date = SYSDATE
529 , last_update_login = FND_GLOBAL.LOGIN_ID
530 WHERE inventory_item_id = l_wlc_cur.inventory_item_id
531 AND organization_id = p_organization_id
532 AND subinventory_code = Nvl(p_sub_code,'@@@@')
533 AND locator_id = Nvl(p_locator_id ,-9999)
534 AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_cur.lot_number,'@@@@')
535 AND lpn_id = l_wlc_cur.parent_lpn_id;
536 ELSIF(l_serial_status_enabled = 1)THEN
537 UPDATE mtl_serial_numbers
538 set status_id = p_onhand_status_id
539 , last_updated_by = FND_GLOBAL.USER_ID
540 , last_update_date = SYSDATE
541 , last_update_login = FND_GLOBAL.LOGIN_ID
542 WHERE inventory_item_id = l_wlc_cur.inventory_item_id
543 AND current_organization_id = p_organization_id
544 AND current_subinventory_code = Nvl(p_sub_code,'@@@@')
545 AND current_locator_id = Nvl(p_locator_id ,-9999)
546 AND lpn_id = l_wlc_cur.parent_lpn_id;
547 END IF;
548 END LOOP;
549
550
551 --LPN status project end for full lpn update
552 --bug 6952533
553 ELSIF (p_lpn_id IS NOT NULL AND p_inventory_item_id IS NOT NULL AND p_from_SN IS NOT NULL AND p_to_SN is not null) THEN
554 mdebug('came here for updating serial in range to status '|| p_onhand_status_id);
555
556 IF ( l_allow_mixed_status = 2) THEN
557 SELECT outermost_lpn_id into l_outermost_lpn_id
558 FROM wms_license_plate_numbers
559 WHERE lpn_id = p_lpn_id ;
560 l_ret_status := get_mixed_status_serial(p_lpn_id => p_lpn_id,
561 p_organization_id =>p_organization_id ,
562 p_outermost_lpn_id => l_outermost_lpn_id,
563 p_inventory_item_id => p_inventory_item_id,
564 p_lot_number => p_from_lot_number,
565 p_fm_sn => p_from_SN,
566 p_to_sn => p_to_SN,
567 p_status_id => p_onhand_status_id);
568 IF (l_ret_status = 'M') THEN
569 x_status := 'M';
570 RETURN;
571 END IF;
572 END IF;
573
574 update mtl_serial_numbers
575 set status_id = p_onhand_status_id
576 , last_updated_by = FND_GLOBAL.USER_ID
577 , last_update_date = SYSDATE
578 , last_update_login = FND_GLOBAL.LOGIN_ID
579 where lpn_id = p_lpn_id
580 AND current_organization_id = p_organization_id
581 AND inventory_item_id = p_inventory_item_id
582 AND serial_number BETWEEN p_from_SN AND p_to_SN
583 AND Nvl(lot_number,'@@@@') = Nvl(p_from_lot_number,'@@@@');
584
585 --end of bug 6952533
586
587 ELSE
588 --lpn status project
589 IF(p_lpn_id IS NOT null)THEN
590 IF(l_allow_mixed_status = 2)THEN
591 SELECT outermost_lpn_id into l_outermost_lpn_id
592 FROM wms_license_plate_numbers
593 WHERE lpn_id = p_lpn_id;
594 l_ret_status := get_mixed_status(p_lpn_id => p_lpn_id,
595 p_organization_id =>p_organization_id,
596 p_outermost_lpn_id => l_outermost_lpn_id,
597 p_inventory_item_id => p_inventory_item_id,
598 p_lot_number => p_from_lot_number,
599 p_status_id =>p_onhand_status_id);
600 IF l_ret_status = 'M' THEN
601 x_status := 'M';
602 RETURN;
603 END IF;
604 END IF;
605 END IF;
606 --lpn status project
607
608 begin
609
610 OPEN cur_onhand;
611 FETCH cur_onhand BULK COLLECT INTO rowid_list ;
612 FORALL j in rowid_list.first .. rowid_list.last
613
614 update mtl_onhand_quantities_detail
615 set status_id = p_onhand_status_id
616 , last_updated_by = FND_GLOBAL.USER_ID
617 , last_update_date = SYSDATE
618 , last_update_login = FND_GLOBAL.LOGIN_ID
619 where ROWID = rowid_list(j);
620 -- Modified the where clause in the above update as it is not required for the bug # 6633612
621 COMMIT;
622 CLOSE cur_onhand;
623
624 mdebug(' update executed in sql: Onhand');
625
626 -- Added the serial status check for bug # 7113129
627 SELECT serial_control into l_serial_status_control
628 from mtl_material_statuses
629 WHERE status_id = p_onhand_status_id;
630
631 IF (l_serial_status_control = 1) THEN
632 OPEN cur_onhserial;
633 FETCH cur_onhserial BULK COLLECT INTO rowid_list1 ;
634 FORALL j in rowid_list1.first .. rowid_list1.last
635
636 update mtl_serial_numbers
637 set status_id = p_onhand_status_id
638 , last_updated_by = FND_GLOBAL.USER_ID
639 , last_update_date = SYSDATE
640 , last_update_login = FND_GLOBAL.LOGIN_ID
641 where ROWID = rowid_list1(j);
642 COMMIT;
643 CLOSE cur_onhserial;
644 END IF;
645
646 -- Removed the Loop in the above two cursors as a part of changes made for the bug # 6882196
647 mdebug(' update executed in sql: Onhand Serial');
648
649 /* Bug 6917621 */
650 if (p_inventory_item_id is not null) then
651 l_status_rec.inventory_item_id := p_inventory_item_id;
652 end if;
653 l_status_rec.zone_code := p_sub_code;
654 l_status_rec.locator_id := p_locator_id;
655 l_status_rec.lpn_id := p_lpn_id;
656 l_status_rec.status_id := p_onhand_status_id;
657 l_status_rec.update_reason_id := p_onhand_reason_id;
658 l_status_rec.initial_status_flag := 'N';
659 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
660
661 FOR cc IN cur_lot_number LOOP -- To update all the lots in a given sub, locator combination..
662 l_status_rec.lot_number := cc.lot_number;
663 l_status_rec.initial_status_flag := 'N';
664 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
665 -- Bug 6798024
666 l_count := l_count + 1;
667 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
668 END LOOP;
669
670 -- Bug 6798024 : If insert history was not called from inside the lot loop then call it from here
671 if (l_count = 0) then
672 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
673 end if;
674 EXCEPTION WHEN OTHERS THEN
675 mdebug(' IN OTHERS EXCEPTION '||sqlerrm);
676 FND_MESSAGE.SET_NAME('FND','FLEX-HASH DEADLOCK');
677 x_message := FND_MESSAGE.GET;
678 x_status :='E';
679 IF cur_onhand%ISOPEN THEN
680 CLOSE cur_onhand;
681 END IF;
682 -- Added for bug # 6882196
683 IF cur_onhserial%ISOPEN THEN
684 CLOSE cur_onhserial;
685 END IF;
686 RETURN;
687 END;
688 end if; --added for full lpn update case
689 end if;
690
691 -- End of changes for # 6633612---------------
692
693 -- invoke workflow to process the update
694 if p_sub_status_id >0 or p_loc_status_id >0 or
695 p_lot_status_id >0 or p_serial_status_id >0 or p_onhand_status_id > 0 then
696 IF (l_debug = 1) THEN
697 mdebug('before INV_STATUS_PKG.invoke_reason_wf');
698 END IF;
699
700 INV_STATUS_PKG.invoke_reason_wf(
701 p_update_method
702 , p_organization_id
703 , p_inventory_item_id
704 , p_sub_code
705 , p_sub_status_id
706 , p_sub_reason_id
707 , p_locator_id
708 , p_loc_status_id
709 , p_loc_reason_id
710 , p_from_lot_number
711 , p_to_lot_number
712 , p_lot_status_id
713 , p_lot_reason_id
714 , p_from_SN
715 , p_to_SN
716 , p_serial_status_id
717 , p_serial_reason_id
718 , p_onhand_status_id -- Added for # 6633612
719 , p_onhand_reason_id -- Added for # 6633612
720 , p_lpn_id -- Added for # 6633612
721 , x_Status
722 , x_Message
723 );
724 IF (l_debug = 1) THEN
725 mdebug('after INV_STATUS_PKG.invoke_reason_wf');
726 END IF;
727 end if;
728 IF (l_debug = 1) THEN
729 mdebug('x_status: '||x_status);
730 END IF;
731 if x_status ='E' then
732 --BEGIN SCHANDRU INVERES
733 IF p_update_from_mobile = 'Y' THEN
734 ROLLBACK TO INV_UPDATE_STATUS;
735 END IF;
736 --ROLLBACK TO INV_UPDATE_STATUS;
737 --END SCHANDRU INVERES
738 FND_MESSAGE.SET_NAME('WMS','WMS_WORKFLOW_CALL_FAIL');
739 x_message := FND_MESSAGE.GET;
740 end if;
741 if p_sub_status_id <=0 and p_loc_status_id <=0 and
742 p_lot_status_id <=0 and p_serial_status_id <=0
743 and p_onhand_status_id<=0 and x_status ='C' then
744 x_Status := 'E';
745 FND_MESSAGE.SET_NAME('WMS','WMS_NO_STATUS_CHANGED');
746 x_Message := FND_MESSAGE.GET;
747 -- x_Message := 'No changes to update';
748 else
749 --BEGIN SCHANDRU INVERES
750 --commit; --For bug 5487508, the commit will be issued from Java
751 x_Status := 'S';
752 --END SCHANDRU INVERES
753 end if;
754 EXCEPTION
755 WHEN NO_DATA_FOUND THEN
756 x_status := 'E';
757 WHEN OTHERS THEN
758 --BEGIN SCHANDRU INVERES
759 IF p_update_from_mobile = 'Y' THEN
760 ROLLBACK TO INV_UPDATE_STATUS;
761 END IF;
762
763 --ROLLBACK TO INV_UPDATE_STATUS;
764 --END SCHANDRU INVERES
765 x_status := 'E';
766 FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
767 x_Message := FND_MESSAGE.GET;
768 END update_status;
769
770
771 PROCEDURE invoke_reason_wf(
772 p_update_method IN NUMBER
773 , p_organization_id IN NUMBER
774 , p_inventory_item_id IN NUMBER
775 , p_sub_code IN VARCHAR2
776 , p_sub_status_id IN NUMBER
777 , p_sub_reason_id IN NUMBER
778 , p_locator_id IN NUMBER
779 , p_loc_status_id IN NUMBER
780 , p_loc_reason_id IN NUMBER
781 , p_from_lot_number IN VARCHAR2
782 , p_to_lot_number IN VARCHAR2
783 , p_lot_status_id IN NUMBER
784 , p_lot_reason_id IN NUMBER
785 , p_from_SN IN VARCHAR2
786 , p_to_SN IN VARCHAR2
787 , p_serial_status_id IN NUMBER
788 , p_serial_reason_id IN NUMBER
789 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
790 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
791 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
792 , x_Status OUT nocopy VARCHAR2
793 , x_Message OUT nocopy VARCHAR2)
794 IS
795 l_workflow_name varchar2(250);
796 l_reason_name varchar2(30);
797 l_calling_program_name VARCHAR2(30);
798 l_update_method varchar2(80);
799 l_status_code varchar2(80);
800 -- defining output variables
801 lX_RETURN_STATUS VARCHAR2(250);
802 lX_MSG_DATA VARCHAR2(250);
803 lX_MSG_COUNT NUMBER;
804 lX_ORGANIZATION_ID NUMBER;
805 lX_SUBINVENTORY VARCHAR2(250);
806 lX_SUBINVENTORY_STATUS VARCHAR2(250);
807 lX_LOCATOR NUMBER;
808 lX_LOCATOR_STATUS VARCHAR2(250);
809 lX_LPN_ID NUMBER;
810 lX_LPN_STATUS VARCHAR2(250);
811 lX_INVENTORY_ITEM_ID NUMBER;
812 lX_REVISION VARCHAR2(250);
813 lX_LOT_NUMBER VARCHAR2(250);
814 lX_LOT_STATUS VARCHAR2(250);
815 lX_QUANTITY NUMBER;
816 lX_UOM_CODE VARCHAR2(250);
817 lX_PRIMARY_QUANTITY NUMBER;
818 lX_TRANSACTION_QUANTITY NUMBER;
819 lX_RESERVATION_ID NUMBER;
820
821 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
822 BEGIN
823 IF (l_debug = 1) THEN
824 mdebug('In invoke_reason_wf');
825 END IF;
826 l_calling_program_name := 'Update Status';
827 x_Status := 'C';
828 IF (l_debug = 1) THEN
829 mdebug('l_calling_orogram_name: '||l_calling_program_name);
830 END IF;
831 SELECT meaning
832 INTO l_update_method
833 FROM MFG_LOOKUPS
834 WHERE LOOKUP_TYPE = 'MTL_STATUS_UPDATE_METHOD'
835 AND LOOKUP_CODE = p_update_method;
836
837 IF (l_debug = 1) THEN
838 mdebug('p_sub_status_id: '||p_sub_status_id);
839 END IF;
840 if p_sub_status_id >0 then
841 SELECT WORKFLOW_NAME
842 INTO l_workflow_name
843 FROM MTL_TRANSACTION_REASONS
844 WHERE REASON_ID = p_sub_reason_id;
845
846 SELECT REASON_NAME
847 INTO l_reason_name
848 FROM MTL_TRANSACTION_REASONS
849 WHERE REASON_ID = p_sub_reason_id;
850
851 SELECT status_code
852 INTO l_status_code
853 FROM MTL_MATERIAL_STATUSES_VL
854 WHERE status_id = p_sub_status_id;
855
856 IF (l_debug = 1) THEN
857 mdebug('l_workflow_name: '||l_workflow_name);
858 END IF;
859
860 if l_workflow_name is not null then
861 IF (l_debug = 1) THEN
862 mdebug('Before starting workflow: '||l_reason_name);
863 END IF;
864 wms_workflow_wrappers.wf_start_workflow
865 (
866 P_REASON_ID => p_sub_reason_id
867 ,P_REASON_NAME => l_reason_name
868 ,P_CALLING_PROGRAM_NAME => l_calling_program_name
869 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
870 ,P_SOURCE_SUBINVENTORY => p_sub_code
871 ,P_SOURCE_SUBINVENTORY_STATUS => l_status_code
872 ,P_UPDATE_STATUS_METHOD => l_update_method
873 ,X_RETURN_STATUS => lX_RETURN_STATUS
874 ,X_MSG_DATA => lX_MSG_DATA
875 ,X_MSG_COUNT => lX_MSG_COUNT
876 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
877 ,X_SUBINVENTORY => lX_SUBINVENTORY
878 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
879 ,X_LOCATOR => lX_LOCATOR
880 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
881 ,X_LPN_ID => lX_LPN_ID
882 ,X_LPN_STATUS => lX_LPN_STATUS
883 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
884 ,X_REVISION => lX_REVISION
885 ,X_LOT_NUMBER => lX_LOT_NUMBER
886 ,X_LOT_STATUS => lX_LOT_STATUS
887 ,X_QUANTITY => lX_QUANTITY
888 ,X_UOM_CODE => lX_UOM_CODE
889 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
890 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
891 ,X_RESERVATION_ID => lX_RESERVATION_ID
892 );
893
894 end if;
895 end if;
896
897 if p_loc_status_id >0 then
898 SELECT WORKFLOW_NAME
899 INTO l_workflow_name
900 FROM MTL_TRANSACTION_REASONS
901 WHERE REASON_ID = p_loc_reason_id;
902
903 SELECT REASON_NAME
904 INTO l_reason_name
905 FROM MTL_TRANSACTION_REASONS
906 WHERE REASON_ID = p_loc_reason_id;
907
908 SELECT status_code
909 INTO l_status_code
910 FROM MTL_MATERIAL_STATUSES_VL
911 WHERE status_id = p_loc_status_id;
912
913 if l_workflow_name is not null THEN
914
915 wms_workflow_wrappers.wf_start_workflow
916 (
917 P_REASON_ID => p_sub_reason_id
918 ,p_reason_name => l_reason_name
919 ,p_calling_program_name => l_calling_program_name
920 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
921 ,P_SOURCE_SUBINVENTORY => p_sub_code
922 ,P_SOURCE_LOCATOR => p_locator_id
923 ,P_SOURCE_LOCATOR_STATUS => l_status_code
924 ,P_UPDATE_STATUS_METHOD => l_update_method
925 ,X_RETURN_STATUS => lX_RETURN_STATUS
926 ,X_MSG_DATA => lX_MSG_DATA
927 ,X_MSG_COUNT => lX_MSG_COUNT
928 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
929 ,X_SUBINVENTORY => lX_SUBINVENTORY
930 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
931 ,X_LOCATOR => lX_LOCATOR
932 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
933 ,X_LPN_ID => lX_LPN_ID
934 ,X_LPN_STATUS => lX_LPN_STATUS
935 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
936 ,X_REVISION => lX_REVISION
937 ,X_LOT_NUMBER => lX_LOT_NUMBER
938 ,X_LOT_STATUS => lX_LOT_STATUS
939 ,X_QUANTITY => lX_QUANTITY
940 ,X_UOM_CODE => lX_UOM_CODE
941 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
942 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
943 ,X_RESERVATION_ID => lX_RESERVATION_ID
944 );
945 end if;
946 end if;
947
948 if p_lot_status_id >0 then
949 SELECT WORKFLOW_NAME
950 INTO l_workflow_name
951 FROM MTL_TRANSACTION_REASONS
952 WHERE REASON_ID = p_lot_reason_id;
953
954 SELECT REASON_NAME
955 INTO l_reason_name
956 FROM MTL_TRANSACTION_REASONS
957 WHERE REASON_ID = p_lot_reason_id;
958
959 SELECT status_code
960 INTO l_status_code
961 FROM MTL_MATERIAL_STATUSES_VL
962 WHERE status_id = p_lot_status_id;
963
964 if l_workflow_name is not null then
965 wms_workflow_wrappers.wf_start_workflow
966 (
967 P_REASON_ID => p_sub_reason_id
968 ,P_REASON_NAME => l_reason_name
969 ,p_calling_program_name => l_calling_program_name
970 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
971 ,P_SOURCE_SUBINVENTORY => p_sub_code
972 ,P_SOURCE_LOCATOR => p_locator_id
973 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
974 ,P_LOT_NUMBER => p_from_lot_number
975 ,P_TO_LOT_NUMBER => p_to_lot_number
976 ,P_LOT_STATUS => l_status_code
977 ,P_UPDATE_STATUS_METHOD => l_update_method
978 ,X_RETURN_STATUS => lX_RETURN_STATUS
979 ,X_MSG_DATA => lX_MSG_DATA
980 ,X_MSG_COUNT => lX_MSG_COUNT
981 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
982 ,X_SUBINVENTORY => lX_SUBINVENTORY
983 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
984 ,X_LOCATOR => lX_LOCATOR
985 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
986 ,X_LPN_ID => lX_LPN_ID
987 ,X_LPN_STATUS => lX_LPN_STATUS
988 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
989 ,X_REVISION => lX_REVISION
990 ,X_LOT_NUMBER => lX_LOT_NUMBER
991 ,X_LOT_STATUS => lX_LOT_STATUS
992 ,X_QUANTITY => lX_QUANTITY
993 ,X_UOM_CODE => lX_UOM_CODE
994 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
995 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
996 ,X_RESERVATION_ID => lX_RESERVATION_ID
997 );
998 end if;
999 end if;
1000
1001 if p_serial_status_id >0 then
1002 SELECT WORKFLOW_NAME
1003 INTO l_workflow_name
1004 FROM MTL_TRANSACTION_REASONS
1005 WHERE REASON_ID = p_serial_reason_id;
1006
1007 SELECT REASON_NAME
1008 INTO l_reason_name
1009 FROM MTL_TRANSACTION_REASONS
1010 WHERE REASON_ID = p_serial_reason_id;
1011
1012 SELECT status_code
1013 INTO l_status_code
1014 FROM MTL_MATERIAL_STATUSES_VL
1015 WHERE status_id = p_serial_status_id;
1016
1017 if l_workflow_name is not null then
1018 wms_workflow_wrappers.wf_start_workflow
1019 (
1020 P_REASON_ID => p_sub_reason_id
1021 ,P_REASON_NAME => l_reason_name
1022 ,p_calling_program_name => l_calling_program_name
1023 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1024 ,P_SOURCE_SUBINVENTORY => p_sub_code
1025 ,P_SOURCE_LOCATOR => p_locator_id
1026 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
1027 ,P_LOT_NUMBER => p_from_lot_number
1028 ,P_TO_LOT_NUMBER => p_to_lot_number
1029 ,P_SERIAL_NUMBER => p_from_SN
1030 ,P_TO_SERIAL_NUMBER => p_to_SN
1031 ,P_SERIAL_NUMBER_STATUS => l_status_code
1032 ,P_UPDATE_STATUS_METHOD => l_update_method
1033 ,X_RETURN_STATUS => lX_RETURN_STATUS
1034 ,X_MSG_DATA => lX_MSG_DATA
1035 ,X_MSG_COUNT => lX_MSG_COUNT
1036 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1037 ,X_SUBINVENTORY => lX_SUBINVENTORY
1038 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1039 ,X_LOCATOR => lX_LOCATOR
1040 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1041 ,X_LPN_ID => lX_LPN_ID
1042 ,X_LPN_STATUS => lX_LPN_STATUS
1043 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1044 ,X_REVISION => lX_REVISION
1045 ,X_LOT_NUMBER => lX_LOT_NUMBER
1046 ,X_LOT_STATUS => lX_LOT_STATUS
1047 ,X_QUANTITY => lX_QUANTITY
1048 ,X_UOM_CODE => lX_UOM_CODE
1049 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1050 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1051 ,X_RESERVATION_ID => lX_RESERVATION_ID
1052 );
1053 end if;
1054 end if;
1055
1056 -- Start of changes for # 6633612---------------
1057
1058 if p_onhand_status_id >0 then
1059 SELECT WORKFLOW_NAME
1060 INTO l_workflow_name
1061 FROM MTL_TRANSACTION_REASONS
1062 WHERE REASON_ID = p_onhand_reason_id;
1063
1064 SELECT REASON_NAME
1065 INTO l_reason_name
1066 FROM MTL_TRANSACTION_REASONS
1067 WHERE REASON_ID = p_onhand_reason_id;
1068
1069 SELECT status_code
1070 INTO l_status_code
1071 FROM MTL_MATERIAL_STATUSES_VL
1072 WHERE status_id = p_onhand_status_id;
1073
1074 IF (l_debug = 1) THEN
1075 mdebug('l_workflow_name: '||l_workflow_name);
1076 END IF;
1077
1078 if l_workflow_name is not null then
1079 IF (l_debug = 1) THEN
1080 mdebug('Before starting workflow: '||l_reason_name);
1081 END IF;
1082 wms_workflow_wrappers.wf_start_workflow
1083 (
1084 P_REASON_ID => p_sub_reason_id
1085 ,P_REASON_NAME => l_reason_name
1086 ,P_CALLING_PROGRAM_NAME => l_calling_program_name
1087 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1088 ,P_SOURCE_SUBINVENTORY => p_sub_code
1089 ,P_SOURCE_LOCATOR => p_locator_id
1090 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
1091 ,P_LOT_NUMBER => p_from_lot_number
1092 ,P_TO_LOT_NUMBER => p_to_lot_number
1093 ,P_LPN_ID => p_lpn_id -- Added for # 6633612
1094 ,P_ONHAND_STATUS => l_status_code -- -- Added for # 6633612 -- Needs to be added in the WMS file.
1095 ,P_UPDATE_STATUS_METHOD => l_update_method
1096 ,X_RETURN_STATUS => lX_RETURN_STATUS
1097 ,X_MSG_DATA => lX_MSG_DATA
1098 ,X_MSG_COUNT => lX_MSG_COUNT
1099 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1100 ,X_SUBINVENTORY => lX_SUBINVENTORY
1101 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1102 ,X_LOCATOR => lX_LOCATOR
1103 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1104 ,X_LPN_ID => lX_LPN_ID
1105 ,X_LPN_STATUS => lX_LPN_STATUS
1106 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1107 ,X_REVISION => lX_REVISION
1108 ,X_LOT_NUMBER => lX_LOT_NUMBER
1109 ,X_LOT_STATUS => lX_LOT_STATUS
1110 ,X_QUANTITY => lX_QUANTITY
1111 ,X_UOM_CODE => lX_UOM_CODE
1112 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1113 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1114 ,X_RESERVATION_ID => lX_RESERVATION_ID
1115 );
1116
1117 end if;
1118 end if;
1119
1120
1121 -- End of changes for # 6633612---------------
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 x_status := 'E';
1126
1127 END invoke_reason_wf;
1128 --Bug#5577767 Created this procedure to filter sec qty/uom based on tracking_quantity_ind.
1129 PROCEDURE TRACKING_QUANTITY_IND(p_item_id IN NUMBER, p_org_id IN NUMBER ,x_sec_qty IN OUT nocopy NUMBER,x_sec_uom IN OUT nocopy VARCHAR2) IS
1130 p_tracking_qty_ind VARCHAR2(10);
1131 BEGIN
1132 IF p_item_id IS NULL OR p_org_id IS NULL THEN
1133 x_sec_qty := NULL;
1134 x_sec_uom := NULL;
1135 ELSE
1136 SELECT tracking_quantity_ind INTO p_tracking_qty_ind
1137 FROM mtl_system_items_kfv
1138 WHERE inventory_item_id = p_item_id
1139 AND organization_id = p_org_id;
1140 IF p_tracking_qty_ind = 'P' THEN
1141 x_sec_qty := NULL;
1142 x_sec_uom := NULL;
1143 END IF;
1144 END IF;
1145 END TRACKING_QUANTITY_IND;
1146
1147 --added for lpn status project to check whether update transaction will result in mixed or not
1148 FUNCTION get_mixed_status(p_lpn_id NUMBER,
1149 p_organization_id NUMBER,
1150 p_outermost_lpn_id NUMBER,
1151 p_inventory_item_id NUMBER,
1152 p_lot_number VARCHAR2 := NULL,
1153 p_status_id NUMBER)
1154 RETURN VARCHAR2 is
1155
1156 CURSOR wlc_cur is
1157 SELECT *
1158 FROM wms_lpn_contents wlc
1159 WHERE wlc.parent_lpn_id IN
1160 (SELECT lpn_id
1161 FROM wms_license_plate_numbers plpn
1162 start with lpn_id = p_outermost_lpn_id
1163 connect by parent_lpn_id = prior lpn_id
1164 )
1165 and wlc.parent_lpn_id not in
1166 (SELECT lpn_id
1167 FROM wms_license_plate_numbers plpn
1168 start with lpn_id = p_lpn_id
1169 connect by parent_lpn_id = prior lpn_id
1170 );
1171 CURSOR wlc_item_cur is
1172 SELECT *
1173 FROM wms_lpn_contents wlc
1174 WHERE wlc.parent_lpn_id IN
1175 (SELECT lpn_id
1176 FROM wms_license_plate_numbers plpn
1177 start with lpn_id = p_outermost_lpn_id
1178 connect by parent_lpn_id = prior lpn_id
1179 );
1180 CURSOR msn_cur(l_inventory_item_id NUMBER,l_lpn_id NUMBER) is
1181 select status_id
1182 FROM mtl_serial_numbers
1183 where inventory_item_id = l_inventory_item_id
1184 AND lpn_id = l_lpn_id;
1185
1186 l_serial_controlled NUMBER := 0;
1187 l_serial_status_enabled NUMBER := 0;
1188 l_return_mixed NUMBER := 0;
1189 l_return_status VARCHAR2(10) := 'S';
1190 l_default_status_id NUMBER;
1191 BEGIN
1192 mdebug('In get_mixed_status');
1193 mdebug('Values Passed--------');
1194 mdebug('p_lpn_id '||p_lpn_id);
1195 mdebug('p_organization_id '||p_organization_id);
1196 mdebug('p_inventory_item_id '||p_inventory_item_id);
1197 mdebug('p_lot_number '||p_lot_number);
1198 mdebug('p_status_id '||p_status_id);
1199 mdebug('p_outermost_lpn_id '||p_outermost_lpn_id);
1200 IF(p_inventory_item_id is NULL) THEN
1201 mdebug('Item id is NULL so have to check for whole LPN case');
1202 FOR l_wlc_cur in wlc_cur loop
1203 l_serial_controlled := 0;
1204 l_serial_status_enabled := 0;
1205 IF inv_cache.set_item_rec(p_organization_id, l_wlc_cur.inventory_item_id) THEN
1206 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1207 l_serial_controlled := 1; -- Item is serial controlled
1208 END IF;
1209 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1210 l_serial_status_enabled := 1;
1211 END IF;
1212 END IF;
1213 mdebug('Inventory item id from wlc_cur '||l_wlc_cur.inventory_item_id);
1214 mdebug('parent_lpn_id from wlc_cur '||l_wlc_cur.parent_lpn_id);
1215 IF l_serial_controlled = 0 THEN
1216 mdebug('Item is not serial controlled so checking moqd for status');
1217 select DISTINCT status_id INTO l_default_status_id
1218 from mtl_onhand_quantities_detail
1219 WHERE lpn_id = l_wlc_cur.parent_lpn_id
1220 AND inventory_item_id = l_wlc_cur.inventory_item_id
1221 AND NVL(lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
1222 AND organization_id = p_organization_id;
1223 mdebug('status returned from moqd is '||l_default_status_id);
1224 IF(l_default_status_id <> p_status_id) THEN
1225 l_return_mixed := 1;
1226 END IF;
1227 ELSIF (l_serial_controlled = 1 AND l_serial_status_enabled = 1) THEN
1228 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1229 FOR l_msn_cur in msn_cur(l_wlc_cur.inventory_item_id,l_wlc_cur.parent_lpn_id) loop
1230 mdebug('MSN status is '||l_msn_cur.status_id);
1231 IF(l_msn_cur.status_id <>p_status_id)THEN
1232 l_return_mixed := 1;
1233 EXIT;
1234 END IF;
1235 END LOOP;
1236 END IF;
1237 IF(l_return_mixed = 1)THEN
1238 EXIT;
1239 END IF;
1240 END LOOP;
1241 IF (l_return_mixed =1)THEN
1242 l_return_status := 'M';
1243 ELSE
1244 l_return_status := 'S';
1245 END IF;
1246
1247 ELSE
1248 mdebug('Item id is not null ...');
1249 FOR l_wlc_item_cur in wlc_item_cur loop
1250 l_return_mixed := 0;
1251 IF(l_wlc_item_cur.inventory_item_id<>p_inventory_item_id
1252 OR NVL(l_wlc_item_cur.lot_number,'@@@@') <> NVL(p_lot_number,'@@@@')
1253 OR l_wlc_item_cur.parent_lpn_id <> p_lpn_id
1254 )THEN
1255 l_serial_controlled := 0;
1256 l_serial_status_enabled := 0;
1257 IF inv_cache.set_item_rec(p_organization_id, l_wlc_item_cur.inventory_item_id) THEN
1258 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1259 l_serial_controlled := 1; -- Item is serial controlled
1260 END IF;
1261 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1262 l_serial_status_enabled := 1;
1263 END IF;
1264 END IF;
1265 mdebug('Inventory item id from wlc_item_cur '||l_wlc_item_cur.inventory_item_id);
1266 mdebug('parent_lpn_id from wlc_item_cur '||l_wlc_item_cur.parent_lpn_id);
1267 IF l_serial_controlled = 0 then
1268 mdebug('Item is not serial controlled so checking moqd for status');
1269 select DISTINCT status_id into l_default_status_id
1270 from mtl_onhand_quantities_detail
1271 where lpn_id = l_wlc_item_cur.parent_lpn_id
1272 AND inventory_item_id = l_wlc_item_cur.inventory_item_id
1273 AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
1274 AND organization_id = p_organization_id;
1275 mdebug('status returned from moqd is '||l_default_status_id);
1276 IF(l_default_status_id <> p_status_id) THEN
1277 l_return_mixed := 1;
1278 END IF;
1279 ELSIF(l_serial_controlled = 1 AND l_serial_status_enabled = 1)THEN
1280 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1281 FOR l_msn_cur in msn_cur(l_wlc_item_cur.inventory_item_id,l_wlc_item_cur.parent_lpn_id) loop
1282 mdebug('MSN status is '||l_msn_cur.status_id);
1283 IF(l_msn_cur.status_id <>p_status_id)THEN
1284 l_return_mixed := 1;
1285 EXIT;
1286 END IF;
1287 END LOOP;
1288 END IF;
1289 END IF;
1290 IF(l_return_mixed = 1)THEN
1291 EXIT;
1292 END IF;
1293 END LOOP;
1294 IF (l_return_mixed =1)THEN
1295 l_return_status := 'M';
1296 ELSE
1297 l_return_status := 'S';
1298 END IF;
1299 END IF;
1300 RETURN l_return_status;
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 mdebug('Exception occured so returning M');
1304 RETURN 'M';
1305 END get_mixed_status;
1306 --bug 6952533
1307 FUNCTION get_mixed_status_serial(p_lpn_id NUMBER,
1308 p_organization_id NUMBER,
1309 p_outermost_lpn_id NUMBER,
1310 p_inventory_item_id NUMBER,
1311 p_lot_number VARCHAR2 := NULL,
1312 p_fm_sn VARCHAR2,
1313 p_to_sn VARCHAR2,
1314 p_status_id NUMBER)
1315 RETURN VARCHAR2 is
1316 CURSOR wlc_item_cur is
1317 SELECT *
1318 FROM wms_lpn_contents wlc
1319 WHERE wlc.parent_lpn_id IN
1320 (SELECT lpn_id
1321 FROM wms_license_plate_numbers plpn
1322 start with lpn_id = p_outermost_lpn_id
1323 connect by parent_lpn_id = prior lpn_id
1324 );
1325 CURSOR msn_cur(l_inventory_item_id NUMBER,l_lpn_id NUMBER) is
1326 select status_id
1327 FROM mtl_serial_numbers msn
1328 where inventory_item_id = l_inventory_item_id
1329 AND lpn_id = l_lpn_id
1330 AND nvl(msn.lot_number , '@@@@') = NVL(p_lot_number,'@@@@')
1331 AND msn.serial_number NOT IN (select serial_number
1332 from mtl_serial_numbers
1333 where serial_number between p_fm_sn AND p_to_sn);
1334
1335 l_serial_controlled NUMBER := 0;
1336 l_serial_status_enabled NUMBER := 0;
1337 l_return_mixed NUMBER := 0;
1338 l_return_status VARCHAR2(10) := 'S';
1339 l_default_status_id NUMBER;
1340 BEGIN
1341 FOR l_wlc_item_cur in wlc_item_cur loop
1342 l_return_mixed := 0;
1343 l_serial_controlled := 0;
1344 l_serial_status_enabled := 0;
1345 IF inv_cache.set_item_rec(p_organization_id, l_wlc_item_cur.inventory_item_id) THEN
1346 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1347 l_serial_controlled := 1; -- Item is serial controlled
1348 END IF;
1349 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1350 l_serial_status_enabled := 1;
1351 END IF;
1352 END IF;
1353 mdebug('Inventory item id from wlc_item_cur '||l_wlc_item_cur.inventory_item_id);
1354 mdebug('parent_lpn_id from wlc_item_cur '||l_wlc_item_cur.parent_lpn_id);
1355 IF l_serial_controlled = 0 then
1356 mdebug('Item is not serial controlled so checking moqd for status');
1357 select DISTINCT status_id into l_default_status_id
1358 from mtl_onhand_quantities_detail
1359 where lpn_id = l_wlc_item_cur.parent_lpn_id
1360 AND inventory_item_id = l_wlc_item_cur.inventory_item_id
1361 AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
1362 AND organization_id = p_organization_id;
1363 mdebug('status returned from moqd is '||l_default_status_id);
1364 IF(l_default_status_id <> p_status_id) THEN
1365 l_return_mixed := 1;
1366 END IF;
1367 ELSIF(l_serial_controlled = 1 AND l_serial_status_enabled = 1)THEN
1368 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1369 FOR l_msn_cur in msn_cur(l_wlc_item_cur.inventory_item_id,l_wlc_item_cur.parent_lpn_id) loop
1370 mdebug('MSN status is '||l_msn_cur.status_id);
1371 IF(l_msn_cur.status_id <>p_status_id)THEN
1372 l_return_mixed := 1;
1373 EXIT;
1374 END IF;
1375 END LOOP;
1376 END IF;
1377 IF(l_return_mixed = 1)THEN
1378 EXIT;
1379 END IF;
1380 END LOOP;
1381 IF l_return_mixed = 1 THEN
1382 l_return_status := 'M';
1383 END IF;
1384 RETURN l_return_status;
1385 EXCEPTION
1386 WHEN OTHERS THEN
1387 mdebug('Exception occured so returning M');
1388 RETURN 'M';
1389 END get_mixed_status_serial;
1390 --end of bug 6952533
1391 END INV_STATUS_PKG;