[推荐][原创]完整的BOM构造 分解 运算的SQL代码

goodidea

以下是我在年初写的, 在用友ERP8.5的数据库基础上进行按照BOM分层次的成本核算的后台SQL代码,。

用这些SQL代码实现了95%的功能， 前台完全是对这些过程的调用,所以我最初前台用ACCESS来做的，后来又用VB.NET来写, 很快就完成了。

所有的代码都是我在洁净开发环境中完成，未参考任何第三方的资料。在汇总过程中没有使用递归。

主要步骤是

1。构造一个自己的BOM

2。从材料领用表中取出材料消耗数量和金额

3。显示材料领取与BOM标准的差异

4。手工录入各层次的制造工时。手工录入总制造费用，总人工费用。

5。按照制造工时，分摊制造费用和人工费用

6。按层次向上汇总制造费用，人工费用和材料费用。

这里主要考虑了，

1。同一物料（主要指非直接材料）可能一部分自制，一部分是外购，外购部分是不会产生制造和人工费用的，外购部分直接成为叶子，不再有子孙。

2。一个物料可能用在BOM不同层次组成不同父物料。

3。这里是按批号计算的，而且一个一批实际上只有一件，这个产品很特殊  :-)

4。这里的名词称谓不是ERP标准，按照树的称呼，例如把没有下级的物料，称为叶子

-----------------建立两张表-------------------------------

-- goodidea(朱彦志) 2005/02/25 16:33

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddInBomRoot]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[AddInBomRoot]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddInBomTree]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[AddInBomTree]

GO

CREATE TABLE [dbo].[AddInBomRoot] (

[cBatch] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[cPSPCode] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[cInvName] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[cInvStd] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[iQty] [money] NOT NULL ,

[bComplatedWhenCompute] [bit] NULL ,

[mMateCostAdd] [money] NOT NULL ,

[mProcCostAdd] [money] NOT NULL ,

[mPayCostAdd] [money] NOT NULL ,

[dInputTime] [datetime] NULL ,

[cInputer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[dModifyTime] [datetime] NULL ,

[cModifier] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[saved] [bit] NOT NULL ,

[iStatus] [smallint] NOT NULL ,

[cUnit] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[AddInBomTree] (

[cPSCode] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[iPSQuantity] [money] NULL ,

[cPSPCode] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[iPSPOrder] [int] NOT NULL ,

[iLevel] [int] NULL ,

[iOrder] [int] NOT NULL ,

[cInvName] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[cInvStd] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[bIsLeafage] [bit] NOT NULL ,

[cBatch] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[bComplated] [bit] NULL ,

[cRootCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[mProcHour] [money] NOT NULL ,

[mQtyInTree] [money] NOT NULL ,

[mQtyInFact] [money] NOT NULL ,

[mPrice] [money] NOT NULL ,

[mMateCost] [money] NOT NULL ,

[mProcCost] [money] NOT NULL ,

[mPayCost] [money] NOT NULL ,

[mMateCostAdd] [money] NOT NULL ,

[mProcCostAdd] [money] NOT NULL ,

[mPayCostAdd] [money] NOT NULL ,

[dInputTime] [datetime] NULL ,

[cInputer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[dModifyTime] [datetime] NULL ,

[cModifier] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[bIsPurchase] [bit] NOT NULL ,

[AutoId] [bigint] IDENTITY (1, 1) NOT NULL ,

[bPrintFlag] [bit] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AddInBomRoot] WITH NOCHECK ADD

CONSTRAINT [PK_AddInBomRoot] PRIMARY KEY  CLUSTERED

(

  [cBatch]

)  ON [PRIMARY]

GO

ALTER TABLE [dbo].[AddInBomTree] WITH NOCHECK ADD

CONSTRAINT [PK_AddInBomTree] PRIMARY KEY  CLUSTERED

(

  [AutoId]

)  ON [PRIMARY]

GO

ALTER TABLE [dbo].[AddInBomRoot] ADD

CONSTRAINT [DF_AddInBomRoot_iQty] DEFAULT (0) FOR [iQty],

CONSTRAINT [DF_AddInBomRoot_bComplatedWhenCompute] DEFAULT (0) FOR [bComplatedWhenCompute],

CONSTRAINT [DF_AddInBomRoot_mMateCostAdd] DEFAULT (0) FOR [mMateCostAdd],

CONSTRAINT [DF_AddInBomRoot_mProcCostAdd] DEFAULT (0) FOR [mProcCos

goodidea

-----------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddinPrcComputeCostAdded]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[AddinPrcComputeCostAdded]

GO-----------------逐层向上汇总成本,存储在累加成本字段--------------------------------- goodidea(朱彦志) 2005/02/25 18:53 Updated

-- goodidea(朱彦志) 2005/02/26 13:26 使用方法二，并提高运算效率

-- goodidea(朱彦志) 2005/02/26 15:34  

-- goodidea(朱彦志) 2005/02/26 21:57 再次使用方法一，法二不能满足材料成本的计算

-- goodidea(朱彦志) 2005/02/26 22:55

-- goodidea(朱彦志) 2005/02/27 10:50 改进方法二，但还有点问题

-- goodidea(朱彦志) 2005/02/28 20:15

CREATE    procedure AddinPrcComputeCostAdded(

@cBatch varchar(20)  --批号

)

asbeginset nocount onDeclare @Order int  --顺序号

Declare @MinOrder int  --顺序号--更新完工标志

update AddInBomRoot

set bcomplatedwhencompute = bcompmark

from CA_ProNb inner join AddInBomRoot

on AddInBomRoot.cBatch = CA_ProNb.cBatch

where AddInBomRoot.cBatch = @cBatch-- 初始化

--把本层的成本加入到本层的累计成本中

update AddInBomTree

set mPayCostAdd = mPayCost

  ,mMateCostAdd = mMateCost

  ,mProcCostAdd = mProcCost

where  cBatch = @cBatch  -- and mProcHour is not null  --- 方法二 比原方法一减少循环次数 -- 取最大的Order 和最小order

set @Order = (select max(iPSPOrder) from AddInBomTree where bIsLeafage = 0  and cBatch = @cBatch )

set @MinOrder = (select min(iOrder) from AddInBomTree where cBatch = @cBatch)

if (@order % 10  <> 0) -- 能被10整除的才是非叶子(1 为叶子，2为“变种”叶子)

begin

set @order = @order / 10

set @order = @order * 10

end--先把叶子的材料成本加入到双亲的累计材料成本中

update AddInBomTree

set  mMateCostAdd = mMateCostAdd + isnull(mMateCostAdds,0)

from (select sum(mMateCostAdd) mMateCostAdds, iPSPorder

  from AddInBomTree

  where cBatch = @cBatch and bIsLeafage = 1

  group by iPSPorder

  ) as X inner join AddInBomTree on x.iPSPorder = AddInBomTree.iOrder

where cBatch = @cBatch and bIsLeafage = 0--向上依次累加非叶子的成本到双亲的累计成本中

while @order >= @MinOrder

begin  update AddInBomTree

set  mPayCostAdd  = mPayCostAdd  + isnull(mPayCostAdds, 0)

  ,mMateCostAdd = mMateCostAdd + isnull(mMateCostAdds,0)

  ,mProcCostAdd = mProcCostAdd + isnull(mProcCostAdds,0)

from (select sum(mPayCostAdd) mPayCostAddS

   ,sum(mMateCostAdd) mMateCostAdds

   ,sum(mProcCostAdd) mProcCostAdds

  from AddInBomTree

  where iPSPOrder  = @order and  cBatch = @cBatch  and bIsLeafage = 0

  ) as X

where AddInBomTree.iOrder = @order and cBatch = @cBatch  set @Order = (select max(iPSPOrder) from AddInBomTree where iPSPOrder < @order and bIsLeafage = 0  and cBatch = @cBatch )end/*

--- 方法一， 已删除select  cFormartedCode = replicate( '|',iLevel ) + '-' + cPSCode , *

from AddInBomTree

where bIsLeafage = 0

ORDER BY IORDER*/endGO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO--------------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddinPrcComputeLeafageMateCost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[AddinPrcComputeLeafageMateCost]

GO-----------------读取叶子材料费用(按照在树叶子中的权分配),但不改变标志--------------------------------- goodidea(朱彦志) 2005/02/26 14:26

-- goodidea(朱彦志) 2005/03/15 11:26 -- 读取材料费用总共有3个过程

-- 这里用于第三次计算CREATE        procedure AddinPrcComputeLeafageMateCost(

@cBatch varchar(20)  --批号

)

asbeginset nocount on--初始化

update AddInBomTree

set  mMateCost = 0 , mPrice = 0, mQtyInFact = 0

where cBatch = @cBatch update T

set mMateCost =( mQtyInTree / mQtyInTreeS )* iAOutPrice

, mPrice = (mQtyInTree / mQtyInTreeS) * iAOutPrice

, mQtyInFact = mQtyInTree / mQtyInT

goodidea

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO---------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddinPrcComputeMateCost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[AddinPrcComputeMateCost]

GO-----------------读取材料费用(按照在树中的权),但不改变标志--------------------------------- goodidea(朱彦志) 2005/02/26 14:26

-- goodidea(朱彦志) 2005/03/15 11:26 -- 读取材料费用总共有3个过程

-- 这里用于第一次计算create      procedure AddinPrcComputeMateCost(

@cBatch varchar(20)  --批号

)

asbeginset nocount on

update AddInBomTree

set  mMateCost = 0 , mPrice = 0, mQtyInFact = 0

where cBatch = @cBatch update T

set mMateCost =( mQtyInTree / mQtyInTreeS )* iAOutPrice

, mPrice = (mQtyInTree / mQtyInTreeS) * iAOutPrice

, mQtyInFact = mQtyInTree / mQtyInTreeS

from AddInBomTree T

inner join (/* 计算出该物料领取的实际数量和 */

  select cInvCode , sum(iAOutQuantity) as iAOutQuantity , sum(iAOutPrice) as iAOutPrice

  from IA_Subsidiary S

  where cBusType = '领料' and cProCode = @cBatch



  group by cInvcode

  having sum(iAOutQuantity) <> 0

  ) O on T.cPSCode = O.cInvcode



inner join (/* 计算出该物料在bom中的权的和 */ select cPSCode, sum(mQtyInTree) as mQtyInTreeS

    from AddInBomTree

    where cBatch = @cBatch

    group by cPSCode) as S on T.cPSCode = S.cPSCode

where  T.cBatch = @cBatch

and O.iAOutQuantity <>0 and O.iAOutPrice <> 0

--    and T.bIsLeafage = 1    --全部节点，包括叶子和非叶子/*

select  cFormartedCode = replicate( '|',iLevel ) + '-' + cPSCode, *

from AddInBomTree

--where bIsLeafage = 1

ORDER BY IORDER

*/endGO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO---------------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddinPrcComputeNotLeafageMateCost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[AddinPrcComputeNotLeafageMateCost]

GO

-----------------读取非叶子的材料费用(按照在树中的权),但不改变标志--------------------------------- goodidea(朱彦志) 2005/02/26 14:26

-- goodidea(朱彦志) 2005/03/15 11:26 -- 读取材料费用总共有3个过程

-- 这里用于第二次计算CREATE     procedure AddinPrcComputeNotLeafageMateCost(

@cBatch varchar(20)  --批号

)

asbeginset nocount on

update AddInBomTree

set  mMateCost = 0 , mPrice = 0, mQtyInFact = 0

where cBatch = @cBatch update T

set mMateCost =( mQtyInTree / mQtyInTreeS )* iAOutPrice

, mPrice = (mQtyInTree / mQtyInTreeS) * iAOutPrice

, mQtyInFact = mQtyInTree / mQtyInTreeS

from AddInBomTree T

inner join (/* 计算出该物料领取的实际数量和 */

  select cInvCode , sum(iAOutQuantity) as iAOutQuantity , sum(iAOutPrice) as iAOutPrice

  from IA_Subsidiary S

  where cBusType = '领料' and cProCode = @cBatch



  group by cInvcode

  having sum(iAOutQuantity) <> 0

  ) O on T.cPSCode = O.cInvcode



inner join ( /* 计算出该物料在bom中的权的和 */ select cPSCode, sum(mQtyInTree) as mQtyInTreeS

    from AddInBomTree

    where cBatch = @cBatch and bIsLeafage = 0  -- 非叶子

    group by cPSCode) as S on T.cPSCode = S.cPSCode

where  T.cBatch = @cBatch

and O.iAOutQuantity <>0 and O.iAOutPrice <> 0

    and T.bIsLeafage = 0/*

select  cFormartedCode = replicate( '|',iLevel ) + '-' + cPSCode, *

from AddInBomTree

--where bIsLeafage = 1

ORDER BY IORDER

*/end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO----------------------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddinPrcDeletePurchasedItemAndChildrens]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[AddinPrcDeletePurchasedItemAndChildrens]

GO

tmtony

的确是非常好的原创, 很有启发!!

yodong

楼主真是伟大.收了,可能要看几天才能看明白呀.楼主如果发个例子上来那就太好了.

[此贴子已经被作者于2005-8-30 13:42:54编辑过]

qlm

楼主说:在汇总过程中没有使用递归。

难道使用递归不好吗?有什么不好?能说一下吗?

vogala

谢啦，俺下个做参考

zw211

我水平太低,看不懂.
有原文件供学习吗?

michael100

俺水平太低,看不懂.不过支持楼主

唐玉娥

good!