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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://david.blog.csdn.net/article/details/78317136

在几年前写过存储过程展BOM的存储过程并发布到博客上分享给大家。近期很多用户反馈需要函数版本,因有些连接工具并不支持存储过程。正如大家所知道的存储过程非常灵活,几乎没有任何限制。函数则有些限制比如不允许采用临时表。之前易飞展BOM存储过程采用过临时表,那就改用表变量去处理吧。午饭前改好,还有20分钟动手吧!
考虑到之前版本未考虑替代料问题,函数版也一起解决吧。

单位成本暂未考虑,单位成本各用户自己根据公司规则去获取吧。


-- =============================================      
-- Author: <David Gong>      
-- Create date: <2017-10-23>      
-- Description: <展BOM>      
-- =============================================
ALTER  Function [dbo].[GetBomCost] (@ITEMNO VARCHAR(80))
returns  @Jason table
(
阶层 VARCHAR(80),
主件品号 VARCHAR(80), 
元件品号  VARCHAR(80) DEFAULT '', 
品号属性  CHAR(12),
品名 CHAR(200) DEFAULT '',
规格 CHAR(200) DEFAULT '', 
单位 CHAR(10),
组成用量 NUMERIC(18,6) DEFAULT 0, 
底数 NUMERIC(18,6) DEFAULT 0, 
损耗率 NUMERIC(18,6) DEFAULT 0, 
累计组成用量 NUMERIC(18,6) DEFAULT 0,
纳入标准成本 VARCHAR(80) DEFAULT '',
取替代顺序 NCHAR(100) DEFAULT ''
)

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

测试下:
这里写图片描述

展开阅读全文

如何展BOM

06-18

以下是SQL脚本:rnCREATE TABLE [dbo].[ICBOMChild](rn [FBrNo] [varchar](10) NOT NULL,rn [FEntryID] [int] NOT NULL,rn [FInterID] [int] NOT NULL,rn [FItemID] [int] NOT NULL,rn [FAuxQty] [decimal](28, 10) NOT NULL,rn [FQty] [decimal](28, 10) NOT NULL,rn [FScrap] [decimal](28, 10) NOT NULL,rn [FOperSN] [int] NOT NULL,rn [FOperID] [int] NOT NULL,rn [FMachinePos] [varchar](1000) NULL,rn [FNote] [varchar](1000) NULL,rn [FMaterielType] [int] NOT NULL,rn [FMarshalType] [int] NOT NULL,rn [FPercent] [decimal](28, 10) NOT NULL,rn [FBeginDay] [datetime] NOT NULL,rn [FEndDay] [datetime] NOT NULL,rn [FOffSetDay] [decimal](28, 10) NOT NULL,rn [FBackFlush] [int] NOT NULL,rn [FStockID] [int] NULL,rn [FSPID] [int] NOT NULL,rn [FSupply] [smallint] NOT NULL,rn [FUnitID] [int] NOT NULL,rn [FAuxPropID] [int] NOT NULL,rn [FPDMImportDate] [datetime] NULL,rn [FPositionNo] [nvarchar](4000) NOT NULL,rn [FItemSize] [nvarchar](255) NOT NULL,rn [FItemSuite] [nvarchar](255) NOT NULL,rn [FNote1] [nvarchar](255) NOT NULL,rn [FNote2] [nvarchar](255) NOT NULL,rn [FNote3] [nvarchar](255) NOT NULL,rn [FHasChar] [smallint] NULL,rn [FDetailID] [uniqueidentifier] NOT NULL,rn [FEntryKey] [int] IDENTITY(1,1) NOT NULL,rn [FCostPercentage] [decimal](6, 2) NULL,rn CONSTRAINT [Prm_ICBOMChild] PRIMARY KEY CLUSTERED rn(rn [FInterID] ASC,rn [FEntryID] ASCrn)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]rn) ON [PRIMARY]rnGOrnSET ANSI_PADDING OFFrnGOrn/****** Object: Table [dbo].[ICBOM] Script Date: 06/18/2015 10:52:07 ******/rnSET ANSI_NULLS ONrnGOrnSET QUOTED_IDENTIFIER ONrnGOrnSET ANSI_PADDING ONrnGOrnCREATE TABLE [dbo].[ICBOM](rn [FBrNo] [varchar](10) NOT NULL,rn [FInterID] [int] NOT NULL,rn [FBOMNumber] [varchar](300) NOT NULL,rn [FImpMode] [smallint] NOT NULL,rn [FUseStatus] [int] NULL,rn [FVersion] [varchar](300) NOT NULL,rn [FParentID] [int] NULL,rn [FItemID] [int] NOT NULL,rn [FQty] [decimal](28, 10) NOT NULL,rn [FYield] [decimal](28, 10) NULL,rn [FCheckID] [int] NULL,rn [FCheckDate] [datetime] NULL,rn [FOperatorID] [int] NULL,rn [FEnterTime] [datetime] NOT NULL,rn [FStatus] [smallint] NOT NULL,rn [FCancellation] [bit] NOT NULL,rn [FTranType] [int] NOT NULL,rn [FRoutingID] [int] NOT NULL,rn [FBomType] [int] NOT NULL,rn [FCustID] [int] NOT NULL,rn [FCustItemID] [int] NOT NULL,rn [FAccessories] [int] NOT NULL,rn [FNote] [varchar](300) NOT NULL,rn [FUnitID] [int] NOT NULL,rn [FAUXQTY] [decimal](28, 10) NOT NULL,rn [FCheckerID] [int] NULL,rn [FAudDate] [datetime] NULL,rn [FEcnInterID] [int] NOT NULL,rn [FBeenChecked] [bit] NOT NULL,rn [FForbid] [smallint] NOT NULL,rn [FAuxPropID] [int] NOT NULL,rn [FPDMImportDate] [datetime] NULL,rn [FBOMSkip] [smallint] NOT NULL,rn [FClassTypeID] [int] NULL,rn [FUserID] [int] NULL,rn [FUseDate] [datetime] NULL,rn [FPrintCount] [int] NOT NULL,rn CONSTRAINT [Prm_ICBOM] PRIMARY KEY CLUSTERED rn(rn [FInterID] ASCrn)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]rn) ON [PRIMARY]rnGOrnSET ANSI_PADDING OFFrnGOrn/****** Object: Table [dbo].[t_Item] Script Date: 06/18/2015 10:52:07 ******/rnSET ANSI_NULLS ONrnGOrnSET QUOTED_IDENTIFIER ONrnGOrnSET ANSI_PADDING ONrnGOrnCREATE TABLE [dbo].[t_Item](rn [FItemID] [int] NOT NULL,rn [FItemClassID] [int] NOT NULL,rn [FExternID] [int] NOT NULL,rn [FNumber] [varchar](80) NOT NULL,rn [FParentID] [int] NOT NULL,rn [FLevel] [smallint] NOT NULL,rn [FDetail] [bit] NOT NULL,rn [FName] [varchar](255) NOT NULL,rn [FUnUsed] [bit] NULL,rn [FBrNo] [varchar](10) NOT NULL,rn [FFullNumber] [varchar](80) NOT NULL,rn [FDiff] [bit] NOT NULL,rn [FDeleted] [smallint] NOT NULL,rn [FShortNumber] [varchar](80) NULL,rn [FFullName] [varchar](250) NULL,rn [UUID] [uniqueidentifier] NOT NULL,rn [FGRCommonID] [int] NOT NULL,rn [FSystemType] [int] NOT NULL,rn [FUseSign] [int] NOT NULL,rn [FChkUserID] [int] NULL,rn [FAccessory] [smallint] NOT NULL,rn [FGrControl] [int] NOT NULL,rn [FModifyTime] [timestamp] NOT NULL,rn [FHavePicture] [smallint] NOT NULL,rn以下是我写的SQL代码:rnselect rnt.FNumber,T.FName as '名称',rnI.FBOMNumber as 'BOM单号',rnI.FAudDate as '审核时间',rnI.FAuxQty as '数量',rnI.FBomType as 'BOM单类型',rnI.FCancellation as '作废标志',rnI.FCheckDate as '新建时间',rnI.FCheckerID as '审核人',rnI.FCheckID as '新建人员',rnI.FCustID as '客户代码',rnI.FCustItemID as '客户物料内码',rnI.FEcnInterID as 'ECN内码',rnI.FEnterTime as '最近修改时间',rnI.FForbid as '禁用',rnI.FNote as '备注',rnI.FOperatorID as '最近修改人',rnI.FParentID as 'BOM上级代码',rnI.FQty as '数量',rnI.FStatus as '状态',rnI.FunitID as '单位',rnI.FUseStatus as '使用状态',rnI.FVersion as '版本号',rnI.FYield as '成品率'rnfrom ICBOM I right join ICBOMCHILD ID on I.FInterID=ID.FInterIDrnleft join t_item T ON T.FItemid=ID.FItemidrnwhere I.FBOMNumber is not nullrnunionrnselect rnt.FNumber ,T.FName as '名称',rnI.FBOMNumber as 'BOM单号',rnI.FAudDate as '审核时间',rnI.FAuxQty as '数量',rnI.FBomType as 'BOM单类型',rnI.FCancellation as '作废标志',rnI.FCheckDate as '新建时间',rnI.FCheckerID as '审核人',rnI.FCheckID as '新建人员',rnI.FCustID as '客户代码',rnI.FCustItemID as '客户物料内码',rnI.FEcnInterID as 'ECN内码',rnI.FEnterTime as '最近修改时间',rnI.FForbid as '禁用',rnI.FNote as '备注',rnI.FOperatorID as '最近修改人',rnI.FParentID as 'BOM上级代码',rnI.FQty as '数量',rnI.FStatus as '状态',rnI.FunitID as '单位',rnI.FUseStatus as '使用状态',rnI.FVersion as '版本号',rnI.FYield as '成品率'rnfrom ICBOM I right join ICBOMCHILD ID on I.FInterID=ID.FInterIDrnleft join t_item T ON T.FItemid=I.FItemidrnwhere I.FBOMNumber is not null rn以上代码查询不了 论坛

BOM展阶!

04-11

rn#BOM 单身表字段rnPRODUCT --(主件),rnANO --(序号),rnPARTNO --(元件),rnNAME2 --(元件描述),rnUNIT --(元件单位),rnDIAOLOG --(元件属性,分采购自制),rnSUN --(元件损耗率),rnQTY --(组成用量)) rnrnCREATE PROCEDURE BOM_GENrn@PD CHAR(20),rn@CN INT,rn@I INT,rn@MSG CHAR(100)rnASrnrnBEGINrn IF @I=1 --是否为顶层rn BEGINrn IF (SELECT COUNT(*) rn FROM #BOM WHERE PRODUCT=@PD )=0rn BEGINrn SET @MSG='BOM或配方资料有误,请检查!'rn PRINT @MSG rn ENDrn ELSErn BEGIrn SELECT @I AS BNO,STR(@I)+ANO AS -- BNO用来描述层次 1,2,3... rn CNO,PRODUCT,ANO,PARTNO,NAME2,UNIT,DIAOLOG,SUN,QTYrn INTO RESULT --CNO用来描述逐级层次 101,10101,10201...rn FROM #BOM rn WHERE PRODUCT=@PDrn ENDrn ENDrnDECLARE @C CHAR(20) --@C接受游标赋值CNOrnDECLARE @P CHAR(20)rnDECLARE @L CHAR(2)rnDECLARE @N floatrnDECLARE BOM_SEAR CURSOR FOR rn SELECT CNO,PARTNO,DIAOLOG,QTYrn FROM RESULTrn WHERE BNO=@I and DIAOLOG<>'P' AND DIAOLOG<>'NULL'rnOPEN BOM_SEARrnFETCH NEXT FROM BOM_SEARrnINTO @C,@P,@L,@NrnWHILE @@FETCH_STATUS = 0rn BEGINrn IF (SELECT COUNT(*) rn FROM #BOM WHERE PRODUCT=@P )=0rn BEGINrn SET @MSG=@p+'BOM或配方资料有误,请检查!'rn PRINT @MSG rn ENDrn ELSErn BEGINrn INSERT INTO RESULT rn SELECT @I+1 AS BNO,RTRIM(LTRIM(@C))+STR(@I+1)+ANO AS CNO,PRODUCT,ANO,PARTNO,NAME2,UNIT,DIAOLOG,SUN,QTY ---老报错说数据类型不符rn FROM #BOM WHERE PRODUCT=@Prn ENDrn FETCH NEXT FROM BOM_SEARrn INTO @C,@P,@L,@Nrn ENDrnCLOSE BOM_SEARrnDEALLOCATE BOM_SEARrnIF @I<@CNrn BEGINrn SET @I=@I+1rn exec BOM_GEN '',@CN,@I,''rn ENDrnENDrnrn执行存储过程得到结果表RESULT:rnBNO --层次号1,2,3...rnCNO --层次号101,101201,102,102201,102201301.....rnPRODUCT --(主件),rnANO --(序号),rnPARTNO --(元件),rnNAME2 --(元件描述),rnUNIT --(元件单位),rnDIAOLOG --(元件属性,分采购自制),rnSUN --(元件损耗率),rnQTY --(组成用量)) rn然后把RESULT 按CNO排序,rn用*1替换长度为3,***2替换,*****长度为替换长度为9....rn得到一主件展阶结果如下:rn阶层次序 料号 名称规格 类别 损耗率 单位 用量rn*1 1010-91172CL #91172 Outta Sight Train Case-ACT-Clr 半成品 0 Pcs 72rn**2 1010-9117201 #91172 Kit-A625T-Top Left set 半成品 0 Pcs 1rn***3 1221-C051001 (C-Deep-CR27)-Tran.Pink182C w 半成品 0 Pcs 1rn****4 0202PD51-0 (2001)(C-CR27) 2E-No.36-Textile 过渡性半成品 0 pcs 1rn*****5 1222-E391007 CR27-E1142-平面纹 半成品 0 Pcs 1rn*****6 3110-0CR2700 铝皿-CR27-0.30(t)cm 原材料 0 Pcs 1rn*****6 PD-E1142 E1142 过渡性半成品 0 Kg 0.0008rn*****5 1222-E391015 CR27-E1141-平面纹 半成品 0 Pcs 1rn*****6 3110-0CR2700 铝皿-CR27-0.30(t)cm 原材料 0 Pcs 1rn*****6 PD-E1141 E1141 过渡性半成品 0 Kg 0.0008rn****4 3240-00C0113 粉罐-(C)-透明粉红182C带银葱盖 原材料 0 Pcs 1rnrn但是在执行循环rnINSERT INTO RESULT rn SELECT @I+1 AS BNO,RTRIM(LTRIM(@C))+STR(@I+1)+ANO AS CNO,PRODUCT,ANO,PARTNO,NAME2,UNIT,DIAOLOG,SUN,QTY ---老报错说数据类型不符rn FROM #BOM WHERE PRODUCT=@Prn 是老是报错后经检查发现@C CHAR(20)长度有问题,但不知如何解决,请高手帮助.rn 论坛

没有更多推荐了,返回首页