Demantra Predictive Trade Planning to Siebel Integration

This chapter provides detailed information about how Demantra Predictive Trade Planning (PTP) integrates with Siebel Trade Promotion Management (TPM).

This chapter covers the following topics:

• Introduction
• Architectural Process
• Integration Point Overview
• Business Process
• Setup Overview
• Siebel Setup
• ODI Setup
• Demantra Setup
• Main Batch File Configurations
• Running Integration
• Siebel to Demantra ODI Packages and Batch Files
• Demantra to Siebel ODI Packages and Batch Files

Introduction

To date, most consumer goods companies have focused on gaining control over spending and achieving administrative efficiency in promotion execution—what is referred to as Foundational Trade Promotion Management (TPM). This is a critical first step, but only the beginning of achieving the full potential benefits of automating the TPM process. This foundation provides the transactional data to learn from historical information and apply this learning to improve future sales and operations performance.

Oracle Siebel TPM provides a robust, mature solution to support Foundational TPM. This includes functionality related to planning and executing trade promotions, managing funds, planning volumes, and managing deductions.

The next level, TPM Intelligence, refers to a set of analytic, predictive modeling and optimization capabilities that enable consumer goods companies to plan more profitable promotions resulting in improved sales performance.

Oracle Demantra Predictive Trade Planning (PTP) provides the TPM Intelligence. It includes a promotion simulation capability to predict the lift, incremental volume, and profitability of promotions before they are executed. The advanced promotional modeling engine determines the true net lift achieved by decomposing the promotional lift into cannibalization, halo effect, pre and post activity, and brand switching. The optional Promotion Optimization add-on provides the ability to automatically identify the best promotion to run based on identified goals and constraints.

By integrating the Oracle Siebel TPM application with Demantra PTP, the Siebel TPM solution is enhanced with volume intelligence, promotion intelligence, advanced account planning, and promotion optimization.

There are two scenarios for using Siebel TPM and Demantra PTP together. They are:

• Promotion planning done in Siebel with Demantra running in the background. The Demantra baseline forecast and lift coefficients are transferred into Siebel on a regular basis so that this information is available during the promotion planning process.

• Promotion planning done in either Siebel or Demantra with the results synchronized every 30 minutes. The user has the ability to add, modify, and delete promotions in either application and have the changes synchronized to the other application in a near real-time fashion.

The Demantra-Siebel integration includes a set of interfaces, pre-seeded workflows, related hierarchy levels and data series.

Architectural Process

The integration between Oracle Siebel and Oracle Demantra is a batch-based approach utilizing database staging tables to exchange information. It is accomplished through the use of Siebel’s Enterprise Integration Manager, the Oracle Data Integrator (ODI) to transform the data, and Oracle Demantra integration interfaces and workflows in the following diagram:

The integration processes can be scheduled on a nightly or weekly basis for high-volume integration points such as accounts, products, and shipment data. On-going integration processes can provide near real-time synchronization of promotional plans. For example, you can specify promotions that you want transferred to Demantra every 30 minutes for near real-time integration.

Integration Point Overview

The following integration points are part of the integration between the Oracle Demantra PTP module and Oracle Siebel applications.

The following table details the frequency and recommended load for each integration point is detailed below:

Interface	Flow	Initial Load	On-going Load	On-going Frequency
Account Planning Hierarchy	Siebel to Demantra	Full Load	Full Load	Nightly
Product Planning Hierarchy	Siebel to Demantra	Full Load	Full Load	Nightly
Promotion Tactics Master List	Siebel to Demantra	Full Load	N/A	N/A
Promotion Status Master	Siebel to Demantra	Full Load	N/A	N/A
Price and Cost	Siebel to Demantra	Future Load (18 months)	Full Load	Nightly
Sales Activity	Siebel to Demantra	Historical Load (2 years)	Incremental Load	Nightly
Plan Master List	Siebel to Demantra	Historical Load (2 years)	Incremental Load	Nightly
Promotions	Siebel to Demantra	Historical Load (2 years)	Incremental Load	Nightly
Promotions	Siebel to Demantra	Historical Load (2 years)	Selected Promotions	Every 30 Minutes
Promotions	Demantra to Siebel	N/A	Selected Promotions	Every 30 Minutes
Baseline Volume Forecast	Demantra to Siebel	Future Load (18 months)	Full Load	Weekly
Promotional Lift Coefficients	Demantra to Siebel	Future Load (18 months)	Full Load	Weekly

Business Process

There are two scenarios for using Siebel TPM and Demantra PTP together. They are:

• Siebel Trade Planning/Demantra Intelligence (Regular Synchronization)

• Demantra PTP/Siebel TM (Near Real-Time and Regular Synchronization)

Siebel Trade Planning/Demantra Intelligence Business Process (Regular Synchronization)

All promotion planning is done in Siebel and the Demantra user interface is not accessed. Regularly scheduled processes generate a baseline forecast and lift coefficients in Demantra and transfer this information to Siebel for use during the promotion planning process.

1. The Sales Account Manager logs into Siebel and creates a new promotion. He specifies the account and the associated products/promoted product groups. He also enters all the relevant details associated with the promotion such as tactics and cost components.

2. The Sales Account Manager initiates the “Baseline” process in Siebel to populate the baseline volume for the promotion. Note that the Demantra generated baseline, which has already been loaded into Siebel, is used.

3. The Sales Account Manager initiates the “Simulate” process in Siebel to retrieve and apply lift coefficients for the promotion. Note that the Demantra generated lift coefficients, which have already been loaded into Siebel, are used.

4. The Sales Account Manager finalizes the promotion and submits it for approval.

Nightly Extract Transfers

The following interfaces are required for synchronization:

• Account Planning Hierarchy (Siebel to Demantra)

• Product Planning Hierarchy (Siebel to Demantra)

• Sales Activity (Siebel to Demantra)

• Price and Cost (Siebel to Demantra)

• Plan Master List (Siebel to Demantra)

• Promotions (Siebel to Demantra)

Weekly Extract Transfers

The following interfaces are required for synchronization:

• Baseline Volume Forecast (Demantra to Siebel)

• Promotional Lift Coefficients (Demantra to Siebel)

Demantra Predictive Trade Planning/Siebel Trade Management (Near Real-time and Regular Synchronization)

Promotion planning is done using both Siebel and Demantra. The user has the ability to add and modify promotions in either application and to automatically have the changes synchronized to the other application in a near real-time fashion.

There are two options:

• Siebel is the primary promotion planning application.

• Demantra is the primary promotion planning application.

Siebel Primary Promotion Planning Application

The following is the business process when Siebel is preferred as the primary promotion planning application:

1. The Sales Account Manager logs into Siebel and selects an account plan. He adds new promotions to the plan and modifies existing promotions.

2. For any promotions that the Sales Account Manager wants to work on in Demantra, he changes the promotion status to “Send to Demantra”. At this point the promotions are automatically locked for editing in Siebel.

3. An automated scheduled process is running on a pre-set interval (for example, every 30 minutes) that exports all promotions in the “Send to Demantra” status from Siebel and sends them to Demantra for processing. During this process, the following occurs:

   • The status of the promotions are changed from “Send to Demantra” to “Locked by Demantra” in Siebel (the promotions remain locked for editing in Siebel).

   • If the promotion does not already exist in Demantra then it is created in Demantra.

   • If the promotion already exists in Demantra then it is updated in Demantra based on the information in Siebel.

   • The promotion transfer status in Demantra is set to “Unlocked” for each of these promotions allowing them to be edited in Demantra.

4. Once the transfer is complete, the Sales Account Manager logs into Demantra and runs simulations, optimizations, and lift decompositions on the transferred set of promotions. The Sales Manager can also modify the promotions (for example, change timing, tactics, price points) and add new promotions.

5. When finished, the Sales Account Manager changes the promotion transfer status in Demantra to “Transfer to Siebel”. At this point the promotions are automatically locked for editing in Demantra.

6. An automated scheduled process is running on a pre-set interval (for example, every 30 minutes) which exports all promotions where the promotion transfer status is “Transfer to Siebel” from Demantra and sends them to Siebel for processing:

   • The promotion transfer status of the promotions is changed from “Transfer to Siebel” to “Locked” in Demantra (the promotions remain locked for editing in Demantra).

   • If the promotion does not already exist in Siebel then it is created in Siebel.

   • If the promotion already exists in Siebel then it is updated in Siebel based on the information in Demantra.

   • All of these promotions are unlocked for editing in Siebel.

7. The Sales Account Manager reviews the promotions in Siebel and modifies the funding information if needed.

8. The Sales Account Manager finalizes the promotions in Siebel and submits for approval.

Demantra Primary Promotion Planning Application

The following is the business process when Demantra is preferred as the primary promotion planning application:

1. The Sales Account Manager logs into Demantra and creates a new set of promotions.

2. The Sales Account Manager runs simulations, optimizations, and lift decompositions on the set of promotions.

3. When finished, the Sales Account Manager changes the promotion transfer status in Demantra to “Transfer to Siebel”. At this point the promotions are automatically locked for editing in Demantra.

4. An automated scheduled process is running on a pre-set interval (for example, every 30 minutes) which exports all promotions where the promotion transfer status is “Transfer to Siebel” from Demantra and sends them to Siebel for processing:

   • The promotion transfer status of the promotions is changed from “Transfer to Siebel” to “Locked” in Demantra (the promotions remain locked for editing in Demantra).

   • If the promotion does not already exist in Siebel then it is created in Siebel.

   • If the promotion already exists in Siebel then it is updated in Siebel based on the information in Demantra.

   • All of these promotions are unlocked for editing in Siebel.

5. The Sales Account Manager reviews the promotions in Siebel and modifies the funding information if needed.

6. The Sales Manager finalizes the promotions in Siebel and submits for approval.

Nightly Extract Transfers

The following interfaces are required for synchronization:

• Customer Account Hierarchy (Siebel to Demantra)

• Product Hierarchy (Siebel to Demantra)

• Sales (Siebel to Demantra)

• Pricing and Cost (Siebel to Demantra)

• Promotional Plan Master (Siebel to Demantra)

• Promotions (Siebel to Demantra)

Weekly Extract Transfers

The following interfaces are required for synchronization:

• Future Baseline Volume Forecast (Demantra to Siebel)

• Future Lift Coefficients (Demantra to Siebel)

Near Real-Time Extract Transfers

The following interfaces are required for near real-time integration:

• Promotions (Siebel to Demantra) – every 30 minutes

• Promotions (Demantra to Siebel) – every 30 minutes

Setup Overview

This chapter provides an overview of the installation checklist and discusses how to install and configure the software components necessary for the integration of Oracle Siebel with Oracle Demantra.

Prerequisites

Verify your software versions before installing and configuring Oracle Siebel to integrate with:

• Oracle Demantra version 7.2.1 or later

• Siebel version 7.5 or later

• Oracle Data Integrator version 10.1.3.4.0 or later

• JAVA JDK version 1.5 or later

Setup Steps

The following steps are required to set up the integration between Oracle Demantra and Oracle Siebel.

Step	Instruction
1	Install Demantra version 7.2.1 or later.
2	Install Siebel version 7.5 or later.
3	Install Oracle Data Integrator version 10.1.3.4.0 or later.
4	Install Oracle Demantra Integration Pack for Siebel CRM Consumer Goods 1.0. See the Oracle Demantra Integration Pack for Siebel CRM Consumer Goods 1.0 Installation Guide for more information.
5	Configure Siebel application. See Siebel Setup in this guide.
6	Configure Oracle Data Integrator. See ODI Setup in this guide.
7	Configure Demantra. See Demantra Setup in this guide.
8	Align time units and week start day between Siebel and Demantra. The base time unit is week starting Mondays by default. It is recommended that you use weekly time units and align the week start day to match the associated Siebel implementation.
9	If users will be using both Siebel and Demantra to plan promotions, add new users to both Demantra and Siebel and assign permissions.
10	Load the following extracts: Account Planning Hierarchy (Siebel to Demantra) Product Planning Hierarchy (Siebel to Demantra) Promotional Tactics Master List (Siebel to Demantra) Promotion Status (Siebel to Demantra) Pricing and Cost (Siebel to Demantra) Sales Activity (Siebel to Demantra) Plan Master List (Siebel to Demantra) Promotions (Siebel to Demantra)
11.	Run the Demantra Forecasting Engine to generate the baseline forecast and lift coefficients before running the Demantra-Siebel extracts.
12.	Run the following extracts: Baseline Volume Forecast (Demantra to Siebel) Promotional Lift Coefficients (Demantra to Siebel) Promotions (Demantra to Siebel)
13	Set scheduler to run nightly, weekly and near real-time integration extracts as desired.

Siebel Setup

The following configurations need to be made in Siebel to support integration with Demantra:

• Configuring Campaign_State values

• Configuring State models

• Record locking requirements

• Other data required for integration

Configuring Campaign_State Values

Lists of values (LOVs) are sets of values that populate static pick lists from which a user can select when using Siebel CRM. The values in the LOV are stored as records in the database (S_LST_OF_VAL). They are grouped together using the Type field. For example, the values of LOV that appear in the Status field of the Account Entry Applet all have a Type value of ACCOUNT_STATUS. (List of configurations necessary)

To configure Campaign_State values:

1. Log in to Siebel as an administrator.

2. Click Sitemap (Globe icon on toolbar).

3. Select Administration - Data.

4. Select List of Values. A list of all the value types appears.

5. Select New and create the following value:

   • Type: CAMPAIGN_STATUS

   • Display Value: Locked by Demantra.

6. Select New and create the following value:

   • Type: CAMPAIGN_STATUS

   • Display Value: Send to Demantra

7. From the Menu drop-down list, select Save Record.

8. Click Clear Cache to refresh.

Configuring State Models

If you use a State Model to control the Promotion Status transitions, then you will need to add the new Campaign State values to this State Value. The “Send to Demantra” and “Locked by Demantra” should be added as Transitions to the appropriate State Model (for example, “Plan Account Promotion Status”). To do this, follow the steps below:

1. Log in to Siebel as an administrator.

2. Click Sitemap (the Globe icon on the toolbar).

3. Select Administration - Application.

4. Select State Models.

5. Query for Plan Account Promotion Status.

   

6. Select the Transitions tab and create new entries for the following statuses that users will use to send promotions to Demantra:

   From State	To State
   (Existing Status)	Send to Demantra
   Send to Demantra	Locked by Demantra

Record Locking

Implementors are responsible for creating a routine that locks records whose CAMPAIGN_STATUS=”Locked by Demantra”. This routine must be created for each implementation.

Other Data Required for the Integration

The following reference data must be created in Siebel before running the integration wit Demantra:

• Account types

• Product types

• Promotion status

• Promotional tactics

ODI Setup

The following sections describe the setup that needs to be made in ODI to support Demantra-Siebel integration:

• Overview of the ODI Applications

• Understanding Packages and Scenarios

• Configuring the Demantra Database Connection

• Configuring the Demantra Agent

• Configuring ODI Variables

Overview of the ODI Applications

Oracle Data Integrator (ODI) streamlines the high performance movement and transformation of data between heterogeneous systems in batch, real time, synchronous, and asynchronous modes; it dramatically enhances user productivity with an innovative, modularized design approach and built-in connectivity to all major databases, data warehouse appliances, analytic applications and SOA suites.

Oracle Data Integrator includes graphical modules and software agents that enable you to:

• Reverse engineer application models.

• Check data consistency.

• Design, test, operate, and maintain interfaces between applications.

• Check the data flows processed by the interfaces, with error isolation and recycling.

• Identify missing data input.

The following ODI modules are used with the Demantra-Siebel integration:

Agent: The Agent is a Java service that can be placed as listener on a TCP/IP port. This service allows:

• On demand execution of sessions (model reverses, packages, scenarios, interfaces, and so on) from Oracle Data Integrator modules. For this, you must launch a listener agent.

• Execution of scheduled scenarios, in addition to on demand executions. The physical agent contains an optional scheduler that allows scenarios to be launched automatically according to predefined schedules. For this, you must launch a scheduler agent. A physical agent can delegate executions to other physical agents. This process enables load balancing between agents.

Designer: Through the Designer module, you can handle:

• Models: Descriptions of the data and applications structures

• Projects: The developments made with Designer

The Designer module stores this information in a work repository, while using the topology and the security information defined in the master repository.

Operator: Through the Operator module, you can manage:

• Interface executions in the sessions.

• Scenarios in production.

The Operator module stores this information in a work repository, while using the topology defined in the master repository.

Topology Manager: Through the Topology Manager module, you can manage:

• Topology of your information system

• Technologies and their data types

• Data servers linked to these technologies and the schemas they contain

• Contexts, the languages and the agents

• Repositories

The Topology module stores this information in a master repository. This information can be used by all the other modules.

Understanding Packages and Scenarios

The Demantra-Siebel integration extracts can be viewed and managed by the ODI Designer in two folders DEMANTRA_TO_SIEBEL and SIEBEL_TO_DEMANTRA. Within each folder, the associated extracts are shown as sub-folders such as Account Hierarchy Exports, Pricelist and Cost Exports, Product Hierarchy Exports and so on. Within each sub-folder, a variety of packages may be displayed for that sub-folder. For example, the Pricelist and Cost Exports show two packages: full and incremental.

Lastly, within each package, the ODI Designer displays the steps involved with the extraction and transformation process. The following illustration shows the run Account Hierarchy Full Load scenario steps:

Configuring the Demantra Database Connection

Using the Topology Manager, you can configure your database connection information, Java agent location, as well as your ODI work repository information.

To configure the Demantra database connection:

1. From the ODI Topology Manager, expand the Technologies folder.

2. Expand the Oracle folder.

3. Click on demantra.

4. On the Definition tab, set the following:

   • User: Schema user being used as the repository for Demantra data.

   • Password: Password for the Demantra schema.

5. If you are using a work schema, click the physical schema as shown:

   

6. On the Definition tab, set the following:

   • Schema (Work Schema): The schema user being used as the work repository for Demantra data.

7. Click Apply.

8. On the JDBC tab, set the following:

   • JDBC URL: The URL allowing you to connect to the data server. The JDBC URL format is jdbc:oracle:thin:@<host>:<port>:<sid>

   

9. Click Test to test the connection.

10. Click Apply.

Configuring the Demantra Agent

The Demantra Agent accepts and controls various job requests from the distributed ODI components. The agent is configured by the ODI Topology Manager. By default, the Demantra-Siebel Integration configures the agent on the Demantra application server machine, listening on port 20910. The agent can be moved to any machine that has ODI installed.

To configure the Demantra Agent:

1. From the ODI Topology Manager, expand the Agents folder.

2. Click the demantra_agent.

   

3. On the Definition tab, set the following:

   • Host: Network name or IP address of the machine the agent has been launched on.

   • Port: Listening port used by the agent. By default, this port is the 20910.

4. If the Agent is launched, click Test. A successful connection window should appear

Configuring ODI Variables

A number of variables must be set within ODI to support the Demantra-Siebel integration. Fortunately, most of the variables are set when the Oracle Demantra Integration Pack for Siebel CRM Consumer Goods 1.0 is installed. However, you may want to change some of the global ODI variables periodically. The following is a summary of the ODI variables used during Demantra-Siebel integration:

Variable Name	Description
APP_SERVER_URL	Used internally. Do not change.
DEFAULT_SCENARIOS_ID	Scenario ID with which Siebel promotions interfaced to Demantra are associated. Default is 142, the Scenario ID for the pre-seeded "Sandbox" scenario.
DEFAULT_TRANSFER_STAT_CODE	Code of the promotion transfer status with which promotions created in Siebel and interfaced to Demantra are associated. The default is “Default”.
DEM_SBL_HOME	The directory where the "Demantra Integration Pack for Siebel CRM Consumer Goods" is installed. This is set by the Demantra Integration Pack for Siebel CRM Consumer Goods Installer. For example: D:\ODI_DEM_SBL_HOME.
DEM_WORKFLOW_PASS	Password of user who maintains the Demantra-Siebel integration workflows. Set by the Demantra Integration Pack for Siebel CRM Consumer Goods installer. For example, ptp.
DEM_WORKFLOW_SCHEMA	Used internally. Do not change.
DEM_WORKFLOW_USER	User who maintains the Demantra-Siebel PTP integration workflows. Set by the Demantra Integration Pack for Siebel CRM Consumer Goods installer. For example, ptp.
DMTRA_PROMO_TO_SBL_STG	Used internally. Do not change.
EIM_BIN_DIR	The location of the Siebel Server executable file (srvrmgr.exe). For example, d:\sia8\client\bin.
EIM_ENTERPRISE	Siebel Enterprise server name. For example: SIEB78.
EIM_EXECUTABLE	The name of the Siebel Server executable file. The default is srvrmgr.exe.
EIM_GATEWAY	Siebel Gateway Name Server name. For example, wa2048.oracle.com.
EIM_IFB	Used internally. Do not change.
EIM_LOG_LEVEL	Sets logging of EIM jobs. For example, Traceflags=1, sqlflags=4, errorflags=3.
EIM_PASSWORD	Siebel password.
EIM_SERVER	Siebel server name.
EIM_SHELL	Command to start the shell. For Windows us cmd.exe /c. For Linux/UNIX, use sh –c.
EIM_USER	Siebel username.
INCR_PROCESS_DATE	The number of days prior to the current system date that the incremental promotion interface from Siebel uses to select promotions. Any promotion created or modified within this window is sent to Demantra. This variable is used by the incremental interfaces for shipments and price/cost. For example, 4.
INSERT_COUNT	Used internally. Do not change.
JOB_NAME	Used internally. Do not change.
LeadTime	Used internally. Do not change.
MAIL_FROM	Email address for notification result queries. Set by the Demantra Integration Pack for Siebel CRM Consumer Goods installer. For example, [e-mail].
MAIL_SERVER	Host or IP address that manages your email services. Set by the Demantra Integration Pack for Siebel CRM Consumer Goods installer. For example, [e-mail].
MAIL_TO	Email addresses for those notified of integration results. Separate multiple email addresses with a comma. Set by the Demantra Integration Pack for Siebel CRM Consumer Goods installer. For example, [e-mail].
MAX_FORE_SALES_DATE	Used internally. Do not change.
MIN_FORE_SALES_DATE	Used internally. Do not change.
NA	The value to be used for loading into Demantra when a value is not available from Siebel. The default is “N/A”.
PLAN_SUB_TYPE	Variable used to identify plans in the Siebel S_SRC table. For example, PLAN_ACCOUNT.
PROMO_CATG_SUB_TYPE	Variable used to identify promotions by category in S_SRC table. For example, PLAN_ACCOUNT_PROMOTION_CATEGORY.
PROMO_LOCKED_BY_DMTRA	The promotion status used to identify promotions that have been locked by Demantra. Default is "Locked by Demantra". This must match the CAMPAIGN_STATE List of Values entry.
PROMO_PROD_SUB_TYPE	Variable used to identify promotions by product in Siebel S_SRC table. For example, PLAN_ACCOUNT_PROMOTION_PRODUCT.
PROMO_SEND_TO_DMTRA	The promotion status used to identify promotions that have been flagged to be sent to Demantra. The default is "Send to Demantra". This must match the CAMPAIGN_STATE List of Values entry.
PROMO_STATUS_TYPE	Promotion status type used for Demantra-Siebel integration. For example, CAMPAIGN_STATUS.
PROMO_SUB_TYPE	Variable used to identify promotions in Siebel S_SRC table. For example, PLAN_ACCOUNT_PROMOTION.
TIME_PERIOD	Time bucket used for planning purposes. Needs to be aligned in both Siebel and Demantra. For example, Week.
TYPE_DIVISION	Siebel account type used for division level. For example, Division.
TYPE_PG	Siebel product type used for promoted product group level. For example, Promoted Group.
TYPE_SHIPTO	Siebel account type used for ship to level. For example, Ship To.
V_ACCOUNT	Used internally. Do not change.
V_PRODUCT	Used internally. Do not change.
LOGFILE	Name of Demantra-Siebel integration log file. This file is located on the ODI server. For example, Siebel_demantra_integration.log.
LOGPATH	Path where Demantra-Siebel integration log file located. This file is located on the ODI server. For example, Integration\logs.

To configure the ODI variables:

1. From the ODI Designer, view the Projects.

2. Expand the Variables group.

   

3. Select a variable from the group and change the definition displayed on the right.

4. Click Apply.

Demantra Setup

This is typically a one-time setup that can be done with a database tool such as SqlPlus, Benthic, or Toad.

The following shows the account field name mappings between Siebel and Demantra. The Demantra Reference refers to the Level in the Site Hierarchy. The Siebel Reference refers to the Account Type.

Map Code	Demantra Reference	Siebel Reference
1	Site	Ship To
2	Bill To	Bill To
3	Retailer	Division
4	Corporate	Corporate

The following shows the product field name mappings between Siebel and Demantra. The Demantra Reference refers to the Level in the Item Hierarchy. The Siebel Reference refers to the Product Type or Catalog Hierarchy.

Map Code	Demantra Reference	Siebel Reference
1	Item	Product
2	Promoted Group	Promoted Group
3	Brand	Brand
4	Product SubGroup	Product Sub Category
5	Product Group	Product Category
6	Product Line	Catalog
7	Product Status	Product Status

The PROMOTION_TYPES table is shown below with sample data. Promotional Tactics that are transferred from an external source must have their TACTIC ID and DISCOUNT fields manually configured. The TACTIC ID associates a pre-seeded set of Tactic Groups with the detailed Promotional Tactics, by referencing the TACTICS_GROUPS table shown further down. The DISCOUNT field contains the percentage discount of the promotion and should correspond to the label in the PROMOTION TYPE CODE field.

PROMOTION TYPE ID	PROMOTION TYPE CODE	PROMOTION TYPE DESC	LAST UPDATE DATE	TACTIC ID	DISCOUNT
0	Default	Default Promotion Type	---	1	0
1	None	No Promotion Type	3/12/2008 13:19	1	0
208	88-27XAW	FEATURE 5%	2/19/2008 21:09	3	5
204	88-27XAX	FEATURE 10%	2/19/2008 21:09	3	10
205	88-27XAY	FEATURE 15%	2/19/2008 21:09	3	15
206	88-27XAZ	FEATURE 20%	2/19/2008 21:09	3	20
207	88-27XB0	FEATURE 25%	2/19/2008 21:09	3	25
103	88-27XBD	DISPLAY 10%	2/19/2008 21:09	2	10
104	88-27XBE	DISPLAY 15%	2/19/2008 21:09	2	15
307	88-27XB4	F\&\D 5%	2/19/2008 21:09	4	5
302	88-27XB5	F\&\D 10%	2/19/2008 21:09	4	10
303	88-27XB6	F\&\D 15%	2/19/2008 21:09	4	15
304	88-27XB7	F\&\D 20%	2/19/2008 21:09	4	20
305	88-27XB8	F\&\D 25%	2/19/2008 21:09	4	25
306	88-27XB9	F\&\D 35%	2/19/2008 21:09	4	35
405	0V-FHYD	TPR 5%	2/19/2008 21:09	5	5
402	0V-FHYF	TPR 10%	2/19/2008 21:09	5	10
403	0V-FHYH	TPR 15%	2/19/2008 21:09	5	15
404	88-27XAT	TPR 20%	2/19/2008 21:09	5	20

The TACTICS_GROUPS table is shown below. It provides a pre-seeded list of Promotional Tactics Groups. Tactics groups 6-12 are placeholder tactics which can be used if additional tactic groups are desired beyond the preconfigured set.

TACTIC ID	TACTIC DESC
0	Default Tactics
1	No Tactics Defined
2	Display Only
3	Feature Only
4	Feature \&\ Display
5	TPR
6	Tactic 6
7	Tactic 7
8	Tactic 8
9	Tactic 9
10	Tactic 10
11	Tactic 11
12	Tactic 12

The Analytical Engine is configured to process promotions with an associated positive discount. The default discount for a promotion should be entered in the DISCOUNT field of PROMOTION_TYPE table. Promotion specific discount overrides can be done by entering both Shelf Price and Promoted Price for the promotion. A promotion without default or specific positive discount will be ignored by the engine.

Main Batch File Configurations

All the Demantra-Siebel integration extracts can be run from the Windows command shell. The batch routines are installed by the Oracle Demantra Integration Pack for Siebel CRM Consumer Goods 1.0 Installation process. By default, these batch routines are located in the <root>\ODI_DEM_SBL_Home\integration directory.

There are two key files that need to be customized for your environment:

• Runscenario.bat: Defines the variables used for running the ODI application and scenarios. All ODI package batch routines reference this batch routine.

• Runsrvrmgr.bat: Defines the variables used for launching the Siebel application and running the Enterprise Integration Manager. All the EIM_<package name> batch files reference this batch routine.

These batch routines must be defined before the integration extracts can be run successfully from the command shell.

Within the <root>\ODI_DEM_SBL_Home\integration directory, the extract batch routines are organized into two directories specifying the direction of the integration:

Directory	Extract Subdirectories
Demantra_Siebel	Baseline_Load Liftfactor_Load Promotion_Load
Siebel_Demantra	AccountLoad PricelistLoad ProductLoad PromotionDataLoad Full Incremental PromotionLevelLoad PromotionLock SalesHistoryLoad

Within each extract directory is a main batch file suffixed with _MAIN. This batch file has been designed to run all the extract packages in the correct order.

At the start and end of each extraction package procedure, email messages are sent to the system administrator. This information is also available in the log file and from within the ODI Operator module.

Configuring odiparams.bat

In order to run the integration batch files, you will need to edit the odiparams.bat file to add the following variable setting code. This file is located in the <ODI_HOME>\bin directory.

The following variables need to be set:

Variable Name	Description
ODI_SECU_DRIVER	Java Oracle Driver. For example, oracle.jdbc.driver.OracleDriver.
ODI_SECU_URL	JDBC URL set in the following format: jdbc:oracle:thin:@<host name>:<port>:<SID> For example, jdbc:oracle:thin@localhost:1521:chris
ODI_SECU_USER	ODI master schema. For example: ODI_DEM_SBL.
ODI_SECU_ENCODED_PASS	Encoded password for the ODI master schema. You can find the encoded password by running the following: <ODI_HOME>\bin\agent.bat encode <password>. For example, g.yHxWAOP2QcswgMddNepx9ot.
ODI_SECU_WORK_REP	Work repository schema. If no work repository is being used, it is the same as the master schema. For example, ODI_DEM_SBL.
ODI_USER	Encoded password for the ODI master repository. SUNOPSIS is the default password for the ODI master repository. You can find the encoded password by running the following: <ODI_HOME>\bin\agent.bat encode <password>. For example, 1234.

Configuring the runscenario.bat

From the <root>\ODI_DEM_SBL_Home\integration directory, configure the following variable:

Variable Name	Description
ODI_BIN_DIR	Directory where the ODI applications reside.

Configuring the runsrvrmgr.bat

From the <root>\ODI_DEM_SBL_Home\integration directory, configure the following variables:

Variable Name	Description
EIM_G	Siebel host location. For example, demsb15.us.oracle.com.
EIM_E	Siebel Enterprise server name. For example, Sieb78.
EIM_S	Siebel server name. For example, demsbl2.
EIM_U	Username to access the Siebel Enterprise Integration Manager. Setting this variable will set the username for all the IFB files.
EIM_P	Password to access the Siebel Enteprise Integration Manager. Setting this variable will sett the password for all the IFB files.

Running Integration

Once the Demantra-Siebel integration components have been configured and tested, the ODI scenarios can be run and scheduled. The process is as follows:

1. Start the Agent.

2. Run the scenarios using scheduled batch routines. Alternatively, scenarios can be scheduled using the ODI Operator.

3. View the progress of the scenarios, if desired.

4. Review the log file results if errors.

Launching the Listener Agent

The Agent is a Java TCP/IP service that can be placed as listener on a TCP/IP port. This agent works as follows:

• When it starts, it runs as a listener on its port.

• When an execution query arrives on the agent, it executes the tasks associated with the query then returns as a listener on the port.

Note: A listener agent has no scheduled executions, and does not handle scheduled scenarios.

To launch the listener agent:

From the <ODI_HOME>\bin directory, run the agent.bat file (or agent.sh in UNIX). The agent is then launched as listener.

See Oracle Data Integrator User’s Guide for more information.

Stopping an Agent

You can stop a listener, scheduler or web agent that is listening on a TCP/IP port through the agentstop command.

To stop an agent:

From the <ODI_HOME>\bin\ directory, run the agentstop.bat file (or agentstop.sh in UNIX).

The listening agent is stopped.

Important: For security reasons, it is only possible to stop an agent from the same machine as where the agent's process was started. It is not possible to stop a remote agent.

See Oracle Data Integrator User’s Guide for more information.

Launching a Scheduler Agent

The Agent is a Java TCP/IP service that can be used to execute scenarios using predefined schedules or on demand. This agent works as follows:

• When it starts, it connects to the work repository to get its scheduled executions (all the scenario schedules planned for it), then runs as listener on its port.

• When an execution query arrives on this agent, it executes the tasks associated with the query.

• When a scenario must be launched in accordance to the scheduled executions in memory, the agent executes it, then returns as listener on the port.

• The agent's scheduled executions can be updated at any time by clicking the scheduling update button in the physical agent window.

Note: A scheduler agent has all the functionality of a listener agent.

To launch a scheduler agent:

From the <ODI_HOME>\bin\ directory, run the agentscheduler.bat file (or agentscheduler.sh in UNIX).

The agent starts up and goes to retrieve its scheduled executions on the work repository.

See Oracle Data Integrator User’s Guide for more information.

Displaying Scheduling Information

The scheduling information enables you to visualize the agents' scheduled tasks.

Important: The scheduling information is retrieved from the Agent's schedule. The Agent must be started and its schedule refreshed in order to display accurate schedule information.

To display the scheduling information from the Operator:

1. Open the ODI Operator

2. Click Scheduling Information.

Status of Scenarios

When scenarios are being run, you can view their status in the ODI Operator at the session, step or task level.

There are six possible status values:

• Done: The session, step or task was executed successfully.

• Error: The session, step or task has terminated due to an error.

• Running: The session, step or task is being executed.

• Waiting: The session, step or task is waiting to be executed.

• Warning (Tasks only): The task has terminated in error, but because errors are allowed on this task, this did not stop the session.

• Queued (Sessions only) : The session is waiting for an agent to be available for its execution.

When terminated, a session takes the status of the last executed step (Done or Error). When terminated, the step, takes the status of the last executed task (Except if the task returned a Warning. In this case, the step takes the status Done).

Handling Errors

To analyze an error:

1. In the ODI Operator tree view, identify the session, the step and the task in error.

2. Double-click the Error marker on the task. The task window opens:

   • In the Execution tab, the return code and message give the error that stopped the session.

   • In the Definition tab, the failed order is displayed.

Logging

An email message is sent to the system administrator confirming the success of each scenario run. When there are errors, log files are available for review. The Demantra-Siebel integration log files are located in the <root>\ ODI_DEM_SBL_Home\integration\logs directory.

Siebel to Demantra ODI Packages and Batch Files

This section provides information about the Siebel to Demantra extract packages and associated batch files. They include:

• Account Planning Hierarchy

• Plan Master List

• Pricing and Cost

• Product Planning Hierarchy

• Promotions

• Promotion Status

• Promotional Tactics Master List

• Sales Activity

Account Hierarchy Planning

Siebel is the system of record for customer and account information. The Account Planning Hierarchy as defined in Siebel is sent to Demantra to populate the Customer Location Hierarchy in Demantra. This data is used in Demantra for planning promotions, creating baselines, and aggregating data.

Transformation Details

Customer types can differ for any given implementation based on the client requirements. Therefore, the SIEBEL_ACCOUNT_MAPPING table is designed to map the different Siebel customer types to Demantra entities. The table is preseeded as follows:

Required Data Element	Demantra Reference	Siebel Reference
Ship to Location	Site	Ship-to
Billed Location	Bill-to	Bill-to
Aggregate Customer	Retailer	Division
Corporate	Corporate	Corporate

See Demantra Setup for more information on configuring the SIEBEL_ACCOUNT_MAPPING table.

Mapping

Demantra Field	Type	Siebel Source Table	Siebel Source Field	Comments
Site Number	VARCHAR2(30)	S_ORG_EXT	OU_NUM	Data at the Ship – To Level
Site Name	VARCHAR2(100)	S_ORG_EXT	NAME	Data at the Ship – To Level
Site Description	VARCHAR2(255)	S_ORG_EXT	DESC_TEXT	Data at the Ship – To Level
Status	VARCHAR2(30)	S_ORG_EXT	CUST_STAT_CD	Data at the Ship – To Level
Bill to Number	VARCHAR2(30)	S_ORG_EXT	OU_NUM	Data at the Sold – To Level
Bill to Name	VARCHAR2(100)	S_ORG_EXT	NAME	Data at the Sold – To Level
Bill to Description	VARCHAR2(255)	S_ORG_EXT	DESC_TEXT	Data at the Sold – To Level
Retailer Number	VARCHAR2(30)	S_ORG_EXT	OU_NUM	Data at the Division level
Retailer Name	VARCHAR2(100)	S_ORG_EXT	NAME	Data at the Division level
Retailer Description	VARCHAR2(255)	S_ORG_EXT	DESC_TEXT	Data at the Division level
Corporate Number	VARCHAR2(30)	S_ORG_EXT	OU_NUM	Data at the Corporation level
Corporate Name	VARCHAR2(100)	S_ORG_EXT	NAME	Data at the Corporation level
Corporate Description	VARCHAR2(255)	S_ORG_EXT	DESC_TEXT	Data at the Corporation level
City	VARCHAR2(50)	S_ADDR_PER	CITY	Data at the Ship – To : Address Level
State	VARCHAR2(10)	S_ADDR_PER	STATE	Data at the Ship – To : Address Level
Country	VARCHAR2(30)	S_ADDR_PER	COUNTRY	Data at the Ship – To : Address Level
Zip Code	VARCHAR2(30)	S_ADDR_PER	ZIPCODE	Data at the Ship – To : Address Level

Business Rules

1. It is recommended that this process be scheduled on a regular interval (daily).

2. Customer Account Hierarchy is always a full load.

3. Customer and parent hierarchy details for customers with the site level type in Demantra are transferred from Siebel to Demantra. In the example above, only customers with the type Ship-to are moved from Siebel to Demantra.

4. When the Name or Description fields are not populated in Siebel, they are populated with the Number or Name fields of the corresponding levels.

5. If any of the corresponding levels are not available in Siebel, the administrator or implementation consultant needs to populate the parent hierarchy to the same hierarchy. For example, let us assume that Division doesn’t exist for a particular Siebel Implementation. The SIEBEL_ACCOUNT_MAPPING table needs to be mapped as follows:

Required Data Element	Demantra Reference	Siebel Reference
Ship to Location	Site	Ship-to
Billed Location	Bill-to	Bill-to
Aggregate Customer	Retailer	Corporate
Corporate	Corporate	Corporate

ODI Package and Batch File Details

ODI Package Name: run Account Hierarchy Full Load

ODI Scenario: ACCNT_FULL_LOAD

Recommended Scheduling Frequency: Daily

Directory of batch files: <root>\ODI_DEM_SBL_Home\integration\Siebel-Demantra\AccountLoad

Package Name	Description of Functionality
ACCNT_FULL_LOAD.cmd	Extracts the Customer Account Hierarchy data from Siebel and transfers to the Demantra staging table

Plan Master List

Siebel is the system of record for plan information. Demantra needs Siebel plan information so that promotions created in Demantra can be assigned to specific plans. Typically, a year’s worth of promotions for a particular category or brand are grouped into plans for ease of navigation and reporting.

Transformation Details

In Siebel, plans are specific to the accounts for which the promotions have been planned. Currently in Demantra, plans are associated with promotions irrespective of the account at which the promotion is created.

Mapping

Demantra Field	Type	Siebel Source Table	Siebel Source Field	Comments
Item Number	VARCHAR2(50)	S_PROD_INT	PART_NUM	Product / SKU / Item Number
Item Name	VARCHAR2(100)	S_PROD_INT	NAME	Product / SKU / Item Name
Item Description	VARCHAR2(255)	S_PROD_INT	DESC_TEXT	Product / SKU / Item Desc Empty if no data
Promoted Group Number	VARCHAR2(50)	S_PROD_INT	PART_NUM	Empty if not implemented in a project
Promoted Group Name	VARCHAR2(100)	S_PROD_INT	NAME	Empty if not implemented in a project
Promoted Group Description	VARCHAR2(255)	S_PROD_INT	DESC_TEXT	Empty if no data or not implemented in a project
Product Sub Category #	VARCHAR2(100)	S_CTLG_CAT	NAME	Category Number will be substituted - if no data or not implemented in a project
Product Sub Category Name	VARCHAR2(100)	S_CTLG_CAT	DISPLAY_NAME	Category Name will be substituted - if no data or not implemented in a project
Product Sub Category Description	VARCHAR2(250)	S_CTLG_CAT	DESC_TEXT	Category Desc will be substituted - if no data or not implemented in a project
Product Category #	VARCHAR2(100)	S_CTLG_CAT	NAME	---
Product Category Name	VARCHAR2(100)	S_CTLG_CAT	DISPLAY_NAME	---
Product Category Description	VARCHAR2(250)	S_CTLG_CAT	DESC_TEXT	Empty if no data or not implemented in a project
Brand	VARCHAR2(100)	S_CTLG_CAT	NAME	Empty if no data or not implemented in a project
Catalog Number	VARCHAR2(75)	S_CTLG	NAME	---
Catalog Name	VARCHAR2(250)	S_CTLG	DESC_TEXT	---
Primary Unit of Measure	VARCHAR2(30)	S_PROD_INT	UOM_CD	---
Item Status	VARCHAR2(30)	S_PROD_INT	STATUS_CD	---

Business Rules

1. It is recommended that this process be scheduled on a regular basis (daily).

2. Plan information is always a full load.

3. Plan information needs to be loaded after Account Hierarchy Planning.

ODI Package and Batch File Details

ODI Package Name: run Promotion Plan Exp

ODI Scenario: PROMO_PLAN_EXPORT

Recommended Scheduling Frequency: Full export daily

Directory of batch files: <root>\ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionLeveLoad\

Package Name	Description of Functionality
PROMO_PLAN_EXPORT.cmd	Extracts all promotion plans from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PLAN workflow in Demantra to populate Demantra’s data tables.

Pricing and Cost

Siebel is the system of record for manufacturer list price and manufacturer cost of goods sold. The manufacturer list price and manufacturer cost as stored in Siebel is sent to Demantra to populate cost and price information in Demantra. List price and cost information is used in Demantra for promotion evaluation such as promotion profitability.

Transformation Details

In Siebel, the price and cost information are stored as price lists. Even though Siebel allows a product to have more than one price for a given account at any given period, it is uncommon to have more than one product pricelist associated with an account for a specific time period.

In Demantra, prices are stored in the transaction table at the lowest level (site, item and week). Therefore, Siebel data based on effective dates is converted into multiple records in Demantra. For example, Siebel records product pricelist information for a date range as follows:

Product	Ship-To	Start Date	End Date	List Price	Manufacturer Cost
Fizz Cola 20 oz	ABCMart	1-1-2008	1-31-2008	1.00	0.85

This record is translated as follows in Demantra, assuming a weekly system with Sunday start date:

Product	Ship-To	Sales Date	List Price	Manufacturer Cost
Fizz Cola 20 oz	ABCMart	1-6-2008	1.00	0.85
Fizz Cola 20 oz	ABCMart	1-13-2008	1.00	0.85
Fizz Cola 20 oz	ABCMart	1-20-2008	1.00	0.85
Fizz Cola 20 oz	ABCMart	1-27-2008	1.00	0.85

Mapping

Demantra Field	Type	Siebel Source Table	Siebel Source Field
Product Name	VARCHAR2(100)	S_PROD_INT	PROD_NAME
Product Number	VARCHAR2(50)	S_PROD_INT	PART_NUM
Site Name	VARCHAR2(30)	S_ORG_EXT	NAME
Site Number	VARCHAR2(100)	S_ORG_EXT	OU_NUM
Shipment Volume	VARCHAR2(22)	S_PROD_SHIPMENT	ACTL_CASES
Unit of Measure	VARCHAR2(30)	S_PROD_SHIPMENT	PREF_UOM_CD
Start Date	DTYPE_DATE	S_PERIOD	START_DT
End Date	DTYPE_DATE	S_PERIOD	END_DT

Business Rules

1. It is recommended to schedule this package on a regular interval (daily).

2. Account, Product and Sales extracts need to be loaded before the Pricing and Cost extract.

3. Price and cost information are always loaded for the future 18 months. Each subsequent load overwrites previously loaded data

4. After the translation, if there are multiple records for a given product, ship-to customer and sales date, the record is consolidated into one record by taking the minimum of list price and maximum of manufacturer cost. The following records are consolidated into one record as shown:

Product	Ship-To	Sales Date	List Price	Manufacturer Cost
Fizz Cola 20 oz	ABCMart	1-6-2008	1.00	0.95
Fizz Cola 20 oz	ABCMart	1-6-2008	1.00	0.85
Fizz Cola 20 oz	ABCMart	1-6-2008	1.15	0.95

Consolidated Record:

Product	Ship-To	Sales Date	List Price	Manufacturer Cost
Fizz Cola 20 oz	ABCMart	1-6-2008	1.00	0.95

ODI Package and Batch File Details

ODI Package Names:

• run PriclistCost Incr Export

• run PriclistCost Full Export

ODI Scenarios:

• PRICELISTCOST_INCR_EXPORT

• PRICELISTCOST_FULL_EXPORT

Recommended Scheduling Frequency: Full export during setup; daily incremental export thereafter. Account, Product and Sales extracts need to be loaded before the Pricing and Cost extract.

Directory of batch files: <root>\ODI_DEM_SBL_Home\integration\Siebel-Demantra\PricelistLoad

Package Name	Description of Functionality
PRICLISTCOST_FULL_EXPORT.cmd	Extracts the full manufacturer list Price and manufacturer cost of goods sold data from Siebel. Launches the Demantra web server. Starts the IMPORT: LIST_PRICE_COST workflow in Demantra to populate Demantra’s data tables.
PRICLISTCOST_FULL_EXPORT.cmd	Extracts the full manufacturer list Price and manufacturer cost of goods sold data from Siebel. Launches the Demantra web server. Starts the IMPORT: LIST_PRICE_COST workflow in Demantra to populate Demantra’s data tables.
PRICLISTCOST_FULL_EXPORT.cmd	Extracts the full Manufacturer List Price and Manufacturer Cost of Goods Sold data from Siebel Launches the Demantra web server Starts the IMPORT: LIST_PRICE_COST workflow in Demantra to populate Demantra’s data tables.
PRICLISTCOST_INCR_EXPORT.cmd	Extracts any new or changed Siebel Manufacturer List Price and Manufacturer Cost of Goods Sold data since the last extraction. Launches the Demantra web server Starts the IMPORT: LIST_PRICE_COST workflow

Product Planning Hierarchy

Siebel is the system of record for product information. The product hierarchy as defined in Siebel is sent to Demantra to populate the product item hierarchy in Demantra. This data is used in Demantra for planning promotions, creating baselines, and aggregating data.

Transformation Details

The product types may vary for any given implementation based on the client requirements. A SIEBEL_PRODUCT_MAPPING table is maintained in Demantra which maps all the product types necessary for integration. It is preseeded as follows:

Required Data Element	Demantra Reference	Siebel Reference
Product – SKU	Item	Product
Promoted Product Group	Promoted Group	Promoted Group
Brand	Brand	Brand
Product Sub Category	Product SubGroup	Product Sub Category
Catalog	Product Line	Catalog
Product Status	Product Status	Product Status

See Demantra Setup for more information about the SIEBEL_PRODUCT_MAPPING table.

Mapping

Demantra Field	Type	Siebel Source Table	Siebel Source Field	Comments
Item Number	VARCHAR2(50)	S_PROD_INT	PART_NUM	Product / SKU / Item Number
Item Name	VARCHAR2(100)	S_PROD_INT	NAME	Product / SKU / Item Name
Item Description	VARCHAR2(255)	S_PROD_INT	DESC_TEXT	Product / SKU / Item Desc Empty if no data
Promoted Group Number	VARCHAR2(50)	S_PROD_INT	PART_NUM	Empty if not implemented in a project
Promoted Group Name	VARCHAR2(100)	S_PROD_INT	NAME	Empty if not implemented in a project
Promoted Group Description	VARCHAR2(255)	S_PROD_INT	DESC_TEXT	Empty if no data or not implemented in a project
Product Sub Category #	VARCHAR2(100)	S_CTLG_CAT	NAME	Category Number will be substituted - if no data or not implemented in a project
Product Sub Category Name	VARCHAR2(100)	S_CTLG_CAT	DISPLAY_NAME	Category Name will be substituted - if no data or not implemented in a project
Product Sub Category Description	VARCHAR2(250)	S_CTLG_CAT	DESC_TEXT	Category Desc will be substituted - if no data or not implemented in a project
Product Category #	VARCHAR2(100)	S_CTLG_CAT	NAME	---
Product Category Name	VARCHAR2(100)	S_CTLG_CAT	DISPLAY_NAME	---
Product Category Description	VARCHAR2(250)	S_CTLG_CAT	DESC_TEXT	Empty if no data or not implemented in a project
Brand	VARCHAR2(100)	S_CTLG_CAT	NAME	Empty if no data or not implemented in a project
Catalog Number	VARCHAR2(75)	S_CTLG	NAME	---
Catalog Name	VARCHAR2(250)	S_CTLG	DESC_TEXT	---
Primary Unit of Measure	VARCHAR2(30)	S_PROD_INT	UOM_CD	---
Item Status	VARCHAR2(30)	S_PROD_INT	STATUS_CD	---

Business Rules

1. It is recommended that this package be scheduled on a regular interval (daily).

2. Product Hierarchy is always a full load.

3. When the Name or Description fields are not populated in Siebel, they are populated with the Number or Name fields of the corresponding levels.

4. Only the products with the type corresponding to the item level in Demantra and associated parent hierarchy elements are selected to be sent to Demantra. In the example above, only products with the type Product will be moved from Siebel to Demantra. But each product record will have the hierarchy details based on the reference table.

5. If any of the corresponding levels are not available in Siebel, then during the mapping process, the administrator or implementation consultant needs to populate the parent hierarchy to the same hierarchy.

ODI Package and Batch File Details

ODI Package Name: run Product Hierarchy Full Load

ODI Scenario: PROD_FULL_LOAD

Recommended Scheduling Frequency: Daily

Directory of batch files: <root>\ODI_DEM_SBL_Home\integration\Siebel-Demantra\ProductLoad

Package Name	Description of Functionality
PROD_FULL_LOAD.cmd	Extracts the product data from Siebel and transfers it to the Demantra staging tables.

Promotion Status

Promotional status defines the status of the Promotion. The preconfigured values in Demantra are:

• a. Unplanned

• b. Planned

• c. Approved

• d. Committed

• e. Partially Paid

• f. Paid

• g. Closed

Siebel is the system of record for the master listing of promotional status values. Promotion status is a parent level to the promotion level in Demantra. The master listing of promotional statuses as defined in Siebel is sent to Demantra to populate the promotion status level in Demantra. All the pre-seeded Demantra statuses have been retained and the Siebel statuses added.

Note that Demantra provides the ability to take a “snapshot” of key promotional series to be used for planned vs. actual comparison. This snapshot functionality is hard-coded to the promotion status with the ID value of 4 (this is “d. Committed” in the pre-seeded values). During implementation, whatever status the snapshot should be taken at should be set to ID value 4.

As this definition doesn’t change often, this is typically a one time process that happens once per implementation. If changes are made to the promotional status master list in Siebel, then this interface can be run on-demand to update Demantra.

Business Rules

1. It is recommended that this process be run once and whenever the promotion status values change in Siebel.

2. Promotional status information is always a full load. It must be loaded before the Promotions extract.

Mapping

The Promotional Status data will be transformed from the Siebel table’s structures into the structure and format of the Demantra Staging/Interface tables. The required data elements are:

Required Data Element	Demantra Reference	Siebel Reference
Promotional Status ID	Promotional Status ID	Promotional Status ID
Promotional Status Description	Promotional Status Description	Promotional Status Description
Promotional Status Code	Promotional Status Code	Promotional Status Code

ODI Package and Batch File Details

Recommended Scheduling Frequency: Once or when needed.

ODI Package Name:run Promotion Status Export

Directory of batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromoLevelLoad

Package Name	Description of Functionality
PROMO_STATUS_EXPORT.cmd	Extracts the promotion status values from Siebel to Demantra.

Promotions

Promotion information is sent from Siebel to Demantra for three separate instances as follows:

1. Historical Load: Historical promotion information should be loaded into Demantra for the modeling engine to predict future promotions. Promotional history is typically for the past two years. If a subset of customers/products is configured, to be sent to Demantra, only the promotion history for this subset is sent to Demantra. Promotion history is usually loaded once for each implementation.

2. On-Going Nightly Update: On a nightly basis current promotional information can be loaded into Demantra to ensure that Demantra has current information for the calculation of baseline volume and lift coefficients. This extract is incremental and includes only promotions that have been created, modified, or deleted since the last incremental extract was drawn.

3. Near Real-Time Promotional Planning: Users can specify promotions to transfer from Siebel to Demantra by setting the Status of each promotion to “Send to Demantra”. It is recommended that a background process be scheduled to run every 30 minutes to load these promotions into Demantra. Only promotions in the “Send to Demantra” status are included. After the promotions have been transferred, the status is changed to “Locked by Demantra” and then the record is locked until it returns from Demantra.

Mapping

Demantra Field	Siebel Source Table	Siebel Source Field	Comments
Promotion Name	S_SRC	NAME	---
Promotion Number	S_SRC	PROMO_NUM	---
Promotion ID	S_SRC	ROW_ID	---
Promotion name	S-SRC	NAME	---
Promotion Status	S_SRC	STATUS_CD	---
Promotion Type	S_SRC	SRC_CD	---
Promotion St Dt	S_SRC	PROG_END_DT	---
Promotion End Dt	S_SRC	PROG_END_DT	---
Account Name	S_ORG_EXT	NAME	Join PR_ACCNT_ID of S_SRC with PAR_ROW_ID of S_ORG_EXT
Account Type	S_ORG_EXT	OU_TYPE_CD	Join PR_ACCNT_ID of S_SRC with PAR_ROW_ID of S_ORG_EXT
Plan	S_SRC	NAME	Join PAR_SRC_ID OF PROMOTION ROD_ID
Shipment Start Date	S_SRC	SHIP_START_DT	---
Shipment End Date	S_SRC	SHIP_END_DT	---
Product Name	S_PROD_INT	NAME	Join PROD_ID of S_SRC with ROW_ID of S_PROD_INT
Product Number	S_PROD_INT	PART_NUM	Join PROD_ID of S_SRC with ROW_ID of S_PROD_INT
Product Type	S_PROD_INT	TYPE	Join PROD_ID of S_SRC with ROW_ID of S_PROD_INT
Tactics	S_CG_PROMTACTIC	TACTICS_CD	Join SRC_ID of S_CG_PROMTACTIC with S_SRC ROW_ID
ACV %	S_SRC_CHNL	PROMO_ACV_PCT	Join ROW_ID of S_SRC with PAR_ROW_ID of S_SRC_CHNL
Off Invoice Allowance	S_SRC_CHNL	CMMT_XINV_CASERATE	---
Bill back Allowance	S_SRC_CHNL	CMMT_BILL_BACK	---
Scan Down	S_SRC_CHNL	CMMT_VAL_SCAN_RED	---
Fixed Cost	S_SRC_CHNL	CMMT_FIXED_COST	---

Business Rules

1. It is recommended that this process be run as follows:

   • On-demand for the initial history load including two years worth of promotional data.

   • Nightly as an automated scheduled process for an incremental load of all promotions created or modified in Siebel since the last update.

   • On-going during the day (every 30 minutes) for an incremental load of all promotions with the “Send to Demantra” status.

2. Each time the process for promotion data is run, a snapshot of the information for all promotion data elements is included.

3. The assumption is that the all of the data elements loaded using the promotion data interface are the same for all location members, if the promotion is associated to more than one location (Site, Retailer, Division).

4. The promotion transfer status in Demantra is set as follows for all promotions loaded from Siebel (including both new and modified promotions):

   • For the full load, the promotion transfer status is set to “Locked”.

   • For the nightly incremental load, the promotion transfer status is set to “Locked”.

   • For the near real-time load, the promotion transfer status is set to “Unlocked”.

5. For new promotions created in Siebel and sent to Demantra, any promotion attributes that are not specifically included in the feed from Siebel, should be set to the configured default value for the attribute. If no default value is specified, then 0 (zero) should be used. This includes the following promotion attributes:

   • Promotion Type

   • Integration Status

   • Scenario

   • Plan

ODI Package/Extract Overview

The Promotion Exports extract has been designed to support two product types and four account types. Implementations run only the one (or two) combinations that align to the way your promotions are planned in Siebel.

The product types are Product (SKU) and Promoted Product Group (a grouping of products typically promoted together). The account types are Ship-To Customer, Bill-To Customer, Customer Division, and Corporate Customer. Separate versions of the promotion extracts are provided for each possible combination as listed below:

• Product AND Bill-To Customer

• Product AND Corporate Customer

• Product AND Customer Division

• Product AND Ship-To Customer

• Promoted Product Group AND Bill-To Customer

• Promote d Product Group AND Corporate Customer

• Promoted Product Group AND Customer Division

• Promoted Product Group AND Ship-To Customer

There are three ODI Package groups:

• Promotion Exports Full

• Promotion Exports Incr

• Promotion Export NRT

Within each group, there are two packages:

• Promotion Level (which represents the header)

• Promotion Data (which represents the details)

Finally, the near real-time extraction process ends with the Siebel_Promotion_Lock procedure which changes the status of transferred promotions in Siebel to “Locked by Demantra”.

Important: After the status has been changed to “Locked by Demantra” in Siebel, it isn’t actually locked. The administrator or implementation consultant needs to create a procedure to truly lock these records in the database.

Recommended scheduling frequency:

• Full: On-Demand for the initial history load including two years worth of promotional data.

• Incremental: Nightly as an automated scheduled process for an incremental load of all promotions created or modified in Siebel since the last update.

• Near Real-time: Every 30 minutes suggested to transfer all promotions in the “Send to Demantra” status to Demantra.

Mapping

ODI Package and Batch File Details: Full Load

Full load packages:

Promotion Level	Promotion Data
Run PLevel Full Extract	Run PData Full Extract PRD-SHIP
Run PLevel Full Extract	Run PData Full Extract PRD-BILL
Run PLevel Full Extract	Run PData Full Extract PRD-DIV
Run PLevel Full Extract	Run PData Full Extract PRD-CORP
Run PLevel Full Extract	Run PData Full Extract PG-SHIP
Run PLevel Full Extract	Run PData Full Extract PG-BILL
Run PLevel Full Extract	Run PData Full Extract PG-DIV
Run PLevel Full Extract	Run PData Full Extract PG-CORP

Batch Details:

Directory of full promotion header batch file: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionLevelLoad

Package Name	Description of Functionality
PROMO_LEVEL_FULL_EXTRACT.cmd	Extracts the full set of promotion headers from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PROMOTION workflow

Directory of full promotion detail batch files: <root>\ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionDataLoad\FULL

Package Name	Description of Functionality
PDATA_FULL_EXTRACT_PG_BILL.cmd	Extracts the full set of promotion group data sorted by billing account from Siebel. Launches the Demantra web server. Starts the IMPORT:
PDATA_FULL_EXTRACT_PG_CORP.cmd	Extracts the full set of promotion group data sorted by corporate ID from Siebel. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PG_CORP workflow
PDATA_FULL_EXTRACT_PG_DIV.cmd	Extracts the full set of promotion group data sorted by division from Siebel. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PG_DIV workflow
PDATA_FULL_EXTRACT_PG_SHIP.cmd	Extracts the full set of promotions group data sorted by shipping account from Siebel. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PG_SHIP workflow
PDATA_FULL_EXTRACT_PRD_BILL.cmd	Extracts the full set of promotions data from Siebel organized by product and billing account. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PROD_BILL workflow
PDATA_FULL_EXTRACT_PRD_CORP.cmd	Extracts the full set of promotions data from Siebel organized by product and corporate account. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PROD_CORP workflow
PDATA_FULL_EXTRACT_PRD_DIV.cmd	Extracts the full set of promotions data from Siebel organized by product and division. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PROD_DIV workflow
PDATA_FULL_EXTRACT_PRD_SHIP.cmd	Extracts the full set of promotions data from Siebel organized by product and shipping account. Launches the Demantra web server. Starts the IMPORT: SIEBEL_PDATA_PROD_SHIP workflow

ODI Package and Batch File Details: Incremental Load

Incremental load packages

Promotion Level	Promotion Data
Run Promotion Level Incr Extract	Run PData Incr Extract PRD-SHIP
Run Promotion Level Incr Extract	Run PData Incr Extract PRD-BILL
Run Promotion Level Incr Extract	Run PData Incr Extract PRD-DIV
Run Promotion Level Incr Extract	Run PData Incr Extract PRD-CORP
Run Promotion Level Incr Extract	Run PData Incr Extract PG-SHIP
Run Promotion Level Incr Extract	Run PData Incr Extract PG-BILL
Run Promotion Level Incr Extract	Run PData Incr Extract PG-DIV
Run Promotion Level Incr Extract	Run PData Incr Extract PG-CORP
Run Promotion Level Incr Extract	Run PData Incr Extract PRD-SHIP

Incremental batch files:

Directory of incremental promotion header batch file: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionLevelLoad

Package Name	Description of Functionality
PROMO_LEVEL_INCR_EXTRACT.cmd	Extracts the incremental set of promotion headers from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PROMOTION workflow

Directory of incremental promotion details batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionDataLoad\INCR

Package Name	Description of Functionality
PDATA_INCR_EXTRACT_PG_BILL.cmd	Extracts an incremental set of promotion group data sorted by billing account from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_BILL workflow
PDATA_INCR_EXTRACT_PG_CORP.cmd	Extracts an incremental set of promotion group data sorted by corporate ID from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_CORP workflow
PDATA_INCR_EXTRACT_PG_DIV.cmd	Extracts an incremental set of promotion group data sorted by division from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_DIV workflow
PDATA_INCR_EXTRACT_PG_SHIP.cmd	Extracts an incremental set of promotion group data sorted by shipping account from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_SHIP workflow
PDATA_INCR_EXTRACT_PRD_BILL.cmd	Extracts an incremental set of promotions data from Siebel organized by product and billing account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_BILL workflow
PDATA_INCR_EXTRACT_PRD_CORP.cmd	Extracts an incremental set of promotions data from Siebel organized by product and corporate account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_CORP workflow
PDATA_INCR_EXTRACT_PRD_DIV.c	Extracts an incremental set of promotions data from Siebel organized by product and division Launches the Demantra web server· Starts the IMPORT: SIEBEL_PDATA_PROD_DIV workflow
PDATA_INCR_EXTRACT_PRD_SHIP.cmd	Extracts an incremental set of promotions data from Siebel organized by product and shipping account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_SHIP workflow

ODI Package and Batch File Details: Near Real-Time

Near Real-time Packages:

Promotion Level	Promotion Data
Run Promotion Level NRT Extract	run PData Incr Extract PRD-SHIP
Run Promotion Level NRT Extract	run PData Incr Extract PRD-BILL
Run Promotion Level NRT Extract	run PData Incr Extract PRD-DIV
Run Promotion Level NRT Extract	run PData Incr Extract PRD-CORP
Run Promotion Level NRT Extract	run PData Incr Extract PG-SHIP
Run Promotion Level NRT Extract	run PData Incr Extract PG-BILL
Run Promotion Level NRT Extract	run PData Incr Extract PG-DIV
Run Promotion Level NRT Extract	run PData Incr Extract PG-CORP
Run Promotion Level NRT Extract	run PData Incr Extract PRD-SHIP

Near real-time batch files:

Directory of incremental promotion header batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionLevelLoad

Package Name	Description of Functionality
PROMO_LEVEL_NRT_EXTRACT.cmd	Extracts the promotions with the status “Send to Demantra” from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PROMOTION workflow

Directory of incremental promotion details batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromotionDataLoad\INCR

Package Name	Description of Functionality
PDATA_INCR_EXTRACT_PG_BILL.cmd	Extracts an incremental set of promotion group data sorted by billing account from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_BILL workflow
PDATA_INCR_EXTRACT_PG_CORP.cmd	Extracts an incremental set of promotion group data sorted by corporate ID from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_CORP workflow
PDATA_INCR_EXTRACT_PG_DIV.cmd	Extracts an incremental set of promotion group data sorted by division from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_DIV workflow
PDATA_INCR_EXTRACT_PG_SHIP.cmd	Extracts an incremental set of promotion group data sorted by shipping account from Siebel Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PG_SHIP workflow
PDATA_INCR_EXTRACT_PRD_BILL.cmd	Extracts an incremental set of promotions data from Siebel organized by product and billing account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_BILL workflow
PDATA_INCR_EXTRACT_PRD_CORP.cmd	Extracts an incremental set of promotions data from Siebel organized by product and corporate account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_CORP workflow
PDATA_INCR_EXTRACT_PRD_DIV.cmd	Extracts an incremental set of promotions data from Siebel organized by product and division Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_CORP workflow
PDATA_INCR_EXTRACT_PRD_DIV.cmd	Extracts an incremental set of promotions data from Siebel organized by product and division Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_DIV workflow
PDATA_INCR_EXTRACT_PRD_SHIP.cmd	Extracts an incremental set of promotions data from Siebel organized by product and shipping account Launches the Demantra web server Starts the IMPORT: SIEBEL_PDATA_PROD_SHIP workflow

Directory of locking batch file: <root>\ODI_DEM_SBL_HOME\integration\Siebel-Demantra\PromotionLock

Package Name Description of Functionality	Description of Functionality
SIEBEL_PROMOTIONL_LOCK_MAIN.cmd	Runs: ODI_SIEBEL_PROMOTION_LOCK.cmd EIM_PROMO_STATUS.BAT
ODI_SIEBEL_PROMOTION_LOCK.cmd	Selects the promotions that need their status changed from “Send to Demantra” to “Locked by Demantra”.
EIM_PROMO_STATUS.BAT	Changes the promotion status to “Locked by Demantra” for the selected promotions.

Promotional Tactics

Promotional tactics define the type of promotional activity. Typical tactics are:

• Feature Only: Referring to a feature or ad in a retailer’s circular without any display activity.

• Display Only: Referring to an in-store display such as an end-aisle display or a display unit in the aisle without feature activity.

• Feature & Display: Referring to both feature and display activity.

• TPR: Meaning Temporary Price Reduction, which refers to a reduction in retail price without any associated feature or display activity.

Siebel is the system of record for the master listing of promotional tactics.

The existing “Veh Type” series in Demantra stores the promotional tactic. This series resides on Promotion Data and is supported by a lookup table containing the valid promotional tactics.

The master listing of promotional tactics as defined in Siebel is sent to Demantra to populate the lookup table for the promotional tactic series. The lookup table stores the tactic type and discount percentage for each promotional tactic. These fields are manually maintained and are needed to support the causal factors for the Forecasting Engine. For example, if the promotional tactic from Siebel was “Feature with a 10% discount” the tactic type would be set to “Feature” and the discount percentage would be set to “10.” See the Demantra Setup section for more information about modifying this mapping table.

As this definition doesn’t change often, this is typically a one-time process that happens once per implementation. If changes are made to the promotional tactics master list in Siebel, then this interface can be run on-demand to update Demantra.

Business Rules

1. It is recommended that this process be loaded once, or if the promotional tactics change in Siebel.

2. The promotional tactic information is always a full load.

Mapping

The Promotional Tactics data will be transformed from the Siebel table’s structures into the structure and format of the Demantra Staging/Interface tables. The required data elements are:

Required Data Element	Demantra Reference	Siebel Reference
Promotional Tactic ID	Promotional Tactic ID	Promotional Tactic ID
Promotional Tactic Description	Promotional Tactic Description	Promotional Tactic Description

ODI Package and Batch File Details

Recommended Scheduling Frequency: Once or when needed.

ODI Package Name: run Promotion Hierarchy Exp

Directory of batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\PromoLevelLoad

Package Name	Description of Functionality
PROMO_TYPE_EXPORT.cmd	Extracts the promotional tactics from Siebel to Demantra.

Sales Activity

Historical shipment data must be loaded into Demantra for the modeling engine to predict the baseline forecast. Shipment data is used to derive the baseline forecast for all the products in different accounts. It is also used to derive the proportion logic used to aggregate and disaggregate the data at different hierarchy levels The integration does not include UOM conversion capability and assumes that all volume data in both applications is stored in a common unit of measure.

Business Rules

1. You must perform a one-time load of two years of shipment history.

2. It is recommended that this process be scheduled on a regular interval (weekly). This on-going load is always incremental, including only those records that got changed from the previous load.

Mapping

Demantra Field	Type	Siebel Source Table	Siebel Source Field
Product Name	VARCHAR2(100)	S_PROD_INT	PROD_NAME
Product Number	VARCHAR2(50)	S_PROD_INT	PART_NUM
Site Name	VARCHAR2(30)	S_ORG_EXT	NAME
Site Number	VARCHAR2(100)	S_ORG_EXT	OU_NUM
Shipment Volume	VARCHAR2(22)	S_PROD_SHIPMENT	ACTL_CASES
Unit of Measure	VARCHAR2(30)	S_PROD_SHIPMENT	PREF_UOM_CD
Start Date	DTYPE_DATE	S_PERIOD	START_DT
End Date	DTYPE_DATE	S_PERIOD	END_DT

ODI Package and Batch File Details

Recommended Scheduling Frequency:

• Full: On-demand for the initial history load including two years worth of historical shipment data.

• Incremental: Nightly as an automated scheduled process for an incremental load of all shipments created or modified in Siebel since the last update.

ODI Package Names:

• run Sales History Incr Load

• run Sales History Full Load

Directory of batch files: <root>\ ODI_DEM_SBL_Home\integration\Siebel-Demantra\SalesHistoryLoad

Package Name	Description of Functionality
SALES_HISTORY_INCR_LOAD.cmd	Extracts the incremental shipping data from Siebel to Demantra.
SALES_HISTORY_FULL_LOAD.cmd	Extracts two years of historical shipping data from Siebel to Demantra.

Demantra to Siebel ODI Packages and Batch Files

There are IFB files within each Demantra-Siebel extract directory. These files provide the Siebel Enterprise Integration Manager with configuration information such as the user name and password. The IFB files reference the user name and password in the runsrvrmgr.bat file, so there is no need to configure each IFB file.

Baseline Volume Forecast

The baseline volume forecast is a key element in promotion planning to understand the effect of the promotion compared to the incremental volume. In addition, baseline volume is used to evaluate promotion effectiveness.

The ongoing load is always a full load for 18 months in the future. Baseline records for products that have been deleted or inactivated in Siebel are filtered out. The integration does not include UOM conversion capability and assumes that all volume data in both applications is stored in a common unit of measure.

Recommended Scheduling Frequency: Weekly

Directory: <root>\ ODI_DEM_SBL_Home\integration\Demantra-Siebel\Baseline_Load

Package Name	Description of Functionality
BASELINE_TO_SIEBEL_LOAD_MAIN.cmd	Runs the following batch files in sequence: ODI_APPPROC_BLE_VOLUME_BASE_TTL.cmd ODI_BASELINE_FORECAST_EXPORT.cmd EIM_BASELINE_LOAD.bat If you have another routine that runs the APPPROC_BLE_VOLUME_BASE_FUTURE procedure, you can remove the reference to the ODI_APPPROC_BLE_VOLUME_BASE_TTC.cmd in this batch file.
ODI_APPPROC_BLE_VOLUME_BASE_TTL.cmd	Updates sales_data .vol_base_ttl for dates in the past This procedure sets the field equal to the value of sales_data. sdata5, the syndicated base volume. This procedure must be run before the Baseline Forecast can be exported. If your organization uses a routine that already runs the APPPROC_BLE_VOLUME_BASE_FUTURE.cmd procedure, this batch routine does not need to be run.
ODI_BASLINE_FORECAST_EXPORT.cmd	Launches the Demantra webserver Starts the EXPORT:BASELINE_VOLUME_FORECAST workflow to extract data to the Demantra temporary staging table.
EIM_BASELINE_LOAD.bat	Launches the Enterprise Integration Manager and loads the extract data according to the configuration settings in the Baseline_Import.ifb file.

Mapping

Demantra Name	Siebel Target Table	Siebel Target Field	Type
Product Name	S_PROD_BASELINE	PROD_NAME	VARCHAR(100)
Product Number	S_PROD_INT	PART_NUM	VARCHAR(50)
Site Name	S_ORG_EXT	NAME	VARCHAR(30)
Site Number	S_ORG_EXT	OU_NUM	VARCHAR(100)
Start Date	S_PERIOD	START_DT	DTYPE_DATE
End Date	S_PERIOD	END_DT	DTYPE_DATE
Shipment Volume	S_PROD_BASELINE	BASE_SALES	Number

Promotional Lift Coefficients

Demantra generates lift coefficients for predicting the incremental volume associated with a planned promotion based on the parameters of the promotion such as promotion tactics.

Lift coefficients are generated at the product (SKU) or promoted product group level by customer account (retailer). Category level lift coefficients are not included.

Lift coefficients are generated for a combined promotional tactic and price discount percentage. The following is a list of the most likely sets of tactics and price discounts and varies by implementation:

• Feature with no price discount

• Feature with up to a 5% price discount

• Feature with up to a 10% price discount

• Feature with up to a 15% price discount

• Feature with up to a 20% price discount

• Feature with up to a 25%

• Feature with more than a 25% price discount

• Display with no price discount

• Display with up to a 5% price discount

• Display with up to a 10% price discount

• Display with up to a 15% price discount

• Display with up to a 20% price discount

• Display with up to a 25%

• Display with more than a 25% price discount

• Feature & Display with no price discoun

• Feature & Display with up to a 5% price discount

• Feature & Display with up to a 10% price discount

• Feature & Display with up to a 15% price discount

• Feature & Display with up to a 20% price discount

• Feature & Display with up to a 25%

• Feature & Display with more than a 25% price discount

• TPR of up to a 5% price discount

• TPR of up to a 10% price discount

• TPR of up to a 15% price discount

• TPR of up to a 20% price discount

• TPR of up to a 25%

• TPR of more than a 25% price discount

Business Rules

1. Lift coefficients are generated for the time period of one year. The ongoing load is always a full load for 18 months in the future.

2. Lift coefficient records for products that have been deleted or inactivated in Siebel will be filtered out in the interface.

Mapping

Name	Demantra Source Field	Siebel Target Table	Siebel Target Field
Promotion Name	PROMO_NAME	S_SRC	NAME
Promotion Number	PROMO_NUM	S_SRC	SRC_NUM
Promotion Status	PROMO_STATUS	S_SRC	STATUS_CD
Plan Number	PLAN_NUM	S_SRC	SRC_NUM
Plan Name	PLAN_NAME	S_SRC	NAME
Promotion St Dt	PROMO_ST_DT	S_SRC	PROG_END_DT
Promotion End Dt	PROMO_END_DT	S_SRC	PROG_END_DT
Account Name	ACCNT_NAME	S_ORG_EXT	NAME
Account Number	ACCNT_NUM	S_ORG_EXT	OU_NUM
Shipment Start Date	SHIP_ST_DT	S_SRC	SHIP_END_DT
Shipment End Date	SHIP_END_DT	S_SRC	SHIP_END_DT

ODI Package and Batch File Details

Recommended Scheduling Frequency: Weekly or Monthly

Directory: <root>\ ODI_DEM_SBL_Home\integration\Demantra-Siebel\Liftfactor_Load

Package Name	Description of Functionality
LIFT_FACTOR_EXPORT_MAIN.cmd	Runs the following batch files in sequence: ODI_LIFT_FACTOR_EXPORT.cmd EIM_LFT_FCTR.bat
ODI_LIFT_FACTOR_EXPORT.cmd	Extracts promotional lift coefficients from Demantra internal tables.
EIM_BASELINE_LOAD.bat	Launches the Enterprise Integration Manager and loads the extract data according to the configuration settings in the LIFT_COEFFICIENT.ifb file.

Promotions

Users specify promotions to transfer from Demantra to Siebel by setting the promotion transfer status of each promotion to “Transfer to Siebel”. It is recommended that a background process run every 30 minutes to load these promotions into Siebel. Once these promotions have been transferred, the promotion transfer status is changed to “Locked” so that the promotion is not transferred the next time the process is run.

Promotions can be exported from Demantra at the correct customer account and product level to align with the account type and product type in Siebel. For promotions created in Siebel and sent to Demantra, the account type and product type for the promotion in Siebel must be retained. If these promotions are modified in Demantra and transferred back to Siebel, they must be sent at the appropriate levels of the Demantra site and item hierarchies that correspond to the Siebel account type and product type.

Business Rules

1. This process is designed to run on an on-going basis during the day (every 30 minutes) for an incremental load of all promotions where the promotion transfer status is “Transfer to Siebel”.

2. As part of the process, the promotion transfer status is changed from “Transfer to Siebel” to “Locked” for each processed promotion.

3. It is assumed that all of the data elements loaded using the promotion data interface are the same for all location members, if the promotion is associated to more than one location (Site, Retailer, Division).

4. New promotions created in Demantra are assigned a Demantra promotion ID. Siebel accepts and uses this promotion ID and does not assign its own.

5. Funds are not be assigned to promotions in Demantra. New promotions created in Demantra are sent to Siebel with a fund default of “DemantraFund”. The user will be required to maintain the promotion in Siebel and assign the appropriate fund(s). It is recommended that Siebel be configured to not allow promotions to be approved until a valid fund(s) has been assigned.

6. When new promotions are created in Demantra, they are automatically assigned a status of “Unplanned”. Promotions in this unplanned status are used for planning and testing purposes and are not considered valid promotions. While in the unplanned state, these promotions cannot be transferred to Siebel.

Mapping

Demantra Name	Demantra Source Field	Siebel Target Table	Siebel Target Field
Promotion Name	PROMO_NAME	S_SRC	NAME
Promotion Number	PROMO_NUM	S_SRC	SRC_NUM
Promotion Status	PROMO_STATUS	S_SRC	STATUS_CD
Plan Number	PLAN_NUM	S_SRC	SRC_NUM
Plan Name	PLAN_NAME	S_SRC	NAME
Promotion St Dt	PROMO_ST_DT	S_SRC	PROG_END_DT
Promotion End Dt	PROMO_END_DT	S_SRC	PROG_END_DT
Account Name	ACCNT_NAME	S_ORG_EXT	NAME
Account Number	ACCNT_NUM	S_ORG_EXT	OU_NUM
Shipment Start Date	SHIP_ST_DT	S_SRC	SHIP_END_DT
Shipment End Date	SHIP_END_DT	S_SRC	SHIP_END_DT

Demantra Name	Demantra Source Field	Siebel Target Table	Siebel Target Field
Category Name	PROD_NAME	S_CTLG_CAT	---
Category Number	PROD_NUM	S_CTLG_CAT	---
Product Name	PROD_NAME	S_PROD_INT	NAME
Product Number	PROD_NUM	S_PROD_INT	PART_NUM
Product Type	PROD_TYPE	S_PROD_INT	TYPE

Demantra Name	Demantra Source Field	Siebel Target Table	Siebel Target field
ACV %	ACV	S_SRC_CHNL	PROMO_ACV_PCT
Off Invoice Allowance	OI_RATE	S_SRC_CHNL	CMMT_XINV_CASERATE
Bill back Allowance	BB_RATE	S_SRC_CHNL	CMMT_BILL_BACK
Scan Down Allowance	SD_RATE	S_SRC_CHNL	CMMT_VAL_SCAN_RED
Fixed Cost	FXD_AMT	S_SRC_CHNL	CMMT_FIXED_COST
Promoted Price	PROMOTED_PRICE	S_SRC_CHNL	PROMO_PRI_PT
Tactics	TACTIC	S_CG_PROMTACTIC	TACTICS_CD

ODI Package/Extract Overview

The Promotion Exports extract has been designed to support two product types and four account types. Implementations generally run only the one (or two) combinations that align to the way the promotions are planned in Siebel.

The product types are product (SKU) and promoted product group (a grouping of products typically promoted together). The account types are ship-to customer, bill-to customer, customer division, and corporate customer. Separate versions of the promotion extracts are provided for each possible combination as listed below:

• Product AND Bill-To Customer

• Product AND Corporate Customer

• Product AND Customer Division

• Product AND Ship-To Customer

• Promoted Product Group AND Bill-To Customer

• Promote d Product Group AND Corporate Customer

• Promoted Product Group AND Customer Division

• Promoted Product Group AND Ship-To Customer

Depending on the combinations preferred, a corresponding main batch file runs the subroutines that extract the correct promotion information. The subroutines are organized in the following order:

• Step 1a: Extraction of promotion header with the appropriate combination. (This is the only step that varies in the main batch files.)

• Step 1b: Transfer of promotion header to Siebel Enterprise Integration Manager.

• Step 2a: Extraction of promotion category data.

• Step 2b: Transfer of promotion category data to Siebel Enterprise Integration Manager.

• Step 3a: Extraction of promotion by product data.

• Step 3b: Transfer of promotion product data to Siebel Enterprise Integration Manager.

• Step 4a: Extraction of deleted promotion tactics.

• Step 4b: Transfer of deleted promotion tactics to Siebel Enterprise Integration Manager.

• Step 5a: Extraction of promotion details.

• Step 5b: Transfer of promotion details to Siebel Enterprise Integration Manager.

The extraction process ends with the ODI_Demantra_PROMOTION_LOCK.cmd to lock the promotion transfer status so that it isn’t transferred when the extracts are run the next time.

Note: The runsrvrmgr.bat sets the username and password for all the ifb files.

ODI Package and Batch File Details

Recommended Scheduling Frequency: Every 30 minutes

Directory of batch files: <root>\ ODI_DEM_SBL_Home\integration\Demantra-Siebel\PROMOTION_LOAD

Directory of IFB files: <root>\ODI_DEM_SBL_Home\integration\Demantra-Siebel\PROMOTION_LOAD\IFB

Package Name	Description of Functionality
PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd	Runs the following batch files in sequence: Loading Promotions Header ODI_STEP_1A_HEADER_PD_BILL.cmd. EIM_STEP_1B_PROMOTION_HDR_STEP.BAT Loading Promotions By Category ODI_STEP_2A_PROMO_CATAGORY_EXP.cmd EIM_STEP_2B_PROMOTION_CATG_STEP.BAT Loading Promotions by Product ODI_STEP_3A_PROM_BY_PRODUCT_EXP.cmd EIM_STEP_3B_PROMOTION_PROD_STEP.BAT Loading Promotions Data ODI_STEP_4A_PROMO_DETAILS_EXP.cmd EIM_STEP_4B_PROMOTION_PROD_DTL_STEP.BAT Loading Promotions Promoted Price ODI_STEP_5A_PROMO_PRICE_EXP.cmd EIM_STEP_5B_Promoted_Price_STEP.BAT
PROMOTION_TO_SIEBEL_PD_CORP_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PD_CORP.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PD_DIV_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PD_DIV.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PD_SHIP_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PD_SHIP.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PG_BILL_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PG_BILL.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PG_CORP_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PG_CORP.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PG_DIV_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PG_DIV.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
PROMOTION_TO_SIEBEL_PG_SHIP_MAIN.cmd	Same as PROMOTION_TO_SIEBEL_PD_BILL_MAIN.cmd except for the following: Loading Promotions Header ODI_STEP_1A_HEADER_PG_SHIP.cmd EIM_STEP_1B_PROMOTION_HDR_STEP.BAT
ODI_STEP_1A_HEADER_PD_BILL.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PD_BILL workflow.
ODI_STEP_1A_HEADER_PD_BILL.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PD_BILL workflow.
ODI_STEP_1A_HEADER_PD_CORP.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PD_CORP workflow.
ODI_STEP_1A_HEADER_PD_DIV.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PD_DIV workflow.
ODI_STEP_1A_HEADER_PD_SHIP.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PD_SHIP workflow.
ODI_STEP_1A_HEADER_PG_BILL.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PG_BILL workflow.
ODI_STEP_1A_HEADER_PG_CORP.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PG_CORP workflow.
ODI_STEP_1A_HEADER_PG_DIV.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PG_DIV workflow.
ODI_STEP_1A_HEADER_PG_SHIP.cmd	Launches the Demantra web server and starts the EXPORT: SIEBEL_PROMOTIONS_PG_SHIP workflow.
ODI_STEP_2A_PROMO_CATAGORY_EXP.cmd	Extracts the Promotions By Category data from Demantra.
ODI_STEP_3A_PROM_BY_PRODUCT_EXP.cmd	Extracts the Promotions By Product data from Demantra.
ODI_STEP_4A_DELETE_PROMO_TACTICS.cmd	Extracts the Promotions data from Demantra
ODI_STEP_5A_PROMO_DETAILS_EXP.cmd	Extracts the Promotions Promoted Price data from Demantra.
EIM_STEP_1B_PROMOTION_HDR_STEP.BAT	Launches the Enterprise Integration Manager and loads the promotion header according to the configuration settings in the PROMOTION_HDR.ifb file.
EIM_STEP_2B_PROMOTION_CATG_STEP.BAT	Launches the Enterprise Integration Manager and loads the promotion category data according to the configuration settings in the PROMOTION_CATG.ifb file.
EIM_STEP_3B_PROMOTION_PROD_STEP.BAT	Launches the Enterprise Integration Manager and loads the promotion product data according to the configuration settings in the PROMOTION_PROD.ifb file.
EIM_STEP_4B_DELETE_PROMO_TACTICS.BAT	Launches the Enterprise Integration Manager and loads the deleted promotion tactics data according to the configuration settings in the PROMOTION_TACTICS_DEL.ifb file.
EIM_STEP_5B_PROMOTION_PROD_DTL_STEP.BAT	Launches the Enterprise Integration Manager and loads the promotions promoted price data according to the configuration settings in the PROMOTION_PROD_DTL.ifb file.
ODI_DEMANTRA_PROMOTION_LOCK.cmd	Changes the status of the promotions that have the transfer status “Transfer to Siebel” to “Locked”. This batch file is run after the main batch routine.