Objective

The objective is to deliver a Sales Costs query with Forecast prices in the next periods.

This query is used with Qlik in transparency Dashboards.

Sales data are coming from BW objects Dynasys or Picaso (No more Order Book since May 2024).

Roles & Access

Roles and access

List of application role + menu role  and explanation if we have several applications role with specials rules.

Authorization variables

List of authorization variables mandatory for the application.

Dataflow overview

Source Layer

As showed in the data flow above the data source is a Main Calculation View CV_FMCO_QTY_SLS_FRC.

PICASO data is coming from view CV_FMCO_SP_PICASO detailed below (Projection P_PICASO_TRANSPOSE).

DYNASYS data is directly extracted from Infoprovider CRDYN11 (Projection P_CRDYN11).

Forecast quantity is the Constrained Forecast

→ It is populated with source field K_DYN_034 (Final Forecast ETD) for GBUs Aroma (PA), Novecare (CS), and Technology Solutions (TS)

→ It is populated with source field K_CONFOR (Constrained Forecast) for all other GBUs

Another quantity field is the Unconstrained Forecast :

→ It is populated with source field K_DYN_061 (Demand Review Quantity (ETD) for GBU Novecare (CS) - (Field K_DYBUD (Budget) before December 17th 2024)

→ It is populated with source field K_DYN_044 (Sales Team Forecast ETD) for all other GBUs

Net Price from DYNASYS is  :

→ The Sales Price [K_SALPEKG] in Local currency [0LOC_CURRCY] to convert in EUR, for GBUs Aroma (PA), and Technology Solutions (TS)

→ The Budget Price EUR/KG ("Budget Net Sales" [K_BUDEUR] / "Budget qty in KG" [K_DYBUD]) in EUR, for GBU Novecare (CS) (Deleted since December 2024)

Below are the Calculation Views that make up the Main Calculation View CV_FMCO_QTY_SLS_FRC:

Dependencies with other applications

There is dependency on the following data flows.

Forecast sources :

DYNASYS - Reporting (Dynamic) : CRDYN11

Sales Forecasts from Picaso (Solvay) :  AACOPC01 and AACOPC04

DYN - Demand Planning AERO (Composite Materials) : CPDYN11

Price Conditions :

Price Records Extraction Table (Solvay) : AACOPC02

Price Records Extraction Table (Rhodia) : AACOPC03

Sales Unit Price Forecast : APCOPC03

Source table from SAP is ZSD_ICM_PRICING and is refreshed once a day in each system PF1 and WP1.

PF1 :

The program scheduled for refresh is Z1V_PRICE_COND_RECORDS_LIST with variant SPP_PCCU, which is submitting the extraction program Z1V_PRICE_COND_SELECT_SCREEN with variant PCCU_SPP (current year of validity).

WP1 : 

The program scheduled for refresh is ZWOCT03400 with variant ICM_PRICE.

Alternative Units of measure for quantity conversion :

GENERATED DATASTORE OBJECT (UNITS OF MEASURE): UOMCMAT2

Important : The generated DSO units of measure UOMCMAT2 must be properly populated in the BW system, otherwise the Sales data loading will last hours, trying to access the Database for each CPC unit to convert.

This DSO is deleted and fully loaded daily, with Process Chain PC_UOMC_MAT_2.

Acquisition Layer

Data Loading:

Price Conditions are populated from PICASO and DYNASYS Datasources.

Infoprovider AACOPC02: Price Records Extraction Table (Solvay)

Datasource DTS_ZBW_ICM_PRICING built on table ZSD_ICM_PRICING from PF1 source system.

Infoprovider AACOPC03: Price Records Extraction Table (Rhodia)

Datasource DTS_ZBW_ICM_PRICING built on table ZSD_ICM_PRICING from WP1 source system.

Infoprovider AACOPC01: Sales Forecasts from Picaso (Solvay)

Datasource DTS_ZBW_PICASO_PIR built on table Z1C_PICASO_PIR from PF1 source system.

Sales Forecast are populated from PICASO, DYNASYS and ORDERBOOK .

Infoprovider AACOPC05: Staging DTS_CV_FMCO_QTY_SLS_FRC

Datasource DTS_CV_FMCO_QTY_SLS_FRC built on Calculation View CV_FMCO_QTY_SLS_FRC from HANA.

Sales Forecast for Composite Materials GBU are populated from query "BW - FLICM Extraction of Demand Review for CM" (BW_QRY_CPDYN11_0007 (copy of BW_QRY_CPDYN11_0006)) .

Infoprovider AACOPC08: Sales Forecast - DP query for CM

Query BW_QRY_CPDYN11_0007 

This source query is plugged directly to DYNASYS objects.

The extraction is done with a conversion in USD (CAR4, Calendar Month of each line as conversion date)

Fix from March 2025 : Conversion in USD replaces the conversion in EUR which was using inconsistent Fx rates from Dynasys.

Loading frequency

Daily

Propagation Layer

Data Loading:

Price Conditions sources are merged in Propagation layer.

Infoprovider APCOPC03: Sales Unit Price Forecast

Source currencies are converted to EUR.

Currency Conversion

Prices are converted from original currency to EUR by calling successively the 2 Function Modules below : 

• BAPI_CURRENCY_CONV_TO_INTERNAL : To switch source value in internal format for specific currencies (KRW, IDR...)
  • Specific currencies are identified with less than 2 decimals in table TCURX
• CONVERT_TO_LOCAL_CURRENCY : To convert above result in EUR 
  • Currency translation date used is the current date of loading

The date used for currency conversion is based on Global Filter Master Data parameter MONTH_DATE :

The LOW value is used to shift the actual date of loading to the number of months specified.

Here the current date June 12th 2023 will become May 12th 2023 as the conversion date to use.

Loading frequency

Daily

Business Layer

Data Loading:

Infoprovider ABCOPC07:

Logic to retrieve the Sales Forecast Price:

Specific Routines

The logic detailed above takes place in the respective Transformations below : 

• TRSF:  AACOPC05 -> IB_ABCOPC07

Update from May 2024 : The AMDP routine below has been deleted because the Invoice price is not used anymore from BW in the Transparency Dashboard.
AMDP End Routine : To look for the last invoice according to CPC
Warning : If the structure of the Infosource IB_ABCOPC07 is enhanced, the AMDP code must be adapted accordingly in the OUTTAB2 Select.

• TRSF: IB_ABCOPC07 -> ABCOPC07
  ABAP Start and End Routines : To look for Price Conditions according to CPC

Currency Conversion

Prices are converted from original currency to EUR by calling successively the 2 Function Modules below : 

• BAPI_CURRENCY_CONV_TO_INTERNAL : To switch source value in internal format for specific currencies (KRW, IDR...)
  • Specific currencies are identified with less than 2 decimals in table TCURX
• CONVERT_TO_LOCAL_CURRENCY : To convert above result in EUR 
  • Currency translation date used is the current date of loading

The date used for currency conversion is based on Global Filter Master Data parameter MONTH_DATE :

The LOW value is used to shift the actual date of loading to the number of months specified.

Here the current date June 12th 2023 will become May 12th 2023 as the conversion date to use.

Loading frequency

Daily

Virtual Layer

Composite Providers

CPCOPC07

Reporting (BW Query for Qlik)

Global properties

Variables

Definitions

Variable Sequence

Filters

Key figures

Characteristics

Cells

N/A

Conditions

N/A

Exceptions

N/A

Reporting (BW Queries for Analysis)

For Sales analysis purposes in BW, the Workbook BW_WBK_FL_SAP_COND is created with a tab for each query below : 

BW_QRY_APCOPC03_0001    SAP Price Conditions in EUR

Built on ADSO APCOPC03 in the Propagation layer.

This query gives the full structure of Price Conditions extracted from both SAP systems PF1 and WP1.

Condition amounts are converted in EUR, but condition units are the original ones.

Sources of this ADSO are ADSO AACOPC02 and ADSO AACOPC03.

BW_QRY_AACOPC02_0001    SAP Price Conditions Solvay

Built on ADSO AACOPC02 in the Acquisition layer.

This query gives the condition amount in the original currency, extracted from SAP system PF1.

The source is a SAP specific table ZSD_ICM_PRICING on the PF1 system.

BW_QRY_AACOPC03_0001    SAP Price Conditions Rhodia

Built on ADSO AACOPC03 in the Acquisition layer.

This query gives the condition amount in the original currency, extracted from SAP system WP1.

The source is a SAP specific table ZSD_ICM_PRICING on the WP1 system.

The Workbook must be manually refreshed, with an accurate selection in each query part, to reduce the volume of data displayed.

Selection variables can not be merged between the 3 queries, since the objects are not the same in the corresponding ADSO, due to a different BW layer (Infoobjects vs Fields).

Prompt selections

The workbook is not refreshed by default on opening, in order to prompt the 3 Datasources independently if needed.

It is a very time consuming process to display the SAP Price Conditions of a global Run (around 300.000 records for active conditions only).

Running period and active ranges are filled by default, but you must use additional selections to restrict the scope, and get a result in a reasonable time. 

In the global prompt (all Datasources), you will see 3 blocks (one for each Datasource) with the relevant selections for each.

Selection variables can not be merged for these Datasources, since objects in query are not the same (Fields vs Infoobjects).

Therefore, you should repeat same selections for each Datasource in each relevant block.

Running Period = Monthly Snapshot of the SAP Price Conditions.

Month from = Always use ≤ to the Running Period, to exclude conditions not active yet.

Month to = Always use ≥ to the Running Period, to exclude conditions not active anymore.

Date from = Always use ≤ to the first day of the Running Period, to exclude conditions not active yet.

Date to = Always use ≥ to the first day of the Running Period, to exclude conditions not active anymore.

Condition Record Nbr → Always exclude number 0001506065 which is a condition in % leading to a unit check failure.

Date from = Always use ≤ to the first day of the Running Period, to exclude conditions not active yet.

Date to = Always use ≥ to the first day of the Running Period, to exclude conditions not active anymore.

Workbook details

Global properties

Variables

Definitions

Filters

Key figures

Characteristics

Global properties

Variables

Definitions

Filters

Key figures

Characteristics

Global properties

Variables

Definitions

Filters

Key figures

Characteristics

Process Chains

Process Chains below are scheduled in BW daily.

Transparency extraction from BW is scheduled daily every morning.

Manual Loading procedure for a monthly Run:

Execution of 4 PCs directly from RSPC transaction (Starters are scheduled in Immediate mode).

• • Execution of PC_COPC_02 (COPC: TD - D - Sales Forecasts from Picaso)
    => 2 minutes

• • Execution of PC_COPC_03 (COPC: TD - D - Price Records Extraction Table (Solvay))
    => 2 minutes

• • Execution of PC_COPC_04 (COPC: TD - D - Price Records Extraction Table (Rhodia))
    => 5 minutes

• • Execution of PC_COPC_06 (COPC: TD - M - Sales Unit Price / Quantity Forecast)
    => 30 minutes