Search Results ams_campaign_schedule_status

Overview

The view APPS.AMS_P_CAMPAIGN_SCHEDS_V is a denormalized reporting and integration layer exposed by Oracle Marketing (formerly Oracle Advanced Marketing) within Oracle E-Business Suite releases 12.1.1 and 12.2.2. It presents campaign schedule records—the individual, time-bound execution instances of a marketing campaign—joined to the descriptive meaning of each schedule's lifecycle status. The view's central purpose is to translate the coded values stored on the schedule row into human-readable and API-consumable form, and to restrict the result set to schedules belonging to active, displayed campaigns.

It is a "presentation" view in the AMS (Advanced Marketing) family, distinguished by the _P_ prefix, which conventionally indicates a public, presentation-oriented view intended for external consumption by reports, concurrent programs, and integration interfaces rather than for internal transactional processing. For users who search on ams_campaign_schedule_status, this view is the primary object of interest because it is here that the lookup type AMS_CAMPAIGN_SCHEDULE_STATUS is joined to the schedule row and its meaning exposed as the SCHEDULE_STATUS column.

Underlying Base Objects

The documented referenced base objects are: AMS_CAMPAIGNS_VL (VIEW), AMS_CAMPAIGN_SCHEDULES_VL (VIEW), AMS_CHANNELS_TL (SYNONYM), AMS_EVENT_OFFERS_VL (VIEW), AMS_LOOKUPS (VIEW), AMS_MEDIA_TL (SYNONYM), and HZ_TIMEZONES_TL (SYNONYM). Accordingly, the view orchestrates these sources:

• AMS_CAMPAIGN_SCHEDULES_VL supplies the schedule row attributes—the driving table aliased B—carrying SCHEDULE_ID, CAMPAIGN_ID, status code, dates, and media references.
• AMS_CAMPAIGNS_VL (aliased CAM) supplies the parent campaign's name, type, and source, joined on CAMPAIGN_ID.
• AMS_LOOKUPS is joined twice: once as LKP for the schedule status meaning and once as LKP1 for the media type meaning.
• AMS_MEDIA_TL (aliased M) supplies MEDIA_NAME, AMS_CHANNELS_TL (aliased CH) supplies CHANNEL_NAME, and HZ_TIMEZONES_TL (aliased HZ) supplies the timezone display name.
• AMS_EVENT_OFFERS_VL appears in the documented lineage and is referenced through the union branch that handles event-based schedules (ACTIVITY_TYPE_CODE = 'EVENTS'), which the first branch explicitly excludes.

Key Columns

• SCHEDULE_ID, CAMPAIGN_ID — primary identifiers of the schedule and its campaign.
• CAMPAIGN_NAME, CAMPAIGN_TYPE, CAMPAIGN_SOURCE_CODE — descriptive campaign attributes.
• SCHEDULE_STATUS — the lookup meaning derived from LKP for lookup type AMS_CAMPAIGN_SCHEDULE_STATUS, joined via STATUS_CODE. This is the column users searching on ams_campaign_schedule_status are typically after.
• STATUS_CODE, STATUS_DATE — the underlying lookup code and the timestamp the status was set.
• USE_PARENT_CODE_FLAG, SCHEDULE_SOURCE_CODE — inheritance and origin indicators.
• ACTIVITY_TYPE_CODE and the derived media type meaning — the schedule's activity classification.
• ACTIVITY_ID, MEDIA_NAME, MARKETING_MEDIUM_ID, CHANNEL_NAME — the medium and channel through which the schedule is delivered.
• START_DATE_TIME, END_DATE_TIME, START_PERIOD_NAME, END_PERIOD_NAME, TIMEZONE_ID, HZ.NAME — scheduling window and timezone.
• Contact and delivery attributes: COVER_LETTER_ID, REPLY_TO_MAIL, MAIL_SENDER_NAME, MAIL_SUBJECT, FROM_FAX_NO.
• Governance and financial columns: TRIGGERABLE_FLAG, TRIGGER_ID, NOTIFY_USER_ID, APPROVER_USER_ID, OWNER_USER_ID, PRIORITY, TRANSACTION_CURRENCY_CODE, FUNCTIONAL_CURRENCY_CODE, BUDGET_AMOUNT_TC, BUDGET_AMOUNT_FC.

Common Use Cases and Queries

Typical uses include campaign schedule dashboards, status monitoring reports, and outbound integration extracts. The view's filters (B.ACTIVE_FLAG = 'Y', CAM.SHOW_CAMPAIGN_FLAG = 'Y', CAM.ACTIVE_FLAG = 'Y', and language/timeline matching via USERENV('LANG')) mean it returns only currently displayable, active data for the session language.

Sample query to list schedules by status for a campaign:

• SELECT schedule_id, campaign_name, schedule_status, status_date, start_date_time, end_date_time
• FROM apps.ams_p_campaign_scheds_v
• WHERE campaign_id = :campaign_id
• ORDER BY start_date_time;

To summarize schedule counts per status:

• SELECT schedule_status, COUNT(*) num_schedules
• FROM apps.ams_p_campaign_scheds_v
• GROUP BY schedule_status
• ORDER BY schedule_status;

To retrieve schedules in flight for a given time window and medium:

• SELECT schedule_id, campaign_name, media_name, channel_name, schedule_status
• FROM apps.ams_p_campaign_scheds_v
• WHERE start_date_time >= :from_dt AND end_date_time <= :to_dt
• AND media_name = :media;

Because the view joins translated (_TL) objects using USERENV('LANG'), results are language-sensitive; applications should not cache results across sessions with differing language settings. As with all AMS presentation views, it should be queried rather than modified—status transitions are performed through the underlying Marketing APIs against AMS_CAMPAIGN_SCHEDULES, with the view reflecting the resulting lookup meaning.