Announcements
20
I am trying to close a bank reconciliation period. I am using the CLOSING module and i select PERIOD for Bank Reconciliation and click on Begin Processing. SL goes through through its process then nothing happens, the period does not move forward. The strange thing is, this used to work, I have been able to roll through the first 5 periods of the year. Unfortunately, i can't get to period 6. Please help.
*This post is locked for comments
These are the select statements that show the records that exist it the next month that need to be deleted.
Select * from brheader where acctid = 'xxxx' and reconpernbr = '2013xx'
Select * from brtran where acctid = 'xxxx' and currpernbr = '2013xx'
Select * from brbattran where acctid = 'xxxx' and currpernbr = '2013xx'
Have a current backup.
Besides deleting the above records, you may want to update the bracct. I don't know that doing this makes any difference but does make it consistent if nothing else.
update bracct set currpernbr='yyyymm'
I need to send him the scripts to delete these records:
Select * from brheader where acctid = ‘xxxx’ and reconpernbr = ‘201301’
Select * from brtran where acctid = ‘xxxx’ and perclosed = ‘201301’
Select * from brbattran where acctid ‘xxxx’ and perclosed = ‘201301’
Is that deep enough of a select statement? And if so – he just needs to delete their results? I don’t have a working environment of the bank rec module at the moment so it’s hard to confirm all the fields of consideration for the most accurate update.
You are the best Elaine – you are always so good at giving straight information!
Your assumtion is correct. Although the system allows you to change the BR setup period, you shouldn't do so. Any outstanding transactions have a duplicate transaction created for the next period when you close the BR module. So if you don't close the BR module, then you don't get these still outstanding transactions created for the next month.
You will need to delete both the brheader and brtrans for the new month. Might also review the brbattran . If you don't do this, most likely next time you try to close, you will get an error msg about a duplicate brtran0 records
Hi Elaine - I have a client that changed the period in the setup screen from Dec to Jan (didn't close the period in the GL) and then created 2 bank recs with a January period. The problem is that all the prior month's outstanding items are not pulling forward into January (from December). The only thing pulling is the GL information. My impression is that these two bank rec header records must be deleted so we can then close the module for the year. I am then assuming this process will correct the lost records (ie carry the outstanding checks/deposits) in the Jan period. I don't have access to their SQL right now - should their IT director only need to delete the header records? or are there trans as well? If we can't delete - should we just update those two bank rec header records with some sort of bogus GL account? That won't blow anything up?
Find what period all of your brset records are in. Same for brtran and brheader.
select cpnyid,PerNbr,* from brsetup
select CpnyId,ReconPerNbr,AcctID,* from brheader where ReconPerNbr > 'yyyymm'
select currpernbr,acctid,* from brtran where brtran.currPerNbr > 'yyyymm'
If the BRsetup periods are ok, then you will need to delete records from the more recent period after you have a backup.
Can you look at this event log and tell me what I should do to get my August bank rec to close?
Thank you.
System Message 6124: Process started: Thu Oct 04 04:15 P.M.
Closing Bank Reconciliation:
================================================================================
System Message 5: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot
insert duplicate key row in object 'dbo.BRHeader' with unique
index 'BRHeader0'. The duplicate key value is (WANDW ,
PTNB , 20120
Debugging info: insert brheader
-So if you set the BR setup period back, set it back to what it was.
-If you just added accounts with the same IDs as exist for a different company in this app dbase, rename them with the scripts provided as an example.
-If you have records in BRtran, Brbanktran, Brbattran, Brheader for a future period, in Management Studio delete them first in a test copy of the database or after a current backup.
Hello,
You will have an eventlog describing the issue that is keeping the bank rec from closing.
There are a couple of reasons this could occur. If you have created a Bank Rec (creates record in the BRHeader table and possibly the Brtran table) for period 6, that causes an issue because when you close the Bank Rec module, the system wants to create these records. If they already exist, you will get an error message that there is a duplicate record. It will either be in the Brheader or Brtran table.
This can happen because someone changed the BR setup period number back so that they could reconcile/change something in a prior bank rec.
Or...
If you add a new company BR setup information, it defaults the period as the GL period rather than as the same period of all of the other BRSetup records in this application database. So if GL is ahead of the Bank Rec period and you allow the BR setup period number to be accepted as the GL period rather than the period of the other companies in BR setup, you can run into this issue.
Or...
If you just manually go into the BR Bank Rec screen and create a Bank Rec for the next period becuase in this company you are ready to move forward but the other companies in this application database are not.
Or...
If you have multiple companies in the same application database and used the same AcctID in each company such as PR or AP, etc. Bug 13366.
If you update all of the tables to a different acctid other then the duplicate the Bank Reconciliation module will close.
For example the statements would be:
select acctid, cpnyid, * from brbattran where cpnyid = '20'
select acctid, cpnyid, * from bracct where cpnyid = '20'
select acctid, cpnyid, * from brtran where cpnyid = '20'
select acctid, cpnyid, * from brheader where cpnyid = '20'
select acctid, cpnyid, * from brbanktran where cpnyid = '20'
--these statements should pull back records regarding the company 20
To update these tables you can run the below statements while replacing XXXX with your new acctid.
update brbattran set acctid = 'XXXX' where cpnyid = '20' and acctid = 'YYYY'
update brtran set acctid = 'XXXX' where cpnyid = '20' and acctid = 'YYYY'
update bracct set acctid = 'XXXX' where cpnyid = '20' and acctid = 'YYYY'
update brbanktran set acctid = 'XXXX' where cpnyid = '20' and acctid = 'YYYY'
update brheader set acctid = 'XXXX' where cpnyid = '20' and acctid = 'YYYY'
-- where XXXX is the new ID and YYYY is the old ID
#1
Community Member
136
#2
Mohamed Amine Mahmoudi
102
Super User 2025 Season 1
#3
REUser
8
Share
Report
All responses (
Answers (
