SAP B1
库龄分析报表是行业内悬而未决的难题,为了得到这个基本的库存分析报表,众多财务经理不得不在系统外手工实现,其实,库龄问题还是有好的解决办法的。<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
一般情况下,库龄问题分为两种情况:
1
. 客户使用批次或者序列号管理
2
. 客户未使用批次或序列号管理
有批次
/序列号
- 批次
/序列号时间
/
思路
有
PO单
- 入库时间
\
/
无批次
期初录入
- ?
\
/
无
PO单
— 库存移动(转储调拨、收发)
- ?
\
库存盘点
- ?
对于第一种情况,非常容易解决,只需要使用批号上的创建时间,写个自定义查询就可以实现。对于这种情况,稍微资深的顾问应该都可以处理,就不在深入讨论。
SELECT T0.ItemCode, T0.BatchNum, T0.ItemName, T0.Quantity, T0.CreateDate
,'0-30'=case when T0.CreateDate >= getdate()-30 then T0.Quantity else 0 end,'31-60'=case when T0.CreateDate >= getdate()-60 and T0.CreateDate < getdate()
-30 then T0.Quantity else 0 end
,'61-90'=case when T0.CreateDate >= getdate()-90 and T0.CreateDate < getdate() -60 then T0.Quantity else 0 end,'91-180'=case when T0.CreateDate >= getdate()-180 and T0.CreateDate < getdate()-90 then T0.Quantity else 0 end,'181-360'=case when T0.CreateDate >= getdate()-360 and T0.CreateDate < getdate()-180 then T0.Quantity else 0 end
,'360++'=case when T0.CreateDate < getdate()-360 then T0.Quantity else 0 end
,T0.CardCode, T0.CardName
FROM OIBT T0 WHERE WhsCode='BJ111' and T0.Quantity <>0
,'61-90'=case when T0.CreateDate >= getdate()-90 and T0.CreateDate < getdate() -60 then T0.Quantity else 0 end,'91-180'=case when T0.CreateDate >= getdate()-180 and T0.CreateDate < getdate()-90 then T0.Quantity else 0 end,'181-360'=case when T0.CreateDate >= getdate()-360 and T0.CreateDate < getdate()-180 then T0.Quantity else 0 end
,'360++'=case when T0.CreateDate < getdate()-360 then T0.Quantity else 0 end
,T0.CardCode, T0.CardName
FROM OIBT T0 WHERE WhsCode='BJ111' and T0.Quantity <>0
对于第二种情况,可能很多顾问都有解决思路,不过由于实现的技术难度较高,业界还没有很好的解决办法。因此,针对这个问题,和大家分享一下我们的解决方案。
账龄分析存储过程
“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′
转载于:https://blog.51cto.com/kanshan/568384