Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics RMS forum

Storewise item cost price and selling price are not Matching in HQ

Posted on by Microsoft Employee

HI,

I have an issue in HQ manager,when am checking the store selling price and cost price in item properties,its shows different different costs and prices.How it is happened.There are many items like this.Please give me the proper solution for this.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi,

    Can you please help me with the query. Thanks in advance.

    Thanks & Regards,

    Upen.

  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    hi upendra,

    I believe there is a column named "snapshotprice" in the itemdynamic table.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi,

    I have checked with the ItemDynamic table, there is no colum with "Price".

    Thanks & Regards,

    Upen

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi Abhilash,

    Take a backup of the database and try to do your SQL on the backed up copy. You should be safe in this way. Once happy with the resule you can implement in the live database. I have done this twice or thrice before.

    - VKR

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi VKR,

    Am little bit worry in database to run the queries,if anything happens,its a big problem

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi Abhilash,

    It is good to know your cost information is right, one less thing to worry about. In regards to price, it is bit shocking you say that they are 0. You have to check something with the help of SQL. 

    You may know about the table "Itemdynamic" which has all the stores item data in HQ database (like snapshot quantity,snapshotprice,Qty,Price and etc..)

    Snapshotprice & snapshotquantity is coming from stores's database. Qty & Price is calculated by HQ in its own logic and it may not match the other 2 values always. Thus you see discrepancies in quantity or price (rare) from time to time.  

    Query 1:

    Select item.itemlookupcode,itemdynamic.price,itemdynamic.snapshotprice from itemdynamic where itemdynamic.price <> itemdynamic.snapshotprice and storeid=XXX

    This will give a list of items with discrepancies. If you feel snapshotpice is right then you may do another SQL to update the price to snapshotprice. I hope your snapshotprices are right. Snapshotprice is actually item.price from store database.

    Query 2:

    Update itemdynamic set price=snapshotprice where storeid=XXX

    what if you are not sure that the current snapshotprice for the item is not right? then you have to push all the item price from store to HQ database's itemdynamic table. How? again by sql

    Query 3:

    BEGIN
    declare @mytable table
    (
    itemid int,
    itemprice money
    )
    -- insert values to temp table
    insert into @mytable select distinct item.id,Price from Item
    --update main table with your temptable
    update Item set Price= my.itemprice from @mytable my
    inner join Item on item.ID=my.itemid
    END

    once you run the above query send a 401 worksheet to store and wait for it to finish. Then run query 2 in hq database. You can also use sql view if you are not comfortable. 

    Also please make sure you take a backup of the databases. ( You may want to run this in your test environment first). 

    Hopefully it solves your problem for now and also it is an once off issue. 

     

    -VKR.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi VKR,

    I have calculated as per ur formulas,the result is given correct.That means all stores is calculated  as weighted average cost.Thats why cost is showing different in the stores.I got it.Thanks a lot VKR.And one more thing the why the price are not updating in store prices tab?Its showing zero only.Can u plz help me for that?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi Abhilash,

    How did you go?

    -VKR.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi Abhilash,

    When you are using weighted average cost then it is meant to be different.  At the store operations configuration also you have same setup (weighted average). Store Manager-->Configuration-->Inventory-->Cost update Method.

    For the example you mentioned, the cost is $100 which is correct. Stores with 0 quantities are ignored while calculating cost. 

    You mentioned about POs. When you order an item from 2 different suppliers (or same supplier) but at different cost at different times then cost information will change. 

    Check the last few purchase orders for the item, you may find it is ordered at different cost at different times. 

    -VKR. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Storewise item cost price and selling price are not Matching in HQ

    Hi VKR,

    I checked in HQ as u said,that option is selected(Calculate HQ item cost as weighted Average cost).But in item propeties,the store cost tab ,the cost is showing different from other stores.For eg.

    Sore 1 item A Qty is 1 cost is 100

    Store 2 item A Qty is 0 cost is 68

     Store 3 item A Qty is 0 cost is 10

     Store 4 item A Qty is 0 cost is 11

    Sore 5 item A Qty is 2 cost is 100

    its is coming like this.Because of this , in general price Tab,that cost is also affected.We are making the PO in correct way only.But still the costs are changing

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,888 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,247 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans