[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
SELECT 'Check for empty LPN'
FROM DUAL
WHERE EXISTS
(SELECT 'Child LPN'
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE parent_lpn_id = p_lpn_id);
SELECT 'Check for empty LPN'
FROM DUAL
WHERE EXISTS
(SELECT 'Non serialized items'
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = p_lpn_id);
SELECT 'Check for empty LPN'
FROM DUAL
WHERE EXISTS
(SELECT 'Serialized items'
FROM MTL_SERIAL_NUMBERS
WHERE lpn_id = p_lpn_id);
DELETE FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_lpn_id;
/* Check if the LPN history of this should be deleted or not */
IF (p_purge_history = 1) THEN
-- If this value is other than 1, we will just assume that no
-- LPN history records shall be purged
DELETE FROM WMS_LPN_HISTORIES
WHERE lpn_id = p_lpn_id
OR parent_lpn_id = p_lpn_id;
DELETE FROM wms_lpn_histories
WHERE (lpn_id = p_lpn_id
OR parent_lpn_id = p_lpn_id)
AND (SYSDATE - creation_date >= p_del_history_days_old);
SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
revision, lot_number, serial_number, cost_group_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE Level <= p_explosion_level
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
revision, lot_number, serial_number, cost_group_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT parent_lpn_id, inventory_item_id, item_description,
organization_id, revision, lot_number,
serial_number, quantity, uom_code, cost_group_id
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_current_lpn
AND NVL(serial_summary_entry, 2) = 2;
SELECT inventory_item_id, current_organization_id, lpn_id,
revision, lot_number, serial_number, cost_group_id
FROM MTL_SERIAL_NUMBERS
WHERE lpn_id = l_current_lpn;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
AND organization_id = v_lpn_serial_content.current_organization_id;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
AND organization_id = v_lpn_serial_content.current_organization_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET parent_lpn_id = p_lpn_id_dest
WHERE parent_lpn_id = p_lpn_id_source;
UPDATE WMS_LPN_CONTENTS
SET parent_lpn_id = p_lpn_id_dest
WHERE parent_lpn_id = p_lpn_id_source;
UPDATE MTL_SERIAL_NUMBERS
SET lpn_id = p_lpn_id_dest
WHERE lpn_id = p_lpn_id_source;
SELECT max_load_quantity
FROM WSH_CONTAINER_ITEMS
WHERE master_organization_id = p_organization_id
AND container_item_id = p_dest_cont_item_id
AND load_item_id = p_source_item_id;
SELECT container_item_id, max_load_quantity, preferred_flag
FROM WSH_CONTAINER_ITEMS
WHERE master_organization_id = p_organization_id
AND load_item_id = p_source_item_id
AND container_item_id IN
(SELECT inventory_item_id
FROM MTL_SYSTEM_ITEMS
WHERE mtl_transactions_enabled_flag = 'Y'
AND container_item_flag = 'Y'
AND organization_id = p_organization_id);
/* Select the most constraining value for l_max_load_quantity */
IF (l_dest_cont_item.maximum_load_weight IS NOT NULL) THEN
-- Check that the source item's unit weight is less than or
-- equal to the destination container item's maximum load weight
IF (l_temp_value <= l_dest_cont_item.maximum_load_weight) THEN
IF (l_max_load_quantity > FLOOR (l_dest_cont_item.maximum_load_weight /
l_temp_value)) THEN
l_max_load_quantity := FLOOR (l_dest_cont_item.maximum_load_weight /
l_temp_value);
l_SelectStmt VARCHAR2(500);
SELECT *
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT *
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_current_lpn
CONNECT BY lpn_id = PRIOR parent_lpn_id;
l_SelectStmt := 'SELECT PARENT_LPN_ID FROM WMS_LPN_CONTENTS WHERE ';
l_SelectStmt := l_SelectStmt || 'inventory_item_id = ' ||
p_inventory_item_id || ' AND ';
l_SelectStmt := l_SelectStmt || 'revision = ' ||
p_revision || ' AND ';
l_SelectStmt := l_SelectStmt || 'lot_number = ' ||
p_lot_number || ' AND ';
l_SelectStmt := l_SelectStmt || 'serial_number = ' ||
p_serial_number || ' AND ';
l_SelectStmt := l_SelectStmt || '1 = 1';
DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, DBMS_SQL.V7);
IF (x_lpn_list.COUNT = 0) THEN -- Insert first initial record
x_lpn_list(l_index_2) := l_temp_table(l_index);
l_SelectStmt VARCHAR2(500);
SELECT parent_lpn_id
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_temp_lpn
AND NVL(serial_summary_entry, 2) = 2;
SELECT parent_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE parent_lpn_id = l_temp_lpn;
l_SelectStmt := 'SELECT a.lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS a, WMS_LPN_CONTENTS b WHERE ';
l_SelectStmt := l_SelectStmt || 'a.lpn_context = ' ||
p_lpn_context || ' AND ';
l_SelectStmt := l_SelectStmt || 'b.inventory_item_id = ' ||
p_content_item_id || ' AND ';
l_SelectStmt := l_SelectStmt || 'b.quantity <= ' ||
p_max_content_item_qty || ' AND ';
l_SelectStmt := l_SelectStmt || 'a.organization_id = ' ||
p_organization_id || ' AND ';
l_SelectStmt := l_SelectStmt || 'a.subinventory_code = ' ||
p_subinventory || ' AND ';
l_SelectStmt := l_SelectStmt || 'a.locator_id = ' ||
p_locator_id || ' AND ';
l_SelectStmt := l_SelectStmt || 'b.revision = ' ||
p_revision || ' AND ';
l_SelectStmt := l_SelectStmt || 'b.lot_number = ' ||
p_lot_number || ' AND ';
l_SelectStmt := l_SelectStmt || 'b.serial_number = ' ||
p_serial_number || ' AND ';
l_SelectStmt := l_SelectStmt || 'a.inventory_item_id = ' ||
p_container_item_id || ' AND ';
l_SelectStmt := l_SelectStmt || 'a.lpn_id = b.parent_lpn_id';
DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, DBMS_SQL.V7);
SELECT *
INTO l_temp_record
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_temp_lpn;
SELECT lpn_id,
lpn_context,
subinventory_code,
locator_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_drop_lpn
AND organization_id = p_organization_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp temp
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = temp.move_order_line_id
AND wdd.organization_id = temp.organization_id
AND temp.transfer_lpn_id = p_pick_lpn_id
AND temp.organization_id = p_organization_id ;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wms_license_plate_numbers lpn
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = lpn.lpn_id
AND lpn.outermost_lpn_id = l_lpn_id
AND wdd.organization_id = p_organization_id ;
/* UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
WHERE lpn_id = drop_lpn_rec.lpn_id;*/
SELECT lpn_id,
lpn_context
FROM wms_license_plate_numbers
WHERE license_plate_number = p_drop_lpn
AND organization_id = p_organization_id;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT 'x'
FROM dual
WHERE EXISTS (
SELECT 'x'
FROM wsh_delivery_details
WHERE lpn_id = l_lpn_id
AND organization_id = p_organization_id
);
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp temp
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = temp.move_order_line_id
AND wdd.organization_id = temp.organization_id
AND temp.transfer_lpn_id = p_pick_lpn_id
AND temp.organization_id = p_organization_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = l_lpn_id
AND wdd.organization_id = p_organization_id;
UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
WHERE lpn_id = drop_lpn_rec.lpn_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp temp
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = temp.move_order_line_id
AND wdd.organization_id = temp.organization_id
AND temp.transfer_lpn_id = p_pick_lpn_id
AND temp.organization_id = p_organization_id;
SELECT wlpn.outermost_lpn_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn
WHERE wda.delivery_id = l_delivery_id_c
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = p_organization_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.subinventory_code = l_drop_sub_c
AND wlpn.locator_id = l_drop_loc_c
AND wlpn.lpn_context = 11
ORDER BY wda.CREATION_DATE DESC ;
Select transfer_subinventory, transfer_to_location into l_drop_sub,
l_drop_loc
from mtl_material_transactions_temp
where transfer_lpn_id = p_pick_lpn_id
AND organization_id = p_organization_id;
SELECT license_plate_number INTO x_lpn_number FROM
wms_license_plate_numbers WHERE lpn_id = l_lpn_id;