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
I have recently started working with Microsoft Dynamics 365 Business Central and have decided to blog about it as I learn; I’ve started a In Microsoft Dynamics 365 Business Central, how do I… series which will include everything, but have also decided to break that down into a set of smaller series on specific areas of Dynamics BC.
