如何在库存查询SQL中排除已取消的GRPO（收货采购订单）

要解决已取消GRPO（收货采购订单）仍被计入InQty的问题，需在查询中过滤掉关联到已取消GRPO的库存交易记录。在SAP Business One中，GRPO对应的基础表是OPDN，取消的单据其DocStatus字段值为'C'，而库存交易表OINM中GRPO对应的TransType为20。

以下是调整后的SQL语句，核心修改为在所有库存数量统计的子查询中，排除来自已取消GRPO的记录：

/*select T0."DocDate" from OINM T0 where T0."DocDate">=[%0] and T0."DocDate"<=[%1] */
select DISTINCT T1."ItemCode", T1."ItemName", T2."WhsCode",
(
    Select IFNULL(Sum("InQty"-"OutQty"), 0) 
    from OINM 
    where "DocDate" < [%0] 
      and "ItemCode"=T0."ItemCode" 
      and "Warehouse" = T2."WhsCode"
      -- 排除已取消的GRPO记录
      and not ("TransType" = '20' and exists (
          select 1 from OPDN where "DocEntry" = OINM."BaseEntry" and "DocStatus" = 'C'
      ))
) as "Opening Qty",
(
    select IFNULL(Sum("InQty"), 0) 
    From OINM 
    where "DocDate">=[%0] and "DocDate"<=[%1] 
      and "ItemCode"=T0."ItemCode" 
      and "Warehouse" = T2."WhsCode"
      -- 排除已取消的GRPO记录
      and not ("TransType" = '20' and exists (
          select 1 from OPDN where "DocEntry" = OINM."BaseEntry" and "DocStatus" = 'C'
      ))
) as "Receipt Qty",
(
    select IFNULL(Sum("OutQty"), 0) 
    From OINM 
    where "DocDate">=[%0] and "DocDate"<=[%1] 
      and "ItemCode"=T0."ItemCode" 
      and "Warehouse" = T2."WhsCode" 
      and "TransType"='67'
) as "Issue Qty",
(
    Select IFNULL(Sum("InQty"-"OutQty"), 0) 
    from OINM 
    where "DocDate" <=[%1] 
      and "ItemCode"=T0."ItemCode" 
      and "Warehouse" = T2."WhsCode"
      -- 排除已取消的GRPO记录
      and not ("TransType" = '20' and exists (
          select 1 from OPDN where "DocEntry" = OINM."BaseEntry" and "DocStatus" = 'C'
      ))
) as "Closing Qty"
FROM OINM T0 
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
LEFT JOIN OWHS T2 ON T0."Warehouse" = T2."WhsCode" 
WHERE T2."WhsCode" = '[%2]'
Group by T1."ItemCode", T1."ItemName", T2."WhsCode", T0."ItemCode"
ORDER BY T1."ItemCode"

关键修改说明 #

• TransType='20' 用于标识该库存交易记录来自GRPO
• 通过exists子查询关联OPDN表，检查对应的GRPO单据是否处于取消状态（DocStatus='C'）
• 所有涉及库存数量统计的子查询均添加了过滤条件，确保已取消GRPO的交易不会被计入InQty、期初库存或期末库存统计

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