[Home] [Help]
135: Cursor check_recipe_cust_exists(v_recipe_id NUMBER, v_customer_id NUMBER) IS
136: SELECT 1
137: FROM sys.DUAL
138: WHERE EXISTS ( SELECT 1
139: FROM gmd_recipe_customers
140: WHERE recipe_id = v_recipe_id
141: AND customer_id = v_customer_id);
142:
143: /* Check if recipe step material exists */
569: AND rec.formula_id = eff.formula_id
570: AND NVL ( rec.routing_id, -111 ) = NVL ( eff.routing_id, -111 );
571:
572: /* Step 3 */
573: /* Migrate data into the GMD_RECIPE_CUSTOMERS table */
574: FOR recipe_cust_rec IN (SELECT a.recipe_id,
575: a.cust_id,
576: a.created_by,
577: a.creation_date,
580: a.text_code,
581: a.last_update_date
582: FROM gmd_recipe_eff_assoc a
583: WHERE NOT EXISTS ( SELECT 1
584: FROM gmd_recipe_customers
585: WHERE recipe_id = a.recipe_id
586: AND customer_id = a.cust_id )
587: AND cust_id IS NOT NULL
588: GROUP BY a.cust_id,
597: OPEN check_recipe_cust_exists(recipe_cust_rec.recipe_id,
598: recipe_cust_rec.cust_id);
599: FETCH check_recipe_cust_exists INTO l_dummy_id;
600: IF (check_recipe_cust_exists%NOTFOUND) THEN
601: INSERT INTO gmd_recipe_customers
602: ( recipe_id,
603: customer_id,
604: created_by,
605: creation_date,