Adjust claims desk · Quillmark Mutual
World clock Mon 17:00:00 CET paused · 60×

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 detector

The 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 note
now 0.02 · used by finance
now 30 · used by finance

History

append-only; nothing is edited in place
  1. definitionHead of Claims Finance finance v1 First version.