Definitions
The claims terms the operator, its detectors and Ask all run on. Each has an owner, a version, SQL and tests. Edits make a new version.Two invoices for one repair
double_invoice unit: claims operator detectorThe same repair invoiced twice on one claim: the same garage or contractor, a new invoice number, and an amount within a couple of per cent of the first. Paying both is an overpayment.
financeHead of Claims Finance · finance
v1
On any claim, open or closed, a second invoice from the same vendor with a different reference, an amount within tolerance of an earlier invoice, received within days_apart of it (and in the last 90 days). An invoice re-issued with tax added on top is outside the tolerance and is not caught by this version.
latest change: First version.
- pass count_betweenobserved 80, expected between 1.0 and 80.0
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 80}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, b.vendor_id, b.id AS document_id,
p.id AS payment_id, b.received_at AS started_at,
CASE WHEN p.paid_at <= TIMESTAMP 'Mon 17:00:00' THEN 'high' ELSE 'medium' END AS severity,
json_object(
'claim_no', c.claim_no, 'first_invoice', a.ref, 'first_amount', a.amount, 'first_received_at', a.received_at,
'second_invoice', b.ref, 'second_amount', b.amount, 'second_received_at', b.received_at,
'difference_pct', round(100 * (b.amount / a.amount - 1), 2), 'vendor', v.name,
'second_payment_status', CASE WHEN p.id IS NULL THEN 'not requested'
WHEN p.held_at <= TIMESTAMP 'Mon 17:00:00' THEN 'held'
WHEN p.paid_at <= TIMESTAMP 'Mon 17:00:00' THEN 'paid'
WHEN p.approved_at <= TIMESTAMP 'Mon 17:00:00' THEN 'approved'
ELSE 'requested' END
) AS evidence
FROM documents a
JOIN documents b ON b.claim_id = a.claim_id AND b.kind = 'invoice' AND b.id > a.id
AND b.vendor_id = a.vendor_id AND b.ref <> a.ref AND abs(b.amount / a.amount - 1) <= 0.02
AND b.received_at - a.received_at <= INTERVAL (30) DAY
JOIN claims c ON c.id = a.claim_id
LEFT JOIN vendors v ON v.id = b.vendor_id
LEFT JOIN payments p ON p.invoice_doc_id = b.id AND p.requested_at <= TIMESTAMP 'Mon 17:00:00'
WHERE a.kind = 'invoice' AND b.received_at <= TIMESTAMP 'Mon 17:00:00' AND b.received_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
ORDER BY started_at, c.id
Parameters
change one, see what changes today, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionHead of Claims Finance finance v1 First version.