Thursday, June 4, 2015
As usual, Supplier Migration can be carried out either using Standard Interface tables or API’s. In this article, you will find sample Scripts for API’s required for Creating and updating Vendor information in Oracle Apps R12.
Step | Action | API | Sample Script |
1 | Create Vendor | AP_VENDOR_PUB_PKG.CREATE_VENDOR | |
2 | Create Vendor Site | AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE | |
3 | Create Vendor Contact | AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT | |
4 | Create External Bank | IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK | |
5 | Create External Bank Branch | IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH | |
6 | Create Bank Branch Address | HZ_LOCATION_V2PUB.CREATE_LOCATION | |
7 | Assign Address to Bank Branch | HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE | |
8 | Create Vendor Bank Account | IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT | |
9 | Assign Vendor Bank Account to Vendor | IBY_DISBURSEMENT_SETUP_PUB. SET_PAYEE_INSTR_ASSIGNMENT |
In some cases, we may need to End Date the vendor details in the source system. In case, if the source system is also Oracle Apps R12, then below API’S would be useful.
Step | Action | API | Sample Script |
1 | End Date Vendor | AP_VENDOR_PUB_PKG.UPDATE_VENDOR | |
2 | End Date Vendor Site | AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE |
Hopefully, This helps!!
Thursday, June 4, 2015 by Team search · 2
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_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;
p_vendor_site_id NUMBER;
p_calling_prog VARCHAR2(200);
BEGIN
-- Initialize apps session
fnd_global.apps_initialize(1119, 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_site_id := 2040; -- to be end dated
p_calling_prog := 'XXCUSTOM';
BEGIN
SELECT *
INTO lr_existing_vendor_site_rec
FROM ap_supplier_sites_all assa
WHERE assa.vendor_site_id = p_vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unable to derive the supplier site information for site id:' ||
p_vendor_site_id);
END;
-- Assign Vendor Site Details
lr_vendor_site_rec.vendor_site_id := lr_existing_vendor_site_rec.vendor_site_id;
lr_vendor_site_rec.last_update_date := SYSDATE;
lr_vendor_site_rec.last_updated_by := 1119;
lr_vendor_site_rec.vendor_id := lr_existing_vendor_site_rec.vendor_id;
lr_vendor_site_rec.org_id := lr_existing_vendor_site_rec.org_id;
lr_vendor_site_rec.inactive_date := SYSDATE;
AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(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_site_rec => lr_vendor_site_rec,
p_vendor_site_id => p_vendor_site_id,
p_calling_prog => p_calling_prog);
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;
/
Test Results:
by Team search · 2