[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT formula_id
FROM fm_form_mst
WHERE formula_no = UPPER(pformula_no)
AND formula_vers = pversion;
/* Prior to updates the formulaline_id value */
/* should exists in the database */
/* IN formulaline_id NUMBER */
/* OUT NOCOPY return_code NUMBER */
/* ==================================================== */
PROCEDURE get_formulaline_id(pformulaline_id IN NUMBER,
xreturn_code OUT NOCOPY NUMBER) IS
/* Local variables. */
/* ================ */
l_formulaline_id fm_matl_dtl.formulaline_id%TYPE := 0;
SELECT formulaline_id
FROM fm_matl_dtl
WHERE formulaline_id = pformulaline_id;
SELECT inventory_item_id, primary_uom_code, enabled_flag
FROM mtl_system_items_kfv
WHERE concatenated_segments = pitem_no
AND organization_id = porganization_id;
SELECT inventory_item_id, primary_uom_code, enabled_flag
FROM mtl_system_items
WHERE inventory_item_id = pinventory_item_id
AND organization_id = porganization_id;
SELECT inventory_item_id
FROM fm_matl_dtl
WHERE formula_id = pformula_id
AND line_type = 1;
SELECT primary_uom_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_item_id;
/* Validates before inserting detail line into fm_matl_dtl */
/* table. */
/* If this record that has same formula_id, line_type , */
/* and line_no we cannot make an insert. */
/* In such cases we return a non zero value. */
/* If no record is found we return a zero */
/* =============================================== */
FUNCTION detail_line_val(pformula_id NUMBER,
pline_no NUMBER,
pline_type NUMBER) RETURN NUMBER IS
/* Local variables. */
/* ================ */
l_line_existing NUMBER := 0;
SELECT formula_id
FROM fm_matl_dtl
WHERE formula_id = pformula_id
AND line_type = pline_type
AND line_no = pline_no;
SELECT formula_class_desc
FROM fm_form_cls
WHERE formula_class = UPPER(pform_class)
AND delete_mark = 0;
SELECT unique(lookup_code)
FROM gem_lookups
WHERE lookup_type = ptype_name
--Begin Bug#3090630 P.Raghu
--Removed TO_CHAR function for pvalue parameter.
AND lookup_code = pvalue;
/* 900 Effectivity is locked (update not allowed) */
/* ============================================= */
FUNCTION GMD_EFFECTIVITY_LOCKED_STATUS(pfmeff_id NUMBER) RETURN VARCHAR2 IS
l_status VARCHAR2(32) := '700';
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM cm_cmpt_dtl
WHERE fmeff_id = pfmeff_id
AND rollover_ind = 1);
/* -92213 Effectivity is locked (update not allowed) */
/* < 0 error */
/* ============================================= */
FUNCTION locked_effectivity_val(pformula_id NUMBER) RETURN NUMBER IS
/* Local variables. */
/* ================ */
l_formula_id NUMBER := 0;
SELECT rcp.formula_id
FROM gmd_recipe_validity_rules vr,
gmd_recipes_b rcp,
cm_cmpt_dtl cost
WHERE rcp.formula_id = pformula_id
AND vr.recipe_validity_rule_id = cost.fmeff_id
AND vr.recipe_id = rcp.recipe_id
AND cost.rollover_ind = 1;
select h.formula_no, h.formula_vers, h.formula_id
from fm_form_mst h, fm_matl_dtl d
where d.inventory_item_id = pitem_id
AND h.formula_id = d.formula_id;
select formulaline_id
from fm_matl_dtl
where formula_id = pformula_id;
select formula_no, formula_vers
from fm_form_mst
where formula_id = pformula_id;
select formula_id
from fm_form_mst
where formula_no = UPPER(pRecord_in.formula_no)
and formula_vers = pRecord_in.formula_vers;
select user_id from fnd_user where user_name = pRecord_in.user_name;
Select formula_id
INTO l_formula_id
From gmd_recipes
Where recipe_id = pRecord_in.Recipe_id;
select *
from fm_matl_dtl
where formula_id = pformula_id
order by line_type, line_no;
SELECT formula_id
INTO l_formula_id
FROM gmd_recipes
WHERE recipe_id = pRecord_in.Recipe_id;
FOR formula_rec IN (select *
from fm_form_mst
where formula_id = l_formula_id) LOOP
xFormulaHeader_rec := formula_rec;
select *
from fm_matl_dtl
where formula_id = vformula_id
order by line_type, line_no;
SELECT * FROM fm_matl_dtl WHERE formulaline_id = vFormulaline_id;
SELECT *
FROM gmd_material_effectivities_vw vw
WHERE vw.formula_id = vFormula_id
AND vw.formulaline_id = vFormulaline_id
AND vw.line_item_id = vItem_id
AND vw.start_date <= vDate
AND (vw.end_date IS NULL OR vw.end_date >= vDate)
ORDER BY vw.preference asc, vw.start_date;
SELECT hdr.original_qty,
dtl.unit_qty,
hdr.original_uom,
dtl.detail_uom,
hdr.replacement_uom_type /* Added dtl.detail_uom 8271618*/
FROM gmd_formula_substitution fmsub,
gmd_item_substitution_hdr_b hdr,
gmd_item_substitution_dtl dtl
Where fmsub.formula_id = vFormula_id
and fmsub.substitution_id = hdr.substitution_id
and hdr.substitution_id = dtl.substitution_id
and hdr.original_inventory_item_id = vLine_Item_id
and hdr.substitution_status between 700 and 799
and fmsub.associated_flag = 'Y'
and dtl.inventory_item_id = vSubstitute_item_id
Order by hdr.preference asc, hdr.start_date;
SELECT count(*)
INTO l_subst_recs
FROM gmd_material_effectivities_vw
WHERE formula_id = get_fmline_rec.formula_id
AND formulaline_id = get_fmline_rec.formulaline_id;
SELECT count(*)
INTO l_subst_recs
FROM gmd_material_effectivities_vw
WHERE formula_id = get_fmline_rec.formula_id
AND formulaline_id = get_fmline_rec.formulaline_id;
SELECT count(*)
INTO l_subst_recs
FROM gmd_material_effectivities_vw
WHERE formula_id = get_fmline_rec.formula_id
AND formulaline_id = get_fmline_rec.formulaline_id;
SELECT fm.*
FROM gmd_formula_substitution fm
WHERE formula_id = pOldFormula_id
AND associated_flag = 'Y'
AND NOT EXISTS (SELECT 1
FROM gmd_formula_substitution
WHERE formula_id = pNewFormula_id)
AND EXISTS
(SELECT 1
FROM gmd_item_substitution_hdr_b subs
WHERE subs.substitution_id = fm.substitution_id
AND (subs.end_date IS NULL OR subs.end_date > SYSDATE)
AND (subs.substitution_status between 700 and 799 OR
subs.substitution_status between 900 and 999));
SELECT owner_organization_id
FROM gmd_item_substitution_hdr_b im
WHERE substitution_id = c_substitution_id;
SELECT gmd_formula_substitution_s.nextval
INTO x_newformsubs_id
FROM DUAL;
INSERT INTO gmd_formula_substitution
(formula_substitution_id,
substitution_id,
formula_id,
associated_flag,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date)
VALUES
(x_newformsubs_id,
x_new_substitution_id,
pNewFormula_id,
subs_rec.associated_flag,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
SYSDATE);
insert detail record.
****************************************************** */
PROCEDURE validate_insert_record(P_formula_dtl IN GMD_FORMULA_COMMON_PUB.formula_insert_rec_type,
X_formula_dtl OUT NOCOPY GMD_FORMULA_COMMON_PUB.formula_insert_rec_type,
xReturn OUT NOCOPY VARCHAR2) IS
--Cursor Declaration
CURSOR Cur_get_revision(Vitem_id NUMBER, Vorgn_id NUMBER) IS
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = Vitem_id
AND organization_id = Vorgn_id;
SELECT serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = Vitem_id
AND organization_id = Vorgn_id;
SELECT 1
FROM mtl_item_revisions
WHERE inventory_item_id = Vitem_id
AND organization_id = Vorgn_id
AND revision = V_revision;
END validate_insert_record;
update detail record.
****************************************************** */
PROCEDURE validate_update_record(P_formula_dtl IN GMD_FORMULA_COMMON_PUB.formula_update_rec_type,
X_formula_dtl OUT NOCOPY GMD_FORMULA_COMMON_PUB.formula_update_rec_type,
xReturn OUT NOCOPY VARCHAR2) IS
--Cursor Declaration
CURSOR Cur_get_revision(Vitem_id NUMBER, Vorgn_id NUMBER) IS
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = Vitem_id
AND organization_id = Vorgn_id;
SELECT 1
FROM mtl_item_revisions
WHERE inventory_item_id = Vitem_id
AND organization_id = Vorgn_id
AND revision = V_revision;
END validate_update_record;
SELECT 1
FROM SYS.DUAL
WHERE EXISTS (SELECT 1
FROM fm_matl_dtl d, mtl_system_items_b i
WHERE recipe_enabled_flag = 'Y'
AND i.ORGANIZATION_ID = p_org_id
AND d.formula_id = V_formula_id
AND i.inventory_item_id = d.inventory_item_id
AND i.eng_item_flag = 'Y');
select owner_organization_id
into l_orgid
from fm_form_mst
where formula_id = V_formula_id;
SELECT SUM(qty)
FROM fm_matl_dtl
WHERE formula_id = V_formula_id
AND line_type > 0;
SELECT 1
FROM SYS.DUAL
WHERE EXISTS (SELECT 1
FROM fm_matl_dtl d, mtl_system_items_b i
WHERE d.formula_id = V_formula_id
AND i.inventory_item_id = d.inventory_item_id
AND i.recipe_enabled_flag = 'N'
AND i.organization_id = d.organization_id); -- Bug # 5040915 Kapil M