Managing suppliers in Oracle Applications R12 is a critical task for every organization, especially within the Procure-to-Pay (P2P) process. While suppliers can be created through the application interface, automation using APIs is often preferred. APIs help in handling large data migrations, integrating external procurement systems, and ensuring faster and more reliable onboarding of suppliers.
In this guide, we will explore how to create a supplier using APIs in Oracle Apps R12 and also cover related operations like creating supplier sites and updating supplier information. You will learn about the main API package, its key procedures, required parameters, and practical examples with PL/SQL code.
Why Use Oracle Apps R12 APIs for Supplier Management?
Manually creating suppliers and their sites can be tedious when dealing with hundreds or thousands of records. APIs offer a better approach:
- Efficiency: Bulk loading of supplier data from legacy or third-party systems.
- Accuracy: Reduces data entry errors by enforcing Oracle’s built-in validations.
- Integration: Enables seamless supplier creation from external procurement platforms.
- Consistency: Uses the same business logic as Oracle’s front-end forms.
- Control: Provides flexibility with commit/rollback and error handling.
API Package for Supplier Creation and Maintenance
Oracle provides the AP_VENDOR_PUB_PKG package to perform supplier operations. This package supports both creation and updates of supplier headers and supplier sites. The most commonly used procedures include:
AP_VENDOR_PUB_PKG.CREATE_VENDOR– Creates a supplier header (vendor).AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE– Creates supplier sites for specific Operating Units.AP_VENDOR_PUB_PKG.UPDATE_VENDOR– Updates supplier information (like name, tax details, payment method).AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE– Updates supplier site details (like address, bank account, terms).
These procedures can be combined to fully automate the supplier lifecycle in Oracle Apps R12.
Key Parameters
Each of the above procedures has some common parameters:
- p_api_version – API version number.
- p_init_msg_list – Initializes the message list (use
FND_API.G_TRUE). - p_commit – Controls transaction commit.
- x_return_status – Output parameter showing success (
S) or failure (E). - x_msg_count / x_msg_data – Return message details in case of errors.
Additionally, each procedure requires specific record structures:
r_vendor_rec_type– For supplier header details.r_vendor_site_rec_type– For supplier site details.
Step 1: Create Supplier Header
The first step is to create a supplier header using CREATE_VENDOR.
Sample code:
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
-- Populate supplier details
l_vendor_rec.vendor_name := 'ABC SUPPLIERS LTD';
l_vendor_rec.vendor_type_lookup_code := 'STANDARD';
l_vendor_rec.segment1 := 'ABC001';
l_vendor_rec.start_date_active := SYSDATE;
l_vendor_rec.taxpayer_id := 'TAX12345';
-- Call API
AP_VENDOR_PUB_PKG.create_vendor (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_vendor_rec => l_vendor_rec,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
DBMS_OUTPUT.put_line('Supplier Created: Vendor ID = ' || l_vendor_id);
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Error: ' || l_msg_data);
ROLLBACK;
END IF;
END;
/
Step 2: Create Supplier Sites
After creating the supplier header, you must create supplier sites. A supplier can have different sites per Operating Unit (OU), such as billing, purchasing, or payment sites. This is done using CREATE_VENDOR_SITE.
Sample code:
DECLARE
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_vendor_site_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
-- Populate supplier site details
l_vendor_site_rec.vendor_id := 1234; -- Vendor ID created earlier
l_vendor_site_rec.vendor_site_code := 'ABC_HEAD_OFFICE';
l_vendor_site_rec.address_line1 := '123 Business Street';
l_vendor_site_rec.city := 'Mumbai';
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := 204; -- Operating Unit ID
-- Call API
AP_VENDOR_PUB_PKG.create_vendor_site (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => l_vendor_site_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
DBMS_OUTPUT.put_line('Supplier Site Created: Site ID = ' || l_vendor_site_id);
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Error: ' || l_msg_data);
ROLLBACK;
END IF;
END;
/
Step 3: Update Supplier Header
Sometimes you may need to update existing supplier details, such as name, tax registration, or payment method. This can be achieved using UPDATE_VENDOR.
Sample code:
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
-- Update supplier information
l_vendor_rec.vendor_id := 1234; -- Existing Vendor ID
l_vendor_rec.vendor_name := 'ABC SUPPLIERS INTERNATIONAL';
-- Call API
AP_VENDOR_PUB_PKG.update_vendor (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
DBMS_OUTPUT.put_line('Supplier Updated Successfully');
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Error: ' || l_msg_data);
ROLLBACK;
END IF;
END;
/
Step 4: Update Supplier Site
If a supplier site address, payment terms, or banking information needs to be updated, use UPDATE_VENDOR_SITE.
Sample code:
DECLARE
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
-- Update supplier site details
l_vendor_site_rec.vendor_site_id := 5678; -- Existing Site ID
l_vendor_site_rec.city := 'Delhi';
l_vendor_site_rec.address_line1 := '456 Corporate Avenue';
-- Call API
AP_VENDOR_PUB_PKG.update_vendor_site (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
DBMS_OUTPUT.put_line('Supplier Site Updated Successfully');
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Error: ' || l_msg_data);
ROLLBACK;
END IF;
END;
/
Best Practices
When working with supplier APIs in Oracle Apps R12, follow these best practices:
- Always validate mandatory fields before API execution.
- Use test environments before running in production.
- Handle commit/rollback carefully for data integrity.
- Capture error messages for troubleshooting.
- Ensure Operating Unit IDs and supplier types are valid.
- Consider building a wrapper procedure for bulk processing.
Benefits of Automating Supplier Creation and Updates
- Scalability: Supports thousands of supplier records.
- Data Quality: Enforces consistent validations.
- Integration: Works with external procurement, ERP, and finance systems.
- Productivity: Reduces manual work for procurement teams.
- Control: Provides better tracking through programmatic logs.
Conclusion
Using the AP_VENDOR_PUB_PKG package in Oracle Apps R12 is the most efficient way to manage suppliers programmatically. By combining CREATE_VENDOR, CREATE_VENDOR_SITE, UPDATE_VENDOR, and UPDATE_VENDOR_SITE, organizations can completely automate the supplier onboarding and maintenance process.
This approach saves time, ensures accuracy, and allows seamless integration with external systems. Whether you are migrating data or building automation, knowing how to create supplier using API in Oracle Apps R12 and manage sites and updates is an essential skill for developers and consultants.
The post How to Create Supplier Using API in Oracle Apps R12 appeared first on Vinish.Dev.