# [易飞]展BOM数据结构之函数版本

622人阅读 评论(0)


-- =============================================
-- Author: <David Gong>
-- Create date: <2017-10-23>
-- Description: <展BOM>
-- =============================================
ALTER  Function [dbo].[GetBomCost] (@ITEMNO VARCHAR(80))
returns  @Jason table
(

)

BEGIN

--BOM展阶
--@ITEMNO：查询品号
--返回:元件品号V1001，主件品号V1002，阶码V1004，阶层次V1005，尾阶标志V1006，展开标志V1007，组成用量V1011，底数V1012，损耗率%V1013,纳入标准成本 V1014,取替代顺序 V1015
DECLARE @ITEM_CHILD VARCHAR(200), @ITEM_EXPAND VARCHAR(200), @ITEM_LEVELNO INT, @COUNT INT
DECLARE @BATCH_NUMBER NUMERIC(18,6)
DECLARE @ITEM_PARENT VARCHAR(200)
DECLARE @V1011 NUMERIC(18,6) ,@V1012 NUMERIC(18,6),@V1013 NUMERIC(18,6),@V1014 VARCHAR(200)

DECLARE  @VIEW1 TABLE
(V1001 VARCHAR(200),
V1002 VARCHAR(200),
V1004 VARCHAR(200) 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,
V1014 VARCHAR(200) DEFAULT '',
V1015 CHAR(1) DEFAULT ''
)

SELECT @ITEM_LEVELNO = 1

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

DECLARE BOMEXPAND CURSOR FOR SELECT V1001, V1004, V1005, V1011/V1012,V1014,V1002 FROM @VIEW1 WHERE V1007 = '0' FOR UPDATE

OPEN BOMEXPAND  /* 打开游标 */
FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER,@V1014,@ITEM_PARENT
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,V1014)
SELECT MD003, @ITEM_CHILD, @ITEM_EXPAND + '.' + MD002, @ITEM_LEVELNO + 1, MD006 * @BATCH_NUMBER, MD007*MC004, MD008,
MD014=(case when @V1014='N' then 'N' else MD014 end)
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,@V1014,@ITEM_PARENT /* 在循环体内将读取其余行数据 */
END
CLOSE BOMEXPAND   /* 关闭游标 */
DEALLOCATE BOMEXPAND   /* 删除游标 */
----声明表变量------

declare
@阶层 VARCHAR(80),
@主件品号 VARCHAR(80),
@元件品号  VARCHAR(80),
@品号属性  CHAR(12),
@品名 CHAR(200) ,
@规格 CHAR(200) ,
@单位 CHAR(10),
@组成用量 NUMERIC(18,6) ,
@底数 NUMERIC(18,6) ,
@损耗率 NUMERIC(18,6) ,
@累计组成用量 NUMERIC(18,6) ,
@纳入标准成本 CHAR(1) ,
@取替代顺序 NCHAR(1)

---------遍历BOM，增加替代料，写入表变量--------

DECLARE BOMEXPAND_1 CURSOR FOR  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 累计组成用量,V1014 AS 纳入标准成本,V1015 AS 取替代顺序  FROM @VIEW1 INNER JOIN  INVMB ON V1001=MB001

OPEN BOMEXPAND_1  /* 打开游标 */
FETCH NEXT FROM BOMEXPAND_1 INTO
@阶层 ,
@主件品号 ,
@元件品号 ,
@品号属性 ,
@品名 ,
@规格 ,
@单位 ,
@组成用量  ,
@底数 ,
@损耗率  ,
@累计组成用量 ,
@纳入标准成本  ,
@取替代顺序
WHILE @@FETCH_STATUS = 0   /* 用WHILE循环控制游标活动*/
BEGIN

INSERT INTO @Jason(阶层,主件品号,元件品号,品号属性,品名,规格,单位,组成用量,底数,损耗率,累计组成用量,纳入标准成本 ,取替代顺序)
values(@阶层,@主件品号,@元件品号,@品号属性,@品名,@规格,@单位,@组成用量,@底数,@损耗率,@累计组成用量,@纳入标准成本,@取替代顺序)

--增加替代料
IF exists(SELECT * FROM BOMMA LEFT JOIN BOMMB ON MA001=MB001 AND MA002=MB002 WHERE MA001=@元件品号  AND MA002=@主件品号)
BEGIN
INSERT INTO @Jason(阶层,主件品号,元件品号,品号属性,品名,规格,单位,组成用量,底数,损耗率,累计组成用量,纳入标准成本 ,取替代顺序)
SELECT @阶层,@主件品号,BOMMB.MB004,case when INVMB.MB025='P' THEN '采购件'
when INVMB.MB025='M' THEN '自制件'
when INVMB.MB025='S' THEN '委外加工件'
when INVMB.MB025='Y' THEN '虚设件'
else '配置件' END ,INVMB.MB002,INVMB.MB003,INVMB.MB004,@组成用量,@底数,@损耗率,@累计组成用量 ,@纳入标准成本,BOMMB.MB009
from BOMMA LEFT JOIN BOMMB ON MA001=BOMMB.MB001 AND MA002=BOMMB.MB002
inner JOIN INVMB ON BOMMB.MB004=INVMB.MB001
WHERE MA001=@元件品号  AND MA002=@主件品号

END

FETCH NEXT FROM BOMEXPAND_1 INTO
@阶层 ,
@主件品号 ,
@元件品号 ,
@品号属性 ,
@品名 ,
@规格 ,
@单位 ,
@组成用量  ,
@底数 ,
@损耗率  ,
@累计组成用量 ,
@纳入标准成本  ,
@取替代顺序
/* 在循环体内将读取其余行数据 */
END
CLOSE BOMEXPAND_1   /* 关闭游标 */
DEALLOCATE BOMEXPAND_1   /* 删除游标 */

--select  阶层 ,@ITEMNO as 主件品号,主件品号 as 上阶品号 , 元件品号  , dbo.GetItemCost(元件品号) as 单位成本, 品名 ,规格 , 单位 ,组成用量 , 底数 , 损耗率 , 累计组成用量 ,纳入标准成本 ,取替代顺序 from @Jason where 品号属性='采购件'
delete @Jason
where 品号属性<>'采购件'

return

END


个人资料
等级：
访问量： 257万+
积分： 3万+
排名： 133
博客专栏
 ERP-易飞 文章：78篇 阅读：197326 Ext.Net 文章：43篇 阅读：145363 Sql Server 文章：125篇 阅读：182057 活动目录 文章：8篇 阅读：25296 DevExpress 文章：18篇 阅读：90751 ERP管理 文章：27篇 阅读：55499 MsChart 文章：11篇 阅读：28364
最新评论
文章存档