[Home] [Help]
PACKAGE BODY: APPS.INV_STATUS_PKG
Source
1 PACKAGE BODY INV_STATUS_PKG AS
2 /* $Header: INVUPMSB.pls 120.32 2012/01/20 09:10:21 skommine ship $ */
3
4 --Bug11826279, moving procedure as it's been called from get_from_status_code
5 PROCEDURE mdebug(msg in varchar2)
6 IS
7 l_msg VARCHAR2(5100);
8 l_ts VARCHAR2(30);
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
12
13 l_msg:=l_ts||' '||msg;
14
15 IF (l_debug = 1) THEN
16 inv_mobile_helper_functions.tracelog
17 (p_err_msg => l_msg,
18 p_module => 'INV_STATUS_PKG',
19 p_level => 4);
20 END IF;
21
22 --dbms_output.put_line(msg);
23 -- null;
24 END;
25
26 -- START SCHANDRU INVERES
27 function get_from_status_code ( p_org_id in number default null,
28 p_item_id in number default null,
29 p_sub_inv in varchar2 default null,
30 p_locator_id in number default null,
31 p_lot in varchar2 default null,
32 p_serial in varchar2 default null) return varchar2
33 as
34
35 PRAGMA AUTONOMOUS_TRANSACTION;
36
37 x_status_code varchar2(100) := NULL;
38
39 BEGIN
40
41 if (p_sub_inv IS NOT NULL ) then
42
43 select mms.status_code
44 into x_status_code
45 from mtl_material_statuses mms ,
46 MTL_SECONDARY_INVENTORIES msi
47 where mms.status_id = msi.status_id
48 and msi.SECONDARY_INVENTORY_NAME = p_sub_inv
49 and msi.organization_id = p_org_id;
50
51 elsif (p_locator_id IS NOT NULL ) then
52
53 select mms.status_code
54 into x_status_code
55 from mtl_material_statuses mms ,
56 MTL_ITEM_LOCATIONS_KFV mil
57 where mms.status_id = mil.status_id
58 and mil.INVENTORY_LOCATION_ID = p_locator_id
59 and mil.organization_id = p_org_id;
60
61 elsif (p_serial IS NOT NULL) then
62
63 select mms.status_code
64 into x_status_code
65 from mtl_material_statuses mms ,
66 MTL_SERIAL_NUMBERS msn
67 where mms.status_id = msn.status_id
68 and msn.SERIAL_NUMBER = p_serial
69 and msn.current_organization_id = p_org_id
70 and msn.inventory_item_id = p_item_id;
71
72 elsIF (p_lot IS NOT NULL) then
73
74 select mms.status_code
75 into x_status_code
76 from mtl_material_statuses mms ,
77 MTL_LOT_NUMBERS mln
78 where mms.status_id = mln.status_id
79 and mln.LOT_NUMBER = p_lot
80 and mln.organization_id = p_org_id
81 and mln.inventory_item_id = p_item_id;
82
83 end if;
84
85 return x_status_code;
86
87 exception when others then
88 return NULL;
89
90 end get_from_status_code ;
91 --END SCHANDRU INVERES
92
93 --Bug11826279,creating new function for onhand status change
94 function get_onhand_from_status ( p_org_id in number,
95 p_item_id in number,
96 p_sub_inv in varchar2,
97 p_locator_id in number default null,
98 p_lot in varchar2 default null,
99 p_lpn_id in number default null) return varchar2
100 as
101
102 PRAGMA AUTONOMOUS_TRANSACTION;
103
104 x_status_code varchar2(100) := NULL;
105 l_item_serial_check NUMBER;
106
107 BEGIN
108
109 SELECT SERIAL_NUMBER_CONTROL_CODE
110 INTO l_item_serial_check
111 FROM mtl_system_items
112 WHERE organization_id = p_org_id
113 AND inventory_item_id = p_item_id;
114
115 IF (l_item_serial_check IN (1,6)) THEN
116
117 BEGIN
118 mdebug('Item is not serial controlled');
119 select mms.status_code
120 into x_status_code
121 from mtl_material_statuses mms ,
122 mtl_onhand_quantities_detail moqd
123 where mms.status_id = moqd.status_id
124 and moqd.organization_id = p_org_id
125 and moqd.inventory_item_id = p_item_id
126 AND moqd.subinventory_code = p_sub_inv
127 AND Decode(p_locator_id,NULL,-999,moqd.locator_id) = Nvl(p_locator_id,-999)
128 AND Decode(p_lot,NULL,'@@@',moqd.lot_number) = Nvl(p_lot,'@@@')
129 AND Nvl(moqd.lpn_id,-999) = Decode(p_lpn_id,NULL,-999,p_lpn_id) --Bug12621577
130 AND rownum < 2;
131
132 exception when others then
133 mdebug('x_status_code:'||x_status_code);
134 mdebug('p_locator_id:'||p_locator_id);
135 mdebug('p_lot:'||p_lot);
136 mdebug('p_lot:'||p_lpn_id);
137 return NULL;
138 END;
139 END IF;
140
141 RETURN x_status_code;
142
143 EXCEPTION WHEN OTHERS THEN
144 RETURN NULL;
145
146 END get_onhand_from_status ;
147
148
149 PROCEDURE check_lot_range_status(
150 p_org_id IN NUMBER,
151 p_item_id IN NUMBER,
152 p_from_lot IN VARCHAR2,
153 p_to_lot IN VARCHAR2,
154 x_Status OUT nocopy VARCHAR2,
155 x_Message OUT nocopy VARCHAR2,
156 x_Status_Code OUT nocopy VARCHAR2
157 ) IS
158 lot_status_id NUMBER:=0;
159 first_row BOOLEAN := TRUE;
160 -- Bug# 1520495
161 l_lot_status_enabled VARCHAR2(1);
162 l_default_lot_status_id NUMBER;
163 l_serial_status_enabled VARCHAR2(1);
164 l_default_serial_status_id NUMBER;
165 l_return_status VARCHAR2(1);
166 l_msg_data VARCHAR2(2000);
167 l_msg_count NUMBER;
168
169
170 cursor lot_cur is
171 SELECT status_id
172 FROM MTL_LOT_NUMBERS
173 WHERE organization_id = p_org_id
174 AND inventory_item_id = p_item_id
175 AND lot_number BETWEEN p_from_lot AND p_to_lot;
176 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
177 BEGIN
178 x_Status := 'C';
179 FOR cc IN lot_cur LOOP
180 if first_row then
181 lot_status_id := cc.status_id;
182 first_row := FALSE;
183 elsif cc.status_id <> lot_status_id then
184 x_Status := 'E';
185 FND_MESSAGE.SET_NAME('WMS','WMS_MULTI_STATUS');
186 x_Message := FND_MESSAGE.GET;
187 -- x_Message := 'Multiple lot status';
188 end if;
189 END LOOP;
190
191 -- Bug# 1520495
192 -- From the above fetch, it is possible that lot_status_id is NULL in which
193 -- case get the default lot status id for the organization item.
194
195 if (x_Status <> 'E') AND (lot_status_id is null) then
196 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
197 p_organization_id => p_org_id
198 , p_inventory_item_id => p_item_id
199 , x_return_status => l_return_status
200 , x_msg_count => l_msg_count
201 , x_msg_data => l_msg_data
202 , x_lot_status_enabled => l_lot_status_enabled
203 , x_default_lot_status_id => l_default_lot_status_id
204 , x_serial_status_enabled => l_serial_status_enabled
205 , x_default_serial_status_id => l_default_serial_status_id);
206
207 if ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
208 x_Status:= 'E';
209 x_Message := l_msg_data;
210 end if;
211 if (NVL(l_lot_status_enabled, 'Y')='Y') then
212 lot_status_id := l_default_lot_status_id;
213 end if;
214 end if;
215 if x_Status = 'C' then
216 SELECT status_code
217 INTO x_status_code
218 FROM MTL_MATERIAL_STATUSES_VL
219 WHERE status_id = lot_status_id;
220 end if;
221
222 END check_lot_range_status;
223
224
225 PROCEDURE check_serial_range_status(
226 p_org_id IN NUMBER,
227 p_item_id IN NUMBER,
228 p_from_serial IN VARCHAR2,
229 p_to_serial IN VARCHAR2,
230 x_Status OUT nocopy VARCHAR2,
231 x_Message OUT nocopy VARCHAR2,
232 x_Status_Code OUT nocopy VARCHAR2
233 ) IS
234 serial_status_id NUMBER:=0;
235 first_row BOOLEAN := TRUE;
236 -- Bug# 1520495
237 l_lot_status_enabled VARCHAR2(1);
238 l_default_lot_status_id NUMBER;
239 l_serial_status_enabled VARCHAR2(1);
240 l_default_serial_status_id NUMBER;
241 l_return_status VARCHAR2(1);
242 l_msg_data VARCHAR2(2000);
243 l_msg_count NUMBER;
244
245 /* FP-J Lot/Serial Support Enhancements
246 * Add current status of resides in receiving
247 */
248 cursor serial_cur is
249 SELECT status_id
250 FROM MTL_SERIAL_NUMBERS
251 WHERE current_organization_id = p_org_id
252 AND inventory_item_id = p_item_id
253 --AND current_status in (1, 3, 5)
254 AND current_status in (1, 3, 5, 7)
255 AND serial_number BETWEEN p_from_serial AND p_to_serial;
256
257 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
258 BEGIN
259 x_Status := 'C';
260 FOR cc IN serial_cur LOOP
261 if first_row then
262 serial_status_id := cc.status_id;
263 first_row := FALSE;
264 elsif cc.status_id <> serial_status_id then
265 x_Status := 'E';
266 FND_MESSAGE.SET_NAME('WMS','WMS_MULTI_STATUS');
267 x_Message := FND_MESSAGE.GET;
268 -- x_Message := 'Multiple serial status';
269 end if;
270 END LOOP;
271
272 -- Bug# 1520495
273 -- From the above fetch, it is possible that serial_status_id is NULL in which
274 -- case get the default serial status id for the organization item.
275
276 if (x_Status <> 'E') AND (serial_status_id is null) then
277 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
278 p_organization_id => p_org_id
279 , p_inventory_item_id => p_item_id
280 , x_return_status => l_return_status
281 , x_msg_count => l_msg_count
282 , x_msg_data => l_msg_data
283 , x_lot_status_enabled => l_lot_status_enabled
284 , x_default_lot_status_id => l_default_lot_status_id
285 , x_serial_status_enabled => l_serial_status_enabled
286 , x_default_serial_status_id => l_default_serial_status_id);
287
288 if ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
289 x_Status:= 'E';
290 x_Message := l_msg_data;
291 end if;
292 if (NVL(l_serial_status_enabled, 'Y')='Y') then
293 serial_status_id := l_default_serial_status_id;
294 end if;
295 end if;
296
297 if x_Status = 'C' then
298 SELECT status_code
299 INTO x_status_code
300 FROM MTL_MATERIAL_STATUSES_VL
301 WHERE status_id = serial_status_id;
302 end if;
303
304 END check_serial_range_status;
305 --ERES Deferred
306 PROCEDURE post_sign_changes (p_event_name IN VARCHAR
307 ,p_transaction_id IN NUMBER
308 ,p_status_id IN NUMBER
309 ,p_pending_status IN NUMBER DEFAULT 1)
310 IS
311
312 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
313 TYPE rowidtab1 IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
314
315 CURSOR cur_onhand IS
316 select moqd.rowid FROM
317 mtl_onhand_quantities_detail moqd,
318 mtl_material_status_history mmsh
319 where moqd.inventory_item_id = mmsh.inventory_item_id
320 and moqd.organization_id = mmsh.organization_id
321 and moqd.subinventory_code = mmsh.zone_code
322 and nvl(moqd.locator_id, -9999) = nvl(mmsh.locator_id, Nvl(moqd.locator_id, -9999))
323 and nvl(moqd.lpn_id, -9999) = nvl(mmsh.lpn_id , -9999)
324 and nvl(moqd.lot_number, '@@@@') BETWEEN nvl (mmsh.lot_number, Nvl(moqd.lot_number, '@@@@'))
325 and nvl (mmsh.lot_number, Nvl(moqd.lot_number, '@@@@'))
326 and exists
327 (select 1 from mtl_system_items_b msi
328 where moqd.inventory_item_id = msi.inventory_item_id
329 AND moqd.organization_id = msi.organization_id
330 AND msi.serial_number_control_code in (1,6)
331 )
332 AND mmsh.group_id = p_transaction_id
333 FOR UPDATE NOWAIT;
334
335
336 CURSOR cur_onhserial IS
337 select msn.rowid FROM mtl_serial_numbers msn,
338 mtl_material_status_history mmsh
339 where msn.inventory_item_id = mmsh.inventory_item_id
340 and msn.current_organization_id = mmsh.organization_id
341 and msn.current_subinventory_code = mmsh.zone_code
342 and nvl(msn.current_locator_id, -9999) = nvl(mmsh.locator_id, Nvl(msn.current_locator_id, -9999))
343 and nvl(msn.lpn_id, -9999) = nvl(mmsh.lpn_id, -9999)
344 and nvl(msn.lot_number, '@@@@') BETWEEN nvl (mmsh.lot_number, Nvl(msn.lot_number, '@@@@'))
345 and nvl (mmsh.lot_number, Nvl(msn.lot_number, '@@@@'))
346 and current_status = 3
347 and exists
348 (select 1 from mtl_system_items_b msi
349 where msn.inventory_item_id = msi.inventory_item_id
350 AND msn.current_organization_id = msi.organization_id
351 AND nvl(msi.serial_status_enabled, 'N') = 'Y'
352 )
353 AND mmsh.group_id = p_transaction_id
354 FOR UPDATE NOWAIT;
355
356 CURSOR cur_Subinv IS
357 select ms.rowid FROM mtl_secondary_inventories ms,
358 mtl_material_status_history mmsh
359 where ms.organization_id = mmsh.organization_id
360 and ms.secondary_inventory_name = mmsh.zone_code
361 AND mmsh.status_update_id = p_transaction_id
362 FOR UPDATE NOWAIT;
363
364 CURSOR cur_Loc IS
365 select mil.rowid FROM mtl_item_locations mil,
366 mtl_material_status_history mmsh
367 where mil.organization_id = mmsh.organization_id
368 and mil.inventory_location_id = mmsh.locator_id
369 AND mmsh.status_update_id = p_transaction_id
370 FOR UPDATE NOWAIT;
371
372
373 CURSOR cur_Lot IS
374 select mln.rowid FROM mtl_lot_numbers mln,
375 mtl_material_status_history mmsh
376 where mln.organization_id = mmsh.organization_id
377 and mln.inventory_item_id = mmsh.inventory_item_id
378 AND mln.lot_number = mmsh.lot_number
379 AND mmsh.status_update_id = p_transaction_id
380 FOR UPDATE NOWAIT;
381
382 CURSOR cur_Serial IS
383 select msn.rowid FROM mtl_serial_numbers msn,
384 mtl_material_status_history mmsh
385 where msn.current_organization_id = mmsh.organization_id
386 and msn.inventory_item_id = mmsh.inventory_item_id
387 AND msn.serial_number = mmsh.serial_number
388 AND mmsh.status_update_id = p_transaction_id
389 FOR UPDATE NOWAIT;
390
391
392 l_signature_status VARCHAR2(24);
393 l_serial_status_enabled NUMBER;
394 l_old_status_id NUMBER := NULL;
395 rowid_list rowidtab;
396 rowid_list1 rowidtab1;
397 l_serial_status_control NUMBER := 1;
398 --ERED Deferred
399
400
401 BEGIN
402
403 l_signature_status := '';
404 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
405
406 mdebug('p_pending_status is:'||p_pending_status||'l_signature_status is:'||l_signature_status||'p_event_name is:'||p_event_name);
407 IF (p_pending_status = 0) OR (l_signature_status = 'SUCCESS') THEN
408 IF p_event_name = 'oracle.apps.inv.onhandStatus' THEN
409 mdebug('before updating MMSH');
410 UPDATE mtl_material_status_history
411 SET pending_status = 0
412 WHERE pending_status = 1
413 AND group_id = p_transaction_id;
414
415 COMMIT;
416 mdebug('before updating MOQD');
417 OPEN cur_onhand;
418 FETCH cur_onhand BULK COLLECT INTO rowid_list ;
419
420 FORALL j in rowid_list.first .. rowid_list.last
421 update mtl_onhand_quantities_detail
422 set status_id = p_status_id
423 , last_updated_by = FND_GLOBAL.USER_ID
424 , last_update_date = SYSDATE
425 , last_update_login = FND_GLOBAL.LOGIN_ID
426 where ROWID = rowid_list(j);
427 CLOSE cur_onhand;
428
429 mdebug(' update executed in sql: Onhand');
430
431 -- Added the serial status check for bug # 7113129
432 SELECT serial_control into l_serial_status_control
433 from mtl_material_statuses
434 WHERE status_id = p_status_id;
435
436 IF (l_serial_status_control = 1) THEN
437 OPEN cur_onhserial;
438 FETCH cur_onhserial BULK COLLECT INTO rowid_list1 ;
439
440 FORALL j in rowid_list1.first .. rowid_list1.last
441
442 update mtl_serial_numbers
443 set status_id = p_status_id
444 , last_updated_by = FND_GLOBAL.USER_ID
445 , last_update_date = SYSDATE
446 , last_update_login = FND_GLOBAL.LOGIN_ID
447 where ROWID = rowid_list1(j);
448 CLOSE cur_onhserial;
449
450 END IF;
451 ELSIF p_event_name = 'oracle.apps.inv.subinvStatus' THEN
452 UPDATE mtl_material_status_history
453 SET pending_status = 0
454 WHERE pending_status = 1
455 AND status_update_id = p_transaction_id;
456
457 COMMIT;
458
459 OPEN cur_Subinv;
460 FETCH cur_Subinv BULK COLLECT INTO rowid_list ;
461
462 FORALL j in rowid_list.first .. rowid_list.last
463 update mtl_secondary_inventories
464 set status_id = p_status_id
465 , last_updated_by = FND_GLOBAL.USER_ID
466 , last_update_date = SYSDATE
467 , last_update_login = FND_GLOBAL.LOGIN_ID
468 where ROWID = rowid_list(j);
469 CLOSE cur_Subinv;
470
471 mdebug(' update executed in sql: Onhand');
472
473
474 ELSIF p_event_name = 'oracle.apps.inv.loctStatus' THEN
475 UPDATE mtl_material_status_history
476 SET pending_status = 0
477 WHERE pending_status = 1
478 AND status_update_id = p_transaction_id;
479
480 COMMIT;
481
482 OPEN cur_Loc;
483 FETCH cur_Loc BULK COLLECT INTO rowid_list ;
484
485 FORALL j in rowid_list.first .. rowid_list.last
486 update mtl_item_locations
487 set status_id = p_status_id
488 , last_updated_by = FND_GLOBAL.USER_ID
489 , last_update_date = SYSDATE
490 , last_update_login = FND_GLOBAL.LOGIN_ID
491 where ROWID = rowid_list(j);
492 CLOSE cur_Loc;
493
494 mdebug(' update executed in sql: Onhand');
495 ELSIF p_event_name = 'oracle.apps.inv.lotStatus' THEN
496 UPDATE mtl_material_status_history
497 SET pending_status = 0
498 WHERE pending_status = 1
499 AND status_update_id = p_transaction_id;
500
501 COMMIT;
502
503 OPEN cur_Lot;
504 FETCH cur_Lot BULK COLLECT INTO rowid_list ;
505
506 FORALL j in rowid_list.first .. rowid_list.last
507 update mtl_lot_numbers
508 set status_id = p_status_id
509 , last_updated_by = FND_GLOBAL.USER_ID
510 , last_update_date = SYSDATE
511 , last_update_login = FND_GLOBAL.LOGIN_ID
512 where ROWID = rowid_list(j);
513 CLOSE cur_Lot;
514
515 mdebug(' update executed in sql: Onhand');
516
517
518 ELSIF p_event_name = 'oracle.apps.inv.serialStatus' THEN
519 UPDATE mtl_material_status_history
520 SET pending_status = 0
521 WHERE pending_status = 1
522 AND status_update_id = p_transaction_id;
523
524 COMMIT;
525
526 OPEN cur_Serial;
527 FETCH cur_Serial BULK COLLECT INTO rowid_list ;
528
529 FORALL j in rowid_list.first .. rowid_list.last
530 update mtl_serial_numbers
531 set status_id = p_status_id
532 , last_updated_by = FND_GLOBAL.USER_ID
533 , last_update_date = SYSDATE
534 , last_update_login = FND_GLOBAL.LOGIN_ID
535 where ROWID = rowid_list(j);
536 CLOSE cur_Serial;
537
538 mdebug(' update executed in sql: Onhand');
539
540 END IF;
541
542 END IF;
543
544
545
546 IF (l_signature_status = 'REJECTED' OR l_signature_status = 'TIMEDOUT') THEN
547 mdebug('ERES Rejected');
548 DELETE FROM mtl_material_status_history
549 WHERE pending_status = 1
550 AND (group_id = p_transaction_id OR status_update_id = p_transaction_id);
551 COMMIT;
552 END IF;
553
554 COMMIT;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 NULL ;
559 -- x_status := 'E';
560 -- FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
561 -- x_Message := FND_MESSAGE.GET;
562 END;
563 --ERES Deferred
564
565 PROCEDURE update_status(
566 p_update_method IN NUMBER
567 , p_organization_id IN NUMBER
568 , p_inventory_item_id IN NUMBER
569 , p_sub_code IN VARCHAR2
570 , p_sub_status_id IN NUMBER
571 , p_sub_reason_id IN NUMBER
572 , p_locator_id IN NUMBER
573 , p_loc_status_id IN NUMBER
574 , p_loc_reason_id IN NUMBER
575 , p_from_lot_number IN VARCHAR2
576 , p_to_lot_number IN VARCHAR2
577 , p_lot_status_id IN NUMBER
578 , p_lot_reason_id IN NUMBER
579 , p_from_SN IN VARCHAR2
580 , p_to_SN IN VARCHAR2
581 , p_serial_status_id IN NUMBER
582 , p_serial_reason_id IN NUMBER
583 , x_Status OUT nocopy VARCHAR2
584 , x_Message OUT nocopy VARCHAR2
585 , p_update_from_mobile IN VARCHAR2 DEFAULT 'Y'
586 -- NSRIVAST, INVCONV , Start
587 , p_grade_code IN VARCHAR2 DEFAULT NULL
588 , p_primary_onhand IN NUMBER DEFAULT NULL
589 , p_secondary_onhand IN NUMBER DEFAULT NULL
590 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
591 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
592 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
593 -- NSRIVAST, INVCONV , End
594 , p_lpn_indicator IN NUMBER DEFAULT 1 --bug12621577
595 , p_group_id IN NUMBER DEFAULT -999 --ERES Deferred
596 , p_pending_status IN NUMBER DEFAULT 0 --ERES Deferred
597
598 )
599 IS
600 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
601 dummy varchar2(100);
602 -- Added below two variables for LPN status Project
603 l_serial_controlled NUMBER;
604 l_serial_status_enabled NUMBER;
605
606 l_multiple_app NUMBER := 0; --ERES Deferred
607 l_old_status_id NUMBER := NULL; -- Bug 10359438
608
609
610
611 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
612 rowid_list rowidtab;
613 -- Added for bug # 6882196
614 TYPE rowidtab1 IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
615 rowid_list1 rowidtab1;
616
617 cursor cur_lot_number is
618 SELECT lot_number
619 FROM MTL_LOT_NUMBERS
620 WHERE organization_id = p_organization_id
621 AND inventory_item_id = p_inventory_item_id
622 AND lot_number between p_from_lot_number and p_to_lot_number;
623
624 -- Added the cur_onhand for bug 6633612
625 -- Added the cursor CUR_ONHSERIAL and modified the exists clause in the cusror CUR_ONHAND for bug# 6633612
626
627 CURSOR cur_onhand IS
628 select moqd.rowid FROM mtl_onhand_quantities_detail moqd
629 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
630 and organization_id = p_organization_id
631 and subinventory_code = Nvl(p_sub_code, subinventory_code)
632 and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
633 and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1))
634 OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0))) --bug12621577 modifying changes done in Bug 7012984
635 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
636 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
637 and exists
638 (select 1 from mtl_system_items_b msi
639 where moqd.inventory_item_id = msi.inventory_item_id
640 AND moqd.organization_id = msi.organization_id
641 AND msi.serial_number_control_code in (1,6)
642 )
643 FOR UPDATE NOWAIT;
644 --Bug#11826279
645 CURSOR cur_onhand_status IS
646 select moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.lot_number,moqd.lpn_id,
647 Sum(moqd.primary_transaction_quantity) primary_onhand
648 FROM mtl_onhand_quantities_detail moqd
649 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
650 and organization_id = p_organization_id
651 and subinventory_code = Nvl(p_sub_code, subinventory_code)
652 and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
653 and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1)) --bug12621577
654 OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0)))
655 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
656 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
657 GROUP BY moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.lot_number,moqd.lpn_id;
658
659 CURSOR cur_onhserial IS
660 select msn.rowid FROM mtl_serial_numbers msn
661 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
662 and current_organization_id = p_organization_id
663 and current_subinventory_code = Nvl(p_sub_code, current_subinventory_code)
664 and nvl(current_locator_id, -9999) = nvl(p_locator_id, Nvl(current_locator_id, -9999))
665 and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1))
666 OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0))) --bug12621577 modifying changes done in Bug 7012984
667 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
668 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
669 and current_status = 3
670 and exists
671 (select 1 from mtl_system_items_b msi
672 where msn.inventory_item_id = msi.inventory_item_id
673 AND msn.current_organization_id = msi.organization_id
674 AND nvl(msi.serial_status_enabled, 'N') = 'Y'
675 )
676 FOR UPDATE NOWAIT;
677 --LPN status project
678
679 CURSOR wlc_cur
680 IS
681 SELECT *
682 FROM wms_lpn_contents wlc
683 WHERE wlc.parent_lpn_id IN
684 (SELECT lpn_id
685 FROM wms_license_plate_numbers plpn
686 start with lpn_id = p_lpn_id
687 connect by parent_lpn_id = prior lpn_id
688 )
689 order by serial_summary_entry
690 FOR UPDATE NOWAIT;
691
692 --LPN status project end
693
694
695
696 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
697 l_allow_mixed_status NUMBER := NVL(FND_PROFILE.VALUE('WMS_ALLOW_MIXED_STATUS'),2);--lpn status project
698 l_ret_status varchar2(20) := 'S';
699 l_outermost_lpn_id NUMBER;
700 l_count NUMBER :=0; -- Bug 6798024
701 l_serial_status_control NUMBER := 1;--bug 6952533
702 l_status_group_id NUMBER := NULL; --Bug#11826279
703 --ERES Deferred
704 l_eres_enabled VARCHAR2(3) := NVL(fnd_profile.VALUE('INV_DEF_ERES_ENABLED'), 'N');
705 l_pending_eres_chk NUMBER :=0;
706 -- e_invalid_update EXCEPTION;
707
708 BEGIN
709 IF (l_debug = 1) THEN
710 mdebug('p_group_id is: '||p_group_id);
711 END IF;
712
713 l_multiple_app := NVL(FND_PROFILE.VALUE('INV_ERES_MULTIPLE_SIGNATURE'),0); --ERES Deferred
714
715 --BEGIN SCHANDRU INVERES
716 IF p_update_from_mobile = 'Y' THEN
717 SAVEPOINT INV_UPDATE_STATUS;
718 END IF;
719 -- SAVEPOINT INV_UPDATE_STATUS;
720 --END SCHANDRU INVERES
721 IF (l_debug = 1) THEN
722 mdebug('in update status');
723 END IF;
724
725 --ERES Deferred
726 /*BEGIN
727 IF (l_eres_enabled <> 'N') THEN
728 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
729 mdebug('p_organization_id is:'||p_organization_id);
730 mdebug('p_sub_code is:'||p_sub_code);
731 SELECT Count(1) INTO l_pending_eres_chk
732 FROM mtl_material_status_history
733 WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
734 AND organization_id = p_organization_id
735 AND zone_code = NVL(p_sub_code,zone_code)
736 AND Decode(p_locator_id,NULL,-999,locator_id) = Nvl(p_locator_id,-999)
737 AND Decode(p_from_lot_number,NULL,'@@@',lot_number) = Nvl(p_from_lot_number,'@@@')
738 AND Decode(p_lpn_id,NULL,-999,lpn_id) = Nvl(p_lpn_id,-999)
739 AND pending_status = 1
740 AND ROWNUM = 1 ;
741
742 END IF;
743
744 IF (l_pending_eres_chk = 1) THEN
745 RAISE e_invalid_update;
746 END IF;
747
748 EXCEPTION WHEN e_invalid_update THEN
749 mdebug(' IN e_invalid_update EXCEPTION ');
750 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
751 x_message := FND_MESSAGE.GET;
752 x_status :='E';
753 RETURN;
754 END; */
755 --ERES Deferred
756
757 x_Status := 'C';
758 l_status_rec.organization_id := p_organization_id;
759 l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_manual;
760
761 IF (l_debug = 1) THEN
762 mdebug('p_sub_status_id: '||p_sub_status_id);
763 END IF;
764 if p_sub_status_id >0 then
765 --ERES Deferred
766 BEGIN
767
768 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
769 mdebug('p_organization_id is:'||p_organization_id);
770 mdebug('p_sub_code is:'||p_sub_code);
771 SELECT Count(1) INTO l_pending_eres_chk
772 FROM mtl_material_status_history
773 WHERE organization_id = p_organization_id
774 AND zone_code = p_sub_code
775 AND locator_id is null
776 AND inventory_item_id is null
777 AND lot_number is null AND serial_number is null
778 AND pending_status = 1
779 AND ROWNUM = 1 ;
780
781 EXCEPTION WHEN NO_DATA_FOUND THEN
782 l_pending_eres_chk := 0;
783 END;
784 IF (l_pending_eres_chk = 1) THEN
785 mdebug(' IN e_invalid_update EXCEPTION ');
786 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
787 x_message := FND_MESSAGE.GET;
788 x_status :='E';
789 RETURN;
790 END IF;
791
792 --ERES Deferred
793
794 IF p_pending_status = 0 THEN --ERES Deferred
795 update mtl_secondary_inventories
796 set status_id = p_sub_status_id
797 , last_updated_by = FND_GLOBAL.USER_ID
798 , last_update_date = SYSDATE
799 , last_update_login = FND_GLOBAL.LOGIN_ID
800 where organization_id = p_organization_id
801 and secondary_inventory_name = p_sub_code;
802 END IF;
803 l_status_rec.zone_code := p_sub_code;
804 l_status_rec.status_id := p_sub_status_id;
805 l_status_rec.update_reason_id := p_sub_reason_id;
806 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
807 l_status_rec.initial_status_flag := 'N';
808 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
809
810 l_status_rec.pending_status := p_pending_status; --ERES Deferred
811
812 -- update the status history
813 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
814 end if;
815 IF (l_debug = 1) THEN
816 mdebug('p_loc_status_id: '||p_loc_status_id);
817 END IF;
818 if p_loc_status_id >0 then
819 --ERES Deferred
820 BEGIN
821
822 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
823 mdebug('p_organization_id is:'||p_organization_id);
824 mdebug('p_sub_code is:'||p_sub_code);
825 SELECT Count(1) INTO l_pending_eres_chk
826 FROM mtl_material_status_history
827 WHERE organization_id = p_organization_id
828 AND locator_id = p_locator_id
829 AND inventory_item_id is null
830 AND lot_number is null AND serial_number is null
831 AND pending_status = 1
832 AND ROWNUM = 1 ;
833
834 EXCEPTION WHEN NO_DATA_FOUND THEN
835 l_pending_eres_chk := 0;
836 END;
837
838 IF (l_pending_eres_chk = 1) THEN
839 mdebug(' IN e_invalid_update EXCEPTION ');
840 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
841 x_message := FND_MESSAGE.GET;
842 x_status :='E';
843 RETURN;
844 END IF;
845
846 --ERES Deferred
847
848 IF p_pending_status = 0 THEN --ERES Deferred
849 update mtl_item_locations
850 set status_id = p_loc_status_id
851 , last_updated_by = FND_GLOBAL.USER_ID
852 , last_update_date = SYSDATE
853 , last_update_login = FND_GLOBAL.LOGIN_ID
854 where organization_id = p_organization_id
855 and inventory_location_id = p_locator_id;
856 END IF;
857 l_status_rec.zone_code := p_sub_code;
858 l_status_rec.locator_id := p_locator_id;
859 l_status_rec.status_id := p_loc_status_id;
860 l_status_rec.update_reason_id := p_loc_reason_id;
861 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
862 l_status_rec.initial_status_flag := 'N';
863 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
864
865 l_status_rec.pending_status := p_pending_status; --ERES Deferred
866
867 -- update the status history
868 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
869 end if;
870 IF (l_debug = 1) THEN
871 mdebug('p_lot_status_id: '||p_lot_status_id);
872 END IF;
873 if p_lot_status_id >0 then
874 --ERES Deferred
875 BEGIN
876
877 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
878 mdebug('p_organization_id is:'||p_organization_id);
879 mdebug('p_sub_code is:'||p_sub_code);
880 SELECT Count(1) INTO l_pending_eres_chk
881 FROM mtl_material_status_history
882 WHERE inventory_item_id = p_inventory_item_id
883 AND organization_id = p_organization_id
884 AND lot_number BETWEEN p_from_lot_number and p_to_lot_number
885 AND zone_code is null
886 AND locator_id is null
887 AND serial_number is null
888 AND pending_status = 1
889 AND ROWNUM = 1 ;
890
891 EXCEPTION WHEN NO_DATA_FOUND THEN
892 l_pending_eres_chk := 0;
893 END;
894
895
896 IF (l_pending_eres_chk = 1) THEN
897 mdebug(' IN e_invalid_update EXCEPTION ');
898 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
899 x_message := FND_MESSAGE.GET;
900 x_status :='E';
901 RETURN;
902
903 END IF;
904
905 --ERES Deferred
906
907 IF p_pending_status = 0 THEN --ERES Deferred
908 update mtl_lot_numbers
909 set status_id = p_lot_status_id
910 , last_updated_by = FND_GLOBAL.USER_ID
911 , last_update_date = SYSDATE
912 , last_update_login = FND_GLOBAL.LOGIN_ID
913 where organization_id = p_organization_id
914 and inventory_item_id = p_inventory_item_id
915 and lot_number BETWEEN p_from_lot_number and p_to_lot_number ;
916 END IF;
917 -- update status history
918 l_status_rec.inventory_item_id := p_inventory_item_id;
919 l_status_rec.status_id := p_lot_status_id;
920 l_status_rec.update_reason_id := p_lot_reason_id;
921 -- NSRIVAST, INVCONV , Start
922 l_status_rec.grade_code := p_grade_code ;
923 l_status_rec.primary_onhand := p_primary_onhand ;
924 l_status_rec.secondary_onhand := p_secondary_onhand ;
925
926
927
928 -- NSRIVAST, INVCONV , End
929 FOR cc IN cur_lot_number LOOP
930 l_status_rec.lot_number := cc.lot_number;
931 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
932 l_status_rec.initial_status_flag := 'N';
933 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
934 l_status_rec.pending_status := p_pending_status; --ERES Deferred
935
936 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
937 END LOOP;
938 end if;
939 IF (l_debug = 1) THEN
940 mdebug('p_serial_status_id: '||p_serial_status_id);
941 END IF;
942 if p_serial_status_id >0 then
943 --ERES Deferred
944 BEGIN
945
946 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
947 mdebug('p_organization_id is:'||p_organization_id);
948 mdebug('p_sub_code is:'||p_sub_code);
949 SELECT Count(1) INTO l_pending_eres_chk
950 FROM mtl_material_status_history
951 WHERE inventory_item_id = p_inventory_item_id
952 AND organization_id = p_organization_id
953 AND serial_number BETWEEN p_from_SN AND p_to_SN
954 AND zone_code is null
955 AND locator_id is null
956 AND pending_status = 1
957 AND ROWNUM = 1 ;
958
959
960 EXCEPTION WHEN NO_DATA_FOUND THEN
961 l_pending_eres_chk := 0;
962 END;
963
964 IF (l_pending_eres_chk = 1) THEN
965 mdebug(' IN e_invalid_update EXCEPTION ');
966 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
967 x_message := FND_MESSAGE.GET;
968 x_status :='E';
969 RETURN;
970 END IF;
971
972 --ERES Deferred
973
974 IF p_pending_status = 0 THEN --ERES Deferred
975 update mtl_serial_numbers
976 set status_id = p_serial_status_id
977 , last_updated_by = FND_GLOBAL.USER_ID
978 , last_update_date = SYSDATE
979 , last_update_login = FND_GLOBAL.LOGIN_ID
980 where current_organization_id = p_organization_id
981 and inventory_item_id = p_inventory_item_id
982 and serial_number BETWEEN p_from_SN AND p_to_SN;
983 END If;
984 l_status_rec.inventory_item_id := p_inventory_item_id;
985 l_status_rec.status_id := p_serial_status_id;
986 l_status_rec.update_reason_id := p_serial_reason_id;
987 l_status_rec.serial_number := p_from_SN;
988 l_status_rec.to_serial_number := p_to_SN;
989 -- Bug# 1695432 added initial_status_flag and from_mobile_apps_flag
990 l_status_rec.initial_status_flag := 'N';
991 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
992 -- update the status history
993 l_status_rec.pending_status := p_pending_status; --ERES Deferred
994
995 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
996
997 end if;
998
999 ------Start of changes for # 6633612---------------
1000 IF (l_debug = 1) THEN
1001 mdebug('p_onhand_status_id: '||p_onhand_status_id);
1002 END IF;
1003
1004 if p_onhand_status_id >0 and p_serial_status_id = 0 then
1005 ---lpn status project start for updating full lpn case
1006 IF (p_lpn_id IS NOT NULL AND p_inventory_item_id IS NULL)THEN
1007 --bug 6952533
1008 SELECT serial_control into l_serial_status_control
1009 from mtl_material_statuses
1010 WHERE status_id = p_onhand_status_id;
1011 --end of bug 6952533
1012 IF(l_allow_mixed_status = 2)THEN
1013 SELECT outermost_lpn_id into l_outermost_lpn_id
1014 FROM wms_license_plate_numbers
1015 WHERE lpn_id = p_lpn_id ;
1016 l_ret_status := get_mixed_status(p_lpn_id => p_lpn_id,
1017 p_organization_id =>p_organization_id,
1018 p_outermost_lpn_id => l_outermost_lpn_id,
1019 p_inventory_item_id => NULL,
1020 p_lot_number => NULL,
1021 p_status_id =>p_onhand_status_id);
1022 IF (l_ret_status = 'M') THEN
1023 x_status := 'M';
1024 RETURN;
1025 END IF;
1026 END IF;
1027 FOR l_wlc_cur IN wlc_cur LOOP
1028 l_serial_controlled := 0;
1029 l_serial_status_enabled := 0;
1030 mdebug('in mass update of lpn '||p_onhand_status_id);
1031 IF inv_cache.set_item_rec(p_organization_id, l_wlc_cur.inventory_item_id) THEN
1032 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1033 l_serial_controlled := 1; -- Item is serial controlled
1034 END IF;
1035 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1036 l_serial_status_enabled := 1;
1037 --bug 6952533
1038 IF(l_serial_status_control = 2)THEN
1039 x_status := 'E';
1040 FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
1041 x_Message := FND_MESSAGE.GET;
1042 RETURN;
1043 END IF;
1044 --end of bug 6952533
1045 END IF;
1046 END IF;
1047 IF(l_serial_controlled = 0)THEN
1048 UPDATE mtl_onhand_quantities_detail
1049 SET status_id = p_onhand_status_id
1050 , last_updated_by = FND_GLOBAL.USER_ID
1051 , last_update_date = SYSDATE
1052 , last_update_login = FND_GLOBAL.LOGIN_ID
1053 WHERE inventory_item_id = l_wlc_cur.inventory_item_id
1054 AND organization_id = p_organization_id
1055 AND subinventory_code = Nvl(p_sub_code,'@@@@')
1056 AND locator_id = Nvl(p_locator_id ,-9999)
1057 AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_cur.lot_number,'@@@@')
1058 AND lpn_id = l_wlc_cur.parent_lpn_id;
1059 ELSIF(l_serial_status_enabled = 1)THEN
1060 UPDATE mtl_serial_numbers
1061 set status_id = p_onhand_status_id
1062 , last_updated_by = FND_GLOBAL.USER_ID
1063 , last_update_date = SYSDATE
1064 , last_update_login = FND_GLOBAL.LOGIN_ID
1065 WHERE inventory_item_id = l_wlc_cur.inventory_item_id
1066 AND current_organization_id = p_organization_id
1067 AND current_subinventory_code = Nvl(p_sub_code,'@@@@')
1068 AND current_locator_id = Nvl(p_locator_id ,-9999)
1069 AND lpn_id = l_wlc_cur.parent_lpn_id;
1070 END IF;
1071 END LOOP;
1072
1073
1074 --LPN status project end for full lpn update
1075 --bug 6952533
1076 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
1077 mdebug('came here for updating serial in range to status '|| p_onhand_status_id);
1078
1079 IF ( l_allow_mixed_status = 2) THEN
1080 SELECT outermost_lpn_id into l_outermost_lpn_id
1081 FROM wms_license_plate_numbers
1082 WHERE lpn_id = p_lpn_id ;
1083 l_ret_status := get_mixed_status_serial(p_lpn_id => p_lpn_id,
1084 p_organization_id =>p_organization_id ,
1085 p_outermost_lpn_id => l_outermost_lpn_id,
1086 p_inventory_item_id => p_inventory_item_id,
1087 p_lot_number => p_from_lot_number,
1088 p_fm_sn => p_from_SN,
1089 p_to_sn => p_to_SN,
1090 p_status_id => p_onhand_status_id);
1091 IF (l_ret_status = 'M') THEN
1092 x_status := 'M';
1093 RETURN;
1094 END IF;
1095 END IF;
1096
1097 update mtl_serial_numbers
1098 set status_id = p_onhand_status_id
1099 , last_updated_by = FND_GLOBAL.USER_ID
1100 , last_update_date = SYSDATE
1101 , last_update_login = FND_GLOBAL.LOGIN_ID
1102 where lpn_id = p_lpn_id
1103 AND current_organization_id = p_organization_id
1104 AND inventory_item_id = p_inventory_item_id
1105 AND serial_number BETWEEN p_from_SN AND p_to_SN
1106 AND Nvl(lot_number,'@@@@') = Nvl(p_from_lot_number,'@@@@');
1107
1108 --end of bug 6952533
1109
1110 ELSE
1111 --lpn status project
1112 IF(p_lpn_id IS NOT null)THEN
1113 IF(l_allow_mixed_status = 2)THEN
1114 SELECT outermost_lpn_id into l_outermost_lpn_id
1115 FROM wms_license_plate_numbers
1116 WHERE lpn_id = p_lpn_id;
1117 l_ret_status := get_mixed_status(p_lpn_id => p_lpn_id,
1118 p_organization_id =>p_organization_id,
1119 p_outermost_lpn_id => l_outermost_lpn_id,
1120 p_inventory_item_id => p_inventory_item_id,
1121 p_lot_number => p_from_lot_number,
1122 p_status_id =>p_onhand_status_id);
1123 IF l_ret_status = 'M' THEN
1124 x_status := 'M';
1125 RETURN;
1126 END IF;
1127 END IF;
1128 END IF;
1129 --lpn status project
1130
1131 begin
1132
1133 --ERES Deferred
1134 BEGIN
1135 mdebug('p_inventory_item_id is:'||p_inventory_item_id);
1136 mdebug('p_organization_id is:'||p_organization_id);
1137 mdebug('p_sub_code is:'||p_sub_code);
1138 mdebug('p_locator_id is:'||p_locator_id);
1139 mdebug('p_from_lot_number is:'||p_from_lot_number);
1140 mdebug('p_to_lot_number is:'||p_to_lot_number);
1141 mdebug('p_lpn_id is:'||p_lpn_id);
1142 SELECT Count(1) INTO l_pending_eres_chk
1143 FROM mtl_material_status_history
1144 WHERE inventory_item_id = NVL(p_inventory_item_id,inventory_item_id)
1145 AND organization_id = p_organization_id
1146 AND zone_code = NVL(p_sub_code,zone_code)
1147 AND nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
1148 AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1149 and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
1150 and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
1151 AND pending_status = 1
1152 AND ROWNUM = 1 ;
1153
1154 EXCEPTION WHEN NO_DATA_FOUND THEN
1155 l_pending_eres_chk := 0;
1156
1157 END;
1158 IF (l_pending_eres_chk = 1) THEN
1159 mdebug(' IN e_invalid_update EXCEPTION ');
1160 FND_MESSAGE.SET_NAME('INV','INV_ERES_IN_PROCESS');
1161 x_message := FND_MESSAGE.GET;
1162 x_status :='E';
1163 RETURN;
1164 END IF;
1165 --ERES Deferred
1166
1167
1168 /*Bug 10359438. Moved the below code and added select query to retrieve old status id of the record. */
1169 /* Bug 6917621 */
1170 if (p_inventory_item_id is not null) then
1171 l_status_rec.inventory_item_id := p_inventory_item_id;
1172 end if;
1173 l_status_rec.zone_code := p_sub_code;
1174 l_status_rec.locator_id := p_locator_id;
1175 l_status_rec.lpn_id := p_lpn_id;
1176 l_status_rec.status_id := p_onhand_status_id;
1177 l_status_rec.update_reason_id := p_onhand_reason_id;
1178 l_status_rec.initial_status_flag := 'N';
1179 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
1180
1181 FOR cc IN cur_lot_number LOOP -- To update all the lots in a given sub, locator combination..
1182 l_status_rec.lot_number := cc.lot_number;
1183 l_status_rec.initial_status_flag := 'N';
1184 l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
1185 -- Bug 6798024
1186 l_count := l_count + 1;
1187
1188 begin
1189 select status_id into l_old_status_id
1190 from mtl_onhand_quantities_detail moqd
1191 where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
1192 and organization_id = p_organization_id
1193 and subinventory_code = Nvl(p_sub_code, subinventory_code)
1194 and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
1195 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1196 and nvl(lot_number, '@@@@') = l_status_rec.lot_number ;
1197 exception
1198 when others then
1199 l_old_status_id := NULL ;
1200 end ;
1201
1202 if NVL(l_old_status_id, -9999) <> p_onhand_status_id then
1203 --Bug#11826279
1204 /*Select mtl_onhand_status_group_s.nextval
1205 Into l_status_group_id
1206 From dual;
1207
1208 l_status_rec.group_id := l_status_group_id; */
1209
1210 l_status_rec.group_id := p_group_id ; --ERES Deferred
1211 IF (l_debug = 1) THEN
1212 mdebug(' l_status_rec.group_id is: '|| l_status_rec.group_id);
1213 END IF;
1214
1215
1216 l_status_rec.pending_status := p_pending_status; --ERES Deferred
1217
1218 FOR j in cur_onhand_status LOOP
1219 l_status_rec.zone_code := j.subinventory_code;
1220 l_status_rec.locator_id := j.locator_id;
1221 l_status_rec.lpn_id := j.lpn_id;
1222 l_status_rec.lot_number := j.lot_number;
1223 l_status_rec.primary_onhand := j.primary_onhand;
1224 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
1225 END LOOP;
1226 end if ;
1227 END LOOP;
1228
1229
1230 -- Bug 6798024 : If insert history was not called from inside the lot loop then call it from here
1231 if (l_count = 0) then
1232 --Bug#11826279
1233 /*Select mtl_onhand_status_group_s.nextval
1234 Into l_status_group_id
1235 From dual;
1236
1237 l_status_rec.group_id := l_status_group_id; */
1238 l_status_rec.group_id := p_group_id ; --ERES Deferred
1239 l_status_rec.pending_status := p_pending_status; --ERES Deferred
1240
1241
1242 FOR j in cur_onhand_status LOOP
1243 l_status_rec.zone_code := j.subinventory_code;
1244 l_status_rec.locator_id := j.locator_id;
1245 l_status_rec.lpn_id := j.lpn_id;
1246 l_status_rec.lot_number := j.lot_number;
1247 l_status_rec.primary_onhand := j.primary_onhand;
1248 l_count := l_count + 1;
1249 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
1250
1251 END LOOP;
1252
1253 end if;
1254 --ERES Deferred
1255 IF p_pending_status = 0 THEN
1256 OPEN cur_onhand;
1257 FETCH cur_onhand BULK COLLECT INTO rowid_list ;
1258 FORALL j in rowid_list.first .. rowid_list.last
1259
1260 update mtl_onhand_quantities_detail
1261 set status_id = p_onhand_status_id
1262 , last_updated_by = FND_GLOBAL.USER_ID
1263 , last_update_date = SYSDATE
1264 , last_update_login = FND_GLOBAL.LOGIN_ID
1265 where ROWID = rowid_list(j);
1266 -- Modified the where clause in the above update as it is not required for the bug # 6633612
1267 --COMMIT; Bug#11826279, MTL_GRADE_STATUS_ERES_GTM getting deleted in the same session.
1268 CLOSE cur_onhand;
1269
1270 mdebug(' update executed in sql: Onhand');
1271
1272 -- Added the serial status check for bug # 7113129
1273 SELECT serial_control into l_serial_status_control
1274 from mtl_material_statuses
1275 WHERE status_id = p_onhand_status_id;
1276
1277 IF (l_serial_status_control = 1) THEN
1278 OPEN cur_onhserial;
1279 FETCH cur_onhserial BULK COLLECT INTO rowid_list1 ;
1280 FORALL j in rowid_list1.first .. rowid_list1.last
1281
1282 update mtl_serial_numbers
1283 set status_id = p_onhand_status_id
1284 , last_updated_by = FND_GLOBAL.USER_ID
1285 , last_update_date = SYSDATE
1286 , last_update_login = FND_GLOBAL.LOGIN_ID
1287 where ROWID = rowid_list1(j);
1288 --COMMIT; Bug#11826279, MTL_GRADE_STATUS_ERES_GTM getting deleted in the same session.
1289 CLOSE cur_onhserial;
1290 END IF;
1291 END IF;
1292
1293 -- Removed the Loop in the above two cursors as a part of changes made for the bug # 6882196
1294 mdebug(' update executed in sql: Onhand Serial');
1295
1296 EXCEPTION WHEN OTHERS THEN
1297 mdebug(' IN OTHERS EXCEPTION '||sqlerrm);
1298 FND_MESSAGE.SET_NAME('FND','FLEX-HASH DEADLOCK');
1299 x_message := FND_MESSAGE.GET;
1300 x_status :='E';
1301 IF cur_onhand%ISOPEN THEN
1302 CLOSE cur_onhand;
1303 END IF;
1304 -- Added for bug # 6882196
1305 IF cur_onhserial%ISOPEN THEN
1306 CLOSE cur_onhserial;
1307 END IF;
1308 RETURN;
1309 END;
1310 end if; --added for full lpn update case
1311 end if;
1312
1313 -- End of changes for # 6633612---------------
1314
1315 -- invoke workflow to process the update
1316 if p_sub_status_id >0 or p_loc_status_id >0 or
1317 p_lot_status_id >0 or p_serial_status_id >0 or p_onhand_status_id > 0 then
1318 IF (l_debug = 1) THEN
1319 mdebug('before INV_STATUS_PKG.invoke_reason_wf');
1320 END IF;
1321
1322 INV_STATUS_PKG.invoke_reason_wf(
1323 p_update_method
1324 , p_organization_id
1325 , p_inventory_item_id
1326 , p_sub_code
1327 , p_sub_status_id
1328 , p_sub_reason_id
1329 , p_locator_id
1330 , p_loc_status_id
1331 , p_loc_reason_id
1332 , p_from_lot_number
1333 , p_to_lot_number
1334 , p_lot_status_id
1335 , p_lot_reason_id
1336 , p_from_SN
1337 , p_to_SN
1338 , p_serial_status_id
1339 , p_serial_reason_id
1340 , p_onhand_status_id -- Added for # 6633612
1341 , p_onhand_reason_id -- Added for # 6633612
1342 , p_lpn_id -- Added for # 6633612
1343 , x_Status
1344 , x_Message
1345 );
1346 IF (l_debug = 1) THEN
1347 mdebug('after INV_STATUS_PKG.invoke_reason_wf');
1348 END IF;
1349 end if;
1350 IF (l_debug = 1) THEN
1351 mdebug('x_status: '||x_status);
1352 END IF;
1353 if x_status ='E' then
1354 --BEGIN SCHANDRU INVERES
1355 IF p_update_from_mobile = 'Y' THEN
1356 ROLLBACK TO INV_UPDATE_STATUS;
1357 END IF;
1358 --ROLLBACK TO INV_UPDATE_STATUS;
1359 --END SCHANDRU INVERES
1360 FND_MESSAGE.SET_NAME('WMS','WMS_WORKFLOW_CALL_FAIL');
1361 x_message := FND_MESSAGE.GET;
1362 end if;
1363 if p_sub_status_id <=0 and p_loc_status_id <=0 and
1364 p_lot_status_id <=0 and p_serial_status_id <=0
1365 and p_onhand_status_id<=0 and x_status ='C' then
1366 x_Status := 'E';
1367 FND_MESSAGE.SET_NAME('WMS','WMS_NO_STATUS_CHANGED');
1368 x_Message := FND_MESSAGE.GET;
1369 -- x_Message := 'No changes to update';
1370 else
1371 --BEGIN SCHANDRU INVERES
1372 --commit; --For bug 5487508, the commit will be issued from Java
1373 x_Status := 'S';
1374 --END SCHANDRU INVERES
1375 end if;
1376 EXCEPTION
1377 WHEN NO_DATA_FOUND THEN
1378 x_status := 'E';
1379 WHEN OTHERS THEN
1380 --BEGIN SCHANDRU INVERES
1381 IF p_update_from_mobile = 'Y' THEN
1382 ROLLBACK TO INV_UPDATE_STATUS;
1383 END IF;
1384
1385 --ROLLBACK TO INV_UPDATE_STATUS;
1386 --END SCHANDRU INVERES
1387 x_status := 'E';
1388 FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
1389 x_Message := FND_MESSAGE.GET;
1390 END update_status;
1391
1392
1393 PROCEDURE invoke_reason_wf(
1394 p_update_method IN NUMBER
1395 , p_organization_id IN NUMBER
1396 , p_inventory_item_id IN NUMBER
1397 , p_sub_code IN VARCHAR2
1398 , p_sub_status_id IN NUMBER
1399 , p_sub_reason_id IN NUMBER
1400 , p_locator_id IN NUMBER
1401 , p_loc_status_id IN NUMBER
1402 , p_loc_reason_id IN NUMBER
1403 , p_from_lot_number IN VARCHAR2
1404 , p_to_lot_number IN VARCHAR2
1405 , p_lot_status_id IN NUMBER
1406 , p_lot_reason_id IN NUMBER
1407 , p_from_SN IN VARCHAR2
1408 , p_to_SN IN VARCHAR2
1409 , p_serial_status_id IN NUMBER
1410 , p_serial_reason_id IN NUMBER
1411 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
1412 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
1413 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
1414 , x_Status OUT nocopy VARCHAR2
1415 , x_Message OUT nocopy VARCHAR2)
1416 IS
1417 l_workflow_name varchar2(250);
1418 l_reason_name varchar2(30);
1419 l_calling_program_name VARCHAR2(30);
1420 l_update_method varchar2(80);
1421 l_status_code varchar2(80);
1422 -- defining output variables
1423 lX_RETURN_STATUS VARCHAR2(250);
1424 lX_MSG_DATA VARCHAR2(250);
1425 lX_MSG_COUNT NUMBER;
1426 lX_ORGANIZATION_ID NUMBER;
1427 lX_SUBINVENTORY VARCHAR2(250);
1428 lX_SUBINVENTORY_STATUS VARCHAR2(250);
1429 lX_LOCATOR NUMBER;
1430 lX_LOCATOR_STATUS VARCHAR2(250);
1431 lX_LPN_ID NUMBER;
1432 lX_LPN_STATUS VARCHAR2(250);
1433 lX_INVENTORY_ITEM_ID NUMBER;
1434 lX_REVISION VARCHAR2(250);
1435 lX_LOT_NUMBER VARCHAR2(250);
1436 lX_LOT_STATUS VARCHAR2(250);
1437 lX_QUANTITY NUMBER;
1438 lX_UOM_CODE VARCHAR2(250);
1439 lX_PRIMARY_QUANTITY NUMBER;
1440 lX_TRANSACTION_QUANTITY NUMBER;
1441 lX_RESERVATION_ID NUMBER;
1442
1443 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1444 BEGIN
1445 IF (l_debug = 1) THEN
1446 mdebug('In invoke_reason_wf');
1447 END IF;
1448 l_calling_program_name := 'Update Status';
1449 x_Status := 'C';
1450 IF (l_debug = 1) THEN
1451 mdebug('l_calling_orogram_name: '||l_calling_program_name);
1452 END IF;
1453 SELECT meaning
1454 INTO l_update_method
1455 FROM MFG_LOOKUPS
1456 WHERE LOOKUP_TYPE = 'MTL_STATUS_UPDATE_METHOD'
1457 AND LOOKUP_CODE = p_update_method;
1458
1459 IF (l_debug = 1) THEN
1460 mdebug('p_sub_status_id: '||p_sub_status_id);
1461 END IF;
1462 if p_sub_status_id >0 then
1463 SELECT WORKFLOW_NAME
1464 INTO l_workflow_name
1465 FROM MTL_TRANSACTION_REASONS
1466 WHERE REASON_ID = p_sub_reason_id;
1467
1468 SELECT REASON_NAME
1469 INTO l_reason_name
1470 FROM MTL_TRANSACTION_REASONS
1471 WHERE REASON_ID = p_sub_reason_id;
1472
1473 SELECT status_code
1474 INTO l_status_code
1475 FROM MTL_MATERIAL_STATUSES_VL
1476 WHERE status_id = p_sub_status_id;
1477
1478 IF (l_debug = 1) THEN
1479 mdebug('l_workflow_name: '||l_workflow_name);
1480 END IF;
1481
1482 if l_workflow_name is not null then
1483 IF (l_debug = 1) THEN
1484 mdebug('Before starting workflow: '||l_reason_name);
1485 END IF;
1486 wms_workflow_wrappers.wf_start_workflow
1487 (
1488 P_REASON_ID => p_sub_reason_id
1489 ,P_REASON_NAME => l_reason_name
1490 ,P_CALLING_PROGRAM_NAME => l_calling_program_name
1491 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1492 ,P_SOURCE_SUBINVENTORY => p_sub_code
1493 ,P_SOURCE_SUBINVENTORY_STATUS => l_status_code
1494 ,P_UPDATE_STATUS_METHOD => l_update_method
1495 ,X_RETURN_STATUS => lX_RETURN_STATUS
1496 ,X_MSG_DATA => lX_MSG_DATA
1497 ,X_MSG_COUNT => lX_MSG_COUNT
1498 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1499 ,X_SUBINVENTORY => lX_SUBINVENTORY
1500 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1501 ,X_LOCATOR => lX_LOCATOR
1502 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1503 ,X_LPN_ID => lX_LPN_ID
1504 ,X_LPN_STATUS => lX_LPN_STATUS
1505 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1506 ,X_REVISION => lX_REVISION
1507 ,X_LOT_NUMBER => lX_LOT_NUMBER
1508 ,X_LOT_STATUS => lX_LOT_STATUS
1509 ,X_QUANTITY => lX_QUANTITY
1510 ,X_UOM_CODE => lX_UOM_CODE
1511 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1512 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1513 ,X_RESERVATION_ID => lX_RESERVATION_ID
1514 );
1515
1516 end if;
1517 end if;
1518
1519 if p_loc_status_id >0 then
1520 SELECT WORKFLOW_NAME
1521 INTO l_workflow_name
1522 FROM MTL_TRANSACTION_REASONS
1523 WHERE REASON_ID = p_loc_reason_id;
1524
1525 SELECT REASON_NAME
1526 INTO l_reason_name
1527 FROM MTL_TRANSACTION_REASONS
1528 WHERE REASON_ID = p_loc_reason_id;
1529
1530 SELECT status_code
1531 INTO l_status_code
1532 FROM MTL_MATERIAL_STATUSES_VL
1533 WHERE status_id = p_loc_status_id;
1534
1535 if l_workflow_name is not null THEN
1536
1537 wms_workflow_wrappers.wf_start_workflow
1538 (
1539 P_REASON_ID => p_sub_reason_id
1540 ,p_reason_name => l_reason_name
1541 ,p_calling_program_name => l_calling_program_name
1542 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1543 ,P_SOURCE_SUBINVENTORY => p_sub_code
1544 ,P_SOURCE_LOCATOR => p_locator_id
1545 ,P_SOURCE_LOCATOR_STATUS => l_status_code
1546 ,P_UPDATE_STATUS_METHOD => l_update_method
1547 ,X_RETURN_STATUS => lX_RETURN_STATUS
1548 ,X_MSG_DATA => lX_MSG_DATA
1549 ,X_MSG_COUNT => lX_MSG_COUNT
1550 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1551 ,X_SUBINVENTORY => lX_SUBINVENTORY
1552 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1553 ,X_LOCATOR => lX_LOCATOR
1554 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1555 ,X_LPN_ID => lX_LPN_ID
1556 ,X_LPN_STATUS => lX_LPN_STATUS
1557 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1558 ,X_REVISION => lX_REVISION
1559 ,X_LOT_NUMBER => lX_LOT_NUMBER
1560 ,X_LOT_STATUS => lX_LOT_STATUS
1561 ,X_QUANTITY => lX_QUANTITY
1562 ,X_UOM_CODE => lX_UOM_CODE
1563 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1564 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1565 ,X_RESERVATION_ID => lX_RESERVATION_ID
1566 );
1567 end if;
1568 end if;
1569
1570 if p_lot_status_id >0 then
1571 SELECT WORKFLOW_NAME
1572 INTO l_workflow_name
1573 FROM MTL_TRANSACTION_REASONS
1574 WHERE REASON_ID = p_lot_reason_id;
1575
1576 SELECT REASON_NAME
1577 INTO l_reason_name
1578 FROM MTL_TRANSACTION_REASONS
1579 WHERE REASON_ID = p_lot_reason_id;
1580
1581 SELECT status_code
1582 INTO l_status_code
1583 FROM MTL_MATERIAL_STATUSES_VL
1584 WHERE status_id = p_lot_status_id;
1585
1586 if l_workflow_name is not null then
1587 wms_workflow_wrappers.wf_start_workflow
1588 (
1589 P_REASON_ID => p_sub_reason_id
1590 ,P_REASON_NAME => l_reason_name
1591 ,p_calling_program_name => l_calling_program_name
1592 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1593 ,P_SOURCE_SUBINVENTORY => p_sub_code
1594 ,P_SOURCE_LOCATOR => p_locator_id
1595 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
1596 ,P_LOT_NUMBER => p_from_lot_number
1597 ,P_TO_LOT_NUMBER => p_to_lot_number
1598 ,P_LOT_STATUS => l_status_code
1599 ,P_UPDATE_STATUS_METHOD => l_update_method
1600 ,X_RETURN_STATUS => lX_RETURN_STATUS
1601 ,X_MSG_DATA => lX_MSG_DATA
1602 ,X_MSG_COUNT => lX_MSG_COUNT
1603 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1604 ,X_SUBINVENTORY => lX_SUBINVENTORY
1605 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1606 ,X_LOCATOR => lX_LOCATOR
1607 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1608 ,X_LPN_ID => lX_LPN_ID
1609 ,X_LPN_STATUS => lX_LPN_STATUS
1610 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1611 ,X_REVISION => lX_REVISION
1612 ,X_LOT_NUMBER => lX_LOT_NUMBER
1613 ,X_LOT_STATUS => lX_LOT_STATUS
1614 ,X_QUANTITY => lX_QUANTITY
1615 ,X_UOM_CODE => lX_UOM_CODE
1616 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1617 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1618 ,X_RESERVATION_ID => lX_RESERVATION_ID
1619 );
1620 end if;
1621 end if;
1622
1623 if p_serial_status_id >0 then
1624 SELECT WORKFLOW_NAME
1625 INTO l_workflow_name
1626 FROM MTL_TRANSACTION_REASONS
1627 WHERE REASON_ID = p_serial_reason_id;
1628
1629 SELECT REASON_NAME
1630 INTO l_reason_name
1631 FROM MTL_TRANSACTION_REASONS
1632 WHERE REASON_ID = p_serial_reason_id;
1633
1634 SELECT status_code
1635 INTO l_status_code
1636 FROM MTL_MATERIAL_STATUSES_VL
1637 WHERE status_id = p_serial_status_id;
1638
1639 if l_workflow_name is not null then
1640 wms_workflow_wrappers.wf_start_workflow
1641 (
1642 P_REASON_ID => p_sub_reason_id
1643 ,P_REASON_NAME => l_reason_name
1644 ,p_calling_program_name => l_calling_program_name
1645 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1646 ,P_SOURCE_SUBINVENTORY => p_sub_code
1647 ,P_SOURCE_LOCATOR => p_locator_id
1648 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
1649 ,P_LOT_NUMBER => p_from_lot_number
1650 ,P_TO_LOT_NUMBER => p_to_lot_number
1651 ,P_SERIAL_NUMBER => p_from_SN
1652 ,P_TO_SERIAL_NUMBER => p_to_SN
1653 ,P_SERIAL_NUMBER_STATUS => l_status_code
1654 ,P_UPDATE_STATUS_METHOD => l_update_method
1655 ,X_RETURN_STATUS => lX_RETURN_STATUS
1656 ,X_MSG_DATA => lX_MSG_DATA
1657 ,X_MSG_COUNT => lX_MSG_COUNT
1658 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1659 ,X_SUBINVENTORY => lX_SUBINVENTORY
1660 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1661 ,X_LOCATOR => lX_LOCATOR
1662 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1663 ,X_LPN_ID => lX_LPN_ID
1664 ,X_LPN_STATUS => lX_LPN_STATUS
1665 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1666 ,X_REVISION => lX_REVISION
1667 ,X_LOT_NUMBER => lX_LOT_NUMBER
1668 ,X_LOT_STATUS => lX_LOT_STATUS
1669 ,X_QUANTITY => lX_QUANTITY
1670 ,X_UOM_CODE => lX_UOM_CODE
1671 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1672 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1673 ,X_RESERVATION_ID => lX_RESERVATION_ID
1674 );
1675 end if;
1676 end if;
1677
1678 -- Start of changes for # 6633612---------------
1679
1680 if p_onhand_status_id >0 then
1681 SELECT WORKFLOW_NAME
1682 INTO l_workflow_name
1683 FROM MTL_TRANSACTION_REASONS
1684 WHERE REASON_ID = p_onhand_reason_id;
1685
1686 SELECT REASON_NAME
1687 INTO l_reason_name
1688 FROM MTL_TRANSACTION_REASONS
1689 WHERE REASON_ID = p_onhand_reason_id;
1690
1691 SELECT status_code
1692 INTO l_status_code
1693 FROM MTL_MATERIAL_STATUSES_VL
1694 WHERE status_id = p_onhand_status_id;
1695
1696 IF (l_debug = 1) THEN
1697 mdebug('l_workflow_name: '||l_workflow_name);
1698 END IF;
1699
1700 if l_workflow_name is not null then
1701 IF (l_debug = 1) THEN
1702 mdebug('Before starting workflow: '||l_reason_name);
1703 END IF;
1704 wms_workflow_wrappers.wf_start_workflow
1705 (
1706 P_REASON_ID => p_sub_reason_id
1707 ,P_REASON_NAME => l_reason_name
1708 ,P_CALLING_PROGRAM_NAME => l_calling_program_name
1709 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
1710 ,P_SOURCE_SUBINVENTORY => p_sub_code
1711 ,P_SOURCE_LOCATOR => p_locator_id
1712 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
1713 ,P_LOT_NUMBER => p_from_lot_number
1714 ,P_TO_LOT_NUMBER => p_to_lot_number
1715 ,P_LPN_ID => p_lpn_id -- Added for # 6633612
1716 ,P_ONHAND_STATUS => l_status_code -- -- Added for # 6633612 -- Needs to be added in the WMS file.
1717 ,P_UPDATE_STATUS_METHOD => l_update_method
1718 ,X_RETURN_STATUS => lX_RETURN_STATUS
1719 ,X_MSG_DATA => lX_MSG_DATA
1720 ,X_MSG_COUNT => lX_MSG_COUNT
1721 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
1722 ,X_SUBINVENTORY => lX_SUBINVENTORY
1723 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
1724 ,X_LOCATOR => lX_LOCATOR
1725 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
1726 ,X_LPN_ID => lX_LPN_ID
1727 ,X_LPN_STATUS => lX_LPN_STATUS
1728 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
1729 ,X_REVISION => lX_REVISION
1730 ,X_LOT_NUMBER => lX_LOT_NUMBER
1731 ,X_LOT_STATUS => lX_LOT_STATUS
1732 ,X_QUANTITY => lX_QUANTITY
1733 ,X_UOM_CODE => lX_UOM_CODE
1734 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
1735 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
1736 ,X_RESERVATION_ID => lX_RESERVATION_ID
1737 );
1738
1739 end if;
1740 end if;
1741
1742
1743 -- End of changes for # 6633612---------------
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 x_status := 'E';
1748
1749 END invoke_reason_wf;
1750 --Bug#5577767 Created this procedure to filter sec qty/uom based on tracking_quantity_ind.
1751 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
1752 p_tracking_qty_ind VARCHAR2(10);
1753 BEGIN
1754 IF p_item_id IS NULL OR p_org_id IS NULL THEN
1755 x_sec_qty := NULL;
1756 x_sec_uom := NULL;
1757 ELSE
1758 SELECT tracking_quantity_ind INTO p_tracking_qty_ind
1759 FROM mtl_system_items_kfv
1760 WHERE inventory_item_id = p_item_id
1761 AND organization_id = p_org_id;
1762 IF p_tracking_qty_ind = 'P' THEN
1763 x_sec_qty := NULL;
1764 x_sec_uom := NULL;
1765 END IF;
1766 END IF;
1767 END TRACKING_QUANTITY_IND;
1768
1769 --added for lpn status project to check whether update transaction will result in mixed or not
1770 FUNCTION get_mixed_status(p_lpn_id NUMBER,
1771 p_organization_id NUMBER,
1772 p_outermost_lpn_id NUMBER,
1773 p_inventory_item_id NUMBER,
1774 p_lot_number VARCHAR2 := NULL,
1775 p_status_id NUMBER)
1776 RETURN VARCHAR2 is
1777
1778 CURSOR wlc_cur is
1779 SELECT *
1780 FROM wms_lpn_contents wlc
1781 WHERE wlc.parent_lpn_id IN
1782 (SELECT lpn_id
1783 FROM wms_license_plate_numbers plpn
1784 start with lpn_id = p_outermost_lpn_id
1785 connect by parent_lpn_id = prior lpn_id
1786 )
1787 and wlc.parent_lpn_id not in
1788 (SELECT lpn_id
1789 FROM wms_license_plate_numbers plpn
1790 start with lpn_id = p_lpn_id
1791 connect by parent_lpn_id = prior lpn_id
1792 );
1793 CURSOR wlc_item_cur is
1794 SELECT *
1795 FROM wms_lpn_contents wlc
1796 WHERE wlc.parent_lpn_id IN
1797 (SELECT lpn_id
1798 FROM wms_license_plate_numbers plpn
1799 start with lpn_id = p_outermost_lpn_id
1800 connect by parent_lpn_id = prior lpn_id
1801 );
1802 CURSOR msn_cur(l_inventory_item_id NUMBER,l_lpn_id NUMBER) is
1803 select status_id
1804 FROM mtl_serial_numbers
1805 where inventory_item_id = l_inventory_item_id
1806 AND lpn_id = l_lpn_id;
1807
1808 l_serial_controlled NUMBER := 0;
1809 l_serial_status_enabled NUMBER := 0;
1810 l_return_mixed NUMBER := 0;
1811 l_return_status VARCHAR2(10) := 'S';
1812 l_default_status_id NUMBER;
1813 BEGIN
1814 mdebug('In get_mixed_status');
1815 mdebug('Values Passed--------');
1816 mdebug('p_lpn_id '||p_lpn_id);
1817 mdebug('p_organization_id '||p_organization_id);
1818 mdebug('p_inventory_item_id '||p_inventory_item_id);
1819 mdebug('p_lot_number '||p_lot_number);
1820 mdebug('p_status_id '||p_status_id);
1821 mdebug('p_outermost_lpn_id '||p_outermost_lpn_id);
1822 IF(p_inventory_item_id is NULL) THEN
1823 mdebug('Item id is NULL so have to check for whole LPN case');
1824 FOR l_wlc_cur in wlc_cur loop
1825 l_serial_controlled := 0;
1826 l_serial_status_enabled := 0;
1827 IF inv_cache.set_item_rec(p_organization_id, l_wlc_cur.inventory_item_id) THEN
1828 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1829 l_serial_controlled := 1; -- Item is serial controlled
1830 END IF;
1831 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1832 l_serial_status_enabled := 1;
1833 END IF;
1834 END IF;
1835 mdebug('Inventory item id from wlc_cur '||l_wlc_cur.inventory_item_id);
1836 mdebug('parent_lpn_id from wlc_cur '||l_wlc_cur.parent_lpn_id);
1837 IF l_serial_controlled = 0 THEN
1838 mdebug('Item is not serial controlled so checking moqd for status');
1839 select DISTINCT status_id INTO l_default_status_id
1840 from mtl_onhand_quantities_detail
1841 WHERE lpn_id = l_wlc_cur.parent_lpn_id
1842 AND inventory_item_id = l_wlc_cur.inventory_item_id
1843 AND NVL(lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
1844 AND organization_id = p_organization_id;
1845 mdebug('status returned from moqd is '||l_default_status_id);
1846 IF(l_default_status_id <> p_status_id) THEN
1847 l_return_mixed := 1;
1848 END IF;
1849 ELSIF (l_serial_controlled = 1 AND l_serial_status_enabled = 1) THEN
1850 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1851 FOR l_msn_cur in msn_cur(l_wlc_cur.inventory_item_id,l_wlc_cur.parent_lpn_id) loop
1852 mdebug('MSN status is '||l_msn_cur.status_id);
1853 IF(l_msn_cur.status_id <>p_status_id)THEN
1854 l_return_mixed := 1;
1855 EXIT;
1856 END IF;
1857 END LOOP;
1858 END IF;
1859 IF(l_return_mixed = 1)THEN
1860 EXIT;
1861 END IF;
1862 END LOOP;
1863 IF (l_return_mixed =1)THEN
1864 l_return_status := 'M';
1865 ELSE
1866 l_return_status := 'S';
1867 END IF;
1868
1869 ELSE
1870 mdebug('Item id is not null ...');
1871 FOR l_wlc_item_cur in wlc_item_cur loop
1872 l_return_mixed := 0;
1873 IF(l_wlc_item_cur.inventory_item_id<>p_inventory_item_id
1874 OR NVL(l_wlc_item_cur.lot_number,'@@@@') <> NVL(p_lot_number,'@@@@')
1875 OR l_wlc_item_cur.parent_lpn_id <> p_lpn_id
1876 )THEN
1877 l_serial_controlled := 0;
1878 l_serial_status_enabled := 0;
1879 IF inv_cache.set_item_rec(p_organization_id, l_wlc_item_cur.inventory_item_id) THEN
1880 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1881 l_serial_controlled := 1; -- Item is serial controlled
1882 END IF;
1883 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1884 l_serial_status_enabled := 1;
1885 END IF;
1886 END IF;
1887 mdebug('Inventory item id from wlc_item_cur '||l_wlc_item_cur.inventory_item_id);
1888 mdebug('parent_lpn_id from wlc_item_cur '||l_wlc_item_cur.parent_lpn_id);
1889 IF l_serial_controlled = 0 then
1890 mdebug('Item is not serial controlled so checking moqd for status');
1891 select DISTINCT status_id into l_default_status_id
1892 from mtl_onhand_quantities_detail
1893 where lpn_id = l_wlc_item_cur.parent_lpn_id
1894 AND inventory_item_id = l_wlc_item_cur.inventory_item_id
1895 AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
1896 AND organization_id = p_organization_id;
1897 mdebug('status returned from moqd is '||l_default_status_id);
1898 IF(l_default_status_id <> p_status_id) THEN
1899 l_return_mixed := 1;
1900 END IF;
1901 ELSIF(l_serial_controlled = 1 AND l_serial_status_enabled = 1)THEN
1902 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1903 FOR l_msn_cur in msn_cur(l_wlc_item_cur.inventory_item_id,l_wlc_item_cur.parent_lpn_id) loop
1904 mdebug('MSN status is '||l_msn_cur.status_id);
1905 IF(l_msn_cur.status_id <>p_status_id)THEN
1906 l_return_mixed := 1;
1907 EXIT;
1908 END IF;
1909 END LOOP;
1910 END IF;
1911 END IF;
1912 IF(l_return_mixed = 1)THEN
1913 EXIT;
1914 END IF;
1915 END LOOP;
1916 IF (l_return_mixed =1)THEN
1917 l_return_status := 'M';
1918 ELSE
1919 l_return_status := 'S';
1920 END IF;
1921 END IF;
1922 RETURN l_return_status;
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925 mdebug('Exception occured so returning M');
1926 RETURN 'M';
1927 END get_mixed_status;
1928 --bug 6952533
1929 FUNCTION get_mixed_status_serial(p_lpn_id NUMBER,
1930 p_organization_id NUMBER,
1931 p_outermost_lpn_id NUMBER,
1932 p_inventory_item_id NUMBER,
1933 p_lot_number VARCHAR2 := NULL,
1934 p_fm_sn VARCHAR2,
1935 p_to_sn VARCHAR2,
1936 p_status_id NUMBER)
1937 RETURN VARCHAR2 is
1938 CURSOR wlc_item_cur is
1939 SELECT *
1940 FROM wms_lpn_contents wlc
1941 WHERE wlc.parent_lpn_id IN
1942 (SELECT lpn_id
1943 FROM wms_license_plate_numbers plpn
1944 start with lpn_id = p_outermost_lpn_id
1945 connect by parent_lpn_id = prior lpn_id
1946 );
1947 CURSOR msn_cur(l_inventory_item_id NUMBER,l_lpn_id NUMBER) is
1948 select status_id
1949 FROM mtl_serial_numbers msn
1950 where inventory_item_id = l_inventory_item_id
1951 AND lpn_id = l_lpn_id
1952 AND nvl(msn.lot_number , '@@@@') = NVL(p_lot_number,'@@@@')
1953 AND msn.serial_number NOT IN (select serial_number
1954 from mtl_serial_numbers
1955 where serial_number between p_fm_sn AND p_to_sn);
1956
1957 l_serial_controlled NUMBER := 0;
1958 l_serial_status_enabled NUMBER := 0;
1959 l_return_mixed NUMBER := 0;
1960 l_return_status VARCHAR2(10) := 'S';
1961 l_default_status_id NUMBER;
1962 BEGIN
1963 FOR l_wlc_item_cur in wlc_item_cur loop
1964 l_return_mixed := 0;
1965 l_serial_controlled := 0;
1966 l_serial_status_enabled := 0;
1967 IF inv_cache.set_item_rec(p_organization_id, l_wlc_item_cur.inventory_item_id) THEN
1968 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1969 l_serial_controlled := 1; -- Item is serial controlled
1970 END IF;
1971 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
1972 l_serial_status_enabled := 1;
1973 END IF;
1974 END IF;
1975 mdebug('Inventory item id from wlc_item_cur '||l_wlc_item_cur.inventory_item_id);
1976 mdebug('parent_lpn_id from wlc_item_cur '||l_wlc_item_cur.parent_lpn_id);
1977 IF l_serial_controlled = 0 then
1978 mdebug('Item is not serial controlled so checking moqd for status');
1979 select DISTINCT status_id into l_default_status_id
1980 from mtl_onhand_quantities_detail
1981 where lpn_id = l_wlc_item_cur.parent_lpn_id
1982 AND inventory_item_id = l_wlc_item_cur.inventory_item_id
1983 AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
1984 AND organization_id = p_organization_id;
1985 mdebug('status returned from moqd is '||l_default_status_id);
1986 IF(l_default_status_id <> p_status_id) THEN
1987 l_return_mixed := 1;
1988 END IF;
1989 ELSIF(l_serial_controlled = 1 AND l_serial_status_enabled = 1)THEN
1990 mdebug('Item is serial controlled and serial status is alos enabled so checking MSN for status');
1991 FOR l_msn_cur in msn_cur(l_wlc_item_cur.inventory_item_id,l_wlc_item_cur.parent_lpn_id) loop
1992 mdebug('MSN status is '||l_msn_cur.status_id);
1993 IF(l_msn_cur.status_id <>p_status_id)THEN
1994 l_return_mixed := 1;
1995 EXIT;
1996 END IF;
1997 END LOOP;
1998 END IF;
1999 IF(l_return_mixed = 1)THEN
2000 EXIT;
2001 END IF;
2002 END LOOP;
2003 IF l_return_mixed = 1 THEN
2004 l_return_status := 'M';
2005 END IF;
2006 RETURN l_return_status;
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 mdebug('Exception occured so returning M');
2010 RETURN 'M';
2011 END get_mixed_status_serial;
2012 --end of bug 6952533
2013 END INV_STATUS_PKG;