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 PA Report Posting Definitions Master (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

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?

Your Name (required) -
Your Email (required) -

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 *