How to create a price list based on item cost - Part I

To create a price list that has prices of all items based on the item cost plus markup you could use the standard functionality to copy the costs into prices and create a price list that has a cost plus markup. While it works, it is not very flexible and as cost changes and new items are added the price list requires constant attention and manual updates.

Instead doing that you can create a price list that has only one line for ALL ITEMS and dynamically sources the current cost and applies markup at the moment of the actual transaction. This does not require any maintenance, other than updating the costs which is done by the costing department anyway. This approach consists of 3 steps:

1. Map the item cost as a pricing attribute
2. Use the pricing attribute to create a dynamic pricing formula that adds markup
3. Create ALL ITEMS price list and assign the pricing formula there.

1. Mapping the cost as a pricing attribute

I could utilize the standard Pricing Attribute context, but I recommend adding a separate one for better transparency. You can later build upon it and create additional attributes such as Material Cost, Overhead cost etc.

Setup -> Attribute Management -> Context and Attributes

Select a column for attribute mapping, i.e. PRICING_ATTRIBUTE1 and a numeric value set such as Number_15. Do not select the seeded ‘Number’ value set as it is not numeric.

Now that we have our new pricing context defined go to

Setup -> Attribute Management -> Context and Attributes

Now, map the attributes on line level only.

In the ‘Global Object Name’ field you can see the name of the record structure that has all the variables that can be used when deriving the price. In this case it is OE_ORDER_PUB.G_LINE

You can go to the package definition of OE_ORDER_PUB and see what fields the G_LINE record contains

Now, if I just typed OE_ORDER_PUB.G_LINE.UNIT_COST in the User Value String then my pricing attribute would return the unit cost and it would be usable. The problem with this approach is that it is limited to Order Management and the UNIT_COST field is not always populated even there.

Lets take a more generic approach that should work for any record type.

In my previous post I used the

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)

function to get the cost of any item.

I will paste this function directly into the ‘User Value String’ of the pricing attribute mapping. The inventory_item_id and organization_id need to be substituted by the OE_ORDER_PUB.G_LINE variables:

Nvl(cst_cost_api.get_item_cost(1, OE_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL),0)

The NVL prevents the pricing engine to error out when no cost is defined. Depending on the situation, you may want it to error out.

This is what needs to be pasted into the ‘User Value String’ field.

Now, go to Tools and select the ‘Build Attribute Mapping’ to complete the definition

This would work fine, except that my shipping warehouse can be in USD and my order in EUR. Since the cost is always returned in the currency of the set of books of the warehouse the above expression needs to be multiplied by the exchange rate that you can also get from my previous post:

gl_currency_api.get_rate(from_currency_code,to_currency_code, conversion_date, Conversion_type)

For transparency it is better to source the exchange rate as a separate pricing attribute such as CONV_RATE and then multiply them in the pricing formula

Now, TO_CURRENCY_CODE is OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE

Note that I took currency from the header record, which is also populated at runtime.

Conversion date is OE_ORDER_PUB.G_LINE. PRICING_DATE

And conversion type is fixed such as ‘Corporate’

The only remaining parameter is

from_currency_code

To find the operating unit of the shipping warehouse in R12 use the below function:

qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID).

Let me know if you find a relevant function in 11i. The ones I found are either not public, or they are not available to every installation, such as the Indian localization function jai_cmn_utils_pkg.get_operating_unit( 'anything', SHIP_FROM_ORG_ID). In 11i you may have end up writing your own function.

Now that the operating unit of the shipping warehouse is known, the CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode function can be used to derive the from_currency. The full expression is:

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID))

The complete expression that finds the conversion rate between the shipping warehouse and the order operating unit is :

gl_currency_api.get_rate(

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),

OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE,

sysdate,

'Corporate')

Create and map the Conversion Rate Pricing Attribute the same way as Item Cost.

Now that have both item cost and the conversion rate defined we can build our Pricing Formula

2. Pricing Formula

Pricing Formula -> Formula setup

Go to Tools -> Build Formula Package

3. Price List

The Price list needs to be defined for all base units of measure.

Now the price list is ready to be used.