CREATE PROCEDURE dbo.OTALogCleanUp @ANumberOfDaysToKeep INT, @ANumberOfDaysToKeepError INT AS
SET NOCOUNT ON

/*
  Created By Kent Johansen, October 2016
  Delete OTALog records
  Cascade deletes has to be setup on OTALog+OTALogBase

  Changes:
    KJ 20180216: Delete inbound logs
    KJ 20180214: Added delete of FIUSys logs
    KJ 20161004: Running in cursor to prevent lock request timeout.
*/

DECLARE @SystemDate DATETIME
SET @SystemDate = CONVERT(DATETIME, (SELECT SystemDate FROM SYSTEM WITH (NOLOCK)))

SELECT DISTINCT OTALogId
INTO #OTAids
FROM OTALogBase WITH (NOLOCK)
INNER JOIN OTALog WITH (NOLOCK) ON OTALog.PK = OTALogBase.OTALogId
  AND OTALog.Datestamp <= DATEADD(DAY, -@ANumberOfDaysToKeep, @SystemDate)
WHERE Journey = 1 -- Outbound
AND Status IN (1,2) -- Success and Warning
UNION
SELECT DISTINCT OTALogId
FROM OTALogBase WITH (NOLOCK)
INNER JOIN OTALog WITH (NOLOCK) ON OTALog.PK = OTALogBase.OTALogId
  AND OTALog.Datestamp <= DATEADD(DAY, -@ANumberOfDaysToKeepError, @SystemDate)
WHERE Journey = 1 -- Outbound
AND NOT Status IN (1,2) -- NOT Success and Warning
UNION
SELECT DISTINCT OTALogId
FROM OTALogBase WITH (NOLOCK)
INNER JOIN OTALog WITH (NOLOCK) ON OTALog.PK = OTALogBase.OTALogId
  AND OTALog.Datestamp <= DATEADD(DAY, -@ANumberOfDaysToKeepError, @SystemDate)
WHERE ISNULL(FKOTAHotelCode, '') = ''
UNION
SELECT DISTINCT OTALogId
FROM OTALogBase WITH (NOLOCK)
INNER JOIN OTALog WITH (NOLOCK) ON OTALog.PK = OTALogBase.OTALogId
  AND OTALog.Datestamp <= DATEADD(DAY, -@ANumberOfDaysToKeep, @SystemDate)
INNER JOIN CustomerOTA WITH (NOLOCK) ON CustomerOTA.HotelCode = OTALogBase.FKOTAHotelCode
INNER JOIN OTARequestor WITH (NOLOCK) ON OTARequestor.RequestorID = CustomerOTA.RequestorID
  AND RTRIM(LTRIM(OTARequestor.Id)) = 'Konventum'
UNION
SELECT DISTINCT OTALogId
FROM OTALogBase WITH (NOLOCK)
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = OTALogBase.PMSKey1
  AND Reservations.GuestNumber = OTALogBase.PMSKey2
WHERE Journey = 0
AND (ArrivalDate <= DATEADD(DAY, -14, @SystemDate) OR ArrivalDate IS NULL)

DELETE OTALogBase
WHERE OTALogId IN (SELECT OTALOGID FROM #OTAids)

DROP TABLE #OTAids