The Complete Guide to Supplier Creation & Updates in Oracle Apps R12

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:

1. AP_VENDOR_PUB_PKG.CREATE_VENDOR – Creates a supplier header (vendor).
2. AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE – Creates supplier sites for specific Operating Units.
3. AP_VENDOR_PUB_PKG.UPDATE_VENDOR – Updates supplier information (like name, tax details, payment method).
4. 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.