if not exists (select 1 from sys.objects where object_id = object_id(N'dbo.vLicHistBase') and type in (N'V'))
	exec ('
	create view dbo.vLicHistBase as
	SELECT
		LH.intLicHistId,
		LH.dteDate,
		LH.txtLicName,
		LH.intCountChange,
		P.txtProductName,
		D.txtResellerCompany AS txtResellerCompany,
		LH.txtLicMode,
		L.txtLang AS txtLicLang,
		LTP.LicType AS txtLicType,
		P.txtProductType,
		CASE WHEN LH.intCountChange<0 THEN 0 ELSE CASE WHEN LH.dteDate>GETUTCDATE() OR LH.dteDateExpire<GETUTCDATE() THEN 0 ELSE CASE WHEN LHC.intCancelledCountChange IS NULL THEN LH.intCountChange ELSE CASE WHEN LH.intCountChange+LHC.intCancelledCountChange>0 THEN LH.intCountChange+LHC.intCancelledCountChange ELSE 0 END END END END AS intCurrentCount,
		CASE WHEN LH.lintCancelLicHistId IS NULL THEN LA.intAssignedCount ELSE 0 END AS intAssignedCount,
		LH.dteDateExpire,
		LH.txtActivationKey,
		LH.txtInventoryNum,
		D.txtId AS txtInvoice,
		P.txtLang,
		CASE WHEN LD.LicHistId IS NULL THEN 0 ELSE 1 END AS bolDowngrade,
		LH.txtMemo
	FROM
		tblLicHist AS LH
		join LicKind lk on lk.id=LH.LicKindId
		INNER JOIN ( 
			SELECT
				P_P.*,
				P_PT.txtProductType, 
				P_C.txtName AS txtCompanyName,
				P_PC.txtProductCategory,
				P_L.txtLang,
				st.Name SamType,
				ps.ProductState ProductState,
				p.sPerson ModifiedPerson
			FROM vProduct AS P_P
				join SamType st on st.id=P_P.SamTypeId
				join ProductState ps on ps.id=P_P.ProductStateId
				left join tPerson p on p.iPersonId=P_P.ProductStateModifiedPersonId
				INNER JOIN (SELECT intProductTypeId,txtProductType FROM vProductType) AS P_PT ON P_PT.intProductTypeId=P_P.lintProductTypeId
				LEFT JOIN tblCompany AS P_C ON P_C.intCompanyId=P_P.lintProducerCompanyId
				LEFT JOIN (SELECT intProductCategoryId,txtProductCategory FROM vProductCategory) AS P_PC ON P_PC.intProductCategoryId=P_P.lintProductCategoryId
				LEFT JOIN (SELECT intNameLangId,txtLang FROM vLang) AS P_L ON P_L.intNameLangId=P_P.lintLangId
		) AS P ON LH.lintProductId=P.intProductId
		LEFT JOIN vLang AS L ON LH.intLanguage=L.intNameLangId
		LEFT JOIN (
			SELECT LicHistId 
			FROM LicDowngrade
			GROUP BY LicHistId
		) AS LD ON LH.intLicHistId=LD.LicHistId
		LEFT JOIN (
			SELECT
				lintCancelLicHistId AS lintLicHistId,
				SUM(intCountChange) AS intCancelledCountChange
			FROM tblLicHist
			WHERE lintCancelLicHistId IS NOT NULL AND RemovedDate IS NULL
			GROUP BY lintCancelLicHistId
		) AS LHC ON LH.intLicHistId=LHC.lintLicHistId
		JOIN LicType AS LTP ON LH.LicTypeId = LTP.id
		LEFT JOIN (
			SELECT
				lintLicHistId,
				COUNT(*) AS intAssignedCount
			FROM tblLicHistItem 
			WHERE NodeId IS NOT NULL
				AND lintCancelledByLicHistId IS NULL
			GROUP BY lintLicHistId
		) AS LA ON LH.intLicHistId=LA.lintLicHistId
		LEFT JOIN tPerson AS U ON LH.CreatedPersonId=U.iPersonId
		LEFT JOIN vLicHist_InvoiceAtomLast AS A ON LH.intLicHistId=A.lintLicHistId
		LEFT JOIN vDocument AS D ON A.lintAtomId=D.lintAtomId
		LEFT JOIN (SELECT lintLicHistId,txtId FROM tblLicHistAtom AS lhat
		INNER JOIN tblDocument AS d ON d.lintAtomId=lhat.lintAtomId and lintDocumentKindId=4
		INNER JOIN tblAsset AS ass ON ass.lintAtomId=lhat.lintAtomId) AS C ON C.lintLicHistId=LH.intLicHistId');

go

alter function dbo.ftLicHistRelations
(
	@licHistId int
)
RETURNS
@ret TABLE
(
	id int,
	LicHistRelationTypeId int,
	ModifiedDate datetime,
	ModifiedByPerson nvarchar(255),
	Straight bit,
	BeginLicHistId int,
	BeginLicHistRelation nvarchar(32),
	BeginDteDate datetime,
	BeginTxtLicName nvarchar(255),
	BeginIntCountChange int,
	BeginTxtProductName nvarchar(255),
	BeginTxtResellerCompany nvarchar(255),
	BeginTxtLicMode nvarchar(255),
	BeginTxtLicLang nvarchar(255),
	BeginTxtLicType nvarchar(255),
	BeginTxtProductType nvarchar(255),
	BeginIntCurrentCount int,
	BeginIntAssignedCount int,
	BeginDteDateExpire datetime,
	BeginTxtActivationKey nvarchar(1024),
	BeginTxtInventoryNum nvarchar(255),
	BeginTxtInvoice nvarchar(255),
	BeginTxtLang nvarchar(255),
	BeginBolDowngrade bit,
	BeginTxtMemo nvarchar(max),
	EndLicHistId int,
	EndLicHistRelation nvarchar(32),
	EndDteDate datetime,
	EndTxtLicName nvarchar(255),
	EndIntCountChange int,
	EndTxtProductName nvarchar(255),
	EndTxtResellerCompany nvarchar(255),
	EndTxtLicMode nvarchar(255),
	EndTxtLicLang nvarchar(255),
	EndTxtLicType nvarchar(255),
	EndTxtProductType nvarchar(255),
	EndIntCurrentCount int,
	EndIntAssignedCount int,
	EndDteDateExpire datetime,
	EndTxtActivationKey nvarchar(1024),
	EndTxtInventoryNum nvarchar(255),
	EndTxtInvoice nvarchar(255),
	EndTxtLang nvarchar(255),
	EndBolDowngrade bit,
	EndTxtMemo nvarchar(max)
)

BEGIN
	declare 
		@beginLicHistId int,
		@endLicHistId int,
		@directional bit

	declare @tmpTable table
	(
		id  int,
		BeginLicHistId int,
		EndLicHistId int,
		Directional bit
	)

	insert into @ret
		select 
			LHR.id,
			LHR.LicHistRelationTypeId,
			LHR.ModifiedDate,
			tPerson.sPerson ModifiedByPerson,
			cast(case when LHR.BeginLicHistId = @licHistId or LHR.EndLicHistId = @licHistId then 1 else 0 end as bit) Straight,
			LHR.BeginLicHistId BeginLicHistId,
			LHR.BeginLicHistRelation BeginLicHistRelation,
			beginLicHist.dteDate BeginDteDate,
			beginLicHist.txtLicName BeginTxtLicName,
			beginLicHist.intCountChange BeginIntCountChange,
			beginLicHist.txtProductName  BeginTxtProductName,
			beginLicHist.txtResellerCompany BeginTxtResellerCompany,
			beginLicHist.txtLicMode BeginTxtLicMode,
			beginLicHist.txtLicLang BeginTxtLicLang,
			beginLicHist.txtLicType BeginTxtLicType,
			beginLicHist.txtProductType BeginTxtProductType,
			beginLicHist.intCurrentCount BeginIntCurrentCount,
			beginLicHist.intAssignedCount BeginIntAssignedCount,
			beginLicHist.dteDateExpire BeginDteDateExpire,
			beginLicHist.txtActivationKey BeginTxtActivationKey,
			beginLicHist.txtInventoryNum BeginTxtInventoryNum,
			beginLicHist.txtInvoice BeginTxtInvoice,
			beginLicHist.txtLang BeginTxtLang,
			beginLicHist.bolDowngrade BeginBolDowngrade,
			beginLicHist.txtMemo BeginTxtMemo,
			LHR.EndLicHistId EndLicHistId,
			LHR.EndLicHistRelation EndLicHistRelation,
			endLicHist.dteDate EndDteDate,
			endLicHist.txtLicName EndTxtLicName,
			endLicHist.intCountChange EndIntCountChange,
			endLicHist.txtProductName  EndTxtProductName,
			endLicHist.txtResellerCompany EndTxtResellerCompany,
			endLicHist.txtLicMode EndTxtLicMode,
			endLicHist.txtLicLang EndTxtLicLang,
			endLicHist.txtLicType EndTxtLicType,
			endLicHist.txtProductType EndTxtProductType,
			endLicHist.intCurrentCount EndIntCurrentCount,
			endLicHist.intAssignedCount EndIntAssignedCount,
			endLicHist.dteDateExpire EndDteDateExpire,
			endLicHist.txtActivationKey EndTxtActivationKey,
			endLicHist.txtInventoryNum EndTxtInventoryNum,
			endLicHist.txtInvoice EndTxtInvoice,
			endLicHist.txtLang EndTxtLang,
			endLicHist.bolDowngrade EndBolDowngrade,
			endLicHist.txtMemo EndTxtMemo
		from
		(
		SELECT core.id, core.LicHistRelationTypeId AS LicHistRelationTypeId, EndName BeginLicHistRelation, BeginName EndLicHistRelation, core.BeginLicHistId, core.EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
		FROM (
			select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
			from LicHistRelation
		) core
		LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
		JOIN tblLicHist LH ON LH.intLicHistId = core.BeginLicHistId AND LH.Valid = 1
		WHERE core.BeginLicHistId = @licHistId
		UNION
		SELECT core.id, core.LicHistRelationTypeId, BeginName BeginLicHistRelation, EndName EndLicHistRelation, core.EndLicHistId as BeginLicHistId, core.BeginLicHistId as EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
		FROM (
			select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
			from LicHistRelation
		) core
		LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
		JOIN tblLicHist LH ON LH.intLicHistId = core.EndLicHistId AND LH.Valid = 1
		WHERE EndLicHistId = @licHistId
		) as LHR
		LEFT JOIN vLicHistBase beginLicHist on beginLicHist.intLicHistId = LHR.BeginLicHistId
		LEFT JOIN vLicHistBase endLicHist on endLicHist.intLicHistId = LHR.EndLicHistId
		left join tPerson on tPerson.iPersonId = LHR.ModifiedByPersonId




	insert into @tmpTable
	select LicHistRelation.id, 0 BeginLicHistId, EndLicHistId, LicHistRelationType.Directional 
	from LicHistRelation 
		left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId 
	where BeginLicHistId = @licHistId
	union
	select LicHistRelation.id, BeginLicHistId, 0 EndLicHistId, LicHistRelationType.Directional 
	from LicHistRelation 
		left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId 
	where EndLicHistId = @licHistId

	declare cur cursor for
	select BeginLicHistId, EndLicHistId, Directional from @tmpTable
	
	open cur

	FETCH NEXT FROM cur INTO @beginLicHistId, @endLicHistId, @directional
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @beginLicHistId = 0 
		begin
			if @directional = 1
			begin
				insert into @ret
				select 
					LHR.id,
					LHR.LicHistRelationTypeId,
					LHR.ModifiedDate,
					tPerson.sPerson ModifiedByPerson,
					cast(case when LHR.BeginLicHistId = @licHistId or LHR.EndLicHistId = @licHistId then 1 else 0 end as bit) Straight,
					LHR.BeginLicHistId BeginLicHistId,
					LHR.BeginLicHistRelation BeginLicHistRelation,
					beginLicHist.dteDate BeginDteDate,
					beginLicHist.txtLicName BeginTxtLicName,
					beginLicHist.intCountChange BeginIntCountChange,
					beginLicHist.txtProductName  BeginTxtProductName,
					beginLicHist.txtResellerCompany BeginTxtResellerCompany,
					beginLicHist.txtLicMode BeginTxtLicMode,
					beginLicHist.txtLicLang BeginTxtLicLang,
					beginLicHist.txtLicType BeginTxtLicType,
					beginLicHist.txtProductType BeginTxtProductType,
					beginLicHist.intCurrentCount BeginIntCurrentCount,
					beginLicHist.intAssignedCount BeginIntAssignedCount,
					beginLicHist.dteDateExpire BeginDteDateExpire,
					beginLicHist.txtActivationKey BeginTxtActivationKey,
					beginLicHist.txtInventoryNum BeginTxtInventoryNum,
					beginLicHist.txtInvoice BeginTxtInvoice,
					beginLicHist.txtLang BeginTxtLang,
					beginLicHist.bolDowngrade BeginBolDowngrade,
					beginLicHist.txtMemo BeginTxtMemo,
					LHR.EndLicHistId EndLicHistId,
					LHR.EndLicHistRelation EndLicHistRelation,
					endLicHist.dteDate EndDteDate,
					endLicHist.txtLicName EndTxtLicName,
					endLicHist.intCountChange EndIntCountChange,
					endLicHist.txtProductName  EndTxtProductName,
					endLicHist.txtResellerCompany EndTxtResellerCompany,
					endLicHist.txtLicMode EndTxtLicMode,
					endLicHist.txtLicLang EndTxtLicLang,
					endLicHist.txtLicType EndTxtLicType,
					endLicHist.txtProductType EndTxtProductType,
					endLicHist.intCurrentCount EndIntCurrentCount,
					endLicHist.intAssignedCount EndIntAssignedCount,
					endLicHist.dteDateExpire EndDteDateExpire,
					endLicHist.txtActivationKey EndTxtActivationKey,
					endLicHist.txtInventoryNum EndTxtInventoryNum,
					endLicHist.txtInvoice EndTxtInvoice,
					endLicHist.txtLang EndTxtLang,
					endLicHist.bolDowngrade EndBolDowngrade,
					endLicHist.txtMemo EndTxtMemo
				from
				(
					SELECT core.id, core.LicHistRelationTypeId , EndName BeginLicHistRelation, BeginName EndLicHistRelation, core.BeginLicHistId, core.EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
					FROM (
						select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
						from LicHistRelation nr
					) core
				LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.BeginLicHistId AND LH.Valid = 1
				WHERE core.BeginLicHistId = @endLicHistId and Directional = @directional
				) as LHR
				LEFT JOIN vLicHistBase beginLicHist on beginLicHist.intLicHistId = LHR.BeginLicHistId
				LEFT JOIN vLicHistBase endLicHist on endLicHist.intLicHistId = LHR.EndLicHistId
				left join tPerson on tPerson.iPersonId = LHR.ModifiedByPersonId
			where not exists (select * from @ret where id = LHR.id)

			insert into @tmpTable
				select LicHistRelation.id, 0 BeginLicHistId, EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where BeginLicHistId = @endLicHistId and Directional = 1 and not exists (select * from @tmpTable where EndLicHistId = LicHistRelation.EndLicHistId or LicHistRelation.id = id)
			end

			else
			begin
				insert into @ret
				select 
					LHR.id,
					LHR.LicHistRelationTypeId,
					LHR.ModifiedDate,
					tPerson.sPerson ModifiedByPerson,
					cast(case when LHR.BeginLicHistId = @licHistId or LHR.EndLicHistId = @licHistId then 1 else 0 end as bit) Straight,
					LHR.BeginLicHistId BeginLicHistId,
					LHR.BeginLicHistRelation BeginLicHistRelation,
					beginLicHist.dteDate BeginDteDate,
					beginLicHist.txtLicName BeginTxtLicName,
					beginLicHist.intCountChange BeginIntCountChange,
					beginLicHist.txtProductName  BeginTxtProductName,
					beginLicHist.txtResellerCompany BeginTxtResellerCompany,
					beginLicHist.txtLicMode BeginTxtLicMode,
					beginLicHist.txtLicLang BeginTxtLicLang,
					beginLicHist.txtLicType BeginTxtLicType,
					beginLicHist.txtProductType BeginTxtProductType,
					beginLicHist.intCurrentCount BeginIntCurrentCount,
					beginLicHist.intAssignedCount BeginIntAssignedCount,
					beginLicHist.dteDateExpire BeginDteDateExpire,
					beginLicHist.txtActivationKey BeginTxtActivationKey,
					beginLicHist.txtInventoryNum BeginTxtInventoryNum,
					beginLicHist.txtInvoice BeginTxtInvoice,
					beginLicHist.txtLang BeginTxtLang,
					beginLicHist.bolDowngrade BeginBolDowngrade,
					beginLicHist.txtMemo BeginTxtMemo,
					LHR.EndLicHistId EndLicHistId,
					LHR.EndLicHistRelation EndLicHistRelation,
					endLicHist.dteDate EndDteDate,
					endLicHist.txtLicName EndTxtLicName,
					endLicHist.intCountChange EndIntCountChange,
					endLicHist.txtProductName  EndTxtProductName,
					endLicHist.txtResellerCompany EndTxtResellerCompany,
					endLicHist.txtLicMode EndTxtLicMode,
					endLicHist.txtLicLang EndTxtLicLang,
					endLicHist.txtLicType EndTxtLicType,
					endLicHist.txtProductType EndTxtProductType,
					endLicHist.intCurrentCount EndIntCurrentCount,
					endLicHist.intAssignedCount EndIntAssignedCount,
					endLicHist.dteDateExpire EndDteDateExpire,
					endLicHist.txtActivationKey EndTxtActivationKey,
					endLicHist.txtInventoryNum EndTxtInventoryNum,
					endLicHist.txtInvoice EndTxtInvoice,
					endLicHist.txtLang EndTxtLang,
					endLicHist.bolDowngrade EndBolDowngrade,
					endLicHist.txtMemo EndTxtMemo
				from
				(
				SELECT core.id, core.LicHistRelationTypeId AS LicHistRelationTypeId, EndName BeginLicHistRelation, BeginName EndLicHistRelation, core.BeginLicHistId, core.EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
				FROM (
					select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
					from LicHistRelation lhr
				) core
				LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.BeginLicHistId AND LH.Valid = 1
				WHERE BeginLicHistId = @endLicHistId and Directional = @directional
				UNION
				SELECT core.id, core.LicHistRelationTypeId, BeginName BeginLicHistRelation, EndName EndLicHistRelation, core.EndLicHistId as BeginLicHistId, core.BeginLicHistId as EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
				FROM (
					select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
					from LicHistRelation lhr
				) core
				LEFT JOIN LicHistRelationType ON LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.EndLicHistId AND LH.Valid = 1
				WHERE EndLicHistId = @endLicHistId and Directional = @directional
				) as LHR
				LEFT JOIN vLicHistBase beginLicHist on beginLicHist.intLicHistId = LHR.BeginLicHistId
				LEFT JOIN vLicHistBase endLicHist on endLicHist.intLicHistId = LHR.EndLicHistId
				left join tPerson on tPerson.iPersonId = LHR.ModifiedByPersonId
			where not exists (select * from @ret where id = LHR.id)

			insert into @tmpTable
			select LicHistRelation.id, 0 BeginLicHistId, EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where BeginLicHistId = @endLicHistId and Directional = 0 and not exists (select * from @tmpTable where EndLicHistId = LicHistRelation.EndLicHistId or LicHistRelation.id = id)
			union
			select LicHistRelation.id, BeginLicHistId, 0 EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where EndLicHistId = @endLicHistId and Directional = 0 and not exists (select * from @tmpTable where BeginLicHistId = LicHistRelation.BeginLicHistId or LicHistRelation.id = id)
			end
		
		end
	
		if @endLicHistId = 0
		begin
			if @directional = 1
			begin
				insert into @ret
				select 
					LHR.id,
					LHR.LicHistRelationTypeId,
					LHR.ModifiedDate,
					tPerson.sPerson ModifiedByPerson,
					cast(case when LHR.BeginLicHistId = @licHistId or LHR.EndLicHistId = @licHistId then 1 else 0 end as bit) Straight,
					LHR.BeginLicHistId BeginLicHistId,
					LHR.BeginLicHistRelation BeginLicHistRelation,
					beginLicHist.dteDate BeginDteDate,
					beginLicHist.txtLicName BeginTxtLicName,
					beginLicHist.intCountChange BeginIntCountChange,
					beginLicHist.txtProductName  BeginTxtProductName,
					beginLicHist.txtResellerCompany BeginTxtResellerCompany,
					beginLicHist.txtLicMode BeginTxtLicMode,
					beginLicHist.txtLicLang BeginTxtLicLang,
					beginLicHist.txtLicType BeginTxtLicType,
					beginLicHist.txtProductType BeginTxtProductType,
					beginLicHist.intCurrentCount BeginIntCurrentCount,
					beginLicHist.intAssignedCount BeginIntAssignedCount,
					beginLicHist.dteDateExpire BeginDteDateExpire,
					beginLicHist.txtActivationKey BeginTxtActivationKey,
					beginLicHist.txtInventoryNum BeginTxtInventoryNum,
					beginLicHist.txtInvoice BeginTxtInvoice,
					beginLicHist.txtLang BeginTxtLang,
					beginLicHist.bolDowngrade BeginBolDowngrade,
					beginLicHist.txtMemo BeginTxtMemo,
					LHR.EndLicHistId EndLicHistId,
					LHR.EndLicHistRelation EndLicHistRelation,
					endLicHist.dteDate EndDteDate,
					endLicHist.txtLicName EndTxtLicName,
					endLicHist.intCountChange EndIntCountChange,
					endLicHist.txtProductName  EndTxtProductName,
					endLicHist.txtResellerCompany EndTxtResellerCompany,
					endLicHist.txtLicMode EndTxtLicMode,
					endLicHist.txtLicLang EndTxtLicLang,
					endLicHist.txtLicType EndTxtLicType,
					endLicHist.txtProductType EndTxtProductType,
					endLicHist.intCurrentCount EndIntCurrentCount,
					endLicHist.intAssignedCount EndIntAssignedCount,
					endLicHist.dteDateExpire EndDteDateExpire,
					endLicHist.txtActivationKey EndTxtActivationKey,
					endLicHist.txtInventoryNum EndTxtInventoryNum,
					endLicHist.txtInvoice EndTxtInvoice,
					endLicHist.txtLang EndTxtLang,
					endLicHist.bolDowngrade EndBolDowngrade,
					endLicHist.txtMemo EndTxtMemo
				from
				(
					SELECT core.id, core.LicHistRelationTypeId, BeginName BeginLicHistRelation, EndName EndLicHistRelation, core.EndLicHistId as BeginLicHistId, core.BeginLicHistId as EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
					FROM (
						select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
						from LicHistRelation lhr
				) core
				LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.EndLicHistId AND LH.Valid = 1
				WHERE EndLicHistId = @beginLicHistId and Directional = @directional
				) as LHR
				LEFT JOIN vLicHistBase beginLicHist on beginLicHist.intLicHistId = LHR.BeginLicHistId
				LEFT JOIN vLicHistBase endLicHist on endLicHist.intLicHistId = LHR.EndLicHistId
				left join tPerson on tPerson.iPersonId = LHR.ModifiedByPersonId
				where not exists (select * from @ret where id = LHR.id)

				insert into @tmpTable
				select LicHistRelation.id, BeginLicHistId, 0 EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where EndLicHistId = @beginLicHistId and Directional = 1 and not exists (select * from @tmpTable where BeginLicHistId = LicHistRelation.BeginLicHistId or LicHistRelation.id = id)
			end

			else
			begin
				insert into @ret
				select 
					LHR.id,
					LHR.LicHistRelationTypeId,
					LHR.ModifiedDate,
					tPerson.sPerson ModifiedByPerson,
					cast(case when LHR.BeginLicHistId = @licHistId or LHR.EndLicHistId = @licHistId then 1 else 0 end as bit) Straight,
					LHR.BeginLicHistId BeginLicHistId,
					LHR.BeginLicHistRelation BeginLicHistRelation,
					beginLicHist.dteDate BeginDteDate,
					beginLicHist.txtLicName BeginTxtLicName,
					beginLicHist.intCountChange BeginIntCountChange,
					beginLicHist.txtProductName  BeginTxtProductName,
					beginLicHist.txtResellerCompany BeginTxtResellerCompany,
					beginLicHist.txtLicMode BeginTxtLicMode,
					beginLicHist.txtLicLang BeginTxtLicLang,
					beginLicHist.txtLicType BeginTxtLicType,
					beginLicHist.txtProductType BeginTxtProductType,
					beginLicHist.intCurrentCount BeginIntCurrentCount,
					beginLicHist.intAssignedCount BeginIntAssignedCount,
					beginLicHist.dteDateExpire BeginDteDateExpire,
					beginLicHist.txtActivationKey BeginTxtActivationKey,
					beginLicHist.txtInventoryNum BeginTxtInventoryNum,
					beginLicHist.txtInvoice BeginTxtInvoice,
					beginLicHist.txtLang BeginTxtLang,
					beginLicHist.bolDowngrade BeginBolDowngrade,
					beginLicHist.txtMemo BeginTxtMemo,
					LHR.EndLicHistId EndLicHistId,
					LHR.EndLicHistRelation EndLicHistRelation,
					endLicHist.dteDate EndDteDate,
					endLicHist.txtLicName EndTxtLicName,
					endLicHist.intCountChange EndIntCountChange,
					endLicHist.txtProductName  EndTxtProductName,
					endLicHist.txtResellerCompany EndTxtResellerCompany,
					endLicHist.txtLicMode EndTxtLicMode,
					endLicHist.txtLicLang EndTxtLicLang,
					endLicHist.txtLicType EndTxtLicType,
					endLicHist.txtProductType EndTxtProductType,
					endLicHist.intCurrentCount EndIntCurrentCount,
					endLicHist.intAssignedCount EndIntAssignedCount,
					endLicHist.dteDateExpire EndDteDateExpire,
					endLicHist.txtActivationKey EndTxtActivationKey,
					endLicHist.txtInventoryNum EndTxtInventoryNum,
					endLicHist.txtInvoice EndTxtInvoice,
					endLicHist.txtLang EndTxtLang,
					endLicHist.bolDowngrade EndBolDowngrade,
					endLicHist.txtMemo EndTxtMemo
				from
				(
				SELECT core.id, core.LicHistRelationTypeId, EndName BeginLicHistRelation, BeginName EndLicHistRelation, core.BeginLicHistId, core.EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
				FROM (
					select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
					from LicHistRelation lhr
				) core
				LEFT JOIN LicHistRelationType ON core.LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.BeginLicHistId AND LH.Valid = 1
				WHERE BeginLicHistId = @beginLicHistId and Directional = @directional
				UNION
				SELECT core.id, core.LicHistRelationTypeId, BeginName BeginLicHistRelation, EndName EndLicHistRelation, core.EndLicHistId as BeginLicHistId, core.BeginLicHistId as EndLicHistId, core.ModifiedDate, core.ModifiedByPersonId
				FROM (
					select id, LicHistRelationTypeId, BeginLicHistId, EndLicHistId, ModifiedDate, ModifiedByPersonId
					from LicHistRelation lhr
				) core
				LEFT JOIN LicHistRelationType ON LicHistRelationTypeId = LicHistRelationType.id
				JOIN tblLicHist LH ON LH.intLicHistId = core.EndLicHistId AND LH.Valid = 1
				WHERE EndLicHistId = @beginLicHistId and Directional = @directional
				) as LHR
				LEFT JOIN vLicHistBase beginLicHist on beginLicHist.intLicHistId = LHR.BeginLicHistId
				LEFT JOIN vLicHistBase endLicHist on endLicHist.intLicHistId = LHR.EndLicHistId
				left join tPerson on tPerson.iPersonId = LHR.ModifiedByPersonId
				where not exists (select * from @ret where id = LHR.id)

				insert into @tmpTable
				select LicHistRelation.id, 0 BeginLicHistId, EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where BeginLicHistId = @beginLicHistId and Directional = 0 and not exists (select * from @tmpTable where EndLicHistId = LicHistRelation.EndLicHistId or LicHistRelation.id = id)
				union
				select LicHistRelation.id, BeginLicHistId, 0 EndLicHistId, LicHistRelationType.Directional from LicHistRelation left join LicHistRelationType on LicHistRelationType.id = LicHistRelation.LicHistRelationTypeId where EndLicHistId = @beginLicHistId and Directional = 0 and not exists (select * from @tmpTable where BeginLicHistId = LicHistRelation.BeginLicHistId or LicHistRelation.id = id)
			end
		end
	
		FETCH NEXT FROM cur INTO @beginLicHistId, @endLicHistId, @directional
	END
	
	close cur
	deallocate cur
	
	RETURN
END