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;

OPM create formula api validations and Code

   PROCEDURE validate_formula_data (p_validate_formula_data_flag IN VARCHAR2)
   IS
      ln_formula_id          NUMBER;
      ln_formulaline_id      apps.fm_matl_dtl.formulaline_id%TYPE;
      ln_formula_num         apps.fm_form_mst.formula_no%TYPE;
      ln_formula_desc        apps.fm_form_mst.formula_desc1%TYPE;
      ln_formula_vers        apps.fm_form_mst.formula_vers%TYPE;
      ln_formula_no          apps.fm_form_mst.formula_no%TYPE;
      ln_line_num            apps.fm_matl_dtl.line_no%TYPE;
      lc_line_type           VARCHAR2 (40);
      ln_item_no             VARCHAR2 (25);
      ln_bulk_error_cnt      NUMBER;
      ln_valrec_cnt          NUMBER;
      ln_max_form_vers       NUMBER;
      ln_count               NUMBER;
      ln_trnsfrec_cnt        NUMBER;
      ln_organization_id     NUMBER;
      ln_conversion_value    NUMBER;
      ln_err_count           NUMBER; 
      lc_msg                 VARCHAR2(50);
      lc_uom_code            VARCHAR2(3);
      lc_item_uom_code       VARCHAR2(3);
      lc_mass_uom            VARCHAR2(3);
      lc_formula_class       VARCHAR2(20);
      ln_inventory_item_id   NUMBER;
      lc_cont_to_yield       VARCHAR2(3);

--===============================
--Cursor to get Formula Header Data
--===============================
 
      CURSOR lcu_validate_formula_data (cp_status_flag VARCHAR2)
      IS
         SELECT  transaction_id
               , sr_no
               , new_formula_no formula_no
               , formula_type
               , formula_vers
               , formula_desc
               , formula_class
               , organization_code
               , line_type
               , line_no
               , new_item_no item_no
               , required_qty
               , item_uom
               , secondary_product_qty
               , secondary_product_qty_uom
               , issue_to_step
               , contribute_to_yield
               , scrap_factor
               , primary_quantity
               , standard_potency
               , potency_uom
               , ingredient_type
               , cost_allocation
               , process_flag
               , error_flag
               , error_message
               , error_code
               , record_status
               , last_updated_by
               , created_by
               , last_updated_date
               , creation_date
               , last_update_login
               , formula_id
               , formulaline_id
               , inventory_item_id
               , organization_id
            FROM xxblr_opm_formula_stg xoms
           WHERE 1=1
             AND (   NVL(xoms.record_status, 'N') = gc_newrecord_flag
                  OR NVL(xoms.record_status, 'N') = gc_validation_error_flag
                 )
        ORDER BY transaction_id;

--==================================================
--Cursor to get Formula_No and Version
--==================================================
      CURSOR lcu_formula_chk (cp_formula_no VARCHAR2, cp_formula_vers NUMBER)
      IS
         SELECT formula_vers, formula_no
           FROM apps.fm_form_mst
          WHERE 1=1
            AND formula_no   =  cp_formula_no
            AND formula_vers =  NVL(cp_formula_vers,1);

--==================================================
--Cursor to get Formula_Version
--==================================================
      CURSOR lcu_formula_ver (cp_formula_no VARCHAR2)
      IS
         SELECT ffm.formula_vers
           FROM apps.fm_form_mst ffm
          WHERE 1=1
            AND ffm.formula_no = cp_formula_no;

--==================================================
--Cursor to get Formula_Class
--==================================================
      CURSOR lcu_formula_class (p_formula_class VARCHAR2)
      IS
         SELECT formula_class
           FROM apps.fm_form_cls
          WHERE 1=1
            AND (    UPPER(formula_class_desc) = UPPER (p_formula_class)
                 OR  UPPER(formula_class)      = UPPER(p_formula_class)
                );

--==================================================
 --Cursor to get Line_Type
--==================================================
      CURSOR lcu_line_type (cp_line_type VARCHAR2)
      IS
       SELECT lookup_code
           FROM apps.fnd_lookup_values
          WHERE 1=1
            AND lookup_type = 'GMD_FORMULA_ITEM_TYPE'
            AND (   UPPER (meaning)     = UPPER(cp_line_type)
                 OR UPPER(lookup_code)  = UPPER(cp_line_type)
                );

--==================================================
  --Cursor to get Max Version
--==================================================
      CURSOR max_formula_vers
      IS
         SELECT MAX (formula_vers)
           FROM apps.fm_form_mst;

--==================================================
 --Cursor to get item_NO
--================================================
      CURSOR lcu_item_no (p_item_no VARCHAR2) --, p_org_code VARCHAR2)
      IS
         SELECT inventory_item_id
           FROM apps.mtl_system_items_b msib
             --  , apps.org_organization_definitions ood
          WHERE 1=1
            AND UPPER(msib.segment1)           =    TRIM(p_item_no)         
            AND msib.organization_id           =    g_master_org ;--ood.organization_id    
         --   AND UPPER(ood.organization_code)   =    UPPER(TRIM(p_org_code));

--==================================================
--Cursor to get UOM and Item UOM
--==================================================
         
      CURSOR lcu_uom(cp_uom_code varchar2)
      IS
        SELECT uom_code
          FROM apps.mtl_units_of_measure
         WHERE 1=1
           AND (   UPPER(uom_code)        = UPPER(cp_uom_code)
                OR UPPER(unit_of_measure) = UPPER(cp_uom_code)
               );

      CURSOR lcu_item_uom(cp_item_no varchar2)
      IS
        SELECT primary_uom_code
          FROM apps.mtl_system_items_kfv
         WHERE 1=1
           AND concatenated_segments =  cp_item_no
           AND organization_id       =  g_master_org;

--==================================================
  --Cursor to get org
--==================================================
      CURSOR lcu_org_code (p_org_code VARCHAR2)
      IS
         SELECT organization_id
           FROM apps.org_organization_definitions
          WHERE 1=1
            AND UPPER(organization_code) = UPPER(p_org_code);

--==================================================
  --Cursor to get Contribute to yield
--==================================================
      CURSOR lcu_cont_to_yield (p_contribute_to_yield VARCHAR2)
      IS
         SELECT lookup_code
           FROM apps.fnd_lookup_values
          WHERE 1=1
            AND UPPER(lookup_type)   =  'GMD_QC_YES_NO'
            AND  UPPER(meaning)     =  UPPER(p_contribute_to_yield);

--=====================================================
--Cursor to get count of the records to be processed
--=====================================================
      CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
      IS
         SELECT COUNT (1)
           FROM xxblr_opm_formula_stg xoms
          WHERE 1=1
            AND xoms.record_status = cp_record_status;

      TYPE per_tbl_type IS TABLE OF lcu_validate_formula_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_per                 per_tbl_type;
     
   BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG, '   --------- Executing validate_formula_data ---------');
      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 validate_formula_data ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

        
      SELECT COUNT (1)
        INTO ln_count
        FROM xxblr_opm_formula_stg
       WHERE 1=1
         AND (   NVL(record_status,'N')  = gc_newrecord_flag
              OR NVL(record_status,'N')  = gc_validation_error_flag
             );
      -----
           
      apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records to be Processed:' || ln_count);
    
    
      IF ln_count > 0 THEN
    
          OPEN lcu_validate_formula_data (p_validate_formula_data_flag);
          LOOP
             lt_per.DELETE;

             FETCH lcu_validate_formula_data BULK COLLECT INTO lt_per LIMIT 50000; 
            
             apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track1: '||lt_per.count);

             FOR i IN lt_per.FIRST .. lt_per.COUNT
             LOOP
                apps.fnd_file.put_line (apps.fnd_file.LOG, 'Record = ' || i || ' transaction_id = ' || lt_per(i).transaction_id || ' formula = ' || lt_per (i).formula_no);
                lc_msg := NULL;
                 gc_error_code                   := NULL;
                 gc_error_message                := NULL;           
                 lt_per (i).error_code      := gc_error_code;
                 lt_per (i).error_message   := gc_error_message;

                --===============================
                -- Applying Transformation rules
                --===============================
               
                IF lt_per (i).formula_no IS NULL
                THEN
                   lt_per (i).record_status  := gc_error_flag;
                   gc_error_code             := '-PAT04';
                   gc_error_message          := ' ,FORMULA_NUMBER IS NULL';
                   lt_per (i).error_code     := gc_error_code;
                   lt_per (i).error_message  := gc_error_message;
                ELSE
                   ln_formula_no := null;
                   OPEN lcu_formula_chk (lt_per (i).formula_no,lt_per (i).formula_vers);
                   FETCH lcu_formula_chk INTO ln_formula_vers, ln_formula_no;
                   CLOSE lcu_formula_chk;

                   IF ln_formula_no IS NOT NULL THEN
                      lt_per (i).record_status := gc_error_flag;
                      gc_error_code            := '-PAT04';
                      gc_error_message         := ' ,FORMULA_NUMBER AND VERSION ALREADY EXISTED';
                      lt_per (i).error_code    := gc_error_code;
                      lt_per (i).error_message := gc_error_message;
                   END IF;
                END IF;

                IF lt_per (i).formula_vers IS NULL THEN                 

                   lt_per (i).formula_vers := 1;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula Desc');

                IF lt_per (i).formula_desc IS NULL THEN
                   lt_per (i).record_status  := gc_error_flag;
                   gc_error_code             := '-PAT04';
                   gc_error_message          := ' ,FORMULA DESC NOT FOUND';
                   lt_per (i).error_code     := gc_error_code;
                   lt_per (i).error_message  := gc_error_message;
                END IF;

                IF lt_per (i).formula_class IS NULL THEN
                   lt_per (i).record_status  := gc_error_flag;
                   gc_error_code             := '-PAT04';
                   gc_error_message          := ' ,FORMULA CLASS IS REQUIRED';
                   lt_per (i).error_code     := gc_error_code;
                   lt_per (i).error_message  := gc_error_message;
                ELSE
                   lc_formula_class := NULL;
 
                   OPEN lcu_formula_class (lt_per (i).formula_class);
                   FETCH lcu_formula_class INTO lc_formula_class;
                   CLOSE lcu_formula_class;

                   IF lc_formula_class IS NULL THEN
                      lt_per (i).record_status   := gc_error_flag;
                      gc_error_code              := '-PAT04';
                      gc_error_message           := ' ,FORMULA CLASS IS INVALID';
                      lt_per (i).error_code      := gc_error_code;
                      lt_per (i).error_message   := gc_error_message;
                   ELSE
                      lt_per (i).formula_class   := lc_formula_class;
                   END IF;
                END IF;

                IF lt_per (i).organization_code IS NULL THEN
                   lt_per (i).record_status  := gc_error_flag;
                   gc_error_code             := '-PAT04';
                   gc_error_message          := ' ,ORGANIZATION CODE IS NULL';
                   lt_per (i).error_code     := gc_error_code;
                   lt_per (i).error_message  := gc_error_message;
                ELSE
                   ln_organization_id := NULL;
                     
                   OPEN lcu_org_code (lt_per (i).organization_code);
                   FETCH lcu_org_code INTO ln_organization_id;                      
                   CLOSE lcu_org_code;
                     
                   apps.fnd_file.put_line (apps.fnd_file.LOG,lt_per (i).organization_code || ': ' || ln_organization_id);
                      
                   IF ln_organization_id IS NULL THEN
                      lt_per (i).record_status   := gc_error_flag;
                      gc_error_code              := '-PAT04';
                      gc_error_message           := ' ,ORGANIZATION CODE IS INVALID';
                      lt_per (i).error_code      := gc_error_code;
                      lt_per (i).error_message   := gc_error_message;
                   ELSE
                      lt_per (i).organization_id := ln_organization_id;
                   END IF;
                END IF;
               

                IF lt_per (i).line_type IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,LINE TYPE IS NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                ELSE
                  
                   if lt_per (i).line_type = '1' or lt_per (i).line_type = '0' or lt_per (i).line_type = '2' or lt_per (i).line_type = '-1' then
                     
                      NULL;
                     
                   else
                     
                      lc_line_type := NULL;
                      OPEN  lcu_line_type (lt_per (i).line_type);
                      FETCH lcu_line_type INTO lc_line_type;
                      CLOSE lcu_line_type;

                      apps.fnd_file.put_line (apps.fnd_file.LOG,'LINE TYPE IS :' || lc_line_type);

                      IF lc_line_type IS NULL THEN
                         lt_per (i).record_status  := gc_error_flag;
                         gc_error_code             := '-PAT04';
                         gc_error_message          := ' ,LINE TYPE IS INVALID';
                         lt_per (i).error_code     := gc_error_code;
                         lt_per (i).error_message  := gc_error_message;
                      ELSE
                         lt_per (i).line_type      := lc_line_type;
                      END IF;
                   END IF;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Item no');

                IF lt_per (i).item_no IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,ITEM NO IS REQUIRED';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                ELSE
                   ln_inventory_item_id := NULL;

                   OPEN lcu_item_no (lt_per (i).item_no); --,lt_per (i).organization_code);
                   FETCH lcu_item_no  INTO ln_inventory_item_id;
                   CLOSE lcu_item_no;

                   apps.fnd_file.put_line(apps.fnd_file.log,lt_per (i).item_no||':'||lt_per (i).organization_code||':'||ln_inventory_item_id);

                   IF ln_inventory_item_id IS NULL THEN
                      lt_per (i).record_status  := gc_error_flag;
                      gc_error_code             := '-PAT04';
                      gc_error_message          := ' ,ITEM_NUMBER IS INVALID';
                      lt_per (i).error_code     := gc_error_code;
                      lt_per (i).error_message  := gc_error_message;
                   ELSE
                      lt_per (i).inventory_item_id := ln_inventory_item_id;
                   END IF;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Required Quantity');

                IF lt_per (i).required_qty IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,REQUIRED_QTY IS NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Item UOM:'|| lt_per (i).item_uom);

                IF lt_per (i).item_uom IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,ITEM_UOM IS NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                ELSE
                   apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK1');
                   lc_uom_code := NULL;
                   
                   OPEN lcu_uom (lt_per (i).item_uom);
                   FETCH lcu_uom INTO lc_uom_code;
                   CLOSE lcu_uom;
                  
                   IF lc_uom_code IS NULL THEN
                      lt_per (i).record_status := gc_error_flag;
                      gc_error_code := '-PAT04';
                      gc_error_message := ' , UOM '|| lt_per (i).item_uom ||' IS INVALID';
                      lt_per (i).error_code := gc_error_code;
                      lt_per (i).error_message := gc_error_message;
                   ELSE
                       lt_per (i).item_uom := lc_uom_code;
                  
                       IF lt_per (i).contribute_to_yield = 'Y' OR lt_per (i).contribute_to_yield = 'Yes' THEN
                      
                          OPEN lcu_item_uom (lt_per(i).item_no);
                          FETCH lcu_item_uom INTO lc_item_uom_code;
                          CLOSE lcu_item_uom;

                          SELECT uom_code
                            INTO lc_mass_uom
                            FROM apps.mtl_units_of_measure
                           WHERE 1=1
                             AND UPPER(uom_class) =  UPPER(gc_mass_uom_class)
                             AND UPPER(base_uom_flag)    =  'Y';
                      
                          SELECT apps.inv_convert.inv_um_convert (lt_per(i).inventory_item_id, 5, 1,lc_mass_uom,lc_uom_code,null,null)
                            INTO ln_conversion_value
                            FROM dual;

                          apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK2');

                          IF ln_conversion_value IS NULL OR ln_conversion_value = -99999 THEN
                             lt_per (i).record_status  := gc_error_flag;
                             gc_error_code             := '-PAT04';
                             gc_error_message          := ' ,UOM CONVERSION(MASS/UOM) BETWEEN '||lc_mass_uom||' , '||lc_uom_code||' IS INVALID FOR ITEM :'||lt_per (i).item_no;
                             lt_per (i).error_code     := gc_error_code;
                             lt_per (i).error_message  := gc_error_message;
                          END IF;

                          SELECT apps.inv_convert.inv_um_convert (lt_per(i).inventory_item_id, 5, 1, lc_uom_code, lc_item_uom_code,null,null)
                            INTO ln_conversion_value
                            FROM dual;
                      
                          apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK2');

                          IF ln_conversion_value IS NULL OR ln_conversion_value = -99999 THEN
                             lt_per (i).record_status := gc_error_flag;
                             gc_error_code            := '-PAT04';
                             gc_error_message         := ' ,UOM CONVERSION(UOM/ITEM UOM) BETWEEN '|| lc_uom_code||' , '|| lc_item_uom_code ||' IS INVALID FOR ITEM :'||lt_per (i).item_no;
                             lt_per (i).error_code    := gc_error_code;
                             lt_per (i).error_message := gc_error_message;
                          END IF;
                       END IF;                  
                   END IF;
                  
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK3');

                IF lt_per (i).contribute_to_yield IS NULL THEN
                   /*
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,CONTRIBUTE_TO_YIELD IS NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;                   */
                   lt_per (i).contribute_to_yield := 'Y';
                ELSE
                   IF lt_per (i).contribute_to_yield = 'Y' or lt_per (i).contribute_to_yield = 'N' then
                      NULL;
                   ELSE
                      lc_cont_to_yield := NULL;
                   
                      OPEN lcu_cont_to_yield (lt_per (i).contribute_to_yield);
                      FETCH lcu_cont_to_yield INTO lc_cont_to_yield;
                      CLOSE lcu_cont_to_yield;

                      IF lc_cont_to_yield IS NULL THEN
                         lt_per (i).record_status  :=      gc_error_flag;
                         gc_error_code             :=      '-PAT04';
                         gc_error_message          :=      ' ,CONTRIBUTE_TO_YIELD IS INVALID';
                         lt_per (i).error_code     :=      gc_error_code;
                         lt_per (i).error_message  :=      gc_error_message;
                      ELSE
                         apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK6');
                         lt_per (i).contribute_to_yield := lc_cont_to_yield;
                      END IF;
                   END IF;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK7');
                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Cost Allocation');

                IF lt_per (i).cost_allocation IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,COST_ALLOCATION IS NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                END IF;
               
                IF lt_per(i).ingredient_type ='API' THEN
                    apps.fnd_file.put_line(apps.fnd_file.LOG,'Ingredient Type API validation');
                
                    IF lt_per(i).standard_potency IS NULL THEN
                       lt_per (i).record_status     := gc_error_flag;
                       gc_error_code                := '-PAT04';
                       gc_error_message             := ' ,STANDARD POTENCY IS NULL';
                       lt_per (i).error_code        := gc_error_code;
                       lt_per (i).error_message     := gc_error_message;
                    ELSE
                       NULL;
                       /*
                       IF lt_per(i).standard_potency NOT BETWEEN 0 AND 100 THEN
                          lt_per (i).record_status     := gc_error_flag;
                          gc_error_code                := '-PAT04';
                          gc_error_message             := ' ,STANDARD POTENCY IS INVALID';
                          lt_per (i).error_code        := gc_error_code;
                          lt_per (i).error_message     := gc_error_message;                               
                       END IF;
                       */
                    END IF;
                
                    IF lt_per(i).POTENCY_UOM IS NULL THEN
                       lt_per (i).record_status     := gc_error_flag;
                       gc_error_code                := '-PAT04';
                       gc_error_message             := ' ,POTENCY UOM IS NULL';
                       lt_per (i).error_code        := gc_error_code;
                       lt_per (i).error_message     := gc_error_message;
                    END IF;
                END IF;

                --=================================
                  -- Identifying the success records
                 --=================================
                
                IF lt_per(i).record_status <> gc_error_flag THEN
                   lt_per(i).record_status := gc_validation_flag;
                   lt_per(i).error_message := 'All Validations Passed';
                   lt_per(i).error_code    := NULL;
                ELSE
                   lt_per (i).record_status := gc_validation_error_flag;
                END IF;

             END LOOP;

             --EXIT WHEN lcu_validate_formula_data%NOTFOUND;

             --===================================================================
             --Bulk Update the records withe status flag, validation error message
             --===================================================================
             ln_bulk_error_cnt := 0;

             BEGIN
               
                FORALL i IN 1 .. lt_per.COUNT SAVE EXCEPTIONS
                   UPDATE  xxblr_opm_formula_stg
                      SET  record_status         =      lt_per (i).record_status
                         , error_message         =      lt_per (i).error_message
                         , error_code            =      lt_per (i).error_code
                         , formula_class         =      lt_per (i).formula_class
                         , line_type             =      lt_per (i).line_type
                         , item_uom              =      lt_per (i).item_uom
                         , contribute_to_yield   =      lt_per (i).contribute_to_yield
                         , organization_id       =      lt_per (i).organization_id
                         , inventory_item_id     =      lt_per (i).inventory_item_id
                         , last_updated_date     =      gd_current_date
                         , formula_vers          =      lt_per (i).formula_vers
                         , last_updated_by       =      gn_user_id
                         , last_update_login     =      gn_login_id
                    WHERE  transaction_id        =      lt_per (i).transaction_id;
                COMMIT;
             EXCEPTION
                WHEN OTHERS
                THEN
                   apps.fnd_file.put_line (apps.fnd_file.LOG,'   Bulk Update Error Message :-> '|| SQLERRM);
                   ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

                   FOR i IN 1 .. ln_bulk_error_cnt
                   LOOP
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
                   END LOOP;
             END;

          END LOOP;
          CLOSE lcu_validate_formula_data;
     
          OPEN lcu_valrec_cnt ('V');                        --gc_validation_flag);
          FETCH lcu_valrec_cnt INTO ln_valrec_cnt;
          CLOSE lcu_valrec_cnt;

          apps.fnd_file.put_line (apps.fnd_file.LOG,'   Number Of Records Validated       :-> '|| ln_valrec_cnt);
          apps.fnd_file.put_line (apps.fnd_file.LOG,'   Number Of Records Failured        :-> '|| (ln_trnsfrec_cnt - ln_valrec_cnt));
          apps.fnd_file.put_line (apps.fnd_file.LOG,'   ------ Procedure validate_formula_data Exit------');
          apps.fnd_file.put_line (apps.fnd_file.LOG,'   No Data Found to be Process ');
      END IF;                

   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VALIDATE- :> ' || SQLERRM || ', ' || SQLCODE );
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure validate_formula_data Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END validate_formula_data;