Search Results icx_get_total_margin




Overview
Explore in AI Chat

Overview

The APPS.ICX_MARGIN_WEB_ANA_PKG package is a PL/SQL component within the Oracle E-Business Suite E-Business Intelligence / Supply Chain Intelligence family, historically associated with the Oracle Sales Analyzer and margin analysis web reporting facilities. It serves as the backend engine for computing and exposing gross margin and sales figures to web-based analytical dashboards, particularly those rendered through the Oracle iStore / ICX (Interaction Center eXtension) web application framework. The package extracts transactional cost and revenue information from Oracle Order Management, Inventory, and Cost Management source tables, aggregates it into margin analysis structures, and returns summary values that web pages and reports can display to users without requiring direct access to the underlying costing tables.

The package declares AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking schema rather than the owner (APPS). In EBS 12.1.1 and 12.2.2 deployments this is significant because it means callers must themselves possess the appropriate grants on the referenced tables and synonyms. The package header carries a legacy version identifier (115.0) with a date stamp of 99/08/09, indicating it is a long-standing codebase component whose public interfaces have remained stable across many releases.

Key Procedures and Functions

The documented API surface consists of four program units:

  • BUILD_ICX_CST_MARGIN_TABLE — A procedure that populates the margin analysis staging and reporting tables. It is the data-preparation routine that must run before the query functions return meaningful results.
  • ICX_GET_TOTAL_MARGIN (IN_PERIOD) — A function returning a NUMBER that yields the aggregate gross margin for a given accounting or reporting period supplied as a VARCHAR2 parameter. This is the object the user searched for by name (icx_get_total_margin). It is declared with pragma restrict_references using WNDS, RNPS, WNPS, guaranteeing it writes no database state, reads no package state, and writes no package state. This makes it safe for use inside SQL statements and views.
  • ICX_GET_TOTAL_SALES (IN_PERIOD) — A function returning a NUMBER that yields total sales for the specified period, mirroring the margin function and carrying the same restricted-reference pragma.
  • GET_ITEM_NUMBER (in_ITEM_ID) — A function returning a VARCHAR2 that resolves an internal inventory item identifier into a display-ready item number, also pragma-restricted for deterministic use.

The pragma restrictions on the three functions are deliberate: they allow margin and sales values to be embedded directly in SQL SELECT lists and reporting queries, a common pattern in the margin web analysis pages.

Tables Accessed

Via APPS synonyms, the package reads and writes the following documented tables:

  • CST_MARGIN_BUILD and CST_MARGIN_TEMP — Working and staging tables used by BUILD_ICX_CST_MARGIN_TABLE to assemble cost and margin data before consolidation into final analysis tables.
  • ICX_MARGIN_ANALYSIS — The primary reporting table from which the getter functions derive total margin and total sales figures for a requested period.
  • ICX_MARGIN_ANALYSIS_ERR — An error log table capturing rows that fail validation or transformation during the build, supporting troubleshooting of the margin build process.
  • MTL_SYSTEM_ITEMS_KFV — The key flexfield view over inventory items, used by GET_ITEM_NUMBER to translate item IDs into concatenated, user-facing item numbers.

Usage Notes

This package is not typically exposed directly to end users. It is invoked by the margin web analysis pages and by concurrent programs that periodically refresh the margin data. The documented metadata states it is referenced by thirteen other packages, confirming its role as a shared utility rather than a standalone entry point. In a 12.1.1 or 12.2.2 environment, the standard operating sequence is to execute BUILD_ICX_CST_MARGIN_TABLE on schedule to repopulate ICX_MARGIN_ANALYSIS, after which ICX_GET_TOTAL_MARGIN and ICX_GET_TOTAL_SALES are called by reporting code to retrieve period-specific totals. Custom code should respect the AUTHID CURRENT_USER declaration and ensure the calling schema holds the necessary object privileges; because the functions are pragma-restricted, they may be safely embedded in SQL, including views and custom concurrent program queries.