Do not trust what you extracted

By the end of this lesson, your code knows which of four documents it can post unattended, and it names the reason for each one it refuses.

Lesson 3 of 5~20 minScript: inv_extract_pkg.pks, inv_extract_pkg.pkb, 03_validate.sql

Validate extracted invoice data

Every answer in lesson 2 was complete, well formed and correct. A strict schema returns a complete answer for a document it read wrong, and for a document that is not an invoice at all. The answer looks the same either way, and needs_review has to come from somewhere other than the model.

This lesson builds the five checks that decide it. None of them calls a model.

When the document is not an invoice at all

ferrotek-delivery-note.pdf is a packing list. It has quantities and no prices, no totals and no VAT, and it prints this in a red box:

THIS IS A DELIVERY NOTE. IT IS NOT AN INVOICE. DO NOT PAY FROM THIS DOCUMENT.

Send it through a schema that has no document_type field, and here is what comes back:

Recorded answergpt-5.6-terra2026-08-25

supplier_name: FerroTek Components Ltd
invoice_number: (empty string)
po_number: PO-4500198231
total_amount: 0
extraction_note: This document is a delivery note, not an invoice. Delivery note no. DN-77118. No invoice number, currency, net amount, tax amount, or total amount is provided.

Your wording will differ. What must match is the data, and the checks below.

The invoice number is empty, and extraction_note names the document as a delivery note.

The fields in your hands are a supplier name, a real purchase order number, and a total of zero. Not one field says this must not be paid. The only text that says it is an English sentence in extraction_note, and no case statement can read an English sentence.

A typed field can hold the document type:

"document_type": { "type": "string",
                   "enum": ["INVOICE", "CREDIT_NOTE", "DELIVERY_NOTE", "OTHER"] }

Add that one field and run the same document again. The script prints this:

document_type: DELIVERY_NOTE
invoice_no:   [DN-77118]
total_amount:  0
checks:       [NOT_AN_INVOICE]

document_type is now a value your PL/SQL can branch on. invoice_number is no longer empty: it holds DN-77118, the delivery-note number, because the field asked for a number and the page has one.

Without the document_type check, that row posts as invoice DN-77118 for 0.00, against a real purchase order. Nothing in your database then shows an error.

Check that the invoice adds up

An invoice is arithmetic. It has to add up, and it adds up the same way in every country:

The line items add up to the net amount.
The net amount plus the tax is the total.

Neither rule needs your database, your supplier master or your purchase orders. The document carries its own check, and a document that fails it was read wrong, whatever else the answer looks like.

The European e-invoicing standard EN 16931 has these two rules as BR-CO-10 (the line totals add up to the total of the lines) and BR-CO-15 (the total with tax is the total without tax plus the tax). If you need more checks than the two here, that is the list to take them from.

The three invoices, from the recorded run:

Document	Sum of line_total	net_amount	tax	total
`ferrotek.pdf`	2136.50	2136.50	427.30	2563.80
`nordwind.pdf`	6450.85	6450.85	1225.66	7676.51
`halvorsen.pdf`	4375.28	4375.28	350.02	4725.30

All three hold, to the cent.

The first version of the schema allowed TAX as a line kind. In three runs over halvorsen.pdf the line count came back two ways: two runs added a tax line and one did not. The sum check then compared a different thing each time.

Three runs is not a measurement of how often that happens. It shows that the schema made the check unreliable. Lesson 5 has the published version of this behaviour.

The second version defined net_amount as “the net amount as printed”. On ferrotek.pdf that is the subtotal of 2,091.50, which leaves out the 45.00 delivery charge. So net + tax did not equal total on a good invoice.

The system prompt fixes both, and the schema does not. Tax is not a line kind, and net_amount is defined as total_amount minus tax_amount. An ambiguous field definition looks exactly like a model error.

Compare amounts with a tolerance, not with =:

c_cent constant number := 0.005;

if nvl(l_inv.get_string('document_type'), 'MISSING') = 'INVOICE'
   and ( abs(l_line_sum - l_net) > c_cent
         or abs(l_net + l_tax - l_total) > c_cent )
then
  add_reason('SUM_MISMATCH');
end if;

The guard is needed, and so is the field it guards on. A delivery note is all zeros, and zeros add up perfectly, so the check has to skip it.

A condition of if l_total != 0 skips invoices with an incorrectly extracted zero total. SUM_MISMATCH exists for the invoice a model read so wrong that it returned a total of 0. That version lets exactly that case pass. Guard on the typed field, which the model cannot make disappear.

The nvl is there for the same reason. An absent document_type gives NULL, and NULL != 'INVOICE' is NULL. Without the nvl, a document with no type at all passes the one check that exists to stop it.

The date 08/11/2026 means two different days

halvorsen.pdf prints its date as 08/11/2026.

In Cleveland, Ohio that is 11 August. In Leeds it is 8 November. The model picks one and never tells you which. In the recorded run it picked 11 August, and it was right, because the invoice also prints “Net 45” and a due date of 09/25/2026. Eleven August plus 45 days is 25 September. Eight November plus 45 days is not.

The model does not always find a clue like that. Two fields from the schema in lesson 2 exist for exactly this, and they always come back as a pair:

"invoice_date_raw": { "type": "string" },
"invoice_date_iso": { "type": "string" }

invoice_date_raw is the string as printed. invoice_date_iso is the model’s reading of it as YYYY-MM-DD. Your code can find the problem because it has both:

l_date_raw := l_inv.get_string('invoice_date_raw');

if regexp_like(l_date_raw, '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$') then
  l_first  := to_number(regexp_substr(l_date_raw, '^[0-9]{1,2}'));
  l_second := to_number(regexp_substr(l_date_raw, '[0-9]{1,2}', 1, 2));

  if l_first <= 12 and l_second <= 12 then
    add_reason('DATE_AMBIGUOUS');
  end if;
end if;

The check does not find the right day. The right day is not on the document. The check finds the documents where a person has to decide. It can do that because invoice_date_raw is stored.

25/11/2026 does not raise the flag. Only one of those two numbers can be a month.

You can add a date format to the string field in the schema:

"invoice_date": { "type": "string", "format": "date" }

UC AI passes format through and OpenAI accepts it. Here is what three runs over halvorsen.pdf returned with that schema, and nothing else changed:

{"invoice_number":"HB-88214","invoice_date":"0811-12-31"}
{"invoice_number":"HB-88214","invoice_date":"0811-11-11"}
{"invoice_number":"HB-88214","invoice_date":"0811-11-20"}

Every one is nonsense, and every one is a valid YYYY-MM-DD date. The model has to produce that shape, it cannot decide which of 08 and 11 is the month, and so it pushed both into the year.

A type says what the answer must look like. It cannot say what the answer is. Two plain strings and one rule in the prompt do better, because they let the model tell you what it saw as well as what it decided.

Check the invoice against the order you placed

The invoice alone cannot establish whether the purchase order exists. This check compares the extracted order number with records in your database.

halvorsen.pdf names PO-4500199003. Nobody raised it. And nordwind.pdf names a purchase order that does exist, for 6,300.00 EUR, against an invoice of 6,450.85 net. That is 150.85 over.

l_po_number := l_inv.get_string('po_number');

begin
  select * into l_po
    from inv_purchase_orders
   where po_number = l_po_number;

  if l_po.status != 'OPEN' then
    add_reason('PO_NOT_FOUND');
  elsif l_net > l_po.amount_limit then
    add_reason('OVER_PO');
  end if;
exception
  when no_data_found then
    add_reason('PO_NOT_FOUND');
end;

An empty po_number lands in no_data_found, which is the right answer. An invoice with no purchase order is an invoice nobody agreed to.

What the five checks say about each document

Five checks, four documents:

ferrotek.pdf                  []
nordwind.pdf                  [OVER_PO]
halvorsen.pdf                 [DATE_AMBIGUOUS, PO_NOT_FOUND]
ferrotek-delivery-note.pdf    [NOT_AN_INVOICE]

One document is clean. Three are not, for three different reasons. Every reason came from your database or from the arithmetic of the page. None of them came from a question to the model about its confidence.

SUM_MISMATCH did not fire. Keep that check for the day a model misses page 2.

Verification

One block in 03_validate.sql proves every branch from hand-written JSON, in under a second, and for no tokens:

sys.dbms_output.put_line('page 1 only -> [' || check_json('{
  "document_type":"INVOICE","supplier_name":"FerroTek Components Ltd",
  "invoice_number":"FT-2","invoice_date_raw":"14 July 2026",
  "invoice_date_iso":"2026-07-14","po_number":"PO-4500198231","currency":"GBP",
  "net_amount":1000,"tax_amount":200,"total_amount":1200,
  "line_items":[{"position":1,"description":"x","kind":"GOODS","quantity":1,
  "unit_price":600,"line_total":600}],"extraction_note":""}') || ']');

All eight payloads:

clean            -> []
page 1 only      -> [SUM_MISMATCH]
ambiguous date   -> [DATE_AMBIGUOUS]
unambiguous date -> []
unknown PO       -> [PO_NOT_FOUND]
closed PO        -> [PO_NOT_FOUND]
over the PO      -> [OVER_PO]
two failures     -> [NOT_AN_INVOICE, PO_NOT_FOUND]

Keep this block in your own project. Your checks are ordinary PL/SQL over ordinary JSON, so they can be tested like ordinary PL/SQL. Nothing about them needs a model, a network or a key.

Key takeaways

A strict schema has no way to say “the document does not have this”, so the model returns a value anyway. Decide what an absent value looks like, and give your code a typed field it can branch on.
The arithmetic of the document is a check that needs nothing else. An invoice that does not add up was read wrong, whatever the answer looks like.
if nvl(l_inv.get_string('document_type'), 'MISSING') != 'INVOICE' then add_reason('NOT_AN_INVOICE'); end if;

Full reference: Structured output covers what each provider does with your schema.

Next lesson: From the answer into your tablesWalk the JSON into an invoice header and its lines, read money out of JSON safely, and see what a person still has to look at.