[易飛]ERP的展BOM程式

展BOM程式一直以來都是ERP二次開發人員 非常迫切的任務

易飛7.05的系統，經過在網上一段時間的尋找資料，結合現有的報表需求。寫了一個預存程序 。

CREATE PROCEDURE [dbo].[SP_BOM_EXPAND] (@ITEMNO VARCHAR(80))
AS  
BEGIN
    --BOM展階
 --@ITEMNO：查詢品號
 --返回:元件品號V1001，主件品號V1002，階碼V1004，階層次V1005，尾階標誌V1006，展開標誌V1007，組成用量V1011，底數V1012，損耗率%V1013
 DECLARE @ITEM_CHILD VARCHAR(80), @ITEM_EXPAND VARCHAR(80), @ITEM_LEVELNO INT, @COUNT INT
 DECLARE @BATCH_NUMBER NUMERIC(18,6)
 CREATE TABLE  #VIEW1 (V1001 VARCHAR(80), V1002 VARCHAR(80), V1004 VARCHAR(80) DEFAULT '', V1005 INT DEFAULT 0, V1006 CHAR(1) DEFAULT '0', V1007 CHAR(1) DEFAULT '0'
 , V1011 NUMERIC(18,6) DEFAULT 0, V1012 NUMERIC(18,6) DEFAULT 0, V1013 NUMERIC(18,6) DEFAULT 0) 

 SELECT @ITEM_LEVELNO = 1

 INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013)
 SELECT MD003, @ITEMNO, MD002, 1, MD006 , MD007*MC004, MD008 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEMNO

 DECLARE BOMEXPAND CURSOR FOR SELECT V1001, V1004, V1005, V1011/V1012 FROM #VIEW1 WHERE V1007 = '0' FOR UPDATE
    OPEN BOMEXPAND  /* 開啟遊標 */
 FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER
 WHILE @@FETCH_STATUS = 0   /* 用WHILE迴圈控制遊標活動*/
 BEGIN
  --SELECT @ITEM_LEVELNO, @ITEM_CHILD
  UPDATE #VIEW1 SET V1007 = '1' WHERE CURRENT OF BOMEXPAND
  SELECT @COUNT = COUNT(*) FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD
  IF @COUNT = 0
   UPDATE #VIEW1 SET V1006 = '1' WHERE CURRENT OF BOMEXPAND
  ELSE
   BEGIN
    INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013)
    SELECT MD003, @ITEM_CHILD, @ITEM_EXPAND + '.' + MD002, @ITEM_LEVELNO + 1, MD006 * @BATCH_NUMBER, MD007*MC004, MD008 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD
   END
  FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER /* 在迴圈體內將讀取其餘行資料 */
 END
 CLOSE BOMEXPAND   /* 關閉遊標 */
 DEALLOCATE BOMEXPAND   /* 刪除遊標 */
   
  SELECT space(V1005*1)+'.'+cast(V1005  as varchar)  as 階層,V1002 as 主件品號,V1001 as 元件品號,case when MB025='P' THEN '採購件'
                                                               when MB025='M' THEN '自製件'
                                                               when MB025='S' THEN '委外加工件'
                                                               when MB025='Y' THEN '虛設件'
                                                               else '配置件'END AS  品號屬性,
         MB002 品名,MB003 規格,MB004 as 單位,V1011 as 組成用量,V1012 as 底數,V1013 as 損耗率,V1011/V1012*(1+V1013) as 累計組成用量  FROM #VIEW1 INNER JOIN  INVMB ON V1001=MB001
 ORDER BY V1004
END
GO

 

效果如下：

系統標準報表

鐵觀音-廠家直銷