SQL view for easier General Ledger transaction searches in Dynamics GP

Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search type). Often this is fine. However, once in a while this comes back with thousands of results. When you’re doing an ‘or’ search in SmartList, you cannot at the same time add an ‘and’. So you’re kind of stuck here, you can get all the results and then dump them to Excel to do further searching, do multiple searches in SmartList, or you can to go into SQL and do some more sophisticated querying there.

To combat this, I was recently asked to create a custom SmartList that would make it possible to both search for an amount and add some additional criteria. I thought that would be a great topic for my blog, so I have some up with the view below that returns the common columns I like to see when I search the General Ledger and also has a column for ‘Amount’ – this will be either the debit or the credit, whichever is not zero.

While I was at it, I also added a few other things, like a timestamp and the transaction note. I think it’s really useful to be able to see the date and time something was posted using the DEX_ROW_TS (this means it will only work in the last few GP versions, GP 10.0, 2010 and 2013). Having this allows answering a question like, “I know my AR and GL reconciled yesterday afternoon, but now they do not, what was posted for $297 after 5pm yesterday?” The timestamp in my code is converted to EDT, since that’s where I am, but you can easily change the code to work with your time zone if you prefer by editing rows 68 and 70.

Some additional resources:

• General Ledger SQL views
• General Ledger commonly used tables
• Other GP Reporting links

create view view_GL_Trx_for_Searches
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created September 4, 2014 by Victoria Yudin
-- Flexible Solutions, Inc.
-- For other code, please visit http://victoriayudin.com
-- Converts to EDT time zone
-- Update on Sep 14, 2014 to move amounts and account higher
--     in the list of fields
-- Update on Dec 27, 2018 to change to UNION ALL
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
g.OPENYEAR Fiscal_Year,
g.JRNENTRY JE,
case when g.CRDTAMNT <> 0
     then g.CRDTAMNT
	 else g.DEBITAMT
	 end Amount,
g.DEBITAMT Debit_Amount,
g.CRDTAMNT Credit_Amount,
a.ACTNUMST Account_Number,
d.ACTDESCR Account_Description,
g.SOURCDOC Source_Doc,
g.REFRENCE Reference,
g.DSCRIPTN [Description],
g.TRXDATE Trx_Date,
g.TRXSORCE Transaction_Source,
g.LASTUSER Last_User,
g.LSTDTEDT Last_Edited,
g.USWHPSTD User_Who_Posted,
g.ORGNTSRC Batch_Number,
case g.SERIES
	 when 1 then 'All'
	 when 2 then 'Financial'
	 when 3 then 'Sales'
	 when 4 then 'Purchasing'
	 when 5 then 'Inventory'
	 when 6 then 'Payroll – USA'
	 when 7 then 'Project'
	 when 10 then '3rd Party'
	 else ''
	 end Series,
g.ORTRXTYP Originating_Trx_Type,
g.ORCTRNUM Originating_Control_Number,
g.ORMSTRID Originating_Master_ID,
g.ORMSTRNM Originating_Master_Name,
g.ORDOCNUM Originating_Doc_Number,
g.ORPSTDDT Originating_Posted_Date,
g.ORTRXSRC Originating_Trx_Source,
g.CURNCYID Currency_ID,
case g.ICTRX
	 when 1
	 then 'Yes'
	 else 'No'
	 end Intercompany,
g.ORCOMID Originating_Company_ID,
g.ORIGINJE Originating_JE,
g.ORCRDAMT Originating_Credit_Amount,
g.ORDBTAMT Originating_Debit_Amount,
case when g.ORCRDAMT <> 0
	 then g.ORCRDAMT
	 else g.ORDBTAMT
	 end Originating_Amount,
case g.VOIDED
	 when 0
	 then 'No'
	 else 'Yes'
	 end Voided,
cast(dateadd(hour, -4, g.DEX_ROW_TS) as date) EDT_Date_Stamp,
cast(cast(dateadd(
   hour,-4,g.DEX_ROW_TS) as time) as char(5)) EDT_Time_Stamp,
coalesce(n.TXTFIELD,'') Note

from

(select OPENYEAR, JRNENTRY, SOURCDOC,
 REFRENCE, DSCRIPTN, TRXDATE, TRXSORCE,
 ACTINDX, LASTUSER, LSTDTEDT, USWHPSTD,
 ORGNTSRC, SERIES, ORTRXTYP, ORCTRNUM,
 ORMSTRID, ORMSTRNM, ORDOCNUM, ORPSTDDT,
 ORTRXSRC, CURNCYID, NOTEINDX, ICTRX,
 ORCOMID, ORIGINJE, CRDTAMNT, DEBITAMT,
 ORCRDAMT, ORDBTAMT, VOIDED, DEX_ROW_TS
 from GL20000
 union all
 select HSTYEAR, JRNENTRY, SOURCDOC,
 REFRENCE, DSCRIPTN, TRXDATE, TRXSORCE,
 ACTINDX, LASTUSER, LSTDTEDT, USWHPSTD,
 ORGNTSRC, SERIES, ORTRXTYP, ORCTRNUM,
 ORMSTRID, ORMSTRNM, ORDOCNUM, ORPSTDDT,
 ORTRXSRC, CURNCYID, NOTEINDX, ICTRX,
 ORCOMID, ORIGINJE, CRDTAMNT, DEBITAMT,
 ORCRDAMT, ORDBTAMT, VOIDED, DEX_ROW_TS
 from GL30000) g

left outer join SY03900 n
	on g.NOTEINDX = n.NOTEINDX

left outer join GL00105 a
	on g.ACTINDX = a.ACTINDX

left outer join GL00100 d
	on g.ACTINDX = d.ACTINDX

go
grant select on view_GL_Trx_for_Searches 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.
Share this:
Email
Print
Tweet
Like Loading...


	
Related