期间进销存报表

-- 报表名： 财务进销存报表

--备注：期初余额取值OIVL表

代码示例

DECLARE @Bdate DATETIME
DECLARE @edate DATETIME
SELECT @Bdate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%0]'

SELECT @edate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%1]'

SELECT DISTINCT T0.[ItemCode], T1.[ItemName] AS '物料描述',  T1.[FrgnName] AS'旧物料编码',Ts.qty AS '期初库存', Ts.je AS '期初金额'
	,CASE WHEN TA.qty=0 THEN 0
WHEN TA.qty<>0 THEN TA.JE / TA.qty END AS'本期购进单价（国内）',TA.qty AS '本期购进数量（国内）', TA.JE AS '本期购进金额（国内）', 

CASE WHEN TB.qty=0 THEN 0
WHEN TB.qty<>0 THEN TB.JE / TB.qty END AS'本期购进单价（国外）',

  TB.qty AS '本期购进数量（国外）', TB.JE AS '本期购进金额（国外）',TC.JE AS '本期领用金额'
	, TC.QTY AS '本期领用数量'
	,ISNULL(Ts.je,0)+ ISNULL(TA.JE, 0) + ISNULL(TB.JE, 0) - ISNULL(TC.JE, 0) AS '结存金额'
	, 
 ISNULL(Ts.qty,0) + ISNULL(TA.qty, 0) + ISNULL(TB.qty, 0) - ISNULL(TC.qty, 0) AS '结存数量'
FROM OIVL T0
	INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
			, SUM(ISNULL(T0.[InQty], 0) * T0.[Price] - ISNULL(T0.[OutQty], 0) * T0.[Price]) AS 'je'
		FROM OIVL T0
		WHERE T0.[DocDate] < @Bdate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TS ----库存期初
	ON TS.itemcode = t0.itemcode
		AND TS.loccode = T0.loccode
	LEFT JOIN (
		SELECT T1.[ItemCode], SUM(T1.[Quantity] * T1.[Price]) AS 'JE'
			, SUM(T1.[Quantity]) AS 'qty'
		FROM OPDN T0
			INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
		WHERE T0.[DocDate] >= @Bdate AND T0.[DocDate] <= @Edate 
			AND T2.[CmpPrivate] = 'C'
		GROUP BY T1.[ItemCode]
	) TA  ----本期采购收货(国内)
	ON TA.[ItemCode] = T0.[ItemCode]
	LEFT JOIN (
		SELECT T1.[ItemCode], SUM(T1.[Quantity] * T1.[Price]*T1.[Rate]) AS 'JE'
			, SUM(T1.[Quantity]) AS 'qty'
		FROM OPDN T0
			INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
		WHERE T0.[DocDate] >= @Bdate  AND T0.[DocDate] <= @Edate 
			AND T2.[CmpPrivate] = 'I'
		GROUP BY T1.[ItemCode]
	) TB ----本期采购收货(国外)
	ON TB.[ItemCode] = T0.[ItemCode]
	LEFT JOIN (
		SELECT T1.[ItemCode], SUM(T1.[Quantity] * T1.[StockPrice]) AS 'JE'
			, SUM(T1.[Quantity]) AS 'QTY'
		FROM OIGE T0
			INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry]
		WHERE T1.[BaseType] = '-1'
			AND T0.[DocDate] >= @Bdate  AND T0.[DocDate] <= @Edate 
		GROUP BY T1.[ItemCode]
	) TC ----库存发货
	ON TC.[ItemCode] = T0.[ItemCode]
WHERE T0.[DocDate] <= @Bdate