TMS-LHT(Long Haul Transportation)

Schema Info

Ord Table

This table stores order details, including columns:

  • code
  • soldTo
  • type - pick/drop
  • deliveryDate
  • netValue
  • customer - the destination where the order will be dropped or picked up
  • orderedAt - time of order(default current time)

Line Table

This table stores the line details for each order, it includes columns:

  • orderId - the parent ID from order table
  • sku
  • mrp
  • qty
  • uom
  • wt
  • vol

Load Table

Load means the deliverable capacity at a time for a truck. This table stores the load details containing lines, including columns:

  • code
  • vehicleType
  • vehicle
  • driver
  • dock - dock schedule(startTime and endTime)
  • transporter
  • container
  • dispatchDate
  • planId
  • active - (default true)

State Tables

We have seperate state table for ord, line and load tables, used to store the previous and current states. It includes the columns

  • Parent ID(orderId, lineId, loadId)
  • prevStateId - points to previous state row
  • state - current state(default UNPLANNED)
  • blocked - (default false)
  • active - (default true)
  • deleted - (default false)

Load-Line Table

This table is used to determine the mapping between lines and loads, as lines(also order) can be splitted into one or more loads

  • orderId(for querying purposes)
  • lineId
  • loadId
  • qty
  • active - (default true)

Load-Route Table

This table is used for storing the route sequence for the delivery trucks

  • loadId
  • customer
  • index
  • active - (default true)

Contract Table

This table is used to store the contracts assigned to carriers

  • carrier
  • type
  • startDate
  • endDate
  • fileUrl
  • active - (default true)

Plan Table

This table stores plans created from orders

  • code
  • completedAt
  • frozenAt
  • active - (default true)
  • dispatchDate

Load Tender Table

This table is used to store the tender details for loads

  • loadId - corresponding load's id
  • transporterId - transporter's id
  • active - (default true)
  • status - (default PENDING)

Plan Index Table

To keep track of plans indices

  • index

All the tables contain these common columns node_id, created_at, updated_at

LIFECYCLE STATES

Order states

  • Unplanned
  • PLANNING IN PROGRESS
  • PLANNED
  • PARTIALLY PLANNED
  • LOAD CREATED
  • PART-LOAD CREATED
  • OUT FOR DELIVERY ( when all loads associated are out for delivery)
  • PART DELIVERED ( When one or more loads associated got delivered but not all)
  • Delivered

LINE STATES

  • UNPLANNED
  • PLANNING IN PROGRESS
  • PLANNED
  • LOAD CRATED
  • CARRIER ASSIGNED
  • VEHICLE ASSIGNED
  • YMS STATUSES
  • OUT FOR DELIVERY
  • DELIVERED
  • FAILED

LOAD STATES

  • PLANNED
  • CARRIER ASSIGNED
  • YMS STATUSES
  • OUT FOR DELIVERY
  • DELIVERED
  • PART-DELIVERED