SAP B1 库存明细表:批次、序列、库位【HANA & SQL 通用】

BlenderKou 阅读量:171 2020-01-05 SAP B1 库存明细表:批次、序列、库位【HANA & SQL 通用】 SAPB1 报表

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

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

    批次+库位、批次无库位;

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

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

查询条件如下(不输入即为所有):

报表1.png

查询结果如下图所示:

报表2.png

代码如下,将该代码放在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 ;


扫描二维码,关注我的公众号,第一时间获取文章!

上一篇

下一篇
SAP B1 SQL Server 版的 Fiori 主题

上一篇:

下一篇:SAP B1 SQL Server 版的 Fiori 主题