Demantra Demand Management to EnterpriseOne Integration
This chapter describes how Demantra Demand Management (DM) 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.
This chapter explains the integration processes that synchronize or move data between the Oracle Demantra and EnterpriseOne applications.
Oracle Demantra Demand Management provides access to EnterpriseOne historical sales data, returns, and other reference data organized into multiple hierarchies that reflect the needs of your organization.
Integration between JD Edwards EnterpriseOne and Oracle Demantra Demand Management is supported by a combination of xml and flat-files that are transformed by the Oracle Data Integrator (ODI) to meet the requirements of Oracle APS Planning and Demantra Demand Management into an intermediate file structure. The intermediate file is then imported into the Oracle Demantra Demand Management data model using workflows.
Forecasts are generated and then approved within Demand Management. This process may be iterative in nature, and allows for manual intervention before finalizing the forecast. At this point, the forecast is extracted into the intermediate file structure, and in turn imported into the EnterpriseOne data model via ODI in the existing forecast table. Optionally, customers using other VCP applications like Advanced Supply Chain Planning can publish the generated forecast to the VCP tables using the standard Demantra workflows.
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 integration processes can be run when required from Oracle EBS Advanced Planning.
Integration Points Overview
The following integration points are part of the integration between the Oracle Demantra Demand Management module and EnterpriseOne applications.
Business Process
The following diagram shows the flow of data between EnterpriseOne and Demantra Demand Management:
Note: The relevant EnterpriseOne extracts need to be run prior to launching the collection programs in APS.
The process is as follows:
-
From APS, run Collect Planning Data. This loads ODS with planning information from EnterpriseOne including customer, branch and product information.
-
From APS, run Collect Sales History. This program processes the SalesOrderHistory.txt file extracted from EnterpriseOne, transforms it and loads it into Demantra.
-
From APS, run Collect Price List and UOM. This loads the price list and UOM information into Demantra.
-
Within Demantra, create a forecast based on the sales history gathered from EnterpriseOne.
-
From APS, run Publish Forecast to Source System. This generates a flat file named forecast.txt and loads it into EnterpriseOne.
-
(Optional) The forecast can also be published for use by other VCP applications using the Demantra workflows "EBS Upload Local Forecast" and "EBS Upload Local Fcst, Demand Class".
For more details about how to implement EnterpriseOne integration with Demantra Demand Management, see:
-
Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Installation Guide
-
Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Implementation Guide
Mapping
There are two files transferred between Oracle Demantra Demand Management and EnterpriseOne after the Collect Planning Data procedure has been run from APS Planning (which loads Customer, Branch and Product information into ODS/PDS, as well as other planning data). They are:
-
Sales Order History (EnterpriseOne to Demantra)
-
Forecast (Demantra to EnterpriseOne)
Sales Order History
The purpose of the Sales Order History extract is for EnterpriseOne to provide either full or incremental sales order history to Demantra Demand Management. Details:
EnterpriseOne Extract Name: SalesOrderHistory.txt
Transformed File Name: DemHistory.dat
ODI Package Name: LoadE1SalesOrderHistoryDataToDMPkg
Demantra Workflow: EBS Full Download
See the Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Implementation Guide for more information about how to change the download of the sales order history extract from full to incremental.
Sample layout of the SalesOrderHistory.txt file from EnterpriseOne:
| EnterpriseOne Field | Example |
|---|---|
| Order Number | 3363 |
| Order Type | SO |
| Order Company | 200 |
| Order Line No | 1 |
| Business Unit | TORONTO |
| Customer No Sold To | 1744 |
| Customer No Ship To | 1744 |
| Short Item Number | 9797760 |
| 2nd Item Number | OCLV_110_DROP_OUT |
| 3rd Item Number | OCLV_110_DROP_OUT |
| Actual Ship Date | |
| Invoice Date | |
| Quantity | 54 |
| Requested Date | 5/8/2006 |
| Scheduled Pick Date | 5/8/2006 |
| Promised Shipment Date | 5/8/2006 |
| Promised Delivery Date | 5/8/2006 |
| Price Per Unit | 105.62 |
| Company | 200 |
When you launch the "Collect Sales History" program from the "Advanced Supply Chain Planner" or the "Demand Management System Administrator" responsibility in Oracle EBS, the SalesOrderHistory.txt file is transformed by ODI into the format required by Demantra.
The format required by Demantra follows the format of the DemHistory.dat file that is used to load sales history information into Demantra using the legacy collections framework as follows:
| DemHistory.dat Fields | Example | Mapping to SalesOrderHistory.txt |
|---|---|---|
| ACTUAL QTY | 54 | Quantity field from SalesOrderHistory.txt.
Note: For a given combination of item, business unit (branch in EnterpriseOne, organization in Oracle), customer ship to and requested date, if there are multiple records in SalesOrderHistory.txt then the quantities are aggregated and a single record is created in DemHistory.dat. |
| SALES_DATE | 8-May-06 | Requested Date from SalesOrderHistory.txt |
| DM_ITEM_CODE | 9797760 | Short Item Number field from SalesOrderHistory.txt |
| DM_ORG_CODE | TORO1 | Business Unit field from SalesOrderHistory.txt
Note: As there is a restriction in the field size for the organization code in the Value Chain Planning ODS tables, the business unit name of "TORONTO" in this example is translated into "TORO1" table used in the new integration. Refer to "Creating User Maintained Data" in Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Implementation Guide |
| DM_SITE_CODE | Olympia Sports Inc:3150:3150+Olympia Sports Inc:OU1 | Customer.xml:customer:parentName+Parameter.Demantra_Field_Delimiter+Customer.xml:customer:parentAddressNumber+Parameter.Demantra_Field_Delimiter customer.xml:customer:customerCode_Parameter.Field_Delimiter_Customer.xml:customer.name_Parameter.Demantra_Field_Delimiter_OPERATING_UNIT
For more details on the below fields in User Defined Integration Data.xml, please refer to "Creating User Maintained Data" in Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Implementation Guide :
|
| EBS_SALES_CHANNEL_CODE | DRT | Value of the customer category code in Customer.xml pointed to by the parameter Sales_Channel_Category in the Parameters table in User Defined Integration Data.xls, where Customer.xml:customer:customerCode = "Customer No Ship To" in SalesOrderHistory.txt. |
| EBS_DEMAND_CLASS_CODE | EST | Value of the customer category code in Customer.xml pointed to by the parameter Demand_Class_Category i nthe Parameters table in User Defined Integration Data.xls, where Customer.xml:customer:customerCode = "Customer No Ship To" in SalesOrderHistory.txt. |
| EBS_BOOK_HIST_BOOK_QTY_BD | 54 | Quantity field from SalesOrderHistory.txt. |
| EBS_BOOK_HIST_REQ_QTY_BD | <null> | |
| EBS_BOOK_HIST_BOOK_QTY_RD | <null> | |
| EBS_BOOK_HIST_REQ_QTY_RD | <null> | |
| EBS_SHIP_HIST_SHIP_QTY_SD | <null> | |
| EBS_SHIP_HIST_SHIP_QTY_RD | <null> | |
| EBS_SHIP_HIST_REQ_QTY_RD | <null> | |
| EBS_PARENT_ITEM_CODE | <null> | |
| EBS_BASE_MODEL_CODE | <null> |
Forecast
When you run the "Publish forecast to source system" program from the Advanced Supply Chain Planner or the Demand System Administrator responsibility in APS, the forecast from Demantra is exported out into a file called forecast.txt and is transformed to the format required by EnterpriseOne. Details:
Demantra Extract Name: forecast.txt
Note: ODS is case-sensitive to file names; ensure that forecast.txt is all lower case.
ODI Package Name: LoadDMForecast
Demantra Integration Interface Name: AIA-E1 Upload
Demantra Integration Data Profile Name: AIA-Forecast data
Demantra Workflow: AIA-Forecast_Export
The forecast.txt file is transformed by ODI and then loaded into EnterpriseOne. The layout of the forecast.txt file from Demantra is as shown:
| Field Name | Sample Data |
|---|---|
| Short Item Number | 9797760 |
| Branch/Plant | TORONTO |
| <Blank Field> | |
| Sales Date | 4/7/2008 0:00 |
| Customer No Sold To | 1744 |
| Final Forecast | 80 |
The forecast is exported out of Demantra into forecast.txt at an Item, Organization and Customer site level. The customer site value exported out of Demantra follows the concatenated format that is used in EBS-Demantra collections. See "Sales Order History" for more information about the format of the DM_SITE_CODE field in DemHistory.dat mapping table. ODI transforms the value of the customer site to the EnterpriseOne format.