General Ledger SQL Code


Dynamics GP General Ledger SQL Code

26 Responses to “General Ledger SQL Code”

  1. Hi Victoria,

    Do you know if the batch comment from batch entry window is saved in any of the the GL tables when you post the batch?

    Thanks
    fek

    Like

  2. Hi Victoria,

    First – thank you so much for maintaining such a high quality website for so many years.

    My question: Are you aware of a way to see all of the je’s that have been made to one account? It would be similar to a detailed trial balance, but it would contain only entries that hit a certain account. If you select the account in the cross-reference report, it will show only the lines of the je that hit the account.

    I know this can be done one at a time through JE Inquiry once you know the JE numbers, but this is difficult when an account has many je’s.

    Thank you

    Like

    • Hi David,

      So you want to select an account and then see all the JE lines for JE’s containing that account? So if a JE has 4 lines and you’re selecting one of the accounts you still want to see all 4 of the lines?

      That’s absolutely doable, but would have to be a custom report. Nothing out-of-the-box in GP can do this. And it cannot be done in a SmartList, it has to be somewhere where you can enter a parameter – Excel, SSRS, Crystal…

      If I were doing this, I would write SQL code for this first – the basic logic is a 2 step process:

      1. Find a list of all the JE’s that have the account you want.
      2. Get the details for that list of JE’s.

      Once you have that, you can use it in the reporting tool of your choice.

      -Victoria

      Like

  3. Victoria – is there anyway, in an of itself to know through querying if a GL entry is a Reversing Entry? Right now it appears that the only way to do that is by finding the matching GL Entry that performs the reversal

    Like

    • Hi Devin,

      I believe reversing entries will have a TRXSORCE with ‘REV’ in them. In our data I see either ‘ICREV’ or ‘GLREV’ as the first 5 characters. Also the reversing entry will have the RCTRXSEQ = 1 and the original entry will have the same JE number with RCTRXSEQ = 0.

      Hope that helps.

      -Victoria

      Like

  4. Hello Victoria,
    I am currently using the “AccountTransactions” view to look at monthly inventory count adjustments. I am trying to bring in the item # and a quantity count for each adjustment. To achieve this for inventory, I am using IV30300 which seems to return the results I need. My problem is bringing in the items from the purchasing side. I assumed I would use POP10500 but not being incredibly familiar with GP, I wasn’t quite sure. I have included my query below. Any direction you could give me would be greatly appreciated. Thank you so much for your time.

    Meredith

    SELECT
    at.[TRX Date]
    ,at.[Account Description]
    ,at.[Credit Amount]
    ,at.[Debit Amount]
    ,’QTY’ = SUM(IV33.TRXQTY)
    ,’TOTAL_COST’ = CASE WHEN IV33.TRXQTY>0 THEN SUM(IV33.EXTDCOST) ELSE -SUM(IV33.EXTDCOST) END
    ,at.[Description]
    ,at.[Originating Control Number]
    ,at.[Originating Source]
    ,IV11.ITEMNMBR
    ,iv11.ITEMDESC

    FROM [AccountTransactions] at
    left join IV30300 IV33 on iv33.DOCNUMBR = at.[Originating Control Number]
    left JOIN IV00101 IV11 ON IV33.ITEMNMBR=IV11.ITEMNMBR

    where at.[TRX Date] between ‘2015-11-01’ and ‘2015-11-30’
    and at.[Account Number] = ‘100-50320-‘

    group by at.[Series]
    ,at.[TRX Date], at.[Account Number], at.[Account Description], at.[Credit Amount], at.[Debit Amount], at.[Account Index]
    ,at.[Description], at.[Originating Control Number], at.[Originating Credit Amount], at.[Originating Master ID]
    ,at.[Originating Sequence Number], at.[Originating Source], at.[Source Document], at.[TRX Source], IV11.ITEMNMBR
    ,IV33.TRXQTY, IV33.EXTDCOST, iv11.ITEMDESC, iv33.[DOCNUMBR]

    Like

    • Hi Meredith,

      There should not be inventory adjustments coming from the POP module… Or are you looking to link every GL line that hits inventory back to the source? Part of the problem with that will be that POP transactions are typically posted to the GL in summary, so one GL entry might be associated with 10 line items with different quantities. Or you might have a situation where there are 5 GL distributions from a POP receipt with 12 line items. How are you looking to display this data?

      -Victoria

      Like

  5. Hi Victoria,
    Is there an easy way to identify which GL accounts have never been used?

    Like

    • Hi Beth,

      The code below should give you the list:

      select n.ACTINDX [Account Index],
      rtrim(n.ACTNUMST) Account,
      rtrim(a.ACTDESCR)[Account Name]
      from GL00105 n
      inner join GL00100 a on a.ACTINDX = n.ACTINDX

      where n.ACTINDX
      not in
      (select distinct ACTINDX from GL10001
      union
      select distinct ACTINDX from GL20000
      union
      select distinct ACTINDX from GL30000)

      Please note that this is not checking any setup windows, only GL transactions.

      -Victoria

      Like

      • Hi Victoria,

        Thank you for the query above, i too and looking for GL accounts which have not been used.
        When i use the query above, I am getting an error

        “Ambiguous column name ‘ACTINDX’ ”

        I was curious if you might know what this out be, i suspect the inner join.
        using GP2015 and SQL2014

        Thank you for your time in advance!

        Like

        • Hi Brandon,

          I just tested this code on my GP and it worked with no error.

          I suspect you might have made a change to the code posted above or something didn’t copy properly into SQL.
          Can you please try re-copying the code and testing it again? If you’re still having trouble, it would help to see a screenshot of what this looks like in your SQL Server Management Studio.

          -Victoria

          Like

  6. Hi Victoria,

    I’m looking for a way to pull summary data similar to what is in the AccountSummary view, but includes entries for months with no activity. Reason being that we create YTD balances for an external reporting system and we are running into missing YTD numbers when we select a month that had no activity in it, even if that particular account had activity earlier in the year. Any advice is much appreciated!

    Like

    • Dave,

      To accomplish this you really cannot use the AccountSummary view, at least not as is. You would have to build your own code that starts out with all accounts, then links to the monthly numbers. You can always pull out any rows with all zeros, but at least that will capture all data.

      Another option would be to use something that gets the balances for all accounts that have a balance – take a look at the two Open Year GL Trial Balance views on this page for some ideas on this.

      -Victoria

      Like

  7. Is there a way to pull the company name, or company ID, or database name into the same table with the GL transactions? I’m a total beginner, so feel free to give me push in the right direction. I combined our companies’ GL transactions into one report using “union all,” but now I can’t tell what entities each transaction came from because our account number convention does not define this sufficiently.

    Like

  8. I was wondering if you had an SQL view to show all journal posting details? We usually use the system default “” under the SMARTLIST > Financial > Account Transactions > “” and just use the criterias to filter but our auditor just picked up the fact that if the journal had multiple entries going to the same GL code then the smartlist picked up the 1st amount and duplicated that to all the entries for that same code:

    Ex actual journal
    Balance Brought FWD journal : Debtors DR 500k
    Balance Brought FWD journal : Debtors DR CR 42k
    Balance Brought FWD journal : Debtors DR CR 4k
    etc

    SMARTLIST result
    Balance Brought FWD journal : Debtors DR 500k
    Balance Brought FWD journal : Debtors DR 500k
    Balance Brought FWD journal : Debtors DR 500k

    After a mild heart attack, our controller noticed this problem with the smartlis.

    Like

    • Geraldine,

      That does not sound right. SmartList should be simply pulling the data out of GP exactly how it appears. If you happen to restart the GP application and rerun the same SmartList filtering simply on that one journal entry number, do you still get the same results?

      My SQL code usually filters out the Balance Brought Forward entries, since technically they would be doubling up data, but if you want to, you can change my SQL view for all GL transactions to take out that restriction. (Remove the “SOURCDOC not in (‘BBF’,’P/L’) and” on lines 43 and 51.)

      -Victoria

      Like

  9. Is there any View Available to findoing out Oustanding invoices from GL Table GL20000?

    Like

    • Sanjay,

      There is no direct relationship between the GL20000 table and outstanding invoices, so I am not sure what you mean. If you would like to explain what you’re looking for in more detail, I can try to help.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. SQL view for easier General Ledger transaction searches in Dynamics GP | Victoria Yudin - September 4, 2014

    […] General Ledger SQL Views […]

    Like

  2. SQL view for Dynamics GP open year GL trial balance with month end balances | Victoria Yudin - July 9, 2014

    […] General Ledger SQL Views […]

    Like

  3. SQL view for Dynamics GP open year GL trial balance | Victoria Yudin - March 17, 2014

    […] General Ledger SQL Views […]

    Like

  4. SQL view for all posted GL transactions in Dynamics GP | Victoria Yudin - September 12, 2013

    […] General Ledger SQL Views […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: