General presentation

Objective of the application

This application extract the forecasts from tool DynaSys prepared in dedicated DynaSys tables :

• view EXP_FORE_DR_BW for dynamic forecasts to provide reporting Demand Review (DR) / Sales Follow-up
• view EXP_FORE_BW for monthly snapshots to provide reporting Forecast Accuracy

We also extract data from DiP Dynasys tool module prepared in dedicated Dynasys tables :

• view EXP_DIP_BW_SKU_BW for datafields type without receiving type
• view EXP_DIP_BW_SKU_SITE_BW for datafields type with a receiving type

.Tool Leader + IT leader of the application: Vendula Siamska / Diogo Paiva based in Lisbon

Usage information

History

Roles & Access

Roles and access

See also file maintained by Authorization team : BW Catalog of Roles / link: https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I

Authorization objects

List of autorisation objects mandatory for the application.

Dataflow overview

DynaSys source view used 

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

IECRA determination (implemented since 13/11/2017)

RCS notion, IECRA is only determined for RCS records.

1. Retrieve the division (0DIVISION) from material (C_MATNR2)

2. Get IECRA (0G_CWWE01) from masterdata C_CDSA which is distribution channel (0DISTR_CHAN) compounds with division (0DIVISION).

Determination done at Business Level (EndRoutine)

BU (C_DYN_010) / GBU (CPFCTR1_2) determination

Both BU and GBU are determined at Business Level (EndRoutine) by reading mastersdata C_DYN_005 / Material with System Extension.

The link Material / BU is given by DynaSys interface part : DSO DBDYN02.

The link BU/GBU is given by C_SUBACT2 / Sub-Activity 2

BU = ERP Group of activities (C_PFCTR2)

GBU = BFC Global Business Unit (CPFCTR1_2)

Rule for RCS records was changed the 13/11/2017 (item #58). BU/GBU is now determined reading IECRA (0G_CWWE01)

Sold-to (C_SOLDID) determination

The Sold-to is determined at Business Level (EndRoutine) by reading mastersdata C_DYN_014 / Ship-to with System Extension.

The link Ship-to / Sold-to is given by DynaSys (table EXP_DP_SHIPTO) and stored daily in masterdata C_DYN_014.

Commercial Product (C_PROD) determination

The commercial product is needed to be able to use attributs of masterdata C_GBR14 / GBR: Customer end-use Mat Grp (Ship-to)

The commercial product is determined by reading masterdata C_MATNR2 / Material

Sales Office (C_SAL_OFF) / Sales Document Type (C_DOCTYP2) / Company Code (C_COMPCDE) determination

Sales Office, Sales Document Type, Company code can not be determined for DynaSys, they are set by constant DYN because axis are needed in reporting Sales Follow-up.

Caution Sales Office is autorization relevant for Sales Reporting (Obas, Global Sales), it has been added in Application role, auhorization object ZBI_SD

Company Code also, it will be added ( item #39)

End of Month View (C_FCSTMTH)

For daily dynamic flow, it corresponds to current month/year

For snapshot flow, it corresponds to previous month of the snapshot

In case of a manual reload the value can be changed by using Global Filter 

Concerned Period (C_FCGMTH)

For daily dynamic flow, it is set with 'M' (mean view of the month 0CALMONTH

For snapshot flow, it is calculated :

1) If period (0CALMONTH) = end of month view (C_FCSTMTH) => Concerned Period (C_FCGMTH) is set to 'M' means value of the month

2) If period (0CALMONTH) < end of month view (C_FCSTMTH) => Concerned Period (C_FCGMTH) is set to 'N/A' means Not Applicable, SnapShot done after period

3) If period (0CALMONTH) > end of month view (C_FCSTMTH) => 

a) compute NB Month =  (period (0CALMONTH) - end of month view (C_FCSTMTH) ) - 1

b) if NB Month between -999 and -1 => concatenate 'M' with NB Month into Concerned Period  (C_FCGMTH) 

    else Concerned Period (C_FCGMTH)  is set to 'N/A' 

Caution : Concern period M-1 does not exists.

               Concern period M for Forecast is considered as M-1 in BW queries.

Examples:

DiP-PP - Datafields type & key figures

For the DiP reporting we use only two key figures :

• K_DYN_022 : Amount in €
• K_DYN_021 : Quantity in KG

The quantity or the amount is assigned to a specific Kpi determined by the field DataField Type (C_DATATYP).

In BW we've created one restricted key figure per Datafield type.

The list of the different Datafields loaded from Dynasys and their respective RKF is available here.

To know if the Datafield type is a quantity (assignment of the value to K_DYN_021) or an amount (assignment of the value to K_DYN_021), we're using Global filters entries.

Stream : DPS
Rule : DIP_KF_VAL

In this rule, we've listed the Datafield type expressed in value (amount). 

This Global filter variables are used in the transformation between AADPDY06 & APDPDY07 to make the assignment.

Some datafield types are also defined in hours on Dynasys. There is no changes on the value but only on the unit. All datafields defined with the "H" unit are listed in the global filter rule : DIP_KF_HR

In this case the unit assigned to the key Figure will be "H" (hour) . This assignment is done in the end routine of the transformation between AADPDY06 & APDPDY07.

DiP-PP - Shipping Site (Plant) & Receiving Site (Site) 

Transactional data :

For the DiP reporting, some datafields are defined with only a Shipping site and some with also a Receiving site.

Datafields source :

• view EXP_DIP_BW_SKU_BW for datafields type without receiving type
• view EXP_DIP_BW_SKU_SITE_BW for datafields type with a receiving type

Following the cases, Shipping site and Receiving site can be :

1. A site (plant)
2. A ShipTo (customer)

The assignment is done in the propagation Layer :

• AADPDY06 -> APDPDY07 :
  • Shipping site : SHIPPINGSITE_COD => C_DYN_068
• AADPDY07 -> APDPDY08 :
  • Shipping site : SHIPPINGSITE_COD => C_DYN_068
  • Receiving site : RECEIVINGSITE_CO => C_DYN_025

Master data :

Somes attributes associated to Shipping and Receiving site are send by Dynasys tool with the datasource DTS_CV_EXP_DIP_BW_MD_SKU_BW based on the Dynasys view EXP_DIP_BW_MD_SKU_BW.

The content of the view is stored in the master data C_DYN_074 (Shipping site).

There is also another master data C_DYN_084 (Receiving site) which is in reference of C_DYN_074

To determine if a Shipping (receiving) Site is a plant or a ship-to, we'll use the attribute "TYPE_OF_SITE" C_DYN_078.

IF TYPE_OF_SITE = SHIPTO => Shipping or receiving site is a ShipTo code

ELSE => Shipping site or receiving site is a Plant code

This test is used in the calculation view CV_DY_DIP_REPORTING to catch attributes from C_PLANT or C_SHIPTO master data following the record :

It is also used to know where the description have to be loaded.

IF it's a shipTo, the description is loaded from C_CUSTID.

If it's a plant, the description is loaded from C_PLANT.

The check is done in the transformation between the different master data :

• C_PLANT => C_DYN_074 (Shipping Site description if it's a plant)
• C_SHIPTO => C_DYN_074 (Shipping Site description if it's a shipTo)
• C_PLANT => C_DYN_084 (Receiving Site description if it's a plant)
• C_SHIPTO => C_DYN_084 (Receiving Site description if it's a shipTO)

BW objects assignments are described in details here.

There is another specific master data C_DYN_071 (material /plant) that is loaded from the ADSO APDPDY06 (view EXP_DIP_BW_SKU_BW).

A filter at the DTP level load only these datafields in the master data :

• PRICE_ADVISOR => C_DYN_080
• PRICE_ADVISOR_IMPACT => C_DYN_081
• SHSRLT => C_DYN_072
• STD_COST_SAP =>C_DYN_082

Assignment between transactional and master data :

The assignment is done in the calculation view in the projection of the ADSO ABDPDY53 :

• C_DYN_074 (master data Shipping Site) = C_DYN_068 (Transactional data)
• C_DYN_084 (master data Receiving Site) = C_DYN_025 (Transactional data) 

DiP-PP - Logsys assignment

The assignment of LOGSYS field is done on BW between acquisition and propagation layer reading by default the extension of the material code send by Dynasys.
=> See the end routine of the transformation between ADSO AADPDY06 → ADSO APDPDY07

But for some Datafields type, there is no Material code, we must use and read the extension of Shipping site code send by Dynasys to determine the LOGSYS
Datafields coming from the view SKU_MAIN_SITE always have a Material Code assigned.
See below the list of Datafields type without Material code :

In the end routine, we are using a Global filter entry with the list of these datafield type to determine if we are usgin the material or the shipping code to determine the logsys :

Stream : DPS
Rule : DIP_LOGSYS

AERO- Transcodification for BAAN Ship-to

Ship-to BAAN are longer than SAP Ship-to (10 characters). For technical reason and to fit into the 10 character SAP Ship-to, a transcodification has been handle on DynaSys side.

BW receives directly result of the transcodification, and consider it as the Ship-to.

BEM  xxxx CUS xxx can be found in masterdata C_DYN_024

AERO- How Program/Market assignement works ?

Always refer to Dataflow for a better understanding => DynaSys Reporting Dataflow

1) Program/Market assignment is managed in DynaSys. at Material/Ship-to/Distribution Channel/Main spec/Month level

2) assignments are uploaded into BW daily from monday to friday at 10.15pm from DynaSys view EXP_SAP_PROG_BW

NB: on BW a rule put value DUMMY if assignement is provided from DynaSys without program or if the sum of percentages are different from 100%.

AERO- How conversion to Base Unit or Sales Unit works ?

BAAN quantities are provided in Sales Unit (SU).

As this point was discoverd late during the project, and as BW model is mostly designed to collect only quantities in KG, BAAN quantities are stored in KG keyfigures.

So keep in mind, the unit is Not the correct one for all KG keyfigures

AERO- How the Final Forecast Cost is calculated ?

The Final Forecast Cost is a key figure available in the report :

We also have 3 characteristics available and associated to these key figures :

Storage on BW

The material cost are loaded at the beginning of every month in the cube CUB_PCP01.

it means the product cost is depending of the month, each month we can have the same value or a different one.

in SAP, we can check the cost in MM03 => Sheet : Costing 2.

Example : Material 160704 / Plant : 8350

 => 

On the BW Dynasys application, we load these same costs and stored them in the ADSO APDPDY05.

The ADSO is used in the calculation view CV_DY_BL_DYN22_CPDYN11 :

Final Forecast Calculation

The Final Forecast Cost amount is calculated like below :

FF Cost = Final Forecast (BaseUoM) * Product Cost

The FF Cost is calculated for each month in the past and also in the future.

As the product cost is not existing for the month in the future, we have copied the cost of the current month to the other month in the future.

Example : We are on August 2021, the lastest Cost value available is the cost of August 2021. They are are copied until the end of Current Year + 2 (rule defined with business)

Each month, the product costs in the future are re-calculated based on the latest cost values loaded and available.

We also apply the latest product cost to the previous month of the same year.

Example 1 : We are on August 2021, the lastest Cost value available is the cost of August 2021. Cost Values are copied on January 2021, February 2021 etc...

Example 2 : We are in January 2022, the cost values of december 2021 will not be updated with the cost values of january 2022. 
We keep the last version of cost for december 2021 and copy them in all the months of the year 2021.

On BW, it's done in the transformation of ADSO APDPDY05 on itself.

The calculation of the FF Cost is done at the calcualtion view level because we need Final Forecast (BaseUoM) quantities :

Exchange Rate

As the product cost are defined in the local currency, we have given the possibility to convert in a target currency (USD in our case) at the Query level :

We are using the same exchange rate for the amount measures in the report => CAR4.

Please find below a summary of the FF Cost calculation based on the previous rules exposed.

Example : Vision of August 2021, if we run the report for year 2021

CRM Prospect transcodification (18C>11C)

Prospects were not part of initial DynaSys project, so Ship-to lenght has been based on SAP Ship-to, 10 characters (11 characters to add extension B or R).

In reality CRM Prospect are defined in CRM on 18 characters (contact on CRM side josiane.peytraud@solvay.com)

A transcodification on 11 characters has been put un place on BW site to fit BW object defined on 11 characters.

Class ZBW_DPS_DYNASYS_UTILS => method GET_PROSPECT_TRANSCODED

* Some explanation from CRM team about CRM Prospect Code
* Positions 1,2,3   = type of object or table.For prospect, value is always 001
* Positions 4,5     = organisation, value is currently 24 or 1p for CRM Core
* Position 6        = reserved for SalesForce
* Position 7 to 15  = counter but sensible to upper/lower case
* Position 16,17,18 = important for unicity if upper/lower case is not kept

CRM Prospect Code is on 18 characters, has to be cut to fit 11 characters
Ex= 0011p00001WzSnTAAV => 001WZSNTAAV

Dependencies with other applications

Data loadings

Process Chains

Loading frequency

Average performance

Record Keeping

Reporting

Queries End User Documentation

Main queries

Main functionalities

Broadcast

Maintenance

Tips & Tricks

Extract of 2019 Gross History as a reference

 File with 2019 GH by GBU to have an idea of volume & net sales

Forecast Accuracy reference.xlsx

Known bugs

Wrong amounts in Local Currency Yen (Currency JPY) - Issue closed

For low currency like JPY, SAP has its own internal format to store amounts.

Transformation has been adapted.

See routine

Caution this internal format has to be applied ONLY to keyfigures type Currency (CURR)

For keyfigures type Float (FLTP) routine has been removed as amounts in JPY where divided by 100. Check keyfigure definition.

Example to check

Material 32787 / Ship-to 88719 / Distrib Channel 7K / Source System RCS / GBU Aroma PA

Material 135918 / Ship-to 2066945 / Distrib Channel 7F / Source System RCS / GBU Aroma PA

Recurring procedure

Reload a monthly snapshot from flat file 

A reload has been done in April 2017 for End of March snapshot for SILICA.

Caution, the flow is always changing. The current mapping might not be up-to date and need to be adapted.

A snapshot reload by flat file has to be validated in WBQ first

Below example of file used.

Reprise EXP_FORE SILICA.csv

Particularities :

• decimal point has to be used
  
• GH quantity has to be up-to date, as it will then be considered as the more accurate one.

Upload manually DPDYN01 / DYN - Reporting (SnapShots)

Then follow upper steps as in the process chain  "DPS Dynasys: META - M - 6.Reporting DynaSys (Snapshots)" / PC_DPS_DYNASYS_09

Reload a monthly snapshot from DynaSys table EXP_FORE_BW

Snapshot to reload has to be previously removed from all level :

• CRDYN11 / DYN - Reporting
  
• DBDYN13 / DYN - Reporting (SnapShots)
• DPDYN01 / DYN - Reporting (SnapShots)
• DBDYN15 / DYN - Reporting PV/PY/PV since 2019
  
  

If reload is done the same month than the deleted snapshot, just run chain PC_DPS_DYNASYS_09 via SE37, Fonction Module RSPC_API_CHAIN_START

(manually chedule again PC_DPS_DYNASYS_09 for next month)

If much later, change Global Filter to set the wanted month and activate the line, and run chain PC_DPS_DYNASYS_09

 Caution variable has to be unactivate afterwards, otherwise following snapshot will be fix to that month!

For info, the variable is used to fix "End of Month View" (C_FCSTMTH)

Important : After a BW snapshot reload QV Team has to be informed to reload their part.

Increase/decrease DR upload from DynaSys

Change values in Global Filter (C_GLBFILT)

Put Active = 'N' to unactive an existing loading.

Add a new line for an extra loading. Please keep the same logic, all loading times are recorded chronologically to make reading easier.

 Do not forget to record your modifications in paragraph "Process Chains" in PC_DPS_DYNASYS_15 comments

Add or remove email address in distribution list for DR data availability  (All GBU except CM (aero))

NB: Distribution list exists on all systems, but user list is only maintained in production system WBP

Transaction SO23 

Name DL_DP_001

Add or remove email adresses. 

List of user email :

• carlos.lau@solvay.com => SD
• alessandro.caleffi@solvay.com => added 25/01/2018
• alessandro.marconcini@solvay.com => added 25/01/2018
• geert.schodts@solvay.com => added 25/01/2018
• marie.pereira@solvay.com => added 28/05/2018 => Silica
• anne.delrue@solvay.com=> added 21/06/2018 => TS

• stacey.walker-robertson-contractor@solvay.com  => added 21/06/2018

• adele.fleming@solvay.com=> added 21/06/2018 => TS

• john.hodges@solvay.com  => added 21/06/2018

• laure.pichot-plasse@solvay.com => added 05/12/2017
• pablo.prieri-contractor@solvay.com => added 13/02/2018

• diogo.paiva@solvay.com => added 18/10/2018

• vendula.sianska@solvay.com => added 06/02/2018

• ismail.ammor-ext@solvay.com 
  

• laura.labbadi-ext@solvay.com  => added 15/07/2019
• maggie.ma@solvay.com  => added  07/08/2019
• min.yu@solvay.com  => added  07/08/2019
• etienne.ribet-ext@solvay.com => added 02/01/2020
• julien.agasse-ext@solvay.com => added 07/04/2020
• katrina.amdur@solvay.com => added 16/11/2020
• bianca.cavagioni@solvay.com => SD, added30/11/2020

• laura.labbadi@solvay.com => added 18/08/2021

• nathalie.perrillatcollomb@solvay.com => added 18/08/2021
• mickael.arzur-ext@solvay.com ==>added 18/02/2022
•  Kevin.Richardot@solvay.com ==> addded 18/03/2022

Removed 

• ann.bailly-ext@solvay.com       removed 12/02/2019
  

• mark.langston@solvay.com      removed 12/02/2019
  

• krystle.medina@solvay.com => removed 13/02/2018
• marion.baggio@solvay.com => removed 28/02/2017
• emmanuel.hatt@solvay.com => removed 05/12/2017
• fabien.piret@solvay.com => removed 29/10/2018
• pablo.prieri-ext@solvay.com => added 05/12/2017 removed 13/02/2018
• Celia.gonzalez-roux@solvay.com => removed 18/10/2018
• pablo.prieri@solvay.com => added 05/12/2017 removed 13/02/2018
• haixiang.song1@solvay.com => removed 01/04/2019
• pablo.prieri-contractot@solvay.com => removed 30/08/2019
• bhaskar.patil@solvay.com => added  07/08/2019 => removed 22/09/2020
• nicholas.barrett@solvay.com => SD => removed 18/11/2020
• joel.mathiot-ext@solvay.com => SD => removed 31/05/2021
• laura.labbadi-ext@solvay.com => removed 18/08/2021

• pablo.prieri@solvay.com => removed 18/08/2021

• nathalie.perillatcolomb-ext@solvay.com => removed 18/08/2021
• cecile.vanpaseuth-ext@solvay.com => removed 18/02/2022

Do not forget to record your modifications in that paragraph

Add or remove email address in distribution list for DR data availability (GBU CM (aero))

NB: Distribution list exists on all systems, but user list is only maintained in production system WBP

Transaction SO23 

Name DL_DP_002

Add or remove email adresses. 

List of user email :

• kevin.anderson2@solvay.com=> CM => added 22/01/2020
• kp.nagabhushana@solvay.com=> CM => added 22/01/2020
• patricia.harrison@solvay.com=> CM => added 22/01/2020
• amanda.pullen@solvay.com=> CM => added 22/01/2020
• clare.downing-smith@solvay.com => CM => added ??

• laure.pichot-plasse@solvay.com => added 22/01/2020

• diogo.paiva@solvay.com => added 22/01/2020

• vendula.sianska@solvay.com => added 22/01/2020

• ismail.ammor-ext@solvay.com => added 22/01/2020

• maggie.ma@solvay.com  => added 22/01/2020
• min.yu@solvay.com  => added 22/01/2020
• etienne.ribet-ext@solvay.com => added 22/01/2020
• julien.agasse-ext@solvay.com => added 07/04/2020

• laura.labbadi@solvay.com => added 18/08/2021

• nathalie.perrillatcollomb@solvay.com => added 18/08/2021
• mickael.arzur-ext@solvay.com => 18/02/2022
• penny.wu@solvay.com => 18/02/2022

Do not forget to record your modifications in that paragraph

Removed 

• romain.clerc@solvay.com => added 22/01/2020 => removed 31/01/2020
• bhaskar.patil@solvay.com => added 22/01/2020 => removed 22/09/2020
• ander.beain@solvay.com => CM => added 22/01/2020 => removed 19/01/2021
• laura.labbadi-ext@solvay.com => removed 18/08/2021

• pablo.prieri@solvay.com => removed 18/08/2021

• nathalie.perillatcolomb-ext@solvay.com => removed 18/08/2021
• kevin.anderson2@solvay.com => removed 18/02/2022

Add or remove email address in distribution list for DiP-PP reporting

Modify email message for DR data disponibility sent to users (All GBU except CM (aero))

Email message has to be modified in WBD and moved to WBQ, then WBP.

 RSA1 / Chain PC_DPS_DYNASYS_15

Change to 'change' mode

right click => maintain message => Successful => Edit Document 

Do not forget to record your modifications in that paragraph.

Modify email message for DR data disponibility sent to users (GBU CM (aero)

Email message has to be modified in WBD and moved to WBQ, then WBP.

 RSA1 / Chain PC_DPS_DYNASYS_15

Change to 'change' mode

right click => maintain message => Successful => Edit Document 

Do not forget to record your modifications in that paragraph.

Clean Forecast Accuracy for all GBU except CM-AERO (ABAP ZBW_DYN018)

Careful, this action is definitive !!!!

Program ZBW_DYN018 / DYN - Forecast Accuracy cleaning (Selective del in DBDYN13 + DPDYN01)

This program has been created to delete data from Forecast Accuracy DSOs.

1) prepare input file as following template

https://drive.google.com/file/d/11v1GZUu8w7Sg7RSG2O1pTETTzabr6bHN/view?usp=sharing

prospect reference (like 0011p00002banehAAA) have to be removed from the list

fill interval of months (even if deletion is asked for a single month)

2) run abap

3) reload CRDYN13 cube

May 2021 : Cleaning has been done for ODYSSEY project (NOVECARE) as Open Gate was not going to keep DynaSys

> Genthin from January 1, 2021 [Carve out]
> UP / Halifax from March, 1rst [Carve out]

ODYSSEY FA Genthin Cleaning.xlsx

ODYSSEY FA UPHalifax Cleaning.xlsx

May 2021 : Cleaning has been done for Silica

Silica FA Cleaning.xlsx

July 2021 : Cleaning has been done for SA&D LATAM / ODYSSEY / WAREHOUSE MULTIMODAL LOGISTICS (NOVECARE) / SILICA

FA Cleaning ODYSSEY & others 072021.xlsx

Feb 2022 : Cleaning has been done for Novecare / RASAL Plant from JAN 2022

FA Cleaning RASAL.xlsx

April 2022 : Cleaning has been done for Novecare / RASAL Plant from JAN 2022 (2nd round)

CS-Analysis-RasalCleaning-March22.xlsx

Perioding cleaning to limit DSO/Cubes volume (TO BE COMPLETED Nathalie)

For FA and DR/BR reports, 

• only GH for current year + 2 years of history is needed
• only FA for  current year + 2 years of history is needed
• only FA for last 6 snapshots is needed 

A selective deletion can be done every year to reduce significaly the volume of records in DSOs/Cubes

May 2021 :  (to be completed Nathalie)

Ship-to/Forecast Customer descriptions onshot loading for CM-AERO and BAAN/PE1 cases

Situation : Ship-to or Forecast Customer description are missing in AERO Demand Review report (BW_QRY_CPDYN11_0006)

This is usually due to mixte DFU (RCS material (extension R) with BAAN or PE1 ship-to (extension B)

1) File format expected

BAAN Customer Forecast Description.csv

2) check file - listed customers should not already have description in table /BIC/TC_CUSTID for language = 'EN'

Caution : if description already exists for the source system, if you load a new description it will impact all BW application. Object C_CUSTID is widely used.

3) load file

=> emty PSA

=> IP: DTS_C_CUSTID_TEXT (PC_FILE) - Full  (change File Name first)

=> check PSA

=> WBP DTP: DTS_C_CUSTID_TEXT (PC_FILE)-> C_CUSTID

4) Description are instantly available in reporting

Planned Evolution