Results for “edw_sicm_sic_lstg”

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

Overview

The EDW_SICM_SIC_LSTG table is a Financial Intelligence (FII) interface staging object that holds Standard Industry Code (SIC) level data before it is loaded into the enterprise data warehouse. It resides in the FII schema and is documented as a VALID table in Oracle EBS 12.1.1 and 12.2.2. Its stated purpose in the ETRM metadata is the "Interface table for Standard Industry Code level," which positions it as a transient landing area for SIC classification records sourced from operational applications and consumed by downstream warehouse ETL processes.

The Data Vault classification mined from the foreign key structure is standalone. In modeling terms, this suggests the table behaves as an independent staging entity rather than participating in a hub, link, or satellite relationship within the documented FK graph. The only documented foreign key relationship is ROW_ID → CS_SYSTEMS_ALL_B_TEMP, indicating a link to a temporary systems staging table rather than to a persistent master data entity. This reinforces its role as a non-authoritative, pre-load interface surface.

Key Information Stored

The table contains 29 documented columns. The most significant include:

• SIC_CODE_PK — the surrogate primary key for the staging row, providing a stable unique identifier during interface processing.
• ROW_ID — a row identifier that also participates in the documented foreign key to CS_SYSTEMS_ALL_B_TEMP.
• SIC_CODE — the business Standard Industry Code value, the core attribute of the SIC level being staged.
• SIC_CODE_DP — the displayed or descriptive form of the SIC code, typically used for reporting labels.
• NAME — the textual name or description of the SIC classification.
• DESCRIPTION — a longer explanatory description of the SIC level.
• INSTANCE — the EBS instance identifier, supporting multi-instance or multi-org data segregation.
• REQUEST_ID — the concurrent request that created or processed the staging rows, essential for ETL traceability.
• OPERATION_CODE — indicates the operation to apply (typically insert, update, or delete) during the load.
• COLLECTION_STATUS — the processing status of the row within the collection or load cycle.
• ERROR_CODE — populated when validation or load failures occur, supporting error reconciliation.
• UPDATE_FACT_FLAG — a control flag governing whether downstream fact records should be updated.
• ALL_FK / ALL_FK_KEY — generic foreign key placeholders used by the interface framework to resolve parent references.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE15 — the standard EBS descriptive flexfield columns for extensible, client-specific attributes.

Common Use Cases and Queries

Typical usage centers on ETL monitoring, error reconciliation, and SIC dimension loading. Analysts frequently query this table to verify what was staged for a given concurrent request or to isolate failed rows before reprocessing.

• Load audit by request: SELECT SIC_CODE, NAME, COLLECTION_STATUS, ERROR_CODE FROM FII.EDW_SICM_SIC_LSTG WHERE REQUEST_ID = :p_request_id;
• Error isolation: SELECT * FROM FII.EDW_SICM_SIC_LSTG WHERE ERROR_CODE IS NOT NULL; to identify rejected SIC records.
• Pending fact updates: filter on UPDATE_FACT_FLAG to determine which staged SIC changes should propagate to warehouse fact tables.
• Instance-scoped reporting: constrain by INSTANCE when consolidating across multiple EBS instances.

Because this is a staging interface, production reporting should be directed at the post-load warehouse dimension rather than this table, which is typically purged after successful loading.

Related Objects

• CS_SYSTEMS_ALL_B_TEMP — the sole documented FK target, joined via EDW_SICM_SIC_LSTG.ROW_ID = CS_SYSTEMS_ALL_B_TEMP.ROW_ID, representing the temporary systems staging source.
• SIC dimension tables in the FII warehouse — the ultimate target of the staged SIC_CODE data after transformation.
• Concurrent program / request metadata (FND_CONCURRENT_REQUESTS) — linked through REQUEST_ID for load traceability.
• Downstream fact tables — updated conditionally based on UPDATE_FACT_FLAG.
• Other EDW_SICM_* interface tables — sibling staging objects following the same collection/status pattern.