[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
and if it is not assciated then added a select statement to retrieve the planed start date of the
parent batch into l_phantom_batch_header_rec.plan_cmplt_date
Susruth Bug#5359091 Finite Scheduled indicator is set back to 0 once the batch is rerouted.
Sunitha Ch. bug 5353941 REWORK Check the release type of material also alog
with association to step.If release type is automatic then call rescedule batch of the phantom batch
Sunitha ch Bug#5391396 included the code to rescedule_step of the child batch when the product
is associated to step and its release type is autobystep.
=============================================================================================*/
PROCEDURE reroute_batch (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_validity_rule_id IN NUMBER
,p_use_workday_cal IN VARCHAR2
DEFAULT fnd_api.g_false
,p_contiguity_override IN VARCHAR2
DEFAULT fnd_api.g_false
,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'REROUTE_BATCH';
SELECT r.recipe_id, r.routing_id, calculate_step_quantity,v.inventory_item_id
FROM gmd_recipe_validity_rules v, gmd_recipes r
WHERE recipe_validity_rule_id = v_validity_rule_id
AND r.recipe_id = v.recipe_id;
SELECT MAX (plan_cmplt_date)
FROM gme_batch_steps
WHERE batch_id = p_batch_header_rec.batch_id;
SELECT material_detail_id, phantom_id
FROM gme_material_details
WHERE batch_id = v_batch_id;
SELECT plan_qty, dtl_um
FROM gme_material_details
WHERE batch_id = v_batch_id
AND inventory_item_id = v_inventory_item_id
AND line_type = gme_common_pvt.g_line_type_prod
ORDER BY line_no ASC;
/* Now we have to delete the existing poc data */
gme_reroute_batch_pvt.delete_all_steps (x_batch_header_rec.batch_id
,x_return_status);
x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
SELECT primary_uom_code
INTO l_prim_item_um
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = x_batch_header_rec.organization_id;
if it is not assciated then added a select statement to retrieve the planed start date of the parent batch into l_phantom_batch_header_rec.plan_cmplt_date*/
IF ( l_phantom_ids_tab (i) IS NOT NULL ) THEN
/*Sunitha ch Bug#5391396 included the code to rescedule_step of the child batch when the product
is associated to step and its release type is autobystep.*/
IF NOT gme_material_details_dbl.fetch_row(l_material_detail_rec, l_material_detail_rec) THEN
l_proc := 'gme_material_details_dbl.fetch_row';
SELECT batchstep_id INTO l_phantom_batch_step_rec.batchstep_id
FROM gme_batch_step_items
WHERE batch_id = l_phantom_ids_tab (i)
AND material_detail_id = l_material_detail_rec.phantom_line_id;
R: When reservations are deleted for a material line
M: When MO Allocations are deleted for a material line
B: When Both reservations and material lines are deleted for a material line
*/
IF x_return_status = 'R' THEN
l_R_count := l_R_count + 1;
/* If any of the reservations are MO allocations deleted then respective message
will be put on the stack but return status will be 'S' to form or API */
IF (l_B_count > 0) OR (l_R_count > 0 AND l_M_count > 0) THEN
--atleast for one material line MO allocations and reservations are deleted
gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
IF NOT gme_batch_header_dbl.update_row (x_batch_header_rec) THEN
RAISE fnd_api.g_exc_error;
delete_all_steps
Description
This particular procedure is used to delete all the steps in the batch.
Parameters
p_batch_id Batch ID
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE delete_all_steps (
p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ALL_STEPS';
DELETE FROM gme_process_parameters
WHERE batch_id = p_batch_id;
DELETE FROM gme_resource_txns
WHERE doc_id = p_batch_id;
DELETE FROM gme_resource_txns_gtmp
WHERE doc_id = p_batch_id;
DELETE FROM gme_batch_step_resources
WHERE batch_id = p_batch_id;
DELETE FROM gme_batch_step_items
WHERE batch_id = p_batch_id;
DELETE FROM gme_batch_step_dependencies
WHERE batch_id = p_batch_id;
DELETE FROM gme_batch_step_charges
WHERE batch_id = p_batch_id;
DELETE FROM gme_batch_step_activities
WHERE batch_id = p_batch_id;
DELETE FROM gme_batch_steps
WHERE batch_id = p_batch_id;
END delete_all_steps;
SELECT gr.formula_id, grvr.inventory_item_id
FROM gmd_recipe_validity_rules grvr, gmd_recipes gr
WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
AND gr.recipe_id = grvr.recipe_id;
SELECT plan_qty, dtl_um
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = 1
AND inventory_item_id = v_inventory_item_id;
SELECT COUNT (1)
FROM gmd_recipe_validity_rules v
WHERE recipe_validity_rule_id = v_validity_rule_id
AND ( (validity_rule_status BETWEEN 700 AND 799)
OR (validity_rule_status BETWEEN 900 AND 999)
OR ( v_laboratory_ind = 1
AND validity_rule_status BETWEEN 400 AND 699) )
AND ( (v_batch_type = 0 AND recipe_use = 0)
OR (v_batch_type = 10 AND recipe_use IN (0, 1) ) )
AND delete_mark = 0
AND inv_min_qty <= v_qty
AND inv_max_qty >= v_qty;
SELECT d.inventory_item_id, d.organization_id, d.plan_qty
,d.dtl_um line_um, msib.primary_uom_code item_um
FROM gme_material_details d
,mtl_system_items_b msib
,gmd_recipe_validity_rules v
WHERE d.batch_id = v_batch_id
AND d.line_type = 1
AND msib.inventory_item_id = d.inventory_item_id
AND msib.organization_id = d.organization_id
AND v.recipe_validity_rule_id = v_validity_rule_id
AND d.inventory_item_id = v.inventory_item_id;