Search Results get_valueset_select

Overview

QP_Param_Util is a utility PL/SQL package owned by APPS within the Oracle E-Business Suite Advanced Pricing (QP) module. Its primary business function is to manage the parameter-driven configuration framework that underpins Oracle Pricing, Oracle Order Management, and related supply chain modules. Oracle Pricing relies on a set of system parameters that control defaulting behavior, processing logic, and functional switches at various levels — site, application, responsibility, user, and others. QP_Param_Util provides the programmatic interface for resolving, populating, inserting, and deleting those parameter values, as well as for constructing dynamic SQL fragments needed to drive value set lookups.

The package is classified as a UTIL (utility) API, meaning it is not a public, supported business API for external integration, but rather an internal helper used by other Oracle Pricing packages and forms. The ETRM metadata indicates the package is referenced by four other packages, underscoring its role as a foundational dependency within the pricing parameter infrastructure.

Key Procedures and Functions

• GET_VALUESET_SELECT — This is the procedure that is the subject of the user's search. Based on its name and the documented signature metadata, it constructs and returns a dynamic SELECT statement string (x_select_stmt) for a given value set identifier (p_valueset_id). It is used to build the SQL text that resolves valid values for a flexfield value set at runtime, enabling the pricing parameter framework to dynamically query legal parameter values.
• POPULATE_PARAMETER_VALUES — Accepts a parameter identifier, a seeded value, and a parameter level, and is responsible for populating the parameter values table with appropriate records for the supplied parameter. It is typically invoked when parameter defaults need to be materialized for a given level.
• INSERT_PARAMETER_VALUES — Given a level and a level name, inserts the relevant parameter value rows into the parameter values table. This supports establishing parameter values at a specific hierarchical level, such as a responsibility or user.
• DELETE_PARAMETER_VALUES — The counterpart to the insert routine; removes parameter value records for a specified level and level name, supporting cleanup or reconfiguration of parameter settings.
• GET_PARAMETER_VALUE — A function returning a VARCHAR2. Given a level, level name, and parameter code, it resolves and returns the effective parameter value. This is the principal lookup routine used throughout pricing to retrieve a configured parameter at runtime.
• IS_SEED_USER — A function returning VARCHAR2 that determines whether the current user context corresponds to a seeded (Oracle-provided) user, which affects whether seeded parameter values may be modified.

Tables Accessed

The package reads and writes several pricing parameter tables accessed through APPS synonyms. QP_PARAMETERS_B and QP_PARAMETERS_TL store the base and translated definitions of pricing parameters. QP_PARAMETER_VALUES and QP_PARAMETER_VALUES_S hold the actual parameter values and their corresponding security/sequence records. QP_PTE_REQUEST_TYPES_B is referenced in the context of pricing request types. DUAL is used for singleton queries, such as evaluating the IS_SEED_USER condition.

Usage Notes

QP_Param_Util is invoked internally by Oracle Pricing forms and by other PL/SQL packages within the pricing schema; the ETRM metadata notes it is referenced by four other packages. It is not intended for direct customer invocation. Customizations that need to read pricing parameter values should prefer supported public APIs. GET_VALUESET_SELECT is of particular interest when diagnosing dynamic value set lookups in pricing setup, as it generates the SQL used to validate parameter values against a value set definition. Because the package header dates to 2005 and carries an "noship" header tag, DBAs and developers should treat it as an internal, non-supported utility whose behavior may change across releases, including between EBS 12.1.1 and 12.2.2.