Search Results triggerable_flag

Overview

AMS_CAMPAIGN_SCHEDULES_VL is an APPS-owned, VALID view within the Oracle E-Business Suite Marketing (AMS) module. It serves as the primary reporting and integration interface for marketing campaign schedules, exposing the denormalized combination of transactional schedule data and its translated descriptive content. The "_VL" suffix indicates a "view with language translation" — the view joins the base entity table (AMS_CAMPAIGN_SCHEDULES_B) to its translation table (AMS_CAMPAIGN_SCHEDULES_TL) and surfaces the translation columns alongside the base columns so that consumers retrieve a single row per schedule in the appropriate language. Because it is defined in the APPS schema, it is accessible to any responsibility or custom code with grants on the APPS synonym layer, making it the canonical object referenced by concurrent programs, OAF/Forms pages, BI Publisher reports, and external integrations that need campaign schedule details without navigating the B/TL join themselves.

Underlying Base Objects

The view is documented in ETRM 12.2.2 as being defined over two referenced base objects, both consumed via SYNONYM:

• AMS_CAMPAIGN_SCHEDULES_B — the base (B) table holding the transactional, language-independent attributes of a campaign schedule. This is the "driving" table in the view and is aliased as B in the view text.
• AMS_CAMPAIGN_SCHEDULES_TL — the translation (TL) table holding the language-dependent descriptive text. In the view text it is aliased as T and contributes SCHEDULE_NAME, DESCRIPTION, GREETING_TEXT, and FOOTER_TEXT.

The view therefore implements the standard Oracle EBS multi-language pattern: one physical row in _B, one or more rows in _TL keyed by LANGUAGE_CODE, and a _VL view that presents the merged result. The B-side columns include the primary key SCHEDULE_ID, the foreign key CAMPAIGN_ID, audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER), and the multi-org column ORG_ID.

Key Columns

• SCHEDULE_ID — primary key of the campaign schedule; also the join key between the B and TL tables.
• CAMPAIGN_ID — parent marketing campaign to which the schedule belongs.
• SCHEDULE_NAME, DESCRIPTION, GREETING_TEXT, FOOTER_TEXT — translation columns sourced from the TL table; SCHEDULE_NAME is the user-facing identifier and is the natural display key for reports.
• STATUS_CODE / USER_STATUS_ID / STATUS_DATE — the schedule's lifecycle state and status timestamp, used for workflow and approval reporting.
• START_DATE_TIME / END_DATE_TIME / TIMEZONE_ID — the scheduling window, timezone-qualified.
• ACTIVITY_TYPE_CODE / ACTIVITY_ID — the marketing activity (e.g., email, fax, telemarketing) associated with the schedule.
• ACTIVE_FLAG / USE_PARENT_CODE_FLAG / TRIGGERABLE_FLAG / TRIG_REPEAT_FLAG — behavioral control flags.
• OWNER_USER_ID / APPROVER_USER_ID / NOTIFY_USER_ID — responsible parties for workflow routing.
• ORG_ID — the operating unit; critical for any multi-org (MOAC) secured query.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–15 — the standard DFF (descriptive flexfield) columns for schedule-level extensions.
• ACTIVITY_ATTRIBUTE_CATEGORY, ACTIVITY_ATTRIBUTE1–15 — a second DFF block for activity-level extensions.
• LANGUAGE_CODE — populated from the TL join; must be constrained or joined to FND_LANGUAGES for deterministic single-language output.

Common Use Cases and Queries

The most common requirement is a simple language-filtered listing of schedules for a campaign or date range. Because the VL view can return multiple rows per SCHEDULE_ID when multiple translations exist, always constrain LANGUAGE_CODE:

SELECT schedule_id, schedule_name, campaign_id,
       status_code, start_date_time, end_date_time
FROM   apps.ams_campaign_schedules_vl
WHERE  language_code = USERENV('LANG')
AND    org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
AND    start_date_time >= TRUNC(SYSDATE)
ORDER  BY start_date_time;

For translation-aware reporting (show the description in the session language, falling back to the base language), join the TL portion rather than relying on the view's implicit join, or filter with language = USERENV('LANG') to guarantee one row per schedule. For integration extracts, select the audit and DFF columns alongside the descriptive columns to preserve both technical and business context:

SELECT v.schedule_id, v.schedule_name, v.description,
       v.campaign_id, v.status_code, v.activity_type_code,
       v.owner_user_id, v.attribute1, v.attribute_category
FROM   apps.ams_campaign_schedules_vl v
WHERE  v.language_code = USERENV('LANG')
AND    v.campaign_id = :p_campaign_id;

Because the view is read-only and owned by APPS, it is safe to query from custom reports, OBIEE/OTBI extracts, interfaces, and ad-hoc SQL without disturbing the underlying AMS transactional tables. Any inserts or updates must instead target the AMS_CAMPAIGN_SCHEDULES_B and _TL tables through the supported AMS APIs, not the VL view.