[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* insert_routing */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the insert into routing */
/* header (fm_rout_hdr or gmd_routings) table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* P.Raghu 08/27/2003 Bug#3068013 K is intialized with 1. */
/* kkillams23-03-2004 Added call to modify_status to set routing */
/* status to default status if default status is */
/* defined organization level w.r.t. bug 3408799 */
/* Uday Phadtare 13-MAR-2008 Bug 6871738. Select ROUTING_CLASS_UOM */
/* instead of UOM in Cursor Rout_cls_cur. */
/* Raju -- Bug 9314021 Feb 02 2010 if owner id is passed then it has */
/* to be considered else assign gmd_api_grp.user_id. */
/* ================================================================= */
PROCEDURE insert_routing
(
p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routings IN gmd_routings%ROWTYPE
, p_routings_step_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_tab
, p_routings_step_dep_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROUTING';
Select ROUTING_CLASS_UOM
From fm_rout_cls
Where routing_class = vRouting_class
and delete_mark = 0;
Select oprn_no, oprn_vers
From gmd_operations_b
Where oprn_id = vOprn_id;
SELECT gem5_routing_id_s.NEXTVAL
FROM sys.dual;
SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
SELECT 1
FROM fnd_user
WHERE user_id = p_owner_id;
,'INSERT_ROUTING'
,gmd_routings_PUB.m_pkg_name) THEN
RAISE invalid_version;
GMD_ROUTINGS_PVT.insert_routing
( p_routings => l_routings_rec
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => x_return_status
);
||'Insert the routing steps for routing with routing id = '||l_routing_id);
GMD_ROUTING_STEPS_PUB.insert_routing_steps
(p_routing_id => l_routing_id
,p_routing_step_rec => p_routings_step_tbl(i)
,p_routings_step_dep_tbl => l_stepdep_tbl
,p_commit => FALSE
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => l_return_from_routing_step
);
l_step_dep_tab.delete;
l_step_dep_tab(k).last_updated_by := p_routings_step_dep_tbl(j).last_updated_by ;
l_step_dep_tab(k).last_update_date := p_routings_step_dep_tbl(j).last_update_date ;
l_step_dep_tab(k).last_update_login := p_routings_step_dep_tbl(j).last_update_login ;
GMD_ROUTING_STEPS_PUB.insert_step_dependencies
(
p_routing_id => l_routing_id
,p_routingstep_no => p_routings_step_tbl(i).routingstep_no
,p_routings_step_dep_tbl => l_step_dep_tab
,p_commit => FALSE
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => l_return_from_routing_step_dep
);
/* Check if insert of step dependency was done */
IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS THEN
RAISE routing_step_dep_failure;
END insert_routing;
/* update_routing */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the update into routing */
/* header (fm_rout_hdr or gmd_routings) table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* RLNAGARA 25-Apr-2008 B6997624 Check if the fixed process loss uom is valid*/
/* Raju -- Bug 9314021 Feb 02 2010 if owner id is passed then it has */
/* to be considered for update */
/* =============================================================== */
PROCEDURE update_routing
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routing_id IN gmd_routings.routing_id%TYPE := NULL
, p_routing_no IN gmd_routings.routing_no%TYPE := NULL
, p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
, p_update_table IN update_tbl_type
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTING';
Select routing_status
From gmd_routings
Where routing_id = vRouting_id;
Select *
From gmd_routings
Where Routing_id = vRouting_id;
SELECT 1
FROM fnd_user
WHERE user_id = p_owner_id;
routing_update_failure EXCEPTION;
SAVEPOINT update_routing;
||'Begining of Update API ');
/* Validation 1. Check if this routing that is updated does exists
in the the database. The routing_id is the PK or Routing_no and version is
the unique key for this table (gmd_routings_b). */
IF p_routing_id IS NOT NULL THEN
l_routing_id := p_routing_id;
RAISE routing_update_failure;
RAISE routing_update_failure;
RAISE routing_update_failure;
are not allowed. Therefore when the routing status is updated check
all the associated operation status */
OPEN get_cur_status(l_routing_id);
FOR a IN 1 .. p_update_table.count LOOP
/* Validation: status dependencies */
IF UPPER(p_update_table(a).p_col_to_update) = 'STATUS' THEN
IF NOT GMD_STATUS_CODE.CHECK_DEPENDENT_STATUS
( P_Entity_Type => 4,
P_Entity_id => l_routing_id,
P_Current_Status => l_cur_status,
P_To_Status => p_update_table(a).p_value) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS_DEPEND_NOT_APPROVED');
RAISE routing_update_failure;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'OWNER_ORGANIZATION_ID' THEN
l_owner_orgn_id := p_update_table(a).p_value;
RAISE routing_update_failure;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'ROUTING_CLASS' THEN
IF p_update_table(a).p_value IS NOT NULL THEN
IF GMDRTVAL_PUB.check_routing_class(p_update_table(a).p_value) <> 0 THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ROUT_CLS');
RAISE routing_update_failure;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'ROUTING_UOM' THEN
IF p_update_table(a).p_value IS NOT NULL THEN
IF (NOT(gmd_api_grp.validate_um(p_update_table(a).p_value))) THEN
FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
RAISE routing_update_failure;
/* Validation: delete_mark validation */
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'DELETE_MARK' THEN
GMDRTVAL_PUB.check_delete_mark ( Pdelete_mark => p_update_table(a).p_value,
x_return_status => l_return_status);
FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
RAISE routing_update_failure;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM' THEN
IF p_update_table(a).p_value IS NOT NULL THEN
IF (NOT(gmd_api_grp.validate_um(p_update_table(a).p_value))) THEN
FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
RAISE routing_update_failure;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'OWNER_ID' THEN
-- rnalla Bug 9314021 add the new cusror to check for valid user id
IF p_update_table(a).p_value IS NOT NULL THEN
OPEN Cur_user_id(p_update_table(a).p_value);
Raise routing_update_failure;
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED
(Entity => 'ROUTING',
Entity_id => l_routing_id,
Update_Column_Name => p_update_table(a).p_col_to_update ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
RAISE routing_update_failure;
GMD_ROUTINGS_PVT.update_routing
( p_routing_id => l_routing_id
, p_update_table => p_update_table
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => x_return_status
);
RAISE routing_update_failure;
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing was updated successfullly');
WHEN routing_update_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT update_routing;
ROLLBACK TO SAVEPOINT update_routing;
ROLLBACK TO SAVEPOINT update_routing;
END update_routing;
/* delete_routing */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the delete into routing */
/* header (fm_rout_hdr or gmd_routings) table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* kkillams 02/17/2004 Added new validation which checks whether */
/* Routing is associated with any recipe or */
/* not w.r.t. bug 3355204 */
/* =============================================================== */
PROCEDURE delete_routing
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routing_id IN gmd_routings.routing_id%TYPE := NULL
, p_routing_no IN gmd_routings.routing_no%TYPE := NULL
, p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/*Cursor verifies whether routing associated with any recipe or not*/
CURSOR Cur_check_rout(cp_routing_id gmd_recipes.routing_id%TYPE)
IS SELECT count(1) FROM gmd_recipes
WHERE routing_id = cp_routing_id
AND delete_mark = 0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTING';
l_update_table GMD_ROUTINGS_PUB.UPDATE_TBL_TYPE;
routing_delete_failure EXCEPTION;
SAVEPOINT delete_routing;
/* Validation :. Check if this routing that is deleted does exists
in the the database. The routing_id is the PK or Routing_no and version is
the unique key for this table (gmd_routings_b). */
IF p_routing_id IS NOT NULL THEN
l_routing_id := p_routing_id;
RAISE routing_delete_failure;
l_update_table(1).P_COL_TO_UPDATE := 'DELETE_MARK';
l_update_table(1).P_VALUE := '1';
GMD_ROUTINGS_PUB.update_routing
( p_routing_id => l_routing_id
, p_update_table => l_update_table
, p_commit => FALSE
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => x_return_status
);
RAISE routing_delete_failure;
WHEN routing_delete_failure OR invalid_version or routing_used THEN
ROLLBACK TO SAVEPOINT delete_routing;
ROLLBACK TO SAVEPOINT delete_routing;
ROLLBACK TO SAVEPOINT delete_routing;
END delete_routing;
/* undelete_routing */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the delete into routing */
/* header (fm_rout_hdr or gmd_routings) table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* =============================================================== */
PROCEDURE undelete_routing
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routing_id IN gmd_routings.routing_id%TYPE := NULL
, p_routing_no IN gmd_routings.routing_no%TYPE := NULL
, p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'UNDELETE_ROUTING';
l_update_table GMD_ROUTINGS_PUB.UPDATE_TBL_TYPE;
routing_undelete_failure EXCEPTION;
SAVEPOINT undelete_routing;
/* Validation :. Check if this routing that is deleted does exists
in the the database. The routing_id is the PK or Routing_no and version is
the unique key for this table (gmd_routings_b). */
IF p_routing_id IS NOT NULL THEN
l_routing_id := p_routing_id;
RAISE routing_undelete_failure;
UPDATE gmd_routings_b
SET delete_mark = 0
WHERE routing_id = l_routing_id;
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing was undeleted successfullly');
WHEN routing_undelete_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT undelete_routing;
ROLLBACK TO SAVEPOINT undelete_routing;
ROLLBACK TO SAVEPOINT undelete_routing;
END undelete_routing;