Dynamics 365: Migrating fixed price projects and WIP balances

Dynamics 365: Migrating fixed price projects and WIP balances

To import a fixed-price project in Dynamics 365 for Finance, you need up to 14 different entities and more, and 18-20 steps, sometimes manual and time consuming.

Before importing, make sure that the employees have been declared as bookable resources in the company. If not, use the entities “Project scheduling resource” (ResResourceDataEntity) and “Project scheduling resource details” (ResResourceSetupDataEntity). See also AX7 Table Browser and Class Runner | Microsoft Learn for more information on the ResResourceIdentifier table.

Project contract and funding sources

Next, import the project master table in the following order:

  • Project contract (ProjContractEntity)
  • Project funding source (ProjFundingSourceEntity, the link between the customer and the project contract)
  • Project funding rule (ProjFundingRuleEntity, the link between the [multiple] customers and sources, and the project; typically, there is just one record with 100% allocation of the funds of just one customer to the whole project = project contract)
  • Projects (ProjectEntity, parent projects first)

Next, deal with the open (unbilled) on-account transactions (a.k.a. instalments in CRM Project operations, a.k.a. billing milestones). There is no entity to export these, and no entity to import. The easiest is to extract the billing milestones to Excel, then open the form Project management and accounting > Enquiries and reports > Transactions inquiries > On-account, tailor the Excel sheet to match exactly the order of columns in the grid, then paste all milestones and hit Save, see also How to copy data from Excel into D365. The financial dimensions and VAT groups are inherited from the project master.

By the way, the billed milestones i.e. the on-account transactions in the Fully invoiced status are imported with the Beginning balance journal, see below.

Work breakdown structures

Project Work Breakdown Structures (WBS) are often used to slice the project scope into phases and tasks. Dynamics is currently only able to import draft WBS’ through the entities 

  • Project work breakdown structure – Draft (ProjProjectWBSDraftEntity, these are the projects tasks) and 
  • Project work breakdown structure – Draft estimates” (ProjWBSActivityEstimatesEntity, number of hours per task).

To export a WBS, the current WBS must be turned into a draft with the Edit button first; this is a manual work with 3 mouse clicks per project: select a project, open its WBS, click Edit.

Conversely, upon the migration of the draft WBS’es these must be activated manually with 4 clicks: select a project, open the WBS, click Publish, confirm with OK.

Project cost budgets

Importing the Project budget is a somewhat similar job, with even more manual interventions. There is no entity for the project budget, but project forecasts may be used as a primer. First, prepare a set of 3 forecast files, all with the same forecast model ModelID:

  • Project hour forecasts (ProjForecastEmplEntity by category, the employee ID is negligible)
  • Project expense forecasts (ProjForecastCostEntity, by category) 
  • Project item forecasts (ProjForecastSalesEntity by category; the item number may be omitted, populate the item allocation key ItemAllocateID instead).

Import the forecasts, then initiate the budgets project by project, this is a manual time-consuming operation with 7 clicks per project: select a project, click Project budget, choose Import, confirm with OK, then send the resulting budget into an automated Workflow (the project budget workflow must exist), close the form, repeat.

Historical project transactions

Finally, to migrate started, already running fixed-fee projects, you must import all historical project transactions. They are used to either build up the WIP balance of the project, or for the revenue recognition, or both. The historical project transactions are imported through the Beginning balances journals:

  • Project journal lines for on account beginning balances (ProjBeginningBalanceOnAccEntity) for the billed on-account milestones. The grand total of billed and unbilled (Chargeable) milestones gives the total contract value of the fixed-price project.
  • Project journal lines for cost/sale beginning balances (ProjBeginningBalanceCostSaleEntity) of 3 kinds: hour, expense, item [consumption].

The 3rd entity of this class, called “Project journal lines for fee beginning balances” (ProjBeginningBalanceFeeEntity) is rarely used, because the accrued revenue may be caught up differently.

The target project must be in the In progress stage already. Transactions posted through the beginning balances journal do not affect the general ledger but stay in the project sub-ledger, hence the voucher number in the line is not important. The impact on the P&L and/or balance (for project posted into WIP) only becomes visible after a proper GL balance migration.

To export project transactions of all kinds the entity “Project posted transactions“ (ProjPostTransViewEntity) may be used. Please keep in mind it is a SQL UNION VIEW, and it may skip some transactions under unclear circumstances. To export data from a legacy Dynamics system, consider using the usual Project management and accounting > Enquiries and reports > Transactions inquiries instead.

The minimal set of data for the beginning balances is listed below. Sales prices and amounts are irrelevant in fixed-price projects, and may therefore be omitted:

TRANSACTIONTYPE Cost Hour Item Remark
JOURNALNAME ProjBeg ProjBeg ProjBeg
JOURNALID 149-000020 149-000020 149-000020 A new number makes a new journal head
LINENUMBER 1 2 3 increment the line number
CATEGORYID Hotel PrjMan ItemReq
COSTPRICE 123,45 345,67 567,89
ITEMID R0028055 Not really mandatory for phased out products
DESCRIPTION Trv.exp 238 Kick-off 11th Little pwny Not mandatory
PROJECTDATE 06.11.2024 06.11.2024 06.11.2024
PROJECTID ABC000011 ABC000011 ABC000011
RESOURCE 000_542100 Personnel number, not mandatory
QUANTITY 1 8 1 For items, only the cost price may be imported; when multiplied with the Quantity, a rounding error may occur. Set the quantity to "1" for items and use the Cost amount for the Price
COSTSTATUS Operations Operations Operations "Balance" for projects with WIP, "Operations" for projects with an immediate P&L recognition

Once the beginning balances have been imported and posted, the recognised revenue may be brought up to date. The revenue recognition must be performed in the “Update with beginning balance amounts“ mode: the posting of such a revenue calculation record does not update the general ledger. If manually amended percentages of completion are used, let the revenue recognition run in this mode, then set the Completion method to Manual, enter the voluntary Percentage complete = XX%, hit Calculate, then Post.

Sales and purchase orders

At last, use the entity “Project item requirements” (ProjSalesItemRequirementEntity) to import open (not delivered) Item requirements (also known as a “project BOM”). Be aware that you cannot control the Sales order number with this entity, D365 simply draws one Sales ID per project.

The entities

  • Purchase order headers V2 (PurchPurchaseOrderHeaderV2Entity) and
  • Purchase order lines V2 (PurchPurchaseOrderLineV2Entity) 

are suitable for the project PO import. Keep in mind that open project POs for tangible goods (stocked products) are getting imported as standalone project purchase orders, “self-destructing” upon receipt of the goods; D365 prepares a neutralizing negative inventory transaction. However, if the purchase order was derived from an item requirement, this is going to leave an uncovered demand for items, since a standalone project PO remains “stock neutral”, so to say. When importing SO–backed purchase orders, remove the Project ID from the PO header, then apply a marking to the SO item requirements line (manually). This will allow for PO line consumption through the item requirements.

Good luck!

Project Management and Accounting blog series

Further reading:

Advance payment invoices in Fixed fee projects in D365, D-A-CH style
What Item requirements can’t do
Revenue recognition with project budget shadow forecasts
Intercompany project invoicing in practice: Process
Intercompany project invoicing in practice: Setup

Troubleshoot the VAT Declaration in D365 for Finance

Troubleshoot the VAT Declaration in D365 for Finance

Users of the VAT declarations (both Excel and XML) in the financial department are often shocked by error messages of this kind:

  • Evaluating binding of format component Excel/Sheet1/I_Turnover/Box_200
  • Fehler beim Bewerten des Ausdrucks für Pfad ‚Boxes/Box81_Base‘

The exact error message differs by country: the first is produced by the “VAT Declaration Excel (CH)” / “VAT Declaration XML (CH)” for Switzerland, the second one is from Germany. In reality, the error has nothing to do with the “Boxes” i.e. the Excel layout but a clear sign that the Application specific parameters / Setup fail to classify a specific VAT use case. Namely, a VAT code was used in a module / situation not envisioned by the FiCo functional consultant who configured the VAT Declaration.

Follow the below algorithm to troubleshoot the VAT Declaration and add additional cases into Application specific parameters:

1. Use division by dichotomy to find the exact date

… when it happened. Most of the European VAT declarations are returned monthly, and it takes on average 7 attempts to narrow down the date. Locate the VAT declaration in the Organisation administration > Workspaces > Electronic reporting, Reporting configurations tree under the Tax declaration model node. Keep starting the report with the Run button above, variate the dates. For example:

01.05.2024 – 15.05.2024: no error

16.05.2024 – 23.05.2024: error

16.05.2024 – 19.05.2024: no error

20.05.2024 – 21.05.2024: error

20.05.2024: no error => it happened on the 21.05.2024

2. Find uncommon combinations of VAT code and Direction

Having spotted the date, filter tax transactions by that date and the VAT settlement period using the query Tax > Inquiries and reports > VAT inquiries > Posted VAT (hereafter I refer to the en-gb language of the D365 UI). Export the results into Excel. Build a pivot table by the VAT code and direction, counting the vouchers (= business cases) at the cross sections. Start looking for outliers, pay particular attention to the low counts i.e. seldom use cases. Often the mistake is obvious:

VAT codeDuty-free purchaseDuty-free saleUse taxVAT payableVAT receivable
euSt171  
exSt 7   
FoodR   15 
R   25
ServSt   11 
St   39 

In this example, the euSt code designates VAT exempt intra-community sales, or intra-community acquisitions at the standard tax rate of 19% (= “use tax” or Import VAT / purchase VAT in en-gb, see Minimalistic EU VAT Configuration in Dynamics 365). This code is not supposed to appear in the context of a VAT free purchase, but someone managed to make such a transaction in a general ledger journal when entering a EU supplier invoice.

You cannot fix the tax transactions, but the Application specific parameters may be amended to account for this irregularity. Open this list of Conditions and filter it by the tax code in question to validate the suspicion:

You have to add a new line with this combination of the Tax code and the Transaction classifier to capture this exceptional case. Namely, a pair euSt–PurchaseExempt must be added. Try the VAT declaration one more time to confirm that the problem is now gone.

3. Use a fallback condition

Sometimes it is not that easy to spot a pattern: the occurrences may be too many even on a single day. Your next approach may be to leverage the placeholders *Not blank* for the Tax code and *Not blank* for the Transaction classifier. These lines must be placed at the very bottom of the list of conditions (note the Move down button).

Use these fallback lines with placeholders to redirect the total tax amount of the questionable nature into a separate VAT declaration cell which is typically empty, for example to the cell 61-InputTaxCorrection on the German “UVA” tax declaration. This will give you an idea how much is missing in tax, and what the tax origin amount may be.

Do not keep the placeholders in your PROD setup for long: by doing so, you are resigning and testifying that you don’t really know what is happening in the tax ledger but play dice with the tax authority, which is a very bad idea.

You may also have some tax transactions that do not need to be reported to the tax authority at all. Capture such a case with a combination of a VAT code and direction, and send it into the Other cell i.e. into oblivion:

4. If nothing helps

…then you may have encountered a true bug in the VAT declaration, a case that does not fit into any of the Transaction classifier categories but an Error one!

Indeed, recently we discovered that the reversal of a customer transaction settlement with a realised cash discount is accompanied by a positive adjustment of the VAT payable (i.e. we again own more VAT to the state, which is correct). But this adjustment is not attributed to the VAT direction of Sales, it results in an Error in the VAT declaration instead.

Add lines with the explicit Transaction classifier = Error to manage this case:

Blog series on Finance and VAT in the EU

Further reading:

Troubleshoot the VAT Declaration in D365 for Finance
D365 Petty cash review
Advance payment invoices in Fixed fee projects in D365, D-A-CH style
Sometimes you pay Reverse Charge in D365
Minimalistic EU VAT Configuration in Dynamics 365
Austrian VAT declaration / Umsatzsteuervoranmeldung 2020
EU Tax directives

Assistance and Secondary operations in D365 for SCM to separate machining costs from labour

Assistance and Secondary operations in D365 for SCM to separate machining costs from labour

In Dynamics 365 Supply Chain Management (SCM), in its recent internal MES incarnation called “Production Floor Execution” (PFE), there is a notion of Assistance.

The Assistance model helps solve a controlling challenge: recording machine costs and labour costs separately. For example, setting a machine up consumes less energy than a full speed machine operation: during the setup job, the labour cost prevails if we neglect the amortization. On a contrary, once a highly automated work cell is programmed and set up, the worker may leave and let the cell run on its own. The machine cost prevails. Consequently, instead of a blended rate of the machine hour, we may decide to split the hours into 2 different route categories: one for the machine, one for the worker, and even one more for the energy consumption.

Assistance is when multiple workers share one job. A lead worker (pilot) is assigned the production order job, and other workers join as assistants. The Pilot’s time is replicated into the Assistant’s time records, thus increasing the labour cost per item: N workers : 1 order.
In contrary, if workers handle several production orders at once it is called bundling. This mode shares labour costs across orders, lowering the cost per item: M orders : 1 worker. In a general case, the relation between the orders and workers may be M:N, if a team of assistants takes on a bundle of production orders.

Primary vs. Secondary operations

In Dynamics 365, production steps are called operations. Some operations are “primary”, meaning they control the main task, usually involving a machine or workstation. Others are “secondary”, like additional labour or support roles, running alongside the primary task. Primary and secondary operations share the same operation number but have different Operation IDs in Dynamics 365. They both run at the same time, and the primary operation controls how long the job lasts.

The key is, secondary operations allow for distinct cost recording for personnel assisting a machine operation. These can help track labour costs separately and in real-time.
 

Setting up secondary operations for labour cost tracking

  1. Define distinct Production control > Setup > Routes > Cost categories for the machining and for the labour.
  2. In Production control > Setup > Routes > Route groups, create one group and activate the Setup and/or Process jobs appropriately. Check Job management and Capacity to make the jobs visible on the PFE terminal. Turn on the Setup time and/or Run time estimation and costing.
  3. Define secondary operations in the Route: specify the main machine task as the primary operation and the labour support task as the secondary.
  4. In Production control > Setup > Manufacturing execution > Configure production floor execution, choose Design tabs and make sure the button (Action) “Assistant” is placed onto one of the toolbars.
  5. Check if the system jobs Start assistance and Stop assistance exist under Time and attendance > Manage indirect activities > Indirect activities. Use Time and attendance > Setup > Wizards > Time and attendance configuration wizard if they don’t.
  6. Go to Production control > Setup > Manufacturing execution > Production order Defaults and check Assistants use secondary operations on the Operations tab. This setting lets assistants’ time be logged under the secondary operation, ensuring correct labour cost tracking.
  7. Make sure a dummy worker representing the machine exists alongside the real machine operator and they are activated for Time registration in the Time and attendance > Setup > Time registration workers.
  8. With the setup in place, Release a test production order.
  9. Open the Production control > Manufacturing execution > Production floor execution terminal, perform the initial configuration if needed. Choose the machine ID as a filter for the production jobs. 
  10. Clock in the machine first with its fictive Badge ID. The PFE terminal opens the list of the primary jobs planned at the machine as shown on the screenshot above.
  11. Let the machine “Start job“.
  12. In a “shared terminal” scenario such as ours, the PFE terminal usually immediately logs the user=machine off, otherwise click Leaving / Log off.
  13. Log in as a human worker (machine operator) with your own Badge ID.
  14. Use the Assistant button on the PFE terminal to “attach” yourself as an assistant to the Pilot=machine.
  15. You will see a “You are now registered as an assistant” message right after. The machine becomes the “pilot”.
  16. Check Time and attendance > Inquiries and reports > Teams: there is now a Job pilot driving the primary operation and an assistant Worker attached to it.

The Resource pilot in this form is a remnant of a nice feature Assist Resource. The Assist Resource was supposed to be a machine or a similar asset acting as the Pilot instead of a dummy machine worker, making it simpler to track time. However, the corresponding button "Assist resource" is defunct on the modern PFE terminal: the necessary twin action "Start resource jobs" from the legacy Job card terminal is not implemented in the Production floor execution, so the Assist resource does not do anything useful.

  1. Check the time records of the day in the Time and attendance > Review and approve > Approve list: a Start assistance line has been recorded for the worker. The T&A module is quietly listening to everything what the pilot does:Start assistance
  2. After a while, Clock out the human worker.
  3. Check the time records in Time and attendance > Review and approve > Approve: the T&A module has now copied all the records of Pilot=machine to the Assistant during the active assistance period, yet the Process Job ID is not the same, it is the one of the secondary operation with its distinct cost rate! This does not work properly if you register route operations instead of jobs. Specifically, a Job level = Route is a no-go in Production order defaults.
  4. Check Time and attendance > Inquiries and reports > Teams again: the team is disengaged. If the worker mostly serves the same machine, you may activate Permanent teams in the Production control > Setup >  Manufacturing execution > Manufacturing execution parameters. This will auto-attach the human worker to the machine on subsequent clock-ins.
  5. At the end of the shift, assistants must clock out first: the Pilot can’t clock out until all assistants have done so. Indeed, you may now Clock out the machine.

Separate energy cost

In the above scenario, the human machine operator may come and leave at will, his or her working time is independent from the machine schedule. Recording a separate energy contribution is a slightly different, simple scenario.
Here we may use secondary operations, too. If we can attribute a certain volume of natural gas or electric energy to one hour of the machine’s operation or to a kilogram of the product / semi-finished product, then this overhead may be posted synchronously with every machine time/quantity route transaction.
 
Create a special Route group for the secondary operation. Deactivate Job management, because the electricity meter is not going to actively post its working hours. Turn on the Run time (=constant consumption in kWh per machine hour) or Quantity (=constant consumption in kWh per machined piece) in the Automatic route consumption group. Do it in sync with the Estimation and costing sliders.
 
Assign this Route group to the secondary operation on the route. This will be the NRJ on one of the screenshots above.

Production and Manufacturing blog series

Further reading:

Integrate APS with Dynamics 365 for SCM
D365 Mass de-reservation utility
Picking list journal: Inventory dimension Location must be specified
Consumable “Kanban” parts in D365 Warehouse management
Subcontracting with Warehouse management Part 2
Subcontracting with Warehouse management Part 1
Semi-finished goods in an advanced warehouse
The case of a missing flushing principle