[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT activity
FROM gmd_operation_activities a
WHERE NOT EXISTS (select 'X' from gmd_operation_resources r
where a.oprn_line_id = r.oprn_line_id)
AND a.oprn_id = poprn_id;
/* API returns (x_return_code) = 'S' if the update of status code */
/* is successful. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* Shyam 05/30/03 Bug 2985443 Cannot change formula status */
/* to approved for general use or Lab use for */
/* formulas created with total output qty =0 */
/* Jeff Baird 02/11/2004 Changed gmd_api_pub to gmd_api_grp. */
/* Kalyani 07/03/2006 Bug 5347418 Fetched recipe_use and checked */
/* if items are costing enabled if recipe use */
/* is for costing. */
/* kalyani 08/23/2006 Bug 5394532 Added code for substitution */
/* kalyani 09/19/2006 Bug 5534373 Removed code to check if */
/* product is GME enabled */
/* =============================================================== */
PROCEDURE modify_status
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_entity_name IN VARCHAR2
, p_entity_id IN NUMBER := NULL
, p_entity_no IN VARCHAR2 := NULL
, p_entity_version IN NUMBER := NULL
, p_to_status IN VARCHAR2
, p_ignore_flag IN BOOLEAN := FALSE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_STATUS';
SELECT a.status_type, a.description, b.target_status
FROM gmd_status a, gmd_status_next b
WHERE a.status_code = vStatus_from
AND b.target_status = vStatus_to
AND a.status_code = b.current_status;
SELECT status_type, description
FROM gmd_status
WHERE status_code = vStatus_to;
SELECT recipe_id, recipe_no, recipe_version, recipe_status
FROM gmd_recipes_b
WHERE ((vRecipe_no IS NULL AND vRecipe_vers IS NULL) AND
(recipe_id = vRecipe_id)) OR
((vRecipe_id IS NULL) AND
(recipe_no = vRecipe_no AND recipe_version = vRecipe_vers));
SELECT formula_id, formula_no, formula_vers, formula_status
FROM fm_form_mst_b
WHERE ((vformula_no IS NULL AND vformula_vers IS NULL) AND
(formula_id = vformula_id)) OR
((vformula_id IS NULL) AND
(formula_no = vformula_no AND formula_vers = vformula_vers));
SELECT routing_id, routing_no, routing_vers, routing_status
FROM gmd_routings_b
WHERE ((vrouting_no IS NULL AND vrouting_vers IS NULL) AND
(routing_id = vrouting_id)) OR
((vrouting_id IS NULL) AND
(routing_no = vrouting_no AND routing_vers = vrouting_vers));
SELECT oprn_id, oprn_no, oprn_vers, operation_status
FROM gmd_operations_b
WHERE ((voperation_no IS NULL AND voperation_vers IS NULL) AND
(oprn_id = voperation_id)) OR
((voperation_id IS NULL) AND
(oprn_no = voperation_no AND oprn_vers = voperation_vers));
SELECT recipe_validity_rule_id, validity_rule_status, recipe_use
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = vVR_id;
SELECT substitution_id, substitution_name, substitution_version, substitution_status
FROM gmd_item_substitution_hdr_b
WHERE ((vSubs_no IS NULL AND vSubs_vers IS NULL) AND
(substitution_id = vSubs_id)) OR
((vSubs_id IS NULL) AND
(substitution_name = vSubs_no AND substitution_version = vSubs_vers));
SELECT r.formula_id, vr.organization_id
FROM gmd_recipes r, gmd_recipe_validity_rules vr
WHERE r.recipe_id = vr.recipe_id
AND vr.recipe_validity_rule_id = vVR_id;
SELECT 1 from sys.dual
WHERE EXISTS (
SELECT recipe_validity_rule_id
FROM gmd_status s, gmd_recipe_validity_rules v
WHERE recipe_id = vEntity_id
AND v.validity_rule_status = s.status_code
AND (to_number(s.status_type) < to_number('800')
OR s.status_type = '900') );
SELECT 1 from sys.dual
WHERE EXISTS (
SELECT recipe_validity_rule_id
FROM gmd_status s, gmd_recipe_validity_rules v
WHERE recipe_id = vEntity_id
AND v.validity_rule_status = s.status_code
AND to_number(s.status_type) < to_number('800') );
SELECT 1 from sys.dual
WHERE EXISTS (
SELECT recipe_validity_rule_id
FROM gmd_status s, gmd_recipe_validity_rules v
WHERE recipe_id = vEntity_id
AND v.validity_rule_status = s.status_code
AND to_number(s.status_type) < to_number('1000') );
SELECT SUM(qty)
FROM fm_matl_dtl
WHERE formula_id = vEntity_id
AND line_type IN (1,2);
SELECT i.process_execution_enabled_flag
FROM mtl_system_items i, gmd_recipe_validity_rules v
WHERE v.recipe_validity_rule_id = V_val_rule_id
AND i.inventory_item_id = v.inventory_item_id
AND i.organization_id = v.organization_id
AND v.organization_id IS NOT NULL
UNION
-- Get process ececution enabled setting for the recipe owning orgn
-- for Global recipes (Global val rules)
SELECT i.process_execution_enabled_flag
FROM mtl_system_items i, gmd_recipe_validity_rules v, gmd_recipes_b re
WHERE v.recipe_validity_rule_id = V_val_rule_id
AND i.inventory_item_id = v.inventory_item_id
AND v.recipe_id = re.recipe_id
AND i.organization_id = re.owner_organization_id
AND v.organization_id IS NULL;
SELECT COUNT(f.inventory_item_id)
FROM fm_matl_dtl f, mtl_system_items i
WHERE f.formula_id = V_form_id
AND f.inventory_item_id = i.inventory_item_id
AND f.organization_id = i.organization_id
AND i.eng_item_flag = 'Y';
status_update_failure EXCEPTION;
SELECT meaning
FROM gmd_status
WHERE status_code = P_status_code;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
RAISE status_update_failure;
SELECT DECODE(l_entity_name,
'FORMULA','GMD_FORMULA_INUSE',
'RECIPE','GMD_RECIPE_BTCH_DEP',
'OPERATION','GMD_OPERATION_INUSE',
'ROUTING','GMD_ROUTING_INUSE',
'VALIDITY','GMD_VR_BTCH_DEP') INTO l_mesg_text
FROM sys.dual;
RAISE status_update_failure;
RAISE status_update_failure;
SELECT DECODE(l_entity_name,
'FORMULA',1,
'RECIPE',3,
'OPERATION',2,
'ROUTING',4,
'VALIDITY',5) INTO l_entity_value
FROM sys.dual;
RAISE status_update_failure;
RAISE status_update_failure;
/* Check if the VR for this recipe needs to be updated */
IF (p_ignore_flag) THEN
-- Now update the VR according to recipe status change
IF (to_number(l_to_status_type) = 800) THEN
-- Change status to ON-HOLD for less than ON-HOLD
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = P_to_status
WHERE recipe_id = l_entity_id
AND (to_number(validity_rule_status) < to_number('800') OR
to_number(validity_rule_status) between 900 and 999);
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = P_to_status
WHERE recipe_id = l_entity_id
AND to_number(validity_rule_status) < to_number('800') ;
gmd_debug.put_line('Ignore flag was true and we are about update VR ');
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = P_to_status
WHERE recipe_id = l_entity_id
AND to_number(validity_rule_status) < to_number('1000') ;
RAISE status_update_failure;
GMD_ERES_UTILS.update_formula_status
(p_formula_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_object_name => l_entity_no
,p_object_version => l_entity_version
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
gmd_debug.put_line('In GMD Status Pub - About call ERES Util for update Recipe ');
GMD_ERES_UTILS.update_recipe_status
(p_recipe_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_object_name => l_entity_no
,p_object_version => l_entity_version
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
gmd_debug.put_line('In GMD Status Pub - After call ERES Util for update Recipe ');
GMD_ERES_UTILS.update_operation_status
(p_oprn_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_object_name => l_entity_no
,p_object_version => l_entity_version
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
GMD_ERES_UTILS.update_routing_status
(p_routing_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_object_name => l_entity_no
,p_object_version => l_entity_version
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
GMD_ERES_UTILS.update_validity_rule_status
( p_validity_rule_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
gmd_debug.put_line('In GMD Status Pub - About call ERES Util for update substitution ');
GMD_ERES_UTILS.update_substitution_status
(p_substitution_id => l_entity_id
,p_from_status => l_from_status
,p_to_status => P_to_status
,p_pending_status => l_pending_status
,p_rework_status => l_rework_status
,p_called_from_form => 'T'
,x_return_status => l_eSignature_status);
gmd_debug.put_line('In GMD Status Pub - After call ERES Util for update substitution ');
RAISE status_update_failure;
gmd_debug.put_line('Status was updated successfullly');
WHEN status_update_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT modify_status;
SELECT DECODE(P_entity_name,
'FORMULA','FM_FORM_MST_B',
'RECIPE','GMD_RECIPES_B',
'OPERATION','GMD_OPERATIONS_B',
'ROUTING','GMD_ROUTINGS_B',
'VALIDITY','GMD_RECIPE_VALIDITY_RULES') INTO l_table_name
FROM sys.dual;