Results for “ams_program_components_v”

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

Overview

AMS_PROGRAM_COMPONENTS_V is a reporting view owned by the APPS schema in the Oracle E-Business Suite Marketing (AMS) module. As its name and documentation indicate, the view is created to provide general information about program components — that is, the campaigns and events that make up marketing programs. It presents a consolidated, denormalized projection of marketing activity that joins campaign and event data with owner, status, and location descriptors so that consumers do not need to resolve those relationships themselves.

The view is defined as a UNION ALL across at least two branches. The first branch projects campaign rows from AMS_CAMPAIGNS_VL, tagged with a CAMPAIGN_KEY derived from the campaign identifier concatenated with ROLLUP_TYPE. The second branch projects event header rows from the event headers view, tagged with an 'EVEH' rollup type. This union structure allows a single query to retrieve both campaigns and events as program components, distinguished by the ROLLUP_TYPE column. The view is valid in both EBS 12.1.1 and 12.2.2 and is intended primarily for reporting and integration rather than transactional maintenance.

Underlying Base Objects

The documented base objects referenced by AMS_PROGRAM_COMPONENTS_V are:

• AMS_CAMPAIGNS_VL (VIEW) — the campaign header rows
• AMS_CAMPAIGNS_ALL_B (SYNONYM) — parental campaign lookup
• AMS_EVENT_HEADERS_VL (VIEW) — event header rows for the union branch
• AMS_EVENT_OFFERS_VL (VIEW) — associated event offer data
• AMS_USER_STATUSES_TL (SYNONYM) — user-defined status translations
• JTF_LOC_HIERARCHIES_B (SYNONYM) — location hierarchy lookup
• JTF_LOC_AREAS_TL (SYNONYM) — location area (country) translations
• JTF_RS_RESOURCE_EXTNS_VL (VIEW) — resource/owner details

The campaign branch filters on ACTIVE_FLAG = 'Y' and SHOW_CAMPAIGN_FLAG = 'Y'. Language-dependent joins to AMS_USER_STATUSES_TL and JTF_LOC_AREAS_TL are constrained with USERENV('LANG'), ensuring the caller sees descriptions in their session language. Location, parent, and status joins are largely outer joins, so components are not dropped when related descriptive rows are missing.

Key Columns

• CAMPAIGN_KEY / PROGRAM_KEY — synthetic keys built from identifier and ROLLUP_TYPE, useful for hierarchical grouping.
• CAMPAIGN_ID / PROGRAM_ID — the component identifier and its parent program identifier.
• CAMPAIGN_NAME — the display name of the component.
• ROLLUP_TYPE — distinguishes campaign rows from 'EVEH' event rows in the union.
• STATUS_CODE / STATUS_DATE / USER_STATUS — system status, its effective date, and the translated user-defined status name.
• OWNER_ID / OWNER_NAME / OWNER_MAIL_ID — the owning resource, resolved from JTF_RS_RESOURCE_EXTNS_VL. The OWNER_NAME column is the primary target for user searches of "owner_name".
• COUNTRY_ID / COUNTRY_NAME — mapped from the campaign CITY_ID through the location hierarchy.
• START_DATE / END_DATE — actual execution start and end dates.

Common Use Cases and Queries

The view is commonly used to list all program components with their owners and statuses, and to filter by owner name. A typical query:

• SELECT campaign_name, rollup_type, owner_name, user_status FROM ams_program_components_v WHERE owner_name = :owner;
• SELECT program_id, campaign_name, start_date, end_date FROM ams_program_components_v WHERE rollup_type = 'EVEH' ORDER BY start_date;
• SELECT owner_name, COUNT(*) FROM ams_program_components_v GROUP BY owner_name;

Because OWNER_NAME originates in JTF_RS_RESOURCE_EXTNS_VL, users searching for "owner_name" should apply the filter against this view rather than joining resources manually. Note that reporting queries should account for language settings, as status and country descriptions are language-dependent.