Recovering three months of unrecoverable ERP data

Context

I spent four years at TOTVS, Latin America’s largest ERP provider — the last three as ERP Consultant, leading end-to-end implementations across manufacturing, supply chain, procurement and cost management for clients in Colombia and Ecuador.

On one engagement a client discovered that three months of inventory and billing records were missing from the system. Not corrupted in an obvious way — simply not there when the reports ran. The internal conclusion was that the data was lost and the quarter would have to be reconstructed manually from paper, if it could be reconstructed at all.

The constraint

There was no backup that predated the problem and postdated the last good state. The only copy of that quarter was whatever remained inside the live production database — which meant any investigation had to happen without disturbing a system the client was actively running their business on.

What I did

I started from the assumption that “the data is gone” was a conclusion about the reports, not about the database. Those are different claims, and only one of them had actually been tested.

• Traced the data model rather than the symptom. I worked backwards from what the reports queried to what the tables actually contained, table by table, to find where the chain broke.
• Wrote SQL to read the records directly. The underlying rows existed. They had lost the relationships and flags that made them visible to the application layer.
• Rebuilt the records with ADVPL scripts — TOTVS’s own scripting language — so the restoration went through the ERP’s logic rather than around it. A raw database write would have produced rows that looked right and behaved wrong.
• Validated against the client’s own totals before declaring anything recovered.

Results

• 100% of three months of inventory and billing records recovered, validated against the client’s own figures.
• No manual reconstruction — the quarter closed on real data.

The rest of the four years

• Full data migration for three companies inside one sugar mill in Ecuador — procurement, inventory, costs and accounting master catalogs. Three legal entities, one operation, three sets of assumptions about what a “product” is.
• A knowledge-transfer framework built on video documentation and case studies. I made it for myself because I was tired of re-explaining the same implementation decisions. The team adopted it to accelerate later projects — the same pattern that would repeat years later at MedStar.
• Cross-functional user training and integration testing across multiple industries and client teams, in-situ at production sites.

What I’d do differently

• I’d have documented the recovery as it happened. I reconstructed the method afterwards for the knowledge-transfer framework, and lost detail doing it. Writing while working is slower in the moment and faster overall — a lesson I now apply by default.
• I’d push harder on the root cause. We recovered the quarter. We spent less time than we should have on why the relationships broke in the first place, which means the same failure was still possible the following month.
• I’d involve the client’s own team in the recovery. Doing it for them was faster. Doing it with them would have left capability behind, which is the thing I actually care about now.

Stack

SQL · ADVPL · TOTVS ERP (MRP, supply chain, procurement, costs, quality) · Excel · Power Query · Agile / Scrum