How to Automate Supplier Site Creation in Oracle R12 Using APIs

In Oracle Applications R12, suppliers are a critical part of the Procure-to-Pay (P2P) process. While supplier headers define the main vendor information, supplier sites hold location-specific details such as addresses, operating units, payment terms, and purchasing information. Each supplier can have multiple sites, for example, a billing site, a payment site, or a purchasing site.

Creating supplier sites manually through the front-end forms is possible but time-consuming, especially when an organization has to handle a large number of vendors. To streamline this process, Oracle provides a powerful API that allows developers to create supplier sites programmatically. This article will walk you through the details of the Supplier Site Creation API in Oracle Apps R12 with a working example.

Why Use the Supplier Site Creation API?

Using the supplier site creation API provides several advantages:

Bulk Upload Capability: Automates the process of adding hundreds of supplier sites.
Data Accuracy: Enforces Oracle’s built-in validations.
Integration: Enables direct integration from external procurement or ERP systems.
Time Efficiency: Reduces manual effort and accelerates supplier onboarding.
Consistency: Ensures data is structured uniformly across operating units.

API for Supplier Site Creation

Oracle provides the AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE procedure to create supplier sites. This API is part of the AP_VENDOR_PUB_PKG package, which manages all supplier-related operations.

Key Parameters

Some important parameters in this procedure include:

p_vendor_site_rec – A record type (r_vendor_site_rec_type) holding supplier site details such as address, site code, city, country, and operating unit.
x_vendor_site_id – Output parameter that returns the newly created site ID.
x_return_status – Indicates success (S) or failure (E).
x_msg_count / x_msg_data – Capture API messages or error details.

Step-by-Step Guide for Creating a Supplier Site

To create a supplier site using the API, follow these steps:

Ensure a supplier header exists – You must already have a valid supplier (created using CREATE_VENDOR).
Initialize the record structure – Use r_vendor_site_rec_type to store site attributes.
Populate required fields – Such as supplier ID, site code, address, and operating unit.
Call the API procedure – Execute CREATE_VENDOR_SITE.
Check return status – Validate if the site was successfully created.
Commit or rollback – Save changes if successful or roll back in case of errors.

Example: Supplier Site Creation in Oracle Apps R12

The following PL/SQL block demonstrates how to create a supplier site programmatically:

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; -- Existing Vendor ID
   l_vendor_site_rec.vendor_site_code := 'ABC_MUMBAI'; -- Unique site code
   l_vendor_site_rec.address_line1    := '123 Business Street';
   l_vendor_site_rec.city             := 'Mumbai';
   l_vendor_site_rec.state            := 'MH';
   l_vendor_site_rec.zip              := '400001';
   l_vendor_site_rec.country          := 'IN';
   l_vendor_site_rec.org_id           := 204; -- Operating Unit ID
   l_vendor_site_rec.inactive_date    := NULL; -- Active site
   l_vendor_site_rec.purchasing_site_flag := 'Y'; -- Mark as Purchasing site
   l_vendor_site_rec.pay_site_flag    := 'Y'; -- Mark as Pay site

   -- 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
   );

   -- Check results
   IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
      DBMS_OUTPUT.put_line('Supplier Site Created Successfully');
      DBMS_OUTPUT.put_line('Site ID: ' || l_vendor_site_id);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line('Error in Supplier Site Creation');
      DBMS_OUTPUT.put_line('Error Message: ' || l_msg_data);
      ROLLBACK;
   END IF;
END;
/

Important Considerations

When creating supplier sites with the API, keep the following in mind:

Unique Site Code: Ensure vendor_site_code is unique for each supplier.
Valid Operating Unit: The org_id must be a valid Operating Unit ID.
Mandatory Fields: Fields like supplier ID, address, city, and country must be populated.
Flags: Set site flags (purchasing_site_flag, pay_site_flag, etc.) correctly depending on business requirements.
Error Handling: Always capture x_msg_data for troubleshooting failed executions.
Commit Control: Manage commits carefully, especially during bulk processing.

Benefits of Automating Supplier Site Creation

Automating supplier site creation with the API ensures:

Speed: Faster onboarding of suppliers across multiple OUs.
Accuracy: Reduced human error during site setup.
Integration: Smooth flow of supplier data from legacy systems or third-party tools.
Flexibility: Ability to create multiple sites (billing, shipping, payment) in one program.
Compliance: Adherence to Oracle’s standard validations and data integrity rules.

Conclusion

Supplier sites are a crucial part of supplier setup in Oracle Apps R12. They define how an organization interacts with suppliers across different business functions like purchasing and payments. Instead of relying on manual entry, using the AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE API provides a faster, more reliable, and scalable approach.

By following the steps and example shared in this article, developers and consultants can easily implement supplier site automation, ensuring efficiency and consistency in supplier management.

The post Supplier Site Creation API in Oracle Apps R12 with Example appeared first on Vinish.Dev.