Lanes freight exceptions
World clock Tue 18:00:00 UTC held · 120×

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: docs

A 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 note
now 21 · used by docs

History

append-only; nothing is edited in place
  1. definitionDocumentation Lead docs v1 First version.