CREATE PROCEDURE [dbo].[PostDeleteOTAPayment] @AReferenceNumber INT, @AGuestNumber INT, @AUserIdNumber INT, @ANote VARCHAR(50) AS
SET NOCOUNT ON

/*
  Created by: Kent Johansen June 2023
  Delete existing OTA payments.

  Changes:
*/

DECLARE @SystemDate CHAR(8)
DECLARE @SystemDatedt DATETIME
DECLARE @TranNumber INT
DECLARE @HotelCustomerNumber INT

SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK)
SET @SystemDateDt = CONVERT(DATETIME, @SystemDate)

DECLARE db_cur1 CURSOR STATIC READ_ONLY FOR
  SELECT Transactions.TransactionNumber, Transactions.HotelCustomerNumber
  FROM Transactions WITH (NOLOCK)
  LEFT JOIN InvoiceTransactionXtab WITH (NOLOCK) ON InvoiceTransactionXtab.FkTransaction = Transactions.TransactionNumber
  LEFT JOIN Invoice WITH (NOLOCK) ON Invoice.PkInvoice = InvoiceTransactionXtab.FkInvoice
  INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
    AND Folios.ReferenceNumber = @AReferenceNumber
    AND Folios.GuestNumber = @AGuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = Folios.ReservationAddressNumber
    AND ReservationAddress.AddressType = 1
  WHERE ISNULL(Invoice.PkInvoice, 0) <= 0
  AND Transactions.ShowOnBillFlag = 1 
  AND Transactions.MovedDeleted = 0 
  AND Transactions.Payment IN (1,2,3,4)
  AND Invoice.CancelDate IS NULL
  AND Transactions.Note = @ANote  
OPEN db_cur1

FETCH FIRST FROM db_cur1 INTO @TranNumber, @HotelCustomerNumber 

WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC PostMoveDeleteTransaction @TranNumber, @AUserIdNumber, -1, @HotelCustomerNumber, 0, 0, 0

  FETCH NEXT FROM db_cur1 INTO @TranNumber, @HotelCustomerNumber 
END

CLOSE db_cur1
DEALLOCATE db_cur1