Search Results moac_sql_change

Overview

OE_SYS_PARAMETERS_PVT is the private PL/SQL package that manages the retrieval and caching of Oracle Order Management system parameter values within Oracle E-Business Suite 12.1.1 and 12.2.2. Order Management exposes a large number of configurable parameters that govern scheduling, shipping, freight rating, reservations, approvals, retrobilling, recurring charges, and related order-entry behaviors. These parameters are persisted in the OE_SYS_PARAMETERS_ALL table at the operating unit level. The package body provides the internal machinery by which other Order Management APIs and forms read those values efficiently, caching them in package-level global variables so that repeated lookups do not require repeated SQL execution.

The source header identifies the file as OEXVSPMB.pls and shows a Multi-Org Access Control (MOAC) revision, denoted by the MOAC_SQL_CHANGE markers embedded in the body. This reflects the architectural shift introduced with R12, in which operating unit context is passed explicitly rather than derived solely from client information.

Key Procedures and Functions

Two documented functions are exposed by this package, classified as PVT (private API):

• VALUE — The principal accessor through which callers obtain the current value of a named Order Management system parameter. Callers supply a parameter code and, in the MOAC architecture, an operating unit identifier so that the correct organization-specific value is returned. This function underpins the caching mechanism, populating the internal value table when a parameter has not yet been retrieved.
• GET_AR_SYS_PARAMS — Retrieves Receivables system parameters relevant to Order Management processing. Order entry depends on Receivables configuration for several downstream behaviors, and this function surfaces those settings to the Order Management layer.

The body also declares a private function, find, which accepts a parameter code and organization identifier and returns the parameter value as a VARCHAR2. It further declares Get_Value_From_Db, which queries OE_SYS_PARAMETERS_ALL directly for a given parameter code and optional organization identifier. The global variable g_org_id (defaulted to 0) holds the current organizational context, and numerous flag variables—including g_freight_rating_flag, g_ship_method_flag, g_lad_flag, g_request_date_flag, g_promise_date_flag, g_partial_reservation_flag, g_enable_retrobilling, g_no_response_from_approver, and g_recurring_charges—cache the specific parameter values once loaded.

Tables Accessed

• OE_SYS_PARAMETERS_ALL — The primary source table. It stores Order Management system parameter codes and their values per operating unit. The package reads from this table through the private cursor in Get_Value_From_Db, filtering on parameter code and org_id, to populate the cache.
• AR_SYSTEM_PARAMETERS_ALL — Referenced by GET_AR_SYS_PARAMS to obtain Receivables system parameters required by Order Management processing.
• PLITBLM — A standard Oracle EBS infrastructure table used in PL/SQL table and index-by-table processing, supporting the internal caching structures.

Usage Notes

As a PVT-classified package, OE_SYS_PARAMETERS_PVT is not intended for direct invocation by external or customer code. It is consumed internally by the Order Management application, and the ETRM metadata records that it is referenced by eleven other packages. Typical invocation paths include Order Management forms, such as the Sales Orders window, which call the accessor functions to determine behavior flags during order entry and scheduling, and concurrent programs that process orders and require consistent parameter values across their execution.

Developers investigating the g_org_id global should note that this variable establishes the operating unit context for parameter resolution under MOAC. Because values are cached in session-level globals, changes to a parameter made during the same session may not be reflected until the cache is refreshed or the session is reinitialized. Custom code requiring parameter values should use the supported public Order Management APIs rather than calling this private package directly.