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
