Results for “pv_ge_benefits_vl”

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

Overview

PV_GE_BENEFITS_VL is a seeded, read-only view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PV (Partner Management) product family. It is a "VL" (translated/MLS) view, meaning it presents the language-independent business data of a benefit record joined to the language-specific descriptive text for the session language. The view resolves a partner program benefit entity to a single flattened row per benefit, exposing both the operational attributes stored on the base table and the user-facing text stored in the translation table.

Its principal role is to supply benefit name and description text alongside the benefit's identity, type, status, and code, so that concurrent programs, OAF pages, forms, and outbound integrations do not have to perform the multi-table lookup themselves. For a report or interface that must display a benefit label rather than a numeric ID, PV_GE_BENEFITS_VL is the standard access path. Because it filters on the runtime language, applications should query the view rather than the underlying translation table directly to guarantee they return the correct localized text and avoid duplicate rows across languages.

Underlying Base Objects

The ETRM documentation records two referenced base objects for this view, both surfaced in the APPS schema as synonyms: PV_GE_BENEFITS_B and PV_GE_BENEFITS_TL.

• PV_GE_BENEFITS_B — the base (language-independent) table holding the core benefit definition, including its primary key, type and status codes, descriptive flexfield attribute columns, and standard WHO audit columns.
• PV_GE_BENEFITS_TL — the translation table carrying language-dependent BENEFIT_NAME and DESCRIPTION text, keyed by BENEFIT_ID and LANGUAGE.

The view joins the two on BENEFIT_ID and restricts the translation row with T.LANGUAGE = USERENV('LANG'), which resolves to the language of the current session. This is the mechanism that makes the view return exactly one row per benefit in the caller's language rather than one row per translated language. The view text also selects B.ROWID AS ROW_ID from the base table, preserving row identification semantics for downstream consumers.

Key Columns

• ROW_ID — the B.ROWID value of the base-table row; useful for row addressing and debugging.
• BENEFIT_ID — the primary/foreign key joining the base and translation tables; the unique identifier of the benefit.
• BENEFIT_NAME — the localized display name of the benefit, sourced from PV_GE_BENEFITS_TL. This is the column most often targeted by users searching for benefit-oriented views.
• DESCRIPTION — the localized long description of the benefit, also from the translation table.
• BENEFIT_CODE — the user-defined or system-defined short code that uniquely names the benefit for interface and integration purposes.
• BENEFIT_TYPE_CODE and BENEFIT_STATUS_CODE — lookup-backed classifications defining the kind of benefit and its lifecycle state (for example active versus inactive).
• OBJECT_VERSION_NUMBER — the optimistic locking token used by the OAF/BC4J layer.
• DELETE_FLAG — indicates a logically deleted benefit record.
• ADDITIONAL_INFO_1 / ADDITIONAL_INFO_2 — extension columns for supplementary benefit data.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield segments, available for customer-specific extensions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns inherited from the base table.

Common Use Cases and Queries

Typical scenarios include listing all active benefits for a partner program, driving LOVs and reports that need a translated name, and extracting benefit metadata for outbound interfaces. The following query returns the localized benefit catalogue, filtering out logically deleted rows and ordering by the translated name.

SELECT benefit_id,
       benefit_code,
       benefit_name,
       description,
       benefit_type_code,
       benefit_status_code
FROM   apps.pv_ge_benefits_vl
WHERE  NVL(delete_flag,'N') = 'N'
ORDER  BY benefit_name;

To resolve a single benefit by its code or ID — the common pattern when a downstream object stores only the identifier — the view supplies the human-readable text in one lookup:

SELECT benefit_name, description
FROM   apps.pv_ge_benefits_vl
WHERE  benefit_code = :p_benefit_code;

Because the view already applies the session-language filter, callers must not additionally join to PV_GE_BENEFITS_TL, as doing so risks re-introducing duplicate language rows. Where a query is executed by a concurrent program, the language is normally derived from the runtime environment established for the request, so the returned BENEFIT_NAME matches the requesting user's language. For integrations that require all translations regardless of session language, the translation table PV_GE_BENEFITS_TL should be queried directly instead of this view.