Results for “as_ref_sites”

4 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AS_REF_SITES is a table within the Oracle E-Business Suite AS - Sales Foundation module. According to the ETRM repository metadata, the object is explicitly marked as "No Longer Used" and is "Not implemented in this database." These two annotations are significant: they indicate that AS_REF_SITES was a legacy structure associated with the Sales Foundation (AS) product family, most likely tied to the earlier Oracle Sales and Marketing / Sales Compensation data model, and that it has been superseded by functionality consolidated into the Oracle Trading Community Architecture (TCA) and the Oracle Receivables (AR) product family in release 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure is link. This is a modeling suggestion rather than a documented architectural claim: the table carries two independent foreign keys (to HZ_REFERENCES and HZ_PARTY_SITES) alongside its own surrogate primary key, which is the classic signature of an associative/relationship entity rather than a descriptive satellite or a standalone hub.

Key Information Stored

Because the metadata documents the object as obsolete, the column inventory is limited to what appears in the FK/PK relationship data. The most important attributes are:

  • SITE_ID — the surrogate primary key, enforced by the constraint ASF_REF_SITES_PK. It uniquely identifies each row and serves as the link's hash key candidate in a Data Vault model.
  • REFERENCE_ID — foreign key to HZ_REFERENCES. This column anchors the row to a TCA reference record (typically a phone number, e-mail, URL, or other contact reference associated with a party).
  • PARTY_SITE_ID — foreign key to HZ_PARTY_SITES. This column anchors the row to a specific party site record in TCA, permitting a site-level association for the reference.

Together, REFERENCE_ID and PARTY_SITE_ID form the natural business-key candidate for this associative entity: a given reference can be linked to a given party site. No unique index names are documented in the ETRM metadata; only the surrogate PK constraint ASF_REF_SITES_PK is provided. If the table were live, one would expect a uniqueness constraint on the (PARTY_SITE_ID, REFERENCE_ID) pair to prevent duplicate associations, but the metadata does not document one.

Common Use Cases and Queries

Since AS_REF_SITES is not implemented in current 12.1.1 / 12.2.2 instances, direct querying is generally not possible. However, the historical use case is straightforward: reporting and resolving which contact references belong to which party sites, and vice versa. Customizations or legacy migrations that surface the table would have followed patterns such as:

  • Listing all references for a given party site: SELECT reference_id FROM as_ref_sites WHERE party_site_id = :party_site_id;
  • Finding all sites tied to a specific reference: SELECT party_site_id FROM as_ref_sites WHERE reference_id = :reference_id;
  • Joining to TCA to enrich the association: SELECT a.site_id, r.reference, ps.party_site_id FROM as_ref_sites a, hz_references r, hz_party_sites ps WHERE a.reference_id = r.reference_id AND a.party_site_id = ps.party_site_id;

The recommended modern approach is to query HZ_REFERENCES and HZ_PARTY_SITES directly, or the TCA views HZ_PARTY_SITE_USES / HZ_CONTACT_POINTS, which carry the equivalent relationships in current releases.

Related Objects

The documented foreign keys point to the two principal TCA dependencies:

  • HZ_REFERENCES — joined via AS_REF_SITES.REFERENCE_ID = HZ_REFERENCES.REFERENCE_ID.
  • HZ_PARTY_SITES — joined via AS_REF_SITES.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID.

Supporting TCA objects typically consumed alongside these include HZ_PARTIES (the owning party), HZ_CUST_ACCOUNTS and HZ_CUST_ACCT_SITES_ALL (site-to-account assignment), and HZ_CONTACT_POINTS, which stores the reference values themselves. No Oracle EBS API or concurrent program is documented as depending on AS_REF_SITES, further confirming its deprecated status. Implementations replacing this table should validate their reference-to-site logic against HZ_PARTY_SITE_USES, which is the functional successor in the 12.1.1 / 12.2.2 data model.