有些时候在项目上，顾问难免会需要利用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
  

通用脚本示例

  
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
            ) 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" 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"