Results for “cancellation_reason_code”

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

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:

• EVENT_REGISTRATION_ID — unique identifier for the registration record.
• EVENT_OFFER_ID and EVENT_OFFER_NAME — the event offer being registered for.
• ORDER_HEADER_ID, TRANSACTIONAL_CURR_CODE, PAYMENT_TYPE_CODE, PAYMENT_AMOUNT — order and payment context for the registration.
• DATE_REGISTRATION_PLACED — date the registration was submitted; central to trend and history analysis.
• CUSTOMER_ID and PARTY_NAME — the registrant party (customer) information.
• MAIN_CONTACT_ID, MAIN_CONTACT_FULL_NAME, ENROLLEE_FULL_NAME — contact-level identification of the person associated with the registration.
• REL_PARTY_ID and REL_NAME — the related party resolved through HZ_RELATIONSHIPS. This is directly relevant to the "rel_name" search term: REL_NAME returns the party name of the relationship counterparty, allowing reports to display who the registrant is associated with (for example, an employee of a company, or a secondary contact).
• ENROLLEE_PHONE_NUMBER — a formatted concatenation of country code, area code, phone number, and extension.
• CONFIRMATION_CODE — registration confirmation reference.
• SYSTEM_STATUS_CODE and PAYMENT_STATUS — decoded meanings for the registration's system status and payment status.
• PRICE_LIST_NAME — the name of the price list applied to the associated order.
• CANCELLATION_REASON_CODE and OBJECT_VERSION_NUMBER — cancellation tracking and concurrency control.

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.