DHL C-TMS

Parcel Carrier Management

FUNCTIONAL SPECIFICATION - 10.7

28/11/11 - 3.1
Reference: FS 290934 NW-8KEMRU

FUNCTIONAL OVERVIEW

Client Requirement

It is assumed that this RIO will be managed in conjunction with the Project Rigel System Requirements Document v1.0 or higher.

Parcel carrier integration including the creation of a standard EDI output to DHL Link of an electronic Carrier Manifest, a hard copy (.pdf or .csv) manifest and labels based on carrier specifications. This also includes the ability to manage carrier tracking numbers and import gazetteer.

Solution

The ‘TripOrder’ XML format will be used for the production of the electronic parcel carrier manifests.

A hard copy will be available in PDF format in the specific format for the carrier.

A new trip error message will be generated, and displayed in a new tab page in the ‘Interface Errors’ screen, should the electronic parcel carrier manifest fail to be transferred to DHL Link so that the users are aware that the manifest has not been sent to the carrier. Therefore, there will also be the ability to re-send the file for the manifest via a button in a new tab page called ‘Carrier Manifest’ of the ‘Interface Errors’ screen.

The existing ‘Default Printer Maintenance’ screen will be used to store the default printer required for each user.

The default printer for the user will then be used automatically when the user requests the automatic printing of the labels via the ‘Pack Confirmation’ EDI message. If the ‘Pack Confirmation’ message contains a printer then its value will be used in preference to the default value stored against the user.

For manually requested re-printed labels, and during manual ‘Pack Confirmation’, the user will be able to specify a printer but will be given the option to accept the user’s default printer setup for the process.

Once planning and packing has been completed and the DU’s labelled (e.g. PARCELS or PALLETS), an end of day manifest will need to be sent to each Parcel Carrier. This will send an electronic copy of the manifest. The trigger point to generate the electronic copy of the carrier manifest should be the changing of the status of the Trip to EN-ROUTE. This should also generate the Despatch Confirmation message from C-TMS to Unison or another WMS.

There is a requirement to be able to delay the transmission of this Despatch Confirmation message to Unison or another WMS for certain transport orders: a new screen will be developed to enable the user to hold the transmission for any transport order on the trip stop.

A new screen called ‘Trip Message Holds’ will be developed for the trip screens to view all orders scheduled on it. There will be a check box against each order that can be manually checked by a user: this will hold the ‘Despatch Confirmation’ message once it is generated. Another button will be available to release the holds.

N.B. There are a number of different label formats required for each carrier of the trip:

Parcelforce (Logos & Barcodes):
- Standard Domestic Label
Domestic Label – Saturday Delivery
Yodel (Logos & Barcodes)
DHL Express TD (Logos & Barcodes)
DHL Own Fleet (Logos & Barcodes)
Polarspeed (Logos & Barcodes)
Movianto (Barcodes)

Each label will be produced from a text file, which includes the appropriate printer commands, which will be printed directly to a remote printer as requested by the packer upon processing of the packing confirmation message. All labels will be printed to ZPL2 standard.

A new import message will be created for the upload of the parcel carrier gazetteer information into C-TMS. (See RIO NW-8KENDW for further details.)

A new maintenance screen for the parcel carrier gazetteer/route codes information will be developed.

The following new functionality will be required in C-TMS to allow integration with these Parcel Carriers:

Routing Tables
Routing Codes
Tracking References***

The Range is provided by the Parcel Carrier and held in C-TMS. A system alert or warning message is required to highlight when a new range is required i.e. only 200 numbers left.

Parcel Carrier tracking ranges can be defined in three ways:

A tracking range covering a shipment
A tracking range covering shipment and a separate tracking range covering DU
A tracking range covering DU only

In the instance where the tracking range is applied at DU level the associated interface should include a shipment reference as well. See system requirement documents Appendix 13.2.

The following information is specific to Yodel and is also required to be held and managed within C-TMS:

Meter Number – Covers all clients in DHL Healthcare – one number
Account Number – Site Specific (Assigned to specific Despatching Location/Depot and/or C-TMS Customers and multiple combinations thereof)
Contract Number – Site Specific (Despatching Location/Depot)
Schedule Number – Client code within account (C-TMS Customer/Warehouse Owner Code)

This information is to be held against the carrier.

This information is used by YODEL to identify particular clients and contracts within DHL and is required to be passed to them with the daily manifest files.

A new tab page will be developed for the carrier to maintain these numbers for combination of location (i.e. despatching depot) and customer.

A Service Table will be required in C-TMS which will drive the labels and manifests; this is one table of seven that is part of the “gazetteer” or Routing Table. Routing Tables are generally updated every three to six months and this should be handled either via a CSV import into C-TMS.

Each carrier will handle this differently as shown:

Yodel – Multiple Files
DHL Express TD – Single File
Polarspeed – Single File
Movianto – Single File
Parcelforce – Single File

Service Types are used by Parcel Carriers to manage their delivery capability and SLAs.

If the Service Type is blank an assumption should be made in DHL Link and the field populated with S24. This should then be mapped to the Parcel Carrier Service Type once scheduled.

Scope

This change will be applied to system version 10.7.0.

SET-UP

Pre-Requisites

Table changes have been applied.

Data

The new reports will be added to the standing data to allow it to be selected from the standard reports form.

Implementation Advice

Database table changes must be applied before the new programs are compiled and installed.

Access to the new tab pages will be controlled for specific user groups.

FUNCTIONAL DESCRIPTION

Trip Message Holds

A new form called ‘Trip Message Holds’ (‘TRIP_MSG_HOLDS.fmb’) will be created and made available via a right-click option in the ‘Trip Manipulation’, ‘Trip Execution’, Trip Overview’, ‘Trip Debrief’ and ‘Trip Planning’ screens, to enable the user to prevent the transmission of the despatch confirmation message to DHL Link for a transport order.

The right-click option called ‘Desp Conf Message Hold’ will be available in the following data blocks:

The parameters passed will be:

Trip ID (SCH_TRIP.TRIP_ID)
Stop Number (SCH_TRIP_STOP.STOP_NO)
‘DESP_CONF’ (message type)

An error message will be issued if the user attempts to call the new screen at a stop that is not loading at the owning depot of the trip.

The ‘Trip Execution’ screen will pass the stop number of the ‘Load Location’.

The ‘Trip Overview’ screen will pass the stop number for the loading activity at the owning depot of the trip when the data is displayed at the trip level and the order level in each tab page.

The ‘Trip Debrief’ screen will pass the stop number of the ‘Load’ location.

If the user accesses the screen via the menu then the trip and stop number may be entered to select the orders on the trip stop; if the screen is called from another trip screen then the trip and stop number will be passed by the calling screen and used automatically to select the orders.

The message type may be selected from a dropdown list which will be ‘Despatch Confirmation’ at present (but the screen could be developed in the future for other messages).

The screen will display the transport orders present on the trip stop at the despatching depot of the trip with a box for each order, when the box is ticked it will prevent transmission of the despatch confirmation message.

For example:

Clicking ‘Release All’ will un-tick all of the ‘Hold’ boxes.
Clicking ‘Hold All’ will tick all of the ‘Hold’ boxes.
Clicking ‘Save’ will save any changes made.
Clicking ‘Close’ will close the screen and return the user to the calling trip screen or menu.

If the ‘Hold’ box is ticked then the transmission of the message will be prevented. (See section 3.13 for further details about the transmission.)

The ‘SCH_ORD’ database table will be changed to include the following column:

The menu structure will be changed so that the new form is available in the ‘Trip Management’ menu.

Access to the new form will be restricted to specific user groups in the ‘User Access Control’ screen by a new function called ‘TRIP_MESSAGE_HOLD’ (i.e. ‘Allow access to the ‘Trip Message Hold’ screen’). The same access controls will be used to activate the right-click option in the other trip screens.

Access to the new form will also be restricted by user group in the ‘Menus’ tab.

Orion Menu

The menus will be changed so that the new form is available in the ‘Trip Management’ menu:

‘Trip Message Holds’ will be added to the menu options.

Default Printer Maintenance

The default printers may be setup for the user in the ‘User Access Control’ screen:

The default printer type will be a dropdown list of values from the existing ‘REP_PRINTER_TYPE’ database table and the expected values will be:

Label
Laser
Matrix

The default printer type and name for the user will be stored on the existing ‘ADM_DFLT_PRINTER’ database table.

The manifest reports setup should not contain a default queue as the user’s default printer will be used.

The function ‘REP.PARAMS’ will be called for the printing of the manifests specifying local printing but not the user as the system username will be used.

The user’s default printer will be obtained for the printer type of the report: therefore, the manifest reports should be setup for printer type ‘Laser’ and the carrier labels should be setup for printer type ‘Label’.

Meter Number

The meter number will be maintained for the cost centre code of the client, a new field called ‘Meter Number’ will be added to the ‘Cost Centres’ tab page of the ‘Customers’ screen:

The ‘REV_COST_CENTRE’ database table will be changed to include the following column:

Location Maintenance

A new field called ‘Collecting Depot’ will be added to the ‘Locations’ maintenance screen to store the code of origin of the depot location.

The new field will be displayed for ‘CROSSDOCK’ and ‘RDC’ location types.

The ‘GEO_LOCATION’ database table will be changed to include the following column:

For example, the collecting depot could be ‘OXF’ although the location exists for a different sortation hub of ‘LUT’.

Despatch Unit Types Maintenance

A new column called ‘DU Category’ will be added to the ‘Despatch Unit Types’ tab page of the ‘Resource Maintenance’ screen and to the ‘Edit’ screen.

The ‘DU Category’ will define the type of despatch unit for the carrier manifests from a dropdown list of values (e.g. ‘Parcel’ or ‘Pallet’).

The ‘RES_DESPATCH_UNIT_TYPE’ database table will be changed to include the following column:

Carrier Maintenance

Carriers

A new tab page called ‘Config’ (i.e. ‘Carrier Configuration’) will be added to the ‘Resource Maintenance’ screen to include extra fields related to the production of labels and manifests.

The schedule number, account number and contract number are stored against the carrier rather than the customer: they are carrier specific but can be applied at the customer level.

Access to the new tab page will be restricted to specific user groups in the ‘User Access Control’ screen.

For example:

Clicking ‘Add’ will insert a new record.
Clicking ‘Save’ will save any changes made and deletes the carrier configuration record.
Clicking ‘Delete’ will delete the carrier configuration record.
Clicking ‘Close’ will return the user to the menus.

The fields will be:

‘Label Format’ (Controls which label format will be used)
‘Manifest Format’ (Controls which physical manifest format will be used)
‘Print Manifest’ (Controls whether the physical manifest will be printed automatically)
‘Vol Factor’ (Stores the volumetric factor for the carrier to convert a weight into a dimensional weight in Kg/m3)
‘Location’ (i.e. the despatching depot of the trip)
‘Customer’ (i.e. the customer of the order)
‘Schedule Number’
‘Account Number’
‘Contract Number’
‘Shipment Tracking Ref’ (Controls which tracking number range will be used at the shipment level and defines destination of lookup on existing data)
‘Unit Tracking Ref’ (Controls which tracking number range will be used at the delivery unit level and defines destination of lookup on existing data)

The ‘RES_CARRIER’ database table will be changed to include the following columns displayed in the second section of the screenshot above:

A new database table called ‘RES_CARRIER_CONFIG’ will be created to store the following columns displayed in the third section of the screenshot above:

The following indexes will be added to the new database table:

Normal Ascending Index 1:

CARRIER_ID
LOCATION_ID
CUSTOMER_ID

Normal Ascending Index 2:

CARRIER_ID
CUSTOMER_ID
LOCATION_ID

The numbers and tracking references may be maintained at the carrier level, at the carrier/location level, at the carrier/customer level or the carrier/location/customer level as required.

In the example above the ‘Unit Tracking Ref’ has been set at the carrier level because a location and a customer have not been set.

The ‘Label Format’ and ‘Manifest Format’ fields will have a list of values based on the new ‘RES_CARRIER_FORMATS’ database table described in section 3.1.2: the ‘Label Format’ must have a type of ‘Label’ and the ‘Manifest Format’ must have a type of ‘Manifest’.

‘Print Manifest’ will be a dropdown list with values of ‘N’ and ‘Y’ available: ‘Y’ will indicate that the physical manifest will be printed to the default printer when the trip is updated to ‘EN-ROUTE’. A ‘NULL’ value will be considered to be ‘N’.

The ‘Schedule Number’, ‘Account Number’ and ‘Contract Number’ will be free text.

The ‘Shipment Tracking Ref’ and ‘Unit Tracking Ref’ fields will have a list of values based on the new ‘RES_CARRIER_TRACKING’ database table described in section 3.5.

The formats are expected to be setup as follows:

N.B. The level at which the tracking numbers will be generated will control whether a despatch unit and/or a shipment label will be produced:

If only a ‘Unit Tracking Ref’ exists then a label will be printed for each despatch unit quantity.

If only a ‘Shipment Tracking Ref’ exists then a label will be printed for each despatch unit quantity in the shipment and a label will be printed for the shipment itself.

If both a ‘Unit Tracking Ref’ and a ‘Shipment Tracking Ref’ exist then a label will be printed for each despatch unit quantity in the shipment and a label will be printed for the shipment itself.

Carrier Printing Formats

A new tab page called ‘Carrier Formats’ will be created in the ‘Resource Maintenance’ screen to maintain the valid label and manifest formats.

A new database table called ‘RES_CARRIER_FORMATS’ will be created to store the following columns:

The new tab page will display fields called ‘Type’ and ‘Format’ as shown in the example below:

Clicking ‘Add’ will insert a new record.
Clicking ‘Save’ will save any changes made and also deletes the route codes for the version number if set in the ‘Delete Version’ column.
Clicking ‘Delete’ will delete the record and associated routing codes.
Clicking ‘Close’ will return the user to the menus.

The ‘Type’ and ‘Format’ fields will be free text to enable any other formats to be used in the future.

The ‘Print Total on Label’ field will control the printing of the total number of labels per shipment/transport order, i.e. ‘of X’ or ‘/X’. There will be a dropdown list with values of ‘N’ and ‘Y’ available: if the total number of labels needs to be printed on the label (if possible for the label format) then ‘Y’ should be entered.

A list of values will be present for the ‘Current Version’ and ‘Delete Version ‘fields based on the corresponding ‘RES_CARRIER_ROUTING’ or ‘CAR_GAZ_VERSION’ database tables.

The user will be able to update the current version of the format (should one exist on the corresponding ‘RES_CARRIER_ROUTING’ or ‘CAR_GAZ_VERSION’ database table) and the activation date will store the system date and time when the record is updated.

It is expected that the label format will correspond to the gazetteer/routing codes and that the appropriate carrier(s) will be assigned the label format.

The user will be able to delete any old gazetteer/routing codes from the corresponding ‘RES_CARRIER_ROUTING’ or ‘Yodel’ database tables. A check will be present to ensure that the deleted version is not the current version number.

It is expected that the deleted versions will be versions that are no longer required.

N.B. The ‘Current Version’ field will contain validation for ‘Yodel’ to ensure that the gazetteer ID entered exists on the new ‘GAZ_YODEL_VERSION’ database table and that the same gazetteer ID exists on the related database tables ‘GAZ_YODEL_ACTIVATE’, ‘GAZ_YODEL_DEST_STAT’, ‘GAZ_YODEL_PRDSERV’, ‘GAZ_YODEL_DEST_EXC’, ‘GAZ_YODEL_REAMUSID’, ‘GAZ_YODEL_SERVICES’, ’GAZ_YODEL_FEATURE’, ‘GAZ_YODEL_HANDLING’, ‘GAZ_YODEL_COUNTRY’ and ‘GAZ_YODEL_FREIGHT’. It will also be a requirement that the activation date present in the ‘GAZ_YODEL_ACTIVATE’ database table is not in the future.

These database tables will include a gazetteer ID obtained from the filename (e.g. ‘SERVICES.102’) so that they may be maintained with the other database tables described above.

See section 3.6 for further information about the import process.

The current version will be used when the gazetteer/routing code information is accessed.

Carrier Service Types

A new tab page called ‘Carrier Services’ will be created in the ‘Resource Maintenance’ screen to maintain the relationship between the service type of the trip stops and the service code of the carriers for printing on the labels.

The ‘PARCELFORCE’ carrier will be setup with service types ‘S09’, ‘S10’, ‘S12’, ‘SND’ and ‘SUP’.

The ‘CARRIER CODE’ column above is the Unison carrier code and it will be mapped by DHL Link to the carrier ID in C-TMS during the order upload.

The new ‘Carrier Services’ tab page will enable the carrier service type of the trip stop to be mapped to the carrier service code for printing on the labels (e.g. ‘S24’ maps to ‘24’):

For example:

A new database table called ‘RES_CARRIER_SERVICES’ will be created to store the following columns:

The following index will be added to the new database table:

Unique Ascending Index 1:

CARRIER_ID
SERVICE_TYPE

Carrier Routing Codes

Maintenance

The new ‘Carrier Routing’ tab page will display records from the new ‘RES_CARRIER_ROUTING’ database table in the ‘Resource Maintenance’ screen:

For example:

A horizontal scrollbar will be present to display the other columns:

Earliest Del Time
Sun
Mon
Tue
Wed
Thu
Fri
Sat

Clicking ‘Add’ will insert a new record.
Clicking ‘Save’ will save any changes made.
Clicking ‘Delete’ will delete the record.
Clicking ‘Close’ will return the user to the menus.

The days of the week will be a tick box which if ticked will have a value of ‘Y’.

The earliest delivery time will be stored as 4 digits, e.g. ‘0730’.

The ‘RES_CARRIER_ROUTING’ database table will be created for the new carrier route code files:

Each of the carrier routing files uploaded (except ‘Yodel’) will write data to the new database table as follows:

Each upload of a route code file for the carrier will be provided with a version number so that the current version may be set.

The version number will be a sequential number for the carrier file.

N.B. It is expected that unused versions will be deleted to limit the number of records stored on the database tables. This will be a process managed by the user and the records will not be deleted automatically.

N.B. It is expected that only the ‘ESD’ format will be uploaded for ‘DHL Express’ and not the ‘TDB’ format.

Activation

The current ‘Yodel’ gazetteer and other carrier routing codes will be maintained in the new ‘Carrier Printing Formats’ tab page of the ‘Resources’ screen: the user will be able to set the current version and delete any old versions.

A check will be in place for when the gazetteer is activated to check that all records are present with the same version number, if they are not then the gazetteer cannot be activated.

Carrier Tracking Numbers

Maintenance

A new maintenance tab page called ‘Carrier Tracking’ will be created in the ‘Resources’ screen to maintain the tracking ranges required for each combination of carrier, client (customer) and level (shipment and/or DU level).

An example of the current tracking number ranges is shown below:

The carrier ID will have a shipment reference and/or a DU reference assigned to it to obtain the next tracking number at the appropriate level.

A new database table called ‘RES_CARRIER_TRACKING’ will be created to store the tracking numbers at the different levels:

‘RES_CARRIER_TRACKING’:

A unique primary key constraint will be created for the following columns:

REF_TYPE

The remaining numbers will be calculated by the deduction of the next number from the end number for each record.

The new ‘Carrier Tracking’ tab page will display records from the new ‘RES_CARRIER_TRACKING’ database table in the ‘Resource Maintenance’ screen:

For example:

A horizontal scrollbar will be be present to display the other columns:

SMS Number 2
SMS Number 3
SMS Number 4

Clicking ‘Add’ will insert a new record.
Clicking ‘Save’ will save the changes made.
Clicking ‘Delete’ will delete the record.
Clicking ‘Close’ will return the user to the menus.

The alert may be sent to an e-mail address plus up to 4 mobile phone numbers as a text message, therefore, at least one address must exist for the alert to be sent.

The check digit will be maintained with the update of the next number because the next number is used to form the check digit.

For example, next number ‘717923521’ will have a check digit of ‘0’ based on the function for the carrier.

There will be check digits formats for ‘Parcelforce’, ‘DHL Express’, ‘Courier’ and ‘Own Fleet’ carrier/customer combinations only:

‘Recycle’ will be a tick box that will indicate that the next number may return automatically to the start of the range when the end of the range has been reached; an alert will still be sent even if the number range may be recycled. A ticked box will have a value of ‘Y’. The default will be ‘N’ for no recycling.

The maintenance tab pages may be used to insert, change and delete records.

Parcelforce Check Digit Algorithm

To calculate a check digit for a ‘Guaranteed’ service consignment number where:

XX is a two character alphabetic prefix

nnnnnn is a 6 digit number

C is the check digit

The procedure is as follows:

Multiply the first digit in the 6 digit number by 4.

Multiply the second digit in the 6 digit number by 2. Multiply the third digit in the 6 digit number by 3. Multiply the fourth digit in the 6 digit number by 5. Multiply the fifth digit in the 6 digit number by 9. Multiply the sixth digit in the 6 digit number by 7.

Add the results together.
Divide the resulting total by 11, note the remainder.
Subtract the remainder from 11.
If the result of (4) is 10, then the check digit is zero.

If the result of (4) is 11, then the check digit is 5. Else the check digit is the result of (4).

Thus, given a 6 digit number of 162738 the check digit calculation is as follows:

1 x 4 = 4.

6 x 2 = 12. 2 x 3 = 6. 7 x 5 = 35. 3 x 9 = 27. 8 x 7 = 56.

4 + 12 + 6 + 35 + 27 + 56 = 140.
140 * 11 = 12 remainder 8.
11 – 8 = 3.
Check digit = 3.

Alerts

An alert message will be generated in an e-mail when the tracking number range is nearing its limit for the calendar year (i.e. when the number of remaining tracking numbers reaches the alert level set).

The recipient e-mail address will be setup for the tracking reference type.

The alert message will have the following format:

‘Tracking reference type X is approaching its limit – please investigate.’

The e-mail will be sent to the e-mail address of the required recipient when the next tracking number used leaves the remaining numbers below the threshold set.

N.B. The method of transfer of messages to be used is described in section 3.6 of the functional specification ‘FS-290930 NW-8KENDB SMS Pre-advice v1.0.doc’.

Processing

When the label is printed the tracking number will be stored at the appropriate level on a new database table called ‘SCH_ORD_TRACKING’:

REF_TYPE’ will indicate the type of tracking reference recorded: ‘S’ for shipment level and ‘D’ for despatch unit level depending on the next number used from the ‘RES_CARRIER_TRACKING’ database table.

‘SHIPMENT_ID’ will indicate the shipment ID generated for the transport order/shipment.
‘CARRIER_ID’ will indicate the carrier for whom the tracking reference was generated.
‘LINE_NO’ will indicate the line of the transport order to link the despatch unit tracking reference with the despatch unit.
‘DU_TYPE’ will indicate the despatch unit type of the despatch unit tracking reference.
‘TRACKING_NO’ will be the actual number used (‘next’ at the time).
‘TRACKING_REF’ will be the full tracking referenced used on the packing label.
‘CARTON_NUMBER’ will be the carton number obtained from the ‘CIPD’ message from Unison WMS, for which the despatch unit tracking numbers will be generated.

If a tracking number is used at the shipment or the despatch unit levels then a record will be written to the new table to store which tracking numbers have been used for which shipments/despatch units. The full tracking reference (a.k.a. air bill number) will also be recorded.

The storage of the carton number on the ‘SCH_ORD_TRACKING’ database table means that a record will need to be written when the ‘CIPD’ message is processed and then the record will be updated when the tracking number is generated for the printing of the label. The tracking number may then be linked to the carton number for the ‘CITD’ message that is sent to Unison WMS to store the tracking number against the carton number.

N.B. If a transport order has been entered manually then a carton number and sales order number will not be known and thus the ‘CARTON_NUMBER’ and ‘SO_REF’ columns on the ‘SCH_ORD_TRACKING’ database table will be blank. Therefore, the columns may be described as ‘nullable’.

Parcel Carrier Gazetteer

Imports - Yodel

A new import type of ‘GAZ_YODEL’ will be introduced for the ‘Import Maintenance’ screen so that the following record type may be setup:

VERSION (Version)

The import of the gazetteer will be run for a single record type for the version and all of the other files will be processed automatically with the version provided that all files are for the same gazetteer ID.

The other record types are listed below although they do not need to be setup in the ‘Import Maintenance’ screen:

ACTIVATE (Activation)
DESTINATION_STATION (Destination Station)
DESTINATION_PRDSERV (Destination Services)
DESTINATION_EXCEPT (Destination Exception)
REAMUSID (Reamusid)
SERVICES (Service Codes)
FEATURE (Feature Codes)
HANDLING (Handling Codes)
COUNTRY (Country Codes)
CONFRDES (Freight Codes)

The format name will be setup as ‘Yodel Carrier Gazetteer’, for example, with an import type of ‘GAZ_YODEL’ and a default filename of ‘VERSION.txt’.

The fields in the files to be uploaded will be delimited by pipes (‘|’) (configurable).

If only the version file were not used then the other pipe-delimited files would be setup as separate record types; and separate import and record types would be introduced, as listed below, for the files that do not contain the gazetteer ID as an item:

SERVICES (Service Codes)
FEATURE (Feature Codes)
HANDLING (Handling Codes)
COUNTRY (Country Codes)
CONFRDES (Freight Codes)

The fields in the above files to be uploaded would be of fixed length.

‘String’ data types will be considered as ‘VARCHAR2’ data types and ‘Float’ data types will be considered as ‘NUMBER’ data types.

All import files processed will add new records on the new database tables created for each aspect of the gazetteer.

The following filenames will be expected and they will be required to be placed by the user into the required directory to be found in the browser:

The files expected will be:

VERSION.txt
ACTIVATE.999
DESTINATION_PRDSERVICE.999
DESTINATION_STATION.999
DESTINATION_EXCEPTION.999
REAMUSID.999
SERVICES.999
FEATURE.999
HANDLING.999
COUNTRY.999
CONFRDES.999

The ‘Import Maintenance’ and ‘Import’ screens will not be changed.

IMP Package

For the gazetteer information, only the file ‘VERSION.txt’ need be imported to set the gazetteer ID; the other files will then be uploaded using the gazetteer ID as the suffix (e.g. ‘ACTIVATE.102’); this will be done by appending the gazetteer ID obtained in the ‘VERSION.txt’ file.

The whole set of files must be uploaded together so that the gazetteer is maintained correctly, the gazetteer ID in the ‘VERSION.txt’ file must match the gazetteer ID contained in the other files as the suffix.

A check will be performed to ensure that the 11 gazetteer files are present in the same directory with the same version number before the upload can proceed: if the whole gazetteer is not present and validated then the upload will be rejected.

The gazetteer ID must be greater than the previous gazetteer IDs stored to ensure that old data is not re-used.

The files with a suffix of ‘999’ will be renamed with a suffix of ‘txt’ prior to upload, for example, ‘ACTIVATE_102.txt’.

The ‘Import’ button in the ‘Import’ screen will call the ‘IMP.IMPORT_SERVER_FILE’ function and pass the directory path and file found.

Function ‘IMP.IMPORT_SERVER_FILE’ will then read and validate the file received.

Changes will be made for the new ‘GAZ_YODEL’ import type.

‘IMPORT_SERVER_FILE’

This function will be changed to check for the new import types and call the new functions.

‘PROCESS_YODEL_GAZ’

A check will be performed at the start of the function to ensure that all of the gazetteer information is present in the same directory with the same gazetteer ID. (N.B. The 6 different filenames will be stored as the record types for the ‘GAZ_YODEL’ import type: VERSION, ACTIVATE, DESTINATION_STATION, DESTINATION_PRDSERV, DESTINATION_EXCEPT, REAMUSID; the other 5 files will be available as separate record types due to their fixed formats.)

If a valid set of gazetteer files is present then each file will be processed after the ‘VERSION.txt’ file within the same import process. Thus there will not be a requirement to import all of the files separately.

Each file will need to be processed consecutively so the file will need to be accessed and read afresh; it will be necessary to rename the files with a ‘txt’ suffix; UNIX commands may be used for this purpose.

The import will only store the data on the new gazetteer tables (external) and will not update automatically the active data for the carrier lanes used to assign carriers and to schedule transport orders/shipments onto trips for the carriers.

A new screen called ‘Carrier Printing Formats’ will be available to perform the update of the new version of the gazetteer as the live data once the activation date has passed. See section 3.5.2 for further information.

‘PROCESS_YODEL_SERVICES’