[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* inserting a recipe */
/* HISTORY: */
/* 09/16/2003 Jeff Baird Bug #3136456 */
/* Changed owner from created_by. */
/* */
/* ===================================================*/
/* Start of commments */
/* API name : Create_Recipe_Header */
/* Type : Private */
/* Function : */
/* Paramaters : */
/* p_recipe_tbl IN Required */
/* */
/* OUT x_return_status */
/* */
/* */
/* Notes : */
/* End of comments */
/* ===================================================*/
PROCEDURE CREATE_RECIPE_HEADER
( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
p_recipe_hdr_flex_rec IN GMD_RECIPE_HEADER.flex ,
x_return_status OUT NOCOPY VARCHAR2
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_HEADER';
SELECT gmd_recipe_id_s.nextval INTO l_recipe_id
FROM sys.dual;
/* Making an insert into Recipe table */
/* To incorporate MLS, we need to call the */
/* on-insert pkg to insert into */
/* and table */
/* Text Code is handled by another package. */
GMD_RECIPES_MLS.INSERT_ROW(
X_ROWID => l_rowid,
X_RECIPE_ID => l_recipe_id,
X_RECIPE_NO => p_recipe_header_rec.recipe_no,
X_RECIPE_VERSION => p_recipe_header_rec.recipe_version,
X_OWNER_ORGANIZATION_ID => p_recipe_header_rec.owner_organization_id,
X_CREATION_ORGANIZATION_ID => p_recipe_header_rec.creation_organization_id,
X_FORMULA_ID => l_formula_id,
X_ROUTING_ID => l_routing_id,
X_PROJECT_ID => NULL,
X_RECIPE_STATUS => p_recipe_header_rec.recipe_status,
X_CALCULATE_STEP_QUANTITY => p_recipe_header_rec.calculate_step_quantity,
X_PLANNED_PROCESS_LOSS => p_recipe_header_rec.planned_process_loss,
X_CONTIGUOUS_IND => p_recipe_header_rec.contiguous_ind,
X_ENHANCED_PI_IND => p_recipe_header_rec.enhanced_pi_ind,
X_RECIPE_TYPE => p_recipe_header_rec.recipe_type,
X_RECIPE_DESCRIPTION => p_recipe_header_rec.recipe_description,
X_OWNER_LAB_TYPE => p_recipe_header_rec.owner_lab_type,
X_ATTRIBUTE_CATEGORY => p_recipe_hdr_flex_rec.attribute_category,
X_ATTRIBUTE1 => p_recipe_hdr_flex_rec.attribute1,
X_ATTRIBUTE2 => p_recipe_hdr_flex_rec.attribute2,
X_ATTRIBUTE3 => p_recipe_hdr_flex_rec.attribute3,
X_ATTRIBUTE4 => p_recipe_hdr_flex_rec.attribute4,
X_ATTRIBUTE5 => p_recipe_hdr_flex_rec.attribute5,
X_ATTRIBUTE6 => p_recipe_hdr_flex_rec.attribute6,
X_ATTRIBUTE7 => p_recipe_hdr_flex_rec.attribute7,
X_ATTRIBUTE8 => p_recipe_hdr_flex_rec.attribute8,
X_ATTRIBUTE9 => p_recipe_hdr_flex_rec.attribute9,
X_ATTRIBUTE10 => p_recipe_hdr_flex_rec.attribute10,
X_ATTRIBUTE11 => p_recipe_hdr_flex_rec.attribute11,
X_ATTRIBUTE12 => p_recipe_hdr_flex_rec.attribute12,
X_ATTRIBUTE13 => p_recipe_hdr_flex_rec.attribute13,
X_ATTRIBUTE14 => p_recipe_hdr_flex_rec.attribute14,
X_ATTRIBUTE15 => p_recipe_hdr_flex_rec.attribute15,
X_ATTRIBUTE16 => p_recipe_hdr_flex_rec.attribute16,
X_ATTRIBUTE17 => p_recipe_hdr_flex_rec.attribute17,
X_ATTRIBUTE18 => p_recipe_hdr_flex_rec.attribute18,
X_ATTRIBUTE19 => p_recipe_hdr_flex_rec.attribute19,
X_ATTRIBUTE20 => p_recipe_hdr_flex_rec.attribute20,
X_ATTRIBUTE21 => p_recipe_hdr_flex_rec.attribute21,
X_ATTRIBUTE22 => p_recipe_hdr_flex_rec.attribute22,
X_ATTRIBUTE23 => p_recipe_hdr_flex_rec.attribute23,
X_ATTRIBUTE24 => p_recipe_hdr_flex_rec.attribute24,
X_ATTRIBUTE25 => p_recipe_hdr_flex_rec.attribute25,
X_ATTRIBUTE26 => p_recipe_hdr_flex_rec.attribute26,
X_ATTRIBUTE27 => p_recipe_hdr_flex_rec.attribute27,
X_ATTRIBUTE28 => p_recipe_hdr_flex_rec.attribute28,
X_ATTRIBUTE29 => p_recipe_hdr_flex_rec.attribute29,
X_ATTRIBUTE30 => p_recipe_hdr_flex_rec.attribute30,
X_DELETE_MARK => 0,
X_TEXT_CODE => p_recipe_header_rec.text_code,
X_OWNER_ID => NVL(p_recipe_header_rec.owner_id
,gmd_api_grp.user_id),
X_CREATION_DATE => NVL(p_recipe_header_rec.creation_date
,SYSDATE),
X_CREATED_BY => NVL(p_recipe_header_rec.created_by
,gmd_api_grp.user_id),
X_LAST_UPDATE_DATE => NVL(p_recipe_header_rec.last_update_date
,SYSDATE),
X_LAST_UPDATED_BY => NVL(p_recipe_header_rec.last_updated_by
,gmd_api_grp.user_id),
X_LAST_UPDATE_LOGIN => NVL(p_recipe_header_rec.last_update_login
,gmd_api_grp.login_id),
X_FIXED_PROCESS_LOSS => p_recipe_header_rec.fixed_process_loss,
X_FIXED_PROCESS_LOSS_UOM => p_recipe_header_rec.fixed_process_loss_uom
);
/* Update_Recipe_Header */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating a recipe */
/* */
/* ================================================== */
/* Start of commments */
/* API name : Update_Recipe_Header */
/* Type : Private */
/* Function : */
/* Paramaters : */
/* p_recipe_tbl IN Required */
/* */
/* OUT x_return_status */
/* */
/* */
/* Notes : */
/* Sukarna Reddy 03/14/02. Bug 2099699. Modified */
/* to include validation for routing. */
/* Vipul Vaish 02/12/04 BUG#3427313 */
/* Modified CURSOR cur_getrcprout - Added one more */
/* condition in the Where clause. */
/* KSHUKLA added as per as 5138316 to incorporate */
/* deletion of the records for step and step*/
/* material association if the routing is */
/* nullified. 10-APR-2006 */
/* End of comments */
PROCEDURE UPDATE_RECIPE_HEADER
( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
p_flex_header_rec IN GMD_RECIPE_HEADER.update_flex ,
x_return_status OUT NOCOPY VARCHAR2
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_HEADER';
Select r.routing_id,b.status_type
From GMD_RECIPES r,gmd_status b
Where Recipe_id = pRecipe_id
And r.Recipe_status = b.Status_code;--BUG#3427313
SELECT 1
FROM gmd_recipe_step_materials a, fm_matl_dtl b
WHERE a.recipe_id = vRecipe_id
AND a.formulaline_id = b.formulaline_id
AND b.formula_id <>vFormula_id ;
SELECT 1
FROM gmd_recipe_validity_rules a
WHERE a.recipe_id = vRecipe_id
AND item_id not in (select item_id
from fm_matl_dtl
where line_type = 1
and formula_id = vFormula_id);
SELECT 1
FROM gmd_recipe_step_materials a, fm_rout_dtl b
WHERE a.recipe_id = vRecipe_id
AND a.routingstep_id = b.routingstep_id
AND b.routing_id <> NVL(vRouting_id, 0);
l_deleteRoutDependent BOOLEAN := FALSE;
/* If Recipe is frozen no updates can be made */
/* This needs to be a part of the GMD_COMMON_VAL , */
/* can be used by formulas and recipes */
/* ============================================= */
GMD_COMMON_VAL.Get_Status
( Status_code => p_recipe_header_rec.Recipe_status ,
Meaning => l_meaning ,
Description => l_description ,
x_return_status => l_return_status
);
FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_UPDATE_NOT_ALLOWED');
/* Making an updates into Recipe table */
/* To incorporate MLS, we need to call the */
/* on-insert pkg to insert into */
/* and table */
/* Text Code is handled by another package. */
IF (x_return_status = 'S') THEN
GMD_RECIPES_MLS.UPDATE_ROW(
X_RECIPE_ID => p_recipe_header_rec.recipe_id,
X_OWNER_ID => p_recipe_header_rec.owner_id,
X_OWNER_LAB_TYPE => p_recipe_header_rec.owner_lab_type,
X_DELETE_MARK => p_recipe_header_rec.delete_mark,
X_RECIPE_NO => p_recipe_header_rec.recipe_no,
X_RECIPE_VERSION => p_recipe_header_rec.recipe_version,
X_OWNER_ORGANIZATION_ID => p_recipe_header_rec.owner_organization_id,
X_CREATION_ORGANIZATION_ID => p_recipe_header_rec.creation_organization_id,
X_FORMULA_ID => p_recipe_header_rec.formula_id,
X_ROUTING_ID => l_routing_id,
X_PROJECT_ID => NULL,
X_RECIPE_STATUS => p_recipe_header_rec.recipe_status,
X_CALCULATE_STEP_QUANTITY => p_recipe_header_rec.calculate_step_quantity,
X_PLANNED_PROCESS_LOSS => p_recipe_header_rec.planned_process_loss,
X_CONTIGUOUS_IND => p_recipe_header_rec.contiguous_ind,
X_ENHANCED_PI_IND => p_recipe_header_rec.enhanced_pi_ind,
X_RECIPE_TYPE => p_recipe_header_rec.recipe_type,
X_RECIPE_DESCRIPTION => p_recipe_header_rec.recipe_description,
X_ATTRIBUTE_CATEGORY => p_flex_header_rec.attribute_category,
X_ATTRIBUTE1 => p_flex_header_rec.attribute1,
X_ATTRIBUTE2 => p_flex_header_rec.attribute2,
X_ATTRIBUTE3 => p_flex_header_rec.attribute3,
X_ATTRIBUTE4 => p_flex_header_rec.attribute4,
X_ATTRIBUTE5 => p_flex_header_rec.attribute5,
X_ATTRIBUTE6 => p_flex_header_rec.attribute6,
X_ATTRIBUTE7 => p_flex_header_rec.attribute7,
X_ATTRIBUTE8 => p_flex_header_rec.attribute8,
X_ATTRIBUTE9 => p_flex_header_rec.attribute9,
X_ATTRIBUTE10 => p_flex_header_rec.attribute10,
X_ATTRIBUTE11 => p_flex_header_rec.attribute11,
X_ATTRIBUTE12 => p_flex_header_rec.attribute12,
X_ATTRIBUTE13 => p_flex_header_rec.attribute13,
X_ATTRIBUTE14 => p_flex_header_rec.attribute14,
X_ATTRIBUTE15 => p_flex_header_rec.attribute15,
X_ATTRIBUTE16 => p_flex_header_rec.attribute16,
X_ATTRIBUTE17 => p_flex_header_rec.attribute17,
X_ATTRIBUTE18 => p_flex_header_rec.attribute18,
X_ATTRIBUTE19 => p_flex_header_rec.attribute19,
X_ATTRIBUTE20 => p_flex_header_rec.attribute20,
X_ATTRIBUTE21 => p_flex_header_rec.attribute21,
X_ATTRIBUTE22 => p_flex_header_rec.attribute22,
X_ATTRIBUTE23 => p_flex_header_rec.attribute23,
X_ATTRIBUTE24 => p_flex_header_rec.attribute24,
X_ATTRIBUTE25 => p_flex_header_rec.attribute25,
X_ATTRIBUTE26 => p_flex_header_rec.attribute26,
X_ATTRIBUTE27 => p_flex_header_rec.attribute27,
X_ATTRIBUTE28 => p_flex_header_rec.attribute28,
X_ATTRIBUTE29 => p_flex_header_rec.attribute29,
X_ATTRIBUTE30 => p_flex_header_rec.attribute30,
X_TEXT_CODE => p_recipe_header_rec.text_code,
X_LAST_UPDATE_DATE => NVL(p_recipe_header_rec.last_update_date
,SYSDATE),
X_LAST_UPDATED_BY => p_recipe_header_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => p_recipe_header_rec.last_update_login,
X_FIXED_PROCESS_LOSS => p_recipe_header_rec.fixed_process_loss,
X_FIXED_PROCESS_LOSS_UOM => p_recipe_header_rec.fixed_process_loss_uom
);
# KSHUKLA added the update statement
# While the recipe is deleted set the
# validity rules as deleted.
-------------------------------------------*/
IF p_recipe_header_rec.delete_mark =1 then
update GMD_RECIPE_VALIDITY_RULES
set DELETE_MARK = p_recipe_header_rec.delete_mark
WHERE recipe_id = p_recipe_header_rec.recipe_id;
# KSHUKLA added the delete statement
# as if the recipe is nullified
# delete the step and step material
# association records.
-------------------------------------------*/
-- bug 5138316 KSHUKLA
--Deleting the validity rules if formula_no or vers is updated
OPEN Cur_get_formula_val(p_recipe_header_rec.recipe_id, l_formula_id);
l_deleteRoutDependent := TRUE;
l_deleteRoutDependent := TRUE;
l_deleteRoutDependent := TRUE;
IF l_deleteRoutDependent THEN
delete from gmd_recipe_routing_steps
WHERE recipe_id =p_recipe_header_rec.recipe_id;
delete from gmd_recipe_step_materials
WHERE recipe_id =p_recipe_header_rec.recipe_id;
delete from gmd_recipe_validity_rules
where recipe_id =p_recipe_header_rec.recipe_id;
END UPDATE_RECIPE_HEADER;
PROCEDURE DELETE_RECIPE_HEADER
( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
p_flex_header_rec IN GMD_RECIPE_HEADER.update_flex ,
x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
/* Call the update API */
/* Delete in OPM world is not a physical delete. Its a logical delete */
/* i.e its an update with the delete_mark set to 1 */
/* Therefore prior to calling this procedure the delete_mark need to be set to 1 */
GMD_RECIPE_HEADER_PVT.UPDATE_RECIPE_HEADER
(p_recipe_header_rec => p_recipe_header_rec ,
p_flex_header_rec => p_flex_header_rec ,
x_return_status => x_return_status
);
END DELETE_RECIPE_HEADER;
/* inserting a recipe */
/* */
/* ===================================================*/
/* Start of commments */
/* API name : Copy_Recipe_Header */
/* Type : Private */
/* Function : */
/* Paramaters : */
/* p_recipe_tbl IN Required */
/* p_old_recipe_id */
/* p_recipe_header_rec */
/* p_recipe_hdr_flex_rec */
/* */
/* OUT x_return_status */
/* */
/* */
/* Notes : */
/* End of comments */
/* ===================================================*/
PROCEDURE COPY_RECIPE_HEADER
( p_old_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE ,
p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
p_recipe_hdr_flex_rec IN GMD_RECIPE_HEADER.flex ,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR get_old_recipe_record(vRecipe_id GMD_RECIPES_B.recipe_id%TYPE) IS
SELECT *
FROM gmd_recipes
WHERE recipe_id = vRecipe_id;
SELECT max(Recipe_version) + 1
FROM gmd_recipes_b
WHERE Recipe_no = vRecipe_no;
SELECT formula_no, formula_vers
FROM fm_form_mst_b
WHERE formula_id = vFormula_id;
SELECT formula_id
FROM fm_form_mst_b
WHERE formula_no = vFormula_no
AND formula_vers = vFormula_vers;
SELECT routing_id
FROM gmd_routings_b
WHERE routing_no = vRouting_no
AND routing_vers = vRouting_vers;
SELECT routing_no, routing_vers
FROM gmd_routings_b
WHERE routing_id = vRouting_id;
SELECT gmd_recipe_id_s.nextval
INTO l_recipe_header_rec.recipe_id
FROM sys.dual;
/* Delete Mark should always be set to 0 */
IF p_recipe_header_rec.delete_mark = FND_API.G_MISS_NUM THEN
fnd_message.set_name ('GMI', 'GMI_MISSING');
fnd_message.set_token ('MISSING', 'DELETE_MARK');
l_recipe_header_rec.delete_mark := 0;
IF p_recipe_header_rec.last_updated_by = FND_API.G_MISS_NUM THEN
fnd_message.set_name ('GMI', 'GMI_MISSING');
fnd_message.set_token ('MISSING', 'LAST_UPDATED_BY');
IF p_recipe_header_rec.last_updated_by IS NULL THEN
l_recipe_header_rec.last_updated_by
:= old_recipe_rec.last_updated_by;
ELSIF p_recipe_header_rec.last_updated_by
<> old_recipe_rec.last_updated_by THEN
l_recipe_header_rec.last_updated_by
:= p_recipe_header_rec.last_updated_by;
l_recipe_header_rec.last_updated_by
:= old_recipe_rec.last_updated_by;
IF p_recipe_header_rec.last_update_date = FND_API.G_MISS_DATE THEN
fnd_message.set_name ('GMI', 'GMI_MISSING');
fnd_message.set_token ('MISSING', 'LAST_UPDATE_DATE');
IF p_recipe_header_rec.last_update_date IS NULL THEN
l_recipe_header_rec.last_update_date
:= old_recipe_rec.last_update_date;
ELSIF p_recipe_header_rec.last_update_date
<> old_recipe_rec.last_update_date THEN
l_recipe_header_rec.last_update_date
:= p_recipe_header_rec.last_update_date;
l_recipe_header_rec.last_update_date
:= old_recipe_rec.last_update_date;
IF p_recipe_header_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_recipe_header_rec.last_update_login := NULL;
IF p_recipe_header_rec.last_update_login IS NULL THEN
l_recipe_header_rec.last_update_login
:= old_recipe_rec.last_update_login;
ELSIF p_recipe_header_rec.last_update_login
<> old_recipe_rec.last_update_login THEN
l_recipe_header_rec.last_update_login
:= p_recipe_header_rec.last_update_login;
l_recipe_header_rec.last_update_login
:= old_recipe_rec.last_update_login;