set nocount on

if object_id('tempdb..#lic') is not null
	drop table #lic
create table #lic (id int primary key identity(1,1), licId int, nodeId int, cancelLicId int, done bit default(0))

insert #lic (licId, nodeId)
select distinct
	lh.intLicHistId, lhi.NodeId
from tblLicHist lh
	join tblLicHistItem lhi on lhi.lintLicHistId=lh.intLicHistId and lhi.lintCancelledByLicHistId is null --item is valid
	join tblNode n on n.intNodeId=lhi.NodeId
	join vNodeDisposed nd on nd.NodeId=n.intNodeId	--object is discared
where lh.LicTypeId=4	--OEM
	and lh.intCountChange>0
	and lh.lintCancelLicHistId is not null
	and exists(select 1 from tblLicHist lhc where lhc.lintCancelLicHistId=lh.intLicHistId) --exists at last one invalidated record
	and exists(select 1 from tblLicHist lhc where lh.lintCancelLicHistId=lhc.intLicHistId) --license cancels invalidated record


--assign one of the cancel records to items
declare @licId int, @nodeId int
while exists(select 1 from #lic where done=0)
begin
	select top 1 @licId=licId, @nodeId=nodeId from #lic where done=0

	update top (1) l
	set l.cancelLicId=lhc.intLicHistId
	from tblLicHist lhc
		join #lic l on l.licId=lhc.lintCancelLicHistId and l.licId=@licId and l.nodeId=@nodeId
	where lhc.lintCancelLicHistId=@licId
		and not exists(select 1 from #lic where cancelLicId=lhc.intLicHistId)	--not used on licences to be repaired
		and not exists(select 1 from tblLicHistItem lhi where lhi.lintCancelledByLicHistId=lhc.intLicHistId) -- not used in any cancelled items

	update #lic set done=1 where licId=@licId and nodeId=@nodeId
end

/*
select
	l.*, n.txtName, lh.txtLicName
from #lic l
	join tblNode n on n.intNodeId=l.nodeId
	join tblLicHist lh on lh.intLicHistId=l.licId

return
*/

set nocount off

update lh 
set lh.lintCancelLicHistId=null
from tblLicHist lh
	join #lic l on l.licId=lh.intLicHistId


update lhi
set lhi.lintCancelledByLicHistId=l.cancelLicId
from tblLicHistItem lhi
	join #lic l on l.licId=lhi.lintLicHistId and l.nodeId=lhi.NodeId