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 Batch Release Query

OPM Batch release Code
=====================================


SET serveroutputon

DECLARE
l_batch_header gme_batch_header%ROWTYPE;
x_message_countNUMBER;
x_message_listVARCHAR2 (2000);
l_exception_material_tblgme_common_pvt.exceptions_tab;
x_return_statusVARCHAR2 (10);
x_batch_header gme_batch_header%ROWTYPE;
v_st_dateDATE;
v_com_dateDATE;
l_msg_index_outNUMBER;
BEGIN
SELECTplan_start_date,plan_cmplt_date INTOv_st_date,v_com_date
FROM gme_batch_header WHEREbatch_no= 3000046;
fnd_global.apps_initialize(user_id=> 1490,
resp_id=> 50764,resp_appl_id=> 553 );
l_batch_header.batch_type:= 0;
l_batch_header.batch_no:=3000046;
l_batch_header.plan_start_date:=v_st_date;
l_batch_header.plan_cmplt_date:=v_com_date;
l_batch_header.update_inventory_ind:= 'Y';
l_batch_header.recipe_validity_rule_id:=172;
--L_BATCH_HEADER.WIP_WHSE_CODE:=V_WHSE_CODE;
l_batch_header.actual_start_date:= SYSDATE;

gme_api_pub.release_batch (p_api_version=> 2.0,
p_validation_level=> 100,
p_init_msg_list=> fnd_api.g_false,
p_commti=> fnd_api.g_false,
x_message_count=>x_message_count,
x_message_list=>x_message_list,
x_return_status=>x_return_status,
p_batch_header_rec=>l_batch_header,
p_org_code=> 'VCP',
p_ignore_exception=> fnd_api.g_false,
p_validate_flexfields=> fnd_api.g_false,
x_batch_header_rec=>x_batch_header,
x_exception_material_tbl=>l_exception_material_tbl );
COMMTI;
IF x_return_status= FND_API.g_ret_sts_success THEN
dbms_output.put_line('Batch Released');
--l_batch_header.batch_id:=3000065;
-- SAVE THE CHANGES
--gme_api_pub.save_batch(
--p_batch_header => l_batch_header,
--x_return_status => x_return_status,
--p_commti =>fnd_api.g_true);
gme_api_pub.save_batch(p_header_id=>22001 ,
p_table=>2 ,
p_commti=> fnd_api.g_false ,
x_return_status=>x_return_status
--Bug#5584699 Changed the datatype from boolean to varchar2.,
p_clear_qty_cache=> fnd_api.g_true);
else
dbms_output.put_line('Batch Release failed'); IFx_message_count= 1 THEN
DBMS_OUTPUT.PUT_LINE('Error:'||x_message_list);
ELSE
FOR i IN 1..x_message_countLOOP FND_MSG_PUB.get (p_msg_index=>i ,
p_data=>X_message_list ,
p_msg_index_out=>l_msg_index_out);
DBMS_OUTPUT.PUT_LINE ('Error: '||x_message_list);
END LOOP;
end if;
end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;


OPM BATCH Complete Batch

setserveroutputon;

-- request Create Cost Adjustment From AP Invoice

declare
l_statusVARCHAR2 (10);
l_return_statusVARCHAR2 (1):= fnd_api.g_ret_sts_success;
l_countNUMBER ;l_record_countNUMBER (10):= 0;
l_loop_cntNUMBER (10):= 0;
l_dummy_cntNUMBER (10):= 0;
l_dataVARCHAR2 (2000);
l_msg_index_outNUMBER;
l_in_batch_header_rec gme_batch_header%ROWTYPE;
l_batch_header_rec gme_batch_header%ROWTYPE;
l_exception_material_tblgme_common_pvt.exceptions_tab;
xnumber;
BEGIN fnd_global.apps_initialize(user_id=> 1490,resp_id=> 50764,resp_appl_id=> 553 );
l_in_batch_header_rec.batch_type:= 0;
l_in_batch_header_rec.batch_id:='36001';
--batch_id
l_in_batch_header_rec.update_inventory_ind:='Y';
l_in_batch_header_rec.actual_start_date:= SYSDATE;
p_msg_index_out=>l_msg_index_out );
DBMS_OUTPUT.put_line ('Error: '||SUBSTR (l_data, 1, 255));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error: '||SUBSTR (l_data, 1, 255));
END LOOP;
END IF;
END IF;
exception when others then null;
end ;

/After Completion it has to be done
set serveroutputon;

declare
x_message_countnumber;
x_message_listvarchar2(200);
x_return_statusvarchar2(100);
x_mmt_rec mtl_material_transactions%ROWTYPE;
x_mmln_tblgme_common_pvt.mtl_trans_lots_num_tbl;
l_mmti_rec mtl_transactions_interface%ROWTYPE;
l_mmli_tblgme_common_pvt.mtl_trans_lots_inter_tbl;
beginfnd_global.apps_initialize(user_id=> 1490,resp_id=> 50764,resp_appl_id=> 553 );
l_mmti_rec.source_code:='TEST';
l_mmti_rec.source_header_id:=2;
l_mmti_rec.source_line_id:=2;
l_mmti_rec.process_flag:=3;
l_mmti_rec.lock_flag:=2;
l_mmti_rec.transaction_mode:=3;
l_mmti_rec.last_update_date:=SYSDATE;
l_mmti_rec.last_updated_by:=fnd_global.user_id;
l_mmti_rec.creation_date:=SYSDATE;
l_mmti_rec.created_by:=fnd_global.user_id;
l_mmti_rec.organization_id:=423;
l_mmti_rec.inventory_item_id:=43006;
l_mmti_rec.inventory_item:='G011009300105M';
l_mmti_rec.transaction_quantity:=1;
l_mmti_rec.transaction_uom:='ECH';
l_mmti_rec.primary_quantity:=1;
l_mmti_rec.secondary_transaction_quantity:=22;
l_mmti_rec.secondary_uom_code:='GRM';
l_mmti_rec.transaction_date:=sysdate;
l_mmti_rec.SUBINVENTORY_CODE:='Corp_FG';
l_mmti_rec.transaction_type_id:=44;
--44;
l_mmti_rec.transaction_action_id:=31;
--31;
l_mmti_rec.transaction_source_type_id:=5;
l_mmti_rec.transaction_interface_id:=10001;
l_mmli_tbl(1).LOT_NUMBER:='MAH1456';
l_mmli_tbl(1).transaction_quantity:=1;
l_mmli_tbl(1).secondary_transaction_quantity:=22;


l_mmli_tbl(1).last_update_date:=SYSDATE;
l_mmli_tbl(1).last_updated_by:=fnd_global.user_id;
l_mmli_tbl(1).creation_date:=SYSDATE;
l_mmli_tbl(1).created_by:=fnd_global.user_id;
gme_api_pub.create_material_txn(p_api_version=> 2.0 ,
p_validation_level=> 1000 ,
p_init_msg_list=> fnd_api.g_true ,
p_commit=> fnd_api.g_true ,
x_message_count=>x_message_count,
x_message_list=>x_message_list,
x_return_status=>x_return_status,
p_org_code=>'VCP' ,
p_mmti_rec=>l_mmti_rec ,
p_mmli_tbl=>l_mmli_tbl,
p_batch_no=>3000100 ,
p_line_no=>1 ,
p_line_type=>1 ,
p_create_lot=>'T' ,
p_generate_lot=>'F' ,
p_generate_parent_lot=>'F' ,
x_mmt_rec=>x_mmt_rec,
x_mmln_tbl=>x_mmln_tbl);
DBMS_OUTPUT.put_line ('status: '|| x_return_status ||' msg Count '||x_message_count);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'status: '|| x_return_status ||'msg Count '|| x_message_count);
IFx_return_status= 'S' THEN
COMMIT;
DBMS_OUTPUT.put_line ('success!!');
ELSE IFx_message_count= 1 THEN
DBMS_OUTPUT.put_line('Error:'|| x_message_list); FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error:'|| x_message_list);
ELSE
DBMS_OUTPUT.put_line ( 'status: '|| x_return_status||' Error Count '|| x_message_count );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'status: '|| x_return_status||' Error Count '|| x_message_count );
DBMS_OUTPUT.put_line ('Error: '||SUBSTR (x_message_list, 1, 255));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error: '||SUBSTR (x_message_list, 1,255));
END IF;
END IF;
exception when others then null;
end ;


============================================================================================

OPM Batch - Release and Unrelease


OPM Batch Release :

The  Release_Batch API checks  for the validity of the batch validity rule, and ingredient reservations to create transactions. It also checks for unexploded phantoms, except for those that are Automatic by Step with a step association. The Batch status is set to work in process.

API: GME_API_PUB.RELEASE_BATCH

Sample Code:

create or replace PROCEDURE release_batch (p_batch_id IN NUMBER, p_run_seq_id IN NUMBER)
   IS
      lr_batch_header             gme_batch_header%ROWTYPE;
      lt_exception_material_tbl   gme_common_pvt.exceptions_tab;
      x_return_status             VARCHAR2 (10);
      x_batch_header              gme_batch_header%ROWTYPE;
      ld_st_date                  DATE;
      ld_com_date                 DATE;
      ln_msg_index_out            NUMBER;
      ln_batch_no                 NUMBER;
      ln_rule_id                  NUMBER;
      ln_org_id                   NUMBER;
      lc_org_code                 VARCHAR2 (5);
      lc_status                   VARCHAR2 (5);
      lc_message_list             VARCHAR2 (2000);
      ln_step_no                  NUMBER;
      x_message_count             NUMBER;
      x_message_list              VARCHAR2 (2000);
   BEGIN
      fnd_global.apps_initialize (user_id           => gn_user_id,
                                  resp_id           => gn_resp_id,
                                  resp_appl_id      => gn_prog_appl_id
                                 );

      BEGIN
         SELECT plan_start_date, plan_cmplt_date, batch_no,
                recipe_validity_rule_id, organization_id
           INTO ld_st_date, ld_com_date, ln_batch_no,
                ln_rule_id, ln_org_id
           FROM gme_batch_header
          WHERE batch_id = p_batch_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Error In Selecting Batch Mat Details'
                                  || SQLERRM
                                 );
      END;

      IF ln_org_id IS NOT NULL
      THEN
         SELECT organization_code
           INTO lc_org_code
           FROM org_organization_definitions
          WHERE organization_id = ln_org_id;
      END IF;

      lr_batch_header.batch_type := 0;
      lr_batch_header.batch_no := ln_batch_no;
      lr_batch_header.plan_start_date := ld_st_date;
      lr_batch_header.plan_cmplt_date := ld_com_date;
      lr_batch_header.update_inventory_ind := 'Y';
      lr_batch_header.recipe_validity_rule_id := ln_rule_id;
      lr_batch_header.actual_start_date := SYSDATE;
      gme_api_pub.release_batch
                        (p_api_version                 => 2.0,
                         p_validation_level            => 100,
                         p_init_msg_list               => fnd_api.g_false,
                         p_commit                      => fnd_api.g_false,
                         x_message_count               => x_message_count,
                         x_message_list                => x_message_list,
                         x_return_status               => x_return_status,
                         p_batch_header_rec            => lr_batch_header,
                         p_org_code                    => lc_org_code,
                         p_ignore_exception            => 'T',
                         p_validate_flexfields         => fnd_api.g_false,
                         x_batch_header_rec            => x_batch_header,
                         x_exception_material_tbl      => lt_exception_material_tbl
                        );
      COMMIT;

      IF x_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('Batch Released');
       
      ELSE
         DBMS_OUTPUT.put_line ('Batch Release failed');

         IF x_message_count = 1
         THEN
            DBMS_OUTPUT.put_line ('Error:' || x_message_list);
         ELSE
            FOR i IN 1 .. x_message_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => i,
                                p_data               => x_message_list,
                                p_msg_index_out      => ln_msg_index_out
                               );
               DBMS_OUTPUT.put_line ('Error: ' || x_message_list);
            END LOOP;
         END IF;

               END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error ' || TO_CHAR (SQLCODE) || ': '
                               || SQLERRM
                              );
   END;

OPM Batch Unrelease :
==============================================

Once a batch is released, the status will be reversed from Released to Pending.
The completed transactions are reversed, actual values are reset to zero, and inventory is updated.

API: GME_API_PUB.UNRELEASE_BATCH

Sample Code:

create or replace PROCEDURE unrelease_batch (p_batch_id IN NUMBER, p_run_seq_id IN NUMBER)
   IS
      lr_batch_header             gme_batch_header%ROWTYPE;
      lt_exception_material_tbl   gme_common_pvt.exceptions_tab;
      x_return_status             VARCHAR2 (10);
      x_batch_header              gme_batch_header%ROWTYPE;
      ln_msg_index_out            NUMBER;
      ln_batch_no                 NUMBER;
      lc_org_code                 VARCHAR2 (5);
      lc_status                   VARCHAR2 (5);
      lc_message_list             VARCHAR2 (2000);
      x_message_count             NUMBER;
      x_message_list              VARCHAR2 (2000);
      i                           NUMBER;
   BEGIN
      fnd_global.apps_initialize (user_id           => gn_user_id,
                                  resp_id           => gn_resp_id,
                                  resp_appl_id      => gn_prog_appl_id
                                 );
      lr_batch_header.batch_id := p_batch_id;

      BEGIN
         SELECT organization_code
           INTO lc_org_code
           FROM org_organization_definitions
          WHERE organization_id = (SELECT organization_id
                                     FROM gme_batch_header
                                    WHERE batch_id = p_batch_id);
      EXCEPTION
         WHEN OTHERS
         THEN
            lc_org_code := NULL;
      END;

      gme_api_pub.unrelease_batch (p_api_version                => 2.0,
                                   p_validation_level           => 100,
                                   p_init_msg_list              => fnd_api.g_false,
                                   p_commit                     => fnd_api.g_false,
                                   p_save_batch                 => fnd_api.g_false,
                                   x_message_count              => x_message_count,
                                   x_message_list               => lc_message_list,
                                   x_return_status              => x_return_status,
                                   p_batch_header_rec           => lr_batch_header,
                                   p_org_code                   => lc_org_code,
                                   p_create_resv_pend_lots      => 1, -- Recreate reservations- NO
                                   p_continue_lpn_txn           => 'N',
                                   x_batch_header_rec           => x_batch_header
                                  );
      COMMIT;

      IF x_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('Batch Unreleased' || x_message_list);
      ELSE
         IF x_message_count = 1
         THEN
            DBMS_OUTPUT.put_line ('Error' || lc_message_list);
         ELSE
            FOR i IN 1 .. x_message_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => i,
                                p_data               => x_message_list,
                                p_msg_index_out      => ln_msg_index_out
                               );
            END LOOP;
         
            COMMIT;
         END IF;
      END IF;
   END;

======================================================================================