[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oprn_no
FROM gmd_operations
WHERE oprn_id = P_oprn_id;
SELECT oprn_vers
FROM gmd_operations
WHERE oprn_id = P_oprn_id;
SELECT formula_no
FROM fm_form_mst_b
WHERE formula_id = P_formula_id;
SELECT formula_vers
FROM fm_form_mst_b
WHERE formula_id = P_formula_id;
SELECT formula_desc1
FROM fm_form_mst
WHERE formula_id = P_formula_id;
SELECT recipe_no
FROM gmd_recipes_b
WHERE recipe_id = P_recipe_id;
SELECT recipe_version
FROM gmd_recipes_b
WHERE recipe_id = P_recipe_id;
SELECT routing_no
FROM gmd_routings_b
WHERE routing_id = P_routing_id;
SELECT routing_vers
FROM gmd_routings_b
WHERE routing_id = P_routing_id;
SELECT meaning
FROM gem_lookups g, fm_matl_dtl d
WHERE formulaline_id = P_formulaline_id
AND lookup_type = 'LINE_TYPE'
AND lookup_code = d.line_type;
SELECT meaning
FROM gmd_status
WHERE status_code = P_status_code;
SELECT process_qty_uom
FROM gmd_operations
WHERE oprn_id = P_oprn_id;
SELECT activity_desc
FROM fm_actv_mst
where activity = p_activity;
SELECT resource_desc
from cr_rsrc_mst
where resources = p_resource;
UPDATE fm_form_mst_b
SET formula_status = p_to_status
WHERE formula_id = p_formula_id;
UPDATE fm_form_mst_b
SET formula_status = l_pending_status
WHERE formula_id = p_formula_id;
UPDATE fm_form_mst_b
SET formula_status = l_rework_status
WHERE formula_id = p_formula_id;
UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
SET substitution_status = p_to_status
WHERE substitution_id = p_substitution_id;
UPDATE GMD_FORMULA_SUBSTITUTION SET Associated_flag ='Y'
WHERE substitution_id = P_substitution_id;
GMD_API_GRP.update_end_date (p_substitution_id);
UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
SET substitution_status = l_pending_status
WHERE substitution_id = p_substitution_id;
UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
SET substitution_status = l_rework_status
WHERE substitution_id = p_substitution_id;
UPDATE gmd_formulation_specs
SET spec_status = p_to_status
WHERE formulation_spec_id = p_formulation_spec_id;
UPDATE gmd_formulation_specs
SET spec_status = l_pending_status
WHERE formulation_spec_id = p_formulation_spec_id;
UPDATE gmd_formulation_specs
SET spec_status = l_rework_status
WHERE formulation_spec_id = p_formulation_spec_id;
UPDATE gmd_operations_b
SET operation_status = p_to_status
WHERE oprn_id = p_oprn_id;
UPDATE gmd_operations_b
SET operation_status = l_pending_status
WHERE oprn_id = p_oprn_id;
UPDATE gmd_operations_b
SET operation_status = l_rework_status
WHERE oprn_id = p_oprn_id;
UPDATE gmd_routings_b
SET routing_status = p_to_status
WHERE routing_id = p_routing_id;
UPDATE gmd_routings_b
SET routing_status = l_pending_status
WHERE routing_id = p_routing_id;
UPDATE gmd_routings_b
SET routing_status = l_rework_status
WHERE routing_id = p_routing_id;
SELECT r.owner_organization_id, r.formula_id, r.recipe_no, r.recipe_version
FROM gmd_recipes_b r, fm_form_mst_b f
WHERE r.recipe_id = p_recipe_id
AND r.formula_id = f.formula_id;
SELECT recipe_use_prod, recipe_use_plan, recipe_use_cost, recipe_use_reg, recipe_use_tech, managing_validity_rules
FROM gmd_recipe_generation
WHERE (organization_id = V_orgn_id
OR organization_id IS NULL)
ORDER BY orgn_code;
UPDATE gmd_recipes_b
SET recipe_status = p_to_status
WHERE recipe_id = p_recipe_id;
UPDATE gmd_recipes_b
SET recipe_status = l_pending_status
WHERE recipe_id = p_recipe_id;
UPDATE gmd_recipes_b
SET recipe_status = l_rework_status
WHERE recipe_id = p_recipe_id;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = p_to_status
WHERE recipe_validity_rule_id = p_validity_rule_id;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = l_pending_status
WHERE recipe_validity_rule_id = p_validity_rule_id;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = l_rework_status
WHERE recipe_validity_rule_id = p_validity_rule_id;
UPDATE fm_form_mst_b
SET formula_status = p_to_status
WHERE formula_id = p_formula_id;
UPDATE fm_form_mst_b
SET formula_status = l_pending_status
WHERE formula_id = p_formula_id;
UPDATE fm_form_mst_b
SET formula_status = l_rework_status
WHERE formula_id = p_formula_id;
SELECT recipe_validity_rule_id
FROM gmd_recipe_validity_rules
WHERE recipe_id = p_recipe_id
AND validity_rule_status < p_to_status;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = p_to_status
WHERE recipe_validity_rule_id = l_recipe_validity_rule_id;
# update_formula_status
# SYNOPSIS
# update_formula_status
# DESCRIPTION
# Performs update of the formula status and the raise of event
###############################################################*/
PROCEDURE update_formula_status ( p_formula_id IN VARCHAR2,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_version IN NUMBER,
p_called_from_form IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_desc IS
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
AND lookup_code = 'FORMULA';
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
STATUS_UPDATE_FAILURE EXCEPTION;
SELECT OWNER_ORGANIZATION_ID
FROM FM_FORM_MST_B
WHERE FORMULA_ID = CP_FORMULA_ID;
SAVEPOINT update_formula;
SELECT 'x'
INTO l_text
FROM fm_form_mst
WHERE formula_id = p_formula_id
FOR UPDATE OF formula_status nowait;
UPDATE fm_form_mst
SET formula_status = p_to_status,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE formula_id = p_formula_id;
RAISE STATUS_UPDATE_FAILURE;
UPDATE fm_form_mst
SET formula_status = p_pending_status
WHERE formula_id = p_formula_id;
ROLLBACK TO SAVEPOINT update_formula;
ROLLBACK TO SAVEPOINT update_formula;
WHEN STATUS_UPDATE_FAILURE THEN
ROLLBACK TO SAVEPOINT update_formula;
gmd_debug.put_line ('In GMDERESB.pls - Status update failure section');
ROLLBACK TO SAVEPOINT update_formula;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
END update_formula_status;
# update_operation_status
# SYNOPSIS
# update_operation_status
# DESCRIPTION
# Performs update of the operation status and the raise of event
###############################################################*/
PROCEDURE update_operation_status(p_oprn_id IN VARCHAR2,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_version IN NUMBER,
p_called_from_form IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_desc IS
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
AND lookup_code = 'OPERATION';
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
SAVEPOINT update_operation;
SELECT 'x'
INTO l_text
FROM gmd_operations_b
WHERE oprn_id = p_oprn_id
FOR UPDATE OF operation_status nowait;
UPDATE gmd_operations_b
SET operation_status = p_to_status,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE oprn_id = p_oprn_id;
UPDATE gmd_operations_b
SET operation_status = p_pending_status
WHERE oprn_id = p_oprn_id;
ROLLBACK TO SAVEPOINT update_operation;
ROLLBACK TO SAVEPOINT update_operation;
ROLLBACK TO SAVEPOINT update_operation;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
END update_operation_status;
# update_routing_status
# SYNOPSIS
# update_routing_status
# DESCRIPTION
# Performs update of the routing status and the raise of event
###############################################################*/
PROCEDURE update_routing_status ( p_routing_id IN VARCHAR2,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_version IN NUMBER,
p_called_from_form IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_desc IS
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
AND lookup_code = 'ROUTING';
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
SAVEPOINT update_routing;
SELECT 'x'
INTO l_text
FROM gmd_routings_b
WHERE routing_id = p_routing_id
FOR UPDATE OF routing_status nowait;
UPDATE gmd_routings_b
SET routing_status = p_to_status,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE routing_id = p_routing_id;
UPDATE gmd_routings_b
SET routing_status = p_pending_status
WHERE routing_id = p_routing_id;
ROLLBACK TO SAVEPOINT update_routing;
ROLLBACK TO SAVEPOINT update_routing;
ROLLBACK TO SAVEPOINT update_routing;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
END update_routing_status;
# update_recipe_status
# SYNOPSIS
# update_recipe_status
# DESCRIPTION
# Performs update of the recipe status and the raise of event
###############################################################*/
PROCEDURE update_recipe_status ( p_recipe_id IN VARCHAR2,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_version IN NUMBER,
p_called_from_form IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_desc IS
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
AND lookup_code = 'RECIPE';
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
SELECT status_type, description
FROM gmd_status
WHERE status_code = pstatus;
SAVEPOINT update_recipe;
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'The to status type = '||l_status_type);
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'VR eres sig is required and it has be done manually ');
/* Based on the recipe status condition - update the Vr status */
IF l_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 = p_recipe_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 = p_recipe_id
AND to_number(validity_rule_status) < to_number('800') ;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = p_to_status
WHERE recipe_id = p_recipe_id
AND to_number(validity_rule_status) < to_number('1000') ;
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'About to lock this recipe '||p_recipe_id);
SELECT 'x'
INTO l_text
FROM gmd_recipes_b
WHERE recipe_id = p_recipe_id
FOR UPDATE OF recipe_status nowait;
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'About to update recipe with status = '||p_to_status);
UPDATE gmd_recipes_b
SET recipe_status = p_to_status,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE recipe_id = p_recipe_id;
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'Checks if recipe esig is req ');
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'Esig is req with pending sts = '|| p_pending_status);
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'Raising Esig event ');
gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
'After Raise Esig event ');
UPDATE gmd_recipes_b
SET recipe_status = p_pending_status
WHERE recipe_id = p_recipe_id;
ROLLBACK TO SAVEPOINT update_recipe;
ROLLBACK TO SAVEPOINT update_recipe;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
ROLLBACK TO SAVEPOINT update_recipe;
ROLLBACK TO SAVEPOINT update_recipe;
FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
END update_recipe_status;
# update_validity_rule_status
# SYNOPSIS
# update_validity_rule_status
# DESCRIPTION
# Performs update of the validity status and the raise of event
###############################################################*/
PROCEDURE update_validity_rule_status ( p_validity_rule_id IN VARCHAR2,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_called_from_form IN VARCHAR2 DEFAULT 'F',
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
SELECT a.recipe_no, a.recipe_version, b.recipe_use
FROM gmd_recipes_b a, gmd_recipe_validity_rules b
WHERE a.recipe_id = b.recipe_id
AND b.recipe_validity_rule_id = vVr_id;
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_FORMULA_USE'
AND lookup_code = vlookup_code;
l_api_name VARCHAR2(100) := 'UPDATE_VALIDITY_RULE_STATUS';
SAVEPOINT update_validity;
SELECT 'x'
INTO l_text
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = P_validity_rule_id
FOR UPDATE OF validity_rule_status nowait;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = p_to_status,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE recipe_validity_rule_id = P_validity_rule_id;
UPDATE gmd_recipe_validity_rules
SET validity_rule_status = p_pending_status
WHERE recipe_validity_rule_id = P_validity_rule_id;
ROLLBACK TO SAVEPOINT update_validity;
ROLLBACK TO SAVEPOINT update_validity;
ROLLBACK TO SAVEPOINT update_validity;
ROLLBACK TO SAVEPOINT update_validity;
END update_validity_rule_status;
# update_substitution_status
# SYNOPSIS
# update_substitution_status
# DESCRIPTION
# Procdure will update the substitution status based on the default status
# and raise the event if required.
# Added the procedure for bug#5394532.
###############################################################*/
PROCEDURE update_substitution_status (p_substitution_id IN NUMBER,
p_from_status IN VARCHAR2,
p_to_status IN VARCHAR2,
p_pending_status IN VARCHAR2,
p_rework_status IN VARCHAR2,
p_called_from_form IN VARCHAR2 DEFAULT 'F',
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
SELECT description
FROM gmd_status
WHERE status_code = pstatus;
SELECT owner_organization_id,
substitution_name,
substitution_version
FROM gmd_item_substitution_hdr
WHERE substitution_id = p_substitution_id;
l_api_name VARCHAR2(100) := 'UPDATE_SUBSTITUTION_STATUS';
SAVEPOINT update_substitution;
SELECT 'x'
INTO l_text
FROM gmd_item_substitution_hdr_b
WHERE substitution_id = p_substitution_id
FOR UPDATE OF substitution_status nowait;
UPDATE gmd_item_substitution_hdr_b
SET substitution_status = p_to_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE substitution_id = p_substitution_id;
UPDATE gmd_item_substitution_hdr_b
SET substitution_status = p_pending_status
WHERE substitution_id = p_substitution_id;
ROLLBACK TO SAVEPOINT update_substitution;
ROLLBACK TO SAVEPOINT update_substitution;
ROLLBACK TO SAVEPOINT update_substitution;
END update_substitution_status;
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_FORMULA_USE'
and lookup_code = V_lookup_code;
SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE inventory_item_id = pitem_id
AND organization_id = porgn_id;
SELECT organization_code
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_id = p_orgn_id;
SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = plookup_type and
lookup_code = plookup_code;
SELECT tech_parm_name, lm_unit_code
FROM gmd_tech_parameters_b
WHERE tech_parm_id = P_tech_parm_id;
SELECT concatenated_segments
FROM mtl_categories_kfv
WHERE category_id = P_category_id;
SELECT category_set_name
FROM mtl_category_sets
WHERE category_set_id = P_category_set_id;
SELECT LINE_NO
FROM fm_matl_dtl
WHERE formulaline_id = P_formulaline_id;
SELECT ROUTING_NO, ROUTING_VERS ,ROUTING_DESC , b.MEANING
FROM gmd_routings_vl a , gmd_status b
WHERE a.routing_id = V_routing_id
AND a.routing_status = b.status_code;
SELECT MEANING
FROM GEM_LOOKUPS
WHERE LOOKUP_TYPE = 'GME_YES'
AND LOOKUP_CODE = vLookup_code;
SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'GME_YES' and
lookup_code = plookup_code;