有些时候在项目上，顾问难免会需要利用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.DocQty0 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 通用脚本示例 CREATE VIEW [dbo].[IPS_StockTransfersBinFlow] AS SELECT T0."DocEntry", T1."LineNum", T0."TaxDate", T1."ItemCode", T1."Dscription", T1."FromWhsCod", T6."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 "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 ) T5 ON T1."DocEntry"=T5."ApplyEntry" AND T1."LineNum"=T5."ApplyLine" AND T4."MdAbsEntry"=T5."MdAbsEntry" LEFT JOIN "OWHS" T6 ON T1."FromWhsCod"=T6."WhsCode" UNION ALL SELECT T0."DocEntry", T1."LineNum", T0."TaxDate", 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' AND T8."ManSerNum"'Y' LEFT JOIN "OWHS" T9 on T1."FromWhsCod"=T9."WhsCode" LEFT JOIN "OWHS" T10 on T1."WhsCode"=T10."WhsCode"