Configuring Oracle Demantra

This chapter discusses configuration steps that must be completed before using Oracle Demantra with this integration. The steps include:

  1. Set up database directories

  2. Run concurrent programs

  3. Update profiles

  4. Add additional Demantra levels (optional)

This chapter covers the following topics:

Setting Up Database Directories

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:

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).

Running Concurrent Programs for EBS to Demantra Integration

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:

  1. 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.

  2. Run the Update Synonyms concurrent request.

  3. Run the Configure Legacy Profiles concurrent request.

  4. Specify the instance code, master organization and the category set name.

  5. 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.

Updating Profiles

To update profiles

  1. Navigate to System Administrator Responsibility, Profile, and System.

  2. Set the MSD_DEM: Host URL profile option to the correct Demantra Application Server url.

  3. Because CTO is not supported, the MSD_DEM: Include Dependent Demand profile option should be set to No.

  4. Set the MSC: E1 Concatenation Character profile option to the correct delimiter character in the generated flat files.

    The default value is +.

Updating the Demantra Data Model

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

  1. Open Demantra Business Modeler.

  2. Navigate to Data Model, Open Data Model.

  3. Select the data model DM/S&OP

  4. Click OK.

  5. Click Next until you get to the Data Model Design form.

  6. Click Site level and select the lr2a level.

  7. Click lr2a_desc.

  8. Change the field name from t_ep_lr2a to t_ep_lr2a_desc.

  9. Click Next.

  10. Click Build Model.

    Important: : Do not click Build New Model.

  11. Click Upgrade Existing Model.

  12. Click OK.

Oracle Demantra Hierarchies

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:

In the Location hierarchy, the following levels are supported for this integration:

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.

Adding Demantra Levels

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

  1. Configure the ERP system to extract the additional category codes into Customer.xml and Base.xml.

  2. 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.

  3. 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.

    the picture is described in the document text

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.

  1. Navigate to Form > Group Sets.

  2. 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.

  1. 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).

  2. 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.

Custom Hook Functionality

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

  1. Add custom code to in the APPS.MSD_DEM_CUSTOM_HOOKS package.

  2. 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.

  1. Restart the Demantra application server.

  2. 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:

Custom Hooks Examples

CustomTable = ITEM_HIERARCHY

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

Custom Table = ORGANIZATION_HIERARCHY

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

CustomTable = SITE_HIERARCHY

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

Item Hook Procedure Pseudocode

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: