set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[zzz_cz_wyh_bom]
@Fnumber nvarchar(255)
 AS

declare @finterid  int
declare @fitemid int

select @fitemid=fitemid 
from t_icitem
where 
--fnumber='01.HY.HY-1/116-B-S'
fnumber=@fnumber


select @finterid=finterid
from icbom
where fitemid=@fitemid -- and fusestatus=1072


Create Table #Errorss 
( FIndex int IDENTITY, FType smallint default(0),
FBomNumber int default(0), FErrText nvarchar(355) )

Create Table #data1 
(  FIndex int IDENTITY,
 FBomInterid int, 
FItemID int null, 
FNeedQty decimal(28,8) default(0) null, 
FBOMLevel int null, FItemType int null, 
FParentID int default(0)null, 
FRate   decimal(20,8) default(0) null, 
FHistory int default(0) null, 
FHaveMrp smallint default(0) null, 
FLevelString nvarchar(200) null, FBom int 

) 

exec PlanBomNestingCheck @finterid

Create Table #Mutidata 
(  FIndex int IDENTITY,
  FEntryID INT, FBomInterid int, 
FItemID int null, 
FNeedQty decimal(28,14) default(0) null, 
FBOMLevel int null, 
FItemType int null, 
FParentID int default(0)null, 
FRate   decimal(28,14) default(0) null, 
FHistory int default(0) null,
FHaveMrp smallint default(0) null, 
FLevelString varchar(200) null, 
FBom int, 
FMaterielType int  default(371) null,
FOperID int default(0),
FCZQualityCheck  bit) 

 Create Table #MutiParentItem
(FIndex int IDENTITY,
FEntryID INT default(0), 
FBomInterid int, 
FItemID int null, 
FNeedQty decimal(28,14) default(0) null, 
FBOMLevel int null, 
FItemType int null,  
FParentID int default(0)null, 
FRate   decimal(28,14) default(0) null, 
FHistory int default(0) null, 
FHaveMrp smallint default(0) null, 
FLevelString varchar(200) null , 
FBom int, 
FMaterielType int  default(371) null,
FOperID int default(0),
froutingid  int null,
FCZQualityCheck  bit
) 

 Create Table #Errors 
( FIndex int IDENTITY, 
FType smallint default(0), 
FErrText varchar(355) )

Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom,froutingid,FCZQualityCheck) 
Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID, a.FRoutingid,t1.FCZQualityCheck 
From icbom a,t_ICItem t1 
Where t1.FItemID = a.fitemid and   a.finterid=@finterid 


declare @P1 int
set @P1=0
declare @P2 char(400)
set @P2='                                                                                                                                                                                                                                                                                                                                                                                                                '
exec PlanMutiBomExpandEx 50, 1, '1900-01-01', '2100-01-01', @P1 output, @P2 output

Create Table #Mutidata1 
(  FIndex int IDENTITY,
  FEntryID INT, FBomInterid int, 
FItemID int null, 
FNeedQty decimal(28,14) default(0) null, 
FBOMLevel int null, 
FItemType int null, 
FParentID int default(0)null, 
fprice   decimal(28,14) default(0) null, 
famount   decimal(28,14) default(0) null, 
FLevelString varchar(200) null, 
FBom int, 
FMaterielType int  default(371) null,
FOperID int default(0),
FCZQualityCheck  bit) 


insert into #Mutidata1(fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid,FCZQualityCheck)
select fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid,FCZQualityCheck
from #Mutidata
order by findex


update t1 set t1.fprice=
(select  fprice 
from icpurchaseentry  t2 
where  t2.fdetailid= (
   select max(t3.fdetailid) 
   from  icpurchaseentry t3 
   inner join t_icitem t4 on t3.fitemid=t4.fitemid     
     and t4.fitemid=t1.fitemid) )
 from  #Mutidata1  t1
 where fitemtype=1 

update t1 set famount=isnull(isnull(fprice,0)*isnull(fneedqty,0),0)
fr