物料ErpBomConfig的存储过程

create procedure bomlist @mtlno varchar(40),@alternativebom varchar(2)='01',@bomUsage varchar(1)='1'
as
declare @materialbill varchar(8),@bomcomponent varchar(40),@quantity NUMERIC(16,6),@classtype varchar(3),@configuration varchar(18)
create table #tmp (characterval varchar(30),intcharact varchar(10));
create table #tmp2 (bomcomponent varchar(40),quantity NUMERIC(16,6),classtype varchar(3));

if (select configuralbeMtl from di_t_mmrplantmtl where mtlno=@mtlno) is null
begin
	set @materialbill=(select materialbill from di_t_BDMMaterToBOMLink where mtlno=@mtlno and alternativebom =@alternativebom and  bomUsage =@bomUsage)
	if(@materialbill is not null)
		insert into #tmp2(bomcomponent ,quantity ,classtype) 
		(
			select a.bomcomponent,a.quantity,a.classtype from di_t_bdmbomitem a where  bomitemnodeno in (select bomitemnodeno
			from di_t_BDMBOMsItemSelect where materialbill=@materialbill) and materialbill=@materialbill 
		)
end
else
begin

	insert into #tmp(characterval,intcharact) 
	(
		select 
		charactvalue,intcharact
		from 
		di_t_pcmibsymbol 
		where 
		numberindic in 
		(
			select 
			signnumindic 
			from 
			di_t_pcmexamchartrestrain 
			where 
			internalCfg = 
			(
				select configuration from di_t_mmrplantmtl where mtlno=@mtlno
			)
		)
	)
	set @materialbill = (select
				materialbill 
				from 
				di_t_BDMMaterToBOMLink 
				where 
				alternativebom = '01' 
				and  bomUsage = '1' 
				and mtlno=(
				select configuralbeMtl from di_t_mmrplantmtl where mtlno=@mtlno
				));
	--select * from #tmp;

	insert into #tmp2(bomcomponent ,quantity ,classtype) 
	(
		select a.bomcomponent,a.quantity,a.classtype from di_t_bdmbomitem a where  bomitemnodeno in (select bomitemnodeno
		from di_t_BDMBOMsItemSelect where materialbill=@materialbill) and materialbill=@materialbill and classtype is null
	)
	--print(@materialbill);

	declare cur cursor for
		select c.bomcomponent ,c.quantity,c.classtype,a.configuration from di_t_pcmclasstypes b 
		,(select 
		bomcomponent,quantity,classtype 
		from 
		di_t_bdmbomitem 
		where  
		bomitemnodeno in (select bomitemnodeno
		from 
		di_t_BDMBOMsItemSelect 
		where 
		materialbill=@materialbill)
		and materialbill=@materialbill 
		and classType is not null) c 
		,(select * from di_t_PCMLinkBeInterNumAndObj) a
		where c.classtype=b.classtype and a.keyobjclassified = c.bomcomponent and multipleobjallowed = 'Y'
	open cur
	fetch next from cur into @bomcomponent,@quantity,@classtype,@configuration
	--select @bomcomponent,@quantity,@classtype,@configuration
	while @@fetch_status = 0
		begin

			--print(@bomcomponent)

			if(exists (select a.classreleaseindi , b.classificastatus
				from 
				di_t_pcmclassificastatus a , di_t_pcmallocatableobjtoclass b
				where 
				a.classificastatus=b.classificastatus 
				and 
				a.classType = @classtype
				and keyobjclassified=@configuration and a.classreleaseindi <>'Y'
			))
				return
			else
				if (exists (
					select a.* from 
					(	select intcharact ,characterval from di_t_PCMCharacterVal where keyobjclassified =@configuration and internalclassno in (select internalclassno from di_t_pcmallocatableobjtoclass where keyobjclassified=@configuration  and classType =@classtype)
					) a ,
					#tmp b where a.intcharact=b.intcharact and a.characterval = b.characterval
				))
				insert into #tmp2(bomcomponent ,quantity ,classtype) values (@bomcomponent ,@quantity ,@classtype)

			fetch next from cur into @bomcomponent,@quantity,@classtype,@configuration
		end
	close cur
	deallocate cur
end
select * from #tmp2 order by classtype;
drop table #tmp
drop table #tmp2
go


--测试
exec bomlist 'B11F58001SER'
exec bomlist '02-01'
exec bomlist '02'
exec bomlist 'B011B474C1C4304MH'
exec bomlist 'B22A151Q31B0000D1'
exec bomlist 'JFBX4'
exec bomlist '12345678'