August 11, 2009
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 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 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 WHERE SOURCDOC not in ('BBF','P/L') UNION ALL SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,CURNCYID FROM GL30000 WHERE SOURCDOC not in ('BBF','P/L')) GL INNER JOIN GL00105 GM ON GL.ACTINDX = GM.ACTINDX INNER JOIN GL00100 GA ON GL.ACTINDX = GA.ACTINDX /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ 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
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
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
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
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
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
Awww! Thanks Vic!
-Victoria
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
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
Hi Victoria, would you be able to include a column that provides the journal entry total, ie total debit value or total credit value?
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_Debitsand 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
You’re awesome! That’s perfect!
Erin
Hi Victoria
Do you have a modification for this view that will show the posted transaction from a closed year as well??
Thanks
Vic
Hi Vic,
This should already be bringing in all closed years, as it’s including the GL30000 table.
-Victoria
Thanks Victoria I mis wrote what we were looking for, We found it in the view with the unposted as well that of course you already have done. As usual you are ahead of the curve!!!!!
Thanks
Vic
Thanks Vic!
Glad you were able to find what you needed.
-Victoria
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
Hi Deborah,
You can add the following line after Currency_ID (before FROM) to accomplish this:
,GL.ACTINDX-Victoria
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
Hi Vic,
To remove the exclusions you can remove the following code (which appears twice):
WHERE SOURCDOC not in ('BBF','P/L')-Victoria
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
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
As always you come to the rescue of us mere mortals!!!!
Thanks
LOL! Anytime.
-Victoria
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
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
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
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
Victoria
There were only results in the GL20000 table
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
Sorry Victoria it did not change. We are still missing the debit amount
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
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!
Jeff,
I have seen 2 different requests of this type:
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
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!
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
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
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
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
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
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
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
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
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
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
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