CREATE PROCEDURE [dbo].[DebtorExportCleanup] @AUserIdNumber INT AS
SET NOCOUNT ON

/*
  Created by: Kent Johansen Febuary 2009
  Used to delete illegal ledger,vaucher payments. If not invoicebill is on the transaction, then
    it's not a legal transactions, and is deleted. Then the folio is then showed in Invoice plan.

  Changes:
    20210325: Bug: Fix for "Running after systemdate change, so no transaactions is on systemdate". Case #CR206444
    20160621: Bug: If Multiple invoices has been made on same transactions then result set is wrong. Case #CR107261
    20160317: Bug: Fix for "PostMoveDeleteTransactions has implemented extra params for reduction".
*/

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 T.TransactionNumber, T.HotelCustomerNumber
  FROM Transactions T WITH (NOLOCK)
    LEFT JOIN dbo.InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
    LEFT JOIN dbo.Invoice I WITH (NOLOCK) ON I.PkInvoice = ITX.FkInvoice
  WHERE ISNULL(I.PkInvoice, 0) <= 0
  AND T.DateStamp >= DATEADD(DAY, -1, @SystemDateDt) 
  AND T.ShowOnBillFlag = 1 
  AND T.MovedDeleted = 0 
  AND T.Payment IN (1,2,14)
  AND I.CancelDate IS NULL
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