@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.
DECLARE @CycleID as varchar(10);
DECLARE @LastStmtDate as varchar(10);
DECLARE @CompanyID as int;
SET @CycleID = 'EOM';
SET @LastStmtDate = '2021-12-31';
SET @CompanyID = 2;
UPDATE ARStatementCycle
SET LastStmtDate = @LastStmtDate
WHERE
ARStatementCycle.StatementCycleID = @CycleID
AND CompanyID = @CompanyID
;
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
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
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 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 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 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;
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
;
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