If statement can not be deleted on the screen, is there a way to clean it by db script?

Hi, @ray20  You can delete the Customer Statements using the below query but if you delete the last statement and try to click on the “Customer Statement” in the Customers screen, you will get the below error. Please find the screenshot for reference.

Here is the query:

Delete from ARStatement where  CompanyID=2 and BranchID=16 and CustomerID=7048 and StatementDate ='2021-02-01 00:00:00' and OnDemand=1

Hope this helps!!

@Naveen B 
Thank you, Captain.
I have submitted a case, and would update after I have got response.
By the way, if you regenerate a statement, will this error be disapperred?
 

Sure.  

Yes @ray20 Error will NOT come if you regenrate again.

@Naveen B 
Thank you for confirming this.
the Acumatica support ask me to rasie an idea for enhancement.

Voted!!

That would great, if we get this feature in future builds.

@Naveen B 
Hello, my friend and others would be interested.
From the official feedback, we’ve already recorded a change request to ask for the function of deleting the latest statement. But without ETA or guranteed time to complete this change request.

So, right now, a DB script might be the only opinion

Note: every case is unique.

Below is just an example and it would not applied to all scenarios

Every time if someone met statement issue, the best way is to ask help from the official support team.

Do not run the codes on production environment.

/*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 = '2021-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

@ray20 Great.. Thanks for sharing the update :)

When will these be implemented as part of being able to delete and rerun statements from the Application?

@Jp89 ， Hello, I am sorry, I don’t know their release plan.
But currently, the statements can be re-prepared from the application. You can find all records on statement history.

I met this case again
The scripts Acumatica provided is fully functional, but it would roll back all to a certain date.

Normally, we just want to clean a single one, then you can do below
NOTE: it is risky, should never run unless you get official support or verification directly.

supposing you are having a statement date larger than 0730 and under customer id=648 to clean.

Checking the  below 5 tables, to see whether this table having data to clean or update.

select * from ARStatement where CustomerID=648 and CompanyID=2 and StatementDate>'2022-07-30 00:00:00'
select * from ARStatementDetail where CustomerID=648 and CompanyID=2 and StatementDate>'2022-07-30 00:00:00'
select * from  ARStatementAdjust where CustomerID=648 and CompanyID=2 and StatementDate>'2022-07-30 00:00:00'
select * from ARRegister where CustomerID=648 and CompanyID=2 and StatementDate>'2022-07-30 00:00:00'
select * from ARAdjust where CustomerID=648 and CompanyID=2 and StatementDate>'2022-07-30 00:00:00'

ARRegister,ARAdjust should have no data if you have already re-prepare statement in system.
ARStatementAdjust, I did not see any data on my end, I don’t know what is this table for.

So normally, you only have to clean the unnecessary data in ARStatementDetail and ARStatement.
First, clean ARStatementDetail, then ARStatement

NOTE: it is risky, always remember the where clause and never run it unless get official verification.