I recently did a webinar for my employer, ISC Software, on Prepayments and Accruals and as part of the accruals section used a SQL View to extract the accruals to be created; as mentioned in thw webinar, the extract cna be done either using a SmartList or a direct query in SmartConnect.
The SQL view has been created using the EOMONTH function which si available only in later versions of SQL Server; you may need to tweak the script a little to handle getting dates in different ways, if you are running an older version of SQL Server.
This script was written with a specific client in mind for use on a few companies so it has been written to look for a specific account description; you will need to either change the description or join in another way to get the credit line to output unless your accrual account has the exact description of Accrued Purchases. The section which will need changing is highlighted.
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'
,ISNULL(GL00105_DAIM.ACTNUMST,GL00105.ACTNUMST) AS 'Account'
,CASE WHEN DOCTYPE = 1 THEN
CASE WHEN PP000100.PP_Module IS NOT NULL THEN
SUM(PP000101.TRXAMNT)
ELSE
SUM(PM10100.DEBITAMT)
END
ELSE
0
END AS 'Debit'
,CASE WHEN DOCTYPE = 1 THEN
0
ELSE
CASE WHEN PP000100.PP_Module IS NOT NULL THEN
SUM(PP000101.TRXAMNT)
ELSE
SUM(PM10100.CRDTAMNT)
END
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
LEFT JOIN -- Join to RED
PP000100 AS PP000100 --Deferral Header Work (PP000100)
on
PP000100.CNTRLTYP = PM10100.CNTRLTYP
AND
PP000100.PP_Document_Number = PM10100.VCHRNMBR
AND
PP000100.PP_Sequencer = PM10100.DSTSQNUM
LEFT JOIN
GL00105 AS GL00105_DAIM --Account Index Master (GL00105)
on
GL00105_DAIM.ACTINDX = PP000100.ACTINDX
LEFT JOIN
PP000101 AS PP000101 --Deferral Line Work (PP000101)
on
PP000101.PP_Module = PP000100.PP_Module
AND
PP000101.PP_Record_Type = PP000100.PP_Record_Type
AND
PP000101.PP_Document_Number = PP000100.PP_Document_Number
AND
PP000101.PP_Sequencer = PP000100.PP_Sequencer
AND
PP000101.PPOFFSEQ = PP000100.PPOFFSEQ
AND
PP000101.CNTRLTYP = PP000100.CNTRLTYP
AND
PP000101.VCHRNMBR = PP000100.VCHRNMBR
AND
PP000101.DSTSQNUM = PP000100.DSTSQNUM
AND
PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE()))
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
,GL00105_DAIM.ACTNUMST
,PM10000.DOCTYPE
,PP000100.PP_Module
,PM10100.DistRef
,PM10100.VCHRNMBR
,PM10000.BACHNUMB
,PM10000.TRXDSCRN
,PM10000.DOCNUMBR
,PM10000.PORDNMBR
HAVING
SUM(PP000101.TRXAMNT) > 0 --partial deferral
OR
PP000100.PP_Module IS NULL --no deferral
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'
,CASE WHEN PP000100.PP_Module IS NOT NULL THEN
SUM(PP000101.TRXAMNT)
ELSE
SUM(PM10100.CRDTAMNT)
END AS 'Debit'
,CASE WHEN PP000100.PP_Module IS NOT NULL THEN
SUM(PP000101.TRXAMNT)
ELSE
SUM(PM10100.DEBITAMT)
END 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
LEFT JOIN -- Join to RED
PP000100 AS PP000100 --Deferral Header Work (PP000100)
on
PP000100.CNTRLTYP = PM10100.CNTRLTYP
AND
PP000100.PP_Document_Number = PM10100.VCHRNMBR
AND
PP000100.PP_Sequencer = PM10100.DSTSQNUM
LEFT JOIN
GL00105 AS GL00105_DAIM --Account Index Master (GL00105)
on
GL00105_DAIM.ACTINDX = PP000100.ACTINDX
LEFT JOIN
PP000101 AS PP000101 --Deferral Line Work (PP000101)
on
PP000101.PP_Module = PP000100.PP_Module
AND
PP000101.PP_Record_Type = PP000100.PP_Record_Type
AND
PP000101.PP_Document_Number = PP000100.PP_Document_Number
AND
PP000101.PP_Sequencer = PP000100.PP_Sequencer
AND
PP000101.PPOFFSEQ = PP000100.PPOFFSEQ
AND
PP000101.CNTRLTYP = PP000100.CNTRLTYP
AND
PP000101.VCHRNMBR = PP000100.VCHRNMBR
AND
PP000101.DSTSQNUM = PP000100.DSTSQNUM
AND
PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE()))
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
,GL00105_DAIM.ACTNUMST
,PM10000.DOCTYPE
,PP000100.PP_Module
HAVING
SUM(PP000101.TRXAMNT) > 0 --partial deferral
OR
PP000100.PP_Module IS NULL --no deferral
) AS Accruals
WHERE
Accruals.Debit > 0
OR
Accruals.Credit > 0
GO
GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP
GO
Updated 27/05/2022: Added highlight for accrual account join