Search Results ams_deliverables_vl




Overview
Explore in AI Chat

Overview

AMS_DELIVERABLES_VL is a seeded, VALID database view owned by the APPS schema within the Oracle E-Business Suite Marketing (AMS) module. Its documented purpose is to return marketing deliverable information, making it the principal read-only interface through which deliverable definitions are surfaced for reporting, integration, and concurrent processing. The "_VL" suffix denotes a "view with language" pattern: the view joins the base entity table to its translation table and restricts the translation row to the session's current language through USERENV('LANG'). As a result, deliverable name and description values are automatically returned in the language of the querying session without the caller having to write join or NLS logic. The view is available in both Oracle EBS 12.1.1 and 12.2.2, and its definition is consistent across those releases, as reflected in the ETRM documented metadata.

Underlying Base Objects

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

In the documented view text, the translation table is aliased T and the base table B. The two are joined on B.DELIVERABLE_ID = T.DELIVERABLE_ID with the filter T.LANGUAGE = USERENV('LANG'), and all base columns are projected from B while only DELIVERABLE_NAME and DESCRIPTION are taken from T. The view therefore presents exactly one row per deliverable per queried language.

Key Columns

The projection includes the full set of base attributes plus the two translated fields. Significant columns include:

Common Use Cases and Queries

Because the view encapsulates the base-to-translation join, it is the preferred source for any query that needs deliverable names in the user's language. Typical uses include LOV-style lookups in custom extensions, marketing pipeline and calendar reporting, inventory and fulfillment reconciliation, and extract routines feeding downstream analytics.

A minimal lookup that returns active, translated deliverables for the current operating unit:

  • SELECT deliverable_id, deliverable_name, description, status_code, active_flag FROM apps.ams_deliverables_vl WHERE org_id = :p_org_id AND active_flag = 'Y' ORDER BY deliverable_name;

For scheduling reporting, the forecasted and actual completion dates, together with CATEGORY_TYPE_ID, support on-time completion analysis:

  • SELECT deliverable_id, deliverable_name, forecasted_complete_date, actual_complete_date, budget_amount_tc, currency_code FROM apps.ams_deliverables_vl WHERE org_id = :p_org_id AND actual_complete_date IS NULL;

Callers should always constrain by ORG_ID in a multi-org environment and be aware that, because the view filters the translation table by session language, a deliverable with no row in the current language will not be returned. Where all translations are required regardless of session language, the base and translation tables must be joined directly.