Updating Supplier Name on Supplier

I recently was faced with an issue where I had to update the Supplier name & Alternate Supplier Name for a Supplier in Oracle Payables.

After wasting a lot of time and running thru thousands of lines of Oracle API code I figured out that the so called Supplier Public API AP_VENDOR_PUB_PKG.UPDATE_VENDOR DOES NOT UPDATE SUPPLIER NAME.

But I found that this API does update other attributes for a Supplier.

DECLARE
l_vendor_id NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
BEGIN

FND_GLOBAL.apps_initialize(1198,20639,200);
l_vendor_id:= 99999;
l_vendor_rec.vendor_name:='COOL DUDE SUPPLIER';
l_vendor_rec.segment1:='1252';

AP_VENDOR_PUB_PKG.UPDATE_VENDOR(p_api_version =>'1.0',
p_init_msg_list => FND_API.G_TRUE ,
p_commit => FND_API.G_TRUE,
p_validation_level =>FND_API.G_VALID_LEVEL_FULL,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_vendor_rec => l_vendor_rec,
p_vendor_id => l_vendor_id
);

FOR I IN 1..l_msg_count
LOOP
l_msg_data := SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;
end;

Now after struggling very hard I found that one does have an API to update Supplier Name: HZ_PARTY_V2PUB.update_organization. All you need is the PartyID of the Supplier.

Another interesting thing is to update the Supplier Alternate Name I had to pass that value to the
organization_name_phonetic field in the .

DECLARE

L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;

x_profile_id NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_object_version_number NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);

begin

SELECT aps.PARTY_ID, hzp.object_version_number
into l_party_id, l_object_version_number
FROM AP_SUPPLIERS aps, HZ_PARTIES hzp
WHERE
vendor_id =
l_vendor_id,
AND aps.party_id = hzp.party_id;

l_organization_rec.party_rec.party_id := l_party_id;

l_organization_rec.organization_name := l_suplier_name_tbl(i);
l_organization_rec.organization_name_phonetic := l_alt_name(i);

HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

FOR I IN 1..l_msg_count
LOOP
l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;

exception
when others then
raise;
end;

5 comments:

  1. Thanks for the Info. it worked..

    ReplyDelete
  2. Sorry it's not working!!! Getting error "This record in table HZ_PARTIES cannot be locked as it has been updated by another user".Please help.

    ReplyDelete
  3. Thank you very much good stuff it saved my time a lot...!

    ReplyDelete
  4. Hi Raja you have to pass current object version number using by old record to API via l_object_version_number you can see above.

    ReplyDelete

There was an error in this gadget