Results for “ast_event_enrollees_v”

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

Overview

The AST_EVENT_ENROLLEES_V view is an Oracle Application Object Library (APPS) schema database view owned by the APPS schema and exposed through the AST (TeleSales) product family. It presents a consolidated, denormalized list of event enrollees for event offers managed by Oracle Marketing and TeleSales. Its primary purpose is to provide a single queryable source of enrollee registration data together with the associated customer, contact, telephone, email, and status information, removing the need for report developers and integrators to join the underlying Marketing and Trading Community Architecture (TCA) tables directly.

The view draws its data predominantly from the AMS_EVENT_REGISTRATIONS table, which stores event registration records, and from the HZ_PARTIES table, which stores party and contact information. Because it joins across these modules, the view is typically consumed in TeleSales reporting, marketing event administration, confirmation mailings, and enrolment rosters. The EMAIL_ADDRESS column (aliased from HP4.EMAIL_ADDRESS) is a common point of interest, since it supports email confirmations and contactability checks for enrollees.

Because the object is a view and not a base table, it does not store data itself. Filtering, sorting, and joins performed against it are resolved at runtime by the underlying SQL statement, which combines two arms with a UNION ALL.

Underlying Base Objects

The documented dependent objects for AST_EVENT_ENROLLEES_V are:

• AMS_EVENT_REGISTRATIONS (synonym) — the primary driver of the view, containing the event registration rows.
• AMS_LOOKUPS (view) — supplies decoded meanings for the system status code and registration source type code.
• HZ_CONTACT_POINTS (synonym) — provides phone contact point data for the enrollee.
• HZ_ORG_CONTACTS (synonym) — links organizations to their contacts.
• HZ_PARTIES (synonym) — supplies party, customer, contact, and enrollee name and email data.
• HZ_RELATIONSHIPS (synonym) — supports the contact and registrant relationship resolution.

In the view text, the registrant, main contact, and enrollee parties are resolved from the HZ_PARTIES table aliased as HP1, HP2, and HP4 respectively. Phone data is joined from HZ_CONTACT_POINTS with outer-join conditions and a primary, active, PHONE-type filter. The status and source lookups are outer-joined to AMS_LOOKUPS so that registration rows remain visible even where lookup meanings are missing.

Key Columns

• EVENT_REGISTRATION_ID / EVENT_OFFER_ID — identifiers for the registration and the associated event offer.
• CUSTOMER_ID, CUSTOMER_NAME, PARTY_TYPE — the registered customer party (restricted to PERSON party type in the first arm).
• MAIN_CONTACT_FULL_NAME — name of the main contact for the registration.
• ENROLLEE_FULL_NAME, ENROLLEE_NUMBER — name and party number of the enrollee/attendant.
• ATTENDANT_ACCOUNT_ID — account associated with the enrollee.
• EMAIL_ADDRESS — email of the enrollee (HP4.EMAIL_ADDRESS), used for confirmations.
• ENROLLEE_PHONE_NUMBER — formatted phone with country code, area code, extension.
• SYSTEM_STATUS_CODE / REG_SOURCE_TYPE_CODE — decoded lookup meanings for status and registration source.
• CONFIRMATION_CODE — code used to confirm the registration.
• ATTENDED_FLAG — indicates whether the enrollee attended.
• TITLE — person title of the registrant.

Common Use Cases and Queries

Typical uses include generating event rosters, sending confirmation emails to enrollees, and auditing cancelled or waitlisted registrations. The following sample queries illustrate typical access patterns.

• List confirmed enrollees with email addresses for an event offer:
  SELECT enrollee_full_name, email_address FROM ast_event_enrollees_v WHERE event_offer_id = :p_offer_id AND system_status_code = 'REGISTERED';
• Produce a phone contact roster:
  SELECT enrollee_full_name, enrollee_phone_number FROM ast_event_enrollees_v WHERE event_offer_id = :p_offer_id ORDER BY enrollee_full_name;
• Audit registration sources and attendance:
  SELECT reg_source_type_code, attended_flag, COUNT(*) FROM ast_event_enrollees_v GROUP BY reg_source_type_code, attended_flag;

Because the view resolves across TCA and Marketing, performance-sensitive queries should filter on EVENT_OFFER_ID or EVENT_REGISTRATION_ID where possible.