物料库龄分析报表

代码示例

DECLARE	@DATE DATETIME
SELECT 	@DATE = T.CREATEDATE FROM OIVL T WHERE T.CREATEDATE = '[%0]'

IF(ISNULL(@DATE,'')='')
BEGIN
	SET @DATE=CONVERT(NVARCHAR(10),GETDATE())
END

SELECT
	T0.ITEMCODE,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE>@DATE
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<=@DATE AND CREATEDATE>=@DATE-30
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY30,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-30 AND CREATEDATE>=@DATE-60
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY60,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-60 AND CREATEDATE>=@DATE-90
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY90,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-90 AND CREATEDATE>=@DATE-180
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY180,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-180 AND CREATEDATE>=@DATE-360
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY360,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-360 AND CREATEDATE>=@DATE-540
			GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY540,
	ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-540
			GROUP BY ITEMCODE,LOCCODE),0) AS ENDQTY,
	T0.LOCCODE,T2.ONHAND
INTO	#TEMP_1
FROM	OITM T1 INNER JOIN OIVL T0 ON T1.ITEMCODE = T0.ITEMCODE
			    INNER JOIN OITW T2 ON T2.ITEMCODE = T1.ITEMCODE AND T2.WHSCODE = T0.LOCCODE
WHERE	T1.FROZENFOR = 'N' AND T1.MANSERNUM ='N' AND T1.MANBTCHNUM ='N'
		AND	T2.ONHAND <> 0
GROUP	BY	T0.ITEMCODE,T0.LOCCODE,T2.ONHAND

SELECT	ITEMCODE,
		CASE WHEN (ONHAND-BEGQTY)<=0 THEN ONHAND 
			ELSE BEGQTY END AS BEGQTY,
		CASE WHEN (ONHAND-BEGQTY)<=0 THEN 0	
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30)<=0 THEN (ONHAND-BEGQTY) 
				ELSE BEGQTY30 END END AS BEGQTY30,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60)<=0 THEN (ONHAND-BEGQTY-BEGQTY30) 
				ELSE BEGQTY60 END END AS BEGQTY60,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60) 
				ELSE BEGQTY90 END END AS BEGQTY90,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90) 
				ELSE BEGQTY180 END END AS BEGQTY180,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180) 
				ELSE BEGQTY360 END END AS BEGQTY360,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360) 
				ELSE BEGQTY540 END END AS BEGQTY540,
		CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540)<=0 THEN 0 
			ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540-ENDQTY)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540) 
				ELSE ENDQTY END END AS ENDQTY,
		LOCCODE,ONHAND
INTO	#TEMP_2
FROM	#TEMP_1


SELECT	N'无序列号无批次管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',

		T0.BEGQTY30 AS '0-30(数量)',

		T0.BEGQTY60 AS '31-60(数量)',

		T0.BEGQTY90 AS '61-90(数量)',

		T0.BEGQTY180 AS '91-180(数量)',

		T0.BEGQTY360 AS '181-360(数量)',

		T0.BEGQTY540 AS '361-540(数量)',

		T0.ENDQTY AS '541++(数量)',
		T1.WHSNAME AS '仓库',
		T0.ONHAND AS '现有库存数量'

FROM	#TEMP_2 T0	INNER JOIN OWHS T1 ON T0.LOCCODE = T1.WHSCODE
					INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE



UNION ALL
SELECT	N'序列号管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540(数量)',

		ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++(数量)',
		T1.WHSNAME AS '仓库',
		T4.ONHAND AS '现有库存'
FROM	(
			SELECT T0.ITEMCODE,T0.WHSCODE,T1.INDATE,COUNT(*) AS QTY FROM OSRQ T0
			INNER JOIN OSRN T1 ON T1.ABSENTRY=T0.ABSENTRY
			WHERE ISNULL(T0.QUANTITY,0)>0
			GROUP BY T0.ITEMCODE,T0.WHSCODE,T1.INDATE
		) T0 INNER JOIN OWHS T1 ON T0.WHSCODE = T1.WHSCODE
			 INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
			 INNER JOIN OITW T4 ON T4.ITEMCODE = T0.ITEMCODE AND T4.WHSCODE = T0.WHSCODE


UNION ALL
SELECT	N'批次管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30(金额)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360(数量)',

		ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540(数量)',

		ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++(数量)',
		T1.WHSNAME AS '仓库',
		T4.ONHAND AS '现有库存'
FROM	(
			SELECT T0.ITEMCODE,T0.WHSCODE,T1.INDATE,SUM(ISNULL(T0.QUANTITY,0)) AS QTY FROM OBTQ T0
			INNER JOIN OBTN T1 ON T1.ABSENTRY=T0.ABSENTRY
			WHERE ISNULL(T0.QUANTITY,0)>0
			GROUP BY T0.ITEMCODE,T0.WHSCODE,T1.INDATE
		) T0 INNER JOIN OWHS T1 ON T0.WHSCODE = T1.WHSCODE
			 INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
			 INNER JOIN OITW T4 ON T4.ITEMCODE = T0.ITEMCODE AND T4.WHSCODE = T0.WHSCODE

ORDER BY T0.ITEMCODE

DROP	TABLE #TEMP_1
DROP	TABLE #TEMP_2