Results for “ams_channels_v”

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

Overview

AMS_CHANNELS_V is a valid, seeded view in the APPS schema within the AMS (Marketing) product family of Oracle E-Business Suite. It exposes channel detail information used throughout Oracle Marketing for campaigns, campaign activities, lead generation, and multi-channel execution. The view presents a denormalized, read-only projection of marketing channel definitions combined with vendor reference data, allowing reporting, integration, and concurrent programs to retrieve channel attributes without joining the underlying normalized base objects directly. Because it is a view rather than a table, no DML should be issued against it; consumers must treat it strictly as a query interface.

The view is available in both 12.1.1 and 12.2.2, and is registered as VALID in the ETRM metadata. Its primary purpose is to return channel details — name, type, direction flags, validity dates, ownership, rating, country, and preferred vendor information — in a single row per channel, which simplifies downstream consumption.

Underlying Base Objects

Per the documented ETRM metadata, AMS_CHANNELS_V is defined over two referenced base objects:

• AMS_CHANNELS_VL (VIEW) — the multi-language (translated) base view for channels, supplying all channel-specific attributes such as CHANNEL_NAME, CHANNEL_ID, CHANNEL_TYPE_CODE, and the various flags and dates. The VL suffix indicates the underlying table set includes a _TL translation table for channel name translation by language.
• PO_VENDORS (VIEW) — the Purchasing suppliers view, supplying VENDOR_NAME.

The join between the two is an outer join on the preferred vendor identifier. The documented view text confirms: WHERE A.PREFERRED_VENDOR_ID = B.VENDOR_ID(+). This means channels that have no preferred vendor still appear in the result set, with VENDOR_NAME returning NULL. Note that the join uses PO_VENDORS, not the newer POZ_SUPPLIERS, reflecting the traditional Purchasing vendor master used at this schema level.

Key Columns

The view exposes the following documented columns:

• CHANNEL_ID — primary identifier of the marketing channel; used as a foreign key reference in campaign and activity tables.
• CHANNEL_NAME — the translated channel name (from AMS_CHANNELS_VL).
• CHANNEL_TYPE_CODE — the classification code describing the type of channel (for example, distinguishing channel categories used by marketing setup and execution). This is the column most commonly queried by users searching for channel_type_code.
• MANAGED_BY_PERSON_ID — identifies the internal resource responsible for managing the channel.
• OUTBOUND_FLAG / INBOUND_FLAG — indicate whether the channel supports outbound execution, inbound response capture, or both.
• ACTIVE_FROM_DATE / ACTIVE_TO_DATE — the effective date range during which the channel is valid for use.
• DESCRIPTION — free-text description of the channel.
• PREFERRED_VENDOR_ID / VENDOR_NAME — the preferred supplier associated with the channel and its translated name.
• RATING — a rating value assigned to the channel.
• PARTY_ID — the trading community party associated with the channel.
• CREATION_DATE, COUNTRY_ID, INTERNAL_RESOURCE, and OBJECT_VERSION_NUMBER — audit, geography, internal-resource, and optimistic-locking attributes.

Common Use Cases and Queries

Typical usage includes channel setup validation, marketing reporting, and integration extracts that need channel-to-vendor context. A common query filtering by channel type is:

• SELECT channel_id, channel_name, channel_type_code, outbound_flag, inbound_flag, vendor_name FROM ams_channels_v WHERE channel_type_code = :p_type AND SYSDATE BETWEEN active_from_date AND NVL(active_to_date, SYSDATE + 1);

Other scenarios include listing all active channels with their preferred vendors, joining CHANNEL_ID to campaign or activity tables to enrich marketing execution reports, and verifying which channels are outbound versus inbound before launching a campaign wave. Because VENDOR_NAME is outer-joined, reports should use NVL or LEFT JOIN semantics to avoid dropping channels without a preferred vendor.