Results for “ams_scheduler”

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

Overview

The AMS_SCHEDULER table is a core data object within the Oracle Marketing (AMS) module of Oracle E-Business Suite, present in both the 12.1.1 and 12.2.2 releases. It stores the scheduling information associated with marketing objects, providing the mechanism by which marketing activities, campaigns, and related deliverables are assigned recurring or one-time execution timings. In practical terms, the table acts as the persistence layer for scheduling definitions that drive when marketing objects are processed, delivered, or refreshed.

The table resides in the AMS schema and is classified as VALID in the ETRM metadata. From a Data Vault modeling perspective, the mined structural metadata suggests this object behaves as a standalone entity — effectively a hub-like construct keyed on a single surrogate identifier, with no inbound foreign-key dependencies declared against it beyond a single referencing table. This classification is a heuristic derived from the FK topology and should be treated as a modeling suggestion rather than a documented architectural mandate.

Key Information Stored

The documented physical schema for release 12.2.2 defines eleven columns. The most significant are described below.

• SCHEDULER_ID — The surrogate primary key, enforced by the AMS_SCHEDULER_PK constraint. It uniquely identifies each scheduling record and is the column referenced by dependent tables.
• OBJECT_TYPE and OBJECT_ID — Together these form the business-key candidate that identifies the specific marketing object being scheduled. OBJECT_TYPE records the class of the target entity, while OBJECT_ID holds its identifier.
• FREQUENCY and FREQUENCY_TYPE — These capture the recurrence definition, specifying the interval value and its unit or category. They are the operative fields for determining how often a scheduled action fires.
• OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrency control during concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle EBS audit columns tracking row provenance and modification history.

The combination of OBJECT_TYPE, OBJECT_ID, FREQUENCY, and FREQUENCY_TYPE constitutes the natural business key, distinguishing it from the artificial SCHEDULER_ID surrogate.

Common Use Cases and Queries

Typical usage centers on retrieving and auditing schedule definitions for marketing objects. A frequently used query pattern resolves schedules for a specific object type:

• SELECT scheduler_id, object_type, object_id, frequency, frequency_type FROM ams.ams_scheduler WHERE object_type = :p_object_type AND object_id = :p_object_id; — retrieves all schedule rows for a given marketing object.
• SELECT * FROM ams.ams_scheduler WHERE frequency_type = :p_type ORDER BY creation_date DESC; — reporting on schedules grouped by recurrence category.
• Auditing recently modified schedules using LAST_UPDATE_DATE and LAST_UPDATED_BY for compliance and troubleshooting.

These patterns support campaign scheduling reports, integration extracts, and diagnostic investigations when marketing deliverables fail to execute at expected intervals.

Related Objects

The documented FK topology identifies PO_ASL_ATTRIBUTES as a referencing table through the shared SCHEDULER_ID column, linking approved supplier list attributes to scheduler definitions. Beyond this documented relationship, the AMS module typically pairs AMS_SCHEDULER with marketing entity tables referenced indirectly via OBJECT_TYPE and OBJECT_ID, and with the AMS_SCHEDULER_PK constraint governing uniqueness. Analysts should join through SCHEDULER_ID when tracing dependent records and through OBJECT_TYPE/OBJECT_ID when resolving the scheduled marketing object itself.