Query to get from and to bin location in stock transfer

Dear All

The  below sql query display from and to bin location for batch and non batch items.Even if you have both batch and non batch items in same inventory transfer document,this query will work.you can also set this query as layout.you can add or replace the columns on your own.

select vw1.*,vw2.[To Bin Location],vw2.WhsCode from (SELECT T3.[BatchNum],
T0.DocEntry AS DOCENTRY,t1.docentry as doc,CONVERT(DECIMAL,T0.ObjType) AS OBJTYPE,T0.DocNum AS DOCNUM,t1.linenum as line,
T1.ItemCode AS ITEMCODE,
T1.Dscription AS ITEMNAME,T1.unitMsr AS UOM,CONVERT(VARCHAR,T0.DocDate,103) AS TRNSFRDATE,
T1.FromWhsCod AS ISSUEWHS,
(SELECT DOCNUM FROM OWTQ WHERE T1.BaseEntry=OWTQ.DOCENTRY) AS BASEDOCNUM,T1.BaseQty as reqqty,
T3.Quantity  AS QTY,
t6.BinCode 'From Bin Location'
FROM
OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry=T1.DocEntry
inner join OUSR T2 on t0.UserSign=t2.USERID
LEFT join  IBT1 T3 on  T3.[BaseEntry] = T0.DocENtry   and Direction = 0  and T3.BaseType = 67 and t3.BaseLinNum=t1.LineNum
  inner join oilm t4 on t1.docentry=t4.DocEntry and t1.LineNum=t4.DocLineNum and t4.TransType=67 and t4.LocCode=t1.FromWhsCod 
  AND T4.AccumType=1
 left join obtl t5 on t4.MessageID=t5.MessageID
  left join obin t6 on t5.BinAbs=t6.absentry --and t6.whscode=t1.FromWhsCod
  where t0.docentry=(replace with your parameter)
  group by
T3.[BatchNum],
T0.DocEntry ,t1.docentry ,T0.ObjType,T0.DocNum
,t1.linenum ,T1.ItemCode ,
T1.Dscription,T1.unitMsr, T0.DocDate,
T1.FromWhsCod,T0.U_ProNo,
T1.BaseQty ,
T3.Quantity  ,
t6.BinCode ,t1.BaseEntry
)vw1 inner join (select t1.docentry,t1.linenum,t6.BinCode 'To Bin Location',t1.ItemCode ,t1.whscode FROM
OWTR T0
left JOIN WTR1 T1 ON T0.DocEntry=T1.DocEntry
  inner join oilm t4 on t1.docentry=t4.DocEntry and t1.LineNum=t4.DocLineNum and t4.TransType=67 and t4.LocCode=t1.WhsCode
  AND T4.AccumType=1
  left join obtl t5 on t4.MessageID=t5.MessageID
left join obin t6 on t5.BinAbs=t6.absentry and t6.whscode=t1.WhsCode
where t0.docentry=(replace with your parameter)
group by t1.docentry,t1.linenum,t6.BinCode ,t1.ItemCode ,t1.WhsCode
  )vw2 on vw1.line=vw2.LineNum where vw1.batchnum is not null
union all
  select vw1.*,vw2.[To Bin Location],vw2.WhsCode from (SELECT T3.[BatchNum],
T0.DocEntry AS DOCENTRY,t1.docentry as doc,CONVERT(DECIMAL,T0.ObjType) AS OBJTYPE,T0.DocNum AS DOCNUM,t1.linenum as line,T1.ItemCode AS ITEMCODE,
T1.Dscription AS ITEMNAME,T1.unitMsr AS UOM,CONVERT(VARCHAR,T0.DocDate,103) AS TRNSFRDATE,
T1.FromWhsCod AS ISSUEWHS,
(SELECT DOCNUM FROM OWTQ WHERE T1.BaseEntry=OWTQ.DOCENTRY) AS BASEDOCNUM,T1.BaseQty as reqqty,
T3.Quantity  AS QTY,
t6.BinCode 'From Bin Location'
FROM
OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry=T1.DocEntry
inner join OUSR T2 on t0.UserSign=t2.USERID
LEFT join  IBT1 T3 on  T3.[BaseEntry] = T0.DocENtry   and Direction = 0  and T3.BaseType = 67 and t3.BaseLinNum=t1.LineNum
  inner join oilm t4 on t1.docentry=t4.DocEntry and t1.LineNum=t4.DocLineNum and t4.TransType=67 and t4.LocCode=t1.FromWhsCod 
  AND T4.AccumType=1
 left join obtl t5 on t4.MessageID=t5.MessageID
  left join obin t6 on t5.BinAbs=t6.absentry --and t6.whscode=t1.FromWhsCod
  where t0.docentry=(replace with your parameter)
  group by
T3.[BatchNum],
T0.DocEntry ,t1.docentry ,T0.ObjType,T0.DocNum
,t1.linenum ,T1.ItemCode ,
T1.Dscription,T1.unitMsr, T0.DocDate,
T1.FromWhsCod,T0.U_ProNo,
T1.BaseQty ,
T3.Quantity  ,
t6.BinCode ,t1.BaseEntry
)vw1 inner join (select t1.docentry,t1.linenum,t6.BinCode 'To Bin Location',t1.ItemCode ,
t1.whscode FROM
OWTR T0
left JOIN WTR1 T1 ON T0.DocEntry=T1.DocEntry
  inner join oilm t4 on t1.docentry=t4.DocEntry and t1.LineNum=t4.DocLineNum and t4.TransType=67 and t4.LocCode=t1.WhsCode
  AND T4.AccumType=1
  left join obtl t5 on t4.MessageID=t5.MessageID
left join obin t6 on t5.BinAbs=t6.absentry and t6.whscode=t1.WhsCode
where t0.docentry=(replace with your parameter)
group by t1.docentry,t1.linenum,t6.BinCode ,t1.ItemCode ,t1.WhsCode
  )vw2 on vw1.line=vw2.LineNum where vw1.batchnum is null