Global Real Estate & Construction — Financial Ingestion & Project Master Sync

Financial ingestion and project master synchronization—reference architecture

Summary

Delivered a finance-grade ingestion and reconciliation pipeline for multi-issuer credit card statements plus an event-driven master sync for Person/Project/WBS. The solution cut month-end delays, reduced reconciliation noise, and established consistent golden entities for downstream planning and reporting.

Problem

Month-end close delays from manual handling of VISA/AMEX/Mastercard statements, format drift, and rework on split/export.
Inconsistent masters for Person/Project/WBS across ERP, MDM, and TM1—causing lookup failures, stale attributes, and planning mismatches.
Audit risk: lack of uniform masking, archival, and change tracking for sensitive data and financial artifacts.

Solution Mechanics

Primary pattern: Batch sync for financial statement ingestion (file → validate → split → load → confirm → archive).
Secondary pattern (used): Event-driven streaming for Person/Project/WBS synchronization and profile retrieval.

Ingestion & Reconciliation (Statements)
- Statement Loader (scheduled pickup): retrieves issuer files from secure shares/SFTP; optional decryption; validates headers/footers and record counts.
- Org Unit Resolver: looks up operating unit per card account via ERP APIs; classifies multi-org, non-existent, end-dated, and no-transaction cases.
- Splitter & Publisher: writes one transaction file per org into ERP intake; generates CSV summaries (amounts, counts, codes) and error reports; archives originals + splits to ECM.
- Request-Set Invoker & File Cleanup: triggers ERP request sets per org unit; listens for success events to delete staged files from ERP landing zones.
Master Data & Project Sync
- Person 360: synchronous, coarse-grained search & retrieve services that federate MDM + ODS/AD with case/logic-aware search options; exposes fine-grained getters assembled into one-call APIs.
- Project & WBS Publisher: reacts to ERP project lifecycle events; publishes Project master to MDM and Project+WBS to TM1; supports grouping & hierarchy propagation from MDM → TM1.
- Reliability: retryable exception/fallout queues, ordered fan-out, and DLQ/replay for both ingestion and master sync.
Data, Storage & Controls
- Oracle Database for transient staging tables (statement lines, splits, error catalogs, per-region variants) and run metadata.
- Messaging (JMS) topics/queues for statement events, exception routing, and master sync fan-out.
- ECM archival of originals, splits, error CSVs, and reconciliation summaries; PII masking (only last 4 digits in notifications).
- Observability: job/run histories, per-phase success metrics, and drift checks on master propagation.

Diagram 1 - Context Diagram — Batch ingestion + event-driven master sync across ERP, MDM, TM1

Context Diagram — Batch ingestion + event-driven master sync across ERP, MDM, TM1

Diagram 2 - Sequence — Multi-issuer statement flow: pickup → validate/split → ERP load → confirm → cleanup

Sequence — Multi-issuer statement flow: pickup → validate/split → ERP load → confirm → cleanup

Diagram 3 - Operations — DLQ/replay, exception categorization, and master publish ordering

Operations — DLQ/replay, exception categorization, and master publish ordering

Process Flow

Pickup & Validate: poll issuer/secure shares; decrypt if required; validate header/footer and record counts.
Resolve Org Unit per card using ERP APIs; classify multi-org / end-dated / missing / no-transaction cases.
Split & Publish: create per-org transaction files in ERP intake; archive originals; generate summary + error CSVs.
Trigger ERP processing via request sets; track run status per org.
On success: remove processed files from ERP landing zones; archive summaries/reports; notify stakeholders.
Person 360 search/retrieve: synchronous calls aggregate MDM + ODS/AD attributes with case/logic-aware options.
Project events from ERP: publish Project master to MDM and Project+WBS to TM1; propagate grouping/hierarchy from MDM → TM1.
Exceptions: route to fallout queues; operators use DLQ/replay to recover; audit trails updated.

Outcomes

Faster close: automated splits, request-set orchestration, and cleanup shortened month-end processing windows.
Reconciliation clarity: issuer-specific summaries + error CSVs improved investigation throughput and reduced rework.
Master consistency: near-real-time Person/Project/WBS alignment across ERP/MDM/TM1 elevated planning/reporting trust.

Strategic Business Impact

Month-end cycle time –20–40% (Verified): measured by job histories and ERP request-set completions in pilot months.
Reconciliation error rate –30–50% (Proxy): driven by issuer validation, per-org splits, and error categorization reports.
Planning readiness uplift (Modeled): assumes reduction in stale masters and faster TM1 availability improves plan run timeliness.

Role & Scope

Owned solution design for statement ingestion, reconciliation reporting, and master sync. Delivered Java services on the Integration Layer (WebLogic), JMS topics/queues, Oracle DB staging schemas, ECM archival, exception handling (DLQ/replay), and Person 360 / Project+WBS APIs and flows.

Key Decisions & Trade-offs

Batch ingestion for statements vs ad-hoc uploads: governed cadence and repeatability; required robust validation and archival.
Event fan-out for masters vs nightly dumps: fresher data in TM1/MDM; added ordering and retry concerns.
Coarse-grained Person 360 reduces chatty calls; demanded careful case-sensitive search and multi-value handling options.
Strict masking & no-payload logging for financial data: audit-safe; limited raw payload inspection—offset via structured run logs.
Per-org splitting simplified ERP processing; increased file counts—mitigated by request-set batching and notifications.

Risks & Mitigations

Format drift (issuer specs) → schema validators + regression suites; versioned parsers.
PII exposure → at-source masking in notifications; encrypted transit; ECM retention controls.
DLQ growth → age-based alerts, replay windows, and auto-archive; runbooked backoffs.
Master ordering issues (Project before WBS, MDM before TM1) → publish dependencies, idempotent consumers, and causal ordering keys.
Regional variants (same card type, multiple regions) → region tags in staging, separate pickup folders, and per-region transforms.

Suggested Metrics (run-time SLOs)

Ingestion lead time p95 (pickup → ERP request-set start) and completion p95 (pickup → cleanup).
Validation failure rate by issuer/region; reconciliation rework hours avoided (proxy).
DLQ depth & replay age (statements, master events).
Person 360 latency p95 and match success rate; search case-sensitivity overrides frequency.
Project/WBS publish lag (ERP event → MDM/TM1 visible).

Closing principle

Make finance ingestion deterministic and masters authoritative—close faster by removing variance, and plan better by removing doubt.