Setting Up Web ADI

This chapter covers the following topics:

• Web ADI overview
• Web ADI Layouts
• Prerequisites for Using Web ADI
• General Guidelines for Using Web ADI Layouts
• Seeded Layouts
• Security Requirements for Web ADI
• Implementing Web ADI Integration

Web ADI overview

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.

Web ADI Layouts

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.

Prerequisites for Using Web ADI

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:

1. In Excel, go to Tools > Macro > Security > Trusted Sources.

2. 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:

1. Navigate to Tools > Internet Options > Security > Custom Level.

2. Set the "Initialize and script ActiveX controls not marked as safe" option to "Prompt".

• Refer to the Web ADI documentation for more information.

General Guidelines for Using Web ADI Layouts

Refer to the Oracle Marketing User Guide to know the general guidelines for using Web ADI layouts.

Seeded 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.

Security Requirements for Web ADI

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.

Implementing Web ADI Integration

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:

• Implementing Web ADI

• Imlementing Web ADI Responsibility

• Concurrent Programs for Web ADI Integration

• Creating Custom Layouts

Implementing Web ADI

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.

Implementing Web ADI Responsibility

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.

Concurrent Programs for Web ADI Integration

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.

Creating Custom Layouts

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:

Recommendations for Custom Import Layouts

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	Repeat Frequency UOM Repeat For	-
Schedule Flexfields	No	NA	-
Channel Based Flexfields	No	Channel Based Flexfield Context	-
Product Category 1	No	Product 1 Product Category Id 1 Product Id 1	-
Product Category 2	No	Product 2 Product Category Id 2 Product Id 2	-
Product 1	No	Product Category 1 Product Category Id 1 Product Id 1	-
Product 2	No	Product Category 2 Product Category Id 2 Product Id 2	-
Cover Letter	No	Cover Letter Id	-
Any Web Planner field	No	Web Planner - Application Web Planner - Placement Web Planner - Placement Id Web Planner - Content Item Web Planner - Content Item Id CTD - Action CTD - URL CTD - Parameter 1 CTD - Parameter 2 CTD - Parameter 3 CTD - Parameter Id 1 CTD - Parameter Id 2 CTD - Parameter Id 3 If you are using customizable Web scripts, then include the following fields: CTD - Additional Parameter 1 CTD - Additional Parameter 1 - Value CTD - Additional Parameter 2 CTD - Additional Parameter 2 - Value CTD - Additional Parameter 3 CTD - Additional Parameter 3 - Value CTD - Additional Parameter 4 CTD - Additional Parameter 4 - Value CTD - Additional Parameter 5 CTD - Additional Parameter 5- Value	Including the Web Planner - Title field is optional Other than the Web Planner - Title field, either include all the Web Planner fields or none
Any Landing Page URL field	No	Landing Page URL Additional Parameter CTD - Action CTD - URL CTD - Parameter 1 CTD - Parameter 2 CTD - Parameter 3 CTD - Parameter Id 1 CTD - Parameter Id 2 CTD - Parameter Id 3 If you are using customizable Web scripts, then include the following fields: CTD - Additional Parameter 1 CTD - Additional Parameter 1 - Value CTD - Additional Parameter 2 CTD - Additional Parameter 2 - Value CTD - Additional Parameter 3 CTD - Additional Parameter 3 - Value CTD - Additional Parameter 4 CTD - Additional Parameter 4 - Value CTD - Additional Parameter 5 CTD - Additional Parameter 5- Value	-
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.

Recommendations for Custom Update Layouts

Field Name	Required?	Additional Fields to be Included	Remarks
Spreadsheet Reference	NA	NA	A read-only field in the Header region that displays a system generated value Value is passed to the concurrent program during the Update process
Schedule Name	Yes	Schedule Id Object version Number	-
Campaign	No	Campaign Id	A read-only field If you include it in the layout and try to set it to Null, it will retain its previous value
Channel	No	Channel Id	A read-only field Include this field if you want to include the field Channel Based Flexfields
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	Channel Channel Based Flexfield Context	-
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.

Recommendations for Custom Media Planner Layouts

Fields	Required?	Additional Fields to be Included	Remarks
Spreadsheet Reference	NA	NA	A read-only field in the Header region that displays a system generated value Value is passed to the concurrent program during the Update process
Schedule Name	Yes	Schedule Id Object version Number	-
Campaign	No	Campaign Id	A read-only field If you include it in the layout and try to set it to Null, it will retain its previous value
Channel	No	Channel Id	A read-only field Include this field if you want to include the field Channel Based Flexfields
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	A read-only field If you include this field, also include the field "Channel"
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 1 - Manual Fixed Forecast Metric 1 - Manual Fixed Currency	-
Metric 2 - Manual Fixed	No	Metric 2 - Manual Fixed Forecast Metric 2 - Manual Fixed Currency	-
Metric 3 - Manual Fixed	No	Metric 3 - Manual Fixed Forecast Metric 3 - Manual Fixed Currency	-
Metric 4 - Manual Fixed	No	Metric 4 - Manual Fixed Forecast Metric 4 - Manual Fixed Currency	-
Metric 5 - Manual Fixed	No	Metric 5 - Manual Fixed Forecast Metric 5 - Manual Fixed Currency	-
Metric 6 - Manual Fixed	No	Metric 6 - Manual Fixed Forecast Metric 6 - Manual Fixed Currency	-
Metric 7 - Manual Fixed	No	Metric 7 - Manual Fixed Forecast Metric 7 - Manual Fixed Currency	-
Metric 8 - Manual Fixed	No	Metric 8 - Manual Fixed Forecast Metric 8 - Manual Fixed Currency	-
Metric 9 - Manual Fixed	No	Metric 9 - Manual Fixed Forecast Metric 9 - Manual Fixed Currency	-
Metric 10 - Manual Fixed	No	Metric 10 - Manual Fixed Forecast Metric 10 - Manual Fixed Currency	-
Metric 1 - Manual Variable	No	Metric 1 - Manual Variable Forecast Unit Metric 1 - Manual Variable Currency Metric 1 - Manual Variable Actual	-
Metric 2 - Manual Variable	No	Metric 2 - Manual Variable Forecast Unit Metric 2 - Manual Variable Currency Metric 2 - Manual Variable Actual	-
Metric 3 - Manual Variable	No	Metric 3 - Manual Variable Forecast Unit Metric 3 - Manual Variable Currency Metric 3 - Manual Variable Actual	-
Metric 4 - Manual Variable	No	Metric 4 - Manual Variable Forecast Unit Metric 4 - Manual Variable Currency Metric 4 - Manual Variable Actual	-
Metric 5 - Manual Variable	No	Metric 5 - Manual Variable Forecast Unit Metric 5 - Manual Variable Currency Metric 5 - Manual Variable Actual	-
Metric 1 - Function Fixed	No	Metric 1 - Function Fixed Forecast Metric 1 - Function Fixed Currency	-
Metric 2 - Function Fixed	No	Metric 2 - Function Fixed Forecast Metric 2 - Function Fixed Currency	-
Metric 3 - Function Fixed	No	Metric 3 - Function Fixed Forecast Metric 3 - Function Fixed Currency	-
Metric 4 - Function Fixed	No	Metric 4 - Function Fixed Forecast Metric 4 - Function Fixed Currency	-
Metric 5 - Function Fixed	No	Metric 5 - Function Fixed Forecast Metric 5 - Function Fixed Currency	-
Metric 6 - Function Fixed	No	Metric 6- Function Fixed Forecast Metric 6 - Function Fixed Currency	-
Metric 7 - Function Fixed	No	Metric 7- Function Fixed Forecast Metric 7 - Function Fixed Currency	-
Metric 8 - Function Fixed	No	Metric 8- Function Fixed Forecast Metric 8 - Function Fixed Currency	-
Metric 9 - Function Fixed	No	Metric 9- Function Fixed Forecast Metric 9 - Function Fixed Currency	-
Metric 1 - Function Variable	No	Metric 1 - Function Variable Forecast Unit Metric 1 - Function Variable Currency	-
Metric 2 - Function Variable	No	Metric 2 - Function Variable Forecast Unit Metric 2 - Function Variable Currency	-
Metric 3 - Function Variable	No	Metric 3 - Function Variable Forecast Unit Metric 3 - Function Variable Currency	-
Metric 4 - Function Variable	No	Metric 4 - Function Variable Forecast Unit Metric 4 - Function Variable Currency	-
Metric 5 - Function Variable	No	Metric 5 - Function Variable Forecast Unit Metric 5 - Function Variable Currency	-