konhon

// 一個算物料計劃的存儲過程
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