[Home] [Help]
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
2307: -- Lists the organization id for the recipe which are not owned
2308: -- By the owner organization id of formula
2309: CURSOR Cur_recipe_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2310: SELECT recipe_no, recipe_version, organization_code
2311: FROM gmd_recipes_b r, mtl_parameters o
2312: WHERE formula_id = p_formula_id
2313: AND r.owner_organization_id = o.organization_id
2314: AND recipe_status < 1000
2315: AND NOT EXISTS (SELECT 1
2319: AND m.organization_id = r.owner_organization_id);
2320: -- Bug 5237351 added
2321: CURSOR Cur_recipe_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2322: SELECT recipe_no, recipe_version, organization_code
2323: FROM gmd_recipes_b r, mtl_parameters o
2324: WHERE formula_id = p_formula_id
2325: AND r.owner_organization_id = o.organization_id
2326: AND recipe_status < 1000
2327: AND NOT EXISTS (SELECT 1
2336:
2337: -- Cursors to get the recipe over rides for a recipe id
2338: CURSOR Cur_recipe_override(p_formula_id number, p_inventory_item_id number) IS
2339: select r.recipe_no, r.recipe_version, o.organization_code
2340: from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2341: where r.recipe_id = rpl.recipe_id
2342: AND r.formula_id = p_formula_id
2343: AND r.owner_organization_id <> rpl.organization_id
2344: AND rpl.organization_id = o.organization_id
2351:
2352: -- Bug 5237351 added
2353: CURSOR Cur_recipe_override_revision(p_formula_id number, p_inventory_item_id number,p_revision VARCHAR2) IS
2354: select r.recipe_no, r.recipe_version, o.organization_code
2355: from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2356: where r.recipe_id = rpl.recipe_id
2357: AND r.formula_id = p_formula_id
2358: AND r.owner_organization_id <> rpl.organization_id
2359: AND rpl.organization_id = o.organization_id
2369:
2370: -- Cursor to get the validity rules for the recipes
2371: CURSOR Cur_recipe_validity(p_formula_id number,p_inventory_item_id number) IS
2372: select o.organization_code,r.recipe_no, r.recipe_version
2373: from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2374: where r.recipe_id = rvr.recipe_id
2375: and r.formula_id = p_formula_id
2376: and o.organization_id = rvr.organization_id
2377: AND rvr.organization_id <> r.owner_organization_id
2384: AND m.organization_id = rvr.organization_id);
2385: -- Bug 5237351 added
2386: CURSOR Cur_recipe_validity_revision(p_formula_id number,p_inventory_item_id number,p_revision VARCHAR2) IS
2387: select o.organization_code,r.recipe_no, r.recipe_version
2388: from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2389: where r.recipe_id = rvr.recipe_id
2390: and r.formula_id = p_formula_id
2391: and o.organization_id = rvr.organization_id
2392: AND r.recipe_status < 1000