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
Hi,
Can you please help me with the query. Thanks in advance.
Thanks & Regards,
Upen.
hi upendra,
I believe there is a column named "snapshotprice" in the itemdynamic table.
Hi,
I have checked with the ItemDynamic table, there is no colum with "Price".
Thanks & Regards,
Upen
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
Hi VKR,
Am little bit worry in database to run the queries,if anything happens,its a big problem
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.
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?
Hi Abhilash,
How did you go?
-VKR.
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (