Solved

How to delete/Clean customer statement?


Userlevel 5
Badge +4

Hello,

     As we verified, by clicking the “customer statement” button on customer screen, the system always prints the report version with the statement date largest.

 

 

  However,a user prepared a statement on 2/5/2021 and chosen  12/31/2021 as the statement date, which makes it always the latest one.

 

       Is there a way to delete this statement?

If this record exists, the user always get an incorrect version of statement by clicking the “customer statement” on customer screen.

icon

Best answer by ray20 24 April 2021, 09:55

View original

11 replies

Userlevel 5
Badge +4

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

Userlevel 7
Badge +12

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

 

Userlevel 5
Badge +4

@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?
 

Userlevel 7
Badge +12

Sure.  

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

 

 

Userlevel 5
Badge +4

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

 

 

Userlevel 7
Badge +12

Voted!!

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

Userlevel 5
Badge +4

@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


 

Userlevel 7
Badge +12

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

Badge

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

 

Userlevel 5
Badge +4

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.

Userlevel 5
Badge +4

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.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved