Day: August 22, 2022

Back in January 2021 I posted an article which included a SQL view to return accruals from the Payables Management module of Microsoft Dynamics GP. What I forgot at the time was that view included joins into the Revenue/Expense Deferral module which is not part of the standard installation.

I’ve been asked a few times since for aversion which didn;t include the RED joins as a system without this module installed with throw errors. I’ve given the updated script out a few times and have finally got around to posting it here; I’ll add a link to the original article pointing to this one.

The view below can be added to a SQL database and then used in SmartList Designer, SmartList Builder or other reporting tools such as refreshable Excel reports.

The highlighted section is the name of the accrual account which should be used; you may want to hard code this to your accrual account, change the description to the name of your accrual account or change the logic of hw the accrual acount is selected.

IF OBJECT_ID (N'uv_ISC_PayablesAccruals', N'V') IS NOT NULL
    DROP VIEW uv_ISC_PayablesAccruals
GO

CREATE VIEW uv_ISC_PayablesAccruals 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
	*
FROM
	(SELECT
		'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
		,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
		,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
		,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()), 'MM/yyyy') AS 'Reference'
		,GL00105.ACTNUMST AS 'Account'
		,CASE WHEN DOCTYPE = 1 THEN
			SUM(PM10100.DEBITAMT)
		ELSE
			0
		END AS 'Debit'
		,CASE WHEN DOCTYPE = 1 THEN
			0
		ELSE
			SUM(PM10100.CRDTAMNT)
		END AS 'Credit'
		,LEFT(RTRIM(CAST(PM10100.VCHRNMBR AS VARCHAR(15))) + ' ' + PM10100.DistRef, 30) AS 'Description'
		,PM10100.VCHRNMBR AS 'Voucher Number'
		,PM10000.BACHNUMB AS 'Batch Number'
		,PM10000.TRXDSCRN AS 'Document Description'
		,PM10000.DOCNUMBR AS 'Document Number'
		,PM10000.PORDNMBR AS 'PO Number'
		,PM10100.DistRef AS 'Distribution Reference'
	FROM
		PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
	INNER JOIN
		PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
			on
				PM10100.CNTRLTYP = PM10000.CNTRLTYP
			AND
				PM10100.VCHRNMBR = PM10000.VCHRNMBR
	INNER JOIN
		GL00105 AS GL00105 --Account Index Master (GL00105)
			on
				GL00105.ACTINDX = PM10100.DSTINDX
	WHERE
		PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
	AND
		PM10000.DOCTYPE = 1 --Include only Invoices
	AND
		PM10100.DISTTYPE = 6 --Include only Purchases Distribution
	GROUP BY
		GL00105.ACTNUMST
		,PM10000.DOCTYPE
		,PM10100.DistRef
		,PM10100.VCHRNMBR
		,PM10000.BACHNUMB
		,PM10000.TRXDSCRN
		,PM10000.DOCNUMBR
		,PM10000.PORDNMBR
	UNION ALL
		SELECT
			'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
			,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
			,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
			,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()),'MM/yyyy') AS 'Reference'
			,GL00105.ACTNUMST AS 'Account'
			,SUM(PM10100.CRDTAMNT) AS 'Debit'
			,SUM(PM10100.DEBITAMT) AS 'Credit'
			,'Accrued Purchases' AS 'Description'
			,'' AS 'Voucher Number'
			,'' AS 'Batch Number'
			,'' AS 'Document Description'
			,'' AS 'Document Number'
			,'' AS 'PO Number'
			,'' AS 'Distribution Reference'
		FROM
			PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
		INNER JOIN
			PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
				on
					PM10100.CNTRLTYP = PM10000.CNTRLTYP
				AND
					PM10100.VCHRNMBR = PM10000.VCHRNMBR
		INNER JOIN
			GL00100 AS GL00100 --Breakdown Account Master (GL00100)
				on
					GL00100.ACTDESCR = 'Accrued Purchases'
		INNER JOIN
			GL00105 AS GL00105 --Account Index Master (GL00105)
				on GL00105.ACTINDX = GL00100.ACTINDX
		WHERE
			PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
		AND
			PM10000.DOCTYPE = 1 --Include only Invoices
		AND
			PM10100.DISTTYPE = 6 --Include only Purchases Distribution
		GROUP BY
			GL00105.ACTNUMST
			,PM10000.DOCTYPE
	) AS Accruals
WHERE
	Accruals.Debit > 0
OR
	Accruals.Credit > 0
GO

GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP
GO