GMI Inventory Transfer Report By Batch

select xf.transfer_status Stat,
       xf.transfer_batch batch,	
       ge.meaning Status,
       xf.from_warehouse||decode(xf.from_location,null,' ' , 		'/'||xf.from_location) from_ware_loc,
             ic.item_no||decode(lt.lot_no,null,' ','/'||lt.lot_no)||decode( lt.sublot_no,null,' ','/'||lt.sublot_no) item_lot_sub,
       xf.to_warehouse||decode(xf.to_location,null,' ','/'||xf.to_location) To_ware_loc,
       xf.release_quantity1||' '||xf.release_uom1 Release_qty,       decode(xf.transfer_status,1,to_char(xf.scheduled_release_date,'MM/DD/RR HH24:MI'),2,to_char(xf.actual_release_date,'MM/DD/RR HH24:MI'),
	3,to_char(xf.actual_release_Date,'MM/DD/RR HH24:MI'),4,to_char(xf.scheduled_release_Date,'MM/DD/RR HH24:MI'), 5,to_char(xf.actual_release_date,'MM/DD/RR HH24:MI'),null) 
                actual_rel_date,
      decode(xf.transfer_status,1,to_char(xf.scheduled_receive_date,'MM/DD/RR HH24:MI'),2,to_char(xf.scheduled_receive_Date,'MM/DD/RR HH24:MI'),
	3,to_char(xf.actual_receive_date,'MM/DD/RR HH24:MI'),4,to_char(xf.scheduled_receive_Date,'MM/DD/RR HH24:MI'),5,to_char(xf.cancel_Date,'MM/DD/RR HH24:MI'),null)
     	receive_cancel_date,                                                 
      xf.orgn_code||' '||xf.transfer_no Transfer_No, 
	GMI_CMXFER02_XMLP_PKG.release_cfformula(xf.transfer_status) RELEASE_CF, 
	GMI_CMXFER02_XMLP_PKG.receive_cfformula(xf.transfer_status) RECEIVE_CF
from   ic_xfer_mst xf,
       ic_whse_mst wh,
       ic_whse_mst wh1,
       ic_item_mst ic,
       ic_lots_mst lt,
       gem_lookups ge
where  xf.item_id=ic.item_id
and    xf.from_warehouse=wh.whse_code
and    xf.to_warehouse=wh1.whse_code
and    xf.lot_id=lt.lot_id
and    xf.item_id=lt.item_id
and    xf.transfer_status=ge.lookup_code
and    ge.lookup_type='TRANSFER_STATUS'
and (xf.from_warehouse IN 
(SELECT w.whse_code FROM ic_whse_mst w, sy_orgn_usr u 
WHERE w.orgn_code = u.orgn_code AND u.user_id = :v_user_id AND w.delete_mark = 0) 
OR xf.to_warehouse IN (SELECT w.whse_code FROM ic_whse_mst w, sy_orgn_usr u 
WHERE w.orgn_code = u.orgn_code AND u.user_id =:v_user_id AND w.delete_mark = 0) 
OR xf.orgn_code IN (SELECT orgn_code FROM sy_orgn_usr WHERE user_id = :v_user_id)) 
&ReleaseDateCP
&SourceWareHouseCP
&DestinationWarehouseCP
&TransferStatusCP
&ScheduledShipDateCP
&CancelDateCP
&ActualReceiveDateCP
&BatchNoCP
order by   xf.transfer_batch,3,
	xf.transfer_status,
	wh1.orgn_code,
	xf.transfer_no,
	xf.from_warehouse,
	xf.from_location,
	 ic.item_no,
	 lt.lot_no,
	 lt.sublot_no,
	 xf.to_warehouse