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)

IF @CustomerTypeNumber = 4 -- CompanyCorp
BEGIN
  INSERT #FoundValues(CustomerNumber)
  SELECT CustomerNumber 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)
  SELECT CustomerNumber 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)
  SELECT CustomerNumber 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 
