Search Results invoiced_amount




Overview
Explore in AI Chat

Overview

ICX_CST_ITEM_7DAY_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, shipped as part of the ICX (Oracle iProcurement) product family. Its documented purpose is "Margin Analysis Summary View by Item for the last 7 days." The view consolidates margin data at the inventory item level for a rolling seven-day window, giving procurement and sourcing users a compact picture of what was invoiced, what margin was realized, and how each item contributes to the overall sales and margin totals of the period.

In practice the view functions as the item-level detail layer behind iProcurement's margin analysis dashboards. Rather than exposing raw invoice or cost buildup records, it pre-aggregates them per item and period, and it derives several percentage-based measures — customer margin percent, share of total sales, and share of total margin — directly in SQL. Because the object is a view rather than a table, it carries no stored data of its own; every query re-executes the packaged functions and aggregation logic against the underlying margin analysis and margin build tables. The view is marked VALID and is documented against both EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view text references two base objects joined on BUILD_ID:

  • ICX_MARGIN_ANALYSIS MA — supplies the item, organization, build, invoiced amount, and margin figures. Rows with a NULL margin value are excluded by the view's WHERE clause.
  • CST_MARGIN_BUILD MB — the margin buildup header, providing the build context against which the analysis rows are matched.

Although the ETRM metadata lists no documented referenced base objects, the view definition itself is explicit: it selects from ICX_MARGIN_ANALYSIS and CST_MARGIN_BUILD. It also invokes three packaged functions in ICX_MARGIN_WEB_ANA_PKG — GET_ITEM_NUMBER, ICX_GET_TOTAL_SALES('7D'), and ICX_GET_TOTAL_MARGIN('7D') — so its behavior depends on that package being valid and on the caller's session context. Organization filtering is enforced through USERENV('CLIENT_INFO'): the first ten bytes of client info are parsed into an ORG_ID and matched against MA.ORG_ID, with a NVL fallback of -99. This means the view only returns data for the organization currently set in the user's session.

Key Columns

  • INVENTORY_ITEM_ID — the item identifier on which the summary is grouped.
  • ITEM_NUMBER — the item number returned by ICX_MARGIN_WEB_ANA_PKG.GET_ITEM_NUMBER for the inventory item.
  • PERIOD — the analysis period; the view groups by this column and restricts output with HAVING PERIOD = '7D', fixing the window to seven days.
  • SALES — SUM(INVOICED_AMOUNT), the total invoiced amount for the item over the period. This is the column most often sought when users search for "invoiced_amount."
  • MARGIN — the summed margin for the item over the period.
  • CUSTOMER_MARGIN_PCT — margin divided by invoiced amount, multiplied by 100 and rounded to two decimals; returns NULL when invoiced amount is zero.
  • TOTAL_SALES_PCT — the item's invoiced amount as a percentage of total 7-day sales, obtained from ICX_GET_TOTAL_SALES('7D').
  • TOTAL_MARGIN_PCT — the item's margin as a percentage of total 7-day margin, from ICX_GET_TOTAL_MARGIN('7D').
  • TO_DATE / FROM_DATE — the period end date and, for FROM_DATE, that date minus seven days.

Common Use Cases and Queries

The primary scenario is item-level margin reporting within iProcurement: identifying top-selling items, highlighting items whose margin percentage falls below expectations, and comparing each item's share of sales against its share of margin. A typical query filters to a set of items and orders by invoiced amount:

  • SELECT inventory_item_id, item_number, sales, margin, customer_margin_pct FROM apps.icx_cst_item_7day_v ORDER BY sales DESC;
  • SELECT item_number, sales, total_sales_pct, total_margin_pct FROM apps.icx_cst_item_7day_v WHERE customer_margin_pct < 10;
  • SELECT inventory_item_id, sum(sales) FROM apps.icx_cst_item_7day_v GROUP BY inventory_item_id;

Because results are org-context sensitive, the reporting session must have CLIENT_INFO populated with a valid organization before querying; otherwise the ORG_ID comparison resolves to -99 and returns no rows. Queries should also be aware that percentage columns are NULL by design when denominators are zero.