You’re offline. This is a read only version of the page.
Announcements
303
Hello all,
Weird issue here. So one of our clients manages different funds/departments by using accounts. Accounts that start with 1 are fund 1, accounts that start with 2 are fund 2, etc. The issue they are having is that they made some mistakes over the last fiscal period and cannot find which transactions are the problems. These transactions are ones that were accidentally posted between funds instead of just in one. We are talking of looking through tens of thousands of transactions. I have tried creating a sql script to find the accounts but have had no luck. I'm just wondering if anyone has had an issue like this in the past that they created a resolution for?
My idea for the sql script would be that you would need to pull back data that has the same journal entry number but has accounts that start with 2 different numbers (if we can specify the numbers, that would also be helpful).
Let me know if I need to clarify anything, etc.
Thanks,
Rebecka
Glad to help!
Hi Rebecca,
I have updated it see below. if you need assistance just call me 703 906 9407
--
--Created By Gerald Buenafe
--BTP Technologies LLC
--www.btptek.com
--Contact Email: info@btptek.com
select OPENYEAR,JRNENTRY,left(ACTNUMBR_1,1) Department, A.TRXSORCE,sum(DEBITAMT-CRDTAMNT) NET INTO #TEMP from GL20000 a inner join GL00105 b on a.ACTINDX=b.ACTINDX
GROUP BY OPENYEAR,JRNENTRY,left(ACTNUMBR_1,1),TRXSORCE
union
select HSTYEAR,JRNENTRY,left(ACTNUMBR_1,1) Department,A.TRXSORCE,sum(DEBITAMT-CRDTAMNT) NET from GL30000 a inner join GL00105 b on a.ACTINDX=b.ACTINDX
GROUP BY HSTYEAR,JRNENTRY,left(ACTNUMBR_1,1),A.TRXSORCE
SELECT 'ALL JE that are Not Matching' as BTPStatus, * FROM #TEMP WHERE NET<>0
--Create a table with Distinct JE
SELECT DISTINCT OPENYEAR,JRNENTRY, TRXSORCE INTO [tag:TEMP3] FROM #TEMP WHERE NET<>0
--show the THe details of the JE
SELECT A.OPENYEAR, A.JRNENTRY,A.TRXDATE,A.REFRENCE,C.ACTNUMST,D.ACTDESCR,A.DEBITAMT,A.CRDTAMNT FROM GL20000 A INNER JOIN [tag:TEMP3] B ON A.OPENYEAR=B.OPENYEAR
AND A.JRNENTRY=B.JRNENTRY AND A.TRXSORCE=B.TRXSORCE INNER JOIN GL00105 C ON
A.ACTINDX=C.ACTINDX INNER JOIN GL00100 D ON A.ACTINDX=D.ACTINDX
UNION
SELECT A.HSTYEAR, A.JRNENTRY,A.TRXDATE,A.REFRENCE,C.ACTNUMST,D.ACTDESCR,A.DEBITAMT,A.CRDTAMNT FROM GL30000 A INNER JOIN [tag:TEMP3] B ON A.HSTYEAR=B.OPENYEAR
AND A.JRNENTRY=B.JRNENTRY AND A.TRXSORCE=B.TRXSORCE INNER JOIN GL00105 C ON
A.ACTINDX=C.ACTINDX INNER JOIN GL00100 D ON A.ACTINDX=D.ACTINDX
DROP TABLE #TEMP
drop table [tag:TEMP3]---
Hey Gerald,
Yes! We need to locate ones that are in different departments/funds basically so it is just the first character that is different. So if a transaction uses accounts that are in the same fund, we don't need to see it. We just want to find the ones that got posted across funds (so a transaction having an account that starts with 1 and then another account that starts with 2 (or 3, or 4, etc.)
Hi Rebecca,
I just sent a query and did not see this one. is it the First Segment that you are looking for and you are using the the first Character to identify if it is the same or not? Not the full segment?
Hi,
Its hard to do a query without looking at the data, but tried it anyways :)
Below is the script and you probably just need to change the ACTNUMBR_1 to whatever the segment of your trying to compare it to:
---------
--Created By Gerald Buenafe
--BTP Technologies LLC
--www.btptek.com
--Contact Email: info@btptek.com
select OPENYEAR,JRNENTRY,ACTNUMBR_1, A.TRXSORCE,sum(DEBITAMT-CRDTAMNT) NET INTO #TEMP from GL20000 a inner join GL00105 b on a.ACTINDX=b.ACTINDX
GROUP BY OPENYEAR,JRNENTRY,ACTNUMBR_1,TRXSORCE
union
select HSTYEAR,JRNENTRY,ACTNUMBR_1,A.TRXSORCE,sum(DEBITAMT-CRDTAMNT) NET from GL30000 a inner join GL00105 b on a.ACTINDX=b.ACTINDX
GROUP BY HSTYEAR,JRNENTRY,ACTNUMBR_1,A.TRXSORCE
SELECT 'ALL JE that are Not Matching' as BTPStatus, * FROM #TEMP WHERE NET<>0
--Create a table with Distinct JE
SELECT DISTINCT OPENYEAR,JRNENTRY, TRXSORCE INTO [tag:TEMP3] FROM #TEMP WHERE NET<>0
--show the THe details of the JE
SELECT A.OPENYEAR, A.JRNENTRY,A.TRXDATE,A.REFRENCE,C.ACTNUMST,D.ACTDESCR,A.DEBITAMT,A.CRDTAMNT FROM GL20000 A INNER JOIN [tag:TEMP3] B ON A.OPENYEAR=B.OPENYEAR
AND A.JRNENTRY=B.JRNENTRY AND A.TRXSORCE=B.TRXSORCE INNER JOIN GL00105 C ON
A.ACTINDX=C.ACTINDX INNER JOIN GL00100 D ON A.ACTINDX=D.ACTINDX
UNION
SELECT A.HSTYEAR, A.JRNENTRY,A.TRXDATE,A.REFRENCE,C.ACTNUMST,D.ACTDESCR,A.DEBITAMT,A.CRDTAMNT FROM GL30000 A INNER JOIN [tag:TEMP3] B ON A.HSTYEAR=B.OPENYEAR
AND A.JRNENTRY=B.JRNENTRY AND A.TRXSORCE=B.TRXSORCE INNER JOIN GL00105 C ON
A.ACTINDX=C.ACTINDX INNER JOIN GL00100 D ON A.ACTINDX=D.ACTINDX
DROP TABLE #TEMP
drop table [tag:TEMP3]
----------
This script would exclude those two accounts.
SELECT JRNENTRY,GLYEAR,ACTNUMST,TRXDATE,DEBITAMT,CRDTAMNT FROM (
SELECT JRNENTRY,OPENYEAR AS GLYEAR,ACTINDX,TRXDATE,DEBITAMT,CRDTAMNT FROM TWO..GL20000
UNION ALL
SELECT JRNENTRY,HSTYEAR AS GLYEAR,ACTINDX,TRXDATE,DEBITAMT,CRDTAMNT FROM TWO..GL30000) T
INNER JOIN TWO..GL00105 G ON T.ACTINDX=G.ACTINDX
WHERE LEFT(ACTNUMST,1)='1' AND ACTNUMST NOT IN('1258-01','2568-10')
Hey Richard,
Thanks for the script! I do know how to run queries. We were able to use some of victoria yudins queries to pull transactions. The problem we are having is narrowing it down to only pull transactions that have account numbers that start with different numbers.
For example, Journal entry 1027, hits 2 accounts, 1258-01 and 2568-10. This entry we would want pulled. For Journal 1028, it hits 1258-01 and 1568-10. We would not want to pull this entry.
As usual Bill has pointed you in the right direction. Do you know how to run queries in SSMS?
SELECT JRNENTRY,GLYEAR,ACTNUMST,TRXDATE,DEBITAMT,CRDTAMNT FROM (
SELECT JRNENTRY,OPENYEAR AS GLYEAR,ACTINDX,TRXDATE,DEBITAMT,CRDTAMNT FROM TWO..GL20000
UNION ALL
SELECT JRNENTRY,HSTYEAR AS GLYEAR,ACTINDX,TRXDATE,DEBITAMT,CRDTAMNT FROM TWO..GL30000) T
INNER JOIN TWO..GL00105 G ON T.ACTINDX=G.ACTINDX
WHERE LEFT(ACTNUMST,1)='1'
You could run this query changing TWO to your database. Hopefully this will get you started.
#1
André Arnaud de Cal...
294,161
Super User 2025 Season 1
#2
Martin Dráb
232,942
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
