Issue Implementing VAT Daybook in Microsoft Dynamics GP When Transactions are Being Included in Previous Periods

Microsoft Dynamics GPWhile I no longer deal with Microsoft Dynamics GP myself, since my move to working with Microsoft Dynamics 365 Business Central, but I do still know a lot of people who work with Dynamics GP.

I received a call from one of them recently to see if I had a way of flagging items for inclusion on a specific VAT Daybook return. The script for setting transactions as already included on a VAT Daybook return was originally rejected, when we discussed requirements it turned out that it was the solution, but a little more work was required.

The issue was that a company was having the VAT Daybook enabled in a company which had never had VAT installed or configured before which meant transactions hadn’t previously been included in the VAT tables.

When the VAT Daybook module was installed transactions started being included. However, the first return which needed to be submitted was Q4, but with the VAT calendar created, there were all four quarters created.

To make sure transactions were only included in the Q$ return, the solution was multi-step:

  1. Use the script to flag all transactions as included on a VAT return.
  2. Run the Q1, Q2 and Q3 VAT returns.
  3. Run the script to remove the flag against transactions saying they had been included.
  4. Run the Q4 VAT return to pick up all of the required transactions.

Once the above was done, all future VAT returns can be processed as normal and they will include the correct transactions.

SQL Scripts for Microsoft Dynamics GP: Script to Set Transactions as Included on VAT Daybook Return

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script can be used when implementing the VAT Daybook; if the implementation is on an existing system then you need to flag already procesed transactions as included on a return.

This can be done through the system by running VAT returns, but if Dynamics GP has been in use for a while then this could mean a large number of transactions.

The below script can be used to select transactions and stamp them as included on a VAT return so they are not picked up and submitted to HMRC again.

/*
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). */
DECLARE @Tax_Return_ID VARCHAR(20) = 'Y2022Q4' DECLARE @YEAR VARCHAR(4) = 2022 DECLARE @START_MONTH VARCHAR(2) = 10 DECLARE @END_MONTH VARCHAR(2) = 12 UPDATE TX30000 --Tax History (TX30000) SET Tax_Return_ID = @Tax_Return_ID ,Included_On_Return = 1 WHERE YEAR(DOCDATE) = @YEAR AND MONTH(docdate) BETWEEN @START_MONTH AND @END_MONTH

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

In Microsoft Dynamics 365 Business Central (Financial), how do I… Understand the Chart of Accounts

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Financial), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

Each ERP system hand;es the chart of accounts in slightly different ways. Coming from an Microsoft Dynamics GP background I am used to the accounts being multi-segment and containing cost centres as well as the general ledger account. So for example, if I had an account for the North region IT department for purchasing of office supplies, I might have an account with a structure of N-300-61400.

Dynamics BC does not work in the same way as this. In Dynamics BC you would have an account of 61400 in the chart of accounts and then the cost centres of Region and Department would be managed as dimensions. The accounts are created through the G/L Account Page.

When you design the chart of accounts in Dynamics BC, you also create headings and totals through the G/L Account Page and can assign categories and subcategories which are used in financial reporting.

When creating the accounts, they need to be numbered in the order in which they should be included in the chart of accounts list, which is important for the totaling of sections for financial reporting.

Below is an example from the top of the Cronus USA, Inc sample company:

Chart of Accounts list

As you can see, 10000 is a heading, 10001 is the beginning of a total section and has 10990 as the end total; the value in the Totaling column shows that rows 10001 to 10990 inclusive are included in the total (which it is technically possible to amend the cells included in an End-Total it should not be done as there is an indenting function which will override any changes).

You can also create specific Total rows where you manually specify what should be added together, which is useful for creating a row like Net Income.

Over the next few posts in this series, I’m going to take a look at the process for creating new accounts as well as some of the other data used by them, such as account categories and subcategories, and will then take a look into dimensions.

In Microsoft Dynamics 365 Business Central, how do I…

In Microsoft Dynamics 365 Business Central (Financial), how do I…

SQL Scripts for Microsoft Dynamics GP: Change Web Service URi

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client to run to change the web service location when they copied the DYNAMICS database from production to either the dev or test servers; to allow for a single script to be used for both systems, the script includes an IF statement.

/*
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). */
-- set parameter DECLARE @Web_Service_Server AS VARCHAR(100) IF @@SERVERNAME = 'DEVSQL-01\GP' -- dev SET @Web_Service_Server = 'devweb-01' IF @@SERVERNAME = 'TESTSQL-01\GP' -- test SET @Web_Service_Server = 'testweb-01' -- use parameter to update the WF00100 table with the new URL UPDATE WF00100 --Workflow Setup (WF00100) SET Web_Service_Server = @Web_Service_Server WHERE SETUPKEY = 0

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

diagrams.net For Flow Chart Creation (Visio Replacement)

Useful WebsitesI’ve been a long time user of Microsoft Visio for creating flow charts and workflow diagrams. Someone recently suggested diagrams.net, an open source alternative to me as a site worth taking a look at.

I’m not really an advanced user of Visio and the site does allow me to create all of the diagrams I want and is flexible enough to allow me to format them as I want.

Diagrams.net home page

According to the about page, diagrams.net/draw.io is an open source technology stack for building diagramming applications, and the world’s most widely used browser-based end-user diagramming software.

Continue reading “diagrams.net For Flow Chart Creation (Visio Replacement)”

SQL Scripts for Microsoft Dynamics GP: Allow Workflow Originator to be an Approver

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for running against a company database when it had been copied to a test company or test system; it sets the WF_AllowOrigApprover flag on Workflow Master (WF100002) to allow originators to be an approver.

/*
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). */
UPDATE WF100002 --Workflow Master (WF100002) SET WF_AllowOrigApprover = 1 WHERE WF_AllowOrigApprover = 0

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

In Microsoft Dynamics 365 Business Central (Financial), how do I… Series Index

Microsoft Dynamics 365 Business CentralI 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.

This is the series index for the Financial related posts I will be writing. The series index, below, will automatically update as each post in the series goes live so make sure you bookmark this post so you can see keep up-to-date with my journey into Business Central.

Continue reading “In Microsoft Dynamics 365 Business Central (Financial), how do I… Series Index”

ClassicPress Plugins Available From azurecurve | Development in 2023: Contact Forms

ClassicPressIn this series of articles, I am going to introduce each of the plugins I have developed for ClassicPress, a hard-fork of WordPress, which was originally created to provide an alternative, yet compatible, CMS without the Gutenberg block editor.

The 10th plugin is Contact Forms.

Contact Forms
A simple contact forms plugin with an options page allowing default settings to be configured; these settings can, in most cases, be overridden when adding a contact form to a page. This plugin supports the use of multiple contact forms on a page.

Contact forms can be placed on pages using the simple-contact-form shortcode which supports the following parameters:

  • id is the only mandatory parameter; a unique id for each contact form on the page must be supplied.
  • send-from-email allows the default send from email address to be overridden.
  • send-from-name allows the default send from email name to be overridden.
  • recipients is a comma separated list of email address which allows the default recipients to be overridden.
  • label-name allows the default label for the name field to be overridden.
  • label-email allows the default label for the email field to be overridden.
  • label-subject allows the default label for the subject field to be overridden.
  • label-message allows the default label for the message field to be overridden.
  • subject allows the default subject for the name field to be overridden. If an empty subject is provided, users can free form type a subject. Multiple subjects to allow picking from a drop down list can be supplied, separated with a |.
  • subject-prefix allows a subject prefix to be added to all subjects when the email is sent and override the default.

Example shortcode usage:
[simple-contact-form id=”contact-us” recipients=”bob@example.com,jane@example.com” subject=”Sales|Support|Accounts” subject-prefix=”Contact us from example.com:”]

This plugin is multisite compatible, with options set on a per site basis.

Continue reading “ClassicPress Plugins Available From azurecurve | Development in 2023: Contact Forms”

ClassicPress Plugins Available From azurecurve | Development in 2023: Conditional Links

ClassicPressIn this series of articles, I am going to introduce each of the plugins I have developed for ClassicPress, a hard-fork of WordPress, which was originally created to provide an alternative, yet compatible, CMS without the Gutenberg block editor.

The 9th plugin is Conditional Links.

Conditional Links
Conditional links allows shortcodes to be created which will automatically link when a page or blog post exists with the same slug or title. If the conditionally linked page or post exists, the text will be wrapped in anchor tags otherwise plain text will be returned.

The following shortcodes are available:

  • cpl for page links.
  • cbl for blog post links.

Shortcodes can be used in the following ways:

  • [cpl title="this link title"]link
  • the link title

This plugin is multisite compatible.

Continue reading “ClassicPress Plugins Available From azurecurve | Development in 2023: Conditional Links”

SQL Scripts for Microsoft Dynamics GP: Simple RMA Audit

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script is another variation of the simple audit I created, but this time it audits for changes to the RMA tables.

/*
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). */
CREATE TABLE ut_AZRCRV_Audit ( RecordType VARCHAR(100) ,RecordID VARCHAR(100) ,UpdateType VARCHAR(10) ,Username varchar(150) ,ChangeDateTime DateTime ,OldData NVARCHAR(MAX) ,NewData NVARCHAR(MAX) ) GO CREATE TRIGGER utr_AZRCRV_SVC05000_AuditUpdate ON SVC05000 AFTER UPDATE AS INSERT INTO ut_AZRCRV_Audit --VALUES SELECT 'SVC05000' ,d.RETDOCID ,'Update' ,SYSTEM_USER ,GETDATE() ,'RATETPID = ' + CAST(RTRIM(d.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(d.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(d.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(d.EXCHDATE) AS VARCHAR(100)) ,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100)) FROM deleted d LEFT JOIN inserted i ON i.RETDOCID = d.RETDOCID GO CREATE TRIGGER utr_AZRCRV_SVC05000_AuditInsert on SVC05000 AFTER INSERT AS INSERT INTO ut_AZRCRV_Audit --VALUES SELECT 'SVC05000' ,i.RETDOCID ,'Insert' ,SYSTEM_USER ,GETDATE() ,'' ,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100)) FROM inserted i GO CREATE TRIGGER utr_AZRCRV_SVC05200_AuditUpdate ON SVC05200 AFTER UPDATE AS INSERT INTO ut_AZRCRV_Audit --VALUES SELECT 'SVC05200' ,CAST(d.RETDOCID AS VARCHAR(15)) + ' | ' + CAST(d.LNSEQNBR AS VARCHAR(20)) ,'Update' ,SYSTEM_USER ,GETDATE() ,'UNITPRCE = ' + CAST(RTRIM(d.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(d.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(d.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(d.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(d.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(d.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(d.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100)) ,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100)) FROM deleted d LEFT JOIN inserted i ON i.RETDOCID = d.RETDOCID AND i.LNSEQNBR = d.LNSEQNBR GO CREATE TRIGGER utr_AZRCRV_SVC05200_AuditInsert on SVC05200 AFTER INSERT AS INSERT INTO ut_AZRCRV_Audit --VALUES SELECT 'SVC05200' ,CAST(RETDOCID AS VARCHAR(15)) + ' | ' + CAST(LNSEQNBR AS VARCHAR(20)) ,'Insert' ,SYSTEM_USER ,GETDATE() ,'' ,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100)) FROM inserted i GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index