Query warehouse available quantity

Muthu · January 13, 2017, 4:17am

Hi Guys ,

I am writing a query report , where I have joined few of my custom module apps , now I want to find out the balance quantity of a particular item in a particular warehouse . How to accomplish this ? I don have any clue . Please help

revant_one · January 13, 2017, 4:50am

Check Stock Balance report - get_stock_ledger_entries, get_item_warehouse_map

github.com

frappe/erpnext/blob/develop/erpnext/stock/report/stock_balance/stock_balance.py

# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt


from operator import itemgetter
from typing import Any, Dict, List, Optional, TypedDict

import frappe
from frappe import _
from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, date_diff, flt, getdate
from frappe.utils.nestedset import get_descendants_of

import erpnext
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
from erpnext.stock.utils import add_additional_uom_columns, is_reposting_item_valuation_in_progress

This file has been truncated. show original

Muthu · January 19, 2017, 4:52am

Hi @revant_one . I am not good in python (script reports) , could you please tell me how to query the available or actual qty of a particular item in selected warehouse in a query report.

Thanks

Riyas_Rawther · December 19, 2017, 12:57pm

Try modify this

select item_code as ‘Item Code:Data:300’,

case warehouse when ‘100-Main Warehouse - Nahda - DLTL’ then actual_qty else 0 END AS ‘Main WH:Int:80’,
case warehouse when ‘101- VAN DXB-T99861 NIZHAM - DLTL’ then actual_qty else 0 END AS ‘NIZHAM:Int:80’,
case warehouse when ‘102- VAN SHJ-92558 DIYA - DLTL’ then actual_qty else 0 END AS ‘DIYA:Int:80’,
case warehouse when ‘104-VANDXB-48094SHAMSEER-DLTL’ then actual_qty else 0 END AS ‘SHAMSEER:Int:80’,
case warehouse when ‘103-VAN DXB- NADEEM - DLTL’ then actual_qty else 0 END AS ‘NADEEM:Int:80’,
case warehouse when ‘105-VAN AJM - M56404 MITRA - DLTL’ then actual_qty else 0 END AS ‘MITRA:Int:80’,
case warehouse when ‘107 VAN - AL AIN -U56399 TARA - DLTL’ then actual_qty else 0 END AS ‘TARA:Int:80’,
sum(actual_qty) AS Total
FROM tabBin

Group by item_code, warehouse
order by Total desc
LIMIT 500;