The system administration guide contains key information to setup pricing in Manufacturing Sales Platform (MSP) CPQ Internal Pricing Guide effectively. The guide provides the concepts and detailed definition of the functions, aiming to enable administrator to have sufficient knowledge to setup the function with minimal supervision.
The document will be continually edited and updated based on latest product releases. Taking into the consideration of expensive effort keeping the document updated and effective usage of it, only major and complex features should be covered.
To better illustrate how to setup certain feature, some examples are given. It also contains the steps linked to relevant screens. Each step contains actions which the administrator needs to perform to complete the setup.
This administration guide is based on Sales Platform release version 1902.
The Product Engineering Team
2 CPQ Internal Pricing Guide Introduction
CPQ Internal Pricing Guide enables administrator to maintain data, configure major pricing steps and pricing scheme and sum up sales item price and quote price.
CPQ has available 3 pricing categories, these are:
- Internal pricing is based on the pricing scheme and pricing steps maintained in CPQ system.
- External pricing is done with the help of pricing simulation in ERP system. All the price related information such as pricing procedures, condition tables, condition types, access sequences is maintained in the ERP system. It involves a BAPI (Business Application Programming Interface) call with parameters maintained in CPQ. BAPI returns the pricing of each of the sales Items and whole quotation together with breakdown of each sales item pricing. (For the moment, we skipped this portion)
Basic Pricing based on SPARQL (Deprecated)
- Based on the user model, the pricing calculation methods are written in SPARQL and is maintained in CPQ system. This is not supported after 1711 release
Determination for pricing categories is handled by the setting in CPQ. To enable internal pricing, setting “Enable Internal Pricing” must be turn on. In this document, we will focus on “Internal Pricing Engine (IPE)”.
3 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.
3.1 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 2.5.|
|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||Determines whether the price item type is delta price item or not. Detail explanation about delta pricing is described in section 2.7 Delta Pricing.|
3.2 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 2.7 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||YES, represents this lookup field is used as key or part of combined key to search.|
|5||Lookup Field Unique Key||No||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 2.2.1.|
|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||Attribute||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.|
3.2.1 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.
3.3 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.
3.4 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, place holder for future scalability|
3.5 Reordering Questions
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 six 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 in the following from Section Lookup Category to Section Script Category below.
3.5.1 Lookup Category
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.
Maintain Product List Price in a lookup table named “Product List Price”. This list price is retrieved by product ERP ID.
188.8.131.52 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.|
|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.
184.108.40.206 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.
220.127.116.11 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.
18.104.22.168 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.
3.5.2 Fetched Category
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.
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), calcualted category is the right choice.
22.214.171.124 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’)”. In this case, Shipping_Cost is the dynamic attribute name.
126.96.36.199 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.
3.5.3 Calculated Category
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.
Price is the multiplication of hourly rate and the hours need to build the sales item (maintained as a configuration of sales item.)
188.8.131.52 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 for quote currency
- SalesItem().hasCurrency for sales item currency
- SalesItem().getDACurrency(‘DAName’) 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
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:
184.108.40.206 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 Create Pricing Step.
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.
3.5.4 Manual Category
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.
Sales representative gives 100 SGD discount to one of the sales items.
220.127.116.11 Create Pricing Step
Below figure show creation of pricing step. The price item type category is manual in this case.
18.104.22.168 How It Works
After discount is given, the discount amount is subtracted from the sales item net price and net value.
3.5.5 Subtotal Category
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.
Sales representative wants to see sum of the product price and shipping cost in user interface as total cost.
22.214.171.124 Create Pricing Step
Below figure show creation of pricing step. The price item type category is subtotal in this case.
126.96.36.199 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.
3.5.6 Aggregation Category
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.
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.
188.8.131.52 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.
184.108.40.206 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.
3.5.7 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.
220.127.116.11 Script Category
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 59.
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.
18.104.22.168 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.
3.6 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.
3.7 Editing Answer data
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.
|Price Item Type||No||Started From|
|Offer Price||2 SGD||1 Jan 2018|
|Offer Price||-1 SGD||1 Mar 2018|
|Offer Price||3 SGD||1 Jun 2018|
|Offer Price||4 SGD||1 Aug 2018|
In February, the offer price is 2 SGD. But in April, the offer price becomes 1 SGD (2 -1). In December, the cumulated 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 optiion.
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.
3.7.1 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.
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.
4 Audit and Debug
Audit is essential to figure out the situation such as administrator suspects that price is not coming out correctly or price calculation is wrong or incomplete set up of pricing scheme. In order to do that, setting “Enable audit of Internal Price Engine” must be turn on from the administration master data management screen.
Audit file can be found under Content Management -> audit -> [today date]-> [quoteId] ->Internal_Pricing. The output audit file is html format.