[Home] [Help]
599: ORDER BY max_quantity;
600:
601: CURSOR get_total_qty_cur(vFormula_id NUMBER) IS
602: SELECT total_output_qty, yield_uom
603: FROM fm_form_mst_b
604: WHERE formula_id = vFormula_id;
605:
606: -- NPD Conv.
607: CURSOR Get_validity_cur (vValidity_Rule_Id NUMBER) IS
1020: l_return_status VARCHAR2(10);
1021:
1022: CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1023: SELECT owner_organization_id
1024: FROM fm_form_mst_b
1025: WHERE formula_id = vformula_id;
1026:
1027: BEGIN
1028:
1201: form.total_output_qty recipe_qty,
1202: rout.routing_uom rout_uom,
1203: form.formula_id formula_id,
1204: form.yield_uom yield_typ_uom
1205: from fm_form_mst_b form,
1206: gmd_routings_b rout
1207: where form.formula_id = pformula_id and
1208: rout.routing_id = prouting_id ;
1209:
1779: END IF;
1780: IF (Entity = 'FORMULA') THEN
1781: SELECT delete_mark, formula_status
1782: INTO l_delete_mark, l_status_code
1783: FROM fm_form_mst
1784: WHERE formula_id = Entity_id;
1785: ELSIF (Entity = 'RECIPE') THEN
1786:
1787: SELECT delete_mark, recipe_status, owner_organization_id
1859: Status_cur Status_ref_cur;
1860:
1861: CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1862: SELECT owner_organization_id
1863: FROM fm_form_mst_b
1864: WHERE formula_id = vformula_id;
1865: CURSOR get_recipe_owner_orgn_id(vrecipe_id NUMBER) IS
1866: SELECT owner_organization_id
1867: FROM gmd_recipes_b
1892: FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1893: CLOSE get_formula_owner_orgn_id;
1894: OPEN Status_cur FOR
1895: Select f.formula_status, s.version_enabled
1896: From fm_form_mst f, gmd_status s
1897: Where f.formula_id = Entity_id
1898: And f.formula_status = s.status_code;
1899: FETCH Status_cur INTO l_status, l_version_enabled;
1900: ClOSE Status_cur;
2187: FETCH Get_FmRout_id INTO l_formula_id, l_routing_id;
2188: CLOSE Get_FmRout_id;
2189:
2190: /* Update the formula and routing status */
2191: UPDATE fm_form_mst_b
2192: SET formula_status = '900'
2193: WHERE formula_id = l_formula_id
2194: AND to_number(formula_status) < 800;
2195:
2276: --RLNAGARA start Bug 6982623 Added validation for formula item access
2277:
2278: CURSOR Cur_formula_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2279: SELECT fm.formula_no, fm.formula_vers, organization_code
2280: FROM fm_form_mst fm, mtl_parameters o
2281: WHERE formula_id =p_formula_id
2282: AND fm.owner_organization_id = o.organization_id
2283: AND formula_status < 1000
2284: AND NOT EXISTS (SELECT 1
2288: AND m.organization_id = fm.owner_organization_id);
2289:
2290: CURSOR Cur_formula_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2291: SELECT fm.formula_no, fm.formula_vers, organization_code
2292: FROM fm_form_mst fm, mtl_parameters o
2293: WHERE formula_id =p_formula_id
2294: AND fm.owner_organization_id = o.organization_id
2295: AND formula_status < 1000
2296: AND NOT EXISTS (SELECT 1
2642: -- Cursor Definitions
2643:
2644: CURSOR Cur_get_formula_org IS
2645: SELECT OWNER_ORGANIZATION_ID
2646: FROM FM_FORM_MST
2647: WHERE formula_id = p_formula_id;
2648:
2649: CURSOR get_formula_lines (vformula_id NUMBER) IS
2650: SELECT line_no, line_type, inventory_item_id, qty, DETAIL_UOM, scale_type,
2884: /* Finally update the formula level product qty as prod qty + by product qty. */
2885: lhdrqty := lhdrqty + l_prod_fix_qty + NVL (l_by_product_qty, 0);
2886:
2887: -- Update formula Header also
2888: UPDATE fm_form_mst_b
2889: SET total_output_qty = lhdrqty
2890: WHERE formula_id = p_formula_id;
2891: /* Get the message count from the Message stack */
2892: END IF;
3023: FETCH Get_FmRout_id INTO l_formula_id, l_routing_id;
3024: CLOSE Get_FmRout_id;
3025:
3026: /* Update the formula and routing status */
3027: UPDATE fm_form_mst_b
3028: SET formula_status = '900'
3029: WHERE formula_id = l_formula_id
3030: AND to_number(formula_status) < 800;
3031: