SQL Scripts for Microsoft Dynamics GP: Payment Run Apply Query

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 was written to return documents included in the selected payment batch (entered in the highlighted parameter) along with the apply information.

/*
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).
*/
DECLARE @BACHNUMB VARCHAR(15) = 'COMPUTER CHECKS'
SELECT

	['Payables Document Types'].DOCTYNAM

	,['PM Creditor Master'].VENDNAME

	,['PM Transactions'].*

	,['PM Payment Apply'].*

FROM

	(

		SELECT

			BACHNUMB

			,VCHRNMBR

			,VENDORID

			,DOCDATE AS PYMTDOCDATE

			,CHEKBKID

			,PSTGDATE

		FROM

			PM10300 AS ['PM Payment Work'] --PM Payment WORK (PM10300)
		UNION ALL

			SELECT

				BACHNUMB

				,VCHRNMBR

				,VENDORID

				,DOCDATE AS PYMTDOCDATE

				,CHEKBKID

				,PSTGDATE

			FROM

				PM30200 --PM Paid Transaction History File (PM30200)
			WHERE

				DOCTYPE = 6

		) AS ['PM Transactions']
INNER JOIN

	(

		SELECT

			DOCTYPE

			,PMNTNMBR

			,APFVCHNM

			,DOCDATE

			,CASE WHEN DOCTYPE >= 3 THEN

				DOCAMNT * -1

			ELSE

				DOCAMNT

			END AS DOCAMNT

			,CASE WHEN DOCTYPE >= 3 THEN

				0

			ELSE

				AMNTPAID

			END AS AMNTPAID

			,Net_Paid_Amount

		FROM

			PM10201 AS PM --PM Payment Apply To Work File (PM10201)
	) AS ['PM Payment Apply']
		ON

			['PM Payment Apply'].PMNTNMBR = ['PM Transactions'].VCHRNMBR

INNER JOIN

	PM40102 AS ['Payables Document Types'] --Payables Document Types (PM40102)
		ON

			['Payables Document Types'].DOCTYPE = ['PM Payment Apply'].DOCTYPE

INNER JOIN

	PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
		ON

			['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID

WHERE

	['PM Transactions'].BACHNUMB = @BACHNUMB

ORDER BY

	['PM Payment Apply'].APFVCHNM

Read original post SQL Scripts for Microsoft Dynamics GP: Payment Run Apply Query at azurecurve|Ramblings of an IT Professional