Search Results medchn_init_fk_key

Overview

OPI.OPI_EDW_COGS_F is a fact table in the Oracle E-Business Suite (12.1.1 and 12.2.2) Operational Intelligence (OPI) / Enterprise Data Warehouse schema. It stores Cost of Goods Sold (COGS) information derived from inventory activities, specifically shipment transactions and Return Material Authorization (RMA) transactions, while also accounting for the effects of intercompany invoicing. The table consolidates data from inventory (INV) and costing (CST) sources, along with shipment details from Order Entry (OE) and Shipping (SHP), to provide a shipment-level view of COGS dollar value and associated item quantity.

Data is retained at the shipment line detail level. COGS values are sourced from material account distributions and are split based on shipped quantity at that granularity. Because COGS can be held at the cost element level within MTL_TRANSACTION_ACCOUNTS, distributions associated with non-material cost elements—material overhead, resources, overhead, and OSP—return zero COGS-related item quantity. Total COGS is derived by aggregating values across sales orders, RMA transactions, and intercompany account payable invoices. The storage resides in the APPS_TS_SUMMARY tablespace with a PCT Free of 10.

Per the heuristic Data Vault classification mined from the foreign key structure, this object is modeled as a standalone structure rather than a conventional hub, link, or satellite. Analysts translating this fact into a Data Vault model should treat it as a self-contained fact construct, since it carries its own surrogate key and does not resolve into distinct hub or link dependencies through documented foreign key relationships.

Key Information Stored

The table contains 101 documented columns. The surrogate primary key is COGS_PK_KEY, defined by the unique index OPI_EDW_COGS_F_PK. A second unique index, OPI_EDW_COGS_F_U1, spans COGS_PK and COGS_PK_KEY, making these the documented business-key candidates. A nonunique index OPI_EDW_COGS_F_N1 exists on ORDER_LINE_ID.

Among the most significant columns:

• COGS_T / COGS_G / COGS_B — the COGS dollar value measures across transactional, global, and base currency representations.
• COGS_PK and COGS_PK_KEY — the business-key candidate and surrogate primary key.
• ORDER_NUMBER and ORDER_LINE_ID — order identification at the line level.
• SHIPPED_QTY_B and RMA_QTY_B — shipped and RMA quantities in base units of measure.
• COST_ELEMENT — the cost element classification governing COGS distribution behavior.
• OFFER_HDR_FK_KEY and OFFER_LINE_FK_KEY — foreign keys linking to offer/order header and line dimensions; OFFER_HDR_FK_KEY is the column most directly relevant to the user search.
• CUSTOMER_FK_KEY, SHIP_TO_LOC_FK_KEY, BILL_TO_LOC_FK_KEY — customer and location dimension references.
• ITEM_ORG_FK_KEY and INV_ORG_FK_KEY — item and inventory organization references.
• DATE_SHIPPED_FK_KEY, DATE_SCHEDULED_FK_KEY, DATE_REQUESTED_FK_KEY, DATE_PROMISED_FK_KEY, DATE_BOOKED_FK_KEY, COGS_DATE — date dimensions supporting lead-time measures.
• REQUEST_LEAD_TIME, PROMISE_LEAD_TIME, ORDER_LEAD_TIME — computed lead-time measures.

Common Use Cases and Queries

Typical usage centers on COGS analysis by order, item, customer, and time period. A common pattern joins offer header keys to resolve order context:

SELECT c.ORDER_NUMBER, c.SHIPPED_QTY_B, c.COGS_B
FROM OPI.OPI_EDW_COGS_F c
WHERE c.OFFER_HDR_FK_KEY = :offer_hdr_fk_key
AND c.COST_ELEMENT = 'MATERIAL';

Reporting scenarios include margin and profitability analysis, RMA impact assessment, intercompany COGS reconciliation, and shipment lead-time performance tracking. Aggregating COGS across order sources yields total cost of goods sold, while filtering on RMA_QTY_B isolates return activity. Because non-material cost elements return zero quantity, quantity-based reports should filter on COST_ELEMENT or handle these rows explicitly.

Related Objects

The fact resolves dimensions through its foreign keys:

• OPI_EDW_OFFER_HDR_D — joined via OFFER_HDR_FK_KEY for order header context.
• OPI_EDW_OFFER_LINE_D — joined via OFFER_LINE_FK_KEY for order line detail.
• OPI_EDW_CUSTOMER_D — joined via CUSTOMER_FK_KEY.
• OPI_EDW_ITEM_ORG_D — joined via ITEM_ORG_FK_KEY.
• OPI_EDW_DATE_D — joined via the various DATE_*_FK_KEY columns.
• MTL_TRANSACTION_ACCOUNTS — source of COGS account distributions.
• OE_ORDER_LINES_ALL — source order line detail referenced through ORDER_LINE_ID.
• WSH_DELIVERY_DETAILS — shipment detail referenced through DELIVERY_ID.