GMI Inventory Transfer (1)

select xf.transfer_status ST,
        ge.meaning Status,
       xf.from_warehouse x,
       wh.whse_name From_Whse,
       xf.from_location From_Location,
       ic.item_no Item,
       lt.lot_no Lot,
       lt.sublot_no Sublot,
       xf.to_warehouse x1,
       wh1.whse_name To_Whse,
       xf.release_quantity1 Release_Quantity,
       xf.release_uom1 UOM,
       to_char(decode(xf.transfer_status,1,xf.last_update_date,xf.actual_release_date),'mm/dd/yyyy HH:MI AM') date1,
      to_char(decode(xf.transfer_status,1,xf.scheduled_release_date,
                                                   2,xf.scheduled_receive_date,
                                                   5, xf.cancel_date,
                                                   3, xf.actual_receive_date,
                                                       xf.scheduled_release_date),'mm/dd/yyyy HH:MI AM') date2,                                                 
       xf.orgn_code Orgn_Code,
       xf.transfer_no Transfer_No
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 whse_code from ic_whse_mst a, sy_orgn_usr b
                                          where a.orgn_code=b.orgn_code and
                                          b.user_id=:user_id and
                                          a.delete_mark=0)
and xf.to_warehouse in (select whse_code from ic_whse_mst a, sy_orgn_usr b
                                      where a.orgn_code=b.orgn_code and
                                       b.user_id=:user_id and
                                       a.delete_mark=0)                    
&ReleaseDateCF
&SourceWareHouseCF
&DestinationWarehouseCF
&TreansferStatusCF
&ScheduledShipDateCF
&CancelDateCF
&ActualReceiveDateCF
order by     xf.transfer_status ,
	 xf.from_warehouse,
	 xf.from_location,
	 ic.item_no,
	 lt.lot_no,
	 lt.sublot_no,
	 xf.to_warehouse