DECLARE @WineryID UNIQUEIDENTIFIER
SET     @WineryID =
(
  SELECT WineryID
  FROM   AdminContacts
  WHERE  Username = 'benb'
    AND  Password = 'ewinery11'
)

DECLARE @OrderNumber VARCHAR(50)
SET     @OrderNumber = '162757'

DECLARE @CartID UNIQUEIDENTIFIER
SET     @CartID =
(
  SELECT c.CartID
  FROM   Carts c
  WHERE c.WineryID    = @WineryID
    AND c.OrderNumber = @OrderNumber
)

DECLARE @FirstPartyWine    UNIQUEIDENTIFIER
DECLARE @ThirdPartyWine    UNIQUEIDENTIFIER
DECLARE @FirstPartyProduct UNIQUEIDENTIFIER

SET @FirstPartyWine =
(
  SELECT ProductTypeID
  FROM   ProductTypes
  WHERE  ProductType = 'First Party Wine (Bottles/Cases)'
)

SET @ThirdPartyWine =
(
  SELECT ProductTypeID
  FROM   ProductTypes
  WHERE  ProductType = 'Third Party Wine (Bottles/Cases)'
)

SET @FirstPartyProduct =
(
  SELECT ProductTypeID
  FROM   ProductTypes
  WHERE  ProductType = 'First Party Product'
)

DECLARE @SentToFulfillment                INT
DECLARE @PaymentAccepted                  INT
DECLARE @OrderTypePhone                   INT
DECLARE @OrderTypeClub                    INT
DECLARE @OrderTypeInternet                INT
DECLARE @FulfillmentTypeClub              INT
DECLARE @FulfillmentTypeDaily             INT

SET @SentToFulfillment      = 5
SET @PaymentAccepted        = 4
SET @OrderTypePhone         = 6
SET @OrderTypeClub          = 1
SET @OrderTypeInternet      = 4
SET @FulfillmentTypeClub    = 1
SET @FulfillmentTypeDaily   = 2

SELECT ci.CartItemID,
       CASE p.ProductTypeID
         WHEN @FirstPartyWine  THEN wb.WineBrandKey
         WHEN @ThirdPartyWine  THEN tpwb.WineBrandKey
         ELSE 'NONWINE'
       END AS BrandKey,
       ISNULL(cs.ShippingCode, '') AS ShippingCode, cs.ShippingType,
       win.WineryID, win.ShipCompliantFulfillmentAccount,
       ci.ProductSKU, ws.WineShipper,
       CASE win.IsShipCompliantForFulfillment
         WHEN 1 THEN @SentToFulfillment
         ELSE @PaymentAccepted
       END AS ShipmentStatus,
       CASE
         WHEN ci.PriceUnits = 'Case(s)' THEN
           -- Note: ci.BottleCount is never NULL when PriceUnits is Case(s).
           CAST(ISNULL(ci.CustomizedPrice, ci.Price) / ci.BottleCount AS MONEY)
         ELSE
           CAST(ISNULL(ci.CustomizedPrice, ci.Price) AS MONEY)
       END AS UnitPrice,
       CASE
         WHEN ci.BottleCount IS NULL THEN ci.Quantity
         ELSE ci.BottleCount * ci.Quantity
       END AS Quantity,
       c.CartID, c.WineryID, cb.ClubBatchID, win.ShipCompliantURL,
       win.ShipCompliantUsername, win.ShipCompliantPassword,
       win.ShipCompliantTag, cb.BatchName, c.GiftMessage,
       c.OrderNotes, c.ShipCity, c.ShipCompany, c.ShipFirstName,
       c.ShipLastName, c.ShipPhone, c.ShipState, c.ShipZipCode,
       c.ShipAddress, c.ShipAddress2, c.BillCity, c.BillCompany,
       c.BillEmail, c.BillFirstName, c.BillLastName, c.BillPhone,
       c.BillState, c.BillAddress, c.BillAddress2, c.BillZipCode,
       ISNULL(c.CustomizedShippingPrice, c.Shipping) AS Shipping,
       CASE
         WHEN c.IsClubOrder  = 1 THEN @OrderTypeClub
         WHEN c.IsAdminOrder = 1 THEN @OrderTypePhone
         ELSE @OrderTypeInternet
       END AS OrderType,
       CASE
         WHEN cb.ClubBatchID IS NOT NULL THEN @FulfillmentTypeClub
         ELSE @FulfillmentTypeDaily
       END AS FulfillmentType,
       c.DateAdded,
       CASE
         WHEN c.ShipDate IS NOT NULL THEN c.ShipDate
         ELSE c.DateAdded
       END AS ShipDate,
       c.ShipBirthDate, c.BillBirthDate, c.DateAdded AS PurchaseDate,
       CASE
         WHEN c.OrderNumber IS NOT NULL THEN
           ISNULL(win.ShipCompliantOrderPrefix, '') + CAST(c.OrderNumber AS VARCHAR(50))
         ELSE CAST(c.CartID AS VARCHAR(50))
       END AS SalesOrderKey,
       c.MemberID AS CustomerKey,
       ISNULL(lr.VALUE, 1) AS LicenseRelationship,
       p2.ProductSKU AS KitSKU
FROM   Carts c
INNER JOIN CartItems ci ON c.CartID = ci.CartID
INNER JOIN Products p ON ci.ProductID = p.ProductID
INNER JOIN Wineries win ON c.WineryID = win.WineryID
LEFT OUTER JOIN CartShippings cs ON c.CartID = cs.CartID
  AND cs.ProductTypeID = p.ProductTypeID
LEFT OUTER JOIN ClubBatches cb ON c.ClubBatchID = cb.ClubBatchID
LEFT OUTER JOIN Wines w ON p.ProductKeyID = w.WineID
LEFT OUTER JOIN ThirdPartyWines tpw ON p.ProductKeyID = tpw.ThirdPartyWineID
LEFT OUTER JOIN WineBrands wb ON w.WineBrandID = wb.WineBrandID
LEFT OUTER JOIN ThirdPartyWineBrands tpwb ON tpw.ThirdPartyWineBrandID = tpwb.ThirdPartyWineBrandID
LEFT OUTER JOIN WineShippers ws ON ci.ShipperID = ws.WineShipperID
LEFT OUTER JOIN LicenseRelationships lr ON c.LicenseRelationshipID = lr.RowID
LEFT OUTER JOIN Products p2 ON ci.KitID = p2.ProductID
WHERE c.Cartid = @CartID
ORDER BY ShippingCode