Oracle Demantra Integration with Advanced Planning Command Center

This chapter covers the following topics:

• Introduction
• Integration Points
• Business Process
• E-Business Suite Setup
• Demantra Setup
• Publishing
• Integration Workflows and Integration Profiles
• Troubleshooting

Introduction

Oracle Advanced Planning Command Center (APCC), part of the E-Business Suite of products, provides decision makers with a comprehensive solution that delivers powerful analysis of operational, tactical, and strategic supply chain plans, robust scenario modeling and management, and automated business process execution capabilities. Demantra Demand Management (DM) and Sales & Operations Planning (S&OP) sales forecasts, backlog and financial forecast measures can be uploaded to APCC so that the change between plans and scenarios can be analyzed.

This integration supports:

• Publishing plans and measures from Demand Management and Sales and Operations Planning.

• Mapping of additional seeded and customized measures from Demand Management and Sales and Operatons Planning to APCC.

• Limited mapping of item hierarchy between Demantra and APCC.

• Incremental publishing of historical forecasting measures.

Integration Points

Demand Management:

Sales and Operations Planning:

Predictive Trade Planning:

This integration is similar to the integration with Demand Management and the same information is collected. In Demantra PTP, Promotions and scenarios are then modeled and specific Series data is exported to APCC.

For a list of PTP Series that are exported to APCC, refer to the Publish PTP-APCC Promotion Measures workflow.

Refer to the Predictive Trade Planning section in “Business Process,” below, for more information.

Business Process

Demand Management:

1. From EBS, user Demand Management System Administrator responsibility runs the following collections, which transfer the latest data from the EBS to Demantra:

   • Standard Collections

   • Shipment and Booking Data

   • Return History

   • Currency Conversion

   • Units of Measure

   • Pricing Data

   For more information about this process, see Downloading to Oracle Demantra.

2. From EBS, run Demand Management Workbench.

3. From Demand Management, finalize forecast.

4. From EBS, run Workflow Manager.

5. (Optional) If desired, run one of the standard Demantra Demand Management to EBS workflows to publish the full set of forecast and measure data to EBS. This is necessary if you are integrating with other EBS products like ASCP or third-party applications. Available workflows include:

   • EBS Upload Local Forecast

   • EBS Upload Global Zone Forecast

   • EBS Upload Local Forecast, Demand Class

   • EBS Upload Global Zone Forecast, Demand Class

   For more information about the Demantra Demand Management uploading workflows, see Uploading from Oracle Demantra.

6. Run Publish DM-APCC Measures workflow to publish the final forecast and measures to APCC. Both plan-specific and plan-independent measures are transferred to APCC.

7. From EBS, user with Advanced Planning Scenario Manager responsibility logs into APCC and defines scenario containing Demantra plan name. The default plan name is APCC-DM.

8. From APCC, user with Sales and Operations Planning Analyst responsibility reviews the scenario in the Sales and Operations Planning dashboard. Alternatively, you can create a custom dashboard in APCC for reviewing Demand Management reports.

Sales and Operations Planning:

1. From EBS, user with Sales & Operations Planning Administrator responsibility runs the following collections:

   • Standard Collections

   • Shipment and Booking Data

   • Return History

   • Currency Conversion

   • Units of Measure

   • Pricing Data

   For more information about this process, see Downloading to Oracle Demantra.

2. From EBS, run Sales and Operations Planning Workbench and log into S&OP.

3. From S&OP, finalize consensus forecast in S&OP.

4. From EBS, run Workflow Manager.

5. (Optional) If desired, run one of the standard Demantra S&OP to EBS workflows to publish the full set of consensus forecast and measures to EBS. This is necessary if you are integrating with other EBS products like ASCP or third-party applications. Available workflows include:

   • Upload Consensus Forecast -- Org, Week

   • Upload Consensus Forecast -- Org, Period

   • Upload Consensus Forecast -- Zone, Week

   • Upload Consensus Forecast -- Zone, Period

   • Upload Consensus Forecast -- Org, Demand Class, Week

   • Upload Consensus Forecast -- Zone, DC, Week

   For more information about the Demantra S&OP workflows, see Integration Workflows and Interface Profiles.

6. Run Publish S&OP-APCC Measures workflow to publish the consensus forecast and measures to APCC. Both plan-specific and plan-independent measures are transferred to APCC.

7. From EBS, user with Advanced Planning Scenario Manager responsibility logs into APCC and defines scenario containing Demantra plan name. The default plan name is APCC-SOP.

8. From APCC, user with S&OP Analyst responsibility reviews the scenario in the S&OP dashboard.

Predictive Trade Planning

1. From EBS, user Predictive Trade Planning Administrator responsibility runs the following collections, which transfer the latest data from the EBS to Demantra:

   • Standard Collections

   • Shipment and Booking Data

   • Return History

   • Currency Conversion

   • Units of Measure

   • Pricing Data

   For more information about this process, see Downloading to Oracle Demantra.

2. From EBS, run Demand Management Workbench.

3. From Demand Management, finalize forecast.

4. From the Predictive Trade Planning module, create and model (and optionally optimize) promotions.

5. From Workflow Manager run the 'Publish PTP-APCC Promotion Measures' workflow to publish the aggregate and detail measures to APCC EBS.

   For more information about the Demantra Demand Management uploading workflows, see Uploading from Oracle Demantra.

6. From EBS, user with Advanced Planning Scenario Manager responsibility logs into APCC and defines scenario containing Demantra plan name.

   The default plan names are:

   • APCC-PTP-A (aggregate measures)

   • APCC-PTP (detail measures)

7. . From APCC, user with Trade Analyst responsibility reviews the scenario in the Trade Analyze dashboard.

E-Business Suite Setup

The integration between Demantra and EBS is preconfigured and requires no additional setup. However, if desired, you can fine-tune the integration as follows:

• Updating Measures to be Published to APCC

• Creating Additional Custom Measures

• Configuring Additional Demantra Item Hierarchies in APCC

Updating Measures to be Published to APCC

Various Demantra measures are automatically mapped in APCC. They are:

Demantra Series	APCC Measure	Demand Management	Sales & Operations Planning	Type of Measure
13 Week Lag Absolute Pct Err	Consensus Forecast Accuracy – MAPE – 13 week	Y	Y	Plan-independent
4 Week Lag Absolute Pct Err	Consensus Forecast Accuracy – MAPE – 4 week	Y	Y	Plan-independent
8 Week Lag Absolute Pct Err	Consensus Forecast Accuracy – MAPE – 8 week	Y	Y	Plan-independent
Actual Onhand	Inventory History		Y	Plan-independent
Actual Production	Production History		Y	Plan-independent
Annual Plan Value	Annual Plan Value		Y	Plan-specific
Booking – Book Items – Book Date	Bookings History	Y	Y	Plan-independent
Booking – Book Items – Req Date	Bookings – Requested Date History	Y	Y	Plan-independent
Booking forecast	Bookings Forecast		Y	Plan-specific
Budget Value	Budget		Y	Plan-specific
Consensus Forecast	Consensus Forecast	Y	Y	Plan-specific
Demand Priority	Demand Priority	Y	Y	Plan-specific
Final Forecast	Final Forecast	Y	Y	Plan-specific
Financial Forecast Value	Financial Forecast Value		Y	Plan-specific
Marketing Forecast	Marketing Forecast		Y	Plan-specific
Projected Backlog	Projected Backlog		Y	Plan-specific
Returns History	Returns History	Y	Y	Plan-independent
Sales Forecast	Sales Forecast		Y	Plan-specific
Shipment forecast	Shipment Forecast	Y	Y	Plan-specific
Shipping – Ship Item – Ship Date	Shipment History	Y	Y	Plan-independent
Total Backlog	Actual Backlog		Y	Plan-indepedent

There are placeholders for up to ten more plan-based measures and five plan-independent measures. The Collect Demantra Publish Information to APCC concurrent program must be run after additional measures are configured.

To update measures to be published to APCC:

1. Plan the custom measures to be published.

   • List all the Demantra measures to be published.

   • Try to publish base measures wherever possible and avoid derived measures, especially with complex calculations. This is because data is published at the lowest level, rolled up in Demantra and published to APCC independently. If the aggregation requires aggregation on components first, which is not published to APCC, the result will be inconsistent.

   • Please note that Demantra only publishes server expressions. If the series is defined with client expressions, it will show derived results in the worksheet, but will not be published.

2. Modify the export profiles to add or remove series.

3. (Optional) If you want to add more columns for custom measures in APCC fact tables, see additional details in Creating Additional Custom Measures.

4. Restart Demantra servers.

5. Run the Collect Demantra Customization Information to APCC concurrent program to generate column map table MSC_APCC_CUSTOM_MEASURES.

   • The process respects existing mapping. For example, if Base Override has been mapped to ATTRIBUTE2, it will keep Base Override as ATTRIBUTE2 until you decide not to publish it or manually change it.

   • It skips mapping when the target columns have not been properly defined.

   • The labels are inherited from Demantra.

6. Review the map table and modify if necessary.

7. Update RPD to reflect additional columns and aggregation methods.

   • Follow the existing custom columns as samples, to map additional custom columns in RPD in all three layers.

   • Default aggregation method defined in RPD is SUM for all custom measures. You need to manually update them to correct the methods.

8. Deploy RPD.

The Collect Demantra Publish Information to APCC concurrent program must be run after you change the measures being exported from Demantra to APCC.

To run the Collect Demantra Publish Information to APCC concurrent program:

1. From the Advanced Supply Chain Planner responsibility, choose Other, then Request.

2. Specify a Single Request.

3. From the Submit Request window, enter Collect Demantra Publish Information to APCC in the Name field.

4. In the Parameters field, enter ODS.

5. Click Submit.

Creating Additional Custom Measures

If you have used up the ten plan-specific and five plan-independent measure placeholders, you can create additional custom measures by adding more columns for custom measures in the APCC fact tables. The APCC fact tables are MSC_DEMANTRA_F for plan-specific custom measures (10 maximum: ATTRIBUTE1…ATTRIBUTE10) and MSC_DEMANTRA_ODS_F for plan-independent custom measures (5 maximum: ATTRIBUTE1…ATTRIBUTE5).

• Run alter table to create more columns.

• Make sure the column names follow convention like ATTRIBUTE 11, ATTRIBUTE12, etc., all nullable number type.

Follow the rest of the steps involved with updating measures including:

• Restarting the Demantra servers.

• Running the Collect Demantra Customization Information to APCC concurrent program.

• Updating RPD.

• Deploying RPD.

For more details about updating measures, see To update measures to be published to APCC.

Configuring Additional Demantra Item Hierarchies in APCC

Specify the highest parent level in the selected item hierarchy in a profile option. When done, you must run the Refresh APCC Materialized Views concurrent program. To Add a Demantra Item Hierarchy in APCC:

1. From the Demand Management System Administrator responsibility, choose User Management, then Other, and then Profile.

2. In the System Profile Values window, define any of the following:

   • MSC: Additional Item Hierarchy in APCC

   • MSC: Additional Customer Hierarchy in APCC

   • MSC: Additional Organization Hierarchy in APCC

   

3. In the Site field, specify the additional Demantra levels you want visible in APCC. All of the Demantra item, customer, and organization levels are available for you to select from.

4. Click OK.

The Refresh APCC Materialized Views concurrent program must be run after changing the item hierarchy. It is also run automatically by the collections process to build dimensions.

To run the Refresh APCC Materialized Views concurrent program:

1. From the Advanced Supply Chain Planner responsibility, choose Other, then Request.

2. Specify a Single Request.

3. From the Submit Request window, enter Refresh APCC Materialized Views in the Name field.

4. In the Parameters field, enter ODS.

5. Click Submit.

Demantra Setup

The integration between Demantra and APCC is preconfigured and requires no additional setup. However, if desired, you can fine-tune the integration as follows:

• Customizing Plan Names

• Setting Up Incremental Exporting of Historical Forecasting Measures from Demantra

Aside from the ones listed above, you may customize other areas as well, like the parameters passed to APCC in seeded Demantra workflows, the Demantra publish profile, etc. Customization is restricted to expect data always published at the item\organization\customer site\demand class\date level, or their combinations.

Customizing Plan Names

For Demand Management:

1. From the Demand Management System Administrator responsibility, launch the Demantra Workflow Manager.

2. Edit the Publish DM-APCC Measures workflow.

3. Open the Archive Demantra Plan stored procedure step.

4. In Parameters field, change the plan name on the second line. The default is APCC-DM.

For Sales & Operations Planning

1. From the Sales and Operations Planning System Administrator responsibility, launch the Demantra Workflow Manager.

2. Edit the Publish S&OP-APCC Measures workflow.

3. Open the Archive SOP Plan stored procedure step.

4. In Parameters field, change the plan name on the second line. The default is APCC-SOP.

ETL (Extraction, Transformation and Loading tool) will report an error if the same plan name already exists for a different plan type, or for an imported plan.

Setting Up Incremental Exporting of Historical Forecasting Measures from Demantra

By default, all the integration interface profiles are set to publish a full export of data from Demantra. If desired, you can change to an incremental export of data. This is useful if you are a Sun customer and want to keep six quarters of the latest history, updating changes on a weekly basis.

When you publish incrementally, new data is added, but old data is not deleted. Use custom codes to delete old rows.

To Change to an Incremental Export:

1. Start the Business Modeler.

2. From the Tools menu, choose Integration Interfaces.

3. Choose the integration interface you want to modify, for example DM Plan Facts. The Integration Interface details wizard appears.

4. Click Next.

5. Select the data profile you want to modify. For example, DM ODS Facts.

6. From Export Data field, choose Incremental instead of Full.

7. Click Finish to exit out of the Integration Interface wizard.

8. Exit out of the Business Modeler.

9. Restart the Demantra application server to apply the change.

Note: For consistent reporting between Demantra and APCC, you can change the seeded time bucket from Week to Lowest Period. See Viewing Calendar Months in a Weekly System .

Publishing

Demand Management to APCC

To upload the Demand Management measures to APCC:

1. From the Demand Management System Administrator responsibility, launch the Workflow Manager.

2. Login to the Workflow Manager.

3. Run the Publish DM-APCC Measures workflow.

Note: To upload the final forecast for other EBS applications, you will still need to run an EBS Upload workflow. See Uploading from Oracle Demantra.

Sales & Operations Planning

To upload the Sales and Operations Planning measures to APCC:

1. From the Sales and Operations Planning System Administrator responsibility, launch the Workflow Manager.

2. Login to the Workflow Manager.

3. Run the Publish S&OP-APCC Measures workflow.

Note: To upload the consensus forecast for other EBS applications, you will still need to run an Upload Consensus Forecast workflow. See Workflow Overview.

Predictive Trade Planning to APCC

To upload the PTP measures to APCC:

1. From the Demand Management System Administrator responsibility, launch the Workflow Manager.

2. Login to the Workflow Manager.

3. Run the Publish PTP-APCC Promotion Measures workflow.

Note: To upload the final forecast for other EBS applications, you will still need to run an EBS Upload workflow. See Uploading from Oracle Demantra.

Integration Workflows and Integration Profiles

Workflows

Workflows	Description
Publish S&OP-APCC Measures	This workflow publishes the consensus forecast, plan-specific measures, and then plan-independent measures to APCC. The default plan name is APCC-SOP, but this plan name parameter can be changed in the workflow step “Archive SOP Plan”. Custom measures can be added to the upload if designated. See Updating Measures to be Published.
Publish DM-APCC Measures	This workflow publishes the final forecast, plan-specific measures, and then plan-independent measures such as lagged MAPE to APCC. The default plan name is APCC-DM, but this plan name parameter can be changed in the workflow step “Archive Demantra Plan”. Custom measures can be added to the upload if designated. See Updating Measures to be Published.
Publish PTP-APCC Promotion Measures	Publishes aggregate and detailed measures to APCC, including: Plan measures, at an aggregate level. These measures are visible in the RT S&OP dashboard. Promotion measures, at a detailed level. These measures are visible in the Trade Analyst dashboard. The measures are: Base Volume Incremental Event Volume Total Event Volume Base Value Incremental Event Value Total Event Value Manufacturer List Price Regular Price Promoted Price Promotional Cannibalization Volume Forecast Promotional Cannibalization Value Forecast Promotional Pre-Post Effect Volume Forecast Promotional Pre-Post Effect Value Forecast Promotional Net Incremental Value Forecast The detail promotion measures will be visible in the Trade Analyst dashboard. They are published at the lowest level of each dimension: Promotion Tactic Item Site Org (optional) Date

Export Integration Profiles

DM Plan Facts Integration Interface

This export integration profile is called by the Publish DM-APCC Measures workflow. It publishes plan-specific factors to APCC.

Data Profiles	Series/Levels/View Name
DM Plan Facts	Series: Consensus Forecast Demand Priority Final Forecast Item Destination Key Shipment Forecast Site Destination Key By default, Consensus Forecast = Final Forecast in Demand Management. Output Levels: Item, Demand Class, Organization, SiteView Name: BIEO_APCC_DM.

DM ODS Plan Facts Integration Interface

This export integration profile is called by the Publish DM-APCC Measures workflow. It publishes plan-independent factors to APCC.

Data Profiles	Series/Levels/View Name
DM ODS Facts	Series: 13 Week Lag Absolute Pct Err 4 Week Lag Absolute Pct Err 8 Week Lag Absolute Pct Err Booking Book Items Book Date Booking Book Items Req Date Item Destination Key Returns History Shipping Ship Items Ship Date Site Destination Key Output Levels: Item, Demand Class, Organization, Site. View Name: BIEO_APCC_DM_ODS.

SOP Plan Facts Integration Interface

This export integration profile is called by the Publish S&OP-APCC Measures workflow. It publishes plan-specific factors to APCC.

Data Profiles	Series/Levels/View Name
Export APCC	Series: Annual Plan Value Booking Forecast Budget Value Consensus Forecast Demand Priority Final Forecast Financial Forecast Value Item Destination Key Marketing Forecast Sales Forecast Shipment Forecast Site Destination Key Total Backlog Output Levels: Item, Demand Class, Organization, Site. View Name: BIEO_APCC_SOP.

Note: By publishing using the “Lowest Period” time aggregation, APCC analysts can review reports at the weekly and monthly level.

Troubleshooting

Critical error messages are logged in the Demantra DB_EXCEPTION_LOG table. To view log messages, use the following sample SQL command after customizing with your Demantra schema name:

Select err_date, proc_name, err_msg
From <<Demantra_schema>>.DB_EXCEPTION_LOG
Where proc_name like 'MSC_DEMANTRA_PKG.%' 
Order by err_date 

If this information is not sufficient, you can set the MSC:APCC debug mode profile to Yes to gather more information.

To use the MSC:APCC debug mode profile:

1. From the Demand Management System Administrator responsibility, choose User Management, then Other, and then Profile.

2. From the Systems Profile Values window, set MSC:APCC debug mode profile to Yes.

3. Find the APCC log file path. For example,

   Select msc_phub_pkg.log_file from dual;
   

4. Run the Demantra-APCC workflow that you want to debug.

5. Get log file on the database server.

6. Set MSC: APCC debug mode profile to No.