Financial reports in accounts receivable not balancing after legacy data import

[sureshdev]

After importing historical AR data from a legacy system into D365 F&O 10.0.39, our financial reports are showing significant imbalances. The aged accounts receivable report shows a total outstanding balance that’s $2.3M higher than what appears in the customer balance list and trial balance reports.

We used the Data Management framework with standard AR entities for the import. The data import mapping seemed straightforward - we mapped customer accounts, invoice numbers, amounts, and dates from our legacy CSV files. All records show as successfully imported with no error messages in the DMF execution log.

The AR reconciliation process is now flagging these discrepancies daily, and we can’t close our monthly financial reporting until this is resolved. The financial report validation is failing because the subledger doesn’t match the general ledger. Has anyone encountered similar balancing issues after AR data migration? What validation steps should we have included in our import process?

Your imbalance issue stems from three interconnected problems with your data import mapping, AR reconciliation process, and financial report validation. Here’s the comprehensive solution:

Root Cause Analysis: The $2.3M discrepancy occurs because your DMF import created customer transactions without properly establishing the settlement chain and GL voucher linkages. When D365 imports AR data, it requires specific sequencing and relationship mapping that standard CSV imports often miss.

Data Import Mapping Correction:

First, identify all affected transactions:

1. Export current CustTrans records imported from legacy system (filter by creation date)
2. Compare against your original import files to identify settlement mismatches
3. Create a reconciliation spreadsheet mapping: Invoice Number | Original Amount | Imported Amount | Settlement Status | GL Voucher Reference

For the corrective import, you need THREE synchronized data packages:

Package 1 - Transaction Reversals:

• Entity: Customer transactions V2
• Include reversal flag and original voucher reference
• Must process before new imports to clear incorrect balances

Package 2 - Corrected Open Transactions:

• Entity: Customer transactions V2
• Map fields: Customer account, Invoice date, Due date, Currency, Amount
• Critical: Set TransactionType correctly (Invoice, Payment, Credit note)
• Include posting profile that matches your legacy system’s AR account structure

Package 3 - Settlement Applications:

• Entity: Customer payment journal
• Map: Invoice reference, Payment reference, Settlement amount, Settlement date
• MUST import in chronological order to maintain payment history accuracy
• Include offset account and dimension values

AR Reconciliation Process Implementation:

Set up automated reconciliation checks:

1. Create a recurring batch job using “Customer/vendor settlement” form

   • Schedule: Daily at 2 AM
   • Parameters: Include all customer accounts, date range = All
   • Enable automatic posting of settlement differences under $10

2. Configure AR aging snapshot:

   • Navigate to Credit and collections > Setup > Aging period definitions
   • Create custom aging periods matching your legacy system’s buckets
   • Run aging snapshot before and after correction to validate changes

3. Implement three-way reconciliation validation:

   • CustTrans subledger total
   • GL account 130100 (or your AR control account) balance
   • Customer balance list report total
   • All three MUST match within $100 tolerance

Financial Report Validation Process:

Establish systematic validation before month-end close:

1. Pre-close validation checklist:

   • Run “Trial balance” report with subledger detail
   • Execute “Customer balance list” with aging breakdown
   • Generate “Voucher transactions” report filtered to AR posting types
   • Compare all three reports - discrepancies indicate settlement issues

2. Create custom financial report in Management Reporter:

   • Row definition: AR control account with drill-down to CustTrans
   • Column definition: Beginning balance, Debits, Credits, Ending balance
   • Reporting tree: By customer group and posting profile
   • Add variance column comparing subledger to GL

3. Set up data validation rules in DMF:

   • For future imports, create validation rules checking:
   • Sum of imported invoice amounts = Sum of imported payment amounts + Open balance
   • All transactions have valid GL voucher references
   • Settlement dates are >= invoice dates
   • Customer accounts exist in customer master

Correction Execution Steps:

1. Create backup of CustTrans, CustSettlement, and GeneralJournalAccountEntry tables
2. Run correction in test environment first
3. Import Package 1 (reversals) - validate GL impact before proceeding
4. Import Package 2 (corrected transactions) - verify customer balances
5. Import Package 3 (settlements) - confirm aging report accuracy
6. Execute AR to GL reconciliation report - must show zero variance
7. Run financial reports validation - all three reports must balance
8. If test successful, schedule production correction during maintenance window

Post-Correction Validation:

• Run aged AR report - compare totals to pre-migration legacy system reports
• Verify trial balance - AR control account should match customer balance list exactly
• Check customer statements - ensure payment history displays correctly
• Test customer inquiry forms - drill-down to transactions should show proper settlement status

This systematic approach addresses all three focus areas and should resolve your $2.3M variance. The key is maintaining the relationship integrity between customer transactions, settlements, and GL vouchers throughout the correction process. Plan for 2-3 days of validation in test environment before production correction.

---

This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

[raymondbuilder]

This is a common issue with AR imports. Did you import the customer transactions and the corresponding GL voucher entries separately or together? If you imported them separately, the system might not have created the proper links between subledger and GL. Check if your imported AR transactions have proper voucher references.

[shirley_func]

We imported customer transactions first, then tried to let the system generate GL entries automatically through posting. Should we have imported GL vouchers directly? Also, some of our legacy invoices had partial payments - could that be causing the mismatch?

[raymond_expert]

Partial payments are definitely a red flag. When you import AR transactions with payment history, you need to import both the original invoice amounts AND the payment applications in the correct sequence. Otherwise, the system thinks invoices are fully open when they’re actually partially paid. Run a query comparing CustTrans.AmountCur against settled amounts in CustSettlement table to find discrepancies.

[nicole_analyst]

I ran that comparison and found about 450 invoices where the settlement amounts don’t match. These are mostly invoices from 2023 that had multiple partial payments in the legacy system. How do we correct these without manually adjusting each transaction?

[jacob_master]

You’ll need to reverse the incorrect settlements and re-apply them properly. Create a data package that includes both the CustTrans reversals and the correct settlement applications. Use the Customer payment journal entity to reprocess the payment applications. Make sure to do this in a test environment first to validate the balancing before applying to production.

[john_user]

Before you start reversals, run the AR to GL reconciliation report with detailed voucher tracking enabled. This will show you exactly which GL accounts are out of balance and by how much. The $2.3M variance might be concentrated in specific customer groups or posting profiles.