如何获取SAP Business One中客户每批次货物的收货数量？

问题背景 #

需制作客户收货报表，提取指定客户的所有收货单并关联批次数据，但现有查询存在以下问题：

• 初始查询中，每批次显示的是整单收货数量，而非对应批次的实际收货数量
• 尝试关联IBT1表后，获取到的是随机交易的数量，数据不准确

初始问题查询语句 #

SELECT T5.[DocNum], T5.[DocDate], T0.[ItemCode], T0.[Dscription], T4.[FrgnName], T4.[U_Customercode], T0.[Quantity], T3.[DISTNUMBER], T3.[Notes]
FROM DBO.OPDN T5 INNER JOIN DBO.PDN1 AS T0 ON T5.DOCENTRY=T0.DOCENTRY 
INNER JOIN
DBO.OILM AS T1 ON T1.DOCENTRY = T0.DOCENTRY AND T0.OBJTYPE = T1.TRANSTYPE AND T0.LINENUM = T1.DOCLINENUM 
INNER JOIN
DBO.ILM1 AS T2 ON T1.MESSAGEID = T2.MESSAGEID 
INNER JOIN
DBO.OBTN AS T3 ON T2.ITEMCODE = T3.ITEMCODE AND T2.SYSNUMBER = T3.SYSNUMBER 
INNER JOIN
DBO.OITM AS T4 ON T2.ITEMCODE = T4.ITEMCODE
WHERE T5.[CardCode] = XXXX AND  T5.[DocDate] >=[%0] AND  T5.[DocDate] <=[%19]

关联IBT1后的错误查询语句 #

SELECT T5.[DocNum], T5.[DocDate], T0.[ItemCode], T0.[Dscription], T4.[FrgnName], T4.[U_Customercode], T6.[Quantity], T3.[DISTNUMBER], T3.[Notes]
FROM DBO.OPDN T5 INNER JOIN DBO.PDN1 AS T0 ON T5.DOCENTRY=T0.DOCENTRY 
INNER JOIN
DBO.OILM AS T1 ON T1.DOCENTRY = T0.DOCENTRY AND T0.OBJTYPE = T1.TRANSTYPE AND T0.LINENUM = T1.DOCLINENUM 
INNER JOIN
DBO.ILM1 AS T2 ON T1.MESSAGEID = T2.MESSAGEID 
INNER JOIN
DBO.OBTN AS T3 ON T2.ITEMCODE = T3.ITEMCODE AND T2.SYSNUMBER = T3.SYSNUMBER 
INNER JOIN
DBO.OITM AS T4 ON T2.ITEMCODE = T4.ITEMCODE
INNER JOIN
DBO.IBT1 AS T6 ON T6.BatchNum = Cast(T3.SYSNUMBER as nvarchar(20))

WHERE T5.[CardCode] = XXXX AND  T5.[DocDate] >=[%0] AND  T5.[DocDate] <=[%19]

修正后的查询语句 #

SELECT 
    T5.[DocNum], 
    T5.[DocDate], 
    T0.[ItemCode], 
    T0.[Dscription], 
    T4.[FrgnName], 
    T4.[U_Customercode], 
    T6.[Quantity] AS BatchQuantity,
    T3.[DISTNUMBER], 
    T3.[Notes]
FROM DBO.OPDN T5 
INNER JOIN DBO.PDN1 AS T0 ON T5.DOCENTRY = T0.DOCENTRY 
INNER JOIN DBO.OILM AS T1 ON T1.DOCENTRY = T0.DOCENTRY 
    AND T0.OBJTYPE = T1.TRANSTYPE 
    AND T0.LINENUM = T1.DOCLINENUM 
INNER JOIN DBO.ILM1 AS T2 ON T1.MESSAGEID = T2.MESSAGEID 
INNER JOIN DBO.OBTN AS T3 ON T2.ITEMCODE = T3.ITEMCODE 
    AND T2.SYSNUMBER = T3.SYSNUMBER 
INNER JOIN DBO.OITM AS T4 ON T2.ITEMCODE = T4.ITEMCODE
INNER JOIN DBO.IBT1 AS T6 ON T6.BaseEntry = T5.DocEntry 
    AND T6.BaseType = T5.ObjType 
    AND T6.ItemCode = T0.ItemCode 
    AND T6.BatchNum = CAST(T3.SysNumber AS NVARCHAR(20))
WHERE 
    T5.[CardCode] = 'XXXX' 
    AND T5.[DocDate] >= [%0] 
    AND T5.[DocDate] <= [%1]

关键修正说明 #

1. IBT1关联条件补全：
   • T6.BaseEntry = T5.DocEntry：关联当前收货单的凭证编号
   • T6.BaseType = T5.ObjType：指定交易类型为收货单（OPDN的ObjType为20）
   • T6.ItemCode = T0.ItemCode：匹配当前行的物料代码
     这三个条件确保只获取当前收货单对应批次的数量，避免关联到其他交易的批次数据
2. 日期参数修正：原查询中[%19]为错误参数，SAP Business One查询参数从[%0]开始，第二个日期参数应为[%1]
3. 字段别名优化：将T6.Quantity命名为BatchQuantity，明确区分整单数量与批次数量

内容的提问来源于stack exchange，提问作者user22257439