The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
FROM GME_BATCH_STEPS
WHERE batch_id = v_batch_id
AND step_status < gme_common_pvt.g_step_completed
AND rownum = 1;
,p_delete_reservations => 'T'
,p_recursive => 'R'
,x_return_status => l_return_status);
update_step_qty_error EXCEPTION;
,p_update_inv_ind => p_batch_header_rec.update_inventory_ind
,x_exception_material_tbl => x_exception_material_tbl
,x_return_status => l_return_status);
/* Invoke the update step qty API to update the step quantities and the */
/* quantities of the succeeding steps */
l_in_batch_step_rec := x_batch_step_rec;
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => l_in_batch_step_rec
,x_message_count => l_msg_count
,x_message_list => l_msg_stack
,x_return_status => l_return_status
,x_batch_step_rec => x_batch_step_rec);
RAISE update_step_qty_error;
WHEN update_step_qty_error THEN
x_return_status := l_return_status;
This particular procedure is used to complete the step and updates actual dates for activity and resource.
Parameters
p_batch_step_rec Batch Step Line
x_batch_step_rec Batch Step Line
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
History
=============================================================================================*/
PROCEDURE complete_step_line
(p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'complete_step_line';
/* Update the Batch Step Status to WIP */
x_batch_step_rec.step_status := gme_common_pvt.g_step_completed;
IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec)) THEN
RAISE batch_step_upd_err;
x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
/* Update the actual completion dates of the activities */
UPDATE gme_batch_step_activities
SET actual_cmplt_date = x_batch_step_rec.actual_cmplt_date
WHERE batchstep_id = x_batch_step_rec.batchstep_id
AND batch_id = x_batch_step_rec.batch_id
AND actual_cmplt_date IS NULL;
/* Update the actual start dates of the activities */
UPDATE gme_batch_step_activities
SET actual_start_date = x_batch_step_rec.actual_start_date
WHERE batchstep_id = x_batch_step_rec.batchstep_id
AND batch_id = x_batch_step_rec.batch_id
AND actual_start_date IS NULL;
/* Update the actual completion dates of the resources */
UPDATE gme_batch_step_resources
SET actual_cmplt_date = x_batch_step_rec.actual_cmplt_date
WHERE batchstep_id = x_batch_step_rec.batchstep_id
AND batch_id = x_batch_step_rec.batch_id
AND actual_cmplt_date IS NULL;
/* Update the actual start dates of the resources */
UPDATE gme_batch_step_resources
SET actual_start_date = x_batch_step_rec.actual_start_date
WHERE batchstep_id = x_batch_step_rec.batchstep_id
AND batch_id = x_batch_step_rec.batch_id
AND actual_start_date IS NULL;
DELETE FROM gme_batch_step_rsrc_summary
WHERE batchstep_id = x_batch_step_rec.batchstep_id
AND batch_id = x_batch_step_rec.batch_id;
,p_update_inv_ind IN VARCHAR2
,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
,x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_step_prod_byprod(v_batchstep_id NUMBER) IS
SELECT matl.*
FROM gme_material_details matl, gme_batch_step_items item
WHERE item.batchstep_id = v_batchstep_id
AND item.material_detail_id = matl.material_detail_id
-- 12896375 - Look at all line types.
-- AND (matl.line_type IN (gme_common_pvt.g_line_type_prod, gme_common_pvt.g_line_type_byprod) OR
-- (matl.line_type = gme_common_pvt.g_line_type_ing AND matl.phantom_id IS NOT NULL))
AND matl.release_type = gme_common_pvt.g_mtl_autobystep_release;
,p_update_inv_ind => p_update_inv_ind
,x_exception_material_tbl => x_exception_material_tbl
,x_return_status => l_return_status);
SELECT d.dep_step_id, d.dep_type, d.standard_delay, s.steprelease_type,
s.step_status,s.actual_cmplt_date,s.actual_start_date
FROM gme_batch_step_dependencies d, gme_batch_steps s
WHERE d.batchstep_id = v_step_id
AND s.batchstep_id = d.dep_step_id
AND s.batch_id = v_batch_id
AND s.batch_id = d.batch_id;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
SELECT status_type
FROM gmd_status
WHERE status_code=v_validity_rule_status;
SELECT dep_step_id, dep_type, standard_delay
FROM gme_batch_step_dependencies
START WITH batchstep_id = p_batch_step_rec.batchstep_id
CONNECT BY batchstep_id = PRIOR dep_step_id;
SELECT dep_step_id, dep_type, standard_delay
FROM (SELECT * FROM gme_batch_step_dependencies WHERE batch_id = p_batch_header_rec.batch_id) d,
(SELECT * FROM gme_batch_steps WHERE batch_id = p_batch_header_rec.batch_id) s,
(SELECT * FROM gme_batch_steps WHERE batch_id = p_batch_header_rec.batch_id) p
WHERE s.batchstep_id = d.batchstep_id
AND p.batchstep_id(+) = d.dep_step_id
START WITH d.batchstep_id = p_batch_step_rec.batchstep_id
CONNECT BY d.batchstep_id = PRIOR d.dep_step_id;