[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* insert_routing_steps */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the insert into routing */
/* details (fm_rout_dtl) table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* KMOTUPAL 21/4/2006 Bug# 3558478 Commented the code for */
/* validation of Operation */
/* =============================================================== */
PROCEDURE insert_routing_steps
(
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_routing_step_rec IN fm_rout_dtl%ROWTYPE
, 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_STEPS';
Select enforce_step_dependency
From gmd_routings_b
Where routing_id = vRouting_id;
Select oprn_no, oprn_vers
From gmd_operations_b
Where oprn_id = vOprn_id;
Select effective_start_date, effective_end_date
From gmd_routings_b
Where routing_id = vRouting_id;
,'INSERT_ROUTING_STEPS'
,gmd_routing_steps_PUB.m_pkg_name) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
and Operation is not logically deleted */
/* IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
Entity_id => p_routing_step_rec.oprn_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
'Insert the routing steps for routing with routing id = '||l_routing_id);
GMD_ROUTING_STEPS_PVT.insert_routing_steps
(p_routing_id => l_routing_id
,p_routing_step_rec => l_routing_step_rec
,x_return_status => x_return_status
);
||'After calling the pvt insert step API the return status: '||x_return_status);
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_routing_step_rec.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;
gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'failure due to insert step dep'||SQLERRM);
END insert_routing_steps;
/* insert_step_dependencies */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the insert into step */
/* dependency table is successfully. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implemenation */
/* =============================================================== */
PROCEDURE insert_step_dependencies
(
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_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
, p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
, 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_STEP_DEPENDENCIES';
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
/* Insert made into the step dependency table */
GMD_ROUTING_STEPS_PVT.insert_step_dependencies
( p_routing_id => l_routing_id
, p_routingstep_no => p_routingstep_no
, p_routings_step_dep_tbl => p_routings_step_dep_tbl
, x_return_status => x_return_status
);
gmd_debug.put_line('After inserting routing step dependencies');
END insert_step_dependencies;
/* update_routing_steps */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the update into routing */
/* details (fm_rout_dtl table) is success. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* =============================================================== */
PROCEDURE update_routing_steps
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
, p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
, 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 GMD_ROUTINGS_PUB.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_STEPS';
Select oprn_id
From fm_rout_dtl
Where routingStep_id = vRoutingStep_id;
Select owner_orgn_code
From gmd_routings_b
Where routing_id = vRouting_id;
routing_update_step_failure EXCEPTION;
SAVEPOINT update_routing_details;
/* Validation prior to Routings Steps update */
/* Validation : Check if the routing id exists in the db */
/* Validation : Check if routing header exists in the database */
IF (l_debug = 'Y') THEN
gmd_debug.put_line('Validation : check if the routing id is valid ');
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
RAISE routing_update_step_failure;
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
RAISE routing_update_step_failure;
and Operation is not logically deleted */
OPEN get_oprn_id(l_routingStep_id);
RAISE routing_update_step_failure;
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
Entity_id => l_oprn_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
RAISE routing_update_step_failure;
FOR a in 1..p_update_table.COUNT LOOP
if UPPER(p_update_table(a).p_col_to_update) = 'OPRN_ID' THEN
select effective_start_date,effective_end_date
into l_rout_start_date,l_rout_end_date
from fm_rout_hdr
where routing_id =l_routing_id;
IF GMDRTVAL_PUB.check_oprn(poprn_id =>p_update_table(a).p_value
,prouting_start_date => l_rout_start_date
,prouting_end_date => l_rout_end_date
) <> 0 THEN
RAISE VALID_DATE_EXCEPTION;
are not allowed. Therefore when the routing status is updated check
all the associated operation status */
/* Call the private API that does the actual update */
GMD_ROUTING_STEPS_PVT.update_routing_steps
( p_routingstep_id => l_routingstep_id
, p_update_table => p_update_table
, x_return_status => x_return_status
);
RAISE routing_update_step_failure;
gmd_debug.put_line('Routing step was updated successfullly');
WHEN routing_update_step_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT update_routing_details;
ROLLBACK TO SAVEPOINT update_routing_details;
ROLLBACK TO SAVEPOINT update_routing_details;
ROLLBACK TO SAVEPOINT update_routing_details;
END update_routing_steps;
/* update_step_dependencies */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the update into routing */
/* step dependency (fm_rout_dep table) is success. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* =============================================================== */
PROCEDURE update_step_dependencies
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE := NULL
, p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
, p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
, p_routing_id IN fm_rout_dep.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 GMD_ROUTINGS_PUB.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_STEP_DEPENDENCIES';
routing_update_dep_failure EXCEPTION;
SAVEPOINT update_step_dependency;
/* Validation prior to Routings Step dependency update */
/* Validation : Impact with ASQC ON and change to transfer % */
/* To be determined */
FOR a IN 1 .. p_update_table.count LOOP
/* Validation : Check if transfer percent value is valid */
IF UPPER(p_update_table(a).p_col_to_update) = 'TRANSFER_PCT' THEN
l_transfer_pct := p_update_table(a).p_value;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'DEP_TYPE' THEN
l_dep_type := p_update_table(a).p_value;
ELSIF UPPER(p_update_table(a).p_col_to_update) = 'STANDARD_DELAY' THEN
l_std_delay := p_update_table(a).p_value;
END IF; /* UPPER(p_update_table(i).p_col_to_update) = 'TRANSFER_PCT' */
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
RAISE routing_update_dep_failure;
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
RAISE routing_update_dep_failure;
GMD_ROUTING_STEPS_PVT.update_step_dependencies
( p_routingstep_no => l_routingstep_no
, p_dep_routingstep_no => p_dep_routingstep_no
, p_routing_id => l_routing_id
, p_update_table => p_update_table
, x_return_status => x_return_status
);
RAISE routing_update_dep_failure;
gmd_debug.put_line('Routing was updated successfullly');
WHEN routing_update_dep_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT update_step_dependency;
ROLLBACK TO SAVEPOINT update_step_dependency;
ROLLBACK TO SAVEPOINT update_routing_details;
END update_step_dependencies;
/* Delete_Routing_step */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the delete into routing */
/* step dependency (fm_rout_dep table) is success. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* =============================================================== */
PROCEDURE delete_routing_step
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
, p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
, p_routing_id IN fm_rout_dtl.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) := 'DELETE_ROUTING_STEP';
needs to be deleted */
Cursor Check_Step_dep_rec(vRoutingstep_no fm_rout_dep.routingStep_no%TYPE
,vRouting_id gmd_routings.Routing_id%TYPE) IS
Select dep_routingstep_no
From fm_rout_dep
Where routingStep_no = vRoutingStep_no
And routing_id = vrouting_id;
routing_delete_step_failure EXCEPTION;
routing_delete_stepdep_failure EXCEPTION;
SAVEPOINT delete_routing_step;
RAISE routing_delete_step_failure;
RAISE routing_delete_step_failure;
RAISE routing_delete_step_failure;
for the routing step dep delete */
IF p_routingStep_id IS NOT NULL THEN
l_routingstep_id := p_routingstep_id;
RAISE routing_delete_step_failure;
RAISE routing_delete_step_failure;
RAISE routing_delete_step_failure;
step material association table. If it is then delete is not allowed */
IF GMDRTVAL_PUB.Check_routing_override_exists(l_routingstep_id) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_STEP_USED_IN_RECIPE');
RAISE routing_delete_step_failure;
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
RAISE routing_delete_step_failure;
/* Actual delete is performed */
GMD_ROUTING_STEPS_PVT.delete_routing_step
( p_routingstep_id => l_routingstep_id
, p_routing_id => l_routing_id
, x_return_status => x_return_status
);
gmd_debug.put_line('Routing step was deleted successfullly');
WHEN routing_delete_step_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT delete_routing_step;
ROLLBACK TO SAVEPOINT delete_routing_step;
WHEN routing_delete_stepdep_failure THEN
ROLLBACK TO SAVEPOINT delete_routing_step;
gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'delete step dep API not complete');
ROLLBACK TO SAVEPOINT delete_routing_step;
END delete_routing_step;
/* delete_step_dependencies */
/* */
/* DESCRIPTION: */
/* */
/* API returns (x_return_code) = 'S' if the delete in routing */
/* step dependency (fm_rout_dep table) is success. */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* =============================================================== */
PROCEDURE delete_step_dependencies
( p_api_version IN NUMBER := 1
, p_init_msg_list IN BOOLEAN := TRUE
, p_commit IN BOOLEAN := FALSE
, p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
, p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE := NULL
, p_routing_id IN fm_rout_dep.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) := 'DELETE_STEP_DEPENDENCIES';
routing_delete_dep_failure EXCEPTION;
SAVEPOINT delete_step_dependency;
/* Validation prior to Routings Step dependency delete */
/* Validation 1: Check if this step is being used in other tables */
/* Tables to be checked are mainly gmd step material association
and maybe batch table. Prevent delete if these steps are used in these tables */
/* Get the routing_id value */
IF (l_debug = 'Y') THEN
gmd_Debug.put_line('Validation: In dep step API if routing header exists in the database ');
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
RAISE routing_delete_dep_failure;
/* Actual delete in fm_rout_dep table */
/* This delete can be specific to a dep_routingstep_no or a
Routingstep_no */
IF (l_debug = 'Y') THEN
gmd_Debug.put_line('About to delete from step dep table - the routingstep no = '
||p_routingstep_no ||' and routing id = '||l_routing_id);
and Routing is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
Entity_id => l_routing_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
RAISE routing_delete_dep_failure;
GMD_ROUTING_STEPS_PVT.delete_step_dependencies
( p_routingstep_no => p_routingstep_no
, p_dep_routingstep_no => p_dep_routingstep_no
, p_routing_id => l_routing_id
, x_return_status => x_return_status
);
RAISE routing_delete_dep_failure;
gmd_debug.put_line('Routing was deleted successfullly');
WHEN routing_delete_dep_failure OR invalid_version THEN
ROLLBACK TO SAVEPOINT delete_step_dependency;
ROLLBACK TO SAVEPOINT delete_step_dependency;
ROLLBACK TO SAVEPOINT delete_step_dependency;
END delete_step_dependencies;