Do you have GL batch approval workflow turned on in Microsoft Dynamics GP and now your Auditors are asking for a report to show the approvals for your Journal entries? Or do you need to get Workflow approval information for another workflow type? It seems that Auditors are asking for this approval information more and so the purpose of this blog is to help you with how to get this information for them. I hope the options below will be helpful to you in case your Auditor asks for it.
Workflows are nice once in place as it gives you tracking in Dynamics GP of when items may have happened for Auditors, such as the Administration Approval for Security roles, Security Task, and User approval.
OPTION 1: WORKFLOW REPORT:
The Workflow Transaction History Detail report, which was added in GP 2018, is a great report designed just for this purpose. It lists the batch ID, along with the workflow steps and originator/approver that completed each step and the date/time of the action. This option can be used to get workflow history for any of the approval processes. Check it out to see if this report will work for you:
1. On the Reports menu, point to Company and click Workflow History.
2. Name the option.
3. Set the Range restrictions as needed. Under Ranges, click the drop-down list to add restrictions for the Workflow Type needed, and/or Workflow Manager, Workflow User, Completion Date or Due Date, as desired. In my example, I selected the Workflow type for 'General Ledger Batch Approval' under Financial and also a date range on the completion date for today, so I can capture my test batch I just submitted and approved myself as a a test.
4. Click on the Destination button in the lower right and select to print the report to the screen or file, or both.
5. Save the report option.
6. Click PRINT at the top to print the report. Review the report. As you can see below the batch number is listed first and then the workflow originator is listed and the completion date followed by step 1 of my workflow for the Final Approver, which was myself again. This report is nice to see all the steps in the workflow and who submitted/approved each.
This report does suffice for many auditors, so I hope you will find it useful.
NOTE: The process starts with the GL20000 or GL30000 table depending on if the JE is in open or history and links the Batch ID from the journal entry to the SY30500 table, which is a newer table added to track batch history. The SY30500 table is used to link back to the WF10002 table for the batch. The WFI10002 then links to the WF30100 table to get the workflow history/approver records.
NOTE: If you do not get any results, then check to see if the batch record is missing from the SY30500 table due to any issues during posting. You can open a support case if you need assistance
===========================
OPTION 2: SQL VIEW/SMARTLIST DESIGNER
If you want to use a SQL view and incorporate it into a Smartlist Designer report, you can do that too. There are free examples of SQL views for Workflow that you can check out as well.
1. Click on the blog link below and near the end of the article, click to download the 'GP SQL VIEW LIVING DOC' to check out more SQL views available for all modules and for workflow:
Microsoft Dynamics GP SQL Views for Smartlist Designer to Enhance reporting
2. In the Word Document downloaded from the article above, scroll down to the last section for WORKFLOW, and you will find these SQL views:
• WFListFinalApprovers.sql - This view lists all workflow documents that have been final approved and will also display the originator and what time the approval occurred.
• WFPendingApprovalList.sql - This view lists all vendors that have been submitted for approval (and can be modified to use with any workflow type).
• JEwfFinalApproverOPEN.sql - This view is for GL workflow and lists the Journal entry number and batch ID and the approver, date and time for JE's in the current open year.
NOTE: The SQL views available above are free examples to use. If you need these views modified further, please reach out to your Microsoft Partner for a customization request.
NOTE: You can use this KB article (KB# 4014658) to create a Smartlist Designer report using the SQL view above.
How to create a Smartlist Designer report using a SQL view
---------------------------------------------
ADDITIONAL RESOURCES:
• WORKFLOW TABLES:
Workflow tables, what each one does and what information you can get from them for reporting purposes
I hope you will find the options above helpful and saves you a lot of time the next time your Auditors ask for workflow approval information.
