[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_delete_batch_step_pvt';
delete_step
Description
This particular procedure is used to delete the step and its details in a batch.
Parameters
p_api_version For version specific processing - Default 1
p_validation_level Errors to skip before returning - Default 100
p_init_msg_list Signals wether the message stack should be initialised
p_commit Indicator to commit the changes made
p_batch_step The batch step record.
x_message_count The number of messages in the message stack
x_message_list message stack where the api writes its messages
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE delete_step (
p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_reroute_flag IN BOOLEAN := FALSE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_step';
SELECT COUNT (1)
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id <> v_batchstep_id;
SELECT batchstep_id, dep_step_id
FROM gme_batch_step_dependencies
WHERE (batch_id = l_batch_header.batch_id)
AND (batchstep_id = v_batchstep_id OR dep_step_id = v_batchstep_id);
SELECT material_detail_id
FROM gme_batch_step_items
WHERE batchstep_id = v_batchstep_id;
step_activity_delete_error EXCEPTION;
step_dependency_delete_error EXCEPTION;
step_item_delete_error EXCEPTION;
step_delete_error EXCEPTION;
SAVEPOINT delete_batch_step;
/* Don't allow the Batch Step to be deleted if the Batch Status */
/* is Cancelled or Closed or Certified or WIP */
IF (l_batch_step.step_status <> 1) THEN
RAISE invalid_step;
/* Don't allow all the steps in the batch to be deleted */
/* Load all the transactions and resources to the temporary table */
/* for the current batch if the update inventory ind is set for the batch */
IF l_batch_header.update_inventory_ind = 'Y' THEN
gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_header
,x_rsc_row_count => l_rsrc_trans_count
,x_return_status => l_return_status);
END IF; /* IF x_batch_header.update_inventory_ind = 'Y' */
/* Delete all the activities attached to the step and the subsequent child nodes */
IF NOT p_reroute_flag THEN
OPEN cur_get_count (l_batch_step.batch_id
,l_batch_step.batchstep_id);
UPDATE gme_resource_txns_gtmp
SET action_code = 'DEL'
WHERE line_id IN (
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = l_batch_step.batch_id
AND batchstep_id = l_batch_step.batchstep_id)
AND action_code NOT IN ('REVS', 'REVL', 'DEL')
AND delete_mark <> 1;
DELETE gme_process_parameters
WHERE batch_id = l_batch_step.batch_id
AND batchstep_id = l_batch_step.batchstep_id;
DELETE gme_batch_step_resources
WHERE batch_id = l_batch_step.batch_id
AND batchstep_id = l_batch_step.batchstep_id;
DELETE gme_batch_step_activities
WHERE batch_id = l_batch_step.batch_id
AND batchstep_id = l_batch_step.batchstep_id;
DELETE gme_batch_step_charges
WHERE batch_id = l_batch_step.batch_id
AND batchstep_id = l_batch_step.batchstep_id;
IF NOT (gme_batch_step_depend_dbl.delete_row (l_batchstep_dependency) ) THEN
RAISE step_dependency_delete_error;
IF NOT (gme_batch_step_items_dbl.delete_row (l_batchstep_items) ) THEN
RAISE step_item_delete_error;
IF NOT (gme_batch_steps_dbl.delete_row (l_batch_step) ) THEN
RAISE step_delete_error;
UPDATE gme_batch_steps
SET plan_step_qty = l_step_tbl (i).step_qty
,plan_mass_qty = l_step_tbl (i).step_mass_qty
,mass_ref_um = l_step_tbl (i).step_mass_uom
,plan_volume_qty = l_step_tbl (i).step_vol_qty
,volume_ref_um = l_step_tbl (i).step_vol_uom
WHERE batch_id = l_batch_header.batch_id
AND batchstep_no = l_step_tbl (i).step_no;
ROLLBACK TO SAVEPOINT delete_batch_step;
ROLLBACK TO SAVEPOINT delete_batch_step;
WHEN step_activity_delete_error OR error_load_trans OR batch_step_fetch_error OR batch_header_fetch_error OR step_dependency_delete_error OR step_item_delete_error OR step_delete_error THEN
x_return_status := fnd_api.g_ret_sts_error;
ROLLBACK TO SAVEPOINT delete_batch_step;
END delete_step;
delete_activity
Description
This particular procedure is used to delete the activity and its details in a batch.
Parameters
p_batch_step_activities_rec The batch step activity record.
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE delete_activity (
p_batch_step_activities_rec IN gme_batch_step_activities%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_activity';
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT batch_id
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
activity_resource_delete_error EXCEPTION;
step_activity_delete_error EXCEPTION;
/* Delete all the resources associated with the activity */
delete_resource (l_gme_batchstep_resources, l_return_status);
RAISE activity_resource_delete_error;
GMF_VIB.Update_Batch_Requirements
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_batch_id => l_batch_id,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_list);
IF NOT (gme_batch_step_activities_dbl.delete_row
(p_batch_step_activities_rec) ) THEN
RAISE step_activity_delete_error;
WHEN step_activity_delete_error OR activity_resource_delete_error THEN
x_return_status := fnd_api.g_ret_sts_error;
END delete_activity;
delete_resource
Description
This particular procedure is used to delete the resource and its details in a batch.
Parameters
p_batch_step_resources_rec The batch step resources record.
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
Pawan kumar Added the process parameters deletion procedure for bug 2509572
Rishi Varma bug 3307549 18-05-2004
Added code to remove charges associated with a chargeable resource.
Rishi Varma 3682311 11-06-2004
Removed the explicit commit after the clear_charge procedure.
Rishi Varma 3718176 20-07-2004
Added code to remove the charges assocaited with a chargeable resource only if its the
last instance of the resource present in the step.
=============================================================================================*/
PROCEDURE delete_resource (
p_batch_step_resources_rec gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
/* 2841929: Added parameters to the cursor and passing proper parameters */
CURSOR cur_get_update_inventory (v_batch_id IN NUMBER)
IS
SELECT update_inventory_ind
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM gme_batch_step_charges
WHERE resources = v_resources
AND batchstep_id = v_batchstep_id);
resource_charge_delete_error EXCEPTION;
SELECT COUNT (1)
FROM gme_batch_step_resources
WHERE resources = v_resources AND batchstep_id = v_batchstep_id;
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_resource';
l_update_inventory_ind VARCHAR2 (1);
trans_delete_error EXCEPTION;
activity_resource_delete_error EXCEPTION;
rsrc_param_delete_error EXCEPTION;
RAISE activity_resource_delete_error;
/* First delete all the process parameters associated with the resource */
l_rsrc_parameters.batchstep_resource_id :=
p_batch_step_resources_rec.batchstep_resource_id;
this resouces in the step which is being deleted*/
OPEN cur_check_last_rsrc_instance
(l_batch_step_resource.resources
,l_batch_step_resource.batchstep_id);
RAISE resource_charge_delete_error;
IF NOT (gme_process_parameters_dbl.delete_all
(p_process_parameters => l_rsrc_parameters) ) THEN
RAISE rsrc_param_delete_error;
OPEN cur_get_update_inventory (l_batch_step_resource.batch_id);
FETCH cur_get_update_inventory
INTO l_update_inventory_ind;
CLOSE cur_get_update_inventory;
IF l_update_inventory_ind = 'Y' THEN
/* First delete all the resources transactions associated with the resource */
gme_delete_batch_step_pvt.delete_resource_transactions
(p_batch_step_resources_rec => p_batch_step_resources_rec
,x_return_status => l_return_status);
RAISE trans_delete_error;
IF NOT (gme_batch_step_resources_dbl.delete_row
(p_batch_step_resources_rec) ) THEN
RAISE activity_resource_delete_error;
WHEN trans_delete_error OR activity_resource_delete_error OR rsrc_param_delete_error OR resource_charge_delete_error THEN
x_return_status := fnd_api.g_ret_sts_error;
END delete_resource;
delete_resource_transactions
Description
This particular procedure is used to delete the resource transactions in a batch.
Parameters
p_batch_step_resources_rec The batch step resources record.
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE delete_resource_transactions (
p_batch_step_resources_rec gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
l_api_name CONSTANT VARCHAR2 (30) := 'delete_resource_transactions';
gme_resource_engine_pvt.delete_resource_trans
(p_tran_rec => l_resource_txns
,x_return_status => l_return_status);
END delete_resource_transactions;