APPS.GMD_AUTO_STEP_CALC SQL Statements

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

Line: 87

    SELECT dep_routingstep_no, transfer_pct
    FROM   fm_rout_dep
    WHERE  routing_id = X_routing_id
    AND    routingstep_no = V_step;

Line: 93

    SELECT batchstep_no, transfer_percent, step_status
    FROM   gme_batch_step_dependencies d, gme_batch_steps s
    WHERE  d.batch_id = P_parent_id
    AND    d.batchstep_id = V_step_id
    AND    s.batchstep_id = d.dep_step_id
    AND    s.batch_id = d.batch_id;

Line: 101

    SELECT a.conversion_rate, b.uom_class
    FROM   mtl_uom_conversions a, mtl_units_of_measure b
    WHERE  a.uom_code = b.uom_code
           AND a.inventory_item_id = 0
	   AND b.uom_code = V_uom_code;

Line: 108

    SELECT step_status
    FROM   gme_batch_steps
    WHERE  batchstep_id = V_step_id;

Line: 533

    SELECT routing_id
    FROM   gmd_recipes_b
    WHERE  recipe_id = P_parent_id;

Line: 539

    SELECT dep_routingstep_no, max(level)
    FROM   fm_rout_dep
    START WITH ((routing_id = P_routing_id) AND
                ((p_step_no IS NULL) OR (routingstep_no = p_step_no)))
    CONNECT BY routing_id = prior routing_id
    AND    routingstep_no = prior dep_routingstep_no
    GROUP BY dep_routingstep_no
    ORDER BY max(level) desc;

Line: 550

    SELECT routingstep_no
    FROM   fm_rout_dtl
    WHERE  routing_id = P_routing_id
    AND    routingstep_no NOT IN
           (SELECT dep_routingstep_no
            FROM   fm_rout_dep
            WHERE  routing_id = P_routing_id);

Line: 559

    SELECT o.process_qty_uom, d.routingstep_id
    FROM   fm_rout_dtl d,
           gmd_operations_b o
    WHERE  d.oprn_id = o.oprn_id
    AND    d.routing_id = P_routing_id
    AND    d.routingstep_no = V_routingstep_no;

Line: 567

    SELECT routing_id
    FROM   gme_batch_header
    WHERE  batch_id = P_parent_id;

Line: 572

    SELECT d.dep_step_id, max(level)
    FROM   gme_batch_step_dependencies d
    WHERE  d.batch_id = P_parent_id
    START WITH ((d.batch_id = P_parent_id) AND
                ((v_step_id IS NULL) OR (batchstep_id = v_step_id)))
    CONNECT BY d.batch_id = prior d.batch_id
    AND    d.batchstep_id = prior d.dep_step_id
    GROUP BY d.dep_step_id
    ORDER BY max(level) desc;

Line: 583

    SELECT batchstep_id
    FROM   gme_batch_steps
    WHERE  batch_id = P_parent_id
    AND    batchstep_no = V_step_no;

Line: 589

    SELECT batchstep_no
    FROM   gme_batch_steps
    WHERE  batch_id = P_parent_id
    AND    batchstep_id = V_step_id;

Line: 595

    SELECT batchstep_id, batchstep_no
    FROM   gme_batch_steps s
    WHERE  s.batch_id = P_parent_id
    AND    s.batchstep_id NOT IN
           (SELECT dep_step_id
            FROM   gme_batch_step_dependencies
            WHERE  batch_id = P_parent_id);

Line: 604

    SELECT STEP_QTY_UM
    FROM   gme_batch_steps
    WHERE  batch_id = P_parent_id
    AND    batchstep_no = V_batchstep_no;

Line: 834

    SELECT uom_class
    FROM   mtl_units_of_measure
    WHERE  uom_code = V_uom_code;

Line: 839

    SELECT uom_code
    FROM   mtl_units_of_measure
    WHERE  uom_class = V_uom_class;

Line: 945

    SELECT s.formulaline_id, d.line_type, d.qty, d.detail_uom, d.inventory_item_id, d.scale_type
    FROM   gmd_recipe_step_materials s,
           fm_matl_dtl d
    WHERE  s.recipe_id = P_parent_id
    AND    s.formulaline_id = d.formulaline_id
    AND    s.routingstep_id = V_step_id
    AND    NVL (d.contribute_step_qty_ind, 'Y') = 'Y'
    ORDER BY d.line_type;

Line: 955

    SELECT b.material_detail_id batchline_id, d.line_type,
           d.plan_qty,
           (d.actual_qty/(1+scrap_factor)) actual_qty,
           d.dtl_um, d.inventory_item_id
    FROM   gme_batch_step_items b,
           gme_material_details d,
           gme_batch_steps r
    WHERE  b.batch_id = P_parent_id
    AND    b.batchstep_id = r.batchstep_id
    AND    b.material_detail_id = d.material_detail_id
    AND    b.batchstep_id = V_step_id
    AND    NVL (d.contribute_step_qty_ind, 'Y') = 'Y'
    ORDER BY d.line_type;

Line: 971

    SELECT concatenated_segments
    FROM   mtl_system_items_kfv
    WHERE  inventory_item_id = X_item_id;

Line: 985

  P_work_step_tbl.DELETE;

Line: 1323

    SELECT s.formulaline_id, d.line_type, d.qty, d.detail_uom, d.inventory_item_id, d.scale_type
    FROM   gmd_recipe_step_materials s,
           fm_matl_dtl d
    WHERE  s.recipe_id = P_parent_id
    AND    s.formulaline_id = d.formulaline_id
    AND    s.routingstep_id = V_step_id
    AND    NVL (d.contribute_step_qty_ind, 'Y') = 'Y'
    ORDER BY d.line_type;

Line: 1334

    SELECT concatenated_segments
    FROM   mtl_system_items_kfv
    WHERE  inventory_item_id = X_item_id;

Line: 1340

    SELECT d.*
    FROM  fm_matl_dtl d ,
          gmd_recipes_b r
    WHERE r.recipe_id = P_parent_id
      AND r.formula_id = d.formula_id
    ORDER BY d.line_type, d.line_no;

Line: 1349

    SELECT f.owner_organization_id
    FROM   fm_form_mst f, gmd_recipes r
    WHERE  r.recipe_id = P_parent_id
    AND    f.formula_id = r.formula_id;

Line: 1363

  P_work_step_tbl.DELETE;

Line: 1373

  x_cost_scale_tab.DELETE;

Line: 1374

  p_cost_scale_tab.DELETE;

Line: 1375

  p_formulaline_scale_tab.DELETE;

Line: 1723

    SELECT formula_id, routing_id
    FROM   gmd_recipes_b
    WHERE  recipe_id = P_check.parent_id;

Line: 1728

    SELECT 1
    FROM   sys.dual
    WHERE EXISTS (SELECT 1
                  FROM   fm_rout_dtl
                  WHERE  routing_id = V_routing_id);

Line: 1739

    SELECT 1
    FROM   fm_matl_dtl
    WHERE  formula_id = V_formula_id
    AND    NVL(contribute_step_qty_ind, 'Y') = 'Y'
    AND    formulaline_id NOT IN (SELECT formulaline_id
                                  FROM   gmd_recipe_step_materials
                                  WHERE  recipe_id = P_check.parent_id)
    AND (P_check.formulaline_id IS NULL OR
               formulaline_id <> P_check.formulaline_id)
    ;

Line: 1751

    SELECT d.qty, d.detail_uom, d.inventory_item_id
    FROM   fm_matl_dtl d
    WHERE  d.formula_id = V_formula_id
      AND    NVL(d.contribute_step_qty_ind, 'Y')  = 'Y'
      AND (P_check.formulaline_id IS NULL OR
                   formulaline_id <> P_check.formulaline_id)
    ;

Line: 1760

    SELECT concatenated_segments
    FROM   mtl_system_items_kfv
    WHERE  inventory_item_id = X_item_id;

Line: 1765

    SELECT uom_code
    FROM   mtl_units_of_measure
    WHERE  uom_class = V_uom_class;

Line: 1770

    SELECT COUNT(distinct uom_class)
     FROM  fm_matl_dtl d ,mtl_units_of_measure m
     WHERE d.detail_uom = m.uom_code
         AND d.formula_id = pformula_id
         AND NVL(d.contribute_step_qty_ind, 'Y')  = 'Y'
         AND (P_check.formulaline_id IS NULL OR
                   formulaline_id <> P_check.formulaline_id);

Line: 1779

    SELECT distinct m.uom_class
     FROM  fm_matl_dtl d ,mtl_units_of_measure m
     WHERE d.detail_uom = m.uom_code
         AND d.formula_id = pformula_id
         AND NVL(d.contribute_step_qty_ind, 'Y')  = 'Y'
         AND (P_check.formulaline_id IS NULL OR
              formulaline_id <> P_check.formulaline_id);

Line: 1788

    SELECT count(*)
    FROM  fm_rout_dtl h,fm_rout_dep d
    WHERE h.routing_id = prouting_id AND
          h.routing_id = d.routing_id;

Line: 1794

    SELECT count(distinct u.uom_class)
    FROM   fm_rout_dtl d,
           gmd_operations_b o,
           mtl_units_of_measure u
    WHERE  d.oprn_id = o.oprn_id
    AND    d.routing_id = prouting_id
    AND    o.process_qty_uom = u.uom_code;

Line: 1804

    SELECT distinct u.uom_class
    FROM   fm_rout_dtl d,
           gmd_operations_b o,
           mtl_units_of_measure u
    WHERE  d.oprn_id = o.oprn_id
    AND    d.routing_id = prouting_id
    AND    o.process_qty_uom = u.uom_code;

Line: 2076

      SELECT m.recipe_id,
             r.recipe_status
      FROM   gmd_recipe_step_materials m,
             gmd_recipes_b r,
             gmd_status_b s
      WHERE  s.status_code    = r.recipe_status
        AND  r.recipe_id      = m.recipe_id
        AND  ((p_formulaline_id is not null and m.formulaline_id = P_formulaline_id)
               OR
               (p_routingstep_id is not null and m.routingstep_id = P_routingstep_id))
        AND  r.calculate_step_quantity > 0
        AND  s.status_type   <> 1000
        AND  r.delete_mark    = 0;

Line: 2108

  SELECT COUNT(unique recipe_id) into P_check_step_mat.STEP_ASSOC_RECIPES
    FROM   gmd_recipe_step_materials
   WHERE  (P_check.formulaline_id is not null AND formulaline_id = P_check.formulaline_id)
             OR
          (p_check.routingstep_id is not null AND routingstep_id = P_check.routingstep_id) ;

Line: 2167

x_update_flex       gmd_recipe_detail.recipe_update_flex;

Line: 2200

    DELETE FROM   gmd_recipe_step_materials
     WHERE  (P_check.formulaline_id is not null AND formulaline_id = P_check.formulaline_id)
             OR
            (p_check.routingstep_id is not null AND routingstep_id = P_check.routingstep_id) ;

Line: 2207

    /* We need not perform the ASQC recalculation and update the GMD Recipe
       step table because if the ASQC flag is ON then the values are not saved
       in the db or the GMD Recipe Steps table.  Each time the Recipes form
       open if the ASQC flag is ON then it performs the recalculation */

    IF p_check.routingstep_id is not null THEN
       -- save what has been done so far and go to end.  Put message on stack.
       IF (P_check_step_mat.ASQC_RECIPES > 0) THEN
           RAISE ALL_MTL_LINES_NOT_ASSOC;

Line: 2217

           DELETE FROM gmd_recipe_routing_steps
           WHERE  (p_check.routingstep_id is not null
                   AND routingstep_id = P_check.routingstep_id);

Line: 2221

    END IF;   -- end if routingstep is being deleted.

Line: 2268

          X_all_steps_tbl(x_step_cntr).last_update_date  := P_check.last_update_date;

Line: 2269

          X_all_steps_tbl(x_step_cntr).last_updated_by   := P_check.last_updated_by;

Line: 2270

          X_all_steps_tbl(x_step_cntr).last_update_login := P_check.last_update_login;

Line: 2287

                                    p_recipe_insert_flex => x_flex,
                                    p_recipe_update_flex => x_update_flex
                                   );

Line: 2294

  END IF;    -- end if there are any recipes affected by the formulaline or routingstep delete

Line: 2337

    SELECT formula_id, routing_id
    FROM   GME_BATCH_HEADER
    WHERE  BATCH_ID = P_check.parent_id;

Line: 2343

    SELECT UOM_CODE
    FROM   mtl_units_of_measure
    WHERE  uom_class = V_um_type
    AND BASE_UOM_FLAG = 'Y';

Line: 2349

    SELECT COUNT(distinct m.uom_class)
     FROM  fm_matl_dtl d ,mtl_units_of_measure m
     WHERE d.DETAIL_UOM = m.uom_code
         AND d.formula_id = pformula_id
         AND NVL(d.contribute_step_qty_ind, 'Y')  = 'Y'
         AND (P_check.formulaline_id IS NULL OR
                   formulaline_id <> P_check.formulaline_id);

Line: 2358

    SELECT distinct m.uom_class
     FROM  fm_matl_dtl d ,mtl_units_of_measure m
     WHERE d.DETAIL_UOM= m.uom_code
         AND d.formula_id = pformula_id
         AND NVL(d.contribute_step_qty_ind, 'Y')  = 'Y'
         AND (P_check.formulaline_id IS NULL OR
              formulaline_id <> P_check.formulaline_id);

Line: 2367

    SELECT count(distinct u.uom_class)
    FROM   fm_rout_dtl d,
           gmd_operations_b o,
           mtl_units_of_measure u
    WHERE  d.oprn_id = o.oprn_id
    AND    d.routing_id = prouting_id
    AND    o.process_qty_uom = u.uom_code;

Line: 2376

    SELECT distinct u.uom_class
    FROM   fm_rout_dtl d,
           gmd_operations_b o,
           mtl_units_of_measure u
    WHERE  d.oprn_id = o.oprn_id
    AND    d.routing_id = prouting_id
    AND    o.process_qty_uom = u.uom_code;