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