Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.Document names the wrong container
container_number_mismatch unit: documents operator detector canonical: docsA bill of lading, packing list or VGM declaration that names a container number the booking does not have. Spaces and lower case are ignored; a number written without its check digit is matched on the first ten characters. A typo that keeps a valid check digit is still caught, because the booking is the reference.
docsDocumentation Lead · docs
v1canonical
Container numbers on documents received at or before now (within lookback_days), as corrected if the correction is at or before now, upper-cased with spaces removed, that match no container of the shipment (in full, or on the first ten characters). The evidence names the closest booked number and how many other documents of the shipment carry it.
latest change: First version.
- pass count_betweenobserved 4, expected between 0.0 and 100.0
- pass uniqueobserved {'duplicates': 0}, expected ref_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 4}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH refs AS (
SELECT r.id AS ref_id, r.document_id, r.shipment_id, d.doc_type, d.received_at, r.container_number AS written,
upper(replace(CASE WHEN r.corrected_at <= TIMESTAMP 'Tue 18:00:00' THEN r.corrected_number ELSE r.container_number END,
' ', '')) AS number_now
FROM document_refs r JOIN documents d ON d.id = r.document_id
WHERE d.received_at <= TIMESTAMP 'Tue 18:00:00' AND d.received_at >= TIMESTAMP 'Tue 18:00:00' - INTERVAL (21) DAY
),
bad AS (
SELECT f.* FROM refs f
WHERE NOT EXISTS (SELECT 1 FROM containers c WHERE c.shipment_id = f.shipment_id
AND (c.number = f.number_now OR left(c.number, 10) = f.number_now))
),
closest AS (
SELECT b.ref_id, arg_min(c.number, levenshtein(c.number, b.number_now)) AS booked_number,
arg_min(c.id, levenshtein(c.number, b.number_now)) AS container_id,
min(levenshtein(c.number, b.number_now)) AS distance
FROM bad b JOIN containers c ON c.shipment_id = b.shipment_id
GROUP BY b.ref_id
)
SELECT 'document_ref' AS subject_type, b.ref_id AS subject_id, b.ref_id, b.document_id, b.shipment_id,
s.shipper_id, b.received_at AS started_at, 'medium' AS severity,
json_object(
'shipment', s.ref, 'doc_type', b.doc_type, 'written_number', b.written, 'read_as', b.number_now,
'booked_number', k.booked_number, 'booked_container_id', k.container_id, 'distance', k.distance,
'other_documents_with_booked_number',
(SELECT count(DISTINCT f.document_id) FROM refs f WHERE f.shipment_id = b.shipment_id
AND f.document_id <> b.document_id AND (f.number_now = k.booked_number
OR f.number_now = left(k.booked_number, 10))),
'shipper', sh.name, 'shipper_contact', sh.contact_email
) AS evidence
FROM bad b
JOIN closest k ON k.ref_id = b.ref_id
JOIN shipments s ON s.id = b.shipment_id
JOIN shippers sh ON sh.id = s.shipper_id
ORDER BY started_at, b.ref_id
Parameters
change one, see what changes in the window, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionDocumentation Lead docs v1 First version.