Search Results get_profile_value

Overview

GMA_GET_PROFILE is a small utility package owned by the APPS schema in Oracle E-Business Suite, classified in the ETRM repository as an "OTHER" API type. Its purpose is to provide a PL/SQL-callable wrapper around the Oracle EBS profile option infrastructure, allowing any server-side code to retrieve the value of a named profile option for the currently connected application user without directly querying the profile option tables. The package is declared with AUTHID CURRENT_USER, meaning that name resolution and privilege checking occur in the schema of the calling user rather than the APPS schema, which allows the package to be invoked safely from custom schemas and from within other PL/SQL units that rely on the caller's context.

Because Oracle's profile option mechanism normally exposes values to forms and concurrent programs through the FND_PROFILE built-in used in client-side code, GMA_GET_PROFILE serves the complementary need on the database tier: a simple, single-call function that returns the profile value as a VARCHAR2 for use in arbitrary SQL or PL/SQL logic. The header comment, dated 1999, indicates the package was ported during the early GMA (Process Manufacturing) code line, which explains its placement in the GMAPROFS source file naming convention.

Key Procedures and Functions

• GET_PROFILE_value — The sole documented function in the package. It accepts an application short name and a profile option name and returns the resolved profile value as a VARCHAR2. It is declared with PRAGMA RESTRICT_REFERENCES (WNDS, WNPS, RNPS), which guarantees that the function writes no database state, writes no package state, and reads no package state. This purity assertion permits the function to be called directly from SQL statements, including WHERE clauses and SELECT lists, and from other PL/SQL packages that enforce similar restrictions.

No other procedures, functions, or overloads are documented for this package. No public package-level variables or cursors are declared in the specification.

Tables Accessed

The underlying resolution of a profile value is performed against the standard Oracle Application Object Library profile tables, referenced through APPS synonyms:

• FND_APPLICATION — Used to translate the supplied application short name into the numeric application identifier required by the profile option lookup.
• FND_PROFILE_OPTIONS — Holds the definition of each profile option, including its application and user-accessibility attributes; consulted to validate the requested profile name.
• FND_PROFILE_OPTION_VALUES — Stores the actual values assigned at the site, application, responsibility, and user levels; this is the table from which the effective profile value is ultimately derived after applying the standard profile hierarchy.

Because the function carries the RNPS/WNPS/WNDS restrictions, all three of these tables are accessed in read-only mode only. The package performs no DML and therefore cannot be used to set or alter profile option values.

Usage Notes

GMA_GET_PROFILE is typically invoked from custom PL/SQL packages, stored procedures, database triggers, and SQL statements that execute in the server tier and require a profile option value. The ETRM metadata records that it is referenced by zero other packages in the standard application, confirming that it is not part of Oracle's internal dependency chain; its intended consumer is customer-developed or partner-developed code, particularly within Process Manufacturing extensions where the GMA schema lineage applies.

Typical invocation patterns include calling GET_PROFILE_value with the owning application's short name and the profile option's user-visible name to obtain values such as organizational defaults, numbering conventions, or feature flags. Because the function is a pure read with RESTRICT_REFERENCES declared, it may also be embedded in SQL:

• Within SELECT lists or WHERE predicates in custom reports and views.
• Within packages that compile under PL/SQL purity rules.
• Within concurrent program logic where the executing user's profile hierarchy determines the returned value.

Callers should note that the value returned reflects the profile hierarchy resolved for the database session, including any FND_GLOBAL initialization performed at session start; if the session context has not been initialized, profile resolution may fall back to site-level values only. The package is not documented as supporting bulk retrieval, caching, or multi-level profile queries, so repeated calls incur repeated catalog lookups against the three FND tables.