[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
FROM gmd_status
WHERE status_code = P_status_code;
FOR C_status_code IN (Select formula_status from fm_form_mst_b
where formula_id = vFormula_id) LOOP
OPEN get_status_meaning(C_status_code.formula_status);
/* Insert_FormulaDetail */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for inserting a formula. */
/* ======================================================================== */
/* ======================================================================== */
/* Start of commments */
/* API name : Insert_Formula */
/* Type : Public */
/* Function : */
/* Paramaters : */
/* IN : p_api_version IN NUMBER Required */
/* p_init_msg_list IN Varchar2 Optional */
/* p_commit IN Varchar2 Optional */
/* p_called_from_forms IN VARCHAR2 DEFAULT 'NO' */
/* p_formula_header_tbl_type IN Required */
/* BUG#2868184 p_allow_zero_ing_qty IN VARCHAR2 DEFAULT 'FALSE' */
/* */
/* OUT x_return_status OUT varchar2(1) */
/* x_msg_count OUT Number */
/* x_msg_data OUT varchar2(2000) */
/* */
/* Version : Current Version 1.0 */
/* */
/* Notes : */
/* */
/* History: */
/* V. Ajay Kumar 08/25/2003 BUG#2930523 Added code such that a message */
/* is displayed if the user tries to create an */
/* exisiting formula/version. */
/* Jeff Baird 09/26/2003 Bug #3119000 Changed values returned */
/* kkillams 23-03-2004 Added call to modify_status to set formula */
/* status to default status if default status is*/
/* defined organization level w.r.t. bug 3408799*/
/* G Kelly 10-MAY-2004 Bug# 3604554 Added functionality for Recipe */
/* Generation to the procedure after modify_status */
/* Kapil ME 05-FEB-2007 Bug# 5716318- Added the new Auto_product_calc*/
/* fields for Auto -Product Qty ME */
/* Michael Tou 21-Sep-2011 FP Bug No.6658328 */
/* Added code to re-initialize the table l_formula_detail_tbl */
/* End of comments */
/* ======================================================================== */
PROCEDURE Insert_Formula
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,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_formula_header_tbl IN FORMULA_INSERT_HDR_TBL_TYPE
,p_allow_zero_ing_qty IN VARCHAR2 := 'FALSE'
)
IS
/* Local Variables definitions */
--BEGIN BUG#2868184
--Created a new variables to hold the profile value and Flag.
l_profile NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULA';
p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
l_formula_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
SELECT formula_id
FROM fm_form_mst
WHERE formula_id = vFormula_id;
SAVEPOINT Insert_FormulaHeader_PUB;
/* for any record even if header exists we may need to insert its */
/* detail lines and associted effectivity. */
l_header_exists_flag := 'N';
/* product and ingredient while inserting a formula header. */
/* While looping thro each record if we come across a byproduct (line_type =2) */
/* we set the line counter = 2 and for ingredient or product (line_type = 1 or -1) */
/* we set this counter = 1 */
l_line_type_counter := 0;
/* and do validate before inserting the header info */
l_header_exists_flag := 'Y';
SELECT segment1,recipe_enabled_flag INTO v_item_no, v_recipe_enabled
FROM mtl_system_items_b
WHERE (inventory_item_id = NVL(p_formula_header_tbl(j).inventory_item_id, -9999) OR
segment1 = p_formula_header_tbl(j).item_no) AND
organization_id = p_formula_header_tbl(j).owner_organization_id;
ROLLBACK to Insert_FormulaHeader_PUB;
/* Only in Inserts */
/* To insert a header it should have formula description */
/* ===================================================== */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' In Formula Header Pub - '
||'Before validation of formula desc '
||p_formula_header_rec.formula_desc1
||' - '
||x_return_status);
/* Call the private API to insert header information */
/* ================================================== */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' In Formula Header Pub - '
||'About to assign values before calling pvt API '
||' - '
||x_return_status);
l_fm_form_mst_rec.delete_mark := p_formula_header_rec.delete_mark;
l_fm_form_mst_rec.last_update_date := NVL(p_formula_header_rec.last_update_date, SYSDATE);
l_fm_form_mst_rec.last_update_login := NVL(p_formula_header_rec.last_update_login, l_user_id);
l_fm_form_mst_rec.last_updated_by := l_user_id; -- 4603060
GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
( p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_formula_header_rec => l_fm_form_mst_rec
);
END IF; /* end after formula header insert */
/* Formula/Formula's are either rolled back or inserted with warning */
IF l_flag = 'F' AND l_formula_detail_tbl(1).line_type = -1 AND l_formula_detail_tbl(1).qty = 0 THEN
FND_MESSAGE.SET_NAME('GMD','GMD_ZERO_INGREDIENT_QTY');
/* Based on return codes we need to insert formula details too */
/* If header inserts had failed for some reason either during */
/* validation or insertion we do not load fomula detail information. */
/* Create formulalines only if the header is succesfully created */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' In Formula Header Pub - '
||'About to call the Formula line Pub API '
||' - '
||x_return_status);
SELECT fm.formula_id
INTO l_temp_fm_id
FROM fm_form_mst fm
WHERE fm.formula_no = p_formula_header_rec.formula_no
AND fm.formula_vers = p_formula_header_rec.formula_vers;
GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_called_from_forms => p_called_from_forms,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_formula_detail_tbl => l_formula_detail_tbl);
l_formula_detail_tbl.delete; /* Added this statement in FP Bug No.6658328 */
rather than trying to insert other header / lines */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
ROLLBACK to Insert_FormulaHeader_PUB;
ROLLBACK to Insert_FormulaHeader_PUB;
ROLLBACK to Insert_FormulaHeader_PUB;
ROLLBACK to Insert_FormulaHeader_PUB;
END Insert_Formula;
/* Update_FormulaHeader */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for updating a formula. */
/* HISTORY : */
/* Kapil M Bug# 5716318 - Changes for Auto -Product Qty Calculation ME */
/* ======================================================================== */
PROCEDURE Update_FormulaHeader
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,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_formula_header_tbl IN FORMULA_UPDATE_HDR_TBL_TYPE
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAHEADER';
p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
l_dbdelete_mark FM_FORM_MST.DELETE_MARK%TYPE;
l_update_chk_col VARCHAR2(30);
SELECT * from fm_form_mst
WHERE formula_id = vFormula_id;
Select *
From fm_form_mst
Where formula_id = vFormula_id;
SAVEPOINT Update_FormulaHeader_PUB;
/* For updates we must */
/* have a formula id */
/* ==================== */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' Before formula validation - '||x_return_status);
/* is not provided, update it with what */
/* exists in the db */
/* ==================================== */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' Assigning all not nulls from db - '||x_return_status);
FOR update_not_null_rec IN get_formula_in_db(l_formula_id)
LOOP
IF (p_formula_header_rec.formula_no IS NULL) THEN
p_formula_header_rec.formula_no := update_not_null_rec.formula_no;
p_formula_header_rec.formula_vers := update_not_null_rec.formula_vers;
p_formula_header_rec.formula_desc1 := update_not_null_rec.formula_desc1;
p_formula_header_rec.owner_organization_id := update_not_null_rec.owner_organization_id;
p_formula_header_rec.owner_id := update_not_null_rec.owner_id;
:= update_not_null_rec.formula_status;
:= update_not_null_rec.formula_type;
:= update_not_null_rec.scale_type;
:= update_not_null_rec.inactive_ind;
IF (p_formula_header_rec.delete_mark IS NULL) THEN
p_formula_header_rec.delete_mark
:= update_not_null_rec.delete_mark;
:= update_not_null_rec.created_by;
:= update_not_null_rec.creation_date;
p_formula_header_rec.last_updated_by
:= l_user_id;
IF (p_formula_header_rec.last_update_date IS NULL) THEN
p_formula_header_rec.last_update_date
:= SYSDATE;
l_dbdelete_mark := update_not_null_rec.delete_mark;
:= update_not_null_rec.auto_product_calc;
/* Update of the Flag is prevented form the API as the user cannot specify the Percentages. */
IF NVL(UPPER(p_called_from_forms),'NO') <> 'YES' THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_AUTO_FLAG');
/* Check if update is allowed */
IF NVL( l_lastformula_id, -1) <> l_formula_id THEN -- Do this check if the formulaid is different from the one last validated
IF (l_dbdelete_mark = 1 AND p_formula_header_rec.delete_mark = 0) THEN
l_update_chk_col := 'DELETE_MARK';
l_update_chk_col := NULL;
--Check whether record is update allowed or not
IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id, l_update_chk_col) THEN
FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
/* as the user is intending to update the field to NULL */
/* Validate all optional parameters passed */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' Before G-MISS validation - '
||p_formula_header_rec.formula_id
||' - '
||x_return_status);
/* Call the private API to update the header info */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
l_fm_form_mst_rec.formula_id := l_formula_id;
l_fm_form_mst_rec.delete_mark := p_formula_header_rec.delete_mark; /* Important */
l_fm_form_mst_rec.last_update_date := p_formula_header_rec.last_update_date;
l_fm_form_mst_rec.last_update_login := p_formula_header_rec.last_update_login;
l_fm_form_mst_rec.last_updated_by := p_formula_header_rec.last_updated_by;
gmd_debug.put_line(' Before calling the private fm update API - '
||x_return_status);
GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
( p_api_version => 1.0
,p_init_msg_list => p_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_formula_header_rec => l_fm_form_mst_rec
);
gmd_debug.put_line('After the private fm update API - '
||x_return_status);
l_lastformula_id := l_formula_id; -- Assign the last formula header updated.
update FM_MATL_DTL
SET PROD_PERCENT = NULL
where formula_id = l_formula_id
and line_type = 1
and scale_type = 1;
UPDATE FM_MATL_DTL
SET SCALE_TYPE = 0
WHERE formula_id = l_formula_id;
END IF; /* end after update of header */
rather than trying to update other header details */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
ROLLBACK to Update_FormulaHeader_PUB;
ROLLBACK to Update_FormulaHeader_PUB;
ROLLBACK to Update_FormulaHeader_PUB;
END Update_FormulaHeader;
/* Delete_FormulaHeader */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for deleting a formula. */
/* ======================================================================== */
PROCEDURE Delete_FormulaHeader
( 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_formula_header_tbl IN FORMULA_UPDATE_HDR_TBL_TYPE
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMULAHEADER';
p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
SELECT *
FROM fm_form_mst
WHERE formula_id = vFormula_id;
SAVEPOINT Delete_FormulaHeader_PUB;
/* delete a header */
/* ======================= */
IF (p_formula_header_rec.formula_id is NULL) THEN
GMDFMVAL_PUB.get_formula_id(p_formula_header_rec.formula_no,
p_formula_header_rec.formula_vers,
l_formula_id, l_return_val);
IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
/* Call the private API to update the header info */
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
FOR l_formula_rec IN get_fm_db_rec(l_formula_id) LOOP
l_fm_form_mst_rec.formula_id := l_formula_id;
l_fm_form_mst_rec.delete_mark := 1; /* Important */
l_fm_form_mst_rec.last_update_date := l_formula_rec.last_update_date;
l_fm_form_mst_rec.last_update_login := l_formula_rec.last_update_login;
l_fm_form_mst_rec.last_updated_by := l_formula_rec.last_updated_by;
GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
( p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_formula_header_rec => l_fm_form_mst_rec
);
rather than trying to delete other header details */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
ROLLBACK to Delete_FormulaHeader_PUB;
ROLLBACK to Delete_FormulaHeader_PUB;
ROLLBACK to Delete_FormulaHeader_PUB;
END Delete_FormulaHeader;