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;

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

MTL ITEM RESERVATIONS QUERY

==========     MTL ITEM RESERVATIONS QUERY  ===========

select * from MTL_RESERVATIONS

select * from mtl_reservations where organization_id= 2035

select * from mtl_system_items_b bb where bb.INVENTORY_ITEM_ID = 133109 and bb.ORGANIZATION_ID = 10--


select *  from mtl_reservations where organization_id = 2035 and INVENTORY_ITEM_ID = 133109 and RESERVATION_ID=1345--

select * from MTL_DEMAND

SELECT o.organization_code, o.organization_name, i.segment1, i.description,
       r.reservation_quantity, orig_demand_source_header_id,
       supply_source_type_id
  FROM mtl_reservations r,
       org_organization_definitions o,
       mtl_system_items_b i
 WHERE r.inventory_item_id = 396964  --132940
   AND r.organization_id = o.organization_id
   AND r.organization_id = i.organization_id
   AND r.inventory_item_id = i.inventory_item_id

----------DEL---RES--TB--

SELECT *
  FROM gme_batch_header b
 WHERE b.batch_id = 1349161 --2052297



SELECT *
  FROM mtl_material_transactions_temp t
 WHERE t.inventory_item_id = 396964 --132940

 select * from MTL_RESERVATIONS_INTERFACE ri
where ri.INVENTORY_ITEM_ID = 396964


select * from mtl_transactions_interface rt
where rt.INVENTORY_ITEM_ID = 396964


select *  from mtl_reservations where organization_id = 2035 and INVENTORY_ITEM_ID = 133109 and RESERVATION_ID=134598531

Cost Finding SQL for Oracle Apps R12

=========  Cost Finding SQL for Oracle Apps R12  ===================


select ccd.PERIOD_ID,ccd.CMPNT_COST
from  cm_cmpt_dtl ccd,mtl_system_items_b msi
where msi.INVENTORY_ITEM_ID = ccd.INVENTORY_ITEM_ID
and ccd.ORGANIZATION_ID = msi.ORGANIZATION_ID
and msi.ORGANIZATION_ID = :A01
and ccd.PERIOD_ID = :PERIOD_ID2

select MAX(PERIOD_ID)TOP1 from GMF_PERIOD_STATUSES
select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES order by PERIOD_ID

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )
               
 select top 1 col
from (
    select top 2 col
    from [table]
    order by col) topTwo
order by col desc


select MAX(PERIOD_ID) TOP1 from (select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES order by PERIOD_ID ) TOP2 order by PERIOD_ID desc

select *
from(
select MAX(PERIOD_ID) TOP1 From GMF_PERIOD_STATUSES ,
select MAX(PERIOD_ID) From GMF_PERIOD_STATUSES
where PERIOD_ID <(select MAX(PERIOD_ID) TOP2from GMF_PERIOD_STATUSES) ,
(select MAX(PERIOD_ID)  TOP3 From GMF_PERIOD_STATUSES
where PERIOD_ID <(select MAX(PERIOD_ID) from GMF_PERIOD_STATUSES where PERIOD_ID <(select MAX(PERIOD_ID) from GMF_PERIOD_STATUSES)))))

(select * from

(

select MAX(PERIOD_ID)  TOP3 From GMF_PERIOD_STATUSES

where PERIOD_ID <(select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES where PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES))

select TOP2,ccd.CMPNT_COST from

(select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES 

where PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES))BB,cm_cmpt_dtl ccd

where ccd.PERIOD_ID = TOP2 

select ccd.CMPNT_COST from

(

select MAX(GPS.PERIOD_ID) TOP2, ccd.CMPNT_COST from GMF_PERIOD_STATUSES GPS,cm_cmpt_dtl ccd 

where GPS.PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES)

and ccd.PERIOD_ID = GPS.PERIOD_ID

group by GPS.PERIOD_ID,ccd.CMPNT_COST

where ccd.PERIOD_ID = TOP2 

)) TOP

Cost Comp for OPM Organization

===== Cost Comp for OPM Organization ==========

Select OU_NAME,OU_UNIT,ORG_CODE,ORG_NAME,Item_COED,description,UOM,ITEM_TYPE,PERIOD_CODE, pvs_mth2,pvs_mth1,cur_mth,
(cur_mth - pvs_mth1) Diff,
TO_CHAR(ROUND ((  (  (cur_mth - pvs_mth1)
                    / (CASE
                          WHEN (cur_mth) = 0
                             THEN 1
                          ELSE (cur_mth)
                       END
                      )
                   )
                 * 100
                ),
                6
               )) PP_VR
from
(SELECT ood.OPERATING_UNIT OU_UNIT, hr.NAME OU_NAME,msib.segment1 Item_COED, msib.description,msib.primary_uom_code UOM,
msib.ITEM_TYPE, mp.organization_code ORG_CODE,
       ood.organization_name ORG_NAME,ps.PERIOD_ID,ps.PERIOD_CODE,
     
       trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                    (SELECT ps.PERIOD_ID FROM GMF_PERIOD_STATUSES PS WHERE TRUNC(END_DATE) = (
                    LAST_DAY(ADD_MONTHS((SELECT TRUNC(START_DATE) FROM GMF_PERIOD_STATUSES PS WHERE PERIOD_ID=:P_PERIOD_ID),-1))))
                 ),4) pvs_mth1,
        trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                  ps.PERIOD_ID
                 ),4)cur_mth
  FROM mtl_system_items_b msib,
       mtl_parameters mp,
       org_organization_definitions ood,
      HR_OPERATING_UNITS hr,
       GMF_PERIOD_STATUSES PS
 WHERE ood.organization_id = mp.organization_id
   AND msib.organization_id = mp.organization_id
   --AND ood.BUSINESS_GROUP_ID = BU.BUSINESS_GROUP_ID
   AND ood.operating_unit = hr.ORGANIZATION_ID
   AND ood.operating_unit = :P_OU_ID
   -----AND ps.PERIOD_ID = :P_PERIOD_ID-----
   ---and ps.PERIOD_CODE =:P_PERIOD_NAME
  ---- and msib.ITEM_TYPE in----
   ---AND mp.process_enabled_flag -------
   --AND mp.organization_id <> 102
   AND EXISTS (
          SELECT 1
            FROM mtl_material_transactions
           WHERE organization_id = mp.organization_id
             AND inventory_item_id = msib.inventory_item_id
          AND TRUNC(TRANSACTION_DATE) BETWEEN TO_DATE(TO_CHAR(PS.START_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS') AND 
                TO_DATE(TO_CHAR(PS.END_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS'))
  order by  ood.organization_name)
  group by OU_UNIT,OU_NAME,Item_COED,description,UOM,ITEM_TYPE,ORG_CODE,ORG_NAME,PERIOD_CODE,pvs_mth2,pvs_mth1,cur_mth
  order by ORG_NAME ASC

Oracle Apss R12 OPM (Oracle Process manufacturing) Formula Base Tables and Detail of Products and Ingredients

select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,
decode(b.line_type,-1,'Ingredient','Product') "Type"
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>'COSTING'
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID

---------------------


select  a.FORMULA_ID,a.FORMULA_NO,decode(a.FORMULA_VERS,1,'Version 1',2,'Version 2', 3, 'Version 3', 'Others') "Formula_Versions",
a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id, --distinct(a.FORMULA_NO),
decode(b.line_type,-1,'Ingredient','Product') "Type"
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c, org_organization_definitions ood --- ORG_ORGANIZATION_DEFINITIONS
where a.FORMULA_ID = b.FORMULA_ID
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and c.ORGANIZATION_ID = ood.ORGANIZATION_ID
---and b.ORGANIZATION_ID=c.organization_id
and ood.ORGANIZATION_CODE = '&A01'

--and b.ORGANIZATION_ID = c.ORGANIZATION_ID
--and b.ORGANIZATION_ID=:your_Org_id
--and a.FORMULA_CLASS<>'COSTING'

--order by a.FORMULA_NO, a.FORMULA_ID, a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id