GMI Lot Inventory

select        inv.whse_code  whscode,
                 i.item_no,
                 l.lot_no,
                 l.sublot_no,
                 w.whse_name,
                 s.orgn_code orgn_code,
                 i.item_desc1,
                 i.item_um,   
                 i.item_um2,
	 l. lot_created,
                 l.expire_date,
                 inv.location,
                 inv.lot_status as lot_status,
                 round(sum( inv.loct_onhand),2) as sum_inv_loct_onhand,
                 round(sum(inv.loct_onhand2),2) as sum_inv_loct_onhand2,
                 i.delete_mark,
	 s.orgn_name  name
from          ic_item_mst i,
                 ic_lots_mst l,
                 ic_loct_inv inv,
                 ic_whse_mst w ,
	 sy_orgn_mst s
where        i.item_id  =  l.item_id
and            l.item_id  =  inv.item_id
and            l.lot_id  =  inv.lot_id
and          l.lot_no != :deflot
and          inv.whse_code  =  w.whse_code
and         w.orgn_code=s.orgn_code
and       inv.whse_code between NVL(:FROM_WHSE, inv.whse_code ) and  NVL(:TO_WHSE,inv.whse_code)
and  	  i.item_no between NVL( :FROM_ITEM,i.item_no) and NVL(:TO_ITEM, i.item_no)
and            i.delete_mark=decode(:select_criteria,'Active',0,'Deleted',1,'All',i.delete_mark,0)
and w.orgn_code =:p_orgn
group by    inv.whse_code,
	 item_no,
                 lot_no,
                 sublot_no,
                 w.whse_name, 
                 item_desc1,               
                 item_um,
                 item_um2,
                 lot_created,
                 expire_date,
                 location,                
                 s.orgn_code,
                 i.delete_mark ,
	 s.orgn_name,
               inv.lot_status
having      sum(inv.loct_onhand)  <>  0 or sum(inv.loct_onhand2)   <>  0