The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select distinct organization_id
From mtl_reservations
Where supply_source_line_id = p_batch_line_id
and supply_source_type_id = 5
-- and demand_source_type_id = inv_reservation_global.g_source_type_oe
and demand_source_type_id IN (inv_reservation_global.g_source_type_oe, gme_common_pvt.g_txn_source_type) -- Bug 13630492
and reservation_quantity<>0
;
Select material_detail_id
, inventory_item_id
, plan_qty
From gme_material_details
where batch_id = p_batch_id
and line_type <> -1 -- not ingredient
;
Select *
From mtl_reservations
Where supply_source_line_id = p_batch_line_id
and supply_source_type_id = 5
and demand_source_type_id = inv_reservation_global.g_source_type_oe
and organization_id = p_organization_id
and reservation_quantity <> 0
Order by requirement_date
;
Select count(1)
From mtl_reservations
Where supply_source_line_id = p_batch_line_id
--and organization_id = p_organization_id
and supply_source_type_id = 5
and reservation_quantity <> 0
;
Select material_detail_id
, plan_qty
From gme_material_details
Where batch_id = p_batch_id
and inventory_item_id = p_item_id
and line_type <> -1
;
Select plan_cmplt_date
From gme_batch_header
where batch_id = p_batch_id;
l_last_updated_by NUMBER;
SELECT Distinct orig_demand_source_line_id
FROM mtl_reservations
WHERE supply_source_header_id = p_batch_id
and supply_source_type_id = 5
and demand_source_type_id = 2
and reservation_quantity <> 0;
SELECT batch_type
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT Distinct orig_demand_source_line_id
FROM mtl_reservations
WHERE supply_source_line_id = p_batch_line_id
and supply_source_type_id = 5
and demand_source_type_id = 2
and reservation_quantity <> 0;
SELECT gl.batch_id,gh.batch_type
FROM gme_material_details gl,
gme_batch_header gh
WHERE gl.material_detail_id = p_batch_line_id
and gl.batch_id = gh.batch_id;
SELECT last_updated_by, created_by,header_id
FROM oe_order_lines_all
WHERE line_id = p_so_line_id;
Select distinct orig_demand_source_line_id
From mtl_reservations
Where created_by = p_user_id
and orig_supply_source_header_id = p_batch_id
and demand_source_type_id = 2
and orig_supply_source_type_id = 5;
Select distinct orig_demand_source_line_id
From mtl_reservations
Where created_by = p_user_id
and orig_supply_source_header_id = p_batch_id
and orig_supply_source_line_id = p_batch_line_id
and demand_source_type_id = 2
and orig_supply_source_type_id = 5;
SELECT DISTINCT orig_demand_source_line_id
FROM mtl_reservations mr, gme_batch_header gbh
WHERE mr.created_by = p_user_id
AND mr.orig_supply_source_header_id = p_batch_id
AND mr.orig_supply_source_header_id = gbh.batch_id
AND mr.organization_id = gbh.organization_id
AND mr.demand_source_type_id = 2
AND mr.orig_supply_source_type_id = 5;
SELECT DISTINCT orig_demand_source_line_id
FROM mtl_reservations mr, gme_material_details gmd
WHERE mr.created_by = p_user_id
AND mr.orig_supply_source_header_id = p_batch_id
AND mr.orig_supply_source_line_id = p_batch_line_id
AND mr.inventory_item_id = gmd.inventory_item_id
AND mr.organization_id = gmd.organization_id
AND gmd.batch_id = mr.orig_supply_source_header_id
AND gmd.material_detail_id = mr.orig_supply_source_line_id
AND mr.demand_source_type_id = 2
AND mr.orig_supply_source_type_id = 5;
SELECT lot_number
FROM gme_pending_product_lots
WHERE batch_id = p_batch_id and material_detail_id = p_batch_line_id;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_last_updated_by is '||l_last_updated_by);
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_fpo_id => p_fpo_id
, p_organization_id => l_organization_id
, p_action_code => l_action_code );
wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
IF(l_last_updated_by <> l_created_by) THEN
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
for mul_line in check_mul_line_id1 (l_last_updated_by, l_batch_id ) Loop
l_old_header_id := l_so_header_id ;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_fpo_id => NULL
, p_organization_id => l_organization_id
, p_action_code => l_action_code );
wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
IF(l_last_updated_by <> l_created_by) THEN
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
for mul_line in check_mul_line_id2 (l_last_updated_by, l_batch_id, l_batch_line_id ) Loop
l_old_header_id := l_so_header_id ;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_fpo_id => NULL
, p_organization_id => l_organization_id
, p_action_code => l_action_code );
wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
IF(l_last_updated_by <> l_created_by) THEN
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
l_parameter_list.DELETE;
update_reservation_err EXCEPTION;
update mtl_reservations
set reservation_quantity = l_txn_quantity,
primary_reservation_quantity = l_txn_primary,
secondary_reservation_quantity = l_txn_secondary
where reservation_id = l_rsv_rec.reservation_id;
RAISE update_reservation_err;
WHEN update_reservation_err THEN
IF (g_debug <= gme_debug.g_log_error) THEN
gme_debug.put_line
( 'terminating due to error in inv_reservation_pub.update_reservation ');
update_reservation_err EXCEPTION;
delete_reservation_err EXCEPTION;
|| ' about to invoke delete_reservation ');
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, 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_serial_number => l_dummy_sn
);
|| ' after calling delete_reservation status is '
|| x_return_status );
RAISE delete_reservation_err;
RAISE update_reservation_err;
WHEN delete_reservation_err THEN
IF (g_debug <= gme_debug.g_log_error) THEN
gme_debug.put_line
( 'inv_reservation_pub.delete_reservation failure ');
WHEN update_reservation_err THEN
IF (g_debug <= gme_debug.g_log_error) THEN
gme_debug.put_line
( 'inv_reservation_pub.relieve_reservation failure ');
( 'inv_reservation_pub.update_reservation failure ');
PROCEDURE delete_prod_supply_resv (
p_matl_dtl_rec IN gme_material_details%ROWTYPE
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_prod_supply_resv';
update_reservation_err EXCEPTION;
delete_reservation_err EXCEPTION;
|| ' about to invoke delete_reservation for reservation id '
|| l_rsv_rec.reservation_id);
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, 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_serial_number => l_dummy_sn
);
|| ' after calling delete_reservation status is '
|| x_return_status );
RAISE delete_reservation_err;
WHEN delete_reservation_err THEN
IF (g_debug <= gme_debug.g_log_error) THEN
gme_debug.put_line
( 'inv_reservation_pub.delete_reservation failure ');
END delete_prod_supply_resv;
PROCEDURE delete_batch_prod_supply_resv (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_prod_supply_resv';
update_reservation_err EXCEPTION;
delete_reservation_err EXCEPTION;
|| ' about to invoke delete_reservation for reservation id '
|| l_rsv_rec.reservation_id);
inv_reservation_pub.delete_reservation
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, 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_serial_number => l_dummy_sn
);
|| ' after calling delete_reservation status is '
|| x_return_status );
RAISE delete_reservation_err;
WHEN delete_reservation_err THEN
IF (g_debug <= gme_debug.g_log_error) THEN
gme_debug.put_line
( 'inv_reservation_pub.delete_reservation failure ');
END delete_batch_prod_supply_resv;