[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_result
FROM dual
WHERE EXISTS (
SELECT 1
FROM mtl_reservations
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND (demand_source_line_id <> p_demand_source_line_id
OR demand_source_line_id IS NULL)
AND lpn_id = p_lpn_id
);
SELECT 1
INTO l_result
FROM dual
WHERE EXISTS (
SELECT 1
FROM mtl_reservations
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND (demand_source_header_id <> p_demand_source_header_id
OR demand_source_header_id IS NULL)
AND lpn_id = p_lpn_id
);
SELECT 1
INTO l_result
FROM dual
WHERE EXISTS (
SELECT 1
FROM mtl_reservations
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND (demand_source_name <> p_demand_source_name
OR demand_source_name IS NULL)
AND lpn_id = p_lpn_id
);
SELECT revision_qty_control_code
, lot_control_code
, primary_uom_code
, secondary_uom_code -- INVCONV
, tracking_quantity_ind -- INVCONV
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT revision
, lot_number
, subinventory_code
, locator_id
, SUM(primary_transaction_quantity)
, SUM(secondary_transaction_quantity) -- INVCONV
FROM mtl_onhand_quantities_detail
WHERE lpn_id = p_lpn_id
GROUP BY revision, lot_number, subinventory_code, locator_id;
SELECT SUM(primary_transaction_quantity),
sum (secondary_transaction_quantity) -- 16773889
FROM mtl_onhand_quantities_detail
WHERE lpn_id = p_lpn_id;
SELECT nvl(ordered_quantity,0),order_quantity_uom
INTO l_ord_qty,l_order_qty_uom
FROM oe_order_lines_all
WHERE line_id = p_demand_source_line_id;
SELECT nvl(sum(primary_reservation_quantity),0), nvl(sum(SECONDARY_RESERVATION_QUANTITY),0) -- 16773889
INTO l_reserved_qty, l_sec_rsv_qty --16773889
FROM mtl_reservations
WHERE demand_source_line_id = p_demand_source_line_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT nvl(sum(primary_reservation_quantity),0), nvl(sum(SECONDARY_RESERVATION_QUANTITY),0) -- 16773889
INTO l_lpn_reserved_qty, l_lpn_sec_rsv_qty -- 16773889
FROM mtl_reservations
WHERE demand_source_line_id = p_demand_source_line_id
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND demand_source_line_detail IS NULL;
debug_print('Calling Update Reservation');
inv_reservation_pvt.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => l_rsv_tbl(1)
, p_to_rsv_rec => l_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
debug_print('Error from update_reservation');
debug_print('Unexpected error from update_reservation');
SELECT reservation_id, subinventory_code, locator_id, lpn_id
FROM mtl_reservations
WHERE organization_id = v_organization_id
AND (v_inventory_item_id IS NULL OR inventory_item_id = v_inventory_item_id)
AND lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = v_lpn_id OR lpn_id = v_lpn_id)
AND demand_source_type_id not in decode(p_system_task_type,3,-1,9);
SELECT msn.inventory_item_id,
msn.serial_number
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND (p_inventory_item_id IS NULL OR mr.inventory_item_id = p_inventory_item_id)
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id=p_lpn_id
AND mr.reservation_id=v_reservation_id
AND (msn.lpn_id IS NULL OR msn.lpn_id = p_lpn_id); -- lpn may be unpack...
SELECT reservable_type,lpn_controlled_flag
INTO l_reservable_type, l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
AND organization_id = p_organization_id;
debug_print('Try to use API to Update MR');
inv_reservation_pvt.update_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_original_rsv_rec => l_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_serial_number_tbl,
p_to_serial_number => l_serial_number_tbl,
p_validation_flag => fnd_api.g_false
);
debug_print ('Return Status after update reservations '||x_return_status);
debug_print(l_api_name||': Delete MR');
inv_reservation_pvt.delete_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => l_serial_number_tbl
);
SELECT reservation_id, subinventory_code, locator_id, lpn_id
FROM mtl_reservations
WHERE organization_id = v_organization_id
AND (v_inventory_item_id IS NULL OR inventory_item_id = v_inventory_item_id)
AND lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = v_lpn_id OR lpn_id = v_lpn_id)
AND (v_check_dsld_flag='FALSE' OR demand_source_line_detail IS NULL)
AND demand_source_type_id not in decode(p_system_task_type,3,-1,9);
SELECT msn.inventory_item_id,
msn.serial_number
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND (p_inventory_item_id IS NULL OR mr.inventory_item_id = p_inventory_item_id)
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id=p_lpn_id
AND mr.reservation_id=v_reservation_id
AND (msn.lpn_id IS NULL OR msn.lpn_id = p_lpn_id); -- lpn may be unpack...
SELECT reservable_type,lpn_controlled_flag
INTO l_reservable_type, l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
AND organization_id = p_organization_id;
debug_print('Try to use API to Update MR');
inv_reservation_pvt.update_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_original_rsv_rec => l_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_serial_number_tbl,
p_to_serial_number => l_serial_number_tbl,
p_validation_flag => fnd_api.g_false
);
debug_print ('Return Status after update reservations '||x_return_status);
debug_print(l_api_name||': Delete MR');
inv_reservation_pvt.delete_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => l_serial_number_tbl
);