Dynamics Community Forum Thread Details

Tracking journal entries creation and posting

Hi,

My external auditors are asking me to provide a listing which identifies the creator and the approver of all journal entries. I know how to retrieve the information regarding who posted the entry from a Smartlist but I don't know where to find the info regarding the creator. Has anyone had the same request and how did you answer it? Thanks!

Categories:

Reporting and BI

Hi,

Do you have the batch approval required from Setup>Posting>Posting setup?

If yes,

LastUser = Who created the entry found in GL20000 (YEAR to date open) and GL30000 (History) in the company database

aprvluserid= who approved the entry found in gl20000 and gl30k tables in the company database.

apprvldt= Date entry was approved found in gl20000 and gl30k tables in the company database.

Here is a sample SQL query you can run( or someone from IT can run) against the company database:

-- for the current open year

SELECT apprvldt [approval date],
aprvluserid [ approver userid],
LASTUSER [ Last User],
USWHPSTD [User Who Posted],
jrnentry
FROM two.dbo.GL20000 WITH (NOLOCK)
where JRNENTRY = 1234

-- for a previous historical year

SELECT apprvldt [approval date],
aprvluserid [ approver userid],
LASTUSER [ Last User],
USWHPSTD [User Who Posted],
jrnentry
FROM two.dbo.GL30000 WITH (NOLOCK)
where JRNENTRY = 1234

-- Please note this will work for a JRNENTRY = 1234

-- You may use a different criteria such

You may remove the where clause line from code - or use

PERIODID =4 for April for open year query or

periodid =4 and hstyear =2010 ( for April 2010)

You could use this script to build a smartlist using smartlist designer for next year.

Here is a post detailing steps to build a new smartlist using smartlist designer

https://lifehacks365.com/2018/03/06/gp-lifehacks-137-add-a-sql-view-to-smartlist-using-designer/

Good luck

Quick Links

Leaderboard