[推薦][原創(chuàng)]完整的BOM構(gòu)造 分解 運(yùn)算的SQL代碼

goodidea

以下是我在年初寫的, 在用友ERP8.5的數(shù)據(jù)庫基礎(chǔ)上進(jìn)行按照BOM分層次的成本核算的后臺SQL代碼,。

用這些SQL代碼實(shí)現(xiàn)了95%的功能， 前臺完全是對這些過程的調(diào)用,所以我最初前臺用ACCESS來做的，后來又用VB.NET來寫, 很快就完成了。

所有的代碼都是我在潔凈開發(fā)環(huán)境中完成，未參考任何第三方的資料。在匯總過程中沒有使用遞歸。

主要步驟是

1。構(gòu)造一個(gè)自己的BOM

2。從材料領(lǐng)用表中取出材料消耗數(shù)量和金額

3。顯示材料領(lǐng)取與BOM標(biāo)準(zhǔn)的差異

4。手工錄入各層次的制造工時(shí)。手工錄入總制造費(fèi)用，總?cè)斯べM(fèi)用。

5。按照制造工時(shí)，分?jǐn)傊圃熨M(fèi)用和人工費(fèi)用

6。按層次向上匯總制造費(fèi)用，人工費(fèi)用和材料費(fèi)用。

這里主要考慮了，

1。同一物料（主要指非直接材料）可能一部分自制，一部分是外購，外購部分是不會產(chǎn)生制造和人工費(fèi)用的，外購部分直接成為葉子，不再有子孫。

2。一個(gè)物料可能用在BOM不同層次組成不同父物料。

3。這里是按批號計(jì)算的，而且一個(gè)一批實(shí)際上只有一件，這個(gè)產(chǎn)品很特殊  :-)

4。這里的名詞稱謂不是ERP標(biāo)準(zhǔn)，按照樹的稱呼，例如把沒有下級的物料，稱為葉子

-----------------建立兩張表-------------------------------

-- 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 使用方法二，并提高運(yùn)算效率

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

-- goodidea(朱彥志) 2005/02/26 21:57 再次使用方法一，法二不能滿足材料成本的計(jì)算

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

-- goodidea(朱彥志) 2005/02/27 10:50 改進(jìn)方法二，但還有點(diǎn)問題

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

CREATE    procedure AddinPrcComputeCostAdded(

@cBatch varchar(20)  --批號

)

asbeginset nocount onDeclare @Order int  --順序號

Declare @MinOrder int  --順序號--更新完工標(biāo)志

update AddInBomRoot

set bcomplatedwhencompute = bcompmark

from CA_ProNb inner join AddInBomRoot

on AddInBomRoot.cBatch = CA_ProNb.cBatch

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

--把本層的成本加入到本層的累計(jì)成本中

update AddInBomTree

set mPayCostAdd = mPayCost

  ,mMateCostAdd = mMateCost

  ,mProcCostAdd = mProcCost

where  cBatch = @cBatch  -- and mProcHour is not null  --- 方法二 比原方法一減少循環(huán)次數(shù) -- 取最大的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--先把葉子的材料成本加入到雙親的累計(jì)材料成本中

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--向上依次累加非葉子的成本到雙親的累計(jì)成本中

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-----------------讀取葉子材料費(fèi)用(按照在樹葉子中的權(quán)分配),但不改變標(biāo)志--------------------------------- goodidea(朱彥志) 2005/02/26 14:26

-- goodidea(朱彥志) 2005/03/15 11:26 -- 讀取材料費(fèi)用總共有3個(gè)過程

-- 這里用于第三次計(jì)算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-----------------讀取材料費(fèi)用(按照在樹中的權(quán)),但不改變標(biāo)志--------------------------------- goodidea(朱彥志) 2005/02/26 14:26

-- goodidea(朱彥志) 2005/03/15 11:26 -- 讀取材料費(fèi)用總共有3個(gè)過程

-- 這里用于第一次計(jì)算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 (/* 計(jì)算出該物料領(lǐng)取的實(shí)際數(shù)量和 */

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

  from IA_Subsidiary S

  where cBusType = '領(lǐng)料' and cProCode = @cBatch



  group by cInvcode

  having sum(iAOutQuantity) <> 0

  ) O on T.cPSCode = O.cInvcode



inner join (/* 計(jì)算出該物料在bom中的權(quán)的和 */ 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    --全部節(jié)點(diǎn)，包括葉子和非葉子/*

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

-----------------讀取非葉子的材料費(fèi)用(按照在樹中的權(quán)),但不改變標(biāo)志--------------------------------- goodidea(朱彥志) 2005/02/26 14:26

-- goodidea(朱彥志) 2005/03/15 11:26 -- 讀取材料費(fèi)用總共有3個(gè)過程

-- 這里用于第二次計(jì)算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 (/* 計(jì)算出該物料領(lǐng)取的實(shí)際數(shù)量和 */

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

  from IA_Subsidiary S

  where cBusType = '領(lǐng)料' and cProCode = @cBatch



  group by cInvcode

  having sum(iAOutQuantity) <> 0

  ) O on T.cPSCode = O.cInvcode



inner join ( /* 計(jì)算出該物料在bom中的權(quán)的和 */ 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

的確是非常好的原創(chuàng), 很有啟發(fā)!!

yodong

樓主真是偉大.收了,可能要看幾天才能看明白呀.樓主如果發(fā)個(gè)例子上來那就太好了.

[此貼子已經(jīng)被作者于2005-8-30 13:42:54編輯過]

qlm

樓主說:在匯總過程中沒有使用遞歸。

難道使用遞歸不好嗎?有什么不好?能說一下嗎?

vogala

謝啦，俺下個(gè)做參考

zw211

我水平太低,看不懂.
有原文件供學(xué)習(xí)嗎?

michael100

俺水平太低,看不懂.不過支持樓主

唐玉娥

good!