Technical‎ > ‎Misc‎ > ‎

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;


Free Hit Counters
Comments