Imports
The C-TMS Flat File Import functionality allows for data to be loaded into the system without the need for a formal electronic interface. The import mechanism allows a file from a users PC to be uploaded to the server and from there it can be loaded into the database.
There is a predefined set of import types which have been configured along with the fields which can be included in each import. It is possible to configure these imports to suit individual requirements, for example, on an Order import it is possible to pass in the id for an existing location or it is possible to pass in the name and address for a new location (only the first 2 parts of a postcode are required for a new location to be created) with relevant information such as loading rates automatically being copied from existing locations to their orders. Certain imports also have the concept of a record type allowing 2 types of information to be included in one import such as orders and order lines. Order details can be contained on one line and then one or many orders lines can be included in subsequent lines.
Access Control
Access to maintain and execute the imports is subject to access control.
To access the maintenance or execution of imports, your user must be part of a group that has access to the screens. You can do this through the Access Control screen, Groups maintenance, Menu tab for your assigned group.
- Imports (FILE_INTERFACES.IMPORTS) - this is for the execution of imports.
- Imports (MAINTENANCE.IMPORTS_MAINT) - this is for the maintenance of imports.
In order to import in a particular format, you also have to have access to the format, which is controlled through the same screen, Imports tab:
In both cases, access is granted by double-clicking the required menu or import on the lower "Available" table to add it to the upper "Authorised" table.
If you do not have privilege to change access control, contact your system administrator for access.
Import Processing
There are a number of imports that are currently configured including Orders., Bookings, Slots and Receipts.
While processing the file C-TMS will attempt to process each line individually, upon completion it will report back to the user the number of lines that were processed successfully and the number that failed. Results and failures files (.res and .fail) are produced and can be viewed on the server to see what happened during the import. Upon completion a record will also be written to an audit table, detailing when the import was run, who ran it, the number of records processed successfully and the number that failed. If the Import was for orders it will also contain a comma separated string of all the orders that were created.
With regard to the Orders imports it is very important to prevent a file from being processed more than once. C-TMS will perform a check to ensure that the same file cannot be loaded twice, this is achieved using the checksum functionality on the server, if a user attempts to load a file that has already been processed it will be rejected. C-TMS also performs checks to ensure that the order being processed is not already in the system, the check is made based on the key fields of an order.
User Interface
There are 2 screens in C-TMS for imports, one for maintenance and the other for execution as follows:
Maintenance
To create a new import, select a row on the top section and click the Add button.
Enter a unique format name.
Enter an import format or select from a lookup using CTRL-L - a list of currently available imports is shown below.
Enter a default path and filename - this is a default LOCAL path and filename on your PCs or network which is used when executing to find the import files. You can change this when executing an import, but is useful as a default.
You may also enter:
- XFer Type - ASCII (character, the default) or BINARY.
- Max Uploads - a maximum number of records that can be uploaded through this import.
- Default Record Type - a default for the records (lines) included in the file.
- Fixed/Delimited - this will default to delimited if unspecified.
- Delimiter - this will default to a comma if unspecified.
You can save your export using the Save button provided, or cancel any changes you have made after the last save with the Cancel button.
To edit an existing import, you can select it from the top table, and any defined extract fields will be listed below.
To delete an existing import, select the import in the top table and click the Delete button.
To add extract fields, select the lower table and click the Add button. The screen will take you through the adding of a new field.
If there are multiple record (line) types that can be specified in the import, the screen will automatically show you the available record types. You can select one by clicking on it and clicking OK, or by double-clicking on the data row. You can cancel the addition by clicking the Cancel button.
After selecting a record type (or if there is only one record type), the screen will automatically show you a list of fields for that record type. You can select one by clicking on it and clicking OK, or by double-clicking on the data row. You can cancel the addition by clicking the Cancel button.
After that, you can specify where the data for this system field comes from:
- FIXED - a fixed value, typed directly in the Source Value column.
- FIELD - a column of data from the inbound file, indicated by a numeric entry in the Source Value column.
- PARAM - a parameter from the execution screen, identified by the Source Value column.
- OMIT - a placeholder. The field will not be imported or set.
You may also specify the following:
- Occ - occurrence of that field
- Prefix - a prefix to add to the start of the field data
- Pad - How may characters to pad the field, if required. Applies to the left i.e. padding to 3 characters with "0", "1" becomes "001", "17" becomes "017", "12345" becomes "123".
- Char - a padding character for the field when padding.
- Default - a default value if the import field is not provided.
- Format - Available formats are defined in the Formulae tab. If selected, one of:
- COMET - a bespoke format.
- SSL - a bespoke service level format.
- PLUS_X - where X is a number to add to a numeric field value.
- ADD_DAYS_SCHED_OFFSET - an offset to a date, specified in the 4th and 5th parameters.
- DATE_NEXT_SLOT - the date of the next delivery slot, defined by parameters: 2 - secondary location; 3 - principal location, 4 - Product, 5 - RPE
- DAY+1WD - the next working day from the date specified.
- DAYNAME_TO_WKDAYNO - convert a week day number to a human-readable day name.
- LOC_REF_X - Obtain a location ID by an alternative reference. Various reference types are possible, which will be suffixed to the model prefix "LOC_REF_", eg "LOC_REF_EXT", "LOC_REF_WHS".
- PRD_REF_X - Obtain a PRODUCT TYPE by an alternative reference. Various reference types are possible, which will be suffixed to the model prefix "PRD_REF_", "PRD_REF_SECTION_DFLT".
- DU_REF_X - Obtain a DU_TYPE by an alternative reference. Various reference types are possible, which will be suffixed to the model prefix "DU_REF_PRD_DFLT", eg "PRD_REF_SECTION_DFLT".
- Decode - a decode table to lookup a value from the import field value to a system field value. This is covered more in the following section. A lookup list of decodes is provided. If a decode value is not found, the direct import field value is used.
You can add new fields, delete existing fields, edit existing fields and save your changes using the buttons provided.
Note: Typically with each import, there is a record type and action that is required. This is usually specified against the core data record type.
For example, the PERSON import type (for importing drivers and crew) supports the following 3 record type:
- PERSON - the person being imported and its data - the core data.
- CARRIER - the carriers to which this person belongs.
- VEHICLE - any vehicle types that person is capable of using.
The REC_TYPE and ACTION fields are specified against the PERSON record type.
You can use this to your advantage to import multiple record types in a single file.
For example, you might want to import a person, a carrier for that person and some vehicle types that they can use.
Each record might be configured as follows:
- Common:
- Column 1:REC_TYPE, column 2:ACTION
- PERSON
- Column 3:PERSON_ID, Column 4:FORENAME, Column 5:SURNAME
- CARRIER
- Column 3:CARRIER_PERSON_ID, Column 4:CARRIER_ID, Column 5:CARRIER_INACTIVE
- VEHICLE
- Column 3:VEHICLE_PERSON_ID, Column 4:VEHICLE_TYPE
The import file might look like this:
PERSON,A,120,Walker,Jay CARRIER,A,120,CARRIER1,N VEHICLE,A,120,VTYPE1 VEHICLE,A,120,VTYPE2 PERSON,A,121,Golding,Harry CARRIER,A,121,CARRIER1,N
Importing this file would create 2 people, both assigned to carrier 1, with the first person assigned to two vehicle types.
Decodes
Decodes are tables of cross-reference data that you can use to convert import values to system values, to account for differences in import data formats.
For example, you have a delivery type STANDARD. All external data import files provide the data, but one field says "STD" instead of "STANDARD". You could create an import decode to change "STD" to "STANDARD" and reference the decode table here.
There are also many different decodes in the system, and some are specifically required for some functionality to operate, usually required decodes for specific interfaces.
Some examples:
- LOGIX Interface
- Decode Name: LOGIX_DU_TYPES
- Decode Type: DU Types
- Source Value: The CTMS DU Type
- Target Value: The Logix DU Type
- LogiNext Interface
- Decode Name: LOGINEXT_DEL_TYPES
- Decode Type: Reference
- Source Value: The CTMS Delivery Type
- Target Value: The LogiNext Delivery Type
Typically, these will be mentioned in the documentation of the specific screens, interfaces or processes as to which decodes are required for certain bespoke functionality to operate as required, and are not listed in this guide.
You create decodes in the Decode import tab.
You can create a new decode table from the top table by clicking the Add button on the top section.
You must provide:
- A unique name.
- A type, provided in a drop-down list.
You can save using the Save button and delete the decode table with the Delete button.
When a decode table is selected, the decodes are shown in the lower table. You can edit existing decodes or add new ones with the Add button in this section. You can delete decode entries with the Delete button.
You must provide:
- Source value - the value to be changed.
- Target value - the value to change to.
You may also provide a customer and/or carrier to which this decode pertains. This decode will then only be used if the import references this customer and/or carrier.
Note: You can use the import process itself to import decode tables, by creating an import of import type "DECODE".
Execution
When an import is executed the file must be on the server, the system allows the user to upload the file onto the server if it is not already there.
To upload a file onto the server for importing you will need to either accept the default file location for the type of import you are performing or use the Browse button to select a file to upload from your PC or a network location that you have access to:
Once the file is found and selected you should click the Open Button. This will then prompt you to confirm you wish to upload the file onto the system's server:
If you click on Yes the file will be uploaded onto the server, depending on the import file's size you may be presented with an hour glass whilst the upload occurs. When the upload is complete you will be asked if you wish to proceed with the import:
If you click Yes you will then be presented with a confirmation message advising what has happened during the import:
If errors occur during the import as above you will notice the Show Results File button has become active you will be able to click on this to see why the data was rejected:
The Show Failure File will list those records that have failed to upload - this can be downloaded, corrected and then re-uploaded.
Notes on Validation of Imports
The import fields have strict restrictions, such as particular values, formats, lengths, etc.
As any field can be decoded through a decode table entry, this restriction applies to the decoded value.
Similarly, data can be modified through formulae, amending the data uploaded for a particular field. Any fields which apply formulas will have the restrictions applied to the transformed data.
If there is no decode or formula, then the data you attempt to upload must abide by the stated restrictions, otherwise the system will reject the data.
To preserve database integrity and prevent duplicate data being imported, every CSV file has a unique numeric identifier, not visible to the user. This is called the "checksum value".
The system will prevent a file import if the content is identical to a previously uploaded file. For example, if an import is executed and completes ok, then the exact same file is attempted to be uploaded, with which the content remains unchanged from the first import, the following error message is displayed:
In this instance the data will already have been uploaded onto the system.
Currently Available Imports
A variety of imports are available in the C-TMS system, and bespoke imports are developed when required by the business. A current list of the imports available are described below:
| Import | Description |
|---|---|
| ACCOUNT | This import allows users to upload Accounts into C-TMS. This import allows for addition, modification or deletion of account data. |
| ACC_SERVICES | This import allows users to upload Services into C-TMS. This import allows for addition and modification of additional services. |
| ACC_SRV_RTS | This import allows users to upload Account Service Requirements into C-TMS. This import allows addition ONLY. |
| ASSET | This import allows users to upload permanent assets into C-TMS. This allows for creation and modification of permanent assets. |
| BAX_DEL_TYPE | |
| BAX_ROUTE | |
| BOOKING | This import allows users to upload Bookings into C-TMS. This import allows 3 different types: BOOKING, BOOKINGS_ASN and BOOKINGS_MDD. |
| CARRIER | This import allows you to upload Carriers into C-TMS. This allows for addition, modification or deletion of carriers. |
| CARRIER_LANE | This import allows users to upload Carrier Lanes into C-TMS |
| CONTRACT | This import allows users to upload Contracts into C-TMS |
| CURR_USAGE | This import allows users to upload Account Currency Usage records into C-TMS. This import allows for addition or deletion of currency usage data. |
| CUSTOMER | This import allows you to upload Customers into C-TMS |
| CUST_FLEET | This import allows users to upload Customer Fleets into C-TMS. Note that this is specific to Fleet Maintenance users (Bespoke) |
| DEBRIEF | This import allows users to upload basic debrief information against a location on a trip into C-TMS. This includes signatory and arrival/departure date/time, and sets the orders at that location to POD confirmed. |
| DECODE | This import allows users to upload Import/Export Decodes into C-TMS |
| DEL_SCHEDULE | This import allows users to upload into C-TMS |
| DRIVERS | This import allows users to be uploaded Drivers into C-TMS. This allows for addition, modification or deletion of basic driver details. Note: The PERSON import supersedes this import and should be used instead.
|
| DRIVER_SHIFT | This import allows users to allocate Drivers to Shift Patterns into C-TMS. This allows for Addition, modification or deletion of driver shift patterns. |
| DU_TYPE | This import allows users to upload Despatch Unit Types|Despatch Unit Types into C-TMS |
| FIXED_ROUTE | This import allows users to upload Fixed Routes into C-TMS. This allows for addition, modification or deletion of fixed routes, stops and transport modes. |
| GAZ_DHL_ESD | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_MOVIANTO | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_POLARSP | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_UKMAIL | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_UKM_PO | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_UKM_SE | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_AC | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_CF | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_CY | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_DE | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_DP | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_DS | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_FE | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_HA | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_RE | This import allows users to upload Carrier Gazetteers into C-TMS |
| GAZ_YODEL_SE | This import allows users to upload Carrier Gazetteers into C-TMS |
| INVOICE_RQTS | This import allows users to upload Invoice Requirements into C-TMS. This import allows for addition, modification or deletion of account invoice requirements data. |
| LANE | This import allows users to upload Lanes into C-TMS |
| LOCATION | This import allows users to upload Locations into C-TMS. This import allows addition ONLY of locations, constraints, contacts, references and trailer types. |
| LOC_PRD_FACT | This import allows users to upload location product factors into C-TMS |
| LOC_UPDATE | This import allows users to upload updated locations into C-TMS. This import allows update ONLY of basic location address details. |
| LOC_ZONES | This import allows users to upload Location Zones into C-TMS |
| MILK_ROUND | |
| ORD_LINE_ITM | This import allows users to upload Orders into C-TMS. Note that orders can be added, modified or deleted through this interface. |
| PARAGONXDOCK | |
| PAR_DEL_DATE | |
| PAR_TRIP_DTL | |
| PAR_XDOCKS | |
| PERSON | This import allows users to upload Drivers into C-TMS. This import allows for addition, modification or deletion of drivers details (record type PERSON, the core type), carrier assignment (CARRIER) and allowed vehicle types (VEHICLE). |
| PORTAL | This import is used by portal when uploading Orders from Portal. |
| PROCESS_ITEM | This is a bespoke interface for Toyota items. |
| PROC_ITM_FIX | |
| PRODUCT | This import allows users to upload Product information into C-TMS |
| PROD_ITEMS | This import allows users to upload Product Items into C-TMS |
| RECEIPT | This import allows users to upload Receipts into C-TMS |
| REGION_DATA | This import allows users to upload Region mapping into C-TMS. This import allows for addition, modification or deletion of region data. |
| REGION_DEPOT | This import allows users to upload Region assignments to Depot into C-TMS. This import allows for addition, modification or deletion of region depot assignment data. |
| RES_AVAIL | This import allows users to upload Resource Availability exceptions into C-TMS |
| SCHED_RULES | This import allows users to upload Schedule Rules into C-TMS. This import allows for addition and modification of scheduling rules per cost centre, customer, service level and transport mode. |
| SCREENING | This import allows users to upload Customer Screening Charges into C-TMS. Note that this is bespoke functionality |
| SHIFT_PATT | This import allows users to upload Depot Shift Patterns into C-TMS. This import allows for addition, modification or deletion of shift patterns per depot. |
| SKU_ORDER | This import allows users to upload SKU’s into C-TMS |
| SLOT | This import allows users to upload Slot details into C-TMS |
| TEAMS | This import allows users to upload Teams into C-TMS. Note that this is bespoke to Sessions Collections processing only. |
| TI_ORDER | This import allows users to upload Orders into C-TMS. Note that orders can only be added or deleted through this interface, not modified. |
| TRACTOR | This import allows users to upload Tractors into C-TMS. This import allows addition, modification or deletion of tractors and the carrier to which they are assigned. |
| TRAILER | This import allows users to upload Trailers into C-TMS. This import allows for addition, modification or deletion of trailers. |
| TYRES | This import allows users to upload Tyres cross-reference data into C-TMS. Note that this is bespoke for Fleet Maintenance processes only. |
| VEHICLE | This import allows users to upload Vehicles into C-TMS. This import allows addition, modification or deletion of fixed vehicles (linked tractor and trailer) and the carriers to which they are assigned. |
| VOLUMETRICS | This import allows users to upload Product Vehicle volumes data into C-TMS. |
| WMS_PRODUCT | This import allows users to upload WMS Product cross-reference data into C-TMS. Note that this is bespoke and used for Brexit export processes only. |
| XDOCK_PATH | This import allows users to upload XDock_Paths into C-TMS. |
| ZONE_CHGS | This import allows users to upload Location Zone Surcharges into C-TMS. |
Further Configuration
The following System Parameters affect this functionality:
| Parameter | Description | Level |
|---|---|---|
| BKG_INCREMENT | Increment exiting Bookings on import rather than overwriting | SYSTEM |
| BKG_SLOT_INACTIVATE | Controls whether existing Slots are updated to INACTIVE during a Slot Import. | SYSTEM |
| CLIENT_CODE_CHECK | Client code assessment for Location import | SYSTEM |
| CONTINGENCY_IMPORT | Contingency Import setting | COST_CENTRE |
| FIXED_IMPORT | Customer is using fixed order import | CUSTOMER |
| GEO_LOC_IMP_POST_PLAN | Populate the postal and planning regions using rules in country and static data when doing location import | SYSTEM |
| GEO_SLOT_IMPORT_FILE | Name of Slot Import file | SYSTEM |
| GEO_SLOT_IMPORT_PATH | Slot Import path | SYSTEM |
| IMP_BWSC_DEF_CARRIER | Default Carrier for BWSC import for self delivery suppliers | COST_CENTRE |
| IMP_CARRIER_LANE_COUNTRY_CODE | Country code for each cost centre imported on Carrier_Lane - INDUSTRIAL | COST_CENTRE |
| IMP_COL_DATE_REQD | Collection Date must be specified for imports | COST_CENTRE |
| IMP_CREATE_ITEM_ID | Controls if Item identifiers are system generated in CSV | CUSTOMER |
| IMP_FULL_LOC_ADDR_MATCH | Check the addresses match for the location name, address line 1, postcode, country and type for imported orders before creating a new location (Y/N). | SYSTEM |
| IMP_LOC_ID_ONLY | Only validate the location id when importing orders not the address lines | SYSTEM |
| IMP_OVERRIDE_SOURCE_REF | Set source system and control additional functionality in ORD_LINE_ITM CSV order import | SYSTEM |
| IMP_RESET_ORD_WINDOWS | Reset Order windows during Import | COST_CENTRE |
| IMP_RESET_ORD_WINDOWS | Reset Order windows during Import | CUSTOMER |
| IMP_TI_ORDER_CALC_WEIGHTS | CSV Import - Calculate weights from dims(Y=X*Y*Z/6000) | COST_CENTRE |
| IMP_TI_ORDER_CHECK_SMS | CSV Import - Check contact numbers can be used as SMS numbers | COST_CENTRE |
| IMP_TI_ORDER_DEFAULT_DU | CSV Import - Default DU Type Method | COST_CENTRE |
| IMP_TI_ORDER_GEN_ITEMS | CSV Import - Generate order items from Qty | COST_CENTRE |
| IMP_TI_ORDER_GEN_LOC | CSV Import - Location Name generation by Post Code | COST_CENTRE |
| IMP_TI_ORDER_QTY_ADD | CSV Import - Add line qty | COST_CENTRE |
| IMP_TI_ORDER_REVENUE_LIMIT | CSV Import -Pre-call above revenue limit (Numeric Limit).0 means no pre-call | COST_CENTRE |
| IMP_TI_ORD_GEN_LOC_NAME | Send Supplier Collection Messages | CUSTOMER |
| IMP_USE_DEFAULT_DU | Use Default DU Type in import | CUSTOMER |
| IMP_USE_DEFAULT_DU | Use Default DU Type in import | CUSTOMER |
| IMP_USE_GERMAN_DECODE | Controls if location names are decoded from German | SYSTEM |
| OMS_UNIQUE_EXT_REF | Controls if imports can load multiple order lines | CUSTOMER |
| ORD_BOOKING_METHOD | CSV Import - Booking Method -HOME for home delivery, any other value for standard | COST_CENTRE |
| ORD_CHECK_DUPLICATE_ORDERS | For orders where source system is IMPORTS check to see if order is a duplicate - Y or N | SYSTEM |
| PORTAL_ORDERS | Portal Imports available | SYSTEM |
| PRD_ITEM_IMPORT_FILE | Name of Product Item Import file | SYSTEM |
| PRD_ITEM_IMPORT_PATH | Product Item Import path | SYSTEM |
| SCH_SCHED_ORD_IMP_IGGESUND_SCHED_DATE_OVERRIDE | When Importing IGGESUND data, can the sched_date be over-written by the SCH_SCHED_ORD_DERIVE rule? | SYSTEM |
| SCH_SCHED_ORD_IMP_TI_ORDER_SCHED_DATE_OVERRIDE | When Importing TI_ORDER data, can the sched_date be over-written by the SCH_SCHED_ORD_DERIVE rule? | SYSTEM |
| UTL_IMPORT_IGNORE_INVALID_OPERATION | The import process of files will suppress the auditing of messages when a file does not exist on the server to overwrite (Y/N) | SYSTEM |
| ZONE_POSTCODE_CHECK | Does Zone import check the postcode | SYSTEM |
