You can delete the most recent customer statements of the selected statement cycle by clicking the Delete Last Statement button on the form toolbar of the Statement Cycles (AR202800) form. Once the last customer statements are deleted, you can delete the statements that now have the latest date (which became the most recent statements after the later customer statements were deleted).

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!  :) 

Hi Jamesh, thank you for your speedy reply. Unfortunately, I do not have that option:

Is this something my VAR would have to activate?

Thank you again,

Jeff

What version of Acumatica are you running?

Thank you Jamesh, I will talk to my VAR regarding this but it appears we do not have the button in our version 2023 R1 so I will send them over your script for execution.

Jeff

Sounds good! 

I just verified this was released in 2023R2:

Ability to Delete Customer Statements
In previous versions of Acumatica ERP, users could not delete any customer statements that had been generated. Acumatica ERP 2023 R2 introduces the ability to delete the most recent customer statements. On the form toolbar of the Statement Cycles (AR202800) form, the new Delete Last Statement button has been added. This button appears on the form only for users with the Financial Supervisor role assigned on the Users (SM201000) form.

Thanks Jamesh for all your help:)

From the 2023 R2 Release Notes:

Ability to Delete Customer Statements
In previous versions of Acumatica ERP, users could not delete any customer statements that had been generated. Acumatica ERP 2023 R2 introduces the ability to delete the most recent customer statements. On the form toolbar of the Statement Cycles (AR202800) form, the new Delete Last Statement button has been added. This button appears on the form only for users with the Financial Supervisor role assigned on the Users (SM201000) form.

When a user clicks the Delete Last Statement button, an informative warning message is displayed (as the following screenshot shows). Once a user clicks Delete, the system deletes the last generated customer statements.

Once the last customer statements are deleted, a user can delete the statements that now have the latest date (which became the most recent statements after the later customer statements were deleted).