Results for “orig_system_reference”

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

Overview

AMS_DS_ORIG_SYS_REFERENCES_V is a VALID database view owned by the APPS schema within the AMS (Marketing) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view presents a filtered, TCA-oriented projection of origin system reference data — the cross-reference records that map an external or legacy system identifier to a native Oracle entity. Its role in EBS reporting and integration is to expose only the subset of origin system references that are both active and associated with parties, so downstream consumers (marketing data synchronization, customer data management, and third-party integration staging) can resolve an external system key to an HZ_PARTIES record without scanning the full reference table or applying filter predicates themselves.

Underlying Base Objects

The view is defined over the base object HZ_ORIG_SYS_REFERENCES, referenced through a synonym. That table is the TCA (Trading Community Architecture) repository for origin system cross-references, storing the pairing of an originating system name, an originating system reference value, the owning entity type, and the owning entity's primary identifier. The view imposes two hard-coded predicates on that source: STATUS = 'A' and OWNER_TABLE_NAME = 'HZ_PARTIES'. The result is a read-only, party-scoped slice of the base table. Because the definition is fully inlined — a simple SELECT with WHERE clauses and no joins — there is no aggregation or derived logic; every row in the view corresponds one-to-one with an active row in HZ_ORIG_SYS_REFERENCES whose owning table is HZ_PARTIES. This makes the view lightweight and predictable for query planning, but also means it inherits the base table's indexes and does not provide any additional tuning surface.

Key Columns

The view exposes five columns, directly inherited from the base table:

• ORIG_SYSTEM — the name of the external or source system that originated the reference (for example, a legacy CRM or billing system identifier). Together with ORIG_SYSTEM_REFERENCE it forms the lookup key from the external side.
• ORIG_SYSTEM_REFERENCE — the unique identifier of the entity as known in ORIG_SYSTEM. This is the value an inbound interface typically supplies when it needs to locate the corresponding Oracle party.
• OWNER_TABLE_NAME — the base table that owns the referenced entity. In this view it is always 'HZ_PARTIES' by virtue of the view's filter, so the column is effectively constant and useful mainly as a self-documenting marker in result sets.
• OWNER_TABLE_ID — the primary key of the owning row in the owner table, i.e., the PARTY_ID of the HZ_PARTIES record. This is the join key back to the party entity and the column most commonly used when resolving a cross-reference to a customer or party record.
• STATUS — the record status flag; the view restricts this to 'A' (active), excluding inactive or obsolete references.

Common Use Cases and Queries

The view is most often used to resolve an incoming external key to a party, or to enumerate the external identifiers attached to a known party. A typical inbound lookup is:

SELECT owner_table_id
  FROM ams_ds_orig_sys_references_v
 WHERE orig_system = :p_orig_system
   AND orig_system_reference = :p_reference;

The returned OWNER_TABLE_ID (PARTY_ID) is then joined to HZ_PARTIES or a party-related view to obtain names, account associations, or contact details. In the reverse direction, a reporting query lists all source-system mappings for a given party:

SELECT orig_system, orig_system_reference
  FROM ams_ds_orig_sys_references_v
 WHERE owner_table_id = :p_party_id;

Because the active-status and party-owner filters are embedded, these queries never need to restate those predicates, reducing the risk of returning stale or non-party references. For data migration and reconciliation, the view serves as an authoritative list of external-to-Oracle party mappings, and it is frequently joined to marketing staging tables during customer data synchronization. Note that any resolution should guard against duplicates — the base table may hold multiple references per party across different ORIG_SYSTEM values — and applications should treat (ORIG_SYSTEM, ORIG_SYSTEM_REFERENCE) as the composite lookup key.