How to see which User ID Created, Approved, and Posted Historical General Ledger Journal Entries

Here are the steps to create a SQL View and a SmartList Designer report to see who created, approved, and posted Historical General Ledger Journal Entries.

Note: In this example, we are creating a SQL View for the records in the Fabrikam, Inc. Company Database: TWO.

Create a SQL View in Microsoft SQL Server Management Studio.

1. Open Microsoft SQL Server Management Studio.
2. Connect to your Microsoft Dynamics GP Server.
3. Expand the Server name, expand Databases, and then expand DYNAMICS (or the name of the DYNAMICS database).
4. Right-click on Views, and then click New View.
5. In the Add Table window, click Close.
6. In the section with SELECT FROM, replace SELECT FROM with the following:
  - select distinct a.JRNENTRY, a.CRUSRID, b.APRVLUSERID, b.USWHPSTD, a.CMPANYID from DYNAMICS..RSAS1000 as a JOIN TWO..GL30000 as b on a.JRNENTRY = b.JRNENTRY where b.SOURCDOC = ‘GJ’
7. Once the statement is in the section, right-click in that section, and then click Execute Statement.
8. Click File, and then click Save.
9. Enter a name for the View. For example, GL_JE_HIST_ROADMAP_TWO. Note the Name of the View.
10. Click OK.
11. Close the View window.

Create a SmartList Designer report in Microsoft Dynamics GP.

1. Log into Microsoft Dynamics GP as sa or a POWERUSER.
2. Go to Microsoft Dynamics GP | SmartList.
3. Click New.
4. Enter GL_JE_HIST_ROADMAP_TWO, for example, in the List Name field. Note the List Name you enter.
5. For the results to appear in the Auditor section, select Auditor for Product.
6. Expand Views, and then expand System.
7. Mark the checkbox for the SQL View you created in Step 1 and noted in Step 1i.
  - Note: You can add a Relationship to another table and a Filter if needed. Or you can modify the report afterwards to add these options.
8. Click OK.

View the records in SmartList.

1. Expand Auditor.
2. Then select the GL_JE_HIST_ROADMAP_TWO report, for example.

Note:

Below are more scripts you can use to create other SQL Views for the General Ledger Journal Entry Historical transactions (GL30000) that are tracked in the RSAS1000. Please note the RSAS1000 is updated with the General Ledger transactions going forward once the Auditor build containing the Journal Voucher Roadmap is installed on the workstations. The latest builds for Versions 2013, 2013 R2, 2015, 2015 R2, 2016, 2016 R2, and 2018 on our Product Downloads page contain the Journal Voucher Roadmap.

–Historical Journal Entries: All