Stock Transfer Query Is Working Only If Two Different Warehouses

[former_member565526]

Dear Experts,

I have query below, From & To bin location is detected only in case of two different warehouses, if the transfer is happening in the same warehouse it shows null

could you help pls

SELECT distinct T0.DocEntry, T0.[DiscSumSy], (case when OWHS.BinActivat ='Y' then (OBTL.Quantity/UGP1.BaseQty) else T1.[Quantity] end) "Quantity", T1.LineNum, T1.ItemCode, T1.Dscription, T1.[UomCode], printed, T1.LineNum+1 [RowNumber], T1.UOMcode,OUOM.UomCode, UGP1.UomEntry, T0.Filler, T0.ToWhsCode, T0.DocNum, T0.Comments, T0.DocDate, T0.U_JobCard, T0.U_SerialNo, OBTL.MessageID, OIVL.BASE_REF, OIVL.TransType, OIVL.ItemCode, OIVL.DocLineNum, T8.WhsCode, T8.BinCode, T9.BinCode, Case when T1.InvQty <> T1.Quantity and OWHS.BinActivat ='Y' then (OBTL.Quantity/UGP1.BaseQty) when T1.InvQty <> T1.Quantity and OWHS.BinActivat ='N' then T1.Quantity/UGP1.BaseQty else (Select Sum(A.Quantity) from OBTL A where A.MessageID = OIVL.MessageID and A.BinAbs = T8.AbsEntry) end as "New Quantity 2", Case when T1.InvQty <> T1.Quantity and OWHS.BinActivat ='Y' then (OBTL.Quantity/UGP1.BaseQty) when T1.InvQty <> T1.Quantity and OWHS.BinActivat ='N' then T1.Quantity/UGP1.BaseQty else (Select Sum(A.Quantity) from OBTL A where A.MessageID = OIVL.MessageID and A.BinAbs = T9.AbsEntry) end as "New Quantity 3", (select DocNum from OWTQ A0 inner join WTQ1 A1 on A0.DOcentry=A1.Docentry where A1.TrgetEntry=T0.DocEntry and A1.Itemcode=T1.ItemCode and A1.TargetType=67 and A1.LineNum=T1.LineNum) [ITR NO], (select U_Mnfr from OITM A0 where A0.ItemCode=T1.ItemCode) [Manufacturer], (select U_brandname from OITM A0 where A0.ItemCode=T1.ItemCode) [Brand], (select whsname from OWHS where Whscode=T0.[Filler]) [From Whs Name], (select whsname from OWHS where Whscode=T0.[ToWhsCode]) [To Whs Name], (select U_Name from OUSR where USERID=T0.UserSign) [User], (OBTL.Quantity/UGP1.BaseQty)"New Quantityxxx", Case when (Select STUFF((Select ','+CAST(SK2.DistNumber As Varchar) from (select ROW_NUMBER() over (partition by DocLine,DocType,DocEntry order by Logentry desc) [Row],ItemCode,DocType,LogEntry,Docentry,ManagedBy from OITL where(DocEntry = T0.Docentry And DocType = T0.objType) ) SK inner join ITL1 SK1 on SK.LogEntry=SK1.LogEntry inner join OSRN SK2 on Sk2.ItemCode=Sk1.ItemCode and SK1.SysNumber=SK2.SysNumber and Sk2.ObjType=SK.ManagedBy where(SK.Row = 1) for xml path('')),1,1,'') ) is not null then '('+(Select STUFF((Select ','+CAST(SK2.DistNumber As Varchar) from (select ROW_NUMBER() over (partition by DocLine,DocType,DocEntry order by Logentry desc) [Row],ItemCode,DocType,LogEntry,Docentry,ManagedBy from OITL where(DocEntry = T0.Docentry And DocType = T0.objType) ) SK inner join ITL1 SK1 on SK.LogEntry=SK1.LogEntry inner join OSRN SK2 on Sk2.ItemCode=Sk1.ItemCode and T1.ItemCode=SK.ItemCode and SK1.SysNumber=SK2.SysNumber and Sk2.ObjType=SK.ManagedBy where(SK.Row = 1) for xml path('')),1,1,'') )+')' end [SerialNo.], (Select U_Name from OUSR where UserID=T0.Usersign) [User1] FROM OWTR T0 INNER JOIN WTR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join OWHS on T1.WhsCode = OWHS.WhsCode left join OITM on T1.ItemCode = OITM.ItemCode left join OUOM on OUOM.UomEntry = OITM.UgpEntry --and T1.UomCode = OUOM.UomCode left join UGP1 on OUOM.UomEntry = UGP1.UomEntry and OITM.UgpEntry = UGP1.UgpEntry Left Join OIVL on OIVL.BASE_REF = T0.DocNum and OIVL.ItemCode = T1.ItemCode and OIVL.DocLineNum = T1.LineNum and OIVL.TransType = T1.ObjType and OWHS.BinActivat = 'Y' left join OBTL ON OIVL.MessageID = OBTL.MessageID and OBTL.Quantity>0 and OWHS.BinActivat = 'Y'and OBTL.BinAbs is not null left JOIN OBIN T8 ON T8.AbsEntry = OBTL.BinAbs and T8.WhsCode = T1.WhsCode and OWHS.BinActivat = 'Y' and T8.Bincode is not null and T8.WhsCode is not null left JOIN OBIN T9 ON T9.AbsEntry = OBTL.BinAbs and T9.WhsCode = T1.FromWhsCod and OWHS.BinActivat = 'Y' and T9.Bincode is not null and T9.WhsCode is not null left JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] LEFT JOIN OCPR T3 ON T0.[CntctCode] = T3.[CntctCode] Inner Join DLN12 T5 ON T0.[DocEntry] = T5.[DocEntry] order by T0.DocEntry

Regards,

Mohamed