Search Results oe_sys_params_upg_v




Overview
Explore in AI Chat

Overview

OE_SYS_PARAMS_UPG_V is an APPS-owned database view within the Oracle Order Management (ONT) product family. It is registered as a VALID object in EBS 12.1.1 and 12.2.2 and serves as a reporting and integration layer over the Order Management system parameters that control order entry behavior at the operating unit level. The view presents a denormalized representation of order management profile values by pivoting the key/value rows stored in the underlying parameters table into named columns. Each row in the view corresponds to the parameter set associated with a single operating unit, identified by ORG_ID.

The primary purpose of the view is to expose frequently referenced configuration flags — including FREIGHT_RATING_ENABLED_FLAG, CUSTOMER_RELATIONSHIPS_FLAG, COMPUTE_MARGIN_FLAG, AUDIT_TRAIL_ENABLE_FLAG, and FTE_SHIP_METHOD_ENABLED_FLAG — as discrete, query-friendly columns. This allows external reports, extensions, and integration routines to read Order Management setup data without invoking the OE_SYS_PARAMETERS API for every attribute.

Underlying Base Objects

The view is defined over two documented base objects: the synonym OE_SYS_PARAMETERS_SYN and the package OE_SYS_PARAMETERS. The FROM clause references OE_SYS_PARAMETERS_SYN (aliased SP), which resolves to the physical parameter storage table, while each descriptive column is produced by calling OE_SYS_PARAMETERS.VALUE with a parameter name string and the ORG_ID of the current row. The WHERE clause filters SP to PARAMETER_CODE = 'MASTER_ORGANIZATION_ID', meaning the driving row for each operating unit is the master organization parameter record, and the remaining flag values are derived at query time through the package function rather than stored as separate physical rows.

Because the descriptive columns depend on a PL/SQL package call, the view is not a simple projection of stored columns; the function is evaluated per row returned, which has performance implications for large queries.

Key Columns

  • ROW_ID, ORG_ID — row identifier and the operating unit identifier that anchors each parameter set.
  • MASTER_ORGANIZATION_ID — numeric value returned by OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID', ORG_ID).
  • FREIGHT_RATING_ENABLED_FLAG — indicates whether freight rating is enabled; the column most frequently associated with this view in user searches.
  • CUSTOMER_RELATIONSHIPS_FLAG — controls customer relationship processing behavior.
  • AUDIT_TRAIL_ENABLE_FLAG, COMPUTE_MARGIN_FLAG, FTE_SHIP_METHOD_ENABLED_FLAG — audit, margin computation, and shipping method controls respectively.
  • CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield context and attribute values carried from the underlying record.
  • WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN for standard audit tracking.

Common Use Cases and Queries

Typical usage includes diagnosing why freight charges are not calculated during order entry, validating setup across operating units, and feeding configuration data into custom reports or interfaces.

  • Checking freight rating status for a given operating unit:
SELECT org_id, freight_rating_enabled_flag
FROM   apps.oe_sys_params_upg_v
WHERE  org_id = :p_org_id;
  • Reviewing all order management flags for each organization:
SELECT org_id,
       master_organization_id,
       freight_rating_enabled_flag,
       compute_margin_flag,
       audit_trail_enable_flag
FROM   apps.oe_sys_params_upg_v
ORDER BY org_id;

Because the flag columns are resolved through OE_SYS_PARAMETERS.VALUE, queries should restrict ORG_ID wherever possible to avoid unnecessary function execution across all operating units.