Stock report issue

1

we create query report for stock balance . but it show the qty stock reconcilation amount. erpnext v15.22.1
SELECT
sle.item_code,
item.item_name,
sle.warehouse,
SUM(sle.actual_qty) AS actual_balance_qty
FROM
tabStock Ledger Entry AS sle
JOIN
tabItem AS item ON sle.item_code = item.name
WHERE
sle.company = %(company)s
GROUP BY
sle.item_code, item.item_name, sle.warehouse;

2

if you want to show real stock (qty) the table name is not actual_qty but qty_after_transaction so your query will be like :

SELECT
sle.item_code,
item.item_name,
sle.warehouse,
SUM(sle.qty_after_transaction) AS actual_balance_qty
FROM
tabStock Ledger Entry AS sle
JOIN
tabItem AS item ON sle.item_code = item.name
WHERE
sle.company = %(company)s
GROUP BY
sle.item_code, item.item_name, sle.warehouse;

3

now it is shows abnormal values.

4

Please try it. Use the Bin doctype for get the stock qty.

SELECT 
    a.item_code AS "Item:Link/Item:120",
    a.item_name AS "Item Name::150",
    a.item_group AS "Item Group:Link/Item Group:120",
    a.description AS "Description::150",
    b.warehouse AS "Warehouse:Link/Warehouse:120",
    b.actual_qty AS "Balance Qty:Float:140",
    w.company AS "Company:Link/Company:120"

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

Output:

image
image1920×924 83.3 KB

(Only available in v14 or v13)

3 Likes
5

Its seems ok now. Thank you