Search Results ozf_reason_codes




Overview
Explore in AI Chat

Overview

APPS.OZF_REASON_CODES_VL is a language-dependent (VL, "view language") reporting view within the Oracle E-Business Suite Trade Management (formerly Oracle Trade Management / OZF) schema. It exposes the translatable and non-translatable attributes of trade management reason codes in a single, denormalized result set, resolving the master-detail relationship between the base reason code entity and its multilingual translation rows automatically. The view uses USERENV('LANG') in its WHERE clause to return only the translation matching the session's language, so consumers receive the correct NAME and DESCRIPTION without joining to the translation table themselves.

In Oracle EBS 12.1.1 and 12.2.2 the object is owned by APPS and is typically consumed by concurrent programs, OAF-based Trade Management pages, BI Publisher reports, and custom integrations that need a list of valid reason codes (for adjustments, invoicing, order changes, and similar trade events) filtered by language. Because the view is a synonym-based VL construct rather than a table, it is read-only and should be treated as a query surface.

Underlying Base Objects

The view is defined over two documented base objects, both accessed through APPS-owned synonyms:

  • OZF_REASON_CODES (aliased B) — the transactional base table holding the reason code definition, keyed by REASON_CODE_ID, including descriptive flexfield columns, ORG_ID, and status/date range columns.
  • OZF_REASON_CODES_ALL_TL (aliased T) — the translation table storing language-specific NAME and DESCRIPTION values keyed by REASON_CODE_ID and LANGUAGE.

The join predicate is B.REASON_CODE_ID = T.REASON_CODE_ID AND T.LANGUAGE = USERENV('LANG'). The view therefore returns exactly one row per reason code per session language, effectively hiding the _ALL_TL multitenant/translation structure. Note the view exposes B.ROWID as ROW_ID; this rowid is taken from the base table and is used by EBS framework (OAF) machinery for row identification.

Key Columns

The SELECT list projects a comprehensive set of columns:

Common Use Cases and Queries

Typical scenarios include populating a reason-code LOV, validating a submitted reason code before an adjustment or invoice, and reporting on active reason codes by operating unit and language.

List active reason codes for a given type and org:

  • SELECT reason_code_id, reason_code, name, description FROM apps.ozf_reason_codes_vl WHERE reason_type = 'ADJUSTMENT' AND org_id = :p_org_id AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE)) AND NVL(end_date_active, TRUNC(SYSDATE)) AND NVL(partner_access_flag,'N') = 'N' ORDER BY name;

Look up a specific code's label for a report:

  • SELECT name, description FROM apps.ozf_reason_codes_vl WHERE reason_code = :p_code AND org_id = :p_org_id;

Because the view already restricts to the session language, no additional join to OZF_REASON_CODES_ALL_TL is required. For cross-language reporting (all translations), developers must query the base tables directly rather than this VL view.