CREATE PROCEDURE dbo.OTASupportLog @AFromDate DATETIME, @AToDate DATETIME, @AOTAHotelCode VARCHAR(100), @AStatusCSV VARCHAR(50), @AOTARootGroupCSV VARCHAR(50),
  @ARefefenceNumber INT, @AGuestNumber INT, @AEchoToken VARCHAR(100) AS

/*
  Created by Kent Johansen, August 2019
  Changes:
    20190924: Added EchoToken

*/
SET NOCOUNT ON 

SELECT OTAPostMain.EchoToken, OTALogBase.EchoToken AS OTALogBaseEchoToken, OTAPostMain.DateStamp, 
OTAPostMain.HotelCode, OTAPostMain.RootType, OTALogBase.OTALogId,
(
  SELECT TOP 1 Text
  FROM OTALog OL WITH (NOLOCK) 
  WHERE OL.PK = OTALogBase.OTALogId
  AND OL.Kind = 80
) AS SubversionRevisionNumber,
(
  SELECT TOP 1 CASE WHEN ISNULL(ResultText, '') = '' THEN Message ELSE ResultText END
  FROM [Techotel_PicassoDirect].[dbo].[Log] WITH (NOLOCK) 
  WHERE [Techotel_PicassoDirect].[dbo].[Log].MessageTransactionID COLLATE DATABASE_DEFAULT  = OTAPostMain.EchoToken COLLATE DATABASE_DEFAULT 
  AND [Techotel_PicassoDirect].[dbo].[Log].HotelCode COLLATE DATABASE_DEFAULT = OTAPostMain.HotelCode COLLATE DATABASE_DEFAULT
  AND (ISNULL(ResultText, '') <> '' OR ISNULL(Message, '') <> '')
  ORDER BY [Techotel_PicassoDirect].[dbo].[Log].Id desc
) AS DirectServiceLogResult
FROM OTAPostMain WITH (NOLOCK)
LEFT OUTER JOIN OTALogBase WITH (NOLOCK) ON OTALogBase.EchoToken = OTAPostMain.EchoToken
LEFT OUTER JOIN OTAPostHotelResNotif WITH (NOLOCK) ON OTAPostHotelResNotif.FK_OTAPostMainId = OTAPostMain.Id
WHERE 1=1
AND (@AStatusCSV = '' OR ISNULL(OTALogBase.Status, -1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AStatusCSV))) -- Status unknown or Error
AND OTAPostMain.HotelCode = @AOTAHotelCode
AND OTAPostMain.DateStamp BETWEEN @AFromDate AND DATEADD(DAY, 1, @AToDate)
AND (@AOTARootGroupCSV = '' OR OTAPostMain.RootType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AOTARootGroupCSV)))
AND (@ARefefenceNumber <= 0 OR (OTAPostHotelResNotif.ReferenceNumber = @ARefefenceNumber AND OTAPostHotelResNotif.GuestNumber = @AGuestNumber))
AND (ISNULL(@AEchoToken, '') = '' OR OTAPostMain.EchoToken = @AEchoToken)
ORDER BY OTAPostMain.EchoToken DESC