Search Results canonical_to_number

Overview

FND_NUMBER is a low-level Oracle E-Business Suite utility package owned by the APPS schema that provides canonical conversion services for numeric values. Its central purpose is to translate between Oracle's internal numeric representation and a locale-neutral "canonical" character format, and vice versa. This is essential wherever EBS components must exchange, store, compare, or transmit numbers in a form that is independent of the session's NLS_NUMERIC_CHARACTERS and territory settings.

The package is classified in ETRM as OTHER, reflecting its role as supporting infrastructure rather than a business-facing API. With a documented footprint of 1261 other packages referencing it, FND_NUMBER is one of the most widely depended-upon utilities in the EBS technology stack. Internally it maintains module-level state — a canonical mask (canonical_mask), a decimal character (decimal_char), and a group separator (group_separator) — which are established during initialization and used to render or interpret canonical text consistently across the application.

Key Procedures and Functions

The package exposes four documented routines:

• canonical_to_number — A function that accepts a canonical character string and returns the corresponding NUMBER value. This is the routine users encounter when searching for "canonical_to_number," and it is the inverse of number_to_canonical. It carries the PRAGMA RESTRICT_REFERENCES directive with WNDS, WNPS, and RNDS, making it safe for use in SQL statements and guaranteeing that it does not read or write package state or database tables.
• number_to_canonical — A function that performs the opposite transformation, accepting a numeric value and returning its canonical character representation. It likewise declares WNDS, WNPS, and RNDS purity, so it can be invoked from SQL without side effects.
• initialize — A procedure that establishes the package's working state: the canonical mask, decimal character, and group separator. It is typically called during session or package startup so that subsequent conversions reflect the intended canonical format.
• test — A diagnostic procedure included to verify package functionality. It is used for development and validation rather than production processing.

Tables Accessed

The ETRM metadata documents no tables referenced by FND_NUMBER through APPS synonyms. This is consistent with the package's declared purity: both conversion functions are tagged WNDS and WNPS, meaning they neither read nor write database state. The package operates entirely on in-memory values and its own module-level variables. The absence of table dependencies is a deliberate design characteristic, allowing the conversion functions to be called freely from SQL, PL/SQL, and view definitions without introducing read-consistency or performance concerns.

Usage Notes

FND_NUMBER is an internal technology-layer utility rather than a user-invoked feature. It is most commonly called from custom PL/SQL and SQL code that must normalize numbers across NLS environments — for example, converting a value stored in canonical form before comparison or arithmetic, or producing canonical text suitable for inter-module transmission. Because canonical_to_number and number_to_canonical are RESTRICT_REFERENCES-pure, they may be embedded directly in SELECT statements and WHERE clauses.

Given that more than twelve hundred packages depend on it, developers should treat FND_NUMBER as stable infrastructure and avoid modifying or overloading its behavior. In Oracle EBS 12.1.1 and 12.2.2, the package remains available under the APPS schema without documented signature changes. Applications that rely on locale-aware formatting should invoke initialize before performing conversions so that canonical_mask, decimal_char, and group_separator are set appropriately. The test procedure may be run manually to confirm that conversion behavior matches expectations after an upgrade or environment change.