



CREATE PROCEDURE DBO.AllUpLinksGuest AS
SET NOCOUNT ON

--***************************************************************************************************************************************************************************************************** AgencyCorp og CompanyCorp

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel der er linket til agencycorp og CompanyCorp
  Select CustomerNumber,CustomerTypeNumber,1 from Customer WITH (NOLOCK) where CustomerTypeNumber=7  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #FoundValues where (CustomerTypeNumber=4 or CustomerTypeNumber=5)) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #FoundValues where (CustomerTypeNumber=4 or CustomerTypeNumber=5)))

--***************************************************************************************************************************************************************************************************** Agent

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel og agency der er linket til Agent
  Select CustomerNumber,CustomerTypeNumber,2 from Customer WITH (NOLOCK) where (CustomerTypeNumber=3 or CustomerTypeNumber=7)  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=6) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=6))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel og agencyCorp der er linket til Agency
  Select CustomerNumber,CustomerTypeNumber,2 from Customer WITH (NOLOCK) where (CustomerTypeNumber=5 or CustomerTypeNumber=7)  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where CustomerTypeNumber=3) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where CustomerTypeNumber=3))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel  der er linket til AgencyCorp
  Select CustomerNumber,CustomerTypeNumber,2 from Customer WITH (NOLOCK) where CustomerTypeNumber=7  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where CustomerTypeNumber=5) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where CustomerTypeNumber=5))

--***************************************************************************************************************************************************************************************************** Agency

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel og  AgencyCorp der er linket til Agency
  Select CustomerNumber,CustomerTypeNumber,3 from Customer WITH (NOLOCK) where (CustomerTypeNumber=5 or CustomerTypeNumber=7)  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=3) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=3))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel der er linket til AgencyCorp
  Select CustomerNumber,CustomerTypeNumber,3 from Customer WITH (NOLOCK) where CustomerTypeNumber=7  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=5 and ProcessingNumber=3)) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=5 and ProcessingNumber=3)))

--***************************************************************************************************************************************************************************************************** Company

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle Agency, CompanyCorp og saleschannel der er linket til Company
  Select CustomerNumber,CustomerTypeNumber,4 from Customer WITH (NOLOCK) where (CustomerTypeNumber=3 or CustomerTypeNumber=4 or CustomerTypeNumber=7)  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=2) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #FoundValues where CustomerTypeNumber=2))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel der er linket til CompanyCorp
  Select CustomerNumber,CustomerTypeNumber,4 from Customer WITH (NOLOCK) where CustomerTypeNumber=7  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=4  and ProcessingNumber=4)) UNION All
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=4  and ProcessingNumber=4)))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel og  AgencyCorp der er linket til Agency
  Select CustomerNumber,CustomerTypeNumber,4 from Customer WITH (NOLOCK) where (CustomerTypeNumber=5 or CustomerTypeNumber=7)  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=3  and ProcessingNumber=4)) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=3 and ProcessingNumber=4)))

Insert #TempTab(CustomerNumber,CustomerTypeNumber,ProcessingNumber) -- Finder alle saleschannel der er linket til AgencyCorp
  Select CustomerNumber,CustomerTypeNumber,4 from Customer WITH (NOLOCK) where CustomerTypeNumber=7  And CustomerNumber In
 (Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=5 and ProcessingNumber=4)) UNION ALL
  Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerNumber in (select CustomerNumber from #TempTab where (CustomerTypeNumber=5 and ProcessingNumber=4)))

--*****************************************************************************************************************************************************************************************************

