Oracle EBS r12.1.3 – Address Purpose Db Tables 3


Screenshot of Update Address DetailsWe know that Oracle has a funny sense of humor, to constantly switch the way they design their table structures used in Oracle EBS.

I recently had to stage the details that is displayed when you update an Supplier Address’s detail, and had a hard time finding the table structure that is used to store the information of the “Address Purpose” check boxes.

Since they don’t use Java Forms anymore in r12, we cannot use the diagnostic menu to get this information. So I had to run a lot of time consuming scripts to detect the tables that store this information. So to spare anyone else’s time that is tasked with this requirement here is the answer…

Address Purpose for Supplier Sites

You can also configure this same 3 checkbox options under each site of the supplier. I had no hard time in finding them.

They were simple “_FLAG” fields in the AP_SUPPLIER_SITES_ALL table, named:

  1. PURCHASING_SITE_FLAG,
  2. RFQ_ONLY_SITE_FLAG and
  3. PAY_SITE_FLAG.

This is logical and easy enough. But the same cannot be said for the Address Level values…

Address Purpose for Supplier Addresses

For each Address entry in the “Address Book” you can set the “Address Purpose”. I don’t know exactly what they are used for, but you can override all the current sites under this address with this detail.

Oracle thought it would be nice to make this simple 3 flags a lot more complicated. So they saved the 3 values in side the HZ_PARTY_SITE_USES table. Each checkbox has it’s own entry in this table. You differentiate between each checkbox entry by filtering on the SITE_USE_TYPE field with the 3 values:

  1. ‘PURCHASING’ for “Purchasing”,
  2. ‘PAY’ for “Payment” and
  3. ‘RFQ’ for “RFQ Only”.

And the table has a field called: STATUS that depict a “A” for active (checked) and a “I” for inactive (unchecked).

I hope this information can help someone out!

 


Leave a comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

3 thoughts on “Oracle EBS r12.1.3 – Address Purpose Db Tables

  • Tracy Glasgow

    Hi,

    Quick question. What is the difference between the address purpose and the site purpose. We have an address that is RFQ only and the site is RFQ and PTP.

    Kind regards

  • Wasted Blogger Post author

    Hi Tracy,

    I worked on this about 3 years ago. So I’m not quite up to date anymore!

    If I remembered it correctly the Address purpose is merely an easy way to centralise editing the values if you have a lot of sites. Otherwise, you need to edit each one separately. All financial transactions are performed on each site level, so the value on the address level is really just a mere cache value.

    Bernard