Objective

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

Raw Material Forecast prices are first proposed to the Business in a dedicated Workbook.

Business has to confirm those prices in the Workbook for each forecast period.

If a monthly forecast price is unconfirmed by Business, it is extrapolated from the first previous confirmed price, at the time of pushing the data into the Manufacturing data flow (monthly on 7th).

Functional Logic

There are three key transactional sources supporting proposed Raw Material Unit Price: 

→ Open Purchase Orders

For Raw Material and Plant combination : Purchase Orders with quantities and no Goods Receipt.

Scheduling delivery line date is used to allocate to relevant period.

→ Open Purchase Contracts

For Raw Material and Plant combination : Contracts quantity excluding quantities of Purchase Orders created in reference to Contract.

Validity dates at Header and Price Determination Date at line-item level

→ Material Standard Price

For Raw Material and Plant combination : Standard Price from Material Accounting information

Current date is used and extrapolated to allocate to all relevant periods.

The Forecast quantity for a Raw Material/Plant/Period is then consumed chronologically by relevant Purchase Orders and Contracts.

The proposed Unit Price is the weighted average price of the assigned documents.

In case of no document for Raw Material and Plant combination, the Material Standard Price is proposed.

Example :

For a Raw Material/Plant/Period a Forecast quantity of 50 KG is scheduled in Dynasys.

Looking for Purchase Orders :

This quantity can be partially consumed by a Purchase Order of 30 KG, with a unit price of 12 EUR.

Looking for Contracts :

The quantity left can be consumed by a Contract for 20 KG, with a unit price of 10 EUR.

Price Unit proposed :

The average price is the sum of the amounts, divided by the Forecast quantity to consume.

Purchase Order amount = 30 x 12 EUR = 360 EUR

Contract amount = 20 x 10 EUR = 200 EUR

Weighted Average Price = 360+200 / 50 = 11,20 EUR

For the Raw Material/Plant/Period with a Forecast quantity of 50 KG, the proposed Unit Price is 11,20 EUR.

This price must be confirmed by Business on the relevant line in the Workbook.

The confirmed price will be populated in the Manufacturing data flow, and extracted to the Qlik dashboards.

BW 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.

It uses an Input Parameter IP_EXCHGE_RT, reading 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’

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, Technology Solutions, and Aroma data, on periods after current month of loading.

("CPFCTR1_2" = 'CS' or "CPFCTR1_2" = 'TS' or "CPFCTR1_2" = 'PA') AND
"C_DATATYP" ='PLANNED_PURCH_MAT'
AND "0CALMONTH"  >= "ZACTUALDATE"

In this Forecast source from DYNASYS, value is always in Euro (EUR).

The retrieved Company Code currency must no be associated to the value in EUR.

At the time of the Union (Union_1), a constant EUR is used for this Forecast source.

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

The filter expression is getting Novecare, Technology Solutions, and Aroma data only.

"CPFCTR1_2" = 'CS' or "CPFCTR1_2" = 'TS' or "CPFCTR1_2" = 'PA'

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, Technology Solutions, and Aroma data only, and exclude orders without any quantity left to deliver.

("CPFCTR1_2" = 'CS' or "CPFCTR1_2" = 'TS' or "CPFCTR1_2" = 'PA')
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, Technology Solutions, and Aroma data only.

"CPFCTR1_2" = 'CS' or "CPFCTR1_2" = 'TS' or "CPFCTR1_2" = 'PA'

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

A set of 11 calculated columns CC_ is merging data from Critical Raw Mat or Dynasys Forecast, thanks to Formulas.

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), with the PO quantity to use (CC_PO_QTY).

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

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

Projection Contract : Extraction of Contract Items from Master Data C_AGRITM, with the Target Quantity to use (TARG_QTY).

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 Aggregations.

Projection Projection_1 : Purpose is to filter the Contract with full quantity already consumed by Purchase Orders.

"CC_OPEN_CO_KG" > 0

CC_OPEN_CO_KG is computed as below:

CC_PO_QTY = "K_QCDEUA"-"K_QCDEUAR" (Order qty - Order qty Returned)

CC_OPEN_CO_KG = CC_CO_QTY = "TARG_QTY"-"CC_PO_QTY"

Detail of CV_FMCO_OPEN_PO :

Aggregation AG_PUSL1 and AG_PUSL2 : Extraction of Purchase documents from Rhodia source system (DB_PUSL1) and Solvay source system (DB_PUSL2), with the PO quantity and amount to use (CC_PO_QTY / CC_PO_AMNT).

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

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

Aggregation AG_PUHD1 and AG_PUHD2 : Extraction of Good Receipts from Rhodia source system (DB_PUHD1) and Solvay source system (DB_PUHD2), with the GR quantity and amount to use (CC_PO_QTY / CC_PO_AMNT).

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

("C_MATNR2" !='') AND ("C_VGABE" ='1') AND ("C_LOEKZ" = '' )

To filter the PO with full quantity already delivered, the expression is part of the main calling CV_FMCO_CS_RAW_MAT :

"CC_OPEN_PO_KG" > 0

CC_OPEN_PO_KG is computed as below:

CC_PO_QTY = "K_QCDEUA"-"K_QCDEUAR" (Order qty - Order qty Returned)

CC_GR_QTY = "K_QEMUA"-"K_QEMUAR" (Good Receipt qty - Good Receipt qty Returned)

CC_OPEN_PO_KG = "CC_PO_QTY"-"CC_GR_QTY"

CC_PO_AMNT = "K_VCDEDD"-"K_VCDEDDR" (Order amount - Order amount Returned)

Detail of CV_FMCO_DYNASYS_PP :

Projection P_DYNASYS : Extraction of Forecast lines from DYNASYS Infoprovider

The filter expressions are filtering Forecast (PLANNED_PURCH_MAT) and Inventory (PROJECTED_INVENTORY or ACTUAL_PROJ_INV) lines from Dynasys, with a period from current period.

"0CALMONTH"  >= "CC_ACT_MONTH"
AND ("C_DATATYP" ='PROJECTED_INVENTORY' OR "C_DATATYP" ='ACTUAL_PROJ_INV' 
OR "C_DATATYP" ='PLANNED_PURCH_MAT' )

Note : Inventory data is not used anymore since 14th December 2022, date from which the Main CV_FMCO_CS_RAW_MAT is using Standard Price from C_MATPNT3 and not Inventory price in Projection P_INVENTORY.

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 the Union (Union_1).

The Calculation View CV_FMCO_DYNASYS_PP is composed of CV_DY_DIP_REPORTING below, which is the DYNASYS View built for DIP Reporting.

The Main Calculation View CV_FMCO_CS_RAW_MAT is also composed of CV_FMCO_SP_RAW_MAT for Specialty Polymers GBU (SpP) data.

CV_FMCO_SP_RAW_MAT

Aggregation AG_RM_CRITIC : Extract list of Critical Raw Material.

The filter expression is getting SpP data only.

"CPFCTR1_2" = 'SP'

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

The filter expression is getting SpP data only.

"CPFCTR1_2" = 'SP'

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 SpP data only, and exclude orders without any quantity left to deliver.

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

Projection P_INV : Extract latest Inventory Price of Material. Built on CV_FMCO_SP_PRICE.

These projections are all Inner joined with the list of Critical Raw Material coming from the Aggregation above.

The Calculation View CV_FMCO_SP_RAW_MAT is composed by:

This CV is already detailed in the Novecare part above, in CV_FMCO_CS_RAW_MAT.

This CV is already detailed in the Novecare part above, in CV_FMCO_CS_RAW_MAT.

Projection Projection_1 : Extract Inventory lines for Raw Material, from ADSO AAMMIM02S (Price control (Solvay)).

Filter expression is selecting Value Type initial only.

"C_VALTYPE" = ' '

Latest Inventory line is kept in the Rank Rank_2 on 0CALDAY.

Further Projections are used in Joins to get Standard Price from C_MATPNT3, and unit conversion ratio from UOMCAMT2.

BW Data Flow

BW Logic

From HANA Datasource  with all Purchase documents identified for Raw Material Forecast, data is handled in several BW layers to deliver a detailed view of Forecast in one hand, and an overall view in another hand.

The global logic is as follow (see ABAP code comments for detail) :

Bullet 1 : Datasource DTS_FMCO_CO_PC_CV_FI_FL_FRAWMA built on HANA source CV_FMCO_RAW_MAT.

Bullet 2 : Consolidation of data into APCOPC02

Start Routine :

→ Creation of fake Raw Mat lines on current period, for Raw Mat without Forecast on any of the coming 12 months.

→ Deletion of all Forecast lines on period above current month + 12.

Bullet 3 : Price calculation of data into ABCOPC08

End Routine :

→ Creation of internal tables for each type of incoming lines (Forecast FOR, Order ORD, Contract CON, Standard Price INV)

→ Extrapolation of each Raw Material / Plant combination on all missing periods from current to current + 12 (with Forecast quantity null).

→ Loop at all Forecast lines (FOR) for each Raw Material / Plant combination, and read corresponding documents (ORD, CON) to determine quantity consumption, and compute each price accordingly.

→ Price conversion in Buyer currency (CAR3)

Path to the Overall view of data

Bullet 5/6 : Aggregation and distribution of fields into ABCOPC02

For each Raw Material / Plant combination, transposition of quantities and prices in columns, according to each type of source document (ORD, CON, INV, FOR).

End Routine :

→ Compute Unit Forecast Price for each Raw Material / Plant combination

→ Force Standard Unit Price if no Forecast quantity on period

Bullet 10 : Adjust structure for Planning layout into ABCOPC01

This is the object with BI Planning functionalities, used as a source of query.

It is encapsulated in Composite Provider CPCOPC11, used in Aggregation Level ALCOPC13 on which the query is built.

This is the source of Manufacturing data flow, started monthly on 7th.

Bullet 7 : Retrieve and adjust Business updates into ABCOPC02

Conversion of quantities and prices updated by Business in workbook, according to Buyer information from Critical Raw Material C_FLRMAT.

Path to the Detail view of data

Bullet 11 : Detailed structure of Price calculation ABCOPC08 (Bullet 3) is used as source in Composite Provider CPCOPC01, on which the detail query is built.

Reporting

A global workbook BW_WBK_FL_RMAT_FORCAST is built on 2 queries :

• Overall view query BW_QRY_ALCOPC13_0001
• Detail view query BW_QRY_CPCOPC01_001

See details in Reporting layer.

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.

Critical Raw Material source in SAP

Master Data C_FLRMAT is used in the data flow to get critical information for Raw Materials.

Information are extracted from SAP source systems PF1 and WP1.

Transactions and tables used in SAP to handle those data are : 

Transaction ZCRIT
Table ZFI_ICM_CRITIC for Novecare data.

Transaction ZCRIT_TS
Table ZFI_ICM_CRI_TS for Technology Solutions data.

Transaction ZCRIT_PA  
Table ZFI_ICM_CRI_PA for Aroma data.

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. This is the view where Business must confirm the proposed prices.

Only Raw Materials flagged as Critical are displayed in the queries.

The field “Critical flag” (C_FLRMATF) is used as global filter.

Available values for the field are :

“ “  : Not critical

“X” : Critical

“B” : Critical No BOM

“I”  : Critical Internal

“E” : Critical External

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.