金蝶K3--BOM表--展开

declare @strbomsinterid nvarchar(1000)
declare @sbominterid nvarchar(20)
declare @ipos int
declare @iorderrule int
declare @dbitemtime datetime
declare @deitemtime datetime
set @strbomsinterid = '1143'
set @sbominterid = ''
set @iorderrule = 0
set @dbitemtime = '1900-01-01'
set @deitemtime = '2100-01-01'

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,fopersn int null default(0),foperid int default(0),frootbomid int default(0))
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,fopersn int null default(0),foperid int default(0),frootbomid int default(0))
create table #errors (findex int identity, ftype smallint default(0), ferrtext nvarchar(355) )

insert into #mutiparentitem(fbominterid,fitemid,fneedqty,fbomlevel,fparentid,fitemtype,fbom,frootbomid)
    select a.finterid, t1.fitemid,a.fqty, 0,0,(case t5.fid when 'wg' then 0 when 'zz' then 1 when 'wwjg' then 1 else 2 end) fitemtype,t1.fitemid,a.finterid
    from icbom a
        inner join t_icitem t1 on t1.fitemid = a.fitemid
        left join t_submessage t5 on t1.ferpclsid = t5.finterid
    where t5.ftypeid = 210 and  a.finterid in(1143)
declare @p5 int
set @p5=null
declare @p6 nvarchar(400)
set @p6=null
exec planmutibomexpand 50,@iorderrule,@dbitemtime,@deitemtime,@p5 output,@p6 output
select a.frootbomid as finterid, icbomchild.fentryid, icbomchild.fitemid, t002.fqtydecimal, t002.fpricedecimal, t002.funitgroupid, t002.fnumber as  fitemidnumber, t002.fname as  fitemidname, t002.fname as fitemname, t002.fmodel as fmodel,(select fname from t_submessage where t002.ferpclsid=finterid) as ferpcls, (case icbomchild.fhaschar when 1 then  '是' else '否' end) as fhaschar, icbomchild.fauxpropid, t007.fnumber as  fauxpropidnumber, t007.fname as  fauxpropidname, icbomchild.funitid, t008.fnumber as  funitidnumber, t008.fname as  funitidname, t009.fname as fbaseunit, icbomchild.fmaterieltype, t010.fid as  fmaterieltypenumber, t010.fname as  fmaterieltypename, icbomchild.fmarshaltype, t011.fid as  fmarshaltypenumber, t011.fname as  fmarshaltypename, icbomchild.fqty, a.fneedqty/(case when isnull(t008.fcoefficient,0)=0 then 1 else isnull(t008.fcoefficient,0) end) as fauxqty, icbomchild.fbeginday, icbomchild.fendday, icbomchild.fpercent, icbomchild.fscrap, icbomchild.fpositionno, icbomchild.fitemsize, icbomchild.fitemsuite, icbomchild.fopersn, t021.fopersn as  fopersnnumber, t021.fopersn as  fopersnname, icbomchild.foperid, t022.fid as  foperidnumber, t022.fname as  foperidname, icbomchild.fmachinepos, icbomchild.foffsetday, icbomchild.fbackflush, t025.fid as  fbackflushnumber, t025.fname as  fbackflushname, t002.fiskeyitem as fiskeyitem,(select fname from t_submessage where t002.fusestate=finterid) as fusestate, t002.fdeleted as fforbituse, icbomchild.fstockid, t029.fnumber as  fstockidnumber, t029.fname as  fstockidname, icbomchild.fspid, t030.fnumber as  fspidnumber, t030.fname as  fspidname, icbomchild.fnote, icbomchild.fnote1, icbomchild.fnote2, icbomchild.fnote3, t002.ferpclsid as ferpclsid, t002.fauxclassid as fauxpropcls, icbomchild.fpdmimportdate, icbomchild.fdetailid, tsubs.fisrepitem as fisrepitem ,cast('' as varchar(255)) as fitemchardesc, icbomchild.fcostpercentage, icbomchild.fbrno,a.flevelstring as flevelno, a.frootbomid as frootbomid, a.findex as findexsort
into #tmpbillprint
 from icbomchild join t_icitem  t002 on t002.fitemid = icbomchild.fitemid left join t_auxitem  t007 on t007.fitemid = icbomchild.fauxpropid left join t_measureunit  t008 on t008.fitemid = icbomchild.funitid left join t_measureunit t009 on  t009.fitemid=t002.funitid  left join t_submessage  t010 on t010.finterid = icbomchild.fmaterieltype left join t_submessage  t011 on t011.finterid = icbomchild.fmarshaltype left join  icbom  t_bom on t_bom.finterid = icbomchild.finterid
 left join t_routingoper  t021 on t021.fopersn = icbomchild.fopersn and t_bom.froutingid = t021.finterid
 left join t_submessage  t022 on t022.finterid = icbomchild.foperid left join t_submessage  t025 on t025.finterid = icbomchild.fbackflush left join t_stock  t029 on t029.fitemid = icbomchild.fstockid left join t_stockplace  t030 on t030.fspid = icbomchild.fspid inner join (select  distinct t1.finterid,t1.fitemid,t1.fentryid ,case when t2.fitemid is null then 'n' else 'y' end as fisrepitem from icbomchild t1
 left join t_subsitem t2 on t1.fitemid=t2.fitemid  ) tsubs on icbomchild.fitemid=tsubs.fitemid and icbomchild.fentryid=tsubs.fentryid  and icbomchild.finterid=tsubs.finterid
 inner join #mutidata a on a.fbominterid=icbomchild.finterid and a.fitemid=icbomchild.fitemid and a.foperid=icbomchild.foperid and a.fentryid=icbomchild.fentryid
 where a.fbomlevel>0 order by a.frootbomid,a.findex desc

--select * from #mutidata
select * from #tmpbillprint
drop table #tmpbillprint
drop table #mutidata
drop table #mutiparentitem
drop table #errors

 

转载于:https://my.oschina.net/ansenchina/blog/3023640