求SAP B1按Warehouse Code统计Total AR Invoice的查询语句

以下是符合需求的查询语句，通过关联发票表头和行项目表实现按仓库代码汇总AR发票总额：

SELECT 
  T0.WhsCode AS "Warehouse Code",
  SUM(T1.DocTotal) AS "Total AR Invoice"
FROM 
  INV1 T0
INNER JOIN 
  OINV T1 ON T0.DocEntry = T1.DocEntry
GROUP BY 
  T0.WhsCode

语句说明 #

• INV1是SAP B1中AR发票的行项目表，WhsCode字段存储仓库代码
• OINV是AR发票的表头表，DocTotal字段对应单张发票的总金额
• 通过DocEntry字段关联表头与行表，确保行项目和对应发票的关联关系正确
• SUM(T1.DocTotal)用于按仓库汇总所有关联发票的总金额
• GROUP BY T0.WhsCode指定按仓库代码分组统计

可选调整 #

如果需要过滤特定状态的发票（比如仅统计已结清的发票），可以添加WHERE子句：

SELECT 
  T0.WhsCode AS "Warehouse Code",
  SUM(T1.DocTotal) AS "Total AR Invoice"
FROM 
  INV1 T0
INNER JOIN 
  OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE
  T1.DocStatus = 'C' -- 'C'代表已结清状态，可根据实际需求调整
GROUP BY 
  T0.WhsCode

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