Search Results cogs_pk_key

Overview

The table OPI.OPI_EDW_COGS_F is the Cost of Goods Sold (COGS) Shipment Fact table within the Oracle Operations Intelligence (OPI) product family of Oracle E-Business Suite. It serves as a dimensional fact table in the Operations Intelligence data warehouse, holding pre-aggregated and transaction-level measures for shipped goods, along with the foreign key references (denoted by the _FK_KEY suffix) that link each shipment record to the surrounding dimension structures. In EBS 12.1.1 it is documented with 101 columns and is owned by the OPI schema.

The metadata classifies this object, via a heuristic mined from its foreign key structure, as standalone under the Data Vault modelling convention. This suggestion implies the table behaves as a self-contained fact construct rather than resolving into a strict hub, link, or satellite pattern, though a fact table of this nature is most naturally treated as a satellite-style measure repository keyed by shipment grain. Its declared primary key, OPI_EDW_COGS_F_PK, is defined on the COGS_PK_KEY column.

Key Information Stored

The most significant columns fall into three groups: the surrogate key, the business-key candidates, and the analytical measures and dimension references.

• COGS_PK_KEY — the surrogate primary key and the single column forming OPI_EDW_COGS_F_PK.
• COGS_PK — appears in the unique index OPI_EDW_COGS_F_U1 (COGS_PK, COGS_PK_KEY), making it the primary business-key candidate. This is the column a user searching "cogs_pk" is most likely seeking.
• COGS_T / COGS_G / COGS_B — the core COGS monetary measures (transaction, global, and base currency amounts respectively).
• ACCOUNT — the general ledger account associated with the COGS posting.
• SHIPPED_QTY_B / RMA_QTY_B / ICAP_QTY_B — shipped, return-material-authorization, and intercompany quantity measures.
• ORDER_NUMBER / ORDER_LINE_ID / ORDER_DATE / DELIVERY_ID — order and delivery identifiers that anchor the shipment to its source transaction.
• COGS_DATE / COGS_DATE_FK_KEY and DATE_SHIPPED_FK_KEY — the accounting date and shipping date references.
• OPERATING_UNIT_FK_KEY / INV_ORG_FK_KEY / ITEM_ORG_FK_KEY / BASE_UOM_FK_KEY — organizational and item dimension references.
• CUSTOMER_FK_KEY / SHIP_TO_SITE_FK_KEY / BILL_TO_SITE_FK_KEY — customer and site dimension references.
• BASE_CURRENCY_FK_KEY / TRX_CURRENCY_FK_KEY — currency dimension references that give meaning to the COGS measure set.
• COST_ELEMENT — the cost component being reported.
• LOT / SERIAL_NUMBER / REVISION — item instance identifiers carried on the shipment.
• LAST_UPDATE_DATE / CREATION_DATE — standard audit columns.

Common Use Cases and Queries

The principal reporting use case is COGS analysis by shipment grain — profit-and-loss, margin, and cost-of-sales reporting sliced by item, customer, channel, operating unit, or accounting period. A frequent lookup pattern resolves a business key to its surrogate:

SELECT cogs_pk_key, cogs_b, shipped_qty_b, cogs_date
FROM opi.opi_edw_cogs_f
WHERE cogs_pk = :p_cogs_pk;

Aggregation queries roll the measures up by dimension:

SELECT operating_unit_fk_key, cost_element, SUM(cogs_b) cogs
FROM opi.opi_edw_cogs_f
WHERE cogs_date_fk_key BETWEEN :p_start AND :p_end
GROUP BY operating_unit_fk_key, cost_element;

Because the table carries extensive _FK_KEY references, joins to the OPI dimension views are typical for resolving surrogate keys into display values for financial and channel-margin dashboards.

Related Objects

The ETRM metadata does not enumerate explicit foreign key constraints to other tables; the relationship data classifies the object as standalone. The following related objects are the most significant by convention and are referenced through the _FK_KEY columns on this fact table:

• OPI_EDW_COGS_F_U1 — the unique index enforcing the COGS_PK / COGS_PK_KEY business key.
• Item and organization dimension (ITEM_ORG_FK_KEY, INV_ORG_FK_KEY, TOP_MODEL_ITEM_FK_KEY).
• Customer and location dimension (CUSTOMER_FK_KEY, SHIP_TO_SITE_FK_KEY, BILL_TO_SITE_FK_KEY, SHIP_TO_LOC_FK_KEY).
• Date dimension (COGS_DATE_FK_KEY, DATE_SHIPPED_FK_KEY, MONTH_BOOKED_FK_KEY).
• Currency dimension (BASE_CURRENCY_FK_KEY, TRX_CURRENCY_FK_KEY).
• Order and offer dimensions (ORDER_NUMBER, ORDER_LINE_ID, OFFER_HDR_FK_KEY, OFFER_LINE_FK_KEY).
• Sales resource and channel dimensions (PRIM_SALES_REP_FK_KEY, SALES_CHANNEL_FK_KEY, MEDCHN_INIT_FK_KEY, CAMPAIGN_INIT_FK_KEY).

All object names, column names, and key definitions above are drawn directly from the documented OPI.OPI_EDW_COGS_F metadata.