[Home] [Help]
2218: --RLNAGARA start Bug 6982623 Added validation for formula item access
2219:
2220: CURSOR Cur_formula_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2221: SELECT fm.formula_no, fm.formula_vers, organization_code
2222: FROM fm_form_mst fm, mtl_parameters o
2223: WHERE formula_id =p_formula_id
2224: AND fm.owner_organization_id = o.organization_id
2225: AND formula_status < 1000
2226: AND NOT EXISTS (SELECT 1
2230: AND m.organization_id = fm.owner_organization_id);
2231:
2232: CURSOR Cur_formula_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2233: SELECT fm.formula_no, fm.formula_vers, organization_code
2234: FROM fm_form_mst fm, mtl_parameters o
2235: WHERE formula_id =p_formula_id
2236: AND fm.owner_organization_id = o.organization_id
2237: AND formula_status < 1000
2238: AND NOT EXISTS (SELECT 1
2249: -- Lists the organization id for the recipe which are not owned
2250: -- By the owner organization id of formula
2251: CURSOR Cur_recipe_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2252: SELECT recipe_no, recipe_version, organization_code
2253: FROM gmd_recipes_b r, mtl_parameters o
2254: WHERE formula_id = p_formula_id
2255: AND r.owner_organization_id = o.organization_id
2256: AND recipe_status < 1000
2257: AND NOT EXISTS (SELECT 1
2261: AND m.organization_id = r.owner_organization_id);
2262: -- Bug 5237351 added
2263: CURSOR Cur_recipe_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2264: SELECT recipe_no, recipe_version, organization_code
2265: FROM gmd_recipes_b r, mtl_parameters o
2266: WHERE formula_id = p_formula_id
2267: AND r.owner_organization_id = o.organization_id
2268: AND recipe_status < 1000
2269: AND NOT EXISTS (SELECT 1
2278:
2279: -- Cursors to get the recipe over rides for a recipe id
2280: CURSOR Cur_recipe_override(p_formula_id number, p_inventory_item_id number) IS
2281: select r.recipe_no, r.recipe_version, o.organization_code
2282: from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2283: where r.recipe_id = rpl.recipe_id
2284: AND r.formula_id = p_formula_id
2285: AND r.owner_organization_id <> rpl.organization_id
2286: AND rpl.organization_id = o.organization_id
2293:
2294: -- Bug 5237351 added
2295: CURSOR Cur_recipe_override_revision(p_formula_id number, p_inventory_item_id number,p_revision VARCHAR2) IS
2296: select r.recipe_no, r.recipe_version, o.organization_code
2297: from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2298: where r.recipe_id = rpl.recipe_id
2299: AND r.formula_id = p_formula_id
2300: AND r.owner_organization_id <> rpl.organization_id
2301: AND rpl.organization_id = o.organization_id
2311:
2312: -- Cursor to get the validity rules for the recipes
2313: CURSOR Cur_recipe_validity(p_formula_id number,p_inventory_item_id number) IS
2314: select o.organization_code,r.recipe_no, r.recipe_version
2315: from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2316: where r.recipe_id = rvr.recipe_id
2317: and r.formula_id = p_formula_id
2318: and o.organization_id = rvr.organization_id
2319: AND rvr.organization_id <> r.owner_organization_id
2326: AND m.organization_id = rvr.organization_id);
2327: -- Bug 5237351 added
2328: CURSOR Cur_recipe_validity_revision(p_formula_id number,p_inventory_item_id number,p_revision VARCHAR2) IS
2329: select o.organization_code,r.recipe_no, r.recipe_version
2330: from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2331: where r.recipe_id = rvr.recipe_id
2332: and r.formula_id = p_formula_id
2333: and o.organization_id = rvr.organization_id
2334: AND r.recipe_status < 1000