Results for “ams_event_registrations_pk”

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

Overview

AMS_EVENT_REGISTRATIONS is the core transactional table within the Oracle Marketing (AMS) module that stores enrollment information for marketing events. It records who registered for an event, who will actually attend, and the lifecycle status of each registration. In Oracle EBS 12.1.1 and 12.2.2, this table sits at the intersection of marketing campaign execution, trade show and seminar management, and the order capture lifecycle, since registrations frequently generate sales orders.

Each row represents a single registration record, linked to a registrant party (typically from the Trading Community Architecture, or TCA) and optionally to an attendant party, account, and contact. The table carries both the "who" (HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_ORG_CONTACTS references) and the "what happened" (status codes, attendance flags, cancellation reasons, and order references).

From a heuristic Data Vault modeling perspective, the metadata classifies this object as satellite-leaning. This reflects its role as a descriptive, attribute-heavy record tied to a natural business key — here, the registration identity and its related party. A Data Vault design would typically place EVENT_REGISTRATION_ID as a hub or link key, with the numerous descriptive and status columns treated as satellite attributes that change over the registration lifecycle.

Key Information Stored

The table contains 75 documented columns. The most significant include:

• EVENT_REGISTRATION_ID — the surrogate primary key (AMS_EVENT_REGISTRATIONS_PK), also enforced by unique index AMS_EVENT_REGISTRATIONS_U1. This is the business-key candidate for the registration record.
• EVENT_OFFER_ID — foreign key to AMS_EVENT_OFFERS_ALL_B, identifying the specific event offering that was enrolled.
• REGISTRANT_PARTY_ID — the TCA party who registered (HZ_PARTIES).
• REGISTRANT_ACCOUNT_ID and REGISTRANT_CONTACT_ID — the associated account and org contact for the registrant.
• ATTENDANT_PARTY_ID, ATTENDANT_ACCOUNT_ID, ATTENDANT_CONTACT_ID — the actual attendee, which may differ from the registrant.
• USER_STATUS_ID — foreign key to AMS_USER_STATUSES_B, the user-defined registration status.
• SYSTEM_STATUS_CODE — the system-derived status, complementing the user status.
• ATTENDED_FLAG, CONFIRMED_FLAG, PROSPECT_FLAG, EVALUATED_FLAG — lifecycle indicator flags.
• DATE_REGISTRATION_PLACED and LAST_REG_STATUS_DATE — key dates for registration and last status change.
• CANCELLATION_CODE and CANCELLATION_REASON_CODE — where the registration was cancelled.
• ORDER_HEADER_ID and ORDER_LINE_ID — links to OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL when the registration generated a sales order.
• TARGET_LIST_ID — links the registration to the source list in AMS_LIST_HEADERS_ALL.

Descriptive and audit columns include ATTRIBUTE1 through ATTRIBUTE15 (DDF-style flex fields), PROGRAM_ID, REQUEST_ID, CREATION_DATE, and LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical reporting and integration scenarios include attendance tracking, status distribution, and order linkage.

• Listing all active registrations for an event offer, joined to status and party:

  SELECT r.event_registration_id, p.party_name,
         r.date_registration_placed, r.attended_flag
  FROM   ams.ams_event_registrations r,
         hz.hz_parties p
  WHERE  r.registrant_party_id = p.party_id
  AND    r.event_offer_id = :offer_id
  AND    r.active_flag = 'Y';

• Reporting registrations that converted to sales orders, joining to OE_ORDER_HEADERS_ALL on ORDER_HEADER_ID.
• Analyzing cancellation reasons using CANCELLATION_CODE and CANCELLATION_REASON_CODE grouped by EVENT_OFFER_ID.
• Tracking waitlisted attendees via WAITLISTED_PRIORITY ordered ascending.
• Auditing inbound source attribution using INBOUND_MEDIA_ID and INBOUND_CHANNEL_ID against AMS_MEDIA_B and AMS_CHANNELS_B.

Related Objects

The following tables and objects are most significant to this registration record:

• AMS_EVENT_OFFERS_ALL_B — via EVENT_OFFER_ID; defines the event offering enrolled.
• HZ_PARTIES — via REGISTRANT_PARTY_ID and ATTENDANT_PARTY_ID; TCA party master.
• HZ_CUST_ACCOUNTS — via REGISTRANT_ACCOUNT_ID and ATTENDANT_ACCOUNT_ID.
• HZ_ORG_CONTACTS — via REGISTRANT_CONTACT_ID, ATTENDANT_CONTACT_ID, and ORIGINAL_REGISTRANT_CONTACT_ID.
• AMS_USER_STATUSES_B — via USER_STATUS_ID; registration status definition.
• OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL — via ORDER_HEADER_ID and ORDER_LINE_ID.
• AMS_LIST_HEADERS_ALL — via TARGET_LIST_ID; source marketing list.
• AMS_ACT_COMMUNICATIONS — references this table through ACT_COMMUNICATION_USED_BY_ID, linking communications to the registration.
• AMS_CHANNELS_B and AMS_MEDIA_B — via INBOUND_CHANNEL_ID and INBOUND_MEDIA_ID for attribution.