API/Script to update Supplier and Supplier Sites Oracle Apps R12

DECLARE

  l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;

  l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;

  x_vendor_site_id NUMBER;

  x_vendor_id NUMBER;

  x_msg_data       VARCHAR2(1000);

  x_msg_count      NUMBER;

  p_count          NUMBER;

  x_return_status VARCHAR2(10);

 

  CURSOR lcu_rec

  IS

    SELECT vendor_id,

      vendor_site_id,

      org_id

    FROM ap_supplier_sites_all

    WHERE match_option <> 'R';

   

  CURSOR lcu_head IS

   SELECT vendor_id

   FROM PO_VENDORS

   WHERE match_option <> 'R';

 

    

BEGIN

/*Supplier Update*/

  FOR j IN lcu_head

  LOOP

    x_vendor_id := j.vendor_id;      -- Supplier Id

    l_vendor_rec.match_option := 'R';              -- eg: Match Option

   

    AP_VENDOR_PUB_PKG.Update_Vendor(p_api_version          => 1,

                x_return_status => x_return_status,

    x_msg_count => x_msg_count,

    x_msg_data => x_msg_data,

    p_vendor_rec                               => l_vendor_rec,

    p_vendor_id                  => x_vendor_id

  );

      

    IF (x_msg_count = 1) THEN

      dbms_output.put_line('x_msg_data ' || x_msg_data);

    ELSIF (x_msg_count > 1) THEN

      LOOP

        p_count         := p_count + 1;

        x_msg_data     := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

        IF (x_msg_data IS NULL) THEN

          EXIT;

        END IF;

        dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

    END IF;

   

  END LOOP;

  /*Supplier Sites Update*/

  FOR i IN lcu_rec

  LOOP

    l_vendor_site_rec.vendor_id    := i.vendor_id;      -- Supplier Id

    l_vendor_site_rec.org_id       := i.org_id;         -- Operating Unit id

    x_vendor_site_id               := i.vendor_site_id; -- Site Id to be updated

    l_vendor_site_rec.match_option := 'R';              -- eg: Match Option

   

    AP_VENDOR_PUB_PKG.Update_Vendor_Site ( p_api_version => 1,

    x_return_status => x_return_status,

    x_msg_count => x_msg_count,

    x_msg_data => x_msg_data,

    p_vendor_site_rec => l_vendor_site_rec,

    p_vendor_site_id => x_vendor_site_id );

   

    IF (x_msg_count = 1) THEN

      dbms_output.put_line('Vendor:'||i.vendor_id||' x_msg_data ' || x_msg_data);

    ELSIF (x_msg_count > 1) THEN

      LOOP

        p_count         := p_count + 1;

        x_msg_data     := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

        IF (x_msg_data IS NULL) THEN

          EXIT;

        END IF;

        dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

    END IF;

   

    COMMIT;

   

  END LOOP;

 

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Error:'||SQLERRM);

END;