Search Results deliverable_code




Overview
Explore in AI Chat

Overview

The AMS_P_DELIVERABLES_V view is a public, dictionary‑maintained database object owned by the APPS schema within the Oracle E‑Business Suite Marketing (AMS) product family. It is exposed as a standard editioning‑safe view with a status of VALID in both EBS 12.1.1 and 12.2.2. Its stated purpose is to return marketing deliverable information — meaning the physical or electronic collateral, offers, kits, and supporting assets that are planned, budgeted, and fulfilled as part of a marketing campaign or program.

Because it is a public view rather than a base table, customers and integrators are expected to consume it directly in reports, concurrent programs, and outbound interface extracts without modifying the underlying AMS table structure. The view abstracts multilingual (MLS) joins, status descriptions, category names, and owner employee names so that downstream reporting does not have to replicate the same lookup logic. In this sense it serves as a stable reporting and integration surface for the AMS deliverables entity.

Underlying Base Objects

The ETRM metadata for 12.2.2 documents four referenced objects on which the view is built:

  • AMS_DELIVERABLES_VL (VIEW) — the primary MLS‑enabled source of deliverable rows; the view's FROM clause aliases it as B.
  • AMS_CATEGORIES_TL (SYNONYM) — the translated categories table, joined twice (aliases C1 and C2) to resolve the category type name and the category sub‑type name.
  • AMS_USER_STATUSES_VL (VIEW) — supplies the user‑defined status label, aliased as ST.
  • AMS_JTF_RS_EMP_V (VIEW) — supplies the owner's full name, aliased as U.

The joins are inner joins with a language predicate of C1.LANGUAGE = USERENV('LANG') (and equivalently for C2), so only the session's current language rows are returned. Because the underlying deliverable and status objects are themselves _VL views, AMS_P_DELIVERABLES_V effectively re‑layers translated data on top of already‑translated views, producing a single flat, presentation‑ready record per deliverable.

Key Columns

The projection is broad and can be grouped into functional clusters:

Common Use Cases and Queries

Typical consumers include marketing operations reports, budget vs. actual deliverable analyses, and interface extracts feeding fulfillment or data warehouses. A straightforward usage lists active deliverables with their owner and status:

SELECT deliverable_id,
       deliverable_name,
       owner,
       user_status,
       category_name,
       status_date
  FROM apps.ams_p_deliverables_v
 WHERE active_flag = 'Y';

Costing and response reviews benefit from the financial columns:

SELECT deliverable_code,
       deliverable_name,
       currency_code,
       forecasted_cost,
       actual_cost,
       forecasted_responses,
       actual_responses
  FROM apps.ams_p_deliverables_v
 WHERE actual_complete_date IS NOT NULL;

Inventory‑aware queries can filter on the fulfillment flags, for example WHERE inventory_flag = 'Y' to review physical deliverables with on‑hand balances. Because the view already resolves language‑sensitive descriptions, joined queries should avoid re‑joining AMS_CATEGORIES_TL or AMS_USER_STATUSES_VL directly. The COUNTRY and COUNTRY_ID columns support geographic segmentation, while REPLACED_BY_DELIVERABLE_ID enables replacement‑chain analysis across deliverable versions. Wherever possible, add a bind variable on DELIVERABLE_ID or OWNER_USER_ID to limit the cost of the multi‑table join.