Intercompany project invoicing in practice: Setup

Intercompany project invoicing in practice: Setup

The most comprehensive guidance on the Intercompany Project Management in Dynamics 365 for Finance used to be the blog of Brian Welcker, the historical product manager of the feature. The original source must have disappeared from the Microsoft sites, but a copy remains in the community archive:
intercompany-project-invoicing-in-ax-2012-r3-part-1
intercompany-project-invoicing-in-ax-2012-r3-part-2

However inspiring this source used to be, consultants face a challenge setting this up: in which company which configuration needs to be placed? I will try to fill this knowledge gap.

Prerequisite setup

First of all, you need to go to the General ledger > Posting setup > Intercompany accounting form, pick some accounts and journals and establish a connection between the borrowing legal entity (the one leveraging resources of a subsidiary and issuing invoices to the end customer) and the lending entity (the one providing the workforce and absorbing the cost). Use the button Create reciprocal relationship to make the connection bi-directional.

The reason for this setup is the most profane: without it you cannot choose the lending / borrowing entities in any of the following configuration forms.

In the borrowing company, create a supplier (en-us: vendor) representing the lending company. Pick the known name from the global address book to reuse the legal entity addresses, phones etc. in the supplier card. You can also create a supplier right from the GAB.
In the lending entity, create a customer record representing the borrowing company in a similar manner. With the button General / Intercompany, establish a trading relationship between the two by connecting the supplier with the customer or vice versa. No other Intercompany settings are relevant, because the project intercompany recharge works through the free text invoices and pending supplier invoices with procurement categories (and not through the sales orders and purchase orders with items).

Core Lending company configuration

The lending legal entity barely requires any project management and accounting configuration at all! The project groups, projects, the billing project categories, the “line properties” (billable vs. non-billable) remain in the borrowing entity.

Still, you better replicate the relevant project categories in the lending company, because without it the system is going to fail deriving the Item VAT groups (en-us: Sales tax item groups) when making an intercompany invoice to the borrowing entity, and it is a reason weighty enough. Since you need the project categories, you will create the project Category group(s) too.

The project category does not really require any project-specific GL integration. However, a notorious ‘feature’ of the timesheet posting demands a ledger account for the Project cost and the Payroll allocation, even if the project group stipulates Never ledger i.e. do not post the hours into the General ledger. Moreover, a setting per Category group will not work for a reason unknown; it must be an All-All line in Project management and accounting > Setup > Posting > Ledger posting setup:

On the Revenue accounts tab, select the Intercompany revenue account(s). Again, only the All-All seems to work.
Should the same subsidiary provide multiple fellow companies with the services, the revenue accounts may vary (e. g. between domestic IC partners and export partners). In Dynamics, they may be differentiated by the Borrowing legal entity (this column did not exist in Dynamics Ax2012, an improvement indeed):

The most crucial setup is actually the [weekly] timesheet period template (Project management and accounting > Setup > Timesheets > Timesheet period types):

Use the Generate periods button to pre-populate the weeks one year ahead, then navigate to Human resources > Workers > Employees and assign this TSWeek Period type under the Project / Project setup button to all the employees who must record time, then come back, click the obscure Update timesheets periods and Update worker periods. No, these manipulations do not make much sense, but they are absolutely necessary.

Next, open Project management and accounting > Setup > Project management and accounting parameters and make sure that the Timesheet and the Timesheet voucher number sequences are set, but most importantly, flip the Enable intercompany resource… slider and choose the Borrowing legal entity below.

The Timesheet review workflows are [sadly] local to every legal entity: Project management and accounting > Setup > Project management and accounting workflows. The timesheet approvers may (and should) be people from different companies, an access to the lending company and the Project supervisor role provided. The workflow setup may be easily exported from one company and imported into another.

Finally, set the Project management and accounting > Setup > Prices > Transfer price: this is what the headquarters will be charged for the services provided.

Most of the time, there will be a fixed internal price per hour, and a setting to recharge [travel] expenses and – possibly – subcontracted services with no or little uplift (that’s the rationale behind the Contribution ratio = 0 on the above screenshot).
And no, the fixed transfer price can hardly be further differentiated by departments but only the [customer-facing, billing] project categories. This can be a nuisance; still, the different internal cost rates by department/maturity/roles may be accounted for by a [tedious] list of prices by single persons (see the Resource column) or by Role IDs (however, the latter option puts a burden of picking the proper roles in every timesheet line).

Core Borrowing company configuration

The borrowing company holds most of the Project management and accounting machinery, including the projects themselves, the billing settings and the [higher] sales prices for hours and expenses, should the project be billable on a time and material basis:

Check the Project management and accounting parameters: do NOT set the flag Set the cost price as the sales price by default on the General tab: this will erase the hours sales price from the posted IC invoice and the billing of the services to the end customer is going to fail.
Most importantly, the Default category must be selected on the Intercompany tab:

This single procurement category is used as a stub in all incoming pending IC invoice lines; in fact, it must not be integrated with the project categories or posting at all! It doesn’t even need to be integrated with the GL in the Inventory management module either, as the special Intercompany cost account is used at all times:

When looking for the cost account, the pending supplier (en-us: AP) invoice accounting distribution respects the original hour categories chosen in the timesheet, not the expense procurement category used as a placeholder in the invoice line. In conjunction with the Lending legal entity column (fun fact: the Lending legal entity may not be selected from the drop-down list, but only manually typed in) this helps assign different cost account by different ICO parties and/or kind of services delivered.

This concludes the specific configuration of the borrowing LE.

Process

Let’s dive into the business process demonstration: Intercompany project invoicing in practice: Process!

Electronic Reporting (ER) Cookbook 4: References in a model

Electronic Reporting (ER) Cookbook 4: References in a model

The “Item reference” in Dynamics 365 Electronic Reporting models has been an elusive concept for me for quite a long time. A reference creates a kind of a cross-link between the ER model parts, but what for? Now I think I have figured out some important use cases:

  • Re-use structures and record list definitions within the model, especially for UNIONs
  • Map the same model in different ways and export similarly structured files from different sources

References to facilitate LISTJOINs

The LISTJOIN ER function is an analogue to the SQL UNION. It combines heterogenous record lists into one list of a common type. For example, a LISTJOIN(model.PurchaseLines, model.SalesLines) creates a typed single record list with their common fields: Product no, Quantity, Delivery date, Warehouse etc. However, for the LISTJOIN to recognize the common fields, the fields must be aligned, i.e. follow in exactly the same order, bear exactly the same names.

Here is where the references come into play. One can organize a “Dictionary” root entry in the model, define the most elementary composite entries there, then click the Switch item reference button elsewhere and include this small “brick” into a larger record. From the small bricks one may build larger bricks and so on. Such cross references are indicated by the asterisk (*):

In the above example, an Order record includes the CategoryComposite, Coverage order, Item order nested 1:1 records, and uses fields of the Category type and Line status enumerations. The resulting common Order record type is further assigned to the Sales and Purchase record lists, and this makes their structure in the model identical. The LISTJOIN() then produces a list of records with the following fields:

@.Amount
@.’Qty avail’
@.Category.Group
@.Category.’Category ID’

and so on.
By the way, the root node Item dictionary here is a Container. A Container is used simply to put together diverse artefacts, a way to make the model definition more readable.

References to apply different mappings to the same model

Imagine you need to export sales order lines and purchase order lines into 2 different files, similar in structure. You need a model to abstractly describe a generic Order line list, then a Sales line format export definition, a Purch line format definition both preferrably derived from a common Order line format, and 2 mappings: Sales line to model and Purch line to model to populate the Order line list model from 2 different data sources in Dynamics 365.

Try to run the Sales line format file. It is going to start complaining about 2 models present: “More than one model mapping exists. Set one of the configurations as default.” You set the Sales line to model as Default for model mapping = Yes and the export starts working.
No try to run the Purch line format. It is going to export sales order lines in the purchase order line format, because a link between the format and the outbound mapping is through the model node they use:

Format → Model [node] → [default] Model mapping

To fix this, the 2 formats should be mapped to different nodes in the model!

I’ve got a similar issue when I was trying to use the same model both for the export and for the import of data. There was a Counting journal model and 2 formats: Export to Kardex and Import from Kardex. There was a Counting model mapping for the export and a Counting model to destination mapping for the import.

Tried to run the export Export to Kardex and it asked to “Set one of the configurations as default“, which I did.
Then I tried to run the Counting model to destination mapping but it was not prompting any files for the upload, because a link between the inbound mapping to the import format was established through the model and it was pointing to the export format:

Model mapping to destination → Model [node] → Format

The trick is to use different nodes! The import format should write the data from the inbound file into one Record list in the model, while the export format should read data from another Record list in the model. The mappings then bind the same table to the one node and another, respectively. To reuse the data types and the structure, one node references the other (Switch item reference).

Electronic reporting for data migration

Electronic reporting for data migration

Intro

There are still dozens of important tables in Dynamics 365 for Finance and SCM not exposed by any entity. Sometimes simple solutions like my Copy-paste with a keyboard script from Excel don’t work due to the volume or complexity of the data, and sometimes there is no UI at all.

One of the notorious examples is the table EcoResCatalogControl which is associated with product attributes bound to procurement categories, see the Searchable flag in my blog Searchable product attributes. An import of these “Product category attributes” through the entity EcoResProductCategoryAttributeEntity leaves the attributes dysfunctional: the internal table EcoResCategoryAttributeLookup is left out of sync and the new attributes remain invisible in the product master.
Procurement category attributes
The only form where the EcoResCatalogControl is editable and visible is the Procurement category form, but an attempt to update the flag fails with a “Missing reference” error: the system expects a EcoResCatalogControl record to exist already. Dead end.

Electronic reporting: the last resort

Since the table browser is not an option anymore in a sandbox or production environment, the number of tools available to the consultant reduces to just one: Electronic reporting. Despite the name, the Electronic reporting module is able not only to read, but also freely insert, update or delete voluntary tables in D365FO.
In essence, the idea is to make a CSV text file and import it into the EcoResCatalogControl with the help of the Electronic reporting (ER).

This requires a so-called Mapping to destination where the “destination” is one or many tables or entities in D365FO. The concept is nicely outlined in the blog of Mr. Ties Philippi. In total, 4 “components” are required in at least 2 ER configurations (the Mapping to destination may in theory be detached into a separate – third – configuration).

  1. Model
  2. Model mapping “To destination”
  3. [CSV file] Format
  4. Model mapping [from format] ”To model”

ER configuration and component diagram
The execution flows in the order (3) -> (4) -> (1) -> (2).
The 2 configurations I used can be downloaded here.

First, create a model, for the simplification its structure may follow exactly the table.

Next, create a mapping with the Direction = To destination. Add the target table to the right with the button Destination, then bind the model from the left to the table at the right side of the Model mapping designer. Change the status of the configuration to Complete.

Next, create a format configuration based on the model. It should describe the CSV file structure, namely a set of records (Lines) separated by CRLF, with 2 fields in every line, separated by the semicolon “;” (in a German version of the Excel; in an English version the comma “,” is more appropriate). The encoding of the CSV format should better be UTF-8 (Excel is able to produce UTF-8 encoded files).

Do not bind the format to the model, but use the button Map format to model to create a mapping of the same name. Bind the format to the model as shown on the screenshot below. The button Run can be used to test the mapping ad hoc: it takes a CSV file, interprets it according to the format definition, translates to the model internal container and exports this container to an XML file.
ER components and mappings

Once tested, update the format status to Completed. Make note that the import is started from the artefact (2) Model mapping to destination (with the button Run). The system looks for a format based on the model the mapping belongs to, and inside of that format it looks for a “To model” mapping.
Run Mapping to destination
Having found an appropriate format, the system asks for a CSV file and unleashes the import, there is no way back.

Update route

Added on 31.01.2020
Electronic reporting destination mappings can also be used to launch X++ code. If you have ever worked with production routes, you may have noted the Update route button. The class behind is called RouteUpdate and it is executed by the system automatically from the UI on any change in the order of operations. On plain production routes, it updates the Next operation numbers, and multiplies the variable scrap percentages to cache and store accumulated scrap factors. The problem is, this class cannot be run over all routes in the batch mode; in the Route headers entity they forgot to use it. Opening every single route out of thousands imported is obviously not an option.

I took the same CSV file and created a new destination mapping which updates the RouteTable. One column in the CSV file (Attribute) contains the key RouteId, the other (Category) bears the route name. I am updating the name by the key, but in the middle I am injecting the following code
RouteSearch.newRouteId(@.Attribute).routeId
like this
LEFT(RouteSearch.newRouteId(@.Attribute).routeId,0)&@.Category
It instantiates and executes the RouteSearch class which as a by-product calls RouteUpdate in its constructor method, then gives a routeId() value back which I neglect. Thousands of routes are updated blazing fast, and the Next operation and the Accumulated scrap is set everywhere:

The ER configuration can be downloaded here: RouteUpdateMapping.zip.

Open project milestones

Added on 16.04.2020
Finally, I have designed a data model and mappings for the fixed fee project On-account transactions aka Billing milestones. The configuration may take a project TransId from the CSV file, or it may create its own from the standard number sequence in the Project module parameters. Both the related tables ProjOnAccTrans + ProjOnAccTransSale are generated.
Project on-account transactions
The ER configuration can be downloaded here: ProjOnAccTransModel.zip
Fun fact: a DMF entity for this existed in AX2012, but not D365.

Epilogue

The above crude trick works, but it is dangerous. Fixing a broken import might be a challenge, because to delete the data and start all over you need to change the destination Record action to Delete and then hope to find the right record by the primary key from the CSV file.

I would love to use standard entities, and make Microsoft add them to the application. Please vote for my 2 “favourites”: