Formulas

This chapter covers the following topics:

• Overview of Formulas
• Creating a Pricing Formula
• Finding Formula Factor Lines
• Updating Formula Prices

Overview of Formulas

Formulas consist of mathematical expressions that the pricing engine evaluates to calculate the list prices of items and discounts applied to them. Formulas can be linked to a price list line or modifier line. You can use formulas to:

• Create a price from a computation as an alternative to entering prices in a price list.

• Calculate a price adjustment. For example, you can instruct the pricing engine to calculate a discount by attaching a formula to a discount line.

• Set up and maintain formulas based on component types.

When you attach a formula to a price list line, you typically do not enter a price for that line because the pricing engine uses the formula to calculate the final list price of the product or service. One exception is a formula that has a list price as Formula Type--for this type, you must enter the list price (base price) for the formula to use in its calculations.

Note: You can attach a formula that has a List Price component to price list lines and to modifier lines. For more information, see Oracle Advanced Pricing User's Guide, Price Lists and Modifiers chapters.

You can choose one of following methods to determine how a formula calculates the list price:

• Dynamic calculation: The list price resulting from the formula calculation is not calculated or stored anywhere until the sales order is entered with that price list line item. When the sales order is entered, the pricing engine evaluates the formula and displays the final list price on the order.

• Static calculation: If the formula is attached to a price list line for static calculation of the final list price, you can run a concurrent program at any time to calculate the final list price using the formula up front (not wait until order entry time) and also store it in the price list.

Seeded Formulas

Oracle Advanced Pricing provides seeded formulas that you can use when setting up freight charges. For more information, see the Oracle Advanced Pricing Implementation Guide.

Creating a Pricing Formula

You can set up and update formulas and formula lines in the Advanced Pricing - Pricing Formulas window. A formula is a valid mathematical expression that you can use to determine the list prices of items and the discounts applied to those items. The formula lines provide details about each part of the formula.

Note: The concurrent program Build Formula Package should be run if you create or change a formula expression.

A formula can contain any of the following:

• Parentheses: for example, (and)

• Mathematical operators: for example, +, -, /, and *

• Built-in functions: for example, NVL, SQRT, and MOD

• Operands: Operands are step numbers about which you provide more detail. You can use as many step numbers as you need, up to the limit of the field. You can repeat a step number in a formula, for example, 1+2*2.

  Note: An operand is not a numeric constant. If you want to use a numeric constant in a formula, you must create a step number in the formula expression and then assign the numeric constant to the step number in a formula line

For example, the valid formula (1+2*SQRT(3)) / 4 contains:

• Operands: 1, 2, 3, and 4

• Mathematical operators: +, *, and /

• Built-in function: SQRT

• Parentheses: to group the operands and operators

For each preceding step number, you need to create four formula lines since the formula has four step numbers.

Oracle Advanced Pricing uses the formula line and evaluates it to obtain the value of the operand and calculates a formula. It does not use the face value of the step number.

You assign one of the following types to each formula line:

• Function: The function GET_CUSTOM_PRICE retrieves a price from an external system and uses it in the formula calculation. You create the program logic to map to the external data source by developing PL/SQL program packages. The function GET_CUSTOM_PRICE returns a numeric value. For detailed information on setting up GET_CUSTOM_PRICE, see the Oracle Advanced Pricing Implementation Guide.

  Note: Advanced Pricing formulas supports all the functions supported by PL/SQL. For more information on PL/SQL functions, see the Oracle SQL reference guide.

• List Price: The price of the item in a specific price list to which you have attached a formula.

• Factor List: Enables you to define pricing conditions that you can link to multiple pricing attributes or a range of these attributes. The pricing engine evaluates the formula, and then chooses one of these factors, depending into which range the actual pricing attribute of the item falls.

  For example, a step in the formula may have different factors defined for different ranges of glass thickness: a glass with thickness between 0.1 and 0.3 mm has a factor of 3, and a glass with thickness between 0.4 and 0.8 mm has a factor of 5. The pricing engine determines which factor qualifies when it evaluates an order and applies this factor in the formula calculation.

  You can also relate multiple factor conditions. For example, if the base pricing attribute for glass thickness is between 0.1 and 0.3 mm AND the length of the glass is between 0.5 and 2 m, apply the factor of 3 OR if the base pricing attribute for glass thickness is between 0.4 and 0.8 mm AND the length of the glass is between 0.5 and 2 m, apply the factor of 5.

• Modifier Value: Uses the value entered in the Value field of the modifier line or price break line.

• Numeric Constant: A numeric value.

• Price List Line: The price of the item in a specific line of a specific price list.

• Pricing Attribute: The absolute value of a pricing attribute (such as thickness or height) of an item.

  Pricing attributes are characteristics of products and services that you can specify when the characteristics help to determine the price of a product or service. Distance, age of a related product, customer class, product family group, and level of service are examples of pricing attributes. You can specify one or a combination of pricing attributes and assign them to a product or service. At order entry time, the pricing engine evaluates the attributes you have specified during formula setup to calculate the price.

  You can define as many attributes as you need to meet your pricing business needs. For example, you may use the formula 1*2 to calculate the price of a glass item. Step 1 is a pricing attribute for thickness and step 2 is the list price to calculate the price of a glass item; if 100 is the base price of the glass item and 0.3 is the value of the thickness attribute of the glass, then the pricing engine evaluates the formula as 0.3*100, which is 30.

WARNING: Null Values in Formulas

During formula calculation, if a step results in a null value, the formula fails. For example, consider the formula 1*2, where step 2 is of type Pricing Attribute, pricing attribute context is Physical Attribute, pricing attribute is Volume, and the user must supply the volume at order entry time.

Since the user cannot provide a volume, use the NVL expression in the formula to refer to a step number that evaluates to a non-null value; for example, changing the formula to 1* NVL(2,3). In the formula line for step number 3, use a non-null value such as the numeric constant of 8. If the user does not provide a value for volume, the pricing engine uses 8 in the formula calculation.

If the expression does not use NVL in the expression, and the step number evaluates to NULL, the entire expression evaluates to NULL, the formula calculation fails, and the calling application processes the failure.

To create a pricing formula

1. Navigate to the Advanced Pricing - Pricing Formulas window.

   Advanced Pricing - Pricing Formulas window

   

2. Enter a Name and Description for the formula.

3. Enter the Effective Dates range.

4. Enter a Formula expression. A formula consists of step numbers (such as 1, 2, 3) used in an arithmetic equation such as 1*2.

   A step number such as 1 in the formula expression corresponds to the formula line with the same step number in the Formula Lines region.

   The Seeded box identifies if the selected formula is seeded.

5. Every time a new formula expression is created, the concurrent program Build Formula Package should be run to generate the formula package.

   From the Tools menu, select Build Formula Package to run the program. Alternately, you can also run the Build Formula Package concurrent program. A Dialog box displays a message if the formula package generation was successful.

6. Save your work.

   In the Formula Lines region, you create formula lines and assign a Step number that can be used in the Formula expression. Complete the following steps for each formula line to be created.

7. Select one of the following Formula Types: 

   • Function: The Component field displays GET_CUSTOM_PRICE. You must write the function in this PL/SQL package and it must return a number. For more information, see Oracle Advanced Pricing Implementation Guide.

   • List Price: The price of the item in a specific price list to which you have attached a formula.

   • Factor List: Select an existing factor list in Component field. Alternately, to create a new factor list, type a new name in the Component field, and press the Enter key. When you complete your entries for the formula line, click Factors to create the factor criteria in the Factors window.

   • Modifier Value: A formula that has a Modifier Value as a component cannot be attached to a price list line.

   • Numeric Constant: Enter the numeric constant in Component.

   • Price List Line: A formula that has List Price as a component and is attached to a price list line gets the list price from the Value entered in a price list line.

   • Pricing Attribute: Select the Pricing Attribute Context and Pricing Attribute.

8. Enter the Step number for the formula expression. Step numbers cannot be repeated in the Formula Lines region even though they can be repeated in the Formula expression.

   The Seeded box is selected for seeded formula lines.

   The Reqd Flag check box is currently not used by the pricing engine unless you are using custom code for tracking formula line information. If you are using custom code for this purpose, then select the Reqd Flag check box.

9. Save your work.

To define factor list details

1. If the Formula Type is Factor List, click Factors in the Advanced Pricing - Pricing Formulas window to display the Factors window.

   Factors window

   

2. In the Base Pricing Attributes region, complete steps 2-9 for each base pricing attribute factor. For information on searching for formula factor lines, see Finding Formula Factor Lines.

3. Select a value for Base Pricing Attribute Context.

   If you have multiple entries, you must use the same base pricing attribute context in this region (to create an OR condition); the pricing engine chooses one of the entries. Use the Associate Pricing Attributes region to associate additional contexts with the base context (to create an AND condition where both attributes must be present to qualify).

   Note: In the Base Pricing Attribute region, it is possible to enter duplicate values for any attribute such as entering the same Item Number of AS54888.

   If matching base pricing attributes exist, then their ranking order determines the adjustment factor selected. For example, if base pricing attributes PA 1 and PA 2 match, then the pricing engine selects the PA 1 adjustment factor because it is defined first.

4. Select a value for Base Pricing Attribute.

5. Select a comparison Operator and enter a Value From and a Value To:

   • If you select the operator BETWEEN, you must enter a Value From. If you do not enter a value for Value To, the default is unlimited.

   • If you select the operator =, you must enter Value From and you cannot enter Value To. Enter Value To only when the Operator is BETWEEN.

6. Enter the Adjustment Factor. An adjustment factor enables you to adjust the price based on the options selected. For example, if you are selling cars, you could use adjustment factors to adjust the price based on the car options the customer chooses:

   • If the car options are Standard, then the price is multiplied by 1 (factor=1).

   • If the car options are Business, then the price is multiplied by 1.5 (factor=1.5).

   • If the car options are Deluxe, then the price is multiplied by 2 (factor=2).

     If the cars you are selling are second hand, you probably want to adjust the price further based on the age of the car. For example:

   • If the car is between 0.0 and 1.0 years old, the price is multiplied by 1.

   • If the car is between 1.1 and 3.0 years old, the price is multiplied by 0.8.

   • If the car is older than 3.0 years, the price is multiplied by 0.6.

7. Enter a Start Date and an End Date range for this attribute in this factor list.

   Note: The effectivity of this pricing attribute in this factor list also applies in all other formulas that use this factor list.

8. Complete the following steps for each associated pricing attribute:

   1. In the Associate Pricing Attributes region, select an Associated Pricing Attribute Context and Associated Pricing Attribute to associate with the base pricing attribute context and base pricing attribute values (to create an AND condition).

   2. Select a comparison Operator and enter the Value From and Value To:

      • If you select the operator BETWEEN, you must enter a value for Value From. If you do not enter a value for Value To, the default is unlimited.

      • If you select =, you must enter Value From and you cannot enter Value To.

9. Save your work.

Finding Formula Factor Lines

A factor list can consist of hundreds of factor lines and associated base pricing attributes. If your business requirements require you to change the values of certain pricing attributes, it is time consuming to scroll through the entire factor list to find a required record. Instead, use the Find Formula Factor Lines window to selectively retrieve the records you want.

You can narrow your search by selecting search criteria that defines the factor line you want to retrieve. You can query by Pricing Context, Pricing Attribute, Query Operator, and Pricing Attribute Value.

The query returns all those factors in the factor list that have either a base or an associated pricing attribute that meets the query criteria. The results are sorted by the Pricing Attribute Value From of the Base Pricing Attribute of the factor lines that are returned by the query.

To find formula factor lines

1. Navigate to the Find Formula Factor Lines window.

   Find Formula Factor Lines window

   

2. Enter the Factor Pricing Attribute Context for the query, such as Item.

3. Enter the Factor Pricing Attribute, such as or Item Category or Customer Item.

4. Enter the Query Operator, such as = or Between. The = operator returns an exact match while the Between operator returns a range of values. If the Pricing Context on the Query Find window is Item and the Pricing Attribute is either Item Number or Item Category, then the Query Operator is restricted to the value =.

5. Enter a Pricing Attribute Value From High/Low to define the range of values you want queried; for example, to restrict a query to a range of item numbers from 23456 to 45674. However, if the = operator is selected, you can only enter a value for Pricing Attribute Value From Low.

6. Click Find to return the query results.

7. Alternately, to start a new query, click Clear to clear the current query criteria and enter your query criteria.

8. Click New to create a new Base Pricing Attribute.

Updating Formula Prices

Use this process to use static formulas to set list prices. When you attach a formula to a price list line, you can select from the following calculation methods:

• Dynamic calculation: When you enter an order, the pricing engine uses the formula to calculate the list price.

• Static calculation: Before order entry, you run a concurrent process that calculates the list price.

To update formula prices for static calculation

1. Navigate to the Update Formula Prices window.

   Update Rule Prices window

   

2. To calculate formula-based prices:

   • For price list lines whose formulas have been added or changed from when they were last updated by this concurrent program, select New and Modified Lines Only of Price Lists.

   • For all price list lines, clear New and Modified Lines Only of Price Lists.

3. To consider price list lines that have:

   • Any formula, select Update All Pricing Formulas for Price Lists.

   • A certain formula, select Update Individual Pricing Formula for Price Lists, and select the specific pricing formula.

     The process applies this choice after it selects the price list lines according to the criteria for Step 2.

4. Click Submit to display the Request ID.