Search Results ast_ev_his_org_v




Overview
Explore in AI Chat

Overview

The AST_EV_HIS_ORG_V view is a BI Publisher / OBIEE reporting object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides within the AST — TeleSales product family and provides a consolidated, denormalized presentation of event registration history for a given operating unit. Its principal function is to join registration records held in the AMS (Marketing) tables with customer, contact, order, pricing, and lookup information so that event enrollment activity can be reported without the report author needing to reconstruct the underlying multi-table joins. The view is a UNION ALL construct, meaning that the data set it exposes is the union of two related SELECT statements, and it is intended to be consumed directly by concurrent programs, Oracle Business Intelligence Publisher (BIP) reports, and ad hoc SQL in the TeleSales Event Management area.

Underlying Base Objects

The documented view metadata lists the following referenced base objects:

  • AMS_EVENT_REGISTRATIONS (SYNONYM) — the primary registration fact table; the driving table for registration IDs, offer IDs, order header IDs, contact IDs, cancellation reason, and confirmation code.
  • AMS_EVENT_OFFERS_VL (VIEW) — supplies the event offer name and inventory item ID via the EVENT_OFFER_ID.
  • AMS_LOOKUPS (VIEW) — resolves lookup codes for system status (AMS_EVENT_REG_STATUS) and payment status (AMS_EVENT_PAYMENT_STATUS) into meaningful descriptions.
  • HZ_PARTIES (SYNONYM) — joined multiple times to obtain the customer name, main contact full name, and related party name (REL_NAME).
  • HZ_RELATIONSHIPS (SYNONYM) — the relationship backbone in TCA, used to identify the registrant contact relative to the registrant party.
  • HZ_CONTACT_POINTS (SYNONYM) — outer-joined to retrieve the enrollee's primary phone number, with country code, area code, and extension concatenated into a single column.
  • OE_ORDER_HEADERS (SYNONYM) — supplies the transactional currency code, payment type, and payment amount from the associated order.
  • QP_LIST_HEADERS_TL (SYNONYM) — resolves the price list name from the order's price list ID.

The view is defined in the APPS schema and all base objects are referenced without schema qualification, relying on APPS synonyms for resolution. Outer joins are used for several of the optional relationships (HZ_CONTACT_POINTS, AMS_LOOKUPS, OE_ORDER_HEADERS, QP_LIST_HEADERS_TL), reflecting the fact that a registration may exist without a completed order, phone, or price list.

Key Columns

The view exposes a wide set of analytical columns. The most significant include:

Common Use Cases and Queries

Typical uses include event enrollment history reports, cancellation analysis, payment reconciliation against event orders, and relationship-level reporting where the related party name is required. A representative query follows:

SELECT event_offer_name,
       customer_id,
       party_name,
       rel_party_id,
       rel_name,
       date_registration_placed,
       payment_status,
       system_status_code
FROM   apps.ast_ev_his_org_v
WHERE  date_registration_placed >= :p_from_date
AND    date_registration_placed <  :p_to_date
ORDER BY date_registration_placed DESC;

Because the view is a UNION ALL, duplicate elimination is not performed; report authors should aggregate or filter appropriately. The outer-joined columns (PRICE_LIST_NAME, PAYMENT_AMOUNT, ENROLLEE_PHONE_NUMBER) may return NULL when the corresponding order or contact point is absent, and queries should account for this when applying filters. Access is granted through the APPS schema, so custom reports should reference APPS.AST_EV_HIS_ORG_V and be registered with the appropriate responsibility and security profile to enforce operating-unit level data access.