SQL Scripts for Microsoft Dynamics GP: Verify PM Batches Exist

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created when I needed to verify that all work status Payables transactions were attached to a valid batch.

CREATE VIEW [dbo].[uv_AZRCRV_VerifyBatchForPayablesTransactions] AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	PM.BACHNUMB AS 'PM Batch Number'
	,ISNULL(SY.BACHNUMB, 'Missing') AS 'Batch Record'
	,PM.VCHRNMBR AS 'Voucher Number'
	,PM.DOCDATE AS 'Document Date'
FROM
	PM10000 AS PM WITH (NOLOCK) --PM Transaction WORK File (PM10000)
LEFT JOIN
	SY00500 AS SY WITH (NOLOCK) --Posting Definitions Master Dup (SY00500)
		ON
			PM.BACHNUMB = SY.BACHNUMB
		AND
			SY.BCHSOURC = 'PM_TRXENT'
		AND
			SERIES = 4
WHERE
	SY.BACHNUMB IS NULL
GO