CREATE PROCEDURE dbo.ArrangementCalcLinkedWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumber INT, @AUseTimeFields BIT = 0, @ASearchType INT = 0, @AReferenceNumberCSV VARCHAR(1000) = '' AS
/*
--CREATED AV 2008 1011 Used in the lower ware plan on the hall days plan in booking module.
Changes:
  RN 20250206: Use hotelnumber from arrangementlines departments instead of ReservationMain's hotelnumber
  KJ 20181002: Wrong number of wares if All selected in hall days plan. Case #CR153298
  KJ 20170712: Ext: Search for refno and Main course refno.
  KJ 20170117: Req #3286. Added Warelink group to Arrangement -> Weekplan report
  RN 20160803:  CR110139, Fixed random error messages that Time and DepartureTime columns was missing
  RN 20160525: CR103764, Fixed wrong join resulting in "NEW" reservations was not in the result
  RN 20160309: CR099531, Do not include main course reservations in result.
  BA 20160304: CR099649/Fixing error at Hooks hotel: Time and DepartureTime cannot be found.
  RN 20160208: Changed to choose if arrangement arrival/departure time should be included.
  RN 20150717: Added Date in resulting datasets sort order. #CR090375
  KJ 20150624: Does not show empty warelink groups. #CR089462
  RN 20150623:BUG: Added WareLinkGroup SortNumber. #CR089498
  KJ 20150609:Bug: Fix for "Wrong number of pieces". Case #CR088798 + more
  KJ 20150603: Total rewrite of sp.
--Last changed AV 2009 0407 fix for cannot insert the value null into column pieces1
*/
DECLARE @IncludeTentativeInArrReports BIT
DECLARE @InternalCourseProduction BIT
DECLARE @HotelCustomerNumber INT
DECLARE @AStartDate CHAR(8)
DECLARE @AEndDate CHAR (8)
DECLARE @BookMixedResources BIT

SET @AStartDate = CONVERT(CHAR(8), @AStartDatetime, 112)
SET @AEndDate = CONVERT(CHAR(8), @AEndDatetime, 112)

SELECT @HotelCustomerNumber = CustomerNumber
FROM Customer WITH (NOLOCK) 
WHERE HotelNumber = @AHotelNumber
AND LocalHotel=1 
AND CustomerTypeNumber = 7
AND ISNULL(MarkAsDeleted, 0) = 0

SELECT @IncludeTentativeInArrReports = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeTentativeInArrReports'
SET @InternalCourseProduction = 0
SELECT @InternalCourseProduction = dbo.GetHotelSetupKeyValue('InternalCourseProduction', @HotelCustomerNumber)
SELECT @BookMixedResources = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BookMixedResourcesEnabled'

IF (@AUseTimeFields = 0)
BEGIN
  IF (@InternalCourseProduction = 0)
  BEGIN
    IF @BookMixedResources = 0
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr 
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
      AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))
    
      SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr ON #tmpArr.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, Date

      DROP TABLE #tmpArr
	END
	ELSE
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr1 
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN ArrangementLineDepartmentXTab WITH (NOLOCK) ON ArrangementLineDepartmentXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLineDepartmentXTab.FK_DepartmentNumber --AND Departments.HotelNumber = @AHotelNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND ((@IncludeTentativeInArrReports = 1) OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND Departments.HotelNumber = @AHotelNumber))
        AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))
    
      SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr1 ON #tmpArr1.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, Date

      DROP TABLE #tmpArr1
	END
  END
  ELSE
  BEGIN
    IF @BookMixedResources = 0
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr2
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

	    AND (
        (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
        OR
        (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
        SELECT FK_ReferenceNumberSub
        FROM ReservationReservationXtab WITH (NOLOCK)
        WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
        )))
      )

      SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr2 ON #tmpArr2.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, Date

      DROP TABLE #tmpArr2
	END
	ELSE
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr3
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
	  INNER JOIN ArrangementLineDepartmentXTab WITH (NOLOCK) ON ArrangementLineDepartmentXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLineDepartmentXTab.FK_DepartmentNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND (ISNULL(Ware.WareLinkNumber, 0) > 0)
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND Departments.HotelNumber = @AHotelNumber))

	    AND (
        (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
        OR
        (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
        SELECT FK_ReferenceNumberSub
        FROM ReservationReservationXtab WITH (NOLOCK)
        WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
        )))
      )

      SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr3 ON #tmpArr3.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, Date

      DROP TABLE #tmpArr3
	END
  END
END
ELSE
BEGIN
  IF (@InternalCourseProduction = 0)
  BEGIN
    IF @BookMixedResources = 1
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, ArrangementLines.Time, ISNULL(ArrangementLines.DepartureTime, '') AS DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr4
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
	  INNER JOIN ArrangementLineDepartmentXTab WITH (NOLOCK) ON ArrangementLineDepartmentXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLineDepartmentXTab.FK_DepartmentNumber --AND Departments.HotelNumber = @AHotelNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
  --    AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND Departments.HotelNumber = @AHotelNumber))
        AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))

      SELECT SUM(Pieces) AS Pieces, #tmpArr4.[Date], #tmpArr4.[Time], #tmpArr4.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr4 ON #tmpArr4.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, #tmpArr4.[Time], #tmpArr4.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, #tmpArr4.[Date], #tmpArr4.[Time]
  
      DROP TABLE #tmpArr4
	END
	ELSE
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.Date, ArrangementLines.Time, ISNULL(ArrangementLines.DepartureTime, '') AS DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr5
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
        AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))

      SELECT SUM(Pieces) AS Pieces, #tmpArr5.[Date], #tmpArr5.[Time], #tmpArr5.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr5 ON #tmpArr5.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, #tmpArr5.[Time], #tmpArr5.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, #tmpArr5.[Date], #tmpArr5.[Time]
  
      DROP TABLE #tmpArr5
	END
  END
  ELSE
  BEGIN
    IF @BookMixedResources = 1
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.[Date], ArrangementLines.[Time], ISNULL(ArrangementLines.DepartureTime, '') AS DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr6
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
	  INNER JOIN ArrangementLineDepartmentXTab WITH (NOLOCK) ON ArrangementLineDepartmentXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLineDepartmentXTab.FK_DepartmentNumber --AND Departments.HotelNumber = @AHotelNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber

	--INNER JOIN ReservationReservationXtab WITH (NOLOCK) ON ReservationReservationXtab.FK_ReferenceNumberSub = ArrangementReservationsXtab.ReferenceNumber
	--  AND ReservationReservationXtab.FK_ReferenceNumber <> ReservationReservationXtab.FK_ReferenceNumberSub

      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate AND Arrangementlines.Date <= @AEndDate
--      AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND Departments.HotelNumber = @AHotelNumber))
	    AND (
        (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
        OR
        (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
          SELECT FK_ReferenceNumberSub
          FROM ReservationReservationXtab WITH (NOLOCK)
          WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
        )))
      )

      SELECT SUM(Pieces) AS Pieces, #tmpArr6.[Date], #tmpArr6.[Time], #tmpArr6.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr6 ON #tmpArr6.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, #tmpArr6.[Time], #tmpArr6.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, #tmpArr6.[Date], #tmpArr6.[Time]
  
      DROP TABLE #tmpArr6
	END
	ELSE
	BEGIN
      SELECT DISTINCT
        ArrangementLines.Pieces, 
        ArrangementLines.[Date], ArrangementLines.[Time], ISNULL(ArrangementLines.DepartureTime, '') AS DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
      INTO #tmpArr7
      FROM ArrangementReservationsXtab WITH (nolock) 
      INNER JOIN RESERVATIONS WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
      INNER JOIN ArrangementLines WITH (nolock) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
	  INNER JOIN ArrangementLineDepartmentXTab WITH (NOLOCK) ON ArrangementLineDepartmentXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
	  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLineDepartmentXTab.FK_DepartmentNumber --AND Departments.HotelNumber = @AHotelNumber
      INNER JOIN Ware WITH (nolock) ON ArrangementLines.WareNumber = Ware.WareNumber AND ISNULL(Ware.WareLinkNumber, 0) > 0 
      INNER JOIN ReservationMain WITH (nolock) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber

	--INNER JOIN ReservationReservationXtab WITH (NOLOCK) ON ReservationReservationXtab.FK_ReferenceNumberSub = ArrangementReservationsXtab.ReferenceNumber
	--  AND ReservationReservationXtab.FK_ReferenceNumber <> ReservationReservationXtab.FK_ReferenceNumberSub

      INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber AND Status NOT IN (7,8) AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
      WHERE ArrangementLines.Date >= @AStartDate AND Arrangementlines.Date <= @AEndDate
        AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
	    AND (
        (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
        OR
        (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
          SELECT FK_ReferenceNumberSub
          FROM ReservationReservationXtab WITH (NOLOCK)
          WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
        )))
      )

      SELECT SUM(Pieces) AS Pieces, #tmpArr7.[Date], #tmpArr7.[Time], #tmpArr7.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
      FROM WareLinkGroup WITH (NOLOCK)
      LEFT OUTER JOIN #tmpArr7 ON #tmpArr7.WareLinkNumber = WareLinkGroup.Id
      GROUP BY Date, #tmpArr7.[Time], #tmpArr7.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
      ORDER BY WareLinkGroup.SortNumber, #tmpArr7.[Date], #tmpArr7.[Time]
  
      DROP TABLE #tmpArr7
	END
  END
END