binoculars

SQL view for all posted GL transactions in Dynamics GP


Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my General Ledger SQL views page or the GP Reports page. A few notes on this view:

  • Year-end close transactions are excluded
  • Only functional amounts are brought in
  • Unposted transactions will not be shown
create view view_Posted_GL_Trx
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Posted_GL_Trx
-- Created Aug 11 2009 by Victoria Yudin, Flexible Solutions Inc
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns all lines for posted GL transactions
-- Excludes year-end closing entries
-- Returns Functional amounts only
--   GL20000 - Open Year Trx
--   GL30000 - Historical Trx
--   GL00100 - Account Master
--   GL00102 - Account Categories
--   GL00105 - Account Index Master
-- Updated Jun 12 2013 to remove voided transactions
-- Updated Sep 12 2013 to add account categories
-- Updated Nov 7, 2013 to add user who posted
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
YEAR1 Trx_Year,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
ACCATDSC Account_Category,
CURNCYID Currency_ID,
USWHPSTD User_Who_Posted

from
(select ACTINDX, OPENYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL20000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0

 union all

 select ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL30000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0) GL

inner join GL00105 GM
     on GL.ACTINDX = GM.ACTINDX
inner join GL00100 GA
     on GL.ACTINDX = GA.ACTINDX
inner join GL00102 C
     on GA.ACCATNUM= C.ACCATNUM

go
grant select on view_Posted_GL_Trx to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

88 Responses to “SQL view for all posted GL transactions in Dynamics GP”

  1. Victoria,

    Would you happen to know what setting in GP fills the fields: ORDOCNUM, ORMSTRID, ORMSTRNM? It appears that this setting was turned on in my database in March of 2013, but prior to that these fields did not fill in for the same exact set of account numbers. Looking back 2012 was not filled in, but for a few months in 2011 whatever setting was turned on that allowed these fields to populate. Do you know which setting forces GP to fill these fields in?

    Thank you

    Like

    • Hi JR,

      There is not just one setting to populate or not populate these fields. Those fields are typically referred to as the ‘originating fields’ and they get populated when you post from a subledger to the General Ledger in detail. Posting in detail means that you are creating one GL transaction for each subledger transaction. (As opposed to posting is summary, where you are creating one summarized GL entry for an entire batch of subledger transactions.)

      The posting settings can be different for each type of transaction and can be changed on the Posting Setup window (Microsoft Dynamics GP | Tools | Setup | Posting | Posting). Please be very careful about making changes on this window – there is no undo button. There are also additional summary/detail settings that can be set at the GL account level on the Account Maintenance window (Cards | Financial | Account). I typically recommend to set all GL account settings to Detail and control the posting on the Posting Setup window.

      Hope that helps.
      -Victoria

      Like

  2. Victoria – awesome script (as usual). We have one more field that we need to add, it’s the USWHPSTD – I see from above that it comes from GL1000 – but how do I incorporate that into the script (joins still baffle me).
    Thanks for the help!

    Like

  3. Victoria, Your Budget Script was simply AWESOME! You are a life saver. I was wondering if you could tell me how to do the following:

    What I need to do is this:

    1. Union All the GL11111 table into the below sql code. I need all the Records returned. This will allow me to do some historical budget analyses.

    2. I need to Add Year1 in GL11110 and GL1111 tables and BUDGETID in GL0120 as part of the output so that I can use them, I tried adding them in but then either my amount or BudgetAmt were blank. This will allow me to do a Matrix and dynamically display as many trailing years as needed.

    I took your code and added in the Category table so that we can filter by category and also the main Account tables

    SQL CODE BEGIN——

    SELECT n.ACTNUMST AS Account, d.ACTDESCR AS Name, a.PERIODID AS Period, SUM(CASE a.AmtType WHEN ‘Actual’ THEN Amt ELSE 0 END) AS Actual_Amt,
    SUM(CASE a.AmtType WHEN ‘Budget’ THEN Amt ELSE 0 END) AS Budget_Amt, dbo.GL00102.ACCATDSC, d.PSTNGTYP
    FROM dbo.GL00102 INNER JOIN
    dbo.GL00100 AS d ON dbo.GL00102.ACCATNUM = d.ACCATNUM RIGHT OUTER JOIN
    (SELECT ACTINDX, PERIODID, BUDGETAMT AS Amt, ‘Budget’ AS AmtType
    FROM dbo.GL01201
    WHERE (BUDGETID IN (@Budget_Name))
    UNION ALL
    SELECT ACTINDX, PERIODID, PERDBLNC AS Amt, ‘Actual’ AS AmtType
    FROM dbo.GL11110
    WHERE (YEAR1 IN (@Year) and PERIODID <=@Period)) AS a ON d.ACTINDX = a.ACTINDX

    Like

    • Larry,

      Try this:

      select
      n.ACTNUMST Account, 
      d.ACTDESCR Name, 
      a.PERIODID Period, 
      sum(case a.AmtType when 'Actual' 
          then Amt else 0 end) Actual_Amt,
      sum(case a.AmtType when 'Budget' 
          then Amt else 0 end) Budget_Amt, 
      c.ACCATDSC Category, 
      d.PSTNGTYP Posting_Type
      from 
      from 
      (select ACTINDX, PERIODID, BUDGETAMT Amt, 
       'Budget' AmtType
       from GL01201
       where BUDGETID = @Budget_Name
         union
       select ACTINDX, PERIODID, PERDBLNC Amt, 
       'Actual' AmtType
       from GL11110
       where YEAR1 = @Year and PERIODID <=@Period
         union
       select ACTINDX, PERIODID, PERDBLNC Amt, 
       'Actual' AmtType
       from GL11111
       where YEAR1 = @Year and PERIODID <=@Period) a
      inner join GL00105 n
      	 on a.ACTINDX = n.ACTINDX
      inner join GL00100 d 
      	 on a.ACTINDX = d.ACTINDX	 
      inner join GL00102 c
      	 on d.ACCATNUM = c.ACCATNUM
      group by n.ACTNUMST, d.ACTDESCR, a.PERIODID, 
      c.ACCATDSC, d.PSTNGTYP 
      

      -Victoria

      Like

      • Thanks Victoria!!

        All the historical transactions are now included!!!

        Here is a really hard question for you….

        The Above code works really good using a single year parameter, However, we would like to PIVOT Years AND BUDGETS to do a analyses.

        The problem is that some years have multiple budgets The question is:

        1. Can we Pivot on BudgetID to display all Budgets in columns?

        So that if we choose 2 budgets for 2013 it would display like this:

        BUDGET 1 BUDGET 2
        Account | Actual | Budget | Actual | Budget

        100-00-00 $100 $500 $200 $600

        Like

        • Hi Larry,

          I don’t know if you can ‘dynamically’ do this easily. By dynamically I mean without hardcoding the years and budgets. You might want to check with a SQL resource for something like that.

          -Victoria

          Like

  4. Hi Victoria,

    I have run this code on this page and discovered that it does not include all transactions.

    I ran a query on GL20000 for year 2013 and the GL returned 55,552 Rows I then ran this script with a filter of open year = 2013 and it returned 55,242 rows

    We have no voided transactions or any SOURCDOC not in (‘BBF’,’P/L’

    Why would we get a completely different amount of rows returned when we run a straight report on GL2000

    Like

    • Larry,

      I am adding inner joins to a number of tables. If all accounts are set up properly, that should not cause an issue, but maybe that’s where the discrepancy is coming from. It might be difficult to troubleshoot without looking at your data, but we can try. Please run the following scripts against your data and tell me how many results you get for each:

      Script 1:

      select count (*) from GL20000
      where OPENYEAR = 2013
      

      Script 2:

      select count (*) from GL20000 
      where SOURCDOC not in ('BBF','P/L') 
      and VOIDED = 0
      and OPENYEAR = 2013
      

      Script 3:

      select count (*) from GL20000 
      where ACTINDX not in 
      (select ACTINDX from GL00105)
      

      Script 4:

      select count (*) from GL20000 
      where ACTINDX not in 
      (select ACTINDX from GL00100)
      

      Script 5:

      select count (*) from GL00100
      where ACCATNUM not in 
      (select ACCATNUM from GL00102)
      

      -Victoria

      Like

      • Hi Victoria,

        They all matched up. so weird!

        Unless, the accounting department actually posted Items after I had ran your script , but before I checked the GL2000 account.

        I think that’s what may have happened…I am very sorry , how embarrassing.

        Anyway thanks so much for your help. This script is completely awesome and has helped me so much in understanding the financial data and table structures for GP.

        I hope to contribute sometime in the future as we are developing many reports in SQL report builder. I would love to give back to the community that has helped so much.

        Again, thank you Victoria, you are the absolute master of GP tables and SQL scripts!!!!

        Like

  5. Hi Victoria,

    This view is real good. How could we include our category of accounts so that when generating a report we could group by, Revenue, Expenses, etc…? We would also like to include some custom names we use for example:

    Dues
    Dues – Bad Debt
    Special Projects
    Special Projects – Bad Debt
    Recoveries
    Bad Debt & Fx Loss for Recoveries
    Other
    Enforcement
    Public Policy
    Public Relations
    Hotline
    Direct Comp
    Benefits
    Professional Fees
    Travel and Entertainment
    All Other

    Arent these stored in the GL40200 Table?

    Like

  6. What if I wanted to add the GL00100 for ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3? We would like to run this report based on account segments as well. It’s probably a simple Union? I think I did something wrong with the Union and couldn’t get it to work correctly.

    Like

  7. Hi Victoria,

    I was thrilled to find your SQL script to get transaction detail from the GL! I am working on a new KPI that needs daily transactions, and this is a great starting point. I saw a post earlier that needed the credit and debit added. What I need to do is 1) Total the entries for each day, and then 2) do a calculation using the totals from two different accounts for each day.

    For example, I have two unit accounts that have daily information on number of theatre tickets sold, and another with total museum attendance. I need to divide the number of tickets for the day, by the total museum attendance to get the capture rate.

    In some other instances, I need to take the total revenue for the day and divide it by the number of tickets to get the dollars per transaction. In this case the revenue may have several postings per day, and so it is necessary to total the daily amounts first.

    I’m hoping you can help with this. I’ve made several attemps, but I’m still not getting the results that I need.

    Thanks in advance!
    Laurie

    Like

    • Hi Laurie,

      What you’re asking for can be done, but would probably require some significantly different SQL code from what you see here. If I am understanding correctly, all you need as a start is the total of the transactions per account per day. If the total is the sum of the Debits minus the sum of the Credits, then use can use the following code as a start:

      select ACTINDX, TRXDATE, 
      sum(DEBITAMT-CRDTAMNT) amount 
      from gl20000
      where VOIDED = 0 and SOURCDOC not in ('BBF','P/L')
      group by ACTINDX, TRXDATE
      

      Hope that helps.
      -Victoria

      Like

      • Victoria,

        Thank you so much for your reply ! That was very helpful, and a much more efficient way of getting the daily totals for each account. I inserted some qualifiers in the where statement to pull only the data needed for a particular KPI:

        select ACTINDX, TRXDATE,
        sum(DEBITAMT-CRDTAMNT) DailyAmt
        from gl20000
        where (ACTINDX = ‘1504’ or ACTINDX = ‘1506’ OR ACTINDX = ‘1165’)
        AND VOIDED = 0 and SOURCDOC not in (‘BBF’,’P/L’)
        group by TRXDATE, ACTINDX

        I would like to add a calculation to this to divide the DailyAmt from ACTINDX 1504 by the DailyAmt from ACTINDX 1506. To me, that seems like an easy calculation, but I’m evidently not referencing the ACTINDX correctly.

        I think if I had an example of how to do this, I could apply it and use the structure for the othe KPI’s that I will work on.

        Can you help with this, Victoria?
        Thank you,
        Laurie

        Like

        • Hi Laurie,

          Try this:

          select TRXDATE,
          sum(case when ACTINDX = 1504 then (DEBITAMT-CRDTAMNT) 
              else 0 end) DailyAmt_1504,
          sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
              else 0 end) DailyAmt_1506,
          case when 
               sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
                  else 0 end) = 0 then 0 
               else
               sum(case when ACTINDX = 1504 then (DEBITAMT-CRDTAMNT) 
                  else 0 end)/
               sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
               else 0 end) 
               end KPI
          from gl20000
          where VOIDED = 0 and SOURCDOC not in ('BBF','P/L')
               and ACTINDX in (1504,1506)
          group by TRXDATE
          

          -Victoria

          Like

          • Hi Victoria,

            Thank you so much! That worked beautifully. I’m going to use this as a basis for some of the other KPI’s.

            You’ve been very helpful,
            Laurie

            Like

          • Hi Victoria,

            I was able to replace the GL2000 table with the GL3000 table in the SQL statement and was able to pull the prior year data as well.

            One more quick question: What is the best way to put the two SQL statements together? I would like to create one view that will include both current year and at least one prior year of information.

            Thanks in advance, Victoria

            Laurie

            Like

            • Hi Laurie,

              Here you go:

              select TRXDATE,
              sum(case when ACTINDX = 1504 then (DEBITAMT-CRDTAMNT) 
                  else 0 end) DailyAmt_1504,
              sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
                  else 0 end) DailyAmt_1506,
              case when 
                   sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
                      else 0 end) = 0 then 0 
                   else
                   sum(case when ACTINDX = 1504 then (DEBITAMT-CRDTAMNT) 
                      else 0 end)/
                   sum(case when ACTINDX = 1506 then (DEBITAMT-CRDTAMNT) 
                   else 0 end) 
                   end KPI
              from 
              (
              select TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT
              from GL20000
              where VOIDED = 0 and SOURCDOC not in ('BBF','P/L')
                and ACTINDX in (1504,1506)
              union 
              select TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT
              from GL30000
              where VOIDED = 0 and SOURCDOC not in ('BBF','P/L')
                and ACTINDX in (1504,1506)
              )g
              group by TRXDATE
              

              -Victoria

              Like

  8. Hello Victoria! I’m currently setting up some reports via Excel Report Builder. I would like to include Open and Historical GL data for 4 accounts (not in a range). What is the order for linking the GL20000, GL30000 and GL00100? Is there a way in restrictions to include more than one account from the Account Master table?
    As always, thank you in advance!

    Best regards,
    Bob

    Like

  9. Victoria: I just found your script and I am looking for something that will also join in the subsidiary ledger distribution type. I have an issue with the Cash Management module and the GJ’s are not the issue. It must be non CASH distribution types have my cash accounts included.

    Any suggestions on how to identify the the distribution type attached to gl transactions?

    Like

  10. Hi Victoria – you have some great information on your site!

    We are currently on GP v10. Every quarter we need to generate a report that lists the total number of transactions by series (financial, purchasing, sales). We currently run a smartlist and then just review the total number of transactions. Due to the high volumn this takes a long time to generate. Is there a SQL script or maybe a SSRS report available that would give me this information?

    Thanks in advance!
    Bob

    Like

    • Hi Bob,

      While there is nothing like this available currently, you can certainly create a report like this. You would need to go one module (or series) at a time, decide what types of transactions you want to count and write a script, then you can join them all together for one report. The reason this is not so clear cut is that it really depends on exactly what constitutes a transaction for you, which may differ based on why you want the report and may also differ by module.

      For example, I have seen some companies use this for performance monitoring – how many transactions are our employees processing per day/week/month/quarter? In this case you would want to include everything, posted and unposted, voided, not voided, etc. Other companies may want more filtered numbers to use this for business metrics. For example, we had x number of invoices, and $y in sales, average sale is $y/x – how does that compare to last quarter? In that case you would not just want to add up all sales transactions, but instead only count posted, not voided sales invoices.

      Hope that at least give you an idea of the kinds of questions you need to answer to be able to create a report like this. If you have some more specific questions, please let me know.

      -Victoria

      Like

  11. Victoria again thanks for all the contributions. I have a 3rd party document management system which we are using for some compliance issues. And so I am attempting to write some common SSRS reports to show transactions (GL, AP , & AR, etc.) without attached supporting documents. With AR & AP transactions having posting date fields I can create post year, post month using the post date fields. However GL (financials) has a year but not period and of course (I think) DOES NOT have a post date. I saw a link on the Dynamics community forums which a Tom Foster shows a way to create a post period, but thought I’d ask here if anyone knew of any other way or if I might be missing this idea about post date. My working SQL script with Victoria’s code and Tom’s add code is as follows. Any input would be helpful. Also, I will be mostly reporting in this script for GJ transactions as the AP & AR will get the others.

    SELECT YEAR1 Trx_Year,
    TRXDATE Trx_Date,
    JRNENTRY JRNENTRY,
    ORTRXSRC Originating_TRX_Source,
    REFRENCE Reference,
    DEBITAMT Debit_Amount,
    CRDTAMNT Credit_Amount,
    ACTNUMST Account_Number,
    ACTDESCR Account_Description,
    PERIODID
    FROM
    (SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
    JRNENTRY, ORTRXSRC, REFRENCE,
    DEBITAMT, CRDTAMNT,
    (SELECT SY40100.PERIODID
    FROM SY40101 INNER JOIN SY40100 ON SY40101.YEAR1=SY40100.YEAR1
    WHERE SERIES=0 AND FORIGIN=1
    AND PERIODID0
    AND TRXDATE BETWEEN SY40100.PERIODDT
    AND SY40100.PERDENDT)AS PERIODID
    FROM GL20000
    WHERE SOURCDOC not in (‘BBF’,’P/L’)
    AND RTRIM(SOURCDOC) = ‘GJ’
    UNION ALL
    SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
    JRNENTRY, ORTRXSRC, REFRENCE,
    DEBITAMT, CRDTAMNT,
    (SELECT SY40100.PERIODID
    FROM SY40101 INNER JOIN SY40100 ON SY40101.YEAR1=SY40100.YEAR1
    WHERE SERIES=0 AND FORIGIN=1
    AND PERIODID0
    AND TRXDATE BETWEEN SY40100.PERIODDT
    AND SY40100.PERDENDT)AS PERIODID
    FROM GL30000
    WHERE SOURCDOC not in (‘BBF’,’P/L’)
    AND RTRIM(SOURCDOC) = ‘GJ’
    ) GL
    INNER JOIN GL00105 GM ON GL.ACTINDX = GM.ACTINDX
    INNER JOIN GL00100 GA ON GL.ACTINDX = GA.ACTINDX

    Like

    • Hi Ron,

      If you are entering transactions directly into the General Ledger, the transaction date (TRXDATE) is all there is – that is both the ‘document date’ and the ‘GL posting date’. If your fiscal year is the calendar year and you are using calendar months for your fiscal periods, then you could simply use the month of the TRXDATE to determine the period. Otherwise, your code looks like it is doing that just fine.

      -Victoria

      Like

  12. Hi victoria

    Just wanted to share that i made a tweak to this amazing view. I added the DSCRIPTN field from GL20000. We records the PAYEE in this field. so we can now drill down and see the name of who we paid what to by GL code in a very quick way. This is going to make my budget justification a breeze this year and my controller and owners are in Heaven now because she can reconcile the month much faster and they can see who is spending what to who and what by just opening a spreadsheet. THANKS TO YOU!!!

    Vic

    Like

    • Awww! Thanks Vic!

      -Victoria

      Like

    • Hello,

      Does the table GL30000 contain only the GL transactions coming from AP alone? Or all the transactions generated from other systems (non-AP systems) as well? – I need to extract all GL transactions and not just the AP related journal entries – so if I extract records from GL30000 table will it contain all the journal Entries?

      Thanks in Advance,

      Sreedhar

      Like

      • Hi Sreedhar,

        GL30000 holds all historical year transactions for the entire General Ledger, not just entries coming from AP. GL20000 holds all open year transactions for the entire General Ledger.

        -Victoria

        Like

  13. Hi Victoria, would you be able to include a column that provides the journal entry total, ie total debit value or total credit value?

    Like

    • Hi Erin,

      I am not sure I want to complicate the view above with this, but you can do that by adding the following to the SELECT list, right before the first FROM:
      ,S.Debits Total_Debits

      and the following at the end, before the code in blue:
      LEFT OUTER JOIN
      (SELECT JRNENTRY JE, TRXDATE TrxD, sum(DEBITAMT) Debits
      FROM GL20000
      GROUP BY JRNENTRY, TRXDATE
      UNION
      SELECT JRNENTRY JE, TRXDATE TrxD, sum(DEBITAMT) Debits
      FROM GL30000
      GROUP BY JRNENTRY, TRXDATE) S
      ON GL.JRNENTRY = S.JE
      AND GL.TRXDATE = S.TrxD

      -Victoria

      Like

  14. Hi Victoria

    Do you have a modification for this view that will show the posted transaction from a closed year as well??

    Thanks
    Vic

    Like

  15. Deborah Newcomer Reply July 1, 2011 at 10:51 am

    Hi Victoria,

    All your posts are greatly appreciated. Thank you for putting this info out there! I have one question on the Posted GL Transactions view. Can the ACTINDX field from the transaction be included? I have not been successful in trying to add it.

    Thank you!
    Deborah

    Like

  16. Hi victoria

    After we ran the yr end close some deposits are no linger in this view. I see this in your comments
    Excludes year-end closing entries

    Is there any way to get those back in the view?

    Thanks
    Vic

    Like

    • Hi Vic,

      To remove the exclusions you can remove the following code (which appears twice):
      WHERE SOURCDOC not in ('BBF','P/L')

      -Victoria

      Like

      • Thanks Victoria I knew you would have the answer.
        When I remove the code though I get this error
        Msg 102, Level 15, State 1, Procedure x_V_Posted_GL_Trx, Line 47
        Incorrect syntax near ‘ACTINDX’. Here is what it looks like now

        USE [WESTN]
        GO
        /****** Object: View [dbo].[x_V_Posted_GL_Trx] Script Date: 02/04/2011 08:10:38 ******/
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        ALTER VIEW [dbo].[x_V_Posted_GL_Trx]
        AS

        /*******************************************************************
        view_Posted_GL_Trx
        Created Aug 11, 2009 by Victoria Yudin – Flexible Solutions, Inc.
        For updates see http://victoriayudin.com/gp-reports/
        – Returns all lines for posted GL transactions
        – Excludes year-end closing entries
        – Returns Functional amounts only
        GL20000 – Open Year Trx
        GL30000 – Historical Trx
        GL00100 – Account Master
        GL00105 – Account Index Master
        *******************************************************************/

        SELECT YEAR1 Trx_Year,
        TRXDATE Trx_Date,
        JRNENTRY Journal_Entry,
        ORTRXSRC Originating_TRX_Source,
        REFRENCE Reference,
        ORMSTRID Originating_Master_ID,
        ORMSTRNM Originating_Master_Name,
        ORDOCNUM Originating_Doc_Number,
        DEBITAMT Debit_Amount,
        CRDTAMNT Credit_Amount,
        ACTNUMST Account_Number,
        ACTDESCR Account_Description,
        CURNCYID Currency_ID
        FROM
        (SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
        JRNENTRY, ORTRXSRC, REFRENCE,
        ORDOCNUM, ORMSTRID, ORMSTRNM,
        DEBITAMT, CRDTAMNT, CURNCYID
        FROM GL20000

        UNION ALL
        SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
        JRNENTRY, ORTRXSRC, REFRENCE,
        ORDOCNUM, ORMSTRID, ORMSTRNM,
        DEBITAMT, CRDTAMNT,CURNCYID
        FROM GL30000
        GL
        INNER JOIN GL00105 GM
        ON GL.ACTINDX = GM.ACTINDX
        INNER JOIN GL00100 GA
        ON GL.ACTINDX = GA.ACTINDX

        Thanks
        Vic

        Like

        • Hi Vic,

          Looks like you removed an extra )

          In the second WHERE clause there were 2 closing parentheses, you only need to remove one, leave the other.

          -Victoria

          Like

          • As always you come to the rescue of us mere mortals!!!!

            Thanks

            Like

            • LOL! Anytime. :-)

              -Victoria

              Like

              • Hi Victoria,

                When we did our close we removed those transactions. I belive they went to a history table. Is there a way to include the history table in the view as well?

                Thanks

                Like

                • Vic,

                  The history table is GL30000 and is being included in this view already. Are you only missing certain transactions? Or all historical transactions? Can you still see these in GP? In SmartList, for example?

                  -Victoria

                  Like

                  • Victoria

                    Here is what my Controller said

                    “The deposit/cash receipt transactions (debits) still do not appear. However, the offsetting A/R transactions (credits) appear to the correct GL.”

                    I am way out of my paygrade for accounting speak!!! LOL

                    Does this help? Thanks

                    Like

                    • No, sorry. Since I am not filtering out anything else except unposted transactions, I would have to suspect what you are looking for may not actually be posted in the General Ledger. Can you see these transactions in SmartList under Account Transactions? If so, add a column called Document Status – what is the status?

                      An alternative would be to look for this using the Journal Entry number – can they give you one to look for? Once you have that, run the following code in SQL substituting YourNumber in:
                      SELECT * FROM GL10000 WHERE JRNENTRY = 'YourNumber'
                      SELECT * FROM GL20000 WHERE JRNENTRY = 'YourNumber'
                      SELECT * FROM GL30000 WHERE JRNENTRY = 'YourNumber'

                      you should only see results for one of the three tables above, which is it?

                      -Victoria

                      Like

                    • Victoria

                      There were only results in the GL20000 table

                      Like

                    • Vic,

                      There are 2 places in the code that say INNER JOIN (toward the bottom). Can you try changing them both to LEFT OUTER JOIN and rerunning it? Does that make a difference?

                      -Victoria

                      Like

                    • Sorry Victoria it did not change. We are still missing the debit amount

                      Like

                    • Victoria

                      Success! I was viewing this in Access, so I just relinked the view and everything was there. Your changes fix the issue. Here is what I have now

                      As usual your help is invalubale, and the Dynamics Community is lucky to have you!!!!

                      Thanks
                      Vic

                      USE [WESTN]
                      GO
                      /****** Object: View [dbo].[x_V_Posted_GL_Trx] Script Date: 02/04/2011 14:15:37 ******/
                      SET ANSI_NULLS ON
                      GO
                      SET QUOTED_IDENTIFIER ON
                      GO
                      ALTER VIEW [dbo].[x_V_Posted_GL_Trx]
                      AS

                      /*******************************************************************
                      view_Posted_GL_Trx
                      Created Aug 11, 2009 by Victoria Yudin – Flexible Solutions, Inc.
                      For updates see http://victoriayudin.com/gp-reports/
                      – Returns all lines for posted GL transactions
                      – Excludes year-end closing entries
                      – Returns Functional amounts only
                      GL20000 – Open Year Trx
                      GL30000 – Historical Trx
                      GL00100 – Account Master
                      GL00105 – Account Index Master
                      *******************************************************************/

                      SELECT YEAR1 Trx_Year,
                      TRXDATE Trx_Date,
                      JRNENTRY Journal_Entry,
                      ORTRXSRC Originating_TRX_Source,
                      REFRENCE Reference,
                      ORMSTRID Originating_Master_ID,
                      ORMSTRNM Originating_Master_Name,
                      ORDOCNUM Originating_Doc_Number,
                      DEBITAMT Debit_Amount,
                      CRDTAMNT Credit_Amount,
                      ACTNUMST Account_Number,
                      ACTDESCR Account_Description,
                      CURNCYID Currency_ID
                      FROM
                      (SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
                      JRNENTRY, ORTRXSRC, REFRENCE,
                      ORDOCNUM, ORMSTRID, ORMSTRNM,
                      DEBITAMT, CRDTAMNT, CURNCYID
                      FROM GL20000

                      UNION ALL
                      SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
                      JRNENTRY, ORTRXSRC, REFRENCE,
                      ORDOCNUM, ORMSTRID, ORMSTRNM,
                      DEBITAMT, CRDTAMNT,CURNCYID
                      FROM GL30000
                      ) GL
                      LEFT OUTER JOIN GL00105 GM
                      ON GL.ACTINDX = GM.ACTINDX
                      LEFT OUTER JOIN GL00100 GA
                      ON GL.ACTINDX = GA.ACTINDX

                      Like

  17. Hi Victoria,

    Thanks for sharing these information, but do you happen to have a view where docdates are compared between posted GL and AR transactions where results show what is mismatched on docdates?

    I tried to make use of your SQL view for GL and AR posted transactions and create a condition to show mismatched docdates but i am kinda stuck in the middle.

    Any ideas?

    Thank you!

    Like

    • Jeff,

      I have seen 2 different requests of this type:

      1. Compare the Doc Date to the GL Posting Date in the subledger.
      2. Compare the GL Posting Date in the subledger to the GL Transaction Date.

      Which are you looking to do? The second request is less common and would only occur if users are manually changing the dates on the GL transactions that are created by AR prior to posting them. (Which is not good practice and should be discouraged.) The first request is the more common and there is no reason you need to go to the GL for this, both the Doc Date and GL Posting Date are in the RM tables. You can use my SQL view with all posted Receivables transactions to see these.

      Hope that helps.
      -Victoria

      Like

  18. Hi Victoria:

    I have used many of your views. Thank you so much for the work you have shared with us! I was wondering if it would be possible to add the check # (doc #) from payables management and check date (original doc date) to this view? We have funds that have to have everything documented and need to be able to restrict on GL account number. Currently it is being pulled through GL without the payables info.

    Various AP views have similar information, but I cannot find anything that has everything she needs.

    Thanks in advance for your help!

    Like

    • Hi Brenda,

      Thanks for the kind words!

      The Originating_Doc_Number in this view would have the check number for AP checks, but for the date you would need to link back to the payables tables. Since this view returns all GL transactions, singling out payables only would not really make sense….and linking back to every possible module that generated the original transaction would be a daunting task.

      Have you looked at my SQL view to show all GL distributions for AP transactions? This will have the check number, date and account numbers for the check. I think that this is easier than starting from the GL.

      If this is not what you’re looking for, can you please explain in a little bit more detail? Having an explanation of what a report is needed for would help in recommending the best approach.

      -Victoria

      Like

      • Victoria:

        Thank you for your help! Currently the user pulls everything for a “restricted funds” account that hits the GL (because that is a requirement of receiving these dollars). She pulls at GL level because everything that hits the “fund” can be pulled by date range. We use SmartList to pull this info through GL. Then she manually looks up check numbers and check dates to put on the spreadsheet. However, I believe it will be ok to pull two separate SmartList (one for the GL section not related to AP and one for AP).

        The “SQL view to show all GL distributions for AP transactions” (view_AP_Distributions) does not show the check number in the Document Number field (mine shows a description (for example one of mine shows “TRAVEL 02 24 10″). However, it has all the other information I need. The view_AP_Payment_Apply does have the document number (which shows “TRAVEL 02 24 10″) and the Payment Doc Number (which shows the actual check number), but it does not have the GL account number like “SQL view to show all GL distributions for AP transactions” (view_AP_ Distributions) does.

        Thanks.
        Brenda

        Like

        • Brenda,

          Thanks for the additional detail. As you’ve correctly surmised, you need several different pieces to pull this all together. Since the GL distributions come from the payables invoices, not the checks, to get the check numbers you need to add in the AP Apply information.

          If this is something needed on an ongoing basis and it’s taking the user significant time to gather all the information, you could save all that repetitive work for them by creating one report that returns all the information they need. I would start with the GL data and combine that with the AP Apply information for the AP transactions in your GL data. If there are other types of transactions, you may also want to add links to other modules. This would greatly depend on where these transactions were coming from and what kind of detail was needed.

          This is beyond the scope of what I can do in a blog comment, but if you are interested in getting help in creating this report, my company offers report generation as part of our consulting services.

          -Victoria

          Like

  19. Hi Victoria,
    Love the GL transactions smartlist. I have basically the same thing, but what I want is all the gl transactions that hit a certain account, and I want the item number, qty and stnd cost related to that transaction. I have come close, but am having troubles linking the GL files to the inventory files for gl transactions that don’t have ORMSTRID. If I just link with the transaction source I am getting duplicate records. I tried using the sequence numbers in GL and inventory – but for some reason they don’t match, the inventory ones were double the GL ones. Is there some other file that gets the journal entry lines to the inventory lines, one for one? Here’s my code – that is basically getting the right data – just too much of it.
    select DISTINCT
    GL3.JRNENTRY,
    GL3.SERIES,
    GL3.TRXDATE,
    GL3.ORDOCNUM,
    GL3.ORGNTSRC,
    GL3.ORMSTRNM,
    INV1.ITEMNMBR,
    INV1.TRXQTY,
    INV1.UNITCOST,
    GL3.DEBITAMT,
    GL3.CRDTAMNT,
    ITEM1.STNDCOST,
    INV1.DOCTYPE,
    GL3.OPENYEAR,
    GL3.SOURCDOC,
    GL3.ACTINDX,
    GL3.ORMSTRID,
    GL3.ORCTRNUM

    from (select
    OPENYEAR,
    JRNENTRY,
    SOURCDOC,
    TRXDATE,
    SERIES,
    ORCTRNUM,
    ORMSTRID,
    ORGNTSRC,
    ORMSTRNM,
    ORDOCNUM,
    CRDTAMNT,
    DEBITAMT,
    ACTINDX,
    SEQNUMBR
    from SS8..GL20000 GL2
    union
    select
    HSTYEAR as OPENYEAR,
    JRNENTRY as JRNENTRY,
    SOURCDOC as SOURCDOC,
    TRXDATE as TRXDATE,
    SERIES as SERIES,
    ORCTRNUM as ORCTRNUM,
    ORMSTRID as ORMSTRID,
    ORGNTSRC AS ORGNTSRC,
    ORMSTRNM as ORMSTRNM,
    ORDOCNUM as ORDOCNUM,
    CRDTAMNT as CRDTAMNT,
    DEBITAMT as DEBITAMT,
    ACTINDX as ACTINDX,
    SEQNUMBR as SEQNUMBR

    from SS8..GL30000) GL3

    join SS8..IV30300 INV1 on INV1.TRXSORCE = GL3.ORGNTSRC
    join SS8..GL00105 ACT1 on ACT1.actindx = GL3.actindx
    left outer join SS8..IV00101 ITEM1 on GL3.ormstrid = ITEM1.itemnmbr
    WHERE ACT1.ACTNUMST = ’01-5010-0000-00-0000′
    order by ORDOCNUM

    Like

    • Sorry, hit the return too quickly – also wanted to say I originally had them liked by the original document number from GL to the document number in Inventory but then I was losing the transactions where there was no document number.

      Thanks for your help in advance!
      -Laura

      Like

      • Hi Laura,

        Without looking at your code in detail, my gut reaction is that you will not be able to accomplish your stated goal (“I want is all the gl transactions that hit a certain account, and I want the item number, qty and stnd cost related to that transaction”) with 100% accuracy because of the way GP stores this data. You have seen that you can come close, but because different transactions are stored in different ways, the only way I can think of accomplishing this is to code for each transaction type separately, then bring all the detailed results together. You would probably also need to also have a section for ‘other’ transactions that you may not be able to link to any items. Hope this helps steer you in the right direction.

        -Victoria

        Like

  20. Hi Victoria,

    I need to track who create the transaction for GL.
    Because in Smartlist – Financial – Account Trx we made a filter for ‘Doc Status’=’Work’, we found that User who posted the transaction is not the one who entered it.

    I only find field USWHPSTD in GL10000 which is ‘User Who Posted’.

    Thanks in advance.

    Regards,
    Erik

    Like

    • Hi Erik,

      There is also a LASTUSER column, but the last user may also not be the user that created the transaction. If this is the only thing you want to track like this, the easiest way to accomplish this may be creating a trigger and your own table to track this information. Otherwise, you could look at Audit Trails or Auditor to accomplish this.

      -Victoria

      Like

  21. Hi,

    Do you have query to generate AP Aged TB by vendor wise (Detail and Summary).

    For RM Summary we already have data in table RM00103. Using this we gan generate any ready report. But in case of PM. Vendor Summary table is not showing agening column as table PM00201 is not designed this way.

    If you have ready query for Vendor Agening. Please share with me.

    Regards,
    Santosh

    Like

    • Hi Santosh,

      I do not have a ready query to share for Payables aging. I will add this to my ‘wish list’ for future posts. Or if this is something you need help with right away we could create this as a consulting project for you, please let me know.

      -Victoria

      Like

      • Hi Victoria,

        Yea you can add into your wish list. I have seen many query on GP foums but this was missing in my list so I asked you.

        Anyway that for positive feedback.

        Regards,
        Santosh

        Like

Trackbacks/Pingbacks

  1. Dynamics GP Blog Carnival – I | DynamicsBlogger - March 14, 2012

    [...] well known for her SQL scripts tips and reports tips. In this carnival, I’m picking up her latest SQL view for all posted GL Transactions in Dynamics GP article that will helps us all. Do you want to reset your system password in Dynamics GP? Here is [...]

    Like

  2. Using the Dynamics GP Copy Company Tool | Dynamics GP & Life Conversations - January 24, 2012

    [...] Victoria Yudin’s wonderful blog made these scripts simple. We tweaked the base scripts we found here by adding a “group by” at the end of the script that included the date range we were looking [...]

    Like

  3. SQL view for all GL transactions in Dynamics GP | Victoria Yudin - April 27, 2011

    [...] have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for [...]

    Like

  4. SQL view for all GL transactions in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - April 27, 2011

    [...] 0 I have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for [...]

    Like

  5. Dynamics GP Blog Carnival – I - DynamicsBlogger - September 4, 2009

    [...] well known for her SQL scripts tips and reports tips. In this carnival, I’m picking up her latest SQL view for all posted GL Transactions in Dynamics GP article that will helps us all. Do you want to reset your system password in Dynamics GP? Here is [...]

    Like

  6. Twitter Trackbacks for SQL view for all posted GL transactions in Dynamics GP « Victoria Yudin [victoriayudin.com] on Topsy.com - August 31, 2009

    [...] SQL view for all posted GL transactions in Dynamics GP « Victoria Yudin victoriayudin.com/2009/08/11/sql-view-for-all-posted-gl-transactions-in-dynamics-gp – view page – cached #Victoria Yudin RSS Feed Victoria Yudin » SQL view for all posted GL transactions in Dynamics GP Comments Feed Victoria Yudin Just back from The Partner Event Dynamics GP Windows 7 and Server 2008 R2 compatibility announced Service Pack 4 for Dynamics GP 10 — From the page [...]

    Like

  7. SQL View for all Posted GL Transactions - DynamicAccounting.net - August 12, 2009

    [...] View for all Posted GL Transactions Victoria Yudin has a great post up SQL code designed to show all posted GL Transactions in GP. This is very useful for reporting. Published: Wednesday, August 12, 2009, 05:00 [...]

    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

Follow

Get every new post delivered to your Inbox.

Join 1,494 other followers

%d bloggers like this: