SQL Scripts for Microsoft Dynamics GP: List of PM Invoices for Vendors with POs

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

This script returns a list of payables management invoices for vendors who also have purchase orders.

/*
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
	['Payables Transactions'].VENDORID AS 'Control Type'
	,['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['Payables Transactions'].VCHRNMBR AS 'Voucher Number'
	,['Payables Transactions'].DOCTYPE AS 'Document Type'
	,['Payables Transactions'].TRXDSCRN AS 'Description'
	,['Payables Transactions'].DOCDATE AS 'Document Date'
	,['Payables Transactions'].DOCNUMBR AS 'Document Number'
	,['Payables Transactions'].DOCAMNT AS 'Document Amount'
	,['Purchase Orders'].POCOUNT AS 'Count of POs'
FROM
	(
		SELECT
			CNTRLTYP
			,DOCTYPE
			,VCHRNMBR
			,TRXDSCRN
			,VENDORID
			,DOCDATE
			,DOCNUMBR
			,DOCAMNT
			,PORDNMBR
			,BCHSOURC
		FROM
			PM20000 AS PM WITH (NOLOCK)
		WHERE
			DOCTYPE = 1
		AND
			BCHSOURC = 'PM_Trxent'
		UNION ALL
			SELECT
				CNTRLTYP
				,DOCTYPE
				,VCHRNMBR
				,TRXDSCRN
				,VENDORID
				,DOCDATE
				,DOCNUMBR
				,DOCAMNT
				,PORDNMBR
				,BCHSOURC
			FROM
				PM30200 AS PM WITH (NOLOCK)
			WHERE
				DOCTYPE = 1
			AND
				BCHSOURC = 'PM_Trxent'

	) AS ['Payables Transactions']
INNER JOIN
	(
		SELECT
			VENDORID
			,SUM(POCOUNT) AS POCOUNT
		FROM
			(
				SELECT
					VENDORID
					,COUNT(PONUMBER) AS POCOUNT
				FROM
					POP10100 WITH (NOLOCK)
				GROUP BY
					VENDORID
				UNION
					SELECT
						VENDORID
						,COUNT(PONUMBER) AS POCOUNT
					FROM
						POP30100 WITH (NOLOCK)
					GROUP BY
						VENDORID
			) AS ['Purchase Orders']
		GROUP BY
			VENDORID
	) AS ['Purchase Orders']
		ON
			['Purchase Orders'].VENDORID = ['Payables Transactions'].VENDORID
WHERE
	['Purchase Orders'].POCOUNT > 0
GO