Search Results ozf_lookups




Overview
Explore in AI Chat

Overview

OZF_LOOKUPS is a PL/SQL view owned by the APPS schema within the Oracle Trade Management (OZF) product family. Its purpose is to expose a filtered subset of Oracle E-Business Suite lookup values — specifically, only those lookup codes that belong to lookup types registered against Oracle Marketing, covering the Funds and Budgets functional areas. Rather than presenting the entire contents of the FND_LOOKUP_VALUES table, the view restricts its result set to the lookup types that the Trade Management and Marketing applications actually consume. The view's status is VALID in both Oracle EBS 12.1.1 and 12.2.2, and its definition is unchanged between those releases according to the ETRM documentation. Reporting, integration, and concurrent programs that need a clean, application-scoped list of marketing lookup codes reference this view instead of querying FND_LOOKUP_VALUES directly.

Underlying Base Objects

The view is defined over a single documented base object, FND_LOOKUP_VALUES, which is accessed through a SYNONYM in the APPS schema. FND_LOOKUP_VALUES is the standard EBS lookup-values table that stores every lookup code, meaning, description, enablement flag, effective dates, and the fifteen attribute columns. OZF_LOOKUPS does not join to any other table; all filtering is performed through the WHERE clause. The critical filter is LV.VIEW_APPLICATION_ID = 682, which restricts rows to the application identifier associated with the Marketing/Trade Management lookup set. Two additional predicates enforce language and security semantics: LV.LANGUAGE = USERENV('LANG') returns only the lookup descriptions in the session's current language, and LV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID) applies Oracle's lookup security model so that only lookup types the current user is authorized to see are returned.

Key Columns

  • LOOKUP_TYPE — the classification of the lookup (for example, a Funds or Budgets lookup category). This is the primary grouping column.
  • LOOKUP_CODE — the internal code stored on transactions and records; together with LOOKUP_TYPE it forms the semantic key.
  • MEANING — the user-facing display text for the lookup code in the session language.
  • DESCRIPTION — the longer descriptive text associated with the lookup code.
  • ENABLED_FLAG — indicates whether the lookup is currently active (Y) or disabled (N).
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range during which the lookup is valid.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield segments used by the Marketing/Trade Management setup to hold additional configuration.
  • TAG — a free-form tag column carried through from the base lookup table.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN record who created and last modified each row.

Common Use Cases and Queries

The view is typically used to populate LOVs, validate incoming interface data, and drive reporting on marketing configuration. A basic listing of enabled Funds and Budgets lookups might be written as:

SELECT lookup_type, lookup_code, meaning
FROM apps.ozf_lookups
WHERE enabled_flag = 'Y'
ORDER BY lookup_type, lookup_code;

Because effective dating is significant, a point-in-time query is common:

SELECT lookup_code, meaning
FROM apps.ozf_lookups
WHERE lookup_type = :p_lookup_type
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE);

A third frequent pattern validates that a submitted lookup code is legitimate before it is stored on a Funds or Budgets record, using LOOKUP_TYPE and LOOKUP_CODE as the match key. All such queries automatically inherit the language and security-group predicates defined in the view, so callers need not repeat them.