7: BEGIN
8: BEGIN
9: SELECT 1
10: INTO l_retval
11: FROM mtl_lot_numbers
12: WHERE gen_object_id = p_object_id;
13: EXCEPTION
14: WHEN NO_DATA_FOUND THEN
15: BEGIN
83: , p_primary_uom
84: , p_inventory_item_id
85: , p_object_number
86: , l_status_id
87: FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
88: WHERE mln.gen_object_id = p_object_id
89: AND mln.inventory_item_id = msivl.inventory_item_id
90: AND mln.organization_id = msivl.organization_id;
91:
256: , p_primary_uom
257: , p_inventory_item_id
258: , p_object_number
259: , l_status_id
260: FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
261: WHERE mln.gen_object_id = p_object_id
262: AND mln.inventory_item_id = msivl.inventory_item_id
263: AND mln.organization_id = msivl.organization_id;
264:
589: -- Lot Genealogy is available even if WMS is not installed
590: IF l_genealogy_prefix_suffix = 1 THEN --Prefix
591: SELECT lot_number || l_genealogy_delimitter || concatenated_segments
592: INTO l_object_number
593: FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
594: WHERE mln.gen_object_id = p_object_id
595: AND mln.inventory_item_id = msikfv.inventory_item_id
596: AND mln.organization_id = msikfv.organization_id;
597: ELSIF l_genealogy_prefix_suffix = 2 THEN --Suffix
596: AND mln.organization_id = msikfv.organization_id;
597: ELSIF l_genealogy_prefix_suffix = 2 THEN --Suffix
598: SELECT concatenated_segments || l_genealogy_delimitter || lot_number
599: INTO l_object_number
600: FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
601: WHERE mln.gen_object_id = p_object_id
602: AND mln.inventory_item_id = msikfv.inventory_item_id
603: AND mln.organization_id = msikfv.organization_id;
604: ELSIF l_genealogy_prefix_suffix = 3 THEN --None
603: AND mln.organization_id = msikfv.organization_id;
604: ELSIF l_genealogy_prefix_suffix = 3 THEN --None
605: SELECT lot_number
606: INTO l_object_number
607: FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
608: WHERE mln.gen_object_id = p_object_id
609: AND mln.inventory_item_id = msikfv.inventory_item_id
610: AND mln.organization_id = msikfv.organization_id;
611: END IF;
614: AND p_object_type2 = 1 THEN -- Lot Serial controlled
615: BEGIN
616: SELECT lot_number
617: INTO l_lot_number
618: FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
619: WHERE mln.gen_object_id = p_object_id2
620: AND mln.inventory_item_id = msikfv.inventory_item_id
621: AND mln.organization_id = msikfv.organization_id;
622: EXCEPTION