[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# delete_recipe_dependencies
# SYNOPSIS
# delete_recipe_dependencies
# DESCRIPTION
# Deletes Recipe Dependencies when either its formula or Recipe
# information is changed.
###############################################################*/
PROCEDURE delete_recipe_dependencies(precipe_id NUMBER,
update_item VARCHAR2) IS
l_api_name VARCHAR2(100) := 'DELETE_RECIPE_DEPENDENCIES';
IF (update_item = 'FORMULA') THEN
/* Bug 3037410 Appended the where clause to check for the
validity rule's Product */
DELETE FROM gmd_recipe_validity_rules
WHERE recipe_id = precipe_id AND
inventory_item_id NOT IN (SELECT inventory_item_id FROM fm_matl_dtl
WHERE formula_id = (SELECT formula_id FROM gmd_recipes_b
WHERE recipe_id = precipe_id)
AND line_type = 1);
fnd_message.set_name('GMD', 'GMD_DELETE_RECIPE_FM_DEP');
ELSIF(update_item = 'ROUTING') THEN
DELETE FROM gmd_recipe_routing_steps
WHERE recipe_id = precipe_id;
DELETE FROM gmd_recipe_orgn_activities
WHERE recipe_id = precipe_id;
DELETE FROM gmd_recipe_orgn_resources
WHERE recipe_id = precipe_id;
fnd_message.set_name('GMD', 'GMD_DELETE_RECIPE_RT_DEP');
DELETE FROM gmd_recipe_step_materials
WHERE recipe_id = precipe_id;
fnd_message.set_name('GMD', 'GMD_DELETE_STEP_MAT_ASSOC');
END delete_recipe_dependencies;
l_object_select_ind NUMBER;
DELETE FROM gmd_msnr_results
WHERE concurrent_id IS NULL;
dbms_sql.define_column(l_cursor_id, 6, l_object_select_ind );
dbms_sql.column_value(l_cursor_id, 6, l_object_select_ind );
l_object_select_ind := 0;
X_search_tbl(l_row_cnt).object_select_ind := l_object_select_ind ;
INSERT INTO gmd_msnr_results
( concurrent_id
,object_id
,object_name
,object_vers
,object_desc
,object_status_code
,object_status_desc
,object_select_ind
)
VALUES
( Null
,l_object_id
,l_object_name
,l_object_vers
,l_object_desc
,l_object_status_code
,l_object_status_desc
,l_object_select_ind
);
p_rout_update_table GMD_ROUTINGS_PUB.update_tbl_type;
p_oprn_update_table GMD_OPERATIONS_PUB.update_tbl_type;
p_oprn_activity_table GMD_OPERATION_ACTIVITIES_PUB.update_tbl_type;
p_oprn_resources_table GMD_OPERATION_RESOURCES_PUB.update_tbl_type;
p_validity_rules_table GMD_VALIDITY_RULES_PVT.update_tbl_type;
SElECT Upper(pObject_type) Object_type -- e.g 'FORMULA', 'RECIPE' etc
,Upper(pReplace_type) Replace_type -- e.g 'FORMULA_CLASS'
,pOld_Name Old_Name -- e.g 'SHY-TEST-FMCLS'
,pNew_Name New_Name -- e.g 'TDAN-TEST-FMCLS'
,pOld_Version Old_Version -- Applicable only for formula
,pNew_Version New_version -- Routing and Operation
,pScale_factor Scale_factor -- defaults to 1
,pVersion_flag Version_flag -- defaults to 'N'
,object_id -- e.g formula_id = 100
,object_name -- e.g formula_no = 'SHY-TEST'
,object_vers -- e.g formula_vers = 2
,object_desc
,object_status_code -- e.g formula_status = '100'
,concurrent_id
FROM gmd_msnr_results
WHERE object_select_ind = 1 AND
concurrent_id = pConcurrent_id;
SELECT version_enabled
FROM gmd_status_b
WHERE status_type = vStatus;
SELECT formula_id
FROM gmd_recipes_b
WHERE recipe_id = v_recp_id;
SELECT r.formula_id
FROM gmd_recipes_b r, gmd_recipe_validity_rules vr
WHERE vr.recipe_validity_rule_id = v_vr_id
AND r.recipe_id = vr.recipe_id;
SELECT recipe_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = V_rcp_vldty_rule_id;
SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = V_item_id
AND recipe_enabled_flag = 'Y'
AND organization_id = (SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = V_form_id);
select REVISION_QTY_CONTROL_CODE
from mtl_system_items_b
where inventory_item_id = v_item_id;
select 1
from mtl_item_revisions
where inventory_item_id = v_item_id
and organization_id =(SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = V_form_id)
and REVISION = pNew_Version ;
SELECT owner_organization_id
FROM gmd_recipes_b
WHERE recipe_id = V_recipe_id;
SELECT organization_id
FROM gmd_recipe_process_loss
WHERE recipe_id = V_recipe_id;
SELECT substitution_id
FROM gmd_item_substitution_hdr_b
WHERE original_inventory_item_id = vOriginal_item_id
AND owner_organization_id = (SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = V_form_id) ;
SELECT COUNT(i.inventory_item_id)
FROM fm_form_mst f, mtl_system_items i
WHERE f.formula_id = V_form_id
AND i.organization_id = f.owner_organization_id
AND i.inventory_item_id = V_item_id
AND i.eng_item_flag = 'Y';
NO_UPDATE_EXCEPTION EXCEPTION;
SELECT meaning
FROM Gem_lookups
WHERE lookup_type = 'GMD_FORMULA_USE'
AND lookup_code = vRecipe_use;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = vItem_id;
SELECT user_name
FROM fnd_user
WHERE user_id = vOwner_id;
SELECT meaning
FROM gmd_status
WHERE status_code = P_status_code;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_orgn_id;
SELECT meaning
FROM Gem_lookups
WHERE lookup_type = 'GMD_RECIPE_TYPE'
AND lookup_code = vRecipe_type;
RAISE NO_UPDATE_EXCEPTION;
RAISE No_Update_Exception;
SELECT fm.owner_organization_id
INTO l_orgn_id
FROM fm_form_mst_b fm
WHERE fm.formula_id = get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
SELECT DECODE(get_object_rec.replace_type,'FORMULA_CLASS'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'INGREDIENT'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'INGREDIENT'
,get_object_rec.old_name
,NULL),
DECODE(get_object_rec.replace_type,'OWNER_ID'
,get_object_rec.new_name
,NULL)
INTO l_formula_class, l_new_ingredient, l_old_ingredient, l_owner_id
FROM dual;
('In MSNR replace : In Insert mode '
||' l_fm_class = '||l_formula_class
||' l_new_ingredient = '||l_new_ingredient
||' l_old_ingredient = '||l_old_ingredient
||' l_owner_id = '||l_owner_id);
RAISE No_Update_Exception;
SELECT formula_vers
INTO l_object_version
FROM fm_form_mst_b
WHERE formula_id = l_formula_id;
UPDATE fm_form_mst_b
SET formula_class = DECODE(get_object_rec.replace_type,'FORMULA_CLASS'
,get_object_rec.new_name
,formula_class),
owner_id = DECODE(get_object_rec.replace_type,'OWNER_ID'
,get_object_rec.new_name
,owner_id),
last_update_date = P_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE formula_id = get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
UPDATE fm_matl_dtl
SET inventory_item_id = get_object_rec.new_name,
revision = get_object_rec.new_version,
qty = qty * l_scale_factor,
ingredient_end_date = Null, --bug 4479101
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE formula_id = get_object_rec.object_id
AND line_type = -1
AND inventory_item_id = get_object_rec.old_name
AND NVL(revision, -1) = NVL(get_object_rec.old_version, -1);
RAISE NO_UPDATE_EXCEPTION;
DELETE from gmd_formula_substitution
WHERE formula_id = get_object_rec.object_id
AND substitution_id = my_subs_rec.substitution_id;
SELECT rcp.owner_organization_id
INTO l_orgn_id
FROM gmd_recipes_b rcp
WHERE rcp.recipe_id = get_object_rec.object_id;
||' : About check if Recipe update is allowed, Recipe Id = '
||get_object_rec.object_id);
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
SELECT DECODE(get_object_rec.replace_type,'FORMULA_ID'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'ROUTING_ID'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'RECIPE_TYPE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OWNER_ID'
,get_object_rec.new_name
,NULL)
INTO l_formula_id, l_routing_id, l_organization_id, l_recipe_type, l_owner_id
FROM dual;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE NO_UPDATE_EXCEPTION;
RAISE No_Update_Exception;
SELECT recipe_version
INTO l_object_version
FROM gmd_recipes_b
WHERE recipe_id = l_recipe_id;
UPDATE gmd_recipes_b
SET formula_id = DECODE(get_object_rec.replace_type,'FORMULA_ID'
,get_object_rec.new_name
,formula_id),
routing_id = DECODE(get_object_rec.replace_type,'ROUTING_ID'
,get_object_rec.new_name
,routing_id),
owner_id = DECODE(get_object_rec.replace_type,'OWNER_ID'
,get_object_rec.new_name
,owner_id),
owner_organization_id = DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
,get_object_rec.new_name
,owner_organization_id),
recipe_type = DECODE(get_object_rec.replace_type,'RECIPE_TYPE'
,get_object_rec.new_name
,recipe_type),
last_update_date = P_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE recipe_id = get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
delete_recipe_dependencies(get_object_rec.object_id,'FORMULA');
delete_recipe_dependencies(get_object_rec.object_id,'ROUTING');
SELECT rot.owner_organization_id , rot.effective_end_date
INTO l_orgn_id, l_old_end_date
FROM gmd_routings_b rot
WHERE rot.routing_id = get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
SELECT DECODE(get_object_rec.replace_type,'START_DATE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'END_DATE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OPRN_ID'
,get_object_rec.old_name
,NULL),
DECODE(get_object_rec.replace_type,'OPRN_ID'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'ROUTING_CLASS'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OWNER_ID'
,get_object_rec.new_name
,NULL)
INTO l_start_date, l_end_date, l_old_oprn, l_new_oprn,
l_routing_class, l_owner_id
FROM dual;
RAISE No_Update_Exception;
SELECT routing_vers
INTO l_object_version
FROM gmd_routings_b
WHERE routing_id = l_routing_id;
p_rout_update_table(1).p_col_to_update := get_object_rec.replace_type;
gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : About update Routing '
||' Replace type = '
||get_object_rec.replace_type
||' new_name = '
||get_object_rec.new_name
);
p_rout_update_table(1).p_value := get_object_rec.new_name;
SELECT RoutingStep_id
INTO l_routingStep_id
FROM fm_rout_dtl
WHERE routing_id = get_object_rec.object_id
AND oprn_id = get_object_rec.old_name;
||' : About update Routing - Operation'
||' Routing Step id = '
||l_routingStep_id
||' Routing id = '
||get_object_rec.object_id
||' old_name = '
||get_object_rec.old_name
);
GMD_ROUTING_STEPS_PUB.update_routing_steps
(p_routingstep_id => l_routingStep_id
,p_routing_id => get_object_rec.object_id
,p_update_table => p_rout_update_table
,x_return_status => l_return_status
,x_message_count => l_mesg_count
,x_message_list => l_mesg_data
);
||' : About to call Update Routing API ');
GMD_ROUTINGS_PUB.update_routing
( p_routing_id => get_object_rec.object_id
, p_update_table => p_rout_update_table
, x_message_count => l_mesg_count
, x_message_list => l_mesg_data
, x_return_status => l_return_status
);
RAISE No_Update_Exception;
SELECT opr.owner_organization_id
INTO l_orgn_id
FROM gmd_operations_b opr
WHERE opr.oprn_id = get_object_rec.object_id;
RAISE NO_UPDATE_EXCEPTION;
SELECT DECODE(get_object_rec.replace_type,'START_DATE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'END_DATE'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'ACTIVITY'
,get_object_rec.old_name
,NULL),
DECODE(get_object_rec.replace_type,'ACTIVITY'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OPRN_CLASS'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'RESOURCES'
,get_object_rec.old_name
,NULL),
DECODE(get_object_rec.replace_type,'RESOURCES'
,get_object_rec.new_name
,NULL),
DECODE(get_object_rec.replace_type,'OWNER_ORGN_CODE'
,get_object_rec.new_name
,NULL)
INTO l_start_date, l_end_date, l_old_actv, l_new_actv,
l_oprn_class, l_old_resource, l_new_resource, l_organization_id
FROM dual;
RAISE No_Update_Exception;
SELECT oprn_vers
INTO l_object_version
FROM gmd_operations_b
WHERE oprn_id = l_oprn_id;
p_oprn_update_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
p_oprn_update_table(1).P_VALUE := get_object_rec.new_name;
gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : Update of Operation '||
' Replace type = '||p_oprn_update_table(1).P_COL_TO_UPDATE||
' and value = '||p_oprn_update_table(1).P_VALUE);
GMD_OPERATIONS_PUB.update_operation
( p_oprn_id => get_object_rec.object_id
, p_update_table => p_oprn_update_table
, x_message_count => l_mesg_count
, x_message_list => l_mesg_data
, x_return_status => l_return_status
);
SELECT oprn_line_id
INTO l_oprn_line_id
FROM gmd_operation_activities
WHERE oprn_id = get_object_rec.object_id
AND activity = get_object_rec.old_name;
p_oprn_activity_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
GMD_OPERATION_ACTIVITIES_PUB.update_operation_activity
( p_oprn_line_id => l_oprn_line_id
, p_update_table => p_oprn_activity_table
, X_RETURN_STATUS => l_return_status --Return Status
, X_MESSAGE_COUNT => l_mesg_count --Message Count
, X_MESSAGE_LIST => l_mesg_data --Message Data
);
SELECT r.oprn_line_id
INTO l_oprn_line_id
FROM gmd_operation_resources r, gmd_operation_activities a
WHERE a.oprn_id = get_object_rec.object_id
AND r.resources = get_object_rec.old_name
AND a.oprn_line_id = r.oprn_line_id;
p_oprn_resources_table(1).P_COL_TO_UPDATE := get_object_rec.replace_type;
GMD_OPERATION_RESOURCES_PUB.update_operation_resources
( p_oprn_line_id => l_oprn_line_id
, p_resources => get_object_rec.old_name
, p_update_table => p_oprn_resources_table
, X_RETURN_STATUS => l_return_status --Return Status
, X_MESSAGE_COUNT => l_mesg_count --Message Count
, X_MESSAGE_LIST => l_mesg_data --Message Data
);
RAISE No_Update_Exception;
RAISE NO_UPDATE_EXCEPTION;
gmd_debug.put_line(g_pkg_name||'.'||l_api_name||' : The update clumn = '||
get_object_rec.replace_type||
' and update value = '||
get_object_rec.new_name);
p_validity_rules_table(1).p_col_to_update := 'ORGANIZATION_ID';
RAISE NO_UPDATE_EXCEPTION;
p_validity_rules_table(1).p_col_to_update := get_object_rec.replace_type;
GMD_VALIDITY_RULES_PVT.update_validity_rules
( p_validity_rule_id => get_object_rec.object_id
, p_update_table => p_validity_rules_table
, x_message_count => l_mesg_count
, x_message_list => l_mesg_data
, x_return_status => l_return_status
);
RAISE No_Update_Exception;
RAISE NO_UPDATE_EXCEPTION;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT');
WHEN NO_UPDATE_EXCEPTION THEN
-- Bug# 5008299 Kapil M
-- Passing fnd_msg_pub.Count_Msg to get the top most message
fnd_msg_pub.get
(p_msg_index => fnd_msg_pub.Count_Msg
,p_data => l_error_text
,p_encoded => 'F'
,p_msg_index_out => l_dummy_cnt
);
||' : In the No_update_exception section '
||' Error text is '||l_error_text);
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
DELETE
FROM gmd_msnr_results
WHERE concurrent_id = pconcurrent_id;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
l_dynamic_select VARCHAR2(2000);
CURSOR get_select_id IS
Select object_id
From Gmd_MSNR_Results
Where concurrent_id IS NULL
And object_select_ind = 1;
SELECT count(*)
INTO l_row_count
FROM Gmd_MSNR_Results
WHERE object_select_ind = 1
AND Concurrent_id IS NULL;
SELECT DECODE(pObject_type,
'FORMULA','FM_FORM_MST_B',
'RECIPE','GMD_RECIPES_B',
'OPERATION','GMD_OPERATIONS_B',
'ROUTING','GMD_ROUTINGS_B',
'VALIDITY','GMD_RECIPE_VALIDITY_RULES'),
DECODE(pObject_type,
'FORMULA','FORMULA_ID',
'RECIPE','RECIPE_ID',
'OPERATION','OPRN_ID',
'ROUTING','ROUTING_ID',
'VALIDITY','RECIPE_VALIDITY_RULE_ID')
INTO l_table_name, l_primary_key
FROM sys.dual;
fnd_dsql.add_text( ' Update Gmd_MSNR_Results '||
' Set Object_select_ind = 0 '||
' Where concurrent_id IS NULL '||
' And Object_select_ind = 1 '||
' And object_id NOT IN ( Select object_id '||
' From Gmd_MSNR_Results, '||l_table_name||
' Where '||l_primary_key||' = object_id'||
' And concurrent_id IS NULL '||
' And Object_select_ind = 1' );
fnd_dsql.add_text(' SELECT FORMULA_ID FROM FM_MATL_DTL
WHERE LINE_TYPE = -1
AND INVENTORY_ITEM_ID = ');
fnd_dsql.add_text(' SELECT OPRN_ID FROM GMD_OPERATION_ACTIVITIES
WHERE ACTIVITY = ');
fnd_dsql.add_text(' SELECT OPRN_ID
FROM GMD_OPERATION_ACTIVITIES a, GMD_OPERATION_RESOURCES r
WHERE a.OPRN_LINE_ID = r.OPRN_LINE_ID
AND r.RESOURCES = ');
fnd_dsql.add_text(' SELECT ROUTING_ID
FROM FM_ROUT_DTL
WHERE OPRN_ID = ');
fnd_dsql.add_text(' SELECT RECIPE_ID
FROM GMD_RECIPES_B
WHERE OWNER_ORGANIZATION_ID = ');
fnd_dsql.add_text(' SELECT recipe_validity_rule_id
FROM GMD_RECIPE_VALIDITY_RULES
WHERE ORGANIZATION_ID = ');
l_dynamic_select := fnd_dsql.get_text(FALSE);
dbms_sql.parse(l_cursor_id, l_dynamic_select, dbms_sql.native);
' and Original select cnt = '||l_row_count);