Demantra - Setup, Collections, and Download
Set Demantra related Profiles
1.
Set Demantra related Profiles
Set these profiles MSD_DEM: Host URL MSD_DEM: Schema MSD_DEM: Version
MSD_DEM: Customer Attribute
Note 726444.1 - How To Setup MSD (MSD_DEM) Customer Attribute Profile for Demand Planning and/or Demantra
Update Synonyms
1.
Update Synonyms
Update synonyms needs run prior to running Collect Shipment/Booking history
Launch Collect Shipment and Booking History - ERP - V3
2.
Launch Collect Shipment and Booking History - ERP - V3
Concurrent Requests
Request Set Collect Shipment and Booking History - ERP - V3 Request Set Stage Push Setup Parameters Collect Shipment and Booking History Collect CTO Level Data Populate Staging Table Collect Level Type Collect Time Launch EP LOAD
Dynamic query’s used to create the source views to collect the source data example select * from MSD_DEM_QUERIES where upper(query) like '%OE_ORDER_LINES%' order by query_id;
Demantra – Collect Shipment and Booking History
Nav > Demand Management System Administrator > Collections > Oracle Systems > Shipment and Booking History
These are the History and Quantity streams and fields populated by the various options above. Booking History Shipment History Booked Items Requested Items Shipped Items Booked Date Requested Date Shipped Date Booked Quantity Requested Quantity Shipped Quantity
Quantity Column where data is populated in T_SRC_SALES_DATA_TMPL by Collect Shipment/Booking History process and ultimately into SALES_DATA by the EBS Full Download (EP_LOAD) process.
EBS_BOOK_HIST_REQ_QTY_BD EBS_BOOK_HIST_REQ_QTY_BD
EBS_BOOK_HIST_BOOK_QTY_RD EBS_BOOK_HIST_REQ_QTY_RD
EBS_SHIP_HIST_SHIP_QTY_SD EBS_SHIP_HIST_SHIP_QTY_RD EBS_SHIP_HIST_REQ_QTY_RD
Note: Launch Download set to No
Prevents the EBS Full Download Workflow Process from being submitted,
Leaving the records in the Demantra Staging T_SRC_xxxxxx and Interface BIIO_xxxxxx tables
Submit
Push Setup Parameters
Reads the value of the msd_dem profile options on the source
Populates the msd_dem_setup_parameters table with those values
Then launches collections process that reads/filters the collected data based on the values
On the source when the source views are created
Push Setup Parameters log file: +---------------------------------------------------------------------------+ Demand Planning: Version : 12.0.0 Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved. MSDDEMPSP module: Push Setup Parameters +---------------------------------------------------------------------------+
Current system time is 08-NOV-2010 12:32:00 +---------------------------------------------------------------------------+ **Starts**08-NOV-2010 12:32:00 **Ends**08-NOV-2010 12:32:01 +---------------------------------------------------------------------------+ Start of log messages from FND_FILE +---------------------------------------------------------------------------+ Push Setup Parameters Program ----------------------------Source Instance ID : 2061 DB Link: Push Profiles --------------------------------------------------------------------------Profile Name Value ------------------------------------------------------------Profile MSD_DEM_CATEGORY_SET_NAME - 27 Profile MSD_DEM_CONVERSION_TYPE - Corporate Profile MSD_DEM_CURRENCY_CODE - USD Profile MSD_DEM_MASTER_ORG - 204 Profile MSD_DEM_CUSTOMER_ATTRIBUTE Profile MSD_DEM_TWO_LEVEL_PLANNING - 2 Profile MSD_DEM_SCHEMA - MSDEM Profile MSD_DEM_PLANNING_PERCENTAGE - 1 Profile MSD_DEM_INCLUDE_DEPENDENT_DEMAND- 2 Profile MSD_DEM_EXPLODE_DEMAND_METHOD - 1 ------------------------------------------------------------Actions --------Deleting records from msd_dem_setup_parameters in the Source instance Inserting profiles into source msd_dem_setup_parameters Push Organizations - Actions -----------------------------Deleting Organizations from source msd_dem_app_instance_orgs Inserting Organizations into source msd_dem_app_instance_orgs Push Time Data - Actions -----------------------------Deleting time data from source msd_dem_dates Inserting time data into source msd_dem_dates +---------------------------------------------------------------------------+ End of log messages from FND_FILE +---------------------------------------------------------------------------+
Do you need to verify the launched requests? See Note 280295.1 – REQUESTS.SQL
VCP R12.1.3.1 Case Study Verification Scripts Shipment/Booking History Collections Verification SQL*Plus prompt T_SRC_ITEM_TMPL staging; prompt ===================;
SELECT t_ep_i_att_1, dm_item_code, dm_item_desc, ebs_product_category_code, ebs_product_category_desc, ebs_product_family_code, ebs_product_family_desc, ebs_demand_class_code, ebs_demand_class_desc, ebs_demand_class_dest_key FROM msdem.T_SRC_ITEM_TMPL WHERE dm_item_code LIKE '%EE-ATO%'; --prompt T_SRC_SALES_TMPL staging; prompt ===================; SELECT actual_qty, item_price, sales_date, t_ep_m1, t_ep_m2, dm_item_code, dm_org_code, dm_site_code, ebs_demand_class_code, ebs_sales_channel_code, ebs_book_hist_req_qty_bd, ebs_book_hist_req_qty_bd, ebs_book_hist_book_qty_rd, ebs_book_hist_req_qty_rd, ebs_ship_hist_ship_qty_sd, ebs_ship_hist_ship_qty_rd,
ebs_ship_hist_req_qty_rd, ebs_item_sr_pk, ebs_org_sr_pk, ebs_site_sr_pk, ebs_demand_class_sr_pk, ebs_sales_channel_sr_pk, dm_parent_item_code, cto_pln_pct, ebs_parent_item_sr_pk, ebs_base_model_sr_pk, ebs_parent_item_code, ebs_base_model_code, component_code FROM msdem.T_SRC_SALES_TMPL WHERE dm_item_code LIKE '%EE-ATO%'; Location Verification SQL*Plus prompt T_SRC_LOC_TMPL staging; prompt ===================; SELECT t_ep_lr2, t_ep_lr2a, dm_site_code, dm_site_desc, dm_org_code, dm_org_desc, ebs_tp_zone_code, ebs_tp_zone_desc, ebs_zone_code, ebs_zone_desc, ebs_account_code, ebs_account_desc, ebs_customer_code,
ebs_customer_desc, ebs_customer_class_code, ebs_customer_class_desc, ebs_operation_unit_code, ebs_operation_unit_desc, ebs_business_group_code, ebs_business_group_desc, ebs_legal_entity_code, ebs_legal_entity_desc, ebs_sales_channel_code, ebs_sales_channel_desc, ebs_site_dest_key, ebs_org_dest_key, ebs_sales_channel_dest_key, ebs_supplier_code, ebs_supplier_desc, t_ep_lr2_desc, t_ep_lr2a_desc FROM msdem.T_SRC_LOC_TMPL WHERE dm_site_code LIKE 'EE-Part%'; Demantra CTO Staging Table SQL*Plus Verification prompt BIIO_CTO_BASE_MODEL interface; prompt ===================; SELECT * FROM msdem.BIIO_CTO_BASE_MODEL WHERE t_ep_cto_base_model_code LIKE 'EE%'; --prompt BIIO_CTO_CHILD interface; prompt ===================; SELECT * FROM msdem.BIIO_CTO_CHILD
WHERE t_ep_item_id LIKE 'EE%'; --prompt BIIO_CTO_DATA interface; prompt ===================; SELECT * FROM msdem.BIIO_CTO_DATA WHERE level2 LIKE 'EE%'; --prompt BIIO_CTO_LEVEL interface; prompt ===================; SELECT * FROM msdem.BIIO_CTO_LEVEL WHERE item LIKE 'EE%'; --prompt BIIO_CTO_POPULATION interface; prompt ===================; SELECT * FROM msdem.BIIO_CTO_POPULATION WHERE filter_member LIKE 'EE%'; --prompt BIIO_CTO_POPULATION interface for all level_member records; prompt ===================; SELECT * FROM msdem.BIIO_CTO_POPULATION WHERE level_member IN (SELECT level_member FROM msdem.BIIO_CTO_POPULATION WHERE filter_member LIKE 'EE%' ) ORDER BY level_order;
Setup/Verify CTO Integration Interface Data Profile
Setup/Verify CTO Integration Interface Data Profile Known Issue Collaborator Log file. Following is the log message from collaborator.log file: ####################################################### 2010-03-18 11:35:05,170 PDT [WFProcess_60] INFO
appserver.integration:
Import Data. 2010-03-18 11:35:05,171 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20044: Run Start Time: 03-18-2010 11:35:05 2010-03-18 11:35:05,171 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20045: Interface Name: CTO 2010-03-18 11:35:05,171 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20046: Profile Name: IMPORT_CTO_DATA 2010-03-18 11:35:05,171 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20052: Owner: dm 2010-03-18 11:35:05,172 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20053: Profile From Date: 03-17-2008 2010-03-18 11:35:05,172 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20054: Profile Until Date: 05-24-2010 2010-03-18 11:35:07,509 PDT [WFProcess_60] ERROR appserver.integration: ODPM-00077: Query Validator of Profile 'IMPORT_CTO_DATA' Encountered 206 Errors. Watch Error Table 'BIIO_CTO_DATA_ERR' For More Information. 2010-03-18 11:35:08,865 PDT [WFProcess_60] INFO ODPM-20064: Rows processed: 0.
appserver.integration:
2010-03-18 11:35:09,821 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20048: Run End Time: 03-18-2010 11:35:09 2010-03-18 11:35:09,822 PDT [WFProcess_60] INFO
appserver.integration:
ODPM-20049: Run Duration Time (hh:mm:ss:msec):0:0:4.651 #######################################################
Bug 9488234 due to date problem in BIIO_CTO_DATA_ERR for Invalid Date of 22-SEP-2008 for our CTO sales history, data does not meet this setting below which only imports data that falls after 10-NOV-2010 1. Fix dates in Integration Interface in Business Modeler for the CTO Interface Name as reported from the Collaboration.log file 2. Bounce demantra application server 3. Rerun Collect Shipment and Booking History 4. Rerun the Demantra Workflow – either CTO’s or Full EBS Download
Nav > Demantra Windows > Business Modeler > Tools > Integration Interface
Open the CTO Integration Interface
Select next
And Open the IMPORT_CTO_DATA Data Profile (or whichever Data Profile may get an issue/error as reported in the Collaborator.log or Integration.log file)
Click Next Next and see this is the Import Integration Type profile (from EBS into Demantra staging)
Click Next Review the Series available to the Series.
Click Next Review the Import Options See which Series have been selected for this profile
Click Next Review the Time Filtering
Change the date with the pop-up calendar
Setup/Verify related Demantra Parameters
1.
Setup/Verify related Demantra Parameters
Worksheet Parameters tab
System Parameters tab
Engine Profile tab quantity_form parameter
Set value to include the Shipment History Data record quantity, that we created previously, modified the Business Modeler > Parameters > System > Engine > Data Manipulation > quantity_form parameter to point to EBS_SH_SHIP_QTY_SD from SALES_DATA table as that is the demand quantity field that contained our shipment data quantity collected from EBS. The quantity_form parameter may be set to any of these columns from MSDEM.SALES_DATA
Booking History
Shipment History
Booked Items
Requested Items
Shipped Items
Booked Date
Requested Date
Shipped Date
Booked Quantity Requested Quantity
Shipped Quantity EBS_BOOK_HIST_REQ_QTY_BD EBS_BOOK_HIST_REQ_QTY_BD
EBS_BOOK_HIST_BOOK_QTY_RD EBS_BOOK_HIST_REQ_QTY_RD
EBS_SHIP_HIST_SHIP_QTY_SD EBS_SHIP_HIST_SHIP_QTY_RD EBS_SHIP_HIST_REQ_QTY_RD
Different columns may be defined in different Engine Profiles And may be different for Simulation engine run or Analytical engine run.
Application Server tab
Audit Trail tab. Shows any changes applied.
Setup/Verify Analytical Engine Verify various parameters in the Business Modeler If launching the engine from something other than the host computer directly, such as a call from a workflow, set these parameters to an appropriate value EngineBaseURL EnginePlatform
Test Run Analytical Engine prior to downloading data to verify it starts, runs, completes successfully.
Error Parameters problem. Will not send http completion notification WARNING Failed to load profile id info for profile: 0 WARNING Failed to load profile id info for profile: 1 Run the mdac_typ.exe file (Microsoft Data Access Components setup) within the Analytical Engines\bin directory. Rerun the Analytical Engine ( successfully )
Setup/Verify/Run Simulation Engine
Run the Simulation Engine
Enter database connection information. Run Mode: Simulation Note: The Simulation Engine needs to be run anytime a user wants to take the existing data, manipulate it, and rerun the worksheet. When User is completed doing simulation, the Simulation Engine should be stopped.
Launch Workflow EBS Full Download
Launch Workflow EBS Full Download
From T_SRC_xxxxx staging and BIIO_xxxxxx interface tables into the Demantra base tables Download Item (LoadItems) Download Location (LoadLocations) Download Sales Data (LoadHistory) Base Tables MSDEM.ITEMS MSDEM.LOCATION MSDEM.SALES_DATA
Nav > Demantra Demand Management System Administrator > Workflow Manager, Login to Workflow Manager
Start EBS Upload Local Forecast
Data Flow for Items From vcp – msc_system_items Data Flow for Locations From vcp - msc_trading_partners/sites Orgs Customers/sites Data Flow for Sales Data – Booking/Shipment History from ebs - oe_order_lines_all
Through a dynamic temporary view into t_src_sales_data into biio_cto into t_ep_sales_data into (whatever tables for cto data)
Diagnostics Script - Demantra Base Tables after EBS Full Download -- EP_LOAD from T_SRC_xxxx /BIIO_xxxxx to T_EP_xxxx and other DEM Base Tables ----prompt
msdem.ITEMS demantra base tables;
prompt ===================; SELECT * FROM msdem.ITEMS; -----
prompt msdem.LOCATIONS demantra base tables; prompt ===================;
SELECT * FROM msdem. LOCATIONS; -----
prompt msdem.SALES_DATA demantra base tables; prompt ===================; SELECT * FROM msdem. SALES_DATA; -----
prompt T_EP_CTO demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO WHERE t_ep_cto_base_model_id = 143; ----prompt T_EP_CTO_BASE_MODEL demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_BASE_MODEL; ----prompt T_EP_CTO_CHILD demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_CHILD; -----
prompt T_EP_CTO_DATA demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_DATA; ----prompt T_EP_CTO_DATES demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_DATES; ----prompt T_EP_CTO_DEMAND_TYPE demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_DEMAND_TYPE; ----prompt T_EP_CTO_LEVELS demantra base tables; prompt ===================; SELECT * FROM msdem.T_EP_CTO_LEVELS; -----
Demantra – Generate, Manipulate, Publish Forecast Run Analytical Engine
Run the Analytical Engine on that now downloaded data
The engine reads these base tables MSDEM.ITEMS MSDEM.LOCATION MSDEM.SALES_DATA Calculates based on its Engine Profile. Outputs its combination results to : MSDEM.MDP_MATRIX
Setup/Verify Worksheet Designer settings
Setup/Verify Worksheet Designer settings Display Series Time Aggregation Filters Exceptions Layout Advanced
Login to the Collborator Workbench > Worksheets. Error when logging into the Worksheet:
Found note that stated to clear java cache : C:\Documents and Settings\rbevans\Application Data\Sun\Java\Deployment\cache
Opened the Collaborator Workbench > CTO Worksheet “ CTO: Item Demand by BOM and Week (Crosstab) Demand Forecast by Base Model, Option, and Item by Week in an indented BOM format
Press Any Icon for Designer > or Worksheet > Display
Worksheet > Series
CTO Series
Worksheet > Time
One year prior (52 weeks) to the last sales date in the data collected
Worksheet > Aggregation
CTO is lowest level Worksheet > Filters
We picked only the organization where our model resides.
We picked the Base Model and only our Model from the filter
Worksheet > Exception
Worksheet > Layout
Layout > Advanced
After making changes in the Worksheet Designer, rerun the worksheet by using the Running man icon
Run Worksheet
Run Worksheet We made the following changes: -
Series
-
We Added the EBS Input > Shipping – Ship Items – Ship Date
We added the Entire Forecast series
and moved it over to Selected Series
Re-Run the Worksheet Consensus Forecast was Generated on our Sales Order of Shipped Quantity 10
a.
Launch EBS or CTO Upload Local Forecast (Publish/Submit to ASCP)
Launch EBS or CTO Upload Local Forecast (Publish/Submit to ASCP)
Publishes/Submits back to ASCP
Uploads from Demantra to Denorm table
Login to Workflow Manager Nav > Demantra Demand Management System Administrator > Workflow Manager
EBS Upload Local Forecast Upload Data Flow From Demantra Schema base table to VCP Forecast Inteface From Base table -- MSDEM.MDP_MATRIX Interface Tables - BIEO_xxxxx
Denorm table MSD.MSD_DP_SCN_ENTRIES_DENORM Nav > Demantra Demand Management System Administrator > Workflow Manager
Login to Workflow Manager Start EBS Upload Local Forecast
CTO Upload Local Forecast From Base table -- MSDEM.MDP_MATRIX Interface Tables - BIEO_xxxxx Denorm table MSD_DP_SCN_ENTRIES_DENORM
Nav > Demantra Demand Management System Administrator > Workflow Manager Login to Workflow Manager
Populate Denorm Table - Step
Populate Planning Percent Denorm - Step
Run Script to verify data in denorm table SELECT * FROM msd_dp_scn_entries_denorm WHERE inventory_item_id IN (SELECT DISTINCT inventory_item_id FROM msc_system_items WHERE item_name LIKE 'EE-ATO%' ); SQL Output DEMAND_PLAN_ID SCENARIO_ID DEMAND_ID BUCKET_TYPE START_TIME END_TIME QUANTITY SR_ORGANIZATION_ID SR_INSTANCE_ID SR_INVENTORY_ITEM_ID ERROR_TYPE FORECAST_ERROR INVENTORY_ITEM_ID SR_SHIP_TO_LOC_ID SR_CUSTOMER_ID SR_ZONE_ID PRIORITY DP_UOM_CODE ASCP_UOM_CODE DEMAND_CLASS UNIT_PRICE CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN REQUEST_ID PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE PF_NAME ---------------- -------------- ------------ ------------- ------------------------- ------------------------ ---------------------- ---------------------- ---------------------- --------------------- ------------ ---------------- ---------------------- ---------------------- --------------------- ---------------------- ---------------------- ----------- ------------- -------------- --------------------- ------------------------- ---------------------- ---------------------- --------------------- ---------------------- ---------------------- ------------------------- ------5555555 0.77714 70082 Units -1 5555555 0.77714 70082 Units -1
5556409 207
2233
2
14-NOV-11 2061
Ea
0
5556409 207
2234
211955 18-NOV-10
2
07-NOV-11 2061
Ea
0
211955 18-NOV-10
20-NOV-11 MAPE -1
13-NOV-11 MAPE -1
. . . 5555555 75 70083 Units -1
5556409 207 Ea
62 rows selected
2294
2
13-DEC-10 2061
0
211956 18-NOV-10
19-DEC-10 MAPE -1
We are now ready to complete our test.
