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.

HS code does not fit the goods

hs_code_mismatch unit: lines operator detector canonical: customs

A customs declaration line whose HS chapter is not one the customs desk's keyword table expects for the words in its description. Lines whose description matches no keyword are not judged.

customsCustoms Desk Lead · customs v1canonical

Lines of declarations drafted at or before now that are not yet released, or released within released_lookback_days, and not put on hold by the desk as of now. The code as of now is the amended code if the amendment is at or before now, else the declared one. Every hs_keywords keyword contained in the lower-cased description gives an expected chapter; the line counts when it matched at least one keyword and its chapter is none of them. The suggestion is the desk's typical code for the first matched keyword.

latest change: First version.

  • pass count_betweenobserved 0, expected between 0.0 and 300.0
  • pass uniqueobserved {'duplicates': 0}, expected subject_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
WITH lines AS (
  SELECT l.id, l.declaration_id, l.line_no, l.description, l.value_usd,
         CASE WHEN l.amended_at <= TIMESTAMP 'Tue 18:00:00' THEN l.hs_code ELSE l.declared_hs_code END AS code_now,
         d.shipment_id, d.drafted_at, d.filed_at, d.released_at, d.broker
  FROM customs_lines l JOIN customs_declarations d ON d.id = l.declaration_id
  WHERE d.drafted_at <= TIMESTAMP 'Tue 18:00:00'
    AND (d.released_at IS NULL OR d.released_at > TIMESTAMP 'Tue 18:00:00' - INTERVAL (10) DAY)
    AND (d.on_hold_at IS NULL OR d.on_hold_at > TIMESTAMP 'Tue 18:00:00')
),
hits AS (
  SELECT l.id, list(DISTINCT k.chapter ORDER BY k.chapter) AS chapters,
         list(DISTINCT k.keyword ORDER BY k.keyword) AS words,
         min(k.typical_code) FILTER (WHERE k.chapter <> substr(l.code_now, 1, 2)) AS suggested
  FROM lines l JOIN hs_keywords k ON contains(lower(l.description), k.keyword)
  GROUP BY l.id
)
SELECT 'customs_line' AS subject_type, l.id AS subject_id, l.shipment_id, s.shipper_id, l.declaration_id,
       l.line_no, l.drafted_at AS started_at, 'medium' AS severity,
       json_object(
         'shipment', s.ref, 'description', l.description, 'declared_code', l.code_now,
         'declared_chapter', substr(l.code_now, 1, 2), 'expected_chapters', h.chapters,
         'matched_words', h.words, 'suggested_code', h.suggested,
         'filed', coalesce(l.filed_at <= TIMESTAMP 'Tue 18:00:00', FALSE), 'filed_at', CASE WHEN l.filed_at <= TIMESTAMP 'Tue 18:00:00' THEN l.filed_at END,
         'released', coalesce(l.released_at <= TIMESTAMP 'Tue 18:00:00', FALSE), 'broker', l.broker, 'value_usd', l.value_usd,
         'shipper', sh.name
       ) AS evidence
FROM lines l
JOIN hits h ON h.id = l.id
JOIN shipments s ON s.id = l.shipment_id
JOIN shippers sh ON sh.id = s.shipper_id
WHERE NOT list_contains(h.chapters, substr(l.code_now, 1, 2))
ORDER BY started_at, l.id

Parameters

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

History

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