Results for “repeat_frequency_type”

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

Overview

AMS_TRIGGERS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2, owned by the AMS schema. The table stores trigger definitions that govern the time-based or event-based initiation of Marketing activities, such as campaign and activity metric events. As stated in the ETRM documentation, AMS_TRIGGERS stores triggers to start execution of a certain activity at a certain time. The object is classified as VALID and presents 35 documented columns in the 12.2.2 physical schema.

The table functions as the scheduling and control hub for Marketing activity execution. Each row defines when and how a trigger should fire, including recurrence rules, time zone handling, notification behavior, and execution status. Based on the heuristic Data Vault classification mined from the foreign key structure, this table is hub-leaning. From a modeling perspective, TRIGGER_ID can be treated as a hub key, with the table also acting as the parent in several one-to-many relationships to dependent detail tables.

Key Information Stored

• TRIGGER_ID — Surrogate primary key defined by AMS_TRIGGERS_PK and also enforced by the unique index AMS_TRIGGERS_U1. This is the column referenced by all child tables.
• TRIGGER_NAME — Business identifier for the trigger, part of the composite unique key AMS_TRIGGERS_UK1 along with VIEW_APPLICATION_ID. This is the primary business-key candidate used to locate a trigger by name.
• VIEW_APPLICATION_ID — Application context associated with the trigger, forming the second component of AMS_TRIGGERS_UK1.
• TRIGGER_CREATED_FOR_ID — Identifies the owning entity, resolving to an AMS_CAMPAIGNS_ALL_B campaign or an AMS_ACT_METRICS_ALL activity metric record. ARC_TRIGGER_CREATED_FOR provides the corresponding entity discriminator.
• TRIGGERING_TYPE — Classifies the trigger category, determining which scheduling and check logic applies.
• START_DATE_TIME, LAST_RUN_DATE_TIME, NEXT_RUN_DATE_TIME — Core scheduling timestamps tracking the planned start, most recent execution, and next scheduled execution.
• REPEAT_DAILY_START_TIME, REPEAT_DAILY_END_TIME, REPEAT_STOP_DATE_TIME — Daily window and recurrence termination parameters.
• REPEAT_FREQUENCY_TYPE, REPEAT_EVERY_X_FREQUENCY — Recurrence unit and interval (for example, every N hours or days).
• TIMEZONE_ID — Foreign key to HZ_TIMEZONES, giving the time zone in which scheduling timestamps are interpreted.
• USER_START_DATE_TIME, USER_LAST_RUN_DATE_TIME, USER_NEXT_RUN_DATE_TIME, USER_REPEAT_DAILY_START_TIME, USER_REPEAT_DAILY_END_TIME, USER_REPEAT_STOP_DATE_TIME — User-facing display forms of the scheduling timestamps, typically rendered in the user's time zone.
• METRICS_REFRESH_TYPE — Controls metric refresh behavior associated with the trigger.
• NOTIFY_FLAG, EXECUTE_SCHEDULE_FLAG, TRIGGERED_STATUS — Control flags indicating whether notification is sent, whether the schedule should execute, and the current execution status.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-org style data security.
• USAGE — Free-form classification of the trigger's intended usage.

Standard WHO audit columns are also present: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER, the latter supporting optimistic locking.

Common Use Cases and Queries

Typical reporting scenarios include listing all active triggers for a campaign, identifying overdue executions, and auditing recurrence configuration. A basic query joining the trigger to its campaign:

• SELECT t.trigger_id, t.trigger_name, t.next_run_date_time, t.triggered_status FROM ams_triggers t, ams_campaigns_all_b c WHERE t.trigger_created_for_id = c.campaign_id AND t.triggering_type = 'CAMPAIGN';
• Locate a trigger by its business key: SELECT * FROM ams_triggers WHERE trigger_name = :name AND view_application_id = :app_id;
• Find triggers not yet run or overdue: SELECT trigger_id, trigger_name, next_run_date_time FROM ams_triggers WHERE next_run_date_time < SYSDATE AND triggered_status <> 'COMPLETE';
• Report time zone distribution: SELECT z.timezone_code, COUNT(*) FROM ams_triggers t, hz_timezones z WHERE t.timezone_id = z.timezone_id GROUP BY z.timezone_code;

Because notification and execution are governed by NOTIFY_FLAG and EXECUTE_SCHEDULE_FLAG, these flags are frequently included in operational dashboards and troubleshooting queries.

Related Objects

• AMS_CAMPAIGN_SCHEDULES_B — References AMS_TRIGGERS via TRIGGER_ID, linking schedules to their parent trigger.
• AMS_TRIGGERS_TL — The translation (TL) table, joined on TRIGGER_ID, holding language-specific trigger text.
• AMS_TRIGGER_ACTIONS — Child table keyed by TRIGGER_ID, defining actions to perform when a trigger fires.
• AMS_TRIGGER_CHECKS — Child table keyed by TRIGGER_ID, defining conditions that gate trigger execution.
• AMS_CAMPAIGNS_ALL_B — Parent of TRIGGER_CREATED_FOR_ID when the trigger belongs to a campaign.
• AMS_ACT_METRICS_ALL — Alternate parent of TRIGGER_CREATED_FOR_ID for metric-based triggers.
• HZ_TIMEZONES — Joined on TIMEZONE_ID to resolve the scheduling time zone.
• FND_SECURITY_GROUPS — Joined on SECURITY_GROUP_ID to enforce data security.

Together these objects form the Marketing trigger framework: AMS_TRIGGERS holds the definition, AMS_TRIGGER_CHECKS and AMS_TRIGGER_ACTIONS supply the evaluation and action logic, and AMS_CAMPAIGN_SCHEDULES_B and AMS_TRIGGERS_TL provide scheduling and multilingual context.