Search Results schedule_source_code

Overview

AMS_P_CAMPAIGN_SCHEDS_V is a public view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the AMS (Marketing) product family and returns campaign schedule information for reporting and integration purposes. A campaign schedule represents a dated execution instance of a marketing campaign, carrying its own status, activity type, media, channel, budget, and approval attributes. The view consolidates this data with descriptive values resolved from lookup, media, channel, campaign, and timezone sources so that downstream consumers—such as the Marketing dashboard, concurrent programs, and custom extracts—can query a single denormalized result set without reconstructing joins themselves. Because it is a public (APPS-owned) view, it is treated as a supported interface for customer reporting, though its columns reflect internal AMS data structures and should be used with a stable column list rather than SELECT *.

Underlying Base Objects

The documented base objects referenced by the view are:

• AMS_CAMPAIGN_SCHEDULES_VL — the primary source of schedule rows, including status, dates, activity, budget, and approval columns.
• AMS_CAMPAIGNS_VL — supplies campaign name, campaign type, and campaign source code.
• AMS_LOOKUPS — joined twice, once on lookup type AMS_CAMPAIGN_SCHEDULE_STATUS and again on AMS_MEDIA_TYPE, to translate codes into meanings.
• AMS_MEDIA_TL — resolves the media name for the activity.
• AMS_CHANNELS_TL — resolves the channel name; the join is outer, so schedules without a marketing medium remain visible.
• HZ_TIMEZONES_TL — resolves the timezone description in the user's session language.
• AMS_EVENT_OFFERS_VL — referenced in the metadata for the second branch of the union that completes the view definition.

The view is defined as a UNION, with the first branch restricted to schedule records whose activity type is not EVENTS and whose parent campaign is active and marked for display. Filtering enforces ACTIVE_FLAG = 'Y' on schedules and campaigns, and language-sensitive joins use USERENV('LANG') for media, channel, and timezone translations.

Key Columns

• SCHEDULE_ID / CAMPAIGN_ID — primary identifiers for the schedule and its parent campaign.
• SCHEDULE_STATUS — the translated meaning of STATUS_CODE from the AMS_CAMPAIGN_SCHEDULE_STATUS lookup; this is the column returned by searches on "schedule_status".
• STATUS_CODE / STATUS_DATE — the untranslated status code and the timestamp of the last status change.
• CAMPAIGN_NAME, CAMPAIGN_TYPE, CAMPAIGN_SOURCE_CODE — campaign context for the schedule.
• ACTIVITY_TYPE_CODE, LKP1.MEANING, ACTIVITY_ID, MEDIA_NAME — the schedule's activity classification and associated media.
• MARKETING_MEDIUM_ID, CHANNEL_NAME — the marketing medium and its channel name.
• START_DATE_TIME, END_DATE_TIME, TIMEZONE_ID, HZ.NAME — execution window with timezone.
• START_PERIOD_NAME / END_PERIOD_NAME — accounting or planning periods for the schedule.
• BUDGET_AMOUNT_TC / BUDGET_AMOUNT_FC — budget in transaction and functional currency.
• APPROVER_USER_ID, OWNER_USER_ID, NOTIFY_USER_ID, PRIORITY — ownership and workflow attributes.
• TRIGGERABLE_FLAG, TRIGGER_ID, USE_PARENT_CODE_FLAG, SOURCE_CODE — control flags governing triggering and inheritance.
• COVER_LETTER_ID, REPLY_TO_MAIL, MAIL_SENDER_NAME, MAIL_SUBJECT, FROM_FAX_NO — direct-marketing execution details.

Common Use Cases and Queries

Typical usage includes operational status reporting, schedule pipeline analysis, and budget tracking by campaign or channel.

SELECT schedule_id, campaign_name, schedule_status,
       start_date_time, end_date_time, channel_name
FROM   apps.ams_p_campaign_scheds_v
WHERE  schedule_status = 'Active'
ORDER BY start_date_time;

SELECT campaign_name, schedule_status, COUNT(*)
FROM   apps.ams_p_campaign_scheds_v
GROUP BY campaign_name, schedule_status;

SELECT schedule_id, campaign_name, budget_amount_fc
FROM   apps.ams_p_campaign_scheds_v
WHERE  start_date_time BETWEEN :p_from AND :p_to
AND    channel_name = :p_channel;

Because SCHEDULE_STATUS is derived from a lookup join, status values depend on the seeded meanings of AMS_CAMPAIGN_SCHEDULE_STATUS; joins back to code columns should use STATUS_CODE for exact matching. The view is read-only and is best consumed through a report or an extract rather than as a DML target.