Results for “bom_odi_ws_revisions”

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

Overview

BOM_ODI_WS_REVISIONS is a table owned by the BOM schema within the Oracle Bills of Material module. Its naming convention — the ODI_WS prefix — indicates that this is not a master reference entity used by the core Bills of Material application, but rather a staging or interface structure populated by Oracle Data Integrator (ODI) web services during data conversion, integration, or bulk-load operations. In practice, rows in this table represent an inbound set of engineering revision definitions that are associated with a specific integration session before they are validated and merged into the base BOM revision tables.

From a data modeling perspective, the supplied metadata classifies this object heuristically as standalone, meaning no declared foreign key relationships exist to core BOM tables. A Data Vault modeling suggestion would be to treat it as a staging or satellite-like structure anchored by SESSION_ID, with ROW_ID serving as the technical surrogate. Because the only documented reference points to a temporary CS_SYSTEMS_ALL_B_TEMP structure, the table behaves as a transient interface rather than a durable hub or link.

Key Information Stored

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

• ROW_ID — the surrogate technical identifier for the row, referenced against CS_SYSTEMS_ALL_B_TEMP; this is the primary join candidate.
• SESSION_ID — the integration session that loaded the record set, permitting batch-level isolation and purge.
• REVISION — the engineering revision code (for example, A, B, or C) being uploaded.
• REVISION_ID — the internal identifier that maps the incoming revision to an existing BOM revision record.
• REVISION_LABEL — the descriptive label attached to the revision for user-facing display.
• REVISION_HIGH_DATE — the effective expiration date of the revision, defining the upper bound of its validity window.
• PARENT_REVISION — identifies the predecessor revision, supporting revision lineage and change history.
• PUBLISH_FLAG — indicates whether the revision is intended for release or remains in a draft state.
• SEQUENCE_NUMBER — orders the records within the uploaded set, preserving deterministic processing.
• CREATION_DATE / CREATED_BY — audit columns recording when and by whom the staging row was inserted.

No unique business index is documented in the supplied metadata, so REVISION plus REVISION_ID should be treated as the practical business-key candidate until confirmed against the live data dictionary.

Common Use Cases and Queries

This table is typically queried during the loading and reconciliation phase of a Bills of Material conversion. A common pattern is to isolate the current session and count the staged revisions:

SELECT SESSION_ID, COUNT(*) staged_rows
FROM   BOM.BOM_ODI_WS_REVISIONS
GROUP  BY SESSION_ID;

Analysts also inspect the revision hierarchy and validity windows before merging:

SELECT REVISION, REVISION_LABEL, PARENT_REVISION,
       REVISION_HIGH_DATE, PUBLISH_FLAG
FROM   BOM.BOM_ODI_WS_REVISIONS
WHERE  SESSION_ID = :session_id
ORDER  BY SEQUENCE_NUMBER;

Reconciliation reporting compares staged revisions against the target revision table to detect missing or duplicated revision codes, and PURGE routines delete rows by SESSION_ID once the merge completes.

Related Objects

• CS_SYSTEMS_ALL_B_TEMP — the only documented FK target, joined on ROW_ID, providing the surrounding system context for the staged load.
• BOM_ODI_WS_* tables — companion staging tables loaded by the same ODI web service interface.
• Core BOM revision tables (BOM_BILL_OF_MATERIALS / revision entities) — the eventual merge targets for REVISION and REVISION_ID.
• ODI integration repository objects — control the session and mapping that populate SESSION_ID.
• BOM conversion APIs and concurrent programs — consume the staged rows to create valid revisions.

Because no foreign keys to core BOM tables are documented, dependencies on the base revision structures are maintained procedurally by the integration layer rather than enforced by the database.