Friday 29 November 2013

opm recipe uploading through api script

PROCEDURE create_recipe (p_create_recipe IN VARCHAR2)
   IS
  
--local variables
      l_recipe_tbl           apps.gmd_recipe_header.recipe_tbl;
      l_recipe_flex          apps.gmd_recipe_header.recipe_flex;
      l_recipe_vr_tbl        apps.gmd_recipe_detail.recipe_vr_tbl;
      l_recipe_flex_tbl      apps.gmd_recipe_detail.recipe_flex;
      l_recipe_mtl_tbl       apps.gmd_recipe_detail.recipe_mtl_tbl;
      l_return_status        VARCHAR2 (10);
      l_msg_ind              VARCHAR2 (240);
      l_msg_data             VARCHAR2 (2000);
      l_msg_count            NUMBER;     
      l_count                NUMBER;
     
      ln_formula_no          apps.fm_form_mst.formula_no%TYPE;
      ln_formula_vers        apps.fm_form_mst.formula_vers%TYPE;
      ln_routing_no          apps.fm_rout_hdr.routing_no%TYPE;
      ln_routing_vers        apps.fm_rout_hdr.routing_vers%TYPE;
      ln_progress            NUMBER                                := 0;
      ln_suc_rec_cnt         NUMBER                                := 0;
      ln_rej_rec_cnt         NUMBER                                := 0;
      ln_count               NUMBER;
      ln_organization_id     NUMBER;
      ln_formula_id          NUMBER;
      ln_routing_id          NUMBER;
      ln_recipe_type         NUMBER;
      ln_routingstep_id      NUMBER;
      ln_inventory_item_id   NUMBER;
      ln_required_qty        NUMBER;
      ln_recipe_id           NUMBER;
      ln_recipe_version      NUMBER;
      ln_formulaline_id      NUMBER;
      ln_routstep_id         NUMBER;
      lc_recipe_no           VARCHAR2 (15);
      lc_uom_code            VARCHAR2 (20);
      lc_segment1            VARCHAR2 (40);
      lc_organization_code   VARCHAR2 (10);

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

--===============================
--Cursor to get Recipe Data
--===============================
 
     CURSOR lcu_recipe_data
         IS            
         SELECT  transaction_id
               , sr_no
               , new_recipe_no recipe_no
               , recipe_version
               , recipe_description
               , new_formula_no formula_no
               , formula_version
               , new_routing_no routing_no
               , routing_version
               , owner_org_code
               , recipe_validity_date
               , recipe_type
               , formula_id
               , routing_id
               , organization_id
               , process_flag
               , error_code
               , record_status
               , error_message
               , last_updated_by
               , created_by
               , last_update_date
               , creation_date
               , last_update_login
           FROM xxblr_opm_recipe_stg
          WHERE 1=1           
       GROUP BY transaction_id
               , sr_no
               , new_recipe_no
               , recipe_version
               , recipe_description
               , new_formula_no
               , formula_version
               , new_routing_no
               , routing_version
               , owner_org_code
               , recipe_validity_date
               , recipe_type
               , formula_id
               , routing_id
               , organization_id
               , process_flag
               , error_code
               , record_status
               , error_message
               , last_updated_by
               , created_by
               , last_update_date
               , creation_date
               , last_update_login;

--===============================
--Cursor to get Formula Line Id
--===============================         
   CURSOR lcu_recipe_id(p_recipe_no varchar2,p_recipe_version number)
   IS
   SELECT recipe_id
     FROM apps.gmd_recipes_b
    WHERE 1=1
      AND recipe_no       = p_recipe_no
      AND recipe_version  = p_recipe_version;
  
--===============================
--Cursor to get Issue to Step
--===============================            

 -------------  
    CURSOR lcu_issue_to_step(p_formula_id number,p_routing_id  number)
     IS  
        SELECT fmd.formulaline_id
             , frd.routingstep_id
          FROM xxblr_opm_formula_stg xrof
             , apps.fm_form_mst ffm
             , apps.fm_matl_dtl fmd
             , apps.mtl_system_items_b msi
             , apps.fm_rout_dtl frd
         WHERE 1=1
           AND xrof.new_formula_no        =  ffm.formula_no
           AND xrof.formula_vers          =  ffm.formula_vers
           AND ffm.formula_id             =  p_formula_id
           AND fmd.formula_id             =  ffm.formula_id
           AND (    fmd.inventory_item_id =  msi.inventory_item_id
                and msi.organization_id   =  g_master_org
                and msi.segment1          =  xrof.new_item_no
               )
           AND fmd.qty                    =  xrof.required_qty
           AND frd.routing_id             =  p_routing_id
           AND frd.routingstep_no         =  xrof.issue_to_step
      GROUP BY fmd.formulaline_id, frd.routingstep_id          
      ORDER BY fmd.formulaline_id,frd.routingstep_id;
--------------- 
    
--===============================
--Cursor to get RoutingStep Id
--===============================
      CURSOR lcu_routstep_id (p_recipe_no VARCHAR2, p_recipe_vers NUMBER)
      IS
         SELECT frd.routingstep_id
           FROM apps.fm_rout_dtl frd,
                apps.gmd_recipes grb
          WHERE 1=1
            AND frd.routing_id     = grb.routing_id
            AND grb.recipe_no      = p_recipe_no
            AND grb.recipe_version = p_recipe_vers;

      TYPE tbl_recipe_data IS TABLE OF lcu_recipe_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_per                 tbl_recipe_data;
     
   BEGIN
      apps.fnd_file.put_line(apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing CREATE_RECIPE ---------');
      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_RECIPE ---------');
      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 Loaded :' || ln_count);

            -------------------------------------------------------------------------
            -- Bulk Insert Data into Recipe.
            --------------------------------------------------------------------------
      OPEN lcu_recipe_data;
      LOOP
         lt_per.DELETE;
         gn_bulk_err := 0;

         FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;
         IF lt_per.COUNT > 0
         THEN
            apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);

            FOR i IN lt_per.first .. lt_per.last
            LOOP
              
              apps.fnd_file.put_line(apps.fnd_file.LOG,'Recipe No:'||lt_per (i).recipe_no);
              apps.fnd_file.put_line(apps.fnd_file.LOG,'Rouitng Id:'||lt_per (i).routing_id);
                             
               l_recipe_tbl (1).recipe_id                   := NULL;
               l_recipe_tbl (1).recipe_description          := lt_per (i).recipe_description;
               l_recipe_tbl (1).recipe_no                   := lt_per (i).recipe_no;
               l_recipe_tbl (1).recipe_version              := lt_per (i).recipe_version;
               l_recipe_tbl (1).user_id                     := l_user_id;
               l_recipe_tbl (1).user_name                   := l_user_name;
               l_recipe_tbl (1).owner_orgn_code             := lt_per (i).owner_org_code;              
               l_recipe_tbl (1).creation_orgn_code          := lt_per (i).owner_org_code;
               l_recipe_tbl (1).owner_organization_id       := lt_per (i).organization_id;
               l_recipe_tbl (1).creation_organization_id    := lt_per (i).organization_id;
               l_recipe_tbl (1).formula_id                  := lt_per (i).formula_id;
               l_recipe_tbl (1).formula_no                  := lt_per (i).formula_no;
               l_recipe_tbl (1).formula_vers                := lt_per (i).formula_version;
               l_recipe_tbl (1).routing_id                  := lt_per (i).routing_id;
               l_recipe_tbl (1).routing_no                  := lt_per (i).routing_no;
               l_recipe_tbl (1).routing_vers                := lt_per (i).routing_version;                           
               l_recipe_tbl (1).project_id                  := NULL;
               l_recipe_tbl (1).recipe_status               := gn_status;
               l_recipe_tbl (1).planned_process_loss        := 0;
               l_recipe_tbl (1).text_code                   := NULL;
               l_recipe_tbl (1).delete_mark                 := 0;
               l_recipe_tbl (1).contiguous_ind              := 0;
               l_recipe_tbl (1).enhanced_pi_ind             := 1;
               l_recipe_tbl (1).recipe_type                 := lt_per(i).recipe_type;              
               l_recipe_tbl (1).creation_date               := gd_sysdate;
               l_recipe_tbl (1).created_by                  := l_user_id;
               l_recipe_tbl (1).last_updated_by             := l_user_id;              
               l_recipe_tbl (1).last_update_date            := gd_sysdate;
               l_recipe_tbl (1).last_update_login           := gn_login_id;              
               l_recipe_tbl (1).owner_id                    := l_user_id;
               l_recipe_tbl (1).owner_lab_type              := NULL;              
               l_recipe_tbl (1).calculate_step_quantity     := 1;
                             
               apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_HEADER');

               BEGIN
                  apps.gmd_recipe_header.create_recipe_header
                                       (p_api_version             => '1.0',
                                        p_init_msg_list           => 'T',
                                        p_commit                  => 'F',
                                        p_called_from_forms       => 'NO',
                                        x_return_status           => l_return_status,
                                        x_msg_count               => l_msg_count,
                                        x_msg_data                => l_msg_data,
                                        p_recipe_header_tbl       => l_recipe_tbl,
                                        p_recipe_header_flex      => l_recipe_flex
                                       );
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-' || l_return_status || ' : ' || l_msg_data);
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
               END;

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

                  FOR i IN 1 .. l_msg_count
                  LOOP
                     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.6' || l_msg_data);
                  END LOOP;
               ELSE
                  ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
               END IF;

               apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 14');
              
                    ------------------------------------------------------------------------------------------------------------------------
                    --- INITIATING THE VALIDATE RULE CREATION API WHICH WILL UPDATE THE GMD_RECIPE_VALIDITY_RULES TABLE
                    -------------------------------------------------------------------------------------------------------------------------
             
               BEGIN
             
                  BEGIN
                     SELECT fmd.inventory_item_id
                          , ffm.total_output_qty
                          , ffm.yield_uom
                       INTO ln_inventory_item_id
                          , ln_required_qty
                          , lc_uom_code
                       FROM apps.fm_form_mst ffm
                           , apps.fm_matl_dtl fmd                           
                      WHERE 1=1
                        AND ffm.formula_id  =  fmd.formula_id
                        AND ffm.formula_id  =  l_recipe_tbl (1).formula_id
                        AND fmd.line_type   =  1
                        AND fmd.line_no     =  1;                     
                   
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'INVENTORY ITEM ID IS INVALID'|| ln_inventory_item_id);
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'REQUIRED QTY IS INVALID'     || ln_required_qty);
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'UOM CODE IS INVALID'         || lc_uom_code);
                     WHEN OTHERS
                     THEN
                        ln_inventory_item_id := NULL;
                        lc_uom_code          := NULL;
                        ln_required_qty      := NULL;
                  END;

                  ln_recipe_id := NULL;
                  OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
                  FETCH lcu_recipe_id
                   INTO ln_recipe_id;
                  CLOSE lcu_recipe_id;

                  l_count := 1;
                 
                  l_recipe_vr_tbl (l_count).recipe_validity_rule_id     := NULL;
                  l_recipe_vr_tbl (l_count).recipe_id                   := ln_recipe_id;
                  l_recipe_vr_tbl (l_count).recipe_no                   := lt_per (i).recipe_no;
                  l_recipe_vr_tbl (l_count).recipe_version              := lt_per (i).recipe_version;
                  l_recipe_vr_tbl (l_count).user_id                     := l_user_id;
                  l_recipe_vr_tbl (l_count).user_name                   := l_user_name;
                  l_recipe_vr_tbl (l_count).orgn_code                   := lt_per (i).owner_org_code;
                  l_recipe_vr_tbl (l_count).inventory_item_id           := ln_inventory_item_id;
                  l_recipe_vr_tbl (l_count).revision                    := NULL;
                  l_recipe_vr_tbl (l_count).item_no                     := NULL; --lc_segment1;
                  l_recipe_vr_tbl (l_count).recipe_use                  := 0;
                  l_recipe_vr_tbl (l_count).preference                  := 1;
                  l_recipe_vr_tbl (l_count).start_date                  := NULL;
                  l_recipe_vr_tbl (l_count).end_date                    := NULL;
                  l_recipe_vr_tbl (l_count).min_qty                     := 0;
                  l_recipe_vr_tbl (l_count).max_qty                     := 999999999;
                  l_recipe_vr_tbl (l_count).std_qty                     := ln_required_qty;
                  l_recipe_vr_tbl (l_count).detail_uom                  := lc_uom_code;
                  l_recipe_vr_tbl (l_count).inv_min_qty                 := NULL;
                  l_recipe_vr_tbl (l_count).inv_max_qty                 := NULL;
                  l_recipe_vr_tbl (l_count).text_code                   := NULL;
                  l_recipe_vr_tbl (l_count).created_by                  := l_user_id;
                  l_recipe_vr_tbl (l_count).creation_date               := gd_sysdate;
                  l_recipe_vr_tbl (l_count).last_updated_by             := l_user_id;
                  l_recipe_vr_tbl (l_count).last_update_date            := gd_sysdate;
                  l_recipe_vr_tbl (l_count).last_update_login           := l_user_id;
                  l_recipe_vr_tbl (l_count).delete_mark                 := 0;
                  l_recipe_vr_tbl (l_count).planned_process_loss        := NULL;
                  l_recipe_vr_tbl (l_count).validity_rule_status        := gn_status;
                  l_recipe_vr_tbl (l_count).organization_id             := lt_per(i).organization_id;
                  l_recipe_vr_tbl (l_count).fixed_process_loss          := NULL;
                  l_recipe_vr_tbl (l_count).fixed_process_loss_uom      := NULL;
                 
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_VR');

                  BEGIN
                     apps.gmd_recipe_detail.create_recipe_vr
                                                   (  p_api_version            => '1.0'
                                                    , p_init_msg_list          => 'F'
                                                    , p_commit                 => 'F'
                                                    , p_called_from_forms      => 'NO'
                                                    , x_return_status          => l_return_status
                                                    , x_msg_count              => l_msg_count
                                                    , x_msg_data               => l_msg_data
                                                    , p_recipe_vr_tbl          => l_recipe_vr_tbl
                                                    , p_recipe_vr_flex         => l_recipe_flex_tbl
                                                   );
                     apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RETURN_STATUS:-'|| l_return_status);
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
                  END;

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

                     FOR i IN 1 .. l_msg_count
                     LOOP
                        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.6' || l_msg_data);
                     END LOOP;
                  ELSE
                     ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
                  END IF;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Recipe Validity Rule Proc:'|| SQLCODE|| SQLERRM);
               END;
              
               --=================STEP/MATERIAL ASSOCIATION =========================----------

               BEGIN
                      
                  ln_recipe_id := NULL;

                  OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
                  FETCH lcu_recipe_id INTO ln_recipe_id;
                  CLOSE lcu_recipe_id;                 

                  ln_count := 0;
                  l_recipe_mtl_tbl.delete;
                 
                  FOR c_cur IN lcu_issue_to_step(lt_per (i).formula_id, lt_per (i).routing_id)
                  LOOP
                      ln_count := ln_count + 1;
                      l_recipe_mtl_tbl (ln_count).recipe_id          := ln_recipe_id;
                      l_recipe_mtl_tbl (ln_count).recipe_no          := lt_per (i).recipe_no;
                      l_recipe_mtl_tbl (ln_count).recipe_version     := lt_per (i).recipe_version;
                      l_recipe_mtl_tbl (ln_count).user_id            := l_user_id;
                      l_recipe_mtl_tbl (ln_count).user_name          := l_user_name;
                      l_recipe_mtl_tbl (ln_count).formulaline_id     := c_cur.formulaline_id;
                      l_recipe_mtl_tbl (ln_count).text_code          := NULL;
                      l_recipe_mtl_tbl (ln_count).creation_date      := gd_sysdate;
                      l_recipe_mtl_tbl (ln_count).created_by         := l_user_id;
                      l_recipe_mtl_tbl (ln_count).last_updated_by    := l_user_id;
                      l_recipe_mtl_tbl (ln_count).last_update_date   := gd_sysdate;
                      l_recipe_mtl_tbl (ln_count).last_update_login  := l_user_id;
                      l_recipe_mtl_tbl (ln_count).routingstep_id     := c_cur.routingstep_id;
                     
                   /*  
                      UPDATE  xxblr_opm_formula_stg
                         SET  formulaline_id         = c_cur.formulaline_id
                            , routingstep_id         = c_cur.routingstep_id
                       WHERE  1=1
                         AND  new_formula_no         = lt_per (i).formula_no
                         AND  formula_vers           = lt_per (i).formula_version;
                     */                                                                                                                                                      
                  EXIT WHEN lcu_issue_to_step%NOTFOUND;
                 
                  END LOOP;
                 
               --   COMMIT;
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_MTL');

                  BEGIN
                     apps.gmd_recipe_detail.create_recipe_mtl
                                                          (  p_api_version            => '1.0'
                                                           , p_init_msg_list          => 'F'
                                                           , p_commit                 => 'F'
                                                           , p_called_from_forms      => 'NO'
                                                           , x_return_status          => l_return_status
                                                           , x_msg_count              => l_msg_count
                                                           , x_msg_data               => l_msg_data
                                                           , p_recipe_mtl_tbl         => l_recipe_mtl_tbl
                                                           , p_recipe_mtl_flex        => l_recipe_flex_tbl
                                                          );
                                      
                     apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API  RETURN_STATUS:-'|| l_return_status);
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
                  END;

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

                     FOR i IN 1 .. l_msg_count
                     LOOP
                        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.6' || l_msg_data);
                     END LOOP;
                  ELSE
                     ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
                  END IF;
                     
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Step Material Association Proc:'|| SQLCODE|| SQLERRM);
               END;
                        
               BEGIN
                   UPDATE  xxblr_opm_recipe_stg
                      SET  record_status    = l_return_status
                         , error_code       = l_msg_count
                         , error_message    = l_msg_data
                    WHERE  new_recipe_no    = lt_per (i).recipe_no
                      AND  recipe_version   = lt_per (i).recipe_version;
                 
                
                   UPDATE  xxblr_opm_formula_stg
                      SET  record_status_recipe   = l_return_status
                         , error_code_recipe      = l_msg_count
                         , error_message_recipe   = l_msg_data
                    WHERE  new_formula_no         = lt_per (i).formula_no
                      AND  formula_vers           = lt_per (i).formula_version;   
                     
               EXCEPTION
                     WHEN OTHERS THEN
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
               END;
               COMMIT;   
                    
            END LOOP;                      
           
         END IF;
         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, ' ');
         EXIT WHEN lcu_recipe_data%NOTFOUND;
      END LOOP;
      CLOSE lcu_recipe_data;
   EXCEPTION
      WHEN OTHERS THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-RECEPI :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure CREATE_RECIPE 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_recipe;

OPM create recipe api validations and Code

PROCEDURE validate_recipe_data (p_validate_recipe IN VARCHAR2)
   IS
      --Local Variables
      lc_formula_num      VARCHAR2(25);
      lc_routing_num      VARCHAR2(25);
     
      ln_routing_id       NUMBER;
      ln_routstep_id      NUMBER;
      ln_formula_id       NUMBER;     
      ln_formulaline_id   NUMBER;
      ln_recipe_type      NUMBER;
      ln_valrec_cnt       NUMBER (10);
      ln_trnsfrec_cnt     NUMBER (10);
      ln_recipe_no        VARCHAR2 (30);
      ln_routing_vers     NUMBER (10);
      ln_org_id           NUMBER (10);
      ln_bulk_error_cnt   NUMBER (20);
      ln_formula_vers     NUMBER (10);
      ln_rout_max_vers    NUMBER (10);
      ln_ffm_max_vers     NUMBER (10);
      ln_max_vers         NUMBER (10);
      lv_record_status_recipe     VARCHAR2 (2);
      lv_error_code_recipe        VARCHAR2 (30);
      lv_error_message_recipe     VARCHAR2 (500);
--===============================
--Cursor to get Recipe Data
--===============================
      CURSOR lcu_recipe_data
      IS
         SELECT  transaction_id
               , sr_no
               , new_recipe_no recipe_no
               , recipe_version
               , recipe_description
               , new_formula_no formula_no
               , formula_version
               , new_routing_no routing_no
               , routing_version
               , owner_org_code
               , recipe_validity_date
               , recipe_type
               , formula_id
               , routing_id
               , organization_id
               , process_flag
               , error_code
               , record_status
               , error_message
               , last_updated_by
               , created_by
               , last_update_date
               , creation_date
               , last_update_login
           FROM xxblr_opm_recipe_stg
          WHERE 1=1
            AND (   NVL(record_status, 'N') = gc_newrecord_flag
                 OR NVL(record_status, 'N') = gc_validation_error_flag)  
       ORDER BY new_recipe_no;  

--===============================
--Cursor to get Recipe Number
--===============================
 /* cursor lcu_recipe_no(cp_recipe_no VARCHAR2)
 is
 select recipe_no
FROM apps.gmd_recipes
where recipe_no =cp_recipe_no; */

      --=====================================
--Cursor to get Recipe Num With Version
--=====================================
      CURSOR lcu_recipe_no (cp_recipe_no VARCHAR2, cp_recipe_vers NUMBER)
      IS
         SELECT recipe_no
           FROM apps.gmd_recipes
          WHERE 1=1
            AND recipe_no      = cp_recipe_no
            AND recipe_version = cp_recipe_vers;

--===============================
--Cursor to get Formula Number
--===============================
      CURSOR lcu_formula_no (cp_formula_no VARCHAR2)
      IS
         SELECT formula_no
           FROM apps.fm_form_mst
          WHERE 1=1
            AND formula_no = cp_formula_no;
         
--===============================
--Cursor to get Formula Line Id
--===============================
         
      CURSOR lcu_formulaline_id (p_formula_no VARCHAR2, p_formula_vers NUMBER)
      IS           
           SELECT fmd.formulaline_id
             FROM apps.fm_form_mst_b ffb,
                  apps.fm_matl_dtl fmd
            WHERE 1=1
              AND ffb.formula_id     = fmd.formula_id
              AND ffb.formula_no     = p_formula_no
              AND ffb.formula_vers   = p_formula_vers
         ORDER BY fmd.formulaline_id;

--=====================================
--Cursor to get Formula Num with Version
--======================================
      CURSOR lcu_formula_num (
                                 cp_formula_no        VARCHAR2,
                                 cp_formula_version   NUMBER
                             )
      IS                 
         SELECT formula_no,formula_id
           FROM apps.fm_form_mst
          WHERE 1=1
            AND formula_no   =  cp_formula_no
            AND formula_vers =  cp_formula_version;

--===============================
--Cursor to get Routing Number
--===============================
      CURSOR lcu_routing_no (cp_routing_no VARCHAR2)
      IS
         SELECT routing_no
           FROM apps.fm_rout_hdr
          WHERE 1=1
            AND routing_no = cp_routing_no;

--===================================
--Cursor to get Routing Num with Vers
--===================================
      CURSOR lcu_routing_num (cp_routing_no VARCHAR2, cp_routing_vers NUMBER)
      IS           
         SELECT routing_no,
                routing_id
           FROM apps.fm_rout_hdr
          WHERE 1=1
            AND routing_no   =  cp_routing_no
            AND routing_vers =  cp_routing_vers;

--===================================
--Cursor to get Routing Num with Vers
--===================================
      CURSOR lcu_routstep_id (p_routing_no VARCHAR2,p_routing_vers NUMBER)
      IS                                   
         SELECT  frd.routingstep_id
           FROM apps.fm_rout_hdr frh,
                apps.fm_rout_dtl frd                              
          WHERE 1=1
            AND frh.routing_id        =     frd.routing_id                   
            AND frh.routing_no        =     p_routing_no                         
            AND frh.routing_vers      =     p_routing_vers;
           
--===============================
--Cursor to get Org ID
--===============================
      CURSOR lcu_org_id (cp_owner_org_code VARCHAR2)
      IS
         SELECT organization_id
           FROM apps.org_organization_definitions
          WHERE 1=1
            AND UPPER(organization_code) = UPPER(cp_owner_org_code);

      -- Cursor to get Recipe Validity date

      --===============================
--Cursor to get Recipe Type
--===============================
     
    CURSOR lcu_recipe_type(p_recipe_type varchar2)
    IS
    SELECT lookup_code
      FROM apps.fnd_lookup_values
     WHERE 1=1
       AND UPPER(lookup_type)     = 'GMD_RECIPE_TYPE'                                     
       AND (   UPPER(meaning)     = UPPER (p_recipe_type)
            OR UPPER(lookup_code) = p_recipe_type );
   
--================================
--Cursor to get Max Recipe Version
--================================
      CURSOR lcu_max_vers
      IS
         SELECT MAX (recipe_version)
           FROM apps.gmd_recipes;

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

 --=================================
--Cursor to get Max Routing Version
--=================================
      CURSOR lcu_rout_max_vers
      IS
         SELECT MAX (routing_vers)
           FROM apps.fm_rout_hdr;

--===============================
--Cursor to get Valid Records
--===============================
      CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
      IS
         SELECT COUNT (1)
           FROM xxblr_opm_recipe_stg
          WHERE 1=1
            AND record_status = cp_record_status;
           
----------
--=========================================================================
--Cursor to Update formula line id and routing id in formula staging table
--==========================================================================            
   
    CURSOR lcu_updt_form_rout_id(p_formula_id number,p_routing_id  number)
     IS  
        SELECT fmd.formulaline_id
             , frd.routingstep_id
--             , xrof.new_item_no
--             , xrof.issue_to_step
             , xrof.transaction_id  
          FROM xxblr_opm_formula_stg xrof
             , apps.fm_form_mst ffm
             , apps.fm_matl_dtl fmd
             , apps.mtl_system_items_b msi
             , apps.fm_rout_dtl frd
         WHERE 1=1
           AND xrof.new_formula_no        =  ffm.formula_no
           AND xrof.formula_vers          =  ffm.formula_vers
           AND ffm.formula_id             =  p_formula_id
           AND fmd.formula_id             =  ffm.formula_id
           AND (    fmd.inventory_item_id =  msi.inventory_item_id
                and msi.organization_id   =  g_master_org 
                and msi.segment1          =  xrof.new_item_no
               )
           AND fmd.qty                    =  xrof.required_qty
           AND frd.routing_id             =  p_routing_id
           AND frd.routingstep_no         =  xrof.issue_to_step
      ORDER BY xrof.transaction_id;
--------------- 

      TYPE per_tbl_type IS TABLE OF lcu_recipe_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_RECIPE_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_RECIPE_DATA ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

      OPEN lcu_recipe_data;

      LOOP
         lt_per.DELETE;

         FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;

         FOR i IN lt_per.FIRST .. lt_per.LAST
         LOOP
            apps.fnd_file.put_line (apps.fnd_file.LOG,'Transaction ID:-'|| lt_per (i).transaction_id);
            --===============================
            -- Applying Transformation rules
            --===============================
            apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number');

            IF lt_per (i).recipe_no IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',RECIPE  NUM 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 recipe Version');
           
            IF lt_per (i).recipe_version IS NULL
            THEN
               lt_per (i).recipe_version := 1;
            END IF;
            ln_recipe_no :=NULL;
           
            apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
           
             OPEN lcu_recipe_no (lt_per (i).recipe_no,lt_per (i).recipe_version);
             FETCH lcu_recipe_no
             INTO ln_recipe_no;
             CLOSE lcu_recipe_no;
           
            apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid_1.0');
           
            IF ln_recipe_no IS NOT NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',RECIPE NUMBER ALREADY EXISTED';
               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 recipe Number descrp');
           
            IF lt_per (i).recipe_description IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',RECIPE DESCRIPTION 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 formula_no');
           
            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;
            END IF;

            apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for formula Version');
           
            IF lt_per (i).formula_version IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',FORMULA VERSION 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,'Formula version :'|| lt_per (i).formula_version);

               OPEN lcu_formula_num (lt_per (i).formula_no,
                                     lt_per (i).formula_version
                                    );
               FETCH lcu_formula_num
                INTO lc_formula_num,ln_formula_id;
               CLOSE lcu_formula_num;

               IF lc_formula_num IS  NULL THEN
                  lt_per (i).record_status  := gc_error_flag;
                  gc_error_code             := '-PAT04';
                  gc_error_message          := ',FORMULA NO IS INVALID';
                  lt_per (i).error_code     := gc_error_code;
                  lt_per (i).error_message  := gc_error_message;
               ELSE
                  lt_per(i).formula_no      := lc_formula_num;
                  lt_per(i).formula_id      := ln_formula_id;
               END IF;
            END IF;                       

           apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for recipe Number Valid');
            IF lt_per (i).routing_no IS NULL
            THEN
               lt_per (i).record_status      := gc_error_flag;
               gc_error_code                 := '-PAT04';
               gc_error_message              := ',ROUTING NUMBER IS NULL';
               lt_per (i).error_code         := gc_error_code;
               lt_per (i).error_message      := gc_error_message;
            END IF;

            IF lt_per (i).routing_version IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',ROUTING VERSION IS NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               lc_routing_num  :=NULL;
               ln_routing_id   :=NULL;
               
                OPEN lcu_routing_num (lt_per (i).routing_no,lt_per (i).routing_version);
               FETCH lcu_routing_num
                INTO lc_routing_num,ln_routing_id;
               CLOSE lcu_routing_num;

               IF lc_routing_num IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ',ROUTING NUMBER DOESNOT EXIST';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
               ELSE
                lt_per(i).routing_no         :=     lc_routing_num;
                lt_per(i).routing_id         :=     ln_routing_id;            
               END IF;
            END IF;
         
            IF lt_per (i).recipe_validity_date IS NULL
            THEN
               lt_per (i).recipe_validity_date := '01-JAN-2011';
             /* 
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,RECIPE VALIDITY DATE IS NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;             */
            END IF;
                                                               
            IF lt_per (i).owner_org_code IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,OWNER ORG CODE IS NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               ln_org_id :=NULL;
               OPEN lcu_org_id (lt_per (i).owner_org_code);
               FETCH lcu_org_id INTO ln_org_id;
               CLOSE lcu_org_id;

               IF ln_org_id IS NULL
               THEN
                  lt_per (i).record_status  := gc_error_flag;
                  gc_error_code             := '-PAT04';
                  gc_error_message          := ' ,OWNER ORG 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_org_id;
               END IF;
            END IF;

            IF lt_per (i).recipe_type IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,RECIPE TYPE IS NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               ln_recipe_type    :=NULL;
               OPEN lcu_recipe_type(lt_per (i).recipe_type);
               FETCH lcu_recipe_type INTO ln_recipe_type;
               CLOSE lcu_recipe_type;
              
               IF ln_recipe_type IS NULL THEN
                  lt_per (i).record_status     := gc_error_flag;
                  gc_error_code                := '-PAT04';
                  gc_error_message             := ' ,RECIPE TYPE IS INVALID';
                  lt_per (i).error_code        := gc_error_code;
                  lt_per (i).error_message     := gc_error_message;
               ELSE
                  lt_per(i).recipe_type          := ln_recipe_type;
               END IF;
            END IF;
           
            --============VALIDATIONS FOR RECIPE VALIDATITY RULES==============--
            ln_formulaline_id :=NULL;
           
             OPEN lcu_formulaline_id(lt_per (i).formula_no,lt_per (i).formula_version);
            FETCH lcu_formulaline_id
             INTO ln_formulaline_id;
            CLOSE lcu_formulaline_id;
           
            IF ln_formulaline_id IS NULL THEN
               lt_per (i).record_status  := gc_error_flag;
               gc_error_code             := '-PAT04';
               gc_error_message          := ',FORMULA LINE ID IS NOT FOUND';
               lt_per (i).error_code     := gc_error_code;
               lt_per (i).error_message  := gc_error_message;
            --    ELSE
            --    lt_per (i).formulaline_id  := ln_formulaline_id;        -- commented for a while
            END IF;
           
            ln_routstep_id := NULL;
            OPEN lcu_routstep_id (lt_per (i).routing_no,lt_per (i).routing_version);           
            FETCH lcu_routstep_id INTO ln_routstep_id;
            CLOSE lcu_routstep_id;
           
            IF ln_routstep_id Is null then
               lt_per (i).record_status  := gc_error_flag;
               gc_error_code             := '-PAT04';
               gc_error_message          := ',ROUTING STEP ID IS NOT FOUND';
               lt_per (i).error_code     := gc_error_code;
               lt_per (i).error_message  := gc_error_message;
            --    ELSE
            --      lt_per (i).routstep_id      := ln_routstep_id;   
            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';
            ELSE
               lt_per (i).record_status := gc_validation_error_flag;
            END IF;
        
            BEGIN
                 FOR c_rec IN lcu_updt_form_rout_id(lt_per (i).formula_id,lt_per (i).routing_id)
                 LOOP        
   
                         UPDATE  xxblr_opm_formula_stg
                            SET  formulaline_id        =   c_rec.formulaline_id
                               , routingstep_id        =   c_rec.routingstep_id
                           --    , record_status_recipe  =   lv_record_status_recipe
                           --    , error_message_recipe  =   lv_error_message_recipe
                           --    , error_code_recipe     =   lv_error_code_recipe
                          WHERE  1=1
                            AND  transaction_id         = c_rec.transaction_id;
                 
                   EXIT WHEN   lcu_updt_form_rout_id%NOTFOUND;     
                 END LOOP;
           
            EXCEPTION
            WHEN OTHERS THEN
               apps.fnd_file.put_line (apps.fnd_file.LOG,'Formulaline id  Step id Update Error Message :-> '|| SQLERRM);
        
            END;

         END LOOP;

        --===================================================================
        --Bulk Update the records with status flag, validation error message
        --===================================================================
         ln_bulk_error_cnt := 0;
         apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 11:');

         BEGIN
           
            FORALL i IN lt_per.FIRST .. lt_per.LAST SAVE EXCEPTIONS
              
               UPDATE xxblr_opm_recipe_stg
                  SET record_status         =   lt_per (i).record_status,
                      error_message         =   lt_per (i).error_message,
                      error_code            =   lt_per (i).error_code,
                      recipe_type           =   lt_per (i).recipe_type,
                      new_formula_no        =   lt_per (i).formula_no,
                      formula_version       =   lt_per (i).formula_version,
                      formula_id            =   lt_per (i).formula_id,
                      organization_id       =   lt_per (i).organization_id,
                      recipe_validity_date  =   lt_per (i).recipe_validity_date,
                      new_routing_no        =   lt_per (i).routing_no,
                      routing_id            =   lt_per (i).routing_id,
                      recipe_version        =   lt_per (i).recipe_version,
                      last_update_date      =   gd_current_date,
                      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;
  
         EXIT WHEN lcu_recipe_data%NOTFOUND;
      END LOOP;

      CLOSE lcu_recipe_data;

      COMMIT;

      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_DATA Exit------');
   EXCEPTION
      WHEN OTHERS THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-RECP-DATA :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure VALIDATE_RECIPE_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_recipe_data;