Results for “ams_event_type”

50+ results




Overview
Explore in AI Chat

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

Overview

APPS.BIM_DIMV_CHANNELS is a dimensional conformance view in Oracle E-Business Suite Marketing (AMS/BIM) that presents a unified list of marketing channels, event headers, and event offers as a single logical dimension. It is defined with a UNION ALL across three virtual channel sources — AMS_CHANNELS_VL, AMS_EVENT_HEADERS_VL, and AMS_EVENT_OFFERS_VL — and a final sentinel row from FND_LOOKUPS where LOOKUP_TYPE = 'BIM_VALUE_TYPE' and LOOKUP_CODE = '-999'. The view is declared WITH READ ONLY, confirming its role as a reporting and integration artifact rather than a transactional entity. It is typically consumed by Business Intelligence (BIM) warehouse ETL processes and by concurrent programs that populate fact tables requiring a channel dimension key.

Underlying Base Objects

The view is built on the following documented base objects:

  • AMS_CHANNELS_VL — the marketing channel master view; each row produces an ID prefixed with CHLS.
  • AMS_EVENT_HEADERS_VL — event headers at EVENT_LEVEL = 'MAIN'; each row produces an ID prefixed with EVEH and joins AMS_LOOKUPS on AMS_EVENT_TYPE to derive the channel type meaning.
  • AMS_EVENT_OFFERS_VL — event offers at EVENT_LEVEL = 'MAIN'; each row produces an ID prefixed with EVEO, also joined to AMS_LOOKUPS.
  • AMS_LOOKUPS — supplies MEANING for CHANNEL_TYPE via LOOKUP_TYPE = 'AMS_EVENT_TYPE'.
  • FND_LOOKUPS — contributes the reserved -999 "unknown/not applicable" row.
  • FND_GLOBAL — referenced in the standard environment setup (org/user context) available to the marketing views.

The UNION ALL preserves duplicates and row order, and the prefixing scheme (CHLS/EVEH/EVEO) guarantees synthetic uniqueness across the heterogeneous sources.

Key Columns

  • ID — concatenated surrogate key, e.g. 'CHLS' || CHANNEL_ID; the primary addressing column for the dimension.
  • VALUE — SUBSTR of the channel/event/offer name truncated to 80 characters; used as the display label.
  • CHANNEL_ID — the prefixed identifier retained in a separate column for joins and lookups.
  • CHANNEL_TYPE_CODE / CHANNEL_TYPE — the code and its decoded meaning (from AMS_LOOKUPS for the event sources).
  • INBOUND_FLAG — populated only for rows sourced from AMS_CHANNELS_VL; indicates whether the channel supports inbound activity.
  • OUTBOUND_FLAG — the column that prompted this query; also populated only for AMS_CHANNELS_VL rows, flagging channels that support outbound marketing activity. Event header and event offer rows return NULL for this column, since those constructs do not carry inbound/outbound semantics.
  • ACTIVE_FROM_DATE / ACTIVE_TO_DATE — date-bounded validity of the channel, mapped from ACTIVE_FROM_DATE/ACTIVE_TO_DATE on channels and events, and from EVENT_START_DATE/EVENT_END_DATE on offers.
  • CHANNEL_NAME / DESCRIPTION — descriptive attributes.

Common Use Cases and Queries

The view is used to resolve a channel dimension key from a source identifier, to filter channels by direction, and to join channel attributes into BI reports. A typical directionality query is:

  • SELECT id, value, outbound_flag FROM apps.bim_dimv_channels WHERE outbound_flag = 'Y';
  • SELECT id, value, channel_type FROM apps.bim_dimv_channels WHERE channel_type_code = 'EMAIL';
  • SELECT id, value FROM apps.bim_dimv_channels WHERE active_to_date IS NULL OR active_to_date >= SYSDATE;

Note the sentinel row (-999) is often used as a placeholder dimension member for fact rows whose channel could not be resolved. Because the view is read-only and union-based, it should not be updated directly; changes flow from the underlying AMS_* tables.