285418
DHL MTS
New Message Flow from C-TMS
FUNCTIONAL SPECIFICATION - 10.6
18/02/2011 - 1.2
Reference: FS 285418 MA-8D9L9J
Client Requirement
Change Request Summary:
Produce a new message flow from C-MTS in the Unison message format specified in RIO JF-85NC2Y (OBS 277256) where the OWNING_DEPOT = "EXELBAWT" FTP messages to ESI location.
Change Request Details:
Produce a new message flow from C-MTS in the Unison message format specified in RIO JF-85NC2Y (OBS 277256
Pre-Allocation Report) where the OWNING_DEPOT = "EXELBAWT". FTP messages to ESI location TBC.The messages are to be produced in the format that Unison expects to receive.The message triggers from C-MTS are expected to be as follows:
- CREATE - Trip Creation
- ADD - Orders Added to the Trip.
- REMOVE - Orders Removed from the Trip
- ALL REMOVED - All Orders Removed from the Trip
- DELETE - Orders Deleted or Trip Deleted
- In all these instances the message to be produced for the trip specifying the orders or no orders on the trip. - In the instance where all orders are removed from the trip or the trip is deleted it is expected that C-MTS sends the header "T001" segment for that trip with the date and time elements populated with current system date and current system time.
Benefits identified as a result of the change:
Client requirement.
Solution
A new outbound message will be created and sent to ESI to transfer to Unison as specified in RIO ‘JF-85NC2Y’:
| T001 Record | |||
| Start | Length | Element | Notes |
| 1 | 4 | Record ID | “T001” |
| 5 | 20 | Trip ID | |
| 25 | 8 | Departure Date | Format YYYYMMDD |
| 33 | 4 | Departure Time | Format HH24:MI |
| O001 Record | |||
| Start | Length | Element | Notes |
| 1 | 4 | Record ID | “O001” |
| 5 | 3 | Order Owner | |
| 8 | 20 | Order Number | |
| 28 | 3 | Drop Sequence | Numeric |
The new message will be created as a fixed-length file in the format shown in the table above but only for trips with an owning depot of ‘EXELBAWT’. The ‘T001’ record will be at the trip level and the ‘O001’ records at the order level’. N.B. There may be multiple trips included in the same file.
The new message will be created via triggers based on the actions performed on the trip:
- CREATE - Trip Creation
- ADD - Orders Added to the Trip.
- REMOVE - Orders Removed from the Trip
- ALL REMOVED - All Orders Removed from the Trip
- DELETE - Orders Deleted or Trip Deleted
N.B. A message will only be generated for point 1 if the trip is created with at least one order assigned because it will prevent an unnecessary upload into Unison.
To generate the file, the triggers will write a control record for the schedule of the trip, trip, status (i.e. ‘NEW’) and message type (e.g. ‘OUTBOUND_TRIP_DTL’ on table ‘INT_TRIP_DTL’); once the record is being processed the status will be updated to ‘PROCESSING’ and once completed to ‘SUCCESS’; if a record exists already with the same values at status ‘NEW’ then a new record will not be written to the table as the up-to-date details of the trip will be obtained when the record is processed via a database job (e.g. to run ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’)
The expected departure date and time of the trip will be included; however, if all of the orders have been deleted from the trip, or the trip has been deleted, then the current system date and time will be included. The ‘Drop Sequence’ will be considered to be the ‘Stop Number’ of the order on the trip.
Scope
This change will be applied to system version 10.6
Set-up
Pre-requisites
- The new EDI flows and the directory structure for the new files are required.
- A new index on table ‘INT_TRIP_DTL’ is required.
- A new sequence number will be required for the new filename.
Menu Structure
Unchanged
Data
The new EDI flows and system parameters will need to be setup.
Functional Description
System Parameters
A new system parameter called ‘CTMS_OUTBOUND_TRIP_PATH’ will be created to store the directory for the outbound trip files generated; if the directory path is new then the directory will need to be created.
For example, ‘/webint/conprd/interface/out’.
A new system parameter called ‘CTMS_OUTBOUND_TRIP_FILENAME’ will be created to store the filename of the trip files generated.
For example, ‘CTMS_EXELBAWT_TRIP_DTL’.
A new system parameter called ‘CTMS_OUTBOUND_TRIP_FLAG’ will be created to store a value of ‘Y’ to permit the outbound trip files to be created or ‘N’ if not.
A new system parameter called ‘CTMS_OUTBOUND_TRIP_OWNING_DEPOT’ will be created to store the owning depot of the outbound trip.
For example, ‘EXELBAWT’.
A new system parameter called ‘CTMS_OUTBOUND_TRIP_MSG_TYPE’ will be created to store the message type required for the outbound trip file.
For example, ‘EXELBAWT_TRIP_DTL’.
New system parameters will be created to store the FTP information:
‘CTMS_ESI_FTP_DESTINATION_IP_ADDRESS’
‘CTMS_ESI_FTP_DESTINATION_PORT’
‘CTMS_ESI_FTP_DESTINATION_DIRECTORY’
‘CTMS_ESI_FTP_DESTINATION_USERNAME’
‘CTMS_ESI_FTP_DESTINATION_PASSWORD’
Trip Triggers
The triggers will only be active if the owning depot of the trip is that of the system parameter ‘CTMS_OUTBOUND_TRIP_OWNING_DEPOT’.
The new message will be created via triggers based on the actions performed on the trip:
- CREATE - Trip Creation
- ADD - Orders Added to the Trip.
- REMOVE - Orders Removed from the Trip
- ALL REMOVED - All Orders Removed from the Trip
- DELETE - Orders Deleted or Trip Deleted
N.B. A message will only be generated for point 1 if the trip is created with at least one Unison sales order assigned because it will prevent an unnecessary upload into Unison, however, it is not expected that such a situation can arise.
A Unison sales order will be identified as such if the ‘UNISON_IF_VALUE’ of the customer is populated (i.e. with an order owner from Unison) and the location of the customer matches the new system parameter called ‘CTMS_OUTBOUND_TRIP_OWNING_DEPOT’.
To generate the file, the triggers will write a control record for the schedule of the trip, trip, status (i.e. ‘NEW’) and message type on table ‘INT_TRIP_DTL’.
Once the record is being processed the status will be updated to ‘PROCESSING’ and once completed to ‘SUCCESS’; if a record exists already with the same values at status ‘NEW’ then a new record will not be written to the table as the up-to-date details of the trip will be obtained when the record is processed via a database job (e.g. to run ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’)
The existing trigger on table ‘SCH_HAULAGE_ACTIVITY’ called ‘T_SCH_HAULAGE_ACTIVITY_BIU’ will be changed to write records to the table ‘INT_TRIP_DTL’.
If the system parameter ‘CTMS_EXELBAWT_TRIP_FLAG’ is set to ‘Y’ then the trip will be assessed and the record written to table ‘INT_TRIP_DTL’ as follows:
- INSERTING: If the owning depot of the trip is that of the system parameter ‘CTMS_OUTBOUND_TRIP_OWNING_DEPOT’ and a record for the trip and schedule does not exist on the table ‘INT_TRIP_DTL’ at status ‘NEW’.
- DELETING: If the owning depot of the trip is that of the system parameter ‘CTMS_OUTBOUND_TRIP_OWNING_DEPOT’ and a record for the trip and schedule does not exist on the table ‘INT_TRIP_DTL’ at status ‘NEW’.
These conditions will enable the 5 points listed above to be considered as a trip cannot be created without an order assigned and points 2 to 5 will all require a record on the table ‘SCH_HAULAGE_ACTIVITY’ to be either inserted or deleted. The difference between the points relates to the timing of the processing so that the status of the record may be assessed.
If many orders are being assigned to the same trip in a short space of time then it may be possible to write a new record to process whilst an existing record for the same trip is still processing or has been processed, this situation could occur but it will not cause a problem as a new record may be created for the trip, schedule and record status; the program to upload the trip file into WMS will only process the last record received for a trip in the same file so a duplicate trip cannot be uploaded.
Sequence Number
A new sequence called ‘SEQ_OUTBOUND_TRIP_DTL’ will be created for the new filename format.
Database Job
An existing database job will be used to process the outbound control records written by the trip triggers.
In the ‘CONPRD’ this database job is currently number 32 and runs the following procedure:
‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’
Procedure ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’
This procedure will be changed to assess the new message type stored in system parameter ‘CTMS_OUTBOUND_TRIP_MSG_TYPE’ written by the trip triggers.
Once the record is being processed the status will be updated to ‘PROCESSING’ to allow new records to be written to the outbound control table should any new trip messages be triggered.
If the message type is read then a new procedure called ‘INT_MSG.F_PROCESS_OUTBOUND_TRIP_DTL’ will be run and will pass the following parameters:
- R_MSG.INT_RECORD_ID
- R_MSG.SCHED_NAME
- R_MSG.TRIP_ID
- T_FILENAME
- T_ERRMSG
If the new procedure has been run successfully then the record on the table ‘INT_TRIP_DTL’ will be updated to status ‘SUCCESS’ and the filename that was created will be stored (i.e. from ‘T_FILENAME’), otherwise it will be updated to status ‘FAILURE’ and the validation error that was generated will be stored (i.e. from ‘T_ERRMSG’).
Function ‘INT_MSG.F_PROCESS_OUTBOUND_TRIP_DTL’
A new procedure will be written to assess the trip details and to create the outbound file.
The procedure will receive the following parameters:
- I_INT_RECORD_ID
- I_SCHED_NAME
- I_TRIP_ID
- O_FILENAME
- O_ERRMSG
The inbound parameters (i.e. those beginning with ‘I’) will be used to assess the trip details. The outbound parameters (i.e. those beginning with ‘O’) will be populated and passed back to procedure ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’.
Only the Unison sales orders on the trip will be processed and included in the file.
The expected departure date and time of the trip will be included for the start-up leg of the trip from the owning depot; however, if all of the orders have been deleted from the trip, or the trip has been deleted, then the current system date and time will be included.
The orders will be sorted by the stop ID and then the OMS reference.
The ‘Drop Sequence’ will be a count of the orders processed for the ‘T001’ record and it will be zero-filled and right-justified.
N.B. The order owner of the WMS order number is stored in ‘SCH_ORD.CUSTOMER’ but its value is translated as ‘ORG_CUSTOMER.CUSTOMER_ID’ from ‘ORG_CUSTOMER.UNISON_IF_VALUE’ and it is ‘ORG_CUSTOMER.UNISON_IF_VALUE’ that will need to be included in the trip file.
The ‘Order Number’ will contain the ‘Order Owner’ plus the first 17 characters of ‘SCH_ORD.EXTERNAL_REF’ to ensure that the Unison sales order number is recognised when it is uploaded into WMS.
If the trip no longer contains any orders when it is processed then an ‘O001’ record line will not be added to the ‘T001’ record line; this will not cause a problem with the file in WMS as doing so will indicate that the orders have been removed from the trip.
N.B. There may be multiple trips included in the same file depending on how many trips have been processed between database jobs being run and the distribution of the message types on table ‘INT_TRIP_DTL’ in relation to the other message types waiting to be processed.
The file will be moved into the outbound directory once it has been created successfully and then transferred via FTP to ESI if the system parameter called ‘CTMS_ESI_FTP_DESTINATION_IP_ADDRESS’ is setup with an IP address for the transfer.
File Format
A new outbound message will be created and sent to ESI to transfer to Unison as specified in RIO ‘JF-85NC2Y’:
| T001 Record | ||||||
| Start | Length | Type | Element | Column | Table | Notes |
| 1 | 4 | Character | Record ID | “T001” | ||
| 5 | 20 | Character | Trip ID | TRIP_ID | SCH_TRIP | |
| 25 | 8 | Date | Departure Date | DEPART | SCH_TRIP_STOP | ‘YYYYMMDD’ |
| 33 | 4 | Date | Departure Time | DEPART | SCH_TRIP_STOP | ‘HH24MI’ |
| O001 Record | ||||||
| Start | Length | Type | Element | Column | Table | Notes |
| 1 | 4 | Character | Record ID | “O001” | ||
| 5 | 3 | Character | Order Owner | UNISON_IF_VALUE | ORG_CUSTOMER | |
| 8 | 20 | Character | Order Number | UNISON_IF_VALUE
+ EXTERNAL_REF[1:17] |
ORG_CUSTOMER + SCH_ORD | |
| 28 | 3 | Number | Drop Sequence | Count | ||
Table A
The new message will be created as a fixed-length file with detail lines in the format shown in ‘Table A’ above. The ‘T001’ record will be at the trip level and the ‘O001’ records at the order level’.
The filename format will be:
- {CTMS_OUTBOUND_TRIP_FILENAME}_{SEQ_OUTBOUND_TRIP_DTL}.txt
Table Updates Required
The table ‘INT_TRIP_DTL’ will have a new index added for the following columns to enable quicker access for the trigger ‘T_SCH_HAULAGE_ACTIVITY_BIU’:
- TRIP_ID
- SCHED_NAME
The following script may be run to create the new system parameters:
Error! Objects cannot be created from editing field codes.
The following script may be run to create a new sequence number:
Error! Objects cannot be created from editing field codes.
References
| EST-285418 MA-8D9L9J New Message Flow from C-TMS v1.0.txt | |||
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
| Updated to match names used in development and change to how Unison sales orders are assessed | ||||
| Included FTP process to ESI | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
