2 Replies Latest reply on Jan 1, 2018 5:46 AM by 2904008

    Got one requirement to update VAT_REGISTRATION_NUM column in ap_suppliers and ap_supplier_sites_all table, I have written an API for the same, API returns success but data not inserting in the base table.

    2904008

      The api is below :

       

       

      DECLARE

      l_api_version           NUMBER;

      l_init_msg_list         VARCHAR2 (200);

      l_commit                VARCHAR2 (200);

      l_validation_level      NUMBER;

      l_return_status         VARCHAR2 (200);

      l_msg_count             NUMBER;

      l_msg_data              VARCHAR2 (200);

       

       

      l_vendor_api_rec        ap_vendor_pub_pkg.r_vendor_site_rec_type;

      l_vendor_site_rec       ap_supplier_sites_all%ROWTYPE;

      l_msg                   varchar2 (200);

      l_vendor_site_id        number;

      l_calling_prog          varchar2(100):='XXAP_CUSTOM_UPDATE';

      l_gstn                  varchar2(50);

       

      cursor cur_data

          is

          select * from xxap_gst_update;

       

      BEGIN

       

         -- Setting the Context --

          mo_global.init('SQLAP');

          fnd_global.apps_initialize ( user_id      => 1157         

                                      ,resp_id      => 50653

                                      ,resp_appl_id => 222);

          mo_global.set_policy_context('S',82);

       

              l_api_version           := 1.0;

              l_init_msg_list         := fnd_api.g_true;

              l_commit                := fnd_api.g_true;

              l_validation_level      := fnd_api.g_valid_level_full;

          

           for i in cur_data   

           LOOP

             

              begin

                      select gstn

                        into l_gstn

                        from xxe_gst_update aa

                       where aa.segment1 = i.segment1

                       and   rownum=1;  

              end;

             

              begin

                  select *

                    into l_vendor_site_rec

                  from   ap_supplier_sites_all aps

                   where aps.vendor_site_id = i.vendor_site_id;

              end;

             

                     l_vendor_api_rec.vendor_id            := l_vendor_site_rec.vendor_id;

                     l_vendor_api_rec.vat_registration_num := l_gstn;

                                l_vendor_site_id           := l_vendor_site_rec.vendor_site_id;              

             

                  ap_vendor_pub_pkg.Update_Vendor_Site

                                               (     p_api_version        => l_api_version,

                                                     p_init_msg_list      => l_init_msg_list,

                                                     p_commit             => l_commit,

                                                     p_validation_level   => l_validation_level,

                                                     x_return_status      => l_return_status,

                                                     x_msg_count          => l_msg_count,

                                                     x_msg_data            => l_msg_data,

                                                     p_vendor_site_rec    => l_vendor_api_rec,

                                                     p_vendor_site_id        => l_vendor_site_id,

                                                     p_calling_prog        => l_calling_prog

                                                 );

       

                IF l_return_status = fnd_api.g_ret_sts_success THEN

                  COMMIT;

                  update XXE_GST_UPDATE

                     set status = 'GSTN UPDATED'

                   where segment1 = i.segment1;

                   commit;   

              ELSE

                  ROLLBACK;

                  update XXE_GST_UPDATE

                     set status = 'GSTN ERROR',

                         ERROR_MSG = l_msg_data

                   where segment1 = i.segment1;

                   commit;                     

              END IF; 

             

            END LOOP;              

      END;

       

       

      The API returning Success but data not inserting in the table.

       

      Can someone please help ? Is their any mistake I made in the API or Is it possible to update vat_registration_num ??