Results for “ams_offer_adjustments_vl”

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

Overview

AMS_OFFER_ADJUSTMENTS_VL is a multilingual (VL) view owned by the APPS schema in Oracle E-Business Suite. It belongs to the AMS – Marketing product family and is documented as returning details of offer adjustments. Offer adjustments represent modifications applied to a marketing offer, such as price reductions, promotional discounts, or other settlement-related changes captured against an offer header. The VL suffix indicates that the view joins a base table with its translation table and filters translation rows by the session language, exposing descriptive, language-dependent attributes alongside the transactional columns.

Because it consolidates both the base and translated data into a single relational interface, the view serves as the standard reporting and integration entry point for offer adjustment information. Developers and report authors query AMS_OFFER_ADJUSTMENTS_VL rather than the underlying tables directly, ensuring that translated names and descriptions are resolved according to the user's language environment. This makes it suitable for concurrent programs, Oracle Reports, Business Intelligence Publisher layouts, and inbound/outbound interface logic.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms in the APPS schema:

• OZF_OFFER_ADJUSTMENTS_B — the base table holding the primary transaction columns for each offer adjustment, including identifiers, dates, codes, flags, and the standard WHO/attribute columns.
• OZF_OFFER_ADJUSTMENTS_TL — the translation table holding language-dependent descriptive columns.

The join condition links the two tables on OFFER_ADJUSTMENT_ID, and the translation side is restricted by the predicate T.LANGUAGE = USERENV('LANG'). This constrains the result set to the language of the current session, so each OFFER_ADJUSTMENT_ID yields a single row with the appropriate translated values. Columns sourced from the _B table are prefixed as B in the view text, while DESCRIPTION and OFFER_ADJUSTMENT_NAME are drawn from the _TL table.

Key Columns

The view exposes the following significant columns:

• OFFER_ADJUSTMENT_ID — primary identifier and join key between the base and translation tables.
• EFFECTIVE_DATE and APPROVED_DATE — the date the adjustment becomes effective and the date it was approved.
• SETTLEMENT_CODE and STATUS_CODE — classification and lifecycle status of the adjustment.
• LIST_HEADER_ID — reference to the associated list header.
• VERSION and BUDGET_ADJUSTED_FLAG — version tracking and an indicator of whether the budget has been adjusted.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• OBJECT_VERSION_NUMBER — optimistic locking support.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard descriptive flexfield (DFF) columns.
• DESCRIPTION and OFFER_ADJUSTMENT_NAME — the translated, language-dependent descriptive values from OZF_OFFER_ADJUSTMENTS_TL.

Common Use Cases and Queries

Typical scenarios include listing all active offer adjustments for an offer, auditing approval and effective dates, and reporting adjustment status. A representative query is:

SELECT offer_adjustment_id,
       offer_adjustment_name,
       description,
       status_code,
       settlement_code,
       effective_date,
       approved_date
FROM   ams_offer_adjustments_vl
WHERE  status_code = 'ACTIVE'
ORDER BY offer_adjustment_name;

Because the view already filters the translation table by USERENV('LANG'), callers receive descriptions in the language of their session without adding explicit language predicates, simplifying both ad hoc reporting and packaged integrations.