​

Reconciliation Logic - Technical Documentation

​

Overview

​

Core Principles

​

1. Immutability of Closed Periods

• GL entries from closed accounting periods MUST NEVER be modified
• Their reconciliation group IDs remain unchanged
• They can participate in reconciliation but are read-only

​

2. Reuse IN_PROGRESS Groups

• If entries already belong to an IN_PROGRESS reconciliation group, complete that group
• Don’t create duplicate groups for the same set of entries
• This happens when closing periods creates pending groups

​

3. Protect Already-Exported Data

• reconciled_at (DateTime): ALWAYS update to NOW() when completing (reflects current completion time)
• reconciled_on (String, Belegfeld 1): NEVER change if already set (DATEV export integrity)
• reference_document_* fields: NEVER change if already set (DATEV export integrity)
• Once exported, Belegfeld 1 and reference document data are immutable

​

4. Smart Group Strategy

• 1 existing IN_PROGRESS group → Complete it
• Multiple IN_PROGRESS groups → Fail reconciliation (inconsistent state, requires investigation)
• No existing groups → Create new group (standard reconciliation)

​

Process Flow

​

STEP 1: Analyze Entries

// Separate entries by period status
entriesFromClosedPeriods = filter(is_closed === true)
entriesFromOpenPeriods = filter(is_closed !== true)

// Find ALL existing IN_PROGRESS groups on BOTH sides
for each glEntry:
  check credit_reconciliation_group.status === "IN_PROGRESS"
  check debit_reconciliation_group.status === "IN_PROGRESS"
  collect unique group IDs

• Check BOTH credit and debit sides (don’t rely on entry.isCredit)
• Track which entries have which groups
• Log warnings for closed period entries

​

STEP 2: Determine Strategy

if (existingGroupIds.size === 1):
  // Complete the existing IN_PROGRESS group
  finalReconciliationId = existingGroupIds[0]
  shouldCreateNewGroup = false
  isCompletingExistingGroup = true

else if (existingGroupIds.size > 1):
  // Multiple groups - inconsistent state, FAIL reconciliation
  return {
    success: false,
    error: "MULTIPLE_IN_PROGRESS_GROUPS"
  }
  
else:
  // No existing groups - standard case
  finalReconciliationId = randomUUID()
  shouldCreateNewGroup = true
  isCompletingExistingGroup = false

​

STEP 3: Create or Update Group

if (shouldCreateNewGroup):
  // Create new reconciliation_group with status=COMPLETED
  INSERT INTO reconciliation_groups (
    status = 'COMPLETED',
    reconciled_at = NOW()
  )

else:
  // Update existing IN_PROGRESS group to COMPLETED
  // ALWAYS update reconciled_at to reflect current completion time
  UPDATE reconciliation_groups
  SET status = 'COMPLETED',
      reconciled_at = NOW()

​

STEP 4: Update Reference Document

// Fetch existing reference data
existing = SELECT reference_document_number, reconciled_on
FROM reconciliation_groups

if (existing.reference_document_number OR existing.reconciled_on):
  // Already set - DO NOT CHANGE (breaks DATEV export integrity)
  LOG "Preserving existing reference document information"
  SKIP this step
else:
  // Compute reference document from GL entries
  // Priority: Sales Invoice > Credit Note > Any document > UUID
  referenceDocumentNumber = extractReferenceDocument(glEntries)

  UPDATE reconciliation_groups
  SET reference_document_id = ...,
      reference_document_number = ...,
      reference_document_type = ...,
      reconciled_on = referenceDocumentNumber  // Belegfeld 1

​

STEP 5: Update GL Entries

for each glEntry:
  // CRITICAL CHECKS
  if (glEntry.accounting_period.is_closed):
    SKIP - Do not modify closed period entries

  if (isCompletingExistingGroup && already has finalReconciliationId):
    SKIP - Entry already has correct group

  // UPDATE LOGIC
  if (entry.isCredit && credit_account.to_be_reconciled):
    if NOT already has group:
      SET credit_reconciliation_group_id = finalReconciliationId

  if (entry.isDebit && debit_account.to_be_reconciled):
    if NOT already has group:
      SET debit_reconciliation_group_id = finalReconciliationId

• Never modify entries from closed periods
• When completing existing group, check if entry already has that group
• Only update entries that need updating
• Log all decisions for debugging

​

STEP 6: Create Activity Logs

​

STEP 7: Connect Documents & Update Statuses

​

Example Scenarios

​

Scenario 1: Reconciling with Closed Period Entry

• Invoice (ID: inv-1) from closed period 2024-Q1
  • credit_reconciliation_group_id: “group-abc” (IN_PROGRESS)
  • accounting_period.is_closed: true
• Payment (ID: pay-1) from open period 2024-Q2
  • credit_reconciliation_group_id: null
  • accounting_period.is_closed: false

1. Detect existing IN_PROGRESS group: “group-abc”
2. Strategy: Complete existing group “group-abc”
3. Update group: “group-abc” → status=COMPLETED
4. Update GL entries:
   • Invoice: SKIP (closed period)
   • Payment: SET credit_reconciliation_group_id = “group-abc”

• Invoice: unchanged (immutable)
• Payment: linked to “group-abc”
• Group “group-abc”: COMPLETED

​

Scenario 2: Fresh Reconciliation (No Existing Groups)

• Entry A: No reconciliation group
• Entry B: No reconciliation group

1. No existing IN_PROGRESS groups found
2. Strategy: Create new group
3. Create group: “group-new” with status=COMPLETED
4. Update both entries to link to “group-new”

• Both entries linked to new group “group-new”

​

Scenario 3: Multiple IN_PROGRESS Groups (Error Case)

• Entry A: credit_reconciliation_group_id: “group-1” (IN_PROGRESS, reconciled_on: “GID-001”)
• Entry B: credit_reconciliation_group_id: “group-2” (IN_PROGRESS, reconciled_on: “GID-002”)

1. Multiple IN_PROGRESS groups detected: [“group-1”, “group-2”]
2. Strategy: Fail reconciliation (inconsistent state)
3. Return error: “MULTIPLE_IN_PROGRESS_GROUPS”
4. User must investigate and fix the data inconsistency

• Reconciliation fails with error
• Both groups remain unchanged
• Both entries remain unchanged
• Requires manual investigation to determine which group is correct

​

Logging & Debugging

• Closed period detection
• Existing group analysis
• Strategy selection
• Entry update results (updated vs skipped)
• Reasons for skipping entries

{
  "message": "GL entry updates completed",
  "reconciliationId": "group-abc",
  "totalEntries": 2,
  "updatedCount": 1,
  "skippedCount": 1,
  "skippedReasons": {
    "closed period": 1
  }
}

​

Variables & State

​

Common Issues & Solutions

​

Issue: Entry from closed period gets modified

​

Issue: Duplicate groups created for same entries

​

Issue: Entries already in group get “updated” unnecessarily

​

Issue: Belegfeld 1 (reconciled_on) gets changed after DATEV export

​

Testing Checklist

• Reconcile two fresh entries → creates new group
• Reconcile with closed period entry → closed entry unchanged
• Reconcile entries already in IN_PROGRESS group → completes that group
• Reconcile entries in different IN_PROGRESS groups → creates new group
• Reconcile with auto-booking → auto-booking entry handled correctly
• Check activity logs created only for updated entries
• Verify closed period entries never modified
• Verify no duplicate updates when completing existing group
• Verify reconciled_at timestamp ALWAYS updated to NOW() when completing
• Verify reconciled_on (Belegfeld 1) preserved when already set
• Verify reference_document_ fields preserved when already set*
• Reconcile again with already-exported group → Belegfeld 1 unchanged, reconciled_at updated