This chapter discusses configuration steps that must be completed before using Oracle Demantra with this integration. The steps include:
Set up database directories
Run concurrent programs
Update profiles
Add additional Demantra levels (optional)
This chapter covers the following topics:
Important: The VCP schema and the Demantra schema must reside on the same database instance.
In this integration, Demantra workflows look for inbound flat-files and generate outbound flat-files on the Demantra database server. Run the following script in the Demantra schema to indicate the location of these files on the database server:
begin data_load.setupSystemObjects('V_PATH'); end; / exit /
where V_PATH is the path where ODI files are stored.
Note: Alternatively you can execute the AIA-Create_Database_Directory with the appropriate parameters.
For inbound integration to Demantra, workflows pick up ODI transformed files from this location and loads them into Demantra.
For outbound integration from Demantra, workflows extract data from Demantra and place the output flat-files in this location.
Running this script creates three entries in the ALL_DIRECTORIES table in Oracle:
DAT_DIR: The location of the flat-files: For example: DAT_DIR = C:/E1 Files.
LOG_DIR: The location of the log files. For example: LOG_DIR = C:/E1 Files/log.
BAD_DIR: The location of bad files. For example, BAD_DIR = C:/E1 Files/bad.
Note: You must manually create the LOG_DIR and BAD_DIR directories and provide read and write access to the LOG_DIR, BAD_DIR, and DAT_DIR directories.
In a shared directory configuration, the DAT_DIR directory must be shared with the DemInputDir and DemOutputDir directories.
In a non-shared directory configuration, the DAT_DIR directory is basically the DemTargetDir (ODI Variable: PVV_DEM_TARGET_DIR) and DemSourceDir (ODI Variable: PVV_DEM_SOURCE_DIR).
This section discusses running concurrent programs to initialize the EBS to Demantra integration. The first time you run the VCP-Demantra collections, perform the following steps:
Run concurrent programs to initialize the EBS-Demantra integration by navigating to Demand Management System Administrator Responsibility, Other, Requests, Submit a New Request, Single Request.
Run the Update Synonyms concurrent request.
Run the Configure Legacy Profiles concurrent request.
Specify the instance code, master organization and the category set name.
Run the concurrent request Cleanup Entities in Use concurrent request.
It is not recommended to have multiple Demantra schemas on the same database instance.
The Master organization is a branch that contains all items and their category codes, and is used by planning or forecasting in every branch of a model.
Additional Information: For additional information, see Designating a Master Branch.
To update profiles
Navigate to System Administrator Responsibility, Profile, and System.
Set the MSD_DEM: Host URL profile option to the correct Demantra Application Server url.
Because CTO is not supported, the MSD_DEM: Include Dependent Demand profile option should be set to No.
Set the MSC: E1 Concatenation Character profile option to the correct delimiter character in the generated flat files.
The default value is +.
If you are using Demantra Predictive Trade Planning or Demantra Deductions and Settlement Designer, then you must modify the data mapping for the Retailer level.
To update the Demantra data model
Open Demantra Business Modeler.
Navigate to Data Model, Open Data Model.
Select the data model DM/S&OP
Click OK.
Click Next until you get to the Data Model Design form.
Click Site level and select the lr2a level.
Click lr2a_desc.
Change the field name from t_ep_lr2a to t_ep_lr2a_desc.
Click Next.
Click Build Model.
Important: : Do not click Build New Model.
Click Upgrade Existing Model.
Click OK.
This integration uses the EBS legacy collections framework to load sales history information into Oracle Demantra. The level hierarchies supported in this integration are a sub-set of the level hierarchies supported by the integration between an EBS ERP source and Demantra.
In the Item hierarchy, the following levels are supported for this integration:
Item > Product Category > All
Item > Product Family > All
Demand Class > All
In the Location hierarchy, the following levels are supported for this integration:
Site > Account > Customer > Customer Class
Organization > Operating Unit
Sales Channel > All
Organization > Legal Entity
Note: Use the parameters.txt file to configure the data field that is mapped to the levels Product Category, Demand Class, Operating Unit and Sales Channel levels.
Additional Information: For additional information, see Appendix A: Parameters Table.
VCP Base Pack Integration loads data into pre-seeded Demantra levels. ERP category codes are mapped to pre-seeded levels defined in the Parameters tab in the Manage Integrations Parameters UI.
Additional Information: For more information, see Creating User-Maintained Data
Depending on your business needs, you might want to load additional category codes into additional levels in Demantra.
To load data into additional Demantra levels
Configure the ERP system to extract the additional category codes into Customer.xml and Base.xml.
Extend ODI to load the additional category codes from the Customer.xml and Base.xml files into the custom tables.
Note: Extending ODI might require a design-time license for ODI. As an alternate approach, you can parse the XML files and populate custom tables using PL/SQL procedures. These custom procedures can be invoked from the post-process custom hook procedure (COL_PLAN_DATA_POST_PROCESS) called after Collect Planning Data.
Use Demantra's custom hooks functionality to load the additional category codes from the custom tables into Demantra.
The following diagram is an example of how can be used to load additional category code data into Demantra.
Extensions to standard ODI mappings load additional category codes to custom tables in the ODI flow.
MSD_DEM_CUSTOM_HOOKS maps additional category codes to Demantra staging table columns.
Configuring JDE E1 to Extract Additional Category Codes
You can configure JDE E1 to extract additional category codes into Customer.xml and Base.xml. Integration Constants (P34A10) can be used to select category codes for JD Edwards EnterpriseOne extraction.
Navigate to Form > Group Sets.
Select the Item Category codes, Customer Category codes and Branch Category codes.
The selected category codes are extracted into Base.xml for items and branches or into Customer.xml for Customers.
Configuring PeopleSoft to Extract Additional Category Codes
Item group mappings and customer group mappings can be used to extract Item and Customer categories.
Additional Information: For additional information, see Item Group Mappings or Customer Group Mappings.
Extending ODI to Load Additional Category Codes
This integration populates standard ODI scenarios with specific category codes from JD Edwards EnterpriseOne into VCP ODS and then into Demantra. However, you can extend ODI to load additional category codes from Customer.xml into custom tables and Base.xml.
To do this, ODI must be customized by defining a custom ODI scenario and invoking it in the POSTPROCESSHOOKPKG ODI package of the Collect Planning Data program.
Define a new interface object in ODI. This object uses the xml files from JD Edwards EnterpriseOne (Base.xml for item and branch and Customer.xml for customer) as the source data store and the custom table as the target data store.
You might need to define a separate interface with each of the custom tables as the target data store (one table for item category codes, one for branch category codes, and one for customer category codes).
Define a new ODI scenario and include the new interfaces in this custom scenario.
Invoke the custom ODI scenario in the POSTPROCESSHOOKPKG ODI package when the concurrent program Collect Planning Data is invoked.
Additional Information: For additional information, see Optional User-Defined Customizations.
VCP Base Pack loads data into pre-seeded Demantra levels. You can use custom hooks to load data into from custom tables into additional Demantra levels. Custom hooks are invoked by the Collect Sales History concurrent program before the EBS Full Download workflow is launched. Data is then loaded from Demantra staging tables into Demantra base tables.
To use custom hook functionality to load data from custom tables into additional Demantra levels
Add custom code to in the APPS.MSD_DEM_CUSTOM_HOOKS package.
Run the ITEM_HOOK, LOCATION_HOOK procedures.
Use the ITEM_HOOK procedure to load data for new item levels
Use the LOCATION_HOOK procedure to load data for new organization and customer levels
T_SRC_ITEM_TMPL Demantra Staging Table
Data for additional levels in the item hierarchy can be stored in the T_SRC_ITEM_TMPL Demantra staging table.
T_SRC_ITEM_TMPL has several placeholder columns such as E1_ITEM_CATEGORY_1 to E_ITEM_CATEGORY_23. These columns can be used to load data for new item hierarchy levels.
In the shipped settings, seven columns are mapped to placeholder levels such as Item Category Code 1 and Item Category Code 2. If you need to use more than seven levels, update the data model to map the columns in T_SRC_ITEM_TMPL to new levels.
T_SRC_LOC_TMPL Demantra Staging Table
Data for additional levels in the organization hierarchy can be stored in the T_SRC_LOC_TMPL Demantra staging table.
T_SRC_LOC_TMPL has several placeholder columns such as E1_BRANCH_CATEGORY_1 to E1_BRANCH_CATEGORY_30. These columns can be used to load data for new organization hierarchy levels.
In the shipped settings, five columns are mapped to placeholder levels such as Branch Category Code 1 and Branch Category Code 2. If you need to use more than five levels, update the data model to map the columns in T_SRC_LOC_TMPL to new levels.
The data for the additional levels in the organization hierarchy can be stored in the Demantra staging table T_SRC_LOC_TMPL. The data for the additional levels in the customer site hierarchy can be stored in T_SRC_LOC_TMPL.
Refreshing the Data Model
Each time new levels are added, you must refresh the data model to ensure that the new levels have been assigned to the correct relevant Demantra component (for example; Demand Management.
Restart the Demantra application server.
Ensure that the new levels are visible in the aggregation level tab of the worksheet.
For legacy systems, there is no sales history hook for combination levels in T_SRC_SALES_TMPL. There is a HISTORY_HOOK procedure in MSD_DEM_CUSTOM_HOOKS, but it is not invoked during legacy sales history collection. If you need to update the T_SRC_SALES_TMPL table for combination levels, you can embed code in the ITEM_HOOK or LOCATION_HOOK procedures.
When you add columns to custom tables, it is suggested that you name the key columns similar to the following:
DM_ITEM_CODE
DM_SITE_CODE
DM_ORG_CODE
This example describes how to add five additional levels to the item hierarchy. To load data into these additional levels, create a custom table in the Demantra schema, similar to the table below. Category codes for each item should be populated using dm_item_code as the key from the JD Edwards EnterpriseOne .xml extracts. Ensure that there are no duplicate records.
Column | Description |
---|---|
DM_ITEM_CODE | Item name |
ITEM_CATEGORY_CODE1 | New item hierarchy level 1 |
ITEM_CATEGORY_CODE2 | New item hierarchy level 2 |
ITEM_CATEGORY_CODE3 | New item hierarchy level 3 |
ITEM_CATEGORY_CODE4 | New item hierarchy level 4 |
ITEM_CATEGORY_CODE5 | New item hierarchy level 5 |
This example describes how to add five additional levels to the organization hierarchy. To load data into these additional levels, create a custom table in the Demantra schema, similar to the table below. Category codes for each branch should be populated using dm_org_code as the key from the JD Edwards EnterpriseOne .xml extracts. Ensure that there are no duplicate records.
Column | Description |
---|---|
DM_ORG_CODE | Organization name |
BRANCH_CATEGORY_CODE1 | New org hierarchy level 1 |
BRANCH_CATEGORY_CODE2 | New org hierarchy level 2 |
BRANCH_CATEGORY_CODE3 | New org hierarchy level 3 |
BRANCH_CATEGORY_CODE4 | New org hierarchy level 4 |
BRANCH_CATEGORY_CODE5 | New org hierarchy level 5 |
This example describes how to add five additional levels to the customer site hierarchy. To load data into these additional levels, create a custom table in the Demantra schema, similar to the table below. Category codes for each customer site should be populated using dm_site_code as the key from the JD Edwards EnterpriseOne .xml extracts. Ensure that there are no duplicate records.
Column | Description |
---|---|
DM_SITE_CODE | Organization name |
CUSTOMER_CATEGORY_CODE1 | New site hierarchy level 1 |
CUSTOMER_CATEGORY_CODE2 | New site hierarchy level 2 |
CUSTOMER_CATEGORY_CODE3 | New site hierarchy level 3 |
CUSTOMER_CATEGORY_CODE4 | New site hierarchy level 4 |
CUSTOMER_CATEGORY_CODE5 | New site hierarchy level 5 |
Populate the branch and customer site category codes from the custom tables into the new organization and site hierarchy level columns in the T_SRC_LOC_TMPL table as follows:
For branch category codes:
Set T_SRC_LOC_TMPL. E1_BRANCH_CATEGORY_1 = ORGANIZATION_HIERARCHY.BRANCH_CATEGORY_CODE1
where
T_SRC_ITEM_TMPL.DM_ORG_CODE = ORGANIZATION_HIERARCHY.DM_ORG_CODE
Set T_SRC_LOC_TMPL. E1_BRANCH_CATEGORY_2 = ORGANIZATION_HIERARCHY.BRANCH_CATEGORY_CODE2
where
T_SRC_ITEM_TMPL.DM_ORG_CODE = ORGANIZATION_HIERARCHY.DM_ORG_CODE
and so on, for each of the five new organization hierarchy levels.
For customer site category codes:
Set T_SRC_LOC_TMPL. E1_CUSTOMER_CATEGORY_1 = SITE_HIERARCHY.CUSTOMER_CATEGORY_CODE1
where
T_SRC_ITEM_TMPL.DM_SITE_CODE = ORGANIZATION_HIERARCHY.DM_SITE_CODE
Set T_SRC_LOC_TMPL. E1_CUSTOMER_CATEGORY_2 = SITE_HIERARCHY.CUSTOMER_CATEGORY_CODE2
where
T_SRC_ITEM_TMPL.DM_SITE_CODE = ORGANIZATION_HIERARCHY.DM_SITE_CODE
After adding custom code, recompile and verify the package to check for warnings or errors.
Run the Collect Sales History concurrent program. You have the following options:
Auto Download = No. This option populates the new level data into the staging tables of Demantra.
.Auto Download = Yes. This option populates the new level data into the staging tables and moves the data to the Demantra base tables.