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 shown 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_KG = "CC_QTY_DOC"*"CC_RATIO" (Doc Qty converted in KG with CC_RATIO from UOMCMAT2)

Projection FILTER_DOC_12_MONTH : Expression to exclude all lines on periods before current month of loading, except ones with null value representing Contract lines.

( "CC_CALMONTH" >= "CC_ACTUAL_DATE" )
OR
("CC_CALMONTH" = '000000' OR  isNull("CC_CALMONTH" ))

Until 14th December 2022, this expression was also excluding lines on periods after current month of loading + 12.

This exclusion has been moved to ABAP stack in order to do a specific process for Material without any line in range Current Month -> Current Month + 12.

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 sourced on CV_FMCO_CS_RAW_MAT and CV_FMCO_SP_RAW_MAT :

CV_FMCO_CS_RAW_MAT

Aggregation AG_PURCH_PLAN : Is the main Forecast source from Dynasys. Built on CV_FMCO_DYNASYS_PP.

The filter expression is getting Forecast Dynasys lines (PLANNED_PURCH_MAT), for Novecare data, on periods after current month of loading.

"CPFCTR1_2" = 'CS' AND
"C_DATATYP" ='PLANNED_PURCH_MAT'
AND "0CALMONTH"  >= "ZACTUALDATE"

Projection P_OPEN_CO : Extract all the Contracts with global quantity. Built on CV_FMCO_OPEN_CO.

The filter expression is getting Novecare data only.

"CPFCTR1_2" = 'CS'

Projection P_OPEN_PO : Extract all the Purchase Orders with quantity left to deliver. Built on CV_FMCO_OPEN_PO.

The filter expression is getting Novecare data only, and exclude orders without any quantity left to deliver.

("CPFCTR1_2" ='CS')
and "CC_OPEN_PO">0

Projection P_INVENTORY : Extract list of Material/Plant with Standard Price from Master Data C_MATPNT3.

The filter expression is selecting lines with chart of accounts but without value type.

(("0CHRT_ACCTS" !=''))

AND (("C_VALTYPE"=''))

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.

These projections are all Inner joined with the Forecast Dynasys lines coming from the Aggregation above.

Projection C_FLRMAT : Extract list of Critical Raw Material.

The filter expression is getting Novecare data only.

"CPFCTR1_2" = 'CS'

This projection is full outer joined with the Union of previous lines from Dynasys Forecast, in order to display Materials without any Forecast in query.

Detail of CV_FMCO_OPEN_CO :

Aggregation AG_PUSL1 and AG_PUSL2 : Extraction of Purchase documents from Rhodia source system (DB_PUSL1) and Solvay source system (DB_PUSL2).

The filter expressions are getting Purchase documents not deleted with a Material code.

("C_MATNR2" !='') AND ("C_LOEKZ" ='') AND ("C_LOEKZK" ='')

Projection C_AGRITM_REPORTING : 

The filter expression is getting Contract Items not deleted with a Material code, and with a period not null.

("C_MATNR2" !='') AND ("C_LOEKZ" ='') 
AND ( "CC_CALMONTH" != '000000' OR not(isnull("CC_CALMONTH" )))

This Projection is left joined with the Purchase information coming from above Projections.

Detail of CV_FMCO_OPEN_PO :

DB_PUHD1, DB_PUHD2, DB_PUSL1, DB_PUSL2

Detail of CV_FMCO_DYNASYS_PP :

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.