Internal Pricing Configuration (Excel)

• The currency expression will be shown as drop-down list and generally, it will contain the following two expressions.
  • Quote().hasCurrency[0]
  • SalesItem().hasCurrency[0]
• If there is a Dynamic Data Attribute with Data Type Currency, it will also show in the drop-down list.

• UoM EXPRESSION
  • Currently, there will be only one expression for that expression that user can choose.
    • SalesItem().isProduct.hasUnitofMeasure[0]
  • ARITHMETIC EXPRESSION
    • It is a kind of formula that following the JEXL expression syntax and it can contain variable/component name which can be defined late in details part.

The pricing/costing model can be defined in the excel for an existing pricing scheme or new pricing scheme. The excel can support to define the pricing step with the different Price Item Type categories as mentioned in Price Item Category using the following four main sheets.

• PRICING_SCHEME
• APPLICABILITY
• LOOKUP_SEQUENCE
• CALCULATION

It can also be used to define the following master data which is required by each Pricing Step depending on the Price Item Type Category of the Pricing Step.

• GROOVY_SCRIPT
• PRICE_ITEM_TYPE
• PRODUCT_TYPE
• LOOKUP_TABLE

Things to Note About : Even though the master data related with pricing scheme can be defined in excel, it is recommended to define the master data needed by Pricing Scheme in advance in order to reduce the additional efforts during the pricing/costing modelling. Example, it is recommended to define the lookup table to use in pricing step in advance in order to show the list of table name and field name in PRICING_SCHEME, LOOKUP_SEQUENCE and CALCULATION sheet. 

Define New Pricing Scheme

In order to define new pricing scheme in excel, user can click the download icon without selecting any pricing scheme to obtain the empty workbook template.

Things to Note About : When you download empty template and defined the new pricing scheme with a name that was already found in system, it will also create new Pricing Scheme with that name. Therefore, it is recommended to give a unique name for pricing scheme. The pricing scheme will be considered as new if there is no value found in hidden ID column for the defined pricing scheme.

Update an Existing Pricing Scheme

In order to download an existing pricing scheme,

• Select a pricing scheme to download
• Click the download icon

GROOVY_SCRIPT Sheet

This sheet lists all the Groovy Script objects which are having the PricingGroovyScript type. User can add new Groovy Script object inside the excel sheet and use it in PRICING_SCHEME sheet for the pricing step having the Price Item Type with Script type. However, it is not allowed to update an existing Groovy Script as well as define the content of the Groovy Script. The sheet is only needed when there is a need to calculate the cost/price using the Groovy Script. In order to define the new Groovy Based Pricing Step.

Step 1: Add new Groovy Script name if the name does not exist in Sheet

Step 2: Define the Price Item Type with Category 

Step 3: Add Pricing Step with Price Item Type defined in Step 2

Step 4: Upload the excel

Step 5: Upload the Groovy script

Once pricing scheme having the new pricing step that is using the new Groovy Script based Price Item Type had been uploaded successfully, it is needed to upload the actual groovy script that contains the actual implementation of how to calculate the price/cost.

• Go to Price Engine Master Data tab
• Select Groovy Script to list all groovy scripts that is relevant for Pricing
• Select the Groovy Script that was used in Pricing Step to upload the Groovy Script File

Things to Note About: It is necessary to assign the Lookup Table if the Groovy Script implementation is using the Lookup Table.

PRODUCT_TYPE Sheet

In each pricing step, Digital Sales Platform can allow user to assign the applicable product type so that pricing step will be executed based on current sales item’s product’s product type. The sheet will list all the existing product type available in the system. User can add new product type or modify the name of the existing one.

Step 1: Add new Pricing Step with Applicable Product

Go to APPLICABILITY sheet if the Product Type was already found inside the PRODUCT_TYPE sheet. Otherwise, define the new Product Type in PRODUCT_TYPE sheet.

Step 2: Assigning the Applicable Product Type to Pricing Step

Select the Product Type for the pricing step to apply

Things to Note About : It is important not to copy the row entirely as there is a hidden ID column but in order to copy the properties of existing row to create as a new object, select all the visible cells in a row itself, copy and paste it to empty row.

PRICE_ITEM_TYPE Sheet

The PRICE_ITEM_TYPE sheet will list all the Price Item Type available in the system. It is allowed to change the Price Item Type Category of an existing Price Item Type. However, it is needed to make sure that the valid new properties of the pricing step must be redefined. For example, initially the price item type is having Lookup based category and then changed to Calculation based, then it needs to define the calculation formula in CALCULATION sheet.

Step 1: Adding new Price Item Type

• Enter the new Price Item Type name
  • It is important not to copy the entire row if you want to copy an existing row with different name. Instead it is needed to select from column B to K to copy an existing Price Item Type property as new Price Item Type with different name.
• Enter/Select the properties of Price Item Type

Step 2: Adding new Pricing Step with newly created Price Item Type

• Select to PRICING_SCHEME sheet
• Add new Pricing Step with Step Position and Step Name
• Choose the Price Item Type from Step 1
  • Based on the Price Item Type Category of a selected Price Item Type, it is needed to define the related properties.
  • If the Price Item Type’s category is Lookup based, it is needed to define the Lookup Table Sequence properties inside the LOOKUP_SEQUENCE sheet. (See also LOOKUP_TABLE Sheet)
  • If the Price Item Type’s category is Calculation based, it is needed to define the calculation formula and its calculated component inside CALCULATION sheet. (See also CALCULATION Sheet)

Things to Note About : It is important not to copy the row entirely as there is a hidden ID column but in order to copy the properties of existing row to create as a new object, select all the visible cells in a row itself, copy and paste it to empty row.

If the Price Item Type was used in other Pricing Scheme, it is not recommended to change the properties of Price Item Type such as Category unless user is sure about the impact of those changes to other Pricing Scheme that is using the same Price Item Type.

LOOKUP_TABLE Sheet

The LOOKUP_TABLE sheet will list all the lookup table in the system and can be used to define new Lookup Table if the table has not been defined inside the system.

• Go the the last row inside the sheet, LOOKUP_TABLE to define new lookup table
• Define the properties of each lookup field
• Define the FIELD SEARCH EXPRESSION if RECORD TYPE is selected as FETCH.

The system will create the new Lookup Table after confirmation of excel upload as shown below.

Things to Note About : A new lookup table can be defined inside the sheet. However, modification of an existing lookup table will not be allowed in excel sheet. It is also necessary to add new lookup table column in _LOOKUP sheet in order to show the lookup table name and field name in LOOKUP_SEQUENCE sheet.

PRICING_SCHEME Sheet

The PRICING_SCHEME sheet is the main sheet for the pricing steps to be defined where the basic and mandatory properties of the pricing step can be specified. To define the pricing step, the following are mandatory to be filled.

• STEP POSITION
• STEP NAME
• STEP PRICE ITEM TYPE

Based on the category of the assigned Price Item Type, the excel will highlight the cell to accept the input. For Price Item Type with Price Item Type Category, Lookup and Calculation will be required to complete the pricing step definition in other sheets, LOOKUP_SEQUENCE ( See Also LOOKUP_SEQUENCE Sheet) and CALCULATION ( See also CALCULATION Sheet)sheets.

Things to Note About : Copying the entire row in excel is not allowed. However, if you wanted to copy the properties of existing pricing step, it is recommended to do the following.

• Copy the cell from column B to M
• Paste it into empty row
• Change the STEP PRICE ITEM TYPE as having the same price item type is not allowed.
• Cut the row and paste it to row that you wanted to put
• Reorder the STEP Position

APPLICABILITY Sheet

This sheet is optional and is only needed when some steps are to be applied to certain product type. PRICING SCHEME NAME

• STEP POSITION
• PRODUCT TYPE (where the number of columns will be dynamic depending on how many product types are defined in the system.)

LOOKUP_SEQUENCE Sheet

The LOOKUP_SEQUENCE sheet is needed when Pricing Scheme contains Pricing Step with Price Item Type the category of which is having Lookup type. The following cells are mandatory to be filled for each step.

• PRICING SCHEME NAME
• STEP POSITION (Based on Pricing Scheme Name cell’s value, the STEP Position cell will be enabled.)
• LOOKUP SEQUENCE ID (Pricing Step with Lookup based category needs to have at least one sequence id)

CALCULATION Sheet

The CALCULATION sheet is needed when the Pricing Scheme contains Pricing Step having STEP CATEGORY, Calculated and will list all the Pricing Steps which are having STEP CATEGORY, Calculated.

The definition of calculated Pricing Step needs to define in two parts, Header Information and Details Information.

Header information contains the following mandatory columns

Details information is to define how to obtain the numeric value for each Component Variable used in the ARITHMETIC EXPRESSION. Based on the COMPONENT TYPE, the columns will be highlighted to let user to choose/enter the data.