This chapter covers the following topics:
Advanced Pricing supports both single currency price lists and multiple currency price lists. For single currency price lists, one currency is defined per price list. For multicurrency price lists, one base currency price list exists along with an attached currency conversion list defining conversion factors and rules for converting prices.
You need to determine which price list strategy your organization wants to support. Advanced Pricing provides a profile option and concurrent program to convert existing price lists from a Single Currency Price List to Multiple Currency price lists. However, once this profile is enabled, and price lists are converted, users should not return to non-multicurrency price lists. Changing the profile back to No may cause undesired results if conversion criteria were used. Oracle does not support changing the setting back to No.
Single currency price lists are the default setting for Advanced Pricing. These are used when your business requires that you maintain different prices for different currencies, and no relationship exists between prices that are defined.
Multiple Currency price lists enable businesses that have pricing strategies that are based on a single price for an item in a base currency and use exchange rates or formulas to convert that price into the ordering currency. At pricing engine run time, the pricing engine takes the currency from the order and search for a price list(s) with base or conversion currencies matching this currency. The price is converted from the base currency and calculates the ordering currency based upon the established conversion rules.
Multicurrency price lists use a specified base currency and the conversions for other currencies are applied to the values of the base currency price list. Additionally, multicurrency price lists allow some significant features such as markup conversions that can be applied to the values of the base price list without any changes to the list line values. This can significantly reduce the number of price lists that must be maintained and reduce data storage.
The Currency Conversion window is used to define the conversion criteria. Seeded conversion types include: fixed, formula, user defined, spot, EMU fixed, transaction and corporate.
Note: Some of these seeded conversion types require Oracle General Ledger to be installed. Users can still link to other non-Oracle stored conversion rate information by using the formula functionality
Attributes
You can define multiple conversion criteria based upon certain attributes for the same base currency. For further information about attributes, see chapters in the Advanced Pricing Implementation Guide, Attribute Management and Oracle Advanced Pricing User's Guide.
Markup Values and Formulas
You can define markup criteria per currency definition, including the base currency. This markup can be a fixed value of amount or percent, or based upon a formula. For further information on formulas, see: Oracle Advance Pricing User's Guide, Formulas.
Rounding
Base Round To: Rounds the selling price after applying the modifiers. In this field, you can enter a numeric-rounding factor that rounds the list price after the conversion and markup is applied.
Note: You cannot enter the Base Round To on the price list. Base Round To must be set in the currency conversion list as a Base Round To and will always default to the price list from the attached currency conversion list.
Conversion Rounding Factor: Rounds the To Currency list price after the conversion and markup is applied. This rounding factor could be different for the same To-Currency. This is entered in the Conversion Rounding Factor field in the currency conversion list.
Round To: Rounds the selling price after applying the modifiers. This rounding factor will always be the same for a Currency To.
The following example shows how a multicurrency conversion list calculates a price in U.S. dollars when an item is ordered in Canadian Dollars (CAD). The following multi-conversion list and base price list setups are used in the conversion scenarios:
| Header Level Field Name | Value |
|---|---|
| Name of multicurrency list | Test Multicurrency Conversion List |
| Currency (base currency) | USD |
| Base Round To | - 2 |
| Base Markup Value | % |
| Base Markup Value | 10 |
| Base Markup Formula | My Base Markup Formula with expression 1/2 (Step 1 divided by Step 2) where:
|
| Line Level Field Name | Value |
| To-currency | CAD |
| Conversion Type | FORMULA |
| Formula (conversion) | My Conversion Formula with expression 1*2 where:
|
| Markup Operator | AMT |
| Markup Value | 50.12345 |
| Conversion Rounding Factor | -3 |
| Round To | -2 |
The following table describes the setup for the base price list that is used in this example:
| Field Name | Value |
|---|---|
| Name | Base PL |
| Currency | USD |
| Item | Item A |
| List Price | 20 |
| Dynamic Formula | My Base Price List Line Formula with expression 1*2 where:
|
Scenario 1
If 1 unit of Item A is ordered in the currency Canadian Dollars (CAD), then the price is determined in the following way:
Price of Item A from base price list Base PL is calculated using the dynamic formula 1 * 2, where step 1 is List Price and step 2 is Numeric Constant. The list price on the base price list line for Item A is 20 and numeric constant is 30. So the list price now is 20 * 30 = 600.
Because the Item A is ordered in CAD (which is not the base currency USD), the Base Markup Value, Base Markup Operator, Base Markup Formula, and Base Round To are not used in this calculation. Instead, the information at the conversion line level is used:
The conversion type is FORMULA.
The formula that is used is My Conversion Formula with expression 1*2, where step 1 is of type Numeric Constant and value 25 and step 2 is of type List Price.
The List Price that is used is the result of step 1, which is 600.
So the new list price now is 25* 600 = 15,000.
Because a markup formula My Conversion Markup Formula exists with expression 1/2 + 3, where:
Step 1 is of type List Price.
Step 2 is numeric constant 5,000.
Step 3 is of type Modifier Value.
The markup is calculated as
15,000/5,000 + 50.12345
= 3 + 50.12345
= 53.12345
Note that the List Price is taken from Step 2 and that the Modifier Value is the Markup Value on the conversion line. The final markup value is 53.12345, and the markup operator is AMT.
So the new list price now is 15,000 + 53.12345 = 15053.12345.
If Item A is a non service item, then the conversion rounding factor - 3 is applied to the result of step 3, that is, after the markup, if any, has been applied to the post-conversion list price of step 2.
Note: This step is skipped for service items. So the new list price now is 15053.123
So the new list price now is 15053.123.
The conversion line Round To is applied to the result of step 4. This Round To is applicable to the list price, as well as to the net price but its application is subject to the value of the profile option QP: Selling Price Rounding Options.
If the profile option value is No, then the list price 15053.123, any adjustments, and the selling price are not rounded.
If the profile option value is Individual, then the list price, any adjustments, and the selling price are each individually rounded to two places after the decimal because the Round To on the conversion list is - 2.
So the new list price would be 15053.123 and is rounded to 15053.12.
Comments
Markup operator can apply to the Markup Value as well as to the Markup Formula. This is true for both Base Markup and Conversion Markup.
Markup Value and Markup Formula can both be present in the same list. In that case, the Markup Formula is used; the Markup Value may be used as the value for any Modifier Value type of Formula Line in the Markup Formula.
Modifiers is selected and applied only if either the currency on the modifier is null or the currency on the modifier matches the ordering currency.
If a Conversion Formula has a List Price formula component, the List Price from the Base Price List (after dynamic formula calculation on the base price list if applicable) is used for this formula component.
If a Markup Formula (at the conversion line level) has a List Price formula component, then the List Price after performing conversion is used for this formula component.
Conversion Rounding Factor is applied on the marked up value of list price. For example, apply conversion rounding to the marked up conversion list price.
Round To value on the conversion line is used to perform rounding of final list price, adjustments, and selling price subject to the QP: Selling Price Rounding Options.
Scenario 2
If 1 unit of Item A is ordered in the base currency USD, then the price is determined in the following way:
Price of Item A from base price list is calculated using the dynamic formula 1 * 2 where step 1 is of type List Price and step 2 is of type Numeric Constant. The list price on the base price list line for Item A is 20 and numeric constant is 30.
So the list price now is 20 * 30 = 600.
Because the Item A is ordered in the base currency USD, only the Base Markup Value, Base Markup Operator, Base Markup Formula and Base Round To are used in this calculation. Setup values at the conversion line level are not used.
Because there is a base markup formula My Base Markup Formula with expression 1/2 (step 1 divided by step 2), where step 1 is of type List Price and step 2 is of type Modifier Value, the base markup is calculated as 600 / 10 = 60 (where list price is taken from step I) and step 2 is the Base Markup Value. Because the Base Markup Operator is %, the new list price now is 600 + 60% of 600 = 600 + 360 = 960.
The Base Round To -2 is applied to the result of step 2. This Round To is applicable to the list price and the net price, and how it is applied is determined by the profile option QP: Selling Price Rounding Options:
If the profile option value is No, then the list price 960, any adjustments, and the selling price are not rounded.
If the profile option value is Individual, then the list price, any adjustments, and the selling price are each individually rounded to two places after the decimal. So in this case, the new list price is 960.00.
You can set the profile option QP: Multi-Currency Installed to control which type of price lists you will use:
No (default setting): Can create and maintain multiple price lists with each price list that is defined in its own base currency (non-multicurrency enabled).
Yes: Enables you to attach a multicurrency conversion list to each price list or agreement price list. Setting the value of the profile to Yes enables all Price List and Agreement windows with some window field and functionality changes. Once the profile is set, you must create at least one multiple currency conversion list. A currency conversion list is required when you are setting up a price list.
The following steps outline how to set up multiple currency price lists:
Navigate to System Administer responsibility >Profile > System> QP: Multi-Currency Installed > Select Yes.
Navigate to Oracle Pricing Manager responsibility >Price List Setup > Create a base currency master price list
Note: If single currency price lists were created prior to changing QP: Multi-Currency Installed to Yes, then you must run the concurrent program Update Price Lists with Multi-Currency Conversion Criteria. This converts all existing price lists and agreement forms as Multi-Currency and activates the Multi-Currency Conversion Setup form.
If you are already using single currency price lists and want to upgrade to multiple currency price lists and agreements, the following steps are required.
Step 1. Set profile option QP: multicurrency Installed
The profile QP: Multi-Currency Installed controls which type of price lists you will use. Set this profile to Yes. This will enable you to run the Concurrent Request program that updates all existing price lists and agreements to the multiple currency forms and functionality.
Step 2. Run concurrent request Update Price Lists with Multicurrency Conversion Criteria
To start using the multicurrency feature, run the concurrent program Update Price Lists with Multi-Currency Conversion Criteria only once. This program creates:
A currency conversion list for each combination of price list currency and rounding factor. This conversion will have the same Base Currency with the Base Round To from the price list.
Attach a currency conversion list to each price list and agreement.
This is a mandatory step because without this the pricing engine will not be able to use the current price lists as multicurrency price lists.
Example
Five price lists are set up as outlined in the following table:
| Currency | Round To |
|---|---|
| USD | -2 |
| USD | -2 |
| USD | -3 |
| FRF | -1 |
| CAD | NULL |
The concurrent program will create four Currency Conversion Lists:
| Currency | Base Round To |
|---|---|
| USD | -2 |
| USD | -3 |
| FRF | -1 |
| CAD | NULL |
After you run the concurrent program, all price lists and agreements will be multicurrency enabled. You can now add additional To Currencies and their conversions.
Prior to using multicurrency price lists, you need to consider the following guidelines:
Combining Price Lists
Before creating or merging price lists and their currency conversion lists you must determine whether you can use one single price list or whether you need to create more than one.
You can use a single base currency price list to replace price lists that are defined in various other currencies for conversions provided that all have the following identical attributes:
Items
Qualifiers
Pricing attributes for price list lines
The basis of the price list line values (of the single currency defined price lists) should equal the base currency of the single multicurrency price list to which you attach the conversion list. Otherwise you will need to define multiple conversion criteria based upon attributes for the same To-Currency.
Example of combining single currency price lists:
Qualifiers and Items are identical in the selected single currency price lists. All of the single currency price lists have list line values that are converted values based on the USD price list. Except for CAD, all the lists apply their conversion types, equally to all items except for the CAD defined price list. CAD Price List line values equal a Fixed conversion rate = 2, except Item C which is valued at a conversion rate of 1.5 as shown in the following table:
| Price List = USD | Price | Price List = CAD | Price | = Conversion Rate |
|---|---|---|---|---|
| All Items | -- | All Items | -- | 2 |
| Item A | 100 | Item A | 200 | 2 |
| Item B | 200 | Item B | 400 | 2 |
| Item C | 200 | Item C | 300 | 1.5 |
In the following table, the individual price lists for MXN, JPY, and Euro that previously were set up with the price list values equal to the conversion types have been transitioned in the setup. The CAD defined price list required an additional step to set up a conversion line for the item(s) that were not converted at the same conversion type of Fixed, with same Fixed Value.
Currency conversions will apply to the price list values on the base currency price list to which it is attached.
| Base Currency (USD) | Convers. Type | Fixed Value | Attribute Code | Attribute Value | Start Date | End Date | Pr.1 |
|---|---|---|---|---|---|---|---|
| To- Currency (CAD) |
Fixed | 2 | -- | -- | 01/01/02 | 112/31/02 | 2 |
| To- Currency (CAD) |
Fixed | 1.5 | Item Number | Item C | 01/01/02 | 12/31/02 | 1 |
| To- Currency (MXN) |
Corporate | -- | -- | -- | 01/01/02 | 12/31/02 | -- |
| To- Currency (JPY) |
Corporate | -- | -- | -- | 01/01/02 | 12/31/02 | -- |
| To- Currency (Euro) |
Spot | -- | -- | -- | 01/01/02 | 12/31/02 | -- |
Note: 1Pr. = Precedence
Deactivating Price lists
You should deactivate a price list once you have merged it successfully with a multicurrency conversion list.
Rounding
Three rounding profiles affect rounding in multicurrency price lists. These profiles are discussed in the Oracle Advanced Pricing Implementation Guide, Profile Options.
QP: Unit Price Precision Type: This profile is used to determine the value for the rounding factor which is defaulted on the price list.
QP: Price Rounding: If this profile option is set to Enforce Currency Precision, the Base Round To cannot be updated in the currency conversion list.
QP: Selling Price Rounding Options: This profile has optional settings to determine how the converted list price and adjustments can be rounded.
Currencies
The following must be defined in Oracle General Ledger:
Base currency and To Currencies
Seeded conversion types of user defined, spot, EMU fixed and corporate
Markup and Conversion Formulas
Formulas can be created and used for Markup and for Conversion Type 'Formula'. For setup information, see the Oracle Advanced Pricing User's Guide.
Using formulas in multiple currency price lists has some limitations.
Base Markup Formula: The value that is returned by the Base Markup Formula will be used as markup for the base currency. If the Formula uses the List Price (LP) component, the formula will use the list price after applying the conversion rate. A formula with a component type as PLL (Price List Line) is not allowed as a Base Conversion Formula. This is because the PLL line may have a different base currency than the price list to which this currency conversion criteria is attached.
Conversion Type Formula: The value that is returned by the Formula selected, is the conversion rate.
A formula with a component type of PLL (Price List Line) is not allowed as a conversion formula because the PLL line may have a different base currency than the price list to which this currency conversion is attached.
A formula with a component type of MV (Modifier Value) is not allowed as a conversion formula because there is no modifier value is in the currency conversion list.
Attributes
You can use different attribute values for attribute types: Product, Pricing, and Qualifier, to specify more than one conversion for the same Currency To, start date and end date.
Precedence
This precedence differs from attribute precedence.
This precedence, which is set up by the user, applies only to the currency conversion window. You must enter a precedence value in this field when setting up more than one conversion for the same Currency-To, start date, and end date and having different Attribute Value. When more than one attribute is passed from the calling application, the attribute with the lowest precedence value is selected by the pricing engine for conversion.
| Currency-To | Conversion Type | Attribute Code | Attribute Value | Start Date | End Date | Prc. |
|---|---|---|---|---|---|---|
| JPY | Fixed | Item Number | AS54888 | 3/1/01 | 3/31/01 | 1 |
| JPY | Daily | All Items | All | 3/1/01 | 3/31/01 | 2 |
In this scenario, when item AS5488 is passed from the calling application the conversion that is defined for Item Number= AS54888 is used by the pricing engine because it since it has lower precedence value = 1 as compared to the precedence for All Items = All which is 2.
Related Topics
Oracle Advanced Pricing User's Guide, Multi-Currency Conversion List chapter
Creating Context and Attributes to be used for Pricing Setup windows
The Multiple Currency Price List feature is fully integrated with Oracle Order Management. Before using this feature, you should check with other Oracle products that integrate with Advanced Pricing to determine when they will support this feature. At the time of this writing, these products include Oracle iStore, Oracle Order Capture, Oracle Quoting and Oracle Contracts.
If Advanced Pricing is multicurrency enabled and you use Oracle products that do not yet support multiple currency price lists, when the calling application passes the price list, the pricing engine matches the base currency of the price list with the order currency. If no price list is passed, the pricing engine looks for price lists for which the base currency matches the order currency. The pricing engine does not look to the conversion currencies on the multiple currency price lists. This means that price lists still need to be defined and maintained for every single base currency used.