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;