Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches

Microsoft Dynamics GPI recently deal with a support call for a client where they were having problems creating a report which showed the approval status of GL batches. Building the WfBusObjKey was more complicated than I expected as it requires the date and time from the batch; for an unposted journal this can easily be retrieved from the Posting Definitions Master Dup (SY00500) table, but for posted batches I had to do some exploring to find where the data was stored.

The table was the Posting Definitions Master History (SY30500); I’ve obviously never written a query which used this table as the name dones;t look familiar, but once I had the tale, it was a simple task to concatenate the fields together to make the WfBusObjKey and pass it to my function which returns the Workflow Approval Status.

I’ve used the SQL query to create a view so that it could easily be hooked into SmartList Builder, Refreshable Excel or other reports::

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GeneralLegderBatchApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus AS
SELECT
	['Year-to-Date Transaction Open'].OPENYEAR AS 'Open Year'
	,['Year-to-Date Transaction Open'].JRNENTRY AS 'Journal Entry'
	,['Year-to-Date Transaction Open'].SEQNUMBR AS 'Sequence Number'
	,dbo.uf_AZRCRV_GetWorkflowApprovalStatus(
												'General Ledger Batch Approval'
												,CAST(RTRIM(['Posting Definitions Master History'].BACHNUMB) AS VARCHAR(15))
													+ '~' + RTRIM(['Posting Definitions Master History'].BCHSOURC) 
													+ '~' + FORMAT(['Posting Definitions Master History'].CREATDDT, 'yyyy/M/d') 
													+ '~' + FORMAT(['Posting Definitions Master History'].TIME1 ,'HH:mm:ss')
											) AS 'Wordflow Approval Status'
FROM
       GL20000 AS ['Year-to-Date Transaction Open'] WITH (NOLOCK)
INNER JOIN
	SY30500 AS ['Posting Definitions Master History'] WITH (NOLOCK)
		ON
			['Posting Definitions Master History'].TRXSORCE = ['Year-to-Date Transaction Open'].TRXSORCE
GO
GRANT SELECT ON uv_AZRCRV_GeneralLegderBatchApprovalStatus TO DYNGRP
GO

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches

  1. Pam Robertson says:

    Ian,

    Since you’re using ‘General Ledger Batch Approval’ for the workflow type name, does this only work for journal entries that get approved in the GL workflow? Have you done something similar for journal entries that were approved as part of a payables workflow?

    Pam

    1. Ian Grieve says:

      Hi Pam,

      If you post a transaction in a subledger, the workflow status would be for the original transaction in the subledger rather than for the journal (unless you have post through switched off in which case you’d be approving the journal again on the GL).

      The uf_AZRCRV_GetWorkflowApprovalStatus function called within the view can be used to return the status of any workflow approval type as long as you pass in the correct workflow type name and WfBusObjKey.

      Ian

Leave a Reply

Your email address will not be published. Required fields are marked *