APPS.GMD_RECIPE_GENERATE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 80

**			    and updates accordingly.
**			    Rewrote some of the code for workflow
**   kkillams   01-DEC-2004 4004501, replaced p_orgn_code with p_orgn_id.
**   Kapil M    03-JAN-2007 LCF-GMO ME : Bug#5458666- Added the parameters routing-id and pi-indicator
**                          Passed the parameters to create_recipe.
**   Raju 06-JULY-2010 Added creation_type = 1 in where condition to create
**        recipes and VR automatically only when  auto recipe is setup
**        creation type as Automatic.
+========================================================================+*/
PROCEDURE recipe_generate(p_orgn_id        IN NUMBER,
			  p_formula_id	   IN NUMBER,
			  x_return_status  OUT NOCOPY VARCHAR2,
			  x_recipe_no      OUT NOCOPY VARCHAR2,
			  x_recipe_version OUT NOCOPY NUMBER,
			  p_event_signed   IN BOOLEAN,
                          p_routing_id IN NUMBER DEFAULT NULL,
                          p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL) IS

  /* Cursors */
  CURSOR c_get_recipe_info IS
    SELECT 	*
    FROM	gmd_recipe_generation
    WHERE (organization_id = p_orgn_id OR organization_id IS NULL)
    AND   creation_type IN (1,2)
    ORDER BY organization_id;

Line: 109

    SELECT 	formula_status
    FROM	fm_form_mst_b
    WHERE	formula_id = p_formula_id;

Line: 350

    SELECT validity_rule_status
    FROM   gmd_recipe_validity_rules
    WHERE  recipe_validity_rule_id = V_validity_rule_id;

Line: 413

      UPDATE gmd_recipe_validity_rules
      SET    validity_rule_status = l_default_vr_status.entity_status
      WHERE  recipe_validity_rule_id = l_vr_id;

Line: 483

    SELECT recipe_status
    FROM   gmd_recipes_b
    WHERE  recipe_no = V_recipe_no
    AND    recipe_version = V_recipe_version;

Line: 490

    SELECT ROUTING_STATUS
    FROM GMD_ROUTINGS_B
    WHERE routing_id = V_routing_id;

Line: 496

    SELECT ROUTING_NO, ROUTING_VERS
    FROM GMD_ROUTINGS_B
    WHERE routing_id = V_routing_id;

Line: 590

      UPDATE gmd_recipes_b
      SET    recipe_status = l_default_recipe_status.entity_status
      WHERE  recipe_no = l_recipe_tbl.recipe_no
      AND    recipe_version = l_recipe_tbl.recipe_version;

Line: 655

** Notes   : This procedure is used to update the existing validity rules
**           based on the recipe generation setup record.
** HISTORY
**    Thomas Daniel	1 June	 	Created.
**  PARAMETERS
**
** kkillams 01-dec-2004 p_orgn_code parameter is replaced with p_orgn_id w.r.t. 4004501
**+========================================================================+*/
PROCEDURE manage_existing_validity(p_item_id               IN NUMBER,
                                   p_orgn_id               IN NUMBER,
				   p_recipe_use            IN NUMBER,
				   p_start_date            IN DATE,
				   p_end_date              IN DATE,
				   p_inv_min_qty           IN NUMBER,
				   p_inv_max_qty           IN NUMBER,
				   p_manage_validity_rules IN VARCHAR2) IS
BEGIN
  /* If Managing Validity Rules is set as First Preference */
  IF p_manage_validity_rules = 1 THEN
    /* We need to increase the preference for all the validity rules that are valid */
    /* in the current validity rules validity dates */
    UPDATE gmd_recipe_validity_rules
    SET    preference = preference + 1,
           last_updated_by = g_user_id,
           last_update_date = sysdate,
           last_update_login = g_login_id
    WHERE  inventory_item_id = p_item_id
    AND    organization_id = p_orgn_id
    AND    recipe_use = p_recipe_use
    AND    NVL(end_date, p_start_date) >= p_start_date
    AND    start_date <= NVL(p_end_date, start_date)
    AND    inv_max_qty >= p_inv_min_qty
    AND    inv_min_qty <= p_inv_max_qty
    AND    validity_rule_status < 800
    AND    delete_mark = 0;

Line: 694

    UPDATE gmd_recipe_validity_rules
    SET    end_date = sysdate,
           last_updated_by = g_user_id,
           last_update_date = sysdate,
           last_update_login = g_login_id
    WHERE  inventory_item_id = p_item_id
    AND    organization_id = p_orgn_id
    AND    recipe_use = p_recipe_use
    AND    ((end_date IS NULL AND p_end_date IS NULL) OR
            (end_date <= NVL(p_end_date, end_date)))
    AND    NVL(end_date, p_start_date) >= p_start_date
    AND    inv_max_qty <= p_inv_max_qty
    AND    inv_max_qty >= p_inv_min_qty
    AND    validity_rule_status < 800
    AND    delete_mark = 0;