CREATE PROCEDURE dbo.ProductReportWithPackInfo @AFromDate DATETIME, @AToDate DATETIME, @AProductNumberCSV VARCHAR(1000), @ADayProduct BIT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen - April 2017
  Changes:

*/

CREATE TABLE #PackageInfo
(
  View_DateStamp DATETIME,
  View_Id INT,
  View_FK_ProductNumber INT,
  View_PacketType INT
)

INSERT INTO #PackageInfo
  EXEC ProductPricePacketInfoCSV @AFromDate, @AToDate, @AProductNumberCSV

SELECT DISTINCT Products.ProductNumber, Products.ProductName, Products.Description, ProductPrice.DateStamp,
(
  SELECT Top 1 ProductPricePacket.FK_PacketNumber
  FROM #PackageInfo WITH (NOLOCK)
  INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
  INNER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = ProductPricePacket.FK_PacketNumber
  WHERE FK_ProductNumber = ProductPrice.ProductNumber
  AND #PackageInfo.View_DateStamp = ProductPrice.DateStamp
  AND ISNULL(ProductPricePacket.FK_PacketNumber, 0) > 0
  AND PacketType = 0
  ORDER BY Id DESC
) AS ArrivalPacketNumber,
(
  SELECT Top 1 ProductPricePacket.FK_PacketNumber
  FROM #PackageInfo WITH (NOLOCK)
  INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
  INNER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = ProductPricePacket.FK_PacketNumber
  WHERE FK_ProductNumber = ProductPrice.ProductNumber
  AND #PackageInfo.View_DateStamp = ProductPrice.DateStamp
  AND ISNULL(ProductPricePacket.FK_PacketNumber, 0) > 0
  AND PacketType = 1
  ORDER BY Id DESC
) AS StayPacketNumber,    
(
  SELECT Top 1 ProductPricePacket.FK_PacketNumber
  FROM #PackageInfo WITH (NOLOCK)
  INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
  INNER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = ProductPricePacket.FK_PacketNumber
  WHERE FK_ProductNumber = ProductPrice.ProductNumber
  AND #PackageInfo.View_DateStamp = ProductPrice.DateStamp
  AND ISNULL(ProductPricePacket.FK_PacketNumber, 0) > 0
  AND PacketType = 2
  ORDER BY Id DESC
) AS DeparturePacketNumber
INTO #ProductInfo
FROM Products WITH (NOLOCK)
INNER JOIN ProductPrice WITH (NOLOCK) ON ProductPrice.ProductNumber = Products.ProductNumber
WHERE Products.ProductNumber IN (SELECT Number FROM dbo.iter_intlist_to_table(@AProductNumberCSV)) 
AND (DateStamp >= @AFromDate AND DateStamp <= @AToDate)
AND DayProduct = @ADayProduct

SELECT #ProductInfo.*, 0 AS PackageType, Packet.Text AS PackageName,
PacketItem_NEW.PacketItemNumber, PacketItem_NEW.PacketNumber, PacketItem_NEW.WareNumber, PacketItem_NEW.TextOverwrite,
PacketItem_NEW.SortNumber, PacketItem_NEW.Time, WareGroups.SortNumber AS WareGroupSortNumber, Ware.SortNumber AS WareSortNumber,
PacketItem_NEW.PriceOverwrite, PacketItem_NEW.DebType, PacketItem_NEW.Arrangement, Ware.ResultAccountNumber
INTO #Result
FROM #ProductInfo
LEFT OUTER JOIN PacketItem_NEW WITH (NOLOCK) ON PacketItem_NEW.PacketNumber = #ProductInfo.ArrivalPacketNumber
LEFT OUTER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = PacketItem_NEW.WareNumber
LEFT OUTER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
LEFT OUTER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = PacketItem_NEW.PacketNumber

INSERT INTO #Result
SELECT #ProductInfo.*, 1 AS PackageType, Packet.Text AS PackageName,
PacketItem_NEW.PacketItemNumber, PacketItem_NEW.PacketNumber, PacketItem_NEW.WareNumber, PacketItem_NEW.TextOverwrite,
PacketItem_NEW.SortNumber, PacketItem_NEW.Time, WareGroups.SortNumber AS WareGroupSortNumber, Ware.SortNumber AS WareSortNumber,
PacketItem_NEW.PriceOverwrite, PacketItem_NEW.DebType, PacketItem_NEW.Arrangement, Ware.ResultAccountNumber
FROM #ProductInfo
LEFT OUTER JOIN PacketItem_NEW WITH (NOLOCK) ON PacketItem_NEW.PacketNumber = #ProductInfo.StayPacketNumber
LEFT OUTER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = PacketItem_NEW.WareNumber
LEFT OUTER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
LEFT OUTER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = PacketItem_NEW.PacketNumber
UNION
SELECT #ProductInfo.*, 2 AS PackageType, Packet.Text AS PackageName,
PacketItem_NEW.PacketItemNumber, PacketItem_NEW.PacketNumber, PacketItem_NEW.WareNumber, PacketItem_NEW.TextOverwrite,
PacketItem_NEW.SortNumber, PacketItem_NEW.Time, WareGroups.SortNumber AS WareGroupSortNumber, Ware.SortNumber AS WareSortNumber,
PacketItem_NEW.PriceOverwrite, PacketItem_NEW.DebType, PacketItem_NEW.Arrangement, Ware.ResultAccountNumber
FROM #ProductInfo
LEFT OUTER JOIN PacketItem_NEW WITH (NOLOCK) ON PacketItem_NEW.PacketNumber = #ProductInfo.DeparturePacketNumber
LEFT OUTER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = PacketItem_NEW.WareNumber
LEFT OUTER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
LEFT OUTER JOIN Packet WITH (NOLOCK) ON Packet.PacketNumber = PacketItem_NEW.PacketNumber

SELECT ProductNumber, ProductName, Description, DateStamp, 
PackageType, PackageName,
PacketItemNumber, PacketNumber, WareNumber, TextOverwrite, SortNumber, Time, WareGroupSortNumber, WareSortNumber,
PriceOverwrite, DebType, Arrangement, ResultAccountNumber,
dbo.GetPacketItemDepartmentStr(PacketItemNumber, 1, 0, 0) AS DepartmentServiceOverwrite
FROM #Result
WHERE ISNULL(PacketNumber, 0) > 0
ORDER BY ProductNumber, DateStamp, PackageType, SortNumber

DROP TABLE #PackageInfo
DROP TABLE #ProductInfo
DROP TABLE #Result