This chapter covers the following topics:
Web Application Desktop Integrator or Web ADI is a tool that brings Oracle E-Business Suite functionality to a spreadsheet, where familiar data entry and modeling techniques can be used to complete Oracle E-Business Suite tasks. Oracle Marketing uses Web ADI support to setup campaign activities from the Campaign Workbench.
When the marketing department of an organization needs to work with a large number of campaign activities that are part of a bigger marketing plan, marketers can use the spreadsheet interface provided by Web ADI. The worksheet provides a quick and simple means to create and update multiple campaign activities within and across campaigns. The campaign activity data can be exported into seeded Web ADI layouts, and can be easily accessed externally, for analysis.
A Web ADI layout is a combination of a spreadsheet interface consisting of a set of logically grouped columns, and the mapping of attributes from each of these columns to the appropriate column in the underlying data store; and the logic to create or update the data with the necessary validations.
As an administrator, you can customize Web ADI layouts for marketing use. You can also modify or delete Web ADI layouts.
The following are the prerequisites for Web ADI:
Client PC with Windows ME, Windows NT 4.0 (with Service Pack 3 or later), Windows 2000, Windows XP, or Windows 98 installed on it
Internet Explorer 5.0 or greater installed on the client PC
Microsoft Excel 97, 2000, 2003, or XP installed on the client PC. For Web ADI to work with Microsoft Excel XP/2003, you must change the macro settings for Excel XP/2003. To do so:
In Excel, go to Tools > Macro > Security > Trusted Sources.
Select the "Trust access to Visual Basic Project" option.
To allow spreadsheets to be created on your desktop, change your intranet browser security settings as follows:
Navigate to Tools > Internet Options > Security > Custom Level.
Set the "Initialize and script ActiveX controls not marked as safe" option to "Prompt".
Refer to the Web ADI documentation for more information.
Refer to the Oracle Marketing User Guide to know the general guidelines for using Web ADI layouts.
Oracle Marketing ships with three seeded Web ADI layouts. These include:
Activity Import
Marketers can use this layout to create new schedules and import them into the Marketing application.
Activity Update
Marketers can use this layout for mass update of basic identifying activity attributes. Sample scenarios where this layout may be used:
The marketing manager needs to adjust the dates of the Campaign Activities for a specific Campaign for which the dates have changed
The marketing administrator needs to mass update the ownership of Campaign Activities when the current owner leaves the marketing department
The marketing administrator needs to change the parent campaign for a set of Campaign Activities
The marketing manager needs to cancel a set of Campaign Activities
Schedule Media Planner
Marketers can use this layout to create or update schedule metrics data.
Important: Please do not modify or delete seeded Web ADI layouts.
Web ADI security is based on the access permissions to the application that the logged in user has.
Marketing administrators with full access privileges can create and update activities and access all the activities data in the system.
For marketers and marketing managers, the following restrictions must be applied on the Web ADI layouts:
Access to parent campaigns should be based on team access security restrictions
Access to Oracle Content Manager (OCM) content and cover letters should be based on OCM security definitions. Refer to OCM documentation for more information.
Marketers can update the metrics of an activity only if they have the edit metrics privileges for the parent campaign (unless the user is the activity owner or a system administrator).
When the Update process is invoked to process data from a spreadsheet saved earlier, logged in users can update only those activities to which they have access.
For export of activities and media planner, you should set the macros security level to Medium in Microsoft Excel.
For Oracle Marketing, the Web ADI worksheet provides a quick and simple means for marketers to create or update multiple Campaign Activities, within and across campaigns. Marketers can also save a Web ADI worksheet on their desktops and work with it off line.
To implement the Web ADI integration, see the following sections:
Oracle Marketing ships with one seeded menu called "AMS Web ADI Related Functions" (AMS_ADI_ALL_FUNCTIONS). To enable Web ADI for Marketing, you must enable the Grant flag for the menu and place it in the custom menu for Marketing.
The "AMS Web ADI Related Functions" menu contains three functions:
Desktop Integrator - Define Layout: Select this function to be able to define layouts
Marketing Schedule - Export Integrators: Select this function to enable the basic schedule Update and Media Planner functionality
Marketing Schedule - Import Integrator: Select this function to enable the Import functionality
All the above functions have the Grant flag disabled by default.
All the functions (Web ADI Import and Export) necessary for marketers, marketing managers, and administrators to use Web ADI are added to the Marketing Administrator and Marketing User seeded responsibilities. They must be added when you create any custom responsibility too.
The following concurrent programs run in the background for Web ADI integration:
AMS: Schedules Import: When the Import process is invoked.
AMS: Export and Update Schedule: When the Update process is invoked.
AMS: Media Planner Loader: When the Schedule Media Planner layout is used to map metrics data from the interface table to schedule metrics. If metrics already exist for the schedule, the metric values are updated. If the metrics do not exist, new metrics are created for the schedule.
Refer to Oracle Marketing User Guide for information on Web ADI processes.
The three seeded Web ADI layouts contain a large number of columns, including the columns needed for basic campaign activity details, different schedule associations, and schedule metrics. For example, your organization may use only 5 metrics, which is a much smaller number to what is provided in the seeded layouts. In such a situation, you may customize the seeded layout by reducing the number of columns in it.
Some usage examples follow.
You may customize a Web ADI layout to create specific import layouts for specific schedule types, such as:
Advertising Schedules Import Worksheet - may include basic schedule details, associations, a few metrics, and Web site response handling details.
Web Advertising Schedules Import Worksheet - may include basic schedule details, associations, media planning details, and click through tracking URL definition for the Web content.
Direct Marketing Schedules Import Worksheet - may include basic schedule details for direct marketing channels, collateral associations, and Web site response handling details.
Metrics Import Worksheet - may include schedule Id, schedule name, and all the metric details and can be used by a marketer to upload metrics only.
The following figure depicts the flow for creating a customized Web ADI layout.
Process Flow for Customizing a Web ADI Layout
To create a customized Web ADI layout, you can do one of the following:
Duplicate a seeded Web ADI layout, make the necessary changes to it, and save it as a new Marketing usage specific layout.
Define a new layout based on a seeded layout (referred to as "Integrator").
Navigation: Campaign Dashboard > Administration > Define Layout
Notes
Select from the three integrators available for Marketing:
Marketing - Schedules Import Integrator
Marketing - Schedules Update Integrator
Marketing - Schedules Media Planner
Placement: To define the location of the field on the spreadsheet, you can choose from:
Context: Appears at the top of the spreadsheet as read only, contextual information. For example, for a layout definition specific for a channel, you may want to place the Channel field as read only contextual information.
Header: Fields whose values do not change for every record you are uploading. This region appears above the line region in the spreadsheet. For example, for a layout definition specific for a campaign, you may want to place the Campaign field information in the header region.
Line: Fields whose values change belong in the line region. This is data you add and modify. Most campaign activity related fields belong in the Line region, and will appear as rows in the spreadsheet.
For the Required fields, the default Placement is Header. You may need to change them to Line, if they must appear in the spreadsheet rows.
Default Type: To specify the nature of the default value, you can choose from:
None: Use when there no Default Value need be specified.
Constant: Use when the text entered in the Default Value field must be displayed as the default value in the spreadsheet.
Environment: Use to reference an environment variable when setting a default for a field. Examples of values for the Environment Default Type:
sysdate: System date
database: Name of current database
oauser.id: The identification code of the user currently logged in to Oracle Applications
SQL: Use to run a SQL statement to determine the default for the field. Web ADI will run the SQL statement entered in the Default Value field and automatically populate the spreadsheet with the result. If more than one value is returned from the query, the first value is used. Some tokens you can use in the SQL statement include:
$profile$.profileName: Enter the name of the profile option, and its value for the current user’s profile will be returned
$env$.userid: Returns the current user ID
$env$.appid: Returns the current application ID
$env$.respid: Returns the current responsibility ID
Parameter: Use to reference a parameter that your system administrator stores in the form function (Self Service Link) you use to access the Create Document flow.
Cancel: To be redirected back to the Campaign Dashboard when Cancel is clicked, the profile BNE_REDIRECT_PORTAL must be set up to point to the Campaign Dashboard as follows:
http://OA_HTML/OA.jsp?OAFunc=AMS_WB_CAMP_DASHBOARD
Note: Set the VO_MAX_FETCH_SIZE profile value to define the number of rows that must be downloaded for updates during the Export process. This value is typically set to 200.
The following table provides guidelines for including fields in custom layouts based on the Marketing - Schedules Import Integrator:
Field Name | Required? | Additional Fields to be Included | Remarks |
---|---|---|---|
Campaign | Yes | Campaign Id | Campaign Id must be included in all new layouts |
Channel | Yes | Channel Id | Channel Id must be included in all new layouts |
Coordinator | Yes | Coordinator Id | - |
Schedule Name | Yes | NA | - |
Template | Yes | NA | - |
Currency | Yes | NA | The Currency field is needed to default metrics values |
Timezone | Yes | NA | - |
Launch On | Yes | NA | - |
Launch Time | No | NA | - |
End On | No | NA | - |
End Time | No | NA | - |
Source Code | No | NA | - |
Country | No | Country Id | - |
Marketing Medium | No | Marketing Medium Id | For some schedules, such as Advertising schedules, this is a mandatory field, and hence must be included |
Repeat Frequency | No |
|
- |
Schedule Flexfields | No | NA | - |
Channel Based Flexfields | No | Channel Based Flexfield Context | - |
Product Category 1 | No |
|
- |
Product Category 2 | No |
|
- |
Product 1 | No |
|
- |
Product 2 | No |
|
- |
Cover Letter | No | Cover Letter Id | - |
Any Web Planner field | No |
|
|
Any Landing Page URL field | No |
|
- |
Collaboration Content Item 1 | No | Collaboration Content Item Id 1 | - |
Collaboration Content Item 2 | No | Collaboration Content Item Id 2 | - |
Collaboration Content Item 3 | No | Collaboration Content Item Id 3 | - |
Metric 1 - Manual Fixed | No | Metric 1 - Manual Fixed Forecast | - |
Metric 2 - Manual Fixed | No | Metric 2 - Manual Fixed Forecast | - |
Metric 3 - Manual Fixed | No | Metric 3 - Manual Fixed Forecast | - |
Metric 4 - Manual Fixed | No | Metric 4 - Manual Fixed Forecast | - |
Metric 5 - Manual Fixed | No | Metric 5 - Manual Fixed Forecast | - |
Metric 6 - Manual Fixed | No | Metric 6 - Manual Fixed Forecast | - |
Metric 7 - Manual Fixed | No | Metric 7 - Manual Fixed Forecast | - |
Metric 8 - Manual Fixed | No | Metric 8 - Manual Fixed Forecast | - |
Metric 9 - Manual Fixed | No | Metric 9 - Manual Fixed Forecast | - |
Metric 10 - Manual Fixed | No | Metric 10 - Manual Fixed Forecast | - |
Metric 1 - Manual Variable | No | Metric 1 - Manual Variable Forecast Unit | - |
Metric 2 - Manual Variable | No | Metric 2 - Manual Variable Forecast Unit | - |
Metric 3 - Manual Variable | No | Metric 3 - Manual Variable Forecast Unit | - |
Metric 4 - Manual Variable | No | Metric 4 - Manual Variable Forecast Unit | - |
Metric 5 - Manual Variable | No | Metric 5 - Manual Variable Forecast Unit | - |
Metric 1 - Function Fixed | No | Metric 1 - Function Fixed Forecast | - |
Metric 2 - Function Fixed | No | Metric 2 - Function Fixed Forecast | - |
Metric 3 - Function Fixed | No | Metric 3 - Function Fixed Forecast | - |
Metric 4 - Function Fixed | No | Metric 4 - Function Fixed Forecast | - |
Metric 5 - Function Fixed | No | Metric 5 - Function Fixed Forecast | - |
Metric 6 - Function Fixed | No | Metric 6 - Function Fixed Forecast | - |
Metric 7- Function Fixed | No | Metric 7 - Function Fixed Forecast | - |
Metric 8 - Function Fixed | No | Metric 8 - Function Fixed Forecast | - |
Metric 9 - Function Fixed | No | Metric 9 - Function Fixed Forecast | - |
Metric 10 - Function Fixed | No | Metric 10 - Function Fixed Forecast | - |
Metric 1 - Function Variable | No | Metric 1 - Function Variable Forecast Unit | - |
Metric 2 - Function Variable | No | Metric 2 - Function Variable Forecast Unit | - |
Metric 3 - Function Variable | No | Metric 3 - Function Variable Forecast Unit | - |
Metric 4 - Function Variable | No | Metric 4 - Function Variable Forecast Unit | - |
Metric 5 - Function Variable | No | Metric 5 - Function Variable Forecast Unit | - |
The following table provides guidelines for including fields in custom layouts based on the Marketing - Schedules Update Integrator.
Field Name | Required? | Additional Fields to be Included | Remarks |
---|---|---|---|
Spreadsheet Reference | NA | NA |
|
Schedule Name | Yes |
|
- |
Campaign | No | Campaign Id |
|
Channel | No | Channel Id |
|
Coordinator | No | Coordinator Id | A read-only field |
Template | No | NA | A read-only field |
Timezone | No | NA | A read-only field |
Launch On | No | NA | A read-only field |
Launch Time | No | NA | A read-only field |
End On | No | NA | A read-only field |
End Time | No | NA | A read-only field |
Source Code | No | NA | A read-only field |
Country | No | Country Id | A read-only field |
Marketing Medium | No | Marketing Medium Id | For some schedules, such as Advertising schedules, this is a mandatory field, and hence must be included |
Objective | No | NA | A read-only field |
Current Status | No | NA | A read-only field |
Update Status To | No | NA | - |
Schedule Flexfields | No | NA | - |
Channel Based Flexfields | No |
|
- |
Intent To Submit for Approval | No | NA | - |
The following table provides guidelines for including fields in custom layouts based on the Marketing - Media Planner Integrator.
Fields | Required? | Additional Fields to be Included | Remarks |
---|---|---|---|
Spreadsheet Reference | NA | NA |
|
Schedule Name | Yes |
|
- |
Campaign | No | Campaign Id |
|
Channel | No | Channel Id |
|
Coordinator | No | Coordinator Id | A read-only field |
Template | No | NA | A read-only field |
Timezone | No | NA | A read-only field |
Launch On | No | NA | A read-only field |
Launch Time | No | NA | A read-only field |
End On | No | NA | A read-only field |
End Time | No | NA | A read-only field |
Source Code | No | NA | A read-only field |
Country | No | Country Id | A read-only field |
Marketing Medium | No | Marketing Medium Id | For some schedules, such as Advertising schedules, this is a mandatory field, and hence must be included |
Status | No | NA | A read-only field |
Purpose | No | NA | A read-only field |
Channel Based Flexfields | No | Channel Based Flexfield Context |
|
Primary Product Category | No | Primary Product | A read-only field |
Primary Product | No | Primary Product Category | A read-only field |
Metric 1 - Manual Fixed | No |
|
- |
Metric 2 - Manual Fixed | No |
|
- |
Metric 3 - Manual Fixed | No |
|
- |
Metric 4 - Manual Fixed | No |
|
- |
Metric 5 - Manual Fixed | No |
|
- |
Metric 6 - Manual Fixed | No |
|
- |
Metric 7 - Manual Fixed | No |
|
- |
Metric 8 - Manual Fixed | No |
|
- |
Metric 9 - Manual Fixed | No |
|
- |
Metric 10 - Manual Fixed | No |
|
- |
Metric 1 - Manual Variable | No |
|
- |
Metric 2 - Manual Variable | No |
|
- |
Metric 3 - Manual Variable | No |
|
- |
Metric 4 - Manual Variable | No |
|
- |
Metric 5 - Manual Variable | No |
|
- |
Metric 1 - Function Fixed | No |
|
- |
Metric 2 - Function Fixed | No |
|
- |
Metric 3 - Function Fixed | No |
|
- |
Metric 4 - Function Fixed | No |
|
- |
Metric 5 - Function Fixed | No |
|
- |
Metric 6 - Function Fixed | No |
|
- |
Metric 7 - Function Fixed | No |
|
- |
Metric 8 - Function Fixed | No |
|
- |
Metric 9 - Function Fixed | No |
|
- |
Metric 1 - Function Variable | No |
|
- |
Metric 2 - Function Variable | No |
|
- |
Metric 3 - Function Variable | No |
|
- |
Metric 4 - Function Variable | No |
|
- |
Metric 5 - Function Variable | No |
|
- |