Search Results icx_margin_analysis




Overview
Explore in AI Chat

Overview

ICX.ICX_MARGIN_ANALYSIS is a table within the Oracle iProcurement (ICX) product schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description indicates, it stores margin analysis data broken down by different categories for different periods, providing a pre-aggregated analytical store used by iProcurement reporting and purchasing-intelligence features. The object is documented as VALID with an owner of ICX and a total of 12 physical columns across the ETRM 12.2.2 schema.

Under the heuristic Data Vault classification derived from its foreign-key structure, this object is characterized as standalone. In Data Vault modeling terms, this suggests treating ICX_MARGIN_ANALYSIS as a self-contained analytical table rather than as a hub, link, or satellite within a conventional normalized model. It aggregates measurable facts (invoiced amount, cost of goods sold, and margin) across a defined combination of business dimensions, so it functions in practice as a fact-like or summary table whose grains are established by its composite business key.

Key Information Stored

The table carries 12 documented columns. The most significant are described below, distinguishing the surrogate/primary-key construct from the documented business-key candidate.

  • BUILD_ID — Identifier associated with the build or aggregation run; participates in both the primary key and the unique index.
  • ORG_ID — Operating unit (multi-org) identifier, the standard partitioning dimension for EBS transactional and analytical data.
  • CUSTOMER_ID — Customer identifier for the margin record.
  • PRIMARY_SALESREP_ID — Primary sales representative associated with the analysis row.
  • TERRITORY_ID — Sales territory dimension.
  • INVENTORY_ITEM_ID — Inventory item for which margin is analyzed.
  • PARENT_INVENTORY_ITEM_ID — Parent item, supporting category or rollup-level analysis.
  • SOLD_TO_CUSTOMER_NAME — Denormalized customer name used for display and reporting.
  • PERIOD — The accounting or reporting period to which the margin figures apply.
  • INVOICED_AMOUNT — Revenue amount invoiced for the combination of dimensions.
  • COGS_AMOUNT — Cost of goods sold associated with the invoiced amount.
  • MARGIN — The resulting margin value for the row.

The primary key is ICX_MARGIN_ANALYSIS_PK, defined over BUILD_ID, ORG_ID, CUSTOMER_ID, PRIMARY_SALESREP_ID, TERRITORY_ID, INVENTORY_ITEM_ID, and PERIOD. The documented business-key candidate is the unique index ICX_MARGIN_ANALYSIS_U1, defined over ORG_ID, BUILD_ID, CUSTOMER_ID, PRIMARY_SALESREP_ID, TERRITORY_ID, INVENTORY_ITEM_ID, and PERIOD — the same logical grain, differing only in column ordering.

Common Use Cases and Queries

Typical usage centers on margin and profitability reporting by period, customer, sales representative, territory, and item. A representative query aggregates margin by period for an operating unit:

  • SELECT period, SUM(invoiced_amount) revenue, SUM(cogs_amount) cogs, SUM(margin) margin FROM icx.icx_margin_analysis WHERE org_id = :org_id GROUP BY period ORDER BY period;
  • Margin by customer or territory: SELECT territory_id, SUM(margin) FROM icx.icx_margin_analysis WHERE org_id = :org_id AND period = :period GROUP BY territory_id;
  • Item-level margin analysis, optionally rolled to PARENT_INVENTORY_ITEM_ID for category reporting.
  • Sales-representative performance comparisons using PRIMARY_SALESREP_ID.

Because the table is pre-aggregated and keyed on BUILD_ID and PERIOD, reports should filter on the appropriate build and period to avoid mixing generations of analytical output.

Related Objects

The metadata classifies the object as standalone, so no explicit foreign-key relationships are documented. In practice, its dimension columns correspond to standard EBS master and transactional entities, and joins are typically made on the following: