0 Replies Latest reply: Jun 26, 2013 5:19 AM by 957539 RSS

    Not able to insert data into formula table using gmd_formula_pub Api

    957539

      HI.....

      I have to insert data in formula table. for that i am using gmd_formula_pub Api in which i have created staging table same as that of Api table and assign that table to Api but somehow its not working.

      I dont know what to do.. i have tried another api but i am not getting... Kindly help to insert data in formula using API......

       

       

      CREATE OR REPLACE PROCEDURE APPS.formula_api

      is

      /******************************************************************************

      NAME: NAVIN_FML_UPLOAD_PD

      PURPOSE: Formula Uploading

       

      REVISIONS:

      Ver Date Author Description

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

       

       

      ******************************************************************************/

      mjil_fml_tabtype apps.gmd_formula_pub.formula_insert_hdr_tbl_type;

      cursor c1 is

      select * from MJIL_FORMULA_UPLOAD where formula_no not in(select formula_no from fm_form_mst);

      cnt number;

      l_return_status varchar2(1);

      l_msg_count number;

      l_msg_data varchar2(1000);

      l_out_index NUMBER :=0;

      l_user_id number:= 1013416;

      l_responsibility_id number :=22883;

      l_responsibility_app_id number :=552;

      begin

      FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_responsibility_id,l_responsibility_app_id);

      cnt := 0;

      for i in c1

      loop

      cnt := cnt+1;

      mjil_fml_tabtype(cnt).record_type := i.record_type;

      mjil_fml_tabtype(cnt).FORMULA_NO := trim(i.FORMULA_NO);

      mjil_fml_tabtype(cnt).FORMULA_VERS := i.formula_vers;

      mjil_fml_tabtype(cnt).formula_type := i.formula_type;

      mjil_fml_tabtype(cnt).formula_desc1 := i.formula_desc1;

      mjil_fml_tabtype(cnt).formula_desc2 := i.formula_desc2;

      mjil_fml_tabtype(cnt).inactive_ind := i.inactive_ind;

      mjil_fml_tabtype(cnt).OWNER_ORGANIZATION_ID := i.owner_organization_id;

      mjil_fml_tabtype(cnt).total_input_qty := i.total_input_qty;

      mjil_fml_tabtype(cnt).total_output_qty := i.total_output_qty;

      mjil_fml_tabtype(cnt).formula_status := i.formula_status;

      mjil_fml_tabtype(cnt).line_no := i.line_no;

      mjil_fml_tabtype(cnt).line_type := i.line_type;

      mjil_fml_tabtype(cnt).item_no := i.item_no;

      mjil_fml_tabtype(cnt).qty := i.qty;

      mjil_fml_tabtype(cnt).detail_uom := i.detail_uom;

      mjil_fml_tabtype(cnt).release_type := i.release_Type;

      mjil_fml_tabtype(cnt).scrap_factor := i.scrap_factor;

      mjil_fml_tabtype(cnt).scale_type_hdr := i.scale_type_hdr;

      mjil_fml_tabtype(cnt).scale_type_dtl := i.scale_type_dtl;

      mjil_fml_tabtype(cnt).cost_alloc := i.cost_alloc ;

      mjil_fml_tabtype(cnt).phantom_type := i.phantom_type;

      mjil_fml_tabtype(cnt).rework_type := i.rework_type ;

      mjil_fml_tabtype(cnt).buffer_ind := i.buffer_ind ;

      mjil_fml_tabtype(cnt).contribute_yield_ind := i.contribute_yield_ind ;

      mjil_fml_tabtype(cnt).contribute_step_qty_ind := i.contribute_step_qty_ind ;

      mjil_fml_tabtype(cnt).delete_mark := i.delete_mark;

      end loop;

      GMD_FORMULA_PUB.Insert_Formula

      ( p_api_version => 1

      , p_init_msg_list => FND_API.G_FALSE

      , p_commit => FND_API.G_FALSE

      , p_called_from_forms => 'NO'

      , x_return_status => l_return_status

      , x_msg_count =>l_msg_count

      , x_msg_data =>l_msg_data

      , p_formula_header_tbl => mjil_fml_tabtype

      , p_allow_zero_ing_qty => 'FALSE');

      dbms_output.put_line('Return status - '||l_return_status||SQLERRM);

      dbms_output.put_line('Message count - '||l_msg_count||SQLERRM);

      for i IN 1 .. l_msg_count

      LOOP

      FND_MSG_PUB.get(p_msg_index => i,

      p_encoded => 'F',

      p_data => l_msg_data,

      P_MSG_INDEX_OUT => l_out_index);

      DBMS_OUTPUT.PUT_LINE('Message Text '||l_msg_data);

      END LOOP;

      exception

      when others then

      dbms_output.put_line('Return status - '||l_return_status||SQLERRM);

      dbms_output.put_line('Message count - '||l_msg_count||SQLERRM);

       

      for i IN 1 .. l_msg_count

      LOOP

      FND_MSG_PUB.get(p_msg_index => i,

      p_encoded => 'F',

      p_data => l_msg_data,

      P_MSG_INDEX_OUT => l_out_index);

      DBMS_OUTPUT.PUT_LINE('Message Text '||l_msg_data);

      END LOOP;

      end;

      /

       

      this is what i am getting...

       

      Return status - EORA-0000: normal, successful completion

      Message count - 1ORA-0000: normal, successful completion

      Message Text Saving a formula/version 1212 120 requires a Product and an ingredient.

       

      Please Guide...