Search Results customer_margin_pct




Overview
Explore in AI Chat

Overview

The view ICX_CST_ITEM_30DAY_V is a reporting object within Oracle iProcurement (product code ICX) that presents margin analysis summarized by inventory item over a rolling 30-day window. Its documented description, "Margin Analysis Summary View by Item for the last 30 days," identifies its purpose precisely: it aggregates invoiced revenue and margin at the item level and expresses those amounts as percentages of customer margin, total sales, and total margin for the period. The view is part of the iProcurement margin analysis framework and depends on the ICX_MARGIN_WEB_ANA_PKG package, which supplies item numbers and the period totals used as denominators. In Oracle EBS 12.1.1 and 12.2.2 the object belongs to the cost and margin analytics surface exposed to procurement and sourcing users, allowing buyers and category managers to compare supplier and item profitability rather than raw spend.

Underlying Base Objects

The documented view text selects from two base objects joined on BUILD_ID: ICX_MARGIN_ANALYSIS (aliased MA) and CST_MARGIN_BUILD (aliased MB). ICX_MARGIN_ANALYSIS stores the summarized margin rows, including INVENTORY_ITEM_ID, PERIOD, INVOICED_AMOUNT, MARGIN, ORG_ID, and BUILD_ID. CST_MARGIN_BUILD is the margin build header that identifies each build run; the join MA.BUILD_ID = MB.BUILD_ID ties analysis rows to a specific build. The view also calls three package functions: ICX_MARGIN_WEB_ANA_PKG.GET_ITEM_NUMBER to resolve INVENTORY_ITEM_ID to a display item number, ICX_GET_TOTAL_SALES('30D') for the total sales denominator, and ICX_GET_TOTAL_MARGIN('30D') for the total margin denominator. ETRM documentation for 12.2.2 lists no separate referenced base objects and records the object as not implemented in the sample database, meaning view text may differ or the object may be inactive in a given instance.

Key Columns

  • INVENTORY_ITEM_ID — internal identifier of the item summarized.
  • PERIOD — period code; the view filters to '30D' via the HAVING clause.
  • ITEM_NUMBER — item number returned by ICX_MARGIN_WEB_ANA_PKG.GET_ITEM_NUMBER.
  • SALES — sum of INVOICED_AMOUNT for the item in the period.
  • MARGIN — sum of MARGIN, excluding rows where MARGIN is null.
  • CUSTOMER_MARGIN_PCT — margin divided by invoiced amount, times 100, rounded to two decimals.
  • TOTAL_SALES_PCT — item sales as a percentage of total 30-day sales.
  • TOTAL_MARGIN_PCT — item margin as a percentage of total 30-day margin.
  • TO_DATE and FROM_DATE — period end date and the date 30 days earlier.

Common Use Cases and Queries

Typical usage involves ranking items by margin contribution, identifying low-margin or negative-margin items, and comparing an item's share of sales against its share of margin. The example below returns the top items by customer margin percentage:

SELECT item_number, sales, margin, customer_margin_pct, total_sales_pct, total_margin_pct
FROM icx_cst_item_30day_v
WHERE customer_margin_pct IS NOT NULL
ORDER BY customer_margin_pct DESC;

Because the view applies USERENV('CLIENT_INFO') to filter ORG_ID, results are restricted to the operating unit in the session context, so queries must run within an initialized iProcurement or Multi-Org session. Division-by-zero protection is built into the DECODE expressions, which return NULL rather than an error when denominators are zero.