Results for “dlv_category”

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

Overview

AMS_ACT_DELIVERABLES_V is an Oracle EBS Marketing (AMS) reporting view owned by the APPS schema and marked VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM metadata, the view returns all marketing deliverables, including collaterals. It exposes the intersection between a deliverable (the "using" object) and any object that consumes or references it, by joining operational association rows in AMS_OBJECT_ASSOCIATIONS to the deliverable master data in AMS_P_DELIVERABLES_V. Four outer-joined lookup resolutions enrich the raw codes with human-readable meanings.

The view is not a transactional entry point; it is a reporting and integration artifact. It provides a denormalized, description-bearing projection of deliverable-to-object associations that BI Publisher reports, custom concurrent programs, and outbound interfaces can consume without re-implementing the AMS lookup joins. Because it filters only on OBJ.USING_OBJECT_TYPE = 'DELV', every row represents a distinct association record in which the deliverable is the object being used.

Underlying Base Objects

The documented base objects are AMS_OBJECT_ASSOCIATIONS (exposed as a SYNONYM), AMS_P_DELIVERABLES_V (a VIEW), and AMS_LOOKUPS (a VIEW) joined four times under the aliases LKUP1 through LKUP4. AMS_OBJECT_ASSOCIATIONS is the driving object; it stores the association identifier, usage type, master object identity, using object identity, quantity requirements, fulfillment attributes, and the object version number. AMS_P_DELIVERABLES_V, which represents the live deliverable records, supplies the deliverable name and its category and sub-category identifiers and descriptions.

The four lookups are joined with Oracle outer-join syntax (LOOKUP_TYPE(+) on AMS_LOOKUPS and LOOKUP_CODE(+) = OBJ.<column> on the base view), so an association row persists even when a lookup code is missing. They resolve AMS_USAGE_TYPE, AMS_MASTER_OBJECT_TYPE, AMS_USING_OBJECT_TYPE, and AMS_EVENT_FULFILL_ON. The inner join between the association table and AMS_P_DELIVERABLES_V is enforced by the USING_OBJECT_ID = DELIVERABLE_ID predicate, meaning only associations pointing to live deliverables survive.

Key Columns

• OBJECT_ASSOCIATION_ID — primary identifier of the association row; the finest grain returned by the view.
• USAGE_TYPE / USAGE_TYPE_MEANING — the coded usage classification and its decoded meaning from AMS_OBJECT_USAGE_TYPE.
• MASTER_OBJECT_ID / MASTER_OBJECT_TYPE / MASTER_OBJECT_TYPE_MEANING — the parent or owning object to which the deliverable association is attached.
• DELIVERABLE_ID / DELIVERABLE_NAME — the using object identity and the deliverable name sourced from AMS_P_DELIVERABLES_V.
• USING_OBJECT_TYPE / USING_OBJECT_TYPE_MEANING — decoded from AMS_USING_OBJECT_TYPE; effectively 'DELV' for every returned row.
• DLV_CATEGORY / DLV_SUB_CATEGORY and their _TYPE_ID counterparts — classification of the deliverable, useful for grouping collaterals versus other deliverable classes.
• QUANTITY_NEEDED and QUANTITY_NEEDED_BY_DATE — the requested quantity and the date by which it is required; these are the fields most frequently interrogated in demand, scheduling, and fulfillment reporting. The date column is the target of the widely used search term "quantity_needed_by_date."
• FULFILL_ON_TYPE_CODE / FULFILL_ON_TYPE — decoded from AMS_EVENT_FULFILL_ON, indicating the event that triggers fulfillment.
• PRIMARY_FLAG, OBJECT_VERSION_NUMBER — designation of the primary association and the optimistic-locking version stamp.

Common Use Cases and Queries

Typical uses include deliverable demand reports, fulfillment scheduling by required date, and integration extracts that need decoded lookup values. The "quantity_needed_by_date" search reflects the demand-timing report pattern below, which lists deliverables ordered by the date they are required.

SELECT deliverable_name, dlv_category, quantity_needed, quantity_needed_by_date, fulfill_on_type
FROM apps.ams_act_deliverables_v
WHERE quantity_needed_by_date IS NOT NULL
ORDER BY quantity_needed_by_date, deliverable_name;

To isolate a single deliverable's associations, filter on DELIVERABLE_ID or DELIVERABLE_NAME. To group demand by category for a date window, add a predicate such as quantity_needed_by_date BETWEEN :p_start AND :p_end and aggregate on DLV_CATEGORY. To locate the owning master object, select MASTER_OBJECT_ID, MASTER_OBJECT_TYPE_MEANING, and OBJECT_ASSOCIATION_ID. Because the view is read-only and fully decoded, it is safe to use directly in BI Publisher data models and custom PL/SQL, with the standard caution that quantity and date columns may be null where fulfillment is not yet planned.