账龄分析存储过程“DMO_TmSp_1010_Inventory_Aging”
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N’DMO_TmSp_1010_Inventory_Aging’
AND type = ‘P’)
DROP PROCEDURE DMO_TmSp_1010_Inventory_Aging
GO
–1010,库龄分析
CREATE PROC [dbo].[DMO_TmSp_1010_Inventory_Aging]
@itemcode nvarchar(20)
AS
BEGIN
–步骤1
SELECT
ItemCode, –物料编码
SUM(OnHand) AS OnHand –库存总数
INTO #TEMP1
FROM
OITW –库存表
GROUP BY
ItemCode
–步骤2
SELECT
TransNum, –台账流水号
DocDate, –入库日期
ItemCode, –物料编码
InQty –入库数量
INTO #TEMP2
FROM
OINM –库存台账表
WHERE
TransType = 20 –只考虑采购收货
ORDER BY
DocDate DESC,
TransNum DESC
–步骤3
SELECT
T1.DocDate, –日期
T1.ItemCode, –物料编码
T0.OnHand, –库存数量
T1.InQty, –入库数量
( –查找日期比我小或者日期相同但是台账编码比我小的入库总计
SELECT
SUM(InQty)
FROM
#TEMP2 J0
WHERE
T1.ItemCode = J0.ItemCode AND
(T1.DocDate < J0.DocDate OR
(T1.DocDate = J0.DocDate AND T1.TransNum <= J0.TransNum))
) AS Total, –倒推库存总计
0 AS Balance –倒推库存余量(先设为0)
INTO #TEMP3
FROM
#TEMP1 T0
JOIN
#TEMP2 T1 ON T0.ItemCode = T1.ItemCode
ORDER BY
T1.ItemCode,
DocDate DESC
UPDATE #TEMP3
SET
Balance = –更新库存余量
CASE WHEN Total <= OnHand
THEN InQty
ELSE
CASE WHEN Total - InQty >= OnHand
THEN 0
ELSE OnHand - (Total - InQty)
END
END
–步骤4
SELECT
ItemCode,
SUM(Balance) AS OnHand
INTO #TEMP4
FROM –30天库龄
#TEMP3
WHERE
DocDate >= DATEADD(DAY,-30,GETDATE())
GROUP BY
ItemCode
SELECT
ItemCode,
SUM(Balance) AS OnHand
INTO #TEMP5
FROM –60天库龄
#TEMP3
WHERE
DocDate >= DATEADD(DAY,-60,GETDATE()) AND
DocDate < DATEADD(DAY,-30,GETDATE())
GROUP BY
ItemCode
SELECT
ItemCode,
SUM(Balance) AS OnHand
INTO #TEMP6
FROM –60天以上库龄
#TEMP3
WHERE
DocDate < DATEADD(DAY,-60,GETDATE())
GROUP BY
ItemCode
SELECT
T0.ItemCode,
T0.OnHand,
ISNULL(T1.OnHand,0) AS ‘0-30′,
ISNULL(T2.OnHand,0) AS ‘31-60′,
ISNULL(T3.OnHand,0) AS ‘61~’
FROM –汇总库龄报表
#TEMP1 T0
LEFT JOIN
#TEMP4 T1 ON T0.ItemCode = T1.ItemCode
LEFT JOIN
#TEMP5 T2 ON T0.ItemCode = T2.ItemCode
LEFT JOIN
#TEMP6 T3 ON T0.ItemCode = T3.ItemCode
WHERE
T0.ItemCode= @itemcode OR @itemcode = ”
–删除临时表
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
DROP TABLE #TEMP4
DROP TABLE #TEMP5
DROP TABLE #TEMP6
END
–EXEC DMO_TmSp_1010_Inventory_Aging ‘D00002′