仓库进销存（含价格）
大约 4 分钟
代码示例
DECLARE @BeginDate DATETIME ,
@EndDate DATETIME ,
@ItmsGrpCod NVARCHAR(30) ,
@WhsCode NVARCHAR(40)
SET @BeginDate = /* FROM ORDR T0 WHERE T0.DocDate >= */ '[%0]'
SET @EndDate = /* FROM ORDR T1 WHERE T1.DocDate <= */ '[%1]'
SET @ItmsGrpCod = /* FROM OITB T2 WHERE T2.ItmsGrpNam = */ '[%2]'
SET @WhsCode = /* FROM OWHS T3 WHERE T3.WhsCode = */ '[%3]'
--直接提取OINM表
SELECT @BeginDate 开始日期 ,
@EndDate 结束日期 ,
T0.ItemCode 物料代码 ,
T0.ItemName 物料名称 ,
OITB.ItmsGrpNam AS '物料组' ,
t2.InvntryUom AS '库存单位' ,
SUM(T0.OpenQty) 期初数量 ,
SUM(T0.MOpenQty) 期初金额 ,
SUM(T0.PurInQty) 采购入库数量 ,
SUM(T0.MPurInQty) 采购入库金额 ,
SUM(T0.SalBackInQty) 销售退货入库数量 ,
SUM(T0.MSalBackInQty) 销售退货入库金额 ,
SUM(T0.ProInQty) 生产入库数量 ,
SUM(T0.MProInQty) 生产入库金额 ,
SUM(T0.ProBackInQty) 生产退料数量 ,
SUM(T0.MProBackInQty) 生产退料金额 ,
SUM(T0.WhsInQty) 库存交易入库数量 ,
SUM(T0.MWhsInQty) 库存交易入库金额 ,
SUM(T0.TrsInQty) 库存转储入库数量 ,
SUM(T0.MTrsInQty) 库存转储入库金额 ,
SUM(qmOpenQty - T0.OpenQty - T0.PurInQty - T0.SalBackInQty
- T0.ProInQty - T0.ProBackInQty - T0.WhsInQty - T0.TrsInQty
+ T0.SalOutQty + PurBackQty + T0.ProOutQty + WhsOutQty + TrsOutQty
+ ElsOutQty) 其它类型入库数量 ,
SUM(qmMOpenQty - T0.MOpenQty - T0.MPurInQty - T0.MSalBackInQty
- T0.MProInQty - T0.MProBackInQty - T0.MWhsInQty - T0.MTrsInQty
+ T0.MSalOutQty + MPurBackQty + T0.MProOutQty + MWhsOutQty
+ MTrsOutQty + MElsOutQty) 其它类型入库金额 ,
SUM(T0.SalOutQty) 销售出库数量 ,
SUM(T0.MSalOutQty) 销售出库金额 ,
SUM(PurBackQty) 采购退货出库数量 ,
SUM(MPurBackQty) 采购退货出库金额 ,
SUM(T0.ProOutQty) 生产发料数量 ,
SUM(T0.MProOutQty) 生产发料金额 ,
SUM(WhsOutQty) 库存发货数量 ,
SUM(MWhsOutQty) 库存发货金额 ,
SUM(TrsOutQty) 库存转储出库数量 ,
SUM(MTrsOutQty) 库存转储出库金额 ,
SUM(ElsOutQty) 其它类型出库数量 ,
SUM(MElsOutQty) 其它类型出库金额 ,
SUM(qmOpenQty) 期末数量 ,
SUM(qmMOpenQty) 期末金额
FROM ( --取期初
SELECT T0.ItemCode ,
T1.ItemName ,
InQty - OutQty OpenQty ,
TransValue AS MOpenQty ,
CONVERT(NUMERIC(19, 6), 0) PurInQty ,
CONVERT(NUMERIC(19, 6), 0) SalBackInQty ,
CONVERT(NUMERIC(19, 6), 0) ProInQty ,
CONVERT(NUMERIC(19, 6), 0) ProBackInQty ,
CONVERT(NUMERIC(19, 6), 0) WhsInQty ,
CONVERT(NUMERIC(19, 6), 0) TrsInQty ,
CONVERT(NUMERIC(19, 6), 0) ElsInQty ,
CONVERT(NUMERIC(19, 6), 0) SalOutQty ,
CONVERT(NUMERIC(19, 6), 0) PurBackQty ,
CONVERT(NUMERIC(19, 6), 0) ProOutQty ,
CONVERT(NUMERIC(19, 6), 0) WhsOutQty ,
CONVERT(NUMERIC(19, 6), 0) TrsOutQty ,
CONVERT(NUMERIC(19, 6), 0) ElsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MPurInQty ,
CONVERT(NUMERIC(19, 6), 0) MSalBackInQty ,
CONVERT(NUMERIC(19, 6), 0) MProInQty ,
CONVERT(NUMERIC(19, 6), 0) MProBackInQty ,
CONVERT(NUMERIC(19, 6), 0) MWhsInQty ,
CONVERT(NUMERIC(19, 6), 0) MTrsInQty ,
CONVERT(NUMERIC(19, 6), 0) MElsInQty ,
CONVERT(NUMERIC(19, 6), 0) MSalOutQty ,
CONVERT(NUMERIC(19, 6), 0) MPurBackQty ,
CONVERT(NUMERIC(19, 6), 0) MProOutQty ,
CONVERT(NUMERIC(19, 6), 0) MWhsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MTrsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MElsOutQty ,
CONVERT(NUMERIC(19, 6), 0) qmOpenQty ,
CONVERT(NUMERIC(19, 6), 0) qmMOpenQty
FROM OINM T0
JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
JOIN OITB t2 ON T1.ItmsGrpCod = t2.ItmsGrpCod
AND ( t2.ItmsGrpNam = @ItmsGrpCod
OR ISNULL(@ItmsGrpCod, N'') = N''
)
WHERE T0.DocDate < @BeginDate
AND ( T0.Warehouse = @WhsCode
OR ISNULL(@WhsCode, N'') = N''
) --取期末
UNION ALL
SELECT T0.ItemCode ,
T1.ItemName ,
CONVERT(NUMERIC(19, 6), 0) OpenQty ,
CONVERT(NUMERIC(19, 6), 0) AS MOpenQty ,
CONVERT(NUMERIC(19, 6), 0) PurInQty ,
CONVERT(NUMERIC(19, 6), 0) SalBackInQty ,
CONVERT(NUMERIC(19, 6), 0) ProInQty ,
CONVERT(NUMERIC(19, 6), 0) ProBackInQty ,
CONVERT(NUMERIC(19, 6), 0) WhsInQty ,
CONVERT(NUMERIC(19, 6), 0) TrsInQty ,
CONVERT(NUMERIC(19, 6), 0) ElsInQty ,
CONVERT(NUMERIC(19, 6), 0) SalOutQty ,
CONVERT(NUMERIC(19, 6), 0) PurBackQty ,
CONVERT(NUMERIC(19, 6), 0) ProOutQty ,
CONVERT(NUMERIC(19, 6), 0) WhsOutQty ,
CONVERT(NUMERIC(19, 6), 0) TrsOutQty ,
CONVERT(NUMERIC(19, 6), 0) ElsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MPurInQty ,
CONVERT(NUMERIC(19, 6), 0) MSalBackInQty ,
CONVERT(NUMERIC(19, 6), 0) MProInQty ,
CONVERT(NUMERIC(19, 6), 0) MProBackInQty ,
CONVERT(NUMERIC(19, 6), 0) MWhsInQty ,
CONVERT(NUMERIC(19, 6), 0) MTrsInQty ,
CONVERT(NUMERIC(19, 6), 0) MElsInQty ,
CONVERT(NUMERIC(19, 6), 0) MSalOutQty ,
CONVERT(NUMERIC(19, 6), 0) MPurBackQty ,
CONVERT(NUMERIC(19, 6), 0) MProOutQty ,
CONVERT(NUMERIC(19, 6), 0) MWhsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MTrsOutQty ,
CONVERT(NUMERIC(19, 6), 0) MElsOutQty ,
InQty - OutQty qmOpenQty ,
TransValue qmMOpenQty
FROM OINM T0
JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
JOIN OITB t2 ON T1.ItmsGrpCod = t2.ItmsGrpCod
AND ( t2.ItmsGrpNam = @ItmsGrpCod
OR ISNULL(@ItmsGrpCod, N'') = N''
)
WHERE T0.DocDate <= @EndDate
AND ( T0.Warehouse = @WhsCode
OR ISNULL(@WhsCode, N'') = N''
) --取本期发生额
UNION ALL
SELECT T0.ItemCode ,
T1.ItemName ,
0 OpenQty ,
0 TransValue ,
( CASE WHEN T0.TransType = 20 THEN 1
ELSE 0
END ) * T0.InQty PurInQty ,
( CASE WHEN T0.TransType = 16 THEN 1
ELSE 0
END ) * T0.InQty SalBackInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj = -1
AND T0.CardCode = '50010101' THEN 1
ELSE 0
END ) * T0.InQty ProInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj IS NULL THEN 1
ELSE 0
END ) * T0.InQty ProBackInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj = -1
AND T0.CardCode <> '50010101' THEN 1
ELSE 0
END ) * T0.InQty WhsInQty ,
( CASE WHEN T0.TransType = 67 THEN 1
ELSE 0
END ) * T0.InQty TrsInQty ,
( CASE WHEN T0.TransType NOT IN ( 20, 16, 59, 67 ) THEN 1
ELSE 0
END ) * T0.InQty ElsInQty ,
( CASE WHEN T0.TransType = 15 THEN 1
ELSE 0
END ) * T0.OutQty SalOutQty ,
( CASE WHEN T0.TransType = 21 THEN 1
ELSE 0
END ) * T0.OutQty PurBackQty ,
( CASE WHEN T0.TransType = 60
AND T0.ApplObj = -1
AND T0.CardCode = '50010101' THEN 1
ELSE 0
END ) * T0.OutQty ProOutQty ,
( CASE WHEN T0.TransType = 60
AND T0.ApplObj = -1
AND T0.CardCode <> '50010101' THEN 1
ELSE 0
END ) * T0.OutQty WhsOutQty ,
( CASE WHEN T0.TransType = 67 THEN 1
ELSE 0
END ) * T0.OutQty TrsOutQty ,
( CASE WHEN T0.TransType NOT IN ( 15, 21, 60, 67 ) THEN 1
ELSE 0
END ) * T0.OutQty ElsOutQty ,
( CASE WHEN T0.TransType = 20
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MPurInQty ,
( CASE WHEN T0.TransType = 16
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MSalBackInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj = -1
AND T0.CardCode = '50010101'
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MProInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj IS NULL
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MProBackInQty ,
( CASE WHEN T0.TransType = 59
AND T0.ApplObj = -1
AND T0.CardCode <> '50010101'
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MWhsInQty ,
( CASE WHEN T0.TransType = 67
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MTrsInQty ,
( CASE WHEN T0.TransType NOT IN ( 20, 16, 59, 67 )
AND T0.InQty > 0 THEN 1
ELSE 0
END ) * T0.TransValue MElsInQty ,
( CASE WHEN T0.TransType = 15
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MSalOutQty ,
( CASE WHEN T0.TransType = 21
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MPurBackQty ,
( CASE WHEN T0.TransType = 60
AND T0.ApplObj = -1
AND T0.CardCode = '50010101'
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MProOutQty ,
( CASE WHEN T0.TransType = 60
AND T0.ApplObj = -1
AND T0.CardCode <> '50010101'
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MWhsOutQty ,
( CASE WHEN T0.TransType = 67
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MTrsOutQty ,
( CASE WHEN T0.TransType NOT IN ( 15, 21, 60, 67 )
AND T0.OutQty > 0 THEN 1
ELSE 0
END ) * -1 * T0.TransValue MElsOutQty ,
0 qmOpenQty ,
0 qmMOpenQty
FROM OINM T0
JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
JOIN OITB t2 ON T1.ItmsGrpCod = t2.ItmsGrpCod
AND ( t2.ItmsGrpNam = @ItmsGrpCod
OR ISNULL(@ItmsGrpCod, N'') = N''
)
WHERE T0.DocDate BETWEEN @BeginDate AND @EndDate
AND ( T0.Warehouse = @WhsCode
OR ISNULL(@WhsCode, N'') = N''
)
) T0
INNER JOIN OITM t2 ON T0.ItemCode = t2.ItemCode
INNER JOIN OITB ON OITB.ItmsGrpCod = t2.ItmsGrpCod --
WHERE ( T0.OpenQty + T0.PurInQty + T0.SalBackInQty + T0.ProInQty
+ T0.ProBackInQty + T0.WhsInQty + T0.TrsInQty + T0.ElsInQty
+ T0.SalOutQty + PurBackQty + T0.ProOutQty + WhsOutQty + TrsOutQty
+ ElsOutQty ) > 0
GROUP BY T0.ItemCode,T0.ItemName,OITB.ItmsGrpNam,t2.InvntryUom