SQL Scripts for Microsoft Dynamics GP: Select All Pending Prepayments

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 will return a list of all pending prepayments in Dynamics GP; the highlighted account at the can be changed or removed as required.

CREATE VIEW [dbo].[uv_AZRCRV_PendingPrepayments] 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 Transaction WORK File'].VCHRNMBR AS 'Voucher Number'
	,CASE ['PM Transaction WORK File'].DOCTYPE
		WHEN 1 THEN 'Invoice'
		WHEN 5 THEN 'Credit Memo'
		ELSE ''
	END AS 'Document Type'
	,['PM Creditor Master'].VENDORID AS 'Creditor ID'
	,['PM Creditor Master'].VENDNAME AS 'Creditor Name'
	,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
	,['PM Transaction WORK File'].BACHNUMB AS 'Batch Number'
	,['PM Transaction WORK File'].TRXDSCRN AS 'Document Description'
	,['PM Transaction WORK File'].DOCNUMBR AS 'Document Number'
	,['PM Transaction WORK File'].PORDNMBR AS 'PO Number'
	,ISNULL(['Deferral Account Index Master'].ACTNUMST,['GL Account Index Master'].ACTNUMST) AS 'Account'
	,['PM Distribution WORK OPEN'].DEBITAMT AS 'Debit'
	,['PM Distribution WORK OPEN'].CRDTAMNT AS 'Credit'
	,CASE WHEN DOCTYPE = 1 THEN
		CASE WHEN ['Deferral Header Work'].PP_Module IS NOT NULL THEN
			SUM(['Deferral Line Work'].TRXAMNT)
		ELSE
			0 --SUM(['PM Distribution WORK OPEN'].DEBITAMT)
		END
	ELSE
		0
	END AS 'Deferred Debit'
	,CASE WHEN DOCTYPE = 1 THEN
		0
	ELSE
		CASE WHEN ['Deferral Header Work'].PP_Module IS NOT NULL THEN
			SUM(['Deferral Line Work'].TRXAMNT)
		ELSE
			0 --SUM(['PM Distribution WORK OPEN'].CRDTAMNT)
		END
	END AS 'Deferred Credit'
	,['PM Distribution WORK OPEN'].DistRef AS 'Distribution Reference'
	,CASE WHEN ['Deferral Header Work'].PP_Module IS NULL THEN
		'Not Deferred'
	ELSE
		'Deferred'
	END AS 'Deferred'
FROM
	PM10000 AS ['PM Transaction WORK File'] -- FOR OPEN CHANGE TO PM20000
INNER JOIN
	PM00200 AS ['PM Creditor Master']
		ON
			['PM Creditor Master'].VENDORID = ['PM Transaction WORK File'].VENDORID
INNER JOIN
	PM10100 AS ['PM Distribution WORK OPEN']
		ON
			['PM Distribution WORK OPEN'].CNTRLTYP = ['PM Transaction WORK File'].CNTRLTYP
		AND
			['PM Distribution WORK OPEN'].VCHRNMBR = ['PM Transaction WORK File'].VCHRNMBR
INNER JOIN
	GL00105 AS ['GL Account Index Master']
		ON
			['GL Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN'].DSTINDX
LEFT JOIN -- Join to RED
	PP000100 AS ['Deferral Header Work'] -- FOR OPEN CHANGE TO PP100100
		ON
			['Deferral Header Work'].CNTRLTYP = ['PM Distribution WORK OPEN'].CNTRLTYP
		AND
			['Deferral Header Work'].PP_Document_Number = ['PM Distribution WORK OPEN'].VCHRNMBR
		AND
			['Deferral Header Work'].PP_Sequencer = ['PM Distribution WORK OPEN'].DSTSQNUM
LEFT JOIN
	GL00105 AS ['Deferral Account Index Master']
		ON
			['Deferral Account Index Master'].ACTINDX = ['Deferral Header Work'].ACTINDX
LEFT JOIN
	PP000101 AS ['Deferral Line Work']
		ON
			['Deferral Line Work'].PP_Module = ['Deferral Header Work'].PP_Module
		AND
			['Deferral Line Work'].PP_Record_Type = ['Deferral Header Work'].PP_Record_Type
		AND
			['Deferral Line Work'].PP_Document_Number = ['Deferral Header Work'].PP_Document_Number
		AND
			['Deferral Line Work'].PP_Sequencer = ['Deferral Header Work'].PP_Sequencer
		AND
			['Deferral Line Work'].PPOFFSEQ = ['Deferral Header Work'].PPOFFSEQ
		AND
			['Deferral Line Work'].CNTRLTYP = ['Deferral Header Work'].CNTRLTYP
		AND
			['Deferral Line Work'].VCHRNMBR = ['Deferral Header Work'].VCHRNMBR
		AND
			['Deferral Line Work'].DSTSQNUM = ['Deferral Header Work'].DSTSQNUM
WHERE
	['PM Transaction WORK File'].BCHSOURC = 'PM_Trxent'
AND
	['PM Distribution WORK OPEN'].DISTTYPE = 6 --Include only Purchases Distribution
GROUP BY
	['GL Account Index Master'].ACTNUMST
	,['Deferral Account Index Master'].ACTNUMST
	,['PM Transaction WORK File'].DOCTYPE
	,['Deferral Header Work'].PP_Module
	,['PM Distribution WORK OPEN'].DistRef
	,['PM Transaction WORK File'].VCHRNMBR
	,['PM Creditor Master'].VENDORID
	,['PM Creditor Master'].VENDNAME
	,['PM Transaction WORK File'].DOCTYPE
	,['PM Transaction WORK File'].BACHNUMB
	,['PM Transaction WORK File'].TRXDSCRN
	,['PM Transaction WORK File'].DOCNUMBR
	,['PM Transaction WORK File'].PORDNMBR
	,['PM Distribution WORK OPEN'].DEBITAMT
	,['PM Distribution WORK OPEN'].CRDTAMNT
HAVING
	['GL Account Index Master'].ACTNUMST = '000-8888-00'
GO