[Home] [Help]
95: );
96:
97:
98: BEGIN
99: inv_trx_util_pub.TRACE('Entered is_status_applicable ', 'Material Status', 9);
100: inv_trx_util_pub.TRACE('p_wms_installed'||p_wms_installed||'p_trx_status_enabled'
101: ||p_trx_status_enabled||'p_trx_type_id'||p_trx_type_id
102: ||'p_lot_status_enabled'||p_lot_status_enabled||'p_serial_status_enabled'
103: ||p_serial_status_enabled, 'Material Status', 9);
96:
97:
98: BEGIN
99: inv_trx_util_pub.TRACE('Entered is_status_applicable ', 'Material Status', 9);
100: inv_trx_util_pub.TRACE('p_wms_installed'||p_wms_installed||'p_trx_status_enabled'
101: ||p_trx_status_enabled||'p_trx_type_id'||p_trx_type_id
102: ||'p_lot_status_enabled'||p_lot_status_enabled||'p_serial_status_enabled'
103: ||p_serial_status_enabled, 'Material Status', 9);
104: inv_trx_util_pub.TRACE('p_organization_id'||p_organization_id||'p_inventory_item_id'
100: inv_trx_util_pub.TRACE('p_wms_installed'||p_wms_installed||'p_trx_status_enabled'
101: ||p_trx_status_enabled||'p_trx_type_id'||p_trx_type_id
102: ||'p_lot_status_enabled'||p_lot_status_enabled||'p_serial_status_enabled'
103: ||p_serial_status_enabled, 'Material Status', 9);
104: inv_trx_util_pub.TRACE('p_organization_id'||p_organization_id||'p_inventory_item_id'
105: ||p_inventory_item_id||'p_sub_code'||p_sub_code||'p_locator_id'
106: ||p_locator_id||'p_lot_number'||p_lot_number||'p_serial_number'
107: ||p_serial_number||'p_object_type'||p_object_type,'Material Status', 9);
108: inv_trx_util_pub.TRACE('p_fromlpn_id'||p_fromlpn_id||'p_xfer_lpn_id'||p_xfer_lpn_id||'p_xfer_sub_code'||p_xfer_sub_code||'p_xfer_locator_id'||p_xfer_locator_id||'p_xfer_org_id'||p_xfer_org_id, 'Material Status', 9);
104: inv_trx_util_pub.TRACE('p_organization_id'||p_organization_id||'p_inventory_item_id'
105: ||p_inventory_item_id||'p_sub_code'||p_sub_code||'p_locator_id'
106: ||p_locator_id||'p_lot_number'||p_lot_number||'p_serial_number'
107: ||p_serial_number||'p_object_type'||p_object_type,'Material Status', 9);
108: inv_trx_util_pub.TRACE('p_fromlpn_id'||p_fromlpn_id||'p_xfer_lpn_id'||p_xfer_lpn_id||'p_xfer_sub_code'||p_xfer_sub_code||'p_xfer_locator_id'||p_xfer_locator_id||'p_xfer_org_id'||p_xfer_org_id, 'Material Status', 9);
109:
110: IF p_xfer_lpn_id IS NOT NULL THEN
111: BEGIN
112: inv_trx_util_pub.TRACE('mixed status: .. Xfer LPN is New LPN or not?? .. 1','Material Status', 9);
108: inv_trx_util_pub.TRACE('p_fromlpn_id'||p_fromlpn_id||'p_xfer_lpn_id'||p_xfer_lpn_id||'p_xfer_sub_code'||p_xfer_sub_code||'p_xfer_locator_id'||p_xfer_locator_id||'p_xfer_org_id'||p_xfer_org_id, 'Material Status', 9);
109:
110: IF p_xfer_lpn_id IS NOT NULL THEN
111: BEGIN
112: inv_trx_util_pub.TRACE('mixed status: .. Xfer LPN is New LPN or not?? .. 1','Material Status', 9);
113:
114: l_xfer_locator_id := p_xfer_locator_id;
115: l_xfer_sub_code := p_xfer_sub_code;
116: l_xfer_org_id := p_xfer_org_id;
129: FROM wms_license_plate_numbers
130: WHERE lpn_id = p_xfer_lpn_id
131: )
132: ));
133: inv_trx_util_pub.TRACE('mixed status: .. 1.1.1 - l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
134:
135: EXCEPTION
136: WHEN NO_DATA_FOUND THEN
137: l_xferlpn_context := 5;
134:
135: EXCEPTION
136: WHEN NO_DATA_FOUND THEN
137: l_xferlpn_context := 5;
138: inv_trx_util_pub.TRACE('mixed status: .. New LPN[Ctrl+G] .. 2','Material Status', 9);
139: END;
140:
141:
142:
147: from wms_license_plate_numbers where lpn_id = p_xfer_lpn_id;
148: EXCEPTION
149: WHEN NO_DATA_FOUND THEN
150: l_xferlpn_context := 5;
151: inv_trx_util_pub.TRACE('mixed status: .. 2.1','Material Status', 9);
152: END;
153: END IF;
154:
155: inv_trx_util_pub.TRACE('l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
151: inv_trx_util_pub.TRACE('mixed status: .. 2.1','Material Status', 9);
152: END;
153: END IF;
154:
155: inv_trx_util_pub.TRACE('l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
156: END IF; --transfer lpnid not null
157: inv_trx_util_pub.TRACE('l_xfer_locator_id:'||l_xfer_locator_id||'l_xfer_sub_code'||l_xfer_sub_code||'l_xfer_org_id'||l_xfer_org_id,'Material Status', 9);
158: --for bug 6918618
159: IF p_fromlpn_id is NOT NULL and p_inventory_item_id IS NOT NULL AND (p_trx_type_id = 42 OR p_trx_type_id = 41) AND l_allow_mixed_status = 2 THEN --7173146
153: END IF;
154:
155: inv_trx_util_pub.TRACE('l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
156: END IF; --transfer lpnid not null
157: inv_trx_util_pub.TRACE('l_xfer_locator_id:'||l_xfer_locator_id||'l_xfer_sub_code'||l_xfer_sub_code||'l_xfer_org_id'||l_xfer_org_id,'Material Status', 9);
158: --for bug 6918618
159: IF p_fromlpn_id is NOT NULL and p_inventory_item_id IS NOT NULL AND (p_trx_type_id = 42 OR p_trx_type_id = 41) AND l_allow_mixed_status = 2 THEN --7173146
160: BEGIN
161: select lpn_context into l_lpn_context
161: select lpn_context into l_lpn_context
162: from wms_license_plate_numbers
163: where lpn_id = p_fromlpn_id;
164:
165: inv_trx_util_pub.TRACE('l_lpn_context::'||l_lpn_context,'Material Status', 9);
166: EXCEPTION
167: when no_data_found then
168: inv_trx_util_pub.TRACE('l_lpn_context'||l_lpn_context,'Material Status', 9);
169: l_lpn_context := 5;
164:
165: inv_trx_util_pub.TRACE('l_lpn_context::'||l_lpn_context,'Material Status', 9);
166: EXCEPTION
167: when no_data_found then
168: inv_trx_util_pub.TRACE('l_lpn_context'||l_lpn_context,'Material Status', 9);
169: l_lpn_context := 5;
170: END;
171:
172: IF l_lpn_context = 1 THEN
170: END;
171:
172: IF l_lpn_context = 1 THEN
173:
174: inv_trx_util_pub.TRACE('l_lpn_context'||l_lpn_context,'Material Status', 9);
175: l_return_status_id := get_default_status --calling function to get the MOQD status
176: (p_organization_id => p_organization_id,
177: p_inventory_item_id => p_inventory_item_id,
178: p_sub_code =>p_sub_code,
180: p_lot_number => p_lot_number,
181: p_lpn_id => p_fromlpn_id,
182: p_transaction_action_id=> NULL,
183: p_src_status_id => NULL);
184: inv_trx_util_pub.TRACE('l_return_status_id'||l_return_status_id,'Material Status', 9);
185:
186:
187: IF l_return_status_id <> -1 THEN
188: BEGIN
201: FROM wms_license_plate_numbers
202: WHERE lpn_id = p_fromlpn_id
203: )
204: ));
205: inv_trx_util_pub.TRACE('l_allow_status::::'||l_allow_status,'Material Status', 9);
206: EXCEPTION
207: WHEN NO_DATA_FOUND THEN
208: l_allow_status := 'N';
209: inv_trx_util_pub.TRACE('l_allow_status::::(exception block)'||l_allow_status,'Material Status', 9);
205: inv_trx_util_pub.TRACE('l_allow_status::::'||l_allow_status,'Material Status', 9);
206: EXCEPTION
207: WHEN NO_DATA_FOUND THEN
208: l_allow_status := 'N';
209: inv_trx_util_pub.TRACE('l_allow_status::::(exception block)'||l_allow_status,'Material Status', 9);
210: END;
211: END IF; --l_return_status_id <> -1
212: END IF; -- lpn context
213: END IF; --6918618
215: IF p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 AND l_allow_mixed_status = 2 THEN
216:
217: IF p_inventory_item_id IS NOT NULL THEN
218: BEGIN
219: inv_trx_util_pub.TRACE('mixed status: inv id not null .. 10','Material Status', 9);
220:
221: l_allow_status := 'N';
222: --Added for Bug 7007389
223: BEGIN
231: AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
232: AND NVL(moqdsrc.lpn_id, 0) = NVL(p_fromlpn_id, 0)
233: AND ROWNUM = 1;
234:
235: inv_trx_util_pub.TRACE('mixed status: before excep block SerialCheck.. 10.1','Material Status', 9);
236:
237: EXCEPTION
238: WHEN NO_DATA_FOUND THEN
239: inv_trx_util_pub.TRACE('mixed status: in excep block SerialCheck.. 10.2','Material Status', 9);
235: inv_trx_util_pub.TRACE('mixed status: before excep block SerialCheck.. 10.1','Material Status', 9);
236:
237: EXCEPTION
238: WHEN NO_DATA_FOUND THEN
239: inv_trx_util_pub.TRACE('mixed status: in excep block SerialCheck.. 10.2','Material Status', 9);
240:
241: END;
242:
243: IF temp_status_id is NULL THEN
240:
241: END;
242:
243: IF temp_status_id is NULL THEN
244: inv_trx_util_pub.TRACE('mixed status: temp_status_id is null .. Serial 10.3','Material Status', 9);
245: l_allow_status := 'Y';
246: END IF;
247: /*End of changes for Bug # 7007389 */
248: /*Following condition has also added as part of Bug # 7007389 */
246: END IF;
247: /*End of changes for Bug # 7007389 */
248: /*Following condition has also added as part of Bug # 7007389 */
249: IF temp_status_id is NOT NULL THEN
250: inv_trx_util_pub.TRACE('mixed status: inside if .. 10.4','Material Status', 9);
251: SELECT 'Y'
252: INTO l_allow_status
253: FROM mtl_onhand_quantities_detail moqdsrc
254: WHERE moqdsrc.organization_id = p_organization_id
277: END IF; --added as part of 7007389
278: EXCEPTION
279: WHEN NO_DATA_FOUND THEN
280: l_allow_status := 'N';
281: inv_trx_util_pub.TRACE('mixed status: came here .. 20 l_allow_status'||l_allow_status,'Material Status', 9);
282: END ;
283:
284: ELSIF p_inventory_item_id IS NULL AND p_fromlpn_id is NOT NULL THEN
285:
283:
284: ELSIF p_inventory_item_id IS NULL AND p_fromlpn_id is NOT NULL THEN
285:
286: BEGIN
287: inv_trx_util_pub.TRACE('mixed status: inv id is null and from LPN ID not null .. 30','Material Status', 9);
288:
289: SELECT 'N'
290: INTO l_allow_status
291: FROM dual
335: ));
336: EXCEPTION
337: WHEN NO_DATA_FOUND THEN
338: l_allow_status := 'Y';
339: inv_trx_util_pub.TRACE('mixed status: ..40 l_allow_status'||l_allow_status,'Material Status', 9);
340:
341: END ;
342:
343: END IF; --p_inventory_item_id
346:
347: --moved the code out of p_xfer_lpn_id condition bcoz another check added for 6918618
348: IF l_allow_status = 'N' THEN
349: l_validate := 1;
350: inv_trx_util_pub.TRACE('mixed status: .. 50 returning l_validate'||l_validate,'Material Status', 9);
351: return l_validate;
352: END IF;
353:
354: IF p_trx_type_id IS NOT NULL AND l_allow_status = 'Y' THEN --Checking for allow/disallow of source
354: IF p_trx_type_id IS NOT NULL AND l_allow_status = 'Y' THEN --Checking for allow/disallow of source
355: IF p_inventory_item_id IS NOT NULL THEN
356:
357: BEGIN
358: inv_trx_util_pub.TRACE('txn allowed or not: .. 60 verifying at source, inv id not null','Material Status', 9);
359: --First check whether the source material ALLOWS/DISALLOWS the transaction whether from LPN or LOOSE.
360:
361:
362: inv_trx_util_pub.TRACE('txn allowed or not: ..60.1 verifying at source','Material Status', 9);
358: inv_trx_util_pub.TRACE('txn allowed or not: .. 60 verifying at source, inv id not null','Material Status', 9);
359: --First check whether the source material ALLOWS/DISALLOWS the transaction whether from LPN or LOOSE.
360:
361:
362: inv_trx_util_pub.TRACE('txn allowed or not: ..60.1 verifying at source','Material Status', 9);
363: SELECT 'N'
364: INTO l_allow_transaction
365: FROM dual
366: WHERE EXISTS
381:
382: EXCEPTION
383: WHEN NO_DATA_FOUND THEN
384: l_allow_transaction := 'Y' ;
385: inv_trx_util_pub.TRACE('txn allowed or not: .. 70 Reached here for source:'||l_allow_transaction,'Material Status', 9);
386:
387: END ;
388:
389:
389:
390: --destination check if into another LPN else part for xfer into as LOOSE
391: IF l_allow_transaction = 'Y' THEN
392: IF p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
393: inv_trx_util_pub.TRACE('txn allowed or not: .. 80 verifying at dest, xfer lpn id not null','Material Status', 9);
394: BEGIN
395: inv_trx_util_pub.TRACE('txn allowed or not: .. 80.1 verifying at dest','Material Status', 9);
396: SELECT 'N'
397: INTO l_allow_transaction
391: IF l_allow_transaction = 'Y' THEN
392: IF p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
393: inv_trx_util_pub.TRACE('txn allowed or not: .. 80 verifying at dest, xfer lpn id not null','Material Status', 9);
394: BEGIN
395: inv_trx_util_pub.TRACE('txn allowed or not: .. 80.1 verifying at dest','Material Status', 9);
396: SELECT 'N'
397: INTO l_allow_transaction
398: FROM dual
399: WHERE EXISTS
411:
412: EXCEPTION
413: WHEN NO_DATA_FOUND THEN
414: l_allow_transaction := 'Y' ;
415: inv_trx_util_pub.TRACE('txn allowed or not: .. 90 Reached here l_allow_transaction:'||l_allow_transaction,'Material Status', 9);
416:
417: END ;
418:
419:
419:
420: ELSIF p_xfer_lpn_id is NULL THEN -- transfer lpn id is NULL so making the qty as loose at destination
421:
422: BEGIN
423: inv_trx_util_pub.TRACE('txn allowed or not: .. 100 verifying at dest, xfer lpn id is null','Material Status', 9);
424: SELECT 'N'
425: INTO l_allow_transaction
426: FROM dual
427: WHERE EXISTS
440: ) ;
441: EXCEPTION
442: WHEN NO_DATA_FOUND THEN
443: l_allow_transaction := 'Y' ;
444: inv_trx_util_pub.TRACE('txn allowed or not: .. 110 ','Material Status', 9);
445: END ;
446:
447: END IF;
448: END IF;
450:
451: ELSIF p_inventory_item_id is NULL and p_fromlpn_id is NOT NULL THEN -- p_inventory_item_id NULL so passing full LPN which just need to be checked at source
452:
453: BEGIN
454: inv_trx_util_pub.TRACE('txn allowed or not: .. 120 full LPN Case','Material Status', 9);
455: --checking whether the source material ALLOWS/DISALLOWS the transaction.
456: --if l_serial_status_enabled is 1 then it is serial controlled item
457: l_counter := 0;
458: FOR l_cur_wlc IN c_lpn_item LOOP
459: l_serial_controlled := 0;
460: l_serial_status_enabled := 0;
461:
462: IF inv_cache.set_item_rec(p_organization_id, l_cur_wlc.inventory_item_id) THEN
463: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.0','Material Status', 9);
464: IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
465: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.1 serial controlled','Material Status', 9);
466: l_serial_controlled := 1; -- Item is serial controlled
467: END IF;
461:
462: IF inv_cache.set_item_rec(p_organization_id, l_cur_wlc.inventory_item_id) THEN
463: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.0','Material Status', 9);
464: IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
465: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.1 serial controlled','Material Status', 9);
466: l_serial_controlled := 1; -- Item is serial controlled
467: END IF;
468:
469: IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
466: l_serial_controlled := 1; -- Item is serial controlled
467: END IF;
468:
469: IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
470: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.2 serial status enabled-true','Material Status', 9);
471: l_serial_status_enabled := 1;
472: END IF;
473: END IF;
474: --if it is serial controlled and its serial status is enabled
473: END IF;
474: --if it is serial controlled and its serial status is enabled
475: IF l_serial_controlled = 1 AND l_serial_status_enabled=1 THEN
476: l_counter := l_counter + 1;
477: inv_trx_util_pub.TRACE('txn allowed or not: .. Exitting from the loop','Material Status', 9);
478: EXIT; --exit even one item is serial controlled and its serial status enabled
479: END IF;
480: END LOOP;
481: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.3 Serial Count:'||l_counter,'Material Status', 9);
477: inv_trx_util_pub.TRACE('txn allowed or not: .. Exitting from the loop','Material Status', 9);
478: EXIT; --exit even one item is serial controlled and its serial status enabled
479: END IF;
480: END LOOP;
481: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.3 Serial Count:'||l_counter,'Material Status', 9);
482: --check for all non-serial items
483:
484: SELECT 'N'
485: INTO l_allow_transaction
506:
507: EXCEPTION
508: WHEN NO_DATA_FOUND THEN
509: l_allow_transaction := 'Y' ;
510: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.4 came here','Material Status', 9);
511:
512: END;
513: --if still the transaction is allowed and the lpn contains serials
514: BEGIN
512: END;
513: --if still the transaction is allowed and the lpn contains serials
514: BEGIN
515: IF l_counter <> 0 AND l_allow_transaction = 'Y' THEN --if l_counter !=0 then serials exist in the LPN
516: inv_trx_util_pub.TRACE('txn allowed or not: .. 120.5 Serials Exist so need to check MSN Status','Material Status', 9);
517: SELECT 'N'
518: INTO l_allow_transaction
519: FROM dual
520: WHERE EXISTS
537: END IF;
538: EXCEPTION
539: WHEN NO_DATA_FOUND THEN
540: l_allow_transaction := 'Y' ;
541: inv_trx_util_pub.TRACE('txn allowed or not: .. 130 came here','Material Status', 9);
542:
543: END;
544: --checking whether the destination LPN ALLOWS/DISALLOWS the transaction.
545: IF l_allow_transaction = 'Y' AND p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
542:
543: END;
544: --checking whether the destination LPN ALLOWS/DISALLOWS the transaction.
545: IF l_allow_transaction = 'Y' AND p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
546: inv_trx_util_pub.TRACE('txn allowed or not: .. 140 verifying at dest-- xfer lpn allows/not','Material Status', 9);
547: BEGIN
548: SELECT 'N'
549: INTO l_allow_transaction
550: FROM dual
561:
562: EXCEPTION
563: WHEN NO_DATA_FOUND THEN
564: l_allow_transaction := 'Y' ;
565: inv_trx_util_pub.TRACE('txn allowed or not: .. 150 came here','Material Status', 9);
566: END ;
567: END IF; --l_allow_transaction = 'Y' AND p_xfer_lpn_id condition
568:
569:
570:
571: END IF; --p_inventory_item_id check
572: IF l_allow_transaction = 'N' THEN
573: l_validate := 2;
574: inv_trx_util_pub.TRACE('txn allowed or not: .. 160 came here','Material Status', 9);
575: END IF;
576: inv_trx_util_pub.TRACE('txn allowed or not: .. 170 returning the value l_validate'||l_validate,'Material Status', 9);
577: RETURN l_validate;
578: END IF; --p_trx_type_id check
572: IF l_allow_transaction = 'N' THEN
573: l_validate := 2;
574: inv_trx_util_pub.TRACE('txn allowed or not: .. 160 came here','Material Status', 9);
575: END IF;
576: inv_trx_util_pub.TRACE('txn allowed or not: .. 170 returning the value l_validate'||l_validate,'Material Status', 9);
577: RETURN l_validate;
578: END IF; --p_trx_type_id check
579:
580: END is_status_applicable_lpns;
762: l_return_status VARCHAR2(1);
763: BEGIN
764:
765: if (g_debug = 1) then
766: inv_trx_util_pub.TRACE('inside non-overloaded is_status_applicable ', 'INV_MATERIAL_STATUS_GRP', 14);
767: end if;
768:
769: l_return_status:= is_status_applicable(p_wms_installed ,
770: p_trx_status_enabled ,
1018: end if;
1019: /* Bug 6918409 */
1020:
1021: if (g_debug = 1) then
1022: inv_trx_util_pub.TRACE('default org status id ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1023: end if;
1024:
1025: if (p_sub_code is not null ) and (p_object_type = 'Z' or p_object_type = 'A') then
1026: if(( p_organization_id <> nvl(g_isa_organization_id,-9999) ) or
1062: if (p_inventory_item_id is not null) then
1063: begin
1064:
1065: if (g_debug = 1) then
1066: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1067: end if;
1068: --ERES Deferred
1069: -- IF g_eres_enabled <> 'N' THEN
1070: BEGIN
1096: and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1097: and rownum = 1 ;
1098:
1099: if (g_debug = 1) then
1100: inv_trx_util_pub.TRACE('sub status id ' || g_isa_sub_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1101: end if;
1102:
1103: g_isa_organization_id := p_organization_id;
1104: g_isa_sub_code := p_sub_code;
1107: -- If no onhand record exists and the item is not locator/lot/serial controlled then
1108: -- we need to check whether the subinv status allows the transaction as that is going to be
1109: -- the status in MOQD except for the transfer transactions.
1110: if (g_debug = 1) then
1111: inv_trx_util_pub.TRACE('locator control '|| get_locator_control(p_organization_id, p_inventory_item_id, p_sub_code), 'INV_MATERIAL_STATUS_GRP', 14);
1112: end if;
1113:
1114: /* Bug 6918409 */
1115: if(l_lot_status_enabled = 'Y' or l_serial_controlled = 1
1121: else
1122: if (get_action_id(p_trx_type_id) in (2,3,21,28)) then -- Need to use action Id.
1123:
1124: if (g_debug = 1) then
1125: inv_trx_util_pub.TRACE('returning Y as its a transfer transaction', 'INV_MATERIAL_STATUS_GRP', 14);
1126: end if;
1127:
1128: return 'Y';
1129: else
1130: /* Bug 6918409 */
1131: l_count := 0;
1132: if (l_lot_status_enabled <> 'Y' ) then
1133: if (g_debug = 1) then
1134: inv_trx_util_pub.TRACE('lot status is not enabled', 'INV_MATERIAL_STATUS_GRP', 14);
1135: end if;
1136: /* Bug 6975416 : Modified the SQL for 10g
1137: * compliance
1138: */
1152: l_count := 0;
1153: end;
1154:
1155: if (g_debug = 1) then
1156: inv_trx_util_pub.TRACE('sub, l_count: '||l_count, 'INV_MATERIAL_STATUS_GRP', 14);
1157: end if;
1158:
1159: if (l_count = 1 and p_object_type <> 'A') then
1160: return 'Y';
1172: elsif inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1173: g_isa_sub_status_id := inv_cache.tosub_rec.status_id;
1174: end if;
1175: if (g_debug = 1) then
1176: inv_trx_util_pub.TRACE('sub, l_count is 0, sub_status_id: '||g_isa_sub_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1177: end if;
1178: end if;
1179: end if;
1180: end if;
1186: if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1187: if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1188:
1189: if (g_debug = 1) then
1190: inv_trx_util_pub.TRACE('sub, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1191: end if;
1192:
1193: FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1194: FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1207: end if;
1208: end if;
1209: else
1210: if (g_debug = 1) then
1211: inv_trx_util_pub.TRACE('Item id is null for sub', 'INV_MATERIAL_STATUS_GRP', 14);
1212: end if;
1213:
1214: begin
1215: select count(distinct status_id)
1269: FROM mtl_material_statuses_vl
1270: WHERE status_id = l_new_status_id ;
1271: end if;
1272: if (g_debug = 1) then
1273: inv_trx_util_pub.TRACE('sub,'||p_sub_code||' l_status_id ' || l_status_id ||',status ' || l_status_code || 'pending status id is:'
1274: ||l_new_status_id||'pending status is:'||l_new_status_code||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1275: end if;
1276:
1277:
1281: ,x_return_status => l_return_status
1282: ,x_msg_count => l_msg_count
1283: ,x_msg_data => l_msg_data);
1284: if (g_debug = 1) then
1285: inv_trx_util_pub.TRACE('sub, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1286: end if;
1287:
1288: IF l_new_status_id is not null THEN
1289: l_new_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1293: ,x_msg_count => l_msg_count
1294: ,x_msg_data => l_msg_data);
1295:
1296: if (g_debug = 1) then
1297: inv_trx_util_pub.TRACE('sub, l_new_return status ' || l_new_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1298: end if;
1299: END IF;
1300: if (p_object_type = 'Z') or (p_object_type = 'A' and
1301: (l_return_status = 'N' OR l_new_return_status = 'N')) then
1394: if (p_inventory_item_id is not null) then
1395: begin
1396:
1397: if (g_debug = 1) then
1398: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1399: end if;
1400: --ERES Deferred
1401: -- IF g_eres_enabled <> 'N' THEN
1402: BEGIN
1425: and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1426: and rownum = 1 ;
1427:
1428: if (g_debug = 1) then
1429: inv_trx_util_pub.TRACE('loc status id ' || g_isa_loc_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1430: end if;
1431:
1432: g_isa_organization_id := p_organization_id;
1433: g_isa_locator_id := p_locator_id;
1446: /* Bug 6918409 */
1447: l_count := 0;
1448: if (l_lot_status_enabled <> 'Y' ) then
1449: if (g_debug = 1) then
1450: inv_trx_util_pub.TRACE('loc, lot status is not enabled', 'INV_MATERIAL_STATUS_GRP', 14);
1451: end if;
1452: /* Bug 6975416 : Modified the SQL for 10g
1453: * compliance
1454: */
1468: l_count := 0;
1469: end;
1470:
1471: if (g_debug = 1) then
1472: inv_trx_util_pub.TRACE('loc, l_count: '||l_count, 'INV_MATERIAL_STATUS_GRP', 14);
1473: end if;
1474:
1475: if (l_count = 1 and p_object_type <> 'A') then
1476: return 'Y';
1516: if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1517: if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1518:
1519: if (g_debug = 1) then
1520: inv_trx_util_pub.TRACE('Loc, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1521: end if;
1522:
1523: FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1524: FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1536: end if;
1537: else
1538:
1539: if (g_debug = 1) then
1540: inv_trx_util_pub.TRACE('Item id is null for loc', 'INV_MATERIAL_STATUS_GRP', 14);
1541: end if;
1542:
1543: begin
1544: select count(distinct status_id)
1595: FROM mtl_material_statuses_vl
1596: WHERE status_id = l_new_status_id ;
1597: end if;
1598: if (g_debug = 1) then
1599: inv_trx_util_pub.TRACE('sub, l_status_id ' || l_status_id ||',status ' || l_status_code || 'pending status id is:'
1600: ||l_new_status_id||'pending status is:'||l_new_status_code||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1601: end if;
1602:
1603: l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1607: ,x_msg_count => l_msg_count
1608: ,x_msg_data => l_msg_data);
1609:
1610: if (g_debug = 1) then
1611: inv_trx_util_pub.TRACE('loc, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1612: end if;
1613: IF l_new_status_id is not null THEN
1614: l_new_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1615: p_status_id =>l_new_status_id
1618: ,x_msg_count => l_msg_count
1619: ,x_msg_data => l_msg_data);
1620:
1621: if (g_debug = 1) then
1622: inv_trx_util_pub.TRACE('sub, l_new_return status ' || l_new_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1623: end if;
1624: END IF;
1625:
1626: if (p_object_type = 'L') or (p_object_type = 'A' and
1709: else
1710: begin
1711:
1712: if (g_debug = 1 ) then
1713: inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1710: begin
1711:
1712: if (g_debug = 1 ) then
1713: inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1711:
1712: if (g_debug = 1 ) then
1713: inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1719: inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1712: if (g_debug = 1 ) then
1713: inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1719: inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1720: end if;
1713: inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1719: inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1720: end if;
1721:
1714: inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1719: inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1720: end if;
1721:
1722: --Adding following locator id logic to support locator_id = -1
1715: inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1716: inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1717: inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1718: inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1719: inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1720: end if;
1721:
1722: --Adding following locator id logic to support locator_id = -1
1723: --which is being passed from some mobile pages for null value.
1755: and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1756: and rownum = 1 ;
1757:
1758: if (g_debug = 1 ) then
1759: inv_trx_util_pub.TRACE('lot status id ' || g_isa_lot_number_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1760: end if;
1761:
1762: g_isa_lot_number := p_lot_number;
1763: exception
1827: if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1828: if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1829:
1830: if (g_debug = 1) then
1831: inv_trx_util_pub.TRACE('lot, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1832: end if;
1833:
1834: FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1835: FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1861: FROM mtl_material_statuses_vl
1862: WHERE status_id = l_new_status_id ;
1863: end if;
1864: if (g_debug = 1) then
1865: inv_trx_util_pub.TRACE('sub, l_status_id ' || l_status_id ||',status ' || l_status_code || 'pending status id is:'
1866: ||l_new_status_id||'pending status is:'||l_new_status_code||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1867: end if;
1868:
1869:
1874: ,x_msg_count => l_msg_count
1875: ,x_msg_data => l_msg_data);
1876:
1877: if (g_debug = 1) then
1878: inv_trx_util_pub.TRACE('lot, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1879: end if;
1880: IF l_new_status_id is not null THEN
1881: l_new_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1882: p_status_id =>l_new_status_id
1885: ,x_msg_count => l_msg_count
1886: ,x_msg_data => l_msg_data);
1887:
1888: if (g_debug = 1) then
1889: inv_trx_util_pub.TRACE('sub, l_new_return status ' || l_new_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1890: end if;
1891: END IF;
1892:
1893:
1936: AND serial_number = p_serial_number;
1937:
1938: EXCEPTION
1939: WHEN NO_DATA_FOUND THEN
1940: inv_trx_util_pub.TRACE('In dynamic serial checking default serial status'
1941: || l_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1942: SELECT default_serial_status_id
1943: INTO l_status_id
1944: FROM mtl_system_items
1969: FROM mtl_material_statuses_vl
1970: WHERE status_id = l_status_id ;
1971:
1972: if (g_debug = 1) then
1973: inv_trx_util_pub.TRACE('ser, l_status_id ' || l_status_id ||',status ' || l_status_code || ',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1974: end if;
1975:
1976: l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1977: p_status_id =>l_status_id
1989: ,x_msg_count => l_msg_count
1990: ,x_msg_data => l_msg_data);
1991:
1992: if (g_debug = 1) then
1993: inv_trx_util_pub.TRACE('ser, l_new_return status ' || l_new_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1994: end if;
1995: END IF;
1996: if (p_object_type = 'S') or (p_object_type = 'A' and
1997: l_return_status = 'N') then
2208:
2209: BEGIN
2210:
2211: if (g_debug = 1) then
2212: inv_trx_util_pub.TRACE('Inside get default status ', 'INV_MATERIAL_STATUS_GRP', 14);
2213: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2214: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' action ' || p_transaction_action_id || ' src ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2215: inv_trx_util_pub.TRACE('lock id '|| p_lock_id || ' header id '||p_header_id, 'INV_MATERIAL_STATUS_GRP', 14);
2216: end if;
2209: BEGIN
2210:
2211: if (g_debug = 1) then
2212: inv_trx_util_pub.TRACE('Inside get default status ', 'INV_MATERIAL_STATUS_GRP', 14);
2213: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2214: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' action ' || p_transaction_action_id || ' src ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2215: inv_trx_util_pub.TRACE('lock id '|| p_lock_id || ' header id '||p_header_id, 'INV_MATERIAL_STATUS_GRP', 14);
2216: end if;
2217:
2210:
2211: if (g_debug = 1) then
2212: inv_trx_util_pub.TRACE('Inside get default status ', 'INV_MATERIAL_STATUS_GRP', 14);
2213: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2214: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' action ' || p_transaction_action_id || ' src ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2215: inv_trx_util_pub.TRACE('lock id '|| p_lock_id || ' header id '||p_header_id, 'INV_MATERIAL_STATUS_GRP', 14);
2216: end if;
2217:
2218: if inv_cache.set_org_rec(p_organization_id) then
2211: if (g_debug = 1) then
2212: inv_trx_util_pub.TRACE('Inside get default status ', 'INV_MATERIAL_STATUS_GRP', 14);
2213: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2214: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' action ' || p_transaction_action_id || ' src ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2215: inv_trx_util_pub.TRACE('lock id '|| p_lock_id || ' header id '||p_header_id, 'INV_MATERIAL_STATUS_GRP', 14);
2216: end if;
2217:
2218: if inv_cache.set_org_rec(p_organization_id) then
2219: l_default_org_status_id := inv_cache.org_rec.default_status_id;
2219: l_default_org_status_id := inv_cache.org_rec.default_status_id;
2220: end if;
2221:
2222: if (g_debug = 1) then
2223: inv_trx_util_pub.TRACE('default org status ' || l_default_org_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2224: end if;
2225:
2226: if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
2227: if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
2254: AND rownum = 1;
2255: END IF ; /*LPN Status Project */
2256:
2257: if (g_debug = 1) then
2258: inv_trx_util_pub.TRACE('default status in MOQD ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2259: end if;
2260:
2261: if (l_default_status_id = -1) then
2262: if (l_serial_controlled = 0) then
2261: if (l_default_status_id = -1) then
2262: if (l_serial_controlled = 0) then
2263:
2264: if (g_debug = 1) then
2265: inv_trx_util_pub.TRACE('status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
2266: end if;
2267:
2268: FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
2269: FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
2278: end if;
2279: end if;
2280:
2281: if (g_debug = 1) then
2282: inv_trx_util_pub.TRACE(' 1 default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2283: end if;
2284:
2285: return l_default_status_id;
2286: end if;
2298: if (p_lock_id <> 0 and INV_TABLE_LOCK_PVT.lock_onhand_records(p_organization_id,p_inventory_item_id,null
2299: ,p_lot_number,p_sub_code,p_loc_id,1,p_header_id)
2300: ) then
2301: if (g_debug = 1) then
2302: inv_trx_util_pub.TRACE('Locked the MOQD record', 'INV_MATERIAL_STATUS_GRP', 14);
2303: end if;
2304: else
2305: if (g_debug = 1) then
2306: inv_trx_util_pub.TRACE('Unbale to lock MOQD ', 'INV_MATERIAL_STATUS_GRP', 14);
2302: inv_trx_util_pub.TRACE('Locked the MOQD record', 'INV_MATERIAL_STATUS_GRP', 14);
2303: end if;
2304: else
2305: if (g_debug = 1) then
2306: inv_trx_util_pub.TRACE('Unbale to lock MOQD ', 'INV_MATERIAL_STATUS_GRP', 14);
2307: end if;
2308: end if;
2309:
2310: -- Material Status Enhancement - Tracking bug: 13519864
2314: If (p_transaction_action_id is not null and p_transaction_action_id in (27,12,31) ) then
2315: If(m_status_id is not null) then
2316:
2317: if (g_debug = 1) then
2318: inv_trx_util_pub.TRACE('src status ex ' || m_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2319: end if;
2320: -- Calling the insert procedure to insert the status of the new onhand record into
2321: -- the table : mtl_material_status_history
2322: insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
2360:
2361: exception
2362: when others then
2363: if (g_debug = 1) then
2364: inv_trx_util_pub.TRACE('exception in the MTLN query',
2365: 'INV_MATERIAL_STATUS_GRP', 14);
2366: end if;
2367: end;
2368: end if;
2376: l_wip_lot_return := NVL(FND_PROFILE.VALUE('INV_DEFAULT_LOT_STATUS_FOR_RETURN'),2);
2377: If(l_wip_lot_return=2) THEN
2378: If(p_txn_source_id is not null) then
2379: if (g_debug = 1) then
2380: inv_trx_util_pub.TRACE('src id ' || p_txn_source_id, 'INV_MATERIAL_STATUS_GRP', 14);
2381: end if;
2382:
2383: begin
2384:
2396: ORDER BY mmt.creation_date desc) X
2397: where rownum = 1;
2398:
2399: if (g_debug = 1) then
2400: inv_trx_util_pub.TRACE('MTLN l_default_status_id ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2401: end if;
2402:
2403:
2404: if (l_default_status_id is not null) then
2407:
2408: exception
2409: when others then
2410: if (g_debug = 1) then
2411: inv_trx_util_pub.TRACE('exception in the MTLN query', 'INV_MATERIAL_STATUS_GRP', 14);
2412: end if;
2413: end;
2414: end if;
2415: end if;
2424: If (p_transaction_action_id is not null and p_transaction_action_id in (2,3,28,50,51,52,40,42) ) then --ADDED 50,51,52 FOR LPN STATUS PROJECT
2425: If(p_src_status_id is not null) then
2426:
2427: if (g_debug = 1) then
2428: inv_trx_util_pub.TRACE('src status ex ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2429: end if;
2430: -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
2431: -- the table : mtl_material_status_history
2432: insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
2520:
2521: WHEN OTHERS THEN
2522:
2523: if (g_debug = 1) then
2524: inv_trx_util_pub.TRACE('Exception default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2525: end if;
2526:
2527: return l_default_status_id;
2528:
2549:
2550: BEGIN
2551:
2552: if (g_debug = 1) then
2553: inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
2554: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2555: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' status id ' || p_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2556: inv_trx_util_pub.TRACE('lock id '|| p_lock_id, 'INV_MATERIAL_STATUS_GRP', 14);
2557: end if;
2550: BEGIN
2551:
2552: if (g_debug = 1) then
2553: inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
2554: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2555: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' status id ' || p_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2556: inv_trx_util_pub.TRACE('lock id '|| p_lock_id, 'INV_MATERIAL_STATUS_GRP', 14);
2557: end if;
2558:
2551:
2552: if (g_debug = 1) then
2553: inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
2554: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2555: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' status id ' || p_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2556: inv_trx_util_pub.TRACE('lock id '|| p_lock_id, 'INV_MATERIAL_STATUS_GRP', 14);
2557: end if;
2558:
2559:
2552: if (g_debug = 1) then
2553: inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
2554: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2555: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' status id ' || p_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2556: inv_trx_util_pub.TRACE('lock id '|| p_lock_id, 'INV_MATERIAL_STATUS_GRP', 14);
2557: end if;
2558:
2559:
2560: if (p_lock_id <> 0) then
2574:
2575: WHEN OTHERS THEN
2576:
2577: if (g_debug = 1) then
2578: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_ret_status: '|| l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
2579: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_count: '|| l_msg_count, 'INV_MATERIAL_STATUS_GRP', 14);
2580: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_data: '||l_msg_data, 'INV_MATERIAL_STATUS_GRP', 14);
2581: end if;
2582:
2575: WHEN OTHERS THEN
2576:
2577: if (g_debug = 1) then
2578: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_ret_status: '|| l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
2579: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_count: '|| l_msg_count, 'INV_MATERIAL_STATUS_GRP', 14);
2580: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_data: '||l_msg_data, 'INV_MATERIAL_STATUS_GRP', 14);
2581: end if;
2582:
2583: END insert_status_history;
2576:
2577: if (g_debug = 1) then
2578: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_ret_status: '|| l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
2579: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_count: '|| l_msg_count, 'INV_MATERIAL_STATUS_GRP', 14);
2580: inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_data: '||l_msg_data, 'INV_MATERIAL_STATUS_GRP', 14);
2581: end if;
2582:
2583: END insert_status_history;
2584:
2598: l_serial_controlled NUMBER := 0;
2599: BEGIN
2600:
2601: if (g_debug = 1) then
2602: inv_trx_util_pub.TRACE('Inside get default status conc ', 'INV_MATERIAL_STATUS_GRP', 14);
2603: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2604: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
2605: end if;
2606:
2599: BEGIN
2600:
2601: if (g_debug = 1) then
2602: inv_trx_util_pub.TRACE('Inside get default status conc ', 'INV_MATERIAL_STATUS_GRP', 14);
2603: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2604: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
2605: end if;
2606:
2607: if inv_cache.set_org_rec(p_organization_id) then
2600:
2601: if (g_debug = 1) then
2602: inv_trx_util_pub.TRACE('Inside get default status conc ', 'INV_MATERIAL_STATUS_GRP', 14);
2603: inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2604: inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
2605: end if;
2606:
2607: if inv_cache.set_org_rec(p_organization_id) then
2608: l_default_org_status_id := inv_cache.org_rec.default_status_id;
2610:
2611: l_default_status_id := l_default_org_status_id;
2612:
2613: if (g_debug = 1) then
2614: inv_trx_util_pub.TRACE('default org status ' || l_default_org_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2615: end if;
2616:
2617: if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
2618: if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
2666:
2667: end if;
2668:
2669: if (g_debug = 1) then
2670: inv_trx_util_pub.TRACE(' 1 default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2671: end if;
2672:
2673: return l_default_status_id;
2674:
2676:
2677: WHEN OTHERS THEN
2678:
2679: if (g_debug = 1) then
2680: inv_trx_util_pub.TRACE('Exception default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2681: end if;
2682:
2683: return l_default_status_id;
2684:
2764: l_onhand_status_id NUMBER ;
2765:
2766: BEGIN
2767: IF (g_debug = 1) then
2768: inv_trx_util_pub.TRACE('Inside get_onhand_status_id' , 'INV_MATERIAL_STATUS_GRP', 9);
2769: END IF;
2770: BEGIN
2771: SELECT NVL( status_id ,0 )
2772: INTO l_onhand_status_id
2860: --First get the value of move different status parameter.
2861: --Printing all input parameters to debug file
2862:
2863: if (g_debug = 1) then
2864: inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2861: --Printing all input parameters to debug file
2862:
2863: if (g_debug = 1) then
2864: inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2862:
2863: if (g_debug = 1) then
2864: inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2863: if (g_debug = 1) then
2864: inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2864: inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2865: inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2866: inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2867: inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2868: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2869: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2877: inv_trx_util_pub.TRACE('inside check_move_diff: p_primary_quantity = ' || p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
2870: inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2877: inv_trx_util_pub.TRACE('inside check_move_diff: p_primary_quantity = ' || p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
2878: end if;
2871: inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2877: inv_trx_util_pub.TRACE('inside check_move_diff: p_primary_quantity = ' || p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
2878: end if;
2879:
2872: inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2877: inv_trx_util_pub.TRACE('inside check_move_diff: p_primary_quantity = ' || p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
2878: end if;
2879:
2880:
2873: inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2874: inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2875: inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2876: inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2877: inv_trx_util_pub.TRACE('inside check_move_diff: p_primary_quantity = ' || p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
2878: end if;
2879:
2880:
2881: l_org_id := p_org_id ;
2900: l_allow_different_status := NVL(inv_cache.org_rec.allow_different_status,1);
2901: END IF;
2902:
2903: if (g_debug = 1) then
2904: inv_trx_util_pub.TRACE('inside check_move_diff: l_allow_different_status = ' || l_allow_different_status, 'INV_MATERIAL_STATUS_GRP', 9);
2905: end if;
2906: --Correcting below if condition as OR clause is not needed.Transfer subinventory has to be not null irrespective of transaction action.
2907: --IF (p_transfer_subinventory IS NOT NULL OR p_transaction_action_id = 21 )
2908: IF p_transfer_subinventory IS NOT NULL
2930: END IF;
2931:
2932: if (g_debug = 1) then
2933:
2934: inv_trx_util_pub.TRACE('inside check_move_diff: object_type = ' || p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2935: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2931:
2932: if (g_debug = 1) then
2933:
2934: inv_trx_util_pub.TRACE('inside check_move_diff: object_type = ' || p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2935: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2932: if (g_debug = 1) then
2933:
2934: inv_trx_util_pub.TRACE('inside check_move_diff: object_type = ' || p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2935: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2934: inv_trx_util_pub.TRACE('inside check_move_diff: object_type = ' || p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2935: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2935: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2943: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2936: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2937:
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2943: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2944: inv_trx_util_pub.TRACE('inside check_move_diff: l_locator_control_code = '||l_locator_control_code, 'INV_MATERIAL_STATUS_GRP', 9);
2938: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2943: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2944: inv_trx_util_pub.TRACE('inside check_move_diff: l_locator_control_code = '||l_locator_control_code, 'INV_MATERIAL_STATUS_GRP', 9);
2945:
2946: end if;
2939: inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2943: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2944: inv_trx_util_pub.TRACE('inside check_move_diff: l_locator_control_code = '||l_locator_control_code, 'INV_MATERIAL_STATUS_GRP', 9);
2945:
2946: end if;
2947:
2940: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2941:
2942: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2943: inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2944: inv_trx_util_pub.TRACE('inside check_move_diff: l_locator_control_code = '||l_locator_control_code, 'INV_MATERIAL_STATUS_GRP', 9);
2945:
2946: end if;
2947:
2948: IF (
2974: END IF;
2975: END IF;
2976:
2977: if (g_debug = 1) then
2978: inv_trx_util_pub.TRACE('inside check_move_diff: l_default_source_status = ' || l_default_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2979: inv_trx_util_pub.TRACE('inside check_move_diff: l_default_destin_status = ' ||l_default_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2980: end if;
2981:
2982: IF (l_default_source_status = 'Y' AND l_default_destin_status ='Y') THEN
2975: END IF;
2976:
2977: if (g_debug = 1) then
2978: inv_trx_util_pub.TRACE('inside check_move_diff: l_default_source_status = ' || l_default_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2979: inv_trx_util_pub.TRACE('inside check_move_diff: l_default_destin_status = ' ||l_default_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2980: end if;
2981:
2982: IF (l_default_source_status = 'Y' AND l_default_destin_status ='Y') THEN
2983: -- AND NVL(l_allow_different_status,1) = 2) --(O,O) Third if
2986: -- If both the organizations are onhand status controlled
2987: -- then get the status from corresponding SKU's and compare.
2988:
2989: if (g_debug = 1) then
2990: inv_trx_util_pub.TRACE('Inside O=O', 'INV_MATERIAL_STATUS_GRP', 9);
2991: end if ;
2992: IF l_serial_control_code_source IN (1,6) AND l_serial_control_code_destin IN (1,6) THEN
2993: --Get onhand status id from source org.
2994:
3012: , p_lpn_id => p_lpn_id
3013: , x_onhand_status_id => l_onhand_destin_status_id );
3014:
3015: if (g_debug = 1) then
3016: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_source_status_id = ' || l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3017: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '||l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3018: end if;
3019:
3020: -- Correcting nvl condition
3013: , x_onhand_status_id => l_onhand_destin_status_id );
3014:
3015: if (g_debug = 1) then
3016: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_source_status_id = ' || l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3017: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '||l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3018: end if;
3019:
3020: -- Correcting nvl condition
3021: IF NVL(l_onhand_source_status_id , 0) = 0 --no onhand in source org
3034: --AND NVL(l_allow_different_status,1) = 2)
3035: /*Bug 7833168 :Commenting above AND clause */
3036: --(L ,0)
3037: if (g_debug = 1) then
3038: inv_trx_util_pub.TRACE('Inside L-O', 'INV_MATERIAL_STATUS_GRP', 9);
3039: end if ;
3040: -- In source org, item is lot status enabled and destination org is onhand so
3041: -- get the lot status from source org and MOQD status from destination org
3042: IF l_serial_control_code_destin IN (1,6) THEN
3061: , p_lpn_id => p_lpn_id
3062: , x_onhand_status_id => l_onhand_destin_status_id );
3063:
3064: if (g_debug = 1) then
3065: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_status_id = ' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3066: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '|| l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3067: end if;
3068:
3069: -- Correcting nvl condition
3062: , x_onhand_status_id => l_onhand_destin_status_id );
3063:
3064: if (g_debug = 1) then
3065: inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_status_id = ' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3066: inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '|| l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3067: end if;
3068:
3069: -- Correcting nvl condition
3070: IF NVL(l_lot_status_id, 0) = 0 -- lot doesnt exists in source org
3081: ELSIF (l_default_source_status = 'Y' AND l_lot_destin_status = 'Y') THEN
3082: IF p_object_type = 'O' THEN
3083: --(O,L)
3084: if (g_debug = 1) then
3085: inv_trx_util_pub.TRACE('Inside O-L', 'INV_MATERIAL_STATUS_GRP', 9);
3086: end if ;
3087: -- source org is onhand status enabled and destination is lot status enabled
3088: -- Check onhand status id from source org and lot status id from destination org
3089:
3109: l_lot_status_id := 0;
3110: END;
3111:
3112: if (g_debug = 1) then
3113: inv_trx_util_pub.TRACE('l_onhand_source_status_id'||l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3114: inv_trx_util_pub.TRACE('l_lot_status_id' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3115: end if ;
3116: -- Correcting nvl condition
3117: IF NVL(l_onhand_source_status_id,0)= 0
3110: END;
3111:
3112: if (g_debug = 1) then
3113: inv_trx_util_pub.TRACE('l_onhand_source_status_id'||l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3114: inv_trx_util_pub.TRACE('l_lot_status_id' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
3115: end if ;
3116: -- Correcting nvl condition
3117: IF NVL(l_onhand_source_status_id,0)= 0
3118: OR NVL(l_lot_status_id ,0) = 0
3177: RAISE fnd_api.g_exc_error;
3178: END IF;
3179:
3180: IF (g_debug = 1) then
3181: inv_trx_util_pub.TRACE('l_qoh: '||l_qoh, 'INV_MATERIAL_STATUS_GRP', 9);
3182: inv_trx_util_pub.TRACE('p_primary_quantity '|| p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
3183: END IF;
3184: /* Added for bug#7833080 Start */
3185: IF (p_transaction_action_id = 3) THEN
3178: END IF;
3179:
3180: IF (g_debug = 1) then
3181: inv_trx_util_pub.TRACE('l_qoh: '||l_qoh, 'INV_MATERIAL_STATUS_GRP', 9);
3182: inv_trx_util_pub.TRACE('p_primary_quantity '|| p_primary_quantity, 'INV_MATERIAL_STATUS_GRP', 9);
3183: END IF;
3184: /* Added for bug#7833080 Start */
3185: IF (p_transaction_action_id = 3) THEN
3186: l_qoh := l_qoh + p_primary_quantity;
3201: END IF; --IF p_object_type = 'O' THEN
3202: END IF ; --Third if
3203: IF NOT l_go THEN
3204: if (g_debug = 1) then
3205: inv_trx_util_pub.TRACE('Comingling Occurs', 'INV_MATERIAL_STATUS_GRP', 9);
3206: end if ;
3207:
3208: fnd_message.set_name('INV','INV_TXF_MOVE_DIFF_MAT_STAT');
3209: fnd_msg_pub.ADD;
3292:
3293:
3294: BEGIN
3295: if(g_debug = 1)THEN
3296: inv_trx_util_pub.TRACE('In get_lpn_status','INV_MATERIAL_STATUS_GRP',9);
3297: END if;
3298: l_lpn_org_id := p_organization_id;
3299: l_lpn_sub := p_sub_code;
3300: l_lpn_loc := p_loc_id;
3308: where lpn_id = p_lpn_id;
3309: EXCEPTION
3310: WHEN NO_DATA_FOUND THEN
3311: if(g_debug = 1)THEN
3312: inv_trx_util_pub.TRACE('Unable to find the LPN''INV_MATERIAL_STATUS_GRP',9);
3313: END IF;
3314: x_return_status_code := NULL;
3315: x_return_status_id :=NULL;
3316: RETURN;
3318: END IF;
3319: IF l_lpn_context IN (WMS_Container_PUB.LPN_CONTEXT_PREGENERATED,
3320: WMS_Container_PUB.LPN_CONTEXT_VENDOR) THEN
3321: IF(g_debug = 1)THEN
3322: inv_trx_util_pub.TRACE('LPN CONTEXT IS '||l_lpn_context||' Status should be NULL for that','INV_MATERIAL_STATUS_GRP',9);
3323: END IF;
3324: l_return_status_id := NULL; --if lpn_context is 5 or 7 lpn status should be NULL
3325: ELSIF l_lpn_context IN (WMS_Container_PUB.LPN_CONTEXT_STORES,
3326: WMS_Container_PUB.LPN_CONTEXT_INTRANSIT) THEN
3329: INTO l_def_org_status
3330: FROM mtl_parameters
3331: WHERE organization_id = l_lpn_org_id;
3332: IF(g_debug = 1)THEN
3333: inv_trx_util_pub.TRACE('LPN CONTEXT IS '||l_lpn_context|| ' Status should be default org level staus which is '||l_return_status_id,'INV_MATERIAL_STATUS_GRP',9);
3334: END IF;
3335: l_return_status_id := l_def_org_status; --If lpn_context is 4 or 6 lpn status should be derived from default org parameters
3336: ELSE
3337:
3336: ELSE
3337:
3338: IF (l_lpn_context = WMS_Container_PUB.LPN_CONTEXT_PACKING )THEN--wlc don't exists for the lpn therefore checking mmtt
3339: IF(g_debug = 1)THEN
3340: inv_trx_util_pub.TRACE('WLC is not there and no child record is there for the lpn therefor querying mmtt for details','INV_MATERIAL_STATUS_GRP',9);
3341: END IF;
3342: FOR l_mmtt_cur IN mmtt_cur
3343: LOOP
3344: l_serial_status_enabled := 0;
3377: IF NVL(l_return_status_id,-99) <> NVL(l_status_id,-99) THEN --checking current status from the first status
3378: --There are mixed status so returning -1 and exiting the loop
3379: l_return_status_id := -1;
3380: IF(g_debug = 1)THEN
3381: inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 1','INV_MATERIAL_STATUS_GRP',9);
3382: END IF;
3383: EXIT;
3384: END IF;
3385: END LOOP; --mtlt_cur loop finished
3395: IF l_return_status_id <> l_status_id THEN --checking current status from the first status
3396: --There are mixed status so returning -1 and exiting the loop
3397: l_return_status_id := -1;
3398: IF(g_debug = 1)THEN
3399: inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 2','INV_MATERIAL_STATUS_GRP',9);
3400: END IF;
3401: EXIT;
3402: END IF;
3403: END LOOP; --l_msnt_cur loop finished
3460: l_status_id := l_return_status_id ; --assigning it for the first to check further if all the statuses are same or not
3461: END IF;
3462: IF (NVL(l_status_id,-99) <> NVL(l_return_status_id,-99)) THEN
3463: IF(g_debug = 1)THEN
3464: inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 3','INV_MATERIAL_STATUS_GRP',9);
3465: END IF;
3466: l_return_status_id := -1;
3467: END IF;
3468: ELSE --item is serial controlled therefor checkin msn for status
3501: ELSIF (NVL(l_return_status_id,-99) = -1)THEN
3502: l_return_status_code := FND_MESSAGE.get_string('WMS','WMS_LPN_STATUS_MIXED');
3503: END IF;
3504: IF(g_debug = 1)THEN
3505: inv_trx_util_pub.TRACE('Return Status id is '||l_return_status_id||' Return staus code is '||l_return_status_code,'INV_MATERIAL_STATUS_GRP',9);
3506: END IF;
3507: x_return_status_id := l_return_status_id;
3508: x_return_status_code := l_return_status_code;
3509: END get_lpn_status;
3561: FROM mtl_parameters
3562: WHERE organization_id =p_xfr_org_id ;
3563:
3564: IF(g_debug = 1)THEN
3565: inv_trx_util_pub.TRACE('Status_Commingle_Check: allow_different_status: '||l_allow_diff_status, 1);
3566: END IF;
3567: IF l_allow_diff_status<>1 THEN
3568: l_comingle:='Y';
3569: END IF;
3571: WHEN OTHERS THEN
3572: IF (g_debug = 1) THEN
3573: l_progress := 'WMSSCC-0001';
3574: IF(g_debug = 1)THEN
3575: inv_trx_util_pub.TRACE('Status_Commingle_Check: allow_different_status is not available'||l_progress, 1);
3576: END IF;
3577: END IF;
3578: END;
3579:
3602: EXCEPTION
3603:
3604: WHEN No_Data_Found THEN
3605: IF(g_debug = 1)THEN
3606: inv_trx_util_pub.TRACE('No onhand is available with this combination ');
3607: END IF;
3608:
3609: WHEN too_many_rows THEN
3610: l_source_status_id := NULL;
3609: WHEN too_many_rows THEN
3610: l_source_status_id := NULL;
3611: l_progress := 'WMSSCC-0002';
3612: IF(g_debug = 1)THEN
3613: inv_trx_util_pub.TRACE('Status_Commingle_Check: More than one status for the comming material: ', 1);
3614: inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3615: END IF;
3616: RAISE fnd_api.g_exc_error;
3617: WHEN OTHERS THEN
3610: l_source_status_id := NULL;
3611: l_progress := 'WMSSCC-0002';
3612: IF(g_debug = 1)THEN
3613: inv_trx_util_pub.TRACE('Status_Commingle_Check: More than one status for the comming material: ', 1);
3614: inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3615: END IF;
3616: RAISE fnd_api.g_exc_error;
3617: WHEN OTHERS THEN
3618: l_progress := 'WMSSCC-0003';
3616: RAISE fnd_api.g_exc_error;
3617: WHEN OTHERS THEN
3618: l_progress := 'WMSSCC-0003';
3619: l_source_status_id := null;
3620: inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3621: RAISE fnd_api.g_exc_error;
3622: END;
3623:
3624: IF l_source_status_id <> -9999 and p_item_id is not null THEN
3654: -- This case will occure if LPN contains all serial controlled items.
3655: FOR l_wlc_rec IN c_wlc_status() LOOP
3656: BEGIN
3657: IF(g_debug = 1)THEN
3658: inv_trx_util_pub.TRACE('In loop ');
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3655: FOR l_wlc_rec IN c_wlc_status() LOOP
3656: BEGIN
3657: IF(g_debug = 1)THEN
3658: inv_trx_util_pub.TRACE('In loop ');
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3656: BEGIN
3657: IF(g_debug = 1)THEN
3658: inv_trx_util_pub.TRACE('In loop ');
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3657: IF(g_debug = 1)THEN
3658: inv_trx_util_pub.TRACE('In loop ');
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3665: inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3658: inv_trx_util_pub.TRACE('In loop ');
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3665: inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3666: END IF;
3659: inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3665: inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3666: END IF;
3667:
3660: inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3665: inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3666: END IF;
3667:
3668: SELECT 'Y' INTO l_comingle
3661: inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3662: inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3663: inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3664: inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3665: inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3666: END IF;
3667:
3668: SELECT 'Y' INTO l_comingle
3669: FROM DUAL WHERE EXISTS
3694: RETURN l_comingle;
3695: EXCEPTION
3696: WHEN OTHERS THEN
3697: IF(g_debug = 1)THEN
3698: inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3699: END IF;
3700: RAISE fnd_api.g_exc_error;
3701: END Status_Commingle_Check;
3702:
3811: -- WHERE msnt.transaction_temp_id = l_transaction_temp_id
3812: -- AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
3813: BEGIN
3814: IF(g_debug = 1)THEN
3815: inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3812: -- AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
3813: BEGIN
3814: IF(g_debug = 1)THEN
3815: inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3813: BEGIN
3814: IF(g_debug = 1)THEN
3815: inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3814: IF(g_debug = 1)THEN
3815: inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3815: inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3816: inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3817: inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3818: inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3819: inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3820: inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3821: inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3822: inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3823: inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3824: inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3825: inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3832: inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3833: inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3826: inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3832: inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3833: inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3834: END IF;
3827: inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3832: inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3833: inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3834: END IF;
3835:
3828:
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3832: inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3833: inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3834: END IF;
3835:
3836: BEGIN
3829: inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3830: inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3831: inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3832: inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3833: inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3834: END IF;
3835:
3836: BEGIN
3837:
3841: where lpn_id = p_fromlpn_id;
3842: EXCEPTION
3843: WHEN NO_DATA_FOUND THEN
3844: IF(g_debug = 1)THEN
3845: inv_trx_util_pub.TRACE('Unable to find the LPN -- Error occured');
3846: END IF;
3847: RAISE fnd_api.g_exc_unexpected_error;
3848:
3849: END;
3848:
3849: END;
3850:
3851: IF(g_debug = 1)THEN
3852: inv_trx_util_pub.TRACE('LPN Context: '|| l_lpn_context,'inv_material_status_grp', 9);
3853: inv_trx_util_pub.TRACE('LPN Org Id: '|| l_lpn_org_id,'inv_material_status_grp', 9);
3854: inv_trx_util_pub.TRACE('LPN Sub: '|| l_lpn_sub,'inv_material_status_grp', 9);
3855: inv_trx_util_pub.TRACE('LPN Locator: '|| l_lpn_loc,'inv_material_status_grp', 9);
3856: END IF;
3849: END;
3850:
3851: IF(g_debug = 1)THEN
3852: inv_trx_util_pub.TRACE('LPN Context: '|| l_lpn_context,'inv_material_status_grp', 9);
3853: inv_trx_util_pub.TRACE('LPN Org Id: '|| l_lpn_org_id,'inv_material_status_grp', 9);
3854: inv_trx_util_pub.TRACE('LPN Sub: '|| l_lpn_sub,'inv_material_status_grp', 9);
3855: inv_trx_util_pub.TRACE('LPN Locator: '|| l_lpn_loc,'inv_material_status_grp', 9);
3856: END IF;
3857:
3850:
3851: IF(g_debug = 1)THEN
3852: inv_trx_util_pub.TRACE('LPN Context: '|| l_lpn_context,'inv_material_status_grp', 9);
3853: inv_trx_util_pub.TRACE('LPN Org Id: '|| l_lpn_org_id,'inv_material_status_grp', 9);
3854: inv_trx_util_pub.TRACE('LPN Sub: '|| l_lpn_sub,'inv_material_status_grp', 9);
3855: inv_trx_util_pub.TRACE('LPN Locator: '|| l_lpn_loc,'inv_material_status_grp', 9);
3856: END IF;
3857:
3858: if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
3851: IF(g_debug = 1)THEN
3852: inv_trx_util_pub.TRACE('LPN Context: '|| l_lpn_context,'inv_material_status_grp', 9);
3853: inv_trx_util_pub.TRACE('LPN Org Id: '|| l_lpn_org_id,'inv_material_status_grp', 9);
3854: inv_trx_util_pub.TRACE('LPN Sub: '|| l_lpn_sub,'inv_material_status_grp', 9);
3855: inv_trx_util_pub.TRACE('LPN Locator: '|| l_lpn_loc,'inv_material_status_grp', 9);
3856: END IF;
3857:
3858: if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
3859:
3867: --call kamesh api
3868: FOR l_cur_wlc IN c_lpn_item(p_inventory_item_id)
3869: LOOP
3870: IF(g_debug = 1)THEN
3871: inv_trx_util_pub.TRACE('In loop, Checking the material status for the item: '||l_cur_wlc.inventory_item_id);
3872: END IF;
3873: l_trx_allow := inv_material_status_grp.is_status_applicable_lpns(p_wms_installed => p_wms_installed,
3874: p_trx_status_enabled =>p_trx_status_enabled,
3875: p_trx_type_id => l_trx_type_id,
3888: p_xfer_locator_id => p_xfer_locator_id,
3889: p_xfer_org_id => p_xfer_org_id);
3890:
3891: IF(g_debug = 1)THEN
3892: inv_trx_util_pub.TRACE('l_trx_allow status for the item: '||l_cur_wlc.inventory_item_id||' is: '|| l_trx_allow,'Material Status', 9);
3893: END IF;
3894:
3895: if l_trx_allow=0 THEN
3896: l_trx_allowed_count := l_trx_allowed_count+1;
3910: elsif l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_RCV or
3911: l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_WIP then
3912: -- LPN is in receiving or WIP
3913: IF(g_debug = 1)THEN
3914: inv_trx_util_pub.TRACE('LPN is in Receiving:','inv_material_status_grp', 9);
3915: END IF;
3916:
3917: --IF p_trx_type_id IS NULL AND l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_WIP THEN
3918: --p_trx_type_id:=43; -- WIP Component Return.
3921:
3922: FOR l_cur_mtrl IN l_lpn_mtrl(p_inventory_item_id)
3923: LOOP
3924: IF(g_debug = 1)THEN
3925: inv_trx_util_pub.TRACE('In loop, Checking the material status for the item: '||l_cur_mtrl.inventory_item_id);
3926: END IF;
3927: l_serial_controlled := 0;
3928: l_serial_status_enabled := 0;
3929: IF inv_cache.set_item_rec(p_organization_id, l_cur_mtrl.inventory_item_id) THEN
3936: END IF;
3937: IF (l_serial_controlled <> 1) then
3938: -- Non serial controled item. It may be plain item or lot controlled item.
3939: IF(g_debug = 1)THEN
3940: inv_trx_util_pub.TRACE('Before calling get_default_status');
3941: END IF;
3942: l_return_status :=
3943: INV_MATERIAL_STATUS_GRP.get_default_status
3944: (p_organization_id => p_organization_id,
3948: p_lot_number => l_cur_mtrl.lot_number,
3949: p_lpn_id => l_cur_mtrl.lpn_id,
3950: p_transaction_action_id=> NULL, p_src_status_id => NULL);
3951: IF(g_debug = 1)THEN
3952: inv_trx_util_pub.TRACE('Value of l_return_status: '||l_return_status);
3953: END IF;
3954:
3955: l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3956: p_status_id => l_return_status
3958: ,x_return_status => l_trx_allowed
3959: ,x_msg_count => l_msg_count
3960: ,x_msg_data => l_msg_data);
3961: IF(g_debug = 1)THEN
3962: inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
3963: END If;
3964: if l_trx_allowed='Y' then
3965: l_trx_allowed_count:=l_trx_allowed_count+1;
3966: if l_trx_not_allowed_count > 0 then
4010: END IF; -- IF(l_serial_status_enabled = 1)
4011: END IF; -- IF (l_serial_controlled <> 1) then
4012:
4013: IF(g_debug = 1)THEN
4014: inv_trx_util_pub.TRACE('Completed if condition execution');
4015: END IF;
4016:
4017: if l_trx_not_allowed_count>0 and l_trx_allowed_count>0 then
4018: exit;
4017: if l_trx_not_allowed_count>0 and l_trx_allowed_count>0 then
4018: exit;
4019: end if;
4020: IF(g_debug = 1)THEN
4021: inv_trx_util_pub.TRACE('Completed one iteration');
4022: END IF;
4023: END LOOP; --FOR l_cur_wlc IN c_lpn_item
4024:
4025: --elsif l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_PACKING then
4026: -- LPN is in packing context.
4027: --l_trx_allowed_count:=l_trx_allowed_count+1;
4028: --l_trx_allowed_count:=l_trx_allowed_count-1;
4029: elsif l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_PACKING then
4030: inv_trx_util_pub.TRACE('LPN is in Packing context');
4031: inv_trx_util_pub.TRACE('Querying MMTT to get the status id and transaction type id');
4032:
4033: FOR l_mmtt_cur IN mmtt_cur(p_inventory_item_id)
4034: LOOP
4027: --l_trx_allowed_count:=l_trx_allowed_count+1;
4028: --l_trx_allowed_count:=l_trx_allowed_count-1;
4029: elsif l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_PACKING then
4030: inv_trx_util_pub.TRACE('LPN is in Packing context');
4031: inv_trx_util_pub.TRACE('Querying MMTT to get the status id and transaction type id');
4032:
4033: FOR l_mmtt_cur IN mmtt_cur(p_inventory_item_id)
4034: LOOP
4035: l_serial_status_enabled := 0;
4045: IF (inv_cache.item_rec.lot_control_code = 2) THEN
4046: l_lot_controlled := 1; -- item is lot controlled
4047: END IF;
4048: END IF;
4049: inv_trx_util_pub.TRACE('L_SERIAL_CONTROLLED_FLAG IS '||l_serial_controlled||'l_serial_status_enabled flag is '||l_serial_status_enabled,9);
4050: IF (l_lot_controlled = 1 AND l_serial_controlled = 0) THEN
4051: -- Item is lot controlled item. We are not taking care of both lot and serial controlled items.
4052: FOR l_mtlt_cur IN mtlt_cur(l_mmtt_cur.transaction_temp_id)
4053: LOOP
4064:
4065:
4066:
4067: IF(g_debug = 1)THEN
4068: inv_trx_util_pub.TRACE('Value of l_return_status: '||l_return_status);
4069:
4070: END IF;
4071:
4072: l_trx_allowed := inv_material_status_grp.is_trx_allowed(
4075: ,x_return_status => l_trx_allowed
4076: ,x_msg_count => l_msg_count
4077: ,x_msg_data => l_msg_data);
4078: IF(g_debug = 1)THEN
4079: inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
4080: -- inv_trx_util_pub.TRACE('Value of l_allow_transaction: '||l_allow_transaction);
4081:
4082: END If;
4083:
4076: ,x_msg_count => l_msg_count
4077: ,x_msg_data => l_msg_data);
4078: IF(g_debug = 1)THEN
4079: inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
4080: -- inv_trx_util_pub.TRACE('Value of l_allow_transaction: '||l_allow_transaction);
4081:
4082: END If;
4083:
4084: /* BEGIN
4117: END LOOP; -- FOR l_mtlt_cur IN mtlt_cur(l_mmtt_cur.transaction_temp_id)
4118:
4119: ELSIF (l_serial_controlled = 1) THEN
4120: IF (l_serial_status_enabled = 1) THEN
4121: inv_trx_util_pub.TRACE('It is serial controlled item ', 9);
4122: inv_trx_util_pub.TRACE('Querying MSN and MSNT to know the status ', 9);
4123: FOR l_msnt_cur IN msnt_cur(l_mmtt_cur.transaction_temp_id) LOOP
4124: l_return_status := l_msnt_cur.status_id;
4125: l_trx_allowed := inv_material_status_grp.is_trx_allowed(
4118:
4119: ELSIF (l_serial_controlled = 1) THEN
4120: IF (l_serial_status_enabled = 1) THEN
4121: inv_trx_util_pub.TRACE('It is serial controlled item ', 9);
4122: inv_trx_util_pub.TRACE('Querying MSN and MSNT to know the status ', 9);
4123: FOR l_msnt_cur IN msnt_cur(l_mmtt_cur.transaction_temp_id) LOOP
4124: l_return_status := l_msnt_cur.status_id;
4125: l_trx_allowed := inv_material_status_grp.is_trx_allowed(
4126: p_status_id => l_return_status
4128: ,x_return_status => l_trx_allowed
4129: ,x_msg_count => l_msg_count
4130: ,x_msg_data => l_msg_data);
4131: IF(g_debug = 1)THEN
4132: inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
4133: END If;
4134: if l_trx_allowed='Y' then
4135: l_trx_allowed_count:=l_trx_allowed_count+1;
4136: if l_trx_not_allowed_count > 0 then
4164: ,x_return_status => l_trx_allowed
4165: ,x_msg_count => l_msg_count
4166: ,x_msg_data => l_msg_data);
4167: IF(g_debug = 1)THEN
4168: inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
4169: END If;
4170: if l_trx_allowed='Y' then
4171: l_trx_allowed_count:=l_trx_allowed_count+1;
4172: if l_trx_not_allowed_count > 0 then
4185:
4186: if l_trx_not_allowed_count>0 and l_trx_allowed_count>0 then
4187: exit;
4188: end if;
4189: inv_trx_util_pub.TRACE('Completed one iteration of the MMTT');
4190: END LOOP; -- FOR l_mmtt_cur IN mmtt_cur(p_inventory_item_id)
4191:
4192:
4193: end if; --if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
4193: end if; --if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
4194:
4195:
4196: IF(g_debug = 1)THEN
4197: inv_trx_util_pub.TRACE('Before returning the from API is_trx_allow_lpns','Material Status', 9);
4198: inv_trx_util_pub.TRACE('l_trx_allowed_count: '||l_trx_allowed_count,'Material Status', 9);
4199: inv_trx_util_pub.TRACE('l_trx_not_allowed_count: ' || l_trx_not_allowed_count,'Material Status', 9);
4200: END IF;
4201:
4194:
4195:
4196: IF(g_debug = 1)THEN
4197: inv_trx_util_pub.TRACE('Before returning the from API is_trx_allow_lpns','Material Status', 9);
4198: inv_trx_util_pub.TRACE('l_trx_allowed_count: '||l_trx_allowed_count,'Material Status', 9);
4199: inv_trx_util_pub.TRACE('l_trx_not_allowed_count: ' || l_trx_not_allowed_count,'Material Status', 9);
4200: END IF;
4201:
4202: if l_trx_allowed_count=0 AND l_trx_not_allowed_count <> 0 then
4195:
4196: IF(g_debug = 1)THEN
4197: inv_trx_util_pub.TRACE('Before returning the from API is_trx_allow_lpns','Material Status', 9);
4198: inv_trx_util_pub.TRACE('l_trx_allowed_count: '||l_trx_allowed_count,'Material Status', 9);
4199: inv_trx_util_pub.TRACE('l_trx_not_allowed_count: ' || l_trx_not_allowed_count,'Material Status', 9);
4200: END IF;
4201:
4202: if l_trx_allowed_count=0 AND l_trx_not_allowed_count <> 0 then
4203: -- All the contents of the LPN dis allowed this transaction
4214: end if;
4215:
4216: EXCEPTION
4217: WHEN OTHERS THEN
4218: inv_trx_util_pub.TRACE('Exception occured in is_trx_allow_lpns in function');
4219:
4220: END is_trx_allow_lpns;
4221: /* -- LPN Status Project --*/
4222: --Bug 7626228, added following function to validate sub and loc together.