Solved

How to delete/Clean customer statement?

  • 17 April 2021
  • 8 replies
  • 67 views

Userlevel 5
Badge +2

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

@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


 

View original

8 replies

Userlevel 5
Badge +2

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

Userlevel 6
Badge +3

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 +2

@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 6
Badge +3

Sure.  

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

 

 

Userlevel 5
Badge +2

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

 

 

Userlevel 6
Badge +3

Voted!!

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

Userlevel 5
Badge +2

@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 6
Badge +3

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

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