Search Results opi_edw_cogs_fstg

Overview

OPI_EDW_COGS_FSTG is a staging (interface) table owned by the OPI schema within the Oracle Operations Intelligence (OPI) product family. It serves as the inbound landing area for Cost of Goods Sold (COGS) Shipment Fact data before that data is validated, transformed, and loaded into the enterprise data warehouse star schema used for supply chain and order management analytics. In Oracle EBS 12.1.1 and 12.2.2, OPI provides pre-built analytic applications for order management, fulfillment, and profitability, and tables bearing the _FSTG suffix function as the extract-stage conveyor between operational EBS transaction tables and the dimensional fact tables consumed by reporting.

The documented physical schema at ETRM 12.1.1 lists 152 columns, reflecting the wide, denormalized shape typical of a fact staging interface: measures, foreign-key surrogate references, dates, and descriptive attributes are all flattened into a single row structure. The heuristic Data Vault classification derived from the mined foreign-key structure is standalone, meaning no deterministic hub, link, or satellite pattern emerges from the FK graph alone. As a modeling suggestion, this table is best treated as a link-with-satellite or staged fact accumulator: its many *_FK_KEY references behave like dimension link references, while its measures (COGS_B, COGS_G, COGS_T) and descriptive attributes behave like satellite payload.

Key Information Stored

The table centers on a COGS fact grain tied to shipment and order activity. The most significant columns include:

• COGS_B / COGS_G / COGS_T — the core COGS measures in base, global (functional reporting), and transaction currency amounts.
• COGS_PK and ROW_ID — the surrogate key identifier for the staged row. ROW_ID is documented as referencing CS_SYSTEMS_ALL_B_TEMP, indicating the row originates from an EBS source-system extraction context rather than a pure business key.
• COGS_DATE / COGS_DATE_FK / COGS_DATE_FK_KEY — the accounting/recognition date for the COGS entry, carrying both raw and surrogate-key forms for warehouse loading.
• ORDER_NUMBER, ORDER_LINE_ID, ORDER_DATE, DELIVERY_ID — transactional identifiers linking the staged COGS back to the order and delivery lifecycle.
• ITEM_ORG_FK / ITEM_ORG_FK_KEY, INV_ORG_FK / INV_ORG_FK_KEY, LOCATOR_FK / LOCATOR_FK_KEY, SHIP_INV_LOCATOR_FK / SHIP_INV_LOCATOR_FK_KEY — the item, organization, and locator dimensions that qualify the shipped quantity.
• SHIPPED_QTY_B, RMA_QTY_B, ICAP_QTY_B — quantity measures for shipments, returns, and inventory capitalization.
• CUSTOMER_FK, SHIP_TO_SITE_FK, BILL_TO_SITE_FK, SHIP_TO_LOC_FK, BILL_TO_LOC_FK — customer and site dimension references.
• SET_OF_BOOKS_FK, ACCOUNT, COST_ELEMENT — the financial context for the COGS posting.
• ERROR_CODE, OPERATION_CODE, COLLECTION_STATUS — interface control columns indicating load outcome and row disposition.
• CREATION_DATE, LAST_UPDATE_DATE — audit timestamps for the staging row.

Business-key candidacy is not formally documented via unique indexes in the supplied metadata; the surrogate identity is COGS_PK, and uniqueness is reinforced functionally by the combination of order line, shipment, item/organization, and COGS date.

Common Use Cases and Queries

Primary use cases involve loading and reconciliation of COGS shipment facts. Analysts query the staging table to verify extract completeness, diagnose load failures, and reconcile warehouse fact totals against EBS source transactions.

• Detecting rejected rows prior to warehouse load: SELECT ORDER_NUMBER, ERROR_CODE FROM OPI.OPI_EDW_COGS_FSTG WHERE ERROR_CODE IS NOT NULL;
• Aggregating staged COGS by accounting period and set of books: SELECT SET_OF_BOOKS_FK, COGS_DATE, SUM(COGS_B) FROM OPI.OPI_EDW_COGS_FSTG GROUP BY SET_OF_BOOKS_FK, COGS_DATE;
• Reconciling shipped quantity against COGS measures to validate cost flow for a specific item organization: SELECT ITEM_ORG_FK, SHIPPED_QTY_B, COGS_B FROM OPI.OPI_EDW_COGS_FSTG WHERE ITEM_ORG_FK = :p_org;
• Tracking return-driven COGS adjustments using RMA_QTY_B and RMA_VAL_G within a date window bounded by COGS_DATE BETWEEN :from AND :to.

Related Objects

The documented foreign-key linkage identifies CS_SYSTEMS_ALL_B_TEMP as the referencing source for ROW_ID, confirming the source-system extraction lineage. Additional significant dependencies in the OPI/EDW COGS flow include:

• CS_SYSTEMS_ALL_B_TEMP — referenced by ROW_ID; provides the source system and staging origin context.
• OPI_EDW_COGS_FS — the downstream COGS Shipment Fact star table populated from this staging interface.
• OPI_EDW_ITEM_ORG_D, OPI_EDW_INV_ORG_D, OPI_EDW_CUSTOMER_D — dimension tables resolved through the ITEM_ORG_FK, INV_ORG_FK, and CUSTOMER_FK surrogate keys.
• OPI_EDW_DATE_D — date dimension resolved through COGS_DATE_FK and related date foreign keys.
• OPI_EDW_SET_OF_BOOKS_D — financial dimension resolved through SET_OF_BOOKS_FK.
• OPI_EDW_TRX_CURRENCY_D — currency dimension reached via TRX_CURRENCY_FK and BASE_CURRENCY_FK.

These relationships position OPI_EDW_COGS_FSTG as the operational bridge between EBS transactional sources and the OPI analytic warehouse, with its surrogate-key columns designed to be resolved against the conformed OPI dimension tables during the fact load.