SAP B1 库存转储单查询出库位

有些时候在项目上，顾问难免会需要利用SAPB1强大的功能之一（格式化搜索）进行实现一些功能，如：在库存转储单上查询出库位信息，如下图所示：

select * from (

---无批次
SELECT

   T0.DocEntry,
   T0.U_TransType,
   T0.TaxDate,
   T31.DisplayName as U_EmpCode,
   T1.ItemCode,
   T1.Dscription,
   T1.FromWhsCod,
   T8.WhsName as FromWhsName,
   T4.BinCode as FromBinCode,
   T1.WhsCode,
   T3.WhsName,
   T5.BinCode,
   T1.Quantity
   FROM OWTR  T0

inner JOIN WTR1 T1 ON T1.DocEntry=T0.DocEntry
LEFT JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
LEFT JOIN IPS_ERP.[dbo].[Sys_User] T31 ON T31.LoginName=T0.U_EmpCode
inner JOIN OWHS T3 ON T1.WhsCode=T3.WhsCode
INNER join (
select T1.MdAbsEntry,T0.ApplyEntry,T0.ApplyLine,T3.BinCode from OITL T0
left join ITL1 T1 on T0.LogEntry=T1.LogEntry AND T0.DocQty<0
LEFT JOIN OBTL T2 ON T1.LogEntry=T2.ITLEntry AND T1.MdAbsEntry=T2.SnBMDAbs
LEFT JOIN OBIN t3 on t2.BinAbs=t3.AbsEntry
WHERE T0.ApplyType=67
) T4 on T1.DocEntry=T4.ApplyEntry AND T1.LineNum=T4.ApplyLine
INNER join(
select T1.MdAbsEntry,T0.ApplyEntry,T0.ApplyLine,T3.BinCode from OITL T0
left join ITL1 T1 on T0.LogEntry=T1.LogEntry AND T0.DocQty>0
LEFT JOIN OBTL T2 ON T1.LogEntry=T2.ITLEntry AND T1.MdAbsEntry=T2.SnBMDAbs
LEFT JOIN OBIN t3 on t2.BinAbs=t3.AbsEntry
WHERE T0.ApplyType=67
) T5 ON T1.DocEntry=T5.ApplyEntry AND T1.LineNum=T5.ApplyLine and t4.MdAbsEntry=t5.MdAbsEntry
LEFT JOIN OWHS T8 ON T1.FromWhsCod=T8.WhsCode
WHERE
(T1.WhsCode='M01' or T1.FromWhsCod='M01')
--and T0.DocEntry=3583

UNION ALL
-----有批次
select

   T0.DocEntry,
   T0.U_TransType,
   T0.TaxDate,
   T31.DisplayName AS U_EmpCode,
   T1.ItemCode,
   T1.Dscription,
   T1.FromWhsCod,
   T9.WhsName as FromWhsName,
   T4.BinCode,
   T1.WhsCode,
   T10.WhsName,
   T7.BinCode,
   T1.Quantity

FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry=T1.DocEntry
INNER JOIN OIVL T2 ON T1.ObjType=T2.TransType AND T1.DocEntry=T2.BASE_REF AND T1.LineNum=T2.DocLineNum AND T2.OutQty>0
LEFT JOIN OBTL T3 ON T2.MessageID=T3.MessageID
LEFT JOIN OBIN T4 ON T3.BinAbs=T4.AbsEntry
INNER JOIN OIVL T5 ON T1.ObjType=T5.TransType AND T1.DocEntry=T5.BASE_REF AND T1.LineNum=T5.DocLineNum AND T5.InQty>0
LEFT JOIN OBTL T6 ON T5.MessageID=T6.MessageID
LEFT JOIN OBIN T7 ON T6.BinAbs=T7.AbsEntry
INNER JOIN OITM T8 ON T1.ItemCode=T8.ItemCode AND T8.ManBtchNum<>'Y'
LEFT JOIN IPS_ERP.[dbo].[Sys_User] T31 ON T31.LoginName=T0.U_EmpCode
LEFT JOIN OWHS T9 on T1.FromWhsCod=T9.WhsCode
LEFT JOIN OWHS T10 on T1.WhsCode=T10.WhsCode
where (T1.WhsCode='M01' or T1.FromWhsCod='M01')
--and T0.DocEntry=3583
) T00
where 1=1