[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* RECIPE_FOR_UPDATE in: recipe_id; out: recipe_data, lock row */
/* PROCESS_LOSS_FOR_UPDATE in: recipe_id, orgn_code; out: lock row */
/* This procedure does no insert/update/delete */
/* P_validation_level - standard parameter */
/* OUT: */
/* x_return_status - standard parameter. S=success,E=expected error, */
/* U=unexpected error */
/* x_msg_count - standard parameter. Num of messages generated */
/* x_msg_data - standard parameter. If only1 msg, here it is */
/* x_return_code - num rows returned or SQLCODE (Database error number)*/
/* **************************************************************************/
/* NAME */
/* recipe_exists */
/* DESCRIPTION */
/* This procedure will check if given id or name and version exist in GMD_RECIPES. */
/* If name and vers provided, id will be returned. */
/* PARAMETERS standard + recipe_id, recipe_no, recipe_vers */
/* RETURN VALUES standard + recipe_id */
/* 24Jul2001 L.R.Jackson Added "AND recipe_no is null" clause. */
/**************************************************************************** */
PROCEDURE recipe_exists
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_recipe_id IN NUMBER,
p_recipe_no IN VARCHAR2,
p_recipe_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY NUMBER,
x_recipe_id OUT NOCOPY NUMBER)
IS
/* If recipe id alone is given */
/* OR */
/* If recipe_no and recipe_version are given. */
/* */
/* If all 3 are given, compare the recipe_id returned with the */
/* recipe_id given as parameter. */
CURSOR get_record_with_recipe_id(vRecipe_id NUMBER) IS
select recipe_id
from gmd_recipes_b
where recipe_id = vRecipe_id;
select recipe_id
from gmd_recipes_b
where recipe_no = vRecipe_no
and recipe_version = vRecipe_version;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
/* PARAMETERS standard + recipe_no, recipe_vers, action_code=I(insert) or U(udpate) */
/* RETURN VALUES standard + recipe_id */
/**************************************************************************** */
PROCEDURE recipe_name
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_recipe_no IN VARCHAR2,
p_recipe_version IN NUMBER,
p_action_code IN VARCHAR2 := 'U',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY NUMBER,
x_recipe_id OUT NOCOPY NUMBER)
IS
CURSOR get_record IS
select recipe_id
from gmd_recipes_b
where recipe_no = p_recipe_no
and recipe_version = p_recipe_version;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF; /* end if action code is insert or update */
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
select gmd_recipe_id_s.NEXTVAL
from dual;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
/* recipe_for_update */
/* DESCRIPTION */
/* This procedure will */
/* */
/* PARAMETERS (other than standard parameters) */
/* */
/* RETURN VALUES (other than standard return values) */
/*
/* Person Date Comments */
/* --------- ------ ------------------------------------------ */
/* LRJackson 14Nov2000 Created */
/* LRJackson 27Dec2000 Updated parameters */
/**************************************************************************** */
PROCEDURE recipe_for_update
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_recipe_id IN NUMBER,
p_last_update_date IN DATE,
p_form_or_asynch IN VARCHAR2 := 'A',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY NUMBER)
IS
CURSOR get_recipe_data IS
select last_update_date
from gmd_recipes
where recipe_id = p_recipe_id;
l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_FOR_UPDATE';
l_update_date DATE;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
FETCH get_recipe_data into l_update_date;
IF p_last_update_date is NULL OR l_update_date <> p_last_update_date THEN
RAISE fnd_api.g_exc_error;
END IF; /* end if update dates do not match */
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
END recipe_for_update;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
select plant_ind, lab_ind
from gmd_parameters_hdr
where organization_id = g_orgn_id;
SELECT 1
FROM org_access_view
WHERE responsibility_id = vresp_id
AND organization_id = g_orgn_id;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
/* process_loss_for_update */
/* DESCRIPTION */
/* This procedure will */
/* */
/* PARAMETERS (other than standard parameters) */
/* */
/* RETURN VALUES (other than standard return values) */
/* */
/* Person Date Comments */
/* --------- ------ ------------------------------------------ */
/* LRJackson 14Nov2000 Created */
/**************************************************************************** */
PROCEDURE process_loss_for_update
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_recipe_id IN NUMBER,
p_orgn_id IN NUMBER,
p_last_update_date IN DATE,
p_form_or_asynch IN VARCHAR2 := 'A',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY NUMBER)
IS
CURSOR check_recipe_id IS
select last_update_date
from gmd_recipe_process_loss
where recipe_id = p_recipe_id
and organization_id = p_orgn_id;
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOSS_FOR_UPDATE';
l_update_date DATE;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
FETCH check_recipe_id into l_update_date;
IF p_last_update_date is NULL OR l_update_date <> p_last_update_date THEN
RAISE fnd_api.g_exc_error;
END IF; /* end if update dates do not match */
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
END process_loss_for_update;
select recipe_id
from gmd_recipe_customers
where recipe_id = p_recipe_id
and customer_id = p_customer_id;
/* no SAVEPOINT needed because there is no insert/update/delete */
IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
l_api_name, G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
/* no standard check of p_commit because no insert/update/delete */
/* standard call to get msge cnt, and if cnt is 1, get mesg info */
FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
Deleted status type 900 so that recipe can be created with
frozen routings.
**************************************************************************** */
FUNCTION check_routing_validity(p_routing_id NUMBER,
p_recipe_status VARCHAR2) RETURN BOOLEAN IS
CURSOR Cur_rtstatus_vldty IS
SELECT COUNT(*)
FROM gmd_routings_b h,gmd_status s
WHERE h.routing_id = p_routing_id AND
h.routing_status = s.status_code AND
to_number(h.routing_status) >= to_number(p_recipe_status) AND
-- Begin Bug#2402946 Ravi S Reddy
-- Deleted Status_Type 900
s.status_type NOT IN ('800','1000');
SELECT std_qty, inventory_item_id, detail_uom, organization_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = V_vr_id;
SELECT recipe_validity_rule_id
FROM gmd_recipe_validity_rules
WHERE recipe_id = pRecipe_id
AND inventory_item_id = pitem_id
AND ((organization_id = pOrgn_id) OR
(organization_id IS NULL AND pOrgn_id is NULL))
AND recipe_use = pRecipe_Use
AND preference = pPreference
AND std_qty = pstd_qty
AND min_qty = pmin_qty
AND max_qty = pmax_qty
AND inv_max_qty = pinv_max_qty
AND inv_min_qty = pinv_min_qty
AND detail_uom = pitem_um
AND validity_rule_status = pValidity_Rule_status
AND ((pPlanned_process_loss IS NULL AND Planned_process_loss IS NULL) OR
(planned_process_loss = pPlanned_process_loss))
AND start_date = pstart_date
AND ((end_date = pend_date) OR (end_date is NULL and pend_date is NULL));