[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Update_FormulaDetail API.
| 16-APR-2003 Eddie Oumerretane. Implemented call to
| gmd_api_grp.Update_allowed_with_fmsec function in
| order to get formula user access defined in
| Formula Security.
| 30-MAR-2004 kkillams
| Modified Get_Formula_Mode procedure w.r.t. bug 3344335.
| 27-APR-2004 Sriram.S Bug# 3408799
| Added SET_DEFAULT_STATUS procedure for Default Status Build.
| 23-JUN-2004 Sriram.S Bug# 3702561
| Added validation to check for ingredient with zero qty in
| Validate_Formula_Details procedure.
| 23-JUN-2004 Sriram.S Bug# 3700829
| Added procedure CHECK_USR_HAS_FSEC_RESP to check if user has formula
| security responsibility (i-e) Product development security manager.
| 29-SEP-2004 Sriram.S Bug# 3761032
| Added validation to check for experimental items if formula status in
| in (600,700).
| 16-Dec-2005 TDaniel Bug#4771255
| Added code to handle the return status of Q in insert_formula_detail
| and update_formula_detail routines.
| 27-Jan-2006 TDaniel Bug#4720080
| Added code to pass back return code as "W" for validateCostAlloc.
| 05-FEB-2007 Kapil M Bug# 5716318
| Changes for Auto-Product Qty Calculation ME. Added the new procedure
| CALCULATE_TOTAL_PRODUCT_QTY and CHECK_AUTO_PRODUCT. Changed made to pass
| the newly added fields.
==============================================================================================
*/
/* Api start of comments
+============================================================================
| PROCEDURE NAME
| Get_Formula_Mode
|
| DESCRIPTION
| Determine whether the user has access to this formula and in which
| mode (view or update/create mode).
|
| INPUT PARAMETERS
| p_formula_id NUMBER
|
| OUTPUT PARAMETERS
| x_formula_mode VARCHAR2
| x_create_allowed VARCHAR2
| x_return_code VARCHAR2
| x_error_msg VARCHAR2
|
| HISTORY
| 05-SEP-2002 Eddie Oumerretane Created.
| 16-APR-2003 Eddie Oumerretane. Implemented call to
| gmd_api_grp.Update_allowed_with_fmsec function in
| order to get formula user access defined in
| Formula Security.
| 30-03-2004kkillamsCalling GMD_API_GRP.get_formula_access_type api to get the
| formula access type w.r.t. bug 3344335.
| 29-Jul-2005 Tdaniel Added organization_id for convergence changes.
+=============================================================================
Api end of comments
*/
PROCEDURE Get_Formula_Mode (p_formula_id IN NUMBER,
p_organization_id IN NUMBER,
x_formula_mode OUT NOCOPY VARCHAR2,
x_create_allowed OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_code VARCHAR2(1);
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = vFormula_id;
IF GMD_COMMON_VAL.Update_Allowed(entity => 'FORMULA',
entity_id => p_formula_id) THEN
x_formula_mode := 'U';
| Update_Formula_Header
|
| DESCRIPTION
| Update formula header
|
| INPUT PARAMETERS
| p_formula_id NUMBER
| p_formula_no VARCHAR2
| p_formula_vers NUMBER
| p_formula_desc VARCHAR2
| p_formula_desc2 VARCHAR2
| p_formula_status VARCHAR2
| p_formula_class VARCHAR2
| p_owner_organization_id NUMBER
| p_owner_id NUMBER
| p_formula_type NUMBER
| p_scale_type NUMBER
| p_text_code NUMBER
| p_last_update_date DATE
| p_user_id NUMBER
| p_last_update_date_orig DATE
| p_auto_product_calc VARCHAR2
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 05-SEP-2002 Eddie Oumerretane Created.
| 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
| Added the new column auto_product_calc
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Formula_Header ( p_formula_id IN NUMBER,
p_formula_no IN VARCHAR2,
p_formula_vers IN NUMBER,
p_formula_desc IN VARCHAR2,
p_formula_desc2 IN VARCHAR2,
p_formula_status IN VARCHAR2,
p_formula_class IN VARCHAR2,
p_owner_organization_id IN NUMBER,
p_owner_id IN NUMBER,
p_formula_type IN NUMBER,
p_scale_type IN NUMBER,
p_text_code IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
p_last_update_date_orig IN DATE,
p_auto_product_calc IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_formula IS
SELECT *
FROM fm_form_mst
WHERE formula_id = p_formula_id AND
last_update_date = p_last_update_date_orig;
UPDATE_FORMULA_EXCEPTION EXCEPTION;
l_update_table GMD_FORMULA_PUB.formula_update_hdr_tbl_type;
l_update_table(1).fmcontrol_class := l_rec.fmcontrol_class;
l_update_table(1).inactive_ind := l_rec.inactive_ind;
l_update_table(1).total_input_qty := l_rec.total_input_qty;
l_update_table(1).total_output_qty := l_rec.total_output_qty;
l_update_table(1).yield_uom := l_rec.yield_uom;
l_update_table(1).formula_id := p_formula_id;
l_update_table(1).formula_no := l_rec.formula_no;
l_update_table(1).formula_vers := l_rec.formula_vers;
l_update_table(1).formula_desc1 := p_formula_desc;
l_update_table(1).formula_desc2 := p_formula_desc2;
l_update_table(1).formula_status := p_formula_status;
l_update_table(1).formula_class := p_formula_class;
l_update_table(1).owner_organization_id := p_owner_organization_id;
l_update_table(1).owner_id := p_owner_id;
l_update_table(1).user_id := p_user_id;
l_update_table(1).formula_type := p_formula_type;
l_update_table(1).scale_type_hdr := p_scale_type;
l_update_table(1).text_code_hdr := l_text_code;
l_update_table(1).last_update_date := p_last_update_date;
l_update_table(1).last_updated_by := p_user_id;
l_update_table(1).last_update_login := p_user_id;
l_update_table(1).delete_mark := l_rec.delete_mark;
l_update_table(1).created_by := l_rec.created_by;
l_update_table(1).creation_date := l_rec.last_update_date;
l_update_table(1).attribute1 := l_rec.attribute1;
l_update_table(1).attribute2 := l_rec.attribute2;
l_update_table(1).attribute3 := l_rec.attribute3;
l_update_table(1).attribute4 := l_rec.attribute4;
l_update_table(1).attribute5 := l_rec.attribute5;
l_update_table(1).attribute6 := l_rec.attribute6;
l_update_table(1).attribute7 := l_rec.attribute7;
l_update_table(1).attribute8 := l_rec.attribute8;
l_update_table(1).attribute9 := l_rec.attribute9;
l_update_table(1).attribute10 := l_rec.attribute10;
l_update_table(1).attribute11 := l_rec.attribute11;
l_update_table(1).attribute12 := l_rec.attribute12;
l_update_table(1).attribute13 := l_rec.attribute13;
l_update_table(1).attribute14 := l_rec.attribute14;
l_update_table(1).attribute15 := l_rec.attribute15;
l_update_table(1).attribute16 := l_rec.attribute16;
l_update_table(1).attribute17 := l_rec.attribute17;
l_update_table(1).attribute18 := l_rec.attribute18;
l_update_table(1).attribute19 := l_rec.attribute19;
l_update_table(1).attribute20 := l_rec.attribute20;
l_update_table(1).attribute21 := l_rec.attribute21;
l_update_table(1).attribute22 := l_rec.attribute22;
l_update_table(1).attribute23 := l_rec.attribute23;
l_update_table(1).attribute24 := l_rec.attribute24;
l_update_table(1).attribute25 := l_rec.attribute25;
l_update_table(1).attribute26 := l_rec.attribute26;
l_update_table(1).attribute27 := l_rec.attribute27;
l_update_table(1).attribute28 := l_rec.attribute28;
l_update_table(1).attribute29 := l_rec.attribute29;
l_update_table(1).attribute30 := l_rec.attribute30;
l_update_table(1).attribute_category := l_rec.attribute_category;
l_update_table(1).auto_product_calc := p_auto_product_calc;
GMD_FORMULA_PUB.Update_FormulaHeader
( p_api_version => 2
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_called_from_forms => 'YES'
, x_return_status => l_return_status
, x_msg_count => l_message_count
, x_msg_data => l_msg_data
, p_formula_header_tbl => l_update_table);
RAISE UPDATE_FORMULA_EXCEPTION;
UPDATE
FM_FORM_MST_B
SET
formula_no = p_formula_no,
formula_vers = p_formula_vers
WHERE
formula_id = p_formula_id;
WHEN UPDATE_FORMULA_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Formula_Header;
| Insert_Formula_Detail
|
| DESCRIPTION
| Create a formula line
|
| INPUT PARAMETERS
| p_formula_id IN NUMBER
| p_formulaline_id IN NUMBER
| p_line_type IN NUMBER
| p_line_no IN NUMBER
| p_item_id IN NUMBER
| p_item_no IN VARCHAR2
| p_qty IN NUMBER
| p_item_um IN VARCHAR2
| p_release_type IN NUMBER
| p_scrap_factor IN NUMBER
| p_scale_type IN NUMBER
| p_cost_alloc IN NUMBER
| p_phantom_type IN NUMBER
| p_rework_type IN NUMBER
| p_text_code IN NUMBER
| p_tp_formula_id IN NUMBER
| p_iaformula_id IN NUMBER
| p_scale_uom IN VARCHAR2
| p_contribute_step_qty_ind IN VARCHAR2
| p_contribute_yield_ind IN VARCHAR2
| p_scale_multiple IN NUMBER
| p_scale_rounding_variance IN NUMBER
| p_rounding_direction IN NUMBER
| p_by_product_type IN VARCHAR2
| p_text_code IN NUMBER
| p_last_update_date IN DATE
| p_user_id IN NUMBER
| p_prod_percent IN NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 05-SEP-2002 Eddie Oumerretane Created.
| 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
| Added the new column prod_percent
|
+=============================================================================
Api end of comments
*/
PROCEDURE Insert_Formula_Detail ( p_formula_id IN NUMBER
,p_formulaline_id IN NUMBER
,p_line_type IN NUMBER
,p_line_no IN NUMBER
,p_item_id IN NUMBER
,p_item_no IN VARCHAR2
,p_revision IN VARCHAR2
,p_qty IN NUMBER
,p_item_um IN VARCHAR2
,p_release_type IN NUMBER
,p_scrap_factor IN NUMBER
,p_scale_type IN NUMBER
,p_cost_alloc IN NUMBER
,p_phantom_type IN NUMBER
,p_rework_type IN NUMBER
,p_text_code IN NUMBER
,p_tp_formula_id IN NUMBER
,p_iaformula_id IN NUMBER
,p_scale_uom IN VARCHAR2
,p_contribute_step_qty_ind IN VARCHAR2
,p_contribute_yield_ind IN VARCHAR2
,p_scale_multiple IN NUMBER
,p_scale_rounding_variance IN NUMBER
,p_rounding_direction IN NUMBER
,p_by_product_type IN VARCHAR2
,p_last_update_date IN DATE
,p_user_id IN NUMBER
,p_prod_percent IN NUMBER
,x_return_code OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_formula IS
SELECT formula_no,
formula_vers , auto_product_calc -- Kapil ME Auto-Prod
FROM fm_form_mst
WHERE formula_id = p_formula_id;
INSERT_DTL_EXCEPTION EXCEPTION;
l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
l_formula_dtl_rec(1).last_updated_by := p_user_id;
l_formula_dtl_rec(1).last_update_date := p_last_update_date;
l_formula_dtl_rec(1).creation_date := p_last_update_date;
l_formula_dtl_rec(1).last_update_login := p_user_id;
GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_message_count
, x_msg_data => l_message_list
, p_formula_detail_tbl => l_formula_dtl_rec);
RAISE INSERT_DTL_EXCEPTION;
WHEN INSERT_DTL_EXCEPTION THEN
ROLLBACK TO Add_Item;
END Insert_Formula_Detail;
| Update_Formula_Detail
|
| DESCRIPTION
| Update formula detail line
|
| INPUT PARAMETERS
| p_formula_id IN NUMBER
| p_formulaline_id IN NUMBER
| p_line_type IN NUMBER
| p_line_no IN NUMBER
| p_item_id IN NUMBER
| p_item_no IN VARCHAR2
| p_qty IN NUMBER
| p_item_um IN VARCHAR2
| p_release_type IN NUMBER
| p_scrap_factor IN NUMBER
| p_scale_type IN NUMBER
| p_cost_alloc IN NUMBER
| p_phantom_type IN NUMBER
| p_rework_type IN NUMBER
| p_text_code IN NUMBER
| p_tp_formula_id IN NUMBER
| p_iaformula_id IN NUMBER
| p_scale_uom IN VARCHAR2
| p_contribute_step_qty_ind IN VARCHAR2
| p_contribute_yield_ind IN VARCHAR2
| p_scale_multiple IN NUMBER
| p_scale_rounding_variance IN NUMBER
| p_rounding_direction IN NUMBER
| p_by_product_type IN VARCHAR2
| p_text_code IN NUMBER
| p_last_update_date IN DATE
| p_user_id IN NUMBER
| p_text_code IN NUMBER
| p_last_update_date IN DATE
| p_user_id IN NUMBER
| p_last_update_date_orig IN DATE
| p_prod_percent IN NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 05-SEP-2002 Eddie Oumerretane Created.
| 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
| Added the new column prod_percent
|
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Formula_Detail ( p_formula_id IN NUMBER
,p_formulaline_id IN NUMBER
,p_line_type IN NUMBER
,p_line_no IN NUMBER
,p_item_id IN NUMBER
,p_item_no IN VARCHAR2
,p_revision IN VARCHAR2
,p_qty IN NUMBER
,p_item_um IN VARCHAR2
,p_release_type IN NUMBER
,p_scrap_factor IN NUMBER
,p_scale_type IN NUMBER
,p_cost_alloc IN NUMBER
,p_phantom_type IN NUMBER
,p_rework_type IN NUMBER
,p_text_code IN NUMBER
,p_tp_formula_id IN NUMBER
,p_iaformula_id IN NUMBER
,p_scale_uom IN VARCHAR2
,p_contribute_step_qty_ind IN VARCHAR2
,p_contribute_yield_ind IN VARCHAR2
,p_scale_multiple IN NUMBER
,p_scale_rounding_variance IN NUMBER
,p_rounding_direction IN NUMBER
,p_by_product_type IN VARCHAR2
,p_last_update_date IN DATE
,p_user_id IN NUMBER
,p_last_update_date_orig IN DATE
,p_prod_percent IN NUMBER
,x_return_code OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2) IS
l_text_code NUMBER(10);
l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
UPDATE_DTL_EXCEPTION EXCEPTION;
SELECT formula_no,
formula_vers, auto_product_calc -- Kapil ME Auto-Prod
FROM fm_form_mst
WHERE formula_id = p_formula_id;
SELECT *
FROM fm_matl_dtl
WHERE formulaline_id = p_formulaline_id AND
last_update_date = p_last_update_date_orig;
IF p_last_update_date_orig IS NOT NULL THEN
OPEN Cur_get_line;
l_formula_dtl_rec(1).last_updated_by := p_user_id;
l_formula_dtl_rec(1).last_update_date := p_last_update_date;
l_formula_dtl_rec(1).creation_date := p_last_update_date;
l_formula_dtl_rec(1).last_update_login := p_user_id;
update fm_matl_dtl
set prod_percent = 0
where formula_id = p_formula_id
and formulaline_id <> p_formulaline_id
and line_type = 1
and scale_type <> 0
and prod_percent IS NULL ;
GMD_FORMULA_DETAIL_PUB.Update_FormulaDetail
( p_api_version => 2
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_called_from_forms => 'YES'
, x_return_status => l_return_status
, x_msg_count => l_message_count
, x_msg_data => l_message_list
, p_formula_detail_tbl => l_formula_dtl_rec);
RAISE UPDATE_DTL_EXCEPTION;
WHEN UPDATE_DTL_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Formula_Detail;
p_last_update_date IN DATE,
p_auto_product_calc IN VARCHAR2,
x_formula_id OUT NOCOPY NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(5);
INSERT_FORMULA_EXCEPTION EXCEPTION;
l_fm_form_mst_rec.delete_mark := 0;
l_fm_form_mst_rec.creation_date := p_last_update_date;
l_fm_form_mst_rec.last_update_date := p_last_update_date;
l_fm_form_mst_rec.last_update_login := g_login_id;
l_fm_form_mst_rec.last_updated_by := g_created_by;
GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_message_count
, x_msg_data => l_msg_data
, p_formula_header_rec => l_fm_form_mst_rec
);
RAISE INSERT_FORMULA_EXCEPTION;
WHEN INSERT_FORMULA_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
| Delete_Formula_Detail
|
| DESCRIPTION
| Delete a formula line
|
| INPUT PARAMETERS
| p_formula_id NUMBER
| p_formulaline_id NUMBER
| p_line_type NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 05-SEP-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Formula_Detail(p_formula_id IN NUMBER,
p_formulaline_id IN NUMBER,
p_line_type IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_dtl IS
SELECT formulaline_id
FROM fm_matl_dtl
WHERE formula_id = p_formula_id AND
formulaline_id = p_formulaline_id AND
last_update_date = p_last_update_date;
l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
DELETE_LINE_EXCEPTION EXCEPTION;
GMD_FORMULA_DETAIL_PUB.Delete_FormulaDetail
( p_api_version => 1.1
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_message_count
, x_msg_data => l_message_list
, p_formula_detail_tbl => l_formula_dtl_rec);
RAISE DELETE_LINE_EXCEPTION;
WHEN DELETE_LINE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Delete_Formula_Detail;
| Delete a formula line without performing any validations
|
| INPUT PARAMETERS
| p_formula_id NUMBER
| p_formulaline_id NUMBER
| p_line_type NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 26-SEP-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Del_Formula_Detail_With_No_Val(p_formula_id IN NUMBER,
p_formulaline_id IN NUMBER,
p_line_type IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_dtl IS
SELECT formulaline_id
FROM fm_matl_dtl
WHERE formula_id = p_formula_id AND
formulaline_id = p_formulaline_id AND
last_update_date = p_last_update_date;
l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
DELETE_LINE_EXCEPTION EXCEPTION;
DELETE FROM
fm_matl_dtl
WHERE
formulaline_id = p_formulaline_id;
SELECT SUM(cost_alloc)
FROM fm_matl_dtl
WHERE line_type = 1
AND formula_id = p_formula_id
AND formulaline_id <> p_formulaline_id;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM mtl_units_of_measure
WHERE uom_code = p_item_uom);
| Cascade_Update_Recipes
|
| DESCRIPTION
| Update all recipes impacted by the deletion of an item in the formula
| or a step in the routing. Check_Item_Used_In_Recipe must be called
| prior to invoking this procedure.
|
| INPUT PARAMETERS
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2
| x_warning_message VARCHAR2
| x_error_msg VARCHAR2
|
| HISTORY
| 20-SEP-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Cascade_Update_Recipes(x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_calculatable_rec GMD_AUTO_STEP_CALC.CALCULATABLE_REC_TYPE;
UPDATE_RECIPE_EXCEPTION EXCEPTION;
g_calculatable_rec.last_updated_by := g_created_by;
g_calculatable_rec.last_update_login := g_login_id;
g_calculatable_rec.last_update_date := l_date;
RAISE UPDATE_RECIPE_EXCEPTION;
WHEN UPDATE_RECIPE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Cascade_Update_Recipes;
SELECT
line_no,
line_type,
inventory_item_id, -- NPD Conv.
qty,
detail_uom, -- NPD Conv.
scale_type,
contribute_yield_ind,
scale_multiple,
scale_rounding_variance,
rounding_direction
FROM
fm_matl_dtl
WHERE
formula_id = p_formula_id;
SELECT owner_organization_id
FROM fm_form_mst
WHERE formula_id = vformula_id;
SELECT
line_no,
line_type,
inventory_item_id, -- NPD Conv.
qty,
detail_uom, -- NPD Conv.
scale_type,
contribute_yield_ind,
scale_multiple,
scale_rounding_variance,
rounding_direction
FROM
fm_matl_dtl
WHERE
formula_id = p_formula_id;
SELECT owner_organization_id
FROM fm_form_mst
WHERE formula_id = vformula_id;
SELECT 1
FROM fm_matl_dtl
WHERE formula_id = p_formula_id AND
line_type = p_line_type;
SELECT 1
FROM fm_matl_dtl
WHERE formula_id = p_formula_id AND
qty = 0 AND
line_type = l_line_type;
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = l_formula_id;
SELECT 1
FROM FND_USER_RESP_GROUPS rg ,
FND_RESPONSIBILITY rs
WHERE rg.user_id = fnd_global.user_id
AND rg.responsibility_id = rs.responsibility_id
AND rs.responsibility_key = 'GMD_PD_SECURITY_MGR'
AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE)
AND SYSDATE BETWEEN rs.start_date AND NVL(rs.end_date, SYSDATE);
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = p_formula_id;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM mtl_item_revisions
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND revision = p_item_revision);