Search Results ap_supplier_sites_pk

Overview

AP_SUPPLIER_SITES_ALL is the Oracle Payables table that stores supplier site level attributes within Oracle E-Business Suite 12.1.1 and 12.2.2. A supplier in Oracle Payables (AP_SUPPLIERS) can have one or more sites, and each site represents a specific purchasing, payment, or remittance location associated with that supplier. The table is owned by the AP schema and is described in the ETRM repository as storing "information about your supplier site level attributes." Because a supplier may transact with a purchasing organization from multiple addresses with distinct payment terms, bank accounts, tax settings, and holding rules, AP_SUPPLIER_SITES_ALL is the operational record that drives invoice entry, payment processing, tax calculation, and withholding behavior at the site level.

From a dimensional modeling perspective, the heuristic Data Vault classification mined from the foreign key structure treats AP_SUPPLIER_SITES_ALL as a hub. This suggests it functions as a core business entity — the supplier site — whose identity is anchored by its own primary key, independent of the descriptive attributes that may be tracked elsewhere or versioned separately.

Key Information Stored

The table contains 164 columns in the documented 12.2.2 physical schema. The most significant columns are:

• VENDOR_SITE_ID — the surrogate primary key of the table, defined by the AP_SUPPLIER_SITES_PK constraint. It uniquely identifies each supplier site.
• VENDOR_ID — foreign key to AP_SUPPLIERS, linking the site to its parent supplier.
• VENDOR_SITE_CODE and VENDOR_SITE_CODE_ALT — the primary and alternate site identifiers used throughout invoice and purchasing entry.
• ORG_ID — the operating unit context, enabling multi-org access control over supplier sites.
• PAY_SITE_FLAG, PURCHASING_SITE_FLAG, RFQ_ONLY_SITE_FLAG — flags determining the functional role of the site.
• ADDRESS_LINE1 through ADDRESS_LINE4, CITY, STATE, ZIP, PROVINCE, COUNTRY — the supplier site address.
• PAYMENT_METHOD_LOOKUP_CODE, PAY_GROUP_LOOKUP_CODE, TERMS_ID, PAYMENT_PRIORITY — default payment instructions.
• BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_NUM, BANK_ACCOUNT_TYPE — remittance bank account details.
• INVOICE_CURRENCY_CODE, PAYMENT_CURRENCY_CODE — currency defaults for invoicing and payment.
• DISTRIBUTION_SET_ID — foreign key to AP_DISTRIBUTION_SETS_ALL.
• TOLERANCE_ID and SERVICES_TOLERANCE_ID — references to AP_TOLERANCE_TEMPLATES governing matching tolerances.
• HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG, HOLD_REASON — payment hold controls.
• INACTIVE_DATE — the date from which the site is considered inactive.
• PARTY_SITE_ID, LOCATION_ID — links into the Trading Community Architecture (TCA) model.

Two unique indexes are documented: AP_SUPPLIER_SITES_U1 (VENDOR_SITE_ID), the primary key, and AP_SUPPLIER_SITES_U2 (VENDOR_ID, VENDOR_SITE_CODE, ORG_ID), which is the business-key candidate that enforces uniqueness of the site code within a supplier and operating unit.

Common Use Cases and Queries

AP_SUPPLIER_SITES_ALL is queried in nearly every Payables workflow. Typical use cases include retrieving active payment sites for a supplier, validating that a site permits invoicing, and identifying sites configured for a specific pay group or currency.

A common pattern is to list active pay sites with their payment defaults:

• SELECT ass.vendor_site_id, ass.vendor_site_code, ass.pay_group_lookup_code, ass.terms_id, ass.payment_method_lookup_code, ass.invoice_currency_code FROM ap_supplier_sites_all ass WHERE ass.vendor_id = :vendor_id AND ass.pay_site_flag = 'Y' AND (ass.inactive_date IS NULL OR ass.inactive_date > SYSDATE) AND ass.org_id = :org_id;

Another frequent requirement is to join supplier and site information for invoice reconciliation or reporting:

• SELECT sup.vendor_name, ass.vendor_site_code, inv.invoice_num, inv.invoice_amount FROM ap_invoices_all inv JOIN ap_supplier_sites_all ass ON inv.vendor_site_id = ass.vendor_site_id JOIN ap_suppliers sup ON ass.vendor_id = sup.vendor_id WHERE inv.org_id = :org_id;

Site-level hold analysis is also common, particularly to identify sites where all payments are held:

• SELECT vendor_site_id, vendor_site_code, hold_reason FROM ap_supplier_sites_all WHERE hold_all_payments_flag = 'Y' AND org_id = :org_id;

Related Objects

AP_SUPPLIER_SITES_ALL sits at the center of an extensive foreign key network. The most significant related objects are:

• AP_SUPPLIERS — joined on VENDOR_ID; the parent supplier record.
• AP_INVOICES_ALL — references VENDOR_SITE_ID; invoices are entered against a supplier site.
• AP_INVOICE_PAYMENTS_ALL — references INVOICING_VENDOR_SITE_ID; links payments to the invoicing site.
• AP_SUPPLIER_CONTACTS — references VENDOR_SITE_ID; contacts belong to a site.
• AP_DISTRIBUTION_SETS_ALL — referenced by DISTRIBUTION_SET_ID; provides default distribution sets.
• AP_TOLERANCE_TEMPLATES — referenced by TOLERANCE_ID; governs matching tolerances.
• JAI_AP_TDS_INV_PAYMENTS, JAI_RETRO_LINE_CHANGES, OKL_CURE_REPORTS_ALL, OKL_PARTY_PAYMENT_DTLS, and OKL_TXL_EXTENSION_B — downstream or regional components (India tax, leasing, and tax extensions) that depend on VENDOR_SITE_ID.

These relationships confirm that AP_SUPPLIER_SITES_ALL is a foundational hub: invoices, payments, contacts, and multiple specialized extensions all resolve their supplier site context through its primary key.