GMI Warehouse Inventory

select            l.whse_code whscode ,
		      w.whse_name ,  
		      i.item_no , 
		      i.item_desc1 ,  
		      i.item_um ,
		      i.item_um2 , 
		      l.item_id ,
		      s.orgn_code  orgn_code,
	     	 s.orgn_name, 
	GMI_RIWIN_XMLP_PKG.whse_qtyformula(l.whse_code, l.item_id) Whse_Qty, 
	GMI_RIWIN_XMLP_PKG.total_qtyformula(l.item_id) Total_Qty, 
	round(GMI_RIWIN_XMLP_PKG.whse_qty2formula(l.whse_code, l.item_id)) Whse_Qty2, 
	round(GMI_RIWIN_XMLP_PKG.total_qty2formula(l.item_id)) Total_Qty2
from             ic_item_mst i, 
		          ic_whse_mst w,  
		          ic_loct_inv  l   
		        ,  sy_orgn_mst s
                  where       i.item_id  =  l.item_id   
                  and          w.whse_code  =  l.whse_code  
                  and           i.delete_mark  =  0
                  and          w.delete_mark  =  0
                  and          UPPER(l.whse_code) between UPPER(NVL(:From_whse,l.whse_code)) and UPPER(NVL(:To_whse,l.whse_code))
                  and          UPPER(i.item_no) between UPPER(NVL(:From_item,i.item_no)) and UPPER(NVL(:To_item,i.item_no))
                  and           w.orgn_code = s.orgn_code
                  and w.orgn_code=:p_orgn  
	  group by   l.whse_code,
		  w.whse_name,
		   i.item_no, 
		  i.item_desc1,
		       i.item_um,  
		       i.item_um2,
		       l.item_id,
		       s.orgn_code,
                                       s.orgn_name
	     having     sum(l.loct_onhand)  <>  0  or  sum(l.loct_onhand2)  <>  0
                    order by whscode,i.item_no asc