declare @Site varchar(50), @PartNumber varchar(20), 
	@MaterialName varchar(200), @ComponentMaterialName varchar(200), 
	@ComponentLotNumber varchar(20), @ComponentSerialNumber varchar(20),
	@UsageDate datetime, @ExpirationDate datetime, @SubAssemblyExpirationDate datetime, @SubSerialNumber varchar(20)
	
declare @refid uniqueidentifier
set @refid=null
declare getRef cursor for select top 1000 refid from tempSourcerefId
where processed=0
open getRef
fetch next from getRef into @refid
while @@fetch_status = 0 begin
	set @Site='' 
	set @PartNumber =''
	set	@MaterialName ='' 
	set @SubAssemblyExpirationDate = null
	set @SubSerialNumber=''
	--get subassembly info
	select @Site=s.Name, @PartNumber=m.PartNumber, @materialName=m.MaterialName, @SubSerialNumber =sl.SerialNumber,
		@SubAssemblyExpirationDate= coalesce(l.ExpirationDate, sl.ExpirationDate)
		from material m join lot l on m.materialid=l.materialid
		join sublot sl on l.lotid=sl.lotid
		join site s on s.siteid=sl.siteid
		where sl.sublotid=@refid
	
	--get subassembly usage dates
	declare @subUsageTbl table (UsageDate datetime)
	insert into @subUsageTbl select consumedate from itemconsumption where sublotid=@refid
	
	--get component		
	declare @componentTbl table (Componentid uniqueidentifier)
	insert into @componentTbl select sublotid from itemconsumption where referenceid=@refid
	
	declare @sublotid uniqueidentifier
	set @sublotid=null
	declare getCom cursor for select distinct componentid from @componentTbl
	open getCom
	fetch next from getCom into @sublotid
	while @@fetch_status=0 begin
		set @ExpirationDate = null
		select @ExpirationDate = coalesce(l.ExpirationDate, sl.ExpirationDate)
			from lot l join sublot sl on l.lotid =sl.lotid
			where sl.sublotid =@sublotid
		if @ExpirationDate is not null begin
			if exists(select * from @subUsageTbl where 
				dateadd(dd, datediff(dd, 0, UsageDate)+0, 0) > @ExpirationDate)
			begin
				set @ComponentMaterialName ='' 
				set @ComponentLotNumber =''
				set @ComponentSerialNumber =''
				
				select @ComponentMaterialName = m.MaterialName, 
					@ComponentLotNumber =l.LotNumber, @ComponentSerialNumber =sl.SerialNumber
					from Material m join lot l on m.materialid=l.materialid
					join sublot sl on l.lotid =sl.lotid
					where sl.sublotid =@sublotid
				
				set @UsageDate = null
				declare getUsageDates cursor for select UsageDate from @subUsageTbl
					where dateadd(dd, datediff(dd, 0, UsageDate)+0, 0) > @ExpirationDate
				open getUsageDates
				fetch next from getUsageDates into @UsageDate
				while @@fetch_status=0 begin
					insert into tempOutputSubs values (@Site, @PartNumber, 
						@MaterialName, @SubSerialNumber, @ComponentMaterialName, 
						@ComponentLotNumber, @ComponentSerialNumber,
						@UsageDate, @SubAssemblyExpirationDate, @ExpirationDate)
			
					set @UsageDate = null
					fetch next from getUsageDates into @UsageDate
				end
				close getUsageDates
				deallocate getUsageDates
			end		
		end
		set @sublotid=null
		fetch next from getCom into @sublotid
	end
	close getCom
	deallocate getCom
	
	delete @subUsageTbl
	delete @componentTbl
	
	update tempSourceRefid set processed=1 where refid=@refid
	set @refid=null
	fetch next from getRef into @refid
end
close getRef
deallocate getRef