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 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 https://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
Victoria Yudin 
Hi Victoria,
i have this query which i made apparently shows only those transactions in that period which has some sort of debit or credit amount . . The user wants also those periods showing which does not have any debit or credit . . The period for a particular account shows in GP even if it does not have any debit or credit !! I hope i make sense
select a.YEAR1 as YEAR,a.PERIODID,a.PERDBLNC as [PERIOD BALANCE],a.CRDTAMNT as [CREDIT AMOUNT],
a.DEBITAMT as [DEBIT AMOUNT],b.ACTINDX,b.ACTNUMBR_1,b.ACTNUMBR_2,b.ACTNUMBR_3,
b.ACTNUMBR_4,b.ACTNUMBR_5,b.ACTDESCR as [ACCOUNT DESCRIPTION],b.ACTIVE from GL10110 a,GL00100 b
WHERE a.ACTINDX = b.ACTINDX
LikeLike
Hi Magid,
Sorry, I am not quite clear on what you’re looking for. Can you maybe give some more detail or an example?
-Victoria
LikeLike
The query i have only shows those periods in which there is some sort of debit or credit amount related to that account from the both GL tables.. for example i have account number 001-01-000-1110-001 for this account it shows me period from 0-5 then it skips period 6 and then shows period 7-11 that is because period 6 dint have any debit or credit done for that account is what i am thinking..
i want to see all the periods even if they don;t have debit or credit
LikeLike
Magid,
One way to do this would be to first create a query that has all the periods, then link from that to the data. That way it would show the zero’s for the periods that are missing. Or you could do something like what I do here: https://victoriayudin.com/2014/03/17/sql-view-for-dynamics-gp-open-year-gl-trial-balance/.
-Victoria
LikeLike
Hi Victoria,
I am wondering how I could limit this to a specific Account Category within the code. I know I can do it the Power Query Editor or even after I run the code, but currently this pulls in so many transactions it takes forever for the sheet to refresh, so I’m hoping limiting it within the code will allow me to run the report much quicker.
My thought was to add something like
where Account_Description = ‘Sales Expense’
but I can’t seem to figure out how to make that work. Any help would be greatly appreciated!
LikeLike
Hi Lyndsey,
Try this query:
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
and ACTINDX in (select ACTINDX from GL00100
where ACTDESCR = ‘Sales Expense’)
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
and ACTINDX in (select ACTINDX from GL00100
where ACTDESCR = ‘Sales Expense’)) 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
-Victoria
LikeLike
PS – You might have to fix all the single quotes after copying and pasting that into SQL.Just wanted to mention that before you got an error.
LikeLike
Hi Victoria, asking a very basic question (newbie…) so sorry in advance. In select statement from GL30000; can you clarify what ‘HSTYEAR YEAR1’ means? is this pulling all historical entries in the system? Or is it pulling prior year only, i.e. year1? if I wanted to limit the script to pull current year posted journal entries, and just the prior year, what would that look like?
LikeLike
Hi JR,
My code pulls everything, all years. Probably the easiest thing to do would be to create the view, using this code. Then you can run the following to pull just the date range you want:
select * from view_Posted_GL_Trx
where Trx_Date between ‘2018-01-01’ and ‘2019-12-31’
-Victoria
LikeLike
Hi Victoria, I am trying to add the originating Document Date for AP transactions to view_Posted_GL_Trx. I’ve linked to PM20000 & PM30200 but I’m not getting any results in the Doc_Date column. Do you see what’s wrong with the below? Thank you!
SELECT
convert(date,TRXDATE,104) Trx_Date,
JRNENTRY Journal_Entry,
‘ ‘ Doc_Date,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
convert(numeric(19,2),DEBITAMT) Debit_Amount,
convert(numeric(19,2),CRDTAMNT) Credit_Amount
FROM
(SELECT ACTINDX, TRXDATE,
JRNENTRY, PM20000.DOCDATE, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT
FROM GL20000
LEFT OUTER JOIN
PM20000 on GL20000.ORTRXSRC = PM20000.TRXSORCE
WHERE SOURCDOC not in (‘BBF’,’P/L’) and TRXDATE between ‘2019-03-17 00:00:00.000’ and ‘2019-03-17 00:00:00.000’
UNION ALL
SELECT ACTINDX, TRXDATE,
JRNENTRY, PM30200.DOCDATE, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT
FROM GL30000
LEFT OUTER JOIN
PM30200 ON GL30000.ORGNTSRC = PM30200.TRXSORCE
WHERE SOURCDOC not in (‘BBF’,’P/L’) and TRXDATE between ‘2019-03-17 00:00:00.000’ and ‘2019-03-17 00:00:00.000’) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
LikeLike
Hi Debi,
I see a few issues:
The linking you have to the PM tables will not work. You cannot simply link on the TRXSORCE as not is not unique to a transaction. Linking this way will give you lots of incorrect and duplicate results. I would recommend limiting it only to lines where the SOURCDOC starts with ‘PM’ and also link on the GLXXXXX.ORTRXTYP = PMXXXXX.DOCTYPE and GLXXXXX.ORCTRNUM = PMXXXXX.VCHRNMBR.
The GL20000 table can have transactions in both the open and history PM tables and so can the GL30000 table. So it’s not a one to one, as you have linked. In fact, most of the transactions for March will likely be in the PM30200 table, as they are probably already paid.
Your Doc Date field in the select statement at the top is hard coded to be a blank. You might have done this on purpose since it was not working, just wanted to make sure.
Hope that helps.
-Victoria
LikeLike
Thank you so much Victoria.
From your response I don’t think this will work like I want without adding code for all modules since I want all transactions that makeup the GL balance but only need the Invoice Date if it’s Payables.
I originally tried to modify the GP Trial Balance by Period but as you indicated I would, I was getting dups. I thought that a view might be an easier route.
Is there anything else available that retrieves the originating doc date other than a customized report?
Thanks for all you do for the GP community!
Debi
LikeLike
Hi Debi,
I don’t think there is anything existing to do this. 😦 I think it is going to have to be a custom report.
-Victoria
LikeLike
Thank you Victoria!
LikeLike
Hi Victoria. Thank you for sharing your experience via this website, it is truly remarkable what one can find here. After some searching around, I thought it would be best to try ask you:
We are trying to export all posted journal entries from the current year, with the indication of what periods the journal entries were posted to. I guess it’s the PERIODID field, however, our simple extract just shows all transactions going to period “0”. This doesn’t seem correct. Would you have any advice? Thank you!
LikeLike
Hi Gabriel,
Interesting, looking at our production data, the only entries with 0 in the PERIODID are those from the year end close transactions, which is correct, as GP considers those as period 0. That said, I often hear stories about the majority of transactions in other people’s data having the PERIODID equal to zero, so I would recommend not relying on that for anything. Why not simply use the TRXDATE to determine the period?
-Victoria
LikeLike
Thanks so much for responding Victoria.
We are trying to run the usual test against the data to identify transactions that may have been posted into “closed” periods. This would require that we are able to see the TRXDATE for a given transaction that is outside of a period. It seems we would need to have that second data point, and I thought it would be the PERIODID.
TRXDATE will allow us to see transactions booked close to period cut off date, that should be easy.
I will play with this a bit more to see what we can get.
Thanks again for your response. If you have any other hints, I’d be grateful.
LikeLike
Hi Gabriel,
TRXDATE is always going to be the GL Posting Date specified on a transaction, regardless of when it was posted. PERIODID, when it works, is simply the month (or period, if you’re on a fiscal year that’s not the calendar year) of the TRXDATE, so those should always match. It sounds like you may be looking for transactions posted to a particular month AFTER the month is closed. If so, you may be able to use the DEX_ROW_TS – that is the time stamp of when the transaction actually hit the General Ledger. If you use that, please note it uses UTC time – which may be several hours off your time.
Hope that helps,
-Victoria
LikeLike
Thank you very much Victoria.
LikeLike
Victoria, first let me say thank you! Great info, and will definitely check out your books (any chance you have one coming out for GP 2016?).
Question is related to adding additional transaction descriptive info to the query. I currently have to run these via smartlist company by company. Can you tell me what tables contain:
“Description”
“Originating Master Name”
“User Defined 1, 2, etc”
Thanks,
Kevin
LikeLike
Hi Kevin,
Thanks for your kind words. There are currently no plans for a GP 2016 book…not enough interest. 🙂
For the additional data you’re looking for:
-Victoria
LikeLike
Your site is a wonderful resource – appreciate all you do! I am needing a view that will show me all transactions in the Cash account, with the offsetting GL account – do you have any suggestions? I created a report in MR and broke it out using Attributes of Source Document so I know where the transaction originated from and have the distribution reference, but I can’t figure out how to get the actual other side of the GL transaction. Thanks!
LikeLiked by 1 person
Hi Sally,
I can’t see how you could do this in MR. I have created similar reports before for SmartList and Excel using custom SQL code. The basic logic for this is a ‘two step’ process: first step is to get a list of all the GL transactions that have the account you’re after, second step is to get all the details for those transactions. If you were just looking at the open year transactions, the SQL code would look something like this:
select *from GL20000
where JRNENTRY in
(select JRNENTRY
from GL20000
where ACTINDX in
(select ACTINDX
from GL00105
where ACTNUMST = 'ACCOUNT'))
Just replace ACCOUNT with your full account number (including dashes, etc). If you need additional help on this, send me an email.
-Victoria
LikeLike
THANK YOU! That was just what I needed!
LikeLike
Hi Victoria,
I have a questions with regards to the SQL view for all posted GL transactions in Dynamics GP. Our company auditors have requested to review all GL transactions by month. My company does more than a million GL entries a month. So trying to export to an Excel file doesn’t work. I did adjust the debit and credit columns so that they would be before the reference information. By using Excel Power Query I am able to summarize by GL account number.
the issue is that the users are entering in reference information or descriptions on the various transactions and they are using commas. So when exporting to a CSV file format it moves the columns around. Would you have a recommendation on how to export the GL Transactions without that issue?
Thanks,
Kevin
LikeLike
Hi Kevin,
One idea would be to change the SQL code to replace the commas in fields that are giving you trouble with something else, maybe a dash or a semicolon…I think that might be the fastest way to address the export issue.
So instead of bring in the field REFRENCE, change the code to something like this:
REPLACE(REFRENCE, ‘,’, ‘ -‘)
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
I am newbie to SQL Sever database and would like to know is there any way to run this query against all the databases at a time in GP
we would wanted to pull all the posted GL transactions from the GP , we have around 12 databases instances against the GP like ELIM, Farm etc..
Thanks
Ramesh
LikeLike
Ramesh,
I think there are a couple of options:
-Victoria
LikeLike
Thank you very much.
This is working perfectly for me.
I really appreciate your efforts to make things so simple and easy to understand.
-Ramesh
LikeLike
I’m sure you plan on limiting your data with date ranges or something, but you are talking about one crazy amount of returned data; if you want data from all 12 companies at the same time. We have 10 companies and I could never imagine all of the data that would be returned on a SQL statement like this. Maybe you have better plans for that, but that’s just my thought when I first read what you were asking for.
May I also suggest looking into Management Reporter 2012 for your needs? It’s free with your Dynamics GP 2013 license. You can create some really great reports and they can also drill down to transaction level detail.
For your research:
MR 2012 Downloads and Documentation
Creating Consolidated Financial Statements using Management Reporter
Good luck!
John Olley
LikeLike
Thanks John for your valuable inputs.
Yes its a huge data so temporarily i just used date filter and pulled data for with date range.
I will explore the Management reporter . Thank you again.
-Ramesh
LikeLike
Another option would be to turn this into a stored procedure with a few of the common parameters (like amount, account and date range) and set it up in Excel. That would probably give your users the most bang for their buck in terms of speed and usability.
-Victoria
LikeLike
Thanks Victoria,
I have an other question regarding additional fields.
Is there a field that indicates which ones are manual JE versus an automated JE?
Also, is there a field available that reflects the date that the entry was actually posted? Not looking for the effective date but the actual date the posting keystroke was made?
-Ramesh
LikeLike
Ramesh,
This may depend on what you mean by ‘automated JE’ – I don’t know everything you have going on in your system. However, typically, journal entries entered manually will have a SOURCDOC of ‘GJ’.
I believe ORPSTDDT stores the date the entry was posted. You might want to test that to be 100% certain.
-Victoria
LikeLike
Thanks Victoria.
SOURCDOC and ORPSTDDT columns works for us.
-Ramesh
LikeLike
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
LikeLike
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
LikeLike
This has been a great tip. Which field contains the PO #? Thanks!
LikeLike
Carlo,
Typically PO numbers do not get stored in the General Ledger.
-Victoria
LikeLike
So with the right configuration in GP, it can get stored in the GL? Thanks!
LikeLike
Carlo,
No, I am not aware of any out-of-the-box way to do this.
-Victoria
LikeLike
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!
LikeLike
Hi Jeanne,
I have added that field for you at the end. 🙂
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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:
Script 2:
select count (*) from GL20000 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0 and OPENYEAR = 2013Script 3:
Script 4:
Script 5:
-Victoria
LikeLike
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!!!!
LikeLike
Larry,
Thanks for the update. Glad everything is working as expected. I sometimes forget that I might be chasing a moving target, also. It’s all good. 🙂
-Victoria
LikeLike
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?
LikeLike
Larry,
I just updated this view to include account categories – they are in GL00102. 🙂
-Victoria
LikeLike
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.
LikeLike
John,
You can just add the following before the FROM:
,GA.ACTNUMBR_1, GA.ACTNUMBR_2, GA.ACTNUMBR_3-Victoria
LikeLike
Sorry, before the first FROM (after Currency_ID). 🙂
-Victoria
LikeLike
Perfect, thanks! 🙂
-John Olley
LikeLike
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
LikeLike
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, TRXDATEHope that helps.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thank You Victoria! Works wonderfully!
Laurie
LikeLike
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
LikeLike
Hi Bob,
I am not sure if you can do a ‘union’ in Excel Report Builder. I would recommend instead creating a SQL view – you can use code that is already created here and just modify as needed. Then point the Builder to the SQL view.
-Victoria
LikeLike
Thank you, Victoria! Worked great!
-Bob
LikeLike
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?
LikeLike
Hi Lisa,
The problem with building something like that is that you would need to code it separately for every subledger, which is a pretty daunting task. You most likely need to go through one subledger at a time to track this down. I have code that may have for some of the subledgers, for example: GL Distributions for AR Transactions and GL Distributions for AP Transactions.
-Victoria
LikeLike
Thanks Victoria. This will help.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Awww! Thanks Vic!
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria, would you be able to include a column that provides the journal entry total, ie total debit value or total credit value?
LikeLike
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
LikeLike
You’re awesome! That’s perfect!
Erin
LikeLike
Hi Victoria
Do you have a modification for this view that will show the posted transaction from a closed year as well??
Thanks
Vic
LikeLike
Hi Vic,
This should already be bringing in all closed years, as it’s including the GL30000 table.
-Victoria
LikeLike
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
LikeLike
Thanks Vic!
Glad you were able to find what you needed. 🙂
-Victoria
LikeLike
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
LikeLike
Hi Deborah,
You can add the following line after Currency_ID (before FROM) to accomplish this:
,GL.ACTINDX-Victoria
LikeLike
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
LikeLike
Hi Vic,
To remove the exclusions you can remove the following code (which appears twice):
WHERE SOURCDOC not in ('BBF','P/L')-Victoria
LikeLike
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 https://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
LikeLike
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
LikeLike
As always you come to the rescue of us mere mortals!!!!
Thanks
LikeLike
LOL! Anytime. 🙂
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Victoria
There were only results in the GL20000 table
LikeLike
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
LikeLike
Sorry Victoria it did not change. We are still missing the debit amount
LikeLike
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 https://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
LikeLike
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!
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike