[Home] [Help]
605:
606: -- NPD Conv.
607: CURSOR Get_validity_cur (vValidity_Rule_Id NUMBER) IS
608: SELECT r.recipe_id, formula_id, routing_id, v.std_qty, v.organization_id, v.inventory_item_id, v.detail_uom
609: FROM gmd_recipes_b r, gmd_recipe_validity_rules v
610: WHERE r.recipe_id = v.recipe_id
611: AND v.recipe_validity_rule_id = vValidity_Rule_Id;
612:
613: CURSOR Get_recipe_orgn_loss (vRecipe_id NUMBER, vOrgn_code_id VARCHAR2) IS
1192: x_Routing_Scale_factor NUMBER := 1 ;
1193:
1194: CURSOR Cur_get_recipe IS
1195: SELECT formula_id, routing_id, calculate_step_quantity
1196: FROM gmd_recipes_b
1197: WHERE recipe_id = vRecipe_Id;
1198:
1199: Cursor get_routrecipe_qty(pFormula_Id NUMBER, pRouting_Id NUMBER) IS
1200: Select rout.routing_qty rout_qty,
1761: l_status_code GMD_STATUS.Status_Code%TYPE;
1762: l_delete_mark NUMBER := 0;
1763:
1764: l_resp_id NUMBER(15) DEFAULT FND_PROFILE.VALUE('RESP_ID');
1765: l_owner_orgn_id GMD_RECIPES_B.owner_organization_id%TYPE;
1766: l_dummy NUMBER := 0;
1767:
1768: Cursor Check_recipe_orgn_access(vresp_id NUMBER, vOwner_orgn_id NUMBER) IS
1769: SELECT 1
1785: ELSIF (Entity = 'RECIPE') THEN
1786:
1787: SELECT delete_mark, recipe_status, owner_organization_id
1788: INTO l_delete_mark, l_status_code, l_owner_orgn_id
1789: FROM gmd_recipes_b
1790: WHERE recipe_id = Entity_id;
1791:
1792: /* Check if user has access to this Recipe orgn */
1793: OPEN Check_recipe_orgn_access(l_resp_id, l_Owner_orgn_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
1868: WHERE recipe_id = vrecipe_id;
1869: CURSOR get_routing_owner_orgn_id(vrouting_id NUMBER) IS
1870: SELECT owner_organization_id
1871: FROM gmd_routings_b
2133: SELECT recipe_id from gmd_recipe_validity_rules
2134: WHERE recipe_validity_rule_id = vValidity_Rule_id;
2135:
2136: CURSOR Get_FmRout_id(vRecipe_id NUMBER) IS
2137: SELECT formula_id, routing_id From gmd_recipes_b
2138: WHERE recipe_id = vRecipe_id;
2139:
2140: Standard_costing_exception EXCEPTION;
2141: Period_status_exception EXCEPTION;
2177: FETCH Get_Recipe_id INTO l_Recipe_id;
2178: CLOSE Get_Recipe_id;
2179:
2180: /* Update the Recipe - status field */
2181: UPDATE gmd_recipes_b
2182: SET recipe_status = '900'
2183: WHERE recipe_id = l_Recipe_Id
2184: AND to_number(recipe_status) < 800;
2185:
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
2960: SELECT recipe_id from gmd_recipe_validity_rules
2961: WHERE recipe_validity_rule_id = vValidity_Rule_id;
2962:
2963: CURSOR Get_FmRout_id(vRecipe_id NUMBER) IS
2964: SELECT formula_id, routing_id From gmd_recipes_b
2965: WHERE recipe_id = vRecipe_id;
2966:
2967: Standard_costing_exception EXCEPTION;
2968: Period_status_exception EXCEPTION;
3013: FETCH Get_Recipe_id INTO l_Recipe_id;
3014: CLOSE Get_Recipe_id;
3015:
3016: /* Update the Recipe - status field */
3017: UPDATE gmd_recipes_b
3018: SET recipe_status = '900'
3019: WHERE recipe_id = l_Recipe_Id
3020: AND to_number(recipe_status) < 800;
3021: