Tuesday, July 7, 2009

How to create a price list based on item cost - Part II, Intercompany Price List in Oracle

You can find my presentation on this topic from 2015 OAUG here

In my previous post I described how to create a price list based on cost. Why would we want to have a price list based on cost? From my experience there are 2 common scenarios where this comes handy:

  1. RMA price list for obsolete items that are no longer priced, but customers keep returning them.

  2. For intercompany transactions where the transfer price between organizations is cost plus markup, and it would be cumbersome to have a manually maintained price list where all the costs are already defined.
The RMA situation is not that common, but all multinational companies have intercompany transfers. I have seen one company where the number of internal price list lines exceeded 180,000 while the regular sales price lists had only 42,000. They could no longer maintain it and had to write custom concurrent requests to do it for them.

The beauty of the below solution is that it requires no customizations and it will price all intercompany transactions based on ‘Cost + markup’ logic, with no maintenance required at all.
To begin with, I recommend creating separate responsibility for Intercompany pricing, to keep the IC and Sales price lists, modifiers, formulas, qualifiers and modifiers separate. This is not required, as alternatively you can set up the relevant profile options on a dedicated user level, separate responsibility is by far a cleaner solution.

Go to Application Developer -> Profiles and check the responsibility checkboxes for the below:


Now create your custom responsibility
(N) System Administrator -> Security -> Responsibility -> Define




Set the QP: Pricing Transaction Entity and QP: Source System Code profiles on the Responsibility Level.



Add the responsibility to your user. You have successfully set up your tools to begin working with Intercompany Pricing.
In addition to the above, set the following profiles on the site level


- CST: Transfer Pricing Option - Yes, Price As Incoming Cost (read more about other settings)
- INV: Advanced Pricing for Inter-Org Transfers - Yes
- INV:Advanced Pricing for Intercompany Invoice - Yes
- INV: Always suffix inter-Company AP Invoice number – Yes (optional)
- INV: Inter-Organization Currency Conversion – Corporate
- INV: Intercompany Currency Conversion - Corporate
- INV:Intercompany Invoice for Internal Orders – Yes (optional)

Set up shipping networks in inventory responsibility.
Log into the Intercompany Pricing responsibility -> Setup -> Attribute Management -> Attribute Linking and Mapping. Select Intercompany Transaction as the Pricing Entity and Pricing Context, then map the same COST attributes here as for the Order Fulfillment Entity in the previous post.


Map both pricing attributes using the PL/SQL logic, the same as in previous post.
For cost:

cst_cost_api.get_item_cost(1, INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL)
Note that record OE_ORDER_PUB.G_LINE in Intercompany pricing entity is replaced with INV_IC_ORDER_PUB.G_LINE

For Exchange Rate:

gl_currency_api.get_rate(CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),'USD', sysdate,'Corporate')

The 'USD' needs to be replaced with the currency of the intercompany transactions for your installation.

This will work on R12 as long as you don’t use Global Purchase Agreements nor have Oracle Process Manufacturing enabled for any of your organizations that do intercompany pricing. In those cases you will need to use additional logic, which I am omitting here to keep the case simple. As I mentioned in my previous post additional coding may be needed for 11i where the qa_moac_pkg.derive_ou_id function does not exist.
After you are done mapping run the Build Attribute Mapping Rules Concurrent request .
I think that all of the above should be seeded, but it isn’t. The enhancement requests to do so are sitting out on metalink for the last couple of years. On top of that the All Items Product Attribute is not mapped for the ‘Intercompany Transaction’ pricing entity.

Lets fix this obvious omission.


Press Link Attributes button.

Run Build Attribute Mapping request again.

Now that all the core setups are done lets create an Intercompany pricing formula.


Because this formula is created in the Intercompany Pricing responsibility, it is visible, but not updateable in the regular Oracle Pricing Manager or any OM responsibility With the formula ready we can create the Intercompany Price List.

Make sure to list all basic units of measure that can be transacted. In the above example I had 4.
Note the Pricing Transaction Entity for your price list. It will not be usable in Order Management or any other sales applications.

There is a bug in EBS, that Oracle Support did not fix since I first discovered it in 2006. While the sales applications residing in Order Fulfillment Pricing Entity do not see the Intercompany Transactions Entity, the opposite is not true and regular sales Price lists are visible in the Intercompany Transactions entity. To work around this problem assign precedence =1 to your intercompany price list lines.

There is another bug that prevents an Internal Order form being booked if the Intercompany price list is not assigned to the Internal Order type. To work around it assign it to the Internal Order type and also to the internal customer/customer site on the customer definition. From the logical perspective this is not really needed since the intercompany pricing is done by the Trip Stop interface which happens AFTER the order is booked and shipped and all the prices on the intercompany order are dummy values.

Now that you have your intercompany price list set up, you can create Internal Requisitions, Internal Sales Orders, ship them, create Intercompany AP and AR invoices and never worry about updating your intercompany price list.

The Global Purchase Agreement, transfers originating from multi node transactions, and different markups between different organizations, including logical nodes can be also set up to work, but this is a too long topic to cover in this post.

30 comments:

  1. I have started looking at this solution. I have a couple of questions. Do i need to all steps from Part 1 and part to to use a price list with internal requisitions? Also can I use a pricelist with actual items and actual prices since we do not do a flat percent markup over item cost? How can I do that?

    ReplyDelete
  2. You do not need setups from part one. The part 2 is designed to be used for IR/ISO process. On the surface the requisition and the internal order will still have prices but they will be bogus. The Intercompany invoices and the Intercompany material transactions will be priced using the IC price list.

    It does not make sense to put items on the internal price list. This would mean that someone would have to maintain them as you add new items and the intercompany invoices and material transactions would come out as 0 if the person forgets to add the item. This always happens and is very difficult to undo. What you need to do is to create modifiers with different markups depending on number of conditions, such as the from and to org or the item category of the product. I find the item category particularly useful, especially if you map the segments of the category as separate qualifiers. I.e if your category is Furniture.Chairs or Furniture.Tables you can split it into 2 separately mapped qualifiers and have 10% markup for Furniture and 3% markup for Chairs and 1% markup for tables, etc. The mapping of the category attributes does require custom PL/SQL extension that can be called the same way as the cost or exchange rate described in my post

    ReplyDelete
  3. Thank you for the reply. Could you give more detail about the PL/SQL extension you are talking about. Maybe we could use that but if I had more detail that would help in selling this rather than keeping a price list.

    ReplyDelete
  4. please provide the code needed for an opm enabled organization.
    saurabh

    ReplyDelete
    Replies
    1. In an OPM organization you need to replace the cost sourcing. Instead of calling costing package cst_cost_api.get_item_cost as in the above post you need to use this:

      GMF_CMCOMMON.get_cmpt_cost( inventory_item_id, organization_id, sysdate, &cost_id ,0)

      Replace the &cost_id with a static value that you get from this query:

      select cost_type_id from CM_MTHD_MST where where cost_mthd_desc='Standard Cost'

      OPM organizations are tricky, because you can set the standard cost well after the month is over. If you run the Intercompany invoicing before the month is over and you do not have the standard cost in place then the IC invoices may come across at $0 or error out. If you NVL the above function and they do come across at 0 you cannot correct them.

      Today is May 1st and lets say you just created OPM costing for April and happily run the IC process. If you had any transactions as of the 1st of May the IC process is going to pick them up and invoice then as well even if the correct cost is not set yet.
      Unfortunately the IC invoice creation process does not allow you to select the period or time frame for which you would like to create invoices it invoices everything that is ready for invoicing.

      To use this solution effectively you would need to have the OPM standard costs defined before the beginning of the month, but that is usually not how OPM enabled organizations work. They establish the cost after the month is over based on the manufacturing runs and variances during the month.

      Delete
    2. We want to transfer material from Non-OPM org to the OPM enabled inventory org. and we want that material should be transferred at the cost of the transferring org and will also received in the recipient org at transferred price i.e cost of if the transferring org.

      How we can achieve this?

      Delete
  5. Hi there , i have facing a similar problem, I also need to use cost to calculate the price i price list ,I wonder the cost u use is refer to the PO cost or what cost in system?

    Is there a way i can use the additional line information attribute as a cost and when i finish entering the field, the price will show up?

    ReplyDelete
  6. Hi,
    I have to prepare price list based on item costs for inter-org transfer between OPM-Discrete organization, I am using GMF_CMCOMMON.GET_CMPT_COST(INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,SYSDATE, 1000,1).

    But not getting item cost.

    Shashikant

    ReplyDelete
  7. Run a query in SQL first

    select msi.segment1 item,
    GMF_CMCOMMON.get_cmpt_cost( inventory_item_id, organization_id, sysdate, cmm.cost_type_id ,0) cost
    from mtl_system_items_b msi, CM_MTHD_MST cmm
    where cmm.cost_mthd_desc='Standard Cost'

    I am not sure why you used 1 as the last variable. In my queries I always use 0.
    If this query does not return cost this means that the cost for the current period is not defined. Several OPM organizations define cost for any period when closing the period and cost all the transactions backwards, which the OPM Finance module allows. In that case you need to either:

    - use cost form previous period or have the finance
    - have finance define the cost for the current period
    - do not run the Create intercompany AR and AP invoices until the period is over , just before it is closed.

    We have tried the last option and we had significant difficulties enforcing it even after limiting access to the concurrent request

    ReplyDelete
  8. I used both 0 & 1 in GMF_CMCOMMON.get_cmpt_cost, but not getting item cost both the times. We are using average cost. The query given by you returns item cost in toad. But in EBS, getting 0 cost.

    Shashikant

    ReplyDelete
  9. If the query returns results then use:

    cst_cost_api.get_item_cost(1, INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL)

    Note that record OE_ORDER_PUB.G_LINE in Intercompany pricing entity is replaced with INV_IC_ORDER_PUB.G_LINE

    You still had OE_ORDER_PUB for the org, it returned NULL so there was no pricing

    ReplyDelete
  10. Excellent Blog... Thanks for taking time to create this blog

    ReplyDelete
  11. i have one question, where we are trying to Invoice 10%+ item cost, but when i run Create AR invoice program, external API price= Item cost 10% markup is adding. I did all the setups like
    1) Formula to get the item cost
    2) formula attached to Inter company price list.
    3) qualifiers are added to price list.

    Can anyone help me on this.

    Thank you in Advance

    Thanks
    Pavan

    ReplyDelete
  12. Hi Piotr,
    Can you please help if you have any note (for setups and code) for calling pricing engine from external source? We have external application that sends item, quantity, customer etc. details to oracle and pricing engine has to return unit selling price after modifiers are applied.

    Regards
    GRSR

    ReplyDelete
  13. There is quite a few articles about it on the web, just google QP_PREQ_GRP.PRICE_REQUEST. One important hint is to make the p_control_rec.pricing_event :='BATCH'; instead of 'LINE'. Probably the best idea is to look for the above string with BATCH in it and you will hit a page where they did do their homework rather than quoting the Oracle standard script.

    When called correctly the external call can be a very powerful tool. A few years ago I written an interface using the above API to call Oracle QP from eCommerce web site to price web orders. The QP setup had over 400,000 modifiers, each qualified with multiple qualifiers. We got performance of below 1 s per call. The QP pricing engine is amazingly powerful.

    ReplyDelete
  14. Hi Peter,

    Nice article. I am also looking for a similar solution for my case. Our business requirement is to transfer the material from ong Inv. org to another org in the same OU. We would like to transfer with the markup price but we don't create any invoice for that.

    Typically normal IR-ISO flow and as far as I understand normal IR-ISO will pickup the source organization Item cost. Can I use your suggested solution to achieve me requirement.

    Pls. help and guide.

    Thanks lot for all your good work and efforts.

    Regards-Siva

    ReplyDelete
  15. Hi Piotr,
    Do you know how to set the unit_selling_price as formula type in Pricing formulas setup? I want to customized the rounding of unit_selling price by different customer. So I need to get the unit_selling_price instantly and perform round(xx,1).

    I can't get the unit_selling_price instead I only get the unit_list_price now. Thanks in advance

    ReplyDelete
  16. Hi Piotr Belter and everyone.

    First of all, thanks for sharing knowledge in your blog.

    I have few scenarios that is inter-organization Transfer (All org. are process enabled ).
    1. Inter-org transfer within a Operating Unit (Same Legal Entity)
    2. Inter-org transfer within a Operating Unit (Across Legal Entity)
    3. Inter-org transfer across Operating Unit (Different Legal Entity).

    I have searched documents in Oracle Metalink and found
    Reference: White paper on Inventory Organization Transfers in R12 (Doc ID 1464168.1)


    1065808.1 - How is Unit Price Populated on an Internal Requisition for a Process Enabled Org? (Doc ID 1065808.1)

    I need reference doc to setup the above scenarios.

    We have done Direct transfer across Legal entity and within OU. after performing actual cost process, cost update, create accounting. The event of Direct Org. transfer has entries but item cost of transfer org. is not updated.

    Thanks in advance.

    Nelson

    ReplyDelete
  17. Hi,

    I have setup 'Intercompany Pricelist' & 'Intercomany relation '. As per our requirement we have to generate AR intercompany invoice with the currency code of 'To Organization'. Like of US shipp material to Germany then IC AR invoice would be generated in Euro. Euro is functional currency of Germany & USD is functional currency of US. We have attribute mapping for CONV_RATE where we used User value string as : gl_currency_api.get_rate (csd_cost_analysis_util.get_glcurrencycode(qa_moac_pkg.derive_ou_id(INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),
    'EUR',SYSDATE,'Corporate'.----------------------- But the Problem here is the same context will be used for multiple other OU where EUR is not the functional currency. In this case can you help me by giving the User Value string which will able to dynamically identify the 'To Org functional currency'?

    Thanks,
    Sounak Sen
    IBM
    +919007047628
    sounak.sen@icloud.com

    ReplyDelete
  18. Sounak,

    The approach you are proposing should but you can achieve it in a much easier way by turning on multi currency conversion setup in pricing. This way you can have a single price list in USD that will convert prices on EUR invoices to EUR.

    Check/set the profile options:


    QP: Multi Currency Installed - Yes on site level


    QP: Multi Currency Usage - Yes on "Inventory" Application level.

    Then set up the currency conversion types and assign the set to your IC price list. Check the QP Implementation manual under "Multicurrency Conversion Criteria"

    ReplyDelete
  19. Sounak,

    The approach you are proposing should but you can achieve it in a much easier way by turning on multi currency conversion setup in pricing. This way you can have a single price list in USD that will convert prices on EUR invoices to EUR.

    Check/set the profile options:


    QP: Multi Currency Installed - Yes on site level


    QP: Multi Currency Usage - Yes on "Inventory" Application level.

    Then set up the currency conversion types and assign the set to your IC price list. Check the QP Implementation manual under "Multicurrency Conversion Criteria"

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

    ReplyDelete
  21. HI Piotr,
    I want to transfer material from Non-OPM org to the OPM enabled inventory org. and I want that material should be transferred at the cost of the transferring org and will also received in the recipient org at transferred price i.e cost of if the transferring org. I am using GMF_CMCOMMON.GET_CMPT_COST(INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,SYSDATE, 1000,1). but i want to transaction date not sysdate...how its possible????

    How we can achieve this?

    ReplyDelete
  22. I wanted to say that instead of using sysdate use INV_IC_ORDER_PUB.G_LINE.actual_shipment_date but..
    If you want to do it at the cost of the transferring non-OPM org then you want to use this API instead:
    cst_cost_api.get_item_cost(1, INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL)

    ReplyDelete
    Replies
    1. Thanks Piotr, For your reply,
      Actually already solve this problem when transfer Non-OPM to OPM Org. Now I'm facing the problem when transfer to OPM to Non OPM. My client requirement period wise costing. i create e custom function (Bellow) the function:

      CREATE OR REPLACE FUNCTION APPS.XX_MFGORG_PL (P_ORG_ID IN NUMBER,
      P_ITEM_ID IN NUMBER,
      P_DATE IN DATE)
      RETURN NUMBER
      IS
      V_ITEM_CST NUMBER;
      BEGIN
      SELECT SUM (IC.CMPNT_COST)
      INTO V_ITEM_CST
      FROM apps.CM_CMPT_DTL ic, apps.gmf_period_statuses gp
      WHERE ic.period_ID = gp.period_ID
      AND IC.ORGANIZATION_ID = P_ORG_ID
      AND IC.INVENTORY_ITEM_ID = P_ITEM_ID
      AND P_DATE BETWEEN GP.start_date AND GP.END_DATE;
      RETURN V_ITEM_CST;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      RETURN NULL;
      END;
      /
      ..
      Problem is that INV_IC_ORDER_PUB have no transaction date.
      but i need the transaction date....can u help me how i get the transaction date......or any others pkg where i get.


      Thanks
      Moktarul

      Delete
  23. Hi Piotr, this is regarding currency conversion when the IR gets imported as ISO. In the current setup if US is ordering from UK, then US raises an IR (which will be in USD) and the ISO gets imported in UK (in GBP). What i would like to achieve is that the IR and ISO currency are the same ( USD - USD and not USD - GBP ). Please let me know if this is possible. FYI - the currency on the Intercompany price list is USD with multi-currency conversion enabled.

    ReplyDelete
    Replies
    1. Unfortunately Oracle Standard functionality only allows you create the ISO in the ledger currency. The only thin you could do is invoice in the from or to Operating Currency, which can be controlled in the Inter Company Transaction flows.

      Thanks,
      Sudhakar

      Delete
  24. Hi everyone,

    I have done setups for direct inter org transfers between inv orgs of two different ou's,working fine,I mean qty is deducting from source and adding in destination,but accounting does not reflect in material distributions and we are using costing method FIFO. Please suggest.

    REgards,

    ReplyDelete
  25. Dear Sir,
    Kindly help to overcome following an error encountered in Subledger Period Close Exception Report after executing create accounting against Event type # Direct Interorg Receipt, Transfer Pricing.
    Error Number is 95318. How to fix Create Accounting Error Number # 95318
    The account code combination id 0 specified for line 2 does not exist. Please use a valid account code combination id for the subledger journal entry line.

    Ledger Account GL Date Accounting Class Accounted DR Accounted CR
    JIL Primary Ledger 21.9999.99.99.711116.99.9999 24-Sep-19 Inter-Org Payables 226313.94
    JIL Primary Ledger 24-Sep-19 Inventory Valuation 226313.94
    In the above transaction , you can see that account is null for 2nd line against single itemcode.
    how we can solve this issue. Kindly reply for the same . I shall be highly thankful for your concern & cooperation.

    ReplyDelete