金蝶K3 WISE BOM多级展开_BOM成本表

/****** Object:  StoredProcedure [dbo].[pro_bobang_BOMCost]    Script Date: 07/29/2015 16:09:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
drop proc pro_bobang_BOMCost
GO
create PROC [dbo].[pro_bobang_BOMCost]
@FBomNumber1 varchar(50),  --bom单号
@FBomNumber2 varchar(50)   --bom单号
as
begin
SET ANSI_WARNINGS OFF
set nocount on


--1.--业务员查找某个BOM单,包含所有BOM(已使用,未使用,已审核,未审核)
  with cte as (
  select convert(varchar(100),'') as cen,Finterid,FBOMNumber as fppbomnumber,convert(varchar(50),'') as fpbomnumber,Fbomnumber AS FCbomnumber,fitemid,fitemid as fpitemid,0 as fpinterid,convert(decimal(18,4),1) as FBomQty,convert(varchar(500),RIGHT('000000'+CONVERT(varchar(10),Finterid),6)) as code  from ICBOM 
  where (1=1)
  and FBOMNumber >= @FBomNumber1
  and FBOMNumber <= case when @FBomNumber2='' then (select MAX(FBOMNumber) from ICBOM) else @FBomNumber2 end
  union all
  select convert(varchar(100),cen