286729
DHL MTS
Flag Required to Route Export Files
FUNCTIONAL SPECIFICATION - 10.6
- 1.1
Reference: FS 286729 AJ-8ELGEY
Client Requirement
A flag currently exists in CTMS that enables a user to flag a trip to send to Microlise or Isotrak. There is now a requirement to extend this functionality to enable the end user to flag a trip so that the export is sent to Amazon which is the Smart Phone Database host.
The Smart phone application will be piloted within DHL though is aimed at Third Party Hauliers this means that the export will need to be sent to both Microlise and Amazon for the pilot this functionality must be considered in the quote as the future requirement will be to only export to Microlise or Amazon.
NOTE – the timescale of this development will determine if this functionality is developed and used for the pilot, a second RIO has been submitted to ESI as a backup.
To recap on the export rules the flag must manage:
Do not export
Export to Isotrak
Export to Microlise
Export to Amazon
Export to Amazon and Microlise (this is only applicable for the pilot)
Solution
This estimate formulates ideas based on the information acquired and details gleaned from a meeting at DHL Bedford dated 23rd February 2011, the slides produced as a result of the meeting and post meeting discussions with Alistair Jacques and Nick Ebbs.
It was agreed that a phased approach would be taken in delivering an overall solution for the Smartphone functionality. Initially, a pilot of the Smartphone ‘Driver Application’ would be achieved with no CTMS development, utilising the existing Microlise functionality (both inbound and outbound) in Consumer production COLV (CONPRD) and capturing messages under provision of some DHL Link (ESI) rules in order to redirect messages accordingly.
Further to the pilot, to make permanent the Smartphone solution, development changes would be required to CTMS as detailed below. This solution is understood to be specific to the ‘Driver Application’ at this stage.
It was stated that future enhancements may be to consider a Web Service HTTPS transaction method for data/messages from CTMS and ESI; however, this is out of scope for this particular RIO.
This RIO has also been utilised to provide some basic implementation and functional advice in order to assist in understanding the solution requirements to date.
Further to the above requirements, it is understood that there is no longer a need for the ability to send to both Smartphone (Amazon) and Microlise for the same trip.
Development
The existing Trip message functionality for Microlise will be utilised to create new inbound and outbound EDI flows, to be transacted in XML format, using the current latest version of TripOrder XSD. This will primarily be triggered using similar rules as the current Microlise functionality for the outbound messages. Updates for the actual times and order quantities in the inbound messages will be accepted, again mirroring the Microlise functionality.
The configuration to generate outbound files will be based on the Carrier ID and/or Driver, as oppose to the Tractor ID and/or Trailer ID as is currently the case for Microlise.
Smartphone – Outbound File
Two new configurable items will be created to control the activation of the outbound messages. A ‘Smartphone Enabled’ checkbox will be added to the Carrier Maintenance tab on the Resources screen. A ‘Smartphone Enabled’ checkbox will also be added to the Driver Maintenance tab on the Resources screen. This will allow the activation of outbound Smartphone messages wherever a CTMS trip is assigned that particular carrier ID and/or that particular driver.
In order to prevent both Smartphone and Microlise messages being triggered, logic will be added to check configuration and where activation parameters are met for both outbound flows only a Smartphone message will be sent and not a Microlise message – this requires careful operational consideration.
The existing Microlise outbound control system parameters will be duplicated, renamed and will function in an identical fashion:
| SMA_OWNING_DEPOT | Indicates if the Smartphone file is sent with Database or owning depot in the name |
| SMA_SEND_ACCEPTED | Send ACCEPTED message to Smartphone |
The actual message will be added to a queue table to be generated where the following triggers are activated:
- Trip Level :-
- Status changed to EN-ROUTE, DELETED or ACCEPTED (and SMA_SEND_ACCEPTED system registry is Y) : TRP sent to Smartphone for all orders on a trip using a carrier ID or driver (from trip header) that is Smartphone enabled (ignoring any activity at the carriers Hub Location to avoid sending cross-dock data too many times).
- CARRIER_ID, DRIVER_ID or TRACTOR_ID is changed for a trip at status EN-ROUTE or ACCEPTED (and SMA_SEND_ACCEPTED system registry is Y) and with old/new carrier ID or current driver from its trip header enabled for Smartphone then send TRP message.
- Trip Stops :-
- If STOP_NO = 1 is changed for a trip at status EN-ROUTE or ACCEPTED (and SMA_SEND_ACCEPTED system registry is Y) and that has a Carrier ID or driver that is Smartphone enabled then send TRP.
If ARRIVE or DEPART times are changed for a trip at status EN-ROUTE or ACCEPTED (and SMA_SEND_ACCEPTED system registry is Y) and that has a Carrier ID or driver that is Smartphone enabled then send TRP.
- Trip/Order Level :-
- On inserting or deleting orders on trips at status EN-ROUTE or ACCEPTED (and SMA_SEND_ACCEPTED system registry is Y) if the trip header has a Carrier ID or driver that is Smartphone enabled then send TRP.
- Order Line Level :-
- If ACTUAL_DESPATCHED_QUANTITY changed for an order on a trip at status ACCEPTED then send TRP to Smartphone.
This will include one trip per file.
Based on the above triggers, replacement TripOrder files will be generated to cover updates of the changed data.
There is currently the ability to manually send an ‘Enabling message’ from a right-click option in the Trip Manipulation and Trip Planning screens, this would be modified to send a Smartphone message if appropriate based on the Trip carrier ID and/or driver ID assigned.
Suggested outbound polling cycle:
Check for messages to generate every 1 minute.
Suggested Filename:
YYYYMMDDHHMISSII.XML
Suggested configurable folder structure using the following system parameters:
| SMA_OUTBOUND_PATH | Path for outbound Smartphone XML interfaces |
| SMA_OUTBOUND_FAIL | Path for failing outbound Smartphone XML interfaces |
| SMA_OUTBOUND_ARCH | Path for archiving outbound Smartphone XML interfaces |
/webint/COLV/interface/SMA/OUT
/webint/COLV/interface/SMA/OUT/archive
/webint/COLV/interface/SMA/OUT/failures
Suggested FTP control System Parameters
SMA_FTP_DESTINATION_USERNAME
|
Username for FTP of Smartphone Files |
SMA_FTP_DESTINATION_IP_ADDRESS
|
IP for FTP of Smartphone Files |
SMA_FTP_DESTINATION_DIRECTORY
|
Directory for FTP of Smartphone Files |
SMA_FTP_DESTINATION_PORT
|
Port for FTP of Smartphone Files |
SMA_FTP_DESTINATION_PASSWORD
|
Password for FTP of Smartphone Files |
This again will mirror the Microlise functionality and actual dates and times, along with order quantity updates will be received by CTMS. Inbound message will be expected in the same format as current Microlise messages covering the same functions:
STA – Departure date/time from first stop
DEP – Departure date/time from a stop
ARR – Arrival date/time at a stop
END – Arrival date/time at the last stop
SUM – Summary of all trip resource and stop information
DEL – Order quantity debrief at delivery stop
This will include one trip per file. It is also required that the stop sequence is sent as matches that of the outbound file, to ensure the correct trip stop is updated.
Receipt of the actual dates and times on the first stop will automatically set the trip to EN-ROUTE status.
The existing Microlise outbound control system parameters will be duplicated, renamed and will function in an identical fashion:
| SMA_SET_TO_COMP_CONF | Update Trip Status once Smartphone has provided all quantities and times |
Once trip/order information is received it will not be updated by subsequent messages for the same trip/order.
Suggested inbound polling cycle:
Check for messages to process every 1 minute.
Suggested Filenames:
SMA_TMS_TMC_STA_YYYYMMDDHHMI_###.XML
SMA_TMS_TMC_DEP_YYYYMMDDHHMI_###.XML
SMA_TMS_TMC_ARR_YYYYMMDDHHMI_###.XML
SMA_TMS_TMC_END_YYYYMMDDHHMI_###.XML
SMA_TMS_TMC_SUM_YYYYMMDDHHMI_###.XML
SMA_TMS_TMC_DEL_YYYYMMDDHHMI_###.XML
Suggested configurable folder structure using the following system parameters:
| SMA_INBOUND_ARCH | Path for Smartphone XML archiving |
| SMA_INBOUND_FAIL | Path for Smartphone XML failures |
| SMA_INBOUND_PATH | Path for yet to be processed XML files from Smartphone |
/webint/COLV/interface/SMA/IN
/webint/COLV/interface/SMA/IN/archive
/webint/COLV/interface/SMA/IN/failures
Suggested upload control System Parameters
| SMA_LISTING_SCRIPT_NAME | Script name for SMA XML Trip Inbound |
| SMA_INBOUND_LISTING_NAME | Filename for list of files in directory for SMA XML Trip Inbound |
| SMA_INBOUND_IDENTIFIER | Pattern for SMA Trip Inbound XML |
Considerations
- ESI developments are likely to be required transfer/mapping of the new flows, both inbound and outbound.
- This estimate allows for an amount consultancy on implementation advice during UAT of the Smartphone application and message mapping advice.
- Message transaction timings are at liberty of the polling cycles across the interfaces under traditional EDI functionality and FTP transmission protocol.
- It is assumed for this estimate that both inbound and outbound message formats, layout and data (tag) contents to be used by the Smartphone functionality must be identical to current Microlise formats. The modification of the CTMS system and/or the XML document handling framework and/or the XML files themselves is out of scope of this estimate.
- No report or export changes are included in this estimate.
- No changes to the interface errors screen are included in this estimate. You may wish to consider what error tracking methods will be required if any.
Reference
For reference the original Microlise and Isotrak developments were completed against the following development RIO references:
- Microlise XML Outbound GN-7M8VK3 (259080)
- Line Level Integration JB-7NHG8Z (260498)
- Isotrak ATMS Interface (Two Way) NW-7RGU7M (264375)
Scope
This change will be applied to system version 10.6.0 on CONTST and once approved CONPRD.
Set-up
Pre-requisites
Latest version of the XSD, TripOrderv2.15
Menu Structure
‘Unchanged’
Data
There will be 17 new system parameters added to the ADM_SYSTEM_PARAM table, covering the Inbound and Outbound functionality.
Insert into ADM_SYSTEM_PARAM (param_name, value, data_type, max_length, displayed, user_modifiable, description, config_by, config_by_value)
Functional Description
Resources Table and Screen Changes
Two new fields will be added to the database to support the development. ‘SMARTPHONE_ENABLED’ fields will be added to the Carrier and Driver maintenance tables, RES_CARRIER and RES_PERSON.
The field will be a VARCHAR2 field and will be populated from a drop down list. Currently, the only value available to select will be ‘SMARTPHONE’. The field will be displayed in the following screens:
The edit carrier screen above is accessed from the Carrier tab in the Resources Maintenance screen. The new field will be displayed in the carrier header of the form and will require some re alignment of the existing fields and a possible increase in the width of the screen.
The new field will only be available in the EDIT CARRIER screen as there is no space available in the Summary tab(displayed previous). To view or edit the value of the field, users must select the relevant carrier record and then select EDIT.
For drivers, the new field will be available to view in the Driver Summary tab and the Edit Driver screen. As with current maintenance screen functionality, to change the value of the ‘SMARTPHONE_ENABLED’ field the user must select EDIT. In the Edit Driver screen, the new field will be added to the header part of the screen, under Surname.
In the main driver tab, the ID and Job Title fields will be shortened to allow space for the ‘SMARTPHONE_ENABLED’ field. The field will be displayed next to the contact number.
Validation
A SMARTPHONE resource will ALWAYS overrule a MICROLISE resource. From a system perspective, if a trip has been assigned a SMARTPHONE enabled carrier or driver and a MICROLISE enabled Tractor or Trailer a control record will only be created for SMARTPHONE and not for MICROLISE.
If a MICROLISE enabled resource has been assigned without a SMARTPHONE resource and a trigger point is set, then a control record will be created for MICROLISE. The user may then assign a SMARTPHONE enabled driver. Once the status on the trip is changed to trigger a control record, the control record will be created for SMARTPHONE and not MICROLISE.
When determining if a record should be created in the control table, the code currently assess if a trip has MICROLISE enabled resources. The code will be amended to assess if the trip has SMARTPHONE resources assigned, if there are no SMARTPHONE resources, the code will then check for MICROLISE. A control record will only be created for MICROLISE in the event that a SMARTPHONE record is not required.
Outbound File
The existing triggers for generating MICROLISE records at TRIP, STOP, ORDER_LINE ,TRIP-ORDER and ORDER ITEMS level will be amended to generate SMARTPHONE records. If the criteria of the trigger is met, a record will be added to the control table INT_XML_CONTROL, populating the following data:
| Field name | Data |
| INT_XML_SEQ | SEQ_INT_XML.nextval |
| EXTERNAL_
SYSTEM |
‘SMA’ |
| TRIP_SCHED | Trip schedule from SCH_TRIP.SCHED_NAME |
| TRIP_ID | Trip id from SCH_TRIP.TRIP_ID |
| TRIP_STATUS | Trip status from SCH_TRIP.STATUS |
| OMS_SCHED | NULL |
| OMS_REF | NULL |
| OMS_STATUS | NULL |
| CUSTOMER | NULL |
| PROCESSED | ‘N’ |
| EVENT_TYPE | ‘TRP’ |
| FILENAME | TMS_SMA_<database/owning depot>_TRP_YYYYMMDDHHMISSII |
The trigger points for inserting a TRP record into the control table are:
(All based on trips where SMARTPHONE_ENABLED resources have been assigned)
| LEVEL | DATA REQUIREMENTS |
| TRIP | STATUS = EN-ROUTE OR DELETED |
| STATUS = ACCEPTED and SMA_SEND_ACCEPTED =Y | |
| STATUS = EN-ROUTE and the driver or carrier is changed | |
| STATUS = ACCEPTED and SMA_SEND_ACCEPTED =Y and the driver or carrier is changed | |
| STOP | STATUS =EN ROUTE and data for the first stop (where stop no =1 ) is changed |
| STATUS =ACCEPTED and SMA_SEND_ACCEPTED = Y and the data for the first stop (where stop no =1) is changed | |
| STATUS =EN-ROUTE and the arrive or depart times are changed | |
| STATUS =ACCEPTED and SMA_SEND_ACCEPTED=Y and the arrive or depart times are changed | |
| TRIP/ ORDER | STATUS = EN-ROUTE and orders are added or removed from the trip |
| STATUS = ACCEPTED and SMA_SEND_ACCEPTED =Y and orders are added or removed from the trip | |
| ORDER LINE | STATUS = ACCEPTED and ACTUAL_DESPATCHED_QUANTITY on the order line has changed |
| ORDER
ITEMS
|
STATUS = ACCEPTED or EN-ROUTE and quantity delivered or quantity to deliver has changed. |
Enabling Message
In the trip Planning and Manipulation screens, the user is able to select a trip in the tree and right click to display a pop up menu.
One of the menu options is ‘Send Enabling Message’. Currently if this is selected, the trip is checked for Microlise enabled tractor or trailer. If this is true, and there are no TRP records in the control table waiting to be processed for the selected trip, a new record is added to the control table.
The code behind the menu item will be amended to process SMARTPHONE messages. The new code will be added above the existing MICROLISE checks,to ensure that SMARTPHONE is processed first. The SMARTPHONE message will follow the same rules as the MICROLISE, only allowing a new TRP record for the trip to be created if there are no TRP records for the trip waiting in the control table to be processed.
TMS TRP Message to SMARTPHONE
A new database job will run every minute to identify any TRP messages that need sending to SMA and produce a file in the XML format using the system registries for the folder name. SMA records will be identified by the value of the external system being set to ‘SMA’.
In the MICROLISE outbound code, it is possible to consolidate stops and orders, the SMARTPHONE development will be based on the non consolidation code ensuring users receive information on every order and stop to the SMARTPHONE.
The system parameter SMA_OWNING_DEPOT will determine if the owning depot or database is sent in the filename to SMARTPHONE. The file will be sent with the naming convention :
SMARTPHONE Messages into MTS
A new job will run every minute to check if XML documents have been placed in the SMA inbound directory as stated in section 2.3 Data.
The job will call a new flow, created within INT_XML_OUT2 for processing inbound messages received from SMA and updating the information in C-TMS. The new flow will be based on the existing procedure INT_XML_OUT.PROCESS_MIC_TRIP_XML_IN as currently compiled in Consumer Production.
The following information will be established from the system parameters:
- name of the inbound file,
- list of the files to be processed,
- name of the script that will move the files from inbound, to archive, or failure directories,
- the location of the inbound directory,
- the location of the archive directory where successfully processed files are saved and
- the location of the failure directory where files that have failed processing are saved.
Inbound messages will be expected in the same format as MICROLISE and will cover the following event types
STA – Departure date/time from first stop
DEP – Departure date/time from a stop
ARR – Arrival date/time at a stop
END – Arrival date/time at the last stop
SUM – Summary of all trip resource and stop information
DEL – Order quantity debrief at delivery stop
The file name will be checked to see if it has already been processed, if so, an error message will be given in the table INT_XML_TRIP_STOP.
Once these parameters are passed to DP_FILE_HANDLING, and the files have been listed for processing (passed validation as above), the content of the files can be uploaded into the generic area xml_extract, and validation performed.
The file content extraction is performed by a procedure called XML_EXTRACT part of the database package ‘XML’. This is a generic component, part of the OBS XML document handling infrastructure, and does not need to be modified.
Once the inbound file data is fully validated, as part of the PROCESS_ORDER procedure, the data will be applied to the required tables in MTS.
MTS will expect the stop sequence field sent out to SMARTPHONE to be returned in the actuals update preserved. The value in the stop sequence will be used to select the appropriate stop on the trip.
The actual departure date and time update from the first stop will update the MTS trip status to EN-ROUTE.
If actual date and time are captured in MTS for all stops then trip status will be set to COMPLETED.
Once trip/order information is received in MTS any subsequent updates for the same trip/order will be ignored by the interface. The upload will work on a first come first serve basis.
As part of the inbound process, control records will be inserted in the following tables;
INT_XML_TRIP_STOP
INT_XML_ORD_HEADER
INT_XML_ORD_DETAILS
INT_XML_ORD_REASON
Any errors will be recorded in the relevant table, in the VALIDATION_ERROR field and the record status will be set to FAILURE.
If the inbound file is successfully processed, the following tables in C-TMS will be updated, depending on the message type
SCH_TRIP
SCH_TRIP_STOP
SCH_ORDER_LINE
SCH_ORD_NON-CONFORM
SCH_ORD_ITEMS
SCH_ORD_ITEMS_REASONS
Sample XML Inbound file
SMA_TMS_TMC_DEL_20110426144722_###.xml
The source type for MICROLISE is set to TRAK, this will be changed to SMA for SMARTPHONE. When receiving DEL type messages to update the delivered order quantity, the detail type indicates if the order line or order item quantities are updated. Currently for MICROLISE, the detail type is D and the order lines are updated, but this field can be set to S to update the item quantites.
If the detail type is set to D, the delivered quantity at line level is updated and any non-conformance is inserted into SCH_ORD_NON_CONFORM. If the detail type is set to S, the delivered qty at item level is updated and any non-conformance is inserted into the SCH_ORD_ITEMS_REASONS table.
Table Updates Required
Alter table RES_CARRIER
ADD SMARTPHONE_ENABLED VARCHAR2(100);
Alter table RES_PERSON
ADD SMARTPHONE_ENABLED VARCHAR2(100);
References
| EST-286729 AJ-8ELGEY Flag Required to Route Export Files v1.0 | |||
Document History
| FS-286729 AJ-8ELGEY Flag Required to Route Export Files v0.1 | ||||
| FS-286729 AJ-8ELGEY Flag Required to Route Export Files v1.0 | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
