[Home] [Help]
59: SELECT 'Check for empty LPN'
60: FROM DUAL
61: WHERE EXISTS
62: (SELECT 'Non serialized items'
63: FROM WMS_LPN_CONTENTS
64: WHERE parent_lpn_id = p_lpn_id);
65: CURSOR c_lpn_serial_contents IS
66: SELECT 'Check for empty LPN'
67: FROM DUAL
115: RAISE FND_API.G_EXC_ERROR;
116: END IF;
117: CLOSE c_lpn;
118:
119: /* Check WMS_LPN_CONTENTS table for any item(s) stored within the LPN */
120: OPEN c_lpn_contents;
121: FETCH c_lpn_contents INTO l_lpn_contents_exist;
122: IF c_lpn_contents%FOUND THEN
123: FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NON_EMPTY_LPN');
247: -- SELECT *
248: SELECT parent_lpn_id, inventory_item_id, item_description,
249: organization_id, revision, lot_number,
250: serial_number, quantity, uom_code, cost_group_id
251: FROM WMS_LPN_CONTENTS
252: WHERE parent_lpn_id = l_current_lpn
253: AND NVL(serial_summary_entry, 2) = 2;
254: CURSOR lpn_serial_contents_cursor IS
255: -- Bug# 1546081
564: UPDATE WMS_LICENSE_PLATE_NUMBERS
565: SET parent_lpn_id = p_lpn_id_dest
566: WHERE parent_lpn_id = p_lpn_id_source;
567:
568: UPDATE WMS_LPN_CONTENTS
569: SET parent_lpn_id = p_lpn_id_dest
570: WHERE parent_lpn_id = p_lpn_id_source;
571:
572: UPDATE MTL_SERIAL_NUMBERS
1084: END LOOP;
1085: ELSE -- Find outermost LPN(s) for a specific item
1086: -- First we need to get all of the LPN's which store the given
1087: -- inventory item. Use DBMS_SQL to do a dynamic query on the
1088: -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1089: -- given item with the specified parameters.
1090:
1091: l_SelectStmt := 'SELECT PARENT_LPN_ID FROM WMS_LPN_CONTENTS WHERE ';
1092: l_SelectStmt := l_SelectStmt || 'inventory_item_id = ' ||
1087: -- inventory item. Use DBMS_SQL to do a dynamic query on the
1088: -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1089: -- given item with the specified parameters.
1090:
1091: l_SelectStmt := 'SELECT PARENT_LPN_ID FROM WMS_LPN_CONTENTS WHERE ';
1092: l_SelectStmt := l_SelectStmt || 'inventory_item_id = ' ||
1093: p_inventory_item_id || ' AND ';
1094: IF (p_revision IS NOT NULL) THEN
1095: l_SelectStmt := l_SelectStmt || 'revision = ' ||
1130:
1131: -- Retrieve the rows from the buffer into a temp variable.
1132: DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_temp_lpn);
1133:
1134: -- Get the outermost LPN for each parent lpn from WMS_LPN_CONTENTS
1135: l_current_lpn := l_temp_lpn;
1136: FOR l_lpn_record IN nested_parent_lpn_cursor_2 LOOP
1137: IF (l_lpn_record.parent_lpn_id IS NULL) THEN
1138: l_temp_table(l_index) := l_lpn_record;
1276: l_single_item_flag INTEGER;
1277: l_dummy_lpn NUMBER;
1278: CURSOR item_cursor IS
1279: SELECT parent_lpn_id
1280: FROM WMS_LPN_CONTENTS
1281: WHERE parent_lpn_id = l_temp_lpn
1282: AND NVL(serial_summary_entry, 2) = 2;
1283: CURSOR container_cursor IS
1284: SELECT parent_lpn_id
1320: /* End of input validation */
1321:
1322: -- First we need to get all of the LPN's which store the given
1323: -- inventory item. Use DBMS_SQL to do a dynamic query on the
1324: -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1325: -- given item with the specified parameters.
1326:
1327: l_SelectStmt := 'SELECT a.lpn_id
1328: FROM WMS_LICENSE_PLATE_NUMBERS a, WMS_LPN_CONTENTS b WHERE ';
1324: -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1325: -- given item with the specified parameters.
1326:
1327: l_SelectStmt := 'SELECT a.lpn_id
1328: FROM WMS_LICENSE_PLATE_NUMBERS a, WMS_LPN_CONTENTS b WHERE ';
1329:
1330: IF (p_lpn_context IS NOT NULL) THEN
1331: l_SelectStmt := l_SelectStmt || 'a.lpn_context = ' ||
1332: p_lpn_context || ' AND ';