金蝶k3wise BOM展开

金蝶系统BOM展开调用sql

Create Table #data (  FIndex int IDENTITY, FBomInterid int, FBomEntryID int NOT NULL DEFAULT 0,/*BOM分录*/ FItemID int null, FAuxPropID int default(0) null, FNeedQty decimal(28,14) default(0) null, FBeginDay Datetime Default('1900-01-01') NOT NULL,  FEndDay DateTime Default('2100-01-01') NOT NULL,  FPercent Decimal(28,13) Default(100) 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 ) 

Create Table #ParentItem(  FIndex int IDENTITY, FBomInterid int, FBomEntryID int NOT NULL DEFAULT 0,/*BOM分录*/ FItemID int null, FAuxPropID int default(0) null, FNeedQty decimal(28,14) default(0) null, FBeginDay Datetime Default('1900-01-01') NOT NULL,  FEndDay DateTime Default('2100-01-01') NOT NULL,  FPercent Decimal(28,13) Default(100) 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 )

Create Table #Errors ( FIndex int IDENTITY, FType smallint default(0), FErrText nvarchar(355) )
 
Insert into #ParentItem (FBOMInterID,FBOMEntryID,FItemID,FAuxPropID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom) Select 0 AS FBOMInterID,0 AS FBOMEntryID, t1.FItemID,0,round(1,t1.FQtyDecimal+2), 0,0,t1.FErpClsID FItemtype,t1.FItemID  From t_ICItem t1 Where  t1.fitemid=1640

declare @p4 int
set @p4=0
declare @p5 nchar(400)
set @p5=N'                                                                                                                                                                                                                                                                                                                                                                                                                '
exec PlanMutiBomReverse 0,0,1,@p4 output,@p5 output
select @p4, @p5

select a.FLevelString FLevel,b.FNumber FNumber  ,b.FName, b.FModel, k.FName as FErpClsName, isnull(d.FName,'') FUnitID, a.FNeedQty as FQty,  1 as FColPrecision, a.FBeginDay, a.FEndDay, a.FPercent,  (case c.FBomSkip when 1058 then '是' else '否' end) as FBomSkip ,
CASE WHEN (a.FBeginDay BETWEEN '1900-01-01' AND  '2100-01-01') THEN 0  WHEN (a.FEndDay BETWEEN '1900-01-01' AND  '2100-01-01' ) THEN 0  WHEN ('1900-01-01' >= a.FBeginDay AND '2100-01-01' <= a.FEndDay) THEN 0 ELSE 1 END AS FAlterBackColor, '253, 223, 223' AS FBackColor, 
 b.FChartNumber, isnull(e.FBillNo,'') as FRoutingID, isnull(e.FRoutingName,'') as FRoutingName,c.FNote,a.FBomInterid,a.FBOMLevel,b.FitemID EditFitem,F.FQtyDecimal FInitDecimal,F.FQtyDecimal FQtyDecimal , ISNULL(g.FmachinePos,'') AS FMachinePos,a.FAuxPropID,ISNULL(ta.FName,'') as FAuxPropName  from #data a inner join t_icitem b on a.FItemid = b.FItemid inner join icbom c on a.FBomInterid = c.FInterID And a.FItemid = c.FItemid  left outer join T_routing e on c.FRoutingID=e.FinterID inner join t_icitem f on a.FBom=f.fitemid
 Left Join t_AuxItem ta on a.FAuxPropID=ta.FItemID 
 left outer join t_measureunit d on F.FUnitID=d.FItemID inner join  t_subMessage k on b.FErpclsID = k.FInterID LEFT JOIN ICBOMChild g on c.FInterID=g.FInterID AND f.FItemID=g.FItemID and a.FBOMEntryID=g.FEntryID Where  a.FBom=1640  order by a.Findex desc