Results for “opi_edw_perd_margin_fur”

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

Overview

The OPI_EDW_PERD_MARGIN_FUR table belongs to the Oracle Operations Intelligence (OPI) product family, a Business Intelligence application historically layered on top of Oracle E-Business Suite 12.1.1 and 12.2.2. It is an Enterprise Data Warehouse (EDW) fact-style table that stores period-based margin, cost of goods sold (COGS), production, and profitability measures aggregated across multiple analytical dimensions. The suffix _FUR follows the OPI naming convention for "fully summarized" or "full utilization reporting" aggregations that consolidate transactional detail into a period-oriented reporting structure.

In the context of ETRM 12.1.1, the table is owned by the OPI schema and contains 38 documented columns. The documentation explicitly notes that the object is "Not implemented in this database," and the OPI module itself is flagged as Obsolete. This means the table should be treated as a legacy, archival, or reference-only artifact in most current EBS environments; it may exist only in installations that deployed the OPI operational reporting suite prior to its deprecation.

From a heuristic Data Vault modeling perspective, the table is classified as standalone, meaning no strong hub/link/satellite pattern was inferred from its foreign-key topology. In practice, however, the density of surrogate *_FK_KEY columns and additive measures (amounts and quantities) indicates the object behaves as a dimensional fact table or, in Data Vault terms, a link-and-satellite composite where each row represents a unique combination of dimension keys plus period measures.

Key Information Stored

The table stores additive margin measures alongside a rich set of dimension foreign keys. The most significant columns include:

• ROW_ID – surrogate identifier; foreign key to CS_SYSTEMS_ALL_B_TEMP (ROW_ID), and the closest documented primary-key candidate. A secondary ROW_ID1 is also present.
• MARGIN_PERIOD_FK_KEY – the period dimension key that drives time-series margin reporting.
• OPERATING_UNIT_FK_KEY, SOB_FK_KEY – operating unit and set-of-books (ledger) context, essential for multi-org and multi-ledger analysis.
• ITEM_ORG_FK_KEY, UOM_FK_KEY – item and unit-of-measure dimension keys for product-level rollups.
• CUSTOMER_FK_KEY, BILL_TO_LOC_FK_KEY, SHIP_TO_LOC_FK_KEY, SHIP_LOCATION_FK_KEY, SALES_CHANNEL_FK_KEY – customer and shipping dimensions supporting channel and geography reporting.
• PROJECT_FK_KEY, PRIM_SALES_REP_FK_KEY, PRIM_SALESRESOURCE_FK_KEY – project and sales-rep attribution.
• BASE_CURRENCY_FK_KEY, INSTANCE_FK_KEY – currency and instance context for consolidation.
• Measures: COGS_G, COGS_B, PROD_AMT_G, PROD_AMT_B (global/base currency amounts), plus quantity measures PROD_LINE_QTY_INVOICED, PROD_LINE_QTY_CREDITED, SHIPPED_QTY, RMA_QTY, and ICAP_QTY.
• Descriptors CREATION_DATE and LAST_UPDATE_DATE, plus five user-defined key columns (USER_FK1_KEY … USER_FK5_KEY) and five user measures (USER_MEASURE1 … USER_MEASURE5) reserved for customer extension.

Business-key candidates are not formally documented beyond ROW_ID; the natural grain is the combination of MARGIN_PERIOD_FK_KEY, ITEM_ORG_FK_KEY, OPERATING_UNIT_FK_KEY, and CUSTOMER_FK_KEY.

Common Use Cases and Queries

Because the table is obsolete and typically unpopulated, use cases are primarily historical reporting or migration auditing. A representative query aggregates margin by period and operating unit:

SELECT margin_period_fk_key, operating_unit_fk_key, SUM(cogs_b), SUM(prod_amt_b) FROM opi_edw_perd_margin_fur GROUP BY margin_period_fk_key, operating_unit_fk_key;

Typical scenarios include period-over-period margin trend analysis, COGS variance versus production amounts, RMA and credited-quantity reconciliation, and sales-rep or channel profitability reporting. Analysts migrating off OPI often extract this data into modern warehouse schemas before decommissioning.

Related Objects

• CS_SYSTEMS_ALL_B_TEMP – referenced via OPI_EDW_PERD_MARGIN_FUR.ROW_ID → CS_SYSTEMS_ALL_B_TEMP, the only documented FK path; used for row-level lineage.
• OPI_EDW family fact tables (e.g., period, order, and shipment margin variants) sharing identifier conventions and dimension keys.
• Dimension source tables feeding the *_FK_KEY columns: item, customer, period, operating unit, and currency masters within OPI/EDW.
• Standard EBS reporting dependencies such as GL (SOB/ledger), INV (item/org), and AR (customer) master objects that underpin the surrogate keys.