APPS.GMD_FORM_MIGRATION SQL Statements

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

Line: 4

  PROCEDURE  Insert_form_status
   IS
    /* Need to migrate data from fm_form_mst to fm_form_mst_vl */
    CURSOR formula_cur_vl IS
      SELECT  * from fm_form_mst m1
      WHERE  EXISTS ( Select 1
                        from fm_form_mst
                       Where formula_no = m1.formula_no AND
                             formula_vers = m1.formula_vers AND
                             formula_status IS NULL);

Line: 18

      SELECT   distinct d.formula_id
      FROM     ic_item_mst i, fm_matl_dtl d
      WHERE    i.item_id = d.item_id
      AND      i.delete_mark = 1
      GROUP BY d.formula_id;

Line: 45

   /* Update the apps.fm_form_mst  with appropriate status and qty values */
   FOR formula_rec IN formula_cur_vl LOOP
     BEGIN
     /* get the qty and uoms */
     GMD_COMMON_VAL.CALCULATE_TOTAL_QTY (
         formula_id       => formula_rec.formula_id ,
         x_product_qty    => l_total_output_qty     ,
         x_ingredient_qty => l_total_input_qty      ,
         x_uom            => l_uom                  ,
         x_msg_count      => l_msg_count            ,
         x_msg_data       => l_msg_data             ,
         x_return_status  => l_return_status);

Line: 75

     IF (formula_rec.delete_mark = 1) THEN
        l_formula_status := '1000';

Line: 89

     /* Call the formula update MLS API*/
     UPDATE fm_form_mst_b
     SET    orgn_code          = l_orgn_code,
            total_output_qty   = l_total_output_qty,
            total_input_qty    = l_total_input_qty,
            formula_uom        = l_uom,
            formula_status     = l_formula_status,
            owner_id           = formula_rec.created_by,
            inactive_ind       = l_inactive_ind
     WHERE  formula_id = formula_rec.formula_id;

Line: 101

       UPDATE fm_form_mst_tl
       SET    formula_desc1 = l_fm_desc
       WHERE  formula_id = formula_rec.formula_id
       AND    userenv('LANG') IN (language, source_lang);

Line: 109

       GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_FORM_MST'
                                   ,p_target_table => 'FM_FORM_MST'
                                   ,p_source_id    => formula_rec.formula_id
                                   ,p_target_id    => formula_rec.formula_id
                                   ,p_message      => error_msg
                                   ,p_error_type   => 'E');

Line: 117

       GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_FORM_MST'
                                   ,p_target_table => 'FM_FORM_MST'
                                   ,p_source_id    => formula_rec.formula_id
                                   ,p_target_id    => formula_rec.formula_id
                                   ,p_message      => error_msg
                                   ,p_error_type   => 'U');

Line: 128

       UPDATE fm_form_mst_b
       SET    formula_status = '1000'
       WHERE  formula_id = get_inactive_form_rec.formula_id;

Line: 133

  END Insert_form_status ;

Line: 140

    /*Introduced the NVL statements to update the defaults only if they were not filled in earlier */
    -- FOR my_rec IN (select * from fm_matl_dtl) LOOP

    BEGIN
        UPDATE  fm_matl_dtl
        SET     contribute_step_qty_ind = NVL(contribute_step_qty_ind, 'Y'),
                scale_multiple  = NVL(scale_multiple, 0),
                scale_rounding_variance = NVL(scale_rounding_variance, 0),
                contribute_yield_ind = NVL(contribute_yield_ind, 'Y');

Line: 158

          GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_MATL_DTL'
                                   ,p_target_table => 'FM_MATL_DTL'
                                   ,p_source_id    => NULL
                                   ,p_target_id    => NULL
                                   ,p_message      => error_msg
                                   ,p_error_type   => 'U');