[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT v.*
FROM gmd_recipe_validity_rules v, gmd_recipes r, gmd_status s
WHERE v.recipe_id = r.recipe_id
AND v.validity_rule_status = s.status_code
AND v.recipe_id = NVL(P_RECIPE_ID, v.recipe_id)
AND ( r.recipe_no = NVL(p_recipe_no, r.recipe_no) AND r.recipe_version = nvl(p_recipe_version, r.recipe_version) )
AND r.formula_id = NVL(p_formula_id, r.formula_id)
AND ( (p_status_type IS NULL AND s.status_type IN ( '700', '900'))
OR (p_status_type IS NOT NULL AND s.status_type = p_status_type) )
AND v.recipe_use IN (0,p_recipe_use)
AND ((v.organization_id = NVL(p_organization_id,v.organization_id))
OR (v.organization_id IS NULL) )
/* Bug 2690833 - Thomas Daniel */
/* Modified the following start and end date condtions to ensure that the date */
/* range validation is done properly */
AND ( (p_start_date IS NULL) or
((start_date) <= (p_start_date) AND
(NVL(end_date, p_start_date)) >= (p_start_date)
)
)
AND ( (p_end_date IS NULL) OR
((NVL(end_date,p_end_date)) >= (P_end_date) AND
(start_date) <= (p_end_date))
)
AND (p_validity_rule_id IS NULL OR
(p_validity_rule_id IS NOT NULL AND v.recipe_validity_rule_id = p_validity_rule_id))
AND v.delete_mark = 0
ORDER BY orgn_code,preference, recipe_use, s.status_type ;
SELECT v.*
FROM gmd_recipe_validity_rules v, gmd_recipes_b r, gmd_status_b s,
mtl_system_items_kfv I, fm_matl_dtl d
WHERE v.recipe_id = r.recipe_id
AND v.validity_rule_status = s.status_code
AND i.inventory_item_id = v.inventory_item_id
AND r.owner_organization_id = i.organization_id
AND (v.inventory_item_id = p_item_id or i.concatenated_segments = p_item_no)
AND (p_revision IS NULL OR (p_revision IS NOT NULL AND v.revision = p_revision))
AND (r.formula_id = NVL(p_formula_id, r.formula_id))
AND (inv_min_qty <= nvl(l_quantity,inv_min_qty) AND inv_max_qty >= nvl(l_quantity,inv_max_qty))
AND ((p_status_type is NULL) AND (s.status_type IN ( '700', '900'))
OR ( p_status_type is NOT NULL AND s.status_type = p_status_type))
AND v.recipe_use IN (0,p_recipe_use)
AND ((v.organization_id = NVL(p_organization_id,v.organization_id))
or (v.organization_id IS NULL) )
AND ( (p_start_date IS NULL) or
((start_date) <= (p_start_date) AND
(NVL(end_date, p_start_date)) >= (p_start_date)
)
)
AND ( (p_end_date IS NULL) OR
((NVL(end_date,p_end_date)) >= (P_end_date) AND
(start_date) <= (p_end_date))
)
AND (p_validity_rule_id IS NULL OR
(p_validity_rule_id IS NOT NULL AND v.recipe_validity_rule_id =
p_validity_rule_id))
AND v.delete_mark = 0
AND d.formula_id = r.formula_id
AND v.inventory_item_id = d.inventory_item_id
AND (p_revision IS NULL OR (p_revision IS NOT NULL AND d.revision = p_revision))
AND d.line_type = 1
ORDER BY orgn_code,preference, recipe_use, s.status_type ;
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_no;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE uom_class = p_uom_class
AND base_uom_flag = 'Y';
SELECT qty, scale_type, detail_uom
FROM fm_matl_dtl
WHERE formula_id = l_formula_id
AND inventory_item_id = V_item_id
AND line_type = 1
ORDER BY line_no;
SELECT recipe_id
FROM gmd_recipes_b
WHERE recipe_no = V_recipe_no
AND recipe_version = V_recipe_vers;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT *
FROM GMD_VAL_RULE_GTMP;
SELECT inventory_item_id, qty, detail_uom
FROM fm_matl_dtl
WHERE formula_id = l_formula_id
AND line_type = 1
AND line_no = 1;
SELECT formula_id
FROM gmd_recipes_b
WHERE recipe_id = V_recipe_id;
/* Delete from this table for any existing data */
DELETE FROM GMD_VAL_RULE_GTMP;
GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
,p_unit_cost => l_unit_cost
,p_total_cost => l_total_cost);
GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
,p_unit_cost => l_unit_cost
,p_total_cost => l_total_cost);
GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
,p_unit_cost => l_unit_cost
,p_total_cost => l_total_cost);
x_recipe_validity_out(i).last_updated_by := l_rec.last_updated_by ;
x_recipe_validity_out(i).last_update_date := l_rec.last_update_date ;
x_recipe_validity_out(i).last_update_login := l_rec.last_update_login ;
SELECT inventory_item_id, qty, detail_uom, scale_type
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
AND line_type IN (1,2);
SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
AND line_type = V_line_type;
SELECT inventory_item_id, qty, detail_uom, scale_type
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
AND line_type = -1;
SELECT total_input_qty, yield_uom
FROM fm_form_mst
WHERE formula_id = p_formula_id;
SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
AND line_type = -1;
SELECT inventory_item_id, qty, detail_uom, scale_type
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
AND line_type = 1;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id;
SELECT v.*
FROM Gmd_recipe_validity_rules V, Gmd_recipes R, Gmd_status S
WHERE V.Recipe_id = R.Recipe_id
AND V.Validity_rule_status = S.Status_code
AND V.Recipe_id = NVL (P_recipe_id, V.Recipe_id)
AND v.delete_mark = 0
ORDER BY R.Recipe_no,R.Recipe_version, V.Recipe_use,Orgn_code, Preference,S.Status_type;
SELECT V.*
FROM Gmd_recipe_validity_rules V,
Gmd_recipes R,
Gmd_status S
WHERE V.Recipe_id = R.Recipe_id
AND V.Validity_rule_status = S.Status_code
AND V.inventory_item_id = P_item_id
AND (p_revision IS NULL OR (p_revision IS NOT NULL AND v.revision = p_revision))
AND v.delete_mark = 0
ORDER BY R.Recipe_no,R.Recipe_version, V.Recipe_use,Orgn_code, Preference,S.Status_type;
SELECT *
FROM GMD_VAL_RULE_GTMP;
SELECT rcp.formula_id, SUM(qty), MAX(detail_uom)
FROM gmd_recipes rcp, fm_matl_dtl d
WHERE rcp.recipe_id = v_recipe_id
AND rcp.formula_id = d.formula_id
AND d.line_type = 1
AND d.inventory_item_id = V_inventory_item_id;
/* Delete from this table for any existing data */
DELETE FROM GMD_VAL_RULE_GTMP;
GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
,p_unit_cost => l_unit_cost
,p_total_cost => l_total_cost);
GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
,p_unit_cost => l_unit_cost
,p_total_cost => l_total_cost);
x_recipe_validity_out(i).last_updated_by := l_rec.last_updated_by ;
x_recipe_validity_out(i).last_update_date := l_rec.last_update_date ;
x_recipe_validity_out(i).last_update_login := l_rec.last_update_login ;
SELECT qty, detail_uom
FROM gmd_recipes_b r, fm_matl_dtl d
WHERE r.recipe_id = p_recipe_id
AND r.formula_id = d.formula_id
AND d.line_type = 1
AND d.inventory_item_id = p_item_id;
SELECT f.formula_id, total_output_qty, yield_uom
FROM fm_form_mst_b f, gmd_recipes_b r
WHERE r.recipe_id = p_recipe_id
AND r.formula_id = f.formula_id;
PROCEDURE insert_val_temp_tbl (p_val_rec IN GMD_RECIPE_VALIDITY_RULES%ROWTYPE
,p_unit_cost IN NUMBER
,p_total_cost IN NUMBER) IS
BEGIN
INSERT INTO GMD_VAL_RULE_GTMP(
recipe_validity_rule_id, recipe_id , orgn_code , recipe_use ,
preference , start_date , end_date , min_qty ,
max_qty , std_qty , inv_min_qty , inv_max_qty ,
text_code , attribute_category , attribute1 , attribute2 ,
attribute3 , attribute4 , attribute5 , attribute6 ,
attribute7 , attribute8 , attribute9 , attribute10 ,
attribute11 , attribute12 , attribute13 , attribute14 ,
attribute15 , attribute16 , attribute17 , attribute18 ,
attribute19 , attribute20 , attribute21 , attribute22 ,
attribute23 , attribute24 , attribute25 , attribute26 ,
attribute27 , attribute28 , attribute29 , attribute30 ,
created_by , creation_date , last_updated_by , last_update_date ,
last_update_login , validity_rule_status , planned_process_loss , organization_id ,
inventory_item_id , revision , detail_uom , unit_cost ,
total_cost , delete_mark)
VALUES
(
p_val_rec.recipe_validity_rule_id, p_val_rec.recipe_id ,
p_val_rec.orgn_code , p_val_rec.recipe_use ,
p_val_rec.preference , p_val_rec.start_date ,
p_val_rec.end_date , p_val_rec.min_qty ,
p_val_rec.max_qty , p_val_rec.std_qty ,
p_val_rec.inv_min_qty , p_val_rec.inv_max_qty ,
p_val_rec.text_code , p_val_rec.attribute_category ,
p_val_rec.attribute1 , p_val_rec.attribute2 ,
p_val_rec.attribute3 , p_val_rec.attribute4 ,
p_val_rec.attribute5 , p_val_rec.attribute6 ,
p_val_rec.attribute7 , p_val_rec.attribute8 ,
p_val_rec.attribute9 , p_val_rec.attribute10 ,
p_val_rec.attribute11 , p_val_rec.attribute12 ,
p_val_rec.attribute13 , p_val_rec.attribute14 ,
p_val_rec.attribute15 , p_val_rec.attribute16 ,
p_val_rec.attribute17 , p_val_rec.attribute18 ,
p_val_rec.attribute19 , p_val_rec.attribute20 ,
p_val_rec.attribute21 , p_val_rec.attribute22 ,
p_val_rec.attribute23 , p_val_rec.attribute24 ,
p_val_rec.attribute25 , p_val_rec.attribute26 ,
p_val_rec.attribute27 , p_val_rec.attribute28 ,
p_val_rec.attribute29 , p_val_rec.attribute30 ,
p_val_rec.created_by , p_val_rec.creation_date ,
p_val_rec.last_updated_by , p_val_rec.last_update_date ,
p_val_rec.last_update_login , p_val_rec.validity_rule_status ,
p_val_rec.planned_process_loss , p_val_rec.organization_id ,
p_val_rec.inventory_item_id , p_val_rec.revision ,
p_val_rec.detail_uom , p_unit_cost ,
p_total_cost , p_val_rec.delete_mark);
END insert_val_temp_tbl;
SELECT Cost_Type, cost_source
FROM gmd_tech_parameters_b
WHERE organization_id = v_orgn_id
AND Default_cost_parameter = 1;
SELECT *
FROM fm_matl_dtl
WHERE formula_id = p_formula_id
ORDER BY line_type, line_no;