Queries: Bank Statement

General

select c_bankstatement_id,name from c_bankstatement where ad_client_id = 1000009 order by name;

Rough working queries to reinstate voided bank statement

• Possibly could be converted into a plugin
• Refer to MBankStatement.voidIt
• It should also be possible to simply delete the bank statement and reinsert the copy before void was done instead of using the staging table, but I didn't try it.
• Take a backup before running any queries

select docstatus, beginningbalance, statementdifference, endingbalance, * from c_bankstatement where C_BankStatement_ID=1000555;

-- Backup
select * into bk_20170408_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatement;

select * into bk_20170408_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;

select count(*) from bk_20170408_bankstatementline;

select * into bk_20170408_payment from c_payment;

select count(*) from bk_20170408_payment;

select * into bk_20170408_c_bankaccount from c_bankaccount where c_bankaccount_id  =
(select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555);

select count(*) from bk_20170408_c_bankaccount;

-- Staging tables (must contain the original data before void was done)
select * into zz_stage_bankstatement from c_bankstatement where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatement;
select * into zz_stage_bankstatementline from c_bankstatementline where c_bankstatement_id = 1000555;
-- delete from zz_stage_bankstatementline;

select count(*) from zz_stage_bankstatement;
select count(*) from zz_stage_bankstatementline;
select count(*) from c_bankstatement where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555;
select count(*) from c_bankstatementline where c_bankstatement_id = 1000555 and c_payment_id is not null; --0; 90 on restore

select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555); -- 90

update c_bankstatement
	set
		docstatus = b.docstatus ,
		docaction = b.docaction ,
		processed = b.processed ,
		description = b.description ,
		statementdifference = b.statementdifference
from
	zz_stage_bankstatement b 
where 
	c_bankstatement.c_bankstatement_id = 1000555 and 
	c_bankstatement.c_bankstatement_id = b.c_bankstatement_id ;

update c_bankstatementline
	set
		description = b.description ,
		stmtamt = b.stmtamt ,
		trxamt = b.trxamt ,
		chargeamt = b.chargeamt ,
		interestamt = b.interestamt,
		c_payment_id = b.c_payment_id
from
	zz_stage_bankstatementline b 
where 
	c_bankstatementline.c_bankstatement_id = 1000555 and
	c_bankstatementline.c_bankstatementline_id = b.c_bankstatementline_id ;

-- ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));

select currentbalance from c_bankaccount where c_bankaccount_id = ( 
	select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555 
	);

-- See MBankStatement.voidIt: 
--  ba.setCurrentBalance(ba.getCurrentBalance().subtract(getStatementDifference()));
update c_bankaccount
set currentBalance = currentBalance + 
(select statementdifference from c_bankstatement where c_bankstatement_id = 1000555)
where c_bankaccount_id = ( 
	select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555 
	)
;

select currentbalance from c_bankaccount where c_bankaccount_id = ( 
	select c_bankaccount_id from c_bankstatement where c_bankstatement_id = 1000555 
	);

update c_payment set
isreconciled = 'Y' 
where c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);

-- Expect 90
select count(*) from c_payment where isreconciled = 'Y' and c_payment_id in (select c_payment_id from c_bankstatementline where c_bankstatement_id = 1000555);

This page is brought to you by nTier Software Services. Feel free to improve directly or suggest using the Discussion tab.