SAP B1-报表-库存明细表：批次、序列、库位 [HANA &

关键词：SAPBusiness One,SAP B1,SAPB1,SBO,库存报表,库存明细表，库位报表，批次报表，序列号报表，存货报表

SAP B1 库存明细表【HANA & SQL 通用】，包含：

    批次+库位、批次无库位；

    序列号+库位、序列号无库存；

    无批次无序列+库位、无批次无序列无库位；

查询条件如下（不输入即为所有）：

查询结果如下图所示：

代码如下，将该代码放在SAP B1的查询管理器中执行即可。

/*---SAP B1 库存明细表[HANA & SQL 通用]---*/

/*有批次有库位*/

SELECT T0."ItemCode", T4."ItemName" AS "物料名称", N'批次' AS "类型", T2."DistNumber" "批号/序列号"

,T0."OnHandQty" AS "库存", T4."InvntryUom" AS "库存单位", T0."WhsCode", T3."WhsName" AS "仓库名称"

,T1."Attr1Val" AS "区域", T1."BinCode" AS "库位"

FROM OBBQ T0

INNER JOIN OBIN T1 ON T0."BinAbs"=T1."AbsEntry"

INNER JOIN OBTN T2 ON T0."SnBMDAbs"=T2."AbsEntry"

INNER JOIN OWHS T3 ON T3."WhsCode"=T0."WhsCode"

INNER JOIN OITM T4 ON T4."ItemCode"=T0."ItemCode"

WHERE T3."WhsName" LIKE '%%[%0]%%' AND T1."BinCode" LIKE '%%[%2]%%'

AND T0."ItemCode" LIKE '%%[%3]%%' AND T2."DistNumber" LIKE '%%[%4]%%' AND T0."OnHandQty">0

/*有序列号有库位*/

UNION ALL

SELECT T0."ItemCode", T4."ItemName" AS "物料名称", N'序列' AS "类型", T2."DistNumber" AS "批号/序列号"

,T0."OnHandQty" AS "库存", T4."InvntryUom" AS "库存单位", T0."WhsCode", T3."WhsName" AS "仓库名称"

,T1."Attr1Val" AS "区域", T1."BinCode" AS "库位"

FROM OSBQ T0

INNER JOIN OBIN T1 ON T0."BinAbs"=T1."AbsEntry"

INNER JOIN OSRN T2 ON T0."SnBMDAbs"=T2."AbsEntry"

INNER JOIN OWHS T3 ON T3."WhsCode"=T0."WhsCode"

INNER JOIN OITM T4 ON T4."ItemCode"=T0."ItemCode"

WHERE T3."WhsName" LIKE '%%[%0]%%' AND T1."BinCode" LIKE '%%[%2]%%'

AND T0."ItemCode" LIKE '%%[%3]%%' AND T2."DistNumber" LIKE '%%[%4]%%' AND T0."OnHandQty">0

/*有批次 无库位*/

UNION ALL

SELECT T0."ItemCode", T2."ItemName", N'批次' AS "类型", T1."DistNumber", T0."Quantity" AS "库存"

,T2."InvntryUom", T0."WhsCode", T3."WhsName", '无' AS "区域", '无' AS "库位"

FROM OBTQ T0

INNER JOIN OBTN T1 ON T0."SysNumber"=T1."SysNumber" AND T0."ItemCode"=T1."ItemCode"

INNER JOIN OITM T2 ON T0."ItemCode"=T2."ItemCode"

INNER JOIN OWHS T3 ON T0."WhsCode"=T3."WhsCode" AND T3."BinActivat"='N'

WHERE T3."WhsName" LIKE '%%[%0]%%' AND T0."ItemCode" LIKE '%%[%3]%%'

AND T1."DistNumber" LIKE '%%[%4]%%' AND T0."Quantity">0

/*有序列号 无库位*/

UNION ALL

SELECT T0."ItemCode", T2."ItemName", N'序列' AS "类型", T1."DistNumber", T0."Quantity" AS "库存"

,T2."InvntryUom", T0."WhsCode", T3."WhsName", '无' AS "区域", '无' AS "库位"

FROM OSRQ T0

INNER JOIN OSRN T1 ON T0."SysNumber"=T1."SysNumber" AND T0."ItemCode"=T1."ItemCode"

INNER JOIN OITM T2 ON T0."ItemCode"=T2."ItemCode"

INNER JOIN OWHS T3 ON T0."WhsCode"=T3."WhsCode" AND T3."BinActivat"='N'

WHERE T3."WhsName" LIKE '%%[%0]%%' AND T0."ItemCode" LIKE '%%[%3]%%'

AND T1."DistNumber" LIKE '%%[%4]%%' AND T0."Quantity">0

/*无批次/无序列  有库位*/

UNION ALL

SELECT T0."ItemCode", T2."ItemName", N'无' AS "类型", '无', T0."OnHandQty" AS "库存", T2."InvntryUom"

,T0."WhsCode", T3."WhsName" AS "仓库名称", T1."Attr1Val" AS "区域", T1."BinCode" AS "库位"

FROM OIBQ T0

INNER JOIN OBIN T1 ON T0."BinAbs"=T1."AbsEntry"

INNER JOIN OITM T2 ON T0."ItemCode"=T2."ItemCode" AND T2."ManBtchNum"='N'

AND T2."ManSerNum"='N'

INNER JOIN OITB T4 ON T4."ItmsGrpCod"=T2."ItmsGrpCod"

INNER JOIN OWHS T3 ON T3."WhsCode"=T0."WhsCode"

WHERE T2."ItemCode" LIKE '%%[%3]%%' AND T3."WhsName" LIKE '%%[%0]%%' AND T0."OnHandQty">0

/*无批次/无序列   无库位*/

UNION ALL

SELECT T0."ItemCode", T1."ItemName", N'无' AS "类型", '无', T0."OnHand" AS "库存", T1."InvntryUom"

,T0."WhsCode", T3."WhsName", '无 ' AS "区域", '无' AS "库位"

FROM OITW T0

INNER JOIN OITM T1 ON T0."ItemCode"=T1."ItemCode" AND T1."ManBtchNum"='N'

AND T1."ManSerNum"='N'

INNER JOIN OITB T2 ON T1."ItmsGrpCod"=T2."ItmsGrpCod"

INNER JOIN OWHS T3 ON T3."WhsCode"=T0."WhsCode" AND T3."BinActivat"='N'

WHERE T0."ItemCode" LIKE '%%[%3]%%' AND T3."WhsName" LIKE '%%[%0]%%' AND T0."OnHand">0 ;