Solved

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

  • 2 February 2024
  • 8 replies
  • 79 views

AR Statements were prepared for 12/31/2024 instead of 12/31/2023 at the beginning of 2024. If we try to go back and prepare for 12/31/2023 no Cycle IDs are shown as options. The only way for the correct Cycle ID to show is if we choose a “Prepare For:” of 01/31/2025.

I created a new Cycle ID, MONTH, using a different Schedule Type but that did not work and did not process any AR statements. It appears that once a statement has been processed for a future period there is no way to go back and correct the date. This appears awfully draconian as it is easy to enter the incorrect year, especially at year end.

Does anyone know of a workaround or a way to be able to reset the “Last Statement Date”.

Thank you

icon

Best answer by jamesh 2 February 2024, 22:46

View original

8 replies

Userlevel 6
Badge +6

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).

 

 

Userlevel 6
Badge +6

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

Userlevel 6
Badge +6

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

Userlevel 6
Badge +6

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

Userlevel 6
Badge +6

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.

Figure: The new Delete Last Statement button

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.

Figure: The Delete Customer Statements warning message

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).

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 — 2024  Acumatica, Inc. All rights reserved