OPM formula uploading through api script

 PROCEDURE create_formula (p_create_formula IN VARCHAR2)
   IS
      l_frmla_inst_tbl          apps.gmd_formula_pub.formula_insert_hdr_tbl_type;
      l_return_status           VARCHAR2 (1);
      l_msg_count               NUMBER;
      l_msg_data                VARCHAR2 (4000);
      l_count                   NUMBER;
      l_msg_ind                 VARCHAR2 (240);
      ln_scale_type             NUMBER;
      ln_contribute_yield_ind   VARCHAR2 (10);
      ln_suc_rec_cnt            NUMBER                                   := 0;
      ln_rej_rec_cnt            NUMBER                                   := 0;
      ln_count                  NUMBER;
      ln_ing_uom_code           VARCHAR2 (20);
      ln_progress               NUMBER                                   := 0;     
      ln_formula_type           NUMBER;    
      lc_uom_code               VARCHAR2 (20);
      lc_ing_uom_code           VARCHAR2 (20);     
      lc_item_no                VARCHAR2 (20);

--===============================
--Cursor to get new records count
--===============================
      CURSOR lcu_count
      IS
         SELECT COUNT (*)
           FROM xxblr_opm_formula_stg
          WHERE 1=1
            AND record_status = gc_validation_flag;

--===============================
--Cursor to get Formula Header Data
--===============================
      CURSOR c_frml_main
      IS
        SELECT  new_formula_no formula_no
              , formula_type
              , formula_vers
          FROM
             (
               (  SELECT  new_formula_no
                         , formula_type
                         , formula_vers
                     FROM  xxblr_opm_formula_stg xoms
                    WHERE  1=1
                      AND  xoms.record_status = gc_validation_flag
                 GROUP BY  new_formula_no
                         , formula_type
                         , formula_vers
                )
                 MINUS
                (  SELECT  new_formula_no
                         , formula_type
                         , formula_vers
                     FROM xxblr_opm_formula_stg xoms
                    WHERE 1=1
                      AND    (   NVL (record_status, 'N')  = gc_newrecord_flag
                              OR NVL (record_status, 'N')  = gc_validation_error_flag
                              OR NVL (record_status, 'N')  = gc_error_flag
                              OR NVL (record_status, 'N')  = gc_success_flag
                              OR NVL (record_status, 'N')  = gc_item_error_flag
                             )
                 GROUP BY  new_formula_no
                         , formula_type
                         , formula_vers
                )
             )
         ORDER BY new_formula_no;
     
--===============================
--Cursor to get Formula Product data
--===============================
      CURSOR c_frml_product (p_formula_no VARCHAR2, p_formula_vers NUMBER)
      IS                  
        SELECT  new_formula_no formula_no
              , formula_vers
              , formula_desc
              , formula_class
              , organization_code
              , organization_id
              , line_no
              , new_item_no  item_no
              , item_uom
              , line_type
              , required_qty
              , scrap_factor
              , secondary_product_qty
              , secondary_product_qty_uom
              , contribute_to_yield
              , cost_allocation
              , ingredient_type
              , inventory_item_id
          FROM  xxblr_opm_formula_stg xoms
         WHERE  1=1
           AND  NVL(xoms.record_status,'N')   =     gc_validation_flag
           AND  new_formula_no                =     p_formula_no
           AND  formula_vers                  =     p_formula_vers
           AND  line_type                     =     1
      ORDER BY  new_formula_no, line_no;

--=====================================
--Cursor to get Formula Ingredient Data
--=====================================
      CURSOR c_frml_ingrediet (p_formula_no VARCHAR2, p_formula_vers NUMBER)
      IS
         SELECT   new_formula_no formula_no
                , formula_vers
                , formula_desc
                , formula_class
                , organization_code
                , organization_id
                , line_type
                , line_no
                , new_item_no item_no
                , required_qty
                , secondary_product_qty
                , item_uom
                , issue_to_step
                , contribute_to_yield
                , scrap_factor
                , primary_quantity
                , standard_potency                 
                , potency_uom
                , overage
                , ingredient_type
                , inventory_item_id
             FROM xxblr_opm_formula_stg xoms
            WHERE 1=1
              AND NVL(xoms.record_status, 'N')  =   gc_validation_flag
              AND new_formula_no                =   p_formula_no
              AND formula_vers                  =   p_formula_vers
              AND line_type                     =   -1
         ORDER BY new_formula_no, line_no;


      CURSOR lcu_item_uom (p_uom_code VARCHAR2)
      IS
         SELECT uom_code
           FROM apps.mtl_units_of_measure
          WHERE 1=1
            AND UPPER(uom_code) = UPPER(p_uom_code);

   BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing CREATE_FORMULA ---------');
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.output,'   --------- Executing CREATE_FORMULA ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

       OPEN lcu_count;
      FETCH lcu_count
       INTO ln_count;
      CLOSE lcu_count;

      apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records to be Processed :-> ' || ln_count);

            -------------------------------------------------------------------------
            -- Bulk Insert   Data into Qualification.
            --------------------------------------------------------------------------
      BEGIN
         FOR for_rec IN c_frml_main
         LOOP
         
            BEGIN
               l_count := 0;
               l_frmla_inst_tbl.delete;
               apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK1.0');

               FOR prod_rec IN c_frml_product (for_rec.formula_no,for_rec.formula_vers)
               LOOP
                  l_count := l_count + 1;

                  BEGIN
                     apps.fnd_file.put_line (apps.fnd_file.LOG,' Product :: '|| for_rec.formula_no|| ' Line :: '|| prod_rec.line_no|| ' Count :: '|| l_count);
                                                             
                     l_frmla_inst_tbl (l_count).record_type             := 'I';
                     l_frmla_inst_tbl (l_count).formula_no              := for_rec.formula_no;
                     l_frmla_inst_tbl (l_count).formula_vers            := for_rec.formula_vers;
                     IF for_rec.formula_type = 'FLP' then
                        l_frmla_inst_tbl (l_count).formula_type            := 1;
                     ELSE
                        l_frmla_inst_tbl (l_count).formula_type            := 0;
                     END IF;
                     l_frmla_inst_tbl (l_count).formula_desc1           := prod_rec.formula_desc;
                     l_frmla_inst_tbl (l_count).formula_desc2           := NULL;
                     l_frmla_inst_tbl (l_count).formula_class           := prod_rec.formula_class;
                     l_frmla_inst_tbl (l_count).fmcontrol_class         := NULL;
                     l_frmla_inst_tbl (l_count).inactive_ind            := 0;
                     l_frmla_inst_tbl (l_count).owner_organization_id   := prod_rec.organization_id;
                     l_frmla_inst_tbl (l_count).total_input_qty         := 10;
                     l_frmla_inst_tbl (l_count).total_output_qty        := 10;
                     l_frmla_inst_tbl (l_count).yield_uom               := prod_rec.item_uom;
                     l_frmla_inst_tbl (l_count).formula_status          := gn_status;
                     l_frmla_inst_tbl (l_count).owner_id                := l_user_id;
                     l_frmla_inst_tbl (l_count).formula_id              := NULL;
                     l_frmla_inst_tbl (l_count).formulaline_id          := NULL;
                     l_frmla_inst_tbl (l_count).line_type               := 1;
                     l_frmla_inst_tbl (l_count).line_no                 := prod_rec.line_no;
                     l_frmla_inst_tbl (l_count).item_no                 := prod_rec.item_no;
                     l_frmla_inst_tbl (l_count).inventory_item_id       := prod_rec.inventory_item_id;
                     l_frmla_inst_tbl (l_count).revision                := NULL;
                     l_frmla_inst_tbl (l_count).qty                     := prod_rec.required_qty;--prod_rec.PRIMARY_QUANTITY; --
                     l_frmla_inst_tbl (l_count).detail_uom              := prod_rec.item_uom;
                     l_frmla_inst_tbl (l_count).master_formula_id       := NULL;
                     l_frmla_inst_tbl (l_count).release_type            := 3;
                     l_frmla_inst_tbl (l_count).scrap_factor            := prod_rec.scrap_factor/100;
                     l_frmla_inst_tbl (l_count).scale_type_hdr          := 1;
                     l_frmla_inst_tbl (l_count).scale_type_dtl          := 1;
                     l_frmla_inst_tbl (l_count).cost_alloc              := 1;
                     l_frmla_inst_tbl (l_count).phantom_type            := 0;
                     l_frmla_inst_tbl (l_count).rework_type             := 0;
                     l_frmla_inst_tbl (l_count).buffer_ind              := 0;
                     l_frmla_inst_tbl (l_count).ingredient_end_date     := NULL;                    
                     l_frmla_inst_tbl (l_count).dtl_attribute_category  := prod_rec.ingredient_type;
                     l_frmla_inst_tbl (l_count).scale_multiple          := NULL;
                     l_frmla_inst_tbl (l_count).contribute_yield_ind    := prod_rec.contribute_to_yield;
                     l_frmla_inst_tbl (l_count).scale_uom               := 'Y';
                     l_frmla_inst_tbl (l_count).contribute_step_qty_ind := prod_rec.contribute_to_yield;
                     l_frmla_inst_tbl (l_count).scale_rounding_variance := NULL;
                     l_frmla_inst_tbl (l_count).rounding_direction      := NULL;
                     --l_frmla_inst_tbl(l_count).text_code_hdr             :=
                     --l_frmla_inst_tbl(l_count).text_code_dtl             :=
                     l_frmla_inst_tbl (l_count).user_id                 := l_user_id;
                     l_frmla_inst_tbl (l_count).creation_date           := gd_sysdate;
                     l_frmla_inst_tbl (l_count).created_by              := l_user_id;
                     l_frmla_inst_tbl (l_count).last_updated_by         := l_user_id;
                     l_frmla_inst_tbl (l_count).last_update_date        := gd_sysdate;
                     l_frmla_inst_tbl (l_count).last_update_login       := l_user_id;
                     l_frmla_inst_tbl (l_count).user_name               := l_user_name;
                     l_frmla_inst_tbl (l_count).delete_mark             := 0;
                     l_frmla_inst_tbl (l_count).auto_product_calc       := 'Y';
                     l_frmla_inst_tbl (l_count).prod_percent            := 100;
                     l_frmla_inst_tbl (l_count).attribute1              := for_rec.formula_type;
                                         
                     apps.fnd_file.put_line (apps.fnd_file.LOG, 'Debug3');
                  END;

                  apps.fnd_file.put_line (apps.fnd_file.LOG, 'Debug4');
               END LOOP;
               apps.fnd_file.put_line (apps.fnd_file.LOG, ' Formula Product count = '||l_count);
               FOR ingre_rec IN c_frml_ingrediet (for_rec.formula_no,for_rec.formula_vers)
               LOOP
                  l_count := l_count + 1;
                  BEGIN
                     apps.fnd_file.put_line (apps.fnd_file.LOG,' Ingredient :: '|| for_rec.formula_no|| ' Line :: '|| ingre_rec.line_no|| ' Count :: '|| l_count);

                     apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK3');
      
                     l_frmla_inst_tbl (l_count).record_type             := 'I';
                     l_frmla_inst_tbl (l_count).formula_no              := for_rec.formula_no;
                     l_frmla_inst_tbl (l_count).formula_vers            := for_rec.formula_vers;
                     l_frmla_inst_tbl (l_count).formula_type            := 0;
                     l_frmla_inst_tbl (l_count).formula_desc1           := ingre_rec.formula_desc;
                     l_frmla_inst_tbl (l_count).formula_desc2           := NULL;
                     l_frmla_inst_tbl (l_count).formula_class           := ingre_rec.formula_class;
                     l_frmla_inst_tbl (l_count).fmcontrol_class         := NULL;
                     l_frmla_inst_tbl (l_count).inactive_ind            := 0;
                     l_frmla_inst_tbl (l_count).owner_organization_id   := ingre_rec.organization_id;
                     l_frmla_inst_tbl (l_count).total_input_qty         := 10;
                     l_frmla_inst_tbl (l_count).total_output_qty        := 10;
                     l_frmla_inst_tbl (l_count).yield_uom               := ingre_rec.item_uom;
                     l_frmla_inst_tbl (l_count).formula_status          := gn_status;--500;
                     l_frmla_inst_tbl (l_count).owner_id                := l_user_id;
                     l_frmla_inst_tbl (l_count).formula_id              := NULL;
                     l_frmla_inst_tbl (l_count).formulaline_id          := NULL;
                     l_frmla_inst_tbl (l_count).line_type               := -1;
                     l_frmla_inst_tbl (l_count).line_no                 := ingre_rec.line_no;
                     l_frmla_inst_tbl (l_count).item_no                 := ingre_rec.item_no;
                     l_frmla_inst_tbl (l_count).inventory_item_id       := ingre_rec.inventory_item_id;
                     l_frmla_inst_tbl (l_count).revision                := NULL;
                     l_frmla_inst_tbl (l_count).qty                     := ingre_rec.primary_quantity;
                     l_frmla_inst_tbl (l_count).detail_uom              := ingre_rec.item_uom;
                     l_frmla_inst_tbl (l_count).master_formula_id       := NULL;
                     l_frmla_inst_tbl (l_count).release_type            := 3;
                     l_frmla_inst_tbl (l_count).scrap_factor            := ingre_rec.scrap_factor/100;
                     l_frmla_inst_tbl (l_count).scale_type_hdr          := 1;
                     l_frmla_inst_tbl (l_count).scale_type_dtl          := 1;
                     l_frmla_inst_tbl (l_count).cost_alloc              := 1;
                     l_frmla_inst_tbl (l_count).phantom_type            := 0;
                     l_frmla_inst_tbl (l_count).rework_type             := 0;
                     l_frmla_inst_tbl (l_count).buffer_ind              := 0;
                     l_frmla_inst_tbl (l_count).ingredient_end_date     := NULL;
                     l_frmla_inst_tbl (l_count).dtl_attribute_category  := ingre_rec.ingredient_type;
                     l_frmla_inst_tbl (l_count).scale_multiple          := NULL;
                     l_frmla_inst_tbl (l_count).contribute_yield_ind    := ingre_rec.contribute_to_yield;
                     l_frmla_inst_tbl (l_count).scale_uom               := 'Y';
                     l_frmla_inst_tbl (l_count).contribute_step_qty_ind := ingre_rec.contribute_to_yield;
                     l_frmla_inst_tbl (l_count).scale_rounding_variance := NULL;
                     l_frmla_inst_tbl (l_count).rounding_direction      := NULL;                   
                     l_frmla_inst_tbl (l_count).user_id                 := l_user_id;
                     l_frmla_inst_tbl (l_count).creation_date           := gd_sysdate;
                     l_frmla_inst_tbl (l_count).created_by              := l_user_id;
                     l_frmla_inst_tbl (l_count).last_updated_by         := l_user_id;
                     l_frmla_inst_tbl (l_count).last_update_date        := gd_sysdate;
                     l_frmla_inst_tbl (l_count).last_update_login       := l_user_id;
                     l_frmla_inst_tbl (l_count).user_name               := l_user_name;
                     l_frmla_inst_tbl (l_count).delete_mark             := 0;
                     l_frmla_inst_tbl (l_count).auto_product_calc       := 'Y';
                     l_frmla_inst_tbl (l_count).prod_percent            := NULL;
                     l_frmla_inst_tbl (l_count).dtl_attribute1          := ingre_rec.standard_potency;
                     l_frmla_inst_tbl (l_count).dtl_attribute2          := ingre_rec.potency_uom;
                     l_frmla_inst_tbl (l_count).dtl_attribute3          := ingre_rec.overage;
                   
                  END;
               END LOOP;
              
               apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling the API: '||l_count);
               BEGIN
                 
                  apps.gmd_formula_pub.insert_formula
                                                     (  p_api_version             => '1.0'
                                                      , p_init_msg_list           => 'T'
                                                      , p_commit                  => 'T'
                                                      , p_called_from_forms       => 'NO'
                                                      , x_return_status           => l_return_status
                                                      , x_msg_count               => l_msg_count
                                                      , x_msg_data                => l_msg_data
                                                      , p_formula_header_tbl      => l_frmla_inst_tbl
                                                      , p_allow_zero_ing_qty      => 'FALSE'
                                                     );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_return_status);
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.4 data:' || l_msg_data);             
               END;

               IF l_return_status <> 'S'
               THEN
                  ln_rej_rec_cnt := ln_rej_rec_cnt + 1;

                  FOR i IN 1 .. l_msg_count
                  LOOP
                     BEGIN
                        apps.fnd_msg_pub.get (  p_msg_index          => i
                                              , p_encoded            => 'F'
                                              , p_data               => l_msg_data
                                              , p_msg_index_out      => l_msg_ind
                                             );
                        apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.4 data:'|| l_msg_data);
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           apps.fnd_file.put_line (apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
                           apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.3'|| l_return_status);
                     END;
                  END LOOP;
               ELSE
                  ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
               END IF;

               BEGIN
                  UPDATE  xxblr_opm_formula_stg
                     SET  record_status   =  l_return_status
                        , error_code      =  l_msg_count
                        , error_message   =  l_msg_data
                   WHERE  new_formula_no  =  for_rec.formula_no
                     AND  formula_vers    =  for_rec.formula_vers;
               EXCEPTION
                  WHEN OTHERS THEN
                  apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm); 
               END;
            --- ---
            END;
         END LOOP;

         COMMIT;
         apps.fnd_file.put_line (apps.fnd_file.LOG, ' ');
         apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
         apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records processed :           '|| ln_suc_rec_cnt);
         apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records rejected  :           '|| ln_rej_rec_cnt);
         apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
         apps.fnd_file.put_line (apps.fnd_file.LOG, ' ');
      EXCEPTION
         WHEN OTHERS
         THEN
            apps.fnd_file.put_line (apps.fnd_file.LOG,'INS-INT-ERR @'|| ln_progress|| ' Error- '|| SQLERRM);
      END;
      apps.fnd_file.put_line (apps.fnd_file.LOG,'    ----- INSERT_INTO_API Exit -----');
   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-INS-API :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure CREATE_FORMULA Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END create_formula;