D365 BC: Mix of input and output VAT in one credit note

Once a year, I get a tricky invoice for the PV generation from my power supplier. They reimburse me for the delivery of the photovoltaic electricity into the power grid, and one invoice contains:

reverse charge (output VAT, 0%)
supplier services (input VAT, 20%)

If the energy is supplied to the grid by an entrepreneur to an entrepreneur, it is subject to VAT under the reverse charge mechanism. The reverse charge VAT is paid by the customer in this case, and the grid operator / utility company is the customer for the PV feed-in. However, usually they supply energy to our company i.e. they are registered as a vendor in the ERP system. The document is issued by the utility in my name under a self-billing arrangement (“Gutschriftsverfahren“). Consider the following:

the PV feed-in is a side revenue of my company;
one solution would be to register the vendor as a customer and perform the netting (see Consolidating customer and vendor balances in Business Central);
however, the invoice number is issued by the vendor. If the vendor is registered as a customer, the credit note can only be entered in the General journal as a generic GL transaction;
most of the revenue is VAT exempt under the reverse charge regime, but the supplier also included his services on top. These services (well hidden on the printed invoice) not only reduce the revenue, but there is also a VAT on top;
as a result, the credit note contains both the 0% output VAT (0% sales reverse charge) and the regular 20% input on services.

This was a tough case. The solution is to keep the vendor a vendor, posting revenue via a purchase document and mixing VAT directions in it.

Registering a purchase invoice did not work, D365 Business Central kept showing the “Amount must be negative in Gen. Journal Line Journal Template Name=”,Journal Batch Name=”,Line No.=’0′.“ error. Unlike Dynamics 365 for Finance, in BC the Invoice and the Credit memo are 2 distinct document types, and the invoice may not be negative. Only the Credit memo can.

Enter a Purchase Credit Memo. In one line, enter the amount received for the PV feed-in in connection with a revenue account and select a customer (!) VAT Bus. Posting Group (the group must be made visible through a personalisation). This forces Business Central to treat the VAT as output VAT despite using a purchase document.

The service shall be entered with a negative quantity against a revenue reduction account, while the VAT group remains the regular one for the input VAT:

This credit memo produces 2 VAT entries, one for the [exempt] output VAT and one for the input VAT:

The reverse charge must be reported as such on the monthly VAT statement. Unlike D365 FO, the Business Central does not strictly follow the Sales direction of the VAT when making the VAT return. In the VAT statements matrix, add the Purchase VAT into the reverse charge section to record and report the PV turnover:

This has been a good example of how Business Central’s strict document types can be worked around by combining posting logic from both purchasing and sales VAT setups. Hope this saves someone else a few hours.

Blog series on Finance and VAT in the EU

Proposal for a shorter EndToEndId in pain.001, or a 3-letter hash for RecId

As mentioned in the recent blog Matching own payments in Modern bank reconciliation, the <EndToEndId> element generated by the Electronic Reporting configuration for ISO20022 Credit Transfers is built from the AP payment journal line voucher + RecId of the journal line + a 2-digit counter if multiple attempts are made to generate the same pain.001 file:

				
					<EndToEndId>APP-0036644-5637312576-02</EndToEndId>

In my opinion, the RecId (the “5637312576” part in the example) is unnecessary. Consider the arguments:

The voucher number is unique in every journal line, unless the Voucher series parameter at the Journal name is set to Manual or One voucher number only, or the Number allocation at posting is activated. All three setups are truly exotic in Dynamics 365 for Finance implementations for a number of reasons:
The One voucher number only option contradicts the recommended GL master setting [Dis]allow multiple transactions within one voucher. An attempt to post is going to produce 2 error messages for every journal line – “There can only be one vendor or customer transaction per voucher.” and “There can only be one bank account transaction per voucher.“
The Number allocation at posting defies the purpose: in the unposted journal, the system assigns temporary voucher numbers, which are then replaced at posting. The SEPA pain.001 file is made before posting, and its EndToEndId is going to hold the temporary voucher number: <EndToEndId>Temp001-5637312576-02</EndToEndId>. But such a line cannot be reconciled with the Modern bank reconciliation (see Matching own payments in Modern bank reconciliation), since the bank transaction will receive a different, the final voucher number on posting.
The RecId has a low entropy: it is usually monotonously rising in a payment journal, since the lines are created all at once by the Payment proposal. The number of lines is typically below 1000, as a longer journal takes increasingly longer to post. Consequently, just the last 3-4 digits of the RecId are significant.
In certain geographies, the EndToEndId may not be that long (India: 16 characters only). Truncating from the right will remove the important attempt counter, and the bank upload will fail on a second attempt. Truncating from the left will corrupt the voucher number: now the bank statement reconciliation will fail.

I suggest replacing CONCATENATE(@.PaymentIdentifications.EndToEndIdentification, "_", LEFT("00", 2-LEN(@.'$paymentEnumerator')), @.'$paymentEnumerator') in model.Payments.$PreprocessedEndToEndId …

…with CONCATENATE(REPLACE(@.PaymentIdentifications.EndToEndIdentification,”-\d+$”,””,true),”_”, LEFT(“00”, 2-LEN(@.’$paymentEnumerator’)), @.’$paymentEnumerator’)

This eliminates the last group of digits together with the trailing “-” from the EndToEndIdentification element, and the result is going to be like this:

				
					<CdtTrfTxInf>
        <PmtId>
          <InstrId>APP-0036644-2026</InstrId>
          <EndToEndId>APP-0036644-02</EndToEndId>
        </PmtId>

Shorten the RecId with a 3-letter hash “ABC”

If for some reason the RecId as a unique element must be preserved (I would not know, why, but let’s assume), then it may be reduced from a long 64-integer to a pseudo-unique 3 letter combination, derived from the last 4 digits of the RecId. These 4 digits are shuffled and dispersed, then the resulting integer value is converted into a base-26 number, encoded by capital latin letters:

				
					str input = REPLACE(model.Payments.PaymentIdentifications.EndToEndIdentification, "^.*-", "", true)
int last4 = INTVALUE(RIGHT(@.input, 4))
int mixed = @.last4*73 + 41                             ' prime numbers *salt*
int mod17576 = @.mixed-INTVALUE(@.mixed/17576)*17576    ' 17576 = 26³

int c1v  = INTVALUE(@.mod17576/676)  ' 676 = 26²
int rem1 = @.mod17576 - @.c1v*676
int c2v  = INTVALUE(@.rem1/26)       ' same as div(26)
int c3v  = @.rem1 - @.c2v*26

str output = MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c1v+1, 1)&
             MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c2v+1, 1)&
             MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", @.c3v+1, 1)

The input is the EndToEndIdentification stripped of the preceding voucher number up to the last “-” i.e. the RecId at the end. The result is a short deterministic string at the place of the longer RecId number:

<EndToEndId>APP-0036644UUT29</EndToEndId>

<EndToEndId>APP-0036645UXO01</EndToEndId>

Electronic reporting blog series

Luxembourg VAT Declaration for D365FO in PDF

There is the 2nd most prosperous country on this planet, which is apparently too small to get a proper VAT declaration in Dynamics 365 for Finance. Not too small for ER-Consult, though. We have released a complete Luxembourg VAT Declaration (TVA) built entirely with Electronic Reporting (ER) and delivered as an AED-compliant PDF form.

All relevant boxes of the declaration will be populated, including the sales regime, the key turnover fields, reverse-charge sections for both EU sales and EU purchases, the deductible VAT areas, adjustments, and all totals required for the final VAT balance, as well as the identification header. We support 17% (standard), 14% (intermediate), 8% (reduced), 3% (super-reduced) as the VAT rates, i.e. the status quo from the 1st of January 2024 onward. The title is automatically generated, based on the selected from-to date criteria.

In detail, the following sections and cells are calculated:

Section I.A: Grand total of sales A.1.b
Section I.B: Exempt sales, including “1” intra-community deliveries (457), “2” export of goods into 3rd countries (014), “3” other sales exempt everywhere (015, international transport), “4” exempt domestic sales (016, financial and insurance, postal services), “9.a” sales of goods acquired in triangular trade (018), “9.b.1” delivery of services and intangible goods into an EU country (423), “9.b.2” delivery of services and intangible goods into an EU country where they are exempt (424), and “9.c” rendering services for a non-EU customer and other operations abroad (019)
Section I.C: Total taxable sales turnover as a difference of I.A-I.B
Section II.A: Breakdown of domestic sales of goods and services (701-040)
Section II.B: Intra-community acquisitions of goods implemented as a Use tax in D365 (711-194)
Section II.D.1: Import of goods from outside of the EU for business purposes, either self-declared or recharged by the customs broker as an VAT receivable (721-195)
Section II.E.1: Receipt of reverse-charged services rendered in a foreign EU country and implemented as a Use tax (741-435)
Section II.E.2: Receipt of reverse-charged services rendered in a foreign country outside of the EU (ex: Britain, USA, India) and implemented as a Use tax (751-445)
Section II.F: Receipt of reverse-charged goods domestically in Luxembourg (for example: purchases of scrap; construction materials within the construction industry, 769-764)
Section II.H: Total of the VAT payable, aggregating all of the above
Section III.A: Totals of the VAT receivable, including “1” domestic purchases, “2” intra-community acquisitions from II.B, “3” import VAT from II.D.1, “4” reverse-charged VAT receivable offsetting the II.E.1, II.E.2 and 2.F
Section III.C: Grand total of the VAT receivable
Section IV: Grand totals and the resulting VAT load

A tax code lookup, implemented exactly like in all other supported VAT declarations in Dynamics 365 for Finance, namely under the Application specific parameters of the ER format, is fully configurable and free of hard-coding:

*This screenshot has been taken in the DEMF company of the “Contoso” demo database, hence the German rates.

All this makes the solution transparent, maintainable, and aligned with Microsoft’s Globalisation framework. The ER format is compatible with the Tax calculation add-on, too: in a multi-country, multi-VATID scenario it may be selected as the VAT declaration for the LUX country, alongside Microsoft’s standard VAT declarations for NLD or BEL.

Author Archives: Eugen Glasow

D365 BC: Mix of input and output VAT in one credit note

D365 BC: Mix of input and output VAT in one credit note

Blog series on Finance and VAT in the EU

Proposal for a shorter EndToEndId in pain.001, or a 3-letter hash for RecId

Proposal for a shorter EndToEndId in pain.001, or a 3-letter hash for RecId

Shorten the RecId with a 3-letter hash “ABC”

Electronic reporting blog series

Luxembourg VAT Declaration for D365FO in PDF

Luxembourg VAT Declaration for D365FO in PDF

Blog series on Finance and VAT in the EU