Search Results hz_orig_systems_u2

Overview

The AR.HZ_ORIG_SYSTEMS_B table is a foundational TCA (Trading Community Architecture) reference table that stores information about source systems — the systems that load data into the TCA Registry. This table holds basic attributes about each source system, including its name, description, and type, together with a status flag indicating whether the system is actively providing data to TCA. The documented source system type values are Spoke (for legacy systems) and Purchased (for third-party data providers). The base table is registered in the application as FND Design Data AR.HZ_ORIG_SYSTEMS_B and resides in the APPS_TS_SEED tablespace, consistent with its role as seeded reference data.

From a Data Vault modeling perspective, the heuristic classification mined from the FK structure is standalone. This suggests the table functions effectively as a reference or hub-like object: it is not itself a dependent link or satellite, but rather a stable parent that many other tables point to via ORIG_SYSTEM_ID. Because ORIG_SYSTEM (the short code) is also unique, the table can be treated as a natural-key reference dimension in downstream reporting models.

Key Information Stored

The primary key is ORIG_SYSTEM_ID (NUMBER(15)), the surrogate identifier for each source system. Two unique indexes serve as documented business-key candidates: HZ_ORIG_SYSTEMS_U1 on ORIG_SYSTEM_ID and HZ_ORIG_SYSTEMS_U2 on ORIG_SYSTEM. In the 12.2.2 documented physical schema both indexes additionally include ZD_EDITION_NAME, reflecting Edition-Based Redefinition (EBR) support.

The most significant columns are:

• ORIG_SYSTEM_ID — surrogate primary key identifying each source system.
• ORIG_SYSTEM — the source system code, unique business key.
• ORIG_SYSTEM_TYPE — classification, Spoke or Purchased.
• SST_FLAG — indicates whether the system is enabled for Single Source of Truth and used as a content source (Y/N or null).
• STATUS — Active or Inactive state of the source system.
• START_DATE_ACTIVE / END_DATE_ACTIVE — activation and deactivation dates defining the validity window.
• CREATED_BY_MODULE — the module that created the source system record.
• OBJECT_VERSION_NUMBER — optimistic locking / concurrency control column.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Who columns for audit lineage.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — descriptive flexfield (DFF) context and segment columns.
• ZD_EDITION_NAME — EBR edition identifier present in the 12.2.2 schema.

Common Use Cases and Queries

Typical uses include resolving human-readable source system names for operational reports, filtering data by origin, and verifying Single Source of Truth configuration prior to data loads. A common lookup pattern:

• SELECT orig_system_id, orig_system, orig_system_type, sst_flag, status FROM hz_orig_systems_b WHERE status = 'ACTIVE';
• Identify the SST content source: SELECT orig_system FROM hz_orig_systems_b WHERE sst_flag = 'Y' AND status = 'ACTIVE';
• Join transactional registry tables to this dimension: SELECT t.*, o.orig_system FROM <child_table> t, hz_orig_systems_b o WHERE t.orig_system_id = o.orig_system_id;

In 12.2.2, queries should be aware of EBR; when querying seed data, the ZD_EDITION_NAME = 'SET1' filter may be required for some editions. Reporting use cases include TCA data-quality dashboards, source-system inventory reports, and lineage analysis for party, location, and contact records.

Related Objects

Numerous tables carry ORIG_SYSTEM_ID as a foreign key to this table. The most significant include:

• WF_LOCAL_ROLES (and related WF_LOCAL_ROLES_TL, WF_LOCAL_ROLES_STAGE, WF_LOCAL_ROLES_TL_STAGE, WF_LOCAL_ROLES_OLD) — join on ORIG_SYSTEM_ID.
• JTF_LOC_POSTAL_CODES and JTF_LOC_AREAS_B — location reference data.
• PER_EMPDIR_* tables (PEOPLE, ASSIGNMENTS, ORGANIZATIONS, LOCATIONS, JOBS, POSITIONS, PHONES, and _TL variants) — HR employee directory views.
• AME_APPROVAL_GROUP_MEMBERS — approval management membership.
• AP_APINV_APPROVERS — invoice approval routing.
• EDR_PSIG_DETAILS and EDR_PSIG_PRINT_HISTORY — print/signature-related records.

These relationships confirm the table's role as a central reference dimension for origin tracking across TCA, HR, workflow, and approval subsystems.