*This post is locked for comments
Leslie, this database is a little over 100GB in size so transported the files will be a bit of a bother. I do have a copy on one my images so perhaps you could remote over to check out your script. I could also backup the image and then upgrade to GP 2013.
Richard,
Will you share your adjustments with me? Something is dreadfully wrong with the script and I'd love it if you would help me correct it.
Regarding cash receipts, I have the same kind of script that compares the cash receipts to the GL, the transfers and payables checks should exist in the CM transaction file - what's not matching up?
Kind regards,
Leslie
PS, I would be tempted to take their data off line, update it to 2013 and run through the new routine for cash reconciliation.
LV
Kind regards,
Leslie
Since GL initiated transactions 'should' only be used for exceptions/corrections - - perhaps isolating them will help bring your volumes down to a more manageable level.
.
Best of luck
Thanks Dinter. I will see if they have a clerk to check out what I will be giving them. These folks do thousands of transactions per day and this is a monumental task.
What I did when I ran into the same problem was do a Smart List of all transactions in the Ledger account that had a transaction code starting GL. All our discrepancies were the result of General Journals being posted to the GL account, and they don't flow to the checkbook. A simplistic approach (I am not a techy person) but it worked for me.
Best of luck!
I was able to adjust the script to prove that all the checkbook transactions for this checkbook exist in the ledger. However, that leaves the checkbook transfers, cash receipts and payables checks to track down. Something is hitting the checkbook GL account but not the checkbook. They reconcile everyday. They claim the gap is widening everyday
Leslie, I am going to have a look at this script. Right now when I run it, it comes back with almost 1,000,000 records.
Thanks Leslie, I will be grabbing a copy of their data tonight and will give this a try.
Oh, the above is using Fabrikam's account indexes.
Kind regards,
Leslie
Hi Richard,
I feel certain there is a more elegant answer to this, but give the following a try and see if it helps. Please let me know what changes you think should be made to it - if it's even close :)
SELECT CM_Union.audittrail
, CM_Union.chekbkid
, gl20000.jrnentry
, gl20000.ortrxsrc
, gl20000.debitamt
, gl20000.crdtamnt
, CM_Union.rcptamt
, gl20000.dex_row_id
FROM gl20000
FULL OUTER JOIN (SELECT audittrail, chekbkid, rcptamt
FROM cm20300
UNION
SELECT audittrail, chekbkid, trxamnt
FROM cm20200) AS CM_Union
ON CM_Union.audittrail = gl20000.ortrxsrc
WHERE ( gl20000.ortrxsrc IS NULL )
OR ( CM_Union.audittrail IS NULL )
AND ( gl20000.actindx IN ( 1, 2, 365, 362,366, 363, 368, 367, 364, 452 ) )
AND ( gl20000.sourcdoc <> 'BBF' )
ORDER BY CM_Union.audittrail, gl20000.dex_row_id DESC, CM_Union.rcptamt
Kind regards,
Leslie
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,253 Super User 2024 Season 2
Martin Dráb 230,188 Most Valuable Professional
nmaenpaa 101,156