易飞SQL_BOM展阶

DECLARE @TOP VARCHAR(80)



SET @TOP='101038240001'



DECLARE @LEVEL INT



DECLARE @TMP1 TABLE(TOPMD001 VARCHAR(80),MD001 VARCHAR(80),MD003 VARCHAR(80),MD002 VARCHAR(8000),LEVEL INT,



MB004 CHAR(4),MD004 CHAR(4),MD005 CHAR(4),MD006 Numeric(16,6),MD007 Numeric(4,0),MD008 Numeric(5,4),



MD009 CHAR(4),MD011 CHAR(8),MD012 CHAR(8),MD017 CHAR(1),



MB002 CHAR(60),MB003 CHAR(60),MB025 CHAR(1),FLAG CHAR(2))



-----写入0数据



INSERT INTO @TMP1



SELECT @TOP,'',@TOP,'',0,



MB004,MC002, MB072,MC004,1,0,



'***','','','1',



MB002,MB003,MB025,'0'



FROM BOMMC



INNER JOIN INVMB ON MB001=MC001



WHERE MB001=@TOP



-----写入1阶数



SELECT @LEVEL=1



INSERT INTO @TMP1



SELECT @TOP,MD001,MD003,MD002,@LEVEL,



MB004,MD004,MB072,MD006,MD007,MD008,



MD009,MD011,MD012,MD017,



MB002,MB003,MB025,'0'



FROM BOMMD



INNER JOIN INVMB ON MB001=MD003



WHERE MD001=@TOP



----循环写入下阶数量



WHILE EXISTS(SELECT FROM @TMP1 WHERE MD003 IS NOT NULL AND LEVEL=@LEVEL)



BEGIN



    INSERT INTO @TMP1



    SELECT @TOP,A.MD001,A.MD003,B.MD002+A.MD002,@LEVEL+1,



     IB.MB004,A.MD004,IB.MB072,A.MD006,A.MD007,A.MD008,



         A.MD009,A.MD011,A.MD012,A.MD017,



         IB.MB002,IB.MB003,IB.MB025,'0'



    FROM BOMMD A,@TMP1 B,INVMB IB



    WHERE A.MD001=B.MD003 AND B.LEVEL=@LEVEL AND     IB.MB001=A.MD003



    SET @LEVEL=@LEVEL+1



END

----写入取替代料数据



INSERT INTO @TMP1



SELECT @TOP,A.MD001,B.MB004,A.MD002,A.LEVEL,



     IB.MB004,A.MD004,IB.MB072,B.MB005,1,A.MD008,



         A.MD009,B.MB006,B.MB006,A.MD017,



         IB.MB002,IB.MB003,IB.MB025,B.MB003



FROM @TMP1 A



INNER JOIN BOMMB B ON B.MB001+B.MB002=A.MD003+A.MD001



INNER JOIN INVMB IB ON IB.MB001=B.MB004



----读取数据生成BOM树型结构



SELECT LEVEL AS [阶次码],RTRIM(replace(SPACE(LEVEL),' ','-')) AS [树枝],



CASE A.FLAG WHEN '0' THEN '0.主料' WHEN '3' THEN '3.替代料件' WHEN '2' THEN '2.替换料件' END AS [取替代],



RTRIM(MD003) AS [元件品号],RTRIM(MB002) AS [品名],RTRIM(MB003) AS [规格],



RTRIM(CASE MB025 WHEN 'M' then 'M.自制' WHEN 'P' then 'P.采购' WHEN 'S' then 'S.委外加工' WHEN 'Y' then 'Y.虚设' WHEN 'C' THEN 'C.配置' END) AS [品号属性],



MB004 AS [库存单位],MD004 AS [BOM单位],MD005 AS [小单位],CAST(MD006 AS FLOAT) AS [组成用量],MD007 AS [底数],CAST(MD008 AS FLOAT) AS [损耗率],--CAST(UDF51 AS FLOAT) AS [有效长度],CAST(UDF52 AS FLOAT) AS [只数],



MD009 AS [工艺],MW002 AS [工艺名称],MD011 AS [生效日期],MD012 AS [失效日期],



CASE MD017 WHEN '1' THEN '1.直接材料' WHEN '2' THEN '2.间接材料' WHEN '3' THEN '3.供应商供' WHEN '4' THEN '4.不发' WHEN '5' THEN '5.客户供料' END AS [材料类型]



FROM @TMP1 AS A



LEFT JOIN CMSMW ON MW001=MD009

ORDER BY MD002,A.FLAG

运行效果

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满