Statement Cycle - Incorrect Statement Date entered 12/31/24 vs 12/31/23. No way to go back

Alternatively, if you’re on a version that doesn’t have this button yet, you’ll need access to the database to run the following query, or open a support ticket and have them run this query with the appropriate values for:

@LastStmtDate , @CycleID, @CompanyID

Here’s the script:

/*The script removes statements and all associated data for the specified period.
Also corrected the wrong StatementLastDate of customers.*/

DECLARE @CycleID as varchar(10);
DECLARE @LastStmtDate as varchar(10);
DECLARE @CompanyID as int;

--=============================================
--SET YOUR PARAMETERS HERE:
SET @CycleID = 'EOM'; -- put here statement cycle
SET @LastStmtDate = '2023-12-31';  -- put here latest statement date you want to rollback
SET @CompanyID = 2; -- put here companyid
--=============================================

UPDATE ARStatementCycle
SET LastStmtDate = @LastStmtDate
WHERE
 ARStatementCycle.StatementCycleID = @CycleID
 AND CompanyID = @CompanyID
;
--find all the statements to remove
SELECT
 ARStatement.CompanyID,
 ARStatement.BranchID,
 ARStatement.CustomerID,
 ARStatement.StatementDate,
 ARStatement.CuryID,
 ARStatement.StatementCycleID,
 ARStatementCycle.LastStmtDate
INTO #ARStatement
FROM
 ARStatement
 INNER JOIN ARStatementCycle
  ON ARStatementCycle.CompanyID = ARStatement.CompanyID
  AND ARStatementCycle.StatementCycleID = ARStatement.StatementCycleID
WHERE  
 ARStatement.CompanyID = @CompanyID
 AND ARStatementCycle.StatementCycleID = @CycleID
 AND ARStatement.StatementDate > @LastStmtDate

UNION ALL

--Customer with the appropriate StatementCycleID, but he has a statement for another StatementCycleID in the generated period.
--He is the parent with appropriate parameters to generate the statement
SELECT
 ARStatement.CompanyID,
 ARStatement.BranchID,
 ARStatement.CustomerID,
 ARStatement.StatementDate,
 ARStatement.CuryID,
 ARStatement.StatementCycleID,
 @LastStmtDate
FROM
 Customer
 INNER JOIN ARStatement
  ON ARStatement.CompanyID = Customer.CompanyID
  AND ARStatement.CustomerID = Customer.BAccountID
  AND ARStatement.StatementCycleId != customer.StatementCycleId
  AND Customer.StatementCycleId = @CycleID
  AND Customer.StatementLastDate > @LastStmtDate
  AND Customer.CompanyID = @CompanyID
  AND ARStatement.StatementDate > @LastStmtDate
 INNER JOIN Customer as CustomerMaster
  ON CustomerMaster.BAccountID = Customer.StatementCustomerID
  AND CustomerMaster.CompanyID = Customer.CompanyID
  AND (CustomerMaster.StatementLastDate <= @LastStmtDate
   OR CustomerMaster.StatementLastDate IS NULL)
;
SELECT
 ARStatementDetail.CompanyID,
 ARStatementDetail.BranchID,
 ARStatementDetail.CustomerID,
 ARStatementDetail.CuryID,
 ARStatementDetail.DocType,
 ARStatementDetail.RefNbr,
 ARStatementDetail.StatementDate,
 #ARStatement.LastStmtDate
INTO #ARStatementDetails
FROM
 ARStatementDetail
 INNER JOIN #ARStatement
  ON ARStatementDetail.CompanyID = #ARStatement.CompanyID
  AND ARStatementDetail.BranchID = #ARStatement.BranchID
  AND ARStatementDetail.CustomerID = #ARStatement.CustomerID
  AND ARStatementDetail.StatementDate = #ARStatement.StatementDate
  AND ARStatementDetail.CuryID = #ARStatement.CuryID
;
SELECT
 ARStatementAdjust.CompanyID,
 ARStatementAdjust.BranchID,
 ARStatementAdjust.CustomerID,
 ARStatementAdjust.StatementDate,
 ARStatementAdjust.CuryID,
 ARStatementAdjust.AdjdDocType,
 ARStatementAdjust.AdjdRefNbr,
 ARStatementAdjust.AdjgDocType,
 ARStatementAdjust.AdjgRefNbr,
 ARStatementAdjust.AdjNbr,
 #ARStatement.LastStmtDate
INTO #ARStatementAdjust
FROM
 ARStatementAdjust
 INNER JOIN #ARStatement
  ON ARStatementAdjust.CompanyID = #ARStatement.CompanyID
  AND ARStatementAdjust.CustomerID = #ARStatement.CustomerID
  AND ARStatementAdjust.BranchID = #ARStatement.BranchID
  AND ARStatementAdjust.StatementDate = #ARStatement.StatementDate
  AND ARStatementAdjust.CuryID = #ARStatement.CuryID
;
--UPDATE ARRegister TABLE:
UPDATE ARRegister
SET ARRegister.StatementDate = #ARStatementDetails.LastStmtDate
FROM
 ARRegister
 INNER JOIN #ARStatementDetails
  ON #ARStatementDetails.CompanyID = ARRegister.CompanyID
  AND #ARStatementDetails.BranchID = ARRegister.BranchID
  AND #ARStatementDetails.CustomerID = ARRegister.CustomerID
  AND #ARStatementDetails.CuryID = ARRegister.CuryID
  AND #ARStatementDetails.DocType = ARRegister.DocType
  AND #ARStatementDetails.RefNbr = ARRegister.RefNbr
;
--UPDATE ARAdjust TABLE:
UPDATE ARAdjust
SET ARAdjust.StatementDate = #ARStatementAdjust.LastStmtDate
FROM
 ARAdjust
 INNER JOIN #ARStatementAdjust
  ON #ARStatementAdjust.CompanyID = ARAdjust.CompanyID
  AND #ARStatementAdjust.AdjdDocType = ARAdjust.AdjdDocType
  AND #ARStatementAdjust.AdjdRefNbr = ARAdjust.AdjdRefNbr
  AND #ARStatementAdjust.AdjgDocType = ARAdjust.AdjgDocType
  AND #ARStatementAdjust.AdjgRefNbr = ARAdjust.AdjgRefNbr
  AND #ARStatementAdjust.AdjNbr = ARAdjust.AdjNbr
;
--DELETE FROM ARStatementDetail TABLE:
DELETE ARStatementDetail
FROM
 ARStatementDetail
 INNER JOIN #ARStatementDetails
  ON #ARStatementDetails.CompanyID = ARStatementDetail.CompanyID
  AND #ARStatementDetails.BranchID = ARStatementDetail.BranchID
  AND #ARStatementDetails.CustomerID = ARStatementDetail.CustomerID
  AND #ARStatementDetails.StatementDate = ARStatementDetail.StatementDate
  AND #ARStatementDetails.CuryID = ARStatementDetail.CuryID
  AND #ARStatementDetails.DocType = ARStatementDetail.DocType
  AND #ARStatementDetails.RefNbr = ARStatementDetail.RefNbr
;
--DELETE FROM ARStatementAdjust TABLE:
DELETE ARStatementAdjust
FROM
 ARStatementAdjust
 INNER JOIN #ARStatementAdjust
  ON #ARStatementAdjust.CompanyID = ARStatementAdjust.CompanyID
  AND #ARStatementAdjust.BranchID = ARStatementAdjust.BranchID
  AND #ARStatementAdjust.CustomerID = ARStatementAdjust.CustomerID
  AND #ARStatementAdjust.CuryID = ARStatementAdjust.CuryID
  AND #ARStatementAdjust.StatementDate = ARStatementAdjust.StatementDate
  AND #ARStatementAdjust.AdjdDocType = ARStatementAdjust.AdjdDocType
  AND #ARStatementAdjust.AdjdRefNbr = ARStatementAdjust.AdjdRefNbr
  AND #ARStatementAdjust.AdjgDocType = ARStatementAdjust.AdjgDocType
  AND #ARStatementAdjust.AdjgRefNbr = ARStatementAdjust.AdjgRefNbr
  AND #ARStatementAdjust.AdjNbr = ARStatementAdjust.AdjNbr
;
--DELETE FROM ARStatement TABLE:
DELETE ARStatement
FROM
 ARStatement
 INNER JOIN #ARStatement
  ON #ARStatement.CompanyID = ARStatement.CompanyID
  AND #ARStatement.BranchID = ARStatement.BranchID
  AND #ARStatement.CustomerID = ARStatement.CustomerID
  AND #ARStatement.StatementDate = ARStatement.StatementDate
  AND #ARStatement.CuryID = ARStatement.CuryID
;
DROP TABLE #ARStatement;
DROP TABLE #ARStatementDetails;
DROP TABLE #ARStatementAdjust;

--Set StatementLastDate to max StatementDate from all customer's statements:
UPDATE Customer
SET Customer.StatementLastDate = c.MaxStatementDate
FROM
 Customer
 INNER JOIN
  (SELECT
   Customer.BAccountID,
   Customer.CompanyID,
   Customer.StatementLastDate,
   MAX(ARStatement.StatementDate) AS MaxStatementDate
  FROM
   Customer
   INNER JOIN ARStatement
    ON ARStatement.CompanyID = Customer.CompanyID
    AND ARStatement.CustomerID = Customer.BAccountID
  WHERE
   Customer.CompanyID = @CompanyID
   AND Customer.DeletedDatabaseRecord = 0
   AND Customer.StatementLastDate > @LastStmtDate
  GROUP BY
   Customer.BAccountID,
   Customer.CompanyID,
   Customer.StatementLastDate
  HAVING
   MAX(ARStatement.StatementDate) != Customer.StatementLastDate) AS c

  ON Customer.BAccountID = c.BAccountID
   AND Customer.CompanyID = c.CompanyID
   AND Customer.StatementLastDate != c.MaxStatementDate
;
--Reset Customer.StatementLastDate for those who have no ARStatement
UPDATE Customer
SET Customer.StatementLastDate = @LastStmtDate
FROM
 Customer
 LEFT JOIN ARStatement
  ON ARStatement.CompanyID = Customer.CompanyID
  AND ARStatement.CustomerID = Customer.BAccountID
WHERE
 Customer.CompanyID = @CompanyID
 AND NOT Customer.StatementLastDate IS NULL
 AND ARStatement.CustomerID IS NULL

Hope this helps!  :)