Analysis of Existing Booking CSV files
In the existing Smart Hub system, the current client Room has to drag and drop CSV files onto a website which contains ‘inbound’ or ‘outbound’ bookings. The existing CSV columns are shown in the following tables with an explanation of each column.
INBOUND FORMAT
Field | Example Value | Explanation |
|---|---|---|
ref | testabc | Their reference (their 'Transfer Order') |
mode | pre-alert | mode of transport (road/sea etc), but 'pre-alert' means no transport i.e. ASN |
service_level | standard | not used, was intended to try to identify priority etc |
consignor_name | jasper group | collection location name (jasper are the manufacturer) |
consignor_address_line_1 |
| consignor' is really a term used here to mean collection / origin of goods |
consignor_address_line_2 |
|
|
consignor_town_city |
|
|
consignor_state_region |
|
|
consignor_zip_post_code |
|
|
consignor_country | united states |
|
consignor_contact_name | dave |
|
consignor_contact_email |
| |
consignor_contact_number | 12345 |
|
consignee_name | seko sacramento | consignee is really a term used here to mean delivery / destination of goods |
consignee_address_line_1 | 3940 Seaport Blvd |
|
consignee_address_line_2 |
|
|
consignee_town_city | sacramento |
|
consignee_state_region | california |
|
consignee_zip_post_code | 95691 |
|
consignee_country | united states |
|
consignee_contact_name | nate |
|
consignee_contact_email |
| |
consignee_contact_number | 98765 |
|
col_date | 20210101 | date to be collected by |
del_req_by | 20210201 | delivery required by |
sku | PR22WOWOSJ0 | these product SKUs will always be at 'parent' level, but they won't represent customer SKU products, only component parts |
sku_qty | 1 | sku / qty will typically repeat for each component SKU being shipped to warehouse |
sku_condition | new | new or used (the latter for returns) |
allocated_order |
| not used at the moment |
OUTBOUND FORMAT
Field | Example Value | Explanation |
|---|---|---|
line_id | 1 | references the line ID from their end customer sales order / invoice. We currently require them to have a separate CSV record for each line item associated with same invoice number (next field) |
invoice_number | test1207 | Client's invoice # to their end customer - associated with their Sales Order |
order_type | Sales Order | majority of time it's this - but occasional 'return' / 'event' marketing (future only) |
carrier | SEKO | if not SEKO, outbound release only and it will state another carrier to handover to |
regional_warehouse | SEKO SMF | the warehouse from which goods emanate, but actually we should decide |
assembly | N | white glove or not? |
account_name | Joe Thomas | Delivery name - account who placed the Sales order |
consignee_add1 | 5555 Saint Louis Mills Blvd, Ste 119 | Where is it being delivered; again consignee is the term used for where the goods are going, not necessarily the consignee of record |
consignee_add2 |
|
|
consignee_city | Hazelwood |
|
consignee_state | MO |
|
consignee_country | United States |
|
consignee_zip | 63042 |
|
consignee_contact |
| specific contacts |
consignee_number |
|
|
consignee_email |
|
|
deliver_by | 03/09/2021 | required date to deliver by |
delivery_type | Ship to Customer | ship to customer' or 'ship to warehouse'; this field is not really used |
quantity | 1 | Number of parent SKUs e.g. full booths; could be 2 if both booths identical from the sales order |
item | PR20DOA1SJ0 | parent SKU e.g "one booth" |
child_sku_1 | PR21DOA1SJ0 | component SKU of the above booth |
child_sku_2 | PR22DOWOSJ0 | component SKU of the above booth |
child_sku_3 | PR23DOWOSJ0 | component SKU of the above booth |
child_sku_4 | PR23DOWOSJ0 | component SKU of the above booth |
child_sku_5 | PR24DOA1SJ0 | component SKU of the above booth |
child_sku_6 | PR25DOWOSJ0 | component SKU of the above booth |
child_sku_7 |
|
|
child_sku_8 |
|
|
child_sku_9 |
|
|
child_sku_10 |
|
|
child_sku_11 |
|
|
child_sku_12 |
|
|
child_sku_13 |
|
|
child_sku_14 |
|
|
child_sku_15 |
|
|
child_sku_16 |
|
|
child_sku_17 |
|
|
child_sku_18 |
|
|
child_sku_19 |
|
|
child_sku_20 |
|
|
child_sku_21 |
|
|
child_sku_22 |
|
|
child_sku_23 |
|
|
preferred_delivery_window |
| here below, is mostly ignored |
coi | F | only needed in States, certain insurance certificate required |
coi_sample_link |
|
|
union_delivery | F | sometimes the installer has to be a union member in NY apparently... |
union_assembly | F |
|
union_debris | F |
|
freight_elevator | F | These fields and below typically get found out later anyway through site surveys etc |
can_freight_elevator_fit_the_boxes | F | ditto |
passenger_elevator | F | ditto |
can_passenger_elevator_fit_the_boxes | F | ditto |
loading_dock | F | ditto |
loading_dock_address |
| ditto |
delivery_floor |
| ditto |
flights_of_stairs |
| ditto |
comments |
| free form stuff, gubbins |
shipping_instruction |
| e.g. "make sure it's picked / packed in container X" etc |
Target API - Requirements Analysis
The following table shows the main data elements that need to be conveyed in any booking. This takes the existing knowledge from Voyager end-to-end transport bookings for road freight, and extends it to cover inbound to a warehouse, outbound from a warehouse, and various handover scenarios.
It also covers the concept of a booking which represents an ASN from the client i.e. “we are arranging our own transport, but these Goods are going to arrive at your warehouse on date X”.
Booking Type why this booking? | ASN | GOODS TRANSFER | SALES ORDER |
|---|---|---|---|
Organisations of Record Official Who? Consignor / Consignee of record | OPTIONAL if missing, we must enrich with Consignor, Consignee and also Responsible SEKO Entity | OPTIONAL if missing, we must enrich with Consignor, Consignee and also Responsible SEKO Entity | OPTIONAL if missing, we must enrich with Consignor, Consignee and also Responsible SEKO Entity |
Goods what are the goods? | MANDATORY No children | MANDATORY No children | MANDATORY Children may be specified. In the absence of children, we should have logic to decide how to construct the parent SKU based on pre-approved combinations of child SKUs from the Client |
Collection Location from where? | NOT APPLICABLE | OPTIONAL if missing, we must enrich with normal factory premises for this client, if configured | OPTIONAL if missing, we will decide which of our warehouses when planning workflow, based on available inventory & destination. We may need to create more than one workflow depending on where the inventory is, to fulfil the order |
Collection Time when goods collected? | NOT APPLICABLE | MANDATORY | OPTIONAL if missing, we decide when we plan the workflow; however if they require collection directly from their factory then they will likely tell us |
Delivery Location to where? | OPTIONAL if missing, we must enrich with normal inbound warehouse for this client, if configured | OPTIONAL if missing, we must enrich with normal inbound warehouse for this client, if configured | MANDATORY client may optionally define an additional destination which is the final destination, but this is only valid if there is a Handover To Agent |
Delivery Window when goods delivered? | MANDATORY | MANDATORY | MANDATORY |
Receive From Agent handover from who? | MANDATORY | OPTIONAL if missing, there is no separate agent we are receiving from at origin | OPTIONAL if missing, there is no separate agent we are receiving from at origin |
Handover To Agent handover to who? | NOT APPLICABLE | OPTIONAL if missing, there is no separate agent we are handing over to at destination | OPTIONAL if missing, there is no separate agent we are handing over to at destination |
Transport How? (Mode, Containers etc) | MANDATORY | OPTIONAL if missing, we decide how to transport (and if present, we may still decide to do it differently when setting up workflow) | OPTIONAL if missing, we decide how to transport (and if present, we may still decide to do it differently when setting up workflow) |
White Glove installation required? | NOT APPLICABLE | NOT APPLICABLE | OPTIONAL if missing, there is no White Glove service applicable or required to be managed by us. Must not be present if there is a Handover To Agent |
Notes
|
|
|
|