[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* inserting a recipe */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Create_Recipe_Header */
/* Type : Public */
/* Function : */
/* Paramaters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_tbl IN Required */
/* */
/* OUT x_return_status OUT NOCOPY varchar2(1) */
/* x_msg_count OUT NOCOPY Number */
/* x_msg_data OUT NOCOPY varchar2(2000) */
/* */
/* Version : Current Version 1.1 */
/* kkillams 23-03-2004 Added call to modify_status to set recipe */
/* status to default status if default status is*/
/* defined organization level w.r.t. bug 3408799*/
/* */
PROCEDURE CREATE_RECIPE_HEADER
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_called_from_forms IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_recipe_header_tbl IN recipe_tbl
,p_recipe_header_flex IN recipe_flex
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_HEADER';
SAVEPOINT Insert_Recipe;
ROLLBACK TO Insert_Recipe;
ROLLBACK to Insert_Recipe;
ROLLBACK to Insert_Recipe;
/* Update_Recipe_Header */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating a recipe */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Update_Recipe_Header */
/* Type : Public */
/* Function : */
/* Paramaters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_tbl IN Required */
/* */
/* OUT x_return_status OUT NOCOPY varchar2(1) */
/* x_msg_count OUT NOCOPY Number */
/* x_msg_data OUT NOCOPY varchar2(2000) */
/* */
/* Version : Current Version 2.0 */
/* */
PROCEDURE UPDATE_RECIPE_HEADER
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_called_from_forms IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_header_tbl IN recipe_tbl ,
p_recipe_update_flex IN recipe_update_flex
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_HEADER';
p_flex_header_rec update_flex;
/* Define a cursor for dealing with updates */
CURSOR Recipe_cur(pRecipe_id GMD_RECIPES.recipe_id%TYPE) IS
Select *
From GMD_RECIPES
Where Recipe_id = pRecipe_id;
update_recipe_err EXCEPTION;
SAVEPOINT Update_Recipe;
/* provided for updates and it must exists */
/* ============================================= */
GMD_RECIPE_VAL.RECIPE_EXISTS
( P_API_VERSION => 1.0 ,
P_RECIPE_ID => p_recipe_header_rec.Recipe_id ,
P_RECIPE_NO => p_recipe_header_rec.Recipe_no ,
P_RECIPE_VERSION => p_recipe_header_rec.Recipe_version ,
X_RETURN_STATUS => X_return_status ,
X_MSG_COUNT => x_msg_count ,
X_MSG_DATA => x_msg_data ,
X_RETURN_CODE => l_return_code ,
X_RECIPE_ID => l_recipe_id
);
IF (p_recipe_update_flex.count <> 0) THEN
p_flex_header_rec := p_recipe_update_flex(i);
RAISE update_recipe_err;
/* is not provided, update it with what */
/* exists in the db */
/* ==================================== */
IF (p_recipe_header_rec.recipe_no IS NULL) THEN
p_recipe_header_rec.recipe_no := l_recipe_header_rec.recipe_no;
RAISE update_recipe_err;
RAISE update_recipe_err;
/* If organization ID is being updated then we need to verify if the new organization */
/* has access to the fomula elements */
IF p_recipe_header_rec.owner_organization_id <> l_recipe_header_rec.owner_organization_id THEN
GMD_API_GRP.check_item_exists (p_formula_id => p_recipe_header_rec.formula_id
,p_organization_id => p_recipe_header_rec.owner_organization_id
,x_return_status => x_return_status);
RAISE update_recipe_err;
IF (p_recipe_header_rec.delete_mark IS NULL) THEN
p_recipe_header_rec.delete_mark
:= l_recipe_header_rec.delete_mark;
IF (p_recipe_header_rec.last_update_date IS NULL) THEN
p_recipe_header_rec.last_update_date
:= sysdate;
p_recipe_header_rec.last_updated_by
:= gmd_api_grp.user_id;
IF (p_recipe_header_rec.last_update_login IS NULL) THEN
p_recipe_header_rec.last_update_login
:= NVL(l_recipe_header_rec.last_update_login,
gmd_api_grp.login_id);
/* as the user is intending to update the field to NULL */
IF (p_recipe_header_rec.routing_id = FND_API.G_MISS_NUM) THEN
p_recipe_header_rec.routing_id := NULL;
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);
RAISE update_recipe_err;
WHEN update_recipe_err THEN
ROLLBACK TO Update_Recipe;
ROLLBACK to Update_Recipe;
ROLLBACK to Update_Recipe;
END UPDATE_RECIPE_HEADER;
PROCEDURE DELETE_RECIPE_HEADER
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_called_from_forms IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_recipe_header_tbl IN recipe_tbl
,p_recipe_update_flex IN recipe_update_flex
) 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.UPDATE_RECIPE_HEADER
(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_called_from_forms => p_called_from_forms
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_recipe_header_tbl => p_recipe_header_tbl
,p_recipe_update_flex => p_recipe_update_flex
);
END DELETE_RECIPE_HEADER;