Internal Pricing Engine

Internal Pricing Engine (IPE) is basically composed of price item type, pricing table (also known as lookup table), pricing scheme, pricing step and price item type category. It provides functionality for currency conversion with maintained exchange rates. Moreover, IPE has an easy retrieval of price data based on validity of price records and the quote pricing date and manipulation of price data for quotation.

Pricing scheme, pricing step, data maintained in lookup table and currently executed quote’s information are the input elements to the IPE. The output from IPE are sales item net price (based on each sales item and known as sales item unit price), sales item total price (based on total quantity), document header net value and the breakdown of each of them. Starting from the following section, each of components will be described in detail.

Price Item Type

Price item type is one of the most basic parts of internal pricing engine. It is maintained as master data. Administrator of CPQ system needs to maintain all the price item types required from the master data management screen. The following figure shows price item type sample.

No	Data Point	Mandatory	How it works
1	Name	Yes	Price Item Type Name. Unique name for each of the price item type.
2	Price Item Type Category	Yes	It will be explained detailed in another section Price Item Type Category
3	Price Item Type Sign	No	Two options:  PLUS, and MINUS are available. Default value is PLUS, which will add up to the cost of quote and sales item net price. It is used whether the value of price item type is added or subtracted from sales item amount.
4	Price Item Relevance	No	Three options: 0, 1, 2 are available. Determines whether the price item type is applicable to quote (0) or sales item (1) or both (2). Default value is 1, only applicable to sales Item.
5	Default	No	N/A to IPE
6	Is Percent Value	No	Determines whether the price item type is percentage or amount.
7	Is Quantity Based	No	Determines whether the price item type is based on quantity or lump sum.
8	External Id	No	N/A to IPE
9	ERP Id	No	N/A to IPE
10	Is Delta	No	Deterines whether the price item type is delta price item or not. Detail explanation about delta pricing is described in section Delta Pricing.

 

Pricing Table

Pricing table (also known as Lookup Table) are the place holders to store data inside CPQ system. It supports the ability to search with well-defined expression or criteria.

No	Data Point	Mandatory	How It Works
1	ERP Id	No	ERP Id of lookup table.
2	Name	Yes	Name of lookup table.
3	Has Validity	No	Determines whether lookup table if validity based or not. If YES, it will add extra “validFrom” and “validTo” fields automatically.
4	Delta Price Table	No	YES, represents this lookup table is delta price table and will add “PRICE_ITEM_TYPE” and “DELTA_PRICE” fields automatically. By default, administrator should not choose this option to avoid unnecessary columns. The usage of this will be explained in section Delta Pricing

No	Data Point	Mandatory	How It Works
1	Name	Yes	Lookup field name
2	Column Id	Yes	Lookup field sequence ID
3	Record Type	Yes	Available record types are Currency, Data, Fetched, Price, “Valid From” and “Valid To”, Assert, Percent and Rate. However, the latter three are rarely used.
4	Lookup Field Searchable	No	Option: YES Represents this lookup field is used as key or part of combined key to search.
5	Lookup Field Unique Key	No	Option: YES Represents this lookup field is unique. No duplicate values are allowed for this value.
6	Currency Field	No	Use for lookup table synchronization from ERP system. Usage is described in detail Synchronizing Custom Table/View from ERP as Lookup Table
7	ERP Id	No	ERP ID of lookup field. Use for lookup table synchronization from ERP system.
8	Business Type	No	Represents the business type of which owns attribute. It helps to support lookup record data creation.
9	Atttribute	No	Represents the attribute name to search. Generally, the attribute that represents the last part of search expression. It helps to support lookup record data creation.
10	Search Expression	No	Represents expression how the value is built as key to retrieve data from lookup table. Correct expression is important. Lookup field searchable and search expression are pair data points.

 

Synchronizing Custom Table/View from ERP as Lookup Table

CPQ can integrate with SAP ERP system. The data maintained in SAP ERP custom tables or view can be synchronized into lookup table and can make it use by the IPE. In this case, data is maintained in ERP system, but pricing and calculation is done in CPQ system. The ERP ID of lookup table and lookup field represents the ERP table and ERP table fields respectively in SAP ERP table field.

Usually there is one to one mapping not only for ERP table and lookup table but also for ERP field and lookup field. The mapping is done via ERP ID. The following figure shows how to set up the lookup table to make it synchronize.

As you see in the above figure, one lookup field is mapped to one ERP field. Linked currency is mandatory for price lookup field in data synchronization.

Pricing Scheme

Pricing scheme is a predefined arrangement or setup of how the calculation of quote pricing is done in the CPQ system. It is composed of multiple pricing steps. Each pricing step generally yields the part of sales item net price. Summation of all the sales item net price usually gives document header price or quote price.

IPE produces sales item net price, sales item net value and document header net value which is quote price. All the events that can affect the quote price such as adding a new line item, changing sales item quantity, deletion of sales item or changing of price date automatically triggers internal pricing. Besides automatic triggering, users can click the internal pricing button to explicitly make pricing.

At one point of time, only one pricing scheme must be activated. Till 1902r, pricing scheme determination logic is not supported. To make it clear, only inactive price scheme is editable. Deactivation and activation and of price scheme is required as administrator to make changes.

Pricing Step

The following data points are mandatory for pricing step creation.

No	Data Point	Mandatory	How It Works
1	Price Item Type	Yes	Represents price item type and its associated category determines how the pricing step will be calculated
2	Pricing Step Name	Yes	The name of pricing step
3	Sequence Id	Yes	Sequence ID of pricing step. No duplicate is allowed
4	Percent On	Yes	Enables only when the price item type is percent value
5	Arithmetic Expression	No	Enables only when the price item type is subtotal
6	Search Expression	No	Enables only when the price item type is fetched
7	Record Type	No	NA, placeholder for future scalability

 

Price Item Type Category

Price item type category of price item type determines how the pricing step will be calculated to get the sales item amount. From pricing scheme point of view, the data points required to maintain for each individual pricing step will be different according to the price item type category.

There are seven price item type categories:

1. Lookup table category
2. Fetch category
3. Calculated category
4. Manual category
5. Subtotal category
6. Aggregation category (also known as Aggregation of specific line item of child)
7. Script category

The detail description of each of the categories together with the pricing will be explained below.

Lookup Category

Use Case

The pricing data (amount with currency and validity) is maintained in a table. This data is retrieved by a unique key or a combination of unique keys such as (product id only or product id together with sales organization). CPQ gives the flexibility to define such a lookup table with fields dynamically, ability to maintain pricing data and usage in IPE.

Example

Maintain Product List Price in a lookup table named “Product List Price”. This list price is retrieved by product ERP ID.

Create Pricing Step

Create a pricing step with price item type whose category is lookup table. Any name and sequence id can be given to the pricing step.

No	Data Point	Mandatory	How It Works
1	Sequence ID	Yes	Determines order of pricing step in execution. No duplicate is allowed
2	Name	Yes	Name of Pricing Step
3	Price Item Type	Yes	In this case, it is Lookup table category
4	Percent On	No	N/A
5	Arithmetic Expression	No	N/A
6	Search Expression	No	N/A
7	Groovy Script	No	Applicable only when price item type category is groovy category
8	Routing Relevant	No	Determines whether pricing step is a place holder for material routing. If it is routing relevant, the purpose of using this pricing step is for ERP routing creation and the output of the price item type will be excluded in sales item amount
9	Mandatory	No	Determines whether pricing step is mandatory or not. If it is mandatory, notification will be shown if pricing step does not have amount
10	Disabled	No	Determines whether pricing step is disabled
11	Applicable Product Type	No	Determines the product type applicable to the product. If blank, the pricing step is applicable to all product types

Apart from price item type, the description of rest of price item type category 
is the same.

Add Lookup Table Sequence

Pricing step with lookup up category must have at least one lookup table sequence. If more than one lookup table sequences are existed, pricing scheme will follow the sequence to get price data. The sequence is determined the order that it usually goes from most specific to generic ones.

The following figure shows the example pricing step with two lookup table sequences. Sequence ID  1 is used for lookup table searched with the combination of product ID and account ID. Sequence ID 2 is used for lookup table searched with product ID only.

Add Lookup Table and Field

Each lookup table sequence must have exactly one lookup table and one lookup field. Prior creation of lookup table and fields is required in order to select lookup table and its related field from dropdown. Here, it is only mentioned about the lookup table usage and how it will retrieve. It defines only lookup table and lookup table field. Under which condition or criteria or how the lookup record is picked up is defined in lookup table. The following figures shows steps by step creation of lookup table and lookup fields and how-to entry data to lookup table.

Once lookup table is created and set up fully, check the schema before adding lookup record addition. Once confirmed, activate lookup table to add new records.

How It Works

The product price maintained must be picked up according to product id and validity date with respective to quote pricing date. If lookup record currency is different from sales item currency, the exchange rate conversion is applied.

The unit price for Product 803 is maintained as 150 SGD and is valid from Feb 7, 2019 to Dec 31, 9999. Since quote currency is in EUR, exchange rate (0.656173 from SGD to EUR) must be applied to lookup record value.

150 SGD = 150 * 0.656173 = 98.42595 EUR per quantity
Since quantity = 2, 98.42595 * 2 = 196.95 EUR for total price

System administrator can define exchange rate with validity in master data management screen. Below figure shows how the exchange rate for SGD to EUR is defined.

Fetched Category

Use Case

Fetched Category is used when price calculation consists of fetching data/ retrieving from quote or configuration of sales item. Necessary data points used for the pricing step are maintained within the quote.

Example

Shipping cost is maintained as configuration of sales item. That shipping cost is added in sales item net price.

Sales representative configures sales Item by adding shipping fees in this example, the amount retrieved is part of sales item price.

As of Sales Platform Version 1902, fetched category can only retrieve 
configuration values. 
The fetched value + The quote currency will be used fetched price. 
In the other way, quote currency becomes configuration currency by default. 
If the system administrator wants to use currency from dynamic attribute 
(also known as DA currency), calculated category is the right choice.

Create Pricing Step

Create a pricing step with price item type “Additional Price” whose price item type category is FETCHED.  Give the search expression “SalesItem().getDAValue('Shipping_Cost')[0]”. In this case, Shipping_Cost is the dynamic attribute name.

How It Works

Let check on a Quote with two sales items. The main difference here is the first sales item does not have fetched price, but the second one does.

Calculated Category

Use Case

Calculated category is the combination of FETCHED and LOOKUP. In addition, it has the functionality of calculation on the top of these two price item type categories.

Example

Price is the multiplication of hourly rate and the hours need to build the sales item (maintained as a configuration of sales item.)

Create Pricing Step

Each of calculated steps has exactly one pricing component. Pricing step component defines how the price will be calculated and which currency will be used for pricing step. Since unit of measure is optional and does not have specific impact on IPE, administrator can leave it as default. However, currency expression does influence the result currency of price item for calculated step. Usually, currency expression is one of the three expressions:

• Quote().hasCurrency[0] for quote currency
• SalesItem().hasCurrency[0] for sales item currency
• SalesItem().getDACurrency('DAName')[0] for dynamic attribute currency whereas DAName is the dynamic attribute name.

Arithmetic expression is the place that we define how the price will be retrieved or calculated. Usage of arithmetic operators such as addition (+), subtraction (-), multiplication (*) and division (/) are supported in it.

The type of pricing step component are

1. Fetched
2. Lookup
3. Delta, which will be explained in Delta Pricing Component 
4. Stepped which will use previous pricing step(s) of the sales item.

The following figure shows the creation of pricing step component with Stepped. The expression is mandatory field in step-based component. It must start with PricingStep followed by dot(.) and followed by pricing step sequence id that the administrator wants to use.

Sample expression, PricingStep.X where X is the sequence id of pricing step:

How It Works

In the below example, set up cost is calculated from the multiplication of Rate (which is from lookup table) and Hrs (which is from sales item configuration). The setup is already converted in Section 2.5.3.1.

Calculation step uses currency from the calculated component expression. However, till in 1902r, it does not consider currency conversion if variables inside pricing component have different currencies. It assumes all the variables has same currency. It only calculates the value with currency from calculated component expression.

How to define the calculated pricing step with default record key

In lookup based on calculated pricing step, instead of using the standard way of fetching lookup table data using the search expression of the lookup searchable fields defined in selected lookup table, user can also specify the simple condition to find the desired record for the lookup based calculated component. The followings are the steps to define the lookup based calculated component using the default lookup record key concept.

Define the new table, called LOGISTIC_TYPE for example as follow having the LOGISTIC_TYPE as searchable field and unique key and LOGISTIC_COST to maintain the cost for the selected LOGISTIC_TYPE.

After selecting the table, you can now enter the default lookup record key syntax as shown below based on the data inside the LOGISTIC table and field of your interest.  The syntax is Table.[LOOKUP FIELD NAME]=[VALUE] where Table is the keyword to start the syntax followed by . and field name of the lookup field. The right side of the equation must be the value inside the table.

Adding the product, price engine will calculate the Logistic Cost by searchable the LOGISTIC table by LOGISTIC_TYPE with SHIP_TO_US and create price item called Logistic Cost as shown below.

Manual Category

Use Case

Sales representative gives discount or mark up (either percentage or cash amount) to a sales item with affected pricing date (also known as “Valid From Date” of price item). For example, seasonal discounts (such as Christmas discount) will be affected from target date (December) but before that, the discount is not applied yet. By this way, sales representative will be able to define discount at a predefined date.

Example

Sales representative gives 100 SGD discount to one of the sales items.

Create Pricing Step

Below figure show creation of pricing step. The price item type category is manual in this case.

How It Works

After discount is given, the discount amount is subtracted from the sales item net price and net value.

Subtotal Category

Use Case

Subtotal is the intermediate step to show meaningful pricing information in the user. The output price from subtotal category is neither part of sales item net price nor quote price.

Example

Sales representative wants to see sum of the product price and shipping cost in user interface as total cost.

Create Pricing Step

Below figure show creation of pricing step. The price item type category is subtotal in this case.

How It Works

Assume that shipping cost is retrieved by Pricing Step 2 and set up cost is retrieved by pricing step 3. In this example, arithmetic expression of subtotal step is “PricingStep.2 + PricingStep.3”, the sum of the result of pricing step 2 and pricing step 3 is shown in pricing step 5. However, the sales item net price does not include that subtotal price.

Aggregation Category

Use Case

Consider there is a sales item hierarchy. Administrator wants to find a specific pricing step of all sub sales items and sum up to get the output of aggregation category. The output price from aggregation category is not part of quote price.

Example

Breaker is composed of multiple components. Each of the components has its own set up cost. Sales representative wants to see sum of all the set-up cost of breaker’s components.

Create Pricing Step

Arithmetic expression is mandatory for aggregation category. It decides which pricing step that administrator wants to sum up. The syntax of arithmetic expression is “PricingStep” followed by dot(.), followed by pricing step sequence id. In the following example, pricing step 3 will be used to aggregate.

How It Works

The following figure show the sales item hierarchy. Each of sub line item has its own set up cost. In order to build parent line item “Assembly”, the total set up cost is the sum of its children’s set up cost.

Script Category

Script category is introduced in 1902 release. The purpose of script category is to make custom implementation with the use of groovy language.

Create Pricing Step

This will show the groovy script to be used for this price step. If administrator opens the groovy script drop down, it will show all the groovy script maintained in CPQ system which are intended for IPE usage. That means administrator can pre-write and publish the groovy scripts which can be used in the pricing step. The place to maintain this is explained in figure above

Administrator can maintain all the groovy scripts used for IPE in master data management screen with the type of ”Internal Pricing – Custom Item Calculation”. Only the groovy script used for that type will be shown in pricing Step groovy script selection. Below is the groovy script from master data management screen.

How It Works

The output from custom implementation is price item base amount. On the top of that, the rest of price item type additional information such as Mandatory, Disabled, Applicable Product Types are still applicable and handled by the IPE.

Custom Implementation

From 1902 onwards, two extension points are ready for custom implementation of IPE. 

1. Custom Implementation for a pricing step which is shown in detailed for (already covered in Script Category)
2. Custom Implementation for sales item net price, net value and quote price calculation.

Delta Pricing

Delta pricing is also known as price change. The changes about price over time are saved into one lookup table and can be used as tracking purpose.

In February, the offer price is 2 SGD. But in April, the offer price becomes 1 SGD (2 -1). In December, the cumulative offer price is 8 SGD.

In order to save delta price in look up table, administrator must indicate the table as delta price table by enabling “delta price” at the time of lookup table creation

The below picture shows the default fields created by the options that administrator selected.

The lookup fields “PRICE_ITEM_TYPE” and “DELTA_PRICE” are added as delta price table selection and “validFrom” and “validTo” are added as has validity option.

The price item type needs to indicate itself as “is Delta” if it is used for price change. The following picture shows creation of delta price item.

In order to create delta price item, the setting “Enables Delta Price Item Creation” must be on.

Once the quote is in accepted state (everything is confirmed and got approval from superiors if existed any), administrator can save delta price in order to be used by next quote.

Once delta price is created, it is saved to delta price table. However, it can be checked in two places, delta lookup table and product pricing. The below screen is the individual product page. In Product Pricing tab, administrator can see the different delta prices with validity date.

 

Delta Pricing in Pricing Scheme

In order to use delta pricing, administrator must use price item type of Calculated Category. The key difference is the component type is “Delta” and must choose lookup table and price item type to calculate the cumulative price.

Example

In this example, the added fee has two different values with different validity date. 10 EUR from Feb 12 and -7 EUR from Mar 1.

The below picture shows the quote pricing date is 25 Feb. So only one record is applicable for delta price resulting in the cumulative added fee of 10 Euro.

The below picture shows the quote pricing date is 11 Apr. Now two records are applicable for delta price resulting in the cumulative added fee of 3 Euro. Hence pricing date has the effect on delta price.