Tested on:
Project Operations solution version 4.9.0.52 (pre-alpha), F&O 10.0.16.
I recently redeployed my F&O T2 and Dataverse environments in hopes of seeing my F&O T2 appear in PPAC. While the whole deployment process has small details that have changed and evolved since ProjOps’ GA, the overall process has remained the same: Deploy F&O, deploy Dataverse, link environments, choose legal entities, apply Dual Write maps, run Dual Write maps. By now I have deployed integrated around 9-10 times since private preview so the process is somewhat in muscle memory. Interestingly enough, I’ve not had to diagnose Dual Write failures or missing data. My overall experience with Dual Write is fairly positive and I’ve always managed to get data across from Dataverse to F&O and vice versa. The only occasions when something has not worked has been around known issues in the DW sync or features not yet implemented in the product by the product group.
Needless to say, this time my experience was different even though the cause was most likely a user error on my behalf. Seems muscle memory can also be a bad thing. So how did I end up diagnosing the staging table and integration journals? Glad you asked. I was providing feedback to the product team on the intercompany invoicing docs article. I configured intercompany invoicing in F&O as the F&O Contoso demo data won’t have intercompany invoicing related demo configurations until around 10.0.19. I also set everything up in Dataverse to support inter-org sales (intercomany invoicing has always been called inter-organizational sales in Dataverse since PSA).
The problem – Where are my integration journals and staging table entries?
When intercompany invoicing is configured in F&O and inter-organizational sales is set up in Dataverse, approved time entries lead to either a new Project Operations integration journal for the legal entities that lend and borrow resources or to entries in an existing integration journal. As I have been following instructions on docs, the lending legal entity in this case is GBPM and the borrowing legal entity is USPM. In my scenario an integration journal was only created for USPM but not for GBPM. Let’s go through a checklist to diagnose missing integration journals and staging table entries.
1. Inter-org sales Actuals in Dataverse
When time entries are approved, inter-org sales Actuals are created. When all Transaction Types produce desired prices in ProjOps, it’s an indication that inter-org sales setup in Dataverse has been done correctly. As the image below shows, there’s nothing wrong with the inter-org setup so this isn’t the root cause for missing integration journals and staging table entries.
2. Check Project Operations integration journals for both legal entities
When Actuals are created, Project Operations integration journals (or entries in existing journals) are created when the Import from staging table workflow in F&O is run. This should lead to an integration journal for both USPM and GBPM. As we can see from the images below, an integration journal has only been created for USPM. Additional diagnosis is needed as GBPM is missing an integration journal.
3. Check the Import from staging table batch job
If integration journals don’t get created the first step is to make sure that an Import from staging table batch job has been created and that is has run without errors. The batch job creates an integration journal or entries in existing integration journals from the staging table. One example of where the batch job may fail is incorrectly set up exchange rates between an accounting currency and transaction currency.
4. Check the staging table
When Actuals are created in Dataverse they flow to a staging table named ProjCDSActualsImport in F&O. This process is near real-time and automatic so when Actuals are created they should be seen in the staging table immediately. As the staging table for GBPM is empty, additional diagnosis is needed. For some reason Actuals don’t flow to F&O.
To view the staging table in F&O, use the following link:
https://yourEnvironmentHere.operations.dynamics.com/?mi=SysTableBrowser&tableName=ProjCDSActualsImport&cmp=LegalEntityHere
5. Check Dual Write maps for errors and table map versions
If the staging table is empty the root cause is most likely a configuration error. The next place to look is Dual Write maps to see if there are any errors. Table map versions should also be verified. In my case I was running an old version of the Project Operations integration actuals (msdyn_actuals) table map. That wasn’t the root cause in my case but it did turn out that a newer version of the table map was available.
6. Check skipsync from Dual-Write and Actuals
The next step is to verify msdyn_skipsync on Actuals and in Dual Write. Look at the query on Project Operations integration actuals (msdyn_actuals) Dual Write map’s Common Data Service.Actual filter. The query should match the one seen in the image below.
Skipsync should be set to No on all Actuals except Cost Actuals as those are not synced to F&O in inter-org sales scenarios.
7. Check legal entities in Dual Write
As Dual Write needs to sync data for both USPM and GBPM, make sure that both legal entities are added to Dual Write. In my case the root cause turned out to be simple: GBPM was not included in my Dual Write configuration! While I always select USPM, GBPM, and THPM when I set up ProjOps integrated with demo data, on this occasion muscle memory must have failed on me. The only legal entity I had in Dual Write was USPM.
Words of caution when adding a new legal entity to an existing Dual Write configuration
Adding a new legal entity to Dual Write is as easy as selected a legal entity and pressing save. This has repercussions though! When a new legal entity is added, Dual Write will produce errors for pre-existing data. The existing Actuals that didn’t originally sync for GBPM will not get synced after adding GBPM in Dual Write.
As existing Actuals for GBPM are not synced after the fact, financial data between Dataverse and F&O is inconsistent. If corrections to Actuals are made while environments are linked, corrections sync to F&O. This is desirable for UPSM’s Actuals but not GBPM’s as GBPM’s Actuals didn’t sync in the first place. This is why a legal entity should not be added in Dual Write before corrections are made in Dataverse.
If for some reason you’re facing in a situation where you’ve forgotten to include a legal entity in Dual Write and inter-org sales Actuals have already been created in Dataverse, the following steps should help you settle your data in Dataverse and F&O:
- Correct all Actuals in Dataverse. This will sync the reversed Actuals to F&O. In this case reversals are for USPM.
- Take appropriate action in F&O so that original transactions and corrections balance each other out.
- Add the new legal entity (in this case GBPM).
- Create new business transactions in Dataverse to sync all appropriate Actuals for the legal entities (USPM & GBPM) to F&O.
The results after adding a new legal entity to Dual Write
When a new legal entity is added to Dual Write all the maps restart. When all ProjOps related maps display as Running, new business transactions can be made in Dataverse. When inter-org Actuals are created, the staging table for GBPM will get entries and an integration journal is created after the Import from staging table batch job has run.
