# ERP Expense Settlement Advance Reconciliation and Input VAT > How to reconcile a cash advance against category-tagged expense lines in an ERP, and why VAT on each line stays informational unless a tax-invoice number exists. URL: https://www.matthewswong.com/en/blog/erp-expense-settlement-advance-reconciliation-input-vat/ - Author: Matthews Wong (https://www.matthewswong.com/en/about/) - Published: 2026-10-17 - Updated: 2026-10-17 - Category: ERP > **Key takeaway:** To reconcile a cash advance in an ERP, store the advance as one header and the settlement as category-tagged expense lines, then require the lines to sum to the settled amount before submission. Treat input VAT on each line as an informational flag that never changes that sum, and count it as creditable only when a tax-invoice number is present. A cash advance is the one AP document that is not finished when it is paid. Money has left the company, but nobody has said what it bought. Until the employee comes back with receipts, the advance is an open liability on the books, and the ERP has to know that. This post covers the settlement half of that document in an ERP I built: the data model, the single invariant that keeps the header honest, the approval routing for overspend, and how input VAT is carried on each expense line without ever touching the reconciliation. It is design and data model only. There are no tax rates or amounts here, because the design does not need them. ## Settlement is a reconciliation, not a second form The tempting model is a single request with an amount, and a settlement screen that overwrites it with the real figure. That loses the one thing finance needs, which is the comparison between what was advanced and what was actually spent. I model the advance and its settlement on the same record, but they are two different numbers with two different lifecycles. The advance amount is fixed at approval and payment. The settled amount is claimed later, backed by expense lines, and reviewed. The gap between them is the whole point: a positive gap is a surplus the employee returns, a negative gap is an overspend the company reimburses. Both need a reviewer, and the second needs a reason. ## The data model: one header, many category lines The schema below is a generic rewrite of the pattern, not the production source. The advance header carries the fixed amount, the claimed settled amount, a reconciliation flag and the optional overspend reason. Each expense line carries its own gross amount, a category, a date, and two tax-related fields that are both nullable. Two choices matter here. Money is a fixed-scale decimal column, never a float, because PostgreSQL documents numeric as the type recommended for monetary amounts. And the lines are children of the header with a cascade delete, so a resubmitted settlement replaces its lines wholesale instead of editing them one by one. Keep the category as a short controlled vocabulary such as transport, lodging, meals, supplies and other. The category is what lets finance group an advance by spend type, and a free-text field there produces five spellings of the same thing within a month. ## The one invariant: the lines are the settled amount The settled amount on the header and the sum of the expense lines must agree before the settlement can be submitted. A header total that merely sits next to its lines is a number someone typed; a header total that is checked against its lines is a reconciliation. The service rejects a submission when the two differ by more than a tolerance of one currency unit, which absorbs rounding but nothing else. Per-line rules such as a positive amount are declared on a DTO and checked by the NestJS ValidationPipe, so the service only has to enforce the rules that span lines. This cannot be a database constraint. PostgreSQL documents that CHECK constraints cannot reference table data other than the row being written, and a header-versus-children sum is exactly that. So the rule lives in the service, in the same method that decides the next status, and the comment in my code names the risk it closes: without it, a submitter could claim one figure on the header while the lines add up to another, and finance would approve the header with a broken audit trail. I should be exact about the evidence. That guard came from reasoning about the failure, not from an incident I can report. The reasoning is simple enough to trust, but it is a design decision, not a measurement. Do not let the VAT figure leak into this sum. Settling on net-of-VAT lines makes the reconciliation disagree with the receipts and the cash the employee actually spent. The settled amount is always gross, and the VAT portion is a label on a line that is already inside that gross. ## Overspend, underspend and who approves The comparison between the settled and advanced amounts decides the route. An advance may only be settled once it is in the paid state. Settled above the advance means overspend: a reason is mandatory and the settlement goes to the department manager first. Anything else goes straight to finance. In both cases a rejection sends the advance back to paid with the rejection reason stored, so the employee can resubmit. The reconciliation flag has three values: pending, partial and settled. In the code it is set at submission, and partial means the settled amount is below the advance, so a surplus is owed back. The service also accepts an optional reference for the surplus return transfer. It stores that reference; it does not model the return as a ledger entry, which I come back to in the limits. Situation Reconciliation flag Next status Extra requirement | | | | | | --- | --- | --- | --- | | Settled equals the advance | settled | pending_settlement (finance) | Lines sum to the settled amount | | Settled below the advance | partial | pending_settlement (finance) | Optional reference for the surplus return | | Settled above the advance | settled | pending_settlement_manager, then finance | Overspend reason is mandatory | | Rejected at either step | unchanged | paid (resubmit) | Rejection reason stored, previous approvals cleared on resubmit | ## Input VAT: informational on every line, creditable only with an invoice number Each expense line can carry an input VAT portion and a tax-invoice number. I treat these as two separate facts about the same line. The VAT amount is informational: it says how much of the gross was VAT, for the input-VAT recap. It does not change the line amount, the settled amount or the reconciliation. The invoice number is what decides whether that VAT counts as creditable. No number, no credit. The header gets a roll-up of the line VAT in the same transaction as the settlement, and the code is explicit that the roll-up never feeds the checks above it. The nested write that replaces the lines runs as one transaction in Prisma, so a failed resubmission cannot leave a header pointing at half of an old set of lines. The schema comment states the creditable rule, but the header roll-up in my service adds every line, with or without an invoice number. So the header figure is the informational total, and the creditable total has to be computed at read time by requiring the number. I would name the header column accordingly. ## Where each fact lives Most of the design is deciding which table owns which fact. This is the split I settled on, and the reason each fact sits where it does. Fact Lives on Why Advance amount Header, fixed at payment It is what was paid out; settlement must never overwrite it Settled amount Header, claimed at submit Compared with the advance to pick the route Spend category and date Expense line Lets finance group an advance by type of spend Input VAT portion Expense line, nullable Informational; never enters the settled amount Tax-invoice number Expense line, nullable The only thing that makes that VAT creditable Notice that nothing here needs a tax rate. The design only needs to know whether a VAT portion was recorded and whether an invoice number backs it. Which rate applies, and what is creditable under current rules, is a tax-compliance question that belongs in a dated, sourced reference rather than in a schema. ## What I would do differently, and the limits Reading my own service back, these are the places I would tighten, listed as limits rather than hidden. - The line sum is computed with JavaScript numbers and a tolerance, while the columns are decimals. Summing the lines with a decimal type and requiring an exact match would remove the tolerance. - The category is a free string with the vocabulary in a comment. A database enum or a lookup table would stop typos. - The reconciliation flag reads settled at submission, before finance has approved. The status column carries the real state, so the name is misleading and I would rename the flag. - A surplus return is a stored reference string, not a ledger entry, so nothing reconciles the returned cash against the books. None of these has been measured against production data. They are findings from reading the code, and the design choice at the centre, lines must equal the header, does not depend on fixing them. ## Monday checklist Store the advance amount and the settled amount in two separate columns and never overwrite the first. Use a fixed-scale decimal for every money column, on the header and on the lines. Reject a settlement submission when the line sum and the settled amount differ, in the service, since a CHECK constraint cannot see other rows. Require a reason for overspend, and route overspend to a manager before finance. Put the input VAT portion and the tax-invoice number on the line, both nullable, and keep the VAT out of every reconciliation sum. Compute the creditable total at read time by requiring the invoice number, and replace lines wholesale in one nested write on resubmission. A settlement is only trustworthy when its lines add up to its header and nothing else is allowed to move that sum. Reconcile gross against the advance, route overspend to a reviewer, and carry VAT as a flag on the line that becomes creditable only when an invoice number backs it. ## Sources and further reading - PostgreSQL docs: numeric types and arbitrary precision numbers - PostgreSQL docs: constraints, including the limits of CHECK - Prisma docs: transactions and nested writes - Prisma docs: special fields and types, including Decimal - NestJS docs: validation with ValidationPipe ## Source links - https://www.postgresql.org/docs/current/datatype-numeric.html - https://www.postgresql.org/docs/current/ddl-constraints.html - https://www.prisma.io/docs/orm/v7/prisma-client/queries/transactions - https://www.prisma.io/docs/orm/v7/prisma-client/special-fields-and-types - https://docs.nestjs.com/techniques/validation ## FAQ ### How do you reconcile a cash advance against expenses in an ERP? Keep the advance amount fixed on the header and record the settlement as expense lines, each with a gross amount, category and date. Before the settlement is submitted, check that the lines sum to the settled amount, then compare that figure with the advance. The difference is either a surplus to return or an overspend to reimburse. ### Why does input VAT not change the settled amount? The settled amount is gross: it is what the employee actually spent and what the receipts show. The VAT portion is already inside that gross, so it is stored as a label on the line for the input-VAT recap. Subtracting it would make the reconciliation disagree with the cash that left the employee. ### When is input VAT on an expense line creditable? In this design, only when the line also carries a tax-invoice number. The VAT amount alone is informational bookkeeping. Whether a given amount is actually creditable under current rules is a tax question, so check it against official sources rather than the schema. ### Can a database constraint enforce that expense lines add up to the header? Not a CHECK constraint. PostgreSQL documents that CHECK constraints cannot reference table data other than the row being written, and a header-versus-lines sum needs the other rows. Enforce it in the service, in the same method that decides the next status, or with a trigger. ### What should happen when an employee overspends a cash advance? Require a written reason and route the settlement to the department manager before finance reviews it. If the settlement is rejected, return the advance to the paid state with the reason stored so the employee can resubmit corrected lines. --- - HTML version: https://www.matthewswong.com/en/blog/erp-expense-settlement-advance-reconciliation-input-vat/ - Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-expense-settlement-advance-reconciliation-input-vat/ - Site index for agents: https://www.matthewswong.com/llms.txt - Sitemap: https://www.matthewswong.com/sitemap.xml - API description (OpenAPI): https://www.matthewswong.com/openapi.json