pending_to_deliver_with_bin_qty_query.sql

select 
	so.name as "SO No:Link/Sales Order:100",
	so.transaction_date as "SO Date::80",
	so_item.item_code as "Item Code:Link/Item:160",
	so_item.description as "Description::300",
	(so_item.qty - ifnull(so_item.delivered_qty, 0)) as "PEND QTY:Decimal:70",
	so_item.reserved_warehouse as "WH::80",
	bin.actual_qty as "ACT:Decimal:40",
	bin.reserved_qty as "RSVD:Decimal:40",
	so.customer as "Customer:Link/Customer:150"
from
	`tabSales Order` so, 
	`tabSales Order Item` so_item 
		left join `tabBin` bin on (bin.item_code=so_item.item_code 
			and bin.warehouse=so_item.reserved_warehouse)
where
	so_item.parent = so.name
	and so.docstatus = 1
	and so.status != "Stopped"
	and so.transaction_date <= curdate()
	and so_item.item_code not in (
		"CSP000000-000", "HSP000000-000", "JHKG-01", "JHNO-0075-01", "JCNO-01")
	and bin.actual_qty > 0
	and ifnull(so_item.delivered_qty,0) < ifnull(so_item.qty,0)
order by so.transaction_date asc