[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* inserting a recipe */
/* ============================================= */
/* Start of commments */
/* API name : Create_Recipe_Process_loss */
/* Type : Public */
/* Function : */
/* parameters : */
/* 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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE CREATE_RECIPE_PROCESS_LOSS
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_called_from_forms IN VARCHAR2 := 'NO'
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_recipe_detail_tbl IN recipe_detail_tbl
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_PROCESS_LOSS';
SAVEPOINT Insert_Recipe_Process_loss;
ROLLBACK to Insert_Recipe_Process_loss;
ROLLBACK to Insert_Recipe_Process_loss;
ROLLBACK to Insert_Recipe_Process_loss;
/* inserting a recipe */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Create_Recipe_Customers */
/* Type : Public */
/* Function : */
/* Parameters : */
/* 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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE CREATE_RECIPE_CUSTOMERS
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_CUSTOMERS';
SAVEPOINT Insert_Recipe_Customers;
ROLLBACK to Insert_Recipe_Customers;
ROLLBACK to Insert_Recipe_Customers;
ROLLBACK to Insert_Recipe_Customers;
/* inserting a recipe */
/* ============================================= */
/* Start of commments */
/* API name : Create_Recipe_VR */
/* Type : Public */
/* Function : */
/* parameters : */
/* 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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* 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*/
/* */
/* End of comments */
PROCEDURE CREATE_RECIPE_VR
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_called_from_forms IN VARCHAR2 := 'NO'
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_recipe_vr_tbl IN recipe_vr_tbl
,p_recipe_vr_flex IN recipe_flex
) IS
/* Define all variables specific to this procedure */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_VR';
SELECT inventory_item_id, qty, detail_uom
FROM fm_matl_dtl f, gmd_recipes_b r
WHERE f.formula_id = r.formula_id
AND r.recipe_id = vRecipe_id
AND f.line_type = 1
AND f.line_no = 1;
SELECT qty, detail_uom
FROM fm_matl_dtl f, gmd_recipes_b r
WHERE f.formula_id = r.formula_id
AND r.recipe_id = vRecipe_id
AND f.line_type IN (1,2)
AND f.inventory_item_id = vItem_id
AND rownum = 1;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = vItem_id;
SELECT rt.Effective_Start_Date,
rt.Effective_End_Date
FROM gmd_routings_b rt, gmd_recipes_b rc
WHERE rc.routing_id = rt.routing_id AND
rc.recipe_id = vRecipe_id AND
rt.delete_mark = 0;
SELECT 1
FROM sys.dual
WHERE EXISTS (Select h.formula_id
From fm_form_mst h, gmd_recipes_b r
WHERE r.formula_id = h.formula_id AND
r.recipe_id = vRecipe_id AND
h.scale_type = 0);
Recipe_VR_insert_failure EXCEPTION;
SELECT uom_code
FROM mtl_units_of_measure_vl
WHERE uom_code = vDetail_uom;
SAVEPOINT Insert_Recipe_VR;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
RAISE Recipe_VR_insert_failure;
/* Insert into the recipe validity rules table */
gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := NULL;
RAISE Recipe_VR_insert_failure;
ROLLBACK to Insert_Recipe_VR;
WHEN setup_failure OR Recipe_VR_insert_failure THEN
ROLLBACK to Insert_Recipe_VR;
ROLLBACK to Insert_Recipe_VR;
ROLLBACK to Insert_Recipe_VR;
/* inserting a recipe */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Create_Recipe_Mtl */
/* Type : Public */
/* Function : */
/* parameters : */
/* 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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE CREATE_RECIPE_MTL
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_mtl_tbl IN recipe_mtl_tbl ,
p_recipe_mtl_flex IN recipe_flex
) IS
/* Define all variables specific to this procedure */
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_MTL';
SELECT line_type
FROM fm_matl_dtl f
WHERE f.FORMULALINE_ID= cp_formulaline_id;
insert_rcp_mat_failure EXCEPTION;
SAVEPOINT Insert_Recipe_Materials;
RAISE insert_rcp_mat_failure;
RAISE insert_rcp_mat_failure;
RAISE insert_rcp_mat_failure;
/* Validations so as to insert minimum_transfer_qty,minimum_delay,maximum_delay. Bug : 10109808 */
/* Minimum_delay
/* END of Validations so as to insert minimum_transfer_qty,minimum_delay,maximum_delay. Bug : 10109808 */
/* ======================================= */
/* Insert into the recipe materials table */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
GMD_RECIPE_DETAIL_PVT.create_recipe_mtl (p_recipe_mtl_rec => p_recipe_mtl_rec
,p_recipe_mtl_flex_rec => p_recipe_mtl_flex_rec
,x_return_status => x_return_status);
RAISE insert_rcp_mat_failure;
ROLLBACK to Insert_Recipe_Materials;
WHEN setup_failure OR insert_rcp_mat_failure THEN
ROLLBACK to Insert_Recipe_Materials;
ROLLBACK to Insert_Recipe_Materials;
ROLLBACK to Insert_Recipe_Materials;
ROLLBACK to Insert_Recipe_Materials;
/* Update_Recipe_Process_Loss */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating recipe process loss */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Update_Recipe_Process_loss */
/* Type : Public */
/* Function : */
/* parameters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE UPDATE_RECIPE_PROCESS_LOSS
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_PROCESS_LOSS';
Select *
From gmd_recipe_process_loss
Where Recipe_process_loss_id = VProcess_loss_id;
update_pr_loss_failure EXCEPTION;
SAVEPOINT Update_Recipe_Process_loss;
/* For updates we expect the surrogate */
/* key to be provided */
/* ================================== */
IF (p_recipe_detail_rec.recipe_process_loss_id IS NULL) THEN
FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
RAISE update_pr_loss_failure;
RAISE update_pr_loss_failure;
RAISE update_pr_loss_failure;
/* Update into the recipe process loss table */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
GMD_RECIPE_DETAIL_PVT.update_recipe_process_loss (p_recipe_detail_rec => p_recipe_detail_rec
,x_return_status => x_return_status);
RAISE update_pr_loss_failure;
ROLLBACK to Update_Recipe_Process_loss;
WHEN setup_failure OR update_pr_loss_failure THEN
x_return_status := FND_API.G_RET_STS_ERROR;
ROLLBACK to Update_Recipe_Process_loss;
ROLLBACK to Update_Recipe_Process_loss;
END UPDATE_RECIPE_PROCESS_LOSS;
/* Update_Recipe_Customers */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating recipe process loss */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Update_Recipe_Customers */
/* Type : Public */
/* Function : */
/* parameters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE UPDATE_RECIPE_CUSTOMERS
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl
) IS
/* Defining all local variables */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_CUSTOMERS';
Select text_code
from gmd_recipe_customers
where recipe_id = rc_id
and customer_id = cust_id;
update_rcp_cust_failure EXCEPTION;
SAVEPOINT Update_Recipe_Customers;
RAISE update_rcp_cust_failure;
RAISE update_rcp_cust_failure;
RAISE update_rcp_cust_failure;
/* Only updateable field is text code */
IF (p_recipe_detail_rec.text_Code IS NULL) THEN
OPEN get_rc_text_code(p_recipe_detail_rec.recipe_id,
p_recipe_detail_rec.customer_id);
/* Update the recipe customer table */
/* only who columns needs to be updated */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
GMD_RECIPE_DETAIL_PVT.update_recipe_customers (p_recipe_detail_rec => p_recipe_detail_rec
,x_return_status => x_return_status);
RAISE update_rcp_cust_failure;
ROLLBACK to Update_Recipe_Customers;
WHEN setup_failure OR update_rcp_cust_failure THEN
ROLLBACK to Update_Recipe_Customers;
ROLLBACK to Update_Recipe_Customers;
END UPDATE_RECIPE_CUSTOMERS;
/* Update_Recipe_VR */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating recipe Validity Rules */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Update_Recipe_VR */
/* Type : Public */
/* Function : */
/* parameters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE UPDATE_RECIPE_VR
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_called_from_forms IN VARCHAR2 := 'NO'
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_recipe_vr_tbl IN recipe_vr_tbl
,p_recipe_update_flex IN recipe_update_flex
) IS
/* Define all variables specific to this procedure */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_VR';
p_flex_update_rec UPDATE_FLEX;
l_flex_update_rec update_flex;
/* Define a cursor for dealing with updates */
CURSOR Flex_cur(vRecipe_VR_id NUMBER) IS
SELECT attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24,attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30
FROM gmd_recipe_validity_rules
WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
/* Define a cursor for dealing with updates */
CURSOR update_vr_cur(vRecipe_VR_id NUMBER) IS
SELECT recipe_id, orgn_code, end_date, planned_process_loss
FROM gmd_recipe_validity_rules
WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = pItem_no;
Update_VR_Failure EXCEPTION;
SELECT uom_code
FROM mtl_units_of_measure_vl
WHERE uom_code = vDetail_uom;
SAVEPOINT Update_Recipe_VR;
RAISE Update_VR_Failure;
/* as the user is intending to update the field to NULL */
FOR update_rec IN update_vr_Cur(p_recipe_vr_rec.recipe_validity_rule_id) LOOP
IF (p_recipe_vr_rec.orgn_code = FND_API.G_MISS_CHAR) THEN
p_recipe_vr_rec.orgn_code := NULL;
p_recipe_vr_rec.orgn_code := update_rec.orgn_code;
p_recipe_vr_rec.planned_process_loss := update_rec.planned_process_loss;
p_recipe_vr_rec.end_date := update_rec.end_date;
p_recipe_vr_rec.recipe_id := update_rec.recipe_id;
RAISE Update_VR_Failure;
RAISE Update_VR_Failure;
RAISE Update_VR_Failure;
RAISE Update_VR_Failure;
FETCH Flex_cur INTO l_flex_update_rec;
/* If no flex field is updated retain the old values */
IF (p_recipe_update_flex.count = 0) THEN
p_flex_update_rec := l_flex_update_rec;
p_flex_update_rec := p_recipe_update_flex(i);
IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute1 := NULL;
ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute2 := NULL;
ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute3 := NULL;
ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute4 := NULL;
ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute5 := NULL;
ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute6 := NULL;
ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute7 := NULL;
ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute8 := NULL;
ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute9 := NULL;
ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute10 := NULL;
ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute11 := NULL;
ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute12 := NULL;
ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute13 := NULL;
ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute14 := NULL;
ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute15 := NULL;
ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute16 := NULL;
ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute17 := NULL;
ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute18 := NULL;
ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute19 := NULL;
ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute20 := NULL;
ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute21 := NULL;
ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute22 := NULL;
ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute23 := NULL;
ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute24 := NULL;
ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute25 := NULL;
ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute26 := NULL;
ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute27 := NULL;
ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute28 := NULL;
ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute29 := NULL;
ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute30 := NULL;
ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute_category := NULL;
ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
/* Update recipe validity rules table */
GMD_RECIPE_DETAIL_PVT.UPDATE_RECIPE_VR(p_recipe_vr_rec => p_recipe_vr_rec
,p_flex_update_rec => p_flex_update_rec
,x_return_status => x_return_status);
RAISE Update_VR_Failure;
WHEN Update_VR_Failure THEN
x_return_status := FND_API.G_RET_STS_ERROR;
END LOOP; -- Loops thro all VR that needs to be updated
ROLLBACK to Update_Recipe_VR;
ROLLBACK to Update_Recipe_VR;
ROLLBACK to Update_Recipe_VR;
END UPDATE_RECIPE_VR;
/* inserting and updating recipe Routing steps */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Recipe_Routing_Steps */
/* Type : Public */
/* Function : */
/* parameters : */
/* p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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 */
/* */
/* Notes : 24Jul2001 L.R.Jackson Added mass and volume fields. */
/* Changed routing step id validation */
/* Increased the version to 1.1 */
/* Removed the detail record. Just use table(i) */
/* Removed check of user id/user name. There is */
/* no userid in this table. WHO columns are */
/* passed in, not derived here. */
/* Changed call to RECIPE_NAME to RECIPE_EXISTS. */
/* */
/* End of comments */
PROCEDURE RECIPE_ROUTING_STEPS
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl ,
p_recipe_insert_flex IN recipe_flex ,
p_recipe_update_flex IN recipe_update_flex
) IS
/* Define all variables specific to this procedure */
l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ROUTING_STEPS';
/* flex field records for inserts and updates */
p_flex_insert_rec flex;
p_flex_update_rec update_flex;
l_flex_update_rec update_flex;
/* Define a cursor for dealing with updates */
CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
Select attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24,attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30
From gmd_recipe_routing_steps
where recipe_id = NVL(vRecipe_id,-1) AND
RoutingStep_id = NVL(vRoutingStep_id,-1);
CURSOR update_rt_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
Select mass_qty, volume_qty, mass_std_uom, volume_std_uom
From gmd_recipe_routing_steps
where recipe_id = NVL(vRecipe_id,-1) AND
RoutingStep_id = NVL(vRoutingStep_id,-1);
/* Updating recipe routing step for first time is in fact inserting a new record */
/* in gmd_recipe_routing_step table. [Form initially shows values from */
/* fm_rout_dtl. When user "changes" values, they are saved in recipe table.] */
/* Define Savepoint */
SAVEPOINT Recipe_Routing_Steps;
IF (p_recipe_insert_flex.count = 0) THEN
p_flex_insert_rec := NULL;
p_flex_insert_rec := p_recipe_insert_flex(i);
FOR update_rec IN update_rt_cur(p_recipe_detail_rec.recipe_id,
p_recipe_detail_tbl(i).routingstep_id)
LOOP
IF (p_recipe_detail_rec.mass_qty = FND_API.G_MISS_NUM) THEN
p_recipe_detail_rec.mass_qty := NULL;
p_recipe_detail_rec.mass_qty := update_rec.mass_qty;
p_recipe_detail_rec.volume_qty := update_rec.volume_qty;
p_recipe_detail_rec.mass_std_uom := update_rec.mass_std_uom;
p_recipe_detail_rec.volume_std_uom := update_rec.volume_std_uom;
FETCH Flex_cur INTO l_flex_update_rec;
/* If no flex field is updated retain the old values */
IF (p_recipe_update_flex.count = 0) THEN
p_flex_update_rec := l_flex_update_rec;
p_flex_update_rec := p_recipe_update_flex(i);
/* as the user is intending to update the field to NULL */
IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute1 := NULL;
ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute2 := NULL;
ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute3 := NULL;
ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute4 := NULL;
ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute5 := NULL;
ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute6 := NULL;
ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute7 := NULL;
ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute8 := NULL;
ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute9 := NULL;
ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute10 := NULL;
ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute11 := NULL;
ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute12 := NULL;
ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute13 := NULL;
ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute14 := NULL;
ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute15 := NULL;
ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute16 := NULL;
ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute17 := NULL;
ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute18 := NULL;
ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute19 := NULL;
ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute20 := NULL;
ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute21 := NULL;
ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute22 := NULL;
ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute23 := NULL;
ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute24 := NULL;
ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute25 := NULL;
ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute26 := NULL;
ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute27 := NULL;
ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute28 := NULL;
ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute29 := NULL;
ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute30 := NULL;
ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute_category := NULL;
ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
,p_flex_insert_rec => p_flex_insert_rec
,p_flex_update_rec => p_flex_update_rec
,x_return_status => x_return_status);
/* inserting and updating recipe orgn activities */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Recipe_Orgn_operations */
/* Type : Public */
/* Function : */
/* Parameters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE RECIPE_ORGN_OPERATIONS
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl ,
p_recipe_insert_flex IN recipe_flex ,
p_recipe_update_flex IN recipe_update_flex
) IS
/* Define all variables specific to this procedure */
l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_OPERATIONS';
/* flex field records for inserts and updates */
p_flex_insert_rec flex;
p_flex_update_rec update_flex;
l_flex_update_rec update_flex;
/* Define a cursor for dealing with updates */
CURSOR Flex_cur(vRecipe_Id NUMBER, vRoutingstep_Id NUMBER,
vOprn_Line_Id Number, vOrgn_id NUMBER) IS
Select attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24,attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30
From gmd_recipe_orgn_activities
where recipe_id = NVL(vRecipe_id,-1) AND
RoutingStep_id = NVL(vRoutingStep_id,-1) AND
oprn_line_id = NVL(vOprn_line_id,-1) AND
organization_id = vOrgn_id;
/* Updating recipe orgn activity for forst time infact insert a new record in */
/* gmd_recipe_orgn activities table */
/* Define Savepoint */
SAVEPOINT Recipe_Orgn_Activities;
IF (p_recipe_insert_flex.count = 0) THEN
p_flex_insert_rec := NULL;
p_flex_insert_rec := p_recipe_insert_flex(i);
FETCH Flex_cur INTO l_flex_update_rec;
/* If no flex field is updated retain the old values */
IF (p_recipe_update_flex.count = 0) THEN
p_flex_update_rec := l_flex_update_rec;
p_flex_update_rec := p_recipe_update_flex(i);
/* as the user is intending to update the field to NULL */
IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute1 := NULL;
ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute2 := NULL;
ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute3 := NULL;
ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute4 := NULL;
ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute5 := NULL;
ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute6 := NULL;
ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute7 := NULL;
ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute8 := NULL;
ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute9 := NULL;
ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute10 := NULL;
ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute11 := NULL;
ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute12 := NULL;
ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute13 := NULL;
ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute14 := NULL;
ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute15 := NULL;
ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute16 := NULL;
ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute17 := NULL;
ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute18 := NULL;
ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute19 := NULL;
ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute20 := NULL;
ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute21 := NULL;
ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute22 := NULL;
ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute23 := NULL;
ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute24 := NULL;
ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute25 := NULL;
ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute26 := NULL;
ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute27 := NULL;
ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute28 := NULL;
ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute29 := NULL;
ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute30 := NULL;
ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute_category := NULL;
ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
,p_flex_insert_rec => p_flex_insert_rec
,p_flex_update_rec => p_flex_update_rec
,x_return_status => x_return_status);
/* inserting and updating recipe orgn resources */
/* */
/* ============================================= */
/* Start of commments */
/* API name : Recipe_Orgn_Resources */
/* Type : Public */
/* Function : */
/* parameters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_recipe_detail_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.0 */
/* */
/* Notes : p_called_from_forms parameter not currently used */
/* originally included for returning error messages */
/* */
/* End of comments */
PROCEDURE RECIPE_ORGN_RESOURCES
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_called_from_forms IN VARCHAR2 := 'NO' ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_recipe_detail_tbl IN recipe_detail_tbl ,
p_recipe_insert_flex IN recipe_flex ,
p_recipe_update_flex IN recipe_update_flex
) IS
/* Define all variables specific to this procedure */
l_dml_type VARCHAR2(1) := 'I';
/* flex field records for inserts and updates */
p_flex_insert_rec flex;
p_flex_update_rec update_flex;
l_flex_update_rec update_flex;
/* Define a cursor for dealing with updates */
CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
Select attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24,attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30
From gmd_recipe_orgn_resources
where recipe_id = NVL(vRecipe_id,-1) AND
RoutingStep_id = NVL(vRoutingStep_id,-1) AND
oprn_line_id = NVL(vOprn_line_id,-1) AND
resources = vResources AND
organization_id = vOrgn_id;
CURSOR update_res_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
Select min_capacity, max_capacity, process_qty, usage_uom,
resource_usage
From gmd_recipe_orgn_resources
where recipe_id = NVL(vRecipe_id,-1) AND
RoutingStep_id = NVL(vRoutingStep_id,-1) AND
oprn_line_id = NVL(vOprn_line_id,-1) AND
resources = vResources AND
organization_id = vOrgn_id;
SELECT gos.process_qty_uom
FROM gmd_operation_activities goa, gmd_operations gos
WHERE goa.oprn_line_id = vOprn_line_id
AND gos.oprn_id = goa.oprn_id;
/* Updating recipe orgn resources for forst time infact insert a new record in */
/* gmd_recipe_orgn_resources table */
/* Define Savepoint */
SAVEPOINT Recipe_Orgn_Resources;
FOR update_rec IN update_res_cur (p_recipe_detail_rec.recipe_id,
p_recipe_detail_rec.routingstep_id,
p_recipe_detail_rec.oprn_line_id,
p_recipe_detail_rec.resources,
p_recipe_detail_rec.organization_id) LOOP
IF (p_recipe_detail_rec.min_capacity = FND_API.G_MISS_NUM) THEN
p_recipe_detail_rec.min_capacity := NULL;
p_recipe_detail_rec.min_capacity := update_rec.min_capacity;
p_recipe_detail_rec.max_capacity := update_rec.max_capacity;
p_recipe_detail_rec.process_qty := update_rec.process_qty;
p_recipe_detail_rec.resource_usage := update_rec.resource_usage;
p_recipe_detail_rec.usage_uom := update_rec.usage_uom;
IF (p_recipe_insert_flex.count = 0) THEN
p_flex_insert_rec := NULL;
p_flex_insert_rec := p_recipe_insert_flex(i);
FETCH Flex_cur INTO l_flex_update_rec;
/* If no flex field is updated retain the old values */
IF (p_recipe_update_flex.count = 0) THEN
p_flex_update_rec := l_flex_update_rec;
p_flex_update_rec := p_recipe_update_flex(i);
/* as the user is intending to update the field to NULL */
IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute1 := NULL;
ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute2 := NULL;
ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute3 := NULL;
ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute4 := NULL;
ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute5 := NULL;
ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute6 := NULL;
ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute7 := NULL;
ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute8 := NULL;
ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute9 := NULL;
ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute10 := NULL;
ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute11 := NULL;
ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute12 := NULL;
ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute13 := NULL;
ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute14 := NULL;
ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute15 := NULL;
ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute16 := NULL;
ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute17 := NULL;
ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute18 := NULL;
ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute19 := NULL;
ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute20 := NULL;
ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute21 := NULL;
ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute22 := NULL;
ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute23 := NULL;
ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute24 := NULL;
ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute25 := NULL;
ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute26 := NULL;
ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute27 := NULL;
ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute28 := NULL;
ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute29 := NULL;
ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute30 := NULL;
ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
p_flex_update_rec.attribute_category := NULL;
ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
,p_flex_insert_rec => p_flex_insert_rec
,p_flex_update_rec => p_flex_update_rec
,x_return_status => x_return_status);