[Home] [Help]
620: IF l_serial_control_code NOT IN (1,6) THEN
621: --13695803
622: SELECT lpn_context
623: INTO l_lpn_context
624: FROM wms_license_plate_numbers
625: WHERE lpn_id = l_lpn
626: AND organization_id = l_org_id;
627:
628: IF l_lpn_context = 3 THEN
1338: -- only receiving or WIP.
1339:
1340: SELECT lpn_context
1341: INTO l_lpn_context
1342: FROM wms_license_plate_numbers
1343: WHERE lpn_id = l_lpn_id
1344: AND organization_id = p_org_id;
1345:
1346: -- ATF_J2 moved crossdock call to after cleanup_suggestions
1465: SELECT subinventory_code
1466: , locator_id
1467: INTO l_m_sub
1468: , l_m_loc
1469: FROM wms_license_plate_numbers
1470: WHERE lpn_id = l_lpn_id;
1471:
1472: -- Need to close those old MOL lines
1473: -- for inventory LPN
3958: */
3959: -- Check to see if its a receipt LPN
3960: SELECT lpn_context
3961: INTO l_lpn_context
3962: FROM wms_license_plate_numbers
3963: WHERE lpn_id = p_lpn_id;
3964:
3965: IF l_lpn_context = 3 THEN
3966: IF (l_debug =1) THEN
4087: SELECT 1
4088: INTO l_exist_lpn
4089: FROM DUAL
4090: WHERE EXISTS(SELECT 1
4091: FROM wms_license_plate_numbers
4092: WHERE license_plate_number = p_to_lpn
4093: AND organization_id = p_org_id);
4094: EXCEPTION
4095: WHEN NO_DATA_FOUND THEN
4155: ELSE
4156: -- LPN exists. Get LPN ID
4157: SELECT lpn_id
4158: INTO l_to_lpn_id
4159: FROM wms_license_plate_numbers
4160: WHERE license_plate_number = p_to_lpn
4161: AND organization_id = p_org_id;
4162: END IF;
4163: END IF;
6107: --get the context
6108: BEGIN
6109: SELECT lpn_context
6110: INTO l_lpn_context
6111: FROM wms_license_plate_numbers
6112: WHERE lpn_id = l_lpn_id
6113: AND organization_id = l_org_id;
6114: EXCEPTION
6115: WHEN NO_DATA_FOUND THEN
6139: SELECT 1
6140: INTO l_rti_cnt
6141: FROM rcv_transactions_interface
6142: WHERE (transfer_lpn_id IN (SELECT lpn_id
6143: FROM wms_license_plate_numbers
6144: START WITH lpn_id = l_lpn_id
6145: CONNECT BY PRIOR lpn_id = parent_lpn_id)
6146: OR lpn_id IN (SELECT lpn_id
6147: FROM wms_license_plate_numbers
6143: FROM wms_license_plate_numbers
6144: START WITH lpn_id = l_lpn_id
6145: CONNECT BY PRIOR lpn_id = parent_lpn_id)
6146: OR lpn_id IN (SELECT lpn_id
6147: FROM wms_license_plate_numbers
6148: START WITH lpn_id = l_lpn_id
6149: CONNECT BY PRIOR lpn_id = parent_lpn_id))
6150: AND to_organization_id = l_org_id
6151: AND TRANSACTION_STATUS_CODE <> 'ERROR'
6183: -- current session from hanging (bug 1724818).
6184: BEGIN
6185: SELECT lpn_id
6186: INTO l_dummy
6187: FROM wms_license_plate_numbers
6188: WHERE lpn_id = l_lpn_id
6189: AND organization_id = l_org_id
6190: FOR UPDATE NOWAIT;
6191: EXCEPTION
7142: SELECT 1
7143: , lpn_id
7144: INTO l_count
7145: , l_to_lpn_id
7146: FROM wms_license_plate_numbers wlpn
7147: WHERE wlpn.license_plate_number = p_to_lpn
7148: AND wlpn.organization_id = p_org_id;
7149: EXCEPTION
7150: WHEN NO_DATA_FOUND THEN
7262: INTO l_count
7263: FROM DUAL
7264: WHERE EXISTS(
7265: SELECT 1
7266: FROM wms_license_plate_numbers wlpn
7267: WHERE wlpn.organization_id = p_org_id
7268: AND license_plate_number = p_to_lpn
7269: AND (
7270: wlpn.lpn_context = 5
7372: END IF;
7373:
7374: SELECT wlc.lpn_context
7375: INTO l_lpn_context
7376: FROM wms_license_plate_numbers wlc
7377: WHERE wlc.license_plate_number = p_to_lpn
7378: AND wlc.organization_id = p_org_id;
7379:
7380: IF (l_debug = 1) THEN
7403:
7404: l_lpn_update := NULL;
7405:
7406: -- 4411792 The below is replaced by the above API call
7407: --UPDATE wms_license_plate_numbers
7408: --SET lpn_context = wms_container_pub.lpn_context_inv
7409: --WHERE license_plate_number = p_to_lpn
7410: --AND organization_id = p_org_id;
7411:
7745: -- Create a cursor to loop through all the nested LPNs within
7746: -- the given outer LPN, p_lpn_id.
7747: CURSOR lpn_csr IS
7748: SELECT lpn_id
7749: FROM wms_license_plate_numbers
7750: START WITH lpn_id = p_lpn_id
7751: CONNECT BY PRIOR lpn_id = parent_lpn_id;
7752: l_current_lpn_id NUMBER;
7753:
7810: AND EXISTS
7811: (SELECT 1
7812: FROM
7813: mtl_txn_request_headers moh,
7814: wms_license_plate_numbers wlc
7815: WHERE mol.header_id = moh.header_id
7816: AND moh.move_order_type = inv_globals.g_move_order_put_away
7817: AND wlc.lpn_id = mol.lpn_id
7818: AND wlc.lpn_context = 1);
7832: SELECT subinventory_code
7833: , locator_id
7834: INTO l_m_sub
7835: , l_m_loc
7836: FROM wms_license_plate_numbers
7837: WHERE lpn_id = l_lpn_id;
7838:
7839: SELECT mil.project_id
7840: , mil.task_id
7975: l_mmtt_primary_qty NUMBER;
7976:
7977: CURSOR lpn_cur IS
7978: SELECT lpn_id
7979: FROM wms_license_plate_numbers
7980: START WITH lpn_id = p_lpn_id
7981: CONNECT BY parent_lpn_id = PRIOR lpn_id;
7982:
7983: BEGIN
8421: -- Bug# 3281512 - Performance Fixes
8422: -- Cursor to retrieve the nested LPNs within a given outer LPN
8423: CURSOR nested_lpn_cursor IS
8424: SELECT lpn_id
8425: FROM wms_license_plate_numbers
8426: START WITH lpn_id = p_lpn_id
8427: CONNECT BY PRIOR lpn_id = parent_lpn_id;
8428: l_current_lpn_id NUMBER;
8429:
8570: mtl_txn_request_lines mtrl
8571: WHERE mmtt.organization_id = p_organization_id
8572: AND mmtt.move_order_line_id = mtrl.line_id
8573: AND mtrl.lpn_id IN (SELECT lpn_id
8574: FROM wms_license_plate_numbers
8575: START WITH lpn_id = p_lpn_id
8576: CONNECT BY parent_lpn_id = PRIOR lpn_id);
8577:
8578:
8578:
8579:
8580: SELECT lpn_context,subinventory_code,locator_id
8581: INTO l_lpn_context,l_subinventory,l_locator_id
8582: FROM wms_license_plate_numbers
8583: WHERE lpn_id = p_lpn_id;
8584:
8585: IF l_lpn_context = 1 THEN
8586: -- Inventory LPN so need to create MOLs first
9259: -- Bug# 3434940 - Performance Fixes
9260: -- Cursor to retrieve the nested LPNs within a given outer LPN
9261: CURSOR nested_lpn_cursor IS
9262: SELECT lpn_id
9263: FROM wms_license_plate_numbers
9264: START WITH lpn_id = p_lpn_id
9265: CONNECT BY PRIOR lpn_id = parent_lpn_id;
9266: l_current_lpn_id NUMBER;
9267: