[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mr.reservation_id
,TO_CHAR (mr.requirement_date, l_date_format)
,mr.primary_uom_code, mr.reservation_uom_code
,NVL (mr.reservation_quantity, 0)
,NVL (mr.primary_reservation_quantity, 0)
,mr.subinventory_code, mr.subinventory_id, mr.locator_id
,mr.lot_number, mr.lot_number_id
,NVL (mr.detailed_quantity, 0)
,NVL (mr.secondary_detailed_quantity, 0)
,NVL (mr.secondary_reservation_quantity, 0)
,mr.secondary_uom_code, mr.inventory_item_id
,loc.concatenated_segments
FROM mtl_reservations mr, wms_item_locations_kfv loc
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_material_detail_id
AND NVL (mr.subinventory_code, '1') =
NVL (p_subinventory_code, '1')
AND NVL (mr.locator_id, -1) = NVL (p_locator_id, -1)
AND mr.organization_id = loc.organization_id(+)
AND mr.subinventory_code = loc.subinventory_code(+)
AND mr.locator_id = loc.inventory_location_id(+)
AND (p_lot_number IS NULL OR mr.lot_number = p_lot_number)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date;
SELECT d.*
FROM gme_material_details d, mtl_system_items i
WHERE d.batch_id = p_batch_id
AND d.line_type = -1
AND d.line_type = -1
AND ( NVL (p_timefence, 0) = 0
OR (d.material_requirement_date < SYSDATE + p_timefence) )
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.reservable_type = 1
AND d.phantom_type = 0
AND (i.lot_control_code < 2
OR i.lot_control_code > 1 AND i.lot_divisible_flag = 'Y')
ORDER BY d.line_no;
SELECT mr.*
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date, mr.reservation_id; -- nsinghi bug#5176319. Add mr.reservation_id in order by clause.
PROCEDURE delete_batch_reservations (
p_organization_id IN NUMBER
,p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_batch_reservations';
delete_resvn_error EXCEPTION;
SELECT d.material_detail_id, d.batch_id, d.organization_id
FROM gme_material_details d, mtl_system_items_b i
WHERE d.organization_id = p_organization_id
AND d.batch_id = p_batch_id
AND d.line_type = gme_common_pvt.g_line_type_ing
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id
AND i.reservable_type = 1;
gme_reservations_pvt.delete_material_reservations
(p_organization_id => get_rec.organization_id
,p_batch_id => get_rec.batch_id
,p_material_detail_id => get_rec.material_detail_id
,x_return_status => l_return_status);
RAISE delete_resvn_error;
WHEN delete_resvn_error THEN
x_return_status := l_return_status;
END delete_batch_reservations;
PROCEDURE delete_material_reservations (
p_organization_id IN NUMBER
,p_batch_id IN NUMBER
,p_material_detail_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_reservations';
gme_reservations_pvt.delete_reservation
(p_reservation_id => l_rsv_tbl (i).reservation_id
,x_return_status => l_return_status);
END delete_material_reservations;
PROCEDURE delete_reservation (
p_reservation_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_reservation';
gme_debug.put_line ('Calling inv_reservation_pub.delete_reservation');
inv_reservation_pub.delete_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_rsv_rec => l_rsv_rec
,p_serial_number => l_serial_number);
( 'inv_reservation_pub.delete_reservation returns '
|| l_return_status);
END delete_reservation;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT mr.*
FROM mtl_reservations mr
WHERE mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = l_mtl_dtl_rec.batch_id
AND mr.demand_source_line_id = l_mtl_dtl_rec.material_detail_id
AND ( (p_supply_sub_only = fnd_api.g_false)
OR (mr.subinventory_code = l_mtl_dtl_rec.subinventory) )
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id);
/* Pass only values needed to be updated reservation ID is required */
PROCEDURE update_reservation (
p_reservation_id IN NUMBER
,p_revision IN VARCHAR2 DEFAULT NULL
,p_subinventory IN VARCHAR2 DEFAULT NULL
,p_locator_id IN NUMBER DEFAULT NULL
,p_lot_number IN VARCHAR2 DEFAULT NULL
,p_new_qty IN NUMBER DEFAULT NULL
,p_new_sec_qty IN NUMBER DEFAULT NULL
,p_new_uom IN VARCHAR2 DEFAULT NULL
,p_new_date IN DATE DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_reservation';
update_resvn_error EXCEPTION;
|| ':Calling inv_reservation_pub.update_reservation with reservation_id = '
|| p_reservation_id);
inv_reservation_pub.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_orig_rsv_rec
,p_to_rsv_rec => l_rsv_rec
,p_original_serial_number => l_serial_number
,p_to_serial_number => l_serial_number
,p_validation_flag => fnd_api.g_true
,p_check_availability => fnd_api.g_true);
|| 'Return status from inv_reservation_pub.update_reservation is '
|| l_return_status);
RAISE update_resvn_error;
WHEN update_resvn_error THEN
x_return_status := l_return_status;
END update_reservation;
update_resvn_error EXCEPTION;
RAISE update_resvn_error;
WHEN update_resvn_error THEN
x_return_status := l_return_status;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id =
gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND NOT EXISTS (
SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id =
mr.reservation_id) );
SELECT *
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
update_mo_fail EXCEPTION;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT *
FROM mtl_txn_request_lines
WHERE line_id = v_move_order_line_id;
/* Update the Move Order */
gme_move_orders_pvt.update_move_order_lines
(p_batch_id => p_material_details_rec.batch_id
,p_material_detail_id => p_material_details_rec.material_detail_id
,p_new_qty => l_open_qty
,p_new_date => NULL
,p_invis_move_line_id => p_material_details_rec.move_order_line_id
,x_return_status => x_return_status);
|| ' Return from update_move_order_lines is '
|| x_return_status);
RAISE update_mo_fail;
l_trolin_rec_type.last_updated_by := l_trolin_rec.last_updated_by;
l_trolin_rec_type.last_update_date := l_trolin_rec.last_update_date;
l_trolin_rec_type.last_update_login := l_trolin_rec.last_update_login;
l_trolin_rec_type.program_update_date := l_trolin_rec.program_update_date;
relieve_res_error OR open_qty_error OR update_mo_fail OR
create_suggestions_err OR error_unexpected OR create_reservation_err THEN
IF g_debug <= gme_debug.g_log_unexpected THEN
gme_debug.put_line ( 'Exiting due to error exception in '
|| g_pkg_name
|| '.'
|| l_api_name);
SELECT d.*
FROM gme_material_details d,mtl_system_items_b i
WHERE d.batch_id = p_batch_rec.batch_id
AND d.line_type = -1
AND d.material_requirement_date < SYSDATE + NVL(gme_common_pvt.g_rule_based_resv_horizon,p_timefence)
AND i. inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.reservable_type = 1
ORDER BY d.line_no;