Oracle EBS: Updating Vendor and Vendor Site (Supplier) Information in Oracle e-Business Suite (11i)

This blog post covers how you update Vendor and Vendor Site records in Apps 11i. The included example shows how you clear the Tax Code associated with both types of record (this is particularly useful in the UK where our Sales Tax has changed).


The Problem
Changes quite often need to be made to the records in the PO_VENDORS and PO_VENDOR_SITES tables that involve changes to a large number of records . For example when the Sales Tax (VAT) rate changed from 17.5 to 15% in the UK each Vendor and Vendor Site had the old rate stored against it (this is the default rate that populates the dialog when you create an invoice for a vendor).


The problem with making these changes directly into the tables themselves is that Oracle a) does not support this, and b) doesn't publish the necessary information for us to work out exactly what doing something as simple as this actually entails.

The Solution
Summary/ Description
Oracle has provided two packages which contain routines that enable developers to make these changes themselves;

AP_VENDORS_PKG

 AP_VENDOR_SITES_PKG

The first thing to note is that there is no "delete" routine; if you want to delete a record you need to update it and set the "INACTIVE_DATE" field to the current date.

The two routines we are interested in are called INSERT_ROW and UPDATE_ROW. There are lots of other routines but they are basically checking/validation or display routines and the Lock routine which we don't want to touch.

Inserting a New Record using INSERT_ROW
Basically you need to pass in ALL the parameters listed (for Vendor Sites there is around forty) these are the direct values you want to end up in the columns in the table. The only field you don't need to worry about is the VENDOR_SITE_ID which is populated from the PO_VENDOR_SITES_S sequence (NEXTVAL).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Other than that the insert relies on Oracles validation (i.e. you won't be able to squeeze 12 characters into 10 character field, put a floating point number into an integer, etc) to stop things if you are inserting invalid data, but there really isn't anything to stop you doing something stupid like creating a new vendor who has only been active in the past or who has an inactive date before their active date.

If you pass in a Shipping Location then a new record will be created in the PO_LOCATION_ASSOCIATIONS table. This appears to be optional.

Updating a Record using UPDATE_ROW
This is the best example of how not to write an API you will ever come across. Basically you call it an pass in the ROWID and then new values for all the existing fields (fantastic potential to delete other peoples work here!).

The two pieces of validation (as of 12th May 2009) that are carried out are a check for duplicate records (based on Vendor ID, and Site Code) and if you have specified the new site as a tax-reporting site (i.e. x_tax_reporting_site_flag = 'Y') a check is also made to make sure there aren't multiple tax sites for a single vendor (I guess you'll need to use UPDATE_ROW to turn off the tax reporting site flag on another record before inserting your new record).

Obviously if the ROWID you specify doesn't exist you will get an error, equally if you try and insert invalid data you will get an error as well.

If you pass in a Shipping Location then a call will be made to the ap_po_locn_association_pkg.update_row package to update the location ID you've specified. This appears to be optional.

Example;
There is a "sample" script here which performs an update on the VAT Code attached to suppliers.