[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 detail. */
/* HISTORY: */
/* 10-Apr-2003 P.Raghu Bug#2893682 Modified the code such that */
/* p_formula_detail_rec.item_no is correctly set to */
/* ITEM_NO TOKEN. Uncommented the assigment statement*/
/* of GMDFMVAL_PUB.p_called_from_forms package */
/* variable in Insert_FormulaDetail procedure. */
/* 18-Apr-2003 J. Baird Bug #2908311 Uncommented initialization of */
/* x_return_status */
/* 18-Apr-2003 J. Baird Bug #2906124 Was not setting the TO_UOM token. */
/* ======================================================================== */
PROCEDURE Insert_FormulaDetail
( 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_detail_tbl IN formula_insert_dtl_tbl_type
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULADETAIL';
p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
X_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = V_formula_id;
SELECT AUTO_PRODUCT_CALC
FROM FM_FORM_MST_B
WHERE FORMULA_ID = V_formula_id;
SAVEPOINT Insert_FormulaDetail;
/* 2. Call the private API that does the database inserts/ updates */
IF (p_formula_detail_tbl.count = 0) THEN
RAISE FND_API.G_EXC_ERROR;
SELECT segment1,recipe_enabled_flag INTO v_item_no, v_recipe_enabled
FROM mtl_system_items_b
WHERE inventory_item_id = p_formula_detail_rec.inventory_item_id AND
organization_id = p_formula_detail_rec.owner_organization_id;
ROLLBACK to Insert_FormulaDetail;
SELECT nvl(max(line_no),0)+1 INTO new_line_no FROM fm_matl_dtl
WHERE formula_id = l_formula_id AND
line_type = p_formula_detail_rec.line_type;
GMDFMVAL_PUB.validate_insert_record (P_formula_dtl => P_formula_detail_rec,
X_formula_dtl => X_formula_detail_rec,
xReturn => X_return_status);
l_fm_matl_dtl_rec.last_update_date := NVL(p_formula_detail_rec.last_update_date, SYSDATE);
l_fm_matl_dtl_rec.last_update_login := NVL(p_formula_detail_rec.last_update_login, l_user_id);-- Bug No.6672176 l_user_id; -- Bug 4603060
l_fm_matl_dtl_rec.last_updated_by := l_user_id; -- Bug 4603060
GMD_FORMULA_DETAIL_PVT.Insert_FormulaDetail
( 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_detail_rec => l_fm_matl_dtl_rec
);
/* Product Qty Calculation after Inserting a Record */
IF l_formula_calc_flag = 'Y' THEN
GMD_COMMON_VAL.Calculate_Total_Product_Qty( p_formula_id =>l_formula_id ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
rather than trying to insert other lines */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
END LOOP; -- for number of lines to be inserted
ROLLBACK to Insert_FormulaDetail;
ROLLBACK to Insert_FormulaDetail;
ROLLBACK to Insert_FormulaDetail;
END Insert_FormulaDetail;
/* Update_FormulaDetail */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for updating a formula. */
/* details. */
/* HISTORY: */
/* 10-Apr-2003 P.Raghu Bug#2893682 Modified the code such that */
/* p_formula_detail_rec.item_no is correctly set */
/* to ITEM_NO TOKEN. */
/* 07-MAR-2006 Kapil M Bug#4603056 Added the check for update of revision*/
/* of non-revision controlled item */
/* ======================================================================== */
PROCEDURE Update_FormulaDetail
( 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_detail_tbl IN formula_update_dtl_tbl_type
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULADETAIL';
p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
X_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
SELECT * from fm_matl_dtl
WHERE formulaline_id = vFormulaline_id;
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = V_formula_id;
SELECT inventory_item_id
FROM fm_matl_dtl
WHERE formulaline_id = V_formulaline_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = V_item_id;
SELECT AUTO_PRODUCT_CALC
FROM FM_FORM_MST_B
WHERE FORMULA_ID = V_formula_id;
SAVEPOINT Update_FormulaDetail;
/* 2. Call the private API that does the database updates */
IF (p_formula_detail_tbl.count = 0) THEN
RAISE FND_API.G_EXC_ERROR;
gmd_debug.put_line(' In Formula Detail Update Pub - Entering loop with row # '||i);
/* Check if update is allowed */
IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_COL_UPDATES');
/* (fm_matl_dtl). If any field value is not provided, update it */
/* with what exists in the db */
/* ================================================================= */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' In Formula Detail Pub - '
||' Retrieving all not null columns '
||' for formula line id = '
||p_formula_detail_rec.formulaline_id
||' - '
||x_return_status);
p_formula_detail_rec.last_updated_by := l_user_id;
IF (p_formula_detail_rec.last_update_date IS NULL) THEN
p_formula_detail_rec.last_update_date := SYSDATE;
IF (p_formula_detail_rec.last_update_login IS NULL) THEN
p_formula_detail_rec.last_update_login := fmline_not_null.last_update_login;
GMDFMVAL_PUB.validate_update_record (P_formula_dtl => P_formula_detail_rec,
X_formula_dtl => X_formula_detail_rec,
xReturn => X_return_status);
/* as the user is intending to update the field to NULL */
IF (get_detail_rec%FOUND) THEN
/*Bug 2509076 - Thomas Daniel */
/* QM Integration */
IF (p_formula_detail_rec.by_product_type = FND_API.G_MISS_CHAR) THEN
l_by_product_type := NULL;
l_fm_matl_dtl_rec.last_update_date := p_formula_detail_rec.last_update_date;
l_fm_matl_dtl_rec.last_update_login := p_formula_detail_rec.last_update_login;
l_fm_matl_dtl_rec.last_updated_by := p_formula_detail_rec.last_updated_by;
GMD_FORMULA_DETAIL_PVT.Update_FormulaDetail
( 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_detail_rec => l_fm_matl_dtl_rec
);
||' After Update Pvt API call '
||' - '
||x_return_status);
/* IF update of a line fails - Raise an exception
rather than trying to insert other lines */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
END LOOP; -- End of main update loop
ROLLBACK to Update_FormulaDetail;
ROLLBACK to Update_FormulaDetail;
ROLLBACK to Update_FormulaDetail;
END Update_FormulaDetail;
/* Delete_FormulaDetail */
/* */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* delete a formula detail. */
/* */
/* HISTORY */
/* 06-Nov-2001 M. Grosser BUGS 1922679, 1981755 - Modified procedure Delete_FormulaDetail */
/* to not allow the deletion of a product with a valid */
/* validity rule against it and to not delete the only */
/* ingredient or product in a formula */
/* ============================================= */
PROCEDURE Delete_FormulaDetail
( 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_detail_tbl IN formula_update_dtl_tbl_type
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMULADETAIL';
p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
SELECT count(*)
FROM fm_matl_dtl
WHERE formula_id = pformula_id
AND line_type = pline_type;
SELECT formula_id
FROM fm_matl_dtl
WHERE formulaline_id = vFormulaLine_id;
SELECT 1
FROM gmd_recipes_b rcp,
gmd_recipe_validity_rules vr,
mtl_system_items_vl it
WHERE vr.delete_mark = 0
AND vr.validity_rule_status < 1000
AND (vr.end_date IS NULL OR vr.end_date >= SYSDATE)
AND it.concatenated_segments = pitem_no
AND vr.inventory_item_id = it.inventory_item_id
AND vr.recipe_id = rcp.recipe_id
AND rcp.formula_id = pformula_id;
SELECT OWNER_ORGANIZATION_ID
FROM FM_FORM_MST_B
WHERE FORMULA_ID = V_formula_id;
SELECT AUTO_PRODUCT_CALC
FROM FM_FORM_MST_B
WHERE FORMULA_ID = V_formula_id;
SAVEPOINT Delete_FormulaDetail;
/* 2. Call the private API that does the database inserts/ updates */
IF (p_formula_detail_tbl.count = 0) THEN
RAISE FND_API.G_EXC_ERROR;
IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
/* If there s only 1 ingredient or product, stop the delete */
IF (l_count < 2) THEN
IF p_formula_detail_rec.line_type = 1 THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_PRODUCT');
/* If there are valid validity rules, stop the delete */
IF (check_validity_rules%FOUND) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_VALID_VALIDITY');
GMD_FORMULA_DETAIL_PVT.Delete_FormulaDetail
( 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_detail_rec => l_fm_matl_dtl_rec
);
/* IF delete of a line fails - Raise an exception
rather than trying to deleting other lines */
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
END LOOP; -- End of main delete loop
ROLLBACK to Delete_FormulaDetail;
ROLLBACK to Delete_FormulaDetail;
ROLLBACK to Delete_FormulaDetail;
END Delete_FormulaDetail;