You’re offline. This is a read only version of the page.
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!
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
Share
Report
All responses (
Answers (
1,775
