SQL View for to Extract Accruals from Microsoft Dynamics GP Payables Management Module

Microsoft Dynamics GPI recently did a webinar for my employer, ISC Software, on Prepayments and Acruals 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.

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 a Dynamics GP Consultant (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'[/sqlred] ,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 'Debit' ,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 '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'[/sqlred]) 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.DEBITAMT) END AS 'Debit' ,CASE WHEN PP000100.PP_Module IS NOT NULL THEN SUM(PP000101.TRXAMNT) ELSE SUM(PM10100.CRDTAMNT) 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 >[/sqlgrey] 0 OR Accruals.Credit > 0 GO GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP GO

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *