Radix-Skills/skills/data-science/erp-data-reconciliation/references/coop-payment-advice.md

# Coop betalingsadviseringer / payment advice as reconciliation bridge

Use this when Coop provides per-payment settlement/advice documents (PDF/XLSX)
for bank deposits. These documents can turn previously unexplained bank deposits
into explainable groups of invoice, credit, bonus, and modregning lines.

## Durable workflow

1. Inventory files by extension and classify each file:
   - `payment_advice` — Coop betalingsadvisering/payment advice with invoice lines.
   - `bank_statement` — bank print/screenshot containing a Coop deposit message.
   - `unknown` — manual calculation, unsupported layout, or needs OCR/manual review.
2. Extract payment advice rows:
   - invoice/fakturanummer
   - invoice date/fakturadato
   - company code/firmakode
   - amount/beløb
   - currency/valuta
   - raw row/text and parse confidence
3. Compute advice totals from parsed rows; do not rely solely on a trailing total
   because some PDFs have manually added middle calculations or split sign columns.
4. Match advice total to bank transaction with ±5 DKK tolerance.
5. Match advice lines to Coop ledger by reference/doc number and to Uniconta by
   invoice number. Fall back to amount/date only for remaining lines.
6. Produce a bank-deposit-level report for accounting review.

## PDF/XLSX parser patterns

XLSX pattern:
- Supplier metadata may be rows 1-2.
- Header row often contains: `Fakturanummer`, `Fakturadato`, `Firmakode`, `Beløb`,
  `Valuta`.
- Dates can already be Excel datetimes.

Text PDF pattern A:
```text
Payment Advice
Fakturanummer
Fakturadato
Firmakode Beløb
Valuta
5119751312
17.11.2023 2245
-1556,98 DKK
...
```

Text PDF pattern B with split sign:
```text
5126294822
24.10.2024 1000
2.119,86
-
DKK
```
Treat the amount as negative when a standalone `-` follows the amount.

Bank-statement PDFs:
- May include `Kontobevægelser`, `Coop Danmark A/S -SE MEDD.`, and a bank amount.
- Do not parse these as settlement invoice lines; classify separately and optionally
  link to the bank transaction/message.

## Reporting shape

Per bank deposit show:
- bank date
- bank amount
- matched payment advice file(s)
- total according to advice
- difference
- number of advice lines
- number of lines found in Coop ledger
- number of invoice numbers found in Uniconta
- credit/negative line total
- status: `OK`, `partial`, `needs_review`, `out_of_period`

## Interpretation pitfalls

- Advice files may include invoice dates from prior years but payment dates in the
  current year. Derive both invoice year and payment/bank year.
- A low overlap with existing “likely Coop owes” flags does not mean the advice
  files are useless; it means the analysis must model settlement files as their
  own source rather than using them as loose attachments.
- 2026 advice files should be imported but labelled out-of-period when bank or
  Uniconta data for 2026 is incomplete.
- Keep original PDFs/XLSX confidential and out of git; only commit parser code,
  schemas, docs, and non-sensitive aggregate findings.

## Coop-Uniconta session observations

A mixed folder of 125 files contained 116 PDFs and 9 XLSX files. A first pass found
122 payment advice files, 2 bank-statement PDFs, and 1 unknown/special PDF. Parsed
advice lines were strong as a bridge: most lines matched Coop references/doc
numbers, many matched Uniconta invoice numbers, and many previously unexplained
bank deposits could be explained by matching advice totals to bank amounts within
±5 DKK.

Later production import of the same class of files used PyMuPDF (`fitz`) for PDFs
and pandas/openpyxl-style Excel parsing, creating one source-file record per
Payment_advice file and line-level records with raw JSON/text. A folder with 125
files (116 PDF, 9 Excel) produced 2,727 Payment_advice lines without mutating
original files. For a 2025 auditor pack, 1,253 lines were relevant and 78 of 296
open invoice/credit-note residuals had a Payment_advice trail.

Important interpretation: a Payment_advice line must not automatically close or
"pay" an invoice in the ledger analysis. Treat it as an evidence/explanation layer
for open items: the invoice can remain open in Uniconta while the advice proves
Coop included it in a settlement, modregning, credit, bonus, or manual middle
calculation. Reports should therefore add fields such as advice line count, advice
amount, document list, voucher/bilag list, classifications, and a human-readable
explanation rather than changing ledger status silently.

Implementation pattern that worked:
- Add `PyMuPDF>=1.24` to backend dependencies before relying on PDF import in Docker.
- Extend the batch importer to include `Entydigt*.pdf`, `Entydigt*.xlsx`, `*.xlsm`,
  and `*.xls` from the payment-advice folder.
- Parse file names like `Entydigt id_4765_Bilag_7283.xlsx` into unique/document id
  and voucher/bilag metadata when present.
- Classify lines as payment, credit, adjustment/manual total, or unknown, while
  preserving raw row/text for manual review.
- Add regression tests that cover the case "open invoice has Payment_advice trail
  but no Uniconta payment line"; expected output is explanatory evidence, not an
  auto-paid status.
- Regenerate Markdown + Excel auditor packs after import and include Payment_advice
  coverage counts in the summary.