Export SOP Data from Demantra to APCC

An Oracle White Paper

August 2013

Table of Contents

1. OVERVIEW.................................................................................................................................................................................................................................. 2

2. NEW DEMANTRA INSTALLATION......................................................................................................................................................................................... 2

3. STANDARD COLLECTIONS...................................................................................................................................................................................................... 7

4. SHIPMENT AND BOOKING HISTORY COLLECTIONS.......................................................................................................................................................... 7

5. LOAD SUPPLY PLAN DATA................................................................................................................................................................................................... 12

6. RUN ENGINE............................................................................................................................................................................................................................ 21

7. RUN ASSIGN PLAN NAME..................................................................................................................................................................................................... 24

1
 1. Overview

The objective of this white paper is to provide a step by step test

case on the how to export SOP data from demantra to APCC. There is already an
existing EBS – Demantra integration, but on this test case we are starting
with a clean new demantra installation, bring data from EBS , generate
forecast , then export it to APCC.

2. New Demantra installation

Install demantra 7.3.1.4 on the same database as apps schema. Schema

name: DEMLNX
There is already an existing demantra schema installed on the same database :
MSDDEM

Change the MSD_DEM profiles options from System Administrator

responsibility >> Profile >> System >> check for MSD_DEM% profiles

2
Then modify the profiles values to match my demantra schema name and my
demantra application URL.

To run Update Synonyms , I first need to enable the schema parameter,

following Update Synonyms procedure - What it does and why it is
important to EBS/ASCP - Demantra implementations (Doc ID 1061331.1)
From System Administrator Responsibility >> Concurrent >> Program >>
Define >> Update Synonyms >> Parameters >> select the Enabled check-box
on the right side of the Parameter

3
Then run Update Synonyms concurrent request from Demand Management System
Administrator >> Other >> Requests >> Submit a New Request >> Single Request
>> OK >> Update Synonyms >> A new windows with the Parameters popup >> choose
my schema : DEMLNX >> Submit

4
To check if the Update Synonyms completed successfully or not, go to Demand
Management System Administrator >> Other >> Requests >> Find
It failed:

Check the log file:

+---------------------------------------------------------------------------+
Demand Planning: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

MSDDEMUPDSYN module: Update Synonyms

+---------------------------------------------------------------------------+

Current system time is 03-SEP-2013 06:20:43

+---------------------------------------------------------------------------+

**Starts**03-SEP-2013 06:20:43
**Ends**03-SEP-2013 06:20:43
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+

5
The Demantra Schema Name is - DEMLNX
Updated synonym MSD_DEM_TRANSFER_LIST
Updated synonym MSD_DEM_TRANSFER_QUERY
Updated synonym MSD_DEM_TRANSFER_QUERY_LEVELS
Updated synonym MSD_DEM_GROUP_TABLES
Updated synonym T_SRC_SALES_TMPL
Updated synonym MSD_DEM_RETURN_HISTORY
Execute privilege granted on package MSD_DEM_UPLOAD FORECAST to DEMLNX schema.
Execute privilege granted on package MSD_DEM_SOP to DEMLNX schema.
Execute privilege granted on package MSD_DEM_COMMON_UTILITIES to DEMLNX
schema.
Execute privilege granted on package MSC_PHUB_PKG to DEMLNX schema.
Granting SELECT privilege on following tables to DEMLNX schema.
MSC.MSC_PLANS
MSC.MSC_INT_PUBLISH_DATA
MSC.MSC_INT_SUPPORTED_API
ORA-01031: insufficient privileges
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...

Output file size:

35

Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 03-SEP-2013 06:20:43

+---------------------------------------------------------------------------+

Log in as msc user and execute:

grant select on MSC.MSC_PLANS to apps WITH GRANT OPTION;

grant select on MSC.MSC_INT_PUBLISH_DATA to apps WITH GRANT OPTION;

grant select on MSC.MSC_INT_SUPPORTED_API to apps WITH GRANT OPTION;

Then run again the Update Synonyms. It completes successfully.

3. Standard Collections

6
 Run Standard Collections

First choose the organizations.

Go to Demand Management >> Setup >> Instenaces >> click on my TST instance >>
Organization>> Check the Demand Plannung Enabled check-box for the
organiztaions I want to collect in demantra.

Then run Standard Collections from Advanced Supply Chain Planner >>
Collections >> Oracle Systems >> Standard Collections

Completed Successfully.

4. Shipment and Booking History collections

Run Shipment and Booking History collections for my instance:

From Demand Management System Administrator responsibility >> Collections
>> Oracle Systems >> Shipment and Booking History >> Choose Parameters >>
Choose my instance, launch Download to No.

7
Verify that the request complete successfully from Demand Management System
Administrator >> Other >> Requests >> Find

8
Check the log files and the output files from View Log and View Output .

Check and confirm data was populated into demantra staging tables:
select * from t_src_item_tmpl;
select * from t_src_loc_tmpl;
select * from t_src_sales_tmpl;

9
Run EBS Full Download workflow from Demand Management System Administrator
responsibility >> Workflow Manager >> EBS Full Download workflow >> Start

Then click on running Instance (click on 1) to monitor the load from staging
tables into demantra internal tables.

When the MsgDownloadOK shows up, the load had completed.

This step is sending a message to Admin1 user. To complete the workflow, we

need to load into Collaborator Workbench as Admin1 user and complete the Task.

10
Check if data was loaded into items, location, sales_data , mdp_matrix
internal tables.
Check if any data was rejected into t_src_item_tmpl_err, t_src_loc_tmpl_err,
t_src_sales_tmpl_err tables.
Check if any error in collaborator.log file, db_exception_log and integ_status
tables.

Check if we can see data in worksheets.

Open Demand Analysis Item & Organization worksheet.
Adjust the Filters and the Time. Run the worksheet.

11
 5. Load Supply Plan Data

Follow Demantra SOP - EBS integration (Doc ID 1464910.1)

Advanced Supply Chain Planner --> Supply Chain Plan --> >> Names >> Choose
Organization >>
Input APCCPlan >> Plan Options >> select Use for Sales and Operations Planning

12
Launch Plan

13
Click on Submit button, and then wait until complete

14
Run Download Plan Scenarios workflow from Sales and Operations Planning System
Administrator >> Workflow Manager

15
Run Download Plan Scenarios workflow data from

Sales and Operations Planning System Administrator >> Sales and Operations
Planning Workbench >> Dashboard: Supply Review

16
17
Check Download Plan Scenario Data workflow – is running

Log into CWB as Sop1 user to resolve the exceptions

18
Check all the exceptions one by one:

After the Load scenarios data had completed

19
20
 6. Run Engine

After loading data, Run engine.

Adjust forecast tree, engine parameters from init_params_% tables and

sys_params table. Adjust the DBHints parameters from db_params table.
Execute proport procedure.
Then run engine.

21
Check forecast_status table.

Check if I have forecast generated in sales_data table in fore_0 column:

22
Check the consensus forecast in worksheets.

Run upload consensus forecast

select APPLICATION_ID, QUERY_NAME, transfer_query.* from transfer_query

where QUERY_NAME = 'Consensus Fcst(Org, Week)';

Check data was exported into BIEO_Exp_Cns_Fcst_Org_Week view

select * from BIEO_Exp_Cns_Fcst_Org_Week

Check data was exported into MSD_DP_SCN_ENTRIES_DENORM

select * from MSD_DP_SCN_ENTRIES_DENORM
where demand_plan_id = 5555555
and scenario_id = 5555975

23
 7. Run Assign Plan Name

Run Assign Plan Name:

Check the log file

+---------------------------------------------------------------------------+
Demand Planning: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

MSDAPN module: Assign Plan Name

+---------------------------------------------------------------------------+

Current system time is 17-SEP-2013 06:19:14

+---------------------------------------------------------------------------+

**Starts**17-SEP-2013 06:19:14
**Ends**17-SEP-2013 06:19:24
+---------------------------------------------------------------------------+

24
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
2013-09-17 06:19:15: Advanced Planning Command Center Populating API Starts
2013-09-17 06:19:15: msc_phub_pkg.populate_details_fn(8907218,,1,,,2,10,)
2013-09-17 06:19:15: msc_phub_pkg.create_plan_run(,2,1,8907218,10)
2013-09-17 06:19:15: msc_phub_pkg.create_fact_partitions: dbms_lock.request
2013-09-17 06:19:15: msc_phub_pkg.manage_partitions(1, 282004, 1, 5)
2013-09-17 06:19:16: alter table MSC_DEMANTRA_F add partition
DEMANTRA_F_282004 values less than (282005)
2013-09-17 06:19:16: msc_phub_pkg.manage_partitions: complete
2013-09-17 06:19:16: msc_phub_pkg.create_staging_partitions(10,2)
2013-09-17 06:19:16: msc_phub_pkg.create_plan_run(8907218, 282004, 'MGPlan
09/17(0)')
2013-09-17 06:19:16: msc_demantra_pkg.populate_details
2013-09-17 06:19:24: msc_demantra_pkg.populate_details: insert=969
2013-09-17 06:19:24: msc_phub_util.gather_table_stats: MSC.MSC_DEMANTRA_F
skipped(MSC_APCC_GATHER_FACT_STATS=)
2013-09-17 06:19:24: msc_demantra_pkg.summarize_demantra_f
2013-09-17 06:19:24: msc_demantra_pkg.summarize_demantra_f, delete=0
2013-09-17 06:19:24: msc_demantra_pkg.summarize_demantra_f, level1=612
2013-09-17 06:19:24: msc_phub_pkg.finalize_plan_run(8907218,282004,2,1,1,1)
2013-09-17 06:19:24: Advanced Planning Command Center Populating API Ends
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...

Output file size:

0

+------------- 1) PRINT -------------+

Disabling requested Output Post Processing. Nothing to process. The output
of the request is zero byte.
+--------------------------------------+

Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 17-SEP-2013 06:19:24

+---------------------------------------------------------------------------+

Check that data was populated into MSC_DEMANTRA_F:

select * from MSC_DEMANTRA_F
where plan_id = 8907218;

Go to Advanced Planning Scenario Manager responsibility >> Scenario Manager >>

Scenarios

25
26