Demantra Predictive Trade Planning to EnterpriseOne Integration

This chapter describes how Demantra Predictive Trade Planning (PTP) integrates with EnterpriseOne.

This chapter covers the following topics:

Overview

Important: The content in this chapter refers to the new integration available between J.D. Edwards EnterpriseOne and the Value Chain Planning product suite (which includes Oracle Demantra) using the Process Integration Pack (PIP) Oracle Value Chain Planning Integration to J.D. Edwards EnterpriseOne.

It does not refer to the older flat-file based integration between JD Edwards EnterpriseOne and Demantra.

Please contact Oracle Support Services for the certification status of thisPIP-based integration with Oracle Demantra version 7.3.

The Oracle Demantra Predictive Trade Planning (PTP) solution provides industry leading capabilities for companies that are primarily in the Consumer Goods industry, to plan, manage and optimize their trade promotion spending.

PTP Integration Builds On Demand Management Integration

PTP integration builds on existing Oracle Demantra Demand Management integration. Please read the chapter "Demantra Demand Management to EnterpriseOne Integration" first. All of the information there applies to integrating Predictive Trade Planning, except where specifically noted in this chapter. In particular, the following collections must be run prior to running the PTP collections:

  1. Collect Planning Data

  2. Collect Sales History

  3. Collect Price List and UOM

For more information about Oracle Demantra integration with the JD Edwards EnterpriseOne application suite, see the guides titled:

Batch-based Approach Using Flat Files

The integration between JD Edwards EnterpriseOne and Oracle Demantra is a batch-based approach utilizing flat files to exchange information. It is accomplished through the use of Oracle EBS collection procedures, Oracle Data Integrator, Oracle Demantra workflows and JD Edwards EnterpriseOne RunUBEXml procedures.

JD Edwards EnterpriseOne to Oracle Demantra

The APS collection procedure "Collect PTP Data" performs the following steps:

  1. Collects the EnterpriseOne extracts generated by the runubexml program.

  2. Passes the flat file through the Oracle Data Integrator where the data is transformed.

  3. Populates the Demantra interface tables.

  4. Runs an Oracle Demantra workflow that moves the information from the interface tables to the production tables.

Oracle Demantra to JD Edwards EnterpriseOne

The APS collection procedure "Publish PTP Results" performs the following steps:

  1. Runs a Demantra workflow to extract the required information from Demantra and send the data to a flat file.

  2. Passes the flat file through the Oracle Data Integrator where the data is transformed.

Architectural Process

Oracle EnterpriseOne and Oracle Demantra exchange information through the use of the Oracle Data Integrator (ODI) Adapter for Value Chain Planning, the Oracle Data Integrator (ODI) to transform the data, and Oracle Demantra integration interfaces and workflows as shown in the following diagram:

the picture is described in the document text

The integration processes can be run when required from APS Planning. After EnterpriseOne to Demantra Demand Management integration loads Demantra with sales history, price list and units of measure data, PTP data can be collected (list price, item cost and price history). The PTP data flows directly from EnterpriseOne to Demantra via ODI, where it is transformed.

Integration Points Overview

The following integration points are part of the integration between the Oracle Demantra Predictive Trade Planning (PTP) module and JD Edwards EnterpriseOne applications.

the picture is described in the document text

Business Process

The following diagram provides more details about the business process and data that flows between EnterpriseOne and Oracle Demantra PTP:

the picture is described in the document text

Note: The relevant EnterpriseOne extracts need to be run prior to launching the collection programs in APS.

The business process is as follows:

  1. In APS, run "Collect Planning Data" to collect master data to Oracle APS Planning (ODS).

    • Customer Hierarchy – Customer Master information is interfaced from J D Edwards EnterpriseOne to Oracle APS Planning (ODS), then to Oracle Demantra where it populates the Demantra Site Hierarchy. This process is part of the Demantra Demand Management to EnterpriseOne integration.

    • Branch Hierarchy – Branch master information is interfaced from J.D. Edwards EnterpriseOne to Oracle APS Planning (ODS), then to Oracle Demantra where it populates the Demantra Organization Hierarchy. This process is part of the Demantra Demand Management to EnterpriseOne integration.

    • Product Hierarchy – Product Master information is interfaced from J D Edwards EnterpriseOne to Oracle APS Planning (ODS), then to Oracle Demantra where it populates the Demantra Item Hierarchy. This process is part of the Demantra Demand Management to EnterpriseOne integration.

  2. In APS, run "Collect Sales History" to collect sales history from EnterpriseOne to Demantra.

    Sales Activity (Historical and Current) – Shipment information interfaced from EnterpriseOne to Demantra populates the Demantra sales data structure. This interface supports loading historical sales data as well as ongoing updates of current sales data. This process is part of the Demantra Demand Management to EnterpriseOne integration.

  3. In APS, run "Collect Price List and UOM" to collect pricing and units of measure from Oracle APS Planning (ODS). This procedure adds pricing and units of measure to the sales activity data already transferred from EnterpriseOne.

  4. In APS, run "Collect PTP Data" to collect list price, item cost and price history from EnterpriseOne.

    • List Price and Standard Cost – Future List Price and Standard Cost information interfaced from EnterpriseOne to Demantra populates the Demantra sales data structure. This information is used when planning future promotions.

    • Promotion History – Historical promotional information must be loaded into Demantra at the beginning of an implementation for the Demantra promotion modeling engine to predict the impact of future promotions. This information is expected to come from a legacy Trade Promotions Management system. The existing Demantra Integration Interface is used to load this information.

  5. In Demantra PTP, run the forecasting engine

    The Predictive Trade Planning forecasting engine generates baseline forecasts for the customer hierarchy and product hierarchy based on the sales activity and promotion history.

  6. In Demantra PTP, plan promotions.

    Practitioners use the Predictive Trade Planning module and process to plan future promotions.

  7. In APS, run "Publish PTP Results" to load future promotional pricing from Demantra to EnterpriseOne.

    Promotional Pricing (Off-Invoice and Bill-Back Allowances) – Promotional Pricing information interfaced from Demantra to the EnterpriseOne Advanced Pricing module applies correct price discounts during order management.

  8. In EnterpriseOne, process orders against new promotions.

  9. Extract Price History information from EnterpriseOne (PriceHistory.txt).

  10. In APS, run "Collect PTP Data" to load price history into Demantra, ensuring that the Price History parameter is set to "Yes"

    Actual Promotional Spending or Accruals – The actual amount spent (for off-invoice promotions) or accrued (for bill-back promotions) interfaced from EnterpriseOne to Demantra captures the actual cost of promotions.

  11. In APS, run "Publish Forecast to Source System" to load the Volume Forecast from Demantra to EnterpriseOne.

    Volume Forecast – Oracle Demantra generates a projected volume forecast and provides this information to JD Edwards EnterpriseOne. This process is part of the Demantra Demand Management to EnterpriseOne integration.

Promotion Method Configuration

Pre-seeded Workflows are provided for flagging promotions that need to be integrated to EnterpriseOne. During implementation the standard promotion methods must be updated to use these Workflows instead of the standard ones. The following configuration must be done in order to ensure that promotions are properly integrated to EnterpriseOne.

In Business Modeler, select “Configuration” and then “Configure Methods”. Select “Promotion” in the drop-down and then edit each of the following methods (double-click on the method in the list to edit it).

Modeling Considerations

Note: PTP integration builds on existing Oracle Demantra Demand Management integration. For information about Oracle Demantra Demand Management integration, see "Demantra Demand Management to EnterpriseOne Integration".

Levels

The following Oracle Demantra level supports EnterpriseOne to Demantra PTP integration:

Integration Status
Description Integration Status is a parent to the Promotion level. It is an indicator that is used to determine whether or not a promotion needs to be exported for EnterpriseOne.
Demantra Hierarchy Integration Status
- Promotion
Level Type General Level
Demantra Database Table promo_integ_status

Attributes

The following Oracle Demantra attribute supports EnterpriseOne to Demantra PTP integration:

promo_integ_status
Description This is a lookup level to the new Integration Status level.
Demantra Level Promotion
Demantra Database Table Promotion
Demantra Database Column promo_integ_status_id

Series

The following Oracle Demantra series support EnterpriseOne to Demantra PTP integration:

BB Amt
Description BB Amt is the actual Bill-Back Accrual amount as provided by EnterpriseOne
Data Table Promotion
Update Field bb_amt
Data Type Numeric
OI Amt
Description OI Amt is the actual Off-Invoice Spending amount as provided by EnterpriseOne
Data Table Promotion
Update Field oi_amt
Data Type Numeric
Promotion Desc
Description Promotion Desc is a series that points to the existing description field on the Promotion level. Required for exporting Promotion Description using a Data Profile.
Data Table <level> promotion
Update Field N/A
Data Type String
Promotion Pay Indicator
Description The Promotion Pay Indicator shows whether the Promotion Rate Amount is Off-Invoice (“O”) or Bill-Back (“B”). Used in the Promotional Pricing export for EnterpriseOne.
Data Table Promotion
Update Field promo_pay_indicator
Data Type String
Promotion Rate Amt
Description This is the Promotion Rate Amount that is exported for EnterpriseOne. This equals the existing Buy-Down series multiplied by negative one. (EnterpriseOne requires promotional discounts to be represented as negative numbers.)
Data Table Promotion
Update Field promo_rate_amt
Data Type Numeric
Promotion Integration Status
Description Promotion Integration Status is a series that points to the new promo_integ_status attribute on the Promotion level.
Data Table <Level> promotion
Update Field promo_integ_status_id
Data Type Numeric

Promotion History Load

During the initial implementation of Demantra PTP, it is recommended that two years of promotional history is loaded. It is unlikely that this information will reside in EnterpriseOne. As such, no automated feed of historical promotions from EnterpriseOne to Demantra is provided. Historical promotions should be loaded from the applicable source using the standard Demantra promotional load process.

Customer Hierarchy Load

When the customer hierarchy data is loaded into Demantra during Demand Management integration, the Retailer and Bill To levels in Demantra are populated with relevant data from EnterpriseOne. The Retailer level is populated with the Parent Address Number (into the code field of the level) and Parent Address Name (into the code description field of the level). If the Parent Address Number and Parent Address Name are null in EnterpriseOne, the Customer Code and Customer Name are populated instead.

For a PTP implementation, it is recommended that the Parent Address Number and Parent Address Name fields in EnterpriseOne be populated. Bill To level is populated with the Bill To Number (into the code field of the level) and the Bill To Name (into the description field of the level).

Mapping

Note: PTP integration builds on existing Oracle Demantra Demand Management integration. For information about Oracle Demantra Demand Management integration, see Demantra Demand Management to EnterpriseOne Integration.

There are four files transferred between Oracle Demantra PTP and EnterpriseOne:

Note: You may need to modify the default values of time filters defined in the integration interface data profiles to suit your implementation needs.

Pricing History

The purpose of the Price History extract is for EnterpriseOne to provide the actual amount spent (for off-invoice promotions) or accrued (for bill-back promotions). This captures the actual cost of promotions in Demantra. Details:

EnterpriseOne Extract Name: PriceHistory.txt

ODI Package Name: LoadE1PriceHistoryDataToDMPkg

Demantra Integration Interface Names:

Demantra Integration Data Profile Names:

Demantra Workflow: AIA-E1ToPTP_PriceHistory_Download

To prevent loading the same amount multiple times, the load process checks for duplicate records in prior loads. If a record has already been loaded, it is bypassed during the current load. This allows overlapping EnterpriseOne extract date ranges to be used without overstating the amounts in PTP. Duplicate records are determined by an exact match on the following eleven fields in the EnterpriseOne extract file:

Caution: The Price History load requires that the promotion, for which the actual off-invoice spending or bill-back accrual amount applies, already exists in Demantra. In the case of historical information, if the corresponding promotions have not been loaded into Demantra, this load will generate errors.

Historical promotional spending is not a requirement for Demantra. Therefore no recovery is required if the historical promotional spending load does not complete successfully.

The Price History extract fields are transformed by ODI to match the format of the fields in the new integration and then the file is loaded into Demantra. The layout of the Price History extract from EnterpriseOne is as shown:

EnterpriseOne
Extract Field Name		 Field Type and (Value) Demantra Mapping
Document
(Order Number, Invoice)		 Number (8) Used to check for duplicates
Order Type Varchar2 (2) Used to check for duplicates
Order Company
(Order Number)		 Varchar2 (5) Used to check for duplicates
Line Number Number (6,3) Used to check for duplicates
Short Item Number Number (10) Maps to the Item level
Customer No Ship To Number (10) Maps to the Site level
Business Unit Varchar2 (200) Maps to the Organization level
Actual Ship Date Date Maps to the Time Period
Promotion ID Varchar2 (12) Maps to the Promotion level
Cost Type Char "O" for Off-Invoice
"B" for Bill-Back
Amount - Extended Price Number (15,15) The actual extended amount.
Maps to "OI Amt" if the Cost Type equals "O".
Maps to "BB Amt" if the Cost Type equals "B".
Order Suffix Varchar2 (3 Used to check for duplicates
Price History Alternate Key Number (15) Used to check for duplicates
Price History Alternate Key So Varchar2 (2) Used to check for duplicates
Sequence Number Number (4) Used to check for duplicates
Sub-Sequence Number Number (4) Used to check for duplicates
Tier Number (2) Used to check for duplicates
Target Application Char (1) Used to check for duplicates

List Price

The purpose of this interface is to load Manufacturer’s List Prices for future time periods. List Price is used to calculate profitability when planning future promotions. Details:

EnterpriseOne Extract Name: ListPrice.txt

ODI Package Name: LoadE1ListPriceDataToDMPkg

Demantra Integration Interface Names:

Demantra Integration Data Profile Names:

Demantra Workflow: AIA-E1ToPTP_PromoPrice_Download

Date Range

EnterpriseOne provides this information with an Effective Date and an Expiration Date. During load processing, this date range is converted into individual time periods to align with the Demantra data structure. For example, using a weekly model a date range of January 1, 2008 through December 31, 2008 would be converted into 52 individual week entries.

EnterpriseOne has the capability to define customer specific prices or global prices that apply to all customers. If the Customer Number field is null, then the List Price applies to all customers.

  1. The load processing will first load all global prices where the Customer Number field is null. In Demantra this will be stored for all active Site / Item / Organization combinations for each of the time periods within the date range.

  2. The load processing then loads any customer specific prices for all active Item / Organization combinations for the customer for each of the time periods within the date range.

This two-step process will overlay the global prices initially loaded with any customer specific prices.

The List Price extract fields are transformed by ODI to match the format of the fields in the new integration and then the file is loaded into Demantra. The layout of the List Price extract from EnterpriseOne is as shown:

EnterpriseOne
Extract Field Name		 Field Type and (Value) Demantra Mapping
Short Item Number Number (8) Maps to the Item level.
2nd Item Number Varchar2 (25) Not Used
3rd Item Number Varchar2 (25) Not Used
Branch/Plant Varchar2 (12) Maps to the Organization level
Customer Number Number (8) Maps to the Site level. If null, then List Price applies to all customers.
Expired Date Date The date through which the List Price is valid.
Effective Date Date The date the List Price goes into effect.
Amount - List Price Calculated The List Price.

Standard Cost

The purpose of this interface is to load Manufacturer’s Cost Of Goods Sold (COGS) values for future time periods. Details:

EnterpriseOne Extract Name: ItemCost.txt

ODI Package Name: LoadE1ItemCostDataToDMPkg

Demantra Integration Interface Name: LoadCostGlobal

Demantra Integration Data Profile Name: CostGlobal

Demantra Workflow: AIA-E1ToPTP_PromoCost_Download

COGS is used to calculate profitability when planning future promotions. COGS is stored in the existing “COGS SD” series which resides on the Sales Data table in Demantra. This table contains an entry for each unique Site / Item / Organization / Time Period combination. COGS information from EnterpriseOne does not include Effective or Expiration Dates. When received it is assumed to take effect immediately and stay in effect through the last future date for which data is stored in the application.

EnterpriseOne does not have the capability to define customer specific COGS values. The COGS value applies to all customers. In Demantra, data are stored for all active Site / Item / Organization combinations for each of the future time periods

The COGS extract fields are transformed by ODI to match the format of the fields in the new integration and then the file is loaded into Demantra. The layout of the COGS extract from EnterpriseOne is as shown:

EnterpriseOne
Extract Field Name		 Field Type and (Value) Demantra Mapping
Short Item Number Number (8) Maps to the Item level.
Branch/Plant Varchar2 (12) Maps to the Organization level
2nd Item Number Varchar2 (25) Not Used
3rd Item Number Varchar2 (25) Not Used
Item Cost Calculated The COGS value
Cost Method Number (8) Not Used
Cost Method Description Varchar2 (30) Not Used

Promotional Pricing (Off-Invoice and Bill-Back Allowances)

The purpose of this interface is to provide promotional pricing information to EnterpriseOne to be used in the Advanced Pricing module. Details:

Extract Names:

ODI Package Name: LoadDMPromotionPricingDataToE1Pkg

Demantra Integration Interface Name: AIA-E1UploadPromoPrices

Demantra Integration Data Profile Name: AIA-ExtractPromoPrices

Demantra Workflow: AIA-PTPTOE1_UploadPromotionPrices

Promotions are exported from Demantra if they meet the following criteria:

The following table defines the layout of the Promotional Pricing file and maps the fields to the corresponding fields in Demantra:

EnterpriseOne
Field Name		 EnterpriseOne
Field Type		 Demantra
Type		 Demantra
Level or Series
Comments
Promotion Row ID Number (15) Level Promotion Demantra Promotion ID
Promotion Name String (50) Series Promotion Desc Demantra Promotion Description
Customer Number Number (10) Level Site EnterpriseOne Ship-To Customer Number
Customer Number Description String (30)   Null Value Blank
Short Item Number Number (10) Level Item EnterpriseOne Item Number
Item Number Description String (30) Level Item Description EnterpriseOne Item Description
Promotion Effective Date Date Series Start Ship Starting Ship Date of Promotion
Promotion Expiration Date Date Series End Ship Ending Ship Date of Promotion
Promotion Amount Number (15) Series Promotion Rate Amt The Promotion Buy-Down Rate multiplied by -1 (EnterpriseOne requires that price discounts are negative values)
Cost Type Char Series Promotion Pay Indicator Payment Type: “O” for Off-Invoice / “B” for Bill-Back. This indicates whether the Promotion Amount is off-invoice or bill-back.

When new promotions are sent to EnterpriseOne, this file contains a record for every Account and Item combination on the promotion. Regardless of the customer or product level that the promotion is created at in Demantra, the information will always be sent to EnterpriseOne at the Account location level and the Item product level. This lowest level is typically the Ship-To Customer / SKU level. If a promotion is deleted in Demantra after it has been sent to EnterpriseOne, the following entries are sent to EnterpriseOne, which will cause the promotion expire in EnterpriseOne:

If a promotion is modified in Demantra after it has been sent to EnterpriseOne, a set of delete entries (as described previously) will first be sent to EnterpriseOne followed by a set of new promotion entries. This will cause the promotion to be replaced in EnterpriseOne.

Note: Promotional changes are handled by completely replacing the promotion in EnterpriseOne. There is no logic to provide delta changes to EnterpriseOne.

Promotional Pricing Workflow

The AIA-PTPTOE1_UploadPromotionPrices workflow creates and processes two versions of the Promotional Pricing file. The first pass contains only delete entries. These are for promotions that were deleted, or the delete portion for promotions that were modified. The second pass contains only new entries. These are for new promotions, or the new (replacement) portion for promotions that were modified.

Interfaces

Database stored procedures are used to generate the delete promotions version of the Promotional Pricing file. The new promotions version of the Promotional Pricing file is generated using the Integration Interface AIA-E1UploadPromoPrices. It uses the AIA-ExtractPromoPrices Data Profile to export the information based on the criteria defined previously.

Two versions of the promotional pricing export with EnterpriseOne are supported to identify modified and deleted promotions. The details are as follows:

  1. The modified and deleted promotions are identified using the "Integration Status" level, which has the following members: New Promotion, Edited Promotion and No Changes to Promotion.

  2. A stored procedure step at the end of each of the following existing workflows:

    • New Promotion – sets the promotion to belonging to the Parent Level “New Promotion” (also sets the last_update_date for the promotion level to sysdate)

    • Edit Promotion – stores the Promotion ID, Code and Description of the edited promotion in a database table , as well as sets the promotion to belonging to the Parent Level “Edited Promotion” (also sets the last_update_date for the promotion level to sysdate)

    • Delete Promotion – stores the Promotion ID, Code and Description of the deleted promotion in a database table

  3. A series called “Promotion Integration Status” captures changes to any of the following series which impact the EnterpriseOne integration:

    • Start Ship Date

    • End Ship Date

    • Buy-down Rate unit

    • Payment Type

    This series has a client expression with an “is modified” expression used to capture changes to the 4 series above, if they are changed a the “Integration Status” Promotion Level is set to “Edited Promotion”.

  4. At the end of the workflow, after all export processes have run, the “Integration Status” is set to “No Changes to Promotion” for all promotions.