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, Picaso, and Order Book.

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.

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

Price Conditions :

Price Records Extraction Table (Solvay) : AACOPC02

Price Records Extraction Table (Rhodia) : AACOPC03

Sales Unit Price Forecast : APCOPC03

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.

Order Book and Invoices sources :

SO Order Book: OPEN ORDER+SHIPPED NOT INVOICED (Solvay) : CRSDSO02

SO Order Book: OPEN ORDER+SHIPPED NOT INVOICED (Rhodia) : CRSDSO42

SO Global: Invoices (Solvay) : DBSDSO06

SO Global: Invoices (Rhodia) : DBSDSO46

Propagation Layer

Data Loading:

Infoprovider AACOPC05:

Loading frequency

Daily

Business Layer

Data Loading:

Infoprovider ABCOPC07:

Logic to retrieve the Sales Forecast Price:

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 must be added in a Meta Chain for an automatic run the 7th of each month.

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