Oracle Pricing Cloud — Formula Management
In EBS, we had the Formula based pricing, wherin on top of base price you can apply formula, basically do arithemtic operations based on others factors and calculate the base price.
This functionality is extensively used in Furniture industries that produce Office/Kitchen Cabinet etc., The pricing of the final product is take into considerations of multiple factors for eg.,Fabric that was used, Cabinet dimensions etc.,
We will look at a example of this pricing setup in EBS.
You can see the final price is arrived by combining List Price, Factors and Numeric constant.
In Oracle Pricing Cloud, we can leverage Algorithm and modify to get the objective. But the main thing to be answered is how these are managed, since you can end up creating 100’s of formula, so you need a method and process to manage these. This article is going to cover the solution approach and design.
(1) Setup Charge level DFF
1. Navigate to ‘Setup and Maintenance’.
2. Search for Task ‘Manage Pricing Descriptive Flexfields’ Functional Setup Manager (FSM) task
3. In the Search results, click on the task link ‘Manage Pricing Descriptive Flexfields’
In the ‘Manage Pricing Descriptive Flexfields’ page, search for the DFF with Name = ‘Price List Charge DFF’
Edit and add new Segment as follows.
Deploy the flexfield.
Once the deployment is complete, Actions -> Download Flexfield Archive, once it completes, click the Download button. This downloads the Flexfield Archive zip file.
Open the zip file, move to ‘oracle\apps\flex\scm\pricing\priceExecution\pricingProcesses\priceListCharges\view’, open the View Object Definition with the name PriceListChargeDFFVO.xml. Open this in IE or Notepad ++ and lookfor <ViewAttribute Name=”Priceformula” IsUpdateable=”false” EntityAttrName=”AttributeCharX” EntityUsage=”FlexfieldDataEO”>
Also, open below in the DFF Archive:
oracle\apps\flex\scm\pricing\priceExecution\pricingProcesses\priceListCharges\applicationModule\PriceListChargeDFFAM.xml
Look for the View Usage below”
<ViewUsage Name=”DefaultFlexViewUsage” ViewObjectName=”oracle.apps.flex.scm.pricing.priceExecution.pricingProcesses.priceListCharges.view.PriceListChargeDFFVO”/>
Note down the View Object Name and View Attribute Name, we will use in the Customize “Get Base List Price for Goods and Services” Algorithm step.
(2) Define Custom Matrix Class
Pricing Administration > Manage Matrix Classes
Here we are defining a Custom Matrix to hold Pricing Factors which will be used during Base Price calculation. In this example, our scenario is to calculate Base Price based on Item EFF’s.
Setup and Maintenance > Order Management > Pricing > Manage Pricing Matrix Types
(3) Define Custom Algorithm
This Algorithm will house all the Pricing Formula that needs to be defined.
a) Stay logged in as Pricing User, Manage Algorithms,
b) Actions > Create, provide name for the Algorithm
c) Click on the Variables tab and Add the below
Enter the following details
d) Add step to call “Get Item Extensible Attributes” Algorithm as below
e) Add step to execute the Formula as per below
Save and Publish.
You will notice the MatrixId: 300000366694837 is hardcoded, how we got this will be covered in the last step, this will be unique for an implementation. Ideally you can store this Lookup and call the Lookup PVO to retrieve this, instead of hardcoding.
In the above step, we are adjusting the Base Price with Constant value and multiplying with a factor (Matrix.Adjustment). This Adjustment is coming from the Custom Matrix Class that defined in the prior step.
So you keep adding all the list of formula in this algorithm.
This Algorithm will be calculated with Input Variable “Step Name”, so each custom formula step should have a condition to check if the current step to be executed based on the Input Variable.
Formula steps will call the Matrices at runtime to evaluate the Dimensions and rules available for the Custom Matrix Class and derive the necessary factors/adjustment. You can build logic based on it.
(4) Customize “Get Base List Price for Goods and Services”
High level design/approach:
a) Stay logged in as Pricing User, Manage Algorithms,
b) Search using ‘Query By Example’ (QBE) for Algorithm ‘Get Base List Price for Goods and Services’, click Actions -> Create Version. Wait for a new Version to get created.
c) Click on the Functions tab and create a new function.
Enter the following details
d) Click on ‘+’ under Arguments tab and add an Argument with name PriceListChargeId
e) Click on ‘View Object Query’ tab, Click on ‘+’ and add the following details.
f) Click ‘+’ under ‘Bind Variables’ and add a new Bind Variable with the following details.
g) Click on the Variables tab and Add the below.
Enter the following details
Save.
Add Algorithm steps:
a) Create the Step “XX Formula Pricing Custom” of Type = Block, just before the “Create Charge Components”
b) Add Step > Conditional Action just below the above step — “XX Formula Pricing Custom”
c) Add Step of Type “if”, with Condition as “Formula != null” as per below
d) Add Step of Type “Run Algorithm” to call “XX Pricing Formula Custom” with Input and Output variables as below
Save and Publish.
(5) Maintain Formula name at Charge level DFF. Remember it’s nothing but the step names for different formulas under Algorithm “XX Pricing Formula Custom” and your associating with step to be executed during the runtime.
(6) Create Matrix Dimensions and Rules for Custom Matrix Class using REST API’s
Remember we are using Custom Matrix Class and there’s no front end UI, Out of the box provided by Oracle. Hence you will need to use REST API’s to cretae dimensions and rules.
API call to Create Matrix along with Dimensions and Rules
https://<pod>/fscmRestApi/resources/11.13.18.05/pricingMatrixes
Action: Post
Payload:
{
“MatrixName” : “FormulaMatrixName”,
“MatrixClassName” : “XX Formula Matrix”,
“dimensions”: [
{“DimensionName”: “Config”},
{“DimensionName”: “Segment”},
{“DimensionName”: “Adjustment”}
],
“rules”: [ { “Dimension1”: “Config”,
“Dimension1Value”: “BULK”,
“Dimension1KeyValue”: “BULK”,
“Dimension2”: “Segment”,
“Dimension2Value”: “MOBILE”,
“Dimension2KeyValue”: “MOBILE”,
“Dimension3”: “Adjustment”,
“Dimension3Value”: 10,
“Dimension3KeyValue”: 10}
]
}
Later, you can keep adding Rules as required with this API. Create Rules on existing Matrix
https://<pod>/fscmRestApi/resources/11.13.18.05/pricingMatrixes/<MatrixId>/child/rules
Action: Post
Payload:
{
“Dimension1” : “Config”,
“Dimension1Value” : “RTL”,
“Dimension1KeyValue”: “RTL”,
“Dimension2” : “Segment”,
“Dimension2Value” : “EXTERNAL”,
“Dimension2KeyValue”: “EXTERNAL”,
“Dimension3” : “Adjustment”,
“Dimension3Value” : 10,
“Dimension3KeyValue”: 10
}
You can write BI Report for displaying the matrix rules to business users.
