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.

Bill of lading held for payment

bl_held_unpaid unit: shipments operator detector canonical: finance

The vessel has arrived and the consignee needs the bill of lading, but it has not been released: the invoice is unpaid, or it has been paid and nobody released the bill. Either way the containers cannot leave the terminal.

financeFinance Controller · finance v1canonical

Sea shipments with an 'arrived' event at least grace_hours before now (within 30 days), a release request at or before now and no release at or before now. Paid means the shipment's invoice has paid_at at or before now; once paid, the problem is counted from the payment, because the right action changes.

latest change: First version.

  • pass count_betweenobserved 8, expected between 0.0 and 80.0
  • pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 8}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH arrived AS (
  SELECT e.shipment_id, min(e."at") AS arrived_at FROM shipment_events e
  WHERE e.type = 'arrived' AND e."at" <= TIMESTAMP 'Tue 18:00:00' - INTERVAL (2) HOUR
    AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 30 DAY
  GROUP BY e.shipment_id
)
SELECT 'shipment' AS subject_type, s.id AS subject_id, s.id AS shipment_id, s.shipper_id, i.id AS invoice_id,
       CASE WHEN i.paid_at <= TIMESTAMP 'Tue 18:00:00' THEN greatest(a.arrived_at, i.paid_at) ELSE a.arrived_at END AS started_at,
       'high' AS severity,
       json_object(
         'shipment', s.ref, 'arrived_at', a.arrived_at, 'requested_at', b.requested_at,
         'invoice', i.number, 'amount_usd', i.amount_usd, 'due_at', i.due_at,
         'payment_received', coalesce(i.paid_at <= TIMESTAMP 'Tue 18:00:00', FALSE),
         'paid_at', CASE WHEN i.paid_at <= TIMESTAMP 'Tue 18:00:00' THEN i.paid_at END,
         'days_overdue', CASE WHEN i.paid_at IS NULL OR i.paid_at > TIMESTAMP 'Tue 18:00:00'
                              THEN greatest(0, date_diff('hour', i.due_at, TIMESTAMP 'Tue 18:00:00') // 24) END,
         'containers', s.n_containers,
         'first_free_time_end', (SELECT min(c.last_free_at) FROM containers c WHERE c.shipment_id = s.id),
         'shipper', sh.name, 'shipper_contact', sh.contact_email, 'account_owner', sh.account_owner,
         'credit_terms_days', sh.credit_terms_days
       ) AS evidence
FROM bl_releases b
JOIN arrived a ON a.shipment_id = b.shipment_id
JOIN shipments s ON s.id = b.shipment_id
JOIN shippers sh ON sh.id = s.shipper_id
JOIN invoices i ON i.shipment_id = s.id AND i.issued_at <= TIMESTAMP 'Tue 18:00:00'
WHERE b.requested_at <= TIMESTAMP 'Tue 18:00:00' AND (b.released_at IS NULL OR b.released_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 2 · used by finance

History

append-only; nothing is edited in place
  1. definitionFinance Controller finance v1 First version.