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

I 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