Configuring Predictive Trade Planning

This chapter covers the following topics:

Defining a Promotion Calendar

A Promotion Calendar allows Demantra Predictive Trade Planning (PTP) users to view and optionally edit promotions in a format that resembles a calendar. This view enables users to easily see all promotions that are planned for a selected population, such as account, product group, or time period.

In a Promotion Calendar, each promotion appears in a separate color-coded "box" with the series values displayed in each. The number of Series displayed in the Calendar Box is configurable, based on the value of MAX_CALENDAR_SERIES in the CALENDAR_VIEW_DEF table (see table definition below).

Promotions appear in the same row unless their start and end dates overlap. When promotions overlap, they are displayed in separate rows.

This section describes how to define a Promotion Calendar. For details about viewing and using a Promotion Calendar in a PTP worksheet, see the Oracle Demantra Predictive Trade Planning User's Guide.

Promotion Calendars use two tables, CALENDAR_VIEW_DEF and CALENDAR_SERIES_DEF. To create a Promotion Calendar you must enter data directly into these tables using a tool such as SQL Developer.

You define a Promotion Calendar by a two-step process:

  1. Define general information about the Promotion calendar, in CALENDAR_VIEW_DEF.

  2. Define the series that will appear in the Promotion Calendar, in CALENDAR_SERIES_DEF.

The following table descriptions will assist the implementer in this process.

Note: If a Promotion Calendar is defined on an invalid level, an error message will be written to the application server log file.

CALENDAR_VIEW_DEF

Each row in the table contains the definitions for a Promotion Calendar View.

Column Description
CALENDAR_VIEW_ID Unique ID (number) for the Promotion Calendar. This is a required field and does not get automatically populated.
CALENDAR_LEVEL_ID The internal ID of the level on which the Promotion Calendar is defined. For example, for the Promotion level, enter 232. A Promotion Calendar can be defined on any General Level with attributes that can be used as start and end dates.
To determine the Level ID for any General Level, query the GROUP_TABLES database table. The GTABLE column stores the name of the General Level and the GROUP_TABLE_ID stores the corresponding Level Id. Use the GROUP_TABLE_ID value as the CALENDAR_LEVEL_ID.
CALENDAR_VIEW_NAME User-defined name for the calendar. For example: “FY2012 Promotion Calendar.”
MAX_EVENTS_OVERLAPPING The number of overlapping promotions that can occur in the calendar without displaying the overlap indicator. For example: 3.
If the number of overlapping promotions exceeds this value, an overlap indicator displays in the calendar. Clicking on this indicator expands the row to display all overlapping promotions. Also, the collapse icon displays, allowing you to return to the original view.
COLOR_EXPRESSION_SERIES_ID The unique ID of a specific series that you want the Promotion Calendar to use as the background color expression for the promotion in the calendar (the color of the 'block' or region that identifies the promotion). The color expression of the chosen series will then be used to define the background color of each member's area in the graph. (The Series ID is available in the Business Modeler or by querying the COMPUTED_FIELDS database table where the FORECAST_TYPE_ID column contains the Series ID value).
LOCK_EXPRESSION_SERIES_ID The unique ID of a specific series that you want the Promotion Calendar to use as the Edit Lock expression defined for a referenced series (optional). The Edit Lock Expression of the specified Series determines if the promotion can be edited on the Calendar.
BASE_TIME_LEVEL_ID The default time level that the Promotion Calendar time will display when opened. Set to 1 to display Days within Weeks, 2 for Weeks within Calendar Months, 3 for Calendar Months within Years, and 4 for Years.
START_DATE_SERIES_ID A required field. The unique ID of a specific series that you want the Promotion Calendar to use as the calendar's start date (for example, to have promotion start date based on series 'Start Event', enter '1815')
END_DATE_SERIES_ID A required field. Same as above, but for the end date.
APPLICATION_ID Not user defined, populated by the system (unique ID for Promotion Calendar)
MAX_CALENDAR_SERIES Defines the number of series to display in each calendar box. If the number of series defined for the calendar view in the CALENDAR_SERIES_DEF table exceeds this number, then the additional series will not be displayed in the box but will displayed in a pop-up when the user hovers the mouse over the box.

Note: Series that are defined in this table, but are not included in the worksheet, will not show on the calendar.

CALENDAR_SERIES_DEF

This table defines the series that appear in a Promotion Calendar View.

Column Description
CALENDAR_VIEW_ID Unique ID of the Promotion Calendar (from CALENDAR_VIEW_DEF table).
SERIES_ID Unique ID of the series you want to display in the calendar (for example, '1815'). A particular series should only be listed once per CALENDAR_VIEW_ID
SERIES_ORDER Sequence in which each Series appears in a Calendar box, or when the end user hovers over the box to view the full list (top to bottom).
DISPLAY_LABEL Set to 0 if you don't want a label for the series, or set to 1 to display the series name. If want to display text other than the series name, set to 1 and then enter that text in LABEL_VALUE.
LABEL_VALUE Text that you want to display as the series label, when DISPLAY_LABEL is set to '1'.
APPLICATION_ID Not user defined, populated by the system.

Note: The number of visible series in each promotion 'box' is determined by MAX_CALENDAR_SERIES. If the number of series defined exceeds the setting for the MAX_CALENDAR_SERIES setting on the CALENDAR_VIEW_DEF table, then the additional series will not be shown in the Calendar Box but will appear in alt text/popup when the user places the cursor over the box.

Predefined Promotion Calendar

The following tables describe the seeded values of the predefined Promotion Calendar view. For more information about the columns in this table, please refer to the section "Defining a Promotion Calendar".

CALENDAR_VIEW_DEF Table

Column Value Comments
CALENDAR_VIEW_ID 1  
CALENDAR_LEVEL_ID 232 This is the GROUP_TABLE_ID value from GROUP_TABLES for the Promotion Level.
CALENDAR_VIEW_NAME Promotion Calendar  
MAX_EVENTS_OVERLAPPING 3  
COLOR_EXPRESSION_SERIES_ID The Series ID for the Calendar View Settings series  
LOCK_EXPRESSION_SERIES_ID The Series ID for the Calendar Edit Lock Setting series  
BASE_TIME_LEVEL_ID 2  
START_DATE_SERIES_ID 1815 This is the FORECAST_TYPE_ID value from COMPUTED_FIELDS for the Start Event series.
END_DATE_SERIES_ID 3903 This is the FORECAST_TYPE_ID value from COMPUTED_FIELDS for the End Event series.
APPLICATION_ID System Assigned  
MAX_CALENDAR_SERIES 4  

CALENDAR_SERIES_DEF Table

SERIES_ID SERIES_ORDER DISPLAY_LABEL Series Name
3766 1 0 Promotion Desc
1806 2 0 Promotion Tactics
1818 3 1 Sale Price
1865 4 1 Evt Spend
1815 5 1 Start Event
3903 6 1 End Event
1813 7 1 Start Ship
1814 8 1 End Ship
1829 9 1 Lift
1926 10 1 Incr Mfg Prft

Note: The following columns are included in the CALENDAR_SERIES_DEF database table, but are not shown in the table above:

Overview of Promotion Effectiveness

Promotion Effectiveness is a configurable Web-based product that analyzes the effectiveness of your marketing promotions, in particular trade promotions. In addition to base forecasting and forecasting lift due to promotions, Promotion Effectiveness can analyze the effects of different items on the sales patterns of others.

Promotion Effectiveness uses the same Web client that is used for Demand Management and DSM. For Promotion Effectiveness, the Analytical Engine provides a greater breakdown of details than does the engine for demand planning.

Overview of the Configuration Process

These steps assume that you have already set up the basic Demantra implementation. This means that your implementation already contains the item levels and location levels that are meaningful in the designated environment.

To configure Promotion Effectiveness, the general steps are as follows:

  1. Create the levels required by Promotion Effectiveness and then optionally customize them; see "Configuring Levels for Promotion Effectiveness".

  2. Set values of parameters that control the behavior of the Web client, as it relates to Promotion Effectiveness. See "Setting Parameters".

  3. Optionally configure the Activity Browser, if the default configuration is not suitable. See "Configuring the Activity Browser".

  4. Decide if you are going to use the default promotion life cycle provided by Demantra. Configure this life cycle as described in "Configuring Promotion Statuses".

  5. Load promotions and promotion data as described in "Loading Promotion Data".

  6. Configure the Analytical Engine for use with Promotion Effectiveness. See "Configuring and Running the Analytical Engine" in the Demantra Analytical Engine Guide.

Note: Deductions and Settlement Management functionality can be disabled for a given user by restricting access to the Settlement Level to 'No Access" in the user's permissions. For details, please see Data Security.

Configuring Levels for Promotion Effectiveness

The Demantra installer sets up the Promotion Effectiveness (PE) structures by default.

You can customize these levels to some extent. See “Reference: PE Levels”.

Setting Parameters

To configure Promotion Effectiveness, specify values for the following parameters:

Parameter Description
ColorCodingLevel Specifies the ID of the level that will be used to color code promotions.
PromoDefaultStart Specifies the default start date for promotions created within a worksheet: the current date, the last loaded sales date, or the worksheet start.
PromoDefaultSpan Specifies the default length of time for promotions created within a worksheet, in base time units.
For additional parameters, see “Engine Parameters” in the Demantra Analytical Engine Guide.

See also

Configuring the Activity Browser

The CREATE_PE_STRUCT procedure configures the Activity Browser in the Activity Details sub tab in worksheets. You can re-configure the Activity Browser as needed.

To configure the Activity Browser

  1. Click Configuration > Configure Levels. Or click the Configure Levels button.

    Business Modeler displays the Configure Levels screen.

  2. Right-click a general level and select Open > Activity Browser.

    the picture is described in the document text

  3. For each general level to include in the Activity Browser, click the left arrow to move that general level from the left list to the right list.

    Or drag and drop general levels between the two lists as needed.

  4. To specify the order of levels in the right side of the screen, select a level and click the up or down buttons.

  5. When you are done, click Finish.

Configuring the Application Server

Note: To configure the Promotion Optimization engine for Tomcat running on Linux you must include the following in the .bash_profile file:

Configuring Promotion Statuses

Demantra provides a default set of promotion statuses and structures to support a typical promotion life cycle.statuses to support a typical promotion life cycle. The IDs have default names and specific hardcoded behaviors meanings, as follows:

promotion.status field Default status name* Hardcoded behavior
1 Unplanned Analytical Engine ignores this promotion.
A user can manually change the promotion status to 1, 2, or 3.
2 Cancelled A user can manually change the promotion status to 1, 2, or 3.
3 Planned A user can manually change the promotion status to 1, 2, or 3.
4 Committed User cannot change the status. (An optional procedure can be used to advance the status; see below.)
User cannot edit the promotion.
 
5 Executed User can change the status to 6, but cannot otherwise edit the promotion.
6 Closed User cannot change the status and cannot edit the promotion in other ways.
*The status names are in the promotion_status_lookup table.

Automatic Advancement of Promotion Status

Demantra provides a tool to automatically advance the status of promotions based on their starting dates. The EXPOSE_PROMOTIONS procedure iterates through the promotions listed in the promotion table, checks the status field of each, and does the following:

You should schedule this procedure to run periodically either within the Workflow Engine.

Customizing the Promotion Status Behavior

Depending on how suitable the default behavior is, you have several options:

This system provides flexibility until promotions are committed.

Loading Promotion Data

To load promotions (which are general levels), create and use an integration interface that includes a level profile. Demantra does not provide a predefined interface for this, because promotions vary in nature.

To load promotional data (promotion-type series), create and use an integration interface that includes a data profile that includes the desired series.

See “Series and Level Integration”.

Reference: PE Levels

The CREATE_PE_STRUCT procedure adds the following levels to your database:

the picture is described in the document text

Note:Demantra provides the Promotion Effectiveness (PE) structures by default. Therefore, the CREATE_PE_STRUCT procedure should be run only after creating a new or custom data model from scratch. This is not common or recommended. Only in this scenario would these structures not already be in place. To determine whether PE structures exist, search the DB for table names containing %PROMOTION%.

The following sections provide details on these levels:

Promotion

This level contains the details for promotions. This is a general level with the following attributes:

Attribute Column Name Data Type Purpose
Population Attribute n/a n/a Allows you to associate the promotion with combinations and dates.
Status status Number ID of the status of this promotion.
This is a lookup attribute of type table; it uses the promotion_status_lookup table. See “Configuring Promotion Statuses”.

Promotion Type

This level contains the default promotion types. This is a general level with no attributes. You can redefine this level as needed.

Scenarios

This level groups the promotions. This is a general level with the following attributes:

Attribute Column Name Data Type Purpose
Name SCENARIO_DESC Character Name of this scenario.
Plans PLAN_ID Number ID of the plan to which this scenario belongs.
This is a lookup attribute based on the Plans level.

You can redefine this level as needed.

Plans

This level groups the scenarios.

This is a general level with one attribute:

Attribute Column Name Data Type Purpose
Name PLAN_DESC Character Name of this plan.

You can redefine this level as needed.

PTP Data Needs

Without going into details of the specific formats (given later), this section lists the data needs for PTP.

Always Required

Every PTP system must have the following data:

Nice to Have

By default, PTP assumes that additional data is also available, but you can reconfigure PTP to work if it is not.

Purely Optional

Other data is purely optional:

Integration in PTP

To understand integration in PTP, it is useful to review how the Demantra platform handles integration.

Data Loading and Integration in the Platform

Demantra provides the following tools for data loading and integration:

In both cases, the wizards create staging tables, which usually serve as the starting point for data loading.

These tools are documented in the Part titled: "Basic Configuration".

Data Loading and Integration in PTP

Because the PTP model is already defined, PTP provides an ep_load_sales procedure and integration interfaces that all work with the PTP model. It is not necessary to use the Data Model Wizard or the Integration Interface Wizard. The required staging tables already exist as well.

To facilitate data loading, PTP offers two options.

Based on the decision made, review the Data Model Wizard as well as Integration Interface Wizard to verify that current settings match selections. Default settings for the Data Model Wizard are for loading through database tables. Promotional information loaded through integration profiles is set as a default for text files.

Reference: Staging Tables

Internally, PTP uses the following staging tables:

The information here is provided for reference and debugging purposes.

BIIO_IMP_PROMO_DATA

This staging table is used by the Import Promotion Data2 integration interface and has the following structure:

Field Data Type Required? Purpose*
Sdate date   Sales date.
LEVEL1 varchar2(500)   Code of the promotion member to which this data applies.
PROMO_PRICE number(20,10)   Sale price per unit at shelf. Used by the Sale Price series.
SLOTTING_SPEND number(20,10)   Planned spend associated with slotting costs. Used by the Slotting $ series.
CASE_BUYDOWN number(20,10)   Buydown allowance per unit, for this promotion. Can be used for off-invoice deductions or bill-back. Used by the Buydown series.
VEHICLE_SPEND number(20,10)   Total planned vehicle costs for this promotion. Used by the Loading Veh $ series.
ALLOWANCE_TYPE varchar2(50)   Description of the pay type of this promotion, as listed in the pay_type_lookup table. Use the pay_type_desc field, rather than the code or ID field. Used by the Pay Type series.
IS_SELF number(20,10)   Specify as "1" for all records. Used for internal purposes.
* For details on these series, see "Series".

BIIO_Promotion

This staging table is used by the Import Promotion Levels integration interface. It contains all the promotion attributes, of which only a subset are typically imported. This table has the following structure:

Field Data Type Required? Purpose*
PROMOTION_CODE varchar2(500) Required Unique code for the promotion, for use in Demantra.
PROMOTION_DESC varchar2(2000) Required Description or name of the promotion, for use in Demantra.
PROMO_INTEG_STATUS_CODE varchar2(120)   Used for integration to determine whether promotion details have been changed
SCENARIO_CODE varchar2(500) Required Unique code for the scenario to which this promotion belongs, as listed in the SCENARIO table.
PROMOTION_TYPE_CODE varchar2(500) Required Unique code for the promotion type, as listed in the PROMOTION_TYPE table.
PROMOTION_STAT_CODE varchar2(120) Required Unique code for the promotion status, as listed in the promotion_stat table.
BUY_DOWN number(20,10)   Populates the Buydown attribute.
OPTIMIZATION_GOAL varchar2(2000)   Populates the Optimization Goal attribute.
OPTIMAL_PRICE_DECREASE number(20,10)   Populates the Optimal Price Decrease attribute.
STATUS varchar2(30)   Populates the Status attribute.
APPROVAL varchar2(200)   Populates the Approval attribute.
SHIP_DATE date   Populates the Start Ship attribute with the date on which shipments will start for this promotion.
CONS_PROMO varchar2(50)   Populates the Cons Promo attribute, which indicates any associated consumer overlay.
OPTI_PROMOTION_TYPE_ID varchar2(2000)   Populates the Optimal Type attribute.
MAX_BUDGET number(20,10)   Populates the Max Budget attribute. Maximum allowed budget for this promotion.
SPEND number(20,10)   Populates the Optimal Budget attribute.
ROI number(20,10)   Populates the Return on Investment (ROI) attribute.
PROFIT number(20,10)   Populates the Optimal Profit attribute.
TOTAL_LIFT_U number(20,10)   Populates the Optimal Lift attribute.
TOTAL_LIFT_D number(20,10)   Populates the Optimal Revenue attribute.
PROMOTION_TYPE_ID1 varchar2(2000)   Populates the Promotion Type1 attribute.
STATUS_ID1 varchar2(30)   Populates the Promotion Status attribute.
END_SHIP date   Populates the End Ship attribute.
METHOD_STATUS varchar2(200)   Populates the method_status attribute.
OPTIMIZATION_STATUS varchar2(50)   Populates the Optimization Status attribute.
PROMOTION_BUDGET number(20,10)   Promotional budget updated by DSM
MIN_RTL_MARGIN_OVERRIDE number(20,10)   Populates the Min Rtl Margin Override attribute.
FIXED_BUYDOWN_YN varchar2(100)   Populates the Fixed Buydown attribute.
MAX_BUYDOWN number(20,10)   Populates the Max Buydown attribute.
OPTIMIZATION_RANGE_START date   Populates the Optimization Range Start attribute.
OPTIMIZATION_RANGE_END date   Populates the Optimization Range End attribute.
OPTIMIZATION_COUNT number(20,10)   For internal use only.
START_EVENT Date   Populates the Start Event attribute. The starting date for the consumption activity on the promotion.
END_EVENT Date   Populates the End Event attribute. The ending date for the consumption activity on the promotion.
* For details on these attributes, see "Promotion".

BIIO_Population

This staging table is used by the Import Promotion Levels integration interface. It describes the population of each promotion. Specifically, it contains the same information as this window:

the picture is described in the document text

For each promotion, the table can contain multiple rows. Each row specifies a level and a member of that level, just as the preceding screen does (the previous screen shows that this promotion is associated with the Low Fat member of the Product Family). This table has the following structure:

Field Data Type Required? Purpose
LEVEL_MEMBER varchar2(40) Required Code of the promotion that you are loading.
FROM_date date Required Start date for this promotion.
UNTIL_date date Required End date for this promotion.
FILTER_LEVEL varchar2(50) Required Name of a level, for example "Product Family" or "SKU".
LEVEL_ORDER number(15) Required Use "1" for a location-type level or "2" for an item-type level.
FILTER_MEMBER varchar2(50) Required Description of a member of this level, for example "Low Fat".

Reference: Integration Interfaces

The information here is provided for reference and debugging purposes.

Oracle Demantra provides the following integration interfaces.

Import Promotion Levels

This integration interface is defined as follows:

Type: Import
Description: Imports rows from a staging table and adds the new members to the Promotion level. If the new promotions refer to combinations that are not yet present in this database, this interface creates those combinations as well.
Also imports rows from another staging table, which contains the population of these promotions.
Staging Tables: BIIO_Promotion stores the promotion members. Edit this table before editing BIIO_Population.
BIIO_Population stores the populations of the promotions.

Import Promotion Data2

This integration interface is defined as follows:

Type: Import
Description: Imports rows from a staging table and updates the promotion series data in the appropriate internal tables.
Staging Table: BIIO_IMP_PROMO_DATA

Configuring the Default Promotion Start and Span

To configure the default promotion start and span

  1. In the Business Modeler, click Parameters > System Parameters.

  2. Click the Worksheet tab.

  3. Edit the following parameters:

    PromoDefaultStart Specifies the default start date for promotions created within a worksheet: the current date, the last loaded sales date, or the worksheet start.
    PromoDefaultSpan Specifies the default length of time for promotions created within a worksheet.

  4. Click Save.

  5. Click Close.

Changing the Default Retailer Profile

Predefined Behavior

For any given retailer, the user can specify attributes or can instead use the default retailer profile. The CopyRetailerDefaults workflow checks for any retailers that use the default profile, and it copies the default details to those retailers. You should run this workflow each time you change a retailer to use the default profile and each time you change the default profile.

Possible Changes

You can change the default retailer profile, as follows:

  1. In the Business Modeler, click Configuration > Configure Levels.

  2. Right-click the Retailer level and select Open > General Attributes.

    Business Modeler displays the default attributes for the retailer level. Together, these constitute the "default retailer profile."

  3. Click a retailer attribute on the left.

  4. In the right area, change Default Value.

  5. Continue with other attributes as needed.

  6. When you are done, click Next and then click Finish.

  7. Restart the Application Server to make the changes available to the users.

Configuring the Right-Click "Open With" Option

By default, when users right-click a promotion, scenario, or retailer within PTP, they can use the Open and Open With menu options to open a worksheet that is filtered to that selection (the Open option opens the default worksheet). This is configurable within the Business Modeler.

To configure the "Open With" menu option

  1. In the Business Modeler, click Components > Create/Open Component. Or click the Create/Open Component button.

  2. Click Oracle PTP, and then click OK.

  3. Click Next repeatedly until the Select Component Queries for Levels screen is displayed.

    This screen allows you to associate public worksheets with levels.

    the picture is described in the document text

    This association is used in two ways:

    • Within the Members Browser, a user can use the right-click menu to open any of these associated worksheets directly from a member of the level (via the Open With menu option). In this case, Demantra opens the associated worksheet. The worksheet is filtered to show only data relevant to the member.

    • Within the worksheet designer, users can add a sub tab to a worksheet; the sub tab shows details for a given member. The sub tab can display any of the worksheets that are associated with a level included in the main worksheet. The sub tab is filtered to show only data relevant to the member.

  4. To associate a worksheet with a level, do the following:

    1. Click the level in the Select Level drop down menu.

    2. Double-click the worksheet in Available Queries list, which moves it to the Selected Queries list.

    3. Move other worksheets from the Available Queries list to the Selected Queries list, as needed.

    4. Decide which worksheet in the Selected Queries list should be the default worksheet for this level. For that worksheet, click the Default check box. When the user right-clicks and selects Open, this is the worksheet that will be used.

  5. When you are done on this screen, click OK.

Removing Right-Click Menu Options

The options on the right-click menu are Oracle methods. You can remove these options if needed; for example, if your system is not using optimization, you might want to remove the optimization options.

To modify a level method

  1. In the Business Modeler, click Configuration > Configure Methods.

    The system displays a screen showing the existing methods, including all the predefined methods.

  2. Optionally click a level name (such as Promotion, Scenario, or Retailer) in the drop-down list at the top of the screen.

    The screen is re-displayed with only the methods associated with that level.

  3. To hide this menu option, deselect the Display in menu check box.

  4. Click Finish.

Replacing Demantra Local Application Graphics

The Web-based Demantra products contain default images that you can replace with your organization's own designs. To do so, just back up the default images and substitute your own image files, giving them the same filenames as listed here.

The graphic files are in the following directory:

Demantra_root/Collaborator/portal/images

You can replace any of the graphics files in this directory. If you replace the default graphics with other graphics that have the same width and height, those graphics will fit on the page without the need for any further editing.

Demantra Local Application Splash Screen

The splash screen uses the graphic collaborator_splash.gif.

Demantra Local Application Login Page

On the login page, the most commonly replaced images are the following:

On the login page, the most commonly replaced images are the following:

the picture is described in the document text

Demantra Local Application Main Page

Demantra Local Application Main Page

On the main page, the most commonly replaced images are as follows:

the picture is described in the document text

Configuring Promotion Status Behavior

You can redefine the behavior of the promotion statuses in PTP.

Caution: You should not do this unless you are familiar with series definitions and with the Business Modeler.

Predefined Behavior

Demantra provides predefined promotion statuses (the Status attribute) and behavior, which PTP uses indirectly. PTP internally uses the Demantra predefined statuses but instead displays its own set of statuses (the Evt Status series).

Specifically the Status series maps the PTP statuses to the internal statuses. To do this mapping, this series has a client expression as follows:

if ( (evt status = 1), 1, if ( (evt status < 7), 2, 6))

This expression checks the value of the Evt Status series and decides the internal status value to which it maps. The resulting value is saved to the update field for the Status series, which is promotion.status. This is the field that controls whether a promotion is editable.

The following table lists the PTP status, the internal hardcoded statuses and their behavior, and explains the added TPMO behavior:

Evt Status series Promotion.Status field Hardcoded behavior Additional Trade Promotions behavior
1 (a. Unplanned) 1 (Unplanned) Analytical Engine ignores this promotion. Promotion does not affect fund balances or the forecast.
2 (b. Planned) 2 (Cancelled) . The cost of the promotion is deducted from the available balance of funds.
3 (c. Approved) .  
4 (d. Committed) . Via a PTP procedure, PTP takes a snapshot of the current state of the promotion, for use in later analysis. The committed promotions are included in all projections.
5 (e. Partial Paid) .
6 (f. Paid) . .
  3 (Planned)
4 (Committed)
5 (Executed)
PTP does not use these hard coded status values.		 See "Automatic Advancement of Promotion Status". .
7 (g. Closed) 6 (Closed) User cannot edit the promotion. .

Notes

Possible Changes

Re-configuring Series if Syndicated Data Is Not Used

Demantra uses syndicated data that includes the following breakdowns:

If this data is not available, it is necessary to reconfigure some PTP series. You may also need to modify some PTP procedures and triggers.

Caution: You should not do this unless you are thoroughly familiar with the Demantra platform.

Series to Reconfigure

The following series directly use the syndicated breakdowns via client expressions:

Each of these series has a client expression of the following form:

if past end date = 1, use syndicated data; otherwise, use engine data

(For each promotion, the Past End Date series equals 1 if the promotion is past or equals 0 otherwise.) For example, the client expression for Base Evt $ Rtl is as follows:

if(past end date = 0,

if( isnull(Base Evt $ Rtl Fut),0, Base Evt $ Rtl Fut ),

if(isnull( Base Evt $ Rtl Act ),0, Base Evt $ Rtl Act ))

The Base Evt $ Rtl Act contains the syndicated data.

You can reconfigure these series by rewriting this client expression so that the series always uses engine data. Or you can modify the actuals series to contain different data, depending on your needs.

Also, several series refer directly to the syndicated incremental volume via server expressions. These include BDF Exp Ttl Act, Incr COGS $ Act, Incr Evt $ Act, and MDF Exp Ttl Act.

Data Synchronization

If you reconfigure any of these series, you should understand how PTP maintains the data for these series. PTP loads this data into sales_data. For performance reasons, this information is needed in the promotion_data table instead, so PTP uses procedures and triggers to copy this data to that table. The following section gives the details.

Syndicated Data in PTP

The following table summarizes where the syndicated data is loaded, where that data is synchronized, and what series use this data in the sales_data and promotion_data tables, respectively. For completeness, this table lists all the syndicated data, including required data and data that is not synchronized.

Information In the sales_data table In the promotion_data table
Field* Series Field** Series
Average retail price, always required item_price Avg Rtl sd avg_rtl_pd Avg Rtl
Cost of goods, always required sdata7 COGS sd cogs_pd COGS
List price, always required sdata8 List Price sd list_price_pd List Price
Shelf price, always required shelf_price_sd Shelf Price sd ed_price Shelf Price
Total volume, always required actual_quantity Actuals Ttl    
Base volume sdata5 Actuals Base    
volume_base_ttl*** Many series volume_base_ttl Many series
Base dollars BASE_EVT_DOL_RTL Base Evt $ Rtl sd base_evt_d_rtl_act Base Evt $ Rtl Act
Incremental volume sdata6 Actuals Incr incr_evt_vol_act Incr Evt Vol Act
Incremental dollars INCR_EVT_DOL_RTL Incr Evt $ Rtl sd incr_evt_d_rtl_act Incr Evt $ Rtl Act
% ACV ANY PROMO sdata9 % ACV ANY PROMO    
% ACV DISP sdata10 % ACV DISP    
%ACV FEAT sdata11 % ACV FEAT    
%ACV FEAT&DISPLAY sdata12 % ACV FEAT&DISPLAY    
%ACV TPR sdata13 % ACV TPR    
%ACV FREQSHOPPER sdata14 % ACV FREQSHOPPER    
*These fields in sales_data are loaded.
**These fields in promotion_data are maintained by procedures and triggers.
***This field in sales_data is maintained by procedures and triggers.

For information on procedures and triggers, see "Procedures".

Parameters Affecting Goal Function

Determine Optimization Focus (GOAL_FOCUS)

Determine Optimization Goal (OPTIMIZATION_GOAL)

Choosing between these three goal functions will affect the formulation of the expression that the optimization is maximizing. If Units is chosen, optimization will look for valid promotions which will generate the most lift while not exceeding constraints such as maximum budget and minimum margins.

If Revenue is chosen, the expression will incorporate the price paid for each unit sold. The price used will depend on whether the optimization is focused on a retailer or manufacturer.

When choosing Profit the optimized expression will include both incoming revenues and costs associated with the product and promotion.

Determine Buydown Behavior

Configuring Promotion Population Entry

Note: For additional details and required configuration tasks, refer to the Oracle Demantra Enhanced Support for Shipment and Consumption Planning white paper on My Oracle Support (support.oracle.com).

Configuring the Population Dates label

Population Attribute definitions are stored in the GROUP_ATTRIBUTES_POPULATION database table. The DATES_LABEL column specifies the label to display on the UI for the Population Dates. The default value is "Dates". To update this value use a database utility such as Oracle SQL Developer.

Configuring the height of the Population Panel

Population Attribute definitions are stored in the GROUP_ATTRIBUTES_POPULATION database table. The POPULATION_WIN_HEIGHT specifies the height to use on the UI for the Population Panel. The default value is 100. To update this value use a database utility such as Oracle SQL Developer.

CConfiguring Available Filter Levels

In many instances, promotions are created on a subset of all available Item and Location levels. However, in certain circumstances, listing all of the available Item and Location levels in the Edit Population window could make it cumbersome to find the specific levels you want. To make it easier to enter promotions, you can configure which Item and Location levels to display in the Available Filter Levels for Population list. For example, you might have access to Item, Brand, and Category levels, but you might never create a promotion on an entire Category.

Note: Populations can only be defined on Item and Location levels. If the configured Available Filters Levels contains a General Level that is not an Item or Location level, the filter is not applied and all Item and Location levels are displayed.

To configure the Available Filter Levels, use a database utility such as Oracle SQL Developer to populate the POPU_ATTR_VALID_FILTER_LEVELS database table. You need to insert one row into this table for each Level that you want listed as an Available Filter Level for a Population Attribute.

This POPU_ATTR_VALID_FILTER_LEVELS database table has the following columns:

The POPU_ATTR_VALID_FILTER_LEVELS database table is empty when initially installed. If there are no entries in this table for a Population Attribute, then all levels that the user has security to will be listed as Available Filter Levels.

Configuring Required Filter Levels

You determine whether a Filter Level is required by modifying the MANDATORY column of the POPU_ATTR_VALID_FILTER_LEVELS. A value of 1 means the Filter Level is required. A value of 0 means it is not.

To configure the Required Filter Levels, use a database utility such as Oracle SQL Developer.

Configuring the Maximum Members than can be selected for a Filter Level

You determine the maximum number of members that can be selected for a Filter Level by modifying the MAX_MEMBERS column of the POPU_ATTR_VALID_FILTER_LEVELS table.

To configure the Maximum Members, use a database utility such as Oracle SQL Developer.