We will be discuss about the api to update vendor payment terms update in ap_suppliers. This is the standard API to update vendor details in ap_suppliers.
==========================================================================
Step1:- Create the below table
==========================================================================
create table apps.payment_terms (SUPPLIER_NUMBER varchar2(20),TERMS varchar2(20));
==========================================================================
Step2:- Insert the data into table
==========================================================================
insert into apps.payment_terms(‘123′,’30 NET’);
insert into apps.payment_terms(‘124′,’10 NET’);
==========================================================================
Step3:- Execute the below script
==========================================================================
SET serveroutput on;
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
lr_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
lr_existing_vendor_rec APPS.ap_terms%ROWTYPE;
lr_existing_vendor_rec1 APPS.ap_SUPPLIERS%ROWTYPE;
l_msg VARCHAR2(200);
p_vendor_id NUMBER;
BEGIN
— Initialize apps session
fnd_global.apps_initialize(1234, 50833, 200);
mo_global.init(‘SQLAP’);
fnd_client_info.set_org_context(101);
— Assign Basic Values
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;
–p_vendor_id := 321902;
— gather vendor details
FOR j IN (
select a.vendor_id,c.TERMS
from apps.ap_suppliers a,
—apps.ap_supplier_sites_all b,
apps.payment_terms c
where 1=1
—and a.vendor_id=b.vendor_id
and a.segment1=c.SUPPLIER_NUMBER
–and a.vendor_id=’2915970′
–and b.vendor_id=c.SUPPLIER_NUMBER
–and b.org_id=147
–AND a.end_date_active IS NULL
—AND b.inactive_date IS NULL
order by a.vendor_id
) LOOP
DBMS_OUTPUT.put_line(‘ENETERED’);
BEGIN
SELECT *
INTO lr_existing_vendor_rec
FROM apps.ap_terms asa
WHERE asa.name =j.terms;
DBMS_OUTPUT.put_line(j.terms);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘Unable to derive the supplier term information for vendor id:’ ||
p_vendor_id);
END;
DBMS_OUTPUT.put_line(‘TERMID’||lr_existing_vendor_rec.term_id);
–Deactivate Vendor
lr_vendor_rec.vendor_id := j.vendor_id;
—lr_vendor_rec.end_date_active := SYSDATE;
lr_vendor_rec.TERMS_ID:=lr_existing_vendor_rec.term_id;—10011;
–lr_vendor_rec1.enabled_flag := ‘Y’;
–lr_vendor_rec.ext_payee_rec.default_pmt_method := ‘TRANSFER’;
ap_vendor_pub_pkg.update_vendor(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => lr_vendor_rec,
p_vendor_id => j.vendor_id);
DBMS_OUTPUT.put_line(‘X_RETURN_STATUS = ‘ || x_return_status);
DBMS_OUTPUT.put_line(‘X_MSG_COUNT = ‘ || x_msg_count);
DBMS_OUTPUT.put_line(‘X_MSG_DATA = ‘ || x_msg_data);
end loop;
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i,
p_encoded => fnd_api.g_false);
DBMS_OUTPUT.put_line(‘The API call failed with error ‘ || l_msg);
END LOOP;
ELSE
DBMS_OUTPUT.put_line(‘The API call ended with SUCESSS status’);
END IF;
END;
