285843

DHL C-TMS

Improve EDI Delivery

FUNCTIONAL SPECIFICATION - 10.6

25/05/2011 - 1.1
Reference: 285843 PG-8DPLYJ

Client Requirement

Change Request Summary:

Improve EDI Delivery.

Change Request Details:

For outbound EDI where the message is "Pushed" to a 3rd Party recipient, that when the connection fails that the C-TMS application recovers automatically.

The EDI process should detect when a message fails to be delivered and blacklist that address, subsequent messages should be queued up awaiting transmission. The system will, at suitable periods, attempt to resend the original message. When connection is re-established the queued messages will be processed in the correct order.

Benefits identified as a result of the change:

Automatic recovery of the EDI process.

Reduced downtime due to messages not being processed automatically.

Removal of manual intervention in finding and resending failed messages.

Solution

Currently if the FTP process fails, there is no message returned to inform the business and there is no process to retry the FTP of failed files. The control table is updated to indicate that the record was processed so the user is unable to identify which messages have failed due to FTP errors.

As part of the process of creating the file, a filename is generated and the filename field is populated in the control table. Only records where the filename is populated are updated to processed status.

After opening the first cursor, we will extract the relevant FTP parameters and call a new procedure called ‘GET_RESPONSE’. The new procedure will attempt to open a connection to the EDI server, if the connection returns a network error, the procedure will return false, if the connection is established, the procedure will return true.

A new ‘IF’ statement will be added based on the return Boolean value from the new procedure, if it returns TRUE, the code will continue and the new file will be created and pushed to the DHL Link server. If the procedure returns FALSE, the cursor will loop to the next record and call the new procedure again.

Only records that have a successful response from the server will be processed, the filename will be populated and the processed flag set to ‘Y’. Any records that fail to get a response from the server will not be populated with a filename and will remain in the control table waiting to be processed. Each time the code is triggered, the system will attempt to process any records waiting in the control table.

To ensure that any failures are processed in the correct order, we would amend the cursor which selects the records from the control table to order by request_id, ensuring the older records are processed first.

All outbound EDI procedures would be required to be changed, this covers MICROLISE, CIM, LOTS, ISOTRACK etc.

When an ftp fails, a message will be e-mailed to the appropriate DHL site. For order events, an e-mail will be sent to a DHL site based on the GROUP NAME, for trip events, a message will be sent to the DHL site based on the OWNING DEPOT.

To achieve this, a new message type will be created called FTP_FAILURE. Within the existing MSG_CONSTRUCTOR package a new procedure will be created called P_FTP_FAILURE. This procedure will be passed a number of parameters regarding the record which has failed to be processed. This information will be used to build the message text.

If an FTP failure occurs, the new procedure P_FTP_FAILURE will be called and the data passed into the procedure will be linked to the process that has failed. For order based processing the OMS reference will be passed to the new message procedure, for trip processing, the trip id will be passed.

The message content for the FTP_FAILURE message type will be designed in detail as part of the functional specification.

The existing procedure MSG_PROCESSING.F_GET_MSG_DATA will be amended to process the new message type FTP_FAILURE.

The message maintenance screen will be used to assign e-mail addresses to each DHL site for the new message type.

As part of this development, any records that have failed to FTP will not be given a filename and the processed flag will remain as ‘N’. In the INT_ERRORS screen, a new field will be added to the XML_OUT tab called TRANSMIT.

If the record has a filename and processed flag is ticked, the transmit field will be set to SUCCESS.

If a record does not have a filename , processed flag is not ticked and the record was created over 30 minutes ago, TRANSMIT will be set to FAIL, if the record has been created within the last 30 minutes and is still not processed, TRANSMIT will be set to PENDING.

The TRANSMIT field will be re-evaluated each time the screen is refreshed.

Scope

This change will be applied to system version 10.6.0 on every test area and once approved every production area.

Set-up

Pre-requisites

1. The new system parameter ‘FTP_TRANSMIT_TIME_LIMIT’ exists.
2. The new message type ‘FTP_FAILURE’ exists.

Menu Structure

‘Unchanged’

Data

1. The new system parameter ‘FTP_TRANSMIT_TIME_LIMIT’ should be set to ‘30’ (minutes).
2. The new message type ‘FTP_FAILURE’ should exist.

Functional Description

Date Setup

System Parameters

The new system parameter ‘FTP_TRANSMIT_TIME_LIMIT’ should be set to ‘30’ minutes:

The description of the system parameter will be:

‘Determines the time limit (in minutes) above which a file being transferred is considered to be failed instead of pending.’

Messaging Maintenance

The new message type ‘FTP_FAILURE’ will be created for display in the ‘Message Config’ tab page:

The new message type will be setup for the ‘User’:

FTP processing

A new process will be written to check that the file may be transferred via FTP, this check will be a new function called ‘GET_RESPONSE’ in package ‘FTP’, for example:

The following processes currently use the FTP file handling and will need to check that FTP is available prior to processing the record read:

• INT_MSG.F_CREATE_EFX_TRIP_FILE
• INT_MSG.PROCESS_EFX_CANC
• INT_MSG.F_PROCESS_OUTBOUND_TRIP_DTL

• INT_XML_MIC.PROCESS_XML_OUTBOUND_MIC
• INT_XML_SMA.PROCESS_XML_OUTBOUND_SMA

• INT_XML_OUT2.PROCESS_XML_OUTBOUND_BEIG
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_BEIG2
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_ISO
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_ZETES
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_UNI
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_UNI_ITEM
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_UNI_ARR
• INT_XML_OUT2.PROCESS_GEN_TRIP_XML_ARR
• INT_XML_OUT2.PROCESS_GEN_TRIP_XML_DEL
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_GLO
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_GLO_ITEM
• INT_XML_OUT2.PROCESS_XML_OUTBOUND_GLO_ARR
• INT_XML_OUT2.PROCESS_MIL_XML_OUT
• INT_XML_OUT2.GEN_TRIP_XML

• PAR.XML_ORDERS_TO_PARAGON

The check for the FTP availability will be performed within the loop through the unprocessed messages and these messages will be ordered by request_id in ascending sequence to ensure that the oldest record is read first.

‘Microlise’ and ‘Smartphone’ Messages

For example, in procedure ‘INT_XML_MIC.PROCESS_XML_OUTBOUND_MIC’ as the first step after the line below:

‘FOR r_mic_trip IN c_mic_trip LOOP’

If availability is lost within the loop then the processing will stop until the next database job runs.

This method will ensure that files are not generated out of sequence if the FTP availability is restored during the same run.

The same logic will be applied to the ‘INT_XML_SMA.PROCESS_XML_OUTBOUND_SMA’ procedure.

‘EFX’ and ‘Pre-allocation’ Messages

For the ‘EFX’ messages the procedure ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’ will be changed to skip subsequent records for message types ‘EFX_TRIP_DTL’ and ‘EFX_CANC’ if FTP is found to be unavailable before processing a record so that the subsequent records remain at status ‘NEW’ for the next run.

The same logic will be applied to the pre-allocation messages of the message type based on system parameter ‘CTMS_OUTBOUND_TRIP_MSG_TYPE’ for records read in procedure ‘INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL’.

‘XML’ Messages

The ‘INT_XML_OUT2’ package will be changed for the outbound ‘XML’ messages so that they check the FTP availability, this will involve ordering the unprocessed records on the ‘INT_XML_CONTROL’ table by created date and skipping records should the FTP address become unavailable for that record type (i.e. ‘EXTERNAL_SYSTEM’).

The status of the control records will then remain at ‘N’ ready for the next run.

The procedures involved are listed in section 3.2.

‘Paragon’ Messages

The ‘PAR.XML_ORDERS_TO_PARAGON’ procedure will be changed to check the FTP availability before generating any messages.

E-mail Notification

When the unavailability of the FTP address is detected for a record as described in sections 3.2.1 to 3.2.4 then notification will be sent to the appropriate users of the unavailability by inserting a new event record in the ‘MSG_EVENT’ table

For order events, an e-mail will be sent to a DHL site based on the group name, for trip events, a message will be sent to the DHL site based on the owning depot.

N.B. The trip-level messages will be used except for ‘Paragon’ messages.

To achieve this, a new message type will be created called ‘FTP_FAILURE’. Within the existing MSG_CONSTRUCTOR package a new procedure will be created called P_FTP_FAILURE. This procedure will be passed a number of parameters regarding the record which has failed to be processed. This information will be used to build the message text.

If an FTP failure occurs or the FTP address has become unavailable, then the new procedure ‘P_FTP_FAILURE’ will be called and the data passed into the procedure will be linked to the process that has failed. For order based processing the OMS reference will be passed to the new message procedure and for trip processing the trip id will be passed.

The message content for the ‘FTP_FAILURE’ message type will be as follows:

• ‘Order X has failed to FTP for message X’ or
• ‘Trip X has failed to FTP for message X’ or
• ‘FTP address X is not available’

The existing ‘MSG_PROCESSING.F_GET_MSG_DATA’ procedure will be amended to process the new message type ‘FTP_FAILURE’.

The message maintenance screen will be used to assign e-mail addresses to each DHL site for the new message type.

‘Interface Errors’ Screen (‘INT_ERR’)

The ‘XML_OUT’ tab page of the ‘Interface Errors’ screen will be changed to include a new column with the heading ‘Transmit’ as may be seen in the screenshot below:

The ‘Transmit’ column will be displayed after the ‘Filename’ column and the existing columns will be realigned to accommodate the extra column.

‘Transmit’ will display one of three values depending on the processing of the record:

Value	Where
SUCCESS	The ‘Filename’ is populated and the ‘Processed’ box is ticked.
PENDING	The ‘Filename’ is not populated and the ‘Processed’ box is not ticked and the record was created at a time not greater than the system parameter ‘FTP_TRANSMIT_TIME_LIMIT’.
FAIL	The ‘Filename’ is not populated and the ‘Processed’ box is not ticked and the record was created at a time greater than the system parameter ‘FTP_TRANSMIT_TIME_LIMIT’.

The ‘Transmit’ value will be recalculated with each query.

Table Updates Required

The new system parameter may be created using the following script:

INSERT INTO ADM_SYSTEM_PARAM (PARAM_NAME,VALUE,DATA_TYPE,MAX_LENGTH,DISPLAYED,USER_MODIFIABLE,DESCRIPTION,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,CONFIG_BY,CONFIG_BY_VALUE) VALUES ('FTP_TRANSMIT_TIME_LIMIT','30','N',3,'Y','Y','Determines the time limit (in minutes) above which a file being transferred is considered to be failed instead of pending.','OBS',SYSDATE,'OBS',SYSDATE,'SYSTEM','NONE'); /

The new message type may be created using the following script:

insert into msg_event_type(event_type,event_type_desc,archive_retention_days)

VALUES ('FTP_FAILURE','Message for FTP failures',NULL);

insert into msg_type(msg_type,msg_type_desc,concat,sending_email_address,archive_retention_days)

values ('FTP_FAILURE','Message for FTP failures','N',,NULL);

insert into msg_req(event_type,recipient_type_id,msg_type) VALUES ('FTP_FAILURE','USER','FTP_FAILURE');

References

Ref No	Document Title & ID	Version	Date
1	EST-285843 PG-8DPLYJ Improve EDI Delivery v1.0.doc	1.0	09/03/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	23/05/11	Draft	Initial version	PDR
1.0	24/05/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager