Results for “campaign_calendar”

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

Overview

AMS_CAMPAIGN_SCHEDULES_B is a core transactional table within the Oracle Marketing (AMS) module of Oracle E-Business Suite, residing in the AMS schema. It stores the schedule-level definition of a marketing campaign activity — that is, the operational instance that governs when, through which channel, to which audience, and under what trigger conditions a campaign deliverable is executed. Each record represents one scheduled campaign activity, binding a parent campaign to a marketing medium, an optional trigger, an owner, and a time window defined by start and end date-times. The table is the "base" (translated-independent) entity, complemented by the multilingual AMS_CAMPAIGN_SCHEDULES_TL table for descriptive text.

From a Data Vault modeling perspective, the table is heuristically classified as satellite-leaning. It carries a single surrogate primary key (SCHEDULE_ID) and a wide set of descriptive and type-2-eligible attributes (statuses, dates, flags, and user-defined attributes), rather than acting as a pure hub of business keys or a pure association link. A rigorous Data Vault design would likely treat the schedule identifier as a hub key and most remaining columns as satellite attributes.

Key Information Stored

The surrogate primary key is SCHEDULE_ID, enforced by the unique constraint AMS_CAMPAIGN_SCHEDULES_B_PK (and mirrored by unique index AMS_CAMPAIGN_SCHEDULES_B_U1). The most consequential columns include:

• CAMPAIGN_ID — foreign key to AMS_CAMPAIGNS_ALL_B, the parent campaign.
• ACTIVITY_ID — foreign key to AMS_MEDIA_B, the marketing activity/media definition.
• MARKETING_MEDIUM_ID — foreign key to AMS_CHANNELS_B, the delivery channel.
• TRIGGER_ID / TRIGGERABLE_FLAG — trigger linkage (AMS_TRIGGERS) and whether the schedule is trigger-driven.
• START_DATE_TIME / END_DATE_TIME / TIMEZONE_ID — the effective scheduling window, with timezone reference to HZ_TIMEZONES.
• STATUS_CODE / USER_STATUS_ID / STATUS_DATE — lifecycle state and user-defined status (AMS_USER_STATUSES_B).
• OWNER_USER_ID / APPROVER_USER_ID / NOTIFY_USER_ID — resource references to JTF_RS_RESOURCE_EXTNS governing responsibility and notification.
• TASK_ID — link to JTF_TASKS_B for CRM task integration.
• ORG_ID / SECURITY_GROUP_ID — multi-org and security partitioning.

Approximately 102 physical columns are documented, including a fifteen-column ATTRIBUTE and ACTIVITY_ATTRIBUTE flexfield set for extensibility. Note that SCHEDULE_ID is both the surrogate PK and the only documented unique business-key candidate; no alternate natural unique key is defined.

Common Use Cases and Queries

Typical reporting retrieves active schedules for a campaign with their channel and temporal bounds:

SELECT s.schedule_id, s.campaign_id, s.marketing_medium_id,
s.start_date_time, s.end_date_time, s.status_code
FROM ams_campaign_schedules_b s
WHERE s.campaign_id = :p_campaign_id
AND s.active_flag = 'Y';

Operations teams frequently join the base table to its translation table to report schedule names in a target language, and join to AMS_CHANNELS_B for channel-level summaries. Trigger configuration audits join to AMS_TRIGGERS on TRIGGER_ID. Approval and ownership dashboards resolve OWNER_USER_ID, APPROVER_USER_ID, and NOTIFY_USER_ID against JTF_RS_RESOURCE_EXTNS. Data-mart extracts for campaign analytics commonly join AMS_TCOP_CONTACTS and AMS_TCOP_CONTACT_SUM_DTL on SCHEDULE_ID to measure contact and response volumes per schedule.

Related Objects

The most significant related objects, with join columns, include:

• AMS_CAMPAIGNS_ALL_B — parent campaign; join on CAMPAIGN_ID.
• AMS_CAMPAIGN_SCHEDULES_TL — translated names/descriptions; join on SCHEDULE_ID.
• AMS_CHANNELS_B — channel definition; join on MARKETING_MEDIUM_ID.
• AMS_MEDIA_B — activity/media; join on ACTIVITY_ID.
• AMS_TRIGGERS — trigger definitions; join on TRIGGER_ID.
• AMS_TCOP_CONTACTS and AMS_TCOP_CONTACT_SUM_DTL — contact and summary analytics; join on SCHEDULE_ID.
• AMS_TCOP_REQUESTS — request tracking; join on SCHEDULE_ID.
• AMS_DM_OPT_TGT_METRICS — target metric optimization; join on SCHEDULE_ID.
• JTF_TASKS_B and JTF_RS_RESOURCE_EXTNS — task and resource integration; join on TASK_ID, OWNER_USER_ID, APPROVER_USER_ID, NOTIFY_USER_ID.

These relationships position AMS_CAMPAIGN_SCHEDULES_B as the operational hub of campaign execution data in the Oracle Marketing schema.