OPM – GMD – Delete Row from the formula in R12

1. 

   Krishna says:

   October 9, 2018 at 9:43 am

   How to use GMD_FORMULA_DETAIL_PUB.Update_FormulaDetail?

   Reply
   • 

     Velmurugan says:

     October 9, 2018 at 10:08 am

     Hi Krishna,

     Hope this might help you.

     procedure update_formuladetail (p_formula_id NUMBER,
     p_formulaline_id NUMBER,
     p_new_qty NUMBER
     p_return_status OUT VARCHAR2,
     p_message OUT VARCHAR2) is
     l_api_version CONSTANT NUMBER : = 2.0;
     l_init_msg_list VARCHAR2 (1): = FND_API.G_TRUE;
     l_commit VARCHAR2 (1): = FND_API.G_FALSE;
     l_called_from_forms VARCHAR2 (10): = ‘NO’;
     l_return_status VARCHAR2 (1);
     l_msg_count NUMBER ;
     l_msg_data VARCHAR2 (400);
     l_formula_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
     –
     l_formula_head fm_form_mst_b% rowtype;
     l_formula_line fm_matl_dtl% rowtype;
     –
     i pls_integer: = 1;
     no_proc_finish exception ;
     begin

     if (p_formula_id is null) then
     p_message: = ‘ The p_formula_id parameter cannot be empty ‘;
     raise no_proc_finish;
     end if ;
     begin
     select * into l_formula_head
     from fm_form_mst_b fm where fm.formula_id = p_formula_id;
     exception
     when others then
     p_message: = ‘ Could not find formula ‘;
     raise no_proc_finish;
     end ;
     begin
     select * into l_formula_line
     from fm_matl_dtl fm where fm.FORMULALINE_ID = p_formulaline_id;
     exception
     when others then
     p_message: = ‘ Could not find formula line ‘;
     raise no_proc_finish;
     end ;

     gme_common_pvt.set_who;

     l_formula_detail_tbl (i) .formula_id: = p_formula_id;
     l_formula_detail_tbl (i) .formula_no: = l_formula_head.formula_no;
     l_formula_detail_tbl (i) .formula_vers: = l_formula_head.formula_vers;
     l_formula_detail_tbl (i) .formulaline_id: = p_formulaline_id;

     l_formula_detail_tbl (i) .QTY: = p_new_qty;

     gmd_formula_detail_pub.update_formuladetail (
     p_api_version => l_api_version,
     p_init_msg_list => l_init_msg_list,
     p_commit => l_commit,
     p_called_from_forms => l_called_from_forms,
     x_return_status => l_return_status,
     x_msg_count => l_msg_count,
     x_msg_data => l_msg_data,
     p_formula_detail_tbl => l_formula_detail_tbl
     );
     if l_return_status <> FND_API.g_ret_sts_success THEN
     if l_msg_count = 1 THEN
     p_message: = FND_MSG_PUB.get (1, ‘ F ‘);
     RAISE no_proc_finish;
     else
     for l in 1..l_msg_count loop
     FND_MSG_PUB.get (p_msg_index => l
     , p_encoded => ‘ F ‘
     , p_data => p_message
     , p_msg_index_out => l_msg_count);
     end loop ;
     RAISE no_proc_finish;
     end if ;
     end if ;

     p_return_status: = ‘ S ‘;
     exception
     when no_proc_finish then
     p_return_status: = ‘ E ‘;
     when others then
     p_message: = SQLERRM ;
     p_return_status: = ‘ E ‘;
     end ;

     Thanks

     Reply
     • 

       Krishna says:

       October 9, 2018 at 10:56 am

       Thanks Velmurugan, but I need to update with below condition –

       update fm_matl_dtl md
       set release_type = 1
       where line_type = -1
       and release_type = 0
       and formula_id in (select formula_id from apps.fm_form_mst fm where fm.formula_status in(700,900));

       Like I want to know how can I add more conditions?

       Many thanks!

       Reply

Leave a Reply