286734

286734 - DK-8EMEWU/ Carrier Trip Planning

Copyright OBS Logistics © 2011

The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied

FUNCTIONAL OVERVIEW

Client Requirement

Auto planning by lane rules, carrier assignment, shipment well make and brief loads, combined shipments, vessel voyage (ports), allocation (HL) to single haulier.

Solution

Carrier Lane Maintenance:

New tables will be created to store carrier lanes and rates for an active date range:

• RTE_HAULIER_LANE (For the lane ID, region, lane group (to control max loads by carrier), carrier & name, route type collect from and deliver to (i.e. postcode/postal region/planning region/distance), procured type (i.e. DHL or PET), lane type (i.e. REG or DIST), priority, trip action (i.e. Manual Plan, Create Trip, Brief Trip), cost per tonne, maximum trips per lane, tariff id, rate agreement reference and start and end effective dates of lane agreement, inactive flag (i.e. Y/N))
• RTE_HAULIER_LANE_GROUP (For the lane group expected to be organised by region, carrier and maximum trips per day)

These tables will be used to store the lane rules including preferred carrier(s) for the route between the collection and delivery locations, plus the agreed cost per tonne where known.

There will be the ability to rank preferred carriers by order of priority (i.e. ‘1’ is the first choice carrier to fulfil the order movement) for the automatic planning of unscheduled orders onto trips. The carrier suggestions screen will list the carriers from lane rules that match the order(s) and there will be no limitation to the number of matching carriers displayed in the list.

The lane selection logic with best match the lane from and to attributes first on postcode then postal region then planning region and then distance of the order collection and delivery addresses with a wildcard (blank) meaning anywhere and any distance. This selection will be considered in the following sequence postcode to postcode then postcode to postal region then postcode to planning region then postcode to distance breakpoint then postcode to wildcard then postal region to postcode etc. through all permutations.

The trip action field defines whether a trip created via the automatic planning process will just create the trip or also brief the carrier of the trip by updating the status of the trip to ‘ACCEPTED’ and so trigger the production of the driver manifest/trip sheet. Allowed values will be Manual, Create and Brief. (Manual means manually planned trip, create and brief will allow automatic planning of trip; where planning means allocation to carrier).

Carrier Lane Upload:

A new CSV import process will be created to upload carrier lane information into C-TMS. The resulting data upload will then be visible in the carrier lane maintenance screen as described above. The import function will provide insert of new lane data and update of existing records.

Haulier Lane Extract:

A new CSV extract process will be created to extract the carrier lane information from the C-TMS database into a CSV format. This functionality will allow maintenance and amendments to be managed in excel and then re-imported using the CSV upload function mentioned above.

Automatic Planning by Lanes:

Automatic planning of single order full load trips will be provided by reference to the carrier lane data and will be performed when an order is raised via the inbound EDI order upload; the order will be assessed against the carrier lanes setup and if there is a carrier available for the route then a trip will be created with the carrier and the order will be assigned to it; the carrier will then be advised of the work via the automatic briefing of the driver manifest.

The pre-sold carrier lanes will have agreed costs (or lane table rates) in place which will automatically generate the costs for the trip and pass them through to EFX.

Create and Brief Carrier Trips:

The planner will plan unscheduled orders to trips using the carrier lanes data in a new carrier planning screen: this new screen will display the unscheduled orders for specific selection criteria and the planner can then create trips for the orders and brief them to carriers. The intention is that the screen can also be used to query trips already planned to aid visibility of the operation over days and weeks.

The selection criteria will be:

• Mandatory schedule date range (delivery date range)
• Optional planning region from and to (multiple selection values will be allowed)
• Optional postal region from and to (multiple selection values will be allowed)
• Optional postcode from and to
• Optional collection location
• Optional delivery location
• Optional customer reference
• Optional OMS reference
• Optional order status (i.e. UNSCHEDULED or SCHEDULED defaulting to UNSCHEDULED’)
• Optional Carrier (to filter on orders possibly fulfilled by the lane table agreements and rules)
• Optional Carrier Region & Lane Group
• Optional Trip Status
• Optional Trip ID

The orders will display the following information:

• Schedule
• Order status
• Customer Order reference
• OMS Reference
• R (flag to denote if redirect order)
• S (flag to denote if split order)
• Planning region (collect)
• Postal region (collect)
• Collection location
• Name
• Town
• Postcode
• Early collection date and time
• Late collection date and time
• Planning region (deliver)
• Postal region (deliver)
• Delivery location
• Name
• Town
• Postcode
• Early delivery date and time
• Late delivery and time
• Distance
• Weight
• Revenue calculated from customer contract (Openfield)
• Revenue per Tonne

A function will be available to view previous ten orders with the same from and to location and the cost incurred from the appointed carrier for each.

The screen will also be used to display visibility of planned orders so trip and cost information will also be displayed:

• Trip ID
• Trip distance
• Trip status
• Carrier
• Region
• Lane group
• Carrier cost
• Cost per Tonne

Once the orders are displayed, the planner will highlight an order or orders using mouse clicks. Action buttons will be used to allow the planner to process the highlighted orders:

Combine orders – The highlighted orders will be used to reference the lane table rules and a suggestion list of possible carriers including preference, type, cost per tonne, lane type and contact details will be generated. The planner will select a carrier from the list (or lookup an alternative from the full list of known carriers) and vehicle type (trailer type).

The selection can be abandoned in which case the carrier pop up list will close and the orders can then be selected again. Once the planner confirms the carrier and confirms the action a single trip will be created for the selected orders at PLANNED status.

Combine orders and brief - As above combine orders function but the resulting single trip will be created at ACCEPTED status which will automatically generate the driver manifest document and send via the defined method of e-mail or fax.

Create trip - The highlighted order (or orders) will be used to reference the lane table rules and a suggestion list of possible carriers including preference, type, cost per tonne, lane type and contact details will be generated. The suggested carrier will be displayed only if the lane table defines that the carrier can fulfil all the orders selected. The planner will select a carrier from the list (or lookup an alternative from the full list of known carriers) and vehicle type (trailer type). The selection can be abandoned in which case the carrier pop up list will close and the orders can then be selected again. Once the planner confirms the carrier and the action, a single trip for each order will be created for the selected orders at PLANNED status.

Create trip and brief – As above create trip function but the resulting trips (one trip per order) will be created at ACCEPTED status which will automatically generate the driver manifest document and send via the defined method of e-mail or fax.

The trip creation functionality will be developed with the following assumptions:

The schedule of the trip created will be based on the delivery date of the orders planned.

If the capacity of a trip is exceeded, based on weight capacity of vehicle type (trailer type) selected, then an error message will be generated to prevent the creation of an overweight trip.

The maximum number of orders per carrier per day (carrier group) and the maximum number of trips per lane will be assessed for each carrier and validation performed to warn if these constraints are exceeded.

The planning screen will be developed so that orders on PLANNED trips can be selected. A right-click option will allow the planner to brief the haulier assigned to the trip displayed by setting the trip status to ACCEPTED.

The planning screen will allow trips to be created without a carrier and cost at PLANNED status. The carrier and cost will be added later before the trip can be promoted to ACCEPTED status.

The current trip planning screens (‘Trip Planning’, ‘Trip Manipulation’ and ‘Execution’) can also be used as standard functionality to create trips for a group of orders, add orders to existing trips and create separate trips for a group of orders.

It is assumed planning and scheduling for own fleet will be achieved using the same functionality described above.

Change Date Function:

There is a requirement in the operation to be able to amend order collection and delivery dates quickly. The planner will select multiple orders simultaneously in the carrier planning screen described above and amend the collection and/or the delivery date in one function.

This is a general requirement that allows easy manipulation of unscheduled order dates. One operational example relates to orders placed to facilitate dock delivery; a number of orders may be booked for delivery to the docks but the arrival and, therefore, the loading dates of the ship have been estimated; on confirmation of a loading date being received, this will trigger the planning sequence to reschedule the planned delivery dates to dock for respective orders.

A ‘Change Date’ button will be available in the new carrier planning screen to perform this function for unscheduled orders. The user will select orders and then using the change date, a pop up screen will allow the user to enter early collection date and time, late collection date and time, early deliver date and time and late deliver date and time and then save.

The C-TMS trip planning screens provide a function to move planned trips to another schedule day. In this circumstance the dates and times of the planned orders remain unchanged.

Scope

This change will be applied to system version 10.6.0 on INDTST and once approved INDPRD.

Set-Up

Pre-Requisites

1. The new tables called ‘RTE_HAULIER_LANE’ and ‘RTE_HAULIER_LANE_GROUP’ will be required.
2. The new triggers to maintain the created and updated information will be required.
3. The new tab page called ‘Carrier Lanes’ will need access to be granted to the relevant user groups.
4. The new screen called ‘Carrier Trip Planning’ will need access to be granted to the relevant user groups and added to the menu structure.
5. The new sequence number must be setup.
6. The new function to allow user access to the new carrier lane maintenance tab page and the carrier trip planning screen must be setup.
7. The new import and extract details must be setup.

Menu Structure

The new form called ‘HAUL_TRIP_PLAN’ will be added to the ‘Trip Management’ menu within C-TMS.

Data

1. The new tables will be required.
2. The new triggers will be required.
3. The new tab page will be required.
4. The new screen will be required.
5. The new sequence number will be required.
6. The new function will be required.
7. The new import and extract setup will be required.

FUNCTIONAL DESCRIPTION

Carrier Lane Maintenance

A new tab page in the ‘Resource Maintenance’ screen called ‘CARRIER_LANES’ with the title ‘Carrier Lanes’ will be created to view and manipulate the carrier lane data stored on the new tables:

The tab page will contain a table to display the haulier lane details and a sub-screen for the haulier group details that can be called via a button called ‘Group’. An example of the layout of the tab page and sub-screen is shown below:

• Clicking New will allow the user to add a new row in the screen (user function to control access). Greyed if not applicable.
• Clicking Delete will delete the highlighted record (dependent on user level access). Greyed if not applicable.
• Clicking Cancel will not save any changes made.
• Clicking Save will save the changes made.
• Clicking Refresh will select the records for display.
• Clicking Group will display the information for the lane group and carrier for the highlighted row (see screen at section 3.1.2).
• Clicking Update Rates will display a window in which the user can select a carrier and optionally a Tariff ID and adjust the rate by a percentage (see screen at section 3.1.3).
• Clicking Audit will display the audit trail for the changes to the Carrier Lanes (see screen at section 3.1.4).
• Clicking Close will close the form and return the user to the menus.
• A horizontal scrollbar will be available at the bottom of the data columns so that all of the columns may be viewed.
• A vertical scrollbar will be available at the end of the data rows so that all of the rows may be viewed.
• Double Click on the Tariff ID will open the relevant contracts tariff detail screen.

Permission to insert and change carrier lane information will be granted in the access control screens for the user group:

An example of the access control screen is shown below to illustrate where this permission is mainatained for user groups.

New functions will be created to enable the records to be maintained by authorised user groups.

These functions will control whether a user can maintain carrier lanes, plan trips for carrier lanes and change order collection and delivery dates.

The records for the carrier lanes will be selected and queried using the filters:

• Lane ID
• Carrier Region
• Carrier Lane Group
• Carrier
• Collect (PlanningRegion, Postal Region, Postcode, Distance Greater or Distance Less)
• Deliver (PlanningRegion, Postal Region, Postcode, Distance Greater or Distance Less)
• Procured (DHL or PET)
• Lane Type (REG or DIST)
• Historic (defaults to N so current effective selected)
• Inactive (defaults to N so active selected)

Each filter will allow a list of values to be entered which can be displayed via the use of ‘%’ in the field or by pressing a button positioned directly to the right of the field.

No records will be displayed on initail entry into the screen as the user is expected to enter filters first. If the user uses the keyboard to navigate in the filters in the screen the fields entered will be in the following sequence:

• Lane ID
• Carrier Region
• Carrier Lane Group
• Carrier
• Collect
• Deliver
• Procured
• Lane Type
• Historic
• Inactive
• Refresh (Button)

If the user uses the keyboard to navigate in the haulier lane rows then each column will be entered in turn from left to right.

The user will require access to the ‘New’ and ‘Delete’ buttons and they will be inactive (i.e. greyed out) should the user not have permission to insert or change data in this screen. If the user presses the ‘Save’ or ‘Cancel’ buttons in the screen then the data will be refreshed automatically.

Validation will be performed to ensure that the haulier lane record is unique, the unique record will be:

• Carrier Region
• Carrier Lane Group
• Carrier
• Country Collect
• Collect Type
• Collect
• Country Deliver
• Deliver Type
• Deliver
• Start Date
• End Date
• Tariff_ID

This will allow more than one record to exist but for different date ranges.

The ‘Lane ID’ will be generated automatically for each record using sequence ‘SEQ_RTE_HAULIER_LANE’.

The data displayed on screen is described as follows with the corresponding database column in which the data will be stored and retrieved (from the database table ‘RTE_HAULIER_LANE’ unless otherwise stated):

The records will be validated during entry:

• The carrier must exist as a carrier in the ‘Carriers’ tab page of the ‘Resource Maintenance’ screen (i.e. ‘RES_CARRIER.CARRIER_ID’)
• The country codes must exist in the ‘Country Code’ screen (i.e. ‘GEO_COUNTRY.COUNTRY_CODE’)
• The collect and deliver types will be a dropdown list of ‘Postcode’, ‘Postal Region’, ‘Planning Region’, ‘Distance Greater’ and ‘Distance Less’ and will be mapped to values ‘PC’, ‘PR’, ‘PL’, ‘DG’ and ‘DL’ respectively
• Collect and deliver will correspond to the type:
  • Postcodes must exist for a location (i.e. ‘GEO_LOCATION.POSTCODE’)
  • Postal Regions must exist for the country code of the lane in the ‘Region Data’ tab page of the ‘Static Data Maintenance’ screen (i.e. ‘GEO_PLAN_REGION.POSTAL_REGION’)
  • Planning Regions must exist for the country code of the lane in the ‘Region Data’ tab page of the ‘Static Data Maintenance’ screen (i.e. ‘GEO_PLAN_REGION.PLANNING_REGION’)
  • Distance Greater and Distance Less must be numeric values
• The procured value will be a dropdown list of ‘DHL’ or ‘PET’
• The lane type will be a dropdown list of ‘REG’ or ‘DIST’
• The priority will be a number and ‘1’ will represent the highest priority
• The action will be a dropdown list of ‘Manual Plan’, ‘Create Trip’ or ‘Brief Trip’ and will be mapped to values ‘M’, ‘C’ and ‘B’ respectively:
  • ‘Manual Plan’ indicates that the lane will not be used to automatic trip plan
  • ‘Create Trip’ indicates that the lane will be used for trip planning but the carrier will NOT be briefed should a trip be created
  • ‘Brief Trip’ indicates that the lane will be used for trip planning but the carrier will be briefed should a trip be created by updating the status of the trip to ‘ACCEPTED’ and so trigger the production of the driver manifest/trip sheet
• The cost per tonne will be the cost to the carrier in currency but may be left blank
• The maximum number of trips per lane that a carrier can accept per day must be a positive number
• The tariff ID will be an existing tariff from a matching carrier contract.
• The rate agreement will be a free format reference
• The start date will be in format ‘DD/MM/YYYY’ and must be valid
• The end date will be optional and in format ‘DD/MM/YYYY’ and if entered must be valid and must not be before the start date
• The inactive flag will be a tick box which if ticked will set the flag to ‘Y’

The lane selection logic will best match the lane ‘collect from’ and ‘deliver to’ attributes first on postcode, then postal region, then planning region of the order collection location and delivery addresses and then distance of the order. This selection will be considered in the following sequence: postcode to postcode; postcode to postal region; postcode to planning region; postcode to distance less breakpoint; postcode to distance greater breakpoint then postal region to postcode, etc through all permutations of postcode, postal region planning region and distance breakpoints.

This logic will allow for the most specific data to be applied first, i.e. postcode, postal region, planning region then distance breakpoint.

Carrier Lane Groups

The ‘Group’ button will call the screen below in a different window and canvas:

• Clicking New will allow the user to add a new row in the screen (user function to control access). Greyed if not applicable.
• Clicking Delete will delete the highlighted record (dependent on user level access). Greyed if not applicable.
• Clicking Cancel will not save any changes made.
• Clicking Save will save the changes made.
• Clicking Refresh will select the records for display.
• Clicking Close will close the screen and return the user to the Carrier Lanes screen.
• A vertical scrollbar will be available at the end of the data rows so that all of the rows may be viewed.

If the ‘Carrier Lane Groups’ sub-screen has been called with a carrier lane record highlighted then the carrier lane group will be found automatically and the filters populated.

The user will require access to the ‘New’ and ‘Delete’ buttons and they will be inactive (i.e. greyed out) should the user not have permission to insert or change data in this screen.

The users with access to the tab page will be able to insert, update and delete carrier lanes and update carrier lane groups. If a new carrier lane is inserted then the carrier lane group will be inserted too with a default number of maximum trips per day of ‘9999’.

A unique carrier lane can be defined as the combination of the types of collection and delivery locations (postcodes, postal regions, planning regions or distance breakpoints).

When the tab page is opened the ‘Carrier Lanes’ window and canvas will be displayed with all records stored on the ‘RTE_HAULIER_LANES’ table.

The filters at the top of the screen will be used to select specific records.

The data displayed on screen is described as follows with the corresponding database column in which the data will be stored and retrieved (from datebase table ‘RTE_HAULIER_LANE_GROUP’):

The records will be validated during entry:

• The lane group must exist as a carrier lane (i.e. ‘RTE_HAULIER_LANE.LANE_GROUP’)
• The carrier must exist as a carrier in the ‘Carriers’ tab page of the ‘Resource Maintenance’ screen (i.e. ‘RES_CARRIER.CARRIER_ID’) and in combination with the lane group as a carrier lane
• The maximum number of trips per lane group that a carrier can accept per day must be a positive number

Update Rate Agreement

The ‘Update Rates’ button will call the screen below in a different window and canvas:

• Clicking Cancel will not save any changes made.
• Clicking Save will save the changes made.
• Clicking Close will close the screen and return the user to the Haulier Lanes screen.

A valid carrier and optionally a carrier region and lane group and/or a rate agreement and/or a tariff Id will be entered supported by a list of values lookup, and a percentage entered for which the cost per tonne of the carrier lane records will be calculated.

The percentage entered can be a positive or a negative number.

The update rates function will always create new records set with the entered effective date start and end. The current most recent effective date start lane record will be used as input to the function.

If a date range would overlap then the existing record will be updated to end on the day before the start date of the new record, this will avoid concurrent rates being created.

The update rate function will apply to the carrier lane records if a tariff id is not entered and to the contract tariff id is a tariff id is entered.

Carrier Lane Audit

The ‘Audit’ button will call the screen below in a different window and canvas for the carrier lane highlighted:

• Clicking Close will close the screen and return the user to the Carrier Lanes screen.
• A vertical scrollbar will be available at the end of the data rows so that all of the rows may be viewed.

An audit record will be created when a carrier lane is created, updated or deleted via a database trigger on the new ‘RTE_HAULIER_LANE’ table.

The trigger will write to the new ‘RTE_HAULIER_LANE_AUDIT’ table with the change(s) made recorded.

An audit ID will be generated automatically from a database sequence number.

The audit screen will display the audit records in reverse chronological sequence so the most recent change first.

Tables

New database tables will be used to store the data displayed in the new tab page and sub-screen as described in the tables below:

RTE_HAULIER_LANE:

RTE_HAULIER_LANE_GROUP:

RTE_HAULIER_LANE_ORDERS:

RTE_HAULIER_LANE_AUDIT:

Carrier Lane Upload=

Import Maintenance

A new CSV import function will be created to upload carrier lane information into C-TMS. The resulting data uploads will then be visible in the carrier lane maintenance screen as described above. The import function will provide insert of new lane data and update of existing records.

The new import will be setup in the ‘Import Maintenance’ screen for the following values:

Header Level (‘IMP_RECORD_FORMAT’):

Detail Level (‘IMP_FIELD_SOURCE’):

The ‘IDENTIFIER’ will be the activity to be performed, i.e. ‘I’ to insert a record, ‘U’ to update a record or ‘D’ to delete a record.

The import type ‘CARRIER_LANE’ will be inserted into the ‘IMP_IMPORT_TYPES’ table so that it can be used in the configuration of the import.

The user group will need authorised access for the import type to process a file. An example of how this authorisation is maintained is shown below;

Validation will be performed to ensure that the insertion of a carrier lane will not create a duplicate record and that the record is found to update or delete.

If a haulier lane is inserted without lane group then a default record will be created with the maximum number of trips per day of ‘9999’.

The created and updated columns on the tables ‘RTE_HAULIER_LANE’ and ‘RTE_HAULIER_LANE_GROUP’ will be set as required via the triggers on those tables.

IMP Package

The ‘IMP’ package will be changed to process the new ‘CARRIER_LANE’ type in function ‘IMPORT_SERVER_FILE’ for which a new function called ‘PROCESS_CARRIER_LANE’ will be created to extract the items in the file and upload the data into the carrier lane and group tables.

The record type will determine into which table the data is inserted, updated or deleted based on the value in the ‘IDENTIFIER’ field.

The fields will need to be validated as follows:

LANE

GROUP

Once the data has been validated it will be inserted, updated or deleted as required. The update action will create a new carrier lane record in place of the original which will be closed by its effective END_DATE.

Haulier Lane Extract

A new CSV extract process will be created to extract the carrier lane information from the C-TMS database into a CSV format. This functionality will allow maintenance and amendments to be managed in excel and then re-imported using the CSV upload function mentioned above. The output will be configured to be of the same format expected by the import described above. The generic C-TMS reporting suite function will be used to obtain the extracts.

The screens and description below illustrates the method that this will be achieved using for example an extract of data across orders and trips and stops.

The file produced in the directory indicated for the example above looks like:

1. more ORS_MTS_OWNER_110412090457.csv

Schedule,Trip ID,Trip Status,Stop Location,Stop No.,Order Ref

050812,=t("MAN-00002162"),=t("CONFIRMED"),=t("EXELLUTT"),1,=t("3349")

050812,=t("MAN-00002162"),=t("CONFIRMED"),=t("RAMAHAYD"),2,=t("3349")

050812,=t("MAN-00002162"),=t("CONFIRMED"),=t("EXELLUTT"),3,=t("")

050812,=t("MAN-00002163"),=t("CONFIRMED"),=t("EXELLUTT"),1,=t("3346")

050812,=t("MAN-00002163"),=t("CONFIRMED"),=t("BAYLGOOL"),2,=t("3346")

050812,=t("MAN-00002163"),=t("CONFIRMED"),=t("EXELLUTT"),3,=t("")

050812,=t("MAN-00002164"),=t("CONFIRMED"),=t("EXELLUTT"),1,=t("3345")

050812,=t("MAN-00002164"),=t("CONFIRMED"),=t("BAYLWARR"),2,=t("3345")

050812,=t("MAN-00002164"),=t("CONFIRMED"),=t("EXELLUTT"),3,=t("")

050812,=t("MAN-00002166"),=t("CONFIRMED"),=t("EXELLUTT"),1,=t("3370")

050812,=t("MAN-00002166"),=t("CONFIRMED"),=t("TESTYORK"),3,=t("3351")

050812,=t("MAN-00002166"),=t("CONFIRMED"),=t("NISASCUN"),2,=t("3370")

050812,=t("MAN-00002166"),=t("CONFIRMED"),=t("EXELLUTT"),4,=t("3351")

And then once uploaded into a spreadsheet:

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-259410 PA-7MKDYG CN Loading Schedule Report Changes v1.doc	1	06/01/10

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
1a	12/01/10	Draft	Initial version	PDR
1	12/01/10	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager