SQL Scripts for Microsoft Dynamics GP: View to Return List of Payments and Linked Invoices

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 creates a SQL view which returns a list of payments and the invoices linked to them.

/*
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).
*/
CREATE VIEW uv_AZRCRV_InvoicesLinkedToPayments AS

SELECT
	['Payment Batch'].BACHNUMB AS 'Batch ID'
	,['Payment Batch'].DOCDATE AS 'Payment Date'
	,['Payment Batch'].DOCNUMBR As 'Cheque Number'
	,['PM Transactions'].VCHRNMBR
	,['PM Transactions'].VENDORID
	,['PM Creditor Master'].VENDNAME
	,CASE WHEN ['PM Transactions'].DOCTYPE = 1 THEN
		'Invoice'
	WHEN ['PM Transactions'].DOCTYPE = 5 THEN
		'Credit Note'
	WHEN ['PM Transactions'].DOCTYPE = 6 THEN
		'Payment'
	END AS DOCTYPE
	,['PM Transactions'].DOCDATE
	,['PM Transactions'].VALUE
	,['PM Transactions'].MODIFDT
	,['PM Transactions'].PSTGDATE
	,['PM Transactions'].POSTEDDT
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	PM30200 AS ['Payment Batch'] WITH (NOLOCK) --PM Paid Transaction History File (PM30200)
INNER JOIN
	PM10201 AS ['Remittance'] WITH (NOLOCK) --PM Payment Apply To Work File (PM10201)
		ON
			['Payment Batch'].VCHRNMBR = ['Remittance'].PMNTNMBR
INNER JOIN
	(SELECT
		['PM Transaction Open'].VCHRNMBR
		,['PM Transaction Open'].VENDORID
		,['PM Distribution Work Open'].DSTINDX
		,['PM Transaction Open'].DOCTYPE
		,['PM Transaction Open'].DOCDATE
		,CASE WHEN ['PM Distribution Work Open'].CRDTAMNT > 0 THEN
			['PM Distribution Work Open'].CRDTAMNT * -1
		ELSE
			['PM Distribution Work Open'].DEBITAMT
		END AS 'Value'
		,['PM Transaction Open'].MODIFDT
		,['PM Transaction Open'].PSTGDATE
		,['PM Transaction Open'].POSTEDDT
	FROM
		PM20000 AS ['PM Transaction Open'] WITH (NOLOCK) --PM Transaction OPEN File (PM20000)
	INNER JOIN
		PM10100 AS ['PM Distribution Work Open'] WITH (NOLOCK) --PM Distribution WORK OPEN (PM10100)
			ON
				['PM Distribution Work Open'].VCHRNMBR = ['PM Transaction Open'].VCHRNMBR
			AND
				['PM Distribution Work Open'].CNTRLTYP = ['PM Transaction Open'].CNTRLTYP
	UNION ALL
		SELECT
			['PM Transaction History'].VCHRNMBR
			,['PM Transaction History'].VENDORID
			,['PM Distribution Hist'].DSTINDX
			,['PM Transaction History'].DOCTYPE
			,['PM Transaction History'].DOCDATE
			,CASE WHEN ['PM Distribution Hist'].CRDTAMNT > 0 THEN
				['PM Distribution Hist'].CRDTAMNT * -1
			ELSE
				['PM Distribution Hist'].DEBITAMT
			END AS 'Value'
			,['PM Transaction History'].MODIFDT
			,['PM Transaction History'].PSTGDATE
			,['PM Transaction History'].POSTEDDT
		FROM
			PM30200 AS ['PM Transaction History'] WITH (NOLOCK)
		INNER JOIN
			PM30600 AS ['PM Distribution Hist'] WITH (NOLOCK) --PM Distribution History File (PM30600)
				ON
					['PM Distribution Hist'].VCHRNMBR = ['PM Transaction History'].VCHRNMBR
				AND
					['PM Distribution Hist'].DOCTYPE = ['PM Transaction History'].DOCTYPE
	) AS ['PM Transactions']
		ON
			['PM Transactions'].VCHRNMBR = ['Remittance'].VCHRNMBR
		AND
			['PM Transactions'].DOCTYPE = ['Remittance'].DOCTYPE
INNER JOIN
	GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105)
		ON ['Account Index Master'].ACTINDX = ['PM Transactions'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master'] WITH (NOLOCK) --Breakdown Account Master (GL00100)
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
INNER JOIN
	PM00200 AS ['PM Creditor Master'] WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON ['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_InvoicesLinkedToPayments TO DYNGRP
GO