Results for “ozf_thresholds”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OZF_THRESHOLDS view is a reporting and integration layer within the Oracle E-Business Suite Trade Management (OZF) module. It exposes the definition of thresholds — the qualifying limits, tiers, or targets used by Trade Management to evaluate accruals, volume-based incentives, and other promotional agreements against performance data. In EBS 12.1.1 and 12.2.2, the object is registered in the APPS schema with a status of VALID and is classified as a multi-org secured view, meaning the rows returned are automatically filtered for the organization context of the session issuing the query.

Rather than storing data itself, the view presents a secured and denormalized projection of the threshold definition table. Its principal purpose is to supply threshold configuration data to reporting tools, concurrent programs, and external integrations that need to determine which thresholds are active, which calendar governs their periods, and the effective period windows in which they apply.

Underlying Base Objects

The view is defined over a single documented base object, OZF_THRESHOLDS_ALL_B, which is referenced through a synonym. OZF_THRESHOLDS_ALL_B is the "_ALL" table holding threshold definitions across all organizations; the OZF_THRESHOLDS view applies the standard Oracle multi-org filter to restrict the result set to the operating unit identified by the CLIENT_INFO environment setting.

The multi-org predicate compares NVL(ORG_ID, -99) against the organization identifier extracted from USERENV('CLIENT_INFO'). When a session has no organization context set, ORG_ID defaults to -99 and only records flagged as global (ORG_ID NULL) are returned. This mechanism is consistent with the multi-org architecture used throughout EBS 12.1.1 and 12.2.2.

Key Columns

The view exposes a set of descriptive, operational, and audit columns:

• THRESHOLD_ID — Primary identifier for the threshold definition row.
• THRESHOLD_CALENDAR — The calendar against which threshold periods are evaluated, linking the definition to an accounting or promotional period structure.
• START_PERIOD_NAME / END_PERIOD_NAME — The named periods that bound the threshold's active window. These are the values most frequently queried when determining whether a threshold applies to a given reporting period.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Date-effective bounds that complement the period names.
• OWNER — Identifies the owner or responsible party associated with the threshold.
• ENABLE_FLAG — Indicates whether the threshold is currently active and should be considered in calculations.
• ORG_ID / SECURITY_GROUP_ID — Multi-org and security grouping keys used for row-level access control.
• Audit and WHO columns — CREATION_DATE, CREATED_BY, CREATED_FROM, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the REQUEST_ID / PROGRAM_APPLICATION_ID / PROGRAM_ID triplet that records the concurrent program that last affected the row.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — The standard Oracle flexfield descriptor columns for customer-defined extension attributes.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the underlying entity objects.

Common Use Cases and Queries

Because the view enforces organization security, it is the preferred access path for reports that must respect the operating unit boundary. Typical scenarios include listing active thresholds bounded by a given period, resolving the calendar assigned to a threshold, and feeding integration extracts with threshold definitions.

The START_PERIOD_NAME column is frequently used as a filter predicate. A representative query retrieving active thresholds whose window begins in a specific period is:

• SELECT threshold_id, threshold_calendar, start_period_name, end_period_name, enable_flag FROM ozf_thresholds WHERE start_period_name = 'JAN-24' AND enable_flag = 'Y';
• SELECT threshold_id, start_period_name, end_period_name FROM ozf_thresholds WHERE org_id = :p_org_id ORDER BY start_period_name;

Reports invoking these queries from a concurrent program should initialize the organization context so that the multi-org predicate resolves correctly; otherwise only global definitions (ORG_ID NULL) are returned.