web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Finding Account Transa...
Finance | Project Operations, Human Resources, ...
Answered

Finding Account Transactions Posted to 2 different departments/funds

(1) ShareShare
ReportReport
Posted on by RJarrell 319

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

Categories:
Microsoft Dynamics GP
I have the same question (0)
  • Suggested answer
    Bill Campbell Profile Picture
    Bill Campbell 12 on at

    Rebecka, your script will have to look to see what Journal Entries have different Segment_1 values.  You will have to look at the Account Master linked to the Transaction Open and or Historical ( GL20000 or GL30000 ) and see where the values are different in the same JE.

    You will need to list the distribution details attached to the JE and then look for the crossing of the Segment_1 values.

    If you want to get more details on how the script would have to be created, I am sure that someone in the community will have some time -but this is where I would start.  Hope this helps some.

  • RJarrell Profile Picture
    RJarrell 319 on at

    Hey Bill! Thanks for the insight!

    I think this is above my sql knowledge at this point. If anyone has any tips, it would be much appreciated!

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,850 Moderator on at

    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.

  • RJarrell Profile Picture
    RJarrell 319 on at

    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.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,850 Moderator on at

    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')

  • Suggested answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at

    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]

    ----------

  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at

    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?

  • RJarrell Profile Picture
    RJarrell 319 on at

    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.)

  • Verified answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at

    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]---

  • RJarrell Profile Picture
    RJarrell 319 on at

    Gerald, you are a saint. THANK YOU THANK YOU THANK YOU!

    This worked perfectly.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 456 Super User 2025 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 429 Most Valuable Professional

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans