[Home] [Help]
5114: , lpn.organization_id
5115: , msi.serial_number_control_code
5116: , msi.primary_uom_code
5117: , lpnc.secondary_quantity -- Bug 7708998
5118: FROM wms_lpn_contents lpnc, wms_license_plate_numbers lpn, mtl_system_items_b msi, rcv_shipment_headers rsh
5119: WHERE rsh.shipment_header_id = p_shipment_header_id
5120: AND((lpn.source_header_id = rsh.shipment_header_id)
5121: OR (lpn.source_header_id IS NULL AND lpn.source_name = rsh.shipment_num)) --BUG14846625/16711363
5122: AND lpn.lpn_context IN(6, 7) -- only those pre-ASN receiving ones
5126: -- LPN id will be NULL, In this case we should not expand the LPN
5127: -- in which case start with lpn_id = p_lpn_id will fail.
5128: AND(lpn.lpn_id = NVL(p_lpn_id, lpn.lpn_id)
5129: OR lpn.lpn_id IN(SELECT lpn_id
5130: FROM wms_license_plate_numbers
5131: START WITH lpn_id = p_lpn_id
5132: CONNECT BY parent_lpn_id = PRIOR lpn_id))
5133: --AND lpn.lpn_id = nvl(p_lpn_id, lpn.lpn_id)
5134: AND lpn.lpn_id = lpnc.parent_lpn_id
5622:
5623: select count(1) into
5624: l_msni_count
5625: from mtl_serial_numbers_interface msni, rcv_transactions_interface rti,
5626: wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
5627: mtl_serial_numbers msn -- Bug 16517269
5628: where rti.interface_transaction_id = msni.product_transaction_id
5629: and rsh.shipment_header_id = p_shipment_header_id
5630: and (lpn.source_header_id = rsh.shipment_header_id
8505: , x_msg_data OUT NOCOPY VARCHAR2
8506: ) IS
8507: CURSOR l_curs_remaining_asn IS --modified for bug 4951745
8508: SELECT 1
8509: FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
8510: WHERE (lpn.source_header_id = rsh.shipment_header_id
8511: OR lpn.source_name = rsh.shipment_num)
8512: AND rsh.shipment_header_id = p_shipment_header_id
8513: AND (p_lpn_id IS NULL OR lpn.lpn_id=p_lpn_id)
8522:
8523: --Added for Bug 4951745
8524: CURSOR l_curs_remaining_asn_has_lpn IS
8525: SELECT 1
8526: FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
8527: WHERE (lpn.source_header_id = rsh.shipment_header_id
8528: OR lpn.source_name = rsh.shipment_num)
8529: AND rsh.shipment_header_id = p_shipment_header_id
8530: AND lpn.lpn_id = p_lpn_id
8656:
8657: l_progress := '20';
8658:
8659: -- update LPN context to receiving
8660: UPDATE wms_license_plate_numbers
8661: SET lpn_context = 5
8662: WHERE lpn_id = p_lpn_id;
8663:
8664: l_progress := '30';
8695: l_wms_enabled VARCHAR2(1);
8696:
8697: CURSOR l_curs_lpn_for_ship IS
8698: SELECT lpn_id
8699: FROM wms_license_plate_numbers
8700: WHERE source_name = (SELECT shipment_num
8701: FROM rcv_shipment_headers
8702: WHERE shipment_header_id = p_shipment_header_id);
8703:
8702: WHERE shipment_header_id = p_shipment_header_id);
8703:
8704: CURSOR l_curs_lpn_for_asn IS
8705: SELECT lpn_id
8706: FROM wms_license_plate_numbers
8707: WHERE source_header_id = p_shipment_header_id;
8708:
8709: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8710: BEGIN
8963: x_lpn_flag := 0;
8964:
8965: SELECT COUNT(*)
8966: INTO l_lpn_count
8967: FROM wms_license_plate_numbers
8968: WHERE organization_id = p_from_organization_id
8969: AND lpn_context = 6
8970: AND source_name = p_shipment_number;
8971:
9056: x_return_status := fnd_api.g_ret_sts_success;
9057:
9058: SELECT COUNT(*)
9059: INTO l_lpn_count
9060: FROM wms_license_plate_numbers
9061: WHERE lpn_context = 7
9062: AND source_header_id = p_shipment_header_id;
9063:
9064: l_progress := '20';
9214: SELECT organization_id
9215: , lpn_context
9216: INTO l_lpn_org
9217: , l_lpn_context
9218: FROM wms_license_plate_numbers
9219: WHERE lpn_id = l_lpn_id;
9220: ELSE
9221: SELECT organization_id
9222: , lpn_context
9221: SELECT organization_id
9222: , lpn_context
9223: INTO l_lpn_org
9224: , l_lpn_context
9225: FROM wms_license_plate_numbers
9226: WHERE lpn_id = l_lpn_id
9227: AND lpn_id IN(SELECT parent_lpn_id
9228: FROM wms_lpn_contents
9229: WHERE parent_lpn_id = l_lpn_id);
9378: x_return_status := fnd_api.g_ret_sts_success;
9379: SAVEPOINT rcv_update_lpn_org_sp;
9380: l_progress := '10';
9381:
9382: UPDATE wms_license_plate_numbers
9383: SET organization_id = p_organization_id
9384: , subinventory_code = ''
9385: , locator_id = NULL
9386: WHERE lpn_id = p_lpn_id;