Search Results on_reg

Overview

APPS.AST_EVENT_DELIVERABLES_V is an Oracle E-Business Suite 12.1.1 / 12.2.2 reporting view that resolves the deliverables associated with event offers (marketing events and one-time events) in the Oracle Marketing / Advanced Marketing (AMS) module. Its specific purpose is to expose the set of deliverables that are scheduled to be fulfilled at enrollment or registration time, which is signalled by the FULFILL_ON_TYPE_CODE IN ('ON_ENROLL','ON_REG') predicate in the view definition. This is the object that satisfies the "on_enroll" search: it tells a caller which items a customer is entitled to receive at the moment they enroll in or register for an event offer.

The view is a UNION of two branches. The first branch returns inventory-tracked deliverables and joins to MTL_SYSTEM_ITEMS_B; the second branch returns electronically fulfillable deliverables and does not join to inventory. Both branches share the same column list, so downstream reports, concurrent programs, and integrations can consume a single, uniform result set without branching logic of their own.

Underlying Base Objects

Per the documented ETRM metadata for 12.2.2, the view is owned by APPS and is defined over the following base objects:

• AMS_DELIVERABLES_VL (view) — the primary deliverable master, supplying DELIVERABLE_ID, JTF_AMV_ITEM_ID, DELIVERABLE_NAME, DESCRIPTION, availability dates, and the electronic/physical fulfillment flags.
• AMS_EVENT_OFFERS_ALL_B (synonym) — the event offer entity, supplying EVENT_OFFER_ID.
• AMS_OBJECT_ASSOCIATIONS (synonym) — the association table that links a master event object to its using deliverable and carries the FULFILL_ON_TYPE_CODE driving the ON_ENROLL / ON_REG filter.
• MTL_SYSTEM_ITEMS_B (synonym) — the inventory item master, supplying ORGANIZATION_ID and the customer-orderability flags.

The join path is: AMS_OBJECT_ASSOCIATIONS.MASTER_OBJECT_ID = AMS_EVENT_OFFERS_ALL_B.EVENT_OFFER_ID (with MASTER_OBJECT_TYPE IN ('EVEO','EONE')), AMS_OBJECT_ASSOCIATIONS.USING_OBJECT_ID = AMS_DELIVERABLES_VL.DELIVERABLE_ID (with USING_OBJECT_TYPE = 'DELV'), and in the inventory branch MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID = AMS_DELIVERABLES_VL.INVENTORY_ITEM_ID.

Key Columns

• DELIVERABLE_ID — primary key of the deliverable in AMS_DELIVERABLES_VL.
• JTF_AMV_ITEM_ID — reference to the associated media/content item.
• EVENT_OFFER_ID — the parent event offer to which the deliverable is attached.
• INVENTORY_ITEM_ID / INVENTORY_ITEM_ORG_ID — the shippable item and its organization; INVENTORY_ITEM_ID is NULL in the electronic branch.
• DELIVERABLE_NAME / DESCRIPTION — descriptive text for the deliverable.
• CONCATENATED_SEGMENTS, NON_INV_QUANTITY_ON_HAND, PRIMARY_UOM_CODE, PRIMARY_UNIT_OF_MEASURE, NON_INV_QUANTITY_ON_ORDER — reserved placeholders (NULL by definition). They preserve a consistent shape with related inventory views.
• ACTUAL_AVAIL_FROM_DATE / ACTUAL_AVAIL_TO_DATE — availability window; the query requires TRUNC(SYSDATE) to fall within these, using SYSDATE as a default when they are NULL.
• CAN_FULFILL_ELECTRONIC_FLAG / CAN_FULFILL_PHYSICAL_FLAG — fulfillment mode indicators; the physical branch filters on 'Y'.

Common Use Cases and Queries

Typical uses include building enrollment/registration entitlement reports, seeding order or fulfillment lines at the point of enrollment, and auditing which deliverables are eligible for electronic distribution versus physical shipment.

• List all enrollment-time deliverables for an event offer:

  SELECT event_offer_id, deliverable_id, deliverable_name,
         can_fulfill_physical_flag, can_fulfill_electronic_flag
  FROM   apps.ast_event_deliverables_v
  WHERE  event_offer_id = :p_event_offer_id;

• Identify physically shippable items with their inventory organization:

  SELECT event_offer_id, deliverable_name, inventory_item_id, inventory_item_org_id
  FROM   apps.ast_event_deliverables_v
  WHERE  can_fulfill_physical_flag = 'Y'
  AND    inventory_item_id IS NOT NULL;

• Isolate electronic deliverables (which carry no inventory organization):

  SELECT event_offer_id, deliverable_name, jtf_amv_item_id
  FROM   apps.ast_event_deliverables_v
  WHERE  can_fulfill_electronic_flag = 'Y';

Because the availability-date filter is embedded in the view, results are already restricted to deliverables currently available as of the run date; callers do not need to repeat that predicate.