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.

Customs hold without a document request

customs_hold unit: shipments operator detector canonical: customs

An import declaration that customs is holding for a missing or wrong document, with nobody having asked the shipper for that document since the hold. While it is held the cargo cannot leave the terminal.

customsCustoms Desk Lead · customs v1canonical

held_at at least grace_minutes before now, not released as of now, and no document request for the shipment made at or after held_at (and at or before now) that is still open. The document customs wants is read from the hold reason: 'missing <document>' names it; a value mismatch needs a corrected commercial invoice.

latest change: First version.

  • pass count_betweenobserved 0, expected between 0.0 and 200.0
  • pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
SELECT 'shipment' AS subject_type, s.id AS subject_id, s.id AS shipment_id, s.shipper_id, d.id AS declaration_id,
       d.held_at AS started_at, 'high' AS severity,
       json_object(
         'shipment', s.ref, 'mode', s.mode, 'hold_reason', d.hold_reason, 'held_at', d.held_at,
         'hours_held', round(date_diff('minute', d.held_at, TIMESTAMP 'Tue 18:00:00') / 60.0, 1),
         'doc_type', CASE WHEN d.hold_reason LIKE 'missing %' THEN replace(substr(d.hold_reason, 9), ' ', '_')
                          ELSE 'commercial_invoice' END,
         'broker', d.broker, 'shipper', sh.name, 'shipper_contact', sh.contact_email,
         'account_owner', sh.account_owner, 'destination', l.name,
         'documents_on_file', (SELECT list(DISTINCT doc.doc_type ORDER BY doc.doc_type) FROM documents doc
                               WHERE doc.shipment_id = s.id AND doc.received_at <= TIMESTAMP 'Tue 18:00:00'
                                 AND (doc.rejected_at IS NULL OR doc.rejected_at > TIMESTAMP 'Tue 18:00:00'))
       ) AS evidence
FROM customs_declarations d
JOIN shipments s ON s.id = d.shipment_id
JOIN shippers sh ON sh.id = s.shipper_id
JOIN locations l ON l.id = s.dest_id
WHERE d.held_at <= TIMESTAMP 'Tue 18:00:00' - INTERVAL (30) MINUTE
  AND (d.released_at IS NULL OR d.released_at > TIMESTAMP 'Tue 18:00:00')
  AND NOT EXISTS (SELECT 1 FROM document_requests r WHERE r.shipment_id = s.id AND r.requested_at >= d.held_at
                    AND r.requested_at <= TIMESTAMP 'Tue 18:00:00' AND (r.cancelled_at IS NULL OR r.cancelled_at > TIMESTAMP 'Tue 18:00:00'))
ORDER BY started_at, s.id

Parameters

change one, see what changes in the window, then save it as a new version with a note
now 30 minutes · used by customs

History

append-only; nothing is edited in place
  1. definitionCustoms Desk Lead customs v1 First version.