Sunday, July 5, 2009

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.

26 comments:

  1. Hello
    Thanks for your smart efforts, I want to know how to get item cost in price list in case of OPM organizations, in difference legal entities

    ReplyDelete
    Replies
    1. Hi Saber. I want this answer too. How do you acheive eventually?

      Thanks!

      fortunekg@gmail.com

      Delete
  2. Hi Piotr Belter,

    Nice Article. Based on this article, i have given solution to my client.

    Thanks
    Lakshmikantha

    ReplyDelete
  3. Would this work for using a price list for internal requisitions that flow into internal orders? I would want a price, other than item price, to be the cost on the internal req.

    ReplyDelete
    Replies
    1. That is exactly what this is intended to do

      Delete
    2. Adam,

      For the IR/ISO process please read my second post:
      "How to create a price list based on item cost - Part II, Intercompany Price List in Oracle"

      Delete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. Hi,

    Thanks for the Wonderful Post.

    Can we have the same thing on the bases of Item Cost.

    i.e. if Item Average Cost is 5; which falls between 1 - 10, Add 200% Markup.
    If Item Average Cost is 100; which falls between 11 - 101, Add 150% Markup.
    and so on.

    Lemme know if you require any updates.

    ReplyDelete
  6. In addition to mapping the item cost as pricing attribute map it as a qualifier.
    Then create 2 modifiers, one as 200% surcharge and the other as 150%
    Qualify the 200% modifier with Item Cost qualifier between 1-10 and the 150% with 11-101

    ReplyDelete
    Replies
    1. Could you please Elobrate and help me out to achieve this functionality

      Delete
    2. Not sure where to start..
      Where did you get stuck? Mapping the cost as Qualifier? It needs to be done the same way as mapping a pricing attribute, which the steps above cover, but select Qualifier context instead of pricing context.

      Delete
    3. Hi,

      Thanks for the update.

      I m stuck in linking qualifier to Price List.

      Lemme know if i can send across setup FYR.

      It seems so Close, But unable to Finalize.

      Delete
  7. Hi,

    Thanks for the Quick Update.

    Could you please clarify and let me know the steps to do the same.

    I was trying to perform; but unable to get the same.

    ReplyDelete
  8. In your scenario you don't need to link qualifier to Price List. You link the qualifier to a modifier with the appropriate surcharge.
    The modifier will apply automatically on top of the price list.

    ReplyDelete
    Replies
    1. Hi,

      Could you please let me know following: The Above Solution requires Advance Pricing Licence. Rite?

      or can it be achieved with Basic Pricing?

      Delete
  9. I believe all the attribute mapping requires Advanced Pricing license

    ReplyDelete
  10. Hi Mr. Piotr Belter,
    Many many thanks for this wonderful document. Realy it is an excellent guidance to an important achievement. Please accept my heart-felt thanks.
    Best regards,

    ReplyDelete
  11. Hi Mr. Piotr Belter,
    Under guidance of your blog, we have created / setup ItemCost related pricing formula. That is perfact. The UC is taken from the Shipping Organization. There is another query, 'can we select / take the highest item cost out-from all Inventyory Organizations under the same OU?' If YES then how that can be achieved?
    Thanks and regards,

    ReplyDelete
    Replies
    1. You can do that, but you need to write your own PL/SQL package that would retrieve all the costs from different orgs and return the highest one, and put it in the User Value String.

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  13. Hi everybody,

    Thanks for this post, I found it very useful.

    B U T!!!!

    I have tested formula with different UOM's and price is not re-calculated from the new cost, i have many UOM Conversion defined but formula not works with others UOM, only with primary UOM.
    In my sales order, price is not recalculated with formula when i select other UOM, but we have "UNIT_COST" field in sales order line and that field is upgraded when I select another UOM, i have read in “my oracle support” that this field use the same Function to UNIT COST Calculation Doc ID 781718.1.

    I have used Function:

    "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)"

    BEST REGARDS

    ReplyDelete
  14. This is an absolutely wonderful post. Clear, completely written and exceptionally useful - you just saved me hours of figuring this out myself. THANK YOU!!!!!

    ReplyDelete
  15. Hello,
    I followed all the specs described above for an IR/ISO for a transfer from a discrete org to an OPM org, but it's not working. The formula retuns zero instead of the cost picked from the pricelist
    Ple

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. I'm working on a requirement in Oracle R12, forms version 12.2.9. for Advance Pricing.
    I have a price list "Item Cost Price List" which only has the item categories assigned to it.
    This Price list is assigned to a specific Order type, so whenever I'm creating a Sales Order with the order type as Pre-COD Item Cost Price List gets defaulted.
    Now, I want to fetch the Item price from a different Price list "Factory Interwarehouse Price List" when the Sales Order has the price list Item Cost Price List.
    I know that what ever the default price list is given at the Sales Order Header level, the Item cost will be fetched from the same price list.
    Now I want to know how I can fetch the Item cost from "Factory Interwarehouse Price List" to Sales Order and then reduce 4% of the price sourced from this price list.
    Can we achieve this using Oracle Advance Pricing?

    ReplyDelete