Advance payment invoices in Fixed fee projects in D365, D-A-CH style


Advance payment invoices in Fixed fee projects in D365, D-A-CH style


Engineering and construction projects are mostly offered at a fixed price and almost every time require a down payment from the client. In Dynamics 365 for Finance, this is reflected by a Fixed-price project. Payments are represented by On-account transactions, also called payment Milestones. By the Austrian, German tax law there is a difference between:

  • Down payments or Advance payment invoices (de: Anzahlungsrechnungen): they are charged by the service provider against an obligation to provide a future service. Upon the receipt of the payment to the bank account there is no income in the sense of the corporate tax, yet a VAT obligation (VAT-on-payment or IST-Besteuerung (de)). An invoiced advance payment debits the Accounts receivable and may credit the account “Advance payments invoiced” (de: “Verrechnete Anzahlungen”), while a received prepayment becomes a liability (de: “Erhaltene Anzahlungen”).
  • Partial and final invoices (de: Teilrechnungen, Schlussrechnungen, see Anzahlungs-, Teil- und Schlussrechnungen ( are issued upon the delivery of goods and materials and/or services and subject to VAT “on invoice”. A turnover is generated, and (at least in Germany and Austria) the profit is recognized. The partial and final invoices offset (reverse) the advance payments. In construction, this is even a different type of the VAT: reverse charge vs. full VAT on prepayments.

Our accountants wish to book the advance payment invoice in the Balance sheet (liability) and the partial or final invoice in the Profit & Loss section (turnover). For a Time & material project, this is trivial: the true revenues come from billed hours, expenses, materials, while the advance payments – and advance payments only – are represented by on-account transactions with the type “Prepayment voucher” and “Deduction”.

Yet in the case of a Fixed fee project, there is no difference between down, partial and final payments in standard Dynamics 365: everything is a milestone with a certain amount. To divert the project milestones to different accounts, we’ve seen sub-projects opened only for cause of the advance payment invoicing. We believe, there is a much better, straightforward solution: to mark the Advance payment milestone with a special VAT Group. This is especially practical in the case of huge amounts and long payment terms where we also wish to apply the delayed VAT on payment mode and activate the “French” Conditional VAT.

Setup example

In the DEMF legal entity of the “Contoso” demo database, we create a Fixed fee project group project where the on-account transactions are posted into the Profit and loss:

This applies to the normal -partial and final invoices. To divert the advance payment milestone to a balance account, in Project management and accounting > Setup > Ledger posting setup we configure the Invoice revenue – on-account posting by VAT group and introduce at least one new special debtor VAT group for advance payments: AR-DOMA. This way of aligning the revenue accounts with the VAT is very common in European D365 implementations, you see it in every product posting setup:

A milestone with the group AR-DOMA is posted here against a liability (main account 212150).
Now, if – for a total order volume of €100.000,00 – we agreed upon the 10%-40%-50% payment schedule with the customer, then the on-account transaction should look like this, at a first glance:

Yet with the 2nd milestone cannot directly offset the liability. To debit this account 212150, we have to introduce a negative advance payment milestone, increase the partial invoice milestone by the same amount with the intent to bill them together: 40% – 10% = 30%.

To round this up, we activate the Conditional VAT general ledger parameter and give the AR-DOMA group a special VAT code VAT19A, there the VAT19A relays the tax liability to the regular Payment VAT code = VAT19 upon the settlement of the future project advance invoice with the customer payment:

The Settlement period may be a special one, not reported to the German tax authority, and the account VAT paid may be the same as 212150: we calculate the prepayment VAT, we show it on the invoice to let out customer refund it, but we don’t have to pay it right away .

The “anti-prepayment” milestone should actually contain a yet another VAT group, because we are crediting account 212150, but the VAT code should the regular one, because we are going to reverse a paid advance payment invoice with the VAT already paid.

Create an invoice proposal with the advance payment milestone, then try to post it. A highly unpleasant surprize awaits: “Discrepancy between account 68719604503 and setup of the group for invoicing Profit and loss” (de: Projektvertragskennung: …Unstimmigkeit zwischen Konto und Einstellung der Gruppe für die Rechnungsstellung ‘Gewinn und Verlust’). The enigmatic number here is the RecID of the main account 212150. An overzealous developer implemented in the class ProjPostOnAccProposal.checkAccount() a validation of the main account type (BS vs. P&L) against the project group setting, they never expected a project invoice posted into the Balance sheet today, and into the Profit and loss statement tomorrow.

To inhibit this check, implement a one-liner in X++, an extension
final class ProjPostOnAccProposal_Extension
protected boolean performMainAccountCheck()
next performMainAccountCheck();
return false;


Now the advance invoice can be posted. The posted VAT code is VAT19A. Settle the advance payment invoice with the customer payment, and the Related voucher shows the re-allocation of the VAT liability from VAT19A to VAT19:

Now post the second (partial) invoice with the 2nd and the 3rd milestones together and behold the main account turnovers and balances after: they are right how they should be:

By the way, should we introduce an interim account “Advance payments invoiced”, the transfer between the accounts “Advance payments invoiced” à “Advance payments received” cannot be performed by the automatic routines of the Project management and accounting module. This has to happen manually triggered by the settlement of the advance payment invoice. The accountant may also monitor the movements on the VAT19A code which happen upon the settlement. The “anti-prepayment” milestone should offset the final account “Advance payments received”, then.

D365 Electronic Reporting: JOIN records in pairs

Test for odd number
Test for odd number

D365 Electronic Reporting: JOIN records in pairs

Recently, I came across a really challenging requirement in Electronic Reporting / Configurable Business Documents in Dynamics: in an additional section in the Delivery note, print a flat table where every pair of consecutive records becomes one line:

No Not like this But like this No
1 Left lace Left lace – Right lace 1
2 Right lace Left shoe – Right shoe 2
3 Left shoe
4 Right shoe

This required a set of sophisticated techniques I am thrilled to share. The idea is to split into odd and even records and build something like

SELECT *, ROW_NUMBER as Rn1 FROM Records JOIN *, ROW_NUMBER as Rn2 from Records ON Rn2= Rn1+1

  1. Enumerate: make a so called Calculated field at the ER configuration’s root level with a record list and a row Number inside:
  2. Add another Calculated field at the row level to check if the current record has an odd or an even row number: LEN(REPLACE("13579",RIGHT(NUMBERFORMAT(@.Number,"#"),1),"",false))<5 I’ll leave the formula uncommented, but I wonder if somebody manages to write a shorter function. I can also imagine using TRANSLATE() or the VALUEIN(SPLIT(“1,3,5,7,9″…) functions. The problem is the missing MOD operator in Electronic Reporting: They promised, but failed to deliver. Anyway, now we’ve got this:
  3. Next, let’s add another root Calculated field, filter the list by $Odd = true and make a list with only the odd records:
    WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.'$Odd')
  4. Finally, attach an ‘even’ record to every ‘odd’ one by placing another Calculated field with a lookup inside of the $OddRecord: FIRSTORNULL(WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.Number=@.Number+1)). Here is the result, a JOIN to itself:


Cross-company data sharing vs. Duplication in Dynamics

Cross-company data sharing vs. Duplication in Dynamics

Cross-company data sharing

Cross-company data sharing in Dynamics 365 for Finance and SCM (see System administration > Setup > Configure cross-company data sharing) supposed to be a valid replacement of the virtual companies in Dynamics AX2012 and earlier. I don’t know how about you, but I get the error “Table … is a Main table as specified by the table group and may not be shared unless its Data Sharing Type is Duplicate” every time I wish to share something useful.

It looks for the TableGroup property in the D365 table metadata and practically rejects any table which is not a Group or does not have a simple primary key. What is left for sharing? Some little configuration tables such as the Delivery terms (INCOTERMS), Payment terms ans so on: only these have the TableGroup = Group.  This rules out the most interesting tables in Dynamics such as the InventTable (Product list), CustTable (Customers) or VendTable (Suppliers) (well, this is not entirely true: for the latter there is a dedicated Feature Customer and Vendor master data sharing available for activation). Namely, these have TableGroup = Main.

The TableGroup property of the standard tables is not extendable. The developers left a backdoor: a Main table with the property DataSharingType explicitly set to Single or Duplicate may be eligible too. CustTable is an example. Needless to say, the DataSharingType is not extendable either. There seems to be a yet another backdoor in the form of the SysDataSharingTypeTableConfiguration table where this DataSharingType is supposed to be configured. However, this feature is protected by the flight EnableSysDataSharingTypeTableConfiguration. As with every flight it is very promising and tempting but it remains hard-locked in any production environment.

Dead end? Not quite.

Recurring ‘Copy into legal entity’ data project 

In Data management, create a new data project of the Copy into legal entity kind, select the destination company(ies) and the appropriate entity or entities:

It should not grab unfinished work, though. In this example it is obvious to only consider an approved bill of materials. The BOM lines do not have an approved  flag, but if there is no BOMId, no line can be copied either, i.e. it is sufficient to only filter the BOM headers. In general, you should take care of the data consistency. In the particular case of the bills of materials, they should not contain any unknown items or refer to any local inventory dimension such as the site, warehouse or the warehouse location: ensure the default sites in all companies in question have the same code “STD” or “DEF”, and set the Resource consumption checkbox in all lines to make them warehouse-agnostic. The Approver is not a problem, since the employee list is technically shared across all legal entities.

Click Copy into legal entity in batch and set a Recurrence e.g. every weekday. The data project may be run over and over again in a cycle copying the same data, but it is not a problem as it works in the UPSERT mode in Dynamics. However, as the data grows the data project runs slower. We should restrict the set and take only the changes of the day, for example. If you are lucky, there will be a ModifiedDateTime column you can use in the Filter:

(here with a Join and an advanced moving date query).