收发存表
- 格式:xls
- 大小:50.00 KB
- 文档页数:1
收发存报表(按仓库查询)包含字段:物料编号T0.[ItemCode],物料描述T0.[ItemName],物料组T0.[ItmsGrpCod],时间范围,仓库T1.[WhsCode],期初结存=入库inqty-出库outqiy,本期入库inqty,本期出库outqty,期末结存 = 期初+入库-出库(注:OINM库存日记账,OWHS仓库主数据,OITW仓库物品列表,OITB物料组)起初日期DECLARE @DateFrom DATEDECLARE @DateTo DATE/*SELECT FROM [dbo].[OINM] T0*/SET @DateFrom =/* T0.DocDate */'[%1]';SET @DateTo =/* T0.DocDate */'[%2]';SELECT T0.ItemCode[物料编号] ,T0.ItemName[物料描述] ,T5.BeginQty[期初结存] ,SUM(T4.InQty)[本期入库] ,SUM(T4.OutQty)[本期出库] ,T6.EndQty[期末结存],T3.WhsName[仓库]FROM OITM T0LEFT JOIN OITW T1 ON T0.ItemCode = T1.ItemCodeLEFT JOIN OWHS T3 ON T1.WhsCode = T3.WhsCodeLEFT JOIN (SELECT ItemCode ,Warehouse ,SUM(InQty)[InQty],SUM(OutQty)[OutQty]FROM OINM WHERE DocDate >= @DateFrom AND DocDate <= @DateTo GROUP BY ItemCode ,Warehouse ) T4 ON T0.ItemCode = T4.ItemCode AND T1.WhsCode = T4.WarehouseLEFT JOIN (SELECT ItemCode ,Warehouse ,SUM(InQty - OutQty)[BeginQty] FROM OINM WHERE DocDate < @DateFromGROUP BY ItemCode ,Warehouse ) T5 ON T1.ItemCode = T5.ItemCode AND T1.WhsCode = T5.WarehouseLEFT JOIN (SELECT ItemCode ,Warehouse ,SUM(InQty - OutQty)[EndQty]FROM OINM WHERE DocDate <= @DateToGROUP BY ItemCode ,Warehouse ) T6 ON T1.ItemCode = T6.ItemCode AND T1.WhsCode = T6.WarehouseGROUP BY T0.ItemCode ,T0.ItemName ,T3.WhsName,T5.BeginQty ,T6.EndQty。