Results for “ams_p_event_offers_v”

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

Overview

The AMS_P_EVENT_OFFERS_V view is a public, VALID database object owned by the APPS schema within the Oracle E-Business Suite Marketing (AMS) module. Its documented purpose is to return the general information about marketing event offerings, making it a denormalized read-only interface over the Event Management transactional model. Because it is a public view exposed to the APPS schema, it is the supported integration surface for reporting, custom concurrent programs, Oracle Discoverer workbooks, OBIEE / BI Publisher extracts, and third-party integration layers requiring a flattened projection of event offer header data. The view is available in both Oracle EBS 12.1.1 and 12.2.2, and it retains the same object type (VIEW) and status across releases, with the 12.2.2 ETRM metadata confirming APPS ownership and a stable referenced-object list.

Underlying Base Objects

Per the 12.2.2 ETRM metadata, the view is defined over the following referenced base objects:

• AMS_EVENT_OFFERS_ONLY_V (VIEW) — supplies the core event-offer attribute columns (EVENT_OFFER_ID, EVENT_OFFER_NAME, STATUS fields, scheduling, registration controls, and so on).
• AMS_EVENT_REGISTRATIONS (SYNONYM) — queried via correlated scalar subqueries to derive live registration counts and capacity metrics.
• AMS_JTF_RS_EMP_V (VIEW) — joined to resolve coordinator identity (JTFR.FULL_NAME) from the coordinator ID.
• MTL_SYSTEM_ITEMS_KFV (SYNONYM) — joined (as ITEM) to resolve INVENTORY_ITEM_ID into a human-readable CONCATENATED_SEGMENTS value.

The view text also references AMS_EVENT_REGISTRATIONS with the SYSTEM_STATUS_CODE values ENROLLED, WAITLISTED, and REGISTERED to compute enrollment, waitlist, remaining-capacity, and registered-count values inline.

Key Columns

• EVENT_OFFER_ID / EVENT_OFFER_NAME / DESCRIPTION — the primary identifier and descriptive attributes of the marketing event offering.
• SOURCE_CODE, EVENT_TYPE_CODE, EVENT_LEVEL, USER_STATUS, SYSTEM_STATUS_CODE — classification and lifecycle status columns used for filtering and segmentation.
• INVENTORY_ITEM_ID and ITEM.CONCATENATED_SEGMENTS — inventory item linkage and its concatenated flexfield display value.
• EVENT_LOCATION_ID, TIMEZONE_NAME, CITY, STATE, COUNTRY — venue and locale attributes.
• EVENT_DELIVERY_METHOD_ID / EVENT_DELIVERY_METHOD_CODE, EVENT_LANGUAGE_CODE / EVENT_LANGUAGE, URL — delivery, localization, and web reference information.
• COORDINATOR_ID and JTFR.FULL_NAME — the responsible coordinator and resolved name.
• PRIORITY_CODE, CERTIFICATION_CREDIT_TYPE, CERTIFICATION_CREDITS — priority and certification tracking attributes.
• EVENT_DURATION / EVENT_DURATION_UOM_CODE, EVENT_START_DATE(_TIME), EVENT_END_DATE(_TIME), REG_START_DATE(_TIME), REG_END_DATE(_TIME) — scheduling and registration window columns.
• REG_EFFECTIVE_CAPACITY, REG_MINIMUM_CAPACITY, REG_MINIMUM_REQ_BY_DATE — capacity thresholds for registration control.
• Flag columns — EVENT_STANDALONE_FLAG, REG_FROZEN_FLAG, REG_REQUIRED_FLAG, REG_CHARGE_FLAG, REG_INVITED_ONLY_FLAG, REG_WAITLIST_ALLOWED_FLAG, REG_OVERBOOK_ALLOWED_FLAG.
• Derived registration counts — COUNT of AMS_EVENT_REGISTRATIONS by SYSTEM_STATUS_CODE (ENROLLED, WAITLISTED, REGISTERED), plus remaining capacity calculated as REG_EFFECTIVE_CAPACITY minus registered count.

Common Use Cases and Queries

The view supports capacity monitoring, coordinator workload reporting, event calendars, and integration feeds. Because registration counts are computed inside the view, consumers can report fill rates without writing their own aggregation logic.

• Event catalogue listing:

  SELECT event_offer_id, event_offer_name, event_type_code,
         event_start_date, city, country
  FROM   apps.ams_p_event_offers_v
  WHERE  system_status_code = 'ACTIVE';

• Capacity and fill-rate report:

  SELECT event_offer_id, event_offer_name,
         reg_effective_capacity, registered_count, waitlisted_count
  FROM   apps.ams_p_event_offers_v
  WHERE  reg_effective_capacity IS NOT NULL
  ORDER BY event_start_date;

• Coordinator workload:

  SELECT full_name, COUNT(*) events
  FROM   apps.ams_p_event_offers_v
  GROUP BY full_name;

• Inventory-linked events:

  SELECT event_offer_name, concatenated_segments
  FROM   apps.ams_p_event_offers_v
  WHERE  inventory_item_id IS NOT NULL;

Where performance is critical, restrict results by date or status predicates to limit the correlated subqueries against AMS_EVENT_REGISTRATIONS.