Objective

The objective is to have Raw Material Costs in a Manufacturing query. This query will be used with Qlik Sense Dashboards.

Raw Material Forecast prices are confirmed by Business in a dedicated Workbook.

If a monthly forecast price is unconfirmed by Business, it is extrapolated from the first previous confirmed price at the time of pushing this Forecast into Manufacturing data flow.

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

Data Flow 1: HANA Calculation Views

Data Flow 2: BW Stack

Source Layer

As showed in the data flow above, the BW data source is the Main Calculation View CV_FMCO_RAW_MAT:

Calculation View Logic

Projection P_CS_RAW : Get Raw Material records for GBU NOVECARE (CS) from CV_FMCO_CS_RAW_MAT (see detail below).

Projection P_SP_RAW : Get Raw Material records for GBU SPECIALITY POLYMERE (SP) from CV_FMCO_SP_RAW_MAT (see detail below).

Further Projections are used in Joins to get unit and currency information from UOMCMAT2, C_PLANT, and C_COMPCODE.

CC_QTY_MAT = "CC_QTY_DOC"*"CC_RATIO" (Doc Qty converted in Material Base Unit of Measure with CC_RATIO from UOMCMAT2)

CC_QTY_DOC = "CC_QTY_DOC"*"CC_RATIO" (Doc Qty converted in KG with CC_RATIO from UOMCMAT2)

Currency Conversion in HANA

At Semantic level, both values below are converted using CAR3 exchange rate type.

This Exchange Rate Type has been updated from 'M' to 'CAR3' on October 2022.

CC_VALUE_EUR : Conversion of Document Currency (CC_VALUE_DOC) in EUR :

CC_VALUE_COMP : Conversion of Document Currency (CC_VALUE_DOC) in Company Code Currency :

The Main Calculation View above is composed by CV_FMCO_CS_RAW_MAT and CV_FMCO_SP_RAW_MAT :

CV_FMCO_CS_RAW_MAT

Until 14th December 2022, the Projection P_INVENTORY was built on CV_FMCO_DYNASYS_PP to retrieve Inventory Price (INV).

From that date, the Projection P_INVENTORY is built on Master Data C_MATPNT3 to retrieve Material Standard Price instead.

For technical reason, the identification INV (Inventory) is kept to identify this source of data.

The Calculation View CV_FMCO_CS_RAW_MAT is composed by:

In this CV_FMCO_DYNASYS_PP used to retrieve Forecast (FOR) from DYNASYS, value is always in Euro (EUR).

The join with C_PLANT and C_COMPCODE to retrieve the Company Code currency must no be used in association with the value in EUR.

In the calling CV_FMCO_CS_RAW_MAT, a constant EUR is used for source Forecast (AG_PURCH) at the time of union (Union_1).

The Calculation View CV_FMCO_DYNASYS_PP is composed by:

CV_FMCO_SP_RAW_MAT

The Calculation View CV_FMCO_SP_RAW_MAT is composed by:

Dependencies with other applications

There is a dependency from data flows: 

InfoObjects: C_COMPCDE, C_PLANT, C_PO_ITM, C_SUBACT2, C_AGRITM and C_FLRMAT, C_GLBFILT, C_MATPNT3.

Infoproviders: DB_PUHD1, DB_PUHD2, DB_PUSL1, DB_PUSL2, ABDPDY53 and AAMMIM02S.

Currency Conversion in BW

BW Raw Material data flows are using function module ‘CONVERT_TO_LOCAL_CURRENCY’ to convert currencies.

The exchange rate type used is read from Master Data C_GLBFILT (Global Filter) thanks to parameters below :

C_STREAM = ‘FMCO_COPC’

C_RULE = ‘EXCHGE_RT’

C_GLBFILT = ‘000’

C_SIGN = ‘I’

C_OPTION = ‘EQ’

C_LOW = ‘CAR3’

C_ACTIVE = ‘Y’

The Exchange Rate Type value was 'M' until October 2022.

Propagation Layer

Data Loading from Datasource DTS_FMCO_CO_PC_CV_FI_FL_FRAWMA built on CV_FMCO_CS_RAW_MAT.

Target Infoprovider APCOPC02

Transformation Logic

If a monthly forecast price is unconfirmed by Business, it is extrapolated from the first previous confirmed price at the time of pushing this Forecast into Manufacturing data flow.

This process takes place in the Transformation ABCOPC01 on itself.

Loading frequency

Daily

Business Layer

Data Loading:

Infoproviders ABCOPC01, ABCOPC02 and ABCOPC08:

Loading frequency

Daily

Reporting

Two queries are available in the Workbook : the Query Overall and the Query Detail.

The first one allows to modify the value of the Forecasted Price and automatically filters the second one to a more detailed view.

• The Query Detail is the detailed view of the Raw Material per month with a Forecast quantity for a minimum of one year. All documents (Purchase Order, Contract, and Inventory) are available for each month as well as the forecast price calculated for the forecast quantity.
• The Query Overall is the global view of the Raw Material aggregated by month without the detail by documents. The Forecast price is calculated from the detail and aggregated by month and Raw Material. The Quantity Assigned for each document is available in columns.

Reporting Layer

Aggregation Level

ALCOPC13

Reporting (BW Query)

Global properties

Variables

Definitions

Variable Sequence

Filters

Key figures

Characteristics

Cells

N/A

Conditions

N/A

Exceptions

N/A

Virtual Layer

Composite Providers

CPCOPC01

Reporting (BW Query)

Global properties

Variables

Definitions

Variable Sequence

Filters

Key figures

Characteristics

Cells

N/A

Conditions

N/A

Exceptions

N/A

Process Chains

PC_COPC_01    COPC: MD - D - RMAT Planning (Daily)

PC_COPC_01     COPC: MD - D - RMAT Planning (Daily)

Loading of Master Data Critical Flag from WP1 and PF1 Datasources.

PC_COPC_05    COPC: TD - D - RMAT Panning (Monthly)

PC_COPC_05     COPC: TD - D - RMAT Panning (Monthly)

Loading of Transactional Forecast Price from Contract, Purchase Order, Inventory, and merge with already confirmed Forecast prices from Workbook.

PC_COPC_08    COPC: TD - D - RMAT Planning (Daily)

PC_COPC_08     COPC: TD - D - RMAT Planning (Daily)

Loop logic to validate confirmed prices from Workbook (Currency conversion in EUR).

PC_COPC_12    COPC: TD - M CD7- RMAT Planning (CD7)

PC_COPC_12     COPC: TD - M CD7- RMAT Planning (CD7)

Extrapolation logic of Forecast prices across months.

This Process Chain is called by main Manufacturing Process Chain PC_COPC_10.