CREATE PROCEDURE dbo.FindOrderBookCustomerLinks ( @CustomerNumber int)

AS
	SET NOCOUNT ON
	
Declare @CustomerTypeNumber SmallInt

select @CustomerTypeNumber = CustomerTypeNumber from Customer WITH (NOLOCK) where CustomerNumber = @CustomerNumber


Create Table #FoundValues (CustomerNumber int NOT NULL,
                                              CustomerTypeNumber Smallint NOT NULL)

If  @CustomerTypeNumber=4 -- CompanyCorp
begin
	Insert #FoundValues(CustomerNumber,CustomerTypeNumber)
	select CustomerNumber,CustomerTypeNumber from Customer WITH (NOLOCK) where CustomerTypeNumber=2 and MarkAsDeleted = 0 and CustomerNumber in
	(Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber=@CustomerNumber UNION ALL
	Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and  CustomerNumber=@CustomerNumber)
end
else
If  @CustomerTypeNumber=5  -- AgencyCorp
begin
	Insert #FoundValues(CustomerNumber,CustomerTypeNumber)
	select CustomerNumber,CustomerTypeNumber from Customer WITH (NOLOCK) where CustomerTypeNumber=3 and MarkAsDeleted = 0 and CustomerNumber in
	(Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber=@CustomerNumber UNION ALL
	Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and  CustomerNumber=@CustomerNumber)
end
else
If  @CustomerTypeNumber=7  -- SalesChannel
begin
	Insert #FoundValues(CustomerNumber,CustomerTypeNumber)
	select CustomerNumber,CustomerTypeNumber from Customer WITH (NOLOCK) where CustomerTypeNumber in (1,2,3,4,5,6) and MarkAsDeleted = 0 and CustomerNumber in
	(Select CustomerNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and CustomerLinkNumber=@CustomerNumber UNION ALL
	Select CustomerLinkNumber from CustomerLinks WITH (NOLOCK) where LinkTypeNumber=0 and  CustomerNumber=@CustomerNumber)
end;

select CustomerNumber from #FoundValues

Drop Table #FoundValues 
