Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 277

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
  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    Glad to help!

  • RJarrell Profile Picture
    RJarrell 277 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

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

    This worked perfectly.

  • Verified answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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 277 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

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

  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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?

  • Suggested answer
    Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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]

    ----------

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

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

  • RJarrell Profile Picture
    RJarrell 277 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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,730 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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 277 on at
    RE: Finding Account Transactions Posted to 2 different departments/funds

    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!

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans