Search Results edw_source_instances_n1

Overview

BIS.EDW_SOURCE_INSTANCES is an Oracle E-Business Suite table that stores configuration metadata for source instances registered with the Enterprise Data Warehouse (EDW) / Business Intelligence System (BIS) integration layer. Each row describes a source system instance that the EDW process recognizes, including how that instance links back to a warehouse target and how its collection process has been configured. The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, while its two indexes are hosted in APPS_TS_TX_IDX. In EBS 12.1.1 and 12.2.2 the object is delivered under the BIS schema with FND Design Data reference BIS.EDW_SOURCE_INSTANCES, and it carries the standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

From a data-modeling perspective the heuristic Data Vault classification for this table is standalone; the miner detected no strong parent-child FK structure other than a reference from ROW_ID to CS_SYSTEMS_ALL_B_TEMP. This suggests treating EDW_SOURCE_INSTANCES as an independent reference or configuration entity rather than as a hub, link, or satellite in a conformed warehouse model. The sole documented foreign-key relationship implies a soft coupling to the CS_SYSTEMS source-system registry, not a strict enforced dependency.

Key Information Stored

The table contains 35 documented columns. The most operationally significant are:

• INSTANCE_CODE (VARCHAR2 30) — the business key of the row and the column underpinning the unique index EDW_SOURCE_INSTANCES_U1. This is the natural identifier used to look up a specific source instance.
• ENABLED_FLAG (VARCHAR2 10) — activation status controlling whether the instance participates in EDW collection.
• WAREHOUSE_TO_INSTANCE_LINK (VARCHAR2 240) — the mapping string that ties the warehouse definition to the source instance.
• INSTANCE_PK_KEY and ALL_FK_KEY (NUMBER) — surrogate key columns used internally by the EDW load and referential machinery.
• INSTANCE_PK (VARCHAR2 40) and INSTANCE_DP (VARCHAR2 240) — the instance primary key value and its descriptive profile.
• ALL_FK (VARCHAR2 40) — the foreign-key reference value carried for cross-instance linkage.
• REQUEST_ID (NUMBER) — the concurrent request that created or last processed the row.
• NAME (VARCHAR2 80) and DESCRIPTION (VARCHAR2 240) — human-readable labels for the instance.
• OPERATION_CODE (VARCHAR2 30), COLLECTION_STATUS (VARCHAR2 30), ERROR_CODE (VARCHAR2 2000) — operational status of the most recent collection attempt, including the diagnostic error text.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE15 (VARCHAR2 240) — extension columns for customer-specific instance attributes.
• ROW_ID (ROWID) — the physical row identifier, documented as referencing CS_SYSTEMS_ALL_B_TEMP.

The unique index EDW_SOURCE_INSTANCES_U1 on INSTANCE_CODE is the definitive business-key candidate, while EDW_SOURCE_INSTANCES_N1 on INSTANCE_PK provides a non-unique access path for surrogate-key lookups. No separate enforced primary-key constraint is documented.

Common Use Cases and Queries

Typical usage centers on validating and troubleshooting EDW source instance registration. A common pattern is resolving a business instance code to its active configuration:

• Lookup by business key: SELECT * FROM bis.edw_source_instances WHERE instance_code = :code;
• List of enabled instances for a collection cycle: SELECT instance_code, name, enabled_flag FROM bis.edw_source_instances WHERE enabled_flag = 'Y';
• Collection health reporting: SELECT instance_code, collection_status, error_code FROM bis.edw_source_instances WHERE collection_status <> 'SUCCESS';
• Dependency and linkage review joining on INSTANCE_PK via EDW_SOURCE_INSTANCES_N1: SELECT a.instance_code, a.warehouse_to_instance_link FROM bis.edw_source_instances a WHERE a.instance_pk = :pk;
• Audit reporting using the WHO columns to identify instances not recently maintained.

Because ERROR_CODE is 2000 characters, reports that surface error text should truncate or substring it (for example SUBSTR(error_code,1,200)) before display.

Related Objects

The documented relationship data links this table to the following significant objects:

• CS_SYSTEMS_ALL_B_TEMP — referenced through EDW_SOURCE_INSTANCES.ROW_ID, providing the source-system registry context for each instance.
• BIS.EDW_SOURCE_INSTANCES_U1 — the unique index on INSTANCE_CODE, which is the access path for all business-key queries.
• BIS.EDW_SOURCE_INSTANCES_N1 — the non-unique index on INSTANCE_PK used for surrogate-key joins.
• FND_USER — implicit reference target for CREATED_BY and LAST_UPDATED_BY per the standard WHO column definitions.
• FND_LOGINS — implicit reference target for LAST_UPDATE_LOGIN.

Because the table is classified as standalone in the mined data vault structure, downstream EDW staging and collection programs consume it as a configuration source rather than as a transactional parent. Queries should therefore rely on INSTANCE_CODE or INSTANCE_PK as the stable join keys rather than assuming additional enforced relationships.