CREATE PROCEDURE dbo.FindOrderBookCustomerLinksCSV @CustomerNumberCSV VARCHAR(MAX) AS
SET NOCOUNT ON

CREATE TABLE #FoundValues (CustomerNumber INT NOT NULL)
	
DECLARE @LCustomerNumber INT
DECLARE @LCustomerTypeNumber SMALLINT

DECLARE #Cur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT Number, Customer.CustomerTypeNumber
  FROM DBO.iter_intlist_to_table_NO_Dummy(@CustomerNumberCSV) IntList
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = IntList.Number
OPEN #Cur

FETCH NEXT FROM #Cur INTO @LCustomerNumber, @LCustomerTypeNumber
  
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #FoundValues VALUES (@LCustomerNumber)

  IF @LCustomerTypeNumber = 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 = @LCustomerNumber 
      UNION ALL
      SELECT CustomerLinkNumber FROM CustomerLinks WITH (NOLOCK) WHERE LinkTypeNumber = 0 AND CustomerNumber = @LCustomerNumber
    )
  END
  ELSE IF @LCustomerTypeNumber = 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 = @LCustomerNumber
      UNION ALL
      SELECT CustomerLinkNumber FROM CustomerLinks WITH (NOLOCK) WHERE LinkTypeNumber = 0 AND CustomerNumber = @LCustomerNumber
    )
  END
  ELSE IF @LCustomerTypeNumber = 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 = @LCustomerNumber 
      UNION ALL
      SELECT CustomerLinkNumber FROM CustomerLinks WITH (NOLOCK) WHERE LinkTypeNumber = 0 AND CustomerNumber = @LCustomerNumber
    )
  END
  FETCH NEXT FROM #Cur INTO @LCustomerNumber, @LCustomerTypeNumber
END

SELECT CustomerNumber FROM #FoundValues

DROP TABLE #FoundValues 

CLOSE #Cur
DEALLOCATE #Cur