Support for Making Tax Digital (UK)

[nicksellen]

In the UK taxes are moving towards being submitted electronically via an API under the Making Tax Digital plan. Initially this is just for VAT-registered businesses to submit their VAT returns and needs to be in place by 1st April 2019.

They provide a list of existing software that can do this. I didn't look at all of them on the list, but I'm not aware that any of them are open source.

The documentation looks pretty good from what I've seen:

• overview
• developer tutorials
• a somewhat verbose step-by-step walk through (pdf)
• developer API docs

What would be involved in getting this supported by erpnext? Is this something that can be implemented as a seperate app/add-on, or would it need to go into the core?

I am a python developer myself and can likely work on this if we choose to use erpnext (which is dependent on being able to implement this).

[chriscroome]

The 1st April 2019 date might get pushed back, see this article, which includes:

Other findings and recommendations from the committee’s report included:

• So far, no free software products have been offered by the software industry. The smallest businesses will struggle unless HMRC provides a basic free software option.

So this is potentially an opportunity for ERPNext to make some inroads in the UK…

[barredterra]

@nicksellen, the documentation looks very good indeed.

This has to go into core because every UK business will need it.

Here's my idea of a minimal implementation:

• The option to save the Make Tax Digital (MTD) client ID and client secret in the company DocType (or in a specific MTD DocType).

  • Only shows if your business is located in the UK

  • On save, you will have to complete the oAuth flow.

• A submittable DocType "VAT Returns" that enables you to enter all mandatory fields and also stores the response.

  • On submit, the fields will be posted to HMRC using the credentials from above. If posting fails, submit fails as well. If posting succeeds, the answer will be stored in the same record before submission.

This would satisfy the requirement and could later be extended to also get information back from HMRC about obligations, liabilities, and payments.

[joolsr]

Although we are not currently using ERPNext for accounting we would also we interested in seeing big anything can be done here. We have no coding resource though

[umairsy]

Would this feature developed as per Italian taxation requirement would be anywhere close and workable / extendable for UK as well?

https://github.com/frappe/erpnext/issues/16259

[krnkris]

And may it work for Hungary taxation?

https://discuss.erpnext.com/t/how-to-make-xml-invoice-send-it-automatically-hungary-onlineszamla/29458

[cgpurbaugh]

Has any work been done on this? This needs to be in place by April 1, or all UK business are up a creek.

[joolsr]

You can export your VAT accounts and process this manually . There are some free tools around that you can use with making Tax digital. It can also be done in a limited way with just using a spreadsheet to provide the figures 'digitally' - this is what we did

[cgpurbaugh]

@joolsr I was under the impression that everything had to be submitted via the API? From Notice 700/22:

Functional compatible software is a software program, or set of software programs, products or applications, that must be able to: record and preserve digital records (see paragraph 4.3) provide to HMRC information and returns from data held in those digital records by using the API platform receive information from HMRC via the API platform

[barredterra]

@cgpurbaugh , work will probably get done when you get together, create a collective budget and pay developers to do the work. ;)

[cgpurbaugh]

@barredterra I've got no skin in this game. I'm in the USA.

[Wesley114]

Getting onto the UK government's list of Making Tax Digital compatible software would be good advertising for ERPnext in UK and is bound to bring new users. The developers should be looking at this. It baffles me that this issue seems to remain unnoticed.

Compatible software list here: https://www.tax.service.gov.uk/making-tax-digital-software

[barredterra]

@Wesley114 Frappe's team is way too small to consider the peculiarities of every country on earth. The fastest way to get something implemented is to collect funding, provide a specification and pay frappe or a service provider to do it.

For example, I am building a digital tax interface for Germany on behalf of a German company. @chdecultot built one for France, frappe probably built one for India. But somebody's got to pay for it. ;)

[chriscroome]

We (Webarchitects Co-operative) are still interested in this and @nicksellen potentially has some time to work this, but we are not now in as much of a rush as we though we were — it doesn't look like we will need to register for VAT before autumn 2019.

[umairsy]

I am from Frappe Tech. and we wish to collaborate with the community on this project for sponsorship and scoping. We are at the stage of defining the specs for the project, so that we can put the estimates. We look forward to your cooperation.

1. The format of VAT report which should be made available in ERPNext. Could someone share the excel file.
2. Would be it sufficient (atleast in phase 1), to export VAT report from ERPNext and manually import in HMRC portal?
3. For the automated sync between ERPNext and HMRC portal, will there be Agent signup required for ERPNext? Or user customer will sign-up to the govt. portal from their ID.
4. Important links explaining the requirement of integration between ERPNext and HMRC portal.
5. Will there be some data which will be flowing back from HMRC portal to ERPNext? Could someone share use-case and formats?
6. Other important information related to specification we should consider.

[Wesley114]

I am from Frappe Tech. and we wish to collaborate with the community on this project for sponsorship and scoping. We are at the stage of defining the specs for the project, so that we can put the estimates. We look forward to your cooperation.

1. The format of VAT report which should be made available in ERPNext. Could someone share the excel file.
2. Would be it sufficient (atleast in phase 1), to export VAT report from ERPNext and manually import in HMRC portal?
3. For the automated sync between ERPNext and HMRC portal, will there be Agent signup required for ERPNext? Or user customer will sign-up to the govt. portal from their ID.
4. Important links explaining the requirement of integration between ERPNext and HMRC portal.
5. Will there be some data which will be flowing back from HMRC portal to ERPNext? Could someone share use-case and formats?
6. Other important information related to specification we should consider.

In the first post nicksellen seems to have linked to most of the info provided by UK government, but here is something additional (see section 4): https://www.gov.uk/government/publications/vat-notice-70022-making-tax-digital-for-vat/vat-notice-70022-making-tax-digital-for-vat

[racitup]

Hello all, my first post here on ERPNext and I hope many more to follow! I'd be interested in collaborating on this, am in the UK, am a python & django developer and am hoping to start offering ERPNext services in the UK once I'm up to speed. This would be my first jump in at the deep end (read complete newbie ;-) Have spent many years as a professional services consultant (amongst other sins), 2 years with MS NAV (ERP) but was not impressed in the slightest, mainly due to the business model, but the product has some pretty big flaws too. Have had a brief look at the top python OS ERPs and I really like the look and ethics behind ERPNext (& frappe). Odoo puts me off by their money-grabbing license model, much like MS.

@chriscroome @nicksellen @umairsy Has there been any real progress on this? An update would be appreciated. @barredterra Could you supply links to the digital interfaces for France & Germany? It would be useful to see the 'style' you've both gone for. I'm all for learning from other people's architectures and consistency.

My thoughts are that Making Tax Digital (MTD) seems to be the new UK HMRC framework, VAT and Income Tax seem to be the first to go live. So really I see MTD as a UK-specific extensible integration that should draw its data from tax returns provided by ERPNext in the core.

MS NAV handles localisations by having completely separate builds for each country; country-specific features are not mixed. This is one of the flaws; useful functionality in one locale is unavailable in others. Getting the split right is crucial. The MS NAV MTD functionality is only available in the GB build, but it is simply a mechanism to submit figures from the VAT return which is a core component. I think they've got that right. I'm not sure if ERPNext has Tax (VAT) returns? I'm not sure if ERPNext has any facilities to extend core functionality beyond separate apps? @barredterra above suggests this should go into the core and extend core components like the Company doctype, but I'm not sure that would be the right approach. A 'UK Government' app or integration seems appropriate. Then it could be a central place for all UK integration, e.g. Corporation Tax (CT600), Companies House, VAT, Intrastat, Duty...

@joolsr I'll catch you on the UK chapter on Facebook Any input would be great!

[chriscroome]

No news I'm afraid, we don't currently have the resources to configure ERPNext for our needs.

[barredterra]

@racitup both the German and French one are implemented as a Report in the regional module. They collect the necessary data in a SQL query and provide it as a CSV file.

Germany. This one is supposed to go to a private company (DATEV) which has the de facto monopoly on tax software.

France.

A UK tax report could go into the regional module as well.

[jpincas]

Latecomer to the conversation here.

Since MTD is now obligatory in the UK and it appears this wasn't implemented, are there any UK users out there that could point me in the direction of a workaround? I'm evaluating ERPNext for two medium-sized UK businesses.

[Wesley114]

Latecomer to the conversation here.

Since MTD is now obligatory in the UK and it appears this wasn't implemented, are there any UK users out there that could point me in the direction of a workaround? I'm evaluating ERPNext for two medium-sized UK businesses.

Welcome to the chat! We are a small UK business in our 3rd year with ERPnext. Looking into MTD we found that its basically just the figures that you were submitting in the 9 boxes, that are now required to be submitted digitally. Our external accountant is able to pop these 9 numbers into his own software (Xero or Sage I think) and submit them on our behalf, and charges us around £30. Alternatively there is a variety of software packages becoming available, I think they are called bridging software and are listed on the government MTD website, that allow you to type in the figures yourself and submit them digitally.

[garyhyam]

We are an SME also using ERPnext; with regards to submitting our VAT returns we are exporting data from ERPnext into a spreadsheet for validation and then using Xero as a form of bridging software.

I'm keen to see ERPNext become MTD compliant and happy to support anyone willing to make this possible. Essentially ERPNext needs the ability to communicate with HMRC's API (REST), details of which can be found under VAT (Making Tax Digital) at: https://developer.service.hmrc.gov.uk/api-documentation/docs/api

Thanks, Gary

[racitup]

@garyhyam awesome, this is good to hear. I'm currently starting a business offering ERPNext to the UK market and MTD was towards the top of my ToDo list. Watch this space. As for workarounds, you should be able to find free Excel spreadsheets able to submit the MTD figures as bridging software. I'll update this message when I find the ones I've seen before...

[racitup]

Hi all, so I've been working on a design for this but have a few questions on how it should integrate and how it would be used. A rough outline is:

• create an OAuth2 interface to initially get authorisation per-user (the currently logged in/session user) and store the token in the User doctype. This means each user would need separate authorisation via the HMRC government gateway
• create a page (maybe more than one) to display the data obtainable from HMRC (obligations, existing returns, liabilities and payments), and also submit new returns against the obligations. Each Open obligation would have a button for submitting the VAT return if you have authorisation (there are vat read and write permissions)
• I don't really see the need to create many, if any, new doctypes, since once the authorisation is given and token stored, all the data can be requested freely directly from HMRC, probably via js & ajax from the browser (see questions below)

My questions:

1. Should I be looking to use existing OAuth2 endpoints built-in to ERPNext to capture the initial authorisation code or create a new one? Authorisation is received via a redirect to a specified URL which then has to decode the url parameters. Example success URL: https://erpnext.somewhere/redirect-path?code=abcd&state=efgh" The authcode then has to be used in another step to obtain the real auth_token (see 2)
2. Should I be looking to store the auth_token (there are other parameters needed to be stored) in the User Third Party Authentication (aka User Social Login) table? This table may need to be modified unless I can use the User ID depending on the "Data" Type restrictions, or should I create a new one? I was thinking of storing the json object as a string with several parameters instead of splitting it out into lots (probably 7) of fields. The Social Login Key table also looks important (see 1)!
3. The most logical way of getting the values for a VAT return seems to be to store links to various (Chart of) Accounts to pull the 7 required values, probably in a new section in the Company doctype. Does this meet with everyone's expectations? Would you expect the values to be editable afterward? (I'm not an accountant but I would guess you might want to submit an overdue VAT return or split your current values across multiple obligations) I would also expect it to automatically populate a Journal Entry with a new type VAT Return for posting.
4. Would the ERPNext way of handling the data retrieval from HMRC be server or browser-side? As I said, I really don't see the need to store all of HMRC's data again in the ERPNext db, so it seems silly to request it server-side and then render it to the client. There is the obvious issue of transferring the oauth tokens to the browser. Is this a no-no security-wise?

@umairsy Could you comment or suggest someone that could from the dev team? @garyhyam @Wesley114 @jpincas @chriscroome @nicksellen @barredterra @joolsr All comments welcome 😄

Edit: also have another question: I found the documentation on Social Login Keys (below). Why are the keys not common for the whole of ERPNext? As far as I understood OAuth, these keys just authenticate the application before then allowing user authorisation. I guess the answer is there's no way to keep client secrets secret with Open Source, so any project could use them? I may need to extend this feature; HMRC support rotation of 5 client secrets.

https://frappe.io/docs/user/en/guides/deployment/how-to-enable-social-logins

[Paul-STHL]

Following on from a discussion thread I started including a proof of concept for a simple VAT return maker.

I think there are probably a few issues here of which MTD seems to be the simplest of them.

1. We need to create a consistent, easy-to-use, accurate recording of VAT related elements of transactions to enable the VAT return to be constructed. Looking through the code, it seemed to me that India has the most comprehensive version of this but it is probably overkill for UK users. UX is important and I pity Indian businesses having to work through that lot! (GST not the code!) On our side, it seems there are various ways people are tackling this so we'd need to capture what's needed and document the "correct" way to record transactions. In my proof-of-concept, I've captured what works for us at this moment in time. It's pretty simple so it might make a good starting point for discussions.

2. The production of the figures for the VAT return. Should be a relatively easy step given 1.

3. The submission of the VAT return to the government servers is seemingly the easiest bit because it's all documented by HMRC. Just find the API and call it! (Although a per-software secret key might complicate things a little, it's nothing that can't be solved. Even closed source software can't really hope to keep it secret!)

[racitup]

Hi Paul, thanks for your input and great to see this is gaining traction.

I read your thread but am afraid I don't understand why your customisation is needed or what your issue in 1 is about.

The structure of your chart of accounts should let the 7 figures fall out of your accounts naturally, by creating a suitable hierarchy of groups. I may be oversimplifying though, due to the requirement to track EU sales and purchases. This may be trackable with cost centres if not purely by the COA hierarchy.

[Paul-STHL]

Hey cheers for the reply. That's partly what my thread is about - I'm relatively new to this so I'm trying to understand how to do this - so if there's a better way we should document it and get everyone using it. That way an implementation of a VAT return and subsequent submission will work for everybody. At the same time, I'm also a coder so happy to express through examples. :-)

I think if you're just doing domestic UK transactions then you've really just got 4 figures. Output VAT, Input VAT, Output Total, Input total (i.e. Boxes 1, 4, 6 and 7.) I can see how you can simply track them using COA hierarchy. In fact I did that early on during testing.

Where I came unstuck is when EU transactions and transactions from abroad came in - particularly reverse charging - where the value of the transaction is included both as an output and an input (thereby cancelling each other out.) I tried all sorts of COA gymnastics to make that work but in the end decided it would be easier to annotate the transactions with that information and report on that instead.

The customisation deals with some of that, providing the VAT return report and it also provides a convenient place to record your returns at the same time.

[racitup]

@Paul-STHL Hmm, okay, I'll have to try and get my head around these weird EC rules then. The Purchase/Sales Tax Templates can have a Tax Category, Account and Cost Centre applied, but can only be applied to a whole Invoice as far as I can see. The Item Tax Templates can have separate Accounts only, but can be applied per-line. Since we have different rates in the UK depending on the item, this seems more applicable, but a combination might work. Depends if the EC tax rates vary per item. I'll try some worked examples. It's a good exercise as I've created a UK compatible COA (according to CT600), I haven't looked at VAT much.

[Paul-STHL]

Just found this which is very helpful and concise: https://www.crowe.com/uk/croweuk/insights/completing-a-vat-return-guidance-for-businesses-that-trade-worldwide

[racitup]

Thanks, I simplified it a bit more: https://docs.google.com/spreadsheets/d/1wgVkg9sJkeLj5-v0tob5P3kw6Xkh_LVHmBi-HENea2M/edit?usp=sharing

Could they have made it any more awkward? We need to be able to track:

• Transaction: Sale or Purchase
• Item: Goods or Service (Item Group)
• Item: VAT Rate (Tax Templates)
• Other Party: EU or RoW (Shame that Territory is only for Sales, Tax Category?)

I think this should be possible with Tax Templates and Tax Rules to apply them along with a few "for info" Accounts , but probably won't be pretty :-/ Alternatively the Item-wise Sales/Purchase Registers look useful. Also the Accounting Dimension Feature.

[Paul-STHL]

I think that we should be careful about re-using other features like accounting dimensions to track this. The chances are those features are in use by somebody for their original purpose.

We may need to extend the doctypes to reliably track the required info - particularly reverse charge (Which applies in other circumstances too). I wonder how other packages do it? I might get hold of some and see. :-) (I based my setup and COA on the information I could find on Sage.)

I thought about item group too. If we walk up the hierarchy and bump into "Services" we know it's a service which may be useful.

Could they have made it any more awkward?

Oh, they could! Don't tempt them... who knows what Brexit will do do this! :-)

[garyhyam]

Hi Paul,

I've been watching this thread with great interest. We are based in the UK and actively using ERPNext, we also have a requirement to file a UK VAT return, EC Sales and Intrastat. We are also required to file via MTD but are currently doing this using Xero as bridging software... I'm keen as you can imagine to move away from this method

I'd like the opportunity to support this project where I can, either by providing real life examples to aid in the development or by testing. I also have as I mentioned above an active Xero subscription so am happy to share my understanding of how they are achieving this.

What I would say is that I believe you are on the right track. ERPNext in my view would need adjustments to the current doctypes to accommodate some of the tracking requirements needed to be able to report on the different types of transactions and then sum these together for reporting in the relevant boxes on the VAT return. Additionally it will need a way of marking these transactions as 'filled'. This would both insure that they are not picked up in the next period but also those transactions which were entered after the point the return was finalised were included on the next submission.

Xero tags each line of a transaction with a VAT rate or code. I have included a screenshot of this list:

These codes would essentially be the same as those created within ERPNext under Sales /Purchase Taxes and Charges Template. Ours mirror what we have in Sage e.g. T0, T1, T4 etc...

I hope this helps but I am more than happy to give further information or answer any specific questions

Regards, Gary

[Paul-STHL]

How's this for a starter plan.

1) We define a some tax categories used based on the other party's (customer/supplier) location (and VAT registration status):

• UK

• EU VAT registered*

• RoW

• Assigning this category should ideally require a VAT registration number to be in the customer's Tax ID field.

2) We define item-level tax templates for UK (and non VAT registered EU) use linked to the UK category :

• Zero rated
• Exempt
• VAT does not apply
• Standard rated
• Lower rate

We link these rates to the VAT control account so for non-zero rates, these will affect the VAT control account directly.

3) We define some item-level tax templates for EU use too.

• EU Goods
• EU Services
• EU Goods (Zero rated)

4) We extend the item-level tax template to enable us to describe:

• If the category is to be included in the VAT return at all. (I.e. VAT does not apply)
• If the category is subject to the reverse charge mechanism.
• If the category refers to goods or services.
• The VAT rate that applies. This is necessary because the only other place to describe the rate is when you link it to a ledger account and in this case we don't want to do that.

5) We extend the sales invoice item to include a new field to describe the line for the EC sales list. (I think @garyhyam would know more :smiley:.)

6) We extend the invoice to include a "included in VAT return" field which we link to the VAT return.

7) We create a VAT return doctype with the code to walk through the above and produce the actual return. I think it would be nice if the report could also show the workings - i.e. which invoices/lines were included where.

8) When we have that right, we can think about MTD.

This may unravel in practice, it's all a thought exercise at the moment so to make it more concrete I will do this on a test instance and give you access to it so we can play and get it right.

[barredterra]

Should I be looking to use existing OAuth2 endpoints built-in to ERPNext to capture the initial authorisation code or create a new one?

@racitup, please see https://github.com/frappe/frappe/issues/9722 . Unfortunately there's no generic oAuth2 client yet.

[racitup]

1. We define a some tax categories used based on the other party's (customer/supplier) location (and VAT registration status):

Check!

2. We define item-level tax templates for UK (and non VAT registered EU) use linked to the UK category :

• Zero rated
• Exempt
• VAT does not apply
• Standard rated
• Lower rate

We link these rates to the VAT control account so for non-zero rates, these will affect the VAT control account directly.

On further inspection neither the Item Tax Template or Sales/Purchase Tax Templates are ideal. The Item Tax Templates require you to have a single VAT account but it makes more sense to have separate sales and purchase VAT accounts to ease traceability. Would a single VAT account be a major issue? Since it looks like the UK VAT return will require a very bespoke report, I guess it doesn't really matter. The Sales/Purchase Templates apply a rate to the whole invoice rather than per-line, which although might be okay 99% of the time, is not a universal solution. Item Tax Templates can't be linked to a Tax Category, unless I'm mistaken? In any case I don't think you'd need/want to. You want the transaction line to link the Tax Category (Party Location), Item Rate and Item Type (Goods/Service).

3. We define some item-level tax templates for EU use too.

• EU Sale of goods
• EU Sale of services
• EU Purchase of goods
• EU Purchase of goods (Zero rated)

I don't see the reason to solve EU problems at the same time. Let's keep this UK-specific.

4. We extend the item-level tax template to enable us to describe:

a) If the category is to be included in the VAT return at all. (I.e. VAT does not apply) b) If the category is subject to the reverse charge mechanism. c) If the category refers to goods or services. d) The VAT rate that applies. This is necessary because the only other place to describe the rate is when you link it to a ledger account and in this case we don't want to do that.

a) Zero rate is sufficient. The name already captures this b) Do you mean the Tax Category? Not sure I follow here c) Same here d) Agreed, tax rate needs to be per-line

5. We extend the sales invoice item to include a new field to describe the line for the EC sales list. (I think @garyhyam would know more 😃.)

Again I think we should stick to VAT for now and think about Intrastat and EC Sales List separately.

6. We extend the invoice to include a "included in VAT return" field which we link to the VAT return.

I don't see the need for this. The point of a VAT return (and accounting periods) is that it covers all transactions in a period. There's no need to separately track whether or not a transaction has been filed. If you didn't post it in the period, that's just inaccurate recording which applies to all transactions. The period dates come directly from the MTD for VAT obligations.

7. We create a VAT return doctype with the code to walk through the above and produce the actual return. I think it would be nice if the report could also show the workings - i.e. which invoices/lines were included where.

I think this is what I'm most concerned about: having a magic report (not doctype) that produces figures from thin air. There's nothing worse than 'magic' in an ERP system, because invariably it doesn't work and it's a pain to figure out why. I wonder if there's some kind of table functionality that will sum rows or columns when given a table of data, and then allows you to drill down to that data if required.

8 - Agreed, we need to agree the best way forward first.

My alternate proposal would be:

1. Modify Tax Categories with tick boxes for UK-EU VAT and UK VAT Reverse Charge. This allows you to create and assign the tax rules to Customers/Suppliers based on location.

2. Modify Item Tax Template rate table with tick boxes for UK-EU Goods and UK VAT Reverse Charge. This allows the rules to be applied per Item. Only if the same box is ticked on both Tax Category and Tax Template (boolean AND) for a transaction line does the special VAT return rule get applied with whatever rate is set.

3. We could also add another select box to the Item Tax Template table for blank (for backward compatibility/both), Sale or Purchase. This would allow the Sales/Purchase VAT to be separated into different Accounts. This may be a more general PR though.

Unfortunately we can't take account of Item Groups because it would mean we'd need to have all purchase/sales items pre-defined with a group, which isn't feasible.

Edit: I suppose there is this option instead of no. 3, but it is duplication of information which isn't ideal:

[Paul-STHL]

Would a single VAT account be a major issue?

I don't think so - I run our company with just one account. But I'm not proposing to use that account at all for VAT return reporting so people are free to setup whatever accounts they like.

Item Tax Templates can't be linked to a Tax Category, unless I'm mistaken?

They are linked through entries in either the item or the item group. (If you use the group then you don't need to do tag each item individually. This works very well.

I don't see the reason to solve EU problems at the same time. Let's keep this UK-specific.

I'm afraid I disagree with that. Many of the EU problems are actually present in domestic VAT anyway and trading with the world is very much part of UK business so we need to cater for it from the outset.

a) Zero rate is sufficient. The name already captures this

The names are for users to define. Using them directly implies hard-coded behaviour which I think we should avoid.

b) Do you mean the Tax Category? Not sure I follow here c) Same here

I mean we extend the Item Level Tax category like this :

Again I think we should stick to VAT for now and think about Intrastat and EC Sales List separately.

Just eliciting requirements. If it's easy to bundle this in then we should just do it. If it turns out to be a tonne of extra work then we can defer.

I don't see the need for this. The point of a VAT return (and accounting periods) is that it covers all transactions in a period. There's no need to separately track whether or not a transaction has been filed

I think it's quite cheap to do this and it provides value for people who operate their systems in different ways. (See above!) Also if someone does mispost something in the wrong period after a VAT return has already been done it will be difficult to find it without this. In fact, I think we need to go further than that - not only stamping the invoice with the link to VAT return but also the rates/parameters used at that time in case the templates are altered after the fact. Might want to skip that for version 1 though ;-)

I think this is what I'm most concerned about: having a magic report (not doctype) that produces figures from thin air. There's nothing worse than 'magic' in an ERP system, because invariably it doesn't work and it's a pain to figure out why. I wonder if there's some kind of table functionality that will sum rows or columns when given a table of data, and then allows you to drill down to that data if required.

I think a dedicated doctype is more suitable because we need to capture the state at the time of the return, we need to capture the result of the submission to HMRC (both MTD and manual), we need to allow adjustments to the figures, we need the "once submitted it's read only" behaviour, and we need an anchor for linking all the invoices to this specific return.

[racitup]

I don't see the need for this. The point of a VAT return (and accounting periods) is that it covers all transactions in a period. There's no need to separately track whether or not a transaction has been filed

I think it's quite cheap to do this and it provides value for people who operate their systems in different ways. (See above!) Also if someone does mispost something in the wrong period after a VAT return has already been done it will be difficult to find it without this. In fact, I think we need to go further than that - not only stamping the invoice with the link to VAT return but also the rates/parameters used at that time in case the templates are altered after the fact. Might want to skip that for version 1 though ;-)

It appears this alludes to ERPNext's dubious feature of rewriting history, that is being eliminated and should be discouraged at all costs. https://discuss.erpnext.com/t/discussion-immutable-ledger/31153

[Paul-STHL]

Ok! I set up a test installation here: https://erpdev.softwaretohardware.com/ . I invite you to check it out and put a scenario or two in there to see if it works for you.

A VAT return generator is included too, e.g: https://erpdev.softwaretohardware.com/desk#Form/UK%20VAT%20Return/VATRETURN00001

It all links together like this:

Tax Categories

Three "Tax Categories" are defined:

These are assigned:

• To customers and suppliers depending on which rules you want to apply for that party.
• To Item Groups, and optionally Items themselves to link in the appropriate "Tax Item Template." More on this below.

Tax Item Templates

We are using "Tax Item Templates" to allow item level distinctions. The following templates have been defined:

The Tax Item Template itself has been customised to allow capturing of data required for the VAT return. Doing it this way, rather than relying on naming or pointers to individual instances, enables people to redefine these groups to suit their operations and also to accommodate changes to the tax system in future.

The rate defined in the "Tax Rates" section affects the ledger. For EU transactions this is set to 0. For UK transactions it's set to the appropriate rate. We don't use this rate for producing the VAT return so people are free to use ledger accounts in whatever crazy ways they like. :smile:

The VAT rate in the "VAT details" section is used for the producing the VAT return. We will need an internal check to ensure the invoice VAT and our report calculation match.

Item Groups

Item groups are the ideal place to use Tax Item Templates as they enable the application of these templates to large numbers of items without needing to visit each item. Where specialisation is required, they can also be applied to individual items.

For example, in our "Services" group we have defined the links as follows:

Note that both EU Service and Goods are defined. The order defines which one is chosen by default. Having both allows the invoice line items to be customised to use either. This is required because if service delivery is a component of goods delivery then for VAT purposes it may need to be counted as goods. Our "Products" group defines them in the opposite order.

I think this works. Critique welcome as always! :smile:

[Paul-STHL]

Code is now working on HMRC 's Test API so, with a production key from HMRC, we will be able to do MTD VAT.

Integration looks like this.

1) First there is a core API setup doctype. This is where you enable the HMRC API and put the client secret and specify the API endpoints. In the top right (not shown here) is a "Test HMRC Connectivity" button to enable you to test the client credentials work before moving on.

2) Then there is the authorisations list where you can request that HMRC grant authority to your ERPNext installation to view and submit your VAT returns. This is a list so that if you have multiple companies, you can have an authorisation for each. The authorisation document features an integrated workflow that enables you to connect to HMRC and authorise your ERPNext to access your VAT account. To use, you add a "New HMRC Authorisation" and then click the "Request above authorisations" button to connect to HMRC.

If successful, you'll be returned to a form that looks like this:

3) Finally there's the VAT return workflow, where you submit your returns. First you must go to your company screen set your company Tax ID in the following format GB123456789. Then you go to create a new UK VAT return which looks like this:

After selecting your company, you can either enter dates manually or press the "Fetch open returns from HMRC" button to query HMRC for a list of currently waiting VAT returns:

Selecting one of those automatically populates the dates and generates a VAT return. At the bottom of the VAT return is a new submission button. You first confirm that the VAT return is correct, and then press the button:

This will then prompt for confirmation, after which your VAT return will be sent to HMRC and the form will reload showing the submission results:

As you can see, HMRC return lots of meta-data. This is stored in the VAT Return record in case you have problems with the submission and need to discuss it with HMRC.

Code is here: https://github.com/software-to-hardware/erpnext-vat-mtd

Happy Easter!

[garyhyam]

Paul this is great work, thank you for your support! You have made a lot of headway in such a short space of time.

I tried to have a look at the test site that you setup but cannot see off these screens, I wanted to walk through some real life examples and see how it was all fitting together. Additionally, once you have pulled the VAT return together is there a method of pulling the detail behind the numbers (a report)? I also can’t see separate GL accounts for the VAT e.g Input, output and VAT control? I would expect if you were marking the transactions as ‘submitted’ you could also generate the clearing journal from input/output into VAT control which would then clear from either a payment or reclaim

Thanks, Gary

[Paul-STHL]

Paul this is great work, thank you for your support! You have made a lot of headway in such a short space of time.

Thanks. :-)

I tried to have a look at the test site that you setup but cannot see off these screens, I wanted to walk through some real life examples and see how it was all fitting together.

That's a "discoverability failure" on my part. I have put links in a new "UK VAT" section under "accounting."

Additionally, once you have pulled the VAT return together is there a method of pulling the detail behind the numbers (a report)?

There is now! At the top of the VAT return is a new button:

Pressing it links to a report run with the same parameters as the return that shows the breakout for each box. (Note, the report uses the same code as the VAT return so there's no chance the report will get out of sync with the return.)

I also can’t see separate GL accounts for the VAT e.g Input, output and VAT control? I would expect if you were marking the transactions as ‘submitted’ you could also generate the clearing journal from input/output into VAT control which would then clear from either a payment or reclaim Thanks, Gary

I have left the GL as open-ended because I think everyone configures this differently for their needs. For example, we have just a single VAT control account that reflects total liability at any time so VAT payments (or refunds, that'll be the day!) go into that as well. Adding a suspense account wouldn't be hard, and actually neither would child accounts like you're suggesting but some people take this child concept to a whole other level so for now, I think people should post their own ledger entry as a separate step. Definitely something for a future update though.

[garyhyam]

Thanks again Paul

I’m working through this and thinking about any complications I may face of integrating it within my own version of ERP. One thing, is the return configurable via a settings page? i.e. what is picked up in what boxes? Or is this hard coded?

Not everyone will have the COA and Tax codes setup in the same way as your example so it would be good to allow for flexibility

Thanks, Gary

From: Paul Banks notifications@github.com Sent: 14 April 2020 10:32 To: frappe/erpnext erpnext@noreply.github.com Cc: Gary Hyam ghyam@sea-fire.co.uk; Mention mention@noreply.github.com Subject: Re: [frappe/erpnext] Support for Making Tax Digital (UK) (#16025)

Paul this is great work, thank you for your support! You have made a lot of headway in such a short space of time.

Thanks. :-)

I tried to have a look at the test site that you setup but cannot see off these screens, I wanted to walk through some real life examples and see how it was all fitting together.

That's a "discoverability failure" on my part. I have put links in a new "UK VAT" section under "accounting."

Additionally, once you have pulled the VAT return together is there a method of pulling the detail behind the numbers (a report)?

There is now! At the top of the VAT return is a new button:

[LinkToReport]https://user-images.githubusercontent.com/50230903/79208453-f6f97280-7e39-11ea-918f-031a00a9c174.png

Pressing it links to a report run with the same parameters as the return that shows the breakout for each box:

[Drulldown]https://user-images.githubusercontent.com/50230903/79208496-0678bb80-7e3a-11ea-9f07-6a2a394dec21.png

I also can’t see separate GL accounts for the VAT e.g Input, output and VAT control? I would expect if you were marking the transactions as ‘submitted’ you could also generate the clearing journal from input/output into VAT control which would then clear from either a payment or reclaim Thanks, Gary

I have left the GL as open-ended because I think everyone configures this differently for their needs. For example, we have just a single VAT control account that reflects total liability at any time so VAT payments (or refunds, that'll be the day!) go into that as well. Adding a suspense account wouldn't be hard, and actually neither would child accounts like you're suggesting but some people take this child concept to a whole other level so for now, I think people should post their own ledgers. Maybe a future thing.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/frappe/erpnext/issues/16025#issuecomment-613332298, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOPRUQORWHQITK6KCP6T4ETRMQUQTANCNFSM4GFGLWPQ.

[Paul-STHL]

Thanks again Paul I’m working through this and thinking about any complications I may face of integrating it within my own version of ERP. One thing, is the return configurable via a settings page? i.e. what is picked up in what boxes? Or is this hard coded? Not everyone will have the COA and Tax codes setup in the same way as your example so it would be good to allow for flexibility Thanks,

The CoA and Tax categories are defined entirely at the discretion of the end user and do not have to match my test setup. The VAT return is configured, by the end user, using an extension to the Item Tax Template. See below for how this is configured :

[Paul-STHL]

Prior to trial on our reference environment, I have turned this into a separate Frappe application. This enables easy maintenance of the code and also makes it easy to add it to a running ERPNext installation. Code and documentation including installation instructions are here:

https://github.com/software-to-hardware/erpnext-vat-mtd

Enjoy! As always, feedback invited.

[Paul-STHL]

Good news: I'm pleased to say that the trial on our ref environment was successful and produced correct VAT returns and even identified a mis-posting on the previous one thanks to the drill down report. Happy days.

Thanks @garyhyam for testing too.

We also identified some shortcomings that I've documented as issues in the new repo:

• https://github.com/software-to-hardware/erpnext-vat-mtd/issues

[garyhyam]

Thanks Paul, very happy to help. I’ve not been able to review things for a couple of days but it looks like you’ve made some further progress. Again, thank you! I wanted to test your install on a local test server which I have started to build but having a nightmare getting it to work… not your install, just the install of ERP!

Do you have a guide or maybe an install script? Also, which OS would you recommend? I was going to use Centos 7?

Thanks, Gary

From: Paul Banks notifications@github.com Reply to: frappe/erpnext reply@reply.github.com Date: Thursday, 16 April 2020 at 22:35 To: frappe/erpnext erpnext@noreply.github.com Cc: Gary Hyam ghyam@sea-fire.co.uk, Mention mention@noreply.github.com Subject: Re: [frappe/erpnext] Support for Making Tax Digital (UK) (#16025)

Good news: I'm pleased to say that the trial on our ref environment was successful and produced correct VAT returns and even identified a mis-posting on the previous one thanks to the drill down report. Happy days.

Thanks @garyhyamhttps://github.com/garyhyam for testing too.

We also identified some shortcomings that I've documented as issues in the new repo:

• https://github.com/software-to-hardware/erpnext-vat-mtd/issues

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/frappe/erpnext/issues/16025#issuecomment-614908989, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOPRUQPL5YU7JJNDIDSIQRLRM523JANCNFSM4GFGLWPQ.

[Paul-STHL]

Thanks Paul, very happy to help. I’ve not been able to review things for a couple of days but it looks like you’ve made some further progress. Again, thank you! I wanted to test your install on a local test server which I have started to build but having a nightmare getting it to work… not your install, just the install of ERP! Do you have a guide or maybe an install script? Also, which OS would you recommend? I was going to use Centos 7?

If you know Centos then I'd pick Centos 8. Here's a guide that should work for CentOS 8 and latest ERPNext.

https://github.com/software-to-hardware/erpnext-vat-mtd/blob/master/doc/erpnext_centos8.md

[nameduser0]

Hi all, I finished the design that will allow Making Tax Digital for VAT to be integrated as a regional module, and fit with 3 other developments that would be suitable to connect with HMRC to maximise UK uptake. Comments and support welcome!

As it happens, boxes 1 to 5 of the VAT return can be produced using standard ERPNext functionality, explained in the Setup section of the first link:

Making Tax Digital for VAT PAYE Real Time Information Making Tax Digital for Income Tax Corporation Tax and Companies House return

[jonyboymentao]

Can someone help I am trying to install the app but I get the bellow error

Traceback (most recent call last): File "/usr/local/bin/bench", line 11, in load_entry_point('bench', 'console_scripts', 'bench')() File "/home/frappe_user/.bench/bench/cli.py", line 41, in cli bench_command() File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 764, in call return self.main(args, kwargs) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 717, in main rv = self.invoke(ctx) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 1137, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 956, in invoke return ctx.invoke(self.callback, ctx.params) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 555, in invoke return callback(args, *kwargs) File "/home/frappe_user/.bench/bench/commands/make.py", line 62, in get_app get_app(git_url, branch=branch, skip_assets=skip_assets, overwrite=overwrite) File "/home/frappe_user/.bench/bench/app.py", line 140, in get_app cwd=os.path.join(bench_path, 'apps')) File "/home/frappe_user/.bench/bench/utils.py", line 270, in exec_cmd subprocess.call(cmd, cwd=cwd, universal_newlines=True) File "/usr/lib/python3.6/subprocess.py", line 287, in call with Popen(popenargs, **kwargs) as p: File "/usr/lib/python3.6/subprocess.py", line 729, in init restore_signals, start_new_session) File "/usr/lib/python3.6/subprocess.py", line 1364, in _execute_child raise child_exception_type(errno_num, err_msg, err_filename) FileNotFoundError: [Errno 2] No such file or directory: './apps': './apps'

[Paul-STHL]

A quick update. After fixing a precision issue that was not uncovered by testing against HMRC's sandbox API, we successfully submitted our first VAT return to HMRC from ERPNext today using the MTD API. Happy days!

[jukkis69]

I have been trying to evaluate suitable ERP for a new business. ERPNext looks really promising, but being UK Business we need the UK VAT reporting and MTD submission. @Paul-STHL seems to have done really great work. Looking at Github there are though not many commits.

1. Has Paul's work now been integrated to ERPNext distribution?
2. Do we know many companies in the UK using ERPNext (and VAT reconciliation)? It looks really great platform but slightly concerned if there are not many users, maybe there is a reason for it?

Thank you.