Item Stock with value report

1

We try to get the value of the stock based on valuation rate . but it shows Purchase receipts amount

SELECT
a.item_code AS “Item:Link/Item:120”,
a.item_name AS “Item Name::150”,
b.actual_qty AS “Balance Qty:Float:140”,
b.valuation_rate AS “Valuation Rate:Currency:120”, – Correctly labeling the unit rate as “Valuation Rate”
(b.valuation_rate * b.actual_qty) AS “Value:Currency:140”
FROM
tabItem a
LEFT JOIN
tabBin b ON a.item_code = b.item_code
LEFT JOIN
tabWarehouse w ON b.warehouse = w.name
WHERE
w.company = %(company)s;

2

You don’t need this because the stock value is already in the bin doctype itself.

image
image1920×924 44.2 KB

3

actually i want to get it valuation rate which i mentioned in the item master and selling rate. but i got the purchase price

ddddd
ddddd1161×458 24.6 KB

4

I try this below query . i got only purchase rate as valuation rate and selling rate is zero

SELECT
a.item_code AS “Item:Link/Item:120”,
a.item_name AS “Item Name::150”,
b.actual_qty AS “Balance Qty:Float:140”,
b.valuation_rate AS “Valuation Rate:Currency:120”,
(b.valuation_rate * b.actual_qty) AS “Value:Currency:140”,
COALESCE(p.price_list_rate, 0) AS “Selling Price:Currency:120” – Using COALESCE to handle NULL values
FROM
tabItem a
LEFT JOIN
tabBin b ON a.item_code = b.item_code
LEFT JOIN
tabWarehouse w ON b.warehouse = w.name
LEFT JOIN
tabItem Price p ON a.item_code = p.item_code AND p.price_list = ‘Selling’
WHERE
w.company = %(company)s;

5

If you have mentioned the standard selling rate in the item master at the time of item creation then it will be there. You cannot add to the Selling Rate Item Master once created. Now talking about the purchase rate, item master has the last purchase rate, you can use it.

6

Thank you