Skip to Content

Summary:

This blog describes a concept how to enhance the SAP HANA-optimzed Business Warehouse content Product Cost Forecast and Simulation by tariffs.

The concept bases on the detailed product cost information, that the standard data sources can provide. SAP Business Warehouse modeling and business logic are covered in detail to explain the concept.

The blog contains the following chapters:

 

Motivation

Product Cost Forecast and Simulation has been delivered as part of the SAP HANA-optimised BI Content for SAP Business Warehouse. Details about Product Cost Forecast and Simulation may be found in our blog or the online help.

In the standard content, the impact of conversion costs – costs resulting from manual or machine-based production steps – can be simulated on a coarse level only. However, the need to forecast and simulate these conversion costs in more detail is also a valid business case and a natural enhancement to the standard content. The conversion costs are calculated from tariffs, i.e. standard rates for activities within a company.

With the concept described in this blog, customers can implement the necessary enhancements to the standard content on their own.

(Back to top)

 

Prerequisites

A comprehensive understanding of the HANA-optimized Product Forecasting and Simulation content and the technologies being used – namely SAP BW on HANA and SAP HANA including SQL scripts – is required. Ideally, the standard content has already been implemented and is up-and-running.

The blog outlines a concept and supports this with proposals of how to model SAP Business Warehouse objects and SQL code snippets. Both modelling details and code snippets are used in this document to illustrate the concept but they are not intended to be used productively.

Please read the concept thoroughly and decide, if your business needs can be covered as described. As usually it is recommended to do a proper design before starting the implementation. Other best practices like separating the development from the production and thorough testing before going into production for sure also apply here.

The concept assumes that the proposed standard SAP data sources are used (0CO_PC_PCP_20 and 0CO_PC_PCP_30). Using other data sources is feasible, but in that case the concept cannot be used out-of-the-box.

(Back to top)

 

Outline of the Concept

Standard Content only uses sum of conversion costs

The data sources 0CO_PC_PCP_20 and 0CO_PC_PCP_30 can deliver the basic tariff information and the break-down of conversion costs into individual production process steps. Because the focus of the standard content is on material costs, the tariff information has so far not been used, but filtered out in the data load process. Consequently, only the content in the SAP Business Warehouse (SAP BW) and the underlying SAP HANA database need to be enhanced, no changes are required in the source system.

In general, the Product Cost Forecast and Simulation works as follows:

For each product, the required amounts of materials (“Bill of Material” or “BOM”) are known. For each material, the cost component structure and costs are known. Costs can be rolled up per cost component from raw material level to each product.

Diagram 1 shows this schematically, though real products are typically more complex.

Diagram 1: Example bill of material with cost structure

In this example, the two raw materials 1 and 2 cost 10 $ and 20 $ respectively. The costs are assigned to raw material costs. No other costs exist on this level, so the other cost components are not filled.

The raw material costs roll-up to the semi-finished good S1 based on the required quantities. The production of S1 costs 10 $, that are assigned to conversion costs.

Finally, the production of product P from 2 pieces of S1 costs 10 $ plus the conversion costs of 2 pieces of S1 to add up to 30 $. The raw material costs have been rolled-up and new costs (10 $ each for packaging and freight) are considered.

The cost component information is retrieved from the data source 0CO_PC_PCP_20 and stored in the data store object (DSO) /IMO/CFS_D20. The required quantities are retrieved from the data source 0CO_PC_PCP_30 and stored in the data store object /IMO/CFS_D30.

Diagram 2 shows this schematically:

Diagram 2: Relationship of bill of material and data sources/DSOs

This is what the standard content currently covers. In the current example, the conversion costs that reflect the complete production process can be treated as lump sums.

While it is feasible to change the conversion costs in total, a differentiated view and a simulation of tariff changes are not offered. However, the data sources already deliver a break-down of the total conversion costs per material. The concept described in this paper aims to help implement a tariff-based forecast and simulation.

A breakdown of production costs brings higher precision

The total conversion costs can be broken down into several components – here manual labour and machine hours of two machines M and N.

Diagram 3 shows this:

Diagram 3: Example break-down of production costs

The production of 1 semi-finished good S1 requires (in addition to the materials) 5 minutes of manual labour and 10 minutes of machine M. The tariffs are 15,00 $ and 52,50 $ for 1 hour respectively. Similarly, the production of the product consumes 20 minutes of manual labour and 5 minutes of machine N. Taking the respective tariffs into account, this sums up to 10 $.

This break-down allows to simulate the production costs on a finer level, which gives a higher precision. And a higher precision is required, whenever the production costs play a more important role.

Assume that an increase in wages by 1 $/h has to be simulated. In the standard content, only the sum of conversion costs could be changed: The increase of 6% in wages could be used to change all conversion costs at the same time. The impact would be the same for all conversion costs. However, we see that the wages impact the conversion costs differently, namely roughly 10% for the semi-finished good S1 and 50% for the product. This distinction cannot be made in the standard content.

Remark: This is a simplification. In the standard content one could actually differentiate between semi-finished goods and products. For the sake of keeping the example easy to understand, this fact is neglected here. However, within the group of products or the group of semi-finished goods a distinction is not possible; but important for the same reason outlined here.

What needs to be done to achieve this break-down?

The concept describes two ways of dealing with tariffs, quite similar to the two ways of dealing with material costs: individual changes to one tariff and a driver-based approach to change several tariffs simultaneously.

In our case, changing the hourly wage from 15 $ to 16 $ is an individual change. Changing the machine hour tariffs for both machines M and N because of an increase of the energy costs is a driver-based approach, changing several tariffs by one driver (energy costs).

    1. Calculate and store tariff to material link
      The data source delivers the amount of work (usually a time) and the costs per material.
      The first step is to calculate the tariffs from this information: In our manual labour example, 5 min cost 1,25 $. So, the hourly tariff amounts to 15 $.
      The tariffs are stored together with the individual materials.
    2. Store “standard” or “actual” tariffs as basis for planning
      Tariffs calculated in step 1 are stored in a dedicated InfoProvider without a connection to materials: This is the base line for planning. Also, these “standard” or “actual” tariffs serve as a reference when manually planning future tariffs.Remark: Test have not shown, that different tariffs can exist for the same activity but different materials or cost components. Please verify, that this assumption is valid for your source system. Tariffs have to be unique.
    3. Store “plan” tariffs and create suitable input-ready query
      The tariffs need to changeable individually. From a technical perspective, this requires them to be input-ready.
    4. Correlate drivers to tariffs In addition, the already existing drivers (stored in data store object /IMO/CFS_D01) have to be correlated to the tariffs. This requires an InfoProvider similar to the correlation of drivers and materials.Remark: The grouping of materials and the introduction of so-called material-driver-segments is feasible, but not covered in the following text. The reader may decide to introduce a similar grouping if necessary.
    5. Adapt business logic
      The underlying business logic has to be enhanced: The tariff-based cost changes have to be calculated and then summed up into the corresponding cost components (there could be more than one, simplified for this example).Remark: This can be done either in a dedicated SQL procedure or through enhancing existing SQLs.Once the cost components have been adjusted based on the tariff changes, the roll-up logic to calculate the total costs of a product remains unchanged. And the preparation step, that calculates how many pieces/units of a certain material are required to produce a given product (table /IMO/CFS_ITEMZTN) also remains unchanged.

The required changes are shown in Diagram 4:

Diagram 4: Overview of changes and enhancements

(Back to top)

 

Implementation of the Concept

This chapter provides detailed information about the 5 enhancements.

Be reminded that all data models and SQL snippets are shown to explain the concept in detail. They should not be used without verification and possibly adaptations in your landscape.

The key and data fields are listed (as screen shots). Wherever possible, standard InfoObjects are used, but in certain cases new fields are introduced. It is recommended to stick to standard fields in the same way. If this is not appropriate, please make sure you also adapt any subsequent object and SQL business logic.

Calculate and store tariff to material link

Create an InfoProvider to hold the links between tariffs and the individual materials. In the prototype, the InfoProvider is called ZCFS_T_M (“Tariff to Material Link”).

Use the following fields as shown in Diagram 5:

Diagram 5: Field list “Tariff to Material Link” InfoProvider

In addition to First Planning Period and Fiscal Year Variant, there are two groups of keys that define the two business entities “Material” and “Tariff”: The yellow box contains the fields that identify the Material, the blue box contains the fields that uniquely define an activity to which a tariff is assigned. 0PLANT belongs to both groups.

In the prototype, we introduced the key figure ZCFS_TF in which the calculated tariffs are stored. The key figure is of data type “CURR” and has “0CURRENCY” as Currency.

The tariff has to be calculated based on 0AMOUNT and 0QUANTITY:

ZCFS_TF = 0AMOUNT / 0QUANTITY.

The data flow for this InfoProvider is based on the InfoSource /IMO/CFS_IS30 (Product Costing Analysis: Itemization (0CO_PC_PCP_30)).

The transformation rules can be found in Diagram 6:

Diagram 6: Transformation Rule “Tariff to Material Link”

All fields are directly assigned from the InfoSource to the InfoProvider except for:

  • 0CFS_FPP: this field is sourced from a routine. Please use the same routine as the standard content. Check the transformation rules from InfoSource /IMO/CFS_IS30 to InfoSource /IMO/CFS_IS301 for details.
  • 0FISCVARNT: same as for 0CFS_FPP
  • ZUNIT: a dedicated characteristic has been defined which is sourced from 0UNIT
  • ZCFS_TF: calculation – see above

The Filter for the data transfer process has to be set as follows: select “2” for Stufe and exclude the Item Categories “I”, “L” and “M”.

Store “standard” or “actual” tariffs as basis for planning

Create an InfoProvider to hold the standard or actual tariffs. In the prototype, the InfoProvider is called ZCFS_T_L (“Standard Tariffs”).

Use the following fields as shown in Diagram 7:

Diagram 7: Field List „Standard Tariffs” InfoProvider

Remark: The cost components have been included. This is necessary if the conversion costs are kept in more than 1 cost component. If conversion costs always refer to one single cost component, this InfoProvider may be simplified by omitting 0COSTCOMP / 0CCOMPSTRUC. The other enhancements can then be simplified accordingly.

The “Standard Tariffs” InfoProvider is filled from the “Tariff to Material Link” InfoProvider (see above). The transformation rules can be found in Diagram 8:

Diagram 8: Transformation Rule „Standard Tariffs“

All fields can just be moved from the source to the target. The routine for the field 0VERSION can be taken over from the standard content: Please check the transformation rule into DSO /IMO/CFS_D031. Make sure, that the Tariffs will not be summarized: Check that the aggregation rule for field ZCFS_TF is set to overwrite.

Store “plan” tariffs and create suitable input-ready query

Create an InfoProvider to hold the plan tariffs. In our prototype, the InfoProvider is called ZCFS_T_P (“Plan Tariffs”).

Use the InfoProvider “Standard Tariffs” as a template to create the “Plan Tariffs” as a planning enabled InfoProvider. Switch on “External SAP HANA view”.

Add the field 0FISCPER as Key field. The complete field list is shown in Diagram 9:

Diagram 9: Field List “Plan Tariffs” InfoProvider

Create a MultiProvider or Composite Provider which includes the two InfoProviders “Standard Tariffs” and “Plan Tariffs”. Include all fields of the InfoProvider “Plan Tariffs”. Identify (Assign) all field to both InfoProvider, 0FISCPER will only be mapped to “Plan Tariffs”.

Based on the MultiProvider / Composite Provider, create an Aggregation Level that contains all the fields. Create a suitable Filter and create a Planning Function of type copy to fill the InfoProvider “Plan Tariffs” as follows:

Diagram 10: Define Fields to change

Set the following fields to be changed: 0FISCPER, 0INFOPROVIDER and 0VERSION.

In the prototype, the 0CFS_FPP has been chosen to define a condition. In the copy process, the version has to be changed from “actual” to “plan” and the InfoProvider has to be changed from “Standard Tariffs” (ZCFS_T_L) to “Plan Tariffs” (ZCFS_T_P). The data has to be copied to fill the remaining fiscal periods in accordance with the setting of the planning horizon (DSO /IMO/CFS_D05).

The following example has been taken from the prototype:

Diagram 11: Copy Standard Tariffs to Plan

Based on the Aggregation Level, create an input-ready query that allows to manually adjust the plan values for the future periods. It is recommended to show the standard tariff values for reference. Refer to other input-ready query in the standard content for details.

Correlate drivers to tariffs

The cost drivers are planned based on DSO /IMO/CFS_D01 “Cost Drivers”.

The correlation of tariffs to a driver works analogously to the correlation of material groups (called “Material-Driver-Segments”, 0CFS_SEGMNT). For details, check the standard content DSO IMO/CFS_D02 “Correlation Cost Drivers”.

Adapt business logic

Based on the new InfoProviders created, the business logic needs to be adapted as follows:

  • New SQL logic to calculate the changes of cost component values based on the tariffs
  • Incorporate changed values in existing logic

Calculate changes based on tariffs

First, a new SQL procedure has to be created (or the new logic has to be implemented in the existing procedures). The following code snippets illustrate the logic. They need to be adjusted to fit to your system and setup and must not be used productively.

Remark: The code snippets do not use CE-functions, even though the standard content does.

In the first step, the materials and their tariffs are read in. As common in Product Cost Forecast and Simulation, in this step the tariffs are copied over (“expanded”) to all fiscal periods defined by the planning horizon.

-- Read in the CO-PC tariffs baseline 
-- and expand them to the planning horizon

 join1 = select 
        d0."0VERSION" as "0VERSION" ,
        t0."0CFS_FPP" ,
	t0."0FISCVARNT" ,
	d0."0FISCPER" ,
	t0."0MAT_PLANT" as "0MATERIAL" ,
	t0."0PLANT" as "0PLANT" ,
	t0."0CCOMPSTRUC" ,
	t0."0COSTCOMP" ,
	t0."0ACTTYPE" ,
	t0."0COSTCENTER" , 
	t0."0COSTELMNT" ,
	t0."0PCP_RES" ,
	t0."0COSTPOS" ,
	t0."0CO_AREA" ,
	t0."0UNIT" ,
	t0."ZUNIT" , 
	t0."0CURRENCY" ,		
	sum(t0."0AMOUNT") as "0AMOUNT" ,
	sum(t0."0AMOUNTFX") as "0AMOUNTFX" ,
	sum(t0."0AMOUNTVR") as "0AMOUNTVR" ,
	sum(t0."0QUANTITY") as "0QUANTITY" ,
	sum(t0."ZCFS_TF") as "ZCFS_TF" ,
	round ( sum(t0."0QUANTITY") * sum(t0."ZCFS_TF") , 2 ) as "0AMOUNT_CHECK" 

	from <new DSO “Tariff to Material Link”> as t0	
		
	join
	    
	(select "0VERSION" , "0CFS_FPP", 
                "0FISCVARNT" , "0FISCPER" 
	 from “_SYS_BIC"."system-local.bw.bw2hana.imo/CFS_D05”
	 where "0VERSION"   = :VERSION and
               "0CFS_FPP"   = :ACTPER and
               "0FISCVARNT" = :FISCVARNT
	) as d0

	on  
        t0."0CFS_FPP"   = d0."0CFS_FPP" and 
        t0."0FISCVARNT" = d0."0FISCVARNT"  
		
	where t0."0CFS_FPP"   = :ACTPER and
              t0."0FISCVARNT" = :FISCVARNT 
		
        group by d0."0VERSION" , 
                 t0."0CFS_FPP" , t0."0FISCVARNT" , d0."0FISCPER" , 
                 t0."0MAT_PLANT" , t0."0PLANT" , t0."0CCOMPSTRUC" , 
                 t0."0COSTCOMP" , t0."0ACTTYPE" , t0."0COSTCENTER" , 
                 t0."0COSTELMNT" , t0."0PCP_RES" , t0."0COSTPOS" , 
                 t0."0CO_AREA" , t0."0UNIT" , t0."ZUNIT" , 
                 t0."0CURRENCY" 
;

Now, the drivers and the correlations are read in:

-- Read in the driver and their correlations
joind = select 	
        d."0VERSION" ,
        d."0FISCVARNT" ,
        d."0FISCPER" , 
        d."0CFS_DRIVID" , 
        d."0CURRENCY" ,
        c."0CCOMPSTRUC" , 
        c."0COSTCOMP" , 
        c."0ACTTYPE" , 
        c."0CO_AREA" , 
        c."0COSTCENTER" , 
        c."0COSTELMNT" , 
        c."0PCP_RES" ,
        sum(b."DRIV_BASE") as "DRIV_BASE" ,
        sum(d."0CFS_DRPRC") as "DRIV_PRIC" ,
        max(c."0CFS_COREL") as "COREL" ,
        case 
        when sum(b."DRIV_BASE") = 0.0 or sum(b."DRIV_BASE") is null 
        then 0.0 else (   
        (sum(d."0CFS_DRPRC")-sum(b."DRIV_BASE"))/sum(b."DRIV_BASE") 
         * max(c."0CFS_COREL") 
        ) end as "FACTOR"

        from "_SYS_BIC"."system-local.bw.bw2hana.imo/CFS_D01" as d
        
        join 
        
        (select "0VERSION" , "0FISCVARNT" , "0FISCPER" , 
                "0CFS_DRIVID" , "0CURRENCY" , 
                sum("0CFS_DRPRC") as "DRIV_BASE"
         from  "_SYS_BIC"."system-local.bw.bw2hana.imo/CFS_D01"
         where "0VERSION"   = :VERSION and 
               "0FISCPER"   = :ACTPER and 
               "0FISCVARNT" = :FISCVARNT
         group by "0VERSION" , "0FISCVARNT" , "0FISCPER" , 
                  "0CFS_DRIVID" , "0CURRENCY"
        ) as b
        on 
        d."0VERSION"    = b."0VERSION" and 
        d."0FISCVARNT"  = b."0FISCVARNT" and 
        d."0CFS_DRIVID" = b."0CFS_DRIVID" and 
        d."0CURRENCY"   = b."0CURRENCY"       
                
        join 

        (select "0VERSION" , "0CFS_DRIVID" , "0CCOMPSTRUC" , 
                "0COSTCOMP" , "0ACTTYPE" , "0CO_AREA" , 
                "0COSTCENTER" , "0COSTELMNT" , "0PCP_RES" , 
                max("0CFS_COREL") as "0CFS_COREL"
         from  <new DSO “Correlation to Drivers”>
         where "0VERSION" = :VERSION
         group by "0VERSION" , "0CFS_DRIVID" , "0CCOMPSTRUC" , 
                  "0COSTCOMP" , "0ACTTYPE" , "0CO_AREA" ,
                  "0COSTCENTER", "0COSTELMNT" , "0PCP_RES" 
        ) as c
        on 
        d."0VERSION"    = c."0VERSION" and 
        d."0CFS_DRIVID" = c."0CFS_DRIVID"
              
        where d."0VERSION" = :VERSION and d."0FISCVARNT"= :FISCVARNT 
        
        group by d."0VERSION" , d."0FISCVARNT" , d."0FISCPER" , 
                 d."0CFS_DRIVID" , d."0CURRENCY" , c."0CCOMPSTRUC" , 
                 c."0COSTCOMP" , c."0ACTTYPE" , c."0CO_AREA" , 
                 c."0COSTCENTER" , c."0COSTELMNT" , c."0PCP_RES" 
;

The logic determines the driver values including the future values that may be changed as part of the simulation. The first join determines the so-called base line, namely the driver values for the first planning period. The percentage change of future value compared to base line value is calculated (“Factor”).

The second join determines the correlation between drivers and their future percentage changes on the one hand and the tariffs on the other hand.

As with the standard content (DSO /IMO/CFS_D21), it is possible to manually plan tariffs. In case manually planned values exist that are different to the baseline, these values are used.

join2 = select 
        t0."0VERSION" ,
        t0."0CFS_FPP" ,
	t0."0FISCVARNT" ,
	t0."0FISCPER" ,
	t0."0MATERIAL" ,
	t0."0PLANT" ,
	t0."0CCOMPSTRUC" ,
	t0."0COSTCOMP" ,
	t0."0ACTTYPE" ,
	t0."0COSTCENTER" , 
	t0."0COSTELMNT" ,
	t0."0PCP_RES" ,
	t0."0COSTPOS" ,
	t0."0CO_AREA" ,
	t0."0UNIT" ,
	t0."ZUNIT" ,
	t0."0CURRENCY" ,		
	t0."0AMOUNT" ,
	t0."0AMOUNTFX" ,
	t0."0AMOUNTVR" ,
	t0."0QUANTITY" ,
	t0."ZCFS_TF" ,
	t0."0AMOUNT_CHECK" ,
	tp."ZCFS_TF_PLAN_MANUAL" ,
	t0."ZCFS_TF" * td."FACTOR" as "ZCFS_TF_PLAN_DRIV" ,
		
	case when  tp."ZCFS_TF_PLAN_MANUAL" = 0 or 
                   tp."ZCFS_TF_PLAN_MANUAL" is null or 
                   t0."ZCFS_TF" = tp."ZCFS_TF_PLAN_MANUAL" 
	then 
		case when  ( t0."ZCFS_TF" * td."FACTOR" ) is null 
		then round ( t0."0QUANTITY" * t0."ZCFS_TF" , 2 ) 
		else round ( t0."0QUANTITY" * t0."ZCFS_TF" 
                             * td."FACTOR" , 2 ) end
		            
	else round ( t0."0QUANTITY" * tp."ZCFS_TF_PLAN_MANUAL" , 2 )
	end  as "0AMOUNT_PLAN" 
								
	from :join1 as t0
		
	left join
		
	(select "0VERSION" , "0CFS_FPP" , "0FISCVARNT" , 
                "0FISCPER" , "0CCOMPSTRUC" , "0COSTCOMP" , 
                "0ACTTYPE" , "0COSTCENTER" , "0COSTELMNT" , 
                "0PCP_RES" , "0CO_AREA" , "0PLANT" , 
                "0CURRENCY" , "ZUNIT" , 
                max("ZCFS_TF") as "ZCFS_TF_PLAN_MANUAL" 
	 from <new DSO “Plan Tariffs”> 
	 where "0CFS_FPP"   = :ACTPER and 
               "0FISCVARNT" = :FISCVARNT and 
               "0VERSION"   = :VERSION 
         group by "0VERSION" , 
                  "0CFS_FPP" , "0FISCVARNT" , "0FISCPER" , 
                  "0CCOMPSTRUC" , "0COSTCOMP" , "0ACTTYPE" , 
                  "0COSTCENTER" ,  "0COSTELMNT" , "0PCP_RES" , 
                  "0CO_AREA" , "0PLANT" , "0CURRENCY" , "ZUNIT"
        ) as tp
	on 
	t0."0VERSION"    = tp."0VERSION" and
	t0."0CFS_FPP"    = tp."0CFS_FPP"  and
	t0."0FISCVARNT"  = tp."0FISCVARNT" and
	t0."0FISCPER"    = tp."0FISCPER" and
	t0."0CCOMPSTRUC" = tp."0CCOMPSTRUC" and
	t0."0COSTCOMP"   = tp."0COSTCOMP" and
	t0."0ACTTYPE"    = tp."0ACTTYPE" and
	t0."0COSTCENTER" = tp."0COSTCENTER" and
	t0."0COSTELMNT"  = tp."0COSTELMNT" and
	t0."0PCP_RES"    = tp."0PCP_RES" and
	t0."0CO_AREA"    = tp."0CO_AREA" and
	t0."0CURRENCY"	 = tp."0CURRENCY" and
	t0."0PLANT"       = tp."0PLANT" and
	t0."0UNIT"       = tp."ZUNIT"        
		
	
	left outer join 
	
	(select "0VERSION" , "0FISCVARNT" , "0FISCPER" , 
                "0CURRENCY" , "0CCOMPSTRUC" , "0COSTCOMP" , 
                "0ACTTYPE" , "0CO_AREA" , "0COSTCENTER" , 
                "0COSTELMNT" , "0PCP_RES" , 
                1.0 + sum("FACTOR") as "FACTOR"
         from :joind
         group by "0VERSION" , "0FISCVARNT" , "0FISCPER" , 
                  "0CURRENCY" , "0CCOMPSTRUC" ,"0COSTCOMP" , 
                  "0ACTTYPE" , "0CO_AREA" , "0COSTCENTER" ,  
                  "0COSTELMNT" , "0PCP_RES" 
	) as td
	on 
	t0."0VERSION"    = td."0VERSION" and
	t0."0FISCVARNT"  = td."0FISCVARNT" and
	t0."0FISCPER"    = td."0FISCPER" and
	t0."0CCOMPSTRUC" = td."0CCOMPSTRUC" and
	t0."0COSTCOMP"   = td."0COSTCOMP" and
	t0."0ACTTYPE"    = td."0ACTTYPE" and
	t0."0COSTCENTER" = td."0COSTCENTER" and
	t0."0COSTELMNT"  = td."0COSTELMNT" and
	t0."0PCP_RES"    = td."0PCP_RES" and
	t0."0CO_AREA"    = td."0CO_AREA" and
	t0."0CURRENCY"	 = td."0CURRENCY" 
;

Finally, the results are aggregated to material / cost component level:

t_aggr = select 
 	"0VERSION" ,
        "0CFS_FPP" , 
	"0FISCVARNT" ,
	"0FISCPER" ,
	"0MATERIAL" ,
	"0PLANT" ,
	"0CCOMPSTRUC" ,
	"0COSTCOMP" ,
	"0CURRENCY" ,
        sum("0AMOUNT_PLAN") as "0AMOUNT_TOTAL" 

        from :join2

        group by "0VERSION" , "0CFS_FPP" , "0FISCVARNT" , 
                 "0FISCPER" , "0MATERIAL" , "0PLANT" , 
                 "0CCOMPSTRUC" , "0COSTCOMP" , "0CURRENCY"
;

 

Incorporate tariff extension in exisiting logic

The last step is to incorporate the tariffs extension into the existing logic.

This works similar to the concept of overwrite: The additional values are read in, in this case by calling the new tariffs procedure explained in the previous step. In the final calculation of the new material costs per cost component, the new tariff-based values substitute the original costs. This avoids double counting.

There is a priority in the calculations: First the material prices are calculated – no change yet to the standard content. As described above, the standard content allows to also change conversion costs. It is not recommended to use this option parallel to the enhancements described in this concept, but if one does, the new logic “wins” and overwrites the already changed conversion costs. The highest priority comes from the overwrites. Direct entry of a future cost component always has the highest priority. This could be changed if necessary.

The only procedure that needs to be adapted is SP_MTPR1.

Call the newly created procedure. It is recommended to add the call after the logic that reads in the overwrite prices from DSO /IMO/CFS_D21:

-- Read in the "overwrite" prices from DSO /IMO/CFS_D21
-- In case that this DSO has values, these values 
-- will be used instead of the driver-based forecast results

<some coding skipped>

    t_over = CE_PROJECTION( :t_over_1, [
    	     "0MAT_PLANT",
	     "0PLANT",
             "0COSTCOMP",
	     "0CCOMPSTRUC",
	     "0LOC_CURRCY",
	     "0VERSION",
	     "0FISCPER",
             "0CFS_PRPRCL" as "PRPRCL_OVER" ]);				

-- Read in the cost components that have been influenced by Tariff Changes 
-- using newly created procedure
-- In case that values exists for a given material/cost component, 
-- these values will be used instead of the driver-based forecast results
-- In this way, this works similarly to the overwrite concept

 call <new procedure> ( :VERSION , :ACTPER , :FISCVARNT , :t_tarif_0)  ;

 t_tarif = CE_PROJECTION( :t_tarif_0, [
           "0VERSION" ,
 	   "0CFS_FPP" as "ACTPER" ,
 	   "0FISCVARNT" ,
 	   "0FISCPER" ,
 	   "0MATERIAL" as "0MAT_PLANT",
	   "0PLANT",
           "0COSTCOMP",
	   "0CCOMPSTRUC",
	   "0CURRENCY" as "0LOC_CURRCY",
 	   "0AMOUNT_TOTAL" as "PRPRCL_TARIF" ]);

Further down, join the tariffs as follows: It is recommended to add this join after the join of the “overwrites”:

“overwrites”:
-- add the "overwrites"
    join9 = ce_left_outer_join( :join8 , :t_over, [
            "0MAT_PLANT",
	    "0PLANT",
   	    "0COSTCOMP",
	    "0CCOMPSTRUC",
	    "0FISCPER",
	    "0LOC_CURRCY",
	    "0VERSION"  ]);		

-- add the "tarifs"
    join10 = ce_left_outer_join( :join9 , :t_tarif, [
             "0VERSION" ,
 	     "ACTPER" ,
 	     "0FISCVARNT" ,
 	     "0FISCPER" ,
     	     "0MAT_PLANT",
	     "0PLANT",
             "0COSTCOMP",
	     "0CCOMPSTRUC",
	     "0LOC_CURRCY"  ]);	

Calculate the results. Please adjust “proj5” accordingly:

First, the join to read from has to be changed – in our example join10 is used instead of join9. The tariffs can be added as indicated below:

-- now, calculate the results
  proj5 = CE_PROJECTION( :join10, [
        "0MAT_PLANT",
        "0PLANT",
	"0MATL_TYPE",
	"0COSTCOMP",
	"0CCOMPSTRUC",
	"0FISCPER",
	"0FISCVARNT",
	"0VERSION",
	"0CFS_PRPRCL",
	"0CFS_PRPRCS",
	"0CFS_PRPRCX",
        "PRPRCL_OVER",
        "PRPRCL_TARIF",
	"0LOC_CURRCY",
	"FX1",
	"YFXV1",
	"YFXV_REF1",
	"0CFS_DRPRCC",
	"0CFS_SEGMNT",
	"0UNIT",
	"CT_LOTSIZ",
	"0CFS_LOCK",

-- calculate the FX rates if the local (manufacturing) 
-- currency <> global currency
	CE_CALC( 'if("0LOC_CURRCY" != '':L_GLOB_CURR'', 
        "FX1", if("0LOC_CURRCY" == '':L_GLOB_CURR'',1.0, 0.0)) ', 
        FLOAT) AS "CURR_CHANGE",

	CE_CALC( '"0CFS_PRPRCS"', float) as "0CFS_PRPRCL_0",

	CE_CALC( 'if( "0LOC_CURRCY" = ''USD'', "0CFS_PRPRCS", 
        if(isnull("YFXV1"), 0, 
	"0CFS_PRPRCS"*"YFXV1"))', float) as "0CFS_PRPRC_0",
			
-- The locked portion does not need to be forcasted, 
-- the remaining part needs be forecasted 
-- by multiplying the sum of all relevant driver changes		

	CE_CALC( '"0CFS_PRPRCS"*(if(isnull("0CFS_LOCK"), 0, "0CFS_LOCK")) + 
        "0CFS_PRPRCS"*(1.0+"0CFS_DRPRCC")*
        (if(isnull("0CFS_LOCK"), 1, 1-"0CFS_LOCK"))',
        float) as "CT_PRPRCL_NON_OVER",
		
        CE_CALC( '"0CFS_PRPRCL_PL"*
        if( "0LOC_CURRCY" = '':L_GLOB_CURR'', 1.0,
        if(isnull("YFXV1"), 0, "YFXV1"))', float) 
        as "CT_PRPRC_NON_OVER",
		
-- Calculate tariffs on top		
	CE_CALC( 'if( isnull("PRPRCL_TARIF") or "PRPRCL_TARIF" = 0.0 , 
        "CT_PRPRCL_NON_OVER" , "PRPRCL_TARIF" ) ', float) 
        as "CT_PRPRCL_TARIF",

	CE_CALC( '"CT_PRPRCL_TARIF"*
        if( "0LOC_CURRCY" = '':L_GLOB_CURR'', 1.0,
        if(isnull("YFXV1"), 0, "YFXV1"))', float) as "CT_PRPRC_TARIF" , 	

-- Ignore the driver-based results in case 
-- that overwrite prices have been specified		
	CE_CALC( 'if( isnull("PRPRCL_OVER") or "PRPRCL_OVER" = 0.0 , 
        "CT_PRPRCL_TARIF" , "PRPRCL_OVER" ) ',
        float) as "0CFS_PRPRCL_PL",

        CE_CALC( '"0CFS_PRPRCL_PL"*
        if( "0LOC_CURRCY" = '':L_GLOB_CURR'', 1.0, 
        if(isnull("YFXV1"), 0, YFXV1"))', float) as "0CFS_PRPRC_PL" 
		
  ] , ' "0FISCVARNT"= '':FISCVARNT'' and "0VERSION" = '':VERSION'' ' )  
;

Finally, return the results.

  RESULT = ce_projection( :proj5, [
        "0VERSION",
	"0FISCPER",
	"0FISCVARNT",
	"0MAT_PLANT",
	"0PLANT",
	"0MATL_TYPE",
	"0CFS_SEGMNT",

	"0COSTCOMP",
	"0CCOMPSTRUC",
	CE_CALC(''':L_GLOB_CURR''', nvarchar(5)) AS "0CURRENCY",
	"0UNIT",
	"0LOC_CURRCY",
	"YFXV1",
	"YFXV_REF1",
	"FX1",

	"0CFS_LOCK",

	"CURR_CHANGE",
	"0CFS_DRPRCC",
		
	"0CFS_PRPRCL_0",
	"0CFS_PRPRC_0",
	"0CFS_PRPRCL_PL" AS "0CFS_PRPRCL",
	"0CFS_PRPRC_PL" AS "0CFS_PRPRC",
	"CT_LOTSIZ",

-- Non-overwrite prices are handed out in order to verify results. 
-- They are not available in BW without additional customizing.
	"CT_PRPRCL_NON_OVER",
	"CT_PRPRC_NON_OVER"   ] );

There is no need to change the output structure and consequently, all other logic can remain unchanged.

(Back to top)

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Elliott Bajema

    Excellent stuff Rolf – thank you for such a clear explanation.
    We are looking forward to implementing this approach with a customer, and to updating this thread with any feedback.

    One question. Considering PCFS is part of BI Content, what approach would you advise in implementing these modifications?
    Would you advocate following the ‘usual’ approach of creating these under a new custom package and assigning to a delivery unit for transport?

    (0) 
    1. Rolf Sieberg Post author

      Hello Elliott,

      any feedback is welcome for sure!

      Yes, I would recommend standard processes for the development – just treat this like any other customer enhancement.

      Best regards,
      Rolf

      (0) 
  2. Lars Breddemann

    Really glad to see some real-life implementation blog that goes way beyond the too common “here are five SQL to monitor HANA” type of posts! Thanks for writing this up.

    One question I have is: why are the CE_-functions used here? Mixing them with SQL limits the options for the optimizer to rewrite and reorder statement execution, so there surely is a huge benefit in this harder to maintain approach. But what is it?

     

    (0) 
    1. Rolf Sieberg Post author

      Hello Lars,

      thank you for your kind words.

      Why did I mix CE functions with plain SQL? Well, historical reasons for this mostly: When we created the Product Cost Forecast and Simulation content some years ago, the CE functions were recommended, not least for performance reasons. So, the standard content still has the CE functions and this is why they still appear in the code snippets.

      By now, SAP HANA has evolved: If I had to create content now, I would no longer use the CE functions. This is why my enhancements have been written in plain SQL to indicate this change in recommendation.

      Hope this helps,
      Rolf

       

       

       

       

      (0) 
      1. Lars Breddemann

        Thanks for the swift answer and clarification. It’s exactly what I expected.

        Again, I’m really happy about this well-written and extensive post showing actual development results. Really, I wouldn’t mind if you would post some more of this (or motivate colleagues to do it).

        Cheers,

        Lars

        (0) 

Leave a Reply