Enumerate lines in Configurable Business Documents

The delivery note, invoice etc. Report Data Providers in Dynamics 365 for Finance do not expose line numbers for no obvious reason. For instance, the SalesInvoiceTmp table sent from D365 to the report renderer – whatever it is – misses the line number. The situation becomes dire when it comes to Electronic Reporting / Configurable Business Documents.

The ENUMERATE(Lines) function applied to the lines makes a totally new object {Number, Value} and every binding “@” becomes “@.Value“. This is a massive change in the report, very hard to maintain and upgrade (rebase).

In XML or CSV outbound formats there is a Counter element, it auto-counts itself. In Excel and Word outbound formats there are just Ranges, Cells. I thought we worked with lists as isolated objects and there were no internal variables in Electronic Reporting which were aware of the execution history.

But today I finally understood what the DATA COLLECTION does. It is a listener, you pass a value to it, it returns it back unchanged but keeps a global variable to count and summarise the values.

For example, to enumerate delivery note (en-us: packing slip) lines, first create a root Data collection class of the integer type in the Mapping to the right, Collect all values = Yes, let’s call it a LineNumberCollection.

Then put the following formula into the Excel Cell with the line number:
LineNumberCollection.Collect(1)+LineNumberCollection.Sum(false)-1

The first LineNumberCollection.Collect() call takes 1, records 1 internally, and returns the 1 back into the cell. The second call LineNumberCollection.Sum(false) adds the running total of 1’es recorded so far. The last operand subtracts 1.

Here is the result: Bingo!

Electronic reporting blog series

Batch jobs in D365: a Russian roulette

Real world production scenarios include automated batch tasks executed in the background with some cadence: Batch processing overview – Finance & Operations. They apply to sets of documents fetched at the run time, for example to sales orders delivered, but not invoiced yet. Obviously, every day the query with the documents should return a different set with the recently despatched sales orders. It is called Late selection: Late selection in D365FO – D365Tour. So far so known.

As the complexity of the business grows, so does the complexity of the batch jobs. You’ll need jobs consisting of multiple tasks, executed one after another. It is called task dependency, or task Constraints. One of the latest unpleasant discoveries was the fact, that you cannot always rely on the sequence of steps set in the batch job: the most useful tasks, such as the SalesFormLetter_Invoice and its likes (used to update sales and purchase orders with order confirmations, delivery notes and invoices) act as orchestrators and spin off child batch tasks for simultaneous, multi-threaded sales order, purchase order, production order processing. These child tasks are not constrained (!) and get executed at a first come, first served basis. They may still run while the master task SalesFormLetter_Invoice seemingly ends and relays the baton to the next task. This leads to interference and racing conditions. You better evaluate the average execution time of the master controller task as a whole, put these controllers into different batch jobs, schedule them at a safe time distance from one another.

Heterogenous batch jobs as the one shown below pose a different challenge.

Here is the how-to instruction:

Spin off the first task from the main menu (here: Production control > Periodic tasks > Production order status update) and choose Run in the background / Batch processing. This will be your starting point, a template for the batch job.
Locate the batch job in the My batch jobs list, set the status to Withheld and start editing.
Every task within a batch job needs a Class name. Now you know the class name of the first task: ProdMultiCostEstimation. To add the next task, you have to manually specify its Class name. The owners of this framework provided a lookup list, but it is slow and completely dysfunctional: it shows just a fraction of the possible executable RunBase or SysOperation classes, sometimes it crashes if there are some bad customizations in the source code. If you don’t know the class name, repeat the step 1 to reveal it.
Add the next task, type the class name in. The first time it will take the system up to 20-30 seconds to accept and retrieve the Class description.
Most batch tasks need a query. Click Parameters to populate it. Here you experience another nasty trait of the batch framework: the query shown is the last one you used in the system elsewhere. It is not necessarily the one really persisting at the batch task. Another user is not going to see your query criteria at all: only the owner/creator has the control. *1) *2) *3)
On the Batch task details tab, Constraints, choose the predecessor task to build a chain. You many need to change the Expected status to Ended or error if the batch job has to continue with the next task on an error somewhere in the chain.
Check the recurrence, remove the Alerts if needed, and put the Batch job back to Waiting.

1) Lessons learned: take a screenshot of every query and parameter, document it thoroughly to pass the maintenance to the Dynamics system administrator.

2) In certain cases a manually added task does not let edit the execution parameters and/or the query. Sometimes(e.g. on sales order updates) it helps to execute the same periodic task in the normal user session once. This is the reason why it is often hard to pre-configure the batch jobs in a “Golden Config” environment: it may need real transactions to execute against. Sometimes it wishes to be initialised from the main menu and nowhere else because badly programmed. In such a case, start your step 1 – the key task – from this critical periodic task. Should there be 2 such bitchy tasks in one batch job chain, it is over. They must be separated into different batch jobs.

3) As a really bad surprize came the fact that even the owner has no control over the query: if you use the Late selection, in a chain of nearly identical tasks as the ones below, the QUERY OF THE LAST TASK IS APPLIED TO EVERY TASK of the kind, at least in the case of the production order status updates.

You should let the bloody job run one first time, then put the batch job back on hold and adjust the queries in the tasks once more, then save and re-activate the batch job.

Dynamics 365 tips and tricks

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

Introduction

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 (wolf-partner.at)) 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[ExtensionOf(classStr(ProjPostOnAccProposal))] 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.

Monthly Archives: September 2022

Enumerate lines in Configurable Business Documents

Enumerate lines in Configurable Business Documents

Electronic reporting blog series

Batch jobs in D365: a Russian roulette

Batch jobs in D365: a Russian roulette

Dynamics 365 tips and tricks

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

Introduction

Setup example

Project Management and Accounting blog series