// 一個算物料計劃的存儲過程
CREATE FUNCTION DBO.F_BomList
(
@PPartNo NVARCHAR(20),
@PQuantity INT = 1,
@PType BIT = 0
)
RETURNS @BomList TABLE(
PartNo NVARCHAR(20),
FUsage FLOAT,
Raw_ID INT)
AS
BEGIN
DECLARE @BomListTmp TABLE(
CPartNo NVARCHAR(20),
PartNo NVARCHAR(20),
FUsage FLOAT,
_Level INT)
--@Lev是層的定義
DECLARE @LLevel INT, @LSample INT
SET @LLevel = 1
SELECT @LSample = ISNULL(Sample, 1) FROM Bom WHERE CPartNo = @PPartNo
IF @LSample = 0
SET @LSample = 1
--先取@PPartNo的第一層數據到#Temp
INSERT INTO @BomListTmp (CPartNo, PartNo, FUsage, _Level)
SELECT CPartNo, CPartNo, 1, @LLevel FROM Bom WHERE CPartNo = @PPartNo
--以循環方式取得所有的下層元件清單到#Temp
WHILE EXISTS(SELECT CPartNo FROM @BomListTmp WHERE _Level = @LLevel)
BEGIN
SET @LLevel = @LLevel + 1
INSERT INTO @BomListTmp (CPartNo, PartNo, FUsage, _Level)
SELECT a.CPartNo, a.PartNo,
CASE WHEN @LLevel = 2 THEN a.FUsage * b.FUsage / @LSample ELSE a.FUsage * b.FUsage END, @LLevel
FROM BomDtl a, (SELECT PartNo, FUsage FROM @BomListTmp WHERE _Level + 1= @LLevel) b
WHERE a.CPartNo = b.PartNo
END
-- 計算總量
INSERT INTO @BomList(PartNo, FUsage, Raw_ID)
SELECT PartNo, SUM(FUsage) * @PQuantity FUsage, 0 FROM @BomListTmp WHERE _Level <> 1
GROUP BY PartNo
IF @PType = 0
-- 標識非原材料
UPDATE @BomList SET Raw_ID = 1
WHERE PartNo IN (SELECT DISTINCT CPartNo FROM BomDtl)
RETURN
End