How To Delete and Re Post Retail Statement

Problem :- Getting retail statement calculation error "Insufficient Inventory Transactions With Status On Order,Reserved Ordered Reserved Physical And Picked."

Cause :- this error comes due to partial calculation or partial posted statement.

Solution:- Run below procedure into the HQ database, then clear the statement and again calculate statement.

USE [ProdDynamicsAX]
GO
/****** Object:  StoredProcedure [dbo].[RCT_ReverseStatement]    Script Date: 09-10-2018 07:37:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[ReverseStatement] (@dataareaid  nvarchar(50),@stmtid nvarchar(50), @storeid nvarchar(50) )as

--declare @dataareaid  nvarchar(50)
--set @dataareaid='1001'
--declare @stmtid   nvarchar(50)
--set @stmtid='AFCOURT-000051'
--declare  @storeid   nvarchar(50) 
--set @storeid = '011'


update retailtransactiontable set SALESORDERID = '' where statementid = @stmtid and type = 2 and store = @storeid and recid in 
(select recid from retailtransactiontable where statementid = @stmtid and type = 2 and store = @storeid  )

update RETAILSTATEMENTTABLE set POSTINGDATE = '' where STATEMENTID = @stmtid and recid in 
(select recid from RETAILSTATEMENTTABLE  where STATEMENTID = @stmtid )

UPDATE RETAILTRANSACTIONTABLE SET STATEMENTID ='', ENTRYSTATUS = 0,SALESORDERID = '',INVOICEID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND ENTRYSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONTABLE where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND ENTRYSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONTABLE SET STATEMENTID ='', SALESORDERID = '',INVOICEID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND ENTRYSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONTABLE where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND ENTRYSTATUS in (1,3) )


UPDATE RETAILTRANSACTIONSALESTRANS SET STATEMENTID ='', INVENTTRANSID = '', TRANSACTIONSTATUS = 0, INVENTSTATUSSALES = 0,COSTAMOUNT = 0, INVENTDIMID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSALESTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONSALESTRANS SET STATEMENTID ='', INVENTTRANSID = '', INVENTSTATUSSALES = 0, COSTAMOUNT = 0, INVENTDIMID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSALESTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONPAYMENTTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0,STATEMENTCODE  = ''
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONPAYMENTTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3))


UPDATE RETAILTRANSACTIONPAYMENTTRANS SET STATEMENTID = '', STATEMENTCODE  = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONPAYMENTTRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONBANKEDTENDERTRANS SET STATEMENTID = '',TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONBANKEDTENDERTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))


UPDATE RETAILTRANSACTIONBANKEDTENDERTRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONBANKEDTENDERTRANS where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONSAFETENDERTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSAFETENDERTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))

UPDATE RETAILTRANSACTIONSAFETENDERTRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) 
AND TRANSACTIONSTATUS in (1,3)
and recid in (select recid from  RETAILTRANSACTIONSAFETENDERTRANS where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
AND TRANSACTIONSTATUS in (1,3))

UPDATE RETAILTRANSACTIONINCOMEEXPENSETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINCOMEEXPENSETRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) 
AND TRANSACTIONSTATUS not in (1,3) )


UPDATE RETAILTRANSACTIONINCOMEEXPENSETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINCOMEEXPENSETRANS WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONORDERINVOICETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONORDERINVOICETRANS where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONORDERINVOICETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONORDERINVOICETRANS WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONINFOCODETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINFOCODETRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONINFOCODETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINFOCODETRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONTENDERDECLARATIONTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0, 
StatementCode = '' WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in
(select recid from RETAILTRANSACTIONTENDERDECLARATIONTRANS where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))

UPDATE RETAILTRANSACTIONTENDERDECLARATIONTRANS SET STATEMENTID = '', StatementCode = '' WHERE
 DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
 (select recid from RETAILTRANSACTIONTENDERDECLARATIONTRANS where  DATAAREAID = @dataareaid AND 
 STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))

DELETE RETAILSTATEMENTJOUR WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) and recid in 
(select recid from RETAILSTATEMENTJOUR where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) )

DELETE RETAILSTATEMENTTRANS WHERE DATAAREAID = @dataareaid AND  STATEMENTID in (@stmtid) and recid in
(select recid from RETAILSTATEMENTTRANS where  DATAAREAID = @dataareaid AND  STATEMENTID in (@stmtid))

Delete RETAILSTATEMENTVOUCHER where STATEMENTID in (@stmtid) and recid in 
(select recid from RETAILSTATEMENTVOUCHER  where STATEMENTID in (@stmtid))

Exec Reversestatement '1001','afcourt-000037','011'