Search Results convert_uom

Overview

APPS.POA_EDW_UTIL is a utility PL/SQL package in Oracle E-Business Suite that supplies unit-of-measure (UOM) conversion logic for the Procurement and Oracle Advanced Planning / Enterprise Data Warehouse (POA/EDW) product family. Its header identifies it as a clone of conversion routines originally delivered in the INV_CONVERT package: the internal comment "Cloned from inv_convert.inv_um_conversion" precedes the CONVERT_UOM procedure, and "Cloned from inv_convert.inv_um_convert" precedes the GET_UOM_RATE function. This lineage is significant because INV_CONVERT is the canonical Inventory module utility for UOM arithmetic, and the POA/EDW clone exists so that downstream warehouse, planning, and analytical extractions can perform the same conversions without a runtime dependency on the Inventory PL/SQL API. The package is classified in the ETRM repository as a UTIL object owned by APPS, meaning it is not an interface or public API in the formal sense, but a shared helper invoked by other application code. The RDBMS version stamp (120.0, dated 2005) and the PRAGMA RESTRICT_REFERENCES declarations on both routines are characteristic of a stable, low-change utility that has been carried forward through the 12.1.1 and 12.2.2 release lines with minimal modification.

Key Procedures and Functions

• CONVERT_UOM — The core conversion routine, declared as a procedure. It accepts a source UOM code, a destination UOM code, and an item identifier, and returns the conversion rate through an OUT parameter. Because the item identifier is an input, the routine is designed to honor item-specific UOM conversions in addition to the standard class-based relationships, which is essential for items whose units are defined with item-level overrides.
• GET_UOM_RATE — A function counterpart that returns the conversion factor directly as its return value. It takes the item identifier, a precision value, a from-quantity, the from and to UOM codes, and the from and to UOM names. The inclusion of a precision argument and of human-readable UOM names indicates it is tuned for reporting and extraction scenarios where rounding behavior must be controlled and where names may be needed for display or reconciliation.

Both routines carry PRAGMA RESTRICT_REFERENCES declarations for WNDS (write no database state), WNPS (write no package state), and RNPS (read no package state). This asserts that neither routine mutates database or package state, which permits them to be called from SQL statements and from contexts that enforce purity, such as function-based indexes and certain view definitions.

Tables Accessed

The ETRM metadata documents three tables referenced through APPS synonyms: MTL_UNITS_OF_MEASURE, MTL_UOM_CLASS_CONVERSIONS, and MTL_UOM_CONVERSIONS. MTL_UNITS_OF_MEASURE holds the UOM code definitions themselves, including the UOM class each unit belongs to; it is consulted to resolve the class context for a conversion. MTL_UOM_CLASS_CONVERSIONS stores the standard conversion rates between units within the same UOM class, forming the baseline factor used when no item-specific rule applies. MTL_UOM_CONVERSIONS stores item-level conversion definitions, allowing a specific inventory item to carry its own conversion relationship between two units; this table is why the item identifier is a required parameter on both routines. Together these three tables constitute the complete Oracle UOM conversion model, and the package reads all three to resolve a rate.

Usage Notes

Because the package is classified as a UTIL object and is referenced by one other package, it is primarily invoked from PL/SQL rather than from end-user forms. Typical callers are EDW and planning extraction programs, concurrent programs that stage converted quantity data, and custom code that needs UOM arithmetic consistent with Inventory semantics without linking to INV_CONVERT. The purity pragmas make the routines safe to embed inside SQL queries, which is a common pattern in data-warehouse population logic. Consultants working with the "convert_uom" search term should note that the OUT-parameter procedure and the RETURN-value function are functionally parallel; choose the procedure form in procedural blocks and the function form where an expression is required. Both are read-only with respect to database state and should be treated as deterministic conversion helpers rather than as APIs that record or audit activity.