[Home] [Help]
4846: , lpnc.lot_number
4847: , lpn.organization_id
4848: , msi.serial_number_control_code
4849: , msi.primary_uom_code
4850: FROM wms_lpn_contents lpnc, wms_license_plate_numbers lpn, mtl_system_items_b msi, rcv_shipment_headers rsh
4851: WHERE rsh.shipment_header_id = p_shipment_header_id
4852: AND(lpn.source_header_id = rsh.shipment_header_id
4853: OR lpn.source_name = rsh.shipment_num)
4854: AND lpn.lpn_context IN(6, 7) -- only those pre-ASN receiving ones
4858: -- LPN id will be NULL, In this case we should not expand the LPN
4859: -- in which case start with lpn_id = p_lpn_id will fail.
4860: AND(lpn.lpn_id = NVL(p_lpn_id, lpn.lpn_id)
4861: OR lpn.lpn_id IN(SELECT lpn_id
4862: FROM wms_license_plate_numbers
4863: START WITH lpn_id = p_lpn_id
4864: CONNECT BY parent_lpn_id = PRIOR lpn_id))
4865: --AND lpn.lpn_id = nvl(p_lpn_id, lpn.lpn_id)
4866: AND lpn.lpn_id = lpnc.parent_lpn_id
8109: , x_msg_data OUT NOCOPY VARCHAR2
8110: ) IS
8111: CURSOR l_curs_remaining_asn IS --modified for bug 4951745
8112: SELECT 1
8113: FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
8114: WHERE (lpn.source_header_id = rsh.shipment_header_id
8115: OR lpn.source_name = rsh.shipment_num)
8116: AND rsh.shipment_header_id = p_shipment_header_id
8117: AND (p_lpn_id IS NULL OR lpn.lpn_id=p_lpn_id)
8126:
8127: --Added for Bug 4951745
8128: CURSOR l_curs_remaining_asn_has_lpn IS
8129: SELECT 1
8130: FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
8131: WHERE (lpn.source_header_id = rsh.shipment_header_id
8132: OR lpn.source_name = rsh.shipment_num)
8133: AND rsh.shipment_header_id = p_shipment_header_id
8134: AND lpn.lpn_id = p_lpn_id
8260:
8261: l_progress := '20';
8262:
8263: -- update LPN context to receiving
8264: UPDATE wms_license_plate_numbers
8265: SET lpn_context = 5
8266: WHERE lpn_id = p_lpn_id;
8267:
8268: l_progress := '30';
8299: l_wms_enabled VARCHAR2(1);
8300:
8301: CURSOR l_curs_lpn_for_ship IS
8302: SELECT lpn_id
8303: FROM wms_license_plate_numbers
8304: WHERE source_name = (SELECT shipment_num
8305: FROM rcv_shipment_headers
8306: WHERE shipment_header_id = p_shipment_header_id);
8307:
8306: WHERE shipment_header_id = p_shipment_header_id);
8307:
8308: CURSOR l_curs_lpn_for_asn IS
8309: SELECT lpn_id
8310: FROM wms_license_plate_numbers
8311: WHERE source_header_id = p_shipment_header_id;
8312:
8313: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8314: BEGIN
8567: x_lpn_flag := 0;
8568:
8569: SELECT COUNT(*)
8570: INTO l_lpn_count
8571: FROM wms_license_plate_numbers
8572: WHERE organization_id = p_from_organization_id
8573: AND lpn_context = 6
8574: AND source_name = p_shipment_number;
8575:
8660: x_return_status := fnd_api.g_ret_sts_success;
8661:
8662: SELECT COUNT(*)
8663: INTO l_lpn_count
8664: FROM wms_license_plate_numbers
8665: WHERE lpn_context = 7
8666: AND source_header_id = p_shipment_header_id;
8667:
8668: l_progress := '20';
8818: SELECT organization_id
8819: , lpn_context
8820: INTO l_lpn_org
8821: , l_lpn_context
8822: FROM wms_license_plate_numbers
8823: WHERE lpn_id = l_lpn_id;
8824: ELSE
8825: SELECT organization_id
8826: , lpn_context
8825: SELECT organization_id
8826: , lpn_context
8827: INTO l_lpn_org
8828: , l_lpn_context
8829: FROM wms_license_plate_numbers
8830: WHERE lpn_id = l_lpn_id
8831: AND lpn_id IN(SELECT parent_lpn_id
8832: FROM wms_lpn_contents
8833: WHERE parent_lpn_id = l_lpn_id);
8982: x_return_status := fnd_api.g_ret_sts_success;
8983: SAVEPOINT rcv_update_lpn_org_sp;
8984: l_progress := '10';
8985:
8986: UPDATE wms_license_plate_numbers
8987: SET organization_id = p_organization_id
8988: , subinventory_code = ''
8989: , locator_id = NULL
8990: WHERE lpn_id = p_lpn_id;