Search Results hz_win_source_exceps_u1

Overview

The AR.HZ_WIN_SOURCE_EXCEPS table is a data-quality exception log within the Oracle E-Business Suite Trading Community Architecture (TCA) model. It records situations in which a Single Source of Truth (SST) record — the consolidated party profile assembled from multiple external and internal data sources — contains attribute values that were not drawn from the highest-ranked source for that attribute. The highest-ranked source per attribute is defined in the companion setup table HZ_SELECT_DATA_SOURCES. When a user, concurrent program, or API overwrites the winning source value, the discrepancy is captured here as an exception row. The table is owned by the AR schema, resides in the APPS_TS_TX_DATA tablespace, and carries a status of VALID in the ETRM 12.2.2 documentation.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this table is best classified as a link entity. Its two mandatory foreign keys — PARTY_ID referencing HZ_PARTIES and ENTITY_ATTR_ID referencing HZ_ENTITY_ATTRIBUTES — form a natural association between a party and an entity attribute, with the exception context carried as descriptive payload. This is a heuristic classification suitable for data-warehouse lineage design, not a statement about the operational schema.

Key Information Stored

The table contains 13 documented columns. The most significant are:

• PARTY_ID (NUMBER(15), mandatory) — Foreign key to HZ_PARTIES; identifies the party whose SST record triggered the exception.
• ENTITY_ATTR_ID (NUMBER(15), mandatory) — Foreign key to HZ_ENTITY_ATTRIBUTES; identifies the specific attribute (for example, DUNS Number) whose source ranking was overridden.
• CONTENT_SOURCE_TYPE (VARCHAR2(30)) — Describes the origin of the data content that was actually stored.
• EXCEPTION_TYPE (VARCHAR2(30)) — Internally generated designation indicating how the exception arose, such as migration or API.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking row creation and modification.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program WHO columns identifying the request and program that last touched the row, enabling traceability back to batch jobs.

The declared primary key, HZ_WIN_SOURCE_EXCEPS_PK, is the composite (PARTY_ID, ENTITY_ATTR_ID). The unique index HZ_WIN_SOURCE_EXCEPS_U1 — the index name the user searched for — is a NORMAL, UNIQUE index in APPS_TS_TX_IDX spanning the same two columns. It therefore functions as the business-key candidate enforcing one exception record per party-attribute combination, and it is the index the optimizer will typically choose for equality lookups on those columns.

Common Use Cases and Queries

The primary use case is auditing and reporting on SST data-governance exceptions, particularly after large data migrations or bulk customer-load API calls where source precedence is routinely tested.

• Reporting all exceptions for a given party:

  SELECT e.party_id, e.entity_attr_id, e.content_source_type,
         e.exception_type, e.creation_date
  FROM   ar.hz_win_source_exceps e
  WHERE  e.party_id = :p_party_id;

• Counting exceptions by type to gauge migration quality:

  SELECT exception_type, COUNT(*)
  FROM   ar.hz_win_source_exceps
  GROUP  BY exception_type;

• Filtering to a specific attribute using the unique index:

  SELECT * FROM ar.hz_win_source_exceps
  WHERE  party_id = :p_party_id
  AND    entity_attr_id = :p_attr_id;

• Traceability by concurrent request: joining REQUEST_ID to FND_CONCURRENT_REQUESTS identifies which program generated each exception.

Related Objects

The following objects are most significant for joins and dependency analysis, drawn from documented FK and PK relationships:

• HZ_PARTIES — joined on HZ_WIN_SOURCE_EXCEPS.PARTY_ID = HZ_PARTIES.PARTY_ID.
• HZ_ENTITY_ATTRIBUTES — joined on HZ_WIN_SOURCE_EXCEPS.ENTITY_ATTR_ID = HZ_ENTITY_ATTRIBUTES.ENTITY_ATTR_ID.
• HZ_SELECT_DATA_SOURCES — defines the ranked source per attribute and explains why an exception was raised.
• HZ_PARTY_SITES and HZ_LOCATIONS — related TCA party objects commonly queried alongside SST exceptions.
• FND_USER, FND_LOGINS, and FND_CONCURRENT_REQUESTS — referenced by the WHO and concurrent-program columns for audit and traceability.