I’ve handled multiple Odoo 16 cloud migrations with project accounting modules, and the issue you’re describing is almost always related to how the migration tool handles multi-currency transactions and analytic account line conversions. Here’s what’s happening and how to fix it:
The problem occurs when your project accounting data includes transactions in multiple currencies or when analytic lines have been modified after their initial creation (like adjustments, corrections, or reversals). During migration, the standard Odoo migration tool doesn’t always preserve the complete audit trail and modification history of analytic lines, which causes the aggregation calculations to produce different results.
Here’s a comprehensive diagnostic and correction approach:
-- Step 1: Identify specific discrepancy types
-- Check for missing analytic lines
SELECT
'Missing Lines' as issue_type,
COUNT(*) as count,
SUM(amount) as total_amount
FROM account_analytic_line_backup -- Your on-premise backup
WHERE id NOT IN (SELECT id FROM account_analytic_line);
-- Check for amount discrepancies in existing lines
SELECT
'Amount Mismatch' as issue_type,
COUNT(*) as count,
SUM(ABS(new.amount - old.amount)) as total_variance
FROM account_analytic_line new
JOIN account_analytic_line_backup old ON new.id = old.id
WHERE ABS(new.amount - old.amount) > 0.01;
-- Check for incorrect project assignments
SELECT
'Project Reassignment' as issue_type,
COUNT(*) as count
FROM account_analytic_line new
JOIN account_analytic_line_backup old ON new.id = old.id
WHERE new.project_id != old.project_id;
-- Check for currency conversion issues
SELECT
p.name as project_name,
aal.currency_id,
COUNT(*) as line_count,
SUM(aal.amount) as amount,
SUM(aal.amount * cr.rate) as converted_amount
FROM account_analytic_line aal
JOIN project_project p ON aal.project_id = p.id
LEFT JOIN res_currency_rate cr ON cr.currency_id = aal.currency_id
WHERE aal.currency_id IS NOT NULL
GROUP BY p.name, aal.currency_id;
Based on the diagnostic results, you’ll need to apply corrections. The most common issue is that timesheet entries and their corresponding invoice lines got disconnected during migration. Here’s the correction script:
# Run this through Odoo shell in your cloud instance
from odoo import api, SUPERUSER_ID
with api.Environment.manage():
env = api.Environment(cr, SUPERUSER_ID, {})
# Get all projects with discrepancies
projects = env['project.project'].search([('active', '=', True)])
for project in projects:
print(f"Processing project: {project.name}")
# Recalculate all analytic line amounts
analytic_lines = env['account.analytic.line'].search([
('project_id', '=', project.id)
])
for line in analytic_lines:
# Verify currency conversion is correct
if line.currency_id and line.currency_id != line.company_id.currency_id:
# Recalculate with proper exchange rate
rate = line.currency_id._get_conversion_rate(
line.currency_id,
line.company_id.currency_id,
line.company_id,
line.date
)
correct_amount = line.amount * rate
if abs(line.amount_currency - correct_amount) > 0.01:
line.write({'amount_currency': correct_amount})
# Reconnect timesheet entries to SO lines
if line.timesheet_id and not line.so_line:
# Find matching SO line based on project and task
so_line = env['sale.order.line'].search([
('order_id.project_id', '=', project.id),
('product_id', '=', line.product_id.id),
('state', 'in', ['sale', 'done'])
], limit=1)
if so_line:
line.write({'so_line': so_line.id})
# Force recalculation of project totals
project._compute_analytic_line_ids()
env.cr.commit() # Commit after each project
print("Correction complete")
After running the corrections, you need to verify the results:
- Re-run your variance query to confirm discrepancies are resolved
- Generate a reconciliation report comparing pre and post-migration totals
- Verify that revenue recognition schedules match between systems
- Confirm that timesheet billing amounts are correctly linked to sales orders
For the $127,000 discrepancy, you should also check if there are any analytic lines that were created in the on-premise system between the data extraction date and the cutover date - these would need to be manually migrated as a catch-up batch.
Finally, implement a validation script that runs daily to catch any future discrepancies early.